Equivalência de Fórmulas Excel PT-EN



Tabela de equivalência de fórmulas do Excel

Para todos aqueles que necessitam de saber rápidamente a correspondência das fórmulas do Excel em português para inglês.

Função em Português

Função em Inglês

ABS ABS
ACOS ACOS
ACOSH ACOSH
AGORA NOW
ALEATÓRIO RAND
ALEATÓRIOENTRE RANDBETWEEN
AMORDEGRC AMORDEGRC
AMORLINC AMORLINC
ANO YEAR
ÁREAS AREAS
ARRED ROUND
ARREDMULTB FLOOR
ARREDONDAR.PARA.BAIXO ROUNDDOWN
ARREDONDAR.PARA.CIMA ROUNDUP
ARRUMAR TRIM
ASC ASC
ASEN ASIN
ASENH ASINH
ATAN ATAN
ATAN2 ATAN2
ATANH ATANH
BAHTTEXT BAHTTEXT
BD DB
BDCONTAR DCOUNT
BDCONTARA DCOUNTA
BDD DDB
BDDESVPA DSTDEVP
BDEST DSTDEV
BDEXTRAIR DGET
BDMÁX DMAX
BDMÉDIA DAVERAGE
BDMÍN DMIN
BDMULTIPL DPRODUCT
BDSOMA DSUM
BDV VDB
BDVAREST DVAR
BDVARP DVARP
BESSELI BESSELI
BESSELJ BESSELJ
BESSELK BESSELK
BESSELY BESSELY
BETA.ACUM.INV BETAINV
BINADEC BIN2DEC
BINAHEX BIN2HEX
BINAOCT BIN2OCT
CARACT CHAR
CÉL CELL
CÓDIGO CODE
COL COLUMN
COLS COLUMNS
COMBIN COMBIN
COMPLEXO COMPLEX
CONCATENAR CONCATENATE
CONT.SE COUNTIF
CONT.SES COUNTIFS
CONT.VALORES COUNTA
CONTNÚM COUNT
CONTAR.VAZIO COUNTBLANK
CONVERTER CONVERT
CORREL CORREL
CORRESP MATCH
COS COS
COSH COSH
COVAR COVAR
CRESCIMENTO GROWTH
CRIT.BINOM CRITBINOM
CUPDATAANT COUPPCD
CUPDATAPRÓX COUPNCD
CUPDIAS COUPDAYS
CUPDIASINLIQ COUPDAYBS
CUPDIASPRÓX COUPDAYSNC
CUPNÚM COUPNUM
CURT KURT
DATA DATE
DATA.VALOR DATEVALUE
DATAM EDATE
DECABIN DEC2BIN
DECAHEX DEC2HEX
DECAOCT DEC2OCT
DEF.NÚM.DEC FIXED
DEGRAU GESTEP
DELTA DELTA
DESC DISC
DESLOC OFFSET
DESV.MÉDIO AVEDEV
DESVPAD STDEV
DESVPADA STDEVA
DESVPADP STDEVP
DESVPADPA STDEVPA
DESVQ DEVSQ
DIA DAY
DIA.DA.SEMANA WEEKDAY
DIAS360 DAYS360
DIATRABALHO WORKDAY
DIATRABALHOTOTAL NETWORKDAYS
DIREITA RIGHT
DIST.BIN.NEG NEGBINOMDIST
DIST.HIPERGEOM HYPGEOMDIST
DIST.LOGNORMAL LOGNORMDIST
DIST.NORM NORMDIST
DIST.NORMP NORMSDIST
DIST.QUI CHIDIST
DISTBETA BETADIST
DISTEXPON EXPONDIST
DISTF FDIST
DISTGAMA GAMMADIST
DISTORÇÃO SKEW
DISTRBINOM BINOMDIST
DISTT TDIST
DPD SLN
DURAÇÃO DURATION
E AND
É.NÃO.DISP ISNA
É.NÃO.TEXTO ISNONTEXT
ÉCÉL.VAZIA ISBLANK
ÉERRO ISERR
ÉERROS ISERROR
EFETIVA EFFECT
ÉIMPAR ISODD
ÉLÓGICO ISLOGICAL
ENDEREÇO ADDRESS
ÉNÚM ISNUMBER
EPADYX STEYX
ÉPAR ISEVEN
ÉPGTO ISPMT
ÉREF ISREF
ESCOLHER CHOOSE
ESQUERDA LEFT
ÉTEXTO ISTEXT
EXATO EXACT
EXP EXP
EXT.TEXTO MID
FALSO FALSE
FATDUPLO FACTDOUBLE
FATORIAL FACT
FIMMÊS EOMONTH
FISHER FISHER
FISHERINV FISHERINV
FONÉTICA PHONETIC
FRAÇÃOANO YEARFRAC
FREQUÊNCIA FREQUENCY
FUNERRO ERF
FUNERROCOMPL ERFC
GRAUS DEGREES
HEXABIN HEX2BIN
HEXADEC HEX2DEC
HEXAOCT HEX2OCT
HIPERLINK HYPERLINK
HOJE TODAY
HORA HOUR
IMABS IMABS
IMAGINÁRIO IMAGINARY
IMARG IMARGUMENT
IMCONJ IMCONJUGATE
IMCOS IMCOS
IMDIV IMDIV
IMEXP IMEXP
IMLN IMLN
IMLOG10 IMLOG10
IMLOG2 IMLOG2
ÍMPAR ODD
IMPOT IMPOWER
IMPROD IMPRODUCT
IMRAIZ IMSQRT
IMREAL IMREAL
IMSENO IMSIN
IMSOMA IMSUM
IMSUBTR IMSUB
INCLINAÇÃO SLOPE
ÍNDICE INDEX
INDIRETO INDIRECT
INFODADOSTABELADINÂMICA GETPIVOTDATA
INFORMAÇÃO INFO
INT INT
INT.CONFIANÇA CONFIDENCE
INTERCEPÇÃO INTERCEPT
INV.NORM NORMINV
INV.NORMP NORMSINV
INV.QUI CHIINV
INVF FINV
INVGAMA GAMMAINV
INVLOG LOGINV
INVT TINV
IPGTO IPMT
JIS JIS
JUROSACUM ACCRINT
JUROSACUMV ACCRINTM
LIN ROW
LINS ROWS
LN LN
LNGAMA GAMMALN
LOCALIZAR FIND
LOG LOG
LOG10 LOG10
LUCRO YIELD
LUCRODESC YIELDDISC
LUCROPRIMINC ODDFYIELD
LUCROÚLTINC ODDLYIELD
LUCROVENC YIELDMAT
MAIOR LARGE
MAIÚSCULA UPPER
MARRED MROUND
MATRIZ.DETERM MDETERM
MATRIZ.INVERSO MINVERSE
MATRIZ.MULT MMULT
MÁXIMO MAX
MÁXIMOA MAXA
MDC GCD
MDURAÇÃO MDURATION
MED MEDIAN
MÉDIA AVERAGE
MÉDIA.GEOMÉTRICA GEOMEAN
MÉDIA.HARMÓNICA HARMEAN
MÉDIA.INTERNA TRIMMEAN
MÉDIAA AVERAGEA
MÉDIASE AVERAGEIF
MÉDIASES AVERAGEIFS
MENOR SMALL
MÊS MONTH
MÍNIMO MIN
MÍNIMOA MINA
MINÚSCULA LOWER
MINUTO MINUTE
MMC LCM
MOD MOD
MODO MODE
MOEDA DOLLAR
MOEDADEC DOLLARDE
MOEDAFRA DOLLARFR
MTIR MIRR
MUDAR REPLACE
MULT PRODUCT
MULTINOMIAL MULTINOMIAL
N N
NÃO NOT
NÃO.DISP NA
NOMINAL NOMINAL
NPER NPER
NÚM.CARACT LEN
NÚMSEMANA WEEKNUM
OCTABIN OCT2BIN
OCTADEC OCT2DEC
OCTAHEX OCT2HEX
ORDEM RANK
ORDEM.PORCENTUAL PERCENTRANK
OTN TBILLEQ
OTNLUCRO TBILLYIELD
OTNVALOR TBILLPRICE
OU OR
PADRONIZAR STANDARDIZE
PAR EVEN
PEARSON PEARSON
PERCENTIL PERCENTILE
PERMUT PERMUT
PGTO PMT
PGTOCAPACUM CUMPRINC
PGTOJURACUM CUMIPMT
PI PI
POISSON POISSON
POTÊNCIA POWER
PPGTO PPMT
PREÇO PRICE
PREÇODESC PRICEDISC
PREÇOPRIMINC ODDFPRICE
PREÇOÚLTINC ODDLPRICE
PREÇOVENC PRICEMAT
PREVISÃO FORECAST
PRI.MAIÚSCULA PROPER
PROB PROB
PROC LOOKUP
PROCH HLOOKUP
PROCURAR SEARCH
PROCV VLOOKUP
PROJ.LIN LINEST
PROJ.LOG LOGEST
QUARTIL QUARTILE
QUOCIENTE QUOTIENT
RADIANOS RADIANS
RAIZ SQRT
RAIZPI SQRTPI
RECEBER RECEIVED
REPT REPT
ROMANO ROMAN
RQUAD RSQ
RTD RTD
SDA SYD
SE IF
SEERRO IFERROR
SEGUNDO SECOND
SEN SIN
SENH SINH
SINAL SIGN
SOMA SUM
SOMAQUAD SUMSQ
SOMARPRODUTO SUMPRODUCT
SOMASE SUMIF
SOMASEQUÊNCIA SERIESSUM
SOMASES SUMIFS
SOMAX2DY2 SUMX2MY2
SOMAX2SY2 SUMX2PY2
SOMAXMY2 SUMXMY2
SUBSTITUIR REPLACE
SUBTOTAL SUBTOTAL
T T
TAN TAN
TANH TANH
TAXA RATE
TAXAJUROS INTRATE
TEMPO TIME
TENDÊNCIA TREND
TESTE.QUI CHITEST
TESTEF FTEST
TESTET TTEST
TESTEZ ZTEST
TETO CEILING
TEXTO TEXT
TIPO TYPE
TIPO.ERRO ERROR.TYPE
TIR IRR
TIRAR CLEAN
TRANSPOR TRANSPOSE
TRUNCAR TRUNC
VALOR VALUE
VALOR.TEMPO TIMEVALUE
VAR VAR
VARA VARA
VARP VARP
VARPA VARPA
VERDADEIRO TRUE
VF FV
VFPLANO FVSCHEDULE
VP PV
VPL NPV
WEIBULL WEIBULL
XTIR XIRR
XVPL XNPV

Atalhos do Excel



1. Teclas de atalho para o Excel

Este artigo apresenta algumas teclas de atalho de combinação com a tecla Ctrl, assim como os atalhos para aceder ao friso (através da tecla Alt) e teclas de função, comuns do Microsoft Excel.

Setas Navegar para uma célula em qualquer direção
PgDn/PgUp Mover a tela para baixo/cima
Alt+PgDn/PgUp Navegar para a próxima folha/folha anterior
Ctrl+Shift+& Aplica o limite de contorno às células selecionadas
Ctrl+Shift+_ Remove o limite de contorno às células selecionadas
Ctrl+M Aplica o formato de número Geral
Ctrl+Shift+$ Aplica o formato Monetário com duas casas decimais (números negativos entre parênteses)
Ctrl+Shift+% Aplica o formato Percentagem sem casas decimais
Ctrl+Shift+# Aplica o formato Data, com dia, mês e ano
Ctrl+* Seleciona a área atual à volta da célula ativa (a área de dados rodeada por linhas e colunas em branco)
Ctrl+Shift+: Introduz a hora atual
Ctrl+Shift+; Introduz a data atual
Ctrl++ Abre a caixa de diálogo Inserir para inserir células em branco
Ctrl+ Abre a caixa de diálogo Eliminar para eliminar células em branco
Ctrl+C Copia as células selecionadas
Ctrl+N Aplica ou remove a formatação de negrito
Ctrl+I Aplica ou remove a formatação de itálico
Ctrl+S Aplica ou remove o sublinhado
Ctrl+O Aplica ou remove a formatação de itálico
Ctrl+P Vista de pré-visualização do modo de impressão
Ctrl+D Executa o comando Preencher Para Baixo para copiar o conteúdo e o formato da célula mais acima num intervalo selecionado para as células abaixo dela
Ctrl+G Guarda o ficheiro ativo com o nome de ficheiro, o caminho e o formato de ficheiro atuais
Ctrl+V Insere os conteúdos da Área de Transferência no ponto de inserção, substituindo qualquer seleção. Disponível apenas depois de ter cortado ou copiado um objeto, texto ou conteúdo de célula.
Ctrl+Alt+V apresenta a caixa de diálogo Colar Especial. Disponível apenas depois de ter cortado ou copiado um objeto, texto ou o conteúdo de uma célula numa folha de cálculo ou noutro programa.
Ctrl+W Fecha a janela do livro selecionado
Ctrl+X Corta as células selecionadas
Ctrl+R Repete o último comando ou ação, se possível
Ctrl+Z Utiliza o comando Anular para anular o último comando ou para eliminar a última introdução de dados

2. Acesso de teclado ao friso

Se não estiver familiarizado com o friso, as informações nesta secção podem ajudá-lo a compreender o modelo de atalhos de teclado do friso. O friso vem com novos atalhos, denominados Informações de Teclas de Atalho. Para visualizar as Informações de Teclas de Atalho, prima a tecla Alt :

Como visualizar as teclas de atalho do friso do Excel

Para mostrar um separador no friso, prima a tecla do separador — por exemplo, prima a letra W para o separador Inserir ou U para o separador Fórmulas. Isto faz com que apareçam todos os distintivos de Informações de Teclas de Atalho desse separador. Em seguida, prima a tecla relativa ao botão pretendido.

Alt Após utilizar a tecla Alt podemos continuar a introduzir nas teclas conforme as indicações que vão aparecendo no friso. Por exemplo:
Alt+J+E Muda a folha de cálculo para a vista Esquema de Página
Alt+J+L Muda a folha de cálculo para a vista Normal
Alt+J+V Muda a folha de cálculo para a vista Pré-visualização de Quebras de Página

3. Teclas de função

F1 Apresenta painel de tarefas de Ajuda do Excel:
Ctrl+F1 Mostra ou oculta o friso
Alt+F1 Cria um gráfico incorporado com base nos dados no intervalo atual
Alt+Shift+F1 Insere uma nova folha de cálculo
F2 Edita a célula ativa e posiciona o ponto de inserção no fim dos conteúdos da célula. Também move o ponto de inserção para a Barra de Fórmulas quando a edição em células estiver desativada
Shift+F2 Adiciona ou edita um comentário de uma célula
Ctrl+F2 Mostra a área de pré-visualização no separador Imprimir na Vista Backstage
F3 Mostra a caixa de diálogo Colar Nome. Só está disponível se os nomes tiverem sido definidos no livro (separador Fórmulas, grupo Nomes Definidos, Definir Nome)
Shift+F3 Abre a caixa de diálogo Inserir Função
F4 Repete o último comando ou ação, se possível.
Quando é selecionada uma referência de célula ou um intervalo de células numa fórmula, a tecla F4 percorre as várias combinações de referências absolutas e relativas
Ctrl+F4 Fecha a janela do livro selecionado
Alt+F4 Fecha o excel
F5 Abre a caixa de diálogo Ir Para. Só está disponível se os nomes tiverem sido definidos no livro (separador Fórmulas, grupo Nomes Definidos, Definir Nome)
Ctrl+F5 Repõe as dimensões da janela do livro selecionado

F6 Alterna entre a folha de cálculo, o friso, o painel de tarefas e os controlos de Zoom. Numa folha de cálculo que tenha sido dividida (menu Ver, Gerir Esta Janela, Fixar Painéis, comando Dividir Janela), F6 inclui os painéis divididos quando alternar entre painéis e a área do friso.
Shift+F6 Alterna entre a folha de cálculo, os controlos de Zoom, o painel de tarefas e o friso
Ctrl+F6 Muda para a janela do livro seguinte quando estiver aberto mais de um livro
F7 Abre a caixa de diálogo Ortografia para verificar a ortografia na folha de cálculo ou no intervalo de células ativo
Ctrl+F7 Executa o comando Mover na janela do livro quando esta não estiver maximizada. Utilize as teclas de seta para mover a janela e, quando concluir, prima a tecla ENTER ou a tecla ESC para cancelar
F8 Ativa ou desativa o modo de extensão. No modo de extensão, aparece Seleção Expandida na linha de estado e as teclas de seta alargam a seleção
Shift+F8 Permite adicionar uma célula ou um intervalo de células não adjacentes a uma seleção de células com as teclas de seta
Ctrl+F8 Executa o comando Tamanho (no menu Controlo da janela do livro) quando o livro não estiver maximizado
F9 Calcula todas as folhas de cálculo de todos os livros abertos
Shift+F9 Calcula a folha de cálculo ativa
Ctrl+Alt+F9 Calcula todas as folhas de cálculo de todos os livros abertos, tenham ou não sido alterados desde o último cálculo
Ctrl+Alt+Shift+F9 volta a verificar fórmulas dependentes e, em seguida, calcula todas as células de todos os livros abertos, incluindo as células não marcadas como tendo de ser calculadas
Ctrl+F9 Minimiza a janela do livro para um ícone
F10 Ativa ou desativa as Informações de Teclas de Atalho. (Premir Alt faz a mesma coisa.)
Shift+F10 Mostra o menu de atalho do item selecionado
Alt+Shift+F10 mostra o menu ou a mensagem para um botão Verificação de Erros
Ctrl+F10 Maximiza ou repõe a janela do livro selecionado
F11 Cria um gráfico a partir dos dados no intervalo atual numa folha de Gráfico em separado
Shift+F11 Insere uma nova folha de cálculo
Alt+F11 abre o Microsoft Visual Basic For Applications Editor, no qual pode criar macros com a linguagem de programação Visual Basic for Applications (VBA)
F12 Abre a caixa de diálogo Guardar Como

Tabelas Dinâmicas

Análise de dados em Excel através de tabelas dinâmicas

Poder analisar um determinado conjunto de dados de uma folha de cálculo poderá ajudá-lo a tomar melhores decisões empresariais. Contudo, por vezes é difícil saber por onde começar, especialmente quando tem muitos dados. O Excel pode ajudá-lo, recomendando e criando automaticamente Tabelas Dinâmicas, que constituem uma excelente forma de resumir, analisar, explorar e apresentar os seus dados.

    1. Antes de começar, certifique-se de que os seus dados possuem títulos de coluna ou cabeçalhos de tabela e de que não existem linhas em branco.

 

  • Clique em qualquer célula do intervalo de células ou tabela.
  •  

  • Clique em Inserir > Tabelas Dinâmicas.rib2
  •  

  • Na caixa de diálogo para criar Tabelas Dinâmicas, poderá optar por criar uma nova folha para a tabela, ou adicionar a tabela dinâmica a uma folha existente.
  •  

  • Clique em OK. Ao criar a nova tabela, o Excel mostra a Lista de Campos.
  •  

  • A partir deste momento  podemos começar a construir a nossa tabela de acordo com as nossas necessidades.
  •  

  • No exemplo apresentado abaixo, escolhemos mostrar as vendas por zona de um conjunto de produtos, ordenando o valor total das vendas por ordem decrescente.
    Nota: Para ordenar os dados por valores, devemos primeiro clicar na seta Seta suspensa de Filtro  existente nos títulos de linha ou coluna da tabela, e depois selecionar a opção “Mais Opções de Classificação”.
  •  

  • Podemos ainda introduzir gráficos dinâmicos (que se irão atualizar automáticamente caso sejam efetuadas alterações na base de dados) através da ferramenta PivotChart
  •  

  • NOTAS FINAIS:
      • Também pode criar uma Tabela Dinâmica a partir de dados externos, como uma origem de dados OLAP (Online Analytical Processing) ou basear uma Tabela Dinâmica num Modelo de Dados para que possa analisar dados de várias tabelas.

     

  • Em vez do Assistente de Tabela Dinâmica e de PivotChart que podia utilizar em versões anteriores do Excel, irá utilizar os comandos de Tabela Dinâmica ou de Tabelas Dinâmicas Recomendadas no friso para criar Tabelas Dinâmicas. Contudo, se sente a falta do assistente, o mesmo ainda está disponível. Pode adicioná-lo à Barra de Ferramentas de Acesso Rápido, ou premir ‘ALT + D + P’ para o iniciar.
  • Artigo adaptado de: artigo original

    As 3 principais razões para o VLOOKUP não estar a funcionar

    1.  Tem números formatados como texto

    2.  Tem espaços em branco no final dos valores

    3.  Esqueceu-se se bloquear a tabela onde quer procurar

    Frequentemente pedem-nos ajuda para tentar ajudar a perceber porque é que algumas fórmulas VLOOKUP não funcionam. O VLOOKUP é a função de procura mais popular do conjunto de funções de procura que o Excel dispõe. O que é interessante é que, na quase totalidade das vezes em que nos pedem ajuda, as soluções se resumem a um pequeno conjunto de situações.

    Procuta então entender porque é que a função VLOOKUP retorna o erro “#N/A” (ou “#N/D” na versão portuguesa)? Antes de perguntar ao génio do Excel da sua empresa, leia o texto abaixo sobre as três principais razões para o VLOOKUP não estar a funcionar como pretendido.

    O VLOOKUP é uma ferramenta essencial para qualquer pessoa que use regularmente folhas de cálculo. Por favor, lembre-se que estas três razões se aplicam também ao INDEX MATCH, mas iremos utilizar o VLOOKUP nos exemplo porque a sua utilização é mais comum.

    Clique aqui para uma explicação detalhada do funcionamento do INDEX MATCH

     

    1.  Tem números formatados como texto

    Se nenhum dos seus VLOOKUPs estão a funcionar, então clique no valor que procura na sua tabela de referência. Na barra da fórmula poderá encontrar um apóstrofo antes do número que procurava. Significa isto que os valores da tabela estão definidos como texto. Para que o VLOOKUP funcione corretamente, o valor procurado e a coluna da tabela onde o valor será procurado deverão conter o mesmo formato.

    Solução: Multiplicar todos os valores da primeira coluna da tabela de procura por 1. (Em alternativa pode somar zero)

    Isto geralmente acontece quando alguém está a tentar mostrar um zero à esquerda de um número (típico em códigos ou referências).  (Uma solução melhor para lidar com este problema é utilizar a formatação customizada)  Isto pode também acontecer devido aos padrões de formatação dos programas de onde são exportadas as folhas de cálculo.

    Multiplicar valores por 1 é a forma mais simples de converter um valor em número. Note que os valores estão agora alinhados à direita, indicado que é realmente um número.

    É possível utilizar um VLOOKUP com o TEXTO dos valores a procurar, mas este método não resolve realmente o problema. Adicionalmente, se o seu erro vem de um conjunto de dados que tem células numéricas e não numéricas, todos os seus valores de procura numéricos retornarão um erro. Usar a técnica de multiplicação por 1 é a melhor técnica de resolver este problema.

    2.  Tem espaços em branco no final dos valores

    Neste exemplo, temos uma fórmula VLOOKUP que, do ponto de vista visual, parece ter tudo para funcionar corretamente. Mas porque é que nenhum dos VLOOKUPs está a retornar resultados? A resposta é invisível a olho nu. Para realmente ver o problema, precisa de editar a célula da tabela de procura e clicar com o cursor no final do texto. Sim, existe um espaço a mais no final do texto.

    É comum este erro ocorrer em conjuntos grandes de dados. Acontece quando se importa dados de diferentes fontes, ou através de programas de ignoram a existência de espaços no início ou no final do texto. Seja qual for o caso, a solução para este problema é simples.

    Solução: Usar a função do botão Text to Columns (Texto para Colunas), no separador Data (Dados) na sua tabela de procura. Clique na checkbox para delimitar as novas colunas com “espaços” e para tratar os delimitadores consecutivos como um só, no caso de haver mais do que um espaço.  Assumindo que os seus dados contêm apenas uma palavra em cada célula, não correrá o risco de apagar quaisquer dados.

    3.  Esqueceu-se se bloquear a tabela onde quer procurar

    As pessoas raramente utilizam a função VLOOKUP para procurar apenas um valor. Se está a adicionar linhas à sua tabela de procura ou a procurar numa tabela fixa, irá precisar de arrastar para baixo a sua fórmula para a replicar. Clique, portanto, no canto inferior direito da célula para popular as células abaixo e verá que apenas metade das fórmulas funcionam.

    Solução: Usar a tecla F4 para bloquear a referência à tabela de procura e evitar que ela acompanhe a descida da célula do valor a procurar.

    Se reparar no exemplo da imagem abaixo, os valores que está a procurar já não se encontram na tabela de procura dado que a tabela desceu. Isto acontece porque arrastou a fórmula sem que antes fixasse a tabela de procura, clicando em F4 sobre B4:D15, no primeiro VLOOKUP. Enquanto a solução para este problema é relativamente simples, ainda nos espanta a quantidade de vezes que nos deparamos com este problema. Mesmo que não tenha intenção de arrastar as suas fórmulas para baixo, é uma boa prática bloquear a tabela de procura quando se escreve uma fórmula com  VLOOKUP.

    INDEX MATCH

    Como usar o INDEX MATCH

    Qualquer utilizador regular do Excel já usou o VLOOKUP e sabe a fórmula de cor.  (Valor a procurar, Tabela, Coluna, etc.)  Mas muitos destes utilizadores nunca ouviram falar da função INDEX MATCH.  INDEX MATCH é uma das diversas fórmulas de procura disponíveis no Excel. Tem algumas características que o tornam melhor que o VLOOKUP em diversas situações. Independentemente da opinião de qual das duas fórmulas é melhor, vale a pena aprender ambas e tê-las ao seu dispor.

    Por favor, note-se que o INDEX MATCH está desenhado para procuras verticais, a tarefa que o VLOOKUP também desempenha.

     

    A função VLOOKUP

    O nosso primeiro ponto de referência: a famosa fórmula VLOOKUP. O VLOOKUP retorna um VALOR baseado numa determinada matriz e coluna de referência.  A sua sintaxe é a seguinte:

    =VLOOKUP ( valor a procurar , tabela de procura , coluna , tipo de procura )

    Abaixo encontra um exemplo de utilização do VLOOKUP, que retorna o valor “Shirts” quando se procura o valor “1089.”

    O nosso objetivo era encontrar o nome do produto com o ID “1089” e o VLOOKUP fê-lo sem qualquer problema. Por isso, como é que o INDEX MATCH replica essa funcionalidade?

     

    A função INDEX

    A função INDEX retorna uma VALOR baseado num vetor definido / coluna e um número de linha.  A sintaxe da fórmula é  a seguinte:

    =INDEX ( vector , número da linha )

    Abaixo encontra um exemplo do uso da função INDEX que devolve o valor “Shirts,” assumindo que já se conhece que o valor está na terceira posição do dado vetor.

    (também tem a possibilidade de definir a coluna a procurar, mas isso não é relevante num INDEX simples)

    Se descer 3 células no vector INDEX, irá obter o valor “Shirts.”  Mas o problema é que normalmente queremos procurar um valor mas não sabemos qual é a linha em que ele se encontra, neste caso a posição 3.  É preciso que este “3” venha de outra fórmula.

    A função MATCH

    A função básica do MATCH retorna um NÚMERO baseado na posição relativa de um valor procurado dentro de um vetor definido / coluna.  A sintaxe desta fórmula é a seguinte:

    =MATCH (  lookup value , lookup array , match type )

    Abaixo encontra um exemplo do uso da função MATCH para retornar a posição do elemento “1089” dentro do nosso vetor de referência.

    Dado que o “1089” está três células abaixo do vetor, é devolvido o valor “3”.

    INDEX MATCH

    Quando combinamos ambas as funções, INDEX e MATCH, o número retornado pela função MATCH corresponde ao número da linha para a função INDEX.

    =INDEX ( vetor fórmula MATCH  )

    Segue-se um exemplo do uso do INDEX MATCH para revolver o tipo de produto para o valor que procuramos.

    Como poderá ver, o valor devolvido é o mesmo que através da fórmula VLOOKUP.

    Diferenças entre as fórmulas

    Uma das diferenças principais do INDEX MATCH é que, em vez de selecionar uma tabela inteira, apenas selecionamos a coluna onde iremos procurar e a coluna do valor a devolver. Ainda que não seja uma grande evolução em funções de procura simples, poderá sê-lo se estiver a lidar com tabelas na ordem dos milhares de linhas, em que são necessárias milhares de procuras.  Limitando o perímetro para apenas a coluna de procura e coluna de resposta, reduz-se o tempo de processamento do Excel.  A diferença encontra-se demonstrada como se segue:

    Uma outra diferença é que o INDEX MATCH funciona também como uma procura da direita para a esquerda, enquanto o VLOOKUP apenas procura da esquerda para a direita. Como pode ver abaixo, usamos o INDEX MATCH para procurar um valor que está na coluna da direita do valor a devolver. Isto não é possível com o VLOOKUP. Para tal, teríamos de reodenar as colunas ou copiar a coluna de procura para que esta esteja sempre à esquerda do valor a devolver.

    Invertemos a nossa fórmula procurando o ID do tipo de produtos “Shirts”.  Simplesmente mudamos o valor a procurar e trocamos as colunas de procura e as colunas de resposta.

    Em resumo

    =INDEX ( Coluna da resposta que quero ver devolvida , MATCH ( o valor que procuro , coluna na qual quero procurar , introduzir um zero “0” ))

    VLOOKUP

    Porquê utilizar o VLOOKUP?

    O Excel é uma ferramenta importante na automatização de relatórios. Na LA INNOVATIS, todos os add-ins que desenvolvemos utilizam este programa para elaborar folhas de cálculo com uma ótima apresentação. Embora, no início, possa parecer um pouco complicado, o Excel é uma ferramenta extremamente valiosa, valendo a pena gastar algum tempo a aprender o que este nos pode oferecer.

    Ao longo de anos de trabalho diário com o Excel, aprendemos inúmeros truques que nos permitem poupar imenso tempo. Neste site, poderá encontrar muitos destes truques e dicas ao longo de diversos artigos. Também recomendamos a procura de artigos em blogs da área, a maior parte deles em inglês, onde poderá encontrar mais informações sobre fórmulas que lhe dão a informação que é relevante para si.

    O que é que este artigo lhe vai ensinar?

    Um dos truques mais simples e úteis é a função VLOOKUP (ou PROCV, na versão portuguesa). Não tenha receio. Depois de aprender esta função, será capaz de fazer muito mais com os seus dados.

    Este artigo será uma demonstração do que é a função e de como funciona.

    Portanto, o que é o VLOOKUP?

    Depois de aprender para que serve o VLOOKUP, irá fazer muito mais sentido.

    Em poucas palavras, o VLOOKUP é usado para procurar um valor numa outra localização do seu ficheiro Excel. É útil porque quando se lida com grandes quantidades de dados, porque há sempre coisas a procurar.

    Aqui está uma descrição, cortesia do Excel:

    O VLOOKUP procura um valor na coluna mais à esquerda de uma dada tabela, e depois retorna um valor da linha que você procura.

    Esta fórmula também funciona de forma perfeitamente igual nas folhas de cálculo do Google.

    Quando é que devemos utilizar o VLOOKUP?

    O VLOOKUP pode ser utilizado em diversos conjuntos de dados. Aqui deixamos um pequeno exemplo de um conjunto de dados que iremos utilizar para explicar como funciona o VLOOKUP.

    VLookup database

    Tipicamente, utilizamos esta função em folhas Excel reutilizáveis,  como um template de um relatório. Depois, de cada vez que alguém adiciona um item à tabela, a função irá retornar-lhe a informação de que precisa.

    Neste exemplo, iremos começar de forma muito simples. Depois irremos mostrar-lhe como fazer uso dela em quantidades de dados muito maiores.

    O que procurar?

    Por exemplo, se está a utilizar a tabela acima, poderá usar o VLOOKUP para identificar que a peça de fruta está associada a um determinado código de item..

    Como refere em How To Geek,

    “se usarmos o VLOOKUP numa célula e se na tabela houver uma coluna de identificadores únicos, retornará um dos pedaços de informação associados a esse identificador único”

    Portanto, o VLOOKUP irá devolver um valor a uma célula com a resposta que procura.

    My-data close

    No nosso exemplo, poderia escrever o código de um item, e o VLOOKUP iria retornar-lhe o valor correspondente à descrição do item. Também poderia procurar o respetivo preço, ou a sua disponibilidade (através da quantidade em stock).

    A escolha do pedaço de informação que pretende é consigo.

     

    Vamos dividir a fórmula em partes

    O VLOOKUP precisa que lhe forneça no mínimo 3 parâmetros.

    1)      O QUÊ?
    O valor que quer procurar LOOKUP_VALUE

    2)      ONDE?
    A tabela de dados onde quer  ir buscar a informação que procura. A ‘tabela de procura’ TABLE_ARRAY

    3)      QUE COLUNA EXATAMENTE?
    O índice da coluna que contém a informação que quer ver devolvida. A coluna número… COL-INDEX-NUM

    Existe ainda um quarto parâmetro, opcional, que iremos ver mais adiante.

    4)      TRUE or FALSE             (Iremos explicar já de seguida.)

     

    Vamos fazer um VLOOKUP

    Digamos que temos uma longa lista de códigos de Item e que queremos procurar pedaços de informação para esses códigos.

    Queremos procurar as descrições dos código de Item para os códigos da tabela da direita, na imagem abaixo.

    H11

     

    O próximo passo é selecionar a célula onde queremos que a função VLOOKUP devolva a informação que iremos procurar. Selecionamos, portanto, a célula K11, neste caso. Depois vamos ao separador Formulas e clicamos em Insert Function (Inserir Função).

    formula-tab

     

    Depois selecionamos o VLOOKUP da lista.

     

    3.-select-Vlookup

     

    A caixa de argumentos da função aparece, pedindo os argumentos (ou parâmetros) necessários de forma a completar a função VLOOKUP. Aí encontra os três argumentos  que mencionamos acima.

     

    empty-arguments

     

    Vamos introduzir o primeiro argumento.

    argument-1

     

    Queremos procurar o código do Item da célula H11, portanto, selecionamos essa célula. Clique em Enter.

    choosing-h11

     

    Depois queremos ir para o próximo argumento, a tabela de procura (Table_array). Lembre-se, aqui insere o perímetro onde quer procurar.

    2nd-argument

    Depois, selecione a tabela de procura, não precisando de incluir o cabeçalho. Clique em Enter.

    selecting-lookup-table

     

    Agora queremos selecionar o terceiro argumento QUE COLUNA EXATAMENTE?

    Este argumento ajuda-nos a especificar qual o pedaço de informação que queremos da tabela de procura.

    Neste exemplo, contamos o número de colunas. Esta tabela tem 4 colunas, mas a informação que queremos retornar encontra-se na coluna.

    Isto significa que temos de inserir o valor “2” no parâmetro Col_index_num.

    choosing the column

    enter-column-num

     

    Agora vem o quarto pedaço de informação. TRUE or FALSE.

    TRUE irá procurar o valor mais próximo (mas sempre abaixo).

    FALSE irá procurar uma correspondência exata.

    Neste caso, dado que estamos a procurar o valor do nome da peça de fruta, queremos a correspondência exata. Insira FALSE.

    Carregue em OK.

    enter-false

     

    Pear (Pêra) é a resposta que a função corretamente devolve!

    IMPORTANTE: CONTUDO, esta fórmula precisa de ter os sinais $ adicionados para ser realmente útil…

    yay---pear!

     

    IMPORTANTE: Ao usar o símbolo $ irá ser muito mais útil.

    SOZINHA A FÓRMULA NÃO É MUITO ÚTIL para a tabela de dados inteira. Queremos poder arrastar a fórmula para aplicar às restantes linhas da tabela.

    Se tudo o que precisa é de um pedaço de informação da tabela, como no exemplo acima, seria necessário um grande esforço para construir uma fórmula com o VLOOKUP.

    Se arrastarmos a fórmula para baixo, iria atualizar os números da fórmula à medida que desce na folha. Isto significa que o Excel iria procurar os dados a uma tabela que não a correta.

    É necessário dizermos na fórmula que queremos que procure numa tabela fixa.

    Usamos, portanto, o símbolo $ no segundo argumento da fórmula, a tabela de procura.

    adding$

    Pode editar a fórmula clicando diretamente na célula.

    DICA: Em vez de escrever os símbolos $ manualmente (Shift+4), coloque o cursor na parte exata da fórmula que pretende fixar, clicando na tecla F4. 

    $-close-up

     

    Agora pode arrastar para baixo para obter as restantes descrições.

    drag-down

     

    Mais uma vez!

    Valor usar o VLOOKUP para obter o preço.

    Adicione uma coluna para o preço e assegure-se de que formatou a célula como Currency (Moeda). Pode fazer isso no separador Home (Base). Veja a imagem abaixo.

    set-as-currency

     

    Agora vamos escrever a fórmula, em vez de usar o botão ‘Inserir Fórmula’.

    1)      O QUÊ? LOOKUP_VALUE
    Queremos procurar o código do Item, célula H11.

    2)      ONDE? TABLE_ARRAY
    Está na mesma tabela mas vamos adicionar o símbolo $ para que a tabela não se ajuste ao arrastar a fórmula.

    3)      QUE COLUNA PRETENDEMOS EXATAMENTE? column COL-INDEX-NUM
    É a coluna 3, aquela que contém o preço.

    FALSE  – novamente queremos o valor exato.

     

    =VLOOKUP(H11,$B$7:$D$14,3,FALSE)

     

    DICA: em vez do FALSE/FALSO pode utilizar em alternativa o zero (“0”, sem aspas). O 1 pode ser utilizado na vez do TRUE/VERDADEIRO.

    Carregue no Enter.

    explaining-price-formula

     

    Arraste para baixo, e PRONTO!

    drag-price-down

     

    E é assim que funciona o VLOOKUP.

    Veja quanto dinheiro é que gastamos em fruta hoje!

    final-pic

    Se ainda tem problemas com a função VLOOKUP, veja este artigo com dicas interessantes sobre possíveis razões para a função não estar a funcionar:  Top 3 Reasons Why VLOOKUP Isn’t Working.

     

    Este artigo é uma tradução livre deste artigo da Trackpal: artigo original.

    Se anda a perder muito tempo com relatórios Excel, contacte-nos, e veremos como o poderemos ajudar a melhorar os seus relatórios através dos nossos skills de automação.