Instructions et exemples de formules de tableaux

Pour devenir un utilisateur chevronné d'Excel, vous devez maîtriser l'utilisation de formules de tableaux, qui vous permettent d'effectuer des calculs qui vous seraient inaccessibles autrement. Cet article, adapté des chapitres 14 et 15 du livre Excel 2002 Formulas du MVP Excel, John Walkenbach, repose sur une série de chroniques Utilisateurs chevronnés rédigées par Colin Wilcox. Pour en savoir plus sur les autres ouvrages de John Walkenbach, consultez sa page de livres.

Dans cet article


En savoir plus sur les formules de tableaux

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

Pourquoi utiliser des formules de tableaux ?

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 quelques opérations relativement complexes. Par exemple, vous pouvez calculer le coût total d'un emprunt sur un nombre donné d'années. Toutefois, pour maîtriser vraiment les formules d'Excel, vous devez apprendre à utiliser les formules de tableaux. Vous pouvez utiliser des formules de tableaux 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 nème valeur dans une plage de valeurs.

 Remarque   Les formules de tableaux sont parfois appelées « formules CME » car il faut appuyer sur CTRL+MAJ+ENTRÉE pour les insérer dans vos classeurs.

Présentation rapide des tableaux et des formules de tableaux

Si vous avez une petite expérience de programmation, vous avez probablement rencontré le terme de tableau. Dans notre contexte, un tableau correspond à une collection d'éléments. Dans Excel, ces éléments peuvent résider dans une seule ligne (appelée tableau horizontal à une dimension), une seule colonne (tableau vertical à une dimension), ou plusieurs lignes et colonnes (tableau à deux dimensions). Vous ne pouvez pas créer de tableaux ou de formules de tableaux à trois dimensions dans Excel.

Une formule de tableau est une formule capable d'effectuer plusieurs calculs sur un ou plusieurs éléments d'un tableau. Les formules de tableaux peuvent retourner un ou plusieurs résultats. Par exemple, vous pouvez placer une formule de tableau dans une plage de cellules et utiliser la formule de tableau 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 multicellulaire ; une formule de tableau qui réside dans une seule cellule est appelée formule unicellulaire.

Les exemples de la section suivante décrivent comment créer des formules de tableaux multi et unicellulaires.

Essayez !

Cet exercice vous montre comment utiliser des formules de tableaux multi et unicellulaires pour calculer un ensemble de chiffres de vente. La première série d'étapes utilise une formule multicellulaire pour calculer un ensemble de sous-totaux. La deuxième série utilise une formule unicellulaire pour calculer un total général.

Créer une formule de tableau multicellulaire

  1. Ouvrez un nouveau classeur vide.
  2. Copiez les données de l'exemple de feuille de calcul, et collez-les dans le nouveau classeur en commençant à la cellule A1.

AfficherComment copier les données de l'exemple de feuille de calcul

  • Créez un classeur ou une feuille de calcul vide.
  • Sélectionnez l'exemple dans la rubrique d'aide.

 Remarque   Ne sélectionnez pas les en-têtes des lignes ou des colonnes.

Sélection d'un exemple de l'aide

Sélection d'un exemple de l'aide
  • Appuyez sur Ctrl+C.
  • Dans la feuille de calcul, sélectionnez la cellule A1 et appuyez sur Ctrl+V.

Représentant commercial Type de voiture Nombre vendu Prix unitaire Total ventes
Bouraima Berline 5 2200  
  Coupé 4 1800  
Ingle Berline 6 2300  
  Coupé 8 1700  
Jordan Berline 3 2000  
  Coupé 1 1600  
Antoine Berline 9 2150  
  Coupé 5 1950  
Sanchez Berline 6 2250  
  Coupé 8 2000  

  1. Cliquez sur le bouton Options de collageImage du bouton affiché en regard, afin de respecter la mise en forme de destination.
  2. Pour multiplier des valeurs dans le 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

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

Excel met la formule entre accolades ({ }) et 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 correspond au montant total des ventes pour chaque type de voiture par représentant commercial.


Exemple de données


Créer une formule de tableau unicellulaire

  1. Dans la cellule A13 du classeur, tapez Ventes totales.
  2. Dans la cellule B13, 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 111 800 dollars de ventes. Cet exemple illustre la puissance de ce type de formule. Par exemple, supposons que vous disposiez de 15 000 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.

Par ailleurs, notez que la formule unicellulaire (dans la cellule B13) est entièrement indépendante de la formule multicellulaire (la formule des cellules E2 à E11). Cela révèle un autre avantage de l'utilisation des formules de tableaux — la souplesse d'utilisation. Vous pouvez effectuer toutes sortes d'opérations, comme modifier les formules de la colonne E ou supprimer entièrement cette colonne, sans affecter la formule unicellulaire.

Les formules de tableaux 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 multicellulaire. 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, ou 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 intermédiaires. Par exemple, le classeur que vous avez créé pour cet exercice utilise une formule de tableau pour calculer les résultats de la colonne E. Avec des formules standard (telles que =C2*D2), il vous en aurait fallu 11 différentes pour calculer les mêmes résultats.

Syntaxe des formules de tableaux

Pour l'essentiel, les formules de tableaux utilisent la syntaxe des formules standard. Elles commencent 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.

Ce qu'il faut également comprendre, c'est que les fonctions de tableaux constituent une forme de raccourci. Par exemple, la fonction multicellulaire que vous avez utilisée précédemment équivaut à :

=C2*D2
=C3*D3

etc. La formule unicellulaire de la cellule B13 condense toutes ces opérations de multiplication, en plus de l'arithmétique requise pour additionner ces sous-totaux : =E2+E3+E4, et ainsi de suite.

Règle d'entrée et de modification des formules de tableaux

La règle principale en matière de création de formule de tableau mérite d'être répétée : 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 unicellulaires que multicellulaires.

Dès que vous utilisez des formules multicellulaires, vous devez également respecter les règles suivantes :

  • Vous devez sélectionner la plage de cellules qui contiendra vos résultats avant d'entrer la formule. Vous l'avez fait à l'étape 3 de l'exercice de formule de tableau multicellulaire, 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 l'exemple de classeur et appuyez sur SUPPR.
  • 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.

 Conseil   Pour supprimer une formule de tableau, sélectionnez l'ensemble de la formule (par exemple, =C2:C11*D2:D11), appuyez sur SUPPR, puis sur CTRL+MAJ+ENTRÉE.

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

Développement d'une formule de tableau

Vous devez, dans certaines circonstances, développer une formule de tableau. (Souvenez-vous que vous ne pouvez pas réduire 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.

  1. Dans l'exemple de classeur, effacez tout texte et toute formule unicellulaire se trouvant sous le tableau principal.
  2. Collez ces lignes de données supplémentaires dans le classeur, en commençant à la cellule A12. Cliquez sur le bouton Options de collageImage du bouton qui apparaît à côté, pour respecter la mise en forme de destination.

Toth Berline 6 2500
  Coupé 7 1900
Wang Berline 4 2200
  Coupé 3 2000
Young Berline 8 2300
  Coupé 8 2100

  1. 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.
  2. Appuyez sur F2 pour passer en mode d'édition.
  3. Dans la barre de formule, changez C11 en C17 et D11 en 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.

Exemple de données


Inconvénients liés à l'utilisation de formules de tableaux

Les formules de tableaux peuvent sembler magiques, mais elles présentent malheureusement quelques inconvénients :

  • On oublie parfois d'appuyer sur CTRL+MAJ+ENTRÉE. Souvenez-vous d'utiliser cette combinaison de touches chaque fois que vous entrez ou modifiez une formule de tableau.
  • D'autres utilisateurs peuvent ne pas comprendre vos formules. Les formules de tableaux étant assez peu documentées, si d'autres personnes sont appelées à modifier vos classeurs, vous devez soit éviter les formules de tableaux, soit veiller à ce que ces utilisateurs sachent comment les modifier.
  • Suivant la vitesse de traitement et la mémoire de votre ordinateur, les formules de tableaux volumineuses peuvent ralentir vos calculs.

Haut de la page Haut de la page

En savoir plus sur les constantes de tableaux

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

Brève présentation des constantes de tableaux

Les constantes de tableaux sont des composants des formules de tableaux. Vous pouvez créer des constantes de tableaux en entrant une liste d'éléments et en mettant cette liste entre accolades ({ }), comme suit :

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

Plus haut dans cet article, nous avons souligné la nécessité d'appuyer sur CTRL+MAJ+ENTRÉE lorsque vous créez des formules de tableaux. Étant donné que les constantes de tableaux sont des composants de tableaux, vous mettez 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 délimitez (séparez) les éléments à l'aide de virgules, vous créez un tableau horizontal (une ligne). Si vous délimitez 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.

À l'instar des formules de tableaux, vous pouvez utiliser des constantes de tableaux avec chacune 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.

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 de la colonne précédente ou démarrez 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}

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

Vous obtenez le résultat suivant.


Constante de tableau horizontale dans une formule


Vous vous demandez peut-être pourquoi vous ne pouvez pas taper les chiffres manuellement. Poursuivez votre lecture... La section Utiliser des constantes dans les formules, plus loin dans cet article, démontre les avantages liés à l'utilisation de constantes de tableaux.

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 tableau verticale dans une formule de tableau


Créer une constante à deux dimensions

  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 tableau à deux dimensions dans une formule de tableau


Utiliser des constantes dans les formules

Maintenant que vous êtes familiarisé avec l'entrée de constantes de tableaux, voici un exemple simple qui met en pratique ce dont nous avons parlé :

  1. Ouvrez une feuille de calcul vide.
  2. Copiez le tableau suivant en commençant à la cellule A1. Cliquez sur le bouton Options de collagebouton Options de collage apparaît à côté, pour respecter la mise en forme de destination.

3 4 5 6 7

  1. Dans la cellule A3, entrez 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 de tableau avec constante de tableau


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

Syntaxe des constantes de tableaux

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


Syntaxe de la formule de tableau avec constante de tableau

Légende 1 Fonction
Légende 2 Tableau stocké
Légende 3 Opérateur
Légende 4 Constante de tableau

Le dernier élément qui se trouve à l'intérieur des parenthèses est la constante de tableau : {1,2,3,4,5}. Souvenez-vous qu'Excel n'entoure pas les constantes de tableaux d'accolades, c'est à vous de le faire. 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)

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 tableau :

=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 obtenez 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 tableaux 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 tableaux 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 tableaux

La meilleure manière d'utiliser des constantes de tableaux 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 utilisateurs débutants. Pour nommer une constante de tableau 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.

  1. Dans la zone Nom, tapez Trimestre1.
  2. 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

  1. Cliquez sur OK.
  2. Dans la feuille de calcul, sélectionnez une ligne de trois cellules vides.
  3. 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ète le tableau comme une chaîne de texte. Enfin, rappelez-vous que vous pouvez utiliser des combinaisons de texte et de chiffres.

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

Recherchez les problèmes suivants lorsque vos constantes de tableaux 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 tableau 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 tableaux en action

Les exemples suivants montrent de quelle manière vous pouvez mettre les constantes de tableaux à contribution dans des formules de tableaux. 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 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 tableau horizontale, la fonction TRANSPOSE convertit la constante de tableau 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 tableau 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 Haut de la page

Application des formules de tableaux de base

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

Premiers pas

Utilisez les données de cette section pour créer deux exemples de feuilles de calcul.

  1. Ouvrez un classeur existant ou créez-en un, et veillez à ce qu'il contienne deux feuilles de calcul vides.
  2. Copiez les données dans le tableau suivant, et collez-le dans le classeur, en commençant à la cellule A1.
400   Portez ce   1 2 3 4
1 200   vieux whisky   5 6 7 8
3200   au juge   9 10 11 12
475   blond   13 14 15 16
500   qui fume          
2000              
600              
1700              
800              
2700              
  1. Votre feuille de calcul terminée doit ressembler à ceci.

  2. Exemple de données complet

  3. Nommez la première feuille de calcul Données et une deuxième feuille de calcul vide Tableaux.

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

L'exemple suivant explique comment utiliser des formules de tableaux 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 tableau à 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. Entez la formule suivante dans la barre de formule, puis appuyez sur CTRL+MAJ+ENTRÉE :

=Données!E1:G3

Vous obtenez le résultat suivant.

Résultats dans la feuille de calcul Tableaux

La formule crée un lien avec les valeurs stockées dans les cellules E1 à G3 dans la feuille de calcul Données. L'alternative à cette formule multicellulaire 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 modifiez certaines des valeurs dans la feuille de calcul Données, ces modifications sont répercutées dans la feuille de calcul Tableaux. Souvenez-vous que pour modifier une valeur dans la feuille de données Données, vous devez respecter les règles d'édition des formules de tableaux. Pour plus d'informations sur ces règles, consultez la section En savoir plus sur les formules de tableaux.

Créer une constante de tableau à 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 cellules en valeurs. Excel convertit les valeurs en une constante de tableau.
  4. Appuyez sur CTRL+MAJ+ENTRÉE pour entrer la constante de tableau en tant que formule de tableau.

Excel remplace la formule de tableau =Données!E1:G3 par la constante de formule suivante :

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

Le lien a été rompu entre les feuilles de calcul Données et Tableaux, et la formule de tableau a été remplacée par une constante 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.

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

=SOMME(NBCAR(C1:C5))

La valeur 47 apparaît dans la cellule C7.

Dans ce cas, la fonction NBCAR retourne 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 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 calcul Données, sélectionnez les cellules A12 à A14.

Cet ensemble de cellules contiendra les résultats retournés par la formule de tableau.

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

=PETITE.VALEUR(A1:A10,{1;2;3})

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

Cette formule utilise une constante de tableau pour évaluer la fonction PETITE.VALEUR trois fois et retourner 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(A1:A10,{1;2;3}))

=MOYENNE(PETITE.VALEUR(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 calcul Données, sélectionnez les cellules A12 à A14.
  2. Appuyez sur SUPPR pour effacer la formule existante mais conserver les cellules sélectionnées.
  3. Dans la barre de formule, entrez la formule suivante, puis appuyez sur CTRL+MAJ+ENTRÉE :

=GRANDE.VALEUR(A1:A10,LIGNE(INDIRECT("1:3")))

Les valeurs 3200, 2700 et 2000 apparaissent respectivement dans le cellules A12, A13 et A14.

À ce stade, il peut être utile d'en savoir un peu sur les fonctions LIGNE et INDIRECT. Vous pouvez utiliser la fonction LIGNE pour 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 A1:A10, 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 régler 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(A1:A10,LIGNE(INDIRECT("1:3"))) — en partant de la parenthèse intérieure et en procédant vers l'extérieur : la fonction INDIRECT retourne 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 A1:A10, 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

Cet exemple permet de trouver 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.

  • Dans la feuille de calcul Données, effacez la formule qui se trouve dans la cellule C7, entrez la formule suivante dans cette cellule, puis appuyez sur CTRL+MAJ+ENTRÉE :

=INDEX(C1:C5;EQUIV(MAX(NBCAR(C1:C5));NBCAR(C1:C5);0);1)

La valeur à l'utilisateur apparaît dans la cellule C7.

Examinons la formule, en commençant par les éléments intérieurs et en procédant vers l'extérieur. La fonction NBCAR retourne la longueur de chacun des éléments de la plage de cellules C1:C5. 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 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 : une valeur 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(C1:C5))

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

NBCAR(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 retourne la plus grande valeur qui est également inférieure ou égale à la valeur de recherche. Si vous spécifiez 0, EQUIV retourne la première valeur exactement égale à la valeur de recherche. Si vous spécifiez -1, EQUIV trouve 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 utilise la valeur 1.

Enfin, la fonction INDEX utilise les arguments suivants : un tableau et un numéro de ligne et de colonne au sein de ce tableau. La plage de cellules C1:C5 fournit le tableau, la fonction EQUIV fournit l'adresse de cellule et l'argument final (1) spécifie que la valeur provient de la première colonne du tableau.

Pour plus d'informations sur les fonctions décrites ici, consultez l'aide d'Excel.

Haut de la page Haut de la page

Mise en situation des formules de tableaux avancées

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

Additionner une plage qui contient des valeurs erronées

La fonction SOMME d'Excel ne fonctionne pas si vous essayez d'additionner une plage qui contient une valeur d'erreur, telle que #N/A. Cet exemple vous montre comment additionner des valeurs dans une plage nommée Données qui contient des erreurs.

=SOMME(SI(ESTERREUR(Données);"";Data))

La formule crée un nouveau tableau qui contient 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 cellules (Données). La fonction SI retourne une valeur spécifique si une condition que vous spécifiez est évaluée comme VRAIE et une autre valeur si elle est évaluée comme FAUSSE. Dans notre exemple, elle retourne des chaînes vides ("") pour toutes les valeurs d'erreur car elles sont VRAIES, et retourne les valeurs restantes de la plage (Données) car elles sont évaluées comme FAUSSES, 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 retourne le nombre de valeurs d'erreur dans une plage nommée Données au lieu de les extraire par filtrage :

=SOMME(SI(ESTERREUR(Données);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 celles qui n'en contiennent pas. Vous pouvez simplifier la formule et obtenir le même résultat en supprimant le troisième argument pour la fonction SI, comme suit :

=SOMME(SI(ESTERREUR(Données);1))

Si vous ne spécifiez pas l'argument, la fonction SI retourne FAUX si une cellule ne contient pas de valeur d'erreur. Vous pouvez simplifier la formule encore plus :

=SOMME(SI(ESTERREUR(Données)*1))

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

Additionner des valeurs selon la situation

Vous pouvez devoir additionner des valeurs dans certaines conditions. Par exemple, cette formule de tableau additionne uniquement 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. Pour l'essentiel, la fonction SOMME ignore les valeurs fausses car 0+0=0. La plage de cellules que vous utilisez dans cette formule peut se composer de n'importe quel nombre de lignes et de colonnes.

Vous pouvez également additionner les valeurs qui remplissent plusieurs conditions. Par exemple, la formule de tableau suivante calcule des valeurs supérieures à 0 et inférieures ou égales à 5 :

=SOMME((Ventes>0)*(Ventes<=5)*(Ventes))

Souvenez-vous 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 tableaux qui utilisent un type de condition OU. Par exemple, vous pouvez additionner les valeurs qui sont inférieures à 5 et supérieures à 15 :

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

La fonction SI trouve 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 directement dans des formules de tableaux car ces fonctions retournent un résultat unique, VRAI ou FAUX, et les fonctions de tableaux nécessitent des tableaux de résultats. Vous pouvez contourner ce problème à l'aide de la logique présentée dans la formule précédente. En d'autres termes, vous effectuez des opérations mathématiques, telles que des additions ou des soustractions, sur des valeurs qui remplissent la condition OU ou ET.

Calculer une moyenne qui exclut les zéros

Cet exemple vous montre 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 qui ne sont pas égales à 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 retourne le nombre de différences entre les deux. Si les contenus des deux plages sont identiques, la formule retourne 0. Pour utiliser cette formule, les plages de cellules doivent être de la même taille et de la même dimension :

=SOMME(SI(MesDonnées=VosDonnées;0;1))

La formule crée un nouveau tableau, de la taille des plages que vous comparez. La fonction SI remplit le tableau à l'aide des valeurs 0 et 1 (1 pour les cellules identiques, 0 pour les autres). La fonction SOMME retourne ensuite la somme des valeurs dans le tableau.

Vous pouvez simplifier la formule comme suit :

=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 revoie le numéro de ligne de la valeur maximale dans une plage à une colonne nommée Données :

=MIN(SI(Data=MAX(Données);LIGNE(Données);""))

La fonction SI crée un nouveau tableau qui correspond à la plage Données. Si une cellule correspondante contient la valeur maximale de la plage, le tableau contient le numéro de ligne. Si ce n'est pas le cas, le tableau contient une chaîne vide (""). La fonction MIN utilise le nouveau tableau comme son deuxième argument et retourne la valeur la plus petite, qui correspond au numéro de ligne de la valeur maximale dans la plage Données. Si la plage Données contient des valeurs maximales identiques, la formule retourne la ligne de la première valeur.

Si vous souhaitez retourner l'adresse de cellule effective 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 Haut de la page

 
 
S'applique à :
Excel 2007