O Excel é uma ótima ferramenta para relatar, analisar, organizar e automatizar dados. As Funções do Excel ajudam muito no trabalho com dados. As funções como COUNTIFS, SUMIFS, VLOOKUP, etc, são as funções mais poderosas e mais usadas desde o início no mundo do Excel.
Embora as funções disponíveis no Excel 2016 e anteriores sejam suficientes para realizar qualquer tipo de cálculo e automação, às vezes as fórmulas ficam complicadas. Por exemplo, se você não encontrar o valor máximo com algumas condições, você terá que usar alguns truques na versão 2016 mais antiga do Excel. Esses tipos de coisas pequenas, mas importantes, são resolvidos no Excel 2019 e 365.
Existem mais de 10 novas funções no Excel 2019 e 365 que reduzem o esforço humano e a complexidade das fórmulas.
1. A função MAXIFS
No excel 2016 e anteriores, se você deseja obter o valor máximo em um intervalo quando uma ou mais condições correspondem, você deve usar MAX com IF com alguns truques. Isso não é muito difícil, mas demorado e confuso para alguns.
O Excel 2019 apresenta uma nova função chamada função MAXIFS. Esta função retorna o valor máximo de uma matriz quando todas as condições fornecidas são correspondidas.
A sintaxe da função é:
| = MAXIFS (intervalo_max, intervalo_critérios1, intervalo_critérios1, intervalo_critérios2, critérios2…) |
Max_range1: É o intervalo numérico que contém o valor máximo.
Intervalo_de_critérios1: É o intervalo de critérios que você deseja filtrar antes de obter o valor máximo.
Criteria1: É o critério ou filtro que você deseja colocar em intervalo_de_criterios antes de obter o valor máximo.
Suponha que você precise obter as notas máximas da classe 3, então a fórmula será
| = MAXIFS (marcas, classe, 3) |
Aqui, as marcas são o intervalo nomeado que contém as marcas e a classe é o intervalo nomeado que contém a classe.
Leia sobre a função MAXIFS em detalhes aqui.
2. A função MINIFS
Da mesma forma que a função MAXIFS, a função MINIFS é usada para obter o valor mínimo do intervalo fornecido quando todas as condições fornecidas são satisfeitas.
A sintaxe da função é:
| = MINIFS (intervalo_min, intervalo_critérios1, intervalo_critérios1, intervalo_critérios2, critérios2… |
Min_range1: É o intervalo numérico que contém o valor mínimo.
Intervalo_de_critérios1: É o intervalo de critérios que você deseja filtrar antes de obter o valor mínimo.
Criteria1: É o critério ou filtro que você deseja colocar em intervalo_de_criterios antes de obter o valor mínimo.
Suponha que você precise obter as notas mínimas da classe 3, então a fórmula será
| = MINIFS (marcas, classe, 3) |
Aqui, "marcas" é o intervalo nomeado que contém as marcas e "classe" é o intervalo nomeado que contém a classe.
Leia sobre a função MAXIFS em detalhes aqui.
Para encontrar o valor mínimo dentro da faixa com as condições no Excel 2016 e anteriores, leia isto.
3. A Função IFS
Como os Ifs aninhados ocupam um lugar especial em nossa vida profissional diária, gostamos muito deles. Mas para alguns novos alunos, é complexo. Os ifs aninhados nos permitem verificar várias condições e retornar um valor diferente quando qualquer uma das condições for atendida. As fórmulas ficam complexas com cada vez mais IF em função.
O Excel 2019 e o Excel 365 agora usam a função IFS. Ele pode verificar várias condições e retornar valores diferentes para cada condição.
Sintaxe da função IFS:
| = IFS (condição1, Valor1_If_Verdadeiro, [condição2, Valor2_If_Verdadeiro],…) |
Condição1:A primeira condição.
Value1_If_True: Valor se a primeira condição for verdadeira.
[Condição2]: Isso é opcional. A segunda condição, se houver alguma.
[Value1_If_True]: Valor se a segunda condição for verdadeira.
Você pode ter quantas combinações de condições e valores desejar. Há um limite, mas você nunca precisará alcançá-lo.
Digamos que você precise dar as notas aos alunos por suas notas. Para notas superiores a 80, grau A, B para mais de 60, C para mais de 40 e F para menos ou igual a 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Uma explicação detalhada da função IFS pode ser encontrada aqui.
4. A função SWITCH
A função switch retorna valores diferentes dependendo dos resultados de uma expressão. Parece IFS? É meio que. Na verdade, essa função é para substituir outro tipo de fórmulas IF aninhadas.
Ao contrário da função IFS que retorna valores com base em TRUE, FALSE; a função SWITCH retorna valores com base nos VALORES retornados pela expressão.
| = SWITCH (expressão, valor1, resultado1, [padrão ou valor2, resultado2], …) |
Expressão: Pode ser qualquer expressão válida que retorne alguns valores. Uma referência de célula, uma fórmula ou valor estático.
Valor1, resultado1: O valor e o resultado estão emparelhados. Se o valor retornado peloexpressão for valor1, o resultado1 será retornado.
[Padrão ou valor2, resultado2]: Se você deseja retornar algum valor padrão, defina-o aqui. Caso contrário, defina o valor2 e o resultado2. É opcional.
Por exemplo, se você tiver uma fórmula que retorna os nomes de animais. Agora, dependendo do nome do animal retornado, você deseja retornar o som da assinatura daquele animal.
| = SWITCH (A1, "Cachorro", "Bow Wow", "Gato", "Miau", "Fala") |
Eu expliquei a função SWITCH em detalhes aqui.
5. A função FILTER
A função FILTER é usada para filtrar dados com base em alguns critérios. Usamos a opção de filtro da guia inicial do Excel. A função FILTER funciona da mesma forma que a opção de filtro. Ele apenas retorna os dados filtrados usando uma função. Esses dados filtrados podem ser usados como fonte de dados para outras fórmulas.
A sintaxe da função FILTER é:
| = FILTRO (matriz, incluir, [se_empty]) |
Variedade: Esta é a matriz que você deseja filtrar. Pode ser unidimensional ou bidimensional.
Incluir:É o filtro que você deseja colocar no Array. Como cores = "vermelho".
[if_empty]:Isso é opcional. Defina qualquer texto ou expressão se o filtro não retornar nada.
A fórmula abaixo retorna todas as frutas cuja cor é vermelha.
| = FILTRO (frutas, cor = "vermelho", "nenhuma fruta encontrada") |
Aqui fruta e cor são intervalos nomeados que contêm os nomes das frutas e suas cores, respectivamente.
Você pode ler sobre a função FILTER em detalhes aqui.
6. A função SORT
No excel 2016 e anteriores, era realmente complicado obter uma matriz classificada usando uma fórmula. Esse processo é simplificado no Excel 2019 e 365.
O Excel 2019 apresenta a função SORT. A função SORT classifica o array fornecido em ordem crescente ou decrescente pela coluna / linha fornecida.
A sintaxe da função SORT é:
| = SORT (matriz, [índice_de_classificação], [ordem_de_classificação], [por_col]) |
Variedade:É a referência da matriz ou intervalo que você deseja classificar.
[sort_index]:O número da coluna na matriz bidimensional pela qual você deseja classificar o intervalo. Por padrão, é 1.
[ordem de classificação]:A ordem pela qual você deseja classificar a matriz. Para subir é 1 e para descer é -1. Por padrão, é 1.
[by_col]:Defina como True (1) se quiser classificar uma matriz horizontal. Por padrão, é False (0) para dados verticais.
Digamos que você queira classificar os valores no intervalo A2: A11 de forma crescente. então a fórmula será.
| = CLASSIFICAR (A2: A11) |
Eu expliquei a função SORT em detalhes aqui.
7. A função SORTBY
A função SORTBY é semelhante à função SORT. A única diferença é que a matriz de classificação não precisa fazer parte da matriz classificada na função SORTBY.
| = SORTBY (matriz, sorting_array1, [ordem], …) |
Variedade:Esta é a matriz que você deseja classificar.
Sorting_array1:Esta é a matriz pela qual você deseja classificar a matriz. A dimensão desta matriz deve ser compatível com o variedade.
[pedido]:Opcional. Defina-o como -1 se quiser que a ordem seja decrescente. Por padrão, é crescente (1).
Digamos que você queira classificar o intervalo A2: A11 pelo intervalo B2: B11, em ordem decrescente. Então, a fórmula no Excel 2019 ou 365 será:
| = SORTBY (A2: A11, B2: B11, -1) |
Eu expliquei a função SORTBY aqui em detalhes.
8. A função UNIQUE
No Excel 2016 e anteriores, usamos várias funções em combinação para obter todos os valores exclusivos da lista fornecida. A fórmula usada é bastante complexa e difícil de entender.
O Excel 2019 e 365 apresenta uma função UNIQUE simples que retorna todos os valores exclusivos de uma determinada matriz.
A sintaxe da função UNIQUE é:
| = UNIQUE (matriz, [por_col], [exatamente_uma vez]) |
Variedade: A matriz da qual você deseja extrair valores únicos:
[by_col]: Defina como TRUE (1) se a matriz for horizontal. Por padrão, é FALSE para dados verticais.
[exatamente uma vez]: defina-o como TRUE (1) se quiser extrair valores que ocorrem apenas uma vez no array. Por padrão, é FALSE (0) para extrair todos os valores exclusivos.
Digamos que eu queira obter apenas uma instância de cada valor do intervalo A2: A11, então a fórmula será:
| = ÚNICO (A2: A11) |
Para ler sobre a função UNIQUE em detalhes, você pode clicar aqui.
9. A função SEQUENCE
Para obter uma sequência de números no Excel 2016 e anteriores, usamos uma combinação de funções. A solução funciona, mas é complexa.
O Excel 2019 e 365 fornece a solução na forma da função SEQUENCE. A função de sequência simplesmente retorna a série do número.
A sintaxe da função SEQUENCE é:
| = SEQUENCE (linhas, [colunas], [início], [etapa]) |
Linhas:O número de linhas para as quais você deseja espalhar a sequência.
[coluna]:O número de colunas para as quais você deseja espalhar a sequência. Os números irão primeiro preencher as colunas e depois as linhas. A coluna é opcional. Por padrão, é 1.
[começar]:Opcional. O número inicial da sequência. Por padrão, é 1.
[Passo]:Este é o número de incremento para o próximo número. Por padrão, é 1.
O exemplo simples é obter uma série de 1 a 10. A fórmula será:
| = SEQUÊNCIA (10) |
Para entender a função SEQUENCE no Excel 365 em detalhes, leia isto.
10. A função RANDARRAY
Esta é outra fórmula de matriz dinâmica que retorna uma matriz de números aleatórios. É uma combinação das funções RAND e RANDBETWEEN. Você pode obter números aleatórios fracionários ou números inteiros. Você pode especificar o número de números aleatórios que deseja. Até mesmo as linhas e colunas nas quais você deseja distribuir esses números.
A sintaxe da função RANDARRAY é:
| = RANDARRAY ([linhas], [colunas], [min], [max], [inteiro]) |
Todos os argumentos nesta função são opcionais. Por padrão, funciona como a função RAND.
[linhas]:O número de números que você deseja verticalmente (número de linhas que deseja preencher).
[colunas]:O número de números que você deseja horizontalmente (número de colunas que deseja preencher).
[min]:O número inicial ou o valor mínimo do (s) número (s) aleatório (s).
[max]:O intervalo máximo do número.
[inteiro]:Defina como verdadeiro, se quiser que os números aleatórios sejam números inteiros. Por padrão, é falso e retorna números aleatórios fracionários.
A função abaixo retornará cinco números fracionários aleatórios por linha:
| = RANDARRAY (5) |
Leia sobre a função RANDARRAY em detalhes aqui.
11. A função CONCAT
No Excel 2016 e anteriores, não é fácil concatenar mais de uma célula ou intervalo usando uma fórmula.
No excel 2019 e 365 o problema é resolvido com a função CONCAT. A função pode ter várias células, intervalos como argumentos.
A sintaxe da função CONCAT é:
| = CONCAT (texto1, [texto2], …) |
Texto 1 : O text1 pode ser qualquer texto ou intervalo que você deseja concatenar.
[texto2]: Isso é opcional. Também pode ser qualquer texto ou intervalo.
Digamos que se você deseja concatenar cada célula no intervalo A2: A11, a fórmula será
| = CONCAT (A2: A11) |
Para explorar a função CONCAT em detalhes, clique aqui.
12. A função TEXTJOIN
A função acima concatena todas as células em um intervalo, mas não concatena as células com nenhum delimitador especificado. Digamos que se você estiver preparando um arquivo para formato CSV, precisará concatenar as células com vírgulas. Nesse caso, as funções CONCATENATE e CONCAT irão falhar.
Aqui, a função TEXTJOIN funciona maravilha e concatena os textos fornecidos com o delimitador fornecido.
| = TEXTJOIN (delimitador, ignore_empty_cells, text1, [text2], …) |
Delimitador:Este é o delimitador que você deseja usar como separador entre os textos individuais. Pode ser uma vírgula (,), ponto e vírgula (;) ou qualquer coisa, mesmo nada.
Ignore_empty_cells:Esta é uma variável binária. Defina como TRUE se quiser ignorar células vazias em intervalos, caso contrário, defina como FALSE para incluir as células vazias.
Texto 1:Este é o texto que você deseja juntar. Podem ser textos individuais, células ou intervalos inteiros.
Digamos que eu queira concatenar o intervalo A2: A11 com uma vírgula, ignorando as células em branco.
| = TEXTJOIN (",", 1, A2: A11) |
Para entender esta função em detalhes, clique aqui.
Este artigo foi apenas uma introdução à nova função do Excel 365 e 2019. Expliquei essas funções em detalhes em artigos separados. Você pode clicar nos links disponíveis em cada função no artigo para entender a função completamente. Existem outras funções, como XLOOKUP, que ainda não foram lançadas.
Se você tiver alguma dúvida sobre os tópicos do Excel ou VBA, pergunte na seção de comentários abaixo. Diga-nos como podemos melhorar. Agradecemos sua sugestão e estamos ansiosos para ouvir de você.
Criar função VBA para retornar array | Para retornar um array de uma função definida pelo usuário, temos que declará-lo quando nomearmos a UDF.
Arrays no Excel Formul | Saiba o que são arrays no Excel.
Como criar uma função definida pelo usuário através do VBA. Aprenda a criar funções definidas pelo usuário no Excel
Usando uma função definida pelo usuário (UDF) de outra pasta de trabalho usando VBA no Microsoft Excel | Use a função definida pelo usuário em outra pasta de trabalho do Excel
Retornar valores de erro de funções definidas pelo usuário usando VBA no Microsoft Excel | Aprenda como você pode retornar valores de erro de uma função definida pelo usuário
Artigos populares:
Divida a planilha do Excel em vários arquivos com base na coluna usando o VBA | Este código VBA divide a planilha do Excel com base em valores exclusivos em uma coluna especificada. Baixe o arquivo de trabalho.
Desativar mensagens de aviso usando VBA no Microsoft Excel 2016 | Para desligar as mensagens de aviso que interrompem o código VBA em execução, usamos a classe Application.
Adicionar e salvar nova pasta de trabalho usando VBA no Microsoft Excel 2016 | Para adicionar e salvar pastas de trabalho usando o VBA, usamos a classe Workbooks. Workbooks.Add adiciona uma nova pasta de trabalho facilmente, no entanto …