Excel - AVERAGEIFS and IFERROR

Tutorial

Excel

Advanced

Produce

Intermediate

00:00:06

where the sales person has 50 or more orders.

00:00:10

We can do this with the AVERAGEIFS function.

00:00:16

First we select the range of cells we want to average.

00:00:19

In this case cells D2 through D7 in the sales column.

00:00:25

I'm pressing F4 to make this an absolute cell reference.

00:00:30

Next we'll select the first range of cells we want to evaluate;

00:00:33

the range of cells in the Region column.

00:00:38

Then we select the criteria by which the range is evaluated,

00:00:42

in this case, East, cell F2;

00:00:47

then we select the range of cells for the number of orders,

00:00:52

and the criteria it needs to meet.

00:00:59

In the North region, for sales people having 50 or more orders,

00:01:04

their average sales are $63,370.

00:01:11

Let's walk through this; it's similar to the SUMIFS function:

00:01:17

first the function evaluates how many cells in the region column

00:01:21

contain a specific region.

00:01:23

In this example: North.

00:01:28

There are two: Mo and Dave are the salespeople for the North region.

00:01:34

Of these two, SUMIFS then evaluates how many have orders

00:01:39

greater than or equal to 50.

00:01:43

For North, this is both Mo and Dave.

00:01:49

Lastly, the function averages the cells from the range of

00:01:52

cells in the sales column, where all the corresponding criteria are met.

00:01:59

The average of Mo and Dave's sales is $63,370.

00:02:07

You'll notice that the average for the South region is a divide by zero error.

00:02:12

I'll show you why, and how to handle this in a minute.

00:02:17

AVERAGEIFS returns the average of cells that meet multiple criteria.

00:02:23

This is the syntax of the AVERAGEIFS function:

00:02:27

average range is required, it's one or more cells to average;

00:02:31

blank and text values are ignored.

00:02:36

Criteria range 1 is required, it's the first range that's evaluated.

00:02:42

Criteria 1 is required; it's the criteria by which

00:02:46

Criteria range 1 is evaluated.

00:02:50

Criteria range 2; criteria 2, and so on, are optional.

00:02:57

Each of the cells in the average range is used in

00:03:00

the average calculation only if all the corresponding

00:03:03

criteria specified are true for that cell.

00:03:08

All criteria range must be the same size and shape as average range.

00:03:15

You can use the question mark, an asterisk wildcard characters in criteria.

00:03:21

As we saw before, the average for the South region

00:03:24

returns a divide by zero error.

00:03:28

Wei is the only salesperson in the south region, and she has fewer than 50 orders.

00:03:35

If there are no cells that meet all the criteria,

00:03:38

AVERAGEIFS returns the divide by zero error.

00:03:42

We can enhance the formula with the IFERROR function to

00:03:45

handle this and other error conditions.

00:03:49

I'm putting the AVERAGEIFS function inside an IFERROR function.

00:03:56

IFERROR returns the values specified, in this case NA,

00:04:01

if AVERAGEIFS evaluates to an error.

00:04:04

Otherwise it returns the result of the formula.

00:04:12

And we see that the South region no longer returns an error.

00:04:18

This is the syntax of the IFERROR function.

00:04:21

Value is required.

00:04:24

It's the argument that is checked for an error.

00:04:27

Value if error is also required.

00:04:30

It's what IFERROR returns if the value argument returns an error.

00:04:37

Now you've got a pretty good idea about how to use IF functions in Excel.

00:04:41

Of course there's always more to learn.

00:00:03

Agora vamos determinar as vendas médias por região,

00:00:06

onde o vendedor tem 50 ou mais pedidos.

00:00:10

Podemos fazer isso com a função MÉDIASE.

00:00:16

Primeiro selecionamos a faixa de células cuja média queremos.

00:00:19

Neste caso, células D2 a D7, na coluna vendas.

00:00:25

Estou pressionando a tecla F4 para torná-la uma célula de referência absoluta.

00:00:30

A seguir, selecionamos a primeira faixa de células que queremos avaliar;

00:00:33

a faixa de células na coluna Região.

00:00:38

Então selecionamos o critério pelo qual a faixa é avaliada,

00:00:42

neste caso, Leste, célula F2;

00:00:47

então selecionamos a faixa de células para o número de pedidos,

00:00:52

e o critério que precisamos encontrar.

00:00:59

Na região Norte, para vendedores com 50 ou mais pedidos,

00:01:04

a média de vendas é $63.370.

00:01:11

Vamos esmiuçar isso; é similar à função SOMASES:

00:01:17

primeiro a função avalia quantas células na coluna região

00:01:21

contém uma região específica.

00:01:23

Neste exemplo: Norte.

00:01:28

São dois: Mo e Dave são vendedores na região Norte.

00:01:34

Destes dois, a função SOMASES avalia quantos têm pedidos

00:01:39

maiores ou iguais a 50.

00:01:43

Para a região Norte, ambos tiveram.

00:01:49

Por fim, a função calcula a média das células das faixa de

00:01:52

células nas colunas de vendas onde todos os critérios de correspondência são atendidos.

00:01:59

A média das vendas de Mo e Dave é $63.370.

00:02:07

Você vai notar que a média da região Sul é um erro de divisão por zero.

00:02:12

Vou mostrar porque e como lidar com isso em um minuto.

00:02:17

MÉDIASE retorna a média das células que atendem múltiplos critérios.

00:02:23

Esta é a sintaxe da função MÉDIASE:

00:02:27

a média é obrigatória, uma ou mais células para calcular a média,

00:02:31

valores vazios e de texto são ignorados.

00:02:36

A faixa de critério 1 é obrigatória. É a primeira faixa avaliada.

00:02:42

Critério 1 é obrigatório. É o critério pelo qual

00:02:46

a faixa de critério 1 é avaliada.

00:02:50

A faixa de critério 2, critério 2 e daí por diante são opcionais.

00:02:57

Cada uma das células na média é usada no

00:03:00

cálculo da média apenas de todos os critérios

00:03:03

correspondentes especificados forem verdadeiros para esta célula.

00:03:08

Toda a faixa de critério deve ter o mesmo tamanho e formato da média.

00:03:15

Você pode usar o ponto de interrogação e um caráter de curinga do asterisco no critério.

00:03:21

Como visto antes, a média para a região Sul

00:03:24

retorna um erro de divisão por zero.

00:03:28

Wei é a única vendedora na região Sul e ela teve menos de 50 pedidos.

00:03:35

Se não houver células que atendam todos os critérios,

00:03:38

MÉDIASE retorna o erro de divisão por zero.

00:03:42

Podemos melhorar a fórmula com a função SEERRO para

00:03:45

lidar com esta e outras condições de erro.

00:03:49

Estou colocando a função MÉDIASE dentro da função SEERRO.

00:03:56

SEERRO retorna o valor especificado, neste caso NA,

00:04:01

se MÉDIASE avaliar para um erro.

00:04:04

Caso contrário, ele retorna o resultado da fórmula.

00:04:12

E vemos que a região Sul não mais retorna um erro.

00:04:18

Esta é a sintaxe da função SEERRO.

00:04:21

Valor é obrigatório.

00:04:24

Este é o argumento verificado para um erro.

00:04:27

Valor_se_falso também é obrigatório.

00:04:30

É o que SEERRO retorna se o argumento valor retornar um erro.

00:04:37

Agora você tem uma boa noção de como usar as funções SE no Excel.

00:04:41

Obviamente, há sempre mais para se aprender.

00:00:03

Nun werde ich die durchschnittlichen Umsätze

00:00:06

in den Regionen ermitteln, in denen ein Vertriebsmitarbeiter 50 oder mehr Aufträge abgewickelt hat.

00:00:10

Dafür kann ich die Funktion MITTELWERTWENNS verwenden.

00:00:16

Als Erstes markiere ich den Zellbereich, dessen Mittelwert ich ermitteln möchte,

00:00:19

in diesem Fall die Zellen D2 bis D7 in der Spalte „Umsätze”.

00:00:25

Ich drücke F4, um den Zellbezug zu einem absoluten Zellbezug zu machen.

00:00:30

Als Nächstes wähle ich den ersten Zellbereich aus, der ausgewertet werden soll:

00:00:33

den Zellbereich in der Spalte „Region”.

00:00:38

Dann wähle ich die Kriterien aus, nach denen der Bereich

00:00:42

ausgewertet werden soll, in diesem Fall „Osten”, Zelle F2.

00:00:47

Nun wähle ich den Zellbereich mit der Auftragsanzahl und dem

00:00:52

Kriterium aus, das zutreffen muss.

00:00:59

In der Region „Norden” belaufen sich die durchschnittlichen Umsätze von

00:01:04

Vertriebsmitarbeitern mit 50 oder mehr Aufträgen auf $ 63.370.

00:01:11

Wir wollen uns auch diese Funktion genauer anschauen. Sie ist mit der Funktion SUMMEWENNS vergleichbar.

00:01:17

Die Funktion prüft im ersten Schritt, wie viele Zellen in der Spalte „Region”

00:01:21

eine bestimmte Region, in

00:01:23

diesem Fall „Norden”, enthalten.

00:01:28

Es gibt zwei: Mo und Dave arbeiten als Vertriebsmitarbeiter in der Region „Norden”.

00:01:34

Anhand dieser beiden Zellen wird mit SUMMEWENNS nun ausgewertet, wer

00:01:39

50 oder mehr Aufträge abgewickelt hat.

00:01:43

Für die Region „Norden” gilt dies sowohl für Mo als auch für Dave.

00:01:49

Als Letztes ermittelt die Funktion den Mittelwert der Zellen aus dem

00:01:52

Zellbereich in der Spalte „Umsätze”, für die alle entsprechenden Kriterien zutreffen.

00:01:59

Der Mittelwert der Umsätze von Mo und Dave beträgt $ 63.370.

00:02:07

Wie Sie sehen, wird für den Mittelwert der Region „Süden” der Fehler „Division durch Null” angezeigt.

00:02:12

Den Grund hierfür und die Abhilfemaßnahme zeige ich Ihnen gleich.

00:02:17

MITTELWERTWENNS gibt den Mittelwert aller Zellen zurück, die mehreren Kriterien entsprechen.

00:02:23

Hier die Syntax der Funktion MITTELWERTWENNS:

00:02:27

„Mittelwert_Bereich” ist erforderlich. Hierbei handelt es sich um eine oder mehrere Zellen, deren Mittelwert ermittelt werden sollen.

00:02:31

Leere Zellen und Textwerte werden ignoriert.

00:02:36

„Kriterien_Bereich1” ist erforderlich. Dies ist der erste Bereich, der ausgewertet wird.

00:02:42

„Kriterien1” ist erforderlich. Dies ist das Kriterium, anhand dessen

00:02:46

der Kriterienbereich 1 ausgewertet wird.

00:02:50

„Kriterien_Bereich2”, „Kriterien2” und so weiter sind optional.

00:02:57

Jede der Zellen in „Mittelwert_Bereich” wird nur dann bei der Ermittlung

00:03:00

des Mittelwerts verwendet, wenn alle entsprechenden, für diese Zelle angegebenen

00:03:03

Kritierien als WAHR ausgewertet werden.

00:03:08

Alle Bereiche in „Kriterien_Bereich” müssen die gleiche Größe und Form wie die Bereiche in „Mittelwert_Bereich” aufweisen.

00:03:15

Sie können Fragezeichen und Stern als Platzhalterzeichen in Kriterien verwenden.

00:03:21

Wie Sie bereits gesehen haben, wird als Mittelwert der Region „Süden”

00:03:24

der Fehler „Division durch Null” angezeigt.

00:03:28

Wei ist die einzige Vertriebsmitarbeiterin in der Region „Süden” und hat weniger als 50 Aufträge.

00:03:35

Wenn es keine Zellen gibt, die allen Kriterien entsprechen, gibt

00:03:38

MITTELWERTWENNS den Fehler „Division durch Null” zurück.

00:03:42

Ich kann die Formel mit der Funktion WENNFEHLER erweitern, um

00:03:45

diese und andere Fehlerbedingungen abzufangen.

00:03:49

Hier setze ich die Funktion MITTELWERTWENNS in die Funktion WENNFEHLER.

00:03:56

WENNFEHLER gibt den angegebenen Wert, in diesem Fall „N/V” zurück, wenn

00:04:01

MITTELWERTWENNS einen Fehler zurückgibt.

00:04:04

Andernfalls wird das Ergebnis der Formel zurückgegeben.

00:04:12

Wie Sie hier sehen, wird für die Region „Süden” kein Fehler mehr angezeigt.

00:04:18

Hier die Syntax der Funktion WENNFEHLER:

00:04:21

„Wert” ist erforderlich.

00:04:24

Dies ist das Argument, das auf einen Fehler überprüft werden soll.

00:04:27

„Wert_falls_Fehler” ist ebenfalls erforderlich.

00:04:30

Dies ist der Wert, der von WENNFEHLER zurückgegeben wird, wenn das Argument „Wert” einen Fehler zurückgibt.

00:04:37

Hiermit konnten Sie sich schon einen guten Überblick über die Verwendung von WENN-Funktionen in Excel

00:04:41

verschaffen. Aber es gibt natürlich auch noch mehr zu erfahren.