4° Super desafio do ExcelMax® aos usuários do Excel

A Barra de Status do Excel 2007/2010 apresenta diversas utilidades:

• Funções que calculam valores de dados selecionados sem a necessidade da utilização de fórmulas em células;
• Mensagens que informam ao usuário sobre o status do Excel;
• Botão para iniciar a gravação de macros;
• Botões para alterar os modos de visualizações da planilha;
• Opção para alterar o zoom da planilha (tamanho de visualização.

3° Super desafio do ExcelMax® aos usuários do Excel

 Pergunta do 3° Desafio:

Normalmente quando fazemos uma procura no Excel, utilizamos a função PROCV. Como Faço uma procura da direita para a esquerda?
 
 

 

Em primeiro lugar vamos lembrar que a função PROCV faz procuras na Vertical apenas da esquerda para a direita.

  

Para fazermos uma procura da direita para a esquerda, precisamos utilizar outras funções: "Desloc + Corresp" ou "Índice + Corresp".

O terceiro desafio aos usuários do Excel foi respondido pelo amigo Wellington Santos Silva , com sugestão da utilização de uma fórmula com as funções ÍNDICE + CORRESP.

Note o exemplo na figura abaixo: Na célula 'H23' foi selecionado o código do aluno 311597. Nas células da mesma linha (linha 23), tanto para a esquerda como para a direita, com a utilização das funções ÍNDICE e CORRESP, retornamos as informações referente ao código digitado (célula 'H23').
 
Como o código da célula 'H23' corresponde ao mesmo código da célula 'H16', as informações da linha 23 serão os mesmos da linha 16.

 

 

 

Sintaxe da função Índice:

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

• matriz: é um intervalo de células ou uma constante de matriz.

 

• núm_linha: seleciona a linha a partir da qual um valor deverá ser retornado.

 

• núm_coluna: seleciona a coluna na matriz a partir da qual um valor deverá ser retornado.

 

Sintaxe da função Corresp:

= CORRESP ( valor_proc ; matriz_proc ; tipo_corresp )

• valor  _proc: O valor que você d  e  seja corresponder em matriz_procurada.

 

• matriz_proc: O intervalo de c    élulas que estão sendo pesquisadas.

 

• tipo_corresp  : O número -1 (valor maior) , 0 (exato) ou 1 (valor menor).

 

 

Links de postagens que explicam a utilização da função DESLOC que pode ser utilizada assim como a função ÍNDICE.

• Tabela Móvel em uma Base de Dados (Desloc & Procv (Vlookup))

 

• Gráfico: Orçamento X Realizado - Parte II

 

• Funções: Procv, Desloc, Índice e Corresp

 

• Funções de Procura no Excel 2007 - ÍNDICE + CORRESP

 

• Funções de Procura no Excel 2007 - DESLOC + CORRESP

 

Link de um vídeo no YouTube que explica a utilização da função DESLOC que pode ser utilizada assim como a função ÍNDICE. Explica também a utilização da função PROCV.

• Link: Funções no Excel: Procv X Desloc + Corresp

 

Download: Exemplo - Índice + Corresp

Se gostou desta postagem comente e indique a seus amigos. Se desejar, participe do 4° Desafio que já está lançado (veja no blog).

Obrigado! Wellington Santos Silva pela participação, não só a este desafio, mas a todas as suas participações aqui no ExcelMax®.

Abraço a todos e até a próxima.

Obrigado por estar fazendo parte do ExcelMax® !!!
Fique a vontade para sugerir assuntos específicos para novas postagens.
Se desejar, encaminhe email com sua sugestão ou dúvida para: ivairferrari@gmail.com.

Your link text here.

Análises com Fórmulas e Gráficos no Excel

 Observação importante:

 

Esta postagem é de nível avançado com a utilização de recursos: Fórmulas, Gerenciador de Nomes, Gráfico, Validação de Dados e Formatação Condicional.

Para um bom entendimento, sugiro bastante atenção na leitura da postagem e na análise do exemplo que pode ser baixado no link (final na postagem).

 

Analisar as informações em uma tabela é a parte mais importante pois, de nada adianta ter informações e não tirar proveito das mesmas.

 

Estas análises podem ser feitas de muitas maneiras com a utilização de muitos recursos sempre alienados a uma boa criatividade.

 

A tabela abaixo simula os dados de uma pesquisa feita em determinados estados. Nesta pesquisa, foram feitas algumas perguntas: Estado (UF), estado civil, sexo, moradia (aluguel ou casa própria), se a pessoa possui ou não automóvel, se é fumante ou não, a escolaridade e se possui filhos (quantidades).

 

Podemos notar que não é possível analisar as informações simplesmente por observar a tabela. Para isso, é importante que seja feita uma tabulação (consolidação) dos dados coletados.

Mas, como podemos analisar os dados desta base de dados? Quantas tabelas e/ou gráficos precisaremos criar? Bem, tudo depende do que sabemos fazer e de nossa criatividade.

A tabulação dos dados coletados será feita com a utilização de algumas fórmulas. Cito algumas que serão utilizadas: DESLOC, CORRESP, CONT.SE, CONT.VALORES.

A nossa tabela de dados possui 8 colunas com algumas linhas de informações. Normalmente quando precisamos analisar todas as informações de uma tabela com esta quantidade de colunas (ou mais colunas) elaboramos uma tabela com algumas colunas ou até, mais do que uma tabela. Se desejamos analisar os dados graficamente observando valores e/ou percentuais, normalmente criamos alguns gráficos.

Nosso objetivo é tabular (ou consolidar) todas estas informações em uma única tabela e um único gráfico com uma opção de seleção para alterar entre as 8 colunas. A primeira fórmula que vamos criar através do 'Gerenciador de Nomes' (guia Fórmulas) é a mais importante pois, através dela, teremos a opção de selecionar a coluna desejada que será mostrada na tabela de tabulação e no gráfico de barras. No Gerenciador de Nomes vamos criar o nome 'Origem' que conterá algumas funções formando assim uma fórmula conforme vemos na figura abaixo.

 

Fórmula 'Origem':

• =DESLOC(Base!$A$1;1;CORRESP(Dados!$L$1;Base!$A$1:$J$1;0)-1;CONT.VALORES(DESLOC(Base!$A$1;1;CORRESP(Dados!$L$1;Base!$A$1:$J$1;0)-1;10;1));1)

Com esta fórmula, é possível selecionarmos a coluna desejada para ser demonstrada na tabela de tabulação e no gráfico. Note que o nome 'Origem' (que contém uma fórmula com algumas funções do Excel) está contida na fórmula a seguir que se encontra na célula 'L2':

• =SE(ÉERROS(DESLOC(Origem;0;0;1;1));"";DESLOC(Origem;0;0;1;1))

Note que o número destacado (0) é a única coisa a ser alterada nas células das linhas seguintes: 'L3' até 'L7' (com os números 1 à 5 respectivamente.

Na fórmula acima, utilizei mais uma vez a função DESLOC para mostrar mais uma vez em um exemplo visto ser uma função muito útil. Podemos porém, utilizar uma fórmula bem mais simples no lugar da mencionada acima.

• =PROCH($L$1;Base!$A$1:$J$7;LIN();FALSO)

A fórmula na célula 'M2' faz a contagem dos itens referente ao critério contido na célula 'L2'.

• =CONT.SE(DESLOC($A$1;1;CORRESP($L$1;$A$1:$J$1;0)-1;30;1);L2)

Na célula 'N2', a fómula faz o cálculo da participação percentual que cada valor da coluna 'M' tem sobre o total dos itens consolidados.

• =M2/SOMA($M$2:$M$7)

Além das fórmulas utilizadas para a elaboração da tabela onde os dados serão tabulados, utilizei a Formatação Condicional para destacar as bordas das linhas (quando utilizadas).

Por fim, os dados tabulados são demonstrados em um gráfico de barras (tanto em valores como em percentuais). Os dados demonstrados no gráfico podem ser facilmente alterados, isto é, podemos visualizar o que desejarmos apenas selecionando o tipo da informação (célula 'L1'): estado (UF), estado civil, sexo, moradia, carro, fumante, escolaridades e filhos.

Para criação do Gráfico foram criados dois Nomes (no Gerenciador de Nomes) que contém algumas fórmulas.

Nome Descrição (fará a seleção dos nomes dos rótulos do eixo horizontal (Categorias):

• =DESLOC(Dados!$L$1;1;0;CONT.SE(Dados!$M$2:$M$7;">"&0);1)

Nome Valores (fará a seleção dos valores referente a Descrição):

• =DESLOC(Dados!$L$1;1;Dados!$O$1;CONT.SE(Dados!$M$2:$M$7;">"&0);1)

Observação: A célula mencionada acima (Dados!$O$1) está é o local onde os objetos 'Contagem' e 'Part. %' estão vinculados para que o gráfico possa alternar entre mostrar a contagem dos valores ou a participação percentual.

Para selecionar os dados para a criação do gráfico, utilizamos estes dois nomes (Descrição e Valores). O gráfico terá as funcionalidades conforme mencionado e com mais algumas formatações, pode ficar com a aparência da figura abaixo. 

Se gostou desta postagem, utilize-a fazendo os ajustes necessários as suas necessidades e repasse a seus amigos. Se desejar divulgue o endereço para que outras pessoas possam ver as explicações de como criar/trabalhar com este exemplo.

Se ficou alguma dúvida, favor postar comentário.

Download: DashBoard - 2011 - Tabela e Gráfico

Até a próxima.

 

2° Super desafio do ExcelMax® aos usuários do Excel

2° Super desafio do ExcelMax® aos usuários do Excel



Preciso fazer uma procura entre duas planilhas pelo nome das pessoas. Em uma delas, o nome das pessoas possui acento (nas palavras) e na outra não. Como faço isso?

Recebi algumas sugestões mas nenhuma delas faz a procura de forma correta.

Solução:

A mais famosa função de procura: Procv (Vlookup)

Uma das atividades mais realizadas no Excel é a busca (ou procura) de dados ou informações. Existem muitas maneiras de fazermos uma procura por informações no Excel. Uma das maneiras mais comum é a utilização da função: PROCV.

O “V” em “PROCV” significa vertical. Use PROCV em vez de PROCH quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que se deseja procurar.

A função PROCV: Localiza um valor na primeira coluna à esquerda de uma tabela e retorna um valor na mesma linha de uma coluna especificada na tabela.

1° Super desafio do ExcelMax® aos usuários do Excel

1° Super desafio do ExcelMax® aos usuários do Excel

Tenho uma planilha com 5.000 nomes na coluna 'A'. Como colocar estes 5.000 nomes na célula 'B1' separados por vírgula?

A resposta veio de meu amigo Marcos Fávero.

Ele desenvolveu um pequeno código (macro) que percorre as 5.000 linhas da coluna 'A', que possui 5.000 nomes de pessoas, e os coloca na célula 'B1' separados por vírgula: segue abaixo, como ficou o código:

Curiosidades

Especificações e limites de planilha e pasta de trabalho no Excel 2007:

Ocorreram alterações nos recursos e limites do “Excel 2003” para o “Excel 2007”. Abaixo, encontramos uma tabela com estes valores:

Macros: Gerando um Relatório

Gerando um Relatório

As Macros no Excel podem agilizar em muito as atividades que desenvolvemos.

Como mencionei nas postagens anteriores (Macros no Excel, Macros no Excel: Uso de Variáveis e Macros com variáveis e condições) com a utilização de variáveis podemos nos deslocar facilmente em uma base de dados e extrair as informações desejadas da forma que melhor nos convier.

 

Macros com variáveis e condições

Nas duas postagens anteriores a esta, dei uma breve explicação sobre a utilização de variáveis nas macros do Excel.

Dando continuidade a este assunto, esta postagem menciona exemplos que trabalham com variáveis e comandos inteligentes: Que tomam decisões dependendo das informações dispostas na planilha.

Na figura abaixo vemos uma pequena tabela de dados que será utilizada como exemplo. Nosso objetivo bastante simples: fazer com que a macro percorra a tabela e faça uma contagem das pessoas de determinado sexo.