Fittex għal kliem ewlieni fit-test

It-tiftix għal kliem ewlieni fit-test sors huwa wieħed mill-aktar kompiti komuni meta taħdem mad-dejta. Ejja nħarsu lejn is-soluzzjoni tagħha b'diversi modi billi tuża l-eżempju li ġej:

Ejja ngħidu li jien u int għandna lista ta’ kliem prinċipali – l-ismijiet ta’ ditti ta’ karozzi – u tabella kbira ta’ kull tip ta’ spare parts, fejn id-deskrizzjonijiet kultant jista’ jkun fihom marka waħda jew aktar f’daqqa, jekk l-ispare part taqbel għal aktar minn waħda. marka tal-karozza. Il-kompitu tagħna huwa li nsibu u nuru l-kliem kjavi kollha misjuba fiċ-ċelloli ġirien permezz ta 'karattru separatur partikolari (per eżempju, virgola).

Metodu 1. Mistoqsija dwar l-Enerġija

Naturalment, l-ewwel inbiddlu t-tabelli tagħna f'dinamiċi ("intelliġenti") billi tuża shortcut tat-tastiera Ctrl+T jew jikkmanda Home – Format bħala tabella (Id-dar — Format bħala Tabella), agħtihom ismijiet (per eżempju Bololи Spare parts) u tagħbija waħda waħda fl-editur tal-Power Query billi tagħżel fuq it-tab Dejta – Minn Tabella/Firxa (Data — Minn Tabella/Medda). Jekk għandek verżjonijiet eqdem ta' Excel 2010-2013, fejn Power Query huwa installat bħala add-in separat, allura l-buttuna mixtieqa tkun fuq it-tab Mistoqsija dwar l-Enerġija. Jekk għandek verżjoni ġdida fjamanta ta 'Excel 365, allura l-buttuna Minn Tabella/Firxa imsejħa hemm issa Bil-weraq (Mill Folja).

Wara li tgħabbi kull tabella f'Power Query, nirritornaw lura lejn Excel bil-kmand Home — Agħlaq u tagħbija — Agħlaq u tagħbija għal... — Oħloq konnessjoni biss (Dar — Agħlaq u Tagħbija — Agħlaq u Tagħbija għal... — Oħloq biss konnessjoni).

Issa ejja noħolqu talba duplikata Spare partsbilli tikklikkja fuqha bil-lemin u tagħżel Talba duplikata (Mistoqsija duplikata), imbagħad semmi mill-ġdid it-talba tal-kopja li tirriżulta għal Ir-riżultati u se nkomplu naħdmu miegħu.

Il-loġika tal-azzjonijiet hija din li ġejja:

1. Fuq it-tab Avvanzat Żieda ta 'kolonna agħżel tim Kolonna tad-dwana (Żid kolonna — Kolonna personalizzata) u daħħal il-formula = Marki. Wara li tikklikkja fuq OK se jkollna kolonna ġdida, fejn f'kull ċellula se jkun hemm tabella nested b'lista tal-kliem kjavi tagħna - marki tal-produtturi tal-karozzi:

   

2. Uża l-buttuna bi vleġeġ doppji fl-intestatura tal-kolonna miżjuda biex tespandi t-tabelli kollha nested. Fl-istess ħin, il-linji b'deskrizzjonijiet ta 'spare parts se jimmultiplikaw b'multiplu tan-numru ta' marki, u se nġibu l-pari-kombinazzjonijiet kollha possibbli ta '"spare part-marka":

   

3. Fuq it-tab Avvanzat Żieda ta 'kolonna agħżel tim Kolonna kundizzjonali (kolonna kundizzjonali) u waqqaf kundizzjoni biex tiċċekkja l-okkorrenza ta’ keyword (marka) fit-test sors (deskrizzjoni tal-parti):

   

4. Biex tagħmel il-każ tat-tfittxija mhux sensittiv, żid manwalment it-tielet argument fil-bar tal-formula Qabbel.OrdinalIgnoreCase għall-funzjoni tal-kontroll tal-okkorrenza Test.Fih (jekk il-bar tal-formula ma tkunx viżibbli, allura tista 'tiġi attivata fuq it-tab reviżjoni):

   

5. Aħna niffiltraw it-tabella li tirriżulta, u nħallu dawk biss fl-aħħar kolonna, jiġifieri logħbiet u neħħi l-kolonna bla bżonn Ġrajjiet.
6. Raggruppament ta 'deskrizzjonijiet identiċi mal-kmand Grupp minn tab Trasformazzjoni (Trasforma — iggruppa minn). Bħala operazzjoni ta 'aggregazzjoni, agħżel Il-linji kollha (Ir-ringieli kollha). Fl-output, irridu kolonna bit-tabelli, li fiha d-dettalji kollha għal kull spare part, inklużi l-marki tal-produtturi tal-karozzi li għandna bżonn:

   

7. Biex tiġbed il-gradi għal kull parti, żid kolonna kkalkulata oħra fuq it-tab Żieda ta 'Kolonna - Kolonna Custom (Żid kolonna — Kolonna personalizzata) u uża formula li tikkonsisti minn tabella (huma jinsabu fil-kolonna tagħna dettalji) u l-isem tal-kolonna estratta:

   

8. Aħna nikklikkjaw fuq il-buttuna bi vleġeġ doppji fl-intestatura tal-kolonna li tirriżulta u agħżel il-kmand Estratt valuri (Valuri estratti)biex toħroġ timbri bi kwalunkwe karattru delimitatur li trid:

   

9. Tneħħi kolonna bla bżonn dettalji.
10. Biex iżżid mat-tabella li tirriżulta l-partijiet li sparixxew minnha, fejn ma nstabet l-ebda marka fid-deskrizzjonijiet, aħna nwettqu l-proċedura biex tgħaqqad il-mistoqsija Riżultat b'talba oriġinali Spare parts buttuna Għaqqad tab home (Id-dar — Amalgamazzjoni mistoqsijiet). Tip ta' konnessjoni - Outer Join Dritt (Joqgħa fuq barra tal-lemin):

    

11. Li jibqa 'huwa li tneħħi l-kolonni żejda u ċċaqlaq mill-ġdid l-isem ta' dawk li fadal - u l-kompitu tagħna jiġi solvut:

    
    

Metodu 2. Formuli

Jekk għandek verżjoni ta 'Excel 2016 jew aktar tard, allura l-problema tagħna tista' tissolva b'mod kompatt u eleganti ħafna bl-użu tal-funzjoni l-ġdida KOMBINA (JUNTING TEXT):

Il-loġika wara din il-formula hija sempliċi:

• funzjoni FITTEX (SIB) tfittex l-okkorrenza ta 'kull marka mbagħad fid-deskrizzjoni attwali tal-parti u jirritorna jew in-numru tas-serje tas-simbolu, li jibda minn fejn instabet il-marka, jew l-iżball #VALUR! jekk il-marka ma tkunx fid-deskrizzjoni.
• Imbagħad tuża l-funzjoni IF (JEKK) и EOSHIBKA (ISERROR) nissostitwixxu l-iżbalji b'sekwenza ta 'test vojta "", u n-numri ordinali tal-karattri bl-ismijiet tad-ditta nfushom.
• Il-firxa li tirriżulta ta' ċelloli vojta u marki misjuba hija mmuntata f'sekwenza waħda permezz ta' karattru separatur partikolari bl-użu tal-funzjoni KOMBINA (JUNTING TEXT).

Tqabbil tal-Prestazzjoni u Buffering ta' Mistoqsija ta' Mistoqsija dwar l-Enerġija għal Speedup

Għall-ittestjar tal-prestazzjoni, ejja nieħdu tabella ta '100 deskrizzjoni ta' spare parts bħala data inizjali. Fuqha nġibu r-riżultati li ġejjin:

• Ħin ta' kalkolu mill-ġdid bil-formuli (Metodu 2) – 9 sek. meta tikkopja l-ewwel il-formula għall-kolonna kollha u 2 sek. fuq ripetuti (il-buffering jaffettwa, probabbilment).
  
• Il-ħin tal-aġġornament tal-mistoqsija dwar il-Power Query (Metodu 1) huwa ħafna agħar - 110 sekonda.
  

Naturalment, ħafna jiddependi fuq il-ħardwer ta 'PC partikolari u l-verżjoni installata ta' Office u aġġornamenti, iżda l-istampa ġenerali, naħseb, hija ċara.

Biex tħaffef mistoqsija dwar Power Query, ejja nibferixxu t-tabella ta' tfittxija Bolol, minħabba li ma tinbidilx fil-proċess tal-eżekuzzjoni tal-mistoqsija u mhux meħtieġ li tiġi kkalkulata mill-ġdid kontinwament (kif tagħmel Power Query de facto). Għal dan nużaw il-funzjoni Tabella.Buffer mil-lingwa Power Query integrata M.

Biex tagħmel dan, iftaħ mistoqsija Ir-riżultati u fuq it-tab reviżjoni agħfas il-buttuna Editur Avvanzat (Ara — Editur Avvanzat). Fit-tieqa li tiftaħ, żid linja b'varjabbli ġdid Marki 2, li se tkun verżjoni buffered tad-direttorju tagħna tal-produtturi tal-karozzi, u uża din il-varjabbli l-ġdida aktar tard fil-kmand tal-mistoqsija li ġej:

Wara dan ir-raffinament, il-veloċità tal-aġġornament tat-talba tagħna tiżdied bi kważi 7 darbiet - sa 15-il sekonda. Ħaġa pjuttost differenti 🙂

• Tiftix tat-test fuzzy fil-Power Query
• Sostituzzjoni ta' test bl-ingrossa b'formuli
• Sostituzzjoni tat-test bl-ingrossa f'Power Query b'funzjoni List.Accumulate