Snasui Excel Forum

รายละเอียด

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


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


sheet ข้อมูลของเจ้าหน้าที่แต่ละคนกรอกข้อมูลผมไม่รู้จะใส่สูตรอย่างไรให้ auto จึงต้องให้พิมพ์เดือน กับครั้งที่ -ส่วนลำดับจะให้นับ 1 ใหม่ ถ้าครั้งที่เป็นครั้งใหม่ ใส่สูตรยังไงครับ-ยอดรวมแต่ละครั้ง ผมไม่ทราบจะใส่ตรงไหน จึง sum ไว้ด้านข้างครับ-ค่าธรรมเนียมคำนวณที่หน้าสรุปไม่สำเร็จ ผมเลยคำนวณทุกหน้าที่หน้ากรอก แล้วใช้ sumif ให้ไปแสดง (แบบที่สรุปยอดเป็นจังหวัด) แต่อัพขึ้นไม่ได้ ผมเลยต้องลบออกครับ

อาจจะอธิบายไม่ชัดเจน แต่ส่วนไหนพอจะทำได้ ช่วยหน่อยครับ

ข้อมูลผมมีมากกว่านี้ กรอกหลายคน คนละประมาณ 30-40 จังหวัดแต่ไฟล์มันใหญ่เกินอัพโหลดเลยต้องตัดครับ

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

การสรุปข้อมูลสามารถนำ PivotTable มาช่วยได้ครับ สำหรับการทำ PivotTable ใน Excel 2003 (ผมสังเกตจากไฟล์ที่แนบมานามสกุลเป็น .xls) เข้าเมนู Data > PivotTable ลองทำเองได้เลยครับ ติดขัดตรงไหนสามารถถามเข้ามาได้เรื่อย ๆ

สูตรสำหรับการคิดค่าธรรมเนียมหากยังไม่มีจำนวนเงินแล้วต้องการให้แสดงเป็น 0 สามารถเปลี่ยนสูตรเดิมเป็นตามด้านล่างครับ

ที่ชีทสรุป เซลล์ G3 คีย์

=IF(C3=0,0,ROUNDUP(IF(D3=1,20,IF(C3>1000000,1020,IF(C3>=10000,((C3/1000)+20),IF(C3<=9999.99,30)))),0))

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

ขอบคุณครับ

เดี๋ยวลองแล้วเรียบเรียงคำถามได้แล้วจะมาถามใหม่ครับ ตอนนี้กำลังแกะไฟล์ อีกไฟล์ที่อาจารย์เคยแนะนำผู้อื่นไว้

น่าจะดีขึ้นครับ

^___^

โดยรวมๆ ผมยังไม่เข้าใจคำถาม แต่ตรงส่วนที่บอกว่า
"...มีปัญหาตรงที่ จังหวัดที่ยังไม่มีการโอนเงิน ช่องค่าธรรมเนียมเห็นค่ามากกว่า 0 ทำให้มีค่าธรรมเนียมขึ้นมา..."
ให้แก้ไขสูตรตรงที่
=IF(C3="","",ROUNDUP.........
เป็น
=IF(C3=0,0,ROUNDUP.........

ส่วนเรื่องอื่น เดี๋ยวจะลองทำความเข้าใจกับคำถามอีกครั้ง

ขอบคุณครับ แม้จะอธิบายให้อาจารย์ไม่เข้าใจ แต่ก็ได้อะไรเพิ่มเยอะครับ ทั้งที่แนะนำมาและจากนั่งศึกษาในบอร์ดนี้

ใน sheet สรุปนั้น จังหวัดที่ยังไม่มีข้อมูลการโอน ในช่อง C เป็นค่าสูตร ทำให้ช่อง E เห็นเป็นมีค่า ทำให้เปิดค่าธรรมเนียม
ผมได้ลองศึกษาตามสูตรของอาจารย์แล้ว
=IF(C3=0,0,ROUNDUP(IF(D3=1,20,IF(C3>1000000,1020,IF(C3>=10000,((C3/1000)+20),IF(C3<=9999.99,30)))),0))
Enter > Copy ลงด้านล่าง

ผลคือ

ช่อง C ไม่มีจำนวนเงิน ช่อง E ก็ไม่คิดค่าธรรมเนียม ได้ผลตามที่ต้องการครับ

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

หน้าสรุปจึงต้องเป็นการดึงข้อมูลที่ผ่านการคำนวณในหน้าของเจ้าหน้าที่ ซึ่งผลก็ได้ตามต้องการครับ

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

ผมขอตอบต่อจากเดิมที่ได้ตอบมาแล้วนะครับ

avanzaboy wrote:เพื่อนำไปคำนวณค่าธรรมเนียมธนาคารที่เรียกเก็บ
จึงให้เจ้าหน้าที่กรอกข้อมูลการโอนของแต่ละคนเข้ามาใน sheet ของแต่ละคน

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

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

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

งานในทุก ๆ เดือนคือนำข้อมูลของแต่ละคนมาวางไนไฟล์ Template เพื่อหาค่าธรรมเนียม จากนั้นนำข้อมูลทั้งหมดมาวางในไฟล์ Database จะไม่มีการเพิ่มไฟล์ใหม่ในแต่ละเดือนแต่อย่างใด

ผมได้ลองทำDatabase ตามที่อาจารย์แนะนำดังนี้
1.Sheet"MSheet" สำหรับฐานข้อมูลเพืิ่อระบุ ต่างจังหวัด/ปริมณฑล
2.Sheet"Transdata" เป็นฐานข้อมูลสำหรับเก็บข้อมูลทั้งหมด ของทุกคน ของทุกปี/เดือน
3.Sheet"Pivot1" เป็นข้อมูลสรุปรายงานตามที่ต้องการ

วิีธีการก็คือ
1.ทุกคนจะได้รับไฟล์นี้ เพื่อบันทึกข้อมูลของแต่ละคน และสามารถสอบทานข้อมูลของตนเองด้วย
2.ผู้รวบรวมสรุปข้อมูล มีหน้าที่รวบรวมไฟล์ของทุกคน โดยนำเอา Sheet"Transdata"ของทุกคน Copy ต่อท้ายข้อมูล
3.Refresh Data ใน Sheet"Pivot1" จะได้รายงานสรุป และสามารถปรับเปลี่ยนได้ตามที่ต้องการ