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 para acompanhar o exemplo.
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.
