Συνδυασμοί και Διατάξεις

Ο κώδικας της Myrna Larson για την κατασκευή συνδυασμών ή διατάξεων
Για τον υπολογισμό του πλήθους των συνδυασμών και του πλήθους των διατάξεων το excel έχει τις συναρτήσεις φύλλου COMBIN και PERMUT αντίστοιχα. Αν είστε προπονητής που έχει στη διάθεσή του 8 παίχτες και θέλετε να φτιάξετε από αυτούς μια ομάδα των 5, η συνάρτηση =COMBIN(8;5) θα σας πει ότι αυτό μπορείτε να το κάνετε με 56 διαφορετικούς τρόπους, και αν η κάθε θέση στην ομάδα είναι διακριτή, και παίζει ρόλο η θέση που θα παίζει ο καθένας παίχτης, η συνάρτηση =PERMUT(8;5) θα σας πει ότι αυτό γίνεται με 6720 τρόπους. Για να καταγραφούν όμως, ένας-ένας όλοι αυτοί οι τρόποι, θα χρειαστεί vba κώδικας. Ευτυχώς υπάρχει στο δίκτυο, εδώ και πολλά χρόνια, κώδικας που λύνει αποτελεσματικά αυτό το θέμα γραμμένος από Myrna Larson.(I)   Τον κώδικα μπορείτε να βρείτε σε πολλά σημεία στο δίκτυο, όπως και σε σελίδες της Microsoft. Αρκετές φορές φίλοι του ιστολογίου έχουν ζητήσει τρόπο να κατασκευάζουν συνδυασμούς και διατάξεις, και στο παρελθόν, τους παρέπεμπα στον κώδικα της κας Larson. Για να διευκολύνω μελλοντικούς χρήστες που θα χρειαστούν τον ίδιο κώδικα, ετοίμασα δύο excel βιβλία, ένα για τις παλαιότερες εκδόσεις 97-2003 και ένα για τις νεώτερες 2007-13, που τυποποιούν την εργασία. Ο χρήστης αρκεί να εισάγει τα δεδομένα του σε ένα φύλλο και να τρέξει τον κώδικα από ένα κουμπί. Ο κώδικας είναι ακριβώς όπως γράφτηκε το 2000 από την Myrna Larson, έκανα μόνο πολύ μικρές απαραίτητες τροποποιήσεις στην έκδοση 2007-13, και πρόσθεσα μία ρουτίνα ελέγχου των δεδομένων που εισάγει ο χρήστης.

.
Ανοίξτε το βιβλίο και από το πτυσσόμενο πλαίσιο στο κελί Α1, επιλέξτε αν θα σχηματίσετε συνδυασμούς ή διατάξεις.

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

CombinPermut22
Αν επιλέξετε ΝΑΙ, ένα νέο φύλλο θα προστεθεί στο βιβλίο και οι σ/δ που θα δημιουργηθούν, θα εμφανιστούν σε μία ή περισσότερες στήλες του νέου φύλλου:

CombinPermut33
Ο κώδικας είναι αρκετά γρήγορος, αλλά για πολλά εκατομμύρια σ/δ, θα χρειαστεί αρκετό χρόνο. Στο excel βιβλίο θα βρείτε περισσότερες οδηγίες για τη χρήση του.

  • Κατεβάστε από εδώ ή από εδώ το βιβλίο CombinationAndPermutations07_13.xlsm (για excel 2007-2010-2013)
  • ή από εδώ ή από εδώ το βιβλίο CombinationAndPermutations97_03.xls  (για excel 1997-2000-2002-2003)
    . . . . . . . . . . . . . . . . .
    (I) Η γιατρός αιματολόγος Myrna Larson υπήρξε ένα πολύ δραστήριο μέλος των αγγλόφωνων ομάδων συζήτησης για το excel, την προηγούμενη δεκαετία. Μόνο τα έτη 2001 έως 2005 συμμετείχε σε περισσότερες από 7000 συζητήσεις δίνοντας ευφυέστατες λύσεις σε πολλά προβλήματα.
Posted in excel, Μακροεντολές, Συναρτήσεις φύλλου, VBA | Tagged , , , , , , , , , , | 5 Σχόλια

Ένας χρόνος, ένας μήνας, μια ημέρα…. Η συνάρτηση DATEDIF

DimitrisHorn2

‘ένα εκατομμύριο, εκατόν μία χιλιάδες, εκατόν μία και δέκα’. Ο αξέχαστος Δημήτρης Χορν, στο ρόλο του ταμία, ψάχνει για το χαμένο εκατομμύριο της Τράπεζας. Εδώ ψάχνουμε για χαμένες ημέρες στο excel…

Στο κελί Α1 ενός φύλλου γράψτε μια οποιαδήποτε ημερομηνία και στο κελί Β1 μια άλλη μεταγενέστερη. Σε ένα τρίτο κελί γράψτε τη συνάρτηση:

=DATEDIF(A1;B1;"m")

Θα σας επιστραφεί ένας αριθμός που θα δίνει τη συνολική διαφορά Β1-Α1 σε ακέραιους μήνες. Η συνάρτηση DATEDIF αν και δεν εμφανίζεται στον κατάλογο των συναρτήσεων, υπάρχει σε όλες τις εκδόσεις του excel. Η επίσημη βοήθεια για τη συνάρτηση (εδώ) λέει ότι παρέχετε για λόγους συμβατότητας με άλλα προγράμματα.
Η πλήρης σύνταξή της είναι:
=DATEDIF(αρχική ημερομηνία ; τελική ημερομηνία ; μονάδα)
Η συνάρτηση υπολογίζει τη διαφορά των δύο ημερομηνιών και την αποδίδει στη μορφή που καθορίζει το όρισμα ‘μονάδα’, το οποίο μπορεί να πάρει μία από τις εξής τιμές:

"y", "ym", "md", "m", "d", "yd"

Οι τρεις πρώτες αν συνδυαστούν δίνουν τη διαφορά των ημερομηνιών στη μορφή: έτη-μήνες-ημέρες. Θα δούμε όλες τις περιπτώσεις παρακάτω αναλυτικά και για κάθε περίπτωση θα δούμε και εναλλακτικούς τύπους φύλλου.
Σημειώστε ότι:
• Σε όλους τους παρακάτω τύπους, Α1 είναι το κελί που περιέχει την αρχική ημερομηνία, και Β1 το κελί που περιέχει την τελική. (I)
• Στη διαφορά μεταξύ τελικής και αρχικής ημερομηνίας, η αρχική προσμετρείται στη διαφορά ενώ η τελική όχι. Αν θέλετε να προσμετρηθεί και η τελική ημέρα, σε όλους τους τύπους που ακολουθούν αντικαταστήστε το όρισμα B1 με B1+1
• Μερικές από τις συναρτήσεις, μορφοποιούν αυτόματα το κελί που θα εισαχθούν, σε μορφή ημερομηνίας. Στην περίπτωση αυτή, μετά την εισαγωγή της συνάρτησης, αλλάξτε τη μορφή του κελιού σε μια μορφή αριθμού ή σε «Γενική»

(1) Το όρισμα ‘μονάδα’ είναι y
Η συνάρτηση:

=DATEDIF(A1;B1;"y")

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

αρχική           τελική           έτη
1/5/2009         15/11/2011       2
1/5/2009         1/5/2011         2
1/5/2009         30/4/2011        1
1/5/2009         15/3/2011        1

Απολύτως ισοδύναμοι είναι οι παρακάτω τύποι:


=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));YEAR(B1)-YEAR(A1)-1;YEAR(B1)-YEAR(A1))

=YEAR(B1)-YEAR(A1)-(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)))

Παρατηρήστε ότι αν η ημερομηνία (μήνας και ημέρα μόνο, χωρίς το έτος) στην τελική ημερομηνία είναι μεγαλύτερη ή ίση από την ημερομηνία στην αρχική τότε τα έτη είναι απλά η διαφορά των ετών. Αν μήνας και ημέρα της τελικής είναι μικρότερη από μήνα και ημέρα αρχικής, τότε η διαφορά των ετών πρέπει να ελαττωθεί κατά 1. Έτσι, στους δύο εναλλακτικούς τύπους, τοποθετώ μήνα και ημέρα σε ένα τυχαίο έτος (κατά προτίμηση δίσεκτο για να καλύψω και την περίπτωση 29-Φεβ),για να συγκρίνω μήνα και ημέρα τελικής και αρχικής, και ανάλογα η συνάρτηση επιστρέφει τη απλή διαφορά των ετών ή τη διαφορά ελαττωμένη κατά 1.

(2) Το όρισμα ‘μονάδα’ είναι ym
Η συνάρτηση:

=DATEDIF(A1;B1;"ym")

επιστρέφει τους υπόλοιπους ακέραιους μήνες (της διαφοράς Β1-Α1) που απομένουν, αφού αφαιρέσει τους μήνες που αντιστοιχούν σε ολόκληρα έτη. Το τυχόν υπόλοιπο σε ημέρες αγνοείται. Παραδείγματα:

αρχική	        τελική	     μήνες
10/5/2014	23/8/2015	3
10/5/2014	23/9/2015	4
10/5/2014	10/5/2015	0
10/5/2014	20/5/2015	0
10/5/2014	1/2/2015	8

Απολύτως ισοδύναμοι είναι οι παρακάτω τύποι:

=MOD(12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)+IF(DAY(B1)<DAY(A1);-1;0);12)

=12*(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)))-MONTH(A1)+MONTH(B1)-(DAY(B1)<DAY(A1))

=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));12-MONTH(A1)+MONTH(B1)-(DAY(B1)<DAY(A1));MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1)))

Όπως είναι φυσικό, οι τύποι αυτοί επιστρέφουν έναν ακέραιο αριθμό μεταξύ 0 και 11.

(3) Το όρισμα ‘μονάδα’ είναι md
Η συνάρτηση:

=DATEDIF(A1;B1;"md")

επιστρέφει τις υπόλοιπες ημέρες (της διαφοράς Β1-Α1)που απομένουν, αφού αφαιρέσει όλες τις ημέρες που αντιστοιχούν σε ολόκληρα έτη και ολόκληρους μήνες. Παραδείγματα:

αρχική	        τελική	        ημέρες
9/3/2014	20/5/2015	11
9/3/2014	16/9/2015	7
9/3/2014	31/5/2015	22
9/3/2014	9/3/2015	0
1/3/2014	31/1/2015	30

Στην περίπτωση αυτή όμως η DATEDIF παρουσιάζει δύο προβλήματα: Πρώτον έχουν αναφερθεί πολλές περιπτώσεις, όπου η συνάρτηση επιστρέφει προφανώς λάθος αποτέλεσμα. (πχ για Α1= 12/7/2007 και για Β1=4/1/2008, επιστρέφει 136). Τα λάθη αυτά ποικίλουν μεταξύ των εκδόσεων του excel και των υπολογιστών που τρέχουν. (II)
Δεύτερον, πιθανά να μην συμφωνείτε με τον τρόπο που κάνει η DATEDIF τον υπολογισμό. Τα παρακάτω αποτελέσματα:

αρχική	        τελική	        ημέρες
31/1/2015       1/2/2016	-1
30/11/2014	1/12/2015	2
29/2/2016	1/3/2017	3
31/1/2015	11/4/2015 	10 
27/2/2003	7/2/2005	11
31/3/2015	1/4/2015        0
29/2/2000	1/3/2000	3

είτε είναι λάθη του συστήματος, είτε προκύπτουν γιατί με αυτόν τον τρόπο κάνει η DATEDIF τον υπολογισμό, πιστεύω θα συμφωνείτε και εσείς ότι δεν είναι αποδεκτά αποτελέσματα. Στην επίσημη βοήθεια για την συνάρτηση δεν υπάρχει καμία αναφορά για τον τρόπο που ορίζει την διαφορά η DATEDIF. Πριν καταλήξουμε σε έναν αποδεκτό εναλλακτικό τύπο, νομίζω είναι απαραίτητο να συζητήσουμε για τον τρόπο που θα κάνουμε τον υπολογισμό. Διακρίνουμε δύο περιπτώσεις:
α) Όταν η ημέρα του μήνα της αρχικής ημερομηνίας είναι μικρότερη ή ίση από την ημέρα του μήνα της τελικής ημερομηνίας. Με όρους του excel, όταν DAY(A1) <=DAY(B1). Τότε οι υπόλοιπες ημέρες είναι η απλή διαφορά την ημερομηνιών : DAY(B1)-DAY(A1).

αρχική          τελική	        ημέρες
8/1/2011	24/12/2015	16 = 24-8
7/7/2000	17/1/2016	10 = 17-7
7/2/1998	23/12/2000	16 = 23-7
26/8/2001	26/11/2001	0 = 26-26
14/12/2000	15/9/2014	1 = 15-14

Στην περίπτωση αυτή και η DATEDIF με τον ίδιο τρόπο υπολογίζει την διαφορά.
β) Όταν η ημέρα του μήνα της αρχικής ημερομηνίας είναι μεγαλύτερη από την ημέρα του μήνα της τελικής ημερομηνίας. Δηλαδή, όταν DAY(A1) >DAY(B1). . Στην περίπτωση αυτή υπάρχουν πολλοί τρόποι υπολογισμού. Δεν θα σας κουράσω αναφέροντάς τους εδώ. (ΙΙΙ) Θα περιγράψω μόνο αυτόν που τελικά προτείνει το ιστολόγιο: Από το σύνολο των ημερών, (από την αρχική ημερομηνία Α1 έως την τελική Β1), αφαιρούνται οι ημέρες που αντιστοιχούν στους ολόκληρους μήνες που μεσολαβούν μεταξύ των ημερομηνιών και η διαφορά είναι το αποτέλεσμα της συνάρτησης . Παράδειγμα: Αν Α1=25/7/2014 και Β1=10/4/2015,τότε από Α1 έως Β1 οι ημέρες είναι =Β1-Α1=259 ημέρες, και το σύνολο των ημερών στους ακέραιους μήνες που μεσολαβούν, δηλαδή από 1/8/2014 έως και 31/3/2015 είναι 243 ημέρες. Η διαφορά 259-243=16 είναι η τιμή που αναζητάμε.
Την πρώτη ημέρα του πρώτου μήνα που μεσολαβεί, δίνει ο τύπος:
=DATE(YEAR(A1);MONTH(A1)+1;1)
και την τελευταία ημέρα του τελευταίου μήνα που μεσολαβεί, δίνει ο τύπος:
=DATE(YEAR(B1);MONTH(B1);1)-1

Μαζεύοντας όλα τα παραπάνω που είπαμε, σε μία έκφραση, προκύπτει ο τελικός τύπος:

=IF(DAY(B1)>=DAY(A1);DAY(B1)-DAY(A1);B1-A1-(DATE(YEAR(B1);MONTH(B1);1)-DATE(YEAR(A1);MONTH(A1)+1;1)))

ή σε μία ισοδύναμη αλλά πιο σύντομη διατύπωση:

=DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)

Επαναλαμβάνω ότι οι δύο παραπάνω τύποι δύνονται σαν εναλλακτικοί (αλλά που επιστρέφουν σωστότερα αποτελέσματα) του τύπου:

=DATEDIF(A1;B1;"md")

Στο excel βιβλίο datedif_tests.xls (που μπορείτε να κατεβάσετε από εδώ ή από εδώ, μια μακροεντολή παράγει τυχαίες ημερομηνίες και μπορείτε να δείτε στην πράξη τις διαφορές μεταξύ της DATEDIF με όρισμα md και αυτής που προτείνει το ιστολόγιο, και να τις αξιολογήσετε.

Βάζοντας τα όλα μαζί : έτη-μήνες- ημέρες
Συνδυάζοντας τύπους από τις παραπάνω ενότητες μπορείτε να εκφράσετε τη διαφορά δύο ημερομηνιών στη μορφή έτη-μήνες- ημέρες.

=DATEDIF(A1;B1;"y") &" έτη, "& DATEDIF(A1;B1;"ym")& " μήνες, "&DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)&" ημέρες"

ή

=DATEDIF(A1;B1;"y") &"-"& DATEDIF(A1;B1;"ym")& "-"&DAY(DATE(YEAR(A1);MONTH(A1)+1;1)-1)*(DAY(A1)>DAY(B1))-DAY(A1)+DAY(B1)

Ο πρώτος για το χρονικό διάστημα από 11/12/2013 έως 7/6/2015 επιστρέφει: 1 έτη, 5 μήνες, 27 ημέρες και ο δεύτερος επιστρέφει: 1-5-27. Σημειώστε ότι σε αυτή τη σύνταξη, οι συναρτήσεις επιστρέφουν κείμενο και όχι αριθμούς.

(4) Το όρισμα ‘μονάδα’ είναι m
Η συνάρτηση:

=DATEDIF(A1;B1;"m")

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

αρχική	        τελική	        μήνες
19/12/2000	19/2/2004	38
6/3/1950	30/5/2015	782
20/8/1990	28/8/2000	120
1/3/2015	31/3/2015	0

Όπως και στις περιπτώσεις (1) και (2) η DATEDIF με όρισμα m, επιστρέφει απολύτως σωστά αποτελέσματα. Μια ισοδύναμη συνάρτηση φύλλου είναι η:

=12*(YEAR(B1)-YEAR(A1))+MONTH(B1)-MONTH(A1)-(DAY(B1)<DAY(A1))

(5) Το όρισμα ‘μονάδα’ είναι d
Η συνάρτηση:

=DATEDIF(A1;B1;"d")

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

=B1-A1

(6) Το όρισμα ‘μονάδα’ είναι yd
Η συνάρτηση:

=DATEDIF(A1;B1;"yd")

επιστρέφει τις υπόλοιπες ημέρες (της διαφοράς Β1-Α1)που απομένουν, αφού αφαιρέσει όλες τις ημέρες που αντιστοιχούν σε ολόκληρα έτη. Παραδείγματα:

αρχική	        τελική	        ημέρες
30/5/2010	30/5/2015	0
30/5/2010	31/5/2015       1
17/4/2012	23/5/2014	36
1/1/2015	31/12/2015	364

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

=IF(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1));B1-DATE(YEAR(B1)-1;MONTH(A1);DAY(A1));B1-(DATE(YEAR(B1);MONTH(A1);DAY(A1))))

ή σε ποιο σύντομη διατύπωση:

=B1-DATE(YEAR(B1)-(DATE(2004;MONTH(B1);DAY(B1))<DATE(2004;MONTH(A1);DAY(A1)));MONTH(A1);DAY(A1))

. . . . . . . . . . .
(Ι)
Τα ορίσματα αρχική και τελική ημερομηνία εκτός από διευθύνσεις κελιών που περιέχουν ημερομηνίες ή ημερομηνίες σαν κείμενο, μπορούν να είναι συναρτήσεις που επιστρέφουν ημερομηνίες όπως TODAY, DATEVALUE, DATE κλπ. Ακόμα οι ημερομηνίες μπορούν να είναι γραμμένες απ ευθείας μέσα στις συναρτήσεις στη μορφή ημέρα/μήνας/έτος κλεισμένες απαραίτητα σε εισαγωγικά. Παράδειγμα:

=DATEDIF("12/3/2012";"11/1/2015";"y")

Αν στα settings του υπολογιστή σας έχετε ορίσει διαφορετική μορφή αναγραφής ημερομηνιών, προσαρμόστε ανάλογα την μορφή των ημερομηνιών που εισάγονται σαν κείμενο.
Αν η αρχική ημερομηνία είναι μεγαλύτερη από την τελική, οι συναρτήσεις θα επιστρέψουν μία τιμή σφάλματος.
(ΙΙ)
Πέρυσι το καλοκαίρι έγινε στο ιστολόγιο μια μεγάλη συζήτηση και αναφέρθηκαν πολλά τέτοια λάθη. Μπορείτε να δείτε τη συζήτηση στη σελίδα about από 12/7/14 έως 31/7/14 ή να κατεβάσετε το αρχείο about_datedif_comments.pdf, από εδώ ή από εδώ, όπου μάζεψα όλη την ενδιαφέρουσα αυτή συζήτηση και τις λύσεις που προτάθηκαν.
(ΙΙΙ)
Από εδώ ή από εδώ μπορείτε να κατεβάσετε όλο το παραπάνω σημείωμα σε μορφή pdf. Εκεί θα βρείτε επί πλέον τρόπους υπολογισμού για την περίπτωση αυτή, όπου το όρισμα μονάδα είναι md.

DimitrisHorn1

Posted in excel, Συναρτήσεις φύλλου | Tagged , , , ,