Análise de dados do Excel - Guia rápido

A Análise de Dados é um processo de inspeção, limpeza, transformação e modelagem de dados com o objetivo de descobrir informações úteis, sugerir conclusões e apoiar a tomada de decisões

Tipos de análise de dados

Existem várias técnicas de análise de dados abrangendo vários domínios, como negócios, ciências, ciências sociais, etc., com uma variedade de nomes. As principais abordagens de análise de dados são -

• Mineração de dados
• Business Intelligence
• Análise Estatística
• Análise preditiva
• Análise de Texto

Mineração de dados

Data Mining é a análise de grandes quantidades de dados para extrair padrões de dados interessantes e anteriormente desconhecidos, dados incomuns e as dependências. Observe que o objetivo é a extração de padrões e conhecimento de grandes quantidades de dados e não a extração de dados em si.

A análise de mineração de dados envolve métodos de ciência da computação na interseção da inteligência artificial, aprendizado de máquina, estatísticas e sistemas de banco de dados.

Os padrões obtidos da mineração de dados podem ser considerados como um resumo dos dados de entrada que podem ser usados ​​em análises posteriores ou para obter resultados de predição mais precisos por um sistema de suporte à decisão.

Business Intelligence

As técnicas e ferramentas de Business Intelligence são para aquisição e transformação de grandes quantidades de dados de negócios não estruturados para ajudar a identificar, desenvolver e criar novas oportunidades estratégicas de negócios.

O objetivo da inteligência de negócios é permitir a fácil interpretação de grandes volumes de dados para identificar novas oportunidades. Ele ajuda a implementar uma estratégia eficaz com base em percepções que podem fornecer às empresas uma vantagem competitiva de mercado e estabilidade de longo prazo.

Análise Estatística

Estatística é o estudo de coleta, análise, interpretação, apresentação e organização de dados.

Na análise de dados, duas metodologias estatísticas principais são usadas -

• Descriptive statistics - Na estatística descritiva, os dados de toda a população ou amostra são resumidos com descritores numéricos, como -

  • Média, Desvio Padrão para Dados Contínuos

  • Frequência, porcentagem para dados categóricos

• Inferential statistics- Ele usa padrões nos dados da amostra para fazer inferências sobre a população representada ou contabilizar a aleatoriedade. Essas inferências podem ser -

  • respondendo perguntas sim / não sobre os dados (teste de hipótese)

  • estimar características numéricas dos dados (estimativa)

  • descrevendo associações dentro dos dados (correlação)

  • modelagem de relacionamentos dentro dos dados (por exemplo, análise de regressão)

Análise preditiva

O Predictive Analytics usa modelos estatísticos para analisar dados atuais e históricos para previsões (previsões) sobre eventos futuros ou de outra forma desconhecidos. Nos negócios, a análise preditiva é usada para identificar riscos e oportunidades que auxiliam na tomada de decisões.

Análise de Texto

Text Analytics, também conhecido como Text Mining ou Text Data Mining, é o processo de derivar informações de alta qualidade de texto. A mineração de texto geralmente envolve o processo de estruturar o texto de entrada, derivando padrões dentro dos dados estruturados usando meios como aprendizado de padrão estatístico e, finalmente, avaliação e interpretação da saída.

Processo de Análise de Dados

A Análise de Dados é definida pelo estatístico John Tukey em 1961 como "Procedimentos de análise de dados, técnicas de interpretação dos resultados de tais procedimentos, formas de planejamento da coleta de dados para tornar sua análise mais fácil, precisa ou precisa, e todo o maquinário e resultados de estatísticas (matemáticas) que se aplicam à análise de dados. ”

Assim, a análise de dados é um processo para obter dados grandes e não estruturados de várias fontes e convertê-los em informações úteis para -

• Respondendo a perguntas
• Hipóteses de teste
• Decision-making
• Desmentindo teorias

Análise de dados com Excel

O Microsoft Excel fornece vários meios e maneiras de analisar e interpretar dados. Os dados podem ser de várias fontes. Os dados podem ser convertidos e formatados de várias maneiras. Pode ser analisado com os comandos, funções e ferramentas relevantes do Excel - abrangendo formatação condicional, intervalos, tabelas, funções de texto, funções de data, funções de tempo, funções financeiras, subtotais, análise rápida, auditoria de fórmula, ferramenta de consulta, análise de variações hipotéticas, Solucionadores, modelo de dados, PowerPivot, PowerView, PowerMap, etc.

Você aprenderá essas técnicas de análise de dados com o Excel como parte de duas partes -

• Análise de dados com Excel e
• Análise Avançada de Dados com Excel

Análise de dados é um processo de coleta, transformação, limpeza e modelagem de dados com o objetivo de descobrir as informações necessárias. Os resultados assim obtidos são comunicados, sugerindo conclusões e subsidiando a tomada de decisões. A visualização de dados às vezes é usada para retratar os dados para facilitar a descoberta de padrões úteis nos dados. Os termos Modelagem de Dados e Análise de Dados significam o mesmo.

O Processo de Análise de Dados consiste nas seguintes fases que são iterativas por natureza -

• Especificação de Requisitos de Dados
• Coleção de dados
• Processamento de dados
• Limpeza de Dados
• Análise de dados
• Communication

Especificação de Requisitos de Dados

Os dados necessários para a análise são baseados em uma pergunta ou experimento. Com base nos requisitos daqueles que dirigem a análise, os dados necessários como entradas para a análise são identificados (por exemplo, População de pessoas). Variáveis ​​específicas relacionadas a uma população (por exemplo, Idade e Renda) podem ser especificadas e obtidas. Os dados podem ser numéricos ou categóricos.

Coleção de dados

Coleta de dados é o processo de coleta de informações sobre variáveis ​​específicas identificadas como requisitos de dados. A ênfase está em garantir uma coleta de dados precisa e honesta. A coleta de dados garante que os dados coletados sejam precisos, de forma que as decisões relacionadas sejam válidas. A coleta de dados fornece uma linha de base para medir e uma meta para melhorar.

Os dados são coletados de várias fontes, desde bancos de dados organizacionais até as informações em páginas da web. Os dados assim obtidos podem não estar estruturados e conter informações irrelevantes. Portanto, os dados coletados devem ser submetidos ao Processamento de Dados e à Limpeza de Dados.

Processamento de dados

Os dados coletados devem ser processados ​​ou organizados para análise. Isso inclui estruturar os dados conforme necessário para as Ferramentas de Análise relevantes. Por exemplo, os dados podem ter que ser colocados em linhas e colunas em uma tabela dentro de uma planilha ou aplicativo estatístico. Pode ser necessário criar um modelo de dados.

Limpeza de Dados

Os dados processados ​​e organizados podem estar incompletos, conter duplicatas ou conter erros. Limpeza de dados é o processo de prevenção e correção desses erros. Existem vários tipos de limpeza de dados que dependem do tipo de dados. Por exemplo, ao limpar os dados financeiros, certos totais podem ser comparados com números publicados confiáveis ​​ou limites definidos. Da mesma forma, métodos de dados quantitativos podem ser usados ​​para detecção de outliers que seriam subsequentemente excluídos na análise.

Análise de dados

Os dados processados, organizados e limpos estariam prontos para a análise. Várias técnicas de análise de dados estão disponíveis para entender, interpretar e derivar conclusões com base nos requisitos. A visualização de dados também pode ser usada para examinar os dados em formato gráfico, para obter informações adicionais sobre as mensagens nos dados.

Modelos de dados estatísticos como correlação, análise de regressão podem ser usados ​​para identificar as relações entre as variáveis ​​de dados. Esses modelos que são descritivos dos dados são úteis para simplificar a análise e comunicar os resultados.

O processo pode exigir Limpeza de Dados ou Coleta de Dados adicionais e, portanto, essas atividades são iterativas por natureza.

Comunicação

Os resultados da análise de dados devem ser relatados em um formato conforme exigido pelos usuários para apoiar suas decisões e ações futuras. O feedback dos usuários pode resultar em análises adicionais.

Os analistas de dados podem escolher técnicas de visualização de dados, como tabelas e gráficos, que auxiliam na comunicação da mensagem de forma clara e eficiente aos usuários. As ferramentas de análise fornecem facilidade para destacar as informações necessárias com códigos de cores e formatação em tabelas e gráficos.

O Excel fornece comandos, funções e ferramentas que facilitam suas tarefas de análise de dados. Você pode evitar muitos cálculos demorados e / ou complexos usando o Excel. Neste tutorial, você começará a ver como realizar a análise de dados com o Excel. Você vai entender com exemplos relevantes, o uso passo a passo dos comandos do Excel e capturas de tela em cada etapa.

Faixas e tabelas

Os dados que você possui podem estar em um intervalo ou em uma tabela. Certas operações nos dados podem ser executadas se os dados estiverem em um intervalo ou em uma tabela.

No entanto, existem certas operações que são mais eficazes quando os dados estão em tabelas, em vez de intervalos. Existem também operações exclusivamente para tabelas.

Você também compreenderá as maneiras de analisar dados em intervalos e tabelas. Você entenderá como nomear intervalos, usar os nomes e gerenciar os nomes. O mesmo se aplica aos nomes nas tabelas.

Limpeza de dados - funções de texto, datas e horários

Você precisa limpar os dados obtidos de várias fontes e estruturá-los antes de prosseguir com a análise de dados. Você aprenderá como limpar os dados.

• Com funções de texto
• Contendo Valores de Data
• Contendo Valores de Tempo

Formatação condicional

O Excel fornece comandos de formatação condicional que permitem colorir as células ou fonte, ter símbolos ao lado dos valores nas células com base em critérios predefinidos. Isso ajuda a visualizar os valores proeminentes. Você entenderá os vários comandos para formatar condicionalmente as células.

Classificação e filtragem

Durante a preparação da análise de dados e / ou para exibir certos dados importantes, você pode ter que classificar e / ou filtrar seus dados. Você pode fazer o mesmo com as opções de classificação e filtragem fáceis de usar do Excel.

Subtotais com intervalos

Como você sabe, a Tabela Dinâmica é normalmente usada para resumir dados. No entanto, Subtotais com intervalos é outro recurso fornecido pelo Excel que permitirá agrupar / desagrupar dados e resumir os dados presentes em intervalos com etapas fáceis.

Análise Rápida

Com a ferramenta Quick Analysis no Excel, você pode executar rapidamente várias tarefas de análise de dados e fazer visualizações rápidas dos resultados.

Noções básicas sobre funções de pesquisa

As funções de pesquisa do Excel permitem que você encontre os valores dos dados que correspondem a um critério definido de uma grande quantidade de dados.

Tabelas Dinâmicas

Com as tabelas dinâmicas, você pode resumir os dados e preparar relatórios dinamicamente alterando o conteúdo da tabela dinâmica.

Visualização de dados

Você aprenderá várias técnicas de visualização de dados usando gráficos do Excel. Você também aprenderá a criar gráfico de banda, gráfico de termômetro, gráfico de Gantt, gráfico em cascata, minigráficos e gráficos dinâmicos.

Data de validade

Pode ser necessário que apenas valores válidos sejam inseridos em certas células. Caso contrário, eles podem levar a cálculos incorretos. Com comandos de validação de dados, você pode facilmente configurar valores de validação de dados para uma célula, uma mensagem de entrada perguntando ao usuário o que se espera que seja inserido na célula, validar os valores inseridos com os critérios definidos e exibir uma mensagem de erro em caso de entradas incorretas.

Analise financeira

O Excel oferece várias funções financeiras. No entanto, para problemas comuns que requerem análise financeira, você pode aprender como usar uma combinação dessas funções.

Trabalho com várias planilhas

Você pode ter que realizar vários cálculos idênticos em mais de uma planilha. Em vez de repetir esses cálculos em cada planilha, você pode fazer isso em uma planilha e fazer com que apareça nas outras planilhas selecionadas. Você também pode resumir os dados de várias planilhas em uma planilha de relatório.

Auditoria de Fórmula

Ao usar fórmulas, você pode verificar se as fórmulas estão funcionando conforme o esperado. No Excel, os comandos de Auditoria de Fórmula ajudam a rastrear os valores precedentes e dependentes e a verificação de erros.

Investigar

O Excel também fornece o suplemento Inquire que permite comparar duas pastas de trabalho para identificar alterações, criar relatórios interativos e exibir as relações entre pastas de trabalho, planilhas e células. Você também pode limpar a formatação excessiva em uma planilha que torna o Excel lento ou aumenta o tamanho do arquivo.

Ao fazer a Análise de Dados, referir-se a vários dados será mais significativo e fácil se a referência for por Nomes em vez de referências de células - uma única célula ou um intervalo de células. Por exemplo, se você estiver calculando o valor presente líquido com base em uma taxa de desconto e uma série de fluxos de caixa, a fórmula

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

é mais significativo do que

C10 = NPV (C2, C6: C8)

Com o Excel, você pode criar e usar nomes significativos para várias partes de seus dados. As vantagens de usar nomes de intervalo incluem -

• Um nome de intervalo significativo (como Cash_Flows) é muito mais fácil de lembrar do que um endereço de intervalo (como C6: C8).

• Inserir um nome é menos sujeito a erros do que inserir um endereço de célula ou intervalo.

• Se você digitar um nome incorretamente em uma fórmula, o Excel exibirá um #NAME? erro.

• Você pode mover rapidamente para áreas de sua planilha usando os nomes definidos.

• Com os nomes, suas fórmulas serão mais compreensíveis e fáceis de usar. Por exemplo, uma fórmula Net_Income = Gross_Income - Deductions é mais intuitiva do que C40 = C20 - B18.

• Criar fórmulas com nomes de intervalo é mais fácil do que com células ou endereços de intervalo. Você pode copiar um nome de célula ou intervalo em uma fórmula usando o preenchimento automático da fórmula.

Neste capítulo, você aprenderá -

• Regras de sintaxe para nomes.
• Criação de nomes para referências de células.
• Criando nomes para constantes.
• Gerenciando os nomes.
• Escopo de seus nomes definidos.
• Editando nomes.
• Filtrando nomes.
• Excluindo nomes.
• Aplicando nomes.
• Usando nomes em uma fórmula.
• Exibindo nomes em uma pasta de trabalho.
• Usando nomes de colagem e lista de colagem.
• Usando nomes para interseções de alcance.
• Copiando fórmulas com nomes.

Copiando o nome usando o preenchimento automático de fórmula

Digite a primeira letra do nome na fórmula. Uma caixa suspensa aparece com nomes de funções e nomes de intervalos. Selecione o nome necessário. Ele é copiado em sua fórmula.

Regras de sintaxe de nome de intervalo

O Excel tem as seguintes regras de sintaxe para nomes -

• Você pode usar qualquer combinação de letras, números e símbolos - sublinhados, barras invertidas e pontos. Outros símbolos não são permitidos.

• Um nome pode começar com um caractere, sublinhado ou barra invertida.

• Um nome não pode começar com um número (exemplo - 1º Trimestre) ou se parecer com um endereço de célula (exemplo - QTR1).

• Se você preferir usar esses nomes, coloque um sublinhado ou uma barra invertida antes do nome (exemplo - \ 1stQuarter, _QTR1).

• Os nomes não podem conter espaços. Se quiser distinguir duas palavras em um nome, você pode usar sublinhado (exemplo- Cash_Flows em vez de Cash Flows)

• Seus nomes definidos não devem coincidir com os nomes definidos internamente do Excel, como Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title. Se você definir os mesmos nomes, eles substituirão os nomes internos do Excel e você não receberá nenhuma mensagem de erro. No entanto, é aconselhável não fazer isso.

• Mantenha os nomes curtos, mas compreensíveis, embora você possa usar até 255 caracteres

Criação de nomes de intervalo

Você pode criar nomes de intervalo de duas maneiras -

• Usando o Name box.

• Usando o New Name caixa de diálogo.

• Usando o Selection caixa de diálogo.

Crie um nome de intervalo usando a caixa de nome

Para criar um nome de intervalo, usando o Namea caixa à esquerda da barra de fórmulas é o caminho mais rápido. Siga as etapas abaixo -

Step 1 - Selecione o intervalo para o qual deseja definir um Nome.

Step 2 - Clique na caixa Nome.

Step 3 - Digite o nome e pressione Enter para criar o Nome.

Crie um nome de intervalo usando a caixa de diálogo Novo nome

Você também pode criar nomes de intervalo usando a caixa de diálogo Novo nome na guia Fórmulas.

Step 1 - Selecione o intervalo para o qual deseja definir um nome.

Step 2 - Clique na guia Fórmulas.

Step 3- Clique em Definir nome no grupo Nomes definidos. oNew Name a caixa de diálogo aparece.

Step 4 - Digite o nome na caixa ao lado de Nome

Step 5- Verifique se o intervalo selecionado e exibido na caixa Referências está correto. Clique OK.

Crie um nome de intervalo usando a caixa de diálogo Criar nomes a partir da seleção

Você também pode criar nomes de intervalo usando o Create Names na caixa de diálogo Seleção da guia Fórmulas, quando você tem valores de Texto adjacentes ao seu intervalo.

Step 1 - Selecione o intervalo para o qual deseja definir um nome junto com a linha / coluna que contém o nome.

Step 2 - Clique na guia Fórmulas.

Step 3 - Clique Create from Selectionno grupo Nomes definidos. oCreate Names from Selection a caixa de diálogo aparece.

Step 4 - Selecione a linha superior conforme o Texto aparece na linha superior da seleção.

Step 5- Verifique o intervalo que foi selecionado e exibido na caixa ao lado de Refere-se a estar correto. Clique OK.

Agora, você pode encontrar o maior valor no intervalo com =Sum(Nome do Aluno), conforme mostrado abaixo -

Você também pode criar nomes com seleção múltipla. No exemplo abaixo, você pode nomear a linha de marcas de cada aluno com o nome do aluno.

Agora, você pode encontrar as notas totais de cada aluno com =Sum (nome do aluno), conforme mostrado abaixo.

Criação de nomes para constantes

Suponha que você tenha uma constante que será usada em toda a sua pasta de trabalho. Você pode atribuir um nome a ele diretamente, sem colocá-lo em uma célula.

No exemplo abaixo, a taxa de juros do banco de poupança é definida como 5%.

• Clique em Definir nome.
• Na caixa de diálogo Novo nome, digite Savings_Bank_Interest_Rate na caixa Nome.
• Em Escopo, selecione Pasta de trabalho.
• Na caixa Refere-se a, limpe o conteúdo e digite 5%.
• Clique OK.

O nome Savings_Bank_Interest_Rateé definido como 5% constantes. Você pode verificar isso no Gerenciador de nomes. Você pode ver que o valor está definido como 0,05 e noRefers to = 0,05 é colocado.

Gerenciando Nomes

Uma pasta de trabalho do Excel pode ter qualquer número de células nomeadas e intervalos. Você pode gerenciar esses nomes com o Gerenciador de nomes.

• Clique na guia Fórmulas.

• Clique Name Manager no Defined Namesgrupo. oName Managera caixa de diálogo aparece. Todos os nomes definidos na pasta de trabalho atual são exibidos.

A lista de Names são exibidos com o definido Values, Cell Reference (incluindo o nome da folha), Scope e Comment.

O Name Manager tem as opções para -

• Defina um New Nome com o New Botão.

• Edit um nome definido.

• Delete um nome definido.

• Filter os nomes definidos por categoria.

• Modifique o intervalo de um nome definido que Refers to.

Escopo de um nome

o Scopede um nome por padrão é a pasta de trabalho. Você pode encontrar oScope de nomes definidos na lista de nomes sob o Scope coluna no Name Manager.

Você pode definir o Scope de um New Name quando você define o nome usando New Namecaixa de diálogo. Por exemplo, você está definindo o nome Interest_Rate. Então você pode ver que oScope do New Name Interest_Rate é o Workbook.

Suponha que você queira o Scope desta taxa de juros restrita a este Worksheet só.

Step 1- Clique na seta para baixo na caixa de escopo. As opções de escopo disponíveis aparecem na lista suspensa.

As opções de escopo incluem Workbooke os nomes das planilhas na pasta de trabalho.

Step 2- Clique no nome da planilha atual, neste caso NPV e clique em OK. Você pode definir / localizar o nome da planilha na guia da planilha.

Step 3 - Para verificar se o escopo é uma planilha, clique Name Manager. Na coluna Escopo, você encontrará NPV para Interest_Rate. Isso significa que você pode usar o nome Interest_Rate apenas no NPV da planilha, mas não nas outras planilhas.

Note - Depois de definir o Escopo de um Nome, ele não pode ser modificado posteriormente.

Exclusão de nomes com valores de erro

Às vezes, pode acontecer que a definição do nome possa conter erros por vários motivos. Você pode excluir esses nomes da seguinte maneira -

Step 1 - Clique Filter no Name Manager caixa de diálogo.

As seguintes opções de filtragem aparecem -

• Filtro limpo
• Nomes com escopo na planilha
• Nomes com escopo na pasta de trabalho
• Nomes com erros
• Nomes sem erros
• Nomes Definidos
• Nomes de Tabela

Você pode aplicar Filter ao defined Names selecionando uma ou mais dessas opções.

Step 2 - Selecione Names with Errors. Os nomes que contêm valores de erro serão exibidos.

Step 3 - Da lista obtida de Names, selecione aqueles que deseja excluir e clique Delete.

Você receberá uma mensagem, confirmando a exclusão. Clique OK.

Editando Nomes

Você pode usar o Edit opção no Name Manager caixa de diálogo para -

• Mudar o Name.

• Modifique o Refers to alcance

• Edite o Comment em um Name.

Mudar o nome

Step 1 - Clique na célula que contém a função Large.

Você pode ver que mais dois valores são adicionados ao array, mas não são incluídos na função, pois não fazem parte do Array1.

Step 2 - Clique no Name você deseja editar no Name Managercaixa de diálogo. Nesse caso,Array1.

Step 3 - Clique Edit. oEdit Name a caixa de diálogo aparece.

Step 4 - Mude o Name digitando o novo nome que você deseja no Name Box.

Step 5 - Clique no Range botão à direita de Refers to Boxe inclui as novas referências de células.

Step 6 - Adicionar um Comment (Opcional)

Notar que Scope é desativado e, portanto, não pode ser alterado.

Clique OK. Você observará as mudanças feitas.

Aplicando Nomes

Considere o seguinte exemplo -

Como você pode observar, os nomes não são definidos e usados ​​na função PMT. Se você colocar esta função em algum outro lugar da planilha, também precisará se lembrar onde exatamente estão os valores dos parâmetros. Você sabe que usar nomes é a melhor opção.

Neste caso, a função já está definida com referências de células que não possuem nomes. Você ainda pode definir nomes e aplicá-los.

Step 1 - Usando Create from Selection, defina os nomes.

Step 2- Selecione a célula que contém a fórmula. Clique

ao lado deDefine Name no Defined Names grupo no Formulasaba. Na lista suspensa, clique emApply Names.

Step 3 - o Apply Namesa caixa de diálogo aparece. Selecione osNames que você quer Apply and click OK.

The selected names will be applied to the selected cells.

You can also Apply Names to an entire worksheet, by selecting the worksheet and repeating the above steps.

Using Names in a Formula

You can use a Name in a Formula in the following ways −

• Typing the Name if you remember it, or

• Typing first one or two letters and using the Excel Formula Autocomplete feature.

• Clicking Use in Formula in the Defined Names group on the Formulas tab.

  • Select the required Name from the drop-down list of defined names.

  • Double-click on that name.

• Using the Paste Name dialog box.

  • Select the Paste Names option from the drop-down list of defined names. The Paste Name dialog box appears.

  • Select the Name in the Paste Names dialog box and double-click it.

Viewing Names in a Workbook

You can get all the Names in your workbook along with their References and Save them or Print them.

• Click an empty Cell where you want to copy the Names in your workbook.

• Click Use in Formula in the Defined Names group.

• Click Paste Names from the drop-down list.

• Click Paste List in the Paste Name dialog box that appears.

The list of names and their corresponding references are copied at the specified location on your worksheet as shown in the screen shot given below −

Using Names for Range Intersections

Range Intersections are those individual cells that have two Ranges in common.

For example, in the data given below, the Range B6:F6 and the Range C3:C8 have Cell C6 in common, which actually represents the marks scored by the student Kodeda, Adam in Exam 1.

You can make this more meaningful with the Range Names.

• Create Names with Create from Selection for both Students and Exams.

• Your Names will look as follows −

• Type =Kodeda_Adam Exam_1 in B11.

Here, you are using the Range Intersection operation, space between the two ranges.

This will display marks of Kodeda, Adam in Exam 1, that are given in Cell C6.

Copying Formulas with Names

You can copy a formula with names by Copyand Paste within the same worksheet.

You can also copy a formula with names to a different worksheet by copy and paste, provided all the names in the formula have workbook as Scope. Otherwise, you will get a #VALUE error.

A Table is a rectangular range of structured data. The key features are −

• Each row in the table corresponds to a single record of the data. Example - Employee information.

• Each column contains a specific piece of information. Exmaple - The columns can contain data such as name, employee number, hire date, salary, department, etc.

• The top row describes the information contained in each column and is referred to as header row.

• Each entry in the top row is referred to as column header.

You can create and use an Excel table to manage and analyze data easily. Further, with Excel Tables you get built-in Filtering, Sorting, and Row Shading that ease your reporting activities.

Further, Excel responds to the actions performed on a table intelligently. For example, you have a formula in a column or you have created a chart based on the data in the table. When you add more data to the table (i.e., more rows), Excel extends the formula to the new data and the chart expands automatically.

Difference between Tables and Ranges

Following are the differences between a table and range −

• A table is a more structured way of working with data than a range.
• You can convert a range into a table and Excel automatically provides −
  • a Table Name
  • Column Header Names
  • Formatting to the Data (Cell Color and Font Color) for better Visualization

Tables provide additional features that are not available for ranges. These are −

• Excel provides table tools in the ribbon ranging from properties to styles.

• Excel automatically provides a Filter button in each column header to sort the data or filter the table such that only rows that meet your defined criteria are displayed.

• If you have multiple rows in a table, and you scroll down the sheet so that the header row disappears, the column letters in the worksheet are replaced by the table headers.

• When you place a formula in any cell in a column of the table, it gets propagated to all the cells in that column.

• You can use table name and column header names in the formulas, without having to use cell references or creating range names.

• You can extend the table size by adding more rows or more columns by clicking and dragging the small triangular control at the lower-right corner of the lower-right cell.

• You can create and use slicers for a table for filtering data.

You will learn about all these Features in this Chapter.

Create Table

To create a table from the data you have on the worksheet, follow the given steps −

Step 1 − Select the Range of Cells that you want to include in the Table. Cells can contain data or can be empty. The following Range has 290 rows of employee data. The top row of the data has headers.

Step 2 − Under the Insert tab, in the Tables group, click Tables. The Create Table dialog box appears. Check that the data range selected in the Where is the data for your table? Box is correct.

Step 3 − Check the My table has headers box if the top row of the selected Range contains data that you want to use as the Table Headers.

Note − If you do not check this box, your table will have Headers – Column1, Column2, …

Step 4 − Click OK.

Range is converted to Table with the default Style.

Step 5 − You can also convert a range to a table by clicking anywhere on the range and pressing Ctrl+T. A Create Table dialog box appears and then you can repeat the steps as given above.

Table Name

Excel assigns a name to every table that is created.

Step 1 − To look at the name of the table you just created, click table, click on table tools – design tab on the Ribbon.

Step 2 − In the Properties group, in the Table Name box, your Table Name will be displayed.

Step 3 − You can edit this Table Name to make it more meaningful to your data.

Step 4 − Click the Table Name box. Clear the Name and type Emp_Data.

Note − The syntax rules of range names are applicable to table names.

Managing Names in a Table

You can manage table names just similar to how you manage range names with Name Manager.

• Click the Table.

• Click Name Manager in the Defined Names group on Formulas tab.

The Name Manager dialog box appears and you can find the Table Names in your workbook.

You can Edit a Table Name or add a comment with New option in the Name Manager dialog box. However, you cannot change the range in Refers to.

You can Create Names with column headers to use them in formulas, charts, etc.

• Click the Column Header EmployeeID in the Table.

• Click Name Manager.

• Click New in the Name Manager dialog box.

The New Name dialog box appears.

In the Name box, you can find the Column Header, and in the Refers to box,you will find Emp_Data[[#Headers],[EmployeeID]].

As you observe, this is a quick way of defining Names in a Table.

Table Headers replacing Column Letters

When you are working with more number of rows of data in a table, you may have to scroll down to look at the data in those rows.

However, while doing so, you also require the table headers to identify which value belongs to which column. Excel automatically provides a smooth way of doing this. As you scroll down your data, the column letters of the worksheet themselves get converted to table headers.

In the worksheet given below, the column letters are appearing as they are and the table headers are in row 2. 21 rows of 290 rows of data are visible.

Scroll down to see the table rows 25 – 35. The table headers will replace the column letters for the table columns. Other column letters remain as they are.

Propagation of a Formula in a Table

In the table given below, suppose you want to include the age of each employee.

Step 1 − Insert a column to the right of the column Birthdate. Type Age in the Column Header.

Step 2 − In any of the Cells in that empty column, type the Formula, =DAYS ([@BirthDate], TODAY ()) and Press Enter.

The formula propagates automatically to the other cells in that column of the table.

Resize Table

You can resize a table to add or remove rows/columns.

Consider the following table Student_Marks that contains Total Marks for Batches 1 - 15.

Suppose you want to add three more batches 16 – 18 and a column containing pass percentage.

• Click the table.

• Drag the blue-color control at the lower-right, downwards to include three more rows in the table.

• Again drag the blue-color control at the lower-right, sideways to include one more column in the table.

Your table looks as follows. You can also check the range included in the table in the Name Manager dialog box −

Remove Duplicates

When you gather data from different sources, you probably can have duplicate values. You need to remove the duplicate values before going further with analysis.

Look at the following data where you have information about various products of various brands. Suppose, you want to remove duplicates from this data.

• Click the table.

• On the DESIGN tab, click Remove Duplicates in the Tools group on the Ribbon. The Remove Duplicates dialog box appears.

The column headers appear under columns in the Remove Duplicates dialog box.

• Check the column headers depending on which column you want to remove the duplicates and click OK.

You will get a message on how many rows with duplicate values are removed and how many unique values remain. The cleaned data will be displayed in the table.

You can also remove duplicates with Remove Duplicates in the Data Tools group under DATA tab on the Ribbon.

Convert to Range

You can convert a table to a Range.

• Click the table.

• Click Convert to Range in the Tools group, under the Design tab on the Ribbon.

You will get a message asking you if you want to convert the table to a Range. After you confirm with Yes, the table will be converted to Range.

Table Style Options

You have several options of Table Styles to choose. These options can be used if you need to highlight a Row / Column.

You can check / uncheck these boxes to see how your table looks. Finally, you can decide on what options suit your data.

It is advised that the Table Style Options be used only to project important information in your data rather than making it colorful, which is not needed in data analysis.

Table Styles

You have several table styles to choose from. These styles can be used depending on what color and pattern you want to display your data in the table.

Move your mouse on these styles to have a preview of your table with the styles. Finally, you can decide on what style suit your data.

It is advised that the Table Styles be used only to project important information in your data in a presentable way rather than making it colorful, which is not needed in data analysis.

Slicers for Tables

If you are using Excel 2013 or Excel 2016, you can use Slicers for filtering data in your table.

For details on how to use Slicers for Tables, refer the chapter on Filtering in this tutorial.

The data that you obtain from different sources many not be in a form ready for analysis. In this chapter, you will understand how to prepare your data that is in the form of text for analysis.

Initially, you need to clean the data. Data cleaning includes removing unwanted characters from text. Next, you need to structure the data in the form you require for further analysis. You can do the same by −

• Finding required text patterns with the text functions.
• Extracting data values from text.
• Formatting data with text functions.
• Executing data operations with the text functions.

Removing Unwanted Characters from Text

When you import data from another application, it can have nonprintable characters and/or excess spaces. The excess spaces can be −

• leading spaces, and/or
• extra spaces between words.

If you sort or analyze such data, you will get erroneous results.

Consider the following example −

This is the raw data that you have obtained on product information containing the Product ID, Product description and the price. The character “|” separates the field in each row.

When you import this data into Excel worksheet, it looks as follows −

As you observe, the entire data is in a single column. You need to structure this data to perform data analysis. However, initially you need to clean the data.

You need to remove any nonprintable characters and excess spaces that might be present in the data. You can use the CLEAN function and TRIM function for this purpose.

S.No.	Function & Description
1.	CLEAN Removes all nonprintable characters from text
2.	TRIM Removes spaces from text

• Select the Cells C3 – C11.
• Type =TRIM (CLEAN (B3)) and then press CTRL + Enter.

The formula is filled in the cells C3 – C11.

The result will be as shown below −

Finding required Text Patterns with the Text Functions

To structure your data, you might have to do certain Text Pattern matching based on which you can extract the Data Values. Some of the Text Functions that are useful for this purpose are −

S.No.	Function & Description
1.	EXACT Checks to see if two text values are identical
2.	FIND Finds one text value within another (case-sensitive)
3.	SEARCH Finds one text value within another (not case-sensitive)

Extracting Data Values from Text

You need to extract the required data from text in order to structure the same. In the above example, say, you need to place the data in three columns – ProductID, Product_Description and Price.

You can extract data in one of the following ways −

• Extracting Data Values with Convert Text to Columns Wizard
• Extracting Data Values with Text Functions
• Extracting Data Values with Flash Fill

Extracting Data Values with Convert Text to Columns Wizard

You can use the Convert Text to Columns Wizard to extract Data Values into Excel columns if your fields are −

• Delimited by a character, or
• Aligned in columns with spaces between each field.

In the above example, the fields are delimited by the character “|”. Hence, you can use the Convert Text to Columns wizard.

• Select the data.

• Copy and paste values in the same place. Otherwise, Convert Text to Columns takes the functions rather than the data itself as the input.

• Select the data.

• Click on Text to Columns in the Data Tools group under Data Tab on the Ribbon.

Step 1 − Convert Text to Columns Wizard - Step 1 of 3 appears.

• Select Delimited.
• Click Next.

Step 2 − Convert Text to Columns Wizard - Step 2 of 3 appears.

• Under Delimiters, select Other.

• In the box next to Other, type the character |

• Click Next.

Step 3 − Convert Text to Columns Wizard - Step 3 of 3 appears.

In this screen, you can select each column of your data in the wizard and set the format for that column.

• For Destination, select the cell D3.

• You can click Advanced, and set Decimal Separator and Thousands Separator in the Advanced Text Import Settings dialog box that appears.

• Click Finish.

Your data, which is converted to columns appears in the three Columns – D, E and F.

• Name the Column headers as ProductID, Product_Description and Price.

Extracting Data Values with Text Functions

Suppose the fields in your data neither are delimited by a character nor are aligned in columns with spaces between each field, you can use text functions to extract data values. Even in the case the fields are delimited, you can still use text functions to extract data.

Some of the text functions that are useful for this purpose are −

S.No.	Function & Description
1.	LEFT Returns the leftmost characters from a text value
2.	RIGHT Returns the rightmost characters from a text value
3.	MID Returns a specific number of characters from a text string starting at the position you specify
4.	LEN Returns the number of characters in a text string

You can also combine two or more of these text functions as per the data you have at hand, to extract the required data values. For example, using a combination of LEFT, RIGHT and VALUE functions or using a combination of FIND, LEFT, LEN and MID functions.

In the above example,

• All the characters left to the first | give the name ProductID.

• All the characters right to the second | give the name Price.

• All the characters that lie between the first | and second | give the name Product_Description.

• Each | has a space before and after.

Observing this information, you can extract the data values with the following steps −

• Find the Position of First | - First | Position

  • You can use FIND function

• Find the Position of Second | - Second | Position

  • You can use FIND function again

  • Beginning to (First | Position – 2) Characters of the Text give ProductID

    • You can use LEFT Function

    • (First | Position + 2) to (Second | Position - 2) Characters of the Text give Product_Description

      • You can use MID Function

      • (Second | Position + 2) to End Characters of the Text give Price

        • You can use RIGHT Function

        

        The result will be as shown below −

        

        You can observe that the values in the price column are text values. To perform calculations on these values, you have to format the corresponding cells. You can look at the section given below to understand formatting text.

        Extracting Data Values with Flash Fill

        Using Excel Flash Fill is another way to extract data values from text. However, this works only when Excel is able to find a pattern in the data.

        Step 1 − Create three columns for ProductID, Product_Description and Price next to the data.

        

        Step 2 − Copy and paste the values for C3, D3 and E3 from B3.

        

        Step 3 − Select cell C3 and click Flash Fill in the Data Tools group on the Data tab. All the values for ProductID get filled.

        

        Step 4 − Repeat the above given steps for Product_Description and Price. The data is filled.

        

        Formatting Data with Text Functions

        Excel has several built-in text functions that you can use for formatting data containing text. These include −

        Functions that format the Text as per your need −

        S.No.	Function & Description
        1.	LOWER Converts text to lowercase

        S.No.	Function & Description
        1.	UPPER Converts text to uppercase
        2.	PROPER Capitalizes the first letter in each word of a text value

Functions that convert and/or format the Numbers as Text -

S.No.	Descrição da função
1	DOLLAR Converte um número em texto, usando o formato de moeda $ (dólar)
2	FIXED Formata um número como texto com um número fixo de decimais
3 -	TEXT Formata um número e o converte em texto

Functions that convert the Text to Numbers -

S.No.	Descrição da função
1	VALUE Converte um argumento de texto em um número

Executing Data Operations with the Text Functions

Você pode ter que realizar certas operações de texto em seus dados. Por exemplo, se os IDs de login para os funcionários forem alterados para um novo formato em uma organização, com base na alteração de formato, substituições de texto podem ser feitas.

As seguintes funções de texto ajudam você a realizar operações de texto em seus dados contendo texto -

S.No.	Descrição da função
1	REPLACE Substitui caracteres dentro do texto
2	SUBSTITUTE Substitui o texto novo pelo texto antigo em uma string de texto
3 -	CONCATENATE Une vários itens de texto em um item de texto
4 -	CONCAT Combina o texto de vários intervalos e / ou strings, mas não fornece o delimitador ou os argumentos IgnoreEmpty.
5	TEXTJOIN Combina o texto de vários intervalos e / ou strings e inclui um delimitador que você especifica entre cada valor de texto que será combinado. Se o delimitador for uma string de texto vazia, esta função concatenará efetivamente os intervalos.
6	REPT Repete o texto um determinado número de vezes

Os dados que você obtém de diferentes fontes podem conter valores de data. Neste capítulo, você entenderá como preparar seus dados que contêm valores de dados para análise.

Você aprenderá sobre -

• Formatos de Data
  • Data em formato serial
  • Data em diferentes formatos de mês-dia-ano
• Conversão de datas no formato serial para o formato mês-dia-ano
• Conversão de datas no formato mês-dia-ano para o formato serial
• Obtendo a data de hoje
• Encontrar um dia útil após dias especificados
• Personalizando a definição de um fim de semana
• Número de dias úteis entre duas datas fornecidas
• Extraindo ano, mês, dia da data
• Extraindo dia da semana a partir da data
• Obtendo Data do Ano, Mês e Dia
• Calculando o número de anos, meses e dias entre duas datas

Formatos de Data

Suporta Excel Date valores de duas maneiras -

• Formato Serial
• Em diferentes formatos de ano-mês-dia

Você pode converter -

• UMA Date em formato serial para um Date no formato ano-mês-dia

• UMA Date no formato ano-mês-dia para um Date em formato serial

Data em formato serial

UMA Date em formato de série é um número inteiro positivo que representa o número de dias entre a data fornecida e 1º de janeiro de 1900. Ambos os Datee 1º de janeiro de 1900 estão incluídos na contagem. Por exemplo, 42354 é umDate que representa 16/12/2015.

Data em formatos de mês-dia-ano

Excel suporta diferentes Date Formatos baseados no Locale(Local) que você escolher. Portanto, você precisa primeiro determinar a compatibilidade do seuDateformatos e a análise de dados em mãos. Observe que certoDate formatos são prefixados com * (asterisco) -

• Date formatos que começam com * (asterisco) respondem às mudanças nas configurações regionais de data e hora especificadas para o sistema operacional

• Date formatos sem * (asterisco) não são afetados pelas configurações do sistema operacional

Para fins de compreensão, você pode assumir os Estados Unidos como o Local. Você encontra o seguinteDate formatos para escolher para o Date- 8 ^th junho de 2016 -

• * 8/06/2016 (afetado pelas configurações do sistema operacional)
• * Quarta-feira, 8 de junho de 2016 (afetado pelas configurações do sistema operacional)
• 6/8
• 6/8/16
• 06/08/16
• 8-Jun
• 8-Jun-16
• 08-Jun-16
• Jun-16
• June-16
• J
• J-16
• 6/8/2016
• 8-Jun-2016

Se você inserir apenas dois dígitos para representar um ano e se -

• Os dígitos são 30 ou mais, o Excel assume que os dígitos representam anos no século XX.

• Os dígitos são inferiores a 30, o Excel assume que os dígitos representam os anos no século XXI.

Por exemplo, 1/1/29 é tratado como 1º de janeiro de 2029 e 1/1/30 é tratado como 1º de janeiro de 1930.

Conversão de datas no formato serial para o formato mês-dia-ano

Para converter datas do formato serial para o formato Mês-Dia-Ano, siga as etapas abaixo -

• Clique no Number guia no Format Cells caixa de diálogo.

• Clique Date debaixo Category.

• Selecione Locale. O disponívelDate formatos serão exibidos como uma lista em Type.

• Clique em um Format debaixo Type para ver a visualização na caixa adjacente a Sample.

Após escolher o formato, clique OK.

Conversão de datas no formato mês-dia-ano para o formato serial

Você pode converter datas no formato Mês-Dia-Ano para o formato Serial de duas maneiras -

• Usando Format Cells caixa de diálogo

• Usando Excel DATEVALUE função

Usando a caixa de diálogo Formatar células

• Clique no Number guia no Format Cells caixa de diálogo.

• Clique General debaixo Category.

Usando a função DATEVALUE do Excel

Você pode usar o Excel DATEVALUE função para converter um Date para Serial Numberformato. Você precisa incluir oDateargumento em “”. Por exemplo,

= DATEVALUE ("6/8/2016") resulta em 42529

Obtendo a data de hoje

Se você precisar realizar cálculos com base na data de hoje, basta usar a função do Excel TODAY (). O resultado reflete a data em que é usado.

A imagem seguinte da HOJE utilização da função () foi tomada em 16 ^th Maio de 2016 -

Encontrar um dia útil após os dias especificados

Você pode ter que realizar certos cálculos com base em seus dias de trabalho.

Dias úteis excluem dias de fim de semana e quaisquer feriados. Isso significa que se você pode definir seu fim de semana e feriados, quaisquer cálculos que você fizer serão baseados em dias úteis. Por exemplo, você pode calcular as datas de vencimento da fatura, os tempos de entrega previstos, a data da próxima reunião, etc.

Você pode usar o Excel WORKDAY e WORKDAY.INTL funções para tais operações.

S.No.	Descrição da função
1	WORKDAY Retorna o número de série da data anterior ou posterior a um determinado número de dias úteis
2	WORKDAY.INTL Retorna o número de série da data anterior ou posterior a um determinado número de dias úteis usando parâmetros para indicar quais e quantos dias são dias de fim de semana

Por exemplo, você pode especificar o 15 ^º dia de trabalho a partir de hoje (imagem abaixo é tomada em 16 ^th maio 2016), utilizando as funções hoje e dia de trabalho.

Suponha que 25 ^th maio 2016 e 1 ^st junho 2016 são feriados. Então, seu cálculo será o seguinte -

Personalizando a definição de um fim de semana

Por padrão, o fim de semana é sábado e domingo, ou seja, dois dias. Você também pode definir opcionalmente seu fim de semana com oWORKDAY.INTLfunção. Você pode especificar seu próprio fim de semana por um número de fim de semana que corresponda aos dias de fim de semana, conforme indicado na tabela abaixo. Você não precisa se lembrar desses números, porque ao começar a digitar a função, você obtém uma lista de números e dias de fim de semana na lista suspensa.

Dias de fim de semana	Número do fim de semana
Sábado domingo	1 ou omitido
Domingo segunda-feira	2
Segunda-feira terça-feira	3
Terça quarta	4
Quarta-feira quinta-feira	5
Quinta-feira sexta-feira	6
Sexta Sabado	7
Somente domingo	11
Apenas segunda-feira	12
Só terça-feira	13
Apenas quarta-feira	14
Somente quinta	15
Apenas sexta-feira	16
Só sábado	17

Suponha que se o fim de semana for apenas sexta-feira, você precise usar o número 16 na função WORKDAY.INTL.

Número de dias úteis entre duas datas fornecidas

Pode haver um requisito para calcular o número de dias úteis entre duas datas, por exemplo, no caso de calcular o pagamento a um funcionário contratado que é pago por dia.

Você pode encontrar o número de dias úteis entre duas datas com as funções do Excel NETWORKDAYS e NETWORKDAYS.INTL. Assim como no caso de WORKDAYS e WORKDAYS.INTL, NETWORKDAYS e NETWORKDAYS.INTL permitem que você especifique feriados e com NETWORKDAYS.INTL você pode especificar adicionalmente o fim de semana.

S.No.	Descrição da função
1	NETWORKDAYS Retorna o número de dias úteis inteiros entre duas datas
2	NETWORKDAYS.INTL Retorna o número de dias úteis inteiros entre duas datas usando parâmetros para indicar quais e quantos dias são dias de fim de semana

Você pode calcular o número de dias úteis entre hoje e outra data com as funções TODAY e NETWORKDAYS. Na captura de tela a seguir apresentado, hoje é 16 ^th maio 2016 e data final é 16 ^th de junho de 2016. 25 ^th maio 2016 e 1 ^st junho 2016 são feriados.

Novamente, o fim de semana é considerado sábado e domingo. Você pode ter sua própria definição para fim de semana e calcular o número de dias úteis entre duas datas com a função NETWORKDAYS.INTL. Na captura de tela abaixo, apenas sexta-feira é definida como fim de semana.

Extraindo ano, mês, dia da data

Você pode extrair de cada data em uma lista de datas, o dia, mês e ano correspondentes usando as funções do excel DIA, MÊS e ANO.

Por exemplo, considere as seguintes datas -

De cada uma dessas datas, você pode extrair dia, mês e ano da seguinte forma -

Extraindo dia da semana a partir da data

Você pode extrair de cada data em uma lista de datas, o dia da semana correspondente com a função Excel WEEKDAY.

Considere o mesmo exemplo dado acima.

Obtendo Data do Ano, Mês e Dia

Seus dados podem ter as informações sobre Ano, Mês e Dia separadamente. Você precisa obter a data combinando esses três valores para realizar qualquer cálculo. Você pode usar a função DATE para obter os valores de data.

Considere os seguintes dados -

Use a função DATE para obter os valores DATE.

Calculando anos, meses e dias entre duas datas

Você pode ter que calcular o tempo decorrido a partir de uma determinada data. Você pode precisar dessas informações na forma de anos, meses e dias. Um exemplo simples seria calcular a idade atual de uma pessoa. É efetivamente a diferença entre a data de nascimento e hoje. Você pode usar as funções DATEDIF, TODAY e CONCATENATE do Excel para este propósito.

O resultado é o seguinte -

Os dados que você obtém de diferentes fontes podem conter valores de tempo. Neste capítulo, você entenderá como preparar seus dados que contêm valores de tempo para análise.

Você aprenderá sobre -

• Formatos de hora
  • Tempo em formato serial
  • Tempo no formato hora-minuto-segundo
• Conversão de horas em formato serial para formato hora-minuto-segundo
• Conversão de tempos do formato hora-minuto-segundo para o formato serial
• Obtendo a hora atual
• Obtendo o tempo de hora, minuto e segundo
• Extraindo hora, minuto e segundo do tempo
• Número de horas entre a hora de início e a hora de término

Formatos de hora

Suporta Excel Time Valores de duas maneiras -

• Formato Serial
• Em vários formatos de hora-minuto-segundo

Você pode converter -

• Time em formato serial para Time no formato hora-minuto-segundo

• Time no formato hora-minuto-segundo para Time em formato serial

Tempo em formato serial

Time no formato de série é um número positivo que representa o Timecomo uma fração de um dia de 24 horas, sendo o ponto de partida a meia-noite. Por exemplo, 0,29 representa 7h00 e 0,5 representa 12h00.

Você também pode combinar Date e Timena mesma célula. O número de série é o número de dias após 1o de janeiro de 1900 e a fração de tempo associada ao horário especificado. Por exemplo, se você digitar 17 de maio de 2016 6h, ele será convertido para 42507,25 quando você formatar a célula comoGeneral.

Tempo no formato hora-minuto-segundo

O Excel permite que você especifique a hora no formato hora-minuto-segundo com dois-pontos (:) após a hora e outros dois-pontos antes dos segundos. Exemplo: 8:50 AM, 8:50 PM ou apenas 8:50 usando o formato de 12 horas ou como 8:50, 20:50 no formato de 24 horas. O tempo 8:50:55 AM representa 8 horas, 50 minutos e 55 segundos.

Você também pode especificar a data e a hora juntos. Por exemplo, se você digitar 17 de maio de 2016 7:25 em uma célula, será exibido como 17/05/2016 7:25 e representa 17/05/2016 7h25:00.

Excel suporta diferentes Time formatos baseados no Locale(Local) que você escolher. Portanto, você precisa primeiro determinar a compatibilidade do seuTime formatos e análise de dados disponíveis.

Para fins de compreensão, você pode assumir os Estados Unidos como o Local. Você encontra o seguinteTime formatos para escolher Date e Time- 17 ^th Maio de 2016 04:00 -

• 16h00
• 16:00
• 16:00
• 16:00:00
• 17/05/16 16:00
• 17/05/16 16:00

Conversão de horas em formato serial para formato hora-minuto-segundo

Para converter o formato de hora serial para o formato hora-min-seg, siga as etapas abaixo -

• Clique no Number guia no Format Cells caixa de diálogo

• Clique Time debaixo Category.

• Selecione os Locale. acessívelTime formatos serão exibidos como uma lista em Type.

• Clique em um Format debaixo Type para ver a visualização na caixa adjacente a Sample.

Após escolher o formato, clique OK

Conversão de tempos do formato hora-minuto-segundo para o formato serial

Você pode converter o tempo em Hour-Minute-Second formato para formato serial de duas maneiras -

• Usando Format Cells caixa de diálogo

• Usando Excel TIMEVALUE função

Usando a caixa de diálogo Formatar células

• Clique no Number guia no Format Cells caixa de diálogo.

• Clique General debaixo Category.

Usando a função TIMEVALUE do Excel

Você pode usar o Excel TIMEVALUE função para converter Time para Serial Numberformato. Você precisa incluir oTimeargumento em “”. Por exemplo,

TIMEVALUE ("16:55:15") resultados em 0,70503472

Obtendo a hora atual

Se você precisar realizar cálculos com base na hora atual, basta usar a função do Excel AGORA (). O resultado reflete a data e a hora em que é usado.

A seguinte captura de tela da utilização da função Now () foi tomada em 17 ^º de maio de 2016, 12:22.

Obtendo o tempo de hora, minuto e segundo

Seus dados podem ter as informações sobre horas, minutos e segundos separadamente. Suponha que você precise obter o tempo combinando esses 3 valores para realizar qualquer cálculo. Você pode usar a função de hora do Excel para obter os valores de hora.

Extraindo hora, minuto e segundo do tempo

Você pode extrair hora, minuto e segundo de um determinado momento usando as funções do Excel HORA, MINUTO e SEGUNDO.

Número de horas entre a hora de início e a hora de término

Quando você realiza cálculos em valores de tempo, o resultado exibido depende do formato usado na célula. Por exemplo, você pode calcular o número de horas entre 9h30 e 18h da seguinte maneira -

• C4 é formatado como hora
• C5 e C6 são formatados como Número.

Você obtém a diferença de tempo em dias. Para converter em horas, você precisa multiplicar por 24.

No Microsoft Excel, você pode usar Conditional Formattingpara visualização de dados. Você deve especificar a formatação de um intervalo de células com base no conteúdo do intervalo de células. As células que atendem às condições especificadas serão formatadas conforme você definiu.

Exemplo

Em um intervalo que contém os números de vendas do último trimestre para um conjunto de vendedores, você pode destacar as células que representam quem atingiu a meta definida, digamos, $ 2.500.

Você pode definir a condição como vendas totais da pessoa> = $2500e especifique um código de cor verde. O Excel verifica cada célula no intervalo para determinar se a condição que você especificou, ou seja, vendas totais da pessoa> = $ 2500, foi satisfeita.

O Excel aplica o formato que você escolheu, ou seja, a cor verde a todas as células que atendem à condição. Se o conteúdo de uma célula não satisfizer a condição, a formatação da célula permanecerá inalterada. O resultado é o esperado, apenas para os vendedores que atingiram a meta, as células são destacadas em verde - uma rápida visualização dos resultados da análise.

Você pode especificar qualquer número de condições para a formatação, especificando Rules. Você pode escolher as regras que correspondem às suas condições de

• Destacar regras de células
• Regras superior / inferior

Você também pode definir suas próprias regras. Voce pode -

• Adicionar uma regra
• Limpar uma regra existente
• Gerenciar as regras definidas

Além disso, você tem várias opções de formatação no Excel para escolher aquelas que são adequadas para a sua Visualização de Dados -

• Barras de Dados
• Escalas de Cores
• Conjuntos de ícones

A formatação condicional foi promovida nas versões Excel 2007, Excel 2010, Excel 2013. Os exemplos que você encontra neste capítulo são do Excel 2013.

Nas seções a seguir, você entenderá as regras de formatação condicional, opções de formatação e como trabalhar com regras.

Regras de destaque das células

Você pode usar Highlight Cells regra para atribuir um formato a células cujo conteúdo atenda a qualquer um dos seguintes critérios -

• Números dentro de um determinado intervalo numérico -
  • Maior que
  • Menor que
  • Between
  • Igual a
• Texto que contém uma determinada string de texto.
• Data que ocorre dentro de um determinado intervalo de datas em relação à data atual -
  • Yesterday
  • Today
  • Tomorrow
  • Nos últimos 7 dias
  • Semana Anterior
  • Esta semana
  • Semana que vem
  • Mês passado
  • Este mês
  • Próximo mês
• Valores duplicados ou exclusivos.

Siga as etapas para formatar células condicionalmente -

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Highlight Cells Rules no menu suspenso.

• Clique Greater Thane especifique> 750. Escolha a cor verde.

• Clique Less Than e especifique <500. Escolha a cor vermelha.

• Clique Between e especifique 500 e 750. Escolha a cor amarela.

Os dados serão destacados com base nas condições fornecidas e na formatação correspondente.

Regras superior / inferior

Você pode usar Top / Bottom Rules para atribuir um formato às células cujo conteúdo atenda a qualquer um dos seguintes critérios -

• Top 10 items - Células classificadas no N superior, onde 1 <= N <= 1000.

• Top 10% - Células classificadas nos n% superiores, onde 1 <= n <= 100.

• Bottom 10 items - Células classificadas no N inferior, onde 1 <= N <= 1000.

• Bottom 10% - Células classificadas nos n% inferiores, onde 1 <= n <= 100.

• Above average - Células que estão acima da média para o intervalo selecionado.

• Below average - Células que estão abaixo da média para o intervalo selecionado.

Siga as etapas fornecidas abaixo para atribuir as regras Superior / Inferior.

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Top/Bottom Rulesno menu suspenso. As opções de regras superior / inferior são exibidas.

• Clique Top Ten Items e especifique 5. Escolha a cor verde.

• Clique Bottom Ten Items e especifique 5. Escolha a cor vermelha.

Os dados serão destacados com base nas condições fornecidas e na formatação correspondente.

• Repita as três primeiras etapas fornecidas acima.

• Clique Top Ten% e especifique 5. Escolha a cor verde.

• Clique Bottom Ten% e especifique 5. Escolha a cor vermelha.

Os dados serão destacados com base nas condições fornecidas e na formatação correspondente.

• Repita as três primeiras etapas fornecidas acima.

• Clique Above Average. Escolha a cor verde.

• Clique Below Average. Escolha a cor vermelha.

Os dados serão destacados com base nas condições fornecidas e na formatação correspondente.

Barras de Dados

Você pode usar colorido Data Barspara ver o valor em uma célula em relação aos valores nas outras células. O comprimento da barra de dados representa o valor na célula. Uma barra mais longa representa um valor mais alto e uma barra mais curta representa um valor mais baixo. Você tem seis cores sólidas para escolher para as barras de dados - azul, verde, vermelho, amarelo, azul claro e roxo.

As barras de dados são úteis para visualizar os valores superior, inferior e intermediário quando você tem grandes quantidades de dados. Exemplo - temperaturas diurnas nas regiões de um determinado mês. Você pode usar barras de cores de preenchimento de gradiente para visualizar o valor em uma célula em relação aos valores em outras células. Voce tem seisGradient Colors para escolher entre as barras de dados - azul, verde, vermelho, amarelo, azul claro e roxo.

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Data Barsno menu suspenso. oGradient Fill opções e Fill opções aparecem.

Clique na barra de dados azul no Gradient Fill opções.

• Repita as três primeiras etapas.

• Clique na barra de dados azul no Solid Fill opções.

Você também pode formatar barras de dados de forma que a barra de dados comece no meio da célula e se estenda para a esquerda para valores negativos e se estique para a direita para valores positivos.

Escalas de Cores

Você pode usar Color Scalespara ver o valor em uma célula em relação aos valores nas outras células em um determinado intervalo. Como no caso deHighlight Cells Rules, uma Color Scaleusa o sombreamento da célula para exibir as diferenças nos valores das células. Um gradiente de cor será aplicado a um intervalo de células. A cor indica onde cada valor de célula se enquadra nesse intervalo.

Você pode escolher entre -

• Três - Escala de Cores -
  • Escala de cor verde - amarelo - vermelho
  • Escala de cores vermelho - amarelo - verde
  • Escala de cor verde - branco - vermelho
  • Escala de cores vermelho - branco - verde
  • Escala de cor azul - branco - vermelho
  • Escala de cores vermelho - branco - azul
• Escala de duas cores -
  • Branco - Escala de Cor Vermelha
  • Vermelho - Escala de Cor Branca
  • Escala de cor verde - branco
  • Branco - Escala de Cor Verde
  • Escala de cor verde - amarela
  • Escala de cor amarela - verde

Siga as etapas abaixo -

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Color Scalesno menu suspenso. oColor Scale opções aparecem.

• Clique na escala de cores verde - amarelo - vermelho.

Os dados serão destacados com base na escala de cores Verde - Amarelo - Vermelho no intervalo selecionado.

• Repita as três primeiras etapas.
• Clique na escala de cores Verde - Branco.

Os dados serão destacados com base na escala de cores Verde - Branco no intervalo selecionado.

Conjuntos de ícones

Você pode usar os conjuntos de ícones para visualizar diferenças numéricas. Os seguintes conjuntos de ícones estão disponíveis -

Conforme você observa, um conjunto de ícones consiste de três a cinco símbolos. Você pode definir critérios para associar um ícone a cada valor em um intervalo de células. Por exemplo, uma seta vermelha para baixo para números pequenos, uma seta verde para cima para números grandes e uma seta horizontal amarela para valores intermediários.

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Icon Setsno menu suspenso. oIcon Sets opções aparecem.

• Clique nas três setas coloridas.

Setas coloridas aparecem ao lado dos dados com base nos valores no intervalo selecionado.

• Repita as três primeiras etapas. oIcon Sets opções aparecem.

• Selecione 5 avaliações. Os ícones de classificação aparecem ao lado dos dados com base nos valores do intervalo selecionado.

Nova regra

Você pode usar New Rule para criar sua própria fórmula como condição para formatar uma célula conforme você definir.

Existem duas maneiras de usar a Nova Regra -

• Com New Rule opção do menu suspenso

• Com New Rule botão em Manage Rules caixa de diálogo

Com a opção de Nova Regra no menu suspenso

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique New Rule no menu suspenso.

o New Formatting Rule a caixa de diálogo aparece.

• Na caixa Selecione um tipo de regra, selecione Usar uma fórmula para determinar quais células formatar. Edit the Rule Description a caixa aparece.

• Nos valores de formato onde esta fórmula é verdadeira: digite a fórmula.

• Clique no botão formatar e clique em OK.

As células que contêm valores com a fórmula TRUE são formatadas conforme definido.

Com o botão de nova regra na caixa de diálogo Gerenciar regras

• Selecione o intervalo a ser formatado condicionalmente.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Manage Rules no menu suspenso.

o Conditional Formatting Rules Manager a caixa de diálogo aparece.

Clique no New Rule botão.

o New Formatting Rule a caixa de diálogo aparece.

Repita as etapas fornecidas acima para definir sua fórmula e formato.

o Conditional Formatting Rules Manager a caixa de diálogo aparece com New Ruleem destaque. Clique noApply botão.

As células que contêm valores com a fórmula TRUE são formatadas conforme definido.

Regras claras

Você pode limpar regras para excluir todos os formatos condicionais que criou para

• Células selecionadas
• Folha de trabalho atual
• Tabela Selecionada
• Tabela Dinâmica Selecionada

Siga as etapas fornecidas -

• Selecione o intervalo / Clique em uma planilha / Clique na tabela> Tabela dinâmica onde as regras de formatação condicional precisam ser removidas.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Clear Rulesno menu suspenso. As opções de limpar regras são exibidas.

Selecione a opção apropriada. A formatação condicional é limpa em Intervalo / Planilha / Tabela / Tabela Dinâmica.

Gerenciar regras

Você pode Manage Rulesde Conditional Formatting Rules Managerjanela. Você pode ver as regras de formatação para a seleção atual, para toda a planilha atual, para as outras planilhas da pasta de trabalho ou para as tabelas ou Tabelas Dinâmicas da pasta de trabalho.

• Clique Conditional Formatting no Styles grupo sob Home aba.

• Clique Manage Rules no menu suspenso.

o Conditional Formatting Rules Manager a caixa de diálogo aparece.

Clique na seta na caixa de listagem ao lado de Show formatting rules for Aparecem a seleção atual, esta planilha e outras planilhas, tabelas, tabela dinâmica, se houver regras de formatação condicional.

Selecione This Worksheetna lista suspensa. As regras de formatação na planilha atual aparecem na ordem em que serão aplicadas. Você pode alterar essa ordem usando as setas para cima e para baixo.

Você pode adicionar uma nova regra, editar uma regra e excluir uma regra.

• Você já viu New Rulena seção anterior. Você pode excluir uma regra selecionando a Regra e clicando emDelete Rule. A regra destacada é excluída.

• Para editar uma regra, selecione a REGRA e clique em Edit Rule. Edit Formatting Rule a caixa de diálogo aparece.

• Você pode

  • Selecione um tipo de regra

  • Edite a descrição da regra

  • Editar formatação

• Depois de concluir as alterações, clique em OK.

• As mudanças para a Regra serão refletidas no Conditional Formatting Rules Managercaixa de diálogo. CliqueApply.

• Os dados serão destacados com base na modificação Conditional Formatting Rules.

A classificação de dados é parte integrante da Análise de Dados. Você pode organizar uma lista de nomes em ordem alfabética, compilar uma lista de números de vendas do maior para o menor ou ordenar as linhas por cores ou ícones. A classificação de dados ajuda a visualizar e compreender melhor seus dados rapidamente, organizar e encontrar os dados que deseja e, por fim, tomar decisões mais eficazes.

Você pode classificar por colunas ou linhas. A maioria das classificações que você usa serão classificações de coluna.

Você pode classificar os dados em uma ou mais colunas por

• texto (A a Z ou Z a A)
• números (do menor para o maior ou do maior para o menor)
• datas e horas (do mais antigo para o mais recente e do mais recente para o mais antigo)
• uma lista personalizada (por exemplo, grande, média e pequena)
• formato, incluindo cor da célula, cor da fonte ou conjunto de ícones

Os critérios de classificação para uma tabela são salvos com a pasta de trabalho de forma que você possa reaplicar a classificação a essa tabela sempre que abrir a pasta de trabalho. Os critérios de classificação não são salvos para um intervalo de células. Para classificações em várias colunas ou que levam muito tempo para serem criadas, você pode converter o intervalo em uma tabela. Em seguida, você pode reaplicar a classificação ao abrir uma pasta de trabalho.

Em todos os exemplos nas seções a seguir, você encontrará apenas tabelas, pois é mais significativo classificar uma tabela.

Classificar por Texto

Você pode classificar uma tabela usando uma coluna que contém texto.

A tabela a seguir contém informações sobre funcionários em uma organização (você pode ver apenas as primeiras linhas dos dados).

• Para classificar a tabela pelo título da coluna que contém o texto, clique no cabeçalho da coluna - Title.

• Clique no Data aba.

• No Sort & Filter grupo, clique Sort A to Z

A tabela será classificada pela coluna - Título na ordem alfanumérica crescente.

Note - Você pode classificar em ordem alfanumérica decrescente, clicando em Sort Z to A. Você também pode classificar com a opção que diferencia maiúsculas de minúsculas. Passar pelaSort by a Custom List seção fornecida abaixo.

Classificar por Números

Para classificar a tabela pela coluna ManagerID que contém números, siga as etapas abaixo -

• Clique no cabeçalho da coluna - ManagerID.

• Clique no Data aba.

• No Sort & Filter grupo, clique Sort A to Z

A coluna, ManagerID, será classificada em ordem numérica crescente. Você pode classificar em ordem numérica decrescente, clicando em Classificar de Z a A.

Classificar por datas ou horários

Para classificar a tabela pela coluna HireDate que contém datas, siga as etapas abaixo -

• Clique no cabeçalho da coluna - HireDate.

• Clique Data aba.

• No Sort & Filter grupo, clique Sort A to Z como mostrado na captura de tela fornecida abaixo -

A coluna - HireDate será classificada com as datas classificadas da mais antiga para a mais recente. Você pode classificar as datas da mais recente para a mais antiga, clicando emSort Z to A.

Classificar por cor de célula

Para classificar a tabela pelas marcas de total da coluna que contém células com cores (formatado condicionalmente) -

• Clique no cabeçalho da coluna - Total de marcas.

• Clique Data aba.

• No Sort & Filter grupo, clique Sort. A caixa de diálogo Classificar é exibida.

• Escolher Sort By como marcas totais, Sort on Como Cell Color e especifique a cor verde em Order. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort on Como Cell Color e especifique a cor amarela em Order. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort on Como Cell Color e especifique a cor vermelha em Order.

A coluna - Total de marcas será classificada pela cor da célula conforme especificado no pedido.

Classificar por cor da fonte

Para classificar a coluna Total de marcas na tabela, que contém células com cores de fonte (formatado condicionalmente) -

• Clique no cabeçalho da coluna - Total de Marcas.

• Clique Data aba.

• No Sort & Filter grupo, clique Sort. A caixa de diálogo Classificar é exibida.

• Escolher Sort By como marcas totais, Sort On Como Font Colore especifique a cor verde em Ordem. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort On Como Font Color e especifique a cor amarela em Order. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort On Como Font Color e especifique a cor vermelha em Order.

A coluna - Total de marcas é classificada pela cor da fonte conforme especificado no pedido.

Classificar por ícone de célula

Para classificar a tabela pela coluna Total de marcas que contém células com ícones de células (formatados condicionalmente), siga as etapas abaixo -

• Clique no cabeçalho da coluna - Total de marcas.

• Clique Data aba.

• No Sort & Filter grupo, clique Sort. A caixa de diálogo Classificar é exibida.

• Escolher Sort By como marcas totais, Sort On Como Cell Icon e especificar em

  
  Order. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort On Como Cell Icone especifique

  
  no pedido. Clique em Adicionar nível.

• Escolher Sort By como marcas totais, Sort On Como Cell Icone especifique

  
  no pedido.

A coluna - Total de marcas será classificada por ícone de célula conforme especificado no pedido.

Classificar por uma lista personalizada

Você pode criar uma lista personalizada e classificar a tabela pela lista personalizada.

Na tabela abaixo, você encontra uma coluna de indicador com o título - Posição. Possui os valores alto, médio e baixo com base na posição das notas totais em relação a toda a faixa.

Agora, suponha que você queira classificar a coluna - Posição, com todos os valores altos no topo, todos os valores baixos na parte inferior e todos os valores médios entre eles. Isso significa que a ordem que você deseja é baixa, média e alta. ComSort A to Z, você obtém a ordem alta, baixa e média. Por outro lado, comSort Z to A, você obtém a ordem média, baixa e alta.

Você pode resolver isso criando uma lista personalizada.

• Defina a ordem da lista personalizada como alta, média e baixa em um intervalo de células, conforme mostrado abaixo.

• Selecione esse intervalo.

• Clique no File aba.

• Clique Options. NoExcel Options caixa de diálogo, clique Advanced.

• Vá até o General.

• Clique Edit Custom Lists.

o Edit Custom Listsa caixa de diálogo aparece. O intervalo selecionado na planilha aparece noImport list from cells Box. CliqueImport.

Sua lista personalizada é adicionada ao Custom Lists. Clique OK.

A próxima etapa é classificar a tabela com esta Lista Personalizada.

• Clique na coluna - posição. Clique emSort. NoSort caixa de diálogo, certifique-se Sort By é posição, Sort On é Valores.

• Clique em Order. SelecioneCustom List. A caixa de diálogo Listas personalizadas é exibida.

• Clique no High, Medium, LowLista personalizada. Clique em OK.

No Sort caixa de diálogo, no Order Caixa, High, Medium, Lowparece. Clique em OK.

A tabela será classificada na ordem definida - alta, média, baixa.

Você pode criar listas personalizadas com base nos seguintes valores -

• Text
• Number
• Date
• Time

Você não pode criar listas personalizadas com base no formato, ou seja, pela cor da célula / fonte ou ícone da célula.

Classificar por linhas

Você também pode classificar uma tabela por linhas. Siga as etapas abaixo -

• Clique na linha em que deseja classificar os dados.

• Clique Sort.

• No Sort caixa de diálogo, clique Options. oSort Options a caixa de diálogo é aberta.

• Debaixo Orientation, clique Sort from left to right. Clique OK.

• Clique Sort bylinha. Selecione a linha.

• Escolha valores para classificar On e do maior para o menor para Order.

Os dados serão classificados pela linha selecionada em ordem decrescente.

Classifique por mais de uma coluna ou linha

Você pode classificar uma tabela por mais de uma coluna ou linha.

• Clique na tabela.

• Clique Sort.

• No Sort caixa de diálogo, especifique a coluna pela qual você deseja classificar primeiro.

Na captura de tela abaixo, Sort By Título, Sort On Valores, Order A - Z são escolhidos.

• Clique Add Level no Sortcaixa de diálogo. oThen By a caixa de diálogo aparece.

• Especifique a coluna pela qual deseja classificar a seguir.

• Na captura de tela abaixo, Then By Data de contratação, Sort On Valores, Order Os mais antigos para os mais recentes são escolhidos.

• Clique OK.

Os dados serão classificados para Título em ordem alfanumérica crescente e, em seguida, por HireDate. Você verá os dados do funcionário classificados por cargo e em cada categoria de cargo, na ordem de antiguidade.

A filtragem permite que você extraia dados que atendam aos critérios definidos de um determinado intervalo ou tabela. Esta é uma maneira rápida de exibir apenas as informações de que você precisa.

Você pode filtrar dados em um intervalo, tabela ou tabela dinâmica.

Você pode filtrar dados por -

• Valores selecionados
• Filtros de texto se a coluna que você selecionou contiver texto
• Filtros de data se a coluna que você selecionou contiver datas
• Filtros de número se a coluna selecionada contiver números
• Filtros de número se a coluna selecionada contiver números
• Cor da fonte se a coluna que você selecionou contiver fonte com cor
• Ícone de célula se a coluna que você selecionou contiver ícones de célula
• Filtro avançado
• Usando divisores

Em uma tabela, os cabeçalhos das colunas são marcados automaticamente para filtros, conhecidos como AutoFilters. AutoFilteré representado pela seta

ao lado do cabeçalho da coluna. CadaAutoFiltertem opções de filtro com base no tipo de dados que você tem nessa coluna. Por exemplo, se a coluna contém números, quando você clica na seta ao lado do cabeçalho da coluna,Number Filter Options aparecer.

Quando você clica em uma opção de filtro ou quando você clica em Custom Filter que aparece no final das opções de filtro, Custom AutoFilter aparece a caixa de diálogo, onde você pode personalizar suas opções de filtragem.

No caso de um intervalo, você pode fornecer os cabeçalhos das colunas na primeira linha do intervalo e clicar no filtro no Editing grupo em Homeaba. Isso fará com que oAutoFilterpara o intervalo. Você pode remover os filtros que possui em seus dados. Você também pode reaplicar os filtros quando ocorrerem alterações de dados.

Filtrar por valores selecionados

Você pode escolher quais dados devem ser exibidos clicando na seta ao lado de um cabeçalho de coluna e selecionando os Valores na coluna. Apenas as linhas contendo os valores selecionados na coluna escolhida serão exibidas.

Considere os seguintes dados -

Se você deseja exibir os dados apenas para Posição = Alta, clique na seta ao lado de Posição. Uma caixa suspensa aparece com todos os valores na coluna de posição. Por padrão, todos os valores serão selecionados.

• Clique em Selecionar tudo. Todas as caixas estão desmarcadas.
• Selecione Alto conforme mostrado na captura de tela a seguir.

Clique OK. Apenas as linhas, que têm o valor Alto como posição, serão exibidas.

Filtrar por Texto

Considere os seguintes dados -

Você pode filtrar esses dados de forma que apenas as linhas em que o título é “Gerente” sejam exibidas.

Clique na seta ao lado do título da coluna Título. Na lista suspensa, clique emText Filters. As opções de filtro de texto são exibidas.

Selecione Containsdentre as opções disponíveis. oCustom AutoFiltera caixa de diálogo é aberta. Digite Manager na caixa ao lado de Contém.

Clique OK. Apenas as linhas onde o Título contém o gerente serão exibidas.

Filtrar por Data

Você pode filtrar esses dados ainda mais, de modo que apenas as linhas em que o título é “Gerente” e a data de contratação anterior a 2011 possam ser exibidas. Isso significa que você exibirá as informações do funcionário para todos os gerentes que estiveram na organização antes de 2011.

Clique na seta ao lado do cabeçalho da coluna HireDate. Na lista suspensa, clique emDate Filters. As opções de filtro de data são exibidas. SelecioneBefore na lista suspensa.

Custom AutoFiltera caixa de diálogo é aberta. Digite 01/01/2011 na caixa ao lado deis before. Você também pode selecionar a data no seletor de data ao lado da caixa.

Clique OK. Apenas as linhas em que Título contém Gerente e Data de Contratação anterior a 01/01/2011 serão exibidas.

Filtrar por Números

Considere os seguintes dados -

Você pode filtrar esses dados de forma que apenas as linhas onde Total de marcas> 850 possam ser exibidas.

Clique na seta ao lado do cabeçalho da coluna Total de Marcas. Na lista suspensa, clique emNumber Filters. As opções de Filtro de número são exibidas.

Clique Greater Than. Custom AutoFiltera caixa de diálogo é aberta. Digite 850 na caixa ao lado deGreater Than.

Clique OK. Apenas as linhas em que as marcas totais são maiores que 850 serão exibidas.

Filtrar por cor de célula

Se os dados tiverem cores de células diferentes ou estiverem formatados condicionalmente, você pode filtrar pelas cores que são exibidas em sua tabela.

Considere os seguintes dados. A coluna Total de Marcas possui formatação condicional com diferentes cores de células.

Clique na seta

no cabeçalho Total Marks. Na lista suspensa, clique emFilter by Color. oFilter by Cell Color opções aparecem.

Selecione a cor verde e clique em OK. Apenas as linhas em que a coluna de marcas totais tem células verdes serão exibidas.

Filtrar por cor da fonte

Se os dados tiverem cores de fonte diferentes ou estiverem formatados condicionalmente, você pode filtrar pelas cores que são exibidas em sua tabela.

Considere os seguintes dados. A coluna - Total de marcas tem formatação condicional com a cor da fonte aplicada.

Clique na seta

no cabeçalho Total Marks. Na lista suspensa,click Filter by Color. Filter by Font Color opções aparecem.

Selecione a cor verde e clique em OK. Apenas as linhas em que a coluna Total de marcas tem fonte de cor verde serão exibidas.

Filtrar por ícone de célula

Se os dados tiverem ícones diferentes ou um formato condicional, você pode filtrar pelos ícones que são mostrados em sua tabela.

Considere os seguintes dados. A coluna Total de marcas tem formatação condicional com ícones aplicados.

Clique na seta

no cabeçalho Total Marks. Na lista suspensa, selecioneFilter by Color. oFilter by Cell Icon opções aparecem.

Selecione o ícone

e clique em OK.

Apenas as linhas em que a coluna Total de marcas tem o

ícone serão exibidas.

Filtro limpo

Remover filtros é denominado como Clear Filter no Excel.

Você pode remover

• Um filtro de uma coluna específica, ou
• Todos os filtros da planilha de uma vez.

Para remover um filtro de uma coluna específica, clique na seta no cabeçalho da tabela dessa coluna. No menu suspenso, clique emClear Filter From “<specific Column Name>”.

O filtro da coluna é removido. Para remover a filtragem de toda a planilha, selecione

Clear no

• Editing grupo no Home guia, ou

• Sort & Filter grupo no Data aba.

Todos os filtros da planilha são removidos de uma vez. CliqueUndo Show All

se você removeu os Filtros por engano.

Reaplicar filtro

Quando ocorrerem alterações em seus dados, clique em Reapply dentro Sort & Filter grupo no Dataaba. O filtro definido será aplicado novamente nos dados modificados.

Filtragem Avançada

Você pode usar a Filtragem Avançada se quiser filtrar os dados de mais de uma coluna.

Você precisa definir seus critérios de filtragem como um intervalo. Suponha que você deseja exibir as informações dos funcionários que são especialistas ou cujo EmployeeID é 2, defina os critérios da seguinte forma -

• Em seguida clique Advanced no Sort & Filter grupo no Dataaba. oAdvanced Filter a caixa de diálogo aparece.

• Especifique o List Range e a Criteria Range.

• Você também pode filter the list, no local ou copie para outro local.

• Na filtragem fornecida abaixo, filter the data in place é escolhido.

As informações do funcionário onde ManagerID = 2 OR Title = “* Specialist” é exibido.

Suponha que você queira exibir informações sobre especialistas e vice-presidentes. Você pode definir os critérios e filtrar da seguinte forma -

Os critérios que você aplicou são Título = “* Especialista” OU Título = “Vice-presidente”. As informações dos funcionários de especialistas e vice-presidentes serão exibidas.

Vocês can copy the filtered data to another location. Você também pode selecionar apenas algumas colunas para incluir na operação de cópia.

• Copie EmployeedID, Title e SalariedFlag para as células Q2, R2, S2. Esta será a primeira linha de seus dados filtrados.

• Clique em Avançado e na caixa de diálogo Filtro avançado, clique em Copiar para outro local. Na caixa Copiar para, especifique a referência aos cabeçalhos que você copiou em outro local, ou seja, Q2: S2.

Clique em OK após especificar o List Range e Criteria Range. As colunas selecionadas nos dados filtrados serão copiadas para o local que você especificou.

Filtrar usando Slicers

Slicers para filtrar dados em tabelas dinâmicas foram introduzidos no Excel 2010. No Excel 2013, você pode usar Slicers para filtrar dados em tabelas também.

Considere os dados da tabela a seguir.

• Clique na tabela.
• Clique Table Toolsque aparecem na faixa de opções.
• o Design A fita aparece.
• Clique Insert Slicer.

• Insert Slicers a caixa de diálogo aparece conforme mostrado na captura de tela fornecida abaixo.

• No Insert Slicers caixa de diálogo, você encontrará todos os cabeçalhos de coluna, incluindo as colunas que estão ocultas.

• Marque as caixas Título e Data de contratação. Click OK.

UMA Slicer aparece para cada um dos cabeçalhos de tabela que você verificou no Insert Slicerscaixa de diálogo. Em cadaSlicer, todos os valores dessa coluna serão destacados.

No título Slicer, clique no primeiro valor. Apenas esse valor será destacado e o restante dos valores será desmarcado. Além disso, você encontrará os valores em HireDateSlicer que correspondem ao valor do Título Slicer também são destacados.

Na tabela, apenas os valores selecionados são exibidos.

Você pode selecionar / desmarcar os valores nas Segmentações de Dados e descobrir que os dados são atualizados automaticamente na tabela. Para escolher mais de um valor, mantenha pressionada a tecla Ctrl e selecione os valores que deseja exibir.

Selecione os valores de Título que pertencem ao departamento de Contas e os valores de HireDate no ano de 2015 dos dois Segmentadores de Dados.

Você pode limpar as seleções em qualquer Slicer clicando em Limpar Filtro

no canto direito do cabeçalho do Slicer.

Se você tiver uma lista de dados que deseja agrupar e resumir, pode usar o Excel Subtotal e Outlinepara exibir linhas ou colunas de resumo. Você pode usarPivotTable também para este propósito, mas usando Subtotal e Outlineé a maneira mais rápida de analisar uma série de dados. Observe queSubtotal e Outline pode ser usado apenas em um intervalo e não em uma mesa.

Você pode criar um Outlinede até oito níveis, um para cada grupo. Os níveis externos são representados por números inferiores e os níveis internos por números superiores. Cada nível interno exibe dados detalhados para o nível externo anterior.

Para entender como usar Subtotal e Outline, considere o exemplo a seguir, em que os dados de vendas de vários itens são fornecidos pelo vendedor e pela localização. No total, existem 1891 linhas de dados.

Subtotais

Você pode obter a soma do local de vendas usando Subtotal.

Primeiro, classifique a localização dos dados de maneira inteligente.

• Clique em qualquer lugar no intervalo de dados.

• Clique no DATA aba.

• Clique Sort.

Os dados são selecionados. oSort a caixa de diálogo aparece.

No Sort caixa de diálogo,

• Selecione a localização para Sort by

• Selecione valores para Sort On

• Selecione A a Z para Order

Clique OK. Os dados são classificados em termos de localização.

• Clique em qualquer lugar no intervalo de dados.

• Clique DATA aba.

• Clique Subtotal no Outlinegrupo. Os dados são selecionados e oSubtotal a caixa de diálogo aparece.

No Subtotal dialog caixa,

• Selecione a localização em At each change in:

• Selecione Soma em Use function:

• Selecione Unidade e Quantidade em Add subtotal to:

• Selecione Replace current subtotals

• Selecione Summary below data

Clique OK. Os dados são agrupados em três níveis e os subtotais são calculados com base na localização.

Note - Os dados exibidos são de Nível 3 - ou seja, dados inteiros.

Clique no Outline Level 2. O Totals será exibido em termos de localização para unidades e quantidade.

Clique em Nível de estrutura de tópicos 1. O Grand Totals será exibido para unidades e quantidade.

Você pode aumentar ou diminuir o zoom dos dados clicando no Outline Levels ou clicando em + Símbolos à esquerda dos dados.

Subtotais aninhados

Você pode obter a soma das vendas de cada vendedor, por localização usando Nested Subtotals.

Sort a localização dos dados e depois o vendedor.

• Clique em qualquer lugar no intervalo de dados.

• Clique no DATA aba.

• Clique Sort. Os dados são selecionados e oSort a caixa de diálogo aparece.

No Sort caixa de diálogo,

• Selecione a localização para Sort by

• Selecione valores para Sort On

• Selecione A a Z para Order

• Clique em Add Level

Then by linha aparece

• Selecione o nome para Then by

• Selecione valores para Sort On

• Selecione A a Z para Order

Clique OK. Os dados são classificados por localização e depois por nome.

• Clique em qualquer lugar no intervalo de dados

• Clique em DATA aba

• Clique em Subtotal no Outline grupo

Os dados são selecionados. Subtotal a caixa de diálogo aparece.

No Subtotal caixa de diálogo,

• Selecione a localização em At each change in:

• Selecione Soma em Use function:

• Selecione Unidade e Quantidade em Add subtotal to:

• Selecione Replace current subtotals

• Selecione Summary below data

Clique OK. Os dados são agrupados com três níveis e os subtotais são calculados de acordo com a localização, conforme descrito anteriormente.

• Clique Subtotal.

No Subtotal caixa de diálogo,

• Selecione o nome em At each change in:

• Selecione Soma em Use function:

• Selecione Unidade e Quantidade em Add subtotal to:

• Unselect Replace current subtotals

• Selecione Summary below data

Clique OK. Os dados são agrupados em quatro níveis e os subtotais são calculados por localização e nome.

Clique Outline Level 3. oTotals será exibido o nome e a localização para as Unidades e Quantidade.

Clique em Outline Level 2. oTotals será exibido em localização para unidades e quantidade.

Clique Outline Level 1. oGrand Totals será exibido para Unidades e Quantidade.

Você pode aumentar ou diminuir o zoom dos dados clicando no Outline Levels ou clicando no símbolo + à esquerda dos dados.

No Microsoft Excel 2013, o Quick Analysis ferramenta torna possível analisar seus dados de forma rápida e fácil usando diferentes ferramentas do Excel.

Você pode usar Quick Analysiscom um intervalo ou uma tabela de dados. AcessarQuick Accessferramenta, selecione as células que contêm os dados que você deseja analisar. oQuick Analysisbotão de ferramenta

aparece na parte inferior direita dos dados selecionados.

Clique no Quick Analysis

botão. A barra de ferramentas de análise rápida aparece com as opçõesFORMATTING, CHARTS, TOTALS, TABLES, SPARKLINES.

Quick Analysis A ferramenta é prática e rápida de usar, pois você também pode ter uma prévia da aplicação de diferentes opções, antes de selecionar a que deseja.

Formatação

Conditional Formatting permite que você destaque partes de seus dados adicionando barras de dados, cores, etc. Isso permite que você visualize rapidamente os valores em seus dados.

Você aprendeu sobre as regras de formatação no capítulo Formatação condicional deste tutorial. A diferença é que você pode ter uma visualização rápida e selecionar a opção desejada. No entanto, se você deseja utilizar todos os recursos doConditional Formatting, você prefere acessar o menu principal na Faixa de Opções. A mesma coisa se aplica a todas as opções noQuick Analysis ferramenta.

Clique Formatting no Quick Analysisbarra de ferramentas. oConditional Formattingopções aparecem na barra de ferramentas. Mova o mouse sobre as opções. Você vai ver as visualizações. Você pode então selecionar a opção desejada clicando nela.

Gráficos

Chartssão usados ​​para representar os dados pictoricamente. Existem vários tipos deCharts para se adequar a diferentes tipos de dados.

Se você clicar CHARTS no Quick Analysisbarra de ferramentas, os gráficos recomendados para os dados que você selecionou serão exibidos. Você sempre pode escolherMore Charts opção se você quiser ir para o principal Charts na fita.

Passe o mouse sobre as opções. Você vai ver as visualizações. Você pode então selecionar a opção desejada clicando nela.

Totais

Totalspode ser usado para calcular os números em colunas e linhas. Você terá funções como Soma, Média, Contagem, etc.

Entraremos em detalhes sobre como usar Quick Analysis ferramenta com TOTALSmais tarde neste capítulo. Você pode usar as outras opções emQuick Analysis com a mesma facilidade, como você observa.

Mesas

Tables ajudá-lo a filtrar, classificar e resumir seus dados, como você já aprendeu no Tablescapítulo. NoQuick Analysis ferramenta, tanto o Table e PivotTable as opções estão disponíveis em TABLES. No entanto, você pode ter uma visualização da tabela, mas no caso dePivotTable nenhuma visualização está disponível, pois clicando em você obterá um vazio PivotTable que você precisa preencher com os dados.

Minigráficos

Sparklinessão pequenos gráficos que você pode mostrar junto com seus dados em células individuais. Eles fornecem uma maneira rápida de ver tendências.

Análise rápida com TOTALS

Clique em TOTALS no Quick Analysis Barra de ferramentas.

Dentro Quick Analysis com TOTALS, você pode analisar

Row-wise

Coluna

Para cálculos de linha, certifique-se de ter uma linha vazia abaixo dos dados selecionados.

Example

Analisaremos os dados dos votos obtidos em uma eleição para cinco candidatos. A contagem é feita em quatro rodadas. A seguir estão os dados.

Soma

Selecione os dados e clique

noQuick Analysis barra de ferramentas sob TOTALS.

Certifique-se de que a linha abaixo dos dados esteja vazia. Caso contrário, você receberá uma mensagem dizendo que já existem dados presentes e você terá apenas duas opções, substituir os dados existentes ou cancelar a operação.

Na linha abaixo dos dados selecionados, a soma de cada coluna dos dados é exibida. A legendaSumtambém é fornecido automaticamente. Isso significa que a contagem total de votos em cada rodada para todos os candidatos é exibida.

Média

Selecione os dados e clique

noQuick Analysis Barra de ferramentas em TOTALS.

A média de cada coluna dos dados aparece na linha abaixo dos dados. A legendaAveragetambém é fornecido automaticamente. O número médio de votos obtidos em cada rodada é exibido.

Contagem

Selecione os dados e clique

noQuick Analysis Barra de ferramentas em TOTALS.

A contagem de cada coluna dos dados aparece na linha abaixo dos dados. A legendaCounttambém é fornecido automaticamente. Isso significa que a contagem de candidatos em cada rodada é exibida.

%Total

Selecione os dados e clique

noQuick Analysis Barra de ferramentas em TOTALS.

o %Totalde cada coluna dos dados aparece na linha abaixo dos dados. A legenda%Totaltambém é fornecido automaticamente. Isso significa que%Total de votos em cada rodada é exibida.

Execução total

Selecione os dados e clique

noQuick Analysis Barra de ferramentas em TOTALS.

O total acumulado de cada coluna dos dados aparece na linha abaixo dos dados. A legendaRunning Totaltambém é fornecido automaticamente. Isso significa que o total corrente de votos nas rodadas é exibido.

Soma das Colunas

Selecione os dados e clique

noQuick Analysis barra de ferramentas sob TOTALS.

Certifique-se de que a coluna ao lado dos dados esteja vazia. Caso contrário, você receberá uma mensagem dizendo que já existem dados presentes e você terá apenas duas opções, substituir os dados existentes ou cancelar a operação.

Na coluna ao lado dos dados selecionados, a soma de cada linha dos dados é exibida. O cabeçalho da colunaSumtambém é fornecido automaticamente. Isso significa que o número total de votos obtidos para cada candidato em todas as rodadas é exibido.

Você pode usar as funções do Excel para -

• Encontre valores em um intervalo de dados - VLOOKUP e HLOOKUP
• Obtenha um valor ou a referência a um valor de uma tabela ou intervalo - INDEX
• Obtenha a posição relativa de um item especificado em um intervalo de células - MATCH

Você também pode combinar essas funções para obter os resultados necessários com base nas entradas que você tem.

Usando a função VLOOKUP

A sintaxe da função VLOOKUP é

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Onde

• lookup_value- é o valor que você deseja pesquisar. Lookup_value pode ser um valor ou uma referência a uma célula. Lookup_value deve estar na primeira coluna do intervalo de células que você especificar em table_array

• table_array- é o intervalo de células em que VLOOKUP pesquisará o valor_procurado e o valor de retorno. table_array deve conter

  • o lookup_value na primeira coluna, e

  • o valor de retorno que você deseja encontrar

    Note- A primeira coluna contendo lookup_value pode ser classificada em ordem crescente ou não. No entanto, o resultado será baseado na ordem desta coluna.

• col_index_num- é o número da coluna em table_array que contém o valor de retorno. Os números começam com 1 para a coluna mais à esquerda da tabela-array

• range_lookup- é um valor lógico opcional que especifica se você deseja que VLOOKUP encontre uma correspondência exata ou aproximada. range_lookup pode ser

  • omitido, caso em que é assumido como TRUE e VLOOKUP tenta encontrar uma correspondência aproximada

  • TRUE, nesse caso VLOOKUP tenta encontrar uma correspondência aproximada. Em outras palavras, se uma correspondência exata não for encontrada, o próximo maior valor menor que lookup_value é retornado

  • FALSE, caso em que VLOOKUP tenta encontrar uma correspondência exata

  • 1, caso em que é considerado TRUE e VLOOKUP tenta encontrar uma correspondência aproximada

  • 0, caso em que é considerado FALSO e VLOOKUP tenta encontrar uma correspondência exata

Note- Se range_lookup for omitido ou TRUE ou 1, VLOOKUP funcionará corretamente apenas quando a primeira coluna em table_array for classificada em ordem crescente. Caso contrário, isso pode resultar em valores incorretos. Nesse caso, use FALSE para range_lookup.

Usando a função VLOOKUP com range_lookup TRUE

Considere uma lista de notas dos alunos. Você pode obter as notas correspondentes com VLOOKUP de uma matriz contendo os intervalos de notas e a categoria de aprovação.

table_array -

Observe que as marcas da primeira coluna com base nas quais as notas são obtidas são classificadas em ordem crescente. Portanto, usando TRUE para o argumento range_lookup, você pode obter uma correspondência aproximada que é necessária.

Nomeie esta matriz como Grades.

É uma boa prática nomear arrays dessa maneira, para que você não precise se lembrar dos intervalos de células. Agora, você está pronto para procurar a nota para a lista de notas que você tem da seguinte maneira -

Como você pode observar,

• col_index_num - indica que a coluna do valor de retorno em table_array é 2

• a range_lookup é verdade

  • A primeira coluna que contém o valor de pesquisa nas notas table_array está em ordem crescente. Portanto, os resultados estarão corretos.

  • Você também pode obter o valor de retorno para correspondências aproximadas. ou seja, VLOOKUP calcula da seguinte forma -

Marcas	Categoria de aprovação
<35	Falhou
> = 35 e <50	Terceira classe
> = 50 e <60	Segunda classe
> = 60 e <75	Primeira classe
> = 75	Primeira classe com distinção

Você obterá os seguintes resultados -

Usando a função VLOOKUP com range_lookup FALSE

Considere uma lista de produtos contendo a ID do produto e o preço de cada um dos produtos. O ID do produto e o preço serão adicionados ao final da lista sempre que um novo produto for lançado. Isso significa que os IDs do produto não precisam estar em ordem crescente. A lista de produtos pode ser conforme mostrado abaixo -

table_array -

Nomeie essa matriz como ProductInfo.

Você pode obter o preço de um produto dado o ID do produto com a função VLOOKUP, pois o ID do produto está na primeira coluna. O preço está na coluna 3 e, portanto, col_index_ num deve ser 3.

• Use a função VLOOKUP com range_lookup como TRUE
• Use a função VLOOKUP com range_lookup como FALSE

A resposta correta da matriz ProductInfo é 171,65. Você pode verificar os resultados.

Você observa que tem -

• O resultado correto quando range_lookup é FALSE, e
• Um resultado errado quando range_lookup é TRUE.

Isso ocorre porque a primeira coluna na matriz ProductInfo não é classificada em ordem crescente. Portanto, lembre-se de usar FALSE sempre que os dados não forem classificados.

Usando a função HLOOKUP

Você pode usar HLOOKUP função se os dados estiverem em linhas em vez de colunas.

Exemplo

Tomemos o exemplo das informações do produto. Suponha que a matriz tenha a seguinte aparência -

• Nomeie este Array ProductRange. Você pode encontrar o preço de um produto dado o ID do produto com a função HLOOKUP.

A sintaxe da função HLOOKUP é

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Onde

• lookup_value - é o valor a ser encontrado na primeira linha da tabela

• table_array - é uma tabela de informações em que os dados são pesquisados

• row_index_num - é o número da linha em table_array a partir do qual o valor correspondente será retornado

• range_lookup - é um valor lógico que especifica se você deseja que HLOOKUP encontre uma correspondência exata ou aproximada

• range_lookup pode ser

  • omitido, caso em que é assumido como TRUE e HLOOKUP tenta encontrar uma correspondência aproximada

  • TRUE, caso em que HLOOKUP tenta encontrar uma correspondência aproximada. Em outras palavras, se uma correspondência exata não for encontrada, o próximo maior valor menor que lookup_value é retornado

  • FALSE, caso em que HLOOKUP tenta encontrar uma correspondência exata

  • 1, caso em que é assumido como TRUE e HLOOKUP tenta encontrar uma correspondência aproximada

  • 0, caso em que é considerado FALSE e HLOOKUP tenta encontrar uma correspondência exata

Note- Se range_lookup for Omitted ou TRUE ou 1, HLOOKUP funcionará corretamente apenas quando a primeira coluna em table_array for classificada em ordem crescente. Caso contrário, isso pode resultar em valores incorretos. Nesse caso, use FALSE para range_lookup.

Usando a função HLOOKUP com range_lookup FALSE

Você pode obter o preço de um produto dado o ID do produto com a função HLOOKUP, pois o ID do produto está na primeira linha. O preço está na linha 3 e, portanto, row_index_num deve ser 3.

• Use a função HLOOKUP com range_lookup como TRUE.
• Use a função HLOOKUP com range_lookup como FALSE.

A resposta correta da matriz ProductRange é 171,65. Você pode verificar os resultados.

Você observa que, como no caso de VLOOKUP, você tem

• O resultado correto quando range_lookup é FALSE, e

• Um resultado errado quando range_lookup é TRUE.

Isso ocorre porque a primeira linha na matriz ProductRange não é classificada em ordem crescente. Portanto, lembre-se de usar FALSE sempre que os dados não forem classificados.

Usando a função HLOOKUP com range_lookup TRUE

Considere o exemplo de notas de alunos usadas em VLOOKUP. Suponha que você tenha os dados em linhas em vez de colunas, conforme mostrado na tabela abaixo -

table_array -

Nomeie essa matriz como GradesRange.

Observe que as marcas da primeira linha com base nas quais as notas são obtidas são classificadas em ordem crescente. Portanto, usando HLOOKUP com TRUE para o argumento range_lookup, você pode obter as notas com correspondência aproximada e isso é o que é necessário.

Como você pode observar,

• row_index_num - indica que a coluna do valor de retorno em table_array é 2

• a range_lookup é verdade

  • A primeira coluna que contém o valor de pesquisa em table_array Grades está em ordem crescente. Portanto, os resultados estarão corretos.

  • Você também pode obter o valor de retorno para correspondências aproximadas. ou seja, HLOOKUP calcula da seguinte forma -

Marcas	<35	> = 35 e <50	> = 50 e <60	> = 60 e <75	> = 75
Categoria de aprovação	Falhou	Terceira classe	Segunda classe	Primeira classe	Primeira classe com distinção

Você obterá os seguintes resultados -

Usando a função INDEX

Quando você tem uma matriz de dados, pode recuperar um valor na matriz especificando o número da linha e o número da coluna desse valor na matriz.

Considere os dados de vendas a seguir, nos quais você encontra as vendas em cada uma das regiões Norte, Sul, Leste e Oeste pelos vendedores listados.

• Nomeie a matriz como SalesData.

Usando a função INDEX, você pode encontrar -

• As vendas de qualquer um dos vendedores em uma determinada região.
• Vendas totais em uma região por todos os vendedores.
• Vendas totais por vendedor em todas as regiões.

Você obterá os seguintes resultados -

Suponha que você não saiba os números das linhas dos vendedores e os números das colunas das regiões. Em seguida, você precisa encontrar o número da linha e o número da coluna antes de recuperar o valor com a função de índice.

Você pode fazer isso com a função MATCH, conforme explicado na próxima seção.

Usando a função MATCH

Se precisar da posição de um item em um intervalo, você pode usar a função MATCH. Você pode combinar as funções MATCH e INDEX da seguinte maneira -

Você obterá os seguintes resultados -

A análise de dados em um grande conjunto de dados é frequentemente necessária e importante. Envolve resumir os dados, obter os valores necessários e apresentar os resultados.

O Excel fornece uma Tabela Dinâmica para permitir que você resuma milhares de valores de dados de forma fácil e rápida para obter os resultados necessários.

Considere a seguinte tabela de dados de vendas. A partir desses dados, talvez seja necessário resumir o total de vendas por região, mês ou vendedor. A maneira fácil de lidar com essas tarefas é criar uma Tabela Dinâmica que você pode modificar dinamicamente para resumir os resultados da maneira que desejar.

Criação de tabela dinâmica

Para criar tabelas dinâmicas, certifique-se de que a primeira linha tenha cabeçalhos.

• Clique na mesa.
• Clique na guia INSERIR na faixa de opções.
• Clique em Tabela Dinâmica no grupo Tabelas. A caixa de diálogo Tabela Dinâmica é exibida.

Como você pode ver na caixa de diálogo, você pode usar uma Tabela ou um Intervalo da pasta de trabalho atual ou usar uma fonte de dados externa.

• Na caixa Tabela / Intervalo, digite o nome da tabela.
• Clique em Nova planilha para informar ao Excel onde manter a tabela dinâmica.
• Clique OK.

Uma tabela dinâmica em branco e uma lista de campos da tabela dinâmica aparecem.

Tabelas Dinâmicas Recomendadas

Caso você seja novo nas tabelas dinâmicas ou não saiba quais campos selecionar a partir dos dados, você pode usar o Recommended PivotTables que o Excel fornece.

• Clique na tabela de dados.

• Clique na guia INSERIR.

• Clique em Tabelas Dinâmicas Recomendadas no grupo Tabelas. A caixa de diálogo Tabelas Dinâmicas Recomendadas é exibida.

Na caixa de diálogo Tabelas Dinâmicas recomendadas, as possíveis Tabelas Dinâmicas personalizadas que se adequam aos seus dados são exibidas.

• Clique em cada uma das opções de tabela dinâmica para ver a visualização no lado direito.
• Clique na soma da tabela dinâmica do valor do pedido por vendedor e mês.

Clique OK. A tabela dinâmica selecionada aparece em uma nova planilha. Você pode observar os campos da tabela dinâmica selecionados na lista de campos da tabela dinâmica.

Campos da Tabela Dinâmica

Os cabeçalhos em sua tabela de dados aparecerão como campos na Tabela Dinâmica.

Você pode selecioná-los / desmarcá-los para alterar instantaneamente sua Tabela Dinâmica para exibir apenas as informações que você deseja e da maneira que desejar. Por exemplo, se você deseja exibir as informações da conta em vez das informações do valor do pedido, desmarque Valor do pedido e selecione Conta.

Áreas de Tabela Dinâmica

Você pode até mesmo alterar o Layout de sua Tabela Dinâmica instantaneamente. Você pode usar as áreas da tabela dinâmica para fazer isso.

Nas áreas da Tabela Dinâmica, você pode escolher -

• Quais campos exibir como linhas
• Quais campos exibir como colunas
• Como resumir seus dados
• Filtros para qualquer um dos campos
• Quando atualizar seu layout de tabela dinâmica
  • Você pode atualizá-lo instantaneamente enquanto arrasta os campos pelas áreas, ou
  • Você pode adiar a atualização e atualizá-la apenas quando clicar em ATUALIZAR

Uma atualização instantânea ajuda você a brincar com os diferentes Layouts e escolher aquele que se adapta às suas necessidades de relatório.

Você pode simplesmente arrastar os campos por essas áreas e observar o layout da Tabela Dinâmica enquanto o faz.

Aninhamento na Tabela Dinâmica

Se você tiver mais de um talhão em qualquer uma das áreas, o aninhamento acontecerá na ordem em que você colocar os campos nessa área. Você pode alterar a ordem arrastando os campos e observar como o aninhamento muda. Nas opções de layout acima, você pode observar que

• Os meses estão em colunas.
• Região e vendedor em linhas nessa ordem. ou seja, os valores do vendedor são aninhados nos valores da região.
• O resumo é feito pela soma do valor do pedido.
• Nenhum filtro é escolhido.

A Tabela Dinâmica resultante é a seguinte -

Nas áreas da tabela dinâmica, em linhas, clique na região e arraste-a para baixo do vendedor de forma que tenha a seguinte aparência -

A ordem de aninhamento muda e a Tabela Dinâmica resultante é a seguinte -

Note- Você pode observar claramente que o layout com a ordem de aninhamento - Região e depois Vendedor produz um relatório melhor e compacto do que aquele com a ordem de aninhamento - Vendedor e depois Região. Caso o Vendedor represente mais de uma área e você precise resumir as vendas por Vendedor, o segundo layout seria a melhor opção.

Filtros

Você pode atribuir um filtro a um dos campos para que possa alterar dinamicamente a tabela dinâmica com base nos valores desse campo.

Arraste a região das linhas para os filtros nas áreas da tabela dinâmica.

O filtro com o rótulo como Região aparece acima da Tabela Dinâmica (caso você não tenha linhas vazias acima da Tabela Dinâmica, a Tabela Dinâmica é empurrada para baixo para liberar espaço para o Filtro.

Você pode ver isso -

• Os valores do vendedor aparecem em linhas.
• Os valores do mês aparecem em colunas.
• Filtro de região aparece na parte superior com o padrão selecionado como TODOS.
• O valor de resumo é a soma do valor do pedido
  • A soma do valor do pedido do vendedor aparece na coluna Total geral
  • A soma do valor do pedido no mês aparece na linha Total geral

Clique na seta na caixa à direita da região do filtro. Uma lista suspensa com os valores da região do campo é exibida.

• Marque a opção Select Multiple Items. Caixas de seleção aparecem para todos os valores.
• Selecione Sul e Oeste, desmarque os outros valores e clique em OK.

Os dados relativos às regiões Sul e Oeste serão resumidos apenas conforme mostrado na captura de tela abaixo -

Você pode ver que ao lado da região do filtro, Multiple Itemsé exibido, indicando que você selecionou mais de um item. No entanto, quantos itens e / ou quais itens são selecionados não é conhecido no relatório que é exibido. Nesse caso, usar Slicers é uma opção melhor para filtragem.

Slicers

Você pode usar Slicers para ter uma melhor clareza sobre quais itens os dados foram filtrados.

• Clique em ANALISAR em FERRAMENTAS PIVOTÁVEIS na faixa de opções.

• Clique em Inserir Slicer no grupo Filtro. A caixa Inserir Segmentações de Dados é exibida. Ele contém todos os campos de seus dados.

• Selecione os campos Região e mês. Clique OK.

Os Slicers para cada um dos campos selecionados aparecem com todos os valores selecionados por padrão. Ferramentas do Slicer aparecem na Faixa de Opções para trabalhar nas configurações do Slicer, aparência e comportamento.

• Selecione Sul e Oeste no Slicer para Região.
• Selecione fevereiro e março no Slicer para o mês.
• Mantenha a tecla Ctrl pressionada enquanto seleciona vários valores em um Slicer.

Os itens selecionados nos Slicers são destacados. A tabela dinâmica com valores resumidos para os itens selecionados será exibida.

Resumindo valores por outros cálculos

Nos exemplos até agora, você viu valores resumidos por Soma. No entanto, você também pode usar outros cálculos, se necessário.

Na lista de campos da tabela dinâmica

• Selecione a conta de campo.
• Desmarque o valor do pedido de campo.

• Arraste o campo Conta para a área Resumindo Valores. Por padrão, Soma da conta será exibida.
• Clique na seta do lado direito da caixa.
• No menu suspenso que aparece, clique em Configurações do campo de valor.

A caixa Configurações do campo de valor é exibida. Vários tipos de cálculos aparecem como uma lista no campo Resumir valor por -

• Selecione Contagem na lista.
• O nome personalizado muda automaticamente para Contagem de conta. Clique OK.

A tabela dinâmica resume os valores da conta por contagem.

Ferramentas de Tabela Dinâmica

Siga as etapas fornecidas abaixo para aprender a usar as ferramentas de tabela dinâmica.

• Selecione a Tabela Dinâmica.

As seguintes ferramentas de tabela dinâmica aparecem na faixa de opções -

• ANALYZE
• DESIGN

ANALISAR

Alguns dos ANALYZE Os comandos da faixa de opções são -

• Definir opções de tabela dinâmica
• Configurações de campo de valor para o campo selecionado
• Expandir Campo
• Campo de recolhimento
• Insert Slicer
• Inserir linha do tempo
• Atualizar dados
• Alterar fonte de dados
• Mover Tabela Dinâmica
• Resolva a ordem (se houver mais cálculos)
• PivotChart

PROJETO

Alguns dos DESIGN Os comandos da faixa de opções são -

• Layout de tabela dinâmica
  • Opções para subtotais
  • Opções para totais gerais
  • Formulários de Layout de Relatório
  • Opções para linhas em branco
• Opções de estilo de tabela dinâmica
• Estilos de tabela dinâmica

Campo de expansão e recolhimento

Você pode expandir ou recolher todos os itens de um campo selecionado de duas maneiras -

• Selecionando o símbolo
  
  ou
  
  à esquerda do campo selecionado.
• Ao clicar em Expandir campo ou Recolher campo na faixa ANALYZE.

Selecionando o símbolo Expandir

ou Fechar símbolo

à esquerda do campo selecionado

• Selecione a célula que contém Leste na Tabela Dinâmica.
• Clique no símbolo de colapso
  
  à esquerda de Leste.

Todos os itens em Leste serão recolhidos. O símbolo Collapse

à esquerda de East muda para o símbolo Expand .

Você pode observar que apenas os itens abaixo do Leste estão recolhidos. O resto dos itens da tabela dinâmica são como estão.

Clique no símbolo Expandir

à esquerda de Leste. Todos os itens abaixo de Leste serão exibidos.

Usando ANALYZE na fita

Você pode recolher ou expandir todos os itens na Tabela Dinâmica de uma vez com os comandos Expandir Campo e Contrair Campo na Faixa de Opções.

• Clique na célula que contém Leste na Tabela Dinâmica.
• Clique na guia ANALISAR na faixa de opções.
• Clique em Recolher campo no grupo Campo ativo.

Todos os itens do campo Leste na Tabela Dinâmica serão recolhidos.

Clique em Expandir Campo no grupo Campo Ativo.

Todos os itens serão exibidos.

Estilos de apresentação de relatórios

Você pode escolher o estilo de apresentação da sua Tabela Dinâmica, pois a incluiria como um relatório. Selecione um estilo que se encaixe no resto da sua apresentação ou relatório. Porém, não se aborreça com os estilos porque um relatório que dá impacto na exibição dos resultados é sempre melhor do que um colorido, que não destaca os pontos de dados importantes.

• Clique em Leste na Tabela Dinâmica.
• Clique em ANALISAR.
• Clique em Configurações de campo no grupo Campo ativo. A caixa de diálogo Configurações do campo é exibida.
• Clique na guia Layout e impressão.
• Marque Inserir linha em branco após cada etiqueta de item.

Linhas em branco serão exibidas após cada valor do campo Região.

Você pode inserir linhas em branco do DESIGN guia também.

• Clique na guia DESIGN.
• Clique em Layout de relatório no grupo Layout.
• Selecione Mostrar no formulário de contorno na lista suspensa.

• Passe o mouse sobre os Estilos de tabela dinâmica. Uma prévia do estilo no qual o mouse foi colocado aparecerá.
• Selecione o estilo que se adapta ao seu relatório.

Será exibida uma tabela dinâmica em formato de contorno com o estilo selecionado.

Linha do tempo em tabelas dinâmicas

Para entender como usar a linha do tempo, considere o exemplo a seguir, em que os dados de vendas de vários itens são fornecidos pelo vendedor e pela localização. Há um total de 1891 linhas de dados.

Crie uma tabela dinâmica a partir deste intervalo com -

• Localização e vendedor em linhas nessa ordem
• Produto em colunas
• Soma da quantidade em valores de resumo

• Clique na Tabela Dinâmica.
• Clique na guia INSERIR.
• Clique em Linha do tempo no grupo Filtros. O Insert Timelines aparece.

Clique em Data e clique em OK. A caixa de diálogo Timeline é exibida e as Ferramentas da Timeline aparecem na Faixa de Opções.

• Na caixa de diálogo Linha do tempo, selecione MESES.
• Na lista suspensa, selecione QUARTERS.
• Clique em 2014 Q2.
• Mantenha a tecla Shift pressionada e arraste para o quarto trimestre de 2014.

A linha do tempo é selecionada para o segundo - quarto trimestre de 2014.

A Tabela Dinâmica é filtrada para esta Linha do Tempo.

Você pode exibir seus relatórios de análise de dados de várias maneiras no Excel. No entanto, se os resultados da análise de dados podem ser visualizados como gráficos que destacam os pontos notáveis ​​nos dados, seu público pode compreender rapidamente o que você deseja projetar nos dados. Também causa um bom impacto no seu estilo de apresentação.

Neste capítulo, você aprenderá a usar gráficos e recursos de formatação do Excel em gráficos que permitem apresentar os resultados da análise de dados com ênfase.

Visualizando Dados com Gráficos

No Excel, os gráficos são usados ​​para fazer uma representação gráfica de qualquer conjunto de dados. Um gráfico é uma representação visual dos dados, em que os dados são representados por símbolos como barras em um Gráfico de Barras ou linhas em um Gráfico de Linhas. O Excel fornece muitos tipos de gráficos e você pode escolher um que seja adequado aos seus dados ou pode usar a opção Gráficos recomendados do Excel para visualizar os gráficos personalizados para seus dados e selecionar um deles.

Consulte o Tutorial de gráficos do Excel para obter mais informações sobre os tipos de gráficos.

Neste capítulo, você entenderá as diferentes técnicas que pode usar com os gráficos do Excel para destacar os resultados da análise de dados com mais eficácia.

Criação de gráficos de combinação

Suponha que você tenha a meta e os lucros reais para o ano fiscal de 2015-2016 obtidos em diferentes regiões.

Criaremos um gráfico de colunas agrupadas para esses resultados.

Conforme você observa, é difícil visualizar a comparação rapidamente entre as metas e o real neste gráfico. Não tem um impacto real nos seus resultados.

A melhor maneira de distinguir dois tipos de dados para comparar os valores é usando gráficos de combinação. No Excel 2013 e nas versões anteriores, você pode usar gráficos de combinação para a mesma finalidade.

Use colunas verticais para os valores de destino e uma linha com marcadores para os valores reais.

• Clique na guia DESIGN na guia CHART TOOLS na faixa de opções.
• Clique em Alterar tipo de gráfico no grupo Tipo. A caixa de diálogo Alterar tipo de gráfico é exibida.

• Clique em Combo.

• Altere o tipo de gráfico da série real para linha com marcadores. A visualização aparece em Combinação personalizada.

• Clique OK.

Seu gráfico de combinação personalizado será exibido.

Como você pode observar no gráfico, os valores de destino estão em colunas e os valores reais são marcados ao longo da linha. A visualização dos dados ficou melhor, pois também mostra a tendência de seus resultados.

No entanto, esse tipo de representação não funciona bem quando os intervalos de dados de seus dois valores de dados variam significativamente.

Criando um gráfico de combinação com o eixo secundário

Suponha que você tenha os dados sobre o número de unidades de seu produto que foram enviadas e os lucros reais para o ano fiscal de 2015-2016 obtidos em diferentes regiões.

Se você usar o mesmo gráfico de combinação de antes, obterá o seguinte -

No gráfico, os dados de No. of Units não está visível, pois os intervalos de dados variam significativamente.

Nesses casos, você pode criar um gráfico de combinação com o eixo secundário, de forma que o eixo primário exiba um intervalo e o eixo secundário exiba o outro.

• Clique na guia INSERIR.
• Clique em Combo no grupo Charts.
• Clique em Criar gráfico de combinação personalizado na lista suspensa.

A caixa de diálogo Inserir gráfico é exibida com a combinação destacada.

Para o tipo de gráfico, escolha -

• Linha com marcadores para o número da série de unidades

• Coluna agrupada para os lucros reais da série

• Marque a caixa Eixo secundário à direita do Nº de série de unidades e clique em OK.

Uma visualização de seu gráfico aparece em Combinação personalizada.

Seu gráfico de combinação aparece com o eixo secundário.

You can observe the values for Actual Profits on the primary axis and the values for No. of Units on the secondary axis.

A significant observation in the above chart is for Quarter 3 where No. of Units sold is more, but the Actual Profits made are less. This could probably be assigned to the promotion costs that were incurred to increase sales. The situation is improved in Quarter 4, with a slight decrease in sales and a significant rise in the Actual Profits made.

Discriminating Series and Category Axis

Suppose you want to project the Actual Profits made in Years 2013-2016.

Create a clustered column for this data.

As you observe, the data visualization is not effective as the years are not displayed. You can overcome this by changing year to category.

Remove the header year in the data range.

Now, year is considered as a category and not a series. Your chart looks as follows −

Chart Elements and Chart Styles

Chart Elements give more descriptions to your charts, thus helping visualizing your data more meaningfully.

• Click the Chart

Three buttons appear next to the upper-right corner of the chart −

• 
  Chart Elements
• 
  Chart Styles
• 
  Chart Filters

For a detailed explanation of these, refer to Excel Charts tutorial.

• Click Chart Elements.
• Click Data Labels.

• Click Chart Styles
• Select a Style and Color that suits your data.

You can use Trendline to graphically display trends in data. You can extend a Trendline in a chart beyond the actual data to predict future values.

Data Labels

Excel 2013 and later versions provide you with various options to display Data Labels. You can choose one Data Label, format it as you like, and then use Clone Current Label to copy the formatting to the rest of the Data Labels in the chart.

The Data Labels in a chart can have effects, varying shapes and sizes.

It is also possible to display the content of a cell as part of the Data Label with Insert Data Label Field.

Quick Layout

You can use Quick Layout to change the overall layout of the chart quickly by choosing one of the predefined layout options.

• Click the chart.
• Click the DESIGN tab under CHART TOOLS.
• Click Quick Layout.

Different possible layouts will be displayed. As you move on the layout options, the chart layout changes to that particular option.

Select the layout you like. The chart will be displayed with the chosen layout.

Using Pictures in Column Charts

You can create more emphasis on your data presentation by using a picture in place of columns.

• Click on a Column on the Column Chart.

• In the Format Data Series, click on Fill.

• Select Picture.

• Under Insert picture from, provide the filename or optionally clipboard if you had copied an image earlier.

The picture you have chosen will appear in place of columns in the chart.

Band Chart

You might have to present customer survey results of a product from different regions. Band Chart is suitable for this purpose. A Band Chart is a Line Chart with an added shaded area to display the upper and lower boundaries of groups of data.

Suppose your customer survey results from the east and west regions, month wise are −

Here, in the data < 50% is Low, 50% - 80% is Medium and > 80% is High.

With Band Chart, you can display your survey results as follows −

Create a Line Chart from your data.

Change the chart type to −

• East and West Series to Line with Markers.
• Low, Medium and High Series to Stacked Column.

Your chart looks as follows.

• Click on one of the columns.
• Change gap width to 0% in Format Data Series.

You will get Bands instead of columns.

To make the chart more presentable −

• Add Chart Title.
• Adjust Vertical Axis range.
• Change the colors of the bands to Green-Yellow-Red.
• Add Labels to bands.

The final result is the Band Chart with the defined boundaries and the survey results represented across the bands. One can quickly and clearly make out from the chart that while the survey results for the region West are satisfactory, those for the region East have a decline in the last quarter and need attention.

Thermometer Chart

When you have to represent a target value and an actual value, you can easily create a Thermometer Chart in Excel that emphatically shows these values.

With Thermometer chart, you can display your data as follows −

Arrange your data as shown below −

• Select the data.
• Create a Clustered Column chart.

As you observe, the right side Column is Target.

• Click on a Column in the chart.
• Click on Switch Row/Column on the Ribbon.

• Right click on the Target Column.
• Click on Format Data Series.
• Click on Secondary Axis.

As you observe the Primary Axis and Secondary Axis have different ranges.

• Right click the Primary Axis.
• In the Format Axis options, under Bounds, type 0 for Minimum and 1 for Maximum.
• Repeat the same for Secondary Axis.

Both Primary Axis and Secondary Axis will be set to 0% - 100%. The Target Column hides the Actual Column.

• Right click the visible column (Target)
• In the Format Data Series, select
  • No fill for FILL
  • Solid line for BORDER
  • Blue for Color

• In Chart Elements, unselect
  • Axis → Primary Horizontal
  • Axis → Secondary Vertical
  • Gridlines
  • Chart Title
• In the chart, right click on Primary Vertical Axis
• In Format Axis options, click on TICK MARKS
• For Major type, select Inside

• Right click on the Chart Area.
• In the Format Chart Area options, select
  • No fill for FILL
  • No line for BORDER

Resize the chart area, to get the shape of a thermometer.

You got your thermometer chart, with the actual value as against target value being shown. You can make this thermometer chart more impressive with some formatting.

• Insert a rectangle shape superimposing the blue rectangular part in the chart.
• In Format Shape options, select −
  • Gradient fill for FILL
  • Linear for Type
  • 180⁰ for Angle
• Set the Gradient stops at 0%, 50% and 100%.
• For the Gradient stops at 0% and 100%, choose the color black.
• For the Gradient stop at 50%, choose the color white.

• Insert an oval shape at the bottom.
• Format shape with same options.

The result is the Thermometer Chart that we started with.

Gantt Chart

A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time in relation to the amount of work planned for those periods.

In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type so that it depicts tasks, task duration, and hierarchy. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis.

Consider the following data where the column −

• Task represents the Tasks in the project
• Start represents number of days from the Start Date of the project
• Duration represents the duration of the Task

Note that Start of any Task is Start of previous Task + Duration. This is the case when the Tasks are in hierarchy.

• Select the data.
• Create Stacked Bar Chart.

• Right-click on Start Series.
• In Format Data Series options, select No fill.

• Clique com o botão direito no eixo de categorias.
• Nas opções de Formatar eixo, selecione Categorias na ordem inversa.

• Em Elementos do gráfico, desmarque
  • Legend
  • Gridlines
• Formate o eixo horizontal para
  • Ajuste o alcance
  • Marcas principais em intervalos de 5 dias
  • Marcas menores em intervalos de 1 dia
• Formate a série de dados para torná-la impressionante
• Dê um título ao gráfico

Gráfico de Cachoeira

Waterfall Chart é uma das ferramentas de visualização mais populares usadas em pequenas e grandes empresas. Os gráficos em cascata são ideais para mostrar como você chegou a um valor líquido, como receita líquida, dividindo o efeito cumulativo de contribuições positivas e negativas.

O Excel 2016 fornece o tipo de gráfico em cascata. Se você estiver usando versões anteriores do Excel, ainda poderá criar um gráfico em cascata usando o gráfico de colunas empilhadas.

As colunas são codificadas por cores para que você possa diferenciar rapidamente os números positivos dos negativos. As colunas de valor inicial e final começam no eixo horizontal, enquanto os valores intermediários são colunas flutuantes. Por causa dessa aparência, os gráficos em cascata também são chamados de gráficos de pontes.

Considere os seguintes dados.

• Prepare os dados para o Waterfall Chart

• Certifique-se de que a coluna Fluxo de caixa líquido esteja à esquerda da coluna Meses (isso ocorre porque você não incluirá esta coluna ao criar o gráfico)

• Adicione 2 colunas - aumento e diminuição para fluxos de caixa positivos e negativos, respectivamente

• Adicione uma coluna Início - a primeira coluna no gráfico com o valor inicial no Fluxo de Caixa Líquido

• Adicione uma coluna Final - a última coluna no gráfico com o valor final no Fluxo de Caixa Líquido

• Adicione uma coluna Float - que suporta as colunas intermediárias

• Calcule os valores dessas colunas da seguinte forma

• Na coluna Float, insira uma linha no início e no final. Coloque n valor arbitrário 50000. Isso apenas para ter algum espaço à esquerda e à direita do gráfico

Os dados serão os seguintes.

• Selecione as células C2: H18 (coluna Excluir fluxo de caixa líquido)
• Criar gráfico de colunas empilhadas

• Clique com o botão direito na Série Float.
• Clique em Formatar série de dados.
• Nas opções de Formatar série de dados, selecione Sem preenchimento.

• Clique com o botão direito em Negative Series.
• Selecione a cor de preenchimento como vermelho.

• Clique com o botão direito em Positive Series.
• Selecione a cor de preenchimento como verde.

• Clique com o botão direito em Iniciar série.
• Selecione a cor de preenchimento como cinza.
• Clique com o botão direito em End Series.
• Selecione a cor de preenchimento como cinza.
• Exclua a legenda.

• Clique com o botão direito em qualquer série
• Nas opções de Format Data Series, selecione Gap Width como 10% em Series Options

Dê o título do gráfico. O gráfico em cascata será exibido.

Minigráficos

Minigráficos são pequenos gráficos colocados em células individuais, cada um representando uma linha de dados em sua seleção. Eles fornecem uma maneira rápida de ver tendências.

Você pode adicionar Sparklines com ferramenta de análise rápida.

• Selecione os dados para os quais deseja adicionar Minigráficos.
• Mantenha uma coluna vazia à direita dos dados para os Minigráficos.

O botão Análise rápida

aparece na parte inferior direita dos dados selecionados.

• Clique no

  
  botão Quick Analysis . A barra de ferramentas de análise rápida é exibida com várias opções.

Clique SPARKLINES. As opções de gráfico exibidas são baseadas nos dados e podem variar.

Clique Line. Um gráfico de linha para cada linha é exibido na coluna à direita dos dados.

PivotCharts

Os gráficos dinâmicos são usados ​​para resumir dados graficamente e explorar dados complicados.

Um gráfico dinâmico mostra séries de dados, categorias e eixos do gráfico da mesma forma que um gráfico padrão. Além disso, ele também fornece controles de filtragem interativos diretamente no gráfico para que você possa analisar rapidamente um subconjunto de seus dados.

Os gráficos dinâmicos são úteis quando você tem dados em uma tabela dinâmica enorme ou muitos dados de planilha complexos que incluem texto e números. Um gráfico dinâmico pode ajudá-lo a entender esses dados.

Você pode criar um gráfico dinâmico a partir de

• Uma Tabela Dinâmica.
• Uma tabela de dados autônoma, sem tabela dinâmica.

PivotChart from PivotTable

Para criar um gráfico dinâmico, siga as etapas abaixo -

• Clique na Tabela Dinâmica.
• Clique em ANALISAR em FERRAMENTAS PIVOTÁVEIS na faixa de opções.
• Clique em PivotChart. A caixa de diálogo Inserir gráfico é exibida.

Selecione Coluna em Cluster na opção Coluna.

Clique OK. O gráfico dinâmico é exibido.

O gráfico dinâmico possui três filtros - Região, Vendedor e Mês.

• Clique na opção Region Filter Control. A caixa de pesquisa aparece com a lista de todas as regiões. As caixas de seleção aparecem ao lado de Regiões.

• Selecione as opções Leste e Sul.

Os dados filtrados aparecem no gráfico dinâmico e na tabela dinâmica.

Gráfico Dinâmico sem Tabela Dinâmica

Você pode criar um gráfico dinâmico autônomo sem criar uma tabela dinâmica.

• Clique na Tabela de dados.
• Clique na guia Inserir.
• Clique em Gráfico Dinâmico no grupo Gráficos. A janela Criar gráfico dinâmico é exibida.
• Selecione a tabela / intervalo.
• Selecione o local onde deseja que o gráfico dinâmico seja colocado.

Você pode escolher uma célula na própria planilha existente ou em uma nova planilha. Clique OK.

Um gráfico dinâmico vazio e uma tabela dinâmica vazia aparecem junto com a Lista de campos do gráfico dinâmico para criar o gráfico dinâmico.

• Escolha os campos a serem adicionados ao gráfico dinâmico

• Organize os campos arrastando-os para FILTROS, LENDA (SÉRIE), EIXO (CATEGORIAS) e VALORES

• Use os controles de filtro no gráfico dinâmico para selecionar os dados a serem colocados no gráfico dinâmico

O Excel criará automaticamente uma Tabela Dinâmica acoplada.

A Validação de Dados é uma ferramenta muito útil e fácil de usar no Excel com a qual você pode definir validações de dados nos dados que são inseridos em sua Planilha.

Para qualquer célula da planilha, você pode

• Exibe uma mensagem de entrada sobre o que precisa ser inserido.
• Restrinja os valores inseridos.
• Forneça uma lista de valores para escolher.
• Exibe uma mensagem de erro e rejeita uma entrada de dados inválida.

Considere o seguinte rastreador de risco que pode ser usado para inserir e rastrear as informações de riscos identificados.

Neste rastreador, os dados inseridos nas colunas a seguir são validados com restrições de dados predefinidas e os dados inseridos são aceitos apenas quando atendem aos critérios de validação. Caso contrário, você receberá uma mensagem de erro.

• Probability
• Impact
• Categoria de Risco
• Fonte de Risco
• Status

A coluna Exposição ao risco terá valores calculados e você não pode inserir nenhum dado. Até a colunaS. No. é definido para ter valores calculados que são ajustados mesmo se você excluir uma linha.

Agora, você aprenderá como configurar essa planilha.

Prepare a estrutura para a planilha

Para preparar a estrutura da planilha -

• Comece com uma planilha em branco.
• Coloque o cabeçalho na linha 2.
• Coloque os cabeçalhos das colunas na linha 3.
• Para os cabeçalhos de coluna Probabilidade, Impacto e Exposição ao Risco -
  • Clique com o botão direito na célula.
  • Clique em Formatar células no menu suspenso.
  • Na caixa de diálogo Formatar células, clique na guia Alinhamento.
  • Digite 90 em Orientação.
• Mesclar e centralizar as células nas linhas 3, 4 e 5 para cada um dos cabeçalhos das colunas.
• Bordas de formato para as células nas linhas 2 - 5.
• Ajuste as larguras das linhas e colunas.

Sua planilha terá a seguinte aparência -

Definir valores válidos para categoria de risco

Nas células M5 - M13, insira os seguintes valores (M5 é o rumo e M6 - M13 são os valores)

Valores de Categoria

Usuários finais

Cliente

Gestão

Cronograma

Cronograma

Meio Ambiente

produtos

Projeto

• Clique na primeira célula na coluna Categoria de risco (H6).
• Clique na guia DADOS na faixa de opções.
• Clique em Validação de dados no grupo Ferramentas de dados.
• Selecione Validação de dados… na lista suspensa.

A caixa de diálogo Validação de dados é exibida.

• Clique na guia Configurações.
• Nos critérios de validação, no Allow: lista suspensa, selecione a opção List.

• Selecione o intervalo M6: M13 na caixa Fonte: que aparece.
• Marque as caixas Ignorar em branco e o menu suspenso Na célula que aparecem.

Definir mensagem de entrada para categoria de risco

• Clique na guia Mensagem de entrada na caixa de diálogo Validação de dados.
• Verifica a caixa Show input message quando a célula é selecionada.
• Na caixa sob Título :, digite Categoria de risco:
• In the box under Input message: Choose the Category of the Risk from the List.

Set Error Alert for Risk Category

To set error alert −

• Click the Error Alert tab in the Data validation dialog box.
• Check the box Show error alert after invalid data is entered.
• Select Stop under Style: dropdown
• In the box under Title:, type Invalid Entry:
• In the box under Error message: type Choose a value from dropdown list.
• Click OK.

Verify Data Validation for Risk Category

For the selected first cell under Risk Category,

• Data Validation criteria is set
• Input message is set
• Error alert is set

Now, you can verify your settings.

Click in the cell for which you have set Data Validation criteria. The Input message appears. The dropdown button appears on the right side of the cell.

The input message is correctly displayed.

• Click on the dropdown button on the right side of the cell. The drop-down list appears with the values that can be selected.

• Cross-check the values in the drop-down list with those that are used to create the drop-down list.

Both the sets of values match. Note that if the number of values is more, you will get a scroll-down bar on the right side of the dropdown list.

Select a value from the dropdown list. It appears in the cell.

You can see that the selection of valid values is working fine.

Finally, try to enter an invalid entry and verify the Error alert.

Type People in the cell and press Enter. Error message that you have set for the cell will be displayed.

• Verify the Error message.
• You have an option to either Retry or Cancel. Verify both the options.

You have successfully set the Data Validation for the cell.

Note − It is very important to check the spelling and grammar of your messages.

Set Valid Criteria for the Risk Category Column

Now, you are ready to apply the Data Validation criteria to all the cells in the Risk Category column.

At this point, you need to remember two things −

• You need to set the criteria for maximum number of cells that are possible to be used. In our example, it can vary from 10 – 100 based on where the worksheet will be used.

• You should not set the criteria for unwanted range of cells or for the entire column. This will unnecessarily increases the file size. It is called excess formatting. If you get a worksheet from an outside source, you have to remove the excess formatting, which you will learn in the chapter on Inquire in this tutorial.

Follow the steps given below −

• Set the validation criteria for 10 cells under Risk Category.
• You can easily do this by clicking on the right-bottom corner of the first cell.
• Hold on the + symbol that appears and pull it down.

Data Validation is set for all the selected cells.

Click the last column that is selected and verify.

Data Validation for the column Risk Category is complete.

Set Validation Values for Risk Source

In this case, we have only two values – Internal and External.

• Click in the first cell under the column Risk Source (I6)
• Click the DATA tab on the Ribbon
• Click Data Validation in the Data Tools group
• Select Data Validation… from the drop-down list.

Data Validation dialog box appears.

• Click the Settings tab.
• Under Validation criteria, in the Allow: drop-down list, select the option List.
• Type Internal, External in the Source: box that appears.
• Check the boxes Ignore blank and In-cell dropdown that appear.

Set Input Message for Risk Source.

Set Error Alert for Risk Source.

For the selected first cell under Risk Source −

• Data Validation criteria is set
• Input message is set
• Error alert is set

Now, you can verify your settings.

Click in the cell for which you have set Data Validation criteria. Input message appears. The drop-down button appears on the right side of the cell.

The input message is displayed correctly.

• Click the drop-down arrow button on the right side of the cell. A drop-down list appears with the values that can be selected.

• Check if the values are the same as you typed – Internal and External.

Both the sets of values match. Select a value from the drop-down list. It appears in the cell.

You can see that the selection of valid values is working fine. Finally, try to enter an invalid entry and verify the Error alert.

Type Financial in the cell and press Enter. Error message that you have set for the cell will be displayed.

• Verify the Error message. You have successfully set the Data Validation for the cell.

• Set valid criteria for the Risk Source Column

• Apply the Data Validation criteria to the cells I6 - I15 in the Risk Source column (i.e. same range as that of Risk Category column).

Data Validation is set for all the selected cells. Data Validation for the column Risk Source is complete.

Set Validation Values for Status

• Repeat the same steps that you used for setting Validation values for Risk Source.

• Set the List values as Open, Closed.

• Apply the Data Validation criteria to the cells K6 - K15 in the Status column (i.e. same range as that of Risk Category column).

Data Validation is set for all the selected cells. Data Validation for the column status is complete.

Set Validation Values for Probability

Risk Probability Score values are in the range 1-5, 1 being low and 5 being high. The value can be any integer between 1 and 5, both inclusive.

• Click in the first cell under the column Risk Source (I6).
• Click the DATA tab on the Ribbon.
• Click Data Validation in the Data Tools group.
• Select Data Validation… from the drop-down list.

The Data Validation dialog box appears.

• Click the Settings tab.
• Under Validation criteria, in the Allow: drop-down list, select Whole number.

• Select between under Data:
• Type 1 in the box under Minimum:
• Type 5 in the box under Maximum:

Set Input Message for Probability

Set Error Alert for Probability and click OK.

For the selected first cell under Probability,

• Data Validation criteria is set.
• Input message is set.
• Error alert is set.

Now, you can verify your settings.

Click on the cell for which you have set Data Validation criteria. Input message appears. In this case, there will not be a dropdown button because the input values are set to be in a range and not from list.

The input message is correctly displayed.

Enter an integer between 1 and 5 in the cell. It appears in the cell.

Selection of valid values is working fine. Finally, try to enter an invalid entry and verify the Error alert.

Type 6 in the cell and press Enter. The Error message that you have set for the cell will be displayed.

You have successfully set the Data Validation for the cell.

• Set valid criteria for the Probability Column.

• Apply the Data Validation criteria to the cells E6 - E15 in the Probability column (i.e. same range as that of Risk Category column).

Data Validation is set for all the selected cells. Data Validation for the column Probability is complete.

Set Validation Values for Impact

To set the validation values for Impact, repeat the same steps that you used for setting validation values for probability.

Apply the Data Validation criteria to the cells F6 - F15 in the Impact column (i.e. same range as that of Risk Category column).

Data Validation is set for all the selected cells. Data Validation for the column Impact is complete.

Set the Column Risk Exposure with Calculated Values

Risk Exposure is calculated as a product of Risk Probability and Risk Impact.

Risk Exposure = Probability * Impact

Type =E6*F6 in cell G6 and press Enter.

0 will be displayed in the cell G6 as E6 and F6 are empty.

Copy the formula in the cells G6 – G15. 0 will be displayed in the cells G6 - G15.

As the Risk Exposure column is meant for calculated values, you should not allow data entry in that column.

• Select cells G6-G15

• Right-click and in the dropdown list that appears, select Format Cells. The Format Cells dialog box appears.

• Click the Protection tab.

• Check the option Locked.

This is to ensure that data entry is not allowed in those cells. However, this will come into effect only when the worksheet is protected, which you will do as the last step after the worksheet is ready.

• Click OK.
• Shade the cells G6-G15 to indicate they are calculated values.

Format Serial Number Values

You can leave it to the user to fill in the S. No. Column. However, if you format the S. No. values, the worksheet looks more presentable. In addition, it shows for how many rows the worksheet is formatted.

Type =row()-5 in the cell B6 and press Enter.

1 will appear in cell B6. Copy the formula in the cells B6-B15. Values 1-10 appear.

Shade the cells B6-B15.

Wrap-up

You are almost done with your project.

• Hide Column M that contains Data Category values.
• Format Borders for the cells B6-K16.

• Right-click on the worksheet tab.
• Select Protect Sheet from the menu.

The Protect Sheet dialog box appears.

• Check the option Protect worksheet and contents of locked cells.
• Type in a password under Password to unprotect sheet −
  • Password is case sensitive
  • Protected sheet cannot be recovered if password is forgotten
  • It is a good practice to keep a list of worksheet names and passwords somewhere
• Under Allow all users of this worksheet to: check the box Select unlocked cells.

You have protected the locked cells in the column Risk Exposure from data entry and kept the rest of the unlocked cells editable. Click OK.

The Confirm Password dialog box appears.

• Re-enter the password.
• Click OK.

Your worksheet with Data Validation set for selected cells is ready to use.

You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results.

In this chapter, you will learn where and how you can use these functions for your analysis.

What is Annuity?

An annuity is a series of constant cash payments made over a continuous period. For example, savings for retirement, insurance payments, home loan, mortgage, etc. In annuity functions −

• A positive number represents cash received.
• A negative number represents cash paid out.

Present Value of a series of Future Payments

The present value is the total amount that a series of future payments is worth now. You can calculate the present value using the Excel functions −

• PV − Calculates the present value of an investment by using an interest rate and a series of future payments (negative values) and income (positive values). At least one of the cash flows must be positive and at least one must be negative.

• NPV − Calculates the net present value of an investment by using a discount rate and a series of periodic future payments (negative values) and income (positive values).

• XNPV − Calculates the net present value for a schedule of cash flows that is not necessarily periodic.

Note that −

• PV cash flows must be constant whereas NPV cash flows can be variable.

• PV cash flows can be either at the beginning or at the end of the period whereas NPV cash flows must be at the end of the period.

• NPV cash flows must be periodic whereas XNPV cash flows need not be periodic.

In this section, you will understand how to work with PV. You will learn about NPV in a later section.

Example

Suppose you are buying a refrigerator. The salesperson tells you that the price of the refrigerator is 32000, but you have an option to pay out the amount in 8 years with an interest rate of 13% per annum and yearly payments of 6000. You also have an option to make the payments either at the beginning or end of each year.

You want to know which of these options is beneficial for you.

You can use Excel function PV −

PV (rate, nper, pmt, [fv ], [type])

To calculate present value with payments at the end of each year, omit type or specify 0 for type.

To calculate present value with payments at the end of each year, specify 1 for type.

You will get the following results −

Therefore,

• If you make the payment now, you need to pay 32,000 of present value.
• If you opt for yearly payments with payment at the end of the year, you need to pay 28, 793 of present value.
• If you opt for yearly payments with payment at the end of the year, you need to pay 32,536 of present value.

You can clearly see that option 2 is beneficial for you.

What is EMI?

An Equated Monthly Installment (EMI) is defined by Investopedia as "A fixed payment amount made by a borrower to a lender at a specified date each calendar month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years, the loan is paid off in full."

EMI on a Loan

In Excel, you can calculate the EMI on a loan with the PMT function.

Suppose, you want to take a home loan of 5000000 with an annual interest rate of 11.5% and the term of the loan for 25 years. You can find your EMI as follows −

• Calculate interest rate per month (Interest Rate per Annum/12)
• Calculate number of monthly payments (No. of years * 12)
• Use PMT function to calculate EMI

As you observe,

• Present Value (PV) is the loan amount.
• Future Value (FV) is 0 as at the end of the term the loan amount should be 0.
• Type is 1 as the EMIs are paid at the beginning of each month.

You will get the following results −

Monthly Payment of Principal and Interest on a Loan

EMI includes both-interest and a part payment of principal. As the time increases, these two components of EMI will vary, reducing the balance.

To get

• The interest part of your monthly payments, you can use the Excel IPMT function.

• The payment of principal part of your monthly payments, you can use the Excel PPMT function.

For example, if you have taken a loan of 1,000,000 for a term of 8 months at the rate of 16% per annum. You can get values for the EMI, the decreasing interest amounts, the increasing payment of principal amounts and the diminishing loan balance over the 8 months. At the end of 8 months, loan balance will be 0.

Follow the procedure given below.

Step 1 − Calculate the EMI as follows.

This results in an EMI of Rs. 13261.59.

Step 2 − Next calculate the interest and principal parts of the EMI for the 8 months as shown below.

You will get the following results.

Interest and Principal paid between two Periods

You can compute the interest and principal paid between two periods, inclusive.

• Compute the cumulative interest paid between 2^nd and 3^rd months using the CUMIPMT function.

• Verify the result summing up the interest values for 2^nd and 3^rd months.

• Compute the cumulative principal paid between 2^nd and 3^rd months using the CUMPRINC function.

• Verify the result summing up the principal values for 2^nd and 3^rd months.

You will get the following results.

You can see that your calculations match with your verification results.

Calculating Interest Rate

Suppose you take a loan of 100,000 and you want to pay back in 15 months with a maximum monthly payment of 12000. You might want to know the interest rate at which you have to pay.

Find the interest rate with the Excel RATE function −

You will get the result as 8%.

Calculating Term of Loan

Suppose you take a loan of 100,000 at the interest rate 10%. You want a maximum monthly payment of 15,000. You might want to know how long it will take for you to clear the loan.

Find the number of payments with Excel NPER function

You will get the result as 12 months.

Decisions on Investments

When you want to make an investment, you compare the different options and choose the one that yields better returns. Net present value is useful in comparing cash flows over a period of time and deciding which one is better. The cash flows can occur at regular, periodical intervals or at irregular intervals.

First, we consider the case of regular, periodical cash flows.

The net present value of a sequence of cash flows received at different points in time in n years from now (n can be a fraction) is 1/(1 + r)ⁿ, where r is the annual interest rate.

Consider the following two investments over a period of 3 years.

At face value, Investment 1 looks better than Investment 2. However, you can decide on which investment is better only when you know the true worth of the investment as of today. You can use the NPV function to calculate the returns.

The cash flows can occur

• At the end of every year.
• At the beginning of every year.
• In the middle of every year.

NPV function assumes that the cash flows are at the end of the year. If the cash flows occur at different times then you have to take into account that particular factor along with the calculation with NPV.

Suppose the cash flows occur at the end of the year. Then you can straight away use the NPV function.

You will get the following results −

As you observe NPV for Investment 2 is higher than that for Investment 1. Hence, Investment 2 is a better choice. You got this result as cash out flows for Investment 2 are at later periods as compared to that of Investment 1.

Cash Flows at the Beginning of the Year

Suppose the cash flows occur at the beginning of every year. In such a case, you should not include the first cash flow in NPV calculation as it already represents the current value. You need to add the first cash flow to the NPV obtained from rest of the cash flows to get the net present value.

You will get the following results −

Cash Flows in the Middle of the Year

Suppose the cash flows occur in the middle of every year. In such a case, you need to multiply the NPV obtained from the cash flows by $\sqrt{1+r}$ to get the net present value.

You will get the following results −

Cash Flows at Irregular Intervals

If you want to calculate the net present value with irregular cash flows, i.e. cash flows occurring at random times, the calculation is a bit complex.

However, in Excel, you can easily do such a calculation with XNPV function.

• Arrange your data with the dates and the cash flows.

Note − The first date in your data should be the earliest of all the dates. The other dates can occur in any order.

• Use the XNPV function to calculate the net present value.

You will get the following results −

Suppose today’s date is 15^th March, 2015. As you observe, all the dates of cash flows are of later dates. If you want to find the net present value as of today, include it in the data at the top and specify 0 for the cash flow.

You will get the following results −

Internal Rate of Return (IRR)

Internal Rate of Return (IRR) of an investment is the rate of interest at which NPV is 0. It is the rate value for which the present values of the positive cash flows exactly compensate the negative ones. When the discount rate is the IRR, the investment is perfectly indifferent, i.e. the investor is neither gaining nor losing money.

Consider the following cash flows, different interest rates and the corresponding NPV values.

As you can observe between the values of interest rate 10% and 11%, the sign of NPV changes. When you fine-tune the interest rate to 10.53%, NPV is nearly 0. Hence, IRR is 10.53%.

Determining IRR of Cash Flows for a Project

You can calculate IRR of cash flows with Excel function IRR.

The IRR is 10.53% as you had seen in the previous section.

For the given cash flows, IRR may −

• exist and unique
• exist and multiple
• not exist

Unique IRR

If IRR exists and is unique, it can be used to choose the best investment among several possibilities.

• If the first cash flow is negative, it means the investor has the money and wants to invest. Then, the higher the IRR the better, since it represents the interest rate the investor is receiving.

• If the first cash flow is positive, it means the investor needs money and is looking for a loan, the lower the IRR the better since it represents the interest rate the investor is paying.

To find if an IRR is unique or not, vary the guess value and calculate IRR. If IRR remains constant then it is unique.

As you observe, the IRR has a unique value for the different guess values.

Multiple IRRs

In certain cases, you may have multiple IRRs. Consider the following cash flows. Calculate IRR with different guess values.

You will get the following results −

You can observe that there are two IRRs - -9.59% and 216.09%. You can verify these two IRRs calculating NPV.

For both -9.59% and 216.09%, NPV is 0.

No IRRs

In certain cases, you may not have IRR. Consider the following cash flows. Calculate IRR with different guess values.

You will get the result as #NUM for all the guess values.

The result #NUM means that there is no IRR for the cash flows considered.

Cash Flows Patterns and IRR

If there is only one sign change in the cash flows, such as from negative to positive or positive to negative, then a unique IRR is guaranteed. For example, in capital investments, the first cash flow will be negative, while the rest of the cash flows will be positive. In such cases, unique IRR exists.

If there is more than one sign change in the cash flows, IRR may not exist. Even if it exists, it may not be unique.

Decisions based on IRRs

Many analysts prefer to use IRR and it is a popular profitability measure because, as a percentage, it is easy to understand and easy to compare to the required return. However, there are certain problems while making decisions with IRR. If you rank with IRRs and make decisions based on these ranks, you may end up with wrong decisions.

You have already seen that NPV will enable you to make financial decisions. However, IRR and NPV will not always lead to the same decision when projects are mutually exclusive.

Mutually exclusive projects are those for which the selection of one project precludes the acceptance of another. When projects that are being compared are mutually exclusive, a ranking conflict may arise between NPV and IRR. If you have to choose between project A and project B, NPV may suggest acceptance of project A whereas IRR may suggest project B.

This type of conflict between NPV and IRR may arise because of one of the following reasons −

• The projects are of greatly different sizes, or
• The timing of the cash flows are different.

Projects of significant size difference

If you want to make a decision by IRR, project A yields a return of 100 and Project B a return of 50. Hence, investment on project A looks profitable. However, this is a wrong decision because of the difference in the scale of projects.

Consider −

• You have 1000 to invest.

• If you invest entire 1000 on project A, you get a return of 100.

• If you invest 100 on project B, you will still have 900 in your hand that you can invest on another project, say project C. Suppose you get a return of 20% on project C, then the total return on project B and project C is 230, which is way ahead in profitability.

Thus, NPV is a better way for decision making in such cases.

Projects with different cash flows timings

Again, if you consider IRR to decide, project B would be the choice. However, project A has a higher NPV and is an ideal choice.

IRR of Irregularly Spaced Cash Flows (XIRR)

Your cash flows may sometimes be irregularly spaced. In such a case, you cannot use IRR as IRR requires equally spaced time intervals. You can use XIRR instead, which takes into account the dates of the cash flows along with the cash flows.

The Internal Rate of Return that results in is 26.42%.

Modified IRR (MIRR)

Consider a case when your finance rate is different from your reinvestment rate. If you calculate Internal Rate of Return with IRR, it assumes same rate for both finance and reinvestment. Further, you might also get multiple IRRs.

For example, consider the cash flows given below −

As you observe, NPV is 0 more than once, resulting in multiple IRRs. Further, reinvestment rate is not taken into account. In such cases, you can use modified IRR (MIRR).

You will get a result of 7% as shown below −

Note − Unlike IRR, MIRR will always be unique.

In certain situations, you might have to

• Set up workbooks in which several worksheets have a similar format or structure.
• Get information for these worksheets from another worksheet.
• Summarize the results from these worksheets into a summary worksheet.

For example, you might have to track sales information region wise and month wise in separate worksheets. The price of each product is taken from the product catalog that is set up across the company in a separate worksheet. Finally, you have to summarize the results across all the regions into a summary sheet.

In this chapter, you will learn how you can accomplish this in easy steps. You are going to summarize results from April 2015 to March 2016, i.e. financial year 2015-16.

The First Step

The first step is to set up a product catalog. Follow the steps given below −

• Start with a blank workbook.
• Set up product catalog worksheet with products and prices.
• Name the worksheet Product Catalog.
• Assume the catalog is revised on the first of every month.
• Provide a place holder for last updated on.

The price of a product at selling time is determined by the current cost of the product.

Multiple Worksheets with same Structure

Next, you have to set up worksheets for the Regions – East, North, South, and West, in that order with same structure.

• Add 4 blank worksheets.
• Name the worksheets East, North, South and West.

These four worksheets should have the same structure.

• Click the tab East. The East worksheet opens.
• Press the shift key and click on the tab West. All the 4 tabs will be selected.

Now, whatever editing you do in the East worksheet will get automatically reflected in the other three selected worksheets.

In the East worksheet,

• Add the column headers – S. No., Month, Product, Price, No. of Units, Total Amount.
• Add the S. No., Month April and the 4 Product Names.
• Format the Table.

The same structure appears in the other worksheets North, South and West.

Creating a Formula across Multiple Worksheets

To create a formula across multiple worksheets −

• Define names for the price values of the products in the product catalog worksheet.
• Set the Scope as Workbook for all the names.

• Once again select all the four worksheets – East, North, South and West.
• In the East worksheet, for each product, in the price column, give the formula as the Price Value Name.

As we defined earlier, the price of a product is as per the product catalog that is updated on the first of every month.

• Repeat the same steps for each Month

Therefore, for the worksheets for the regions East, North, South, and West, you have successfully set the same structure and placed the price information for each product based on the month from product catalog worksheet.

The Product Catalog can be in another Workbook too.

Calculations in the Worksheets

The next step is to fill in the information of No. of Units sold for each Product in each Month and in each Region. Therefore, you need to work separately on these worksheets.

For each region, for each product −

• Fill No. of Units sold.
• Calculate the corresponding Total Amount as Price*No. of Units.

In each worksheet (East, North, South and West), calculate subtotals month-wise −

Note − You can use Subtotal on a single worksheet but not on multiple worksheets. Hence, you have to repeat this step for North, South and West worksheets.

Click the Outline Level 2. You will get all month-wise Totals.

Now, you are ready to summarize the results from all the four worksheets – East, North, South and West.

Summarizing Data in Multiple Worksheets

The following steps will show you how to summarize data from multiple worksheets.

• Add a worksheet and name it Summary.
• Create the structure for Summary worksheet.

In the column- Total Sales, in the cell C3, type =sum(

• Select the worksheet East.
• Select the cell G7.
• With East tab pressed, click the tab West.
• The tabs East to West get selected.
• The formula in the formula bar appears as

=sum(‘East:West’!G7)

Note that you are still in the East worksheet. Press Enter.

You will be in the Summary worksheet. In the formula bar, you will see the formula as

=SUM(East:West!G7)

The calculated value appears in the cell C3.

• Copy the formula to the cells C4 to C14.
• Click Show Formulas in the Formula Auditing group under the FORMULAS tab.

All the formulas in the column Total Sales appear.

This is exactly how you wanted to summarize the results from each region.

• Click in the cell C15.
• Type =sum(C3:C14)

Your summarized results are ready in the Summary worksheet.

You might want to check formulas for accuracy or find the source of an error. Excel Formula Auditing commands provide you an easy way to find

• Which cells are contributing in the calculation of a formula in the active cell.
• Which formulas are referring to the active cell.

These findings are shown graphically by arrow lines that makes the visualization easy. You can display all the formulas in the active worksheet with a single command. If your formulas refer to cells in a different workbook, open that workbook also. Excel cannot go to a cell in a workbook that is not open.

Setting the Display Options

You need to check whether the display options for the workbooks you are using are correctly set.

• Click FILE > Options.
• In the Excel Options dialog box, click Advanced.
• In Display options for the workbook −
  • Select the workbook.
  • Check that under For objects, show, All is selected.
• Repeat this step for all the workbooks you are auditing.

Tracing Precedents

Precedent cells are those cells that are referred to by a formula in the active cell.

In the following example, the active cell is C2. In C2, you have the formula =B2*C4.

B2 and C4 are precedent cells for C2.

To trace the precedents of the cell C2,

• Click in the cell C2.
• Click the Formulas tab.
• Click Trace Precedents in the Formula Auditing group.

Two arrows, one from B2 to C2 and another from C4 to C2 will be displayed, tracing the precedents.

Note that for tracing precedents of a cell, the cell should have a formula with valid references. Otherwise, you will get an error message.

• Click in a cell that does not contain a formula or click in an empty cell.
• Click Trace Precedents in the Formula Auditing group.

You will get a message.

Removing Arrows

Click Remove Arrows in the Formula Auditing group.

All the arrows in the worksheet will disappear.

Tracing Dependents

Dependent cells contain formulas that refer to other cells. That means, if the active cell contributes to a formula in another cell, the other cell is a dependent cell on the active cell.

In the example below, C2 has the formula =B2*C4. Therefore, C2 is a dependent cell on the cells B2 and C4

To trace the dependents of the cell B2,

• Click in the cell B2.
• Click the Formulas tab.
• Click Trace Dependents in the Formula Auditing group.

An arrow appears from B2 to C2, showing C2 is dependent on B2.

To trace the dependents of the cell C4 −

• Click in the cell C4.
• Click the Formula tab > Trace Dependents in the Formula Auditing group.

Another arrow appears from C4 to C2, showing C2 is dependent on C4 also.

Click Remove Arrows in the Formula Auditing group. All the arrows in the worksheet will disappear.

Note − For tracing dependents of a cell, the cell should be referenced by a formula in another cell. Otherwise, you will get an error message.

• Click in the cell B6 is not referenced by any formula or click in any empty cell.
• Click Trace Dependents in the Formula Auditing group. You will get a message.

Working with Formulae

You have understood the concept of Precedents and Dependents. Now, consider a worksheet with several formulae.

• Click in a cell under Pass Category in Exam Results table.
• Click Trace Precedents. The cell to its left (Marks) and the range E4:F8 will be mapped as the precedents.
• Repeat for all the cells under Pass Category in Exam Results table.

• Click in a cell under Pass Category in Student Grades table.

• Click Trace Dependents. All the cells under Pass Category in Exam Results table will be mapped as the dependents.

Showing Formulas

The worksheet below contains the summary of sales by the salespersons in the regions East, North, South, and West.

• Click the FORMULAS tab on the Ribbon.

• Click Show Formulas in the Formula Auditing group. The Formulas in the worksheet will appear, so that you will know which cells contain formulas and what the formulas are.

• Click in a cell under TotalSales.

• Click Trace Precedents. A worksheet icon appears at the end of the arrow. The worksheet icon indicates that the precedents are in a different worksheet.

Double-click on the arrow. A Go TO dialog box appears, showing the precedents.

As you observe, there are four precedents, on four different worksheets.

• Clique em uma referência de um dos precedentes.
• A referência aparece na caixa Referência.
• Clique OK. A planilha contendo esse precedente é exibida.

Avaliando uma Fórmula

Para descobrir como uma fórmula complexa em uma célula funciona passo a passo, você pode usar o comando Avaliar Fórmula.

Considere a fórmula NPV (Ano do Meio) na célula C14. A fórmula é

=SQRT (1 + C2)*C10

• Clique na célula C14.
• Clique na guia FÓRMULAS na Faixa de Opções.
• Clique em Avaliar Fórmula no grupo Auditoria de Fórmula. A caixa de diálogo Avaliar fórmula é exibida.

No Evaluate Formulacaixa de diálogo, a fórmula é exibida na caixa em Avaliação. Ao clicar noEvaluatebotão várias vezes, a fórmula é avaliada passo a passo. A expressão com um sublinhado sempre será executada a seguir.

Aqui, C2 está sublinhado na fórmula. Portanto, é avaliado na próxima etapa. CliqueEvaluate.

A célula C2 tem valor 0,2. Portanto, C2 será avaliado como 0,2.1+0.2está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

1 + 0,2 será avaliado como 1,2. SQRT(1.2)está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

SQRT (1.2) será avaliado como 1.09544511501033. C10está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

C10 será avaliado como 4976,8518518515.

1.09544511501033 * 4976.8518518515 está sublinhado, mostrando-o como a próxima etapa. CliqueEvaluate.

1.09544511501033 * 4976.8518518515 será avaliado como 5.451,87.

Não há mais expressões para avaliar e esta é a resposta. oEvaluate botão será alterado para Restart botão, indicando a conclusão da avaliação.

Verificação de erros

É uma boa prática fazer uma verificação de erro quando sua planilha e / ou pasta de trabalho estiver pronta com os cálculos.

Considere os seguintes cálculos simples.

O cálculo na célula resultou no erro # DIV / 0 !.

• Clique na célula C5.

• Clique na guia FÓRMULAS na Faixa de Opções.

• Clique na seta ao lado de Verificação de Erros no grupo Auditoria de Fórmula. Na lista suspensa, você encontrará queCircular References está desativado, indicando que sua planilha não possui referências circulares.

• Selecione Trace Error na lista suspensa.

As células necessárias para calcular a célula ativa são indicadas por setas azuis.

• Clique em Remover setas.
• Clique na seta ao lado de Verificação de erros.
• Selecione Verificação de erros na lista suspensa.

o Error Checking a caixa de diálogo aparece.

Observe o seguinte -

• Se você clicar Help on this error, A ajuda do Excel sobre o erro será exibida.

• Se você clicar Show Calculation Steps, A caixa de diálogo Avaliar fórmula é exibida.

• Se você clicar Ignore Error, a caixa de diálogo Verificação de erros fecha e se você clicar Error Checking comando novamente, ele ignora este erro.

• Se você clicar Edit in Formula Bar, você será direcionado para a fórmula na barra de fórmulas, para que possa editar a fórmula na célula.

Você pode usar Inquire para -

• Compare duas pastas de trabalho.
• Analise uma pasta de trabalho em busca de problemas ou inconsistências.
• Visualize os links entre as pastas de trabalho.
• Veja os links entre as planilhas.
• Visualize as relações entre as células.
• Limpe o excesso de formatação de células.
• Gerenciar senhas.

A guia INQUIRE estará na faixa de opções. Se você encontrar a guia INQUIRE na Faixa de Opções, você pode pular para a próxima seção.

Se você não encontrar a guia INQUIRE na Faixa de Opções, torne o Add-in Inquire ativo.

• Clique File > Options.
• Na janela Opções do Excel, clique em Add-Ins.
• Na caixa Gerenciar, clique em Suplementos de COM.
• Clique em Go.

A caixa de diálogo Suplementos COM é exibida.

• Marque a caixa Inquire.
• Clique OK. Agora, Inquire Add-In está ativo. Você encontrará a guia INQUIRE na Faixa de Opções.

Comandos INQUIRE

Vamos aprender sobre os comandos INQUIRE.

Clique na guia INQUIRE. Você encontrará os seguintes comandos -

• Análise da pasta de trabalho
• Relacionamento da pasta de trabalho
• Relacionamento da planilha
• Relacionamento Celular
• Comparar arquivos
• Limpar formatação de excesso de células
• Senhas da pasta de trabalho

Comparando duas pastas de trabalho

Você pode comparar duas pastas de trabalho célula por célula e encontrar as diferenças, se houver, em termos de alterações na segunda pasta de trabalho em comparação com a primeira.

Siga as etapas abaixo -

• Abra duas pastas de trabalho que você deseja comparar.
• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Comparar arquivos no grupo Comparar.

• o Select Files To Compare a caixa de diálogo aparece.
• Verifique os nomes dos arquivos exibidos nas caixas ao lado de Compare e To.

• Se algum nome de arquivo exibido não for o desejado, clique na seta para baixo ao lado desse nome de arquivo.

• Apenas as pastas de trabalho abertas serão exibidas.

• Selecione o arquivo.
• Verifique se a ordem dos Arquivos em Comparar e Para está correta.

• Se o pedido não estiver OK, clique Swap Files. A ordem dos arquivos em Compare e To é alterada.

• Clique em Comparar.

Os resultados da comparação aparecem em uma grade de dois painéis -

• A pasta de trabalho à esquerda corresponde ao arquivo "Comparar" que você escolheu.
• A pasta de trabalho à direita corresponde ao arquivo "Para" que você escolheu.

Os detalhes das alterações na pasta de trabalho-Para em comparação com a pasta de trabalho-Comparar aparecem em um painel abaixo dessas duas grades. As alterações são destacadas por cores, dependendo do tipo de alteração. A legenda para as cores de destaque aparece no painel esquerdo inferior.

Clique Resize Cells to Fitna faixa de opções para visualizar o conteúdo da célula nas pastas de trabalho Comparar e Para. As células em ambas as pastas de trabalho são redimensionadas para que o conteúdo fique visível.

Clique em Exportar resultados no grupo Exportar na faixa de opções.

A caixa de diálogo Salvar como é exibida. Você pode salvar os resultados em uma pasta de trabalho do Excel. Observe que apenas o tipo de arquivo .xlsx está disponível.

Se você precisar dos resultados em outro aplicativo, poderá fazê-lo copiando-o para a área de transferência.

Clique em Copiar resultados para a área de transferência no grupo Exportar na faixa de opções.

Cole no aplicativo que desejar.

Criação de um relatório interativo

Você pode usar o Workbook Analysis comando para criar um relatório interativo que pode mostrar informações detalhadas sobre a pasta de trabalho e sua estrutura, fórmulas, células, intervalos e avisos.

• Clique na guia Inquire na Faixa de Opções.
• Clique em Workbook Analysis no grupo Report.

O relatório será exibido após a conclusão da Análise da pasta de trabalho.

O relatório tem as seguintes seis categorias -

• Summary - Informações gerais sobre a estrutura e conteúdo da pasta de trabalho.

• Workbook (with subcategories) - Estatísticas gerais da pasta de trabalho.

• Formulas (with subcategories) - Informações específicas sobre fórmulas na pasta de trabalho.

• Cells (with subcategories) - Informações específicas sobre células na pasta de trabalho.

• Ranges (with subcategories) - Informações específicas sobre intervalos na pasta de trabalho.

• Warnings - Vários tipos de avisos sobre a estrutura e o conteúdo da pasta de trabalho.

Selecionar uma categoria fornece mais informações sobre essa categoria.

Marque a opção Fórmulas. As subcategorias de fórmulas serão exibidas.

Você observará o seguinte na pasta de trabalho que está analisando -

• Todas as fórmulas têm 224 números.
• Com valores numéricos, eles são 224 em número.
• Clique em Subcategoria com valores numéricos.

No painel Resultados, para cada uma das células com fórmula com valores numéricos, são exibidos o nome da planilha, o endereço da célula e a fórmula.

Clique no botão Exportar do Excel. A caixa de diálogo Salvar como é exibida.

• Salve o relatório como um arquivo Excel.
• O botão Carregar arquivo de exportação aparece próximo ao botão Exportar do Excel.
• Clique no botão Carregar arquivo de exportação.

A pasta de trabalho Report Excel salva é aberta e você pode visualizar claramente os resultados da análise da pasta de trabalho.

Visualizando com Diagramas

Você pode visualizar relacionamentos de pasta de trabalho, relacionamentos de planilha e relacionamentos de célula com diagramas interativos criados por links. Os links mostram as dependências entre os nós no diagrama. Você pode arrastar os links ou nós para organizá-los e alinhá-los para ver o que estiver procurando.

Visualizando relacionamentos da pasta de trabalho

Você pode ter um mapa gráfico interativo das dependências da pasta de trabalho criadas por conexões (links) entre arquivos usando o diagrama de relacionamento da pasta de trabalho.

Os tipos de links no diagrama podem incluir outras pastas de trabalho, bancos de dados do Access, arquivos de texto, páginas HTML, bancos de dados SQL Server e outras fontes de dados.

• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Relacionamento da Pasta de Trabalho no grupo Diagrama.

O diagrama de relacionamento da pasta de trabalho é exibido, mostrando os links da pasta de trabalho com diferentes fontes de dados.

Visualizando Relacionamentos da Planilha

Você pode usar o Diagrama de relacionamento da planilha para criar um mapa gráfico interativo de conexões (links) entre planilhas na mesma pasta de trabalho e / ou planilhas em outras pastas de trabalho.

• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Relacionamento da Planilha no grupo Diagrama.

O Diagrama de Relacionamento da Planilha é exibido, mostrando links entre as planilhas na mesma pasta de trabalho e em outras pastas de trabalho.

A diferença entre os dois pode ser identificada pela direção das setas.

Visualizando relacionamentos celulares

Você pode usar o Diagrama de Relacionamento de Célula para obter um mapa interativo e detalhado de todos os links de uma célula selecionada para células em outras planilhas ou mesmo em outras pastas de trabalho.

• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Relacionamento de Célula no grupo Diagrama.

A caixa de diálogo Cell Relationship Diagram Options é exibida.

• Verifique as planilhas e pastas de trabalho do Span.

• Selecione Rastrear em Precedentes de células de rastreamento e Dependentes de células de rastreamento.

• Em Número inicial de níveis de expansão, selecione limited e digite 5 na caixa ao lado dele.

• Clique OK.

O Diagrama de relacionamento da célula é exibido, mostrando links entre a célula selecionada e as células na mesma planilha, na mesma pasta de trabalho e em outras pastas de trabalho, com base nas opções que você escolheu.

Clique em Zoom. Você pode ver os nós claramente.

Limpeza de formatação de células em excesso

Quando você descobrir que uma pasta de trabalho está sendo carregada lentamente ou tornou-se enorme em tamanho, ela pode ter uma formatação aplicada a linhas e / ou colunas desnecessárias (por exemplo, formatação condicional de uma coluna inteira com menos de 15 valores).

Você pode usar o comando Limpar formatação de célula em excesso para remover o excesso de formatação e reduzir significativamente o tamanho do arquivo. Isso também melhora a velocidade do Excel.

Antes de limpar o excesso de formatação de células, crie uma cópia de backup de seu arquivo Excel porque há certos casos em que esse processo pode aumentar o tamanho do arquivo e não há como desfazer a alteração.

• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Limpar formatação de célula em excesso no grupo Diversos.

A caixa de diálogo Limpar formatação de célula em excesso é exibida. Escolha todas as planilhas noApply to caixa

Você receberá uma mensagem sobre como salvar as alterações. Clique OK.

Gerenciando senhas de arquivos

Se estiver usando os comandos Análise de pasta de trabalho ou Comparar arquivos para pastas de trabalho protegidas por senha, você poderá evitar a necessidade de digitar a senha sempre que esses arquivos forem abertos. Isso é possível usando o Gerenciador de Senhas.

• Clique na guia INQUIRE na Faixa de Opções.
• Clique em Senhas da pasta de trabalho no grupo Diversos.

A caixa de diálogo Gerenciador de Senhas é exibida. Clique no botão Adicionar para adicionar senhas de suas pastas de trabalho.

Adicione também descrições de senha para as senhas que você adicionou.

Da próxima vez que precisar usar qualquer um desses arquivos para comparar ou analisar, não será necessário digitar as senhas.

O Excel fornece vários comandos, funções e ferramentas que facilitam suas complexas tarefas de análise de dados. O Excel permite que você execute vários cálculos complexos com facilidade. Neste tutorial, você entenderá as ferramentas versáteis de análise de dados do Excel. Você entenderá a análise de dados com exemplos relevantes, instruções passo a passo e capturas de tela em cada etapa.

Consolidação de Dados

Você pode ter que consolidar os dados de várias fontes e apresentar um relatório. Os dados podem estar nas planilhas da mesma pasta de trabalho ou em pastas de trabalho diferentes. Com a ferramenta de dados do Excel Consolidate, você pode fazer isso em algumas etapas fáceis.

Análise de variações hipotéticas

A análise de variações hipotéticas fornece ferramentas para lidar com as seguintes situações de análise de dados -

• Encontre os valores de entrada que resultam em um valor especificado. O resultado pode ser configurado como uma fórmula com os valores de entrada como variáveis. Ao variar os valores das variáveis ​​de entrada, o Excel fornece a solução com a ferramenta Goal Seek.

• Encontre os valores de saída possíveis variando os valores de uma ou duas variáveis. O resultado pode ser configurado como uma fórmula com um ou dois valores de entrada como variáveis. Variando os valores das variáveis ​​de entrada, o Excel fornece a solução com a ferramenta Tabela de dados.

• Encontre os possíveis valores de saída que resultam da variação dos valores de mais de duas variáveis. O resultado pode ser configurado como uma fórmula com os valores de entrada como variáveis. Ao variar os valores das variáveis ​​de entrada, o Excel fornece a solução com a ferramenta Gerenciador de cenários.

Otimizando com o suplemento Excel Solver

O Solver é usado para lidar com situações complexas de busca de metas. Nesses casos, além das entradas e saídas, serão definidas restrições ou limites impostos aos possíveis valores de entrada. Além disso, o Solver é usado para resultar em uma solução ideal.

O Excel tem um suplemento Solver que ajuda a resolver esses problemas complexos.

Importando dados para o Excel

Sua análise de dados pode depender de várias fontes de dados externas. No Excel, você pode importar dados de diferentes fontes de dados, como banco de dados Microsoft Access, páginas da Web, arquivos de texto, tabela do SQL Server, cubo de análise do SQL Server, arquivo XML, etc.

Você pode importar qualquer número de tabelas de dados simultaneamente de um banco de dados. Quando você está importando várias tabelas de um banco de dados relacional, como o Access, os relacionamentos existentes entre as tabelas também serão retidos no Excel. Ao importar os dados, você também pode criar opcionalmente um relatório de Tabela Dinâmica ou Gráfico Dinâmico ou Power View com base nesses dados.

Você pode apenas criar uma conexão de dados com uma fonte de dados ou importar os dados para o Excel. Se você importar os dados para o Excel, as tabelas de dados serão adicionadas ao Modelo de Dados no Excel.

Modelo de dados

O modelo de dados no Excel é usado para integrar dados de várias tabelas na pasta de trabalho atual e / ou dos dados importados e / ou das fontes de dados conectadas à pasta de trabalho por meio de conexões de dados. O modelo de dados é usado de forma transparente em relatórios de Tabela Dinâmica, Gráfico Dinâmico, PowerPivot e Power View.

• Você pode criar um modelo de dados durante a importação de dados ou a partir das tabelas do Excel na pasta de trabalho.

• As tabelas de dados no Modelo de Dados podem ser visualizadas na Visualização de Dados ou Visualização de Diagrama.

• Com um modelo de dados, você pode criar relacionamentos entre as tabelas de dados.

• Você pode usar o comando Criar Relacionamento ou apenas clicar e arrastar e conectar os campos nas duas tabelas que definem o relacionamento na visualização do diagrama do Modelo de Dados.

Explorando Dados com Tabela Dinâmica

Como você pode integrar o Modelo de Dados a uma Tabela Dinâmica, pode fazer uma análise de dados extensa, agrupando, conectando, resumindo e relatando dados de várias fontes diferentes. Como você pode importar tabelas de fontes de dados externas e criar uma Tabela Dinâmica, é possível ter atualizações automáticas dos valores na Tabela Dinâmica sempre que os dados nas fontes de dados conectadas são atualizados.

Você pode criar uma Tabela Dinâmica com os campos de várias tabelas, desde que as tabelas tenham relacionamentos definidos. Se não existir um relacionamento, o Excel solicitará que você crie um e você pode fazer isso na própria Tabela Dinâmica. O relacionamento que você define é refletido no Modelo de Dados.

Explorando dados com PowerPivot

Você pode usar o PowerPivot para acessar, analisar e relatar dados de várias fontes de dados. O PowerPivot pode ajudá-lo a lidar com grandes dados com facilidade e produzir relatórios de análise fascinantes.

O PowerPivot fornece comandos para gerenciar o Modelo de Dados, adicionar tabelas do Excel ao Modelo de Dados, adicionar campos calculados nas Tabelas de Dados, definir KPIs, etc.

Explorando Dados com Power View

O Power View fornece exploração, visualização e análise interativas de grandes dados. Devido às suas opções de visualização versáteis, você pode definitivamente encontrar aquele que oferece aos seus dados a plataforma perfeita onde você pode explorar os dados, resumir e relatar.

Variando de tabelas a mapas, é apenas um jogo para você visualizar seus dados, filtrá-los, analisá-los e relatá-los interativamente. Além disso, você pode ter várias visualizações na mesma planilha do Power View que refletem e destacam valores, quando você clica em um ponto de dados em qualquer um deles.

Você pode explorar dados no Power View com uma tabela, uma matriz, um cartão, diferentes tipos de gráficos, múltiplos, mapas e blocos. Você ficará fascinado com a versatilidade dessas diferentes visualizações assim que tiver experiência prática. Isso ocorre porque é fácil produzir relatórios interativos destacando valores significativos e alternando dinamicamente entre as visualizações.

Explorando Dados com Hierarquias

Se seus dados tiverem hierarquias, eles podem ser definidos no Modelo de Dados que é refletido no Power View ou construir as hierarquias no próprio Power View.

Depois que uma hierarquia é definida, você pode fazer drill-up e drill-down na hierarquia, exibindo os dados necessários.

Relatórios de Power View Estético

Você pode chegar a um layout de relatório com base no que deseja apresentar no Power View. Você pode adicionar uma imagem de plano de fundo que reflita o logotipo de sua empresa ou sua visão corporativa. Opcionalmente, você pode formatar o plano de fundo do relatório para dar uma aparência elegante.

Você pode selecionar um tema para seu relatório que melhor retrate seus dados. Você pode alterar a fonte e o tamanho do texto para que seu relatório seja facilmente legível.

Indicadores-chave de desempenho (KPIs)

Os principais indicadores de desempenho são comumente usados ​​para avaliar o desempenho. No Excel, você define e retrata KPIs no PowerPivot ou Power View. A apresentação gráfica dos KPIs elevará seus relatórios.

Você pode ter se deparado com diferentes situações em que deve apresentar dados consolidados. A fonte dos dados pode ser de um ou vários lugares. Outro desafio pode ser que os dados possam ser atualizados por outras pessoas de tempos em tempos.

Você precisa saber como pode configurar uma planilha de resumo que consolida os dados das fontes que você configurou, sempre que quiser. No Excel, você pode realizar essa tarefa facilmente em algumas etapas com oData Tool – Consolidate.

Preparando Dados para Consolidação

Antes de começar a consolidar os dados, certifique-se de que haja consistência nas fontes de dados. Isso significa que os dados são organizados da seguinte forma -

• Cada intervalo de dados está em uma planilha separada.

• Cada intervalo de dados está em formato de lista, com rótulos na primeira linha.

• Além disso, você pode ter rótulos para as categorias, se aplicável, na primeira coluna.

• Todos os intervalos de dados têm o mesmo layout.

• Todos os intervalos de dados contêm fatos semelhantes.

• Não há linhas ou colunas em branco em cada intervalo.

Caso as fontes de dados sejam externas, garanta o uso de um layout predefinido na forma de um modelo Excel.

Suponha que você tenha os dados de vendas de várias mercadorias de cada uma das regiões - Leste, Norte, Sul e Oeste. Você pode precisar consolidar esses dados e apresentar um resumo das vendas do produto de vez em quando. A preparação inclui o seguinte -

• Uma planilha por região - ou seja, quatro planilhas com os nomes Leste, Norte, Sul e Oeste. Eles podem estar na mesma pasta de trabalho ou em pastas de trabalho diferentes.

• Cada planilha possui o mesmo layout, representando os detalhes do produto, número de unidades e quantidade.

• Você precisa consolidar o produto de dados sábio. Portanto, certifique-se de que a coluna com o rótulo Produto seja a primeira coluna e contenha os rótulos do Produto.

Consolidando dados na mesma pasta de trabalho

Se você tiver todos os dados, que deve consolidar, na mesma pasta de trabalho, proceda da seguinte forma -

Step 1 - Certifique-se de que os dados de cada região estejam em uma planilha separada.

Step 2 - Adicione uma nova planilha e nomeie-a Resumo.

Step 3 - Clique na planilha de Resumo.

Step 4 - Clique na célula onde deseja colocar os resultados do resumo.

Step 5 - Clique no DATA guia na faixa de opções.

Step 6 - Clique no Consolidate botão no Data Tools grupo.

o Consolidate a caixa de diálogo aparece.

Step 7 - Selecione Sum da lista suspensa em Function.

Step 8 - Selecione os dados de cada planilha como segue.

• Clique no ícone na caixa em Referência.
• Selecione a planilha - Leste.
• Selecione o intervalo de dados.
• Novamente, clique no ícone na caixa em Referência.

O intervalo selecionado aparece na caixa Referência -

Step 9 - Clique no Addbotão à direita da caixa. O intervalo de dados selecionado aparece na caixa sobAll References.

Step 10- Repita as etapas 1 a 5 para o restante das planilhas de dados - Norte, Sul e Oeste. A caixa de diálogo Consolidar tem a seguinte aparência.

Você pode ver que os intervalos de dados aparecem na planilha em ordem alfabética, na caixa sob All references.

Step 11 - Marque as caixas Top row e Left column debaixo Use labels in. Clique OK.

Seus dados são resumidos em termos de produto para as regiões - Leste, Norte, Sul e Oeste.

Você pode repetir as etapas fornecidas acima para atualizar os resultados do resumo manualmente, sempre que precisar.

Consolidando dados automaticamente

Suponha que você queira que sua folha de resumo seja atualizada automaticamente, sempre que houver alterações nos dados. Para fazer isso, você precisa ter links para os dados de origem.

Step 1 - Marque a caixa - Create links to source data na caixa de diálogo Consolidar e clique em OK.

Seus resultados resumidos aparecem com um esboço da seguinte forma -

Você observará que uma nova coluna é inserida à direita da coluna denominada Produto.

Step 2- Clique no sinal + no contorno da linha que contém o valor do Produto denominado Soap. Você pode ver que a nova coluna contém o valor consolidado para cada conjunto de valores de produto, por região.

Consolidando dados de diferentes pastas de trabalho

No exemplo anterior, todos os dados que você precisa resumir estão na mesma pasta de trabalho. No entanto, é provável que os dados sejam mantidos separadamente para cada região e sejam atualizados por região. Nesse caso, você pode consolidar os dados da seguinte forma -

Step 1 - Abra as pastas de trabalho contendo os dados, digamos, pastas de trabalho - East-Sales, North-Sales, South-Sales e West-Sales.

Step 2 - Abra uma nova pasta de trabalho.

Step 3 - Em uma nova planilha, clique na célula onde deseja que o resumo apareça.

Step 4 - Clique na guia DADOS na faixa de opções.

Step 5 - Clique em Consolidar na caixa Ferramentas de dados.

UMA Consolidatea caixa de diálogo aparece. Na caixa de diálogo Consolidar -

• Selecione Soma na lista suspensa na caixa sob Função.
• Clique no ícone na caixa sob Reference.
• Selecione a pasta de trabalho - East-Sales.xlsx.
• Selecione o intervalo de dados.
• Novamente, clique no ícone na caixa em Referência.
• Clique no Add botão para a direita.

A caixa de diálogo Consolidar tem a seguinte aparência -

• Clique no ícone à direita da caixa em Referências.
• Selecione a pasta de trabalho - North-Sales.xlsx.
• Selecione o intervalo de dados.
• Novamente, clique no ícone à direita da caixa em Referências.
• Clique em Adicionar.

Step 6 - Repita as etapas 1–6 para adicionar os intervalos de dados das pastas de trabalho - South-Sales.xlsx e West-Sales.xlsx.

Step 7 - Sob Use labels in, marque as seguintes caixas.

• Linha superior.
• Coluna esquerda.

Step 8 - Marque a caixa Create links to source data.

Sua caixa de diálogo Consolidar é a seguinte -

Seus dados estão resumidos em sua pasta de trabalho.

What-if analysisé o processo de alterar os valores nas células para ver como essas alterações afetarão o resultado das fórmulas na planilha. Você pode usar vários conjuntos diferentes de valores em uma ou mais fórmulas para explorar todos os vários resultados.

A análise de variações hipotéticas é útil em muitas situações durante a análise de dados. Por exemplo -

• Você pode propor orçamentos diferentes com base na receita.

• Você pode prever os valores futuros com base nos valores históricos fornecidos.

• Se você espera um determinado valor como resultado de uma fórmula, pode encontrar diferentes conjuntos de valores de entrada que produzem o resultado desejado.

O Excel fornece as seguintes ferramentas de análise de variações hipotéticas que podem ser usadas com base em suas necessidades de análise de dados -

• Tabelas de dados
• Gerente de Cenário
• Goal Seek

As tabelas e cenários de dados pegam conjuntos de valores de entrada e projetam para determinar os resultados possíveis. A busca de metas difere de tabelas de dados e cenários porque pega um resultado e projeta para trás para determinar os possíveis valores de entrada que produzem esse resultado.

Neste capítulo, você entenderá as possíveis situações em que pode usar as ferramentas de análise Whatif. Para obter detalhes sobre o uso dessas ferramentas, consulte os capítulos posteriores deste tutorial.

Tabelas de dados

UMA Data Tableé um intervalo de células em que você pode alterar os valores em algumas das células e encontrar diferentes respostas para um problema. Por exemplo, você pode querer saber quanto empréstimo pode pagar por uma casa, analisando diferentes valores de empréstimo e taxas de juros. Você pode colocar esses valores diferentes junto com oPMT função em uma tabela de dados e obter o resultado desejado.

Uma tabela de dados funciona apenas com one or two variables, mas pode aceitar muitos valores diferentes para essas variáveis.

Para obter detalhes sobre tabelas de dados, consulte o capítulo - Análise de variações hipotéticas com tabelas de dados neste tutorial.

Gerente de Cenário

Um cenário é um conjunto de valores que o Excel salva e pode substituir automaticamente nas células de uma planilha.

Os principais recursos são -

• Você pode criar e salvar diferentes grupos de valores em uma planilha e, em seguida, alternar para qualquer um desses novos cenários para visualizar resultados diferentes.

• Um cenário pode ter várias variáveis, mas pode acomodar apenas até 32 valores.

• Você também pode criar um relatório de resumo do cenário, que combina todos os cenários em uma planilha. Por exemplo, você pode criar vários cenários de orçamento diferentes que comparam vários níveis de receita e despesas possíveis e, em seguida, criar um relatório que permite comparar os cenários lado a lado.

• O Gerenciador de cenários é uma caixa de diálogo que permite salvar os valores como um cenário e nomear o cenário.

Para obter detalhes sobre os cenários, consulte o capítulo - Análise de variações hipotéticas com o Gerenciador de cenários neste tutorial.

Goal Seek

Atingir meta é útil se você sabe o resultado que deseja de uma fórmula, mas não tem certeza de qual valor de entrada a fórmula precisa para obter esse resultado. Por exemplo, se você deseja tomar um empréstimo e sabe o valor do empréstimo, o prazo do empréstimo e o EMI que pode pagar, você pode usar o Goal Seek para encontrar a taxa de juros pela qual pode aproveitar o empréstimo.

Goal Seek pode ser usado apenas com um valor de entrada variável. Se você tiver mais de uma variável para valores de entrada, poderá usar o suplemento Solver.

Para obter detalhes sobre o uso de Atingir Meta, consulte o capítulo - Análise de variações hipotéticas com a busca de meta neste tutorial.

Solver

O Solver vem com o Excel como um suplemento. Você pode usar o Solver para encontrar um valor ideal para uma fórmula em uma célula chamada célula de destino em uma planilha.

O Solver trabalha com um grupo de células relacionadas à fórmula na célula-alvo. O Solver ajusta os valores nas células ajustáveis ​​que você especifica, para produzir o resultado que você especifica, a partir da fórmula da célula de destino.

Para obter os detalhes sobre o uso do suplemento Excel Solver, consulte o capítulo - Otimização com o Excel Solver neste tutorial.

Com uma tabela de dados no Excel, você pode facilmente variar uma ou duas entradas e realizar análises de variações hipotéticas. Uma Tabela de dados é um intervalo de células em que você pode alterar os valores em algumas das células e chegar a diferentes respostas para um problema.

Existem dois tipos de tabelas de dados -

• Tabelas de dados de uma variável
• Tabelas de dados de duas variáveis

Se você tiver mais de duas variáveis ​​em seu problema de análise, será necessário usar a ferramenta Scenario Manager do Excel. Para obter detalhes, consulte o capítulo - Análise de variações hipotéticas com o Scenario Manager neste tutorial.

Tabelas de dados de uma variável

Uma tabela de dados de uma variável pode ser usada se você quiser ver como diferentes valores de uma variável em uma ou mais fórmulas irão alterar os resultados dessas fórmulas. Em outras palavras, com uma tabela de dados de uma variável, você pode determinar como alterar uma entrada altera qualquer número de saídas. Você entenderá isso com a ajuda de um exemplo.

Example

Há um empréstimo de 5.000.000 por um mandato de 30 anos. Você deseja saber os pagamentos mensais (EMI) para taxas de juros variadas. Você também pode estar interessado em saber o valor dos juros e do principal que é pago no segundo ano.

Análise com tabela de dados de uma variável

A análise com a tabela de dados de uma variável deve ser feita em três etapas -

Step 1 - Defina o fundo necessário.

Step 2 - Crie a tabela de dados.

Step 3 - Realize a análise.

Vamos entender essas etapas em detalhes -

Etapa 1: definir o plano de fundo necessário

• Suponha que a taxa de juros seja de 12%.

• Liste todos os valores necessários.

• Nomeie as células que contêm os valores, para que as fórmulas tenham nomes em vez de referências de células.

• Defina os cálculos para EMI, Juros cumulativos e Principal cumulativo com as funções do Excel - PMT, CUMIPMT e CUMPRINC respectivamente.

Sua planilha deve ter a seguinte aparência -

Você pode ver que as células na coluna C são nomeadas conforme as células correspondentes na coluna D.

Etapa 2: criar a tabela de dados

• Digite a lista de valores, ou seja, taxas de juros que deseja substituir na célula de entrada da coluna E da seguinte forma -

Como você pode observar, há uma linha vazia acima dos valores da taxa de juros. Esta linha é para as fórmulas que você deseja usar.

• Digite a primeira função (PMT) na célula uma linha acima e uma célula à direita da coluna de valores. Digite as outras funções (CUMIPMT and CUMPRINC) nas células à direita da primeira função.

  Agora, as duas linhas acima dos valores da taxa de juros são as seguintes -

A Tabela de Dados se parece com a seguinte -

Etapa 3: faça a análise com a ferramenta Tabela de dados de análise de variações hipotéticas

• Selecione o intervalo de células que contém as fórmulas e os valores que deseja substituir, ou seja, selecione o intervalo - E2: H13.

• Clique na guia DADOS na faixa de opções.

• Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.

• Selecione Tabela de dados na lista suspensa.

Data Table a caixa de diálogo aparece.

• Clique no ícone na caixa da célula de entrada da coluna.
• Clique na célula Interest_Rate, que é C2.

Você pode ver que a célula de entrada Coluna é considerada $ C $ 2. Clique OK.

A tabela de dados é preenchida com os resultados calculados para cada um dos valores de entrada, conforme mostrado abaixo -

Se você pode pagar um EMI de 54.000, pode observar que a taxa de juros de 12,6% é adequada para você.

Tabelas de dados de duas variáveis

Uma tabela de dados de duas variáveis ​​pode ser usada se você quiser ver como valores diferentes de duas variáveis ​​em uma fórmula irão alterar os resultados dessa fórmula. Em outras palavras, com uma tabela de dados de duas variáveis, você pode determinar como alterar duas entradas altera uma única saída. Você entenderá isso com a ajuda de um exemplo.

Example

Existe um empréstimo de 50.000.000. Você deseja saber como as diferentes combinações de taxas de juros e prazos de empréstimos afetarão o pagamento mensal (EMI).

Análise com tabela de dados de duas variáveis

A análise com a tabela de dados de duas variáveis ​​deve ser feita em três etapas -

Step 1 - Defina o fundo necessário.

Step 2 - Crie a tabela de dados.

Step 3 - Realize a análise.

Etapa 1: definir o plano de fundo necessário

• Suponha que a taxa de juros seja de 12%.

• Liste todos os valores necessários.

• Nomeie as células que contêm os valores, para que a fórmula tenha nomes em vez de referências de células.

• Defina o cálculo para EMI com a função Excel - PMT.

Sua planilha deve ter a seguinte aparência -

Você pode ver que as células na coluna C são nomeadas conforme as células correspondentes na coluna D.

Etapa 2: criar a tabela de dados

• Tipo =EMI na célula F2.

• Digite a primeira lista de valores de entrada, ou seja, taxas de juros na coluna F, começando com a célula abaixo da fórmula, ou seja, F3.

• Digite a segunda lista de valores de entrada, ou seja, número de pagamentos na linha 2, começando com a célula à direita da fórmula, ou seja, G2.

  A tabela de dados tem a seguinte aparência -

Faça a análise com a Tabela de dados da ferramenta de análise de variações hipotéticas

• Selecione o intervalo de células que contém a fórmula e os dois conjuntos de valores que deseja substituir, ou seja, selecione o intervalo - F2: L13.

• Clique na guia DADOS na faixa de opções.

• Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.

• Selecione Tabela de dados na lista suspensa.

A caixa de diálogo Tabela de dados é exibida.

• Clique no ícone na caixa da célula de entrada de linha.
• Clique na célula NPER, que é C3.
• Novamente, clique no ícone na caixa da célula de entrada de linha.
• Em seguida, clique no ícone na caixa da célula de entrada da coluna.
• Clique na célula Interest_Rate, que é C2.
• Novamente, clique no ícone na caixa da célula de entrada da coluna.

Você verá que a célula de entrada Linha é considerada $ C $ 3 e a célula de entrada Coluna é considerada $ C $ 2. Clique OK.

A tabela de dados é preenchida com os resultados calculados para cada combinação dos dois valores de entrada -

Se você pode pagar um EMI de 54.000, a taxa de juros de 12,2% e 288 EMIs são adequados para você. Isso significa que o prazo do empréstimo seria de 24 anos.

Cálculos da tabela de dados

As Tabelas de Dados são recalculadas cada vez que a planilha que as contém é recalculada, mesmo que não tenham sido alteradas. Para acelerar os cálculos em uma planilha que contém uma Tabela de Dados, você precisa alterar as opções de cálculo paraAutomatically Recalculate a planilha, mas não as Tabelas de dados, conforme fornecido na próxima seção.

Acelerando os cálculos em uma planilha

Você pode acelerar os cálculos em uma planilha contendo Tabelas de Dados de duas maneiras -

• Nas opções do Excel.
• Da faixa de opções.

Das opções do Excel

• Clique na guia ARQUIVO na faixa de opções.
• Selecione Opções na lista do painel esquerdo.

A caixa de diálogo Opções do Excel é exibida.

• No painel esquerdo, selecione Formulas.

• Selecione a opção Automatic except for data tables debaixo Workbook Calculationna seção Opções de cálculo. Clique OK.

Da fita

• Clique na guia FÓRMULAS na Faixa de Opções.

• Clique no Calculation Options no grupo Cálculos.

• Selecione Automatic Except for Data Tables na lista suspensa.

O Scenario Manager é útil nos casos em que você tem mais de duas variáveis ​​na análise de sensibilidade. O Scenario Manager cria cenários para cada conjunto de valores de entrada para as variáveis ​​em consideração. Os cenários ajudam você a explorar um conjunto de resultados possíveis, apoiando o seguinte -

• Variando até 32 conjuntos de entrada.
• Mesclar os cenários de várias planilhas ou pastas de trabalho diferentes.

Se você deseja analisar mais de 32 conjuntos de entrada e os valores representam apenas uma ou duas variáveis, você pode usar as tabelas de dados. Embora seja limitada a apenas uma ou duas variáveis, uma Tabela de dados pode incluir quantos valores de entrada diferentes você desejar. Consulte Análise de variações hipotéticas com tabelas de dados neste tutorial.

Cenários

Um cenário é um conjunto de valores que o Excel salva e pode substituir automaticamente em sua planilha. Você pode criar e salvar diferentes grupos de valores como cenários em uma planilha e, em seguida, alternar entre esses cenários para visualizar os diferentes resultados.

Por exemplo, você pode ter vários cenários de orçamento diferentes que comparam vários níveis de receita e despesas possíveis. Você também pode ter cenários de empréstimo diferentes de fontes diferentes que comparam várias taxas de juros possíveis e prazos de empréstimo.

Se as informações que deseja usar nos cenários forem de fontes diferentes, você pode coletar as informações em pastas de trabalho separadas e, em seguida, mesclar os cenários das diferentes pastas de trabalho em uma.

Depois de ter todos os cenários de que precisa, você pode criar um relatório de resumo do cenário -

• Isso incorpora informações de todos os cenários.
• Isso permite comparar os cenários lado a lado.

Gerente de Cenário

O Scenario Manager é uma das ferramentas de análise de variações hipotéticas do Excel.

Para criar um relatório de análise com o Scenario Manager, você deve seguir estas etapas -

Step 1 - Defina o conjunto de valores iniciais e identifique as células de entrada que você deseja variar, chamadas de células em mudança.

Step 2 - Crie cada cenário, nomeie o cenário e insira o valor para cada célula de entrada em mudança para esse cenário.

Step 3- Selecione as células de saída, chamadas de células de resultado que você deseja rastrear. Essas células contêm fórmulas no conjunto inicial de valores. As fórmulas usam as células de entrada variáveis.

O Scenario Manager cria um relatório contendo os valores de entrada e saída para cada cenário.

Valores iniciais para cenários

Antes de criar vários cenários diferentes, você precisa definir um conjunto de valores iniciais nos quais os cenários serão baseados.

As etapas para configurar os valores iniciais para os cenários são:

• Defina as células que contêm os valores de entrada.
• Nomeie as células de entrada apropriadamente.
• Identifique as células de entrada com valores constantes.
• Especifique os valores para as entradas constantes.
• Identifique as células de entrada com valores variáveis.
• Especifique os valores iniciais para as entradas de mudança.
• Defina as células que contêm os resultados. As células de resultado contêm fórmulas.
• Nomeie as células de resultado apropriadamente.
• Coloque as fórmulas nas células de resultado.

Considere o exemplo anterior de empréstimo. Agora, proceda da seguinte forma -

• Defina uma célula para o valor do empréstimo.

  • Este valor de entrada é constante para todos os cenários.

  • Nomeie a célula Loan_Amount.

  • Especifique o valor como 5.000.000.

• Defina as células para Taxa de juros, Nº de pagamentos e Tipo (Pagamento no início ou no final do mês).

  • Esses valores de entrada mudarão nos cenários.

  • Nomeie as células Interest_Rate, NPER e Type.

  • Especifique os valores iniciais para a análise nessas células como 12%, 360 e 0, respectivamente.

• Defina a célula para o EMI.

  • Este é o valor do resultado.

  • Nomeie a célula EMI.

  • Coloque a fórmula nesta célula como -

    =PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)

Sua planilha se parece com a mostrada abaixo -

Como você pode ver, as células de entrada e as células de resultado estão na coluna C com os nomes fornecidos na coluna D.

Criação de cenários

Depois de configurar os valores iniciais para os cenários, você pode criar os cenários usando o Gerenciador de cenários da seguinte maneira:

• Clique na guia DADOS na faixa de opções.
• Clique em Análise de variações hipotéticas no grupo Ferramentas de dados.
• Selecione Scenario Manager na lista suspensa.

A caixa de diálogo Gerenciador de cenários é exibida. Você pode observar que ele contém uma mensagem -

“No Scenarios defined. Choose Add to.”

Você precisa criar cenários para cada conjunto de valores variáveis ​​no Scenario Manager. É bom ter o primeiro cenário definido com valores iniciais, pois permite que você volte aos valores iniciais sempre que quiser, enquanto exibe diferentes cenários.

Crie o primeiro cenário com os valores iniciais da seguinte forma -

• Clique no Add botão na caixa de diálogo Gerenciador de cenários.

o Add Scenario a caixa de diálogo aparece.

• Em Nome do cenário, digite Cenário 1.
• Em Changing Cells, insira as referências para as células, ou seja, C3, C4 e C5 com a tecla Ctrl pressionada.

O nome da caixa de diálogo muda para Editar cenário.

• Edite o texto no Comment as – Initial Values caixa.

• Selecione a opção Impedir alterações em Proteção e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais que você definiu aparecem em cada uma das caixas de células em mudança.

Scenario 1 com os valores iniciais é criado.

Crie mais três cenários com valores variáveis ​​nas células em mudança, como segue -

• Clique no Add botão na caixa de diálogo Valores do cenário.

A caixa de diálogo Adicionar cenário é exibida. Observe que C3, C4, C5 aparecem na caixa Changing cells.

• Na caixa Nome do cenário, digite Cenário 2.

• Edite o texto no Comment as - Taxa de juros diferente.

• Selecione Prevent changes em Protection e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais aparecem nas células em mudança. Altere o valor deInterest_Rate para 0.13 e clique Add.

o Add Scenarioa caixa de diálogo aparece. Observe que C3, C4, C5 aparecem na caixa sob a alteração de células.

• Na caixa Nome do cenário, digite Cenário 3.

• Edite o texto no Commentcaixa como - Diferente não. de Pagamentos.

• Selecione Prevent changes em Protection e clique em OK.

A caixa de diálogo Valores do cenário é exibida. Os valores iniciais aparecem nas células em mudança. Altere o valor de NPER para 300 e cliqueAdd.

o Add Scenarioa caixa de diálogo aparece. Observe que C3, C4, C5 aparecem na caixa Changing cells.

• Na caixa Nome do cenário, digite Cenário 4.

• Edite o texto no Comment caixa como - Diferentes tipos de pagamento.

• Selecione Prevent changes em Protection e clique em OK.

o Scenario Valuesa caixa de diálogo aparece. Os valores iniciais aparecem nas células em mudança. Altere o valor de Tipo para 1. Clique em OK ao adicionar todos os cenários que deseja adicionar.

o Scenario Managera caixa de diálogo aparece. Na caixa sob Cenários, você encontrará os nomes de todos os cenários que criou.

• Clique em Cenário 1. Como você sabe, o Cenário 1 contém os valores iniciais.


Agora clique Summary. A caixa de diálogo Resumo do cenário é exibida.

Relatórios de resumo de cenário

O Excel fornece dois tipos de relatórios de resumo do cenário -

• Resumo do cenário.
• Relatório de tabela dinâmica de cenário.

Na caixa de diálogo Resumo do cenário, você pode encontrar esses dois tipos de relatório.

Selecione Resumo do cenário em Tipo de relatório.

Resumo do Cenário

No Result cells caixa, selecione a célula C6 (Aqui, colocamos o PMTfunção). Clique OK.

O relatório de resumo do cenário aparece em uma nova planilha. A planilha é nomeada como Resumo do Cenário.

Você pode observar o seguinte no relatório de resumo do cenário -

• Changing Cells- Lista todas as células usadas como células em mudança. Como você nomeou as células, Interest_Rate, NPER e Type, elas parecem tornar o relatório significativo. Caso contrário, apenas referências de células serão listadas.

• Result Cells - Exibe a célula de resultado especificada, ou seja, EMI.

• Current Values - É a primeira coluna e lista os valores desse cenário selecionado na caixa de diálogo Gerenciador de cenários antes de criar o relatório de resumo.

• Para todos os cenários que você criou, as células em mudança serão destacadas em cinza.

• Na linha EMI, os valores dos resultados para cada cenário serão exibidos.

Você pode tornar o relatório mais significativo exibindo os comentários que adicionou ao criar os cenários.

• Clique no botão + à esquerda da linha que contém os nomes dos cenários. Os comentários para os cenários aparecem na linha sob os nomes dos cenários.

Cenários de diferentes fontes

Suponha que você obtenha os cenários de três fontes diferentes e precise preparar o relatório de resumo do cenário em uma pasta de trabalho mestre. Você pode fazer isso mesclando os cenários de pastas de trabalho diferentes na pasta de trabalho Mestre. Siga as etapas abaixo -

• Suponha que os cenários estejam nas pastas de trabalho, Bank1_Scenarios, Bank2_Scenarios e Bank3_Scenarios. Abra as três pastas de trabalho.

• Abra a pasta de trabalho Mestre, na qual você tem os valores iniciais.

• Clique em DADOS> Análise de variações hipotéticas> Gerenciador de cenários na pasta de trabalho mestre.

o Scenario Manager A caixa de diálogo é exibida.

Como você pode observar, não há cenários porque você ainda não adicionou nenhum. CliqueMerge.

A caixa de diálogo Mesclar cenários é exibida.

Como você pode ver, em Mesclar cenários de, você tem duas caixas -

• Book
• Sheet

Você pode selecionar uma planilha específica de uma pasta de trabalho específica que contém os cenários que deseja adicionar aos resultados. Clique na seta suspensa deBook para ver as pastas de trabalho.

Note - As pastas de trabalho correspondentes devem ser abertas para aparecer nesta lista.

Selecione o livro - Bank1_Scenarios.

A folha do banco 1 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem é exibido. Clique OK.

A caixa de diálogo Gerenciador de cenários é exibida. Os dois cenários que foram mesclados na pasta de trabalho Mestre serão listados em Cenários.

Clique no Mergebotão. oMerge Scenariosa caixa de diálogo aparece. Agora, selecioneBank2_Scenarios na lista suspensa na caixa Livro.

A folha do banco2 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem é exibido. Clique OK.

o Scenario ManagerA caixa de diálogo é exibida. Os quatro cenários que foram mesclados na pasta de trabalho Mestre estão listados em Cenários.

Clique no Mergebotão. oMerge Scenariosa caixa de diálogo aparece. Agora, selecioneBank3_Scenarios na lista suspensa na caixa Livro.

A folha do banco3 é exibida. Na parte inferior da caixa de diálogo, o número de cenários encontrados na planilha de origem será exibido. Clique OK.

A caixa de diálogo Gerenciador de cenários é exibida. Os cinco cenários que foram mesclados na pasta de trabalho Mestre serão listados em Cenários.

Agora, você tem todos os cenários necessários para produzir o relatório de resumo do cenário.

Clique no botão Resumo. oScenario Summary a caixa de diálogo aparece.

• Selecione Resumo do cenário.
• Na caixa Células de resultado, digite C6 e clique em OK.

O relatório de resumo do cenário aparece em uma nova planilha na pasta de trabalho Mestre.

Exibindo cenários

Suponha que você esteja apresentando seus cenários e gostaria de alternar dinamicamente de um cenário para outro e exibir o conjunto de valores de entrada e valores de resultado do cenário correspondente.

• Clique em DADOS> Análise de variações hipotéticas> Gerenciador de cenários no grupo Ferramentas de dados. A caixa de diálogo Gerenciador de cenários é exibida. A lista de cenários é exibida.

• Selecione o cenário que deseja exibir. CliqueShow.

Os valores da planilha são atualizados de acordo com o cenário selecionado. Os valores do resultado são recalculados.

Relatório de tabela dinâmica de cenário

Você também pode ver o relatório de cenário na forma de uma tabela dinâmica.

• Clique no botão Resumo no Scenario ManagerCaixa de diálogo. A caixa de diálogo Resumo do cenário é exibida.

• Selecione os Scenario PivotTable report em Tipo de relatório.

• Digite C6 no Result cells caixa.

O relatório de tabela dinâmica do cenário aparece em uma nova planilha.

Atingir meta é uma ferramenta de análise de variações hipotéticas que ajuda a encontrar o valor de entrada que resulta em um valor de destino que você deseja. Goal Seekrequer uma fórmula que usa o valor de entrada para dar resultado no valor de destino. Então, variando o valor de entrada na fórmula, o Goal Seek tenta chegar a uma solução para o valor de entrada.

Goal Seek funciona apenas com um valor de entrada variável. Se você tiver mais de um valor de entrada a ser determinado, será necessário usar o suplemento Solver. Consulte o capítulo - Otimização com o Excel Solver neste tutorial.

Análise com busca de metas

Suponha que você queira tomar um empréstimo de 5.000.000 e quitá-lo em 25 anos. Você pode pagar um EMI de 50000. Você quer saber a que taxa de juros pode pedir o empréstimo.

Você pode usar Goal Seek para encontrar a taxa de juros pela qual você pode pedir o empréstimo da seguinte forma -

Step 1 - Configure as células do Excel para Atingir Meta, conforme mostrado abaixo.

Step 2- Insira os valores na coluna C correspondentes à coluna D. A célula Interest_Rate é mantida vazia, pois você deve recuperar esse valor. Além disso, embora você saiba o EMI que pode pagar (50000), esse valor não está incluído, pois você precisa usar a função PMT do Excel para chegar a ele. O Goal Seek requer uma fórmula para encontrar o resultado. A função PMT é colocada na célula EMI para que possa ser usada pelo Goal Seek.

O Excel calcula o EMI com a função PMT. A mesa agora se parece com -

Enquanto o Interest_Ratecélula está vazia, o Excel assume esse valor como 0 e calcula o EMI. Você pode ignorar o resultado-13,888.89.

Realize a análise com a busca de metas da seguinte forma -

Step 1 - Vá para DATA > What If Analysis > Goal Seek na fita.

A caixa de diálogo Atingir Meta é exibida.

Step 2 - Digite EMI no Set cellcaixa. Esta caixa é a referência da célula que contém a fórmula que se deseja resolver, neste caso a função PMT. É a célula C6, que você chamou de EMI.

Step 3 - Digite -50000 no To valuecaixa. Aqui, você obtém o resultado da fórmula, neste caso, o IME que deseja pagar. O número é negativo porque representa um pagamento.

Step 4 - Digite Interest_Rate no By changing cellcaixa. Esta caixa tem a referência da célula que contém o valor que pretende ajustar, neste caso a taxa de juro. É a célula C2, que você nomeou como Interest_Rate.

Step 5- Esta célula que a busca de meta muda, deve ser referenciada pela fórmula na célula que você especificou na caixa Definir célula. Clique OK.

Goal Seek produz um resultado, conforme mostrado abaixo -

Como você pode observar, o Goal Seek encontrou a solução usando a célula C6 (que contém a fórmula) como 12% que é exibido na célula C2, que é a taxa de juros. Clique OK.

Resolvendo Problemas de História

Você pode resolver problemas de história facilmente com o Goal Seek. Vamos entender isso com a ajuda de um exemplo.

Exemplo

Suponha que haja uma livraria com 100 livros armazenados. O preço original do livro é 250 e certo número de livros foi vendido a esse preço. Posteriormente, a livraria anunciou um desconto de 10% naquele livro e liberou o estoque. Você pode querer saber quantos livros são vendidos pelo preço original para obter uma receita total de 24.500.

Você pode usar o Goal Seek para encontrar a solução. Siga as etapas abaixo -

Step 1 - Defina a planilha conforme a seguir.

Step 2 - Vá para DATA > What If Analysis > Goal Seek na fita.

A caixa de diálogo Atingir Meta é exibida.

Step 3 - Tipo Revenue, 24500 and Books_OriginalPricena caixa Definir célula, caixa Para valor e caixa Alterando a célula, respectivamente. Clique OK.

Goal Seek exibe o status e a solução.

Se 80 livros fossem vendidos pelo preço original, a receita seria de 24.500.

Executando uma análise de ponto de equilíbrio

Na economia, o ponto de equilíbrio é o ponto em que não há lucro nem perda. Isso significaria -

Receita = despesas ou

Receita - Despesas = 0

Você pode fazer break-even analysis with Goal Seek no Excel.

Exemplo

Suponha que haja uma loja que venda brinquedos. Você pode querer fazer uma análise do ponto de equilíbrio da loja. Colete as seguintes informações da loja -

• Custo fixo da loja.
• Custo unitário do brinquedo.
• Quantidade de brinquedos a serem vendidos.

Você precisa descobrir a que preço eles deveriam vender os brinquedos para empatar.

Step 1 - Defina a planilha conforme a seguir.

Step 2 - Vá para DATA > What If Analysis > Goal Seekna fita. A caixa de diálogo Atingir Meta é exibida.

Step 3 - Tipo Break_even_Point, 0, and Unit_Pricena caixa Definir célula, na caixa Para valor e na caixa Alterando a célula, respectivamente. Clique OK.

Como você pode observar, o Goal Seek deu o resultado que se o Preço Unitário for 35, a loja terá um ponto de equilíbrio.

Solver é um programa de suplemento do Microsoft Excel que você pode usar para otimização em análises de hipóteses.

De acordo com O'Brien e Marakas, optimization analysisé uma extensão mais complexa da análise de busca de objetivos. Em vez de definir um valor de destino específico para uma variável, o objetivo é encontrar o valor ideal para uma ou mais variáveis ​​de destino, sob certas restrições. Então, uma ou mais outras variáveis ​​são alteradas repetidamente, sujeitas às restrições especificadas, até que você descubra os melhores valores para as variáveis ​​de destino.

No Excel, você pode usar Solver para encontrar um optimal value (máximo ou mínimo, ou um determinado valor) para uma fórmula em uma célula chamada célula objetivo, sujeito a certas restrições ou limites, nos valores de outras células de fórmula na planilha.

Isso significa que o Solver trabalha com um grupo de células chamadas variáveis ​​de decisão que são usadas no cálculo das fórmulas nas células de objetivo e restrição. O Solver ajusta os valores nas células da variável de decisão para satisfazer os limites das células de restrição e produzir o resultado desejado para a célula objetivo.

Você pode usar o Solver para encontrar soluções ideais para diversos problemas, como -

• Determinar o mix mensal de produtos para uma unidade de fabricação de medicamentos que maximize a lucratividade.

• Agendamento de força de trabalho em uma organização.

• Resolvendo problemas de transporte.

• Planejamento financeiro e orçamento.

Ativando Solver Add-in

Antes de prosseguir com a busca de solução para um problema com o Solver, certifique-se de que o Solver Add-in é ativado no Excel da seguinte forma -

• Clique na guia DADOS na faixa de opções. oSolver O comando deve aparecer no grupo Análise, conforme mostrado abaixo.

Caso você não encontre o comando Solver, ative-o da seguinte forma -

• Clique na guia ARQUIVO.
• Clique em Opções no painel esquerdo. A caixa de diálogo Opções do Excel é exibida.
• Clique em Add-Ins no painel esquerdo.
• Selecione Suplementos do Excel na caixa Gerenciar e clique em Ir.

A caixa de diálogo Add-Ins é exibida. VerificaSolver Add-ine clique em Ok. Agora, você deve conseguir encontrar o comando Solver na Faixa de Opções na guia DADOS.

Métodos de resolução usados ​​pelo Solver

Você pode escolher um dos três métodos de solução a seguir que o Excel Solver suporta, com base no tipo de problema -

LP Simplex

Usado para problemas lineares. UMASolver modelo é linear nas seguintes condições -

• A célula-alvo é calculada somando os termos da forma (célula variável) * (constante).

• Cada restrição satisfaz o requisito do modelo linear. Isso significa que cada restrição é avaliada somando os termos da forma (célula variável) * (constante) e comparando as somas com uma constante.

Gradiente Reduzido Generalizado (GRG) Não Linear

Usado para problemas não lineares suaves. Se sua célula-alvo, qualquer uma de suas restrições ou ambas contiverem referências a células variáveis ​​que não são da forma (célula variável) * (constante), você tem um modelo não linear.

Evolucionário

Usado para problemas não lineares suaves. Se sua célula-alvo, qualquer uma de suas restrições ou ambas contiverem referências a células variáveis ​​que não são da forma (célula variável) * (constante), você tem um modelo não linear.

Compreendendo a avaliação do Solver

O Solver requer os seguintes parâmetros -

• Células Variáveis ​​de Decisão
• Células de restrição
• Células Objetivas
• Método de Resolução

A avaliação do Solver é baseada no seguinte -

• Os valores nas células da variável de decisão são restritos pelos valores nas células de restrição.

• O cálculo do valor na célula objetivo inclui os valores nas células da variável de decisão.

• O Solver usa o Método de Resolução escolhido para resultar no valor ideal na célula objetivo.

Definindo um problema

Suponha que você esteja analisando os lucros obtidos por uma empresa que fabrica e vende um determinado produto. Você deve encontrar a quantia que pode ser gasta em publicidade nos próximos dois trimestres, até um máximo de 20.000. O nível de publicidade em cada trimestre afeta o seguinte -

• O número de unidades vendidas, determinando indiretamente o valor da receita de vendas.
• As despesas associadas, e
• O lucro.

Você pode prosseguir para definir o problema como -

• Encontre o custo unitário.
• Encontre o custo de publicidade por unidade.
• Encontre o preço unitário.

Em seguida, defina as células para os cálculos necessários conforme fornecido abaixo.

Como você pode observar, os cálculos são feitos para o Trimestre 1 e o Trimestre 2 que estão em consideração são -

• O número de unidades disponíveis para venda no primeiro trimestre é de 400 e no segundo trimestre é de 600 (células - C7 e D7).

• Os valores iniciais para o orçamento de publicidade são definidos como 10.000 por trimestre (células - C8 e D8).

• O número de unidades vendidas depende do custo de publicidade por unidade e, portanto, é o orçamento para o trimestre / Adv. Custo por unidade. Observe que usamos a função Min para verificar se o não. de unidades vendidas em <= não. de unidades disponíveis. (Células - C9 e D9).

• A receita é calculada como Preço Unitário * Nº de unidades vendidas (Células - C10 e D10).

• As despesas são calculadas como Custo Unitário * Nº Unidades Disponíveis + Adv. Custo para esse trimestre (Células - C11 e D12).

• Lucro é receita - despesas (células C12 e D12).

• O lucro total é o lucro no primeiro trimestre + o lucro no segundo trimestre (célula - D3).

Em seguida, você pode definir os parâmetros do Solver conforme fornecido abaixo -

Como você pode observar, os parâmetros do Solver são -

• A célula objetiva é D3 que contém Lucro Total, que você deseja maximizar.

• As células da variável de decisão são C8 e D8 que contêm os orçamentos para os dois trimestres - Trimestre1 e Trimestre2.

• Existem três células de restrição - C14, C15 e C16.

  • A célula C14 que contém o orçamento total deve definir a restrição de 20.000 (célula D14).

  • A célula C15 que contém o no. de unidades vendidas no primeiro trimestre é definir a restrição de <= não. de unidades disponíveis no primeiro trimestre (célula D15).

  • A célula C16 que contém o não. de unidades vendidas no segundo trimestre é definir a restrição de <= não. de unidades disponíveis no trimestre 2 (célula D16).

Resolvendo o problema

A próxima etapa é usar o Solver para encontrar a solução da seguinte maneira -

Step 1- Vá para DATA> Analysis> Solver na faixa de opções. A caixa de diálogo Solver Parameters é exibida.

Step 2 - Na caixa Definir objetivo, selecione a célula D3.

Step 3 - Selecione Max.

Step 4 - Selecione o intervalo C8: D8 no By Changing Variable Cells caixa.

Step 5 - Em seguida, clique no botão Adicionar para adicionar as três restrições que você identificou.

Step 6- A caixa de diálogo Adicionar restrição é exibida. Defina a restrição para o orçamento total conforme fornecido abaixo e clique em Adicionar.

Step 7- Defina a restrição para nº total. de unidades vendidas no primeiro trimestre conforme fornecido abaixo e clique em Adicionar.

Step 8- Defina a restrição para nº total. de unidades vendidas no trimestre 2 conforme fornecido abaixo e clique em OK.

A caixa de diálogo Solver Parameters aparece com as três restrições adicionadas na caixa –Subject to the Constraints.

Step 9 - no Select a Solving Method caixa, selecione Simplex LP.

Step 10- Clique no botão Solve. A caixa de diálogo Resultados do Solver é exibida. SelecioneKeep Solver Solution e clique em OK.

Os resultados aparecerão em sua planilha.

Como você pode observar, a solução ótima que produz o lucro total máximo, sujeito às restrições dadas, é considerada a seguinte -

• Lucro total - 30000.
• Adv. Orçamento para o primeiro trimestre - 8.000.
• Adv. Orçamento para o segundo trimestre - 12.000.

Percorrendo as soluções de teste do Solver

Você pode percorrer as soluções de teste do Solver, observando os resultados da iteração.

Step 1 - Clique no botão Opções na caixa de diálogo Parâmetros do Solver.

o Options a caixa de diálogo aparece.

Step 2 - Selecione a caixa Mostrar Resultados da Iteração e clique em OK.

Step 3 - o Solver Parametersa caixa de diálogo aparece. CliqueSolve.

Step 4 - o Show Trial Solution caixa de diálogo aparece, exibindo a mensagem - Solver paused, current solution values displayed on worksheet.

Como você pode observar, os valores de iteração atuais são exibidos em suas células de trabalho. Você pode parar o Solver de aceitar os resultados atuais ou continuar com o Solver para encontrar a solução nas próximas etapas.

Step 5 - Clique em Continuar.

o Show Trial SolutionA caixa de diálogo aparece em cada etapa e, finalmente, após a solução ideal ser encontrada, a caixa de diálogo Resultados do Solver é exibida. Sua planilha é atualizada a cada etapa, finalmente mostrando os valores dos resultados.

Salvando seleções do Solver

Você tem as seguintes opções de salvamento para os problemas que resolver com o Solver -

• Você pode salvar as últimas seleções na caixa de diálogo Parâmetros do Solver com uma planilha salvando a pasta de trabalho.

• Cada planilha em uma pasta de trabalho pode ter suas próprias seleções do Solver, e todas elas serão salvas quando você salvar a pasta de trabalho.

• Você também pode definir mais de um problema em uma planilha, cada um com suas próprias seleções de Solver. Nesse caso, você pode carregar e salvar problemas individualmente com Carregar / Salvar na caixa de diálogo Parâmetros do Solver.

  • Clique no Load/Savebotão. A caixa de diálogo Carregar / Salvar é exibida.

  • Para salvar um modelo de problema, insira a referência para a primeira célula de um intervalo vertical de células vazias nas quais você deseja colocar o modelo de problema. Clique em Salvar.

• O modelo do problema (o conjunto de Parâmetros do Solver) aparece começando na célula que você forneceu como referência.

• Para carregar um modelo de problema, insira a referência para todo o intervalo de células que contém o modelo de problema. Em seguida, clique no botão Carregar.

Você pode ter que usar dados de várias fontes para análise. No Excel, você pode importar dados de diferentes fontes de dados. Algumas das fontes de dados são as seguintes -

• Banco de dados Microsoft Access
• Página da web
• Arquivo de texto
• Tabela SQL Server
• Cubo de Análise do SQL Server
• Arquivo XML

Você pode importar qualquer número de tabelas simultaneamente de um banco de dados.

Importando dados do banco de dados Microsoft Access

Aprenderemos como importar dados do banco de dados MS Access. Siga as etapas abaixo -

Step 1 - Abra uma nova pasta de trabalho em branco no Excel.

Step 2 - Clique na guia DADOS na faixa de opções.

Step 3 - Clique From Accessno grupo Obter dados externos. oSelect Data Source a caixa de diálogo aparece.

Step 4- Selecione o arquivo de banco de dados do Access que deseja importar. Os arquivos de banco de dados do Access terão a extensão .accdb.

A caixa de diálogo Selecionar Tabela aparece exibindo as tabelas encontradas no banco de dados Access. Você pode importar todas as tabelas no banco de dados de uma vez ou importar apenas as tabelas selecionadas com base em suas necessidades de análise de dados.

Step 5 - Selecione a caixa Ativar seleção de várias tabelas e selecione todas as tabelas.

Step 6- Clique em OK. oImport Data a caixa de diálogo aparece.

Conforme você observa, você tem as seguintes opções para visualizar os dados que está importando em sua pasta de trabalho -

• Table
• Relatório de Tabela Dinâmica
• PivotChart
• Relatório Power View

Você também tem uma opção - only create connection. Além disso, o Relatório de Tabela Dinâmica é selecionado por padrão.

O Excel também oferece opções para colocar os dados em sua pasta de trabalho -

• Planilha existente
• Nova planilha

Você encontrará outra caixa de seleção que está marcada e desabilitada - Add this data to the Data Model. Sempre que você importa tabelas de dados para sua pasta de trabalho, elas são automaticamente adicionadas ao Modelo de Dados em sua pasta de trabalho. Você aprenderá mais sobre o Modelo de Dados nos capítulos posteriores.

Você pode tentar cada uma das opções para visualizar os dados que está importando e verificar como os dados aparecem em sua pasta de trabalho -

• Se você selecionar Table, A opção de planilha existente é desativada, New worksheeta opção é selecionada e o Excel cria tantas planilhas quanto o número de tabelas que você está importando do banco de dados. As tabelas do Excel aparecem nessas planilhas.

• Se você selecionar PivotTable Report, O Excel importa as tabelas para a pasta de trabalho e cria uma tabela dinâmica vazia para analisar os dados nas tabelas importadas. Você tem a opção de criar a tabela dinâmica em uma planilha existente ou em uma nova planilha.

  As tabelas do Excel para as tabelas de dados importadas não aparecerão na pasta de trabalho. No entanto, você encontrará todas as tabelas de dados na lista de campos da tabela dinâmica, junto com os campos em cada tabela.

• Se você selecionar PivotChart, O Excel importa as tabelas para a pasta de trabalho e cria um gráfico dinâmico vazio para exibir os dados nas tabelas importadas. Você tem a opção de criar o gráfico dinâmico em uma planilha existente ou em uma nova planilha.

  As tabelas do Excel para as tabelas de dados importadas não aparecerão na pasta de trabalho. No entanto, você encontrará todas as tabelas de dados na lista de campos do gráfico dinâmico, junto com os campos em cada tabela.

• Se você selecionar Power View Report, O Excel importa as tabelas para a pasta de trabalho e cria um Relatório do Power View em uma nova planilha. Você aprenderá a usar os relatórios do Power View para analisar dados em capítulos posteriores.

  As tabelas do Excel para as tabelas de dados importadas não aparecerão na pasta de trabalho. No entanto, você encontrará todas as tabelas de dados na lista de campos do Relatório do Power View, junto com os campos de cada tabela.

• Se você selecionar a opção - Only Create Connection, uma conexão de dados será estabelecida entre o banco de dados e sua pasta de trabalho. Nenhuma tabela ou relatório aparece na pasta de trabalho. No entanto, as tabelas importadas são adicionadas ao Modelo de Dados em sua pasta de trabalho por padrão.

  Você precisa escolher qualquer uma dessas opções, com base em sua intenção de importar dados para análise de dados. Como você observou acima, independentemente da opção que você escolheu, os dados são importados e adicionados ao Modelo de Dados em sua pasta de trabalho.

Importando dados de uma página da web

Às vezes, você pode ter que usar os dados que são atualizados em um site. Você pode importar dados de uma tabela em um site para o Excel.

Step 1 - Abra uma nova pasta de trabalho em branco no Excel.

Step 2 - Clique na guia DADOS na faixa de opções.

Step 3 - Clique From Web no Get External Datagrupo. oNew Web Query a caixa de diálogo aparece.

Step 4 - Digite a URL do site de onde deseja importar os dados, na caixa ao lado de Endereço e clique em Ir.

Step 5- Os dados no site aparecem. Haverá ícones de seta amarela ao lado dos dados da tabela que podem ser importados.

Step 6- Clique nos ícones amarelos para selecionar os dados que deseja importar. Isso transforma os ícones amarelos em caixas verdes com uma marca de seleção, conforme mostrado na captura de tela a seguir.

Step 7 - Clique no botão Importar depois de selecionar o que deseja.

o Import Data a caixa de diálogo aparece.

Step 8 - Especifique onde deseja colocar os dados e clique em OK.

Step 9 - Organize os dados para posterior análise e / ou apresentação.

Copiar e colar dados da web

Outra forma de obter dados de uma página da web é copiando e colando os dados necessários.

Step 1 - Insira uma nova planilha.

Step 2 - Copie os dados da página da web e cole na planilha.

Step 3 - Crie uma tabela com os dados colados.

Importando dados de um arquivo de texto

Se você tiver dados em .txt ou .csv ou .prnarquivos, você pode importar dados desses arquivos tratando-os como arquivos de texto. Siga as etapas abaixo -

Step 1 - Abra uma nova planilha no Excel.

Step 2 - Clique na guia DADOS na faixa de opções.

Step 3 - Clique From Textno grupo Obter dados externos. oImport Text File a caixa de diálogo aparece.

Você pode ver isso .prn, .txt and .csv arquivos de texto de extensão são aceitos.

Step 4- Selecione o arquivo. O nome do arquivo selecionado aparece na caixa Nome do arquivo. O botão Abrir muda para botão Importar.

Step 5 - Clique no botão Importar. Text Import Wizard – Step 1 of 3 a caixa de diálogo aparece.

Step 6 - Clique na opção Delimited para escolher o tipo de arquivo e clique em Avançar.

o Text Import Wizard – Step 2 of 3 a caixa de diálogo aparece.

Step 7 - Em Delimitadores, selecione Other.

Step 8- Na caixa ao lado de Outro, digite | (Esse é o delimitador no arquivo de texto que você está importando).

Step 9 - Clique em Avançar.

o Text Import Wizard – Step 3 of 3 a caixa de diálogo aparece.

Step 10 - Nesta caixa de diálogo, você pode definir o formato dos dados da coluna para cada uma das colunas.

Step 11- Depois de concluir a formatação dos dados das colunas, clique em Concluir. oImport Data a caixa de diálogo aparece.

Você observará o seguinte -

• A tabela está selecionada para visualização e acinzentada. Tabela é a única opção de visualização que você tem neste caso.

• Você pode colocar os dados em uma planilha existente ou em uma nova planilha.

• Você pode marcar ou não marcar a caixa de seleção Adicionar esses dados ao modelo de dados.

• Clique em OK depois de fazer as escolhas.

Os dados aparecem na planilha que você especificou. Você importou dados do arquivo de texto para a pasta de trabalho do Excel.

Importando dados de outra pasta de trabalho

Você pode ter que usar dados de outra pasta de trabalho do Excel para sua análise de dados, mas outra pessoa pode manter a outra pasta de trabalho.

Para obter dados atualizados de outra pasta de trabalho, estabeleça uma conexão de dados com essa pasta de trabalho.

Step 1 - Clique DATA > Connections no grupo Conexões na Faixa de Opções.

o Workbook Connections a caixa de diálogo aparece.

Step 2- Clique no botão Adicionar na caixa de diálogo Conexões da pasta de trabalho. oExisting Connections a caixa de diálogo aparece.

Step 3 - Clique Browse for More…botão. oSelect Data Source a caixa de diálogo aparece.

Step 4 - Clique no New Source button. oData Connection Wizard a caixa de diálogo aparece.

Step 5 - Selecione Other/Advancedna lista de fontes de dados e clique em Avançar. A caixa de diálogo Data Link Properties é exibida.

Step 6 - Defina as propriedades do link de dados da seguinte forma -

• Clique no Connection aba.

• Clique em Usar nome da fonte de dados.

• Clique na seta para baixo e selecione Excel Files na lista suspensa.

• Clique OK.

o Select Workbook a caixa de diálogo aparece.

Step 7- Navegue até o local onde você tem a pasta de trabalho a ser importada. Clique OK.

o Data Connection Wizard a caixa de diálogo aparece com Selecionar banco de dados e tabela.

Note- Nesse caso, o Excel trata cada planilha importada como uma tabela. O nome da tabela será o nome da planilha. Portanto, para ter nomes de tabela significativos, nomeie / renomeie as planilhas conforme apropriado.

Step 8- Clique em Avançar. oData Connection Wizard a caixa de diálogo aparece com Salvar Arquivo de Conexão de Dados e Concluir.

Step 9- Clique no botão Concluir. oSelect Table a caixa de diálogo aparece.

Como você pode observar, Nome é o nome da planilha que é importado como tipo TABELA. Clique OK.

A conexão de dados com a pasta de trabalho que você escolheu será estabelecida.

Importando dados de outras fontes

O Excel oferece opções para escolher várias outras fontes de dados. Você pode importar dados em algumas etapas.

Step 1 - Abra uma nova pasta de trabalho em branco no Excel.

Step 2 - Clique na guia DADOS na faixa de opções.

Step 3 - Clique From Other Sources no grupo Obter dados externos.

Uma lista suspensa com várias fontes de dados é exibida.

Você pode importar dados de qualquer uma dessas fontes de dados para o Excel.

Importando dados usando uma conexão existente

Em uma seção anterior, você estabeleceu uma conexão de dados com uma pasta de trabalho.

Agora, você pode importar dados usando essa conexão existente.

Step 1 - Clique na guia DADOS na faixa de opções.

Step 2 - Clique Existing Connectionsno grupo Obter dados externos. A caixa de diálogo Conexões existentes é exibida.

Step 3 - Selecione a conexão de onde deseja importar os dados e clique em Abrir.

Renomeando as conexões de dados

Será útil se as conexões de dados que você tem em sua pasta de trabalho tiverem nomes significativos para facilitar a compreensão e localização.

Step 1 - Vá para DATA > Connectionsna fita. oWorkbook Connections a caixa de diálogo aparece.

Step 2 - Selecione a conexão que deseja renomear e clique em Propriedades.

o Connection Propertiesa caixa de diálogo aparece. O nome atual aparece na caixa Nome da conexão -

Step 3- Edite o nome da conexão e clique em OK. A conexão de dados terá o novo nome que você forneceu.

Atualizando uma conexão de dados externos

Ao conectar sua pasta de trabalho do Excel a uma fonte de dados externa, como viu nas seções acima, você gostaria de manter os dados da pasta de trabalho atualizados, refletindo as alterações feitas na fonte de dados externa de tempos em tempos.

Você pode fazer isso atualizando as conexões de dados feitas com essas fontes de dados. Sempre que você atualizar a conexão de dados, verá as alterações de dados mais recentes dessa fonte de dados, incluindo tudo o que é novo, modificado ou excluído.

Você pode atualizar apenas os dados selecionados ou todas as conexões de dados na pasta de trabalho de uma vez.

Step 1 - Clique na guia DADOS na faixa de opções.

Step 2 - Clique Refresh All no grupo Conexões.

Como você pode observar, há dois comandos na lista suspensa - Atualizar e Atualizar tudo.

• Se você clicar Refresh, os dados selecionados em sua pasta de trabalho são atualizados.

• Se você clicar Refresh All, todas as conexões de dados com sua pasta de trabalho serão atualizadas.

Atualizando todas as conexões de dados na pasta de trabalho

Você pode ter várias conexões de dados com sua pasta de trabalho. Você precisa atualizá-los de vez em quando para que sua pasta de trabalho tenha acesso aos dados mais recentes.

Step 1 - Clique em qualquer célula da tabela que contém o link para o arquivo de dados importado.

Step 2 - Clique na guia Dados na Faixa de Opções.

Step 3 - Clique em Atualizar tudo no grupo Conexões.

Step 4- Selecione Atualizar tudo na lista suspensa. Todas as conexões de dados na pasta de trabalho serão atualizadas.

Atualizar dados automaticamente quando uma pasta de trabalho é aberta

Você pode desejar ter acesso aos dados recentes das conexões de dados com sua pasta de trabalho sempre que ela for aberta.

Step 1 - Clique em qualquer célula da tabela que contém o link para o arquivo de dados importado.

Step 2 - Clique na guia Dados.

Step 3 - Clique em Conexões no grupo Conexões.

A caixa de diálogo Conexões da pasta de trabalho é exibida.

Step 4- Clique no botão Propriedades. A caixa de diálogo Propriedades da conexão é exibida.

Step 5 - Clique na guia Uso.

Step 6 - Marque a opção - Atualizar dados ao abrir o arquivo.

Você também tem outra opção - Remove data from the external data range before saving the workbook. Você pode usar esta opção para salvar a pasta de trabalho com a definição da consulta, mas sem os dados externos.

Step 7- Clique em OK. Sempre que você abre sua pasta de trabalho, os dados atualizados serão carregados em sua pasta de trabalho.

Atualizar dados automaticamente em intervalos regulares

Você pode estar usando sua pasta de trabalho mantendo-a aberta por períodos mais longos. Nesse caso, você pode querer que os dados sejam atualizados periodicamente, sem qualquer intervenção sua.

Step 1 - Clique em qualquer célula da tabela que contém o link para o arquivo de dados importado.

Step 2 - Clique na guia Dados na Faixa de Opções.

Step 3 - Clique em Conexões no grupo Conexões.

A caixa de diálogo Conexões da pasta de trabalho é exibida.

Step 4 - Clique no botão Propriedades.

A caixa de diálogo Propriedades da conexão é exibida. Defina as propriedades da seguinte forma -

• Clique no Usage aba.

• Marque a opção Refresh every.

• Insira 60 como o número de minutos entre cada operação de atualização e clique em OK.

Seus dados serão atualizados automaticamente a cada 60 minutos. (ou seja, a cada uma hora).

Habilitando atualização de fundo

Para conjuntos de dados muito grandes, considere executar uma atualização em segundo plano. Isso retorna o controle do Excel para você em vez de fazer você esperar vários minutos ou mais para que a atualização termine. Você pode usar esta opção quando estiver executando uma consulta em segundo plano. No entanto, durante esse tempo, você não pode executar uma consulta para qualquer tipo de conexão que recupera dados para o Modelo de Dados.

• Clique em qualquer célula da tabela que contém o link para o arquivo de dados importado.

• Clique na guia Dados.

• Clique em Conexões no grupo Conexões. A caixa de diálogo Conexões da pasta de trabalho é exibida.

Clique no botão Propriedades.

A caixa de diálogo Propriedades da conexão é exibida. Clique na guia Uso. As opções de Atualizar controle são exibidas.

• Clique em Ativar atualização em segundo plano.
• Clique OK. A atualização em segundo plano está habilitada para sua pasta de trabalho.

O modelo de dados está disponível no Excel 2013 e em versões posteriores. No Excel, você pode usar o Modelo de Dados para integrar dados de várias tabelas na pasta de trabalho atual e / ou dos dados importados e / ou das fontes de dados conectadas à pasta de trabalho por meio de conexões de dados.

Com um modelo de dados, você pode criar relacionamentos entre as tabelas. O modelo de dados é usado de forma transparente em relatórios de Tabela Dinâmica, Gráfico Dinâmico, PowerPivot e Power View.

Criação de modelo de dados durante a importação de dados

Quando você importa dados de bancos de dados relacionais como o banco de dados Microsoft Access, que contém várias tabelas relacionadas, o Modelo de Dados é criado automaticamente se você importar mais de uma tabela de uma vez.

Você pode opcionalmente adicionar tabelas ao Modelo de Dados, ao importar dados das seguintes fontes de dados -

• Bancos de dados relacionais, uma tabela por vez
• Arquivos de Texto
• Pastas de trabalho do Excel

Por exemplo, enquanto você está importando dados de uma pasta de trabalho do Excel, você pode observar a opção Add this data to the Data Model, com uma caixa de seleção ativada.

Se você deseja adicionar os dados que está importando ao Modelo de Dados, marque a caixa.

Criação de modelo de dados a partir de tabelas do Excel

Você pode criar o Modelo de Dados a partir de tabelas do Excel usando comandos PowerPivot. Você aprenderá sobre PowerPivot em detalhes nos capítulos posteriores.

Todos os comandos do Modelo de Dados estão disponíveis na guia PowerPivot na Faixa de Opções. Você pode adicionar tabelas do Excel ao modelo de dados com esses comandos.

Considere a seguinte pasta de trabalho de dados de vendas, na qual você tem a planilha Catálogo de Produtos que contém Produto, ID do Produto e Preço. Você tem quatro planilhas para vendas em 4 regiões - Leste, Norte, Sul e Oeste.

Cada uma dessas quatro planilhas contém o número de unidades vendidas e o valor total para cada um dos produtos em cada mês. Você precisa calcular o valor total de cada um dos produtos em cada região e o valor total das vendas em cada região.

As etapas a seguir permitem que você chegue aos resultados desejados -

• Comece criando o modelo de dados.
• Clique na planilha do Catálogo de Produtos.
• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Adicionar ao modelo de dados. A caixa de diálogo Criar Tabela é exibida.
• Selecione o intervalo da tabela.
• Marque a caixa Minha tabela tem cabeçalhos. Clique OK.

Uma nova janela - PowerPivot para Excel - <seu nome de arquivo do Excel> é exibida.

A seguinte mensagem aparece no centro da janela em branco -

A tabela Product Backlog que você adicionou ao Modelo de Dados aparece como uma folha na janela do PowerPivot. Cada linha da tabela é um registro e você pode voltar e avançar os registros usando os botões de seta para a esquerda e para a direita na parte inferior da janela.

• Clique na guia Tabela Vinculada na janela do PowerPivot.
• Clique em Ir para a tabela do Excel.

A janela de dados do Excel é exibida.

• Clique na guia da planilha - Leste.
• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Adicionar ao modelo de dados.

Outra folha aparece na janela PowerPivot exibindo a tabela Leste.

Repita para as planilhas - Norte, Sul e Oeste. Ao todo, você adicionou cinco tabelas ao Modelo de Dados. Sua janela do PowerPivot é a seguinte -

Criação de relacionamentos entre tabelas

Se você quiser fazer cálculos nas tabelas, primeiro deve definir as relações entre elas.

• Clique na guia Início na Faixa de Opções na janela do PowerPivot. Como você pode observar, as tabelas são exibidas na Visualização de Dados.

• Clique em Exibir diagrama.

As tabelas aparecem na Visualização de Diagrama. Como você pode observar, algumas das tabelas podem estar fora da área de exibição e todos os campos nas tabelas podem não estar visíveis.

• Redimensione cada tabela para mostrar todos os campos dessa tabela.
• Arraste e organize as tabelas de forma que todas sejam exibidas.

• Na tabela Leste, clique em ID do produto.
• Clique na guia Design na Faixa de Opções.
• Clique em Criar relacionamento. A caixa de diálogo Criar Relacionamento é exibida.

Na caixa sob Tabela, o Leste é exibido. Na caixa abaixo da coluna, a ID do produto é exibida.

• Na caixa em Tabela de pesquisa relacionada, selecione Catálogo de produtos.
• A ID do produto aparece na caixa em Coluna de pesquisa relacionada.
• Clique no botão Criar.

Aparece a linha que representa o relacionamento entre as tabelas Leste e Backlog do produto.

• Repita os mesmos passos para as tabelas - Norte, Sul e Oeste. Linhas de relacionamento aparecem.

Resumindo os dados nas tabelas no modelo de dados

Agora, você está pronto para resumir os dados de vendas de cada um dos produtos em cada região em apenas algumas etapas.

• Clique na guia Página inicial.
• Clique em Tabela Dinâmica.
• Selecione Tabela Dinâmica na lista suspensa.

A caixa de diálogo Criar Tabela Dinâmica aparece na janela de tabelas do Excel. Selecione Nova planilha.

Em uma nova planilha, uma tabela dinâmica vazia é exibida. Como você pode observar, a Lista de Campos contém todas as tabelas do Modelo de Dados com todos os campos exibidos.

• Selecione a ID do produto na Tabela 1 (Catálogo de produtos).

• Selecione Valor total nas outras quatro tabelas.

• Para cada um dos campos em ∑ Valores, altere o Nome personalizado em Configurações do campo de valor para exibir os nomes das regiões como rótulos de coluna.

A soma do valor total será substituída pelo rótulo que você fornecer. A tabela dinâmica com valores resumidos de todas as tabelas de dados mostra os resultados necessários.

Adicionando dados ao modelo de dados

Você pode adicionar uma nova tabela de dados ao Modelo de Dados ou novas linhas de dados às tabelas existentes no Modelo de Dados.

Adicione uma nova tabela de dados ao Modelo de Dados com as seguintes etapas.

• Clique na guia DADOS na faixa de opções.

• Clique em Conexões existentes no grupo Obter dados externos. A caixa de diálogo Conexões existentes é exibida.

• Clique na guia Tabelas. Os nomes de todas as tabelas da pasta de trabalho serão exibidos.

• Clique no nome da tabela que deseja adicionar ao Modelo de Dados.

Clique no botão Abrir. A caixa de diálogo Importar dados é exibida.

Como você sabe, ao importar a tabela de dados, ela é automaticamente adicionada ao Modelo de Dados. A tabela recém-adicionada aparece na janela do PowerPivot.

Adicione novas linhas de dados às tabelas existentes no Modelo de Dados.

Atualize a conexão de dados. Novas linhas de dados da fonte de dados são adicionadas ao Modelo de Dados.

Você pode fazer análises extensas de dados usando tabelas dinâmicas e produzir os relatórios desejados. A integração do Modelo de Dados com a Tabela Dinâmica aprimora a maneira como os dados são agrupados, conectados, resumidos e relatados. Você pode importar tabelas de fontes de dados externas e criar uma tabela dinâmica com as tabelas importadas. Isso facilita as atualizações automáticas dos valores na Tabela Dinâmica sempre que os dados nas fontes de dados conectadas são atualizados.

Criação de uma tabela dinâmica para analisar dados externos

Para criar uma tabela dinâmica para analisar dados externos -

• Abra uma nova pasta de trabalho em branco no Excel.
• Clique na guia DADOS na faixa de opções.
• Clique em From Access no grupo Get External Data. A caixa de diálogo Selecionar fonte de dados é exibida.
• Selecione o arquivo de banco de dados Access.

• Clique no botão Abrir. A caixa de diálogo Selecionar Tabela é exibida, exibindo as tabelas no banco de dados. O banco de dados Access é um banco de dados relacional e as tabelas serão semelhantes às tabelas do Excel, com a exceção de que existem relações entre essas tabelas.

• Marque a caixa Ativar seleção de várias tabelas.

• Selecione todas as tabelas. Clique OK.

o Import Dataa caixa de diálogo aparece. Selecione Relatório de tabela dinâmica. Esta opção importa as tabelas para sua pasta de trabalho do Excel e cria uma tabela dinâmica para analisar as tabelas importadas.

Conforme você observa, a caixa de seleção Adicionar estes dados ao Modelo de Dados está marcada e desabilitada, indicando que as tabelas serão adicionadas ao Modelo de Dados automaticamente.

Os dados serão importados e uma tabela dinâmica vazia será criada. As tabelas importadas aparecem na lista Campos da tabela dinâmica.

Explorando dados em várias tabelas

Você pode analisar os dados das várias tabelas importadas com a Tabela Dinâmica e chegar ao relatório específico desejado em apenas algumas etapas. Isso é possível devido aos relacionamentos pré-existentes entre as tabelas no banco de dados de origem. Conforme você importou todas as tabelas do banco de dados ao mesmo tempo, o Excel recria os relacionamentos em seu Modelo de Dados.

Na lista Campos da Tabela Dinâmica, você encontrará todas as tabelas que importou e os campos em cada uma delas. Se os campos não estiverem visíveis para nenhuma tabela,

• Clique na seta ao lado dessa tabela na lista Campos da Tabela Dinâmica.
• Os campos dessa tabela serão exibidos.

Explorando dados usando a tabela dinâmica

Você sabe como adicionar campos à Tabela Dinâmica e arrastar campos entre áreas. Mesmo que você não tenha certeza do relatório final que deseja, pode brincar com os dados e escolher o relatório apropriado.

Suponha que você queira ter um relatório exibindo o seguinte -

• Dados para cinco disciplinas - Tiro com Arco, Mergulho, Esgrima, Patinação Artística e Patinação de Velocidade.
• Regiões que obtiveram mais de 80 medalhas nessas 5 disciplinas.
• A contagem de medalhas em cada uma das cinco disciplinas em cada uma dessas regiões.
• Contagem total de medalhas para as cinco disciplinas em cada uma dessas regiões.

Você pode ver como é fácil criar este relatório em poucas etapas.

Para começar, crie uma Tabela Dinâmica exibindo a contagem de medalhas em todas as regiões para as cinco disciplinas selecionadas da seguinte forma -

• Arraste o campo NOC_CountryRegion da tabela Medalhas para a área COLUMNS.

• Arraste Disciplina da tabela Disciplinas para a área LINHAS.

• Filtrar Disciplina para exibir apenas as cinco disciplinas para as quais você deseja o relatório. Isso pode ser feito na área Campos da Tabela Dinâmica ou a partir do filtro Rótulos de Linha na própria Tabela Dinâmica.

• Arraste a medalha da tabela Medalhas para a área VALORES.

• Arraste a medalha da tabela Medalhas para a área FILTROS.

Você obterá a seguinte Tabela Dinâmica -

Conforme você observa, a contagem de medalhas é exibida para todas as regiões e para as cinco disciplinas que você selecionou. Em seguida, você deve ajustar este relatório para que apenas as regiões com contagem total de medalhas maior que 80 sejam exibidas.

• Clique no botão de seta à direita de Rótulos da coluna.

• Clique em Filtros de valor na lista suspensa que aparece.

• Selecione Greater Than… na lista suspensa que aparece.

A caixa de diálogo Filtros de valor é exibida.

Como você pode observar, a contagem de medalhas e é maior do que as exibidas nas caixas abaixo Show items for which. Digite 80 na caixa ao lado da caixa que contém é maior que e clique em OK.

Agora, a Tabela Dinâmica exibe apenas as regiões com contagem total de medalhas nas cinco disciplinas selecionadas maior que 80.

Criação de uma relação entre tabelas com campos da tabela dinâmica

Se você não importar as tabelas ao mesmo tempo, se os dados forem de fontes diferentes, ou se você adicionar novas tabelas à sua pasta de trabalho, você mesmo terá que criar as relações entre as tabelas.

Adicione uma nova planilha com uma tabela que contém os campos Esporte e SportID à sua pasta de trabalho.

• Nomeie a mesa - Sports.
• Clique em TODOS na lista Campos da Tabela Dinâmica na planilha da Tabela Dinâmica.

Você pode ver que a tabela recém-adicionada - Esportes também está visível na lista Campos da Tabela Dinâmica.

Em seguida, adicione o campo Esporte também à Tabela Dinâmica da seguinte maneira -

• Arraste o campo Esporte da tabela Esporte para a área LINHAS. Os valores de esporte aparecem como rótulos de linha na tabela dinâmica.

• Uma mensagem aparecerá na lista Campos da Tabela Dinâmica informando que os relacionamentos entre as tabelas podem ser necessários. Um botão CRIAR aparece próximo à mensagem.

Clique no botão CRIAR. A caixa de diálogo Criar Relacionamento é exibida.

• Selecione Medalhas em Tabela.
• Selecione Esporte em Coluna.
• Selecione Esportes na Tabela Relacionada. Esporte aparece na coluna relacionada.
• Clique OK.

Arraste a Disciplina para baixo Sport dentro ROWS. Isso define a hierarquia na Tabela Dinâmica. A Tabela Dinâmica exibe o Esporte e o grupo de disciplinas correspondente para aquele esporte.

O PowerPivot é uma ferramenta de Análise de Dados fácil de usar que pode ser usada no Excel. Você pode usar o PowerPivot para acessar e mashupar dados de praticamente qualquer fonte de dados. Você pode criar seus próprios relatórios fascinantes com o PowerPivot.

Você pode acessar os comandos PowerPivot na guia PowerPivot na Faixa de Opções. Clique na guia PowerPivot na Faixa de Opções. Os comandos PowerPivot serão exibidos na Faixa de Opções. Você pode observar que os comandos relacionados ao Modelo de Dados também aparecem aqui.

Adicionando tabelas ao modelo de dados

Se você importou tabelas, elas serão adicionadas ao Modelo de Dados. Você pode gerenciar o Modelo de Dados na Faixa de Opções do PowerPivot. Você pode adicionar tabelas ao Modelo de Dados com PowerPivot da seguinte maneira -

• Insira uma nova planilha em sua pasta de trabalho.
• Copie os dados da página da web e cole-os na planilha.
• Crie uma tabela com os dados colados.
• Nomeie a tabela como Hosts.
• Renomeie a planilha como Hosts.

• Clique na tabela Hosts.
• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Add to Data Model no grupo Tables.

A tabela é adicionada ao Modelo de Dados. A janela PowerPivot é exibida. Você encontrará a tabela Hosts nas tabelas do Modelo de Dados.

Visualizando Tabelas no Modelo de Dados

• Clique na guia POWERPIVOT na faixa de opções.
• Clique em Gerenciar no grupo Modelo de Dados.

A janela do PowerPivot aparece, na Exibição de Dados.

O PowerPivot tem duas visualizações -

• Data View- Exibe todas as tabelas do Modelo de Dados com os campos exibidos nas colunas e os dados como registros nas linhas, com uma área de cálculo abaixo de cada tabela. As guias da tabela são semelhantes às guias da planilha do Excel com nomes. Você pode mover-se de mesa em mesa clicando nas guias.

• Diagram View- Exibe todas as tabelas como caixas com o nome da tabela como legenda e os campos listados na caixa. Você pode arrastar as tabelas para alinhá-las, redimensioná-las para tornar todos os campos visíveis e criar relacionamentos apenas clicando nos campos e conectando-os com linhas.

Você entenderá a Visualização do Diagrama e os Relacionamentos em detalhes nas seções posteriores.

Aqui, observe que todas as tabelas no Modelo de Dados são visíveis na janela do PowerPivot, independentemente de estarem ou não presentes como planilhas na pasta de trabalho.

Vendo relacionamentos entre tabelas

Você pode usar os dados de diferentes tabelas para análise e relatório apenas quando existem relacionamentos entre eles.

Você pode ver as relações entre as tabelas na exibição do diagrama na janela do PowerPivot.

• Clique Diagram View no View grupo.

• Redimensione o diagrama usando a barra de rolagem para que você possa ver todas as tabelas no Modelo de Dados no diagrama.

Todas as tabelas no Modelo de Dados aparecem com suas listas de campos. As relações entre as tabelas são indicadas pelas linhas que as conectam.

Criação de relacionamentos entre tabelas

Você pode querer criar um relacionamento entre as tabelas - Medalhas e Eventos. Para fazer isso, deve haver um campo que seja comum em ambas as tabelas e contenha valores únicos em uma das tabelas. Primeiro, você precisa verificar isso.

• Clique em Visualização de Dados no grupo Visualização.
• Clique na guia Eventos para visualizar a tabela Eventos.

Você pode observar que o campo DisciplineEvent na tabela de eventos tem valores exclusivos (sem valores duplicados).

Clique na guia Medalhas para visualizar a tabela Medalhas. O campo DisciplineEvent também está disponível na tabela de medalhas. Portanto, você pode criar um relacionamento usando o campo DisciplineEvent da seguinte maneira -

• Clique em Exibir Diagrama no grupo Exibir.

• Reorganize as tabelas na visualização arrastando-as para que a tabela de eventos e a tabela de medalhas fiquem próximas uma da outra.

• Redimensione as tabelas para que todos os campos fiquem visíveis.

Clique no campo DisciplineEvent da tabela Eventos e arraste para o campo DisciplineEvent da tabela Medalhas.

Uma linha aparece entre a tabela Eventos e a tabela Medalhas, indicando que um relacionamento foi estabelecido.

Visualizando o campo definindo um relacionamento

Você pode visualizar o campo que é usado para criar o relacionamento entre duas tabelas.

Clique na linha de relacionamento que conecta as duas tabelas. A linha de relacionamento e o campo que define o relacionamento entre as duas tabelas são destacados.

O Power View permite a exploração, visualização e apresentação interativa de dados que encoraja relatórios ad-hoc intuitivos. Grandes conjuntos de dados podem ser analisados ​​em tempo real usando as visualizações versáteis. As visualizações de dados também podem ser tornadas dinâmicas, facilitando a apresentação dos dados com um único relatório do Power View.

O Power View é apresentado no Microsoft Excel 2013. Antes de iniciar sua análise de dados com o Power View, certifique-se de que o suplemento do Power View esteja ativado e disponível na Faixa de Opções.

Clique na guia INSERIR na faixa de opções. O Power View deve estar visível no grupo Relatórios.

Criação de um relatório do Power View

Você pode criar um relatório do Power View a partir das tabelas no Modelo de Dados.

• Clique na guia INSERIR na faixa de opções.
• Clique em Power View no grupo Relatórios.

Opening Power Viewa caixa de mensagem aparece com uma barra de status verde de rolagem horizontal. Isso pode demorar um pouco.

A planilha do Power View é criada como uma planilha em sua pasta de trabalho do Excel. Ele contém um relatório vazio do Power View, espaço para filtros e a lista de Campos do Power View exibindo as tabelas no Modelo de Dados. O Power View aparece como uma guia na faixa de opções na folha do Power View.

Power View com campos calculados

No modelo de dados de sua pasta de trabalho, você tem as seguintes tabelas de dados -

• Disciplines
• Events
• Medals

Suponha que você queira exibir o número de medalhas que cada país ganhou.

• Selecione os campos NOC_CountryRegion e Medal na tabela Medalhas.

Estes dois campos aparecem em CAMPOS nas Áreas. O Power View será exibido como uma tabela com os dois campos selecionados como colunas.

O Power View mostra quais medalhas cada país ganhou. Para exibir o número de medalhas conquistadas por cada país, as medalhas precisam ser contadas. Para obter o campo de contagem de medalhas, você precisa fazer um cálculo no Modelo de Dados.

• Clique na guia PowerPivot na faixa de opções.

• Clique em Gerenciar no grupo Modelo de Dados. As tabelas no modelo de dados serão exibidas.

• Clique na guia Medalhas.

• Na tabela Medalhas, na área de cálculo, na célula abaixo da coluna Medalha, digite a seguinte fórmula DAX

  Medal Count:=COUNTA([Medal])

Você pode observar que a fórmula da contagem de medalhas aparece na barra de fórmulas e à esquerda da barra de fórmulas, o nome da coluna Medalha é exibido.

Você receberá uma mensagem do Power View informando que o Modelo de Dados foi alterado e, se clicar em OK, as alterações serão refletidas em seu Power View. Clique OK.

Na Folha do Power View, na lista Campos do Power View, você pode observar o seguinte -

• Um novo campo Contagem de medalhas é adicionado à tabela Medalhas.

• Um ícone de calculadora aparece ao lado do campo Contagem de medalhas, indicando que é um campo calculado.

• Desmarque o campo Medalha e selecione o campo Contagem de medalhas.

A tabela do Power View exibe a contagem de medalhas por país.

Filtragem Power View

Você pode filtrar os valores exibidos no Power View definindo os critérios de filtro.

• Clique na guia TABELA nos Filtros.

• Clique em Contagem de medalhas.

• Clique no ícone Modo de arquivo de intervalo que está à direita de Contagem de medalhas.

• Selecione é greater than ou equal to na lista suspensa na caixa abaixo de Mostrar itens para os quais o valor.

• Digite 1000 na caixa abaixo disso.

• Clique em aplicar filtro.

Abaixo do nome do campo - Contagem de medalhas, é maior ou igual a 1000 aparece. O Power View exibirá apenas os registros com Contagem de medalhas> = 1000.

Visualizações do Power View

Na folha do Power View, duas guias - POWER VIEW e DESIGN aparecem na faixa de opções.

Clique no DESIGN Você encontrará vários comandos de visualização no grupo Alternar Visualização na Faixa de Opções.

Você pode criar rapidamente várias visualizações de dados diferentes que se adaptam aos seus dados usando o Power View. As visualizações possíveis são Tabela, Matriz, Cartão, Mapa, Tipos de gráfico como Barra, Coluna, Dispersão, Linha, Pizza e Gráficos de bolhas e conjuntos de gráficos múltiplos (gráficos com o mesmo eixo).

Para explorar os dados usando essas visualizações, você pode começar na planilha do Power View criando uma tabela, que é a visualização padrão, e então convertê-la facilmente para outras visualizações, para encontrar aquela que melhor ilustra seus dados. Você pode converter uma visualização do Power View em outra, selecionando uma visualização no grupo Alternar Visualização na Faixa de Opções.

Também é possível ter várias visualizações na mesma planilha do Power View, para que você possa destacar os campos significativos.

Nas seções abaixo, você entenderá como pode explorar dados em duas visualizações - Matriz e Cartão. Você aprenderá a explorar dados com outras visualizações do Power View em capítulos posteriores.

Explorando dados com visualização de matriz

A visualização de matriz é semelhante a uma visualização de tabela, pois também contém linhas e colunas de dados. No entanto, uma matriz tem recursos adicionais -

• Ele pode ser reduzido e expandido por linhas e / ou colunas.
• Se ele contém uma hierarquia, você pode fazer uma busca detalhada / busca detalhada.
• Ele pode exibir totais e subtotais por colunas e / ou linhas.
• Ele pode exibir os dados sem repetir valores.

Você pode ver essas diferenças nas visualizações, tendo uma visualização de tabela e uma visualização de matriz dos mesmos dados lado a lado no Power View.

• Escolha os campos - Esporte, Disciplina e Evento. Uma Tabela representando esses campos aparece no Power View.

Como você observa, existem várias disciplinas para cada esporte e vários eventos para cada disciplina. Agora, crie outra visualização do Power View no lado direito desta visualização de tabela da seguinte maneira -

• Clique na folha Power View no espaço à direita da Tabela.
• Escolha os campos - Esporte, Disciplina e Evento.

Outra Tabela que representa esses campos aparece no Power View, à direita da Tabela anterior.

• Clique na tabela certa.
• Clique na guia DESIGN na Faixa de Opções.
• Clique em Tabela no grupo Alternar Visualização.
• Selecione Matrix na lista suspensa.

A tabela à direita no Power View é convertida em Matrix.

A tabela à esquerda lista o esporte e a disciplina para cada evento, enquanto a matriz à direita lista cada esporte e disciplina apenas uma vez. Portanto, neste caso, a visualização em Matriz oferece um formato abrangente, compacto e legível para seus dados.

Agora, você pode explorar os dados para encontrar os países que marcaram mais de 300 medalhas. Você também pode encontrar os esportes correspondentes e ter subtotais.

• Selecione os campos NOC_CountryRegion, Sport e Contagem de medalhas nas visualizações Tabela e Matriz.

• Nos Filtros, selecione o filtro para a Tabela e defina os critérios de filtragem como maior ou igual a 300.

• Clique em aplicar filtro.

• Defina o mesmo filtro para Matrix também. Clique em aplicar filtro.

Mais uma vez, você pode observar que na visualização Matrix, os resultados são legíveis.

Explorando dados com visualização de cartão

Em uma visualização de cartão, você terá uma série de instantâneos que exibem os dados de cada linha da tabela, dispostos como um cartão de índice.

• Clique na visualização de matriz que está no lado direito da visualização Power.
• Clique em Tabela no grupo Alternar Visualização.
• Selecione Cartão na lista suspensa.

A visualização de matriz é convertida em visualização de cartão.

Você pode usar a visualização Cartão para apresentar os dados destacados de uma forma abrangente.

Modelo de dados e Power View

Uma pasta de trabalho pode conter as seguintes combinações de Modelo de Dados e Power View.

• Um modelo de dados interno em sua pasta de trabalho que você pode modificar no Excel, no PowerPivot e até mesmo em uma planilha do Power View.

• Apenas um modelo de dados interno em sua pasta de trabalho, no qual você pode basear uma planilha do Power View.

• Múltiplas planilhas do Power View em sua pasta de trabalho, com cada planilha baseada em um modelo de dados diferente.

Se você tiver várias planilhas do Power View em sua pasta de trabalho, poderá copiar visualizações de uma para outra somente se ambas as planilhas forem baseadas no mesmo Modelo de Dados.

Criando Modelo de Dados a partir da Folha do Power View

Você pode criar e / ou modificar o Modelo de Dados em sua pasta de trabalho a partir da planilha do Power View da seguinte maneira -

Comece com uma nova pasta de trabalho que contém dados do vendedor e dados de vendas em duas planilhas.

• Crie uma tabela a partir do intervalo de dados na planilha do Vendedor e nomeie-a como Vendedor.

• Crie uma tabela a partir do intervalo de dados na planilha de Vendas e chame-a de Vendas.

Você tem duas tabelas - Vendedor e Vendas em sua pasta de trabalho.

• Clique na tabela Vendas na planilha de Vendas.
• Clique na guia INSERIR na faixa de opções.
• Clique em Power View no grupo Relatórios.

A Folha do Power View será criada em sua pasta de trabalho.

Você pode observar que na lista Campos do Power View, ambas as tabelas que estão na pasta de trabalho são exibidas. No entanto, no Power View, apenas os campos da tabela ativa (Vendas) são exibidos, pois apenas os campos da tabela de dados ativos são selecionados na lista Campos.

Você pode observar que, no Power View, a ID do vendedor é exibida. Suponha que você queira exibir o nome do vendedor.

Na lista Campos do Power View, faça as seguintes alterações.

• Desmarque o campo ID do vendedor na tabela Vendedor.
• Selecione o campo Vendedor na tabela Vendedor.

Como você não tem um Modelo de Dados na pasta de trabalho, não existe relacionamento entre as duas tabelas. Nenhum dado é exibido no Power View. O Excel exibe mensagens que orientam você sobre o que fazer.

Um botão CRIAR também será exibido. Clique no botão CRIAR.

o Create Relationship A caixa de diálogo é aberta na própria Folha do Power View.

• Crie um relacionamento entre as duas tabelas usando o campo ID do vendedor.

Sem sair da planilha do Power View, você criou com sucesso o seguinte -

• O modelo de dados interno com as duas tabelas, e
• A relação entre as duas tabelas.

O campo Vendedor aparece no Power View junto com os dados de vendas.

• Reter os campos Região, Vendedor e ∑ Valor do Pedido naquele pedido na área CAMPOS.

• Converta o Power View em visualização de matriz.

• Arraste o campo Mês para a área TILE BY. A visualização da matriz aparece da seguinte forma -

Conforme você observa, para cada uma das regiões, os vendedores dessa região e a soma do valor do pedido são exibidos. Os subtotais são exibidos para cada região. A exibição mostra o mês conforme selecionado no bloco acima da visualização. Conforme você seleciona o mês no bloco, os dados desse mês são exibidos.

No Power View, você tem várias opções de gráfico: pizza, coluna, barra, linha, dispersão e bolha. Os gráficos no Power View são interativos. Se você clicar em um valor em um gráfico -

• Esse valor nesse gráfico é destacado.
• Esse valor em todos os outros gráficos no Power View também é destacado.
• Todas as tabelas, matrizes e blocos no Power View são filtrados para esse valor.

Portanto, os gráficos do Power View servem como ferramentas interativas de análise de dados pictóricos. Além disso, os gráficos também são interativos em uma configuração de apresentação, o que permitiria a você destacar os resultados da análise.

Explorando com gráficos de linha

Você pode usar gráficos de linhas para comparar pontos de dados em uma ou mais séries de dados. Os gráficos de linha distribuem dados de categoria uniformemente ao longo de um eixo horizontal (categoria) e todos os dados de valor numérico ao longo de um eixo vertical (valor).

Suponha que você queira exibir a contagem de medalhas para cada país.

• Crie um Power View com os campos NOC_CountryRegion e Medal Count selecionados. Por padrão, a tabela será exibida.

• Clique na tabela.

• Clique em Outro gráfico no grupo Alternar visualização.

• Selecione Linha na lista suspensa. O gráfico de linhas será exibido no Power View.

• Clique na linha ou no eixo da categoria (eixo x).

• Arraste para a esquerda ou direita. As categorias à esquerda ou direita serão exibidas e o gráfico de linha será exibido de acordo.

• Coloque o cursor em qualquer um dos pontos de dados da linha.

Os valores correspondentes a esse ponto de dados serão exibidos nesse ponto.

Explorando com gráficos de barras

Você pode usar gráficos de barras para comparar pontos de dados em uma ou mais séries de dados. Em um gráfico de barras, as categorias são organizadas ao longo do eixo vertical e os valores ao longo do eixo horizontal. No Power View, existem três subtipos de gráfico de barras -

• Barra Empilhada.
• Barra 100% empilhada.
• Barra agrupada.

Você pode converter uma visualização de tabela em visualização de gráfico de barras da seguinte maneira -

• Crie duas visualizações de tabela lado a lado.
• Clique na tabela certa.
• Clique em Gráfico de Barras no grupo Alternar Visualização.
• Clique em Barra empilhada.

A visualização da tabela à direita é convertida em visualização do gráfico de barras. Conforme você observa, os valores do eixo y são classificados pelos valores da categoria em ordem crescente.

• Leve o cursor acima do gráfico de barras. Você encontrará - classificar por NOC_CountryRegion asc.

• Clique em NOC_CountryRegion. É alterado para Contagem de medalhas.

• Clique asc. É alterado para desc. Você verá que o Gráfico de Barras é classificado por contagem decrescente de medalhas.

• Clique na barra com categoria GER. Apenas essa barra será destacada.

• Com a tecla Ctrl pressionada, clique nas Barras com categorias FRA e ITA. As Barras para GER, FRA e ITA terão destaque.

• A tabela à esquerda também mostra os valores apenas para essas três categorias.

Em ambas as visualizações, clique no campo Sexo também na lista Campos do Power View.

Clique na parte esquerda da Barra - GER. Está destacado. Na Tabela, serão apresentadas apenas as informações de GER e Homens.

Note - Você não pode fazer várias seleções neste caso.

Explorando com gráficos de coluna

Você pode usar os gráficos de coluna para mostrar as alterações de dados durante um período de tempo ou para ilustrar a comparação entre os itens. Nos gráficos de colunas, as categorias estão ao longo do eixo horizontal e os valores ao longo do eixo vertical.

No Power View, existem três subtipos de gráfico de coluna -

• Coluna Empilhada.
• Coluna 100% empilhada.
• Coluna agrupada.

Você pode converter uma Visualização de Tabela em Visualização de Gráfico de Colunas da seguinte maneira -

• Crie duas visualizações de tabela lado a lado.
• Clique na tabela certa.
• Clique em Gráfico de Colunas no grupo Alternar Visualização.
• Clique em coluna empilhada.

A visualização da tabela à direita é convertida em visualização do gráfico de barras. Conforme você observa, os valores do eixo x são classificados pelos valores da categoria em ordem crescente.

• Leve o cursor para cima do gráfico de colunas. Você encontrará - classificar por NOC_CountryRegion asc.

• Clique em NOC_CountryRegion. Ele é alterado para Contagem de medalhas.

• Clique em asc. Ele é alterado para desc. Você verá que o Gráfico de Colunas é classificado por contagem decrescente de medalhas.

Clique na parte inferior da barra com a categoria GER. Ele fica destacado.

Na Tabela, serão apresentadas apenas as informações de GER e Homens.

Explorando com gráficos de pizza simples

Os gráficos de pizza no Power View são simples ou sofisticados. Você aprenderá gráficos de pizza simples nesta seção. Você aprenderá gráficos de pizza sofisticados na próxima seção.

Comece criando um gráfico de pizza da seguinte maneira -

• Redimensione o gráfico de colunas empilhadas e mova-o para cima.
• Crie outra visualização de tabela abaixo do gráfico de colunas empilhadas.
• Clique na nova Tabela.
• Clique em Outro gráfico no grupo Alternar visualização.
• Selecione Torta.

A visualização de tabela abaixo do gráfico de colunas empilhadas é convertida em visualização de gráfico de pizza. Como você pode observar, há muitas fatias no gráfico de pizza porque há muitas categorias (países). Observe que os gráficos de pizza funcionam bem apenas quando o número de categorias é 8 ou menos.

Você pode reduzir o número de categorias filtrando os valores da seguinte forma -

• Defina a filtragem como a contagem de medalhas é maior ou igual a 1300 pol -
  • Visualização de mesa
  • Visualização de gráfico de coluna
  • Visualização de gráfico de pizza

Note - Você deve definir e aplicar filtragem a cada uma das visualizações separadamente.

Agora, você tem uma visualização de gráfico de pizza simples, em que a contagem de medalhas é mostrada pelo tamanho da pizza e os países por cores.

Clique em uma fatia de pizza. Essa fatia é destacada e outras ficam acinzentadas. A coluna correspondente no gráfico de colunas também é destacada. Na tabela, apenas os valores correspondentes à fatia de pizza destacada serão exibidos.

Explorando com gráficos de pizza sofisticados

Você pode tornar sua visualização de gráfico de pizza sofisticada, adicionando mais recursos. Você pode fazer uma torta que -

• Aprofunda quando você clica duas vezes em uma fatia, ou
• Mostra sub-fatias dentro das fatias de cores maiores.

Um gráfico de pizza que detalha quando você clica duas vezes em uma fatia

• No gráfico de pizza, na lista Campos do Power View, arraste o campo Gênero para a área COR, abaixo do campo NOC_CountryRegion. Isso significa que você tem duas categorias.

• Na Tabela, inclua Gênero também na lista Campos.

Seu Power View é o seguinte -

Como você pode observar, existe uma única fatia com uma cor para cada categoria - país.

No gráfico de pizza, clique duas vezes na fatia dos EUA.

O gráfico de pizza em seu Power View será alterado para mostrar valores por gênero, que é a segunda categoria, para a categoria selecionada (EUA). As cores do gráfico de pizza agora mostram as porcentagens do segundo campo, ou seja, Gênero, filtrado pela cor da pizza que você clicou duas vezes. Em outras palavras, o gráfico de pizza foi detalhado. Conforme você observa, uma pequena seta aparece no canto superior direito do gráfico de pizza. Se você colocar o mouse sobre ele, a seta será destacada e Drill up será exibido.

Clique na seta de drill up. O gráfico de pizza retorna ao seu estado anterior.

Um gráfico de pizza que mostra sub-fatias dentro das fatias de cores maiores

No gráfico de pizza, na lista Campos do Power View, arraste o campo Sexo da área COR para a área FATIAS.

Seu Power View é o seguinte -

Como você pode ver, no gráfico de pizza, existem duas fatias da mesma cor para a categoria EUA.

Clique em uma dessas fatias.

Você poderá ver as seguintes mudanças no Power View -

• A fatia selecionada é destacada e outras fatias são acinzentadas ou desativadas.
• A barra da categoria EUA exibe a contagem de medalhas para a fatia selecionada.
• A Tabela mostra os valores da fatia selecionada.

• Clique na outra fatia. Você pode observar as alterações fornecidas acima para esta fatia selecionada.

Explorando com gráficos de dispersão

Você pode usar gráficos de dispersão para exibir muitos dados relacionados em um gráfico. Em gráficos de dispersão, o eixo x exibe um campo numérico e o eixo y exibe outro, facilitando a visualização da relação entre os dois valores para todos os itens no gráfico.

Para criar uma visualização de gráfico de dispersão, proceda da seguinte forma -

• Adicione os campos Esporte, Contagem de medalhas e Evento à tabela.

• Clique na seta ao lado de Evento na lista Campos do Power View. Contagem de cliques (distinto). O campo Evento muda para o campo numérico Contagem de eventos. Portanto, você tem um campo de categoria - Esporte e dois campos numéricos - Contagem de medalhas e Contagem de eventos.

• Clique Other Chart no grupo Alternar visualização.

• Clique Scatter.

Você obterá a Visualização do Gráfico de Dispersão, com os pontos de dados exibidos como círculos do mesmo tamanho, mostrando como os valores de Contagem de Eventos e Contagem de Medalhas estão relacionados para cada esporte.

• Clique na guia LAYOUT na faixa de opções.
• Clique em Rótulos de dados no grupo Rótulos.
• Selecione Direito na lista suspensa. Os rótulos de dados aparecem para os pontos de dados.

O esporte Wrestling tem menos número de medalhas em mais número de eventos quando comparado ao esporte Aquático que tem mais número de medalhas em menos número de eventos.

Explorando com gráficos de bolhas

Você pode usar gráficos de bolhas para exibir muitos dados relacionados em um gráfico. Em gráficos de bolhas, o eixo x exibe um campo numérico e o eixo y exibe outro, facilitando a visualização da relação entre os dois valores para todos os itens no gráfico. Um terceiro campo numérico controla o tamanho dos pontos de dados.

Para criar uma visualização de gráfico de bolhas, proceda da seguinte forma -

• Arraste a contagem de medalhas para o tamanho.
• Arraste NOC_CountryRegion para ∑ X-VALUE. O gráfico de dispersão é convertido em gráfico de bolhas.

Como você observa, o tamanho de cada bolha mostra a contagem de medalhas. Os rótulos de dados mostram o esporte.

Explorando com Cores

Você também pode colorir as bolhas por uma categoria da seguinte maneira -

• Arraste o campo NOC_CountryRegion para a área COLOR na lista Campos do Power View.
• Arraste o campo DiscipleEvent para ∑ X-VALUES.

Como você pode observar, a Legenda mostra os valores da categoria que está na área COR e as respectivas cores. Os rótulos de dados correspondem à categoria na área DETALHES. O tamanho dos pontos de dados é da área ∑ SIZE.

A seguir, você pode ver como selecionar uma categoria na Legenda altera a visualização -

Clique em um valor na legenda. Apenas os pontos de dados dessa cor (ou seja, correspondentes a esse valor) serão destacados. Todos os outros pontos de dados serão desativados.

Conforme você observa, todos os esportes correspondentes ao país selecionado são exibidos e o tamanho de cada bolha representa a contagem de medalhas.

Se você quiser saber os detalhes de um único ponto de dados -

• Destaque o ponto de dados apenas clicando nessa bolha.
• Coloque o cursor naquele ponto de dados.

Apenas essa bolha é destacada e o resto das bolhas fica acinzentado. Todas as informações sobre esse ponto de dados serão exibidas em uma caixa ao lado do ponto de dados.

Explorando com Play Axis

Você pode visualizar as mudanças de dados ao longo de um período de tempo usando Play Axis da seguinte forma -

• Arraste o campo Edição na lista Campos do Power View para a área PLAY AXIS.

Uma linha do tempo com um botão Play é inserida na visualização do gráfico de bolhas. Você pode ajustar a linha do tempo filtrando os valores do campo Edição nos Filtros. Isso seria útil se você quiser se concentrar em um intervalo de tempo específico ou se a linha do tempo for muito ampla.

• Ajuste a linha do tempo filtrando o campo Edição em Filtros e escolhendo um intervalo de valores de tempo.

• Clique no botão Play. As bolhas viajam, crescem e encolhem para mostrar como os valores mudam com base no eixo de jogo. Uma pequena linha vertical aparece na linha do tempo que se move ao longo da linha do tempo. A hora nesse ponto também é exibida.

Você pode fazer uma pausa a qualquer momento para estudar os dados com mais detalhes.

Você pode usar mapas para exibir seus dados no contexto geográfico. Os mapas no Power View usam blocos de mapa do Bing, para que você possa aplicar zoom e panorâmica como faria com qualquer outro mapa do Bing. Para fazer os mapas funcionarem, o Power View precisa enviar os dados ao Bing por meio de uma conexão segura da web para geocodificação. Então, ele pede para você habilitar o conteúdo. Adicionar locais e valores coloca pontos no mapa. Quanto maior for o valor, maior será o ponto. Quando você adiciona uma série de vários valores, obtém gráficos de pizza no mapa, com o tamanho do gráfico de pizza mostrando o tamanho do total.

Explorando Dados com Campos Geográficos

Você pode criar uma Visualização de mapa do Power View se seus dados tiverem um campo geográfico, como País / Região, Estado / Província ou Cidade.

Para criar uma visualização de mapa para a contagem de medalhas, por país, proceda da seguinte forma -

• Crie uma nova planilha do Power View na guia INSERT na faixa de opções.

• Arraste os campos NOC_CountryRegion e Medal Count na lista Campos do Power View para o Power View. Uma tabela com esses dois campos é criada.

Então, você tem um campo geográfico e um campo numérico.

• Clique na guia DESIGN na Faixa de Opções.
• Clique em Mapa no grupo Alternar Visualização.

A Visualização de Tabela se converte em Visualização de Mapa.

Conforme você observa, o Power View cria um mapa com um ponto representando cada localização geográfica. O tamanho do ponto é o valor do campo numérico correspondente, que é a contagem de medalhas neste caso. Além disso, na lista Campos do Power View, o campo Geográfico estará na área Locais e o campo Numérico estará na área ∑ TAMANHO.

Para exibir mais informações sobre um ponto de dados, você pode fazer o seguinte -

• Se você colocar o cursor em um ponto no mapa, uma caixa aparecerá exibindo o nome da localização geográfica e o valor numérico correspondente.

• Se você clicar em um ponto no mapa, esse ponto específico será destacado.

Gráficos de pizza como pontos de dados

Suponha que você queira adicionar outro campo também à visualização do mapa. Por exemplo, você pode querer exibir os detalhes sobre as medalhas, viz. os tipos de medalhas - Ouro, Prata e Bronze. Você pode fazer isso da seguinte maneira -

• Arraste a medalha de campo da lista Campos do Power View para a área COR.

Os pontos são convertidos em gráficos de pizza. Uma legenda para medalha aparece exibindo os tipos de medalhas e respectivas cores. ou seja, cada cor no gráfico de pizza representa o tipo da medalha.

Você pode observar que o tamanho de um gráfico de pizza corresponde à contagem de medalhas e o tamanho de cada fatia no gráfico de pizza corresponde à contagem daquele tipo de medalha.

Destacando um Ponto de Dados

Agora, você pode filtrar seus dados e destacar um ponto de dados significativo da seguinte maneira -

• Na área Filtros, defina a Contagem de medalhas para exibir apenas os valores maiores ou iguais a 300.

• Aplique o filtro. O mapa amplia e exibe apenas os valores filtrados.

Coloque o cursor no ponto que representa a Grã-Bretanha. O ponto é destacado e ampliado. Os detalhes do gráfico de pizza serão exibidos.

Como você pode ver, a contagem de medalhas de ouro para a Grã-Bretanha é 514. Você pode encontrar a contagem de medalhas de prata e bronze colocando o cursor nessas fatias.

Destacando uma fatia de pizza em um ponto de dados

A seguir, você pode destacar a contagem da medalha de ouro na Grã-Bretanha.

• Posicione o cursor na fatia com a cor vermelha (como você pode ver que o vermelho representa o ouro na legenda). Clique.

Essa fatia de pizza será destacada. As outras fatias nesse gráfico de pizza e todos os outros gráficos de pizza serão desativados.

Coloque o cursor no ponto novamente. O ponto é ampliado. A fatia que representa o ouro será destacada. Os detalhes da fatia serão exibidos.

Os múltiplos, também chamados de Gráficos Trellis, são uma série de gráficos com eixos X e Y idênticos. Você pode organizar Múltiplos lado a lado, para comparar muitos valores diferentes facilmente ao mesmo tempo.

• Você pode ter gráficos de linhas, gráficos de pizza, gráficos de barras e gráficos de colunas como múltiplos.
• Você pode organizar os múltiplos horizontalmente ou verticalmente.

Gráficos de linha como múltiplos

Você pode exibir a contagem de medalhas por ano para cada região. Em primeiro lugar, você precisa ter o campo Ano. Para obter este campo, você precisa ter uma coluna calculada da seguinte forma -

• Clique na guia Medalhas na Exibição de Dados do Modelo de Dados (na janela do PowerPivot).
• Clique na primeira célula na coluna mais à direita com o cabeçalho Adicionar coluna.
• Tipo =YEAR ([Edition]) na barra de fórmulas e pressione Enter.

Uma nova coluna com cabeçalho CalculatedColumn1 é criada com valores correspondentes aos valores de ano na coluna Edição.

Clique no cabeçalho e renomeie-o como Ano.

• Feche a janela do PowerPivot. O modelo de dados é atualizado. O novo campo - ∑ Ano aparece na lista Campos do Power View.

• Crie uma Tabela no Power View com os campos NOC_CountryRegion, Count of Year e Medal Count, arrastando os campos.

• Converta a tabela em um gráfico de linha no Power View.
• Remova o campo NOC_CountryRegion. Um gráfico de linhas é exibido com Contagem de medalhas por ano.

Como você pode observar, o ano está na área AXIS e a contagem de medalhas está na área ∑ VALORES na lista Campos do Power View. No gráfico de linhas, os valores do ano estão no eixo X e a contagem da medalha no eixo Y.

Agora, você pode criar visualizações múltiplas com gráficos de linhas, como segue -

• Arraste o campo NOC_CountryRegion para a área VERTICAL MULTIPLES na lista Campos do Power View.
• Clique na guia LAYOUT na faixa de opções.
• Clique em Grid Height no grupo Multiples.
• Clique em um valor na lista suspensa.

• Clique em Largura da grade no grupo Múltiplos.
• Clique em um valor da lista suspensa.

Você obterá a visualização múltipla com gráficos de linha organizados como uma grade, com cada gráfico de linha representando um país (NOC_CountryRegion).

Múltiplos verticais

Como você sabe, você colocou o campo NOC_CountryRegion na área VERTICAL MULTIPLES. Portanto, a visualização que você obteve é ​​a visualização Múltiplos Verticais. Você pode observar o seguinte no gráfico fornecido acima.

• Um gráfico de linhas por categoria que é colocado na área VERTICAL MULTIPLES, neste caso - o país.

• A altura e largura da grade que você escolheu determinam o número de linhas e o número de colunas para os múltiplos.

• Um eixo x comum para todos os múltiplos.

• Um eixo y semelhante para cada linha dos múltiplos.

• Uma barra de rolagem vertical no lado direito que pode ser usada para arrastar as linhas dos gráficos de linha para cima e para baixo, de modo a tornar visíveis os outros gráficos de linha.

Múltiplos horizontais

Você pode ter a Visualização de Múltiplos com Múltiplos Horizontais também da seguinte maneira -

• Arraste o campo NOC_CountryRegion para a área VERTICAL MULTIPLES.
• Clique na guia Layout na faixa de opções.
• Selecione os valores para Grid Height e Grid Width no grupo Múltiplos.

Você obterá a visualização de múltiplos horizontais da seguinte forma -

Você pode observar o seguinte no gráfico acima -

• Um gráfico de linhas por categoria que é colocado na área HORIZONTAL MULTIPLES, neste caso - o país.

• A altura da grade que você escolheu determina a altura dos gráficos de linhas, ao contrário do número de linhas dos gráficos de linhas, como é o caso dos MÚLTIPLOS VERTICAIS. Em outras palavras, há uma única linha de gráficos de linhas com a altura determinada pela Altura da grade escolhida.

• A largura da grade que você escolheu determina o número de colunas dos gráficos de linhas na linha.

• Um eixo x comum para todos os múltiplos.

• Um eixo y comum para todos os múltiplos.

• Uma barra de rolagem horizontal na parte inferior, abaixo do eixo x, que pode ser usada para arrastar a linha dos gráficos de linha para a esquerda e para a direita, de modo a tornar visíveis os outros gráficos de linha.

Gráficos de pizza como múltiplos

Se você deseja explorar / visualizar mais de uma categoria em Múltiplos, os gráficos de pizza são uma opção. Suponha que você queira explorar a contagem de medalhas por tipo de medalha para cada um dos países. Proceda da seguinte maneira -

• Clique na guia Design.
• Selecione Pizza no menu suspenso em Outro gráfico.
• Arraste a medalha para a área FATIAS.

Você obterá a visualização de Múltiplos Horizontais com gráficos de pizza, pois você tem o campo NOC_CountryRegion na área MÚLTIPLOS HORIZONTAIS.

Como você pode observar, a contagem de medalhas para cada país é exibida como um gráfico de pizza com as fatias representando os tipos de medalhas com a cor fornecida na legenda.

Suponha que você queira destacar a contagem de medalhas de ouro para todos os países. Você pode fazer isso em uma única etapa da seguinte maneira -

Clique na fatia de cor azul em um dos gráficos de pizza (já que o azul é a cor do ouro de acordo com a legenda). Em todos os gráficos de pizza, apenas as fatias azuis são realçadas e outras fatias são acinzentadas.

Como você pode observar, isso oferece uma maneira rápida de explorar e comparar a contagem de medalhas de ouro entre os países.

Você pode querer exibir mais números de gráficos de pizza em uma visualização. Você pode fazer isso simplesmente mudando para Visualização múltipla vertical e escolhendo os valores corretos para Altura e Largura da grade para uma exibição adequada.

Clique na fatia azul em um dos gráficos de pizza. As fatias azuis em todos os gráficos de pizza são destacadas para que você possa comparar a contagem de medalhas de ouro entre os países.

Gráficos de barras como múltiplos

Você também pode escolher os gráficos de barras para visualização múltipla.

• Mude para a visualização da barra empilhada.
• Ajuste a altura e a largura da grade para obter uma exibição adequada dos gráficos de barras.

Com altura de grade de 6 e largura de grade de 2, você obterá o seguinte -

Você pode ter gráficos de barras agrupadas também para esta visualização.

Gráficos de colunas como múltiplos

Você pode escolher gráficos de colunas também para visualização múltipla.

• Mude para a visualização de colunas empilhadas.
• Ajuste a altura e a largura da grade para obter uma exibição adequada dos gráficos de colunas.

Com Grid Height de 2 e Grid Width de 6, você obterá o seguinte -

Você também pode ter gráficos de colunas agrupadas para esta visualização.

Embrulhar

Os campos que você escolhe dependem do que você deseja explorar, analisar e apresentar. Por exemplo, em todas as visualizações acima, escolhemos Medalha de Fatias que ajudou a analisar a contagem de medalhas por tipo de medalha. Você pode querer explorar, analisar e apresentar os dados em termos de gênero. Nesse caso, selecione o campo Sexo para Fatias.

Mais uma vez, a visualização adequada também depende dos dados que você está exibindo. Se você não tiver certeza sobre a adequação, pode apenas brincar para escolher a correta, pois alternar entre as visualizações é rápido e simples no Power View. Além disso, também o pode fazer na vista de apresentação, para responder a quaisquer questões que possam surgir durante uma apresentação.

Suponha que você tenha muitos dados para exibir com pontos de dados significativos em vários lugares. Nesse caso, você pode ter que rolar com frequência nas visualizações do Power View para encontrar os dados que está procurando. Isso seria tedioso e também pode não ser fácil quando você estiver apresentando os resultados.

Você pode superar esse trabalho enfadonho, usando o recurso de ladrilho no Power View. Com os blocos, você pode obter insights de seus dados muito mais rapidamente. Os blocos funcionam como faixas de navegação, com um único bloco para cada valor de campo possível. Ao clicar em um bloco, apenas os dados relacionados a esse valor de campo são exibidos. Como é fácil rolar os valores na faixa de navegação que altera dinamicamente os valores correspondentes na visualização, os blocos se tornam uma ferramenta fácil de usar para você.

Você pode ter blocos em uma visualização de tabela, matriz, cartão ou gráfico. Você pode ter uma combinação deles no Power View e filtrá-los com blocos. Um Bloco pode ser um texto simples ou uma imagem.

Mesa com Azulejos

Comece com uma visualização de tabela da seguinte forma -

• Arraste os campos NOC_CountryRegion, Sport e Medal Count para o Power View. Conforme você observa, como o número de linhas é grande, é difícil rolar para cima e para baixo para destacar os valores.

• Arraste o campo Esporte da área FIELDS para a área TILE BY na lista Campos do Power View.

A faixa de navegação aparece na parte superior da tabela.

Você pode observar o seguinte -

• Por padrão, o primeiro Bloco na Faixa de Navegação é selecionado.

• Na Tabela, os valores são filtrados de acordo com o Bloco selecionado. Neste caso, o Esporte selecionado.

• Existem botões de seta nas bordas esquerda e direita da faixa de navegação para permitir a rolagem.

Você pode selecionar um bloco diferente da seguinte forma -

• Role a faixa de navegação para exibir o bloco que representa o esporte que você está procurando, por exemplo, badminton.

• Clique no título - Badminton. Os valores da Tabela são filtrados para os do Badminton.

Você pode observar que a contagem de medalhas Total também é exibida. Você tem a opção de ativar ou desativar Totais na Faixa de Opções. Você também pode tornar os blocos mais atraentes e significativos ao colocar imagens no lugar do texto.

• Inclui uma coluna que contém hiperlinks para os arquivos de imagem correspondentes a cada um dos Esportes.

• Inclua esse campo, por exemplo, Discimage em TILE BY. Você receberá as telhas como imagens, retratando cada esporte.

Tile Navigation Strip - Tab Strip

Existem dois tipos de faixas de navegação no Excel - Tile Flow e Tab Strip.

Em uma Tab strip, você pode observar o seguinte -

• A faixa de guias exibe a faixa de navegação na parte superior da Visualização.

• Por padrão, o primeiro bloco na faixa de navegação que está mais à esquerda é selecionado.

• Existem botões de seta nas bordas esquerda e direita da faixa de navegação para permitir a rolagem.

• Você pode rolar para a esquerda ou direita para exibir os blocos.

• O bloco destacado se move para a esquerda ou direita conforme você rola a faixa de guias. Ele também pode ficar fora de vista durante a rolagem.

• Você pode clicar em um bloco para selecioná-lo. O bloco é destacado na mesma posição que estava antes.

• Na Tabela, os valores são filtrados de acordo com o Bloco selecionado. Neste caso, o Esporte selecionado.

Tile Navigation Strip - Tile Flow

Você pode converter a faixa de navegação da faixa de guias para o fluxo de blocos da seguinte maneira -

• Clique em um bloco na faixa de navegação.
• Clique na guia DESIGN na Faixa de Opções.
• Clique em Tile Type no grupo Tiles.
• Clique em Fluxo do bloco na lista suspensa.

A faixa de navegação muda para a parte inferior da Tabela.

Em um fluxo de bloco, você pode observar o seguinte -

• O fluxo do bloco exibe a faixa de navegação na parte inferior da Visualização.

• Por padrão, o primeiro Bloco na faixa de navegação é selecionado. Ele será exibido no centro do fluxo de blocos.

• Não há botões de seta para rolar.

• Você pode rolar para a esquerda ou direita clicando em qualquer um dos blocos à esquerda ou direita do bloco central.

• Os blocos fluem para a esquerda ou direita e o bloco central sempre será destacado.

• Você pode clicar em um bloco para selecioná-lo. O Bloco é destacado e se move para o centro do fluxo do Bloco.

• Como o Bloco selecionado é sempre o Bloco central, acontece o seguinte -

  • O bloco selecionado não fica fora de vista.

  • Quando você rola para a esquerda ou direita, o bloco que vem para a posição central é automaticamente selecionado e destacado.

  • A seleção original desaparece.

  • A Visualização, Tabela neste caso, é automaticamente atualizada para o Bloco no centro do fluxo do Bloco.

• Na Tabela, os valores são filtrados de acordo com o Bloco selecionado. Neste caso, o Esporte selecionado.

Matriz com blocos

Suponha que você queira a contagem de medalhas por tipo de medalha - ouro, prata e bronze e também a contagem total de medalhas, por país para um esporte selecionado, você pode exibir os resultados em uma visualização Matrix Tile.

• Mude Visualização para Matriz.
• Adicione o campo Medalha à Matriz.

Você obterá os resultados desejados da seguinte forma -

Gráfico de barras empilhadas com blocos

Você pode tornar seus resultados explorados mais visíveis mudando sua visualização para Visualização de Bloco de Gráfico de Barras Empilhadas -

Mapas com blocos

Como seus dados contêm localizações geográficas, você também pode alternar para Map Tile Visualization -

Se seus dados mais número de níveis, seria fácil para você explorar e apresentá-los com Hierarquias. Para qualquer valor de dados em sua Hierarquia, você pode fazer uma busca detalhada para exibir mais detalhes ou fazer uma busca detalhada para ter uma visão holística.

Se o seu modelo de dados tiver uma hierarquia, você pode usá-la no Power View. Caso contrário, você pode criar uma hierarquia no próprio Power View com algumas etapas fáceis.

Criação de uma hierarquia no Power View

No Power View, uma hierarquia é melhor representada na visualização de matriz. Crie uma hierarquia na visualização de matriz da seguinte maneira -

• Arraste os campos NOC_CountryRegion, Sport, Discipline, Year e Medal - nessa ordem para a área ROWS.

• Arraste o campo Contagem de medalhas para ∑ VALORES. A ordem dos campos na área ROWS define a hierarquia no Power View.

• Se você não tiver certeza sobre a ordem dos campos, comece com qualquer ordem e, em seguida, reorganize-os na área LINHAS arrastando-os para cima e para baixo, enquanto olha para a tela no Power View.

Você obterá a seguinte visualização de Matriz mostrando os cinco níveis de Hierarquia -

Conforme você observa, os níveis estão aninhados.

Detalhando e Detalhando a Hierarquia

Você pode fazer drill up e drill down na hierarquia de forma que possa mostrar apenas um nível de cada vez. Você pode pesquisar detalhes e obter um resumo.

Para habilitar drill up e drill down, primeiro configure a opção de níveis de exibição como segue -

• Clique na guia DESIGN na Faixa de Opções.
• Clique em Mostrar níveis no grupo Opções.
• Selecione Linhas - Habilitar Drill Down um Nível por Vez na lista suspensa.

A matriz é recolhida para exibir apenas dados de nível 1.

Conforme você observa, quando você clica em cada um dos itens de dados, uma seta para baixo aparece no lado direito para aquele item indicando detalhamento.

Agora, você pode detalhar os dados um nível de cada vez da seguinte maneira -

• Clique no item de dados de nível 1 para o qual deseja mais detalhes. Por exemplo, clique em AUS. Como você pode observar, a contagem de medalhas para AUS é 1079.

• Clique na seta à direita dela. Alternativamente, você pode clicar duas vezes no item de dados.

• Os dados de nível 2 pertencentes ao AUS serão exibidos.

Conforme você observa, uma seta para cima aparece no lado esquerdo do primeiro item, indicando o detalhamento e quando você clica em cada um dos itens de dados, uma seta para baixo aparece no lado direito desse item, indicando o detalhamento.

Agora, você pode detalhar os dados mais um nível da seguinte maneira -

• Clique no item de dados de nível 2 para o qual deseja mais detalhes. Por exemplo, clique em Esportes aquáticos. Como você pode observar, a contagem de medalhas para os esportes aquáticos é 354.

• Clique na seta à direita dela.

• Os dados de nível 3 pertencentes a esportes aquáticos serão exibidos.

Conforme você observa, uma seta para cima aparece no lado esquerdo do primeiro item, indicando o detalhamento e quando você clica em cada um dos itens de dados, uma seta para baixo aparece no lado direito desse item, indicando o detalhamento.

Agora, você pode detalhar os dados mais um nível da seguinte maneira -

• Clique no item de dados de nível 3 para o qual deseja mais detalhes. Por exemplo, clique em Mergulho. Como você pode observar, a contagem de medalhas para Mergulho é 17.

• Clique na seta à direita dela.

• Os dados de nível 4 relativos ao mergulho serão exibidos.

Conforme você observa, uma seta para cima aparece no lado esquerdo do primeiro item, indicando o detalhamento e quando você clica em cada um dos itens de dados, uma seta para baixo aparece no lado direito desse item, indicando o detalhamento.

Agora, você pode detalhar os dados mais um nível da seguinte maneira -

• Clique no item de dados Nível 4 sobre o qual deseja obter mais detalhes. Por exemplo, clique em 2008. Como você pode observar, a contagem de medalhas para 2008 é 3.

• Clique na seta à direita dela.

• Os dados de nível 5 relativos a 2008 serão exibidos.

Conforme você observa, para o primeiro item de dados de Nível 5, uma seta para cima aparece no lado esquerdo indicando o detalhamento. As setas de detalhamento não aparecem no lado direito dos itens de dados, pois há outros níveis para detalhamento.

Agora, você pode pesquisar os dados um nível de cada vez, clicando na seta para cima à esquerda do primeiro item em cada nível.

Explorando uma hierarquia em um gráfico de barras empilhadas

Você pode querer destacar certos valores em uma hierarquia de forma significativa. Nesse caso, você pode usar uma visualização de gráfico, como um gráfico de barras empilhadas da seguinte maneira -

• Clique na visualização de Matriz e mude para Gráfico de Barras Empilhadas.
• Arraste o campo Medalha para a área LEGEND.

Você obterá a visualização do gráfico de barras empilhadas da seguinte forma -

Nesse caso, você precisa clicar duas vezes em uma barra para detalhar.

Clique duas vezes na barra que representa o AUS. O gráfico é detalhado para exibir o próximo nível de dados na hierarquia.

Como você pode observar, uma seta para cima indicando o detalhamento aparece no canto superior direito do gráfico.

Desça mais um nível clicando duas vezes na Barra Aquática. O gráfico é detalhado para exibir o próximo nível de dados na hierarquia.

Você pode fazer uma busca detalhada clicando duas vezes em uma barra ou ampliando clicando na seta de busca no canto superior direito do gráfico.

Isso também oferece uma exploração interativa de dados durante a apresentação.

Você aprendeu como explorar dados interativamente usando o Power View nos capítulos anteriores deste tutorial. Cada folha do Power View pode, por sua vez, ser usada como um relatório interativo. Para tornar o relatório do Power View mais atraente, você pode escolher qualquer um dos temas, paletas de gráficos, fontes e cores de fundo que o Power View fornece.

Quando você altera o tema, o novo tema se aplica a todas as visualizações do Power View no relatório. Além disso, você pode adicionar imagens de fundo, escolher a formatação do fundo, formatar números e alterar a fonte ou o tamanho do texto.

Finalização do Layout do Relatório

Como em qualquer outro relatório, você precisa primeiro decidir o que vai relatar e o melhor layout para poder destacar os pontos de dados significativos.

Suponha que você precise relatar os detalhes das medalhas ganhas pela Austrália no esporte aquático. Como você sabe, os detalhes incluem as disciplinas de esportes aquáticos, contagem de medalhas e os tipos de medalhas (Ouro, Prata e Bronze).

Você pode ter três visualizações no relatório para a melhor representação dos pontos de dados neste caso -

• Matriz que contém os dados - país, esporte e contagem de medalhas.
• Cartão que contém os dados - país, esporte, disciplina e contagem de medalhas.
• Gráfico de barras empilhadas que contém os dados detalhados para disciplina, medalha e contagem de medalhas.

Como você pode observar, os dados na Matriz e no Cartão são rolados para que -

• Matrix exibe os detalhes da Austrália para todos os esportes aquáticos e obteve 354 medalhas.

• Cartão mostra Austrália - esportes aquáticos, modalidades mergulho, natação e pólo aquático e contagem de medalhas em cada um deles.

• O gráfico de barras empilhadas exibe a contagem de medalhas por tipo de medalha nessas três disciplinas.

Agora que o layout do relatório está pronto, você pode começar a torná-lo atraente. No entanto, você precisa manter dois pontos em mente durante esta tarefa -

• A aparência do relatório deve ser baseada no público (Gestores / Alta Administração / Clientes).

• Não fique entediado com as diferentes opções de formatação. Basta mantê-lo simples e destacar os pontos de dados que requerem atenção.

Nas seções a seguir, você entenderá como chegar a um relatório de amostra com as seguintes opções -

• Selecionando o plano de fundo.
• Selecionando o tema.
• Mudando a fonte.
• Alterando o tamanho do texto.

Selecionando o plano de fundo

Você pode ter uma cor de fundo para o relatório do Power View. Por padrão, é branco. Você pode alterá-lo com o comando Background.

• Clique na guia POWER VIEW na faixa de opções.
• Clique em Plano de Fundo no grupo Temas.
• Clique em Light1 Center Gradient. (Você pode escolher aquele que melhor se adequa ao seu relatório).

A cor de fundo muda para a selecionada.

Você pode até definir uma imagem de fundo. Por exemplo, você pode colocar o logotipo da sua empresa ou uma visão das instalações da sua empresa.

Selecionando o tema

O Power View oferece suporte a vários temas. Escolha aquele que se adequa ao seu relatório da seguinte forma -

• Clique na guia POWER VIEW na faixa de opções.
• Clique em Temas no grupo Temas.

Você terá muitas opções para escolher. Se você não tiver certeza sobre o apropriado, experimente alguns para descobrir como a tela se parece.

• Clique no tema Aspect.

Seu relatório será exibido no tema selecionado.

Mudando a fonte

Como você pode observar, o texto do relatório não chama a atenção. Você pode alterar a fonte da seguinte maneira -

• Clique na guia POWER VIEW na faixa de opções.

• Clique em Fonte no grupo Temas.

• Clique em Verdana na lista suspensa de fontes. (Você pode escolher aquele que melhor se adequa ao seu relatório).

Em seguida, você deve tornar a exibição do texto um pouco maior.

Alterar o tamanho do texto

Altere o tamanho do texto da seguinte forma -

• Clique na guia POWER VIEW na faixa de opções.

• Clique em Tamanho do Texto no grupo Temas.

• Clique em 150% na lista suspensa. (Você pode escolher aquele que melhor se adequa ao seu relatório).

• Ajuste as larguras das colunas em Matrix.

• Ajuste o tamanho de cada visualização no relatório.

Seu relatório de amostra está pronto.

Os principais indicadores de desempenho (KPI) são um conjunto de medidas quantificáveis ​​que uma organização usa para medir seu desempenho ao longo do tempo. KPIs são normalmente usados ​​para avaliar o sucesso de uma organização como um todo ou no que diz respeito ao departamento (por exemplo, vendas, finanças, etc.). Você precisa definir os KPIs com base nos objetivos da organização e monitorá-los de tempos em tempos para acompanhar o progresso.

Existem vários tipos de KPI para escolher com base em seus requisitos. Os exemplos incluem -

• Receitas e despesas
• Taxa de retorno
• Valor Médio de Compra
• Valor vitalício do cliente
• Capital de giro

Observe que os KPIs são uma forma de comunicação que envolve as seguintes atividades -

• Identificar os KPIs com base nos objetivos da organização.

• Acompanhamento e reporte dos KPIs.

• Alterar os KPIs conforme a organização progride e / ou as metas da organização mudam.

Identificando os KPIs

A primeira e mais importante etapa na análise de KPIs é identificar os KPIs que monitoram com eficácia as tendências necessárias na organização. Isso requer uma compreensão completa dos objetivos e requer canais de comunicação adequados entre os analistas e aqueles que são responsáveis ​​pelo cumprimento dos objetivos.

Existem vários KPIs para escolher, mas o sucesso no monitoramento depende da escolha certa daqueles que são relevantes para os objetivos. Os KPIs diferem de organização para organização e de departamento para departamento e serão eficazes apenas quando levarem à melhoria do desempenho.

Você pode avaliar a relevância de um KPI usando os critérios SMART - ou seja, o KPI deve ser Sespecífico, Measurable, Aimpossível, Relevante e Tvinculado ao ime. Em outras palavras, o KPI escolhido deve atender aos seguintes critérios -

• O KPI reflete o seu Sobjetivo específico.

• O KPI permite que você Mfacilitar o progresso em direção a esse objetivo.

• A meta para a qual o KPI está sendo definido é realisticamente Attainable.

• O objetivo que o KPI visa é Relevado para a organização.

• Você pode definir um prazo para atingir a meta, de modo que o KPI revele o quão perto da meta está em comparação com o tempo restante.

Os KPIs definidos devem ser avaliados de tempos em tempos para encontrar sua relevância conforme o tempo passa. Se necessário, diferentes KPIs precisam ser definidos e monitorados. Só então, seu monitoramento de KPI estará relacionado às necessidades atuais da organização.

Com base nas necessidades de análise, você deve escolher os KPIs relevantes e os exemplos incluem o seguinte -

• O departamento de vendas pode usar um KPI para medir o lucro bruto mensal em relação ao lucro bruto projetado.

• O departamento de contabilidade pode medir as despesas mensais em relação à receita para avaliar os custos.

• O departamento de recursos humanos pode medir a rotatividade trimestral de funcionários.

• Profissionais de negócios freqüentemente usam KPIs que são agrupados em um scorecard de negócios para obter um resumo histórico rápido e preciso do sucesso dos negócios ou para identificar tendências ou para identificar oportunidades de melhoria de desempenho.

Os exemplos usados ​​neste capítulo são indicativos para ajudá-lo a entender como você pode definir e monitorar KPIs no Excel. O único critério para identificar os KPIs é sua com base nos objetivos, de modo a refletir o cenário atual em comparação com as metas.

KPIs no Excel

• Você pode analisar o desempenho em relação ao destino definido com o PowerPivot. Por exemplo, um PowerPivot KPI pode ser usado para determinar para cada ano e vendedor como suas vendas reais em comparação com sua meta de vendas.

• Você pode explorar e visualizar o mesmo KPI com o Power View.

• Você também pode definir novos KPIs e / ou editá-los no Power View.

• Você pode produzir relatórios estéticos com KPIs no Power View.

Definindo um KPI no Excel

A primeira etapa na análise de KPI é definir o KPI identificado. Isso requer a definição dos três parâmetros para o KPI da seguinte forma -

Valor Base

Um valor base é definido por um campo calculado que se resolve em um valor. O campo calculado representa o valor atual do item nessa linha da tabela. Por exemplo, agregado de vendas, lucro para um determinado período, etc.

Valor alvo / objetivo

Um valor alvo (ou objetivo) é definido por um campo calculado que se resolve em um valor ou por um valor absoluto. É o valor contra o qual o valor atual é avaliado. Pode ser um número fixo, por exemplo, número médio de dias de licença médica que se aplica a todos os funcionários, ou um campo calculado, que resulta em uma meta diferente para cada linha, por exemplo, orçamento de cada departamento da organização .

Status

O status é o indicador do valor. Seria impressionante se você o configurasse como um indicador visual. No Power View no Excel, você pode editar o KPI, escolhendo quais indicadores usar e quais valores acionar cada indicador.

Por exemplo, suponha que você deseja monitorar as metas de vendas dos vendedores em uma organização que está vendendo um produto. O objetivo da análise é identificar os melhores desempenhos que estão atingindo o valor de vendas alvo. Você pode prosseguir para definir o KPI da seguinte maneira -

• Base Value - Valor atual do valor das vendas para cada vendedor.

• Target Value / Goal- Isso é fixo para todos os vendedores de modo a permitir a comparação entre os vendedores. Suponha que o valor alvo de vendas seja 3500. Observe que, para uma análise diferente, você pode variar os valores alvo para os vendedores.

• Status - O status deve ser exibido com um gráfico para determinar facilmente o status do valor base em comparação com o valor alvo.

KPIs no PowerPivot

Você pode definir KPIs no PowerPivot da seguinte maneira -

• Comece com duas tabelas SalesPerson e Sales.
  • A tabela SalesPerson contém o ID do SalesPerson e o nome do SalesPerson.
  • A tabela de vendas contém as informações de vendas relativas ao vendedor e ao mês.
• Adicione as duas tabelas ao modelo de dados.
• Crie um relacionamento entre as duas tabelas usando o campo SalesPerson ID.

Para definir o valor base, você precisa de um campo calculado para o valor das vendas.

• Adicione o campo calculado na tabela Vendas para coluna Valor das vendas no modelo de dados da seguinte forma -

Total Sales:= sum([Sales Amount])

• Clique em Tabela Dinâmica na Faixa de Opções na janela do PowerPivot.
• Selecione Nova Planilha na caixa de diálogo Criar Tabela Dinâmica.

• Adicione o campo Vendedor à área ROWS na Tabela Dinâmica.
• Clique na guia POWERPIVOT na faixa de opções.
• Clique nos KPIs no grupo Cálculos.
• Clique em Novo KPI na lista suspensa.

A caixa de diálogo Key Performance Indicator (KPI) é exibida.

• Selecione Vendas totais na caixa do campo base KPI (valor).

• Em Status de KPI, tem as seguintes opções -

  • Em Definir valor alvo, selecione Valor absoluto e digite 3500 na caixa.

  • Em Definir limites de status, ajuste as barras verticais que representam as porcentagens para 40 e 80.

  • Em Selecionar estilo de ícone, selecione a primeira opção.

Clique no botão OK. Você pode observar o seguinte na tabela Vendas na lista Campos da Tabela Dinâmica -

• O campo Total de vendas é um KPI e é representado pelo ícone

  
  .

• Os três parâmetros de KPI - Valor, Meta e Status aparecem como campos em Total de vendas KPI.

• Selecione os três parâmetros de KPI - Valor, Meta e Status em Vendas totais.

• As três colunas aparecem no PowerPivot, com a coluna Status exibindo os ícones de acordo com o valor correspondente.

Você também pode definir os limites de KPI por valores em vez de porcentagens. Para modificar um KPI definido, proceda da seguinte forma -

• Clique em KPIs no grupo Cálculos na faixa de opções.
• Clique em Gerenciar KPIs na lista suspensa.

A caixa de diálogo Gerenciar KPIs é exibida.

• Clique no KPI - Vendas Totais.
• Clique no botão Editar.

A caixa de diálogo Key Performance Indicator (KPI) é exibida.

• Em Definir limites de status, ajuste as barras verticais para 1500 e 3000.
• Retenha o restante das opções anteriores.
• Clique em OK.

Como você pode observar, os ícones de status refletem os limites alterados.

KPIs no Power View

Você pode criar relatórios estéticos com KPIs no Power View. Você pode usar os KPIs definidos anteriormente no Modelo de Dados ou pode adicionar KPIs no Power View.

Para adicionar ou editar um KPI no Power View, proceda da seguinte forma -

• Na Folha do Power View, clique na guia PowerPivot.

A Faixa de Opções do PowerPivot é exibida, que você usou na seção anterior.

• Clique nos KPIs no grupo Cálculo.
• Clique em Novo KPI para adicionar um KPI.
• Clique em Gerenciar KPIs para editar um KPI.

As etapas são as mesmas da seção anterior.

Você pode criar um relatório estético de Desempenho de Vendas com KPIs no Power View da seguinte maneira -

• Clique na guia DADOS na faixa de opções.
• Clique em Power View no grupo Relatórios.

A folha do Power View é exibida.

• Adicione uma tabela com os campos - Vendedor, Vendas totais e Status de vendas totais.

• Adicione uma segunda tabela com os campos - Vendedor, Vendas totais e Objetivo total de vendas.

• Converta a segunda tabela em barra 100% empilhada.

• Adicione uma terceira tabela com os campos - Vendedor, Região, Vendas totais e Status de vendas totais.

• Converta a terceira mesa em cartão. Arraste o campo Região para Organizar por.

• Adicione o título - Desempenho de vendas.

• Altere a fonte.

• Aumente o tamanho do texto.

• Redimensione a tabela, barra 100% empilhada e cartão apropriadamente.

Seu relatório de desempenho de vendas está pronto -

Como você pode observar, no Power View você pode retratar os resultados da seguinte forma -

• A tabela com ícones de status de KPI é semelhante ao relatório do PowerPivot.

• A barra 100% empilhada representa o percentual alcançado em relação à meta. Você também pode notar que ele dá uma comparação clara do desempenho de todos.

• O cartão descreve o status de KPI dos vendedores junto com a região a que pertencem. Você pode rolar interativamente pelos blocos para exibir os resultados para diferentes regiões que dariam escopo para avaliar o desempenho por região também.