Excel: сумма значений, соответствующих нескольким совпадающим или отдельным числам

Question

надеясь, что кто-то может помочь с этим:

Цифры слева (номера телефонов) будут индивидуальными или повторными. Каждая строка имеет номер во втором столбце, который связан с этим номером телефона.

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

Мой список обычно 2500 строк, так что это может занять некоторое время. Есть ли способ автоматизировать это?

Answer 1

Три решения:

^{(Я предполагаю, что ваши данные находятся в ячейках A1:B22 .)}

1. Условное форматирование

• Установите C1 на =B1 .
  (Если ваши данные не начинаются со столбца 1, а в строке перед первой строкой данных нет чисел, вы можете использовать следующую формулу для всего столбца.)
• Установите C2 на =IF(A1=A2, C1+B2, B2) и перетащите / заполните до C22 .
  Это установит столбец C как промежуточный итог для совпадающих чисел;
  то есть C1 = 50, C2 = 220, C3 = 320, C4 = 900, C5 = 500 и т. д.
• Выберите результаты (например, ячейки C1:C22) и выполните «Условное форматирование» → «Новое правило».  Выберите «использовать формулу, чтобы определить, какие ячейки форматировать», введите формулу =A1=A2 и отформатируйте ячейку, чтобы она была невидимой.  (Обычные способы сделать это - установить цвет шрифта на белый или применить пользовательский формат чисел ;;; .)

В случае, если вышеприведенное не ясно: это помещает число в каждую ячейку в диапазоне, но просто скрывает те, которые вам не нужны.

2. Вспомогательная колонна

• Выберите столбец, который находится вне пути; например, столбец Z Определите его так же, как мы определили столбец C выше.
• Установите C1 на =IF(A1=A2, "", Z1) и перетащите / заполните вниз до C22 .

3. Все в одном

• Установите для C1 значение =IF(A1=A2, "", SUMIF(A$1:A$22, A1, B$1:$B22)) и перетащите / заполните до C22 .

---

Обратите внимание, что если телефонные номера не отсортированы должным образом и не разделены на уникальные группы, эти методы дают разные результаты.  Маркировка методов следующим образом:

1. Условное форматирование
2. Вспомогательная колонна
3. Все в одном

рассмотреть эти данные:

phone   value   method1   method2   method3
 ︙       ︙       ︙        ︙        ︙
 95      800     1500      1500      1500
 42        1                                ← First block of data for phone # 42
 42        2        3         3        99   ← Note that methods 1 and 2 yield 1 + 2 = 3
 17        4                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 17        8
 17       16       28        28        28
 42       32                                ← Second block of data for phone # 42
 42       64       96        96        99   ← Note that methods 1 and 2 yield 32 + 64 = 96
 83     1000                                ↖ but method 3 yields 1 + 2 + 32 + 64 = 99
 83     2000                                                                       (again)
 83     4000     7000      7000      7000
 ︙       ︙

Answer 2

Используя SUMIF, вот ваш пример

Ваш пример

Вы можете использовать эту формулу для пустого столбца, соответствующего столбцу телефонных номеров

=SUMIF($B$5:$B$14,E5,$C$5:$C$14)

Answer 3

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

1. Возьмите весь 1-й столбец и поместите его в новый лист (столбец A) и отсортируйте его по AZ.
2. Поместите в ячейку B2 =IF(A1=A2,1,0) и заполните автозаполнение формул.
3. Отфильтруйте все значения «1» и удалите эти строки - у вас останутся уникальные номера (номера телефонов)
4. удалите значения столбца B и используйте SUMIF на каждом уникальном (телефонном) номере. Диапазон фиксируется до 1-го столбца, 1-го листа. Критериями является каждый номер (номер телефона) от полковника А. Sum_range - 2-й столб, 1-й лист.

Там может быть способ сделать это в макрос для себя.