Les tables dynamiques dans Excel sont les tables où lorsqu'une nouvelle valeur y est insérée, la table ajuste sa taille par elle-même, pour créer une table dynamique dans Excel, nous avons deux méthodes différentes, la fois est qui crée une table des données à partir du la section table tandis qu'une autre consiste à utiliser la fonction de décalage, dans les tableaux dynamiques, les rapports et les tableaux croisés dynamiques changent également à mesure que les données de la table dynamique changent.
Tables dynamiques dans Excel
Dynamique en soi, cela signifie un système de processeur caractérisé par un changement constant ou un changement d'activité. De même, dans Excel, lorsque nous créons des listes ou des données dans un classeur et en faisons un rapport, mais si nous ajoutons des données ou en supprimons une ou déplaçons ou modifions les données, l'ensemble du rapport peut être inexact. Excel a une solution pour cela sous forme de tableaux dynamiques.
Maintenant, la question se pose de savoir pourquoi avons-nous besoin de Dynamic Range ou Dynamic Tables. La réponse à cela est que chaque fois qu'une liste ou une plage de données est mise à jour ou modifiée, il n'est pas certain que le rapport sera modifié en fonction de la modification des données.
Fondamentalement, les tables dynamiques présentent deux avantages principaux:
- Une plage dynamique s'étendra ou se contractera automatiquement en fonction du changement de données.
- Les tableaux croisés dynamiques basés sur le tableau dynamique dans Excel peuvent être automatiquement mis à jour lorsque le pivot est actualisé.
Comment créer des tableaux dynamiques dans Excel?
Il existe deux méthodes de base pour utiliser les tables dynamiques dans Excel - 1) Utilisation des TABLES et 2) Utilisation de la fonction OFFSET.
Vous pouvez télécharger ce modèle Excel de tableau dynamique ici - Modèle Excel de tableau dynamique# 1 - Utilisation de tableaux pour créer des tableaux dynamiques dans Excel
En utilisant des tableaux, nous pouvons créer une table dynamique dans Excel et baser un pivot sur la table dynamique.
Exemple
Nous avons les données suivantes,
Si nous créons un tableau croisé dynamique avec cette plage de données normale de A1: E6, si nous insérons une donnée dans la ligne 7, elle ne sera pas reflétée dans le tableau croisé dynamique.
Nous allons donc d'abord faire une plage dynamique.
# 1 - Sélectionnez les données, c'est-à-dire A1: E6.
# 2 - Dans l'insert, cliquez sur Tabulation sur Tables sous la section Tables.
# 3 - Une boîte de dialogue apparaît.
Comme nos données ont des en-têtes, n'oubliez pas de cocher la case «Ma table a des en-têtes» et cliquez sur OK.
# 4 - Notre gamme dynamique est créée.
# 5 - Sélectionnez les données et dans l'onglet Insertion sous la section des tableaux Excel, cliquez sur les tableaux croisés dynamiques.
# 6 - Comme nous avons créé le tableau, il prend une plage comme tableau 2. Cliquez sur OK et dans les tableaux croisés dynamiques, faites glisser le produit dans les lignes et les ventes dans les valeurs.
# 7 - Maintenant dans la feuille où nous avons notre tableau, insérez une autre donnée en 7e
Dans le tableau croisé dynamique, actualisez le tableau croisé dynamique.
Notre tableau croisé dynamique a automatiquement mis à jour les données du produit 6 dans le tableau croisé dynamique.
# 2 - Utilisation de la fonction OFFSET pour créer un tableau dynamique dans Excel
Nous pouvons également utiliser la fonction OFFSET pour créer des tables dynamiques dans Excel. Jetons un coup d'œil à un tel exemple.
Exemple
J'ai une liste de prix pour mes produits que j'utilise pour mes calculs,
Sélectionnez les données et donnez-leur un nom
Maintenant, chaque fois que je ferai référence à la liste de prix de l'ensemble de données, cela me mènera aux données de la gamme B2: C7 qui contient ma liste de prix. Mais si je mets à jour une autre ligne avec les données, cela me mènera toujours à la plage B2: C7 car notre liste est statique.
Nous utiliserons la fonction Offset pour rendre la plage de données dynamique.
# 1 - Sous l'onglet Formules dans la plage définie, cliquez sur Nom défini et une boîte de dialogue apparaît.
# 2 - Dans la zone de nom, tapez n'importe quel nom, j'utiliserai le PriceA. La portée est le classeur actuel et actuellement, il fait référence à la cellule actuelle sélectionnée qui est B2.
Dans Se réfère à écrire la formule suivante,
= offset (Feuille2! $ B $ 2,1,0, counta (Feuille2! $ B: $ B) -1,2)
= décalage (
# 3 - Sélectionnez maintenant la cellule de départ qui est B2,
# 4 - Maintenant, nous devons taper 1,0 car cela comptera le nombre de lignes ou de colonnes à parcourir
# 5 - Maintenant, nous en avons besoin pour compter les données de la colonne B et l'utiliser comme nombre de lignes, utilisez donc la fonction COUNTA et sélectionnez la colonne B.
# 6 - Comme nous ne voulons pas que la première ligne qui est l'en-tête du produit soit comptée, alors (-) 1 à partir de celle-ci.
# 7 - Maintenant, le nombre de colonnes sera toujours de deux alors tapez 2 et cliquez sur OK.
# 8 - Cette plage de données ne sera pas visible par défaut, donc pour voir cela, nous devons cliquer sur Gestionnaire de noms sous l'onglet Formule et sélectionner Produit,
# 9 - Si nous cliquons sur se réfère à cela montre la plage de données,
# 10 - Ajoutez maintenant un autre produit dans le tableau Produit 6.
# 11 - Cliquez maintenant sur Table des produits dans le gestionnaire de noms, cela fait également référence aux nouvelles données insérées,
C'est ainsi que nous pouvons utiliser la fonction Offset pour créer des tableaux dynamiques.
Choses à retenir
- Les tableaux croisés dynamiques basés sur la plage dynamique sont automatiquement mis à jour lorsqu'ils sont actualisés.
- L'utilisation de la fonction de décalage dans les noms définis peut être vue à partir du gestionnaire de noms dans l'onglet formule.
