Индекс/совпадение/суммирование нескольких строк и столбцов

Я пытаюсь получить годовой итог для конкретной учетной записи. Предположим, мы сейчас в июле. Итого с начала года для счета 5340 должно быть 2800.

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

В приведенных ниже формулах AT29=5340 и AT28=7

я пытался

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0),7)) но это дает мне только первую найденную строку.

=SUMPRODUCT((AU4:AU24=AT29)*AV4:BG24) но это дает мне целый год.

=SUMPRODUCT((AU4:AU24=AT29)*INDEX(AV4:BG24,,AT28)) но это дает мне сумму только за июль.

=SUMPRODUCT((AU4:AU24=AT29)*(SUM(INDEX(AV4:AV24,,1):INDEX(AV4:BG24,,AT28)))) но это дает мне кое-что, я понятия не имею, что это такое XD


excel excel-formula
person findwindow    schedule 21.03.2016    source источник
comment
Всегда ли числа в AU идут по порядку? потому что небольшое изменение вашей первой формулы должно работать. =SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0)+countif(AU$4:AU$24,AT29)-1,7))   -  person Scott Craner    schedule 01.04.2016
comment
@ScottCraner Кто удалил все мои посты ;_;   -  person findwindow    schedule 01.04.2016
comment
Сообщество сделало. Я думаю, они согласились с Сиддом, что это не место для этого. Мне было весело, спасибо.   -  person Scott Craner    schedule 01.04.2016
comment
Ах :/ Я отошел, и все исчезло. Спасибо за юмор! Хм, мне нужно изучить вашу формулу...   -  person findwindow    schedule 01.04.2016
comment
@ScottCraner Научи меня быть таким, как ты~ Если ты хочешь ответить на него, я проголосую за него.   -  person findwindow    schedule 02.04.2016

Ответы (2)


arrow_upward
2
arrow_downward

Поскольку ваши данные упорядочены в столбце первичного индекса, вы можете сделать это с помощью одной функции SUM поверх одной функции OFFSET.

Чтобы определить область, которую вы будете суммировать, начните с верхнего левого угла выше и слева от ваших данных — скажем, это A1 (из ваших примеров я не могу точно сказать, как на самом деле настроена ваша страница). Вы захотите перемещаться вниз по количеству строк, пока не СООТВЕТСТВУЕТЕ учетной записи, которую вы ищете. Вам нужно перейти на правую 1 колонку, потому что вы всегда будете начинать с января. Затем вы выбираете столько строк, сколько совпадений для этого номера учетной записи в ваших данных. Вы выбираете столько столбцов, сколько месяцев на временной шкале вашего теста. Вместе это выглядит примерно так:

=SUM(OFFSET(A1,MATCH(A12,A1:A9,0),1,COUNTIFS(A2:A9,A12),A13))

Примечание. Это предполагает, что ваши учетные записи идут от A2: A9, и что вы ввели указанный номер учетной записи в ячейке A12 и что вы подсчитали количество месяцев в году до настоящего времени в ячейке A13.

TL;DR: суммируйте двумерное поле, которое начинается слева вверху с первого экземпляра конкретной учетной записи, идет вниз на столько строк, сколько экземпляров этой учетной записи, и идет вправо для столько столбцов, сколько месяцев в году на сегодняшний день.

person Grade 'Eh' Bacon    schedule 21.03.2016
comment
Ячейка 5000 находится на AU4, поэтому я сделал =SUM(OFFSET(AU4,MATCH(AT29,AU4:AU24,0),1,COUNTIFS(AU4:AU24,AT29),AT28)), но не смог. Не знаком с offset, так что поищу и посмотрю, что я делаю.... - person findwindow; 21.03.2016
comment
Хм формула должна работать. offset аргументы - это 10, 1, 4 и 7, что правильно... так что не так XD - person findwindow; 21.03.2016
comment
@findwindow Тогда вы захотите, чтобы эта формула начиналась с AU3, а не с AU4. Это связано с тем, что вы переместились вниз на количество строк, равное точке, в которой вы СООТВЕТСТВУЕТЕ AT29, поэтому, вероятно, вы сейчас перемещаетесь вниз на 1 строку слишком много. - person Grade 'Eh' Bacon; 21.03.2016
comment
О да, ах. Если 1 означает 1 столбец сверху, 10 означает 10 строк вниз, но мне нужно 11 XD Спасибо! - person findwindow; 21.03.2016

arrow_upward
2
arrow_downward

ЕСЛИ числа в AU в порядке, будет работать следующая энергонезависимая формула:

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$‌​4:AU$24,0)+COUNTIF(AU$4:AU$24,AT29)-1,7))

Использование COUNTIF() расширит строки на правильное количество строк.

person Scott Craner    schedule 01.04.2016