Περιεχόμενα
Ημερολόγιο παραγωγής, δηλαδή μια λίστα ημερομηνιών, όπου όλες οι επίσημες εργάσιμες ημέρες και αργίες σημειώνονται ανάλογα – κάτι απολύτως απαραίτητο για κάθε χρήστη του Microsoft Excel. Στην πράξη, δεν μπορείτε να κάνετε χωρίς αυτό:
- στους λογιστικούς υπολογισμούς (μισθός, προϋπηρεσία, διακοπές…)
- στα logistics – για τον σωστό προσδιορισμό των χρόνων παράδοσης, λαμβάνοντας υπόψη τα Σαββατοκύριακα και τις αργίες (θυμηθείτε το κλασικό «έλα μετά τις διακοπές;»)
- στη διαχείριση έργου – για τη σωστή εκτίμηση των όρων, λαμβάνοντας υπόψη, και πάλι, εργάσιμες-μη εργάσιμες ημέρες
- οποιαδήποτε χρήση λειτουργιών όπως ΗΜΕΡΑ ΕΡΓΑΣΙΑΣ (ΗΜΕΡΑ ΕΡΓΑΣΙΑΣ) or ΑΓΝΟΙ ΕΡΓΑΤΕΣ (NETWORKDAYS), γιατί απαιτούν ως επιχείρημα μια λίστα διακοπών
- όταν χρησιμοποιείτε λειτουργίες Time Intelligence (όπως TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, κ.λπ.) στο Power Pivot και το Power BI
- … κλπ. κλπ. – πολλά παραδείγματα.
Είναι πιο εύκολο για όσους εργάζονται σε εταιρικά συστήματα ERP όπως το 1C ή το SAP, καθώς το ημερολόγιο παραγωγής είναι ενσωματωμένο σε αυτά. Τι γίνεται όμως με τους χρήστες του Excel;
Μπορείτε, φυσικά, να κρατήσετε ένα τέτοιο ημερολόγιο χειροκίνητα. Αλλά τότε θα πρέπει να το ενημερώνετε τουλάχιστον μία φορά το χρόνο (ή ακόμα πιο συχνά, όπως στο «χαρούμενο» 2020), εισάγοντας προσεκτικά όλα τα Σαββατοκύριακα, τις μεταφορές και τις μη εργάσιμες ημέρες που εφευρέθηκε από την κυβέρνησή μας. Και μετά επαναλάβετε αυτή τη διαδικασία κάθε επόμενο χρόνο. Ανία.
Τι θα έλεγες να τρελαθείς λίγο και να φτιάξεις ένα «αιώνιο» εργοστασιακό ημερολόγιο στο Excel; Αυτό που ενημερώνεται, λαμβάνει δεδομένα από το Διαδίκτυο και δημιουργεί πάντα μια ενημερωμένη λίστα με μη εργάσιμες ημέρες για μετέπειτα χρήση σε οποιονδήποτε υπολογισμό; Πειρασμός?
Για να γίνει αυτό, στην πραγματικότητα, δεν είναι καθόλου δύσκολο.
Πηγή δεδομένων
Το κύριο ερώτημα είναι από πού να ληφθούν τα δεδομένα; Αναζητώντας μια κατάλληλη πηγή, πέρασα από διάφορες επιλογές:
- Τα αρχικά διατάγματα δημοσιεύονται στον ιστότοπο της κυβέρνησης σε μορφή PDF (εδώ, ένα από αυτά, για παράδειγμα) και εξαφανίζονται αμέσως – δεν μπορούν να εξαχθούν χρήσιμες πληροφορίες από αυτά.
- Μια δελεαστική επιλογή, με την πρώτη ματιά, φαινόταν να είναι η «Ανοιχτή Πύλη Δεδομένων της Ομοσπονδίας», όπου υπάρχει ένα αντίστοιχο σύνολο δεδομένων, αλλά, μετά από πιο προσεκτική εξέταση, όλα αποδείχθηκαν λυπηρά. Ο ιστότοπος είναι τρομερά άβολος για εισαγωγή στο Excel, η τεχνική υποστήριξη δεν ανταποκρίνεται (αυτοαπομονώνεται;) και τα ίδια τα δεδομένα είναι ξεπερασμένα εκεί εδώ και πολύ καιρό - το ημερολόγιο παραγωγής για το 2020 ενημερώθηκε τελευταία φορά τον Νοέμβριο του 2019 (κρίμα!) και , φυσικά, δεν περιέχει τον «κορωνοϊό» μας και το Σαββατοκύριακο «ψηφοφορίας» του 2020, για παράδειγμα.
Απογοητευμένος από επίσημες πηγές, άρχισα να σκάβω ανεπίσημες. Υπάρχουν πολλά από αυτά στο Διαδίκτυο, αλλά τα περισσότερα από αυτά, και πάλι, είναι εντελώς ακατάλληλα για εισαγωγή στο Excel και δίνουν ένα ημερολόγιο παραγωγής με τη μορφή όμορφων εικόνων. Αλλά δεν είναι για μας να το κρεμάσουμε στον τοίχο, σωστά;
Και στη διαδικασία αναζήτησης, ανακαλύφθηκε κατά λάθος ένα υπέροχο πράγμα - ο ιστότοπος http://xmlcalendar.ru/
Χωρίς περιττά «διακοσμητικά», ένας απλός, ελαφρύς και γρήγορος ιστότοπος, βελτιωμένος για μία εργασία – για να δώσει σε όλους ένα ημερολόγιο παραγωγής για το επιθυμητό έτος σε μορφή XML. Εξοχος!
Εάν, ξαφνικά, δεν γνωρίζετε, τότε η XML είναι μια μορφή κειμένου με περιεχόμενο επισημασμένο με ειδικές
Για κάθε ενδεχόμενο, επικοινώνησα με τους δημιουργούς του ιστότοπου και επιβεβαίωσαν ότι ο ιστότοπος υπάρχει εδώ και 7 χρόνια, τα δεδομένα σε αυτό ενημερώνονται συνεχώς (έχουν ακόμη και υποκατάστημα στο github για αυτό) και δεν πρόκειται να το κλείσουν. Και δεν με πειράζει καθόλου που εσείς και εγώ φορτώνουμε δεδομένα από αυτό για οποιοδήποτε από τα έργα και τους υπολογισμούς μας στο Excel. Ειναι δωρεάν. Είναι ωραίο να ξέρεις ότι υπάρχουν ακόμα τέτοιοι άνθρωποι! Σεβασμός!
Απομένει να φορτωθούν αυτά τα δεδομένα στο Excel χρησιμοποιώντας το πρόσθετο Power Query (για εκδόσεις του Excel 2010-2013 μπορείτε να το λάβετε δωρεάν από τον ιστότοπο της Microsoft και σε εκδόσεις του Excel 2016 και νεότερες είναι ήδη ενσωματωμένο από προεπιλογή ).
Η λογική των ενεργειών θα είναι η εξής:
- Κάνουμε αίτημα λήψης δεδομένων από τον ιστότοπο για οποιοδήποτε έτος
- Μετατροπή του αιτήματός μας σε συνάρτηση
- Εφαρμόζουμε αυτή τη λειτουργία στη λίστα όλων των διαθέσιμων ετών, από το 2013 και μέχρι το τρέχον έτος – και λαμβάνουμε ένα «αέναο» ημερολόγιο παραγωγής με αυτόματη ενημέρωση. Voila!
Βήμα 1. Εισαγάγετε ένα ημερολόγιο για ένα έτος
Αρχικά, φορτώστε το ημερολόγιο παραγωγής για οποιοδήποτε έτος, για παράδειγμα, για το 2020. Για να το κάνετε αυτό, στο Excel, μεταβείτε στην καρτέλα ημερομηνία (Ή Ερώτημα ισχύοςεάν το εγκαταστήσατε ως ξεχωριστό πρόσθετο) και επιλέξτε Από το διαδίκτυο (Από τον Ιστό). Στο παράθυρο που ανοίγει, επικολλήστε τον σύνδεσμο για το αντίστοιχο έτος, που αντιγράφηκε από τον ιστότοπο:
Μετά το κλικ OK εμφανίζεται ένα παράθυρο προεπισκόπησης, στο οποίο πρέπει να κάνετε κλικ στο κουμπί Μετατροπή Δεδομένων (Μετατροπή δεδομένων) or Για να αλλάξετε τα δεδομένα (Επεξεργασία δεδομένων) και θα φτάσουμε στο παράθυρο επεξεργασίας ερωτημάτων Power Query, όπου θα συνεχίσουμε να εργαζόμαστε με τα δεδομένα:
Αμέσως μπορείτε να διαγράψετε με ασφάλεια στον δεξιό πίνακα Παράμετροι αιτήματος (Ρυθμίσεις ερωτήματος) βήμα τροποποιημένου τύπου (Αλλαγμένος τύπος) Δεν τον χρειαζόμαστε.
Ο πίνακας στη στήλη αργίες περιέχει κωδικούς και περιγραφές μη εργάσιμων ημερών – μπορείτε να δείτε το περιεχόμενό του «πέφτοντας» δύο φορές κάνοντας κλικ στην πράσινη λέξη Τραπέζι:
Για να επιστρέψετε, θα πρέπει να διαγράψετε στον δεξιό πίνακα όλα τα βήματα που εμφανίστηκαν ξανά Πηγή (Πηγή).
Ο δεύτερος πίνακας, στον οποίο μπορείτε να έχετε πρόσβαση με παρόμοιο τρόπο, περιέχει ακριβώς αυτό που χρειαζόμαστε – τις ημερομηνίες όλων των μη εργάσιμων ημερών:
Απομένει να επεξεργαστούμε αυτό το πιάτο, δηλαδή:
1. Φιλτράρετε μόνο τις ημερομηνίες αργιών (δηλαδή αυτές) κατά τη δεύτερη στήλη Ιδιότητα:t
2. Διαγράψτε όλες τις στήλες εκτός από την πρώτη – κάνοντας δεξί κλικ στην επικεφαλίδα της πρώτης στήλης και επιλέγοντας την εντολή Διαγραφή άλλων στηλών (Κατάργηση άλλων στηλών):
3. Διαχωρίστε την πρώτη στήλη με τελεία ξεχωριστά για μήνα και ημέρα με εντολή Διαίρεση στήλης – Κατά οριοθέτη αυτί Μεταμόρφωση (Μετατροπή — Διαίρεση στήλης — Κατά οριοθέτη):
4. Και τέλος δημιουργήστε μια υπολογισμένη στήλη με κανονικές ημερομηνίες. Για να το κάνετε αυτό, στην καρτέλα Προσθήκη στήλης κάντε κλικ στο κουμπί Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη) και εισάγετε τον ακόλουθο τύπο στο παράθυρο που εμφανίζεται:
=#χρονολογημένος(2020, [#»Χαρακτηριστικό:d.1″], [#»Χαρακτηριστικό:d.2″])
Εδώ, ο τελεστής #date έχει τρία ορίσματα: έτος, μήνας και ημέρα, αντίστοιχα. Αφού κάνετε κλικ στο OK παίρνουμε την απαιτούμενη στήλη με τις κανονικές ημερομηνίες του Σαββατοκύριακου και διαγράφουμε τις υπόλοιπες στήλες όπως στο βήμα 2
Βήμα 2. Μετατροπή του αιτήματος σε συνάρτηση
Το επόμενο καθήκον μας είναι να μετατρέψουμε το ερώτημα που δημιουργήθηκε για το 2020 σε καθολική συνάρτηση για οποιοδήποτε έτος (ο αριθμός έτους θα είναι το όρισμά του). Για να γίνει αυτό, κάνουμε τα εξής:
1. Επέκταση (αν δεν έχει ήδη αναπτυχθεί) του πίνακα Ερωτήσεις (Ερωτήματα) στα αριστερά στο παράθυρο Power Query:
2. Μετά τη μετατροπή του αιτήματος σε συνάρτηση, η δυνατότητα να δείτε τα βήματα που απαρτίζουν το αίτημα και να τα επεξεργαστείτε εύκολα, δυστυχώς, εξαφανίζεται. Ως εκ τούτου, είναι λογικό να δημιουργήσουμε ένα αντίγραφο του αιτήματός μας και να το χαζέψουμε ήδη και να αφήσουμε το πρωτότυπο στο αποθεματικό. Για να το κάνετε αυτό, κάντε δεξί κλικ στο αριστερό παράθυρο στο αίτημα ημερολογίου μας και επιλέξτε την εντολή Διπλότυπο.
Κάνοντας ξανά δεξί κλικ στο αντίγραφο του ημερολογίου(2) που προκύπτει θα επιλέξετε την εντολή Μετονομασία (Μετονομάζω) και πληκτρολογήστε ένα νέο όνομα - ας είναι, για παράδειγμα, fxYear:
3. Ανοίγουμε τον πηγαίο κώδικα του ερωτήματος στην εσωτερική γλώσσα Power Query (ονομάζεται συνοπτικά "M") χρησιμοποιώντας την εντολή Advanced Editor αυτί Βαθμολογία Κριτικής(Προβολή — Σύνθετη επεξεργασία) και κάντε μικρές αλλαγές εκεί για να μετατρέψετε το αίτημά μας σε λειτουργία για οποιοδήποτε έτος.
Ήταν:
Μετά:
Αν σας ενδιαφέρουν οι λεπτομέρειες, τότε εδώ:
- (έτος ως αριθμός)=> – δηλώνουμε ότι η συνάρτησή μας θα έχει ένα αριθμητικό όρισμα – μια μεταβλητή έτος
- Επικόλληση της μεταβλητής έτος στον σύνδεσμο web στο βήμα Πηγή. Δεδομένου ότι το Power Query δεν σας επιτρέπει να κολλήσετε αριθμούς και κείμενο, μετατρέπουμε τον αριθμό έτους σε κείμενο εν κινήσει χρησιμοποιώντας τη συνάρτηση Number.ToText
- Αντικαθιστούμε τη μεταβλητή έτους για το 2020 στο προτελευταίο βήμα #"Προστέθηκε προσαρμοσμένο αντικείμενο«, όπου σχηματίσαμε την ημερομηνία από τα θραύσματα.
Μετά το κλικ φινίρισμα Το αίτημά μας γίνεται συνάρτηση:
Βήμα 3. Εισαγάγετε ημερολόγια για όλα τα έτη
Το τελευταίο πράγμα που απομένει είναι να κάνετε το τελευταίο κύριο ερώτημα, το οποίο θα ανεβάσει δεδομένα για όλα τα διαθέσιμα έτη και θα προσθέσει όλες τις ληφθείσες ημερομηνίες αργιών σε έναν πίνακα. Για αυτό:
1. Κάνουμε κλικ στο αριστερό πλαίσιο ερωτήματος σε ένα γκρίζο κενό χώρο με το δεξί κουμπί του ποντικιού και επιλέγουμε διαδοχικά Νέο αίτημα – Άλλες πηγές – Κενό αίτημα (Νέο ερώτημα — Από άλλες πηγές — Κενό ερώτημα):
2. Πρέπει να δημιουργήσουμε μια λίστα με όλα τα έτη για τα οποία θα ζητήσουμε ημερολόγια, δηλαδή 2013, 2014… 2020. Για να το κάνετε αυτό, στη γραμμή τύπων του κενού ερωτήματος που εμφανίζεται, πληκτρολογήστε την εντολή:
Δομή:
={NumberA..NumberB}
… στο Power Query δημιουργεί μια λίστα ακεραίων από το Α έως το Β. Για παράδειγμα, η έκφραση
={1..5}
… θα παρήγαγε μια λίστα με 1,2,3,4,5.
Λοιπόν, για να μην συνδεθούμε αυστηρά με το 2020, χρησιμοποιούμε τη συνάρτηση DateTime.LocalNow() – ανάλογο της συνάρτησης Excel ΣΉΜΕΡΑ (ΣΗΜΕΡΑ) στο Power Query – και εξάγετε από αυτό, με τη σειρά του, το τρέχον έτος από τη συνάρτηση Ημερομηνία.Έτος.
3. Το σύνολο των ετών που προκύπτει, αν και φαίνεται αρκετά επαρκές, δεν είναι ένας πίνακας για το Power Query, αλλά ένα ειδικό αντικείμενο – λίστα (Λίστα). Αλλά η μετατροπή του σε πίνακα δεν είναι πρόβλημα: απλώς κάντε κλικ στο κουμπί Στο τραπέζι (Στο τραπέζι) στην επάνω αριστερή γωνία:
4. Γραμμή του τερματισμού! Εφαρμόζοντας τη συνάρτηση που δημιουργήσαμε νωρίτερα fxYear στον κατάλογο των ετών που προκύπτει. Για να το κάνετε αυτό, στην καρτέλα Προσθήκη στήλης πάτα το κουμπί Κλήση προσαρμοσμένης λειτουργίας (Προσθήκη στήλης — Κλήση προσαρμοσμένης συνάρτησης) και όρισε το μόνο του όρισμα – τη στήλη Column1 με τα χρόνια:
Μετά το κλικ OK η λειτουργία μας fxYear η εισαγωγή θα λειτουργεί με τη σειρά για κάθε έτος και θα λάβουμε μια στήλη όπου κάθε κελί θα περιέχει έναν πίνακα με τις ημερομηνίες των μη εργάσιμων ημερών (τα περιεχόμενα του πίνακα είναι ευδιάκριτα αν κάνετε κλικ στο φόντο του κελιού δίπλα στο η λέξη Τραπέζι):
Απομένει να επεκτείνετε τα περιεχόμενα των ένθετων πινάκων κάνοντας κλικ στο εικονίδιο με τα διπλά βέλη στην κεφαλίδα της στήλης Ημερομηνίες (τσιμπούρι Χρησιμοποιήστε το αρχικό όνομα στήλης ως πρόθεμα μπορεί να αφαιρεθεί):
… και αφού κάνετε κλικ στο OK παίρνουμε αυτό που θέλαμε – μια λίστα με όλες τις διακοπές από το 2013 έως το τρέχον έτος:
Η πρώτη, ήδη περιττή στήλη, μπορεί να διαγραφεί και για τη δεύτερη, ορίστε τον τύπο δεδομένων Ραντεβού (Ημερομηνία) στην αναπτυσσόμενη λίστα στην επικεφαλίδα της στήλης:
Το ίδιο το ερώτημα μπορεί να μετονομαστεί σε κάτι πιο σημαντικό από Αίτημα 1 και στη συνέχεια ανεβάστε τα αποτελέσματα στο φύλλο με τη μορφή ενός δυναμικού «έξυπνου» πίνακα χρησιμοποιώντας την εντολή κλείστε και κατεβάστε αυτί Αρχική (Αρχική — Κλείσιμο & Φόρτωση):
Μπορείτε να ενημερώσετε το ημερολόγιο που δημιουργήθηκε στο μέλλον κάνοντας δεξί κλικ στον πίνακα ή ερώτημα στο δεξί παράθυρο μέσω της εντολής Ενημέρωση & Αποθήκευση. Ή χρησιμοποιήστε το κουμπί Ανανέωση όλων αυτί ημερομηνία (Ημερομηνία — Ανανέωση όλων) ή συντόμευση πληκτρολογίου Ctrl+άλλος+F5.
Αυτό είναι όλο.
Τώρα δεν χρειάζεται ποτέ ξανά να χάνετε χρόνο και σκέψη για να αναζητήσετε και να ενημερώσετε τη λίστα των διακοπών – τώρα έχετε ένα «αέναο» ημερολόγιο παραγωγής. Σε κάθε περίπτωση, αρκεί οι συντάκτες του ιστότοπου http://xmlcalendar.ru/ να υποστηρίζουν τους απογόνους τους, οι οποίοι, ελπίζω, θα είναι για πολύ, πάρα πολύ καιρό (ευχαριστώ και πάλι!).
- Εισαγάγετε ποσοστό bitcoin για να υπερτερείτε από το διαδίκτυο μέσω του Power Query
- Εύρεση της επόμενης εργάσιμης ημέρας χρησιμοποιώντας τη συνάρτηση WORKDAY
- Εύρεση της τομής των διαστημάτων ημερομηνιών