Ας υποθέσουμε ότι εκτελείτε πολλά έργα με διαφορετικούς προϋπολογισμούς και θέλετε να απεικονίσετε το κόστος σας για καθένα από αυτά. Δηλαδή, από αυτόν τον πίνακα πηγής:
.. πάρε κάτι σαν αυτό:
Με άλλα λόγια, πρέπει να κατανείμετε τον προϋπολογισμό στις ημέρες κάθε έργου και να λάβετε μια απλοποιημένη έκδοση του γραφήματος Gantt του έργου. Το να το κάνετε αυτό με τα χέρια σας είναι μακρύ και βαρετό, οι μακροεντολές είναι δύσκολες, αλλά το Power Query για Excel σε μια τέτοια κατάσταση δείχνει τη δύναμή του σε όλο του το μεγαλείο.
Ερώτημα ισχύος είναι ένα πρόσθετο από τη Microsoft που μπορεί να εισάγει δεδομένα στο Excel από σχεδόν οποιαδήποτε πηγή και στη συνέχεια να τα μετατρέψει με πολλούς διαφορετικούς τρόπους. Στο Excel 2016, αυτό το πρόσθετο είναι ήδη ενσωματωμένο από προεπιλογή και για το Excel 2010-2013 μπορεί να ληφθεί από τον ιστότοπο της Microsoft και στη συνέχεια να εγκατασταθεί στον υπολογιστή σας.
Αρχικά, ας μετατρέψουμε τον αρχικό μας πίνακα σε έναν «έξυπνο» πίνακα επιλέγοντας την εντολή Μορφοποίηση ως πίνακα αυτί Αρχική (Αρχική σελίδα — Μορφοποίηση ως πίνακα) ή πατώντας τη συντόμευση πληκτρολογίου Ctrl+T :
Στη συνέχεια, μεταβείτε στην καρτέλα ημερομηνία (εάν έχετε Excel 2016) ή στην καρτέλα Ερώτημα ισχύος (εάν έχετε Excel 2010-2013 και έχετε εγκαταστήσει το Power Query ως ξεχωριστό πρόσθετο) και κάντε κλικ στο κουμπί Από πίνακα / Εύρος. :
Ο έξυπνος πίνακας μας φορτώνεται στο πρόγραμμα επεξεργασίας ερωτημάτων Power Query, όπου το πρώτο βήμα είναι να ρυθμίσετε τις μορφές αριθμών για κάθε στήλη χρησιμοποιώντας τα αναπτυσσόμενα μενού στην κεφαλίδα του πίνακα:
Για να υπολογίσετε τον προϋπολογισμό ανά ημέρα, πρέπει να υπολογίσετε τη διάρκεια κάθε έργου. Για να το κάνετε αυτό, επιλέξτε (κρατήστε πατημένο το πλήκτρο Ctrl) στήλη πρώτη φινίρισμα, και μετά Αρχική και διάλεξε ομάδα Προσθήκη στήλης – Ημερομηνία – Αφαίρεση ημερών (Προσθήκη στήλης — Ημερομηνία — Αφαίρεση ημερών):
Οι αριθμοί που προκύπτουν είναι 1 λιγότεροι από τον απαραίτητο, γιατί υποτίθεται ότι ξεκινάμε κάθε έργο την πρώτη μέρα το πρωί και τελειώνουμε την τελευταία μέρα το βράδυ. Επομένως, επιλέξτε τη στήλη που προκύπτει και προσθέστε μια μονάδα σε αυτήν χρησιμοποιώντας την εντολή Μετασχηματισμός – Τυπικό – Προσθήκη (Μετατροπή — Τυπικό — Προσθήκη):
Τώρα ας προσθέσουμε μια στήλη όπου υπολογίζουμε τον προϋπολογισμό ανά ημέρα. Για να το κάνετε αυτό, στην καρτέλα Προσθήκη στήλης Δεν παίζω Προσαρμοσμένη στήλη (Προσαρμοσμένη στήλη) και στο παράθυρο που εμφανίζεται, πληκτρολογήστε το όνομα του νέου πεδίου και τον τύπο υπολογισμού, χρησιμοποιώντας τα ονόματα των στηλών από τη λίστα:
Τώρα η πιο λεπτή στιγμή – δημιουργούμε μια άλλη υπολογισμένη στήλη με μια λίστα ημερομηνιών από την αρχή μέχρι το τέλος με ένα βήμα 1 ημέρας. Για να το κάνετε αυτό, πατήστε ξανά το κουμπί Προσαρμοσμένη στήλη (Προσαρμοσμένη στήλη) και χρησιμοποιήστε την ενσωματωμένη γλώσσα Power Query M, η οποία ονομάζεται Λίστα.Ημερομηνίες:
Αυτή η συνάρτηση έχει τρία ορίσματα:
- ημερομηνία έναρξης - στην περίπτωσή μας, λαμβάνεται από τη στήλη Αρχική
- ο αριθμός των ημερομηνιών που θα δημιουργηθούν – στην περίπτωσή μας, αυτός είναι ο αριθμός των ημερών για κάθε έργο, που μετρήσαμε νωρίτερα στη στήλη Αφαίρεση
- χρονικό βήμα – καθορισμένο από το σχεδιασμό #duration(1,0,0,0), που σημαίνει στη γλώσσα του Μ – μια μέρα, ώρες μηδέν, μηδέν λεπτά, μηδέν δευτερόλεπτα.
Μετά το κλικ OK παίρνουμε μια λίστα (Λίστα) ημερομηνιών, η οποία μπορεί να επεκταθεί σε νέες γραμμές χρησιμοποιώντας το κουμπί στην κεφαλίδα του πίνακα:
… και παίρνουμε:
Τώρα το μόνο που απομένει είναι να συμπτύξετε τον πίνακα, χρησιμοποιώντας τις ημερομηνίες που δημιουργούνται ως ονόματα για τις νέες στήλες. Η ομάδα είναι υπεύθυνη για αυτό. Στήλη λεπτομερειών (Συγκεντρωτική στήλη) αυτί Μετατρέπω (Μεταμορφώνω):
Μετά το κλικ OK έχουμε ένα αποτέλεσμα πολύ κοντά στο επιθυμητό:
Το Null είναι, σε αυτήν την περίπτωση, ένα ανάλογο ενός κενού κελιού στο Excel.
Απομένει να αφαιρέσετε τις περιττές στήλες και να ξεφορτώσετε τον πίνακα που προκύπτει δίπλα στα αρχικά δεδομένα με την εντολή Κλείσιμο και φόρτωση – Κλείσιμο και φόρτωση σε… (Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε…) αυτί Αρχική (Σπίτι):
Ως αποτέλεσμα παίρνουμε:
Για μεγαλύτερη ομορφιά, μπορείτε να προσαρμόσετε την εμφάνιση των έξυπνων τραπεζιών που προκύπτουν στην καρτέλα Κατασκευαστής (Σχέδιο): ορίστε ένα στυλ μεμονωμένου χρώματος, απενεργοποιήστε τα κουμπιά φίλτρων, ενεργοποιήστε τα σύνολα κ.λπ. Επιπλέον, μπορείτε να επιλέξετε έναν πίνακα με ημερομηνίες και να ενεργοποιήσετε την επισήμανση αριθμών για αυτόν χρησιμοποιώντας μορφοποίηση υπό όρους στην καρτέλα Αρχική — Μορφοποίηση υπό όρους — Κλίμακες χρωμάτων (Αρχική — Μορφοποίηση υπό όρους — Κλίμακες χρώματος):
Και το καλύτερο είναι ότι στο μέλλον μπορείτε να επεξεργαστείτε με ασφάλεια τα παλιά ή να προσθέσετε νέα έργα στον αρχικό πίνακα και στη συνέχεια να ενημερώσετε τον σωστό πίνακα με ημερομηνίες με το δεξί κουμπί του ποντικιού – και το Power Query θα επαναλάβει όλες τις ενέργειες που έχουμε κάνει αυτόματα .
Voila!
- Γράφημα Gantt στο Excel χρησιμοποιώντας μορφοποίηση υπό όρους
- Ημερολόγιο ορόσημο του έργου
- Δημιουργία διπλότυπων σειρών με Power Query