Искате да учите, за да използвате Функция VLOOKUP с 2 условия или много повече? Тогава сте на правилното място!
Ако вече сте запознати с функцията VLOOKUP на Excel, тогава трябва да знаете, че в основния си формат тя е с възможност за търси само един резултат и връща съответната стойност от реда с подробности.
Но какво да правите, когато имате нужда използвайте функцията VLOOKUP с 2 или повече условия? Това е често срещан въпрос сред клиентите на Excel и в статията ще научите за 4 различни опции за извършване на търсения с много повече от един метод. За контраст!
Може да ви заинтересува:
► Научете функцията VLOOKUP за по-малко от 2 минути
► Какво представлява и как се прави VLOOKUP в Microsoft Excel – TecnoBreak
VLOOKUP с 2 условия: вероятни решения
Въпреки че е невъзможно да използвате VLOOKUP с 2 или повече условия в оригиналната му форма, Excel ви дава опции за намиране на резултати въз основа на повече от един метод. Ето 4 опции, които можете да използвате:
Опция 1: VLOOKUP с 2 критерия, използвайки помощна колона
За да илюстрираме нашия пример за VLOOKUP с 2 условия, да предположим, че имате таблица с имената на плодовете, търговците и общата стойност, продадена от всеки продавач (както е показано по-долу):
В такъв случай трябва да погледнете в таблицата, за да видите общата цена според продукта и името на продавача. Тоест имаме 2 критерия за търсене, които са Продукт и Продавач.
Тъй като функцията VLOOKUP може да работи само с един метод за търсене в даден момент, добра алтернатива е да използвате помощна колона, за да направите уникален квалификатор. Този уникален квалификатор може да се използва за намиране на точната стойност. За пример, в предишната таблица Orange се появява три пъти, но има само една комбинация от Orange и продавача César, Orange и продавача Marcio и Orange и продавача João.
Сега стъпките за създаване на уникален квалификатор са описани подробно:
1. Въведете релефна колона между колони B и C.
2. Използвайте следната формула в помощната колона: =A2&”|”&B2. Това ще създаде уникални квалификатори за всеки запис, както е показано по-долу.
В този момент имаме възможност да използваме функцията VLOOKUP по следния начин: =BUSCARV($G1&”|”&G$2;$C$2:$D$10;2;0)
Имайте предвид, че сме разработили уникални квалификатори за всеки запис в таблицата. В използваната преди това функция VLOOKUP стойността за търсене беше променена на $G1&”|”&G$2 така че двата критерия за търсене се комбинират и използват като една стойност за търсене. Например търсената стойност за функцията VLOOKUP в G3 е Orange | Жоао. Тази търсена стойност в този момент се използва за получаване на общата стойност в D2:D10.
съществен аспект
Ако сте стигнали дотук, може да имате някои притеснения относно използването на обобщена колона за VLOOKUP с 2 условия. Така че си струва да им отговорите, преди да продължите.
- Защо да използвате символа | съберете 2 критерия заедно? – При няколко изключително странни (но вероятни) условия имате възможността да имате 2 различни критерия, но в крайна сметка да дадете абсолютно същия резултат, когато се смесят. Следващото изображение показва много прост примерен случай.
Имайте предвид, че докато A2 и A3 не са еднакви и B2 и B3 не са еднакви, композициите се оказват абсолютно еднакви. Но ако използвате разделител, дори комбинацията ще бъде различна (D2 и D3).
Ако не възнамерявате да използвате резервни колони за търсене с 2 или повече условия в Excel, вижте следващите три опции, които ще ви дадат изглед отвъд класическата функция VLOOKUP.
Вариант 2: Използване на функцията PROC
Алтернативен вариант на функцията VLOOKUP с 2 условия е да използвате функцията VLOOKUP. Вижте примера сега:
Функцията LOOKUP може да се използва, когато искате да търсите само в един ред или колона и да получите стойност на точно същото място във втори ред или колона.
В предишния пример откриваме, че приравняваме моделната гама (A2:A10) с клетката, в която има името на продукта (в този случай F1). Всяка стойност в диапазона A2:A10 се приравнява на F1 и връща TRUE, ако е равна, или FALSE, ако е различна.
Така че правим същото с колоната Продавач (B2:B10), съпоставяйки всяка стойност с F2. И накрая, търсим съответната стойност в диапазона C2:C10. Вижте примера сега:
Опция 3: Използвайте функцията SUMPRODUCT
Третият вариант за извършване на търсене в Excel с 2 или повече критерия е да използвате функцията SUMPRODUCT. Тази функция е много гъвкава благодарение на способността си да управлява масиви.
В следващия пример използваме функцията SUMPRODUCT, за да намерим общата стойност въз основа на 2 критерия. Основната инициатива е да умножите ранговете по 1, ако дадено условие е изпълнено, и по 0, ако не.
По същество формулата търси в диапазона A2:A10 за стойността в клетка F1 и в диапазона B2:B10 за стойността в клетка F2. В момента, в който намери и двете, връща стойността в колона C от същия ред, където отговаря и на двата критерия.
проверете тук Пълно обяснение на функцията SUMPRODUCT в Excel.
Вариант 4: Използване на функциите INDEX и MATCH
Друга алтернативна възможност за извършване на търсения с много повече от един метод в Excel е да използвате функциите INDEX и MATCH заедно. Вижте примера сега:
С тази техника можете да използвате функцията MATCH, за да намерите реда, където и двете условия са верни. Това връща стойност 1, която съответства на 1, използвана като стойност за търсене на функцията MATCH, връщайки реда, където са изпълнени условията. С помощта на функцията INDEX можете да намерите стойността, която е в диапазона C2:C10, който е в реда, върнат от функцията MATCH.
Тъй като това е формула за масив, е необходимо да се използват ключовете Ctrl+Shift+Enter.
Забележка: Всеки един от предходните примери описва как да използвате 2 критерия за търсене. Ако имате много повече от 2 критерия, просто ги добавете към формулите
Изтеглете тук работния документ с примерите, представени в този урок
Гледайте видео урока Procv с 2 условия
Вижте и следващите няколко съвета на Excel:
И така, какво мислите за съвета? Оставете своя коментар сега, ще се радваме да чуем от вас! За още повече препоръки за Excel, продължавайте да следвате Excel Easy!
