Excel: динамическая ссылка на столбец для СУММЕСЛИМН
Искал в интернете решение своей проблемы, но не нашел. В основном я ищу способ использовать SUMIFS и динамически изменять область суммы, которая должна соответствовать месяцу.
Вот скриншот:
Здесь вы можете видеть, что C2:C4 является фиксированным диапазоном. Мне бы хотелось, чтобы столбцы менялись в зависимости от месяца, когда пользователь вводил C8. Пример: для 02.01.2019 в C8 формула примет вид =СУММЕСЛИМН(C2:C4;Д2: Д4;B9)
Я знаю, что мне придется использовать функцию ИНДЕКС,ПОИСКПОЗ, но мне сложно понять, как это сделать.
Пожалуйста помоги.
Ответы 2
Вы можете поместить это в B10 и скопировать вниз.
=SUMIF($A$2:$A$4,A10,INDEX($B$2:$D$4,,MATCH($B$9,$B$1:$D$1,0)))
ПОИСКПОЗ находит положение даты в верхнем диапазоне (в данном случае 1)
ИНДЕКС возвращает соответствующий столбец (т.е. B2:B4)
Остальное, наверное, разберешься...
Не за что. Только бит INDEX возвращает диапазон, в данном случае B2:B4, поэтому он не может вернуть результат в ячейку. Только когда вы заключаете его в SUM (или аналогичную формулу), он может преобразовать это в значение. Диапазон сам по себе не имеет смысла. Есть смысл?
Хорошо, спасибо, это была моя гипотеза. Другой вопрос: я видел, объявляю ли я диапазон как таблицу, уважительно объявляя то, что работало ранее, но как таблица это больше не работает. Я не могу это исправить. Почему это ?
@ polo31 Результатом этого является массив, массив не может поместиться в одну ячейку, отсюда и ошибка. Вы можете использовать Формула > Вычислить формулу, чтобы увидеть, как выглядит этот массив, прежде чем превратиться в ошибку.
Чтобы добавить к точке Джерри, если вы введете эту формулу, выделите 3 ячейки и используйте Ctrl Shift и введите для формулы массива, вы получите 3 возвращаемых значения 2,1,2. Не уверен, что вы имеете в виду под своим вторым вопросом - не могли бы вы уточнить?
Я бы, вероятно, выбрал формулу массива (после ввода формулы используйте Ctrl+Shift+Enter — вы можете убедиться, что это работает, когда увидите фигурные скобки {} вокруг формулы в строке формул) с SUMPRODUCT:
=SUMPRODUCT($B$2:$D$4*($A$2:$A$4=A9)*($B$1:$D$1=B8))
В основном это означает суммирование диапазона B2:D4, где A2:A4 = A9 и где B1:D1 = B8.
Ой да так и есть
Спасибо за попытку ответить, Джерри! я нашел что-то лучше, но я буду иметь это в виду.
Отличный ответ! Спасибо вам большое за это. Просто чтобы понять, почему только =INDEX($B$2:$D$4,,MATCH($B$9,$B$1:$D$1,0)) выдает ошибку #!VALUE? В противном случае он работает в функции СУММЕСЛИ, но я хотел бы понять это поведение!