Поиск и извлечение всей строки

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

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

Поиск и извлечение всей строки на основе определенного значения

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

Общий синтаксис

=ИНДЕКС(диапазон_возврата;ПОИСКПОЗ(искомое_значение;диапазон_поиска;0);0)

√ Примечание: Это формула массива, которая требует ввода с помощью Ctrl + Shift + Enter.

Чтобы получить список продаж, сделанных Джимми, скопируйте или введите формулу ниже в ячейку I6, нажмите Ctrl + Shift + Enter, а затем дважды щёлкните по ячейке и нажмите F9, чтобы получить результат:

=ИНДЕКС(C5:F11;ПОИСКПОЗ("Джимми";B5:B11;0);0)

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

=ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0)

Объяснение формулы

=ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0)

• ПОИСКПОЗ(I5;B5:B11;0): Тип_совпадения 0 заставляет функцию ПОИСКПОЗ вернуть позицию Джимми, значение в I5, в диапазоне B5:B11, что равно 7, поскольку Джимми находится на 7-й позиции.
• ИНДЕКС(C5:F11C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0) = ИНДЕКС(C5:F11C5:F11;7;0): Функция ИНДЕКС возвращает все значения в 7-й строке диапазона C5:F11 в виде массива следующего вида: {6678,3654,3278,8398}. Обратите внимание, чтобы сделать массив видимым в Excel, вы должны дважды щёлкнуть по ячейке, где была введена формула, и затем нажать F9.

Суммирование всей строки на основе определенного значения

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

Общий синтаксис

=СУММ(ИНДЕКС(диапазон_возврата;ПОИСКПОЗ(искомое_значение;диапазон_поиска;0);0))

В этом примере, чтобы получить годовой объем продаж, выполненных Джимми, скопируйте или введите формулу ниже в ячейку I7, и нажмите Enter, чтобы получить результат:

=СУММ(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0))

Объяснение формулы

=СУММ(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0))

• ПОИСКПОЗ(I5;B5:B11;0): Тип_совпадения 0 заставляет функцию ПОИСКПОЗ вернуть позицию Джимми, значение в I5, в диапазоне B5:B11, что равно 7, поскольку Джимми находится на 7-й позиции.
• ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0) = ИНДЕКС(C5:F11;7;0): Функция ИНДЕКС возвращает все значения в 7-й строке диапазона C5:F11 в виде массива следующего вида: {6678,3654,3278,8398}. Обратите внимание, чтобы сделать массив видимым в Excel, вы должны дважды щёлкнуть по ячейке, где была введена формула, и затем нажать F9.
• СУММ(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0));0) = СУММ({6678,3654,3278,8398}): Функция СУММ суммирует все значения в массиве, а затем получает годовой объем продаж, выполненных Джимми, $22,008.

Дальнейший анализ всей строки на основе определенного значения

Для дополнительной обработки продаж, выполненных Джимми, вы можете просто добавить другие функции, такие как СУММ, СРЗНАЧ, МАКС, МИН, НАИБОЛЬШИЙ и т.д., в формулу.

Например, чтобы получить среднее значение продаж Джимми за каждый квартал, вы можете использовать формулу:

=СРЗНАЧ(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0))

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

=МИН(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0))
ИЛИ
=НАИМЕНЬШИЙ(ИНДЕКС(C5:F11;ПОИСКПОЗ(I5;B5:B11;0);0);1)

Связанные функции

Связанные формулы