Περίληψη
Για να ρυθμίσετε τις παραμέτρους COUNTIFS (ή COUNTIF) με εύρος μεταβλητών, μπορείτε να χρησιμοποιήσετε τη συνάρτηση OFFSET. Στο παράδειγμα που εμφανίζεται, ο τύπος στο B11 είναι:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Αυτός ο τύπος μετρά μη κενά κελιά σε μια περιοχή που ξεκινά από το B5 και τελειώνει 2 σειρές πάνω από το κελί όπου ζει ο τύπος. Ο ίδιος τύπος αντιγράφεται και επικολλάται 2 σειρές κάτω από την τελευταία καταχώρηση στα δεδομένα όπως φαίνεται.
Εξήγηση
Στο παράδειγμα που εμφανίζεται, ο τύπος στο B11 είναι:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Δουλεύοντας από μέσα προς τα έξω, το έργο της ρύθμισης ενός μεταβλητού εύρους γίνεται από τη λειτουργία OFFSET εδώ:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
Το OFFSET έχει πέντε ορίσματα και έχει διαμορφωθεί ως εξής:
- αναφορά = B $ 5, ξεκινήστε από το κελί B5, κλειδωμένη σειρά
- σειρές = 0, μετατόπιση μηδενικών σειρών από το αρχικό κελί
- cols = 0, μετατόπιση μηδενικών στηλών αρχικού κελιού
- ύψος = ROW () - ROW (B $ 5) -1 = 5 σειρές ύψος
- πλάτος = 1 στήλη πλάτος
Για να υπολογίσουμε το ύψος του εύρους σε σειρές, χρησιμοποιούμε τη λειτουργία ROW ως εξής:
ROW()-ROW(B$5)-1 // work out height
Δεδομένου ότι το ROW () επιστρέφει τον αριθμό σειράς του "τρέχοντος" κελιού (δηλαδή το κελί στον οποίο ζει ο τύπος), μπορούμε να το απλοποιήσουμε ως εξής:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Με την παραπάνω διαμόρφωση, το OFFSET επιστρέφει το εύρος B5: B9 απευθείας σε COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Παρατηρήστε ότι η αναφορά στο B $ 5 στον παραπάνω τύπο είναι μια μικτή αναφορά, με τη στήλη σχετική και τη σειρά κλειδωμένη. Αυτό επιτρέπει στον τύπο να αντιγραφεί σε άλλη στήλη και να λειτουργήσει ακόμα. Για παράδειγμα, μόλις αντιγραφεί στο C12, ο τύπος είναι:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Σημείωση: Το OFFSET είναι μια πτητική λειτουργία και μπορεί να προκαλέσει προβλήματα απόδοσης σε μεγάλα ή πολύπλοκα φύλλα εργασίας.
Με ΑΜΕΣΗ και ΔΙΕΥΘΥΝΣΗ
Μια άλλη προσέγγιση είναι να χρησιμοποιήσετε έναν τύπο που βασίζεται στις συναρτήσεις INDIRECT και ADDRESS. Σε αυτήν την περίπτωση, συγκεντρώνουμε ένα εύρος ως κείμενο και, στη συνέχεια, χρησιμοποιούμε το INDIRECT για να αξιολογήσουμε το κείμενο ως αναφορά. Ο τύπος στο B11 θα ήταν:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Η συνάρτηση ADDRESS χρησιμοποιείται για την κατασκευή ενός εύρους ως εξής:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Στην πρώτη εμφάνιση της ΔΙΕΥΘΥΝΣΗΣ, παρέχουμε τον αριθμό σειράς ως την τιμή 5 του κωδικού και παρέχουμε στον αριθμό στήλης τη συνάρτηση COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Στη δεύτερη περίπτωση, παρέχουμε τον "τρέχοντα" αριθμό σειράς μείον 2 και την τρέχουσα στήλη με τη συνάρτηση COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Αφού συνενώσαμε αυτές τις δύο τιμές μαζί, έχουμε:
"$B$5:$B$9" // as text
Σημειώστε ότι πρόκειται για συμβολοσειρά κειμένου. Για να μετατρέψουμε σε έγκυρη αναφορά, πρέπει να χρησιμοποιήσουμε το INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Τέλος, ο τύπος στο B11 γίνεται:
=COUNTIFS($B$5:$B$9,"") // returns 4
Σημείωση: Το INDIRECT είναι μια πτητική λειτουργία και μπορεί να προκαλέσει προβλήματα απόδοσης σε μεγάλα ή πολύπλοκα φύλλα εργασίας.
