Ας πούμε ότι θέλετε να μπορείτε να μετράτε μοναδικά αντικείμενα από μια λίστα, αλλά με μια στροφή. Και πείτε ότι εργάζεστε με αυτό το φύλλο εργασίας:
Η στήλη D μετρά τον αριθμό των σειρών σε καθεμία από τις ενότητες από τη στήλη B και η στήλη C μετρά τον αριθμό των μοναδικών ενοτήτων με βάση τους πέντε πρώτους χαρακτήρες της στήλης Α για αυτήν την ενότητα. Τα κελιά B2: B11 περιέχουν ARG και μπορείτε να μετρήσετε οκτώ μοναδικά στοιχεία στους πέντε πρώτους χαρακτήρες του A2: A11 επειδή το A7: A9 το καθένα περιέχει 11158, οπότε τα δύο αντίγραφα δεν υπολογίζονται. Ομοίως, το 5 σε D12 σας λέει ότι υπάρχουν πέντε σειρές για το BRD, αλλά μέσα στις σειρές 12:16, υπάρχουν τρία μοναδικά στοιχεία των πέντε πρώτων χαρακτήρων, καθώς το 11145 επαναλαμβάνεται και το 11173 επαναλαμβάνεται.
Αλλά πώς λέτε στο Excel να το κάνει αυτό; Και ποιος τύπος θα μπορούσατε να χρησιμοποιήσετε στο C2 που θα μπορούσε να αντιγραφεί στους C12 και C17;
Ο απλός τύπος μέτρησης στο D2, =COUNTIF(B:B,B2)μετρά τον αριθμό των φορών που υπάρχει το B2 (ARG) στη στήλη Β.
Χρησιμοποιείτε μια βοηθητική στήλη για να απομονώσετε τους πρώτους πέντε χαρακτήρες της στήλης Α, όπως σε αυτό το σχήμα:
Στη συνέχεια, πρέπει κάπως να υποδείξετε ότι για το ARG, ενδιαφέρεστε μόνο για τα κελιά F2: F11 για να βρείτε τον αριθμό των μοναδικών αντικειμένων. Σε γενικές γραμμές, θα βρείτε αυτήν την τιμή χρησιμοποιώντας τον τύπο πίνακα που φαίνεται σε αυτό το σχήμα:
Χρησιμοποιείτε προσωρινά το κελί C3 μόνο για να δείξετε τον τύπο. μπορείτε να δείτε ότι δεν υπάρχει στο C3 σε προηγούμενα σχήματα. (Θα μάθετε σύντομα πώς λειτουργεί αυτός ο τύπος.)
Λοιπόν, ποιος είναι ο τύπος στα C2, C12 και C17; Η εκπληκτική (και δροσερή) απάντηση φαίνεται σε αυτό το σχήμα:
Ω! Πως λειτουργεί αυτό?
Ρίξτε μια ματιά στην Απάντηση στα καθορισμένα ονόματα σε αυτό το σχήμα:
Είναι η ίδια φόρμουλα από μια προηγούμενη εικόνα, αλλά αντί να χρησιμοποιεί την περιοχή F2: F11, χρησιμοποιεί μια περιοχή που ονομάζεται Rg. Επίσης, ο τύπος ήταν ένας τύπος πίνακα, αλλά οι επώνυμοι τύποι αντιμετωπίζονται σαν να είναι τύποι συστοιχιών! Δηλαδή, =Answerδεν εισάγεται με Ctrl + Shift + Enter αλλά απλά εισάγεται ως συνήθως.
Πώς ορίζεται λοιπόν η Rg; Εάν έχει επιλεγεί το κελί C1 (που είναι ένα σημαντικό βήμα για την κατανόηση αυτού του κόλπου), τότε ορίζεται όπως σε αυτό το σχήμα:
Αυτό είναι =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Το Loan_Details είναι το όνομα του φύλλου, αλλά μπορείτε να δείτε αυτόν τον τύπο χωρίς το μεγάλο όνομα φύλλου. Ένας εύκολος τρόπος για να γίνει αυτό είναι να ονομάσετε προσωρινά το φύλλο κάτι απλό, όπως το x και, στη συνέχεια, να κοιτάξετε ξανά το καθορισμένο όνομα:
Αυτή η φόρμουλα είναι πιο ευανάγνωστη!
Μπορείτε να δείτε ότι αυτός ο τύπος ταιριάζει με $ B1 (σημειώστε τη σχετική αναφορά στην τρέχουσα σειρά) με όλες τις στήλες B και αφαιρέσεις 1. Αφαιρείτε το 1 επειδή χρησιμοποιείτε το OFFSET από το F1. Τώρα που γνωρίζετε για τον τύπο για το C, ρίξτε μια ματιά σε αυτόν για το C2:
Το MATCH($B2,$B:$B,0)μέρος του τύπου είναι 2, οπότε ο τύπος (χωρίς την αναφορά στο όνομα του φύλλου) είναι:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
ή:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
ή:
=OFFSET($F$1,1,0,10,1)
Επειδή COUNTIF($B:$B,$B2)είναι 10, υπάρχουν 10 ARG. Αυτό είναι το εύρος F2: F11. Στην πραγματικότητα, εάν έχει επιλεγεί το κελί C2 και πατήσετε F5 για να μεταβείτε στο Rg, βλέπετε αυτό:
Εάν το αρχικό κελί ήταν C12, πατώντας το F5 για να πάει στο Rg παράγει αυτό:
Τώρα λοιπόν, με την απάντηση που ορίζεται ως =SUM(1/COUNTIF(rg,rg)), τελειώσατε!
Ας δούμε πιο προσεκτικά πώς λειτουργεί αυτός ο τύπος, χρησιμοποιώντας ένα πολύ απλούστερο παράδειγμα. Κανονικά, η σύνταξη για COUNTIF είναι =COUNTIF(range,criteria), όπως =COUNTIF(C1:C10, "b")σε αυτό το σχήμα:
Αυτό θα έδινε 2 ως τον αριθμό των b στο εύρος. Αλλά το ίδιο το εύρος ως κριτήριο χρησιμοποιεί κάθε στοιχείο στο εύρος ως κριτήρια. Εάν επισημάνετε αυτό το τμήμα του τύπου:
και πατήστε F9, βλέπετε:
Κάθε στοιχείο στο εύρος αξιολογείται και αυτή η σειρά αριθμών σημαίνει ότι υπάρχει ένα a και υπάρχουν δύο b, τρία c και τέσσερα d. Αυτοί οι αριθμοί χωρίζονται σε 1, δίνοντας 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, όπως μπορείτε να δείτε εδώ:
Έχετε λοιπόν 2 μισά, 3 τρίτα, 4 τέταρτα και 1 ολόκληρο, και προσθέτοντάς τα αποδόσεις 4. Εάν ένα στοιχείο επαναλήφθηκε 7 φορές, τότε θα έχετε 7 έβδομα και ούτω καθεξής. Πολύ ωραίο! (Καπέλα στον David Hager για ανακάλυψη / εφεύρεση αυτού του τύπου.)
Αλλά κρατήστε ένα λεπτό. Ως έχει, πρέπει να εισαγάγετε αυτόν τον τύπο μόνο στα C2, C12 και C17. Δεν θα ήταν καλύτερα αν μπορούσατε να το εισαγάγετε στο C2 και να το συμπληρώσετε και να το δείξετε μόνο στα σωστά κελιά; Στην πραγματικότητα, μπορείτε να το κάνετε αυτό. Μπορείτε να τροποποιήσετε τον τύπο στο C2 =IF(B1B2,Answer,""), και όταν το συμπληρώσετε, κάνει τη δουλειά:
Γιατί όμως να σταματήσεις εδώ; Γιατί να μην κάνετε τον τύπο σε έναν ονομαστικό τύπο, όπως φαίνεται εδώ:
Για να λειτουργήσει αυτό, το κελί C2 πρέπει να είναι το ενεργό κελί (ή ο τύπος θα πρέπει να είναι διαφορετικός). Τώρα μπορείτε να αντικαταστήσετε τους τύπους της στήλης Γ με =Answer2:
Μπορείτε να δείτε ότι το C3 έχει =Answer2, όπως και όλα τα κελιά στη στήλη Γ. Γιατί να μην το συνεχίσετε στη στήλη Δ; Ο τύπος στο D2, αφού εφαρμόσει επίσης τη σύγκριση με τα B1 και B2, εμφανίζεται εδώ:
Αν λοιπόν διατηρήσετε επιλεγμένο το κελί D2 και ορίσετε έναν άλλο τύπο, πείτε Απάντηση3:
τότε μπορείτε να εισάγετε =Answer3στο κελί D2 και να συμπληρώσετε:
Εδώ είναι το κορυφαίο μέρος του φύλλου εργασίας, με τύπους που εμφανίζονται, ακολουθούμενο από το ίδιο στιγμιότυπο οθόνης με τιμές που δείχνουν:
Όταν άλλοι άνθρωποι προσπαθούν να το καταλάβουν, μπορεί να ξύσουν το κεφάλι τους στην αρχή!
Αυτό το άρθρο επισκεπτών προέρχεται από το Excel MVP Bob Umlas. Είναι από το βιβλίο, Περισσότερα Excel εκτός κουτιού. Για να δείτε τα άλλα θέματα στο βιβλίο, κάντε κλικ εδώ.
