(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 ตามรูปต่อไปนี้
เซลล์ 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 ที่น้อยคนนักจะใช้เป็น)