Tableaux dynamiques dans Excel

Table des matières

Que sont les tableaux dynamiques

En septembre 2018, Microsoft a publié une mise à jour qui ajoute un tout nouvel outil à Microsoft Excel : les tableaux dynamiques et 7 nouvelles fonctions pour travailler avec eux. Ces choses, sans exagération, changent radicalement toute la technique habituelle de travail avec des formules et des fonctions et concernent, littéralement, chaque utilisateur.

Prenons un exemple simple pour expliquer l'essence.

Supposons que nous disposions d'un tableau simple contenant des données sur les villes-mois. Que se passera-t-il si nous sélectionnons une cellule vide à droite de la feuille et y entrons une formule qui lie non pas à une cellule, mais immédiatement à une plage ?

Dans toutes les versions précédentes d'Excel, après avoir cliqué sur Entrer on obtiendrait le contenu d'une seule première cellule B2. Sinon comment?

Eh bien, ou il serait possible d'envelopper cette plage dans une sorte de fonction d'agrégation comme = SOMME (B2: C4) et d'obtenir un grand total pour cela.

Si nous avions besoin d'opérations plus complexes qu'une somme primitive, comme l'extraction de valeurs uniques ou de Top 3, nous devions alors saisir notre formule sous forme de formule matricielle à l'aide d'un raccourci clavier. Ctrl+Shift+Entrer.

Maintenant, tout est différent.

Maintenant, après avoir entré une telle formule, nous pouvons simplement cliquer sur Entrer – et obtenir comme résultat immédiatement toutes les valeurs uXNUMXbuXNUMXb auxquelles nous nous sommes référés :

Ce n'est pas de la magie, mais les nouveaux tableaux dynamiques dont Microsoft Excel dispose désormais. Bienvenue dans le nouveau monde 🙂

Fonctionnalités de travail avec des tableaux dynamiques

Techniquement, tout notre tableau dynamique est stocké dans la première cellule G4, remplissant le nombre requis de cellules à droite et en bas avec ses données. Si vous sélectionnez une autre cellule dans le tableau, le lien dans la barre de formule sera inactif, indiquant que nous sommes dans l'une des cellules "enfant":

Une tentative de suppression d'une ou plusieurs cellules "filles" ne mènera à rien - Excel les recalculera immédiatement et les remplira.

En même temps, nous pouvons nous référer en toute sécurité à ces cellules « enfants » dans d'autres formules :

Si vous copiez la première cellule d'un tableau (par exemple, de G4 à F8), alors le tableau entier (ses références) se déplacera dans la même direction que dans les formules régulières :

Si nous devons déplacer le tableau, il suffira de déplacer (avec la souris ou une combinaison de Ctrl+X, Ctrl+V), encore une fois, seule la première cellule principale G4 - après elle, elle sera transférée vers un nouvel endroit et tout notre réseau sera à nouveau étendu.

Si vous avez besoin de faire référence ailleurs sur la feuille au tableau dynamique créé, vous pouvez utiliser le caractère spécial # ("dièse") après l'adresse de sa première cellule :

Par exemple, vous pouvez maintenant facilement créer une liste déroulante dans une cellule qui fait référence au tableau dynamique créé :

Erreurs de tableau dynamique

Mais que se passe-t-il s'il n'y a pas assez d'espace pour étendre le tableau, ou s'il y a des cellules déjà occupées par d'autres données sur son chemin ? Rencontrez un type d'erreurs fondamentalement nouveau dans Excel - #TRANSFÉRER! (#RÉPANDRE!):

Comme toujours, si nous cliquons sur l'icône avec un losange jaune et un point d'exclamation, nous obtiendrons une explication plus détaillée de la source du problème et nous pourrons rapidement trouver des cellules interférentes :

Des erreurs similaires se produiront si le tableau sort de la feuille ou atteint une cellule fusionnée. Si vous supprimez l'obstacle, tout sera immédiatement corrigé à la volée.

Tableaux dynamiques et tables intelligentes

Si le tableau dynamique pointe vers une table "intelligente" créée par un raccourci clavier Ctrl+T ou Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau), il héritera également de sa principale qualité - le dimensionnement automatique.

Lors de l'ajout de nouvelles données en bas ou à droite, le tableau intelligent et la plage dynamique s'étirent également automatiquement :

Cependant, il y a une limitation : nous ne pouvons pas utiliser une référence de plage dynamique dans les formules à l'intérieur d'une table intelligente :

Tableaux dynamiques et autres fonctionnalités Excel

D'accord, dites-vous. Tout cela est intéressant et amusant. Pas besoin, comme avant, d'étirer manuellement la formule avec une référence à la première cellule de la plage d'origine vers le bas et vers la droite et tout ça. Et c'est tout?

Pas tout à fait.

Les tableaux dynamiques ne sont pas simplement un autre outil dans Excel. Désormais, ils sont intégrés au cœur (ou au cerveau) de Microsoft Excel, son moteur de calcul. Cela signifie que d'autres formules et fonctions Excel qui nous sont familières prennent désormais également en charge l'utilisation de tableaux dynamiques. Examinons quelques exemples pour vous donner une idée de la profondeur des changements qui ont eu lieu.

Transposer

Pour transposer une plage (permuter les lignes et les colonnes) Microsoft Excel a toujours eu une fonction intégrée TRANSP (TRANSPOSER). Cependant, pour l'utiliser, vous devez d'abord sélectionner correctement la plage des résultats (par exemple, si l'entrée était une plage de 5 × 3, alors vous devez avoir sélectionné 3 × 5), puis entrez la fonction et appuyez sur la touche combinaison Ctrl+Shift+Entrer, car il ne pouvait fonctionner qu'en mode formule matricielle.

Maintenant, vous pouvez simplement sélectionner une cellule, y entrer la même formule et cliquer sur la normale Entrer – tableau dynamique fera tout par lui-même :

Table de multiplication

C'est l'exemple que j'avais l'habitude de donner lorsqu'on m'a demandé de visualiser les avantages des formules matricielles dans Excel. Maintenant, pour calculer l'ensemble du tableau de Pythagore, il suffit de se tenir dans la première cellule B2, d'y entrer une formule qui multiplie deux tableaux (ensemble vertical et horizontal de nombres 1..10) et de cliquer simplement sur Entrer:

Collage et transformation de caisses

Les tableaux peuvent non seulement être multipliés, mais aussi collés avec l'opérateur standard & (esperluette). Supposons que nous devions extraire le prénom et le nom de deux colonnes et corriger la casse sautante dans les données d'origine. Nous faisons cela avec une formule courte qui forme l'ensemble du tableau, puis nous lui appliquons la fonction PROPNACH (CORRECT)pour ranger le registre :

Conclusion Top 3

Supposons que nous ayons un groupe de nombres à partir desquels nous voulons dériver les trois premiers résultats, en les organisant par ordre décroissant. Maintenant, cela se fait par une formule et, encore une fois, sans aucun Ctrl+Shift+Entrer comme avant:

Si vous souhaitez que les résultats ne soient pas placés dans une colonne, mais dans une ligne, il suffit de remplacer les deux-points (séparateur de ligne) dans cette formule par un point-virgule (séparateur d'éléments dans une ligne). Dans la version anglaise d'Excel, ces séparateurs sont respectivement des points-virgules et des virgules.

VLOOKUP extrayant plusieurs colonnes à la fois

Les fonctions VPR (VLOOKUP) maintenant vous pouvez extraire des valeurs non pas d'une, mais de plusieurs colonnes à la fois - spécifiez simplement leurs numéros (dans n'importe quel ordre souhaité) sous forme de tableau dans le troisième argument de la fonction :

Fonction OFFSET renvoyant un tableau dynamique

L'une des fonctions les plus intéressantes et les plus utiles (après VLOOKUP) pour l'analyse des données est la fonction ÉLIMINATION (DÉCALAGE), auquel j'ai consacré à la fois un chapitre entier dans mon livre et un article ici. La difficulté à comprendre et à maîtriser cette fonction a toujours été qu'elle renvoyait un tableau (plage) de données en conséquence, mais nous ne pouvions pas le voir, car Excel ne savait toujours pas comment travailler avec des tableaux prêts à l'emploi.

Maintenant, ce problème appartient au passé. Voyez comment maintenant, en utilisant une seule formule et un tableau dynamique renvoyé par OFFSET, vous pouvez extraire toutes les lignes d'un produit donné à partir de n'importe quelle table triée :

Voyons ses arguments :

A1 – cellule de départ (point de référence)
ПОИСКПОЗ(F2;A2:A30;0) – calcul du décalage de la cellule de départ vers le bas – jusqu'au premier chou trouvé.
0 – décalage de la « fenêtre » vers la droite par rapport à la cellule de départ
СЧЁТЕСЛИ(A2:A30;F2) – calcul de la hauteur de la « fenêtre » retournée – le nombre de lignes où il y a du chou.
4 — taille de la "fenêtre" horizontalement, c'est-à-dire sortie 4 colonnes

Nouvelles fonctions pour les tableaux dynamiques

En plus de prendre en charge le mécanisme de tableau dynamique dans les anciennes fonctions, plusieurs fonctions entièrement nouvelles ont été ajoutées à Microsoft Excel, affinées spécifiquement pour travailler avec des tableaux dynamiques. Ce sont notamment :

QUALITÉ (TRIER) – trie la plage d'entrée et produit un tableau dynamique sur la sortie
SORTPO (TRIER PAR) – peut trier une plage par des valeurs d'une autre
FILTRE (FILTRE) – récupère les lignes de la plage source qui répondent aux conditions spécifiées
UNIK (UNIQUE) – extrait des valeurs uniques d'une plage ou supprime les doublons
SLMASSIF (ALÉATOIRE) – génère un tableau de nombres aléatoires d'une taille donnée
PLACENTA (SÉQUENCE) — forme un tableau à partir d'une séquence de nombres avec un pas donné

Plus d'informations sur eux - un peu plus tard. Ils valent un article séparé (et pas un seul) pour une étude approfondie 🙂

Conclusions

Si vous avez lu tout ce qui est écrit ci-dessus, je pense que vous réalisez déjà l'ampleur des changements qui ont eu lieu. Tant de choses dans Excel peuvent maintenant être faites plus facilement, plus facilement et plus logiquement. Je dois avouer que je suis un peu choqué du nombre d'articles qu'il va maintenant falloir corriger ici, sur ce site et dans mes livres, mais je suis prêt à le faire le cœur léger.

Résumant les résultats, plus tableaux dynamiques, vous pouvez écrire ce qui suit :

Vous pouvez oublier la combinaison Ctrl+Shift+Entrer. Excel ne voit plus de différence entre les « formules régulières » et les « formules matricielles » et les traite de la même manière.
À propos de la fonction SUMPRODUCT (SOMMEPROD), qui était auparavant utilisé pour saisir des formules matricielles sans Ctrl+Shift+Entrer vous pouvez aussi oublier - maintenant c'est assez facile SUM и Entrer.
Les tables intelligentes et les fonctions familières (SUM, IF, VLOOKUP, SUMIFS, etc.) prennent désormais entièrement ou partiellement en charge les tableaux dynamiques.
Il existe une rétrocompatibilité : si vous ouvrez un classeur avec des tableaux dynamiques dans une ancienne version d'Excel, ils se transformeront en formules matricielles (entre accolades) et continueront à travailler dans « l'ancien style ».

Trouvé un certain nombre inconvénients:

Vous ne pouvez pas supprimer des lignes, des colonnes ou des cellules individuelles d'un tableau dynamique, c'est-à-dire qu'il vit comme une seule entité.
Vous ne pouvez pas trier un tableau dynamique de la manière habituelle via Données – Tri (Données — Trier). Il existe maintenant une fonction spéciale pour cela. QUALITÉ (TRIER).
Une plage dynamique ne peut pas être transformée en tableau intelligent (mais vous pouvez créer une plage dynamique basée sur un tableau intelligent).

Bien sûr, ce n'est pas la fin, et je suis sûr que Microsoft continuera à améliorer ce mécanisme à l'avenir.

Où puis-je télécharger ?

Et enfin, la question principale 🙂

Microsoft a annoncé et montré pour la première fois un aperçu des tableaux dynamiques dans Excel en septembre 2018 lors d'une conférence Enflammer. Au cours des mois suivants, de nouvelles fonctionnalités ont été testées et rodées en profondeur, d'abord sur ~~chats~~ des employés de Microsoft lui-même, puis sur des testeurs bénévoles du cercle des Office Insiders. Cette année, la mise à jour qui ajoute des tableaux dynamiques a commencé à être progressivement déployée auprès des abonnés Office 365 réguliers. Par exemple, je ne l'ai reçu qu'en août avec mon abonnement Office 365 Pro Plus (mensuel ciblé).

Si votre Excel n'a pas encore de tableaux dynamiques, mais que vous voulez vraiment travailler avec, alors il y a les options suivantes :

Si vous avez un abonnement Office 365, vous pouvez simplement attendre que cette mise à jour vous parvienne. La rapidité avec laquelle cela se produit dépend de la fréquence à laquelle les mises à jour sont livrées à votre bureau (une fois par an, une fois tous les six mois, une fois par mois). Si vous avez un PC d'entreprise, vous pouvez demander à votre administrateur de configurer des mises à jour à télécharger plus souvent.
Vous pouvez rejoindre les rangs de ces volontaires de test Office Insiders - vous serez alors le premier à recevoir toutes les nouvelles fonctionnalités et fonctions (mais il y a un risque de bogue accru dans Excel, bien sûr).
Si vous n'avez pas d'abonnement, mais une version autonome en boîte d'Excel, alors vous devrez attendre la sortie de la prochaine version d'Office et d'Excel en 2022, au moins. Les utilisateurs de ces versions ne reçoivent que des mises à jour de sécurité et des corrections de bogues, et tous les nouveaux « goodies » sont désormais réservés aux abonnés Office 365. Triste mais vrai 🙂

Dans tous les cas, lorsque des tableaux dynamiques apparaissent dans votre Excel - après cet article, vous serez prêt pour cela 🙂

Que sont les formules matricielles et comment les utiliser dans Excel
Sommation de fenêtre (plage) à l'aide de la fonction OFFSET
3 façons de transposer un tableau dans Excel