Font s :
Background :

การออกแบบโครงสร้างตารางคำนวณ ภาค 2

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

ในบทความภาคที่ 2 จะนำโครงสร้างตารางคำนวณที่มีความซับซ้อนมากขึ้นมาให้ศึกษากันโดยขอเรียกตารางคำนวณแบบนี้ว่า ตารางคำนวณแบบ Module ซึ่งสามารถนำไปใช้งานกันได้ 2 แบบ คือ แบบ Module เดี่ยว (Single Module) และแบบ Module รวม (Compound Module)

ความหมายของ Module

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

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

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

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

จุดประสงค์ของการสร้างตารางคำนวณแบบ Module

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

ตารางคำนวณแบบ Single Module มีลักษณะตรงกับความหมายของคำว่า Single นั่นคือเป็นตารางคำนวณแบบตารางเดียวหรือตารางเดี่ยว ที่สามารถคำนวณให้ผลลัพธ์ที่ต้องการได้ครบถ้วนภายในตารางคำนวณตารางเดียว

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

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

  1. ส่วนของตาราง Input ใช้สำหรับเก็บตัวแปรที่ต้องการตามรายเดือนหรือแต่ละชุด ซึ่งตาราง Input นี้ใช้จัดเก็บข้อมูลรายเดือนหรือแต่ละชุดไว้เป็นแนวนอนหรือแนวตั้งติดกันก็ได้
  2. ส่วนของตารางสูตรตัวกลางใช้ดึงข้อมูล เพื่อเลือก Input ตามเลขเดือนหรือเลขที่ของชุด ซึ่งมักใช้สูตร Index เพื่อเลือกดึงข้อมูลจากตาราง Input ในชีทเดียวกัน หรือใช้สูตร Indirect เพื่อเลือกดึง Input จากตารางที่แยกชีทกัน
  3. ส่วนของตารางคำนวณ เพื่อคำนวณตามค่าที่รับมาจากตารางสูตรตัวกลาง
ตัวอย่างโครงสร้างตารางคำนวณแบบ Single Module ในชีทเดียว

SingleModuleALLinOne.png

(Download ตัวอย่างได้จาก www.ExcelExpertTraining.com/extreme/files/database/singlemodule.xls)

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

ส่วนแรกใช้สำหรับบันทึกตัวแปรแต่ละ case ซึ่งในภาพใช้ตารางด้านขวาสุดตั้งแต่เซลล์ I3:K6 (ตั้งชื่อ Range Name ตารางส่วนนี้ว่า MyData) แบ่งแยกให้แต่ละ column บันทึกตัวแปรของแต่ละ case เช่น case1 เป็นพื้นที่ตั้งแต่เซลล์ I3:I6 โดยกำหนดให้เรียงลำดับตัวแปรของยอดขายและต้นทุนต่อกันไปใน column เดียวกัน แล้วใช้ column ถัดไปบันทึกตัวแปรของ case ต่อไปในทำนองเดียวกัน

สาเหตุที่ต้องจัดให้บันทึกตัวแปรของแต่ละ case ไว้ในแนวตั้งเดียวกันนั้น เพื่อทำให้สามารถใช้สูตร Index ดึงข้อมูลตัวแปรที่เก็บไว้ในแนวตั้งเดียวกันไปพร้อมกัน โดยสร้างสูตร Index ต่อไปนี้ไว้ในเซลล์ F3:F6 แบบ Array (โดยเลือกเซลล์ F3:F6 แล้วสร้างสูตร จากนั้นกดปุ่ม Ctrl+Shift+Enter พร้อมกันเพื่อบันทึกสูตรลงไปทีเดียวทุกเซลล์ จะพบเครื่องหมายวงเล็บปีกกาปิดหัวท้ายสูตรให้เอง)

=INDEX( MyData, 0, CaseNum )

  • MyData เป็นตารางบันทึกตัวแปรตั้งแต่เซลล์ I3:K6
  • เลข 0 เพื่อกำหนดให้สูตร Index ดึงข้อมูลทุก Row
  • CaseNum คือเซลล์ F2 ใช้สำหรับใส่ตัวเลข 1, 2, หรือ 3 ลงไป เพื่อทำให้สูตร Index ดึงข้อมูล Input จาก Case1, Case2, หรือ Case3 ตามลำดับ

เมื่อดึงตัวแปรตามตัวเลขของ CaseNum ที่ต้องการมาแสดงในเซลล์ F3:F6 ได้แล้ว จากนั้นจึงสร้างสูตร link ตามปกติไปใช้คำนวณงบกำไรขาดทุนในส่วนของตารางด้านซ้ายสุดจากเซลล์ D3:D9

ในกรณีที่ต้องการเปรียบเทียบผลของการคำนวณทุก case พร้อมกัน ให้สร้างตาราง Data Table แบบ 1 ตัวแปร ที่ใช้ Row Input Cell เป็นเซลล์ CaseNum ตามรูปต่อไปนี้

SingleModuleALLinOneDataTable.pngเซลล์ H11:K18 เป็นพื้นที่ตารางที่ใช้กับคำสั่ง Data > Table แล้วกำหนดให้เซลล์ F2 หรือ CaseNum เป็น Row Input Cell หรือเซลล์มี่รับค่าตัวแปรจากหัวตารางตามแนวนอน ซึ่งกำหนดให้ใช้เซลล์ I11:K11 บันทึกหมายเลข 1, 2, 3 ซึ่งหมายถึงเลขที่ case ไว้ ส่วนหัวตารางด้านซ้ายสุดตั้งแต่ H12:H18 เป็นสูตรที่ link การคำนวณมาจากตารางคำนวณงบกำไรขาดทุนจากเซลล์ D3:D9

เมื่อตาราง Data Table นี้ทำงาน เราจะได้ผลลัพธ์การคำนวณของแต่ละ case เสร็จในพริบตา แต่ถ้าเข้าใจขั้นตอนการทำงานของ Excel ทีละขั้น จะพบว่า Excel ค่อยๆส่งตัวเลขตัวแปร 1, 2, 3 บนหัวตารางด้านบนไปที่เซลล์ F2 หรือ CaseNum ทีละค่า จากนั้นสูตร Index จะทำหน้าที่ดึงตัวแปรแต่ละตัวตามเลข case ส่งไปคำนวณในงบกำไรขาดทุนด้านซ้ายสุด แล้วจึงส่งผลลัพธ์กลับมาแสดงในพื้นที่ตาราง I12:K18 ตาม case บนหัวตารางด้านบนของพื้นที่ซึ่งสั่ง Data > Table นั่นเอง (การใช้ Data > Table ร่วมกับสูตร Index นี้จะทำให้ Data > Table สามารถส่งตัวแปรไปใช้คำนวณได้ไม่จำกัดจำนวนตัวแปร ถือเป็นเคล็ดการใช้ Excel ที่น้อยคนนักจะใช้เป็น)

ตัวอย่างโครงสร้างตารางคำนวณแบบ Single Module แบบแยก Input ไว้ในชีทอื่น

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

ในตัวอย่างต่อไปเป็นการคำนวณทางสถิติของการวัด (Calibration) ก่อนอื่นขอเล่าที่ไปที่มาของตัวอย่างต่อไปนี้ก่อนว่า แต่เดิมทีนั้นแฟ้มมีขนาดใหญ่มากเพราะมีชีทนับร้อยชีท แต่ละชีทมีสูตรคำนวณทางสถิติยากๆยาวเหยียดหลายๆเซลล์

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

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

แทนที่จะต้องเปิดชีทใหม่ ขอให้ใช้หลักการสร้างตารางคำนวณแบบ Single Module โดยใช้โครงสร้างตารางแบ่งออกเป็น 3 ส่วน ดังนี้

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

จากเดิมซึ่งต้องมีหลายชีท จึงกลายเป็นแฟ้มใหม่ซึ่งมีชีทเพียง 2 ชีท ชีทหนึ่งเป็นชีทที่ใช้บันทึกผลการวัดคุณภาพแต่ละครั้ง แล้วใช้อีกชีทหนึ่งเป็นชีทแสดงการคำนวณทางสถิติ ( Download ตัวอย่างได้จาก www.ExcelExpertTraining.com/extreme/files/analysis/uncerdbf.xls)

โครงสร้างภายในชีทสำหรับบันทึกตัวเลขจากการวัด

uncerDBF.pngตารางตั้งแต่เซลล์ E2:E21 ใช้สำหรับบันทึกข้อมูลจากการวัดครั้งแรกแล้วใช้ column ถัดไปบันทึกข้อมูลจากการวัดในครั้งถัดไปติดต่อกันไปตามแนว column โดยในตัวอย่างนี้ตั้งชื่อพื้นที่ตารางส่วนที่ใช้บันทึกข้อมูลจากการวัดนี้ว่า DataRange

ตารางด้านซ้ายตั้งแต่เซลล์ C2:C21 เป็นสูตร Index ใช้สำหรับดึงข้อมูลที่บันทึกไว้ตามเลขที่ของการวัด ซึ่งใช้ตัวเลข 1-20 ใน Column D ช่วยในการชี้ตำแหน่งเลขที่ row

=INDEX( DataRange, D2, CaseNo )

  • DataRange คือพื้นที่ตาราง E2:N21 ใช้สำหรับบันทึกข้อมูลจากการวัด
  • D2 เป็นเลขที่ row เนื่องจากเป็นค่าแรกจึงใช้ดึงค่าจาก row ที่ 1
  • CaseNo เป็นเซลล์รับเลขที่ครั้งของการวัด ซึ่ง link มาจากชีทตารางคำนวณทางสถิติ

โครงสร้างภายในชีทสำหรับคำนวณทางสถิติของการวัด

uncerDBFCalc.png

เซลล์ B4 ถือเป็นหัวใจของชีทนี้ เมื่อคุณพิมพ์เลขที่ CaseNo ลงไป จะทำให้สูตร Index ใน column C ของอีกชีทหนึ่งตามภาพก่อนหน้านี้ ดึงข้อมูลส่งต่อมาใช้แสดงหรือคำนวณในตารางคำนวณ Uncertainty Budget อีกทั้งยังใช้เซลล์ B4 ร่วมกับคำสั่ง Data > Table เพื่อสรุปผลเปรียบเทียบการวัด หรือใช้ร่วมกับคำสั่งพิมพ์อัตโนมัติที่เขียนด้วย VBA ตามนี้ได้อีกด้วย

    For i = StartNum To StopNum
        Range("CaseNo") = i
        Calculate
        ActiveWindow.SelectedSheets.PrintPreview
    Next i

  • StartNum เป็นตัวแปรกำหนดตัวเลขเริ่มของเลขที่ Case ที่ต้องการพิมพ์เป็นรายการแรก
  • StopNum เป็นตัวแปรกำหนดตัวเลขสุดท้ายของเลขที่ Case ที่ต้องการพิมพ์เป็นรายการสุดท้าย
  • For Next Loop จะส่งค่า i ไปยังเซลล์ชื่อ CaseNo เพื่อดูผลก่อนพิมพ์บนจอ (Print Preview) ซึ่งถ้าเปลี่ยนเป็นคำสั่ง Print ก็จะพิมพ์รายงานตามรายการที่ต้องการโดยอัตโนมัติ

 

โปรดติดตามการออกแบบตารางคำนวณแบบ Single Module ในกรณีแยกหลายชีทหรือหลายแฟ้มในบทความภาค 3 ต่อไป

 

Categories

About this Entry

This page contains a single entry by สมเกียรติ ฟุ้งเกียรติ published on August 3, 2008 9:51 AM.

การออกแบบโครงสร้างตารางคำนวณ was the previous entry in this blog.

การออกแบบโครงสร้างตารางคำนวณ ภาค 3 is the next entry in this blog.

Find recent content on the main index.

Font s :
Background :