У нас є список замовлень з номерами та назвами товарів. Я хотів би, наприклад, витягнути зі столу за номером замовлення всі товари, які в нього входять. Більш-менш так:
Чудова функція VLOOKUP (ВПР) у такій ситуації це допоможе лише частково, оскільки здатне витягти дані лише за першим знайденим збігом, тобто дасть нам лише яблука. Щоб знайти та витягти всі елементи з таблиці, краще використовувати формулу масиву. Як цей:
=ІНДЕКС($B$2:$B$16;ОСТАННЯ(IF($ E $ 2=A2: A16;ЛІНІЯ(В2: В16)-1;»»);ЛІНІЯ()-5))
Його необхідно ввести наступним чином:
- виберіть комірки, де мають відображатися результати (у нашому прикладі це діапазон D6:D20)
- ввести (скопіювати формулу в першу клітинку) діапазону
- 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
- Що таке формули масивів і для чого вони використовуються?