Χρήση της συνάρτησης SUMPRODUCT για άθροιση υπό όρους.

Η ιστορία που θα σας διηγηθώ έχει αρχή, μέση και τέλος. Άχαρη αρχή και όχι πολύ ενδιαφέρουσα μέση, αλλά αν έχετε υπομονή να φτάσετε ως το τέλος, πιστεύω ότι θα αποζημιωθείτε.

SUMPRODUCTimage01
Στις 2 πρώτες στήλες του πίνακα της εικόνας υπάρχουν πόλεις και αντίστοιχες ποσότητες. Θέλω να βρω το άθροισμα των ποσοτήτων που αντιστοιχούν σε μία από αυτές, ας πούμε στα Χανιά. Φυσικά, γι’ αυτό υπάρχει η συνάρτηση SUMIF. Εισάγω σε ένα κελί:

=SUMIF(A2:A26;"Χανιά";B2:B26)

και η συνάρτηση, μου επιστρέφει σωστά, 20410. Αλλά, για να λύσω το πρόβλημα θα ακολουθήσω έναν άλλο, λίγο τρελό, δρόμο. Στο κελί C2 εισάγω τον τύπο:

=A2="Χανιά"

και τον αντιγράφω έως το τέλος του πίνακα. Ο τύπος αυτός ή θα επαληθεύεται ή όχι και θα επιστρέψει TRUE σε όσα κελιά επαληθεύεται, όπως στα C5, C7, C10 κλπ που αντιστοιχούν στα Χανιά, και FALSE σε όλα τα άλλα.
Οι λογικές τιμές TRUE και FALSE έχουν όμως μία χρήσιμη ιδιότητα: Μετατρέπονται, αυτόματα, στους αριθμούς 1 και 0 αντίστοιχα, όταν τους πολλαπλασιάσετε με έναν οποιαδήποτε αριθμό. Για να το δείτε αυτό, γράψτε σε δυο κελιά τους τύπους
=TRUE*5
=FALSE*5
Θα πάρετε 5 και μηδέν αντίστοιχα. Ο τύπος =TRUE*5 μετατράπηκε σε =1*5 και επέστρεψε 5 και ο τύπος  =FALSE*5 μετατράπηκε σε =0*5=0.
Το ίδιο θα συμβεί αν πολλαπλασιάσετε δυο λογικές τιμές:
=TRUE*TRUE θα επιστρέψει =1*1=1
=TRUE*FALSE θα επιστρέψει =1*0=0
=FALSE*FALSE θα επιστρέψει =0*0=0
Έχοντας αυτή την ιδιότητα κατά νου, γυρίζω στον πίνακά μου και συμπληρώνω μια ακόμα στήλη. Στο κελί D2 γράφω τον τύπο: =B2*C2 και φυσικά τον αντιγράφω έως το τέλος του πίνακα. Ο τύπος αυτός πολλαπλασιάζει τις ποσότητες της στήλης Β με τις λογικές τιμές της στήλης C, και με όσα είπαμε μόλις παραπάνω, όπου συναντά FALSE (δηλαδή μηδέν) θα επιστρέψει μησέν και όπου συναντά TRUE (δηλαδή 1) θα επιστέψει την αντίστοιχη ποσότητα. Για να βρω λοιπόν αυτό που ξεκίνησα, το άθροισμα δηλαδή των ποσοτήτων που αντιστοιχούν στα Χανιά, δεν έχω παρά να προσθέσω όλες τις τιμές της στήλης D, πράγμα που κάνω στο κελί D27 και παίρνω επιτέλους το ποθούμενο αποτέλεσμα 20410.

Θα μου πείτε, και με το δίκιο σας: Για ένα τόσο απλό αποτέλεσμα, τόσο μεγάλη φασαρία, με δύο βοηθητικές στήλες γεμάτες με τύπους; Η απάντηση είναι ότι δεν χρειάζονται οι βοηθητικές στήλες C και D και όλη η δουλειά μπορεί να γίνει πιο απλά. Γράψτε σε ένα κελί, όποιο θέλετε, τον τύπο:

=A2:A26="Χανιά"

Αγνοείστε τι επιστέφει ο τύπος στο κελί, επιλέξτε το, πατήστε F2 και αμέσως μετά F9. (Μην πατάτε Enter). Θα δείτε μια εικόνα σαν και αυτή:

SUMPRODUCTimage02
Αυτό πραγματικά επιστρέφει ο τύπος! (Την ίδια εικόνα μπορείτε να δείτε και στην γραμμή των τύπων). Παρατηρήστε ότι είναι τα ίδια αποτελέσματα της στήλη C. Στην πραγματικότητα, λοιπόν, ο τύπος =A2:A26=»Χανιά» είναι ένας τύπος-πίνακας που επιστρέφει ακριβώς ότι και η C. Πατήστε Escape για να φύγετε από την κατάσταση υπολογισμού (F9) και διόρθωσης (F2). Στη συνέχεια τροποποιήστε τον τύπο ως εξής:

=(A2:A26="Χανιά")*(B2:B26)

Κάντε και για τον τύπο αυτό, τη μέθοδο F2-F9. Θα δείτε μια εικόνα σαν και αυτή:

SUMPRODUCTimage03
Ο τελευταίος αυτός τύπος-πίνακας πολλαπλασιάζει τις προηγούμενες λογικές τιμές, με τις ποσότητες της στήλης Β, ότι κάναμε δηλαδή στην στήλη D, γι’ αυτό επιστρέφει τα ίδια ακριβώς αποτελέσματα με την D.
Επόμενη λογική σκέψη είναι να αθροίσετε τα αποτελέσματα που δίνει ο τελευταίος τύπος-πίνακας με τη SUM. Τροποποιήστε, λοιπόν, τον τύπο ως εξής:

=SUM((A2:A26="Χανιά")*(B2:B26))

Όχι, δεν επιστρέφει το ποθούμενο αποτέλεσμα. Ανάλογα σε ποιο κελί την έχετε γράψει, επιστέφει έναν κάποιο αριθμό, πιθανά μηδέν ή το σφάλμα. #ΤΙΜΗ!  Αυτό συμβαίνει διότι το όρισμα (A2:A26=»Χανιά»)*(B2:B26) της SUM είναι όπως είπαμε ένας τύπος-πίνακας, άρα η συνάρτηση πρέπει να εισαχθεί σαν τύπος- πίνακας. Επιλέξτε ξανά τι κελί με τη SUM, πατήστε F2 και μετά πατήστε Ctrl+Shift+Enter. Τώρα, επιτέλους, έχετε το σωστό αποτέλεσμα 20410. (Περισσότερα για τις συναρτήσεις – πίνακες ή Ctrl+Shift+Enter συναρτήσεις δέστε το σχετικό σημείωμα του ιστολογίου εδώ).  Άρα, οι δύο βοηθητικές στήλες C και D, δεν χρειάζονται, μπορούν όλοι οι υπολογισμοί να γίνουν στην μνήμη του υπολογιστή από τα ορίσματα-πίνακες της συνάρτησης SUM.

Όσοι είχατε την υπομονή να διαβάσετε ως εδώ, ίσως σκέπτεστε ότι άδικα σας παιδεύω, και ότι η αρχική λύση με την SUMIF πλεονεκτεί σε απλότητα. Δεν έχετε άδικο, αλλά ακόμα είμαστε στο μέσο της ιστορίας μας.

.
Όπου εμφανίζεται η ηρωίδα της ιστορίας μας, η SUMPRODUCT

xena
Μια από τις πιο παλιές ενσωματωμένες συναρτήσεις του excel, παλιά όσο και η SUM, είναι και η SUMPRODUCT. Αν οι στήλες Α1:Α100 και Β1:Β100 περιέχουν τιμές, η

 
=SUMPRODUCT(A1:A100;B1:B100)

θα πολλαπλασιάσει τις αντίστοιχες τιμές των δύο στηλών, και θα επιστρέψει το άθροισμα των γινομένων, δηλαδή θα επιστρέψει Α1*Β1+Α2*Β2+Α3*Β3+…….
Γενικότερα η σύνταξή της είναι:

SUMPRODUCT(πίνακας1; πίνακας2; πίνακας3; ...)

όπου οι πίνακες μπορεί να είναι γραμμές, στήλες, ορθογώνιες περιοχές ή πίνακες που προκύπτον από άλλες συναρτήσεις, αρκεί όλοι να είναι με τις ίδιες διαστάσεις και να αποτελούνται από αριθμητικές τιμές. Η συνάρτηση θα πολλαπλασιάσει όλα τα αντίστοιχα στοιχεία των πινάκων μεταξύ τους, θα προσθέσει όλα αυτά τα γινόμενα και θα επιστρέψει το τελικό άθροισμα. (Δέστε και το σχετικό λήμμα στη βοήθεια της Microsoft)

Προσέξτε ότι η SUMPRODUCT δεν θα διαμαρτυρηθεί αν τις δώσετε για όρισμα μία μόνο στήλη ή ένα πίνακα. Απλά, θα επιστρέψει το άθροισμα των στοιχείων της στήλης ή του πίνακα, θα κάνει δηλαδή ότι και η SUM. Δοκιμάστε, λοιπόν, την SUMPRODUCT αντί για την SUM για να αθροίσετε τον πίνακα (A2:A26=»Χανιά»)*(B2:B26). Γράψτε δηλαδή σε ένα κελί τον τύπο:

=SUMPRODUCT((A2:A26="Χανιά")*(B2:B26))

Θα δείτε να επιστρέφει το ζητούμενο αποτέλεσμα 20410, χωρίς να χρειάζεται να την εισάγεται με Ctrl+Shift+Enter. Αυτό οφείλετε στο ότι SUMPRODUCT από το σχεδιασμό της είναι φτιαγμένη να χειρίζεται πίνακες.
Η μέθοδος της άθροισης υπό όρους με τη βοήθεια της SUMPRODUCT, αρχίζει και αποκτά πλεονεκτήματα, όταν οι όροι γίνονται πιο σύνθετοι. Για να υπολογίσετε, στον πίνακα της πρώτης εικόνας, το άθροισμα των ποσοτήτων που αντιστοιχούν στα Χανιά και ταυτόχρονα είναι ποσότητες μεγαλύτερες από 2000 θα χρειαστείτε τον τύπο:

 
=SUMPRODUCT((A2:A26="Χανιά")*(B2:B26>2000)*(B2:B26))

Ο πίνακας (A2:A26=»Χανιά») επιστρέφει τα αποτελέσματα της στήλης C.
Ο πίνακας (B2:B26>2000) επιστρέφει τα αποτελέσματα της στήλης E.
To γινόμενο των δύο αυτών πινάκων, θα δώσει τα αποτελέσματα της στήλης F. (Προσέξτε ότι 1 υπάρχει μόνο όπου ταυτόχρονα η πόλη είναι Χανιά και το ποσό μεγαλύτερο του 2000). Ο τελικός πολλαπλασιασμός με τη στήλη (B2:B26) θα επιστρέψει τα αποτελέσματα της στήλης G. Η SUMPRODUCT, τέλος, θα αθροίσει και θα επιστρέψει σωστά 19313.

.
Ένα ακόμα παράδειγμα από τον πιο κάτω πίνακα:

SUMPRODUCTimage04
Θέλουμε το άθροισμα των ποσοτήτων της Θεσσαλονίκης μόνο για το έτος 2013 και μόνο αυτών που είναι μεταξύ 1000 και 2000. Ξεκινάμε:

Πρώτα η πόλη: (A2:A100="Θεσσαλονίκη")
Μετά η χρονιά: (YEAR(B2:B100)=2013)
Ποσά μεγαλύτερα ή ίσα του 1000: (C2:C100>=1000)
Ποσά μικρότερα ή ίσα του 2000:(C2:C100<=2000)

Οι τέσσερις αυτοί πίνακες περιέχουν λογικές τιμές TRUE και FALSE και αν πολλαπλασιαστούν μεταξύ τους:

=(A2:A100="Θεσσαλονίκη")*(YEAR(B2:B100)=2013)*(C2:C100>=1000)*(C2:C100<=2000)

θα επιστρέφουν ένα πίνακα που θα έχει 1 στις θέσεις (γραμμές) που επαληθεύουν και τους τέσσερις όρους που βάλαμε, και θα έχει μηδέν σε όλες τις άλλες. Αυτός ο τελευταίος πίνακας θα πολλαπλασιαστεί με τη στήλη ποσοτήτων (C2:C100) και θα επιστρέψει έναν τελικό πίνακα που θα περιέχει μόνο τις ποσότητες που ψάχνουμε (στις θέσεις που αντιστοιχούν στο 1) και όλες οι άλλες τιμές θα μηδενιστούν. Τέλος η SUMPRODUCT θα αναλάβει τη τελική άθροιση:

=SUMPRODUCT((A2:A100="Θεσσαλονίκη")*(YEAR(B2:B100)=2013)*(C2:C100>=1000)*(C2:C100<=2000)*(C2:C100))

.

Μια μικρή παραξενιά της ηρωίδας μας
Σημειώστε ότι στα τρία προηγούμενα προβλήματα που έλυσε η SUMPRODUCT, το όρισμα που της περνάμε, είναι τελικά ένας και μόνο ένας πίνακας με αριθμούς. Μπορεί στον αμέσως πιο πάνω τύπο να υπάρχουν τέσσερις όροι, ένας για την πόλη, ένας για το έτος, και δύο για το πόσο, αλλά οι επιμέρους πίνακες με λογικές τιμές που επιστρέφουν οι όροι αυτοί, πολλαπλασιάζονται μεταξύ τους και πολλαπλασιάζονται και με τον πίνακα των ποσοτήτων, και οι διαδοχικοί αυτοί πολλαπλασιασμοί επιστρέφουν τελικά έναν μοναδικό πίνακα, τα στοιχεία του οποίου  αθροίζει η SUMPRODUCT. Ουσιαστικά μετά τους εσωτερικούς πολλαπλασιασμούς έχουμε τη τελική γενική μορφή SUMPRODUCT(πίνακας).

Παρατηρώντας όμως, τον ορισμό της συνάρτησης:
SUMPRODUCT(πίνακας1; πίνακας2; πίνακας3; …)
είναι λογικό να σκεφτούμε ότι θα μπορούσαμε να της περάσουμε καθέναν από τους τέσσερις όρους(-πίνακες λογικών τιμών) και τον πίνακα με τις ποσότητες (C2:C100), σαν χωριστά ορίσματα, αφού έτσι και αλλιώς από την κατασκευή της η συνάρτηση θα τους πολλαπλασιάσει πριν τους αθροίσει. Πολύ λογικό! Εισάγουμε λοιπόν σε ένα κελί το τύπο:

=SUMPRODUCT((A2:A100="Θεσσαλονίκη");(YEAR(B2:B100)=2013);(C2:C100>=1000);(C2:C100<=2000);(C2:C100))

Λογικό μεν, αλλά δεν δουλεύει! Ο τύπος επιστρέφει ένα ολοστρόγγυλο μηδέν!
Η συνάρτηση δείχνει να παραβιάζει τον κανόνα που συζητήσαμε στην αρχή του σημειώματος για τις πράξεις με τις λογικές τιμές TRUE και FALSE. Ίσως η απάντηση γι αυτό να είναι ο παρακάτω αφορισμός που διαβάζουμε στο σύστημα βοήθειας: «Στη συνάρτηση SUMPRODUCT, οι μη αριθμητικές τιμές των πινάκων εκλαμβάνονται ως ίσες με το μηδέν». Φαίνεται πως ο αφορισμός αυτός είναι απόλυτος, και έτσι η συνάρτηση πριν ξεκινήσει τους εσωτερικούς πολλαπλασιασμούς, μετατρέπει όλες τις λογικές τιμές σε μηδέν. Ηρωίδα με παραξενιές αυτή, παλληκάρια που ξέρουμε μονοπάτια εμείς! Θα δώσουμε στη συνάρτηση τη δυνατότητα να μετατρέψει τους πίνακες με λογικές τιμές TRUE και FALSE, σε πίνακες με αριθμούς 1 και 0, πριν αρχίσει τους πολλαπλασιασμούς. Υπάρχουν αρκετοί ισοδύναμοι τρόποι γι αυτό:

Ν(πίνακας)
1*(πίνακας)
--(πίνακας)

Στην πρώτη περίπτωση την αριθμητική μετατροπή αναλαμβάνει η αρχαία συνάρτηση του excel  =N( )
Στην δεύτερη περίπτωση χρησιμοποιούμε απλή αριθμητική, πολλαπλασιάζουμε τον πίνακα με τον αριθμό 1.
Αριθμητική, με πιο σοφιστικέ στυλ, και στην τρίτη περίπτωση, με ένα διπλό πλην μπροστά στον πίνακα.
Τροποποιούμε, λοιπόν, τον παραπάνω τύπο, και τώρα σίγουρα θα δουλέψει:

=SUMPRODUCT(N(A2:A100="Θεσσαλονίκη");N(YEAR(B2:B100)=2013);N(C2:C100>=1000);N(C2:C100<=2000);(C2:C100)) 

=SUMPRODUCT(1*(A2:A100="Θεσσαλονίκη");1*(YEAR(B2:B100)=2013);1*(C2:C100>=1000);1*(C2:C100<=2000);(C2:C100)) 

=SUMPRODUCT(--(A2:A100="Θεσσαλονίκη");--(YEAR(B2:B100)=2013);--(C2:C100>=1000);--(C2:C100<=2000);(C2:C100)) 

=SUMPRODUCT(0+(A2:A100="Θεσσαλονίκη");0+(YEAR(B2:B100)=2013);0+(C2:C100>=1000);0+(C2:C100<=2000);(C2:C100))

Όπως έχει καταγράψει η ιστορία του excel, τη χρήση της SUMPRODUCT αντί για την SUM (Ctrl+Shift+Enter) πρότεινε για πρώτη φορά ο Ken Wright στις αγγλόφωνες ομάδες συζήτησης για το excel. Από τότε η χρήση της SUMPRODUCT διαδόθηκε, έγινε πάρα πολύ δημοφιλής στους χρήστες του excel και η βιβλιογραφία της στο δίκτυο πολύ μεγάλη.

Η ιστορία μας όμως δεν τελειώνει εδώ. Οι περιπέτειες της SUMPRODUCT θα συνεχιστούν και σε επόμενο σημείωμα.
Στο excel βιβλίο SUMPRODUCTexample.xls θα βρείτε όλα τα παραδείγματα του σημειώματος, αν θέλετε κατεβάστε το.

This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , , . Bookmark the permalink.

8 Responses to Χρήση της συνάρτησης SUMPRODUCT για άθροιση υπό όρους.

  1. Ο/Η Stazybο Hοrn λέει:

    Άσκηση με διάζευξη δεν θα βάλεις; :)
    Ωραίος!

  2. Ο/Η vioannis λέει:

    Θα βάλω, θα βάλω… Υπάρχει και συνέχεια με περιπέτειες που έχουν πολύ σασπένς. Να κρατήσεις θέση…

  3. Ο/Η Δημήτρης λέει:

    Φίλε Γιάννη καλησπέρα.
    Σήμερα ψάχνοντας για λύση σε ένα πρόβλημα που έχω στο excel ανακάλυψα το blog σου και χωρίς να το κατλάβω πέρασα 2 ώρες διαβάζοντας πολλά ενδιαφέροντα πράγματα. Συγχαρητήρια!
    Πάμε τώρα και στο πρόβλημα μου. Θα ήθελα στο παράδειγμα σου με τις πόλεις, τις ημερομηνίες και τις ποσότητες να βρω έναν τρόπο να φτιάξω έναν πίνακα δίπλα από τα δεδομένα με τις 10 «καλύτερες πόλεις», βάση την ποσότητά τους ανά έτος. Έναν πίνακα 2 στηλών και δέκα γραμμών δηλαδή που θα λέει πχ.
    Βέροια 7000
    Χανιά 6500
    Γιάννενα 4000


    Αθήνα 300 (10η γραμμή).

    Ο παραπάνω πίνακας για το 2013. Για το 2014 αντίστοιχος πίνακας βάση των δεδομένων.
    Για να καταλάβεις γιατί το θέλω έχω περασμένα τιμολόγια ανα ημερομηνία με ποσά και όνομα πελάτη. Και θέλω να βρω τους 10 καλύτερους μου πελάτες με βάση τα ποσά. Ακριβώς η ίδια αντιστοιχία με το παράδειγμά σου δηλαδή. Έχω χρησιμοποιήσει επιτυχώς την SUMPRODUCT για να υπολογίσω μηνιαίους και ετήσιους τζίρους καθώς και υπολογισμό μηνιαίου και τριμηνιαίου ΦΠΑ. Το κερασάκι στην τούρτα του φύλου μου είναι να μάθω τους καλύτερους μου πελάτες και εκεί έχω κολλήσει. Κάθε βοήθεια εθσπρόσδεκτη.
    Ειλικρινά πάντως χάρηκα πολύ που βρήκα ένα ελληνικό blog που να βοηθάει όλους εμάς που αγαπάμε και δουλεύουμε το excel.

  4. Ο/Η vioannis λέει:

    Δημήτρη,
    Αν η στήλη με τις ημερομηνίες είναι η Α, η στήλη με τα ποσά η Β και τα ονόματα των πελατών στη C, τότε ο τύπος

    =SUMPRODUCT(($C$2:$C$2000=F2)*(YEAR($A$2:$A$2000)=2012)*($B$2:$B$2000))
    

    Θα σου δώσει το άθροισμα των ποσών των τιμολογίων, για το έτος 2012, για τον πελάτη που το όνομά του είναι στο κελί F2.
    Γράφεις λοιπόν στην κενή στήλη F τα (μοναδικά) ονόματα των πελατών, είτε χειριστικά, είτε τα παίρνεις από τη στήλη Β με: Μενού Δεδομένα, Φίλτρο για προχωρημένους, Αντιγραφή σε άλλη θέση, Περιοχή λίστας Β2:Β2000, Περιοχή κριτηρίων κενό, Αντιγραφή σε F2 και τσεκάρεις: Μοναδικές εγγραφές μόνο και ΟΚ. Επίσης μπορείς να πάρεις τα (μοναδικά) ονόματα των πελατών σου με τη μάκρο ή την vba συνάρτηση που θα βρεις στο θέμα του ιστολογίου “Μοναδικές Τιμές. Μακροεντολή και Συνάρτηση”. Στο κελί G2 γράφεις τον παραπάνω τύπο και τον αντιγράφεις μέχρι το κελί που τελειώνουν τα ονόματα των πελατών στη στήλη F. Τέλος επιλέγεις στις στήλες F και G και κάνεις φθίνουσα ταξινόμηση ως προς τη στήλη G. Για ένα κλειστό από νέες εγγραφές έτος, η τρόπος είναι αποτελεσματικός, αλλά για ένα έτος ανοιχτό σε νέες εγγραφές τιμολογίων, τα μεν αθροίσματα των ποσών θα ενημερώνονται, αλλά η ταξινόμηση θα πρέπει ίσως να επαναληφτεί και να ενημερωθεί και η στήλη F για τους εν τω μεταξύ νέους πελάτες. Βέβαια η όλη εργασία ανανέωσης του πίνακα FG από νέες εγγραφές στη βάση μπορεί να ανατεθεί σε μία μακροεντολή, αλλά πριν πάμε στις μάκρο σου προτείνω σαν πιο καλή λύση, άμεσης ενημέρωσης από νέες εγγραφές, έναν απλό Pivot που θα δημιουργήσεις δεξιά στο ίδιο φύλλο με τη βάση σου. Δες τον σε λειτουργία στο παράδειγμα PivotExample.Προηγουμένως θα ορίσεις δυναμικά τη βάση σου με ένα όνομα, για τη βάση στο Φύλλο2 του παραδείγματος έτσι :

    MyData	=OFFSET(Φύλλο2!$A$1;0;0;COUNTA(Φύλλο2!$A:$A);4)
    

    Αν δεν έχεις ξαναδουλέψει με Pivot, πες μου να σου περιγράψω ένα-ένα τα βήματα.

  5. Ο/Η Δημήτρης λέει:

    Γιάννη σε ευχαριστώ πολύ για την γρήρορη απάντηση σου.
    Κατάλαβα το σκεπτικό σου. Ομολογώ ότι ήταν πιο απλό από το δικό μου. Σκεφτόμουνα κάτι σε IF με παραμέτρους (INDEX και MATCH) σε συνάρτηση με την SUMPRODUCT. Είχα χρησιμοποιήσει επιτυχώς την IF με INDEX και MATCH για επιστροφή των πιο 10 πιο συχνά εμφανιζόμενων κελιών (μη αριθμητικών) σε άλλο φύλλο. Όμως η λύση σου μου μοιάζει πιο απλή και πιο εύκολα εφαρμόσιμη. Την δευτέρα που θα πάω στο γραφείο θα την χρησιμοποιήσω και θα σου οω εντυπώσεις.
    ΚΑΛΟ ΣΚ!

  6. Ο/Η Δημήτρης λέει:

    Γιάννη σήμερα δούλεψα το αρχείο μου και σου παρουσιάζω εντυπώσεις.
    Χρησιμοποίησα την SUMPRODUCT όπως μου είπες και υπολόγισα όλα τα ποσά από τον κάθε πελάτη ανά έτος. Στην συνέχεια είχα την ιδέα, να μην κάνω ταξινόμηση ούτε να χρησιμοποιήσω Pivot table, αλλά να χρησιμοποιήσω την LARGE και τις INDEX-MATCH για βγάλω τους 10 καλύτερους πελάτες. Όλα δούλεψαν ρολόι με μια μικρή λεπτομέρεια. Κάθε φορά που περνάω νέο τιμολόγιο κάνει υπολογισμό κελιών. Όχι πολύ ώρα αλλά δεν είναι άμεση η απόκριση. Επίσης να σου πω πως ο υπολογιστής μου είναι τελευταίας τεχνολογίας οπότε δεν νομίζω να φταίει αυτό. Έχει καμιά ιδέα ποια από τις συναρτήσεις αυτές είναι αυτή που προκαλεί αυτήν την καθυστέρηση. Αν θες σου στέλνω το αρχείο μου να το δεις για να μου κάνεις ό,τι παρατηρήσεις θέλεις. Κάθε συμβουλή ευπρόσδεκτη.

  7. Ο/Η vioannis λέει:

    Δημήτρη, δεν είναι θέμα συνάρτησης φύλλου, αλλά όγκου δεδομένων που επεξεργάζονται οι συναρτήσεις. Αν θέλεις να κρατήσεις τις συναρτήσεις και να μην σέρνεται και αγκομαχά το φύλλο, όρισε στο βιβλίο αυτό τον υπολογισμό σε μη αυτόματο. Δες εδώ. Στείλε όμως τη συνάρτηση που βγάζει τους πελάτες μεγαλύτερου όγκου, με ενδιαφέρει και ίσως φανεί χρήσιμος και σε άλλους φίλους του ιστολογίου.

  8. Ο/Η Δημήτρης λέει:

    Γιάννη η συνάρτηση που υπολογίζει όπως είπαμε παραπάνω τα ποσά του κάθε πελάτη ανά έτος είναι η εξής:

    =SUMPRODUCT((‘Εκδοθέντα Τιμολόγια’!$C$3:$C$1000=A4)*(YEAR(‘Εκδοθέντα Τιμολόγια’!$B$3:$B$1000)=2014)*(‘Εκδοθέντα Τιμολόγια’!$D$3:$D$1000)) + SUMPRODUCT((‘Εκδοθείσες Αποδείξεις’!$C$3:$C$1000=A4)*(YEAR(‘Εκδοθείσες Αποδείξεις’!$B$3:$B$1000)=2014)*(‘Εκδοθείσες Αποδείξεις’!$D$3:$D$1000))

    Όπως θα κατάλαβες υπάρχουν δύο ξεχωριστά φύλλα «Εκδοθέντα Τιμολόγια» και «Εκδοθείσες Αποδείξεις». Υπάρχει και άλλο φύλλο με όνομα «Πελατολόγιο», όπου και εκεί γίνεται ο υπολογισμός. Η στήλη Α στο πελατολόγιο περιέχει το όνομα του πελάτη. Στα άλλα δύο φύλλα τα ονόματα των πελατών είναι στην στήλη C, οι ημερομηνίες είναι στην στήλη B και τα ποσά στην στήλη D.

    Όσο αναφορά την συνάρτηση υπολογισμού του μεγαλύτερου όγκου αυτή γίνεται σε 2 στήλες. Αρχικά πρέπει να βρούμε τα μεγαλύτερα ποσά. Αυτό γίνεται ως εξής:

    =LARGE(Πελατολόγιο!$J$4:$J$500;1)

    Η στήλη J του φύλλου πελατολόγιο περιέχει τους παραπάνω υπολογισμούς για το έτος 2014.
    Δίνοντας σαν όρισμα το 1 παίρνουμε το μεγαλύτερο ποσό. Αντιγράφοντας στο αμέσως παρακάτω κελί με όρισμα 2 παίρνουμε το 2ο μεγαλύτερο. κ.ο.κ. Δηλαδή:

    =LARGE(Πελατολόγιο!$J$4:$J$500;2)
    =LARGE(Πελατολόγιο!$J$4:$J$500;3)
    =LARGE(Πελατολόγιο!$J$4:$J$500;4)
    =LARGE(Πελατολόγιο!$J$4:$J$500;5)

    για τους πέντε μεγαλύτερους όγκους.

    Τώρα αυτό που μένει είναι να αντιστοιχίσουμε τα ποσά που υπολογίσαμε με τον κατάλληλο πελάτη. Η συνάρτηση που μας το κάνει αυτό είναι η εξής:

    =INDEX(Πελατολόγιο!$Α$4:$Α$500;MATCH(I4;Πελατολόγιο!$J$4:$J$500;0))

    Όπου η στήλη Α στο πελατολόγιο όπως είπαμε περιέχει τα ονόματα των πελατών και η J τα υπολογίζομενα ποσά (της SUMPRODUCT) για το έτος 2014. Το Ι4 είναι το κελί του ποσού που υπολόγισε παραπάνω η LARGE. Ψάχνουμε να βρούμε με λίγα λόγια ποιο όνομα της στήλης Α του πελατολογίου έχει το ποσό του κελιού Ι4. Αντιγράφοντας αντίστοιχα στα παρακάτω κελιά θα έχουμε τον εξής τύπο.

    =INDEX(Πελατολόγιο!$Α$4:$Α$500;MATCH(I5;Πελατολόγιο!$J$4:$J$500;0))
    =INDEX(Πελατολόγιο!$Α$4:$Α$500;MATCH(I6;Πελατολόγιο!$J$4:$J$500;0))
    =INDEX(Πελατολόγιο!$Α$4:$Α$500;MATCH(I7;Πελατολόγιο!$J$4:$J$500;0))
    =INDEX(Πελατολόγιο!$Α$4:$Α$500;MATCH(I8;Πελατολόγιο!$J$4:$J$500;0))

    Φτιάξαμε τώρα δύο στήλες δηλαδή που στην πρώτη έχουμε το όνομα του πελάτη με τον μεγαλύτερο όγκο και στην δεύτερη το ποσό των δεδομένων αυτών. Και συνεχίζει για όσο θέλουμε. Εδώ το παράδειγμα είναι για τους 5 «καλύτερους πελάτες».
    Το τέσταρα με αρκετά δεδομένα και ενημερώνεται αυτόματα ανάλογα με τα τιμολόγια του τρέχοντος έτους. Το μόνο πρόβλημα που εντόπισα με την LARGE είναι σε περίπτωση ίσοβαθμίας στον όγκο του ποσού. Εκεί βγάζει μόνο το μεγαλύτερο και το δέυτερο δεν υπολογίζεται.

    Ελπίζω να σε βοήθησα να καταλάβεις, εσένα και όποιον άλλο ενδιαφέρεται τον τρόπο λειτουργίας των συναρτήσεων.

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