Provectus VLOOKUP Exempla: Multi Criteria Quaerere

In secunda parte doceo de officio VPR (VLOOKUP) in Excel, pauca exempla resolvere quae omnem potestatem tuam adiuvabunt VPR solvere ambitiosissimas Excel munia. Exempla ponunt te iam fundamentalem cognitionem habere quomodo haec factura operatur. Sin minus, interesse incipias a prima parte huius paedagogiae, quae syntaxin et usum fundamentalem explicat. VPR. Bene, incipias.

• Quaerere in Praecedo pluribus criteriis
• VLOOKUP . extrahuntur 2nd, 3 etc
• Repete omnes repetitiones valoris desiderati
• XNUMXD quaesitum per ordinem notum et columnam
• Per plures VLOOKUPs in una formula
• Dynamic substitutio notitia ex diversis tabulis

Quaerere in Praecedo pluribus criteriis

muneris VPR in Excel instrumentum re validum est ad inquisitiones faciendas pro certo valore in database. Sed notabilis limitatio est - eius syntaxin permittit te ut unum tantum valorem quaeras. Quid si pluribus conditionibus quaerere vis? Solutionem infra invenies.

Exemplum I: Quaerere in 1 diversis indiciis

Ponamus nos habere ordinem ordinum et invenire volumus Quantitas bonorum (Qty.), ex duobus criteriis - Nomen clientis (Lorem) и productum nomen (Product). Materia autem multiplex est per hoc quod quilibet emptorum plura genera bonorum ordinavit, ut patet ex tabula infra;

regularis munus VPR in hac missione non laborabit quia primum valorem reddet invenit quod valoris aspectui dato compositus. Exempli gratia, si vis scire quantitatem rei dulcia'iussi ab emptore Hieremias Hillscribe hanc formulam;

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- Haec formula exitum reddet 15correspondentes productum applesquia illud primum valorem par.

Simplex labor est - additam columnam creare in qua omnes optatos criteriis coniungere. In exemplo nostro hae sunt columnae Nomen clientis (Lorem) и productum nomen (Product). Noli oblivisci columnam immersam semper debere esse columnae sinistrae in ambitu quaerendi, cum columna sinistra sit munus. VPR suspicit vultus valorem.

Ideo tabellae addis columnam auxiliarem et supra omnes cellas sequentem formulam exscribas: = B2&C2. Si chorda magis lectibilior esse vis, valores coniunctos cum spatio separare potes; =B2&» «&C2. deinde uti potes hac formula:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Ubi est cellula B1 valorem concatenatum ad argumentum continet lookup_value (lookup_value) et 4 - Argumentum col_index_num (column_number), ie numerus columnae continens notitias insanabiles.

Exemplum II: VLOOKUP duabus indiciis cum mensa inspectis in alio linteo

Si tabulam principalem (Main table) renovare debes, addendo notitias e mensa secunda (Tabulae lookup), quae in alia scheda vel in alia Exceli workbook posita est, tum in formula quam in formulam inserere potes, valorem desideratum colligere potes. in summa mensa.

Ut in exemplo praecedente, columna auxiliaris in tabula speculativa cum valoribus coniunctis indigebis. Haec columna debet esse columna sinistra in regione investigationis.

Ita formula cum VPR posset sic esse;

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Hic, columnae B et C nomina continentes et productorum nomina, respective et nexum Imperat!$A&$2:$D$2 definit mensam suspicere in alio scheda.

Ut formulam magis lectibilem efficias, nomen eminus prospectum dare potes, et tunc formula multo simplicior spectabit;

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Formulam ad operandum, valores in columna sinistra tabulae quam spectas, eodem prorsus modo componendae sunt ac in quaestionibus indiciis. In figura superius, valores u2bu2bandos inter eos spatium interponimus, eodem modo in primo functionis argumento facere debes (BXNUMX& "" & CXNUMX).

Memento! muneris VPR ad 255 characteres circumscriptos, valorem plus quam 255 characteres diu quaesitum esse non potest. Hoc observa et fac ut longitudo pretii desiderati hunc modum non excedat.

Assentior addens columnam auxiliarem non elegantissimam neque semper solutionem acceptam esse. Idem facere potes sine columna adiuvante, sed multo magis implicatam formulam requireret cum functionum complexione INDEX (Index) et PAR (Prius).

VLOOKUP . extrahuntur 2nd, 3 etc

Iam nostis VPR referre potest unum tantum valorem congruentem, pressius, primum inventum. Sed quid, si pluries iteratur haec utilitas in ordine spectato, et vis ex illis secunda vel tertia excerpere? Quid si omnia bona? Quaestio perplexa videtur, sed exstat solutio!

Si una columna tabulae contineat nomina clientium (Customer Name), et altera columna continet productos (Product) quos emerunt. Conemur invenire 2nd, 3 et 4 vasa data emptori empta.

Facillima via est ante columnam auxiliarem addere Customer Name et repetitis nominibus mos implent singulorum nominum, e.g. Ioannes Doe1, Ioannes Doe2 Dolum faciemus etc COUNTIF (COUNTIF), qui nomina emptoris in columna B sunt;

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Post hoc munus normale uti potes VPRut requiritur ordo. Exempli gratia:

• Find II-th item iussi a elit Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Find II-th item iussi a elit Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Re vera, cellam referentiam inire potes tamquam valorem vultus pro textu, ut sequenti figura ostenditur:

Si modo quaeris I-E iteratio facere potes sine columna auxiliaria, creando formulam magis implicatam;

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Hac formula:

• $F$2 - cellula in qua nomen emptoris (mutatum est, nota placet - nexus absolutus est);
• $ B $ - columnae Customer Name;
• Table4 – Mensa tua (locus etiam regularis esse potest);
• $C16 - cellam finis mensae tuae vel range.

Haec formula secundae tantum pretii congruens invenit. Si reliquas repetitiones extrahere debes, solutione antecedente utere.

Si opus est index omnium par - munus VPR Hoc adiutorium non est, cum tantum refert unum valorem ad tempus. Sed Excel munus habet INDEX (Index), qui facile hoc negotium obire potest. Quam formulam talem viderit, in exemplo sequenti disces.

Repete omnes repetitiones valoris desiderati

, Sicut tactum est supra, VPR omnes valores duplicatos ex range inspectis extrahi non possunt. Ad hoc, formula paulo implicatior opus est, quae pluribus muneribus Praecedo constat, ut INDEX (INDEX). PARVUS (Small) and * TRACTUS (LINE)

Exempli gratia, formula infra reperit omnes repetitiones valoris a cellula F2 in latitudine B2:B16 et redit ex eisdem ordinibus in columna C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Hanc intrant formulam ordinatam in cellas plures vicinas, veluti cellulas F4: F8ut patet in figura infra. Numerus cellularum debet esse aequalis vel maior quam maximus numerus repetitionum valoris quaesiti. Noli oblivisci ut click " + + Accidit Shift Intraad formulam recte ordinata.

Si quid opus est intelligere, in singula formulae aliquid intendere;

Pars 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 — comparare valorem in cellula F2 cum singulis valoribus in ambitu B2:B16. Si par inveniatur, tunc locutio NERVUS(C2:C16)-1 reddit numerum respondentem lineam (valorem -1 sino te linea header non includere). Si par non sunt, munus IF (IF) inane redit filum.

Munus exitum IF (IF) talis erit ordinatio horizontalis; {1,"",3,"",5,"","","","","","",12,"","",""}

Pars 2:

ROW()-3

СТРОКА()-3

Hic munus TRACTUS (LINE) Accedit quod occurro addito. Cum formula in cellulas F4:F9 exscripta sit, numerum subtrahemus 3 ex munus effectus ad valorem 1 in cellula F4 (Line IV, subtrahe III) ad 2 in cellula F5 et sic deinceps.

Pars 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

muneris PARVUS (Small) redit n-oh minimi pretii in notitia ordinata. In nobis, quae positio (ex minimis) reddere constituitur functione TRACTUS (line) (see Pars II). Itaque ad cellulam F4 officium PAULLULUM recurrit II-th (minimum) ordinata elementum, ie 1. Nam cellula F5 recurrit II-th minimum in acie, id est 3, Etc.

Pars 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

muneris INDEX (INDEX) simpliciter refert valorem cellulae specificae in apparatu C2:C16. Nam cellula F4 officium INDEX($C$2:$C$16) et revertetur applesquia F5 officium INDEX($C$2:$C$16) et revertetur dulcia' et sic porro.

Pars 5:

IFERROR()

ЕСЛИОШИБКА()

Demum formulam intra functionem ponimus IFEROR (IFERROR), quia tu es inopinabile delectabilis erroris nuntius #AT (#N/A) si numerus cellularum, in quas formula exscripta est, minor sit quam numerus valorum duplicatorum in latitudine visorum.

XNUMXD quaesitum per ordinem notum et columnam

Faciens XNUMXD inquisitionem in Excel involvit valorem quaerendo per numerum notum ordinis et columnae. Id est, cellam valorem extrahis in intersectione alicuius ordinis et columnae.

Itaque ad mensam nostram accedamus et formulam cum functione scribamus VPRquae notitias de pecunia lemons Martio vendito invenies.

Plures modi ad inquisitionem XNUMXD faciendae sunt. Reprehendo sicco optiones et elige unum quod tibi maxime placet.

VLOOKUP et IUGUM munera

Potes fasciculum functionum VPR (VLOOKUP) et PROJECTUS (CONPAR) invenire valorem in agrorum intersectione productum nomen (Filum) and * Month (Columna) ordinata in quaestionem:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Formula supra est regularis functionis VPRquae exactam par valoris "Lemons" in cellis A2 per A9. Sed quoniam nescis qua columna Martius venditio sit, non poteris columnae numerum pro tertia functione argumenti constituere. VPR. Sed munus adhibetur PROJECTUSdefinire hanc columnam.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

Interpretatio in linguam humanam, haec formula significat:

• Quaerimus ingenia "Mar" - argumentum lookup_value (lookup_value);
• Vultus in cellulis ab A1 ad I1 - argumentum lookup_array (lookup_array);
• Reversus exacte compositus - argumentum match_type " (match_type).

usura 0 in tertia ratione dicis functiones PROJECTUS quaere valorem primum qui prorsus congruit pretii quem quaeris. Hoc est equivalent ad valorem FALSUS Quartum argumentum VPR.

Hoc modo creare potes duos modos quaerendi formulam in Excel, etiam notas duas dimensiones inquisitionis vel inquisitionis bidirectionis.

SUMPRODUCT munus

muneris SUMPRODUCT (SUMPRODUCT) redit summa productorum delectorum vestit:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX et IUGUM munera

In proximo articulo has functiones singillatim exponam, nunc enim modo hanc formulam exscribere potes:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Nominatur iugis et operator intersectio

Si non in omnes illas formulas Excel implicatas, hoc visibile et memorabile fortasse placet:

1. Lego mensam, aperi tab Formulae (Formulae) and click Create from Electio (Cre ex legio).
2. Reprehendo boxes Top row (Per lineam supra) et Columna sinistra (in columna sinistra). Microsoft Praecedo nomina tribuet iugis valorum in summo ordine et columnae sinistrae tuae expansae. Nunc his nominibus quaerere potes sine formulas creando.
3. In cellula vacua aliqua scribe = row_name column_name, exempli gratia sic;

   = Lemons Mar

   ... vel vice versa:

   = Mar Lemons

   Memini nomina inter ordines et columnas spatio separari, quod in hoc casu operatur sicut operator intersectio.

Cum nomen ingredieris, Microsoft Praecedo instrumentum monstrabit cum indice vocabulorum adaptatorum, sicut cum formulam ingredieris.

4. Press Enter deprime effectus

In genere, quidquid praedictorum modorum libet, idem erit effectus inquisitionis duarum dimensivarum;

Per plures VLOOKUPs in una formula

Contingit ut mensa principalis et mensa speculatoria unam columnam in communi non habeant, et hoc impedit quominus functione consueta utaris. VPR. Alia tamen tabula est quae informationes quas interest non continet, sed communem cum tabula principali et specula mensa habet.

Inspice exemplum hoc. Main mensam habemus cum columna SKU (new)ubi vis columnam cum pretia ex alia tabula apponere. Praeterea habemus 2 tabulas lookup. Primum (Lookup tabula I) updated numeros continet SKU (new) and product names, and the second (lookup table 2) - product names and old numbers SKU (old).

Pretia addere ex tabula secunda lookup ad mensam principalem, actionem notam facere debes ut duplex VPR aut nested VPR.

1. Munus scribere VPR, quod nomen in mensa invenit Lookup mensam 1using SKUut desideratum valorem;

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   hic New_SKU - nomine range $A:$B in mensa Lookup mensam 1, 2 — haec est columna B, quae nomina bonorum continet.

2. Scribere formulam inserere pretium a mensa Lookup mensam 2 nota uber nominibus fundatur. Ad hoc faciendum, formulam crustulam quam vultus pretii novi functionis antea creasti VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   hic pretium - nomine range $A:$C in mensa Lookup mensam 2, 3 est columna C continens pretia.

Figura infra ostendit eventum redditum per formulam quam creavimus;

Dynamica substitutio notitiarum ex diversis tabulis utens VLOOKUP et INDIRECT

Primum, elucet quid per ly " substitutio dynamica notitiarum ex diversis tabulis," ut certo se mutuo recte intelligamus.

Casus sunt ubi plures schedae cum notitia eiusdem formae exstant, et necessarias informationes ex quadam scheda extrahere oportet, secundum valorem quod intratur in cellulam datam. Facilius puto hoc exemplo exponere.

Finge te habere venditio relationum plurium regionum cum eisdem productis et in eadem forma. Vis Sales figuras invenire in regione certa;

Si duas tantum relationes habes, turpiter simplicem formulam cum muneribus uti potes VPR и IF (IF) eligere optatam famam quaerere;

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

Ubi:

• I $ D $ est cella continens nomen productum. Nota quod hic notationibus absolutis utimur ad vitandam aspectum valorem, quando formulam aliis cellis imitatus est.
• $D3 est cellam cum nomine regionis. Utimur absoluta columna referente et ordine relativo quod in eadem columna formulam aliis cellulis imitari disponimus.
• FL_Sales и CA_Sales – nomina tabularum (vel iugis nominatarum) quae correspondentes venditionesque relationum continent. Utique potes, nominibus scheda solita uti et in notis cellularum notarum, exempli gratia 'FL Sheet'!$A$3:$B$10, sed iugis nomine multo commodius est.

Sed, cum multae tales tabulae sint, munus IF est optima solutio. Pro, functione uti potes INDIRECTUS (INDIRECT) ad optatam inquisitionem remittere.

Ut probabiliter scis, munus INDIRECTUS vinculum reddere adhibetur chorda textui data, quae prorsus nunc opus est. Audacter igitur repone in formula superiori elocutionis cum functione IF ad munus cum link INDIRECTUS. Hic est compositum VPR и INDIRECTUS magna facit;

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

Ubi:

• I $ D $ – haec cellula cum nomine producti, ob absolutam coniunctionem immutata est.
• $D3 est cellula in qua est prima pars regionis nomen. In exemplo nostro hoc FL.
• _Sales — nomen commune omnium iugis seu tabularum nominatorum. Composita cum valore in cella D3, efficit nomen bene idoneum ad range requisiti. Infra sunt quaedam singularia pro iis qui ad munus novum sunt INDIRECTUS.

Quam obliqua et VLOOKUP opus

Primum tibi syntaxin functionis admonere me INDIRECTUS (DIRECT);

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Prima argumentatio cellulam referentiam (A1 vel R1C1 stilum) esse potest, nomen rhoncus vel chorda textus. Secunda ratio determinat cuius modi nexus in primo argumento contineatur;

• A1si ratio est Verum Codex additicius (verum) vel non datum;
• R1C1, si FAS E * (FALSUM).

In nobis, nexus stilum habet A1ergo secundum rationem et primam intendere potes.

Redeamus igitur ad nostras venditiones rumores. Si meministi, singula referre tabulam separatam in scheda separata positam esse. Ad formulam recte operandi, tabulas tuas (vel regiones) nominare debes, et omnia nomina communem partem habere debent. Exempli gratia; CA_Sales, FL_Sales, TX_Sales et sic porro. Ut vides, "Sales" in omnibus nominibus adest.

muneris INDIRECTUS valorem in columna D coniungit et chorda textus "_Sales", ita narrans VPR in qua mensa quaerere. Si cellula D3 valorem "FL" contineat, formula mensam scrutabitur FL_SalesSi "CA" - in mensa CA_Sales et sic porro.

Effectus functionum VPR и INDIRECTUS erit haec:

Si notitia in diversis libris Excell sita est, nomen libri ante nominatum range addere debes, exempli gratia:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Si munus INDIRECTUS refertur ad aliud opusculum, quod opusculum apertum esse debet. Si clausum fuerit, munus errorem referet. #REF! (#SSYL!).