Soma e Transposição de Valores condicionados - Excel

LicoTurati · 30 de julho de 2013

Caros colegas,

venho novamente solicitar um pouco do conhecimento de vocês sobre o Excel.

Possuo uma planilha em que tenho que somar todos os Valores Contabeis (você) posicionados na coluna C das planilhas "Entradas" e "Saídas" condicionados aos códigos que aparecem na coluna "B".

Isso é, quando aparecerem os códigos que se referem às operações de compras no mês de janeiro (numerado como 1 na coluna A), os valores contábeis da mesma linha deverão ser somados na célula J22, prevendo com isso o total daquela operação no mês.

Os códigos para orientação estão logo abaixo do "discriminativo mensal por tipo de operação".

Segue link do arquivo com a planilha.

http://rapidshare.com/files/4092989470/Planilha%20VAF%20-%20Modelo%20-%20Backup.xlsm

Obrigado.

vinibpeixoto · 30 de julho de 2013

Oi, LicoTurati,

De acordo com o arquivo que você disponibilizou, a primeira coisa que deve ser feita é ter uma planilha com os códigos das entradas (e saídas) e suas descrições (compras, transferências, devoluções, entre outros) por linha. Abaixo, exemplo:

1.101 Compras

1.102 Compras

1.103 Compras

1.104 Compras

[...]

1.151 Transferência

1.152 Transferência

1.153 Transferência

1.154 Transferência

[...]

1.201 Devoluções e Anulações

1.202 Devoluções e Anulações

1.203 Devoluções e Anulações

1.204 Devoluções e Anulações

[...]

A mesma coisa deve ser realizada com os meses.

1 Janeiro

2 Fevereiro

3 Março

[...]

Após ter os códigos, separados em distintas linhas de uma planilha, assim como os meses, todas as entradas devem adicionar a descrição do mês e a descrição da operação em cada registro.

Supondo-se que os códigos das operações foram adicionados numa planilha chamada 'CÓDIGOS ENTRADA', e os meses numa planilha chamada 'MESES', duas novas colunas devem ser adicionadas às entradas:

A coluna MÊS deve apresentar a seguinte fórmula:

=PROCV(A4;MESES!$A$1:$B$12;2;0)

A coluna OP:

=PROCV(C4;'CÓDIGOS ENTRADA'!$A:$B;2;0)

Supondo-se que as alterações acima foram feitas em uma nova planilha, chamada 'ENTRADAS DETALHADAS', a seguinte fórmula deve ser adicionada à celula J22 (e arrastada às demais células que conterão as somas agrupadas por tipo de operação e mês) da planilha 'ENTRADAS'.

=SOMASES('ENTRADAS DETALHADAS'!$E$4:$E$85;'ENTRADAS DETALHADAS'!$B$4:$B$85;J$21;'ENTRADAS DETALHADAS'!$D$4:$D$85;$I22)

Abaixo, explicação do que é realizado.

Devido ao resumo por tipo de operação conter apenas as descrições das operações, além de uma determinada operação apresentar inúmeros códigos distintos, é necessário desmembrar os códigos de cada operação em distintas linhas, associando-os às suas descrições.

Ao invés de:

Compras 1.101 a 1.126, 1.401, 1.403, 1.501, 1.651 a 1.653

Existiria:

1.101 Compras

1.102 Compras

1.103 Compras

[...]

1.401 Compras

1.403 Compras

[...]

Com a segmentação acima, é possível associar os registros de entradas, que contém apenas os códigos das operações, às descrições das operações.

Ao invés de:

1.401 (Cód Op) R$ 403,46 (Valor Contábil)

Existiria:

1.401 (Cód Op) Compras (Desc Op) R$ 403,46 (Valor Contábil)

A associação acima possibilita a soma dos registros pela descrição da operação, o que caracteriza parcialmente a sua necessidade. Como a soma dos registros deve ser feita também por mês - sua real necessidade -, bastaria adicionar a descrição dos meses aos registros de entradas e realizar somas condicionais.

Criei um exemplo, a partir de seu arquivo, e fiz o upload em http://www.sendspace.com/file/kvs5cf. O exemplo foi realizado apenas com as entradas. Bastaria replicar o procedimento realizado às saídas.