Как найти номер индекса столбца из таблицы в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: lookup-formulas

В этой статье мы узнаем, как извлечь индекс столбца из таблицы в Excel.

Сценарий:

Часто при работе с длинными разрозненными данными нам необходимо сначала очистить данные, прежде чем работать с ними. Это требует времени, а вы просто хотели извлечь несколько запросов. Для извлечения данных обычно используется ссылка link: / formulas-and-functions-Introduction-of-vlookup-function [функция VLOOKUP]. Но когда у нас есть длинные данные с множеством полей столбцов, они становятся беспорядочными, потому что функция ВПР требует правильного индекса столбца как числа, иначе формула вернет ошибку. Прямо здесь, ниже приводится формула объяснения такого рода проблем.

Как решить проблему?

Для этого мы будем использовать link: / lookup-formulas-excel-match-function [MATCH function]. Если вы не слышали об этой функции, привыкните к ней. Это функция поиска в Excel, которая возвращает индекс значения поиска в массиве. Здесь нам нужно получить порядковый номер имени столбца. Затем мы перейдем к следующему шагу Как искать значения в таблицах с помощью функции ПОИСКПОЗ. Ниже приведена общая формула

Общая формула:

=MATCH(column_name,

table_header, 0)

имя_столбца: значение поиска table_header: массив заголовков таблицы 0: поиск точного совпадения

Пример:

Все это может сбивать с толку. Давайте разберемся с формулой с объяснением и примером. Здесь у нас есть таблица данных на листе 1 ($ A $ 1: $ U $ 9995). Итак, у нас есть заголовок таблицы как A1: U1 (первая строка таблицы).

Давайте посмотрим на формулу ниже, которую можно применить к таблице.

Используйте формулу

=MATCH(C4,Sheet1!$A$1:$U$1,0)

Пояснение:

Функция ПОИСКПОЗ ищет значение в ячейке C4 «Идентификатор заказа»
Sheet1! $ A $ 1: $ U $ 1 — аргумент массива поиска.
Аргумент 0 используется для поиска точного соответствия.

Как вы можете видеть, индекс столбца Order ID в таблице — второй. Но использовать заголовок таблицы в качестве полного сокращения очень раздражает, поэтому мы используем именованный диапазон для массива заголовков таблицы. Узнайте больше о link: / excel-range-name-all-about-excel-named-range-excel-range-name [именованные диапазоны здесь]. Именованный диапазон, используемый для заголовка таблицы (Sheet1! $ A $ 1: $ U $ 1), является «заголовком». Воспользуйтесь формулой.

=MATCH(C4,header,0)

Приведенный выше снимок объясняет две вещи. Во-первых, индекс состояния столбца в таблице равен 11, а во-вторых, именованный диапазон «заголовок» работает нормально. Скопируйте формулу для оставшихся имен столбцов, используя Ctrl + D или перетащив от правого нижнего края используемую ячейку.

Здесь у нас есть все столбцы Index. Теперь мы можем использовать для ввода в функцию ВПР, как показано ниже.

Индекс ПОИСКПОЗ в функции ВПР:

Теперь у нас есть решение, как получить индекс столбца таблицы.

Мы можем использовать эту формулу в качестве входных данных для функции ВПР. Например, нам нужно название продукта, приобретенного клиентом «Пит Криз».

Используйте формулу:

=VLOOKUP(D10,Table,MATCH(E9,header,0),0)

Примечание: убедитесь, что значение поиска в D10 (Пит Криз) должно быть в первом столбце таблицы.

Как видите, формула возвращает название продукта из имени клиента в таблице. Обычно мы не используем ПОИСКПОЗ с функцией ВПР, потому что искомое значение должно быть в первом столбце, что редко случается. Поэтому мы используем комбинацию link: / lookup-formulas-excel-index-function [INDEX]

и link: / lookup-formulas-excel-match-function [MATCH]

функция. Узнайте больше о link: / lookup-formulas-use-index-and-match-to-lookup-value [Как найти значение с помощью функции ИНДЕКС и ПОИСКПОЗ]. Вот все заметки по использованию формулы.

Примечания:

Формула работает как для текста, так и для чисел.
Функция возвращает ошибку #NA, если аргумент массива поиска функции ПОИСКПОЗ не имеет той же длины, что и массив таблицы.
Формула возвращает ошибку, если lookup_value не соответствует значению в таблице lookup_array.
Функция соответствует точному значению, поскольку аргумент типа соответствия функции ПОИСКПОЗ равен 0.
Используйте аргумент -1 для меньшего, 0 для точного совпадения и 1 для большего, чем поисковый запрос.
Значения поиска могут быть заданы как ссылка на ячейку или напрямую с использованием символа кавычки («) в формуле в качестве аргументов.

Надеюсь, вы поняли, как извлечь индекс столбца из таблицы в Excel. Прочтите больше статей о значении поиска в Excel & Excel 2019 работает здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или вводить новшества в нашу работу и делать ее лучше для вас. Напишите нам на [email protected].