Το Excel είναι ισχυρό. Εάν το χρησιμοποιείτε πολύ, πιθανότατα γνωρίζετε ήδη πολλά κόλπα χρησιμοποιώντας τύπους ή αυτομορφία, αλλά χρησιμοποιώντας τις λειτουργίες κυττάρων και εύρους στο VBA, μπορείτε να ενισχύσετε τα αναλυτικά στοιχεία του Excel σε ένα εντελώς νέο επίπεδο.
Το πρόβλημα με τη χρήση των λειτουργιών κυττάρων και εύρους στο VBA είναι ότι στα προχωρημένα επίπεδα, οι περισσότεροι άνθρωποι δυσκολεύονται να καταλάβουν πώς λειτουργούν αυτές οι λειτουργίες. Η χρήση τους μπορεί να γίνει πολύ συγκεχυμένη. Εδώ μπορείτε να τα χρησιμοποιήσετε με τρόπους που πιθανώς ποτέ δεν φανταστήκατε.
Η λειτουργία κυττάρων
Οι λειτουργίες κυττάρων και εύρους σας επιτρέπουν να αναφέρετε τη δέσμη ενεργειών VBA 4 λάθη που μπορείτε να αποφύγετε όταν προγραμματίζετε μακροεντολές Excel με VBA 4 λάθη που μπορείτε να αποφύγετε όταν προγραμματίζετε μακροεντολές Excel με VBA Απλός κώδικας και μακροεντολές είναι τα κλειδιά για τις υπερδυνάμεις του Microsoft Excel. Ακόμα και οι μη προγραμματιστές μπορούν εύκολα να προσθέσουν εντυπωσιακή λειτουργικότητα στα υπολογιστικά φύλλα τους με το Virtual Basics for Applications (VBA). Απλά αποφύγετε αυτά τα λάθη προγραμματισμού για αρχάριους! Διαβάστε πιο ακριβέστερα πού στο φύλλο εργασίας θέλετε να αποκτήσετε ή να τοποθετήσετε δεδομένα. Η κύρια διαφορά μεταξύ των δύο κυψελών είναι αυτό που αναφέρονται.
Τα κύτταρα συνήθως αναφέρονται σε ένα μόνο κύτταρο κάθε φορά, ενώ το εύρος αναφέρει μια ομάδα κυττάρων ταυτόχρονα. Η μορφή αυτής της λειτουργίας είναι τα Κύτταρα (σειρά, στήλη) .
Αυτό αναφέρει κάθε κελί σε ολόκληρο το φύλλο. Είναι το ένα παράδειγμα όπου η λειτουργία "Κύτταρα" δεν αναφέρεται σε ένα μόνο κελί:
Worksheets("Sheet1").Cells Αυτό αναφέρει το τρίτο κελί από τα αριστερά, της άνω σειράς. Cell C1:
Worksheets("Sheet1").Cells(3) Οι παρακάτω αναφορές κώδικα D15:
Worksheets("Sheet1").Cells(15, 4) Αν θελήσατε, μπορείτε επίσης να αναφερθείτε στο κελί D15 με "Κύτταρα (15, " D ")" - μπορείτε να χρησιμοποιήσετε το γράμμα της στήλης.
Υπάρχει μεγάλη ευελιξία να μπορείτε να αναφέρετε ένα κελί χρησιμοποιώντας έναν αριθμό για στήλη και κελί, ειδικά με δέσμες ενεργειών που μπορούν να βρουν πολύ γρήγορα μέσω ενός μεγάλου αριθμού κυττάρων (και να εκτελούν υπολογισμούς). Θα φτάσουμε σε αυτό με περισσότερες λεπτομέρειες παρακάτω.
Η λειτουργία εύρους
Με πολλούς τρόπους, η λειτουργία εύρους είναι πολύ πιο ισχυρή από τη χρήση κυττάρων, διότι σας επιτρέπει να αναφέρετε είτε ένα μόνο κελί είτε ένα συγκεκριμένο εύρος κυττάρων, ταυτόχρονα. Δεν θέλετε να χρησιμοποιήσετε μια λειτουργία εύρους, επειδή οι αναφορές για τα κελιά δεν είναι αριθμοί (εκτός αν ενσωματώσετε τη λειτουργία κυψελών μέσα σε αυτήν).
Η μορφή αυτής της λειτουργίας είναι Εύρος (Κυψέλη # 1, Κυψέλες # 2) . Κάθε κυψέλη μπορεί να οριστεί με αριθμό επιστολής.
Ας δούμε μερικά παραδείγματα.
Εδώ, η συνάρτηση εμβέλειας παραπέμπει στο κελί A5:
Worksheets("Sheet1").Range("A5") Εδώ, η συνάρτηση εμβέλειας αναφέρει όλα τα κελιά μεταξύ A1 και E20:
Worksheets("Sheet1").Range("A1:E20") Όπως αναφέρθηκε παραπάνω, δεν χρειάζεται να χρησιμοποιείτε αναθέσεις κυψελίδων αριθμητικών γραμμάτων. Θα μπορούσατε να χρησιμοποιήσετε δύο λειτουργίες κυψελών μέσα σε μια λειτουργία εύρους για να προσδιορίσετε μια περιοχή στο φύλλο, όπως αυτή:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Ο παραπάνω κωδικός αναφέρεται στο ίδιο εύρος με τη λειτουργία εύρους ("A1: E20"). Η αξία στη χρήση του είναι ότι θα σας επιτρέψει να γράψετε κώδικα που δουλεύει δυναμικά με σειρές χρησιμοποιώντας βρόχους Πώς Do-While Loops Εργασία σε Προγραμματισμό Υπολογιστών Πώς Do-While Loops Εργασία σε Προγραμματισμό Υπολογιστών Οι βρόχοι είναι ένας από τους πρώτους τύπους ελέγχου που " θα μάθουν στον προγραμματισμό. Πιθανότατα γνωρίζετε για το χρόνο και για τους βρόχους, αλλά τι κάνει ο βρόχος do-while; Διαβάστε περισσότερα .
Τώρα που καταλαβαίνετε πώς μπορείτε να μορφοποιήσετε τις λειτουργίες κυττάρων και εύρους, ας δούμε πώς μπορείτε να κάνετε δημιουργική χρήση αυτών των λειτουργιών στον κώδικα VBA.
Επεξεργασία δεδομένων με λειτουργία κυττάρων
Η λειτουργία "Κύτταρα" είναι πιο χρήσιμη όταν έχετε έναν πολύπλοκο τύπο που θέλετε να εκτελέσετε σε πολλαπλές περιοχές κυψελών. Αυτά τα εύρη μπορούν επίσης να υπάρχουν σε πολλαπλά φύλλα.
Ας πάρουμε ένα απλό παράδειγμα. Ας υποθέσουμε ότι διαχειρίζεστε μια ομάδα πωλήσεων 11 ατόμων και κάθε μήνα θέλετε να δείτε την απόδοσή τους.
Μπορεί να έχετε το Sheet1 που παρακολουθεί τον αριθμό των πωλήσεών τους και τον όγκο των πωλήσεών τους.
Στο Sheet2 εντοπίζεται η βαθμολογία των ανατροφοδοτών για τις τελευταίες 30 ημέρες από τους πελάτες της εταιρείας σας.
Αν θέλετε να υπολογίσετε το μπόνους στο πρώτο φύλλο χρησιμοποιώντας τιμές από τα δύο φύλλα, υπάρχουν πολλοί τρόποι για να το κάνετε αυτό. Θα μπορούσατε να γράψετε έναν τύπο στο πρώτο κελί που εκτελεί τον υπολογισμό χρησιμοποιώντας δεδομένα στα δύο φύλλα και να το σύρετε προς τα κάτω. Αυτό θα λειτουργήσει.
Μια εναλλακτική λύση είναι η δημιουργία δέσμης ενεργειών VBA που ενεργοποιείτε είτε να ανοίγετε κάθε φορά που ανοίγετε το φύλλο είτε να ενεργοποιείτε από ένα κουμπί εντολών στο φύλλο, έτσι ώστε να μπορείτε να ελέγξετε πότε υπολογίζει. Μπορεί να χρησιμοποιήσετε μια δέσμη ενεργειών VBA για να τραβήξετε όλα τα δεδομένα πωλήσεων από ένα εξωτερικό αρχείο ούτως ή άλλως.
Γιατί λοιπόν όχι μόνο να ενεργοποιήσετε τους υπολογισμούς για τη στήλη μπόνους στο ίδιο σενάριο εκείνη τη στιγμή;
Η λειτουργία των κυττάρων σε δράση
Αν δεν έχετε γράψει ποτέ το VBA στο Excel πριν, θα χρειαστεί να ενεργοποιήσετε το στοιχείο του μενού Developer. Για να το κάνετε αυτό, μεταβείτε στο Αρχείο > Επιλογές . Κάντε κλικ στην επιλογή Προσαρμογή κορδέλας . Τέλος, επιλέξτε Προγραμματιστής από το αριστερό παράθυρο, προσθέστε το στο δεξιό τμήμα του παραθύρου και βεβαιωθείτε ότι έχει επιλεγεί το πλαίσιο ελέγχου.
Τώρα, όταν κάνετε κλικ στο κουμπί OK και επιστρέψετε στο κύριο φύλλο, θα δείτε την επιλογή μενού προγραμματιστή.
Μπορείτε να χρησιμοποιήσετε το μενού Εισαγωγή για να εισαγάγετε ένα κουμπί εντολών ή απλά κάντε κλικ στην επιλογή Προβολή κωδικού για να ξεκινήσετε την κωδικοποίηση.
Σε αυτό το παράδειγμα θα κάνουμε τη δέσμη ενεργειών να εκτελείται κάθε φορά που ανοίγει το βιβλίο εργασίας. Για να το κάνετε αυτό, απλώς κάντε κλικ στην επιλογή Προβολή κωδικού από το μενού προγραμματιστή και επικολλήστε την ακόλουθη νέα λειτουργία στο παράθυρο κώδικα.
Private Sub Workbook_Open() End Sub Το παράθυρο του κώδικα θα μοιάζει με κάτι τέτοιο.
Τώρα είστε έτοιμοι να γράψετε τον κώδικα για να χειριστείτε τον υπολογισμό. Χρησιμοποιώντας έναν ενιαίο βρόχο, μπορείτε να περάσετε μέσα από όλους τους 11 υπαλλήλους και με τη λειτουργία Cells τραβήξτε τις τρεις μεταβλητές που απαιτούνται για τον υπολογισμό.
Θυμηθείτε ότι η λειτουργία "Κύτταρα" έχει τις σειρές και τη στήλη ως παραμέτρους για να προσδιορίσετε κάθε μεμονωμένο κελί Θα κάνουμε "x" τη σειρά, χρησιμοποιήστε έναν αριθμό για να ζητήσετε δεδομένα κάθε στήλης. Ο αριθμός των σειρών είναι ο αριθμός των εργαζομένων, οπότε αυτό θα είναι από το 1 έως το 11. Το αναγνωριστικό στήλης θα είναι 2 για τον αριθμό πωλήσεων, 3 για τον όγκο πωλήσεων και 2 για το φύλλο 2 για το σχόλιο ανάδρασης.
Ο τελικός υπολογισμός χρησιμοποιεί τα παρακάτω ποσοστά για να προσθέσει μέχρι και το 100 τοις εκατό της συνολικής βαθμολογίας μπόνους. Βασίζεται σε έναν ιδανικό όγκο πωλήσεων που είναι 50, ο όγκος των πωλήσεων ως $ 50.000 και ένα σχόλιο ανάδρασης των 10.
- (Αριθμός πωλήσεων / 50) x 0, 4
- (Όγκος πωλήσεων / 50.000) x 0.5
- (Δείκτης Ανατροφοδότησης / 10) x 0, 1
Αυτή η απλή προσέγγιση δίνει στους πωλητές ένα σταθμισμένο επίδομα. Για έναν αριθμό 50, όγκο $ 50.000 και ένα σκορ 10 - παίρνουν όλο το μέγιστο μπόνους για το μήνα. Ωστόσο, οτιδήποτε κάτω από το τέλειο με οποιοδήποτε παράγοντα μειώνει το μπόνους. Οτιδήποτε καλύτερο από το ιδανικό ενισχύει το μπόνους.
Τώρα ας δούμε πώς όλη αυτή η λογική μπορεί να τραβηχτεί σε ένα πολύ απλό, σύντομο σενάριο VBA:
Private Sub Workbook_Open() For x = 2 To 12 Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _ Next x End Sub Αυτό θα είναι το αποτέλεσμα αυτής της δέσμης ενεργειών.
Εάν θέλετε να έχετε στη στήλη Μπόνους το πραγματικό μπόνους δολάριου και όχι το ποσοστό, μπορείτε να το πολλαπλασιάσετε με το μέγιστο ποσό μπόνους. Καλύτερα, τοποθετήστε το ποσό σε ένα κελί σε άλλο φύλλο και ανατρέξτε στον κώδικα σας. Αυτό θα διευκόλυνε την αλλαγή της αξίας αργότερα χωρίς να χρειάζεται να επεξεργαστείτε τον κωδικό σας.
Η ομορφιά της λειτουργίας των κυττάρων είναι ότι μπορείτε να δημιουργήσετε κάποια αρκετά δημιουργική λογική για να τραβήξετε δεδομένα από πολλά κελιά σε πολλά φύλλα διαφορικών και να εκτελέσετε μερικούς πολύ περίπλοκους υπολογισμούς μαζί τους.
Μπορείτε να εκτελέσετε όλα τα είδη ενεργειών στα κελιά χρησιμοποιώντας τη λειτουργία Κύτταρα - πράγματα όπως η εκκαθάριση των κελιών, η αλλαγή της μορφοποίησης γραμματοσειρών και πολλά άλλα.
Για να διερευνήσετε όλα όσα μπορείτε να κάνετε περαιτέρω, ελέγξτε τη σελίδα Microsoft MSDN για το αντικείμενο Κύτταρα.
Διαμόρφωση κελιών με λειτουργία εύρους
Για το βρόχο μέσω πολλών κυττάρων ένα κάθε φορά, η λειτουργία κυττάρων είναι τέλεια. Αλλά αν θέλετε να εφαρμόσετε κάτι σε μια ολόκληρη σειρά κελιών ταυτόχρονα, η λειτουργία Range είναι πολύ πιο αποδοτική.
Μια περίπτωση χρήσης για αυτό μπορεί να είναι η μορφοποίηση μιας σειράς κελιών χρησιμοποιώντας δέσμη ενεργειών, εάν πληρούνται ορισμένες προϋποθέσεις.
Για παράδειγμα, ας πούμε ότι αν ο όγκος όλων των πωλήσεων σε όλους τους υπαλλήλους πωλήσεων ξεπεράσει τα $ 400.000 συνολικά, θέλετε να επισημάνετε όλα τα κελιά στη στήλη μπόνους με πράσινο χρώμα για να δηλώσετε ότι η ομάδα έχει κερδίσει επιπλέον μπόνους ομάδας.
Ας ρίξουμε μια ματιά στο πώς μπορείτε να το κάνετε αυτό με μια δήλωση IF Πώς να χρησιμοποιήσετε τις καταστάσεις IF στο Microsoft Excel Πώς να χρησιμοποιήσετε τις καταστάσεις IF στο Microsoft Excel Είτε είστε έμπειρος εμπειρογνώμονας είτε ένας αρχάριος λογιστικού φύλλου, θα θελήσετε να δείτε αυτό οδηγός για τις δηλώσεις IF στο Excel. Διαβάστε περισσότερα .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4 End If End Sub Όταν τρέξει αυτό, αν το κελί είναι πάνω από τον στόχο της ομάδας, όλα τα κελιά της περιοχής θα γεμίσουν πράσινα.
Αυτό είναι ένα απλό παράδειγμα των πολλών ενεργειών που μπορείτε να εκτελέσετε σε ομάδες κελιών χρησιμοποιώντας τη λειτουργία εύρους. Άλλα πράγματα που μπορείτε να κάνετε περιλαμβάνουν:
- Εφαρμόστε ένα περίγραμμα γύρω από την ομάδα
- Ελέγξτε την ορθογραφία του κειμένου μέσα σε μια περιοχή κελιών
- Διαγράψτε, αντιγράψτε ή κόψτε κελιά
- Αναζήτηση μέσω εύρους με τη μέθοδο "Εύρεση"
- Πολύ περισσότερο
Βεβαιωθείτε ότι έχετε διαβάσει τη σελίδα MSDN της Microsoft για το αντικείμενο σειράς για να δείτε όλες τις δυνατότητες.
Πάρτε το Excel στο επόμενο επίπεδο
Τώρα που καταλαβαίνετε τις διαφορές μεταξύ των λειτουργιών κυττάρων και εύρους, ήρθε η ώρα να πάρετε το σενάριό σας VBA στο επόμενο επίπεδο. Το άρθρο του Dann σχετικά με τη χρήση των λειτουργιών "Αρίθμηση και προσθήκη λειτουργιών" στο Excel θα σας επιτρέψει να δημιουργήσετε ακόμα πιο προηγμένα σενάρια που μπορούν να συσσωρεύουν πολύ γρήγορα τιμές σε όλα τα σύνολα δεδομένων σας.
Και αν είστε απλά να ξεκινήσετε με το VBA στο Excel, μην ξεχνάμε έχουμε ένα φανταστικό εισαγωγικό οδηγό για το Excel VBA Το Excel VBA Προγραμματισμός Tutorial για αρχάριους Το Excel VBA Προγραμματισμός Tutorial για αρχάριους VBA είναι ένα εργαλείο ηλεκτρικού ρεύματος του Microsoft Office. Μπορείτε να το χρησιμοποιήσετε για να αυτοματοποιήσετε τις εργασίες με μακροεντολές, να ενεργοποιήσετε ενεργοποιητές και πολλά άλλα. Θα σας παρουσιάσουμε τον Visual βασικό προγραμματισμό του Excel με ένα απλό έργο. Διαβάστε περισσότερα για σας επίσης.