Innehåll
Produktionskalender, det vill säga en lista över datum, där alla officiella arbetsdagar och helgdagar markeras därefter – en absolut nödvändig sak för alla användare av Microsoft Excel. I praktiken kan du inte klara dig utan det:
- i redovisningsberäkningar (lön, tjänstgöringstid, semester ...)
- inom logistik – för korrekt bestämning av leveranstider, med hänsyn till helger och helgdagar (kom ihåg klassikern "kom igen efter semestern?")
- i projektledning – för korrekt uppskattning av villkor, med hänsyn, återigen, arbetsdagar utan arbetstid
- någon användning av funktioner som ARBETSDAG (ARBETSDAG) or RENA ARBETARE (NETWORKDAYS), eftersom de kräver en lista över helgdagar som argument
- när du använder Time Intelligence-funktioner (som TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, etc.) i Power Pivot och Power BI
- … etc. etc. – massor av exempel.
Det är lättare för de som arbetar i företags affärssystem som 1C eller SAP, då produktionskalendern är inbyggd i dem. Men hur är det med Excel-användare?
Du kan naturligtvis hålla en sådan kalender manuellt. Men då måste du uppdatera den minst en gång om året (eller ännu oftare, som i det "joly" 2020), och noggrant ange alla helger, överföringar och arbetsfria dagar som uppfunnits av vår regering. Och upprepa sedan denna procedur varje nästa år. Leda.
Vad sägs om att bli lite galen och göra en "evig" fabrikskalender i Excel? En som uppdaterar sig själv, tar data från Internet och alltid genererar en uppdaterad lista över arbetsfria dagar för efterföljande användning i eventuella beräkningar? Frestande?
Att göra detta är faktiskt inte alls svårt.
Datakälla
Huvudfrågan är var man får tag i data? På jakt efter en lämplig källa gick jag igenom flera alternativ:
- De ursprungliga dekreten publiceras på regeringens webbplats i PDF-format (här, till exempel en av dem) och försvinner omedelbart – användbar information går inte att dra ut ur dem.
- Ett frestande alternativ, vid första anblicken, verkade vara "Federationens öppna dataportal", där det finns en motsvarande datamängd, men vid närmare granskning visade sig allt vara sorgligt. Sajten är fruktansvärt obekväm att importera till Excel, teknisk support svarar inte (självisolerad?), och själva data är föråldrade där under lång tid – produktionskalendern för 2020 uppdaterades senast i november 2019 (skamligt!) och innehåller naturligtvis inte vårt "coronavirus" och "röstningshelgen" 2020, till exempel.
Desillusionerad av officiella källor började jag gräva inofficiella. Det finns många av dem på Internet, men de flesta av dem, återigen, är helt olämpliga att importera till Excel och ger ut en produktionskalender i form av vackra bilder. Men det är väl inte för oss att hänga den på väggen?
Och under sökningsprocessen upptäcktes en underbar sak av misstag - webbplatsen http://xmlcalendar.ru/
Utan onödiga "krusiduller", en enkel, lätt och snabb sajt, vässad för en uppgift – att ge alla en produktionskalender för önskat år i XML-format. Excellent!
Om du plötsligt inte är insatt är XML ett textformat med innehåll markerat med special
För säkerhets skull kontaktade jag författarna till webbplatsen och de bekräftade att webbplatsen har funnits i 7 år, data på den uppdateras ständigt (de har till och med en filial på github för detta) och de kommer inte att stänga den. Och jag har inget emot att du och jag laddar data från den för något av våra projekt och beräkningar i Excel. Är gratis. Det är skönt att veta att det fortfarande finns sådana här människor! Respekt!
Det återstår att ladda dessa data till Excel med Power Query-tillägget (för versioner av Excel 2010-2013 kan det laddas ner gratis från Microsofts webbplats, och i versioner av Excel 2016 och nyare är det redan inbyggt som standard ).
Handlingslogiken kommer att vara följande:
- Vi begär att ladda ner data från webbplatsen för ett år
- Att förvandla vår begäran till en funktion
- Vi tillämpar denna funktion på listan över alla tillgängliga år, från och med 2013 och fram till innevarande år – och vi får en "evig" produktionskalender med automatisk uppdatering. Voila!
Steg 1. Importera en kalender för ett år
Ladda först produktionskalendern för ett år, till exempel för 2020. För att göra detta, i Excel, gå till fliken Data (eller Strömfrågaom du installerade det som ett separat tillägg) och välj Från internet (Från webben). I fönstret som öppnas, klistra in länken till motsvarande år, kopierad från webbplatsen:
Efter att ha klickat på OK ett förhandsgranskningsfönster visas där du måste klicka på knappen Konvertera data (Omvandla data) or För att ändra data (Redigera data) och vi kommer till Power Query-frågeredigeringsfönstret, där vi kommer att fortsätta arbeta med data:
Omedelbart kan du säkert radera i den högra panelen Begär parametrar (Frågeinställningar) steg modifierad typ (Ändrad typ) Vi behöver honom inte.
Tabellen i semesterkolumnen innehåller koder och beskrivningar av arbetsfria dagar – du kan se dess innehåll genom att "falla igenom" den två gånger genom att klicka på det gröna ordet Bord:
För att gå tillbaka måste du ta bort i den högra panelen alla steg som har dykt upp tillbaka till Källa (Källa).
Den andra tabellen, som kan nås på liknande sätt, innehåller exakt vad vi behöver – datumen för alla icke-arbetsdagar:
Det återstår att bearbeta denna platta, nämligen:
1. Filtrera endast helgdagar (dvs. ettor) efter den andra kolumnen Attribut:t
2. Ta bort alla kolumner utom den första – genom att högerklicka på rubriken i den första kolumnen och välja kommandot Ta bort andra kolumner (Ta bort andra kolumner):
3. Dela första kolumn för punkt separat för månad och dag med kommando Dela kolumn – Med avgränsare fliken Transformation (Transformera — Dela kolumn — Med avgränsare):
4. Och slutligen skapa en beräknad kolumn med normala datum. För att göra detta, på fliken Lägger till en kolumn klicka på knappen Anpassad kolumn (Lägg till kolumn – anpassad kolumn) och skriv in följande formel i fönstret som visas:
=#daterad(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])
Här har #date-operatorn tre argument: år, månad respektive dag. Efter att ha klickat på OK vi får den obligatoriska kolumnen med normala helgdatum och tar bort de återstående kolumnerna som i steg 2
Steg 2. Förvandla begäran till en funktion
Vår nästa uppgift är att konvertera frågan skapad för 2020 till en universell funktion för vilket år som helst (årstalet kommer att vara dess argument). För att göra detta gör vi följande:
1. Utökar (om inte redan expanderat) panelen förfrågningar (Frågor) till vänster i Power Query-fönstret:
2. Efter att ha konverterat begäran till en funktion försvinner tyvärr möjligheten att se stegen som utgör begäran och enkelt redigera dem. Därför är det vettigt att göra en kopia av vår förfrågan och leka redan med den och lämna originalet i reserv. För att göra detta, högerklicka i den vänstra rutan på vår kalenderförfrågan och välj kommandot Duplicera.
Högerklicka igen på den resulterande kopian av kalender(2) kommer att välja kommandot Byt namn (Döp om) och ange ett nytt namn – låt det vara t.ex. fxår:
3. Vi öppnar frågekällan på det interna Power Query-språket (det kallas kortfattat "M") med kommandot Avancerad redaktör fliken översyn(Visa — Avancerad redigerare) och gör små ändringar där för att göra vår begäran till en funktion för vilket år som helst.
Det var:
Efter:
Om du är intresserad av detaljerna, så här:
- (år som nummer)=> – vi deklarerar att vår funktion kommer att ha ett numeriskt argument – en variabel år
- Klistrar in variabeln år till webblänk i steg Källa. Eftersom Power Query inte tillåter dig att limma siffror och text konverterar vi årtalet till text i farten med funktionen Number.ToText
- Vi ersätter årsvariabeln för 2020 i det näst sista steget #”Lägg till anpassat objekt«, där vi bildade datumet från fragmenten.
Efter att ha klickat på Finish vår begäran blir en funktion:
Steg 3. Importera kalendrar för alla år
Det sista som återstår är att göra den sista huvudfrågan, som laddar upp data för alla tillgängliga år och lägger till alla mottagna semesterdatum i en tabell. För detta:
1. Vi klickar i den vänstra frågepanelen i ett grått tomt utrymme med höger musknapp och väljer sekventiellt Ny begäran – Andra källor – Tom begäran (Ny fråga – Från andra källor – Tom fråga):
2. Vi måste generera en lista över alla år för vilka vi kommer att begära kalendrar, dvs 2013, 2014 … 2020. För att göra detta, i formelfältet för den tomma frågan som visas, skriv in kommandot:
Strukturera:
={NumberA..NumberB}
… i Power Query genererar en lista med heltal från A till B. Till exempel uttrycket
={1..5}
… skulle producera en lista med 1,2,3,4,5.
Jo, för att inte vara strikt bundna till 2020 använder vi funktionen DateTime.LocalNow() – analog till Excel-funktionen I DAG (I DAG) i Power Query – och extrahera från den i sin tur det aktuella året efter funktionen Datum.År.
3. Den resulterande uppsättningen år, även om den ser ganska tillräcklig ut, är inte en tabell för Power Query, utan ett speciellt objekt – lista (Lista). Men att konvertera den till en tabell är inget problem: klicka bara på knappen Till bord (Till bordet) i det övre vänstra hörnet:
4. Mållinje! Använder funktionen vi skapade tidigare fxår till den resulterande listan över år. För att göra detta, på fliken Lägger till en kolumn tryck på knappen Ring anpassad funktion (Lägg till kolumn — Anropa anpassad funktion) och ställ in dess enda argument – kolumnen Column1 över åren:
Efter att ha klickat på OK vår funktion fxår importen kommer att fungera i tur och ordning för varje år och vi får en kolumn där varje cell kommer att innehålla en tabell med datum för icke-arbetsdagar (innehållet i tabellen syns tydligt om du klickar i bakgrunden av cellen bredvid ordet Bord):
Det återstår att utöka innehållet i kapslade tabeller genom att klicka på ikonen med dubbla pilar i kolumnrubriken Datum (bock Använd det ursprungliga kolumnnamnet som prefix det kan tas bort):
... och efter att ha klickat på OK vi får vad vi ville ha – en lista över alla helgdagar från 2013 till innevarande år:
Den första, redan onödiga kolumnen, kan tas bort, och för den andra, ställ in datatypen datum (Datum) i rullgardinsmenyn i kolumnrubriken:
Själva frågan kan döpas om till något mer meningsfullt än Begäran1 och ladda sedan upp resultaten till arket i form av en dynamisk "smart" tabell med kommandot stäng och ladda ner fliken Hem (Hem — Stäng & ladda):
Du kan uppdatera den skapade kalendern i framtiden genom att högerklicka på tabellen eller fråga i den högra rutan genom kommandot Uppdatera & spara. Eller använd knappen Uppdatera alla fliken Data (Datum – Uppdatera alla) eller kortkommandon ctrl+andra+F5.
Det är allt.
Nu behöver du aldrig mer slösa tid och tankar på att leta efter och uppdatera listan över helgdagar – nu har du en "evig" produktionskalender. I vilket fall som helst, så länge författarna till webbplatsen http://xmlcalendar.ru/ stödjer sina avkommor, vilket jag hoppas kommer att vara under en väldigt, väldigt lång tid (tack vare dem igen!).
- Importera bitcoinkurs för att excel från internet via Power Query
- Hitta nästa arbetsdag med funktionen WORKDAY
- Hitta skärningspunkten för datumintervall