Instructions et exemples de formules matricielles

Important :  Cet article a été traduit automatiquement, voir l’avertissement. Vous pouvez consulter la version en anglais de cet article ici.

Pour devenir un utilisateur averti d’Excel, vous devez savoir comment utiliser des formules matricielles, qui peuvent effectuer des calculs que vous ne pouvez pas effectuer à l’aide de formules matricielles non. L’article suivant est basé sur une série de colonnes Excel Power utilisateur rédigé par Colin Wilcox et adaptée à partir des chapitres 14 et 15 de Les formules Excel 2002, un carnet d’adresses rédigé par John Walkenbach, MVP Excel.

Contenu de cet article

En savoir plus sur les formules de tableau

En savoir plus sur les constantes de matrice

Créer des constantes à une ou deux dimensions

Application de formules matricielles de base

Application de formules matricielles avancées

En savoir plus sur les formules matricielles

Cette section présente des formules matricielles et explique comment entrer, modifier et les résoudre.

Pourquoi utiliser des formules de tableau ?

Si vous avez expérience à l’aide de formules dans Excel, vous savez que vous pouvez effectuer quelques opérations assez sophistiquées. Par exemple, vous pouvez calculer le coût total d’un emprunt sur n’importe quel nombre d’années donné. Toutefois, si vous voulez vraiment maîtres formules dans Excel, vous devez savoir comment utiliser des formules matricielles. Vous pouvez utiliser des formules pour effectuer des tâches complexes, tels que :

  • 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 faibles 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.

Remarque : Vous pouvez constater des formules matricielles appelées « Formules portent », étant donné que vous appuyez sur CTRL + MAJ + ENTRÉE pour les entrer dans vos classeurs.

Présentation des matrices et des formules matricielles

Si vous avez fini de programmation encore un petit, vous avez probablement exécuté sur le magasin de termes matrice. Dans notre cas, un tableau est un ensemble d’éléments. Dans Excel, ces éléments peuvent résider dans une seule ligne (appelé une matrice horizontale dimension), une colonne (une matrice verticale dimension), ou plusieurs lignes et colonnes (une matrice à deux dimensions). Vous ne pouvez pas créer des tableaux ou des formules dans Excel.

Une formule matricielle est une formule qui peut effectuer des calculs plusieurs sur un ou plusieurs des éléments dans un tableau. Les formules matricielles peuvent retourner plusieurs résultats ou un seul résultat. Par exemple, vous pouvez placer une formule matricielle dans une plage de cellules et utiliser la formule matricielle pour calculer une colonne ou ligne de sous-totaux. Vous pouvez également placer une formule matricielle dans une seule cellule et puis calculer un montant unique. Une formule matricielle qui réside dans plusieurs cellules s’appelle une formule à plusieurs cellule, et une formule matricielle qui se trouve dans une seule cellule est appelée une formule à cellule unique.

Les exemples de la section suivante décrivent comment créer des formules matricielles à cellule unique et à plusieurs cellules.

Essayez !

Cet exercice vous montre comment utiliser des formules matricielles à 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.

Créer une formule matricielle à plusieurs cellules

  1. Ouvrez un classeur vierge.

  2. Copier les exemples de données de feuille de calcul et collez-le dans le nouveau classeur commençant par la cellule A1.

Vendeur

Type de voiture

Nombre vendu

Prix unitaire

Total des ventes

Jacques

Berline

5

2200

Coupé

4

1800

Osorio

Berline

6

2300

Coupé

8

1700

Thierry

Berline

3

2000

Coupé

1

1600

Antoine

Berline

9

2150

Coupé

5

1950

Durrer

Berline

6

2 250

Coupé

8

2000

  1. Utilisez le bouton Options de collage image du bouton qui s’affiche à proximité pour correspondre à la mise en forme de destination.

  2. Pour multiplier les valeurs du tableau (la plage de cellules C2 à D11), sélectionnez les cellules E2 à E11, puis entrez la formule suivante dans la barre de formule :

    =C2:C11*D2:D11

  3. Appuyez sur CTRL + MAJ + ENTRÉE.

Excel entoure la formule entre accolades ({}) et le place une instance de la formule dans chaque cellule de la plage sélectionnée. Cela se produit très rapidement, de sorte que ce que vous voyez dans la colonne E est le montant total des ventes pour chaque type de voiture pour chaque vendeur.

Exemple de données

Créer une formule de tableau à cellule unique

  1. Dans la cellule A13 du classeur, tapez Total des ventes.

  2. Dans la cellule B13, tapez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    =SOMME(C2:C11*D2:D11)

Dans ce cas, Excel multiplie les valeurs du tableau (la plage de cellules C2 à D11) et puis utilise la fonction somme pour additionner les totaux. Le résultat est un total général de $111,800 des ventes. Cet exemple montre comment puissantes ce type de formule peut être. Par exemple, supposons que vous avez 15 000 lignes de données. Vous pouvez additionner tout ou partie des données en créant une formule matricielle dans une seule cellule.

Notez également que la formule à cellule unique (dans la cellule B13) est entièrement indépendante de la formule à plusieurs cellule (la formule dans les cellules E2 à E11). Cet exemple illustre un autre avantage de l’utilisation de formules matricielles — flexibilité. Vous pouvez prendre des actions, telles que modifier les formules dans la colonne E ou supprimer cette colonne totalement différent, sans affecter la formule à cellule unique.

Les formules de tableau offrent également les avantages suivants :

  • Cohérence    Si vous cliquez sur n’importe quelle cellule comprise entre E2 et celles placées plus bas, vous voyez la même formule. Cette cohérence peut être synonyme de plus grande précision.

  • Sécurité des messages    Vous ne pouvez pas remplacer un composant d’une formule matricielle à plusieurs cellules. Par exemple, cliquez sur la cellule E3 et appuyez sur SUPPR. Vous devez sélectionner l’intégralité de la plage de cellules (E2 via E11) et modifiez la formule pour l’intégralité du tableau, ou quitter le tableau en l’état. Par mesure de sécurité supplémentaire, vous devez appuyer sur CTRL + MAJ + ENTRÉE pour confirmer la modification de la formule.

  • Plus petite taille de fichier    Vous pouvez souvent utiliser une seule formule matricielle au lieu de plusieurs formules intermédiaires. Par exemple, le classeur que vous avez créé pour cet exercice utilise une formule matricielle pour calculer les résultats dans la colonne E. Si vous aviez utilisé formules standards (telle que = C2 * D2), vous avez souhaitez utilisé 11 différentes formules pour calculer les mêmes résultats.

Syntaxe des formules matricielles

En règle générale, les formules matricielles utilisent une syntaxe de formule standard. Ils sont tous commencent par un signe égal (=), et vous pouvez utiliser les fonctions Excel intégrées dans vos formules matricielles. La différence clée est que lorsque vous utilisez une formule matricielle, vous devez appuyer sur CTRL + MAJ + ENTRÉE pour entrer votre formule. Lorsque vous effectuez cette opération, Excel entoure votre formule matricielle avec accolades : Si vous tapez les accolades manuellement votre formule sera convertie en une chaîne de texte et ne fonctionnera pas.

La prochaine chose que vous devez comprendre est que les fonctions de tableau sont un formulaire de raccourci. Par exemple, la fonction à plusieurs cellule que vous avez utilisé auparavant de revient :

= C2 * D2
= C3 * D3

et ainsi de suite. La formule à cellule unique dans la cellule B13 condense toutes ces opérations de multiplication, ainsi que l’arithmétique requis pour ajouter les sous-totaux : = E2 + E3 + E4et ainsi de suite.

Règles pour la saisie et modification des formules matricielles

La règle principale pour la création d’une formule matricielle vaut extensible : appuyez sur CTRL + MAJ + ENTRÉE chaque fois que vous devez entrer ou modifier une formule matricielle. Cette règle s’applique aux formules à cellule unique et à plusieurs cellules.

Chaque fois que vous travaillez avec des formules à plusieurs cellules, vous devez également suivre ces règles :

  • Vous devez sélectionner la plage de cellules à retenir vos résultats avant d’avoir entré la formule. Vous avez effectué cette étape 3 de l’exercice formule matricielle à plusieurs cellules lorsque vous sélectionniez cellules E2 à E11.

  • Vous ne pouvez pas modifier le contenu d’une cellule dans une formule matricielle. Pour cela, sélectionnez la cellule E3 dans l’exemple de classeur et appuyez sur Supprimer.

  • Vous pouvez déplacer ou supprimer une formule de tableau dans son intégralité, mais vous ne pouvez pas déplacer ou supprimer partie de celui-ci. En d’autres termes, pour réduire une formule matricielle, vous commencez par supprimez la formule existante et repartir de zéro.

    Conseil : Pour supprimer une formule matricielle, sélectionnez l’intégralité de la formule (par exemple, = C2 : C11 * D2 : D11), appuyez sur SUPPR, et appuyez sur CTRL + MAJ + ENTRÉE.

  • Vous ne pouvez pas insérer de cellules vides ou supprimer des cellules dans une formule matricielle à plusieurs cellules.

Développement d’une formule de tableau

Dans certains cas, vous devrez peut-être développer une formule matricielle. (N’oubliez pas que vous ne pouvez pas réduire une formule matricielle). Le processus n’est pas compliqué, mais vous n’oubliez pas les règles répertoriées dans la section précédente.

  1. Dans l’exemple de classeur, désactivez tout texte et les formules à cellule unique qui sont trouvent en dessous du tableau principal.

  2. Collez ces lignes de données supplémentaires dans le classeur commençant par la cellule A12. Utilisez le bouton Options de collage image du bouton qui s’affiche à proximité pour correspondre à la mise en forme de destination.

Mangel

Berline

6

2500

Coupé

7

1900

Garcia

Berline

4

2200

Coupé

3

2000

Gregor

Berline

8

2300

Coupé

8

2100

  1. Sélectionnez la plage de cellules qui contient la formule matricielle actuelle (E2:E11), ainsi que les cellules vides (E12:E17) en regard des nouvelles données. En d’autres termes, sélectionnez les cellules E2:E17.

  2. Appuyez sur F2 pour passer en mode d’édition.

  3. Dans la barre de formule, placez-vous C11C17, placez-vous D11D17et appuyez sur CTRL + MAJ + ENTRÉE. Excel met à jour la formule dans les cellules E2 à E11 et le place une instance de la formule dans les nouvelles cellules, E12 à E17.

    Exemple de données

Inconvénients liés à l’utilisation de formules matricielles

Les formules matricielles peuvent sembler magiques, mais ils ont également présenter quelques inconvénients :

  • Vous risquez d’oublier parfois d’appuyer sur CTRL + MAJ + ENTRÉE. N’oubliez pas d’appuyer sur cette combinaison de touches à chaque fois que vous entrez ou modifiez une formule matricielle.

  • D’autres utilisateurs peuvent ne pas comprennent vos formules. Dans la pratique, les formules matricielles ne sont généralement pas documentées dans une feuille de calcul, afin que si d’autres utilisateurs doivent pouvoir modifier vos classeurs, vous devez éviter les formules matricielles ou apporter ne savez ces utilisateurs comprennent comment les modifier.

  • Suivant la vitesse de traitement et la mémoire de votre ordinateur, les formules de tableau volumineuses peuvent ralentir vos calculs.

Pour plus d’informations sur les formules matricielles de travail, voir les rubriques suivantes :

Haut de la page

En savoir plus sur les constantes de matrice

Cette section présente les constantes de matrice et explique comment les insérer, les modifier et résoudre les problèmes liés à leur utilisation.

Présentation des constantes de matrice

Constantes de matrice sont un composant de formules matricielles. Créer des constantes de matrice en entrant une liste d’éléments et puis manuellement autour de la liste entre accolades ({}), comme suit :

={ 1,2,3,4,5}

Plus haut dans cet article, nous mise en évidence qu’il soit nécessaire d’appuyer sur CTRL + MAJ + ENTRÉE lorsque vous créez des formules matricielles. Étant donné que les constantes matricielles sont un composant de formules matricielles, vous délimitent l’une des constantes entre accolades manuellement en tapant les. Vous utilisez puis CTRL + MAJ + ENTRÉE pour entrer la formule entière.

Si vous délimitez (distinct) les éléments par des virgules, créer une matrice horizontale (ligne). Si vous délimitez les éléments par des points-virgules, vous créez une matrice verticale (colonne). Pour créer un tableau à deux dimensions, vous délimitez les éléments de chaque ligne à l’aide de virgules, et vous délimitez chaque colonne à l’aide des points-virgules.

Comme avec des formules, vous pouvez utiliser des constantes de matrice avec n’importe lequel des fonctions intégrées fournis par Excel. Les sections suivantes expliquent comment créer chaque type de constante et l’utilisation de ces constantes des fonctions dans Excel.

Créer des constantes à une ou deux dimensions

La procédure suivante vous permettra de vous familiariser avec la création de constantes horizontales, verticales et à deux dimensions.

Créer une constante horizontale

  1. Utilisez le classeur des exemples précédents ou créez un nouveau classeur.

  2. Sélectionnez les cellules A1 à E1.

  3. Dans la barre de formule, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    ={1,2,3,4,5}

    Remarque : Dans ce cas, vous devez taper les accolades ouvrantes et fermantes ({ }).

Vous obtenez le résultat suivant.

Constante de matrice horizontale dans une formule

Vous vous demandez peut-être pourquoi vous ne pouvez pas simplement taper les numéros manuellement. La section utilisation des constantes dans les formules , plus loin dans cet article montre les avantages de l’utilisation de constantes de matrice.

Créer une constante verticale

  1. Dans votre classeur, sélectionnez une colonne de cinq cellules.

  2. Dans la barre de formule, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    ={1;2;3;4;5}

Vous obtenez le résultat suivant.

Constante de matrice verticale dans une formule matricielle

Créer une constante

  1. Dans votre classeur, sélectionnez un bloc de cellules de quatre colonnes de largeur et trois lignes de hauteur.

  2. Dans la barre de formule, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    ={1,2,3,4;5,6,7,8;9,10,11,12}

Vous obtenez le résultat suivant :

Constante de matrice à deux dimensions dans une formule matricielle

Utiliser des constantes dans les formules

À présent que vous êtes habitué à la saisie de constantes de matrice, Voici un exemple simple qui utilise ce que nous avons abordés :

  1. Ouvrez une feuille de calcul vide.

  2. Copiez le tableau suivant commençant par la cellule A1. Utilisez le bouton Options de collage image du bouton qui s’affiche à proximité pour correspondre à la mise en forme de destination.

3

4

5

6

7

  1. Dans la cellule A3, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    = Somme ( A1:E1* {1,2,3,4,5})

Notez qu’Excel entoure la formule avec un jeu supplémentaire de parenthèses, car vous l’avez entrée en tant que formule matricielle.

Formule matricielle avec constante de matrice

La valeur 85 apparaît dans la cellule A3. La section suivante explique comment fonctionne la formule.

Syntaxe des constantes de matrice

La formule que vous venez d’utiliser contient différentes parties.

Syntaxe de la formule matricielle avec constante de matrice

1. Fonction

2. Tableau stocké

3. Opérateur

4. Constante de matrice

Le dernier élément dans les parenthèses est la constante de matrice : {12,3,4,5}. N’oubliez pas qu’Excel entourez pas constantes de matrice avec accolades ; Vous devez faire ceci. N’oubliez pas également qu’après avoir ajouté une constante à une formule matricielle, vous appuyez sur CTRL + MAJ + ENTRÉE pour entrer la formule.

Étant donné qu’Excel effectue des opérations sur les expressions entre parenthèses en premier, les deux éléments suivants qui entrent ensuite en jeu sont les valeurs stockées dans le classeur (A1:E1) et l’opérateur. À ce stade, la formule multiplie les valeurs dans le tableau stocké par les valeurs correspondantes dans la constante. Cela équivaut à :

= Somme ( A1 * 1, B1 * 2, C1 * D1 3 * 4, E1 * 5)

Enfin, la fonction somme additionne les valeurs et la somme 85 apparaît dans la cellule A3 :

Pour éviter d’utiliser le tableau stocké et conserver l’opération entièrement en mémoire, remplacez le tableau stocké par une autre constante de matrice :

= Somme ( {3,4,5,6,7} * {1,2,3,4,5})

Pour cela, copiez la fonction, sélectionnez une cellule vide dans votre classeur, coller la formule dans la barre de formule et appuyez sur CTRL + MAJ + ENTRÉE. Vous voyez le même résultat que dans l’exercice précédent qui utilisé la formule matricielle le = SUM(A1:E1*{1,2,3,4,5}).

Éléments que vous pouvez utiliser dans des constantes

Constantes matricielles peuvent contenir des nombres, texte, des valeurs logiques (telles que VRAI et faux) et les valeurs d’erreur (par exemple, # n/a). Vous pouvez utiliser des nombres dans les formats de nombre entier, décimale et scientifiques. Si vous ajoutez du texte, vous devez entourer ce texte entre guillemets doubles ("").

Les constantes matricielles ne peut pas contenir des tableaux, des formules ou fonctions. En d’autres termes, ils peuvent contenir uniquement du texte ou des nombres séparés par des virgules ou des points-virgules. Excel affiche un message d’avertissement lorsque vous entrez une formule comme {1, 2, a1 : D4} ou {1,2,SUM(Q2:Z8)}. En outre, des valeurs numériques ne peut pas contenir des signes de pourcentage, des signes dollar, des virgules ou des parenthèses.

Nommer les constantes de matrice

Éventuellement le meilleur moyen d’utiliser des constantes de matrice consiste à leur attribuer un nom. Constantes nommées peuvent être beaucoup plus faciles à utiliser, et ils peuvent masquer une partie de la complexité de vos formules matricielles du début des utilisateurs. Pour nommer une constante de matrice et l’utiliser dans une formule, procédez comme suit :

  1. Sous l’onglet Formules, dans le groupe Noms définis, cliquez sur Définir un nom.
    La boîte de dialogue Définir un nom apparaît.

  2. Dans la zone Nom, tapez Trimestre1.

  3. Dans la zone Fait référence à, entrez la constante suivante (n’oubliez pas de taper les accolades) :

    ={"Janvier","Février","Mars"}

    Le contenu de la boîte de dialogue doit ressembler à ceci :

    Boîte de dialogue Modifier le nom, avec formule

  4. Cliquez sur OK.

  5. Dans la feuille de calcul, sélectionnez une ligne de trois cellules vides.

  6. Tapez la formule suivante, puis appuyez sur CTRL + MAJ + ENTRÉE.

    =Trimestre1

Vous obtenez le résultat suivant.

Tableau nommé entré en tant que formule

Lorsque vous utilisez une constante nommée en tant que formule matricielle, pensez à entrer le signe égal. Si vous n’est pas, Excel interprète le tableau sous forme de chaîne de texte. Enfin, n’oubliez pas que vous pouvez utiliser les combinaisons de texte et des nombres.

Résolution des problèmes liés aux constantes de matrice

Recherchez les problèmes suivants lorsque vos constantes de matrice ne fonctionnent pas.

  • Certains éléments ne peuvent pas être séparés par le caractère correct. Si vous ne spécifiez pas une virgule ou un point-virgule, ou si vous insérez une au mauvais endroit, la constante de matrice ne peut-être pas être créée correctement ou affiche un message d’avertissement.

  • Vous avez peut-être sélectionné une plage de cellules qui ne correspondent pas au nombre d’éléments dans votre constante. Par exemple, si vous sélectionnez une colonne de six cellules pour une utilisation avec une constante de cinq cellules, la valeur d’erreur # n/a apparaît dans la cellule vide. En revanche, si vous sélectionnez trop peu de cellules, Excel ignore les valeurs qui n’ont pas une cellule correspondante.

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 des exemples utilisent la fonction TRANSPOSE pour convertir des lignes en colonnes et inversement.

Multiplier chaque élément dans un tableau

  1. Sélectionnez un bloc de cellules vides de quatre colonnes de largeur sur trois lignes de hauteur.

  2. Tapez la formule suivante, puis appuyez sur CTRL + MAJ + ENTRÉE.

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Mettre au carré les éléments dans un tableau

  • Sélectionnez un bloc de cellules vides de quatre colonnes de largeur sur trois lignes de hauteur.

  • Tapez la formule matricielle suivante, puis appuyez sur CTRL + MAJ + ENTRÉE.

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Vous pouvez aussi entrer cette formule de tableau, qui utilise l’opérateur circonflexe (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Transposer une ligne à une dimension

  1. Sélectionnez une colonne de cinq cellules vides.

  2. Tapez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    =TRANSPOSE({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

  1. Sélectionnez une ligne de cinq cellules vides.

  2. Entrez la formule suivante, puis appuyez sur CTRL + MAJ + ENTRÉE :

    =TRANSPOSE({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

  1. Sélectionnez un bloc de cellules de trois colonnes de large sur quatre lignes de hauteur.

  2. Entrez la constante suivante, puis appuyez sur CTRL + MAJ + ENTRÉE.

    =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

La fonction TRANSPOSE convertit chaque colonne en une série de colonnes.

Haut de la page

Application de formules matricielles de base

Cette section présente des exemples de formules matricielles de base.

Prise en main

Utiliser les données dans cette section pour créer deux exemples de feuilles.

  1. Ouvrir un classeur existant ou créer un nouveau classeur et vérifiez qu’il contient deux feuilles de calcul vides.

  2. Copiez les données dans le tableau suivant et collez-les dans la feuille de calcul commençant à la cellule A1.

400

rapide

1

2

3

4

1200

kiwis

5

6

7

8

3200

au zéphyr

9

10

11

12

475

les jattes

13

14

15

16

500

utilisateurs avec pouvoir

2000

600

1700

800

2700

Votre feuille de calcul terminé doit ressembler à ceci.

Exemple de données complet

  1. Nommez la première feuille de données, puis nommez un deuxième matricesde feuille de calcul vide.

Créer des tableaux et des constantes à partir de valeurs existantes

L’exemple suivant explique comment utiliser des formules de tableau pour créer des liens entre des plages de cellules situées dans des feuilles de calcul différentes. Cela montre également comment créer une constante de matrice à partir du même ensemble de valeurs.

Créer un tableau à partir de valeurs existantes

  1. Dans votre exemple de classeur, sélectionnez la feuille de calcul tableaux.

  2. Sélectionnez la plage de cellules C1 à E3.

  3. Entrez la formule suivante dans la barre de formule et appuyez sur CTRL + MAJ + ENTRÉE :

    = Données ! E1:G3

Vous obtenez le résultat suivant.

Résultats dans la feuille de calcul Tableaux

Les liens vers les valeurs stockées dans les cellules E1 à G3 sur la feuille de calcul données formules. L’alternative à cette formule matricielle à plusieurs cellules consiste à placer une formule unique dans chaque cellule de la feuille de calcul tableaux, comme suit.

= Données ! E1

= Données ! F1

= Données ! G1

= Données ! E2

= Données ! F2

= Données ! G2

= Données ! E3

= Données ! F3

= Données ! G3

Si vous changez certaines des valeurs de la feuille de données, ces modifications apparaissent sur la feuille de calcul tableaux. N’oubliez pas que pour modifier toutes les valeurs de la feuille de données, vous devez respecter les règles de modification des formules matricielles. Pour plus d’informations sur ces règles, voir la section en savoir plus sur les formules matricielles.

Créer une constante de matrice à partir de valeurs existantes

  1. Dans la feuille de calcul tableaux, sélectionnez les cellules C1 à E3.

  2. Appuyez sur F2 pour passer en mode d’édition.

  3. Appuyez sur F9 pour convertir les références de cellule aux valeurs. Excel convertit les valeurs dans une constante matricielle.

  4. Appuyez sur CTRL + MAJ + ENTRÉE pour entrer la constante de matrice en tant que formule matricielle.

Excel remplace les données de =! E1:G3 tableau formule avec la constante matricielle suivante :

= { 1,2,3 ; 5,6,7 ; 9,10,11}

Le lien a été interrompue entre les feuilles de calcul de données et les matrices, et la formule matricielle a été remplacée par une constante matricielle.

Compter les caractères dans une plage de cellules

L’exemple suivant vous montre comment compter le nombre de caractères, espaces inclus, dans une plage de cellules.

  • Dans la feuille de données, entrez la formule suivante dans la cellule C7 et appuyez sur CTRL + MAJ + ENTRÉE :

    = SOMME (LEN(C1:C5))

La valeur 47 apparaît dans la cellule C7.

Dans ce cas, la fonction LEN renvoie la longueur de chaque chaîne de caractères dans chacune des cellules dans la plage. La fonction somme additionne les valeurs puis et affiche le résultat dans la cellule qui contient la formule, C7.

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.

  1. Dans la feuille de données, sélectionnez les cellules A12 à A14.

    Cet ensemble de cellules contiendra les résultats renvoyés par la formule matricielle.

  2. Dans la barre de formule, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    = SMALL(A1:A10,{1;2;3})

Les valeurs 400, 475et 500 apparaissent dans les cellules A12 à A14, respectivement.

Cette formule utilise une constante de matrice pour évaluer la fonction PETITE.VALEUR trois fois et renvoyer le plus petit membre (1), le deuxième plus petit membre (2) et le troisième plus petit membre (3) contenu dans les cellules A1:A10 du tableau. Pour rechercher d’autres valeurs, vous devez ajouter d’autres arguments à la constante et un nombre de cellules équivalent à la plage A12:A14. Vous pouvez également utiliser des fonctions supplémentaires avec cette formule, telles que SOMME ou MOYENNE. Par exemple :

= Somme ( petite (a1 : a10, {1 ; 2 ; 3}))

= Moyenne ( petite (a1 : a10, {1 ; 2 ; 3}))

Chercher les n valeurs les plus grandes dans une plage

Pour trouver les plus grandes valeurs d’une plage, vous pouvez remplacer la fonction PETITE.VALEUR par la fonction GRANDE.VALEUR. Par ailleurs, l’exemple suivant utilise les fonctions LIGNE et INDIRECT.

  1. Dans la feuille de données, sélectionnez les cellules A12 à A14.

  2. Appuyez sur SUPPR pour supprimer la formule existante tout en conservant les cellules sélectionnées.

  3. Dans la barre de formule, entrez la formule suivante et appuyez sur CTRL + MAJ + ENTRÉE :

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

Les valeurs 3200, 2700et 2000 apparaissent dans les cellules A12 à A14, respectivement.

À ce stade, il peut-être vous aider à savoir un peu sur la ligne et de fonctions INDIRECT . Vous pouvez utiliser la fonction ligne pour créer un tableau de nombres entiers consécutifs. Par exemple, sélectionnez une colonne vide de 10 cellules dans votre classeur d’exercice, entrez la formule matricielle dans les cellules a1 : a10 et puis appuyez sur CTRL + MAJ + ENTRÉE :

=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 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 (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.

Examinons la formule que vous avez utilisé auparavant — =grande (A1:A10,ROW(INDIRECT("1:3"))) , en commençant par les parenthèses internes et vers l’extérieur : la fonction INDIRECT renvoie une série de valeurs de texte, dans ce cas, les valeurs 1 à 3. La fonction ligne génère à son tour un tableau en colonnes de trois cellules. La fonction grande.valeur utilise les valeurs de la plage de cellules a1 : a10, et il est évaluée trois fois, une fois pour chaque référence renvoyées par la fonction ligne . Les valeurs 3200, 2700 et 2000 sont retournés dans le tableau en colonnes de trois cellules. Si vous souhaitez rechercher des valeurs de plus, vous ajoutez une plage de cellules supérieure à la fonction INDIRECT .

Enfin, vous pouvez utiliser cette formule avec d’autres fonctions, telles que SOMME et MOYENNE.

Chercher la chaîne de texte la plus longue dans une plage de cellules

Cet exemple recherche la plus longue chaîne de texte dans une plage de cellules. Cette formule fonctionne uniquement lorsqu’une plage de données contient une seule colonne de cellules.

  • Sur la feuille de données, désactivez la formule existante à partir de la cellule C7, entrez la formule suivante dans la cellule et appuyez sur CTRL + MAJ + ENTRÉE :

    =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

La valeur , au zéphyr, apparaît dans la cellule C7.

Examinons la formule, en commençant par les éléments internes et vers l’extérieur. La fonction LEN renvoie la longueur de chacun des éléments dans la plage de cellules C1 : C5. La fonction MAX calcule la plus grande valeur parmi les éléments, qui correspond à la chaîne de texte la plus longue, qui se trouve dans la cellule C3.

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 ( LEN(C1:C5))

et cette chaîne réside dans le tableau suivant :

LEN ( C1 : C5)

L’argument du type de correspondance est 0. Le type de correspondance peut être composé d’une valeur 1, 0 ou -1. Si vous spécifiez 1, EQUIV renvoie la plus grande valeur qui est inférieure ou égale à la valeur de recherche. Si vous spécifiez 0, la fonction EQUIV renvoie la première valeur exactement égale à la valeur de recherche. Si vous spécifiez -1, la fonction EQUIV recherche la plus petite valeur qui est supérieure ou égale à la valeur de recherche. 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 nombre de lignes et de colonnes dans ce tableau. La plage de cellules C1 : C5 fournit la matrice, la fonction EQUIV fournit une adresse de cellule et l’argument final (1) indique que la valeur provient de la première colonne du tableau.

Haut de la page

Application de formules matricielles avancées

Cette section présente des exemples de formules matricielles avancées.

Additionner une plage qui contient des valeurs d’erreur

La fonction SOMME d’Excel ne fonctionne pas si vous tentez d’additionner une plage contenant une valeur d’erreur, telle que #N/A. Cet exemple vous montre comment additionner les valeurs d’une plage, nommée Données, contenant des erreurs.

= Somme ( IF(ISERROR(Data),"",Data))

La formule crée un nouveau tableau qui contient les valeurs d’origine moins toutes les valeurs d’erreur. En commençant par les fonctions internes et l’utilisation vers l’extérieur, fonction ISERROR fonction recherche dans la plage de cellules (données) pour les erreurs. La fonction Si renvoie une valeur spécifique si une condition que vous spécifiez est vrai et une autre valeur si elle est faux. Dans ce cas, elle renvoie des chaînes vides (» «) pour toutes les erreurs de valeurs, car ils ont la valeur TRUE, et elle retourne les autres valeurs de la plage (données), car ils ont pour résultat faux, ce qui signifie qu’elles ne contiennent des valeurs d’erreur. La fonction somme puis calcule le total de la matrice filtrée.

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’erreurs dans une plage nommée Données au lieu de les filtrer :

= Somme ( IF(ISERROR(Data),1,0))

Cette formule crée un tableau qui contient la valeur 1 pour les cellules qui contiennent des erreurs et la valeur 0 pour les cellules qui ne contiennent des erreurs. Vous pouvez simplifier la formule et obtenir le même résultat en supprimant le troisième argument de la fonction Si , comme suit :

= Somme ( IF(ISERROR(Data),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 ( IF(ISERROR(Data)*1))

Cette version fonctionne car VRAI*1=1 et FAUX*1=0.

Additionner des valeurs basées sur des conditions

Vous devrez peut-être additionner des valeurs en fonction de conditions. Par exemple, cette formule matricielle additionne uniquement les entiers positifs dans une plage nommée Sales :

= Somme ( IF (ventes > 0, ventes))

La fonction SI crée un tableau de valeurs positives et de valeurs 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 des valeurs supérieures à 0 et inférieures ou égales à 5 :

= Somme ( (ventes > 0) * (ventes < =5)*(Sales))

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 inférieures à 5 et supérieures à 15 :

= Somme ( IF ((Sales<5) +(Sales>15), ventes))

La fonction SI recherche toutes les valeurs inférieures à 5 et supérieures à 15, puis passe ces valeurs à la fonction SOMME.

Important : Vous ne pouvez pas utiliser les fonctions et et ou dans des formules matricielles directement, car ces fonctions renvoient un seul résultat vrai ou faux, ainsi que les fonctions de tableau nécessitent des tableaux de résultats. Vous pouvez contourner le problème à l’aide de la logique affiché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, des valeurs qui répondent à l’opérateur OR ou condition AND.

Calculer une moyenne excluant des zéros

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 ( IF (ventes différente de 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 matricielle compare les valeurs dans deux plages de cellules nommées mesdonnées et Vosdonnées et renvoie le nombre de différences entre les deux. Si le contenu des deux plages est identique, la formule retourne 0. Pour utiliser cette formule, les plages de cellules doivent être la même taille et de la même dimension :

= Somme ( IF ( 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 * ( mesdonnées <> 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.

Chercher l’emplacement de la valeur maximale dans une plage

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 ( IF(Data=MAX(Data),ROW(Data),""))

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(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Haut de la Page

Remarque : Avertissement traduction automatique : cet article a été traduit par un ordinateur, sans intervention humaine. Microsoft propose cette traduction automatique pour offrir aux personnes ne maîtrisant pas l’anglais l’accès au contenu relatif aux produits, services et technologies Microsoft. Comme cet article a été traduit automatiquement, il risque de contenir des erreurs de grammaire, de syntaxe ou de terminologie.

Ces informations vous ont-elles été utiles ?

Très bien ! Vous avez d’autres commentaires ?

Comment pouvons-nous l’améliorer ?

Nous vous remercions pour vos commentaires.

×