Nrhiav cov lus tseem ceeb hauv cov ntawv nyeem

Kev tshawb nrhiav cov ntsiab lus hauv cov ntawv nyeem yog ib qho ntawm cov haujlwm tshaj plaws thaum ua haujlwm nrog cov ntaub ntawv. Cia peb saib nws txoj kev daws teeb meem hauv ntau txoj hauv kev siv cov piv txwv hauv qab no:

Cia peb xav tias koj thiab kuv muaj cov npe ntawm cov ntsiab lus - cov npe ntawm lub tsheb hom - thiab lub rooj loj ntawm txhua yam khoom seem, qhov twg cov lus piav qhia qee zaum muaj ib lossis ob peb lub npe xws li ib zaug, yog tias cov khoom seem haum ntau dua ib qho. hom tsheb. Peb txoj haujlwm yog txhawm rau nrhiav thiab tso saib tag nrho cov lus tseem ceeb hauv cov hlwb nyob sib ze los ntawm kev muab cov cim cais (piv txwv li, comma).

Txoj Kev 1. Lus nug

Tau kawg, ua ntej peb tig peb cov ntxhuav rau hauv dynamic ("ntse") siv cov keyboard shortcut Ctrl+T los yog lus txib Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table), muab lawv lub npe (piv txwv Nyiaj Muas Nojи Spare qhov chaw) thiab thauj ib los ntawm ib qho rau hauv Power Query editor los ntawm kev xaiv ntawm lub tab Cov ntaub ntawv - Los ntawm Rooj / Ntau (Cov ntaub ntawv - Los ntawm Table / Range). Yog tias koj muaj cov laus versions ntawm Excel 2010-2013, qhov twg Lub Hwj Chim Query raug teeb tsa raws li kev sib cais ntxiv, ces khawm xav tau yuav nyob ntawm tab Lub Zog Lus Nug. Yog tias koj muaj lub cim tshiab ntawm Excel 365, ces khawm Los ntawm Table/Range npe tam sim no Nrog nplooj (Los ntawm Sheet).

Tom qab thauj khoom txhua lub rooj hauv Power Query, peb rov qab mus rau Excel nrog cov lus txib Tsev — Kaw thiab thauj khoom — Kaw thiab thauj mus rau… — Tsim kev sib txuas nkaus xwb (Lub Tsev - Kaw & Load - Kaw & Load rau… - Tsuas yog tsim kev sib txuas).

Tam sim no cia peb tsim ib qho kev thov dua Spare qhov chawlos ntawm txoj cai-nias ntawm nws thiab xaiv Duplicate thov (Cov lus nug dua), tom qab ntawd hloov npe qhov kev thov luam tawm mus rau Cov kev tshwm sim thiab peb yuav ua haujlwm nrog nws mus ntxiv.

Lub logic ntawm kev ua yog cov hauv qab no:

1. Hauv Advanced tab Ntxiv ib kab xaiv ib pab neeg Kev cai kem (Ntxiv kab - Custom column) thiab sau tus qauv = Brands. Tom qab nias rau OK peb yuav tau txais ib kab tshiab, qhov twg hauv txhua lub xov tooj yuav muaj ib lub rooj nested nrog cov npe ntawm peb cov lus tseem ceeb - automaker hom:

   

2. Siv lub pob nrog ob tug xub nyob rau hauv lub header ntawm kab ntxiv kom nthuav tag nrho cov rooj nested. Nyob rau tib lub sijhawm, cov kab nrog cov lus piav qhia ntawm cov khoom seem yuav sib npaug los ntawm ntau yam ntawm cov npe, thiab peb yuav tau txais txhua qhov ua tau ua khub-kev sib txuas ntawm "cov khoom seem-hom":

   

3. Hauv Advanced tab Ntxiv ib kab xaiv ib pab neeg xwm kab ke (Conditional kem) thiab teem caij rau kev tshuaj xyuas qhov tshwm sim ntawm lo lus tseem ceeb (hom) hauv cov ntawv nyeem (ib ntu lus piav qhia):

   

4. Txhawm rau ua kom cov ntaub ntawv tshawb fawb tsis txaus ntseeg, manually ntxiv qhov kev sib cav thib peb hauv cov qauv bar Sib piv.OrdinalIgnoreCase mus rau qhov tshwm sim check function Text. Muaj (yog tias tsis pom qhov formula bar, ces nws tuaj yeem qhib rau ntawm tab Review):

   

5. Peb lim cov ntsiab lus, tawm tsuas yog ib qho nyob rau hauv kab kawg, piv txwv li ntais ntawv thiab tshem tawm cov kab tsis tsim nyog. Tshwm sim.
6. Muab cov lus piav qhia zoo ib yam nrog cov lus txib Pawg los ntawm tab transformation (Transform — Group los ntawm). Raws li kev ua haujlwm sib sau ua ke, xaiv Txhua kab (Txhua kab). Ntawm cov zis, peb tau txais ib kem nrog cov ntxhuav, uas muaj tag nrho cov ntsiab lus rau txhua qhov khoom seem, suav nrog cov npe ntawm cov neeg tsim khoom peb xav tau:

   

7. Txhawm rau rho tawm cov qib rau txhua ntu, ntxiv lwm kem xam ntawm lub tab Ntxiv ib Kem – Custom Column (Ntxiv kab - Custom column) thiab siv cov mis uas muaj ib lub rooj (lawv nyob hauv peb kem Paub meej) thiab lub npe ntawm kab ntawv rho tawm:

   

8. Peb nyem rau ntawm lub pob nrog ob tug xub nyob rau hauv lub header ntawm lub resulting kem thiab xaiv cov lus txib Extract tus nqi (Extract values)kom tso tawm cov nyiaj muas noj nrog txhua tus cim delimiter koj xav tau:

   

9. Tshem tawm ib kab uas tsis tsim nyog Paub meej.
10. Txhawm rau ntxiv rau cov lus pom cov khoom uas ploj lawm los ntawm nws, qhov twg tsis muaj cov npe tau pom hauv cov lus piav qhia, peb ua cov txheej txheem rau kev sib txuas cov lus nug. tshwm sim nrog daim ntawv thov Spare qhov chaw khawm Ua ke tab Tsev (Lub Tsev - Cov lus nug sib koom ua ke). Kev sib txuas hom - Outer Join Right (Txoj cai sab nraud koom nrog):

    

11. Txhua yam uas tseem tshuav yog tshem tawm cov kab ntawv ntxiv thiab hloov npe-tsav cov uas tseem tshuav - thiab peb txoj haujlwm tau daws:

    
    

Txoj kev 2. Cov qauv

Yog hais tias koj muaj ib tug version ntawm Excel 2016 los yog tom qab ntawd, ces peb cov teeb meem yuav daws tau nyob rau hauv ib tug heev compact thiab elegant txoj kev siv lub tshiab muaj nuj nqi. Ua ke (TEXTJOIN):

Lub logic tom qab cov qauv no yog qhov yooj yim:

• muaj nuj nqi NRHIAV (NRAUG) tshawb nrhiav qhov tshwm sim ntawm txhua lub hom nyob rau hauv lem nyob rau hauv cov lus piav qhia tam sim no ntawm ib feem thiab rov qab yog tus naj npawb ntawm cov cim, pib los ntawm cov hom tau pom, los yog qhov yuam kev #VALUE! yog hais tias lub hom yog tsis nyob rau hauv cov lus piav qhia.
• Tom qab ntawd siv cov haujlwm IF (YOG) и EOSHIBKA (ISERROR) peb hloov qhov yuam kev nrog cov kab ntawv khoob "", thiab cov lej ntawm cov cim nrog cov npe ntawm lawv tus kheej.
• Cov txiaj ntsig ntawm cov hlwb khoob thiab pom cov npe tau sib sau ua ke rau hauv ib txoj hlua los ntawm ib qho kev muab cais ua cim siv lub luag haujlwm Ua ke (TEXTJOIN).

Kev sib piv kev ua tau zoo thiab lub zog Query Query Buffering rau Speedup

Rau kev ntsuas kev ua tau zoo, cia peb ua ib lub rooj ntawm 100 qhov khoom seem piav qhia raws li cov ntaub ntawv pib. Ntawm nws peb tau txais cov txiaj ntsig hauv qab no:

• Rov xam lub sij hawm los ntawm cov qauv (Muab 2) – 9 sec. thaum koj thawj zaug luam cov mis rau tag nrho kem thiab 2 sec. nyob rau hauv rov qab (buffering cuam tshuam, tej zaum).
  
• Lub sijhawm hloov tshiab ntawm Cov Lus Nug Txog Lub Hwj Chim (Txoj Kev 1) yog qhov phem dua - 110 vib nas this.
  

Tau kawg, ntau nyob ntawm kev kho vajtse ntawm ib lub PC tshwj xeeb thiab cov teeb tsa ntawm Chaw Ua Haujlwm thiab hloov tshiab, tab sis tag nrho cov duab, kuv xav tias, yog qhov tseeb.

Txhawm rau kom ceev cov lus nug Lub Hwj Chim, cia peb tsis nyob ntawm lub rooj saib Nyiaj Muas Noj, vim tias nws tsis hloov pauv hauv cov txheej txheem ntawm kev nug cov lus nug thiab nws tsis tas yuav rov xam nws tas li (raws li Power Query de facto ua). Rau qhov no peb siv lub luag haujlwm Rooj.Buffer los ntawm built-in Power Query lus M.

Txhawm rau ua qhov no, qhib cov lus nug Cov kev tshwm sim thiab ntawm lub tab Review nias lub pob Advanced Editor (Saib - Advanced Editor). Nyob rau hauv lub qhov rais uas qhib, ntxiv ib kab nrog ib tug tshiab variable Marky 2, uas yuav yog buffered version ntawm peb automaker directory, thiab siv qhov hloov tshiab no tom qab hauv cov lus nug nram qab no:

Tom qab zoo li no, qhov hloov tshiab ceev ntawm peb qhov kev thov nce yuav luag 7 zaug - txog li 15 vib nas this. Ib qho txawv heev 🙂

• Fuzzy text search hauv Power Query
• Hloov cov ntawv ntau nrog cov qauv
• Cov ntawv hloov pauv ntau hauv Power Query nrog List.Accumulate function