Téléchargez l'exemple de classeur
Ce didacticiel vous donnera une introduction aux formules de tableaux dynamiques dans Excel et Google Sheets.
introduction
En septembre 2022, Microsoft a introduit les formules de tableau dynamique dans Excel. Leur but est de faciliter l'écriture de formules complexes et avec moins de risques d'erreur.
Les formules de tableau dynamique sont destinées à remplacer à terme les formules de tableau, c'est-à-dire les formules avancées qui nécessitent l'utilisation de Ctrl + Maj + Entrée (CSE).
Voici une comparaison rapide entre la formule de tableau et la formule de tableau dynamique utilisées pour extraire une liste de départements uniques de notre liste dans la gamme A2:A7.
Formule de matrice héritée (CSE) :
La formule suivante est entrée dans la cellule D2 et est entré en appuyant sur Ctrl + Maj + Entrée et en le copiant à partir de D2 à D5.
| 1 | {=IFERREUR(INDEX($A$2:$A$7, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$7), 0)), "")} |
Formule de tableau dynamique :
La formule suivante est uniquement entrée dans la cellule D2 et entré en appuyant sur Entrée. D'un simple coup d'œil, vous pouvez dire à quel point il est facile et direct d'écrire une formule de tableau dynamique.
| 1 | =UNIQUE(A2:A7) |
Disponibilité
Depuis août 2022, les formules de tableaux dynamiques ne sont disponibles que pour les utilisateurs d'Office 365.
Déversement et gamme de déversement
Les formules de tableau dynamique fonctionnent en renvoyant plusieurs résultats à une plage de cellules en fonction d'une seule formule entrée dans une cellule.
Ce comportement est appelé "Effusion" et la plage de cellules où les résultats sont placés est appelée la « Plage de déversement ». Lorsque vous sélectionnez une cellule dans la plage de déversement, Excel la met en surbrillance avec une fine bordure bleue.
Dans l'exemple ci-dessous, la formule matricielle dynamique SORTE est dans la cellule D2 et les résultats ont été répandus dans la gamme D2:D7
| 1 | =TRIER(A2:A7) |
Les résultats de la formule sont dynamiques, ce qui signifie que si un changement se produit dans la plage source, les résultats changent également et la plage de déversement est redimensionnée.
#RÉPANDRE!
Vous devez noter que si votre plage de déversement n'est pas complètement vide, une erreur #SPILL est renvoyée.
Lorsque vous sélectionnez l'erreur #SPILL, la plage de déversement souhaitée de la formule est mise en surbrillance avec une bordure bleue en pointillés. Le déplacement ou la suppression des données dans la cellule non vide supprime cette erreur, ce qui permet à la formule de se répandre.
Notation de référence de déversement
Pour référencer la plage de déversement d'une formule, nous plaçons le # symbole après la référence de cellule de la première cellule du déversement.
Vous pouvez également référencer le déversement en sélectionnant toutes les cellules de la plage de déversement et une référence au déversement sera automatiquement créée.
Dans l'exemple ci-dessous, nous aimerions compter le nombre d'employés dans notre entreprise en utilisant la formule COUNTA après avoir été classés par ordre alphabétique à l'aide de la formule matricielle dynamique SORTE.
Nous entrons dans le SORTE formule en D2 pour ordonner les salariés de notre liste :
| 1 | =TRIER(A2:A7) |
Nous entrons alors dans le COUNTA formule dans G2 pour compter le nombre d'employés :
| 1 | =COUNTA(D2#) |
Notez l'utilisation de # dans D2# pour faire référence aux résultats renversés par SORT dans la plage D2:D7.
Nouvelles formules
Vous trouverez ci-dessous la liste complète des nouvelles formules de tableau dynamique :
- UNIQUE - Renvoie une liste de valeurs uniques d'une plage
- SORTE - Trie les valeurs dans une plage
- TRIER PAR - Trie les valeurs en fonction d'une plage correspondante
- FILTRE - Filtre une plage en fonction des critères fournis
- RANDONNÉE - Renvoie un tableau de nombres aléatoires entre 0 et 1
- SÉQUENCE - Génère une liste de numéros séquentiels tels que 1, 2, 3, 4, 5
Formule de tableau dynamiques dans Google Sheets
Tous les exemples ci-dessus fonctionnent exactement de la même manière dans Google Sheets que dans Excel.
