Список формул в excel с описанием

Для человека, начинающего изучать MS Excel информация, что тот поддерживает около 500 функций рабочего листа носит, прямо скажем, двойственный характер: с одной стороны, радостно слышать, что объект твоего интереса умеет вычислять гиперболический секанс комплексного числа или интегральную функцию бета-распределения, однако, с другой стороны, возникает вопрос, какие формулы надо изучать в первую очередь?

Я на основе своего субъективного опыта классифицировал все функции на 5 групп:

Классификация функций Количество
1. Знать наизусть 44
2. Иметь представление, что и как 53
3. Знать о существовании 20
4. Редкая птица. 44
5. Узок круг этих революционеров. 293
Общий итог 454

Кроме этого, вам может пригодиться информация:

  • Группа, к которой принадлежит формула
  • Английское имя
  • Русское имя
  • Краткое описание
  • В какой версии Excel формула появилась

Представляю вашему вниманию проделанную работу в виде отдельного XLSX файла

Microsoft Excel — один из самых популярных инструментов анализа данных в мире. Все больше фирмы используют это программное обеспечение и много людей ежедневно пользуются Excel. Однако, по моему опыту, только немногие из них в полной мере используют возможности, которые может предложить эта программа. Я представляю Вам мой список из топ 20 самых полезных формул Excel, которые каждый должен использовать для повышения эффективности. Функции Excel также очень рекомендуются для вычислений, так как они могут значительно уменьшить количество ошибок. И последнее, но не менее важное: изучение этих функций — прекрасная возможность улучшить Ваши знания Excel и стать уверенным пользователем MS Excel.

Table of Contents

1. Sum

«Sum» — это, наверное, самая простая, но и самая важная функция Exceл. Вы можете использовать эта формула для вычисления суммы диапазона ячеек.

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

2. Average

Еще одна очень важная функция. Как следует из названия, эта формула оценивает среднее число из диапазона ячеек. Предположим, что мы имеем диапазон чисел в столбце A.

Это вернет среднее значение ячеек от A1 до A10.

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

Здесь формула проверит если продажи в ячейке «A2» более 100 €. Если это заявление верно, формула возвращает «да». Если продажи ниже 100 евро, формула возвращает «нет».

4. Sumif

Sumif — еще одна очень важная формула Excel. Формула будет суммировать числа, если выполняется определенное условие.

Давайте рассмотрим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы хотим узнать общее количество продаж для каждого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Парень зарегистрировал общий объем продаж 325 €. Мы можем использовать формулу для расчета оборота других агентов.

5. Countif

Countif — очень полезная функция, которая работает как sumif. Он суммируется при определенном условии. Разница между sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие истинно. Напротив, суммирующая функция суммирует значения внутри ячеек.

Формула в приведенном выше примере проверяет, сколько продаж были выше чем 100 евро и возвращает количество продаж. Вы также можете включить более одного критерия, используя countif для этой цели.

PS: Если вы не уверены в синтаксисе функции, вы всегда можете использовать функцию «insert function» в Excel (просто нажмите знак функции в левой части функциональной панели). В следующем примере мы продемонстрируем, как это работает.

6. Counta

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

Это даст вам количество строк, которые содержат значение, текст или любой другой символ.

7. Vlookup

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

Продолжим этот пример, добавив еще один столбец «А» с номерами клиентов.

Обратите внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Однако у нас есть еще один лист Excel (Sheet2) с именами клиентов и номерами клиентов.

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

Эта формула ищет значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы всегда хотим иметь постоянный диапазон поиска). Затем функция возвращает значение из второго столбца. Последняя часть формулы, мы пишем 0. Таким образом, мы делаем Excel не искать совпадение в порядке чисел.

Теперь попробуйте создать собственную формулу vlookup, используя опцию insert function.

В нашем случае это выглядит так:

8. Left, Right, Mid

Left, right и mid функции — очень важные функции, которые позволяют извлекать определенное количество символов из строки

В нашем примере с формулой left мы берем первый символ с левой стороны. С формулой right мы можем сделать то же самое, но с символами справа налево. Имейте в виду, что пустое пространство также является символом.

9. Trim

Еще одна исключительная функция MS Excel. Функция trim удаляет пустые пространства после слов, когда их больше одного. Это может случиться довольно часто в Excel. Возможно, что информация извлекается из разных баз данных и заполняется ненужными пустым пространством между словами. Это может быть огромной проблемой, потому что ваши формули Excel могут не работать. Чтобы избавиться от раздражающих пустых пространств, мы используем формулу trim. = TRIM(A1)

В этом примере формула удалит лишние пробелы, если она найдет несколько.

10. Concatenate

Это удобная функция Excel, которая помогает, когда мы хотим объединить ячейки. Здесь мы хотим объединить ячейки A1, A2 и A3. Это можно сделать с помощью следующей функции:

Таким образом, у нас не будет пробелов между словами. Чтобы добавить пробелы между «I» и «love», нам нужно добавить пробел. Это можно решить, добавив кавычки внутри функции:

PS: Вы также можете объединить ячейки, просто добавив:

11. Len

Еще одна прекрасная формула, которая может применяться во многих ситуациях. Она широко используется для построения более сложных формул.

Эта функция будет подсчитывать количество символов в ячейке.

12. Max

Функция max возвращает наибольшее значение из диапазона.

Эта формула будет выглядеть в ячейках A2: A8, и тогда она извлечет самое высокое значение из этого диапазона.

13. Min

Функция min функционирует аналогично функции max, однако она дает противоположный результат. Он извлекает самое низкое значение из диапазона данных.

Формула из приведенного выше примера вернет наименьшее число из выбранных ячеек в столбце «A».

14. Days

Это функция, когда вам нужно вычислить количество дней между двумя днями в электронной таблице.

Формула вернет количество прошедших дней.

15. Networkdays

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

16. SQRT

Это довольно просто и довольно полезно одновременно. Как следует из названия, формула дает квадратный корень из числа

Это вернет квадратный корень из 1444 (38). Вы также можете использовать ссылку на ячейку здесь, как и во многих других формулах Excel.

17. Now

Иногда вам нужно знать текущую дату, когда вы открываете электронную таблицу Excel.

Это вернет текущую дату. Не забудьте установить формат на сегодняшний день. Вы можете попробовать с ним столько, сколько хотите. Вы можете, например, добавлять или вычитать дни. = Now () -14 вернет дату до двух недель.

18. Round

Функция Round очень полезна, когда вам приходится работать с округленными номерами. Excel может отображать числа для определенного символа, но сохраняет исходный номер и использует его для вычислений. Однако в некоторых случаях это может быть проблемой, и нам нужно работать только с округленными номерами. В таких случаях функция ROUND обращается к нашей помощи. = ROUND(B1,2)

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

19.Roundup

Это вариация формулы round, которая может очень помочь в некоторых случаях. Онa округляет значение до ближайшего целого числа. Это очень полезно в сочетании с другими формулами Excel. Давайте посмотрим на этот пример:

Здесь мы хотим найти в каком квартале текущего года находится текущую дату. Секрет этой формулы — простая математика. Функция принимает номер месяца, например, январь равен 1, февраль равен 2 и т. Д., А затем делит его на 3, округляя до ближайшего большего числа.

20. Rounddown

Rounddown работает аналогично Roundup, однако она округляет число до ближайшего целого числа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


[an error occurred while processing the directive]
Карта сайта