Γενική φόρμουλα
SUMPRODUCT(--(A:A=A1))
Περίληψη
Πρόλογος
Αυτή είναι μια ενοχλητικά μακρά εισαγωγή, αλλά το πλαίσιο είναι σημαντικό, συγγνώμη!
Εάν προσπαθείτε να μετρήσετε πολύ μεγάλους αριθμούς (16+ ψηφία) σε μια περιοχή με COUNTIF, ενδέχεται να δείτε εσφαλμένα αποτελέσματα, λόγω ενός σφάλματος στον τρόπο με τον οποίο ορισμένες λειτουργίες χειρίζονται μεγάλους αριθμούς, ακόμα και όταν αυτοί οι αριθμοί αποθηκεύονται ως κείμενο. Εξετάστε την παρακάτω οθόνη. Όλες οι μετρήσεις στη στήλη D είναι λανθασμένες - αν και κάθε αριθμός στη στήλη B είναι μοναδικός, ο αριθμός που επιστρέφεται από τον COUNTIF υποδηλώνει ότι αυτοί οι αριθμοί είναι διπλοί.
=COUNTIF(data,B5)
Αυτό το πρόβλημα σχετίζεται με τον τρόπο χειρισμού των αριθμών από το Excel. Το Excel μπορεί να χειριστεί μόνο 15 σημαντικά ψηφία και εάν εισαγάγετε έναν αριθμό με περισσότερα από 15 ψηφία στο Excel, θα δείτε τα ψηφία που ακολουθούν μετατρέπονται σιωπηλά σε μηδέν. Το πρόβλημα καταμέτρησης που αναφέρθηκε παραπάνω προκύπτει από αυτό το όριο.
Κανονικά, μπορείτε να αποφύγετε αυτό το όριο εισάγοντας μεγάλους αριθμούς ως κείμενο, είτε ξεκινώντας τον αριθμό με ένα μόνο απόσπασμα ('99999999999999999999) είτε μορφοποιώντας τα κελιά ως κείμενο πριν εισαγάγετε. Εφόσον δεν χρειάζεται να εκτελείτε μαθηματικές λειτουργίες σε έναν αριθμό, αυτή είναι μια καλή λύση και σας επιτρέπει να εισάγετε πολύ μεγάλους αριθμούς για πράγματα όπως αριθμούς πιστωτικών καρτών και σειριακούς αριθμούς χωρίς να χάσετε αριθμούς.
Ωστόσο, εάν προσπαθήσετε να χρησιμοποιήσετε το COUNTIF για να μετρήσετε έναν αριθμό με περισσότερα από 15 ψηφία (ακόμα και όταν αποθηκεύονται ως κείμενο), ενδέχεται να δείτε αναξιόπιστα αποτελέσματα. Αυτό συμβαίνει επειδή το COUNTIF μετατρέπει εσωτερικά τη μεγάλη τιμή σε αριθμό σε κάποιο σημείο κατά τη διάρκεια της επεξεργασίας, ενεργοποιώντας το 15ψήφιο όριο που περιγράφεται παραπάνω. Χωρίς να υπάρχουν όλα τα ψηφία, ορισμένοι αριθμοί μπορούν να μετρηθούν σαν διπλότυπα όταν μετρηθούν με COUNTIF.
Λύση
Μία λύση είναι να αντικαταστήσετε τον τύπο COUNTIF με έναν τύπο που χρησιμοποιεί SUM ή SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο E5 μοιάζει με τον εξής:
=SUMPRODUCT(--(data=B5))
Ο τύπος χρησιμοποιεί το ονομαζόμενο εύρος "δεδομένα" (B5: B9) και δημιουργεί τη σωστή μέτρηση για κάθε αριθμό με το SUMPRODUCT.
Εξήγηση
Κατ 'αρχάς, η έκφραση μέσα στο SUMPRODUCT συγκρίνει όλες τις τιμές στο ονομαζόμενο εύρος "δεδομένα" με την τιμή από τη στήλη B στην τρέχουσα σειρά. Αυτό έχει ως αποτέλεσμα μια σειρά από ΑΛΗΘΕΙΑ / ΛΑΘΟΣ αποτελέσματα.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Στη συνέχεια, το διπλό αρνητικό συντελεί στις τιμές TRUE / FALSE σε 1/0.
=SUMPRODUCT((1;0;0;0;0))
Τέλος, το SUMPRODUCT απλώς αθροίζει τα στοιχεία του πίνακα και επιστρέφει το αποτέλεσμα.
Παραλλαγή τύπου σειράς
Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση SUM αντί για SUMPRODUCT, αλλά αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter:
(=SUM(--(B:B=B5)))
Άλλες λειτουργίες με αυτό το πρόβλημα
Δεν το έχω επαληθεύσει μόνοι μου, αλλά φαίνεται ότι πολλές λειτουργίες έχουν το ίδιο πρόβλημα, όπως SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF και AVERAGEIFS.
