Как восстановить значение из эталонной сетки в соответствии со значениями индекса Сохранить в Excel
В этой статье мы узнаем, как получить значение из справочной сетки в соответствии со значениями индекса. Сохранить в Excel
Сценарий:
Часто при работе с длинными неразрывными данными нам нужно сначала очистить данные, прежде чем работать с ними. Это требует времени, а вы просто хотели извлечь несколько запросов. Для извлечения данных обычно используется ссылка link: / formulas-and-functions-Introduction-of-vlookup-function [функция VLOOKUP]. Но когда у нас есть длинные данные с множеством полей столбцов, они становятся беспорядочными, потому что функция ВПР требует правильного индекса столбца как числа, иначе формула вернет ошибку. Вот формула, объясняющая этот тип проблемы.
Как решить проблему?
Чтобы формула сначала поняла, нам нужно немного пересмотреть следующие функции. link: / lookup-formulas-excel-index-function [INDEX function]
-
link: / lookup-formulas-excel-match-function [MATCH function]
Теперь мы составим формулу, используя указанные выше функции. Функция соответствия вернет индекс искомого значения1 в поле заголовка строки. А другая функция ПОИСКПОЗ вернет индекс искомого значения 2 в поле заголовка столбца. Номера индексов теперь будут переданы в функцию ИНДЕКС для получения значений под поисковым значением из данных таблицы.
Общая формула:
|
= ( data , ( lookup_value1, row_headers, 0 , ( lookup_value2, column_headers, 0 ) ) ) |
data: массив значений внутри таблицы без заголовков lookup_value1: значение для поиска в row_header.
row_headers: массив индекса строки для поиска.
lookup_value1: значение для поиска в column_header.
column_headers: индексный массив столбца для поиска.
Пример:
Все это может сбивать с толку. Давайте разберемся, как пользоваться функцией, на примере. Здесь у нас есть образцы данных о заказе, и нам нужно найти недостающие данные, относящиеся к заданному значению в таблице.
Здесь нам нужно найти детали заказа, такие как количество, регион и цену для заказа, размещенного 13-01-2019.
Используйте формулу:
|
= INDEX ( A2:D11 , MATCH ( G3 , A2:A11 , 0 ) , MATCH ( F4 , A1:D1 , 0 ) ) |
Пояснение:
-
Функция ПОИСКПОЗ возвращает индекс совпадающего значения в заданном одномерном массиве (горизонтальном или вертикальном).
-
MATCH (F4, A1: D1,0) возвращает 3 в качестве индекса.
-
ПОИСКПОЗ (G3, A2: A11,0) возвращает 5 как индекс. Функция ИНДЕКС вернет 5-й элемент (начиная со 2-й строки) в 3-м столбце.
Как видите, количество, относящееся к заказу, размещенному 13.01.2019, составляет 38. Теперь мы можем найти регион, из которого был размещен заказ, используя формулу.
Используйте формулу:
|
= INDEX ( A2:D11 , MATCH ( G3 , A2:A11 , 0 ) , MATCH ( F5 , A1:D1 , 0 ) ) |
Как видите, заказ был сделан из Южного региона. Теперь получите цену, связанную с размещенным заказом
Используйте формулу:
|
= INDEX ( A2:D11 , MATCH ( G3 , A2:A11 , 0 ) , MATCH ( F6 , A1:D1 , 0 ) ) |
Как видите, цена составляет 82,84. Вы заметили, что мы извлекли все детали по одной формуле. Комбинация ИНДЕКС и ПОИСКПОЗ лучше всего подходит для поиска одного значения из таблицы.
Вот все заметки по использованию формулы.
Примечания:
-
Формула работает как для текста, так и для чисел.
-
Функция возвращает ошибку #NA, если аргумент массива поиска функции ПОИСКПОЗ не имеет той же длины, что и массив таблицы.
-
Формула возвращает ошибку, если lookup_value не соответствует значению в таблице lookup_array.
-
Функция соответствует точному значению, поскольку аргумент типа соответствия функции ПОИСКПОЗ равен 0.
-
Используйте аргумент -1 для меньшего, 0 для точного совпадения и 1 для большего, чем поисковый запрос.
-
Значения поиска могут быть заданы как ссылка на ячейку или напрямую с использованием символа кавычки («) в формуле в качестве аргументов.
Надеюсь, вы поняли, как получить значение из справочной сетки в соответствии со значениями индекса Сохранить в Excel. Узнать больше статьи о значении поиска в Excel и функциях Excel 2019 здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook.
А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, пожалуйста. дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам по адресу [email protected].
Статьи по теме
link: / lookup-formulas-use-index-and-match-to-lookup-value [Используйте ИНДЕКС и ПОИСКПОЗ для поиска значения]: функция ИНДЕКС и ПОИСКПОЗ для поиска необходимого значения.
link: / summing-sum-range-with-index-in-excel [SUM range with INDEX in Excel]: Используйте функцию INDEX, чтобы найти СУММУ значений по мере необходимости.
`link: / lookup-formulas-excel-index-function [Как использовать функцию ИНДЕКС в Excel] `: Найдите ИНДЕКС массива, используя функцию ИНДЕКС, объясненную в примере.
link: / lookup-formulas-excel-match-function [Как использовать функцию ПОИСКПОЗ в Excel]: Найдите ПОИСКПОЗ в массиве, используя значение ИНДЕКС внутри функции ПОИСКПОЗ, объясненное в примере.
link: / lookup-formulas-excel-lookup-function [Как использовать функцию ПРОСМОТР в Excel]: Найдите значение поиска в массиве с помощью функции ПРОСМОТР, объясненной в примере.
Популярные статьи:
link: / tips-if-condition-in-excel [Как использовать функцию IF в Excel]: оператор IF в Excel проверяет условие и возвращает конкретное значение, если условие истинно, или другое конкретное значение, если оно ложно .
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.