Περίληψη
Για να δημιουργήσετε μια μέτρηση λειτουργίας σε έναν πίνακα Excel, μπορείτε να χρησιμοποιήσετε τη συνάρτηση INDEX με μια δομημένη αναφορά για να δημιουργήσετε ένα επεκτεινόμενο εύρος. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5 είναι:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Όταν αντιγράφεται στη στήλη, αυτός ο τύπος θα επιστρέψει έναν αριθμό εκτέλεσης για κάθε χρώμα στη στήλη Χρώμα.
Σε ορισμένες εκδόσεις του Excel, αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με το control + shift + enter.
Εξήγηση
Στον πυρήνα, αυτός ο τύπος χρησιμοποιεί το INDEX για να δημιουργήσει μια επεκτατική αναφορά όπως αυτή:
INDEX((Color),1):(@Color) // expanding range
Στην αριστερή πλευρά της άνω και κάτω τελείας (:), η συνάρτηση INDEX επιστρέφει μια αναφορά στο πρώτο κελί στη στήλη της στήλης.
INDEX((Color),1) // first cell in color
Αυτό λειτουργεί επειδή, η συνάρτηση INDEX επιστρέφει μια αναφορά στο πρώτο κελί και όχι στην πραγματική τιμή. Στη δεξιά πλευρά του παχέος εντέρου, λαμβάνουμε μια αναφορά στην τρέχουσα σειρά της έγχρωμης στήλης ως εξής:
(@Color) // current row of Color
Αυτή είναι η τυπική σύνταξη δομημένης αναφοράς για "αυτήν τη σειρά". Σε συνδυασμό με την άνω και κάτω τελεία, αυτές οι δύο αναφορές δημιουργούν ένα εύρος που επεκτείνεται καθώς ο τύπος αντιγράφεται στον πίνακα. Έτσι, ανταλλάξουμε αυτές τις αναφορές στη συνάρτηση SUM, έχουμε:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Κάθε μία από τις παραπάνω παραστάσεις δημιουργεί έναν πίνακα τιμών TRUE / FALSE και το διπλό αρνητικό (-) χρησιμοποιείται για τη μετατροπή αυτών των τιμών σε 1s και 0s. Έτσι, στην τελευταία σειρά, καταλήγουμε με:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Ο υπόλοιπος τύπος συνδυάζει απλά το χρώμα από την τρέχουσα σειρά έως τον αριθμό που επέστρεψε το SUM:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Απλή επέκταση εύρους;
Γιατί να μην χρησιμοποιήσετε ένα απλό επεκτατικό εύρος σαν αυτό;
SUM(--($B$5:B5=(@Color)))
Για κάποιο λόγο, αυτό το είδος μικτής αναφοράς καταστραφεί σε έναν πίνακα Excel καθώς προστίθενται σειρές. Η χρήση του INDEX με δομημένη αναφορά επιλύει το πρόβλημα.
