Excel: формула Sumifs с возможным индексом соответствия?

Я пытаюсь создать СУММЕСЛИМН, у которого есть ум для проверки «дочерней» строки в диапазоне данных, а затем с помощью «родительского» идентификационного номера (указанного в дочерней строке) проверяются данные и суммы родителей. Я вставил изображение таблицы ниже, которая дает фиктивный набор данных. Желаемые результаты такой формулы будут:

Ребенок с родителем в фазе 1: 0 долларов

Ребенок с родителем на этапе 2: 80 долларов США

Ребенок с родителем в фазе 3: 10 долларов США

Заранее благодарим за помощь!

Пример набора данных

Или как текст:

ID  Type    Parent ID   Phase   Finances
1   Single              1       $10
2   Parent              2       $10
3   Child   2                   $10
4   Single              3       $10
5   Parent              3       $10
6   Child   5                   $10
7   Single              1       $10
8   Parent              2       $10
9   Child   8                   $10
10  Child   8                   $10
11  Child   8                   $10
12  Child   8                   $10
13  Child   8                   $10
14  Child   8                   $10
15  Child   8                   $10

excel excel-formula sumifs
person Andy_NPT    schedule 18.05.2016    source источник
comment
Образец данных в формате ASCII / текст намного предпочтительнее, чем снимок экрана.   -  person Forward Ed    schedule 18.05.2016
comment
Работает ли указанное выше редактирование?   -  person Andy_NPT    schedule 18.05.2016
comment
Если мой ответ ниже был недоразумением, разве не будет 80 детей с родителями в фазе 2? поскольку оба родителя 8 и 2 находятся в фазе 2? а дочерний элемент с родителем в фазе 1 равен 0, поскольку ни один родитель не находится в фазе 1?   -  person Forward Ed    schedule 18.05.2016
comment
Извините, вы правы ... моя ошибка. Да, значит, исходя из приведенных выше данных, должно быть:   -  person Andy_NPT    schedule 18.05.2016
comment
Ребенок с родителем в фазе 1: 0 долларов США Ребенок с родителем в фазе 2: 80 долларов США Ребенок с родителем в фазе 3: 10 долларов США   -  person Andy_NPT    schedule 18.05.2016
comment
допустимо ли присвоить дочернему элементу фазу на основе родителя?   -  person Forward Ed    schedule 18.05.2016

Ответы (2)


arrow_upward
1
arrow_downward

Предполагая, что ваша таблица находится в диапазоне от A1 до E16.

Создайте вспомогательный столбец, который заполняет пустые фазы. Пустые фазы будут заполнены при условии, что они имеют ту же фазу, что и родительский идентификатор. Поэтому в F1 поместите следующую формулу и скопируйте до F16:

=IF(D2<>"",D2,VLOOKUP(C2,$A$2:$D$16,4,0))

Теперь мы можем использовать sumproduct и взять сумму только потомков и фазы 2. В H1 – J3 я построил сводную таблицу того, что вы искали. В H1 – J1 я использовал заголовки, а в H2 – I 4 я определил, что искал. В J2 - J4 ​​я использовал формулу для определения финансов следующим образом:

Type   |  Phase  |  Finance
Child  |    1    |  =SUMPRODUCT(($B$2:$B$16=H2)*($F$2:$F$16=I2)*$E$2:$E$16)
Child  |    2    |  =SUMPRODUCT(($B$2:$B$16=H3)*($F$2:$F$16=I3)*$E$2:$E$16) 
Child  |    3    |  =SUMPRODUCT(($B$2:$B$16=H4)*($F$2:$F$16=I4)*$E$2:$E$16)

Суммарный продукт суммирует только те строки из вашей таблицы, где есть ДЕТСКИЙ (как определено в столбце H) и фаза, равная числу, определенному в I, и суммирует только значения, которые соответствуют вашим финансовым столбцам. Если какой-либо дочерний элемент отсутствует в B или или фаза в F не равна тому, какое число ищется, то эта строка оценивается как 0.

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

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

person Forward Ed    schedule 18.05.2016
comment
Проголосовали за, хотя я бы избегал вызова столбца PHASE и вместо этого перешел на родительскую фазу, чтобы избежать путаницы. А также я бы использовал index / match вместо vlookup, но это может быть потому, что мне нравится index / match. - person Stian Yttervik; 18.05.2016
comment
@StianYttervik Согласился с тем, что индекс / сопоставление более универсален, но в этом случае с данными, размещенными так, как это vlookup, меньше аргументов для ввода. Что касается именования столбцов, изначально у меня было пустое поле .... стал ленивым и просто перепечатал старую ... По крайней мере, у меня случайно были замки на крышке! 8) - person Forward Ed; 18.05.2016
comment
Это большое спасибо, и ответ работает. Ради аргументов, можно ли это сделать без ввода нового столбца (как в столбце F), и вся логика в формуле была опущена? - person Andy_NPT; 22.05.2016
comment
На данный момент я не знаю об этом. Я посмотрю, смогу ли я заставить кого-нибудь еще взглянуть на это. - person Forward Ed; 23.05.2016
comment
@Andy_NPT посмотри мой ответ. ForwardEd, вы звонили, я ответил. - person Scott Craner; 23.05.2016
comment
@ScottCraner, должно быть, много бетона в твоей лесной шее 8). Спасибо за альтернативный ответ, я сейчас рассмотрю его более подробно. - person Forward Ed; 23.05.2016

arrow_upward
1
arrow_downward

Вот она в одной формуле массива:

=SUM(IF($C$2:$C$16<>"",(LOOKUP($C$2:$C$16,$A$2:$A$16,$D$2:$D$16)=G2)*$E$2:$E$16))

Поскольку это формула массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.

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

person Scott Craner    schedule 22.05.2016
comment
Это потрясающе. У меня есть еще один вопрос. Будет ли эта формула работать, если вы добавите такой оператор if / and: - person Andy_NPT; 26.05.2016
comment
= СУММ (ЕСЛИ (И ($ C $ 2: $ C $ 16 ‹›, $ B $ 2: $ B $ 16 = ДЕТСКИЙ), (ПРОСМОТР ($ C $ 2: $ C $ 16, $ A $ 2: $ A $ 16, $ D $ 2 : $ D $ 16) = G2) * $ E $ 2: $ E $ 16)) - person Andy_NPT; 26.05.2016
comment
У меня может быть ситуация, когда вы получаете два типа дочерних данных, и формулу нужно будет различать ... Я пробую свой набор данных, и он не совсем работает. - person Andy_NPT; 26.05.2016
comment
Формулы массива не любят And и Or. Поэтому нам нужно использовать * и + на своих местах, поэтому =SUM(IF(($C$2:$C$16<>"")*($B$2:$B$16="CHILD"),(LOOKUP($C$2:$C$16,$A$2:$A$16,$D$2:$D$16)=G2)*$E$2:$E$16)) @Andy_NPT - person Scott Craner; 26.05.2016