Demagun zerrenda bat duzula, non, "zuzentasun" maila desberdinarekin, hasierako datuak idazten diren (adibidez, helbideak edo enpresen izenak):
 |  |
Argi ikusten da hiri edo enpresa bera dagoela hemen aldaera bitxietan, eta horrek, bistan denez, arazo asko sortuko ditu etorkizunean mahai hauekin lan egitean. Eta pixka bat pentsatzen baduzu, beste arlo batzuetako antzeko zereginen adibide asko aurki ditzakezu.
Orain imajinatu halako datu makurrak aldian-aldian etortzen zaizkizula, hau da, hau ez da "eskuz konpondu, ahaztu" istorio bat, aldizka eta gelaxka ugaritan dagoen arazo bat baizik.
Zer egin? Ez ordezkatu eskuz testu okertua 100500 aldiz zuzenarekin "Bilatu eta Ordeztu" laukiaren bidez edo klik eginez. Ctrl+H?
Egoera horretan burura etortzen den lehen gauza ordezkapen masibo bat egitea da, aurrez osatutako erreferentzia-liburu baten arabera, aukera okerrak eta zuzenak lotzen dituena, honela:
Zoritxarrez, zeregin horren agerikoa denez, Microsoft Excel-ek ez du hori konpontzeko metodo soil integratuta. Hasteko, asma dezagun nola egin hau formulekin, VBA edo Power Query-n makro moduan "artilleria astuna" inplikatu gabe.
1. kasua. Ordezko osoa
Has gaitezen kasu nahiko sinple batekin: testu oker zaharra beste berri batekin ordeztu behar duzun egoera. guztiz.
Demagun bi taula ditugula:
Lehenengoan - enpresen jatorrizko izenak. Bigarrenean, korrespondentzia-liburu bat. Lehenengo taulan enpresaren izenean zutabeko edozein hitz aurkitzen badugu Aurkitu, orduan guztiz ordeztu behar duzu izen oker hau zuzenarekin - zutabetik Ordezko bigarren bilaketa-taula.
Erosotasunerako:
- Bi taulak dinamiko bihurtzen dira ("adimentsua") teklatuko lasterbide baten bidez Ctrl+T edo taldea Txertatu – Taula (Txertatu — Taula).
- Agertzen den fitxan Constructor (Diseinua) izeneko lehenengo taula Data, eta bigarren erreferentzia-taula - Ordezkapenak.
Formularen logika azaltzeko, goazen apur bat urrutitik.
A2 gelaxkako lehen enpresa adibide gisa hartuta eta gainerako enpresak aldi baterako ahaztuta, saia gaitezen zutabetik zein aukera zehazten. Aurkitu bertan biltzen da. Horretarako, hautatu orriaren zati libreko edozein gelaxka huts eta sartu funtzioa bertan AURKITU (AURKITU):
Funtzio honek emandako azpikatea sartzen den zehazten du (lehen argumentua zutabeko balio guztiak dira Aurkitu) iturburu-testuan (datu-taularen lehen konpainia) eta testua aurkitu den karakterearen zenbaki ordinala atera beharko luke, edo errore bat azpikatea aurkitu ez bada.
Hemen trikimailua da lehen argumentu gisa balio bat ez, baizik eta hainbat zehaztu ditugunez, funtzio honek, ondorioz, balio bat ez, 3 elementuz osatutako array bat itzuliko duela da. Array dinamikoak onartzen dituen Office 365-en azken bertsioa ez baduzu, formula hau sartu eta klik egin ondoren. Sartu matrize hau orrialdean ikusiko duzu:
Excel-en aurreko bertsioak badituzu, sakatu ondoren Sartu emaitza-matrizeko lehen balioa bakarrik ikusiko dugu, hau da, #BALOIA errorea! (#BALIOA!).
Ez duzu beldurrik izan behar 🙂 Izan ere, gure formulak funtzionatzen du eta oraindik ere emaitzen sorta osoa ikus dezakezu formula-barran sartutako funtzioa hautatzen baduzu eta tekla sakatzen baduzu. F9(ez ahaztu sakatzeaz Escformulara itzultzeko):
Emaitza sorta horrek esan nahi du jatorrizko enpresaren izen okerrean (GK Morozko OAO) zutabe bateko balio guztien artean Aurkitu bigarrena bakarrik aurkitu zuen (Morozko), eta jarraian 4. karakteretik hasita.
Orain gehi diezaiogun funtzio bat gure formulari VIEW(BILATU):
Funtzio honek hiru argumentu ditu:
- Nahi den balioa - Nahiko kopuru handia erabil dezakezu (garrantzitsua da iturburu-datuen edozein testuren luzera gainditzen duela)
- Ikusi_bektorea – nahi dugun balioa bilatzen ari garen barrutia edo matrizea. Hona hemen aurretik sartutako funtzioa AURKITU, {#VALUE!:4:#VALUE!} matrize bat itzultzen duena
- Bektore_emaitzak – balioa itzuli nahi dugun barrutitik nahi den balioa dagokion gelaxkan aurkitzen bada. Hona hemen zutabeko izen zuzenak Ordezko gure erreferentzia-taula.
Hemen ezaugarri nagusia eta agerikoa ez den funtzioa da VIEW bat-etortze zehatzik ez badago, beti (aurreko) baliorik hurbilenaren bila dabil. Horregatik, nahi den balio gisa edozein zenbaki handi (adibidez, 9999) zehaztuz, behartuko dugu VIEW aurkitu {#VALUE!:4:#VALUE!} matrizean zenbakirik txikiena duen gelaxka (4) eta itzuli emaitza-bektoretik dagokion balioa, hau da, zutabetik enpresaren izen zuzena. Ordezko.
Bigarren ñabardura da, teknikoki, gure formula array formula bat dela, funtzioa delako AURKITU emaitza gisa ez bat, hiru balioko array bat itzultzen du. Baina funtzioaz geroztik VIEW Kutxaz kanpoko matrizeak onartzen ditu, orduan ez dugu formula hau matrize-formula klasiko gisa sartu beharrik - teklatuko lasterbidea erabiliz Ctrl+Shift+Sartu. Soil bat nahikoa izango da Sartu.
Hori da dena. Logika lortuko duzula espero.
Amaitutako formula zutabearen B2 lehen gelaxkara transferitzea geratzen da Fixed - eta gure zeregina konponduta dago!
Jakina, taula arruntekin (ez adimendunekin), formula honek ere ondo funtzionatzen du (ez ahaztu gakoa F4 eta dagozkion estekak konpontzea):
2. kasua. Ordezkapen partziala soltean
Kasu hau apur bat zailagoa da. Berriz ere bi mahai "adimentsu" ditugu:
Zuzendu beharreko helbide oker idatziak dituen lehenengo taula (deitu nuen Datuak2). Bigarren taula erreferentzia liburu bat da, eta horren arabera, helbidearen barruan azpikate baten ordezkapen partziala egin behar duzu (taula honi deitu diot Ordezkapenak2).
Oinarrizko aldea hemen jatorrizko datuen zati bat bakarrik ordeztu behar duzula da; adibidez, lehen helbideak okerra du “St. Petersburg” eskubideari buruz “St. Petersburg”, gainerako helbidea (posta kodea, kalea, etxea) bere horretan utziz.
Amaitutako formula honela izango da (hautematea errazteko, zenbat lerrotan banatu dut Alt+Sartu):
Hemen lan nagusia Excel testu funtzio estandarrak egiten du ORDUTEGIA (ORDENAK), hiru argumentu ditu:
- Iturburu-testua - Helbidea zutabeko lehen helbide okerra
- Zer bilatzen ari garen - hemen funtzioarekin trikimailua erabiltzen dugu VIEW (BILATU)aurreko modutik zutabetik balioa ateratzeko Aurkitu, helbide kurbatu batean zati gisa sartzen dena.
- Zer ordezkatu - modu berean zutabetik dagokion balio zuzena aurkituko dugu Ordezko.
Sartu formula hau honekin Ctrl+Shift+Sartu Hemen ere ez da beharrezkoa, nahiz eta, izatez, array-formula bat den.
Eta argi ikusten da (ikusi #N/A akatsak aurreko irudian) halako formula batek, dotoretasun guztiarekin, pare bat eragozpen dituela:
- Funtzio ORDEZKOAK maiuskulak eta minuskulak bereizten ditu, beraz, azkenaurreko lerroko "Spb" ez zen ordezko taulan aurkitu. Arazo hau konpontzeko, funtzioa erabil dezakezu ZAMENIT (ORDEZKATU), edo aldez aurretik bi mahaiak erregistro berera eraman.
- Testua hasieran zuzena bada edo bertan ez dago ordezkatzeko zatirik (azken lerroa), orduan gure formulak errore bat botatzen du. Momentu hori neutralizatu daiteke funtzioa erabiliz erroreak atzeman eta ordezkatuz ERROREA (ERROREA):
- Jatorrizko testuak badu direktorioko hainbat zati aldi berean, orduan gure formulak azkena bakarrik ordezkatzen du (8. lerroan, Ligovsky «Avenue« aldatu da "pr-t", Baina "S-Pb" on “St. Petersburg” jada ez, zeren “S-Pb” direktorioa altuagoa da). Arazo hau gure formula berriro exekutatuta konpon daiteke, baina dagoeneko zutabean zehar Fixed:
Ez da perfektua eta astuna leku batzuetan, baina eskuzko ordezkapen bera baino askoz hobea, ezta? 🙂
PS
Hurrengo artikuluan, makroak eta Power Query erabiliz ordezkapen ugari nola inplementatu ikusiko dugu.
- ORDEZKO funtzioak nola funtzionatzen duen testua ordezkatzeko
- Testu bat-etortze zehatzak bilatzea EXACT funtzioa erabiliz
- Maiuskulak eta minuskulak bereizten ditu bilaketa eta ordezkapena (maiuskulak eta minuskulak bereizten dira BILAKETA)