Tout utilisateur qui fonctionne avec la fonction de recherche, après un moment compris qu'il n'est pas très pratique de travailler avec une fonction qui se sépare uniquement à droite de la colonne dans laquelle la valeur souhaitée est située. Les fonctions de la carte de recherche (correspondance) et de l'index (index) peuvent résoudre ce problème, mais en général, la formule sera beaucoup plus longue et plus difficile par rapport au temps plus simple.
Superstructure personnalisée rlookup.fonctionne comme une application, mais vous permet d'inverser la recherche. La fonction utilise simplement les méthodes d'index et le match dans VBA. Il est fourni dans un complément Excel pour une facilité d'utilisation.
Télécharger la superstructure
Enregistrez le fichier sur votre ordinateur et installez-le dans Excel en sélectionnant Fichier -\u003e Paramètres -\u003e Complément. Cliquez sur le bouton Va ..., dans la fenêtre qui s'ouvre, sélectionnez Aperçu. Sélectionnez le fichier enregistré et cliquez sur OK pour terminer l'installation. Après l'installation, le message "Reverse_Lookup" apparaît dans la liste des add-ons disponibles et la fonction Rlookup devient disponible dans tous les livres.
Description de la fonction
Les arguments de la fonction RLOOKUP sont les mêmes que pour la fonction FPR, voir Captures d'écran ci-dessous. Pour effectuer une recherche inverse, entrez simplement un nombre négatif dans l'argument "Col_index_num" (voir Exemples ci-dessous). La formule sera une recherche dans la direction à gauche.
Une des différences entre ces deux fonctions que si Intervalle_prise omis dans l'UPR, le résultat ne sera certainement pas. Cependant, l'inverse est true pour RLOOKUP, par défaut, cette fonctionnalité renvoie la valeur exacte. Encore une fois, cela est dû au fait que la plupart des utilisateurs souhaitent avoir une valeur à la suite de la recherche. Les valeurs d'erreurs renvoyées par la fonction RLOOKUP sont similaires à celles de retour à l'UPR.
Exemple de la fonction
Suivant données Excel Affiche les adresses des bureaux, des noms d'employés et de la position.
Pour répondre à cette demande "qui travaille le bureau B43?" Nous pouvons utiliser la fonction PRD:
UPR ("B43"; A1: C6; 2; Faux) renvoie "Dilbert".
Néanmoins, si nous voulons savoir: "Où est le bureau de Dilbert?", Alors l'arbitrage ne fonctionnera pas. Ici, il est utile à la fonction rlookup:
rlookup ("dilbert"; A1: B6, -2).
Remarque, dans les arguments, le numéro de colonne est indiqué par un nombre négatif -2, ce qui vous permet de rechercher dans la colonne de gauche.
En général, la fonction fonctionne dans les deux sens:
rlookup ("B43"; A1: C6; 2) renvoie "Dilbert" et
rlookup ("dilbert"; A1: B6; -2) renvoie "B43".
D'autres exemples sont indiqués ci-dessous:
N'oubliez pas d'installer d'abord le complément, sinon l'erreur reviendra. #Nom?.
Comment fonctionne la fonction
La fonction utilise des fonctions Société de recherche (correspondance) et index (index) Pour commencer la recherche inversée.
Le code source de la fonction voir ci-dessous.
Fonction rlookup (lookup_value, table_array comme plage, col_index_num comme entier, gamme optionnelle_lookup comme booléen)
"
"Fonction personnalisée qui permet des recherches verticales en avant et inverse
"Fonctionne comme VLookup pour un col_index_num positif et des recherches inverse pour le col_index_num négatif
"Contrairement à Vlookup, il cherche la valeur exacte par défaut
"
"Nitin Mehta, www.engineers-excel.com
"
"
Dim Source_Col comme plage, dest_col_num comme entier
"La colonne source est la colonne où se trouve la recherche_value. C'est la colonne la plus à gauche si
"Col_inex_number est positif. Si col_index_number est négatif, c'est la colonne la plus à droite
Set Source_Col \u003d Table_array.columns (iif (col_index_num\u003e 0, 1, table_array.columns.Count))
"Dest_Col_num est le numéro de colonne dans la gamme Table_Array de l'endroit où nous devons récupérer l'entrée contre la recherche_value.
Dest_col_num \u003d iif (col_index_num\u003e 0, col_index_num, table_array.columns.Count + col_index_num + 1)
«Utilisez les fonctions de correspondance et d'index pour la recherche.
"Utilisation de CVRRR (XLerrref) suggère par des cannes brian
Rlookup \u003d iif (dest_col_num< 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))
Fonction finale.
Laisse ton commentaire!
Beaucoup connaissent et utilisent souvent. Mais en même temps, elle a deux inconvénients importants, par exemple, comment faire le "PRD de gauche". J'utilise la EPR elle-même, seulement si vous devez faire quelque chose rapidement. Dans les fichiers pour "utilisation régulière, je fais un index de conception et une recherche. Qu'est-ce que c'est mieux?
- Lors de l'ajout d'une colonne à la table de données, vous n'avez pas besoin de modifier le numéro de colonne dans la formule elle-même (à la fois dans l'UPR). La colonne déplace la mitrailleuse
- Vous pouvez faire un arrangement au contraire, c'est-à-dire Faites un choix à partir de la table à droite. La première colonne doit toujours être à rechercher.
Comment faire tout cela, lire ci-dessous :)
L'utilisation sera un exemple de. Afin de mieux comprendre l'utilisation de cette conception par rapport à l'UPR:
Comment simplement remplir si vous voyez la formule pour la première fois?
Décidez d'abord où la table est initiale et où obtenir des données! Phases qui remplissent.
- Insérez la formule à la zone souhaitée de la table
- En place G: $ g, mettre ces cellules dans lesquelles la valeur doit être trouvée et, en conséquence, devrait apparaître à la suite. Nous recherchons le nom de famille, cela signifie à la recherche d'une colonne avec des noms de famille dans la table de départ.
- Remplacez $ J: J: J, en fonction desquels les valeurs doivent revenir à la cellule. Nous avons besoin de noms de famille en fonction du véhicule - insérez ces véhicules sur lesquels des valeurs doivent apparaître.
- Au lieu de $ H: $ H, remplissez la colonne dans laquelle vous devez trouver la valeur correspondante. Ceux. Nous recherchons le nom de famille du véhicule, puis insérez la colonne avec la CU dans la table d'origine.
Si vous êtes intéressé par ce que dans la formule est une formule $ signe
Index et recherche. Quelle est cette fonction?
L'indice et la planche de recherche sont des fonctions très fortes qu'en combinaison avec d'autres donnent un excellent résultat.
Index (tableau; Number_Name; Number_stolbits)
Renvoie la valeur à l'intersection de la ligne spécifiée et de la colonne d'une certaine gamme. Ceux. Initialement, cela fonctionne avec des tableaux bidimensionnels.
Sélection du tableau de données J1: K4 et définissez le numéro de ligne et une colonne à deux, nous avons obtenu la valeur appropriée.
En regardant la formule initiale
Index ($ G: $ g; comité de recherche ($ j: j; $ h: $ h; 0); 1)
Nous verrons qu'au lieu du deuxième argument (numéro de ligne), nous avons une formule de recherche de recherche. Qu'est ce qu'elle fait ici?
La société de recherche est une recherche par valeur. La fonction recherche une valeur spécifiée dans la chaîne ou dans la colonne et renvoie son numéro de séquence (du début de la plage). Ceux. Dans la deuxième fonction d'argumentation, l'index Nous trouvons le numéro du numéro de TC que nous recevons son numéro, par exemple 2.
Et déjà dans la matrice unidimensionnelle de $ G: $ g Nous trouvons une cellule avec un numéro numérique \u003d 2. Donc, il fonctionnera pour chaque cellule dans la colonne J.
Il ne semble donc pas être un design très difficile, mais comme je l'ai écrit ci-dessus très efficace. Parce que Vous n'avez pas besoin de modifier constamment le nombre de valeur souhaitée, comme dans l'UPR et que vous pouvez effectuer la recherche à droite et à gauche :)
Écrire des commentaires si les questions sont restées.
Comme toujours!
Partagez notre article dans vos réseaux sociaux:L'application Excel est un outil très pratique et fréquemment utilisé pour fonctionner avec des tables en tant que base de données et non seulement. Cette fonctionnalité Facile à apprendre et très fonctionnel lorsque vous effectuez.
Grâce à la combinaison harmonieuse de simplicité et de fonctionnalité, les utilisateurs sont activement utilisés dans le processus de travail avec feuilles de calcul. Mais il convient de noter que cette fonction a de nombreuses lacunes limitant les possibilités. Par conséquent, il doit parfois être utilisé avec d'autres fonctions ou pour remplacer plus complexe. Pour commencer, sur l'exemple final de l'application de la fonction, examinez ses avantages, puis nous définissons les lacunes.
Comment la fonction PRD fonctionne dans Excel: exemple
La fonction FEP est conçue pour sélectionner des données à partir de la table Excel pour certains critères de recherche. Par exemple, si la table se compose de deux colonnes: "Nom du produit" et "prix". À proximité, une autre table, qui recherchera dans la première table par nom de produit et obtiendra la valeur du prix correspondant.
Dans le champ "Valeur initiale", nous entrons un lien vers la cellule sous le nom du produit de la deuxième table D3. Dans le champ "Table", nous entrons dans la plage de toutes les valeurs de la première table A2: B7. Dans le champ "Numéro de colonne", nous entrons une valeur de 2, comme dans la deuxième colonne, nous avons le prix que nous souhaitons obtenir lors de la recherche de biens. Et cliquez sur OK.
Maintenant, sous la colonne de rubrique de la deuxième table "Produit", entrez les noms de ce produit dans lequel nous devons connaître son prix. Et appuyez sur Entrée.
La fonction nous permet de trouver rapidement des données et d'obtenir toutes les valeurs nécessaires à partir de grandes tables. On dirait que travailler avec des bases de données. Lorsque la base de données crée une requête et, en réponse, les résultats sont affichés, qui sont la réponse aux critères de la demande.
Fonction PRD dans Excel et deux tables
Une petite tâche compliquant en modifiant la structure et en augmentant la quantité de données dans le tableau. Développez la quantité de données de la première table en ajoutant des colonnes: "Janvier", "Février", "Mars". Nous écrivons le montant des ventes au premier trimestre, comme indiqué sur la photo:
Comme vous pouvez voir la deuxième table, vous devez également changer un peu de manière à ne pas perdre l'essence de la tâche.
Nous devons maintenant effectuer un échantillon de données à l'aide de la fonction FRP séparément par les marchandises et résumer les ventes pour le premier trimestre. Pour ce faire, allez à la cellule H3 et après avoir appelé la fonction, remplissez ses arguments comme suit:
- Valeur initiale: G3.
- Tableau: A2: E7. La gamme de notre table est élargie.
- Numéro de colonne: (3; 4; 5). Nous avons besoin d'utiliser une fonction à contacter simultanément sur plusieurs colonnes. La valeur de cet argument sera donc prise dans le tableau des crochets. Et les numéros de colonne doivent être répertoriés via un point-virgule.
- Vue intervalle: mensonge.
- Pour que les valeurs des colonnes sélectionnées soient résumées, la fonction complète doit être placée à l'intérieur de la fonction des sommes (). La formule entière dans son ensemble est la suivante: \u003d SUMS (VD (G3; A3: E6; (3; 4; 5); Faux)).
- Après avoir entré cette formule, appuyez sur la combinaison de touches: Ctrl + Maj + Entrée. Attention! Si vous n'appuyez pas sur la combinaison de ces touches, la formule fonctionnera à tort. Dans Excel, vous devez parfois effectuer des fonctions dans la matrice pour cela, vous devez utiliser les touches: Ctrl + Maj + Entrée lors de la saisie de fonctions. Ensuite, dans les formules de ligne, tous les contenus seront pris dans des supports bouclés "()", qui indique la mise en œuvre de la formule dans la matrice.
Entrez maintenant le nom de la marchandise dans la cellule G3, dans la cellule H3, nous recevons des ventes au premier trimestre de ce produit.
Il y a une comparaison de deux tables dans Fonction Excel L'arbitrage et dès que la coïncidence des données demandées est déterminée, leurs valeurs sont immédiatement substituées pour résumer la fonction de quantité. L'ensemble du processus est effectué cycliquement due à la gamme de fonctions comme en témoigne des accolades bouclées dans la rangée de formule.
Noter. Si vous entrez des crochets frisés extrêmes manuellement dans la chaîne de formule, cela ne conduira aucun résultat. Vous pouvez effectuer une fonction de tableau cyclique uniquement via une combinaison de raccourcis arrière: Ctrl + Maj + Entrée.
Il convient de noter que le principal inconvénient de la fonction PRD est le manque de capacité à choisir plusieurs valeurs de source identiques dans la demande.
En d'autres termes, si les valeurs de "poires", "Apple" sont répétées dans notre table, nous ne pourrons pas résumer toutes les poires et toutes les pommes. Pour ce faire, utilisez la fonction View (). Il est très similaire à l'EPR, mais peut bien fonctionner avec des tableaux dans les valeurs de la source.
La fonction EPR dans Excel permet aux données d'une table pour réorganiser les secondes cellules. Son nom anglais est Vlookup.
Très confortable et fréquemment utilisé. Parce que Match les gammes manuelles avec des dizaines de milliers d'articles est problématique.
Comment utiliser la fonction FPR dans Excel
Supposons que l'entreprise de production de conteneurs et d'emballages a été reçue par des matériaux dans un certain montant.
Le coût des matériaux est dans la liste de prix. Ceci est une table séparée.
Il est nécessaire de déterminer le coût des matériaux inscrits à l'entrepôt. Pour cela, vous devez remplacer le prix de la deuxième table dans la première. Et à travers la multiplication ordinaire, nous trouverons la volonté souhaitée.
Algorithme d'actions:
Cliquez sur OK. Puis «Étaler» la fonction dans toute la colonne: accrocher le coin inférieur droit et retirer. Nous obtenons le résultat souhaité.
Maintenant, trouver le coût des matériaux ne sera pas difficile: Quantité * Prix.
La fonction ERP a attaché deux tables. Si le prix est modifié, le coût des matériaux reçus à l'entrepôt (reçu aujourd'hui) changera. Pour éviter cela, utilisez l'insert spécial.
- Nous mettons en évidence une colonne avec des prix insérés.
- Bouton de souris droit - "Copier".
- Sans supprimer la sélection bouton de droite Souris - "insert spécial".
- Mettre une tique en face des "valeurs". D'ACCORD.
La formule dans les cellules disparaîtra. Seules les valeurs resteront.
Comparaison rapide de deux tables à l'aide de PRD
La fonction permet de comparer les valeurs dans d'énormes tables. Supposons que le prix a changé. Nous devons comparer les prix anciens avec de nouveaux prix.
Les données présentées de cette manière peuvent être comparées. Trouvez une différence numérique et pourcentage.
Fonction PRD dans Excel avec plusieurs conditions
Jusqu'à présent, nous n'avons proposé qu'une seule condition pour l'analyse - le nom du matériau. En pratique, il est souvent nécessaire de comparer plusieurs gammes avec des données et de sélectionner une valeur de 2, 3e, etc. Critères.
Table Par exemple:
Supposons que nous ayons besoin de trouver à quel prix carton ondulé d'Oao-Est a été apporté. Vous devez définir deux conditions pour rechercher le nom du matériel et par le fournisseur.
Le cas est compliqué par le fait que plusieurs articles proviennent d'un fournisseur.
Considérez la formule en détail:
- Ce que nous recherchons.
- Où nous recherchons.
- Quelles données prennent.
Fonction PRD et DISPOSITION
Supposons que certaines données de notre part soient effectuées sous la forme d'une liste déroulante. Dans notre exemple - "matériaux". Vous devez configurer la fonction pour que le prix apparaisse lors du choix du nom.
Premièrement, nous ferons la liste déroulante:
Maintenant, vous devez le faire pour que lorsque vous choisissez un certain matériau dans la colonne, le prix est apparu sur la figure correspondante. Nous mettons le curseur dans la cellule E9 (où le prix doit apparaître).
- Ouvrez le "maître des fonctions" et choisissez le PRD.
- Le premier argument est "la valeur souhaitée" - une cellule avec une liste déroulante. Tableau - Plage avec les titres de matériaux et les prix. Colonne, respectivement, 2. Fonction acquise suivant apparence: .
- Appuyez sur l'entrée et profitez du résultat.
Nous changeons le matériel - les changements de prix:
Donc, la liste déroulante dans Excel fonctionne avec la fonction PRD. Tout se passe automatiquement. Dans quelques secondes. Tout fonctionne rapidement et efficacement. Vous avez juste besoin de traiter cette fonctionnalité.
Batyanov Denis Les droits de l'auteur invité indiquent dans cet article sur la manière de trouver des données dans une table excel Et les extraire à un autre et ouvre également tous les secrets de la fonction de vue verticale.
Utilisation d'une fonction de colonne pour spécifier la colonne d'extraction
Si la table dans laquelle vous récupérez des données lorsque assistance, Il a la même structure qu'une table de référence, mais contient simplement un nombre plus petit de lignes, puis la fonction de colonne () peut être utilisée pour calculer automatiquement les numéros de colonnes récupérables. Dans le même temps, toutes les formules EPS seront les mêmes (ajustées au premier paramètre qui change automatiquement)! Veuillez noter que le premier paramètre de la coordonnée de la colonne est absolu.
Créer une clé composite via & "|" &
S'il est nécessaire de rechercher plusieurs colonnes en même temps, vous devez effectuer une clé composite pour rechercher. Si la valeur de retour n'était pas textuelle (comme ici, dans le cas du champ "Code"), et numérique, une formule plus pratique de Sumilimn en viendrait à cela et la clé de colonne composite n'en aurait pas besoin du tout.
Ceci est mon premier article pour Lifehaker. Si vous l'aimez, je vous invite à visiter mon site, ainsi que je suis heureux de lire les commentaires sur vos secrets d'utiliser la fonction FPR et celle-ci. Merci. :)
