Excel Fórmulas

Neste artigo, veremos algumas fórmulas importantes para Análise de Dados:

  1. SUMIF
  2. SUMIFS
  3. COUNT
  4. COUNTIF
  5. COUNTIFS
  6. LOOKUP
  7. VLOOKUP
  8. HLOOKUP

1.1 SUMIF

Você usa a função SUMIF para somar os valores em um intervalo que atende aos critérios especificados. Por exemplo, suponha que em uma coluna que contém números, você deseja somar apenas os valores maiores que 5. Você pode usar a seguinte fórmula:

=SUMIF(B2:B25,">5")

Exemplo: 

1. No conjunto de dados acima, determine a receita total da região Central.

=SUMIF(B2:B44,"Central",G2:G44)

RESULTADO: 11139.07 

2. No conjunto de dados acima, determine o total de unidades vendidas pelo representante Smith.

=SUMIF(C2:C44,"Smith",E2:E44)

RESULTADO: 156

3. No conjunto de dados acima, determine a receita total a partir de 01/01/2021.

=SUMIF(A2:A44,">=01/01/2021", G2:G44)

RESULTADO: 4779.64

4. No conjunto de dados acima, determine o custo total por unidade dos pedidos que tenham preço unitário acima de 100.

=SUMIF(F2:F44,">100")

RESULTADO: 525

1.2 SUMIFS

A função SUMIFS, uma das funções matemática e trigonométrica, adiciona todos os seus argumentos que atendem a vários critérios. Por exemplo, você usaria SUMIFS para somar o número de varejistas no país que (1) residem em um único CEP e (2) cujos lucros excedem um valor específico em dólar.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Argument name

Description

Sum_range (required)

The range of cells to sum.

Criteria_range1 (required)

The range that is tested using Criteria1*. *Criteria_range1* and *Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in *Sum_range* are added.

Criteria1 (required)

The criteria that defines which cells in *Criteria_range1* will be added. For example, criteria can be entered as 32, “>32”, B4, “apples”, or “32”.

Criteria_range2, criteria2, … (optional)

Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs.

Exemplo:

1. No conjunto de dados acima, determine a receita total gerada pelo representante Gill pelas vendas de produtos que comecem com “P”.

=SUMIFS(G2:G44,D2:D44,"=P*",C2:C44,"Gill")

RESULTADO: 617.13

2. No conjunto de dados acima, determine a receita total gerada pelo representante Morgan pelas vendas de quaisquer produtos, exceto Binder.

=SUMIFS(G2:G44,D2:D44,"<>Binder",C2:C44,"Morgan")


RESULTADO: 1136.05

1.3 COUNT

A função COUNT conta o número de células que contêm números e conta os números dentro da lista de argumentos. Use a função COUNT para obter o número de entradas em um campo numérico que está em um intervalo ou matriz de números. Por exemplo, você pode inserir a seguinte fórmula para contar os números no intervalo A1:A20:

=COUNT(A1:A20)

Neste exemplo, se cinco das células no intervalo contiverem números, o resultado será 5.

Exemplo:

1. No conjunto de dados acima, determine o total de pedidos.

=COUNT(E2:E44)


RESULTADO: 43

1.4 COUNTIF

Use COUNTIF, uma das funções estatísticas, para contar o número de células que atendem a um critério; por exemplo, para contar o número de vezes que uma determinada cidade aparece em uma lista de clientes. Em sua forma mais simples, COUNTIF diz:

=COUNTIF(Where do you want to look?, What do you want to look for?)
=COUNTIF(A2:A5,"London")
=COUNTIF(A2:A5,A4)

Exemplo:

1. No conjunto de dados acima, determine quantos pedidos foram de Pencil.

=COUNTIF(D2:D44,"Pencil")

RESULTADO: 13 

2. No conjunto de dados acima, determine quantas vendas foram realizados pelo representante Jones.

=COUNTIF(C2:C44,"Jones")


RESULTADO: 8

1.5 COUNTIFS

A função COUNTIFS aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são atendidos.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Exemplo:

1. No conjunto de dados acima, determine quantas vendas o representante Howard obteve receita acima de 100.

=COUNTIFS(C2:C44,"Howard",G2:G44,">100")

RESULTADO: 1 

2. No conjunto de dados acima, determine quantas vendas foram realizadas na Região East por representantes em que o nome comece por “J” e que geraram receita abaixo de 500.

=COUNTIFS(B2:B44,"East",C2:C44,"=J*",G2:G44,"<500")


RESULTADO: 6

1.6 LOOKUP

Use LOOKUP, uma das funções de pesquisa e referência, quando precisar procurar em uma única linha ou coluna e encontrar um valor da mesma posição em uma segunda linha ou coluna.

LOOKUP(lookup_value, lookup_vector, [result_vector])

Exemplo:

Frequency 4.14

Color red

4.19

orange

5.17

yellow

5.77

green

6.39

blue

1. No conjunto de dados acima, determine a cor associada ao número 4.19.

=LOOKUP(4.19,A2:A6,B2:B6)


RESULTADO: orange

1.7 VLOOKUP

Use VLOOKUP quando precisar encontrar coisas em uma tabela ou intervalo por linha. Por exemplo, procure o preço de uma peça automotiva pelo número da peça ou encontre um nome de funcionário com base em seu ID de funcionário. Em sua forma mais simples, a função VLOOKUP diz:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).
=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP("Fontana",B2:E7,2,FALSE)
=VLOOKUP(A2,’Client Details’!A:F,3,FALSE)
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argument name

Description

lookup_value (required)

The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the *table_array* argument. For example, if *table-array* spans cells B2:D7, then your lookup_value must be in column B. *Lookup_value* can be a value or a reference to a cell.

table_array (required)

The range of cells in which the VLOOKUP will search for the *lookup_value* and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references. The first column in the cell range must contain the *lookup_value*. The cell range also needs to include the return value you want to find.

col_index_num (required)

The column number (starting with 1 for the left-most column of *table_array*) that contains the return value.

range_lookup (optional)

A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match: Approximate match – 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE). Exact match – 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP(“Smith”,A1:B100,2,FALSE).

Exemplo:

ID

Last name

First name

Title

101

Davis

Sara

Sales Rep

102

Fontana

Olivier

VP (Sales)

103

Leal

Karina

Sales Rep

1. No conjunto de dados acima, determine o nome do funcionário a partir do sobrenome Fontana.

=VLOOKUP("Fontana",B2:D4,2,FALSE)

RESULTADO: Olivier 

2. No conjunto de dados acima, determine o título do funcionário a partir do sobrenome Fontana.

=VLOOKUP("Fontana",B2:D4,3,FALSE)


RESULTADO: VP (Sales)

1.8 HLOOKUP

Pesquisa um valor na linha superior de uma tabela ou matriz de valores e, em seguida, retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use HLOOKUP quando seus valores de comparação estiverem localizados em uma linha na parte superior de uma tabela de dados e você desejar pesquisar um número especificado de linhas. Use VLOOKUP quando seus valores de comparação estiverem localizados em uma coluna à esquerda dos dados que você deseja localizar.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Exemplo:

Axles

Bearings

Bolts

4

4

9

5

7

10

6

8

11

1. No conjunto de dados acima, determine o valor da coluna Axles na 3º linha.

=HLOOKUP("Axles",A1:C4,3,TRUE)

RESULTADO: 5 

2. No conjunto de dados acima, determine o valor da coluna Bearings na 4º linha.

=HLOOKUP("Bearings",M1:O4,4,FALSE)

RESULTADO: 8 

Outras funções e fórmulas podem ser vistas por categoria ou ordem alfabética no site de suporte da Microsoft nas referências.

2. Referências

Fique à vontade para deixar nos comentários opiniões ou sugestões de melhoria.

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Dados Estruturados, Semi-Estruturados e Desestruturados

Imagem
Introdução Em Big Data, a variedade dos dados é uma importante característica. Sendo assim, fontes de dados cada vez mais complexas lidarão com diversos tipos de dados, estes que sua vez podem ser classificados em estruturados, semi-estruturados e desestruturados. Nesse contexto, é importante saber identificar a estruturação dos dados a fim de extrair o máximo de valor. Dados Estruturados O termo dados estruturados, geralmente, se refere a dados que estão em conformidade com um esquema bem definido. Exemplos de dados estruturados incluem números, datas e grupos de palavras, que normalmente são associados em um banco de dados. Se observarmos uma tabela de um banco de dados, é importante notar que mesmo sem olhar para os dados, você terá a garantia de que cada registro que aparecerá na tabela sempre estará de acordo com o esquema da mesma, sem exceções. E como cada registro em um banco de dados relacional é uma linha em uma tabela definida, tem-se uma garantia de estrutura, ainda mais se...
Leia mais

Normalização e Desnormalização de Banco de Dados

Imagem
Introdução Desnormalização e normalização são duas estratégias para o design de banco de dados, que como veremos, existem diferenças e trade-offs entre as duas abordagens. Considere os elementos básicos que você pode usar para criar tabelas: colunas, tipos de dados e outras propriedades de coluna, chaves primárias e chaves estrangeiras. Com esses elementos básicos, você tem muita flexibilidade na forma de como projeta as tabelas para seu banco de dados.  O SGBD não exige que você tenha uma chave primária em uma tabela, então esta é uma maneira possível de armazenar os dados. Com toda essa liberdade de como você configura suas tabelas, pode ser útil entender essas estratégias gerais de design de banco de dados dependendo de suas necessidades. Normalização A normalização do banco de dados é uma estratégia pela qual você projeta cada tabela para que ela obedeça a determinadas condições ou regras organizacionais. As regras são construídas para formas cada vez mais rígidas de organizaçã...
Leia mais

Por que usar R?

Imagem
A pergunta do título sugere que devemos utilizar a linguagem R, mas por quê? Ao longo deste post irei dando algumas definições da linguagem R para que gradualmente entendermos de que esta é uma ferramenta proeminente, principalmente, em momentos de fazer ciência com dados. O que é a linguagem R? Nenhuma definição, geralmente, é melhor do que a feita pela própria ferramenta. Abaixo, encontra-se uma breve descrição feita pelo site do projeto R : "R é um ambiente de software livre para computação estatística e gráficos. Ele compila e roda em uma ampla variedade de plataformas UNIX, Windows e MacOS." A linguagem R é um projeto open-source, o que significa que não há custo de licença e seu desenvolvimento é feito graças a contribuidores bastante ativos de todo o mundo. Inicialmente, foi desenvolvida por estatísticos do departamento de Estatística da Universidade de Auckland, Nova Zelândia. Uma de suas principais características é fornecer uma ampla variedade de técnicas estatístic...
Leia mais

Tipos de Análise de Dados

Imagem
Neste artigo, veremos os 4 principais tipos de análises. 1. Descritiva “A análise descritiva explica o que aconteceu.” A análise descritiva é a espinha dorsal dos relatórios – é impossível ter ferramentas e painéis de Business Intelligence (BI) sem ela. Ele aborda questões básicas de “quantos, quando, onde e o quê”. Esse tipo de análise permite que você divida um grande pedaço de dados em pedaços menores, extraindo informações relevantes dos dados ou uma breve sinopse do que aconteceu. Exemplo: Vamos dar um exemplo de uso de análise descritiva para os dados do cliente. Isso inclui encontrar respostas para as seguintes perguntas. Com quantos segmentos diferentes de compradores estamos lidando? Onde esses compradores estão localizados? Como os clientes de alto valor diferem? No que eles estão interessados? Qual é a renda, idade, número de filhos, ocupação e distribuição regional desses compradores? 2. Diagnóstica “A análise diagnóstica indica por que isso aconteceu.” A análise diagnóstic...
Leia mais

Dashboard de Vendas com Power BI - RoupasParaTodos

Imagem
Descrição Você é Analista de Dados na empresa RoupasParaTodos, que comercializa roupas e outros acessórios em seu site online com distribuidoras espalhadas por diversos países. Problema A empresa configurou o Google Analytics para extrair dados sobre o perfil dos visitantes do portal de e-commerce e agora o Gestor da área de Marketing gostaria de ter as respostas às perguntas abaixo: Como os clientes mais acessam nosso portal, por busca orgânica ou paga? Quanto tempo em média um visitante permanece em nosso portal por dia do mês? Qual a principal fonte de acesso ao nosso portal? Qual o sistema operacional mais usado para acessar nosso portal? Qual o dispositivo mais usado para acesso ao nosso portal? Qual o total de faturamento por dia? Proposta de Solução Indagações iniciais Qual problema de negócio eu preciso atender? Ajudar a responder às perguntas do gestor da área de Marketing sobre o perfil dos visitantes do portal de e-commerce. Quais são os requisitos do problema? Avaliar como ...
Leia mais