Maîtriser les tableaux Excel : comment les créer et les utiliser comme un pro

1. Introduction: m'appelle Al Chen, je suis architecte de solutions chez Coda, un outil qui réunit le meilleur des feuilles de calcul, des documents, des applications. Je suis un utilisateur de puissance Excel depuis plus de 10 ans, enseigné plus de 10 000 étudiants en MBA. Ces jours-ci, je passe mon temps à créer des tutoriels et des vidéos sur les feuilles de calcul Excel et d'autres outils connexes. Aujourd'hui, je vais vous apprendre à utiliser des tables Excel une fonctionnalité relativement inconnue dans Excel. Une fois que vous apprendrez la puissance des tables Excel, vous ne regarderez jamais en arrière. Je vais vous apprendre comment créer des tableaux Excel, comment créer des références de structure, et aussi comment formater votre table, sorte qu'ils soient très beaux et jolis pour votre équipe et pour vos collègues. Je suis vraiment excité pour les étudiants de suivre ce cours parce qu' une fois que vous apprendrez les fonctionnalités et les fonctions de base des tables Excel, vous verrez la puissance qu'il débloque pour le reste de vos tableaux de bord, vos analyses de données, vos tâches, et d'autres choses liées aux feuilles de calcul Excel. Juste en apprenant cette fonctionnalité simple, il va, espérons-le, changer la façon dont vous approchez Excel à l'avenir. Une fois que vous aurez fini de suivre ce cours, j'espère que vous participerez aux projets de la classe. Il s'agit d'analyser les données sur les retards de vol du ministère des Transports à partir de 2015, transformer en un tableau Excel, de les transformer en un tableau Excel, ainsi que d'analyser ces données en créant un tableau de bord, créant un rapport pour l'utilisateur final, et en écrivant également quelques informations sur ce que vous pouvez recueillir à partir de l'analyse des données après avoir utilisé des tables Excel. J' espère vraiment que vous publierez vos résultats sur les données dans la galerie du projet. Commençons tout de suite à trouver les avantages des tables Excel. 2. Les avantages des tableaux Excel: Dans cette leçon, je vais vous montrer les avantages des tables Excel faisant une démonstration rapide d'une table que j'ai construite dans le classeur de classe, où je vais vous montrer comment filtrer et trier, ajouter une ligne de résumé à vos données, et aussi ajouter de nouvelles données à la table Excel. J' explorerai ces sujets plus en profondeur plus tard dans la classe. Nous sommes en train de regarder une table Excel en ce moment. À l'étape 1, nous allons simplement cliquer sur le tableau ci-dessous et faire défiler la première ligne d'en-tête, qui est la ligne 11. fur et à mesure que je fais défiler, vous remarquerez que les colonnes deviendront collantes dans la barre d'outils supérieure ou dans le ruban. C' est l'une des fonctionnalités intéressantes des tables Excel où vous pouvez voir les colonnes ici que vous faites défiler. C' est comme geler votre première ligne d'une manière si vous l'avez déjà fait dans vos fichiers Excel. Je peux aussi rapidement filtrer et trier mes données en cliquant sur le « Dropdown » ici dans l'une de ces colonnes, et je peux cliquer sur « Ascendant » ou « Décroissant » et filtrer les choses comme vous l'avez fait auparavant dans vos fichiers Excel. Il s'agit de l'une des fonctionnalités par défaut fournies avec votre tableau Excel. Disons que je veux faire une ligne de résumé à l'étape 3. Je peux cliquer à l'intérieur de ma table, cliquer sur l'onglet « Tableau » dans le ruban, puis vous remarquez que les cases à cocher de la ligne totale décochent par défaut. Si je clique dessus, vous verrez à la ligne 39, il y a une ligne récapitulative que vous pouvez essentiellement résumer vos données par moyennes, sommes, comptes, toutes ces mesures typiques. Je vais décocher ça pour l'instant. L' une des fonctionnalités les plus puissantes avec les tables est répertoriée ici à l'étape 4. Je veux que vous essayez d'ajouter une nouvelle ligne de données à la table. Si je clique juste ici et appuie sur « Entrée » et commence à taper quelque chose comme S510, et c'est un film, si je veux taper une autre ligne S200, vous remarquez que le tableau ajoute automatiquement le formatage aux nouvelles lignes de données, et ces lignes de données sont incluses dans la table en tant qu'objet. Maintenant, une chose à laquelle je veux que vous pensiez est d'imaginer avoir à créer cette table en utilisant une mise en forme conditionnelle. Vous devriez faire beaucoup de formules et de fonctions vraiment intéressantes pour obtenir ces lignes bandées pour obtenir de la couleur comme ça. La bonne chose à propos des tables Excel est qu'Excel prend soin de tout ce formatage conditionnel pour vous, sorte que lorsque vous ajoutez de nouvelles lignes de données, ils obtiennent automatiquement un code couleur pour que les données se démarquent. Enfin, je veux que vous essayez de mettre une formule, n'importe quelle formule dans la colonne K, où il est dit, Ma formule. Je vais écrire des égaux frapper une cellule dans peut-être la même rangée ici, et vous remarquez qu'il vous donne ces références, la colonne, au lieu d'un type de référence traditionnel cuirassé, nous avons A1 et B5. C' est un peu différent de ce que vous verrez, mais nous parlerons plus de ce que c'est et pourquoi il est vraiment puissant dans une future leçon sur les références structurées. Juste pour résumer, nous avons parcouru certains des avantages et fonctionnalités des tableaux Excel, nous vous avons montré comment rendre vos tables un peu moins sujettes aux erreurs en ayant cette mise en forme conditionnelle qui s'applique facilement, et aussi, nous avons parcouru très rapidement comment vous pouvez filtrer et trier les tables. Un élément clé de cette leçon est de commencer à jouer avec la table dans le classeur de classe. Dans la leçon suivante, nous allons parler de la façon de créer votre propre table à partir de zéro afin que vous puissiez commencer à faire quelques analyses. 3. Créer un tableau Excel: Nous allons créer une table Excel à partir de zéro en utilisant une liste de restaurants dans le classeur de classe. Commençons. Nous avons ici une liste de restaurants avec l'emplacement, les ventes, le statut de franchise, et vous pouvez en savoir plus sur ce jeu de données en cliquant sur cette URL que j'ai ici dans cette cellule. Mais nous allons parcourir étape par étape comment créer une table et utiliser certaines des fonctionnalités de ce tableau. Définissez un, nous allons cliquer sur « Insérer » puis sur « Table », et nous allons nommer cette table, Restaurants. Je veux que vous sélectionniez d'abord toutes vos données. C' est environ 50 rangées. Ensuite, allez à Insérer une table. Par défaut, il va dire que ma table a des en-têtes. Normalement, vous voulez garder cela comme vérifié parce que notre table a des en-têtes, vous pouvez voir ici dans la ligne 10. Je vais cliquer sur « OK », et c'est ainsi qu'Excel crée une table pour vous par défaut. Vous remarquez que vous obtenez les lignes de couleur bandées, et vous obtenez également ces listes déroulantes pour un filtrage et un tri rapides. Vous voyez quels sont les avantages de la table lorsque vous appuyez sur Entrée et que vous commencez à taper à nouveau un nouveau restaurant et dites que c'est Arby, Excel développera automatiquement cette table pour inclure cette nouvelle ligne de données. Si j'ajoute de nouvelles lignes de données, Taco Bell, vous verrez comment le tableau Excel codera automatiquement les lignes pour vous afin que vous n'ayez pas à vous soucier de la mise en forme conditionnelle comme vous le feriez normalement avec un liste des données. Maintenant, je vais supprimer certaines données de la table et essayer de naviguer autour des cellules vides à l'étape 2. Supprimons juste quelques-unes de ces lignes. Vous remarquez que tout cela a disparu, mais Excel pense toujours que ces lignes sont probablement table. Maintenant, avant de supprimer ces lignes, je veux que vous naviguiez en appuyant sur « Commande » ou « Contrôle » avec les touches fléchées. Vous remarquez que le comportement est un peu différent de ce que vous attendez. Vous pouvez aller aux bords de la table, au lieu de quand il y a une nouvelle cellule avec une valeur. Je vais juste sélectionner ces lignes, cliquez avec le bouton droit de la souris, puis appuyez sur « Supprimer », supprimer les lignes de tableau, et ces lignes seront supprimées de ma table afin qu'elles ne soient pas comptées comme des lignes vides. Nous avons supprimé les lignes vides, et maintenant je veux que vous fassiez d'autres options pour les tables Excel comme nous pouvons convertir ce tableau en un tableau croisé dynamique si nous le voulons en cliquant sur « Tableau » dans le ruban, puis en cliquant sur « Résumer avec tableau croisé dynamique ». Je vais utiliser la feuille de calcul existante dans ce cas et la plage va être la cellule J14. Une fois que je clique sur « OK » pour le tableau croisé dynamique, vous verrez que cela est créé ici, donc nous avons la possibilité de créer un tableau croisé dynamique si nous le voulons. Si à un moment donné vous voulez convertir votre table Excel à une plage normale de données, tout ce que vous avez à faire est de cliquer à nouveau sur le ruban « Table », et l'une des options de la barre d'outils est convertie en plage. Si je clique sur « Convertir en plage », Excel supprimera automatiquement les listes déroulantes pour moi, mais la mise en forme conditionnelle restera sur le tableau, sorte que vous pouvez supprimer cela en effaçant le formatage si vous le souhaitez. Je vais annuler cette conversion en plage. Une dernière chose que je veux faire, j'ai oublié de mentionner est à l'étape 1 est que nous voulons renommer cette table en restaurants en haut à gauche. Si je clique sur une cellule dans le tableau et que je clique à nouveau sur l'onglet « Table » dans le ruban, vous remarquez par défaut, Excel m'a donné un nom de table de Table4. Je vais renommer cela en Restaurants et appuyer sur « Entrée » de sorte qu'à l'avenir, si jamais je veux référencer cette table, je peux simplement l'appeler Restaurants au lieu de Table4 ou tout ce que Excel me donne par défaut. Dans cette leçon, nous avons parcouru certains des avantages de l'utilisation d'un tableau Excel et comment créer un tableau Excel, comment nous pouvons supprimer des lignes et appliquer une mise en forme rapide aux tableaux. Dans la leçon suivante, nous allons examiner plus en détail comment filtrer, trier et créer des colonnes calculées sur votre table Excel. 4. Filtrer et trier un tableau Excel: Nous allons plonger plus profondément dans le filtrage et le tri de votre table Excel, ainsi que sur la façon d'ajouter une nouvelle formule à votre table, appelée colonnes calculées. Allons plonger dedans. Nous avons la même table de restaurants aussi qu'une table Excel. Si je clique sur « Table » ici, vous verrez que cette table s'appelle Rest2. Comme vous l'avez vu précédemment, vous pouvez trier par de ces colonnes en cliquant sur « Ascendant » ou « Décroissant », et je peux simplement ajouter ce genre très rapidement à n'importe quelle colonne que je veux. Je veux que vous maintenant à l'étape deux, est d'ajouter une ligne de résumé à ce tableau et de trouver les ventes moyennes, qui est dans la colonne D de notre liste de restaurants. Si vous allez à Tableau, cliquez sur « Total Row' et vous remarquez en bas de la ligne 61 nous obtenons cette ligne bleu foncé pour nos totaux. Dans la colonne des ventes, je veux que vous cliquiez sur la liste déroulante et cliquez simplement sur « Moyenne ». Maintenant, nous pouvons juste trouver le chiffre d'affaires moyen dans tous ces restaurants, soit environ 34 millions de dollars. C' est une façon vraiment agréable d'obtenir rapidement le nombre moyen de somme, tout comme vous le pourriez dans un tableau croisé dynamique traditionnel, mais dans un tableau Excel, il est géré à travers cette toute dernière ligne au bas de votre table. À la troisième étape, nous allons créer une trancheuse basée sur la colonne Franchise. Maintenant trancheuse comme une belle façon visuellement agréable pour votre équipe, vos collègues, interagir rapidement avec votre table Excel, je vais cliquer sur mon « Tableau » ici, assurez-vous que vous êtes sur l'onglet Table dans le ruban et cliquez sur « Insérer Trancheuse ». Vous obtiendrez ce menu ici où vous pouvez sélectionner la colonne sur laquelle vous souhaitez découper. Je vais juste cliquer sur « Franchise », cliquer sur « OK », et vous obtiendrez ce petit menu où vous pouvez choisir Oui ou Non. Vous remarquez que lorsque vous cliquez sur Oui ou Non, cette colonne Franchise est automatiquement filtrée à cette valeur spécifique. C' est une très belle fonctionnalité à avoir avec vos trancheuses. Si je clique sur cette option ici, cette option cochée dans la trancheuse, je peux maintenant sélectionner plusieurs options dans ma boîte de trancheuse. Je vais juste supprimer la trancheuse pour l'instant, appuyer sur « Supprimer » dessus, et revenir à ma vue de table principale ici. Enfin, nous allons écrire une formule dans la première colonne vide à droite, qui est la colonne I, pour calculer les ventes moyennes par magasin. Laissez-moi juste annuler ce filtre ici. Si je clique sur la cellule I10 et écris des ventes moyennes, vous remarquez qu'Excel ajoute automatiquement cette colonne en tant que nouvelle colonne à ma table, et si je commence à écrire égal dans la cellule I11, égal aux ventes, qui est la colonne D divisée par la colonne F, qui est emplacements, regardez la formule qui est créée, ventes divisées par emplacements, nous allons appuyer sur « Entrée » et voir ce qui se passe. La formule est automatiquement remplie jusqu'à la dernière ligne de ma table, donc je n'ai pas besoin de double-cliquer ou de faire glisser ma formule jusqu'au bas. formules des tables sont automatiquement appliquées à la colonne entière en une seule fois, sorte que vous n'avez pas à vous soucier de toutes ces étapes manuelles supplémentaires pour créer la formule. Vous avez peut-être fait quelque chose de similaire à ceci, qui dans une table Excel, il est appelé une colonne calculée. Vous avez peut-être fait quelque chose de similaire dans un tableau croisé dynamique, qui est appelé un champ calculé. Nous faisons simplement le [inaudible] dans une table Excel. Revenir un peu, si vous deviez construire cette formule et construire cette table à partir de zéro, et pour obtenir également les données sommaires ici dans cette ligne 61, vous devrez faire une liste compliquée de formules. Vous pouvez utiliser une formule COUNT A pour sélectionner toutes les cellules qui ont une valeur vide. La bonne chose à propos de cette ligne totale est qu'elle est automatiquement ajoutée au bas de la table, vous n'avez pas à vous soucier d'ajouter toutes ces formules supplémentaires qui rendent juste votre vie un peu plus difficile à faire. Nous avons parlé de la façon d'effectuer plus de filtrage, tri et de création de colonnes calculées dans une table Excel. Maintenant, la formule de tableau Excel que vous avez vu dans la colonne pour les ventes moyennes semble difficile, donc nous allons approfondir cette question dans la prochaine leçon sur les références structurées. 5. Formules dans les références structurées: Nous allons plonger plus profondément dans les références structurées. Je vais parcourir différentes façons de créer des formules avec des références structurées dans vos tables Excel. Une remarque rapide sur ces références structurées est que lorsque vous êtes dans la table elle-même, comme nous l'avons fait avec les ventes moyennes, vous référencez la colonne en plaçant cette esperluette plus le nom de la colonne entouré de parenthèses. C' est différent de quand vous faites égal, cellule K5 ou quelque chose comme ça, ou K9. Dans les tables Excel, vous référencez la colonne entière. C'est pourquoi, lorsque vous écrivez une formule, la formule se remplit à elle seule à travers toute la colonne. Si vous êtes en dehors de la table, comme nous l'avons fait ici à l'étape 1, nous allons écrire des égaux. Je vais encore faire référence à la colonne des ventes. Vous remarquez que je dois d'abord mettre le nom de la table Rest3, suivi de la référence structurée, qui est @Sales. Cela ne me donnera que la valeur pour cette ligne, même si elle fait référence à la colonne entière. C' est une chose à laquelle vous devez vous habituer lorsque vous référencez des cellules et des lignes dans votre table. Juste pour confirmer, je vais cliquer sur « Table » ici, en effet appelé Rest3. Enfin, à l'étape 2, je veux que vous fassiez un exercice supplémentaire avec référencement de cette table est écriture égale et essayez de sélectionner la table entière avec les en-têtes. Vous remarquez qu'il est dit Rest3 #All. Cela indique à Excel que vous référencez l'ensemble de la table à la fois. Si vous voulez seulement référencer les données, vous pouvez simplement écrire des égaux et simplement sélectionner tout sans l'en-tête et vous allez juste voir, il est dit Rest3, mais vous pouvez aussi écrire [#Data et vous obtiendrez le même résultat. Cela me donnera une erreur car je représente la table dans une cellule donc cela ne fonctionnera pas. Je veux que vous écriviez une formule SUM sur la colonne emplacements en dehors de la table en utilisant ce que nous venons d'apprendre avec des formules et des remplissages automatiques avec des références de table. Écrivons la formule ici, équivaut à somme. Le nom de notre table est Rest3. Je peux cliquer sur l'une de ces références structurées ici, Rest3 et essayons de référencer seulement la colonne emplacements. Je peux écrire le crochet gauche. À la minute où vous écrivez un crochet gauche, il vous donne la possibilité de sélectionner l'une des colonnes de votre tableau. Je peux maintenant cliquer sur « Emplacement » cliquez sur parenthèse droite. Ce que je fais ici, c'est que je vais avoir besoin d'écrire emplacements avec un S pour référencer cette colonne, colonne F. Ce que je dis ici est la somme de tout dans la colonne emplacements dans ma table Rest3. Appuyez sur « Entrée » et c'est 1,735. Je vais juste formater ça pour être un nombre. Vous vous demandez peut-être pourquoi est-ce plus facile que d'écrire juste somme égale et de sélectionner toutes les cellules ici ? Traditionnellement, vous le faites avec une liste normale de données, mais la bonne chose à propos de ces références structurées est que je descende ici jusqu'à la dernière ligne de ma table et ajoute une nouvelle ligne, 51, Taco Bell, et ajoute une valeur à ma colonne Locations, disons à 100 ou 1 000, ce nombre sera automatiquement mis à jour dans ma somme parce que cette référence structurée se développe et se rétrécit en fonction de ce qui est dans votre table. C' est l'une des fonctionnalités les plus puissantes des tables Excel est que de nouvelles données sont ajoutées à votre table, les références structurées seront mises à jour. Imaginez avoir à faire cela avec une formule COUNT que vous pourriez utiliser aujourd'hui pour compter toutes les valeurs de votre colonne. Les références de structure en font un moyen beaucoup plus propre et moins sujet aux erreurs de référencer toutes les valeurs de votre table. Enfin, nous allons faire un petit exercice dans la création de statistiques sommaires ici dans les colonnes M et N. Je veux que vous écriviez une formule SUMIFS pour résumer toutes les ventes de ces restaurants en fonction des emplacements avec 75 grade ou plus, 50 ou 75 pour les restaurants qui ont des franchises et sans franchises. Commençons à l'écrire. Je peux écrire des sumifs égaux. Je vais juste zoomer ici pour que vous puissiez voir toutes mes données, égal à sumifs. Je vais utiliser mon curseur pour sucer ma portée. Ma plage de somme va juste être mes données de ventes, frapper une virgule, et ensuite mon premier critère sera les emplacements. Je veux tous les emplacements qui sont [inaudibles]. Je vais supprimer cette ligne ici juste pour m'assurer que nous avons les données de localisation correctes. Je vais cliquer sur « Supprimer les lignes ». Vous vous demandez peut-être comment je vais mettre les critères ? Normalement, dans mon fichier Excel, je mets dans ces petites colonnes d'aide. Je suis pour écrire le nombre 1000, 75, 50, 25, et 0. De cette façon, je peux dire à la formule SUMIFS ce qu'il faut faire référence à mes critères. Allons de l'avant et écrivons la formule égale sumifs. Ma gamme de somme va être des ventes à nouveau, virgule, ma plage de critères va être des emplacements qui sont inférieurs à 1 000. Dans ce cas, j'ai dû mettre une esperluette inférieure à 1000, virgule, mais supérieure ou égale à 75 pour cette ligne, je vais juste mettre la double citation là, esperluette. Ensuite, je veux aussi voir seulement les magasins qui ont une franchise dans ce cas. Je peux mettre une virgule franchisage, oui. Pour résumer, je prends les ventes, additionnant les ventes si les emplacements sont inférieurs à 1 000 mais supérieurs ou égaux à 75, appuyez sur « Entrée ». Maintenant, nous avons construit ma formule, appuyez sur « Entrée ». Si je remplis juste cette formule, cela me donnera automatiquement la valeur parce que j'ai mes critères et la colonne L référence dynamiquement. Je vais copier la même formule et la mettre dans ces cellules ici. Mais l'essentiel, c'est que je vais changer la franchise ici non parce que ce sont des cellules. Je veux que mon rapport n'ait pas de franchise. Je vais maintenant résumer ces valeurs très rapidement juste pour m'assurer que le total des ventes est ce que je m'attends à ce qu'il soit, soit 1,689. Encore une fois, si je vais à ma colonne de ventes ici et écrire somme, c'est en effet 1.689. C' est ainsi que vous utiliseriez la formule SUMIFS avec références de cellules dynamiques pour nos emplacements, mais en utilisant des références structurées pour extraire les données de la table Excel. Nous avons vu comment vous pouvez référencer les colonnes votre tableau à l'aide de références structurées et nous avons également montré comment vous pouvez utiliser des références structurées dans certaines des formules les plus populaires que vous utilisez aujourd'hui comme SUM et SUMIFS. Nous avons également vu comment en écrivant vos formules avec des références structurées, vos formules sont un peu plus faciles à lire et beaucoup plus faciles à déboguer, surtout si vos collègues choisissent les formules ou votre fichier Excel auprès de vous. Nous allons maintenant discuter de la façon dont vous pouvez utiliser les références structurées de vos tables Excel avec l'une des formules les plus populaires que vous utilisez aujourd'hui, à savoir VLOOKUP. 6. VLOOKUP et les références structurées: Nous allons utiliser la formule VLOOKUP maintenant pour rechercher une table Excel pour intégrer les données dans notre table principale de données. travaillant sur nos mêmes listes de restaurants maintenant, vous voyez les colonnes L et M, j'ai le nom de la ville plus le numéro de population de cette ville spécifique. À l'étape 1, je veux que vous transformiez cette liste de villes et population en une table Excel nommée Pop. Sélectionnons ces cellules. Vous pouvez cliquer sur « Insérer une table » ou un raccourci que j'aime utiliser est cette pression Contrôle T, qui fait juste apparaître le menu Créer une table pour vous. Ma table a des en-têtes frappé « ok » et maintenant nous avons ceci comme une table. Juste pour être sûr de cliquer sur « Table » et rappelez-vous, nous voulons renommer ce nom de table pour être Pop pour notre population. Nous avons maintenant deux tableaux dans cette feuille de travail, nous avons Rest312, puis nous avons notre table Pop, et vous pouvez le voir en haut à gauche de notre feuille de calcul. Je veux que vous maintenant à l'étape 2 est d'ajouter une nouvelle colonne à notre table principale et d'écrire un VLOOKUP pour rechercher la population de notre ville basée sur la colonne C, qui est nos données de localisation. Appelons cette population et comme nous l'avons vu auparavant, lorsque vous ajoutez une nouvelle colonne, la table Excel prend automatiquement cette colonne comme faisant partie de la table. Commençons à écrire la formule VLOOKUP que vous êtes très habitué à VLOOKUP. Nous voulons rechercher la colonne d'emplacement à partir de la table de notre restaurant et remarquer comment cette référence structurée se transforme simplement en un emplacement de symbole de chapeau. Notre tableau de table est que vous pouvez simplement écrire littéralement Pop et remarquer comment quand j'écris Pop, il met en évidence les cellules de ma table Pop. Je pourrais également écrire le hashtag de crochet gauche tout et cela fait référence à la table entière plus les en-têtes, dont je n'ai pas vraiment besoin, mais cela n'aura pas vraiment d'impact sur ma formule. Laissons-le comme Pop, 2, 0 parce que je veux retourner la deuxième colonne, qui est la population et quand j'appuie sur « Entrée », il remplit automatiquement la formule pour toute ma liste de restaurants. Nous avons beaucoup de ne pas appliquer parce que toutes les villes dans ma table de restaurants ne sont pas dans notre table de localisation. Mais disons que je veux ajouter une nouvelle ligne de données à ma table Pop, qui est l'étape 4. Disons que je veux ajouter la population de Louisville, Kentucky. Je vais juste copier ça pour l'instant à travers une pâte ici et disons que Louisville a 50 000$. Remarquez comment cette ligne dans ma table de restaurants automatiquement remplie parce que ma formule VLOOKUP est automatiquement remplie parce que ma formule VLOOKUPfait toujours une recherche à ma table Pop et à la minute où j'ajoute une ville à ma table pop, elle élargit automatiquement la référence structurée à l'intérieur de ma recherche, qui est dans cette colonne juste ici. Nous pouvons faire la même chose avec la correspondance d'index, que vous pouvez utiliser auparavant. Je peux écrire l'index et je veux sélectionner toute ma population ici. Je veux faire correspondre cette colonne d'emplacement dans mon emplacement de ville pop hit « Enter », et je veux réellement faire une correspondance exacte, c'est pourquoi il me donne des valeurs dans chaque cellule. Faisons une virgule zéro. Ça devrait fonctionner comme ça, mais, oh, je suis désolé, c'est dans ce match. Cela vous donnera exactement les mêmes résultats que le VLOOKUP, mais remarquez comment nous utilisons la référence structurée dans plusieurs tables maintenant. Nous regardons la population à partir d'une table Pop parce que c'est une valeur que vous voulez retirer. Nous faisons correspondre la colonne d'emplacement, qui est cette colonne, et ensuite nous comparons cela avec la colonne de la ville, qui est cette table Pop. Une dernière chose à noter sur les références sociales est que lorsque vous voyez le symbole @, c'est aussi une façon de dire à Excel que cela fait référence à cette ligne dans ma table, c'est pourquoi vous ne voyez que Seattle, Washington, mais pas la colonne entière qui serait juste emplacement par lui-même. Cela fonctionnerait toujours, mais pour être plus spécifique, vous voulez mettre ce symbole @ pour vous assurer que vous référentez juste cette ligne de données. Nous avons examiné comment vous pouvez utiliser la formule VLOOKUP, une formule très courante utilisée aujourd'hui, avec des références de structure à partir de vos tables Excel rechercher dynamiquement des données de sorte que lorsque vous ajoutez de nouvelles données à votre table de choix, une formule très courante utilisée aujourd'hui, avec des références de structure à partir de vos tables Excelpourrechercher dynamiquement des données de sorte que lorsque vous ajoutez de nouvelles données à votre table de choix, votre table de statistiques principale, qu'ils ne sont pas automatiquement actualisés et tirés correctement. Nous avons vu comment vous pouvez utiliser des références structurées et des formules, maintenant nous allons aborder un cas d'utilisation plus avancé d'utiliser des références structurées avec une liste déroulante et la validation des données dans la leçon suivante. 7. Listes déroulantes et validation des données: Nous allons créer une liste déroulante à l'aide de tables Excel, et nous allons vous montrer comment ces options flexibles peuvent se développer automatiquement à l'aide de la table. Avec nos mêmes listes de restaurants, nous avons cette liste distincte de données ici appelée KnownFor. Nous avons juste quelques attributs avec lesquels nous voulons étiqueter nos restaurants. Mais je veux transformer cette liste d'attributs en une liste déroulante que nous pouvons utiliser dans notre table principale des restaurants. À l'étape 1, transformons la liste Connu pour en une table appelée Connu pour. Ici, nous allons insérer la table et ma table a des en-têtes. Alors, OK. Dans le nom de la table, nous voulons appeler ceci KnownFor. Nous avons ceci comme table principale pour la sélection des attributs. Je veux que vous alliez maintenant à Formules, puis allez à Définir le nom et donnez à cette table un nom défini des types. Nous ne voulons pas inclure les en-têtes. Maintenant, le nom de la table et les noms de définition sont deux concepts distincts. C' est quelque chose qui devient un peu délicat lors construction de la liste déroulante avec la validation des données. Nous allons aller à « Formules », « Définir le nom » et vous remarquez ici que j'ai déjà un tas de noms définis, qui inclut des tables. Je vais cliquer sur « Plus », et appelons ce type. Quelle est la gamme de cellules ici ? Vous pouvez simplement écrire KnownFor parce que ça va être notre liste de notre table. Vous remarquez comment lorsque je clique sur la table ici, il dit toujours KnownFor, si je retourne aux formules définissent le nom, vous verrez que si je clique sur les types, il fait référence à ma gamme de cellules, qui est connu pour. Maintenant, passons à l'étape 3. Je veux créer une nouvelle colonne dans ma table principale appelée KnownFor, puis aller à Data Validation, ce que vous avez déjà fait probablement et créer une liste déroulante, où nous allons utiliser les types définissent le nom. Juste pour résumer, nous avons un Define Name appelé types, qui fait référence à notre table KnownFor. Cliquez sur « Colonne J » Connu pour et maintenant passons à « Données » « Validation des données ». Nous allons changer ce permis de lister et quelle est notre source va être ? Vous pouvez être tenté d'écrire des égaux, KnownFor comme liste, mais nous devons réellement utiliser notre nom défini appelé types, qui fait référence à notre table KnownFor. Écrivons des types égaux, appuyez sur « OK », et voyons ce qui se passe dans cette liste déroulante. Vous remarquez que nous obtenons toutes les valeurs de notre table KnownFor. Je peux aussi simplement faire glisser cette formule vers le bas et vous pouvez cliquer sur la liste déroulante, vous pouvez voir toutes ces valeurs. Une chose à garder à l'esprit est que si vous cliquez sur Formules, Définir le nom et dans mon connu.Pour référence de table ici, si vous aviez accidentellement sélectionné la table entière ici comme ceci, vous remarquez comment la référence change de connuFor à connu.Pour Tout connu pour. Je sais que cela semble compliqué, mais la raison pour laquelle cela est problématique est si vous appuyez bien, cela inclura maintenant l'en-tête de colonne KnownFor dans la liste déroulante, ce que vous ne voulez pas avoir eu lieu. C' est pourquoi je vais redéfinir ceci juste pour être connu. De cette façon, vous n'avez que les options sélectionnables dans la table KnownFor. Si je fais glisser cela jusqu'au reste de ma table, j'ai maintenant cet attribut que je peux ensuite sélectionner dans ma table KnownFor. Une autre mise en garde sur la liste déroulante, que vous avez peut-être vu avant est que si je change cela de smoothies à smoothies-YM, vous remarquez comment tous mes sandwichs restent encore des sandwichs. Je dois effectivement cliquer sur la liste déroulante et renommer ceci en Sandwiches-YM. Juste pour vous assurer que si vous corrigez des valeurs ici dans votre liste déroulante KnownFor, vous devez la modifier manuellement ici dans votre table principale. Maintenant, bien sûr, l'avantage d'utiliser des tables Excel dans notre liste déroulante ici, est que si j'ajoute une nouvelle valeur à ma table KnownFor, disons que j'appelle ces pancakes, je reçois maintenant des pancakes comme une option sélectionnable dans ma liste déroulante, ce qui est vraiment agréable à voir parce que je n'ai pas à m'inquiéter la formule référençant les références de cellule appropriées dans cette colonne. Je peux simplement référencer la table via le nom de définition. Maintenant, une étape de plus est, nous pouvons simplifier cela un peu en utilisant la fonction indirecte et notre champ source pour la validation des données. Supprimons tout cela pour l'instant et disons que je clique sur Validation des données. Dans ce champ source, au lieu d'écrire des types, qui fait référence à ce nom de définition, qui fait référence à cette table, je peux réellement écrire la fonction indirecte et écrire le nom de la table qui est KnownFor. Ensuite, mettez dans le nom de la colonne, qui est (« KnownFor [KnownFor] ») Ce que je dis à Excel ici est de me donner la table KnownFor, qui est ceci et le nom de la colonne est connu pour, appuyez sur « OK » et cela me donnera également la liste déroulante pour la validation de mes données. Juste une autre façon de ne pas avoir à utiliser un Define Name, qui fait ensuite référence à la table, si vous voulez juste le raccourcir et simplement référencer la table elle-même en utilisant cette solution de contournement indirect. Pour récapituler, pensez à ce que vous auriez à faire pour créer cette validation déroulante avec une liste dynamique d' expansion automatique en utilisant quelque chose comme la fonction count ou la fonction offset. Les avantages de cette option Définir le nom maintenant sont que vous pouvez l'utiliser n'importe où dans votre fichier Excel où vous voulez référencer cette table spécifique. Encore une fois, revenez à la leçon précédente, réfléchissez à la façon dont vos noms de table et vos noms de définition peuvent être des variables qui font référence à toutes les différentes tables de votre fichier Excel. Nous avons créé une liste déroulante à l'aide de la fonctionnalité de validation des données dans Excel. Nous utilisons les tables Excel comme options sélectionnables dans notre liste déroulante. Nous avons fait une variété de choses différentes dans tableaux Excel avec des références et des formules structurées. Maintenant, plongons dans la mise en forme de nos tableaux pour qu'ils soient vraiment jolis et jolis pour notre équipe et pour nos collègues. 8. Formater des tableaux Excel: Nous parlerons des moyens rapides que vous pouvez mettre en forme vos tableaux afin qu'ils soient vraiment jolis et jolis et bien formatés pour votre équipe et pour vos collègues. Nous avons un nouveau jeu de données dans cette leçon. C' est une liste des prix Bitcoin par date en 2013. Comme vous pouvez le voir, le prix était évidemment beaucoup plus bas à l'époque. Nous avons l'ouvert, élevé, bas, proche, et la propagation pour ces différents prix quotidiens Bitcoin. À l'étape 1, je veux que vous cliquiez sur le menu déroulant dans la section de mise en forme du tableau pour créer votre propre format de tableau personnalisé. Si je clique sur le tableau, onglet Tableau dans le ruban, et si je clique sur cette liste déroulante ici, vous pouvez voir une variété de différentes options de mise en forme que vous pouvez appliquer à votre tableau en fonction de ce dont vous avez besoin pour vos rapports, vos tableaux de bord ainsi que ce que vos collègues et coéquipiers pourraient trouver utile pour eux. Maintenant, une chose qui est vraiment bénéfique à propos de ces formatages est que vous pouvez personnaliser encore plus en créant un format de tableau personnalisé en cliquant avec le bouton droit sur le format de tableau dans ce ruban, cliquez sur Dupliquer, puis vous pouvez cliquer sur appelons ceci AlBitCointable. La raison pour laquelle je pourrais vouloir personnaliser ma table dans ce scénario spécifique est que peut-être je veux mettre en évidence tout comme les prix du week-end Bitcoin, qui est le jour de la semaine six et sept, puis mettre en évidence le jour de la semaine prix qui sont le jour de la semaine un à cinq. Ma première bande de rangées, c'est comme la première ligne à code couleur. Je peux changer cela au lieu d'être une taille de un, change à une taille de cinq. Ensuite, dans ma deuxième rangée de bande, je peux changer cela d'une taille de un à une taille de deux. Ce que cela fait est au lieu d'alterner les couleurs chaque rangée, il alterne les couleurs toutes les cinq lignes, puis deux lignes, cinq lignes, deux lignes correspondent aux jours de la semaine et les week-ends. En outre, vous pouvez également cliquer sur le format ici pour vraiment aller et formater la couleur de la bande spécifique si vous le souhaitez. Je vais juste frapper « Okay » pour l'instant. Si je clique à nouveau sur le menu déroulant ici dans mes formats de tableau, je peux cliquer sur ce format personnalisé. Vous remarquez comment le formatage change maintenant de toutes les autres lignes pour être codé par couleur, pour être cinq premières lignes, puis deux lignes pour vraiment mettre en évidence les prix de week-end et de semaine de Bitcoin à partir de 2013. Enfin, une autre chose à propos de la mise en forme des tables est parfois que vous voudrez peut-être coder des parties spécifiques de vos colonnes. Revenons à notre mise en forme régulière de tableau ici, qui est quelque chose comme ça. Si vous cliquez sur la première colonne, vous remarquez comment elle met automatiquement en évidence la première colonne de données à appeler. La dernière colonne met en surbrillance la dernière colonne, puis les colonnes baguées. Si vous avez un type spécifique de mise en forme où vous avez un codage couleur par bandes. Cliquez à nouveau sur celui-ci. Notez comment par défaut il a des lignes baguées. Si je clique sur les colonnes en bandes et les lignes en bandes non cochées, met en surbrillance toutes les autres colonnes au lieu des lignes. Une mise en forme supplémentaire que vous pouvez appliquer à votre tableau est basée sur les besoins de votre équipe. Nous expliquons comment vous pouvez personnaliser la mise en forme de votre tableau avec certaines des mises en forme intégrées d'Excel pour les tableaux Excel, mais aussi comment vous pouvez personnaliser le nombre alternatif de couleurs de ligne pour votre tableau Excel. Nous avons beaucoup appris sur la façon dont vous pouvez utiliser les tableaux Excel pour les formules, les références structurées, le formatage. Maintenant, nous allons tout mettre dans votre projet pour cette classe. 9. Réflexions finales: Félicitations, vous avez suivi cette classe sur les tables Excel, et nous avons tout appris sur la façon de créer des tables Excel, comment utiliser des références structurées dans vos formules Excel quotidiennes comme VLookUp, certains SUMIFS. Comment les appliquer aux listes déroulantes et à la validation des données afin que vos tables dans Excel, vos tableaux de bord et les rapports soient plus faciles à lire, vos formules soient plus faciles à déboguer. Vos collègues seront beaucoup plus heureux lorsqu'ils examineront vos fichiers Excel. J' espère que vous prendrez toutes les leçons de cette classe et les appliquerez au projet de classe, où vous analyserez un ensemble de données du ministère des Transports sur les retards de vol. J' espère que vous partagerez vos résultats et vos captures d'écran de vos rapports dans la galerie de projets pour que tout le monde puisse les voir. Merci beaucoup d'avoir suivi ce cours, et j'ai hâte de voir vos projets.