Publicado em Deixe um comentário

[Excel Avançado] – Função Desloc

Fala Galera. Tudo bem ? Neste Post vou mostrar para vocês como utilizar essa importante função do Excel que é  o DESLOC.  

Costumo dizer nos meus treinamentos que existem algumas funções mágicas do Excel e que são muito poderosas. Então  se você quer ter um nível avançado do Excel precisa dominar o Desloc.

A ideia do desloc, é  de fato Deslocar virtualmente uma célula na planilha através de parâmetros que podem ser o número da linha, coluna, altura e largura.   

Ficou um pouco confusa essa definição certo ? Vamos para os exemplos que com certeza ficará mais fácil a compreensão.

A gente tem aqui 3 exemplos bem práticos e que mostram como utilizar o desloc. Para isso, vamos  tomar como base a Tabela 1, que basicamente é  uma lista de Nome,  Idade e Aniversario.

Basicamente o DESLOC tem duas grandes formas de usar:

  1. Deslocar uma célula para cima e para baixo que no excel seriam as linhas, e para esquerda e direira que nesse caso seriam as colunas.
  2. A segunda função do desloc é deslocar não somente uma célula apenas mas o intervalo de uma célula, guarda isso que a gente vai ver com mais detalhe lá na frente.
1ª Forma do Desloc

No Exemplo 1 tem a primeira forma do DESLOC , o nosso objetivo aqui é trazer a idade e o Aniversario da Diego Lima. Poderíamos nesse exemplo usar a função PROCV para localizar esses dados,  mas como o objetivo é mostrar a função DESLOC vamos imaginar que não poderíamos fazer isso com o PROCV ok .

Para entender a função vamos pegar como referência a célula C6 que contém o número 25, ou seja, a idade do Carlos. Se eu quiser deslocar a minha referência de célula que esta na C6 para a C7 por exemplo, eu tenho que deslocar mais 1 linha para baixo .

  1. Digitar o DESLOC
  2. Selecionar a célula C6 como Referência
  3. Parâmetro de linha célula G9
  4. Parâmetro de coluna célula G10.

Sua fórmula deve ficar assim → =DESLOC($C$6;G9;G10)

Observe que ele trouxe o número 25 da idade do Carlos pois a referência de linha e coluna está vazia, e o Excel entende nesse caso como zero.

Mas, o nosso objetivo é trazer a idade do Diego, logo temos que deslocar a célula 2 linhas para baixo. Então no parâmetro Coluna iremos digitar justamente o número 2. Observe que trouxe justamente a idade do Diego.

Se a gente quisesse a idade da Amanda seria 3 no parâmetro linha. Muito fácil utilizar a função certo ?

Agora, vamos aprender a deslocar através de coluna. Para isso vamos repetir a mesma formula que digitamos anteriormente. E nos parâmetros vamos digitar na linha o mesmo 2 para encontrar a linha que corresponde ao Diego, e na coluna vamos digitar o número 1, pois precisamos trazer o Aniversário do Diego.

Para a direita na coluna e para baixo na linha é sempre um número positivo, isso significa que se a gente digitar um número negativo iremos para o sentido oposto.

Se eu digitar -1 na coluna irar aparecer o nome do Diego.  Pronto, você já aprendeu uma forma de usar a o desloc. Agora vamos para a segunda forma.

2ª Forma do Desloc

A segunda forma tem mesma lógica da primeira porem é utilizado para deslocar um intervalo.

Nesse exemplo 2 precisamos somar a idade de todos. Obvio que basta só colocar a função soma aqui e selecionar o intervalo de C6:C9. Porém vamos fazer a função Soma com intervalo gerado pelo a função  Desloc para aprender.

  1. Vamos digitar =soma(desloc( selecionar a mesma referência do exemplo 1.
  2.  Marcar com ; “Ponto e virgula” 3 vezes os parâmetros de linha e coluna que nesse exemplo não iremos utilizá-los.
  3.  Referenciar nas células g18 e g19 os parâmetros de altura e largura.

Sua fórmula deve ficar assim → =SOMA(DESLOC($C$6;;;G18;G19))

Esse erro de REF é que nossa referência ainda não está completa, vamos fazer isso agora. A lógica é quase parecida com o do exemplo  anterior, como eu quero deslocar para baixo precisamos deslocar o número positivo, a diferença aqui é que nesse caso eu vou deslocar apenas o intervalo, logo não partimos do 0, e sim do 1 ou seja, na  altura temos que aumentar o intervalo 4 vezes pois são 4 linhas para somar todas as idades, seguindo a mesma lógica na largura precisamos digitar o número 1 pois estamos criando um intervalo de 4 linhas e 1 coluna.

Surgi então o total 130 que é soma das 4 idades.

Vamos para o Exemplo 3 para fixar essa segunda forma de usar o DESLOC.

Vamos combinar ele com uma validação de dados do tipo lista para na célula B16 listar os 4 nomes aqui.

  1. Clicar no Menu Dados > Validação > Permitir Lista
  2.  Campo fonte vamos digitar o DESLOC
  3. Usar a célula b6 como referência
  4. Marcar; 3 vezes pois novamente não iremos usar as referências de linha e coluna.
  5. E referenciar igual no exemplo anterior, a altura na célula c18 e a largura na C19.

Sua fórmula deve ficar assim → =DESLOC($B$6;;;C18;C19)

Nos parâmetros vamos digitar o mesmo 4 na altura pois são 4 linhas na nossa referência e digitar 1 na altura pois estamos no intervalo de 1 coluna.

Pronto, surgiu a validação de dados.

Baixe a planiha usada aqui

Abraços e até a próxima.

Publicado em Deixe um comentário

[Excel Básico] – Como travar uma fórmula no Excel

Introdução

Uma dúvida muito comum no Excel é que como eu consigo travar uma fórmula para que os usuários fiquem impossibilitados de fazerem qualquer alteração na planilha?

Travar células essenciais no Excel é fundamental quando se elabora uma planilha, especialmente quando se faz para outros usuários. É muito comum as pessoas deletarem ainda que sem querer uma fórmula fundamental em uma planilha.

Passo a Passo

  1. Se possível, procure sempre ocultar a célula (Coluna/Linha) que contém uma fórmula ou um dado essencial. Para ocultar a célula é muito simples:
    1. Clicar com o botão direito na letra se a intenção é ocultar a coluna ou no número se for a linha e em seguida selecionar a opção ocultar.
  2. Se o objetivo for deixar visível a fórmula ou o dado mas evitar que seja deletado, segue o passo a passo:
    1. Na tabela abaixo vamos bloquear a coluna Var1 para que o usuário não edite ela.
  3. Devemos selecionar as células que queremos que sejam EDITADAS, ou seja, selecionar as células que devem ser liberadas para que o usuário possa alterar. No nosso caso abaixo são as colunas Orç1 e Real1.
  4. Clicar com o botão direito e em seguida selecionar a opção Formatar Células.
  5. Em seguida, devemos selecionar a aba PROTEÇÃO e DESMARCAR a caixa de bloqueadas. Por padrão o Excel vem com essa opção bloqueada, por isso que a gente deve selecionar as células que precisam ser liberadas e não as bloqueadas.
  6. Confirmar com Ok e em seguida devemos PROTEGER PLANILHA nas seguintes opções: Menu Revisão > Proteger Planilha.
  7. Cadastrar uma senha e clicar no botão OK. A partir desse momento o usuário só irá Editar as células que foram liberadas.

Abs

E. Lima