Page 1 of 1
รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Thu Sep 16, 2021 2:50 pm
by tingpcj
รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
เงื่อนไขตามไฟล์แนบครับ
เวลาเข้างาน 8:30
ผลรวมการคำนวณเข้าสายหลัง 8:30-9:00
ผลรวมการคำนวณเข้าสายหลัง 9:00
ขอบพระคุณครับ
EX TIME.xlsx
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Thu Sep 16, 2021 3:53 pm
by puriwutpokin
AG3=MAX(SUMPRODUCT(--(WEEKDAY(B$2:AF$2,3)<5),IFERROR(TEXT(B3:AF3,"h:mm")-"8:30",0)),0)
AH3=MAX(SUMPRODUCT(--(WEEKDAY(B$2:AF$2,3)>4),IFERROR(TEXT(B3:AF3,"h:mm")-"9:00",0)),0)
และ Format เซลเป็นเวลาด้วยครับ
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Thu Sep 16, 2021 4:56 pm
by tingpcj
puriwutpokin wrote: Thu Sep 16, 2021 3:53 pm
AG3=MAX(SUMPRODUCT(--(WEEKDAY(B$2:AF$2,3)<5),IFERROR(TEXT(B3:AF3,"h:mm")-"8:30",0)),0)
AH3=MAX(SUMPRODUCT(--(WEEKDAY(B$2:AF$2,3)>4),IFERROR(TEXT(B3:AF3,"h:mm")-"9:00",0)),0)
และ Format เซลเป็นเวลาด้วยครับ
ผลลัพธ์ที่ได้ยังไม่ตรงกับที่ต้องการครับ เพราะผลลัพธ์ที่ได้ยังไม่ถูกต้องครับ
เช่น ช่อง สายหลัง 8:30-9:00 น. เวลาเข้างานสมมุติว่าเป็น 10:00 ผลลัพธ์จะเป็น 90 ครับ ซึ่งที่ถูกต้องคือ 30 นาที เป็นต้นครับ และเวลาที่เหลือจะเป็นเวลาสายหลัง 9:00 น. เป็นผลลัพธ์ 60 นาที เป็นต้นครับ
รบกวนด้วยนะครับผม
ขอบคุณครับสำหรับคำตอบครับผม (สูตรที่พี่ให้มานั้นต้องกด Ctrl+Shift+Enter ด้วยครับ)
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Thu Sep 16, 2021 10:54 pm
by norkaz
...
AG3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"8:30")*(WEEKDAY($B$2:$AF$2)<>1)*(WEEKDAY($B$2:$AF$2)<>7)*($B3:$AF3-"8:30"),0))*1440,0)
Ctrl + Shift + Enter
AH3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"9:00")*((WEEKDAY($B$2:$AF$2)=1)+(WEEKDAY($B$2:$AF$2)=7))*($B3:$AF3-"9:00"),0))*1440,0)
Ctrl + Shift + Enter
Norkaz
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 8:54 am
by tingpcj
norkaz wrote: Thu Sep 16, 2021 10:54 pm
...
AG3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"8:30")*(WEEKDAY($B$2:$AF$2)<>1)*(WEEKDAY($B$2:$AF$2)<>7)*($B3:$AF3-"8:30"),0))*1440,0)
Ctrl + Shift + Enter
AH3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"9:00")*((WEEKDAY($B$2:$AF$2)=1)+(WEEKDAY($B$2:$AF$2)=7))*($B3:$AF3-"9:00"),0))*1440,0)
Ctrl + Shift + Enter
Norkaz
ผลลัพธ์ออกมาไม่ถูกต้อง ผมไม่แน่ใจว่าต้องตั้งค่าตรงช่องไหนอีกหรือไม่ครับ ตามไฟล์แนบครับผม
ขอบคุณครับ
EX TIME.xlsx
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 9:33 am
by norkaz
...
Format Cells เป็นแบบ General
สูตรนี้ควรถูกต้อง ตามโจทย์ ที่กำหนดมาครับ
Norkaz
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 10:44 am
by tingpcj
norkaz wrote: Fri Sep 17, 2021 9:33 am
...
Format Cells เป็นแบบ General
สูตรนี้ควรถูกต้อง ตามโจทย์ ที่กำหนดมาครับ
Norkaz
ขอบพระคุณครับ ตามสูตรที่ให้มานั้นถูกต้องครับ ต้องจัดรูปแบบเซลล์เป็น General ครับ
*แต่ผลลัพธ์ที่ได้นั้น ตามตัวอย่างบรรทัดที่ 3 ผลลัพธ์ที่ได้ที่ช่อง AG3 นั้น ผลลัพธ์ที่ถูกต้องควรเป็น 30 นาทีครับ เพราะคิดนาทีสายถึง 9:00 น. ส่วนที่เกินจาก 9:00 น. ต้องนำนาทีที่เกินไปรวมที่ช่อง AH3 ครับผม
**ดังนั้นในแต่ละวันของการสายผลรวมในแต่ละช่องช่วงที่มาสายช่วงเวลา 8:30-9:00 จะไม่เกิน 30 นาทีต่อวัน นาทีที่เกินจาก 9:00 น. นั้น จะต้องนำไปรวมช่อง AH3 ครับ
***ตามตัวอย่างไฟล์แนบที่ผมแนบมาในครั้งนี้ ผลลัพธ์ที่ถูกต้องคือ AG3 = 40 และช่อง AH = 68
รบกวนพี่ๆทุกท่านให้คำแนะนำผมด้วยครับผม
ขอบคุณครับผม
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 11:01 am
by norkaz
...
ใช้สูตรเดิม เอามา บวก ลบกัน แต่ขอใช้ คอลัมน์มาช่วย เพื่อไม่ให้สูตร สลับซับซ้อนมาก
AI3 (Helper)
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"8:30")*(WEEKDAY($B$2:$AF$2)<>1)*(WEEKDAY($B$2:$AF$2)<>7)*($B3:$AF3-"8:30"),0))*1440,0)
Ctrl + Shift + Enter
AG3
=MIN(30,AI3)
AH3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"9:00")*((WEEKDAY($B$2:$AF$2)=1)+(WEEKDAY($B$2:$AF$2)=7))*($B3:$AF3-"9:00"),0))*1440,0)+AI3-AG3
Ctrl + Shift + Enter
Norkaz
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 11:34 am
by tingpcj
norkaz wrote: Fri Sep 17, 2021 11:01 am
...
ใช้สูตรเดิม เอามา บวก ลบกัน แต่ขอใช้ คอลัมน์มาช่วย เพื่อไม่ให้สูตร สลับซับซ้อนมาก
AI3 (Helper)
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"8:30")*(WEEKDAY($B$2:$AF$2)<>1)*(WEEKDAY($B$2:$AF$2)<>7)*($B3:$AF3-"8:30"),0))*1440,0)
Ctrl + Shift + Enter
AG3
=MIN(30,AI3)
AH3
=ROUND(SUM(IFERROR(($B3:$AF3<>"")*($B3:$AF3>--"9:00")*((WEEKDAY($B$2:$AF$2)=1)+(WEEKDAY($B$2:$AF$2)=7))*($B3:$AF3-"9:00"),0))*1440,0)+AI3-AG3
Ctrl + Shift + Enter
Norkaz
ต้องขออภัยครับ ผมอาจจะอธิบายแบบงงไปหน่อยครับ แต่ผลลัพธ์ที่ได้ยังไม่ได้ตามโจทย์ครับ (โจทย์ผมอาจจะงงไปหน่อยครับ)
*AG3 คือ ผลรวมในแต่ละช่อง ในแต่ละวันของการสาย ช่วงที่มาสายช่วงเวลา 8:30-9:00 จะไม่เกิน 30 นาทีต่อวัน (*ต่อวันนะครับ*)
นาทีที่เกินจาก 9:00 น.ในแต่ละวันนั้น จะต้องนำไปรวมช่อง AH3 ครับ
** ช่อง AG3 คือช่องผลรวมของจำนวนนาทีที่มาสายช่วงเวลา 8:30-9:00 เอาเวลาสายช่วงนี้มารวมกันทุกวันครับ ดังนั้น ช่อง AG3 ผลรวมจะเป็นเท่าไหร่ก็ได้ครับ ไม่ใช่ไม่เกิน 30 ครับผม
***ตามตัวอย่างไฟล์แนบที่ผมแนบมาในครั้งนี้ ผลลัพธ์ที่ถูกต้องคือ AG3 = 40 และช่อง AH3 = 68 (จะเห็นได้ว่าช่อง AG3 มีค่าเกิน 30 นะครับ)
รบกวนพี่ๆทุกท่านให้คำแนะนำผมด้วยครับผม
ขอบคุณครับผม
EX TIME.xlsx
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 12:07 pm
by norkaz
...
ยังไม่เข้าใจในสิ่งที่ท่านเจ้าของคำถาม สอบถามมา
1) กรุณา ช่วย ลบสูตรที่เขียนไปทั้งหมดออกไป
2) ใส่คำตอบ ที่ต้องการมาในไฟล์ใหม่ มาสัก 2-3 แถว
หลังจากนั้น ค่อยๆ มาถาม มาตอบกันไปว่า สิ่งที่ท่านเจ้าของคำถาม ต้องการคืออะไร
ท่านอยู่หน้างาน งานนี้คืองานของท่าน ท่านต้องอธิบายสิ่งที่ท่านต้องการ ออกมาให้ชัดเจน
หากท่านไม่สามารถอธิบายสิ่งที่ท่านต้องการออกมา ให้ ผู้อื่นทราบ คำตอบที่ได้ไป ก็มักจะไม่ถูกต้อง
การตอบคำถามในฟอรั่ม สมาชิกที่ เวียนกันมาตอบคำถามนั้น มุ่งไปที่ โจทย์เป็นหลัก แม้คำตอบจะขัดแย้งกับความเป็นจริง แต่หาก ตรงกับโจทย์ คำตอบนั้นย่อม ถูกต้องตามเจตนา ของผู้สอบถาม
แต่ หากโจทย์ที่ท่าน เขียนมาไม่กระจ่าง คำตอบที่ท่านได้ไป จากเพื่อนสมาชิก ก็คลุมเครือ ไม่สามารถนำไปใช้ได้ตามที่ท่านต้องการอย่างแท้จริงครับ
Norkaz
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 12:44 pm
by puriwutpokin
ลองดูครับ
AG3=SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"8:30">0)*(B3:AF3-"8:30"),))-SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))
AH3=SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)>4)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))+SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))
All Ctrl + Shift + Enter
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 1:20 pm
by tingpcj
norkaz wrote: Fri Sep 17, 2021 12:07 pm
...
ยังไม่เข้าใจในสิ่งที่ท่านเจ้าของคำถาม สอบถามมา
1) กรุณา ช่วย ลบสูตรที่เขียนไปทั้งหมดออกไป
2) ใส่คำตอบ ที่ต้องการมาในไฟล์ใหม่ มาสัก 2-3 แถว
หลังจากนั้น ค่อยๆ มาถาม มาตอบกันไปว่า สิ่งที่ท่านเจ้าของคำถาม ต้องการคืออะไร
ท่านอยู่หน้างาน งานนี้คืองานของท่าน ท่านต้องอธิบายสิ่งที่ท่านต้องการ ออกมาให้ชัดเจน
หากท่านไม่สามารถอธิบายสิ่งที่ท่านต้องการออกมา ให้ ผู้อื่นทราบ คำตอบที่ได้ไป ก็มักจะไม่ถูกต้อง
การตอบคำถามในฟอรั่ม สมาชิกที่ เวียนกันมาตอบคำถามนั้น มุ่งไปที่ โจทย์เป็นหลัก แม้คำตอบจะขัดแย้งกับความเป็นจริง แต่หาก ตรงกับโจทย์ คำตอบนั้นย่อม ถูกต้องตามเจตนา ของผู้สอบถาม
แต่ หากโจทย์ที่ท่าน เขียนมาไม่กระจ่าง คำตอบที่ท่านได้ไป จากเพื่อนสมาชิก ก็คลุมเครือ ไม่สามารถนำไปใช้ได้ตามที่ท่านต้องการอย่างแท้จริงครับ
Norkaz
ต้องขออภัยครับ ที่ผมอธิบายได้ไม่ชัดเจนเท่าที่ควรครับ
ขอบคุณท่านครับที่ช่วยเหลือครับผม ท่าน puriwutpokin ได้ให้คำตอบเรียบร้อยแล้วครับผม
ขอบพระคุณครับ
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 1:22 pm
by tingpcj
puriwutpokin wrote: Fri Sep 17, 2021 12:44 pm
ลองดูครับ
AG3=SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"8:30">0)*(B3:AF3-"8:30"),))-SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))
AH3=SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)>4)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))+SUMPRODUCT(IFERROR((WEEKDAY(B$2:AF$2,3)<5)*(B3:AF3-"9:00">0)*(B3:AF3-"9:00"),))
All Ctrl + Shift + Enter
สูตรนี้ใช้ได้เลยครับ ขอบพระคุณมากครับผม (จัดรูปแบบเซลล์เป็น [m])
ขอบพระคุณครับผม
Re: รบกวนถามสูตรคำนวณเวลาเข้างานสายแบบมีเงื่อนไขครับ (นาที)
Posted: Fri Sep 17, 2021 1:56 pm
by Bo_ry
AG3
=ROUND(SUMPRODUCT(TEXT(N(+B3:AF3)*24-8.5,"[>0.5]\.5;[<0]\0;general")*(WEEKDAY($B$2:$AF$2,2)<6))*60,4)
AG4
=ROUND(SUMPRODUCT(TEXT(N(+B3:AF3)*24-9,"[<0]\0;general")*60),4)
Format เป็น Number