Excel Forum

DhitiBank wrote:ที่คุณ monkey34 บอกว่าคำตอบในเซลล์ F60 ต้องได้ 2 ใบนั้นออกจะขัดกับเงื่อนไขนะครับ เพราะ
1. 036/58 และ 038/58 ต่างมีวันที่ในคอลัมน์ F ตรงกับวันอาทิตย์ ดังนั้น สูตรของอาจารย์จะไม่นับเอกสาร 2 ใบนี้อยู่แล้ว
2. เงื่อนไขอีก 1 อย่างที่ไม่ได้แจ้งในกระทู้คือ ค่าในคอลัมน์ K หากต้องการให้นับเมื่อได้รับคนครบแล้ว ค่าในคอลัมน์ K ต้องเป็น 0 แต่เขียนในไฟล์ว่าต้องมากกว่าหรือเท่ากับค่าในคอลัมน์ D ซึ่งมันขัดกันครับ

ดังนั้น อยากให้คุณลองสรุปใหม่ครับ ว่ามีเงื่อนไข "ใดบ้าง" ที่จะใช้นับ และลองใส่ค่าที่ถูกต้องในเซลล์ F60 มาในไฟล์ด้วยเลยครับ จะได้คำนวณเปรียบเทียบได้

1. จากข้อมูลในคอลัมป์ F ตอนแรกจะยังเป็นการลบวันแบบรวมวันหยุดทั้งหมดข้อมูลเลยผิดพลาด ตอนนี้ผมนับวันและพิมพ์แล้ว อยากให้ช่วยบอกสูตรที่จะทำให้ได้มาซึ่งเลขพวกนั้นด้วยครับ

2. ขอโทษสำหรับข้อมูลขอแจ้งเปลี่ยนคอลัมป์ K เป็น คอลัมป์ J ครับ

3. ตอนนี้ในไฟล์ผมใช้วิธีนับวันและพิมพ์ช่วยแนะนำสูตรด้วยครับ

ขอบคุณครับ

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

ที่ F5 คีย์
=IF(E5="","",E5+7)
Enter

ที่ G5 ก่อนคีย์สูตร ให้กด Ctrl+1 แล้วเลือกรูปแบบตัวเลขเป็น Custom แล้วในช่องว่างให้คีย์
0;(0);
จากนั้นกด OK แล้วค่อยคีย์สูตร
=IF(F5="",0,F5-$L$3)
Enter

ที่ I5 จัดรูปแบบเหมือนกับ G5 แล้วค่อยคีย์สูตร
=IF(J5<D5,$L$3-E5,H5-E5)
Enter

จากนั้นคัดลอกลงล่างครับ (คัดลอกทั้งสูตร และรูปแบบ)

ที่ D60 คีย์
="ใบขอครบกำหนด "&SUMPRODUCT(--(TEXT(F5:F55,"mmyyyy")=TEXT(L3,"mmyyyy")))&" ใบ"
Enter

ที่ F60 คีย์
="ใบขอที่ปิดได้ทันกำหนด "&SUMPRODUCT(--(TEXT(F5:F55,"mmyyyy")=TEXT(L3,"mmyyyy")),--(TEXT(F5:F55,"[$-]ddd")<>"Sun"),
--(H5:H55<=F5:F55),--(J5:J55>=D5:D55),--(ISNA(MATCH(F5:F55,holiday!A2:A17,0))))&" ใบ"
Enter

ที่ H60 คีย์
=SUMPRODUCT(--(TEXT(F5:F55,"mmyyyy")=TEXT(L3,"mmyyyy")),--(TEXT(F5:F55,"[$-]ddd")<>"Sun"),
--(H5:H55<=F5:F55),--(J5:J55>=D5:D55),--(ISNA(MATCH(F5:F55,holiday!A2:A17,0))))/SUMPRODUCT(--(TEXT(F5:F55,"mmyyyy")=TEXT(L3,"mmyyyy")))*100
Enter

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

ที่ F5 คีย์
=IF(E5="","",E5+7)
Enter

ที่ G5 ก่อนคีย์สูตร ให้กด Ctrl+1 แล้วเลือกรูปแบบตัวเลขเป็น Custom แล้วในช่องว่างให้คีย์
0;(0);
จากนั้นกด OK แล้วค่อยคีย์สูตร
=IF(F5="",0,F5-$L$3)
Enter

ที่ I5 จัดรูปแบบเหมือนกับ G5 แล้วค่อยคีย์สูตร
=IF(J5<D5,$L$3-E5,H5-E5)
Enter

ใช่ครับต้องตัดวันอาทิตย์ และวันหยุด เนื่องจากมีการคิดคะแนนผลการทำงานทุกใบแยกตามใบเลยนะครับจำนวนวันที่ปรากฎทุกรายการต้องเป็นวันทำการไม่รวมวันหยุดครับ

ขอบคุณครับ

รออาจารย์และเพื่อนๆ ท่านอื่นครับ (ไม่รู้จะเขียนสูตรยังไงดี )

อ่อ ได้แล้วครับ
ที่ F5 คีย์
=IF(E5="","",WORKDAY.INTL(E5,7,11,holiday!$A$2:$A$17))

ที่ G5 คีย์
=IF(F5="",0,NETWORKDAYS.INTL($L$3,F5,11,holiday!$A$2:$A$17))

ที่ I5 คีย์
=IF(J5<D5,NETWORKDAYS.INTL(E5,$L$3,11,holiday!$A$2:$A$17),NETWORKDAYS.INTL(E5,H5,11,holiday!$A$2:$A$17))

แล้วคัดลอกลงไปยังเซลล์ที่เกี่ยวข้องครับ
สูตรด้านบนคิดจากการทำงาน 6 วัน/สัปดาห์นะครับ (หยุดอาทิตย์วันเดียว) และวันหยุดเพิ่มดังที่ระบุไว้ในชีท holiday

DhitiBank wrote:อ่อ ได้แล้วครับ
ที่ F5 คีย์
=IF(E5="","",WORKDAY.INTL(E5,7,11,holiday!$A$2:$A$17))

ที่ G5 คีย์
=IF(F5="",0,NETWORKDAYS.INTL($L$3,F5,11,holiday!$A$2:$A$17))

ที่ I5 คีย์
=IF(J5<D5,NETWORKDAYS.INTL(E5,$L$3,11,holiday!$A$2:$A$17),NETWORKDAYS.INTL(E5,H5,11,holiday!$A$2:$A$17))

แล้วคัดลอกลงไปยังเซลล์ที่เกี่ยวข้องครับ
สูตรด้านบนคิดจากการทำงาน 6 วัน/สัปดาห์นะครับ (หยุดอาทิตย์วันเดียว) และวันหยุดเพิ่มดังที่ระบุไว้ในชีท holiday

จากสูตรที่ให้มาสามารถใช้งานได้แล้วครับแต่ถ้าหากว่าจะไม่ใช้ WORKDAY.INTL() กับ NETWORKDAYS.INTL() พอมีวิธีอื่นไหมครับ เนื่องจากว่ามาประยุกต์ใช้กับโปรแกรมที่ผมใช้ไม่ได้นะครับ

ขอบคุณครับ

เอาไปประยุกต์ใช้กับโปแกรมอะไรครับ หากจำเป็นต้องใช้สูตรอื่น คงต้องรออาจารย์หรือเพื่อนๆ ท่านอื่นครับ

OpenOffice Calc นะครับ
อ่อ จากสูตรด้านบนผมพึ่งสังเกตครับว่าจำนวนวันจะนับเกินจากที่ต้องการ 1 วันคือมันเริ่มนับรวมตั้งแต่ [start_date] แต่ของผมจะไม่นับวันนี้นะครับ

เรื่องสูตรใน excel เอา start date +1 ก็ได้ครับ เพื่อแก้ปัญหาวันเกิน
ส่วน OpenOffice ผมไม่ได้ใช้ เลยไม่แน่ใจว่าจะสามารถใช้สูตรใดได้ครับ

ใน OpenOffice น่าจะมี Sumproduct ดังนั้น ลองคีย์แบบนี้ครับ

F5
=E5+7+SUMPRODUCT(--(TEXT(E5+ROW(1:7),"[$- ]ddd")="Sun"))+SUMPRODUCT(--ISNUMBER(MATCH(E5+ROW(1:7),holiday!$A$2:$A$17,0)))
Enter

G5
=(F5-$L$3)+SUMPRODUCT(--(TEXT(F5+ROW(INDIRECT("1:"&$L$3-F5)),"[$- ]ddd")="Sun"))+SUMPRODUCT(--ISNUMBER(MATCH(F5+ROW(INDIRECT("1:"&$L$3-F5)),holiday!$A$2:$A$17,0)))
Enter

ลองดูว่าตรงกับที่นับด้วยมือหรือไม่ครับ หากตรง ลองเอาไปประยุกต์กับเซลล์ I5 ดูครับ เพื่อความสนุกสนาน

F5
=E5+7+SUMPRODUCT(--(TEXT(E5+ROW(1:7),"[$- ]ddd")="Sun"))+SUMPRODUCT(--ISNUMBER(MATCH(E5+ROW(1:7),holiday!$A$2:$A$17,0)))

ROW(1:7) หมายถึงอะไรหรอครับ

คลุม Row(1:7) ในสูตรแล้วกดแป้น F9 แล้วสังเกตดูผลว่าได้เป็นอะไร นั่นคือคำตอบครับ

ขอบคุณอาจารย์มากครับที่มาช่วยตอบ วันนี้ขับรถทั้งวันเลย T-T

และขอขยายความนิดหน่อยครับ
เนื่องจากไม่ต้องการใช้สูตรสำเร็จรูป ผมก็เลยคิดว่า

วันทำการล่วงหน้า 7 วัน = ([start date] +7) + (จำนวนวันอาทิตย์ในช่วง [start date] +7) + (จำนวนวันหยุดตามประกาศในช่วง [start date] +7)

(น่าจะใช่นะครับ) ส่วนที่คุณถามอยู่ในช่วงที่สองคือ หาวันอาทิตย์ ผมก็เลยเอา

start date + row(1:7)

พอคำนวณมาแล้วก็จะประมาณนี้ครับ

start date + {1,2,3,4,5,6,7}

ผลคือ มันจะเป็นวันที่จำนวน 7 วันเรียงกัน แล้วก็เอามาเข้าสูตร Text เพื่อเปลี่ยนเป็นชื่อวันแบบย่อในภาษาอังกฤษ หาว่าวันอาทิตย์มีกี่วัน

เอ... พอมาเขียนอธิบายแบบนี้ วันที่ 7 วันเรียงกัน มันก็จะมีวันอาทิตย์แค่ 1 วัน... สูตรตรงนี้ เปลี่ยนเป็น +1 ก็จบแล้วนี่นา [T-T]