Сложные формулы в Excel (Содержание)
- Введение в сложные формулы в Excel
- Выберите функцию Служить функцией поиска.
- VLOOKUP С ВЫБРАТЬ
- ЕСЛИ С И И ИЛИ Функцией
Введение в сложные формулы в Excel
В Excel есть много полезных формул, у нас есть категории «Финансовые», «Логические», «Текст», «Дата и время», «Поиск и ссылки», «Математика и триггеры». Каждая отдельная формула достаточно хороша для промежуточных пользователей, где они сталкиваются с прямыми ситуациями, но по мере того, как вы переходите на следующий уровень, ваши навыки должны подниматься, поэтому в этом случае применение более сложных формул в Excel играет жизненно важную роль в решении сложных ситуаций. Помня об этом, в этой статье мы рассмотрим некоторые важные и крайне необходимые сложные формулы. Читай дальше.
Выберите функцию Служить функцией поиска.
Не многие из вас использовали функцию CHOOSE, но это очень полезно, чтобы также выполнять функцию поиска.
Вы можете скачать этот шаблон Excel для сложных формул здесь - шаблон Excel для сложных формул. Например, посмотрите на приведенные ниже данные.
У нас есть данные клиентов ежемесячно под этой таблицей, у нас есть каждая таблица клиентов. В этой таблице в ячейке B13 я создал раскрывающийся список месяца, чтобы при изменении этого раскрытия мы получали сумму за выбранный месяц.
Создайте одну таблицу, как показано ниже.
Примените VLOOKUP к ячейке D13.
Теперь откройте функцию ВЫБЕРИТЕ в ячейке B14.
Выберите номер INDEX в качестве ячейки VLOOKUP и сделайте его абсолютной ссылкой.
Теперь выберите для каждого аргумента номера продаж каждого месяца.
Закройте скобку и нажмите клавишу ввода.
Теперь скопируйте и вставьте формулу в ячейки ниже.
Теперь формула CHOOSE покажет номера выбранного месяца.
VLOOKUP с функцией ВЫБРАТЬ
Мы видели традицию VLOOKUP, но мы можем комбинировать ее и с функцией CHOOSE. Например, посмотрите на данные ниже.
В ячейках с A1 по B11 у нас есть таблица продаж, а в ячейке D2 я создал раскрывающийся список месяцев, а в ячейке E2 нам нужно применить VLOOKUP и получить значение продаж за выбранный месяц. Но проблема применения VLOOKUP заключается в структуре данных, где значение поиска находится справа от столбца результата, поэтому VLOOKUP не может извлечь данные справа налево, в этом случае мы можем использовать функцию CHOOSE.
Чтобы выбрать массив таблиц, нам нужно открыть здесь функцию CHOOSE.
В аргументе Номер индекса в цветке в скобках введите 1 и 2. Теперь выберите первый диапазон в столбце Месяц.
Выберите столбец продаж в качестве второго диапазона.
Упомяните индексный номер столбца как 2 и поиск диапазона как 0.
Таким образом, мы получили результат за апрель месяца из таблицы. Поэтому, когда у вас нет точной структуры данных, мы можем выбрать функцию ВЫБЕРИТЕ.
ЕСЛИ С И И ИЛИ Функцией
Логические функции полны логики для расчета вещи. Посмотрите на приведенные ниже примеры данных.
Из приведенной выше таблицы нам необходимо рассчитать бонус на основе следующих условий.
- Если срок службы превышает 1, 5 года, а отделом является отдел продаж или поддержки, то бонус составляет 1000.
- Если что-то еще, то Бонус составляет 3000.
Для расчета бонуса нам нужно применить IF, AND, & OR вместе. Откройте условие IF в ячейке D2.
Нам нужно протестировать несколько условий, открыть функцию AND.
Теперь следующая логика - это либо Продажи, либо Поддержка, поэтому для этого условия ИЛИ открытого.
Проверьте, является ли название продаж или поддержки.
Таким образом, если все поставленные условия ИСТИНА, то бонус должен быть 10000.
Если поставленные логические тесты ЛОЖЬ, тогда бонус составляет 3000.
Закройте скобку и нажмите клавишу Enter, мы должны получить результат.
Скопируйте и вставьте формулу в ячейки ниже.
Итак, согласно условию мы получили результат. Если срок службы> 1, 5 года, а название - «Продажи» или «Поддержка», мы получим 10000, иначе 3000.
Сложная формула с подстановочными знаками
VLOOKUP требует, чтобы точное значение поиска было сопоставлено для извлечения данных. Это традиционный лозунг, но мы все еще можем получить данные, используя значение поиска частиц. Например, если значение поиска - «VIVO», а в основной таблице - «VIVO Mobile», мы все равно можем сопоставить, используя подстановочные знаки. Сейчас мы увидим один из примеров, ниже приведен пример данных.
У нас есть таблица поиска в столбце A. В столбце C у нас есть значения поиска, эти значения поиска не совсем совпадают со значениями таблицы поиска. Итак, мы увидим, как применять VLOOKUP с использованием групповых символов.
Сначала откройте функцию VLOOKUP в ячейке D1.
Первый аргумент - это значение поиска . Одна из проблем со значением поиска здесь заключается в том, что у нас нет точного соответствия, поэтому нам нужно заключить это значение поиска в звездочку до и после значения поиска.
Здесь мы применили два символа звездочки «*» и C2 & »*». Здесь звездочка указывает на то, что что-либо между подстановочными знаками должно совпадать и возвращать соответствующий результат.
Несмотря на то, что у нас только символ звездочки «Infosys» соответствовал значению в таблице поиска и вернул точный результат как «Infosys Ltd».
Аналогично, в ячейке D6 мы получили значение ошибки как # ЗНАЧЕНИЕ! потому что в справочной таблице нет слова «Минтра».
Рекомендуемые статьи
Это руководство по сложным формулам в Excel. Здесь мы обсуждаем некоторые важные и крайне необходимые сложные формулы с примерами и шаблонами Excel. Вы также можете просмотреть наши другие предлагаемые статьи, чтобы узнать больше -
- ПОИСК Формула в Excel (Советы и хитрости)
- Учебники по функции Excel NORMSINV
- Поле имени и его использование в Excel
- Конкатенация строк в Excel