Функция ВПР() ищет заданное значение в ключевом столбце и выводит значение из соседнего столбца. Ключевой столбец в этом случае не содержит повторов. А что если содержит? Тогда функция выведет только первое встретившееся значение. Напишем формулу, которая выводит все значения, соответствующие искомому. Назовем эту формулу множественный ВПР().
Пусть дана исходная таблица с номерами заказов и товарами. Номера заказов могут повторяться.
У заказа 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:
© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved
Комментарии