Apostila_ExcelAvancado2

Prévia do material em texto

Excel Avançado
Clério Frassao
Sumário
Excel Avançado	1
Clério Frassao	1
Sumário	2
Objetivo	4
Banco de Dados	4
Importação de Dados	4
Incompatibilidade de versões do Excel	4
Resolvendo incompatibilidades de versões do Excel	4
Funções de Banco de Dados	5
BDCONTAR	5
BDCONTARA	6
BDSOMA	6
BDMEDIA	6
BDMÁX	6
BDMÍN	6
Outras funções úteis para manipular dados:	7
SE	7
E	8
OU	8
SOMASE	9
CONT.SE	10
CONT.NUM	11
CONT.VALORES	11
CONTAR.VAZIO	12
PROCV	12
CONCATENAR	13
HOJE	14
AGORA	14
Classificar Dados (Ordenar Crescente ou Decrescente)	15
Subtotais	15
Filtros	17
Validação de dados	17
Proteção de dados	19
Protegendo a pasta de trabalho:	19
Protegendo planilha	19
Senha de abertura do arquivo	20
Auditoria	20
Tabela dinâmica	21
Gráfico Dinâmico	23
Formatação Condicional	24
Macros	25
Guia Desenvolvedor	25
Gravando Macros	26
Executar Macro por um botão	26
Atingir Meta	27
Objetivo
Conhecer em detalhes as funções da planilha eletrônica Excel, manipulado funções, tabelas e gráficos avançados, reduzindo o tempo e o esforço empreendidos nas tarefas.
Banco de Dados
Um banco de dados consiste em tudo que se quer acessar mais tarde, de forma organizada e de rápido acesso. É usado geralmente para dados cadastrais de clientes, produtos, informações financeiras e várias outras funções. Pode ser no formato de tabelas ou em módulos relacionados. 
Importação de Dados
Quando dados precisam ser trazidos de outras fontes, com necessidade ou não de conversão dos formatos. Por exemplo, uma folha de contatos do Hotmail para o Gmail ou ainda tabelas criadas em outros aplicativos concorrentes do Excel. 
Incompatibilidade de versões do Excel
As versões mais recentes do Excel abrem qualquer arquivo criado em versões mais antigas, como Excel2003. Porém versões antigas obviamente podem não estar preparadas para abrir documentos criados em versões mais recentes de Excel, como 2007 e 2010.
Resolvendo incompatibilidades de versões do Excel
Nas últimas versões é possível salvar no formato de arquivo mais antigo (.xls):
* Abra a opção 'salvar como..' e na opção 'tipo' (abaixo do nome do arquivo), escolha 'Pasta de trabalho do Excel 97-2003’
Caso tenha recebido um arquivo no formato posterior ao seu Excel você pode ainda baixar o pacote de conversão (Compatibility Pack for 2007 Office Word, Excel e PowerPoint) e instalar no seu computador.
Isto permitirá que você abra, edite e salve documentos do Excel 2007 ou 2010 em uma versão anterior do Excel.
* Link de downloads Microsoft
http://office.microsoft.com/pt-br/downloads/
Funções de Banco de Dados
São funções que facilitam a coleta das informações de um banco de dados:
BDCONTAR
	Retorna a quantidade de informações que obedecem a um critério de pesquisa (a seguir um exemplo prático).
Nesta tabela os dados já estão tabulados, então com a célula E2 selecionada, basta abrir o Inserir Função, que fica na guia Fórmulas.
 Selecione então a categoria Banco de Dados e abaixo a função BDCONTAR. Após isso confirme em OK.
Na etapa seguinte, Banco_dados será a tabela com as informações tabuladas (A4:D14).
Campo seria uma coluna específica, mas neste exemplo não será usado.
Critérios será o conjunto de células que servirão de referência para a pesquisa (A1:D2)
Clique OK e preencha com critérios, por exemplo, em Idade coloque <30
Podemos também nesta mesma tabela exemplificar as funções 
BDCONTARA 
 Conta as células não vazias no campo (coluna) de registros do banco de dados que atendam às condições especificadas.
BDSOMA
 Soma os números no campo (coluna) de registros do banco de dados que atendam às condições especificadas.
BDMEDIA 
Calcula a média dos valores em uma coluna de uma lista ou um banco de dados que correspondam às condições especificadas.
BDMÁX
 Retorna o maior número no campo (coluna) de registros do banco de dados que atendam às condições especificadas.
BDMÍN
Retorna o menor número no campo (coluna) de registros do banco de dados que atendam às condições especificadas.
Outras funções úteis para manipular dados:
SE
Retorna um valor se uma condição que você especificou avaliar como VERDADEIRO e um outro valor se for avaliado como FALSO.
Use SE para efetuar testes condicionais com valores e fórmulas.
Sintaxe
SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)
Teste_lógico é qualquer valor ou expressão que possa ser avaliado como VERDADEIRO ou FALSO. Por exemplo, A10=100 é uma expressão lógica; se o valor da célula A10 for igual a 100, a expressão será considerada VERDADEIRO. Caso contrário, a expressão será considerada FALSO. Esse argumento pode usar qualquer operador de cálculo de comparação.
Valor_se_verdadeiro é o valor retornado se teste_lógico for VERDADEIRO. Por exemplo, se esse argumento for a seqüência de caracteres de texto "Dentro do orçamento" e o argumento teste_lógico for considerado VERDADEIRO, a função SE exibirá o texto "Dentro do orçamento". Se teste_lógico for VERDADEIRO e valor_se_verdadeiro for vazio, o argumento retornará 0 (zero). Para exibir a palavra VERDADEIRO, use o valor lógico VERDADEIRO para esse argumento. 
Valor_se_falso é o valor retornado se teste_lógico for FALSO. Por exemplo, se esse argumento for a seqüência de caracteres de texto "Acima do orçamento" e o argumento teste_lógico for considerado FALSO, a função SE exibirá o texto "Acima do orçamento". Se teste_lógico for FALSO e valor_se_falso for omitido (ou seja, se não houver vírgula após valor_se_verdadeiro), o valor lógico FALSO será retornado. Se teste_lógico for FALSO e valor_se_falso for vazio (ou seja, se houver uma vírgula após valor_se_verdadeiro seguida do parênteses de fechamento), o valor 0 (zero) será retornado.
Comentários
Valor_se_verdadeiro ou Valor_se_falso podem ser outra fórmula.
Podem ser aninhadas até 64 funções SE como argumentos valor_se_verdadeiro e valor_se_falso para criar testes mais elaborados.
E
Retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO.
Um uso comum para a função E é expandir a utilidade de outras funções que realizam testes lógicos. Por exemplo, a função SE realiza um teste lógico e, em seguida, retornará um valor se o teste for avaliado como VERDADEIRO e outro valor se o teste for avaliado como FALSO. Usando a função E como argumento teste_lógico da função SE, você pode testar várias condições diferentes em vez de apenas uma.
Sintaxe
E(lógico1, [lógico2], ...)A sintaxe da função E tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.):
lógico1 Necessário. A primeira condição que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO.
lógico2, ... Opcional. Condições adicionais que você deseja testar que pode ser avaliada como VERDADEIRO ou FALSO, até um máximo de 255 condições.
OU
Retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS.
Sintaxe
OU(lógico1;lógico2;...)
Logical1,logical2,... são de uma a 255 condições que você deseja testar e que podem resultar em VERDADEIRO ou FALSO.
SOMASE
Use a função SOMASE para somar os valores em um intervalo (intervalo: duas ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não adjacentes.) que atendem aos critérios que você especificar. Por exemplo, suponha que em uma coluna que contém números, você deseja somar apenas os valores maiores que 5. É possível usar a seguinte fórmula:
=SOMASE(B2:B25,">5")
Nesse exemplo, os critérios são aplicados aos mesmos valores que estão sendo somados. Se desejar, você pode aplicar os critérios a um intervalo e somar os valores correspondentes em um intervalo correspondente. Por exemplo, a fórmula =SOMASE(B2:B5, "John", C2:C5) soma apenas os valores no intervalo C2:C5, em que as células correspondentes no intervalo B2:B5 equivalem a "John".
Sintaxe
SOMASE(intervalo, critérios, [intervalo_soma])A sintaxe da função SOMASE tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento,um método, uma função ou um procedimento.):
intervalo Necessário. O intervalo de células que se deseja calcular por critérios. As células em cada intervalo devem ser números e nomes, matrizes ou referências que contêm números. Espaços em branco e valores de texto são ignorados.
critérios Necessário. Os critérios na forma de um número, expressão, referência de célula, texto ou função que define quais células serão adicionadas. Por exemplo, os critérios podem ser expressos como 32, ">32", B5, 32, "32", "maçãs" ou HOJE().
Importante: Qualquer critério de texto ou qualquer critério que inclua símbolos lógicos ou matemáticos deve estar entre aspas duplas ("). Se os critérios forem numéricos, as aspas duplas não serão necessárias.
intervalo_soma Opcional. As células reais a serem adicionadas, se você quiser adicionar células diferentes das especificadas no argumento de intervalo. Se o argumento intervalo_soma for omitido, o Excel adicionará as células especificadas no argumento intervalo (as mesmas células às quais os critérios são aplicados).
Você pode usar os caracteres curinga — o ponto de interrogação (?) e o asterisco (*) — como o argumento critérios. Um ponto de interrogação corresponde qualquer caractere simples, e um asterisco corresponde qualquer seqüência de caracteres. Se quiser localizar um ponto de interrogação ou asterisco real, digite um til (~) antes do caractere.
CONT.SE
A função CONT.SE conta o número de células dentro de um intervalo que atendem a um único critério que você especifica. Por exemplo, é possível contar todas as células que começam com uma certa letra ou todas as células que contêm um número maior do que ou menor do que um número que você especificar. Por exemplo, suponha uma planilha que contenha uma lista de tarefas na coluna A e o nome da pessoa atribuída a cada tarefa na coluna B. Você pode usar a função CONT.SE para contar quantas vezes o nome de uma pessoa aparece na coluna B e, dessa maneira, determinar quantas tarefas são atribuídas a essa pessoa. Por exemplo:
=CONT.SE(B2:B25,"Nancy")
Sintaxe
CONT.SE(intervalo, critérios)A sintaxe da função CONT.SE tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.):
intervalo Necessário. Uma ou mais células a serem contadas, incluindo números ou nomes, matrizes ou referências que contêm números. Campos em branco e valores de texto são ignorados.
critérios Necessário. Um número, uma expressão, uma referência de célula ou uma cadeia de texto que define quais células serão contadas. Por exemplo, os critérios podem ser expressos como 32, "32", ">32", "maçãs" ou B4.
CONT.NUM
A função CONT.NÚM conta o número de células que contêm números e conta os números na lista de argumentos. Use a função CONT.NÚM para obter o número de entradas em um campo de número que esteja em um intervalo ou uma matriz de números. Por exemplo, você pode inserir a seguinte fórmula para contar os números no intervalo A1:A20:
=CONT.NÚM(A1:A20)
Neste exemplo, se cinco das células no intervalo contiverem números, o resultado será 5 .
Sintaxe
CONT.NÚM(valor1, [valor2],...)A sintaxe da função CONT.NÚM tem estes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.):
valor1 Necessário. O primeiro item, referência de célula ou intervalo em que você deseja contar números.
valor2, ... Opcional. Até 255 itens, referências de célula ou intervalos adicionais em que você deseja contar números.
CONT.VALORES
A função CONT.VALORES conta o número de células que não estão vazias em um intervalo (intervalo: duas ou mais células em uma planilha. As células de um intervalo podem ser adjacentes ou não adjacentes.).
Sintaxe
CONT.VALORES(valor1, [valor2], ...)A sintaxe da função CONT.VALORES tem os seguintes argumentos (argumento: um valor que fornece informações a uma ação, um evento, um método, uma função ou um procedimento.):
valor1 Necessário. O primeiro argumento que representa os valores que você deseja contar.
valor2, ... Opcional. Argumentos adicionais que representam os valores que você deseja contar, até o máximo de 255 argumentos.
CONTAR.VAZIO
Conta o número de células vazias no intervalo especificado.
Sintaxe
CONTAR.VAZIO(intervalo)
Intervalo é o intervalo no qual as células em branco serão contadas.
PROCV
Localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela. 
O V em PROCV significa vertical. Use PROCV em vez de PROCH quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você deseja procurar.
Sintaxe
PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)
Valor_procurado O valor a ser procurado na primeira coluna da matriz (matriz: usada para criar fórmulas únicas que produzem vários resultados ou que operam em um grupo de argumentos organizados em linhas e colunas. Um intervalo de matrizes compartilha uma fórmula comum; uma constante de matriz é um grupo de constantes usado como um argumento.) da tabela. O Valor_procurado pode ser um valor ou uma referência. Se o valor_procurado for menor do que o menor valor na primeira coluna de matriz_tabela, o PROCV retornará o valor de erro #N/D.
Matriz_tabela Duas ou mais colunas de dados. Use uma referência para um intervalo ou um nome de intervalo. Os valores na primeira coluna de matriz_tabela são os valores procurados por valor_procurado. Os valores podem ser texto, números ou valores lógicos. Textos em maiúsculas e minúsculas são equivalentes. 
Núm_índice_coluna O número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. Um núm_índice_coluna de 1 retornará o valor na primeira coluna em matriz_tabela; um núm_índice_coluna de 2 retornará o valor na segunda coluna em matriz_tabela, e assim por diante. Se núm_índice_coluna for:
Menor que 1, PROCV retornará o valor de erro #VALOR!.
Maior do que o número de colunas em matriz_tabela, PROCV retornará o valor de erro #REF!.
Procurar_intervalo Um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada.
Se VERDADEIRO ou omitido, uma correspondência exata ou aproximada é retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado é retornado. 
Os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem crescente (classificados em ordem crescente) e não possuir registros vazios.
Se FALSO, PROCV encontrará somente uma correspondência exata. Nesse caso, os valores na primeira coluna da matriz_tabela não precisam ser classificados. Se houver dois ou mais valores na primeira coluna de matriz_tabela que não coincidem com o valor_procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata for localizada, o valor de erro #N/D será retornado.
CONCATENAR
Agrupa duas ou mais cadeias de caracteres em uma única cadeia de caracteres.
Sintaxe
CONCATENAR (texto1;texto2;...)
Texto1; texto2; ... são de 2 a 255 itens de texto a serem agrupados em um único item de texto. Os itens de texto podem ser cadeia de caracteres, números ou referências a células únicas.
Obs.: Você também pode usar o operador de cálculo de 'E' comercial, em vez da função CONCATENAR, para agrupar itens de texto. Por exemplo, =A1&B1 retornará o mesmo valor que =CONCATENAR(A1;B1).
HOJE
Retorna o número de série da data atual. O número de série é o código de data/hora usado pelo Excel para cálculos de data e hora. Se o formato da célula era Geral antes de a função ser inserida, o Excel irá transformar o formato da célula em Data. Se quiser exibir o número de série, será necessário alterar o formato das células para Geral ou Número.
A função HOJE é útil quando você precisa ter a data atual exibida em uma planilha, independentemente de quando a pasta de trabalho for aberta. Ela também é útil para o cálculo de intervalos. Por exemplo, se vocêsouber que alguém nasceu em 1963, poderá usar a seguinte fórmula para descobrir a idade dessa pessoa a partir do aniversário deste ano:
=ANO(HOJE())-1978
Essa fórmula usa a função HOJE como argumento da função ANO de forma a obter o ano atual e, em seguida, subtrai 1963, retornando a idade da pessoa.
AGORA
Retorna o número de série da data e da hora atual. Se o formato da célula era Geral antes de a função ter sido inserida, o Excel irá transformar o formato dessa célula no mesmo formato de data e hora especificado nas configurações regionais de data e hora do Painel de Controle. Você pode alterar o formato de data e hora da célula usando os comandos no grupo Número da guia Início, na Faixa de Opções.
A função AGORA é útil quando você precisa exibir a data e a hora atuais em uma planilha ou calcular um valor com base na data e na hora atuais e ter esse valor atualizado sempre que abrir a planilha.
 Sintaxe
AGORA()
*Esta função não possui argumentos.
Classificar Dados (Ordenar Crescente ou Decrescente)
Um conjunto de dados pode ser ordenado por descrição, data, valor ou qualquer tipo de informação definida nas suas colunas. Pode ainda ser crescente ou decrescente, conforme a necessidade do usuário.
Selecione uma coluna de dados alfanuméricos em um intervalo de células ou certifique-se de que a célula ativa está em uma coluna da tabela que contenha dados alfanuméricos.
Na guia Início, olhando mais a direita, localize e clique em Classificar e Filtrar.
Siga um destes procedimentos:
Para classificar em ordem alfanumérica crescente, clique em Classificar de A a Z.
Para classificar em ordem alfanumérica decrescente, clique em Classificar de Z a A.
Como opção, você pode fazer uma classificação que diferencie letras maiúsculas de minúsculas.
Subtotais
É usada para resumir dados de uma forma fácil, para uma análise e melhor entendimento de volumes muito grandes de dados.
É importante lembrar que para um melhor resultado é importante antes ordenar as colunas do banco de dados conforme os totais que deseja.
Passos:
Na tabela de exemplo, foram ordenados em ordem crescente pela coluna “Cidade” e depois pela coluna “Bairro”.
Na guia Dados, mais a direita encontra-se a ferramenta Subtotais.
Em seguida personalize conforme segue:
	A cada alteração em: CIDADE
	Usar a função: SOMA
	Adicionar subtotal a: CREDITOS
Depois OK.
A tabela ficará desta forma:
Filtros
O filtro pode ser automático, sendo assim dinâmico, ou avançado, gerando uma segunda tabela:
	Filtro automático:
	Basta clicar em qualquer posição dentro da tabela e em seguida clicar na guia Dados e na ferramenta Filtro. 
Em seguida note que em cada título de coluna surgiu uma seta. São os filtros.
Desmarque as opções que não deseja exibir e logo a tabela oculta estes dados, permitindo uma visualização apenas dos dados que ficaram marcados. Pronto!
Para reverter basta remarcar as colunas de dados e para remover o filtro basta clicar novamente no botão da ferramenta Filtro, na guia Dados.
Validação de dados
A validação de dados é usada para controlar os tipos de dados inseridos nas células. Por exemplo, é possível restringir a entrada de datas anteriores à data atual, ou ainda a entrada de valores negativos.
Selecione a célula ou conjunto de células que deseja inserir validação.
Na guia Dados, clique na ferramenta Validação de Dados.
Na janela de validação, em Permitir:, selecione a opção desejada. No exemplo será escolhido “decimal”.
É possível também definir um intervalo, no qual foi definido que o valor de entrada deve ser entre 0 (zero) e 50,00.
Pode ser definida uma mensagem de entrada, que será exibida no momento em que o usuário clicar na célula em questão.
E finalmente, é importante definir o “Alerta de erro”, que é o tipo de advertência que ocorre caso o usuário digite um valor inválido:
	Se for do tipo “Informações” o Excel avisa que o valor é inválido, mas aceita.
	Se for do tipo “Aviso” o Excel avisa e pergunta se quer continuar assim mesmo.
	Se for do tipo “Parar” o Excel não aceitará o valor e pedirá que digite outro.
Confirme tudo e faça os testes necessários.
Proteção de dados
Proteger dados de uma planilha é essencial para manter a integridade das informações. Por isso o Excel possui recursos para inserir senhas e bloquear o acesso a determinadas informações. Na guia Revisão apresentam-se duas formas de proteção interessantes:
Protegendo a pasta de trabalho:
Protege contra alterações todas as planilhas da pasta de trabalho, isto é, todo o arquivo do Excel. No caso do exemplo abaixo, note na barra de título que se trata do arquivo “BD.xlsx”.
A ferramenta Proteger pasta de trabalho impede alterações indesejadas na estrutura como a movimentação, exclusão ou adição de planilhas. Ainda é possível especificar uma senha para que apenas pessoas autorizadas possam desproteger a pasta de trabalho e permitir alterações.
Protegendo planilha
Impede alterações indesejadas nos dados de uma planilha, especificando as informações que podem ser alteradas.
Por exemplo, você pode impedir a edição de células bloqueadas ou a alteração da formatação do documento.
Também é possível definir uma senha para desproteger a planilha e permitir as alterações.
Senha de abertura do arquivo
Outro recurso de proteção interessante é a possibilidade de proteger a abertura e edição do arquivo com uma senha.
Para isso você precisa entrar em “Salvar como..” e abaixo em “Ferramentas”, clicar em “Opções”.
Aparece então uma janela como esta mostrada abaixo , onde temos as duas opções de proteção:
A senha de proteção impede que se abra o arquivo caso não tenha a senha.
A senha de gravação permite que o arquivo abra como “somente leitura”, sendo que nada que for alterado poderá ser salvo.
Obviamente sempre existem maneiras de cotornar todas estas situações, porém devemos lembrar que todos os recursos de segurança conhecidos, não só os de sistemas de informática, existem para dificultar a ação de pessoas mal intencionadas. Nenhum sistema de segurança é 100% seguro. Se o for, será apenas por algum tempo!
Auditoria
As ferramentas de auditoria servem para conferir a origem dos resultados nas funções, destino dos dados e em que resultados eles influenciam na tabela e ainda rastrear erros nas funções, como referências cruzadas.
Tabela dinâmica
Quando as informações de uma planilha estão organizadas no formato de um banco de dados é possível gerencia-las para extrair apenas os dados necessários.
Estar no formato de banco de dados significa que as informações estão dispostas de modo que cada coluna corresponda a um campo e cada linha corresponda a um registro. Além disso a primeira linha deve conter o nome dos campos.
Também é importante não haver colunas ou linhas totalmente vazias para não comprometer o resultado.
A tabela dinâmica é um recurso do Excel capaz de resumir dados de uma planilha que possui uma grande quantidade de informação. Assim, a tabela dinâmica é capaz de gerar rapidamente respostas que seriam difíceis de obter apenas analisando os dados da planilha.
Com base na tabela dinâmica é possível gerar relatórios e gráficos dinâmicos para uma melhor análise das informações.
Para criar uma tabela dinâmica:
Clique em qualquer do intervalo de dados da planilha.
Clique na guia Inserir, e na ferramenta Tabela Dinâmica.
A caixa de dialogo Criar Tabela Dinâmica é exibida com o campo Tabela/Intervalo já preenchido. Isso ocorre porque selecionamos previamente uma célula da tabela.
Selecione a opção Nova Planilha e clique OK.
Uma nova planilha é criada exibindo do lado esquerdo a área de layout e do lado direito o painel Lista de Campos da Tabela Dinâmica.
Na Lista de Campos da Tabela Dinâmica, clique com o botão direito do mouse sobre o campo Vendedor e selecione Adicionar Rótulos de Coluna.
Após, clique com o botão direito em Cidade e selecione Adicionar aos Rótulos de Linha.
Marque a caixa de seleção ao lado do campo Qtde e a tabela dinâmica deve ficar com o seguinte formato:
*Um recurso que também pode ser utilizado é o Filtro de Relatório.
Gráfico Dinâmico
Verifique seo intervalo de células a ser utilizado está no formato de banco de dados.
Selecione uma célula qualquer do intervalo.
Clique na guia Inserir, Tabela dinâmica e depois em Gráfico Dinâmico.
A caixa de diálogo Criar Tabela Dinâmica com Gráfico Dinâmico é exibida com o campo Tabela/Intervalo já preenchido.
Selecione a opção Nova Planilha e clique e OK.
Uma nova planilha é criada, exibindo a tabela dinâmica e o gráfico dinâmico associado a ela.
Clique com o botão direito do mouse sobre o campo Vendedor e selecione a opção Adicionar a Campos de Legenda (Série).
Clique com o botão direito no campo Cidade e selecione Adicionar a Campos de Eixo (Categorias).
Marque a caixa de seleção ao lado do campo Qtde. Pronto!
*O gráfico dinâmico e a tabela dinâmica acompanhante devem estar sempre na mesma pasta de trabalho.
Formatação Condicional
É usada para realçar resultados, conforme condições pré-definidas. Por exemplo, fazer com que resultados apareçam em vermelho caso algum esteja abaixo do esperado.
Para criar uma formatação condicional:
Selecione as células a serem avaliadas, e clique na guia Inicio e em Formatação Condicional.
Você pode em seguida clicar em Realçar Regras de Células e em É menor do que...
Defina o número mínimo e o tipo de formatação que receberá caso seja menor que o esperado. Pronto!
É possível Realçar de uma lista de resultados os 10 primeiros, 10 últimos, acima da média, entre outras opções muito úteis.
Macros
É a possibilidade de automatizar tarefas rotineiras, como formatações padrão da empresa, sequências de comandos ou mesmo agilizar um processo.
Guia Desenvolvedor
Para trabalhar com Macros é preciso que a guia Desenvolvedor esteja disponível. Caso não esteja visível, proceda da seguinte maneira:
Clique no botão Office e em Opções do Excel.
Clique na categoria Mais Usados e em Opções principais para o trabalho com Excel, marque a opção Mostrar Guia Desenvolvedor na Faixa de Opções. E clique OK.
Gravando Macros
Clique em Gravar Macro
Na caixa de diálogo que se abre, digite um nome para sua Macro.
Defina uma tecla de atalho (cuidado para não anular atalhos conhecidos).
Na lista “Armazenar Macro em...” deixe marcada a opção “esta pasta de trabalho”.
Clique OK para iniciar a gravação.
Execute todas as ações que deseja gravar.
Ao finalizar as ações clique na guia Desenvolvedor e em Parar Gravação.
Salve a pasta de trabalho (arquivo).
Executar Macro por um botão
Ao associar uma Macro a um Botão, bastará clicar sobre ele para executar a Macro.
Clique na guia desenvolvedor e no grupo Controles, clique em Inserir.
Nas opões “Controles de Formulários” clique em “Botão”,
Clique e arraste o mouse na planilha para criar o Botão.
A caixa de diálogo “Atribuir Macro” é exibida. Selecione sua Macro e clique em OK.
Você pode editar o texto do Botão sempre com o botão direito do mouse sobre ele.
Agora selecione conjuntos de células e teste seu Botão de Macro!
Atingir Meta
Se você conhece o resultado que deseja obter de uma fórmula, mas não tem certeza sobre o valor de entrada necessário para chegar a esse resultado, use o recurso Atingir Meta. Por exemplo, suponha que você precise de algum dinheiro emprestado. Você sabe o quanto precisa, por quanto tempo deseja pagar o empréstimo e quanto pode pagar por mês. Nesse caso, você pode usar Atingir Meta para determinar que taxa de juros precisa fixar para atender à sua meta de empréstimo.
Como você deseja calcular a taxa de juros do empréstimo necessária para atender à sua meta, use a função PGTO. A função PGTO calcula um valor de pagamento mensal. Nesse exemplo, o valor de pagamento mensal é a meta que você deseja atingir.
Primeiro, adicione alguns rótulos à primeira coluna para facilitar a leitura da planilha.
Na célula A1, digite Valor do Empréstimo .
Na célula A2, digite Prazo em Meses .
Na célula A3, digite Taxa de Juros.
Na célula A4, digite Pagamento .
Em seguida, adicione os valores que você conhece. 
Na célula B1, digite 100000. Esse é o valor que você deseja pedir emprestado.
Na célula B2, digite 180. Esse é o número de meses no qual você deseja liquidar o empréstimo.
*Embora você saiba o valor desejado para o pagamento, não o insere como valor porque ele é um resultado da fórmula. Em vez disso, adiciona a fórmula à planilha e especifica o valor de pagamento em uma etapa posterior, ao usar Atingir Meta.
Em seguida, adicione a fórmula para a qual você tem uma meta. No exemplo, use a função PGTO:
Na célula B4, digite =PGTO(B3/12;B2;B1). Essa fórmula calcula o valor do pagamento. Nesse exemplo, você deseja pagar R$ 900 por mês, mas não insere esse valor porque pretende usar Atingir Meta para determinar a taxa de juros e Atingir Meta requer que você comece com uma fórmula.
A fórmula faz referência às células B1 e B2, que contêm valores que você especificou nas etapas anteriores. A fórmula também faz referência à célula B3, que é onde você irá especificar que Atingir Meta deve inserir a taxa de juros. A fórmula divide o valor em B3 por 12 porque você especificou um pagamento mensal e a função PGTO assume uma taxa de juros anual.
Como não há nenhum valor na célula B3, o Excel assume uma taxa de juros de 0% e, usando os valores no exemplo, retorna um pagamento de R$ 555,56. Você pode ignorar esse valor por enquanto.
Por fim, formate a célula de destino (B3) de forma que ela exiba o resultado como uma porcentagem.
Na guia Início, no grupo Número, clique em Porcentagem.
Clique em Aumentar Casas Decimais ou Diminuir Casas Decimais para definir o número de casas decimais.
Agora use Atingir Meta para determinar a taxa de juros:
Selecione a célula B4, que é o valor a ser ajustado.
Na guia Dados, no grupo Ferramentas de Dados, clique em Teste de Hipóteses e, em seguida, clique em Atingir Meta.
Na caixa Definir célula, já estará definida a célula F4.
Na caixa Para valor, digite o resultado da fórmula desejado. No exemplo, esse valor é -900. Observe que o número é negativo porque representa um pagamento. 
Na caixa Por alteração, insira a referência para a célula que contém o valor que você deseja ajustar. No exemplo, essa referência é a célula B3.
Clique em OK.
O recurso Atingir Meta é executado e gera um resultado, como mostra a ilustração a seguir.
17