Remplacer un tableau croisé dynamique par 3 formules de tableau dynamique - Astuces Excel

Cela fait huit jours que les formules de matrice dynamique ont été annoncées lors de la conférence Ignite 2018 à Orlando. Voici ce que j'ai appris:

Les tableaux modernes ont été annoncés à Ignite le 24 septembre 2018 et officiellement appelés Dynamic Arrays.
J'ai écrit un livre électronique de 60 pages avec 30 exemples d'utilisation, et je le propose gratuitement jusqu'à la fin de 2018.
Le déploiement va être beaucoup plus lent que quiconque ne le souhaite, ce qui est frustrant. Pourquoi si lent? L'équipe Excel a apporté des modifications au code de Calc Engine qui est stable depuis 30 ans. Une préoccupation particulière: avec des compléments qui injectent des formules dans Excel qui utilisent par inadvertance une intersection implicite. Ces compléments seront interrompus si Excel renvoie maintenant une plage Spill.
Il existe une nouvelle façon de faire référence à la plage renvoyée par un tableau: =E3#mais elle n'a pas encore de nom. Le # s'appelle l' opérateur de formule renversée . Que pensez-vous d'un nom comme Spill Ref (suggéré par Excel MVP Jon Acampora) ou The Spiller (suggéré par MVP Ingeborg Hawighorst)?

En tant que co-auteur de Pivot Table Data Crunching, j'aime un bon tableau croisé dynamique. Mais que faire si vous avez besoin de mettre à jour vos tableaux croisés dynamiques et que vous ne pouvez pas faire confiance au responsable de votre responsable pour qu'il clique sur Actualiser? La technique décrite aujourd'hui propose une série de trois formules pour remplacer un tableau croisé dynamique.

Pour obtenir une liste triée de clients uniques, utilisez =SORT(UNIQUE(E2:E564))dans I2.

Une formule de tableau dynamique pour créer des clients sur le côté du rapport

Pour placer le produit sur le dessus, utilisez =TRANSPOSE(SORT(UNIQUE(B2:B564)))en J1.

Pour la zone des colonnes, utilisez TRANSPOSE

Voici un problème: vous ne savez pas quelle sera la taille de la liste des clients. Vous ne savez pas quelle sera la largeur de la liste de produits. Si vous faites référence à I2 #, le Spiller se référera automatiquement à la taille actuelle du tableau retourné.

La formule pour retourner la zone des valeurs de la table de pivotement est une formule unique de réseau à J2: =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

En anglais, cela indique que vous souhaitez ajouter les revenus de G2: G564 où les clients dans E correspondent au client de la ligne actuelle de la formule matricielle I2 et les produits dans B correspondent à la colonne actuelle de la formule matricielle dans J1.

C'est une formule douce

Et si les données sous-jacentes changent? J'ai ajouté un nouveau client et un nouveau produit en modifiant ces deux cellules dans la source.

Modifier certaines cellules dans les données d'origine

Le rapport est mis à jour avec de nouvelles lignes et de nouvelles colonnes. La référence Array-Range de I2 # et J1 # gère la ligne et la colonne supplémentaires.

Votre rapport croisé se développe automatiquement avec les nouvelles données

Pourquoi le SUMIFS fonctionne-t-il? Il s'agit d'un concept dans Excel appelé diffusion. Si vous avez une formule qui fait référence à deux tableaux:

Le tableau un est (27 lignes) x (1 colonne)
Le tableau deux est (1 ligne) x (3 colonnes)
Excel renverra un tableau résultant aussi haut et large que la partie la plus haute et la plus large des tableaux référencés:
Le résultat sera (27 lignes) x (3 colonnes).
C'est ce qu'on appelle des tableaux de diffusion.