Cynnwys
Mae'r tiwtorial bach hwn yn esbonio sut i wneud swyddogaeth VPR (VLOOKUP) achos-sensitif, yn dangos nifer o fformiwlâu eraill y gall Excel eu chwilio mewn modd achos-sensitif, ac yn nodi cryfderau a gwendidau pob swyddogaeth.
Mae'n debyg bod pob defnyddiwr Excel yn gwybod pa swyddogaeth sy'n perfformio chwiliad fertigol. Mae hynny'n iawn, mae'n swyddogaeth VPR. Fodd bynnag, ychydig o bobl sy'n gwybod hynny VPR nid yw'n sensitif i lythrennau, hy mae'r llythrennau bach a phriflythrennau yn union yr un fath ar ei gyfer.
Dyma enghraifft gyflym yn dangos yr anallu VPR adnabod cofrestr. Tybiwch mewn cell A1 yn cynnwys y gwerth “bil” a'r gell A2 – “Bil”, fformiwla:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
Bydd … yn atal ei chwiliad ar “bil” gan fod y gwerth hwnnw'n dod gyntaf yn y rhestr, ac yn tynnu'r gwerth o'r gell B1.
Yn ddiweddarach yn yr erthygl hon, byddaf yn dangos i chi sut i wneud VPR achos sensitif. Yn ogystal, byddwn yn dysgu ychydig mwy o swyddogaethau a all gyflawni chwiliadau achos-sensitif yn Excel.
Byddwn yn dechrau gyda'r symlaf - GWELD (LOOKUP) a SUMPRODUCT (SUMPRODUCT), sydd, yn anffodus, â nifer o gyfyngiadau sylweddol. Nesaf, byddwn yn edrych yn agosach ar y fformiwla ychydig yn fwy cymhleth MYNEGAI+CYDRADDOLDEB (MYNEGAI+MATCH), sy'n gweithio'n ddi-ffael mewn unrhyw sefyllfa a chydag unrhyw set ddata.
Mae swyddogaeth VLOOKUP yn sensitif i achosion
Fel y gwyddoch eisoes, y swyddogaeth arferol VPR yn ansensitif o ran achosion. Fodd bynnag, mae yna ffordd i'w wneud yn sensitif i achosion. I wneud hyn, mae angen ichi ychwanegu colofn ategol at y tabl, fel y dangosir yn yr enghraifft ganlynol.
Tybiwch mewn colofn B mae yna ddynodwyr cynnyrch (Item) ac rydych chi am dynnu pris y cynnyrch a'r sylw cyfatebol o'r colofnau C и D. Y broblem yw bod dynodwyr yn cynnwys llythrennau bach a phriflythrennau. Er enghraifft, gwerthoedd celloedd B4 (001Tvci3u) a B5 (001Tvci3U) yn wahanol yn achos y nod olaf yn unig, u и U yn y drefn honno.
Fel y gallwch ddychmygu, y fformiwla chwilio arferol
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
yn dychwelyd $ 90, ers y gwerth 001Tvci3u yn yr ystod chwilio yn gynharach na 001Tvci3U. Ond nid dyna sydd ei angen arnom, ynte?
I chwilio gyda swyddogaeth VPR yn Excel sensitif i achosion, bydd yn rhaid i chi ychwanegu colofn cynorthwyydd a llenwi ei gelloedd gyda'r fformiwla ganlynol (lle B yw'r golofn chwilio):
=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));"")
Mae'r fformiwla hon yn torri'r gwerth dymunol yn nodau ar wahân, yn disodli pob nod gyda'i god (er enghraifft, yn lle A yn 65, yn lle a cod 97) ac yna'n cyfuno'r codau hyn yn gyfres unigryw o rifau.
Ar ôl hynny, rydym yn defnyddio swyddogaeth syml VPR ar gyfer chwiliad achos sensitif:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Gweithrediad priodol y swyddogaeth VPR Mae achosion sensitif yn dibynnu ar ddau ffactor:
- Rhaid i'r golofn cynorthwyydd fod y golofn ar y chwith yn yr ystod y gellir ei gweld.
- Rhaid i'r gwerth rydych chi'n chwilio amdano gynnwys cod nod yn lle'r gwir werth.
Sut i ddefnyddio'r swyddogaeth CODE yn gywir
Mae'r fformiwla a fewnosodwyd i gelloedd y golofn ategol yn rhagdybio bod gan bob un o'ch gwerthoedd chwilio yr un nifer o nodau. Os na, yna mae angen i chi wybod y niferoedd lleiaf a mwyaf ac ychwanegu cymaint o nodweddion IFERROR (IFERROR) faint o nodau yw'r gwahaniaeth rhwng y gwerth byrraf a'r gwerth hiraf a chwiliwyd.
Er enghraifft, os mai'r gwerth chwilio byrraf yw 3 nod a'r hiraf yw 5 nod, defnyddiwch y fformiwla hon:
=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));"")
Ar gyfer swyddogaeth PSTR (MID) Rydych yn darparu'r dadleuon canlynol:
- dadl 1af - testun (testun) yw'r cyfeirnod testun neu gell sy'n cynnwys y nodau i'w hechdynnu (B2 yn ein hachos ni)
- dadl 2af - cychwyn_num (start_position) yw safle'r cyntaf o'r nodau hynny i'w hechdynnu. rydych chi'n mynd i mewn 1 yn y swyddogaeth gyntaf PSTR, 2 - yn yr ail swyddogaeth PSTR ac ati
- dadl 3af - nifer_chars (number_of_characters) - Yn pennu nifer y nodau i'w tynnu o'r testun. Gan mai dim ond 1 nod sydd ei angen arnom drwy'r amser, rydym yn ysgrifennu ym mhob swyddogaeth 1.
CYFYNGIADAU: swyddogaeth VPR nid dyma'r ateb gorau ar gyfer chwiliadau achos-sensitif yn Excel. Yn gyntaf, mae angen ychwanegu colofn ategol. Yn ail, mae'r fformiwla yn gwneud gwaith da dim ond os yw'r data'n homogenaidd, neu os yw union nifer y nodau yn y gwerthoedd a chwiliwyd yn hysbys. Os nad yw hyn yn wir amdanoch chi, mae'n well defnyddio un o'r atebion rydyn ni'n eu dangos isod.
Swyddogaeth LOOKUP ar gyfer chwiliad achos sensitif
swyddogaeth GWELD (LOOKUP) cysylltiedig VPR, fodd bynnag mae ei gystrawen yn caniatáu chwiliadau achos-sensitif heb ychwanegu colofn ategol. I wneud hyn, defnyddiwch GWELD wedi'i gyfuno â'r swyddogaeth EXACT (EXACT).
Os cymerwn y data o'r enghraifft flaenorol (heb golofn ategol), yna bydd y fformiwla ganlynol yn ymdopi â'r dasg:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Chwiliadau fformiwla mewn ystod A2: A7 cyfateb yn union â gwerth cell F2 achos sensitif ac yn dychwelyd y gwerth o golofn B yr un rhes.
Fel VPRswyddogaeth GWELD yn gweithio'n gyfartal â gwerthoedd testun a rhifol, fel y gwelwch yn y sgrinlun isod:
Pwysig! Er mwyn y swyddogaeth GWELD wedi gweithio'n gywir, dylid didoli'r gwerthoedd yn y golofn chwilio mewn trefn esgynnol, hy o'r lleiaf i'r mwyaf.
Gadewch imi egluro'n fyr sut mae'r swyddogaeth yn gweithio EXACT yn y fformiwla a ddangosir uchod, gan mai dyma'r pwynt allweddol.
swyddogaeth EXACT yn cymharu'r ddau werth testun yn y ddadl 1af ac 2il ac yn dychwelyd GWIR os ydynt yn union yr un fath, neu ANGHYWIR os nad ydynt. Mae'n bwysig i ni fod y swyddogaeth EXACT achos sensitif.
Gadewch i ni weld sut mae ein fformiwla yn gweithio GWELD + UNION:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- swyddogaeth EXACT yn cymharu gwerth celloedd F2 gyda phob elfen mewn colofn A (A2:A7). Yn dychwelyd TRUE os canfyddir cyfatebiaeth union, fel arall ANGHYWIR.
- Gan eich bod yn rhoi'r ddadl swyddogaeth gyntaf GWELD gwerth GWIR, mae'n tynnu'r gwerth cyfatebol o'r golofn benodedig (yn ein hachos ni, colofn B) dim ond os canfyddir cyfatebiaeth union, sy'n sensitif i achosion.
Rwy'n gobeithio bod yr esboniad hwn yn glir a nawr rydych chi'n deall y prif syniad. Os felly, yna ni fyddwch yn cael unrhyw anawsterau gyda swyddogaethau eraill y byddwn yn eu dadansoddi ymhellach, oherwydd. maent i gyd yn gweithio ar yr un egwyddor.
CYFYNGIADAU: Rhaid didoli'r data yn y golofn chwilio mewn trefn esgynnol.
SUMPRODUCT - yn canfod gwerthoedd testun, yn sensitif i lythrennau, ond yn dychwelyd rhifau yn unig
Fel y dealloch eisoes o'r teitl, SUMPRODUCT (SUMPRODUCT) yn swyddogaeth Excel arall a fydd yn eich helpu i wneud chwiliad achos-sensitif, ond bydd yn dychwelyd gwerthoedd rhifol yn unig. Os nad yw'r opsiwn hwn yn addas i chi, yna gallwch fynd ymlaen ar unwaith i'r bwndel MYNEGAI+CYDRADDOLDEB, sy'n rhoi ateb ar gyfer unrhyw achos ac ar gyfer unrhyw fathau o ddata.
Yn gyntaf, gadewch imi egluro cystrawen y swyddogaeth hon yn fyr, bydd hyn yn eich helpu i ddeall yn well y fformiwla sy'n sensitif i achosion sy'n dilyn.
swyddogaeth SUMPRODUCT yn lluosi elfennau'r araeau a roddwyd ac yn dychwelyd swm y canlyniadau. Mae'r gystrawen yn edrych fel hyn:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Gan fod angen chwiliad achos-sensitif, rydym yn defnyddio'r swyddogaeth EXACT (EXACT) o'r enghraifft flaenorol fel un o'r lluosyddion:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Fel y cofiwch, EXACT yn cymharu gwerth celloedd F2 gyda phob elfen mewn colofn A. Yn dychwelyd TRUE os canfyddir cyfatebiaeth union, fel arall ANGHYWIR. Mewn gweithrediadau mathemategol, mae Excel yn cymryd TRUE fel 1, a GAU ar gyfer 0ymhellach SUMPRODUCT yn lluosi'r rhifau hyn ac yn adio'r canlyniadau.
Nid yw sero yn cael eu cyfrif oherwydd wrth luosi maent bob amser yn rhoi 0. Gadewch i ni edrych yn agosach ar yr hyn sy'n digwydd pan fydd union gyfatebiaeth mewn colofn A dod o hyd a dychwelyd 1… swyddogaeth SUMPRODUCT yn lluosi'r rhif yn y golofn B on 1 ac yn dychwelyd y canlyniad – yn union yr un rhif! Mae hyn oherwydd bod canlyniadau'r cynhyrchion eraill yn sero, ac nid ydynt yn effeithio ar y swm canlyniadol.
Yn anffodus y swyddogaeth SUMPRODUCT methu gweithio gyda gwerthoedd testun a dyddiadau gan nad oes modd eu lluosi. Yn yr achos hwn, byddwch yn derbyn neges gwall #VALUE! (#VALUE!) fel mewn cell F4 yn y llun isod:
CYFYNGIADAU: Yn dychwelyd gwerthoedd rhifol yn unig.
MYNEGAI + MATCH – chwiliad achos-sensitif am unrhyw fath o ddata
Yn olaf, rydym yn agos at fformiwla chwilio anghyfyngedig a sensitif i achosion sy'n gweithio gydag unrhyw set ddata.
Daw'r enghraifft hon yn olaf, nid oherwydd bod y gorau yn cael ei adael ar gyfer pwdin, ond oherwydd y bydd y wybodaeth a gafwyd o'r enghreifftiau blaenorol yn eich helpu i ddeall y fformiwla sy'n sensitif i achos yn well ac yn gyflymach. MYNEGAI+CYDRADDOLDEB (MYNEGAI+MATCH).
Fel y mae'n debyg eich bod wedi dyfalu, y cyfuniad o swyddogaethau MWY AMLWG и MYNEGAI a ddefnyddir yn Excel fel dewis amgen mwy hyblyg a phwerus ar gyfer VPR. Bydd yr erthygl Defnyddio INDEX a MATCH yn lle VLOOKUP yn esbonio'n berffaith sut mae'r swyddogaethau hyn yn gweithio gyda'i gilydd.
Fe wnaf i ailadrodd y pwyntiau allweddol:
- swyddogaeth MWY AMLWG (MATCH) yn chwilio am werth mewn amrediad penodol ac yn dychwelyd ei safle cymharol, hynny yw, rhif y rhes a/neu golofn;
- Nesaf, y swyddogaeth MYNEGAI (MYNEGAI) yn dychwelyd gwerth o golofn a/neu res penodedig.
I fformiwla MYNEGAI+CYDRADDOLDEB gallu chwilio achos-sensitif, dim ond un swyddogaeth sydd angen i chi ei ychwanegu ato. Nid yw'n anodd dyfalu beth ydyw eto EXACT (UNION):
=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))
Yn y fformiwla hon EXACT yn gweithio yn yr un modd ag ar y cyd â'r swyddogaeth GWELD, ac yn rhoi'r un canlyniad:
Sylwch fod y fformiwla MYNEGAI+CYDRADDOLDEB wedi'i amgáu mewn braces cyrliog yn fformiwla arae a rhaid i chi ei gwblhau drwy wasgu Ctrl + Shift + Enter.
Pam mai INDEX+MATCH yw'r ateb gorau ar gyfer chwiliad achos-sensitif?
Prif fanteision y bwndel MYNEGAI и MWY AMLWG:
- Nid oes angen ychwanegu colofn ategol, yn wahanol i VPR.
- Nid oes angen i'r golofn chwilio gael ei threfnu, yn wahanol GWELD.
- Yn gweithio gyda phob math o ddata - rhifau, testun a dyddiadau.
Mae'r fformiwla hon yn ymddangos yn berffaith, onid ydyw? Mewn gwirionedd, nid yw. A dyna pam.
Tybiwch fod y gell yn y golofn gwerth dychwelyd sy'n gysylltiedig â'r gwerth chwilio yn wag. Pa ganlyniad fydd y fformiwla yn ei ddychwelyd? Nac ydw? Gawn ni weld beth mae'r fformiwla yn ei ddychwelyd mewn gwirionedd:
Wps, mae'r fformiwla yn dychwelyd sero! Efallai na fydd hyn yn broblem fawr os ydych chi'n gweithio gyda gwerthoedd testun pur. Fodd bynnag, os yw'r tabl yn cynnwys rhifau, gan gynnwys sero “go iawn”, mae hyn yn dod yn broblem.
Mewn gwirionedd, mae'r holl fformiwlâu chwilio eraill (VLOOKUP, LOOKUP, a SUMPRODUCT) a drafodwyd gennym yn gynharach yn ymddwyn yr un ffordd. Ond rydych chi eisiau'r fformiwla berffaith, iawn?
Gwneud achos fformiwla yn sensitif MYNEGAI+CYDRADDOLDEB perffaith, ei roi mewn swyddogaeth IF (IF) a fydd yn profi cell â gwerth dychwelyd ac yn dychwelyd canlyniad gwag os yw'n wag:
=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));"")
Yn y fformiwla hon:
- B yn golofn gyda gwerthoedd dychwelyd
- 1+ yn rhif sy'n troi safle cymharol y gell a ddychwelir gan y ffwythiant MWY AMLWG, i wir gyfeiriad y gell. Er enghraifft, yn ein swyddogaeth MWY AMLWG arae chwilio a roddir A2: A7, hynny yw, sefyllfa gymharol y gell A2 Bydd 1, oherwydd dyma'r un cyntaf yn yr arae. Ond sefyllfa wirioneddol y gell A2 yn y golofn yn 2, felly rydym yn ychwanegu 1i wneud iawn am y gwahaniaeth ac i gael y swyddogaeth INDIRECT (INDIRECT) adalw'r gwerth o'r gell a ddymunir.
Mae'r lluniau isod yn dangos y fformiwla achos-sensitif wedi'i chywiro MYNEGAI+CYDRADDOLDEB Ar waith. Mae'n dychwelyd canlyniad gwag os yw'r gell a ddychwelwyd yn wag.
Ailysgrifennais y fformiwla yn golofnau B:Di ffitio'r bar fformiwla ar y sgrinlun.
Mae'r fformiwla'n dychwelyd 0os yw'r gell a ddychwelwyd yn cynnwys sero.
Os ydych chi eisiau'r ddolen MYNEGAI и MWY AMLWG wedi dangos rhywfaint o neges pan fo'r gwerth dychwelyd yn wag, gallwch ei ysgrifennu yn y dyfyniadau olaf (“”) o'r fformiwla, er enghraifft, fel hyn:
=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.")
