Πώς να μετρήσετε χρωματιστά κελιά στο Excel (Οδηγός βήμα προς βήμα + VIDEO)

Παρακολουθήστε βίντεο - Πώς να μετρήσετε τα χρωματιστά κελιά στο Excel

Δεν θα ήταν υπέροχο αν υπήρχε μια λειτουργία που θα μπορούσε να μετρήσει έγχρωμα κελιά στο Excel;

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

ΑΛΛΑ…

Μπορεί εύκολα να γίνει.

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

Σε αυτό το σεμινάριο, θα σας δείξω τρεις τρόπους για να μετρήσετε έγχρωμα κελιά στο Excel (με και χωρίς VBA):

1. Χρήση φίλτρου και συνάρτησης SUBTOTAL
2. Χρήση της συνάρτησης GET.CELL
3. Χρήση προσαρμοσμένης συνάρτησης που δημιουργήθηκε με χρήση VBA

#1 Μετρήστε χρωματιστά κελιά χρησιμοποιώντας φίλτρο και SUBTOTAL

Για να μετρήσετε έγχρωμα κελιά στο Excel, πρέπει να χρησιμοποιήσετε τα ακόλουθα δύο βήματα:

• Φιλτράρετε χρωματιστά κελιά
• Χρησιμοποιήστε τη συνάρτηση SUBTOTAL για να μετρήσετε τα χρωματιστά κελιά που είναι ορατά (μετά το φιλτράρισμα).

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:

Υπάρχουν δύο χρώματα φόντου που χρησιμοποιούνται σε αυτό το σύνολο δεδομένων (πράσινο και πορτοκαλί).

Ακολουθούν τα βήματα καταμέτρησης χρωματισμένων κελιών στο Excel:

1. Σε οποιοδήποτε κελί κάτω από το σύνολο δεδομένων, χρησιμοποιήστε τον ακόλουθο τύπο: = SUBTOTAL (102, E1: E20)
   
2. Επιλέξτε τις κεφαλίδες.
3. Μεταβείτε στα Δεδομένα -> Ταξινόμηση και φίλτρο -> Φίλτρο. Αυτό θα εφαρμόσει ένα φίλτρο σε όλες τις κεφαλίδες.
4. Κάντε κλικ σε οποιοδήποτε από τα αναπτυσσόμενα μενού φίλτρου.
5. Μεταβείτε στο «Φίλτρο ανά χρώμα» και επιλέξτε το χρώμα. Στο παραπάνω σύνολο δεδομένων, επειδή χρησιμοποιούνται δύο χρώματα για την επισήμανση των κελιών, το φίλτρο εμφανίζει δύο χρώματα για να φιλτράρει αυτά τα κελιά.

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

Πως λειτουργεί αυτό?

Η συνάρτηση SUBTOTAL χρησιμοποιεί το 102 ως πρώτο όρισμα, το οποίο χρησιμοποιείται για την καταμέτρηση ορατών κελιών (οι κρυμμένες σειρές δεν υπολογίζονται) στο καθορισμένο εύρος.

Εάν τα δεδομένα αν δεν φιλτραριστούν επιστρέφουν 19, αλλά αν φιλτραριστούν, τότε επιστρέφει μόνο τον αριθμό των ορατών κελιών.

Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου

#2 Μετρήστε χρωματιστά κελιά χρησιμοποιώντας τη συνάρτηση GET.CELL

Το GET.CELL είναι μια λειτουργία Macro4 που έχει διατηρηθεί για λόγους συμβατότητας.

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

Ωστόσο, λειτουργεί σε εύρη με όνομα Excel.

Δείτε επίσης: Μάθετε περισσότερα για τη λειτουργία GET.CELL.

Ακολουθούν τα τρία βήματα για να χρησιμοποιήσετε το GET.CELL για να μετρήσετε χρωματιστά κελιά στο Excel:

• Δημιουργήστε μια ονομαστική περιοχή χρησιμοποιώντας τη συνάρτηση GET.CELL
• Χρησιμοποιήστε την περιοχή με όνομα για να λάβετε τον κωδικό χρώματος σε μια στήλη
• Χρήση του αριθμού χρώματος για την καταμέτρηση του αριθμού των χρωματισμένων κελιών (ανά χρώμα)

Ας βουτήξουμε βαθιά και θα δούμε τι θα κάνουμε σε καθένα από τα τρία βήματα που αναφέρθηκαν.

Δημιουργία ονόματος εύρους

• Μεταβείτε στους Τύπους -> Ορισμός ονόματος.
• Στο παράθυρο διαλόγου Νέο όνομα, πληκτρολογήστε:
  • Όνομα: GetColor
  • Πεδίο εφαρμογής: Τετράδιο εργασίας
  • Αναφέρεται στο: = GET.CELL (38, Sheet1! $ A2)
    Στον παραπάνω τύπο, έχω χρησιμοποιήσει Φύλλο1! $ A2 ως δεύτερο επιχείρημα. Πρέπει να χρησιμοποιήσετε την αναφορά της στήλης όπου έχετε τα κελιά με το χρώμα φόντου.

Λήψη του κωδικού χρώματος για κάθε κελί

Στο κελί δίπλα στα δεδομένα, χρησιμοποιήστε τον τύπο = GetColor

Αυτός ο τύπος θα επέστρεφε 0 αν ΔΕΝ υπάρχει χρώμα φόντου σε ένα κελί και θα επέστρεφε έναν συγκεκριμένο αριθμό αν υπάρχει χρώμα φόντου.

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

Μετρήστε χρωματιστά κελιά χρησιμοποιώντας τον κωδικό χρώματος

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

Για να λάβετε τον αριθμό ενός συγκεκριμένου χρώματος:

• Κάπου κάτω από το σύνολο δεδομένων, δώστε το ίδιο χρώμα φόντου σε ένα κελί που θέλετε να μετρήσετε. Βεβαιωθείτε ότι το κάνετε αυτό στην ίδια στήλη που χρησιμοποιήσατε για τη δημιουργία του ονόματος εύρους. Για παράδειγμα, χρησιμοποίησα τη στήλη Α και επομένως θα χρησιμοποιήσω τα κελιά στη στήλη "Α" μόνο.
• Στο παρακείμενο κελί, χρησιμοποιήστε τον ακόλουθο τύπο:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Αυτός ο τύπος θα σας δώσει τον αριθμό όλων των κελιών με το καθορισμένο χρώμα φόντου.

Πώς λειτουργεί?

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

Η συνάρτηση COUNTIF χρησιμοποιεί το εύρος ($ F $ 2: $ F $ 18) που περιέχει τους κωδικούς αριθμούς χρωμάτων όλων των κελιών και επιστρέφει τον αριθμό με βάση τον αριθμό κριτηρίων.

Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου

#3 Καταμέτρηση χρωματισμένων με χρήση VBA (με δημιουργία προσαρμοσμένης συνάρτησης)

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

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

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

Εδώ είναι ο κωδικός:

«Κωδικός που δημιουργήθηκε από το Sumit Bansal από τη διεύθυνση https://trumpexcel.com Λειτουργία GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Every rCell rCell.Interior.ColorIndex = CountColorValue Τότε TotalCount = TotalCount + 1 Τέλος αν Επόμενο rCell GetColorCount = TotalCount End function

Για να δημιουργήσετε αυτήν την προσαρμοσμένη συνάρτηση:

• Με το βιβλίο εργασίας σας ενεργό, πατήστε Alt + F11 (ή κάντε δεξί κλικ στην καρτέλα φύλλου εργασίας και επιλέξτε Προβολή κώδικα). Αυτό θα ανοίξει το VB Editor.
• Στο αριστερό παράθυρο, κάτω από το βιβλίο εργασίας στο οποίο εργάζεστε, κάντε δεξί κλικ σε οποιοδήποτε από τα φύλλα εργασίας και επιλέξτε Εισαγωγή -> Ενότητα. Αυτό θα εισάγει μια νέα ενότητα. Αντιγράψτε και επικολλήστε τον κώδικα στο παράθυρο κωδικού λειτουργικής μονάδας.
• Κάντε διπλό κλικ στο όνομα της λειτουργικής μονάδας (από προεπιλογή το όνομα της ενότητας στη μονάδα 1) και επικολλήστε τον κωδικό στο παράθυρο κωδικού.
• Κλείστε το πρόγραμμα επεξεργασίας VB.
• Αυτό είναι! Έχετε τώρα μια προσαρμοσμένη συνάρτηση στο φύλλο εργασίας που ονομάζεται GetColorCount.

Για να χρησιμοποιήσετε αυτήν τη συνάρτηση, απλώς χρησιμοποιήστε την ως οποιαδήποτε κανονική συνάρτηση excel.

Σύνταξη: = GetColorCount (CountRange, CountColor)

• Εύρος Count: το εύρος στο οποίο θέλετε να μετρήσετε τα κελιά με το καθορισμένο χρώμα φόντου.
• CountColor: το χρώμα για το οποίο θέλετε να μετρήσετε τα κελιά.

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

Σημείωση: Δεδομένου ότι υπάρχει ένας κωδικός στο βιβλίο εργασίας, αποθηκεύστε τον με επέκταση .xls ή .xlsm.

Δοκιμάστε το μόνοι σας … Κατεβάστε το Παράδειγμα αρχείου

Γνωρίζετε κάποιον άλλο τρόπο για να μετρήσετε τα χρωματιστά κελιά στο Excel;

Αν ναι, μοιραστείτε το μαζί μου αφήνοντας ένα σχόλιο.