Edukiak
- VLOOKUP funtzioak maiuskulak eta minuskulak bereizten ditu
- LOOKUP funtzioa maiuskulak eta minuskulak bereizten dituen bilaketarako
- SUMPRODUCT - testu-balioak aurkitzen ditu, maiuskulak eta minuskulak bereizten ditu, baina zenbakiak soilik itzultzen ditu
- INDEX + MATCH - edozein datu motaren bilaketa maiuskulak eta minuskulak bereizten dira
Tutorial txiki honek funtzio bat nola egin azaltzen du VPR (BLOOKUP) maiuskulak eta minuskulak bereizten ditu, Excel-ek maiuskulak eta minuskulak bereizten dituen beste formula batzuk erakusten ditu eta funtzio bakoitzaren indarguneak eta ahuleziak adierazten ditu.
Uste dut Excel erabiltzaile bakoitzak badakiela zer funtziok egiten duen bilaketa bertikala. Hori bai, funtzio bat da VPR. Hala ere, jende gutxik daki hori VPR ez du maiuskulak eta minuskulak bereizten, hau da, letra xeheak eta maiuskulak berdinak dira.
Hona hemen ezintasuna erakusten duen adibide azkar bat VPR erregistroa ezagutu. Demagun zelula batean A1 "faktura" balioa eta gelaxka ditu A2 – «Faktura», formula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… "faktura"-n bere bilaketa geldituko du, balio hori zerrendan lehena baitago, eta balioa aterako du gelaxkatik B1.
Artikulu honetan aurrerago, nola egin erakutsiko dizut VPR maiuskulak eta minuskulak bereizten dira. Horrez gain, Excel-en maiuskulak eta minuskulak bereizten dituzten bilaketak egin ditzaketen funtzio gehiago ikasiko ditugu.
Sinpleenetik hasiko gara - VIEW (BILATU) eta SUMPRODUCT (SUMPRODUCT), eta horrek, tamalez, hainbat muga nabarmen dituzte. Ondoren, formula apur bat konplexuagoa aztertuko dugu AURKIBIDEA+BATEKIN (INDEX+MATCH), edozein egoeratan eta edozein datu multzorekin ezin hobeto funtzionatzen duena.
VLOOKUP funtzioak maiuskulak eta minuskulak bereizten ditu
Dagoeneko dakizuenez, ohiko funtzioa VPR maiuskulak eta minuskulak bereizten ditu. Hala ere, badago modu bat maiuskulak eta minuskulak bereizteko. Horretarako, zutabe laguntzaile bat gehitu behar diozu taulari, hurrengo adibidean erakusten den moduan.
Demagun zutabe batean B produktuen identifikatzaileak daude (Artikulua) eta zutabeetatik produktuaren prezioa eta dagokion iruzkina atera nahi dituzu C и D. Arazoa da identifikatzaileek letra xeheak eta letra larriak dituztela. Adibidez, gelaxken balioak B4 (001Tvci3u) eta B5 (001Tvci3U) azken karakterearen kasuan bakarrik desberdintzen dira, u и U hurrenez hurren.
Imajina dezakezun bezala, ohiko bilaketa-formula
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
itzuliko da $ 90, balioaz geroztik 001Tvci3u baino lehenago dago bilaketa barrutian 001Tvci3U. Baina ez da hori behar duguna, ezta?
Funtzio batekin bilatzeko VPR Excel-en maiuskulak eta minuskulak bereizten direnean, zutabe laguntzaile bat gehitu eta bere gelaxkak bete beharko dituzu formula honekin (non B bilaketa-zutabea den):
=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 honek nahi den balioa karaktere bereizietan zatitzen du, karaktere bakoitza bere kodearekin ordezkatzen du (adibidez, ordez A 65 urterekin, aldiz a 97 kodea) eta gero kode hauek zenbaki-kate bakar batean konbinatzen ditu.
Horren ondoren, funtzio sinple bat erabiltzen dugu VPR Maiuskulak eta minuskulak bereizten diren bilaketarako:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Funtzioaren funtzionamendu egokia VPR maiuskulak eta minuskulak bereiztea bi faktoreren araberakoa da:
- Zutabe laguntzaileak ezkerreko zutabea izan behar du ikusgai dagoen barrutian.
- Bilatzen ari zaren balioak karaktere-kode bat izan behar du benetako balioaren ordez.
Nola erabili CODE funtzioa behar bezala
Zutabe laguntzailearen gelaxketan txertatutako formulak zure bilaketa-balio guztiek karaktere kopuru bera dutela suposatzen du. Hala ez bada, zenbaki txikienak eta handienak ezagutu behar dituzu eta adina ezaugarri gehitu ERROREA (IFERROR) zenbat karaktere da bilatutako balio laburrenaren eta luzeenaren arteko aldea.
Adibidez, bilaketa-balio laburrena 3 karaktere bada eta luzeena 5 karaktere bada, erabili formula hau:
=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));"")
Funtziorako PSTR (MID) Argudio hauek ematen dituzu:
- 1. argumentua - testu (testua) atera beharreko karaktereak dituen testua edo gelaxka erreferentzia da (gure kasuan B2 da)
- 2. argumentua - hasiera_zenb (hasiera_posizioa) atera beharreko karaktere horietako lehenengoaren posizioa da. sartzen zara 1 lehenengo funtzioan PSTR, 2 – bigarren funtzioan PSTR eta abar.
- 3. argumentua - zenbaki_karaktere (karaktere_kopurua) - Testutik atera beharreko karaktere kopurua zehazten du. Karaktere bakarra behar dugunez denbora guztian, funtzio guztietan idazten dugu 1.
MUGAK: Funtzio VPR ez da Excel-en maiuskulak eta minuskulak bereizten dituzten bilaketak egiteko irtenbiderik onena. Lehenik eta behin, zutabe laguntzaile bat gehitu behar da. Bigarrenik, formulak lan ona egiten du datuak homogeneoak badira edo bilatutako balioetan karaktere kopuru zehatza ezagutzen bada. Hau ez bada zure kasua, hobe da behean erakusten ditugun irtenbideetako bat erabiltzea.
LOOKUP funtzioa maiuskulak eta minuskulak bereizten dituen bilaketarako
Funtzio VIEW (BIKETA) erlazionatuta VPR, hala ere bere sintaxiak maiuskulak eta minuskulak bereizten dituen bilaketak egiteko aukera ematen du zutabe laguntzailerik gehitu gabe. Horretarako, erabili VIEW funtzioarekin konbinatuta EXACT (ZEHAZ).
Aurreko adibideko datuak hartzen baditugu (zutabe laguntzailerik gabe), orduan hurrengo formulak zereginari aurre egingo dio:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Formula bilaketak barrutian A2: A7 bat etortze zehatza gelaxka-balioarekin F2 maiuskulak eta minuskulak bereizten ditu eta errenkada bereko B zutabeko balioa itzultzen du.
Like VPRfuntzioa VIEW testu eta zenbakizko balioekin berdin funtzionatzen du, beheko pantaila-argazkian ikus dezakezun bezala:
Garrantzitsua! Funtziorako VIEW behar bezala funtzionatu, bilaketa-zutabeko balioak goranzko ordenan ordenatu behar dira, hau da, txikienetik handienera.
Funtzioak nola funtzionatzen duen azalduko dut laburki EXACT goian agertzen den formulan, hori baita gakoa.
Funtzio EXACT 1. eta 2. argumentuetako bi testu-balioak alderatzen ditu eta EGIA itzultzen du berdinak badira, edo GEZURRA ez badira. Funtzioa garrantzitsua da guretzat EXACT maiuskulak eta minuskulak bereizten dira.
Ikus dezagun nola funtzionatzen duen gure formula IKUSI+ZEHAZ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Funtzio EXACT gelaxken balioa konparatzen du F2 elementu guztiekin zutabe batean A (A2:A7). EGIA ematen du bat-etortze zehatza aurkitzen bada, bestela GEZURRA.
- Lehenengo funtzioaren argumentua ematen duzunez VIEW EGIA balioa, zehaztutako zutabetik dagokion balioa ateratzen du (gure kasuan, B zutabea) bat-etortze zehatza aurkitzen bada, maiuskulak eta minuskulak bereizten badira.
Espero dut azalpen hau argia izatea eta orain ideia nagusia ulertzea. Hala bada, orduan ez duzu inolako zailtasunik izango gehiago aztertuko ditugun beste funtzioekin, zeren. denak printzipio berdinean lan egiten dute.
MUGAK: Bilaketa-zutabeko datuak goranzko ordenan ordenatu behar dira.
SUMPRODUCT - testu-balioak aurkitzen ditu, maiuskulak eta minuskulak bereizten ditu, baina zenbakiak soilik itzultzen ditu
Izenburutik jada ulertu duzun bezala, SUMPRODUCT (SUMPRODUCT) Excel beste funtzio bat da, maiuskulak eta minuskulak bereizten dituen bilaketa bat egiten lagunduko dizuna, baina zenbakizko balioak soilik itzuliko ditu. Aukera hau egokitzen ez bazaizu, berehala joan zaitezke sorta batera AURKIBIDEA+BATEKIN, edozein kasurako eta edozein datu motetarako irtenbidea ematen duena.
Lehenik eta behin, funtzio honen sintaxia labur-labur azalduko dut, honek ondoko formula maiuskulak eta minuskulak bereizten dituen hobeto ulertzen lagunduko dizu.
Funtzio SUMPRODUCT emandako matrizeen elementuak biderkatzen ditu eta emaitzen batura itzultzen du. Sintaxia honelakoa da:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Maiuskulak bereizten dituen bilaketa bat behar dugunez, funtzioa erabiltzen dugu EXACT (ZEHAZ) aurreko adibidetik biderkatzaileetako bat bezala:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Gogoratzen duzun bezala, EXACT gelaxken balioa konparatzen du F2 elementu guztiekin zutabe batean A. EGIA ematen du bat-etortze zehatza aurkitzen bada, bestela GEZURRA. Eragiketa matematikoetan, Excel-ek EGIA hartzen du 1, eta GEZURRA 0gehiago SUMPRODUCT zenbaki hauek biderkatzen ditu eta emaitzak batu ditu.
Zeroak ez dira zenbatzen, biderkatzean beti ematen baitute 0. Ikus dezagun zer gertatzen den zutabe batean bat-etortze zehatza denean A aurkitu eta itzuli 1… Funtzioa SUMPRODUCT zutabeko zenbakia biderkatzen du B on 1 eta emaitza itzultzen du - zehazki zenbaki bera! Hau da, beste produktuen emaitzak zero direlako, eta ez dutelako eragiten ondoriozko batura.
Zoritxarrez funtzioa SUMPRODUCT Ezin da testu-balioekin eta datekin lan egin, ezin baitira biderkatu. Kasu honetan, errore-mezu bat jasoko duzu #VALUE! (#BALIOA!) gelaxka batean bezala F4 beheko irudian:
MUGAK: Zenbakizko balioak soilik ematen ditu.
INDEX + MATCH - edozein datu motaren bilaketa maiuskulak eta minuskulak bereizten dira
Azkenik, edozein datu-multzorekin funtzionatzen duen bilaketa-formula mugagabea eta maiuskulak bereizten dituenetik gertu gaude.
Adibide hau azkena da, ez onena postrerako geratzen delako, baizik eta aurreko adibideetatik lortutako ezagutzak maiuskulak eta minuskulak bereizten dituen formula hobeto eta azkarrago ulertzen lagunduko dizulako. AURKIBIDEA+BATEKIN (AURKIBIDEA+PARTIDA).
Seguruenik asmatu duzun bezala, funtzioen konbinazioa ERABILERA GEHIAGO и INDEX Excel-en alternatiba malguagoa eta indartsuago gisa erabiltzen da VPR. VLOOKUP-en ordez INDEX eta MATCH erabiltzea artikuluak ezin hobeto azalduko du funtzio hauek elkarrekin nola funtzionatzen duten.
Puntu nagusiak laburbilduko ditut:
- Funtzio ERABILERA GEHIAGO (MATCH) barruti jakin bateko balio bat bilatzen du eta bere posizio erlatiboa itzultzen du, hau da, errenkada eta/edo zutabe zenbakia;
- Jarraian, funtzioa INDEX (INDEX) zehaztutako zutabe eta/edo errenkada bateko balio bat ematen du.
Formulatzeko AURKIBIDEA+BATEKIN Maiuskulak eta minuskulak bereizten ditu bilaketak, funtzio bakarra gehitu behar diozu. Ez da zaila berriro zer den asmatzea EXACT (ZEHATZA):
=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))
Formula honetan EXACT funtzioarekin batera egiten den moduan funtzionatzen du VIEW, eta emaitza bera ematen du:
Kontuan izan formula AURKIBIDEA+BATEKIN giltza kizkurren artean sartuta matrize formula bat dago eta sakatuz osatu behar duzu Ktrl + Shift + Sartu.
Zergatik da INDEX+MATCH irtenbiderik onena maiuskulak eta minuskulak bereizten dituen bilaketarako?
Sortaren abantaila nagusiak INDEX и ERABILERA GEHIAGO:
- Ez du zutabe laguntzailerik gehitzea eskatzen, ez bezala VPR.
- Ez du eskatzen bilaketa-zutabea ordenatu behar, ez bezala VIEW.
- Mota guztietako datuekin funtzionatzen du: zenbakiak, testuak eta datak.
Formula honek perfektua dirudi, ezta? Egia esan, ez da. Eta horregatik.
Demagun bilaketa-balioarekin lotutako itzulera-balioaren zutabeko gelaxka hutsik dagoela. Zein emaitza emango du formulak? Ez? Ikus dezagun formulak benetan zer itzultzen duen:
Aupa, formulak zero ematen du! Baliteke hori ez izatea arazo handia testu-balio hutsekin lan egiten baduzu. Hala ere, taulak zenbakiak baditu, zero "errealak" barne, arazo bat bihurtzen da.
Izan ere, lehen eztabaidatu ditugun beste bilaketa-formula guztiek (VLOOKUP, LOOKUP eta SUMPRODUCT) berdin jokatzen dute. Baina formula perfektua nahi duzu, ezta?
Formula bat maiuskulak eta minuskulak bereizteko AURKIBIDEA+BATEKIN perfektua, jarri funtzio batean IF (IF) balio itzultzeko gelaxka bat probatuko duena eta emaitza hutsa itzuliko duena hutsik badago:
=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));"")
Formula honetan:
- B itzultzeko balioak dituen zutabe bat da
- 1+ funtzioak itzultzen duen gelaxkaren posizio erlatiboa biratzen duen zenbakia da ERABILERA GEHIAGO, zelularen benetako helbidera. Adibidez, gure funtzioan ERABILERA GEHIAGO bilaketa array emandakoa A2: A7, hau da, zelularen posizio erlatiboa A2 izango 1, arrayko lehenengoa delako. Baina zelularen benetako posizioa A2 zutabean dago 2, beraz, gehitzen dugu 1aldea osatzeko eta funtzioa izateko ZEHARKOAK (ZEHARKOAK) nahi den gelaxkatik balioa berreskuratu du.
Beheko irudiek maiuskulak eta minuskulak bereizten dituzten formula zuzena erakusten dute AURKIBIDEA+BATEKIN Ekintzan. Emaitza huts bat ematen du itzulitako gelaxka hutsik badago.
Formula zutabeetan berridatzi dut B:Dpantaila-argazkian formula-barra egokitzeko.
Formula itzultzen da 0itzulitako gelaxkak zero badu.
Esteka nahi baduzu INDEX и ERABILERA GEHIAGO itzultzeko balioa hutsik dagoenean mezuren bat bistaratu du, formularen azken komatxoetan ("") idatz dezakezu, adibidez, honela:
=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.")
