зміст
Цей маленький підручник пояснює, як створити функцію VPR (VLOOKUP) з урахуванням регістру, показує кілька інших формул, за якими Excel може шукати з урахуванням регістру, і вказує на сильні та слабкі сторони кожної функції.
Мабуть, кожен користувач Excel знає, яка функція виконує вертикальний пошук. Правильно, це функція VPR. Проте це мало хто знає VPR не чутливий до регістру, тобто символи нижнього та верхнього регістру для нього ідентичні.
Ось короткий приклад, який демонструє неспроможність VPR розпізнати реєстр. Припустимо, в клітинку A1 містить значення «рахунок» і клітинку A2 – «Вексель», формула:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… зупинить пошук на «рахунку», оскільки це значення стоїть першим у списку, і витягне значення з комірки B1.
Далі в цій статті я покажу вам, як це зробити VPR чутливий до регістру. Крім того, ми дізнаємося ще кілька функцій, які можуть виконувати пошук у Excel з урахуванням регістру.
Почнемо з найпростішого – Поглянути (ШУК) і SUMPRODUCT (SUMPRODUCT), які, на жаль, мають кілька істотних обмежень. Далі ми детальніше розглянемо трохи складнішу формулу ІНДЕКС+МАТЧ (INDEX+MATCH), який бездоганно працює в будь-якій ситуації та з будь-яким набором даних.
Функція VLOOKUP чутлива до регістру
Як ви вже знаєте, звичайна функція VPR не враховує регістр. Однак є спосіб зробити його чутливим до регістру. Для цього в таблицю потрібно додати допоміжний стовпець, як показано в наступному прикладі.
Припустимо, в стовпчик B є ідентифікатори продукту (товар), і ви хочете отримати ціну продукту та відповідний коментар із стовпців C и D. Проблема полягає в тому, що ідентифікатори містять як малі, так і великі символи. Наприклад, значення клітинок B4 (001Tvci3u) і B5 (001Tvci3U) відрізняються лише у випадку останнього символу, u и U відповідно.
Як ви можете собі уявити, звичайна формула пошуку
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
повернеться 90 XNUMX$, оскільки значення 001Tvci3u знаходиться в діапазоні пошуку раніше ніж 001Tvci3U. Але це не те, що нам потрібно, чи не так?
Для пошуку за допомогою функції VPR в Excel з урахуванням регістру вам доведеться додати допоміжний стовпець і заповнити його комірки такою формулою (де B — стовпець пошуку):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Ця формула розбиває потрібне значення на окремі символи, замінює кожен символ своїм кодом (наприклад, замість A натомість у 65 a код 97), а потім об’єднує ці коди в унікальний рядок чисел.
Після цього ми використовуємо просту функцію VPR для пошуку з урахуванням регістру:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Правильна робота функції VPR чутливість до регістру залежить від двох факторів:
- Допоміжний стовпець має бути крайнім лівим стовпцем у видимому діапазоні.
- Значення, яке ви шукаєте, має містити код символу замість справжнього значення.
Як правильно використовувати функцію КОД
Формула, вставлена в комірки допоміжного стовпця, передбачає, що всі ваші пошукові значення мають однакову кількість символів. Якщо ні, то вам потрібно знати найменше та найбільше числа та додати стільки ознак ПОМИЛКА (IFERROR) скільки символів є різницею між найкоротшим і найдовшим шуканим значенням.
Наприклад, якщо найкоротше значення пошуку становить 3 символи, а найдовше — 5 символів, використовуйте цю формулу:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Для функції PSTR (MID) Ви надаєте такі аргументи:
- 1-й аргумент – текст (текст) — це текст або посилання на клітинку, що містить символи, які потрібно витягти (у нашому випадку це B2)
- 2-й аргумент – номер_початку (початкова_позиція) - це позиція першого з тих символів, які вилучаються. ви входите 1 у першій функції PSTR, 2 – у другій функції PSTR і т.п.
- 3-й аргумент – кількість_символів (кількість_символів) – визначає кількість символів, які потрібно витягти з тексту. Оскільки нам весь час потрібен лише 1 символ, ми пишемо у всіх функціях 1.
ОБМЕЖЕННЯ: функція VPR не є найкращим рішенням для пошуку в Excel з урахуванням регістру. По-перше, потрібно додати допоміжний стовпчик. По-друге, формула добре працює тільки в тому випадку, якщо дані однорідні або відомо точне число символів у шуканих значеннях. Якщо це не ваш випадок, краще скористайтеся одним із наведених нижче рішень.
Функція LOOKUP для пошуку з урахуванням регістру
функція Поглянути (LOOKUP), пов'язані VPR, однак його синтаксис дозволяє здійснювати пошук з урахуванням регістру без додавання допоміжного стовпця. Для цього використовуйте Поглянути в поєднанні з функцією EXACT (ТОЧНО).
Якщо взяти дані з попереднього прикладу (без допоміжного стовпця), то з поставленим завданням впорається наступна формула:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Пошук формули в діапазоні A2: A7 точна відповідність зі значенням клітинки F2 чутливий до регістру та повертає значення зі стовпця B того самого рядка.
Люблю VPRфункція Поглянути працює однаково з текстовими та числовими значеннями, як ви можете бачити на знімку екрана нижче:
Важливо! Для того, щоб функція Поглянути працював правильно, значення в стовпці пошуку повинні бути відсортовані в порядку зростання, тобто від найменшого до найбільшого.
Дозвольте мені коротко пояснити, як працює функція EXACT у наведеній вище формулі, оскільки це ключовий момент.
функція EXACT порівнює два текстові значення в 1-му та 2-му аргументах і повертає TRUE, якщо вони абсолютно однакові, або FALSE, якщо вони не є. Для нас важлива функція EXACT чутливий до регістру.
Давайте подивимося, як працює наша формула ПЕРЕГЛЯД+ТОЧНО:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- функція EXACT порівнює значення клітинки F2 з усіма елементами в стовпці A (A2:A7). Повертає TRUE, якщо знайдено точний збіг, інакше FALSE.
- Оскільки ви надаєте перший аргумент функції Поглянути значення TRUE, він витягує відповідне значення з указаного стовпця (у нашому випадку, стовпця B), лише якщо знайдено точний збіг, чутливий до регістру.
Сподіваюся, це пояснення було зрозумілим і тепер ви зрозуміли головну думку. Якщо так, то з іншими функціями, які ми розберемо далі, у вас не виникне труднощів, т.к. всі вони працюють за одним принципом.
ОБМЕЖЕННЯ: Дані в стовпці підстановки мають бути відсортовані в порядку зростання.
SUMPRODUCT – знаходить текстові значення з урахуванням регістру, але повертає лише числа
Як ви вже зрозуміли з назви, SUMPRODUCT (SUMPRODUCT) — це ще одна функція Excel, яка допоможе виконувати пошук з урахуванням регістру, але повертатиме лише числові значення. Якщо цей варіант вас не влаштовує, то можна відразу переходити до пучка ІНДЕКС+МАТЧ, що дає рішення для будь-якого випадку і для будь-яких типів даних.
По-перше, дозвольте мені коротко пояснити синтаксис цієї функції, це допоможе вам краще зрозуміти наведену нижче формулу з урахуванням регістру.
функція SUMPRODUCT множить елементи заданих масивів і повертає суму результатів. Синтаксис виглядає так:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Оскільки нам потрібен пошук з урахуванням регістру, ми використовуємо функцію EXACT (ТОЧНО) з попереднього прикладу як один із множників:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Як ви пам’ятаєте, EXACT порівнює значення клітинки F2 з усіма елементами в стовпці A. Повертає TRUE, якщо знайдено точний збіг, інакше FALSE. У математичних операціях Excel приймає TRUE як 1, і FALSE для 0далі SUMPRODUCT множить ці числа і підсумовує результати.
Нулі не враховуються, тому що при множенні вони завжди дають 0. Давайте детальніше розглянемо, що відбувається, коли в стовпці є точний збіг A знайшов і повернув 1… Функція SUMPRODUCT множить число в стовпчику B on 1 і повертає результат – точно таке саме число! Це тому, що результати інших добутків дорівнюють нулю, і вони не впливають на результуючу суму.
На жаль функція SUMPRODUCT не може працювати з текстовими значеннями та датами, оскільки їх неможливо перемножити. У цьому випадку ви отримаєте повідомлення про помилку #VALUE! (#VALUE!), як у клітинці F4 на малюнку нижче:
ОБМЕЖЕННЯ: Повертає лише числові значення.
INDEX + MATCH – пошук з урахуванням регістру для будь-якого типу даних
Нарешті, ми наблизилися до необмеженої та чутливої до регістру формули пошуку, яка працює з будь-яким набором даних.
Цей приклад наводиться останнім не тому, що найкраще залишилося на десерт, а тому, що знання, отримані з попередніх прикладів, допоможуть вам краще та швидше зрозуміти формулу з урахуванням регістру. ІНДЕКС+МАТЧ (ІНДЕКС+МАТЧ).
Як ви, напевно, здогадалися, поєднання функцій БІЛЬШ ЕКСПОЗИЦІЙНИЙ и ІНДЕКС використовується в Excel як більш гнучка та потужна альтернатива для VPR. Стаття Використання INDEX і MATCH замість VLOOKUP чудово пояснює, як ці функції працюють разом.
Я лише нагадаю ключові моменти:
- функція БІЛЬШ ЕКСПОЗИЦІЙНИЙ (MATCH) шукає значення в заданому діапазоні та повертає його відносну позицію, тобто номер рядка та/або стовпця;
- Далі функція ІНДЕКС (INDEX) повертає значення з указаного стовпця та/або рядка.
Формулювати ІНДЕКС+МАТЧ можна шукати з урахуванням регістру, вам потрібно лише додати до нього одну функцію. Не важко здогадатися, що це знову EXACT (ТОЧНО):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
У цій формулі EXACT працює так само, як і в поєднанні з функцією Поглянутиі дає той самий результат:
Зверніть увагу, що формула ІНДЕКС+МАТЧ у фігурних дужках є формула масиву, яку потрібно завершити, натиснувши Ctrl + Shift + Enter.
Чому INDEX+MATCH є найкращим рішенням для пошуку з урахуванням регістру?
Основні переваги бандла ІНДЕКС и БІЛЬШ ЕКСПОЗИЦІЙНИЙ:
- Не вимагає додавання допоміжної колонки, на відміну від VPR.
- Не вимагає сортування стовпця пошуку, на відміну від Поглянути.
- Працює з усіма типами даних – числами, текстом і датами.
Ця формула здається ідеальною, чи не так? Насправді це не так. І ось чому.
Припустімо, що клітинка в стовпці поверненого значення, пов’язана зі значенням пошуку, порожня. Який результат поверне формула? Немає? Давайте подивимося, що насправді повертає формула:
На жаль, формула повертає нуль! Це не може бути великою проблемою, якщо ви працюєте з чисто текстовими значеннями. Однак якщо таблиця містить числа, в тому числі «справжні» нулі, це стає проблемою.
Насправді всі інші формули пошуку (VLOOKUP, LOOKUP і SUMPRODUCT), які ми обговорювали раніше, поводяться так само. Але ви хочете ідеальної формули, чи не так?
Щоб зробити формулу чутливою до регістру ІНДЕКС+МАТЧ ідеально, помістіть його у функцію IF (IF), який перевірить клітинку з поверненим значенням і поверне порожній результат, якщо вона порожня:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
У цій формулі:
- B це стовпець із значеннями, що повертаються
- 1+ це число, яке повертає відносне положення клітинки, яку повертає функція БІЛЬШ ЕКСПОЗИЦІЙНИЙ, на справжню адресу комірки. Наприклад, у нашій функції БІЛЬШ ЕКСПОЗИЦІЙНИЙ заданий пошуковий масив A2: A7, тобто взаємне розташування клітини A2 волі 1, оскільки він перший у масиві. Але фактичне положення клітини A2 в колонці є 2, тож додаємо 1компенсувати різницю та мати функцію НЕПРАВИЛЬНО (INDIRECT) отримав значення з потрібної клітинки.
На малюнках нижче показано виправлену формулу з урахуванням регістру ІНДЕКС+МАТЧ В дії. Він повертає порожній результат, якщо повернута клітинка порожня.
Я переписав формулу в стовпчики B:Dщоб рядок формул відповідав знімку екрана.
Формула повертається 0якщо повернута клітинка містить нуль.
Якщо ви хочете посилання ІНДЕКС и БІЛЬШ ЕКСПОЗИЦІЙНИЙ виводить якесь повідомлення, коли значення, що повертається, порожнє, ви можете записати його в останні лапки («») формули, наприклад, так:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
