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 de 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 obter um número de peça e retornar a descrição do item.
Para investigar isso, vamos voltar para 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 usa a função RANDBETWEEN (1,5) para escolher entre cinco tipos de calçados.
Como Jane não pode usar um número, precisamos convertê-lo em um nome, portanto, usamos as funções de pesquisa.
Usamos a função VLOOKUP para traduzir o número da roupa para o nome da roupa. O HLOOKUP traduz o número do sapato para os vários tipos de sapato na fileira.
A planilha eletrônica funciona assim para roupas:
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: o 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 também querem saber disso porque muitas universidades, especialmente as de prestígio, recusam os alunos com notas baixas 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.
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 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 de 3 DSTs é praticamente zero, porque 99,73% das pontuações estão dentro de 3 DSTs.
Agora, vamos examinar a planilha novamente e explicar como ela funciona.
= MÉDIA (B2: B6)
= STDEV.P (B2: B6)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Representando os resultados
Colocar os resultados em um gráfico facilita a compreensão dos resultados, além de poder mostrá-lo em uma apresentação para tornar seu ponto mais claro.
A classificação do percentil é o eixo vertical à direita de 0 a 90% e é representado pela linha cinza.
Como criar um gráfico
Criando 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.
O recurso “Recommended Charts” normalmente evita que você tenha que lidar com detalhes tão complicados quanto 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 altere para "Score".
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".
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 empresa de transporte aéreo e companhia aérea 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 são 8.024 / 20.000 ou US $ 0,40 por iPad.
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).
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).
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 “Set Objective”.
Se o computador disser que não consegue encontrar uma soluçã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 do capital significa quanto esse dinheiro ganharia se simplesmente o deixassem no banco. Você usa o custo de capital para descontar valores monetários 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 seis por cento.
Depois de 13 anos, o NPV é de US $ 3.945.074, então 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
Os vendedores são muito competitivos (isso é 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, além do quanto suas comissões serão.
Suponha que tenhamos 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.
Selecione “Inserir -> Tabela Dinâmica, está no lado esquerdo da barra de ferramentas:
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 quê?
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 de nossa tabela dinâmica.
Exemplo: vendas por vendedor
Você pode editar "Soma das 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".
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:
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; nos sentimos mais abrangentes.
Esperançosamente, apresentando-lhe muitos exemplos da vida real, demonstramos não apenas as funções disponíveis no Microsoft Excel, mas ensinamos algo sobre estatísticas, programação linear, criação de gráficos, uso de números aleatórios e outras idéias que você pode adotar agora. use 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!
