Лучшие формулы и функции Excel

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

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

31 формула и функция Excel

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

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

1. СУММА

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

=СУММ(C2:C4)

Рис: Функция суммы в Excel

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

2. СРЕДНИЙ

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

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

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

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

3. СЧИТАТЬ

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

=СЧЁТ(C1:C4)

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

Как видно выше, здесь мы считаем от C1 до C4, в идеале четыре ячейки. Но поскольку функция СЧЕТ принимает во внимание только ячейки с числовыми значениями, ответ — 3, поскольку ячейка, содержащая «Общий объем продаж», здесь опущена.

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

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

Изучите последние тенденции в аналитике данных! Программа последипломного образования в программе Data AnalyticsИзучите программу

4. ПРОМЕЖУТОЧНЫЙ ИТОГО

Забегая вперед, давайте теперь поймем, как работает функция промежуточного итога. Функция 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 — это только начало: изучите визуализацию, анализ и бизнес-аналитику данных. Зарегистрируйтесь в магистерской программе аналитика данных и станьте сертифицированным аналитиком данных!

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

7. ПОТОЛОК

Далее у нас есть функция потолка. Функция ПОТОЛОК() округляет число до ближайшего кратного значения.

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

=ПОТОЛОК(A2,1)

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

Хотите выделиться на этом конкурентном рынке? Присоединяйтесь к нашей программе «Аналитик данных» и сделайте первый шаг к успешной карьере в области анализа данных! 📈

8. ЭТАЖ

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

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

=ЭТАЖ(A2,1)

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

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

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

Вы знали? 🔍
Согласно прогнозам, рынок труда аналитиков данных вырастет на 23% в период с 2022 по 2032 год. Бюро статистики труда США— и компании готовы платить чрезвычайно конкурентоспособные зарплаты.

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

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

Рис. Функция объединения в Excel

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

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

Рис. Функция объединения в Excel

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

Читайте также: Как использовать объединение в Excel? 📈

Вступайте в ряды первоклассных аналитиков данных! Магистерская программа Data AnalystИзучите программу

10. ЛЕН

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

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

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

11. ЗАМЕНА

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

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

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

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

=ЗАМЕНИТЬ(A15,1,1,”Б”)

Рис. Функция замены в Excel

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

«=ЗАМЕНИТЬ(A16,1,1, «A2»)»

Рис. Функция замены в Excel

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

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

Рис. Функция замены в Excel

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

Изучите последние тенденции в аналитике данных! Программа последипломного образования в программе Data AnalyticsИзучите программу

12. ЗАМЕНА

Функция ПОДСТАВИТЬ() заменяет существующий текст новым текстом в текстовой строке.

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

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

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

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

«=SUBSTITUTE(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

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

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

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

15. СЕЙЧАС()

Функция СЕЙЧАС() в Excel возвращает текущую системную дату и время.

Рис.: Теперь функция в Excel

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

Аналитика данных с помощью Python или R? Почему не оба?! Программа последипломного образования в области анализа данныхПрограмма изучения

16. СЕГОДНЯ()

Функция СЕГОДНЯ() в Excel предоставляет текущую системную дату.

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

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

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

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

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

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

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

17. ВРЕМЯ()

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

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

Программа последипломного образования Simplilearn в области аналитики данных, созданная для начинающих аналитиков и профессионалов, стремящихся улучшить свою карьеру, предоставляет вам практический опыт и инструменты для решения бизнес-задач. 📈

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

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

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

Функция МИНУТА() возвращает минуты из значения времени в виде числа от 0 до 59.

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

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

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

Ваша карьера в области аналитики данных не за горами! Магистерская программа по аналитике данныхИзучите программу

19. РАЗДАТА

Функция РАЗНДАТ() позволяет определить разницу между двумя датами в годах, месяцах или днях.

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

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

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

20. ВПР

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

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

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

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

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

range_lookup — (необязательно) TRUE = приблизительное совпадение (по умолчанию). ЛОЖЬ = точное совпадение.

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

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

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

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

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

Станьте экспертом по аналитике данных всего за 8 месяцев! С помощью программы Data Analytics PG Университета ПердьюПодробнее

21. ПРОСМОТР

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

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

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

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

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

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

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

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

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

Как только вы нажмете Enter, он вернет «Нью-Йорк».

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

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

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

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

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

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

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

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

Станьте экспертом в области аналитики данных с нашей уникальной магистерской программой для аналитиков данныхПрограмма изучения

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

Функция ИНДЕКС-ПОИСКПОЗ используется для возврата значения в столбце слева. При использовании VLOOKUP вы застреваете, возвращая оценку из столбца справа. Другая причина использовать сопоставление индексов вместо ВПР заключается в том, что ВПР требует большей вычислительной мощности Excel. Это связано с тем, что ему необходимо оценить весь выбранный вами массив таблиц. При использовании ИНДЕКС-ПОИСКПОЗ Excel должен учитывать только столбец поиска и столбец возврата.

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

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

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

24. СЧЁТЕСЛИ

Функция СЧЕТЕСЛИ() используется для подсчета общего количества ячеек в диапазоне, соответствующих заданному условию.

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

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

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

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

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

Изучите последние тенденции в аналитике данных! Программа последипломного образования в программе Data AnalyticsИзучите программу

25. СУММЕФ

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

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

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

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

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

Хотите стать аналитиком данных? Учитесь у экспертов! Магистерская программа по аналитике данныхИзучите программу

26. Поиск цели

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

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

Пример

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

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

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

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

• Формула PMT теперь должна быть введена в ячейку, соседнюю с ячейкой «Платеж». В настоящее время в ячейке процентной ставки нет значения, Excel дает нам платеж в размере 3333,33 доллара США, поскольку предполагается, что процентная ставка равна 0%. Не обращайте на это внимания.

• Перейдите в раздел «Данные» > «Анализ «что-если»» > «Поиск цели».

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

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

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

Изучите более 15 навыков и инструментов расширенного анализа данных с помощью программы Университета Пердью по анализу данныхИзучите сейчас

27. Анализ «что, если» с помощью решателя

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

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

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

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

Читайте также: Лучшее руководство по созданию информационной панели Excel 💻 Активация надстройки Solver

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

• Проверьте надстройку Solver и нажмите «ОК».

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

Как использовать Солвер в Excel

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

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

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

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

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

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

Хотите стать аналитиком данных? Учитесь у экспертов! Магистерская программа по аналитике данныхИзучите программу

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

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

If-Else =IF(тест, истинный результат, ложный результат)

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

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

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

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

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

30. Индекс и совпадение

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

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

Вы знали? 🔍 Microsoft Excel собрал невероятную глобальную базу пользователей, по оценкам, от 1,1 миллиарда до 1,5 миллиарда люди.

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

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

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

Пример:

Учитывайте следующие данные:

Чтобы ссылаться на C4, начиная с A1, ссылка — это A1, строки — 3, а столбцы — 2:

Станьте экспертом в области аналитики данных с нашей уникальной магистерской программой для аналитиков данныхПрограмма изучения

Функция СУММА со смещением

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

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