txheem
Formulation ntawm qhov teeb meem
Cia peb saib cov kev daws teeb meem zoo nkauj rau ib qho xwm txheej uas feem ntau cov neeg siv Excel ntsib sai dua lossis tom qab: koj yuav tsum tau ceev thiab khaws cov ntaub ntawv los ntawm ntau cov ntaub ntawv rau hauv ib lub rooj zaum kawg.
Piv txwv tias peb muaj cov ntaub ntawv hauv qab no, uas muaj ntau cov ntaub ntawv nrog cov ntaub ntawv los ntawm ceg nroog:
Tus naj npawb ntawm cov ntaub ntawv tsis muaj teeb meem thiab yuav hloov tau yav tom ntej. Txhua daim ntawv muaj ib daim ntawv npe muagqhov twg cov ntaub ntawv nyob rau hauv:
Tus naj npawb ntawm cov kab (kev txiav txim) hauv cov ntxhuav, ntawm chav kawm, txawv, tab sis cov kab ke yog tus qauv txhua qhov chaw.
Kev Ua Haujlwm: khaws cov ntaub ntawv los ntawm tag nrho cov ntaub ntawv rau hauv ib phau ntawv nrog kev hloov kho tsis siv neeg txuas ntxiv thaum ntxiv lossis tshem tawm cov ntaub ntawv hauv nroog lossis kab hauv cov ntxhuav. Raws li qhov kawg consolidated lub rooj, ces nws yuav ua tau los tsim ib qho kev qhia, pivot ntxhuav, lim-tsim cov ntaub ntawv, thiab lwm yam. Qhov tseem ceeb tshaj plaws yog yuav tsum tau sau.
Peb xaiv riam phom
Rau cov kev daws teeb meem, peb xav tau qhov tseeb version ntawm Excel 2016 (qhov kev ua haujlwm tsim nyog tau tsim rau hauv nws los ntawm lub neej ntawd) lossis yav dhau los versions ntawm Excel 2010-2013 nrog cov dawb add-in ntsia. Lub Zog Lus Nug los ntawm Microsoft (download nws ntawm no). Power Query yog qhov hloov pauv tau yooj yim thiab muaj zog heev rau kev thauj cov ntaub ntawv rau hauv Excel los ntawm lub ntiaj teb sab nraud, tom qab ntawd stripping thiab ua nws. Power Query txhawb yuav luag tag nrho cov ntaub ntawv uas twb muaj lawm - los ntawm cov ntawv nyeem rau SQL thiab txawm Facebook 🙂
Yog tias koj tsis muaj Excel 2013 lossis 2016, ces koj tsis tuaj yeem nyeem ntxiv (tsuas yog kidding). Nyob rau hauv cov laus versions ntawm Excel, xws li ib tug txoj hauj lwm tsuas yog yuav ua tau los ntawm programming macro nyob rau hauv Visual Basic (uas yog ib qho nyuaj heev rau beginners) los yog los ntawm monotonous phau ntawv luam (uas yuav siv sij hawm ntev thiab tsim kom raug).
Kauj ruam 1. Ntshuam ib cov ntaub ntawv raws li ib tug qauv
Ua ntej, cia peb ntshuam cov ntaub ntawv los ntawm ib phau ntawv ua haujlwm ua piv txwv, kom Excel "tso lub tswv yim". Txhawm rau ua qhov no, tsim ib phau ntawv ua haujlwm tshiab thiab…
- Yog tias koj muaj Excel 2016, ces qhib lub tab Cov ntaub ntawv thiab ces Tsim Cov Lus Nug - Los Ntawm Cov Ntaub Ntawv - Los Ntawm Phau Ntawv (Cov ntaub ntawv - Cov Lus Nug Tshiab - Los ntawm cov ntaub ntawv - Los ntawm Excel)
- Yog tias koj muaj Excel 2010-2013 nrog lub Power Query add-in ntsia, ces qhib lub tab Lub Zog Lus Nug thiab xaiv rau nws Los ntawm cov ntaub ntawv - Los ntawm cov ntaub ntawv (Los ntawm cov ntaub ntawv - Los ntawm Excel)
Tom qab ntawd, nyob rau hauv lub qhov rais uas qhib, mus rau peb lub nplaub tshev nrog cov ntawv ceeb toom thiab xaiv ib qho ntawm cov ntaub ntawv hauv nroog (nws tsis muaj teeb meem dab tsi, vim lawv txhua tus raug). Tom qab ob peb lub vib nas this, lub qhov rais Navigator yuav tsum tshwm sim, qhov twg koj yuav tsum xaiv daim ntawv peb xav tau (Kev Muag Khoom) ntawm sab laug, thiab nws cov ntsiab lus yuav tshwm rau sab xis:
Yog tias koj nyem rau ntawm lub pob nyob hauv qab sab xis ntawm lub qhov rais no download (Load), ces lub rooj yuav tsum tam sim ntawd tuaj rau daim ntawv nyob rau hauv nws thawj daim ntawv. Rau ib cov ntaub ntawv, qhov no yog qhov zoo, tab sis peb yuav tsum tau thauj ntau cov ntaub ntawv, yog li peb yuav mus txawv me ntsis thiab nyem lub pob kho (Kho kom raug). Tom qab ntawd, Power Query query editor yuav tsum tau muab tso rau hauv ib lub qhov rais cais nrog peb cov ntaub ntawv los ntawm phau ntawv:
Qhov no yog ib qho cuab yeej muaj zog heev uas tso cai rau koj "ua tiav" lub rooj rau qhov peb xav tau. Txawm hais tias cov lus piav qhia saum toj kawg nkaus ntawm tag nrho nws cov haujlwm yuav siv li ib puas nplooj ntawv, tab sis, yog tias luv luv, siv lub qhov rais no koj tuaj yeem:
- lim tawm cov ntaub ntawv tsis tsim nyog, cov kab khoob, cov kab uas tsis raug
- txheeb cov ntaub ntawv los ntawm ib lossis ntau kab
- tshem tawm ntawm kev rov ua dua
- faib cov ntawv nplaum los ntawm txhua kab (los ntawm tus lej, tus lej ntawm cov cim, thiab lwm yam)
- muab cov ntawv hauv kev txiav txim (tshem tawm qhov chaw ntxiv, rooj plaub raug, thiab lwm yam)
- Hloov cov ntaub ntawv hauv txhua txoj hauv kev (tig cov lej zoo li cov ntawv nyeem rau hauv cov lej ib txwm thiab rov ua dua)
- transpose (rotate) cov rooj thiab nthuav ob-dimensional cross-rooj rau hauv cov tiaj tus
- ntxiv cov kab ntxiv rau lub rooj thiab siv cov qauv thiab cov haujlwm hauv lawv siv M hom lus ua rau hauv Power Query.
- ...
Piv txwv li, cia peb ntxiv ib kab nrog cov ntawv sau npe ntawm lub hli rau peb lub rooj, yog li tom qab ntawd nws yuav yooj yim dua los tsim cov lus qhia pivot. Txhawm rau ua qhov no, right-click ntawm kab ntawv kab lus hnubthiab xaiv cov lus txib Duplicate kem (Dupplicate Kem), thiab ces right-click rau lub header ntawm duplicate kem uas tshwm thiab xaiv Commands Hloov - Lub Hlis - Lub Hlis Lub Npe:
Ib kab tshiab yuav tsum tau tsim nrog cov ntawv sau npe ntawm lub hli rau txhua kab. Los ntawm ob-nias ntawm kab ntawv kab lus, koj tuaj yeem hloov npe los ntawm Luam Hnub mus rau ib qho yooj yim dua Hlis, Xws li.
Yog hais tias nyob rau hauv ib co kab ntawv qhov kev pab cuam tsis zoo heev paub cov ntaub ntawv hom, ces koj yuav pab tau nws los ntawm txhaj rau lub hom icon nyob rau sab laug ntawm txhua kem:
Koj tuaj yeem tshem tawm cov kab uas tsis raug lossis cov kab khoob, nrog rau cov tswj tsis tsim nyog lossis cov neeg siv khoom, siv cov lim yooj yim:
Ntxiv mus, tag nrho cov kev hloov pauv tau raug kho nyob rau hauv txoj cai vaj huam sib luag, qhov twg lawv tuaj yeem raug dov rov qab (hla) lossis hloov lawv qhov tsis muaj (iav):
Lub teeb thiab elegant, tsis yog nws?
Kauj ruam 2. Cia peb hloov peb qhov kev thov mus rau hauv kev ua haujlwm
Txhawm rau rov qab rov qab tag nrho cov ntaub ntawv hloov pauv tau ua rau txhua phau ntawv tuaj txawv teb chaws, peb yuav tsum hloov peb qhov kev thov tsim rau hauv kev ua haujlwm, uas yuav raug siv, tom qab ntawd, rau tag nrho peb cov ntaub ntawv. Ua qhov no yog qhov yooj yim heev.
Hauv Query Editor, mus rau Saib tab thiab nyem lub pob Advanced Editor (Saib - Advanced Editor). Lub qhov rais yuav tsum qhib qhov twg tag nrho peb cov kev ua dhau los yuav raug sau rau hauv daim ntawv code hauv M lus. Thov nco ntsoov tias txoj hauv kev mus rau cov ntaub ntawv uas peb tuaj yeem ua piv txwv yog hardcoded hauv code:
Tam sim no cia peb ua ob peb qhov kev hloov kho:
Lawv lub ntsiab lus yog yooj yim: thawj kab (filepath)=> hloov peb cov txheej txheem rau hauv kev ua haujlwm nrog kev sib cav filepath, thiab hauv qab no peb hloov txoj kev ruaj khov rau tus nqi ntawm qhov hloov pauv no.
Tag nrho. Nyem rau Tag thiab yuav tsum pom qhov no:
Tsis txhob ntshai tias cov ntaub ntawv tau ploj mus - qhov tseeb, txhua yam yog OK, txhua yam yuav tsum zoo li qhov no 🙂 Peb tau ua tiav peb cov kev cai ua haujlwm, qhov twg tag nrho cov txheej txheem rau kev xa tawm thiab ua cov ntaub ntawv raug nco txog yam tsis tau khi rau ib qho ntaub ntawv tshwj xeeb. . Nws tseem yuav muab lub npe nkag siab dua (piv txwv li getData) nyob rau hauv lub vaj huam sib luag ntawm sab xis nyob rau hauv lub teb thawj lub npe thiab koj tuaj yeem sau tau Tsev - Kaw thiab rub tawm (Lub Tsev - Kaw thiab Load). Thov nco ntsoov tias txoj hauv kev rau cov ntaub ntawv uas peb tuaj yeem ua piv txwv yog hardcoded hauv cov lej. Koj yuav rov qab mus rau lub ntsiab Microsoft Excel qhov rais, tab sis lub vaj huam sib luag nrog tsim kev sib txuas rau peb txoj haujlwm yuav tsum tshwm sim ntawm sab xis:
Kauj ruam 3. Sau tag nrho cov ntaub ntawv
Txhua qhov nyuaj tshaj plaws yog tom qab, qhov qab ntxiag thiab qhov yooj yim tseem nyob. Mus rau lub tab Cov ntaub ntawv - Tsim cov lus nug - Los ntawm cov ntaub ntawv - Los ntawm Folder (Cov ntaub ntawv - Cov Lus Nug Tshiab - Los ntawm cov ntaub ntawv - Los ntawm nplaub tshev) lossis, yog tias koj muaj Excel 2010-2013, zoo ib yam li lub tab Lub Zog Lus Nug. Nyob rau hauv lub qhov rais uas tshwm sim, qhia meej lub nplaub tshev qhov twg tag nrho peb lub nroog cov ntaub ntawv nyob rau hauv thiab nyem OK. Cov kauj ruam tom ntej yuav tsum qhib lub qhov rais uas tag nrho cov ntaub ntawv Excel pom nyob rau hauv daim nplaub tshev no (thiab nws cov subfolders) thiab cov ntsiab lus rau txhua tus ntawm lawv yuav raug teev tseg:
Nyem hloov (Kho kom raug) thiab ib zaug ntxiv peb nkag mus rau hauv cov lus nug paub editor qhov rai.
Tam sim no peb yuav tsum ntxiv lwm kem rau peb lub rooj nrog peb tsim muaj nuj nqi, uas yuav "rub" cov ntaub ntawv los ntawm txhua cov ntaub ntawv. Txhawm rau ua qhov no, mus rau lub tab Ntxiv Kem – Custom Column (Ntxiv kab - Ntxiv Custom Column) thiab hauv qhov rai uas tshwm, nkag mus rau peb txoj haujlwm getData, qhia meej rau nws raws li kev sib cav tag nrho txoj hauv kev rau txhua cov ntaub ntawv:
Tom qab txhaj rau OK cov kab tsim yuav tsum muab ntxiv rau peb lub rooj ntawm sab xis.
Tam sim no cia peb tshem tawm tag nrho cov kab tsis tsim nyog (raws li hauv Excel, siv tus nas khawm sab xis - tshem tawm), tawm tsuas yog cov kab ntxiv thiab kab ke nrog cov ntaub ntawv npe, vim tias lub npe no (ntau dua, lub nroog) yuav pab tau kom muaj cov ntaub ntawv tag nrho rau txhua kab.
Thiab tam sim no lub "wow lub sijhawm" - nyem rau ntawm lub cim nrog nws tus kheej xub nyob rau sab xis sab xis ntawm kab ntxiv nrog peb txoj haujlwm:
… uncheck Siv thawj kab npe ua ntej (Siv thawj lub npe kab ntawv ua ntej)thiab nias OK. Thiab peb lub luag haujlwm yuav thauj khoom thiab ua cov ntaub ntawv los ntawm txhua cov ntaub ntawv, ua raws li cov txheej txheem sau tseg thiab sau txhua yam hauv ib lub rooj:
Rau kev zoo nkauj tiav, koj tuaj yeem tshem tawm cov .xlsx txuas ntxiv los ntawm thawj kab nrog cov npe cov ntaub ntawv - los ntawm kev hloov pauv nrog "tsis muaj dab tsi" (xws li txoj cai-nias ntawm kab ntawv header - Hloov chaw) thiab hloov lub npe kab ntawv no rau txoj kev xav. Thiab kuj kho cov ntaub ntawv hom ntawv hauv kab nrog hnub.
Txhua tus! Nyem rau Tsev - Kaw thiab Load (Lub Tsev - Kaw & Load). Tag nrho cov ntaub ntawv sau los ntawm cov lus nug rau txhua lub nroog yuav raug xa mus rau daim ntawv Excel tam sim no hauv "smart table" hom:
Qhov tsim kev sib txuas thiab peb lub rooj sib dhos ua haujlwm tsis tas yuav tsum tau txais kev cawmdim nyob rau hauv txhua txoj kev - lawv tau txais kev cawmdim ua ke nrog cov ntaub ntawv tam sim no raws li ib txwm muaj.
Nyob rau hauv lub neej yav tom ntej, nrog rau tej kev hloov nyob rau hauv lub nplaub tshev (ntxiv los yog tshem tawm cov nroog) los yog nyob rau hauv cov ntaub ntawv (hloov tus naj npawb ntawm kab), nws yuav txaus rau right-click ncaj qha rau ntawm lub rooj los yog ntawm cov lus nug nyob rau hauv txoj cai vaj huam sib luag thiab xaiv cov lus txib Hloov tshiab & Txuag (Refresh) - Lub zog Query yuav "tsim" tag nrho cov ntaub ntawv dua hauv ob peb feeb.
PS
Kev hloov kho. Tom qab lub Ib Hlis 2017 hloov tshiab, Power Query tau kawm yuav ua li cas sau Excel phau ntawv ua haujlwm los ntawm nws tus kheej, piv txwv li tsis tas yuav ua haujlwm cais ntxiv - nws tshwm sim tau. Yog li, cov kauj ruam thib ob los ntawm kab lus no tsis xav tau ntxiv lawm thiab tag nrho cov txheej txheem yuav pom tau yooj yim dua:
- xaiv Tsim Kev Thov - Los Ntawm Cov Ntaub Ntawv - Los ntawm Folder - Xaiv Folder - OK
- Tom qab cov npe ntawm cov ntaub ntawv tshwm, nias hloov
- Nyob rau hauv Query Editor qhov rais, nthuav lub Binary kem nrog ob xub xub thiab xaiv daim ntawv lub npe yuav tsum tau muab los ntawm txhua cov ntaub ntawv
Thiab yog tag nrho! Nkauj!
- Hloov kho ntawm crosstab rau hauv ib lub tiaj tus haum rau lub tsev pivot rooj
- Tsim ib daim ntawv ua npuas animated hauv Power View
- Macro los sau cov ntawv los ntawm cov ntaub ntawv Excel sib txawv rau hauv ib qho