Функція ВПР та інші подібні до неї функції пошуку мають один недолік - вони не можуть розрізняти верхній і нижній регістр символів (великі і маленькі букви). Даний недолік може виявитися досить дратівливим, а іноді істотно ускладнює для певного роду завдань. Якщо поставлена перед вами завдання в Excel вимагає враховувати регістр символів в тексті значень, тоді функцію ВПР (і подібні до неї) слід замінити формулою.
Як змусити формулу Excel розрізняти великі і маленькі букви
Припустимо, що вміст вихідного значення для пошуку знаходиться в осередку D1, а таблиця, по якій буде виконаний пошук, знаходиться в діапазоні A1: B10.
Щоб знайти необхідні значення:
- У осередок E1 введіть наступну формулу:
- Після введення формули, для підтвердження натисніть комбінацію гарячих клавіш CTRL + SHIFT + Enter, так як формула повинна бути виконана в масиві. Якщо все зроблено правильно в рядку формул з'являться фігурні дужки <>.
Приклад таблиці і роботи формули показано на малюнку:
Як видно тепер в умовах пошуку враховується великі букви символів.
Увага! Якщо таблиця не містить початкове значення для пошуку, тоді формула повертає порожню осередок. Якщо ж таблиця містить кілька дублікатів вихідного значення, тоді формула повертає останній дублікат. Це протилежний результат функції ВПР, яка при наявності дублікатів повертає перший з них.
Принцип дії формули пошуку з урахуванням регістру
Для пошуку значення формула використовує функцію = збігаючись (), яка порівнює два тексти. При цьому враховує великі букви символів і повертає логічне значення ІСТИНА, якщо тексти значень збіглися. Інакше буде повернуто логічне значення ЛОЖЬ. Так як ми використовуємо цю функцію в масиві формул, порівняння значення D1 відбувається з кожним значенням всіх елементів таблиці в діапазоні A1: A10.
Завдання функції = ЕСЛИ () - повертати постій текст, в випадки коли логічний вираз АБО (збігаючись (A1: A10; D1)) повертає значення БРЕХНЯ. Порожній текст формула поверне якщо функція збігаючись не знайде жодного збігу при порівнянні з вихідним текстом. Якщо замість цього значення буде знайдено, то в фрагменті формули: збігаючись (A1: A10; D1) * СТРОКА (A1: B10) буде виконаний повторний пошук і в результаті в пам'ять буде повернуто номер рядка, яка містить знайдене значення. Тут використовується той факт, що під ча виконання арифметичних дій логічні значення ІСТИНА і НЕПРАВДА замінюються на числа 1 і 0 - відповідно. Тому у випадку, коли в процесі пошуку текст знайдений, буде отримано значення відповідає номеру рядка (інакше буде дорівнює 0). З усіх отриманих номерів рядків функція = МАКС () вибирає найбільший і передає його в якості аргументу для функції = ІНДЕКС (). Ця функція вже повертає остаточний результат відображення значення осередки з шпальти B відповідної номеру обраного рядка.