Excel Avançado - Avançando para o level Mestre

Este curso foi construído em cima de conceitos abordados em dois cursos: Microsoft Excel para iniciantes & Microsoft Excel Intermediário. Navegue através do plano do curso para o curso de Microsoft Excel para iniciantes e no plano do curso de Microsoft Excel para usuários intermediários. Se você precisar aprender qualquer um dos conceitos abordados nos cursos anteriores, você pode fazer esses cursos, e então retomar este curso. Além do mais, eu desenvolvi um curso completo o qual inclui todo material dos cursos de Microsoft Excel iniciante, intermediário e avançado.

Existem diferentes versões para o Microsoft Excel. A cada alguns anos, a Microsoft lança uma nova versão do Excel. Entendendo a diferença das versões disponíveis, e qual versão você está utilizando, é bem útil. Neste curso, nós vamos estar utilizando a versão mais atual do Microsoft Excel no momento da gravação: Microsoft Excel 2016.

Entender o que fez outras pessoas serem bem sucedidas pode ajudar você a ser bem sucedido. Estes são os princípios que me ajudaram a ser bem sucedido. Eu aprendi estes princípios de outras pessoas e da minha própria experiência. Eu compartilho estes princípios para ajudar você a ser bem sucedido neste curso e na vida:

• Tempo na tarefa

• Determinação

  • Angela Duckworth: Grit: the power of passion and perseverance

• Foco

  • Bill Gates & Warren Buffett

  • Bill Gates, “Get in front of what’s coming and let it hit you.”

• Hábitos de pessoas efetivas

• Meus professores

  • drop by drop, the bucket gets filled

  • persistently, patiently, you are bound to succeed

  • água mole em pedra dura, tanto bate até que fura

  • you’re doing it, baby, you’re doing it, come on, come on!

O plano do curso faz parte do curso. Por favor leia todas as descrições dos vídeos no plano do curso. Isso faz parte do processo de aprendizado. Quando você ler as descrições:

• Os conceitos que você está aprendendo irão ser reforçados

• Você irá aprender o conteúdo mais rapidamente

Adicionalmente, eu forneço informação adicional na descrição dos vídeos. As vezes eu gravo uma aula e então me lembro de algum recurso ou alguma outra informação a qual você deveria ter conhecimento. Alguns desses recursos e informações extras que eu incluí são bastantes valiosos.

• Plano do Curso

  • Completo

    • https://goo.gl/faPHTg E TODOS OS ARQUIVOS DE EXERCÍCIOS ESTÃO AQUI https://goo.gl/oQgg1A

  • Iniciante

    • https://goo.gl/nXn4nr E TODOS OS ARQUIVOS DE EXERCÍCIOS ESTÃO AQUI: https://goo.gl/oQgg1A

  • Intermediário

    • https://goo.gl/sxP3en E TODOS OS ARQUIVOS DE EXERCÍCIOS ESTÃO AQUI: https://goo.gl/oQgg1A

  • Avançado

    • https://goo.gl/brKHsr E TODOS OS ARQUIVOS DE EXERCÍCIOS ESTÃO AQUI: https://goo.gl/oQgg1A

• arquivos do curso

  • Google Drive E TODOS OS ARQUIVOS DE EXERCÍCIOS ESTÃO AQUI: https://goo.gl/oQgg1A

  • GitHub

• • • Efetuando download do arquivo zip

• José Flávio no YouTube

  • https://www.youtube.com/channel/UCiA2btrU7DEkqfXidFNIstQ

• José Flávio Twitter

  • https://twitter.com/joseflaviogm

Você pode aumentar a velocidade dos vídeos quando você estiver assistindo-os. Não são todos que conhecem isso. Assistir vídeos com maior velocidade é de grande ajuda para muitos alunos. Não atende a todo mundo, mas funciona para muita gente. Você também pode habilitar em “tools / document outline” para nosso plano do curso.

Pegue um pedaço de papel e uma caneta. Escreva os princípios para o sucesso do aluno. Escreva o compromisso com você mesmo sobre com que frequência, e quanto, você vai se dedicar para aprender Excel. Por exemplo, você pode escrever, “Eu irei estudar Excel todos os dias por no mínimo 30 minutos até eu terminar o curso” Pendure este papel onde você e todos os outros em sua casa, possam ver ele todos os dias.

Existem várias áreas na vida onde é importante manter uma boa saúde:

• saúde física

• saúde social / familiar

• saúde mental / emocional

• saúde financeira

Este capítulo irá ensinar como usar e compreender as funções financeiras.

Dinheiro e tempo tem um relacionamento. Um real hoje não vale um real amanhã. Existe juros e inflação. Se eu investir um real, eu espero receber juros. Quando eu tinha dezoito anos, com R$5,00 eu conseguia fazer um lanche generoso. Hoje você precisa de R$10,00 para comprar um lanche. Este é o valor monetário no tempo. O valor que o dinheiro tem ao longo do tempo é determinado por juros e inflação. Juros é o custo do dinheiro. Inflação é o aumento nos preços (e deflação é a diminuição nos preços). Quando falamos sobre números “reais”, levamos em consideração a inflação; ajustando nossas equações para anular o efeito da inflação. Boas definições para saber:

• inflação - aumento nos preços ao longo do tempo

  • deflação - diminuição nos preços ao longo do tempo

• juros - custo do dinheiro

• dinheiro “real” - inflação foi anulada

  • por exemplo: taxa real de retorno

    • juros 10%

    • inflação 4%

    • taxa real de retorno 6%

arquivo:

• http://www.investopedia.com/articles/03/082703.asp

• http://www.infomoney.com.br/educacao/glossario

O que vale a pena no futuro? O que é algo de valor no futuro? Isto é o valor futuro. O valor futuro é o valor de um montante no futuro. Ele mede a soma futura nominal do dinheiro que dada soma do dinheiro vale em um tempo específico no futuro assumindo uma certa taxa de juros, ou genericamente, taxa de retorno. Este vídeo ensina o caminho manual de se calcular o valor futuro.

arquivo:

• http://www.investopedia.com/articles/03/082703.asp

• http://www.infomoney.com.br/educacao/glossario/v

• 268-valor-futuro

Este vídeo ensina o caminho automático para calcular valor futuro utilizando a função do Excel VF.

arquivo:

• http://www.investopedia.com/articles/03/082703.asp

• http://www.infomoney.com.br/educacao/glossario/v

• 269-valor-futuro-II

Este vídeo ensina como calcular o valor futuro quando você também está adicionando um investimento a cada período com um pagamento periódico.

arquivo: 270-valor-futuro-III

Aqui é ensinado como calcular manualmente o valor futuro com um pagamento para o investimento a cada período.

arquivo: 271-valor-futuro-IV

O que vale a pena agora? Este é o valor presente. O valor presente é o valor que um pagamento futuro valeria agora. Devemos utilizar uma taxa de juros para determinar o valor presente. Essa taxa de juros é chamada de taxa de desconto.

arquivo:

• http://www.investopedia.com/articles/03/082703.asp

• 272-valor-presente

Aqui é ensinado como utilizar a função VP no Excel para calcular o valor presente.

arquivo:

• http://www.investopedia.com/articles/03/082703.asp

• 273-valor-presente-II

Calcule manualmente, apenas utilizando fórmulas que você criar e não utilize funções do Excel. Crie um cronograma de 30 anos que exiba o valor futuro de um investimento se você investir R$ 100.000,00 hoje e receber 11% de juros a cada ano.

arquivo: 274-exercício-prático-01

Utilizando a planilha do exercício anterior, na coluna ao lado de seus cálculos manuais, utilize a função VF do Excel para conseguir os mesmos resultados.

arquivo: 275-exercício-prático-02

Calcule manualmente, apenas utilizando fórmulas que você criar e não utilize funções do Excel. Crie um cronograma de 30 anos que exiba o valor futuro de um investimento se você investir R$ 100.000,00 hoje, contribuindo com um valor adicional de R$10.000,00 ao investimento todos os anos no final do ano, e ganhar 11% de juros todos os anos. Aqui está como seu cronograma de contribuições deve ficar:

• ano 0 R$100.000

• ano 1 R$10.000

• ano 2 R$10.000

• ano …

• ano 30 R$10.000

arquivo: 276-exercício-prático-03

Utilizando a planilha do exercício anterior, na coluna ao lado de seus cálculos manuais, utilize a função VF do Excel para conseguir os mesmos resultados.

arquivo: 277-exercício-prático-04

Caso alguém oferece para pagar seus herdeiros R$2.850.844.155.882,65 em 200 anos por um trabalho que gastaria 6 meses para concluir, qual salário por hora você ganharia se você trabalhar 40 horas por semana por 26 semanas? Utilize a taxa de desconto de 11,5% para calcular o valor presente.

arquivo: 278-exercício-prático-05

Se você investir R$1.000 hoje, ganhar 11,5% de juros nisso, e houve inflação de 4%, quanto o seu investimento valeria em 200 anos em “reais” reais?

arquivo: 279-exercício-prático-06

Qual é o valor presente de R$1.000.000 30 anos a partir de agora assumindo uma taxa de desconto de 7%?

arquivo: 280-exercício-prático-07

A função pgto nos permite calcular o pagamento de um empréstimo (ou uma anuidade, e então o pagamento deve ser você, o credor).

arquivo: 281-pagamento

Amortizar um empréstimo é abater a dívida. Nós podemos criar um cronograma exibindo o empréstimo sendo quitado. Isto é conhecido como cronograma de amortização

arquivo: 282-amortização

Aqui está um exemplo de um empréstimo onde você é o credor - uma anuidade. A função ipgto também é abordada neste vídeo. A função ipgto permite calcular o pagamento de juros.

arquivo: 283-anuidade

Como utilizar ppgto & ipgto para calcular o pagamento e os juros para um dado período de um empréstimo.

arquivo: 284-ppgto-ipgto

Em finanças, o valor presente líquido (VPL) é uma medida de lucro calculado ao subtrair o valor presente (VP) da saída de dinheiro (incluindo o custo inicial) dos valores presentes das entradas de dinheiro em um período de tempo. VPL é utilizado para tomada de decisões sobre um projeto, se ele deve ou não ser realizado.

arquivo: 285-vpl

A taxa interna de retorno (TIR) é um método de calcular a taxa de retorno. O termo interno se refere ao fato de que o cálculo não envolve fatores externos, como inflação ou o custo do capital. TIR também é utilizado para tomada de decisões sobre um projeto que deve ou não ser executado. VPL é mais popular para aprendizado; TIR é mais popular na indústria.

arquivo: 286-tir

Calcule o pagamento da seguinte hipoteca:

• montante: R$100.000

• taxa: 4,25%

• prazo: 30 anos

• período: mensalmente

arquivo: 287-exercício-prático-01

Crie um cronograma de amortização para a seguinte hipoteca:

• montante: R$100.000

• taxa: 4,25%

• prazo: 30 anos

• período: mensalmente

arquivo: 288-exercício-prático-02

É o seu aniversário. Agora você tem 65 anos. Estatísticas dizem que você irá viver 75 anos. Você pensa estar acima da média, entretanto, gostaria de planejar a vida para 90 anos. Você tem R$3,65M em um investimento o qual retorna 4,5% anualmente. Quanto você pode retirar todo mês, começando ao final deste mês, se você deseja utilizar apenas R$3M de seu investimento até o momento em que fizer 90 anos. Você irá retirar este dinheiro durante 300 períodos mensais. Crie um cronograma de amortização exibindo os balanços a medida que você efetua os saques.

arquivo: 289-exercício-prático-03

Na planilha associada a este exercício você irá encontrar uma lista de fluxo de caixa para cada mês. Utilizando análise VPL, este projeto vale a pena de ser realizado?

arquivo: 290-exercício-prático-04

Na planilha associada a este exercício você irá encontrar uma oportunidade de investimento. Utilizando análise TIR, este investimento ganhará uma taxa de retorno maior que a média de 7% do mercado de ações?

arquivo: 291-exercício-prático-05

Tabelas dinâmicas são super poderosas. Muitas pessoas não sabem como utilizar tabelas dinâmicas. Em poucos minutos, este vídeo irá te deixar apto a trabalhar com tabelas dinâmicas. Uma tabela dinâmica permite que você analise seus dados por diferentes ângulos. Software Tableau é onde você deve ir para análise de dados mais avançadas.

arquivo: 292-intro-tabelas-dinâmicas

O propósito dos computadores é adquirir dados e transformá-los em informação. Tabelas dinâmicas nos permitem adquirir uma grande quantidade de dados e obter informações rapidamente. Um dos maiores desafios com as tabelas dinâmicas é saber quais perguntas fazer. Quando você tem uma tabela de dados, que tipo de informação você pode extrair desses dados? Novamente, abraçar o espírito de exploração e experimentação é a chave para descobrir as informações. Aqui estão vários exemplos de visualizações interessantes sobre nossos dados. Se você é parecido comigo, quando eu aprendi sobre tabelas dinâmicas pela primeira vez, minha reação foi “Espere um pouco! Você pode repetir? E de novo? Mais uma vez? Mais uma? Mais devagar.” Criando grupos flexíveis foi abordado brevemente. Vamos aprofundar nisso novamente em outro vídeo.

arquivo: 293-pivotando-seus-dados

Nós utilizamos tabelas dinâmicas para transformar dados em informação. Quando você tem uma tabela de dados, pode acontecer de ser um pouco desafiador saber qual informação é possível extrair dos dados. Neste vídeo, nós continuamos nossa exploração de transformar nossos dados em informação utilizando tabelas dinâmicas.

arquivo: 294-dados-em-informação

Algumas vezes as tabelas dinâmicas não fornece todas as informações que precisamos. Nós podemos utilizar outras habilidades com Excel, como seu conhecimento da procv, para ajudar-nos a completar a informação de nossas tabelas.

arquivo: 295-informação-adicional

Quando os dados subjacentes de uma tabela dinâmica mudam, a tabela dinâmicas não muda automaticamente. Para atualizar a tabela dinâmica, você deve fazê-lo manualmente. Esse vídeo explica tudo sobre isso e demonstra como fazê-lo: botão direito em sua tabela dinâmica e escolha “atualizar”.

arquivo: 296-atualizar

Você pode criar dados aninhados em tabelas dinâmicas. Isso permite você aprofundar e ver os dados subjacentes. Para criar dados aninhados, adicione múltiplos itens nas áreas de “linha” ou “coluna” no construtor de TabelasDinâmicas. Quando você aninha itens, você precisa ter itens que são conectados logicamente. Por exemplo:

• ano, trimestre, mês, semana

• região, estado, cidade, cep

arquivo: 297-aninhar

Aprenda como agrupar dados em tabelas dinâmicas: botão direito em um título de uma linha ou coluna na sua tabela dinâmica então, escolha “agrupar”

arquivo: 298-agrupar

Um segmento é uma interface visual para filtrar dados em uma tabela. Podemos utilizar segmentos para filtrar visualmente nossos dados. Segmentos também podem ser aplicados a tabelas básicas.

arquivo: 299-segmentação-de-dados

Uma linha do tempo é uma segmentação de dados a qual permite que você crie diferentes segmentos por tempo. Podemos utilizar linhas do tempo para filtrar visualmente nossos dados.

arquivo: 300-linha-do-tempo

Podemos construir gráficos baseados em tabelas dinâmicas. Quando fazemos isso, o gráfico é conectado a tabela dinâmica. O gráfico é conhecido como gráfico dinâmico. Quando a tabela dinâmica é atualizada, o gráfico é atualizado também.

arquivo: 301-gráficos-dinâmicos

Existem tabelas dinâmicas recomendadas que podem ser úteis algumas vezes. Quando você criar uma tabela dinâmica recomendada, Excel escolhe aleatoriamente diferentes combinações de seus dados.

arquivo: 302-recomendado

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: vendedores

• coluna: fabricantes

• valor: soma total vendas

arquivo: 303-exercício-prático-01

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: vendedores

• coluna: região & fabricantes

• valor: contagem total vendas

arquivo: 304-exercício-prático-02

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: data (desagrupado)

• coluna: região e estado

• valor: contagem total vendas

Adicione um filtro de linha do tempo. Escolha dois meses das datas para exibir.

arquivo: 305-exercício-prático-03

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: item

• coluna: região & estado

• valor: média total de vendas

Adicione uma coluna com procv para que possamos saber o fabricante de cada item.

arquivo: 306-exercício-prático-04

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: item

• coluna: região

• valor: soma de quantidade

Adicione três segmentos de dados à sua escolha e selecione alguns dos botões dos segmentos.

arquivo: 307-exercício-prático-05

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: vendedor

• coluna: fabricante

• valor: soma total vendas

Adicione um gráfico dinâmico de coluna empilhada. Adicione uma linha do tempo. Escolha diferentes trimestres na linha do tempo.

arquivo: 308-exercício-prático-06

Utilizando a pasta de trabalho associada a este exercício, crie uma tabela dinâmica com:

• linha: anos & trimestres

• coluna: região

• valor: contagem de transações de vendas

Adicione um gráfico dinâmico de coluna agrupada. Expanda a tabela dinâmica para exibir trimestres. Adicione segmentos de dados para vendedores. Selecione opções no segmento.

arquivo: 309-exercício-prático-07

Algumas peças novas para aprendermos:

• modelo de dados

  • nos leva a power pivot

  • permite-nos a modelar nossos dados

    • conecta planilhas relacionadas juntas

    • é como utilizar um banco de dados

• power pivot

  • • mais ferramentas de análise de dados

• obter e transformar

  • importar e limpar dados

Você pode importar milhões de registros na memória utilizando power pivot ou consulta de obter e transformar (query)

arquivo: 310-intro

Para utilizar dados em uma planilha em power pivot, esses dados devem:

• ser uma tabela / tabela dinâmica (pivot table)

  • NOMEIE SUA TABELA; prefixo com “r” para relacional

• sejam adicionados ao modelo de dados

  • power pivot menu → adicionar ao modelo de dados

Você pode utilizar “alt+tab” para trocar entre excel e a janela power pivot.

arquivo: 311-adicionar-ao-modelo-dados

Precisamos dizer ao power pivot como nossos dados são relacionados. Fazemos isso através de arrastar e soltar.

arquivo: 312-relacionamentos

Uma coluna calculada é uma coluna a qual você adiciona a uma existente tabela Power Pivot. Se você inclui a tabela Power Pivot em uma TabelaDinâmica (ou GráficoDinâmico), a coluna calculada pode ser utilizada como qualquer outra coluna de dados. As fórmulas nas colunas calculadas são bastante parecidas com as fórmulas que você criou no Excel. Ao contrário do Excel, no entanto, você não pode criar uma fórmula diferente para diferentes linhas em uma tabela; Em vez disso, a fórmula DAX é automaticamente aplicada a toda coluna e calculada para cada linha. Os resultados são calculados para a coluna assim que você cria a fórmula. Os valores das colunas apenas são recalculados se os dados subjacentes são atualizados ou se um recálculo manual for utilizado. Você pode criar colunas calculadas que são baseadas em medidas e outras colunas calculadas. Entretanto, evite utilizar o mesmo nome para uma coluna calculada e uma medida, uma vez que pode resultar em resultados confusos.

• coluna (área superior =)

  • fx

    • filtro

  • =related(NomeColuna)

    • clicar no nome da coluna na tabela relacionada

  • =round(<outro código>, 2)

  • formatar como moeda com botão “$”

    • sem formatar com botão-direito

arquivo: 313-colunas

• Documentação Microsoft sobre “DAX Excel” Expressões de análise de dados

Uma medida é uma fórmula a qual é criada especificamente para ser utilizada em uma TabelaDinâmica (ou GráficoDinâmico) que utiliza dados Power Pivot. Medidas podem ser baseadas em funções de agregação padrão, como CONT.NÚM ou SOMA, ou você pode definir sua própria fórmula utilizando DAX. Uma medida é utilizada na área de Valores de uma TabelaDinâmica.

• Uma medida é como campo como campo calculado como medida calculada.

• Se você deseja colocar resultados calculados em uma área diferente de uma tabela dinâmica, use uma coluna calculada em vez disso. Quando você define uma fórmula para uma medida explícita, nada acontece até você adicionar à medida em uma tabela dinâmica. Quando você adiciona a medida, a fórmula é avaliada para cada célula na área Valores da tabela dinâmica. Como um resultado é criado para cada combinação de cabeçalhos de linha e coluna, o resultado para a medida pode ser diferente em cada célula. A definição da medida que você cria é salva com sua tabela de dados de origem. Ele aparece na lista Campos dinâmicos e está disponível para todos os usuários do caderno de trabalho.

• medida (área inferior :=)

  • Qualquer Nome que Desejar :=Soma(rNomeColuna)

    • formatar como moeda com botão “$”

      • é possível formatar com botão-direito

arquivo: 314-medidas

• Documentação Microsoft sobre “DAX Excel”

Partindo da área Power Pivot, escolha “tabela dinâmica” para criar uma tabela dinâmica. Uma vez criada a tabela dinâmica, você também pode criar um gráfico dinâmico da maneira convencional.

arquivo: 315-pivot