Маємо список замовлень з номерами і назвами товарів. Хотілося б, для прикладу, витягувати з таблиці за номером замовлення усі товари, які в нього входять. Приблизно так:
Чудова функція ВПР (VLOOKUP) в такій ситуації допоможе тільки частково, тому що вміє витягувати дані тільки по першому знайденому відповідності, тобто дасть нам тільки Яблука. Для знаходження та вилучення з таблиці всіх найменувань краще використовувати формулу масиву. Ось таку:
= ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; ""); СТРОКА () -5))
Її треба ввести в такий спосіб:
- виділити осередки, куди повинні виводитися результати (в нашому прикладі - це діапазон D6: D20)
- ввести (скопіювати формулу в перший осередок) діапазону
- натиснути Ctrl + Shift + Enter
Віднімання одиниці в фрагменті СТРОКА (B2: B16) -1 робиться через шапки таблиці. З тієї ж причини для компенсації зсуву результуючого діапазону щодо вихідного віднімається число п'ять у фрагменті СТРОКА () - 5
Щоб приховати помилку # ЧИСЛО. яка буде з'являтися в незаповнених комірках результуючого діапазону D6: D20 можна використовувати функції перевірки помилок ЯКЩО і ЕОШ, замінивши нашу формулу трохи складнішою:
= ЕСЛИ (ЕОШ (ІНДЕКС ($ B $ 2: $ B $ 16; НАЙМЕНШИЙ (ЯКЩО ($ E $ 2 = A2: A16; СТРОКА (B2: B16) -1; ""); СТРОКА () - 5))); " "; ІНДЕКС ($ 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 ці функції будуть виглядати так: