Вычислить СУММПРОИЗВ с несколькими критериями столбца и одним критерием строки (с пустыми столбцами в диапазоне данных)
| A |B |C| D |E| F | G | H | I |J| K | L | M |
------|-------|--|-|------|-|--------|------|------|------|-|---------|----------|----------|--
1 | | | |ratio | | tested | 2023 | 2024 | 2025 | | RowCrit | ColCrit1 | ColCrit2 |
2 |Brand A|P1| | 7% | | yes | 500 | 70 | 60 | | 2024 | Brand A | P1 |
3 |Brand A|P2| | 8% | | yes | 100 | 47 | 300 | | | Brand D | P4 |
4 |Brand A|P2| | 10% | | yes | 800 | 21 | 200 | | | | |
5 |Brand B|P1| | 5% | | yes | 90 | 56 | 150 | | | | |
6 |Brand C|P4| | 3% | | no | 45 | 700 | 790 | | | | |
7 |Brand C|P2| | 8% | | no | 600 | 150 | 40 | | Result | 191.5 | |
8 |Brand D|P1| | 12% | | yes | 900 | 90 | 980 | | | | |
9 |Brand D|P1| | 20% | | yes | 125 | 854 | 726 | | | | |
10|Brand D|P3| | 19% | | yes | 70 | 860 | 614 | | | | |
11|Brand D|P4| | 2% | | yes | 842 | 250 | 85 | | | | |
12|Brand E|P4| | 6% | | no | 300 | 324 | 450 | | | | |
Я хочу вычислить SUMPRODUCT, умножив значения из Column D на соответствующие значения в Column G:I.
Формула SUMPRODUCT также должна учитывать фильтры для Row в Cell K2 и Column в Range L2:L4 и Range M2:M4.
В примере выше результат:
L7 = 7%x70 + 12%x90 + 20%x854 + 2%x250 = 191.5
Что касается ответа в на этот вопрос, я попытался применить это решение:
=LET(
a, IF(
COUNTA($L$2:$L$3) = 0,
N(ISNUMBER(ROW($A$2:$A$12))),
COUNTIF($L$2:$L$3, $A$2:$A$12)
),
b, IF(
COUNTA($M$2:$M$3) = 0,
N(ISNUMBER(ROW($B$2:$B$12))),
COUNTIF($M$2:$M$3, $B$2:$B$12)
),
c, CHOOSECOLS($A$2:$I$12, 3, XMATCH($K$2, $A$1:$I$1)),
SUM(a * b * CHOOSECOLS(c, 1) * CHOOSECOLS(c, 2))
)
Однако в результате он возвращает 0. Насколько я могу судить, это вызвано пустым Column C.
Я предполагаю, что в этой формуле Column D должен быть объявлен как фиксированный столбец, который будет использоваться для SUMPRODUCT.
Есть ли у вас идеи, как изменить формулу, чтобы она работала?
Эй, ответ, который я опубликовал на другой вопрос (78533198), подойдет здесь. Пожалуйста, попробуйте, если хотите.
Ответы 2
С моей стороны это работает, вместо жесткого кодирования положения столбца используйте XMATCH(), чтобы сделать его динамичным:
• Использование BYROW():
=LET(
α, A2:I12,
δ, COUNTIF(L2:L4,TAKE(α,,1)),
ε, COUNTIF(M2:M4,INDEX(α,,2)),
φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
SUM(BYROW(CHOOSECOLS(FILTER(α, φ),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
LAMBDA(a, PRODUCT(a)))))
• Или без использования помощника LAMBDA():
=LET(
α, A2:I12,
δ, COUNTIF(L2:L4,TAKE(α,,1)),
ε, COUNTIF(M2:M4,INDEX(α,,2)),
φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
Σ, CHOOSECOLS(FILTER(α, φ),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))
Кстати, вы также можете использовать эту специальную формулу LAMBDA():
=LET(
λ,LAMBDA(α,δ,ABS(AND(ISBLANK(δ))-MMULT(--(α=TOROW(δ)),{1;1;1}))),
SUM(FILTER(G2:I12,G1:I1=K2)*D2:D12*λ(A2:A12,L2:L4)*λ(B2:B12,M2:M4)))
Однако вы можете удалить XMATCH() для столбца ratio и сделать его жестко закодированным с помощью column_index4, если столбец всегда фиксирован.
Согласно комментариям ОП:
Формула работает. Единственный раз, когда он выдает #CALC, это когда я удаляю ВСЕ критерии столбца. Есть ли также способ, которым он вычислит СУММПРОИЗВ, если ВСЕ критерии столбца пусты?
Окончательные обновленные формулы:
=LET(
α, A2:I12,
δ, COUNTIF(L2:L4,TAKE(α,,1)),
ε, COUNTIF(M2:M4,INDEX(α,,2)),
φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
Σ, CHOOSECOLS(FILTER(α, IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ)),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
SUMPRODUCT(TAKE(Σ,,1)*TAKE(Σ,,-1)))
Или,
=LET(
α, A2:I12,
δ, COUNTIF(L2:L4,TAKE(α,,1)),
ε, COUNTIF(M2:M4,INDEX(α,,2)),
φ, IFS(SUM(δ)=0,ε,SUM(ε)=0,δ,1,δ*ε),
SUM(BYROW(CHOOSECOLS(FILTER(α, IF(SUM(φ)=0,SEQUENCE(ROWS(α))^0,φ)),XMATCH("ratio",A1:I1),XMATCH(K2,A1:I1)),
LAMBDA(a, PRODUCT(a)))))
Формула работает. Единственный раз, когда он выдает #CALC, это когда я удаляю ВСЕ критерии столбца. Есть ли также способ вычислить СУММПРОИЗВ, если ВСЕ критерии столбца пусты?
@Мичи, извини за задержку, меня не было за компьютером, я опубликовал скриншот, он все еще работает, даже если ColCrit пуст! пожалуйста, проверьте!
Это также работает на вашей стороне, когда ColCrit1 и ColCrit2 совершенно пусты?
@Мичи, извини, я пропустил, я обновлю. дайте мне время, я снова отошел от компьютера.
@Michi обновлено!
Когда я использую обновленную формулу, я получаю правильное значение, когда очищаю все ColCrit1 и ColCrit2. Однако когда я снова ввожу эти критерии, значение остается прежним. Всегда 416. У вас такая же проблема?
@Мичи, просто сделай эту часть φ для SUM(φ) т.е. IF(SUM(φ)=0,1,φ) извини, это не сработает
Теперь это работает, когда я ввожу критерии для ColCrit1 и ColCrit2, но когда они все пусты, я получаю ошибку #VALUE.
@Мичи, видишь, но обычай LAMBDA() работает во всех сценариях, не так ли?
Теперь это работает. Спасибо за дополнительную помощь. Я не могу проверить функцию LAMBDA, поскольку мой Excel еще не распознает функцию BYROW. Я предполагаю, что мне нужно обновиться до последней версии.
Изменять:
c, CHOOSECOLS($A$2:$I$12, 3, XMATCH($K$2, $A$1:$I$1)),
К:
c, CHOOSECOLS($A$2:$I$12, 4, XMATCH($K$2, $A$1:$I$1)),
Или:
c, CHOOSECOLS($A$2:$I$12, XMATCH("ratio", $A$1:$I$1), XMATCH($K$2, $A$1:$I$1)),
В вашей существующей формуле добавьте
4вместо3, и все заработает! или используйтеXMATCH()вместоratiolookup_value