Excel Forum

อยากทราบสูตร ที่เมื่อกำหนดวันเริ่มต้นและวันสิ้นสุดแล้ว ให้ Run ตั้งแต่วันเริ่มต้นจนถึงวันสุดท้าย (ยกเว้นวันที่ตรงกับวันจันทร์และอังคาร) ในแนวนอน

และสูตรที่ใช้นับชั่วโมงการทำงานของพนักงานคนนั้นๆ ในแต่ละวัน โดยอ้างอิงข้อมูลรหัสพนักงานจากชีท DATA ให้นำเวลาเริ่มแรกของวันนั้นๆ ของพนักงานคนนั้นๆ ลบด้วยเวลาสุดท้ายในวันนั้น เพื่อมาแสดงในชีท ชั่วโมงทำงาน

ซึ่งข้อมูลในชีท DATA จะเพิ่มขึ้นเรื่อยๆ จากบนลงล่าง

Rin wrote: ↑Sun Apr 15, 2018 1:26 pm อยากทราบสูตร ที่เมื่อกำหนดวันเริ่มต้นและวันสิ้นสุดแล้ว ให้ Run ตั้งแต่วันเริ่มต้นจนถึงวันสุดท้าย (ยกเว้นวันที่ตรงกับวันจันทร์และอังคาร) ในแนวนอน

และสูตรที่ใช้นับชั่วโมงการทำงานของพนักงานคนนั้นๆ ในแต่ละวัน โดยอ้างอิงข้อมูลรหัสพนักงานจากชีท DATA ให้นำเวลาเริ่มแรกของวันนั้นๆ ของพนักงานคนนั้นๆ ลบด้วยเวลาสุดท้ายในวันนั้น เพื่อมาแสดงในชีท ชั่วโมงทำงาน

ซึ่งข้อมูลในชีท DATA จะเพิ่มขึ้นเรื่อยๆ จากบนลงล่าง

ที่ D2=IF(COLUMNS($D2:D2)>($F$1),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy")))) คัดลอกไปทางขวาครับ

ที่ D3=IFERROR(LOOKUP(2,1/((INT(DATA!$A$2:$A$20)=D$2+0)*(DATA!$C$2:$C$20=$B3)),MOD(DATA!$A$2:$A$20,24*60*60))-INDEX(MOD(DATA!$A$2:$A$20,24*60*60),MATCH(1,INDEX((INT(DATA!$A$2:$A$20)=D$2+0)*($B3=DATA!$C$2:$C$20),0),0)),"") คัดลอกไปทั่วตารางครับ

-สูตร Run วันที่ตรงตามเงื่อนไขในส่วนของวันเริ่มต้นค่ะ แต่เมื่อคัดลอกไปทางขวาแล้ว ไม่หยุดตามวันสุดท้ายที่กำหนดค่ะ ยังสามารถลากต่อไปได้เรื่อยๆ ไม่มีที่สิ้นสุดค่ะ

-สูตรคำนวณชั่วโมงทำงานตรงตามเงื่อนไขที่กำหนดค่ะ
ขอรบกวนเพิ่มเงื่อนไขว่าหากเวลาเริ่มงานเป็นเวลาก่อน 12.00 น. และเวลาสุดท้ายของวันนั้นเป็นหลัง 13.00 น. ให้ลบข้อมูลนั้นออกไป 1 ชั่วโมง เนื่องจากไม่นับช่วงพักเที่ยง

รบกวนด้วยนะคะ

Rin wrote: ↑Sun Apr 15, 2018 4:33 pm -สูตร Run วันที่ตรงตามเงื่อนไขในส่วนของวันเริ่มต้นค่ะ แต่เมื่อคัดลอกไปทางขวาแล้ว ไม่หยุดตามวันสุดท้ายที่กำหนดค่ะ ยังสามารถลากต่อไปได้เรื่อยๆ ไม่มีที่สิ้นสุดค่ะ

-สูตรคำนวณชั่วโมงทำงานตรงตามเงื่อนไขที่กำหนดค่ะ
ขอรบกวนเพิ่มเงื่อนไขว่าหากเวลาเริ่มงานเป็นเวลาก่อน 12.00 น. และเวลาสุดท้ายของวันนั้นเป็นหลัง 13.00 น. ให้ลบข้อมูลนั้นออกไป 1 ชั่วโมง เนื่องจากไม่นับช่วงพักเที่ยง

รบกวนด้วยนะคะ

ปรับเป็นที่ D2=IF(COLUMNS($D2:D2)>SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>2)),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy")))) คัดลอกไปทางขวา

ที่ D3=IFERROR(LOOKUP(2,1/((INT(DATA!$A$2:$A$20)=D$2+0)*(DATA!$C$2:$C$20=$B3)),DATA!$A$2:$A$20)-INDEX(DATA!$A$2:$A$20,MATCH(1,INDEX((INT(DATA!$A$2:$A$20)=D$2+0)*($B3=DATA!$C$2:$C$20),0),0))-IF(AND(LOOKUP(2,1/((INT(DATA!$A$2:$A$20)=D$2+0)*(DATA!$C$2:$C$20=$B3)),MOD(DATA!$A$2:$A$20,1))>"13:00"+0,INDEX(MOD(DATA!$A$2:$A$20,1),MATCH(1,INDEX((INT(DATA!$A$2:$A$20)=D$2+0)*($B3=DATA!$C$2:$C$20),0),0))),"1:00"+0,0),"") คัดลอกไปทั่วตารางครับ

ขอบคุณมากๆ นะคะ สูตรใช้ประโยชน์ได้ตรงตามวัตถุประสงค์ค่ะ

รบกวนขอความรู้ จากสูตร Run วันที่ที่คุณ puriwutpokin ปรับให้มาล่าสุด

=IF(COLUMNS($D2:D2)>SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>2)),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy")))) คัดลอกไปทางขวา

อยากทราบว่า ช่วงไหนของสูตรที่บอกว่ายกเว้นวันที่ตรงกับวันจันทร์และอังคาร คะ

Rin wrote: ↑Sun Apr 15, 2018 11:30 pm รบกวนขอความรู้ จากสูตร Run วันที่ที่คุณ puriwutpokin ปรับให้มาล่าสุด

=IF(COLUMNS($D2:D2)>SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>2)),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy")))) คัดลอกไปทางขวา

อยากทราบว่า ช่วงไหนของสูตรที่บอกว่ายกเว้นวันที่ตรงกับวันจันทร์และอังคาร คะ

=IF(COLUMNS($D2:D2)>SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>2)),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"))))

ช่วงสีแดงเป็นช่วงเช็คว่าเป็นวันจันทร์กับวันอังคารหรือไม่ โดยใช้ WEEKDAY(ช่วงวัน,เลขที่)<3 ถ้าใช่ให้ใช้ค่า IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1) ตามนี้ครับ

จากสูตร

=IF(COLUMNS($D2:D2)>SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>2)),"",IF(WEEKDAY(TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy"),2)<3,IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),IFERROR(IF(WEEKDAY(C2+1,2)<3,C2+3,C2+1),TEXT(DATE(YEAR($D$1),MONTH($D$1),DAY($D$1)-1+COLUMNS($D2:D2)),"d-mmm-yy")))) คัดลอกไปทางขวา

ที่คุณ puriwutpokin ให้มาและช่วยอธิบายสูตร ซึ่งดิฉันพยายามปรับสูตร โดยใช้วันเริ่มต้นและวันสิ้นสุดตามที่กำหนด ให้ Run ไปเรื่อยๆ จนถึงวันสิ้นสุดเหหมือนเดิม แต่จะให้ยกเว้นเพียงแค่วันจันทร์ เพียงวันเดียว ดิฉันพยามยามปรับแล้วแต่ไม่ลงตัวค่ะ รบกวนช่วยชี้แนะด้วยนะคะ

ปรับสูตรที่ D2 เป็นตามด้านล่างครับ

=IFERROR(SMALL(IF(WEEKDAY(ROW(INDIRECT($D$1&":"&$F$1)),2)>1,ROW(INDIRECT($D$1&":"&$F$1))),COLUMNS($D2:D2)),"")

Ctrl+Shift+Enter > Copy ไปทางขวา

ขอบคุณมากๆ นะคะ

แล้วสูตรนี้ ที่ D3=IFERROR(LOOKUP(2,1/((INT(DATA!$A$2:$A$20)=D$2+0)*(DATA!$C$2:$C$20=$B3)),DATA!$A$2:$A$20)-INDEX(DATA!$A$2:$A$20,MATCH(1,INDEX((INT(DATA!$A$2:$A$20)=D$2+0)*($B3=DATA!$C$2:$C$20),0),0))-IF(AND(LOOKUP(2,1/((INT(DATA!$A$2:$A$20)=D$2+0)*(DATA!$C$2:$C$20=$B3)),MOD(DATA!$A$2:$A$20,1))>"13:00"+0,INDEX(MOD(DATA!$A$2:$A$20,1),MATCH(1,INDEX((INT(DATA!$A$2:$A$20)=D$2+0)*($B3=DATA!$C$2:$C$20),0),0))),"1:00"+0,0),"") คัดลอกไปทั่วตารางครับ

มีแบบใช้ Array หรือไม่คะ สูตรจะได้กระชับมากขึ้น

ตัวอย่างสูตรแบบ Array ครับ

=MAX(IF(INT(DATA!$A$2:$A$20)=D$2,IF(DATA!$C$2:$C$20=$B3,DATA!$A$2:$A$20)))-MIN(IF(INT(DATA!$A$2:$A$20)=D$2,IF(DATA!$C$2:$C$20=$B3,DATA!$A$2:$A$20)))-AND(MAX(IF(INT(DATA!$A$2:$A$20)=D$2,IF(DATA!$C$2:$C$20=$B3,MOD(DATA!$A$2:$A$20,1))))>13/24,MIN(IF(INT(DATA!$A$2:$A$20)=D$2,IF(DATA!$C$2:$C$20=$B3,MOD(DATA!$A$2:$A$20,1))))<12/24)/24

ขอบคุณมากๆ นะคะ