Поиск и извлечение Вся строка

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

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

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

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

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

=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)

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

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

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

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

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

Пояснение формулы

=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)

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

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

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

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

=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))

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

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

Пояснение формулы

=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))

• MATCH(I5,B5:B11,0):Параметр тип_сопоставления 0заставляет функцию ПОИСКПОЗ вернуть позицию значения Джимми, находящегося в ячейке I5, в диапазоне B5:B11, что составляет 7, поскольку имя Джимминаходится на 7-й позиции.
• ИНДЕКС()C5:F11,MATCH(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.

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

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

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

=AVERAGE(INDEX())C5:F11,MATCH()I5,B5:B11,0),0))

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

=MIN(INDEX())C5:F11,MATCH()I5,B5:B11,0),0))
ИЛИ
=SMALL(INDEX())C5:F11,MATCH()I5,B5:B11,0),0),1)

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

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