Maîtriser les fonctions de recherche Excel pour l'analyse de données

1. Bienvenue au cours de Lookups: Bienvenue dans le cours de recherche Excel pour l'analyse des données. Je m'appelle Chen do. Dans ce cours, vous apprendrez à utiliser des formules Excel comme la recherche vLookup x, la correspondance d' index, le filtre HLookup. Et comment combiner ces fonctions pour diverses situations pratiques dans votre domaine de travail. Nous commençons par l'utilisation de base de toutes les fonctions de recherche importantes dans Excel. Et puis je vais enseigner des techniques comme la formule de correspondance d'index. Nous recherchons comment faire tous les résultats correspondants avec une formule de recherche. Comment effectuer une recherche de plusieurs critères. Comment consolider des données dans des tables à l'aide de recherches sur le point d'effectuer une recherche sur dérivation avec des colonnes, et comment écrire des recherches qui renvoient plusieurs sorties de colonnes. Nous allons également examiner les erreurs courantes qui se produisent lorsque vous écrivez des recherches dans la façon de les corriger facilement. Il y a beaucoup de contenu précieux dans ce cours, mais tout est tellement emballé et produit manière concise afin que vous apprendrez le maximum, dans le minimum de temps chaque leçon est livré avec une vidéo et un exemple. Je vous suggère de télécharger les cahiers d'exercices, pratiquer les concepts de recherche et d'apprendre au fur et à mesure. Il y a quelques problèmes d'exemple de devoirs pour vous de le comprendre, ainsi qu'un projet de classe utilisant certaines formules de recherche. Je vous recommande fortement de terminer ces problèmes de devoirs et le projet de classe et de partager vos résultats avec nous dans le domaine communautaire. Je dirige un site Web Excel et Power BI populaire appelé Chengdu.org. Je dirige également une chaîne YouTube sous le même nom. Entre mon site et ma chaîne YouTube. J' aide plus de 0,5 million de personnes chaque année à devenir génial dans leur domaine de travail. C' est ma mission de vous rendre génial dans votre travail. Je fais cela depuis plus de 13 ans maintenant. Je vis dans la belle mais parfois venteuse Wellington en Nouvelle-Zélande. C' est tout le chemin dans le coin du monde. Quand je n'enseigne pas Excel, j'aime passer mon temps en construisant Lego avec mes enfants ou en rejouant l'un des jeux Zelda prennent notre chien, Excel sur une promenade, ou faire un beau et délicieux curry avec ma femme, Joy. Besoin d'eux. Je suis super excité de t'avoir dans ce cours. Je vous souhaite toute l'extraordinaire dans les recherches. 2. Les bases de VLOOKUP, de XLOOKUP et de HLOOKUP: Bienvenue dans notre session d'introduction sur les recherches dans Excel. Dans cette vidéo, nous allons apprendre à utiliser trois des formules de recherche les plus importantes dans Excel. Ils sont up, Xp et Hup. Dans cette vidéo, je vais également vous présenter notre exemple d'ensemble de données, et pendant le reste du cours, nous utiliserons plus ou moins cet ensemble de données. Il s'agit d'un ensemble de données sur les employés dans lequel j'ai leur nom, leur sexe, le service dans lequel ils travaillent, la date à laquelle ils ont rejoint l'organisation et le montant de leur salaire. Maintenant, la toute première formule que nous allons apprendre est la formule hep Hep est une forme abrégée de recherche verticale. Il vous permettra de poser une question précise sur vos données et d' obtenir la réponse. Par exemple, avec de telles données, je peux poser une question du type : quel est le salaire de Hussain Auger et plus vous y répondrons Une façon simple de penser à Weeks comparer avec le pointeur de la souris ou avec l'index. Imaginez maintenant que c'est imprimé et que vous scannez ces données pour trouver où se trouve Hussain Auger Ensuite, il vous suffit de passer à travers l'écran. Pour trouver leur salaire. C'est exactement ce que fait également la boucle. Voyons maintenant comment écrire cette fonction. Pour écrire le talon vers le haut, vous commencez par dire égal à L, puis vous ouvrez. Vous pouvez taper L puis appuyer sur touche Tab pour que xL tape la formule, ouvre le crochet. La valeur de recherche est alors la valeur que vous souhaitez rechercher. Tapez simplement le nom augur, et maintenant la table A est l'endroit où se trouvent vos données, sélectionnez-les numéro d'index de colonne est alors la colonne dans laquelle se trouvent vos données. Dans ce cas, je veux rechercher usinagur et ensuite obtenir Usinagur est la colonne des noms qui est numéro un, sexe est deux, le département trois, date commune est quatre et le salaire Nous voulons que la cinquième colonne soit renvoyée. Et puis le dernier paramètre est de savoir si vous voulez une correspondance approximative ou exacte. Maintenant, dans la plupart des situations professionnelles, vous voulez toujours une correspondance exacte, ce sera donc faux. Et lorsque vous serez au centre de presse, leur salaire vous sera remboursé sous forme de valeur. Vous pouvez donc voir ici qu'il s'agit de 67 910. C'est ce que dit notre formule. Maintenant, revenons en arrière et observons cette formule. Ici, nous avons saisi un nom en double code, mais vous pouvez également le faire apparaître dans une autre cellule, soit sur cette feuille de travail, soit sur autre feuille de travail, et le lier également Faisons-le en tapant un autre nom ici. Je vais le copier , le coller ici, et nous utiliserons ce nom pour rechercher Jan Morph, quel que soit le nom sur huit, c' est-à-dire la cellule Nous voulons le département de cette personne. Le département de cette personne serait en hausse huit, mes données et le département est un index de colonne de trois chutes. Voici comment vous pouvez le configurer. Et je vais obtenir la réponse pour savoir dans quel département ils travaillent. Maintenant que vous avez compris l'utilisation de base de Wheel Cup, explorons simplement la fonction X Lookup, qui ressemble à une version améliorée de la fonction talon vers le haut. Donc, pour faire la même chose, exactement ce que nous venons de faire pour Hoseinager, je peux utiliser x lookup comme ceci X. La valeur de recherche est ce que vous voulez rechercher Dans ce cas, auger et au lieu de sélectionner toutes vos données pour Xp, vous devez sélectionner deux ensembles de données Vous devez indiquer à EXL où se les données que vous souhaitez trouver, dans ce cas, la colonne du nom, ainsi que la colonne à partir de laquelle vous souhaitez renvoyer les résultats correspondants Dans ce cas, nous voulons le salaire. Nous n'avons donc pas à sélectionner l'ensemble des données. Nous sélectionnons simplement le nom et le salaire. Voilà, vous n'avez même pas besoin de spécifier autre chose dans le paramètre par défaut, lorsque vous donnez la colonne de nom et la colonne de salaire, vous obtiendrez le résultat du salaire. D'une certaine manière, XP est plus court à écrire car vous ne faites que sélectionner les données nécessaires et spécifier ce que vous voulez et EXL vous donnera le résultat Donc, comme X Loup est une version améliorée de cup dans le reste du cours, dans la mesure de cup dans le reste du cours, dans la possible, je vous enseignerai les approches basées sur Xup pour résoudre les problèmes Maintenant, écrivons la même formule du département de janvier avec Xp afin que vous vous familiarisiez avec cela. Nous allons le dire. Ma valeur de recherche est ici. Mon tableau de recherche est la colonne du nom et mon tableau de retour est la colonne du département. Maintenant, lorsque vous tapez la formule XP, vous remarquez qu'il existe en fait une option appelée Je n'ai pas trouvé. Utilisons-le, puis spécifions-le dans les codes doubles introuvables. De toute évidence, Jan Morfor étant notre employé, le département s'en sort. Mais comme il s'agit d'une cellule de saisie, je peux modifier cela. Mettons ça comme endo, puis voyons ça. Maintenant, notre formule Wheel Cup originale renvoie une erreur de hachage, alors que cette formule XL cup, parce que nous avons spécifié ce que nous voulons lorsque la valeur n'est pas trouvée, indique « introuvable Il s'agit en fait d'une autre application puissante de X lookup. Xp ajoute ces fonctionnalités supplémentaires afin qu'en tant qu'analyste de données, vous n'ayez pas à vous demander quoi faire en cas d'erreur. Il suffit de régler cela dans le cadre de la formule. L'une des plus grandes limites de la recherche est qu'elle ne peut aller que de gauche à droite. Ce que je veux dire par là, c'est qu'avec des données comme celles-ci, je peux rechercher un nom et obtenir leur date d'adhésion ou leur salaire. Mais je ne peux pas rechercher un salaire et obtenir le nom de la personne par le biais Whop, car hep cherche dans une colonne de gauche puis se dirige vers la Cela représente un gros problème pour les utilisateurs d' Excel depuis plus d'une décennie. C'est pourquoi Microsoft a introduit la fonction de recherche X, car de cette façon, vous pouvez rechercher n'importe quelle colonne et en renvoyer une autre. Cherchons le salaire de 48 170. C'est le salaire que je veux rechercher et la personne sera X. Dans ce cas, nous voulons rechercher ce salaire dans la colonne des salaires et obtenir le nom de la personne. Remarquez que nous avons changé l'ordre des deux car cette fois je recherche dans la colonne des salaires, puis je renvoie le nom. Instantanément, je peux découvrir qui est cette personne et vous pouvez vérifier avec vos données qu'il s'agit bien de Jan Morfort Jusqu'à présent, nous avons utilisé Vp et Xp sur des plages de cellules Excel classiques. Si vous remarquez ces formules, nous avons sélectionné partout les données de C 5 à G 20 ou C 5 à C 20 comme ça. C'est ce qu'on appelle des gammes régulières. Mais vous pouvez également utiliser ces formules lorsque vos données se trouvent dans un tableau. Lorsque vos données sont au format tableau ou tar, les formules deviennent également plus naturelles et plus simples. Ainsi, vous n' avez pas à trop réfléchir lorsque vous écrivez la formule. Utilisons la même chose sur la table. Juste pour que tu te familiarises avec le concept. Cherchons un nom. Je vais juste le copier. C'est le nom que nous voulons rechercher, et nous allons écrire deux formules. L'un est en hausse et l'autre est X, qui correspond à leurs salaires. Nous recherchons Paty, et mes données se trouvent déjà dans une table appelée staff Tout ce que j'ai à faire, c'est de dire personnel, ce sont toutes mes données, puis le salaire est la cinquième colonne. Le dernier argument serait faux et nous recevrons le salaire de Kins. Avec X, la formule devient X L J huit de nos cellules de saisie. Nom du personnel et salaire du personnel. La principale différence entre une recherche vers le haut et une recherche vers le haut est que dans Xp , vous spécifiez la table entière , alors que dans Xp, vous spécifiez deux colonnes individuelles, la colonne de recherche et la colonne de retour, et vous obtiendrez la réponse. J'utiliserai des tableaux pour démontrer le reste des formules cette classe, car c'est ainsi que les données sont conservées dans des situations commerciales. Bien que la formule vers le haut fonctionne, lorsque vos données sont orientées verticalement , comme les valeurs qui apparaissent en bas de l'écran, si vous avez des valeurs qui traversent l'écran, vous devez utiliser la formule vers le haut. Jetons également un coup d' œil à cela. Supposons que nous ayons obtenu nos données de vente l'année dernière dans un format matriciel comme celui-ci. J'ai donc obtenu mon nom, puis mes valeurs de vente. Et je veux juste savoir quelles sont les soldes de juin pour Doughty Mettons le nom du mois ici, juin dans une cellule. Notez la cellule C six, et nous allons écrire deux formules. L'une est une recherche dH et l'autre est Xp, car Xp est également capable de faire des recherches horizontales. Une autre raison pour laquelle X Loup est meilleur que El Cup et Hedge Lookups Commençons par la haie ici. Obtenez la valeur de recherche, qui est mon mois de juin et ma table. Maintenant que les noms des mois trouvent dans la partie principale du tableau, nous devons sélectionner l'ensemble du tableau, et pas seulement la partie des données, pour pas seulement la partie des données, que cela devienne le hachage des ventes A. que cela devienne le hachage des ventes A. Ensuite, nous devons spécifier le numéro d'index des lignes, car nous devons rechercher le mois de juin, descendre et obtenir les données d' une ligne spécifique appartenant à Doughty Strut une ligne spécifique appartenant à Doughty Dans ce cas, Doughty se trouve dans la ligne numéro 13 Si vous comptez à partir du nom, nous disons 13, puis nous disons chutes La syntaxe est H et suit le même schéma, mais dans H, l'orientation des données est horizontale, vous obtiendrez la réponse, qui correspond exactement aux ventes de juin pour Doughty Strut Maintenant, faisons la même chose avec X. X en juin. Cette fois, vous souhaitez spécifier la ligne d'en-tête du tableau, car je souhaite rechercher le mois de juin dans les en-têtes du tableau des ventes et obtenir les données de Doughty Strutle Nous sélectionnons la ligne qui correspond à Doughty et fermons le support et nous obtiendrons à nouveau le même numéro À ce stade, vous vous demandez peut-être, he Chen, c'est bien. Mais si j'ai deux informations, je dois avoir celles du mois de juin, mais je voudrais aussi avoir celles de Doughty Strut, afin de pouvoir consulter les ventes de mai pour Andrea Kimpton ou Ces recherches sont appelées recherches bidirectionnelles ou bidirectionnelles, et j'ai une leçon dans le plan de cours qui parle également de cette technique Alors restez dans les parages et regardez ça aussi. Par, 3. La formule INDEX+MATCH: Bienvenue dans la classe LevelUp Excel Loops. Dans cette vidéo, nous allons comprendre la formule de correspondance des indices. Supposons que vous ayez une date inscrite dans une cellule et que vos données se trouvent ici. Vous aimeriez savoir quel est le nom de la personne qui s'est inscrite à cette date. Maintenant, comme expliqué dans la vidéo précédente, vous pouvez utiliser la fonction Xp pour ce faire. Mais disons simplement que vous ne pouvez pas utiliser X Loup parce que vous n'êtes pas sur EXL 365 ou que vous ne voulez pas utiliser X Loup Alors, comment répondez-vous à cette question ? Pour obtenir la réponse à cette question, vous devez le faire en deux étapes car la fonction p va de gauche à droite, nous ne pouvons donc pas vraiment rechercher la date et aller sur le côté droit pour obtenir le nom. La première étape consiste à trouver où se trouve cette date dans cette liste et à obtenir la position de cette date. Il s'agit en fait de la cinquième date de la liste, donc c'est le cinquième élément. La deuxième étape consiste à obtenir le cinquième nom. Le problème devient double : trouver la position et obtenir l'objet correspondant Pour trouver la position d'un élément dans une liste, nous pouvons utiliser la fonction match. OK. Faites donc correspondre une valeur dans une liste de valeurs. Donc, dans ce cas, notre liste de valeurs que le personnel a rejoint, et comment vous voulez la faire correspondre, vous devez toujours faire une correspondance exacte sauf si vous faites quelque chose de très spécifique. Le dernier argument devient donc zéro et fermez le crochet et vous obtiendrez la position de cette date. Quelle que soit cette date, si elle figure dans cette liste, elle apparaîtra. Le 19 avril devient donc cinq, mais le 18 mai 2018 devient la huitième date. Maintenant, allons-y et cherchons le nom de la personne. Je connais le poste. Je veux obtenir le huitième nom ou le cinquième élément de la liste. C'est à ce moment que l'autre index de fonction est utilisé. Un index, une liste, terminez l'élément que vous souhaitez. Huit. Cela vous donnera Andrea Kimpton comme nom Et si vous revenez à votre date initiale, le 5, le 19 avril, nous obtiendrons le nom de Curtis Advani Ensemble, ces deux formules créent une nouvelle construction appelée formule de correspondance d'index. La façon dont cela fonctionne est que vous écrivez un index rapidement, vous spécifiez la liste à partir de laquelle vous souhaitez l'obtenir. Nous cherchons la date et nous voulons le nom. Nous commençons donc par le nom, puis nous écrivons la fonction match. Nous disons que c'est la date que je recherche. Dans la colonne de date avec une correspondance exacte, nous trouverons le nom. Maintenant que vous comprenez la formule de correspondance des indices, voyons quelques remplacements pour celle-ci Le remplacement numéro un que vous devriez essayer est celui de recherche X. Nous l'avons déjà vu dans la vidéo précédente. Je vais juste faire une démonstration rapide à nouveau. Cette date, dans la liste des dates d'adhésion, et nous voulons le nom. Nous allons chercher Curtis Sadvan. Une autre option que vous pouvez utiliser est index plus x match. La fonction x match appartient à la même famille que x. C'est l'une des fonctions les plus récentes, et elle fonctionne comme ce nom d'index, date de correspondance dans le joint de date. Pour x match, il n'est pas nécessaire de spécifier le mode de correspondance. Ce sera toujours une correspondance exacte par défaut, vous obtiendrez à nouveau Curtis Avan C'est ainsi que fonctionnent les fonctions de correspondance d'index 4. Regardons à double voies (Row Colonne): Dans cette leçon, voyons comment effectuer des recherches bidirectionnelles à l' aide d'Excel Je vais vous montrer trois techniques différentes En fonction de la disponibilité de vos données et de la version d'Excel que vous utilisez, utilisez l'une de ces méthodes. Pour cet exemple de problème, nous aimerions connaître les ventes de bouteilles d'échecs en mars. C'est la valeur que nous essayons d'obtenir. La première technique que nous utiliserons est la technique de correspondance d'index. Sachez déjà que la fonction d' index peut être utilisée pour obtenir une valeur particulière dans une liste. Si je prononce des noms et que j'en donne quatre, j'obtiendrai le quatrième nom, qui est GG. Mais la fonction d'index a une autre utilité. Si vous fournissez un grand A, donc une grande fourchette au lieu d'une seule liste, si je sélectionne le tableau entier, le tableau des ventes, puis que je dis quatre K trois, j'obtiendrai la valeur dans la ligne numéro quatre, colonne numéro trois. Nous allons donc obtenir les soldes de février de Gigi Boling , car il s' agit des colonnes 1, 2, 3, et c'est le numéro 1234, et nous en retrouverons Nous pouvons utiliser cette méthode pour déterminer quelle est la valeur ici en utilisant simplement deux formules de correspondance où les numéros quatre et trois sont disponibles. Essayons ceci ici. Nous dirons index. Nous devons sélectionner uniquement la partie des données de notre tableau. Vous ne souhaitez pas non plus sélectionner le nom, car vos formules de recherche doivent alors être légèrement différentes. Et puis dites match, le match se fera d' abord au niveau de la ligne Nous voulons donc faire correspondre Chess Bonel dans tous les noms avec une correspondance exacte La deuxième formule de correspondance que nous voulons faire est de faire correspondre le mois de mars dans la liste mensuelle. suffit de sélectionner les mois dans votre tableau, puis de trouver une correspondance exacte et de fermer la formule de l'indice. Remarquez comment cela est configuré : index de vos données et une correspondance sur l'élément au niveau de la ligne et une correspondance sur les éléments au niveau de la colonne. Et nous trouverons la réponse aux 36 415. La deuxième méthode pour ce faire est également de loin ma préférée, qui consiste à utiliser deux fonctions X up. Comme vous l'avez vu dans les vidéos précédentes, la formule EXL cup remplace la tasse UP, mais elle possède également un autre super pouvoir Je vais vous le démontrer afin que vous puissiez comprendre ce que je veux dire par là. Alors on dit « up ». Cherchons Chess Borel dans la colonne des noms, et renvoyons le tableau des ventes complet Ainsi, au lieu de spécifier une seule colonne, comme les ventes de janvier ou les ventes de mars, nous vous conseillons de rechercher le nom C seven dans la colonne des noms, puis d'obtenir le tableau complet. Cela vous donnera cette rangée entière pour bouteille thoracique. Excel 2000 x 3605 possède cette magnifique fonctionnalité de déversement Il imprime la valeur et remarque qu'au total, il y a en fait 13 valeurs Il étendra donc automatiquement le résultat dans ces 13 cellules C'est l'un des pouvoirs cachés de la X cup. Il peut en fait renvoyer une plage complète de valeurs au lieu d'une seule valeur. Il ne me reste plus qu'à obtenir cette valeur. Maintenant que nous avons cette liste complète, si je l'envoie à un autre gobelet X L, puis que je demande la valeur de mars. Tu auras celui-là. Nous commençons donc par X. Nous allons rechercher le bonel de poitrine dans la colonne des noms et renvoyer l'une de ces valeurs Ainsi, lorsque vous appuyez sur le centre, vous obtenez les 12 valeurs. Il ne vous donnera pas le nom, mais il vous donnera tout. Vous pouvez voir que ces valeurs vont passer comme ça. Nous allons utiliser un autre x x. Cette fois, nous cherchons le mois de mars dans les noms des mois, puis nous renvoyons la valeur correspondante. Cela vous donnera donc 36 415, ce qui est encore une fois pareil. Comprenons cette formule d'un peu plus près. Donc, comme vous avez pu le voir, nous utilisons deux boucles en X. Cet XP interne vous donnera les 12 valeurs correspondant au bonel du coffre Si vous sélectionnez tout cela et que vous appuyez sur la touche Ctrl égale à, je peux voir que ce tableau sera renvoyé et cette boucle X externe recherchera alors March dans les en-têtes Il trouve donc que March est le troisième en-tête, il en tirera donc la troisième valeur correspondante, qui sera cette valeur. C'est ainsi que fonctionne cette méthode Xp x loup. Voici une astuce bonus. Au lieu de rechercher d'abord le numéro de ligne, vous pouvez abord rechercher la colonne, puis regarder à nouveau la ligne. C'est ainsi que cela fonctionnerait. Nous allons dire x look, rechercher mars dans les en-têtes, puis obtenir les valeurs pour mars Comme vous pouvez le deviner, cette fonction vous donnera toutes les valeurs de mars pour tout le monde. Ensuite, cette recherche en x, je peux l'envoyer à une autre bonel d'échecs en X X dans la colonne des noms, puis en obtenir la valeur correspondante Cela fonctionnera également. importe la direction dans laquelle vous allez vite, vous pouvez aller vite en ligne, puis en colonne ou en colonne , puis en ligne. Même résultat. Voyons maintenant la troisième méthode, qui est en fait un mélange de une et deux. Nous pouvons utiliser index x match x match car fonction x match est une version plus courte de la fonction match, je peux l'utiliser. Je vais dire indexer mes données, juste la partie numérique, X match chess bonel dans la colonne des noms, X match March dans les en-têtes du mois, et nous obtiendrons la même réponse Voilà trois méthodes différentes. Maintenant, si vous vous demandez quelle méthode utiliser, si vous utilisez Excel 3605, cela devrait être votre méthode préférée car elle est assez simple et utilise des méthodes modernes pour faire les choses Mais si vous utilisez une ancienne version d'Excel, cette approche devient votre approche par défaut. Pour le fun, vous pouvez également essayer le numéro trois. Selon ce qui vous convient, vous pouvez les essayer. J'espère que vous avez trouvé tout cela utile. On se revoit dans la prochaine vidéo. 5. Tous les résultats de Lookups correspondent à la réalisation de Lookups: L'une des principales limites de X L p ou de tout autre type de fonction de recherche dans les EXL. Ils ne peuvent consulter que le premier résultat correspondant. Supposons que vous recherchiez le service financier et que vous souhaitiez trouver toutes les personnes de ce service. Vous avez votre formule de recherche ici, X L, la valeur du département et le nom, et dès que vous aurez indiqué le financement, il vous suffira de trouver la toute première personne, Curtis Advani, et d'imprimer qu'il n' y a aucun autre moyen pour nous de récupérer ces autres noms grâce aux fonctions de recherche Dans cette vidéo, je vais montrer une technique permettant de générer tous les résultats correspondants possibles lorsque vous tapez un département. Si je parle de ventes, je trouverai toutes les personnes présentes sur le site Web du service commercial, j'aurai le site Web, si je mets un département qui n' existe pas dans nos données, je n'obtiendrai aucun résultat. Je vais vous montrer deux techniques, l'une pour le faire avec XCL 3605 et l'autre pour l' ancienne version d'Excel Allons voir comment cela peut être fait. Excel 3605 a introduit un nouvel ensemble de fonctions appelées fonctions de tableau dynamique L'une de ces fonctions est connue sous le nom de filtre. La fonction de filtrage peut prendre certaines données et filtrer en fonction des conditions souhaitées. Nous pouvons utiliser la fonction de filtre comme fonction de recherche dans EXCL 365. Par exemple, vous pouvez dire « données du personnel » où département du personnel » est égal à, puis utiliser le double code « finance », ce qui vous donnera à peu près un sous-ensemble de données appartenant uniquement aux responsables des finances C'est donc presque comme si vous appliquiez des filtres sur les données et que vous choisissiez le financement. Quel que soit le résultat, c'est ce que vous obtenez ici. Mais c'est une formule. Vous pouvez donc le paramétrer, changer le mode d'affichage , et tout C'est ainsi que j' ai procédé. J'ai mis mes critères de saisie ici afin de pouvoir taper ce que je veux. Et ici, j'ai écrit une fonction de filtre disant simplement que le département du personnel du filtre est égal à d trois, qui est ma cellule de saisie. Et s'il n'y a aucun résultat, suffit d'imprimer le mot « aucun résultat », et c'est ainsi que les résultats apparaîtront. L'une des limites de la fonction de filtre est qu'elle ne renvoie pas les données conformément au formatage d'origine Ici, les dates et les devises sont bien formatées, alors qu'ici la date apparaîtra sous forme de chiffre, tout comme la devise Vous devrez sélectionner les cellules dans lesquelles vos résultats pourraient apparaître et appliquer au préalable la règle de mise en forme. Notez que chaque fois que vous tapez la fonction de filtre, il s'agit de l'une de ces fonctions de tableau dynamique. En fonction de la taille de votre résultat, Excel réduira ou agrandira automatiquement Excel réduira ou la zone de résultat de cette formule. C'est ce qu'on appelle Spill Range. Et lorsque vous avez une formule comportant une plage de déversement, Excel la met en évidence avec cette zone bleue. Ainsi, chaque partie du jeu de résultats que vous sélectionnez, Excel affichera un rectangle bleu autour du dicton « Tout cela fait partie de la plage de déversement ». Vous pouvez examiner la formule qui renvoie la plage de déversement en sélectionnant la cellule et en regardant dans la barre de formule. Désormais, la barre de formule grise la formule si vous sélectionnez une cellule. Mais si vous sélectionnez la cellule en haut à gauche de la plage de déversement, vous verrez qu' elle est modifiable Je peux donc également modifier la formule C'est ainsi que vous pouvez utiliser la fonction de filtre pour renvoyer tous les résultats correspondants. Permettez-moi de vous donner un autre exemple. Disons que je ne veux pas voir tous leurs détails. Je veux juste les noms des personnes du département des finances. Voici comment nous pourrions le faire. Nous dirons les noms, puis les noms du personnel de filtrage. Donc cette fois, je veux juste que le nom du personnel soit filtré en fonction du fait que le département du personnel est égal à T trois, et je vais juste obtenir les noms. Outre le filtre, Microsoft a également introduit d'autres formules telles que sort uniq, etc. Ainsi, par exemple, je peux envoyer les résultats de ce filtre à une fonction de tri afin voir ces noms dans l'ordre alphabétique. C'est très utile, en particulier pour les objets d'affichage où vos données peuvent être dans n'importe quel ordre, mais les sorties sont dans un ordre bien trié. Si vous utilisez Office 3605, je vous recommande vivement d'essayer ces fonctions et de les implémenter dans vos flux Voyons comment faire la même chose dans une ancienne version d'Excel. L'ensemble de formules que nous allons utiliser dans cette partie de la vidéo légèrement plus avancé et compliqué si vous n'avez jamais fait ce genre de choses. Mais je vous recommande de le regarder et de voir quelles sont les idées clés que vous pouvez retenir et mettre en œuvre dans vos flux de travail. Si je mets le nom du département ici, j'obtiendrai les résultats. Dans le cadre de cet exemple, je ne montre que les cinq premiers résultats. Si je mets les ventes, j'obtiendrai les deux premiers et les résultats, et si je mets le site Web, j'obtiendrai les cinq premiers résultats. Pour que cela fonctionne, nous devons d'abord comprendre certaines choses internes. Allons-y donc et examinons-les d'abord. J'ai donc déjà configuré certains calculs dans la feuille de travail et je les ai simplement cachés. Déballons-le. Nous avons donc une cellule de saisie dans laquelle je vais taper le département que je souhaite consulter. Il s'agit du D trois sur l'ancienne feuille de calcul Excel A correspond. La première étape consiste à déterminer si D trois sur cette feuille est égal à l'une de ces valeurs. Donc, partout où il est égal, je veux renvoyer le numéro de ligne particulier dans le tableau. C'est ici que j'utilise une forme de vie simple. Le personnel du département tarifaire qui est le département de cette rangée est égal à mon D trois. Ensuite, je veux obtenir le numéro de ligne de cette cellule particulière. C'est là qu'intervient la fonction de ligne. Ligne du personnel du département moins le numéro de ligne de la valeur d'en-tête. De cette façon, je peux générer un nombre relatif. Sinon, ce champ sera vide. Ici, je vais choisir pour ce site Web en particulier deux, trois, six, sept, huit, neuf et 12. Partout où il ne correspond pas, il sera simplement vide. Nous pouvons y penser car j' ai une liste de chiffres, et je veux juste savoir quels sont les cinq premiers petits chiffres car nous ne montrons que les cinq premiers résultats. C'est ici que je génère les numéros un à cinq. Il suffit de les saisir dans la cellule. Et je veux savoir quel est le premier plus petit chiffre , le second, le plus petit nombre comme ça. Excel possède également une autre fonction appelée small, que vous pouvez utiliser pour ce faire. Vous dites « petit » de ce rectangle bleu. Il contient tous mes chiffres, et quel est le premier, le plus petit chiffre, deuxième, le plus petit chiffre. Une fois que nous les avons, il ne nous reste plus qu'à obtenir la deuxième rangée complète, la troisième ligne complète, la sixième rangée entière comme ça. Pour ce faire, nous pouvons utiliser la fonction index. Je vais vous montrer comment obtenir un index de ligne spécifique. Nous dirons donc index, données. Donc, dans ce cas, le personnel, et le numéro de ligne que nous voulons est deux. Nous voulons toutes les colonnes de ce tableau qui se trouve dans la deuxième ligne. Lorsque vous voulez toutes les colonnes, vous pouvez simplement dire virgule et ignorer la partie numéro de colonne de la formule d'index. Si vous dites simplement deux, vous n'obtiendrez pas la réponse, vous devez dire une virgule puis fermer le crochet À ce stade, la fonction d' index vous donne la deuxième ligne complète. Et comme l'EXL 3635 est doté d'une fonctionnalité de déversement, il prend en fait la valeur et la répand Mais dans les anciennes versions de X, sélectionnez ces cinq cellules, prenez-les, puis appuyez sur Ctrl Shift Enter pour obtenir le résultat. Mais maintenant, cette fonction est divisée en cinq cellules, chaque cellule affichant la valeur de colonne correspondante. C'est la méthode que j'utilise ici. J'ai généré les numéros d'identification 23678, puis je les insère ici dans ces cellules, cinq cellules, et ici j'utilise simplement l'index de ces données, puis je le laisse Cette partie générera cette ligne entière et nous l'imprimons via Control Shift Enter. Maintenant, il n'y a qu' un seul problème supplémentaire , à savoir si le ministère n'existe pas. Ainsi, par exemple, nous avons mis les ressources humaines. Ces données ne figurent pas dans notre tableau du personnel. Ensuite, nous devons imprimer un résultat, par exemple aucun résultat ou quelque chose comme ça. C'est ici que j'utilise la fonction d'erreur. Si ma formule d'index apparaît comme une erreur, imprimez ce message convivial. C'est ainsi que vous pouvez obtenir tous les résultats de traitement par lots dans les anciennes versions d'Excel Bien que cette technique particulière ne soit pas pertinente pour les utilisateurs d'EXL 365, car vous disposez de la meilleure formule de filtre J'ai l'impression que l'apprentissage de cette utilisation particulière de la formule indicielle peut vous aider dans d'autres situations. J'espère que tout cela vous a été très utile. Je vous reverrai lors de la prochaine leçon. 6. Regarder les colonnes dérivées de on: Dans cette vidéo, expliquons comment effectuer des recherches lorsque les critères ou le calcul sont un peu plus compliqués Je les appelle une recherche sur des colonnes dérivées. Nous verrons comment écrire des formules de recherche pour ces cinq situations. Et puis à la fin de la vidéo, je vous demanderai également de travailler sur trois autres problèmes en guise de devoirs. Allons-y un par un. Nous voulons connaître la personne qui a le salaire maximum. En d'autres termes, j'aimerais connaître le nom de cette personne, mais par le biais de formules. Premièrement, la valeur que vous recherchez deviendra le maximum dans la colonne des salaires. Vous diriez donc soit x lookup, soit up. Allons-y avec la recherche en X. Maximum du salaire. C'est la valeur que je souhaite rechercher dans la colonne des salaires du personnel. Ensuite, nous voulons connaître leur nom, alors nous dirons le nom du personnel. Cela vous donnera le nom de Plaqan. La deuxième est l' embauche de candidats. Dans ce cas, nous voulons déterminer qui est la dernière personne à avoir rejoint l'organisation. Cela signifie donc que la personne dont la date d'adhésion est la plus récente est la dernière, c'est la personne que nous aimerions réunir. Et encore une fois, la formule devient très simple une fois que vous comprenez la logique. Nous indiquons p maximum sur la colonne de date jointe, car techniquement, les dates el sont des nombres. Nous pouvons donc utiliser Max pour effectuer la dernière opération, Nous pouvons donc utiliser Max pour effectuer c'est-à-dire faire le plus tôt possible. Ensuite, nous indiquerons la date d'arrivée du personnel. Et encore une fois, le nom du personnel pour obtenir son nom. Et nous trouverons qui est cette personne. Dans ce cas, il s'agit de Barfony le 6 octobre 2019. troisième est que nous aimerions recruter la personne qui nous a rejoint en 2017. Nous avons donc une valeur de recherche, qui est 2017, mais nous devons d' abord calculer l' année à partir de la date jointe. joint de date contient la date complète et nous voulons simplement rechercher en fonction de la seule composante annuelle de celle-ci. Il se trouve qu'il n'y a qu'une seule valeur, mais s'il y en a plusieurs, alors, comme nous cherchons ou si X le ferait, elles vous donneront simplement le tout premier résultat. C'est exactement là que x up brille vraiment car vous pouvez facilement effectuer de tels calculs dérivés Nous dirons que la recherche date de 2017 et que la date de recherche est la date d'adhésion, mais pas la date d'adhésion directe. Nous ne pouvons pas utiliser la date d'adhésion en tant que telle. Nous voulons plutôt prendre la date d'adhésion et l'envoyer à la fonction auriculaire afin pouvoir déterminer toutes les années à partir de cette date. Lorsque ear lit ceci, il génère une liste ou un tableau de nombres qui ressembleront à 2000 1919 1918 comme ça, puis nous voulons renvoyer le nom. Je vais vous donner Jan Morfor, la personne qui nous a rejoint en 2017. Il s'agit d'une construction très puissante. Je vais donc le démontrer à nouveau avec le quatrième exemple, à savoir que les trois premières lettres sont RAF. Ici, la valeur que je souhaite rechercher est à nouveau fournie. Nous voulons rechercher RAF. Mais le Luca para est dans le nom. Mais au lieu du nom, nous voulons uniquement les trois premières lettres de la colonne des noms. Nous pouvons utiliser une fonction comme celle de gauche dans la colonne des noms. Nous allons dire à gauche du nom trois, puis cela me donnera les trois premières lettres sous forme de tableau, puis nous voudrons obtenir leur nom. Cela vous donnera à nouveau des informations. Maintenant, voyons comment fonctionnera la gauche interne. Je sélectionne cette portion et je l'évalue seule avec un contrôle égal à, et je peux voir les trois lettres, sauf Dong Donc, comme ce sont toutes les trois lettres, XL cup essaiera alors de correspondre à RAF et elle correspondra à la valeur, puis donnera le nom correspondant. Le cinquième exemple que nous devons calculer est le salaire de 88 000$ Maintenant, vous pouvez voir dans la colonne des salaires qu'une telle personne n'existe pas. Et en effet, lorsque vous faites un X Lp ou même plus 88 000 dollars et que vous dites simplement «  vous voulez le salaire et vous voulez le nom », vous recevrez une erreur parce que cette personne n' existe pas Mais la vraie chose que nous voulons récupérer , c'est cette personne, la poitrine. La question qui se pose maintenant est nous devons d'abord arrondir le salaire à 2000 , puis faire le match. Voici comment vous pouvez le faire. Vous pouvez utiliser X L pour rechercher 88, puis prendre le salaire lui-même envoyer à la fonction ronde. Nous prenons la colonne des salaires, puis disons le salaire du personnel divisé par 1 000 à zéro chiffre. Cela arrondira tout le monde à ses milliers, 88 050 dollars deviennent 88, puis nous voulons le nom, et cela vous donnera Il s'agit d'un autre exemple. Ces trois exemples suivent le même schéma, mais ils vous montrent ensuite que X L peut calculs au lieu de colonnes directes, où le nom de cette leçon dérivée des colonnes Comme promis, j'ai trois problèmes de devoirs à vous faire. Je veux que vous découvriez la personne qui touche le salaire minimum, personne dont les deux lettres des deux dernières lettres du nom sont ND et la personne qui a rejoint le groupe au mois d'août. Peu importe l'année. Tout ce que nous savons, c'est qu'ils se sont inscrits au mois d'août et nous voulons simplement connaître leurs noms. Découvrez les formules pour talons ou les formules Excel qui vous permettront d'obtenir ces réponses et partagez-les dans la section des commentaires. C'est tout pour le moment. J'espère que vous avez apprécié cette leçon. On se voit dans le prochain. Au revoir. 7. Repôts multiples (multi-condition) de recherches: Dans cette vidéo, nous allons apprendre à créer une recherche à critères multiples à l'aide d'Excel. Plus précisément, nous allons créer quelque chose comme celui-ci dans lequel vous pouvez saisir un vendeur et un nom de pays, puis vous obtiendrez ces résultats Faisons une démonstration rapide ici. Je vais changer cela pour échecs et quitter le pays en tant que Royaume-Uni, et vous pouvez voir que la formule que j'ai écrite a calculé qu'il y a deux records, et elle affiche le tout premier, qui est également mis en évidence ici. Alors, comment effectuer une recherche basée sur plusieurs conditions. Je le sais déjà si j' utilise Xp ou quelque chose comme ça. Ainsi, par exemple, si vous tapez « échecs » dans la colonne des noms de ma table de vente, puis si vous obtenez le montant, j'obtiendrai le tout premier montant pour les échecs. Ce ne sera donc pas nécessairement le montant britannique. Je veux dire, dans ce cas, ce serait Royaume-Uni parce que c'est la toute première valeur. Mais cela peut être autre chose en fonction de la manière dont vos données sont ajustées. Ainsi, par exemple, si nous changeons cela en barre, nous voulons le montant des barres au Royaume-Uni, soit 2499, mais ma formule X L me donnera le montant des barres aux États-Unis parce que c'est la toute première valeur Alors, comment traiter plusieurs conditions ? Eh bien, la technique consiste à utiliser le concept de colonnes dérivées qui a été abordé dans la vidéo précédente, et à l'étendre. Par exemple, nous allons dire X look, rechercher un, puis le tableau de recherche doit être entre crochets ouverts. La colonne de nom est égale à mon nom, qui est un L six. Multiplier cela par la colonne du pays est égal à mon pays. La colonne de retour doit alors être o car c'est ce que nous voulons rechercher. Maintenant, lors de votre présentation, vous obtiendrez le résultat correct, qui serait 2499 Mais comment cela fonctionne-t-il ? OK. Eh bien, allons-y et comprenons cela. Ici, nous disons d'en rechercher une, puis nous prenons une liste, qui est la colonne de mon nom de vente, puis nous la comparons à L six. Si je fais juste cette étape, puis que je l'évalue pour évaluer une partie de la formule, vous pouvez la sélectionner et appuyer sur la touche Ctrl égale ou sur la touche F neuf, et vous obtiendrez les résultats de l' évaluation. Ce serait vrai partout où la personne se trouve dans un bar. Vous pouvez donc voir que la deuxième valeur est vraie parce que nous vérifions par rapport à la barre, puis que la cinquième valeur est également vraie parce que la cinquième personne est barre. De la même manière, cette liste sera également un ensemble de valeurs vraies et fausses. Lorsque vous prenez un tas de vraies fausses valeurs ici, multipliez-les par les vraies fausses valeurs qui s'y trouvent. Chaque fois que les valeurs correspondantes sont vraies dans les deux listes. Cela signifie que le nom est égal à la barre est vrai. Le pays est égal au Royaume-Uni, c'est également vrai. Le résultat net sera deux fois vrai, ce qui sera un dans le monde d'Excel. Parce qu'Excel traite le vrai et le zéro comme zéro, un et zéro, une fois un devient un. C'est pourquoi nous en recherchons un. Nous disons d'en rechercher un , puis de faire toute cette multiplication ici, puis d'obtenir le montant. Chaque fois que c'est un, cela produira une liste de uns et de zéros, le tout Si je sélectionne tout cela et que j' appuie sur la touche Ctrl égale à, vous pouvez voir qu'il s' agit en fait de zéro, sauf pour le cinquième enregistrement, qui est Bar et UK. La prochaine recherche indiquera que j'ai trouvé l'article correspondant en cinquième position. Le montant correspondant sera donc la colonne des montants, qui serait mon 2499 C'est ainsi que j'ai écrit cette formule. seul élément supplémentaire que j'ai fait est saisi des informations, aucun message d'information chaque fois que rien n'est trouvé. Ensuite, j'ai fait de même pour les clients et les boîtes. Quand nous le formulerons, cela me donnera le résultat. Essayons maintenant la barre USA où nous avons plusieurs enregistrements. Nous avons trois records. Comme XL ne trouvera que le tout premier enregistrement, j'affiche un message facultatif ici et imprime uniquement les détails du tout premier enregistrement. Est-ce que je calcule le nombre d'enregistrements ? Eh bien, c'est très simple. Nous pouvons utiliser la fonction countifs pour rechercher le nombre de fois que la barre et la combinaison USA sont apparues Nous dirons que le nom est bar, le pays est les États-Unis, puis ils me le diront trois fois. Ici, je dis que si mon nombre d'enregistrements est supérieur à un, alors imprimez simplement le message indiquant premier enregistrement, sinon restez silencieux. C'est ainsi que cela est construit. Vous pouvez utiliser cette technique particulière et l' étendre à un certain nombre de situations. Maintenant, voici un autre projet pour vous ? Vous pouvez le traiter comme un projet de classe. Comment trouverais-tu les trois disques et les montrerais-tu ? Au lieu d'une, je veux que vous montriez les numéros deux et trois ci-dessous en utilisant les autres techniques que nous avons abordées dans les vidéos précédentes. Considérez cela comme un défi, puis si vous rencontrez des difficultés, consultez le classeur de solutions dans les liens de description Rendez-vous dans la prochaine vidéo. Au revoir. 8. Faire disparaître les IFs nested de la fonction LOOKUP(): Dans cette leçon, je vais montrer comment calculer le bonus à l'aide de la formule de recherche. J'ai ici les données de nos employés, et nous aimerions offrir des bonus sur la base de ces règles Salaire jusqu'à 60 000$, vous obtiendrez un bonus de 5% jusqu'à 75,4% Et si votre salaire est inférieur à 90 000, vous obtenez 3 % de plus, vous obtenez 2 % Alors, comment calcule-t-on le bonus ? Vous pourriez être tenté d' écrire une longue formule imbriquée. Mais vous pouvez utiliser une fonction de recherche plus courte pour ce faire. Avant cela, nous devons configurer nos données de bonus sous forme de tableau. Vous devez ajuster vos données de manière à ce que l'élément rapide soit égal à zéro, et cela vous indiquera quel en est le bonus. La façon de lire ceci est de zéro à 60 000, vous avez compris. 60 à 75, tu comprends ceci, 75 90, tu comprends ça. Tout ce qui est supérieur à 90, tu comprends. C'est ainsi que vous souhaitez ajuster vos données et créer une table de mappage. Une fois que c'est le cas, vous pouvez utiliser la fonction de recherche comme celle-ci. Recherchez, notez qu'il ne s' agit pas d'une recherche en haut, d'une recherche H ou d'une recherche x , il s'agit d'une simple fonction , sélectionnez le salaire, puis le vecteur de recherche est la première colonne de votre table, puis le résultat est la deuxième colonne de cette table. Désormais, si ces données ne sont pas sous forme de tableau, se trouvent dans des fourchettes de vente. Assurez-vous que vous utilisez toutes ces références comme des références absolues en les remplaçant par le format dollar. Vous pouvez sélectionner la gamme complète et appuyer sur F quatre, et EXL ajoutera les dollars nécessaires pour vous Une fois cela fait, fermez le crochet, appuyez sur Entrée, et EXL calculera les pourcentages de bonus nécessaires et les y imprimera. Vérifions-le. Cette personne touche 75 000 dollars de salaire, devrait toucher 3 % parce qu'elle a plus de 75 000 ans et non pas 75 000 dollars, donc elle tombera dans Alors que cette personne gagne plus de 90 000 dollars, elle gagnera 2 % Cette personne a moins de 60 000 ans, donc elle obtient 5 % comme ça. C'est ainsi que je peux calculer mon pourcentage de bonus. Je peux également calculer montant de ma prime en dollars soit en le prenant et en le multipliant comme ceci, soit en modifiant cette formule et en multipliant le résultat de la recherche par la valeur salariale de ce taux Allez-y et utilisez fonction de recherche au lieu de formules imbriquées La seule chose à garder à l'esprit est que ce tableau doit être trié par ordre croissant On ne peut pas vraiment les mettre en désordre. Vous devez partir de zéro et définir clairement les limites afin que les valeurs puissent être correctement présentées par Excel. Je vous reverrai dans la prochaine vidéo. 9. Une recherche et plusieurs colonnes en résultant de multiples colonnes: Dans cette vidéo, je vais vous montrer comment utiliser à la fois x loop et up pour récupérer plusieurs colonnes avec une seule fonction À cette fin, nous mettrons notre nom de recherche dans la cellule J quatre, et nous verrons instantanément tous les détails de cet employé, horizontalement ou verticalement. Alors, comprenons cela. Je vais juste taper Chess Bottle ici, et instantanément, j' aurai tous les résultats. Maintenant, il ne s'agit pas réellement de cinq formules. Il s'agit d'une formule unique. Alors, comment ça marche ? Repartons de zéro. Nous disons valeur de recherche x, puis où se trouve l' élément de recherche, donc c'est dans le nom. Mais lorsqu'il s'agit de renvoyer un tableau, au lieu de sélectionner une seule colonne, nous sélectionnerons le tableau entier puis fermerons le crochet. Notez que la colonne de retour n' est pas limitée à une seule colonne, elle affichera la ligne complète de bonel d'échecs ici, puis elle l'affichera joliment sur l'écran Il s'agit d'une fonctionnalité introduite dans EXL 3605 en même temps que la fonctionnalité L cup. Ainsi, chaque fois que vous écrivez le clo cup et fournissez plus d'une colonne comme critère de sortie, valeurs sont automatiquement les valeurs sont automatiquement prises et diffusées à l'écran Ce comportement est appelé comportement orthographique, et l'ensemble de cette fonctionnalité est appelé fonctionnalité de tableau dynamique. Nous l'avons également vu ailleurs dans l' exemple de formule de filtre. Bien que ce soit une bonne chose, comment puis-je le prendre et le tourner verticalement ? Eh bien, vous pouvez l'utiliser avec la fonction de transposition. C'est ce que je fais ici. Encore une fois, je vais le montrer à partir de zéro. Nous allons dire x, bonnet de poitrine dans la colonne des noms. Ne prenons pas toute la table. Au lieu de cela, examinons uniquement le sexe par le biais du salaire. Il s'agit de la colonne de retour, et nous obtiendrons quatre résultats qui s'afficheront à l'écran. Maintenant, au lieu de parcourir l'écran, nous pouvons utiliser la fonction de transposition au début et envoyer les résultats de la coupe à la fonction de transposition Ce que fait la transposition, c'est qu'elle prendra un tas de valeurs et changera l'orientation Si vous lui envoyez un tas de lignes, il les transformera en colonnes, et vice versa. Ici, la transposition prend cela et elle les retournera puis les affichera sur l'écran Voyons maintenant que cela s'applique à un format de données différent. Ici, j'ai obtenu mes noms, puis chaque mois, les valeurs des ventes sont répertoriées sous forme de matrice. Et je veux savoir, pour un prénom, quel est le Q q total. Q q ici, ce serait en octobre, novembre, décembre. Voici ma formule de coupe XL. Somme de la tasse Kimpton Andrea Kimpton dans la colonne des noms, puis nous voulons que les résultats proviennent des colonnes d'octobre à proviennent des colonnes d'octobre Ensuite, une fois que la tasse donne ces trois valeurs, nous les résumons. Vous pouvez également l'utiliser avec la bonne vieille boucle comme celle-ci. Vous diriez la somme de la recherche Andrea Kimpton sur le tableau des ventes, et le résultat devrait être de 11, 12 et 13 colonnes entre crochets. Et puis tu diras faux. Dans cette situation, la boucle renverra les trois valeurs sous forme de liste, puis sum les résumera. Maintenant, si vous utilisez cette fonction dans Excel 365, il vous suffit d'appuyer sur Entrée. Mais dans une ancienne version d'Excel, vous pouvez toujours utiliser cette construction, mais vous devez appuyer sur Ctrl, Shift, Enter pour obtenir le résultat. Je vous reverrai dans la prochaine vidéo. 10. Combinez deux tables (consolation) aux Lookups: Dans cette leçon, nous allons comprendre comment combiner deux tables à l'aide de la formule de recherche. Ici, j'ai un ensemble de données légèrement plus long sur les employés avec 1 000 employés, et nous avons plusieurs colonnes d'informations sur les employés. Mais nous avons également une autre partie du puzzle : leur nom et leur date de naissance sont disponibles dans un tableau séparé, et nous aimerions fusionner les deux pour créer une vue combinée. Ici, par exemple, je peux dire la date de naissance, puis utiliser la recherche up ou x. Nous allons donc dire X, ma valeur de recherche est le nom, puis le tableau de recherche est la colonne de nom sur le DOBStable et la colonne de retour est ma date de naissance DOBS Et lorsque vous le fermerez, vous obtiendrez des valeurs, des endroits où l' employé n' a pas de date de naissance correspondante, vous obtiendrez une erreur. Corrigons d' abord l'erreur afin de pouvoir effacer ou de mettre autre chose comme une date de naissance manquante, puis de sélectionner cette colonne entière, y appliquer rapidement un format de date, et nous aurons nos informations de date de naissance disponibles. Voici comment vous pouvez utiliser la recherche Vp ou X pour combiner deux tables. Maintenant, compliquons un peu les choses. Et si votre table de date de naissance ne contient pas le nom, mais contient le nom de famille et le prénom dans deux colonnes distinctes et la valeur de date de naissance. Voici comment vous pouvez procéder. Ajoutez une nouvelle colonne. Appelons cela DO 2. Dans cette colonne, nous allons écrire une formule de recherche afin pouvoir prendre le nom complet ici et le comparer à la combinaison du nom de famille et du prénom qui s' comparer à la combinaison du nom de famille et du y trouve. Notons ce nom stable. Stable s'appelle DOB S two. Nous pouvons écrire la formule ici directement maintenant, la valeur X Loop est mon nom. tableau de recherche doit être la combinaison des colonnes de nom rapide et de nom de famille de mes deux tables DOBS Il s'agirait de DOB, fname, esperluette, puis du nom de famille DOBS two Remarquez qu'avec le para Luka lui-même, nous prenons deux colonnes différentes de cette table et les combinons, puis la colonne de retour doit être DOBS deux dates de naissance Ensuite, si aucune valeur n'est trouvée, nous imprimerons simplement une valeur vide et nous obtiendrons à nouveau le résultat. Appliquons un peu de formatage. Vous pouvez appliquer le formatage des dates depuis chez vous ici, mais voici un raccourci que j'aime normalement utiliser. J'aime utiliser Control Shift Three pour transformer rapidement les valeurs en format de date avec le format DMM y. L'idée d'utiliser deux colonnes et de les combiner ici est similaire à l'idée de colonnes dérivées que vous avez vue dans une autre vidéo. Ces deux approches vous offrent un moyen puissant de combiner des données même si le formatage n'est pas cohérent. J'espère que cela vous a été utile. Je vous reverrai dans une autre vidéo. 11. Extraction des données avec des visionnages de Big Big Table: Dans cette leçon, je vais vous montrer comment extraire quelques colonnes et lignes d'un grand jeu de données. J'ai donc obtenu des données sur 1 000 employés, et nous avons plusieurs colonnes d'informations, donc tout va jusqu'à la colonne CD. Et tout cela est créé au hasard, mais ce type d'ensembles de données est assez courant En fait, j'ai dû utiliser une technique similaire chez un client à plusieurs reprises au cours de l'année dernière. Alors, comment s'y prendre ? Permettez-moi d'abord de vous expliquer ce qui se passe ici. Je peux sélectionner autant de colonnes que je le souhaite. Je peux les spécifier ici, les numéros de colonne que je souhaite extraire, puis je peux donner les noms, et cette partie me montrera les données pertinentes. Par exemple, ici, au lieu de deux, je veux la colonne numéro trois, j'en mets trois, et instantanément, j'obtiens le titre du poste, puis le titre est imprimé ici. Je peux modifier cet ordre. Ils n'ont pas besoin d'être comme ça. Ainsi, par exemple, après trois, je peux en avoir deux, puis sept, puis un, et je vais obtenir les détails comme indiqué ici. Alors, comment s'y prendre ? Tout d'abord, configurez vos données d'origine dans un tableau. Il n'est pas nécessaire que ce soit sur une table, mais le fait de l'avoir dans une table vous simplifie la vie. Donc, dans notre cas, je l'ai mis dans le tableau intitulé « personnel ». Et maintenant, nous passons à la page d'extrait. C'est là que nous aimerions extraire. Le strict minimum dont vous avez besoin est le nom ou l'identifiant unique. Donc, dans ce cas, mes noms figurent dans la colonne C. Et puis je dois également identifier les colonnes que je veux extraire dans quel ordre ? Alors imprimez-les à l'écran en cours de route comme ceci. Et maintenant, pour la première ligne, nous allons utiliser la formule d'index pour obtenir l'en-tête de deuxième colonne, l'en-tête septième colonne, l'en-tête de sixième colonne, et les y imprimer. Cette formule d'index ressemble à cet index des en-têtes de hachage de ma table du personnel Cela vous donnera donc accès à toutes les informations d'en-tête de la table du personnel Et puis l'en-tête que je veux ici est le numéro deux, donc nous allons le pointer du doigt et fermer le crochet. Je vais chercher l'en-tête la deuxième colonne, qui est le genre, puis nous le faisons simplement glisser côté pour voir les en-têtes pertinents pour Maintenant vient le moment où nous devons l'extraire. Je vais simplement le supprimer et nous allons écrire la formule. Nous voulons donc voir en X cette valeur dans la colonne du nom même. Nous allons donc dire le nom du personnel. Mais que retournons-nous ? Maintenant, la colonne que nous devons renvoyer serait deux ici parce que c'est la colonne du genre, mais il y en aurait sept ici, six là, 12 là. La colonne que nous voulons est donc dynamique. C'est là que la fonction d'index est utile. Nous dirons index de la table du personnel. Nous voulons toutes les lignes, donc nous allons simplement dire a pour indiquer que je ne veux pas une ligne spécifique, je veux toutes les lignes. Mais le numéro de colonne que je veux doit être celui-ci, nous allons donc le pointer vers D quatre. Maintenant, vous devez faire de certaines de ces références des références mixtes. Ce sera toujours dans la ligne numéro quatre. Je vais juste le remplacer par un dollar D quatre. De même, cela doit toujours être dans la colonne C, je vais donc remplacer cette référence par le dollar C sept. Encore une fois, pour modifier ces styles de référence, placez votre curseur à cet endroit et maintenez la touche F 4 enfoncée jusqu'à obtenir le style souhaité. Une fois cela fait, nous fermons la formule de l'indice endroit, puis nous fermons le crochet. C'est ça. Nous allons obtenir le sexe Copiez cette formule, sélectionnez cette gamme complète, contrôlez, et vous obtiendrez les résultats ici d'une belle et belle manière. Tout cela est dynamique. En fait, lorsque j'ai créé quelque chose comme ça, l'un de mes collègues chez un client l'a vu, et il a été très impressionné qu'il en ait pris une copie, qu'il en ait créé un modèle de feuille de calcul vierge afin pouvoir l'utiliser pour faire extraits de paie chaque fois qu'il en avait besoin J'espère donc que vous avez trouvé cette technique utile. Désormais, vous n'êtes plus obligé d'utiliser X Loup. Vous pouvez également utiliser hep pour ce faire. Je n'explique pas cette partie ici car nous avons déjà longuement parlé du hop et de la recherche, mais n'hésitez pas à relever le défi et à écrire la formule de base de Vp ou à vous référer au fichier de téléchargement pour savoir comment procéder. J'espère que cette leçon vous a été utile. On se revoit dans le prochain. Au revoir. 12. 8 erreurs de recherche courantes et corrects pour les: Dans cette leçon, je vais parler de huit erreurs d'apparence dans les corrections Ces erreurs sont une erreur de valeur manquante, une erreur de valeur pas vraiment manquante , une réponse incorrecte, erreur de frappe, une erreur de données, une erreur de référence, une erreur de colonne, puis la formule ne fonctionnera pas Allons voir ce que c'est. L' erreur de valeur manquante est de loin l'erreur la plus courante que vous voyez lorsque vous utilisez la formule de recherche. Ainsi, par exemple, si j' utilise x lookup ou v lookup et que j'essaie de rechercher quelque chose qui n'existe pas. Donc, dans ce cas, je cherche Chandu dans colonne des noms de mon personnel et j'essaie d'obtenir le salaire de Chandu. Je vais obtenir le hachage A, qui est l'erreur courante que vous voyez lorsque la valeur Alors, comment corriger cette erreur. Dans XP, vous pouvez utiliser l'option introuvable pour résoudre ce problème. Par exemple, je peux dire « introuvable ici et cela imprimera le message « introuvable ». Dans Vp, la même formule devient quelque chose comme ceci. Nous recherchons donc cinq chutes du personnel de Chando, et ce sera une erreur, et nous pouvons utiliser la fonction if error pour éviter cela Lorsque l'aide contient une erreur, cela signifie qu'il s'agit d'une erreur, alors ce n'est pas notre employé qui sera le message, et elle l'imprimera. Pour le type d'erreur suivant, imaginez que nous ayons ici une valeur de bonel d'échecs, et je veux vraiment rechercher cette personne Nous allons donc utiliser we look up. Nous rechercherons le nom dans le tableau du personnel, puis leur sexe sera faux. Maintenant, nous pouvons voir que Chess Bonel est clairement là, mais nous avons une erreur C'est un peu comme une valeur, qui ne manque pas vraiment. C'est parce que nous pensons que c'est du jeu d'échecs. Mais lorsque vous modifiez la cellule, vous remarquez qu' il y a en fait un espace supplémentaire à la fin, ce qui pose problème. Alors, comment résoudre ce problème ? Eh bien, la solution numéro un est de supprimer l'espace, nous pouvons donc le supprimer et cela résoudra le problème. Mais si vous ne pouvez pas le modifier pour une raison quelconque, l'autre option consiste à utiliser une fonction telle que le découpage de vos ensembles de données d'entrée afin supprimer les espaces indésirables et d'obtenir le résultat correct. Cela fonctionnera avec Luka ou x. Le troisième type d'erreur est une réponse incorrecte Ici, vous pouvez voir que je cherche un bonel de poitrine mais que je comprends leur sexe en tant que femme Alors qu'ici dans le tableau, leur sexe indique un homme. Alors, qu'est-ce qui se passe ? En effet, lorsque vous utilisez la formule de recherche, si vous oubliez le dernier paramètre et que vous l'omettez, Excel le définit par défaut sur true, ce qui signifie qu'il recherchera une correspondance approximative. Cela signifie que cela partira du haut. Cela supposera que votre liste est triée par ordre alphabétique, elle recherchera donc Chess Bonel première personne est B, la deuxième est D. Excel suppose que parce que B puis C n' existent plus et que c'est D maintenant, je vais vous donner la réponse pour B, qui est une femme ici. Pour résoudre ce problème, vous devez définir le dernier paramètre comme faux. Une autre alternative consiste à utiliser x lookup, dans lequel vous n'avez même pas besoin de spécifier le type de mode de recherche. Ce sera toujours une recherche exacte. Cela vous donnera également la bonne réponse. Voici notre prochaine erreur de type d'erreur. Chaque fois que vous faites une faute de frappe dans votre formule, vous êtes plus susceptible de recevoir l'erreur de nom de hachage Ainsi, lorsque vous voyez l'erreur de nom de hachage, cela signifie que vous avez fait une sorte de faute de frappe Voici ma formule Xp. Tout semble correct, mais en y regardant de plus près, vous pouvez voir que nous avons mal orthographié la recherche X en tant que x LUK Dès que vous aurez résolu ce problème, il réglera ce problème. Un autre type de faute de frappe courant est de donner un nom erroné dans le tableau Qu'il s'agisse du tableau ou de la colonne elle-même, si vous avez mal orthographié, vous obtiendrez l'erreur de type Désormais, ces types de fautes de frappe sont faciles à détecter, car dès que vous essayez de faire une présentation, Excel affiche un message d'avertissement indiquant que vous essayez taper quelque chose qui n'a aucun sens C'est à ce moment-là que cela soulignera également que cette partie de la formule n' est pas significative. C'est très facile à attraper. Mais l'autre type d'erreur de frappe n'est pas facile à détecter et XL générera l'erreur de nom de hachage Le prochain type d' erreur que vous pourriez commettre est une erreur de données. Supposons que vous ayez un employé nommé Douty Strutle ici, et que nous voulions voir son département Je dis donc X L cette personne sur le nom du personnel, puis sur le département du personnel. Et puis nous obtenons une erreur de valeur de hachage. Qu'est-ce qui se passe ici ? En effet, lorsque vous observez vos données, vous remarquez que leur valeur de département est en fait une valeur de hachage Cela peut se produire assez fréquemment lorsque vous essayez de copier des données depuis un autre système ou une autre feuille de calcul, et s'il y a eu une erreur dans l'un des calculs, cela se répandra et s'affichera ici Maintenant, ce genre d' erreur est difficile à détecter. Si je mets, par exemple, le département du personnel, puis si ce n'est pas trouvé, c'est une erreur de valeur. Cela ne s'affichera toujours pas. Il vous donnera la valeur de hachage au lieu de ce message d'erreur spécifique En effet, techniquement, nous avons trouvé le Doughty Strutle, il se trouve que leurs données elles-mêmes contiennent une erreur. C'est ici que vous pouvez soit utiliser la fonction d'erreur, soit inspecter la qualité des données et corriger les éventuelles erreurs. Le type d'erreur de texte est une erreur de référence. C'est à ce moment que vous essayez de faire référence dans votre formule à quelque chose qui n'existe plus. Faisons-le avec une formule simple ici. Nous rechercherons le salaire de Doughty Strutle. Nom du personnel , puis salaire du personnel. Nous avons la réponse ici. Mais dès que je supprimerai cette colonne, je recevrai une erreur de hachage car je n' ai plus accès à cette colonne en particulier Je vais donc avoir l'erreur de hachage rah Ce type d'erreur est assez courant, en particulier si vous faites référence à des éléments d' une autre feuille de calcul et que vous fermez ce fichier ou des éléments similaires Lorsque l'erreur de hachage s'affiche, vous devez revenir en arrière et vérifier si vos formules contiennent les éléments dont elles ont besoin pour fonctionner septième erreur est une erreur de colonne, ce qui est très, très courant avec les formules p. Alors écrivons un aperçu ici. Nous avons obtenu Dotty Strutle, puis nous allons inscrire Dotty Strutle dans le tableau du personnel, puis nous voulons leur colonne salariale, qui est Et nous obtenons le bon résultat ici. salaire de Dotty est de 41 980, et c'est ce que nous obtenons ici Mais remarquez ce qui se passe si j' insère une colonne au milieu. Il n'est pas nécessaire que ce soit juste entre les deux salaires, mais cela peut être n'importe où. Tant que le salaire n'est plus dans la colonne numéro cinq, il est maintenant dans la colonne numéro six, cette valeur devient zéro car cette personne n' a aucune idée de ce qui vient de se passer à l'écran. Il fait toujours allègrement référence à la colonne numéro cinq au lieu de la déplacer à six. Donc, lorsque vous réalisez ce genre de choses, vous redeviendrez colmeror C'est très difficile à repérer car il n'y a aucune indication visuelle de ce qui s' est passé, et cela peut être très problématique, surtout lorsque vous travaillez en équipe où d' autres personnes ajoutent des colonnes ou déplacent des objets Encore une fois, c'est une autre raison pour laquelle vous devriez utiliser les fonctions XL cup. Je vais annuler les étapes et vous montrer ce qui se passe avec X Lup. X, je valorise le nom du personnel, le salaire du personnel, et nous aurons la même réponse ici et là. Mais si j'insère une colonne, celle-ci devient zéro, mais celle-ci vous donne toujours la bonne réponse car cela fait toujours techniquement référence à la colonne des salaires du personnel, et cela fonctionnera tant que personne n'a renommé la colonne. Cette dernière formule ne fonctionnera même pas. C'est une question très délicate . Je vais écrire la formule et ensuite vous verrez qu'elle ne fonctionnera pas. Si je dis X, nous rechercherons cette valeur. Nom du personnel, salaire du personnel. Présentateur, rien ne se passe, la formule reste telle quelle, comme s' il s'agissait d'une valeur de texte. Cela est dû au fait qu'ici, dans cette cellule particulière, le formatage a été défini sur le format texte. Maintenant, normalement, le formatage des cellules el serait soit général, soit numérique, soit date, soit monétaire Mais cette cellule, je l'ai déjà mise en texte. Soit vous le faites accidentellement, soit quelqu'un d'autre l'a peut-être fait dans un autre but. Mais lorsque vous avez une cellule en tant que formatage de texte, les formules que vous tapez dans cette cellule ne fonctionneront pas. Corrigez facilement, redéfinissez le formatage sur général, puis modifiez la cellule et appuyez sur Entrée, et maintenant la formule fonctionnera Voilà, huit erreurs de recherche et des solutions faciles à corriger. J'espère que vous avez trouvé cette leçon très utile dans l'ensemble de ce cours pour améliorer votre jeu en boucle et en X L up Merci beaucoup d'avoir regardé, et je vous retrouverai ailleurs.