Somase com Desloc e marcação das células selecionadas

Somase com desloc e marcação das céluas selecionadas

Objetivo: Demonstrar como fazer no Excel a seleção de colunas diferentes em somas condicionais e marcar as células que foram selecionadas para a soma.

Neste artigo vou demonstrar como criar uma planilha aonde sejam utilizados critérios de soma com a função somase variando a coluna utilizando a função desloc.

Para incrementar um pouco o seu uso fiz também uma formatação condicional avançada no Excel que faz com que as células selecionadas nos critérios sejam destacadas na planilha em cores diferentes. Veja como foi feito.

• Faça o download da planilha em Download da planilha clicando no botão 

• Uma lista semelhante existe também para as filiais selecionáveis na célula B5 na planilha Linha e coluna.
• Clique na célula B7 e note a seguinte fórmula que vou explicar:

• A função SOMASE é explicada neste vídeo http://guiadoexcel.com.br/funcao-de-soma-condicional-no-excel-somase, caso não a conheça veja o vídeo e retorne para acompanhar o restante da função.
  
• A função DESLOC realiza o deslocamento de uma célula ou intervalo em relação a colunas, células, altura ou largura. Neste exemplo deslocaremos as colunas.

Parâmetros da função DESLOC:

ref: referência de células, no caso foi selecionada o intervalo $F:$F.

lins: passado como 0, deslocaria células para baixo ou para cima de um intervalo, exemplo caso a referência fosse A1 e o parâmetro lins fosse 1, deslocaria a referência para A2 ou seja uma linha para baixo.

cols: quantidade de colunas deslocada para a esquerda ou direita. Exemplo para a referência $F:$F e caso o parâmetro cols fosse 1, deslocaria para $G:$G, ou seja, uma coluna para a direita. No nosso exemplo utilizamos CORRESP que vou explicar em seguida.

[altura]: opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.

[largura]: opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.

• Como disse em cols foi utilizada a função CORRESP para informar a quantidade de colunas que seriam deslocadas á direita ou á esquerda, vejamos ela:

Parâmetros da função CORRESP:

valor procurado: o valor que será localizado dentro da matriz. No caso $B$4 que se refere ao mês.

matriz procurada: o local aonde serão localizados os valores. No caso $F$1:$Q$1 que se refere aos meses da planilha.

tipo da correspondência: para uma correspondência exata é passado 0, para correspondências aproximadas passar -1 para uma aproximação para baixo e 1 para uma aproximação para um número imediatamente superior. No caso 0 para uma correspondência exata.

Desta forma a função CORRESP está apontando para a coluna dentro do intervalo selecionado retornando para janeiro = 1, fevereiro = 2 e assim por diante. Como a referência da função DESLOC está para F:F, ou seja janeiro, então temos que reduzir 1 da função CORRESP de modo que aponte para a coluna correta, pois caso fosse colocado janeiro ele apontaria para 1 e deslocaria uma coluna á direita ocasionando um erro e apontando para a coluna fevereiro.

Agora vamos á formatação condicional que faz com que as colunas e linhas da planilha que se referem as seleções realizadas sejam destacadas.

Formatação condicional da planilha

• Clique na célula D3 e note a seguinte função: =SE(E3=$B$5;"x";"") ela demonstra que caso a célula E3 seja igual á B5 então deverá constar nesta célula o valor "x", e caso contrário o valor branco "". Esta função está comparando se a filial que está na planilha é a mesma selecionada no filtro.
• Agora clique na célula D25 e veja a função =SE(F1=$B$4;"x";"") é realizada uma marcação com x caso o valor da célula F1 seja semelhante ao valor da célula B4 constante no filtro de dados. Ela compara o mês selecionado.
• Clique sobre a planilha e clique em Página Inicial->Formatação Condicional->Gerenciar Regras
• Veja que as regras identificam se a célula em questão está marcada com o x e desta forma a destaca caso esteja.

Aqui está o truque, quando foi criada a regra note que o $ está somente para a linha e não para a coluna o que significa que a formatação vai variar conforme a coluna, formatar A se A25 estiver x, formatar B se B25 estiver x. De forma semelhante para a segunda regra aonde o que foi travado foi a coluna e não a linha, formatar linha 2 se D2 estiver x, formatar linha 3 se D3 estiver x.

Clicando na segunda planilha "Somente célula" você pode notar que somente a célula que está sendo somada foi selecionada. A condição foi a mesma utilizada na formatação anterior com uma mudança, as regras estão juntas na mesma regra de formatação.

Clicando em Editar regra você consegue ver a fórmula utilizada: =E($D2="x";D$25="x"), aonde identifica os dois critérios anteriormente explicados, e que terminam na seguinte formatação.

Então é isso, agradeço por terem acompanhado mais este artigo e até a próxima.

Abraço

Marcos Rieper