Γενική φόρμουλα
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Περίληψη
Για να υπολογίσετε τις ώρες εργασίας μεταξύ δύο ημερομηνιών με ένα προσαρμοσμένο πρόγραμμα, μπορείτε να χρησιμοποιήσετε έναν τύπο βάσει των συναρτήσεων WEEKDAY και SUMPRODUCT, με τη βοήθεια των ROW, INDIRECT και MID. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F8 είναι:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Που επιστρέφει 36 ώρες, με βάση ένα προσαρμοσμένο πρόγραμμα όπου εργάζονται 8 ώρες Δευ-Παρ, 4 ώρες εργάζονται το Σάββατο και η Δευτέρα 3 Σεπτεμβρίου είναι αργία. Οι γιορτές παρέχονται με την ονομαστική σειρά G6: G8. Το πρόγραμμα εργασίας καταχωρείται ως συμβολοσειρά κειμένου στη στήλη Δ και μπορεί να αλλάξει όπως θέλετε.
Σημείωση: Αυτός είναι ένας τύπος πίνακα που πρέπει να εισαχθεί με Control + Shift + Enter. Εάν έχετε μια κανονική 8ωρη εργάσιμη ημέρα, αυτός ο τύπος είναι απλούστερος.
Εξήγηση
Στον πυρήνα, αυτός ο τύπος χρησιμοποιεί τη συνάρτηση WEEKDAY για να υπολογίσει την ημέρα της εβδομάδας (δηλ. Δευτέρα, Τρίτη κ.λπ.) για κάθε μέρα μεταξύ των δύο δεδομένων ημερομηνιών. Η ΕΒΔΟΜΑΔΑ επιστρέφει έναν αριθμό μεταξύ 1 και 7. Με τις προεπιλεγμένες ρυθμίσεις, Κυριακή = 1 και Σάββατο = 7.
Το κόλπο σε αυτόν τον τύπο είναι η συναρμολόγηση μιας σειράς ημερομηνιών που μπορείτε να τροφοδοτήσετε στη συνάρτηση WEEKDAY. Αυτό γίνεται με ROW με INDIRECT:
ROW(INDIRECT(B6&":"&C6))
Το ROW ερμηνεύει τις συνδυασμένες ημερομηνίες ως αριθμούς σειράς και επιστρέφει έναν πίνακα όπως αυτό:
(43346;43347;43348;43349;43350;43351;43352)
Κάθε αριθμός στον πίνακα αντιπροσωπεύει μια ημερομηνία. Στη συνέχεια, η συνάρτηση WEEKDAY αξιολογεί τον πίνακα και επιστρέφει έναν πίνακα τιμών καθημερινής:
(2;3;4;5;6;7;1)
Αυτοί οι αριθμοί αντιστοιχούν στην ημέρα της εβδομάδας κάθε ημερομηνίας. Παρέχονται στη συνάρτηση MID ως όρισμα αριθμού εκκίνησης, μαζί με την τιμή στο D6, "0888884" για κείμενο:
MID("0888884",(2;3;4;5;6;7;1),1)
Επειδή δίνουμε στο MID έναν πίνακα αριθμών έναρξης, επιστρέφει μια σειρά αποτελεσμάτων όπως αυτή:
("8";"8";"8";"8";"8";"4";"0")
Αυτές οι τιμές αντιστοιχούν στις ώρες εργασίας κάθε ημέρας από την ημερομηνία έναρξης έως την ημερομηνία λήξης. Σημειώστε ότι οι τιμές σε αυτόν τον πίνακα είναι κείμενο και όχι αριθμοί. Για τη μετατροπή σε πραγματικούς αριθμούς, πολλαπλασιάζουμε με έναν δεύτερο πίνακα που δημιουργήθηκε για τη διαχείριση των διακοπών, όπως εξηγείται παρακάτω. Η μαθηματική λειτουργία εξαναγκάζει το κείμενο σε αριθμητικές τιμές.
Διακοπές
Για να χειριστούμε τις διακοπές, χρησιμοποιούμε το ISNA, το MATCH και την ονομαζόμενη σειρά "διακοπές" ως εξής:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Αυτή η έκφραση χρησιμοποιεί MATCH για να εντοπίσει ημερομηνίες που βρίσκονται στο οριοθετημένο εύρος διακοπών χρησιμοποιώντας την ίδια σειρά ημερομηνιών που δημιουργήθηκαν παραπάνω με INDIRECT και ROW. Το MATCH επιστρέφει έναν αριθμό όταν βρεθούν διακοπές και το σφάλμα # N / A όταν όχι. Η συνάρτηση ISNA «αναστρέφει» τα αποτελέσματα έτσι ώστε το TRUE αντιπροσωπεύει τις διακοπές και το FALSE αντιπροσωπεύει τις μη αργίες. Το ISNA επιστρέφει έναν πίνακα ή αποτελέσματα όπως αυτό:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Τέλος, και οι δύο συστοιχίες πολλαπλασιάζονται μεταξύ τους μέσα στο SUMPRODUCT. Η μαθηματική λειτουργία συνδυάζει TRUE και FALSE σε 1 και μηδέν, και οι τιμές κειμένου στον πρώτο πίνακα σε αριθμητικές τιμές (όπως εξηγείται παραπάνω), οπότε στο τέλος έχουμε:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Μετά τον πολλαπλασιασμό, έχουμε έναν ενιαίο πίνακα στο SUMPRODUCT που περιέχει όλες τις ώρες εργασίας στο εύρος ημερομηνιών:
=SUMPRODUCT((0;8;8;8;8;4;0))
Στη συνέχεια, το SUMPRODUCT αθροίζει όλα τα στοιχεία του πίνακα και επιστρέφει ένα αποτέλεσμα 36.
