10 funções essenciais do Microsoft Excel para análise de dados

O Microsoft Excel oferece aos usuários centenas de funções e fórmulas diferentes para diversos fins. Se você precisa analisar suas finanças pessoais ou qualquer grande conjunto de dados, são as funções que facilitam o trabalho. Além disso, economiza muito tempo e esforços. No entanto, encontrar a função certa para o seu conjunto de dados pode ser muito complicado.

Portanto, se você está lutando para encontrar a função apropriada do Excel para análise de dados, veio ao lugar certo. Aqui está uma lista de algumas funções essenciais do Microsoft Excel que você pode usar para análise de dados e aumentar sua produtividade no processo.

1. CONCATENAR

=CONCATENAR é uma das funções mais cruciais para análise de dados, pois permite combinar texto, números, datas etc. de várias células em uma. A função é particularmente útil para combinar dados de diferentes células em uma única célula. Por exemplo, é útil para criar parâmetros de rastreamento para campanhas de marketing, criar consultas de API, adicionar texto a um formato numérico e várias outras coisas.

No exemplo acima, eu queria o mês e as vendas juntos em uma única coluna. Para isso, usei a fórmula =CONCATENAR(A2, B2) na célula C2 para obter Jan$700 como resultado.

Fórmula: =CONCATENAR(células que você deseja combinar)

2. LEN

=LEN é outra função útil para análise de dados que essencialmente gera o número de caracteres em qualquer célula. A função é predominantemente utilizável durante a criação de tags de título ou descrições que possuem um limite de caracteres. Também pode ser útil quando você está tentando descobrir as diferenças entre diferentes identificadores exclusivos, que geralmente são bastante longos e não estão na ordem correta.

No exemplo acima, eu queria contar os números do número de visualizações que recebia a cada mês. Para isso, utilizei a fórmula =LEN(C2) na célula D2 para obter 5 como resultado.

Fórmula: =LEN(célula)

3. VLOOKUP

=VLOOKUP é provavelmente uma das funções mais reconhecidas por qualquer pessoa familiarizada com análise de dados. Você pode usá-lo para combinar dados de uma tabela com um valor de entrada. A função oferece dois modos de correspondência — exata e aproximada, que são controladas pelo intervalo da pesquisa. Se você definir o intervalo como FALSE, ele procurará uma correspondência exata, mas se definir como TRUE, procurará uma correspondência aproximada.

No exemplo acima, eu queria pesquisar o número de visualizações em um determinado mês. Para isso, usei a fórmula =VLOOKUP(“Jun”, A2:C13, 3) na célula G4 e obtive 74992 como resultado. Aqui, “Jun” é o valor de pesquisa, A2:C13 é a matriz da tabela na qual estou procurando “Jun” e 3 é o número da coluna na qual a fórmula encontrará as exibições correspondentes para junho.

A única desvantagem de usar esta função é que ela só funciona com dados organizados em colunas, daí o nome — pesquisa vertical. Portanto, se você tiver seus dados organizados em linhas, primeiro precisará transpor as linhas em colunas.

Fórmula: =PROCV(lookup_value, table_array, col_index_num, [range_lookup])

4. ÍNDICE/CORRESP

Assim como a função VLOOKUP, as funções INDEX e MATCH são úteis para pesquisar dados específicos com base em um valor de entrada. O INDEX e MATCH, quando usados ​​juntos, podem superar as limitações do VLOOKUP de fornecer resultados errados (se você não for cuidadoso). Portanto, quando você combina essas duas funções, elas podem identificar a referência de dados e pesquisar um valor em uma matriz de dimensão única. Isso retorna as coordenadas dos dados como um número.

No exemplo acima, eu queria pesquisar o número de visualizações em janeiro. Para isso, utilizei a fórmula =ÍNDICE (A2:C13, CORRESP(“Jan”, A2:A13,0), 3). Aqui, A2:C13 é a coluna de dados que desejo que a fórmula retorne, “Jan” é o valor que desejo corresponder, A2:A13 é a coluna na qual a fórmula encontrará “Jan” e o 0 significa que desejo a fórmula para encontrar uma correspondência exata para o valor.

Se você quiser encontrar uma correspondência aproximada, terá que substituir o 0 por 1 ou -1. Assim, 1 encontrará o maior valor menor ou igual ao valor de pesquisa e -1 encontrará o menor valor menor ou igual ao valor de pesquisa. Observe que, se você não usar 0, 1 ou -1, a fórmula usará 1, por.

Agora, se você não deseja codificar o nome do mês, pode substituí-lo pelo número do celular. Portanto, podemos substituir “Jan” na fórmula mencionada acima por F3 ou A2 para obter o mesmo resultado.

Fórmula: =ÍNDICE(coluna dos dados que você deseja retornar, MATCH (ponto de dados comum que você está tentando corresponder, coluna da outra fonte de dados que possui o ponto de dados comum, 0))

5. MINIFS/MAXIFS

=MINIFS e =MAXIFS são muito semelhantes às funções =MIN e =MAX, exceto pelo fato de permitirem que você pegue o conjunto mínimo/máximo de valores e os corresponda em critérios específicos também. Então, essencialmente, a função procura os valores mínimo/máximo e os compara com os critérios de entrada.

No exemplo acima, eu queria encontrar as pontuações mínimas com base no sexo do aluno. Para isso, usei a fórmula =MINIFS (C2:C10, B2:B10, “M”) e obtive o resultado 27. Aqui C2:C10 é a coluna na qual a fórmula vai procurar as pontuações, B2:B10 é uma coluna na qual a fórmula procurará os critérios (o gênero) e “M” é o critério.

Da mesma forma, para pontuações máximas, usei a fórmula =MAXIFS(C2:C10, B2:B10, “M”) e obtive o resultado 100.

Fórmula para MINIFS: =MINIFS(min_intervalo, critérios_intervalo1, critérios1,…)

Fórmula para MAXIFS: =MAXIFS(intervalo_max, intervalo_critérios1, critérios1,…)

6. MÉDIAS

A função =AVERAGEIFS permite encontrar uma média para um determinado conjunto de dados com base em um ou mais critérios. Ao usar esta função, você deve ter em mente que cada critério e faixa média podem ser diferentes. No entanto, na função =AVERAGEIF, tanto o intervalo de critérios quanto o intervalo de soma precisam ter o mesmo intervalo de tamanho. Observe a diferença de singular e plural entre essas funções? Bem, é aí que você precisa ter cuidado.

Neste exemplo, eu queria encontrar a pontuação média com base no sexo dos alunos. Para isso, usei a fórmula =AVERAGEIFS(C2:C10, B2:B10, “M”) e obtive 56,8 como resultado. Aqui, C2:C10 é o intervalo no qual a fórmula procurará a média, B2:B10 é o intervalo de critérios e “M” é o critério.

Fórmula: =AVERAGEIFS(intervalo_médio, intervalo_critério1, critério1,…)

7. CONTÉM

Agora, se você quiser contar o número de instâncias em que um conjunto de dados atende a critérios específicos, precisará usar a função =COUNTIFS. Essa função permite adicionar critérios ilimitados à sua consulta e, assim, torna a maneira mais fácil de encontrar a contagem com base nos critérios de entrada.

Neste exemplo, eu queria encontrar o número de alunos do sexo masculino ou feminino que obtiveram notas de aprovação (ou seja, >=40). Para isso usei a fórmula =CONT.IFS(B2:B10, “M”, C2:C10, “>=40”). Aqui, B2:B10 é o intervalo em que a fórmula procurará o primeiro critério (sexo), “M” é o primeiro critério, C2:C10 é o intervalo em que a fórmula procurará o segundo critério (marcas), e “>=40” é o segundo critério.

Fórmula: =CONT.SE(intervalo_critério1, critério1,…)

8. SOMAPRODUTO

A função =SUMPRODUCT ajuda você a multiplicar intervalos ou matrizes juntos e, em seguida, retorna a soma dos produtos. É uma função bastante versátil e pode ser usada para contar e somar arrays como COUNTIFS ou SUMIFS, mas com maior flexibilidade. Você também pode usar outras funções dentro do SUMPRODUCT para estender ainda mais sua funcionalidade.

Neste exemplo, eu queria encontrar a soma total de todos os produtos vendidos. Para isso, usei a fórmula =SOMAPRODUTO(B2:B8, C2:C8). Aqui, B2:B8 é a primeira matriz (a quantidade de produtos vendidos) e C2:C8 é a segunda matriz (o preço de cada produto). A fórmula então multiplica a quantidade de cada produto vendido com seu preço e, em seguida, soma tudo para entregar as vendas totais.

Fórmula: =SOMAPRODUTO(array1, [array2], [array3],…)

9. TRIM

A função =TRIM é particularmente útil quando você está trabalhando com um conjunto de dados que possui vários espaços ou caracteres indesejados. A função permite que você remova esses espaços ou caracteres de seus dados com facilidade, permitindo obter resultados precisos ao usar outras funções.

Neste exemplo, eu queria remover todos os espaços extras entre as palavras Mouse e pad em A7. Para isso usei a fórmula =TRIM(A7).

A fórmula simplesmente removeu os espaços extras e entregou o resultado Mouse pad com um único espaço.

Fórmula: =APARAR(texto)

10. LOCALIZAR/PESQUISAR

Arredondando as coisas estão as funções FIND/SEARCH que irão ajudá-lo a isolar um texto específico dentro de um conjunto de dados. Ambas as funções são bastante semelhantes no que fazem, exceto por uma grande diferença – a função =FIND retorna apenas correspondências que diferenciam maiúsculas de minúsculas. Enquanto isso, a função =SEARCH não tem tais limitações. Essas funções são particularmente úteis ao procurar anomalias ou identificadores exclusivos.

Neste exemplo, eu queria encontrar o número de vezes que ‘Gui’ apareceu dentro do Guiding Tech para o qual usei a fórmula =FIND(A2, B2), que deu o resultado 1. Agora, se eu quisesse encontrar o número de vezes ‘ gui’ apareceu no Guiding Tech, eu teria que usar a fórmula =SEARCH porque não diferencia maiúsculas de minúsculas.

Fórmula para encontrar: =ENCONTRAR(encontrar_texto, dentro_texto, [start_num])

Fórmula de pesquisa: =PESQUISAR(encontrar_texto, dentro_texto, [start_num])

Domine a análise de dados

Essas funções essenciais do Microsoft Excel definitivamente o ajudarão na análise de dados, mas esta lista apenas arranha a ponta do iceberg. O Excel também inclui várias outras funções avançadas para alcançar resultados específicos. Se você estiver interessado em aprender mais sobre essas funções, informe-nos na seção de comentários abaixo.

Próximo: Se você deseja usar o Excel com mais eficiência, verifique o próximo artigo para obter alguns atalhos de navegação úteis do Excel que você deve conhecer.