Μάθετε πώς να χρησιμοποιείτε μακροεντολές Excel για την αυτοματοποίηση κουραστικών εργασιών

Μία από τις ισχυρότερες, αλλά σπάνια χρησιμοποιούμενες λειτουργίες του Excel είναι η δυνατότητα πολύ εύκολης δημιουργίας αυτοματοποιημένων εργασιών και προσαρμοσμένης λογικής μέσα στις μακροεντολές. Οι μακροεντολές παρέχουν έναν ιδανικό τρόπο για να εξοικονομήσετε χρόνο σε προβλέψιμες επαναλαμβανόμενες εργασίες καθώς και να τυποποιήσετε τις μορφές εγγράφων - πολλές φορές χωρίς να χρειάζεται να γράφετε μία γραμμή κώδικα.

Εάν είστε περίεργοι ποιες είναι οι μακροεντολές ή πώς να τις δημιουργήσετε, κανένα πρόβλημα - θα σας οδηγήσουμε σε όλη τη διαδικασία.

Σημείωση: η ίδια διαδικασία θα πρέπει να δουλεύει στις περισσότερες εκδόσεις του Microsoft Office. Τα στιγμιότυπα οθόνης μπορεί να φαίνονται λίγο διαφορετικά.

Τι είναι μια μακροεντολή?

Μια μακροεντολή του Microsoft Office (καθώς αυτή η λειτουργικότητα ισχύει σε πολλές από τις εφαρμογές του MS Office) είναι απλά κώδικας Visual Basic for Applications (VBA) αποθηκευμένος μέσα σε ένα έγγραφο. Για μια παρόμοια αναλογία, σκεφτείτε ένα έγγραφο ως HTML και μια μακροεντολή ως Javascript. Με τον ίδιο τρόπο που ο Javascript μπορεί να χειριστεί HTML σε μια ιστοσελίδα, μια μακροεντολή μπορεί να χειριστεί ένα έγγραφο.

Οι μακροεντολές είναι απίστευτα ισχυρές και μπορούν να κάνουν ό, τι μπορεί να φανταστεί η φαντασία σας. Ως (πολύ) σύντομη λίστα λειτουργιών που μπορείτε να κάνετε με μια μακροεντολή:

• Εφαρμογή στυλ και μορφοποίηση.
• Χειριστείτε δεδομένα και κείμενο.
• Επικοινωνήστε με πηγές δεδομένων (βάση δεδομένων, αρχεία κειμένου, κ.λπ.).
• Δημιουργήστε εντελώς νέα έγγραφα.
• Οποιοσδήποτε συνδυασμός, με οποιαδήποτε σειρά, οποιουδήποτε από τα παραπάνω.

Δημιουργία μακροεντολής: Μια επεξήγηση με το παράδειγμα

Ξεκινάμε με το αρχείο CSV ποικιλίας κήπου. Τίποτα ιδιαίτερο εδώ, απλά ένα σύνολο 10 × 20 αριθμών μεταξύ 0 και 100 με μια σειρά κεφαλίδων και μια στήλη. Στόχος μας είναι να δημιουργήσουμε ένα καλά μορφοποιημένο, εμφανές φύλλο δεδομένων το οποίο να περιλαμβάνει συνοπτικά σύνολα για κάθε σειρά.

Όπως έχουμε δηλώσει παραπάνω, μια μακροεντολή είναι ο κώδικας VBA, αλλά ένα από τα ωραία πράγματα για το Excel είναι ότι μπορείτε να τα δημιουργήσετε / καταγράψετε με μηδενική κωδικοποίηση που απαιτείται - όπως θα κάνουμε εδώ.

Για να δημιουργήσετε μια μακροεντολή, μεταβείτε στην Προβολή> Μακροεντολές> Καταγραφή μακροεντολών.

Εκχωρήστε στη μακροεντολή ένα όνομα (χωρίς κενά) και κάντε κλικ στο κουμπί OK.

Μόλις γίνει αυτό, όλα των ενεργειών σας καταγράφονται - κάθε αλλαγή κυψέλης, δράση κύλισης, αλλαγή μεγέθους παραθύρου, το ονομάζετε.

Υπάρχουν μερικές θέσεις που υποδεικνύουν ότι το Excel είναι λειτουργία εγγραφής. Το ένα είναι προβάλλοντας το μενού Macro και επισημαίνοντας ότι η Stop Recording έχει αντικαταστήσει την επιλογή Record Macro.

Το άλλο είναι στην κάτω δεξιά γωνία. Το εικονίδιο "stop" υποδεικνύει ότι βρίσκεται σε λειτουργία μακροεντολών και πατώντας εδώ θα σταματήσει η εγγραφή (ομοίως, όταν δεν βρίσκεται σε κατάσταση εγγραφής, αυτό το εικονίδιο θα είναι το κουμπί Εγγραφή μακροεντολής, το οποίο μπορείτε να χρησιμοποιήσετε αντί να μεταβείτε στο μενού Macros).

Τώρα που καταγράφουμε τη μακροεντολή μας, ας εφαρμόσουμε τους συνοπτικούς μας υπολογισμούς. Πρώτα προσθέστε τις κεφαλίδες.

Στη συνέχεια, εφαρμόστε τους κατάλληλους τύπους (αντίστοιχα):

• = SUM (Β2: Κ2)
• = ΜΕΣΟ (Β2: Κ2)
• = ΜΙΝ (Β2: Κ2)
• = ΜΑΧ (Β2: Κ2)
• = MEDIAN (Β2: Κ2)

Τώρα, επισημάνετε όλα τα κυκλώματα υπολογισμού και σύρετε το μήκος όλων των γραμμών δεδομένων σας για να εφαρμόσετε τους υπολογισμούς σε κάθε σειρά.

Μόλις γίνει αυτό, κάθε σειρά θα πρέπει να εμφανίζει τις αντίστοιχες περιλήψεις.

Τώρα, θέλουμε να έχουμε τα συνοπτικά δεδομένα για ολόκληρο το φύλλο, γι 'αυτό εφαρμόζουμε μερικούς άλλους υπολογισμούς:

Αντίστοιχα:

• = SUM (L2: L21)
• = ΜΕΣΟ (Β2: Κ21) *Αυτό πρέπει να υπολογιστεί για όλα τα δεδομένα, επειδή ο μέσος όρος των μέσων των γραμμών δεν είναι απαραίτητα ίσος με τον μέσο όρο όλων των τιμών.
• = ΜΙΝ (Ν2: Ν21)
• = ΜΑΧ (Ο2: Ο21)
• = MEDIAN (Β2: Κ21) * Υπολογίστηκε σε όλα τα δεδομένα για τον ίδιο λόγο όπως παραπάνω.

Τώρα που οι υπολογισμοί γίνονται, θα εφαρμόσουμε το στυλ και τη μορφοποίηση. Αρχικά εφαρμόστε μορφοποίηση γενικών αριθμών σε όλα τα κελιά κάνοντας μια Επιλογή Όλων (είτε Ctrl + A είτε κλικ στο κελί μεταξύ των κεφαλίδων γραμμής και στηλών) και επιλέξτε το εικονίδιο "Στυλ Κόλασης" στο μενού "Αρχική".

Στη συνέχεια, εφαρμόστε κάποια οπτική μορφοποίηση τόσο στις κεφαλίδες της σειράς όσο και στις στήλες:

• Τολμηρός.
• Κεντραρισμένο.
• Χρώμα γεμίσματος φόντου.

Και τέλος, εφαρμόστε κάποιο στυλ στα σύνολα.

Όταν όλα τελειώσουν, αυτό φαίνεται στο φύλλο δεδομένων μας:

Δεδομένου ότι είμαστε ικανοποιημένοι με τα αποτελέσματα, σταματήστε την εγγραφή της μακροεντολής.

Συγχαρητήρια - μόλις δημιουργήσατε μια μακροεντολή του Excel.

Για να χρησιμοποιήσουμε τη νέα μας μακροεντολή, πρέπει να αποθηκεύσουμε το βιβλίο εργασίας του Excel σε μορφή αρχείου με δυνατότητα μακροεντολών. Ωστόσο, προτού το κάνουμε αυτό, πρέπει πρώτα να καθαρίσουμε όλα τα υπάρχοντα δεδομένα ώστε να μην είναι ενσωματωμένα στο πρότυπο μας (η ιδέα είναι κάθε φορά που χρησιμοποιούμε αυτό το πρότυπο, θα εισάγουμε τα πιο ενημερωμένα δεδομένα).

Για να το κάνετε αυτό, επιλέξτε όλα τα κελιά και διαγράψτε τα.

Με τα δεδομένα που έχουν πλέον εκκαθαριστεί (αλλά οι μακροεντολές που εξακολουθούν να περιλαμβάνονται στο αρχείο Excel), θέλουμε να αποθηκεύσουμε το αρχείο ως αρχείο πρότυπου μακροεντολής (XLTM). Είναι σημαντικό να σημειώσετε ότι αν αποθηκεύσετε αυτό το αρχείο ως πρότυπο πρότυπο (XLTX), τότε θα εμφανίζονται οι μακροεντολές δεν να είναι σε θέση να τρέξει από αυτό. Εναλλακτικά, μπορείτε να αποθηκεύσετε το αρχείο ως αρχείο πρότυπο κληρονομιάς (XLT), το οποίο θα επιτρέψει την εκτέλεση μακροεντολών.

Μόλις αποθηκεύσετε το αρχείο ως πρότυπο, προχωρήστε και κλείστε το Excel.

Χρησιμοποιώντας μια μακροεντολή Excel

Πριν από την κάλυψη του τρόπου με τον οποίο μπορούμε να εφαρμόσουμε αυτή τη νέα μακροεντολή, είναι σημαντικό να καλύψουμε μερικά σημεία σχετικά με τις μακροεντολές γενικά:

• Οι μακροεντολές μπορεί να είναι κακόβουλη.
• Δείτε το παραπάνω σημείο.

Ο κώδικας VBA είναι στην πραγματικότητα αρκετά ισχυρός και μπορεί να χειριστεί αρχεία εκτός του πεδίου εφαρμογής του τρέχοντος εγγράφου. Για παράδειγμα, μια μακροεντολή μπορεί να τροποποιήσει ή να διαγράψει τυχαία αρχεία στο φάκελο "Τα έγγραφά μου". Ως εκ τούτου, είναι σημαντικό να βεβαιωθείτε ότι είστε μόνο μακροεντολές εκτέλεσης από αξιόπιστες πηγές.

Για να χρησιμοποιήσετε τη μακροεντολή μορφοποίησης δεδομένων, ανοίξτε το αρχείο προτύπου Excel που δημιουργήθηκε παραπάνω. Όταν το κάνετε αυτό, αν έχετε ενεργοποιήσει τις τυπικές ρυθμίσεις ασφάλειας, θα δείτε μια προειδοποίηση στην κορυφή του βιβλίου εργασίας, η οποία αναφέρει ότι οι μακροεντολές είναι απενεργοποιημένες. Επειδή έχουμε εμπιστοσύνη στη μακροεντολή που δημιουργήσαμε, κάντε κλικ στο κουμπί "Ενεργοποίηση περιεχομένου".

Στη συνέχεια, πρόκειται να εισαγάγουμε το πιο πρόσφατο σύνολο δεδομένων από ένα αρχείο CSV (αυτή είναι η πηγή του φύλλου εργασίας που χρησιμοποιήσαμε για τη δημιουργία της μακροεντολής μας).

Για να ολοκληρώσετε την εισαγωγή του αρχείου CSV, ίσως χρειαστεί να ορίσετε μερικές επιλογές, ώστε το Excel να το ερμηνεύσει σωστά (π.χ. οριοθέτης, κεφαλίδες κ.λπ.).

Μόλις εισαχθούν τα δεδομένα μας, απλά μεταβείτε στο μενού Macros (στην καρτέλα Προβολή) και επιλέξτε Προβολή μακροεντολών.

Στο προκύπτον παράθυρο διαλόγου, βλέπουμε τη μακροεντολή "FormatData" που καταγράψαμε παραπάνω. Επιλέξτε το και κάντε κλικ στην επιλογή Εκτέλεση.

Μόλις τρέξετε, μπορείτε να δείτε τον κέρσορα να μεταβείτε για λίγα λεπτά, αλλά όπως θα δείτε τα δεδομένα που χειρίζονται ακριβώς όπως το καταγράψαμε. Όταν όλα λέγονται και γίνονται, θα πρέπει να μοιάζουν με το πρωτότυπο μας - εκτός από διαφορετικά δεδομένα.

Κοιτάζοντας κάτω από το κουκούλα: Τι κάνει μια Macro εργασίας

Όπως έχουμε αναφέρει μερικές φορές, μια μακροεντολή οδηγείται από κώδικα Visual Basic for Applications (VBA). Όταν καταγράφετε μια μακροεντολή, το Excel μεταφράζει στην πραγματικότητα όλα όσα κάνετε στις αντίστοιχες οδηγίες του VBA. Για να το θέσετε απλά - δεν χρειάζεται να γράφετε κανένα κωδικό επειδή το Excel γράφει τον κώδικα για εσάς.

Για να δείτε τον κώδικα που εκτελεί τη μακροεντολή μας, από το διάλογο μακροεντολών κάντε κλικ στο κουμπί Επεξεργασία.

Το παράθυρο που ανοίγει εμφανίζει τον πηγαίο κώδικα που καταγράφηκε από τις ενέργειές μας κατά τη δημιουργία της μακροεντολής. Φυσικά, μπορείτε να επεξεργαστείτε αυτόν τον κώδικα ή ακόμα και να δημιουργήσετε νέες μακροεντολές εξ ολοκλήρου εντός του παραθύρου κώδικα. Ενώ η ενέργεια εγγραφής που χρησιμοποιείται σε αυτό το άρθρο πιθανόν να ταιριάζει με τις περισσότερες ανάγκες, οι πιο προσαρμοσμένες ενέργειες ή οι ενέργειες υπό όρους απαιτούν την επεξεργασία του πηγαίου κώδικα.

Λαμβάνοντας το παράδειγμά μας ένα βήμα μακρύτερα ...

Υπόθετα, υποθέστε ότι το αρχείο δεδομένων προέλευσης, data.csv, παράγεται από μια αυτοματοποιημένη διαδικασία που αποθηκεύει πάντα το αρχείο στην ίδια θέση (π.χ. C: \ Data \ data.csv είναι πάντα τα πιο πρόσφατα δεδομένα). Η διαδικασία ανοίγματος αυτού του αρχείου και εισαγωγής του μπορεί εύκολα να μετατραπεί σε μια μακροεντολή:

1. Ανοίξτε το αρχείο προτύπου Excel που περιέχει τη μακροεντολή "FormatData".
2. Καταγράψτε μια νέα μακροεντολή που ονομάζεται "LoadData".
3. Με τη μακροεντολή, εισάγετε το αρχείο δεδομένων όπως θα κάνατε κανονικά.
4. Μόλις εισαχθούν τα δεδομένα, διακόψτε την εγγραφή της μακροεντολής.
5. Διαγράψτε όλα τα δεδομένα κελιών (επιλέξτε όλα και, στη συνέχεια, διαγράψτε).
6. Αποθηκεύστε το ενημερωμένο πρότυπο (θυμηθείτε να χρησιμοποιήσετε μια μορφή πρότυπου με δυνατότητα μακροεντολών).

Μόλις γίνει αυτό, κάθε φορά που ανοίγεται το πρότυπο, θα υπάρχουν δύο μακροεντολές - μία που φορτώνει τα δεδομένα μας και η άλλη που τα μορφοποιεί.

Εάν θέλετε πραγματικά να βγάλετε τα χέρια σας με λίγο κώδικα, θα μπορούσατε εύκολα να συνδυάσετε αυτές τις ενέργειες σε μια ενιαία μακροεντολή αντιγράφοντας τον κώδικα που δημιουργήθηκε από το "LoadData" και εισάγοντάς τον στην αρχή του κώδικα από το "FormatData".

Κάντε λήψη αυτού του προτύπου

Για τη διευκόλυνσή σας, έχουμε συμπεριλάβει τόσο το πρότυπο Excel που παράγεται σε αυτό το άρθρο, όσο και ένα δείγμα αρχείου δεδομένων για να μπορείτε να παίξετε μαζί σας.

Κατεβάστε το Excel Macro Template από το How-To Geek