Progredindo com Excel - Curso Excel Usuários Intermediários

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. 

Excel é feito para se trabalhar com números; para obter dados e transformá-los em informações. Nesta secção, em nossa faixa de opções, vamos mergulhar no menu de dados. Para trabalhar com dados, precisamos obter alguns dados. Algumas fontes que nós podemos utilizar para adquirir dados para trabalharmos:

• Google finance - financials

• Yahoo finanças - Dados Históricos

• US Census quickfacts

• IBGE cidades

• Google keyword planner

• Oscar winners

Exercício prático:

• copie dados de cada uma das fontes acima e transfira para o Excel

Os dados armazenados em um arquivo de texto são geralmente separados por um delimitador. Usualmente esses delimitadores são uma vírgula ou tabulação. Você também pode escolher outros delimitadores se necessário.

Você pode usar o assistente de importação de texto para importar dados de texto para o Excel. É uma habilidade essencial com o Excel saber como utilizar o assistente de importação de texto.

Exercício prático:

• utilize o assistente de importação de texto para importar dados dos seguintes arquivos:

  • 092-adquirindo-dados-GOOG.csv

  • 092-adquirindo-dados-Keyword Planner 2017-12-01 at 14_41_36.csv

Classificar nos permite classificar dados rapidamente. Essa é uma ferramenta poderosa.

Exercício prático:

• classificar dados

  • abra o arquivo “093-importando-dados-texto” no Excel. Classifique os dados de “keyword” (palavra chave) nesta ordem:

    • “Suggested bid” (lance sugerido) menor para o maior

    • “Competition” (Concorrência) menor para o maior

Procv é outra ótima e poderosa ferramenta. Ela permite que nós procuremos por um valor em uma tabela de valores, e então retorna um valor associado. Por exemplo, em um diário escolar um aluno pode ter atingido a nota de 84%. Eu posso usar procv para então inserir “B” ao lado da nota do aluno.

Exercício prático:

• procure por uma nota percentual e então exiba a nota apresentada por letra

Congelar painéis nos permite congelar certas colunas ou linhas na sua planilha de trabalho. Isso é útil para que você possa visualizar cabeçalhos associados aos dados a medida que você percorre através dos dados

Exercício prático:

• Na pasta de trabalho “096-congelar-painéis” utilize congelar painéis de uma maneira que faça sentido para as planilhas.

Nós podemos usar procv e classificar para ganhar informações adicionais dos dados coletados do Google Adwords Keyword.

Exercício prático:

• Refaça o que foi feito nesse vídeo.

Nós vimos como classificar os dados. Classificar é uma ferramenta muito poderosa. Ela nos permite colocar nossos dados em uma ordem pelos critérios que especificamos. Outra ferramenta poderosa é Filtrar. Filtrar nos permite filtrar nossos dados por critérios que especificamos.Quando nós filtramos dados, nós dizemos ao Excel para apenas apresentar certos tipos de dados baseados em certos critérios.

Exercício prático:

• Efetue download de um Google Adwords Keywords que seja relevante para o seu negócio ou negócio de um amigo. Da mesma maneira demonstrado nesse vídeo, utilize filtrar e classificar para construir uma lista de keywords (palavras chave) que você provavelmente queira comprar.

Filtro avançado caiu em desuso porém ainda tem suas utilidades. Filtrar Avançado é confuso e estranho de se usar.

Filtrar avançado pode ser útil nas seguintes situações:

• você deseja 3+ critérios em uma coluna

• você deseja realizar um “OU” entre colunas

Exercício prático:

• Torture-se experimentando utilizar o filtro avançado. Quando você estiver suficientemente frustrado, volte a utilizar o filtro comum. Tente fazer um “OU” entre colunas.

Localizar e substituir são ótimos. Localizar ajuda a localizar dados rapidamente. Substituir ajuda você a localizar e substituir certos dados. Você também pode usar “localizar” em muitas outras aplicações.

Exercício prático:

• Substituir cada ocorrência de “college” por “university”

Quão popular é o seu primeiro nome? O IBGE no Brasil tem uma lista de nomes dados às crianças no momento de nascimento. Você pode buscar no Google “nomes no Brasil” ou acessar este link e este link. Os dados retirados do material de apoio do segundo link, está no repositório do Github em nossa pasta de arquivos do curso, com o nome “101-nomes-estados.zip”. Nesse arquivo contém arquivos de texto separados por tabulação, importe o arquivo de texto de seu Estado e então determine quantas pessoas também receberam seu nome na década em que você nasceu. 

Quão popular é seu nome por estado? O IBGE possui dados dos nomes dados às crianças de acordo com o Estado. Faça download dos dados em nosso repositório do Github, e utilize “102-UF-Total.csv”. Importe os dados e encontre seu nome relacionado ao estado em que nasceu. 

Entre com as 216.931 perguntas do jogo Jeopardy!, 219 registros fazem menção ao “Wilde” nas perguntas. Filtre os registros para apenas exibir esses registros. 

Entre as 216.931 questões e respostas do jogo Jeopardy!:

• Filtre os registros para apenas exibir os 778 registros que

  • menciona “?lympi*” na pergunta

• Filtre os registros para apenas exibir os 13 registros que

  • contenha “Innsbruck” como resposta

• Filtre os registros para apenas exibir os 11 registros que

  • menciona “?lympi*” na pergunta

  • contenha “Innsbruck” como resposta

• Filtre os registros para apenas exibir os 2 registros que  

  • não menciona “?lympi*” na pergunta

  • contenha “Innsbruck” como resposta

Gapminder exibe uma ótima visualização de dados. Você também pode efetuar download e trabalhar com os dados fornecidos. Utilizando a planilha “105-gapminder-expectativa-de-vida-ao-nascer.xlsx”,

• exiba os países que possuam a expectativa de vida maior que 70 em 2016

• use congelar painéis para manter o cabeçalho das colunas e linhas no lugar

Utilizando o arquivo “106-gapminder-crianças-por-mulher-fertilidade_total.xlsx” :

• Oculte as colunas 1800 - 1999

• congele o cabeçalhos das colunas e linhas

• Crie uma nova coluna com o título “Nota”

  • para cada valor de 2015, utilize procv para fornecer uma nota

    • 0 crianças = A

    • 2 ou menos crianças = B

    • 3 ou menos crianças = C

    • 4 ou menos crianças = D

    • 4 ou mais crianças = F

Utilizando a Lista de línguas da página da Wikipedia,

• traga a tabela de dado exibindo as línguas faladas para o Excel.

• use congelar painéis apropriadamente

• classifique a lista por “fração da população mundial” do menor para o maior

filtre para exibir apenas as línguas faladas por 74 milhões ou mais indivíduos

Abra a pasta de trabalho “108-exercício-prático-08”. Substitua todas as notas 100 por 102.

Importante: você irá precisar substituir de volta os pontos possíveis para cada tarefa por 100.

Filtre os dados utilizando filtrar avançado para exibir

• avg monthly searches > 100001

• OU

• suggested bid  < ,10

Filtre os dados utilizando filtrar avançado para exibir

• keyword contém “*xl”

• OU

• keyword contém “*want”

• OU

• keyword contém “*master”

Importante: você precisa adicionar um caractere de * antes de cada palavra para buscar por qualquer número de caracteres antes de cada palavra.

Revisão e prévia; Onde estivemos e para onde estamos indo.

Nós tivemos uma prévia disso em vários pontos, mas agora nós iremos abordar oficialmente. Utilizando “texto para colunas” você pode selecionar texto e então abrir a caixa de diálogo do “assistente para conversão de texto”. Essa é uma ferramenta poderosa para dividir dados em uma coluna e limpar dado. Nós vamos trazer dados da lista de línguas da página da Wikipedia para ilustrar isso.

Exercício prático: Copie as frases de Bill Gates, coloque-as no Excel, então remova os números.

Você pode remover dados duplicados usando a ferramenta “remover duplicatas” do menu Dados.

Exercício prático:

• Faça download da planilha utilizada nesse vídeo e então remova duplicatas.

• tente mudar o dado de uma linha de uma duplicata, então “remover duplicatas” de uma maneira que essa linha com mudança não seja removida.

Você pode validar a entrada de dados dos usuários utilizando a ferramenta “Validação de Dados” do menu de Dados na faixa de opções. Utilizando validação de dados significa que um usuário só pode dar entrada a certos tipos de dados em certas células.

Exercício prático: crie uma planilha; tenha uma coluna rotulada como “idade” e apenas aceite números inteiros; fornecer uma mensagem para o usuário dizendo a eles isso, e também informando-os caso tenham tentado entrar com dados errados.

Consolidar irá buscar dados em localizações diferentes e rodar cálculos nos dados.

Exercício prático: utilizando a planilha de trabalho desse vídeo,

• crie uma nova planilha chamada “Destino”, então referenciar valores nas duas diferentes planilhas, retornando esses valores nas duas diferentes planilhas “consolidar 1-1” & “consolidar 1-2” na planilha nomeada “Destino”.

• consolide a contagem dos dados de 2017 & 2018

Testes de Hipóteses é a origem das planilhas. Na Universidade de Negócios de Harvard, Dan Bricklin criou a primeira planilha para realizar testes “e se”. Teste de Hipóteses é simplesmente perguntar “e se”. E se isto acontecer? E se aquilo acontecer? Para teste de hipóteses de “atingir meta”, nós definimos alguma meta e então Excel trabalha como os números devem ser alterados para realizar a meta estipulada.

Exercício prático: utilizando a planilha do vídeo, para atingir um ganho de $135.000 qual o valor de “Preço de venda do curso” precisa ser?

O gerenciador de cenários permite você criar e salvar diferentes cenários. Você pode então facilmente clicar entre diferentes cenários. Você também pode mostrar um resumo dos cenários.

Exercício prático: utilizando a planilha do vídeo, crie um cenário no qual o preço é 19, clientes 15000, e professor royalty é 35%.

Uma tabela de dados exibe a você muitos resultados possíveis baseados na mudança de duas entradas de dados. Em contraste, um cenário exibe para você um resultado possível baseados na mudança de várias entradas de dados. 

Planilha de previsão.

Utilizando o que você construiu nos exercícios práticos #1 & #2, utilize procv para adicionar o nome completo do time próximo a cada nome do jogador. 

Utilizando os dados da NFL que estamos criando,

• separe os nomes dos jogadores utilizando texto para colunas

• em uma nova coluna, coloque os nomes dos jogadores juntos de volta utilizando preenchimento relâmpago

• em uma nova coluna, coloque os nomes dos jogadores juntos de volta utilizando concatenar

em uma nova planilha, liste todos os nomes dos jogadores, sem duplicatas

Abra o arquivo com planilhas que contém dados sobre desemprego para indivíduos acima de 15 anos. Existem dois grupos de quatro planilhas de trabalho. Utilize consolidar em cada um dos grupos de dados.

• Qual é a diferença?

• Como ocorreu essa diferença essa?

Utilize atingir meta para determinar qual nota você precisa tirar no exame Final para ganhar um “A”. Depois de ter feito isso, remova duplicatas da sua lista de contatos de amigos. 

Crie os seguintes cenários

• um

  • 53% na prova Final

  • 100% nos capítulos 14,15,16

• dois

  • 75% na prova Final

  • 50% nos capítulos 14,15,16

• três

  • 90% na prova final

  • 0% nos capítulos 14,15,16

Exiba um resumo quando você terminar.

Crie uma tabela de dados com duas entradas sendo a nota da prova Final e sua presença em sala de aula. Quantas aulas você pode faltar e ainda conseguir uma nota “A”? 

Desenvolva a mesma solução para o exercício anterior porém ao invés de utilizar uma tabela de dados utilize referências relativas, absolutas e mixadas. 

Buscando do Yahoo Finanças, efetue download de todos os dados históricos do GOOG. Utilizando o preço (coluna) “Close”, qual é a previsão para agregar valor nos próximos anos com 95% de confiança?

• dica: você precisa de um intervalo regular. Apenas utilize um único dia da semana como seus pontos de dados.

  • altere a data para “personalizado” com “dddd” como formato

  • copie e cole no bloco de notas

  • copie e cole de volta ao excel

    • classificar

    • retire os outros dias

      • utilize a função “se” para encontrar apenas “quarta-feira”

• Lembre-se, quando configurar a sazonalidade, o ano possui 52 semanas.

Revisão e prévia; Onde estivemos e para onde estamos indo.

Nós podemos organizar dados em grupos. Você pode agrupar colunas ou linhas juntas. Quando você agrupa dados, a navegação é indicada na esquerda das linhas ou no topo das colunas. Você pode clicar nesses elementos de navegação para expandir ou contrair seus dados. Isso permite que você rapidamente revele, ou esconda, grupos de dados. Nota de distinção: se você não deseja que pessoas vejam colunas, utilize “ocultar” ao invés. 

Calcule subtotais e totais gerais automaticamente em uma lista para uma coluna utilizando o comando subtotal.

Quando você cria um gráfico, o que você seleciona é crucialmente importante. Em outras palavras, você irá querer selecionar

• dados, sem totais

• cabeçalho de colunas

• cabeçalho de linhas

Algumas vezes isso significa selecionar regiões não-contígua. Para fazer isso

• ctrl + clicar-&-arrastar

Se você não gostar da maneira que o gráfico ficar quando você criá-lo, tente selecionar diferentes dados e criar o gráfico novamente. Nesse vídeo, nós vamos criar os seguintes gráficos:

• pizza

• linha

• coluna

• barra

Criando os seguintes gráficos:

• coluna empilhada

• barra empilhada

• rosca

Você também pode alterar a representação dos dados nos eixos x / y:

• clicar com botão direito

  • mudar tipo do gráfico

    • coluna, linha, pizza, barra ….

  • selecionar dados ...

    • alternar linha e coluna

      • mudar o modo que os dados são exibidos

Preste atenção em qual mensagem você está transmitindo com seus gráficos. A representação dos dados influencia na percepção dos dados. Você pode influenciar qual mensagem você está transmitindo com qual gráfico você escolher e como você o formata. Muito de criar gráficos é um processo de tentativa e erro:

• crie diferentes gráficos até encontrar o que você gosta

• teste as opções de formatação até encontrar o que você gosta

Você pode ROTACIONAR gráficos de pizza 3-D.

• clique no gráfico

  • mais ( + ) na direita

    • exibir/ocultar diferentes aspectos do gráfico

• clique em parte de um gráfico

  • delete essa parte pressionando delete

Quando formatar gráficos, abrace o espírito de exploração e experimentação. Preste atenção em o que você está clicando com o botão esquerdo e com o botão direito do mouse. Analise as opções disponíveis. Explore e experimente até você encontrar o que você quer.

• clique no gráfico

  • mais ( + ) na direita

    • exibir/ocultar diferentes aspectos do gráfico

  • pincel na direita

    • alterar aparencia

• clique com botão direito em um gráfico

  • alterar o tipo do gráfico

    • coluna, linha, pizza, barra ….

  • selecionar dados ...

    • alternar linha e coluna

      • alterar a maneira que os dados são exibidos

  • mover gráfico

    • nova planilha

  • formatar área do gráfico

• rotacionar um gráfico 3-D

Minigráficos são pequenos gráficos que ocupam uma única célula. 

Gráficos Combinados permite que você combine dois gráficos. Para criar um gráfico combinado é necessário seguir alguns passos:

• crie um gráfico com uma coluna de dados

• copie/cole uma segunda coluna de dados para dentro do gráfico

• selecione o gráfico, então mude o tipo do gráfico para Combinação e então um gráfico combinado

Você pode criar um eixo secundário para que os dados de diferentes escalas ainda possam ser ser representadas graficamente.

• considere incluir um eixo título para que os outros possam facilmente interpretar os dados.

Utilize a linha de tendência para exibir a tendência geral de alguns dados. Você pode utilizar as linhas de tendência com alguns gráficos. Para inserir uma linha de tendência, primeiro clique no seu gráfico e então no (+) na direita e então Linhas de Tendência. 

Utilize “obter dados externos do texto” o qual irá abrir a caixa de diálogo do “assistente de importação de texto” e então importe os dados de “142-exercício-prático-01.csv”. Crie um gráfico para os dados. Adicione uma linha de tendência ao gráfico.

Importante: certifique-se de utilizar os dados “close”

Caso pessoal: Um aspecto de todas as jornadas é que, alguma hora, elas nos desafiam e nos  esgotam. Durante a jornada, para completar a jornada, apenas continue.

Abra o arquivo associado com este exercício. Adicione minigráficos para os seguintes dados:

• Tarefas (Azul)

• Presença (Verde)

• Exercícios (Amarelo)

Abra o arquivo associado com este exercício. Crie dois gráficos de combinação:

• um

  • Desemprego

  • Aprovação Presidencial

• dois

  • Confiança do Consumidor

  • Aprovação Presidencial

Abra o arquivo associado com este exercício, calcule os subtotais desses dados:

• esporte

• vendas

Abra o arquivo associado com este exercício, crie um gráfico para os dados de venda. Exiba as vendas para pessoa por mês.

Você pode exibir simultaneamente diferentes áreas de uma planilha ao dividir a planilha.

• Ocultar / Reexibir

Você também pode exibir simultaneamente diferentes áreas de uma planilha ao ter várias janelas.

• nova janela

  • organizar tudo

  • alternar janelas

  • exibir lado-a-lado

    • rolagem sincronizada

    • redefinir posição da janela

Para imprimir corretamente no Excel, a primeira e mais importante informação que você deve saber é como visualizar o que está sendo impresso. Para fazer isso, nós podemos utilizar o seguinte

• menu exibir

  • visualização da quebra de página

  • layout da página

• menu layout da página

  • caixa de diálogo Configurar Página

    • visualizar impressão

Quando na Visualização da Quebra de Página, nós podemos ajustar o que será impresso ao arrastar as linhas azuis.

Você pode incluir títulos de linhas, títulos de colunas, e linhas de grade em suas planilhas de trabalho quando for imprimir.

Existem alguns itens adicionais na configuração de página a qual nós podemos ajustar antes de imprimir:

• orientação

  • paisagem

  • retrato

• ajustar para

• tamanho do papel

  • carta, legal, A4, etc

• margem

  • centralizar

Antes de imprimir nossas planilhas de trabalho, nós também podemos adicionar cabeçalhos e rodapés ao utilizarmos a caixa de diálogo de configuração de página.

Um truque que a maioria das pessoas não têm conhecimento é que você pode imprimir para arquivo. Imprimindo para arquivo permite salvar seu documento como um arquivo. Usualmente este arquivo é um PDF.

Utilize dividir janelas para editar duas áreas diferentes da planilha de trabalho.

Utilizando a pasta de trabalho associada a este exercício,

• crie quatro janelas múltiplas

• alterne entre as janelas

• organize tudo das janelas em uma maneira lado a lado

• oculte a terceira janela

• reexiba a terceira janela

• destaque a célula B4 para que passe a ter:

  • uma cor de fundo amarela

  • uma borda preta e sólida

Utilizando o modo de exibição “visualização da quebra de página”, ajuste as linhas azuis tracejadas para que sua planilha de trabalho seja impressa em apenas duas páginas.

Ainda utilizando o modo de exibição “visualização da quebra de página”, ajuste a “largura” e “altura” no menu layout de página para que sua planilha de trabalho apenas imprima duas páginas.

Utilizando a pasta de trabalho associada a este exercício, garanta que os seguintes itens sejam incluídos ao se imprimir

• títulos de linha

• títulos de colunas

• linhas de grade

Imprima para um arquivo PDF. Salve o arquivo como “159-exercício-prático-05.pdf”

Utilizando a pasta de trabalho associada a este exercício, garanta que os seguintes itens sejam incluídos ao se imprimir

• linhas a se repetir na parte superior - a primeira linha

• colunas a repetir à esquerda - a primeira coluna à esquerda

• linhas de grade

Imprima para um arquivo PDF. Salve o arquivo como “160-exercício-prático-06.pdf”. Compare esse arquivo com “159-exercício-prático-05.pdf”.

Utilizando a pasta de trabalho associada a este exercício, garanta que os seguintes itens sejam incluídos ao se imprimir

• utilize margem de .5 ao redor

  • ajuste as margens superior e inferior de acordo

• centralize a planilha no meio da página

• inclua um cabeçalho

  • área esquerda: seu nome

  • área central: o ícone “enso” - “161-exercício-prático-07.PNG”

  • área direita: a hora & data

• inclua um rodapé

  • área esquerda: nome do arquivo & localização do arquivo

  • área direita: página atual & total de páginas

Imprima para um arquivo PDF. Salve o arquivo como “161-exercício-prático-07.pdf”. Compare esse arquivo com “160-exercício-prático-06.pdf”.

Utilizando a pasta de trabalho associada a este exercício, ajuste a configuração de impressão para que o arquivo imprima corretamente. Imprima o arquivo para um arquivo PDF. Salve o arquivo como “162-exercício-prático-08.pdf”.