Parfois, plutôt que de simplement trouver le nombre le plus grand ou le maximum pour toutes vos données; vous devez rechercher le plus grand nombre dans un sous-ensemble, tel que le plus grand nombre positif ou négatif.
Si la quantité de données est petite, la tâche peut être facile à effectuer en sélectionnant manuellement la plage correcte pour la fonction MAX.
Dans d'autres circonstances, telles qu'un échantillon de données non trié volumineux, la sélection correcte de la plage peut s'avérer difficile, voire impossible.
En combinant la fonction IF avec le paramètre MAX dans une formule matricielle, il est facile de définir des conditions, telles que des nombres positifs ou négatifs, afin que seules les données correspondant à ces paramètres soient testées par la formule.
Répartition par formule de matrice MAX IF
La formule utilisée dans ce tutoriel pour trouver le plus grand nombre positif est la suivante:
= MAX (SI (A1: B5> 0, A1: B5))
RemarqueRemarque: L'argument value_if_false de la fonction IF, qui est facultatif, est omis afin de raccourcir la formule. Si les données de la plage sélectionnée ne répondent pas au critère défini (nombres supérieurs à zéro), la formule renverra un zéro. ( 0 )
Le travail de chaque partie de la formule est:
- La fonction IF filtre les données de sorte que seuls les nombres correspondant au critère choisi soient transmis à la fonction MAX.
- la fonction MAX trouve la valeur la plus élevée pour les données filtrées
- La formule de tableau - indiquée par les accolades { } entourant la formule - permet à l'argument de test logique de la fonction IF de rechercher une correspondance dans l'ensemble de la plage de données, telle que des nombres supérieurs à zéro, plutôt que dans une seule cellule de données
Formules CSE
Les formules de tableau sont créées en appuyant sur la touche Ctrl, Décalage, et Entrer touches du clavier en même temps, une fois la formule saisie.
Le résultat est que la formule entière - y compris le signe égal - est entourée d'accolades. Un exemple serait:
{= MAX (SI (A1: B5> 0, A1: B5))}
En raison des touches utilisées pour créer la formule matricielle, elles sont parfois appelées CSE formules.
Exemple de formule de matrice MAX IF d'Excel
Comme le montre l'image ci-dessus, cet exemple de didacticiel utilise la formule de matrice MAX IF pour rechercher les valeurs positives et négatives les plus grandes dans une plage de nombres.
Les étapes ci-dessous créent d’abord la formule pour trouver le plus grand nombre positif, puis les étapes nécessaires pour trouver le plus grand nombre négatif.
Saisie des données du didacticiel
- Entrez les nombres visibles dans l'image ci-dessus dans les cellules A1 à B5 d'une feuille de calcul
- Dans les cellules A6 et A7, tapez les étiquettes Max positif et Max Négatif
Saisie de la formule imbriquée MAX IF
Puisque nous créons à la fois une formule imbriquée et une formule matricielle, nous devrons taper la formule entière dans une seule cellule de la feuille de calcul.
Une fois que vous avez entré la formule NE PAS appuyez sur la Entrer touche du clavier ou cliquez sur une cellule différente avec la souris pour transformer la formule en une formule matricielle.
- Cliquez sur la cellule B6 - l'emplacement où seront affichés les premiers résultats de la formule.
- Tapez ce qui suit:
= MAX (SI (A1: B5> 0, A1: B5))
Création de la formule de tableau
- Appuyez et maintenez enfoncé le Ctrl et Décalage touches du clavier
- appuyez sur la Entrer touche du clavier pour créer la formule de tableau
- La réponse 45 devrait apparaître dans la cellule B6 puisqu'il s'agit du nombre positif le plus grand dans la liste
- Si vous cliquez sur la cellule B6, la formule complète de la matrice
{= MAX (SI (A1: B5> 0, A1: B5))}
peut être vu dans la barre de formule au-dessus de la feuille de calcul
Trouver le plus grand nombre négatif
La formule permettant de trouver le plus grand nombre négatif ne diffère de la première formule que par l'opérateur de comparaison utilisé dans l'argument de test logique de la fonction IF.
Puisque l'objectif est maintenant de trouver le plus grand nombre négatif, la seconde formule utilise l'opérateur inférieur à (moins de) ( < ), plutôt que le plus grand que l'opérateur ( > ), pour tester uniquement les données inférieures à zéro.
- Cliquez sur la cellule B7
- Tapez ce qui suit:
= MAX (SI (A1: B5 <0, A1: B5))
- Suivez les étapes ci-dessus pour créer la formule matricielle
- La réponse -8 devrait apparaître dans la cellule B7 car il s’agit du plus grand nombre négatif de la liste
Obtenir #VALUE! pour une réponse
Si les cellules B6 et B7 affichent le #VALUE! valeur d'erreur plutôt que les réponses indiquées ci-dessus, c'est probablement parce que la formule du tableau n'a pas été créée correctement.
Pour corriger ce problème, cliquez sur la formule dans la barre de formule et appuyez sur la touche Ctrl, Décalage et Entrer touches du clavier à nouveau.
