📌 ИНДЕКС и ПОИСКПОЗ в Excel: преимущества и практические примеры

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

Сегодня рассмотрим эти функции подробнее и разберем их преимуществ.

ИНДЕКС и ПОИСКПОЗ в Excel: преимущества и практические примеры

⏩ ИНДЕКС

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

Может быть использована в одной из двух форм:

🔹 Ссылочная форма 🔽

= ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) Ссылочная форма возвращает ссылку на ячейку, расположенную на пересечении указанной строки и указанного столбца. Аргументы: ◾ ссылка — обязательный аргумент. Ссылка на один или несколько диапазонов ячеек. ◾ Номер_строки (обязательный аргумент): номер строки в диапазоне, заданном аргументом "ссылка", из которого требуется возвратить ссылку. ◾ Номер_столбца (необязательный аргумент): номер столбца в диапазоне, заданном аргументом "ссылка", из которого требуется возвратить ссылку. ◾ номер_области (необязательный) : выбирает диапазон в ссылке, из которого возвращается пересечение строка-столбец

🔹 Форма массива 🔽

=ИНДЕКС(массив; номер_строки; [номер_столбца]) Форма массива возвращает значение элемента в таблице или массиве, выбранное по индексам номеров строк и столбцов. Аргументы: ◾ массив (обязательный аргумент): диапазон ячеек или константа массива. ◾ Номер_строки (обязательный): выбирает строку в массиве, из которой требуется возвратить значение. Если номер_строки опущен, требуется номер_столбца. ◾ Номер_столбца (необязательный аргумент) выбирает столбец в массиве, из которого требуется возвратить значение. Если номер_столбца опущен, требуется номер_строки.

⏩ ПОИСКПОЗ

Ищет указанное значение и возвращает его позицию в диапазоне:

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления]) ◾ Искомое_значение (обязательный аргумент): Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение. ◾ Просматриваемый_массив (обязательный аргумент): диапазон ячеек, в которых производится поиск. ◾ Тип_сопоставления (необязательный аргумент) Число -1, 0 или 1. * Аргумент тип_сопоставления указывает, каким образом искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив (по умолчанию в качестве этого аргумента используется значение 1).

⏩ Совместное использование ИНДЕКС() и ПОИСКПОЗ() на примере

➡️ Задача 1: Определить для указанного месяца (ячейка G2) и выбранного набора (ячейка G3) сумму продажи в ячейке G8:

Пример: Совместное применение функций ИНДЕКС и ПОИСКПОЗ В данном примере мы используем функцию ИНДЕКС для извлечения значения из диапазона C8:E19. При этом мы указываем номер строки и столбца, используя функцию ПОИСКПОЗ для поиска позиций значений H8 и H9

• Позиция месяца: =ПОИСКПОЗ(H8;B8:B19;0)

ПОИСКПОЗ(H8;B8:B19;0) ищет позицию значения H8 в столбце B8:B19 и возвращает его. Это позволяет определить номер строки, из которой требуется извлечь значение.

• Позиция набора: =ПОИСКПОЗ(H9;C7:E7;0)

Аналогично, функция ПОИСКПОЗ(H9;C7:E7;0) ищет позицию значения H9 в строке C7:E7 и возвращает его. Это позволяет определить номер столбца, из которого требуется извлечь значение.

• Сумма продаж

• Функция ИНДЕКС использует полученные номера строки и столбца для извлечения соответствующего значения из диапазона C8:E19:

➡️ Задача 2: Задать список уникальных значений

Для решения данной задачи применим следующую формулу:
{=ЕСЛИОШИБКА(ИНДЕКС(Заказчик;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик));СЧЁТЕСЛИ(Заказчик;"<"& Заказчик);0));"")}

Как задать список уникальных значений используя функции ИНДЕКС и ПОИСКПОЗ Задаеми формулу {=ЕСЛИОШИБКА(ИНДЕКС(Заказчик;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик));СЧЁТЕСЛИ(Заказчик;"<"& Заказчик);0));"")} в ячейке A21 и протягиваем вниз. Список уникальных значений готоыв!

🔔 Обратите внимание, что данная формула является формулой массива

Формула массива требует завершения с использованием клавиш Ctrl+Shift+Enter

📝 Пояснение к формуле:

• СЧЁТЕСЛИ(A$21:A21; Заказчик)

Эта часть подсчитывает количество вхождений значения "Заказчик" в диапазоне от A21 до текущей строки. Ссылка на диапазон A$21:A21 изменяется по мере заполнения формулы вниз * Заказчик - это именованный диапазон

• СЧЁТЕСЛИ(Заказчик;"<"& Заказчик)

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

• СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик))

Здесь суммируются результаты подсчета из первого шага. Это служит для определения текущего положения значения "Заказчик" в диапазоне

• ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик));СЧЁТЕСЛИ(Заказчик;"<"& Заказчик);0)

Эта часть выполняет поиск позиции текущего значения "Заказчик" в массиве значений "Заказчик". Параметр 0 означает, что будет найдена точная позиция

• ИНДЕКС(Заказчик;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик));СЧЁТЕСЛИ(Заказчик;"<"& Заказчик);0)) выбирает значение из массива "Заказчик" на основе позиции, которая была определена с помощью функции ПОИСКПОЗ:

ИНДЕКС(Заказчик;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(A$21:A21; Заказчик));СЧЁТЕСЛИ(Заказчик;"<"& Заказчик);0))

• ЕСЛИОШИБКА() используется для обработки возможных ошибок:

Функция ЕСЛИОШИБКА

Еще примеры:

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

🔘 Гибкость и масштабируемость

Одним из главных преимуществ использования ИНДЕКС() + ПОИСКПОЗ() является их гибкость и способность работать с большими массивами данных.

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

🔘 Универсальность

ИНДЕКС+ПОИСКПОЗ могут быть использованы в различных сценариях и задачах.

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

🔘 Улучшение эффективности расчетов

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

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

Наша команда очень заинтересована в создании качественного и полезного контента. Поэтому, если Вам понравился этот урок, не забудьте поблагодарить автора лайком 👍. Если у Вас есть вопросы или своё решение, не стесняйтесь оставить комментарий 💬. Нужно решение для другой проблемы? Пожалуйста, напишите свой вопрос в комментариях 💬, и мы обязательно рассмотрим его в ближайшей теме обзора. Мы ценим Вашу обратную связь 💝, так как это помогает нам оценить, насколько данный материал был полезен для Вас