Edukiak
Iturburu-testuan gako-hitzak bilatzea datuekin lan egiten duzunean ohikoenetako bat da. Ikus dezagun bere soluzioa hainbat modutan honako adibide hau erabiliz:
Demagun zuk eta biok gako-hitz-zerrenda bat dugula –auto-marken izenak– eta ordezko piezen taula handi bat, non deskribapenek batzuetan horrelako marka bat edo batzuk izan ditzaketela aldi berean, ordezko pieza bat baino gehiago egokitzen bada. auto marka. Gure zeregina ondoko gelaxketan hautemandako gako-hitz guztiak aurkitzea eta bistaratzea da, bereizle-karaktere jakin baten bidez (adibidez, koma).
1. metodoa. Power Query
Jakina, lehenik eta behin gure taulak dinamiko ("smart") bihurtzen ditugu teklatuko lasterbide bat erabiliz Ctrl+T edo aginduak Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu), eman izenak (adibidez Stampsи Ordezko piezak) eta kargatu banan-banan Power Query editorean fitxan hautatuta Datuak - Taula/Barrutitik (Datuak — Taula/Barrutitik). Excel 2010-2013 bertsio zaharragoak badituzu, non Power Query gehigarri bereizi gisa instalatuta dagoen, orduan nahi duzun botoia fitxan egongo da Power kontsulta. Excel 365 bertsio berri bat baduzu, orduan botoia Taula/Barrutitik hara deitua orain Hostoekin (Fitxatik).
Power Query-n taula bakoitza kargatu ondoren, Excel-era itzuliko gara komandoarekin Hasiera — Itxi eta kargatu — Itxi eta kargatu… — Sortu konexioa soilik (Etxea — Itxi eta kargatu — Itxi eta kargatu hona... — Sortu konexioa soilik).
Orain sor dezagun eskaera bikoiztua Ordezko piezakgainean eskuineko botoiarekin klik eginez eta hautatuz Eskaera bikoiztua (Kontsulta bikoiztua), eta, ondoren, izena aldatu ondoriozko kopia-eskaerari Emaitzak eta berarekin lanean jarraituko dugu.
Ekintzen logika hau da:
- Aurreratua fitxan Zutabe bat gehitzea talde bat aukeratu Zutabe pertsonalizatua (Gehitu zutabea — Zutabe pertsonalizatua) eta sartu formula = Markak. Sakatu ondoren OK zutabe berri bat jasoko dugu, non gelaxka bakoitzean habiaratuta dagoen taula bat egongo den gure gako-hitzen zerrendarekin - autogileen markak:
- Erabili gehitutako zutabearen goiburuko gezi bikoitzak dituen botoia habiatutako taula guztiak zabaltzeko. Aldi berean, ordezko piezen deskribapenak dituzten lerroak marka kopuruaren multiplo batez biderkatuko dira, eta "ordezko pieza-marka"-ren bikote-konbinazio posible guztiak lortuko ditugu:
- Aurreratua fitxan Zutabe bat gehitzea talde bat aukeratu Baldintzapeko zutabea (Baldintzazko zutabea) eta ezarri baldintza bat iturburuko testuan gako-hitz bat (marka) agertzea egiaztatzeko (zatiaren deskribapena):
- Bilaketa maiuskulak eta minuskulak bereizteko, gehitu eskuz hirugarren argumentua formula-barran Konparatu.OrdinalIgnoreCase agerraldiak egiaztatzeko funtziora Testua.Duen (Formula barra ikusgai ez badago, fitxan gaitu daiteke Review):
- Sortutako taula iragazten dugu, azken zutabean bakarrak utziz, hau da, bat-etortzeak eta beharrezkoa ez den zutabea kendu agerraldi.
- Deskribapen berdinak komandoarekin taldekatzea Talde arabera fitxa Eraldaketa (Eraldaketa — Taldekatu). Agregazio eragiketa gisa, aukeratu Lerro guztiak (Errenkada guztiak). Irteeran, taulekin zutabe bat jasoko dugu, ordezko pieza bakoitzaren xehetasun guztiak biltzen dituena, behar ditugun autogileen markak barne:
- Zati bakoitzeko kalifikazioak ateratzeko, gehitu kalkulatutako beste zutabe bat fitxan Zutabe bat gehitzea - Zutabe pertsonalizatua (Gehitu zutabea — Zutabe pertsonalizatua) eta erabili taula batez osatutako formula (gure zutabean daude Xehetasunak) eta ateratako zutabearen izena:
- Sortutako zutabearen goiburuko gezi bikoitzak dituen botoian klik egiten dugu eta komandoa hautatzen dugu Atera balioak (Atera balioak)nahi duzun edozein karaktere mugatzaile duten zigiluak ateratzeko:
- Behar ez den zutabe bat kentzen Xehetasunak.
- Emaitzen taulari handik desagertu ziren zatiak gehitzeko, non deskribapenetan markarik aurkitu ez zen, kontsulta konbinatzeko prozedura egiten dugu. Emaitza jatorrizko eskaerarekin Ordezko piezak botoia Konbinatu fitxa Hasiera (Hasiera — Bateratu kontsultak). Konexio mota - Kanpoko batu eskuinera (eskuineko kanpoko juntadura):
- Geratzen dena da zutabe gehigarriak kentzea eta gainerakoak izena aldatzea eta mugitzea, eta gure zeregina konpondu da:
2. metodoa. Formulak
Excel 2016 bertsioa edo berriagoa baduzu, gure arazoa modu oso trinko eta dotore batean konpondu daiteke funtzio berria erabiliz. UZTARTZEKO (TESTU BATU):
Formula honen atzean dagoen logika sinplea da:
- Funtzio BILAKETA (AURKITU) marka bakoitzaren agerraldia bilatzen du txandaka piezaren uneko deskribapenean eta ikurraren serie-zenbakia itzultzen du, zeinetik hasita marka aurkitu den, edo #BALIOA errorea! marka deskribapenean ez badago.
- Ondoren, funtzioa erabiliz IF (BADA) и EOSHIBKA (ISERROREA) akatsak testu-kate huts batekin ordezkatzen ditugu, eta karaktereen zenbaki ordinalak marka-izenekin.
- Ondorioz, gelaxka hutsen eta aurkitutako marken sorta kate bakar batean muntatzen da bereizle-karaktere jakin baten bidez funtzioa erabiliz UZTARTZEKO (TESTU BATU).
Errendimenduaren alderaketa eta Power Query Query Buffering-a bizkortzeko
Errendimendu probak egiteko, har dezagun 100 ordezko piezen deskribapen taula bat hasierako datu gisa. Horren gainean emaitza hauek lortuko ditugu:
- Formulen bidez birkalkulatzeko denbora (2. metodoa) – 9 seg. formula zutabe osora kopiatzen duzunean eta 2 seg. errepikatzen denean (buffering-afektuak, ziurrenik).
- Power Query kontsultaren eguneratze-denbora (1. metodoa) askoz okerragoa da - 110 segundo.
Jakina, asko PC jakin baten hardwarearen eta Office-ren instalatutako bertsioaren eta eguneraketen araberakoa da, baina irudi orokorra, nire ustez, argia da.
Power Query kontsulta bat bizkortzeko, gorde dezagun bilaketa-taula Stamps, ez baita aldatzen kontsultaren exekuzio prozesuan eta ez baita beharrezkoa etengabe birkalkulatzea (Power Query-k de facto egiten duen bezala). Horretarako funtzioa erabiltzen dugu Taula.Buffer Power Query hizkuntza integratua M.
Horretarako, ireki kontsulta bat Emaitzak eta fitxan Review sakatu botoia Editore aurreratua (Ikusi — Editore aurreratua). Irekitzen den leihoan, gehitu lerro bat aldagai berri batekin Marki 2, gure autogileen direktorioaren buffer-eko bertsioa izango dena, eta erabili aldagai berri hau geroago kontsulta komando honetan:
Hobekuntza horren ondoren, gure eskaeraren eguneratze-abiadura ia 7 aldiz handitzen da - 15 segundora arte. Gauza guztiz ezberdina 🙂
- Testu bilaketa lausoa Power Query-n
- Testu masiboen ordezkapena formulekin
- Testuaren ordezkapena Power Query-n List.Acumulate funtzioarekin