Procura Customizada no Excel

    Quando criamos uma planilha no Excel, a melhor maneira para darmos destaque as informações é a utilização de recursos que nos permitam uma vizualização rápida, clara e objetiva do que estamos querendo visualizar. Os gráficos, por exemplo, são ideais para isso. Mas, além dos gráficos, os diversos tipos de formatos que podemos aplicar aos dados de uma planilha, também são de grande utilidade.

    Vamos imaginar que estamos criando uma simples agenda com dados de clientes. Nesta agenda, precisamos de apenas três colunas: código, nome e telefone. Bom, a princípio, isto é muito fácil de fazer, pois precisamos de apenas três colunas e dos dados dos clientes. Mas, depois que os dados dos clientes estiverem todos inclusos nesta agenda, pode surgir as seguintes situações:

• Preciso encontrar um determinado número de telefone e verificar se pertence a um de nossos clientes;
• Preciso encontrar um determinado cliente e verificar qual é o seu telefone;
• Muitas vezes, posso não lembrar exatamente o nome do cliente. Como fazer para encontrá-lo, se a quantidade de clientes for grande?
• Sei o código do cliente, mas não sei o nome do mesmo.
• Podem surgir ainda outras situações...

    Existem muitas maneiras de resolvermos esta questão. Esta postagem mostrará que podemos fazer isto através de fórmulas com algumas funções do Excel e formatações.

    A figura abaixo mostra a agenda com os dados de alguns clientes. 

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

Quando trabalhamos com uma base de dados de tamanho grande ou médio, podemos visualizar, analisar ou filtrar os dados com algumas ferramentas do Excel.

Porém, podemos visualizar os dados ou parte deles de outra forma: elaborando um mini-tabela que percorra a base maior.

Esta postagem tem por objetivo demonstrar como podemos fazer isto e ainda, como os dados ficarão resumidos a uma parte da tabela maior, podemos demonstrar estes valor em gráficos.

Criar um Cadastro no Excel com VBA

Criar um Cadastro no Excel com VBA

Tenho percebido em minhas pesquisas e análises estatísticas do Blog (ExcelMax® Soluções) que há uma grande procura por macros/vba no Excel.

Muitos não conhecem e querem aprender. Outros, já tem uma boa noção e querem aprimorar este conhecimento. Outros, já sabem muito, mas, todo bom estudante sempre quer aprender mais, assim como eu, que também sempre estou estudando.

A partir desta postagem, de uma série de outras, estarei passando orientações, passo a passo, de como criar um cadastro, que no meu ponto de vista, é um um bom exemplo para iniciar a aprender a trabalhar com VBA/macros no Excel.

A primeira coisa a fazer é criar uma pequena 'base de dados'. Isso deve ser feito para que possamos criar as telas de acordo com cada campo (coluna) da base de dados. A figura abaixo, apresenta os dados que vamos utilizar como exemplo.

Alguns vídeos 'ExcelMax Soluções' no YouTube

    O objetivo desta postagem é listar e dar uma breve explicação de alguns vídeos que estão no YouTube e, que podem ajudar em diversos aspectos do aprendizado do Microsoft Excel.

Loteria: Mega$ena de Final de Ano !

    Com certeza, muitos estão apostando na 'Mega$ena da Virada' que está dando um prêmio superior a 190 milhões de reais.

    Muitos jogam os cartões que vem fazendo a muito tempo, outros fazem um bolão com os amigos, outros preferem jogar sozinhos, enfim, são diversas as maneiras que as pessoas apostam.

    Nesta postagem, estou simplesmente colocando um simulador para 20 cartões da 'Mega$ena' onde ele distribui os números de forma a não deixar nenhuma dezena fora das apostas ou, caso desejar, pode escolher a quantidade de dezenas sem nenhum sorteio (Quanto menor a quantidade, maior será o tempo para que o simulador encontre um simulação apropriada).

Loterias: Mega-Sena, Quina e LotoFácil com o Excel e VBA (Macros)

Loterias: Mega-Sena, Quina e LotoFácil com o Excel e VBA (Macros)

Muitas pessoas fazem apostas em muitos jogos toda semana. Um dos jogos existentes a bastante tempo no Brasil é a 'Quina'.

Através do Excel e VBA (macros), podemos criar um pequeno software (simulador) para que o mesmo desdobre cartões e monte bolões (diversos cartões) aumentando as chances de acertar as dezenas sorteadas.

Este simulador, permite criar desdobramentos da 'Quina' com 6 dezenas até as 80 dezenas da cartela.

Aplicativo em Excel para Mega Sena

Aplicativo em Excel para criar jogos da Mega-sena

Aplicativo desenvolvido em Excel para fazer desdobramentos de jogos da Mega-Sena.

Teclas de Atalhos - agilizam seu trabalho no Excel

    Sempre digo que quando aprendemos (realmente) a utilizar as teclas de atalho é como quando dirigimos um automóvel: "quando dirigimos um automóvel, já há algum tempo, não precisamos pensar para trocar de marcha, acelerar ou frear, ouvir o som que está ligado, pensar em sua família e assim por diante. Da mesma forma, quando utilizamos o Excel a algum tempo e, de certa forma, dominamos a utilização das teclas de atalho, não precisamos pensar muito em qual ou em qual o momento vamos utilizá-las".

Gráfico Móvel com a função Desloc no Excel

Gráfico automático com a função DESLOC no Excel    

Esta postagem faz referência a um vídeo publicado em meu canal no YouTube: ivairferrari. Este vídeo ensina: Como podemos criar um gráfico que, através de um objeto (barra de rolagem), possa ser movimentado na base de dados (através da utilização da função DESLOC), alterando automaticamente os valores plotados/demonstrados neste gráfico.

Tabela Móvel com a Função DESLOC

    A função mais lida e solicitada pelos leitores do ExcelMax® é a função DESLOC!
    Esta função é muito útil em muitos aspectos podendo ser utilizada em muitas ocasiões: consolidação de dados, criação de tabelas e gráficos, criação de dashboards e em muitos outros exemplos.
    Tenho percebido que em muitas ocasiões, a maior dificuldade que os usuários encontram é: quando e como utilizar determinada função.

Criar um gráfico de velocímetro no Excel

Gráfico de Velocímetro no Excel

O gráfico apresentado nesta postagem foi elaborado a partir de dois gráficos.

• Mostrador: é um gráfico de rosca dividido e 4 partes.
• Agulha: é um gráfico de dispersão com dois pontos.

Este é um excelente gráfico para ser utilizado para demonstrar desempenhos e outros valores resultantes de um conjunto de informações. Poder ser utilizado individualmente ou em conjunto com outros gráficos. (na elaboração de DashBoards).

Super Fórmula no MS Excel

    No dia 17/08/2011 postei no YouTube o vídeo: Fórmula Matricial - Super Fórmula. Na parte final desta postagem há um link para download da planilha exemplo.

    Este vídeo demonstra como podemos extrair informações de uma base de dados em uma planilha utilizando fórmulas matriciais criadas com as funções do Excel.

    Podemos montar uma tabela flexível para que, através de diversos critérios, possamos fazer totalizações e diversas analises.

DashBoards: de simples números a um Show de Informações!!! (Vídeo)

No dia 10 de abril de 2011 publiquei uma postagem intitulada:

• DashBoards: de simples números a um Show de Informações

Esta postagem aborda vários recursos do Excel que podem ser utilizados na criação de DashBoards.

Conferência Cartelas de Jogos da MegaSena & LotoFácil

Conferência de Jogos da MegaSena:

Este tutorial, mostra como o Excel pode nos ajudar a fazer uma conferência de jogos da MegaSena. Os recursos utilizados foram: formatação condicional e a função Proch.

Extraindo Informações com Macros no Excel - Parte II

Na postagem anterior: Extraindo Informações com Macros no Excel vimos que podemos extrair facilmente, informações de uma base de dados com a utilização de Macros.

A macro demonstrada nesta postagem é bem simples pois, o critério para a extração das informações fica dentro do código (macro). Esta postagem mostrará como podemos fazer para que o usuário possa escolher a coluna a ser filtrada e o critério a ser utilizado.

Extraindo Informações com Macros no Excel

Existem muitas maneiras de extrairmos informações de uma base de dados. Uma delas, que é pouco utilizada, é com a utilização de Macros.


Muitas pessoas têm dificuldade em entendê-las e existe certo bloqueio nas pessoas de que: é difícil de aprender a trabalhar com elas (as Macros).

Na realidade, quando passamos a utilizá-las com mais frequência, vamos perceber que não são tão difíceis quanto parecem.

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

6° Super desafio do ExcelMax® aos usuários do Excel:

• Uma planilha possui: Datas na coluna "A" e números de 1 à 999 nas colunas "B" à "F" em várias linhas que podem repetir. Preciso encontrar as datas para os números que se repetem e listar: números e datas (na mesma linha).

Recursos: Atingir Meta X Solver - VÍDEO

Recursos: Atingir Meta X Solver

Vídeo tutorial que demonstra como utilizar os recursos: Atingir Meta e Solver.


Neste vídeo, você pode também entender o que há de igual entre os dois recursos e o que há de diferente.

Recurso: Atingir Meta do Excel - Em Vídeo

 Atingir Meta:

A postagem Recurso Atingir Meta do Excel abordou sobre como encontrar um resultado desejado ajustando um valor de entrada.

Nesta postagem há explicações sobre o recurso Atingir Meta através de um exemplo.

Na postagem há também um link para download da planilha que foi utilizada que serve como exemplo para praticar e verificar se o assunto abordado foi entendido.

O vídeo abaixo explica passo-a-passo sobre a utilização deste recurso. O mesmo pode ser visto aqui no ExcelMax® ou pode ser visto no YouTube (YouTube.com/user/IVAIRFERRARI)

 

Consolidando Informações com o MS Excel

  O Microsoft Excel é uma poderosa ferramenta que, a grande maioria das pessoas tem a mão porém, pouco utilizada.


  Em que sentido? No sentido de não utilizar da melhor maneira possível deixando de extrair todo o seu potencial - gerando informações com extrema rapidez, com muita facilidade e, com informações confiáveis ... de qualidade.


   Chocado com esta afirmação !!! Porém é uma realidade. Mas, isso não incluir todos mas, a grande maioria. Felizmente há aqueles que se dedicam um pouco mais para aprender a utilizá-la. Este alguém pode ser você que está lendo está postagem. Se assim for, meus parabéns e, continue estudando e buscando um maior conhecimento assim como eu procuro fazer.

Recurso: Solver do Excel - Em Vídeo

SOLVER (explicação via Vídeo):


A postagem anterior abordou sobre o recurso Recurso Solver do Excel.


Esta nova postagem, traz um tutorial (em VÍDEO) explicando o mesmo exercício utilizado na postagem anterior.

Recurso: Solver do Excel

SOLVER:

Na postagem anterior: Recurso Atingir Meta do Excel vimos que é possível encontrar um resultado desejado ajustando um valor de entrada.

Este recurso, apesar de muito útil, é limitado, pois, como vimos, com ele podemos ajustar apenas um valor de entrada. Além disso, o recurso 'Atingir Meta' obedece apenas a um critério: atingir o resultado desejado sem a opção de podermos estabelecer mais critérios.

Recurso: Atingir Meta do Excel

Atingir Meta:


Encontrar um resultado desejado ajustando um valor de entrada

Se desejamos obter um resultado (valor), mas não sabemos qual o valor de entrada necessário para chegar a esse resultado, podemos utilizar o recurso 'Atingir Meta'. 

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

5° Super desafio do ExcelMax® aos usuários do Excel:

• Preciso calcular: Anos, Meses e Dias entre duas datas

Por alguns fatores, podemos encontrar certa dificuldade para obter o resultado do cálculo mencionado acima:

• Ano Bissexto
• Meses com quantidades diferentes de dias

Sugestão II - 1° Super desafio do ExcelMax® aos usuários do Excel

Na postagem: 1° Super desafio do ExcelMax® aos usuários do Excelfoi lançada a seguinte pergunta:

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

Como resposta foi postado o seguinte algorítmo:

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.