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

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

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

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

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

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

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

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

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

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

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

Για να δημιουργήσετε μια μακροεντολή, μεταβείτε στην προβολή & gt;Μακροεντολές & gt;Εγγραφή μακροεντολής.

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

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

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

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

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

• = SUM( B2: K2)
• = Μέση( B2: K2)
• = MIN( B2: K2)
• = MAX)

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

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

Αντίστοιχα:

• = SUM( L2: L21)
• = Μέση τιμή( B2: K21) * Πρέπει να υπολογιστεί σε όλες τις τιμέςδεδομένου ότι ο μέσος όρος των μέσων όρων της γραμμής δεν είναι απαραίτητα ίσος με τον μέσο όρο όλων των τιμών.
• = MIN( N2: N21)
• = MAX( O2: O21)
• = MEDIAN( B2: K21) * Υπολογίζεται σε όλα τα δεδομένα για τον ίδιο λόγο.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Κοιτάζοντας κάτω από την κουκούλα: Τι κάνει μια μακροεντολή

Όπως έχουμε αναφέρει μερικές φορές, μια μακροεντολή οδηγείται από τον κώδικα 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