Поиск и извлечение Весь столбец

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

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

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

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

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

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

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

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

=ИНДЕКС()C5:F11,0,ПОИСКПОЗ()"Q2",C4:F4,0))

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

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

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

=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))

• MATCH(I5,C4:F4,0): Параметр тип_сопоставления 0 заставляет функцию ПОИСКПОЗ вернуть позицию значения Q2, находящегося в ячейке I5, в диапазоне C4:F4, которая равна 2.
• ИНДЕКС()C5:F11,0,MATCH(I5,C4:F4,0)) = ИНДЕКС(C5:F11,0,2):Функция ИНДЕКС возвращает все значения из 2-го столбца диапазона C5:F11в виде массива:{7865;4322;8534;5463;3252;7683;3654}.Обратите внимание: чтобы сделать массив видимым в Excel, дважды щёлкните по ячейке, в которую ввели формулу, а затем нажмите F9.

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

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

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

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

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

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

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

=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))

• MATCH(I5,C4:F4,0):Параметр тип_сопоставления 0заставляет функцию ПОИСКПОЗ вернуть позицию значения Q2, находящегося в ячейке I5, в диапазоне C4:F4, которая равна 2.
• ИНДЕКС()C5:F11,0,MATCH(I5,C4:F4,0))=C5:F112:Функция ИНДЕКС возвращает все значения из 2-го столбца диапазона C5:F11 в виде массива: {7865;4322;8534;5463;3252;7683;3654}.
• СУММ()ИНДЕКС()C5:F11,0,ПОИСКПОЗ(I5,C4:F4,0))) = СУММ({7865;4322;8534;5463;3252;7683;3654}):Функция СУММ суммирует все значения в массиве и таким образом получает общий объём продаж за второй квартал, равный $40 773.

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

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

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

=СРЗНАЧ(ИНДЕКС())C5:F11,0,ПОИСКПОЗ()I5,C4:F4,0)))

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

=МАКС(ИНДЕКС())C5:F11,0,ПОИСКПОЗ()I5,C4:F4,0)))
ИЛИ
=НАИБОЛЬШИЙ(ИНДЕКС())C5:F11,0,ПОИСКПОЗ()I5,C4:F4,0)),1)

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

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