Téléchargez l'exemple de classeur
Ce didacticiel montrera comment trouver la plus grande valeur de cellule qui répond à des conditions spécifiques dans Excel et Google Sheets.
Max si la fonction de tableau
La fonction MAX identifie la plus grande valeur dans une série de nombres.
Nous pouvons utiliser la fonction MAX combinée à une fonction SI pour identifier la plus grande valeur qui répond à une condition spécifiée.
Il est recommandé aux utilisateurs de Google Sheets et d'Excel 2022 ou version ultérieure d'utiliser la fonction MAXIFS plus simple. Ceci est expliqué dans une section ultérieure.
Cet exemple utilisera les fonctions MAX et SI dans une formule matricielle pour identifier le plus grand Taille de la commande pour chaque Nom du magasin
| 1 | {=MAX(SI(B3:B8="A",D3:D8))} |
Dans Office 365 et les versions d'Excel après 2022, vous pouvez simplement entrer la formule ci-dessus comme vous le feriez normalement (en appuyant sur ENTRÉE).
Cependant, pour Excel 2022 et versions antérieures, vous devez entrer la formule en appuyant sur CTRL + MAJ + ENTRÉE. Après cela, vous remarquerez que des accolades de tableau apparaissent autour de la formule.
Pour montrer comment fonctionne cette formule, décomposons-la en étapes.
Voici notre formule finale (affichée sans les parenthèses de formule matricielle ajoutées automatiquement) :
| 1 | =MAX(SI(B3:B8="A",D3:D8)) |
Tout d'abord, les valeurs de plage de cellules sont ajoutées à la formule sous forme de tableaux :
| 1 | =MAX(SI({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})) |
Ensuite le Nom du magasin = La condition « A » produit un tableau de valeurs VRAI/FAUX :
| 1 | =MAX(SI({VRAI; FAUX; VRAI; FAUX; VRAI; FAUX},{500; 400; 300; 700; 600; 200})) |
Ensuite, la fonction SI change toutes les valeurs VRAI dans le Taille de la commande :
| 1 | =MAX({500; FAUX; 300; FAUX; 600; FAUX}) |
La fonction MAX identifie le plus grand nombre dans le tableau, tout en ignorant les valeurs FAUX, pour afficher le plus grand Taille de la commande pour le Nom du magasin = « A » :
| 1 | =600 |
Max If - Plusieurs critères
Nous pouvons également identifier la plus grande valeur basée sur plusieurs critères en utilisant la logique booléenne.
Cet exemple montrera le plus grand Taille de la commande pour chaque Nom du magasin, mais pour Dates de commande avant le 30/04/2021 en utilisant les fonctions MAX, IF et DATE :
| 1 | {=MAX(SI((B3:B8="A")*(C3:C8 |
Remarquez ici que nous multiplions ensemble deux ensembles de critères VRAI/FAUX :
| 1 | (B3:B8="A") * (C3:C8 |
Si les deux critères sont VRAI, la condition totale sera calculée comme VRAI, mais si un (ou plusieurs) critère est FAUX, il sera calculé comme FAUX.
En utilisant cette méthodologie, il est possible d'ajouter de nombreux critères différents à cette formule.
Max If - Plusieurs critères avec des références de cellule
Habituellement, il n'est pas recommandé de coder en dur des valeurs dans des formules. Au lieu de cela, il est plus flexible d'utiliser des cellules séparées pour définir les critères.
Pour correspondre au Nom du magasin à la valeur indiquée dans la colonne F, nous pouvons mettre à jour la formule pour qu'elle soit :
| 1 | {=MAX(SI((B3:B8=F3)*(C3:C8 |
Verrouillage des références de cellule
Pour rendre nos formules plus faciles à lire, nous avons montré les formules sans références de cellules verrouillées :
| 1 | {=MAX(SI((B3:B8=F3)*(C3:C8 |
Mais ces formules ne fonctionneront pas correctement si elles sont copiées et collées ailleurs dans votre fichier. Au lieu de cela, vous devez utiliser des références de cellules verrouillées comme ceci :
| 1 | {=MAX(SI(($B$3:$B$8=F3)*($C$3:$C$8 |
Lisez notre article sur le verrouillage des références de cellules pour en savoir plus.
Fonction MAXIFS
Les utilisateurs de Google Sheets et d'Excel 2022 ou version ultérieure peuvent utiliser la fonction MAXIFS unique pour reproduire le comportement des fonctions MAX et IF illustrées dans les exemples précédents.
Cet exemple suivant utilise les fonctions MAXIFS et DATE pour afficher le plus grand Taille de la commande pour chaque Nom du magasin pour Dates de commande avant le 30/04/2021 :
| 1 | =MAXIFS(D3:D8,B3:B8,"A",C3:C8,"<"&DATE(2021,4,30)) |
La fonction MAXIFS ne nécessite pas que l'utilisateur appuie sur CTRL + MAJ + ENTRÉE lors de la saisie de la formule.
Max If (valeur maximale avec condition) dans Google Sheets
Les exemples ci-dessus fonctionnent exactement de la même manière dans Google Sheets que dans Excel, mais comme la fonction MAXIFS est disponible, il est recommandé d'utiliser cette fonction unique au lieu de combiner les fonctions MAX et IF.
Si les exemples utilisant les fonctions MAX et IF doivent être utilisés, Google Sheets exige que vous les saisissiez sous forme de formules matricielles. Au lieu d'afficher la formule avec des accolades Excel { }, appuyer sur CTRL + MAJ + ENTRÉE ajoute automatiquement la fonction ARRAYFORMULA autour de la formule :
| 1 | =FORMULE TABLEAU(MAX(SI((B3:B8="A")*(C3:C8 |
