Ikhalenda lasefekthri ku-Excel

Ikhalenda lokukhiqiza, okungukuthi uhlu lwezinsuku, lapho zonke izinsuku zokusebenza ezisemthethweni namaholide zimakwa ngokufanele - into edingekayo ngokuphelele kunoma yimuphi umsebenzisi we-Microsoft Excel. Ngokwenza, awukwazi ukwenza ngaphandle kwayo:

• ekubalweni kwezimali (iholo, ubude besevisi, amaholide ...)
• kwezokuthutha - ukuze kunqunywe kahle izikhathi zokulethwa kwezidingo, kucatshangelwa izimpelasonto namaholide (khumbula okwakudala kokuthi "uzobuya ngemva kwamaholide?")
  
• ekuphathweni kwephrojekthi - ngokulinganisa okulungile kwemigomo, kucatshangelwa, futhi, izinsuku zokusebenza okungezona ukusebenza
• noma yikuphi ukusetshenziswa kwemisebenzi efana USUKU LOMSEBENZI (USUKU LOMSEBENZI) or ABASEBENZI ABAHLANZEKILE (NETWORKDAYS), ngoba zidinga uhlu lwamaholide njengengxabano
• lapho usebenzisa imisebenzi ye-Time Intelligence (njenge-TOTALYTD, i-TOTALMTD, i-SAMEPERIODLASTYEAR, njll.) ku-Power Pivot kanye ne-Power BI
  
• … njll. njll. – izibonelo eziningi.
  

Kulula kulabo abasebenza ezinhlelweni ze-ERP zezinkampani ezifana ne-1C noma i-SAP, njengoba ikhalenda yokukhiqiza yakhelwe kubo. Kodwa kuthiwani ngabasebenzisi be-Excel?

Yebo, ungagcina ikhalenda elinjalo ngesandla. Kodwa-ke kuzodingeka ukuthi uyibuyekeze okungenani kanye ngonyaka (noma kaningi, njengaku-“jolly” 2020), ungena ngokucophelela kuzo zonke izimpelasonto, ukudluliselwa kanye nezinsuku ezingezona zokusebenza eziqanjwe uhulumeni wethu. Bese uphinda le nqubo njalo ngonyaka ozayo. Isithukuthezi.

Kuthiwani ngokuhlanya kancane bese wenza ikhalenda yefekthri “engunaphakade” ku-Excel? Lowo ozibuyekezayo, othatha idatha ku-inthanethi futhi uhlale ekhiqiza uhlu lwakamuva lwezinsuku ezingezona ezokusebenza ukuze zisetshenziswe ngokulandelayo kunoma yiziphi izibalo? Uyalinga?

Ukwenza lokhu, empeleni, akunzima neze.

Umthombo wedatha

Umbuzo omkhulu uthi ungayithola kuphi idatha? Ekufuneni umthombo ofanelekayo, ngidlule ezintweni ezimbalwa:

• Imithetho yokuqala ishicilelwa kuwebhusayithi kahulumeni ngefomethi ye-PDF (lapha, eyodwa yazo, isibonelo) futhi zinyamalale ngokushesha - ulwazi oluwusizo ngeke lukhishwe kuzo.
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

Ngidunyazwe yimithombo esemthethweni, ngaqala ukumba leyo engekho emthethweni. Ziningi zazo ku-inthanethi, kodwa eziningi zazo, futhi, azifaneleki neze ukungenisa ku-Excel futhi zinikeze ikhalenda lokukhiqiza ngendlela yezithombe ezinhle. Kodwa akusikhona okwethu ukuthi sikulengise odongeni, akunjalo?

Futhi ngesikhathi sokusesha, kwatholakala into emangalisayo ngephutha – isiza http://xmlcalendar.ru/

Ngaphandle kokuthi "ama-frills" angadingeki, isayithi elilula, elilula futhi elisheshayo, elicijelwe umsebenzi owodwa - ukunikeza wonke umuntu ikhalenda lokukhiqiza lonyaka oyifunayo ngefomethi ye-XML. Kuhle kakhulu!

Uma, kungazelelwe, ungazi, kusho ukuthi i-XML iyifomethi yombhalo enokuqukethwe okumakwe ngokuthi okukhethekile . Ilula, ilula futhi ifundeka izinhlelo eziningi zesimanje, kufaka phakathi i-Excel.

Uma kwenzeka, ngithinte ababhali besayithi futhi baqinisekisa ukuthi isayithi selikhona iminyaka engu-7, idatha ekuso ivuselelwa njalo (baze babe negatsha ku-github yalokhu) futhi ngeke bayivale. Futhi anginankinga nakancane ukuthi mina nawe silayisha idatha kuyo yanoma imaphi amaphrojekthi ethu nokubala ku-Excel. Kumahhala. Kuyajabulisa ukwazi ukuthi basekhona abantu abanje! Hlonipha!

Kusele ukulayisha le datha ku-Excel kusetshenziswa isengezo Sombuzo Wamandla (ngezinguqulo ze-Excel 2010-2013 ingadawunilodeka mahhala kuwebhusayithi ye-Microsoft, futhi ezinguqulweni ze-Excel 2016 nezintsha isivele yakhelwe ngaphakathi ngokuzenzakalelayo. ).

I-logic yezenzo izoba kanje:

1. Senza isicelo sokulanda idatha kusayithi yanoma yimuphi unyaka owodwa
2. Ukuguqula isicelo sethu sibe umsebenzi
3. Sisebenzisa lo msebenzi ohlwini lwayo yonke iminyaka etholakalayo, kusukela ngo-2013 kuze kufike onyakeni wamanje - futhi sithola ikhalenda lokukhiqiza "elingapheli" elinokubuyekeza okuzenzakalelayo. Voila!
   

Isinyathelo 1. Ngenisa ikhalenda unyaka owodwa

Okokuqala, layisha ikhalenda lokukhiqiza lanoma yimuphi unyaka owodwa, ngokwesibonelo, ngo-2020. Ukuze wenze lokhu, ku-Excel, iya kuthebhu Idatha (noma Umbuzo Wamandlauma uyifakile njengesengezo esihlukile) bese ukhetha Kusuka ku-inthanethi (Kusuka kuwebhu). Ewindini elivulayo, namathisela isixhumanisi sonyaka ohambisanayo, okopishwe kusayithi:

Ngemuva kokuchofoza OK iwindi lokubuka kuqala liyavela, lapho udinga ukuchofoza khona inkinobho Guqula Idatha (Guqula idatha) or Ukuze ushintshe idatha (Hlela idatha) futhi sizofika kuwindi lomhleli wombuzo wamandla, lapho sizoqhubeka nokusebenza ngedatha:

Ngokushesha ungakwazi ukususa ngokuphephile kuphaneli elungile Cela Amapharamitha (Izilungiselelo zombuzo) isinyathelo uhlobo olushintshiwe (Uhlobo Olushintshiwe) Asimdingi.

Ithebula elikukholomu yamaholide liqukethe amakhodi nezincazelo zezinsuku okungezona ezokusebenza - ungabona elikuqukethe "ngokuwela" kabili ngokuchofoza igama eliluhlaza. Ithebula:

Ukuze ubuyele emuva, kuzomele ususe kuphaneli engakwesokudla zonke izinyathelo ezivele emuva Umthombo (Umthombo).

Ithebula lesibili, elingafinyelelwa ngendlela efanayo, liqukethe kanye esikudingayo - izinsuku zazo zonke izinsuku ezingezona ezokusebenza:

Kusele ukucubungula leli cwecwe, okungukuthi:

1. Hlunga kuphela amadethi amaholide (okungukuthi lawo) ngekholomu yesibili Imfanelo:t

2. Susa wonke amakholomu ngaphandle kweyokuqala - ngokuchofoza kwesokudla esihlokweni sekholomu yokuqala bese ukhetha umyalo Susa amanye amakholomu (Susa Amanye Amakholomu):

3. Hlukanisa ikholomu yokuqala ngechashazi ngokwehlukana inyanga nosuku ngomyalo Hlukanisa Ikholomu – Nge-Delimiter ithebhu Uguquko (Guqula — Hlukanisa ikholomu — Nge-delimiter):

4. Futhi ekugcineni udale ikholomu ebaliwe enezinsuku ezivamile. Ukuze wenze lokhu, kuthebhu Yengeza ikholomu chofoza inkinobho Ikholomu yangokwezifiso (Engeza Ikholomu — Ikholomu Ngokwezifiso) bese ufaka ifomula elandelayo efasiteleni elivelayo:

=#yangomhla(2020, [#»Isibaluli:d.1″], [#»Isibaluli:d.2″])

Lapha, umsebenzi we-#date unezimpikiswano ezintathu: unyaka, inyanga, nosuku, ngokulandelana. Ngemva kokuchofoza OK sithola ikholomu edingekayo enezinsuku ezivamile zangempelasonto, futhi sisuse amakholomu asele njengasesinyathelweni sesi-2

Isinyathelo sesi-2. Ukuguqula isicelo sibe umsebenzi

Umsebenzi wethu olandelayo ukuguqula umbuzo odalelwe u-2020 uwenze umsebenzi owenziwa emhlabeni wonke wanoma yimuphi unyaka (inombolo yonyaka kuzoba impikiswano yawo). Ukuze senze lokhu, senza lokhu okulandelayo:

1. Inweba (uma inganwetshiwe kakade) iphaneli Imibuzo (Imibuzo) kwesokunxele ewindini Lombuzo Wamandla:

2. Ngemva kokuguqula isicelo emsebenzini, ikhono lokubona izinyathelo ezenza isicelo futhi uzihlele kalula, ngeshwa, liyanyamalala. Ngakho-ke, kunengqondo ukwenza ikhophi yesicelo sethu futhi udlale ngaso kakade, futhi ushiye esangempela sigciniwe. Ukuze wenze lokhu, chofoza kwesokudla kufasitelana lesokunxele esicelweni sethu sekhalenda bese ukhetha umyalo othi Impinda.

Ukuchofoza kwesokudla futhi ekhophini ewumphumela yekhalenda(2) kuzokhetha umyalo Qamba kabusha (Qamba kabusha) bese ufaka igama elisha - makube njalo, isibonelo, fxYear:

3. Sivula ikhodi yomthombo wombuzo ngolimi lwangaphakathi Lombuzo Wamandla (libizwa kafushane ngokuthi “M”) sisebenzisa umyalo Umhleli Othuthukile ithebhu Buyekeza(Buka — Isihleli Esithuthukile) futhi senze izinguquko ezincane lapho ukuze siguqule isicelo sethu sibe umsebenzi wanoma yimuphi unyaka.

Bekuyi:

Ngemuva kwalokho:

Uma unentshisekelo emininingwaneni, lapha:

• (unyaka njengenombolo)=>  - simemezela ukuthi umsebenzi wethu uzoba ne-agumenti eyodwa yezinombolo - okuguquguqukayo Ngonyaka
• Ukunamathisela okuguquguqukayo Ngonyaka kusixhumanisi sewebhu isinyathelo Umthombo. Njengoba i-Power Query ingakuvumeli ukuthi unamathisele izinombolo nombhalo, siguqula inombolo yonyaka ibe umbhalo endizeni sisebenzisa umsebenzi. Inombolo.ToText
• Sifaka esikhundleni sokuguquguquka konyaka ka-2020 esinyathelweni sokuqala #”Kwengezwe into yangokwezifiso«, lapho sakha khona usuku ngezingcezu.
  

Ngemuva kokuchofoza Qeda isicelo sethu siba umsebenzi:

Isinyathelo 3. Ngenisa amakhalenda ayo yonke iminyaka

Into yokugcina esele ukwenza umbuzo wokugcina oyinhloko, ozolayisha idatha yayo yonke iminyaka etholakalayo futhi wengeze zonke izinsuku zeholide ezitholiwe etafuleni elilodwa. Kwalokhu:

1. Sichofoza iphaneli yombuzo kwesokunxele endaweni empunga engenalutho ngenkinobho yegundane elingakwesokudla bese ukhetha ngokulandelana Isicelo esisha - Eminye imithombo - Isicelo esingenalutho (Umbuzo Omusha - Ovela kweminye imithombo - Umbuzo ongenalutho):

2. Sidinga ukwenza uhlu lwayo yonke iminyaka esizocela ngayo amakhalenda, okungukuthi 2013, 2014 … 2020. Ukwenza lokhu, kubha yefomula yombuzo ongenalutho ovelayo, faka umyalo:

Isakhiwo:

={InomboloA..InomboloB}

… kokuthi Umbuzo Wamandla ikhiqiza uhlu lwamanani asuka ku-A ukuya ku-B. Isibonelo, isisho

={1..5}

… izokhiqiza uhlu lwe-1,2,3,4,5.

Hhayi-ke, ukuze singaboshelwe ngokuqinile ku-2020, sisebenzisa umsebenzi I-DateTime.LocalNow() - i-analogue yomsebenzi we-Excel NAMUHLA (NAMUHLA) kokuthi Umbuzo Wamandla - futhi ukhiphe kuwo, ngokulandelayo, unyaka wamanje ngomsebenzi Usuku.Unyaka.

3. Isethi yeminyaka ewumphumela, nakuba ibukeka yanele, akulona itafula le-Power Query, kodwa into ekhethekile - uhlu (Uhlu). Kodwa ukuyiguqulela etafuleni akuyona inkinga: vele uchofoze inkinobho Etafuleni (Etafuleni) ekhoneni eliphezulu kwesokunxele:

4. Qeda umugqa! Ukusebenzisa umsebenzi esiwudale ngaphambilini fxYear ohlwini lweminyaka oluwumphumela. Ukuze wenze lokhu, kuthebhu Yengeza ikholomu cindezela inkinobho Shayela umsebenzi wangokwezifiso (Engeza Ikholomu — Cela Umsebenzi Wangokwezifiso) futhi usethe ingxabano yayo kuphela - ikholomu Column1 eminyakeni edlule:

Ngemuva kokuchofoza OK umsebenzi wethu fxYear ukungenisa kuzosebenza ngokushintshana unyaka ngamunye futhi sizothola ikholomu lapho iseli ngalinye lizoqukatha ithebula elinezinsuku zezinsuku okungezona ezokusebenza (okuqukethwe kwethebula kubonakala ngokucacile uma uchofoza ngemuva kweseli eduze izwi Ithebula):

Kusele ukukhulisa okuqukethwe kwamathebula abekwe esidlekeni ngokuchofoza isithonjana esinemicibisholo ekabili enhlokweni yekholomu. nezinsuku (uphawu Sebenzisa igama lekholomu yoqobo njengesiqalo ingasuswa):

... futhi ngemva kokuchofoza OK sithola ebesikufuna - uhlu lwawo wonke amaholide kusukela ngo-2013 kuya kulo nyaka wamanje:

Ikholomu yokuqala, engadingekile kakade, ingasuswa, futhi okwesibili, setha uhlobo lwedatha Usuku (Usuku) ohlwini olwehliswayo esihlokweni sekholomu:

Umbuzo ngokwawo ungaqanjwa kabusha ngokuthile okunomqondo odlula Isicelo1 bese ulayisha imiphumela eshidini ngendlela yethebula “elihlakaniphile” eliguquguqukayo usebenzisa umyalo vala futhi ulande ithebhu Ikhaya (Ekhaya — Vala & Layisha):

Ungakwazi ukubuyekeza ikhalenda elidaliwe esikhathini esizayo ngokuchofoza kwesokudla etafuleni noma ubuze kufasitelana elingakwesokudla ngomyalo. Buyekeza & Londoloza. Noma sebenzisa inkinobho Vuselela konke ithebhu Idatha (Idethi - Vuselela Konke) noma isinqamuleli sekhibhodi Ctrl+Alt+F5.

Yilokho kuphela.

Manje awusophinde udinge ukuchitha isikhathi namandla okucabanga ukucinga nokubuyekeza uhlu lwamaholide - manje usunekhalenda lokukhiqiza "elingapheli". Kunoma yikuphi, inqobo nje uma ababhali besayithi http://xmlcalendar.ru/ besekela inzalo yabo, engiyethemba, izoba isikhathi eside kakhulu (ngiyabonga kubo futhi!).

• Ngenisa izinga le-bitcoin ukuze uphumelele ku-inthanethi usebenzisa Umbuzo Wamandla
• Ukuthola usuku lwebhizinisi olulandelayo usebenzisa umsebenzi we-WORKDAY
  
• Ukuthola impambana yezikhawu zedethi