Contents
Hoc parum doceo quomodo ad functionem VPR (VLOOKUP) casus-sensitivos, plures alias formulas ostendit quae Excel in casu sensitivo inquirere potest, et cuiusque functionis vires et debilitates ostendit.
Suspicor omnem usorem Excell notum quid functionis inquisitionis verticalis exerceat. Iustum est, munus est VPR. Sed pauci sciunt quod VPR non est casus sensitivus, id est casus inferior et superior notae ei identicae sunt.
Hic est velox exemplum demonstrandi impotentiam VPR recognosco mandare. Puta in cellula A1 habet valorem "libellum" et in cellula A2 - "Bill", formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... inquisitio eius "libellum" cessabit quia illa valor in album praecedit et valorem e cella extrahit B1.
Postea in hoc articulo, ostendam tibi quomodo facias VPR casus sensitivo. Praeterea pauca plura munera discemus quae inquisitiones casus-sensitivas in Excell.
Nos incipiet cum simplicissima - view (LOOKUP) et SUMPRODUCT (SUMPRODUCT), quae, proh dolor, plures habent notabiles limites. Deinde propius inspiciamus formulam paullo magis implicatam INDEX+MART (INDEX+CONPAR), quod in omni situ et cum quavis dataset emendate operatur.
VLOOKUP munus est causa sensitivo
Ut iam nostis, munus solitum VPR est casus insensibilis. Sed modus est ut sit causa sensitiva. Ad quod faciendum, columnam auxiliarem ad tabulam addere debes, ut sequenti exemplo ostensum est.
Puta in columna B sunt producti identificatores (Item) et vis extrahere pretium producti et commentarium e columnis respondentem C и D. Quaestio est, quod identifiers continet et lowercase et auto ingenia. Eg B4 (001Tvci3u) et B5 (001Tvci3U) solum in ultimis characteribus differunt; u и U respective.
Ut existimare potes, formula inquisitionis consuetae
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
et revertetur 90 $Quia valor 001Tvci3u in investigationis range ante quam 001Tvci3U. Sed quid opus est?
Ad munus quaerere cum VPR in casu excelso sensitivo, adiutorium columnam habebis et cellulas eius implebis hac formula (ubi B est columna vultus);
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Haec formula valorem desideratum in personas separatas frangit, characterem quemque cum suo codice reponit (exempli gratia, loco A ad LXV, pro a CIC 97) ac deinde hos codices in unico numerorum filo coniungit.
Postea simplici functione utimur VPR nam si sensitivo quaerere:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Propria operatio functionis VPR maiusculas minusculasque-casu dependet a duobus;
- Columna adiutrix debet esse columna sinistra in amplitudine spectabilis.
- Valorem quem quaeris pro charactere codicis loco valoris valoris debet continere.
Quomodo codice uti recte functionem
Formula cellulis columnae auxiliaris inserta ponit quod omnes valores inquisitionis tuae totidem characterum habeant. Sin minus, debes scire numeros minimos et maximos et tot lineamenta addere IFEROR (IFERROR) Quot characteres interest inter brevissimum et longissimum valorem quaesitum.
Exempli gratia, si minima quaestionis valor est 3 characteres et longissimum 5 characteres, hac formula utere:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Nam munus PSTR (MID) Haec argumenta praebes:
- Argumentum 1 - text (textus) est textus vel cellula referentia cum characteribus extrahendis (in nostro casu est B2).
- Argumentum 2 - start_num positio prima illorum ingenia extrahenda est. intratis 1 primum munus PSTR, 2 - in secundo munus PSTR etc.
- Argumentum 3 - num_chars (number_of_characteres) - Specificat numerum characterum ut ex textu eliciant. Cum tantum indigemus 1 charactere omni tempore, in omnibus functionibus scribimus 1.
LIMITATIONS: muneris VPR optima solutio non est per inquisitiones sensitivas in Excel. Primo requiritur adiectio columnae auxiliaris. Secundo, formula boni operis tantum facit, si notitia homogenea est, vel numerus notarum in valoribus quaesitis cognoscitur. Si hoc non est in casu tuo, melius est uti una solutione quam infra ostendemus.
LOOKUP munus pro casu sensitivo quaerere
muneris view (LOOKUP) related VPRtamen eius syntaxin permittit inquisitiones sensitivas non addito columna auxiliari. Ad hoc utere view combined per munus TRIBULO (exigo).
Si ex exemplo praecedente datas sumamus (sine columna auxiliaria), tunc haec formula negotium obibit;
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula exquirit in range A2 A7 prorsus par cum cellula pretii F2 casus sensitivus et valor reddit ex columna B eiusdem ordinis.
sicut VPRofficium view aeque laborat cum textu et valores numerorum, ut videre potes in tortor infra:
Magni momenti! Ut munus view recte operata, valores in columna in ascendendo ordine, id est a minimo ad maximum, disponantur.
Paucis exponam quomodo munus opera TRIBULO in formula supra posita, sicut hic est in cardo.
muneris TRIBULO comparat duos textus valores in 1 et 2 argumentis et redit VERA si prorsus eadem sint, vel FALSA si non sint. Aliquam sit amet enim nobis est TRIBULO casus sensitivo.
Videamus quomodo formula opera nostra VIEW+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- muneris TRIBULO confert cellula pretii F2 omnibus elementis in columna A (A2:A7). Vera redit si par exacta inveniatur, secus FALSA.
- Cum primum munus das argumentum view valorem VERUM, valorem respondentem e columna determinata (in nostro casu columna B) extrahit tantum si par exacta inventa est, sensitiva casu.
Spero hanc explicationem perlucidam esse et nunc summam notionem intelligis. Si ita est, difficultates non habebis cum aliis functionibus quas ulterius resolvemus, quia. omnes eadem ratione operantur.
LIMITATIONS: Notitia in columna spec- tari debet in ascendendo ordine.
SUMPRODUCT - valores textus invenit, sensitivos casus, sed solum numeros redit
Ut iam ex titulo; SUMPRODUCT (SUMPRODUCT) Aliud munus Praecedo est quod adiuvabit inquisitionem casus-sensitivam facies, sed solum valores numericos redde. Si haec optio tibi non convenit, statim ad fasciculum procedere potes INDEX+MARTqui dat solutionem pro quolibet casu et pro quolibet dato generum.
Primum syntaxin huius functionis breviter exponam, hoc melius ad intellegendum tibi proderit formulam sensitivam quae sequitur.
muneris SUMPRODUCT elementa multiplicat datarum vestit et redit summa rerum. Syntaxis similis haec est:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Cum opus est inquisitione sensitiva casu-sensitiva, functione utimur TRIBULO (EXACT) ex exemplo praecedente ut unus e multiplicatoribus;
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Ut meministi, TRIBULO confert cellula pretii F2 omnibus elementis in columna A. Vera redit si par exacta inveniatur, secus FALSA. In operationibus mathematicis, Excel sumit verum sicut 1et falsum pro 0Praeterea SUMPRODUCT hos numeros et summas multiplicat eventus.
Zeros non numerantur, quia multiplicati semper dant 0. Propius inspiciamus quid fit cum par exacta in columna A inveni et rediit 1… Officium SUMPRODUCT multiplicat numerum in columna B on 1 et redit eventus - prorsus totidem! Hoc est, quia effectus aliorum productorum nulla sunt, et summam consequentem non afficiunt.
Infeliciter munus SUMPRODUCT non potest operari cum valoribus et dates textibus prout multiplicari non possunt. In hoc casu errorem nuntium accipietis #VALUE! (#VALUE!) Sicut in cellula F4 in tabula infra:
LIMITATIONS: Tantum refert valores numerorum.
INDEX + CONPAR - case-sensitiva inquisitionis pro aliqua notitia type
Denique appropinquamus illimitata et sensitiva formula inquisitionis quae cum quacumque notitia copia operata est.
Ultimum hoc exemplum venit, non quod optimum mensae relictum sit, sed quia cognitio quae ex exemplis praecedentibus adepta est, melius et citius intelligas formulam sensitivam. INDEX+MART (INDEX+CONPAR).
Ut probabiliter suspicatus es, coniunctio functionum PROJECTUS и INDEX usus est in Excel ut flexibilior et potens Vel quia VPR. Articulus INDEX utens et IUGUM loco VLOOKUP perfecte explicabit quomodo haec munera cooperantur.
Ego iustus cardinis metent:
- muneris PROJECTUS (CONPAR) valorem in dato ambitu quaerit et relativum reddit locum suum, id est numerum versuum et/vel columnae;
- Deinde, munus INDEX (INDEX) Valorem reddit ex certa columna et/vel ordine.
Ad formulam INDEX+MART casus-sensitive quaerere potuit, tantum opus est ut unum munus ei addas. Non difficile est suspicari quid sit iterum TRIBULO (EXACTUS);
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
In hac formula TRIBULO operatur sicut cum officio viewatque idem facit;
Nota quod formula INDEX+MART capreolis crispis inclusum est formula ordinata et oportet eam complere premendo + + Accidit Shift Intra.
Cur INDEX+COMMODO optima solutio quaestionis sensitivo?
Praecipua utilitates fasciculi INDEX и PROJECTUS:
- Auxiliantem columnam non requirit, dissimilis VPR.
- Quaesitum agmen non requirit ut sorted possit, dissimilis view.
- Opera cum omnibus generibus notitiarum - numerorum, textuum et temporum.
Haec formula perfecta videtur, annon? Profecto non est. Et ideo.
Pone cellam in reditu pretii columnae, quae cum pretii speculari coniungitur, vacua est. Quid erit formula reditus? Nulla? Videamus quid revera formula redeat;
Oops, formula nulla redit! Hoc problema magnum esse non potest si cum bonis textus puris laboratis. Attamen, si mensa numeros continet, quos possidet zeros "realis", haec quaestio fit.
Re vera omnes aliae formulae speculationis (VLOOKUP, LOOKUP, ET SUMPRODUCT) antehac se habent similiter. Sed vis formulam perfectam, ius?
Ad formulam casu sensitivo INDEX+MART perfect, pone in munere IF (IF) qui cellam reditus cum valore probabit et exitum inanem reddet si inanis est;
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Hac formula:
- B est columna cum reditus values
- 1+ numerus est qui vertit situm relativum cellae a munere rediit PROJECTUSad veram cellam. Exempli gratia in nostro munere PROJECTUS quaerere ordinata data A2 A7id est, situs relativus cellæ A2 autem 1eo quod prius in apparatu s. sed ipsa positione cellae A2 in columna est 2sic addimus 1differentiam conficere et munus habere INDIRECTUS (INDIRECT) Recepit pretium desiderati cellae.
Picturae infra ostende formulam sensitivam correctam casus INDEX+MART Actum in. Frustra redit effectus si cellula vacua est.
Formam rescripsi in columnas B:Dformulam aptare in screenshot talea.
Formula redit 0si redditus cellula nulla contineat.
Si vis nexum INDEX и PROJECTUS nuntium aliquod ostendi cum valor reditus inanis sit, id scribere potes in ultimis sententiis formulae (""), exempli gratia, hoc modo:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
