Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Συζητήσαμε σε άλλα άρθρα για το πώς υπάρχουν λειτουργίες όπως το OFFSET και το INDIRECT που είναι ασταθείς. Εάν αρχίσετε να χρησιμοποιείτε πολλά από αυτά σε ένα υπολογιστικό φύλλο ή έχετε πολλά κελιά που εξαρτώνται από την πτητική λειτουργία, μπορείτε να κάνετε τον υπολογιστή σας να ξοδεύει ένα αξιοσημείωτο χρόνο κάνοντας επανυπολογισμούς κάθε φορά που προσπαθείτε να αλλάξετε ένα κελί. Αντί να απογοητεύεστε για το πώς ο υπολογιστής σας δεν είναι αρκετά γρήγορος, αυτό το άρθρο θα διερευνήσει εναλλακτικούς τρόπους επίλυσης των συνηθισμένων καταστάσεων που χρησιμοποιούν οι άνθρωποι OFFSET και INDIRECT.
Αντικατάσταση OFFSET για δημιουργία δυναμικής λίστας
Αφού μάθετε για τη λειτουργία OFFSET, είναι μια κοινή παρανόηση ότι είναι ο μόνος τρόπος για να επιστρέψετε ένα αποτέλεσμα με δυναμικό μέγεθος χρησιμοποιώντας τα τελευταία δύο επιχειρήματα. Ας δούμε μια λίστα στη στήλη Α όπου ο χρήστης μας μπορεί να αποφασίσει αργότερα να προσθέσει επιπλέον στοιχεία.
Για να δημιουργήσετε ένα αναπτυσσόμενο μενού στο κελί C2, θα μπορούσατε να ορίσετε μια ονομαστική περιοχή με έναν πτητικό τύπο όπως
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Με την τρέχουσα ρύθμιση, αυτό σίγουρα θα επέστρεφε μια αναφορά στο εύρος A2: A5. Ωστόσο, υπάρχει ένας άλλος τρόπος χρήσης του μη πτητικού INDEX. Για να το κάνετε αυτό, σκεφτείτε ότι γράφουμε μια αναφορά στο εύρος που κυμαίνεται από Α2 έως Α5. Όταν γράφετε "A2: A5", μην το σκέφτεστε ως ένα μόνο κομμάτι δεδομένων, αλλά ως "StartingPoint" και "EndingPoint" που χωρίζονται με άνω και κάτω τελεία (π.χ., StartingPoint: EndingPoint). Σε έναν τύπο, τόσο το StartingPoint όσο και το EndingPoint μπορεί να είναι τα αποτελέσματα άλλων συναρτήσεων.
Ακολουθεί ο τύπος που θα χρησιμοποιήσουμε για να δημιουργήσουμε δυναμικό εύρος χρησιμοποιώντας τη συνάρτηση INDEX:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A))
Λάβετε υπόψη ότι έχουμε δηλώσει ότι το StartingPoint για αυτό το εύρος θα είναι πάντα A2. Στην άλλη πλευρά του παχέος εντέρου, χρησιμοποιούμε το INDEX για να καθορίσουμε πού θα βρίσκεται το EndingPoint. Το COUNTA θα καθορίσει ότι υπάρχουν 5 κελιά με δεδομένα στη στήλη Α και έτσι το INDEX θα δημιουργήσει μια αναφορά στο Α5. Ο τύπος έτσι αξιολογείται ως εξής:
= $ A $ 2: INDEX ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: INDEX ($ A: $ A, 5) = $ A $ 2: $ A5
Χρησιμοποιώντας αυτήν την τεχνική, μπορείτε δυναμικά να δημιουργήσετε μια αναφορά σε οποιαδήποτε λίστα ή ακόμη και σε έναν δισδιάστατο πίνακα χρησιμοποιώντας τη συνάρτηση INDEX. Σε ένα υπολογιστικό φύλλο με πληθώρα λειτουργιών OFFSET, η αντικατάσταση των OFFSET με INDEX θα επιτρέψει στον υπολογιστή σας να ξεκινήσει να λειτουργεί πολύ πιο γρήγορα.
Αντικατάσταση INDIRECT για ονόματα φύλλων
Η συνάρτηση INDIRECT συχνά καλείται όταν έχουν σχεδιαστεί βιβλία εργασίας με δεδομένα διάσπαρτα σε πολλά φύλλα εργασίας. Εάν δεν μπορείτε να μεταφέρετε όλα τα δεδομένα σε ένα μόνο φύλλο, αλλά δεν θέλετε να χρησιμοποιήσετε μια πτητική λειτουργία, ίσως να μπορείτε να χρησιμοποιήσετε την ΕΠΙΛΟΓΗ.
Εξετάστε την ακόλουθη διάταξη, όπου έχουμε δεδομένα πωλήσεων σε 3 διαφορετικά φύλλα εργασίας. Στο φύλλο περίληψης, επιλέξαμε από ποιο τρίμηνο θα θέλαμε να βλέπουμε τα δεδομένα.
Ο τύπος μας στο Β3 είναι:
= ΕΠΙΛΟΓΗ (ΑΓΩΝΑΣ (Β2, Δ2: Δ4, 0), Φθινόπωρο! Α2, Χειμώνας! Α2, Άνοιξη! Α2)
Σε αυτόν τον τύπο, η συνάρτηση MATCH θα καθορίσει ποια περιοχή θέλουμε να επιστρέψουμε. Στη συνέχεια, αυτό λέει στη συνάρτηση ΕΠΙΛΟΓΗ ποια από τις ακόλουθες περιοχές θα επιστρέψει ως αποτέλεσμα.
Μπορείτε επίσης να χρησιμοποιήσετε τη λειτουργία CHOOSE για να επιστρέψετε ένα μεγαλύτερο εύρος. Σε αυτό το παράδειγμα, έχουμε έναν πίνακα δεδομένων πωλήσεων για καθένα από τα τρία φύλλα εργασίας μας.
Αντί να γράψετε μια ΕΜΜΕΣΗ συνάρτηση για να δημιουργήσετε το όνομα του φύλλου, μπορείτε να αφήσετε την επιλογή CHOOSE να καθορίσει σε ποιον πίνακα θα γίνει η αναζήτηση. Στο παράδειγμά μου, έχω ήδη ονομάσει τους τρεις πίνακες tbFall, tbWinter και tbSpring. Ο τύπος στο Β4 είναι:
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Σε αυτόν τον τύπο, το MATCH πρόκειται να καθορίσει ότι θέλουμε το 2nd στοιχείο από τη λίστα μας. Το CHOOSE θα πάρει στη συνέχεια αυτό το 2 και θα επιστρέψει την αναφορά στο tbWinter. Τέλος, το VLOOKUP θα μπορεί να ολοκληρώσει την αναζήτηση στον συγκεκριμένο πίνακα και θα διαπιστώσει ότι οι συνολικές πωλήσεις για Banana το χειμώνα ήταν $ 6000.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Αυτή η τεχνική περιορίζεται από το γεγονός ότι πρέπει να συμπληρώσετε τη συνάρτηση CHOOSE με όλες τις περιοχές από τις οποίες μπορεί να θέλετε να λάβετε μια τιμή, αλλά σας προσφέρει το πλεονέκτημα της αποφυγής ενός πτητικού τύπου. Ανάλογα με το πόσους υπολογισμούς χρειάζεστε για να ολοκληρώσετε, αυτή η ικανότητα θα μπορούσε να αποδειχθεί πολύτιμη.
