Excel Vlookup за връщане на множество стойности
Една от ключовите функционалности на функцията VLOOKUP е, че тя ще работи за уникални стойности и ако има дублирани стойности, тогава каквато и първа намерена стойност ще бъде върната и за всички останали справочни стойности. Това е едно от ключовите неща, които трябва да имаме предвид, докато прилагаме формула VLOOKUP. Когато справочната стойност се появи няколко пъти и ако има множество стойности, тогава трябва да включим различни стратегии. В тази статия ще ви покажем как да върнете множество стойности с помощта на функцията VLOOKUP.
Как да върна множество стойности с помощта на функцията Vlookup?
Както казахме по-горе, VLOOKUP работи за уникални стойности, а за дублирани стойности ще върне първата намерена стойност.
Например погледнете данните по-долу.
Имаме „Таблица 1“ и „Таблица 2“ в „Таблица 1“, имаме плодове и техните цени в различни градове, а за „Таблица 2“ трябва да достигнем себестойност от „Таблица 1“ с помощта на функцията VLOOKUP. Приложете първо функцията VLOOKUP.
- Вижте, имаме еднаква цена за всички градове. Например в „Таблица 1“ за „Apple“ в град „Бангалор“ имаме 108, тъй като това е първата стойност, намерена в таблицата за „Apple“, тя е върнала еднаква за всички градове.
- По същия начин, за „Грозде“ първата стойност е 79 и същото е върнато за всички градове, а в случая с „Оранжево“ връща 56 за всички градове.
И така, в тези случаи трябва да създадем помощна колона, за да създадем уникален списък със справочни стойности. Всеки плод има различни цени за всеки град, така че комбинация от име на плодове и град може да създаде уникален списък, да вмъкне помощна колона и да комбинира името на плода и името на града.
Така че всяко име на плодове се комбинира с града, като включва наклонена черта (/) като разделител между името на плода и името на града.
Сега се върнете към „Таблица 2“ и отворете функцията VLOOKUP.
Сега трябва да включим същата стратегия на помощната колона тук, за да изберете справочна стойност, първо изберете име на плодове.
След това комбинирайте наклонена черта назад, преди да комбинирате с името на града.
Сега комбинирайте името на града.
Сега търсещата стойност е подобна на помощната колона. Сега изберете масива на таблицата, започвайки от помощната колона.
Сега посочете номера на колоната като 4 и търсенето на диапазон като FALSE или 0.
Ето, имаме нов ценоразпис с точни цифри, да кажем благодарение на помощната колона или комбинацията от Fruit Name & City.
Използвайте алтернативни методи за множество стойности
Видяхме как помощната колона може да бъде полезна при извличането на множество стойности с помощта на формулата VLOOKUP. Но представете си ситуацията по-долу.
В това нямаме име на град, за да създадем колона за конкатенация, така че може да се наложи да използваме различни стратегии. По-долу е сложната формула, която можем да използваме, за да получим множество стойности на дублиращи се уникални стойности.
= ИНДЕКС ($ B $ 2: $ B $ 11, МАЛКИ (АКО (E3 = $ A $ 2: $ A $ 11, РЯД ($ A $ 2: $ A $ 11) - РЯД ($ A $ 2) +1), РЯД (1: 1)))
Тази формула изглежда обширна, нали? Имаме обаче друг алтернативен метод, т.е. комбиниране на името на плодовете с броя им в списъка.
Приложете функцията COUNTIF по-долу, за да създадете помощна колона.
Горната функция ще ни даде броя на всеки плод, комбиниран със самото име на плода. За пример, погледнете реда номер 4 в това, имаме броене на "Apple" 2 пъти и така броят казва 2 и в комбинация с името на плодовете ни дава "2Apple". Така че това ще създаде уникален списък с плодове.
Сега създайте справочна таблица като долната.
Сега отворете функцията VLOOKUP в таблицата за търсене, т.е. в клетката H3.
В помощната колона се отчита първата комбинирана стойност, така че тук да изберете числовата стойност и след това да се комбинира с името на плодовете.
Сега изберете таблицата и въведете индексния номер на колоната, за да получите резултата.
Неща за запомняне
- VLOOKUP връща същата стойност за справочните стойности, ако справочната стойност има дублирани имена.
- За да извлечем множество стойности на една и съща справочна стойност, трябва да създадем помощни колони, като използваме някой от горните 3 метода.
Препоръчани статии
Това е ръководство за VLOOKUP за връщане на множество стойности. Тук обсъждаме как да върнем множество стойности с помощта на функцията VLOOKUP и да използваме алтернативни методи за множество стойности и изтегляем шаблон на Excel. Можете също да разгледате тези полезни функции в Excel -
- Използвайте VLOOKUP с Power BI
- Приложете VLOOKUP за текст
- Приложете VLOOKUP с Match
- VLOOKUP Урок за Excel
