Γενική φόρμουλα
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Περίληψη
Για να δημιουργήσετε έναν πίνακα σύνοψης με μια αρίθμηση ζευγαρωμένων στοιχείων που εμφανίζονται σε μια λίστα υπαρχόντων συνδυασμών, μπορείτε να χρησιμοποιήσετε μια βοηθητική στήλη και έναν τύπο που βασίζεται στη συνάρτηση COUNTIFS. Στο παράδειγμα που εμφανίζεται ο τύπος στο κελί H5 είναι:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
όπου "helper" είναι το ονομαζόμενο εύρος E5: E24.
Σημείωση: αυτός ο τύπος προϋποθέτει ότι τα στοιχεία δεν επαναλαμβάνονται σε έναν δεδομένο συνδυασμό (δηλαδή το AAB, το EFE δεν είναι έγκυροι συνδυασμοί).
Εξήγηση
Θέλουμε να μετρήσουμε τη συχνότητα εμφάνισης των στοιχείων στις στήλες B, C και D. Για παράδειγμα, πόσο συχνά εμφανίζεται το A με το C, το B εμφανίζεται με το F, το G εμφανίζεται με το D και ούτω καθεξής. Αυτό φαίνεται σαν μια τέλεια χρήση COUNTIFS, αλλά αν προσπαθήσουμε να προσθέσουμε κριτήρια που αναζητούν 2 στοιχεία σε 3 στήλες, δεν θα λειτουργήσει.
Μια απλή λύση είναι να ενώσετε όλα τα στοιχεία μαζί σε ένα κελί και, στη συνέχεια, να χρησιμοποιήσετε COUNTIFS με μπαλαντέρ για να μετρήσετε τα στοιχεία. Το κάνουμε αυτό με μια βοηθητική στήλη (E) που ενώνει στοιχεία στις στήλες B, C και D χρησιμοποιώντας τη συνάρτηση CONCAT ως εξής:
=CONCAT(B5:D5)
Σε παλαιότερες εκδόσεις του Excel, μπορείτε να χρησιμοποιήσετε έναν τύπο όπως αυτό:
=B5&C5&D5
Επειδή δεν επιτρέπονται επαναλαμβανόμενα στοιχεία σε συνδυασμό, το πρώτο μέρος του τύπου εξαιρεί τα αντίστοιχα στοιχεία. Εάν τα δύο στοιχεία είναι τα ίδια, ο τύπος επιστρέφει παύλα ή παύλα ως κείμενο:
=IF($G5=H$4,"-"
Εάν τα στοιχεία είναι διαφορετικά, εκτελείται μια συνάρτηση COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
Το COUNTIFS έχει ρυθμιστεί ώστε να μετράει "ζεύγη" αντικειμένων. Μόνο όταν το στοιχείο στη στήλη G και το αντίστοιχο στοιχείο από τη σειρά 4 εμφανίζονται μαζί σε ένα κελί, το ζεύγος μετράται. Ένα μπαλαντέρ (*) συνδυάζεται και στις δύο πλευρές του αντικειμένου για να διασφαλιστεί ότι ένας αγώνας θα μετρηθεί ανεξάρτητα από το πού εμφανίζεται στο κελί.
