Sempre que dou dicas sobre Excel ou mostro minhas planilhas de análise, algumas pessoas me perguntam quais são as fórmulas mais importantes para se analisar dados.
Bom, não sei se posso categorizar as mais importantes, mas para se tornar um bom analista é necessário conhecer algumas fórmulas e, principalmente, compreender o funcionamento delas. Só assim será possível combiná-las da melhor forma para se obter um bom resultado para análise.
Listei algumas fórmulas que costumo utilizar em minhas planilhas e que considero importantes
para trabalhar com análise de dados.
FÓRMULA “SE”
Sempre precisamos tomar decisões. Quando as vendas estão abaixo da meta, ou quando o prazo de um projeto é ultrapassado. Então, como é que vamos tratar essas decisões no Excel?
Usando formulas SE, é claro.
Por exemplo, digamos que sua empresa decide dar 10% de bonus aos vendedores que baterem a meta de vendas e não premiar os que não baterem a meta. Como você poderia expressar isso no Excel?
Simples:
=SE(vendas > meta; "10% de bônus"; "0% de bônus")A sintaxe da formula SE é simples:
=SE(condição para verificar; resultado se VERDADEIRO; resultado se FALSO)FÓRMULA “SOMASES”
| Mês | Região | Vendedor | Produtos Vendidos |
|---|---|---|---|
| JAN | Sudeste | João | 12 |
| JAN | Sul | Paulo | 7 |
| FEV | Sudeste | João | 11 |
| FEV | Norte | Pedro | 5 |
| FEV | Sul | Carlos | 8 |
| MAR | Sul | Paulo | 16 |
| MAR | Norte | Pedro | 9 |
A fórmula SOMASES é usada para somar um intervalo de valores que atendem a várias condições.
Nas analises de dados, quase sempre temos questionamentos como este: "Quantos produtos vendemos na região A, com o vendedor B no mês M? ".
Para responder a esta pergunta sem ter que usar vários filtros de dados, devemos usar a formula SOMASES.
Esta fórmula mágica pode somar um conjunto de valores que atendam a várias condições.
A sintaxe da formula SOMASES é assim:
= SOMASES(dados que você quer somar; coluna condição 1; valor condição 1; coluna condição 2; valor condição 2 ....)Exemplo:
= SOMASES(produtos_vendidos; mês; "FEV"; região; "Norte"; vendedor; "Pedro")FÓRMULA PROCV
Uma das fórmulas mais utilizadas no Excel, PROCV é usada para procurar valores em uma tabela retornando um valor correspondente na mesma linha.
Por exemplo, você pode perguntar qual é o nome do cliente com COD = C00023 ou qual é o preço do produto com o código P0089? PROCV vai te dar essas respostas.
A sintaxe para PROCV é simples.
= PROCV (o que você quer pesquisar, tabela, coluna do resultado, [a tabela está ordenada?])Exemplo:
= PROCV ("C00023", tabela_clientes, 2, false)Neste exemplo o cliente com ID C00023 é procurado na primeira coluna da tabela_clientes e é retornado o valor da segunda coluna, supondo que a tabela não está ordenada.
Observação: o valor é procurado sempre na primeira coluna da tabela. Se o item procurado estiver duplicado na primeira coluna, o primeiro resultado é o que vale.
Fórmulas INDEX + FÓSFORO
Para cada 10 pessoas que utilizam PROCV, há alguém perceber suas limitações mais irritante. Fórmula PROCV só pode pesquisar na coluna mais à esquerda.
Isso significa que, se uma tabela de clientes tem identificação do cliente na coluna esquerda eo nome na coluna direita, ao usar PROCV, você pode pesquisar apenas para identificação do cliente.
Você não pode fazer perguntas como o que é o ID do cliente de "Samuel Jackson"?
PROCV iria sufocar e trazer o seu mundo Excel para uma parada.
Felizmente, o índice + fórmulas FÓSFORO vir a resgatar. Estas duas belas fórmulas nos ajudar a pesquisa em qualquer coluna e retornar o valor correspondente em qualquer outra coluna.
Sintaxe:
= INDEX (lista de valores, MATCH (o que você quer de pesquisa, pesquisa de coluna, é a coluna de pesquisa classificadas?))
Exemplo:
= INDEX (IDs do cliente, MATCH ("Samuel Jackson", os nomes de clientes, 0))
Clique aqui para saber mais sobre ÍNDICE & Match fórmulas.
5. Fórmulas de nidificação
Infelizmente, as empresas não tomar decisões simples. Eles sempre complicar as coisas. Quero dizer, você já leu as regras do imposto de renda?? Sua cabeça começa a girar pelo tempo que você chegar a segundo parágrafo.
Para modelar essas decisões complexas e situações, você precisa fórmulas ninho.
Assentamento refere-se à inclusão de uma fórmula com a outra fórmula.
Um exemplo de situação: Dê 12% caminhada para funcionários que lêem Chandoo.org pelo menos 3 dias por semana, dar 10% caminhada para aqueles que lêem Chandoo.org pelo menos uma vez por semana, para o resto dar 5% de caminhada.
Excel fórmula: = IF (número de vezes empregado lê chandoo.org em uma semana> = 3, "12% caminhada", IF (número de vezes empregado lê chandoo.org em uma semana> 0, "10% de caminhada", " 5% de caminhada "))
Você vê o que fizemos acima? Usamos IF fórmula dentro de outro IF fórmula. Isso não é nada, mas o assentamento.
Você pode aninhar qualquer fórmula dentro de outra fórmula quase qualquer número de vezes.
Fórmulas de nidificação nos ajuda a expressar complexa lógica de negócio e regras com facilidade. Como analista, é preciso aprender a arte de assentamento.
Muita aninhados fórmula exemplos e explicações aqui.
6. Expressões aritméticas básicas
= (((123 456) * (789 987))> ((123-456) / (789-987))) ^ 3 & "vez que eu vi um tigre"
Se você ler a expressão acima e não teve que raspar a cabeça uma vez, então você está no caminho para se tornar um analista incrível.
A maioria das pessoas saltar para fórmulas do Excel sem primeiro aprender vários operadores básicos e expressões. Felizmente, a aprendizagem destes requer muito pouco tempo. A maioria de nós ter ido através de aritmética básica e expressões na escola. Aqui está um resumo, se você fosse pego cochilando em Matemática 101.
Operador O que ele faz Example
+ - * / Operadores aritméticos básicos. Realizar adição, subtração, multiplicação e divisão 2 +3, 7-2, 9 * 12, 108/3, 2 +3 * 4-2
^ Power of opetator. Levanta alguma coisa para o poder de outro valor. 2 ^ 3, 9 ^ 0,5, PI () ^ 2, EXP (1) ^ 0,5
() Para definir a precedência em cálculos. Tudo está incluído no parentesis calcuated primeiro. (2 3) * (4 + 5) calcuates 2 3 em primeiro lugar, em seguida, 4 5 e multiplica ambos os resultados.
& Para combinar dois valores de texto "Você é" & retornos "Awesome" "Você é incrível"
% Para dividir com 100. 2/4 50% dará como resultado. Nota: (2/4)% irá dar origem a 0,5%, como resultado.
: Usado para especificar intervalos A1: B20 refere-se ao intervalo de células de A1 a B20
$ Para bloquear uma coluna de referência ou linha ou ambos $ A $ 1 refere-se a célula A1 o tempo todo. $ A1 refere-se à coluna A, linha relativa com base em onde você usá-lo. Para mais, consultar referências absolutas contra parente em Excel.
[] Usado para se referir estruturalmente às colunas ourSales tabela [mês] refere-se à coluna do mês na tabela ourSales. Funciona somente em Excel 2007 ou superior. Conheça mais sobre tabelas do Excel.
@ Usado para se referir estruturalmente para valores de linha atuais em uma tabela ourSales [@ mês] refere-se ao valor da linha corrente mês na tabela oursales.
{} Para especificar uma matriz linha de valores {1,2,3,4,5} - refere-se a uma lista de valores 1,2,3,4,5
<> <=> = Operadores de comparação. Saída será sempre boolean - ou seja VERDADEIRO ou FALSO. 2> 3 será FALSE. 99 <101 data-blogger-escaped-p="" data-blogger-escaped-ser="" data-blogger-escaped-true.="">
= <> Operadores de igualdade. Verificar se os dois valores são iguais ou não iguais. Saída será VERDADEIRO ou FALSO 2 = 2, "Olá" = "Olá", 4 <> 5 tudo vai retornar TRUE.
*? Utilizado como curinga em determinadas fórmulas como CONT.SE. etc CONT.SE. (A1: A10, "a *") conta os valores na faixa de A1: A10, começando com um. Para saber mais sobre isso, consulte o COUNTIF e SOMASE no Excel
Operador de interseção ESPAÇO. Retorna o intervalo na interseção de duas faixas A1: B2 C4: D5 se refere à interseção ou intervalo A1: C4 e B2: D5 e retorna B2: C4. Atenção: A saída será um array, por isso você deve usá-lo em outra fórmula que leva matrizes, como SUM, COUNT, etc
7. Fórmulas de texto
Embora existam mais de duas dúzias de fórmulas de texto no Excel, incluindo a BHATTEXT misterioso (que é usado para converter números para Bhats tailandeses, aparentemente projetado pela equipe do Excel, para que pudessem pedir Thai tirar food #), você não precisa aprender tudo deles. Ao aprender algumas fórmulas muito úteis texto, você pode economizar uma tonelada de tempo durante a limpeza de dados ou extrair partes de montanhas de texto.
Como analista aspirante, pelo menos, conhecer o seu auto com as fórmulas abaixo:
LEFT, RIGHT e MID - para extrair partes do texto da esquerda, direita e centro.
Trim - para remover espaços desnecessárias de começo, meio e fim de um texto.
SUBSTITUTO - para substituir partes do texto com outra coisa.
LEN - para calcular o comprimento de um texto
TEXT - para converter um valor para formatação de texto
FIND - para descobrir se algo está presente em um texto, em caso afirmativo em que posição
Você pode encontrar vários exemplos de todas essas fórmulas e seus usuários no nosso site. Basta procurar.
8. NETWORKDAYS & Fórmulas jornada de trabalho
"Não há dias suficientes no fim de semana" - Alguém
Quer um fim de semana tem dias suficientes ou não, como analista de trabalho, você deve lidar com os cálculos dias de trabalho. Por exemplo, se um projeto tem 180 dias úteis para ser concluído e começa em 16 janeiro de 2013, como você encontrar a data final?
Felizmente, não temos de inventar uma fórmula para isso. Excel tem algo exatamente para isso. Fórmula WORKDAY tem uma data de início e dias de trabalho e diz-lhe que a data final seria.
Como sábio fórmula NETWORKDAYS nos diz quantos dias úteis existem entre as duas datas indicadas.
NETWORKDAYS fórmula nos diz que o número de dias úteis entre a data de início e fim
Ambas as fórmulas aceitar uma lista de feriados adicionais a considerar também.
NETWORKDAYS: Calcule o número de dias úteis entre duas datas (assumindo sábado, domingo de fim de semana)
NETWORKDAYS.INTL: Mesmo que NETWORKDAYS, mas permite que você use fins de semana personalizados [Excel 2010 + only]
WORKDAY: Calcule a data de término de uma data de início e número de dias úteis
WORKDAY.INTL: O mesmo que jornada de trabalho, mas permite que você use fins de semana personalizados. [Excel 2010 + only]
Mais sobre o trabalho com valores de data e hora no Excel.
9. Pequenas e Grandes Fórmulas
Quase ninguém pergunta sobre "Quem foi a segunda pessoa a escalar o Monte. Everest, ou caminhar na lua ou terminar 100 mtrs correr o mais rápido? ".
E, no entanto, todas as empresas fazem perguntas como "Quem é o nosso segundo cliente mais valioso?, Terceiro fornecedor de fundo sobre a delinquência fatura? 4 café famoso na Jamaica? "
Assim como analistas de nosso trabalho é responder a essas perguntas com a desperdiçar muito tempo. Isto é onde os pequenos, fórmulas GRANDES vir a calhar.
SMALL: Usado para encontrar enésimo menor valor de uma lista. Use-o como = SMALL (intervalo de valores, n).
GRANDE: Usado para encontrar enésimo maior valor de uma lista.
MIN: Dá o valor mínimo de uma lista.
MAX: Dá o valor máximo de uma lista.
RANK: Encontra a posição de um valor em uma lista. Use-o como = RANK (valor, nesta lista, a ordem)
10. IFERROR Formula
Erros, péssimo cantina alimentos e máquinas de café disfuncionais são verdades eternas da vida corporativa. Enquanto você pode sempre saco marrom do seu almoço e traz um frasco de café finamente fabricada para o trabalho, não há como escapar quando o PROCV # N / As. Ou será que existe?
Bem, você sempre pode usar a bela fórmula IFERROR para lidar com erros em suas fórmulas.
Formula IFERROR - Sintaxe e Ajuda
Sintaxe:
IFERROR (fórmula, o que fazer em caso de erro)
Use-o como:
IFERROR (PROCV (....) ", e não o valor encontrado!")
Clique aqui para saber mais sobre Formula IFERROR.
3 Fórmulas Bônus
Se você pode dominar os acima de 10 fórmulas, você estará à frente de 80% de todos os analistas do Excel. Aqui estão três fórmulas mais importantes que podem vir a calhar quando se faz um trabalho sério de análise de dados.
DESVIO fórmula para gerar as dinâmicas a partir de um ponto de partida e usá-los em outro lugar (em gráficos, fórmulas, etc.)
Fórmula SUMPRODUCT: Liberte o poder do processamento de matriz Excel usando SUMPRODUCT.
Fórmula SUBTOTAL: Calcular totais, contagem e médias etc em uma série com filtros.
Que fórmulas que você acha que são importantes para os analistas?
Durante meus dias como analista de negócios, e não um único dia passou sem o uso de Excel. Foi uma ferramenta importante na minha jornada para se tornar um analista incrível. Eu não posso forçar a importância de fórmulas como SUMIFS, PROCV, ÍNDICE, MATCH suficiente. Eles desempenham um papel vital na análise de dados e apresentação de resultados.
E você? Que fórmulas que você acha que são importantes para os analistas? Por favor, compartilhe suas idéias e dicas usando comentários.
Quer se tornar um analista de impressionante? Considere o nosso programa Escola Excel
Se você é um analista de brotamento ou gerente, acrescentando Skills Excel pode ser um investimento muito valioso de seu tempo. Meu programa escolar Excel é projetado para ajudar pessoas como você a aprender várias características básico e avançado de Excel e usá-los para criar kick ass relatórios, trackers e análise. Este programa tem 24 horas de treinamento Excel, 40 exemplo, pastas de trabalho e seis meses de acesso online.
Clique aqui para saber mais sobre a Escola de Excel.
Nenhum comentário:
Postar um comentário