รบกวนช่วยคิดวิธีการแปลงเวลา เป็น ตัวเลข เพื่อนำไปคำนวณโอที

[snasui]

ช่วยเขียนคำตอบที่ต้องการลงในเซลล์ด้วยครับว่า เซลล์ใดควรได้คำตอบเป็นค่าใด จะได้เข้าถึงปัญหาได้โดยไวครับ เงื่อนไขต่าง ๆ ให้คีย์ลงในไฟล์ให้หมดครับ จะได้สะดวกต่อการดูเพื่อสร้างฟังก์ชั่นหรือใช้เปรียบเทียบ

[kanyavee]

อาจารย์ ได้โปรดช่วยกันต่อไปด้วยค่ะ

คอลัมน์สีแดง คือคำตอบที่ต้องการค่ะ

เริ่มคำนวณดังนี้
07.30-16.30 = 8 ชม. เวลาพัก 11.30-12.30 ค่ะ
เฉพาะการคำนวณในวันหยุดเท่านั้นค่ะ

[kanyavee]

อาจารย์คะ ลืมแจ้งไปคือ โอทีจะคิดทุกๆ ครึ่ง ชม. ค่ะ

[snasui]

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

ที่ H4 คีย์

=IF(OR(D4="",E4="",K4=""),"",LOOKUP(ROUNDUP(D4/"0:30",0)*"0:30",$AN$23:$AN$37,$AK$23:$AK$37))

Enter > Copy ลงด้านล่าง

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

[kanyavee]

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

อาจารย์คะ ต้องขอโทษด้วยค่ะ หนูเพิ่งเข้าทำงานใหม่ สำหรับ Excel หนูทำได้เกี่ยวกับพิมพ์งาน หรือ คำนวณเล็ก ๆ น้อยๆ ค่ะ ถ้าเป็นสูตรยากๆ ยังไม่มีความรู้ค่ะ
จึงไม่ได้ลองทำสูตรไปให้อาจารย์


ที่ H4 คีย์

=IF(OR(D4="",E4="",K4=""),"",LOOKUP(ROUNDUP(D4/"0:30",0)*"0:30",$AN$23:$AN$37,$AK$23:$AK$37))

ตามที่อาจารย์บอก หนูลองทำแล้ว แสดงผลเป็น N/A คะ ไม่ทราบว่าหนูทำอะไรผิดตรงไหนหรือเปล่าคะ

[snasui]

เนื่องจาก Column AN มีค่าเป็น Text ไม่ใช่ Number จึงต้องแปลงมาให้เป็น Number เสียก่อนครับ โดย

1. หาเซลล์ว่าง ๆ > คลิกขวา > Copy
2. เลือกคลุมเซลล์ที่เป็นเวลาในคอลัมน์ AN > คลิกขวา > Paste Special > Value > Add
3. จัด Format กลับมาให้เป็นเวลา

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

ก็ต้องศึกษาครับ เพราะเมื่อถาม-ตอบไประยะหนึ่งผมจะให้ผู้ถามทำเองมากขึ้น

ตามไฟล์ที่แนบมานี้ผมทำเป็นตัวอย่างมาให้แล้ว

[kanyavee]

อาจารย์คะ คำตอบที่ต้องการสีเขียวค่ะ สีแดงที่เปรียบเทียบมาให้ดู ไม่ใช่คำตอบที่ถูกต้องค่ะ

[snasui]

ช่วยอธิบายวิธีคิดมาสักสองสามเซลล์ครับว่า มีวิธีคิดอย่างไรถึงได้คำตอบตามนั้น

[kanyavee]

อาจารย์คะ อธิบายมาตามนี้ค่ะ จะนับทุกๆ ครึ่ง ชม. ค่ะ รายละเอียดตามไฟล์แนบนะคะ

[snasui]

ช่วยใช้ไฟล์ล่าสุดที่ผมเขียนสูตรไปให้ทำตัวอย่างมาแทนไฟล์ปัจจุบันครับ เพราะว่าผมได้ปรับค่าในคอลัมน์ AN ให้เป็น Number เรียบร้อยแล้ว หรือไม่เช่นนั้นก็ปรับไฟล์ปัจจุบันให้เหมือนกับที่ผมทำตัวอย่างไปให้ ผมจะได้ไม่ต้องปรับเองซ้ำ ๆ ครับ

นอกจากนี้เงื่อนไข 11:30 มีหรือไม่ครับ

[snasui]

อันนี้ทำล่วงหน้ามาให้ก่อนเนื่องเพราะดูแล้วค่อนข้างยากต่อการปรับปรุงสูตรเอง ดูที่ชีท Aug (3) พื้นที่สีเขียวจะมีการเรียงคอลัมน์ AN จากมากไปหาน้อย เพื่อใช้กับสูตรในคอลัมน์ H

จากนั้นที่ H4 คีย์

=IF(OR(D4="",E4="",K4=""),"",INDEX($AK$23:$AK$37,MATCH(86400*ROUNDUP(D4/"0:30",0)*"0:30",86400*$AN$23:$AN$37,-1)))

Ctrl+Shift+Enter สังเกตการกดแป้นจะต้องกด 3 แป้นเสมอ เนื่องจากเป็นสูตร Array ดูตัวอย่างในไฟล์แนบครับ

[kanyavee]

อาจารย์คะ แก้ไขมาใหม่แล้วคะ ตามที่อาจารย์บอก

ส่วนเงื่อนไข 11:30-16:30 = 4 เป็นเงื่อนไขเดียวกับ 12:30-16:30 = 4 เนื่องจากเป็นเวลาพักค่ะ (พัก 11:30-12:30)

ขอบคุณค่ะ

[kanyavee]

จากหัวข้อเดิมที่คุณ Bronze ได้ให้คำแนะนำไว้ คือเรื่องการเข้างานกะกลางคืน ให้แทนด้วยเลข 1 และได้ทำสูตร ไว้ให้คือเอาเวลา Moring ของวันถัดไป
มาคำนวณ แต่พอลองปรับสูตรมาจนถึงปัจจุบัน เงือนไขการเข้างานกลางคืนหายไปคะ รบกวนอาจารย์แนะนำด้วยค่ะ


---------------------------------

[อันนี้ลองดูนะว่าพอจะตรงกับความต้องการหรือไม่
แปลงเวลา 19:20
E4=19:20 ซึ่งอยูในรูปแบบ h:mm
M4=IF(F4="",HOUR((E4)),HOUR((D5))) จะได้ค่า 19 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ)
N4=IF(F4="",MINUTE((E4)),MINUTE((D5))) จะได้ค่า 20 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ)
O4=IF(E4="","",(M4&"."&N4)*1) จะได้ค่า 19.20 (ต้องกำหนดรูปแบบเซลเป็นทั่วไป หรือตัวเลขนะ) เพื่อนำค่าไปเปรียบเทียบกับตารางอ้างอิงการกำหนด ช.ม.โอที
ไล่ดูตามไฟล์แนบนะครับ

ปัญหาคือ
1. exell คุณสกุล.xls ซ้อนสูตรได้ไม่พอกับเงื่อนไขของตารางอ้างอิง ถ้าใช้ .xlsx เพียงพอ
2.เวลาออก ของคุณมีเศษนาทีไม่ตรงกับตารางอ้างอิง + กับข้อจำกัดที่1. จึงต้องสร้างการแปรเวลาใหม่ (คอลัมม์ M:AB การเทียบเวลานี้ทำใน VBA จะสะดวกกว่าครับ กำหนดช่วงเวลาและกำหนดค่าโอที เหมือนกับตัดเกรดคะแนนสอบครับ)
3.ในการคำนวณจากตารางข้อมูลคือตารางข้อมูลเวลาแยกไม่ออกว่าเป็นกะกลางวันหรือกะกลางคืน เช่นวันที่ 21/7/2011 หากคุณไม่ทำลูกศรไว้ก็คงไม่ทราบว่าทำงานกะกลางคืน
อาจเข้าใจได้ว่าเริ่มเข้างาน7.21 เลิกงาน 19.00 ดังนั้นควรกำหนดค่าเงื่อนไขก่อนว่าเป็นกะกลางวันหรือกลางคืน ดังนั้นในเบื่องต้นหากข้อมูลมาแบบนี้ คงต้องให้คุณกำหนดเลข 1 ในวันที่เป็นกะกลางคืน
4.ในตารางอ้างกะกลางคืนมีเวลาออกสูงสุด 9.00 แต่ความจริงมีผู้ออกเวลา 11.30 (วันที่ 13/7/2011) ดังนั้นควรทำตารางอ้างอิงเวลาออกให้ครอบคลุม เท่าที่บริษัทกำหนด
และตารางอ้างอิงกะกลาง เงื่อนไขอยูที่เวลาออก ดังนั้นควรปรับเปลี่ยนให้ช่วงเวลออกเป็นเงื่อนไขที่ชัดเจน เช่น Caseที่5. ไม่เข้าใจว่าทำไมมี 5.40 ซึ่งซ้ำกับ Caseที่1
5.การคิดค่าแรงว่ากี่เท่าอันนี้ไม่แน่ใจว่าคุณมีเงื่อนไขอย่างไร แต่เท่าที่สังเกตุ คิดว่าใครทำในวันหยุดในเวลาปกติ ได้โอที 8 ชม.คิดค่าแรง 1 เท่า ใครทำโอต่อในวันหยุด(ที่ไม่ใช่เสาร์มอาทิตย์) ก็ได้ 1.5 เท่า แต่ใครที่ทำโอวันเสาร์ อาทิตย์ ได้สามเท่า

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

[snasui]

ขอให้ผ่านไปทีละเรื่องครับ ตามความเห็นนี้ได้ผลเป็นอย่างไร ได้หรือไม่ได้ช่วยรายงานด้วยครับ

อันนี้ทำล่วงหน้ามาให้ก่อนเนื่องเพราะดูแล้วค่อนข้างยากต่อการปรับปรุงสูตรเอง ดูที่ชีท Aug (3) พื้นที่สีเขียวจะมีการเรียงคอลัมน์ AN จากมากไปหาน้อย เพื่อใช้กับสูตรในคอลัมน์ H

จากนั้นที่ H4 คีย์

=IF(OR(D4="",E4="",K4=""),"",INDEX($AK$23:$AK$37,MATCH(86400*ROUNDUP(D4/"0:30",0)*"0:30",86400*$AN$23:$AN$37,-1)))

Ctrl+Shift+Enter สังเกตการกดแป้นจะต้องกด 3 แป้นเสมอ เนื่องจากเป็นสูตร Array ดูตัวอย่างในไฟล์แนบครับ

ส่วนที่บอกว่าคุณ Bronze แนะนำมานั้นคงไม่ใช่คุณ Bronze ครับ น่าจะเป็นคุณ Bafnet

[kanyavee]

อาจาย์คะ ตามที่ได้แนะนำมา ถูกต้องตามความต้องการค่ะ



อันนี้ทำล่วงหน้ามาให้ก่อนเนื่องเพราะดูแล้วค่อนข้างยากต่อการปรับปรุงสูตรเอง ดูที่ชีท Aug (3) พื้นที่สีเขียวจะมีการเรียงคอลัมน์ AN จากมากไปหาน้อย เพื่อใช้กับสูตรในคอลัมน์ H

จากนั้นที่ H4 คีย์

=IF(OR(D4="",E4="",K4=""),"",INDEX($AK$23:$AK$37,MATCH(86400*ROUNDUP(D4/"0:30",0)*"0:30",86400*$AN$23:$AN$37,-1)))

Ctrl+Shift+Enter สังเกตการกดแป้นจะต้องกด 3 แป้นเสมอ เนื่องจากเป็นสูตร Array ดูตัวอย่างในไฟล์แนบครับ


ส่วนที่ติดได้ส่งไฟล์อธิบายมาตามนี้คะ

[kanyavee]

ปล. ตอนนี้รู้สึกว่ายากมาก เริ่มท้อแล้วค่ะ ตามไม่ค่อยทันคะ

[snasui]

ไฟล์ล่าสุดคอลัมน์ G มีเงื่อนไขว่าอย่างไรหรือต้องดูเงื่อนไขที่ช่วงไหนครับ ช่วยเขียนอธิบายพร้อมทำลูกศรชี้มาเหมือนเดิมจะช่วยได้มากครับ

ปล. ตอนนี้รู้สึกว่ายากมาก เริ่มท้อแล้วค่ะ ตามไม่ค่อยทันคะ

งานแบบนี้ไม่ง่ายนักต้องค่อย ๆ ทำความเข้าใจครับ ตอนนี้ยิ่งคุณ kanyavee ยังไม่ได้ศึกษาสูตรก็เลยยิ่งยากเข้าไปใหญ่ สูตรที่ต้องทำความเข้าใจก็ได้แก่ If, Lookup, Vlookup, Index, Match ครับ

[kanyavee]

อาจารย์อธิบาย มาดังไฟล์แนบค่ะ

[snasui]

ลองตามไฟล์แนบครับ I4 คีย์

=IF(AND(D4<>"",F4=1),LOOKUP(D4,$AO$16:$AO$21,$AK$16:$AK$21),IF(AND(E4>="17:40"+0,G4<>""),LOOKUP(E4,$AO$5:$AO$13,$AK$5:$AK$13),""))

Enter > Copy ลงด้านล่าง

[kanyavee]

ลองทำตามที่บอกแล้วค่ะ แต่ผลไม่ได้ค่ะ