Cia peb hais tias koj tab tom khiav ntau qhov haujlwm nrog cov peev txheej sib txawv thiab xav pom koj cov nqi rau txhua tus ntawm lawv. Ntawd yog, los ntawm daim ntawv qhia no:
.. tau tej yam zoo li no:
Hauv lwm lo lus, koj yuav tsum tau tshaj tawm cov peev nyiaj nyob rau hnub ntawm txhua qhov haujlwm thiab tau txais cov qauv yooj yim ntawm qhov project Gantt daim ntawv. Ua qhov no nrog koj txhais tes yog ntev thiab tho txawv, macros yog qhov nyuaj, tab sis Power Query rau Excel hauv qhov xwm txheej no qhia tau hais tias nws lub hwj chim hauv tag nrho nws lub yeeb koob.
Lub Zog Lus Nug yog ib qho add-on los ntawm Microsoft uas tuaj yeem xa cov ntaub ntawv mus rau hauv Excel los ntawm yuav luag txhua qhov chaw thiab tom qab ntawd hloov nws hauv ntau txoj kev sib txawv. Nyob rau hauv Excel 2016, qhov no add-in yog twb built-in los ntawm lub neej ntawd, thiab rau Excel 2010-2013 nws tuaj yeem rub tawm los ntawm Microsoft lub vev xaib thiab tom qab ntawd nruab rau hauv koj lub PC.
Ua ntej, cia peb tig peb lub rooj qub rau hauv lub rooj "ntse" los ntawm kev xaiv cov lus txib Format ua ib lub rooj tab Tsev (Lub Tsev — Format as Table) los yog nias lub keyboard shortcut Ctrl+T :
Tom qab ntawd mus rau lub tab Cov ntaub ntawv (yog tias koj muaj Excel 2016) lossis ntawm tab Lub Zog Lus Nug (yog tias koj muaj Excel 2010-2013 thiab koj tau teeb tsa Lub Hwj Chim Query raws li kev sib cais ntxiv) thiab nyem lub pob Los ntawm Table / Range. :
Peb lub rooj ntse yog loaded rau hauv Power Query query editor, qhov twg thawj kauj ruam yog teeb tsa tus lej qauv rau txhua kab uas siv cov dropdowns hauv lub rooj header:
Txhawm rau xam cov peev nyiaj hauv ib hnub, koj yuav tsum xam lub sijhawm ntawm txhua qhov haujlwm. Txhawm rau ua qhov no, xaiv (tuav tus yuam sij Ctrl) kem ua ntej Tag, thiab tom qab ntawd Start thiab xaiv ib pab neeg Ntxiv kab – Hnub – rho hnub (Ntxiv kab - Hnub - rho tawm hnub):
Cov lej tshwm sim yog 1 tsawg dua qhov tsim nyog, vim tias peb yuav tsum pib txhua qhov haujlwm thawj hnub thaum sawv ntxov thiab ua tiav hnub kawg thaum yav tsaus ntuj. Yog li ntawd, xaiv cov txiaj ntsig kab thiab ntxiv ib chav rau nws siv cov lus txib Hloov - Txheem - Ntxiv (Transform — Standard — Ntxiv):
Tam sim no cia peb ntxiv ib kab uas peb suav cov peev nyiaj hauv ib hnub. Txhawm rau ua qhov no, ntawm lub tab Ntxiv Kem kuv tsis ua si Kev cai kem (Custom Kem) thiab nyob rau hauv lub qhov rais uas tshwm sim, sau lub npe ntawm daim teb tshiab thiab cov qauv kev suav, siv cov npe ntawm cov kab los ntawm cov npe:
Tam sim no lub sijhawm hloov maj mam tshaj plaws - peb tsim lwm kem suav nrog cov npe ntawm cov hnub txij li pib mus rau qhov kawg nrog ib kauj ruam ntawm 1 hnub. Ua li no, rov nias lub pob Kev cai kem (Custom Kem) thiab siv lub built-in Power Query lus M, uas yog hu ua List. Hnub:
Txoj haujlwm no muaj peb qhov kev sib cav:
- pib hnub - nyob rau hauv peb cov ntaub ntawv, nws yog muab los ntawm kab Start
- tus naj npawb ntawm cov hnub tsim tawm - nyob rau hauv peb rooj plaub, qhov no yog cov hnub rau txhua qhov project, uas peb suav ua ntej hauv kab Sib rho
- lub sij hawm kauj ruam - teem los ntawm tsim #duration(1,0,0,0), lub ntsiab lus hauv hom lus M - ib hnub, xoom teev, xoom feeb, xoom vib nas this.
Tom qab txhaj rau OK peb tau txais ib daim ntawv teev npe (List) ntawm cov hnub, uas tuaj yeem nthuav dav rau hauv cov kab tshiab siv lub pob hauv lub rooj header:
... thiab peb tau txais:
Tam sim no txhua yam uas tseem tshuav yog kom tawg lub rooj, siv cov hnub tsim tawm raws li cov npe rau cov kab tshiab. Pab neeg no yog lub luag haujlwm rau qhov no. Kab lus nthuav dav (Pivot Kem) tab Hloov siab (Kev hloov pauv):
Tom qab txhaj rau OK peb tau txais txiaj ntsig ze rau qhov xav tau:
Null yog, nyob rau hauv cov ntaub ntawv no, ib tug analogue ntawm ib tug khoob ntawm tes nyob rau hauv Excel.
Nws tseem yuav tshem tawm cov kab uas tsis tsim nyog thiab tshem tawm cov lus tshwm sim ntawm ib sab ntawm cov ntaub ntawv qub nrog cov lus txib Kaw thiab thauj khoom - Kaw thiab thauj hauv… (Kaw & Load - Kaw & Load rau…) tab Tsev (Tsev):
Peb tau txais raws li qhov tshwm sim:
Rau kev zoo nkauj ntau dua, koj tuaj yeem hloov kho qhov pom ntawm cov rooj ntse ntse ntawm lub tab constructor (Tsim): teeb ib hom xim ib leeg, lov tes taw lim khawm, pab kom tag nrho, thiab lwm yam Tsev — Conditional Formatting — Xim Scales (Lub Tsev - Conditional Formatting - Xim Scales):
Thiab qhov zoo tshaj plaws yog tias yav tom ntej koj tuaj yeem hloov kho cov qub qub lossis ntxiv cov haujlwm tshiab rau lub rooj qub, thiab tom qab ntawd hloov kho lub rooj zoo nrog cov hnub nrog tus nas khawm sab xis - thiab Lub Hwj Chim Query yuav rov ua txhua yam peb tau ua tiav. .
Voilà!
- Gantt daim ntawv hauv Excel siv cov kev cai formatting
- Daim ntawv qhia hnub tseem ceeb ntawm qhov project
- Generating Duplicate Kab nrog Power Query