зміст
У другій частині нашого уроку про функцію VPR (VLOOKUP) в Excel, ми розберемо кілька прикладів, які допоможуть вам направити всю силу VPR для вирішення найамбітніших завдань Excel. У прикладах передбачається, що ви вже маєте базові знання про те, як працює ця функція. Якщо ні, можливо, вам буде цікаво почати з першої частини цього підручника, де пояснюється синтаксис і базове використання. VPR. Ну що ж, почнемо.
Пошук в Excel за кількома критеріями
функція VPR в Excel є дійсно потужним інструментом для виконання пошуку певного значення в базі даних. Однак є суттєве обмеження – його синтаксис дозволяє шукати лише одне значення. Що робити, якщо ви хочете шукати за кількома умовами? Ви знайдете рішення нижче.
Приклад 1: пошук за 2 різними критеріями
Припустимо, у нас є список замовлень і ми хочемо знайти Кількість товару (Кількість), на основі двох критеріїв – Ім'я клієнта (Замовник) и Назва продукту (Продукція). Справа ускладнюється тим, що кожен із покупців замовляв декілька видів товару, як видно з таблиці нижче:
регулярна функція VPR не працюватиме в цьому сценарії, оскільки він поверне перше знайдене значення, яке відповідає заданому значенню пошуку. Наприклад, якщо ви хочете дізнатися кількість товару солодощіна замовлення покупця Джеремі Хілл, запишіть таку формулу:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– ця формула поверне результат 15відповідний продукту яблука, тому що це перше значення, яке відповідає.
Є простий обхідний шлях – створити додатковий стовпець, у якому об’єднати всі потрібні критерії. У нашому прикладі це стовпці Ім'я клієнта (Замовник) и Назва продукту (Продукція). Не забувайте, що об’єднаний стовпець завжди має бути крайнім лівим стовпцем у діапазоні пошуку, оскільки це лівий стовпець, у якому функція VPR дивиться вгору, коли шукає значення.
Отже, ви додаєте в таблицю допоміжний стовпець і копіюєте таку формулу на всі її комірки: =B2&C2. Якщо ви хочете, щоб рядок був більш читабельним, ви можете розділити комбіновані значення пробілом: =B2&» «&C2. Після цього ви можете використовувати таку формулу:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Де клітина B1 містить об'єднане значення аргументу lookup_value (пошукове_значення) і 4 – Аргумент col_index_num (номер_стовпця), тобто номер стовпця, що містить дані, які потрібно отримати.
Приклад 2: VLOOKUP за двома критеріями з переглядом таблиці на іншому аркуші
Якщо вам потрібно оновити основну таблицю (Main table), додавши дані з другої таблиці (Lookup table), яка розташована на іншому аркуші або в іншій книзі Excel, то ви можете зібрати потрібне значення безпосередньо у формулі, яку ви вставляєте. в основну таблицю.
Як і в попередньому прикладі, вам знадобиться допоміжний стовпець у таблиці пошуку з комбінованими значеннями. Цей стовпець має бути крайнім лівим у діапазоні пошуку.
Так формула с VPR може бути таким:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Тут стовпці B і C містять імена клієнтів і назви продуктів відповідно, а також посилання Замовлення!$A&$2:$D$2 визначає таблицю для пошуку на іншому аркуші.
Щоб зробити формулу більш читабельною, ви можете дати назву діапазону перегляду, і тоді формула виглядатиме набагато простіше:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Щоб формула працювала, значення в крайньому лівому стовпці таблиці, яку ви дивитеся, повинні бути об'єднані точно так само, як і в критеріях пошуку. На малюнку вище ми об'єднали значення u2bu2band і поставили пробіл між ними, так само, як це потрібно зробити в першому аргументі функції (BXNUMX& «» & CXNUMX).
Пам'ятайте! функція VPR обмежено до 255 символів, він не може шукати значення, яке містить більше 255 символів. Майте це на увазі та переконайтеся, що довжина бажаного значення не перевищує цю межу.
Згоден, що додавання допоміжної колонки - не найелегантніше і не завжди прийнятне рішення. Ви можете зробити те саме без допоміжного стовпця, але для цього знадобиться набагато складніша формула з комбінацією функцій ІНДЕКС (ІНДЕКС) і MATCH (БІЛЬШ ВИКРИТИЙ).
Витягуємо 2-е, 3-е і т. д. значення за допомогою VLOOKUP
Ви це вже знаєте VPR може повернути лише одне відповідне значення, точніше, перше знайдене. Але що, якщо це значення повторюється кілька разів у масиві, що переглядається, і ви хочете витягти 2-й або 3-й з них? А якщо всі цінності? Проблема здається складною, але вихід є!
Припустимо, що один стовпець таблиці містить імена клієнтів (Customer Name), а інший стовпець містить продукти (Product), які вони купили. Давайте спробуємо знайти 2-й, 3-й і 4-й предмети, придбані даним клієнтом.
Найпростіший спосіб - додати допоміжний стовпець перед стовпцем Ім'я клієнта і заповніть його іменами клієнтів із номером повторення кожного імені, наприклад, Джон Доу1, Джон Доу2 і т. д. Ми зробимо трюк з нумерацією за допомогою функції COUNTIF (COUNTIF), враховуючи, що імена клієнтів знаходяться в стовпці B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Після цього можна використовувати звичайну функцію VPRзнайти необхідний порядок. Наприклад:
- знайти 2-й товар на замовлення клієнта Ден Браун:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - знайти 3-й товар на замовлення клієнта Ден Браун:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Фактично, ви можете ввести посилання на клітинку як значення пошуку замість тексту, як показано на наступному малюнку:
Якщо ви тільки шукаєте 2-e повторення, ви можете зробити це без допоміжного стовпця, створивши більш складну формулу:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
У цій формулі:
- $ F $ 2 – комірка, яка містить ім’я покупця (не змінюється, зверніть увагу – посилання абсолютне);
- $ B $ – колонка Ім'я клієнта;
- Таблиця4 – Ваш столик (це місце також може бути звичайним асортиментом);
- $ C16 – кінцева комірка таблиці або діапазону.
Ця формула знаходить лише друге відповідне значення. Якщо вам потрібно витягнути решту повторів, скористайтеся попереднім рішенням.
Якщо вам потрібен список усіх збігів – функція VPR це не помічник, оскільки він повертає лише одне значення за раз – крапку. Але в Excel є функція ІНДЕКС (INDEX), який легко впорається з цим завданням. Як буде виглядати така формула, ви дізнаєтеся з наступного прикладу.
Отримати всі повторення потрібного значення
Як зазначено вище VPR не може витягти всі повторювані значення зі сканованого діапазону. Для цього вам потрібна дещо складніша формула, яка складається з кількох функцій Excel, наприклад ІНДЕКС (ІНДЕКС), МАЛИЙ (МАЛИЙ) і ROW (ЛІНІЯ)
Наприклад, наведена нижче формула знаходить усі повторення значення з клітинки F2 у діапазоні B2:B16 і повертає результат із тих самих рядків у стовпці C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Введіть цю формулу масиву в кілька суміжних клітинок, наприклад клітинок F4: F8як показано на малюнку нижче. Кількість клітинок має дорівнювати або перевищувати максимально можливу кількість повторів шуканого значення. Не забудьте натиснути Ctrl + Shift + Enterщоб правильно ввести формулу масиву.
Якщо вам цікаво зрозуміти, як це працює, давайте трохи зануримося в деталі формули:
Частина 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – порівняти значення в комірці F2 з кожним із значень у діапазоні B2:B16. Якщо збіг знайдено, то вираз STRING(C2:C16)-1 повертає номер відповідного рядка (значення -1 дозволяє не включати рядок заголовка). Якщо збігів немає, функція IF (IF) повертає порожній рядок.
Результат функції IF (ЯКЩО) буде такий горизонтальний масив: {1,"",3,"",5,"","","","","","",12,"","",""}
Частина 2:
ROW()-3
СТРОКА()-3
Ось функція ROW (LINE) виконує роль додаткового лічильника. Оскільки формула копіюється в клітинки F4:F9, ми віднімаємо число 3 з результату функції, щоб отримати значення 1 в камері F4 (рядок 4, відняти 3), щоб отримати 2 в камері F5 (рядок 5, відняти 3) і так далі.
Частина 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функція МАЛИЙ (МАЛИЙ) повертається н-ой найменше значення в масиві даних. У нашому випадку, яку позицію (з найменшої) повертати, визначає функція ROW (ЛІНІЯ) (див. Частину 2). Отже, для клітини F4 функція МАЛИЙ({масив};1) Умови повернення 1-й (найменший) елемент масиву, тобто 1. Для клітини F5 Умови повернення 2-й найменший елемент у масиві, тобто 3, І т.д.
Частина 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
функція ІНДЕКС (INDEX) просто повертає значення певної клітинки в масиві С2: С16. Для клітини F4 функція ІНДЕКС($C$2:$C$16) повернеться яблукадля F5 функція ІНДЕКС($C$2:$C$16) повернеться солодощі і так далі.
Частина 5:
IFERROR()
ЕСЛИОШИБКА()
Нарешті, ми поміщаємо формулу всередину функції ПОМИЛКА (ЯКЩО ПОМИЛКА), тому що навряд чи ви будете задоволені повідомленням про помилку #AT (#N/A), якщо кількість комірок, у які копіюється формула, менша за кількість повторюваних значень у діапазоні, який переглядається.
XNUMXD пошук за відомим рядком і стовпцем
Виконання XNUMXD пошуку в Excel передбачає пошук значення за відомим номером рядка та стовпця. Іншими словами, ви витягуєте значення клітинки на перетині певного рядка та стовпця.
Отже, давайте звернемося до нашої таблиці і запишемо формулу з функцією VPR, яка знайде інформацію про вартість проданих лимонів у березні.
Є кілька способів виконати XNUMXD пошук. Перегляньте варіанти та виберіть той, який вам найбільше підходить.
Функції VLOOKUP і MATCH
Ви можете використовувати купу функцій VPR (VLOOKUP) і БІЛЬШ ЕКСПОЗИЦІЙНИЙ (MATCH), щоб знайти значення на перетині полів Назва продукту (рядок) і місяць (стовпець) відповідного масиву:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Наведена вище формула є звичайною функцією VPR, який шукає точний збіг значення «Лимони» в клітинках від A2 до A9. Але оскільки ви не знаєте, у якому стовпці знаходяться розпродажі в березні, ви не зможете встановити номер стовпця для третього аргументу функції. VPR. Замість цього використовується функція БІЛЬШ ЕКСПОЗИЦІЙНИЙщоб визначити цей стовпець.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
У перекладі на людську мову ця формула означає:
- Шукаємо символи «Мар» – аргумент lookup_value (пошукове_значення);
- Перегляд клітинок від A1 до I1 – аргумент пошуковий_масив (масив_пошуку);
- Повертає точну відповідність – аргумент match_type (тип_збігу).
використання 0 у третьому аргументі ви говорите функції БІЛЬШ ЕКСПОЗИЦІЙНИЙ знайдіть перше значення, яке точно відповідає тому значенню, яке ви шукаєте. Це еквівалентно значенню ПОМИЛКОВИЙ (FALSE) для четвертого аргументу VPR.
Ось як ви можете створити формулу двостороннього пошуку в Excel, також відому як двовимірний пошук або двонаправлений пошук.
Функція SUMPRODUCT
функція SUMPRODUCT (SUMPRODUCT) повертає суму добутків вибраних масивів:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Функції INDEX і MATCH
У наступній статті я детально поясню ці функції, тому поки що ви можете просто скопіювати цю формулу:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Іменовані діапазони та оператор перетину
Якщо ви не любите всі ці складні формули Excel, вам може сподобатися цей наочний і запам’ятовуваний спосіб:
- Виберіть таблицю, відкрийте вкладку Формули (Формули) і натисніть Створити з виділення (Створити з виділеного).
- Поставте галочки Верхній ряд (на рядку вище) і Ліва колонка (у колонці зліва). Microsoft Excel призначатиме назви діапазонам із значень у верхньому рядку та лівому стовпці вашої електронної таблиці. Тепер ви можете здійснювати пошук за цими назвами безпосередньо без створення формул.
- У будь-яку порожню клітинку напишіть = ім'я_рядка ім'я_стовпця, наприклад так:
=Лимони Мар
… або навпаки:
=Мар Лимонс
Пам’ятайте, що імена рядків і стовпців повинні бути розділені пробілом, який у цьому випадку працює як оператор перетину.
Коли ви вводите ім’я, Microsoft Excel показує спливаючу підказку зі списком відповідних імен, як і під час введення формули.
- прес Що натомість? Створіть віртуальну версію себе у і перевірте результат
Загалом, який би з наведених вище методів ви не вибрали, результат двовимірного пошуку буде однаковим:
Використання кількох VLOOKUP в одній формулі
Трапляється, що основна таблиця і таблиця пошуку не мають жодного спільного стовпця, і це заважає вам використовувати звичайну функцію VPR. Однак є інша таблиця, яка не містить цікаву для нас інформацію, але має загальний стовпець з основною таблицею і таблицею пошуку.
Давайте розглянемо наступний приклад. У нас є Основна таблиця зі стовпцем SKU (новий), куди потрібно додати стовпець із відповідними цінами з іншої таблиці. Крім того, у нас є 2 таблиці пошуку. Перший (таблиця пошуку 1) містить оновлені числа SKU (новий) і назви товарів, а друга (Таблиця пошуку 2) – назви товарів і старі номери SKU (старий).
Щоб додати ціни з другої таблиці пошуку до основної таблиці, ви повинні виконати дію, відому як подвійне VPR або вкладені VPR.
- Напишіть функцію VPR, який знаходить назву продукту в таблиці Пошукова таблиця 1використання SKU, як бажане значення:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)тут New_SKU – іменований діапазон $A:$B в таблиці Пошукова таблиця 1, 2 – це колонка B, яка містить назви товарів (див. малюнок вище)
- Напишіть формулу для вставки цін із таблиці Пошукова таблиця 2 на основі відомих назв продуктів. Для цього вставте формулу, яку ви створили раніше, як значення пошуку для нової функції VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)тут ціна – іменований діапазон $ A: $ C в таблиці Пошукова таблиця 2, 3 це стовпець C, що містить ціни.
На малюнку нижче показано результат, який повертає створена нами формула:
Динамічна підстановка даних з різних таблиць за допомогою VLOOKUP і INDIRECT
По-перше, давайте уточнимо, що ми маємо на увазі під виразом «Динамічна підстановка даних з різних таблиць», щоб переконатися, що ми правильно розуміємо один одного.
Бувають ситуації, коли є кілька аркушів з даними одного формату, і необхідно витягти необхідну інформацію з певного аркуша в залежності від значення, яке вводиться в дану клітинку. Думаю, простіше пояснити це на прикладі.
Уявіть, що у вас є звіти про продажі для кількох регіонів з однаковими товарами та в одному форматі. Ви хочете знайти цифри продажів для певного регіону:
Якщо таких звітів всього два, то можна використовувати ганебно просту формулу з функціями VPR и IF (IF), щоб вибрати потрібний звіт для пошуку:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
де:
- $D$2 — клітинка, що містить назву товару. Зауважте, що тут ми використовуємо абсолютні посилання, щоб уникнути зміни значення пошуку під час копіювання формули в інші комірки.
- $ 3 – клітинка з назвою регіону. Ми використовуємо абсолютне посилання на стовпець і відносне посилання на рядок, оскільки плануємо скопіювати формулу в інші комірки того самого стовпця.
- FL_Sales и CA_Sales – назви таблиць (або іменованих діапазонів), які містять відповідні звіти про продажі. Ви, звичайно, можете використовувати, наприклад, звичайні назви аркушів і посилання на діапазон клітинок «FL Sheet»!$A$3:$B$10, але іменовані діапазони набагато зручніші.
Однак, коли таких таблиць багато, функція IF не найкраще рішення. Замість цього ви можете використовувати функцію НЕПРАВИЛЬНО (INDIRECT), щоб повернути потрібний діапазон пошуку.
Як ви, мабуть, знаєте, функція НЕПРАВИЛЬНО використовується для повернення посилання, заданого текстовим рядком, що є саме тим, що нам зараз потрібно. Отже, сміливо замініть у наведеній вище формулі вираз на функцію IF зв'язати з функцією НЕПРАВИЛЬНО. Ось така комбінація VPR и НЕПРАВИЛЬНО чудово працює з:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
де:
- $D$2 – це комірка з назвою товару, вона незмінна через абсолютне посилання.
- $ 3 це комірка, що містить першу частину назви регіону. У нашому прикладі це FL.
- _Продажі – спільна частина назви всіх іменованих діапазонів або таблиць. У поєднанні зі значенням у клітинці D3 воно утворює повне ім’я необхідного діапазону. Нижче наведено деякі подробиці для тих, хто тільки починає користуватися цією функцією НЕПРАВИЛЬНО.
Як працюють INDIRECT і VLOOKUP
Спочатку дозвольте мені нагадати вам синтаксис функції НЕПРАВИЛЬНО (НЕПРЯМО):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Першим аргументом може бути посилання на клітинку (стиль A1 або R1C1), ім’я діапазону або текстовий рядок. Другий аргумент визначає, який стиль посилання міститься в першому аргументі:
- A1якщо аргумент є ІСТИННИЙ КОД (ІСТИНА) або не вказано;
- R1C1, Якщо FAS E (ПОМИЛКОВИЙ).
У нашому випадку посилання має стиль A1, тому ви можете пропустити другий аргумент і зосередитися на першому.
Отже, повернемося до наших звітів про продажі. Якщо ви пам'ятаєте, то кожен звіт - це окрема таблиця, розташована на окремому аркуші. Щоб формула працювала правильно, ви повинні назвати свої таблиці (або діапазони), і всі імена повинні мати спільну частину. Наприклад, так: CA_Sales, FL_Sales, TX_Sales і так далі. Як бачите, «_Sales» присутній у всіх назвах.
функція НЕПРАВИЛЬНО з’єднує значення в стовпці D і текстовий рядок «_Sales», таким чином повідомляючи VPR в якій таблиці шукати. Якщо клітинка D3 містить значення «FL», формула шукатиме в таблиці FL_Sales, якщо «CA» – в табл CA_Sales і так далі.
Результат функцій VPR и НЕПРАВИЛЬНО буде наступним:
Якщо дані знаходяться в різних книгах Excel, то вам потрібно додати назву книги перед іменованим діапазоном, наприклад:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Якщо функція НЕПРАВИЛЬНО посилається на іншу книгу, ця книга має бути відкритою. Якщо він закритий, функція повідомить про помилку. #REF! (#SSYL!).