Apostila Excel Impressionador 3.0

APOSTILA

O FORMATO

que vai fazer você relembrar tudinho

Aula A - Aba B – tema C

Qual aula está sendo usada

Qual aba está sendo trabalhada

Página

Qual tema está sendo explicado

Explicação Do tema trabalhado

Print Screen da aba que está sendo explicada

O CONTEÚDO

que você precisa para mandar bem

O SUMÁRIO

Sumário

1 3

Atalhos 1 – 18 Formatação 19 – 36 Layout de Tabelas 37 – 52 Formatação Condicional 53 – 65 Remover Duplicatas & Classificar 66 – 69 Fórmulas Básicas, SE, E & OU 70 – 94 Fórmulas Financeiras 161 – 172 Gráficos 173 – 235 Botões 236 – 246

2 4

Procv/ Proch 95 – 117 Filtro e Subtotal 118 – 129 Somase (s) 130 – 137 Cont.se (s) 138 – 143 Fórmulas de Texto 144 – 160

Dashboards 247 – 273 Tabela Dinâmica 274 – 282 Solver 283 – 307 Gravação de Macro 308 – 317

Aula 1 - Aba Amarela – encontrar célula

1 Nosso objetivo é chegar até a célula AX627. Na imagem ao lado, repare que a seta azul aponta para a localização da célula selecionada. Dessa maneira, se escrevermos naquele local AX627 e apertarmos ENTER, seremos levados para essa célula.

Aula 1 - Aba Amarela – encontrar célula

2 Para ficar mais fácil de localizar essa célula, vamos dar um nome mais intuitivo a ela (INDICADOR, por exemplo). Para isso, basta clicar na caixinha onde tem o nome da célula e trocar para o nome que quisermos. Assim, sempre que quisermos ir até essa célula, basta escrevermos INDICADOR no lugar de AX627.

Aula 1 - Aba Amarela – encontrar célula

3 Para voltar para a célula A1, basta usar o atalho CTRL + Home. É só pensar como se fosse a “casa” do Excel, já que quando criamos uma aba nova, a célula que aparece selecionada é a A1.

Aula 1 – Aba Azul – selecionar linhas ou colunas

4 Nosso objetivo agora é selecionar linhas ou colunas usando apenas o teclado. Para isso, existem dois atalhos: Crtl + Space  seleciona linha Shift + Space  seleciona coluna

Aula 1 - Aba Azul – selecionar linhas ou colunas

5 Basta você ir com seu cursor até uma célula qualquer da linha ou da coluna que deseja selecionar e apertar o comando do atalho. Nesse caso, fomos até a célula J10 e apertamos: Crtl + Space para selecionar a coluna J inteira.

Aula 1 - Aba Azul – selecionar linhas ou colunas

6 Para selecionar uma linha inteira, basta selecionar uma célula qualquer dessa linha e usar o atalho Shift + Space. Nesse caso, para selecionar a linha 11, fomos até a célula D11 e apertamos o atalho.

Aula 1 - Aba Azul – selecionar linhas ou colunas

7 Para selecionar várias linhas, primeiro selecionamos uma linha e depois personalizamos essa seleção. Nesse caso, para selecionar a linha 16, fomos até a célula D16 e apertamos o atalho Shift + Space. Para personalizar a seleção e pegar as linhas 17 e 18 também, usamos o Shift + seta para baixo (duas vezes).

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

8 Nessa aba, o primeiro objetivo é descobrir a quantidade de funcionários que existem na planilha. Para isso, precisamos descobrir até onde vai essa tabela. Podemos usar o atalho Ctrl + Seta. Esse atalho permite a gente ir até o final de um conjunto de células preenchidas.

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

9 Como queremos ir até o final da lista de nomes, basta apertarmos Ctrl + seta para baixo. Assim, seremos levados à última célula preenchida da sequência. Caso houvesse alguma célula vazia no meio da lista, pararíamos na última célula preenchida antes dela com esse atalho. Podemos ver, então, que existem aproximadamente 1600 funcionários na empresa.

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

10 Nosso objetivo agora é criar uma nova linha entre as existentes 21 e 22, para adicionar o funcionário Alexandre Everaldo e depois criar uma coluna entre as existentes B e C. Usando 2 novos atalhos poderemos adicionar ou excluir linhas ou colunas selecionadas! Crtl+Mais - adiciona linhas ou colunas Crtl+Menos - exclui linhas ou colunas

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

11 O primeiro passo é selecionar uma das linhas usando o atalho já ensinado na Aba Azul (Shift + Space). Lembre-se: é necessário selecionar uma célula qualquer dessa linha e apertar o comando do atalho. Quando adicionamos uma nova linha, ela é criada acima da linha selecionada. Portanto, vamos selecionar a linha 22 e então usar o atalho Ctrl + Mais. Se o seu ‘+’ estiver junto do ‘=‘ no teclado, terá que apertar o Shift junto, então fica Ctrl + Shift + Mais.

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

12 Para adicionar uma nova coluna a lógica é a mesma. Basta selecionar a coluna desejada e então apertar o atalho Ctrl + Mais. Quando adicionamos uma nova coluna, ela é criada à esquerda da coluna que selecionamos. Então, como queremos adicionar uma coluna entre B e C, selecionamos a coluna C e usamos o atalho.

Aula 1 - Aba Verde – adicionar ou excluir linhas ou colunas

13 Nosso último objetivo desta aba é excluir os 10 últimos funcionários. Para fazer isso, selecionamos as 10 últimas linhas e usamos o atalho Ctrl + Menos.

Aula 1 - Aba Vermelha – copiar e colar (normal e como valor)

14 A coluna B possui diversos códigos de diferentes produtos. A ideia é copiarmos esses códigos para a lista da coluna D.

Para isso, primeiro precisamos selecionar todos os códigos. Basta chegar na célula B3 e usar o atalho Ctrl + Shift + Seta para baixo.

Aula 1 - Aba Vermelha – copiar e colar (normal e como valor)

15 Com isso, fomos até a última célula da sequência, mas selecionamos todo mundo pois apertamos o Shift junto. Agora que todos os códigos estão selecionados, para copiar as informações das células selecionadas apertamos Ctrl + C.

Aula 1 - Aba Vermelha – copiar e colar (normal e como valor)

16 Com os códigos copiados, chegamos na célula D3 e apertamos Ctrl + V (atalho para colar). Mas repare que quando colamos as informações, a formatação branca das células é levada junto. Portanto, para colar apenas os valores/informações das células e manter a formatação original da coluna D, vamos ter que colar “especial”.

Aula 1 - Aba Vermelha – copiar e colar (normal e como valor)

17 Para colar de diferentes maneiras, basta ir na guia Página Inicial e clicar na setinha de Colar para ver as opções de colagem. Se quisermos colar só os valores das células copiadas, selecionamos a opção ‘Colar Valores’. Podemos fazer isso também pelo atalho Alt + C + V + V.

Aula 1 - Aba Vermelha – copiar e colar (normal e como valor)

18 Repare que quando apertamos a tecla Alt, aparecem legendas na parte superior do Excel. Com elas, podemos acessar basicamente qualquer ferramenta que esteja dentro das guias simplesmente usando as teclas indicadas. Podemos, por exemplo, colar os códigos a partir da célula N3 transpondo as informações, com a sequência Alt + C + V + T (colar transposto). Assim, a coluna copiada virou uma linha.

Aula 1 – Formatação Numérica – editar células

19 Nessa aba vamos aprender algumas formatações mais básicas e outras um pouco mais personalizadas. Toda a parte de formatação de células fica na guia Página Inicial.

Aula 1 – Formatação Numérica – editar células

20 Para formatar a célula C2 como porcentagem, usamos as duas ferramentas indicadas, a primeira para formatar como porcentagem e a segunda para aumentar o número de casas decimais.

Aula 1 – Formatação Numérica – editar células

21

Barra de Fórmulas Atalho F2

Repare que quando aumentamos uma casa decimal, o número que era 51% agora aparece como 50,9%. Isso acontece porque o número que está efetivamente escrito na célula é 50,85%. Antes estava aparecendo 51% pois estávamos visualizando sem casas decimais, então enxergamos como se o número estivesse arredondado. Na prática, para fazer contas, a formatação não interfere. Vale o valor que está escrito dentro da célula. Para visualizar esse valor, podemos ver pela barra de fórmulas ou apertando o atalho F2 para “entrar” dentro do texto da célula.

Aula 1 – Formatação Numérica – editar células

22 O próximo passo é formatar o número 33924 como data. Isso é possível pois todo número no Excel possui uma data correspondente. Exemplo: o número 33924 equivale à data 16/11/1992, 33925 equivale ao dia 17/11/1992 e por aí vai. O número 1 equivale ao dia 1/1/1900. A grande vantagem dessa relação entre números e datas dentro do Excel é que conseguimos fazer contas usando datas.

Aula 1 – Formatação Numérica – editar células

23 Beleza, mas o que a gente quer é deixar essa data no formato mês – ano, para ficar algo do tipo nov - 92. Como essa é uma formatação um pouco mais personalizada, vamos precisar usar a caixa de formatação. Basta usar o atalho Ctrl + 1 para abrila.

Aula 1 – Formatação Numérica – editar células

24 Nessa caixa de formatação podemos escolher dentre várias opções. Na parte de data, tem a opção mês – ano, que está indicada como mar – 12, que é justamente a formatação que queremos.

Aula 1 – Formatação Numérica – editar células

25 Como a célula C4 está com um número grande e difícil de visualizar intuitivamente, a ideia é colocar o separador de milhar para facilitar a visualização. Como é uma formatação um pouco mais simples, já aparece pra gente na parte de formatação de número na guia Página Inicial, no local indicado.

Aula 1 – Formatação Numérica – editar células

26 Por último, na célula C5 queremos formatar como Moeda. Mas se o número for negativo, tem que aparecer em vermelho para indicar um prejuízo. É uma espécie de formatação condicional. Como é um pouco mais personalizada, para criála vamos usar a caixa de formatação, com o atalho Ctrl + 1.

Aula 1 – Formatação Numérica – editar células

27 Dentro da caixa de formatação, na parte de Moeda, podemos escolher a quantidade de casas decimais que vão aparecer na célula, além de escolher qual formato queremos que apareça quando o número for negativo. Basta escolher uma das duas opções em que o a moeda aparece em vermelho.

Aula 1 – Formatação Numérica – editar células

28 Dessa forma, sempre que o valor dessa célula for negativo, vai aparecer escrito em vermelho para destacar e deixar a visualização mais intuitiva.

Aula 1 – Bordas e Pincel – Formatação de tabelas

29 Nessa aba, vamos aprender a formatar tabelas e a copiar formatações já prontas. A ideia é usar a primeira tabela como base para deixar as outras com a formatação idêntica. Para isso, vamos ver primeiro a parte de formatação de bordas.

Aula 1 – Bordas e Pincel – Formatação de tabelas

30 Para formatar as bordas é bem simples. Primeiro selecionamos as células onde queremos aplicar a formatação. Depois, abrimos a caixa de formatação e selecionamos a parte de Borda. Então, basta escolher o tipo de borda na caixinha da esquerda e depois definir onde queremos aplicá-la usando a parte da direita.

É só ir clicando nas opções dentro dos quadradinhos para ir colocando a borda na parte superior, inferior, no meio, na diagonal e por aí vai. Para tirar a borda, é só clicar novamente.

Aula 1 – Bordas e Pincel – Formatação de tabelas

31 Feitas as bordas, fica faltando pintar as cores das letras (Fonte) de preto, as cores de fundo das células (Preenchimento) de branco e ajustar o alinhamento do texto, que deve ficar todo à esquerda. Para fazer isso, usamos as ferramentas que estão marcadas na guia Página Inicial.

Aula 1 – Bordas e Pincel – Formatação de tabelas

32 Mas como já temos a formatação pronta nas tabelas de cima, podemos aproveitar e copiar só a formatação. Para isso, primeiro selecionamos a tabela que está com o formato que desejamos (uma das duas primeiras que já estão prontas), e usamos o atalho para copiar (Ctrl + c).

Aula 1 – Bordas e Pincel – Formatação de tabelas

33 Agora, selecionamos a tabela onde queremos jogar o formato e finalmente colamos só a formatação. Podemos fazer isso usando também a sequência de atalhos Alt + c + v + f + f + Enter.

Aula 1 – Bordas e Pincel – Formatação de tabelas

34 Como copiar e colar só a formatação é uma prática tão comum no Excel, já existe uma ferramenta específica para isso, chamada Pincel de Formatação, que está indicada. Para usar, é só selecionar a formatação que você quer copiar e então clicar onde você quer jogar a formatação.

Aula 1 – Bordas e Pincel – Formatação de tabelas

35 Para jogar a formatação para a tabela de baixo, simplesmente clicamos na célula B20 e então já colará o formato para a tabela toda.

Aula 1 – Bordas e Pincel – Formatação de tabelas

36 Se quisermos jogar a formatação para várias tabelas diferentes sem ter que ficar clicando no pincel a cada vez, basta dar duplo clique no pincel de formatação. Assim, se clicarmos em qualquer célula, a formatação será colada novamente e o pincel ficará selecionado até apertarmos Esc.

Aula 1 – Validação de Dados – Congelar Painéis

37 Nessa aba, vamos começar preenchendo um novo produto lá no final da tabela. Para ir até lá embaixo, usamos o atalho Ctrl + Seta para baixo.

Aula 1 – Validação de Dados – Congelar Painéis

38 Mas repare que quando estamos no final da tabela, os títulos não estão mais visíveis. Então, para deixar mais intuitivo de visualizar, podemos fixar a primeira linha, como se fosse um cabeçalho. Vamos então voltar lá para cima com o atalho Ctrl + Seta para cima e fazer isso.

Aula 1 – Validação de Dados – Congelar Painéis

39 Essa ferramenta é o que chamamos de Congelar Painéis. Lá na guia Exibir/Exibição, vai ter essa opção. Nesse caso, queremos congelar a primeira linha (linha superior).

Aula 1 – Validação de Dados – Congelar Painéis

40 Dessa forma, independentemente de onde estivermos na planilha, a primeira linha estará visível. Assim fica bem mais intuitivo e prático para preencher as informações.

Podemos, também, congelar a primeira coluna. Assim, se formos bem para a direita na nossa tabela, a primeira coluna continuará sendo exibida.

Aula 1 – Validação de Dados – Congelar Painéis

41 Uma outra opção que temos é congelar linhas e colunas ao mesmo tempo. Para isso, basta clicar em uma célula e selecionar a primeira opção em ‘Congelar Painéis’.

Assim, o Excel irá congelar todas as linhas acima e todas as colunas à esquerda da célula que selecionamos. Portanto, se quisermos congelar a 1ª linha e as duas primeiras colunas, por exemplo, devemos selecionar a célula C2.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

42

Agora repare na coluna E, a coluna de Origem. Você, que montou a planilha, sabe que ela deve ser preenchida com ‘Nacional’ ou ‘Importado’. Mas se você mandar essa planilha para outras pessoas, elas provavelmente não vão saber como deve preencher, e aí se uma pessoa escrever ‘Internacional’, outra escrever ‘China’ e assim vai, na hora de analisar essas informações daria muito trabalho para padronizar tudo. Para evitar isso, criamos uma lista com opções para a pessoa poder escolher, usando a Validação de Dados.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

43 Para usar a validação de dados, primeiro selecionamos onde queremos aplicar. Portanto, vamos selecionar a coluna E toda. Depois, na guia dados, selecionamos a opção ‘Validação de Dados’.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

44 Nessa caixinha, podemos limitar o que o usuário vai poder colocar naquelas células que selecionamos. Isso significa que, se escolhermos apenas permitir número inteiro, por exemplo, e a pessoa escrever um texto naquela célula, o Excel não vai deixar e vai aparecer uma mensagem de erro. Como queremos criar uma lista de valores, vamos selecionar a opção de Lista.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

45 Agora, para inserir as informações da lista, podemos escrever à mão, separando por ponto e vírgula, ou selecionar as células que contém as informações.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

46 Feito isso, quando alguém selecionar alguma célula da coluna E e clicar na setinha para baixo, aparecerá uma lista para a pessoa escolher uma opção. Podemos abrir essa lista também pelo atalho Alt + Seta para baixo.

Aula 1 – Validação de Dados – Validação de Dados (Lista)

47 Caso a pessoa tente escrever algo diferente do que está na lista, o Excel não irá deixar.

Aula 1 – Validação de Dados – Ocultar/Reexibir Linhas e Colunas

48 Mas agora repare que as informações que selecionamos na Validação de Dados estão soltas na planilha. Se alguém por acaso deletar essas células, vai apagar os dados da lista. Para não correr esse risco, ocultamos a coluna. Para isso, basta clicar com o botão direito em cima da letra da coluna e selecionar a opção ‘Ocultar’.

Aula 1 – Validação de Dados – Ocultar/Reexibir Linhas e Colunas

49 Assim, as informações continuam lá mas não estão sendo mostradas. Para exibir a coluna novamente, basta selecionar várias colunas, desde que pegue a coluna que queremos (ex. para Reexibir a coluna G, podemos selecionar as colunas E até H), clicar com o botão direito em uma delas e selecionar a opção ‘Reexibir’.

Aula 1 – Validação de Dados – Agrupar/Desagrupar Linhas e Colunas

50 Uma alternativa que temos no lugar de ocultar linhas/colunas é fazer isso de forma que qualquer pessoa que esteja mexendo na planilha consiga Esconder e Reexibir as informações de forma mais intuitiva, mesmo que não saiba muito Excel. A ferramenta que vamos usar para isso é a de Agrupar, que fica na guia Dados. Primeiro, selecionamos as colunas/linhas que desejamos e então, clicamos em Agrupar.

Aula 1 – Validação de Dados – Agrupar/Desagrupar Linhas e Colunas

51 Uma alternativa que temos no lugar de ocultar linhas/colunas é fazer isso de forma que qualquer pessoa que esteja mexendo na planilha consiga Esconder e Reexibir as informações de forma mais intuitiva, mesmo que não saiba muito Excel. A ferramenta que vamos usar para isso é a de Agrupar, que fica na guia Dados. Primeiro, selecionamos as colunas/linhas que desejamos e então, clicamos em Agrupar.

Aula 1 – Validação de Dados – Agrupar/Desagrupar Linhas e Colunas

52 Outra possibilidade é Agruparmos conjuntos diferentes de Linhas/Colunas. Assim, criamos diversos níveis de informação (ex: para estagiário, coordenador, gerente e por aí vai). Além de clicarmos no botão de Menos ou de Mais para esconder ou reexibir as informações, podemos usar os números no canto esquerdo, que representam o nível de cada informação.

Aula 1 – Formatação Condicional – formatar tabela passo-a-passo

53 O objetivo dessa aba é ajeitar a tabela para depois aplicar a formatação condicional e deixar as informações mais visuais e mais fáceis de ser analisadas.

Aula 1 – Formatação Condicional – ajustar tamanho de linhas ou colunas

54

Para ajustar o tamanho das colunas da tabela da esquerda devemos selecionar todas as colunas em que queremos que a alteração seja feita e, em seguida, ir com o cursor na interseção de duas das colunas e dar um duplo clique.

Por exemplo, clicar entre B e C, ou entre C e D. Assim, o Excel irá fazer um ajuste automático, de forma que as colunas fiquem com um tamanho certo, com todos os textos da tabela cabendo no espaço da célula.

Aula 1 - Formatação Condicional – Formatação Condicional

55 Agora a ideia é pintar de vermelho as células dos funcionários com mais de 40 anos usando Formatação Condicional. O primeiro passo é selecionar as células em que queremos aplicar a formatação. No caso, as células da coluna B, que contém as idades dos funcionários.

Aula 1 - Formatação Condicional – Formatação Condicional

56 Agora é só ir na Guia Página Inicial -> Formatação Condicional e escolher o critério de formatação. Como queremos realçar as células que têm o valor maior que 40, vamos selecionar a opção ‘É Maior do que’.

Aula 1 - Formatação Condicional – Formatação Condicional

57 Quando fizermos isso, abrirá essa caixa que permite a gente escolher o critério e a formatação. Se quisermos uma formatação um pouco mais personalizada, clicamos na opção ‘Formato Personalizado’ e então aparecerá a caixa de formatação.

Aula 1 - Formatação Condicional – Formatação Condicional

58 Nessa caixa, podemos definir vários formatos que serão aplicados nessa formatação condicional, como formato do número (moeda, contábil, data etc), Fonte (cor da letra), borda ou preenchimento (cor do fundo da célula).

Como queremos apenas pintar as células de vermelho, vamos escolher o preenchimento vermelho e clicar em OK.

Aula 1 - Formatação Condicional – Formatação Condicional

59 Agora todas as células maiores que 40 estão de acordo com a formatação que definimos. Se alterarmos o valor de qualquer célula da coluna B, a formatação também mudará automaticamente de acordo com o critério que escolhemos.

Se quisermos excluir alguma formatação condicional que fizemos, vamos em Formatação Condicional > Limpar Regras.

Aula 1 - Formatação Condicional – Formatação Condicional

60 O próximo desafio é criar uma formatação condicional com os ícones semafóricos para representar as faixas de salário. O primeiro passo é sempre selecionar as células onde vamos aplicar a formatação condicional. No caso, os salários da coluna D.

Aula 1 - Formatação Condicional – Formatação Condicional

61 Quando clicar na opção de ícone desejada, o Excel irá aplicar a formatação nas células que estavam selecionadas. Perceba que a regra que o Excel escolhe para alocar as bolinhas de acordo com as notas não é necessariamente a que queremos. Por isso, em seguida teremos que fazer uma edição, para fazer com que as bolinhas apareçam de acordo com a regra pré-estabelecida.

Aula 1 - Formatação Condicional – Formatação Condicional

62 Para editar a regra, basta clicar mais uma vez em Formatação Condicional e, em seguida, em Gerenciar Regras.

Depois que clicarmos em Gerenciar Regras irá aparecer um retângulo como o da figura abaixo. Iremos clicar aonde está escrito: Conjunto de Ícones e em Editar Regra para poder confirguar a regra das cores das bolinhas.

Aula 1 - Formatação Condicional – Formatação Condicional

63 Agora de forma bem intuitiva conseguimos ajustar essa formatação condicional e fazê-la seguir as regras que tínhamos estabelecido. Na figura ao lado temos a condição estabelecida inicialmente.

Aula 1 - Formatação Condicional – Formatação Condicional

64 Por último, queremos aplicar uma formatação que pinte de verde quem tiver o cargo de Analista na coluna C. Basta selecionar as células onde vamos aplicar a formatação e então escolher a opção de formatação condicional ‘Texto que Contém’. Na caixinha de edição escolhemos o texto que será o critério da formatação e depois qual formato queremos aplicar nessas células.

Aula 1 - Formatação Condicional – Formatação Condicional

65 Existem vários outros tipos de formatação condicional. Todos são muito intuitivos e seguem a lógica do exemplos que fizemos. Primeiro selecionamos o conjunto de células onde queremos que a formatação valha, depois criamos a regra e selecionamos a formatação que será aplicada para quando a condição for verdadeira. Todas as opções podem também ser acessadas em ‘Nova Regra’. Tente dar uma explorada no que cada uma faz.

Aula 1 – Remover Duplicatas – Remover Duplicatas

66 Nessa aba, veremos duas ferramentas simples porém muito úteis: Remover Duplicatas e Ordenar em Ordem Alfabética.

Aula 1 – Remover Duplicatas – Remover Duplicatas

67 Para remover os nomes duplicados na tabela, primeiro selecionamos as células da coluna desejada, no caso a coluna A. Então, vamos na Guia Dados e clicamos na ferramenta ‘Remover Duplicatas’. Quando abrir essa caixa, basta clicar em OK.

Aula 1 – Remover Duplicatas – Remover Duplicatas

68 Os dois nomes que estavam duplicados na coluna A foram removidos. Agora, todos os valores dessa coluna são exclusivos, ou seja, aparecem uma única vez.

Aula 1 – Remover Duplicatas – classificar em ordem alfabética

69 Para ordenar os nomes em ordem alfabética, o processo é bem parecido. Selecionamos as informações da coluna A e na Guia Dados, clicamos na opção indicada para ordenar. Se desejarmos, podemos também colocar em ordem inversa da alfabética, selecionando a opção de baixo (Classificar de Z a A).

Aula 1 – Operações Básicas – Soma

70 Sempre que formos colocar uma fórmula dentro do Excel, começamos escrevendo ‘=‘.

Para somar os valores 1 e 2, por exemplo, há algumas maneiras. A primeira delas é selecionar a célula C2, colocar o símbolo ‘+’ e depois selecionar a célula C3. Podemos selecionar as células também com as setas do teclado.

Aula 1 – Operações Básicas – Soma

71 Outra opção é usar a fórmula SOMA do Excel. Dentro dela, vamos colocar como argumentos os números/células que queremos somar. Podemos colocar várias separando cada uma por ponto e vírgula (;).

Aula 1 – Operações Básicas – Soma

72 Caso as células estejam em sequência, podemos selecionar várias de uma vez. Nesse caso, o Excel escreve C2:C5, ou seja, C2 até C5.

Aula 1 – Operações Básicas – Subtração

73 No caso da subtração, não existe uma fórmula no Excel. Portanto, selecionamos as células que queremos subtrair usando o sinal de menos (-).

Aula 1 – Operações Básicas – Multiplicação

74 No caso da multiplicação, podemos usar o operador asterisco (*) para multiplicar. Mas no Excel também existe a fórmula MULT (ou PROD, nas versões mais antigas). Essas fórmulas vão retornar a multiplicação de todos os números que passarmos.

Aula 1 – Operações Básicas – Divisão

75 Para o caso da divisão, assim como na subtração, não existe uma fórmula específica no Excel.

Portanto, para dividir, usamos a barra ( / ) como operador de divisão.

Aula 1 – Operações Básicas – Máximo

76 A fórmula de MÁXIMO retorna o maior valor dentre uma lista de valores. Não pode esquecer de colocar o acento para usar a fórmula.

Aula 1 – Operações Básicas – Mínimo

77 A fórmula de MÍNIMO retorna o menor valor dentre uma lista de valores.

Aula 1 – Operações Básicas – Maior

78 A fórmula MAIOR funciona de maneira similar à fórmula MÁXIMO. A diferença é que agora ela pede um segundo argumento: um ‘K’. Esse K nada mais é que um número, que usamos para dizer se queremos o primeiro maior valor da lista, o segundo maior, terceiro maior e assim vai. Portanto, se quisermos saber o 3º maior valor da lista, botamos o K=3.

Aula 1 – Operações Básicas – Menor

79 Da mesma forma, na fórmula MENOR passamos o K para dizer se queremos o 1º menor, 2º menor, 3º menor e assim vai.

Aula 1 – Operações Básicas – Média

80 A fórmula MÉDIA retorna a média aritmética dos argumentos.

Aula 1 – Operações Básicas – Arredondar

81 Para arredondar um valor, usamos a fórmula ARRED. Passamos dois argumentos:

-Qual valor queremos arredondar -Quantas casas decimais

Aula 1 – Operações Básicas – Cálculo Automático/Manual

82 Repare que se alterarmos o valor de uma célula, todas as fórmulas que usavam aquela célula atualizam.

Se tivermos uma planilha com 60 mil linhas e todas com fórmulas, é grande a chance de a planilha travar se tiver que atualizar as fórmulas a cada célula que alterarmos. Nesses casos, utilizamos o cálculo Manual.

Aula 1 – Operações Básicas – Cálculo Automático/Manual

83 Para mudar o cálculo das fórmulas para o modo Manual, basta ir na guia Fórmulas -> Opções de Cálculo ->Manual. Agora, as fórmulas só serão atualizadas quando apertarmos a tecla F9 ou alterarmos o cálculo para Automático novamente.

Aula 1 - SE COMPOSTO – Fórmula SE

84 A fórmula SE é certamente uma das mais requisitadas no uso diário do Excel dentro do mercado de trabalho. A estrutura da mesma é simples. Primeiro estabelecemos uma condição, um critério. Se esa condição for verdadeira, acontece uma coisa. Se for falsa, acontece outra. Portanto, observe: estamos falando de 3 argumentos. A condição, o valor se verdadeiro e o valor se falso. Parece simples, não é?

Aula 1 - SE COMPOSTO – Fórmula SE

85 No caso ao lado nossa intenção é criar uma fórmula abaixo de “Situação” que irá retratar a situação do aluno, dada a média que ele tem. O quadro colorido na figura ao lado mostra quais são as faixas de notas e suas respectivas consequências. Menor que 4, reprovado. Entre 4 e 6, Verificação Suplementar (Prova Final). Maior que 6, aprovado.

Aula 1 - SE COMPOSTO – Fórmula SE

86 Olhe como devemos começar a escrever a fórmula! Se E8 (média) for maior maior do que 6; D5 (Aprovado). Quando apertar “;” mais uma vez irei começar a descrever o que acontece caso minha condição dê falsa. Perceba que esse terceiro argumento só vai acontecer caso a média seja menor do que 6. Faz sentido?

Aula 1 - SE COMPOSTO – Fórmula SE

87 Beleza, agora dá uma olhada no que colocamos para o valor se falso: um outro SE. Por que?

A ideia é fragmentar mais uma vez. Se a média do aluno não for maior que 6, o aluno pode estar em VS ou Reprovado. Não é? Esse segundo SE irá fazer essa distinção. O valor verdadeiro desse segundo SE é média maior ou igual a 4. Observe, não colocamos que deve ser menor do que 6, mas se ele chegou nessa parte da fórmula, obrigatoriamente a média é menor do que 6. Se não teria parado no primeiro valor se verdadeiro.

Aula 1 - SE COMPOSTO – Fórmula SE

88 Continuando o que estávamos fazendo! Se for maior ou igual a 4 (e menor do que 6), o aluno está de VS (valor se verdadeiro do segundo SE). A única opção que falta é “Reprovado”. Se o alunos tirou menos que 4 está Reprovado (valor se falso do segundo SE).

Aula 1 - SE COMPOSTO – Fórmula SE

89 Importante! Você pode usar vários SE dentro de SE. Sempre que tiver N condições para descrever, terá que usar pelo menos N-1 estruturas de SE dentro de SE. No caso do exemplo à esquerda, por exemplo, temos 3 possibilibilidades (Aprovado, VS ou Reprovado) e utilizamos 2 SE. Se fossem 6 possibilidades, teríamos que utilizar 5 SE.

Aula 1 – E OU – Fórmula SE

90 Nessa aba queremos usar a fórmula SE para calcular o bônus do vendedor caso atenda ao seguinte critério: -Se vendeu entre 50 e 100 mil, ganha bônus de 13%

-Caso contrário, não ganha bônus.

Aula 1 – E OU – Fórmula SE

91 Repare que dessa vez, precisamos não só de um teste lógico, mas de dois. Pois para ganhar o bônus, as vendas têm que estar acima de 50 mil E abaixo de 100 mil. Para colocar vários testes lógicos dentro do SE, usamos a fórmula E.

Assim, dentro do argumento ‘teste_lógico’ da fórmula SE, podemos colocar quantos testes quisermos. Caso todos sejam verdadeiros, a fórmula E retornará VERDADEIRO. Caso um deles ou mais sejam falsos, a fórmula E retornará FALSO.

Aula 1 – E OU – Fórmula SE

92 Caso atenda aos dois critérios (acima de 50 mil e abaixo de 100 mil), o bônus será 13% x Vendas. Caso contrário, o bônus será 0.

Aula 1 – E OU – Fórmula SE

93 Na coluna do Método OU, a ideia é exatamente a mesma: calcular o bônus do vendedor, só que agora vamos fazer isso usando a fórmula OU, que também permite colocar vários testes lógicos. Pensando em termos práticos, se o valor de vendas for menor que 50 mil OU maior que 100 mil, o vendedor NÃO ganhará bônus. A fórmula OU retorna VERDADEIRO caso pelo menos um dos testes seja verdadeiro. Retorna FALSO caso todos os testes sejam falsos.

Aula 1 – E OU – Fórmula SE

94 A ordem dos testes lógicos dentro da fórmula OU tanto faz. Portanto, a fórmula deve ficar mais ou menos como na figura e dar as mesmas respostas que na coluna D.

Aula 2 – Procv & Proch – PROCH

95 Agora a ideia é mostrar na célula G6 as vendas do vendedor escolhido nas células F5 e G5 que estão mescladas e têm uma Validação de Dados do tipo Lista. Para isso, usaremos a fórmula PROCH, que procura um valor na horizontal (o nome do vendedor) e retorna uma informação da mesma coluna.

Aula 1 – PROCV & PROCH – PROCH

96 A primeira informação que a fórmula pede é o Valor Procurado, que é de quem estamos buscando a informação. No caso queremos uma informação da Thalita, portanto ela será nosso valor procurado. Repare que ao invés de selecionar a célula E2 onde está escrito Sofia vamos selecionar a célula F5 que possui uma validação de dados com os nomes dos vendedores. Assim, quando selecionarmos um novo vendedor, o valor de vendas será atualizado.

Aula 1 – PROCV & PROCH – PROCH

97 Agora, precisamos selecionar a Matriz Tabela. Para o PROCH, a Matriz Tabela deve começar na linha onde está o Valor Procurado e ir pelo menos até a linha da informação que queremos. Podemos selecionar só o intervalo B2:G3 ou selecionar as linhas 2 e 3 inteiras. Normalmente usamos a segunda opção, pois se for inserida uma nova informação, como um novo vendedor, já estará sendo contemplado.

Aula 1 – PROCV & PROCH – PROCH

98 A próxima informação que temos que passar é o número índice de linha, que é qual linha da Matriz Tabela queremos como resposta. Como queremos a informação das vendas, será a 2ª linha. Portanto, basta escrever o número 2 no argumento da fórmula.

Aula 1 – PROCV & PROCH – PROCH

99 Por último, a informação que a fórmula pede é [procurar_intervalo]. Ou seja, se estamos procurando o Valor Procurado exatamente do jeito que está escrito ou se estamos procurando por algo aproximado a ele.

Valor Exato: 0 (FALSO) Valor Aproximado: 1 (VERDADEIRO) Como estamos procurando Thalita e não uma palavra parecida, vamos colocar o 0 e fechar o parênteses.

Aula 1 – PROCV & PROCH – PROCH

100 Quando dermos Enter, aparecerá o valor de vendas da Thalita. Se alterarmos o nome do vendedor, o PROCH automaticamente retornará o valor de vendas do vendedor escolhido.

Aula 1 – PROCV & PROCH – PROCH

101 OBS: Assim como fizemos agora, em 99% dos casos usamos a opção de correspondência exata do valor procurado. O único estilo de caso em que se usa o valor aproximado é o que veremos a seguir.

Aula 1 – PROCV & PROCH – PROCV

102 Agora a ideia é usar o Procv (procura na vertical) na célula G7 para descobrir a classificação do vendedor, de acordo com o valor de vendas retornado na célula G6.

Aula 1 – PROCV & PROCH – PROCV

103 O valor procurado será o valor de vendas da célula F6; A matriz tabela precisa contemplar desde a coluna B (que é onde será procurado o valor de vendas) até pelo menos a coluna D, que contém as informações que queremos como resposta (as classificações).

Aula 1 – PROCV & PROCH – PROCV

104 Da matriz tabela selecionada, como queremos a classificação como resposta, o número índice de coluna é 3, pois queremos como retorno a informação da 3ª coluna da matriz. Dessa vez, procurar_intervalo será 1 (ou VERDADEIRO), pois estamos procurando um valor aproximado ao valor de vendas.

Aula 1 – PROCV & PROCH – PROCV

105 Como a Thalita vendeu entre 50 e 70 mil, sua classificação foi ‘Bom’. Se alterarmos o vendedor da célula F5 para Mateus, por exemplo, sua classificação deve ser Regular (entre 30 e 50 mil).

Aula 1 – PROCV & PROCH – PROCV

106 OBS: O PROCV está procurando o valor de vendas apenas na coluna B, sem olhar para a coluna C. Por que está funcionando então? Como o PROCV não irá encontrar exatamente o valor de vendas (exemplo: R$47.213), ele achará o valor mais próximo abaixo dele (30 mil). Portanto, para qualquer valor entre R$30.000 e R$49.999,99, a classificação retornada será REGULAR. Entre R$50.000 e R$69.999,99 a classificação será BOM e assim vai.

Aula 1 – Análise Procv – PROCV

107 Nessa aba, queremos fazer uma fórmula para procurar o código na aba ‘Base Procv’ e retornar o seu valor de vendas.

Aula 1 – Análise Procv – PROCV

108 Os códigos estão na coluna A da aba ‘Base Procv’ e queremos descobrir as vendas do produto. Como os códigos estão dispostos na vertical, faremos um PROCV. Portanto, a matriz tabela deverá ir da Coluna A (onde irá procurar o código) e ir pelo menos até a coluna C.

Aula 1 – Análise Procv – PROCV

109 O valor procurado será o código da célula ao lado. Queremos fazer uma fórmula para o primeiro código e depois arrastar para baixo para funcionar para todo mundo.

Aula 1 – Análise Procv – PROCV

110 Repare que quando selecionamos células de outra aba em uma fórmula, o Excel escreve o nome da aba com exclamação no final. Sempre que fizermos isso, escrevemos ponto e vírgula ( ; ) antes de voltar para a aba em que estamos escrevendo a fórmula. Isso porque às vezes o Excel entende que ainda estamos selecionando, então ao invés de pegar as colunas A:C da aba ‘Base Procv’, ele pode acabar pegando as colunas A:C da aba ‘Análise Procv’ se mudarmos de aba sem dar ponto e vírgula antes.

Aula 1 – Análise Procv – PROCV

111 Normalmente, na matriz tabela selecionamos as colunas inteiras, pois se for adicionada alguma informação a mais naquelas colunas, a fórmula já vai contemplando ela. Além disso, quando selecionamos as colunas inteiras, não precisamos nos preocupar em trancar as células da matriz tabela se formos arrastar a fórmula para baixo (a coluna toda não tem como ser arrastada para baixo).

Aula 1 – Análise Procv – PROCV

112 O número índice de coluna é 3 pois queremos como retorno a 3ª coluna da matriz tabela.

Por último, colocamos 0 pois estamos procurando o código exatamente como está escrito (valor exato). Feito isso, podemos arrastar a fórmula para funcionar para todos os códigos.

Aula 1 – Análise Procv – PROCV

113 Repare que um código deu erro: #N/D (não disponível). Isso significa que não conseguiu achar o código na coluna A. Se olharmos a aba ‘Base Procv’, veremos que havia um código marcado na coluna B, que é justamente o código não encontrado.

Aula 1 – Análise Procv – PROCV

114 Portanto, precisamos fazer uma fórmula que procure o código na coluna A e, caso não encontre, procure na coluna B. Existe uma extensão da fórmula SE, que é a fórmula SEERRO. Ela basicamente nos dá duas chances de fazer alguma coisa. Portanto, usaremos ela para fazer essas duas fórmulas de PROCV.

Aula 1 – Análise Procv – PROCV

115 A fórmula SEERRO vai entrar logo no início. Ela pede dois argumentos: ‘Valor’ – o que queremos tentar fazer. ‘Valor se erro’ – O que queremos fazer caso a primeira tentativa retorne um erro.

A primeira coisa que queremos tentar é procurar o código na coluna A, que é o PROCV que já fizemos. Portanto, o ‘valor se erro’ será um outro PROCV que procura o código na coluna B (portanto, a matriz tabela deverá começar na coluna B).

Aula 1 – Análise Procv – PROCV

116 Repare que no segundo PROCV, como nossa matriz tabela é das colunas B:C, agora nosso número índice de coluna é 2, pois agora a informação da descrição está na segunda coluna da matriz tabela.

Aula 1 – Análise Procv – PROCV

117 Agora, basta arrastar a fórmula para todo mundo. Repare que ajeitamos a fórmula para a primeira célula e arrastamos para todo mundo, no lugar de ajeitar apenas para o código que tinha dado erro, pois se vários tivessem dado erro, não iríamos querer ajeitar cada uma das fórmulas.

Aula 1 – Base de Dados – Filtro e Subtotal

118 Agora veremos uma ferramenta que é muito utilizada e extremamente útil: o Filtro. Essa ferramenta permite que possamos apenas exibir as informações que queremos analisar de uma tabela. Essa base de dados representa extrações de petróleo. Então, por exemplo, no dia 5/1/2015, a Empresa 4 extraiu 638.894 barris de petróleo na Plataforma CSP.

Usaremos essa base de dados para realizar diversas análises, tanto utilizando a ferramenta de filtro quanto utilizando fórmulas.

Aula 1 – Base de Dados – Filtro e Subtotal

119 Por exemplo, se quisermos ver todas as extrações que foram realizadas apenas na plataforma CAR, basta clicar na setinha de ‘Plataforma’, desmarcar todo mundo e marcar apenas ‘CAR’.

Aula 1 – Base de Dados – Filtro e Subtotal

120 Assim, só aparecem as linhas correspondentes ao que filtramos. As demais linhas da tabela ficam ocultas.

Aula 1 – Base de Dados – Filtro e Subtotal

121 Para desfazer o filtro, clicamos em ‘Limpar Filtro’. Repare que quando filtramos o que queremos, as demais linhas da tabela ficam ocultas.

Aula 1 – Base de Dados – Filtro e Subtotal

122 Para colocar ou tirar a ferramenta do Filtro, basta selecionar os títulos da tabela, ir na guia Dados, e marcar a opção Filtro. O Excel apenas permite colocar Filtro em um conjunto de células adjacentes. Não podemos colocar um filtro, por exemplo, nas colunas A e B e um outro Filtro na coluna D na mesma aba. A única opção, nesse caso, seria botar um filtro único desde a coluna A até a coluna D.

Aula 1 – Base de Dados – Filtro e Subtotal

123 Outra opção interessante da ferramenta, são os filtros de número. Se quisermos saber, por exemplo, quais empresas extraíram mais do que 700 mil barris de petróleo, basta ir no filtro de Volume -> Filtros de Número -> É Maior do que.

Aula 1 – Base de Dados – Filtro e Subtotal

124 Nessa caixa, podemos personalizar o filtro. Portanto, colocamos os valores que queremos analisar, com volume acima de 700 mil.

Aula 1 – Base de Dados – Filtro e Subtotal

125 Há apenas um cuidado que temos que ter ao utilizar a ferramenta do Filtro:

Vamos usar a fórmula SOMA para calcular o total de volume extraído no ano analisado:

Aula 1 – Base de Dados – Filtro e Subtotal

126 Beleza, em 2015 foram extraídos no total 196.084.887 barris de petróleo. E se quisermos saber o volume extraído apenas pela Empresa 1? Vamos filtrar apenas a Empresa 1 para ver.

Aula 1 – Base de Dados – Filtro e Subtotal

127 Repare que continua dando o mesmo valor de antes. Ou seja, a fórmula SOMA considera também quem está filtrado. Para resolver esse problema, usamos a fórmula SUBTOTAL. Com ela podemos escolher dentre várias operações matemáticas e calcular apenas para as células que estão visíveis. Primeiro vamos remover o filtro que fizemos.

Aula 1 – Base de Dados – Filtro e Subtotal

128 Basta escolhermos o número da operação e depois as células. Quando filtrarmos novamente pela Empresa, a fórmula SUBTOTAL retornará apenas a soma de quem foi filtrado.

Aula 1 – Base de Dados – Filtro e Subtotal

129 Portanto, sempre que for trabalhar com Filtro, tome esse cuidado de utilizar a fórmula SUBTOTAL quando for fazer algum tipo de análise.

Aula 2 - Análise – Função Somase

Mês Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15

Total

CAR

Volume (mmbbls) CPY CSP

130 CMB

CIB

Nosso primeiro objetivo é calcular o volume (em barris) correspondente ao mês de janeiro de 2015, em todas as plataformas, na célula C4. Para isso, utilizaremos a fórmula SOMASE.

Aula 2 - Base de Dados – Função Somase

Date

FPSO

Lifter

05/01/2015 07/01/2015 08/01/2015 08/01/2015 10/01/2015 11/01/2015 14/01/2015 16/01/2015 18/01/2015 21/01/2015 21/01/2015 23/01/2015 23/01/2015 25/01/2015 25/01/2015

CSP CPY CAR CMB CSP CAR CAR CPY CSP CPY CMB CPY CSP CAR CIB

Empresa 4 Empresa 3 Empresa 3 Empresa 3 Empresa 3 Empresa 3 Empresa 3 Empresa 1 Empresa 3 Empresa 3 Empresa 3 Empresa 3 Empresa 1 Empresa 1 Empresa 3

131 Cargo size (bbls) 638,894 495,728 735,907 726,195 606,957 603,465 973,709 772,141 694,948 942,146 491,590 571,309 655,203 919,636 556,626

Auxiliar (Mês) 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Antes de mais nada, criamos duas colunas auxiliares com os índices dos meses a serem usados no intervalo e critério da fórmula, nas abas “Base de Dados” (coluna E) e “Análise” (coluna A), respectivamente.

Aula 2 - Análise – Função Somase

Mês 1 2 3 4 5 6 7 8 9 10 11 12

Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15

Total

CAR

Volume (mmbbls) CPY CSP

132 CMB

CIB

Antes de mais nada, criamos duas colunas auxiliares com os índices dos meses a serem usados no intervalo e critério da fórmula, nas abas “Base de Dados” (coluna E) e “Análise” (coluna A), respectivamente.

Aula 2 - Análise – Função Somase

133 Feito isso, podemos inserir a fórmula SOMASE na célula C4, com os seguintes inputs:

• Intervalo: coluna E da aba Base de Dados • Critério: célula A4 da aba Análise • Intervalo_Soma: coluna D da aba Base de Dados

Aula 2 - Análise – Função Somase

134 Em seguida, “arrastamos” esta mesma fórmula para os meses seguintes, sem nenhum tipo de trancamento.

Aula 2 - Análise – Função Somases

135 Nosso objetivo agora é calcular novamente o volume (em barris) correspondente ao mês de janeiro de 2015, mas agora para cada plataforma separadamente. Para isso, utilizaremos a fórmula SOMASES.

Aula 2 - Análise – Função Somases

136 Os seguintes dados:

inputs

foram

• Intervalo_Soma: coluna D da aba Base de Dados • Intervalo_critérios1: coluna E da aba Base de Dados • Critério_1: célula A4 da aba Análise • Intervalo_critérios2: coluna B da aba Base de Dados • Critério_2: célula D3 da aba Análise

Aula 2 - Análise – Função Somases

137 Repare que alguns trancamentos de célula foram utilizados: • Intervalo_Soma, Intervalo_critérios1 e Intervalo_critérios2 foram trancados para que as colunas não se movam ao arrastarmos a fórmula para o lado • Critérios_1: trancamento de coluna

• Critérios_2: trancamento de linha

Aula 2 - Análise – Função Cont.se

138 Agora queremos calcular a quantidade de operações correspondente ao mês de janeiro de 2015, em todas as plataformas, na célula I4. Para isso, utilizaremos a fórmula CONT.SE.

Aula 2 - Análise – Função Cont.se

139 Os seguintes dados:

inputs

foram

• Intervalo: coluna E da aba Base de Dados • Critério: célula A4 da aba Análise

Aula 2 - Análise – Função Cont.se

140 Em seguida, “arrastamos” esta mesma fórmula para os meses seguintes, sem nenhum tipo de trancamento.

Aula 2 - Análise – Função Cont.ses

141 Nosso objetivo agora é calcular novamente a quantidade de operações correspondente ao mês de janeiro de 2015, mas agora para cada plataforma separadamente. Para isso, utilizaremos a fórmula CONT.SES.

Aula 2 - Análise – Função Cont.ses

142 Os seguintes dados:

inputs

foram

• Intervalo_critérios1: coluna E da aba Base de Dados • Critério_1: célula A4 da aba Análise • Intervalo_critérios2: coluna B da aba Base de Dados • Critério_2: célula J3 da aba Análise

Aula 2 - Análise – Função Cont.ses

143 Repare que alguns trancamentos de célula foram utilizados: • Intervalo_critérios1 e Intervalo_critérios2 foram trancados para que as colunas não se movam ao arrastarmos a fórmula para o lado • Critérios_1: trancamento de coluna • Critérios_2: trancamento de linha

Aula 2 - Texto – Base de Dados

144 Nessa aba temos as principais fórmulas de texto e a função de cada uma delas. É importante entender que em um texto o Excel numera os caracteres de acordo com a sua posição (da esquerda para a direita) e que espaços também são contados como um caractere. Assim, a expressão “Hashtag Treinamentos” tem 20 caracteres e o “H” é o 9º caractere, já que o espaço também é contado como um caractere. É importante lembrar que na fórmula DIREITA a contagem dos caracteres é feita da direita para a esquerda.

Aula 2 - Texto – Base de Dados

145 Usaremos as fórmulas de texto apresentadas para retirar informações da base de dados. Essa base possui a classificação do campeonato brasileiro com a posição de cada time, o nome do time e seu Estado correspondente.

Aula 2 - Exemplo – Fórmulas de Texto

146 Nessa aba iremos usar as fórmulas de texto para preencher a tabela seguindo a ordem. Começaremos pelo número de caracteres do clube, usando a fórmula NÚM.CARACT

Aula 2 - Exemplo – Fórmulas de Texto

147 Como argumento para a fórmula, basta selecionarmos o texto que queremos saber o número de caracteres. No caso vamos selecionar apenas a primeira célula (do Corinthians) e depois arrastar a fórmula para baixo.

Repare que sempre depois do nome do time aparece o texto “ – Estado” (5 caracteres). Como queremos apenas o número de caracteres do nome do clube, vamos portanto subtrair 5 caracteres depois da fórmula.

Aula 2 - Exemplo – Fórmulas de Texto

148 Para sabermos o estado de cada time, utilizaremos a fórmula DIREITA. Como os dois últimos caracteres do nome dos times em nossa base de dados (Ex: “Grêmio – RS”) é sempre a sigla de seu estado correspondente, basta colocarmos na célula D7 a fórmula:

=DIREITA('Texto Dados'!D17;2)

-

Base

de

Onde 'Texto - Base de Dados'!D17 é a célula de onde extrairemos a informação e 2 é a posição (contando da direita para a esquerda) do último caractere extraído. Agora basta arrastar a fórmula.

Aula 2 - Exemplo – Fórmulas de Texto

149 Agora, iremos preencher a coluna E com as siglas dos estados em letra minúscula. Para isso, utilizaremos a fórmula MINÚSCULA. Assim, para a célula E4 teremos: =MINÚSCULA(D4)

Onde D4 é a célula que possui o texto que queremos deixar em letra minúscula (no caso, a sigla dos estados). De modo semelhante, agora basta arrastar a fórmula para baixo e completaremos a coluna E.

Aula 2 - Exemplo – Fórmulas de Texto

150 Para preencher a coluna com o nome do Clube, precisaremos utilizar duas fórmulas: PROCURAR e EXT.TEXTO. Utilizaremos uma coluna auxiliar para facilitar. Para adicionar uma coluna, selecionaremos a coluna F e apertaremos Crtl + Shift + Mais.

Aula 2 - Exemplo – Fórmulas de Texto

151 Como podemos ver, os nomes dos clubes em nossa base de dados não têm tamanho padronizado, porém todos eles seguem a mesma estrutura “Nome – Estado”. Assim, o texto sempre começa com o nome do clube e o nome do clube sempre termina 2 caracteres antes do hífen, já que o caractere anterior ao hífen é sempre um espaço. Assim, para o caso do Atlético – MG, precisaríamos extrair o texto começando no caractere 1 até o caractere 8, que é a posição do texto espaço e hífen (“ –”) (posição 9) – 1.

Aula 2 - Exemplo – Fórmulas de Texto

152 Assim, utilizaremos a fórmula procurar para colocar na coluna auxiliar a posição do texto espaço hífen “ –”. A fórmula na célula F4 será: =PROCURAR(“ -”;'Texto - Base de Dados'!D17;1)-1 Onde " -“ é o texto procurado, 'Texto - Base de Dados'!D17 é a célula onde o texto será procurado e 1 (valor padrão da fórmula) é o caractere onde a procura será iniciada. Agora basta arrastar a fórmula para baixo e completaremos a coluna auxiliar F.

Aula 2 - Exemplo – Fórmulas de Texto

153 Agora, para preenchermos a coluna G (coluna com o nome dos clubes), utilizaremos a fórmula EXT.TEXTO. Colocando então na célula G4 a fórmula: =EXT.TEXTO('Texto Dados'!D17;1;F4)

-

Base

de

Onde 'Texto - Base de Dados'!D17 é a célula na base de dados de onde o texto será extraído, 1 é a posição inicial do texto a ser extraído e F4 é o tamanho do texto a ser extraído, conforme descrito na coluna auxiliar criada. Agora basta arrastar a fórmula para baixo e completaremos a coluna dos clubes G.

Aula 2 - Exemplo – Fórmulas de Texto

154 O desafio agora é preencher a coluna H (Classificação) com a estrutura “Estado – Nome”. Como já temos essas informações separadas em outras colunas, utilizaremos a fórmula: =CONCATENAR(D4;" - ";G4)

Onde D4 é a célula com o nome do Estado, " – “ ( espaço, hífen, espaço) é o segundo texto a ser concatenado e G4 é a célula com o nome do time. Agora, basta arrastar essa fórmula para baixo e teremos a coluna da Classificação completa.

Aula 2 - Exercício – Fórmulas de Texto

155 Na coluna B temos alguns e-mails e nosso objetivo é colocar na coluna E o servidor de cada um deles (yahoo, live, gmail, hotmail). Utilizaremos novamente as fórmulas de texto e temos duas colunas auxiliares que farão parte da nossa fórmula final. Um email possui uma estrutura bem definida, onde o servidor sempre se encontra entre o caracter “@” e o caracter “.”. Assim, faremos a extração do texto entre esses dois caracteres para obter o servidor de cada um dos e-mails.

Aula 2 - Exercício – Fórmulas de Texto

156 Primeiramente, vamos achar a posição do caractere “@”. Para isso, usaremos na célula C3 a fórmula:

=PROCURAR("@";B3;1) Onde “@” é o caractere procurado, B3 é a célula do email (onde estamos procurando o caractere) e 1 é a posição de início da procura (valor padrão). Faremos o mesmo processo para a célula D3, porém substituindo “@” pela expressão “.com”.

Aula 2 - Exercício – Fórmulas de Texto

157 Assim, o caractere inicial de extração será o caractere seguinte ao “@”, logo será a posição do caractere “@” + 1. O tamanho do texto a ser extraído será a diferença entre a posição da expressão “.com” e do caractere “@” – 1. Tomemos o primeiro caso como exemplo: Posição do “@”: 13 Posição Inicial do Servidor: 13 + 1 = 14 Posição do “.com”: 19 Tamanho do nome do servidor: (yahoo) 5 = 19 - 13 - 1

Aula 2 - Exercício – Fórmulas de Texto

158 Agora colocaremos na célula E3 a fórmula para a extração seguindo a lógica apresentada:

=EXT.TEXTO(B3;C3+1;D3-C3-1) Onde B3 é a célula de onde extrairemos o texto (célula que possui o e-mail), C3 é a célula da posição do “@” (C3 + 1 é o primeiro caractere a ser extraído) e D3 é a célula que contém a posição do .com (D3 – C3 – 1 é o tamanho do texto a ser extraído). Arrastando as fórmulas para as outras linhas conseguimos completar nossa tabela.

Aula 2 - Exercício – Fórmulas de Texto

159 Repare que deu um erro apenas no último e-mail. Isso porque já tem um caracter “.” antes do “@”. Queremos, na realidade, encontrar o segundo caracter “.”; Para isso, vamos mudar a fórmula PROCURAR da célula D7 para começar a procura só depois que encontrar o primeiro ponto, e assim retornará o valor correto.

Aula 2 - Exercício – Fórmulas de Texto

160 A fórmula ficará assim: =PROCURAR(".";B7;PROCURAR(".";B7)+ 1) Dessa vez usamos o último argumento (opcional) da fórmula, que é a partir de qual caracter queremos começar a procura. Como queremos encontrar apenas o segundo ponto, falamos para o Excel começar a procurar o ponto após o caracter do primeiro ponto.

Aula 3 - Fórmulas Financeiras

161 Nessa aba temos listadas as principais fórmulas financeiras. É importante entender que o argumento TIPO só precisa ser utilizado em problemas onde há pagamento (qualquer fluxo constante de entrada ou saída de caixa nos instantes intermediários). Vale lembrar também que não é necessário utilizar todos os argumentos das funções. Por último, é importante ressaltar que as informações relativas a nper (nº de períodos) e taxa devem estar sempre na mesma unidade.

Aula 3 - Exercícios – Fórmulas Financeiras

162 Nessa aba temos 5 exercícios para aprender a estrutura e como utilizar fórmulas financeiras.

Aula 3 - Exercícios– Fórmulas Financeiras

163 Neste exercício, queremos calcular o valor futuro. Para isso, preenchemos a tabela ao lado com os dados fornecidos no problema, lembrando que quando não há pagamento não existe tipo, e aplicamos a fórmula: =VF(C9,C10,C11,C12)

Aula 3 - Exercícios – Fórmulas Financeiras

164 Neste exercício, queremos calcular a taxa de rentabilidade. Para isso, preenchemos a tabela ao lado com os dados fornecidos no problema, lembrando que quando não há pagamento não existe tipo, e aplicamos a fórmula:

=TAXA(C25,C26,C27,C29)

Aula 3 - Exercícios – Fórmulas Financeiras

165 Neste exercício, queremos calcular o número de períodos. Para isso, preenchemos a tabela ao lado com os dados fornecidos no problema, lembrando que quando não há pagamento não existe tipo, e aplicamos a fórmula:

=NPER(C39,C41,C42,C44)

Aula 3 - Exercícios – Fórmulas Financeiras

166 Neste exercício, queremos calcular o valor presente. Para isso, preenchemos a tabela ao lado com os dados fornecidos no problema, lembrando que quando não há pagamento não existe tipo, e aplicamos a fórmula: =VP(C54,C55,C56,C59)

Aula 3 - Exercícios – Fórmulas Financeiras

167 Neste exercício, queremos calcular o valor de aluguel, que é a soma do pagamento com o valor do arrendamento. Para calcular o pagamento, preenchemos a tabela ao lado com os dados fornecidos no problema, lembrando que o tipo deve ser 1 pois o pagamento é realizado no início do período, e aplicamos a fórmula: =PGTO(C68,C69,C71,C73,C72) Em seguida, para calcular o valor de aluguel, aplicamos a fórmula: =C75+C70

Aula 3 - Análise de Investimento – Fórmulas Financeiras

168 Nesta aba desejamos analisar a viabilidade econômica de um projeto através da análise de indicadores como VPL (valor presente líquido) e TIR (taxa interna de retorno). Um projeto é considerável viável quando: VPL > 0 e TIR > TMA; onde TMA representa a taxa mínima de atratividade da empresa.

Aula 3 - Análise de Investimento – Fórmulas Financeiras

169 Primeiramente devemos preencher a tabela ao lado com os fluxos de caixa em cada período (coluna C, linha 8 até linha 14). Feito isso, podemos calcular o VPL e a TIR através das seguintes fórmulas:

=VPL(C5,C9:C14)+C8 =TIR(C8:C14) Vale destacar que para o cálculo do VPL devemos aplicar a fórmula e posteriormente deduzir o valor do investimento.

Aula 3 - Análise de Investimento – Fórmulas Financeiras

170 Podemos calcular o VPL também calculando o valor presente de cada um dos fluxos ao longo dos 6 anos, usando a fórmula VP. É como se estivéssemos pegando cada valor e descontando 15% por ano para ver quanto esse fluxo vale no ano 0 (pois para podermos comparar valores monetários, todos devem estar representados em uma mesma data). Na célula D8 vamos escrever a fórmula: =-VP($C$5;B8;0;C8) E depois arrastar para todo mundo.

Aula 3 - Análise de Investimento – Fórmulas Financeiras

171 Assim, temos o que chamamos de Fluxo de Caixa Descontado. Isso quer dizer que os valores que estão apresentados na coluna D representam todo os meus ganhos em termos de quanto eles valem HOJE. Ou seja, 2,8 milhões daqui a um ano valem hoje para a empresa o equivalente a 2,434 milhões e por aí vai. Então, para ver se estamos ganhando dinheiro com esse projeto, somamos quanto vamos ganhar e subtraímos o investimento que fizemos. =SOMA(D9:D14)+D8

Aula 3 - Análise de Investimento – Fórmulas Financeiras

172 Posteriormente, desejamos calcular o payback (tempo necessário para recuperação do investimento). Devemos preencher a tabela ao lado com os saldos acumulados em cada período (coluna D, linha 8 até linha 14).

Feito

isso, podemos calcular o payback através da seguinte fórmula: =CONT.SE(E8:E14,"<0")

Isso porque queremos saber quantos anos ficamos no negativo até conseguir recuperar o que investimos e passar a ganhar dinheiro efetivamente.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

173 Nessa aba vamos construir um gráfico de colunas com as informações das vendas.

Para isso, o primeiro passo é selecionar uma célula vazia. Depois, vamos na guia inserir e selecionamos o gráfico que queremos. No caso vamos selecionar a primeira opção: gráfico de colunas agrupadas.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

174 Agora, para alimentar as informações do gráfico, clicamos nele com o botão direito e depois cliamos em Selecionar Dados.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

175 Nessa caixa, vamos colocar as informações do gráfico. Na parte da esquerda, colocaremos os valores (eixo Y). Na parte da direita, colocaremos as legendas (eixo X). Para inserir os valores, basta clicar em Adicionar.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

176 Na parte de cima, vamos indicar o nome da série (opcional). Para selecionar as células que contém as informações, vamos usar a parte de ‘Valores da Série’. Agora vamos clicar em OK e adicionar as legendas.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

177 Para as legendas, vamos selecionar as células com os nomes dos vendedores.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

178 O próximo desafio é colocar uma linha que represente a meta de vendas: R$100.000. Para isso, vamos criar uma coluna auxiliar com os valores das metas para todos os vendedores.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

179 Feito isso, clicamos com o botão direito no gráfico e depois em Selecionar Dados, para adicionar uma nova Série de Dados ao nosso gráfico.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

180 Vamos clicar em Adicionar e depois selecionar as células com os valores das metas.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

181 Repare que as informações das metas ficaram em colunas. Como queremos deixar em linha, basta clicar em uma das colunas das metas com o botão direito e selecionar a opção de ‘Alterar Tipo de Gráfico de Série’.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

182 Na parte de ‘Combinação’, podemos escolher qual o tipo de gráfico para cada Série de Informações. Então trocaremos o gráfico de Meta para o tipo Linha. Caso sua versão do Excel não possua essa parte de ‘Combinação’, basta usar as opções que estão acima para alterar o tipo de gráfico.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

183 Agora queremos editar algumas partes do nosso gráfico. Os gráficos no Excel são basicamente um conjunto de vários objetos (colunas, legendas, título, eixos etc). Para formatar um objeto, basta selecionar ele e escolher o que fazer. No caso das linhas de grade, queremos apenas deletar. Portanto, vamos selecionar as linhas de grade e apertar ‘Delete’. Repare que quando clicamos em uma das linhas, todas ficam marcadas.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

184 O próximo passo do exercício é mudar a escala para saltar de 10.000 em 10.000.

Para formatar um objeto no gráfico, basta clicar nele com o botão direito e selecionar a última opção: a de formatar.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

185 Quando clicamos em Formatar, abre do lado direito uma caixa para mexermos na formatação. Dependendo da sua versão do Excel, essa caixa pode aparecer no meio da tela, mas as opções são basicamente as mesmas.

Repare que a Unidade Principal está em saltos de 20.000. Basta mudarmos para 10.000.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

186 A última coisa que o exercício pede é para mostrarmos os valores de venda em cima das colunas. Isso é o que chamamos de Rótulos de Dados. Basicamente queremos mostrar quanto vale cada coluna do gráfico. Para isso, basta clicar em uma das colunas com o botão direito e assim selecionamos todas elas. Depois, basta escolher a opção de Adicionar Rótulos de Dados.

Aula 3 – Gráfico Formatação – Gráfico de Colunas

187 Dessa forma, conseguimos visualizar muito mais intuitivamente quanto cada um vendeu.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

188 A ideia nessa aba é ver como usamos os gráficos para fazer previsões.

No caso desse exemplo vamos tentar prever qual será o valor do dólar usando os dados que temos.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

189 A primeira coisa pedida é para diminuir o espaçamento entre as colunas para 100%. Para editar as colunas, clicamos com o botão direito em uma delas e selecionamos a opção de Formatar.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

190 Para adicionar uma linha de tendência, também basta clicar com o botão direito e adicionar. Essa linha vai aproximar o comportamento da variação do dólar.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

191 O próximo desafio é montar a equação da reta para fazer a previsão do dólar em mar/16.

A equação é dada pela equação Y=a*X+b. Y – Valor do dólar X – Mês analisado a – Inclinação da reta b – Intercepção Para descobrir a e b, vamos usar as fórmulas INCLINAÇÃO e INTERCEPÇÃO.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

192 Primeiramente, como queremos analisar como o dólar varia a cada mês, vamos criar uma linha auxiliar para enumerar os meses.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

193 Para achar o ‘a’ usamos a seguinte fórmula: =INCLINAÇÃO(C5:Y5;C3:Y3) Valores de Y: valores do dólar Valores de X: meses enumerados de 1 a 23.

Para achar o ‘b’ usamos a seguinte fórmula: =INTERCEPÇÃO(C5:Y5;C3:Y3) Valores de Y: valores do dólar Valores de X: meses enumerados de 1 a 23.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

194 Para calcular o Y que queremos, ou seja, o valor do dólar no mês Mar/16, temos que saber qual o X. Para isso, podemos simplesmente arrastar as linhas dos meses que descobriremos qual número equivale a Mar/16. Assim, vemos que o mês de Mar/16 equivale ao número 27, que é o X que vamos usar para descobrir a previsão do dólar, que é o valor de Y.

Aula 3 – Previsão de Demanda – Previsões com Gráficos

195 Então, para descobrir o valor do dólar, basta calcular Y=a*X+b, sendo que a e b já calculamos pelas fórmulas e X é 27 que equivale ao mês de Mar/16. A previsão do dólar com relação à estaleca para Mar/16 é, portanto, $4,10.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

196 Nessa aba vamos aprender a exibir dois gráficos com escalas diferentes em um gráfico só. Vamos começar montando o gráfico de colunas para representar as unidades descartadas (valores) ao longo das semanas (legendas).

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

197 Primeiro, selecionamos uma célula em branco e escolhemos o tipo de gráfico na guia Inserir.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

198 Depois, clicamos com o botão direito no gráfico e selecionamos a opção Selecionar Dados para inserir as informações do gráfico.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

199 Os valores do gráfico, que aparecerão no eixo Y, serão os dados das unidades descartadas.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

200 As legendas, que aparecerão no eixo X, serão as informações das Semanas.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

201 Agora, o exercício pede para tirar as linhas de grade, formatar o eixo (mínimo 0 e máximo 50 com saltos de 10 em 10) e adicionar os Rótulos de Dados.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

202 Para remover as linhas de grade, clicamos em uma delas. Dessa forma, como aparece na figura, todas ficam selecionadas. Agora basta apertar ‘Delete’.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

203 Para editar o eixo, basta clicar nele com o botão direito e ir em ‘Formatar Eixo’. Na caixa de formatação, vamos trocar o máximo para 50 e a unidade principal (o salto) para 10.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

204 Para adicionar os Rótulos de Dados, clicamos em uma das colunas. Assim todas ficam selecionadas. Depois, clicamos com o botão direito em uma das colunas e adicionamos os Rótulos de Dados.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

205 Agora vamos adicionar as informações de Unidades Produzidas e depois colocar o tipo de gráfico como gráfico de linha pontilhada. Para adicionar novas informações, mesmo passo de sempre: botão direito em cima do gráfico e escolher a opção Selecionar Dados.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

206 Então selecionamos as células com as informações das Unidades Produzidas da coluna F.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

207 Repare que como os valores das unidades são muito mais altos que os valores das unidades descartadas, as colunas extrapolam a área do gráfico. Isso porque colocamos o eixo para ir até apenas 50 unidades. Para resolver isso, vamos colocar as informações de unidades produzidas em um outro Eixo (Eixo Secundário).

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

208 Para fazer isso, selecionamos as colunas das Unidades Produzidas e aí clicamos com o botão direito e vamos em ‘Formatar Série de Dados’. Na caixinha que abrir, selecionamos a opção ‘Eixo Secundário’. Assim, o Excel criará um segundo eixo para as informações das Unidades Produzidas dentro do mesmo gráfico.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

209 Agora, temos dois eixos, um para cada conjunto de informações (Unidades Descartadas e Unidades Produzidas). Só que agora os gráficos estão sobrepostos. Então vamos trocar o tipo de gráfico das Unidades Produzidas para Linha Pontilhada. Selecionamos as colunas e então clicamos com o botão direito e selecionamos ‘Alterar Tipo de Gráfico de Série’.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

210 Então trocamos o gráfico de Unidades Produzidas para o tipo Linha.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

211 Repare que se selecionarmos o gráfico de linha, aparece lá em cima uma guia ‘Formatar’. Para colocar a linha em forma pontilhada, podemos ir nessa guia e na opção Contorno da Forma -> Traços podemos escolher o tipo de traço que queremos.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

212 Agora falta formatar o segundo Eixo: Mínimo: 0 Máximo: 1600 Saltos: 200

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

213 Para calcular a previsão de unidades descartadas, vamos utilizar a mesma lógica da aba anterior: Calcular o a (Inclinação), o b (Intercepção) e definir qual o X que queremos: Valores de X: Semanas Valores de Y: Unidades Descartadas

a: =INCLINAÇÃO(E4:E15;C4:C15) b: =INTERCEPÇÃO(E4:E15;C4:C15) X: 15 (queremos saber a previsão da 15ª semana) Y: =a*X+b = H4*H6+H5 Portanto, a previsão de descarte para a semana 15 é de 42 unidades.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

214 Por último, queremos saber se existe correlação entre as unidades descartadas e as unidades produzidas. A correlação é um coeficiente que mede a relação entre dois conjuntos. Varia de -1 a 1. Quando a correlação é maior que 0,7, dizemos que os dois conjuntos possuem correlação forte e positiva. Quando a correlação é menor que -0,7, dizemos que possuem correlação forte e negativa. Para calcular a correlação existe, no Excel, a fórmula CORREL.

Aula 3 – Eixo Secundário – Gráficos com diferentes escalas

215 Visualmente parece que existe correlação, pois à medida que um cresce, o outro também cresce. Mas para confirmarmos matematicamente, usaremos a fórmula CORREL. =CORREL(E4:E15;F4:F15) Onde Matriz1 e Matriz2 são os conjuntos de números que queremos comparar. Portanto, a correlação entre o número de Unidades Descartadas e Unidades Produzidas é 0,965 > 0,7. Então, elas estão fortemente correlacionadas.

Aula 3 – Pizza – Gráfico de Pizza

216 Nessa aba vamos criar um gráfico muito utilizado, o gráfico de Pizza. O procedimento é o mesmo de sempre: selecionar uma célula vazia e escolher o gráfico que queremos construir. Depois, clicamos no gráfico com o botão direito e selecionamos a opção de Selecionar Dados.

Aula 3 – Pizza – Gráfico de Pizza

217 Por mais que o gráfico de Pizza não possua eixos X e Y, a lógica para selecionar os dados será a mesma: Do lado esquerdo da caixa, vamos selecionar os valores. Do lado direito, colocamos as legendas.

Aula 3 – Pizza – Gráfico de Pizza

218 Os valores do gráfico serão os valores dos produtos que estão na coluna E. As legendas serão os nomes dos produtos, que estão na coluna D.

Aula 3 – Pizza – Gráfico de Pizza

219 Para editar o título do gráfico, basta clicar nele, digitar o nome desejado e dar Enter.

Aula 3 – Pizza – Gráfico de Pizza

220 Caso o gráfico não tivesse título, poderíamos adicionar clicando no botão de ‘+’ que aparece do lado direito do gráfico. Podemos usar essa ferramenta para adicionar vários elementos ao nosso gráfico. Nas versões mais antigas, ao invés de aparecer o botão ‘+’ para adicionar elementos, aparece uma guia lá em cima chamada ‘Layout’, que possui as mesmas opções. Essa guia, além das guias Design e Formatar, aparecem quando clicamos no gráfico. Nas versões mais novas, a guia Layout foi substituída pelo botão

Aula 3 – Pizza – Gráfico de Pizza

221 Podemos usar essa ferramenta de adicionar elementos para colocar os Rótulos de Dados no nosso gráfico, e então são mostrados os valores de cada parcela. Mas como é pedido para mostrar o percentual dos produtos e não o valor, vamos formatar os Rótulos de Dados.

Aula 3 – Pizza – Gráfico de Pizza

222 Basta, então, marcar a opção de Porcentagem e desmarcar a opção de Valor. Podemos aproveitar, também, para incluir nos Rótulos de Dados o nome da Categoria. Assim, fica bem mais intuitivo de identificar os produtos do que pelas legendas.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

223 Nessa aba, vamos ver o gráfico do tipo Colunas Empilhadas. A ideia nesse tipo de gráfico é comparar vários valores com relação ao total.

Aqui vamos comparar os Projetos de BI, de Processos e Jurídicos da empresa ao longo dos meses.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

224 O gráfico de Colunas Empilhadas é a segunda opção na parte de gráficos de coluna. Para inserir, como sempre, selecionamos uma célula vazia e depois escolhemos o gráfico.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

225 Em seguida, selecionamos os dados do gráfico. No nosso exemplo, haverá 3 séries de valores: -Projetos de BI -Projetos de Processos -Projetos Jurídicos As legendas serão os meses de jan/15 a dez/15.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

226 Agora, queremos mostrar o valor total de cada coluna logo acima, como um Rótulo de Dados.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

227 Mas repare que se adicionarmos os Rótulos de Dados, vão aparecer os Rótulos de cada processo separadamente. Portanto, para aparecer apenas os Rótulos de Dados do Total, teremos que fazer uma “gambiarra”. Primeiro, vamos retirar os Rótulos de Dados que colocamos, já que não servem para a gente.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

228 Primeiro, vamos adicionar as informações de Total no nosso gráfico, já que queremos os valores de Total nos Rótulos de Dados.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

229 Agora, temos as informações de Total no nosso gráfico. Como queremos exibir seus valores, basta clicar em uma das colunas de Total que todas são selecionadas. Então, clicamos com o botão direito e adicionamos os Rótulos de Dados (apenas das informações do Total).

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

230 Agora que já temos as informações dos Totais em cima das colunas, podemos esconder as colunas de Total do gráfico, já que não queremos que apareça. Para isso, com as colunas de Total selecionadas, podemos ir lá na guia Página Inicial e marcar ‘Sem Preenchimento’.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

231 Assim, já temos as informações que precisamos. Para arrumar o gráfico, podemos diminuir a escala para ir no máximo até 5000, por exemplo, e também ajustar a posição dos Rótulos de Dados.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

232 Para formatar os Rótulos de Dados, clicamos em um deles. Assim, todos ficam selecionados. Então, podemos clicar com o botão direito e ir em Formatar. Na caixa de Formatação, aparecerá uma opção para ajustarmos a Posição do Rótulo.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

233 Para conseguirmos identificar qual coluna se refere a qual tipo de processo, vamos adicionar as Legendas no nosso gráfico.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

234 Mas repare que aparece a legenda com a informação ‘Total’, que não queremos. Para deletar essa informação, serão 3 passos bem simples: 1 – Clicar em uma das legendas (assim, todas serão selecionadas). 2 – Clicar na legenda ‘Total’. 3 – Apertar Delete para apagar apenas essa informação.

Aula 3 – Barra Multicor – Gráfico Colunas Empilhadas

235 Para colocar as Legendas abaixo do gráfico, como está pedido, clicamos com o botão direito e depois em Formatar. Na caixa ao lado, podemos mexer na posição da Legenda.

Aula 3 – Botões Básico

236 No Excel temos diversos tipos de botões com diferentes funções. Nessa aba temos exemplificadas as principais formas utilizadas: Hiperlink Botão de Rotação Caixa de Combinação

Aula 3 – Botões Básico – Hiperlink

237 Se quisermos navegar entre abas do Excel ou abrir um site por meio de um botão do Excel, utilizaremos o Hiperlink.

Para criar um botão de Hiperlink, basta inserirmos uma forma qualquer no menu inserir (um retângulo, por exemplo), clicarmos com o botão direito sobre a forma e selecionarmos a opção Hiperlink.

Aula 3 – Botões Básico – Hiperlink

238 Na caixa que será aberta pelo Excel, podemos escolher se vamos inserir um link para uma página da Internet ou para uma aba na planilha. Para inserir um hiperlink para uma página na internet, basta escolher a opção Página da Web ou arquivo e escrever o site no campo endereço: www.hashtagtreinamentos.com Para inserir um Hiperlink para uma aba, basta selecionar a opção Colocar neste documento e selecionar a aba destino desejada, por exemplo: Exercício Botão.

Aula 3 – Botões Básico – Botão de Rotação

239 Outra opção de botão é o Botão de Rotação. Ele cria duas setas, uma para cima e outra para baixo, que permite que aumentemos o valor de uma célula ou diminuamos, de acordo com a seta clicada. Para inserir um botão de rotação, é necessário habilitar a guia Desenvolvedor no Excel.

Aula 3 – Botões Básico – Guia Desenvolvedor

240 Para Habilitar a Guia Desenvolvedor, é necessário entrar em: Arquivo -> Opções -> Personalizar Faixa de Opções -> Selecionar a opção Guias Principais no menu superior esquerdo -> Marcar com um aa opção Desenvolvedor

Aula 3 – Botões Básico – Botão de Rotação

241 Acessando a Guia Desenvolvedor, clicamos na opção Inserir e selecionamos a opção de Botão de Rotação (4ª opção). Em seguida, clicamos em algum lugar da planilha para que o botão apareça e podemos redimensiona-lo como quisermos. Será necessário configurar o botão para que ele altere o valor de alguma célula. Clicamos com o botão direito no Botão de Rotação e selecionamos a opção Formatar Controle. Na caixa que será aberta podemos formatar diferentes características do botão, porém o que precisamos definir são:

Aula 3 – Botões Básico – Botão de Rotação

242 Valor atual: Valor Inicial da célula vinculada ao botão de rotação; Valor mínimo: Valor mínimo aceito pela célula vinculada ao botão de rotação; Valor máximo: Valor máximo aceito pela célula vinculada ao botão de rotação; Alteração Incremental: Incremento que será atribuído à célula sempre que clicado o botão. Se esse valor for 2 e clicarmos na seta para cima, será somado 2 ao valor da célula. Se clicarmos para baixo, será subtraído 2 do valor da célula. Vínculo da célula: Referência da célula que terá seu valor alterado pelo botão. Ex: $C$8

Aula 3 – Botões Básico – Caixa de Combinação

243 De forma semelhante ao botão de rotação, para inserirmos a Caixa de Combinação, precisamos ter a guia desenvolvedor habilitada. Para inserir, basta irmos na guia Desenvolvedor, clicarmos em Inserir, selecionarmos a segunda opção (Caixa de Combinação) e escolhermos o local da planilha em que vamos inserir. A caixa de combinação permite a criação de um menu (muito utilizado em Dashboards) onde podemos escolher qual o valor que que gostaríamos de apresentar.

Aula 3 – Botões Básico – Caixa de Combinação

244 De modo análogo, precisamos vincular a caixa de combinação à uma célula e passarmos as informações necessárias de configuração. Para isso, clicamos com o botão direito do mouse sobre a caixa de combinação e clicamos em Formatar Controle. Na caixa que será aberta, precisamos passar 2 informações principais: Intervalo de Entrada: Lista com todos os valores que podemos escolher na Caixa de Combinação Vínculo da Célula: Célula em que aparecerá o valor escolhido na Caixa de Combinação.

Aula 3 – Botões Básico – Caixa de Combinação

245 No exemplo, ao selecionarmos o nome Tadeu, aparece na célula $C$15 (célula escolhida em Vínculo da Célula) o valor 4. Isso ocorre porque o nome Tadeu é o 4º valor da nossa lista (1º - Carolina, 2º Poline, 3º - Iris e 4º - Tadeu). Assim, a caixa de combinação sempre nos retorna a posição do valor escolhido na célula vinculada.

Aula 3 – Botões Básico – Caixa de Combinação

246 Para termos em uma célula (por exemplo na B21) o valor escolhido na caixa de combinação, deveremos usar a fórmula Índice: =ÍNDICE(B15:B20;C15) Onde B15:B20 é a lista com os nomes das pessoas (mesma lista do Intervalo de Entrada da Caixa de Combinação) e C15 é a célula vinculada à Caixa de Combinação (que retornará o valor da posição do nome escolhido. No exemplo, é a célula que retornará o valor 4)

Aula 4 – Exercício Dashboard - Dashboards

247 Nessa aba, trabalharemos com os Dashboards, que permitem uma forma de apresentação dinâmica dos resultados de análises, combinando gráficos, fórmulas e botões. Temos os valores dos volumes de vendas mensais e os diferentes anos. Nosso objetivo é ter um gráfico que atualize automaticamente ao mudarmos em uma caixa de combinação o ano a ser apresentado no gráfico.

Aula 4 – Exercício Dashboard - Dashboards

248 Para fazer isso, primeiramente é necessário criar um gráfico de colunas, colocando como série de dados os valores do intervalo C8:N8. Tendo o gráfico estruturado, precisaremos criar a caixa de combinação para selecionar o ano a ser exibido no gráfico. Para isso, seguindo o processo apresentado, vamos inserir a caixa de combinação, clicar com o botão direito e escolher a opção formatar controle, colocando: Intervalo de Entrada: B22:B25 Vínculo da célula: B26

Aula 4 – Exercício Dashboard - Dashboards

249 É importante lembrar que o gráfico terá sempre como fonte de dados os valores do intervalo C8:N8, portanto ele não precisará ser editado ao mudarmos o ano na caixa de combinação. Assim, o caráter dinâmico da atualização do gráfico está na mudança dos valores do intervalo C8:N8 de acordo com o ano escolhido, o que será feito por meio de fórmulas. Assim, para as fórmulas que utilizaremos no intervalo C8:N8, precisaremos ter em alguma célula o ano escolhido na caixa de combinação (já que não conseguimos pegar essa informação diretamente da caixa).

Aula 4 – Exercício Dashboard - Dashboards

250 Portanto, para ter essa informação do ano escolhido na caixa de combinação na célula B7, utilizaremos a fórmula:

=ÍNDICE(B22:B25;B26) Onde B22:B25 é o intervalo dos anos e B26 é a célula vinculada à caixa de combinação (célula na qual a caixa de combinação retornará o valor da posição do ano na lista de anos)

Aula 4 – Exercício Dashboard - Dashboards

251 Agora, basta colocarmos a fórmula no intervalo C8:N8 que atualize sempre que o valor da célula B7 for alterado, ou seja, sempre que mudarmos o valor da caixa de combinação. Para isso, utilizaremos combinações da fórmula SE, seguindo a lógica:

“Se o ano escolhido for 2012, então o valor de C8 será igual a C11. Caso não seja, analisaremos: Se o ano escolhido for 2013, então o valor de C8 será igual a C14. Caso não seja, analisaremos...”, seguindo essa lógica até 2015.

Aula 4 – Exercício Dashboard - Dashboards

252 Assim, só precisaremos colocar a fórmula na célula C8, travar as células corretas e “puxar para o lado” até a célula N8. Assim, na célula C8 teremos a fórmula: =SE($B$10=$B$7;C11;SE($B$7=$B$13;C 14;SE($B$7=$B$16;C17;SE($B$7=$B$19 ;C20)))) Ao arrastarmos essa célula até N8, finalizamos nosso Dashboard, e sempre que o ano for alterado na caixa de combinação, nosso gráfico será atualizado automaticamente, criando um relatório visual e dinâmico das vendas dos anos.

Aula 4 – Exercício Dashboard - Dashboards

253 Para 4 anos, é relativamente tranquilo fazer o Dashboard usando a fórmula SE. Agora imagine se fossem 50 anos e tivéssemos que fazer inúmeras fórmulas SE uma dentro da outra. Para fazer uma fórmula que sirva independente do número de anos, podemos usar o PROCV, para procurar o ano e retornar o número de vendas.

Visto que o valor procurado (ano) tem que estar na mesma linha da resposta que queremos (número de vendas), vamos criar uma coluna auxiliar na coluna A para colocar o ano correspondente.

Aula 4 – Exercício Dashboard - Dashboards

254 Para não ter o trabalho de reescrever todos os anos, podemos simplesmente chegar na célula A11 e digitar a fórmula: =B10 E arrastamos para baixo. Assim, sempre teremos o ano correspondente na mesma linha do número de vendas. Repare que também apareceram células escritas ‘Vendas’ e 0, mas não tem problema, pois o PROCV que procura por um ano nunca vai encontrar esses valores. Fora isso, como é só uma coluna auxiliar, a ideia é ocultar ela depois, então não vai ‘poluir’ a planilha com informação a mais.

Aula 4 – Exercício Dashboard - Dashboards

255 A fórmula para janeiro vai ficar assim: =PROCV($B$7;$A$11:$N$20;3;0) • Valor procurado – Ano (B7) • Matriz tabela – A11 até N20 • Número índice de coluna – 3 (queremos a 3ª informação da tabela para janeiro) • Procurar intervalo – 0 (valor exato)

Aula 4 – Exercício Dashboard - Dashboards

256 Mas repare que se arrastarmos a fórmula para a direita, todos os valores continuarão iguais.

Isso porque colocamos o número índice de coluna igual a 3 e, ao arrastar a fórmula, ele continua igual. Mas quando arrastarmos para Fevereiro, queremos o número 4, para Março o número 5 e assim vai. Portanto, vamos criar uma linha auxiliar para colocar o número índice de coluna de cada mês, para arrastar a fórmula e funcionar para todo mundo.

Aula 4 – Exercício Dashboard - Dashboards

257 Então criamos a linha auxiliar na linha 6, por exemplo. Agora simplesmente mudamos o número índice de coluna para a célula acima do mês. Assim, quando arrastarmos a fórmula, o PROCV pegará o número índice de coluna correspondente a cada mês. A fórmula na célula C8 fica assim: =PROCV($B$7;$A$11:$N$20;C6;0 ) E então basta arrastar para a direita e está pronto o Dashboard.

Aula 4 – Resumo Gerencial - Dashboards

258 Nessa aba, a ideia vai ser criar um Dashboard com o gráfico do tipo Pizza 3D usando a base de dados da aba anterior (Exercício Dashboard 2).

Aula 4 – Exercício Dashboard 2 - Dashboards

259 Nossa base de dados tem essa cara. As informações são os gastos da empresa dos meses jan/15 a out/15 e os respectivos pesos percentuais, que vamos usar para construir nosso gráfico.

Aula 4 – Resumo Gerencial - Dashboards

260 A ideia vai ser, então, criar um botão que permita escolher o mês desejado para análise (Jan/15 a Out/15). Depois de escolher o mês, os dados da tabela devem ser atualizados para o mês escolhido e o gráfico também deve ser alterado. Vamos fazer na seguinte ordem: - Inserir o gráfico Pizza 3D - Criar o botão com os meses (Caixa de Combinação - Usar fórmula para pegar os dados da base de acordo com o mês escolhido.

Aula 4 – Resumo Gerencial - Dashboards

261 O gráfico que queremos é o de Pizza 3D, que aparece na figura.

Aula 4 – Resumo Gerencial - Dashboards

262 Os valores do gráfico serão as porcentagens e as legendas serão os gastos (Folha Salarial, Impostos e assim vai).

Aula 4 – Resumo Gerencial - Dashboards

263 À medida que formos colocando as fórmulas na nossa tabela para puxar as informações dos meses, o gráfico vai agora mudar de acordo com os valores que estiverem na coluna D da nossa tabela. Para construir o botão, o primeiro passo é escrever as informações que vão aparecer na lista (os meses de Jan/15 a Out/15). Essas informações precisam estar na vertical e sem pular linha.

Aula 4 – Resumo Gerencial - Dashboards

264 Então, inserimos a Caixa de Combinação. Clicando com o botão direito na caixa, vamos selecionar a opção de Formatar Controle. O intervalo de entrada serão as células com as informações dos meses. O vínculo da célula vai ser a célula que vai aparecer o número de índice (o número que diz quem escolhemos da lista).

Aula 4 – Resumo Gerencial - Dashboards

265 Agora, quando escolhermos algum mês da lista, o número da célula C12 será alterado.

Para aparecer o mês escolhido, usaremos a fórmula ÍNDICE, que vai ficar assim: =ÍNDICE(B12:B21;C12) Onde Matriz é a nossa lista e núm de linha vai ser a célula vínculo da nossa Caixa de Combinação, que informa em qual linha da lista está o mês escolhido (ex: Mar/15 é o terceiro da lista, número 3).

Aula 4 – Resumo Gerencial - Dashboards

266 Repare que agora na célula onde devia aparecer o mês escolhido, aparece um número. Isso aconteceu simplesmente porque a formatação da célula não está como data. Para deixar na formatação certa, podemos usar o pincel de formatação e copiar a formatação de uma das células dos meses.

Aula 4 – Resumo Gerencial - Dashboards

267 Agora, toda vez que selecionarmos um mês na nossa lista, ele aparecerá na célula C13. Podemos até pintar de amarelo para ficar mais fácil de identificar.

Aula 4 – Resumo Gerencial - Dashboards

268 Olhando para a nossa tabela, vamos usar o PROCV ou o PROCH?

Vamos usar o PROCH pois os meses estão dispostos principalmente na horizontal. Mas como temos dois conjuntos separados, teríamos que fazer dois PROCH’s diferentes (para encontrar janeiro a maio e outro para encontrar junho a outubro. Para poder ter duas tentativas para encontrar o mês, podemos usar a fórmula SEERRO.

Aula 4 – Resumo Gerencial - Dashboards

269 Mas repare que queremos procurar, através do mês escolhido, as informações de Folha Salarial, Impostos e assim vai. Para isso, os meses têm que estar na mesma coluna das informações que queremos. Isso porque o PROCH vai procurar o mês e, quando encontrar, irá retornar um dado que estiver na mesma coluna do mês encontrado. Para mover os meses, podemos selecionar as células e, clicando em uma das bordas, mover todas de uma vez. Vamos fazer isso tanto na linha 5 quanto na linha 14.

Aula 4 – Resumo Gerencial - Dashboards

270 Assim como no último Dashboard, vamos querer fazer apenas uma fórmula e arrastar para todo mundo. Já pensando no primeiro PROCH, para os meses de Janeiro a Maio, quando formos pegar a Folha Salarial vamos querer a 3ª informação; para os Impostos, a 4ª informação e assim vai.

Então, vamos criar uma coluna auxiliar na Coluna A com essas informações.

Aula 4 – Resumo Gerencial - Dashboards

271 Para os meses de Janeiro a Maio, nosso PROCH na célula C5 ficará assim: =PROCH($C$13;' Exercício Dashboard 2'!$B$5:$T$12;' Exercício Dashboard 2'!A7;0) Para os meses de Junho a Outubro, o PROCH será assim: PROCH($C$13;' Exercício Dashboard 2'!$B$14:$T$21;' Exercício Dashboard 2'!A7;0) Repare que as fórmulas são praticamente iguais, só muda a Matriz Tabela. Para funcionar para todos os meses, basta colocar os dois PROCH’s dentro da fórmula SEERRO.

Aula 4 – Resumo Gerencial - Dashboards

272 Então a fórmula toda ficou assim: =SEERRO(PROCH($C$13;' Exercício Dashboard 2'!$B$5:$T$12;' Exercício Dashboard 2'!A7;0);PROCH($C$13;' Exercício Dashboard 2'!$B$14:$T$21;' Exercício Dashboard 2'!A7;0))

Como trancamos o valor procurado e a matriz tabela, podemos arrastar a fórmula para baixo.

Aula 4 – Resumo Gerencial - Dashboards

273 Por último, preenchemos as porcentagens. A porcentagem de cada item é simplesmente o valor dele dividido pelo total de todos os itens. Então, basta fazer uma fórmula que pegue o valor do item e divida pela soma de todos. A fórmula da célula D5 vai ficar assim: =C5/SOMA($C$5:$C$10)

Repare que trancamos apenas a seleção da soma e deixamos livre a célula que representa o valor do item, para poder arrastar para baixo e funcionar para todo mundo.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

274 Podemos fazer análises, encontrar resultados específicos e criar resumos gerenciais usando a Tabela Dinâmica. Nessa aba, por exemplo, nossa intenção é responder as quatro perguntas da direita (que também poderiam ser respondidas usando as fórmulas que já vimos) usando a Tabela Dinâmica.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

275 O primeiro passo é selecionar todas as células que compõe a base que você deseja trabalhar.

Podemos fazer essa seleção usando os atalhos de seleção (Crtl + Shift + Seta) ou selecionando as colunas inteiras, clicando diretamente em uma delas e puxando (ex: clica na coluna A e puxa até a coluna I).

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

276 Depois da seleção estar pronta, devemos clicar na opção INSERIR (lá em cima) e, em seguida, procurar por TABELA DINÂMICA. Após clicar em TABELA DINÂMICA, o Excel irá te dar a opção de colocar a dinâmica na própria aba ou em uma outra aba no mesmo arquivo. Caso queira manter na própria aba selecione a opção “Planilha Existente”. Caso queira colocar a dinâmica em outra aba do mesmo arquivo, selecione “Nova Planilha”.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

277 Após selecionar o local para sua dinâmica, aparecerá um retângulo similar ao da imagem à esquerda (com a seta azul apontada). Clicando nesse retângulo você terá aceso aos “Campos da Tabela Dinâmica” (seta laranja apontada). Que são divididos em quatro quadrantes: Filtros, Colunas, Linhas e Valores. Esses quadrantes serão usados para encontrarmos os resultados que queremos.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

278 O manuseio dos quadrantes acontece de forma intuitiva. Basta clicar em um dos títulos (que estão dentro do retângulo vermelho) e arrastá-los para um dos quadrantes. Ex: se colocarmos o SKU no quadrante LINHAS e VALOR DE VENDA no quadrante VALORES, temos uma lista com todos os skus e qual foi o valor de venda de cada.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

279 Se ao invés de SKU tivesse colocado MARCA dentro do quadrante LINHAS, teria obtido um quadro gerencial com a informação de quanto cada marca vendeu no total.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

280 Uma outra possibilidade seria colocarmos a opção TIPO dentro do quadrante FILTROS (mantendo MARCAS em LINHAS e VALOR DE VENDA em VALORES). Dessa maneira poderíamos descobrir, por exemplo, quanto cada marca vendeu levando em consideração apenas as vendas de Boné, Casaco e Chinelo.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

281 Depois de descobrir quanto cada marca vendeu levando em consideração apenas as vendas de Boné, Casaco e Chinelo.

Poderíamos ir além usando a dinâmica e descobrir qual foi a média de vendas por marca ou então quantos itens cada marca vendeu. Para isso, devemos clicar no título que estiver presente no quadrante VALORES e, em seguida, em CONFIGURAÇÕES DO CAMPO DE VALOR.

Aula 4 – Exercício Dinâmica – Tabela Dinâmica

282 Beleza, depois de clicar em CONFIGURAÇÕES DO CAMPO DE VALOR repare que aparecerá essa caixa de opções onde você poderá optar por ver o resultado como Soma, Contagem, Média... dentre outras opções. De forma geral, basta brincar um pouco com os títulos e os quadrantes que você vai entendendo a lógica. É simples, mas requer prática. Se tiver alguma dúvida, fique super à vontade para nos procurar!

Aula 4 – Solver Exemplo – Ferramenta Solver

283 Nessa aba iremos trabalhar com uma ferramenta muito interessante, que é o SOLVER.

Essa ferramenta basicamente permite com que a gente faça otimizações com restrições. Isto é, imagine, por exemplo, que temos um orçamento máximo que podemos gastar com Scanners, Laptops e Impressoras para revender depois. Além disso, temos um espaço de armazenagem limitado. O Solver conseguirá nos dizer qual quantidade de cada item devemos comprar de forma a obter o máximo lucro possível, dadas as restrições de

Aula 4 – Solver Exemplo – Ferramenta Solver

284 O Solver nos dá a possibilidade de fazer 3 coisas: Maximizar, minimizar ou atingir um valor específico. Normalmente estamos interessados em maximizar o lucro ou minimizar os custos.

Aula 4 – Solver Exemplo – Ferramenta Solver

285

Para usar o Solver, precisamos habilitálo. Para fazer isso, seguiremos o passo a passo: Arquivo -> Opções -> Suplementos -> Selecionar Suplementos do Excel -> Ir -> Dar Check no Solver

Aula 4 – Solver Exemplo – Ferramenta Solver

286 E então o Solver aparecerá para a gente na guia Dados, lá no cantinho direito.

Aula 4 – Solver Exemplo – Ferramenta Solver

287 Antes de partir para o Solver, precisamos estabelecer as nossas restrições e o nosso objetivo. As células pintadas de amarelo são o que chamamos de células variáveis, ou seja, o que o Solver vai alterar para atingir o objetivo que definirmos. A ideia é que o Solver fale para a gente quanto temos que comprar de cada item para conseguir o máximo de lucro possível.

Aula 4 – Solver Exemplo – Ferramenta Solver

288 Nossa primeira restrição é: só temos R$93.000 para gastar. Portanto, vamos colocar uma fórmula para calcular quanto estamos gastando, na célula G7. Para isso, vamos multiplicar a quantidade de cada item pelo preço. =C10*C7+D10*D7+E10*A7

Aula 4 – Solver Exemplo – Ferramenta Solver

289 A segunda restrição é: só temos 101 metros quadrados de espaço para armazenar os produtos. Na célula G8, vamos colocar a fórmula para calcular quanto de espaço estamos gastando. Para isso, vamos multiplicar a quantidade utilizada de cada item pelo espaço que ele ocupa no estoque.

=C10*C8+D10*D8+E10*E8

Aula 4 – Solver Exemplo – Ferramenta Solver

290 Por último, vamos definir o nosso objetivo: na célula I10, vamos colocar a fórmula para calcular o Lucro baseado nas quantidades dos itens. O lucro total vai ser a quantidade de cada item vezes o lucro unitário do item. =C10*C5+D10*D5+E10*E5

Aula 4 – Solver Exemplo – Ferramenta Solver

291 Agora nossa planilha está pronta. Podemos até colocar uns valores para cada item para ver os possíveis cenários (ex: 10 unidades compradas de cada item). Repare que, com essas quantidades, estamos gastando R$15.000 e 20 metros quadrados de espaço. Como não ultrapassamos as restrições, poderíamos ficar chutando valores para encontrar lucros maiores. A ideia é que o Solver faça isso de maneira rápida e que encontre o maior lucro possível.

Aula 4 – Solver Exemplo – Ferramenta Solver

292 Indo lá na guia Dados e clicando no Solver, aparecerá a seguinte janela para a gente.

Aula 4 – Solver Exemplo – Ferramenta Solver

293 A primeira informação que temos que passar é qual a nossa célula objetivo e o que queremos fazer com ela: Maximizar, Minimizar ou alcançar um valor específico. Nossa célula objetivo vai ser a célula do Lucro I10, que queremos Maximizar.

Aula 4 – Solver Exemplo – Ferramenta Solver

294 O próximo passo vai ser informar quais células o Solver tem que alterar para maximizar nossa função. No caso as células que terão que ser alteradas são as das quantidades dos itens: C10 até E10.

Aula 4 – Solver Exemplo – Ferramenta Solver

295 Agora, temos que pensar em quais restrições vão ter que entrar no problema.

Não podemos gastar mais do que R$93.000. Portanto, a célula G7 tem que ser menor ou igual à célula H7. O mesmo vale para as células G8 e H8, que representam o espaço utilizado. Mas além disso, não podemos comprar quantidades negativas e nem comprar, por exemplo, meia impressora. Então, as quantidades têm que ser positivas e Inteiras.

Aula 4 – Solver Exemplo – Ferramenta Solver

296 Para adicionar uma restrição, basta clicar em Adicionar no canto direito.

Aula 4 – Solver Exemplo – Ferramenta Solver

297 Então, vamos adicionando cada restrição de uma vez. Para passar para a próxima, basta clicar em Adicionar. Quando terminar, só clicar em OK e assim volta a janela do Solver.

Para colocar a restrição de número inteiro, usamos a opção ‘Int’. Em versões mais antigas, essa opção é a ‘Num’. Se a sua tiver a opção ‘Num’, basta selecionar ela e funcionará igual.

Aula 4 – Solver Exemplo – Ferramenta Solver

298 Agora que preparamos o Solver, clicamos em Resolver.

Aula 4 – Solver Exemplo – Ferramenta Solver

299 Quando o Solver terminar de resolver, aparecerá esta janela dizendo que o Solver encontrou uma solução. Então ele diz que já havia informações nas células (aquelas que escrevemos 10 em cada uma) e pergunta se pode substituir essas informações pela solução que o Solver encontrou. Então, só clicar em OK.

Aula 4 – Solver Exemplo – Ferramenta Solver

300 Agora aparecem para a gente as quantidades que otimizam o Lucro e qual é o Lucro máximo.

Repare que como não colocamos nenhuma restrição dizendo que temos que ter um mínimo de produtos em estoque, o Solver informou que o lucro é maior quando não compramos nenhuma impressora. Se quiséssemos garantir que teríamos pelo menos uma unidade de cada produto em estoque, bastava incluir essa restrição no Solver.

Aula 4 – Solver Exercício – Ferramenta Solver

301 Nessa aba, queremos saber quais as quantidades que devemos comprar de cada ingrediente para conseguir compor 150Kg de ração com o menor custo possível, respeitando os mínimos e máximos de cada ingrediente.

Podemos, novamente, pintar as células variáveis de amarelo para ajudar a identificar.

Aula 4 – Solver Exercício – Ferramenta Solver

302 Na coluna G, vamos preencher com os custos totais de cada item. O custo total de cada um será a quantidade multiplicada pelo seu preço. Então, arrastamos a fórmula para baixo.

Aula 4 – Solver Exercício – Ferramenta Solver

303 A quantidade total de ração será a soma das quantidades de todos os ingredientes. =SOMA(C5:C8)

Aula 4 – Solver Exercício – Ferramenta Solver

304 Como já calculamos o gasto total com cada ingrediente na coluna G, nosso Custo Total de Produção será a soma dos custos dos ingredientes. =SOMA(G5:G8)

Aula 4 – Solver Exercício – Ferramenta Solver

305 Podemos testar valores para checar se está tudo certinho.

Aula 4 – Solver Exercício – Ferramenta Solver

306 Nosso objetivo vai ser minimizar o custo, que está na célula D12. Nossas restrições serão: as quantidades devem ser menores que os valores máximos e maiores que os valores mínimos. Repare que não é necessário criar uma restrição para cada ingrediente de uma vez, podemos selecionar todos e dizer que eles devem ser menores que as quantidades máximas e assim vai. Assim, o Solver irá entender que estamos querendo comparar linha a linha. Por último, as quantidades não podem ser negativas e a quantidade produzida deve ser exatamente 150 (C10=G10). Então, podemos mandar o Solver resolver.

Aula 4 – Solver Exercício – Ferramenta Solver

307 Portanto, essas são as quantidades que devemos comprar para produzir exatamente 150Kg de ração com o menor custo possível, de R$449,25.

Aula 4 – Introdução Macro – Gravação de Macro

308 Nessa aba, vamos mexer com a parte de gravação de Macros

Para começar a gravar uma Macro, basta ir na guia Desenvolvedor e clicar em Gravar Macro.

Aula 4 – Introdução Macro – Gravação de Macro

309 Então, abrirá essa caixa. Primeiro damos um nome à macro. Só não pode ter caracter especial como acento, cedilha etc. Também não pode colocar espaço. Depois, podemos escolher uma tecla de atalho para executar essa Macro. A recomendação é usar letra maiúscula para não conflitar com outros atalhos do Excel (ex: CTRL+d, CTRL+c, CTRL+p e assim vai). Por fim, podemos colocar uma descrição só para lembrarmos depois do que se trata a macro.

Aula 4 – Introdução Macro – Gravação de Macro

310 Repare que agora o botão de Gravar Macro agora virou um botão de Parar Gravação.

Então, qualquer ação que fizermos agora no Excel, ele irá gravar e repetir depois quando rodarmos a Macro.

Aula 4 – Introdução Macro – Gravação de Macro

311 Podemos, então, por exemplo, fazer as seguintes ações:

-Selecionar a célula C10, escrever Hashtag e apertar Enter. -Escrever a idade na célula C11 -Selecionar a célula B3 e pintar de amarelo. Feito isso, para parar a gravação, basta clicar no botão de Parar Gravação lá em cima na Guia Desenvolvedor ou lá embaixo na barra verde.

Aula 4 – Introdução Macro – Gravação de Macro

312 Agora vamos querer executar a Macro para ver o que ela está fazendo.

Então vamos deletar o que a gente tinha feito e então executar a Macro.

Aula 4 – Introdução Macro – Gravação de Macro

313 Para poder rodar a Macro da forma mais intuitiva possível, vamos criar um botão e associar essa Macro ao botão (que nem fizemos lá no Hiperlink), para qualquer pessoa poder clicar e executar facilmente a Macro.

Então, inserimos uma forma. Clicando nela com o botão direito, vai aparecer a opção de Atribuir Macro.

Aula 4 – Introdução Macro – Gravação de Macro

314 Quando abrir a janela, basta escolher a Macro que acabamos de criar e clicar em OK.

Aula 4 – Introdução Macro – Gravação de Macro

315 Agora, sempre que clicarmos nesse botão, ele vai executar a Macro que atribuímos e vai repetir tudo o que a gente tinha feito de maneira bem rápida. Então, sempre que estiver fazendo alguma tarefa muito repetitiva no Excel (como copiar e colar informações e assim vai), tente pensar se dá para automatizar isso com Macro, e muitas vezes dá.

Aula 4 – Introdução Macro – Gravação de Macro

316 Para vermos o que a Macro está efetivamente fazendo, podemos ir no ambiente do VBA e ver o código que aquela Macro está executando. Para isso, na guia Desenvolvedor vamos clicar em Macros.

Quando abrir a caixa, selecionamos a nossa Macro e clicamos em Editar.

Aula 4 – Introdução Macro – Gravação de Macro

317 Apesar de ser um ambiente novo e aparentemente um pouco confuso, mesmo sem saber nada de código de VBA, poderíamos alterar algumas coisas. Por exemplo, a célula que pintamos de amarelo foi a B3. Se quiséssemos que a Macro pintasse a célula D5, por exemplo, era só alterar onde está escrito B3 para D5. Se quiséssemos que a Macro escrevesse algum outro texto ao invés de Hashtag na célula C10, bastava trocar o texto Hashtag pelo texto que queremos. Então, sempre tente usar a ferramenta de gravação de Macro para ajudar nas suas tarefas do Excel.

DÚVIDAS?

Fala com a gente lá no grupo do WhatsApp