ИНДЕКС и ПОИСКПОЗ в нескольких столбцах
Чтобы найти значение путем сопоставления нескольких столбцов, формула массива на основе ИНДЕКС и МАТЧ функции, которые включают MMULT, ТРАНСПОНИРОВАНИЕ и КОЛОНКА сделаю вам одолжение.
Как найти значение, сопоставив несколько столбцов?
Чтобы заполнить соответствующий класс каждого студента как показано в приведенной выше таблице, где информация указана в нескольких столбцах, вы можете сначала использовать уловку функций MMULT, TRANSPOSE и COLUMN для создания матричного массива. Затем функция ПОИСКПОЗ предоставит вам позицию вашего искомого значения, которое будет передано в ИНДЕКС для извлечения искомого значения в массиве.
Общий синтаксис
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого вы хотите, чтобы формула возвращала информацию о классе. Здесь имеется в виду классовая линейка.
- искомое_значение: Значение, которое формула использовала для поиска соответствующей информации о классе. Здесь имеется в виду данное имя.
- искомый_массив: Диапазон ячеек, в которых искомое_значение указан; Диапазон значений для сравнения с искомое_значение. Здесь имеется в виду диапазон имен.
- тип_соответствия 0: Заставляет MATCH найти первое значение, которое точно равно искомое_значение.
Для того, чтобы найти класс Джимми, скопируйте или введите формулу ниже в ячейку H5 и нажмите Ctrl + Shift + Enter чтобы получить результат:
= ИНДЕКС (5 млрд долларов: 7 млрд долларов, (ПОИСКПОЗ (1; ММУЛЬТ (- (5 канадских долларов: 7 канадских долларов=G5), ТРАНСПОРТ (КОЛОНКА (5 канадских долларов: 7 канадских долларов) ^ 0)), 0)))
√ Примечание. Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что имена и диапазоны классов в формуле не изменятся при перемещении или копировании формулы в другие ячейки. Обратите внимание, что вы не должны добавлять знаки доллара к ссылке на ячейку, которая представляет значение подстановки, поскольку вы хотите, чтобы оно было относительным при копировании в другие ячейки. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.
Пояснение формулы
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- - ($ C $ 5: $ E $ 7 = G5): Этот сегмент проверяет каждое значение в диапазоне 5 канадских долларов: 7 канадских долларов если они равны значению в ячейке G5, и генерирует массив ИСТИНА и ЛОЖЬ, как это:
{ИСТИНА, ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ, ЛОЖЬ}.
Затем двойной отрицательный результат преобразует ИСТИНА и ЛОЖЬ в 1 и 0, чтобы получить такой массив:
{1,0,0; 0,0,0; 0,0,0}. - COLUMN (5 $ C $: 7 E $): Функция СТОЛБЕЦ возвращает номера столбцов для диапазона 5 канадских долларов: 7 канадских долларов в таком массиве: 3,4,5 {}.
- ТРАНСПОРТ (COLUMN (5 $ C $: 7 E $)^ 0) = ТРАНСПОРТ (3,4,5 {}^ 0): После увеличения степени до 0 все числа в массиве {3,4,5} будут преобразованы в 1: {1,1,1}. Затем функция TRANSPOSE преобразует массив столбцов в массив строк следующим образом: {1; 1; 1}.
- MMULT (- ($ C $ 5: $ E $ 7 = G5),ТРАНСПОРТ (COLUMN (5 $ C $: 7 E $)^ 0)) = MMULT ({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Функция MMULT возвращает матричное произведение двух массивов следующим образом: {1; 0; 0}.
- ПОИСКПОЗ (1,MMULT (- ($ C $ 5: $ E $ 7 = G5),ТРАНСПОРТ (COLUMN (5 $ C $: 7 E $)^ 0)), 0) = ПОИСКПОЗ (1,{1; 0; 0}, 0): Match_type 0 заставляет функцию ПОИСКПОЗ возвращать позицию первого совпадения 1 в массиве {1; 0; 0}, Которая является 1.
- ПОКАЗАТЕЛЬ(5 млрд долларов: 7 млрд долларов,(ПОИСКПОЗ (1,MMULT (- ($ C $ 5: $ E $ 7 = G5),ТРАНСПОРТ (COLUMN (5 $ C $: 7 E $)^ 0)), 0))) = ИНДЕКС (5 млрд долларов: 7 млрд долларов,1): Функция ИНДЕКС возвращает 1st значение в диапазоне классов 5 млрд долларов: 7 млрд долларов, Которая является A.
Чтобы легко найти значение по нескольким столбцам, вы также можете использовать нашу профессиональную надстройку Excel. Kutools для Excel. См. Инструкцию здесь, чтобы выполнить миссию.
Связанные функции
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Функция Excel MMULT возвращает матричное произведение двух массивов. Результат массива имеет то же количество строк, что и array1, и такое же количество столбцов, как array2.
Функция Excel TRANSPOSE изменяет ориентацию диапазона или массива. Например, он может повернуть таблицу, которая расположена горизонтально по строкам, в вертикально по столбцам или наоборот.
Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки. Например, формула = COLUMN (BD) возвращает 56.
Связанные формулы
Поиск по нескольким критериям с помощью INDEX и MATCH
При работе с большой базой данных в электронной таблице Excel с несколькими столбцами и заголовками строк всегда сложно найти что-то, что соответствует нескольким критериям. В этом случае вы можете использовать формулу массива с функциями ИНДЕКС и ПОИСКПОЗ.
Двусторонний поиск с помощью INDEX и MATCH
Для поиска чего-либо в строках и столбцах в Excel или, как мы говорим, для поиска значения на пересечении конкретной строки и столбца, мы можем использовать функции ИНДЕКС и ПОИСКПОЗ.
Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
