Множественный ВПР() - выводим несколько значений по ключевому значению в MS EXCEL history 30 сентября 2020 г.
Группы статей
Функция ВПР() ищет заданное значение в ключевом столбце и выводит значение из соседнего столбца. Ключевой столбец в этом случае не содержит повторов. А что если содержит? Тогда функция выведет только первое встретившееся значение. Напишем формулу, которая выводит все значения, соответствующие искомому. Назовем эту формулу множественный ВПР().
Пусть дана исходная таблица с номерами заказов и товарами. Номера заказов могут повторяться.
У заказа 2 три повтора, в строке 3, 4 и 6. Перечень заказов не сортирован.
Выведем все наименования товаров для заказа 2, а также количество этих товаров.
Так как в ключевом столбце (Заказ) теперь несколько одинаковых значений, то функция ВПР() не годится - она выведет только самое первое, т.е. товар Манго. Чтобы вывести все 3 значения у заказа 2 создадим служебный столбец рядом с исходной таблицей.
Поместим в него формулу =СЧЁТЕСЛИ($B$8:B8;B8)
Она подсчитает номера повторов для каждого заказа. Нужный заказ введем в желтую ячейку. Соответствующие позиции этого заказа подсвечиваются зеленым цветом в исходной таблице с помощью условного форматирования.
В таблице ниже, там где будем выводить товар и его количество для выбранного заказа, с помощью формулы
=СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7)))
определим позиции повтора в исходной таблице (оранжевый столбец в нижней таблице). Это обычная формула (не формула массива).
Наименование товара выведем с помощью простой формулы =ИНДЕКС(C$8:C$14;$B23), аналогично выведем Количество.
Изменив в желтой ячейке номер заказа на 1, нижняя табличка изменится.
Покажем как работает формула =СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7))) для заказа 1:
три выражения $B$8:$B$14=$A$20, $E$8:$E$14=A23 и СТРОКА($B$8:$B$14)-СТРОКА($B$7) порождают 3 массива чисел
первый массив - это номера позиций искомого заказа в исходной таблице. Выражение дает {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}, т.е. ИСТИНА соответствует номерам позиций 1 и 5 (выделено зеленым). Убедиться в этом просто - достаточно выделить выражение в строке формул и нажать клавишу F9
второе выражение дает массив {ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА}, который представляет номер повтора заказа (данный массив соответствует первому повтору). Номер повтора заказа из А23 (1) и из А24 (2) сравнивается номерами повторов заказов в столбце Е. Например, номер повтора 1 есть во многих строках: 1, 2, 3, 7 и часть из них не соответствует заказу №1. Это как раз и показывает массив - значение ИСТИНА стоит у элементов массива №№1, 2, 3, 7
произведение этих массивов дает множество, которое соответствует и номеру заказа (1) и его повтору в исходной таблице (1 в строке 23, и 2 в строке 24). Вот этот массив {1:0:0:0:0:0:0}, где 1 соответствует ИСТИНА. 1 получается перемножением 2-х ИСТИНА, все остальные комбинации ЛОЖЬ*ЛОЖЬ, ЛОЖЬ*ИСТИНА дают 0. Перемножение происходит попарно.
наконец, умножая предыдущий массив на массив порядковых номеров в исходной таблице получим только тот номер, который соответствует и номеру заказа и его повтору. Он будет только один, остальные в массиве будут равны 0. Вот этот массив для заказа 1 и его повтора 2: {0:0:0:0:5:0:0} Это 5-я позиция в исходной таблице.
Функция СУММПРОИЗ() складывает все элементы массива, т.к. ненулевой элемент заведомо только один, то функция отбросит все 0 и выведет позицию. Самые интересные ролики интернета, кино, видео, музыка, образовательные ролики.
Как ускорить Windows, работать в Exel и Office Access.
Лучшие видео клипы, все собрано на канале.
Отечественные сериалы.
Разделы: #Windows #Office #Exel #Access #Music #Audio #Video #Internet #Видеоклипы #Сериалы