Как использовать формулы Excel

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

Формула ИНДЕКС/ПОИСКПОЗ может помочь вам быстро найти точки данных без необходимости искать их вручную и рисковать ошибиться.

Загрузите 10 шаблонов Excel для маркетологов [бесплатный комплект]

Содержание

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

Понимание функций ИНДЕКС и ПОИСКПОЗ по отдельности

Прежде чем вы сможете понять, как использовать ИНДЕКС и формулу сопоставления, важно знать, как работает каждая функция сама по себе. Это внесет некоторую ясность в то, как они работают вместе после объединения.

Функция ИНДЕКС возвращает значение или ссылку на значение в таблице или диапазоне на основе указанных вами строк и столбцов. Думайте об этой функции как о GPS — она помогает вам находить данные в документе, но сначала вам нужно сузить область поиска, используя строки и столбцы.

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

Например, предположим, что диапазон A1:A4 содержит значения 15, 28, 49, 90. Вы хотите знать, как число «49» относится ко всем значениям в диапазоне. Вы должны написать формулу =ПОИСКПОЗ(49,A1:A4,0), и она вернет число 3, потому что это третье число в диапазоне. 0 в формуле означает «точное совпадение».

Теперь, когда мы разобрались с основами, давайте рассмотрим, как объединить формулу и использовать ее для нескольких критериев.

Как использовать формулы ИНДЕКС и ПОИСКПОЗ с несколькими критериями

Формула для формулы ИНДЕКС/ПОИСКПОЗ выглядит следующим образом:

Вот как каждая функция работает вместе: Match находит значение и сообщает его местоположение. Затем он передает эту информацию функции ИНДЕКС, которая превращает эту информацию в результат.

Чтобы увидеть это в действии, давайте воспользуемся примером.

На этом листе Excel представлен маркетинговый бюджет для двух категорий: события и корпоративные подарки. Есть четыре цели: связи с общественностью (PR), празднование, командная прогулка и ребрендинг. Лист также включает в себя определенный бюджет и фактические расходы по каждой категории.

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

Допустим, вы хотите узнать дисперсию для мероприятия, целью которого было празднование с бюджетом в 10 000 – вот как вы это сделаете.

Во-первых, обратите внимание на номера строк и столбцов. Ответ, который вы ищете, будет в I8. Вот как будет выглядеть формула:

Давайте разберемся, как вы туда доберетесь.

1. Создайте отдельный раздел, чтобы написать свои критерии.

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

2. Начните с ИНДЕКС.

Формула начинается с вашего GPS, который является функцией ИНДЕКС. Вы ищете дисперсию, поэтому выбираете строки с E4 по E9, так как именно там будет ответ.

3. Добавьте свои диапазоны.

Чем больше у вас столбцов, тем больше диапазонов вам нужно будет добавить, чтобы сузить результаты.

Напоминаем, что вы ищете отклонение для мероприятия с бюджетом в 10 000 долл . США и целью празднования . Это означает, что вам придется сообщить Excel, какие строки содержат

Начиная с критерия «событие», вы найдете его первым в I4, а его диапазон расположен в столбце A между строками 4 и 9.

Проделайте тот же процесс для «празднования» — оно находится в I5, а его диапазон — B4 и B9. Наконец, «10 000 долларов» находятся в I6 с диапазоном от C4 до C9.

Последний шаг — добавить 0, что означает, что вы ищете точное совпадение.

Вот как вы получаете эту окончательную формулу: