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.
Pour placer le produit sur le dessus, utilisez =TRANSPOSE(SORT(UNIQUE(B2:B564)))en J1.
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.
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.
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.
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.
Regarder la vidéo
Télécharger le fichier Excel
Pour télécharger le fichier Excel: remplacez-un-tableau-pivot-par-3-dynamic-array-formulas.xlsx
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Gardez vos données à proximité et vos feuilles de calcul plus proches"
Jordan Goldmeier
