Расширенные формулы Excel (Содержание)
- Введение в расширенные формулы в Excel
- Примеры расширенной формулы в Excel
Введение в расширенные формулы в Excel
Как только вы достигнете среднего уровня в Excel, вам нужно усердно работать, чтобы перейти на продвинутый уровень. Чтобы перейти на продвинутый уровень, вы должны знать о некоторых из часто используемых расширенных формул. В этой статье я расскажу о 10 лучших формулах, которые должны знать ученики Excel. Следуйте этой статье, чтобы узнать и изучить.
Примеры расширенной формулы в Excel
Давайте разберемся, как использовать расширенные формулы в Excel с некоторыми примерами.
Вы можете скачать этот шаблон расширенных формул Excel здесь - шаблон расширенных формул ExcelПример # 1 - Частичная функция VLOOKUP
Вы, должно быть, столкнулись с ситуацией, когда VLOOKUP выдает ошибку, даже если в поисковом значении было небольшое несоответствие. Например, если вы ищете для зарплаты имя Abhishek Sinha и если у вас есть только Abhishek, то VLOOKUP не сможет получить вам данные.
Однако, используя символ звездочки в любом конце значения поиска, мы можем извлечь данные для частичного значения поиска.
В таблице 1 у меня есть полное имя и зарплата, но в таблице 2 у меня есть только частичные имена и мне нужно найти зарплату каждого сотрудника.
Шаг 1: Откройте формулу VLOOKUP в ячейке E3. Перед выбором значения поиска поставьте звездочку (*) по обе стороны от значения поиска.
Шаг 2: Как обычно, вы можете заполнить формулу VLOOKUP прямо сейчас, и мы получим результаты.
После использования приведенной выше формулы результат будет показан ниже.
Эта же формула используется в других клетках.
Пример № 2 - COUNTIFS с символами оператора
Вы, должно быть, использовали функцию COUNTIF & IFS для подсчета вещей в списке. Мы также можем рассчитывать на основе символов оператора, таких как больше чем (>), меньше чем (<) и знак равенства (=).
Теперь взглянем на приведенные ниже данные, например. Если вы хотите подсчитать общее количество счетов по региону ЮГ после даты 10 января 2018 года, как вы рассчитываете?
Пример № 3 - ЕСЛИ условие с условиями И & ИЛИ
Логические функции являются частью нашей повседневной деятельности. Вам нужно освоить их, чтобы перейти на следующий уровень. Если вы рассчитываете бонус на основе нескольких условий, то для выполнения работы вам нужно вставить условие И или ИЛИ в условие ЕСЛИ.
Предположим, вам нужно рассчитать сумму бонуса для каждого отдела, исходя из того, с каким отделом и годами службы вам необходимы эти функции. На основании приведенных ниже критериев нам необходимо рассчитать бонус.
Если срок службы превышает 4 года, а отделом является отдел продаж или поддержки, бонус составляет 50000, или же бонус составляет 25000.
Примените приведенную ниже формулу, чтобы получить сумму бонуса.
После использования вышеприведенной формулы вывод показан ниже.
Та же формула применяется в ячейке от E3 до E9.
Пример # 4 - функция TEXT, чтобы сделать вас динамичным
Допустим, вы ведете ежедневную таблицу продаж, и вам нужно обновлять таблицу каждый день. В начале таблицы у вас есть один заголовок, который гласит «Консолидированные данные о продажах от ДД-ММ-ГГГГ до ДД-ММ-ГГГГ». Как и когда таблица обновляется, вам нужно изменить дату заголовка. Разве это не разочаровывающая задача делать одно и то же снова и снова? Мы можем сделать этот заголовок динамическим, используя функции TETX, MIN и Max вместе с амперсандом символа оператора сцепления (&).
Пример № 5 - INDEX + MATCH + MAX, чтобы найти самого высокого продавца
У вас есть список менеджеров по продажам и продаж, которые они совершили против их имени. Как вы скажете, кто лучший или самый высокий продавец в списке? Конечно, у нас есть несколько других методов, чтобы определить результат, но эта функция может вернуть самого высокого продавца из партии.
Применить ниже функцию, чтобы получить имя самого высокого продавца.
После использования вышеприведенной формулы вывод показан ниже.
Пример # 6 - Получить количество уникальных значений из списка
Если у вас много повторяющихся значений, и вам необходимо указать, сколько уникальных значений содержится в этом, как вы говорите? Вы можете сказать, удалив дублирующееся значение, но это не динамический способ подсчета уникального значения.
Используя эту функцию массива, мы можем узнать уникальные значения из лота.
Примените приведенную ниже формулу, чтобы получить список уникальных значений.
Пример №7. Использование именованного диапазона для динамического раскрывающегося списка
Если вы часто работаете с раскрывающимся списком и обновляете его до даты, вам необходимо вернуться к диапазону исходных списков, чтобы удалить или добавить значения. После этого вам необходимо вернуться в выпадающие ячейки и снова обновить диапазон раскрывающегося списка.
Однако, если вы можете создать именованный диапазон для вашего раскрывающегося списка, вы можете сделать раскрывающийся список динамическим и актуальным.
Создайте диапазон имен, как показано на рисунке ниже.
Теперь перейдите в раскрывающуюся ячейку и откройте раскрывающееся диалоговое окно.
В источнике нажмите клавишу F3, он покажет все определенные имена, выберите имя вашего выпадающего списка.
Хорошо, раскрывающийся список готов, и он автоматически обновит значения при изменении диапазона раскрывающегося списка.
Пример № 8 - избавление от значений ошибок с использованием функции IFERROR
Я уверен, что вы столкнулись с ошибками при работе с VLOOKUP, Отдел расчетов. Обработка этих значений ошибок является утомительной задачей. Но мы можем избавиться от этих значений ошибок, используя функцию IFERROR в формуле.
После использования вышеприведенной формулы вывод показан ниже.
Та же формула, что и в других клетках.
Пример № 9 - Использование функции PMT для создания собственной диаграммы EMI
В настоящее время опция EMI не является странной вещью для всех нас. В Excel мы можем оценить нашу собственную диаграмму EMI, используя функцию PMT. Выполните следующие шаги, чтобы создать свой собственный график.
Примените приведенную ниже формулу в ячейке B4, чтобы получить величину EMI.
Пример # 10 - INDEX + MATCH как альтернатива функции VLOOKUP
Я надеюсь, что вы знаете об ограничении функции VLOOKUP. Одним из основных ограничений является то, что VLOOKUP может получать данные слева направо, а не справа налево. Не все данные времени хорошо организованы и готовы к использованию. Часто столбец значений, который мы ищем, находится слева от значения поиска, поэтому VLOOKUP не помогает в этих случаях.
Комбинация функции INDEX + MATCH служит альтернативой функции VLOOKUP.
После использования приведенной выше формулы результат будет показан ниже.
Эта же формула используется в других клетках.
На основе идентификатора продукта нам нужно извлечь значения продаж. В основной таблице идентификатор продукта находится справа от столбца продаж. Таким образом, VLOOKUP не может получить данные. Используйте приведенную ниже формулу для извлечения данных.
Рекомендуемые статьи
Это руководство по расширенным формулам в Excel. Здесь мы обсудим, как использовать расширенные формулы в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
- Как использовать функцию Excel INDEX?
- Расширенный Excel | Функция базы данных
- Примеры по формуле TRIM в Excel
- Руководство по Excel OFFSET Formula