Light-electric.com

IT Журнал
9 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Виды формул и функций в excel

БЛОГ

Только качественные посты

Практический справочник функций Microsoft Excel с примерами их использования

На сегодняшний день программа Microsoft Excel является самой популярной программой в бизнесе, которая позволяет решать различные задачи — от анализа до учета данных. Самым популярным инструментом в Excel являются встроенные функции, количество которых приближается к 1000 штук.

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

– изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN)[Базовые формулы Excel]
2. ВПР (VLOOKUP)[Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX)[По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH)[Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT)[Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS)[Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF)[Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT)[Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR)[Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS)[Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS)[Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS)[Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS)[Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL)[Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT)[Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE)[Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP)[Ищет значения в массиве]
18. СМЕЩ (OFFSET)[Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN)[Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS)[Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP)[Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK)[Возвращает случайное число]
23. Ч (N)[Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY)[Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &)[Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID)[Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT)[Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN)[Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH)[Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE)[Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER)[Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK)[Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM)[Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN)[Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT)[Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT)[Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW)[Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR)[Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM)[Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE)[Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF)[Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY)[Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL)[Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE)[Выдает транспонированный массив]
45. ПРЕОБР (CONVERT)[Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST)[Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE)[Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA)[Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM)[Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

  • Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
  • Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
  • Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

Формулы и функции в Excel

Формулы в Microsoft Excel являются инструкцией по выполнению последовательности операций. Применение формул позволяет на основе исходных данных выполнять разнообразные вычисления. Формула содержит функции, константы, адреса ячеек и диапазонов. Имена ячеек или диапазонов, объединенные знаками математических операций и круглыми скобками. Результатом вычислений по формуле является числовое или логическое значение в ячейке. Числовое значение формируется в случае использования только арифметических операций, а логическое значение (истина/ложь) – при использовании логических операций или операций сравнения. При вычислении учитывается приоритет выполнения операций, принятый в математике. При вводе формулы в ячейку автоматически вычисляется значение, рассчитанное по формуле. Для просмотра в таблице не вычисленных значений, а записи самих формул необходимо установить переключатель Формулы на вкладке Вид диалогового окна команды Сервис-параметры.

Читать еще:  Суммирование строк в excel

Ввод формулы начинается со знака равенства или плюса и заканчивается нажатием клавиши . При задании в формуле адреса ячейки (диапазона ячеек) используется непосредственный ввод с клавиатуры либо, выделив мышью ячейку (диапазон ячеек), автоматическое указание ее адреса в строке формул.

В электронной таблице часто приходится выполнять однотипные вычисления в различных ячейках. Чтобы не повторять в каждой из них ввод формулы в Excel, предлагается использовать технологию мультиплицирования формулы. При копировании формулы необходимо учитывать, что при изменении адресов используемых в формуле ячеек или диапазонов ячеек эти адреса автоматически корректируются.

В формулах возможно использование ячеек, находящихся на другом рабочем листе книги или в другой рабочей книге. В этом случае необходимо указывать имена этой рабочей книги и рабо­чего листа перед адресами ячеек или диапазона, содержимое которых участвует в вычислении. Например, Товары!А2 или [Налог.хls]Лист3!$В$2 или [Налог.хls]Лист3!Процент – если для ячейки В2 задано имя Процент. Задание имени ячейки осуще­ствляется командой Вставка–Имя–Присвоить.

Для удобства работы с рабочей книгой рекомендуется пере­именовать рабочие листы. Для этого используется команда Формат–Лист–Переименовать.

При использовании формул для более сложных расчетов Excel имеет средство автоматизации построения формул – мастер функций. Мастер функций позволяет построить любую комбинацию вложенных функций, отслеживая правильность завершения каждой из них. В процессе работы пользователь отвечает на вопросы мастера. В зависимости от ответов мастер включает необходимые функции. Вызов мастера функций осуществляет­ся командой Вставка–Функция. Создание функции мастером осуществляется за два шага. На первом шаге активизируется диалоговое окно выбора из множества функций Excel требуемой функции (рис. 2).

Рис. 6.2. Диалоговое окно Мастера функций (шаг выбора функции). В нижней части окна описаны аргументы функции СУММ.

В списке Категория приведены категории встроенных в Excel функций. При выборе соответствующей категории в списке Функция появляется перечень функций, принадлежащих выб­ранной категории.

Например, функция СУММ, принадлежащая категории Ма­тематические, выполняет суммирование содержимого ячеек или диапазонов ячеек. Эта функция позволяет задавать до 30 ар­гументов, каждый из которых является адресом отдельной ячей­ки или диапазона ячеек.

Функция ЕСЛИ, принадлежащая категории Логические, реа­лизует возможность задания альтернативных значений в теку­щую ячейку. Функция требует задания трех аргументов, первый из которых обычно принимает логическое значение. Как прави­ло, в качестве этого аргумента используется операция сравнения. Если значение первого аргумента истинно (отлично от нуля), то в ячейку записывается второй аргумент, в противном случае – третий.

Относящаяся к категории Ссылки и массивы функция ПРО­СМОТР имеет две синтаксические формы: вектор и массив. Век­торная форма просматривает диапазон, в который входят значе­ния только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает значе­ние из другого столбца или строки. Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива.

Функция СЕГОДНЯ позволяет включить в таблицу текущую дату (функция без аргументов).

Функции Excel 2016: 10 самых важных формул

Опубликовано Михаил Непомнящий в 24.10.2016 24.10.2016

Общее количество функций для работы с электронными таблицами великое множество. Однако среди них есть наиболее полезные для повседневного использования. Мы составили десять самых важных формул Excel 2016 на каждый день.

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

Раскрывающиеся списки Excel
Добавление раскрывающегося списка в вашу Excel-таблицу может значительно повысить удобство работы, а значит и эффективность.
Подробнее

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

В качестве результата может также выводиться значение указанной ячейки, как текстовое, так цифирное. В таком случае в дальнейшем достаточно будет поменять значение одной ячейки, без необходимости править формулу во всех местах использования.

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

Читать еще:  Пробел в ячейке excel

Как установить пароль на документ Word или Excel
Для безопасности своих документов не лишним бывает установить на них персональный пароль.
Подробнее

Для данной функции используется три параметра – непосредственно число, массив или ссылка на список чисел и порядок. При этом если порядок не указан или стоит значение 0, то ранг определяется в порядке убывание. Любое другое значение для порядка будет отсортировывать значения по возрастанию.

Применение: Для таблицы с доходами по месяцам можно добавить столбец с ранжированием, а в дальнейшем по этому столбцу сделать сортировку.

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

Применение: Возвращаясь к примеру с ранжированием, вместо ранга можно выводить значение лучшего показателя за выбранный период.

Минимум из выбранных значений

Аналогичным образом действует формула поиска минимальных значений. Идентичный синтаксис, обратный результат на выходе.

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Сумма выбранных значений

Напоследок, самая ходовая функция, которую знает каждый, когда-либо использовавший электронные таблицы Excel. Сложение производится по формуле СУММ, а в скобках задается интервал или интервалы ячеек, значения которых требуется суммировать.

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

Математические функции в Microsoft Excel

В программе Excel разработчиками заложено огромное количество различных функций, но, пользователи чаще всего пользуются математическими. Давайте рассмотрим их и подробнее остановимся на самых популярных.

Использование математических функций в программе

В категорию математических функций входит более 60 различных операторов, которые позволяют выполнять различные вычисления.

Вставить функцию в свободную ячейку таблицы можно по-разному:

  1. Жмем кнопку “Вставить функцию” (fx) слева от строки формул. Выполнить данное действие можно, находясь в любой вкладке.
  2. Переключаемся во вкладку “Формулы”. Здесь также представлена кнопка “Вставить функцию” – в левом углу ленты инструментов.
  3. Нажимаем комбинацию клавиш Shift+F3, чтобы вызвать Мастер функций.

Результатом любого из вышеописанных способов будет открытие окна вставки функции. Здесь мы выбираем категорию “Математические”.

Теперь, когда категория выбрана, в поле ниже отмечаем требуемую функцию и щелкаем OK.

После этого откроется окно с аргументами для заполнения.

Примечание: Если мы, находясь во вкладке “Формулы”, в группе инструментов “Библиотека функций” нажмем по значку математических функций, сразу откроется список операторов, которые мы можем выбрать, минуя окно вставки функции.

Стоит учитывать, что в предлагаемом перечне присутствуют не все операторы, но самые необходимые здесь все же есть, и в большинстве случаев их достаточно.

Теперь перейдем к детальному рассмотрению самых популярных функций.

Пожалуй, это самая популярная функция, которая используется в Эксель. С помощью нее выполняется суммирование числовых данных. Формула функции:

В аргументах можно указать как конкретные числа, так и ссылки на ячейки, содержащие числовые значения. Причем указать координаты можно вручную (с помощью клавиш клавиатуры) или методом клика/выделения непосредственно в самой таблице.

Для перехода к заполнению следующего аргумента достаточно кликнуть по полю напротив него или нажать клавишу Tab.

СУММЕСЛИ

Данная функция позволяет считать сумму чисел с заданным условиями, с помощью которых будет выполняться отбор значений, учитывающихся в суммировании. Формула выглядит следующим образом:

В аргументах функции указывается диапазон ячеек (вручную или путем выделения в таблице), значения которых нужно просуммировать. В качестве критерия можно задать следующие условия (в кавычках):

Аргумент “Диапазон_сумирования” заполнять не обязательно.

ПРОИЗВЕД

С помощью данного оператора выполняется умножение чисел. Синтаксис выглядит следующим образом:

В аргументах функции, как и в СУММ, можно указывать как конкретные числа, так и адреса ячеек (диапазоны ячеек), которые содержат числовые значения.

ЧАСТНОЕ

Чаще всего для деления используется формула со знаком “/” между делимым и делителем: =Число1/Число2 .

Однако в программе также есть отдельная функция для выполнения деления, синтаксис которой представлен ниже:

Заполнить нужно два аргумента: Числитель (Делимое) и Знаменатель (Делитель).

СТЕПЕНЬ

Оператор позволяет возвести число в указанную степень. Формула выглядит так:

В аргументах функции указывается само число, а также, степень, в которую нужно его возвести.

КОРЕНЬ

С помощью данного оператора можно извлечь квадратный корень из числа. Синтаксис выглядит следующим образом:

Заполнить требуется только один аргумент – “Число”.

ОКРУГЛ

Функция применяется для выполнения еще одного распространенного математического действия – округления чисел (по общематематическим правилам, т.е., к ближайшему по модулю значению). Синтаксис функции представлен ниже:

В аргументе “Число” указывается значение, которое требуется округлить. В числе разрядов, соответственно, пишем количество цифр, которые хотим оставить после запятой.

Также, в Excel доступны операторы ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ, которые, как следует из их названий, используются для округления до ближайшего верхнего и нижнего числа, соответственно (по модулю).

Позволяет получить модуль числа. Формула функции представлена ниже:

Заполнить нужно всего один аргумент – “Число”, модуль которого требуется найти.

С помощью этого оператора определяется логарифм числа по заданному основанию. Синтаксис функции представлен в виде:

Необходимо заполнить два аргумента: Число и Основание логарифма (если его не указать, программа примет значение по умолчанию, равное 10).

Также для десятичного логарифма предусмотрена отдельная функция – LOG10.

ОСТАТОК

Применяется для получения остатка от деления чисел. Формула оператора выглядит следующим образом:

Для того, чтобы получить результат, требуется заполнить значения двух аргументов: Число и Делитель.

Заключение

Таким образом, мы разобрали самые популярные математические функции, которые используются в Excel. Однако возможности программы гораздо шире, и в ее инструментарии можно найти функцию для успешного выполнения практически любой задачи.

Читать еще:  Как пронумеровать таблицу в excel автоматически

Работа с формулами в excel подробный разбор

Как поставить плюс, равно в Excel без формулы

Если вы читали предыдущие статьи по работе с таблицами Excel, то должны быть в курсе, что запись формулы начинается со знака «Равно». Когда возникает необходимость написать в ячейке этот знак без формулы, программа настойчиво продолжает считать такую запись началом формулы. При клике в другую ячейку после знака прописывается адрес ячейки. В этом случае есть несколько способов перехитрить Excel.

Решение:
Перед написанием знака равно, плюс (сложение), минус (вычитание), наклонная черта (деление) или звездочки(умножение) поставить пробел или апостроф.

Пример использования знаков «умножение» и «равно»

Почему в экселе формула не считает

Если вам приходится работать на разных компьютерах, то возможно придется столкнуться с тем, что необходимые в работе файлы Excel не производят расчет по формулам.

Неверный формат ячеек или неправильные настройки диапазонов ячеек

В Excel возникают различные ошибки с хештегом (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО!, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #ПУСТО!. Они указывают на то, что что-то в формуле работает неправильно. Причин может быть несколько.

Вместо результата выдается #ЗНАЧ! (в версии 2010) или отображается формула в текстовом формате (в версии 2016).

Примеры ошибок в формулах

В данном примере видно, что перемножается содержимое ячеек с разным типом данных =C4*D4.

Исправление ошибки: указание правильного адреса =C4*E4 и копирование формулы на весь диапазон.

  • Ошибка #ССЫЛКА! возникает, когда формула ссылается на ячейки, которые были удалены или заменены другими данными.
  • Ошибка #ЧИСЛО! возникает тогда, когда формула или функция содержит недопустимое числовое значение.
  • Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
  • Ошибка #ДЕЛ/0! возникает, когда число делится на ноль (0).
  • Ошибка #ИМЯ? возникает из-за опечатки в имени формулы, то есть формула содержит ссылку на имя, которое не определено в Excel.
  • Ошибка #ПУСТО! возникает, если задано пересечение двух областей, которые в действительности не пересекаются или использован неправильный разделитель между ссылками при указании диапазона.

Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная — Формат — Автоподбор ширины столбца.

Ошибки в формулах

Зеленые треугольники в углу ячейки могут указывать на ошибку: числа записаны как текст. Числа, хранящиеся как текст, могут приводить к непредвиденным результатам.

Исправление: Выделите ячейку или диапазон ячеек. Нажмите знак «Ошибка» (смотри рисунок) и выберите нужное действие.

Пример исправления ошибок в Excel

Включен режим показа формул

Так как в обычном режиме в ячейках отображаются расчетные значения, то чтобы увидеть непосредственно расчетные формулы в Excel предусмотрен режим отображения всех формул на листе. Включение и отключение данного режима можно вызвать командой Показать формулы из вкладки Формулы в разделе Зависимости формул.

Отключен автоматический расчет по формулам

Такое возможно в файлах с большим объемом вычислений. Для того чтобы слабый компьютер не тормозил, автор файла может отключить автоматический расчет в свойствах файла.

Исправление: после изменения данных нажать кнопку F9 для обновления результатов или включить автоматический расчет. Файл – Параметры – Формулы – Параметры вычислений – Вычисления в книге: автоматически.

Формула сложения в Excel

Выполнить сложение в электронных таблицах достаточно просто. Нужно написать формулу, в которой будут указаны все ячейки, содержащие данные для сложения. Конечно же, между адресами ячеек ставим плюс. Например, =C6+C7+C8+C9+C10+C11.

Пример вычисления суммы в Excel

Но если ячеек слишком много, то лучше воспользоваться встроенной функцией Автосумма. Для этого кликните ячейку, в которой будет выведен результат, а затем нажмите кнопку Автосумма на вкладке Формулы (выделено красной рамкой).

Будет выделен диапазон ячеек для суммирования. Если диапазон выделился неправильно, например, выделены вертикальные ячейки, а необходимы горизонтальные, то выделите новый диапазон. Для этого нажмите левой кнопкой мыши в крайнюю ячейку нового диапазона и не отпуская кнопку проведите указатель по всем ячейкам диапазона до конечной. Завершите ввод формулы нажатием клавиши Enter на клавиатуре.

Формула округления в Excel до целого числа

Начинающие пользователи используют форматирование, с помощью которого некоторые пытаются округлить число. Однако, это никак не влияет на содержимое ячейки, о чем и указывается во всплывающей подсказке. При нажатии на кнопочку (см. рисунок) произойдет изменение формата числа, то есть изменение его видимой части, а содержимое ячейки останется неизменным. Это видно в строке формул.

Уменьшение разрядности не округляет число

Для округления числа по математическим правилам необходимо использовать встроенную функцию =ОКРУГЛ(число;число_разрядов).

Математическое округление числа с помощью встроенной функции

Написать её можно вручную или воспользоваться мастером функций на вкладке Формулы в группе Математические (смотрите рисунок).

Мастер функций Excel

Данная функция может округлять не только дробную часть числа, но и целые числа до нужного разряда. Для этого при записи формулы укажите число разрядов со знаком «минус».

Как считать проценты от числа

Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара.
Формула =C4*(1-D4)

Вычисление стоимости товара с учетом скидки

В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4). Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.

Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.

Шпаргалка с формулами Excel

Шпаргалка выполнена в виде PDF-файла. В нее включены наиболее востребованные формулы из следующих категорий: математические, текстовые, логические, статистические. Чтобы получить шпаргалку, кликните ссылку ниже.

Ваша ссылка для скачивания шпаргалки с яндекс диска

Дополнительная информация:

PS: Интересные факты о реальной стоимости популярных товаров

Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector