Суммирование значений из столбца на основе совпадения в другом столбце и первого отдельного вхождения значения в третьем столбце

Question

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

Вот упрощенный пример электронной таблицы:

Мне нужно сопоставить букву в столбце F (A, B, C) со столбцом B «Буквы», а затем просуммировать значение в столбце D «Значения» один раз для каждого отдельного числа в столбце C «Числа» и отобразить эту сумму в столбце G «Сумма значений».

Правильная сумма отображается в ячейках столбца G, но у меня нет формулы для ее выполнения. Любая помощь будет оценена по достоинству!

Answer 1

Для решения такого рода задач полезно мыслить в терминах массивов.

Если вы можете получить массив (список) чисел в столбце «Значения» (столбец D), где «Буквы» (столбец B) равны «A», а из столбца «Числа» удалены дубликаты, вы можете просто просуммировать массив, чтобы получить ответ.

Это выражение:

(B$2:B$12=F2)

дает массив True/Falseзначений, Trueгде столбец B = "A". Этот:

(C$2:C$12<>C$3:C$13)

дает массив True/Falseзначений с Trueячейкой в ​​столбце C, которая не равна следующей ячейке. Поскольку ваши дубликаты находятся в последовательных ячейках (напишите комментарий ниже, если это не всегда так), этот массив имеет Falseгде находятся дополнительные вхождения значения, и, по сути, он отфильтровывает дубликаты. Перемножение этих двух массивов:

(B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)

преобразует True/Falseзначения в 1 и 0 и дает массив с 1 в позициях, которые мы хотим видеть в сумме. Используя этот массив как в logical_test, IF()а столбец D как value_if_true:

IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12)

возвращает массив значений в столбце D, где бы ни была 1, перемежаемый с Falseнулями. Теперь мы можем просто просуммировать массив. Эта формула, заполненная из G2, дает результаты, показанные ниже.

=SUM(IF((B$2:B$12=F2)*(C$2:C$12<>C$3:C$13)>0,D$2:D$12))

Обратите внимание, что это формула массива и ее необходимо вводить с помощью CTRLShiftEnter.

Answer 2

Если вы можете добавлять столбцы к своим данным, то для приведенного ниже простого примера подойдет следующее:

1. Добавьте формулу для объединения букв и цифр. Я использовал столбец A для этой формулы, т.е. ячейку A2: =B2&C2
2. Перетащите эту формулу вниз, чтобы применить ее к ячейкам A2:A12.
3. Добавьте формулу для проверки того, является ли каждая конкатенация букв и цифр первой уникальной конкатенацией. Я использовал столбец E для этой формулы, т.е. ячейку E2: =COUNTIF(A$2:A2,A2)
4. Перетащите эту формулу вниз, чтобы применить ее к ячейкам E2:E12.
5. Используйте следующую формулу в ячейке H2, чтобы суммировать значения, где совпадает буква, но только для первой конкатенации буквы и цифры, т. е. ячейки G2: =СУММЕСЛИМН($D$2:$D$12,$B$2:$B$12,F2,$E$2:$E$12,1)
6. Перетащите эту формулу вниз, чтобы применить ее к ячейкам G2:G4.