Trocando o texto da célula por uma figura – via Validação


Trocando o conteúdo da célula por uma imagem

 

By Marco Aurélio

Nível de Complexidade: Difícil

Você já precisou digitar ou escolher um nome na célula e após essa ação ser exibida uma figura no lugar? Como se fosse um PROCV procurando por texto, mas usando figuras?

Nesse primeiro artigo do ano, quero fazer uma abertura triunfal. Então vamos a essa dica muito interessante utilizando o Microsoft Excel 2010 (ou anteriores – fazendo apenas os devidos ajustes nos menus de acesso).

Antes é preciso ter o conhecimento das seguintes funções:

  • DESLOC
  • ÍNDICE
  • CORRESP
  • CONT.VALORES
  • INDIRETO

Mas fique tranquilo(a). Farei o exemplo passo a passo nos mínimos detalhes.

(Na dúvida, antes de seguir esse artigo, procure por outras soluções no meu BLOG usando algumas destas funções).

 

Vejamos abaixo as sintaxes destas fórmulas e suas utilidades:

 

DESLOC

Está fórmula retorna um valor obtido a partir do deslocamento feito de uma referência, seguindo um número de linhas e colunas fornecidas pelo usuário, assim como a altura e a largura da tabela.

=DESLOC (referência; linhas; colunas; altura; largura)

Onde:

Referência

É o endereço de uma célula onde desejamos basear o deslocamento;

Linhas

É o número de linhas que deseja deslocar para cima ou para baixo;

Colunas

É o número de colunas que deseja deslocar para a esquerda ou para a direita;

Altura

Indica o número de linhas que a referência apresenta. Deve ser um número positivo;

Largura

Indica o número de colunas que a referência apresenta. Deve ser um número positivo.

 

ÍNDICE

A função índice retorna um elemento de uma matriz, selecionado pelos números da linha e da coluna desta matriz.

=ÍNDICE (matriz; núm_linha; núm_coluna)

Onde:

Matriz

É a matriz onde será realizada a busca;

Núm_Linha

É o número da linha onde se encontra o valor;

Núm_Coluna

É o número da coluna onde se encontra o valor.

 

CORRESP

Retorna a posição relativa de um elemento procurado na matriz mencionada.

=CORRESP (valor_procurado; matriz_procurada; [tipo_correspondência])

Onde:

Valor_Procurado

É o valor que será procurado na matriz;

Matriz_Procurada

É a matriz onde será localizado o valor_procurado;

Tipo_Correspondência

É um número que determina como será realizada a pesquisa.


 

 

 

 

 

 

 

CONT.VALORES

A função CONT.VALORES conta a quantidade de células preenchidas, independente do seu conteúdo no intervalo indicado.

=CONT.VALORES (valor1, [valor2], …)

Onde:

valor1, valor2

É a linha, coluna, tabela ou matriz que se deseja contar os valores não nulos;

 

Uma ou mais áreas podem ser contadas de uma única vez.

 

INDIRETO

A função INDIRETO, basicamente, retorna o valor da célula especificada.

=INDIRETO (referência)

Onde:

referência

É o endereço de onde se deseja obter informação;

 

Usaremos essas fórmulas no exemplo a seguir:

1 – Abra uma nova Pasta de Trabalho do Excel 2007/2010(ou qualquer outra versão).

2 – Renomeie as planilhas Plan1, Plan2 e Plan3 para Imagem, Apoio e Apoio2, respectivamente.

 


 

3 – Insira as imagens desejadas na planilha Apoio. Organize-as na coluna A, de modo que fiquem inteiramente em suas respectivas células. Caso alguma parte de alguma imagem ficar para fora da célula onde está localizada, esta parte não aparecerá na hora de exibir a imagem escolhida.

 

Dica:

-Para redimensionar todas as células da planilha simultaneamente, de maneira que fiquem com o mesmo tamanho, selecione todas as linhas e colunas (com o botão que fica entre o cabeçalho da coluna A e o número 1, logo abaixo da caixa de nome) ou utilize a tecla de atalho CTRL + T e redimensione apenas a primeira coluna (aqui, podemos usar 220 pixels de LARGURA) e automaticamente todas as colunas terão o mesmo tamanho, o mesmo deve ser feito com as linhas (usando 170 pixels de ALTURA). Lembre-se: Isso é apenas uma “sugestão”.

Para redimensionar as imagens, selecione todas as imagens, utilizando o mouse mais a tecla CONTROL (CTRL). Com todas as imagens selecionadas, clique na guia Formatar (Ferramentas de Imagem), grupo Tamanho, em “Altura:” digite 4 e pressione ENTER; (mais uma “sugestão”).

-Para facilitar o posicionamento das imagens em suas respectivas células, pressione a tecla ALT enquanto movimenta a imagem, para que esta navegue pelos extremos das células. Use e abuse dos recursos de alinhamento de Imagens disponíveis na guia Formatar, grupo Organizar.

Para esse procedimento, clique na guia Inserir e no grupo Ilustrações clique no botão Imagem. Selecione-as utilizando a tecla SHIFT para marcar várias imagens sequencialmente ou a tecla CTRL para marcar várias imagens aleatórias. Após isso clique em Inserir e veja o resultado:


 

Ajuste-as conforme a dica no quadro anterior e veja o resultado:

 


 

4 – Agora, retire as linhas de grade, clique na guia Exibição, grupo Mostrar, caixa de seleção Linhas de Grade.

5 – Com a tabela Apoio pronta, monte uma tabela de referência na planilha Apoio2; com o nome das imagens na coluna A e a coluna B contendo o número da linha onde está localizada a imagem (na planilha Apoio).

Exemplo:


 

Atualizando a lista de dados automaticamente

 

6 – Na guia Fórmulas, grupo Nomes Definidos, clique no botão Gerenciador de Nomes (CTRL+F3). Clique em Novo. O nome criado agora será um nome dinâmico, não se referenciando a um intervalo estático. No campo Nome digite “Lista” e no campo Refere-se a digite a fórmula abaixo:

 

=DESLOC(Apoio2!$A$2;0;0;CONT.VALORES(Apoio2!$A:$A)-1;1)

 

Onde:

 

Apoio2!$A$2

Referência. É uma célula da planilha Apoio2, onde está baseado o deslocamento;

0

Linhas. Neste caso não se desloca linhas;

0

Colunas. Neste caso não se desloca colunas;

CONT.VALORES (Apoio2!$A$2)-1

Altura. Determina à altura da tabela, neste caso a fórmula determina a altura de acordo com o número de linhas não nulas na coluna A; O número -1 subtrai a linha de cabeçalho.

1

Largura; determina à largura da tabela, neste caso a largura é um, selecionando apenas os nomes das imagens.

 

Observe que as fórmulas anteriormente citadas podem trabalhar em conjunto. Aqui a função CONT.VALORES serve para contar o número de linhas que contem as células de valores não nulos e, assim, delimitar a altura da tabela.

 


 

Após a digitação da fórmula, clique no botão OK.

7 – Clique na planilha Imagem e selecione a célula A1. Após esse procedimento, clique na guia Dados, grupo Ferramentas de Dados, botão Validação de Dados. Usaremos a validação para criar um dropdown que contenha o nome das imagens, de acordo com o range dinâmico “Lista”. Para isso, na caixa de diálogo Validação de Dados, clique em Permitir e selecione a opção Lista e em Fonte digite =Lista (ou pressione F3 e escolha o nome definido Lista). Clique em OK e observe que ao clicar na seta para baixo, os nomes citados na coluna A podem ser selecionados.

 


 

8 – Crie mais dois Nomes dinâmicos: um para retornar uma matriz, que será usada mais para frente e outro para exibir a imagem selecionada. Clique na planilha Apoio2.

9 – Na guia Fórmulas, grupo Nomes Definidos, botão Gerenciador de Nomes. Clique em Novo Nome. No campo Nome digite “Matriz” e no campo Refere-se a digite a fórmula a baixo:

 

=DESLOC(Apoio2!$A$2;0;0;CONT.VALORES(Apoio2!$A:$A)-1;2)

Onde:

 

Apoio2!$A$2

Referência. É uma célula da planilha Apoio2, onde está baseado o deslocamento;

0 – Linhas. Neste caso não se desloca linhas;

0 – Colunas. Neste caso não se desloca colunas;

CONT.VALORES (Imagem!$A:$A)-1 – Altura. Determina a altura da tabela, neste caso a fórmula determina a altura de acordo com o número de linhas não nulas na coluna A e o número -1 que se encontra depois dos parênteses irá retirar a linha de cabeçalho da seleção.

2 – Largura; Determina à largura da tabela, neste caso a largura é dois, selecionando os nomes das imagens e também o número da linha onde se encontram na planilha Apoio2.

 

Alterando a imagem

 

Embora essa fórmula seja muito semelhante à fórmula anterior, há uma diferença fundamental, desta vez a largura da tabela é o número 2, retornando os valores das duas colunas da tabela.

10 – Repita os mesmos procedimentos para criar o último range que será identificado como “Imagem” e se referira à:

=INDIRETO(“Apoio!$A$”&ÍNDICE(Matriz;CORRESP(Imagem!$A$1;Lista;0);2))

Onde:

 

“Apoio!$A$” – Referência. Aqui, o endereço está dividido, a coluna será determinada por esta expressão (coluna A da planilha Apoio) e a linha será retornada pela combinação das funções ÍNDICE e CORRESP;

& – Concatenação. Une as duas partes do endereço;

ÍNDICE – Referência. Começo da referência da linha;

Matriz – Matriz. É a matriz onde será realizada a busca, usamos o nome Matriz, que definido no passo anterior, referenciando assim, a matriz que contém nomes e números de linhas;

CORRESP – Núm_linha. Usa-se o CORRESP para encontra o número da linha que será usado na pesquisa da função ÍNDICE;

Imagem!$A$1

Valor_procurado. É o valor que se deseja encontrar;

Lista – Matriz_procurada. É a matriz onde se deseja encontrar o valor de A1, usamos o nome Lista
para que o intervalo seja dinâmico, e quando um novo nome seja adicionado, automaticamente ele faça parte da fórmula;

0 – Tipo_correspondência. Procuramos um valor exato, por isso usa-se o 0, além disso, o 0 indica que a tabela não precisa estar organizada para que a fórmula funcione corretamente;

2 – Núm_coluna. Assim, a fórmula retorna o que está na segunda coluna da matriz onde a busca está sendo realizada, retornando, assim, o número da linha onde se encontra a imagem na planilha Apoio.

 

Nesta fórmula, um trecho da expressão retorna a coluna que será usada no indireto (“Apoio!$A$”) e o restante é responsável por identificar a linha. O “&” serve para concatenar as duas partes, retornando endereço de uma célula na coluna A da planilha Apoio.

11 – Para finalizar utilizaremos o recurso câmera para criar uma Imagem da célula encontrada pelo nome Imagem.

 


 

Com qualquer célula da planilha Apoio
selecionada, clique no botão Câmera.

12 – Feito isso, clique sobre a planilha Imagem e clique em qualquer célula.

Será exibida uma foto qualquer. Selecione essa imagem e apague a fórmula exibida na Barra de Fórmulas e digite =Imagem. Pressione ENTER e observe o resultado.

 


 

Conforme você clica na célula A1(que possui a seta para baixo da Validação) e escolhe um nome, a foto é trocada imediatamente.

 


 

13 – De acordo com a imagem escolhida em A1, o nome Imagem relacionará o nome de A1 com os valores de linha na coluna A, retornando um endereço da planilha Apoio2, que será fotografado e exibido. Retire bordas e preenchimentos desta imagem, e redimencione-a.

Para uma melhor exibição retire as linhas de grade da planilha, oculte a barra de fórmulas, desative as guias e deixe as guias no modo Auto-ocultar. Oculte as planilhas Apoio e Apoio2.

Pronto, ao escolher o texto na caixa de validação a imagem será automaticamente alterada.

 


 

Espero, mais uma vez, ter contribuído com a PRODUTIVIDADE!

Até a próxima!!!

MAC

Anúncios

22 comentários em “Trocando o texto da célula por uma figura – via Validação

  1. Marco Aurélio,

    Estou precisando de um Help, fazendo o que vc fez com imagens só que com outras listas. Exemplo:
    COLUNA 1 – LISTA GERÊNCIA COLUNA 2- Lista Setor
    Industrial Gostaria que aqui abrisse uma lista apenas com os setores respectivos de cada
    Administrativa gerência. Que ao selecionar a opção na coluna 1 a lista fosse restringida.
    Agropecuária
    Comercial

    Será que deu para entender?

    obrigada,

    Curtir

  2. Nossa, desconfigurou o coment…bagunçou tudo..

    Considere,
    Coluna 1 – Lista 1
    Gerencia Industrial
    Gerencia Administrativa
    Gerência Agropecuária
    Gerência Comercial

    Coluna 2 – Lista 2
    Gostaria que aqui abrisse uma lista apenas com os setores respectivos de cada
    gerência. Que ao selecionar a opção (Gerência) na coluna 1 a lista da coluna 2 fosse restringida.

    Acho que ficou melhor assim, obrigada!

    Curtir

    1. Olá Silvania!

      Sim, eu consegui entender…
      Bom, em primeiro lugar, esse exemplo é complexo e vou te orientar como chegar lá, ok? Isso não significa que irei te ensinar passo a passo o que deve ser feito mas lhe passar o caminho das pedras:
      1 – Crie uma lista nomeada com intervalo dinâmico: Utilize as funções DESLOC e CONT.VALORES para criar essa lista (vc encontrará mais informações sobre o assunto nesse mesmo exemplo e em outros que tenho postado).
      2 – Faça uma validação dessa lista (vc tb encontrará mais informações no meu Blog).
      3 – Após vc precisará criar uma segunda lista, puxando os dados de acordo com as opções selecionadas na primeira. Para isso, vc deverá utilizar uma função chamada INDIRETO (que tb se encontra nesse exemplo) e inserir essa segunda lista em uma Validação.
      4 – Somente a partir desse momento é que vc utilizará as funções Índice e Corresp para buscar a correspondência de acordo com as opções selecionadas.

      Vou tentar escrever mais sobre o assunto e com riqueza de detalhes assim que eu estiver mais livre, ok?

      Espero pelo menos ter lhe dado a orientação a seguir.

      Um abraço,

      Marco Aurélio

      Curtir

  3. Marco,
    Eu faço o procedimento todo com sucesso. Porém no passo 12, na hora trocar a fórmula exibida na Barra de Fórmulas e digitar =Imagem, a seguinte mensagem aparece: “Essa referência não é válida.”
    Qual o problema com a minha formula. Estou usando Excel 2010.

    Curtir

      1. Olá Gustavo,

        Centenas de pessoas já me escreveram sobre esse “tipo de problema”.

        Segue a explicação:

        Quando copiamos diretamente da Internet para o Excel, alguns símbolos são trocados: Aspas simples, duplas, espaços e caracteres especiais.

        Se você puder digitar a fórmula na íntegra, verá que funciona perfeitamente (ou copie do browser mas altere os caracteres que comentei aqui).

        Espero que dê certo!

        Marco Aurélio

        Curtir

  4. Eu gostaria de saber como faço para utilizar em diversas linhas na mesma planilha … como uma planilha de satisfação .. onde vc coloca o número do atendimento e seleciona o grau de satisfação e aí exibe o emoticon .. mas são diversas linhas

    Curtir

  5. No caso de precisar usar em outras linhas o código:

    =INDIRETO(“Apoio!$A$”&ÍNDICE(Matriz;CORRESP(Imagem!$A$1;Lista;0);2))

    Teria que sofrer alterações na referência Imagem!$A$1 não poderia se limitar a uma célula né.. como ficaria?

    Curtir

  6. Olá Marco A,

    Vi e segui passo-a-passo suas explicações pelo mesmo post no YouTube, que ficou um pouco diferente do que você demonstrou no blog. O problema é, que na hora que vou definir a fórmula “Imagem” para a imagem que eu criei, aparece um erro dizendo que a referência não é válida.. Fiz e refiz as fórmulas e continua aparecendo o mesmo erro.. Você sabe o que pode estar acontecendo?

    Desde já muito obrigado!

    Curtido por 1 pessoa

    1. Fala xará, rs!

      Essa sua dúvida já foi questionada por 1 centena de pessoas aqui no Blog, rs!

      O que ocorre é o seguinte: quando vc copia a fórmula do Blog e cola direto no seu Excel, as “aspas” não são entendidas pelo Excel. Por favor, apague as aspas e digite-as novamente dentro da fórmula no Excel.

      Obrigado pela pergunta e espero que funcione.

      Abraço,

      Marco Aurélio

      Curtir

      1. Caro Xará, rs.

        Tentei de tudo aqui e mesmo assim não funciona.. Fui fazendo passo-a-passo e nada feito.
        Como disse, me utilizei do jeito que você demonstrou no video do Youtube que é um pouco diferente da do blog, pois não utiliza a fórmula ÍNDICE.

        Minhas fórmulas ficaram assim:
        =DESLOC(Apoio!$A$1;0;0;CONT.VALORES(Apoio!$A:$A);1)
        =INDIRETO(Apoio!$A:$A&CORRESP(‘Análise Produtos’!$C$12;Lista;0))

        Uma coisa que deu errado da sua fórmula, é um – ” – que existe na primeira seleção da fórmula INIDIRETO, que da erro também ao tentar fazer a fórmula.

        Viu algo de errado?

        Abraços!

        Curtir

  7. Marco, eu também estou com o mesmo problema do seu xará. Criei uma planilha nova e refiz o passo a passo do seu vídeo, deu certo. Mas quando tento aplicar o esquema numa planilha em que eu estou trabalhando, dá erro de referência também. Já apaguei e reescrevi a fórmula toda e continua com erro.

    Poderia me dizer o que pode estar errado?

    Curtido por 1 pessoa

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s