Instructions et exemples de formules matricielles

Instructions et exemples de formules matricielles

Pour devenir un utilisateur chevronné d’Excel, vous devez apprendre à utiliser les formules de tableau, lesquelles peuvent effectuer des calculs que vous ne pouvez pas effectuer à l’aide de formules autres que des formules de tableau. L’article suivant s’inspire d’une série de rubriques pour les utilisateurs chevronnés d’Excel rédigées par Colin Wilcox et adaptées des chapitres 14 et 15 de Formules Excel 2002, un livre écrit par John Walkenbach, MVP Excel.

En savoir plus sur les formules matricielles

Les formules de tableau portent souvent le nom de formules Ctrl+Maj+Entrée) car au lieu de simplement appuyer sur Entrée, vous devez appuyer sur Ctrl+Maj+Entrée pour valider la formule.

Pourquoi utiliser des formules de tableau ?

Si vous avez de l’expérience en matière d’utilisation de formules dans Excel, vous savez qu’il est possible de réaliser des opérations relativement complexes. Par exemple, vous pouvez calculer le coût total d’un emprunt sur un nombre donné d’années. Vous pouvez utiliser des formules de tableau pour effectuer des tâches complexes telles 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.

Présentation des tableaux et des formules de tableau

Une formule de tableau est une formule capable d’effectuer plusieurs calculs sur un ou plusieurs éléments d’un tableau. Un tableau est une ligne de valeurs, une colonne de valeurs ou une combinaison de lignes et de colonnes de valeurs. Les formules de tableau peuvent renvoyer un ou plusieurs résultats. Par exemple, vous pouvez placer une formule de tableau dans une plage de cellules et utiliser cette formule pour calculer une colonne ou une ligne de sous-totaux. Vous pouvez également placer une formule de tableau dans une seule cellule, puis calculer un seul montant. Une formule de tableau qui réside dans plusieurs cellules est qualifiée de formule à plusieurs cellules ; une formule de tableau qui réside dans une seule cellule est appelée formule à cellule unique.

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

Essayez !

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

Voici un classeur incorporé dans le navigateur. Bien qu’il contienne des exemples de données, vous devez savoir qu’il est impossible de créer ou de modifier des formules de tableau dans un classeur incorporé ; il vous faut le programme Excel. Vous pouvez visualiser les réponses dans le classeur incorporé, ainsi que du texte qui explique comment fonctionne les formules de tableau, mais pour réellement profiter des formules de tableau, vous devrez utiliser le classeur dans Excel.

Créer une formule de tableau à plusieurs cellules

  1. Copiez l’intégralité du tableau ci-dessous et collez-le dans la cellule A1 d’une feuille de calcul vierge dans Excel.

    Vendeur

    Type de
    voiture

    Nombre
    vendu

    Prix
    unitaire

    Total des
    ventes

    Jacques

    Berline

    5

    33 000

    Coupé

    4

    37 000

    Osorio

    Berline

    6

    24 000

    Coupé

    8

    21 000

    Thierry

    Berline

    3

    29 000

    Coupé

    1

    31 000

    Antoine

    Berline

    9

    24 000

    Coupé

    5

    37 000

    Durrer

    Berline

    6

    33 000

    Coupé

    8

    31 000

    Formule (total général)

    Total général

    '=SOMME(C2:C11*D2:D11)

    =SOMME(C2:C11*D2:D11)

  2. Pour afficher le total des ventes de coupés et de berlines pour chaque vendeur, sélectionnez E2:E11, entrez la formule =C2:C11*D2:D11, puis appuyez sur Ctrl+Maj+Entrée.

  3. Pour afficher le total général de toutes les ventes, sélectionnez la cellule F11, entrez la formule =SOMME(C2:C11*D2:D11), puis appuyez sur Ctrl+Maj+Entrée.

Vous pouvez télécharger ce classeur en cliquant sur le bouton Excel vert situé dans la barre noire au bas du classeur. Ensuite, vous pouvez ouvrir le fichier dans Excel, sélectionner les cellules contenant les formules de tableau et appuyer sur Ctrl+Maj+Entrée pour valider la formule.

Si vous travaillez en même temps dans Excel, vérifiez que Feuil1 est active, puis sélectionnez les cellules E2:E11. Appuyez sur F2 et tapez la formule =C2:C11*D2:D11 dans la cellule active, E2. Si vous appuyez sur Entrée, vous constaterez que la formule est entrée uniquement dans la cellule E2, qui affiche 165000. Au lieu de cela, après avoir tapé la formule, appuyez sur Ctrl+Maj+Entrée plutôt que sur Entrée. Vous verrez maintenant les résultats dans les cellules E2:E11. Notez que dans la barre de formule, la formule affichée est {=C2:C11*D2:D11}. Cela indique qu’il s’agit d’une formule de tableau (comme illustré dans le tableau suivant).

Lorsque vous appuyez sur Ctrl+Maj+Entrée, Excel met la formule entre accolades ({ }) et insère 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 correspond au montant total des ventes pour chaque type de voiture par vendeur. Si vous sélectionnez E2, puis E3, E4, et ainsi de suite, vous constaterez que la même formule est affichée : {=C2:C11*D2:D11}.

Les totaux dans la colonne E sont calculés par une formule de tableau

Créer une formule de tableau à cellule unique

Dans la cellule F10 du classeur, tapez la formule suivante, puis appuyez sur Ctrl+Maj+Entrée :

=SOMME(C2:C11*D2:D11)

Dans ce cas, Excel multiplie les valeurs dans le tableau (la plage de cellules C2 à D11), 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.

Par ailleurs, notez que la formule à cellule unique dans la cellule G11 est entièrement indépendante de la formule à plusieurs cellules (la formule des cellules E2 à E11). Cela révèle un autre avantage des formules de tableau : leur souplesse d’utilisation. Vous pourriez modifier les formules de la colonne E ou supprimer entièrement cette colonne, sans affecter la formule de la cellule G11.

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é    Vous ne pouvez pas remplacer un composant d’une formule de tableau à plusieurs cellules. Par exemple, cliquez sur la cellule E3, puis appuyez sur Suppr. Vous devez soit sélectionner toute la plage de cellules (E2 à E11) et modifier la formule pour tout le tableau, soit laisser le tableau en l’état. Comme mesure de sécurité supplémentaire, vous devez appuyer sur Ctrl+Maj+Entrée pour confirmer le changement de la formule.

  • 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, le classeur utilise une formule de tableau pour calculer les résultats de la colonne E. Avec des formules standard (telles que =C2*D2, C3*D3, C4*D4…), il vous en aurait fallu 11 différentes pour calculer les mêmes résultats.

Syntaxe des formules de tableau

En général, les formules de tableau utilisent la syntaxe des formules standard. Elles commencent toutes par un signe égal et vous pouvez y insérer n’importe quelle fonction intégrée d’Excel. La principale différence réside dans le fait qu’avec une formule de tableau, vous devez appuyer sur Ctrl+Maj+Entrée pour entrer votre formule. Excel met ensuite votre formule de tableau entre accolades — si vous tapez les accolades manuellement, votre formule sera convertie en chaîne de texte et ne fonctionnera pas.

Les fonctions de tableau constituent un moyen très efficace de générer des formules complexes. La formule de tableau =SOMME(C2:C11*D2:D11) équivaut à celle-ci : =SOMME(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Saisie et modification des formules de tableau

Important    Vous devez appuyer sur Ctrl+Maj+Entrée chaque fois que vous entrez ou modifiez une formule de tableau. Cette règle s’applique aussi bien aux formules à cellule unique qu’à plusieurs cellules.

Lorsque que vous utilisez des formules à plusieurs cellules, vous devez également respecter les règles suivantes :

  • Sélectionnez la plage de cellules qui contiendra vos résultats avant d’entrer la formule. Vous l’avez fait lorsque vous avez créé la formule de tableau à plusieurs cellules, en sélectionnant les cellules E2 à E11.

  • Vous ne pouvez pas modifier le contenu d’une cellule individuelle dans une formule de tableau. Pour le vérifier, sélectionnez la cellule E3 dans le classeur et appuyez sur Suppr. Excel affiche un message indiquant qu’il est impossible de modifier une partie d’un tableau.

  • Vous pouvez déplacer ou supprimer une formule de tableau entière, mais vous ne pouvez pas en déplacer ou en supprimer une partie. En d’autres termes, pour réduire une formule de tableau, vous devez d’abord supprimer la formule existante et recommencer.

  • Pour supprimer une formule de tableau, sélectionnez l’ensemble de la formule (par exemple, =C2:C11*D2:D11), appuyez sur Suppr, puis appuyez sur Ctrl+Maj+Entrée.

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

Développement d’une formule de tableau

Il peut parfois s’avérer nécessaire de développer une formule de tableau. Le processus n’est pas compliqué, mais vous devez vous souvenir des règles énoncées dans la section précédente.

Dans cette feuille de calcul, nous avons ajouté d’autres lignes de ventes, dans les lignes 12 à 17. Ici, nous souhaitons mettre à jour les formules de tableau de sorte qu’elles incluent ces lignes supplémentaires.

N’oubliez pas d’effectuer cette opération dans le programme de bureau Excel (après avoir téléchargé le classeur sur votre ordinateur).

Développer une formule de tableau

  1. Copiez ce tableau entier dans la cellule A1 d’une feuille de calcul Excel.

    Vendeur

    Type de
    voiture

    Nombre
    vendu

    Prix
    unitaire

    Total des
    ventes

    Jacques

    Berline

    5

    33 000

    165 000

    Coupé

    4

    37 000

    148 000

    Osorio

    Berline

    6

    24 000

    144 000

    Coupé

    8

    21 000

    168 000

    Thierry

    Berline

    3

    29 000

    87 000

    Coupé

    1

    31 000

    31 000

    Antoine

    Berline

    9

    24 000

    216 000

    Coupé

    5

    37 000

    185 000

    Durrer

    Berline

    6

    33 000

    198 000

    Coupé

    8

    31 000

    248 000

    Mangel

    Berline

    2

    27 000

    Coupé

    3

    30 000

    Garcia

    Berline

    4

    22 000

    Coupé

    1

    41 000

    Gregor

    Berline

    5

    32 000

    Coupé

    3

    36 000

    Total général

  2. Sélectionnez la cellule E18, entrez la formule Total général =SOMME(C2:C17*D2:D17) dans la cellule A20, puis appuyez sur Ctrl+Maj+Entrée.
    Vous devez obtenir 2 131 000 comme réponse.

  3. Sélectionnez la plage de cellules qui contient la formule de tableau 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.

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

  5. Dans la barre de formule, remplacez C11 par C17 et D11 par D17, puis appuyez sur Ctrl+Maj+Entrée.
    Excel met à jour la formule dans les cellules E2 à E11 et place une instance de la formule dans les nouvelles cellules, E12 à E17.

  6. Tapez la formule de tableau = SOMME(C2:C17*D2*D17) dans la cellule F17 de sorte qu’elle fasse référence aux lignes 2 à 17 et appuyez sur Ctrl+Maj+Entrée pour entrer la formule de tableau.
    Le nouveau total doit être 2 131 000.

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

Les formules de tableau sont très pratiques, mais elles peuvent malheureusement présenter quelques inconvénients :

  • On oublie parfois d’appuyer sur Ctrl+Maj+Entrée. Cela peut arriver même aux utilisateurs d’Excel les plus expérimentés. Souvenez-vous d’utiliser cette combinaison de touches chaque fois que vous entrez ou modifiez une formule de tableau.

  • D’autres utilisateurs de votre classeur peuvent ne pas comprendre vos formules. Dans la pratique, les formules de tableau ne sont généralement pas expliquées. Aussi, si d’autres personnes sont appelées à modifier vos classeurs, vous devez soit éviter les formules de tableau, soit veiller à ce que ces personnes connaissent les formules de tableau et sachent comment les modifier en cas de besoin.

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

Haut de la page

En savoir plus sur les constantes de matrice

Les constantes de matrice sont un composant des formules de tableau. 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}

Vous savez maintenant que vous devez appuyer sur Ctrl+Maj+Entrée lorsque vous créez des formules de tableau. Étant donné que les constantes de matrice sont un composant des tableaux, vous devez mettre les constantes entre accolades en les tapant. Vous utilisez ensuite la combinaison de touches Ctrl+Maj+Entrée pour entrer toute la formule.

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, vous délimitez les éléments dans chaque ligne à l’aide de virgules et délimitez chaque ligne à l’aide de points-virgules.

Voici un tableau sur une seule ligne : {1,2,3,4}. Voici un tableau dans une seule colonne : {1;2;3;4}. Et voici un tableau de deux lignes et quatre colonnes : {1,2,3,4;5,6,7,8}. Dans le tableau à deux lignes, la première ligne est 1, 2, 3 et 4, et la seconde ligne est 5, 6, 7 et 8. Un seul point-virgule sépare les deux lignes, entre 4 et 5.

À l’instar des formules de tableau, vous pouvez utiliser des constantes de matrice avec la plupart des fonctions intégrées fournies par Excel. Les sections suivantes expliquent comment créer chaque type de constante et comment utiliser ces constantes avec des fonctions dans Excel.

Haut de la page

Créer des constantes à une ou deux dimensions

Les procédures suivantes vous permettront 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, puis appuyez sur Ctrl+Maj+Entrée :

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

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

    Vous obtenez le résultat suivant.

    Constante de matrice horizontale dans une formule

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, puis appuyez sur Ctrl+Maj+Entrée :

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

    Vous obtenez le résultat suivant.

    Constante de matrice verticale dans une formule de tableau

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, puis 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 de tableau

Utiliser des constantes dans les formules

Voici un exemple simple qui fait appel à des constates :

  1. Dans l’exemple de classeur, créez une feuille de calcul.

  2. Dans la cellule A1, tapez 3, puis tapez 4 dans B1, 5 dans C1, 6 dans D1 et 7 dans E1.

  3. Dans la cellule A3, tapez la formule suivante, puis appuyez sur Ctrl+Maj+Entrée :

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

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

    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 de tableau avec constante de matrice

1. Fonction

2. Tableau stocké

3. Opérateur

4. Constante de matrice

Le dernier élément qui se trouve à l’intérieur des parenthèses est la constante de matrice : {1,2,3,4,5}. Souvenez-vous qu’Excel n’entoure pas les constantes de matrice d’accolades ; c’est vous qui devez les taper. Souvenez-vous également que vous devez appuyer sur Ctrl+Maj+Entrée pour entrer la formule après avoir ajouté une constante dans une formule de tableau.

É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*3,D1*4,E1*5)

Pour finir, 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 essayer cela, copiez la fonction, sélectionnez une cellule vide dans votre classeur, collez la formule dans la barre de formule, puis appuyez sur Ctrl+Maj+Entrée. Vous obtiendrez le même résultat qu’à l’exercice précédent qui utilisait la formule de tableau :

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

Éléments que vous pouvez utiliser dans des constantes

Les constantes de matrice peuvent contenir des chiffres, du texte, des valeurs logiques (telles que VRAI et FAUX), et des valeurs d’erreur (telles que #N/A). Vous pouvez utiliser des chiffres aux formats Entier, Décimal et Scientifique. Si vous y insérez du texte, vous devez le mettre entre guillemets (").

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

Une des meilleures manières d’utiliser des constantes de matrice consiste peut-être à 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 :

  1. Dans 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, puis sélectionnez une ligne de trois cellules vides.

  5. 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 de tableau, n’oubliez pas le signe égal. 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, rappelez-vous que vous pouvez utiliser des combinaisons de texte et de chiffres.

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 sont peut-être pas séparés à l’aide du caractère approprié. Si vous omettez une virgule ou un point-virgule, ou en mettez un au mauvais endroit, la constante de matrice risque de ne pas être créée correctement et d’entraîner l’affichage d’un message d’avertissement.

  • Vous pouvez avoir sélectionné une plage de cellules qui ne correspond pas au nombre d’éléments de votre constante. Par exemple, si vous sélectionnez une colonne de six cellules à utiliser dans une constante de cinq cellules, la valeur d’erreur #N/A apparaît dans la cellule vide. À l’inverse, si vous sélectionnez un nombre insuffisant de cellules, Excel omet les valeurs qui ne possèdent pas de 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. Créez une feuille de calcul, puis 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

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

  2. Tapez la formule de tableau 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, puis 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. Tapez 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

Appliquer des formules de tableau de base

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

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 une feuille de calcul dans Excel, sélectionnez les cellules C8:E10, puis entrez cette formule :

    ={10,20,30;40,50,60;70,80,90}

    Veillez à taper { (accolade ouvrante) avant de taper 10, et } (accolade fermante) après avoir tapé 90, car vous créez un tableau de nombres.

  2. Appuyez sur Ctrl+Maj+ Entrée pour insérer ce tableau de nombres dans la plage de cellules C8:E10 à l’aide d’une formule de tableau.
    Dans votre feuille de calcul, les cellules C8 à E10 doivent ressembler à ceci :

    10

    20

    30

    40

    50

    60

    70

    80

    90

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

  4. Entrez la formule suivante dans la barre de formule, puis appuyez sur Ctrl+Maj+Entrée :

    =C8:E10

    Un tableau 3x3 de cellules apparaît dans les cellules C1 à E3 avec les mêmes valeurs que celles affichées dans C8 à E10.

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

  1. Sélectionnez les cellules C1:C3, appuyez sur F2 pour passer en mode édition.
    La formule de tableau doit toujours être = C8:E10.

  2. Appuyez sur F9 pour convertir les références de cellules en valeurs. Excel convertit les valeurs en une constante de matrice. La formule doit maintenant être {10,20,30;40,50,60;70,80,90}, comme C8:E10.

  3. Appuyez sur Ctrl+Maj+Entrée pour entrer la constante de matrice en tant que formule de tableau.

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.

  1. Copiez l’intégralité de ce tableau et collez-le dans une feuille de calcul dans la cellule A1.

  2. Sélectionnez la cellule A9, puis appuyez sur Ctrl+Maj+Entrée pour voir le nombre total de caractères dans les cellules A2:A6 (66).

  3. Sélectionnez la cellule A12, puis appuyez sur Ctrl+Maj+Entrée pour voir le contenu de la cellule la plus longue parmi les cellules A2:A6 (A3).

Données

Voici des

cellules qui,

ensemble,

forment

une phrase.

Nombre total de caractères dans A2:A6

=SOMME(NBCAR(A2:A6))

Contenu de la cellule la plus longue (A3)

=INDEX(A2:A6;EQUIV(MAX(NBCAR(A2:A6));NBCAR(A2:A6);0);1)

La formule suivante utilisée dans la cellule A9 compte le nombre total de caractères dans les cellules A2 à A6.

=SOMME(NBCAR(A2:A6))

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 ensuite ces valeurs et affiche le résultat (66) dans la cellule qui contient la formule, A9.

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. Sélectionnez les cellules A16 à A18.
    Cet ensemble de cellules contiendra les résultats retournés par la formule de tableau.

  2. Dans la barre de formule, entrez la formule suivante, puis appuyez sur Ctrl+Maj+Entrée :

    =PETITE.VALEUR(A5:A14,{1;2;3})

Les valeurs 400, 475 et 500 apparaissent dans les cellules A16 à A18, 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.VALEUR(A 5 :A1 4 ,{1;2;3}))

=MOYENNE(PETITE.VALEUR(A 5 :A1 4 ,{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. Sélectionnez les cellules A1 à A3.

  2. Dans la barre de formule, entrez la formule suivante, puis appuyez sur Ctrl+Maj+Entrée :

    =GRANDE.VALEUR(A5:A14,LIGNE(INDIRECT("1:3")))

Les valeurs 3200, 2700 et 2000 apparaissent dans les cellules A1 à A3, respectivement.

À 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 colonne vide de 10 cellules dans votre classeur de travail, entrez cette formule de tableau dans les cellules A5:A14, 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ée précédemment  — =GRANDE.VALEUR(A5:A14,LIGNE(INDIRECT("1:3"))) — en partant de la parenthèse intérieure et en procédant vers l’extérieur : la fonction INDIRECT renvoie un ensemble de valeurs de texte, dans notre exemple les valeurs 1 à 3. La fonction LIGNE génère ensuite un tableau à colonnes de trois cellules. La fonction GRANDE.VALEUR utilise les valeurs de la plage de cellules A5:A14 et est évaluée trois fois, une fois pour chaque référence retournée par la fonction LIGNE. Les valeurs 3200, 2700 et 2000 sont retournées au tableau à colonnes de trois cellules. Pour trouver d’autres valeurs, vous devez ajouter une plage de cellules plus grande à 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

Cette formule ne fonctionne que lorsqu’une plage de données contient une seule colonne de cellules. Sur Feuil3, entrez la formule suivante dans la cellule A16 et appuyez sur Ctrl+Maj+Entrée.

=INDEX(A6:A9,EQUIV(MAX(NBCAR(A6:A9)),NBCAR(A6:A9),0),1)

Le texte « bunch of cells that » apparaît dans la cellule A16.

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 chacun des éléments de la plage de cellules A6:A9. La fonction MAX calcule la valeur la plus grande parmi ces éléments ; valeur qui correspond à la chaîne de texte la plus longue, qui se trouve dans la cellule A7.

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(NBCAR( A6 : A9 ))

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

NBCAR( A6:A9 )

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.

Pour finir, la fonction INDEX prend les arguments suivants : un tableau, ainsi qu’un numéro de ligne et de colonne au sein de ce tableau. La plage de cellules A6:A9 fournit le tableau, la fonction EQUIV fournit l’adresse de la cellule, tandis que le dernier argument (1) indique que la valeur provient de la première colonne du tableau.

Haut de la page

Appliquer des formules de tableau avancées

Cette section présente des exemples de formules de tableau 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(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’erreurs 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 de tableau additionne seulement les entiers positifs d’une plage nommée Ventes :

=SOMME(SI(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)*(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 inférieures à 5 et supérieures à 15 :

=SOMME(SI((Ventes<5)+(Ventes>15),Ventes))

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

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, sur des valeurs remplissant la condition OU ou ET.

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(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 être de la même taille et de la même dimension (par exemple, si MesDonnées est une plage de 3 lignes par 5 colonnes, VosDonnées doit également être une plage de 3 lignes par 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*( 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(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))

Haut de la page

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Voir aussi

Vue d’ensemble des formules

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×