fórmulas do Excel: pesquisas, gráficos, estatísticas e tabelas dinâmicas

Tendo funções avaliação básicos, referências de célula e funções de data e hora, agora mergulhar em alguns dos recursos mais avançados do Microsoft Excel. Nós apresentamos métodos para resolver problemas clássicos em finanças, relatórios de vendas, custos de transporte, e estatísticas.

Estas funções são importantes para negócios, estudantes e aqueles que são apenas quiser saber mais.

Aqui está um exemplo para ilustrar a pesquisa vertical (PROCV) e as funções de pesquisa horizontal (HLOOKUP). Estas funções são usadas para traduzir um número ou outro valor em algo que é compreensível. Por exemplo, você pode usar PROCV para tirar um número de peça e voltar a descrição do item.

Para investigar isso, vamos voltar para a nossa planilha “Decision Maker” na Parte 4, em que Jane está tentando decidir o que vestir para a escola. Ela não está mais interessado no que ela usa, desde que ela conseguiu um novo namorado, então ela vai agora vestir roupas aleatória e sapatos.

Na planilha de Jane, ela enumera equipamentos em colunas e sapatos verticais, colunas horizontais.

Ela abre a planilha eo RANDBETWEEN função (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 o RANDBETWEEN função de (1,5) para escolher entre cinco tipos de sapatos.

Desde Jane não pode usar um número que precisa converter isso em um nome, por isso usamos funções de pesquisa.

Nós usamos a função PROCV para traduzir o número roupa para citar roupa. HLOOKUP traduz do número de sapato para os vários tipos de sapatos na linha.

A folha de cálculo funciona assim para roupas

Excel escolhe um número aleatório de um a três, uma vez que ela tem três opções equipamento.

Próximo a fórmula traduz o número em texto usando = PROCV (B11, A2: B4,2), que usa números aleatórios o valor de B11 para olhar no intervalo A2: B4. Em seguida, ele dá o resultado (C11) a partir dos dados listados na segunda coluna.

Nós usamos a mesma técnica para pegar sapatos, só que desta vez usamos VOOKUP vez de HLOOKUP.

Quase todo mundo conhece uma fórmula de estatísticas – média – mas há uma outra estatística que é importante para os negócios: desvio padrão.

Por exemplo, muitos uma pessoa que tenha ido para a faculdade tem agonizado com a sua pontuação sáb. Eles podem querer saber como se classificar em comparação com outros estudantes. Universidades quer saber isso também, porque muitas universidades, especialmente os mais prestigiados, desligar os alunos com baixa pontuação SAT.

Então como é que nós, ou uma universidade, medir e interpretar resultados do SAT? Abaixo estão SAT pontuação para cinco estudantes que variam de 1.870 a 2.230.

Os números importantes para entender são

Média – Média também é referido como o “dizer”.

Desvio Padrão (STD ou σ) – Este número mostra como dispersos um conjunto de números são. Se o desvio padrão é grande, então os números são afastados e se ele é zero, todos os números são os mesmos. Pode-se dizer que o desvio padrão é a diferença média entre o valor médio eo valor observado, ou seja, 1.998 e cada pontuação sáb. Por favor, note, é comum para abreviar o desvio padrão usando o símbolo sigma grega “σ”.

Percentil – Quando um estudante recebe uma pontuação elevada, eles podem se gabar de que eles estão no top 99 percentil ou algo parecido. “Percentil rank” significa o percentual de pontuação é inferior a uma determinada pontuação.

desvio padrão e probabilidade estão intimamente ligadas. Você pode dizer que, para cada desvio padrão, a probabilidade ou a probabilidade de que esse número está dentro desse número de desvios padrão é

Como você pode ver, a chance de que qualquer pontuação SAT está fora 3 STD é praticamente zero, porque 99,73 por cento dos resultados estão dentro de 3 STD.

Agora vamos olhar para a folha de cálculo novamente e explicar como ele funciona.

Agora vamos explicar as fórmulas

# Ref!

A média de todas as pontuações sobre o intervalo B2: B6. Especificamente, a soma de todas as pontuações, dividido pelo número de pessoas que fizeram o teste.

# Ref!

O desvio-padrão sobre o intervalo B2: B6. O “.P” significa STDEV.P é usado em todas as pontuações, ou seja, toda a população e não apenas um subconjunto.

# Ref!

Este calcula a porcentagem acumulada durante o intervalo B2: B6 com base na pontuação SAT, neste caso B2. Por exemplo, 83 por cento das pontuações estão abaixo pontuação de Walker.

Colocar os resultados em um gráfico faz com que seja mais fácil de entender os resultados, mais você pode mostrá-lo em uma apresentação para fazer seu ponto mais claramente.

Os alunos estão no eixo horizontal e as suas contagens do SAT são mostrados como um gráfico de barras azul em um (eixo vertical) escala de 1.600 a 2.300.

A classificação percentual é o eixo vertical do lado direito de 0 a 90 por cento, e é representada pela linha cinzenta.

Criando um gráfico é um tema em si mesmo, no entanto, vamos explicar brevemente como o gráfico acima foi criado.

Primeiro, selecione o intervalo de células a ser indicado no gráfico. Neste caso A2 a C6, porque queremos que os números, bem como os nomes dos alunos.

A partir do menu “Inserir”, selecione “Gráficos” -> “Gráficos Recomendados”

O computador recomenda uma “-coluna em cluster, secundário Eixo” gráfico. A parte “eixo secundário” significa que ele chama dois eixos verticais. Neste caso, este gráfico é o que queremos. Nós não temos que fazer qualquer outra coisa.

Você pode usar mover o gráfico ao redor e re-size-lo até que você tê-lo como o tamanho e na posição que você deseja. Quando estiver satisfeito, você pode salvar o gráfico na planilha.

Se você clicar com o botão direito do gráfico, em seguida, “Selecionar dados,” mostra-lhe os dados que são selecionados para o intervalo.

O recurso “Gráficos Recomendados” normalmente socorre-lo de ter que lidar com tais detalhes complicados como determinar quais os dados a incluir, como atribuir rótulos, e como atribuir os eixos verticais esquerdo e direito.

Na caixa de diálogo “Selecionar fonte de dados”, clique em “marcar” em “entradas de legenda (Série)” e pressione “Editar” e alterá-lo para dizer “Score”.

Em seguida, mude a série 2 ( “percentil”) para “percentil”.

Volte para o seu gráfico e clique em “Título do gráfico” e alterá-lo para “SAT Scores.” Agora temos uma tabela completa. Ela tem dois eixos horizontais: uma para pontuação SAT (azul) e um para percentual acumulado (laranja).

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. Ele tem muitas aplicações a grande variedade de problemas de negócios, por isso é útil para aprender como ele funciona.

Antes de começar a trabalhar com este exemplo que temos de permitir que o “Excel Solver.”

Selecione “Arquivo” -> “Opções” -> “Adicionar-ins”. Na parte inferior das opções de add-ins, clique no botão “Go” ao lado de “Gerenciar: Excel Add-ins”.

No menu resultante, clique na caixa de seleção para ativar “, Solver Add-in” e clique em “OK”.

Suponha que nós estamos enviando iPads e estamos tentando preencher nossos centros de distribuição usando os mais baixos custos de transporte possível. Nós temos um acordo com uma empresa de camionagem e companhias aéreas para transportar iPads a partir de Xangai, Pequim e Hong Kong para as distribuições centros mostrado abaixo.

O preço para enviar cada iPad é a distância da fábrica para o centro de distribuição para a planta dividido por 20.000 quilômetros. Por exemplo, é 8.024 km de Xangai para Melbourne, que é 8.024 / 20.000 ou $ 0,40 por iPad.

A questão é como é que vamos enviar todas essas iPads destas três plantas para estes 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 de enviar 462.000 (F12) no total iPads. As plantas têm uma capacidade limitada de 500,250 unidades (G12).

Na planilha, para que você possa ver como ele funciona, digitou 1 em sentido célula B10, queremos enviar um iPad de Xangai para Melbourne. Como os custos de transporte ao longo dessa rota é 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, enviado) “custos” são o B3 intervalos: E5.

E “enviados” são o B9 intervalo: E11

multiplica SUMPRODUCT “custos” vezes o limite “enviados” (B14). Isso é chamado de “multiplicação de matrizes.”

Para que SUMPRODUCT para funcionar corretamente, as duas matrizes – custos e enviados – tem que ser do mesmo tamanho. Você pode contornar essa limitação, fazendo custos adicionais e colunas de transporte e linhas com valor zero para que as matrizes são do mesmo tamanho e não há impacto sobre os custos totais.

Se tudo o que tinha a fazer era multiplicar as matrizes “custos” tempos “enviados” que não seria muito complicado, mas temos que lidar com restrições de lá também.

Temos de enviar o que cada centro de distribuição requer. Colocamos essa constante no Solver como este: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Isto significa a soma do que é enviado, ou seja, os totais em células $ B $ 12: $ E $ 12, deve ser maior ou igual ao que cada centro de distribuição requer ($ B $ 13: $ E $ 13).

Não podemos enviar mais do que produzem. Nós escrevemos que os constrangimentos como este: $ F $ 9: $ F $ 11 <= $ G $ 9: $ G $ 11. Dito de outra forma, o que nós enviamos de cada planta $ F $ 9: $ F $ 11 não pode exceder (deve ser menor ou igual a) a capacidade de cada planta: $ G $ 9: $ G $ 11. Agora vá para o menu "Dados" e pressione o botão "Solver". Se o botão "Solver" não está lá, você precisa habilitar o suplemento Solver. Digite as duas restrições descritas anteriormente e selecione o intervalo de "transferências", que é o intervalo de números que queremos Excel para calcular. Também escolher o algoritmo padrão "Simplex LP" e indicam que queremos "minimizar" a célula B15 ( "custos de envio totais"), onde diz "Definir objetivos". Pressione "Resolver" e Excel salva os resultados na planilha, que é o que queremos. Você também pode salvar isso para que você pode brincar com outros cenários. Se o computador diz que não pode encontrar uma solução, depois de ter feito algo que não é lógico, por exemplo, você pode ter solicitado mais iPads do que as plantas podem produzir. Aqui Excel está dizendo que encontrou uma solução. Pressione "OK" para manter a solução e retornar para a folha de cálculo. Como é que uma empresa decidir se quer investir em um novo projeto? Se o "valor presente líquido" (NPV) é positivo, eles vão investir nela. Esta é uma abordagem padrão tomada pela maioria dos analistas financeiros. Por exemplo, suponha que a empresa de mineração Codelco quer expandir a mina de cobre Andinas. A abordagem padrão para determinar se a avançar com um projeto é calcular o valor presente líquido. Se o NPV é maior do que zero, então o projeto será rentável dado duas entradas (1) tempo e (2) custo de capital. Na planície Inglês, custo de capital significa o quanto seria que o dinheiro iria ganhar se eles apenas deixou-o no banco. Você usa o custo de capital para descontar valores de caixa a valor presente, em outras palavras $ 100 em cinco anos pode ser de US $ 80 hoje. No primeiro ano, US $ 45 milhões estão reservados como capital para financiar o projeto. Os contadores têm determinar que seu custo de capital é de seis por cento. Como eles começam a mineração, o dinheiro começa a chegar em como a empresa encontra e vende o cobre que produzem. Obviamente, quanto mais eles mina, mais dinheiro eles fazem, e sua previsão mostra seu fluxo de caixa aumentando até que chega a US $ 9 milhões por ano. Após 13 anos, o NPV é de US $ 3,945,074 USD, para que o projeto será rentável. De acordo com analistas financeiros o "período de pay-back" é de 13 anos. A "tabela dinâmica" é basicamente um relatório. Nós os chamamos de tabelas dinâmicas, pois você pode facilmente mudar-lhes um tipo de relatório para outro sem ter que fazer todo um novo relatório. Então, eles girar no lugar. Vamos mostrar um exemplo básico que ensina os conceitos básicos. Vendas pessoas são muito competitivos (que é parte de ser um vendedor) para que eles naturalmente querem saber como eles se saem contra o outro no final do trimestre e no final do ano, mais o quanto as suas comissões será. Suponha que temos três pessoas de vendas - Carlos, Fred e Julie - todo o petróleo que vende. Suas vendas em dólares por trimestre fiscal para o ano de 2014 é mostrado na planilha abaixo. Para gerar esses relatórios, criamos uma tabela dinâmica Selecione "Inserir -> Pivot Table, é no lado esquerdo da barra de ferramentas

Selecione as todas as linhas e colunas (incluindo o nome do vendedor) como mostrado abaixo

A caixa de diálogo tabela dinâmica parece o lado direito da planilha.

Se clicarmos todos os quatro campos na caixa de diálogo de tabela dinâmica (Quarter, ano, as vendas e vendedor) Excel adiciona um relatório para a planilha que não faz sentido, mas por quê?

Como você pode ver, nós selecionamos os quatro campos para adicionar ao relatório. O comportamento padrão do Excel é para agrupar linhas de campos de texto e, em seguida, resumir todo o resto das linhas.

Aqui ele nos dá a soma do ano 2014 + 2014 + 2014 + 2014 = 24.168, o que é um absurdo. Também deu é a soma dos trimestres 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Nós não precisamos dessas informações, de modo que desmarcar esses campos para removê-los da nossa tabela dinâmica.

“Sum of Sales” (vendas totais) é pertinente, no entanto, por isso vamos corrigir isso.

Você pode editar “Soma das vendas”, que para dizer “Total de Vendas”, que é mais clara. Além disso, você pode formatar as células como moeda apenas como você formatar quaisquer outras células. Primeiro clique em “Soma das vendas” e selecione “Configurações do Campo de Valor”.

No diálogo resultante, mudamos o nome para “Total Sales”, em seguida, clique em “formato de número” e alterá-lo para “Moeda”.

Você pode então ver a sua obra na tabela dinâmica

Agora vamos adicionar subtotais para cada trimestre. Para adicionar subtotais apenas à esquerda clique no campo “Quarter” e segure e arraste-o para a seção “linhas”. Você pode ver o resultado na imagem abaixo

Enquanto estamos no assunto, vamos remover o “Soma de Quarter” valores. Basta clicar na seta e clique em “Remover campo”. Na imagem, agora você pode ver que nós adicionamos as linhas “quarter”, que degrada as vendas de cada vendedor por trimestre.

Com essas habilidades frescas em mente, agora você pode criar tabelas dinâmicas de seus próprios dados!

Resumindo, eu você tem mostrado algumas das características de fórmulas e funções do Microsoft Excel que você pode aplicar Microsoft Excel para o seu negócio, acadêmico ou outras necessidades.

Como você viu, Microsoft Excel é um enorme produto com tantos recursos que a maioria das pessoas, até mesmo usuários avançados, não sei todos eles. Algumas pessoas podem dizer que o torna complicado, nós sentimos que é mais abrangente.

Esperemos que, ao apresentar-lhe lotes de exemplos da vida real, temos demonstrado não só as funções disponíveis no Microsoft Excel, mas você ensinou algo sobre estatísticas, programação linear, criação de gráficos, usando números aleatórios, e outras ideias que agora você pode adotar e usar em sua escola ou onde você trabalha.

Lembre-se, se você quer ir para trás e tomar a classe novamente, você pode começar de novo com a Lição 1!

Na década de 1980 e início de 1990 (até 1993), a Nintendo procurou manter qualidade de jogo de vídeo de alta via regras de licenciamento rígidas que restringiram as editoras para apenas cinco aprovados títulos do Nintendo Entertainment System por ano.