Завантажте зразок робочої книги
VLOOKUP з урахуванням регістру - Excel
Цей підручник покаже, як виконати VLOOKUP з урахуванням регістру в Excel за допомогою двох різних методів.
Спосіб 1 - VLOOKUP з урахуванням регістру з допоміжним стовпцем
= VLOOKUP (МАКС. (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(РЯД ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Функція VLOOKUP
Функція VLOOKUP використовується для пошуку приблизного або точного відповідності значення в крайньому лівому стовпці діапазону і повертає відповідне значення з іншого стовпця. За замовчуванням VLOOKUP працюватиме лише для значень, які не враховують регістр, наприклад:
VLOOKUP з урахуванням регістру
Комбінуючи VLOOKUP, EXACT, MAX та ROW, ми можемо створити формулу VLOOKUP з урахуванням регістру, яка повертає відповідне значення для нашого VLOOKUP з урахуванням регістру. Пройдемося по прикладу.
У нас є список товарів та їх відповідні ціни (зверніть увагу, що ідентифікатор товару унікальний з урахуванням регістру):
Припустимо, нас попросять отримати ціну за товар, використовуючи його ідентифікатор товару таким чином:
Для цього нам у першу чергу потрібно створити допоміжний стовпець за допомогою ROW:
= Клацніть ROW () і перетягніть (або двічі клацніть), щоб попередньо заповнити всі рядки в діапазоні
Далі об’єднайте VLOOKUP, MAX, EXACT та ROW у формулу так:
= VLOOKUP (МАКС. (ТОЧНИЙ (,)*(РЯД ())), ,, 0)
= VLOOKUP (МАКС. (ТОЧНО (E2, $ B $ 2: $ B $ 7)*(РЯД ($ B $ 2: $ B $ 7))), $ C $ 2: $ D $ 7,2,0)
Як працює формула?
- Функція EXACT перевіряє ідентифікатор елемента в E2 (значення пошуку) щодо значень у B2: B7 (діапазон пошуку) і повертає масив TRUE, де є точна відповідність або FLASE в масиві {FLASE, FLASE, FLASE, FLASE, FLASE, TRUE}.
- Потім цей масив помножується на масив ROW {2, 3, 4, 5, 6, 7} (зверніть увагу, що це відповідає нашому стовпцю -помічнику).
- Функція MAX повертає максимальне значення з отриманого масиву {0,0,0,0,0,7}, що у нашому прикладі становить 7.
- Потім ми використовуємо результат як наше значення пошуку у VLOOKUP і вибираємо наш допоміжний стовпець як діапазон пошуку. У нашому прикладі формула повертає відповідне значення 16,00 доларів США.
Спосіб 2 - VLOOKUP з урахуванням регістру з "віртуальним" допоміжним стовпцем
= VLOOKUP (МАКС. (ТОЧНИЙ (D2, $ B $ 2: $ B $ 7)*(РЯД ($ B $ 2: $ B $ 7))), ВИБІР ({1,2}, ROW ($ B $ 2: $ B $ 7) , $ C $ 2: $ C $ 7), 2,0)
Цей метод використовує ту саму логіку, що і перший метод, але усуває необхідність створення допоміжного стовпця і замість цього використовує CHOOSE і ROW для створення “віртуального” стовпця -помічника так:
= VLOOKUP (MAX (EXACT (,)*(ROW ())), ВИБІР ({1,2}, ROW (),),, 0)
= VLOOKUP (МАКС. (ТОЧНИЙ (D2, $ B $ 2: $ B $ 7)*(РЯД ($ 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
Усі наведені вище приклади працюють точно так само в Google Таблицях, як і в Excel.
