Ένας πρωτότυπος τρόπος χρήσης της συνάρτησης CHOOSE και οι εφαρμογές του

Η συνάρτηση φύλλου του Excel,  =CHOOSE(index_num ; value1 ; value2; …)
χρησιμοποιείται για να αποδώσει μια τιμή μεταξύ των value1, value2, value3, …, βάσει του ορίσματος index_num το οποίο παίρνει τιμές 1,2,3…29
Παράδειγμα, η συνάρτηση CHOOSE(3 ;“a”;“b”;“c”;“d”) θα επιστρέψει “c”.
Αν όμως το όρισμα  index_num αντί να είναι ένας μεμονωμένος ακέραιος, είναι ένας πίνακας ακέραιων τιμών(πχ {1;2;3} ή {1\2\3\4} ή ({1;2\3;4\5;6}), τα δε ορίσματα value είναι και αυτά πίνακες μιας στήλης ή μιας γραμμής ενός φύλλου,  ή πίνακες γραμμής ή στήλης που προκύπτουν από άλλες συναρτήσεις, τότε, η CHOOSE επιστρέφει ένα πίνακα m x n.
Με τον τρόπο μπορούμε:
–Να αναδιατάξουμε τις στήλες ή τις γραμμές ενός πίνακα.
–Να συμπληρώσουμε τον πίνακα με νέες στήλες ή γραμμές ή υπολογιστικά πεδία.
–Να δημιουργήσουμε πίνακα από διάσπαρτες στήλες ή και γραμμές μέσα σε ένα φύλλο ή σε διαφορετικά φύλλα ή σε διαφορετικά βιβλία.
Συνδυάζοντας την δυνατότητα αυτή της CHOOSE με την συνάρτηση VLOOKUP  ( ή την HLOOKUP) αυξάνουμε τις δυνατότητες της VLOOKUP μπορούμε να κάνουμε: «Αριστερό» VLOOKUP,  VLOOKUP μεταξύ στηλών (ή και γραμμών) οι οποίες δεν συγκροτούν πίνακα και οι οποίες βρίσκονται στο ίδιο φύλλο ή σε διαφορετικά φύλλα ή σε άλλα  βιβλία και άλλα.
Ας ξεκινήσουμε με ένα παράδειγμα:
Στο φύλλο υπάρχουν 3 περιοχές με δεδομένα:
Οι στήλες A1:A5 και C3:C7 και η γραμμή E1:I1.
Σε μία περιοχή 5 γραμμών και 3 στηλών (5R x 3C) και συγκεκριμένα στην περιοχή E6:G10 εισάγουμε την συνάρτηση:
{=CHOOSE({1;2;3};A1:A5;C3:C7;TRANSPOSE(E1:I1))}
Η συνάρτηση δημιουργεί ένα πίνακα 5×3.
Το όρισμα index_num = {1;2;3} καθορίζει το πλήθος των στηλών του πίνακα και την σειρά εμφάνισης των στηλών (από αριστερά προς τα δεξιά).
Πρώτη στήλη θα είναι το όρισμα  value1= A1:A5,
Δεύτερη στήλη θα είναι το όρισμα value2=C3:C7
και τρίτη στήλη θα είναι το όρισμα value3=TRANSPOSE(E1:I1)
Τα ορίσματα value μπορούν να είναι σε διαφορετικά φύλλα ή διαφορετικά ανοιχτά ή κλειστά βιβλία.
Η συνάρτηση ενημερώνεται από κλειστά βιβλία.
Παράδειγμα,η συνάρτηση:
{=CHOOSE({1;2};sheet2!A1:A10;'C:\...\...\[Book2.xls]sheet1'!$A$1:$A$10)}
 θα επιστρέψει ένα πίνακα 10R x 2C με πρώτη στήλη τα στοιχεία της  στήλης Α1:Α10 του sheet2 αυτού του βιβλίου και δεύτερη στήλη τα στοιχεία της στήλης Α1:Α10 του sheet1 του βιβλίου Book2.
Στο επόμενο παράδειγμα από τα δεδομένα μιας στήλης (Α4:Α11) δημιουργούμε ένα πίνακα 4 στηλών.
 
Στην περιοχή C1:F8 εισάγουμε την συνάρτηση:
{=CHOOSE({1;2;3;4};ROW(INDIRECT("1:8"));UPPER(LEFT(A4:A11;3));A4:A11;ADDRESS(ROW(A4:A11);COLUMN(A4:A11)))}
–Το όρισμα value1: ROW(INDIRECT(“1:8”)) δημιουργεί μία αρίθμηση των γραμμών.
–Το όρισμα value2: UPPER(LEFT(A4:A11;3)) δημιουργεί ένα κωδικό 3 ψηφίων για κάθε χώρα.
–Το όρισμα value3: A4:A11 είναι τα δεδομένα στοιχεία και τέλος
–Το όρισμα value4: ADDRESS(ROW(A4:A11);COLUMN(A4:A11)) δίνει τις  διευθύνσεις των δεδομένων.
Είπαμε παραπάνω ότι το όρισμα  index_num = {1;2;3;4;…} καθορίζει το πλήθος των στηλών του πίνακα αλλά και την σειρά εμφάνισης των στηλών.
Έτσι, για να πάρουμε τον παραπάνω πίνακα με πρώτη τη στήλη των χωρών, δηλαδή στη μορφή:

ή θα γράψουμε στον παραπάνω τύπο σαν πρώτο όρισμα (value1) : A4:A11 και θα ακολουθήσουν τα άλλα ορίσματα value, ή απλώς θα αλλάξουμε στο όρισμα index_num την σειρά των αριθμών  ως εξής: {3;1;2;4} .
Γενικεύοντας , η συνάρτηση CHOOSE στην παρακάτω σύνταξη θα επιστρέψει ένα πίνακα m x n  (n<30)
{=CHOOSE({1;2;3;...;n};colomn1;column2;...;TRANSPOSE(row1);TRANSPOSE(row2);...;Function1; Function2;...}
ΑΝΑΔΙΑΤΑΞΕΙΣ ΠΙΝΑΚΑ  – ΑΝΑΔΙΑΤΑΞΗ ΣΤΗΛΩΝ
Στο παράδειγμα αυτό, ο πίνακας στην περιοχή A3:D9 στον οποίο έχει δοθεί το όνομα ‘array’, θα αναδιαταχθεί (θα γίνει δηλαδή αλλαγή στη σειρά των στηλών) στη μορφή που φαίνεται στην περιοχή G3:J9. Στις στήλες του πίνακα ‘array’, έχουν δοθεί τα ονόματα col1, col2, col3, cl4 αντίστοιχα.
 Ο τύπος που δημιούργησε τον αναδιατεταγμένο πίνακα είναι:
{=CHOOSE({1;2;3;4};col4;col2;col1;col3)}
Το ίδιο αποτέλεσμα δίνει ο τύπος:
{=CHOOSE({4;2;1;3};col1;col2;col3;col4)}
ή ο τύπος:
{=CHOOSE((A11:D11);col1;col2;col3;col4)}
Στον τελευταίο τύπο τη σειρά εμφάνισης των στηλών καθορίζουν οι αριθμοί που έχουν γραφεί στην περιοχή A11:D11
Η λογική της σειράς των αριθμών 4,2,1,3 είναι η εξής:
–Πρώτη στήλη στον νέο πίνακα θα είναι η 4η του αρχικού πίνακα (value4)
–Δεύτερη στήλη στον νέο πίνακα θα είναι η 2η του αρχικού πίνακα (value2)
–Τρίτη στήλη στον νέο πίνακα θα είναι η 1η του αρχικού πίνακα (value1)
–Τέταρτη στήλη στον νέο πίνακα θα είναι η 3η του αρχικού πίνακα (value3)
Αν έχετε κάποιο λόγο να αναφέρεστε απευθείας στον αρχικό πίνακα (array) μπορείτε να χρησιμοποιήσετε την παρακάτω σύνταξη:
{=CHOOSE({4;2;1;3};INDEX(array;0;1);INDEX(array;0;2);INDEX(array;0;3);INDEX(array;0;4))}
ΑΝΑΔΙΑΤΑΞΕΙΣ ΠΙΝΑΚΑ  – ΑΝΑΔΙΑΤΑΞΗ ΓΡΑΜΜΩΝ
Εντελώς ανάλογα γίνεται και η αναδιάταξη γραμμών ενός πίνακα.
Στο παράδειγμα οι γραμμές του πίνακα C2:E5 (array_b) οι οποίες έχουν ονομαστεί row1, row2, row3, row4 αντίστοιχα έχουν αναδιαταχθεί και δημιούργησαν τον πίνακα I3:K6
Αυτό έγινε με μία από τις παρακάτω συναρτήσεις:
{=CHOOSE({1\2\3\4};row2;row4;row1;row3)}
ή {=CHOOSE({2\4\1\3};row1;row2;row3;row4)}
ή  {=CHOOSE(G2:G5;row1;row2;row3;row4)}
ή {=CHOOSE({2\4\1\3};INDEX(array_b;1;0);INDEX(array_b;2;0);INDEX(array_b;3;0);INDEX(array_b;4;0))}
ΔΗΜΙΟΥΡΓΙΑ ΠΙΝΑΚΑ ΑΠΟ ΜΕΜΟΝΩΜΕΝΑ ΚΕΛΙΑ.
Στο παράδειγμα που ακολουθεί, έχουμε δημιουργήσει πίνακες διαφόρων διαστάσεων από τιμές διάσπαρτες σε ένα φύλλο.

Η συνάρτηση:  {=CHOOSE({1;2;3;4;5;6};D2;B2;E3;C4;A4;E1)}
επιστρέφει ένα πίνακα – γραμμή
Η συνάρτηση:  {=CHOOSE({1\2\3\4\5\6};D2;B2;E3;C4;A4;E1)}
επιστρέφει ένα πίνακα – στήλη
Η συνάρτηση: {=CHOOSE({1;2;3\4;5;6};D2;B2;E3;C4;A4;E1)}
Επιστρέφει ένα πίνακα  2Rx3C
Η συνάρτηση: {=CHOOSE({1;2\3;4\5;6};D2;B2;E3;C4;A4;E1)}
επιστρέφει ένα πίνακα 3Rx2C
Τα διάσπαρτα κελιά μπορούν να βρίσκονται και σε διαφορετικά φύλλα, σε άλλα  βιβλία ή και να είναι σταθερές τιμές. Παράδειγμα, ο τύπος:
{=CHOOSE({1;2;3;4};A1;Sheet2!A1;'C:\examples\[book1.xls]Sheet1'!$A$1;"c")}
επιστέφει ένα πίνακα – γραμμή τεσσάρων στοιχείων τα οποία βρίσκονται:
–στο κελί Α1 αυτού του φύλλου,
–στο κελί Α1 του φύλλου Sheet2 αυτού του βιβλίου,
–στο κελί Α1 του φύλλου Sheet1 του κλειστού βιβλίου book1 το οποίο είναι αποθηκευμένο στο C:\examples
–και το τέταρτο στοιχείο είναι η σταθερή τιμή ‘c’.
ΕΦΑΡΜΟΓΕΣ
Όσα έχουν αναφερθεί ως τώρα αποκτούν μεγαλύτερη χρησιμότητα όταν ο επιστρεφόμενος από την CHOOSE πίνακας χρησιμοποιηθεί σαν όρισμα από μια άλλη συνάρτηση όπως από τις VLOOKUP, HLOOKUP, INDEX, MATCH, MDETERM κλπ. Δεν υπάρχουν, φαντάζομαι, πολλές περιπτώσεις όπου η αναδιάταξη ενός πίνακα ή η συγκρότηση ενός πίνακα από διάσπαρτες στήλες θα ήταν χρήσιμη από μόνη της, αλλά γίνεται ένα χρήσιμο εργαλείο σε συναρτήσεις αναζήτησης .
Τα παραδείγματα που θα ακολουθήσουν θα δείξουν ένα μέρος των δυνατοτήτων αυτών.

‘ΑΡΙΣΤΕΡΟ’ VLOOKUP
Στη συνάρτηση:

VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)
το πρώτο όρισμα πρέπει να είναι μία τιμή από την πρώτη στήλη του δεύτερου ορίσματος. Αλλά αν για όρισμα table_array χρησιμοποιήσουμε ένα αναδιατεταγμένο πίνακα μπορούμε να κάνουμε αναζήτηση από οποιαδήποτε στήλη.

Στο παράδειγμα, εισάγαμε στο κελί D1 την lookup_value = Denmark, για όρισμα table_array χρησιμοποιήσαμε τον αναδιατεταγμένο πίνακα:
CHOOSE({1;2};B1:B10;A1:A10)
και σωστά η συνάρτηση:
=VLOOKUP(D1;CHOOSE({1;2};B1:B10;A1:A10);2;FALSE)
στο κελί D5  επέστρεψε DEN . Το ίδιο αποτέλεσμα θα δώσει και η ισοδύναμη συνάρτηση:
=VLOOKUP(D1;CHOOSE({2;1};A1:A10;B1:B10);2;FALSE)
Η συγκρότηση του αναδιατεταγμένου πίνακα γίνεται βέβαια στην μνήμη του υπολογιστή και όχι στο φύλλο.
VLOOKUP ΜΕ ΔΕΔΟΜΕΝΑ ΠΟΥ ΔΕΝ ΕΙΝΑΙ ΣΕ ΠΙΝΑΚΑ
Στο επόμενο παράδειγμα έγινε αναζήτηση μεταξύ των δεδομένων μιας στήλης και μιας γραμμής.
 Στο κελί των αποτελεσμάτων C8 υπάρχει ο τύπος :
{=VLOOKUP(C4;CHOOSE({1;2};A4:A13;TRANSPOSE(A2:J2));2;0)}
Όμοια, ο παρακάτω τύπος θα αναζητήσει μία τιμή στην στήλη Α του φύλλου Sheet1 και θα επιστρέψει την αντίστοιχη τιμή από τη στήλη Α του φύλλου Sheet2. Φυσικά ο τύπος θα έχει εισαχθεί σε ένα τρίτο φύλλο του ίδιου βιβλίου.
{=VLOOKUP(D1;CHOOSE({1;2};Sheet1!A1:A65535;Sheet2!A1:A65535);2;FALSE)}
Αναζήτηση μπορεί να γίνει και μεταξύ δεδομένων τα οποία βρίσκονται αποθηκευμένα σε διαφορετικά βιβλία. Ο τύπος που ακολουθεί αναζητά την τιμή του κελιού D1 στην περιοχή Α1:Α2000 του φύλλου Sheet1 του  βιβλίου book1 και επιστρέφει την αντίστοιχη τιμή από την περιοχή C100:C2099 του φύλλου Sheet1 του βιβλίου book2.
{=VLOOKUP(D1;CHOOSE({1;2};
'C:\examples\[book1.xls]Sheet1'!$A1:$A2000;
'C:\examples\[book2.xls]Sheet1'!$C100:$C2099);2;FALSE)}
 Αν σας ενδιαφέρει να διαβάσετε μια πιο αναλυτική παρουσίαση με πιο πολλές εφαρμογές και παραδείγματα, κατεβάστε το PDF αρχειο : CHOOSE and VLOOKUP in excel
Η χρήση της CHOOSE σαν συνάρτηση που επιστέφει πίνακα, προτάθηκε το 2005 από τον γράφοντα στις αγγλόφωνες ομάδες συζήτησης για το excel. (25/7/2005 και 6/10/2005 ). Από όσο γνωρίζω αυτή ήταν η πρώτη φορά παρουσίασης της ιδιότητας αυτής της CHOOSE . Οι συζητήσεις αυτές έχουν αναπαραχθεί πολλές φορές στο δίκτυο όπως μπορείτε να δείτε εδώ, εδώ, εδώεδώ εδώ  και  εδώ, εδώ, εδώ, εδώ, εδώ
Πρόσφατα είδα η μέθοδος να προτείνετε και από άλλους, όπως εδώ.
This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , , , . Bookmark the permalink.

One Response to Ένας πρωτότυπος τρόπος χρήσης της συνάρτησης CHOOSE και οι εφαρμογές του

  1. Ο/Η vioannis λέει:

    Πιθανά, οι τύποι του που αναφέρονται παραπάνω, να δείχνουν ότι δεν δουλεύουν στο δικός σας υπολογιστή. Ίσως στο δικό σας excel, να έχετε διαφορετικούς διαχωριστές σταθερών πινάκων και θα πρέπει να προσαρμόσετε ανάλογα τους τύπους. Συμβουλευτείτε το σχετικό σημείωμα του ιστολογίου, «Απαραίτητες διευκρινίσεις για τους διαχωριστές ορισμάτων (Separators)»

Τα σχόλια είναι απενεργοποιημένα.