Pesquisas, gráficos, estatísticas e tabelas dinâmicas
Tendo revisado funções básicas, referências de célula e funções de data e hora, agora mergulhamos em alguns dos recursos mais avançados do Microsoft Excel. Apresentamos métodos para resolver problemas clássicos em finanças, relatórios de vendas, custos de envio e estatísticas.
NAVEGAÇÃO ESCOLAR- Por que você precisa de fórmulas e funções?
- Definindo e Criando uma Fórmula
- Referência de célula relativa e absoluta e formatação
- Funções úteis que você deve conhecer
- Pesquisas, gráficos, estatísticas e tabelas dinâmicas
Essas funções são importantes para os negócios, estudantes e aqueles que querem apenas aprender mais.
VLOOKUP e HLOOKUP
Aqui está um exemplo para ilustrar funções de pesquisa vertical (VLOOKUP) e pesquisa horizontal (HLOOKUP). Essas funções são usadas para traduzir um número ou outro valor em algo que seja compreensível. Por exemplo, você pode usar o VLOOKUP para pegar um número de peça e retornar a descrição do item.
Para investigar isso, vamos voltar à nossa planilha “Decision Maker” na Parte 4, onde Jane está tentando decidir o que vestir na escola. Ela não está mais interessada no que ela usa, desde que ela conseguiu um novo namorado, então ela agora vai usar roupas e sapatos aleatórios.
Na planilha de Jane, ela lista roupas em colunas verticais e sapatos, colunas horizontais.
Ela abre a planilha e a função RANDBETWEEN (1,3) gera um número entre ou igual a um e três correspondentes aos três tipos de roupas que ela pode usar.
Ela usa a função RANDBETWEEN (1,5) para escolher entre cinco tipos de sapatos.
Como Jane não pode usar um número, precisamos convertê-lo em um nome, então usamos as funções de pesquisa.
Usamos a função VLOOKUP para traduzir o número da roupa para o nome da roupa. HLOOKUP traduz do número do calçado para os vários tipos de calçado na fila.
A planilha eletrônica funciona assim para roupas:
O Excel escolhe um número aleatório de um a três, já que ela tem três opções de roupa.
Em seguida, a fórmula converte o número em texto usando = VLOOKUP (B11, A2: B4,2), que usa o número aleatório do valor de B11 para procurar no intervalo A2: B4. Em seguida, ele fornece o resultado (C11) dos dados listados na segunda coluna.
Usamos a mesma técnica para escolher sapatos, exceto que desta vez usamos VOOKUP em vez de HLOOKUP.
Exemplo: estatísticas básicas
Quase todo mundo conhece uma fórmula da estatística - média - mas há outra estatística que é importante para os negócios: desvio padrão.
Por exemplo, muitas pessoas que foram para a faculdade agonizaram com sua pontuação no SAT. Eles podem querer saber como eles se classificam em comparação com outros estudantes. As universidades querem saber disso também porque muitas universidades, especialmente as de prestígio, recusam os alunos com baixos resultados no SAT..
Então, como é que nós, ou uma universidade, medimos e interpretamos as pontuações do SAT? Abaixo estão as pontuações do SAT para cinco alunos, variando de 1.870 a 2.230.
Os números importantes para entender são:
Média - A média também é referida como "média".
Desvio Padrão (STD ou σ) - Este número mostra o quão disperso é um conjunto de números. Se o desvio padrão for grande, os números estarão distantes e, se for zero, todos os números serão iguais. Você poderia dizer que o desvio padrão é a diferença média entre o valor médio e o valor observado, ou seja, 1.998 e cada pontuação SAT. Por favor, note que é comum abreviar o desvio padrão usando o símbolo grego sigma “σ”.
Classificação percentual - Quando um aluno recebe uma pontuação alta, ele pode se gabar de que está no percentil 99 superior ou algo assim. "Rank de percentil" significa que a porcentagem de pontuações é menor do que uma pontuação específica.
O desvio padrão e a probabilidade estão intimamente ligados. Você pode dizer que, para cada desvio padrão, a probabilidade ou probabilidade de que esse número esteja dentro desse número de desvios padrão é:
| DST | Porcentagem de pontuações | Faixa de pontuação do SAT |
| 1 | 68% | 1.854-2.142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99,73% | 1,567-2,429 |
| 4 | 99,994% | 1,424-2,572 |
Como você pode ver, a chance de que qualquer pontuação do SAT esteja fora das 3 DSTs é praticamente zero, porque 99,73% das pontuações estão dentro das 3 DSTs..
Agora vamos olhar a planilha novamente e explicar como ela funciona.
Agora nós explicamos as fórmulas:
= MÉDIA (B2: B6)
A média de todas as pontuações no intervalo B2: B6. Especificamente, a soma de todas as pontuações divididas pelo número de pessoas que fizeram o teste.
= STDEV.P (B2: B6)
O desvio padrão no intervalo B2: B6. O ".P" significa STDEV.P é usado sobre todas as pontuações, ou seja, toda a população e não apenas um subconjunto.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Isso calcula a porcentagem cumulativa no intervalo B2: B6 com base na pontuação do SAT, nesse caso, B2. Por exemplo, 83% das pontuações estão abaixo do escore de Walker.
Representando os resultados
Colocar os resultados em um gráfico facilita a compreensão dos resultados, além de você poder mostrá-lo em uma apresentação para tornar seu ponto mais claro.
Os alunos estão no eixo horizontal e suas pontuações do SAT são mostradas como um gráfico de barras azul em uma escala (eixo vertical) de 1.600 a 2.300.
O ranking percentual é o eixo vertical à direita de 0 a 90% e é representado pela linha cinza.
Como criar um gráfico
Criar um gráfico é um tópico em si, no entanto, vamos explicar brevemente como o gráfico acima foi criado.
Primeiro, selecione o intervalo de células a ser no gráfico. Neste caso, A2 a C6 porque queremos os números, assim como os nomes dos alunos.
No menu “Inserir”, selecione “Gráficos” -> “Gráficos Recomendados”:
O computador recomenda um gráfico "Eixo secundário, coluna em cluster". A parte “Eixo Secundário” significa que desenha dois eixos verticais. Nesse caso, esse gráfico é o que queremos. Nós não temos que fazer mais nada.
Você pode usar o gráfico e redimensioná-lo até tê-lo como o tamanho e a posição desejada. Quando estiver satisfeito, você pode salvar o gráfico na planilha.
Se você clicar com o botão direito do mouse no gráfico e selecionar "Selecionar dados", ele mostrará quais dados estão selecionados para o intervalo.
O recurso “Recommended Charts” normalmente evita que você tenha que lidar com detalhes tão complicados como determinar quais dados incluir, como atribuir rótulos e como atribuir os eixos verticais esquerdo e direito..
Na caixa de diálogo "Select Data Source", clique em "score" em "Legend Entries (Series)" e pressione "Edit" e mude para "Score".
Em seguida, altere a série 2 ("percentil") para "Percentil".
Volte ao seu gráfico e clique no "Título do Gráfico" e mude para "SAT Scores". Agora, temos um gráfico completo. Possui dois eixos horizontais: um para o escore SAT (azul) e um para o percentual cumulativo (laranja).
Exemplo: o problema de transporte
O problema do transporte é um exemplo clássico de um tipo de matemática chamado “programação linear”. Isso permite maximizar ou minimizar um valor sujeito a certas restrições. Tem muitas aplicações para uma ampla gama de problemas de negócios, por isso é útil aprender como funciona.
Antes de começarmos com este exemplo, temos que ativar o "Excel Solver".
Ativar o suplemento do Solver
Selecione "Arquivo" -> "Opções" -> "Add-ins". Na parte inferior das opções de suplementos, clique no botão "Ir" ao lado de "Gerenciar: suplementos do Excel".
No menu resultante, clique na caixa de seleção para ativar "Solver Add-in" e clique em "OK".
Exemplo: calcular os custos mais baixos de envio do iPad
Suponha que estamos enviando iPads e estamos tentando preencher nossos centros de distribuição usando os menores custos de transporte possíveis. Temos um acordo com uma companhia aérea de transporte de carga para enviar iPads de Xangai, Pequim e Hong Kong para os centros de distribuição mostrados abaixo.
O preço para enviar cada iPad é a distância da fábrica até o centro de distribuição e a usina dividida por 20.000 quilômetros. Por exemplo, são 8.024 km de Xangai a Melbourne, que é de 8.024 / 20.000 ou US $ 0,40 por iPad..
A questão é como enviamos todos esses iPads dessas três fábricas para esses quatro destinos com o menor custo possível?
Como você pode imaginar, descobrir isso pode ser muito difícil sem alguma fórmula e ferramenta. Neste caso, temos que enviar 462.000 (F12) iPads totais. As plantas têm capacidade limitada de 500.250 unidades (G12).
Na planilha, para que você possa ver como funciona, digitamos 1 na célula B10, o que significa que queremos enviar um iPad de Xangai para Melbourne. Como os custos de transporte ao longo dessa rota são de US $ 0,40 por iPad, o custo total (B17) é de US $ 0,40.
O número foi calculado usando a função = SUMPRODUCT (custos, enviados) “custos” são os intervalos B3: E5.
E "enviado" é o intervalo B9: E11:
O SUMPRODUCT multiplica “custos” vezes o intervalo “expedido” (B14). Isso é chamado de "multiplicação de matrizes".
Para que o SUMPRODUCT funcione corretamente, as duas matrizes - custos e envio - devem ter o mesmo tamanho. Você pode contornar essa limitação fazendo custos extras e enviando colunas e linhas com valor zero para que as matrizes sejam do mesmo tamanho e não haja impacto nos custos totais.
Usando o Solver
Se tudo o que tivéssemos que fazer era multiplicar as matrizes “custos” vezes “expedidas”, o que não seria muito complicado, mas temos que lidar com as restrições também.
Temos que enviar o que cada centro de distribuição exige. Colocamos essa constante no solucionador da seguinte forma: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Isso significa que a soma do que é enviado, ou seja, os totais nas células $ B $ 12: $ E $ 12, deve ser maior ou igual ao que cada centro de distribuição exige ($ B $ 13: $ E $ 13).
Não podemos enviar mais do que produzimos. Nós escrevemos que restrições como esta: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Agora vá para o menu “Data” e pressione o botão “Solver”. Se o botão "Solver" não estiver lá, você precisará habilitar o suplemento do Solver.
Digite as duas restrições detalhadas anteriormente e selecione o intervalo "Expedições", que é o intervalo de números que queremos que o Excel calcule. Escolha também o algoritmo padrão “Simplex LP” e indique que queremos “minimizar” a célula B15 (“custos totais de envio”), onde diz “Definir objetivo”.
Pressione “Solve” e o Excel salva os resultados na planilha, que é o que queremos. Você também pode salvar isso para poder brincar com outros cenários.
Se o computador diz que não consegue encontrar uma solução, então você fez algo que não é lógico, por exemplo, você pode ter solicitado mais iPads do que as plantas podem produzir.
Aqui o Excel está dizendo que encontrou uma solução. Pressione "OK" para manter a solução e retornar à planilha.
Exemplo: Valor Presente Líquido
Como uma empresa decide investir em um novo projeto? Se o “valor presente líquido” (VPL) for positivo, eles investirão nele. Esta é uma abordagem padrão adotada pela maioria dos analistas financeiros.
Por exemplo, suponha que a mineradora Codelco queira expandir a mina de cobre Andinas. A abordagem padrão para determinar se devemos avançar com um projeto é calcular o valor presente líquido. Se o VPL for maior que zero, então o projeto será lucrativo, dado dois tempos de entrada (1) e (2) custo de capital.
Em linguagem simples, o custo de capital significa quanto esse dinheiro ganharia se simplesmente o deixassem no banco. Você usa o custo de capital para descontar valores em dinheiro a valor presente, em outras palavras, $ 100 em cinco anos pode ser $ 80 hoje.
No primeiro ano, US $ 45 milhões são reservados como capital para financiar o projeto. Os contadores determinam que seu custo de capital é de 6%.
Quando começam a minerar, o dinheiro começa a entrar quando a empresa encontra e vende o cobre que eles produzem. Obviamente, quanto mais eles minam, mais dinheiro ganham, e suas previsões mostram que o fluxo de caixa aumenta até atingir US $ 9 milhões por ano..
Após 13 anos, o NPV é de US $ 3.945.074, portanto, o projeto será lucrativo. Segundo analistas financeiros, o “período de retorno” é de 13 anos.
Criando uma tabela dinâmica
Uma “tabela dinâmica” é basicamente um relatório. Nós os chamamos de tabelas dinâmicas porque é possível alternar facilmente um tipo de relatório para outro sem ter que criar um novo relatório inteiro. Então eles pivô no lugar. Vamos mostrar um exemplo básico que ensina os conceitos básicos.
Exemplo: relatórios de vendas
O pessoal de vendas é muito competitivo (isso faz parte de ser um vendedor), então eles naturalmente querem saber como eles se saem um contra o outro no final do trimestre e no final do ano, mais o quanto suas comissões serão.
Suponhamos que temos três vendedores - Carlos, Fred e Julie - todos vendendo petróleo. Suas vendas em dólares por trimestre fiscal para o ano de 2014 são mostradas na planilha abaixo.
Para gerar esses relatórios, criamos uma tabela dinâmica:
Selecione “Inserir -> Tabela Dinâmica, está no lado esquerdo da barra de ferramentas:
Selecione todas as linhas e colunas (incluindo o nome do vendedor) como mostrado abaixo:
A caixa de diálogo da tabela dinâmica aparece no lado direito da planilha.
Se clicarmos nos quatro campos da caixa de diálogo da tabela dinâmica (Trimestre, Ano, Vendas e Vendedor), o Excel adicionará um relatório à planilha que não faz sentido, mas por que?
Como você pode ver, selecionamos todos os quatro campos para adicionar ao relatório. O comportamento padrão do Excel é agrupar linhas por campos de texto e, em seguida, somar todas as outras linhas.
Aqui nos dá a soma do ano 2014 + 2014 + 2014 + 2014 = 24.168, o que é um absurdo. Também foi dada a soma dos trimestres 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Não precisamos dessa informação, por isso desmarcamos esses campos para removê-los da nossa tabela dinâmica.
"Soma das vendas" (total de vendas) é pertinente, no entanto, então vamos corrigir isso.
Exemplo: vendas por vendedor
Você pode editar "Soma de vendas" para dizer "Total de vendas", o que é mais claro. Além disso, você pode formatar as células como moeda, assim como você formaria outras células. Primeiro, clique em "Soma das vendas" e selecione "Configurações do campo de valor".
Na caixa de diálogo resultante, mudamos o nome para “Total Sales” e depois clicamos em “Number Format” e alteramos para “Currency”.
Você pode ver sua obra na tabela dinâmica:
Exemplo: vendas por vendedor e trimestre
Agora vamos adicionar subtotais para cada trimestre. Para adicionar subtotais, basta clicar com o botão esquerdo do mouse no campo "Trimestre" e mantê-lo pressionado e arrastá-lo para a seção "linhas". Você pode ver o resultado na imagem abaixo:
Enquanto estamos nisso, vamos remover os valores de "Soma do Trimestre". Basta clicar na seta e clicar em "Remover campo". Na captura de tela, você pode ver agora que adicionamos as linhas "Trimestre", que dividem as vendas de cada vendedor por trimestre.
Com essas habilidades em mente, agora você pode criar tabelas dinâmicas com seus próprios dados!
Conclusão
Resumindo, mostramos a você alguns dos recursos das fórmulas e funções do Microsoft Excel que você pode aplicar o Microsoft Excel às suas necessidades comerciais, acadêmicas ou outras.
Como você viu, o Microsoft Excel é um produto enorme com tantos recursos que a maioria das pessoas, mesmo os usuários avançados, não conhecem todos eles. Algumas pessoas podem dizer que isso torna complicado; nós sentimos que é mais abrangente.
Espero que, ao apresentar muitos exemplos da vida real, tenhamos demonstrado não apenas as funções disponíveis no Microsoft Excel, mas tenhamos ensinado algo sobre estatísticas, programação linear, criação de gráficos, números aleatórios e outras idéias que você pode adotar agora. usar na sua escola ou onde você trabalha.
Lembre-se, se você quiser voltar e fazer a aula novamente, você pode começar de novo com a Lição 1!
