Snasui Excel Forum

Posted: **Sun Feb 26, 2012 11:08 am**

ผมพยายามหากระทู้เก่าๆ แกะของเก่าๆ มาไกลได้เท่านี้ครับ ตอนนี้ติดอยู่ตรงนี้ครับ

เงื่อนไขในที่นี้คือ ให้คำนวนเวลาเฉพาะของวันอาทิตย์ ถ้าหากยังไม่มีการคีย์ข้อมูลให้แสดงค่าเป็น 0
ในไฟล์ตัวอย่างของผม เวลายังไม่คีย์ข้อมูล มันแสดงเป็น 9 แทนครับ

Posted: **Sun Feb 26, 2012 11:16 am**

ลองตามนี้ครับ

ในเซลล์ M7 คีย์

=IF(M4="",0,IF(WEEKDAY(M3,2)=7,MIN($K$1,M5)-MAX(M4,$I$1)-AND(M4<12/24,M5>=13/24)/24,0))

Enter > Copy ไปยังเซลล์ต่าง ๆ ที่เกี่ยวข้อง สูตรด้านบนนี้ยังไม่สนใจว่าเป็นวันใด ถ้าช่อง In เป็นเซลล์ว่างก็จะให้ผลลัพธ์เป็น 0 ครับ

Posted: **Sun Feb 26, 2012 11:34 am**

ขอบคุณมาครับ ใช้งานได้แล้วครับ

ถ้าหากผมสงสัยอะไรเพิ่มเติม ผมควรโพสต่อในกระทู้นี้ หรือ โพสกระทู้ใหม่ครับ ?
แต่ตอนนี้ยังไม่มีอะไรสงสัยครับ

Posted: **Sun Feb 26, 2012 11:36 am**

กรณีเป็นเรื่องเดียวกันและต่อเนื่องกันให้ถามในกระทู้เดิม เพื่อสะดวกต่อการติดตามทั้งเจ้าของกระทู้เองและผู้สนใจครับ แต่หากเป็นเรื่องใหม่ก็ตั้งกระทู้ใหม่เลยครับ

Posted: **Mon Feb 27, 2012 8:25 pm**

ติดปัญหาเพิ่มอีก 3 ข้อครับตอนนี้
1.ตอนนี้ติดปัญหาเรื่อง ทำงานข้ามวันครับ
เวลาทำงานวันอาทิตย์จะคิดเป็น OT2 8 ชม.และ นอกเหนือจาก 8 ชม. จะเป็น OT3
แต่ติดปัญหาอยู่ตรงที่ว่า ช่วงเวลา 20:00 - 05:00 หักออกเวลาพักต้องเป็น 8 ชม. แต่ในสูตรคำนวนได้ 9 ชม. แต่ถ้าเวลาช่วงเช้าไม่ข้ามวันไม่มีปัญหาครับ

2.ตั้งค่าเงื่อนไขถ้าค่าในเซลส์เป็น 0 ตัวหนังสือต้องเป็นสีขาว แต่ทีนี้เวลาเราคีย์ข้อมูลลงไป ค่าออกมาเป็น 0 จริง แต่เป็นตัวหนังสือสีดำแทนครับ

3.ตั้งเงื่อนไข (COUNT IF) ไว้ว่าหากเลิกงานก่อนเวลา จะนับเป็น 1 ไว้ แต่มีปัญหาเวลาเอามารวมกับสูตรของกะกลางคืนครับ

ขอบคุณครับ

Posted: **Mon Feb 27, 2012 9:53 pm**

ค่อย ๆ ถามตอบกันไปนะครับ

จากสูตรในเซลล์ F15 คือ

=IF(F12="",0,IF(WEEKDAY(F11,2)=7,MIN($K$9,F13)-MAX(F12,$I$9)-AND(F12<12/24,F13>=13/24)/24,0))+(F13<F12)

ที่ผมระบายสีตัวอักษรไว้นั้นมีเงื่อนไขว่าอย่างไรครับ

สำหรับเซลล์ B15 ผลลัพธ์คือ 1 ไม่ใช่ 0 จึงไม่ถูกจัด Format ให้เป็นตัวอักษรสีขาว

สำหรับข้อ 3 ผลลัพธ์ที่ต้องการคือค่าใด และทำไมจึงได้ค่านั้นครับ

Posted: **Mon Feb 27, 2012 10:32 pm**

snasui wrote: ค่อย ๆ ถามตอบกันไปนะครับ

จากสูตรในเซลล์ F15 คือ

=IF(F12="",0,IF(WEEKDAY(F11,2)=7,MIN($K$9,F13)-MAX(F12,$I$9)-AND(F12<12/24,F13>=13/24)/24,0))+(F13<F12)

ที่ผมระบายสีตัวอักษรไว้นั้นมีเงื่อนไขว่าอย่างไรครับ

จริงๆสูตรนี้ผมแกะมาจากที่คุณคนควนเคยตอบคนอื่นไว้ครับ เหมือนกับว่ามีเงื่อนไข หักเวลาพักออก 1 ชม. น่าจะมีปัญหาเรื่องเวลาข้ามวันใช่หรือเปล่าครับ? และถ้าหากเวลาพักคือ 0:00 - 01:00 ผมต้องใช้สูตรยังไงเหรอครับ?

สำหรับเซลล์ B15 ผลลัพธ์คือ 1 ไม่ใช่ 0 จึงไม่ถูกจัด Format ให้เป็นตัวอักษรสีขาว

[รับทราบครับ]

สำหรับข้อ 3 ผลลัพธ์ที่ต้องการคือค่าใด และทำไมจึงได้ค่านั้นครับ

ผลลัพธ์ที่ต้องการคือแค่นับจำนวนช่อง OUT(ROW 5 และ 13)ที่ออกก่อนเวลา(กะเช้า 17:00 กะดึก 5:00)ว่าจำนวนกี่วันครับ

Posted: **Mon Feb 27, 2012 10:44 pm**

walrus wrote:
snasui wrote: ค่อย ๆ ถามตอบกันไปนะครับ

จากสูตรในเซลล์ F15 คือ

=IF(F12="",0,IF(WEEKDAY(F11,2)=7,MIN($K$9,F13)-MAX(F12,$I$9)-AND(F12<12/24,F13>=13/24)/24,0))+(F13<F12)

ที่ผมระบายสีตัวอักษรไว้นั้นมีเงื่อนไขว่าอย่างไรครับ
จริงๆสูตรนี้ผมแกะมาจากที่คุณคนควนเคยตอบคนอื่นไว้ครับ เหมือนกับว่ามีเงื่อนไข หักเวลาพักออก 1 ชม. น่าจะมีปัญหาเรื่องเวลาข้ามวันใช่หรือเปล่าครับ? และถ้าหากเวลาพักคือ 0:00 - 01:00 ผมต้องใช้สูตรยังไงเหรอครับ?

สำหรับปัญหาเรื่องข้ามวันตัวที่แก้ปัญหาคือสูตรนี้ครับ +(F13<F12) ซึ่งเป็นชุดสุดท้ายของสูตรด้านบน

สำหรับ AND(F12<12/24,F13>=13/24)/24 หมายถึงว่า เวลาใน F2 น้อยกว่า 12:00 และมากกว่าหรือเท่ากับ 13:00 หรือไม่ ถ้าใช่ก็จะได้ค่า True และนำ True (True กับ 1 มีค่าเท่ากัน) ไปหารด้วย 24 เพื่อให้ได้ค่าเท่ากับ 1 ชม. ที่ใช้สูตรนี้เพื่อหาเวลาการพักเที่ยง ถ้าเข้าเงื่อนไขคือพัก ไม่เข้าเงื่อนไขคือไม่พัก เช่นนี้เป็นต้นครับ

ผลลัพธ์ที่ต้องการคือแค่นับจำนวนช่อง OUT(ROW 5 และ 13)ที่ออกก่อนเวลา(กะเช้า 17:00 กะดึก 5:00)ว่าจำนวนกี่วันครับ

ช่วยแจ้งผลลัพธ์ที่ถูกต้องมาด้วยครับ จะได้คำนวณเปรียบเทียบได้

Posted: **Mon Feb 27, 2012 11:21 pm**

snasui wrote:
ผลลัพธ์ที่ต้องการคือแค่นับจำนวนช่อง OUT(ROW 5 และ 13)ที่ออกก่อนเวลา(กะเช้า 17:00 กะดึก 5:00)ว่าจำนวนกี่วันครับ
ช่วยแจ้งผลลัพธ์ที่ถูกต้องมาด้วยครับ จะได้คำนวณเปรียบเทียบได้

ผลลัพธ์ก็คือ ใน Q20 =2 แต่จริงๆแล้วต้องเป็นจำนวน = 3 (ที่ไฮไลท์สีส้มไว้)ครับ
ทีนี้มีคำถามเพิ่มอีก 1 ข้อครับ ใน H14 เข้างาน 20:00 แต่เลิกงาน 22:00 เท่ากับใช้เวลาจริงๆแค่ 2 ชม. แต่คำนวนออกมาให้ว่าทำโอทีไป 16 ชม. 30 นาทีครับ

Posted: **Mon Feb 27, 2012 11:36 pm**

walrus wrote:ผลลัพธ์ก็คือ ใน Q20 =2 แต่จริงๆแล้วต้องเป็นจำนวน = 3 (ที่ไฮไลท์สีส้มไว้)ครับ

จากสูตรนี้

=COUNTIF(D5:AH5,"<17:00")+COUNTIF(B13:AF13,"<5:00")

ผลลัพธ์จะได้ 2 ครับ โดย =COUNTIF(D5:AH5,"<17:00") ได้ผลลัพธ์เป็น 1 ค่าที่เข้าเงื่อนไขคือ N5

ส่วน COUNTIF(B13:AF13,"<5:00") ได้ผลลัพธ์เป็น 1 ค่าที่เข้าเงื่อนไขคือ D13 ไม่ทราบว่ามีค่าใดเข้าเงื่อนไขอีกจึงควรจะได้เป็น 3 ครับ

walrus wrote:ทีนี้มีคำถามเพิ่มอีก 1 ข้อครับ ใน H14 เข้างาน 20:00 แต่เลิกงาน 22:00 เท่ากับใช้เวลาจริงๆแค่ 2 ชม. แต่คำนวนออกมาให้ว่าทำโอทีไป 16 ชม. 30 นาทีครับ

จากสูตร =IF(H13-$N$9<=0,0,IF(WEEKDAY(H11,2)<6,H13-$N$9,0)) ไม่ได้นำ 22:00 ตั้งแล้วหักด้วย 20:00 แต่เป็นการนำ 22:00 ตั้งแล้วหักด้วย 5:30 ครับ

Posted: **Mon Feb 27, 2012 11:50 pm**

snasui wrote:
walrus wrote:ผลลัพธ์ก็คือ ใน Q20 =2 แต่จริงๆแล้วต้องเป็นจำนวน = 3 (ที่ไฮไลท์สีส้มไว้)ครับ
จากสูตรนี้

=COUNTIF(D5:AH5,"<17:00")+COUNTIF(B13:AF13,"<5:00")

ผลลัพธ์จะได้ 2 ครับ โดย =COUNTIF(D5:AH5,"<17:00") ได้ผลลัพธ์เป็น 1 ค่าที่เข้าเงื่อนไขคือ N5

ส่วน COUNTIF(B13:AF13,"<5:00") ได้ผลลัพธ์เป็น 1 ค่าที่เข้าเงื่อนไขคือ D13 ไม่ทราบว่ามีค่าใดเข้าเงื่อนไขอีกจึงควรจะได้เป็น 3 ครับ

ค่าที่ H13 ครับ

walrus wrote:ทีนี้มีคำถามเพิ่มอีก 1 ข้อครับ ใน H14 เข้างาน 20:00 แต่เลิกงาน 22:00 เท่ากับใช้เวลาจริงๆแค่ 2 ชม. แต่คำนวนออกมาให้ว่าทำโอทีไป 16 ชม. 30 นาทีครับ
จากสูตร =IF(H13-$N$9<=0,0,IF(WEEKDAY(H11,2)<6,H13-$N$9,0)) ไม่ได้นำ 22:00 ตั้งแล้วหักด้วย 20:00 แต่เป็นการนำ 22:00 ตั้งแล้วหักด้วย 5:00 ครับ
พอมีทางที่จะทำให้ค่าใน H14 มีค่าเป็น 0 ได้หรือเปล่าครับ ถ้าหากค่าใน H13 นั้นอยู่ในช่วงเวลา 20:00-23:59

Posted: **Mon Feb 27, 2012 11:56 pm**

ค่า H13 คือ 22:00 ไม่ได้น้อยกว่า 5:00 จึงไม่เข้าเงื่อนไขครับ

กรณีต้องการให้ H14 มีค่าเป็น 0 หาก H13 อยู่ในช่วง 20:00-23:59 สามารถปรับสูตรเดิมได้เป็น

=IF(And(h13>=20/24,h13<1),0,If(H13-$N$9<=0,0,IF(WEEKDAY(H11,2)<6,H13-$N$9,0)))

Posted: **Tue Feb 28, 2012 12:22 am**

snasui wrote: ค่า H13 คือ 22:00 ไม่ได้น้อยกว่า 5:00 จึงไม่เข้าเงื่อนไขครับ

ตรงนี้แก้ปัญหาเรียบร้อยแล้วครับ

กรณีต้องการให้ H14 มีค่าเป็น 0 หาก H13 อยู่ในช่วง 20:00-23:59 สามารถปรับสูตรเดิมได้เป็น

=IF(And(h13>=20/24,h13<1),0,If(H13-$N$9<=0,0,IF(WEEKDAY(H11,2)<6,H13-$N$9,0)))

ใส่สูตรนี้เข้าไปแล้ว excel ฟ้องดังรูปครับ

errormsg.jpg

Posted: **Tue Feb 28, 2012 7:12 am**

ผมคีย์ตกไป ได้แก้สูตรให้แล้วตามโพสต์ตามด้านบน

ยกมาอีกทีตามด้านล่างนี้ครับ

snasui wrote: =IF(And(h13>=20/24,h13<1),0,If(H13-$N$9<=0,0,IF(WEEKDAY(H11,2)<6,H13-$N$9,0)))

Posted: **Tue Feb 28, 2012 4:33 pm**

ตอนนี้ติดปัญหาอยู่ตรงการรวมจำนวนเวลาครับ ใช้สูตร SUM
ที่เซลส์ F20 ค่าในสูตรที่ถูกต้อง คือ 16 แต่แสดงออกมาเป็น 256
(รวมจาก F7=8:00 และ T15=8:00)

ขอบคุณครับ

OT_REV04.xlsx

Posted: **Tue Feb 28, 2012 5:32 pm**

ปรับฟอร์แมตเซลล์ จาก [h]:mm เป็น h:mm ดูครับ

Posted: **Tue Feb 28, 2012 6:17 pm**

bank9597 wrote: ปรับฟอร์แมตเซลล์ จาก [h]:mm เป็น h:mm ดูครับ

ขอบคุณครับ ถ้าหากเปลี่ยนตามที่บอกแล้ว กรณีเกิน 24 ชม.ขึ้นไปจะเริ่มเป็น ชม.ที่ 25 หรือ ชม.ที่ 0:00 ครับ?

แต่ตอนนี้ติดปัญหาอีกว่าจำนวนที่ต้องคูณด้วย 16 แต่ผลลัพธ์กลับได้เหมือนกลับว่าคูณด้วย 256 แทนครับทั้งที่ในเซลก็แสดงค่าแค่ 16 ครับ

OT_REV05.xlsx

Posted: **Tue Feb 28, 2012 7:33 pm**

walrus wrote: ตอนนี้ติดปัญหาอยู่ตรงการรวมจำนวนเวลาครับ ใช้สูตร SUM
ที่เซลส์ F20 ค่าในสูตรที่ถูกต้อง คือ 16 แต่แสดงออกมาเป็น 256
(รวมจาก F7=8:00 และ T15=8:00)

ขอบคุณครับ

คงจะเป็น 16 ไปไม่ได้ครับ เพราะว่ามีเซลล์จำนวนมากที่ไม่ได้มีค่าเป็น 0 นั่นคือ N15:S15, U15:Y15 แสดงว่าดักเงื่อนไขไว้ยังไม่ครบครับ

Posted: **Tue Feb 28, 2012 7:35 pm**

walrus wrote:
bank9597 wrote: ปรับฟอร์แมตเซลล์ จาก [h]:mm เป็น h:mm ดูครับ
ขอบคุณครับ ถ้าหากเปลี่ยนตามที่บอกแล้ว กรณีเกิน 24 ชม.ขึ้นไปจะเริ่มเป็น ชม.ที่ 25 หรือ ชม.ที่ 0:00 ครับ?

แต่ตอนนี้ติดปัญหาอีกว่าจำนวนที่ต้องคูณด้วย 16 แต่ผลลัพธ์กลับได้เหมือนกลับว่าคูณด้วย 256 แทนครับทั้งที่ในเซลก็แสดงค่าแค่ 16 ครับ

ใช้ [h]:mm ถูกแล้วครับแต่ต้องแก้ไขสูตรให้ถูกต้องก่อนครับ

Posted: **Tue Feb 28, 2012 8:47 pm**

อาจารย์จัดหนักผมออกอากาศแล้ว

ขออภัยผู้ถามด้วยครับ พอดีรีบตอบ เลยต้องแบบไม่ยั้งคิด (คิดว่าตัวเองเก่ง) กั่ก กั่ก

Snasui Excel Forum

เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?

Re: เงื่อนไขซ้อนกัน 2 ชั้น (IF) ต้องทำยังไงครับ?