Vlookup – como procurar dados em uma tabela do excel
A leitora Claudia fez a seguinte pergunta:
Olá, preciso urgente de uma ajuda, tenho que montar uma planilha onde consigo calcular os valores a serem cobrados dos funcionário referente ao plano de saude, porém, preciso transformar cada faixa etária com seu respectivo valor através da idade de cada uns, efazer com que possa deixar esta alteração automatica para as mudanças de faixa etária. Como faço isso? segue um exemplo:
ex 1
funcionário 1/01/1979 – 31 anos – faixa de 27 a 31 anos – valor 70,00
ex 2
alterou idade p/ 32 anos – faixa de 32 a 36 e valor 90,00?
como deixar esta alteração de valores automatica de 31 p/ 32 anos???
Muito obrigado
Felizmente é muito fácil resolver esse problema no Excel. A planilha do Office possui duas funções feitas exatamente para esses casos: VLOOKUP e HLOOKUP, respectivamente PROCV e PROCH no Excel brasileiro.
Vamos montar uma planilha de exemplo, com faixas etárias e uma alíquota correspondente para cada uma, como na imagem abaixo:
VLOOKUP - tabela de dados
Agora, vamos ver a função VLOOKUP (PROCV).
Sintaxe de VLOOKUP e HLOOKUP
= PROCV (valor_pesquisa;tabela_matriz;no_index_lig;valor_aproximado)
(=VLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
valor_pesquisa: recebe o valor de referência que desejamos procurar, no nosso exemplo, a idade do funcionário.
tabela_matriz: a tabela que contem os dados chave (faixa etária) e o valor desejado (alíquota).
no_index_lig: indica a coluna da tabela onde está o valor que desejo encontrar. No nosso caso, aliquota é a terceira coluna da tabela.
valor_aproximado: se for FALSE (FALSO) o valor indicado em valor_pesquisa deve ser EXATAMENTE o valor encontrado na primeira coluna da tabela_matriz, se for TRUE(VERDADEIRO), pode ser aproximadamente igual. No nosso caso, temos que utilizar TRUE pois a idade nem sempre estará presente na primeira coluna.
Com isso, podemos montar nossa tabela com as indicações corretas.
VLOOKUP - exemplo
Nesse exemplo, indiquei a data de nascimento do funcionário, em seguida, fiz o cálculo da idade no excel e a partir dessa idade busquei a aliquota correta na primeira tabela.
Explicando os termos da função VLOOKUP(C12;$B$3:$D$8;3,TRUE):
C12 é a posição da idade do funcionário;
$B$3:$D$8 é a tabela com os dados da faixa etária e da alíquota correspondente.
3 é o número da coluna “alíquota” na primeira tabela.
TRUE, porque desejo um valor aproximado.
Para finalizar, a diferença entre VLOOKUP (PROCV) e HLOOKUP (PROCH) é que VLOOKUP utiliza a primeira coluna para a busca, enquanto HLOOKUP utiliza a primeria linha.
Claudia, espero ter ajudado.
A todos os usuários, aguardo novas dúvidas.
Intervalo de datas em dias, meses e anos no Excel
Em outro post um internauta enviou uma mensagem muito boa: -como calcular o intervalo entre duas datas e obter a resposta em dias meses e anos.
Na verdade é muito simples de se fazer isso no Excel, basta utilizar a função DATEDIF().
Tudo sobre DATEDIF()
Tá bom, quase tudo sobre DATEDIF(). Vamos lá!
Ela calcula o intervalo entre duas datas e recebe apenas três parâmetros:
Data Inicial, Data final e tipo de cálculo. As duas primeiras são bastante óbvias, as datas que desejo calcular o intervalo. A terceira opção possui 6 valores:
| valor | descrição | explicação |
| “d” | dias | calcula a quantidade de dias entre duas datas |
| “m” | meses | calcula a quantidade de meses entre duas datas |
| “y” | anos | calcula a quantidade de anos entre duas datas |
| “yd” | Dias excluídos anos | calcula a quantidade de dias entre duas datas, como se elas estivessem no mesmo ano |
| “ym” | meses excluídos anos | calcula a quantidade de meses entre duas datas como se elas estivessem no mesmo ano |
| “md” | Dias excluídos meses e anos | calcula a quantidade de dias entre duas datas, como se elas estivessem no mesmo mês do mesmo ano |
Vamos deixar isso um pouco mais claro…
as três primeiras opção são bem óbvias, e dão o resultado do intervalo em dias ou meses ou anos. Por exemplo, entre 28/02/1970 e 20/11/2005, se passaram 13049 dias. Ou ainda, 428 meses. Ou 35 anos.
Já as três seguintes costumam confundir um pouco mais.
YD é o intervalo em dias se as datas estivessem no mesmo ano. No exemplo abaixo, entre 28/02 e 20/11 há 265 dias.
YM é o intervalo em meses se as datas estivessem no mesmo ano. No exemplo abaixo, entre 28/02 e 20/11 há 8 meses.
MD é o intervalo em dias se as datas estivessem no mesmo mês ou em meses consecutivos. No exemplo abaixo, entre o dia 28 de um mês e o dia 20 do mês seguinte há 23 dias. Nesse cálculo são considerados meses de 31 dias.
função datedif no excel
E se eu quiser algo mais complexo, como o exemplo do título, a diferença em dias meses e anos? Bem, basta “juntar” os pedaços. No excel, para anexar um texto a uma fórmula, basta utilizar o &. Portanto, a fórmula:
=TEXT(DATEDIF(B7;B8;”Y”);0) & ” anos, ” & TEXT(DATEDIF(B7;B8;”YM”);0) & “meses e ” & TEXT(DATEDIF(B7;B8;”MD”);0) & ” dias”
exibe a caixa abaixo:
excel - data em dias, meses e anos
Como calcular diferença de datas (idade) no Excel
Veja também: como calcular a idade em dias, meses e anos no Excel.
O Excel, assim como as outras planilhas, transforma todas as datas em números. Assim, quando escrevo 25/03/1954, o programa transforma isso em 19808. Esse número é calculado considerando que 01/01/1900 é igual a 1.
Na verdade, as planilhas fazem isso pra facilitar todos os cálculos que fazemos com datas. Vamos imaginar que eu queira calcular a idade que alguém que nasceu em 25/03/1954 tinha em 19/10/2003. Para fazer isso basta subtrair uma célula da outra, mas o que obtenho é um número inteiro, no caso 18105, que pra mim não significa nada. No entanto, esse é o intervalo de dias entra as duas datas.
Dica: para ver o valor númerica de uma data, clique com o botão direito sobre a célula, no menu que se abre escolha “Formatar célula…” e na categoria selecione “Geral”.
Para calcular a diferença em anos, basta dividir por 365. Bom, quase isso, como a cada quatro anos adicionamos um dia ao ano (viva o 29 de fevereiro!) vamos colocar 1/4 de dia por ano, ou 0.25. Assim, vou dividir o número de dias por 365.25. Só que o resultado será uma extrovenga cheia de casas decimais. Quando alguém pergunta a sua idade você responde 25 anos e não 25.476823 anos. Então, para um toque de refinamento, vamos retirar as casas decimais com a função INT(). Tudo que ela faz é tranformar 25.476823 em 25.
A imagem abaixo ilustra bem o exemplo.
calculo de idade no excel
Tudo o que fiz foi subtrair as duas datas (A3-A2), transformar a diferença em anos (dividindo por 365,25) e mostrando apenas a parte inteira, aplicando a funçao INT(). Simples, não?
Caracteres especiais no Word
A maioria dos usuários não sabe, mas se você estiver com a autocorreção habilitada, é possível configurar (e utilizar os já configurados) atalhos para alguns caracteres especiais, como flechas, smiles (aquelas carinhas) e outros quetais editoriais. Abaixo uma imagem (para que todos vejam da mesma forma) com o que digitar e qual o resultado obtido.
ms word - caracteres especiais
Office: copie a formatação de forma rápida
Imagine que você possui um documento, formatou um parágrafo de determinada forma e deseja copiar essa mesma formatação para outros parágrafos. Não é copiar o texto, apenas a formatação (negrito, cor, cor de fundo, fonte, etc…). Como fazer? O office possui uma ferramenta muito útil e pouco utilizada. Nas últimas versões o pessoal da Microsoft até tentou dar destaque a ela, mas ainda não caiu na boca do povo. Trata-se do “Formatar pincel”.
Ela faz exatamente isso, copia apenas a formatação, não o texto.
Para utiliza-la, formate um parágrafo ou pedaço de texto da forma que deseja, nesse caso eu coloquei uma cor de fundo tipo “caneta marcador” no primeiro parágrafo e desejo copiar essa característica para o segundo:
Agora vou clicar no ícone do pincel de formatação (na imagem abaixo está indicado pela seta) para copiar o estilo:
Pronto, agora basta selecionar o novo texto a ser formatado que as características serão copiadas. Simples não?
Se quiser repetir o mesmo padrão por vários pedaços de texto, dê um duplo clique no texto com a formatação a ser copiada e saia selecionando seus pedaços de texto. Para parar basta clicar novamente no “Formatar pincel”.
Teclas de atalho do excel
Chega de tentar adivinhar que tecla o quê, a seguir uma lista de todas as teclas de atalho (de serviço) do Excel.
| Tecla | Descrição |
|---|---|
| CTRL+SHIFT+( | Exibe novamente as linhas ocultas dentro da seleção. |
| CTRL+SHIFT+) | Exibe novamente as colunas ocultas dentro da seleção. |
| CTRL+SHIFT+& | Aplica o contorno às células selecionadas. |
| CTRL+SHIFT_ | Remove o contorno das células selecionadas. |
| CTRL+SHIFT+~ | Aplica o formato de número Geral. |
| CTRL+SHIFT+$ | Aplica o formato Moeda com duas casas decimais (números negativos entre parênteses) |
| CTRL+SHIFT+% | Aplica o formato Porcentagem sem casas decimais. |
| CTRL+SHIFT+^ | Aplica o formato de número Exponencial com duas casas decimais. |
| CTRL+SHIFT+# | Aplica o formato Data com dia, mês e ano. |
| CTRL+SHIFT+@ | Aplica o formato Hora com a hora e os minutos, AM ou PM. |
| CTRL+SHIFT+! | Aplica o formato Número com duas casas decimais, separador de milhar e sinal de menos (-) para valores negativos. |
| CTRL+SHIFT+* | Seleciona a região atual em torno da célula ativa (a área de dados circunscrita por linhas e colunas vazias).Em uma tabela dinâmica, seleciona o relatório inteiro. |
| CTRL+SHIFT+: | Insere a hora atual. |
| CTRL+SHIFT+” | Copia o valor da célula que está acima da célula ativa para a célula ou a barra de fórmulas. |
| CTRL+SHIFT+Mais (+) | Exibe a caixa de diálogo Inserir para inserir células em branco. |
| CTRL+Menos (-) | Exibe a caixa de diálogo Excluir para excluir as células selecionadas. |
| CTRL+; | Insere a data atual. |
| CTRL+` | Alterna entre a exibição dos valores da célula e a exibição de fórmulas na planilha. |
| CTRL+’ | Copia uma fórmula da célula que está acima da célula ativa para a célula ou a barra de fórmulas. |
| CTRL+1 | Exibe a caixa de diálogo Formatar Células. |
| CTRL+2 | Aplica ou remove formatação em negrito. |
| CTRL+3 | Aplica ou remove formatação em itálico. |
| CTRL+4 | Aplica ou remove sublinhado. |
| CTRL+5 | Aplica ou remove tachado. |
| CTRL+6 | Alterna entre ocultar objetos, exibir objetos e exibir espaços reservados para objetos. |
| CTRL+8 | Exibe ou oculta os símbolos de estrutura de tópicos. |
| CTRL+9 | Oculta as linhas selecionadas. |
| CTRL+0 | Oculta as colunas selecionadas. |
| CTRL+A | Seleciona a planilha inteira.Se a planilha contiver dados, CTRL+A seleciona a região atual. Pressionar CTRL+A novamente seleciona a região atual e suas linhas de resumo. Pressionar CTRL+A novamente seleciona a planilha inteira.
Quando o ponto de inserção está à direita de um nome de função em uma fórmula, exibe a caixa de diálogo Argumentos da função. CTRL+SHIFT+A insere os nomes e os parênteses do argumento quando o ponto de inserção está à direita de um nome de função em uma fórmula. |
| CTRL+N | Aplica ou remove formatação em negrito. |
| CTRL+C | Copia as células selecionadas.CTRL+C seguido por outro CTRL+C exibe a Área de Transferência. |
| CTRL+D | Usa o comando Preencher Abaixo para copiar o conteúdo e o formato da célula mais acima de um intervalo selecionado nas células abaixo. |
| CTRL+F | Exibe a caixa de diálogo Localizar e Substituir com a guia Localizar selecionada.SHIFT+F5 também exibe essa guia, enquanto SHIFT+F4 repete a última ação de Localizar.
CTRL+SHIFT+F abre a caixa de diálogo Formatar Células com a guia Fonte selecionada. |
| CTRL+G | Exibe a caixa de diálogo Ir para.F5 também exibe essa caixa de diálogo. |
| CTRL+H | Exibe a caixa de diálogo Localizar e Substituir com a guia Substituir selecionada. |
| CTRL+I | Aplica ou remove formatação em itálico. |
| CTRL+K | Exibe a caixa de diálogo Inserir Hiperlink para novos hiperlinks ou a caixa de diálogo Editar Hiperlink para os hiperlinks existentes que estão selecionados. |
| CTRL+N | Cria uma nova pasta de trabalho em branco |
| CTRL+O | Exibe a caixa de diálogo Abrir para abrir ou localizar um arquivo.CTRL+SHIFT+O seleciona todas as células que contêm comentários. |
| CTRL+P | Exibe a caixa de diálogo Imprimir.CTRL+SHIFT+P abre a caixa de diálogo Formatar Células com a guia Fonte selecionada. |
| CTRL+R | Usa o comando Preencher à Direita para copiar o conteúdo e o formato da célula mais à esquerda de um intervalo selecionado nas células à direita. |
| CTRL+B | Salva o arquivo ativo com seu nome de arquivo, local e formato atual. |
| CTRL+T | Exibe a caixa de diálogo Criar Tabela. |
| CTRL+S | Aplica ou remove sublinhado.CTRL+SHIFT+S alterna entre a expansão e a redução da barra de fórmulas. |
| CTRL+V | Insere o conteúdo da Área de Transferência no ponto de inserção e substitui qualquer seleção. Disponível somente depois de ter recortado ou copiado um objeto, texto ou conteúdo de célula.CTRL+ALT+V exibe a caixa de diálogo Colar Especial, disponível somente depois que você recortar ou copiar um objeto, textos ou conteúdo de célula em uma planilha ou em outro programa. |
| CTRL+W | Fecha a janela da pasta de trabalho selecionada. |
| CTRL+X | Recorta as células selecionadas. |
| CTRL+Y | Repete o último comando ou ação, se possível. |
| CTRL+Z | Usa o comando Desfazer para reverter o último comando ou excluir a última entrada digitada.CTRL+SHIFT+Z usa o comando Desfazer ou Refazer para reverter ou restaurar a correção automática quando Marcas Inteligentes de AutoCorreção são exibidas. |
fonte: documentação microsoft office
Como evitar a divisão por zero (#DIV/0!) no Excel
Se você está fazendo alguma operação com colunas ou linhas no excel e falta um valor em alguma célula, o sistema mostra algumas mensagens de erro que enfeiam qualquer planilha, como #DIV/0! ou #ERR!
Uma forma fácil de se livrar delas é colocar um condicional (comando SE) na fórmula. No exemplo abaixo quero dividir a primeira coluna pela segunda. Como não possuo todos os valores do divisor, fiz um pequeno comando informando à planilha que se o valor não existir ou for zero, o resultado deve ser zero. Claro que mais correto seria colocar um espaço em branco.
Para isso, basta selecionar o célula e digitar a fórmula abaixo:
=SE(C4;B4/C4;0)
Distrinchando:
SE(condição; valor se verdadeiro; valor se falso)
O comando SE verifica se a “condição” é verdadeira, no caso se há um valor na célula C4. Se for verdadeiro, há um valor, realiza a divisão B4/C4; se for falso, devolve zero.
Na imagem abaixo, a primeira célula apontada pela seta possui a fórmula acima e mostra zero. Já a célula apontada pela seta de baixo possui apenas uma divisão comum (=B7/C7) e dá erro.
Excel - divisão por zero na fórmula
Se quiser colocar um espaço em branco, altere a fórmula para =SE(C4;B4/C4;”)
Pode-se trocar a divisão (B4/C4) por qualquer outra operação válida. Alguns exemplos: B4*C4; ln(C4), B4^C4, … e tudo o mais que a sua necessidade mandar.
5 dicas de atalhos para Word
Aqui vão 5 dicas de atalhos de teclado (deixe esse mouse aí do lado!) para o Microsoft Word.
1. Mude o tamanho da font
Para mudar rapidamente o tamanho da fonte da palavra onde está o cursor ou de um texto selecionado:
- ctrl+shift+> para aumentar o tamanho do texto
- ctrl+shift+< para diminuir o tamanho do texto
O quanto o tamanho da fonte irá mudar depende de alguns fatores. Para tamanhos menores que 12, muda-se apenas um ponto (de 11 para 12, por exemplo). Tamanhos entre 12 e 72 cada passo estará na sequência de tamanhos da barra de ferramentas. Geralmente 12,14,16,18,20,22,24,26,28,36,48 e 72). A partir de 72 o aumento se dá de dez em dez pontos.
Como converter qualquer documento para PDF no Windows
Se você utiliza alguma editor de textos que não possui a opção de salvar como PDF, o Microsoft Word não disponibiliza essa ferramenta, aqui está um aplicativo que vai quebrar um belo de um galho: trata-se do doPDF. O programa é gratuito e de fácil instalação. Basta fazer o download e instalar o programa.
O “segredo” desse conversor é que ele se instala como uma impressora virtual. Na prática seu editor de textos, ou programa de imagens, ou qualquer outro aplicativo, “pensa” que está enviando para uma impressora. Quem cuida de transformar para PDF é o doPDF.
Salvando como PDF
1. Após terminar seu documento, selecione imprimir (o exemplo abaixo foi feito com o Microsoft Word). Na caixa de impressão, selecione como impressora o doPDF.
salvando documentos como PDF
2. Para configurar alguns dados do PDF, como tamanho da página, resolução, escala, etc… clique em Propriedades e verá a janela abaixo. Quando estiver satisfeito basta clicar em OK
doPDF - salvando documentos como PDF
3. De volta à janela de impressão, clique em OK e aparecerá a janela para salvar o arquivo. Escolha o local onde deseja gravar o seu PDF.
doPDF - salvando documento como PDF
Pronto, seu arquivo foi convertido para PDF, basta ir ao diretório selecionado e conferir. Gostou da dica?
Como desativar a autocorreção do Word
Se a autocorreção do Word está te levando à loucura, o melhor é desabilitar os itens que não gosta. Para fazer isso basta seguir os passos abaixo:
1. Clique na flecha do menu e em seguida na opção “Mais Comandos…”
Microsof Word menu
2. Abrirá uma nova janela , clique em “Revisão de texto”, nas opções à esquerda, e em seguida no botão “Opções de AutoCorreção…”
3. Isso abrirá a caixa de diálogo da AutoCorreção. Desative as opções que não deseja que o Word realize sozinho.
Dê uma olhada também nas opções de “Substituir texto ao digitar”. Uma das coisas que mais me irrita são as substituições de pontuação por emoticons. Se você ousar terminar uma frase com ); o Word irá substituir por uma “carinha”, deixando seu texto com a aparência de uma conversa entre pré-adolecentes.
Microsoft Word - autocorrecao
4. Há também algumas opções que podem ser irritantes na aba “AutoFormatação ao digitar…”. Clique e altere as que não gosta.
Word Autoformatacao
deixem comentários sobre as opções do Word que mais gostam ou detestam e ajudem um blogueiro a criar pauta 
<- intervenção pré-adolescente,
-
-
Tags
-
administracao
aplicativo
atalhos
avi
backup
calc
captura
chrome
configurações
CPU
debian
desktop
email
excel
extensão
firefox
formatação
gnome
google
grafico
imagem
Linux
messenger
microsoft
mouse
msn
Office
openoffice
outlook
papel de parede
pdf
planilha
segurança
thunderbird
ubuntu
unix
video
vista
Windows
windows 7
wine
word
writer
xp
youtube