Instructions et exemples de formules matricielles
Les formules matricielles dynamiques, qu’elles utilisent des fonctions existantes ou les fonctions de tableau dynamique, ne doivent être entrées que dans une seule cellule, puis confirmées en appuyant sur entrée. Auparavant, les formules matricielles héritées nécessitent d’abord la sélection de la plage de sortie entière, puis la confirmation de la formule avec Ctrl + Maj + Entrée. Elles sont généralement appelées formules CSE .
Vous pouvez utiliser des formules matricielles pour effectuer des tâches complexes, telles que :
- Créez rapidement des exemples de jeu de données.
- Compter le nombre de caractères contenus dans une plage de cellules.
- Additionner uniquement les nombres qui remplissent certaines conditions, telles que les valeurs les plus basses d’une plage ou les nombres compris entre une limite supérieure et une limite inférieure.
- Additionner chaque nième valeur dans une plage de valeurs.
Les exemples suivants vous montrent comment créer des formules matricielles à cellule unique et à plusieurs cellules. Dans la mesure du possible, nous avons inclus des exemples illustrant certaines fonctions de matrice dynamique, ainsi que les formules matricielles existantes entrées à la fois sous forme de matrices dynamiques et héritées.
Cet exercice vous montre comment utiliser des formules de tableau à plusieurs cellules et à cellule unique pour calculer un ensemble de chiffres de vente. La première série d’étapes utilise une formule à plusieurs cellules pour calculer un ensemble de sous-totaux. La seconde série utilise une formule à cellule unique pour calculer un total général.
- Formule de tableau à plusieurs cellules
- Dans cette section, nous allons calculer le total des ventes de coupés et de de de pour chaque vendeur en entrant = F10 : F19 * G10 : G19 dans la cellule H10.
Lorsque vous appuyez sur entrée, les résultats sont affichés dans les cellules H10 : H19. Vous remarquerez que la plage de projection est mise en évidence avec une bordure lorsque vous sélectionnez une cellule dans la plage de projection. Vous remarquerez peut-être également que les formules dans les cellules H10 : H19 sont grisées. S’il s’agit d’une formule de référence, il vous faudra sélectionner la cellule H10, où se trouve la formule maîtresse.
- Formule matricielle à cellule unique
Dans la cellule H20 de l’exemple de classeur, tapez ou copiez-collez somme (F10 : F19 * G10 : G19), puis appuyez sur entrée.
Dans ce cas, Excel multiplie les valeurs du tableau (la plage de cellules de F10 à G19), puis utilise la fonction somme pour additionner les totaux. Le résultat produit un total général de 1 590 000 dollars de ventes.
Cet exemple illustre la puissance de ce type de formule. Par exemple, supposons que vous disposez de 1000 lignes de données. Vous pouvez additionner tout ou partie de ces données en créant une formule de tableau dans une seule cellule au lieu de faire glisser la formule vers le bas sur les 1000 lignes. Notez également que la formule à cellule unique de la cellule H20 est entièrement indépendante de la formule à plusieurs cellules (formule dans les cellules H10 à H19). Cela révèle un autre avantage des formules de tableau : leur souplesse d’utilisation. Vous pouvez modifier les autres formules dans la colonne H sans affecter la formule dans H20. Il peut également être utile d’avoir des totaux indépendants comme celui-ci, car il permet de valider la précision de vos résultats.
- Les formules matricielles dynamiques présentent également les avantages suivants :
- Cohérence Si vous cliquez sur l’une des cellules de H10 vers le bas, la même formule s’affiche. Cette cohérence peut être synonyme de plus grande précision.
- Sécurité Vous ne pouvez pas remplacer un composant d’une formule de tableau à plusieurs cellules. Par exemple, cliquez sur H11 de cellule, puis appuyez sur SUPPR. Excel ne modifie pas la sortie du tableau. Pour le modifier, vous devez sélectionner la cellule située dans le coin supérieur gauche de la matrice ou la cellule H10.
- Fichiers de plus petite taille Il est souvent possible d’utiliser une seule formule de tableau au lieu de plusieurs formules intermédiaires. Par exemple, l’exemple ventes de voitures utilise une formule matricielle pour calculer les résultats dans la colonne E. Si vous avez utilisé des formules standard, telles que G10, F11 * G11, F12 * G12, etc., vous avez utilisé 11 formules différentes pour calculer les mêmes résultats. Ce n’est pas une véritable affaire, mais si vous avez des milliers de lignes au total ? C’est alors qu’il peut s’avérer très important.
- Efficacité Les fonctions matricielles peuvent faciliter la création de formules complexes. La formule matricielle = somme (F10 : F19 * G10 : G19) est la même que celle-ci : = somme (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14 F15 G15 F16 G16 F17 G17 F18 G18 F19 G19).
- Empiètent Les formules matricielles dynamiques seront automatiquement propagées dans la plage de sortie. Si vos données sources se trouvent dans un tableau Excel, vos formules de tableau dynamique seront automatiquement redimensionnées lors de l’ajout ou de la suppression de données.
Créer une constante de matrice à deux dimensions
Les constantes de matrice sont un composant des formules matricielles. Vous pouvez créer des constantes de matrice en entrant une liste d’éléments et en mettant cette liste entre accolades ({ }), comme ceci :
= {1, 2, 3, 4, 5} ou = {« January », « février », « mars »}
Si vous séparez les éléments à l’aide de virgules, vous créez un tableau horizontal (une ligne). Si vous séparez les éléments à l’aide de points-virgules, vous créez un tableau vertical (une colonne). Pour créer un tableau à deux dimensions, séparez les éléments de chaque ligne par des virgules, puis délimitez chaque ligne par des points-virgules.
Les procédures suivantes vous permettront de vous familiariser avec la création de constantes horizontales, verticales et à deux dimensions. Nous allons vous montrer des exemples d’utilisation de la fonction Sequence pour générer automatiquement des constantes matricielles, ainsi que des constantes de matrice entrées manuellement.
- Créer une constante horizontale
Utilisez le classeur des exemples précédents ou créez un nouveau classeur. Sélectionnez une cellule vide et entrez = Sequence (1 ; 5). La fonction SEQUENCE génère une matrice d’une ligne et de 5 colonnes comme = {1, 2, 3, 4, 5}. Le résultat suivant s’affiche :
- Créer une constante verticale
Sélectionnez une cellule vide dans la salle située en dessous et entrez = Sequence (5)ou = {1 ; 2 ; 3 ; 4 ; 5}. Le résultat suivant s’affiche :
- Créer une constante
Sélectionnez une cellule vide dans la zone située à droite, puis entrez = Sequence (3, 4). Vous obtenez le résultat suivant :
Vous pouvez également entrer : ou = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12}, mais vous devez veiller à ce que vous ayez placé des points-virgules plutôt que des virgules.
Comme vous pouvez le constater, l’option de séquence offre des avantages importants par rapport à la saisie manuelle de valeurs de constantes de matrice. En premier lieu, vous économisez du temps, mais cela peut également permettre de limiter les erreurs d’entrée manuelle. Il est également plus facile de lire, en particulier, les points-virgules peuvent être difficiles à distinguer des séparateurs de virgule.
Syntaxe des constantes de matrice
Voici un exemple qui utilise des constantes de matrice dans le cadre d’une formule plus grande. Dans l’exemple de classeur, accédez à la constante dans une feuille de calcul de formule ou créez une feuille de calcul.
Dans la cellule D9, nous avons entré = Sequence (1 ; 5 ; 3 ; 1), mais vous pouvez également entrer 3, 4, 5, 6 et 7 dans les cellules A9 : H9. Il n’y a rien de particulier sur cette sélection de nombre, nous avons choisi un autre élément que 1-5 pour la différenciation.
Dans la cellule E11, entrez = somme (D9 : H9 * Sequence (1, 5))ou = somme (D9 : H9 * {1, 2, 3, 4, 5}). Les formules renvoient 85.
La fonction SEQUENCE génère l’équivalent de la constante matricielle {1, 2, 3, 4, 5}. Dans la mesure où Excel effectue d’abord des opérations sur des expressions placées entre parenthèses, les deux éléments suivants qui entrent en jeu sont les valeurs de cellule dans la cellule D9 : H9 et l’opérateur de multiplication (*). À ce stade, la formule multiplie les valeurs dans le tableau stocké par les valeurs correspondantes dans la constante. Cela équivaut à :
= Somme (D9 * 1, E9 * 2, F9 * 3, G9 * 4,-H9 * 5)ou = somme (3 * 1 ; 4 * 2 ; 5 * 3 ; 5)
Enfin, la fonction somme additionne les valeurs et renvoie 85.
Pour éviter d’utiliser le tableau stocké et conserver l’opération entièrement en mémoire, vous pouvez le remplacer par une autre constante de matrice :
= Somme (séquence (1 ; 5 ; 3 ; 1) * séquence (1, 5))ou = somme ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})
Éléments que vous pouvez utiliser dans des constantes de matrice
- Les constantes matricielles peuvent contenir des nombres, du texte, des valeurs logiques (comme vrai et faux) et des valeurs d’erreur telles que #N/A. Vous pouvez utiliser des nombres sous forme de nombres entiers, décimaux et scientifiques. Si vous incluez du texte, vous devez le placer entre guillemets (« texte »).
- Les constantes de matrice ne peuvent pas contenir de tableaux, formules ou fonctions supplémentaires. En d’autres termes, elles ne peuvent contenir que du texte ou des chiffres séparés par des virgules ou des points-virgules. Excel affiche un message d’avertissement lorsque vous entrez une formule telle que {1,2,A1:D4} ou {1,2,SOMME(Q2:Z8)}. Par ailleurs, les valeurs numériques ne peuvent pas contenir de signes de pourcentage ou de dollar, de virgules ou de parenthèses.
Nommer les constantes de matrice
L’une des meilleures manières d’utiliser des constantes de matrice consiste à les nommer. Les constantes nommées peuvent être beaucoup plus faciles à utiliser et peuvent dissimuler une partie de la complexité de vos formules aux autres utilisateurs. Pour nommer une constante de matrice et l’utiliser dans une formule, procédez comme suit :
Accédez aux formules > noms définis > définir un nom. Dans la zone nom , tapez Trimestre1. Dans la zone Fait référence à, entrez la constante suivante (n’oubliez pas de taper les accolades) :
={« Janvier », »Février », »Mars »}
La boîte de dialogue doit maintenant ressembler à ceci :
Cliquez sur OK, puis sélectionnez n’importe quelle ligne contenant trois cellules vides et entrez = Trimestre1.
Le résultat suivant s’affiche :
Si vous souhaitez que les résultats soient déplacés verticalement au lieu d’être horizontaux, vous pouvez utiliser =transposer(Trimestre1).
Si vous souhaitez afficher une liste de 12 mois, comme vous pouvez l’utiliser lors de la création d’un état financier, vous pouvez baser une partie de l’année en cours avec la fonction SEQUENCE. Ce n’est pas tout ce qu’il est possible d’afficher, même si le mois est affiché, il existe une date valide au-delà de celle que vous pouvez utiliser dans d’autres calculs. Ces exemples se trouvent dans les feuilles de calcul de matrices nommées et de jeux de données de l’exemple de jeu rapide dans l’exemple de classeur.
= TEXTE (DATE (ANNEe (aujourdhui ()), ordre (1, 12), 1), « MMM »)
Cela utilise la fonction date pour créer une date sur la base de l’année en cours, la séquence génère une constante de matrice entre 1 et 12 pour janvier et décembre, puis la fonction texte convertit le format d’affichage en « MMM » (Jan, fév, mars, etc.). Pour afficher le nom du mois complet (par exemple, janvier), vous devez utiliser « mmmm ».
Lorsque vous utilisez une constante nommée en tant que formule matricielle, n’oubliez pas d’entrer le signe égal, comme dans = Trimestre1, pas seulement Trimestre1. Si vous l’omettez, Excel interprétera le tableau comme une chaîne de texte et votre formule ne fonctionnera pas comme prévu. Enfin, n’oubliez pas que vous pouvez utiliser des combinaisons de fonctions, de texte et de chiffres. Tout dépend de la manière dont vous souhaitez obtenir votre créativité.
Constantes de matrice en action
Les exemples suivants montrent de quelle manière vous pouvez mettre les constantes de matrice à contribution dans des formules de tableau. Certains de ces exemples utilisent la fonction transpose pour convertir des lignes en colonnes et inversement.
- Plusieurs éléments dans un tableau
Entrez = Sequence (1, 12) * 2ou = {1, 2, 3, 4 ; 5, 6, 7, 8, 9, 10, 11, 12} * 2
Vous pouvez également diviser par (/), ajouter avec (+) et soustraire à (–).
- Mettre au carré les éléments dans un tableau
Entrez = Sequence (1, 12) ^ 2ou = {1, 2, 3, 4, 5, 6, 7, 8 ; 9, 10, 11, 12} ^ 2
- Rechercher la racine carrée d’éléments carrés dans un tableau
Entrez =sqrt(séquence (1, 12) ^ 2)ou = sqrt ({1, 2, 3, 4 ; 5, 6, 7, 8, 9, 10, 11, 12} ^ 2)
- Transposer une ligne à une dimension
Entrez = transposer (séquence (1, 5))ou = transposer ({1, 2, 3, 4, 5})
Même si vous avez entré une constante de matrice horizontale, la fonction TRANSPOSE convertit la constante de matrice en une colonne.
- Transposer une colonne à une dimension
Entrée = transposer (séquence (5, 1))ou = transposer ({1 ; 2 ; 3 ; 4 ; 5)
Même si vous avez entré une constante de matrice verticale, la fonction TRANSPOSE convertit la constante en une ligne.
- Transposer une constante à deux dimensions
Entrez = transposer (séquence (3 ; 4))ou = transposer ({1, 2, 3, 4 ; 5, 6, 7, 8, 9, 10, 11, 12})
La fonction TRANSPOSE convertit chaque colonne en une série de colonnes.
Appliquer des formules de tableau de base
Cette section présente des exemples de formules de tableau de base.
- Créer un tableau à partir de valeurs existantes
L’exemple suivant explique comment utiliser des formules de tableau pour créer un tableau à partir d’un tableau existant.
Entrée = séquence (3 ; 6 ; 10 ; 10); ou = {10 ; 20 ; 30 ; 40 ; 50 ; 60 ; 70 ; 80 90100110120 ; 130140150160170180}
Veillez à taper {(accolade ouvrante) avant de taper 10, et} (accolade fermante) après avoir tapé 180, car vous créez un tableau de nombres.
Entrez = D9 #ou = D9 : i11 dans une cellule vide. Un tableau de cellules de 3 x 6 s’affiche avec les mêmes valeurs que celles affichées dans la cellule D9 : D11. Le signe # est appelé l’ opérateur de plage renverséeet il est Excel’s de faire référence à la plage de la matrice entière au lieu de la taper.
- Créer une constante de matrice à partir de valeurs existantes
Vous pouvez obtenir les résultats d’une formule de tableau renversé et les convertir en parties de composants. Sélectionnez la cellule D9, puis appuyez sur F2 pour passer en mode d’édition. Ensuite, appuyez sur F9 pour convertir les références de cellule en valeurs, puis sur Excel dans une constante matricielle. Lorsque vous appuyez sur entrée, la formule, = D9 #, doit maintenant être = {10, 20, 30 ; 40 ; 50 ; 60 ; 70 ; 80 ; 90}.
- Compter les caractères dans une plage de cellules
L’exemple suivant vous montre comment compter le nombre de caractères d’une plage de cellules. Cela inclut les espaces.
= SOMME (NBCAR (C9 : C13))
Dans ce cas, la fonction NBCAR renvoie la longueur de chaque chaîne de texte dans chacune des cellules de la plage. La fonction somme additionne alors ces valeurs et affiche le résultat (66). Pour obtenir le nombre moyen de caractères, vous pouvez utiliser les éléments suivants :
= MOYENNE (FONCTION LEN (C9 : C13))
- Contenu de la cellule la plus longue dans la plage C9 : C13
= INDEX (C9 : C13, MATCH (MAX (C9 (C9 : C13)), NBCAR (C9 : C13), 0), 1)
Cette formule ne fonctionne que lorsqu’une plage de données contient une seule colonne de cellules.
Examinons la formule d’un peu plus près, en commençant par les éléments intérieurs et en procédant vers l’extérieur. La fonction NBCAR renvoie la longueur de chaque élément de la plage de cellules D2 : D6. La fonction max calcule la plus grande valeur parmi les éléments, ce qui correspond à la chaîne de texte la plus longue dans la cellule D3.
C’est ici que les choses se compliquent un peu. La fonction EQUIV calcule le décalage (position relative) de la cellule qui contient la chaîne de texte la plus longue. Pour ce faire, elle a besoin de trois arguments : unevaleur de recherche, un tableau de recherche et un type de correspondance. La fonction EQUIV recherche la valeur de recherche spécifiée dans le tableau de recherche. Dans notre exemple, la valeur de recherche est la chaîne de texte la plus longue :
MAX (FONCTION LEN (C9 : C13)
et cette chaîne réside dans le tableau suivant :
NBCAR (C9 : C13)
L’argument match type dans le cas présent est 0. Le type de match peut être une valeur de 1, 0 ou-1.
- 1 : renvoie la plus grande valeur qui est inférieure ou égale à la valeur valeur de recherche.
- 0 : renvoie la première valeur exactement égale à la valeur de recherche.
- -1 : renvoie la plus petite valeur supérieure ou égale à la valeur de recherche spécifiée.
- Si vous omettez de spécifier un type de correspondance, Excel prend la valeur 1.
Enfin, la fonction index accepte les arguments suivants : un tableau et un numéro de ligne et de colonne dans ce tableau. La plage de cellules C9 : C13 fournit la matrice, la fonction EQUIV fournit l’adresse de la cellule et l’argument final (1) spécifie que la valeur provient de la première colonne du tableau.
Si vous souhaitez obtenir le contenu de la chaîne de texte la plus petite, remplacez MAX dans l’exemple ci-dessus par min.
- Chercher les n valeurs les plus petites dans une plage
Cet exemple montre comment trouver les trois valeurs les plus petites dans une plage de cellules, où un tableau d’exemples de données dans les cellules B9 : B18has a été créé avec : = ent (ALEAT(10 ; 1) * 100). Notez que ALEAT est une fonction volatile, de sorte que vous obtenez un nouvel ensemble de nombres aléatoires chaque fois qu’Excel calcule.
Entrez = petit (B9 #, séquence (D9), = petit (B9 : B18 ; {1 ; 2 ; 3})
Cette formule utilise une constante de matrice pour évaluer la fonction petite fonction trois fois et retourner les 3 plus petits membres du tableau contenus dans les cellules B9 : B18, où 3 est une valeur de variable dans la cellule D9. Pour trouver plus de valeurs, vous pouvez augmenter la valeur de la fonction de séquence ou ajouter d’autres arguments à la constante. Vous pouvez également utiliser des fonctions supplémentaires avec cette formule, telles que SOMME ou MOYENNE. Par exemple :
= SOMME (PETITE (B9, ORDRE (D9))
= MOYENNE (PETITE (B9 #, SÉQUENCE (D9))
- Chercher les n valeurs les plus grandes dans une plage
Pour rechercher les valeurs les plus élevées dans une plage, vous pouvez remplacer la fonction petite en fonction de la fonction grande. Par ailleurs, l’exemple suivant utilise les fonctions LIGNE et INDIRECT.
Entrez = grande grande (B9 #, ligne (indirect (« 1:3 »))) ou = grande (B9 : B18 ; ligne (indirect (« 1:3 ») ))
À ce stade, il peut être utile d’en savoir un peu sur les fonctions LIGNE et INDIRECT. Vous pouvez utiliser la fonction LIGNE ou créer un tableau d’entiers consécutifs. Par exemple, sélectionnez une zone vide et entrez :
=LIGNE(1:10)
La formule crée une colonne de 10 entiers consécutifs. Pour repérer un problème potentiel, insérez une ligne au-dessus de la plage qui contient la formule de tableau (c’est-à-dire, au-dessus de la ligne 1). Excel ajuste les références de ligne et la formule génère désormais des entiers de 2 à 11. Pour corriger ce problème, ajoutez la fonction INDIRECT à la formule :
=LIGNE(INDIRECT(« 1:10 »))
La fonction INDIRECT utilise des chaînes de texte comme arguments (c’est la raison pour laquelle la plage 1:10 est entourée de guillemets). Excel n’ajuste pas les valeurs de texte lorsque vous insérez des lignes ou déplacez la formule de tableau d’une manière ou d’une autre. En conséquence, la fonction LIGNE génère toujours le tableau d’entiers que vous voulez. Vous pouvez aussi facilement utiliser une séquence :
= SEQUENCE (10)
Examinons la formule que vous avez utilisée auparavant, = grande grande (B9 #, ligne (indirecte (« 1:3 »))), en partant des parenthèses internes et en travaillant vers l’extérieur : la fonction INDIRECT renvoie un ensemble de valeurs de texte, dans ce cas les valeurs 1 à 3. La fonction ligne génère un tableau de colonnes à trois cellules. La fonction grande. valeur utilise les valeurs de la plage de cellules B9 : B18 et elle est évaluée trois fois, une fois pour chaque référence renvoyée par la fonction ligne. Pour trouver plus de valeurs, vous devez ajouter une plus grande plage de cellules à la fonction INDIRECT. Pour finir, comme avec les petits exemples, vous pouvez utiliser cette formule avec d’autres fonctions, telles que somme et moyenne.
Gestion des erreurs
- Additionner une plage qui contient des valeurs d’erreur
La fonction somme dans Excel ne fonctionne pas lorsque vous essayez de additionner une plage qui contient une valeur d’erreur, par exemple, #VALUE. ou #N/A. Cet exemple vous montre comment additionner les valeurs d’une plage nommée Data contenant des erreurs :
- =SOMME(SI(ESTERREUR(Données), » »,Données))
La formule crée un tableau contenant les valeurs d’origine sans les valeurs d’erreur. En partant des fonctions intérieures et en procédant vers l’extérieur, la fonction ESTERREUR recherche les erreurs dans la plage de données (Données). La fonction SI renvoie une valeur spécifique si une condition que vous spécifiez prend la valeur VRAI et une autre valeur si la condition prend la valeur FAUX. Dans ce cas, la fonction renvoie des chaînes vides (« ») pour toutes les valeurs d’erreur car elles sont VRAIES, et renvoie les valeurs restantes de la plage (Données) car elles prennent la valeur FAUX, ce qui signifie qu’elles ne contiennent pas de valeurs d’erreur. La fonction SOMME calcule ensuite le total pour le tableau filtré.
- Compter le nombre de valeurs d’erreur dans une plage
Cet exemple est semblable à la formule précédente, à ceci près qu’il renvoie le nombre de valeurs d’erreur dans une plage nommée données au lieu de les filtrer :
=SOMME(SI(ESTERREUR(Données),1,0))
Cette formule crée un tableau qui contient la valeur 1 pour les cellules contenant des erreurs et la valeur 0 pour les cellules n’en contenant pas. Vous pouvez simplifier la formule et obtenir le même résultat en supprimant le troisième argument pour la fonction SI, comme ceci :
=SOMME(SI(ESTERREUR(Données),1))
Si vous ne spécifiez pas l’argument, la fonction SI renvoie FAUX si une cellule ne contient pas de valeur d’erreur. Vous pouvez aller encore plus loin dans la simplification de la formule :
=SOMME(SI(ESTERREUR(Données)*1))
Cette version fonctionne car VRAI*1=1 et FAUX*1=0.
Additionner des valeurs basées sur des conditions
Il est possible que vous deviez additionner des valeurs basées sur des conditions.
Par exemple, cette formule matricielle additionne uniquement les entiers positifs d’une plage nommée ventes, qui représente les cellules E9 : E24 dans l’exemple ci-dessus :
=SOMME(SI(Ventes>0,Ventes))
La fonction si crée un tableau de valeurs positives et fausses. La fonction SOMME ignore surtout les valeurs fausses car 0+0=0. La plage de cellules que vous utilisez dans cette formule peut se composer d’un nombre indéfini de lignes et de colonnes.
Vous pouvez également additionner des valeurs répondant à plusieurs conditions. Par exemple, cette formule de tableau calcule les valeurs supérieures à 0 et inférieures à 2500 :
= SOMME ((ventes>0) * (ventes<2500) * (ventes))
N’oubliez pas que cette formule renvoie une erreur si la plage contient une ou plusieurs cellules non numériques.
Vous pouvez également créer des formules de tableau utilisant un type ou une condition OU. Par exemple, vous pouvez additionner des valeurs supérieures à 0 ou inférieures à 2500 :
= SOMME (si ((ventes>0) + (ventes<2500), ventes))
Vous ne pouvez pas utiliser les fonctions ET et OU directement dans des formules de tableau car ces fonctions renvoient un résultat unique (VRAI ou FAUX) et que les fonctions de tableau nécessitent des tableaux de résultats. Il est possible de contourner le problème en utilisant la logique présentée dans la formule précédente. En d’autres termes, vous effectuez des opérations mathématiques, telles que l’addition ou la multiplication de valeurs qui répondent à la condition ou ou.
Cet exemple indique comment supprimer les zéros d’une plage lorsque vous devez calculer la moyenne des valeurs de cette plage. La formule utilise une plage de données nommée Ventes :
=MOYENNE(SI(Ventes<>0,Ventes))
La fonction SI crée un tableau de valeurs différentes de 0, puis passe ces valeurs à la fonction MOYENNE.
Compter le nombre de différences entre deux plages de cellules
Cette formule de tableau compare les valeurs de deux plages de cellules nommées MesDonnées et VosDonnées et renvoie le nombre de différences entre les deux. Si les contenus des deux plages sont identiques, la formule renvoie 0. Pour utiliser cette formule, les plages de cellules doivent avoir la même taille et la même dimension. Par exemple, si MyData est une plage de 3 lignes de 5 colonnes, vosdonnées doit également comporter 3 lignes de 5 colonnes.
=SOMME(SI(MesDonnées=VosDonnées,0,1))
La formule crée un tableau de la taille des plages que vous comparez. La fonction SI remplit le tableau avec des valeurs 0 et 1 (0 pour les cellules différentes et 1 pour les cellules identiques). La fonction SOMME renvoie ensuite la somme des valeurs du tableau.
Vous pouvez simplifier la formule de la façon suivante :
= SOMME (1 * (MyData<>vosdonnées))
À l’instar de la formule qui compte les valeurs d’erreur dans une plage, cette formule fonctionne car VRAI*1=1 et FAUX*1=0.
Cette formule de tableau renvoie le numéro de ligne de la valeur maximale d’une plage de colonnes uniques nommée Données :
=MIN(SI(Données=MAX(Données),LIGNE(Données), » »))
La fonction SI crée un tableau correspondant à la plage nommée Données. Si une cellule correspondante contient la valeur maximale de la plage, le tableau contient le numéro de la ligne. Dans le cas contraire, le tableau contient une chaîne vide (« »). La fonction MIN utilise le nouveau tableau comme second argument et renvoie la valeur la plus petite, correspondant au numéro de ligne de la valeur maximale dans la plage Données. Si la plage nommée Données contient des valeurs maximales identiques, la formule renvoie la ligne de la première valeur.
Si vous souhaitez renvoyer l’adresse de la cellule réelle d’une valeur maximale, utilisez cette formule :
=ADRESSE(MIN(SI(Données=MAX(Données),LIGNE(Données), » »)),COLONNE(Données))
Vous trouverez des exemples similaires dans l’exemple de classeur de la feuille de calcul différences entre les jeux de données .
