В тази статия ще научим как да извлечем стойност от референтна мрежа според стойностите на индекса Запазване в Excel
Сценарий :
Много пъти, когато работим с дълги неразпръснати данни, трябва първо да изчистим данните, преди да работим върху тях. Отнема време и просто искате да извлечете някои заявки. За извличане на данни обикновено използвате функцията VLOOKUP. Но когато имаме дълги данни с много полета на колони, тогава става объркано, защото функцията VLOOKUP изисква правилния индекс на колоната като номер или иначе формулата връща грешка. Точно тук по -долу е обяснението на формулата за този вид проблем.
Как да решим проблема?
За да разберем първо формулата, трябва да преразгледаме малко следните функции
- INDEX функция
- MATCH функция
Сега ще направим формула, използвайки горните функции. Функцията за съвпадение ще върне индекса на стойността за търсене1 в полето за заглавие на ред. И друга функция MATCH ще върне индекса на стойността за търсене 2 в полето за заглавието на колоната. Номерата на индекса сега ще бъдат въведени във функцията INDEX, за да получат стойностите под стойността за търсене от данните в таблицата.
Обща формула:
| = INDEX (данни, MATCH (lookup_value1, row_headers, 0, MATCH (lookup_value2, column_headers, 0))) |
данни : масив от стойности в таблицата без заглавки
lookup_value1 : стойност за търсене в ред_заглавие.
row_headers : Масив от индекс на редове за търсене.
lookup_value1 : стойност за търсене в колоната_заглавие.
колони_заглавки : колона Индексен масив за търсене.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да използваме функцията, използвайки пример. Тук имаме някои примерни данни за поръчки и трябва да намерим липсващите данни, свързани с дадената стойност в таблицата.
Тук трябва да намерим подробности за поръчката като Количество, регион и цена за поръчката, направена на 13-01-2019.
Използвайте формулата:
| = ИНДЕКС (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F4, A1: D1, 0)) |
Обяснение:
- Функцията MATCH връща индекса на съответстващата стойност в дадения 1D масив (хоризонтален или вертикален).
- MATCH (F4, A1: D1,0) връща 3 като индекс.
- MATCH (G3, A2: A11,0) връща 5 като индекс
- Функцията INDEX ще върне петия елемент (започвайки от втория ред) в третата колона.
Както можете да видите количеството, свързано с поръчка, направена на 13-01-2019, излиза 38. Сега можем да намерим региона, от който е направена поръчката, използвайки формулата.
Използвайте формулата:
| = ИНДЕКС (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F5, A1: D1, 0)) |
Както можете да видите, поръчката е направена от Южния регион. Сега вземете цената, свързана с направената поръчка
Използвайте формулата:
| = ИНДЕКС (A2: D11, MATCH (G3, A2: A11, 0), MATCH (F6, A1: D1, 0)) |
Както можете да видите, цената е 82.84. Забелязахте, че извличаме всички подробности, използвайки една формула. Комбинацията INDEX и MATCH е най -добре да намерите една стойност от таблицата.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Формулата работи както за текст, така и за числа.
- Функцията връща грешката #NA, ако аргументът на масива за търсене към функцията MATCH не е със същата дължина на масива от таблици.
- Формулата връща грешка, ако lookup_value не съвпада със стойността в таблицата lookup_array.
- Функцията съвпада с точната стойност, тъй като аргументът тип съвпадение на функцията MATCH е 0.
- Използвайте аргумента -1 за по -малко от, 0 за точно съвпадение и 1 за по -голямото от съвпадението за търсене.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
Надявам се, че сте разбрали Как да извлечете стойност от референтна мрежа според стойностите на индекса Запазване в Excel. Разгледайте още статии за стойността на търсене в Excel и функциите на Excel 2019 тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Използвайте INDEX и MATCH за търсене на стойност : INDEX & MATCH функция за търсене на стойност според нуждите.
Обхват SUM с INDEX в Excel : Използвайте INDEX функцията, за да намерите SUM на стойностите, както е необходимо.
Как да използвате функцията INDEX в Excel : Намерете INDEX на масив, като използвате функцията INDEX, обяснена с пример.
Как да използвате функцията MATCH в Excel : Намерете MATCH в масива, като използвате стойността INDEX вътре в функцията MATCH, обяснена с пример.
Как да използвате функцията LOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията LOOKUP, обяснена с пример.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.