Snasui Excel Forum

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

ขอบคุณครับ

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

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

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

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

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

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

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

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

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

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

ที่ชีท 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

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

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

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

ลองใช้สูตรตามด้านล่างที่ 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)

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

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

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

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

andyliu wrote:ขอบคุณครับอาจารย์ ผมทำตามที่อาจารย์แนะนำแล้วครับ ช่องผลรวมวัน (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 วัน ถูกต้องไหมครับ

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

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

เซลล์ 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 ทั้งสองเซลล์ลงด้านล่าง

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

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

สามารถใช้ 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 ไปยังเซลล์อื่น ๆ ที่เกี่ยวข้อง