Искать и получать всю строку
Чтобы найти и получить всю строку данных, сопоставив определенное значение, вы можете использовать ИНДЕКС и МАТЧ функции для создания формулы массива.
Поиск и получение всей строки на основе определенного значения
Суммируйте всю строку на основе определенного значения
Дальнейший анализ всей строки по определенному значению
Поиск и получение всей строки на основе определенного значения
Чтобы получить список продаж, сделанных Джимми согласно приведенной выше таблице, вы можете сначала использовать функцию ПОИСКПОЗ, чтобы вернуть позицию продаж, сделанных Джимми, и которая будет передана в ИНДЕКС для получения значений в позиции.
Общий синтаксис
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, содержащий всю строку, которую вы хотите вернуть. Здесь имеется в виду ассортимент продаж.
- искомое_значение: Значение, которое формула комбинирования использовала для поиска соответствующей информации о продажах. Здесь имеется в виду данный продавец.
- искомый_массив: Диапазон ячеек, в которых должен соответствовать искомое_значение. Здесь имеется в виду диапазон имен.
- тип_соответствия 0: Заставляет MATCH найти первое значение, которое точно равно искомое_значение.
Чтобы получить список продаж, сделанный Джимми, скопируйте или введите формулу ниже в ячейку 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)
- ПОИСКПОЗ (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.
Суммируйте всю строку на основе определенного значения
Теперь, когда у нас есть вся информация о продажах, сделанная Джимми, мы можем годовой объем продаж Джимми, мы можем просто добавить в формулу функцию СУММ, чтобы суммировать все значения продаж из списка.
Общий синтаксис
=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))
- ПОИСКПОЗ (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))
OR
= МАЛЕНЬКИЙ (ИНДЕКС (C5: F11,СООТВЕТСТВИЕ(I5,B5: B11, 0), 0),1)
Связанные функции
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Связанные формулы
Поиск и получение всего столбца
Чтобы найти и получить весь столбец, сопоставив определенное значение, вам помогут формулы ИНДЕКС и ПОИСКПОЗ.
Точное совпадение с ИНДЕКСОМ и ПОИСКПОЗ
Если вам нужно найти информацию, указанную в Excel, о конкретном продукте, фильме или человеке и т. Д., Вы должны хорошо использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Примерное совпадение с ИНДЕКСОМ и ПОИСКПОЗ
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel, чтобы оценить производительность сотрудников, выставить оценки учащихся, рассчитать почтовые расходы на основе веса и т. Д. В этом руководстве мы поговорим о том, как использовать функции ИНДЕКС и ПОИСКПОЗ для получения результаты нам нужны.
Возможно, вы знаете, что можете комбинировать функции ИНДЕКС и ПОИСКПОЗ или использовать функцию ВПР для поиска значений в Excel. Однако при поиске регистр не учитывается. Итак, чтобы выполнить сопоставление с учетом регистра, вы должны воспользоваться функциями EXACT и CHOOSE.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
