Sisällys
Tuotantokalenteri eli lista päivämääristä, johon kaikki viralliset työpäivät ja vapaapäivät on merkitty vastaavasti – ehdottoman välttämätön asia jokaiselle Microsoft Excelin käyttäjälle. Käytännössä et voi tehdä ilman sitä:
- kirjanpitolaskelmissa (palkka, palvelusaika, lomat…)
- logistiikassa – toimitusaikojen oikeaan määrittämiseen, viikonloput ja juhlapyhät huomioon ottaen (muistatko klassisen "tulkoon lomien jälkeen?")
- projektinhallinnassa – ehtojen oikeaan arviointiin, ottaen huomioon jälleen työpäivät
- mikä tahansa toimintojen käyttö, kuten TYÖPÄIVÄ (TYÖPÄIVÄ) or PUHTAISTA TYÖNTEKIJÄT (VERKKOPÄIVÄT), koska ne vaativat argumenttina lomaluettelon
- käytettäessä Time Intelligence -toimintoja (kuten TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR jne.) Power Pivotissa ja Power BI:ssä
- … jne. jne. – paljon esimerkkejä.
Se on helpompaa niille, jotka työskentelevät yritysten ERP-järjestelmissä, kuten 1C tai SAP, koska tuotantokalenteri on sisäänrakennettu niihin. Mutta entä Excelin käyttäjät?
Voit tietysti pitää tällaista kalenteria manuaalisesti. Mutta sitten sinun on päivitettävä se vähintään kerran vuodessa (tai jopa useammin, kuten "jollyssa" 2020) syöttämällä huolellisesti kaikki hallitusmme keksimät viikonloput, siirrot ja vapaapäivät. Ja sitten toista tämä toimenpide joka seuraava vuosi. Tylsistyminen.
Mitäpä jos olisit vähän hullu ja tekisi "ikuisen" tehdaskalenterin Excelissä? Sellaisen, joka päivittää itsensä, ottaa tiedot Internetistä ja luo aina ajantasaisen listan vapaapäivistä myöhempää käyttöä varten laskelmissa? Houkutteleva?
Tämän tekeminen ei itse asiassa ole ollenkaan vaikeaa.
Tietolähde
Pääkysymys on, mistä tiedot saa? Etsiessäni sopivaa lähdettä kävin läpi useita vaihtoehtoja:
- Alkuperäiset asetukset julkaistaan hallituksen verkkosivuilla PDF-muodossa (tässä esim. yksi niistä) ja katoavat välittömästi – hyödyllistä tietoa ei niistä saa irti.
- Ensi silmäyksellä houkuttelevalta vaihtoehdolta vaikutti "Federation Open Data Portal", jossa on vastaava tietojoukko, mutta lähemmin tarkasteltuna kaikki osoittautui surulliseksi. Sivusto on hirveän hankala tuoda Exceliin, tekninen tuki ei vastaa (itseeristetty?), ja itse tiedot ovat siellä jo pitkään vanhentuneet – vuoden 2020 tuotantokalenteri on viimeksi päivitetty marraskuussa 2019 (häpeä!) ja , ei tietenkään sisällä esimerkiksi "koronavirustamme" ja "äänestysviikonloppua" vuonna 2020.
Pettynyt virallisiin lähteisiin, aloin kaivaa epävirallisia lähteitä. Niitä on paljon Internetissä, mutta suurin osa niistä on jälleen täysin sopimaton Exceliin tuomiseen ja antaa tuotantokalenterin kauniiden kuvien muodossa. Mutta se ei ole meidän tehtävämme ripustaa sitä seinälle, eihän?
Ja etsimisen aikana löydettiin vahingossa upea asia - sivusto http://xmlcalendar.ru/
Ilman turhia "röyhelöitä", yksinkertainen, kevyt ja nopea sivusto, teroitettu yhteen tehtävään - antaa kaikille halutun vuoden tuotantokalenteri XML-muodossa. Erinomainen!
Jos et yhtäkkiä tiedä, niin XML on tekstimuoto, jonka sisältö on merkitty erityisillä
Varmuuden vuoksi otin yhteyttä sivuston tekijöihin ja he vahvistivat, että sivusto on ollut olemassa 7 vuotta, sen tietoja päivitetään jatkuvasti (heillä on jopa sivukonttori githubissa tätä varten), eivätkä he aio sulkea sitä. Eikä minua haittaa ollenkaan, että sinä ja minä lataamme siitä dataa projekteillemme ja laskelmillemme Excelissä. On ilmainen. Kiva kuulla, että tällaisia ihmisiä on vielä olemassa! Kunnioittaminen!
Jäljelle jää ladata nämä tiedot Exceliin Power Query -apuohjelman avulla (Excel 2010-2013 -versioille se voidaan ladata ilmaiseksi Microsoftin verkkosivustolta, ja Excel 2016:n ja uudemmissa versioissa se on jo oletuksena sisäänrakennettu ).
Toiminnan logiikka on seuraava:
- Pyydämme ladata tietoja sivustolta yhden vuoden ajalta
- Pyynnön muuttaminen toiminnaksi
- Käytämme tätä toimintoa kaikkien käytettävissä olevien vuosien luetteloon vuodesta 2013 alkaen aina kuluvaan vuoteen – ja saamme "ikuisen" tuotantokalenterin automaattisella päivityksellä. Voila!
Vaihe 1. Tuo kalenteri vuodeksi
Lataa ensin tuotantokalenteri mille tahansa vuodelle, esimerkiksi vuodelle 2020. Voit tehdä tämän Excelissä siirtymällä välilehdelle Päiväys (Tai Tehokyselyjos asensit sen erillisenä lisäosana) ja valitse Internetistä (Verkosta). Liitä avautuvaan ikkunaan linkki vastaavaan vuoteen, joka on kopioitu sivustolta:
Napsauttamisen jälkeen OK esikatseluikkuna tulee näkyviin, jossa sinun on napsautettava painiketta Muunna tiedot (Muunna tiedot) or Muuttaaksesi tietoja (Muokkaa tietoja) ja pääsemme Power Queryn kyselyn muokkausikkunaan, jossa jatkamme tietojen käsittelyä:
Voit välittömästi poistaa turvallisesti oikeasta paneelista Pyydä parametreja (Kyselyasetukset) vaihe muokattu tyyppi (Muutettu tyyppi) Emme tarvitse häntä.
Lomat-sarakkeen taulukko sisältää koodit ja vapaapäivien kuvaukset – näet sen sisällön "pudottamalla" sen läpi kahdesti klikkaamalla vihreää sanaa Pöytä:
Palataksesi takaisin, sinun on poistettava oikeasta paneelista kaikki vaiheet, joihin olet palannut lähde (Lähde).
Toinen taulukko, johon pääsee samalla tavalla, sisältää juuri sen mitä tarvitsemme – kaikkien vapaapäivien päivämäärät:
On vielä käsiteltävä tämä levy, nimittäin:
1. Suodata vain lomapäivät (eli juhlapäivät) toisen sarakkeen mukaan Ominaisuus: t
2. Poista kaikki sarakkeet ensimmäistä lukuun ottamatta napsauttamalla hiiren oikealla painikkeella ensimmäisen sarakkeen otsikkoa ja valitsemalla komennon Poista muut sarakkeet (Poista muut sarakkeet):
3. Jaa ensimmäinen sarake pisteellä erikseen kuukaudelle ja päivälle komennolla Jaettu sarake – erottimen mukaan kieleke Muutos (Muunna — Jaa sarake — Erottimen mukaan):
4. Ja lopuksi luo laskettu sarake tavallisilla päivämäärillä. Voit tehdä tämän välilehdellä Sarakkeen lisääminen Napsauta painiketta Muokattu sarake (Lisää sarake — mukautettu sarake) ja kirjoita seuraava kaava näkyviin tulevaan ikkunaan:
=#päivätty(2020, [#»Attribuutti:d.1″], [#»Attribuutti:d.2″])
Tässä #date-operaattorilla on kolme argumenttia: vuosi, kuukausi ja päivä. Napsautuksen jälkeen OK saamme vaaditun sarakkeen tavallisilla viikonloppupäivämäärillä ja poistamme loput sarakkeet kuten vaiheessa 2
Vaihe 2. Pyynnön muuttaminen funktioksi
Seuraava tehtävämme on muuntaa vuodelle 2020 luotu kysely yleisfunktioksi mille tahansa vuodelle (vuosiluku on sen argumentti). Teemme tämän seuraavasti:
1. Paneelin laajentaminen (jos sitä ei ole jo laajennettu). Kyselyt (Kyselyt) vasemmalla Power Query -ikkunassa:
2. Kun pyyntö on muunnettu funktioksi, kyky nähdä pyynnön muodostavat vaiheet ja muokata niitä helposti katoaa. Siksi on järkevää tehdä kopio pyynnöstämme ja leikkimään jo sen kanssa ja jättää alkuperäinen varaukseen. Voit tehdä tämän napsauttamalla hiiren kakkospainikkeella kalenteripyyntöämme vasemmassa ruudussa ja valitsemalla Kopioi-komento.
Napsauta hiiren kakkospainikkeella uudelleen tuloksena olevaa kalenterin kopiota (2), jolloin komento valitaan nimetä uudelleen (Nimeä uudelleen) ja kirjoita uusi nimi – olkoon se esimerkiksi fxYear:
3. Avaamme kyselyn lähdekoodin sisäisellä Power Query -kielellä (sitä kutsutaan ytimekkäästi "M") komennolla Edistynyt editori kieleke Arvostelu(Näytä — Advanced Editor) ja tee siellä pieniä muutoksia muuttaaksesi pyyntömme toiminnaksi mille tahansa vuodelle.
Se oli:
Jälkeen:
Jos olet kiinnostunut yksityiskohdista, niin tässä:
- (vuosi numerona)=> – ilmoitamme, että funktiollamme on yksi numeerinen argumentti – muuttuja vuosi
- Liitä muuttuja vuosi Web-linkkiin vaiheessa lähde. Koska Power Query ei salli numeroiden ja tekstin liimaamista, muunnamme vuosiluvun tekstiksi lennossa funktiolla Number.To Text
- Korvaamme vuosimuuttujan vuodelle 2020 toiseksi viimeisessä vaiheessa #”Lisätty mukautettu objekti«, jossa loimme päivämäärän fragmenteista.
Napsauttamisen jälkeen Suorittaa loppuun pyynnöstämme tulee toiminto:
Vaihe 3. Tuo kalenterit kaikille vuosille
Viimeinen asia on tehdä viimeinen pääkysely, joka lataa tiedot kaikista saatavilla olevista vuosista ja lisää kaikki vastaanotetut lomapäivät yhteen taulukkoon. Tätä varten:
1. Napsauta hiiren oikealla painikkeella vasemmanpuoleista kyselypaneelia harmaassa tyhjässä tilassa ja valitse peräkkäin Uusi pyyntö – Muut lähteet – Tyhjä pyyntö (Uusi kysely – muista lähteistä – Tyhjä kysely):
2. Meidän on luotava luettelo kaikista vuosista, joille pyydämme kalentereita, eli 2013, 2014 … 2020. Voit tehdä tämän kirjoittamalla näkyviin tulevan tyhjän kyselyn kaavapalkkiin komennon:
Rakenne:
={NumeroA..NumeroB}
… Power Queryssa luo luettelon kokonaisluvuista A:sta B:hen. Esimerkiksi lauseke
={1..5}
… tuottaisi luettelon 1,2,3,4,5.
No, jotta emme olisi sidottu tiukasti vuoteen 2020, käytämme toimintoa DateTime.LocalNow() – Excel-funktion analogi TÄNÄÄN (TÄNÄÄN) Power Queryssä – ja poimi siitä vuorostaan kuluvan vuoden funktion mukaan Päivämäärä. Vuosi.
3. Tuloksena oleva vuosijoukko, vaikka se näyttää melko riittävältä, ei ole Power Queryn taulukko, vaan erityinen objekti - lista (Lista). Mutta sen muuntaminen taulukoksi ei ole ongelma: napsauta vain painiketta pöytään (taulukkoon) vasemmassa yläkulmassa:
4. Maaliviiva! Aiemmin luomamme funktion soveltaminen fxYear tuloksena olevaan vuosiluetteloon. Voit tehdä tämän välilehdellä Sarakkeen lisääminen painaa nappia Kutsu mukautettua toimintoa (Lisää sarake – kutsu mukautettua toimintoa) ja aseta sen ainoa argumentti - sarake Column1 vuosien saatossa:
Napsauttamisen jälkeen OK meidän toimintomme fxYear tuonti toimii vuorotellen joka vuosi ja saamme sarakkeen, jossa jokaisessa solussa on taulukko, jossa on vapaapäivien päivämäärät (taulukon sisältö näkyy selvästi, jos napsautat solun taustaa sana Pöytä):
Jäljelle jää sisäkkäisten taulukoiden sisällön laajentaminen napsauttamalla sarakkeen otsikossa olevaa kaksoisnuolikuvaketta Päivämäärät (puutiainen Käytä etuliitteenä alkuperäistä sarakkeen nimeä se voidaan poistaa):
… ja napsautuksen jälkeen OK saamme mitä halusimme – luettelo kaikista lomista vuodesta 2013 kuluvaan vuoteen:
Ensimmäinen, jo tarpeeton sarake, voidaan poistaa ja toiselle asettaa tietotyyppi data (Päivämäärä) sarakkeen otsikon avattavassa luettelossa:
Itse kysely voidaan nimetä uudelleen jollakin merkityksellisemmällä nimellä Pyyntö1 ja lataa sitten tulokset taulukkoon dynaamisen "älykkään" taulukon muodossa komennolla sulje ja lataa kieleke Koti (Koti - Sulje ja lataa):
Voit päivittää luodun kalenterin jatkossa napsauttamalla hiiren kakkospainikkeella taulukkoa tai kyselyä oikeanpuoleisessa ruudussa komennon kautta Päivitä & Tallenna. Tai käytä nappia Päivitä kaikki kieleke Päiväys (Päivämäärä - Päivitä kaikki) tai pikanäppäimellä Ctrl+muut+F5.
Siinä kaikki.
Nyt sinun ei enää koskaan tarvitse tuhlata aikaa ja ajatuksia lomaluettelon etsimiseen ja päivittämiseen – nyt sinulla on "ikuinen" tuotantokalenteri. Joka tapauksessa niin kauan kuin sivuston http://xmlcalendar.ru/ kirjoittajat tukevat jälkeläisiään, jotka toivottavasti kestävät erittäin, erittäin pitkään (kiitos heille jälleen!).
- Tuo bitcoin-kurssi kunnostautuaksesi Internetistä Power Queryn kautta
- Seuraavan arkipäivän etsiminen WORKDAY-toiminnolla
- Päivämäärävälien leikkauskohdan löytäminen