Cur INDEX et IUGUM meliores sunt quam VLOOKUP in Excel

Antea docuimus incipientibus quomodo functiones fundamentales VLOOKUP (latine VLOOKUP utere, abbreviatio pro "munus vultus verticalis" supponit. Utentes periti et formulae magis implicatae ostensae sunt.

Et in hoc articulo informationes dare conabimur de alia methodo operandi cum inquisitione verticali.

Miraris: “Cur hoc opus est?”. Et hoc necessarium est ut omnes methodos quaerendi demonstrare possint. Multae praeterea VLOOKUP restrictiones saepe optatum exitum obtinent. Qua in re INDEX ( ) CONPAR ( ) multo magis sunt functiones ac diversae , sed etiam restrictiones pauciores habent .

Basics INDEX

Cum huius ductoris propositi propositum sit ostendere quam bonum haec factura sit, nos Inspiciamus notitias fundamentales circa principia operationis. Et exempla ostendemus, et cur melius quam VLOOKUP consideremus ().

Index Function Syntaxis et Syntaxis

Hoc munus adiuvat ad valorem desideratum inveniendum in locis quaesitis determinatis in columna vel in numero linea. Syntax:

= INDEX(ordinatus, ordo numerus, columna numerus);

• ordinata – area in qua fient inquisitionis;
• line number - the number of the line to searched in the specified array. Si ordo numerus ignoratur, columnae numerus specificetur;
• numerus columnae - the number of the column to be found in the specified array. Si nescitur valor, linea numerus requiritur.

Exemplum simplex formulae;

= INDEX(A1:S10,2,3)

Munus in range ex A1 ad C10 quaeret. Numeri ostendunt quem ordinem (2) et columnam (3) desideratam ostendere valeat e. Fiet cellam C2.

Pulchellus simplex, vox? Sed cum documenta realia operaris, verisimile non es notitias habere de numerorum columnarum seu cellulis. Hoc illud est quod MENS () munus est.

CONPAR Function Syntax et Ritus

Munus par() munus desideratum valorem perquirit et ostendit numerum proximum suum in area certa inquisitionis.

The searchpos() syntaxis similis haec est:

= IUGUM (valorem ad lookup, ordinata ad lookup, par genus)

• valorem quaerere - numerus vel textus invenitur;
• quaesita ordinata – area in qua investigatione fiet;
• par genus - specificat utrum valorem exactum quaerere an valores ei proximiores:
  • 1 (vel nullius valoris definiti) - redit valorem maximum aequalem vel minus quam valorem qui specificatur;
  • 0 - par indicat exactam cum valore quaesito. In compositione INDEX() CONPAR() feres semper exacto pare, sic scribimus 0;
  • -1 - Indicat minimum valorem maiorem quam vel aequalem valore in formula determinato. Genus fit descendendo.

Verbi gratia, in range B1:B3 New York, Paris, London commemorantur. Formula infra numerum 3 ostendet, quia London tertia in indice est.

= EXPOSITIO (Londini, B1: B3,0)

Quomodo operari cum indice officiorum par 

Probabiliter iam coepisti intellegere principium quo opus iuncturae harum functionum aedificatur. In brevi, tum Index () valorem desideratum inter certos ordines et columnas exquirit. Et COMPONO ostendit numeros horum valorum:

= INDEX (columna e qua valor redditur, IUGUM(value ad quaerendum, columna ad investigandum in, 0))

Difficulter adhuc intellectus quomodo operatur? Forsitan exemplum melius explicabit. Puta te indicem habere capitulorum mundi et eorum hominum;

Ut magnitudinem incolarum capitis cuiusdam inveniamus, exempli gratia, caput Iaponiae, hac formula utimur:

= INDEX(C2:C10, Iaponia, A2:A10,0))

explicandum:

• Munus spectat valorem - "Iaponia" in ordinata A2:A10 et numerus III redit, quia Iaponia tertium valorem in indice. 
• Hoc figure accedit adlinea numerusin indice formula et munus indicat ut ex hoc ordine valorem imprimat.

Fit itaque haec formula regula normae INDEX(C2:C10,3). Formula scrutatur ex cellulis C2 ad C10 et redit notitia e cellula tertia in hoc ambitu, id est, C4, quia turpis incipit a secundo ordine.

Nolo in formula praescribere nomen urbis? Deinde scribe in quavis cellula, dic F1 et utere ea ut referat in formula. Et tandem cum formula inquisitionis dynamicae:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Magni momenti! Numerus linearum in ordinata INDEX () idem esse debet cum versuum numero considerari ordinata in CONPAR(), aliter eventum malum habebis.

Exspecta paulisper, cur non modo formula VLOOKUP uteris?

= VLOOKUP(F1, A2:C10, 3, Falsa)

 Quid tempus terere conatur omnes has complexiones INDEX IUGUM figurare?

In hoc casu non refert quod ad usum. Hoc solum exemplum est intellegere quomodo INDEX() et CONPAR() functiones simul cooperantur. Alia exempla ostendent quaenam haec functiones valeant in condicionibus ubi VLOOKUP impotens est. 

Index IUGUM vel VLOOKUP

Cum definiendi qua quaerendi formula utendum est, multi consentiunt INDEX() et CONPAR() multo praestantiores sunt VLOOKUP. Multi tamen homines VLOOKUP adhuc utuntur (). Primo, VLOOKUP() simplicior est, secundo, utentes non plene intelligunt omnia commoda operandi cum INDEX() et CONPAR(). Absque hac scientia nemo consentit ut intricata ratio vacare studeat.

Hic sunt cardinis commoda index() et CONPAR() super VLOOKUP();

• Quaero a dextra ad sinistram. VLOOKUP() a dextra ad sinistram quaerere non potest, ergo valores quos quaeris semper in columna sinistra tabulae esse debent. Sed INDEX() et CONPAR() hoc sine difficultate tractare possunt. Hoc articulum indicabit tibi quid simile in usu sit: quam in laeva parte pretii invenire desideratum.

1. Salvus additio vel remotio columnarum. Formula VLOOKUP() ostendit eventus rectos cum columnas removendo vel addendo, quod VLOOKUP() numerus columnarum exactus eget ut bene sit. Nempe, ubi adduntur columnae vel ablatae sunt, etiam eorum numeros mutant. 

Et in indice formularum et SPONSUS columnarum ambitus specificatur, non singulae columnae. Quam ob rem, columnas tuto addere et removere sine ullo tempore formulam renovare potes.

1. Limites in voluminibus quaerendi non sunt. Cum usus VLOOKUP(), numerus criteria quaestionis excedere non debet 255 characteres vel #VALUE accipies! Si ergo notitia tua magnum numerum notarum continet, INDEX () et CONPAR() optima optio.
2. Princeps processus celeritatem. Si tabulae tuae parvae sunt, tunc abhorret quid differat. Sed si mensa contineat ordines centena vel millia, et proinde centena milia formularum, INDEX () et CONPAR () multo citius obibit quam VLOOKUP (). Hoc est quod Excel processit solum columnas in formula definitas, loco totam mensam dispensando. 

Perficientur ictum VLOOKUP() maxime notabile erit si officina tua magnum numerum formularum sicut VLOOKUP() et SUM continet (). VLOOKUP functiones separatas reprimuntur ut singulas valores in ordine ad portionem requirantur. Praecedo ergo ut processus ingentem copiam notitiarum habeat, et hoc opus signanter retardat.

Formulae Exempla 

Horum officiorum utilitas iam figurata est, ut ad partem iucundissimam progrediamur: applicatio cognitionis in usu.

Formula quaerendi a dextra ad sinistram

VLOOKUP, ut iam dictum est, hanc inquisitionis formam praestare non potest. Si igitur valores desiderati in columna sinistra non sunt, VLOOKUP() exitum non habebunt. Index () et IUGUM () functiones magis versatiles sunt, et locus valorum munus illis operandi non magnum habet.

Exempli gratia, ad sinistram tabulae nostrae partem columnae gradum adiiciemus, et explorare conamur quem gradum incolarum caput Patriae nostrae occupet.

In cell G1 scribemus valorem inveniendum, deinde utimur sequente formula ad quaerendum in ambitu C1:C10 et valorem respondentem ab A2:A10 redde;

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Promptus. Si hac formula pluribus cellulis uti cogitas, fac iugis absolutis utentes appellationibus (exempli gratia, figere $A$2: $A$10 et $C$2: 4C$10).

INDEX EXPOSITUS EXPOSITUS  columnas et ordines quaerere

In exemplis superioribus his functionibus usi sumus ut VLOOKUP substitueretur () ut valores redderet ex praefinito ordinum latitudine. Sed quid si vulvam vel biformem inquisitionem facere debes?

Complicatum sonat, sed formula talium calculorum similis est index () CONPAR() formulae, una tantum differentia; COMPOSITIO () formula bis utenda est. In primo versu est numerus obtinere, et iterum accipere numerum agminis;

= INDEX(ordinata, IUGUM (valorem verticalem quaerere, columna quaerere, 0), IUGUM (horizontalem valorem quaerere, ordinem quaerere, 0))

Intueamur infra mensam et conantur formulam INDEX () EXPRESSUS () EXPRESSUS () ut diam in certa regione ad annum eximium ostentaret.

Scopum terra est in cellula G1 (speciculi verticalis) et annus target in G2 cellae est (species horizontalis). Formula huius modi erit:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Quomodo haec formula operatur

Cum quibusvis aliis formulis complexis, facilius intelliges eas in singulas aequationes frangendo. Et tunc intelligere potes quid unumquodque munus agat;

• COMPAR(G1,A2:A11,0) — spectat ad valorem (G1) in latitudine A2:A11 et ostendit numerum huius valoris, in nostro casu est 2;
• SEARCH(G2,B1:D1,0) - spectat ad valorem (G2) in range B1:D1. Quo in casu 3 evenit.

Numeri inventi ordo et columna mittuntur ad valorem respondentem in formula:

= INDEX(B2:D11,2,3).

Quam ob rem habemus valorem qui est in cella in intersectione 2 versuum et 3 columnarum in ambitu B2:D11. Et formula indicat valorem desideratum, qui est in cella D3.

Quaere per multiplices condiciones cum indice et IUGUM

Si ducem nostrum VLOOKUP legeris (), multas quaerendi formulas probabiliter temptasti. Sed haec methodus quaerendi unam notabilem limitationem habet - necessitas ad columnam auxiliarem adde.

Sed bonum nuntium est Cum indice () et COMPONO () multas condiciones quaerere potes sine officina recensere vel mutare.

Hic est generalis multi- conditionis quaestionis formulam INDEX() COMPAR();

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

nota: haec formula una cum brevitate claviaturae adhibenda est CTRL+TRABEO+ENTER.

Puta debes invenire valorem quod quaeris secundum 2 condiciones: emptor и Opus.

Hanc formulam requirit sequentem:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

In hac formula, C2:C10 spatium est in quo exquisitio fiet, F1 — haec conditio, A2:A10 - teli comparare conditionem; F2 — conditio 2, V2: V10 - range ad comparationem conditionis II.

Noli oblivisci premere complexionem in fine operis formula CTRL + TRANSMUTO + ENTER - Praecedo automatice claudet formulam cum capreolis crispis, ut in exemplo ostensum est:

Si ordinatim formulam operi tuo uti non vis, alium INDEX () formulae addas et press ENTER, in exemplo erit:

Quomodo hae formulae operantur

Haec formula eodem modo ac vexillum INDEX () COMPAR() facit. Ad multiplices condiciones quaeras, simpliciter plures falsas et veras conditiones creas quae singulas conditiones rectas et non falsas repraesentant. Et tunc haec omnia pertinent ad elementa ordinata. Formula falsas et veras argumenta convertit ad 0 et 1, respective, et disponit ordinem ubi 1 est valorum congruens qui in chorda reperti sunt. COMPONO () valorem primum inveniet qui 1 compositus et eam ad formulam indices transmittens. Et rursus ex desiderata columna redibit valorem iam desideratum in linea determinata.

Formula sine ordine posita a facultate INDEX () eas per se attrectandi. Secundus INDEX () in formula falsus compositus (0), ita totam aciem cum valoribus illis ad COMPOSITUM transit () formulam. 

Haec post hanc formulam logicae prolixior explicatio est. Pro magis notitia legere articulum "Index IUGUM cum multis conditionibus".

MEDIUM INTER MAXIMUM ET MINIMUM, MAX et MIN in INDEX et COMPOSITUM

Praecedo proprias habet functiones speciales inveniendi averages, maximos, et minimos. Sed quid si notitias e cellae cum illis valoribus asciscere cupis? In hoc casu MEDIUM INTER MAXIMUM ET MIN cum INDEX et IUGUM utendum est.

INDEX IUGUM et MAX*

Ad valorem maximum in columna D invenire et in columna C exhibere, formula uti: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX IUGUM et MIN

Minimum valorem in columna D invenire et in columna C exhibere, hac formula utere;

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX and SERPENT

Invenire valorem mediocris in columna D et hunc valorem in C exhibere.

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Secundum quomodo notitia tua scripta est, tertium argumentum ad IUGUM () est vel 1, 0, vel -1;

• si columnae ordine ascendendo digestae sunt, pone 1 (tunc formula valorem maximum, qui minor est vel valor mediocris, computabit);
• si descendat talis, erit -1 (formula valorem minimum, qui mediocris major vel aequalis est);
• si lookup ordinata contineat valorem qui mediocris prorsus aequalis est, pone eum ad 0 . 

 In exemplo nostro, incolarum ordo descendendo sortitur, sic -1. Effectus autem Tokyo est, quia multitudo incolarum (13,189) proxima est pretii mediocris (000).

VLOOKUP() etiam tales calculi praestare possunt, sed tantum ut formula ordinata: VLOOKUP cum MEDIETATE, MIN et MAX.

INDEX IUGUM et ESND / IFERROR

Probabiliter iam animadversum est, si formula valorem desideratum invenire non potest, errorem eiicit # N / A. Reponere potes nuntius errorum vexillum cum aliquo magis notitiativo. Exempli gratia: argumentum pone in formula XNUMX in:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Hac formula, si notitias intraveris quae in mensa non sunt, forma certum nuntium tibi dabit.

Si vis omnes errores capere, excepto XNUMX in adhiberi possunt, IFEROR:

= IFEROR(INDEX(C2:C10,CONPAR(F1,A2:A10,0)), "Aliquid erravit!")

Sed memento errorum masking hoc modo utilem non esse, quia normae errores in formula violationes referunt.

Speramus te ducem nostrum condere ad munus utile utens INDEX.