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

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

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

หมายเหตุ บทความในภาคนี้เป็นเรื่องต่อเนื่องกับเรื่องเดิมที่ได้เขียนอธิบายไปก่อนแล้ว จึงขอแนะนำให้ผู้อ่านเริ่มต้นศึกษาจากบทความภาคแรกเป็นลำดับไปจาก อ่านภาคแรก หรือนิตยสาร EWorld ฉบับเดือนกันยายน 2551 เป็นต้นมา

ลักษณะของตารางคำนวณ Single Module แบบแยกชีทหรือแยกแฟ้ม

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

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

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

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

โปรดสังเกตว่านอกเหนือจากตำแหน่งของตารางในชีท Case1, Case2, และ Case3 ต้องใช้ตำแหน่งเซลล์ตรงกันทั้งสามชีทคือใช้เซลล์ F3:F6 เก็บตัวเลขของยอดขายและต้นทุนแล้ว การตั้งชื่อชีทให้มีคำนำหน้าว่า Case เหมือนกันแล้วตามด้วยตัวเลข 1, 2, หรือ 3 ถือเป็นส่วนสำคัญที่จะช่วยให้สูตรที่ใช้ดึงข้อมูลจากชีทที่ต้องการสามารถทำได้ง่ายขึ้นด้วย ดังรูปต่อไปนี้ซึ่งเป็นชีทชื่อ SheetModule เป็นตารางคำนวณที่เลือกดึงข้อมูลจากชีทชื่อที่ต้องการมาคำนวณ

1. ประเด็นสำคัญคือตำแหน่งเซลล์ F3:F6 เป็นตำแหน่งเดียวกับข้อมูลที่แยกเก็บไว้ในชีท Case1, Case2, และ Case3
2. เซลล์ F2 เป็นเซลล์เดียวเท่านั้นที่มีการแก้ไขตัวเลข คุณสามารถบันทึกตัวเลข 1, 2, หรือ 3 ลงไปเพื่อแสดงถึงเลขที่ของ CaseNum ตาม case ที่ถูกแยกไว้ในชีทอื่น ดังนั้นเมื่อนำตัวเลขในเซลล์ F2 ไปต่อท้ายกับคำว่า Case โดยใช้สูตร ="Case"&$F$2 ย่อมได้ชื่อชีทที่ต้องการ (ตามภาพนี้ F2 มีเลข 2 บันทึกอยู่ ดังนั้นสูตร ="Case"&$F$2 จึงคืนค่าเป็นคำว่า Case2)
3. สูตรในเซลล์ F3:F6 คือ =INDIRECT("Case"&$F$2&"!"&ADDRESS(ROW(),COLUMN()))
   3.1. สูตรส่วนตั้งแต่ "Case"&$F$2&"!" จะคืนค่าออกมาเป็นคำว่า Case2!
   3.2. สูตรส่วนของ ADDRESS(ROW(),COLUMN()) คืนค่าออกมาเป็นตำแหน่งเซลล์ของเลขที่ row และเลขที่ column ของตำแหน่งเซลล์ที่มีสูตรนี้อยู่ เช่น ในเซลล์ F3 ส่งผลให้สูตร Row() มีค่าเป็น 3 และสูตร Column() มีค่าเป็น 6 เพราะ Column F เป็น Column ที่ 6 จึงส่งค่าไปยังสูตร ADDRESS(3,6) ให้คืนค่าเป็น $F$3
   3.3. เมื่อนำผลลัพธ์คำว่า Case2! มาเชื่อมกับคำว่า $F$3 ด้วยสูตร "Case"&$F$2&"!"&ADDRESS(ROW(),COLUMN()) จึงได้ชื่อชีทกับตำแหน่งเซลล์ที่ต้องการออกมาเป็นคำว่า "Case2!$F$3"
   3.4. สูตร Indirect จึงหาค่าจากชื่อชีทและตำแหน่งเซลล์ "Case2!$F$3" ได้ตัวเลข 200 ตามต้องการ
4. เมื่อหาค่าด้วยสูตร Indirect ในเซลล์ F3:F6 ได้แล้ว จึงส่งค่าต่อไปคำนวณในตารางคำนวณงบการเงินในพื้นที่ตารางด้านซ้ายสุด D3:D6 เพื่อใช้คำนวณหา Margin

เคล็ดลับที่ใช้ร่วมกับโครงสร้างตารางคำนวณ Single Module แบบแยกชีท

1. กรณีต้องการสร้างชีทหลายชีทให้มีหน้าตาแบบเดียวกันโดยการสร้างงานพร้อมกันเพียงครั้งเดียว ให้เริ่มจากกดปุ่ม Ctrl พร้อมกับคลิกที่ Sheet Tab เพื่อเลือกชีทที่ต้องการ หรือหากต้องการเลือกทุกชีทที่ติดกัน ให้เริ่มจากอยู่ในชีทแรกแล้วกดปุ่ม Shift พร้อมกับคลิกเลือกชีทสุดท้าย ซึ่งจะพบคำว่า [Group] แสดงไว้ต่อท้ายชื่อแฟ้มที่ปรากฏอยู่ด้านบนสุดของหน้าจอ จากนั้นทุกอย่างที่คุณสร้างลงไปในชีทจะถูกสร้างพร้อมกันลงไปในชีททุกชีทที่เลือกไว้ใน [Group] เดียวกัน และเมื่อต้องการเลิกงานให้คลิกเลือกชีทอื่นที่ไม่ได้เลือกไว้หรือชีทอื่นในกรณีที่เดิมเลือกไว้ทุกชีท ซึ่งจะพบว่าคำว่า [Group] หายไป
2. กรณีต้องการสร้างชีทหลายชีทให้มีหน้าตาแบบเดียวกันโดยไม่ต้องใช้วิธี Copy จากชีทต้นแบบไปยังชีทอื่น ให้เลือกชีทแบบ [Group] โดยกดปุ่ม Ctrl หรือ Shift แล้วคลิกเลือกชีทเช่นเดียวกับวิธีในข้อแรก จากนั้นให้คลิกเลือกพื้นที่ตารางที่ต้องการ แล้วสั่ง Home > Fill > Across Worksheets หรือใน Excel 2003 ให้สั่ง Edit > Fill > Across Worksheets
3. กรณีต้องการคำนวณหายอดรวมของทุกชีท ให้เริ่มจากพิมพ์สูตร =sum( แล้วคลิกเซลล์ในชีทแรกที่ต้องการ จากนั้นกดปุ่ม Shift แล้วคลิกเลือกชีทสุดท้ายแล้วกดปุ่ม Enter จะได้สูตร =SUM(Case1:Case3!F3) โดยสูตรนี้จะหายอดรวมของเซลล์ F3 จากทุกชีทตั้งแต่ชีทชื่อ Case1 ถึง Case3 ให้ทันที โดยไม่ต้องเสียเวลาสร้างสูตรบวกต่อกันทีละเซลล์ทีละชีท ตามสูตร =Case1!F3+Case2!F3+Case3!F3 อีกต่อไป
4. กรณีต้องการคำนวณหายอดรวมของทุกชีท "เว้น" เฉพาะค่าจากชีทที่มีสูตรยอดรวม ให้พิมพ์สูตร =sum('*'!f3) โดยขอสมมติว่าในแฟ้มมี 3 ชีทติดกันตามลำดับ คือชื่อ Sheet1, Sheet2, และ Sheet3
   ถ้าสร้างสูตรนี้ลงไปใน Sheet1 จะได้สูตร =SUM(Sheet2:Sheet3!F3)
   แต่ถ้าสร้างสูตรลงไปใน Sheet2 จะได้สูตร =SUM(Sheet1!F3,Sheet3!F3)
   หรือถ้าสร้างสูตรลงไปใน Sheet3 จะได้สูตร =SUM(Sheet1:Sheet2!F3)
5. กรณีต้องการจัดการแสดงโครงสร้างแต่ละชีทให้ดูต่างกันไปหลายๆแบบโดยอัตโนมัติ ให้จัดโครงสร้างตารางโดยอาจสั่ง Hide Row/Column ซ่อนสิ่งที่ไม่ต้องการแสดง เพื่อทำให้เห็นเฉพาะพื้นที่ตารางเท่าที่ต้องการก่อน จากนั้นให้สั่ง View > Custom Views > Adds แล้วตั้งชื่อ View ให้กับโครงสร้างตารางที่เห็นอยู่บนจอในขณะนั้น จากนั้นให้จัดหน้าตาตารางใหม่ในชีทเดิมซ้ำหรือในชีทอื่นตามต้องการแล้วตั้งชื่อ Custom View เพิ่มเติมจนครบ แล้วเมื่อใดที่ต้องการไปที่ชีทใดก็ไม่จำเป็นต้องใช้วิธีไล่คลิกไปทีละชีทอีก แต่ให้สั่ง View > Custom View แล้วคลิกเลือกชื่อ View แทน ซึ่งวิธีนี้นอกเหนือจากช่วยเลือกตารางในชีทที่ต้องการให้แล้ว ในแต่ละ View ยังบันทึก Print Settings และ Filter Settings ตามให้อีกด้วย
6. กรณีต้องการใช้สูตรเพื่อค้นหาชื่อชีททุกชีทมาแสดงในเซลล์  ให้ใช้สูตร =GetSheetName(เลขที่ชีท) โดยสร้างรหัส Function VBA ต่อไปนี้ไว้ในแฟ้ม
         Function GetSheetName(x)
             GetSheetName = Sheets(x).Name
         End Function

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

1. แก้สูตร Indirect ในเซลล์ F3:F6 เป็นสูตร =Case1!F3, =Case1!F4, =Case1!F5, และ =Case1!F6 ตามลำดับเพื่อ link ข้อมูลมาจากชีทชื่อ Case1 ในตำแหน่งเซลล์เดียวกัน
   (Download ตัวอย่างได้จาก www.ExcelExpertTraining.com/extreme/files/database/singlemodulelink.zip)
2. Move ชีทชื่อ Case1, Case2, Case3 ไปเป็นแฟ้มใหม่ โดยใช้วิธีคลิกขวาที่ชื่อชีท Case1 แล้วเลือก Move or Copy จากนั้นคลิกเลือก (new book) แล้วกด OK จะพบว่าชีท Case1 ถูกย้ายทั้งชีทไปเป็นแฟ้มใหม่ที่มีชีทเพียงชีทเดียว แล้วให้ทำซ้ำกับชีท Case2 และ Case3 ให้เป็นแฟ้มใหม่แยกจากกันโดยใช้วิธีเดียวกันนี้อีก
3. จัดเก็บแฟ้มที่เกิดขึ้นใหม่จากการย้ายชีท Case1, Case2, และ Case3 โดยตั้งชื่อแฟ้มเป็น SingleModuleLinkFilesCase1.xls, SingleModuleLinkFilesCase2.xls, และ SingleModuleLinkFilesCase3.xls ตามลำดับ แล้วเมื่อกลับไปคลิกดูสูตรในเซลล์ F3:F6 ในชีท SheetModule จะพบว่าสูตรเดิมที่เคยเป็นสูตร link ข้ามชีทถูกแก้ไขเองเป็นสูตร link ข้ามแฟ้ม
   =[SingleModuleLinkFilesCase1.xls]Case1!F3,
   =[SingleModuleLinkFilesCase1.xls]Case1!F4,
   =[SingleModuleLinkFilesCase1.xls]Case1!F5, และ
   =[SingleModuleLinkFilesCase1.xls]Case1!F6 ตามลำดับให้เอง
4. เมื่อต้องการดึงข้อมูลจาก Case2 หรือ Case อื่น ให้เปิดแฟ้มปลายทางที่มีสูตร link ข้ามแฟ้มแสดงอยู่ขึ้นมาใช้งานเพียงแฟ้มเดียว (โดยไม่ต้องเปิดแฟ้มต้นทาง) จากนั้นสั่ง Office > Prepare > Edit Links to Files หรือใน Excel 2003 สั่ง Edit > Links
5. ให้คลิกเลือกชื่อแฟ้มต้นทางเดิม แล้วกดปุ่ม Change Source จากนั้นไล่คลิกหาแฟ้มต้นทางใหม่ที่ต้องการ ซึ่งเมื่อเลือกแฟ้มต้นทางใหม่ได้เรียบร้อยและกดปุ่ม OK > Close กลับมาแฟ้มปลายทาง จะพบว่าสูตรเก่าถูกแก้ไขให้ดึงข้อมูลมาจากแฟ้มต้นทางใหม่ให้เองทันที

ข้อควรระวังในการใช้ข้อมูลที่ link ข้ามแฟ้ม

1. โครงสร้างภายในของแฟ้มต้นทางต้องเหมือนกันทุกแฟ้ม โดยตำแหน่งเซลล์ใดเป็นข้อมูลอะไรก็ต้องกำหนดให้ทุกแฟ้มใช้ตำแหน่งเซลล์เดียวกันทั้งหมด
2. หากแฟ้มต้นทางมีชีทเดียวแต่ใช้ชื่อชีทต่างกันเช่นในตัวอย่างนี้ใช้ชื่อชีทว่า Case1, Case2, และ Case3 เมื่อสั่ง Change Source จะพบว่า Excel สามารถปรับสูตร link ข้ามแฟ้มให้ถูกต้อง แต่ถ้าแฟ้มต้นทางมีหลายชีท ต้องตั้งชื่อชีทให้ตรงกันทุกแฟ้มด้วย มิฉะนั้น Excel จะมีขั้นตอนเปิดจอขึ้นมาถามว่าให้จับชื่อชีทใดแทนด้วยชื่อชีทใด
3. ถ้าต้องการแก้ไขโครงสร้างตารางโดยเฉพาะตำแหน่งเซลล์ในแฟ้มต้นทาง ก่อนจะแก้ไขใดๆให้เปิดทุกแฟ้มที่ link กัน ทั้งแฟ้มต้นทาง แฟ้มปลายทาง รวมทั้งแฟ้มต้นทางใหม่ที่ยังไม่ได้ link ขึ้นมาบนจอให้ครบทั้งหมดก่อน จากนั้นจึงค่อยทำการแก้ไข เพื่อทำให้ Excel ไล่แก้ตำแหน่งอ้างอิงในสูตรที่อ้างถึงแฟ้มที่เกี่ยวข้องให้เอง (ถ้าไม่ได้เปิดแฟ้ม Excel จะละเลยไม่สนใจตามไปแก้ไขให้แม้แต่น้อย)
4. ถ้าต้องการ Save เพื่อตั้งชื่อแฟ้มใหม่หรือย้าย Folder ที่เก็บแฟ้ม ให้สั่ง Save แฟ้มต้นทางก่อนแล้วจึง Save แฟ้มปลายทางทีหลัง (แต่ถ้าไม่ได้แก้ไขชื่อแฟ้มหรือย้าย Folder จะ Save แฟ้มใดก่อนก็ได้ แต่ต้อง Save ให้ครบทุกแฟ้ม)

โปรดติดตามการออกแบบตารางคำนวณแบบ Compound Module ในบทความภาคต่อไป