Eu sou um pouco noob, então tenha paciência comigo. Estou fazendo uma planilha de vários produtos comprados e vendidos a vários preços. A tabela é classificada por produto, com uma linha em branco separando cada transação de compra e venda completa. Eu criei uma coluna para calcular o fluxo de caixa por produto. A tabela de exemplo é assim:
Type | Product | Qty | Price | Total | Cashflow
BUY | AA | 2 | 0.50 | 1.00 |
SELL | AA | 2 | 1.50 | 3.00 | 2.00
(blank row)
BUY | BB | 5 | 0.10 | 0.50 |
SELL | BB | 5 | 1.00 | 5.00 | 4.50
Eu quero que a coluna Fluxo de caixa calcule apenas a perda / lucro líquido após a venda do produto (minhas desculpas se o fluxo de caixa for incorretamente usado), por isso as linhas em que o tipo de transação diz "comprar" estão vazias. Eu usei essa fórmula:
IF ([@ Type]="VENDER", ([@ Total] - (OFFSET (F3, -1, -1))), "")
que funcionou bem para as transações mais simples que só tinham uma linha para BUY e uma linha para a SELL. O problema é que alguns produtos são comprados / vendidos a preços diferentes, onde a função offset não funcionaria mais. Exemplo:
Type | Product | Qty | Price | Total | Cashflow
BUY | AA | 2 | 0.50 | 1.00 |
SELL | AA | 2 | 1.50 | 3.00 | 2.00
(blank row)
BUY | BB | 3 | 0.20 | 0.60 |
BUY | BB | 4 | 0.10 | 0.40 |
SELL | BB | 5 | 1.00 | 5.00 |
SELL | BB | 2 | 1.10 | 2.20 | 6.20
Existe uma maneira de fazer uma fórmula para o Cashflow calcular a soma de todas as vendas menos a soma de todas as compras desse produto, então eu obteria o lucro / perda resultante (6,20 no exemplo acima) no último linha de transação do produto? Como em, a fórmula determinaria o intervalo da linha selecionada até a próxima linha vazia acima; e então, dentro desse intervalo, seria necessária a soma de [@Type]="SELL" menos a soma de [@Type]="BUY"
Desculpe se não expliquei claramente. Por favor, deixe-me saber se você tem alguma idéia
Como você está criando a planilha por conta própria, presumirei que você pode escolher mexer na maneira de projetar os componentes da sua solução.
Passo 1: Uma maneira seria calcular os valores na coluna Total como negativos para cada BUY de registro. Então, introduza a fórmula abaixo para o total
=IF([@Type]="BUY",-[@Qty]*[@Price],[@Qty]*[@Price])
Passo 2: insira uma tabela dinâmica para agregar usando Product e Total como abaixo
O óbvio é uma tabela Pivot - no entanto, primeiro você precisa tornar os valores BUY negativos, de forma que a coluna total se torne
=IF([[Type ]]="BUY",-1,1)*[ [ Qty ] ]*[ [ Price ] ]
Em seguida, crie uma tabela dinâmica em Sheet3! $ A $ 3
Issoforneceosresultadosdesejadose,emseguida,retornaàcolunaCashFlowoCashFlowsetorna
=IF(B4<>"","",GETPIVOTDATA(" Total ",Sheet3!$A$3," Product ",[ [ Product ] ]))
