Cliquez sur " M. Han parle bureau " ci-dessus pour suivre rapidement
Veuillez lire et partager, afin que davantage d'apprenants puissent progresser ensemble !


origine du problème

Dans le traitement de données EXCEL, les statistiques sur les données multi-conditions sont souvent utilisées, telles que : comptage multi-conditions, somme multi-conditions, moyenne multi-conditions, maximum multi-conditions, minimum multi-conditions, etc.

Aujourd'hui, M. Han listera ces fonctions multi-conditionnelles et illustrera leur utilisation une par une.

Exemple de données :

image

Résultat des statistiques demandées :

image


Explication de la fonction


Le nombre de conditions multiples, la somme de conditions multiples, la moyenne de conditions multiples, le maximum de conditions multiples, le minimum de conditions multiples, les cinq résultats et les formules correspondantes sont présentés comme suit, puis expliqués un par un. Comme indiqué ci-dessous:

image

Rappel : Après ces cinq fonctions, il existe une autre fonction IFS dans cet article, n'oubliez pas de la lire !

COUNTIFS : nombre de conditions multiples

grammaire

NB.SI(critères_plage1, critères1, [critères_plage2, critères2],…)

Grammaire chinoise

COUNTIFS(CriteriaArea1, Criteria1, [ CriteriaArea2Criteria2 ],…)

La syntaxe de la fonction NB.SI.SI a les paramètres suivants :

  • La zone de condition 1 est     requise. La première zone dans laquelle la condition d'association est calculée.

  • La condition 1 est     requise. Une condition se présente sous la forme d'un nombre, d'une expression, d'une référence de cellule ou de texte et définit la plage de cellules à compter. Par exemple, une condition peut être exprimée sous la forme 32, ">32", B4, "pommes" ou "32".

  • ConditionArea2, Condition2 , ...     facultatif. Champs supplémentaires et leurs conditions associées. Un maximum de 127 paires zone/condition sont autorisées.

Dans cet exemple :

Exiger:

Nombre de femmes ingénieures seniors avec un score de performance supérieur à 10 sur le marché 1

Il existe quatre paires conditionnelles :

  • Zone de condition 1 : Service marketing, Condition 1 : Service marketing 1 ;

  • Domaine de condition 2 : score de performance, condition 2 : supérieur à 10 ;

  • Zone de condition 3 : sexe, condition 3 : femme ;

  • Condition zone 4 : titre professionnel, condition 4 : ingénieur supérieur.

Donc la formule est :

=COUNTIFS(A2:A21,"Marketing 1",E2:E21,">=10",C2:C21,"Femme",D2:D21,"Ingénieur senior")


AVERAGEIFS : moyenne multi-conditions

grammaire

AVERAGEIFS(plage_moyenne, plage_critères1, critère1, [plage_critères2, critères2], ...)

Grammaire chinoise

AVERAGEIFS(Surface moyenne zone de condition 1, condition 1, [zone de condition 2, condition 2], …)

La syntaxe de la fonction AVERAGEIFS  a les paramètres suivants :

  •    Nécessaire pour la zone de moyenne . Une ou plusieurs cellules contenant des nombres ou des noms, des tableaux ou des références contenant des nombres dans lesquels calculer la moyenne.

  • Zone de condition 1, zone de condition 2, etc.    La zone de condition 1 est obligatoire et les zones de condition suivantes  sont facultatives. 1 à 127 zones dans lesquelles les conditions associées sont calculées.

  • Condition 1, Condition 2, etc.    La condition 1 est obligatoire et les critères suivants sont facultatifs. 1 à 127 critères sous forme de nombres, d'expressions, de références de cellule ou de texte qui définissent les cellules pour lesquelles la moyenne sera calculée. Par exemple, une condition peut être exprimée sous la forme 32, "32", ">32", "pomme" ou B4.

Dans cet exemple :

Exiger:

Note de performance moyenne des femmes ingénieures seniors dans le département Marketing 1

Il existe trois paires conditionnelles :

  • Superficie moyenne : score de performance ;

  • Zone de condition 1 : Service marketing, Condition 1 : Service marketing 1 ;

  • Zone de condition 2 : sexe, condition 2 : femme ;

  • Condition zone 3 : titre professionnel, condition 3 : ingénieur supérieur.

Donc la formule est :

=AVERAGEIFS(E2:E21,A2:A21,"Marketing 1",C2:C21,"Femme",D2:D21,"Ingénieur senior")


SUMIFS : somme de plusieurs conditions

grammaire

SUMIFS(plage_somme, plage_critères1, critères1, [plage_critères2, critères2], ...)

Grammaire chinoise

SUMIFS (aire numérique à sommer, aire conditionnelle1, condition1, [aire conditionnelle2, condition2],…)

La syntaxe de la fonction SUMIFS  a les paramètres suivants :

  •    Obligatoire pour la zone numérique à additionner . Une ou plusieurs cellules contenant des nombres ou des noms, des tableaux ou des références contenant des nombres à additionner.

  • Zone de condition 1, zone de condition 2, etc.    La zone de condition 1 est obligatoire et les  zones de condition suivantes  sont facultatives. 1 à 127 zones dans lesquelles les conditions associées sont calculées.

  • Condition 1, Condition 2, etc.    La condition 1 est obligatoire et les conditions suivantes sont facultatives. 1 à 127 critères sous forme de nombres, d'expressions, de références de cellule ou de texte qui définissent les cellules qui seront additionnées. Par exemple, une condition peut être exprimée sous la forme 32, "32", ">32", "pomme" ou B4.

Dans cet exemple :

Exiger:

Note de performance totale des femmes ingénieures seniors dans le département marketing 1

Il existe trois paires conditionnelles :

  • Zone de sommation : score de performance ;

  • Zone de condition 1 : Service marketing, Condition 1 : Service marketing 1 ;

  • Zone de condition 2 : sexe, condition 2 : femme ;

  • Condition zone 3 : titre professionnel, condition 3 : ingénieur supérieur.

Donc la formule est :

=SUMIFS(E2:E21,A2:A21,"Marketing 1",C2:C21,"Femme",D2:D21,"Ingénieur senior")


MAXIFS : trouver la valeur maximale avec plusieurs conditions

grammaire

MAXIFS (plage_max, plage_critères1, critère1, [plage_critères2, critères2], ...)

Grammaire chinoise

MAXIFS  (Plage de cellules pour prendre la valeur maximale, CriteriaRange1, Criteria1, [CriteriaRange2, Criteria2],…)

La syntaxe de la fonction MAXIFS  a les paramètres suivants :

  • La plage de cellules qui prend la valeur maximale est   requise. Une ou plusieurs cellules contenant des nombres ou des noms, des tableaux ou des références contenant des nombres pour prendre la valeur maximale .

  • Zone de condition 1, zone de condition 2, etc.    La zone de condition 1 est obligatoire et les  zones de condition suivantes  sont facultatives. 1 à 126 zones dans lesquelles les conditions associées sont calculées.

  • Condition 1, Condition 2, etc.    La condition 1 est obligatoire et les conditions suivantes sont facultatives. 1 à 126 critères sous forme de nombres, d'expressions, de références de cellule ou de texte pour définir la cellule qui prend la valeur maximale. Par exemple, une condition peut être exprimée sous la forme 32, "32", ">32", "pomme" ou B4.

Dans cet exemple :

Exiger:

Le score de performance le plus élevé des femmes ingénieures seniors sur le marché 1

Il existe trois paires conditionnelles :

  • Prenez la zone de valeur maximale : score de performance ;

  • Zone de condition 1 : Service marketing, Condition 1 : Service marketing 1 ;

  • Zone de condition 2 : sexe, condition 2 : femme ;

  • Condition zone 3 : titre professionnel, condition 3 : ingénieur supérieur.

Donc la formule est :

=MAXIFS(E2:E21,A2:A21,"Marketing 1",C2:C21,"Femme",D2:D21,"Ingénieur senior")


MINIFS : multi-conditions pour trouver la valeur minimale

grammaire

MINIFS(plage_min, plage_critères1, critères1, [plage_critères2, critères2], ...)

Grammaire chinoise

MINIFS  (plage de cellules pour prendre la valeur minimale, conditional area1, condition1, [conditional area2, condition2],…)

La syntaxe de la fonction MINIFS  a les paramètres suivants :

  • La plage de cellules qui prend la valeur minimale est   obligatoire. Une ou plusieurs cellules contenant des nombres ou des noms, des tableaux ou des références contenant des nombres pour prendre la valeur minimale .

  • Zone de condition 1, zone de condition 2, etc.    La zone de condition 1 est obligatoire et les  zones de condition suivantes  sont facultatives. 1 à 126 zones dans lesquelles les conditions associées sont calculées.

  • Condition 1, Condition 2, etc.    La condition 1 est obligatoire et les conditions suivantes sont facultatives. 1 à 126 critères sous la forme d'un nombre, d'une expression, d'une référence de cellule ou d'un texte qui définissent la cellule qui prend la valeur minimale. Par exemple, une condition peut être exprimée sous la forme 32, "32", ">32", "pomme" ou B4.

Dans cet exemple :

Exiger:

Note de performance minimale pour une femme ingénieur senior en marketing 1

Il existe trois paires conditionnelles :

  • Prenez la zone de valeur minimale : score de performance ;

  • Zone de condition 1 : Service marketing, Condition 1 : Service marketing 1 ;

  • Zone de condition 2 : sexe, condition 2 : femme ;

  • Condition zone 3 : titre professionnel, condition 3 : ingénieur supérieur.

Donc la formule est :

=MINIFS(E2:E21,A2:A21,"Marketing 1",C2:C21,"Femme",D2:D21,"Ingénieur principal")


IFS : si une ou plusieurs conditions sont remplies

Fonction:

La fonction IFS vérifie si une ou plusieurs conditions sont remplies et renvoie la valeur correspondant à la première condition TRUE. IFS peut remplacer plusieurs instructions IF imbriquées et est plus facile à lire avec plusieurs conditions.

grammaire

IFS([Quelque chose est Vrai1, Valeur si Vrai1, [Quelque chose est Vrai2, Valeur si Vrai2],…[Quelque chose est Vrai127, Valeur si Vrai127])

Grammaire chinoise populaire :

si (A1 vaut 1, affichage 1, si A1 vaut 2, affichage 2, ou si A1 vaut 3, affichage 3)

Remarques: 

  • La fonction IFS permet de tester jusqu'à 127 conditions différentes.

  • =IFS(A1=1,1,A1=2,2,A1=3,3)

Cet exemple :

Remplissez le niveau de performance de chaque employé, comme indiqué ci-dessous :

image

Critères de notation des performances :

image

Saisissez la formule en F2 : "=IFS( E2>=12,"Excellent" , E2>=8,"Good" , E2>=5,"Qualified" , E2<=5,"Unqualified" )", la formule va à Remplissez le bas pour obtenir le niveau de performance de tous les employés, comme indiqué ci-dessous :

image

La formule imbriquée IF traditionnelle est : "=IF(E2>=12,"Excellent",IF(E2>=8,"Good",IF(E2>=5,"Qualified","Unqualified"))) ”, IFS est-il beaucoup plus facile à utiliser que cette formule ?


Remarque

Les trois fonctions MAXIFS, MINIFS et IFS sont uniques à EXCEL2016, et toutes les versions 2016 ne les ont pas.

En arrière-plan de "M. Han parle de bureau", répondez à office2016, et il y aura un code d'extraction de lien de téléchargement OFFICE2016 mis à jour.


Téléchargement de matériel

Cet exemple d'exercice :

Lien : http://pan.baidu.com/s/1pKF8t8B

Mot de passe : bef9