Перейти к основному содержанию

ИНДЕКС и ПОИСКПОЗ в нескольких столбцах

Чтобы найти значение путем сопоставления нескольких столбцов, формула массива на основе ИНДЕКС и МАТЧ функции, которые включают MMULT, ТРАНСПОНИРОВАНИЕ и КОЛОНКА сделаю вам одолжение.

индекс соответствует нескольким столбцам 1

Как найти значение, сопоставив несколько столбцов?

Чтобы заполнить соответствующий класс каждого студента как показано в приведенной выше таблице, где информация указана в нескольких столбцах, вы можете сначала использовать уловку функций 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)))

√ Примечание. Знаки доллара ($) выше указывают на абсолютные ссылки, что означает, что имена и диапазоны классов в формуле не изменятся при перемещении или копировании формулы в другие ячейки. Обратите внимание, что вы не должны добавлять знаки доллара к ссылке на ячейку, которая представляет значение подстановки, поскольку вы хотите, чтобы оно было относительным при копировании в другие ячейки. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.

индекс соответствует нескольким столбцам 2

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

=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

Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.

Функция ММУМНОГ в Excel

Функция Excel MMULT возвращает матричное произведение двух массивов. Результат массива имеет то же количество строк, что и array1, и такое же количество столбцов, как array2.

Функция ТРАНСПОРТ в Excel

Функция Excel TRANSPOSE изменяет ориентацию диапазона или массива. Например, он может повернуть таблицу, которая расположена горизонтально по строкам, в вертикально по столбцам или наоборот.

СТОЛБЕЦ в Excel

Функция COLUMN возвращает номер столбца, в котором отображается формула, или номер столбца для данной ссылки. Например, формула = COLUMN (BD) возвращает 56.


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

Поиск по нескольким критериям с помощью INDEX и MATCH

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

Двусторонний поиск с помощью INDEX и MATCH

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

Найти значение ближайшего соответствия по нескольким критериям

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


Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations