συναρτήσεις – πίνακες ή Ctrl+Shift+Enter συναρτήσεις

Το σημερινό σημείωμα είναι για όσους από τους φίλους του ιστολογίου δεν είναι εξοικειωμένοι με τις “συναρτήσεις – πίνακες” ή “συναρτήσεις Ctrl+Shift+Enter” όπως αλλιώς είναι γνωστές. Θα επιχειρήσω με όσο το δυνατό λιγότερα λόγια να περιγράψω τις συναρτήσεις αυτές και τον τρόπο που εισάγονται στο φύλλο. Στα παραδείγματα θα χρησιμοποιήσω την συνάρτηση LARGE η οποία αποδίδει την ν-οστή μεγαλύτερη τιμή σε ένα σύνολο αριθμητικών δεδομένων.
Αν στην περιοχή A1:C40 υπάρχουν αριθμητικά δεδομένα τότε η συνάρτηση: =LARGE(A1:C40;1) θα επιστρέψει τον μεγαλύτερο αριθμό της περιοχής, η =LARGE(A1:C40;2) τον δεύτερο μεγαλύτερο, η =LARGE(A1:C40;3) τον τρίτο μεγαλύτερο κλπ.
Προσέξτε, τώρα, την παρακάτω συνάρτηση:
=LARGE(A1:C40;{1;2;3})
Φαίνεται σαν να ζητάμε από την συνάρτηση LARGE να επιστέψει και τους τρεις μεγαλύτερους αριθμούς της περιοχής A1:C40. Είναι λογικό αυτό; Είναι λογικό να ζητάμε από μία συνάρτηση να επιστρέψει τρεις διαφορετικούς αριθμούς; Ας δούμε. Γράψτε την συνάρτηση σε ένα κελί. Θα σας επιστέψει έναν αριθμό, όχι τρεις, θα επιστρέψει τον μεγαλύτερο της περιοχής A1:C40.
Τώρα, κάντε κλικ στο κελί με την συνάρτηση (επιλέξτε το), πατήστε F2 και μετά πατήστε F9 (μην πατάτε Enter). Θα δείτε κάτι παρόμοιο με την παρακάτω εικόνα:

Βλέπετε τι πραγματικά θέλει να επιστρέψει η συνάρτηση, θέλει να επιστρέψει τρεις αριθμούς, αρκεί να της δώσουμε χώρο (κελιά) και να την εισάγουμε κατάλληλα.
Πατήστε Esc για να φύγετε από την κατάσταση επεξεργασίας(F2) και υπολογισμού (F9).
Επιλέξτε το κελί στο οποίο έχετε γράψει τη συνάρτηση και τα δύο επόμενα δεξιά κελιά, όπως στην εικόνα:

Πατήστε F2 και μετά πατήστε Ctrl+Shift+Enter.
Πράγματι, η συνάρτηση επιστρέφει τους τρεις μεγαλύτερους αριθμούς της περιοχής A1:C40 σε 3 συνεχόμενα κελιά μιας γραμμής:

Αυτή που μόλις γράψατε σε 3 κελιά είναι μια “συνάρτηση πίνακας πολλών κελιών”
Το excel αυτόματα περιβάλλει την συνάρτηση σε άγκιστρα: {=LARGE(A1:C40;{1;2;3})}, όπως βλέπετε στην γραμμή των τύπων.
Τα εξωτερικά άγκιστρα δεν μπορείτε να τα βάλετε μόνοι σας. Μπαίνουν αυτόματα μόλις πατήσετε Ctrl+Shift+Enter.
Τα τρία κελιά που χρειάστηκε η συνάρτηση, αποτελούν μια παρέα, μια ενότητα και δεν μπορείτε να διαγράψετε ένα από αυτά. Όπως δεν μπορείτε να διαγράψετε τις στήλες των κελιών αυτών. Αν το επιχειρήσετε θα πάρετε το παρακάτω μήνυμα:

Αν θέλετε να κάνετε κάποια αλλαγή ή διόρθωση στη συνάρτηση, πρέπει πρώτα να “διασπάσετε” την ενότητα τους: Επιλέξτε τα τρία κελιά, πατήστε F2 και μετά πατήστε Ctrl+Enter. Τα άγκιστρα γύρω της θα φύγουν – συνάρτηση δεν επιστρέφει πια σωστά αποτελέσματα – κάντε τις αλλαγές σας και εισάγετε ξανά την συνάρτηση όπως είπαμε πιο πάνω.
Μπορείτε να έχετε τους τρεις αριθμούς σε τρία συνεχόμενα κελιά μιας στήλης (κάθετα), αλλά θα χρειαστεί να συντάξετε την συνάρτηση σας ως εξής: =LARGE(A1:C40;{1\2\3}). Επιλέξτε τρία συνεχόμενα κελιά μίας στήλης, γράψτε την συνάρτηση όπως την βλέπετε και πατήστε Ctrl+Shift+Enter.
Η παρακάτω συνάρτηση:
=LARGE(A1:C40;{1;2;3\4;5;6\7;8;9\10;11;12})
θα επιστέψει τους δώδεκα μεγαλύτερους αριθμούς της περιοχής A1:C40 διατεταγμένους σε 4 γραμμές και 3 στήλες. Γράψτε τη συνάρτηση αυτή, ας πούμε, στο κελί J1. Επιλέξτε την περιοχή J1:L4, πατήστε F2 και μετά πατήστε Ctrl+Shift+Enter. Θα πάρετε μια εικόνα παρόμοια με αυτή:

Ανακεφαλαίωση:
Για να εισάγουμε μία “συνάρτηση – πίνακα πολλών κελιών”
Επιλέγουμε τα κελιά.
Γράφουμε στο ενεργό (πρώτο) κελί την συνάρτηση.
Πατάμε Ctrl+Shift+Enter
ή
Γράφουμε ή αντιγράφουμε στο πρώτο κελί της περιοχής την συνάρτηση.
Επιλέγουμε την περιοχή.
Πατάμε F2 και μετά πατάμε Ctrl+Shift+Enter

Παρατήρηση: Οι παραπάνω σύνταξη των συναρτήσεων ισχύει για τις συνηθισμένες στην Ελλάδα ρυθμίσεις στα settings του υπολογιστή, όπου το διαχωριστικό των ορισμάτων των συναρτήσεων είναι το ελληνικό ερωτηματικό (;). Αν σαν διαχωριστικό των ορισμάτων έχετε το κόμμα (,) τότε οι παραπάνω συναρτήσεις πρέπει να γραφούν:
=LARGE(A1:C40,{1;2;3}) για επιστροφή των αριθμών σε γραμμή (οριζόντια).
=LARGE(A1:C40,{1,2,3}) για επιστροφή των αριθμών σε στήλη (κάθετα).
=LARGE(A1:C40,{1;2;3,4;5;6,7;8;9,10;11;12}) για επιστροφή των αριθμών σε ορθογώνια περιοχή.
Συναρτήσεις πίνακα πολλών κελιών, είναι και κάποιες από τις ενσωματωμένες συναρτήσεις του excel, πιο γνωστή η συνάρτηση TRANSPOSE για τον μετασχηματισμό μιας περιοχής, όπως και οι μαθηματικές συναρτήσεις MINVERSE και MMULT και άλλες.
Διασκεδαστική άσκηση:  Σε 24 συνεχόμενα κελιά μια στήλης (πχ Α1:Α24) κάντε εισαγωγή της Ctrl+Shift+Enter συνάρτησης:
=CHAR(ROW(INDIRECT("1:24"))+224+(ROW(INDIRECT("1:24"))>17))
Θα εκπλαγείτε από το αποτέλεσμα.
Σε ένα κελί γράψτε, τώρα, γράψτε τη συνάρτηση: =SUM(LARGE(A1:C40;{1\2\3}))
Η συνάρτηση αυτή αναζητά τις 3 μεγαλύτερες τιμές της περιοχής A1:C40, και των αριθμών αυτών επιστρέφει το άθροισμα. Αυτή είναι μία απλή συνάρτηση και όχι συνάρτηση – πίνακας. Η LARGE με τη βοήθεια του ορίσματος {1\2\3} επέστρεψε (εσωτερικά) τους τρεις μεγαλύτερους αριθμούς, η SUM τους πρόσθεσε και επέστρεψε το αποτέλεσμα στο κελί. Το ίδιο αποτέλεσμα θα παίρναμε και με την:
=SUM(LARGE(A1:C40;{1;2;3})). Και αν θέλαμε τη μέση τιμή των τριών αυτών αριθμών: =AVERAGE(LARGE(A1:C40;{1\2\3})) ή =AVERAGE(LARGE(A1:C40;{1;2;3})).
Υποθέστε, τώρα, ότι θέλετε να σχεδιάσετε μια συνάρτηση που θα δώσει το άθροισμα των 50 μεγαλύτερων αριθμών της περιοχής A1:C40. Δεν είναι καθόλου πρακτικό να γράψουμε ένα όρισμα σαν αυτό: {1\2\3\4\5\6\7\8\……..\47\48\49\50}. Ευτυχώς υπάρχουν συναρτήσεις που μπορούν να αντικαταστήσουν το τεράστιο αυτό όρισμα. Παράδειγμα η συνάρτηση: =ROW(INDIRECT(«1:50»)) επιστρέφει ένα πίνακα διαδοχικών αριθμών από το 1 έως το 50. (Γράψτε τη συνάρτηση σε ένα κελί, και πατήστε F2 και μετά F9 για να το δείτε).
Η συνάρτηση, λοιπόν, που θα δώσει το άθροισμα
των 50 μεγαλύτερων αριθμών είναι:
=SUM(LARGE(A1:C40;ROW(INDIRECT("1:50"))))
και για τη μέση τιμή:
=AVERAGE(LARGE(A1:C40;ROW(INDIRECT("1:50"))))
Αλλά, και οι δύο αυτές συναρτήσεις για να δουλέψουν και να δώσουν σωστά αποτελέσματα πρέπει να εισαχθούν με Ctrl+Shift+Enter. Είναι “συναρτήσεις – πίνακες ενός κελιού”
Για να εισάγουμε μία “συνάρτηση – πίνακα ενός κελιού”:
Γράφουμε ή αντιγράφουμε τη συνάρτηση στο κελί.
Πατάμε F2 και μετά πατάμε Ctrl+Shift+Enter.
Μόλις εισάγουμε την συνάρτηση με Ctrl+Shift+Enter, το excel περικλείει τη συνάρτηση σε άγκιστρα {……}
Αν και οι δύο τελευταίες συναρτήσεις εισάγονται σε ένα κελί και επιστρέφουν έναν αριθμό, πρέπει να εισαχθούν σαν συναρτήσεις-πίνακες, έτσι μόνο θα γίνει υπολογισμός του εσωτερικού πίνακα-ορίσματος ROW(INDIRECT(«1:50»)).
Οι τύποι πίνακα είναι ένα πολύ ισχυρό εργαλείο στο Excel. Σας δίνουν την δυνατότητα να κάνετε πράγματα που είναι αδύνατα με τις συνηθισμένες συναρτήσεις.
Αρκετές συναρτήσεις – πίνακες ενός κελιού θα βρείτε στο excel βιβλίο Average_functions που συνοδεύει το άρθρο του ιστολογίου: Ειδικές συναρτήσεις για τη μέση τιμή.
This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , , , , , . Bookmark the permalink.

10 Responses to συναρτήσεις – πίνακες ή Ctrl+Shift+Enter συναρτήσεις

  1. Ο/Η Στέφανος λέει:

    Κύριε Βαρλάμη Γ., οι γνώσεις που παρέχετε για το EXCEL είναι εξαιρετικές. Γνωρίζετε μήπως εάν η συνάρτηση πίνακα {=LARGE(A1:C40;{1;2;3})}, στο σχετικό παράδειγμά σας, λειτουργεί σε περιβάλλον EXCEL 2000;

  2. Ο/Η vioannis λέει:

    Βεβαίως και λειτουργεί. Αν σε σένα δεν λειτουργεί, δες στο ίδιο σημείωμα την παρατήρηση για τις ρυθμίσεις στα settings του υπολογιστή.

  3. Ο/Η Νικος λέει:

    Εμένα πάντως (σε Excel 2000) δούλεψε ΚΑΘΕΤΑ βαζοντας το list seperator που εχω ορίσει σε » ; »
    Δηλαδή το =LARGE(A1:C9;{1;2;3}) δίνει σωστά αποτελέσματα σε κάθετη τριάδα και όχι σε οριζόντια.
    Μπορείτε να το εξηγήσετε αυτό;

  4. Ο/Η vioannis λέει:

    Δεν είναι παράξενο. Φαίνεται πως έχετε row separator το ελληνικό ερωτηματικό. Κάντε το τεστ που περιγράφω στο σημείωμα του ιστολογίου “Απαραίτητες διευκρινίσεις για τους διαχωριστές ορισμάτων (Separators)” για να δείτε ποιους ακριβώς διαχωριστές στήλης και γραμμής έχετε. Οι διαχωριστές εξαρτώνται από τις γενικές ρυθμίσεις του υπολογιστή όχι μόνο από την έκδοση του excel.

  5. Ο/Η vioannis λέει:

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

  6. Ο/Η apostolos orfanidis λέει:

    ΚΥΡΙΕ ΒΑΡΛΑΜΗ ΓΙΑ ΑΛΛΗ ΜΙΑ ΦΟΡΑ ΘΑ ΣΑΣ ΕΥΧΑΡΙΣΤΗΣΩ ΓΙΑ ΟΛΗ ΑΥΤΗΝ ΤΗΝ ΠΡΟΣΠΑΘΕΙΑ ΠΟΥ ΕΧΕΤΕ ΚΑΝΕΙ ΜΕΣΑ ΑΠΟ ΤΟ BLOG ΣΑΣ ΤΑ ΤΟΣΑ ΧΡΟΝΙΑ ΚΑΙ ΤΗΝ ΤΟΣΟ ΑΠΟΤΕΛΕΣΜΑΤΙΚΗ ΑΡΘΟΓΡΑΦΙΑ , ΜΕΣΑ ΑΠΟ ΠΑΡΑΔΕΙΓΜΑΤΑ ΚΑΙ ΑΠΑΝΤΩΝΤΑΣ ΣΕ ΟΛΟΥΣ ΕΜΑΣ ΤΟΥΣ ΑΠΛΟΥΣ ΧΡΗΣΤΕΣ . ΘΑ ΗΘΕΛΑ ΛΙΓΟ ΤΗΝ ΒΟΗΘΕΙΑ ΣΑΣ ΣΕ ΚΑΤΙ ..
    ΣΤΗΝ ΚΟΛΩΝΑ Α1 ΜΕ ΤΗΝ ΟΝΟΜΑΣΙΑ ΖΩΝΗ 1 ΚΑΤΑΧΩΡΩ ΧΩΡΕΣ ΤΗΣ ΔΥΤΙΚΗΣ ΕΥΡΩΠΗΣ ΟΠΩΣ ΟΛΛΑΝΔΙΑ, ΓΕΡΜΑΝΙΑ ΒΕΛΓΙΟ ΛΟΥΞΕΜΒΟΥΡΓΟ Κ.Α ΣΤΗΝ ΚΟΛΩΝΑ Β2
    ΜΕ ΤΗΝ ΟΝΟΜΑΣΙΑ ΖΩΝΗ 2 ΚΑΤΑΧΩΡΩ ΤΙΣ ΧΩΡΕΣ ΕΛΛΑΔΑ ΑΛΒΑΝΙΑ ΠΓΔΜ ΚΟΣΟΒΟ ΒΟΥΛΓΑΡΙΑ Κ.Α … ΣΤΟ ΣΥΝΟΛΟ ΤΟΥΣ ΕΙΝΑΙ 5 ΖΩΝΕΣ .

    ΣΤΗΝ ΚΟΛΩΝΑ G ΥΠΑΡΧΟΥΝ ΟΙ ΔΙΑΦΟΡΕΣ ΥΠΗΡΕΣΙΕΣ ΠΟΥ ΠΑΡΕΧΟΝΤΑΙ ΠΧ ΟΔΙΚΗ ΥΠΗΡΕΣΙΑ , ΑΕΡΟΠΟΡΙΚΗ ΜΕΤΑΦΟΡΑ , ΝΑΥΤΙΚΗ ΜΕΤΑΦΟΡΑ ΚΑ…

    ΣΤΗΝ ΚΕΛΙ Ι2 ΕΩΣ Μ20 ΥΠΑΡΧΕΙ ΠΙΝΑΚΑΣ ΜΕ ΤΑ ΕΞΗΣ ΧΑΡΑΚΤΗΡΙΣΤΙΚΑ

    ΟΡΙΖΟΝΤΙΑ ΥΠΑΡΧΟΥΝ ΟΙ ΖΩΝΕΣ ΚΑΙ ΚΑΘΕΤΑ ΒΑΡΗ ΑΝΑ ΚΙΛΟ

    ΠΑΡΑΔΕΙΓΜΑ ΤΟΥ ΠΙΝΑΚΑ ΟΔΙΚΗΣ ΜΕΤΑΦΟΡΑΣ ( ΔΕΝ ΤΟΝ ΚΑΝΩ ΣΕ ΠΛΗΡΗ ΑΝΑΠΤΥΞΗ )

    ΒΑΡΟΣ ΖΩΝΗ 1 ΖΩΝΗ 2 ΖΩΝΗ 3

    1 ΚΙΛΟ 10 12 14
    2 ΚΙΛ 15 18 21
    3 ΚΙΛ 20 23 26

    ΚΑΙ ΣΥΝΕΧΙΖΕΤΑΙ

    ΑΝΤΟΙΣΤΙΧΑ ΥΠΑΡΧΟΥΝ ΠΑΡΟΜΟΙΟΙ ΠΙΝΑΚΕΣ ΑΝΑΛΟΓΑ ΜΕ ΤHN ΥΠΗΡΕΣΙΑ ΜΕΤΑΦΟΡΑΣ

    ΘΑ ΗΘΕΛΑ ΛΟΙΠΟΝ ΕΦΟΣΟΝ ΕΠΙΛΕΞΩ ΣΤΟ ΚΕΛΙ Α40 ΤΗΝ ΧΩΡΑ ΟΛΛΑΝΔΙΑ ΣΤΟ ΚΕΛΙ Α41 … ΥΠΗΡΕΣΙΑ ΟΔΙΚΗΣ ΜΕΤΑΦΟΡΑΣ ΚΑΙ ΣΤΟ ΚΕΛΙ Α42 ΓΡΑΨΩ 2 ΚΙΛΑ ( ΤΟ ΒΑΡΟΣ ΤΟΥ ΔΕΜΑΤΟΣ ) ΝΑ ΜΟΥ ΔΩΣΕΙ ΑΠΟΤΕΛΕΣΜΑ 15 .ΟΠΩΣ ΕΙΝΑΙ ΣΤΟΝ ΠΙΝΑΚΑ

    ΣΑΣ ΕΥΧΑΡΙΣΤΩ ΠΟΛΥ.

  7. Ο/Η vioannis λέει:

    Απόστολε,
    Με βάση τη περιγραφή σου, έστησα ένα παράδειγμα. Κατέβασε το excel βιβλίο apostolosexample να το δεις.
    Στο κελί Q2 επιλέγεις το κράτος. Έχω εισάγει στο κελί αυτό επικύρωση από λίστα, όπως περιγράφεται στην πρώτη παράγραφο του σημειώματος του ιστολογίου «Επικύρωση δεδομένων από λίστα»
    Ο τύπος στο κελί Q3 επιστρέφει τον αριθμό της ζώνης (στήλης) του κράτους.
    Στο κελί Q5 επιλέγεις την υπηρεσία, και εδώ υπάρχει επικύρωση από λίστα.
    Στο κελί Q6 γράφεις το βάρος.
    Στο κελί Q8 ο τύπος επιστρέφει την τιμή που ζητάς. Είναι Ctrl+Shift+Enter τύπος και κάνει «διπλό» VLOOKUP. Θα βρεις περιγραφή αυτής της μεθόδου στην σελίδα 10 του pdf κειμένου που συνοδεύει στο σημείωμα του ιστολογίου «Ένας πρωτότυπος τρόπος χρήσης της συνάρτησης CHOOSE και οι εφαρμογές του»
    Φυσικά τους τύπους μπορείς να τους γράψεις σε όποιο σημείο του φύλλου θες, απλά πρόσεχε οι ζώνες στις 2 περιοχές κρατών και τιμών, να είναι με την ίδια σειρά. Αν κάτι δεν πάει καλά, επανέρχεσαι άφοβα.

  8. Ο/Η vioannis λέει:

    Απόστολε, διόρθωσε τον τύπο στο κελί Q3:
    =SUMPRODUCT((A2:E20=Q2)*COLUMN(A2:E20))

  9. Ο/Η apostolos orfanidis λέει:

    ΔΕΝ ΥΠΑΡΧΟΥΝ ΛΟΓΙΑ ΓΙΑ ΝΑ ΣΑΣ ΕΥΧΑΡΙΣΤΗΣΩ. ΔΕΝ ΣΑΣ ΑΠΑΝΤΗΣΑ ΑΜΕΣΑ ΟΧΙ ΓΙΑΤΙ ΔΕΝ ΛΕΙΤΟΥΡΓΗΣΕ Η ΣΥΝΑΡΤΗΣΗ ΑΛΛΑ ΓΙΑΤΙ ΧΑΘΗΚΑ ΣΤΟ ΠΩΣ ΑΥΤΗ ΚΑΤΑΣΚΕΥΑΣΤΗΚΕ, ΜΕ ΤΟΥΣ ΚΑΝΟΝΕΣ ΕΠΙΚΥΡΩΣΗΣ ΚΑΙ ΟΛΗ ΤΗΝ ΥΠΟΛΟΙΠΗ ΘΕΜΑΤΟΛΟΓΙΑ ΠΟΥ ΥΠΟΣΤΗΡΙΖΕΙ. ΚΑΙ ΠΑΛΙ ΕΥΧΑΡΙΣΤΩ ΓΙΑ ΟΛΑ.

  10. Ο/Η vioannis λέει:

    Απόστολε, πάντα ευπρόσδεκτος. Κρατάω τον αριθμό. Να είσαι καλά.

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