Parfois, plutôt que de trouver le nombre le plus grand ou le maximum pour l’ensemble de vos données, vous devez trouver le plus grand nombre dans un sous-ensemble, par exemple le plus grand nombre positif ou négatif.
Si la quantité de données est faible, la tâche peut être facile à accomplir en sélectionnant manuellement la plage correcte pour la fonction MAX.
Dans d’autres circonstances, comme dans le cas d’un grand échantillon de données non triées, la sélection correcte de la plage peut s’avérer difficile, voire impossible.
En combinant la fonction IF avec la fonction MAX dans une formule de tableau, il est possible de définir facilement des conditions – telles que des nombres positifs ou négatifs uniquement – afin que seules les données correspondant à ces paramètres soient testées par la formule.
COMMENT CRÉER UN TABLEAU DE DONNÉES POUR UNE ANALYSE D’HYPOTHÈSES DANS EXCEL ?
Répartition de la formule MAX IF dans un tableau
La formule utilisée dans ce tutoriel pour trouver le plus grand nombre positif est la suivante :
=MAX( IF( A1:B5>0, A1:B5 ))
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 renvoie un zéro ( 0 ).
Le rôle de chaque partie de la formule est le suivant :
- la fonction IF filtre les données afin que seuls les nombres qui répondent 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 toute la plage de données (par exemple, 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 simultanément sur les touches Ctrl, Shift et Enter du clavier une fois la formule saisie.
Le résultat est que la formule entière – y compris le signe égal – est entourée d’accolades. Voici un exemple :
{=MAX( IF( A1:B5>0, A1:B5 ) ) }
En raison des touches pressées pour créer la formule de tableau, elles sont parfois appelées formules CSE.
Exemple de formule de tableau MAX IF d’Excel
Comme le montre l’image ci-dessus, cet exemple de tutoriel utilise la formule de tableau MAX IF pour trouver les plus grandes valeurs positives et négatives 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 tutoriel
Saisissez les chiffres figurant dans l'image ci-dessus dans les cellules A1 à B5 d'une feuille de calcul.
Dans les cellules A6 et A7, saisissez les étiquettes Max Positif et Max Négatif.Saisie de la formule imbriquée MAX IF
Étant donné que nous créons à la fois une formule imbriquée et une formule de tableau, nous devons saisir l’intégralité de la formule dans une seule cellule de la feuille de calcul.
Une fois que vous avez saisi la formule, n’appuyez pas sur la touche Entrée du clavier et ne cliquez pas sur une autre cellule avec la souris, car nous devons transformer la formule en formule de tableau.
Cliquez sur la cellule B6 - l'endroit où les premiers résultats de la formule seront affichés.
Tapez ce qui suit :=MAX( IF ( A1:B5>0, A1:B5 ) )
Création de la formule de tableau
Appuyez sur les touches Ctrl et Shift du clavier et maintenez-les enfoncées.
Appuyez sur la touche Entrée du clavier pour créer la formule de tableau.
La réponse 45 doit apparaître dans la cellule B6, car il s'agit du plus grand nombre positif de la liste.Si vous cliquez sur la cellule B6, la formule de tableau complète s’affiche dans la barre de formule au-dessus de la feuille de calcul.
{ = MAX( IF( A1:B5>0, A1:B5 ) ) }
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.
L’objectif étant de trouver le plus grand nombre négatif, la deuxième formule utilise l’opérateur inférieur à ( < ), plutôt que l’opérateur supérieur à ( > ), pour tester uniquement les données inférieures à zéro.
Cliquez sur la cellule B7
Tapez ce qui suit :=MAX( IF ( A1:B5<0, A1:B5 ) )
Suivez les étapes ci-dessus pour créer la formule de tableau
La réponse -8 doit apparaître dans la cellule B7 car il s'agit du plus grand nombre négatif de la liste.Obtenir #VALEUR ! pour une réponse
Si les cellules B6 et B7 affichent la valeur d’erreur #VALUE ! plutôt que les réponses indiquées ci-dessus, c’est probablement parce que la formule de tableau n’a pas été créée correctement.
Pour corriger ce problème, cliquez sur la formule dans la barre de formule et appuyez à nouveau sur les touches Ctrl, Shift et Enter du clavier.