DICAS profissionais para Excel Parte 6: análise

1. Dicas de análise: Tudo bem, se você está assistindo este vídeo, você está em um deleite. Esta próxima seção do curso irá apresentá-lo a alguns dos excelentes. As ferramentas de análise mais poderosas, como tabelas de dados, folhas de previsão, busca de ouro e gerenciador de cenários. Vamos trabalhar na resolução de otimização complexa é com solucionador, explorando tabelas de dados com funções de cubo e até mesmo construindo simulações de Monte Carlo usando um simulador de roleta em escala completa. Espero que estejas entusiasmado. Agora vamos começar. 2. Ferramentas de análise rápida: Tudo bem para esta dica profissional. Quero falar sobre como podemos explorar instantaneamente nossos dados usando algo chamado ferramentas de análise rápida . Esta é uma dica de análise de uma estrela muito simples de usar, mas uma ferramenta realmente interessante para jogar. Assim, as versões mais recentes do Excel acreditavam em 2013 e em diante, incluem uma opção chamada Quick Analysis Tools, e tudo que você precisa fazer é selecionar um intervalo de células, como este. Você verá um pop-up aparecer no fundo, certo? Parece um relâmpago com um gráfico de barras debaixo dele. E quando você clica nessa opção, ou pressione controle que, você acessará um menu de ferramenta de análise rápida. E neste menu você encontrará todos os tipos de ferramentas de análise, incluindo formatos condicionais, tipos de gráficos, linhas e colunas calculadas. Você pode converter esse intervalo em uma tabela não pode acender linhas. Todos os tipos de ferramentas de análise muito, muito interessantes e poderosas, todas consolidadas neste único menu aqui para acesso rápido. E o que isso nos permite fazer é pegar uma faixa básica de rocha de células como você vê no topo do slide e convertê-lo em algo assim. Você sabe escala de cores ou adicionar uma coluna calculada com os valores médios ou inserir um gráfico de colunas em cluster com o clique de um botão. Qualquer um que me conheça e conheça meus cursos sabe disso. De um modo geral, eu tento evitar esse tipo de ferramentas de atalho como esta porque eu acho que eu sou da velha guarda. Eu gosto de percorrer os menus tradicionais, mas eu estou realmente bastante impressionado com o quão suave e conveniente este menu de ferramenta de análise rápida é, e como resultado, eu comecei a usá-lo um pouco mais eu mesmo. Agora, uma observação aqui, você nem sempre verá todas essas opções porque essas opções e essas ferramentas podem exigir os dados estejam em um determinado formato. Portanto, se você for selecionado, os dados não são compatíveis com um determinado tipo de ferramenta, como uma linha de faísca ou um pivô. Por exemplo, você pode não ver essa opção na barra de ferramentas de análise rápida. Portanto, os casos de uso mais comuns aqui, obviamente apenas explorando rapidamente uma variedade de ferramentas de análise de dados populares sem ter que navegar manualmente através de diferentes opções de faixa de opções. E também, eu descobri que é uma ótima maneira de adicionar rapidamente linhas ou colunas calculadas para coisas como somas contagens médias totais sem ter que realmente digitar em uma única fórmula. Então vamos entrar no Excel. Temos uma boa gama de dados que podemos usar para praticar algumas dessas ferramentas de análise rápida. Tudo bem, então se você quiser acompanhar, vá em frente e abra seu livro de protesto com seu índice, e nós vamos rolar todo o caminho para a direita para nossas dicas de análise. E neste caso, queremos as ferramentas de análise rápida Demo. Vá em frente e links direto para aquela folha. E o que você verá é um simples intervalo de células aqui. Estamos olhando para o filme I am DB pontuações médias por ano 2011 a 2015 e por gênero aqui na coluna A. Agora este é um grande bit de amostra de dados para usar para praticar essas ferramentas de análise rápida porque é bidimensional. Temos um bom intervalo ou tabela de valores aqui que podemos usar para formatação condicional baseada em valor , coisas como escalas de cores e conceitos altos. Temos tempo, Siri ou tendências aqui com cinco anos de dados para que possamos mostrar itens como totais em execução ou gráficos de linhas ou colunas. Temos oportunidades de adicionar linhas calculadas e linha 13 ou linhas de coluna calculada ou faísca aqui na coluna G. Então, é um conjunto de dados bom e versátil para realmente ver uma ampla gama de opções para essas ferramentas. Vamos em frente e selecionar um para todo o caminho até F 12. Note que estou incluindo os cabeçalhos também. E como você pode ver, se você passar o mouse sobre o intervalo que você selecionou, você terá que análise rápida Pop aqui no canto inferior direito. Vá em frente e clique ou pressione o controle Que. E aqui estamos nós no menu de análise rápida e você tem opções de formatação. Totais de opções de gráfico, que são linhas e colunas calculadas, podem ter tabelas ou linhas dinâmicas. Então, todas as ferramentas de análise mais populares e poderosas embaladas aqui em um único menu conveniente. Agora, talvez minha característica favorita seja o fato de que você nem precisa se comprometer com nenhuma opção para ver a saída. Assim, você obtém esse tipo de pré-visualização rápida em sua seleção enquanto você simplesmente passa o mouse sobre essas diferentes opções. E se você gostaria de fazer ações personalizadas, que é algo que eu costumo fazer Quando você clica nessas ferramentas, você vai realmente acessar a caixa de diálogo de formatação onde você pode personalizar você sabe exatamente o que deseja aqui e em seguida, pressione OK e, como qualquer outra alteração, pressione o controle Z desfazer, e você pode saltar de volta para essas ferramentas para que as opções de formatação realmente falam por si mesmas. Muito simples para gráficos. Basicamente, isso apenas insere e novo gráfico como um objeto de pasta de trabalho tem diferentes opções comuns aqui na frente da nossa lista. Na seção total, podemos adicionar calculado subiu certeza nos mostrou este tipo de linha azul sombreado aqui no ícone, ou se continuarmos para a direita, podemos adicionar colunas calculadas. E este uso é o seu modo de agregação ou de verão mais populares, alguns um percentual de contagem média do total e um total em execução novamente, muito fácil de adicionar sem digitar uma única fórmula. Lá podemos converter, organizar para uma mesa, inserir uma tabela dinâmica em branco diretamente a partir daqui, ou podemos adicionar linhas de faísca e sobressair nariz direito fora do bastão Que o lugar mais apropriado para soltar linhas de faísca neste caso estaria bem ali na coluna G Então, novamente, fiquei muito, muito impressionado com essas ferramentas e pelo quão suave e conveniente elas realmente são. Por isso, encorajo você a trabalhar com os dados mostrados aqui ou inserir uma amostra própria e simplesmente explorar como essas ferramentas de análise rápida podem ajudá-lo a analisar e entender seus dados. 3. O gerente de cenário: Tudo bem, vamos tirar alguns minutos e falar sobre excelência, cenário, gerente, gerente ferramenta e, especificamente, especificamente, como essa ferramenta pode ser usada para comparar rapidamente diferentes saídas de modelos. Então, o Gerenciador de cenários, que você encontrará em sua guia de dados sob as opções de análise do What if essencialmente permite salvar e acessar rapidamente combinações específicas de valores de célula predefinidos. Então deixe-me mostrar-lhe um exemplo aqui. Se analisarmos essa opção do gerenciador de cenários, veremos uma lista de todos os cenários pré-definidos que criamos. Neste caso. Eu tenho quatro. E o que acontece quando você adiciona ou cria um novo cenário do zero? Basicamente, estás a dizer ao Excel. Eu queria encontrar um cenário baseado neste conjunto específico de células. Neste caso, três deles nomearam uma célula por cento abaixo. Nomeei uma segunda taxa de juros e 1/3 de duração de célula. Agora uma dica rápida. Aqui. Não precisam se dar nomes. Isto poderia apenas dizer H sete h oito h nove. Acho que nomear essas células ajuda a tornar este processo um pouco mais intuitivo e amigável . Então, essencialmente, estamos dizendo ao Excel que estamos definindo um cenário com base em valores específicos para cada uma dessas três células. Neste caso, eu nomeei o cenário de 20% 30 anos, e para esse cenário, eu quero vender um para mudar para um valor de ponto para 20%. Eu não vou vender para a taxa de juros para mudar para um valor de 0,475 ou 4,75% e eu quero mudar a Célula três o comprimento do prazo para 30 agora. Depois de definir e nomear esse cenário, você pode visualizá-lo rapidamente e alterar automaticamente essas células para os valores que você configurou . Agora. Isso é muitas vezes usado para exercícios de modelagem, que permitem corrigir diferentes combinações de entradas como estas três células aqui, o pagamento inicial, a taxa de juros e a duração do prazo, a fim de calcular o efeito sobre um determinado conduzida saída neste caso, o dinheiro para fechar na propriedade e as despesas mensais estimadas. E se continuarmos esse processo e criarmos três cenários adicionais, então, com um clique de um botão, podemos comparar rapidamente quatro saídas diferentes com base nessas quatro combinações de nossas entradas. Então, como você pode ver uma grande ferramenta para avaliar rapidamente o impacto de diferentes combinações de valores de entrada. Agora casos de uso comuns. Isso é frequentemente usado para construir previsões que são baseadas em várias variáveis que podem mudar como sazonalidade, taxas de juros, etc, etc, ou modelar vários resultados potenciais nos casos em que a incerteza ou o risco é um fator. Por exemplo, criar um cenário para baixo risco, risco médio ou alto para prever os retornos da carteira de ações. Então, com isso, vamos saltar para o Excel e criar alguns desses cenários para nós mesmos. Tudo bem, então vá em frente e abra seu livro de dicas profissionais. Se você estiver acompanhando, teve que gerente de cenário na seção Purple Analytics Dicas e vamos em frente e link direto aqui. Nós temos uma calculadora de custos de propriedade agora, para aqueles de vocês têm seguido junto com todo o curso. Isso pode parecer familiar da nossa dica de auditoria de fórmulas, mas basicamente estamos avaliando dois números principais aqui. Dinheiro necessário para fechar na propriedade e as despesas mensais estimadas, ambos os quais são determinados por uma série de fatores aqui em Collins, G e H preço de compra da taxa de imposto sobre a propriedade, diferentes condições de empréstimo aqui, que vamos nos concentrar nesta demonstração, bem como outros fatores como serviços públicos, custos de seguro, taxas e assim por diante. Agora você vai notar que eu nomeei cada uma dessas células usando a caixa de nome bem aqui à esquerda da barra de fórmulas. Então, por cento abaixo, taxa de juros e duração do prazo e, novamente, isso vai torná-lo um pouco mais legível, mas mais intuitivo uma vez que começamos a criar alguns cenários agora, a idéia aqui é essencialmente predefinir um número de combinações para esses três em H sete a H nove, a fim de determinar de forma rápida e fácil o impacto em nossas principais saídas aqui. E há algumas maneiras de fazer isso. Poderíamos transformá-los em validação de dados, células suspensas, você sabe, contendo uma lista limitada de opções e fazer com que os usuários selecionem manualmente essas combinações. Mas neste caso, nós realmente temos três ou quatro combinações que são mais prováveis e mais realistas e para tipo de predefinir essas combinações. O gerenciador de cenários será uma ótima ferramenta para usar neste caso particular, então vamos em frente e selecionar as três células que farão parte do nosso cenário h sete h oito h nove. Vamos entrar em nossa pesquisa da cidade de dados sobre as ferramentas de análise e se em nossa seção de previsão e clicar em Gerenciador de cenários. A partir daqui, vamos definir quatro cenários diferentes com o propósito de demonstração. O primeiro será de 20% para baixo e uma taxa de juro de 5% para 30 anos. Então vamos em frente e adicionar este. Agora você pode dar o nome que quiser. Neste caso, não vou ser muito inteligente. Eu só vou digitar o que estamos usando para entradas. Então 20% 5%. 30. E desde que selecionamos H 73 cada nove Excel já sabe que estes ar as três células que serão mudando para definir este cenário. E, em seguida, você pode adicionar comentários aqui personalizar a proteção que este caso estava impedindo os usuários de fazer alterações e pressione. OK, então aqui vamos nós. Ele é preenchido com os valores atuais nessas células, e nós podemos modificá-los de acordo com nossos critérios. Então, por cento abaixo do seu ponto para o que é mais fácil para você pode digitar 0.2 ou você pode realmente digitar 20% aqui. Eu sou uma espécie de abordagem um pouco melhor. Tem sido mais legível. A taxa de juros neste caso é de 5% no termo. Comprimento é 30. Então pressione bem lá, e isso criou nosso cenário. E agora veja o que acontece quando eu clico no botão do show boom. Mudou essa taxa de juros para 5%. Então agora essas três células assumem os valores exatos a serem encontrados. Por esse primeiro cenário, acabamos de criar. Então, o mesmo processo. Vamos cruzar e adicionar mais três. O segundo será 20% abaixo, 4% em 15 anos. 20% 4% 15 Pressione OK, 20%. 4% 15 anos. Tudo bem, vamos continuar com isso. O próximo será um pagamento mais baixo, apenas 10% abaixo em 5% em 30 anos. E eu sei que estou indo rápido, então sinta-se livre para abrandar as coisas para acompanhar. Mas aqui temos 10, 5%. Isso foi correto. Mais de 30 anos. Isso parece bom. E finalmente, vamos adicionar nosso quarto aqui, que é 10% tinha 4% taxa de juros acima de 15 e pressioná-lo OK, 10% 4% 15. Lá vamos nós. Então agora temos nossos quatro cenários definidos aqui, e para determinar rapidamente o impacto de cada um desses cenários em nossas células de saída. Tudo o que precisamos fazer é selecionar o cenário e clicar em Mostrar. Então, agora veja isso. Mudou para 20%. 4% 15 clique. O terceiro item mostra 10% 5% 30 e, em seguida, a última opção. 10% 4% 15. E cada vez que alteramos esses itens, você pode ver que nosso dinheiro para fechar e/ou nossos cálculos de despesas mensais mudam acordo. Então, lá vai você demonstração rápida de usar o gerenciador de cenários para definir combinações predefinidas de valores de entrada de células. 4. Otimização com objetivo que objetivo: Esta próxima dica profissional é divertida. Vamos falar sobre como resolver para saídas individuais ou modelos de otimização simples usando excels Gold search ferramenta agora gold seek, que você encontrará em sua guia de dados sob as opções de análise e if basicamente permite que você encontre o resultado que você deseja Ao permitir que o dedo do Excel altere automaticamente o valor de uma única célula de entrada. A busca de ouro requer entradas e saídas muito simples. Você precisa determinar uma única célula de entrada codificada e uma única fórmula baseada células de saída . Não podemos adicionar outras condições aqui. Não podemos testar combinações de múltiplas entradas para aquelas otimizações mais complexas. Os problemas serão usar a ferramenta Excels Solver em uma demonstração mais avançada no curso. Então considere um caso como este. Temos uma entrada baseada no valor, que é a quantidade de unidades vendidas ali mesmo e vender assento para. Temos um conjunto de fatores de cálculo como o preço unitário ou preço de varejo, o custo para produzir cada unidade e um determinado custo fixo fixo. E com esses fatores, juntamente com o valor da quantidade, podemos determinar o lucro, que é a nossa fórmula baseada na saída neste caso, nosso lucro será igual a quantidade vezes a diferença entre o preço e o custo unitário menos o custo fixo fixo fixo. Então, este cenário particular, este modelo simples verifica todas as caixas para a ferramenta de busca de objetivo. Temos uma única quantidade de entrada codificada e uma única saída baseada em fórmula. O profeta. Então vamos ver como isso realmente se parece. Quando perfurarmos essa ferramenta de busca de objetivo, você verá uma caixa de diálogo que se parece com isso, e o que estamos fazendo aqui é definir nossa célula de saída C oito, que é nosso lucro para um objetivo específico ou valor alvo neste caso, $1000 alterando essa entrada baseada no valor, consulte a quantidade. Então, em inglês simples estavam dizendo ao Excel, Eu quero que você mude a célula C dois como você precisa, a fim de pousar em US $1000 de lucro. Quanta quantidade ou quantas unidades estão fazendo para vender para ganhar 1000 dólares? E quando você configura o ouro, procure desta forma e pressione OK, ele automaticamente produz esses valores. Você fixou seu lucro $2000 tipo de trabalho retroativo para a quantidade que equivale a 833 unidades vendidas agora casos de uso comuns aqui, assim como estamos mostrando aqui, determinando a entrada ideal necessária para produzir uma meta ou meta específica resultado. Você sabe, por exemplo, nós poderíamos fazer um lucro de US $1000 aqui. Poderíamos fazer um lucro de zero, a fim de descobrir quanta quantidade precisamos vender para quebrar o mesmo. Você conhece qualquer combinação como essa, ou realmente apenas resolvendo quaisquer problemas de otimização simples que são baseados em uma única variável de entrada . Então, vamos trazer este exemplo para o Excel e praticar a configuração da ferramenta de busca de ouro nós mesmos. Tudo bem, então se você quiser me acompanhar, abrir sua pasta de trabalho de dicas profissionais e do nosso índice, vamos aprofundar nosso objetivo, procurar demonstração em nossa seção de dicas de análise e ir em frente e link para fora. E aqui, você verá, são familiares. Modelo muito simples baseado nesta quantidade de entrada e esta saída do profeta. E como você pode ver, se eu selecionar a célula C dois, este é simplesmente um valor codificado. Temos alguns fatores de cálculo pré-definidos aqui a partir do preço unitário, do custo unitário e do custo fixo em nossa célula profeta. C oito. Esta é a nossa saída orientada pela fórmula, que leva a quantidade, multiplica pelo preço unitário menos o custo unitário e, em seguida, subtrai o custo fixo no final para determinar o lucro real. Então, como você pode ver, você pode ajustar valores diferentes aqui. Ok, se eu vendesse 325 unidades, meu lucro seria para 38. Se eu vendesse 23.000 unidades, meu lucro seria de 34.250 e assim por diante. Então, a maneira como muitos usuários que não estão familiarizados com o ouro buscam usarão o Excel e especificamente usarão um modelo como este é, vocês sabem, digamos que queremos US$10.000 de lucro. Você sabe, você pode dizer, OK, vamos tentar vender 8000. Bem, isso é demais. Vamos tentar que 7500 ainda estão muito altos. Talvez 6000 para baixo 65. Você está tentando estreitar esse número usando os convidados e verificar abordagem, e isso é exatamente o que ouro busca é projetado dedo do pé automatizar para você e vontade. Você também pode tipo de volta para a fórmula para criar esse valor de quantidade ideal, pré enviar sua busca de lucro ouro é projetado para fazer isso para você passar por esse processo e não fazer você tipo de reverter os cálculos para descobrir esse número e ver para. Então vamos saltar para a nossa guia de dados. Vamos olhar para as nossas ferramentas de análise e clique em busca de ouro ou segunda opção aqui e tudo o que temos são três condições aqui. Vamos definir uma célula específica, que é nosso lucro definir esse profeta para um valor específico. E isso pode ser qualquer coisa. Vamos começar com $0. Quero encontrar o ponto de ruptura. E queremos fazer isso alterando uma determinada célula, que é a quantidade aqui e ver, e tudo o que precisamos fazer é pressionar OK e verificar. Acabou de automatizar o processo de resolver isso para nós. Então o valor alvo era zero. Ele otimizado encontrado valor atual de zero. Certo, isso nos diz que precisamos vender 167 unidades antes de quebrar o lucro. E para confirmar isso, se escolhermos 166 bem, perdemos um dólar. Escolha 1 68 que ganhamos $2 e você pode testar rapidamente diferentes cenários aqui. Como neste caso, queremos definir o profeta para 100.000, por exemplo. Exatamente o mesmo processo. mesmas entradas exatas. Pressione OK, e lá vai você. Encontrei uma solução e disse-nos que está bem. Por US$100.000 de lucro, você tem que vender 66.833 unidades. Então, há um curso intensivo em busca de ouro. É uma ferramenta bastante incrível quando você precisa otimizar modelos simples de entrada única e saída única no excel. 5. Previsão básica: Certo, hora de falar sobre uma das minhas técnicas favoritas de análise de previsão. E, especificamente, quero mostrar a vocês como podemos criar folhas de previsão usando dados históricos e excel. Agora. Versões recentes do Excel acredita que começando com 2016 e Office 3 65 incluem uma ferramenta de fraude de previsão integrada que permite calcular previsões com base em um determinado conjunto de valores históricos. Então, por exemplo, temos dados básicos como esta temperatura média mensal, que neste caso realmente continuam a se estender por cerca de seis anos de dados. O que podemos fazer é selecionar ambas as colunas, incluindo o mês ou o indicador de data, bem como os valores Brochar na guia de dados e clicar na ferramenta de fraude de previsão ao lado das opções de análise “what if”. E quando fizermos isso, veremos uma visualização básica da previsão padrão que o Excel produziu, bem como opções para personalizar coisas como o comprimento da previsão, o intervalo de confiança, qualquer sazonalidade e também personalizado como lidar com coisas como valores ausentes ou duplicados. E uma vez que você tenha configurado essas configurações como você gostaria, Excel produzirá uma folha que se parece com isso. Esta é a folha de previsão real. É uma nova guia em sua pasta de trabalho e contém o gráfico de sazonalidade, bem como uma nova tabela de dados contendo seus valores históricos existentes, bem como um conjunto de valores calculados previstos. Vou mostrar-lhe alguns exemplos de exatamente como isso parece no Excel agora uma nota rápida . Às vezes, você pode usar ferramentas como linhas de tendência para exercícios de previsão muito simples. Mas tenha em mente que isso não explica coisas como confiança ou sazonalidade. Então, para casos mais complexos ou personalizados e definitivamente recomendo usar esta opção de fraude previsão em vez disso. Então casos de uso comuns aqui para um, prevendo quaisquer valores futuros, como taxas de juros ou retornos de ações. E dois se você quiser calcular um intervalo esperado de resultados futuros com base em um determinado nível de confiança, esta folha de previsão e as fórmulas que ela produz são muito, muito úteis para fazer isso. Então, com essa conversa suficiente, vamos para o Excel na prática, construindo nossa própria previsão. Tudo bem, então para aqueles que acompanham comigo tiveram sua pasta de trabalho de dicas profissionais, estamos procurando a demonstração básica de previsão em nossa seção de dicas do Google Analytics roxo. Vá em frente e link direto para fora, e aqui você verá dois conjuntos diferentes de dados tem dados do mercado de ações aqui em colunas, um a E, olhando para os preços das ações da Apple, esse preço de fechamento e volume por dia. Temos também algumas temperaturas médias mensais de Barcelona, Espanha, que abrangem cerca de seis anos de dados. Então, dois tipos muito diferentes de conjuntos de dados que vamos usar para praticar a previsão. Então vamos começar com nossos dados do mercado de ações aqui. E uma coisa que quero mostrar a vocês é que se selecionássemos a coluna B e controlássemos, clicássemos nos valores que queremos prever, que neste caso será o preço das ações de fechamento. Agora, se formos para nossa planilha de dados e escolhermos a planilha de previsão ao lado da análise e se, você verá esta mensagem de erro que diz: “ Ei, Ei, não podemos criar uma previsão porque sua linha de tempo não está uniformemente espaçada. Em outras palavras, você tem lacunas inconsistentes entre alguns desses pontos e a razão que é o caso, porque o mercado está fechado nos feriados e fins de semana. Então nós temos essas lacunas, você sabe, como 2 e 3 de março, onde nós não fornecemos nenhum dado aqui. Não há preço de fechamento ou volume, então o que eu fiz foi em uma nova coluna aqui. Coluna C Isso é apenas uma série de valores sequenciais ou um índice que basicamente apenas rotula o Dia dos Dias um dia, dois dias três, independentemente de ser um fim de semana ou dia da semana ou feriado, o que quer que seja. E agora, se selecionarmos as colunas C e D e voltarmos para a nossa folha de previsões agora, seremos capazes de produzir uma previsão real, então vender terá seu melhor palpite em suas configurações. Você sabe a sua previsão. Comece sua previsão e intervalo de confiança sazonalidade, etc. Mas se você quiser personalizar exatamente como isso é configurado, vá em frente e aprofunde as opções no canto inferior esquerdo e aqui temos um monte de opções adicionais para escolher, e uma opção é mudar o quão longe você deseja um previsão. Então, talvez, em vez de 1574 dias, talvez nós só precisamos prever para 1400 dias, o que tipo de encolhe a duração da previsão dele. Outra opção é realmente mudar um. A previsão começa. Então, por padrão, esta previsão vai começar. No último dia em que temos valores reais e a razão pela qual os padrões se destacaram para adotar essa abordagem é que permite que ele tipo de usar todos esses pontos de dados. Todos esses reais históricos ajudam a gerar a previsão. Mas o que podemos fazer é mudar isso. Você não para algo como 1000 dias e forçar o Excel para iniciar a previsão mais cedo. E basicamente isso nos dá um período de sobreposição onde podemos realmente comparar os valores previstos com os reais. Agora, a desvantagem que estamos vendo os resultados aqui é que ao fazer isso acelerando oinício da previsão, estamos alimentando início da previsão, menos informações históricas para se destacar e, portanto, potencialmente impactando o precisão da própria previsão. Então talvez nós meio que dividimos a diferença aqui e digamos, “ Começamos em 1100 dias”. Isso parece muito bom. Ainda temos uma boa parte de sobreposição aqui, mas parece ser uma previsão um pouco mais realista. Agora, os outros itens que enfraquecem personalizado aqui o intervalo de confiança e que é basicamente criar esses dois limites os limites superior e inferior e nos dizendo que podemos estar 95% confiantes de que os valores reais observados no futuro cairá dentro desses limites, então veja o que acontece quando aumentarmos a confiança para 99. Você notará que os limites ganham mais parte, criando uma janela mais ampla que poderia potencialmente conter nossos valores futuros reais. E se reduzirmos isso para algo como 50% de confiança agora, teremos um limite muito mais estreito e um alvo muito mais difícil de atingir. Portanto, foram menos confiantes de que os valores reais sempre cairão dentro deste intervalo. Então, modo geral, você vai querer ficar com algo como 90 ou 95 esse tipo de ar padrão. E a partir daqui também podemos lidar com a sazonalidade. Por padrão, o Excel detectará a sazonalidade automaticamente. Neste caso, se houvesse um certo número de dias ou um certo período em que vemos um padrão repetido repetidamente , poderíamos definir esse padrão manualmente, e eu mostrarei exatamente como poderíamos fazer isso para os dados meteorológicos. E, em seguida, quando criamos a folha, nós também podemos incluir algumas estatísticas de previsão como Basta marcar essa caixa. E por último, mas não menos importante, temos o nosso intervalo de linha de tempo e coluna C Dia valores de índice Living coluna D, e então podemos escolher se tivéssemos pontos ausentes poderíamos interpretar tarde cedo aqueles em zero. Neste caso, não temos nenhum ponto perdido. E se acontecer de termos duplicados, poderia escolher como resumir ou agregar. Essas duplicatas, na maioria dos casos, representam uma média de escolha adequada. Então, com isso, estou confiante. Estou feliz com nossas escolhas. Dessa forma, podemos descobrir isso. Vá em frente e clique. Crie, e o que o Excel faz é inserir uma nova folha aqui, nos dá um pouco de pop up toe. Dê-nos as boas vindas à nossa nova folha, e ela diz: “ Ótimo. Criamos uma tabela com uma cópia de nossos dados Colunas A e B juntamente com os valores previstos e nossa confiança. Limites na coluna C, D e E Então entendi. Parece bom. Vamos ir em frente e arrastar esta folha para a direita da nossa folha de previsão e nomeá-la algo como previsão de ações. Verifica isso. Ele incluiu o gráfico para nós, assim como nós visualizamos. Incluiu estas estatísticas de previsão aqui nesta pequena mini-tabela nas colunas G e H. Eu não vou entrar nelas agora e então tenho nossas cópias reais de nossos pontos de dados históricos aqui. Em vem A e B e se rolar para baixo ou ainda mais eficiente, selecionado venda vazia usado. O controle está se aproximando. Aqui vamos nós, o último dia de nossos valores observados ou, neste caso, o primeiro dia que determinamos que queremos que nossa previsão comece agora. Essas novas colunas de ar sendo povoadas aqui também, e elas estão sendo preenchidas com fórmulas de previsão que estão alimentando entradas com base em como configuramos nossas configurações na caixa de diálogo e a mesma coisa com nosso confiança superior. Limites estavam usando fórmulas de intervalo de confiança confiante aqui. Então, se quiséssemos ajustar qualquer uma dessas configurações, poderíamos voltar e criar uma nova previsão do zero usando a caixa de diálogo. Ou podemos ir em frente e atualizar entradas individuais ou argumentos dessas funções e ter a tabela atualizar dinamicamente. Então ferramenta muito útil lá. Vamos rolar o topo. E uma coisa que quero mostrar aqui é que, neste caso, podemos editar este gráfico como qualquer outro. E digamos que queríamos seguir uma abordagem mais simples para prever esses valores de ações e simplesmente ir para suas ferramentas de gráfico. Adicione um elemento de gráfico e lance uma linha de tendência linear no lugar que criou esta linha de tendência pontilhada azul que, como você pode ver, segue quase a previsão exata de que o Excel é criado. Então, neste caso particular, usar uma previsão linear como uma primeira passagem muito rápida e simples pode ser uma abordagem válida, e na verdade, ela produzirá quase exatamente os mesmos valores que a nossa previsão aqui. Mas isso nem sempre vai funcionar. E um caso em que muitas vezes fica aquém é um caso em que você tem sazonalidade. Então vamos voltar para a nossa folha de previsão básica, Selecionar Colunas G e H Desta vez, que são nossos dados mensais de temperatura. Volte aos dados e vamos ver o que acontece aqui e escolhemos a folha de previsão. Então, dê uma olhada nesse padrão muito, muito diferente aqui, e há claramente sazonalidade que está acontecendo ao longo desses seis anos de valores reais. Então a diferença entre este exemplo e o exemplo de estoque é que um nós não podemos simplesmente esbofetear uma opção básica de linha de tendência aqui para seguir esse caminho de previsão porque há uma tendência de sazonalidade acontecendo porque, literalmente, estamos olhando para o valor da temporada de dados. Eso Excel está dizendo que tudo bem nós detectamos sazonalidade automaticamente e se você disse isso manualmente, estavam basicamente dizendo a cada 12 pontos, que são meses, vemos o mesmo padrão repetir, o que faz sentido. Então, neste caso, eu não vou criar uma nova folha. Vai parecer o mesmo, tem o outro. Mas aí você tem, também. Bons exemplos de como usar a folha de previsão do excels para fornecer valores previstos ou previstos usando dois tipos muito diferentes de dados. 6. Detecção de outlier: Tudo bem para esta dica profissional, estamos ficando um pouco mais avançados. Esta é uma dica de análise de quatro estrelas. Vamos falar sobre como encontrar ou detectar outliers, usando uma combinação de estatísticas, funções e formatação condicional. Agora, o que vamos fazer aqui é usar funções como mediana e quartil na verdade calcular outliers estatísticos, e então vamos aplicar regras de formatação condicional para chamar a atenção para eles ou realçá-los dentro de nossas colunas. Então, neste caso, estamos olhando para dados de atleta com nomes e coluna A. Temos alturas e coluna B e pesos e colunas. Veja, e o que vai fazer é criar os cálculos outlier. Usando essas funções de estatísticas, calcularemos o valor mediano. O primeiro quartil, terceiro quartil, o intervalo inter quartil ou como você é e algo chamado multiplicador de defesa. E essa cerca é essencialmente o que determina o quão longe da norma de valor deve cair para ser rotulado como um outlier. E o que vai acabar com é um intervalo de valores para encontrar pela cerca interna ou inferior e a cerca superior ou externa, que significa que quaisquer valores que caem fora desse intervalo bem, tecnicamente ser rotulados como um outlier. Então, a partir daí, tudo o que precisamos fazer é ir para a nossa guia inicial, regras de formatação condicional. E vamos usar uma regra de realce personalizada para destacar células que não estão entre esses valores de fence e vende F oito F 11. E quando aplicamos essa regra de formatação para, neste caso, coluna B imediatamente receber os valores outlier realçados em amarelo Now. Uma coisa a notar. Se você estiver lidando com cálculos muito mais simples ou regras de formatação, como os valores finais superiores ou uma porcentagem superior específica, basta usar formatos condicionais básicos. Você não tem que passar por todo esse processo, mas o que estamos fazendo é usar funções estatísticas reais para definir esses outliers de uma forma um pouco mais científica. E como vou mostrar a vocês daqui, podemos ajustar nossa cerca e ver como isso impacta, que vende ar sendo destacado e rotulado como outliers. Portanto, casos de uso comuns aqui, primeiro e acima de tudo, identificando quaisquer anomalias estatísticas no conjunto de dados com base nesses critérios personalizados que você define e, em segundo lugar, localizando e removendo valores que podem ter sido inseridos incorretamente. Então, se nós vimos a altura aqui de 800 polegadas. Você sabe, isso vai ser muito rapidamente sinalizado como um outlier e um mentor que configurações escolhemos. E isso seria uma boa indicação de erro de entrada do usuário e não ah, não apenas um ser humano muito, muito alto. Então, com isso, vamos entrar em uma pasta de trabalho profissional. Vamos dar uma olhada neste conjunto de dados de demonstração exato e executar este processo passo a passo . Tudo bem, então se você está acompanhando, você deve saber o que fazer agora, tinha o seu índice. E procure a demonstração de detecção de outlier em nossa seção de dicas do Purple Analytics. Vá em frente e ligue diretamente para isso. Ela e o que você encontrará aqui são nomes de jogadores ou atletas e coluna A. E assim como descrevemos alturas e pesos e colunas B e C. Nosso objetivo é preencher esses cálculos estatísticos aqui na coluna F para determinar exatamente como estamos tratando e definindo outliers. Então vamos começar com mediana e vender F para. Isso não será realmente usado em nosso cálculo outlier, mas é uma ótima estatística de resumo para incluir eu vou apenas usar a função mediana 0.2 coluna B pressione OK. Isso me diz que a altura mediana nesta amostra de 74 polegadas agora que nos traz para as funções de azulejo núcleo. Comece digitando uma fórmula de quartil, e o que você verá são três opções diferentes. Aqui você tem o E X, c ou versão exclusiva I NC versão inclusiva e a versão original quartil, que é uma função de compatibilidade compatível com versões anteriores do Excel. E o que isso diz a vocês é que originalmente, havia apenas a função quartil e, em seguida, começando e eu acredito que 2010 Microsoft começou a introduzir novas variações dessa fórmula. Agora, neste caso particular, veremos a mesma resposta, não importa qual versão usamos. Mas eu quero mostrar-lhe o i N C. A versão inclusiva agora, alguma guia que em todas as três versões usam os mesmos argumentos, começando com o array cominby e, em seguida, o bloco tribunal, que você pode rotular com um 0123 ou Quatro. E neste caso, queremos o primeiro quartil ou o percentil 25 em uma prensa 81 lá e fechá-lo e pressione enter. Agora o que isso nos diz é que os jogadores que têm 72 polegadas de altura são pelo menos tão altos quanto 25% da outra rosa ou jogadores na amostra. Agora um pouco de nuance aqui. Mas a diferença entre as versões inclusivas e exclusivas é que a versão exclusiva basicamente determinaria o valor que é maior do que 25% dos valores na matriz . A versão inclusiva determina o valor que é maior ou igual a 25% dos valores na matriz. Então essa é a diferença aí. Essa é a nuance. Atos exclusivos como maior do que atos inclusivos como maior ou igual a. E uma outra nota aqui. Se você quiser usar esta função para retornar o valor mínimo ou máximo com um rótulo de zero ou quatro, você teria que usar a versão inclusiva para fazer isso. Então está indo e caindo. São um de volta em sua imprensa enter, podemos simplesmente copiar essa fórmula. Colá-lo aqui para 1/3 quartil simplesmente mudou o 123 e nós temos um 75. Agora eu Q R de ar Inter-quartil alcance. Isso é um simples como o terceiro trimestre eu vou em um quatro menos o primeiro quartil em F três. É um alcance de três polegadas, e em seguida, nosso multiplicador de cerca. Agora, o que a cerca faz é essencialmente determinar quão longe fora do nosso intervalo inter quartil um valor deve cair para ser rotulado como um outlier estatístico. Assim, quanto maior a cerca ou maior o multiplicador da cerca, mais longe do alcance esperado. Esse valor precisa cair para ser considerado um outlier, e este é um valor codificado. Em muitos casos, 1.5 é uma espécie de bom ponto de partida padrão. Vamos em frente e começar por lá e, em seguida, por último, mas não menos importante, precisamos determinar nossa cerca interna ou cerca inferior, bem como nossa cerca externa ou cerca superior . Assim, a cerca inferior é definida como o primeiro quartil, menos parênteses abertos. Inter-quartil alcance vezes nossa cerca então F três menos cinco vezes em seis que retorna um 67.5 . Isso significa que o valor mais baixo que um Selcan toma antes de ser tratado como um outlier é de 67.5 polegadas. Lógica muito semelhante aqui para a cerca externa ou cerca superior. Pegue o terceiro quartil e vamos adicionar o Q.R vezes o multiplicador da cerca. Q.R Feche-o. Pressione Enter. O mesmo processo de pensamento aqui. O valor mais alto que um Selcan toma antes de ser tratado como um outlier é de 79.5 polegadas. Agora, a partir daqui, nós fizemos a parte difícil. O último passo é realmente sinalizar ou chamar a atenção para os outliers reais que definimos aqui na coluna B. Então é grabby, também. Antiga seta de deslocamento de controle para baixo para agarrar toda a coluna. Vou entrar no nosso menu inicial formatação condicional. E nós vamos usar, hum, hum, regras de células destacadas aqui. E o que realmente queremos são células que tenham um valor que não esteja dentro do alcance de nossas cercas superiores e inferiores. Então, queremos uma opção não entre aqui, que não temos é um dos nossos padrões. Mas se entrarmos em mais regras aqui, temos essa opção onde podemos dizer formatar valores de célula que não estão entre. E então podemos simplesmente apontar para as células que nos importamos, que estão todo o caminho aqui em cima no topo. Portanto, queremos destacar as células que não se enquadram neste intervalo. Eles não estão entre oito ou 11 e nós obedecemos. Formatar este é até você vai usar tipo de Phil amarelo aqui e pressione OK e confira . Pressionamos OK agora. Qualquer valor maior que a cerca superior maior que 79.5 agora é realçado. E se saltarmos para o erro de controle inferior para baixo, temos valores aqui que estavam abaixo da cerca inferior de 67,5. Então, nós essencialmente sinalizamos esses outliers com base em nossas condições estatísticas ou uma configuração aqui. E vejo o que acontece se mudarmos este multiplicador de cerca de 1.5 para 1. Agora estamos incluindo mais valores que estavam rotulando como outliers aqui porque criamos essencialmente uma faixa mais estreita de valores normais. Nós mudamos este multiplicador de cerca para ou expandimos essa cerca. Agora estamos tornando menos provável que um valor seja rotulado como um outlier. Então só vemos alguns pontos de dados aqui na coluna B que são marcados neste caso, três dos jogadores de beisebol mais altos do jogo. Então lá você tem um grande curso sobre como usar algumas dessas funções estatísticas relativamente simples combinadas com formatação condicional toe realmente detectar e sinalizar outliers em seus dados 7. Tabelas de dados automatizadas: Tudo bem para esta dica profissional, vamos falar sobre uma dica de análise de quatro estrelas, relativamente avançada e vamos discutir como avaliar entradas variáveis usando tabelas de dados do Excels. Agora, as tabelas de dados que estamos falando aqui são diferentes dos intervalos de células padrão , formatadas como tabelas. Na verdade, eu gostaria que o Excel lhes desse um nome diferente porque eles realmente operam. Muito diferente estamos falando são as tabelas de dados que você vai encontrar em sua guia de dados a partir das ferramentas de análise e o que essas tabelas de dados fazem é que eles permitem que você calcule em uma matriz de resultados com o clique de um botão baseado em um intervalo inteiro variedade de potenciais valores de entrada. Então, para mostrar o que quero dizer com isso, vamos olhar para a nossa ferramenta de calculadora de custos de propriedade, que já vimos antes ao longo do curso. E a idéia aqui é entender exatamente quais são nossos custos de hipoteca. Os custos de hipoteca mensais pareceriam baseados em diferentes taxas de juro que variam todo o caminho para baixo de 3% até 10% em incrementos de metade percentual. Então, o tipo manual de abordagem tediosa da velha escola seria ligar essas diferentes taxas de juros na Célula H 8 e ver o que os custos da hipoteca cuspiam e vender h 14. Mas em vez de fazer isso, vamos ficar um pouco mais sofisticados, e vamos selecionar esta tabela este intervalo em branco em colunas, Jane K. Nós vamos usar a ferramenta de tabela de dados Excels para produzir uma matriz de resultados com base nessa gama de taxas de juro. Então nós vamos para a nossa cidade de dados vamos perfurar a opção tabela de dados a partir das ferramentas de análise e se . E então aqui temos apenas duas entradas potenciais uma célula de entrada de linha e uma célula de entrada de coluna. E nesta demonstração em particular, tudo o que temos é uma coluna contendo esses valores de intervalo e a célula na qual queremos testar esse intervalo de valores vive em h oito. Então, tratando os oito anos como a entrada da coluna, Excel é capaz de cuspir toda esta matriz de resultados ali mesmo na coluna K com base no intervalo de taxas de juros na coluna J. Então, em vez de testar manualmente 15 ou assim diferentes juros e registrando os resultados ou digitando uma fórmula 15 vezes, configuramos a ferramenta de tabela de dados uma vez e produzimos todos os resultados com o clique de um botão. Agora, tabelas de dados podem ser usados para avaliar resultados como vemos aqui com base em alterações em uma única variável de entrada como mostramos com taxa de juros ou com múltiplas variáveis, caso em que também conectamos uma célula de entrada de linha. E vou mostrar-vos um exemplo de ambos assim que entrarmos no Excel. Mas casos de uso comuns rápidos reais calculando uma matriz de resultados com base em combinações de valores de entrada como este. Pagamentos mensais baseados em coisas como taxas de juros e valores de pagamento para baixo. Ou levar isso até um passo adiante. Você pode usar essa ferramenta para identificar o resultado ideal dadas múltiplas combinações de entradas variáveis. Então vamos em frente. Abra nosso livro de dicas profissionais, arregace nossas mangas e pratique a construção de algumas dessas tabelas de dados. Tudo bem, então do seu índice, vá em frente e role até as dicas de análise. Vamos olhar para a demonstração da tabela de dados neste caso, ir em frente e ligar diretamente para aquela aba roxa. E aqui você verá nossa calculadora de custo de propriedade, que, como já vimos antes, leva um monte de entradas como o preço de compra, essa taxa de imposto e alguns termos de empréstimo e cuspe saídas como o valor do empréstimo que hipoteca custos, imposto sobre a propriedade, etc. Agora, o que nos importa neste caso é o custo da hipoteca mensal aqui e vender H 14. E este custo de hipoteca é uma função de alguns fatores diferentes para um, o pagamento inicial e para a taxa de juros, entre outros. Então, se alterarmos o seu pagamento inicial de 20% para 30%, você notará que nossos custos mensais de hipoteca são reduzidos. Em contrapartida, se alterarmos a nossa taxa de juro de 4% para 5%, os nossos custos hipotecários aumentam. Então esses dois fatores impactam o custo da hipoteca. Então o que vamos fazer aqui é criar duas tabelas de dados diferentes para testar ou avaliar diferentes combinações desses potenciais valores de entrada. Então, no primeiro caso aqui, tudo o que estamos testando são variações na taxa de juros. Então, o primeiro passo é criar uma coluna contendo cada uma das variações na taxa de juros que queremos testar, a fim de ver o impacto na nossa fórmula de saída ou custos de hipoteca. E eu só tenho que mostrar esta dica aqui, você pode digitar esses valores manualmente ou o que você pode fazer e excluir esses Você pode digitar seu primeiro valor aqui, mouse sobre o canto inferior direito e ouvir isso. Segure o botão direito do mouse, arrastado para baixo e backup, em seguida, solte para acessar este tipo de menu oculto aqui. E o que vamos fazer é cair na casa da Siri no fundo. Vamos preencher um Siris em uma coluna. Vamos pisar ou incrementar em 0,5%. Vamos parar em 10%. Essa é a nossa imprensa de valor máximo. OK, Booms preencheu aquele perfeito, incremental sério para nós com um clique no botão. Agora, o objetivo aqui é preencher essas células em branco aqui de K 4 a K 18. E para fazer isso com tabelas de dados, temos que configurar as coisas de uma maneira muito específica. primeiro passo, que acabamos de alcançar, é definir o intervalo de valores de entrada. O segundo passo é, na verdade, vincular à fórmula que estamos procurando avaliar. Então nós estamos apenas pegando a fórmula de H 14 e nós estamos fazendo referência aqui mesmo em South K três. O que isso nos permitirá fazer é selecionar toda essa gama de células, que inclui tanto a fórmula em si quanto a variedade de valores de entrada. E a partir daqui podemos entrar em nossa guia de dados. E se a análise datada tabela e novamente, assim como a nossa demo. Nós não temos quaisquer variáveis de entrada em uma linha, mas temos entradas variáveis em uma coluna, e a célula de entrada em si não é esse intervalo que eu acabei de criar. É a célula que é referenciada na fórmula que contém a taxa de juros, que neste caso é vender H 8. E isso é tudo o que precisamos para configurar esta tabela de dados pressione OK, e lá vamos nós. Ele é preenchido todas as saídas de custo mensais com base nessa faixa diferente de taxas de juros . E como você pode ver, ele gerou uma única matriz ou tabela em vez de 15 fórmulas individuais aqui. Então, passando para a nossa próxima demonstração, vamos seguir essa mesma abordagem, exceto que estamos adicionando um conjunto adicional de critérios aqui para pagamentos iniciais diferentes, então ele vai fazer aqui é criar uma matriz real de valores que não respondem apenas muda para a taxa de juros, mas também muda para baixo pagamento quer 5 10 ou 20% processo tão muito semelhante aqui e selecione toda a gama de células, incluindo a fórmula e as células de entrada de linha variável e coluna. Vamos entrar em dados. E se a análise datada da tabela? E agora temos uma célula de entrada de linha. E essa é a célula na fórmula contendo nossas variações de entrada de linha, que são os pagamentos para baixo e vender H sete. Lá vamos nós. E novamente, nosso Colin é a célula que contém a taxa de juros. Apenas o nosso índice de coluna e imprensa. OK, e aí está. Produzimos uma única matriz bidimensional desta vez que produz valores de saída com base em vários critérios. Então, lá vai você. Essas tabelas de dados no Excel podem ser um pouco complicadas, um pouco embaraçosas para trabalhar no início. Mas uma vez que você realmente obter um controle sobre eles, eles podem ser uma ótima ferramenta para avaliar um conjunto inteiro de resultados com base em entradasvariáveis entradas 8. Ferramentas de consulta: Tudo bem. Eu quero tomar alguns minutos e orientá-lo através de uma introdução rápida e breve a uma das ferramentas mais poderosas do Excel chamado Power Query. Agora, esta é uma dica de análise avançada de quatro estrelas. Vamos falar sobre como conectar, moldar, moldar, transformar e carregar dados de fontes externas em Excel usando essas ferramentas de consulta de energia. Então, na moda clássica da Microsoft, você pode ver essas ferramentas nomeadas todos os tipos de coisas diferentes. Dependendo da versão do Excel que você está usando, você pode ver consulta de energia que você pode ver. Obter dados que você pode ver entrar em transformação e tudo significa a mesma coisa. Estou usando o escritório 3 65 pro plus. E isso é o que vejo na minha guia de dados. Eu tenho um grupo de ferramentas chamado Obter e Dados Transformados e eles obter o comando de dados fora para a esquerda. E o que vamos fazer nesta demonstração é conectar-se a uma nota de arquivo CSP simples que você também pode se conectar a fontes de banco de dados ou web ap. Eu não sou assim por diante e assim por diante. Este caso, vamos mantê-lo simples. Eu disponibilizei este arquivo, faz parte do curso. Basicamente, vamos fazer uma análise rápida de alguns dados da campanha do Kickstarter. Então, uma vez que apontar para o arquivo vai ver esta visualização consulta e, em seguida, clique em Editar reboque. Inicie nosso editor de consultas, e isso será aberto como uma pasta de trabalho separada fora de nosso ambiente de planilha do Excel, e você verá uma visualização de seus dados aqui e todos os tipos de ferramentas que temos para moldar filtrar, personalizar e transformar esses dados antes de carregá-los no Excel. Então este é como o nosso centro de comando para configurar esta consulta ou conexão. Agora, tenha em mente, não há nenhuma maneira de eu conseguir arranhar a superfície de todas essas ferramentas dentro do escopo desta demonstração em particular. Mas eu tenho um curso inteiro que vai em profundidade nessas ferramentas o Power Query, Power Pivot e Dax curso. Agora, para fins desta introdução, tenha em mente que existem algumas seções importantes desta janela de edição de consulta. Você tem sua barra de ferramentas de edição de consulta na parte superior com opções para transformar ou adicionar novas colunas e uma janela no canto inferior direito chamada Etapas Aplicadas. Agora, esses passos aplicados são mudanças absolutas do jogo, porque essencialmente, o que está acontecendo aqui? é que toda vez que você faz um ajuste ou transformação, qualquer operação que você aplicar à sua tabela é registrada como uma nova etapa aplicada exatamente como etapas são registradas em um V B uma macro. Agora, uma vez que você tenha transformado e moldado seus dados e você está pronto para carregá-lo em excel lá para lugares onde você pode carregá-lo em uma planilha onde ele vai viver em linhas, colunas e células, ou para os dados modelo, onde você pode comprimir esses dados e armazenar muito, muito mais informações centenas de milhões de rosa. E também é onde você pode realmente criar modelos de relações para criar relações de tabela e conectar ou mesclar informações de várias fontes. Então, para resumir rapidamente casos de uso comuns número um conectando a arquivos simples ou fontes de banco de dados e, em seguida, transformando ou filtrando esses dados antes de carregá-los no Excel. Para uma análise mais aprofundada, número dois Criando um processo E T L totalmente automatizado, que significa extração, transformação e carga que poderia ser atualizado com um clique à medida que novos dados ficam disponíveis novamente. Detalhes. Ar tudo coberto na minha consulta de poder, poder pivô e curso Dax. Então, com isso, vamos entrar no Excel. Você vai orientá-lo através de uma demonstração rápida de como podemos usar consulta de energia para conectar e transformar em arquivo CSP externo. Tudo bem, então se você quiser acompanhar, dirija seu índice e procure por esta demonstração poderosa do Corey em nossas dicas de análise roxas , e quando você se conectar, tudo o que você verá aqui é uma folha em branco. Por agora. Agora, para introduzir este tópico de consulta de energia, eu fiz um arquivo de projeto disponível para você chamado Kickstarter projetos ponto CSP. Sinta-se livre para baixar isso para acompanhar ou simplesmente sentar e assistir este show. Eu vou ir para a minha guia de dados aqui algumas maneiras que eu posso pegar um arquivo CSP ou a partir deste a partir de texto barra CSP botão ou no menu get data down, que me mostra algumas das outras fontes de dados também. Este caso eu vou entrar a partir de um arquivo de texto barra CSP. Vou clicar duas vezes no arquivo do Projeto Kickstarter que carrego na minha área de trabalho. Então a primeira coisa que veremos é uma prévia rápida baseada nas primeiras 200 linhas desse arquivo tem nomes do projeto I DS, categorias, categorias principais, então todo tipo de informação sobre quando o projeto foi lançado, quando o prazo é quantos apoiadores, quanto dinheiro foi prometido e assim por diante e assim por diante. Agora vamos querer filtrar esses dados, cortá-los para o que precisamos. Então, vamos clicar no dedo do botão de edição, na verdade, iniciar o editor de consultas e você verá que ele é iniciado em uma nova janela fora do ambiente de planilha do Excel. E é aqui que vamos fazer toda essa modelagem de dados e filtragem e transformação antes que ele seja carregado no Excel e você vai notar que já foi aplicado alguns passos para identificar a fonte do meu basta parar aqui promoveu os cabeçalhos, e é detectado alguns dos tipos de dados aqui e aplicado esses tipos de dados automaticamente. Então, por uma questão de demonstração, vamos adicionar mais alguns filtros aqui. Talvez não queiramos que todos os dados do prazo tenham projetos voltados para 2009, então vamos aplicar uma data, filtrar e dizer que projetos Onley fora daqui. Talvez não queiramos que todos os dados do prazo tenham projetos voltados para 2009, então vamos aplicar uma data, Leitor Adobe que estão depois, Por que não dizemos 2014 para que possamos ir para dezembro 2014 Selecione 31 Pressione OK, nós adicionamos um novo passo aplicado para filtrar aqueles rosa, e podemos classificar essa coluna ascendente assim como faríamos com qualquer tabela do Excel para ver isso. Agora estamos lidando com projetos que só têm prazos que remontam a 1º de janeiro. 2015. Agora a mesma história aqui podemos filtrar por país. Por enquanto, é só olhar para os U. S. Projects. Lá vamos nós. E agora o estado se a campanha foi bem sucedida ou se falhou ou foi cancelado filtro de fornecimento lá também. E em Lee, olhe para projetos bem-sucedidos. Imprensa. OK, agora podemos continuar a continuar. Poderíamos mudar os tipos de dados aqui. Poderíamos remover colunas extras, temos algumas redundantes neste caso. Mas por uma questão de demonstração, vamos em frente e passar para carregar os dados. Vou escolher roupas e carregar a partir do canto superior esquerdo, e isto vai mostrar-me as minhas opções de carregamento. Agora, se eu não quisesse carregar esses dados em uma planilha e realmente armazenar essas subiu essas observações em linhas e colunas, mas eu poderia fazer é Onley criar a conexão e soltá-la no modelo de dados. Não, você pode não ter acesso ao modelo de dados, dependendo da versão do Excel que você está usando. E neste caso, quero reservar a conversa do modelo de dados para uma dica profissional diferente. Então, por enquanto, vamos usar a opção de tabela e realmente carregar esses dados de filtro que criamos aqui em nossa planilha existente, a planilha de consulta de energia e vender um e pressionamos. Ok, vamos ver nossa dor de consulta aparecer e ele diz, Tudo bem, nós estamos carregando alguns dados do projeto Kickstarter CSP. Carregou 41.097 linhas e deixou cair esses dados como uma tabela bem aqui no meu poder. Corrie 10. Vamos em frente e fechar essa dor daqui. Podemos trabalhar com esta tabela como qualquer outro podemos inserir tabela dinâmica, por exemplo. Vamos deixar isso em uma nova folha e guia sobre, e podemos chamar este pivô Kickstarter, por exemplo. E aqui temos nossos campos daquela mesa Kickstarter e podemos trabalhar como uma tabela dinâmica normal aqui. Talvez queira dividir os dados por categoria principal aqui em Rose. E neste caso, vamos olhar para o montante prometido em dólares americanos. E assim como qualquer outro pivô, podemos alterar os formatos numéricos. Moeda poderia ter feito isso no editor de consulta também. E vamos pegar esses projetos estaduais estado como filtros. E lembre-se, nós classificamos isso são filtrados no dedo do pé só mostram projetos bem-sucedidos. É a única opção que temos aqui. Agora isso é muito legal. Podemos agora ver quanto dinheiro foi prometido por categoria. Veja um monte de dinheiro e tecnologia de jogos de design, o que faz sentido. Mas isso não é realmente o que torna Power Query tão poderoso que poderíamos ter apenas carregado que CS sendo copiado e colado em uma guia. O que torna Power query tão poderoso é que, se agora queremos alterar algumas das configurações ou recursos de nossa conexão, talvez não queiramos filtrar os projetos bem-sucedidos do Onley. Talvez queiramos ver os projetos falhados agora. Em vez disso, não precisamos reinventar a roda. Não precisamos filtrar manualmente a mesa que foi lançada em seu poder. Corey Tam. Em vez disso, podemos entrar em consultas de dados e conexões, e podemos clicar com o botão direito do mouse para editar a consulta do projeto Kickstarter e verificá-la. Este último passo, este passo rosa filtrado é onde nós tínhamos filtrado para baixo para país igual a U S eo estado do projeto igual a sucesso. Tudo o que precisamos fazer é clicar nesta pequena engrenagem. Venho editar esse passo, e a segunda linha aqui é o filtro de estado. Clique nesta elipse, exclua esse filtro e pressione OK e ele será atualizado para trazer essas linhas de volta do arquivo CSP original. Agora, quando fecharmos e carregarmos, você verá em nossa dor de consulta que Kickstarter Project Curry está se atualizando. E o que devemos ver é que mais dados vêm aqui em vez de apenas 41 mil. Isso deve mostrar um total maior porque agora estamos incluindo projetos com diferentes estados de projetos. E isso é exatamente o que vemos Agora vemos 127.000 875 linhas carregadas nesta tabela e assim como você esperaria, podemos fechar nossa dor, ir para o nosso pivô, saltar para as ferramentas de tabela dinâmica e atualizar, e agora confira. Quando olhamos para o nosso filtro de estado. Agora, não temos apenas opções bem-sucedidas. Falhamos, cancelamos ao vivo e suspendemos também. E aí está. muito, muito rápida, Demonstração muito,muito rápida, muito em nível de superfície de algumas dessas ferramentas básicas de consulta de energia. Mais uma vez, se você quiser que o conhecimento básico profundo, confira a consulta de poder, Power pivô e Dax curso e vá realmente apreciá-lo. Mas espero que, por agora que pelo menos inspirou você brincar com algumas dessas ferramentas mais avançadas e ver como eles podem ser capazes de revolucionar seu fluxo de trabalho no excel. 9. Modelagem de dados: Tudo bem para esta dica profissional. Gostaria de apresentá-los a um conceito muito poderoso e excelente. Vamos falar sobre como construir uma análise modelos de dados relacionais no Excel agora se destaca. Modelo de dados é usado para um número de propósitos diferentes. Por um lado, você pode comprimir quantidades extremamente grandes de dados centenas de milhões de rosa, se não mais. Em segundo lugar, você pode criar relacionamentos de tabela, que é uma maneira muito, muito mais eficiente e elegante de mesclar dados entre tabelas ou fontes sem costurá-los manualmente com fórmulas próprias. E terceiro, você pode adicionar novas colunas calculadas e medidas usando uma linguagem de fórmula chamada DAX ou expressões de análise de dados. Portanto, existem algumas maneiras de obter dados no modelo de dados. As formas mais comuns de criar uma nova conexão com uma fonte de dados usando uma ferramenta como consulta de energia , também conhecida como obter dados ou obter transformação e carrega diretamente a partir dessa conexão para o modelo. Uma maneira mais simples de adicionar datado ao modelo, especialmente para fins de demonstração, é pegar uma tabela de uma planilha, que geralmente é uma tabela muito menor e clicar no botão adicionar ao modelo de dados dentro do poder guia dinâmica. Agora este é um bom ponto para pausar e informar que algumas versões do Excel não terão acesso a essas ferramentas de modelagem de dados. Portanto, se você não vir sua guia dinâmica de energia, o primeiro lugar a verificar são as opções de arquivo. Adan e procure sua calma Adan especificamente, se você ainda não vê o poder, gire lá. Eu recomendo Googling Onde está o pivô de energia? E você será levado para o site de suporte do escritório que se parece com isso. E aqui você verá exatamente os produtos de escritório que têm acesso a essas ferramentas de modelagem de dados e dinamizar. Agora, supondo que você tenha acesso ao modelo de dados, uma vez que você tenha adicionado suas tabelas ao modelo, você será capaz de gerenciar ou editar sua janela do modelo de dados e ver algumas exibições diferentes como você vê aqui à esquerda, temos a visão do diagrama, caso em que vemos nossas tabelas aparecem como objetos individuais, e é aí que podemos criar as relações reais de tabela entre eles, com base em coisas chamadas chaves primárias e estrangeiras. À direita, temos nossa visão de dados, que é um tipo mais tradicional de layout tabular. Mas a beleza do modelo de dados é que uma vez que essas relações são definidas, uma vez que você criou o que chamamos de modelo de dados relacional, que, para o propósito desta palestra, estou definindo como um grupo de tabelas relacionadas, então podemos inserir uma tabela dinâmica para explorar e analisar os dados em todas essas fontes relacionadas em uma única exibição. E é por isso que chamamos isso de pivô de energia. Ele parece e se sente exatamente como uma tabela dinâmica regular, mas está sentado em cima de um modelo de dados. Casos de uso tão comuns aqui, combinando informações de várias fontes sem realmente misturá-las, mesclando ou usando funções como Look Up ou Index e dois. Essa é a base para a criação de soluções robustas de business intelligence que podem integrar e combinar fontes de dados de todos os tipos de locais, como dados de vendas. Finanças de RH, marketing, etc. Então, com isso, vamos entrar no Excel. Vou passar por uma demonstração extremamente rápida, muito breve para mostrar algumas das ferramentas do modelo de dados e do poder pivô conduzem. Tudo bem, então se você está se sentindo corajoso e gostaria de seguir adiante, vá para o seu índice em sua pasta de trabalho de protesto procurando as dicas do Purple Analytics aqui e vamos pular para a demonstração de quatro estrelas de modelagem de dados Demonstração. Vamos em frente e ligar para essa folha, e o que estamos olhando aqui é uma tabela chamada Transações. Temos a coluna de data de transação A tem o produto que foi vendido e Colin ser algumas informações sobre o cliente com base em um cliente i d. Aqui na coluna C e, em seguida, os valores reais aqui, a quantidade vendida na coluna D Agora em sua própria, esta tabela não é muito útil para nós. Podíamos ligar isto a uma tabela dinâmica e enrolar a quantidade ou as vendas. Sabe, para produtos, clientes ou dias. Mas de que adianta isso realmente nos diz que vendemos o produto número 76 seis vezes, certo? Não temos contexto adicional sobre essas informações. Então, o que realmente gostaríamos Aqui estão algumas tabelas de pesquisa que poderiam potencialmente mapear este produto I d ou este cliente i d duas dimensões adicionais ou informações adicionais que podemos usar para realmente aprender mais sobre essas vendas. E, na verdade, isso é exatamente o que temos. Se expandirmos as colunas do grupo. Verá que temos mais três mesas. Tenho uma mesa verde chamada produtos. Temos uma mesa laranja chamada clientes e uma tabela amarela chamada Calendário. Agora, como você pode esperar, cada uma dessas tabelas inclui uma coluna I D ou, no caso do calendário, uma coluna de data, que pode mapear essas informações de volta para nossas transações. Dados em colunas Athor profunda. Em outras palavras, se conhecemos o produto I D, que fazemos, então, criando uma conexão ou relação com a tabela de produtos, também conhecemos a marca do produto e o nome do produto, bem como o preço de retalho e o custo. mesmo acontece com o cliente i D. Essa chave pode ser usada para obter informações sobre esses clientes os nomes, cidades, países, estado civil, sexo que tipo de associação eles têm. Todas essas informações podem ser vinculadas a esta tabela de transações porque temos as colunas chave que podem conectá-los. Então você pode ser tentado a ir em frente e começar a escrever funções como a pesquisa ou correspondência de índice para amarrá-los juntos em uma única tabela mestre. Como na velha escola, Excel estava acostumado a precisar de uma única tabela ou de uma única fonte para tabelas dinâmicas. Essa sempre foi a grande limitação dos pivôs. Então talvez você digita, você sabe, se você olhar aqui para cima, nós estamos olhando para o produto que eu d dentro da nossa tabela de produtos e agarrando a segunda coluna com uma correspondência exata. E boom, lá vamos nós. Temos a nossa marca de produtos agora na coluna E. Então ligamos essa marca de produto à nossa tabela de transações. Agora escute. Poderíamos continuar este processo para obter o nome do produto, preço de varejo, bem como todas as informações do cliente e todas as informações do calendário também. Mas há dois problemas com esse número um. E se essas tabelas incluíssem centenas de colunas em vez de apenas um punhado como nós temos aqui de repente? Essa abordagem não é bem escalável, e para o que criamos é uma tabela realmente ineficiente com uma tonelada de valores duplicados que simplesmente não são necessários. Então vamos em frente e excluir essa coluna aqui, e eu vou mostrar a vocês quanto mais elegante é a abordagem mais sofisticada para realizar a mesma coisa usando o modelo de dados. Então, eu já adicionei essas tabelas ao modelo de dados, mas se você quiser praticar você mesmo, selecione uma tabela individual, aprofunde seu poder, aprofunde seu poder, gire o tempo e clique neste anúncio para o botão de modelo de dados direito aqui Agora, porque eu já os adicionei, podemos ir para o nosso botão gerenciar que irá abrir a janela do modelo de dados aqui. Estamos em nossa visão de dados. Por padrão, podemos ver essas quatro tabelas em formato tabular como guias separadas e exibição de diagrama, que na verdade mostra cada uma de nossas tabelas como um objeto na tela. Agora você notará que essas linhas conectando as tabelas representam relacionamentos. Então, neste caso, produto I D. que é destacado, é o que conecta produtos a transações. Temos um cliente, i d, que conecta os clientes a transações. E, finalmente, temos nossa data de transação que se conecta ao campo de data em nossa tabela de calendário. Agora, para mostrar como criar esses relacionamentos do zero, indo em frente para a guia de design aqui, gerenciar relacionamentos, e tudo o que precisamos fazer é selecionar esses três relacionamentos, pressione Apague, ok. E quando fecharmos a caixa de diálogo agora, veremos essas tabelas meio desconectadas novamente. E tudo o que precisamos fazer para simples é pegar o campo que queremos e conectá-lo a uma chave primária dentro da tabela de pesquisa. Então data da transação até o produto data i D para o produto i d. Cliente i d para o cliente 18. E é simples que, em questão de menos de 10 segundos, replicamos todo o esforço de criar essas funções de pesquisa ou índice para juntar manualmente os dados. E conseguimos exatamente o mesmo resultado. E agora aqui está a melhor parte. Volto para a guia inicial aqui, insiro uma tabela dinâmica em uma nova planilha pressionada. OK, vamos clicar duas vezes para nomear desta vez algo como pivô de modelo de dados. Aqui está a beleza disso. Agora estamos lidando com um pivô de energia, que é tecnicamente o mesmo layout de tabela dinâmica antigo apenas sentado em cima de um modelo de dados. temos acesso a todas as nossas tabelas de modelos de dados, calendário , clientes, produtos e transações, bem como a outras tabelas na pasta de trabalho, o que, neste caso, não será útil porque não há relação com encontrar entre eles. Então, se aprofundarmos nossa tabela de transações, você verá algumas medidas diferentes aqui que eu defini transações, quantidade total e receita Estas foram projetadas com uma linguagem chamada Dax, que está fora do escopo deste curso, mas é algo que eu faço cobrir no meu parapeito de consulta de poder e curso Dax. Então vamos pegar a quantidade total. Puxe isso para os nossos valores. Aqui e agora podemos acessar qualquer uma de nossas outras tabelas e quebrar essas quantidades todas as maneiras diferentes, como marca de subproduto em Rose, por exemplo, por exemplo, aqui podemos ver que os produtos americanos venderam 1200 atômicos produtos vendidos 695. Podemos pegar um campo da nossa mesa de clientes também, como a cidade do cliente. Arraste isso para dentro e rótulos de função secundária. Marca do produto Pope fora. Então, como você pode ver, estou manipulando esse pivô exatamente como eu manipularia qualquer outra tabela dinâmica no Excel . A única diferença é que agora eu sou capaz de acessar campos em quatro tabelas diferentes que estão conectadas apenas com os relacionamentos que definimos dentro do modelo de dados. Coisas incrivelmente poderosas 10. Funções de CUBE: Tudo bem. Hora de mergulhar em uma dica analítica de cinco estrelas, verdadeiramente especializada. Vamos falar sobre como explorar modelos de dados fora dos pivôs usando algo chamado funções cubo. Agora, duas advertências rápidas antes de mergulharmos. Número um. Se você não visualizou a dica profissional de modelagem de dados ou não aprendeu sobre modelos de dados no passado e o incentivou muito a fazê-lo Número dois, você precisará de uma versão do Excel. É compatível com o modelo de dados e com o pivô de potência. Se você não tiver certeza, vá no Google search for Where is power pivô e você pulará para o site de suporte do escritório que se parece com isso. E aqui você poderá ver as versões exatas do Excel que incluem acesso a esses modelos de dados e ferramentas de dinamização. Agora vamos voltar ao assunto. Então funções de cubo. A maneira mais fácil de explorar dados em um modelo de dados é por meio de uma tabela dinâmica ou de um gerador de energia. Mas o que funções de cubo fazer é permitir que você puxe um recuperar valores filtrados específicos para fora do seu modelo de dados e puxá-los diretamente para as células da planilha. Então, por exemplo, se você quiser criar uma exibição que se pareça com isso. Você pode usar funções de cubo para fazer isso, e na minha experiência, existem quatro tipos de funções de cubo que eu uso quase exclusivamente. O primeiro é chamado de conjunto de cubos, e essencialmente um conjunto de cubos é uma coleção de itens ou membros de cubo do seu modelo. É essencialmente equivalente a uma coluna inteira de uma tabela no seu modelo. Nesse caso, definimos um conjunto de cubos que contém informações sobre marcas de produtos. Agora, dentro de conjuntos de cubos, você tem algo chamado membros do Cubo e estes ar sombreado em azul aqui no membro do Cubo Visual é um único item de dentro de um conjunto de cubos. Então, em outras palavras, é um item fora de uma coluna de tabela aqui. Estamos olhando para valores diferentes dentro da coluna de membros em nossa tabela de clientes, dourado, prata, bronze e normal. E note que também usamos membros Q para definir medidas quantitativas ou medidas calculadas como transações ou quantidade. Em seguida, temos um tipo especial de membro chamado de membro classificado cubo. Estes são sombreados em verde aqui no visual e estes ar, assim como vocês membros, seus itens individuais dentro de um conjunto de cubos. A única diferença é que eles são baseados em uma classificação de ordem. E é isso que nos permite fazer coisas como mostrar os cinco melhores produtos por quantidade, por exemplo, como você vê aqui. E por último, mas não menos importante, temos nosso quarto tipo primário de cubo, que é o valor do cubo, e essas são todas as células sombreadas em amarelo. Lá, os valores numéricos reais que foram agregados com base em um conjunto de expressões de membro. E essas expressões de membro nos ajudam a filtrar esses valores para recuperar os números apropriados . Agora, estes condenam finitamente ser complicado e um pouco desconhecido para trabalhar. No início, vou mostrar-te exactamente como construir uma vista como esta. Mas tenha em mente que existem muitos outros tipos de funções de cubo, bem como, e eu incluí um link para obter mais informações aqui. O que este link curto irá levá-lo para é o escritório Documentação de Suporte Página quatro funções Cubo , que se parece com isso, e aqui você pode percorrer e aprender um pouco mais sobre os fundamentos de Cuba funções e algumas dessas outras opções que eu não estou cobrindo. Portanto, casos de uso comuns aqui para criar relatórios ou painéis baseados em planilha que se encaixam em cima dos dados em seu modelo de dados sem ter que confiar em tabelas dinâmicas e funções de cubo podem ser uma ótima maneira de documentar todos os conjuntos e membros dentro do seu modelo de dados. Então, com isso, vamos entrar em nossa pasta de trabalho de dicas profissionais e praticar a criação de uma exibição de relatório como a que você vê aqui. Tudo bem, então se você está seguindo com o curso, você sabe, o exercício teve que o seu índice rolar para suas dicas de análise roxo aqui, nós vamos perfurar esta demonstração cinco função Cubo Estrela. Vá em frente e ligue para aquele lençol. Agora, o que você está vendo aqui é um modelo em branco básico que eu criei de um relatório de exemplo . Não há fórmulas nem nada aqui. É apenas formato. Ele vende. E nosso objetivo é converter isso e conectado em um relatório totalmente funcional que extraia dados do modelo de dados subjacente. Agora, para começar, precisamos fazer uma revisão rápida do modelo de dados com o qual estamos trabalhando. Então eu vou saltar para a guia dinâmica de energia, clique em gerenciar o modelo de dados, e vamos saltar para a visão de diagrama aqui Então este é o modelo que estamos trabalhando com nesta pasta de trabalho. Temos dados de transação com três medidas calculadas transações, quantidade total e receita. Essas medidas são métricas que vamos colocar em nosso relatório. E também temos uma série de campos que podemos extrair. Essas três tabelas de pesquisa têm campos de calendário como o dia, o mês, trimestre e ano informações do cliente, como países e cidades, sexo e status do cartão de membro e produtos que incluem coisas como a marca, o nome, o preço de venda a retalho e o custo. Então vamos em frente e realmente inserir uma tabela dinâmica do nosso modelo de dados, e eu vou soltá-la em uma pasta de trabalho existente. Vou colocá-lo bem aqui, hum, em nossa planilha de funções de cubo. E vamos deixar isso aqui e vender H dois e pressionar OK. E o que vamos fazer é usar essa tabela dinâmica para ajudar a verificar pontos na fila e certificar-se de que os números que estavam preenchendo com funções de cubo são precisos. E então, uma vez que fizemos isso, podemos ir em frente e excluir o pivô em si e ficar com apenas o relatório personalizado que criamos para que, como você pode ver aqui, temos algumas saídas diferentes dos dados. Estamos olhando para três métricas diferentes. Transações, quantidade e receita. Primeiro, estamos dividindo por mês nos últimos três meses. Abril, Maio e Junho. Então estamos fazendo um detalhamento por tipo de membro dourado, prata e bronze. E esta terceira tabela aqui está mostrando as vendas por produto para os cinco principais produtos com base em transações, quantidade ou receita. Então, vamos começar no topo com as vendas por mês. E eu vou perfurar este pivô, e eu vou puxar em alguns desses campos transações, quantidade e receita. E neste caso, queremos dividi-lo por mês da minha tabela de calendário Agarre o nome do mês puxando para Rose . E neste caso, tudo o que nos interessa é a imprensa de Abril, Maio e Junho. OK, vamos resolver isso um pouco tonto. Então esta é uma pequena visualização da visão que estamos tentando alcançar aqui, puxando esses mesmos valores para as próprias células da planilha. Então, se você lembrar a função de valor do cubo foi o tipo de cubo que pode puxar ou agregar esses valores numéricos. O problema é, se começarmos apenas digitando o valor do cubo. Estamos a abrir os parênteses. Nossa conexão vai começar com uma citação aberta e vai ser este modelo de dados de pasta de trabalho . Você pode pressionar tabulação toe lock que em fechado A aspas Agora o segundo argumento e o terceiro e o quarto e o quinto para uma função de valor de cubo basicamente pedir que você aponte para uma expressão de membro. E essa expressão de membro ajuda a dizer ao Excel como filtrar os valores a serem retornados. Então, neste caso, estamos filtrando com base em duas condições ou critérios diferentes. O nome do mês e ser cinco e vírgula para o nome da medida na célula C quatro. Então isso é realmente tudo que eu preciso para esta função de valor cubo. Eu fecho o parêntese pressione enter, eu entro em N A. E a razão pela qual eu recebo esse erro é porque os valores Q não sabem como interpretar células que apenas contêm texto. Por isso, neste momento, esta cela não significa nada. É apenas uma cadeia de texto que diz transações. A mesma coisa com os meus nomes de meses. Abril, Maio Junho, prata dourada, bronze. Então, o que vamos precisar fazer é converter essas cadeias de texto em funções de membro Cubo reais . Então vamos começar com nossas métricas aqui, podemos digitar igual a membro Cubo aberto Cite este modelo de dados da pasta de trabalho Comece assim toda vez que vem para a expressão do membro e este é o campo ou o item do nosso modelo de dados que nós deseja capturar nesta célula. Então abra outra citação. Isso permitirá que você acesse as tabelas e campos em seu modelo. Neste caso, queremos um campo das nossas medidas. E se entrarmos em um ponto, ele nos leva para o próximo nível na hierarquia, que é a lista real de opções de medida aqui. E queremos transações. Então eu vou marcar, fechar a citação, fechar o parêntese, pressionar Enter . Então agora ainda diz transações. Mas agora este é um membro que real, e nós vamos passar pelo mesmo processo aqui para abril é igual membro Cubo a partir deste modelo de dados de pasta de trabalho e a expressão de membro, lembre-se, abriu a citação novamente. Desta vez ele está vindo do período da tabela de calendário para a lista de colunas que queremos o nome do mês Colin. E agora, porque queremos um mês específico dentro do nome do mês Colin, vamos adicionar mais um período e digitar o nome do mês entre parênteses. Então queremos especificamente o mês de abril e, em seguida, fechar a citação. Feche o parêntese. Sinta-se livre para pausar na tela por um momento para ter certeza de que você tem a função certa e eu vou pressionar, entrar e verificar. Duas coisas acontecem. Número um. Minha célula membro do cue agora tem um valor de abril, que não é apenas texto. É um membro que real, e nosso valor de cubo agora está retornando um número adequado, que é 6588 que podemos mapear para o nosso pivô como um Q. E isso corresponde. Então, estamos no caminho certo. E agora que definimos alguns desses, é muito fácil aplicá-los ao resto de nós mesmos para que possamos pegar essa coluna de transações , copiá-la, colá-la mais duas vezes, e então simplesmente ajustar esse último argumento das transações para a quantidade total e das transações para a imprensa de receita. Digite a mesma coisa com abril, maio e junho. Vou acelerar isto mais duas vezes e mudar a April na discussão final. Dois principais e, finalmente, para junho. E assim como as funções regulares do Excel. Aqui podemos tratar cuidadosamente os nossos tipos de referência porque os nossos meses vivem sempre na coluna B. Podemos corrigir essa referência de coluna. E porque nossos cabeçalhos são medidas sempre vivem na fileira quatro. Podemos corrigir essa referência de linha e pressionar Enter e verificar isso. Arrastado para baixo, arraste e boom! Temos todos os nossos valores Q preenchidos. Podemos verificar contra o pivô. Tudo parece bom. E agora estamos começando a Cruz aqui. Podemos pegar um desses membros do cubo do mês. Podemos colá-lo no modelo de tipo de membro aqui e agora, em vez da tabela de calendário, vamos abrir isso e queremos que ele sente de nossa tabela de clientes. E a coluna que queremos é a coluna do cartão de membro. E neste caso, para o primeiro valor, os membros especificamente que queremos são dourados, então você pode digitá-lo em aspas fechadas, fechar o parêntese, pressionar enter e, em seguida, copiar que mais duas vezes para povoar prata e bronze. Então prata ali e bronze bem aqui e agora dê uma olhada. Copie o valor do cubo, cole-o no Lee. As referências que queremos mover estão se movendo, as outras estão fixas. Eles ainda estão apontando para os cabeçalhos corretos aqui em cima, bem como os membros apropriados para tipo de membro aqui na coluna B. Então vamos em frente e dar. Foi um mês pobre. Nome para fora, cartão de membro completo dentro, e nós podemos apenas tipo de re classificar este um pouco de ouro no topo e prata do que bronze. Use os valores que deveríamos estar vendo. E bum! Esses são os valores que vemos aqui a partir de nossos valores Q. Então somos dois por dois. A última coisa que precisamos fazer aqui é preencher essas vendas por matriz de produtos, que vai puxar os cinco melhores produtos. Então aqui está a pegadinha. Não sabemos de imediato quais são os cinco melhores produtos, então não podemos simplesmente começar a digitar membros regulares do Cube do zero, como fizemos com o mês ou tipo de membro. Agora podemos classificar as diferentes marcas de produtos no pivô para descobrir quais serão os cinco primeiros . Mas se quisermos que essa visão mude dinamicamente e sempre classifique com base nos dados atuais, temos uma abordagem melhor para fazer isso e o que faremos aqui é introduzido um cubo definido primeiro aqui na coluna B 16. Então vamos em frente e digite Cube set function desta vez como sempre, vai começar com a conexão com este modelo de dados de pasta de trabalho. E agora a expressão do conjunto será a lista ou a coleção de marcas de produtos, que você virá da nossa tabela de produtos. Então abra a citação produtos ponto marca do produto. E agora aqui está a chave. A última pequena opção após o ponto eu não quero selecionar tudo, e eu não quero selecionar nenhum item específico. Então eu vou digitar membros, que vai forçar este conjunto de cubos a armazenar todos os possíveis membros dentro daquela marca de produto Colin fechou a citação, e agora precisamos atualizar ou preencher os argumentos adicionais aqui. Então, para a legenda, o que queremos que a célula exiba, vamos fazer marcas. E agora a ordem de classificação. É assim que determinamos como todos os membros classificados do cubo são classificados dentro deste conjunto. Então, neste caso, vamos classificar volumes altos decrescentes no topo. Esse é o número dois. E o que queremos classificar? Bem, podemos escolher qualquer uma dessas medidas. Vamos em frente e escolher transações. E porque isso vem de nossa tabela de medidas e abrir as medidas de cotação ponto transação fechado A cotação. Feche o parêntese, pause a tela por um momento. Certifique-se de que você tem isso para baixo. função tão longa e entraremos nisso. Então sempre ver é a palavra marcas aqui. Mas esta célula contém um conjunto inteiro de membros, e isso vai permitir-nos agora usar algo chamado de membro do cubo para retirar itens individuais desse conjunto. Então confira isso é igual ao membro classificado do Cubo a partir deste modelo de dados da pasta de trabalho. Agora a expressão set é uma referência a um conjunto de cubos, que acabamos de criar aqui em Selby 16 Press. Pagar uma fechadura porque esse conjunto cubo sempre viverá lá. E agora para a classificação, você vai notar que eu criei um pequeno índice de 1 a 5. Lá na coluna A. Esse será o nosso índice de classificação. Então vamos apontar para esse número de classificação, e podemos ir em frente e fechar a função pressione enter, e o primeiro item que vemos é tudo. Se clicar e arrastar para baixo, vemos algumas marcas de produtos diferentes aparecendo Karmanos, Telltale Ebony e High Top. Agora, as questões de que tudo não é uma marca individual. Na verdade, é o total em todas as marcas e que sempre aparecerá no topo, tem um conjunto de cubos. Então a maneira mais fácil de apenas por isso é simplesmente começar com a classificação dois, depois três e quatro de cinco, depois seis. E porque nós definimos uma referência é, bem, copiar esse valor. Colá-lo para baixo, arrastá-lo para baixo, arrastá-lo para cima, e ele está com bom aspecto. Mas vamos fazer um final Que A e R girar um cartão de membro completo para fora perfuração para produtos marca em, e nós vamos classificar essas marcas de produtos descendentes por transações e imprensa. OK, confira isso. Karmanos Telltale Ébano Oi Top Tri Estado. Mesmo exatamente cinco que preenchemos usando membros de classificação Cube e conjuntos de Cubos, e parece que as transações, quantidade e valores de receita combinam perfeitamente. Portanto, o pivô fez seu trabalho e recomenda manter um apenas para exploração de dados rápida e fácil . Mas neste caso, podemos ir em frente e selecionar essas colunas, excluir o pivô, e lá vamos nós. Ficamos com o formato personalizado perfeito e relatório que construímos inteiramente usando fórmulasde cubo fórmulas 11. Simulação de Monte: Tudo bem, eu quero compartilhar uma das minhas técnicas favoritas de Análise Avançada no Excel. Esta é uma dica de nível de cinco estrelas . O que vamos fazer é construir nosso próprio modelo de simulação de Monte Carlo inteiramente dentro do Excel. Sem V B A. Sem macros, Apenas para cima vender fórmulas e tabelas de dados. Agora simulação Monte Carlo. É normalmente usado para prever a probabilidade de algum resultado futuro ou resultados com base em amostragem aleatória repetida. Em outras palavras, simulamos aleatoriamente o mesmo resultado uma e outra vez e outra vez e depois analisamos a matriz resultante de resultados. Então, que melhor maneira de demonstrar simulação Monte Carlo do que construir nosso próprio simulador de roleta no Excel? Assim, a maneira como isso funciona é que você pode colocar um certo tipo de aposta vermelho ou preto, pares ou ímpares, números específicos, e cada aposta está associada a um determinado pagamento. Então, depois de colocar a sua aposta, determinar uma aposta estavam usando correu entre dois essencialmente aleatoriamente ou simular uma rotação da roleta. Neste caso em particular, nós apostamos $10 no vermelho. O resultado é o número 23 vermelho. Então ganhamos 10 dólares. Mas o objetivo não é apenas sentar aqui e girar aleatoriamente uma e outra e outra vez. O que queremos criar é algo assim onde estamos gravando os resultados de uma série de simulações neste caso, 10 rodadas e, em seguida, avaliando esses resultados e resumindo-os com funções básicas de estatísticas . Então aqui estamos calculando o ganho ou perda total, o número de rodadas, o número de vitórias versus derrotas, a média percentual de vitória, retorno por rodada e máximo básico e homens. E a ferramenta que estamos usando toe realmente conduzir essas simulações é supera tabela de dados. Então vamos para nossa guia de dados, entrar em nossas ferramentas de análise e usar a tabela de dados para criar e gravar quantas simulações determinarmos. Agora. Obviamente, estamos usando isso para criar algo divertido e interessante. Mas há uma série de casos de uso muito sérios, muito realistas onde isso poderia entrar em jogo para um simulando aleatoriamente um modelo de negócios milhares de vezes, a fim de entender a probabilidade de algo como um lucro versus um perda ou para construir sobre essa criação de modelos preditivos que realmente podem explicar um determinado grau de incerteza para um ou mais insumos do modelo, como o que as taxas de juro farão no futuro ou como os custos da oferta vão mudar. Então, esta é uma ferramenta de análise e ciência de dados incrivelmente poderosa que podemos jogar e aprender sobre aqui mesmo no ambiente do Excel familiar. Então vamos em frente e pular em sua pasta de trabalho profissional e praticar a construção de algumas dessas simulações. Tudo bem, então se você quiser acompanhar, vá para o seu índice, role até as dicas de análise aqui em roxo e vamos pular para a demonstração de simulação de Monte Carlo. Vá em frente e link direto para essa guia, e aqui vai encontrar o nosso simulador de roleta. Agora, para aqueles que não estão familiarizados com o jogo de Relent, é assim que a mesa se parece. Você tem 36 números, metade preto, metade lido, bem como números verdes zero e duplo zero. Assim, o negociante vai girar uma roda que contém todos os 38 slots associados a cada um desses números. que diz respeito às apostas faras, Noque diz respeito às apostas faras, você pode fazer apostas de várias maneiras diferentes, que você pode ver aqui nesta célula drop-down. Você pode apostar em números individuais, que são os resultados mais incomuns e, portanto, pagamento, o mais alto neste caso, $35 para 1 em sua aposta. Você também pode apostar no 1º 3º de números. 1ª 12 2ª 12 ou 3ª 12 a 1ª 2ª ou 3ª coluna de números. O primeiro meio segundo, metade números pares. Números ímpares, vermelhos ou pretos. Então, como um exemplo simples, digamos que apostamos no preto. Podemos inserir uma certa quantia de aposta, digamos $10. Este pagamento diz-nos que apostar em preto paga 1 para 1. Então, se ganharmos, ganhamos $10. Se perdermos, perdemos os 10 dólares que apostamos. Então, a partir daqui, comprimimos o botão F nove calcular ou cabeça para sua guia fórmula e rapidamente calcular agora botão para simular diferentes rotações da roda. Então, como você pode ver, nós apostamos no preto. Então, qualquer resultado vermelho significa que perdemos a nossa aposta para baixo $10. Qualquer resultado preto significa que ganhamos $10 por causa do oneto. Um pagamento. Agora, o que realmente nos importa é a informação aqui em baixo. Nossos dados de simulação e nossos resultados de simulação tão bem aqui. Este é o lugar onde vamos usar um dedo da tabela de dados, na verdade girar a roda 10 vezes virtualmente e gravar os resultados de cada rotação aqui na coluna C e eu já preencheu algumas funções em Colin F. Para resumir. Esses resultados criaram um intervalo nomeado chamado Spins, e estamos usando um pouco básico para calcular a perda de ganho total. Estamos tomando um máximo de coluna B para o número de rodadas usando contagem se para calcular os ventos e assim por diante e assim por diante. Então vamos em frente e começar com um pequeno número de simulações, apenas 10 rotações. Então o que vamos fazer aqui é selecionar todo esse intervalo de B 15 a C 25 e lembre-se que quando usamos tabelas de dados, uma das células na seleção precisa conter a fórmula que estamos simulando, que neste caso, é o resultado de rotação alojado e vender I nove. Então eu simplesmente referenciei esse resultado aqui em C 15 para que possamos selecionar toda a referência à medida que inserimos nossa tabela de dados. Então vá para sua guia de dados aqui. Vamos para o “e se as opções de análise colocarem a tabela de dados na parte inferior, e aqui temos nossos dois critérios por linha. Entrada em nossa entrada de coluna. Agora nós não temos quaisquer valores variáveis que estavam testando em linhas, então isso obviamente vai ficar em branco. Mas é aqui que as coisas ficam um pouco estranhas. Nós temos valores diferentes aqui em nossa coluna, então precisamos de algum tipo de entrada de coluna. Mas esses valores não se alimentam de qualquer entrada que impacte nosso resultado. Em outras palavras, nós não estamos realmente conectando o número um, dois ou três em alguma entrada de fórmula. Nós só queremos aleatorizar essa fórmula um certo número de vezes e fazer o Excel perceber que tudo o que temos a fazer é referenciar uma célula em branco. Então Selby, 14 está completamente em branco. Podemos usar isso para uma coluna, entrada e pressione OK, e lá vai você. Uma vez feito isso, Excel agora está iterando através desta tabela de dados, você pode ver que ele é criado um único array contendo todos os resultados, e ele é registrado um Siris de resultados, vitórias e derrotas neste caso com base em 10 rodadas diferentes da roda. Então, neste primeiro exemplo, estamos apostando uma aposta de $10 no preto. Nós giramos 10 vezes. Acabamos ganhando $40 porque batemos preto sete vezes. Chegamos a vermelho ou verde, o que significa que perdemos três vezes. Então nossa probabilidade de vitória foi de 70% e nosso retorno médio por rodada foi de US $4. Então vamos em frente e tentar outra simulação simplesmente calculando agora. Ok, desta vez perdemos 20 quando a percentagem era apenas 40% novamente. Agora perdemos 60. Nós só ganhamos duas vezes em 10 rodadas. Então novamente perdeu 60. Perdido, 40 quebrados. Mesmo que o tempo perdeu 20. Então, como você pode ver, há um pouco de variabilidade em termos dessa probabilidade de vitória. Agora sabemos as estatísticas reais. Aqui conhecemos as probabilidades do Rial. As chances de ganhar uma colocação vermelha ou preta na roleta são iguais a 18 resultados vencedores de 38 resultados possíveis, que equivale a 47,4%. E a razão pela qual essa probabilidade de vitória que vemos aqui e vendemos F 19 está pulando tão descontroladamente e nem mesmo realmente mirando nessa verdadeira probabilidade é que estamos lidando com um tamanho amostral muito, muito pequeno. Qualquer coisa pode acontecer em 10 rodadas. Você poderia ganhar 10 de 10 você poderia perder 10 de 10. Então, o que eu gostaria de fazer aqui é repetir o processo não para 10 rodadas, mas para 10.000 rodadas e ver o que acontece agora. Tenha em mente que você não pode simplesmente excluir uma célula individual de sua tabela de dados, que é uma matriz. Você tem que selecionar a coisa toda, começando com C 16 vai controlar a seta Shift para baixo e, em seguida, excluir. E também vou apagar os números do índice de rotação aqui e a pequena dica profissional aqui. Vou selecionar o número um, passar o mouse sobre o canto direito da célula, mantenha pressionado o botão direito do mouse enquanto eu arrastei para baixo e backup. E quando eu lançar, eu vou ter este menu secreto aqui que tem a opção da Siri na parte inferior. E o que eu quero fazer é preencher uma Siris nesta coluna. Eu quero pisar em inteiros de um e eu quero parar em 10.000. Pressione OK, e lá vamos nós. Se eu controlar nossa queda, você verá que ele foi criado um Siri até 10.000 rotações agora mesmo processo de antes. Quando você seleciona esta mudança de controle de intervalo Seta para baixo para pegar todo o intervalo de células Dados . E se a tabela de dados análise, sem coluna de entrada de linha. Entrada é igual a preto Selby 14 e pressione. Ok, e agora ele vai pensar por um tempo porque ele está iterando 10.000 vezes e registrando os resultados e vamos ver o que acontece. Lá vamos nós. Então estamos fazendo a mesma aposta de antes, apostando $10 no Black. A única diferença é que em vez de fazer essa aposta 10 vezes, fizemos essa aposta 10 mil vezes. Foi uma noite muito longa no cassino. E o resultado que vemos aqui é uma perda total de $4980. Você pode ver o entrevistado 10.000 vezes. Ganhamos 4751 vezes e dê uma olhada na probabilidade de vitória. Lembre-se do que dissemos. A probabilidade real para esta aposta é de 47,4%. Aqui vemos um resultado de 47,5, e se formos em frente e gastá-lo mais uma vez, batendo , calcule agora , lembre-se, vai levar alguns segundos, geralmente 10 segundos ou mais para realmente correr a simulação e aí você tem. Então, 47.6 desta vez perdemos $4900. E vamos fazer mais um cálculo. Dê-lhe alguns segundos para correr. Lá vamos nós. Nosso terceiro resultado, só perdemos 3400 dessa vez. Tivemos uma probabilidade de vento maior do que o normal, mas ainda muito, muito próxima da porcentagem verdadeira ou chances verdadeiras de ganhar. Então o que eu realmente vou fazer aqui é selecionar esse intervalo inteiro aqui e excluir essa tabela de dados só porque eu não quero abrandar as coisas aqui e vamos em frente e apenas excluir esses valores de índice para 10.000 e estamos tipo de volta onde começamos. A partir daqui, sinta-se livre para brincar. Experimente diferentes tipos de apostas, experimente diferentes valores de apostas, simule diferentes números de rodadas. É totalmente com você daqui. Mas esse é o seu curso básico. Na criação de um modelo de simulação Monte Carlo no Excel 12. Otimização avançada com Solver: Tudo bem. Estou tão animado para compartilhar essa próxima dica profissional com você. Esta é uma dica analítica de cinco estrelas, verdadeiramente especializada. Vamos falar sobre a otimização de modelos complexos usando o solucionador. Agora seus solucionadores projetados para fazer é trabalhar com problemas de otimização complexos do mundo real que exigem múltiplas entradas ou variáveis de decisão sujeitas a um determinado conjunto de restrições. Agora considere um caso como este. Você estava olhando para uma matriz de transporte que nos diz o custo de enviar mercadorias entre quatro fábricas diferentes Boston, Nova York, Chicago e Oakland, e quatro centros de distribuição diferentes Miami, Dallas, Seattle e Baltimore. E vamos passar por isso em profundidade quando saltarmos para o Excel. Mas a linha de fundo? O objetivo aqui é descobrir quantas unidades de produto devem ser enviadas de cada fábrica para cada centro de distribuição, a fim de minimizar nossos custos totais de envio. Agora, não é assim tão simples, porque no mundo real temos restrições e limitações também. primeiro lugar, precisamos atender todos os pedidos exigidos neste caso, 6000 pedidos totais, e dois, não podemos exceder o estoque disponível em cada fábrica específica. Então, para um problema como este. Nós realmente não podemos usar ferramentas mais simples como ouro Seek, que exigiu uma única variável de entrada ou decisão e um único resultado concreto. Neste caso, precisamos usar solucionador porque o solucionador nos permite minimizar, maximizar ou direcionar um valor objetivo. Ele nos permite alterar várias células de entrada ou variáveis de decisão, e nos permite determinar restrições específicas também. Então vamos fazer aqui a cabeça para a nossa guia de dados e clicar no botão Solver para iniciar a caixa de parâmetros do Solver. Agora, se você não vir solucionador em sua cidade de dados, lembre-se de que a maioria das versões do Excel half resolver como um plug in integrado, mas, por padrão ele geralmente não está habilitado. Então, você pode precisar ir para as opções de arquivo Adan e, em seguida, aprofundar o seu Excel. Adicionar ins para plug batting. Agora, olhando para esta caixa de diálogo aqui, há três parâmetros que precisarão determinar o número um é nosso objetivo. Neste caso, nosso objectivo vive na célula E 23. É nosso custo de envio, e nosso objetivo não é definir esse custo de envio para qualquer valor específico. É para minimizar o número dois. Nós determinamos exatamente quais células variáveis ou variáveis de entrada alterar para atender aos nossos objetivos. Então aqui estão as variáveis de decisão vivem nas células D 16 30 19 E por último, mas não menos importante, precisamos otimizar sujeitos a um conjunto específico de restrições. Então, neste caso, não podemos enviar frações de unidades, então somos unidades. Necessidade de ser números inteiros são inteiros. Não podemos exceder o estoque disponível em cada fábrica, e precisamos de cada centro de distribuição para atender a toda a sua demanda. Agora uma nota muito rápida sobre a resolução de métodos, que você verá logo abaixo dessa janela de restrição. Existem três opções diferentes que você pode escolher aqui. Não vou entrar nos detalhes. É bem fora do escopo deste curso, mas regra de ouro que você vai usar simplex LP para qualquer otimização linear simples é como problema que estamos lidando aqui e g r g ou evolutivo para uma otimização mais complexa, não linear. A grande diferença entre esses dois é que G R. G é muito mais rápido, mas pode não fornecer a solução ideal global. A evolução é mais provável que forneça essa solução global, mas leva muito mais tempo para calcular. Então, agora que definimos nosso objetivo nossas variáveis de decisão e nossas restrições podem ir em frente e clicar salt e conferir. O Excel produzirá ou preencherá todos os valores nessas variáveis de decisão para minimizar nossos resultados. Custo de envio. Então aqui vemos que estamos enviando 225 unidades de Boston para Miami, venerando 975 de Nova York para Dallas, 500 de Oakland para Seattle. E no final do dia, estamos atendendo a demanda que venera todas as 6000 unidades não estavam excedendo nosso estoque em cada fábrica, e nosso custo total de envio chega em pouco menos de US $97.000. Então, como você pode ver, ferramentas incrivelmente poderosas casos de uso comuns assim como vemos aqui determinando resultados ótimos sujeitos a restrições do mundo real, coisas como inventário limitado como preços baixos, como valores inteiros, etcetera para que vamos pular em uma pasta de trabalho pro tip e resolver esse problema de otimização. Passo a passo. Tudo bem, então se você estiver acompanhando, dirija seu esquilo de conteúdo de tabela todo o caminho até suas dicas de análise, e nós vamos vincular a nossa demonstração do solucionador aqui e aqui, você verá a matriz de transporte que que conversamos no slide. Mas antes de eu mergulhar, apareça na sua guia de dados e procure o botão do solucionador. Geralmente é todo o caminho à direita. Se você não vê-lo, teve que arquivar opções de Adan e, em seguida, a partir deste menu suspenso, você vai querer gerenciar seu Excel, adicionar ins e pressionar Go, e você deve ver Solver Adan nesta janela aqui. Basta colocar uma marca de seleção nessa caixa. Pressione OK e você verá esse pop-up na tentativa de data. Agora voltando ao nosso problema aqui. O que temos nossos custos de transporte, o custo real para enviar uma única unidade de nossas fábricas para nossos centros de distribuição. Isto é baseado na quilometragem real, certo? Então, o envio de Boston para Baltimore é muito barato, apenas cerca de US $4 por unidade. Mas o envio de Boston para Seattle é muito, muito mais caro, mais de 30 dólares por unidade. Agora também estamos lidando com duas restrições diferentes. Aqui temos restrições de fornecimento, que é o limite para o número de unidades que cada fábrica pode fornecer. Então Boston tem atualmente apenas 1000 unidades em estoque. Nova York tem 2000 de Chicago 2500 Oakland só tem 500, então não podemos enviar mais unidades do que uma determinada fábrica tem no inventário. Então isso é restrição número um ou restrição de fornecimento. Temos também restrições à procura. Você está listado aqui em Rhode 10, que diz que Ok, Miami Onley precisa cumprir 1250 pedidos, então não faz sentido enviar mais unidades para Miami. E, de fato, não podemos enviar menos unidades para Miami porque precisamos cumprir esses pedidos. Precisamos colocar esses produtos nas mãos dos clientes que os encomendaram. Então temos que levar 975 para Dallas. Tenho que conseguir 3200 e 52 clientes na área de Seattle e assim por diante. Então essas são as nossas restrições de demanda. E o ponto final é que há 6000 unidades que estamos olhando para enviar aqui como parte deste modelo. Agora, se saltarmos abaixo da matriz de transporte, teremos nosso objetivo ou nossos resultados. Então o objetivo aqui novamente é preencher esses valores aqui em D 16 a G 19. Essas são as variáveis de entrada ou decisão que serão alteradas para avaliar ou calcular o custo total de envio. Então veja o que acontece se ligarmos alguns números. Digamos que enviemos 100 unidades de Boston para Miami, 50 de Nova York para Dallas, 350 de Chicago para Seattle e 25 de Oakland para Baltimore. Como podem ver, estamos calculando o número de unidades enviadas por fábrica. Vamos nos certificar de que esses números não excedam essas restrições em I 5 a 8. Também estamos calculando o número de unidades recebidas por cada centro de distribuição. Quero ter certeza de que esses números não excedam ou entrem sob os que estão aqui em D 10 até G 10. Eles têm que ser exatamente iguais no final do dia, porque nós temos que atender esses pedidos e, em seguida, por último, mas não menos importante, nossa célula de custos totais de envio. Este é o objetivo da linha de fundo que nos preocupamos é um simples alguma função do produto que basicamente leva todas as unidades vendidas ou enviadas de cada centro de distribuição da fábrica, multiplica-los por seus respectivos custos e, em seguida, resume o total para cuspir esse custo total de envio. Neste caso, para as 525 unidades que acabamos de ligar é uma demonstração que custaria um pouco mais de $10.000 para enviar. Então sabemos o nosso objectivo. Conhecemos nossas variáveis de decisão, e conhecemos nossas restrições. Está na hora de resolver isto. Vamos em frente aos dados, clique no Solver e aqui você verá seus parâmetros do solucionador. Então, as primeiras coisas primeiro. Qual é o nosso objetivo? Bem, objetivo é que os custos de transporte vivem aqui e vender E 23. E não queremos definir esse custo de envio para nenhum valor específico, embora pudéssemos, se quiséssemos. E certamente não queremos maximizar o custo do transporte. Isso seria uma loucura. Queremos minimizar nossos custos de envio e vamos fazê-lo alterando um certo conjunto de células variáveis ou variáveis de decisão que vivem aqui mesmo em Green de 16 30 19. Lá vamos nós. E a única coisa que precisamos fazer agora é definir nossas restrições e elas serão algumas restrições diferentes. Vá em frente e adicione o primeiro. Nossa primeira restrição, que é simples, mas é importante acrescentar aqui, é que não podemos enviar frações de produtos, certo? Temos que enviar unidades inteiras ou quantidades. Então sabemos que nossas variáveis de decisão aqui de 16 30 19 tem que ser I anti, o que indica que ele deve ser um inteiro então pressione. OK, lá vamos nós. Essa é a restrição número um. Vamos adicionar mais dois. Primeiro será nossa restrição de fornecimento, que significa que nossas unidades enviadas para fora, que vivem aqui em I 16 19 unidades por fábrica tem que ser menor ou igual ao número de unidades que cada fábrica tem disponível. E note que eu poderia fazer isso em uma etapa selecionando tipo de todo o array de quatro células. A alternativa seria transformar isso em quatro restrições diferentes. Você sabe onde eu 16 é menor ou igual a I cinco Eu 17 este menor ou igual a I seis. Mas podemos fazê-lo em uma etapa selecionando todas as quatro células ao mesmo tempo. Então, de novo, isto diz que não podemos enviar mais unidades do que uma fábrica tem Chris OK? E então nossa terceira restrição é nossa restrição de demanda. Então temos que atender toda a demanda para cada centro de distribuição. Então as unidades recebidas por esses centros de distribuição vivem aqui mesmo em D 21 3º G 21. E neste caso, não podemos estar mais ou menos. Temos que ser exatamente igual ao número de pedidos exigidos ou unidades exigidas para cada um desses locais que vivem aqui e vende D 10 a G 10 Press OK, e estamos prontos para ir. O método de resolução vai ser a opção simplex LP. Uma vez que este é um caso de otimização linear e confira. Tudo o que precisamos fazer agora é clicar resolvido. E quase instantaneamente recebemos esta caixa de resultados que diz que o solucionador encontrou uma solução. Todas as restrições e condições óptimas da alidade são satisfeitas e note que ele conectado nos valores aqui em nossas variáveis de decisão e cuspir que os custos totais mínimos de envio , que neste caso é $96,687 e 75 centavos. Então vamos em frente e manter essa solução solucionadora. Observe que podemos salvar esses valores como um cenário em nosso cenário, gerente ou podemos simplesmente pressionar OK e lá você tem isso. Otimizamos esse desafio logístico complexo determinando exatamente quantas unidades devem ser enviadas de quatro fábricas diferentes para nossos quatro centros de distribuição diferentes. E lá você tem uma demonstração rápida de como usar a ferramenta Excel Solver para resolver esses tipos de problemas de otimização do mundo real mais complexos. 13. Ferramenta de pak (Preview): Tudo bem, Esta próxima dica profissional é um pouco de um teaser, uma espécie de pré-visualização de algumas maneiras diferentes que você pode explorar seus dados usando algo chamado o pacote de ferramentas de análise. Assim, assim como o solucionador, o pacote de ferramentas de análise é um plug-in do Excel integrado que é projetado para suportar uma gama de ciência de dados mais avançada e complexa e ferramentas e métodos estatísticos, coisas como análise de variância e sobre regressão de co variância. Testes T assobiaram um gramas e assim por diante. Agora, obviamente, eu não tenho tempo para mergulhar em cada um desses métodos específicos. Por sua vez, cada um deles merece uma revisão muito mais profunda e abrangente. Mas o que eu vou mostrar a vocês é o escopo de opções disponíveis neste pacote de ferramentas, bem como algumas que podemos usar imediatamente que são mais simples, mas não menos poderosos. Então, nesta demo, vamos estar olhando para uma amostra de cerca de 5000 atletas olímpicos juntamente com suas idades, alturas e pesos. E o que podemos fazer é perfurar nossa guia de dados e procurar o botão de análise de dados. Se você não vir isso provavelmente significa que você só precisa ativá-lo. Adicionar em. Então vá em frente para as opções de arquivo. Adan e gerenciar o dedo do pé do seu Excel Adan. Ative esta ferramenta em sua faixa de opções. E uma vez que você clica na análise de dados, você verá uma lista de rolagem de todas as suas opções diferentes aqui, algumas das quais são simples. Alguns são bastante avançados. Agora, pelo bem da demonstração, vamos falar de duas opções aqui. Primeiro, vamos olhar para uma matriz de correlação entre nossos três campos, idade, altura e peso. E daí vamos gerar algumas estatísticas descritivas para calcular coisas como o modo médio. Variantes variadas contagem inclinação homens nous e valores Max e assim por diante sem ter que digitar uma única fórmula. Então, essa será uma ótima maneira de realmente descrever e entender nossos dados com o clique de um botão. Casos de uso tão comuns aqui, assim como eu mostrei a vocês, gerando essas estatísticas descritivas sem ter que usar fórmulas e explorar ou analisar dados usando esses métodos mais avançados de ciência de dados ou estatísticos como analisar variantes, construção de modelos preditivos, etc. Então vamos saltar para o Excel e tomar este pacote de ferramentas de análise para um test drive. Tudo bem, então vá em frente e abra sua pasta de trabalho de dicas profissionais role no índice para suas dicas de análise Purple e vamos pular para a demonstração de visualização embalada da ferramenta de análise aqui . E quando nos conectarmos, veremos nossa lista de atletas olímpicos novamente. Temos uma amostra de cerca de 5000 atletas aqui. Estamos olhando para idades, alturas e centímetros e pesos em quilogramas. Então este seria um bom conjunto de dados para praticar algumas dessas opções de pacote de ferramentas de análise. Primeiro, vá para a guia de dados à direita. Este é o lugar onde você deve ver seus plugins ativados. Se você não vir a análise de dados, vá em frente para as opções de arquivo. Você vai gerenciar a imprensa do seu Excel Adan Go. Você deve ver a análise Tool Pack nessa lista. Vá em frente e marque a caixa e pressione OK. Isso deve aparecer aqui e vamos em frente e ver o que temos disponível para nós. Então, à medida que percorrer, veremos uma série de opções diferentes aqui. Análise de variância, correlação, co variância, suavização exponencial, números aleatórios de média móvel , testes T, testes T e alguns outros. Então, obviamente, um tipo de gama de nível de dificuldade e complexidade aqui. Algumas ferramentas simples, algumas que são bastante avançadas. Neste caso. Vamos manter algumas opções básicas que você pode utilizar imediatamente, começando com estatísticas descritivas. Então vamos em frente e pressione OK. Aqui, vamos ver uma caixa de diálogo bastante intuitiva diz. Muito bem, qual é a sua gama de valores de entrada? Bem, eu gostaria de explorar ou analisar todas essas três colunas, então vamos selecionar B a D. Estas são organizadas em colunas, então isso é bom. Nós temos cabeçalhos ou rótulos em nossa primeira linha. E onde queremos deixar essa saída? Bem, vamos colocar o canto superior esquerdo bem aqui e vender Fum. E vamos também incluir o caso maior e menor, um valor, basicamente uma maneira chique de dizer o máximo e os homens. E acho que isso deve bastar. Não precisamos nos preocupar com o nível de confiança para ser mau. Neste padrão de 0,95% é muito bom. Vamos avançar e pressionar OK e olhar para isto. Vai cair em todas essas estatísticas resumidas bem aqui nas colunas, F a K, literalmente em questão de segundos. Eu penso sobre quanto tempo isso teria levado para produzir todos esses valores com fórmulas auto . Isso significa que as medianas, variantes de desvio padrão teriam levado algum tempo e foram capazes de apenas produzir todos esses valores, além de alguns mais em questão de segundos. Então olhando para ela, Max e homens parece que temos um atleta que tinha 65 anos, é o mais velho. Nosso atleta mais jovem tinha apenas 12 anos em termos de altura. O atleta mais alto, 216 centímetros. E para aqueles de vocês que não estão no sistema métrico, isso é cerca de sete pés de uma polegada. E podemos confirmar simplesmente filtrando nossos dados de origem aqui. Vamos classificar descendentes por altura. E aqui vemos e vestimos Glina da Kiss, que é uma jogadora de basquete nacional grega, que não deve ser uma surpresa, dado o quão alto ele é e, em seguida, olhar para pesos parece o nosso maior. Na verdade , foram 160 quilos. Nosso menor tinha apenas 35 quilos e a mesma história aqui. Vamos classificar esses pesos para cima, e você pode ver que é como Oh Soo Ihara, que é uma ginasta japonesa. E novamente, para essas pessoas não métricas, 35 quilos é um cabelo acima de £77. Então esses ar são estatísticas descritivas muito, muito úteis, mas também bastante simples de produzir. Vamos seguir em frente e fazer mais um exemplo aqui, volta às nossas opções de análise de dados. É hora de eu querer olhar para as correlações ou relações entre essas três colunas. Então, o mesmo rancho de entrada aqui. Seja 30. Certo, as colunas de ar de novo. Temos cabeçalhos de rótulos na primeira linha, e desta vez vamos deixar nossa saída de correlação abaixo de suas estatísticas bem aqui em F 20. E é simples. Está bem? E lá vamos nós. Vamos em frente e formatá-los para que seja um pouco mais legível como porcentagens. E veja isso. Temos esta matriz de correlação produzida aqui. Você verá 100% tipo de na diagonal porque cada campo está 100% correlacionado com ele mesmo. Mas os que realmente nos importamos aqui são esses dois valores, bem como este aqui. Só vou deixar esses ousados. E basicamente, o que isso nos diz é que não há muita correlação ou relação entre idades e alturas. apenas cerca de 11% de correlação, que pode parecer um pouco surpreendente no início até você perceber que não estamos olhando para toda a população. Não estamos olhando para alturas até dois recém-nascidos ou bebês. Estes são todos bem, na maior parte, atletas adultos. Então, uma vez que você alcança a idade adulta, você não está crescendo mais. Portanto, não é surpreendente que vejamos uma correlação relativamente baixa lá, e essa mesma história é válida para a idade e esperar pelas mesmas razões. Agora o interessante é que vemos uma correlação muito clara e muito forte de 78% entre altura e peso, que também faz sentido porque atletas maiores e mais altos tendem a pesar mais. Então lá vai você. Essa é uma maneira muito rápida de usar ferramentas como correlação e estatísticas descritivas para explorar e aprender mais sobre seu conjunto de dados sem precisar usar nenhuma ferramenta ou fórmulas extravagantes. E novamente, eu não vou mergulhar em outro tipo de ferramentas mais complexas dentro desse pacote de ferramentas de análise . Mas eu encorajaria você a explorar essas opções e tentar jogar com essas ferramentas por si mesmo. Então, lá temos. Primário rápido, Visualização rápida do pacote de ferramentas de análise