Столбец SUMIF в строке столбца совпадает с другим столбцом

Question

У меня есть два листа.

Доход (Лист1):

A          | B      | C
Job number | Amount | Net
1          | 100    | 
2          | 200    | 
3          | 100    | 
4          | 60     |
5          | 100    | 
6          | 100    | 
7          | 100    | 
8          | 100    |

А также

Результат (Лист2):

A          | B      | C
Job number | Amount | Title
1          | 5      | Stationery
2          | 15     | Printer ink
2          | 10     | et 
2          | 10     |
4          | 20     | 
6          | 30     | 
8          | 10     | 
8          | 10     |

Как можно написать формулу для столбца C листа 1, чтобы суммировать значения столбца B листа 2, где Sheet2 ColumnA равен номеру задания, найденному в соответствующем столбце A листа 1? Таким образом, я могу вычесть эту сумму из суммы дохода, в результате, как показано ниже:

Доход (Лист1)

(Работа показана рядом с желаемым результатом)

A          | B      | C
Job number | Amount | Net
1          | 100    | 95         (100 - 5)
2          | 200    | 165        (200 - 15 - 10 - 10)
3          | 100    | 100        (100 - nothing)
4          | 60     | 40         (60 - 20)
5          | 100    | 100        (100 - nothing)
6          | 100    | 70         (100 - 30)
7          | 100    | 100        (100 - nothing)
8          | 100    | 80         (100 - 10 - 10)

Это почти этот вопрос Подсчет ячеек на основе сравнения со значением в той же строке другого столбца, но не совсем ...!

Answer 1

=SumIf(Sheet2!$A$2:$A$9,$A2,Sheet2!$C$2:$C$9) суммирует значения на листе 2 на основе столбца A в листе 1.

Тогда вы можете просто сделать это в своем пол. C в Листе 1:

=$B2-SumIf(Sheet2!$A$2:$A$9,$A2,Sheet2!$C$2:$C$9)

Answer 2

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

Выберите данные (щелкните внутри, нажмите CTRL-A) листа 2 и перейдите к « Insert > PivotTable . Вы, вероятно, хотите создать это на новом листе. Если / когда вы добавляете больше данных в исходную таблицу, вы можете найти методы, чтобы обновить / развернуть сводную таблицу после щелчка внутри нее и поиска PivotTable Options на ленте.

С этого момента вы можете расположить сводную таблицу, как показано здесь (разметка, которую я сделал, была только для наглядности):

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

=B2-VLOOKUP(A2,Sheet4!A:B,2,FALSE)

Но у нас проблема здесь! Мы получаем #N/A потому что некоторые из наших работ не имеют записей на листе 2. Здесь легко исправить, просто учесть ошибки, например:

=IFERROR( наша формула , оригинальная сумма )

Если вы только начинаете использовать сводные таблицы, вам, вероятно, удобнее использовать базовые функции, такие как Vlookup и сложение / вычитание, поэтому этот метод должен быть более полезен для развития хороших навыков Excel.