ต้องการปรับรายการวันขาด ลา มาสาย ลากิจ

[andyliu]

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

ขอบคุณครับ

[snasui]

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

[andyliu]

ขอประทานโทษด้วยนะครับอาจารย์ ผมได้ไฟล์มาจากกระทู้ด้านล่างนี้ครับ
http://www.snasui.com/viewtopic.php?f=3&t=5055
ไฟล์สุดท้ายครับ ส่วนรายละเอียดปัญหาที่เจอหลังจากที่ผมปรับ แทรกแถว แทรกคอลัมภ์แล้ว
อาจทำให้สูตรผิดได้ เอาเป็นว่าพรุ่งนี้จะมาลงใหม่อีกทีนะครับ ผมโพสจากมือถือเลยไม่ค่อยสะดวกครับ
ขอบพระคุณครับ

[andyliu]

สวัสดีตอนเช้าครับ
เมื่อวานผมได้โพสถามเกี่ยวกับการสรุปวัน ขาด ลา มาสาย วันนี้ขอต่อเลยนะครับ
คือ worksheet ชื่อ MonthlyReport กับ Summary คือผลลัพธ์มันสรุปได้ไม่เท่ากันครับ ส่วนสูตร/เงื่อนไขที่ใช้ ผมก็เอามาจาก Link เดิมครับ แต่ปรับเพิ่มช่องประเภทการลาโดยการแทรกเพิ่ม แล้วก็ใช้การ copy สูตรครับ
ปัญหาที่เจอในตอนนี้เกิดจากใน worksheet MonthlyReport กับ Summary มียอดแสดงวันลาไม่ตรงกันครับ

[andyliu]

ผมเคยโพสสอบถามเกี่ยวกับการสรุปวันขาด ลา มาสายไปแล้ว ซึ่งไฟล์ที่ใช้ประกอบผมต้องดึงจากโปรแกรมสำเร็จรูปมา และต้องใช้ Excel ในการคำนวณ
ผมคิดว่ามันซ้ำซ้อน เพราะต้องลงวันขาด ลา มาสาย ในโปรแกรมแล้ว ยังต้อง มาจัดการใน Excel อีก
เลยกะว่าจะลงผ่าน Excel เลย เพราะจะได้ประหยัดเวลาครับ
*** ข้อมูลที่จะลงใน worksheet ที่ชื่อ tblAbsenceLog มีจำนวนมากเสียด้วยครับ (ทั้งสำนักงาน บุคลากรรวมกัน 200 คนเศษ
จะมีปัญหาในการคำนวณหรือไม่ครับ เพราะผมกะว่าจะทำเป็นรายปี ต่อไฟล์หนึ่งไฟล์ครับ)

[snasui]

ผมเคยโพสสอบถามเกี่ยวกับการสรุปวันขาด ลา มาสายไปแล้ว ซึ่งไฟล์ที่ใช้ประกอบผมต้องดึงจากโปรแกรมสำเร็จรูปมา และต้องใช้ Excel ในการคำนวณ
ผมคิดว่ามันซ้ำซ้อน เพราะต้องลงวันขาด ลา มาสาย ในโปรแกรมแล้ว ยังต้อง มาจัดการใน Excel อีก
เลยกะว่าจะลงผ่าน Excel เลย เพราะจะได้ประหยัดเวลาครับ
*** ข้อมูลที่จะลงใน worksheet ที่ชื่อ tblAbsenceLog มีจำนวนมากเสียด้วยครับ (ทั้งสำนักงาน บุคลากรรวมกัน 200 คนเศษ
จะมีปัญหาในการคำนวณหรือไม่ครับ เพราะผมกะว่าจะทำเป็นรายปี ต่อไฟล์หนึ่งไฟล์ครับ)

การทำข้อมูลให้เป็น Database แม้จะมีจำนวนมากก็ไม่มีปัญหาครับ

ปัญหาที่เจอในตอนนี้เกิดจากใน worksheet MonthlyReport กับ Summary มียอดแสดงวันลาไม่ตรงกันครับ

เซลล์ใดต้องตรงกับเซลล์ใด ช่วยอธิบายเพิ่มเติมด้วยครับ

[andyliu]

สวัสดีครับ
ในworksheet MonthlyReport สรุปวันลากิจของนางอารี (C16) ได้ตามที่ลงข้อมูลไว้ในworksheet tblAbsenceLog ซึ่งก็คือ 5 วัน ครับ แต่พอมาดูสรุปใน worksheet Summary ซึ่งในส่วนรายงานสรุปวันลากิจของนางอารี อยู่ในช่อง L14 ครับ ผลไม่ตรงกันครับ ผมลองสังเกตดูว่าหากลงข้อมูลใน worksheet tblAbsenceLog เกิน 3 วัน มันจะไม่ตรงกันครับ จะได้แค่ 2 วัน (ทั้งๆที่ข้อมูลการลากิจ มี 5 วัน ครับ) คิดว่าปัญหาน่าจะเกิดจากตอนที่ผม copy สูตรหรือเปล่าครับ เพราะได้มาจากกระทู้ที่ลงไว้ก่อนหน้านี้ครับ กะว่าจะเอามาลองปรับใช้ดู จะได้ย่นเวลาทำงานครับ
ขอบพระคุณครับ

[snasui]

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

ที่ชีท Summary

1. เซลล์ D7

   Code: Select all

   =INT(SUMPRODUCT(tblAbsenceLog!$E$3:$E$28*(tblAbsenceLog!$B$3:$B$28=$B7)*(tblAbsenceLog!$D$3:$D$28=D$5)*(TEXT(tblAbsenceLog!$C$3:$C$28,"ดดดดbbbb")=$P$2&$R$2))/(8/24))

2. เซลล์ E7

   Code: Select all

   =(SUMPRODUCT(tblAbsenceLog!$E$3:$E$28*(tblAbsenceLog!$B$3:$B$28=$B7)*(tblAbsenceLog!$D$3:$D$28=D$5)*(TEXT(tblAbsenceLog!$C$3:$C$28,"ดดดดbbbb")=$P$2&$R$2))/(8/24)-D7)*8

[andyliu]

ทำได้แล้วครับอาจารย์
แต่ผมยังม่ข้อสงสัยสอบถามอาจารย์นิดนึงนะครับ คือ ปกติถ้าเรากรอกข้อมูลใน worksheet tblAbsenceLog ที่ column C*** สูตรดังกล่าวใน worksheet MonthlyReport และ Summary จะเปลี่ยนไป (อิงข้อมูล) ที่มีอยู่ใน worksheet tblAbsenceLog แถวสุดท้ายใช่มั้ยครับ เพราะผมสังเกตเห็นมีเส้นดำๆ (เหมือนขีดเส้นใต้) ครับ
เราเรียกมันว่าอะไรครับ หรือเราใช้คำสั่งขีดเส้นเฉยๆ ครับ (ผมคิดว่าไม่น่าจะใช่) ถ้าไม่ใช่ เราจะกำหนดเงื่อนไข หรือคำสั่งอะไรครับ ขอสอบถามเป็นความรู้เพิ่มเติมครับ ส่วนตอนนี้ที่ผมลงข้อมูลแล้วก็อาศัยการแทรกแถวเอาครับ เพราะสังเกตสูตรใน worksheet MonthlyReport และ Summary มันไม่เปลี่ยนไปตามจำนวนแถวข้อมูลที่เพิ่มขึ้นใน worksheet tblAbsenceLog ครับ ขอบคุณครับ

[snasui]

ในชีทนั้นเป็นการ Create Table เอาไว้เพื่อรับข้อมูล สูตรที่เกียวข้องกับ Table จะยืดหดตาม Table นั้น สำหรับการสร้าง Table ให้ไปที่เมนู Insert > Table ครับ หากข้อมูลนั้นมีการสร้าง Table ไว้แล้วคำสั่ง Table จะเป็นสีเทาครับ

[andyliu]

ขอบพระคุณมากครับที่ให้ความรู้และเทคนิคการใช้งาน Excel ครับ ผมขอสอบถามเพิ่มเติมนะครับ ใน worksheet Summary ช่อง AD7 AE7 นี่ต้องใช้สูตรอะไรครับ คงใช้สูตร Sum ไม่ได้ใช่มั้ยครับ เพราะลองทำดูแล้ว อีกอย่าง ช่องที่แสดงเป็นชั่วโมง (4ชั่วโมง) ก็ต้องนำมาคำนวณบวกกันให้เป็น 8 แล้วปัดเป็น 1 วัน ต้องรบกวนอาจารย์ด้วยนะครับ ว่าจะต้องปรับรวมกันอย่างไรครับ ขอบคุณครับ

[snasui]

ลองใช้สูตรตามด้านล่างที่ AD7, AE7 ตามลำดับครับ

=SUMIF($D$6:$AC$6,AD$6,D7:AD7)+INT(SUMIF($D$6:$AC$6,AE$6,D7:AC7)/8)
=INT(SUMIF($D$6:$AC$6,AE$6,D7:AC7),8)

[andyliu]

ขอบคุณครับอาจารย์ ผมทำตามที่อาจารย์แนะนำแล้วครับ ช่องผลรวมวัน (AD) ไม่มีปัญหาครับ แต่ ช่อง (AE) ในส่วนที่แสดงเป็นชั่วโมง หากมีรายการลาที่เป็นการลาแบบครึ่งวัน 2 รายการ เช่น ลาป่วย 4 ลาพักผ่อน 4 ผลรวมต้องเป็น 8 (ซึ่งก็คือ 1 วันทำงานนั่นเอง) แต่ผมยังพบปัญหาในการรวมผล เพราะถ้ามันรวมผลแล้วได้ 8 (ชั่วโมง) 1 วัน มันจะไม่ปัดมารวมในช่อง AD ครับ คิดว่าหากทำจริงๆ คงต้องเพิ่มคอลัมภ์ก่อน แล้วนำเอาผลบวกในช่อง AE ที่รวมได้อีก 1 วัน ปัดขึ้นมารวมในช่อง AD (เหมือนการทดตัวเลขในหลักแรก แล้วไปรวมกับอีกหลักหนึ่ง) สูตรคำนวณซับซ้อนยุ่งยากเข้าไปอีก
ผมคิดว่า หากทำจริง คงต้องคำนวณบวกมือเอาเองจะดีกว่า เพราะรบกวนอาจารย์มามาก เกรงใจนะครับ แค่นี้ผมก็ได้ไว้ใช้ทำรายงานสรุปผลเสนอหัวหน้าแล้ว ต้องขอบพระคุณอาจารย์อีกครั้งนะครับที่ได้มอบความรู้ให้ผมครับ

[snasui]

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

+INT(SUMIF($D$6:$AC$6,AE$6,D7:AC7)/8)

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

[Totem]

ขอบคุณครับอาจารย์ ผมทำตามที่อาจารย์แนะนำแล้วครับ ช่องผลรวมวัน (AD) ไม่มีปัญหาครับ แต่ ช่อง (AE) ในส่วนที่แสดงเป็นชั่วโมง หากมีรายการลาที่เป็นการลาแบบครึ่งวัน 2 รายการ เช่น ลาป่วย 4 ลาพักผ่อน 4 ผลรวมต้องเป็น 8 (ซึ่งก็คือ 1 วันทำงานนั่นเอง) แต่ผมยังพบปัญหาในการรวมผล เพราะถ้ามันรวมผลแล้วได้ 8 (ชั่วโมง) 1 วัน มันจะไม่ปัดมารวมในช่อง AD ครับ คิดว่าหากทำจริงๆ คงต้องเพิ่มคอลัมภ์ก่อน แล้วนำเอาผลบวกในช่อง AE ที่รวมได้อีก 1 วัน ปัดขึ้นมารวมในช่อง AD (เหมือนการทดตัวเลขในหลักแรก แล้วไปรวมกับอีกหลักหนึ่ง) สูตรคำนวณซับซ้อนยุ่งยากเข้าไปอีก
ผมคิดว่า หากทำจริง คงต้องคำนวณบวกมือเอาเองจะดีกว่า เพราะรบกวนอาจารย์มามาก เกรงใจนะครับ แค่นี้ผมก็ได้ไว้ใช้ทำรายงานสรุปผลเสนอหัวหน้าแล้ว ต้องขอบพระคุณอาจารย์อีกครั้งนะครับที่ได้มอบความรู้ให้ผมครับ

ตามที่เข้าใจ คือ ช่อง D10 = 1 , H10 = 5 , P10 = 2 รวมได้ 1+5+2 = 8 วัน และ ช่อง E10 = 4 , Q10 = 4 รวมได้ 8 ชั่วโมง = 1 วัน นำไปรวมกับในช่อง AD10 =8+1= 9 วัน แต่ที่แนบไฟล์มา ช่อง AD10 = 8 วัน ซึ่งไม่ตรงกับที่คุณ andyliu ต้องการ คำตอบที่ต้องการ AD10 = 9 วัน ถูกต้องไหมครับ

[andyliu]

สวัสดีครับ
ในช่องผลรวม (AD กับ AE) ครับ ที่ยังผิดอยู่ ครับ ตัวอย่างช่อง AD8 กับ AE8 ครับ ผลลัพธ์ต้องเท่ากับ 5 วันครับ ซึ่งในช่อง AD8 แสดงได้ถูกแล้ว แต่ช่อง AE8 ยังเป็น 1 อยู่ครับ
ผมเข้าใจว่า น่าจะมาจากการทดตัวแลขจากสูตรในช่อง AE9 ครับ ส่วนอีกอัน ช่อง AD9 และ AE9 ผลลัพธ์รวมกันต้องได้เป็น 3 วัน 4 ชั่วโมง แต่ก็ยังแสดงผลผิดอยู่ครับ โดยแสดลเป็น 3 ในช่อง AD9 ส่วน AE9 แสดงผลเป็น 1 ครับ

ต้องขอขอบคุณ คุณ Totem ด้วยนะครับ

[snasui]

เซลล์ AD7 เปลี่ยนสูตรเป็น

=SUMIF($D$6:$AC$6,AD$6,D7:AC7)+INT(SUMIF($D$6:$AC$6,AE$6,D7:AC7)/8)

เซลล์ AE7 เปลี่ยนสูตรเป็น

=MOD(ROUND(SUMIF($D$6:$AC$6,AE$6,D7:AC7),0),8)

จากนั้น Copy ทั้งสองเซลล์ลงด้านล่าง

[andyliu]

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

[andyliu]

สอบถามใหม่อีกครั้งครับ หลังจากที่ทดลองลงข้อมูลดูแล้ว ยังพบมีปัญหาครับ
ตามไฟล์แนบนะครับอาจารย์
พบรายการที่ยังประมวลผลผิดที่ Worksheet Summary ดังนี้ ครับ
1. ที่ I7 แสดงตัวเลขออกมาเป็น 0 ครับ แต่ผลรวมช่อง AD7+AE7 ยังเท่ากันครับ
2. ที่ I9 ปรากฎว่าสัญลักษณ์ - หายไป (ทั้งๆที่มีสูตรอยู่นะครับ) ทำให้ผลรวมช่อง AD9+AE9 ไม่เท่ากันครับ)
**** ตามที่ผมคิดไว้ จะเกี่ยวกับการที่มี เลข 8 (เต็มวัน) และที่มีเลข 4 (ครึ่งวัน) หรือไม่ครับ ****

[snasui]

สามารถใช้ Round เข้าไปช่วยปัดเศษได้ครับ ยกตัวอย่างที่เซลล์ AD7 ปรับสูตรเป็น

=SUMIF($D$6:$AC$6,AD$6,D7:AC7)+INT(ROUND(SUMIF($D$6:$AC$6,AE$6,D7:AC7),0)/8)

เซลล์ I7 คีย์

=ROUND((SUMPRODUCT(tblAbsenceLog!$E$3:$E$35*(tblAbsenceLog!$B$3:$B$35=$B7)*(tblAbsenceLog!$D$3:$D$35=H$5)*(TEXT(tblAbsenceLog!$C$3:$C$35,"ดดดดbbbb")=$P$2&$R$2))/(8/24)-H7)*8,0)

Copy ลงด้านล่างและ Copy ไปยังเซลล์อื่น ๆ ที่เกี่ยวข้อง