สูตรการรวมสถิตการหยุดงาน และการปัดเศษชั่วโมงเป็นวัน

[wimon]

เรียนอ.คนควน

ผมมี Sheet เก็บข้อมูลสถิติการลาประเภทต่างตั้งแต่ Sheet ธ.ค. 52 ถึง พ.ย. 53 ผมมีคำถามดังนี้
1.ผมต้องการรวมสถิติการลาประเภทต่างๆ ตั้งแต่ Sheet ธ.ค. 52 ถึง พ.ย. 53 มารวมใน Sheet Sumธ.ค.52-พ.ย.53 (Col AD: AO )
2. เมื่อรวมแล้ว ผมยังต้องการให้ปัดเศษชั่วโมงให้เป็นวัน หากชั่วโมงการลาประเภทต่างๆ เกิน 8.50 ชั่วโมง(บริษัทฯผม ทำงาน วันละ 8.50 คือตั้งแต่เวลา 7.50-17.30 น. หยุดเสาร์-อาทิตย์)
ตัวอย่าง ลาพักร้อน 4 วัน 18 ชั่วโมง ผมต้องการปัด 18 ชั่วโมง .ให้เป็นวัน และคงเหลือชั่วโมงส่วนที่ไม่เกินชั่วโมงทำงานไว้ หรือถ้าในช่องชั่วโมง เท่ากับ 8.50 ก็ให้ปรับไปเป็น 1 วัน ส่วนในช่องชั่วโมงมีค่าเป็น 0 เป็นต้น รบกวนอ.ช่วยใส่สูตรในSheet Sumธ.ค.52-พ.ย.53 (Col AD5: AO54 )ให้ด้วยนะครับ
3.สำหรับ Col AP5:AT54 ไม่มี ช่องชั่วโมงครับ แต่ต้องการผลรวมจากทุกSheet เช่นกัน
ขอบคุณครับ
วิมล

[snasui]

จากที่ถามมาพบว่าได้คีย์เลขเวลาคั่นด้วยจุดทศนิยม ซึ่งจะให้ความหมายไม่เหมือนกันเวลาที่คั่นด้วยโคล่อนครับ เนื่องจากเวลาที่คั่นด้วยจุดทศนิยมจะเป็นเลขฐาน 100 ส่วนเวลาที่คั่นด้วยโคล่อนจะเป็นเลขฐาน 60

จึงทำให้ไม่แน่ใจว่าตัวเลข 8.50 ที่อธิบายมานั้นเท่ากับ 8:50 (แปดชั่วโมงห้าสิบนาที) หรือเท่ากับ 8:30 (แปดชั่วโมงสามสิบนาที) ครับ อยากให้ช่วยยืนยันเพื่อจะหาวิธีคำนวณต่อไปครับ

[wimon]

เรียนอ.คนควน

จากที่ถามมาพบว่าได้คีย์เลขเวลาคั่นด้วยจุดทศนิยม ซึ่งจะให้ความหมายไม่เหมือนกันเวลาที่คั่นด้วยโคล่อนครับ เนื่องจากเวลาที่คั่นด้วยจุดทศนิยมจะเป็นเลขฐาน 100 ส่วนเวลาที่คั่นด้วยโคล่อนจะเป็นเลขฐาน 60

แสดงว่าเวลาพิมพ์เรื่องของเวลาเราต้องคีย์ให้ Format เป็น 0:00 (หรือ โคล่อน) ไม่เช่นจุดทศนิยม

จึงทำให้ไม่แน่ใจว่าตัวเลข 8.50 ที่อธิบายมานั้นเท่ากับ 8:50 (แปดชั่วโมงห้าสิบนาที) หรือเท่ากับ 8:30 (แปดชั่วโมงสามสิบนาที) ครับ อยากให้ช่วยยืนยันเพื่อจะหาวิธีคำนวณต่อไปครับ
บริษัทฯทำงาน ตั้งแต่ 7:50 ถึง 17:30 รวมเวลา วันทำงาน 8:50 (แปดชั่วโมงห้าสิบนาที)

ขอบคุณครับ

[snasui]

การพิมพ์เวลาเป็นทศนิยมก็สามารถทำได้แต่จะใช้งานลำบาก สูตรที่ใช้จะซับซ้อนกว่าปกติ หากสามารถคีย์เป็นเวลาในรูปแบบ h:mm:ss จะง่ายต่อการใช้งานครับ

สำหรับการรวมข้อมูลจากทุก Sheet ที่ต่อเนื่องกันไม่ข้ามไปข้ามมาสามารถใช้การ Sum ธรรมดาวิธีการคือ

1. เลือกชีทแรก > ในเซลล์เป้าหมายคีย์ =Sum(
2. กดแป้น Shift ค้างไว้เลือกชีทสุดท้าย > คลิกเซลล์ที่ต้องการ > คีย์วงเล็บปิด> Enter หากสูตรไม่ซับซ้อนสามารถ Enter ได้เลยโดยไม่ต้องคีย์วงเล็บปิด

สำหรับการแก้ไขปัญหาของไฟล์นี้ผมทำสรุปข้อมูลมาหนึ่งชีทก่อนคือชีท Sumธ.ค.52-พ.ย.53 จากนั้นเพิ่มชีท Sumธ.ค.52-พ.ย.53_Yes เพื่อรวมยอดเป็นวันและชม.ตามต้องการที่ชีทใหม่เพื่อไม่ให้สูตรมีความซับซ้อน ง่ายต่อการแก้ไข ลองตรวจสอบความถูกต้องจากไฟล์ที่แนบมาครับ

[wimon]

อ.คนควน


ผมดู Sheet Sumธ.ค.52-พ.ย.53 ยกตัวอย่าง AJ5 = 6 วัน และ AK5= 14:00 ชั่วโมง แต่ Sheet Sumธ.ค.52-พ.ย.53_Yes AJ5 = 6 วัน และ AK5= 5:10ชั่วโมง ซึ่งความจรืง มันต้องเป็น Sheet Sumธ.ค.52-พ.ย.53_Yes AJ5 = 7 วัน และ AK5= 5:10ชั่วโมง
(ผมยังไม่ได้ตรวจ Col อื่นๆ นะครับแต่คิดว่าเป็นเหมือนกัน)

รบกวนอ.อีกรอบนะครับ
wimon

[snasui]

สาเหตุเพราะเกิดการคำนวณวนในไฟล์ที่แนบมาครับ (ผมตรวจดูไฟล์ที่ผมส่งไปให้ก่อนหน้านี้นั้นไม่เกิดการคำนวณวน) เมื่อทดลองลบสูตรในชีท Sumธ.ค.52-พ.ย.53 และ Sumธ.ค.52-พ.ย.53_Yes ออกแล้วก็ยังพบว่าเกิดการคำนวณวน นั่นแสดงว่ามีการคำนวณวนในชีทอื่น ๆ ครับ

เช่นชีท ต.ค 2553 ดูเหมือนว่าจะ Copy สูตรมาจากชีท Sumธ.ค.52-พ.ย.53_Yes ซึ่งได้รวมเอาค่าในชีท ต.ค 2553 ไว้แล้วจึงเกิดการคำนวณวน

ดูภาพการตรวจสอบการคำนวณวนด้านล่าง และดูเพิ่มเติมเกี่ยวกับการคำนวณวนที่นี่ครับ การคำนวณวนใน Excel

[wimon]

อ.คนควน
ขอบคุณมากครับ ตอนนี้ได้ปรับปรุงใหม่แล้วครับ ไม่มีปัญหา แล้วครับตามคำแนะนำ และตอนนี้กำลังปั่นโบนัสและการปรับ Salary มี Update เรื่อยๆครับ
หากสงสัยจะถามเพิ่มเติมครับ
wimon

[wimon]

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

[snasui]

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

[wimon]

เรียนอ.คนควน

อาจารย์ครับ ผมมีคำปรึกษาดังนี้
รบกวนสอบถามเพิ่มเติม รายละเอียดคำถามตาม File แนบครับ
wimon

[snasui]

ผมปรับสูตรตามข้อ 3 มาให้ใหม่โดยใช้สูตร IF เนื่องจากตีความแล้วเข้าใจว่าให้ดูใบเตือนที่เป็นวาจาเป็นหลัก แล้วค่อยดูใบเตือนที่เป็นลายลักษณ์อักษร ไม่ได้ดูพร้อมกัน

ข้อมูลใน N38, O38, P38 มาจากไหนครับ ที่ส่งมาเป็นการคีย์ตัวเลขมาตรง ๆ ถ้าเป็นการรวมยอดมาจากด้านหน้าให้เขียนสูตรรวมไว้ให้ด้วยครับ

สำหรับกรณีของการปัด 4:50 เป็น 1 วันนั้นไม่ได้ขึ้นอยู่กับว่าทำงานวันละกี่ชม. ครับ

การปัดจาก นาที เป็น ชม. จาก ชม. เป็น วัน นั้น เพื่อให้ง่าย ให้รวม วัน ชม. นาที ไว้ในเซลล์ช่วย แล้วค่อยสร้างสูตรแสดงผลลัพธ์ที่ N38, O38, P38

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

ส่วน Vlookup ที่นำมาใช้นั้นโครงสร้างสูตรถูกต้อง แต่อ้างอิงเซลล์ที่ไม่ถูกต้อง เพราะไปอ้างอิง AE4 ซึ่งไม่น่าจะเกี่ยวข้องกัน

[wimon]

อ.คนควน

รายละเอียดตามเอกสารแนบครับ
wimon

[snasui]

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

[wimon]

เรียนอาจารย์
- จากFile V.5 สูตรเริ่ม Okay ขึ้นเรื่อยๆครับ
- ช่วงนี้ผมต้องทำแต้มหน่อยนะครับ อ. เห็นเพื่อนเข้ามา Download กันพอสมควร ตั้งแต่V.1-V.5 ผมและเพื่อนๆ เริ่มได้อะไรๆ มากขึ้น ตอนนี้ก็ V. 6 คำถามตาม รายระเอียดใน File ครับ

ขอบคุณมากครับ

wimon

[snasui]

ผมปรับสูตรมาให้ใหม่ ลองดูตามไฟล์แนบครับ

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