Prévia do material em texto
Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 2 Sumário 1. O que são dashboards?..................................................................... 03 2. Funções matemáticas e estatísticas.................................................... 04 2.1. Funções estatísticas condicionais....................................................... 04 3. Funções condicionais......................................................................... 05 3.1. Valores lógicos e testes lógicos........................................................... 05 3.2. Função condicional SE....................................................................... 06 3.3. Função auxiliar E............................................................................... 07 3.4. Função auxiliar OU............................................................................ 07 3.5. Função auxiliar XOR.......................................................................... 08 3.6. Função auxiliar NÃO.......................................................................... 08 3.7. Função SE aninhada.......................................................................... 08 3.8. Funções de informação...................................................................... 10 3.9. Função SEERRO................................................................................ 11 3.10 Função SENÃODISP.......................................................................... 12 3.11 A tabela da verdade.......................................................................... 13 3.12 Precedência dos operadores.............................................................. 13 4. Funções de procura e referência......................................................... 14 4.1. Função PROCV.................................................................................. 14 4.2. Função PROCH.................................................................................. 14 4.3. Função PROC.................................................................................... 15 4.4. Função CORRESP.............................................................................. 15 4.5. Função ÍNDICE................................................................................. 16 4.6. Funções ÍNDICE e CORRESP usadas em conjunto................................ 17 4.7. Função ESCOLHER............................................................................ 20 4.8. Função DESLOC................................................................................ 21 4.8.1 Usando DESLOC para obter um intervalo deslocado........................... 22 4.8.2. Usando DESLOC para recuperar uma célula numa matriz.................... 25 4.8.3. Usando DESLOC para criar intervalos dinâmicos................................. 26 4.9. Função ENDEREÇO............................................................................ 30 4.10 Função INDIRETO............................................................................ 31 4.11. Funções LIN, COL, LINS, COLS.......................................................... 33 5. Funções de texto............................................................................... 33 5.1. Concatenação de texto...................................................................... 34 5.2. Maiúsculas e minúsculas.................................................................... 34 5.3. Operações com caracteres................................................................. 35 5.4. Formatação de números.................................................................... 36 5.5. Extração de texto.............................................................................. 37 5.6. Localização e substituição de caracteres............................................. 38 5.7. Outras funções.................................................................................. 38 5.8. Pontos principais............................................................................... 41 Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 3 1. O que são dashboards? O Excel é uma ferramenta excepcional, capaz de efetuar cálculos complexos e tornar uma grande quantidade de números em informações úteis. Quando você 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. Há anos o conceito de BI vem tomando conta das empresas, onde os gestores precisam rapidamente tornar 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. Da mesma maneira que o painel de um automóvel exibe diversos indicadores (combustível, óleo, quilometragem rodada, etc.), um painel criado no computador apresenta dados críticos numa 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) que geralmente custa centenas de milhares de reais, 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 4 2. Funções matemáticas e estatísticas 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. O foco neste tópico são as funções matemáticas e estatísticas que trabalham com condições (critérios), as quais são particularmente úteis na montagem de dashboards. Essas funções 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. Na apresentação da sintaxe de uma função, quando aparecer algum argumento entre colchetes, isso indica que ele é opcional e pode ser omitido. Se, no final da função, a sintaxe mostrar reticências (...), isso indica que a função poderá continuar com mais argumentos. 2.1. Funções estatísticas condicionais As funções que realizam somas, médias e contagens condicionais são resumidas a seguir: Função SOMASE: Adiciona as células especificadas por um determinado critério dentro de um intervalo: Função MÉDIASE: Retorna a média aritmética de todas as células de um intervalo que atendem a um determinado critério: Função CONT.SE: Calcula o número de células não vazias em um intervalo que corresponde a determinado critério: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 5 Função SOMASES: Adiciona as células em um intervalo, as quais atendem a vários critérios: Função MÉDIASES: Retorna a média aritmética de todas as células que atendem a vários critérios: Função CONT.SES: Conta o número de células não vazias dentro de um intervalo que atende a múltiplos critérios: 3. 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 principais funções condicionais, além de algumas outras que podem ser úteis na elaboração de painéis de indicadores. 3.1. Valores lógicos e testes lógicos Uma célula do Excel é capaz de conter um único dado. Esta 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. 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; Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 6 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 duas funções auxiliares: E e OU. Estas funções conseguem avaliar simultaneamente até 255 testes lógicos. 3.2. Função condicional 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 avaliado como FALSO. A sintaxe é a seguinte: =SE(teste_lógico; valor_se_verdadeiro; valor_se_falso) Veja um exemplo: =SE(B5>B4; 5%*B5; 0) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 7 A função SE também pode ser usada para escolher intervalos: =SOMA(SE(C5>0; D:D; E:E)) 3.3. Função auxiliar E Essa função retornará VERDADEIRO se todos os seus argumentos forem avaliados como VERDADEIRO e retornará FALSO se um ou mais argumentos forem avaliados como FALSO. Sua sintaxe é a seguinte: =E(teste_lógico1; teste_lógico2; ...) Veja um exemplo: =SE(E(C5>0; D5>0); C5/D5; "") 3.4. Função auxiliar OU Esta função retornará VERDADEIRO se ao menos um de seus argumentos for avaliado como VERDADEIRO e retornará FALSO se todos os argumentos forem avaliados como FALSO. Sua sintaxe é a seguinte: =OU(teste_lógico1; teste_lógico2; ...) Veja um exemplo: =SE(OU(C5>0; D5>0); C5/D5; "") Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 8 3.5. Função auxiliar XOR Essa função retornará VERDADEIRO se a quantidade de argumentos avaliados como VERDADEIRO for ímpar, senão, retornará FALSO. Sua sintaxe é a seguinte: =XOR(teste_lógico1; teste_lógico2; ...) Veja um exemplo: =SE(XOR(C5>0; D5>0); C5/D5; ""5) 3.6. Função auxiliar NÃO A função NÃO inverte o resultado de um teste lógico. Veja a sua sintaxe: NÃO(teste_lógico) 3.7. Função 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. Condições (lógica) Sintaxe para a função SE Se teste_lógico1 = VERDADEIRO; Então valor1; =SE( teste_lógico1; valor1; Senão, se teste_lógico2 = VERDADEIRO; Então valor2; SE( teste_lógico2; valor2; Senão, se teste_lógico3 = VERDADEIRO; Então valor3; SE( teste_lógico3; valor3; ... Senão valorN ... valorN ) ) ) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 9 Sintaxe completa: =SE( teste_lógico1; valor1; SE( teste_lógico2; valor2; SE( teste_ lógico3; valor3; ... valorN ) ) ) No exemplo a seguir, imagine que o valor das vendas está na célula B5 e que seja necessário escrever uma fórmula que calcule o valor do bônus em função do valor das vendas, de acordo com várias faixas de valores. Observe as condições escritas e compare-as com a função que será usada: Condições (lógica) Sintaxe Se as vendas forem inferiores a 1000; Então o bônus será 0; =SE(B5<1000; 0; Senão, se as vendas forem inferiores a 2000; Então o bônus será de 10% das vendas; SE(B5<2000; 10%*B5; Senão, o bônus será de 20% das vendas 20%*B5)) Ou seja, a fórmula seria: =SE(B5<1000; 0; SE(B5<2000; 10%*B5; 20%*B5)) Uma fórmula que usa testes lógicos com as funções auxiliares E e OU avalia todos os testes simultaneamente. Uma fórmula que usa a estrutura da função SE aninhada avalia um teste de cada vez. Neste caso, quando um teste for VERDADEIRO, o resultado é trazido e nenhum outro teste na sequência será avaliado. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 10 3.8. 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. É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. É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. ÉFÓRMULA Retorna VERDADEIRO se a referência contiver umafórmula. Exemplo: A célula B3 contém as vendas da Filial 1. A célula B4 contém as vendas de todas filiais. A célula B5 deverá ter a fórmula =B3/B4 que calcula a participação das vendas da Filial 1 sobre o total. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 11 Porém, a divisão somente deverá ser efetuada se ambos os valores não forem textos e se o denominador for um número maior que zero. Caso contrário B5 ficará em branco. A fórmula em B5 pode ser escrita desta maneira: =SE(E(É.NÃO.TEXTO(B3); É.NÃO.TEXTO(B4); B4>0); B3/B4; "") 3.9. Função SEERRO O objetivo da função SEERRO (introduzida no Excel 2007) é avaliar uma expressão ou cálculo. Se o resultado for um valor de erro, então ela retornará o que você usar como 2º argumento. Caso contrário, o próprio resultado da expressão será retornado à célula. Sua sintaxe é a seguinte: =SEERRO(expressão; valor_se_erro) Adiante, estão descritos os argumentos: expressão: É o argumento verificado quanto ao erro (geralmente uma fórmula); valor_se_erro: É o valor a ser retornado se a fórmula gerar um erro. Os seguintes tipos de erro são avaliados #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!. O exemplo a seguir calculará a expressão C5/D5 normalmente, caso não haja nenhum erro (#DIV/0!, por exemplo). Se houver, o texto nulo será retornado à célula: =SEERRO(C5/D5; "") Antes do Excel 2007, a expressão anterior só poderia ser escrita desta maneira: =SE(ÉERRO(C5/D5); ""; C5/D5) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 12 A função SEERRO traz uma boa economia de digitação, principalmente se a expressão for grande. 3.10. Função SENÃODISP O objetivo da função SENÃODISP (introduzida no Excel 2013) é avaliar uma expressão ou cálculo. Se o resultado for um valor #N/D, então ela retornará o que você usar como 2º argumento. Caso contrário, o próprio resultado da expressão será retornado à célula. Sua sintaxe é a seguinte: =SENÃODISP(expressão; valor_se_não_disp) Os argumentos estão descritos adiante: expressão: É o argumento verificado quanto ao erro (geralmente uma fórmula); valor_se_não_disp: É o valor a ser retornado se a fórmula gerar #N/D. O exemplo a seguir retornará o texto “Produto indisponível” se PROCV não conseguir encontrar um valor; caso contrário, retornará o resultado de PROCV. =SENÃODISP(PROCV(C5;H3:J23;2;FALSO); Produto indisponível.) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 13 3.11. A tabela da verdade Veja a combinação dos diversos resultados, quando são usados dois testes lógicos e as funções auxiliares vistas neste tópico. Função Teste lógico 1 Teste lógico 2 Resultado E VERDADEIRO VERDADEIRO VERDADEIRO E VERDADEIRO FALSO FALSO E FALSO VERDADEIRO FALSO E FALSO FALSO FALSO OU VERDADEIRO VERDADEIRO VERDADEIRO OU VERDADEIRO FALSO VERDADEIRO OU FALSO VERDADEIRO VERDADEIRO OU FALSO FALSO FALSO XOR VERDADEIRO VERDADEIRO FALSO XOR VERDADEIRO FALSO VERDADEIRO XOR FALSO VERDADEIRO VERDADEIRO XOR FALSO FALSO FALSO NÃO VERDADEIRO — FALSO NÃO FALSO — VERDADEIRO 3.12. Precedência dos operadores A seguinte tabela mostra a ordem de execução dos cálculos e avaliação de fórmulas no Excel. Os parênteses permitem alterar essa sequência: Símbolo Operador Intervalo (:), União (;), Intersecção ( ) Referência – Negação % Porcentagem ^ Exponenciação * e / Multiplicação e divisão + e – Adição e subtração & Concatenação de texto =, <, >, <=, >=, e <> Comparação Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 14 4. 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. Um exemplo de fácil associação é uma lista telefônica: se você deseja encontrar o número de telefone de alguém, primeiro você procura pelo nome dessa pessoa e, em seguida, verá o telefone na mesma linha. Existem várias funções úteis para efetuar buscas em tabelas e bancos de dados e são bastante usadas na montagem de dashboards, conforme resumido a seguir. 4.1. Função PROCV A função toma o valor_procurado e 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). • Sintaxe: =PROCV(valor_procurado; matriz_tabela; índice_coluna; [tipo_ procura]) 4.2. Função PROCH A função toma o valor_procurado e 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). • Sintaxe: =PROCH(valor_procurado; matriz_tabela; índice_linha; [tipo_procu- ra]) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 15 4.3. Funçã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. • Sintaxe: =PROC(valor_procurado; vetor_de_procura; [vetor_de_resultado]) O vetor de procura precisa estar classificado em ordem crescente. 4.4. Função CORRESP A função procura um item (valor_procurado) num intervalo (matriz_procurado) e retorna a posição relativa em que este item se encontra na lista. Sintaxe: =CORRESP(valor_procurado; matriz_procurada; [tipo_correspondên- cia]) O tipo_correspondência pode ser exata (0) ou aproximada (-1 ou 1). Use 1 quando o valor a se retornado for menor que o valor procurado ou use -1 quando o valor a se retornado for maior que o valor procurado. • Exemplo: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 16 Na imagem anterior, existe um intervalo com nomes de regiões. Na célula E3, está escrito o nome de uma das regiões e a célula E4 contém a fórmula que determina a posição relativa da região escolhida na lista de regiões: =CORRESP(E3; B3:B11; 0) 4.5. Função ÍNDICE A função 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. Sintaxe: =ÍNDICE(matriz; [núm_linha]; [núm_coluna]) Exemplos: Na imagem anterior, existe um intervalo com nomes de regiões. A célula C5 contém uma posição de região dentro da tabela e a célula C6 contém a fórmula que traz o valor da tabela que se encontra na posição especificada em C5: =ÍNDICE(B3:J3; ;C5) Como a matriz tem apenas uma linha (também chamada de vetor), o segundo argumento foi omitido e a posição indica a coluna de interesse dentro da matriz. A fórmula também funcionaria com apenas dois argumentos neste caso: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 17 =ÍNDICE(B3:J3; C5) Quando a matrizfor bidimensional, será necessário especificar os índices de linha e coluna, como no exemplo a seguir: Neste caso, as posições desejadas de linha e coluna estão escritas nas células B2 e B3. A matriz bidimensional de números está no intervalo D2:K6. A fórmula em B5 traz o valor que se encontra nas posições de linha e coluna especificada: =ÍNDICE(D2:K6; B2; B3) 4.6. 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 numa 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 18 • Exemplo: 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 19 Solução com várias fórmulas: 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) 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)) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 20 4.7. Função ESCOLHER Use ESCOLHER para selecionar um valor entre 254 valores que se baseie no número de índice. Sintaxe: =ESCOLHER(núm_índice; valor1; [valor2], ...) Exemplo: A célula C2 contém um número de 1 a 7, representando o dia da semana (domingo a sábado). Imagine que você necessita escrever o nome do dia da semana em espanhol, baseado no número em C2. Uma solução seria usar a função SE de forma aninhada: =SE(C2=1; "Domingo"; SE(C2=2; "Lunes"; SE(C2=3; "Martes";SE(C2=4; "Miércoles"; SE(C2=5; "Jueves"; SE(C2=6; "Viernes"; "Sábado")))))) A fórmula a seguir, mais simples, retorna o nome do dia da semana em espanhol, baseado no número em C2, usando a função ESCOLHER: =ESCOLHER(C2; "Domingo"; "Lunes"; "Martes"; "Miércoles"; "Jueves"; "Viernes"; "Sábado") Se você desejar que o dia da semana seja retornado em função da data de hoje: =ESCOLHER(DIA.DA.SEMANA(HOJE()); "Domingo"; "Lunes"; "Martes"; "Miércoles"; "Jueves"; "Viernes"; "Sábado") Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 21 4.8. Função DESLOC Esta função 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. Sintaxe: =DESLOC(ref; lins; cols; [altura]; [largura]) Argumentos: Ref: A referência a partir da qual você deseja basear o deslocamento. Ref 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, onde começará o intervalo deslocado. Lins 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, onde começará o intervalo deslocado. Cols pode ser um número positivo (que significa à direita da referência inicial) ou negativo (à esquerda da referência inicial); Altura (Opcional): A altura, em número de linhas, que se deseja para a referência fornecida; Largura (Opcional): A largura, em número de colunas, que se deseja para a referência fornecida. A função DESLOC é extremamente útil na criação de intervalos dinâmicos, os quais variam, conforme a quantidade de dados existentes. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 22 Exemplo: No caso a seguir 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)) 4.8.1. Usando DESLOC para obter intervalo deslocado Observe no banco de dados abaixo com estados brasileiros, capitais, população e quantidade de municípios: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 23 Na célula C5 você deverá escolher uma das cinco regiões. Defina uma validação de dados por lista para facilitar a escolha. Nas células abaixo, você criará fórmulas que retornem a maior população dentro da região escolhida e seu estado correspondente. Da mesma forma, a menor população e o maior número de municípios e seus estados correspondentes também serão determinados. Se fosse uma soma, a tarefa seria facilmente resolvida com a função SOMASE. Mas, neste caso, são máximos e mínimos. Como o Excel não possui as funções MÁXIMOSE e MÍNIMOSE, então uma solução neste caso será o uso da função DESLOC para encontrar o intervalo correto para obter os valores de máximo e mínimo. A fórmula para a maior população da região escolhida (célula C7) será obtida pela função MÁXIMO: =MÁXIMO(intervalo) O intervalo de valores de População, porém, deverá ser descoberto no banco de dados e será deslocado para baixo (partindo da célula I5), conforme a região e quantidade de estados que compõem a região: =MÁXIMO(DESLOC(I5; núm_linhas; 0; altura; 1)) Pela fórmula anterior: O deslocamento será a partir da célula I5 (cabeçalho da coluna População); A quantidade de linhas de deslocamento para baixo (núm_linhas) ainda é desconhecida; O deslocamento de colunas será 0, pois o intervalo será na mesma coluna da célula de referência I5; A altura do novo intervalo (quantidade de células) também édesconhecida; Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 24 Por fim, a largura do novo intervalo será de 1 coluna. O deslocamento para baixo (núm_linhas) pode ser obtido pela localização da primeira ocorrência do nome da região escolhida (C5) ao longo do intervalo E6:E32: CORRESP(C5; E6:E32; 0) A altura do intervalo deslocado resultante será determinado pelo número de ocorrências do nome da região escolhida (C5) ao longo do intervalo E6:E32: CONT.SE(E6:E32; C5) Finalmente, a fórmula que obtém a maior população será montada, aninhando- se as duas funções anteriores dentro da expressão original: =MÁXIMO(DESLOC(I5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) Da mesma forma, a menor população da região escolhida (célula C10) será obtida pela fórmula: =MÍNIMO(DESLOC(I5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) O maior número de municípios da região (célula C13) será calculado de maneira semelhante: =MÁXIMO(DESLOC(J5;CORRESP(C5;E6:E32;0);0;CONT.SE(E6:E32;C5);1)) Para o cálculo do nome do estado que corresponde à população calculada (célula C8), não será possível usar PROCV porque a coluna da População não é a primeira da tabela. Também não é possível usar PROC porque a coluna da população não está em ordem crescente. A solução neste caso é usar a função CORRESP para localizar a posição que a população calculada (C7) ocupa na coluna das populações (I6:I32): CORRESP(C7; $I$6:$I$32; 0) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 25 Em seguida, essa posição será usada na função ÍNDICE para obter o estado correspondente: =ÍNDICE($F$6:$F$32; CORRESP(C7; $I$6:$I$32; 0)) Alternativamente, você também pode usar esta fórmula: =DESLOC(F5; CORRESP(C7; I6:I32; 0); 0) Como os intervalos da fórmula com ÍNDICE e CORRESP usam endereçamento absoluto, é possível copiá-la para as células C11 e C14. A planilha finalizada, com escolha da região Sudeste ficará assim: O exemplo supõe que não há repetição de valores de população. 4.8.2. Usando DESLOC para recuperar uma célula numa 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 26 Acompanhe o exemplo abaixo: 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. A célula J4 conterá a fórmula que traz o valor que se encontra na linha e coluna especificada: =DESLOC(B6; D4; G4) 4.8.3. Usando DESLOC para criar intervalos dinâmicos Uma das maiores vantagens de DESLOC é a montagem de um intervalo elásticos para os bancos de dados. A vantagem é que, 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 27 Como exemplo, veja o caso abaixo: 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. 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 planilha acima, você criará três intervalos nomeados: Data, Região e Vendas, correspondentes às colunas do banco de dados. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 28 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 Data. No campo Refere-se a, digite a seguinte fórmula: =DESLOC(Plan5!$E$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) Os argumentos para a função DESLOC são: • Plan5!$E$4: A célula de referência está na planilha DESLOC3, 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 (nenhuma; é a própria coluna); CONT.NÚM(Plan5!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. Da mesma forma, crie o nome Região com a seguinte definição de intervalo: =DESLOC(Plan5!$F$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) 4. Crie também o nome Vendas com o seguinte intervalo dinâmico: =DESLOC(Plan5!$G$4; 1; 0; CONT.NÚM(Plan5!E:E); 1) Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 29 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. As fórmulas serão agora alteradas para: Experimente acrescentar dados no final da base. 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 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. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 30 4.9. Funçã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. Opcionalmente, pode-se fornecer a informação sobre o endereçamento (absoluto, relativo ou misto), sobre o estilo de representação do endereço (A1 ou L1C1) e se o intervalo virá de uma pasta de trabalho externa. O resultado da função é um texto que representa o endereço desejado. Sintaxe: =ENDEREÇO(Linha; Coluna; [Tipo_ref]; [Estilo_A1?]; [Texto_ planilha]) Argumentos: o Linha: Especifica o número de linha a ser usado na referência de célula; o Coluna: Especifica o número de coluna a ser usado na referência de célula; o Tipo_ref: Valor numérico que especifica o tipo de referência: 1 (absoluta), 2 (linha absoluta, coluna relativa), 3 (linha relativa, coluna absoluta) ou 4 (relativa); o Estilo_A1?: VERDADEIRO se o estilo de endereçamento for A1, FALSO se o estilo for L1C1; o Texto_planilha: Um texto que especifica o nome da planilha a ser usada como referênciaexterna. Se este argumento for omitido, nenhum nome de planilha será usado e o endereço retornado pela função fará referência a uma célula da planilha ativa. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 31 4.10. 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. As referências são imediatamente avaliadas para exibir seu conteúdo. A função é útil para mudar a referência a uma célula dentro uma fórmula, sem mudar a própria fórmula. O resultado da função é um endereço de uma célula ou intervalo. Sintaxe: =INDIRETO(Texto_ref; [Estilo_A1?]) No exemplo abaixo, você verá uma tabela com cinco produtos e valores para os doze meses do ano: O objetivo será escolher um produto na célula C5 e um mês na célula C6. As células C8 e C9 deverão conter fórmulas que calculem as somas para o produto escolhido e para o mês escolhido. A célula C10 deverá trazer o valor que se encontra na intersecção da linha do produto escolhido com a coluna do mês escolhido. Para resolver essa questão, é necessário seguir os passos adiante: 1. Crie nomes automaticamente para cada intervalo da tabela. Na guia Fórmulas, grupo Nomes Definidos, clique no botão Criar a partir da Seleção; Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 32 2. Mantenha selecionadas as caixas Linha superior e Coluna esquerda e clique em OK. Uma série de nomes será criada automaticamente. Você poderá conferir os nomes clicando na seta da Caixa de nome ou acionando o Gerenciador de Nomes na guia Fórmulas; 3. Na célula C8, preencha a fórmula que calcula a soma para o produto escolhido em C5: =SOMA(INDIRETO(C5)) 4. Na célula C9, preencha a fórmula que calcula a soma para o mês escolhido em C6: =SOMA(INDIRETO(C6)) 5. Na célula C10, preencha a fórmula que obtém o valor na intersecção da linha do produto com a coluna do mês. Você também pode usar a combinação das funções DESLOC com CORRESP: =DESLOC(E5; CORRESP(C5;E6:E10;0); CORRESP(C6;F5:Q5;0)) Alternativamente, você também poderia usar esta fórmula: =ÍNDICE(F6:Q10; CORRESP(C5;E6:E10;0); CORRESP(C6;F5:Q5;0)) Após a criação das fórmulas sua planilha ficará assim: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 33 4.11. Funções LIN, COL, LINS, COLS Sintaxe para LIN: =LIN(referência_de_célula) Sintaxe para COL: =COL(referência_de_célula) Sintaxe para LINS: =LINS(intervalo) Sintaxe para COLS: =COLS(intervalo) 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. LINS e COLS retornam, respectivamente, o número total de linhas e colunas de um intervalo. 5. Funções de texto As funções de texto são uma excelente ferramenta para tratar uma grande quantidade de dados, em que as edições manuais ficariam impraticáveis ou muito demoradas. Use funções de texto em bancos de dados para efetuar extração de informações, limpeza de textos com excesso de caracteres irrelevantes, concatenações, substituições e outras transformações. Após obter os textos desejados, eles podem ser copiados de volta para a base de dados como valores. Os subtópicos a seguir relacionarão brevemente as principais funções de texto. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 34 5.1. Concatenação de texto A função de concatenação une diversos conteúdo em um único texto. Quando um número é concatenado, este aparecerá sem qualquer formato: CONCATENAR(texto1; texto2; texto3;...) Exemplos: =CONCATENAR(A5;"— ";B5); = A5&" — "&B5 (usando o operador de concatenação “&”). A sintaxe a seguir repete o texto especificado no 1º argumento de acordo com o número de vezes dado pelo segundo parâmetro: REPT(texto; número) Exemplo: =REPT("X-"; 5) retorna o texto X-X-X-X-X-. 5.2. Maiúsculas e minúsculas A função Maiúscula retorna o mesmo texto com todas as letras maiúsculas. Veja a sua sintaxe: MAIÚSCULA(texto) Exemplo: =MAIÚSCULA("Excel") retorna o texto EXCEL. Já a função Minúscula retorna o mesmo texto com todas as letras minúsculas. Veja a sua sintaxe: MINÚSCULA(texto) Exemplo: =MINÚSCULA("Excel") retorna o texto excel. A função a seguir retorna o mesmo texto com cada palavra iniciando com letra maiúscula: Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 35 PRI.MAIÚSCULA(texto) Exemplo: =PRI.MAIÚSCULA("aula de Excel") retorna o texto Aula De Excel. 5.3. Operações com caracteres A função a seguir retorna o número de caracteres para o texto especificado: NÚM.CARACT(texto) Exemplo: =NÚM.CARACT("Aula de Excel") retorna o valor 13. A próxima função retorna o caractere (ou símbolo) que possui o código numérico especificado pela tabela ASCII: CARACT(código) Exemplo: =CARACT(174) retorna o caractere ® (marca registrada). A função Código retorna o código numérico ASCII do primeiro caractere do texto especificado. Veja sua sintaxe: CÓDIGO(texto) Exemplo: =CÓDIGO("Aula de Excel") retorna o número 65 (código para a letra A). A função adiante retorna o caractere Unicode referenciado pelo determinado valor numérico: CARACTUNICODE(número) Exemplo: =CARACTUNICODE(8771) retorna o caractere ≃. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 36 A função a seguir retorna o código Unicode do primeiro caractere em texto: UNICODE(texto) Exemplo: =UNICODE("ABC") retorna o código 65. 5.4. Formatação de números A função Valor retorna o conteúdo de texto fornecido como número (deve conter apenas dígitos numéricos, símbolo de moeda R$, pontos separadores de milhar e vírgula): VALOR(texto) Exemplo: =VALOR("1.234,56") retorna o número 1234,56. A próxima função retorna o conteúdo do texto fornecido como número. O argumento separador_decimal define qual é o caractere que separa a parte inteira da decimal; já o argumento separador_grupo define qual é o caractere que separa os milhares. VALORNUMÉRICO(texto; [separador_decimal], [separador_grupo]) Exemplo: =VALORNUMÉRICO("1,234.56";".";",") retorna o número 1234,56. Esta função retorna um texto que representa um número formatado com a quantidade de decimais especificada e define se haverá ou não separador de milhar: DEF.NÚM.DEC(número; [decimais] ; [sem_sep_milhar]) Exemplo: =DEF.NÚM.DEC(12345,6789; 2; FALSO) retorna o texto 12.345,68. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 37 A função a seguir toma um número e retorna um texto formatado como moeda, com duas casas decimais (por omissão do 2º argumento): MOEDA(número; [decimais]) Exemplo: =MOEDA("2355,6") retorna o texto R$ 2.355,60. A função Texto toma um número e retorna um texto formatado segundo o formato especificado: TEXTO(número; formato) Exemplo: =TEXTO(C5; "dddd, dd/mm/aaaa") retorna uma data formatadacomo texto. Se C5 contém a data 01/01/2014, a expressão retorna o texto quarta-feira, 01/01/2014; Exemplo: =TEXTO(300/1800; "0,0%") retorna o texto 16,7%. 5.5. Extração de texto A função adiante extrai os n primeiros caracteres em texto: ESQUERDA(texto; n) Exemplo: =ESQUERDA("Aula de Excel"; 4) retorna o texto Aula. A função a seguir extrai os n últimos caracteres em texto: DIREITA(texto; n) Exemplo: =DIREITA("Aula de Excel"; 5) retorna o texto Excel. A próxima função extrai n caracteres em texto, a partir da posição inicial especificada: EXT.TEXTO(texto; pos_inicial; n) Exemplo: =EXT.TEXTO("Aula de Excel";6;2) retorna o texto de. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 38 5.6. Localização e substituição de caracteres A função a seguir retorna a posição que texto1 ocupa em texto2, partindo da posição núm_inicial. Maiúsculas e minúsculas são ignoradas. LOCALIZAR(texto1; texto2; [núm_inicial]) Exemplo: =LOCALIZAR("EXCEL"; "Aula de Excel";1) retorna o valor 9. A função adiante extrai em texto1, a partir da posição especificada (pos_inicial), a quantidade de caracteres definida por núm_caract e insere no local os caracteres definidos em texto2. MUDAR(texto1; pos_inicial; núm_caract; texto2) Exemplo: =MUDAR("Aula de Excel"; 1; 4; ("Curso") retorna o texto Curso de Excel. A função a seguir substitui, dentro de texto1, os caracteres do texto2 para os do texto3. SUBSTITUIR(texto1; texto2; texto3) Exemplo: =SUBSTITUIR("Curso de Excel";"curso";"Aula") retorna o texto Aula de Excel. 5.7. Outras funções A função adiante remove todos os espaços extras de um texto. ARRUMAR(texto) Exemplo: =ARRUMAR("Aula de Excel ") retorna Aula de Excel. Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 39 A próxima função remove todos os caracteres não imprimíveis de um texto (geralmente caracteres de controle, tabulações, quebras de linha, quebras de página, etc.): TIRAR(texto) Exemplo: =TIRAR(B5) retorna o texto da célula B5 sem os caracteres que não podem ser impressos. A função a seguir retorna VERDADEIRO se os dois textos são idênticos, caso contrário retorna FALSO. Maiúsculas e minúsculas são consideradas letras diferentes. EXATO(texto1; texto2) Exemplo: =SE(EXATO(B2; C2); "Confere"; "Não Confere”). Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 40 5.8. Pontos importantes • 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 endereçamento (referência) absoluto, relativo e misto; • Frequentemente as bases 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, 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; Fórmulas e funções avançadas para criação de dashboards no Excel ________________________________________________________________________ 41 As funções de texto são uma excelente ferramenta para tratar uma grande quantidade de dados, em que as edições manuais ficariam impraticáveis ou muito demoradas. Use funções de texto em bancos de dados para efetuar extração de informações, limpeza de textos com excesso de caracteres irrelevantes, concatenações, substituições e outras transformações. Após obter os textos desejados, eles podem ser copiados de volta para a base de dados como valores.