Знайдемо текстові значення, що задовольняють заданому користувачем критерієм з урахуванням регістра. Пошук здійснюватимемо в діапазоні з повторюваними значеннями. При наявності повторів, можна очікувати, що критерієм буде відповідати кілька значень. Для їх виведення в окремий діапазон зручно використовувати формули масиву.
Нехай Вихідний список значень (наприклад, перелік інструментів) знаходиться в діапазоні A5: A13 (див. Файл прикладу).
Виведемо в окремий діапазон значення, які відповідають критеріям з урахуванням регістра. Розглянемо різні варіанти пошуку.
А. Знайти значення, які містять критерій
Для зручності написання формул створимо Іменований діапазон Список.
Виведемо всі значення, в яких міститься текст-критерій (наприклад, слово дриль). Слово Дриль виведено не буде, тому що порівняння проводиться з урахуванням регістра (буква Д - в верхньому регістрі, а в критерії буква д знаходиться в нижньому регістрі). Критерій вводиться в осередок С2. Перелік знайдених значень будемо поміщати в окремий стовпець.
Для створення списку, що містить знайдені значення, скористаємося формулою масиву:
= ІНДЕКС (A: A; НАЙМЕНШИЙ (
ЯКЩО (ЕСЛІОШІБКА (ШУКАТИ ($ C $ 2; Список); 0)> 0; СТРОКА (Список); 30);
СТРОКА (ДВССИЛ ( "A1: A" ЧСТРОК (Список)))))
Для коректної роботи цієї формули осередок А30 повинна бути порожньою. Докладний розбір роботи подібної формули наведено в статті Пошук текстових значень у списках. Частина 1. Звичайний пошук.
Б. Знайти значення, які збігаються з критерієм (точний збіг)
В цьому випадку будуть виведені всі значення, які збігаються з критерієм (з урахуванням регістру). Критерій вводиться в осередок E2.
Для створення списку, що містить знайдені значення, скористаємося формулою масиву:
= ІНДЕКС (A: A; НАЙМЕНШИЙ (
ЯКЩО (збігаючись ($ E $ 2; Список); СТРОКА (Список); 30);
СТРОКА (ДВССИЛ ( "A1: A" ЧСТРОК (Список)))))
B. Знайти значення, які починаються з критерію
В цьому випадку будуть виведені всі значення, які починаються або збігаються з критерієм (з урахуванням регістру). Критерій вводиться в осередок G2.
Для створення списку, що містить знайдені значення, скористаємося формулою масиву:
= ІНДЕКС (A: A; НАЙМЕНШИЙ (
ЯКЩО (ЕСЛІОШІБКА (ШУКАТИ ($ G $ 2; Список); 0) = 1; СТРОКА (Список); 30);
СТРОКА (ДВССИЛ ( "A1: A" ЧСТРОК (Список)))))
Г. Знайти значення, які закінчуються на критерій
В цьому випадку будуть виведені всі значення, які закінчуються або збігаються з критерієм. Критерій вводиться в осередок I2.
Для створення списку, що містить знайдені значення, скористаємося формулою масиву:
= ІНДЕКС (A: A; НАЙМЕНШИЙ (
ЯКЩО (збігаючись ($ I $ 2; ПРАВСИМВ (Список; ДЛСТР ($ I $ 2))); СТРОКА (Список); 30);
СТРОКА (ДВССИЛ ( "A1: A" ЧСТРОК (Список)))))