Excel: simulação e análise de cenários

Pequenas boas práticas - Introdução

Tudo bem, pessoal? Meu nome é Guilherme Silveira, e nesse curso a gente vai trabalhar com Excel, funções, funcionalidades, gerenciador de cenários, testes de hipótese, e até um gráfico que a gente precisa dar uma olhada para entender o que está acontecendo com os dados de uma empresa fictícia.

Então o que a gente vai fazer é, a gente vai importar uma série de dados, vamos trabalhar com esses dados importados, dar nome para eles, usando funcionalidades de nomenclatura, de dar nomes para valores, para colunas, para os dados em si, vamos ver como a gente pode atualizar os dados automaticamente quando nossa fonte de dados é alterada, a gente vai ver como executar algumas funções condicionalmente, que são importantes para as análises em geral, que a gente faz de modelos, vamos criar algumas simulações e um modelo para o crescimento de uma empresa, dessa empresa fictícia, e o que a gente vai fazer é: dado esse modelo, assumir algumas características, o que a gente chama de premissas.

A gente vai assumir que, por exemplo, a venda média é de 150 reais, e que o crescimento mensal das vendas é de 5%.

E isso vai gerar um cenário para a gente, um cenário da minha empresa, essa empresa que eu estou analisando, com o passar do tempo.

Claro, se eu assumisse um outro valor de venda, um outro valor de crescimento, é um outro cenário.

Então dado o meu modelo, e as variáveis que eu tenho nesse meu modelo, a gente vai ver diversas simulações que a gente pode criar e analisar esse nosso modelo.

Entender como o modelo dessa empresa é influenciado por essas variáveis. A gente tem aqui, por exemplo, uma tabela de dados. A gente analisa duas variáveis, duas dimensões, como esse cenário é influenciado por essas duas variáveis.

A gente tem outra maneira, que é utilizando o gerenciador de cenários do próprio Excel.

Ou ainda, usando funções próprias nossas como índice e correspondência, correspondente ou corresp, para limitar os cenários através de uma seleção que a gente pode fazer aqui também dentro do próprio Excel, e entender os resultados dessa seleção.

Vamos ver se tem algum erro dentro do modelo, analisando uma função que nós mesmos criamos, um código que nós mesmos criamos, de erro, uma célula de verificação de erro.

E vamos ver, por fim, como atingir metas dado o meu modelo, que eu criei, que é para atingir a meta financeira da empresa, atingir a meta de atendimento por hora da empresa, atingir a meta que for, numérica, de uma empresa, ou de um modelo que eu tenho, como é que eu posso tentar alterar o valor de uma variável para atingir essa meta? A gente vai ver também como o Excel pode ajudar a gente com isso.

Isso é, nesse curso, a gente vai pegar os dados de uma empresa fictícia, que a gente vai carregar no Excel, trabalhar com eles, atualizar eles, gerar um modelo, baseado em algumas ideias de uma empresa, e aí, baseado nisso, a gente vai trabalhar simulações de cenários futuros para essa empresa.

Claro, como eu disse, você pode utilizar isso na parte financeira, na parte de atendimento, de SLA, seja lá do que for.

Se você tem um modelo dentro do Excel, com várias contas, e no final você tem algum objetivo a atingir, você tem algum objetivo que você quer acompanhar, todas essas funcionalidades vão te ajudar a entender como o seu modelo está funcionando com a sua empresa, ou seja lá o que for que você trouxe aqui para o Excel, está funcionando.

Legal? Vamos ver esse curso, e vamos começar essa análise?

Pequenas boas práticas - Importando dados e fórmulas condicionais

Tudo bem, pessoal? Vamos então começar o nosso curso.

Eu quero analisar alguns dados, gerar alguns modelos, e algumas simulações, baseadas em dados dentro do meu Excel. Esses dados vêm do departamento financeiro da minha empresa. Então eu vou puxar alguns dos dados que o departamento financeiro me enviou.

Esses dados vieram em um arquivo chamado dados.csv. Esse arquivo, você vai baixar durante as atividades deste curso.

Beleza, eu tenho o arquivo dados.csv, e eu estou no meu Excel. Eu vou criar uma nova pasta de trabalho em branco, e aqui dentro eu quero trazer os dados do dados.csv.

Csv é de um arquivo separado por vírgula, e por mais que seja parecido, SV não é de separado por vírgula. É em inglês essa abreviação, comma, que é vírgula, que é o C, separated, de separado, values, de valores. Então separado por vírgulas.

Beleza, então eu quero abrir este arquivo. Se eu quero abrir este arquivo aqui dentro da minha planilha, eu vou importar dados de fora para dentro. Então eu venho em dados, e eu quero obter dados, de texto ou CSV, ou eu posso vir por aqui, obter dados, arquivo, texto CSV. E aqui está o meu arquivo, dados csv.

Repare que ele até tenta mostrar para mim um pouquinho do arquivo. Olha, o arquivo tem a coluna preço, aqui bonitinha, separado por vírgula, a categoria da minha venda, e a data em que ocorreu essa venda. Então eu tenho diversas vendas, feitas em datas diferentes, para um cliente de uma categoria PF ou PJ. O que é PF ou PJ? PF ou PJ é uma separação tradicional que o pessoal costuma fazer em análise de vendas, que é quando você vende um produto para pessoas como eu e você, que somos pessoas físicas, que existimos no mundo físico, ou para pessoas que são criadas, que são as pessoas jurídicas, que são as pessoas que são empresas, e órgãos similares. Então empresas, o governo, etc., costuma ser analisado como pessoa jurídica, PJ, e pessoas como eu e você, pessoa física, PF. Então está separada a venda por uma categoria.

Legal, vou importar. Ele tenta importar, e mostra antes de importar esses dados o que ele iria importar. Parece que a coluna preço está ok, categoria ok, data ok. Aqui não veio direitinho o preço, por quê? Porque como é um arquivo de texto, todo arquivo de texto tem um formato interno, e esse formato interno, no coreano é uma coisa, no Japão é outra coisa, no Brasil é outra coisa. Cada um usa uma maneira diferente de representar o próprio alfabeto. A gente tem o cedilha no português, o alemão tem o umlaut, e o japonês tem o kanji, e o coreano tem o hanja, e cada um tem um tipo de caractere diferente. Como adivinhar em qual desses conjuntos de caracteres foi escrito o arquivo? Ele tenta adivinhar, ele tentou razoavelmente bem, chutou aqui que era europeu ocidental, mas errou, porque a gente usa cedilha, e não pegou o cedilha. No meu caso, eu tentaria aqui o latino, que é muito parecido com a gente, se você for procurar aqui, você vai encontrar o latino três ou nove. Três não deu certo, o nove também não deu certo. Se não, o que costuma ser, que aí é um padrão mais uniforme, tentando atingir o mundo inteiro, é um padrão chamado Unicode, que está aqui no finzinho, unicode. Tem vários Unicodes, o mais comum é o UTF-8. Então vou pegar, maravilha. É o mais comum, funcionou. Preço.

Então é um problema bem comum na hora da gente importar arquivos, dados de arquivos de texto, escolher aqui o tipo de codificação que foi usado para o arquivo, dependendo da língua, do país, é um tipo diferente. Ele chuta, o Excel chutou, chutou errado. E aí a gente acertou para ele. Então, UTF-8.

Carreguei. Maravilha. Olha que lindo, já trouxe os dados, já colocou filtragem para a gente, ordenação, já deixou lindão esses dados para a gente. Já adicionou aqui um conjunto de dados, que ele chamou: olha, dados. Opa, deixa eu fechar aqui. Ele chamou esses caras de dados. Inclusive, eu consigo clicar e tenho a seleção desses meus dados. Maravilha, está funcionando.

O que eu, Guilherme, quero fazer com esses dados? Primeiro, uma análise simples. O que eu quero é mostrar a média das minhas vendas. Então a média, média a gente conhece, é a média, ou average, em inglês. Seleciono essas minhas linhas, beleza, selecionei. Selecionei, e imprimimos a linha, a ordem aqui, ou a fórmula. 42328. Essa é a média. Não é, né? Você vai falar: "Guilherme, você errou na hora que você tossiu aí. Você esqueceu da linha 47". E eu esqueci mesmo, a 46, esqueci, desculpa. Vamos corrigir então, igual a méda, disso tudo. Beleza, fechei os parênteses e enter. Agora eu tenho o valor certo, 42091.

Mas, calma aí, olha o que ele mudou. Repara, antes, quando a gente escreveu na unha um valor específico, por exemplo, média só desses linhas aqui, ele deixou, média de A2 até A11. Mas na hora que eu coloquei a média de toda essa coluna, de todos os valores que estavam dentro dessa coluna, o próprio Excel foi mais esperto. Ele falou assim: "Guilherme, essa coluna aqui, A2 até A47, é um nome interessante, mas melhor do que sair falando: 'pessoal, você viu o que está no A2 a A47?', meio estranha essa conversa", então em vez de falar essa conversa que é super abstrata, a gente chama A2 a A47 de dados, que é o nome dos dados que a gente trouxe, lembra, lá do CSV? Dados, preço, que é a coluna preço.

Então repare que quando a gente importou os dados, ele já criou um dados, um conjunto de dados chamado dados, que é o nome do meu CSV, claro, e dentro desse conjunto, se eu usar aqui o colchetes preço, ele representa todos os preços. Win. Muito mais fácil deu visualizar essa fórmula. Antes era média A2 a A47. Por que dois, por que 47, de onde vem essas coisas? Doideira. Agora não, agora é isso, e eu estou feliz e contente. A2, A47, não. É média dos preços.

Funciona, mas não é bem o que eu estava interessado. É interessante importar arquivos para o Excel, faz parte do nosso dia a dia, faz parte do dia a dia utilizar uma coluna inteira pelo nome, só que no nosso caso, o pessoal do financeiro aqui de estratégia, está tentando entender como as vendas funcionam, para pessoa física e jurídica separadamente.

Então o que eu quero fazer é, as vendas para pessoa física, qual é a venda média para pessoa física? Média tem todos os seus desafios, todos os seus problemas de média, mediana, moda, etc., mas é o que a gente está querendo analisar aqui. Então a média de pessoa física, olha, vou selecionar aqui tudo o que é pessoa física, até aqui.

Média da pessoa física. E a média da pessoa jurídica, é a média daqui em diante. Está duro hoje, né? Então a média daqui, até o fim.

Se eu fui rápido o suficiente, fiz um truque de mágica. Então eu tenho as duas médias, do A2 até A10, e do A11 ao A47. Parece ok. Parece que pessoa física vende 24 mil e pessoa jurídica, 46 mil em média. Será mesmo?

Dá uma olhadinha aqui na maldade, olha o que é a maldade no coração. A maldade no coração é: aqui embaixo, na linha 34, eu tenho uma pessoa física. Ninguém falou que isso daqui estava ordenado por categoria. Ninguém falou que estava ordenado por data. Ninguém falou que estava ordenado por preço. Não teve essa conversa, a gente não fez essa conversa, porque não está. Ponto final. De repente eu posso ter uma venda de pessoa jurídica, de repente eu posso ter uma venda de pessoa física. Não dá para saber, não tem como saber isso. Está aqui numa tabela de dados jogados. Então essa fórmula que eu usei aqui, não funciona, só sair calculando a média. Inclusive, olha, está 24 mil, porque eu esqueci aqui, olha.

Esse caso aqui dessa venda, quer ver, eu esqueci uma. Esqueci essa. Olha qual era a média real, 22 mil. De 24, caiu para 22. Dez por cento de diferença.

Claro, os dados que a gente gerou foi de propósito para gerar essa diferença, mas o que eu quero dizer é que não dá para trabalhar dessa maneira. Até porque, eu não sei se daqui a pouco vão colocar uma nova venda aqui no meio, porque não está ordenado, não tenho como saber.

Beleza, o que eu quero então? Então eu quero essas médias de verdade, não as médias falsas que eu criei aqui, e cada uma dessas médias meio que têm uma condição. Ela é, se for pessoa física, ou se for pessoa jurídica. Então o tipo de fórmula que eu quero é uma média, verdade. Só que se estiver de acordo com alguma condição. Se média, se for uma condição. Então aqui eu poderia colocar a minha média, se ela estiver de acordo com alguma coisa. Só que, calma aí, tem algum cuidado. Repara que na média Se, a gente tem duas fórmulas, média Se, média Ses. Que nem quando você tem um ônibus, e dois onibuseses. Eu sei que não é assim que funciona no português. Aqui também não é assim que funciona, mas é assim que a gente usa. Quando a gente vai aplicar várias condições possíveis, a gente acaba usando o média Ses. Então na prática, a gente usa muito mais o média Ses do que o média Se, é bem mais comum.

Média Ses, e aí dentro do média Ses, vou aplicar as condições, o que eu vou fazer é: eu quero a média desse meu preço, maravilha, que é a coluna dados preço. O que mais? Ponto e vírgula. Eu quero calcular de acordo com pessoa física ou pessoa jurídica, então eu vou escolher agora, pessoa física ou pessoa jurídica. Então eu vou calcular a média desse azulzinho aqui, do primeiro argumento da função, da fórmula, vou calcular a média disso, de acordo com isso. Mas de acordo com isso o quê? Eu quero que isso seja pessoa física, entre aspas.

Então se for cada uma dessas linhas aqui, dados, categoria, for igual à pessoa física, ele vai colocar na média. Se for diferente, não vai colocar na média. E está aí, média 22 mil 479, que é o valor que a gente tinha antes. Quero fazer a mesma coisa com pessoa física. Dou um copy, e dou um paste. Aí você fala: "Guilherme, cuidado, porque tem um cifrão do A2 até A47". Não tem, porque a gente está usando o nome que a gente deu, dados preço. Reparou na importância dos nomes dados quando a gente importou os dados para cá? É super prático para a gente. Claro, aqui eu fiz um copy paste nojento, porque não é pessoa física, é pessoa jurídica. E aí sim, eu tenho a média da pessoa física, separadinho da média da pessoa jurídica. Então as duas médias aí, bonitinhas.

Legal, a gente tem as duas médias isoladas, e a média total. Vou apagar essa média total, não estou interessado, estou interessado nas médias isoladas. Claro, por boa prática, é muito mais interessante colocar alguma informação extra aqui. Então eu vou colocar aqui média da venda, aqui eu tenho esses meus dois valores que na verdade são valores monetários, também poderia na verdade já formatar essa coluna inteira como dinheiro. Legal, então está formatado dinheiro bonitinho, e aqui eu tenho valores monetários. Aumentar essa coluna, e aqui eu quero também colocar mais bonitinho, que é o estilo de célula, eu vou escolher se é estilo de total, para ficar um pouquinho mais bonito, a média da venda pessoa física e jurídica, mas queria aplicar uma boa prática.

Aqui eu usei a string PF e PJ. Aí você fala: "Guilherme, e se eu tivesse uma terceira categoria agora, que é governo? A gente separou o de governo de PJ, ou separou ONGs, ou separou qualquer outra categoria", a gente vai ter que ficar dando copypaste e alterando aqui do lado direito, correndo o risco de esquecer, que nem, entre aspas, eu esqueci no começo. Então o que a gente faz? Na boa prática, da mesma maneira que a gente usou aqui referências para as colunas, aqui eu vou usar direto o nome da célula. Ponto e vírgula, o nome da célula aqui, E3. Então ele já pega a pessoa física. Inclusive quando eu der agora o copy e paste, ele já coloca o E4 para mim, que era exatamente o que eu queria, PJ. Claro, se eu escrever aqui Guilherme, algo que não faz sentido nenhum, dá um valor que não faz sentido nenhum. Por isso que eu tenho os valores que fazem sentido, legal?

Então eu vou salvar essa planilha. Análise. E essa é a planilha que a gente vai trabalhar de agora em diante. Então repare que com alguns recursos bem simples, a gente tem um poder muito legal. Primeiro, importar dados é super poderoso, a gente pode passar a referenciar esses dados dessa maneira que eu mostrei até agora. A gente pode referenciar coluna inteira, sem se preocupar mais com sifões e erros de mais um, menos um, etc., copy paste em diversas situações, e a gente aprendeu a mesclar condições com fórmulas. O média ses é super utilizado no dia a dia para a gente mesclar as duas condições. Condição com fórmula.

Pequenas boas práticas - Nomes e boas práticas

Vamos continuar a nossa análise? Primeiro, eu queria alterar o nome dessa planilha. Essa é a planilha com as nossas vendas, então eu vou anotar aqui o nome vendas. Meu próximo passo é o seguinte: a gente calculou aqui a média de acordo com uma condição, maravilha. Mas agora eu queria calcular a média também de acordo com uma condição que não é de igualdade. Aqui a gente fez uma condição de igualdade, que a categoria é igual à pessoa física. Agora, eu queria a média, e eu queria entender se quando a gente implementou um produto novo em primeiro de julho de 2018, as vendas médias aumentaram ou diminuíram.

De novo, olhar só a média, não é o mais interessante. Os nossos cursos de estatística vão apresentar a maneira de analisar uma distribuição de valores, analisar uma faixa, intervalo de confiança, etc. O que eu quero aqui é tentar explorar algumas funções do Excel.

Então o que eu queria era analisar um pouco a nossa média, ou dar uma olhada na nossa média, melhor, antes do dia primeiro de julho de 2018, e depois do dia primeiro de julho de 2018. Vamos fazer isso?

Então, a data que eu quero analisar é primeiro de julho de 2018. Então primeiro de julho de 2018, 1/7/2018. Já formatou, maravilha. Então o que eu quero é fazer uma média. Média, ou average, em inglês, se você estiver usando average. Average se, média se, average if, se você estiver usando em inglês. No caso nosso, a gente está usando em português, média ses se eu quisesse colocar várias condições.

Então eu vou fazer a média do preço, como a gente já está acostumado, que é o dados preço, ponto e vírgula. Mas eu quero analisar que coluna? Não é mais a coluna categoria, é a coluna data. Então dados, data. Maravilha, é só usar os nomes das colunas dos dados que a gente tem.

E o que eu quero fazer agora não é mais igual a PF, ou igual a PJ, que era só escrever a string, o texto PF ou PJ. Eu quero que seja maior do que 1/7/2018. Então o que eu vou escrever é: maior que 1/7/2018. Fechei aspas, fechei os parênteses, e dei o meu enter. Então quer dizer, após o dia 1/7/2018, essa é a média da venda. Isso daqui é após essa data, e antes da data.

Vamos calcular o antes da data? Control C, Control V. Tem algum perigo o control C, control V? Tem perigo sempre. A gente fica de olho, revisa a fórmula. Dados preço, maravilha, dados data, maravilha, eu quero então menores que o dia 1/7/2018, e está lá. Vamos aumentar aqui um pouquinho a coluna. Beleza. E antes é isso daqui. Tem algum errinho na minha fórmula, né? Sempre que a gente fala do menor e do maior, tem que pensar no caso do igual. O dia primeiro de julho, eu quero considerar após a data ou antes da data? Vai depender das análises que vocês fazem. Claro, então dependendo de um produto, pode ter sido que o produto novo foi lançado às três da tarde, então você queria fazer até as três da tarde, ou você quer deixar uma margem de dez dias antes, dez dias depois de fora, porque as pessoas que viram o produto anterior se confundiram com o produto seguinte. Tem mil maneiras de analisar uma mudança de produto. Nosso foco aqui é no Excel, nas ferramentas do Excel, no meu caso, eu vou analisar que, após a data, eu vou considerar também o dia primeiro. Eu vou considerar que o produto foi lançado meia noite do dia primeiro, quando o dia começou, zero horas da manhã, então vou considerar maior ou igual ao dia 1/7/2018.

E eu tenho aqui os meus dados, posso de novo formatar bonitinho, com o estilo de célula que eu tinha antes, que eu queria. Tenho aqui uma formatação de dinheiro um pouquinho feiosa, um pouquinho mais bonita agora, mas tem um detalhe. O detalhe que eu tenho aqui é: de novo, da mesma maneira que antes aqui eu usava o texto solto PF e PJ, mas e se eu errei a data, não é 1/7? Na verdade, era 25/6. Então eu venho aqui, mudo para 25/6/2018, e não muda nada. E aí você fala: "Guilherme, é só mudar aqui, 25/6. Muda aí todos os lugares para 25/6/2018". Tá bom, estou mudando. Mudei todos os lugares. Não mudei, esqueci. Esqueci de mudar esse outro. Então está tudo muito frágil, por quê? Porque esse valor aqui está repetido em todas as células, que nem antes, PF e PJ estava refletido em todas as células.

Então o que eu prefiro fazer, que é uma boa prática? Ficar evitando repetir valores toda hora. Então isso daqui é maior ou igual à data que está ali naquela fórmula, então a gente vai juntar essa string, maior ou igual, com, que é o e comercial, com, junta, concatena, com esse valor aqui. E aqui também, olha, menor, e concatena, esse valor aqui. Então eu tenho que até dia 1/7, maior ou igual é esse daqui, e menor é esse daqui. 37 mil, mais ou menos, e 43 mil. Se eu voltar atrás nas fórmulas, voltei e é igualzinho, com control Z. Vou voltar com control Y, preencher bonitinho, maravilha. Então 37 mil e 43 mil, são os dois valores, e eu estou usando sempre essa fórmula. Se eu mudar para 25/6/2018, vai mudar tudo de acordo, tudo é atualizado de acordo. Voltei atrás porque a data certa é 1/7.

Então repara que legal, a gente usando só o média ses, usando os nomes que foram dados às colunas, concatenação, e extraindo esses valores, a gente já tem bastante ganho. Mas ainda tem uma coisa estranha aqui, o que é o F6? Você chega numa fórmula como essa, que tem dados, é a média dos preços, se a data for menor do que F6. É bizarro esse nome, F6. Está tudo bonito, menos F6. Então o que é comum a gente fazer, principalmente em fórmulas complexas? Em situações que você tem dados complexos, você pode ter uma fórmula complexa cheia de valores, que fica difícil de você lembrar da onde veio exatamente. Tem diversas maneiras da gente analisar isso. Uma delas é, em vez de colocar F6, é fazer que nem a gente fez aqui, dar um nome para o F6. Pega essa célula F6 e dá um nome para ela.

Então o que eu quero fazer para essa célula, cliquei da direita, definir um nome. Vou definir um nome. E ele fala: o nome que você quer dar para essa célula é 01/07/2018, começando com underline? Não, esse nome não faz sentido nenhum. Essa aqui foi a data de lançamento da nova versão, então eu vou colocar a data de lançamento da nova versão. E você pode falar várias configurações quando você está lançando uma variável nova, um nome novo Desculpa, não é uma variável, um nome novo. Inclusive colocar um comentário. Vou deixar o comentário para deixar bem claro o que é. Nesta data foi lançada a última versão do produto. Então é a data de lançamento da nova versão. A última versão dela, a mais recente, foi lançada nessa data, e eu vou usar esse nome. Eu dou um ok, e aí eu tenho essa variável aqui, data de lançamento da nova versão.

Então aqui onde se usava o F6, eu posso passar a usar data de, e ele já preenche para mim. Eu dou um tab, está preenchido bonitinho. Aqui, data do, tab, preencheu bonitinho. Então aqui é a média dos preços se a data da minha venda for menor que a data de lançamento da nova versão, legal? Então aqui eu tenho a fórmula bonitinha.

E repara, que de repente você decide que o nome ficou meio confuso, ou quais são os nomes que eu tenho mesmo na minha planilha? Então você pode procurar quais são os dados que você possui. Então aqui em fórmulas, você vai ter os nomes que foram definidos para você. Então você pode definir um nome, que nem a gente fez agora, ou gerenciar todos os nomes. Olha, tem os dados, que são todos aqueles dados que a gente tinha importado para dentro da planilha, e tem a data de lançamento. Data de lançamento é o vendas F6. Vendas F6 é a data de lançamento. Posso dar dois cliques para editar. Não é data de lançamento da nova versão, é data de lançamento da última versão, e eu dou ok. Esse era o nome que eu queria dar, aí você olha aqui embaixo, e ele atualizou para a data de lançamento da última versão. Então são maneiras de a gente manter a nossa planilha mais limpa, mais compreensível no dia a dia.

Sobre o curso Excel: simulação e análise de cenários

O curso Excel: simulação e análise de cenários possui 120 minutos de vídeos, em um total de 40 atividades. Gostou? Conheça nossos outros cursos de Excel em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Pequenas boas práticas
• Importando e atualizando
• Validação
• Rodando cenários
• Gerenciando
• Metas e mais cenários complexos