Поиск и извлечение всего столбца
Чтобы найти и извлечь весь столбец, сопоставляя определенное значение, формула INDEX и MATCH окажет вам услугу.
Поиск и извлечение всего столбца на основе определенного значения
Суммирование всего столбца на основе определенного значения
Дальнейший анализ всего столбца на основе определенного значения
Поиск и извлечение всего столбца на основе определенного значения
Чтобы получить список продаж за второй квартал согласно таблице выше, вы можете сначала использовать функцию MATCH для возврата позиции продаж второго квартала, которая затем будет передана в INDEX для получения значений на этой позиции.
Общий синтаксис
=INDEX(диапазон_возврата,0,MATCH(значение_поиска,массив_поиска,0))
√ Примечание: Это формула массива, для которой требуется нажатие Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого комбинированная формула должна вернуть список продаж второго квартала. Здесь имеется в виду диапазон продаж.
- значение_поиска: Значение, которое комбинированная формула использует для поиска соответствующей информации о продажах. Здесь имеется в виду заданный квартал.
- массив_поиска: Диапазон ячеек, где нужно найти значение_поиска. Здесь имеются в виду заголовки кварталов.
- тип_совпадения 0: Заставляет MATCH найти первое значение, точно равное значению_поиска.
Чтобы получить список продаж второго квартала, скопируйте или введите формулу ниже в ячейку I6, нажмите Ctrl + Shift + Enter, а затем дважды щелкните по ячейке и нажмите F9, чтобы получить результат:
=INDEX(C5:F11,0,MATCH("Q2",C4:F4,0))
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
Объяснение формулы
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- MATCH(I5,C4:F4,0): Тип_совпадения 0 заставляет функцию MATCH вернуть позицию Q2, значения в I5, в диапазоне C4:F4, что равно 2.
- INDEX(C5:F11C5:F11,0,MATCH(I5,C4:F4,0)) = INDEX(C5:F11C5:F11,0,2): Функция INDEX возвращает все значения во втором столбце диапазона C5:F11 в виде массива следующего вида: {7865;4322;8534;5463;3252;7683;3654}. Обратите внимание, чтобы сделать массив видимым в Excel, вы должны дважды щелкнуть по ячейке, куда вы ввели формулу, а затем нажать F9.
Суммирование всего столбца на основе определенного значения
Теперь, когда у нас есть список продаж, подсчитать общий объем продаж за второй квартал будет легко. Все, что нам нужно сделать, это добавить функцию SUM к формуле, чтобы суммировать все значения продаж из списка.
Общий синтаксис
=SUM(INDEX(диапазон_возврата,0,MATCH(значение_поиска,массив_поиска,0)))
В данном примере, чтобы получить общий объем продаж за второй квартал, скопируйте или введите формулу ниже в ячейку I8 и нажмите Enter, чтобы получить результат:
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
Объяснение формулы
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- MATCH(I5,C4:F4,0): Тип_совпадения 0 заставляет функцию MATCH вернуть позицию Q2, значения в I5, в диапазоне C4:F4, что равно 2.
- INDEX(INDEX(C5:F11C5:F11,0,,0,MATCH(I5,C4:F4,0))) = INDEX(INDEX(C5:F11C5:F11,0,,0,2)): Функция INDEX возвращает все значения во втором столбце диапазона C5:F11 в виде массива следующего вида: {7865;4322;8534;5463;3252;7683;3654}.
- SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0))) = SUM({7865;4322;8534;5463;3252;7683;3654}): Функция SUM суммирует все значения в массиве, а затем получает общий объем продаж за второй квартал, $40,773.
Дальнейший анализ всего столбца на основе определенного значения
Для дополнительной обработки списка продаж за второй квартал вы можете просто добавить другие функции, такие как SUM, AVERAGE, MAX, MIN, LARGE и т.д., к формуле.
Например, чтобы получить средний объем продаж за второй квартал, вы можете использовать формулу:
=AVERAGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
Чтобы узнать наибольшие продажи за второй квартал, используйте одну из формул ниже:
=MAX(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
ИЛИ
=LARGE(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)),1)
Связанные функции
Функция INDEX в Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция MATCH в Excel ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
Связанные формулы
Поиск и извлечение всей строки
Чтобы найти и извлечь всю строку данных, сопоставляя определенное значение, вы можете использовать функции INDEX и MATCH для создания формулы массива.
Точное совпадение с INDEX и MATCH
Если вам нужно найти информацию, указанную в Excel, о конкретном продукте, фильме или человеке и т.д., вам следует эффективно использовать комбинацию функций INDEX и MATCH.
Приблизительное совпадение с INDEX и MATCH
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel для оценки производительности сотрудников, выставления оценок студентам, расчета стоимости доставки на основе веса и т.д. В этом руководстве мы расскажем, как использовать функции INDEX и MATCH для получения нужных результатов.
Вы можете знать, что можно комбинировать функции INDEX и MATCH или использовать функцию VLOOKUP для поиска значений в Excel. Однако эти поиски не чувствительны к регистру. Поэтому для выполнения поиска с учетом регистра следует воспользоваться функциями EXACT и CHOOSE.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.