Saturs
Šajā mazajā pamācībā ir izskaidrots, kā izveidot funkciju VPR (VLOOKUP) ir reģistrjutīgs, parāda vairākas citas formulas, kurās programma Excel var meklēt reģistrjutīgā veidā, un norāda uz katras funkcijas stiprajām un vājajām pusēm.
Es domāju, ka katrs Excel lietotājs zina, kura funkcija veic vertikālo meklēšanu. Tieši tā, tā ir funkcija VPR. Tomēr daži cilvēki to zina VPR nav reģistrjutīgs, ti, mazie un lielie burti tam ir identiski.
Šeit ir ātrs piemērs, kas parāda nespēju VPR atpazīt reģistru. Pieņemsim, ka šūnā A1 satur vērtību “rēķins” un šūnu A2 – “Rēķins”, formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… pārtrauks meklēšanu “rēķins”, jo šī vērtība ir pirmajā vietā sarakstā, un izņems vērtību no šūnas B1.
Vēlāk šajā rakstā es jums parādīšu, kā to izdarīt VPR reģistrjutīgs. Turklāt mēs apgūsim vēl dažas funkcijas, kas var veikt reģistrjutīgu meklēšanu programmā Excel.
Sāksim ar vienkāršāko - SKATĪT (LOOKUP) un SUMPRODUCT (SUMPRODUCT), kam diemžēl ir vairāki būtiski ierobežojumi. Tālāk mēs sīkāk aplūkosim nedaudz sarežģītāko formulu INDEX+MATCH (INDEX+MATCH), kas darbojas nevainojami jebkurā situācijā un ar jebkuru datu kopu.
Funkcija VLOOKUP ir reģistrjutīga
Kā jūs jau zināt, parastā funkcija VPR nav reģistrjutīga. Tomēr ir veids, kā padarīt to reģistrjutīgu. Lai to izdarītu, tabulai jāpievieno papildu kolonna, kā parādīts nākamajā piemērā.
Pieņemsim, ka kolonnā B ir preces identifikatori (Prece) un vēlaties no kolonnām izvilkt preces cenu un atbilstošo komentāru C и D. Problēma ir tā, ka identifikatoros ir gan mazie, gan lielie burti. Piemēram, šūnu vērtības B4 (001Tvci3u) un B5 (001Tvci3U) atšķiras tikai pēdējās rakstzīmes gadījumā, u и U attiecīgi.
Kā jūs varat iedomāties, parastā meklēšanas formula
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
atgriezīsies $ 90, jo vērtība 001Tvci3u atrodas meklēšanas diapazonā agrāk nekā 001Tvci3U. Bet tas nav tas, kas mums vajadzīgs, vai ne?
Lai meklētu ar funkciju VPR Excel reģistrjutīgajā gadījumā jums būs jāpievieno palīga kolonna un jāaizpilda tās šūnas ar šādu formulu (kur B ir uzmeklēšanas kolonna):
=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));"")
Šī formula sadala vēlamo vērtību atsevišķās rakstzīmēs, aizstāj katru rakstzīmi ar tās kodu (piemēram, tā vietā A vietā 65 a kods 97) un pēc tam apvieno šos kodus unikālā ciparu virknē.
Pēc tam mēs izmantojam vienkāršu funkciju VPR reģistrjutīgai meklēšanai:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Pareiza funkcijas darbība VPR reģistrjutība ir atkarīga no diviem faktoriem:
- Palīdzības kolonnai ir jābūt skatāmā diapazona vistālāk kreisājai kolonnai.
- Vērtībai, kuru meklējat, ir jāietver rakstzīmes kods, nevis reālā vērtība.
Kā pareizi lietot funkciju CODE
Papildu kolonnas šūnās ievietotā formula pieņem, ka visām jūsu meklēšanas vērtībām ir vienāds rakstzīmju skaits. Ja nē, tad jums jāzina mazākie un lielākie skaitļi un jāpievieno tik daudz funkciju IFERROR (IFERROR), cik rakstzīmju ir starpība starp īsāko un garāko meklēto vērtību.
Piemēram, ja īsākā meklēšanas vērtība ir 3 rakstzīmes un garākā ir 5 rakstzīmes, izmantojiet šo formulu:
=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));"")
Funkcijai PSTR (MID) Jūs sniedzat šādus argumentus:
- 1. arguments - teksts (teksts) ir teksta vai šūnas atsauce, kas satur izņemamās rakstzīmes (mūsu gadījumā tas ir B2)
- 2. arguments - sākuma_numurs (sākuma_pozīcija) ir pirmās izņemamās rakstzīmes pozīcija. tu ieej 1 pirmajā funkcijā PSTR, 2 – otrajā funkcijā PSTR un tā joprojām
- 3. arguments - rakstzīmju_skaits (rakstzīmju_skaits) — norāda no teksta izņemamo rakstzīmju skaitu. Tā kā mums visu laiku ir nepieciešama tikai 1 rakstzīme, mēs rakstām visās funkcijās 1.
Ierobežojumi: funkcija VPR nav labākais risinājums reģistrjutīgai meklēšanai programmā Excel. Pirmkārt, ir nepieciešams pievienot papildu kolonnu. Otrkārt, formula labi darbojas tikai tad, ja dati ir viendabīgi vai ir zināms precīzs rakstzīmju skaits meklētajās vērtībās. Ja tas nav jūsu gadījums, labāk ir izmantot kādu no tālāk norādītajiem risinājumiem.
LOOKUP funkcija reģistrjutīgai meklēšanai
funkcija SKATĪT (LOOKUP) saistīts VPR, tomēr tās sintakse ļauj veikt meklēšanu, izmantojot reģistrjutīgus datus, nepievienojot papildu kolonnu. Lai to izdarītu, izmantojiet SKATĪT apvienojumā ar funkciju TIKAI (PRECĪZI).
Ja mēs ņemam datus no iepriekšējā piemēra (bez papildu kolonnas), tad ar uzdevumu tiks galā ar šādu formulu:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula meklē diapazonā A2: A7 precīza atbilstība šūnas vērtībai F2 reģistrjutīgs un atgriež vērtību no tās pašas rindas B kolonnas.
tāpat VPRfunkcija SKATĪT darbojas vienādi ar teksta un skaitliskām vērtībām, kā redzams tālāk esošajā ekrānuzņēmumā:
Svarīgi! Lai funkcija SKATĪT darbojās pareizi, vērtības uzmeklēšanas kolonnā ir jākārto augošā secībā, ti, no mazākās uz lielāko.
Ļaujiet man īsi paskaidrot, kā šī funkcija darbojas TIKAI iepriekš parādītajā formulā, jo tas ir galvenais punkts.
funkcija TIKAI salīdzina abas teksta vērtības 1. un 2. argumentā un atgriež TRUE, ja tās ir tieši tādas pašas, vai FALSE, ja tās nav. Mums ir svarīgi, lai funkcija TIKAI reģistrjutīgs.
Apskatīsim, kā darbojas mūsu formula SKATĪT+PRECĪZI:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- funkcija TIKAI salīdzina šūnu vērtību F2 ar visiem elementiem kolonnā A (A2:A7). Atgriež TRUE, ja tiek atrasta precīza atbilstība, pretējā gadījumā atgriež FALSE.
- Tā kā jūs sniedzat pirmās funkcijas argumentu SKATĪT vērtība TRUE, tā izņem atbilstošo vērtību no norādītās kolonnas (mūsu gadījumā kolonnas B) tikai tad, ja tiek atrasta precīza atbilstība, reģistrjutīga.
Es ceru, ka šis skaidrojums bija skaidrs un tagad jūs saprotat galveno domu. Ja tā, tad jums nebūs nekādu grūtību ar citām funkcijām, kuras mēs analizēsim tālāk, jo. viņi visi strādā pēc viena principa.
Ierobežojumi: Uzmeklēšanas kolonnā esošie dati ir jāsakārto augošā secībā.
SUMPRODUCT – atrod teksta vērtības, reģistrjutīgs, bet atgriež tikai skaitļus
Kā jūs jau sapratāt no virsraksta, SUMPRODUCT (SUMPRODUCT) ir vēl viena Excel funkcija, kas palīdzēs veikt reģistrjutīgu meklēšanu, bet atgriezīs tikai skaitliskās vērtības. Ja šī opcija jums nav piemērota, varat nekavējoties pāriet uz komplektu INDEX+MATCH, kas sniedz risinājumu jebkuram gadījumam un jebkuram datu veidam.
Pirmkārt, ļaujiet man īsi izskaidrot šīs funkcijas sintaksi, jo tas palīdzēs jums labāk izprast formulu, kas ir reģistrjutīga.
funkcija SUMPRODUCT reizina doto masīvu elementus un atgriež rezultātu summu. Sintakse izskatās šādi:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Tā kā mums ir nepieciešama reģistrjutīga meklēšana, mēs izmantojam šo funkciju TIKAI (PRECĪZI) no iepriekšējā piemēra kā viens no reizinātājiem:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kā jūs atceraties, TIKAI salīdzina šūnu vērtību F2 ar visiem elementiem kolonnā A. Atgriež TRUE, ja tiek atrasta precīza atbilstība, pretējā gadījumā atgriež FALSE. Matemātiskajās operācijās Excel ņem TRUE kā 1, un FALSE for 0Tālāk SUMPRODUCT reizina šos skaitļus un summē rezultātus.
Nulles neskaita, jo reizinot tās vienmēr dod 0. Apskatīsim tuvāk, kas notiek, ja kolonnā ir precīza atbilstība A atrasts un atgriezts 1… Funkcija SUMPRODUCT reizina skaitli kolonnā B on 1 un atgriež rezultātu – tieši tādu pašu skaitli! Tas ir tāpēc, ka citu produktu rezultāti ir nulle, un tie neietekmē iegūto summu.
Diemžēl funkcija SUMPRODUCT nevar strādāt ar teksta vērtībām un datumiem, jo tos nevar reizināt. Šajā gadījumā jūs saņemsit kļūdas ziņojumu #VĒRTĪBA! (#VALUE!) kā šūnā F4 attēlā zemāk:
Ierobežojumi: Atgriež tikai skaitliskās vērtības.
INDEX + MATCH — reģistrjutīga jebkura datu veida meklēšana
Visbeidzot, mēs esam tuvu neierobežotai un reģistrjutīgai meklēšanas formulai, kas darbojas ar jebkuru datu kopu.
Šis piemērs ir pēdējais, nevis tāpēc, ka labākais paliek desertam, bet gan tāpēc, ka iepriekšējos piemēros iegūtās zināšanas palīdzēs labāk un ātrāk saprast reģistrjutīgo formulu. INDEX+MATCH (INDEX+MATCH).
Kā jūs droši vien uzminējāt, funkciju kombinācija VAIRĀK ATKLĀTA и INDEX izmanto programmā Excel kā elastīgāku un jaudīgāku alternatīvu VPR. Raksts INDEX un MATCH izmantošana VLOOKUP vietā lieliski izskaidros, kā šīs funkcijas darbojas kopā.
Es tikai atkārtošu galvenos punktus:
- funkcija VAIRĀK ATKLĀTA (MATCH) meklē vērtību noteiktā diapazonā un atgriež tās relatīvo pozīciju, tas ir, rindas un/vai kolonnas numuru;
- Tālāk funkcija INDEX (INDEX) atgriež vērtību no noteiktas kolonnas un/vai rindas.
Uz formulu INDEX+MATCH varētu meklēt reģistrjutīgi, jums ir jāpievieno tikai viena funkcija. Nav grūti uzminēt, kas tas atkal ir TIKAI (PRECĪZI):
=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))
Šajā formulā TIKAI darbojas tāpat kā saistībā ar funkciju SKATĪT, un dod tādu pašu rezultātu:
Ņemiet vērā, ka formula INDEX+MATCH cirtainajās iekavās ir masīva formula, un tā ir jāaizpilda, nospiežot Ctrl + Shift + Enter.
Kāpēc INDEX+MATCH ir labākais risinājums reģistrjutīgai meklēšanai?
Galvenās komplekta priekšrocības INDEX и VAIRĀK ATKLĀTA:
- Atšķirībā no papildu kolonnas nav jāpievieno VPR.
- Atšķirībā no meklēšanas kolonnas nav jākārto SKATĪT.
- Strādā ar visa veida datiem – cipariem, tekstu un datumiem.
Šī formula šķiet ideāla, vai ne? Patiesībā tā nav. Un tāpēc.
Pieņemsim, ka šūna atgriešanas vērtības kolonnā, kas saistīta ar uzmeklēšanas vērtību, ir tukša. Kādu rezultātu formula atgriezīs? Nē? Apskatīsim, ko formula faktiski atgriež:
Hmm, formula atgriež nulli! Tā var nebūt liela problēma, ja strādājat ar teksta vērtībām. Tomēr, ja tabulā ir skaitļi, ieskaitot “īstās” nulles, tas kļūst par problēmu.
Faktiski visas pārējās uzmeklēšanas formulas (VLOOKUP, LOOKUP un SUMPRODUCT), par kurām mēs runājām iepriekš, darbojas tāpat. Bet jūs vēlaties perfektu formulu, vai ne?
Lai formula būtu reģistrjutīga INDEX+MATCH ideāls, ievietojiet to funkcijā IF (IF), kas pārbaudīs šūnu ar atgriešanas vērtību un atgriezīs tukšu rezultātu, ja tā ir tukša:
=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));"")
Šajā formulā:
- B ir kolonna ar atgriešanas vērtībām
- 1+ ir skaitlis, kas pārvērš funkcijas atgrieztās šūnas relatīvo pozīciju VAIRĀK ATKLĀTA, uz šūnas reālo adresi. Piemēram, mūsu funkcijā VAIRĀK ATKLĀTA dots meklēšanas masīvs A2: A7, tas ir, šūnas relatīvais novietojums A2 griba 1, jo tas ir pirmais masīvā. Bet faktiskais šūnas stāvoklis A2 kolonnā ir 2, tāpēc pievienojam 1lai kompensētu atšķirību un veiktu funkciju NETIEŠAIS (NETIEŠĀ) izguva vērtību no vajadzīgās šūnas.
Tālāk redzamajos attēlos parādīta koriģētā reģistrjutīgā formula INDEX+MATCH Darbībā. Tas atgriež tukšu rezultātu, ja atgrieztā šūna ir tukša.
Es pārrakstīju formulu kolonnās B:Dlai ekrānuzņēmumā ietilptu formulas josla.
Formula atgriežas 0ja atgrieztajā šūnā ir nulle.
Ja vēlaties saiti INDEX и VAIRĀK ATKLĀTA parādījis kādu ziņojumu, kad atgrieztā vērtība ir tukša, varat to ierakstīt formulas pēdējās pēdiņās (“”), piemēram, šādi:
=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.")
