Повторне використання VLOOKUP (VLOOKUP)

зміст

У нас є список замовлень з номерами та назвами товарів. Я хотів би, наприклад, витягнути зі столу за номером замовлення всі товари, які в нього входять. Більш-менш так:

 

Чудова функція VLOOKUP (ВПР) у такій ситуації це допоможе лише частково, оскільки здатне витягти дані лише за першим знайденим збігом, тобто дасть нам лише яблука. Щоб знайти та витягти всі елементи з таблиці, краще використовувати формулу масиву. Як цей:

=ІНДЕКС($B$2:$B$16;ОСТАННЯ(IF($ E $ 2=A2: A16;ЛІНІЯ(В2: В16)-1;»»);ЛІНІЯ()-5))

Його необхідно ввести наступним чином:

  1. виберіть комірки, де мають відображатися результати (у нашому прикладі це діапазон D6:D20)
  2. ввести (скопіювати формулу в першу клітинку) діапазону
  3. press Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у

Одиничне віднімання у фрагменті STRING(B2:B16)-1 виконується через заголовок таблиці. З цієї ж причини, щоб компенсувати зсув отриманого діапазону відносно вихідного, у фрагменті віднімають цифру п'ять STRING()-5

Щоб приховати #NUM! помилка, яка з’явиться в порожніх комірках в результуючому діапазоні D6:D20, ви можете скористатися функціями перевірки помилок IF і EOSH, замінивши нашу формулу на трохи складнішу:

=ЯКЩО(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

В Excel 2007 з'явилася більш зручна функція IFERROR - вона дозволяє більш компактно вирішити задачу:

=ЯКЩОПОМИЛКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

В англійській версії Excel ці функції виглядатимуть так:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

  • Використання функції VLOOKUP для пошуку даних у таблиці
  • Покращена версія функції VLOOKUP2, яка може здійснювати пошук у будь-якому стовпці, а не лише за першим значенням
  • Функції VLOOKUP2 і VLOOKUP3 із надбудови PLEX
  • Що таке формули масивів і для чого вони використовуються?

 

залишити коментар