✅ Как да направим Excel VLOOKUP чувствителен към регистъра

Съдържание

Осъществяване на чувствителността на регистъра към VLOOKUP

По подразбиране стойността за търсене във функцията VLOOKUP не е чувствителна към регистъра. Например, ако вашата стойност за търсене е MATT, matt или Matt, всичко е същото за функцията VLOOKUP. Той ще върне първата съвпадаща стойност, независимо от случая.

Осъществяване на чувствителността на регистъра към VLOOKUP

Да предположим, че имате данните, както е показано по -долу:

Както можете да видите, има три клетки със същото име (A2, A4 и A5), но с различна буква. Вдясно (в E2: F4) имаме трите имена (Matt, MATT и matt) заедно с техните резултати в Math.

Функцията Excel VLOOKUP не е оборудвана за обработка на чувствителни към регистъра стойности за търсене. В този пример по -горе, без значение каква е стойността на справочната стойност (Matt, MATT или matt), тя винаги ще върне 38 (което е първата съвпадаща стойност).

В този урок ще научите как да направите VLOOKUP чувствителен към регистъра чрез:

Използване на помощна колона.
Без да използвате помощна колона и да използвате формула.

Осъществяване на VLOOKUP чувствителен към регистъра - Използване на помощна колона

Помощна колона може да се използва за получаване на уникална стойност за търсене за всеки елемент в масива за търсене. Това помага да се прави разлика между имена с различни букви.

Ето стъпките за това:

Вмъкнете помощна колона вляво от колоната, от която искате да извлечете данните. В примера по -долу трябва да вмъкнете помощната колона между колони A и C.
В помощната колона въведете формулата = ROW (). Той ще вмъкне номера на реда във всяка клетка.
Използвайте следната формула в клетка F2, за да получите резултата от търсенето, чувствителен към регистъра.
```
= VLOOKUP (MAX (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
Копирайте го поставете за останалите клетки (F3 и F4).

Забележка: Тъй като това е формула за масив, използвайте Control + Shift + Enter вместо просто enter.

Как работи това?

Нека разбием формулата, за да разберем как работи:

ТОЧНО (E2, $ A $ 2: $ A $ 9) - Тази част сравнява стойността на търсене в E2 с всички стойности в A2: A9. Той връща масив от TRUEs/FALSEs, където TRUE се връща, когато има точно съвпадение. В този случай, когато стойността в E2 е Matt, тя ще върне следния масив:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Тази част умножава масива от TRUEs/FALSEs с номера на ред A2: A9. Навсякъде, където има TRUE, то дава номер на ред, иначе дава 0. В този случай той ще върне {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (ТОЧНО (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))) - Тази част връща максималната стойност от масива от числа. В този случай тя ще върне 2 (което е номерът на реда, където има точно съвпадение).
Сега просто използваме този номер като стойност за търсене и използваме масива за търсене като B2: C9.

Забележка: Можете да вмъкнете помощната колона навсякъде в набора от данни. Просто се уверете, че е вляво от колоната, откъдето искате да извлечете данните. След това трябва да коригирате съответно номера на колоната във функцията VLOOKUP.

Сега, ако не сте фен на помощната колона, можете също да направите търсене с чувствителност към регистъра без помощната колона.

Направете VLOOKUP чувствителен към регистъра - без помощната колона

Дори когато не искате да използвате помощната колона, все пак трябва да имате виртуална помощна колона. Тази виртуална колона не е част от работния лист, но е изградена във формулата (както е показано по -долу).

Ето формулата, която ще ви даде резултата без помощната колона:

= VLOOKUP (MAX (ТОЧНО (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), ИЗБЕРЕТЕ ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

Как работи това?

Формулата също използва концепцията за помощна колона. Разликата е, че вместо да поставяте помощната колона в работния лист, считайте я за виртуални помощни данни, които са част от формулата.

Ето частта, която работи като помощни данни (маркирана в оранжево):

= VLOOKUP (MAX (ТОЧНО (D2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))),ИЗБЕРЕТЕ ({1,2}, ROW ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Нека ви покажа какво имам предвид под виртуални помощни данни.

В горната илюстрация, докато избирам частта CHOOSE от формулата и натискам F9, тя показва резултата, който формулата CHOOSE би дала.

Резултатът е {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

Това е масив, където запетая представлява следващата клетка в същия ред, а точка и запетая представлява, че следните данни са в следващия ред. Следователно, тази формула създава 2 колони с данни - една колона има номера на ред, а другата има оценка по математика.

Сега, когато използвате функцията VLOOKUP, тя просто търси стойността за търсене в първата колона (от тези виртуални данни от 2 колони) и връща съответния резултат. Стойността за търсене тук е число, което получаваме от комбинацията от функция MAX и EXACT.

Изтеглете примерния файл