Fórmulas e funções avançadas no Excel, Manuais, Projetos, Pesquisas de Microsoft Excel

Baixe Fórmulas e funções avançadas no Excel e outras Manuais, Projetos, Pesquisas em PDF para Microsoft Excel, somente na Docsity!

________________________________________________________________________

• 1. O que são dashboards?..................................................................... Sumário
• 2 Funções matemáticas e estatísticas....................................................
• 2.1. Funções estatísticas condicionais.......................................................
• 3 Funções condicionais.........................................................................
• 3.1. Valores lógicos e testes lógicos...........................................................
• 3.2. Função condicional SE.......................................................................
• 3 .3. Função auxiliar E...............................................................................
• 3 .4. Função auxiliar OU............................................................................
• 3 .5. Função auxiliar XOR..........................................................................
• 3 .6. Função auxiliar NÃO..........................................................................
• 3 .7. Função SE aninhada..........................................................................
• 3 .8. Funções de informação......................................................................
• 3 .9. Função SEERRO................................................................................
• 3 .10 Função SENÃODISP..........................................................................
• 3 .11 A tabela da verdade..........................................................................
• 3 .12 Precedência dos operadores..............................................................
• 4 Funções de procura e referência.........................................................
• 4 .1. Função PROCV..................................................................................
• 4 .2. Função PROCH..................................................................................
• 4 .3. Função PROC....................................................................................
• 4 .4. Função CORRESP..............................................................................
• 4 .5. Função ÍNDICE.................................................................................
• 4 .6. Funções ÍNDICE e CORRESP usadas em conjunto................................
• 4 .7. Função ESCOLHER............................................................................
• 4 .8. Função DESLOC................................................................................
• 4 .8. 1 Usando DESLOC para obter um intervalo deslocado...........................
• 4.8.2. Usando DESLOC para recuperar uma célula numa matriz....................
• 4.8.3. Usando DESLOC para criar intervalos dinâmicos.................................
• 4 .9. Função ENDEREÇO............................................................................
• 4 .10 Função INDIRETO............................................................................
• 4 .11. Funções LIN, COL, LINS, COLS..........................................................
• 5 Funções de texto...............................................................................
• 5 .1. Concatenação de texto......................................................................
• 5 .2. Maiúsculas e minúsculas....................................................................
• 5 .3. Operações com caracteres.................................................................
• 5 .4. Formatação de números....................................................................
• 5 .5. Extração de texto..............................................................................
• 5 .6. Localização e substituição de caracteres.............................................
• 5 .7. Outras funções..................................................................................
• 5 .8. Pontos principais...............................................................................

---

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:

---

 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;

---

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; "")

---

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 ) ) )

---

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 uma fó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.

---

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 B 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)

---

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

---

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])

---

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 C 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:

---

=ÍNDICE(B3:J3; C5) Quando a matriz for 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.

---

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))

---

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")