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

excel macros hands tutorial

Αυτός ο πρακτικός οδηγός Excel Macros εξηγεί τι είναι μια μακροεντολή, πώς να δημιουργήσετε και να χρησιμοποιήσετε μακροεντολές VBA με πολλά παραδείγματα:

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

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

=> Ρίξτε μια ματιά στον οδηγό για αρχάριους VBA εδώ

Τι θα μάθετε:

• Τι είναι οι μακροεντολές του Excel
  • Πώς να ενεργοποιήσετε τις μακροεντολές στο Excel
  • Ενεργοποίηση της καρτέλας προγραμματιστή
  • Επιλογές καρτέλας προγραμματιστή
  • Πώς να εγγράψετε μια μακροεντολή
  • Αποθήκευση βιβλίου εργασίας του Excel με μακροεντολή
  • Εκτέλεση μακροεντολής
  • Αναφορά κυττάρων
  • Εγγραφή μακροεντολής Excel χρησιμοποιώντας σχετική αναφορά
  • Συχνές Ερωτήσεις
• συμπέρασμα
  • Συνιστώμενη ανάγνωση

Τι είναι οι μακροεντολές του Excel

Η μακροεντολή είναι ένα σύνολο ενεργειών που μπορείτε να εκτελέσετε για να εκτελέσετε την επιθυμητή εργασία.

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

Πώς να ενεργοποιήσετε τις μακροεντολές στο Excel

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

Ακολουθήστε τα παρακάτω βήματα για να εμφανίσετε την καρτέλα Προγραμματιστής. Αυτό λειτουργεί σε όλες τις εκδόσεις του Excel για Windows (Excel 2007.2010, 2013, 2016, 2019).

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

Ενεργοποίηση της καρτέλας προγραμματιστή

# 1) Κάντε κλικ στο Αρχείο αυτί

#δύο) Κάντε κλικ Επιλογές

# 3) Κάντε κλικ στο Προσαρμογή κορδέλας.

# 4) Στην περιοχή Customize Ribbon ενεργοποιήστε Προγραμματιστής.

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

Επιλογές καρτέλας προγραμματιστή

Παρακάτω αναφέρονται οι επιλογές που υπάρχουν στην καρτέλα Προγραμματιστής.

• Οπτική Βασική: Δίνει έναν επεξεργαστή για να γράψει ή να επεξεργαστεί κώδικα VBA. Μπορεί επίσης να ανοίξει χρησιμοποιώντας Alt + F11.
• Μακροεντολές: Δίνει τη λίστα με όλες τις ήδη καταγεγραμμένες μακροεντολές και χρησιμοποιείται επίσης για την εγγραφή μιας νέας. Το Alt + F8 θα ανοίξει απευθείας τη λίστα των μακροεντολών.
• Πρόσθετα: Επιτρέπει την εισαγωγή πρόσθετου και μπορεί επίσης να τα διαχειριστεί.
• Έλεγχοι: Μας βοηθά στη χρήση στοιχείων ελέγχου φόρμας και στοιχείων ελέγχου ActiveX. Προβολή και επεξεργασία των ιδιοτήτων ελέγχου. Ο τρόπος λειτουργίας ON / OFF ελέγχεται εδώ.
• XML: Μας βοηθά να εισάγουμε / εξάγουμε ένα αρχείο δεδομένων XML, να διαχειριζόμαστε πακέτα επέκτασης XML και επίσης να ανοίγουμε το παράθυρο εργασιών Πηγή XML.

Πώς να εγγράψετε μια μακροεντολή

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

Αλλά πριν από την αποστολή σας ζητείται να κάνετε κάποια μορφοποίηση όπως:

1. Εισαγάγετε τον Τίτλο για κάθε φύλλο που περιλαμβάνει το όνομα της ομάδας και τον αριθμό εβδομάδας, σημειώστε το με έντονη γραφή και κίτρινο φόντο.
2. Σχεδιάστε ένα περίγραμμα
3. Έντονες επικεφαλίδες στηλών.
4. Μετονομάστε το όνομα του φύλλου ως το όνομα της ομάδας.

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

Η εγγραφή μακροεντολής είναι αρκετά εύκολη. Μεταβείτε στην καρτέλα Προγραμματιστής και πατήστε Εγγραφή μακροεντολής.

Αυτό θα ανοίξει ένα παράθυρο στο οποίο πρέπει να εισαγάγετε.

# 1) Όνομα μακροεντολής: Το όνομα δεν πρέπει να έχει κενά μεταξύ λέξεων. Αυτό πρέπει να ξεκινήσει με ένα αλφάβητο ή υπογράμμιση.

# 2) Πλήκτρο συντόμευσης: Αυτό είναι χρήσιμο όταν εκτελείτε μια μακροεντολή. Εάν πατήσετε το πλήκτρο συντόμευσης, θα εκτελεστεί. Φροντίστε να δώσετε ένα κλειδί που δεν έχει ήδη ληφθεί, διαφορετικά η μακροεντολή θα το αντικαταστήσει.

Για παράδειγμα, εάν αναφέρετε το Ctrl + S ως συντόμευση, τότε κάθε φορά που πατάτε Ctrl + S, η μακροεντολή σας θα εκτελείται και έτσι θα αγνοηθεί η επιλογή αποθήκευσης αρχείου. Ως εκ τούτου, συνιστάται να προσθέσετε Shift, όπως Ctrl + Shift + D

# 3) Αποθηκεύστε τη μακροεντολή σε: Αυτό έχει 3 επιλογές όπως δίνεται παρακάτω.

• Αυτό το βιβλίο εργασίας: Όλες οι μακροεντολές που δημιουργήθηκαν θα είναι διαθέσιμες μόνο στο τρέχον βιβλίο εργασίας. Εάν ανοίξετε ένα νέο excel, η μακροεντολή που δημιουργήθηκε νωρίτερα δεν θα είναι διαθέσιμη και ως εκ τούτου δεν μπορεί να χρησιμοποιηθεί.
• Προσωπικό βιβλίο εργασίας μακροεντολών: Εάν το επιλέξετε, τότε η δημιουργημένη μακροεντολή θα αποθηκευτεί και θα εμφανιστεί όταν ανοίγετε ένα νέο φύλλο excel.
• Νέο βιβλίο εργασίας: Αυτή η επιλογή θα ανοίξει ένα νέο βιβλίο εργασίας και θα καταγραφούν οι ενέργειες που εκτελούνται σε αυτό το βιβλίο εργασίας.

# 4) Περιγραφή: Αυτό θα περιγράψει τον σκοπό της μακροεντολής. Συνιστάται να δώσετε μια λεπτομερή περιγραφή, ώστε όποιος το χρησιμοποιεί να γνωρίζει τι ακριβώς χρησιμοποιείται.

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

Αποθήκευση βιβλίου εργασίας του Excel με μακροεντολή

Επιλογή μακροεντολής καταστήματος ως 'Αυτό το βιβλίο εργασίας': Σκεφτείτε ότι έχετε επιλέξει τη μακροεντολή Store ως 'Αυτό το βιβλίο εργασίας' κατά την εγγραφή. Μόλις τελειώσετε, προχωρήστε και αποθηκεύστε το αρχείο. Κατά την αποθήκευση πρέπει να επιλέξετε το βιβλίο εργασίας Excel με δυνατότητα μακροεντολής. Δεν χρειάζεται να αποθηκεύσετε ρητά τη μακροεντολή. Αποθηκεύεται αυτόματα.

Επιλογή μακροεντολής αποθήκευσης ως 'Προσωπικό βιβλίο εργασίας μακροεντολών': Τώρα εξετάστε το ενδεχόμενο να επιλέξετε τη μακροεντολή Store ως 'Προσωπικό βιβλίο εργασίας μακροεντολών' κατά την εγγραφή. Πρέπει να αποθηκεύσετε τη μακροεντολή ρητά. Εάν αποθηκεύσετε το αρχείο Excel και προσπαθήστε να κλείσετε το αρχείο. Στη συνέχεια, θα λάβετε ένα αναδυόμενο παράθυρο διαλόγου όπως φαίνεται παρακάτω.

Σημείωση: Εάν δεν το αποθηκεύσετε, τότε η μακροεντολή θα διαγραφεί.

Εκτέλεση μακροεντολής

Τώρα που έχουμε τελειώσει με την εγγραφή και την αποθήκευση του αρχείου, ας προσπαθήσουμε να το εκτελέσουμε και να επιτύχουμε τα επιθυμητά αποτελέσματα. Έχουμε προχωρήσει και καταγράψαμε μια μακροεντολή με όλα τα βήματα που απαιτούνται για να επιτευχθεί στο παράδειγμα του φύλλου χρόνου παρακολούθησης και το αποθηκεύσαμε ως 'Αυτό το βιβλίο εργασίας' με το πλήκτρο συντόμευσης ως Ctrl + Shift + B.

Έτσι, κάθε εβδομάδα όταν λαμβάνετε ένα νέο Excel από το εργαλείο λογισμικού, απλά πρέπει να ανοίξετε αυτό το αρχείο Excel και να πατήσετε το πλήκτρο συντόμευσης (Ctrl + Shift + B) και όλες οι αλλαγές θα ενσωματωθούν όπως αναμενόταν. Το προκύπτον Excel δίνεται παρακάτω.

Επισυνάφθηκε το βιβλίο Excel-Macro-Work

Σημείωση:

1. Εάν έχετε ξεχάσει το πλήκτρο συντόμευσης, μπορείτε να μεταβείτε στην επιλογή Προγραμματιστής -> Μακροεντολές, Επιλέξτε τη μακροεντολή και κάντε κλικ στις επιλογές.
2. Εάν η μακροεντολή που αποθηκεύεται ως προσωπικό κατάστημα δεν είναι ορατή στην καρτέλα μακροεντολών. Μεταβείτε στην Προβολή -> Απόκρυψη και αυτό θα εμφανίσει τη λίστα με όλες τις μακροεντολές.

Αναφορά κυττάρων

Υπάρχουν 2 τρόποι εγγραφής μιας μακροεντολής όπως φαίνεται παρακάτω.

1. Απόλυτη αναφορά κυττάρων
2. Σχετική αναφορά κελιού

Απόλυτη αναφορά κυττάρων: Οι απόλυτες αναφορές θα δείχνουν πάντα στο συγκεκριμένο κελί όπου καταγράφηκε. Για παράδειγμα: Εάν εγγράψετε ένα κείμενο σε κελί A10, τότε την επόμενη φορά που θα χρησιμοποιήσετε αυτήν τη μακροεντολή σε άλλο βιβλίο εργασίας, θα τοποθετήσει αυτό το κείμενο σε A10.

Εξετάστε το παράδειγμα φύλλου παρακολούθησης. Θέλουμε πάντα ο τίτλος να βρίσκεται στην πρώτη σειρά κάθε φύλλου. Δεν θέλουμε την αναφορά κελιού να αλλάζει όταν αντιγράφεται σε άλλα φύλλα ή βιβλία εργασίας. Σε αυτήν την περίπτωση, το Absolute Cell Referencing είναι βολικό.

Σχετική αναφορά κυττάρων: Ας υποθέσουμε ότι πρέπει να επαναλάβετε τα βήματα σε διάφορα σημεία στο φύλλο εργασίας. Οι σχετικές αναφορές είναι βολικές όποτε χρειάζεται να επαναλάβετε τον ίδιο υπολογισμό ή βήματα σε πολλές σειρές ή στήλες.

Παράδειγμα: Ας υποθέσουμε ότι έχετε ένα φύλλο Excel με τα πλήρη ονόματα, τους αριθμούς τηλεφώνου και τα DOB των 1000 υπαλλήλων. (Η μορφή είναι όπως φαίνεται παρακάτω)

Ο διευθυντής σας αναμένει:

το καλύτερο απομακρυσμένο spyware για κινητά τηλέφωνα

1. Ξεχωριστό Όνομα και Επώνυμο.
2. Προσθήκη παραδείγματος κωδικού χώρας (+91) στον αριθμό τηλεφώνου.
3. Εμφάνιση DOB με τη μορφή dd-mon-yy, Παράδειγμα: 10 Ιαν 87.

Δεδομένου ότι υπάρχουν 1000 εγγραφές, το να το κάνεις χειροκίνητα θα χρειαζόταν χρόνο. Έτσι αποφασίζετε να δημιουργήσετε μια μακροεντολή. Αλλά η απόλυτη αναφορά δεν θα λύσει το πρόβλημά σας καθώς θέλετε να λειτουργεί σε πολλές σειρές και στήλες. Σε αυτήν την περίπτωση, η σχετική αναφορά είναι βολική.

Εγγραφή μακροεντολής Excel χρησιμοποιώντας σχετική αναφορά

Για εγγραφή χρησιμοποιώντας σχετική αναφορά, επιλέξτε πρώτα το κελί που θέλετε να ξεκινήσει η εγγραφή.

Μεταβείτε στο Developer -> κάντε κλικ στο Use Relative Reference -> Record Macro . Καταγράψτε οτιδήποτε θέλετε και πατήστε σταματήστε την εγγραφή.

Για το παραπάνω παράδειγμα ακολουθήστε αυτά τα βήματα.

1. Πρώτον, πρέπει να εισαγάγουμε μια στήλη δίπλα στο Emp FullName και να αλλάξουμε την επικεφαλίδα της στήλης ως FirstName και LastName.
2. Επιλέγω B2 κελί-> Μετάβαση στον προγραμματιστή -> Χρήση σχετικής αναφοράς -> Εγγραφή μακροεντολής .
3. Χρησιμοποιώντας το Διαχωριστικό κειμένου ξεχωριστό όνομα και επώνυμο. Μόλις ολοκληρώθηκε η εγγραφή.
4. Ομοίως, δημιουργήστε 2 ακόμη μακροεντολές για αριθμό τηλεφώνου και DOB.
5. Αποθηκεύστε το αρχείο.
6. Για εκτέλεση, επιλέξτε όλο το πλήρες όνομα Emp δηλ. B3 έως το τελευταίο emp που είναι το B1001 και εκτελέστε το 1^αγΜακροεντολή.
7. Ακολουθήστε παρόμοια βήματα για τον αριθμό τηλεφώνου και το DOB. Το προκύπτον Excel εμφανίζεται παρακάτω.

Συνημμένο αρχείο για αναφορά

Συχνές Ερωτήσεις

Ε # 1) Τι είναι το παράδειγμα μακροεντολών στο Excel;

Απάντηση: Η μακροεντολή είναι ένα σύνολο ενεργειών που μπορείτε να εκτελέσετε για να εκτελέσετε την επιθυμητή εργασία.

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

Ε # 2) Πού βρίσκονται οι μακροεντολές στο Excel;

Απάντηση: Όλες οι εγγεγραμμένες μακροεντολές θα είναι διαθέσιμες στο Καρτέλα προγραμματιστή -> Μακροεντολές

Εάν δεν μπορείτε να βρείτε μια προσωπική μακροεντολή, μεταβείτε στη διεύθυνση Προβολή -> Απόκρυψη .

Q # 3) Ποιοι είναι οι τύποι αναφορών κυττάρων στο Excel;

Απάντηση:

• Απόλυτος: Οι απόλυτες αναφορές θα δείχνουν πάντα στο συγκεκριμένο κελί όπου καταγράφηκε. Για παράδειγμα, εάν εγγράψετε ένα κείμενο στο κελί D10 τότε κάθε φορά που χρησιμοποιείται η μακροεντολή δείχνει πάντα το D10.
• Συγγενής: Αυτά είναι βολικά όποτε χρειάζεται να επαναλάβετε τον ίδιο υπολογισμό ή βήματα σε πολλές σειρές ή στήλες.

Q # 4) Πώς μπορώ να αποθηκεύσω μια μακροεντολή στο Excel σε όλα τα βιβλία εργασίας;

Απάντηση: Κατά την εγγραφή μιας μακροεντολής επιλέξτε Προσωπικό βιβλίο εργασίας μακροεντολής κάτω από τη μακροεντολή αποθήκευσης, αυτό θα κάνει τη μακροεντολή σας να είναι διαθέσιμη για όλα τα βιβλία εργασίας. Εάν εξακολουθείτε να μην βλέπετε την επιλογή, μεταβείτε στο Προβολή -> Απόκρυψη .

συμπέρασμα

Σε αυτό το σεμινάριο, μάθαμε μακροεντολές Excel που μας βοηθούν στην αυτοματοποίηση των ρουτίνων εργασιών στο Excel.

Έχουμε δει τι είναι μια μακροεντολή; Τρόπος ενεργοποίησης της μακροεντολής για εμφάνιση στο Excel. Εξετάσαμε επίσης τον τρόπο καταγραφής μιας μακροεντολής χρησιμοποιώντας απόλυτες και σχετικές αναφορές κελιών με παραδείγματα.

=> Διαβάστε το The Easy VBA Training Series

Συνιστώμενη ανάγνωση

• Tutorial Excel VBA - Εισαγωγή στο VBA στο Excel
• Τύποι δεδομένων VBA - Τύποι αριθμητικών και μη αριθμητικών δεδομένων στο VBA
• 35+ Πιο κοινές ερωτήσεις και απαντήσεις για συνέντευξη στο Microsoft Excel
• Τρόπος αυτοματοποίησης επαναλαμβανόμενων εργασιών QA χρησιμοποιώντας μακροεντολές Excel (Παραδείγματα)
• Πώς να ανοίξετε το αρχείο XML σε Excel, Chrome και MS Word
• Πώς να διαβάσετε ή να γράψετε δεδομένα από το φύλλο Excel στο πρόγραμμα οδήγησης Web Selenium
• Κορυφαία 5 πράγματα που πρέπει να έχει ένας δοκιμαστής στο Excel (Και οι προοπτικές αλλαγής λογισμικού του ελεγκτή)
• Εργασία με αντικείμενα VBScript Excel