Κατανόηση κυττάρων Excel έναντι λειτουργιών εύρους στο VBA

Κατανόηση κυττάρων Excel έναντι λειτουργιών εύρους στο VBA

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





Το πρόβλημα με τη χρήση των λειτουργιών Cells and Range στο VBA είναι ότι σε προχωρημένα επίπεδα, οι περισσότεροι άνθρωποι δυσκολεύονται να κατανοήσουν πώς λειτουργούν αυτές οι λειτουργίες. Η χρήση τους μπορεί να γίνει πολύ μπερδεμένη. Δείτε πώς μπορείτε να τα χρησιμοποιήσετε με τρόπους που πιθανότατα δεν φανταζόσασταν.



Η συνάρτηση κυττάρων

Οι λειτουργίες Cells and Range σας επιτρέπουν να το πείτε το σενάριο VBA σας πού ακριβώς στο φύλλο εργασίας σας θέλετε να λάβετε ή τοποθετήστε δεδομένα. Η κύρια διαφορά μεταξύ των δύο κελιών είναι αυτό που αναφέρουν.





Κύτταρα συνήθως αναφέρονται σε ένα κελί κάθε φορά, ενώ Εύρος αναφέρεται μια ομάδα κελιών ταυτόχρονα. Η μορφή αυτής της λειτουργίας είναι Κελιά (γραμμή, στήλη) Το

Αυτό αναφέρεται σε κάθε κελί σε ολόκληρο το φύλλο. Είναι το ένα παράδειγμα όπου η συνάρτηση Cells δεν αναφέρεται σε ένα μόνο κελί:



Worksheets('Sheet1').Cells

Αυτό αναφέρεται στο τρίτο κελί από τα αριστερά, στην επάνω σειρά. Κελί 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

Ο παραπάνω κώδικας αναφέρεται στο ίδιο εύρος με τη λειτουργία Range ('A1: E20'). Η αξία της χρήσης του είναι ότι θα σας επιτρέψει να γράψετε κώδικα που λειτουργεί δυναμικά με εύρη χρησιμοποιώντας βρόχους.

Τώρα που καταλαβαίνετε πώς να διαμορφώσετε τις λειτουργίες Cells and Range, ας δούμε πώς μπορείτε να κάνετε δημιουργική χρήση αυτών των λειτουργιών στον κώδικα VBA.

Επεξεργασία δεδομένων με λειτουργία κυττάρων

Η συνάρτηση Cells είναι πιο χρήσιμη όταν έχετε έναν πολύπλοκο τύπο που θέλετε να εκτελέσετε σε πολλαπλές περιοχές κελιών. Αυτά τα εύρη μπορούν επίσης να υπάρχουν σε πολλά φύλλα.

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

Ισως να έχεις Φύλλο 1 που παρακολουθεί τον αριθμό πωλήσεών τους και τον όγκο πωλήσεών τους.

Επί Φύλλο2 είναι όπου παρακολουθείτε τη βαθμολογία σχολίων τους για τις τελευταίες 30 ημέρες από τους πελάτες της εταιρείας σας.

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

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

Γιατί λοιπόν να μην ενεργοποιήσουμε τους υπολογισμούς για τη στήλη μπόνους στο ίδιο σενάριο εκείνη τη στιγμή;

Η λειτουργία των κυττάρων σε δράση

Εάν δεν έχετε γράψει ποτέ VBA στο Excel στο παρελθόν, θα πρέπει να ενεργοποιήσετε το στοιχείο μενού προγραμματιστή. Για να το κάνετε αυτό, μεταβείτε στο Αρχείο >> Επιλογές Το Κάντε κλικ στο Προσαρμογή κορδέλας Το Τέλος, επιλέξτε Developer από το αριστερό παράθυρο, Προσθήκη στο δεξιό παράθυρο και βεβαιωθείτε ότι το πλαίσιο ελέγχου είναι επιλεγμένο.

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

Μπορείτε να χρησιμοποιήσετε το Εισάγετε μενού για να εισαγάγετε ένα κουμπί εντολών ή απλά κάντε κλικ Προβολή κώδικα για να ξεκινήσει η κωδικοποίηση.

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

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

Αυτή θα είναι η έξοδος αυτού του σεναρίου.

τι μπορείς να κάνεις με ένα βατόμουρο πι

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

Η ομορφιά της λειτουργίας Cells είναι ότι μπορείτε να δημιουργήσετε μια αρκετά δημιουργική λογική για να αντλήσετε δεδομένα πολλά κύτταρα σε πολλά διαφορετικά φύλλα και εκτελέστε μερικά αρκετά περίπλοκοι υπολογισμοί με αυτούς.

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

Για να εξερευνήσετε όλα όσα μπορείτε να κάνετε περαιτέρω, ελέγξτε το Σελίδα Microsoft MSDN για το αντικείμενο Cells.

Μορφοποίηση κελιών με λειτουργία εύρους

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

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

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

Ας ρίξουμε μια ματιά στο πώς μπορείτε να το κάνετε αυτό με μια δήλωση ΑΝ Το

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

Όταν αυτό τρέξει, εάν το κελί είναι πάνω από τον στόχο της ομάδας, όλα τα κελιά στο εύρος θα συμπληρωθούν με πράσινο χρώμα.

Αυτό είναι μόνο ένα απλό παράδειγμα των πολλών ενεργειών που μπορείτε να εκτελέσετε σε ομάδες κελιών χρησιμοποιώντας τη συνάρτηση εύρους. Άλλα πράγματα που μπορείτε να κάνετε περιλαμβάνουν:

  • Εφαρμόστε ένα περίγραμμα γύρω από την ομάδα
  • Ελέγξτε την ορθογραφία του κειμένου μέσα σε μια σειρά κελιών
  • Διαγράψτε, αντιγράψτε ή κόψτε κελιά
  • Αναζήτηση μέσω εύρους με τη μέθοδο «Εύρεση»
  • Πολύ περισσότερο

Φροντίστε να διαβάσετε το Σελίδα Microsoft MSDN για το αντικείμενο Range να δει όλες τις δυνατότητες.

Πηγαίνετε το Excel στο επόμενο επίπεδο

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

Και αν μόλις ξεκινήσατε με το VBA στο Excel, μην ξεχνάτε ότι έχουμε ένα φανταστικό εισαγωγικός οδηγός για το Excel VBA και για σένα

Μερίδιο Μερίδιο Τιτίβισμα ΗΛΕΚΤΡΟΝΙΚΗ ΔΙΕΥΘΥΝΣΗ Canon εναντίον Nikon: Ποια μάρκα κάμερας είναι καλύτερη;

Η Canon και η Nikon είναι τα δύο μεγαλύτερα ονόματα στη βιομηχανία κάμερας. Ποια μάρκα προσφέρει όμως την καλύτερη γκάμα φωτογραφικών μηχανών και φακών;

Διαβάστε Επόμενο Σχετικά θέματα
  • Παραγωγικότητα
  • Προγραμματισμός
  • Προγραμματισμός Visual Basic
  • Microsoft Excel
Σχετικά με τον Συγγραφέα Ράιαν Ντούμπε(942 δημοσιευμένα άρθρα)

Ο Ryan έχει πτυχίο Ηλεκτρολόγων Μηχανικών. Εργάστηκε 13 χρόνια στη μηχανική αυτοματισμού, 5 χρόνια στην πληροφορική και τώρα είναι Μηχανικός Εφαρμογών. Πρώην Διευθύνων Σύμβουλος του MakeUseOf, μίλησε σε εθνικά συνέδρια για την απεικόνιση δεδομένων και συμμετείχε στην εθνική τηλεόραση και ραδιόφωνο.

Περισσότερα από τον Ryan Dube

Εγγραφείτε στο newsletter μας

Εγγραφείτε στο ενημερωτικό μας δελτίο για τεχνικές συμβουλές, κριτικές, δωρεάν ebooks και αποκλειστικές προσφορές!

Κάντε κλικ εδώ για εγγραφή