Note: The other languages of the website are Google-translated. Back to English
English English

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

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

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

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

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

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

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

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

  • диапазон_возврата: Диапазон, из которого вы хотите, чтобы формула возвращала информацию о классе. Здесь имеется в виду классовая линейка.
  • искомое_значение: Значение, которое формула использовала для поиска соответствующей информации о классе. Здесь имеется в виду данное имя.
  • искомый_массив: Диапазон ячеек, в которых искомое_значение указан; Диапазон значений для сравнения с искомое_значение. Здесь имеется в виду диапазон имен.
  • match_type 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 - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места