สอบถามการทำไฟล์สรุปวันลา หลาย ๆชีทค่ะ ต้องทำยังไงค่ะ

[aoae_kan]

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

[snasui]

ช่วยอธิบายมาว่าปัญหาคืออะไร ต้องการคำตอบเป็นเท่าใด ในชีทไหน ด้วยเงื่อนไขใดบ้างครับ

[aoae_kan]

ช่วยอธิบายมาว่าปัญหาคืออะไร ต้องการคำตอบเป็นเท่าใด ในชีทไหน ด้วยเงื่อนไขใดบ้างครับ

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

สรุปวันลา56 - Copy.zip

[nattasiray]

ปัญหาที่คุณสรุปข้อมูลไม่ได้เป็นเพราะ คุณใช้ตารางจัดเก็บข้อมูลเดิมเป็นตารางออกรายงาน (Crosstab,ตารางไขว้) ซึ่งผิดจุดประสงค์ของการใช้งานตารางประเภทดังกล่าว ผลที่ตามสร้างปัญหามากมาย ซึ่งได้แก่
1 ต้องบันทึกข้อมูลไปทางขวา ทำให้ต้องเลื่อนข้อมูลไปทางขวา ผิดนิสัยการอ่านของมนุษย์ที่ต้องอ่านจากบนลงล่าง
2 สูตรที่ใช้ค้นหา สรุปผล และอื่น ๆ จะต้องใช้สูตรซับซ้อนและยุ่งยาก
3 ไม่สามารถใช้คำสั่ง AutoFilter เพื่อคัดกรอกข้อมูลอย่างมีเงื่อนไขได้
4 ใช้เวลายาวนานสรุปผล
5 แฟ้มมีขนาดใหญ่มาก
6 ไม่สามารถใช้สูตร SUMIF COUNTIF SUMIFS COUNTIFS AVERAGEIF AVERAGEIFS SUMPRODUCT สรุปผล

การบันทึกข้อมูลของคุณ ไม่ถูกต้อง คือ แยกชีตชื่อพนักงาน 1 คน/ ชีต แถมมีการซอยคอลัมน์ประเภทการลามากมาย

การแก้ไข คือ
1 สร้างตารางเก็บรายชื่อพนักงานอยู่ชีตหนึ่ง
2 สร้างเวิร์คชีตเก็บประวัติการป่วยสายขาดลาไว้โดยเฉพาะ ซึ่งมีฟิลด์รหัสพนักงาน วันเดือนปีที่ลา ประเภทการลา จำนวนชั่วโมงลา
3 สร้างตารางออกรายงานอยู่อีกเวิร์คชีตหนึ่ง
4 บันทึกข้อมูลให้ต่อเนื่องกันฟิลด์พนักงานต้องบันทึกซ้ำกันห้ามเว้นว่างไว้ ห้ามใช้เครื่องหมาย "

ฟิลด์ใดที่มีลักษณะกรอกข้อมูลซ้ำ ๆ กัน ให้สร้าง Data validation List กำกับการบันทึกข้อมูลไว้

งานของคุณเป็นด้านฐานข้อมูล ดังนั้นผมแนะนำให้คุณเปลี่ยนการบันทึกข้อมูลให้เป็นฐานข้อมูลเถอะครับ เพื่อการทำงานเป็นแบบทำงานอัจฉริยะ (Worksmart) เสร็จภายในชั่วโมงเดียว ดีกว่าทำงานแบบเหนื่อยมาก (Workhard) แบบที่คุณประสบใช่ไหมครับ

[nattasiray]

หลังแปลงตารางบันทึกข้อมูลเป็นฐานข้อมูลแล้วให้สร้างตารางไดนามิกส์ เพื่อไม่ต้องเสียเวลาปรับช่วงการอ้างอิงเซลล์ในสูตนรคำนวณ โดยกระทำตามขั้นตอนดังนี้
1 คลิกลงในตารางใหม่
2 คลิกแท็บ Home
3 คลิกปุ่ม Format As Table
4 คลิกเลือก Light แบบสีเทาสีขาวสลับบรรทัด
5 ใส่เครื่องหมายถูกหน้า My table has Header
6 คลิก OK

งานของคุณเป็นงานฐานข้อมูล คุณลองหาหนังสือ จัดการฐานข้อมูลด้วย Microsoft Excel 2010 มาอ่านด้วยครับ มีหัวข้อ การสร้างตารางไดนามิกส์การออกแบบตารางบันทึกข้อมูลที่ถูกต้อง การใช้สูตร SUMPRODUCT อธิบายอยู่ด้วย

[nattasiray]

มาดูตารางสุดท้ายที่คุณถามไว้ ได้คำตอบแล้ว ด้วยการใช้สูตร SUMPRODUCT TEXT MOD HOUR เข้าร่วมกัน

จะเห็นว่าถ้าเปลี่ยนการบันทึกข้อมูลอยู่ในรูปฐานข้อมูล ส่งผลให้แฟ้มมีขนาดเล็กลง และสามารถใช้ฟังก์ชันการคำนวณที่เกี่ยวข้องกับฐานข้อมูลได้

[snasui]

การจะนำข้อมูลไปทำรายงานได้สะดวกและสามารถสร้างรายงานได้หลายมิติให้ศึกษาจากที่คุณณัฐศิระได้ตอบไปแล้วครับ

ส่วนสูตรที่ถามมา ในเซลล์ D6 ปรับเป็น

=INDEX(INDIRECT("'"&$A6&"'!FH05:FP05"),COLUMNS($D6:D6))

Enter > Copy ไปทางขวาและลงด้านล่าง

[aoae_kan]

ปัญหาที่คุณสรุปข้อมูลไม่ได้เป็นเพราะ คุณใช้ตารางจัดเก็บข้อมูลเดิมเป็นตารางออกรายงาน (Crosstab,ตารางไขว้) ซึ่งผิดจุดประสงค์ของการใช้งานตารางประเภทดังกล่าว ผลที่ตามสร้างปัญหามากมาย ซึ่งได้แก่
1 ต้องบันทึกข้อมูลไปทางขวา ทำให้ต้องเลื่อนข้อมูลไปทางขวา ผิดนิสัยการอ่านของมนุษย์ที่ต้องอ่านจากบนลงล่าง
2 สูตรที่ใช้ค้นหา สรุปผล และอื่น ๆ จะต้องใช้สูตรซับซ้อนและยุ่งยาก
3 ไม่สามารถใช้คำสั่ง AutoFilter เพื่อคัดกรอกข้อมูลอย่างมีเงื่อนไขได้
4 ใช้เวลายาวนานสรุปผล
5 แฟ้มมีขนาดใหญ่มาก
6 ไม่สามารถใช้สูตร SUMIF COUNTIF SUMIFS COUNTIFS AVERAGEIF AVERAGEIFS SUMPRODUCT สรุปผล

การบันทึกข้อมูลของคุณ ไม่ถูกต้อง คือ แยกชีตชื่อพนักงาน 1 คน/ ชีต แถมมีการซอยคอลัมน์ประเภทการลามากมาย

การแก้ไข คือ
1 สร้างตารางเก็บรายชื่อพนักงานอยู่ชีตหนึ่ง
2 สร้างเวิร์คชีตเก็บประวัติการป่วยสายขาดลาไว้โดยเฉพาะ ซึ่งมีฟิลด์รหัสพนักงาน วันเดือนปีที่ลา ประเภทการลา จำนวนชั่วโมงลา
3 สร้างตารางออกรายงานอยู่อีกเวิร์คชีตหนึ่ง
4 บันทึกข้อมูลให้ต่อเนื่องกันฟิลด์พนักงานต้องบันทึกซ้ำกันห้ามเว้นว่างไว้ ห้ามใช้เครื่องหมาย "

ฟิลด์ใดที่มีลักษณะกรอกข้อมูลซ้ำ ๆ กัน ให้สร้าง Data validation List กำกับการบันทึกข้อมูลไว้

งานของคุณเป็นด้านฐานข้อมูล ดังนั้นผมแนะนำให้คุณเปลี่ยนการบันทึกข้อมูลให้เป็นฐานข้อมูลเถอะครับ เพื่อการทำงานเป็นแบบทำงานอัจฉริยะ (Worksmart) เสร็จภายในชั่วโมงเดียว ดีกว่าทำงานแบบเหนื่อยมาก (Workhard) แบบที่คุณประสบใช่ไหมครับ

[aoae_kan]

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

ปัญหาที่คุณสรุปข้อมูลไม่ได้เป็นเพราะ คุณใช้ตารางจัดเก็บข้อมูลเดิมเป็นตารางออกรายงาน (Crosstab,ตารางไขว้) ซึ่งผิดจุดประสงค์ของการใช้งานตารางประเภทดังกล่าว ผลที่ตามสร้างปัญหามากมาย ซึ่งได้แก่
1 ต้องบันทึกข้อมูลไปทางขวา ทำให้ต้องเลื่อนข้อมูลไปทางขวา ผิดนิสัยการอ่านของมนุษย์ที่ต้องอ่านจากบนลงล่าง
2 สูตรที่ใช้ค้นหา สรุปผล และอื่น ๆ จะต้องใช้สูตรซับซ้อนและยุ่งยาก
3 ไม่สามารถใช้คำสั่ง AutoFilter เพื่อคัดกรอกข้อมูลอย่างมีเงื่อนไขได้
4 ใช้เวลายาวนานสรุปผล
5 แฟ้มมีขนาดใหญ่มาก
6 ไม่สามารถใช้สูตร SUMIF COUNTIF SUMIFS COUNTIFS AVERAGEIF AVERAGEIFS SUMPRODUCT สรุปผล

การบันทึกข้อมูลของคุณ ไม่ถูกต้อง คือ แยกชีตชื่อพนักงาน 1 คน/ ชีต แถมมีการซอยคอลัมน์ประเภทการลามากมาย

การแก้ไข คือ
1 สร้างตารางเก็บรายชื่อพนักงานอยู่ชีตหนึ่ง
2 สร้างเวิร์คชีตเก็บประวัติการป่วยสายขาดลาไว้โดยเฉพาะ ซึ่งมีฟิลด์รหัสพนักงาน วันเดือนปีที่ลา ประเภทการลา จำนวนชั่วโมงลา
3 สร้างตารางออกรายงานอยู่อีกเวิร์คชีตหนึ่ง
4 บันทึกข้อมูลให้ต่อเนื่องกันฟิลด์พนักงานต้องบันทึกซ้ำกันห้ามเว้นว่างไว้ ห้ามใช้เครื่องหมาย "

ฟิลด์ใดที่มีลักษณะกรอกข้อมูลซ้ำ ๆ กัน ให้สร้าง Data validation List กำกับการบันทึกข้อมูลไว้

งานของคุณเป็นด้านฐานข้อมูล ดังนั้นผมแนะนำให้คุณเปลี่ยนการบันทึกข้อมูลให้เป็นฐานข้อมูลเถอะครับ เพื่อการทำงานเป็นแบบทำงานอัจฉริยะ (Worksmart) เสร็จภายในชั่วโมงเดียว ดีกว่าทำงานแบบเหนื่อยมาก (Workhard) แบบที่คุณประสบใช่ไหมครับ

[snasui]

คำตอบคือใช่ครับ

[nattasiray]

ผมงงกับข้อความ

ตารางบันทึกประวัติป่วยสายขาดลาตัวนี้เราต้องทำให้กับพนักงานทุก ๆๆ ใช่หรือเปล่าค่ะ หรือว่าต้องทำไงดีค่ะ

คำว่า ทุก ๆ นี้ หมายความว่าอย่างไรครับ

ตารางเก็บประวัติการป่วยสายขาดลาที่เวิร์คชีต tblAbsenceLog นี้ หากมีข้อมูลการป่วยสายขาดลาของพนักงานคนอื่นก็ให้บันทึกต่อท้ายตารางเดิม ห้ามแยกบันทึก 1 ชีต/พนักงาน โดยเด็ดขาด (ดูรูปประกอบ)

ทดสอบโดยเพิ่มข้อมูลใหม่เฉพาะบรรทัดที่ตีกรอบสี่เหลี่ยมสีแดงดังรูป (ไม่ต้องตีกรอบสี่เหลี่ยมสี่แดงนะครับ) จากนั้น ให้ไปดูผลลัพธ์ที่เวิร์กคชีต Summary ว่าจำนวนชั่วโมงที่ลาป่วยของรหัสพนักงาน E002 มาด้วยหรือไม่ จากนั้นไปที่เวิร์คชีต SUMMARY เปลี่ยนข้อมูลที่เซลล์ J2 จาก E001 ไปเป็น E002 แล้วดูผลสรุปครับ

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

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

[aoae_kan]

[quote="nattasiray"]ผมงงกับข้อความ


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

กาญจนา

[snasui]

กับ Version ใดก็ใช้ได้ครับ

[aoae_kan]

ขอบคุณค่ะ

กับ Version ใดก็ใช้ได้ครับ

[namfon220]

สวัสดีค่ะ ดิฉันลองนำไฟล์ตัวอย่างไปลองทดสอบใช้ค่ะ มีปัญหาติดตรงที่
1. หากรายการลาใดเท่ากับ 3 วันในหน้า MonthlyReport จะไม่แสดงจำนวนเลยค่ะ
2. หากรายการลาเกิน 3 วัน นับแต่วันที่ 4 ในหน้า MonthlyReport จะแสดงเป็นเลข 1 (สำหรับ 4วันค่ะ)

ไม่ทราบว่าดิฉันต้องแก้ไขสูตรแบบไหนให้มันแสดงผลได้ถูกต้องค่ะ

รบกวนด้วยนะค่ะ ขอบคุณล่วงหน้าค่ะ

[snasui]

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

ที่ชีท MonthlyReport เซลล์ ปรับสูตรเป็น

=SUMPRODUCT(tblAbsenceLog!$E$3:$E$13*(tblAbsenceLog!$B$3:$B$13=$J$2)*(tblAbsenceLog!$D$3:$D$13=C$4)*(TEXT(tblAbsenceLog!$C$3:$C$13,"ดดดดbbbb")=$B5&$D$2))

Enter > Copy ไปทางขวาและลงด้านล่าง แล้วจัด Format Cell ใหม่ให้เป็น [h]:mm โดยคลุมพื้นที่ที่ต้องการแล้วคลิกขวา > Format Cells > ที่แถบ Number เลือก Custom > ที่ช่อง Type: คีย์ [h]:mm

ผลลัพธ์จะได้เป็นจำนวนชั่วโมง

[namfon220]

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

ที่ชีท MonthlyReport เซลล์ ปรับสูตรเป็น

=SUMPRODUCT(tblAbsenceLog!$E$3:$E$13*(tblAbsenceLog!$B$3:$B$13=$J$2)*(tblAbsenceLog!$D$3:$D$13=C$4)*(TEXT(tblAbsenceLog!$C$3:$C$13,"ดดดดbbbb")=$B5&$D$2))

Enter > Copy ไปทางขวาและลงด้านล่าง แล้วจัด Format Cell ใหม่ให้เป็น [h]:mm โดยคลุมพื้นที่ที่ต้องการแล้วคลิกขวา > Format Cells > ที่แถบ Number เลือก Custom > ที่ช่อง Type: คีย์ [h]:mm

ผลลัพธ์จะได้เป็นจำนวนชั่วโมง

ขอบคุณสำหรับคำตอบนะค่ะ

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

[snasui]

สูตรเหมือนเดิม แต่เพิ่มตัวหารเข้าไป โดยจะได้เป็นด้านล่างครับ

=OldFormula/(8/24) จากนั้นกำหนด Format ให้เป็น d h:mm

[namfon220]

สูตรเหมือนเดิม แต่เพิ่มตัวหารเข้าไป โดยจะได้เป็นด้านล่างครับ

=OldFormula/(8/24) จากนั้นกำหนด Format ให้เป็น d h:mm

ดิฉันต้องขออภัยด้วยนะค่ะ ดิฉันคงตั้งคำถามจนคุณสับสน

คือความจริงแล้ว ดิฉันยังชอบที่ผลลัพธ์ของหน้า Monthlyreport มีรูปแบบเป็น วัน ?/2 , ?/8 เช่น 2 วัน 7 ชั่วโมงจะแสดงรูปแบบเป็น 2 7/8 หากเป็น 3 วันครึ่ง รุปแบบจะแสดงเป็น 3 1/2 มากกว่ายิบย่อยเป็นจำนวนนาทีค่ะ เพียงแต่สูตรเดิมถ้าจำนวนวันหยุดเกิน 3 วันขึ้นไปจะแสดงผลลัพธ์ที่ผิดพลาดเท่่านั้นเอง รวมถึงหน้า Summary ก็ผิดพลาดไปด้วยค่ะ ส่วนสูตรที่คุณแก้มาให้นั่น หากต้องการในรูปชั่วโมงจะใช้งานได้ดีเลยค่ะ

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

[snasui]

ดิฉันต้องขออภัยด้วยนะค่ะ ดิฉันคงตั้งคำถามจนคุณสับสน

ต้องขออภัยที่ผมยังไม่สับสน สิ่งที่ตอบไปนั้นอยู่บนพื้นฐานว่าคุณทราบเรื่องการจัด Format Cell อยู่แล้วครับ

สำหรับการจัด Format ตามนั้นคุณณัฐศิระได้กำหนดไว้ให้แล้วคุณแค่เลือกใช้เท่านั้น วิธีการคือคลุมข้อมูลทั้งหมด > คลิกขวา > Format Cells > ที่แถบ Number เลือก Custom > ที่ด้านขวาเลือกรูปแบบที่เป็น ? ?/? หรือรูปแบบที่ต้องการ