Топ 20 самых важных формул MS Excel
Microsoft Excel - один из самых популярных инструментов анализа данных в мире. Все больше фирмы используют это программное обеспечение и много людей ежедневно пользуются Excel. Однако, по моему опыту, только немногие из них в полной мере используют возможности, которые может предложить эта программа. Я представляю Вам мой список из топ 20 самых полезных формул Excel, которые каждый должен использовать для повышения эффективности. Функции Excel также очень рекомендуются для вычислений, так как они могут значительно уменьшить количество ошибок. И последнее, но не менее важное: изучение этих функций - прекрасная возможность улучшить Ваши знания Excel и стать уверенным пользователем MS Excel.
Table of Contents
1. Sum
«Sum» - это, наверное, самая простая, но и самая важная функция Exceл. Вы можете использовать эта формула для вычисления суммы диапазона ячеек.
= SUM(A1:A10) or = SUM(A1,A10)
В этом примере первая формула суммирует ячейки от A1 до A10. Вторая суммирует только ячейки A1 и A10. Вы можете попробовать с ним и попытаться использовать разные диапазоны, чтобы привыкнуть к этой функций. Обратите внимание, что вы также можете использовать числа внутри этой функции вместо ссылок.
2. Average
Еще одна очень важная функция. Как следует из названия, эта формула оценивает среднее число из диапазона ячеек. Предположим, что мы имеем диапазон чисел в столбце A.
= AVERAGE(A1:A10)
Это вернет среднее значение ячеек от A1 до A10.
3. If
Другая очень необходимая формула. Она относительно простая в использовании, но и очень мощная. Вы можете использовать эту функцию, чтобы проверить, является ли данный оператор истинным или ложным, и чтобы вернуть указанное число.
= IF(A2>100,"yes","no")
Здесь формула проверит если продажи в ячейке «A2» более 100 €. Если это заявление верно, формула возвращает «да». Если продажи ниже 100 евро, формула возвращает «нет».
4. Sumif
Sumif - еще одна очень важная формула Excel. Формула будет суммировать числа, если выполняется определенное условие.
= SUMIF(A1:A20,A2,B1:B20)
Давайте рассмотрим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы хотим узнать общее количество продаж для каждого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Парень зарегистрировал общий объем продаж 325 €. Мы можем использовать формулу для расчета оборота других агентов.
5. Countif
Countif - очень полезная функция, которая работает как sumif. Он суммируется при определенном условии. Разница между sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие истинно. Напротив, суммирующая функция суммирует значения внутри ячеек.
= COUNTIF(A1:A10,">100")
Формула в приведенном выше примере проверяет, сколько продаж были выше чем 100 евро и возвращает количество продаж. Вы также можете включить более одного критерия, используя countif для этой цели.
PS: Если вы не уверены в синтаксисе функции, вы всегда можете использовать функцию «insert function» в Excel (просто нажмите знак функции в левой части функциональной панели). В следующем примере мы продемонстрируем, как это работает.
6. Counta
Эта формула подсчитывает количество ячеек определенного диапазона, которые не являются пустыми. Это очень полезно, когда у вас есть тысячи строк, и вы хотите знать, где все это кончается.
= COUNTA(A:A)
Это даст вам количество строк, которые содержат значение, текст или любой другой символ.
7. Vlookup
Вероятно, самая мощная функция Excel. Очень полезно, когда вам приходится иметь дело с более чем одной таблицей, разными листами или даже другого файла. Vlookup позволяет вам извлекать данные из одной таблицы в другую.
Продолжим этот пример, добавив еще один столбец «А» с номерами клиентов.
Обратите внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Однако у нас есть еще один лист Excel (Sheet2) с именами клиентов и номерами клиентов.
Вместо того, чтобы тратить свое время, чтобы посмотреть с Sheet1 на Sheet2 и искать совпадение вручную для каждой отдельной строки, мы можем использовать следующую формулу:
= VLOOKUP(A2,Sheet2!$A$1:$B$100,2,0)
Эта формула ищет значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы всегда хотим иметь постоянный диапазон поиска). Затем функция возвращает значение из второго столбца. Последняя часть формулы, мы пишем 0. Таким образом, мы делаем Excel не искать совпадение в порядке чисел.
Теперь попробуйте создать собственную формулу vlookup, используя опцию insert function.
В нашем случае это выглядит так:
8. Left, Right, Mid
Left, right и mid функции - очень важные функции, которые позволяют извлекать определенное количество символов из строки
= Left(A1,1) or = MID(A1,4,6) or = RIGHT(A1,5)
В нашем примере с формулой left мы берем первый символ с левой стороны. С формулой right мы можем сделать то же самое, но с символами справа налево. Имейте в виду, что пустое пространство также является символом.
9. Trim
Еще одна исключительная функция MS Excel. Функция trim удаляет пустые пространства после слов, когда их больше одного. Это может случиться довольно часто в Excel. Возможно, что информация извлекается из разных баз данных и заполняется ненужными пустым пространством между словами. Это может быть огромной проблемой, потому что ваши формули Excel могут не работать. Чтобы избавиться от раздражающих пустых пространств, мы используем формулу trim. = TRIM(A1)
В этом примере формула удалит лишние пробелы, если она найдет несколько.
10. Concatenate
Это удобная функция Excel, которая помогает, когда мы хотим объединить ячейки. Здесь мы хотим объединить ячейки A1, A2 и A3. Это можно сделать с помощью следующей функции:
= CONCATENATE(A1,A2,A3)
Таким образом, у нас не будет пробелов между словами. Чтобы добавить пробелы между «I» и «love», нам нужно добавить пробел. Это можно решить, добавив кавычки внутри функции:
= CONCATENATE(A1," ",A2," ",A3)
PS: Вы также можете объединить ячейки, просто добавив:
= A1&" "&A2&" "&A3
Попробуйте сами!
11. Len
Еще одна прекрасная формула, которая может применяться во многих ситуациях. Она широко используется для построения более сложных формул.
= LEN(A1)
Эта функция будет подсчитывать количество символов в ячейке.
12. Max
Функция max возвращает наибольшее значение из диапазона.
= MAX(A2:A8)
Эта формула будет выглядеть в ячейках A2: A8, и тогда она извлечет самое высокое значение из этого диапазона.
13. Min
Функция min функционирует аналогично функции max, однако она дает противоположный результат. Он извлекает самое низкое значение из диапазона данных.
= MIN(A2:A8)
Формула из приведенного выше примера вернет наименьшее число из выбранных ячеек в столбце «A».
14. Days
Это функция, когда вам нужно вычислить количество дней между двумя днями в электронной таблице.
= DAYS(B1,A1)
Формула вернет количество прошедших дней.
15. Networkdays
Иногда вы хотите работать только с рабочими днями. Эта функция работает очень похоже на приведенный выше пример. Разница в том, что он возвращает рабочие дни, прошедшие, за исключением выходных.
16. SQRT
Это довольно просто и довольно полезно одновременно. Как следует из названия, формула дает квадратный корень из числа
= SQRT(1444)
Это вернет квадратный корень из 1444 (38). Вы также можете использовать ссылку на ячейку здесь, как и во многих других формулах Excel.
17. Now
Иногда вам нужно знать текущую дату, когда вы открываете электронную таблицу Excel.
= NOW()
Это вернет текущую дату. Не забудьте установить формат на сегодняшний день. Вы можете попробовать с ним столько, сколько хотите. Вы можете, например, добавлять или вычитать дни. = Now () -14 вернет дату до двух недель.
18. Round
Функция Round очень полезна, когда вам приходится работать с округленными номерами. Excel может отображать числа для определенного символа, но сохраняет исходный номер и использует его для вычислений. Однако в некоторых случаях это может быть проблемой, и нам нужно работать только с округленными номерами. В таких случаях функция ROUND обращается к нашей помощи. = ROUND(B1,2)
В этом примере число округляется до второго десятичного знака.
19.Roundup
Это вариация формулы round, которая может очень помочь в некоторых случаях. Онa округляет значение до ближайшего целого числа. Это очень полезно в сочетании с другими формулами Excel. Давайте посмотрим на этот пример:
= ROUNDUP(MONTH(B3)/3,0)
Здесь мы хотим найти в каком квартале текущего года находится текущую дату. Секрет этой формулы - простая математика. Функция принимает номер месяца, например, январь равен 1, февраль равен 2 и т. Д., А затем делит его на 3, округляя до ближайшего большего числа.
20. Rounddown
Rounddown работает аналогично Roundup, однако она округляет число до ближайшего целого числа
= ROUNDDOWN(20.65,0)
Это вернет 20.
Excel имеет много полезных функций. В этой статье я попытался представить наиболее важные формулы в MS Excel наилучшим образом. Я считаю, что эти функции являются очень хорошей базой для тех из вас, кто хочет развивать свои умения Excel. Посредством освоения этих формул вы можете легко справляться с повседневными ситуациями, которые могут возникать при работе с Excel, и это действительно хорошая возможность для дальнейшего развития ваших навыков.
© 2017 Атанас Йонков
Прочитайте больше: 18 готовые макросы в VBA Excel