excel - динамическое суммирование столбцов

Я хотел бы иметь возможность сочетать функции ИНДЕКС-ПОИСКПОЗ (или ВПР) с функцией СУММЕСЛИМН. В приведенном ниже примере я хотел бы найти данные из таблицы, если они соответствуют ссылке на имя (для строки), и суммировать все расходы этого человека за любой заданный месяц.

Выход 1): позволяет мне быть динамичным по ссылке на имя и ссылке на месяц, но не суммирует, а останавливается на первом «январе», который он видит.

Выход 2: суммирует все месяцы, но не меняется, когда я меняю имя.

Кто-нибудь сталкивался с этим раньше? Я не мог найти ответы в Интернете.

Изображение того, что я описываю


excel indexing sumifs
person b-poe    schedule 11.06.2016    source источник
comment
Будут ли ваши месяцы в строке 2 датами Excel, отформатированными так, чтобы отображался только месяц?   -  person Forward Ed    schedule 11.06.2016

Ответы (2)


arrow_upward
1
arrow_downward

Доказательство концепции

Подтверждение концепции

В ячейке C9 используйте следующее:

=SUMPRODUCT(($B$3:$B$5=$B8)*($C$2:$H$2=C$7)*$C$3:$H$5)
person Forward Ed    schedule 11.06.2016
comment
Очень круто. Это работает даже лучше, чем я ожидал, поскольку эту формулу можно использовать даже в том случае, если в именах строк есть кратные числа. Оцените ответ. - person b-poe; 14.06.2016

arrow_upward
1
arrow_downward

Вот формула без массива:

=SUMIF($2:$2,C$8,INDEX($A:$DDD,MATCH($B9,$B:$B,0),0))

INDEX($A:$DDD,MATCH($B9,$B:$B,0),0)) выбирает правильную строку, первую, где совпадают имена, и устанавливает эту строку в качестве местоположения значений для суммирования для СУММЕСЛИ().

![введите описание изображения здесь

person Scott Craner    schedule 12.06.2016
comment
Благодарю за ваш ответ. Это работает очень хорошо. Теперь просто нужно перепроектировать, что именно происходит: p - person b-poe; 14.06.2016
comment
@b-poe у обоих методов есть свои преимущества и недостатки. Какой бы вариант вы ни выбрали, отметьте его как правильный, щелкнув галочку рядом с соответствующим ответом. - person Scott Craner; 14.06.2016
comment
Спасибо за напоминание, Скотт. Преимущества вашего метода в том, что данные могут расширяться, а формула все еще улавливает изменения? - person b-poe; 14.06.2016
comment
Мои преимущества заключаются в том, что это не формула массива и может использовать полные ссылки на столбцы и строки, в то время как СУММПРОИЗВ должен быть ограничен пределами данных, иначе время вычисления будет долгим. Также, если есть большой набор данных, это будет быстрее. Недостатком является то, что это не будет работать с SUMIFS(), поэтому вы застряли только с двумя критериями. И, как вы заметили, он захватит только первую строку, в которой появляется имя, если имена дублируются и не сортируются, вам нужно будет использовать @ForwardEd. - person Scott Craner; 14.06.2016