Baixe o app para aproveitar ainda mais
Prévia do material em texto
COD.: 1869_0 Excel 2019 Excel 2019 Excel 2019 Excel 2019 SumárioCréditos Excel 2019 Coordenação Geral Henrique Thomaz Bruscagin Supervisão de Desenvolvimento Digital Alexandre Hideki Chicaoka Produção, Gravação, Edição de Vídeo e Finalização Rebecca Labre Rodrigues dos Santos Roteirização Fernando de Carvalho Navarro Curso ministrado por Fernando de Carvalho Navarro Diagramação Bruno de Oliveira Santos Edição e Revisão final Alexandre Hideki Chicaoka Este material é uma nova obra derivada da seguinte obra original, produzida por Monte Everest Participações e Empreendimentos Ltda., em Jun/2019: Excel 2019 – Gráficos e Dashboards Autoria: Fernando de Carvalho Navarro Fernando de Carvalho Navarro é consultor e instrutor em TI especializado no pacote Microsoft Office, e profissional certificado Microsoft Office Specialist Expert. Possui formação em engenharia e ministra cursos de tecnologia da informação na Impacta Certificação e Treinamento desde 2011. Também é autor de material didático e publicações sobre aplicativos Microsoft Office. Copyright © Monte Everest Participações e Empreendimentos Ltda. Todos os direitos autorais reservados. Este manual não pode ser copiado, fotocopiado, reproduzido, traduzido ou convertido em qualquer forma eletrônica, ou legível por qualquer meio, em parte ou no todo, sem a aprovação prévia, por escrito, da Monte Everest Participações e Empreendimentos Ltda., estando o contrafator sujeito a responder por crime de Violação de Direito Autoral, conforme o art.184 do Código Penal Brasileiro, além de responder por Perdas e Danos. Todos os logotipos e marcas utilizados neste material pertencem às suas respectivas empresas. "As marcas registradas e os nomes comerciais citados nesta obra, mesmo que não sejam assim identificados, pertencem aos seus respectivos proprietários nos termos das leis, convenções e diretrizes nacionais e internacionais." 4 Sumário 5 Introdução ...................................................................................................................................... 09 1. Introdução à análise de dados e à inteligência dos negócios .................10 2. Introdução aos dashboards ...................................................................11 3. Benefícios da criação de dashboards pelo Excel ....................................12 4. Exemplos de dashboards ......................................................................12 5. Ferramentas do Excel usadas em dashboards ........................................15 6. Planejando a montagem de um dashboard ............................................16 6.1. Requisitos .............................................................................................16 6.2. Diretrizes para o projeto .......................................................................17 6.2.1. Desenho ...............................................................................................17 6.2.2. Elementos visuais .................................................................................17 6.2.3. Verificação ............................................................................................20 Pontos principais ................................................................................................21 Teste seus conhecimentos...................................................................................................... 23 Fórmulas e funções avançadas ............................................................................................. 27 1. Introdução ............................................................................................28 2. Tipos de referência ...............................................................................28 2.1. Referências a outras planilhas do mesmo arquivo .................................28 2.2. Referências a outros arquivos ...............................................................29 3. Intervalos nomeados e fórmulas nomeadas ...........................................30 4. Funções matemáticas e estatísticas .......................................................32 4.1. Funções básicas ....................................................................................32 4.2. Funções estatísticas condicionais ..........................................................32 4.3. Funções de ranking ...............................................................................34 4.4. Aplicações ............................................................................................34 5. Funções condicionais ............................................................................36 5.1. Funções condicionais ............................................................................37 5.2. Funções de informação .........................................................................38 6. Funções de procura e referência............................................................39 6.1. Aplicação para as funções ÍNDICE e CORRESP usadas em conjunto ........41 6.2. Aplicações para a função DESLOC .........................................................43 6.2.1. Usando DESLOC para obter um intervalo deslocado ..............................45 6.2.2. Usando DESLOC para recuperar uma célula em uma matriz ...................47 6.2.3. Usando DESLOC para criar intervalos dinâmicos ....................................48 6.3. Aplicação para a função INDIRETO ........................................................51 Pontos principais ................................................................................................53 Teste seus conhecimentos...................................................................................................... 55 Mãos à obra! .................................................................................................................................. 59 Excel 2019 – Gráficos e Dashboards Sumário 6 Ferramentas de interatividade .............................................................................................. 63 1. Introdução ............................................................................................64 2. Validação de dados por lista .................................................................64 3. Controles de formulário ........................................................................66 3.1. Descrição dos controles ........................................................................67 3.2. Trabalhando com controles de formulário .............................................69 3.3. Interagindo com a planilha ....................................................................71 4. Exemplos de aplicação ..........................................................................71 4.1. Exemplo 1: Controlando a formatação condicional ................................71 4.2. Exemplo 2: Vinculando controles de listas ............................................74 4.2.1. Primeira abordagem: Caixas de combinação .........................................74 4.2.2. Segunda abordagem: Validação de dados..............................................77 5. Navegação por links ..............................................................................79 5.1. Links de texto .......................................................................................81 5.2. Links de imagem ...................................................................................82 Pontos principais ................................................................................................87 Teste seus conhecimentos...................................................................................................... 89 Mãos à obra! .................................................................................................................................. 93 Tipos de gráficos ........................................................................................................................97 1. Introdução ............................................................................................98 2. Minigráficos ..........................................................................................98 3. Barras de dados ....................................................................................101 3.1. Por formatação condicional ...................................................................101 3.2. Por repetição de caracteres ...................................................................106 4. Mapa de calor .......................................................................................108 5. Combinação ..........................................................................................110 6. Mapa de árvore .....................................................................................113 7. Explosão solar ......................................................................................115 8. Histograma ...........................................................................................117 9. Pareto ...................................................................................................120 10. Caixa estreita ........................................................................................123 11. Cascata .................................................................................................126 12. Funil .....................................................................................................129 13. Mapa coroplético ..................................................................................131 14. Como escolher o melhor tipo de gráfico ................................................137 14.1. Participação ..........................................................................................138 14.2. Tendência .............................................................................................139 14.3. Comparação..........................................................................................140 14.4. Distribuição ..........................................................................................141 Pontos principais ................................................................................................142 Teste seus conhecimentos...................................................................................................... 143 Mãos à obra! .................................................................................................................................. 147 Sumário 7 Técnicas avançadas para gráficos ....................................................................................... 153 1. Introdução ............................................................................................154 2. Séries “flutuantes” .................................................................................154 2.1. Colunas flutuantes ................................................................................154 2.2. Barras flutuantes ...................................................................................160 3. Destacando valores ...............................................................................165 3.1. Exemplo 1: Destacando uma coluna ......................................................165 3.2. Exemplo 2: Destacando um ponto em gráfico de linhas ........................172 3.3. Exemplo 3: Destacando meses de projeção ...........................................173 3.4. Exemplo 4: Destacando meses de projeção ...........................................174 4. Controlando séries com caixas de seleção ............................................175 5. Parametrizando gráficos .......................................................................179 5.1. Parametrizando com a função DESLOC ..................................................180 5.2. Criando validações por lista ..................................................................180 5.3. Determinando as posições das datas na tabela .....................................181 5.4. Corrigindo a validação da Data final ......................................................182 5.5. Criando o intervalo das datas ................................................................183 5.6. Criando o intervalo dos valores .............................................................184 5.7. Associando os novos intervalos ao gráfico ............................................185 5.8. Automatizando o título do gráfico .........................................................187 Pontos principais ................................................................................................189 Teste seus conhecimentos...................................................................................................... 191 Mãos à obra! .................................................................................................................................. 195 Trabalhando com imagens ..................................................................................................... 199 1. Introdução ............................................................................................200 2. Definindo um plano de fundo ...............................................................200 3. Ilustrações ............................................................................................201 3.1. Imagens ................................................................................................202 3.2. Imagens Online .....................................................................................202 3.3. Formas .................................................................................................203 3.4. Ícones ...................................................................................................203 3.5. Modelos 3D ..........................................................................................204 3.6. SmartArt ...............................................................................................205 3.7. Instantâneo ...........................................................................................206 4. Aplicando imagens aos elementos do gráfico ........................................206 5. Imagens dinâmicas ...............................................................................209 Pontos principais ................................................................................................217 Teste seus conhecimentos...................................................................................................... 219 Mãos à obra! .................................................................................................................................. 221 Excel 2019 – Gráficos e Dashboards Sumário 8 Gráficos de desempenho ......................................................................................................... 227 1. Introdução ............................................................................................228 2. Gráfico de anel ......................................................................................228 3. Gráfico de waffle (ou de pixel) ...............................................................233 4. Gráfico de termômetro ..........................................................................239 4.1. Construção do gráfico de termômetro com escala vertical .....................240 4.2. Construção do gráfico de termômetro usando imagens .........................245 5. Gráfico de velocímetro ..........................................................................247 5.1. Construção do gráfico de velocímetro de 360° ......................................249 5.1.1. Construção do fundo do velocímetro ....................................................249 5.1.2. Construção do ponteiro do velocímetro ................................................2555.2. Construção do gráfico de velocímetro de 180° ......................................262 5.3. Construção do gráfico de velocímetro de 240° ......................................263 5.4. Outras ideias para gráficos de velocímetro ............................................264 5.4.1. Usando uma barra de progresso ...........................................................265 5.4.2. Usando uma imagem de fundo ..............................................................265 5.4.3. Usando rótulos posicionados externamente ..........................................266 5.4.4. Outras formas de desenhar o ponteiro ..................................................267 5.5. Trabalhando com valores reais ..............................................................268 6. Trabalhando com elementos gráficos ....................................................270 6.1. Agrupando objetos ...............................................................................270 6.2. Painel de Seleção ..................................................................................271 Pontos principais ................................................................................................273 Teste seus conhecimentos...................................................................................................... 275 Mãos à obra! .................................................................................................................................. 279 Projeto: Dashboard de vendas regionais .......................................................................... 285 1. Introdução ............................................................................................286 1.1. Descrição das planilhas .........................................................................286 1.2. Objetivos ..............................................................................................287 2. Criação do dashboard ...........................................................................289 2.1. Fase 1: Preparação ................................................................................289 2.2. Fase 2: Início da montagem do dashboard ............................................293 2.3. Fase 3: Quadro comparativo ..................................................................295 2.4. Fase 4: Gráfico de participação nas vendas ...........................................300 2.5. Fase 5: Imagens das regiões .................................................................301 2.6. Fase 6: Imagens dos indicadores ...........................................................304 2.7. Fase 7: Gráficos de velocímetro .............................................................305 2.8. Fase 8: Gráfico do termômetro ..............................................................314 2.9. Fase 9: Gráfico anual das vendas ...........................................................316 3. Fazendo mais com seu dashboard ........................................................323 Mãos à obra! .................................................................................................................................. 325 Introdução à Introdução à análise de dados e à inteligência dos negócios; à Introdução aos dashboards; à Benefícios da criação de dashboards pelo Excel; à Exemplos de dashboards; à Ferramentas do Excel usadas em dashboards; à Planejando a montagem de um dashboard. 10 Excel 2019 1. Introdução à análise de dados e à inteligência dos negócios O Excel é uma ferramenta excepcional, capaz de efetuar cálculos complexos e transformar uma grande quantidade de números em informações úteis. Quando você coleta dados, analisa esses números e é capaz de tomar ações em função deles, você está colocando em prática a Inteligência dos Negócios (Business Intelligence ou simplesmente BI). A BI auxilia os gestores a identificar novas oportunidades de negócios, localizar áreas onde podem cortar custos, reconhecer constantes mudanças no mercado, identificar anomalias nos dados e criar uma vasta gama de relatórios gerenciais. A estrutura de BI compreende, de forma simplista, a integração do conjunto de atividades do seu negócio, a gestão do seu negócio e a tecnologia da informação: A fase inicial é a coleta de dados (data warehousing), criando grandes bases de dados com todas as informações relevantes. Dessa imensa massa de dados é necessário buscar informações e padrões pertinentes ao negócio (data mining). Em função dos dados selecionados, você pode começar as análises e extração de relatórios detalhados para dar suporte ao cenário e à gestão do seu negócio. Através de toda a informação gerada, será possível redimensionar os seus processos de negócio e fluxos de trabalho, focando na reestruturação e otimização da organização como um todo. Processos de benchmarking permitem realizar ajustes para maximizar o desempenho e procurar reduzir custos. Há anos o conceito de BI vem tomando conta das empresas, onde os gestores precisam rapidamente transformar a grande quantidade de dados em conhecimento útil, o qual resultará em ações para benefício da corporação. Como resultado dessa tendência, surgiu a necessidade da elaboração de mecanismos que informem de maneira imediata os pontos mais críticos para que ações possam ser tomadas, caso necessário. A forma mais prática de apresentar tais mecanismos é a montagem de um Painel de Indicadores, ou simplesmente Dashboard. 11 Introdução 2. Introdução aos dashboards Observe a imagem a seguir: O painel de um automóvel exibe diversos indicadores (combustível, óleo, quilometragem rodada etc.), os quais têm a finalidade de mostrar ao motorista informações importantes para que tome ações imediatas, caso necessário. Um painel criado no computador apresenta dados críticos em uma variedade de formatos. Um dashboard proporciona uma visualização instantânea das principais métricas relevantes a um objetivo específico ou a um processo de negócios. Há programas de computador dedicados à criação de tais painéis, mas o Excel se mostra vantajoso por causa de sua capacidade de cálculo, manipulação de bases de dados, criação de gráficos, automação e muito mais. Sem contar que está presente em milhões de computadores em todo o mundo e é o programa padrão para edição de planilhas eletrônicas em quase todas as corporações. Mesmo que a empresa possua um poderoso sistema integrado de gestão (Enterprise Resource Planning – ERP), o que geralmente custa bem caro, muitos relatórios gerenciais acabam sendo feitos ou finalizados em planilhas. O Excel contém tantas funções e recursos que é difícil saber por onde começar. O objetivo deste material é tornar o Excel a sua ferramenta de BI pessoal, transformando simples tabelas e bancos de dados em painéis de indicadores que fazem mais sentido a qualquer gestor. 12 Excel 2019 3. Benefícios da criação de dashboards pelo Excel Há uma série de vantagens em criar dashboards pelo Excel. Veja, a seguir, as principais delas: • Familiaridade com o Excel: Quase todos os usuários de computador sabem ao menos abrir uma planilha, visualizar seus valores e digitar alguma coisa. Com um painel de indicadores feito diretamente no Excel, os colaboradores gastarão menos tempo tentando aprender a usar a ferramenta e mais tempo visualizando, interpretando os dados e tomando as ações necessárias; • Flexibilidade: Alguns sistemas corporativos não permitem analisar de maneira fácil os dados fora das visualizações ou relatórios padronizados. Dentro do Excel, ferramentas como tabelas dinâmicas e controles de formulários oferecem uma maneira intuitiva para selecionar cenários diferentes para a exibição dos dados; • Automação e conexão de dados: Com sua linguagem nativa de programação VBA e um sólido modelo de objetos, o Excel pode automatizar determinados processos e até mesmo fazer a conexão com várias fontes de dados. Através de algumas técnicas avançadas, odashboard pode até funcionar de forma autônoma, exibindo indicadores que são atualizados quando os dados da base também o são; • Custos de desenvolvimento: Em tempos modernos, empresas cortam custos, enxugam o quadro de colaboradores e otimizam processos. De maneira geral, os orçamentos são limitados quanto à aquisição de novos equipamentos ou à compra e manutenção dos sistemas corporativos. Para a maioria das empresas, o Excel é a ferramenta com melhor relação custo/benefício para o desenvolvimento rápido de relatórios gerenciais e painéis de indicadores. O custo de desenvolvimento dos painéis pode ser muito baixo ou quase nulo quando são criados pelos próprios colaboradores. 4. Exemplos de dashboards Se você procurar o termo “excel dashboards” na Internet, verá milhares de páginas se referindo ao assunto, porém, muitas mostram também dashboards criados por outros programas ou desenvolvidos em Flash ou Java para exibição em navegador Web e dispositivos móveis. • Exemplo 1 O painel a seguir mostra um dashboard simples criado em Excel com o uso de tabelas e gráficos dinâmicos. Os dados são filtrados interativamente com o recurso Segmentação de Dados, presente no Excel a partir da versão 2010: 13 Introdução • Exemplo 2 O próximo painel aproveita bem um monitor Full HD para exibir um dashboard com informações críticas sobre rotas de transporte aéreo de uma empresa do setor. O painel usa extensivamente os minigráficos (recurso incorporado ao Excel 2010) e vários tipos mais elaborados de formatação condicional com ícones (semáforos). Há também uma variação horizontal do gráfico do termômetro: 14 Excel 2019 • Exemplo 3 O modelo proposto a seguir é usado por uma grande empresa do mercado de medicamentos. Ele visa incluir respostas e motivos de devoluções no banco de dados Access e depois obter, através do dashboard, os resultados em percentuais, por período e por fornecedores, indicados em gráficos do tipo velocímetro: • Exemplo 4 O projeto a seguir é bastante sofisticado e usa muitas ferramentas avançadas do Excel, como controles de formulário, minigráficos, formatação condicional com ícones, botões com hiperlinks e até uma área semelhante a um cronograma semanal (Gráfico de Gantt). Os botões funcionam como mecanismo de navegação e levam o usuário a outras planilhas na mesma pasta de trabalho: 15 Introdução 5. Ferramentas do Excel usadas em dashboards Imagine que você está conduzindo uma orquestra. Você precisa conhecer o funcionamento de cada instrumento musical, suas capacidades e limitações. Com todo esse conhecimento, você será capaz de planejar quais instrumentos usar, em que momento usar e, assim, compor sua obra musical. Com dashboards, ocorre algo semelhante: você precisa conhecer as ferramentas do Excel para poder planejar quais delas usar, de modo que atendam ao objetivo da apresentação dos seus dados em um painel de indicadores. Aqui, você também será um maestro. A montagem de dashboards necessita de várias ferramentas e recursos do Excel, não necessariamente todos ao mesmo tempo: • Intervalos nomeados e fórmulas nomeadas; • Bancos de dados: importação, filtro e classificação; • Tabelas: inserção, formatação, fórmulas, nomes; • Funções de banco de dados: BDSOMA, BDCONTAR etc.; • Funções matemáticas e estatísticas avançadas: SOMASES, MÉDIASES, CONT.SES, SOMARPRODUTO, MAIOR, MENOR etc.; • Validação de dados; • Funções condicionais: SE, E, OU, SEERRO, SE aninhada; • Funções de procura e referência: PROCV, PROCH, ÍNDICE, CORRESP etc.; • Função DESLOC para criação de intervalos elásticos ou deslocados; • Funções para manipulação de texto: ESQUERDA, DIREITA, EXT.TEXTO, LOCALIZAR, MUDAR, SUBSTITUIR, TEXTO etc.; • Funções auxiliares no tratamento de referências: INDIRETO, ENDEREÇO, LIN, COL etc.; • Formatação condicional, incluindo barras de dados, conjuntos de ícones e formatação avançada com fórmulas; • Gráficos e minigráficos; • Tabelas dinâmicas, gráficos dinâmicos e segmentação de dados; • Controles de formulários: caixa de seleção, botões de opção, caixa de combinação, botão de rotação, barra de rolagem etc.; • Gerenciamento de cenários; • Macros e programação em VBA; • Proteção de planilhas e arquivos. 16 Excel 2019 6. Planejando a montagem de um dashboard Conheça, a seguir, as etapas que devem ser consideradas na montagem de um dashboard. 6.1. Requisitos A montagem de um painel de indicadores consome tempo e, portanto, demanda planejamento. Se você receber a tarefa de criar um dashboard para a empresa, trate essa tarefa como um projeto. Ou seja, invista um tempo no planejamento para que você possa informar ao seu gestor a estimativa de prazo e recursos necessários. A coleta inicial de requisitos dos usuários é uma tarefa importante porque evitará que você precise refazer o seu painel ou realizar dezenas de ajustes. É melhor investir nesse tempo no começo do que desperdiçá-lo no final com alterações desnecessárias. • Defina o objetivo: Converse com os gestores e colaboradores envolvidos, para que você possa levantar a real necessidade da montagem de um dashboard. Às vezes, um bom relatório gerencial já é suficiente, além de demandar um tempo menor na sua confecção; • Determine o público: Descubra quem serão os usuários finais e como eles pretendem usar o dashboard. Por exemplo: “serão usuários internos preocupados com o desempenho mensal das vendas ou um público externo com o qual você deseja compartilhar informações de seus produtos?”; • Defina os indicadores: A maioria dos dashboards é projetada em torno de um conjunto de medidas denominadas Indicadores Principais de Desempenho (Key Performance Indicators – KPI). Um KPI é um indicador do nível de desempenho de uma tarefa ou processo importante e revelará quando estiver fora dos valores ideais e se será necessária alguma intervenção; • Faça uma lista das prováveis fontes de dados: Verifique se você e o usuário final terão acesso às bases de dados e com qual frequência os dados serão atualizados. Se o acesso direto aos dados não for possível por questões de segurança, solicite ao administrador das bases da empresa ao menos as tabelas que você precisará, mesmo que exportadas para outros formatos que o Excel consiga manipular; • Determine grupos e filtros: Com acesso às bases de dados, analise como será feito o agrupamento de dados (exemplos: por Região, por Vendedor, por Segmento de Mercado, por Categoria de Produto etc.). Na apresentação dos dados em forma de dashboard, escolha também os filtros para exibição dos grupos (exemplos: por período, por faixa etária etc.). 17 Introdução 6.2. Diretrizes para o projeto Além de considerar os requisitos descritos anteriormente, na montagem de um dashboard, você precisa refletir sobre o esboço e os elementos visuais que pretende adotar no seu painel de indicadores. 6.2.1. Desenho Trate um dashboard como algo que precisa ser desenhado ou projetado. Depois, faça uma espécie de maquete ou um esboço de como serão representados os componentes e os tipos de dados necessários à criação das métricas. Dentro de cada caixa, escreva o objetivo a ser alcançado. Veja o exemplo de um esboço inicial: Nesta fase, você não precisa saber se o componente será mostrado como tabela, gráfico ou outra forma. Também não será necessário usar todos os espaços disponíveis. 6.2.2. Elementos visuais Um dashboard com excesso de informações não atinge o objetivo. Pelo contrário, dispersa. Da mesma forma, elementos visuais distrativos também tiram o foco da mensagem. 18 Excel 2019 O gráfico a seguir apresenta formatações e elementos que tiram a atenção da mensagem que está sendo transmitida: O próximo gráfico exibe os mesmos dados, porém de forma mais simplificada e objetiva: Veja adiante algumas regras básicas que você deve seguir: • Simplicidade: Mantenha somente os dados necessários e não inclua informações apenas porque elas existem. O foco será apenas no objetivo dodashboard; • Formatação simples: Evite gráficos com fundo escuro, preenchimento com gradiente, imagens carregadas, efeitos de sombra, 3D etc. O objetivo de um gráfico ou tabela no dashboard é transmitir uma mensagem rapidamente e não ser motivo de distrações; • Gráficos limpos: Remova ou simplifique elementos gráficos desnecessários, como linhas de grade, linhas de tendência, legendas, títulos de eixos, rótulos de dados, bordas, fundos coloridos etc., até porque não há espaço para isso; 19 Introdução • Formato numérico: Evite rótulos de dados, mas, quando necessário, limite a quantidade de casas decimais ou exiba os valores em milhares ou milhões. Separador decimal também auxilia na leitura de números grandes. Use o símbolo de moeda somente quando essa informação for necessária (geralmente, títulos e legendas já informam isso); • Rótulos: Procure usar títulos relevantes ao conteúdo de cada componente do dashboard, formatando-os com cores e tamanhos mais discretos. Identifique claramente o período dos dados e aquele que corresponde ao foco do painel (exemplo: 2019, Jan/2020, 3º Trim. 2018 etc.). Evite usar abreviaturas e símbolos que não são muito conhecidos pelo público que usará o dashboard; • Limite de uma tela: Faça o possível para colocar todos os componentes do dashboard em uma única tela. Isto facilita a localização imediata do indicador desejado e permite comparações entre diferentes períodos ou categorias. Use mais de uma tela quando precisar mostrar assuntos bem distintos e, quando isso acontecer, projete algum mecanismo de navegação intuitivo entre as telas, como botões e hiperlinks; • Tamanho da tela: Quanto maior a área para exibir o seu painel, melhor. Portanto, a melhor tela para exibir um dashboard é uma TV ou monitor com resolução Full HD (1920 × 1080 pixels). A alta definição permite a colocação de grande quantidade de elementos e ainda será possível ver os detalhes com clareza. Você pode ganhar espaço ocultando elementos do Excel e do próprio Windows (barra de tarefas, faixa de opções, guias das planilhas, barras de rolagem etc.); • Posicionamento: Existem determinadas áreas em uma tela ou página que atraem a atenção mais rapidamente que outras. A figura a seguir mostra a ordem de importância dessas áreas, para que sirva de orientação para o posicionamento dos componentes do seu painel: 20 Excel 2019 6.2.3. Verificação Antes de liberar seu painel de indicadores, faça uma checklist, respondendo a perguntas como as seguintes: • Ele apresenta as informações corretas conforme foi solicitado? • Tudo nele tem um propósito? • Ele consegue transmitir a mensagem principal? • Os dados apresentados são precisos? • Ele necessita de manutenção periódica? Quem a fará? • Ele está bem documentado? • Ele é intuitivo e fácil de operar? • Precisarei configurar página e impressão? 21 Introdução Pontos principais Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes da leitura. • Há anos o conceito de BI (Business Intelligence – Inteligência dos Negócios) vem tomando conta das empresas e, como resultado dessa tendência, surgiu a necessidade da elaboração de mecanismos que informem de maneira imediata os pontos mais críticos para que ações possam ser tomadas, caso necessário. A forma mais prática de apresentar tais mecanismos é a montagem de um Painel de Indicadores, ou simplesmente Dashboard; • Há programas de computador dedicados à criação de dashboards, mas o Excel se mostra vantajoso por causa de sua capacidade de cálculo, manipulação de bases de dados, criação de gráficos, automação, baixíssimo custo e muito mais, além de ser o programa padrão para edição de planilhas eletrônicas em quase todas as corporações; • Você precisa conhecer as ferramentas do Excel para poder planejar quais irá usar, de modo que atendam ao objetivo da apresentação dos seus dados num painel de indicadores; • Ao construir um dashboard, as seguintes etapas devem ser consideradas: requisitos, desenho, elementos visuais e verificação. Teste seus conhecimentos Introdução 24 Excel 2019 1. Qual o nome do processo em que você coleta dados, analisa os números, gera relatórios e, mais importante, toma decisão sobre eles? ☐ a) Estatística. ☐ b) Matemática financeira. ☐ c) Inteligência dos Negócios (Business Intelligence). ☐ d) Pesquisa de mercado. ☐ e) Nenhuma das alternativas anteriores está correta. 2. Qual a afirmação que melhor descreve os dashboards? ☐ a) São planilhas com fórmulas complexas e vínculos com outras pastas de trabalho do Excel. ☐ b) São gráficos dinâmicos com formatação condicional, filtrados por segmentação de dados. ☐ c) São bancos de dados com informações detalhadas de cadastramento. ☐ d) São painéis que reúnem dados, gráficos, tabelas e outros elementos visuais que mostram de forma rápida os principais indicadores de seu negócio. ☐ e) Nenhuma das alternativas anteriores está correta. 3. Qual a alternativa que não representa uma vantagem em usar o Excel para criação de dashboards? ☐ a) A familiaridade com o uso do Excel faz com que você não precise aprender um programa novo porque já conhece as ferramentas. ☐ b) O Excel possui muitas funções avançadas, ferramentas de análise de dados, geração de gráficos e formatações bastante úteis para a montagem de painéis. ☐ c) Existe a possibilidade de você obter dados de arquivos externos e até mesmo trabalhar com macros para automatizar as atualizações de dados. ☐ d) Como o Excel já está instalado na empresa, não haverá custos adicionais de aquisição de software e treinamento. ☐ e) Todas as alternativas anteriores estão corretas. 25 Introdução 4. Qual a alternativa incorreta quanto à montagem de dashboards? ☐ a) A montagem de um dashboard demanda tempo e planejamento. Deve ser tratado como se fosse um projeto. ☐ b) A identificação do público vai determinar o tipo de informação que será visualizada. Dados sensíveis não devem ser mostrados a qualquer usuário. ☐ c) Pelo fato de um dashboard ser muito complexo, não é possível trabalhar com macros. ☐ d) O objetivo do dashboard é mostrar os principais indicadores de desempenho do seu negócio, ou KPIs (Key Performance Indicators). ☐ e) Nenhuma das alternativas anteriores está correta. 5. Qual a alternativa que não faz parte das melhores práticas na montagem dos painéis de indicadores? ☐ a) Não é necessário planejamento. Apenas comece a inserir dados e gráficos. Quaisquer modificações serão feitas mais tarde, de forma fácil e rápida. ☐ b) Crie gráficos limpos, com formatação leve e somente com os elementos relevantes, para não desviar a atenção do que está sendo transmitido. ☐ c) Procure limitar o painel em uma tela. Se as informações forem muito detalhadas, é melhor você usar um monitor de alta definição. ☐ d) Posicione as informações mais importantes no canto superior esquerdo da tela e depois distribua as demais para baixo e para a direita. ☐ e) Nenhuma das alternativas anteriores está correta. Fórmulas e funções avançadas à Tipos de referência; à Intervalos nomeados e fórmulas nomeadas; à Funções matemáticas e estatísticas básicas; à Funções condicionais; à Funções de procura e referência. 28 Excel 2019 1. Introdução A base para a montagem de fórmulas mais sofisticadas é o perfeito conhecimento de como os endereços de células e intervalos são referenciados. Muitas vezes, os dados para montagem do seu dashboard estão em outras planilhas, outros arquivos ou até mesmo vêm de outras fontes externas. Além disso, você usará as funções conhecidas de várias categorias. Não é intenção deste curso detalhar funções já abordadas em módulos anteriores porque são pré-requisitos para este treinamento. Muitas delas serão usadas dentro dos exercícios e projetos em que você irá trabalhar. 2. Tipos de referência O Excel trabalha com os seguintes tipos de referência de célulasem suas fórmulas: • Relativa: É uma referência a uma célula ou intervalo que sempre se ajusta conforme a coluna e linha para onde a fórmula é movida ou copiada. Exemplo: B5; • Absoluta: É uma referência a uma célula ou intervalo que nunca se ajusta conforme a coluna e linha para onde a fórmula é movida ou copiada. Exemplo: $B$5; • Mista: É uma referência a uma célula ou intervalo que ajusta apenas a linha ou coluna para onde a fórmula é movida ou copiada. Exemplo: $B5 (coluna fixa, linha variável) ou B$5 (coluna variável, linha fixa). Para trocar rapidamente o tipo de referência de uma célula enquanto você digita uma fórmula, use a tecla F4. 2.1. Referências a outras planilhas do mesmo arquivo Genericamente, a sintaxe para você referenciar um intervalo que está em outra planilha é a seguinte: Nome_da_planilha!Intervalo A fórmula a seguir calcula a soma do intervalo C5 até C25, obtido da Planilha2: =SOMA(Planilha2!C5:C25) 29 Fórmulas e funções avançadas Um nome de planilha poderá ter espaços. Nesse caso, o nome ficará entre aspas simples, como no exemplo a seguir: ='Vendas Regionais'!A5 Quando terminar de digitar a fórmula que aponta para intervalos em outras planilhas, não clique de volta na guia da planilha que contém a fórmula. Apenas tecle ENTER. Caso contrário, o Excel pode mudar uma referência de planilha que já estava correta. 2.2. Referências a outros arquivos Apesar de mais complexa, a montagem de uma fórmula que contém uma referência de intervalo que está em outro arquivo segue o mesmo princípio de apontar e clicar. Porém, neste caso, há um passo extra: você precisa alternar entre os arquivos para poder localizar as células desejadas. Dicas: • Use CTRL + TAB para alternar entre arquivos abertos sem sair do Excel; • Na guia Exibição, grupo Janela, clique no botão Organizar Tudo e escolha a opção Lado a lado para visualizar todos os arquivos abertos simultaneamente. A sintaxe que representa uma referência a outra pasta de trabalho que está aberta é a seguinte: '[Nome da pasta de trabalho]Nome da Planilha'!Intervalo O exemplo a seguir mostra uma fórmula que soma o intervalo B4 até G7 da planilha Plan1 do arquivo Vendas - Norte.xlsx: =SOMA('[Vendas - Norte.xlsx]Plan1'!$B$4:$G$7) Apesar de longa, a fórmula pode ser escrita rapidamente pelo método de apontar e selecionar: escreva o início da função =SOMA(, depois clique na janela do arquivo Vendas - Norte.xlsx, selecione o intervalo B4 até G7 e tecle ENTER. Depois que as fórmulas forem escritas, não há mais a necessidade de manter os arquivos abertos. Quando os arquivos vinculados estiverem fechados, as fórmulas serão ajustadas automaticamente para incluir também o caminho completo do arquivo referenciado. 30 Excel 2019 A sintaxe que representa uma referência a outra pasta de trabalho que está fechada é a seguinte: 'Disco:\Caminho\[Nome do Arquivo]Nome da Planilha'!Intervalo Se o arquivo do exemplo anterior estiver salvo na pasta E:\Relatórios, a fórmula com a soma ficaria assim: =SOMA('E:\Relatórios\[Vendas - Norte.xlsx]Plan1'!$B$4:$G$7) Depois de criar as fórmulas, os arquivos externos estarão vinculados ao principal. Na abertura do arquivo, será solicitado que você habilite os vínculos para que os valores sejam atualizados. Mantenha os arquivos sempre na mesma pasta e não mude o nome deles, senão os vínculos serão perdidos. Use o botão Editar Links na guia Dados para gerenciá-los, caso necessário. 3. Intervalos nomeados e fórmulas nomeadas O uso avançado de muitos recursos do Excel fica mais evidente quando as fórmulas ou referências são feitas com nomes em vez de intervalos digitados explicitamente. As vantagens do uso de intervalos nomeados são claras: facilitam a leitura das fórmulas, podem ser empregados em outras planilhas do mesmo arquivo e são facilmente organizados pelo Gerenciador de Nomes (acionado pela guia Fórmulas ou pelo atalho CTRL + F3). Existem dois tipos de nomes tratados pelo Excel: • Nome definido: Um nome que representa uma célula, um intervalo de células, uma fórmula ou um valor constante. Ele pode ser atribuído por você ou pelo próprio Excel em algumas situações (área de impressão, área de critérios para filtro avançado etc.); • Nome de tabela: Um nome para uma tabela do Excel, que é uma coleção de dados sobre um assunto específico que está armazenado em registros (linhas) e campos (colunas). Este tipo de nome será abordado mais adiante. 31 Fórmulas e funções avançadas Para criar um novo nome, alterar o nome criado, modificar o intervalo (referência) ou até adicionar um comentário, use o Gerenciador de Nomes, acessado pela guia Fórmulas ou pelo atalho CTRL + F3. O Gerenciador de Nomes permite que você crie novos nomes, edite nomes existentes ou exclua nomes que não mais necessita. Além de nomear células e intervalos, você pode nomear constantes (números ou textos) e fórmulas. Estas últimas podem usar operadores matemáticos e até mesmo as funções do Excel, tornando os nomes criados mais genéricos e potentes. Uma fórmula nomeada é uma fórmula que reside apenas na memória do Excel e não está vinculada a uma célula específica. 32 Excel 2019 4. Funções matemáticas e estatísticas Vejamos, nos subtópicos a seguir, funções básicas, funções estatísticas condicionais, funções de ranking e suas aplicações na prática. 4.1. Funções básicas As funções matemáticas e estatísticas mais comuns no Excel e que trabalham com intervalos de células estão relacionadas a seguir. Função Descrição SOMA Soma todos os valores. MÉDIA Calcula a média aritmética de todos os valores. MÁXIMO Retorna o maior valor entre os argumentos. MÍNIMO Retorna o menor valor entre os argumentos. CONT.NÚM Conta somente valores numéricos entre os argumentos. CONT.VALORES Conta todos os valores entre os argumentos. CONTAR.VAZIO Conta apenas as células vazias entre os argumentos. Na apresentação da sintaxe de uma função, quando aparecer algum argumento entre colchetes, é indicação de que ele é opcional e pode ser omitido. Se, no final da função, a sintaxe mostrar reticências (...) é indicação de que a função poderá continuar com mais argumentos, até um máximo de 255. Com exceção das duas últimas, todas as demais ignoram células contendo texto. 4.2. Funções estatísticas condicionais As funções que realizam somas, médias, máximos, mínimos e contagens condicionais são resumidas a seguir: Função Descrição CONT.SE Calcula o número de células não vazias em um intervalo que corresponde a um determinado critério: =CONT.SE(intervalo_critérios; critério) SOMASE Adiciona as células especificadas por um determinado critério dentro de um intervalo: =SOMASE(intervalo_critérios; critério; [intervalo_ soma]) 33 Fórmulas e funções avançadas Função Descrição MÉDIASE Calcula a média aritmética de todas as células de um intervalo que atendem a um determinado critério: =MÉDIASE(intervalo_critérios; critério; [intervalo_ média]) CONT.SES Conta o número de células não vazias dentro de um intervalo que atende a múltiplos critérios: =CONT.SES(intervalo_critérios1; critério1; ...) SOMASES Adiciona as células em um intervalo, as quais atendem a vários critérios: =SOMASES(intervalo_soma; intervalo_critérios1; critério1; ...) MÉDIASES Calcula a média aritmética de todas as células que atendem a vários critérios: =MÉDIASES(intervalo_média; intervalo_critérios1; critério1; ...) MÁXIMOSES Retorna o maior valor em um intervalo de células, as quais atendem a vários critérios: =MÁXIMOSES(intervalo_máximo; intervalo_critérios1; critério1; ...) MÍNIMOSES Retorna o menor valor em um intervalo de células, as quais atendem a vários critérios: =MÍNIMOSES(intervalo_máximo; intervalo_critérios1; critério1; ...) 34 Excel 2019 4.3. Funções de ranking É bastante comum um dashboard possuir um quadro que resume os melhores ou os piores valores, tipo "Top 10". As funções MAIOR e MENOR cumprem essa tarefa. Para saber que posição um determinadovalor ocupa em uma lista, use a função ORDEM. EQ. Função Descrição MAIOR Retorna o k-ésimo maior valor de um conjunto de dados: =MAIOR(intervalo; k) MENOR Retorna o k-ésimo menor valor de um conjunto de dados: =MENOR(intervalo; k) ORDE.EQ Calcula a posição relativa de um número em uma lista de números, seguindo uma ordem ascendente ou descendente: =ORDEM.EQ(núm; ref; [ordem]) 4.4. Aplicações Como prática, na pasta de exercícios da Aula 2, no arquivo Ranking.xlsx, você deverá preencher as células vazias conforme os passos a seguir: 35 Fórmulas e funções avançadas 1. Na célula D5, digite a fórmula que calcula a posição que o número ao lado (D5) ocupa na coluna de Vendas: =ORDEM.EQ(C5; $C$5:$C$16) 2. Copie a fórmula até a linha 16; 3. Na célula G7, calcule o 1º maior valor de Vendas (a posição está na célula à esquerda): =MAIOR($C$5:$C$16; F7) 4. Copie a fórmula para baixo para determinar a 2ª e 3ª posições; 5. Na célula G14, calcule o 1º menor valor de Vendas: =MENOR($C$5:$C$16; F14) 6. Copie a fórmula para baixo para determinar a 2ª e 3ª posições. No final, sua planilha deverá ficar assim: Depois de encontrar os menores e os maiores valores, caso você queira saber quem são os responsáveis por eles, use funções de procura e referência, como PROCV, ÍNDICE e CORRESP, abordadas mais adiante. 36 Excel 2019 5. Funções condicionais As funções condicionais estão presentes no dia a dia de quem trabalha com o Excel, desde o nível básico. A seguir, você verá um resumo de como realizar testes lógicos e a sintaxe para as funções condicionais, além de algumas outras que podem ser úteis na elaboração de painéis de indicadores. Uma célula do Excel é capaz de conter um único dado, que pode ser um número, um texto ou uma fórmula. Dentre as possibilidades numéricas estão os valores lógicos VERDADEIRO e FALSO. Quando digitados, estes valores aparecem em maiúsculas e centralizados nas células. Porém, são usados normalmente dentro de funções lógicas para interpretação do valor a ser retornado como resposta. Valores lógicos são usados, principalmente, nas seguintes circunstâncias: • Função condicional SE: Para decidir se será retornado um resultado quando o valor lógico for VERDADEIRO, ou se será retornado outro resultado quando o valor lógico for FALSO; • Formatação condicional baseada em fórmula: Um intervalo de células será formatado quando o resultado da fórmula for VERDADEIRO, caso contrário, o intervalo não será formatado; • Validação de dados personalizada, baseada em fórmula: Durante a digitação de dados, uma mensagem de erro aparecerá para o usuário quando o resultado da fórmula for FALSO. Caso contrário, nenhuma mensagem de erro será exibida. Os testes condicionais (ou lógicos) são necessários para a tomada de decisões nas aplicações mencionadas acima. Um teste sempre compara dois valores, os quais podem ser números, textos, endereços de células ou fórmulas. O resultado de um teste lógico é sempre um valor lógico VERDADEIRO ou FALSO. Você pode usar os seguintes sinais para fazer os testes lógicos: = (igual a), > (maior que), < (menor que), >= (maior ou igual a), <= (menor ou igual a), <> (diferente de). Exemplos de testes lógicos B5 > 0 SOMA(C10:C18) >= B5 * B6 NomeCliente <> "" Há situações em que serão necessários dois ou mais testes lógicos. Neste caso, você deve usar as funções auxiliares: E, OU e XOR. Estas funções conseguem avaliar simultaneamente até 255 testes lógicos. 37 Fórmulas e funções avançadas 5.1. Funções condicionais Veja na tabela a seguir um resumo das funções condicionais (ou lógicas). Explicações e práticas mais detalhadas são objeto de outros treinamentos. Função Descrição SE A função SE retornará um valor se o resultado do teste lógico for avaliado como VERDADEIRO e outro valor se o resultado do teste lógico for FALSO. =SE(teste_lógico; valor_se_verdadeiro; valor_se_ falso) E A função E retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. =E(teste_lógico1; teste_lógico2; ...) OU A função OU retornará VERDADEIRO se ao menos um de seus argumentos for avaliado como VERDADEIRO e retornará FALSO se todos os argumentos forem avaliados como FALSO. =OU(teste_lógico1; teste_lógico2; ...) XOR A função XOR retornará VERDADEIRO se a quantidade de argumentos avaliados como VERDADEIRO for ímpar, senão, retornará FALSO. =XOR(teste_lógico1; teste_lógico2; ...) NÃO A função NÃO inverte o resultado de um teste lógico. NÃO(teste_lógico) SE aninhada A função SE testa uma condição e traz duas respostas possíveis. Para testar mais condições e trazer mais respostas, você deve usar a função SE de forma aninhada (uma dentro da outra), para testar outras condições. Pode-se aninhar até 64 funções SE. =SE(teste_lógico1; valor1; SE(teste_lógico2; valor2; SE(teste_lógico3; valor3; valor_se_todos_falsos))) SES A função de SES testa múltiplas condições e retorna o valor correspondente à primeira condição com valor VERDADEIRO. Ela foi introduzida no Excel 2016 para ser usada no lugar de múltiplas funções SE aninhadas. SES(teste_lógico1; valor1; [teste_lógico2; valor2]; [teste_lógico3; valor3]; ...) 38 Excel 2019 Função Descrição ESCOLHER Use ESCOLHER para selecionar um valor entre 254 valores que se baseie no número de índice. =ESCOLHER(núm_índice; valor1; [valor2], ...) PARÂMETRO A função PARÂMETRO avalia uma expressão, que pode ser um valor, uma fórmula ou uma célula que contenha um valor a ser testado. Baseado no resultado dessa expressão, a função realiza comparações com diversos valores, trazendo um valor de retorno correspondente. PARÂMETRO(expressão; valor1; resultado1; [valor_padrão ou valor2; resultado2]; [valor_padrão ou valor3; resultado3]; ...) SEERRO O objetivo da função SEERRO é avaliar uma expressão ou cálculo. Se o resultado for um valor de erro, então, ela retornará o valor alternativo que você indicar como 2º argumento. Caso contrário, o próprio resultado da expressão será retornado à célula. Os seguintes tipos de erro são avaliados #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!. =SEERRO(expressão; valor_se_erro) SENÃODISP O objetivo da função SENÃODISP é avaliar uma expressão ou cálculo. Se o resultado for um valor #N/D, então, ela retornará o valor alternativo que você indicar como 2º argumento. Caso contrário, o próprio resultado da expressão será retornado à célula. =SENÃODISP(expressão; valor_se_não_disp) 5.2. Funções de informação Algumas funções do Excel trazem como resultado um valor lógico. Essas funções também possuem alguma utilidade na montagem de fórmulas porque você pode testar o conteúdo de uma célula para saber se é de um determinado tipo ou não. Os nomes dessas funções iniciam com "É". A seguir, uma relação das funções de informação do Excel que retornam VERDADEIRO ou FALSO: Função Descrição É.NÃO.DISP Retorna VERDADEIRO se o valor for o erro #N/D. É.NÃO. TEXTO Retorna VERDADEIRO se o valor for diferente de texto. ÉCÉL.VAZIA Retorna VERDADEIRO se o valor for vazio. 39 Fórmulas e funções avançadas Função Descrição ÉERRO Retorna VERDADEIRO se o valor for um valor de erro diferente de #N/D. ÉERROS Retorna VERDADEIRO se o valor for um valor de erro. ÉFÓRMULA Retorna VERDADEIRO se a referência contiver uma fórmula. ÉIMPAR Retorna VERDADEIRO se o número for ímpar. ÉLÓGICO Retorna VERDADEIRO se o valor for um valor lógico. ÉNÚM Retorna VERDADEIRO se o valor for um número. ÉPAR Retorna VERDADEIRO se o número for par. ÉREF Retorna VERDADEIRO se o valor for uma referência. ÉTEXTO Retorna VERDADEIRO se o valor for texto. 6. Funções de procura e referência Uma função que pertence à categoria de Procura e Referência retorna um valor de uma tabela ou matriz, através da localização de outro valor ou, então, por sua posição de linha e coluna dentro da matriz. Existem várias funções úteis para efetuar buscas emtabelas e bancos de dados e são bastante usadas na montagem de dashboards. Função Descrição PROCV Através de um valor_procurado, a função faz uma busca ao longo da 1ª coluna da matriz_tabela. Quando encontrado, segue na mesma linha para a direita, até a coluna de número índice_coluna. O argumento opcional tipo_procura informa se a procura será feita de maneira aproximada (1 ou VERDADEIRO) ou exata (0 ou FALSO). =PROCV(valor_procurado; matriz_tabela; índice_ coluna; [tipo_procura]) PROCH Através de um valor_procurado, a função faz uma busca ao longo da 1ª linha da matriz_tabela. Quando encontrado, segue na mesma coluna para baixo, até a linha de número índice_linha. O argumento opcional tipo_procura informa se a procura será feita de maneira aproximada (1 ou VERDADEIRO) ou exata (0 ou FALSO). =PROCH(valor_procurado; matriz_tabela; índice_ linha; [tipo_procura]) 40 Excel 2019 Função Descrição PROC A função toma o valor_procurado e faz uma busca ao longo do intervalo vetor_de_procura (pode ser uma linha ou coluna). Quando encontrado, o valor que está na mesma posição do vetor_ de_resultado será retornado. O vetor_de_procura precisa estar classificado em ordem crescente. A função PROC somente trabalha com procura aproximada. Se o valor exato não for encontrado, o anterior mais próximo será retornado. =PROC(valor_procurado; vetor_de_procura; [vetor_de_ resultado]) ÍNDICE A função ÍNDICE retorna o valor de um elemento em uma tabela ou matriz, selecionado pelos índices de número de linha e coluna. Quando a matriz for apenas uma linha ou uma coluna, o último argumento pode ser omitido. =ÍNDICE(matriz; [núm_linha]; [núm_coluna]) CORRESP A função CORRESP procura um item (valor_procurado) em um intervalo (matriz_procurada) e retorna a posição relativa em que este item se encontra na lista. O tipo_correspondência pode ser: exata (0) ou aproximada (-1 ou 1). Use 1 quando o valor a ser retornado for menor que o valor procurado ou use -1 quando o valor a ser retornado for maior que o valor procurado. =CORRESP(valor_procurado; matriz_procurada; [tipo_ correspondência]) DESLOC A função DESLOC retorna uma referência para um novo intervalo, que é um deslocamento especificado em linhas e colunas a partir de uma célula de referência e que pode possuir um novo tamanho em termos de linhas e colunas. A referência retornada pode ser uma única célula ou um intervalo de células adjacentes. =DESLOC(ref; lins; cols; [altura]; [largura]) INDIRETO A função INDIRETO retorna a referência especificada por uma sequência de caracteres de texto (Texto_ref). O argumento Estilo_ A1?, se VERDADEIRO (padrão), indica que os endereços serão no estilo A1; caso seja FALSO, os endereços serão no estilo L1C1. A função, na realidade, transforma o argumento sob forma de texto no intervalo real. A função é útil para mudar a referência a uma célula dentro de uma fórmula, sem mudar a própria fórmula. O resultado da função é um endereço de uma célula ou intervalo. =INDIRETO(Texto_ref; [Estilo_A1?]) 41 Fórmulas e funções avançadas Função Descrição ENDEREÇO Esta função monta uma referência de célula, baseada no número de linha e número de coluna fornecidos. O resultado da função é um texto que representa o endereço desejado. =ENDEREÇO(Linha; Coluna; [Tipo_ref]; [Estilo_A1?]; [Texto_planilha]) LIN, COL LIN retorna o número da linha e COL o número da coluna de uma referência de intervalo. Caso o parâmetro seja omitido, LIN() traz o número da linha em que está a fórmula e COL() traz o número da coluna em que está a fórmula. =LIN(referência_de_célula) =COL(referência_de_célula) LINS, COLS LINS e COLS retornam, respectivamente, o número total de linhas e colunas de um intervalo. A função LINS pode ser usada para encontrar a quantidade de registros de um banco de dados. Por exemplo, se seu banco de dados foi nomeado como BaseVendas, a fórmula =LINS(BaseVendas)-1 retorna a quantidade de registros desse banco. A subtração de 1 no final indica que a linha de cabeçalho será descontada. =LINS(intervalo) =COLS(intervalo) 6.1. Aplicação para as funções ÍNDICE e CORRESP usadas em conjunto O verdadeiro poder dessas funções é notado quando elas são usadas em conjunto. A finalidade é localizar um valor em uma matriz, cuja posição da linha é obtida pela correspondência de um item na lista de títulos de linha e a posição da coluna é obtida pela correspondência de um item na lista de títulos de coluna. A matriz será apenas o interior da tabela, sem esses títulos. 42 Excel 2019 Veja um exemplo: Na pasta de arquivos da Aula 2, arquivo Funções de Procura e Referência.xlsx, Planilha1, observe a tabela com departamentos escritos na primeira coluna e meses escritos na primeira linha: A célula C8 contém o nome do departamento que se deseja localizar e a célula C10 indica o mês de interesse. A localização do valor desejado é obtida pelo cruzamento da linha que contém o departamento e da coluna que contém o mês. A localização do valor procurado pode ser feita de duas maneiras: • Em etapas: Através da função CORRESP, calcula-se a posição relativa que cada item ocupa na coluna de título e na linha de título. Em seguida, a função ÍNDICE será capaz de recuperar o elemento que se encontra na linha e coluna calculadas anteriormente; • Em uma única fórmula: Use a função ÍNDICE, identificando a matriz de números. Como segundo argumento (posição da linha), use CORRESP para localizar a posição que o departamento (C8) ocupa na lista de departamentos da coluna F. Como terceiro argumento (posição da coluna), use CORRESP para localizar a posição que o mês (C10) ocupa na lista de meses da linha 6. Confira a solução em etapas: • A célula D8 contém a posição do departamento (C8) na lista de departamentos (F7:F14): =CORRESP(C8;F7:F14;0) • A célula D10 contém a posição do mês (C10) na lista de meses (G6:L6): =CORRESP(C10;G6:L6;0) • Finalmente, a célula C12 poderá obter o valor na matriz de números (G7:L14), em função das posições de linha e coluna calculadas anteriormente: =ÍNDICE(G7:L14; D8; D10) 43 Fórmulas e funções avançadas Agora, confira a solução com uma única fórmula, usando funções aninhadas: • Em C14, a função ÍNDICE precisará da matriz de números (G7:L14), da posição da linha e da posição da coluna. No local do 2º e 3º argumentos da função, você precisará incluir a função CORRESP para realizar a tarefa de obter as posições: =ÍNDICE(G7:L14; CORRESP(C8;F7:F14;0); CORRESP(C10;G6:L6;0)) 6.2. Aplicações para a função DESLOC Painéis de indicadores usam de maneira bem intensa intervalos nomeados, tabelas, listas etc. Quando esses intervalos podem apontar para locais diferentes ou mudar de tamanho, a função DESLOC é fundamental para essas matrizes e listas. Confira a seguir algumas aplicações importantes da função. A função DESLOC possui a seguinte sintaxe: =DESLOC(ref; lins; cols; [altura]; [largura]) Em que: ref A referência a partir da qual você deseja basear o deslocamento. Este argumento deve ser uma referência a uma célula ou intervalo de células adjacentes; caso contrário, DESLOC retornará o valor de erro #VALOR!. lins O número de linhas, acima ou abaixo, de onde começará o intervalo deslocado. Pode ser um número positivo (que significa abaixo da referência inicial) ou negativo (acima da referência inicial). cols O número de colunas, à esquerda ou à direita, de onde começará o intervalo deslocado. Pode ser um número positivo (que significa à direita da referência inicial) ou negativo (à esquerda da referência inicial). 44 Excel 2019 Em que: altura (opcional) A altura, em número de linhas, que se deseja para a referência fornecida. Quando não especificada, equivale a 1 célula. largura (opcional) A largura, em número de colunas, que se deseja para a referência fornecida. Quando não especificada, equivale a 1 célula. A função DESLOC é extremamente útil na criação de intervalos dinâmicos, os quais variam, conforme a quantidadede dados existentes. Veja um exemplo: No caso a seguir (ilustrado na Planilha2 do arquivo Funções de Procura e Referência. xlsx), deseja-se somar um intervalo de números que está deslocado de B2 em 4 linhas abaixo e 2 colunas à direita. O novo intervalo possui 3 células de altura por 2 células de largura: A soma na célula B12 será: =SOMA(DESLOC(B2; 4; 2; 3; 2)) No exemplo anterior, não há muito sentido em usar a função DESLOC porque o intervalo é visualmente conhecido. Ele serviu para mostrar o uso dos argumentos. Normalmente, DESLOC é usada quando você desconhece uma ou duas informações (incógnitas). Nesse caso, funções de procura e referência e de contagem auxiliam na descoberta desses valores, aninhando-as dentro de DESLOC. 45 Fórmulas e funções avançadas 6.2.1. Usando DESLOC para obter um intervalo deslocado No arquivo Funções de Procura e Referência.xlsx, Planilha3, observe a tabela com valores de vendas de uma cadeia de lojas, presente em alguns estados brasileiros, em 3 departamentos diferentes: A célula G4 será usada para digitação da sigla do estado de interesse. As células abaixo trarão resultados dos 3 departamentos daquele estado. O problema poderia ser facilmente resolvido pelas funções estatísticas condicionais vistas anteriormente, usando o Estado como critério. Isso só não será possível porque a tabela não está montada como um banco de dados e há células em branco na primeira coluna, impedindo que você a use para comparar os valores ao estado escolhido e, então, calcular a soma condicional. A solução poderá ser concretizada com auxílio da função DESLOC, a qual deverá localizar o intervalo de 3 células usado nos cálculos, conforme o estado escolhido. 46 Excel 2019 O que você sabe: • As siglas de estado estão sempre na coluna B, abaixo da célula B4 (célula de referência); • O intervalo dos valores a serem somados começa na mesma linha da sigla do estado escolhido. Para determinar a linha do estado dentro da tabela, use CORRESP para encontrar o estado na coluna B; • O intervalo dos valores começa 2 colunas à direita da sigla do estado; • O intervalo de valores possui 3 linhas de altura; • O intervalo de valores possui 1 coluna de largura. Portanto, a fórmula em G5, para calcular o total das vendas, ficará assim: =SOMA(DESLOC(B4; CORRESP(G4; B5:B40; 0); 2; 3; 1)) De forma similar, a maior venda será calculada com a seguinte fórmula: =MÁXIMO(DESLOC(B4; CORRESP(G4; B5:B40; 0); 2; 3; 1)) A menor venda será: =MÍNIMO(DESLOC(B4; CORRESP(G4; B5:B40; 0); 2; 3; 1)) E, finalmente, a média entre os departamentos: =MÉDIA(DESLOC(B4; CORRESP(G4; B5:B40; 0); 2; 3; 1)) O resultado dos cálculos será o seguinte: Observe que o intervalo apontado por DESLOC poderia ter sido criado por uma fórmula nomeada, o que reduziria bastante a digitação, conforme visto em outro exemplo adiante. 47 Fórmulas e funções avançadas 6.2.2. Usando DESLOC para recuperar uma célula em uma matriz Quando o intervalo deslocado for de apenas uma célula, a função DESLOC pode ser usada sozinha, como meio de obter um valor numa matriz, na qual são fornecidos os valores do deslocamento de linha e de coluna, partindo do canto superior esquerdo da tabela. Acompanhe o exemplo no arquivo Funções de Procura e Referência.xlsx, Planilha4: O intervalo numérico (matriz) é C7:N16. A célula de referência é B6. Na célula D4, deverá ser digitado o número da linha e, na célula G4, o número da coluna. Esses números representam, na realidade, os deslocamentos a partir da célula de referência. Como o intervalo resultante será de apenas uma célula, os dois últimos argumentos de DESLOC podem ser omitidos e serão iguais a 1. A célula J4 conterá a fórmula que traz o valor que se encontra na linha e coluna especificada: =DESLOC(B6; D4; G4) Essa é a única situação em que DESLOC pode ser usada sozinha em uma fórmula: quando aponta para uma única célula e traz seu valor como resultado. Quando DESLOC aponta para um intervalo, ela deve estar contida dentro de outra função que espera ter um intervalo como argumento. Para efeito de comparação, veja como a função ÍNDICE faria o mesmo trabalho: =ÍNDICE(C7:N16; D4; G4) 48 Excel 2019 6.2.3. Usando DESLOC para criar intervalos dinâmicos Uma das maiores vantagens de DESLOC é a montagem de um intervalo elástico para os bancos de dados. Dessa forma, se o banco de dados crescer em número de linhas, você não precisará reajustar as fórmulas para as novas dimensões do banco. Como exemplo, veja, no arquivo Funções de Procura e Referência.xlsx, Planilha5, um pequeno banco de dados de datas, regiões e vendas: A coluna C contém algumas fórmulas que calculam valores diretamente dos dados da tabela: Quando linhas novas são inseridas dentro da tabela, os intervalos são aumentados automaticamente. Porém, quando novas informações são digitadas na continuação do banco de dados, os intervalos não são ajustados automaticamente. 49 Fórmulas e funções avançadas A função DESLOC pode ser usada com esse benefício. Quando o intervalo usa a função, quaisquer novos dados acrescentados ao final do banco são imediatamente incluídos no intervalo e, portanto, as fórmulas não precisam ser reajustadas manualmente. No exemplo da Planilha5, você criará 2 intervalos nomeados: Datas e Vendas, correspondentes às colunas E e G do banco de dados, usando a função DESLOC para que eles se tornem "elásticos". Em seguida, você alterará as fórmulas da coluna C para usar esses 2 intervalos criados. Siga os passos: 1. Na guia Fórmulas, grupo Nomes Definidos, clique no botão Gerenciador de Nomes (ou use o atalho CTRL + F3). Em seguida, clique no botão Novo; 2. Na caixa de diálogo Novo Nome, na caixa Nome, escreva Datas. No campo Refere- se a, digite a seguinte fórmula: =DESLOC(Planilha5!$E$4; 1; 0; CONT.NÚM(Planilha5!$E:$E); 1) Em que: Plan5!$E$4 A célula de referência está na Planilha5, célula E4 (cabeçalho da coluna Data). 1 A quantidade de linhas abaixo da célula de referência, onde se inicia o intervalo. 0 A quantidade de colunas à direita da célula de referência, onde se inicia o intervalo (nenhum deslocamento horizontal). CONT.NÚM(Planilha5!E:E) Essa função calcula a quantidade de valores numéricos da coluna E. Isso significa a quantidade de linhas de altura. 1 O novo intervalo terá 1 coluna de largura. 3. Crie também o nome Vendas com o seguinte intervalo dinâmico: =DESLOC(Planilha5!$G$4; 1; 0; CONT.NÚM(Planilha5!$E:$E); 1) Se você observar, apenas a célula de referência muda (coluna), uma vez que as datas serão usadas para a contagem de números, o que fornece a quantidade de registros do banco de dados. 50 Excel 2019 As fórmulas da coluna serão, agora, alteradas para: Experimente acrescentar dados no final da tabela. Como exemplo, copie as quatro últimas linhas da tabela para abaixo dela. Você verá que o total de registros calculados em C4 mudou automaticamente de 14 para 18, assim como aconteceu com as outras fórmulas da coluna C: Quando criar intervalos dinâmicos, o único argumento da função DESLOC que você precisará calcular será a altura do novo intervalo (em linhas). Para isso, use funções de contagem e tome cuidado para não selecionar valores fora dos dados que possam interferir com a contagem, como títulos e totais, por exemplo. 51 Fórmulas e funções avançadas 6.3. Aplicação para a função INDIRETO Acompanhe o exemplo no arquivo Funções de Procura e Referência.xlsx, Planilha6. Observe a célula E4, a qual contém uma região, escolhida por validação de dados por lista: No mesmo arquivo, existem 4 outras planilhas: Norte, Leste, Oeste e Sul com tabelas de valores e totais por semestre e por ano: O objetivo deste exemplo é a criação de três fórmulas que tomem o nome de região escolhido na célula E4 da Planilha6 e montem um endereço de célula indiretamente com esse nome. 52 Excel 2019 Se a fórmula em E6 fosse montada manualmente para obtenção do valor do 1º semestre da planilha Norte, ela seria: =Norte!H16Como o nome da planilha varia conforme escolhido em E4, será necessário montar um texto com esse nome, o qual se encontra em E4, concatenado com o texto "!H16". Após a montagem desse texto, a função INDIRETO o transformará em seu endereço efetivo: =INDIRETO(E4&"!H16") Da mesma maneira, o valor para o 2º semestre seria montado assim: =INDIRETO(E4&"!O16") O valor para a célula que possui o total anual seria o seguinte: =INDIRETO(E4&"!P16") Para a região Norte escolhida em E4, os resultados finais serão: Agora, basta alterar a região para que as três fórmulas busquem indiretamente os valores na planilha. Note que você criou um mecanismo interativo evitando, assim, escrever 12 fórmulas separadas ou ter que criar uma tabela dinâmica para apresentar os resultados. Isso representa economia de espaço em seu dashboard, além de rapidez na modificação dos resultados. A função INDIRETO também converte um texto que representa um intervalo nomeado para o intervalo real. Por exemplo, se a célula C5 contiver o texto "Créditos" e o arquivo possuir um intervalo nomeado como Créditos, a fórmula =SOMA(INDIRETO(C5)) fará, na realidade, a soma de todas as células do intervalo chamado Créditos. 53 Fórmulas e funções avançadas Pontos principais Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes da leitura. • A base para a montagem de fórmulas mais sofisticadas é o perfeito conhecimento de como os endereços de células e intervalos são referenciados. O Excel trabalha com os seguintes tipos de endereço: absoluto ($A$1), relativo (A1) e misto ($A1, A$1); • Frequentemente, os bancos de dados do seu dashboard ficarão em planilhas diferentes ou mesmo em arquivos diferentes. A montagem das fórmulas pode seguir o mesmo princípio da facilidade: aponte para os endereços enquanto digita a fórmula. Isso garante que você selecionará o intervalo correto. No final da digitação, tecle ENTER para que a fórmula seja encerrada e deixada na planilha de origem; • O uso avançado de muitos recursos do Excel fica mais evidente quando as fórmulas ou referências são feitas com nomes ao invés de intervalos digitados explicitamente. As vantagens do uso de intervalos nomeados são claras: facilita a leitura das fórmulas, podem ser empregados em outras planilhas do mesmo arquivo e são facilmente organizados pelo Gerenciador de Nomes (acionado pela guia Fórmulas ou pelo atalho CTRL + F3); • As funções matemáticas e estatísticas mais comuns no Excel e que trabalham com intervalos de células são: SOMA, MÉDIA, MÁXIMO, MÍNIMO para estatísticas básicas; CONT.VALORES, CONT.NÚM, CONTAR.VAZIO para realizar contagens diversas. As funções matemáticas e estatísticas que trabalham com condições (critérios) são particularmente úteis na montagem de dashboards. Elas podem calcular uma única informação, ou fazer parte de uma tabela com fórmulas mais complexas, na qual nem sempre uma tabela dinâmica é a melhor alternativa; • As funções condicionais estão presentes no dia a dia de quem trabalha com o Excel, desde o nível básico. As principais são: SE, E, OU, XOR, NÃO, SE aninhada, SES, SEERRO e SENÃODISP; • Uma função que pertence à categoria de procura e referência retorna um valor de uma tabela ou matriz, através da localização de outro valor ou, então, por sua posição de linha e coluna dentro da matriz. Teste seus conhecimentos Fórmulas e funções avançadas 56 Excel 2019 1. O arquivo Vendas.xlsx possui uma fórmula na célula B5, a qual se refere à soma do intervalo $H$2:$H14 da Planilha1 do arquivo Relatório.xlsx. Como deve ser escrita a fórmula na célula B5 no arquivo Vendas.xlsx? ☐ a) =SOMA($H$2:$H$14) ☐ b) =SOMA(Planilha1!$H$2:$H$14) ☐ c) =SOMA([Relatório.xlsx]$H$2:$H$14) ☐ d) =SOMA([Relatório.xlsx]Planilha1!$H$2:$H$14) ☐ e) Nenhuma das alternativas anteriores está correta. 2. Quais funções realizam somas seletivas, ou seja, baseadas em um ou mais critérios? ☐ a) SOMA e SUBTOTAL. ☐ b) SOMASE e SOMASES. ☐ c) BDMÉDIA, BDCONTAR e BDEXTRAIR. ☐ d) MAIOR e MENOR. ☐ e) Nenhuma das alternativas anteriores está correta. 3. Qual a alternativa correta? ☐ a) PROCV, PROCH, ÍNDICE e CORRESP pertencem à categoria Funções Estatísticas. ☐ b) ÍNDICE e CORRESP pertencem à categoria de Funções Lógicas. ☐ c) DESLOC, ENDEREÇO e INDIRETO são funções da categoria Funções de Procura e Referência. ☐ d) SE, E, OU, NÃO, SEERRO pertencem à categoria de Funções Matemáticas. ☐ e) Nenhuma das alternativas anteriores está correta. 57 Fórmulas e funções avançadas 4. Qual a sintaxe que se refere a um intervalo deslocado de B2 em cinco linhas para baixo, 3 colunas para a direita e possui quatro células de altura por seis de largura? ☐ a) DESLOC(B2; 5; 3; 4; 6) ☐ b) DESLOC(B2; -5; 3; 6; 4) ☐ c) DESLOC(B2; 5; 3) ☐ d) DESLOC(5; 3; 4; 6) ☐ e) Nenhuma das alternativas anteriores está correta. 5. Como a função PROCV não consegue realizar buscas para a esquerda da coluna pesquisada na matriz-tabela, que alternativa pode ser usada no lugar? ☐ a) A função PROCH. ☐ b) As funções ÍNDICE e CORRESP usadas em conjunto. ☐ c) A função INDIRETO. ☐ d) Não há alternativas para buscar valores à esquerda da coluna pesquisada. ☐ e) Nenhuma das alternativas anteriores está correta. Mãos à obra! Fórmulas e funções avançadas 60 Excel 2019 Os exercícios desta seção complementam o aprendizado. Faça-os usando os conceitos aprendidos na leitura Fórmulas e funções avançadas. Cada exercício contém as instruções necessárias para você completar as atividades, bem como menção aos arquivos que devem ser abertos, caso necessário. Laboratório 1 A – Utilizando fórmulas e funções avançadas Para este exercício, abra o arquivo Exercícios de Laboratório - Capítulo 2.xlsx onde há apenas uma planilha. Você verá uma base de dados, contendo o Mês, a Região e as Vendas: Um dos indicadores de um dashboard diz respeito aos melhores em cada período. Neste exercício, você deverá escolher um mês na célula D4 e, em seguida, a tabela abaixo de D4 mostrará as posições das três melhores vendas para o mês escolhido, bem como as regiões responsáveis por elas: Para resolver este exercício, siga as orientações adiante: 61 Fórmulas e funções avançadas 1. Use a lista de meses da coluna N para aplicar validação de dados por lista à célula D4; 2. A tabela nas colunas G e H possui a relação de todas as regiões que aparecem na base de dados: 3. Calcule, na coluna H, a soma das vendas usando como critérios o nome da região ao lado esquerdo e o mês escolhido em D4; 4. Nas células D7, D8 e D9, calcule, respectivamente, o 1º maior valor de vendas, o 2º maior valor de vendas e o 3º maior valor de vendas. Use como ordem os números da coluna B; 5. Finalmente, na coluna C, localize cada valor de vendas na tabela de vendas mensais e traga a região correspondente. Quando você terminar, a planilha ficará assim: Ferramentas de interatividade à Validação de dados por lista; à Controles de formulário; à Exemplos de aplicação; à Navegação por links. 64 Excel 2019 1. Introdução Um dos aspectos principais em um dashboard é a interatividade. É o que o diferencia de um relatório gerencial estático. Interagir significa, além de digitar, escolher opções ou mudar valores dinamicamente. Tudo isso pode ser feito diretamente com dois tipos de ferramenta: validação de dados por lista e por controles de formulário. 2. Validação de dados por lista Uma das utilidades da validação de dados é restringir os dados que você pode inserir em uma célula a itens predefinidos em uma lista, a números dentro de um intervalo definido ou a datas e horas dentro de um período definido. Neste curso, a validação de dados por lista é uma rápida e fácil ferramenta para escolha de opções em uma lista de valores. A fonte de uma lista só pode conter células sequenciais que pertençam a uma mesma linha ou a uma mesma coluna. Além disso, você
Compartilhar