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.

Somase com desloc capa

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.

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.

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

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.

[saiba_mais]

Abraço

Marcos Rieper

Sair da versão mobile