Как создать сводную таблицу Excel для расчета чистой позиции по сделкам?

Question

Данные таблицы отслеживания сделок от одного человека к другому:

+------+----+-----+
| From | To | Amt |
+------+----+-----+
| A    | B  | 10  |
+------+----+-----+
| B    | C  | 5   |
+------+----+-----+
| A    | C  | 3   |
+------+----+-----+
| C    | B  | 2   |
+------+----+-----+

Как создать сводную таблицу (с использованием вычисляемых элементов, вычисляемых полей и т.д.), Которая будет выглядеть следующим образом?

+--------+-----+ 
| Person | Net |
+--------+-----+ 
| A      | -13 |
+--------+-----+ 
| B      |   7 |
+--------+-----+ 
| C      |   6 |
+--------+-----+
| Total  |   0 |
+--------+-----+

Answer 1

Это решение основано на использовании преобразованной исходной таблицы данных.

Предполагая, что ваши данные находятся в чистой рабочей таблице, а ваша таблица данных начинается с A1:

Введите эти метки и функции:

E1: Person

F1: Net

E2: =INDEX(A:B,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)),IF(ROW(E2)>COUNTA(A:A),2,1))

F2: =INDEX(C:C,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)))*IF(ROW(E2)>COUNTA(A:A),1,-1)

затем перетащите, чтобы заполнить этими двумя функциями вниз, пока не увидите много нулей.

Начните сводную таблицу с диапазоном исходных данных: E:F и позиционированием цели в том же рабочем листе в H1 .

Перетащите поле « Person область « Row Labels , а поле « Net область « ∑ Values и измените функцию суммирования на « Sum .

Фильтруйте нули и пробелы, и это все.

Я надеюсь, что это помогает.