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

Μετρήστε και αθροίστε τα κελιά με βάση το χρώμα του φόντου

• Χρησιμοποιώντας τη λειτουργία καθορισμένη από το χρήστη
• Χρησιμοποιώντας ένα ισχυρό χαρακτηριστικό – Kutools for Excel
• Χρησιμοποιώντας το φίλτρο και το SUBTOTAL

Καταμέτρηση και άθροιση κελιών με βάση το χρώμα της γραμματοσειράς

• Χρησιμοποιώντας τη λειτουργία καθορισμένη από το χρήστη
• Χρησιμοποιώντας μια εύκολη λειτουργία - Kutools for Excel

Καταμέτρηση και άθροιση κελιών με βάση το χρώμα μορφοποίησης υπό όρους

• Χρησιμοποιώντας κώδικα VBA
• Χρησιμοποιώντας μια έξυπνη λειτουργία – Kutools for Excel

Βίντεο: Μετρήστε και αθροίστε τα κελιά με βάση το χρώμα

Μετρήστε και αθροίστε τα κελιά με βάση το χρώμα του φόντου

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

Μετρήστε και αθροίστε τα κελιά ανά χρώμα φόντου με τη συνάρτηση που καθορίζεται από το χρήστη

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

Βήμα 1: Ανοίξτε το πρόγραμμα επεξεργασίας μονάδας VBA και αντιγράψτε τον κώδικα

1. Τύπος Alt + F11 για να ανοίξετε το Microsoft Visual Basic για εφαρμογές παράθυρο.
2. Στο παράθυρο που ανοίγει, κάντε κλικ Κύριο θέμα > Μονάδα μέτρησης για να δημιουργήσετε μια νέα κενή ενότητα.
3. Στη συνέχεια, αντιγράψτε και επικολλήστε τον παρακάτω κώδικα στην κενή μονάδα.
   Κωδικός VBA: Καταμέτρηση και άθροιση κελιών με βάση το χρώμα του φόντου

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Βήμα 2: Δημιουργήστε τύπους για μέτρηση και άθροιση κελιών με βάση το χρώμα του φόντου

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

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

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Note: Σε αυτόν τον τύπο, G2 είναι το κελί αναφοράς με το συγκεκριμένο χρώμα φόντου που θέλετε να ταιριάξετε. $B$2:$E$12 είναι το εύρος όπου θέλετε να μετρήσετε τον αριθμό των κελιών του χρώματος του G2. ΨΕΥΔΗΣ χρησιμοποιείται για την καταμέτρηση κελιών με αντίστοιχο χρώμα.
  
• Άθροισμα κελιών με βάση το συγκεκριμένο χρώμα φόντου:
  Αντιγράψτε ή πληκτρολογήστε τον τύπο που παρέχεται παρακάτω στο κελί που θέλετε για το αποτέλεσμα. Στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω για να λάβετε άλλα αποτελέσματα. Δείτε στιγμιότυπο οθόνης:
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Note: Σε αυτόν τον τύπο, G2 είναι το κελί αναφοράς με το συγκεκριμένο χρώμα φόντου που θέλετε να ταιριάξετε. $B$2:$E$12 είναι το εύρος όπου θέλετε να μετρήσετε τον αριθμό των κελιών του χρώματος του G2. ΑΛΗΘΙΝΗ χρησιμοποιείται για την άθροιση κελιών με αντίστοιχο χρώμα.
  

Μετρήστε και αθροίστε τα κελιά ανά χρώμα φόντου με ένα ισχυρό χαρακτηριστικό

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

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

Στο Μετρήστε ανά χρώμα παράθυρο διαλόγου, καθορίστε τις λειτουργίες:

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

Αποτέλεσμα:

Τώρα θα λάβετε ένα νέο βιβλίο εργασίας με τα στατιστικά στοιχεία. Δείτε το στιγμιότυπο οθόνης:

Μετρήστε και αθροίστε τα κελιά ανά χρώμα φόντου με τη λειτουργία φίλτρου και SUBTOTAL

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

Βήμα 1: Εφαρμόστε τη συνάρτηση SUBTOTAL

Επιλέξτε κενά κελιά για να εισέλθετε στη συνάρτηση SUBTOTAL.

• Για να μετρήσετε όλα τα κελιά με το ίδιο χρώμα φόντου, εισαγάγετε τον τύπο:

  =SUBTOTAL(102, F2:F16)

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

  =SUBTOTAL(109, F2:F16)

• Note: στους παραπάνω τύπους, 102 αντιπροσωπεύει την καταμέτρηση αριθμητικών τιμών σε μια φιλτραρισμένη λίστα, ενώ εξαιρούνται τα κρυφά κελιά. 109 αντιπροσωπεύει τη σύνοψη τιμών σε μια φιλτραρισμένη λίστα εξαιρουμένων των κρυφών κελιών. F2: F16 είναι το εύρος στο οποίο θα υπολογιστεί είτε η καταμέτρηση είτε το άθροισμα.
• 

Βήμα 2: Φιλτράρισμα κελιών με βάση συγκεκριμένο χρώμα

1. Επιλέξτε την κεφαλίδα του πίνακα και κάντε κλικ ημερομηνία > Φίλτρα. Δείτε το στιγμιότυπο οθόνης:
   
2. Κάντε κλικ στο Φίλτρα εικονίδιο  στο κελί κεφαλίδας του Ποσό στήλη και κάντε κλικ στο Φίλτρο ανά χρώμα και το καθορισμένο χρώμα που θα μετρήσετε διαδοχικά. Δείτε το στιγμιότυπο οθόνης:
   

Αποτέλεσμα:

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

Καταμέτρηση και άθροιση κελιών με βάση το χρώμα της γραμματοσειράς

Θέλετε να μετρήσετε ή να αθροίσετε τα κελιά με βάση το χρώμα της γραμματοσειράς τους στο Excel; Ας υποθέσουμε ότι έχετε τα δεδομένα, όπως στο συγκεκριμένο στιγμιότυπο οθόνης, με κελιά που περιέχουν κείμενα σε κόκκινο, μπλε, πορτοκαλί και μαύρο χρώμα. Το Excel δεν το κάνει εύκολο από προεπιλογή. Αλλά μην ανησυχείτε! Σε αυτήν την ενότητα, θα σας δείξουμε μερικά απλά κόλπα για να το κάνετε ακριβώς αυτό.

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

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

Βήμα 1: Ανοίξτε το πρόγραμμα επεξεργασίας μονάδας VBA και αντιγράψτε τον κώδικα

1. Τύπος Alt + F11 για να ανοίξετε το Microsoft Visual Basic για εφαρμογές παράθυρο.
2. Στο παράθυρο που ανοίγει, κάντε κλικ Κύριο θέμα > Μονάδα μέτρησης για να δημιουργήσετε μια νέα κενή ενότητα.
3. Στη συνέχεια, αντιγράψτε και επικολλήστε τον παρακάτω κώδικα στην κενή μονάδα.
   Κωδικός VBA: Καταμέτρηση και άθροιση κελιών με βάση το χρώμα της γραμματοσειράς

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Βήμα 2: Δημιουργήστε τύπους για μέτρηση και άθροιση κελιών ανά χρώμα γραμματοσειράς

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

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

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Note: Σε αυτόν τον τύπο, G2 είναι το κελί αναφοράς με το συγκεκριμένο χρώμα γραμματοσειράς που θέλετε να ταιριάξετε. $B$2:$E$12 είναι το εύρος όπου θέλετε να μετρήσετε τον αριθμό των κελιών του χρώματος του G2.
  
• Άθροισμα κελιών με βάση το συγκεκριμένο χρώμα γραμματοσειράς:
  Αντιγράψτε ή πληκτρολογήστε τον τύπο που παρέχεται παρακάτω στο κελί που θέλετε για το αποτέλεσμα. Στη συνέχεια, σύρετε τη λαβή πλήρωσης προς τα κάτω για να λάβετε άλλα αποτελέσματα. Δείτε στιγμιότυπο οθόνης:
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Note: Σε αυτόν τον τύπο, G2 είναι το κελί αναφοράς με το συγκεκριμένο χρώμα γραμματοσειράς που θέλετε να ταιριάξετε. $B$2:$E$12 είναι το εύρος όπου θέλετε να μετρήσετε τον αριθμό των κελιών του χρώματος του G2.
  

Μετρήστε και αθροίστε τα κελιά με βάση το χρώμα της γραμματοσειράς με μια εύκολη λειτουργία

Θέλετε να μετρήσετε ή να αθροίσετε αβίαστα τις τιμές κελιών στο Excel με βάση το χρώμα της γραμματοσειράς; Βουτήξτε μέσα Kutools for Excel's Μετρήστε ανά χρώμα χαρακτηριστικό! Με αυτό το έξυπνο εργαλείο, η καταμέτρηση και η άθροιση κελιών με βάση συγκεκριμένο χρώμα γραμματοσειράς γίνεται παιχνιδάκι. Ανακαλύψτε πώς Kutools μπορεί να μεταμορφώσει την εμπειρία σας στο Excel.

Μετά το λήψη και εγκατάσταση Kutools for Excel, πρώτα επιλέξτε το εύρος δεδομένων που θέλετε να μετρήσετε ή αθροίστε τα κελιά με βάση ένα συγκεκριμένο χρώμα γραμματοσειράς. Στη συνέχεια, κάντε κλικ Kutools Plus > Μετρήστε ανά χρώμα για να ανοίξετε το Μετρήστε ανά χρώμα κουτί διαλόγου.

Στο Μετρήστε ανά χρώμα παράθυρο διαλόγου, καθορίστε τις λειτουργίες:

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

Αποτέλεσμα:

Τώρα, έχετε ένα νέο βιβλίο εργασίας που εμφανίζει τα λεπτομερή στατιστικά στοιχεία με βάση το χρώμα της γραμματοσειράς. Δείτε στιγμιότυπο οθόνης:

Καταμέτρηση και άθροιση κελιών με βάση το χρώμα μορφοποίησης υπό όρους

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

Μετρήστε και αθροίστε τα μορφοποιημένα κελιά υπό όρους με κώδικα VBA

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

Βήμα 1: Ανοίξτε το πρόγραμμα επεξεργασίας μονάδας VBA και αντιγράψτε τον κώδικα

1. Τύπος Alt + F11 για να ανοίξετε το Microsoft Visual Basic για εφαρμογές παράθυρο.
2. Στο παράθυρο που ανοίγει, κάντε κλικ Κύριο θέμα > Μονάδα μέτρησης για να δημιουργήσετε μια νέα κενή ενότητα.
3. Στη συνέχεια, αντιγράψτε και επικολλήστε τον παρακάτω κώδικα στην κενή μονάδα.
   Κωδικός VBA: Καταμέτρηση και άθροιση κελιών με βάση το χρώμα μορφοποίησης υπό όρους

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Βήμα 2: Εκτελέστε αυτόν τον κώδικα VBA

1. Αφού επικολλήσετε τον κωδικό, πατήστε F5 κλειδί για την εκτέλεση αυτού του κώδικα, θα εμφανιστεί ένα πλαίσιο προτροπής, επιλέξτε το εύρος δεδομένων όπου θέλετε να μετρήσετε και αθροίστε τα κελιά με βάση τη μορφοποίηση υπό όρους. Στη συνέχεια, κάντε κλικ OK, Δείτε το στιγμιότυπο οθόνης:
   
2. Σε ένα άλλο πλαίσιο προτροπής, επιλέξτε ένα συγκεκριμένο χρώμα μορφοποίησης υπό όρους που θέλετε να μετρήσετε και να αθροίσετε και κάντε κλικ OK κουμπί, δείτε το στιγμιότυπο οθόνης:
   

Αποτέλεσμα:

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

Μετρήστε και αθροίστε τα μορφοποιημένα κελιά υπό όρους με μια έξυπνη λειτουργία

Αν ψάχνετε για άλλες γρήγορες και εύκολες μεθόδους μέτρησης και άθροισης κελιών υπό όρους, Kutools for Excel είναι η λύση που προτιμάτε. Του Μετρήστε ανά χρώμα Η δυνατότητα μπορεί να λύσει αυτήν την εργασία με λίγα μόνο κλικ. Βουτήξτε για να ανακαλύψετε την αποτελεσματικότητα και την ακρίβεια που μπορούν να προσφέρουν τα Kutools στη ροή εργασίας σας.

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

Στο Μετρήστε ανά χρώμα παράθυρο διαλόγου, καθορίστε τις λειτουργίες:

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

Αποτέλεσμα:

Τώρα, έχετε ένα νέο βιβλίο εργασίας που εμφανίζει τα λεπτομερή στατιστικά στοιχεία με βάση το χρώμα μορφοποίησης υπό όρους. Δείτε στιγμιότυπο οθόνης:

Σχετικά Άρθρα: