Genel formül
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Özet
Özel bir programla iki tarih arasındaki çalışma saatlerini hesaplamak için, SATIR, DOLAYLI ve ORTA'nın yardımıyla WEEKDAY ve SUMPRODUCT işlevlerine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, F8'deki formül şöyledir:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
8 saatin Pzt-Cum, Cumartesi 4 saat çalışıldığı ve 3 Eylül Pazartesi tatil olduğu özel bir programa göre 36 saat döndürür. Tatiller G6: G8 olarak adlandırılan aralık olarak sağlanır. İş programı D sütununa bir metin dizisi olarak girilir ve istenildiği gibi değiştirilebilir.
Not: Bu, Control + Shift + Enter ile girilmesi gereken bir dizi formülüdür. Standart 8 saatlik bir iş gününüz varsa, bu formül daha basittir.
Açıklama
Temelde bu formül, verilen iki tarih arasındaki her gün için haftanın gününü (yani Pazartesi, Salı, vb.) Bulmak için HAFTA GÜNÜ işlevini kullanır. WEEKDAY, 1 ile 7 arasında bir sayı döndürür. Varsayılan ayarlarla, Pazar = 1 ve Cumartesi = 7.
Bu formülün püf noktası, WEEKDAY işlevine besleyebileceğiniz bir tarih dizisi oluşturmaktır. Bu, INDIRECT ile ROW ile yapılır:
ROW(INDIRECT(B6&":"&C6))
SATIR, birleştirilen tarihleri satır numaraları olarak yorumlar ve şuna benzer bir dizi döndürür:
(43346;43347;43348;43349;43350;43351;43352)
Dizideki her sayı bir tarihi temsil eder. WEEKDAY işlevi daha sonra diziyi değerlendirir ve bir dizi hafta içi değerleri döndürür:
(2;3;4;5;6;7;1)
Bu sayılar her tarihin haftanın gününe karşılık gelir. ORTA işlevine başlangıç numarası bağımsız değişkeni olarak ve metin için D6'daki "0888884" değeriyle birlikte sağlanır:
MID("0888884",(2;3;4;5;6;7;1),1)
MID'ye bir dizi başlangıç numarası verdiğimiz için, aşağıdaki gibi bir sonuç dizisi döndürür:
("8";"8";"8";"8";"8";"4";"0")
Bu değerler, başlangıç tarihinden bitiş tarihine kadar her gün çalışılan saatlere karşılık gelir. Bu dizideki değerlerin sayı değil metin olduğunu unutmayın. Gerçek sayılara dönüştürmek için, aşağıda açıklandığı gibi tatilleri yönetmek için oluşturulan ikinci bir dizi ile çarparız. Matematik işlemi, metni sayısal değerlere zorlar.
Bayram
Tatilleri idare etmek için ISNA, MATCH ve "tatiller" olarak adlandırılan aralığı şu şekilde kullanıyoruz:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Bu ifade, yukarıda DOLAYLI ve SATIR ile oluşturulan aynı tarih dizisini kullanarak tatiller adlandırılmış aralıktaki tarihleri bulmak için KAÇINCI kullanır. MATCH, tatiller bulunduğunda bir sayı, yoksa #YOK hatası döndürür. ISNA işlevi, DOĞRU'nun tatilleri ve YANLIŞ'ın tatil olmayanları temsil etmesi için sonuçları "çevirir". ISNA, bir dizi veya bunun gibi sonuçlar döndürür:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Son olarak, SUMPRODUCT içinde her iki dizi de birbiriyle çarpılır. Matematik işlemi, DOĞRU ve YANLIŞ'ı 1 ve sıfıra ve ilk dizideki metin değerlerini sayısal değerlere zorlar (yukarıda açıklandığı gibi), bu nedenle sonunda:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
Çarpma işleminden sonra, SUMPRODUCT içinde tarih aralığındaki tüm çalışma saatlerini içeren tek bir dizimiz var:
=SUMPRODUCT((0;8;8;8;8;4;0))
SUMPRODUCT daha sonra dizideki tüm öğeleri toplar ve 36 sonucunu döndürür.
