Telegram-канал lemur_excel - Магия Excel: Неотсортированное

Магия Excel

28 мая 2025 08:10

СУММЕСЛИМН по выбранному товару (поиск строки с помощью ПОИСКПОЗ)

На прошедшем в выходные тренинге по формулам обсуждали вот такую задачку: нужно просуммировать данные за выбранный год и только по штукам или деньгам. А таблица устроена так (плохо, но это жизнь), что в ней есть столбцы со штуками и деньгами попарно. Она не плоская.

Как быть?
Использовать СУММЕСЛИМН / SUMIFS, благо в условиях функции можно использовать символ подстановки (* = любой текст). И благо диапазоны суммирования и условия могут быть горизонтальными, а не только вертикальными.

В нашем случае переменная часть нужных заголовков — это месяцы. Они меняются. А нужный показатель (штуки или деньги) — нет. Соответственно, если нам нужны все продажи в деньгах за 2024 год:

=СУММЕСЛИМН(2:2;1:1;"Деньги*2024")

Все продажи в штуках за все время:

=СУММЕСЛИМН(2:2;1:1;"Штуки*")

Но вторая строка здесь - это конкретный товар. И коллеги на тренинге задали правильный вопрос - а как суммировать по выбранному (в выпадающем списке) товару?
Решили так:
Находим строку с помощью ПОИСКПОЗ / MATCH с выбранным товаром:

ПОИСКПОЗ(нужный товар;список товаров;0)
ПОИСКПОЗ(A11;A1:A7;0)

Делаем ссылку на строку с этим номером - то есть добавляем двоеточие и еще раз этот же номер

ПОИСКПОЗ(A11;A1:A7;0) & ":" & ПОИСКПОЗ(A11;A1:A7;0)

Такая конструкция вернет 3:3, если выбранный товар в третьей строке.
Но это текст. Превратим его в активную ссылку с помощью ДВССЫЛ / INDIRECT и засунем в СУММЕСЛИМН:

=СУММЕСЛИМН(ДВССЫЛ(ПОИСКПОЗ(A11;A1:A7;0) & ":" & ПОИСКПОЗ(A11;A1:A7;0));1:1;B10)

В новой версии можно с помощью LET один раз найти номер строки, а не вычислять его дважды:

=LET(строка;ПОИСКПОЗX(A11;A1:A7);
СУММЕСЛИМН(ДВССЫЛ(строка&":"&строка); 1:1 ;B10))

(но в новых формулах столько функций, что можно и как-нибудь иначе вообще это решить ;) )