coluna SUMIF na linha da coluna, é o mesmo que outra coluna

Question

Eu tenho duas folhas.

Renda (Folha1):

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

e

Resultado (Folha2):

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     |

Como posso escrever uma fórmula para a Coluna Sheet1 C, para somar valores de Sheet2 Column B, em que Sheet2 ColumnA é igual ao número do job encontrado na Sheet1 Column A correspondente? Dessa forma eu posso deduzir esse valor do montante da renda, o resultado é o seguinte:

Renda (Folha1)

(Trabalho mostrado ao lado do resultado desejado)

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)

É quase esta questão Conte as células com base em uma comparação com o valor na mesma linha de outra coluna , mas não completamente ...!

    

Answer 1

Você pode fazer isso com uma fórmula SUMIF personalizada, com certeza, mas adoro as Tabelas Dinâmicas. As habilidades de Tabela Dinâmica se traduzem em uma experiência muito mais fácil de usar, sem aprender a sintaxe da fórmula (relativamente) complicada.

Selecione os dados (clique dentro, aperte CTRL-A) da Folha 2 e vá para Insert > PivotTable . Você provavelmente deseja criar isso em uma nova planilha. Se você adicionar mais dados à tabela de origem, poderá encontrar os métodos para atualizar / expandir a tabela dinâmica depois de clicar nela e localizar PivotTable Options na faixa de opções.

A partir deste ponto, você pode organizar a tabela dinâmica como mostrado aqui (a marcação que fiz foi apenas para referência visual):

Aofazerisso,vocêcondensouseusdadosemumaúnicalinhapornúmerodetrabalhosemalterarseusdadosdeorigem.Apartirdisso,vocêpodeusarumafunçãoVLOOKUPinativaparafazersuamatemática:

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

Masnóstemosumproblemaaqui!Estamosrecebendo#N/AporquealgunsdenossostrabalhosnãotêmentradasnaPlanilha2.Asoluçãofácilaquiécontabilizarerros,assim:

=IFERROR(nossafórmula,valororiginal)

Se você está apenas começando a usar Tabelas Dinâmicas, provavelmente ficará mais à vontade usando funções básicas como Vlookup e adição / subtração, portanto, esse método deve ser mais útil no desenvolvimento de boas habilidades em Excel.

    

Answer 2

=SumIf(Sheet2!$A$2:$A$9,$A2,Sheet2!$C$2:$C$9) somará os valores na planilha 2, com base na Col A na planilha 1.

Então, você poderia fazer isso no seu col. C na Folha1:

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