На прошедшем в выходные тренинге по формулам обсуждали вот такую задачку: нужно просуммировать данные за выбранный год и только по штукам или деньгам. А таблица устроена так (плохо, но это жизнь), что в ней есть столбцы со штуками и деньгами попарно. Она не плоская.
Как быть?
Использовать СУММЕСЛИМН / 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))
(но в новых формулах столько функций, что можно и как-нибудь иначе вообще это решить ;) )