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

Question

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

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

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

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

Answer 1

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

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

Answer 2

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

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

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

(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))

Обратите внимание, что это формула массива, и ее нужно вводить с помощью клавиши CTRL Shift Enter.