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

1. CONCATENAR

= CONCATENAR é uma das funções mais importantes para a 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 os parâmetros de rastreamento para campanhas de marketing, construir consultas de API, adicionar texto a um formato de número 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 = CONCATENATE (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 produz o número de caracteres em qualquer célula. A função é predominantemente utilizável ao criar tags de título ou descrições que têm um limite de caracteres. Também pode ser útil quando você está tentando descobrir as diferenças entre os diferentes identificadores exclusivos que geralmente são muito longos e não estão na ordem correta.

No exemplo acima, eu queria contar os números para o número de visualizações que estava recebendo 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. PROCV

= VLOOKUP é provavelmente uma das funções mais reconhecíveis para qualquer pessoa familiarizada com análise de dados. Você pode usá-lo para combinar os dados de uma tabela com um valor de entrada. A função oferece dois modos de correspondência - exata e aproximada, que é controlada pelo alcance da pesquisa. Se você definir o intervalo como FALSO, ele procurará uma correspondência exata, mas se você definir como VERDADEIRO, ele 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 procuro "Jun" e 3 é o número da coluna na qual a fórmula encontrará as visualizações correspondentes para junho.

A única desvantagem de usar essa função é que ela só funciona com dados que foram organizados em colunas, daí o nome - pesquisa vertical. Então, se você tem seus dados organizados em linhas, primeiro você precisa transpor as linhas em colunas.

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

4. ÍNDICE / CORRESPONDÊNCIA

Muito parecido com 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 o MATCH, quando usados ​​juntos, podem superar as limitações do VLOOKUP de entregar os resultados errados (se você não for cuidadoso). Portanto, quando você combina essas duas funções, elas podem localizar 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, usei a fórmula = INDEX (A2: C13, MATCH ("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 é o coluna em que a fórmula encontrará "Jan" e o 0 significa que desejo que a fórmula encontre uma correspondência exata para o valor.

Se você quiser encontrar uma correspondência aproximada, terá que substituir o 0 por 1 ou -1. Para que 1 encontre o maior valor menor ou igual ao valor de pesquisa e -1 encontre 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 quiser 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: = INDEX (coluna dos dados que você deseja retornar, MATCH (ponto de dados comum que você está tentando corresponder, coluna da outra fonte de dados que tem 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 que elas permitem 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 combina 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 procurará as pontuações, B2: B10 é uma coluna na qual a fórmula procurará os critérios (o gênero) e "M" são os critérios.

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 (intervalo_min, intervalo_critérios1, critérios1, ...)

Fórmula para MAXIFS: = MAXIFS (intervalo_máx, intervalo_critérios1, critérios1, ...)

6. MÉDIOS

A função = AVERAGEIFS permite que você encontre 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 intervalo médio podem ser diferentes. No entanto, na função = AVERAGEIF, o intervalo de critérios e 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édia, intervalo_critérios1, critérios1, ...)

7. COUNTIFS

Agora, se você quiser contar o número de instâncias que um conjunto de dados atende a critérios específicos, você precisará usar a função = COUNTIFS. Esta função permite adicionar critérios ilimitados à sua consulta e, portanto, 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 = COUNTIFS (B2: B10, "M", C2: C10, "> = 40"). Aqui, B2: B10 é o intervalo no qual a fórmula procurará os primeiros critérios (gênero), "M" é o primeiro critério, C2: C10 é o intervalo no qual a fórmula procurará o segundo critério (marcas) e "> = 40" é o segundo critério.

Fórmula: = COUNTIFS (intervalo_de_critérios1, critérios1, ...)

8. SUMPRODUTO

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

Neste exemplo, eu queria encontrar a soma total de todos os produtos vendidos. Para isso, usei a fórmula = SUMPRODUCT (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 o total de vendas.

Fórmula: = SUMPRODUCT (matriz1, [matriz2], [matriz3], ...)

9. APARAR

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 remover esses espaços ou caracteres de seus dados com facilidade, permitindo que você obtenha resultados precisos enquanto usa 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: = TRIM (texto)

10. ENCONTRAR / PESQUISAR

Para completar, estão as funções FIND / SEARCH que o ajudarão 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 diferença principal - a função = FIND retorna apenas correspondências com distinção entre maiúsculas e 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 no Guiding Tech para o qual usei a fórmula = FIND (A2, B2), que gerou o resultado 1. Agora, se eu quisesse encontrar o número de vezes que 'gui' apareceu na Guiding Tech, eu teria que usar a fórmula = SEARCH porque ela não diferencia maiúsculas de minúsculas.

Fórmula para Encontrar: = ENCONTRAR (find_text, within_text, [núm_início])

Fórmula para pesquisa: = PESQUISA (find_text, within_text, [núm_início])