Изтеглете примерната работна книга
Този урок ще покаже как да извършите чувствителен към регистър VLOOKUP в Excel, използвайки два различни метода и Google Sheets, използвайки един метод.
Чувствителен към регистъра VLOOKUP с помощна колона
Функция VLOOKUP
Функцията VLOOKUP се използва за търсене на приблизително или точно съвпадение за стойност в най -лявата колона на диапазон и връща съответната стойност от друга колона. По подразбиране VLOOKUP ще работи само за нечувствителни към регистъра стойности по следния начин:
| 1 | = VLOOKUP ($ E $ 2, $ B $ 2: $ C $ 4,2,0) |
Чувствителен към регистъра VLOOKUP
Чрез комбиниране на VLOOKUP, EXACT, MAX и ROW, можем да създадем VLOOKUP чувствителна към регистъра формула, която връща съответната стойност за нашия VLOOKUP, чувствителен към регистър. Нека преминем през пример.
Имаме списък с артикули и съответните им цени (забележете, че идентификационният номер на елемента е чувствителен към малки и главни букви):
Да предположим, че сме помолени да получим цената за даден артикул, като използваме неговия идентификационен номер по следния начин:
За да постигнем това, първо трябва да създадем помощна колона, използвайки ROW:
| 1 | = ROW () |
= ROW () щракнете и плъзнете (или щракнете двукратно), за да запълните предварително всички редове в диапазона
След това комбинирайте VLOOKUP, MAX, EXACT и ROW във формула така:
| 12 | = VLOOKUP (MAX (ТОЧНО (,)*(ROW ())),,, 0) |
| 1 | = VLOOKUP (MAX (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0) |
Как действа формулата?
- Функцията EXACT проверява идентификационния номер на елемента в E2 (стойност за търсене) спрямо стойностите в B2: B7 (диапазон за търсене) и връща масив от TRUE, когато има точно съвпадение или FLASE в масив {FLASE, FLASE, FLASE, FLASE, FLASE, ИСТИНА}.
- След това този масив се умножава по ROW масива {2, 3, 4, 5, 6, 7} (имайте предвид, че това съвпада с нашата помощна колона).
- Функцията MAX връща максималната стойност от получения масив {0,0,0,0,0,7}, което е 7 в нашия пример.
- След това използваме резултата като стойност за търсене във VLOOKUP и избираме помощната колона като диапазон за търсене. В нашия пример формулата връща съответстващата стойност от $ 16.00.
Метод 2 - чувствителен към регистър VLOOKUP с „виртуална“ помощна колона
Този метод използва същата логика като първия метод, но премахва необходимостта от създаване на помощна колона и вместо това използва CHOOSE и ROW за създаване на „виртуална“ помощна колона по следния начин:
| 12 | = VLOOKUP (MAX (ТОЧНО (,)*(ROW ())),ИЗБЕРИ ({1,2}, ROW (),),, 0) |
| 1 | = VLOOKUP (MAX (ТОЧНО (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7))), ИЗБЕРИ ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0) |
Как действа формулата?
- Първата част на формулата работи по същия начин като първия метод.
- Комбинирането на CHOOSE и ROW връща масив с две колони, една за номера на ред и друга за цената. Масивът е разделен с точка и запетая, за да представлява следващия ред и запетая за следващата колона по следния начин: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- След това можем да използваме резултата от първата част на формулата във VLOOKUP, за да намерим съответната стойност от нашия масив CHOOSE и ROW.
Чувствителен към регистър VLOOKUP в Google Таблици
За да извършите чувствителен към регистър VLOOKUP в Google Sheets, използвайте този метод:
