Évitez VLOOKUP en utilisant le modèle de données. Donc, vous avez deux tables qui doivent être jointes avec VLOOKUP avant de pouvoir créer un tableau croisé dynamique. Si vous avez Excel 2013 ou plus récent sur un PC Windows, vous pouvez désormais le faire simplement et facilement.
Supposons que vous disposez d'un ensemble de données contenant des informations sur les produits, les clients et les ventes.
Le service informatique a oublié de mettre le secteur là-dedans. Voici une table de recherche qui mappe le client au secteur. Il est temps pour une RECHERCHEV, non?
Il n'est pas nécessaire d'effectuer des RECHERCHEV pour joindre ces ensembles de données si vous avez Excel 2013 ou Excel 2016. Ces deux versions d'Excel ont intégré le moteur Power Pivot dans le noyau Excel. (Vous pouvez également le faire à l'aide du complément Power Pivot pour Excel 2010, mais il y a quelques étapes supplémentaires.)
Dans l'ensemble de données d'origine et dans la table de recherche, utilisez Accueil, Mettre en forme comme table. Sous l'onglet Outils de tableau, renommez la table de Table1 en quelque chose de significatif. J'ai utilisé des données et des secteurs.
Sélectionnez une cellule dans le tableau de données. Choisissez Insertion, Tableau croisé dynamique. À partir d'Excel 2013, il existe une zone supplémentaire Ajouter ces données au modèle de données que vous devez sélectionner avant de cliquer sur OK.
La liste Champs du tableau croisé dynamique apparaît avec les champs de la table de données. Choisissez Revenu. Étant donné que vous utilisez le modèle de données, une nouvelle ligne apparaît en haut de la liste, proposant Actif ou Tout. Cliquez sur Tout.
Étonnamment, la liste des champs de tableau croisé dynamique propose toutes les autres tables du classeur. C'est révolutionnaire. Vous n'avez pas encore effectué de RECHERCHEV. Développez le tableau Secteurs et choisissez Secteur. Deux choses se produisent pour vous avertir qu'il y a un problème.
Tout d'abord, le tableau croisé dynamique apparaît avec le même numéro dans toutes les cellules.
L'avertissement le plus subtil est peut-être qu'une boîte jaune apparaît en haut de la liste des champs de tableau croisé dynamique indiquant que vous devez créer une relation. Choisissez Créer. (Si vous êtes dans Excel 2010 ou 2016, tentez votre chance avec la détection automatique.)
Dans la boîte de dialogue Créer une relation, vous avez quatre menus déroulants. Choisissez Données sous Table, Client sous Colonne (étranger) et Secteurs sous Table associée. Power Pivot remplira automatiquement la colonne correspondante sous la colonne associée (principale). Cliquez sur OK.
Le tableau croisé dynamique résultant est un mashup des données d'origine et de la table de recherche. Aucun VLOOKUP requis.
Regarder la vidéo
- À partir d'Excel 2013, la boîte de dialogue Tableau croisé dynamique propose le modèle de données
- Ceci est le mot de code pour Power Pivot Engine
- Pour utiliser le modèle de données, créez une table Ctrl + T à partir de chaque table du classeur
- Créer un tableau croisé dynamique à partir du premier tableau
- Dans la liste des champs du tableau croisé dynamique, passez de Actif à Tout
- Choisissez un champ dans la table de recherche
- Créez la relation ou Détectez automatiquement
- Auto-Detect n'était pas là en 2013
- Merci à Colin Michael et Alejandro Quiceno pour avoir suggéré Power Pivot en général.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2014 - Éliminez VLOOKUP!
Podcasting ce livre entier, cliquez sur le «i» dans le coin supérieur droit pour la playlist!
Hé, bienvenue à nouveau sur le netcast, je suis Bill Jelen, cela s'appelle en fait Eliminate VLOOKUP avec le modèle de données! Maintenant, je m'excuse, c'est Excel 2013 et plus récent, si vous êtes de retour dans Excel 2010, vous devez aller télécharger le complément Power Pivot, qui est bien sûr gratuit en 2010. Nous avons donc notre ensemble de données principal, il y a un champ Client ici, puis j'ai un petit tableau qui mappe le client au secteur, j'ai besoin de créer un revenu total par secteur, non? Ceci est une RECHERCHEV, faites simplement une RECHERCHEV, mais bon, grâce à Excel 2013, nous n'avons pas à faire une RECHERCHEV! J'ai fait les deux dans un tableau, et sur les outils de tableau, Design, je renomme les tableaux, j'appelle celui-ci Secteurs, et j'appelle celui-ci Data, pour en faire un tableau, choisissez simplement une cellule, appuyez sur Ctrl + T. Donc, si nous avons des titres et des nombres, lorsque vous appuyez sur Ctrl + T,ils demandent «Où sont les données de votre tableau?», Mon tableau a des en-têtes, puis ils l'appellent Table3, vous l'appelez autre chose. D'accord, c'est comme ça que j'ai créé ces deux tables, je vais me débarrasser de cette table, d'accord.
Donc, pour que cette astuce fonctionne, toutes les données doivent vivre dans des tables. Nous allons à l'onglet Insertion, choisissez Tableau croisé dynamique, et juste en bas ici, en bas, ajoutez ces données au modèle de données. Cela semble très anodin, non? Il n'y a rien de tel que le point clignotant qui dit "Hé, ça te permettra de faire des choses incroyables!" Et ce qu'ils disent ici, ce qu'ils essaient de ne pas dire, c'est que - Oh, au fait, chaque copie d'Excel 2013 a le moteur Power Pivot derrière. Vous savez, si vous êtes dans Office 365, vous payez 10 $ par mois, et ils veulent que vous payiez 12 $ ou 15 $ par mois pour obtenir Power Pivot, les deux ou cinq dollars supplémentaires. Eh bien, hé, chut, ne le dites pas, vous avez déjà la plupart de Power Pivot dans Excel 2013. D'accord, alors je clique sur OK, prend un peu plus de temps pour charger le modèle de données, d'accord, mais c'est OK, et tout de suite Ici,dans les champs de tableau croisé dynamique, j'ai une liste de tous les champs. Donc, je veux montrer Revenue, certes, mais ce qui est différent, c'est ici avec Active and All. Lorsque je choisis Tout, j'obtiens toutes les tables du classeur. D'accord, je vais donc aux secteurs, et j'ai dit que je voulais mettre le secteur dans la zone des lignes. Maintenant, au départ, le rapport va être faux, voir les 6,7 millions tout en bas, et cet avertissement jaune ici dira que vous devez créer une relation.et cet avertissement jaune ici vous dira que vous devez créer une relation.et cet avertissement jaune ici vous dira que vous devez créer une relation.
D'accord maintenant, en 2010 avec Power Pivot, il aurait simplement offert AutoDetect, en 2013, ils ont retiré AutoDetect et en 2016, ils ont ramené AutoDetect, d'accord? Je devrais vous montrer à quoi ressemble CRÉER, mais quand je clique sur ce bouton CRÉER, oh ouais, c'est tout, d'accord, bien. Donc, à partir de notre première table Données, j'ai un champ appelé Client, de la table associée Secteurs, j'ai un champ appelé Client, puis vous cliquez sur OK, d'accord. Mais laissez-moi vous montrer à quel point la détection automatique est cool, si vous êtes en 2016, là-bas, ils l'ont compris, à quel point est-ce génial, non? Vous n'avez pas à vous soucier de VLOOKUP, et la virgule tombe à la fin, si VLOOKUP vous fait mal à la tête, vous allez adorer le modèle de données. A pris ces deux tableaux, les a réunis, vous savez, comme Access le ferait, je suppose, et a créé un tableau croisé dynamique, absolument incroyable.Vérifiez donc le modèle de données la prochaine fois que vous devrez effectuer une RECHERCHEV entre deux tables. Eh bien ceci et tous les 40 autres conseils sont dans le livre, cliquez sur ce «i» dans le coin supérieur droit. Vous pouvez acheter le livre, avoir une référence croisée complète à toute cette série de vidéos, tout le mois d'août, tout le mois de septembre, diable, nous pourrions même reporter en octobre pour que tout soit fait.
D'accord, récapitulez aujourd'hui: à partir d'Excel 2013, la boîte de dialogue Tableau croisé dynamique propose quelque chose qui s'appelle le modèle de données, c'est le mot de code pour le moteur Power Pivot. Avant de créer vos tableaux croisés dynamiques, faites Ctrl + T pour créer un tableau à partir de chaque classeur, j'ai pris le temps supplémentaire de nommer chacun d'eux. Créez un tableau croisé dynamique à partir de la première table, puis dans la liste des champs, montez en haut et passez de Actif à Tout. Choisissez un champ dans la table de recherche, puis il vous avertira que vous devez soit créer une relation, soit AutoDetect, en 2013, vous devez cliquer sur CRÉER. Mais c'est quoi, 4 clics pour le créer, 5 si vous comptez le bouton OK, donc vraiment, vraiment facile à faire.
D'accord, Colin, Michael et Alejandro Quiceno ont suggéré Power Pivot en général pour les livres, merci à eux, merci à vous pour votre visite, nous vous verrons la prochaine fois pour un autre netcast de!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2014.xlsx
