rss
  •  

Как получить по формуле несколько значений из списка по заданному критерию

| Категория: Приемы и советы, Формулы и функции |

12

Такие замечательные функции, как ВПР, ПОИСКПОЗ+ИНДЕКС, ГПР позволяют по известному критерию найти только одно значение из списка: самое первое при просмотре сверху вниз для вертикальных таблиц или самое первое при просмотре слева направо при просмотре слева направо. А что делать, если нужно получить не одно, а все значения. Например, выбрать для каждой компании объекты, которые ей подчиняются и не с помощью фильтрации, а именно по формулам. Найдем ответ и на эту задачу!

vprs1.png

Решение этой задачи будет с использованием формулы массива, поэтому необходимо заранее выделить предполагаемый диапазон ячеек (можно с запасом) и написать формулу, завершив её формулой массива, т.е. нажав клавиши Ctrl+Shift+Enter:

vprs2.png

Сперва в ответ получаем значения из таблицы, а когда они заканчиваются, то сообщение об ошибке. Подобное, как и всегда, можно убрать с применением функции ЕСЛИОШИБКА.

Разберем формулу «по косточкам»:

  • $C$2:$C$16 — диапазон ячеек-результата, т.е. объекты.
  • НАИМЕНЬШИЙ(Массив;K) — возвращает k-е наименьшее значение в множестве данных:
    Массив определяется по формуле: ЕСЛИ(D2:D16=$F$2;СТРОКА(D2:D16)-СТРОКА($D$1);»»), т.е. проверяется в ячейках D2:D16 соответствие выбранной компании (критерий) в ячейке F2, а затем определяются позиции (номера по порядку) в выделенной области, но так как диапазон выделен без заголовков (с ячейки D2), то необходимо скорректировать на строку-заголовок, т.к. СТРОКА($D$1), а в случае если совпадение критерия не найдено, то позиция не определяется — «».
    K — позиция в диапазоне данных.

vprs7.png

Окончательно формула будет выглядеть:

vprs5.png

В этом случае, порядок расположения объектов следует сверху вниз. Если требуется обратный порядок, т.е. снизу вверх, то функцию НАИМЕНЬШИЙ надо заменить на НАИБОЛЬШИЙ:

vprs6.png

В случае, если ответ необходимо получать данные то одного столбца, то другого, то необходимо расширить массив в функции ИНДЕКС и определить номер столбца с помощью функции ПОИСКПОЗ:

vprs8.png




Оставьте комментарий!

На сообщение “Как получить по формуле несколько значений из списка по заданному критерию” комментариев 12

  1. Катерина:

    Мощная формула! спасибо огромное

  2. Денис Борисович:

    СУПЕР! То что нужно!!! СПАСИБО огромное!!! Как Вы до этого всего доходите??? )))

  3. Ольга Кулешова:

    С чувством, с толком, с расстановкой 🙂

  4. MichaelTHAR:

    ой… какая прелесть…

  5. MichaelTHAR:

    Применил к своей таблице — и … работает!

  6. Richard:

    Присоединяюсь. Всё выше сказанное работает отлично.

  7. Марина:

    Не могу сказать, что решение не очень простое, но разобраться можно!

  8. Алексей:

    Спасибо! Круто! Подскажите, каким образом доработать формулу, чтобы она выводила значения не только по одному критерию (компания), а по двум (и более, если возможно)???

  9. Ольга Кулешова:

    Алексей, надо думу думать — так сходу не могу сказать 🙂

  10. НаталиКо:

    Огого какая мощная формула!

  11. Дмитрий:

    Мегамощная формула.

  12. Константин:

    Да уж, такое и не приснится!