30 лучших формул и функций Excel, которые вам следует знать

Microsoft Excel — это инструмент для работы с данными. Вероятно, найдется несколько человек, которые не пользовались Excel, учитывая его огромную популярность. Excel — это широко используемое программное приложение в современных отраслях, созданное для создания отчетов и бизнес-анализа. Excel поддерживает несколько встроенных приложений, которые упрощают его использование.

Одной из таких функций, которая выделяет Excel, является – формулы листа Excel. Здесь мы рассмотрим 25 лучших формул Excel, которые необходимо знать при работе в Excel. В этой статье мы рассмотрим следующие темы:

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

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

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

1. СУММА

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

Сумма “=СУММ(C2:C4)”

Рис. Функция «Сумма» в Excel

Как вы можете видеть выше, чтобы найти общую сумму продаж для каждой единицы, нам пришлось просто ввести функцию «=SUM(C2:C4)». Это автоматически добавляет 300, 385 и 480. Результат сохраняется в C5.

2. СРЕДНИЙ

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

СРЕДНЕЕ =СРЕДНЕЕ(C2, C3, C4)

Рис.: Функция среднего в Excel

Он автоматически вычисляет среднее значение, и вы можете сохранить результат в нужном вам месте.

3. СЧИТАЙТЕ

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

СЧЕТ =СЧЕТ(C1:C4)

Рис. Функция Microsoft Excel — Количество

Как видно выше, здесь мы считаем от C1 до C4, в идеале четыре ячейки. Но поскольку функция COUNT учитывает только ячейки с числовыми значениями, ответ — 3, так как ячейка, содержащая «Total Sales», здесь опущена.

Если вам необходимо подсчитать все ячейки с числовыми значениями, текстом и любым другим форматом данных, вы должны использовать функцию 'COUNTA()'. Однако COUNTA() не подсчитывает пустые ячейки.

Для подсчета количества пустых ячеек в диапазоне ячеек используется функция COUNTBLANK().

4. ИТОГО

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

Рис. Функция промежуточного итога в Excel

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

ПРОМЕЖУТОЧНЫЙ ИТОГ =ПРОМЕЖУТОЧНЫЙ ИТОГ(1, A2: A4)

В списке промежуточных итогов «1» относится к среднему. Следовательно, указанная выше функция даст среднее значение A2:A4 и ответ на него будет 11, который хранится в C5. Аналогично,

“=ПРОМЕЖУТОЧНЫЙ ИТОГ(4; A2: A4)”

Это выбирает ячейку с максимальным значением от A2 до A4, что равно 12. Включение «4» в функцию обеспечивает максимальный результат.

Рис. Функция подсчета в Excel

5. МОДУЛЬ

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

• В первом примере мы разделили 10 на 3. Остаток вычисляется с помощью функции

МОДУЛЬ =MOD(A2,3)

• Результат сохраняется в B2. Мы также можем напрямую ввести «=MOD(10,3)», так как это даст тот же ответ.

Рис. Функция модуля в Excel

• Аналогично, здесь мы разделили 12 на 4. Остаток равен 0, который хранится в B3.

Рис. Функция модуля в Excel

6. МОЩНОСТЬ

Функция «Power()» возвращает результат возведения числа в определенную степень. Давайте рассмотрим примеры, показанные ниже:

Рис.: Функция степенного возведения в Excel

Как вы можете видеть выше, чтобы найти степень числа 10, хранящегося в A2, возведенную в 3, нам нужно ввести:

Мощность =МОЩНОСТЬ (A2,3)

Вот как работает степенная функция в Excel.

7. ПОТОЛОК

Далее у нас есть функция ceiling. Функция CEILING() округляет число до ближайшего кратного ему по значимости.

Рис.: Функция потолка в Excel

Ближайшее наибольшее число, кратное 5 для 35,316, равно 40.

8. ПОЛ

В отличие от функции Ceiling, функция floor округляет число до ближайшего кратного по значимости числа.

Рис. Функция Floor в Excel

Ближайшее наименьшее число, кратное 5 для 35,316, равно 35.

9. ОБЪЕДИНИТЬ

Эта функция объединяет или объединяет несколько текстовых строк в одну текстовую строку. Ниже приведены различные способы выполнения этой функции.

• В этом примере мы оперировали синтаксисом:

СЦЕПИТЬ =СЦЕПИТЬ(A25; ” “; B25)

Рис. Функция «Конкатенация» в Excel

• В этом примере мы оперировали синтаксисом:

“=СЦЕПИТЬ(A27&” “&B27)”

Рис. Функция «Конкатенация» в Excel

Это были два способа реализации операции конкатенации в Excel.

Читайте также: Как использовать функцию «Конкатенация» в Excel?

10. ЛЕН

Функция LEN() возвращает общее количество символов в строке. Таким образом, она подсчитает общее количество символов, включая пробелы и специальные символы. Ниже приведен пример функции Len.

Рис. Функция Len в Excel

Давайте теперь перейдем к следующей функции Excel в нашем списке этой статьи.

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

11. ЗАМЕНИТЬ

Как следует из названия, функция REPLACE() заменяет часть текстовой строки другой текстовой строкой.

Синтаксис: «=REPLACE(old_text, start_num, num_chars, new_text)». Здесь start_num относится к индексной позиции, с которой вы хотите начать замену символов. Затем num_chars указывает количество символов, которые вы хотите заменить.

Давайте рассмотрим, как можно использовать эту функцию.

• Здесь мы заменяем A101 на B101, набрав

ЗАМЕНИТЬ =ЗАМЕНИТЬ(A15;1;1;”B”)

Рис.: Заменить функцию в Excel

• Далее мы заменяем A102 на A2102, введя:

“=ЗАМЕНИТЬ(A16,1,1, “A2”)”

Рис.: Заменить функцию в Excel

• Наконец, мы заменяем Адама на Саама, введя:

«=REPLACE(A17,1,2, «Са»)”

Рис.: Заменить функцию в Excel

Давайте теперь перейдем к нашей следующей функции.

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

12. ЗАМЕНА

Функция SUBSTITUTE() заменяет существующий текст новым текстом в текстовой строке.

Синтаксис: «=SUBSTITUTE(текст, старый_текст, новый_текст, (номер_экземпляра))».

Здесь (instance_num) ссылается на индексную позицию текущих текстов более одного раза.

Ниже приведены несколько примеров этой функции:

• Здесь мы заменяем «Мне нравится» на «Ему нравится», набрав:

“=ПОДСТАВИТЬ(A20; “Мне нравится”; “Ему нравится”)”

Рис.: Замена функции в Excel

• Далее мы заменяем второе число 2010, которое встречается в исходном тексте в ячейке A21, на число 2016, введя «=SUBSTITUTE(A21,2010, 2016,2)».

Рис.: Замена функции в Excel

• Теперь мы заменим оба 2010 в исходном тексте на 2016, введя «=SUBSTITUTE(A22,2010,2016)».

Рис.: Замена функции в Excel

Это все о функции замены, теперь давайте перейдем к нашей следующей функции.

13. ЛЕВЫЙ, ПРАВЫЙ, СРЕДНИЙ

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

Давайте разберем эти функции на нескольких примерах.

• В примере ниже мы используем функцию left для получения самого левого слова в предложении в ячейке A5.

Рис.: Левая функция в Excel

Ниже показан пример использования функции mid.

Рис.: Функция Mid в Excel

• Здесь у нас есть пример правильной функции.

Рис. Правая функция в Excel

14. ВЕРХНИЙ, НИЖНИЙ, СОБСТВЕННЫЙ

Функция UPPER() преобразует любую текстовую строку в верхний регистр. В отличие от этого, функция LOWER() преобразует любую текстовую строку в нижний регистр. Функция PROPER() преобразует любую текстовую строку в правильный регистр, т. е. первая буква в каждом слове будет заглавной, а все остальные — строчными.

Давайте лучше разберем это на следующих примерах:

• Здесь мы преобразовали текст в формате A6 в полный заглавный текст в формате A7.

Рис.: Верхняя функция в Excel

• Теперь мы преобразовали текст в формате A6 в полностью строчный, как показано на формате A7.

Рис.: Нижняя функция в Excel

• Наконец, мы преобразовали неправильный текст в формате A6 в чистый и правильный формат в формате A7.

Рис. Правильная функция в Excel

Теперь давайте перейдем к изучению некоторых функций даты и времени в Excel.

15. СЕЙЧАС()

Функция NOW() в Excel возвращает текущую системную дату и время.

Рис.: Функция Now в Excel

Результат функции NOW() будет меняться в зависимости от даты и времени вашей системы.

16. СЕГОДНЯ()

Функция TODAY() в Excel возвращает текущую системную дату.

Рис. Функция «Сегодня» в Excel

Функция DAY() используется для возврата дня месяца. Это будет число от 1 до 31. 1 — первый день месяца, 31 — последний день месяца.

Рис. Функция «День» в Excel

Функция MONTH() возвращает месяц, число от 1 до 12, где 1 — январь, а 12 — декабрь.

Рис. Функция «Месяц» в Excel

Функция YEAR(), как следует из названия, возвращает год из значения даты.

Рис. Функция года в Excel

17. ВРЕМЯ()

Функция TIME() преобразует часы, минуты и секунды, заданные в виде чисел, в порядковый номер Excel, отформатированный с использованием формата времени.

Рис. Функция времени в Excel

18. ЧАС, МИНУТА, СЕКУНДЫ

Функция HOUR() генерирует час из значения времени в виде числа от 0 до 23. Здесь 0 означает 12:00, а 23 — 23:00.

Рис. Функция «Час» в Excel

Функция MINUTE() возвращает минуту из значения времени в виде числа от 0 до 59.

Рис. Функция минут в Excel

Функция SECOND() возвращает секунду из значения времени в виде числа от 0 до 59.

Рис. Вторая функция в Excel

19. РАЗНДАТ

Функция DATEDIF() вычисляет разницу между двумя датами в годах, месяцах или днях.

Ниже приведен пример функции DATEDIF, в которой мы вычисляем текущий возраст человека на основе двух заданных дат: даты рождения и сегодняшней даты.

Рис. Функция Dateif в Excel

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

20. ВПР

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

Ниже приведены аргументы для функции ВПР:

искомое_значение — это значение, которое нужно найти в первом столбце таблицы.

таблица — указывает таблицу, из которой извлекается значение.

col_index — столбец в таблице, из которого необходимо извлечь значение.

range_lookup – (необязательно) TRUE = приблизительное совпадение (по умолчанию). FALSE = точное совпадение.

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

Если вы хотите найти отдел, к которому относится Стюарт, вы можете использовать функцию ВПР, как показано ниже:

Рис. Функция ВПР в Excel

Здесь ячейка A11 содержит искомое значение, A2: E7 — массив таблицы, 3 — индекс столбца с информацией об отделах, а 0 — диапазон поиска.

Если нажать Enter, вы получите ответ «Маркетинг», что означает, что Стюарт из отдела маркетинга.

21. ГПР

Подобно VLOOKUP, у нас есть еще одна функция, называемая HLOOKUP() или горизонтальный просмотр. Функция HLOOKUP ищет значение в верхней строке таблицы или массива преимуществ. Она возвращает значение в том же столбце из указанной вами строки.

Ниже приведены аргументы функции HLOOKUP:

искомое_значение — указывает искомое значение.

таблица — это таблица, из которой вам нужно извлечь данные.

row_index — это номер строки, из которой следует извлечь данные.

range_lookup – (необязательно) Это логическое значение, указывающее на точное или приблизительное совпадение. Значение по умолчанию — TRUE, что означает приблизительное совпадение.

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

Рис. Функция Hlookup в Excel

Здесь H23 имеет искомое значение, т. е. Дженсон, G1:M5 — массив таблицы, 4 — номер индекса строки, 0 — приблизительное совпадение.

После нажатия Enter будет выведено «Нью-Йорк».

Наша магистерская программа по аналитике данных поможет вам изучить аналитические инструменты и методы, чтобы стать экспертом по аналитике данных! Это идеальный курс для вас, чтобы начать карьеру. Запишитесь сейчас!

22. Формула ЕСЛИ

Функция IF() проверяет заданное условие и возвращает определенное значение, если оно ИСТИНА. Она вернет другое значение, если условие ЛОЖЬ.

В приведенном ниже примере мы хотим проверить, больше ли значение в ячейке A2 5. Если оно больше 5, функция вернет «Да, 4 больше», в противном случае она вернет «Нет».

Рис. Функция If в Excel

В этом случае будет возвращено «Нет», поскольку 4 не больше 5.

'IFERROR' — еще одна функция, которая широко используется. Эта функция возвращает значение, если выражение вычисляется как ошибка, в противном случае она вернет значение выражения.

Предположим, вы хотите разделить 10 на 0. Это недопустимое выражение, так как вы не можете разделить число на ноль. Это приведет к ошибке.

Вышеуказанная функция вернет «Невозможно разделить».

23. ИНДЕКС-МАТЧ

Функция INDEX-MATCH используется для возврата значения в столбце слева. С VLOOKUP вы застряли, возвращая оценку из столбца справа. Другая причина использовать index-match вместо VLOOKUP заключается в том, что VLOOKUP требует больше вычислительной мощности от Excel. Это связано с тем, что ей необходимо оценить весь выбранный вами массив таблиц. С INDEX-MATCH Excel нужно учитывать только столбец подстановки и столбец возврата.

Давайте посмотрим, как с помощью приведенной ниже таблицы можно найти город, в котором проживает Дженсон.

Рис. Функция Index-Match в Excel

Теперь давайте найдем отдел Зампа.

24. СЧЁТЕСЛИ

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

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

Давайте найдем, сколько раз Афганистан встречается в таблице.

Рис. Функция Countif в Excel

Функция СЧЁТЕСЛИМН подсчитывает количество ячеек, заданных заданным набором условий.

Если вы хотите подсчитать количество дней, в течение которых количество случаев в Индии превышало 100, вот как можно использовать функцию СЧЁТЕСЛИМН.

25. СУММЕСЛИ

Функция СУММЕСЛИ() складывает ячейки, заданные заданным условием или критериями.

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

Рис. Функция Sumif в Excel

Функция SUMIFS() складывает ячейки, заданные заданным набором условий или критериев.

Давайте найдем общее количество случаев заболевания во Франции в те дни, когда число смертей было менее 100.

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

26. Поиск цели

Goal Seek — это встроенная функция Advanced Excel Functions, которая позволяет вам получить желаемый результат, изменяя предположения. Процесс зависит от метода проб и ошибок для достижения желаемого результата.

Давайте рассмотрим пример, чтобы лучше понять это.

Пример

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

5000 долларов в месяц для погашения суммы кредита.

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

Давайте рассмотрим эту задачу по шагам, чтобы увидеть, как можно рассчитать процентную ставку, которая позволит погасить кредит в размере 400 000 долларов США с ежемесячным платежом в размере 5000 долларов США.

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

• Формулу PMT теперь следует ввести в ячейку, которая является ячейкой Payment, смежной с ней. В настоящее время в ячейке процентной ставки нет значения, Excel дает нам платеж в размере $3,333.33, поскольку он предполагает, что процентная ставка равна 0%. Игнорируйте ее.

• Перейти к данным > Анализ «Что – если» > Поиск цели

• Нажмите OK. Вы увидите, что функция поиска цели автоматически выдает процентную ставку, необходимую для погашения суммы кредита.

Перейдите в раздел «Главная» > «Число» и измените значение на «Процент».

Ваш результат будет выглядеть следующим образом:

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

27. Анализ «что если» с решателем

Анализ «что если» — это метод изменения значений для проверки различных сценариев формул в расширенном Excel.

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

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

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

Читайте также: Лучшее руководство по созданию панели мониторинга Excel

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчасАктивация надстройки Solver

• На вкладке Файл нажмите Параметры.
• Перейдите в раздел «Надстройки», выберите «Надстройка Solver» и нажмите кнопку «Перейти».

• Установите флажок «Надстройка Solver» и нажмите «ОК».

• На вкладке «Данные» в группе «Анализ» вы можете увидеть добавленную опцию «Поиск решения».

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчасКак использовать Solver в Excel

В этом примере мы попытаемся найти решение простой задачи оптимизации.

Задача: Предположим, вы владелец бизнеса и хотите, чтобы ваш доход составлял 8000 долларов.

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

Например, мы создали следующую модель:

• На вкладке Данные в группе Анализ нажмите кнопку Решатель.
• В заданной цели выберите ячейку дохода и установите ее значение равным 8000 долларов США.
• Чтобы изменить ячейку переменной, выберите ячейки C5, C6 и C10.

Ваша модель данных будет меняться в зависимости от условий.

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас

28. Если-иначе

Функция IF используется для проверки условия и возвращает значение, если условие действительно истинно, и предопределенное другое значение, если оно оказывается ложным.

Если-иначе =ЕСЛИ(тест, истинный результат, ложный результат)

29. Если-Ошибка

Функция Excel ЕСЛИОШИБКА возвращает альтернативный результат, если формула генерирует ошибку, и ожидаемый результат, если ошибка не обнаружена.

Если-Ошибка =ЕСЛИОШИБКА (значение, значение_если_ошибка)

Например, Excel возвращает ошибку деления на ноль, когда формула пытается разделить число на 0.

Используя функцию ЕСЛИОШИБКА, вы можете добавить сообщение, если формула возвращает ошибку.

Стратегии лидерского совершенстваБесплатный вебинар | 6 декабря, среда | 19:00 ISTРЕГИСТРИРУЙТЕСЬ СЕЙЧАС!

30. Индекс и соответствие

Это расширенная функция Excel. Функция ПОИСКПОЗ предназначена для возврата позиции значения в указанном диапазоне, тогда как функция ИНДЕКС возвращает определенное значение, присутствующее в одномерном диапазоне.

Функция MATCH возвращает позицию идентификатора, который вы ищете. Функция INDEX вернет значение зарплаты, соответствующее должности.

31. Функция смещения

Функция СМЕЩ возвращает ссылку на диапазон ячеек, представляющий собой указанное количество строк и столбцов из ячейки или диапазона ячеек.

Функция смещения =СМЕЩ(ссылка, строки, столбцы, (высота), (ширина))

Пример:

Рассмотрим следующие данные:

Чтобы сослаться на ячейку C4, начинающуюся с A1, ссылка будет A1, строки — 3, а столбцы — 2:

Станьте бизнес-аналитиком с использованием искусственного интеллектаПрограмма аспирантуры Purdue по бизнес-анализуИзучите сейчас

Функция СУММ со СМЕЩЕНИЕМ

В этом примере у нас есть данные по ежемесячным продажам за два года. Цель — найти сумму продаж за определенный месяц.

Функция OFFSET возвращает диапазон 1×2, 8 строк ниже ячейки A2 и 1 столбец справа от ячейки A2. Затем функция SUM вычисляет сумму этого диапазона.

Станьте самым высокооплачиваемым экспертом по бизнес-анализу с магистерской программой по бизнес-анализуУзнайте сейчас