txheem
Daim ntawv qhia hnub tsim khoom, piv txwv li ib daim ntawv teev hnub, qhov twg tag nrho cov hnub ua haujlwm thiab hnub so raug cim raws li - qhov tsim nyog rau txhua tus neeg siv Microsoft Excel. Hauv kev xyaum, koj tsis tuaj yeem ua yam tsis muaj nws:
- hauv kev suav nyiaj (cov nyiaj hli, qhov ntev ntawm kev pabcuam, so haujlwm…)
- nyob rau hauv logistics - rau kev txiav txim kom raug ntawm lub sij hawm xa khoom, coj mus rau hauv tus account hnub so thiab hnub so (nco ntsoov cov classic "los tom qab hnub so?")
- hauv kev tswj xyuas qhov project - rau qhov kev kwv yees qhov tseeb ntawm cov ntsiab lus, suav nrog, dua, hnub ua haujlwm-tsis ua haujlwm
- txhua yam kev siv cov haujlwm zoo li UA HAUJ LWM (HNUB ua haujlwm) or PURE cov neeg ua haujlwm (NETWORKDAYS), vim lawv xav tau ib daim ntawv teev cov hnub so raws li kev sib cav
- thaum siv sij hawm txawj ntse ua haujlwm (xws li TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, thiab lwm yam) hauv Power Pivot thiab Power BI
- … etc. etc. – ntau yam piv txwv.
Nws yooj yim dua rau cov neeg ua haujlwm hauv tuam txhab ERP systems xws li 1C lossis SAP, vim tias daim ntawv qhia hnub tsim tau tsim rau hauv lawv. Tab sis dab tsi txog cov neeg siv Excel?
Koj muaj peev xwm, ntawm chav kawm, khaws xws li daim ntawv qhia hnub manually. Tab sis tom qab ntawd koj yuav tau hloov kho tsawg kawg ib xyoos ib zaug (lossis ntau zaus, xws li hauv "jolly" 2020), ua tib zoo nkag mus rau txhua hnub so, kev hloov pauv thiab hnub tsis ua haujlwm tau tsim los ntawm peb tsoomfwv. Thiab tom qab ntawd rov ua cov txheej txheem no txhua xyoo tom ntej. Ntxub.
Yuav ua li cas mus rau me ntsis vwm thiab ua "perpetual" Hoobkas daim ntawv qhia hnub hauv Excel? Ib qho uas hloov kho nws tus kheej, siv cov ntaub ntawv los ntawm Is Taws Nem thiab ib txwm tsim cov npe tshiab ntawm cov hnub tsis ua haujlwm rau kev siv tom ntej hauv kev suav? Kev ntxias?
Ua qhov no, qhov tseeb, tsis yog qhov nyuaj.
Cov ntaub ntawv qhov chaw
Cov lus nug tseem ceeb yog qhov twg kom tau txais cov ntaub ntawv? Hauv kev tshawb nrhiav qhov tsim nyog, kuv tau mus dhau ntau txoj kev xaiv:
- Cov thawj coj tau tshaj tawm hauv tsoomfwv lub vev xaib hauv PDF hom (ntawm no, ib qho ntawm lawv, piv txwv li) thiab ploj mus tam sim ntawd - cov ntaub ntawv tseem ceeb tsis tuaj yeem rub tawm ntawm lawv.
- Ib qho kev sim siab, thaum xub thawj siab ib muag, zoo li yog "Qhib Cov Ntaub Ntawv Portal ntawm Lub Koom Haum", qhov twg muaj cov ntaub ntawv sib raug zoo, tab sis, raws li kev soj ntsuam ze dua, txhua yam ua rau tu siab. Lub vev xaib tsis yooj yim heev rau kev xa mus rau hauv Excel, kev txhawb nqa kev tsis teb (kev cais tus kheej?), thiab cov ntaub ntawv nws tus kheej yog outdated nyob rau lub sijhawm ntev - daim ntawv qhia hnub tsim khoom rau 2020 tau hloov kho tshiab kawg rau lub Kaum Ib Hlis 2019 (kev txaj muag!) thiab Yog lawm, tsis muaj peb "coronavirus ' thiab ' pov npav' lis xaus ntawm 2020, piv txwv li.
Disillusioned nrog official qhov chaw, kuv pib khawb unofficial sawv daws yuav. Muaj ntau ntawm lawv hauv Is Taws Nem, tab sis feem ntau ntawm lawv, dua, tsis tsim nyog rau kev xa mus rau hauv Excel thiab muab ib daim ntawv qhia hnub ntau lawm hauv daim duab zoo nkauj. Tab sis nws tsis yog rau peb dai rau ntawm phab ntsa, txoj cai?
Thiab nyob rau hauv tus txheej txheem ntawm kev tshawb nrhiav, ib qho zoo kawg nkaus tau nrhiav pom - qhov chaw http://xmlcalendar.ru/
Yog tias tsis tsim nyog "frills", qhov chaw yooj yim, lub teeb thiab nrawm, ua kom pom tseeb rau ib txoj haujlwm - muab txhua tus tsim daim ntawv qhia hnub rau xyoo uas xav tau hauv hom XML. Zoo heev!
Yog tias, dheev, koj tsis paub, ces XML yog cov ntawv nyeem nrog cov ntsiab lus cim tshwj xeeb
Tsuas yog nyob rau hauv rooj plaub no, kuv tau hu rau cov kws sau ntawv ntawm lub xaib thiab lawv tau lees paub tias lub xaib muaj nyob rau 7 xyoo, cov ntaub ntawv ntawm nws tau hloov kho tas li (lawv txawm muaj ceg ntawm github rau qhov no) thiab lawv tsis mus kaw. Thiab kuv tsis mloog txhua qhov uas koj thiab kuv thauj cov ntaub ntawv los ntawm nws rau ib qho ntawm peb cov haujlwm thiab kev suav hauv Excel. Yog dawb. Zoo siab tias tseem muaj cov neeg zoo li no! hwm!
Nws tseem yuav tsum thauj cov ntaub ntawv no mus rau hauv Excel siv Power Query add-in (rau versions of Excel 2010-2013 nws tuaj yeem rub tawm dawb los ntawm Microsoft lub vev xaib, thiab hauv versions ntawm Excel 2016 thiab tshiab dua nws twb tau teeb tsa los ntawm lub neej ntawd. ).
Lub logic ntawm kev ua yuav yog raws li nram no:
- Peb thov kom rub tawm cov ntaub ntawv los ntawm lub xaib rau ib xyoos
- Tig peb qhov kev thov ua haujlwm
- Peb siv cov haujlwm no rau cov npe ntawm txhua lub xyoo muaj, pib txij xyoo 2013 mus txog rau xyoo tam sim no - thiab peb tau txais daim ntawv qhia hnub "perpetual" ntau lawm nrog kev hloov kho tsis siv neeg. Voila!
Kauj Ruam 1. Ntshuam daim ntawv qhia hnub rau ib xyoos
Ua ntej, thauj khoom daim ntawv qhia hnub rau ib xyoos, piv txwv li, rau 2020. Ua li no, hauv Excel, mus rau tab Cov ntaub ntawv (los yog Lub Zog Lus Nugyog tias koj ntsia nws raws li ib qho kev cais ntxiv) thiab xaiv Los ntawm Internet (Los ntawm Web). Nyob rau hauv lub qhov rais uas qhib, paste qhov txuas mus rau lub xyoo sib thooj, theej los ntawm qhov chaw:
Tom qab txhaj rau OK ib lub qhov rais saib ua ntej tshwm, uas koj yuav tsum tau nyem lub pob Hloov cov ntaub ntawv (Hloov cov ntaub ntawv) or Hloov cov ntaub ntawv (Kho kom raug cov ntaub ntawv) thiab peb yuav tau mus rau Power Query query editor qhov rai, qhov twg peb yuav txuas ntxiv ua haujlwm nrog cov ntaub ntawv:
Tam sim ntawd koj tuaj yeem nyab xeeb rho tawm hauv lub vaj huam sib luag txoj cai Thov Parameters (Ntawv qhia zaub mov) kauj ruam hloov hom (Hloov hom) Peb tsis xav tau nws.
Lub rooj nyob rau hauv kab hnub so muaj cov lis dej num thiab cov lus piav qhia ntawm cov hnub tsis ua hauj lwm - koj tuaj yeem pom nws cov ntsiab lus los ntawm "dhau los ntawm" nws ob zaug los ntawm nyem rau ntawm lo lus ntsuab rooj:
Txhawm rau rov qab mus, koj yuav tsum rho tawm hauv vaj huam sib luag txoj cai tag nrho cov kauj ruam uas tau tshwm sim rov qab rau Tau qhov twg los (Tau qhov twg los).
Lub rooj thib ob, uas tuaj yeem nkag mus tau zoo ib yam, muaj raws nraim qhov peb xav tau - cov hnub ntawm txhua hnub tsis ua haujlwm:
Nws tseem yuav ua cov phaj no, uas yog:
1. Lim tsuas yog hnub so (piv txwv li) los ntawm kab thib ob Attribute: t
2. Rho tawm tag nrho cov kab tshwj tsis yog thawj zaug - los ntawm txoj cai-nias ntawm lub npe ntawm thawj kab thiab xaiv cov lus txib Tshem tawm lwm kab (Tshem Lwm Kab):
3. Split thawj kab los ntawm qhov chaw sib cais rau lub hli thiab hnub nrog cov lus txib Split Kem – Los ntawm Delimiter tab transformation (Transform — Split kem — Los ntawm delimiter):
4. Thiab thaum kawg tsim ib kem suav nrog cov hnub qub. Txhawm rau ua qhov no, ntawm lub tab Ntxiv ib kab nyem rau ntawm lub pob Kev cai kem (Ntxiv Kem - Custom Column) thiab sau cov qauv hauv qab no hauv lub qhov rai uas tshwm:
=# hnub tim(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])
Ntawm no, tus neeg teb xov tooj # hnub muaj peb qhov kev sib cav: xyoo, hli, thiab hnub, feem. Tom qab nias rau OK peb tau txais cov kab uas yuav tsum tau muaj nrog cov hnub so ib txwm muaj, thiab tshem tawm cov kab seem ntxiv raws li hauv kauj ruam 2
Kauj Ruam 2. Tig qhov kev thov ua haujlwm
Peb txoj haujlwm tom ntej no yog hloov cov lus nug tsim rau xyoo 2020 rau hauv kev ua haujlwm thoob ntiaj teb rau txhua xyoo (tus lej xyoo yuav yog nws qhov kev sib cav). Txhawm rau ua qhov no, peb ua cov hauv qab no:
1. Expanding (yog tias tsis tau nthuav dav) lub vaj huam sib luag Cov lus nug (Cov lus nug) ntawm sab laug hauv Power Query qhov rai:
2. Tom qab hloov qhov kev thov mus rau ib qho kev ua haujlwm, lub peev xwm pom cov kauj ruam uas ua raws li qhov kev thov thiab yooj yim kho lawv, hmoov tsis, ploj. Yog li ntawd, nws ua rau kev txiav txim siab ua ib daim qauv ntawm peb qhov kev thov thiab frolic twb nrog nws, thiab tawm hauv qhov qub cia. Ua li no, right-click nyob rau hauv sab laug pane ntawm peb daim ntawv thov daim ntawv thov thiab xaiv Duplicate hais kom ua.
Right-clicking dua ntawm cov ntawv luam ntawm daim ntawv qhia hnub (2) yuav xaiv cov lus txib Rename (Rename) thiab sau lub npe tshiab - cia nws ua, piv txwv li, fx xyoo:
3. Peb qhib cov lus nug qhov chaws nyob rau hauv lub hwj chim Query lus (nws yog succinctly hu ua "M") siv cov lus txib Advanced Editor tab Review(Saib - Advanced Editor) thiab hloov pauv me me nyob rau ntawd los tig peb qhov kev thov mus rau hauv kev ua haujlwm rau txhua xyoo.
Nws yog:
Tom qab:
Yog tias koj txaus siab rau cov ntsiab lus, ces ntawm no:
- (xyoo raws li tus lej)=> - peb tshaj tawm tias peb txoj haujlwm yuav muaj ib qho kev sib cav ntawm tus lej - qhov sib txawv xyoo
- Pasting qhov sib txawv xyoo mus rau lub vev xaib txuas hauv kauj ruam Tau qhov twg los. Txij li thaum Lub Hwj Chim Query tsis tso cai rau koj los ua cov lej thiab cov ntawv nyeem, peb hloov xyoo tus lej rau cov ntawv ntawm yoov siv cov haujlwm Number.ToText
- Peb hloov lub xyoo hloov pauv rau 2020 hauv cov kauj ruam kawg #”Ntxiv cov khoom siv«, qhov twg peb tsim hnub los ntawm cov khoom tawg.
Tom qab txhaj rau Tag peb qhov kev thov dhau los ua haujlwm:
Kauj ruam 3. Ntshuam daim ntawv qhia hnub rau tag nrho cov xyoo
Qhov kawg ntawm sab laug yog ua cov lus nug tseem ceeb kawg, uas yuav xa cov ntaub ntawv rau txhua xyoo muaj thiab ntxiv tag nrho cov hnub so uas tau txais rau hauv ib lub rooj. Rau qhov no:
1. Peb nyem rau ntawm sab laug cov lus nug vaj huam sib luag nyob rau hauv ib qho chaw grey khoob nrog txoj cai nas khawm thiab xaiv ua ntu zus Kev thov tshiab – Lwm qhov chaw – thov tsis pub (Cov Lus Nug Tshiab - Los ntawm lwm qhov chaw - Cov lus nug tsis muaj):
2. Peb yuav tsum tsim ib daim ntawv teev npe ntawm txhua lub xyoo uas peb yuav thov daim ntawv qhia hnub, piv txwv li 2013, 2014 … 2020. Ua li no, hauv lub formula bar ntawm cov lus nug khoob uas tshwm sim, nkag mus rau cov lus txib:
Qauv:
={NumberA..NumberB}
… hauv Power Query tsim ib daim ntawv teev cov lej ntawm A txog B. Piv txwv li, cov lus qhia
={1..5}
… yuav tsim ib daim ntawv teev 1,2,3,4,5.
Zoo, txhawm rau kom tsis txhob raug khi nruj rau xyoo 2020, peb siv txoj haujlwm DateTime.LocalNow() - analogue ntawm Excel ua haujlwm HNUB NO (Hnub no) hauv Power Query - thiab rho tawm los ntawm nws, tig, xyoo tam sim no los ntawm kev ua haujlwm Hnub tim. Xyoo.
3. Qhov tshwm sim ntawm lub xyoo, txawm hais tias nws zoo nkaus li txaus, tsis yog lub rooj rau Lub Hwj Chim Query, tab sis yog ib qho khoom tshwj xeeb - daim ntawv teev (Npe). Tab sis hloov nws mus rau ib lub rooj tsis yog ib qho teeb meem: tsuas yog nyem lub pob Rau rooj (rau rooj) nyob rau hauv lub Upper sab laug ces kaum:
4. Ua tiav kab! Siv cov haujlwm peb tsim ua ntej fx xyoo mus rau qhov tshwm sim ntawm cov xyoo. Txhawm rau ua qhov no, ntawm lub tab Ntxiv ib kab nias lub pob Hu rau kev cai muaj nuj nqi (Ntxiv Kem - Invoke Custom Function) thiab teem nws qhov kev sib cav nkaus xwb - kab Column1 xyoo dhau los:
Tom qab txhaj rau OK peb txoj haujlwm fx xyoo kev ntshuam yuav ua hauj lwm nyob rau hauv lem rau txhua xyoo thiab peb yuav tau txais ib kem uas txhua lub xov tooj yuav muaj ib lub rooj nrog cov hnub uas tsis ua hauj lwm (cov ntsiab lus ntawm lub rooj yog pom meej yog tias koj nyem rau hauv lub keeb kwm yav dhau ntawm lub xov tooj ntawm tes. lus rooj):
Nws tseem yuav nthuav dav cov ntsiab lus ntawm cov rooj nested los ntawm txhaj rau ntawm lub cim nrog ob tug xub nyob rau hauv kem header Hnub (tick Siv thawj kab npe ua ntej nws tuaj yeem raug tshem tawm):
... thiab tom qab nias rau OK peb tau txais yam peb xav tau - ib daim ntawv teev tag nrho cov hnub so ntawm 2013 txog rau xyoo tam sim no:
Thawj, twb tsis tsim nyog kem, tuaj yeem raug tshem tawm, thiab rau qhov thib ob, teeb tsa hom ntaub ntawv hnub (Hnub) nyob rau hauv lub dropdown daim ntawv teev nyob rau hauv kem nqe lus:
Cov lus nug nws tus kheej tuaj yeem hloov npe qee yam muaj txiaj ntsig ntau dua Thov 1 thiab tom qab ntawd xa cov txiaj ntsig mus rau daim ntawv hauv daim ntawv dynamic "smart" rooj siv cov lus txib kaw thiab download tau tab Tsev (Lub Tsev - Kaw & Load):
Koj tuaj yeem hloov kho daim ntawv qhia hnub tsim yav tom ntej los ntawm txoj cai-nias ntawm lub rooj lossis cov lus nug hauv txoj cai pane los ntawm cov lus txib Hloov tshiab & Txuag. Los yog siv lub khawm Hloov tshiab txhua yam tab Cov ntaub ntawv (Hnub tim - Refresh All) los yog keyboard shortcut Ctrl+Alt+F5.
Hais tias yog tag nrho.
Tam sim no koj tsis tas yuav nkim sijhawm thiab xav txog roj nrhiav thiab hloov kho cov npe hnub so - tam sim no koj muaj daim ntawv qhia hnub "perpetual" ntau lawm. Txawm li cas los xij, tsuav yog cov kws sau ntawv ntawm qhov chaw http://xmlcalendar.ru/ txhawb nqa lawv cov xeeb ntxwv, uas, kuv vam tias, yuav yog lub sijhawm ntev heev (ua tsaug rau lawv dua!).
- Ntshuam bitcoin tus nqi kom ua tau zoo los ntawm is taws nem ntawm Power Query
- Nrhiav hnub ua haujlwm tom ntej uas siv WORKDAY muaj nuj nqi
- Nrhiav qhov kev sib tshuam ntawm hnub tim