Dans ce tutoriel, vous apprendrez ce qu'est une formule de tableau Excel, comment l'entrer correctement dans vos feuilles de travail et comment utiliser les constantes de tableau et les fonctions de tableau.

Les formules de tableau dans Excel sont un outil extrêmement puissant et l'une des plus difficiles à ma?triser. Une formule de tableau unique peut effectuer plusieurs calculs et remplacer des milliers de formules habituelles. Et pourtant, 90% des utilisateurs n'ont jamais utilisé de fonctions de tableau dans leurs feuilles de travail simplement parce qu'ils ont peur de commencer à les apprendre.

En effet, les formules de tableaux l'une des fonctionnalités Excel les plus confuses à apprendre. Le but de ce tutoriel est de rendre la courbe d'apprentissage aussi facile et lisse que possible.

Qu'est-ce qu'un tableau dans Excel?

Avant de commencer les fonctions et les formules du tableau, déterminons ce que signifie le terme "tableau". Essentiellement, un tableau est une collection d'articles. Les éléments peuvent être du texte ou des numéros et ils peuvent résider dans une seule ligne ou colonne, ou dans plusieurs lignes et colonnes.

Par exemple, si vous mettez votre liste d'épicerie hebdomadaire dans un format de tableau Excel, cela ressemblerait:

{"Lait", "?ufs", "beurre", "flocons de ma?s"}

Ensuite, si vous sélectionnez les cellules A1 à D1, entrez le tableau ci-dessus précédé d'un signe égal (=) dans la barre de formule et appuyez sur Ctrl Shift Entrée , vous obtiendrez le résultat suivant:

Ce que vous venez de faire, c'est de créer un tableau horizontal unidimensionnel. Rien de terrible jusqu'à présent, non?

Qu'est-ce qu'une formule de tableau dans Excel?

La différence entre une formule de tableau et une formule régulière est qu'une formule de tableau traite plusieurs valeurs au lieu d'une seule. En d'autres termes, une formule de tableau dans Excel évalue toutes les valeurs individuelles dans un tableau et effectue plusieurs calculs sur un ou plusieurs éléments selon les conditions exprimées dans la formule.

Non seulement une formule de tableau peut gérer plusieurs valeurs simultanément, mais elle peut également renvoyer plusieurs valeurs à la fois. Ainsi, les résultats renvoyés par une formule de tableau sont également un tableau.

Des formules de tableaux sont disponibles dans toutes les versions d'Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 et Lower.

Et maintenant, cela semble être le bon moment pour vous de créer votre première formule de tableau.

Exemple simple de formule de tableau Excel

Supposons que vous ayez des articles dans la colonne B, leurs prix dans la colonne C et que vous souhaitez calculer le total grand de toutes les ventes.

Bien s?r, rien ne vous empêche de calculer d'abord les sous-totaux dans chaque ligne avec quelque chose d'aussi simple que =B2*C2 , puis de résumer ces valeurs:

Cependant, une formule de tableau peut vous épargner ces traits de clés supplémentaires car il obtient Excel pour stocker des résultats intermédiaires en mémoire plut?t que dans une colonne supplémentaire. Ainsi, il suffit d'une formule de tableau unique et de 2 étapes rapides:

1. Sélectionnez une cellule vide et entrez la formule suivante:

   =SUM(B2:B6*C2:C6)

2. Appuyez sur le décalage du raccourci du clavier Ctrl Shift Entrez pour terminer la formule du tableau.

   Une fois que vous avez fait cela, Microsoft Excel entoure la formule avec {Braces Curly}, qui est une indication visuelle d'une formule de tableau.

   Ce que fait la formule, c'est multiplier les valeurs dans chaque ligne individuelle du tableau spécifié (cellules B2 à C6), ajouter les sous-totaux ensemble et sortir le grand total:

   

Cet exemple simple montre à quel point une formule de tableau peut être puissante. Lorsque vous travaillez avec des centaines et des milliers de rangées de données, pensez simplement combien de temps vous pouvez économiser en entrant une formule de tableau dans une seule cellule.

Pourquoi utiliser des formules de tableau dans Excel?

Les formules de tableau Excel sont l'outil le plus pratique pour effectuer des calculs sophistiqués et effectuer des taches complexes. Une formule unique peut remplacer littéralement des centaines de formules habituelles. Les formules de tableau sont très bonnes pour les taches telles que:

• Les nombres de somme qui remplissent certaines conditions, par exemple, la somme n plus grande ou plus petite valeur dans une plage.
• Summer toutes les autres lignes, ou toutes les ^lignes ou colonnes, comme démontré dans cet exemple.
• Comptez le nombre de tous ou certains caractères dans une plage spécifiée. Voici une formule Array qui compte tous les caractères, et un autre qui compte tous les caractères donnés.

Comment saisir la formule du tableau dans Excel (Ctrl Shift Entrée)

Comme vous le savez déjà, la combinaison des 3 touches Ctrl Shift Entrée est une touche magique qui transforme une formule régulière en une formule de tableau.

Lorsque vous entrez dans une formule Array dans Excel, il y a 4 choses importantes à garder à l'esprit:

1. Une fois que vous avez terminé la saisie de la formule et appuyé simultanément sur les touches Ctrl Shift Entrée, Excel enferme automatiquement la formule entre {Braces Curly}. Lorsque vous sélectionnez une (s) cellule (s), vous pouvez voir les accolades dans la barre de formule, ce qui vous donne une idée qu'une formule de tableau est là.
2. Typer manuellement les accolades autour d'une formule ne fonctionnera pas. Vous devez appuyer sur le raccourci Entrée Ctrl Shift pour terminer une formule de tableau.
3. Chaque fois que vous modifiez une formule de tableau, les accolades disparaissent et vous devez appuyer sur Ctrl Shift Entrez à nouveau pour enregistrer les modifications.
4. Si vous oubliez d'appuyer sur Ctrl Shift Entrée, votre formule se comportera comme une formule habituelle et ne traitera que la (s) valeur (s) dans le (s) tableau (s) spécifié.

étant donné que toutes les formules du tableau Excel nécessitent une appuye sur le décalage Ctrl Entrée, ils sont parfois appelés formules CSE .

Utilisez la touche F9 pour évaluer les parties d'une formule de tableau

Lorsque vous travaillez avec des formules de tableau dans Excel, vous pouvez observer comment ils calculent et stockent leurs articles (tableaux internes) pour afficher le résultat final que vous voyez dans une cellule. Pour ce faire, sélectionnez un ou plusieurs arguments entre parenthèses d'une fonction, puis appuyez sur la touche F9 . Pour quitter le mode d'évaluation de la formule, appuyez sur la touche ESC .

Dans l'exemple ci-dessus, pour voir les sous-totaux de tous les produits, vous sélectionnez B2: B6 * C2: C6, appuyez sur F9 et obtenez le résultat suivant.

Note. Veuillez faire attention que vous devez sélectionner une partie de la formule avant d'appuyer sur F9, sinon la touche F9 remplacera simplement votre formule par la valeur calculée.

Formules de tableau unique et multi-cellules dans Excel

La formule du tableau Excel peut renvoyer un résultat dans une seule cellule ou dans plusieurs cellules. Une formule de tableau saisie dans une gamme de cellules est appelée formule multi-cellules . Une formule de tableau résidant dans une seule cellule est appelée formule unique .

Il existe quelques fonctions de tableau Excel con?ues pour renvoyer des tableaux multi-cellules, par exemple la transposition, la tendance, la fréquence, le linest, etc.

D'autres fonctions, telles que la somme, la moyenne, l'agrégat, le max, le min, peuvent calculer les expressions du tableau lorsqu'elles sont entrées dans une seule cellule en utilisant le décalage de Ctrl Entrée .

Les exemples suivants montrent comment utiliser une formule de tableau unique et multi-cellules.

Exemple 1. Une formule de tableau unique

Supposons que vous ayez deux colonnes répertoriant le nombre d'articles vendus en 2 mois différents, disons des colonnes B et C, et que vous souhaitez trouver l'augmentation maximale des ventes.

Normalement, vous ajouteriez une colonne supplémentaire, disons la colonne D, qui calcule la modification des ventes pour chaque produit à l'aide d'une formule comme =C2-B2 , puis trouvez la valeur maximale dans cette colonne supplémentaire =MAX(D:D) .

Une formule de tableau n'a pas besoin d'une colonne supplémentaire car elle stocke parfaitement les résultats intermédiaires dans la mémoire. Ainsi, vous entrez simplement la formule suivante et appuyez sur Ctrl Shift Entrée :

=MAX(C2:C6-B2:B6)

Exemple 2. Une formule de tableau multi-cellules dans Excel

Dans l'exemple de somme précédent, supposons que vous deviez payer 10% de taxe de chaque vente et que vous souhaitez calculer le montant de l'imp?t pour chaque produit avec une formule.

Sélectionnez la plage des cellules vides, disons D2: D6, et entrez la formule suivante dans la barre de formule:

=B2:B6 * C2:C6 * 0.1

Une fois que vous appuyez sur Ctrl Shift Entrée , Excel placera une instance de votre formule de tableau dans chaque cellule de la plage sélectionnée, et vous obtiendrez le résultat suivant:

Exemple 3. Utilisation d'une fonction de tableau Excel pour renvoyer un tableau multi-cellules

Comme déjà mentionné, Microsoft Excel fournit quelques soi-disant "fonctions de tableau" qui sont spécialement con?ues pour fonctionner avec des tableaux multi-cellules. Transpose est l'une de ces fonctions et nous allons l'utiliser pour transposer le tableau ci-dessus, c'est-à-dire convertir les lignes en colonnes.

1. Sélectionnez une gamme vide de cellules où vous souhaitez sortir le tableau transposé. étant donné que nous convertissons les lignes en colonnes, assurez-vous de sélectionner le même nombre de lignes et de colonnes que votre table source a des colonnes et des lignes, respectivement. Dans cet exemple, nous sélectionnons 6 colonnes et 4 lignes.
2. Appuyez sur F2 pour entrer le mode d'édition.
3. Entrez la formule et appuyez sur Ctrl Shift Entrée .

Dans notre exemple, la formule est:

=TRANSPOSE($A$1:$D$6)

Le résultat va ressembler à ceci:

C'est ainsi que vous utilisez Transpose comme formule de tableau CSE dans Excel 2019 et plus t?t. Dans Dynamic Array Excel, cela fonctionne également comme une formule régulière. Pour apprendre d'autres fa?ons de transposer dans Excel, veuillez consulter ce tutoriel: comment changer de colonne et de lignes dans Excel.

Comment travailler avec des formules de tableau multi-cellules

Lorsque vous travaillez avec des formules de tableau multi-cellules dans Excel, assurez-vous de suivre ces règles pour obtenir les résultats corrects:

1. Sélectionnez la plage de cellules où vous souhaitez produire les résultats avant d'entrer dans la formule.
2. Pour supprimer une formule de tableau multi-cellules, sélectionnez toutes les cellules contenant et appuyez sur Supprimer , soit sélectionnez la formule entière dans la barre de formule, appuyez sur Supprimer , puis appuyez sur Ctrl Shift Entrée .
3. Vous ne pouvez pas modifier ou déplacer le contenu d'une cellule individuelle dans une formule de tableau, et vous ne pouvez pas insérer de nouvelles cellules dans ou supprimer les cellules existantes d'une formule de tableau multi-cellules. Chaque fois que vous essayez de le faire, Microsoft Excel lancera l'avertissement " Vous ne pouvez pas changer une partie d'un tableau ".
4. Pour réduire une formule de tableau, c'est-à-dire pour l'appliquer à moins de cellules, vous devez d'abord supprimer la formule existante, puis entrer une nouvelle.
5. Pour développer une formule de tableau, c'est-à-dire l'appliquer à plus de cellules, sélectionnez toutes les cellules contenant la formule actuelle plus les cellules vides où vous souhaitez l'avoir, appuyez sur F2 pour passer en mode d'édition, ajustez les références dans la formule et appuyez sur Ctrl Shift Entrez pour le mettre à jour.
6. Vous ne pouvez pas utiliser des formules de tableau multi-cellules dans des tables Excel.
7. Vous devez saisir une formule de tableau multi-cellules dans une gamme de cellules de la même taille que le réseau résultant renvoyé par la formule. Si votre formule Excel Array produit un tableau supérieur à la plage sélectionnée, les valeurs excédentaires n'appara?tront pas sur la feuille de calcul. Si un tableau renvoyé par la formule est plus petit que la plage sélectionnée, les erreurs # N / A appara?tront dans des cellules supplémentaires.

Si votre formule peut renvoyer un tableau avec un nombre variable d'éléments, entrez-le dans une plage égale ou supérieure au tableau maximal renvoyé par la formule et enveloppez votre formule dans la fonction IFERROR, comme démontré dans cet exemple.

Constantes de tableau Excel

Dans Microsoft Excel, une constante de tableau est simplement un ensemble de valeurs statiques. Ces valeurs ne changent jamais lorsque vous copiez une formule vers d'autres cellules ou valeurs.

Vous avez déjà vu un exemple de constante de tableau créé à partir d'une liste d'épicerie au tout début de ce tutoriel. Maintenant, voyons quels autres types de tableaux existent et comment vous les créez.

Il existe 3 types de constantes de tableau:

1. Constante de réseau horizontal

Une constante de réseau horizontale réside dans une rangée. Pour créer une constante de tableau de ligne, tapez les valeurs séparées par des virgules et enfermez ensuite en accolades, par exemple {1,2,3,4}.

Note. Lors de la création d'une constante de tableau, vous devez taper manuellement les accolades d'ouverture et de fermeture.

Pour entrer un tableau horizontal dans une feuille de calcul, sélectionnez le nombre correspondant de cellules vierges en ligne, tapez la formule ={1,2,3,4} dans la barre de formule et appuyez sur Ctrl Shift Entrée . Le résultat sera similaire à cela:

Comme vous le voyez dans la capture d'écran, Excel enveloppe une constante de tableau dans un autre ensemble d'accès, exactement comme il le fait lorsque vous entrez dans une formule de tableau.

2. Constante de réseau vertical

Une constante de réseau vertical réside dans une colonne. Vous le créez de la même manière qu'un tableau horizontal avec la seule différence que vous délimitez les éléments avec des demi-colons, par exemple:

={11; 22; 33; 44}

3. Constante de tableau bidimensionnel

Pour créer un tableau bidimensionnel, vous séparez chaque ligne par un point-virgule et chaque colonne de données par une virgule.

={"a", "b", "c"; 1, 2, 3}

Travailler avec des constantes de tableau Excel

Les constantes de tableau sont l'une des pierres angulaires d'une formule de tableau Excel. Les informations et conseils suivants pourraient vous aider à les utiliser de la manière la plus efficace.

1. éléments d'une constante de tableau

   Une constante de tableau peut contenir des nombres, des valeurs de texte, des booléens (true et false) et des valeurs d'erreur, séparées par des virgules ou des demi-colons.

   Vous pouvez saisir une valeur numérique en tant qu'entier, décimal ou en notation scientifique. Si vous utilisez des valeurs de texte, elles doivent être entourées de citations doubles (") comme dans n'importe quelle formule Excel.

   Une constante de tableau ne peut pas inclure d'autres tableaux, références de cellules, plages, dates, noms définis, formules ou fonctions.

2. Nommer les constantes de tableau

   Pour rendre une constante de tableau plus facile à utiliser, donnez-lui un nom:

   • Passez à l' onglet Formules> Groupe des noms définis et cliquez sur Définir le nom . Alternativement, appuyez sur Ctrl F3 et cliquez sur Nouveau .
   • Tapez le nom dans le nom
   • Dans la bo?te référence , entrez les éléments de votre constante de tableau entouré de contreventement avec le signe d'égalité précédent (=). Par exemple:

     ={"Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"}

     

   • Cliquez sur OK pour enregistrer votre tableau nommé et fermer la fenêtre.

   Pour saisir la constante de tableau nommée dans une feuille, sélectionnez autant de cellules dans une ligne ou une colonne car il y a des éléments dans votre tableau, tapez le nom du tableau dans la barre de formule précédée du signe et appuyez sur Ctrl Shift Entrée .

   Le résultat devrait ressembler à ceci:

   

3. Prévenir les erreurs

   Si votre constante de tableau ne fonctionne pas correctement, vérifiez les problèmes suivants:

   • Délimiter les éléments de votre tableau constant avec le caractère approprié - virgule dans les constantes de réseau horizontal et semi-colon en vertical.
   • Sélectionné une gamme de cellules qui correspond exactement au nombre d'éléments dans votre constante de réseau. Si vous sélectionnez plus de cellules, chaque cellule supplémentaire aura l'erreur # n / a. Si vous sélectionnez moins de cellules, seule une partie du tableau sera insérée.

Utilisation de constantes de tableau dans des formules Excel

Maintenant que vous connaissez le concept de constantes de tableau, voyons comment vous pouvez utiliser les informations des arrayons pour résoudre vos taches pratiques.

Exemple 1. Sum n les plus grands / plus petits nombres dans une plage

Vous commencez par créer une constante de tableau vertical contenant autant de nombres que vous souhaitez résumer. Par exemple, si vous souhaitez additionner 3 nombres les plus petits ou les plus importants dans une plage, la constante de tableau est {1,2,3}.

Ensuite, vous prenez une fonction grande ou petite, spécifiez une gamme entière de cellules dans le premier paramètre et incluez la constante de tableau dans la seconde. Enfin, intégrez-le dans la fonction de somme, comme ceci:

Sumage les 3 plus grands nombres: =SUM(LARGE(range, {1,2,3}))

Sumage les 3 plus petits nombres: =SUM(SMALL(range, {1,2,3}))

N'oubliez pas d'appuyer sur Ctrl Shift Entrée puisque vous entrez dans une formule de tableau, et vous obtiendrez le résultat suivant:

De la même manière, vous pouvez calculer la moyenne de n les plus petites ou les plus grandes valeurs dans une gamme:

Moyenne des 3 premiers nombres: =AVERAGE(LARGE(range, {1,2,3}))

Moyenne des 3 nombres inférieurs: =AVERAGE(SMALL(range, {1,2,3}))

Exemple 2. Formule du tableau pour compter les cellules avec plusieurs conditions

Supposons que vous ayez une liste de commandes et que vous voulez savoir combien de fois un vendeur donné a vendu des produits donnés.

Le moyen le plus simple serait d'utiliser une formule Coups avec plusieurs conditions. Cependant, si vous souhaitez inclure de nombreux produits, votre formule Coups peut devenir trop grande. Pour le rendre plus compact, vous pouvez utiliser les comptes avec SUM et inclure une constante de tableau dans un ou plusieurs arguments, par exemple:

=SUM(COUNTIFS(range1, "criteria1", range2, {"criteria1", "criteria2"}))

La vraie formule peut ressembler à ce qui suit:

=SUM(COUNTIFS(B2:B9, "sally", C2:C9, {"apples", "lemons"}))

Notre échantillon de tableau ne se compose que de deux éléments car l'objectif est de démontrer l'approche. Dans vos réelles formules de tableau, vous pouvez inclure autant d'éléments que l'exige votre logique commerciale, à condition que la durée totale de la formule ne dépasse pas 8 192 caractères dans Excel 2019 - 2007 (1024 caractères dans Excel 2003 et inférieur) et votre ordinateur est suffisamment puissant pour traiter les grandes tableaux. Veuillez consulter les limites des formules du tableau pour plus de détails.

Et voici un exemple de formule de tableau avancé qui trouve la somme de toutes les valeurs correspondantes dans un tableau: Sum et Vlookup avec une constante de tableau.

Et et ou les opérateurs dans des formules de tableau Excel

Un opérateur de tableau explique à la formule comment vous souhaitez traiter les tableaux - en utilisant et ou ou logique.

• Et l'opérateur est l'astérisque (*) qui est le symbole de multiplication. Il demande à Excel de retourner vrai si toutes les conditions évaluent à vrai.
• Ou l'opérateur est le signe plus (). Il renvoie True si l'une des conditions d'une expression donnée évalue TRUE.

Formule de tableau avec l'opérateur et

Dans cet exemple, nous trouvons la somme des ventes où le vendeur est Mike et le produit est des pommes :

=SUM((A2:A9="Mike") * (B2:B9="Apples") * (C2:C9))

Ou

=SUM(IF(((A2:A9="Mike") * (B2:B9="Apples")), (C2:C9)))

Techniquement, cette formule multiplie les éléments des trois tableaux dans les mêmes positions. Les deux premiers tableaux sont représentés par des valeurs vraies et fausses qui sont les résultats de la comparaison A2: A9 à Mike "et B2: B9 à" Pommes ". Le troisième tableau contient les nombres de vente de la plage C2: C9. Comme toute opération mathématique, la multiplication convertit vrai et FAUX à 1 et 0, respectivement. Et parce que la multiplication par 0 donne toujours un zéro, le tableau correspondant a 0 L'élément du troisième tableau entre dans le tableau final (par exemple 1 * 1 * C2 = 10).

Formule Excel Array avec l'opérateur OR

La formule de table suivante avec l'OR OR () additionne toutes les ventes où le vendeur est Mike ou le produit est des pommes:

=SUM(IF(((A2:A9="Mike") (B2:B9="Apples")), (C2:C9)))

Dans cette formule, vous additionnez les éléments des deux premiers matrices (qui sont les conditions que vous souhaitez tester) et devenez réalité (> 0) si au moins une condition évalue à TRUE; False (0) lorsque toutes les conditions évaluent à False. Ensuite, si vous vérifie si le résultat de l'addition est supérieur à 0, et si c'est le cas, la somme additionne un élément correspondant du troisième tableau (C2: C9).

Conseil. Dans les versions modernes d'Excel, il n'est pas nécessaire d'utiliser une formule de tableau pour ce type de taches - une simple formule SUMIFS les gère parfaitement. Néanmoins, les opérateurs et / ou les formules de tableau peuvent s'avérer utiles dans des scénarios plus complexes, sans parler d'une très bonne gymnastique d'esprit :)

Double opérateur unaire dans les formules de tableau Excel

Si vous avez déjà travaillé avec des formules Array dans Excel, il est probable que vous en rencontriez quelques-uns contenant un double tableau de bord (-) et vous vous êtes peut-être demandé pour quoi il a été utilisé.

Un double tableau de bord, qui est techniquement appelé l' opérateur double unaire ou le double négatif , est utilisé pour convertir les valeurs booléennes non numériques (vrai / false) renvoyées par certaines expressions en 1 et 0 qu'une fonction de tableau peut comprendre.

Nous espérons que l'exemple suivant rendra les choses plus faciles à comprendre. Supposons que vous ayez une liste de dates dans la colonne A et que vous souhaitez savoir combien de dates se produisent en janvier, quelle que soit l'année.

La formule suivante fonctionnera un régal:

=SUM(--(MONTH(A2:A10)=1))

Puisqu'il s'agit d'une formule de tableau Excel, n'oubliez pas d'appuyer sur Ctrl Shift Entrée pour la terminer.

Si vous êtes intéressé par un autre mois, remplacez 1 par un numéro correspondant. Par exemple, 2 représente le 3 février 3 signifie mars, etc. Pour rendre la formule plus flexible, vous pouvez spécifier le numéro de mois dans une cellule, comme démontré dans la capture d'écran:

Et maintenant, analysons comment fonctionne cette formule de tableau. La fonction du mois renvoie le mois de chaque date dans les cellules A2 à A10 représentée par un numéro de série, qui produisant le tableau {2; 1; 4; 2; 12; 1; 2; 12; 1}.

Après cela, chaque élément du tableau est comparé à la valeur de la cellule D1, qui est le numéro 1 dans cet exemple. Le résultat de cette comparaison est un tableau de valeurs booléennes vraies et fausses. Si vous vous en souvenez, vous pouvez sélectionner une certaine partie d'une formule de tableau et appuyer sur F9 pour voir à quoi cette partie équivaut à:

Enfin, vous devez convertir ces valeurs booléennes en 1 et 0 que la fonction de somme peut comprendre. Et c'est pour cela que le signe double négatif est nécessaire. Les premières coercitions unaires True / False à -1/0, respectivement. Le second annule les valeurs, c'est-à-dire inverse le signe, les transformant en 1 et 0, avec lesquels la plupart des fonctions Excel peuvent comprendre et fonctionner. Si vous retirez le double unary de la formule ci-dessus, cela ne fonctionnera pas.

J'espère que ce court tutoriel s'est avéré utile sur votre route pour ma?triser les formules de tableau Excel. La semaine prochaine, nous allons continuer avec des tableaux Excel en nous concentrant sur des exemples de formule avancés. Veuillez rester à l'écoute et merci d'avoir lu!

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!