วิธีใช้คำสั่ง Data Table

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

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

Data Table มี 3 แบบตามจำนวนตัวแปรที่เกี่ยวข้อง ได้แก่

• Data Table แบบ 1 ตัวแปร
• Data Table แบบ 2 ตัวแปร
• Data Table แบบไม่จำกัดตัวแปร โดยใช้ร่วมกับสูตร Index (กลายเป็นวิธีที่ฉลาดกว่าการใช้ Scenarios)

โครงสร้างตาราง Data Table แบ่งเป็น 4 ส่วน ตามรูป

Data Table แบบ 1 ตัวแปร

ในภาพนี้เซลล์ I3 และ J3 เป็นเซลล์สูตร VLookup เพื่อหาว่ารหัส NewID a001-2 ซึ่งกรอกลงไปในเซลล์ H3 นั้นมีชื่อ Name และจำนวน Amount เท่ากับ c และ 30 ตามลำดับ

เมื่อต้องการหารายละเอียดของรหัส NewID ตัวอื่น แทนที่จะต้องเสียเวลากรอกรหัสอื่นลงไปในเซลล์ H3 เพื่อดูคำตอบเป็นครั้งๆไปหรือจะต้องสร้างสูตร VLookup ทำนองเดียวกับสูตรในเซลล์ I3 และ J3 ซ้ำอีกเพื่อใช้ค้นหารายละเอียดของรหัสอื่นทั้งหมด ให้สร้างตาราง Data Table แบบ 1 ตัวแปรตามที่เห็นจากภาพนี้คือตารางจากเซลล์ H5:J10

ปัญหานี้ต้องการเปลี่ยนรหัสเพียงค่าเดียว ดังนั้นจึงเข้าข่าย Data Table แบบ 1 ตัวแปร ซึ่งมีโครงสร้างแยกแยะออกไปอีก 2 แบบย่อยตามแต่ว่าคุณต้องการดูตัวแปรตามแนวใด ได้แก่

• Data Table แบบวางตัวแปรไว้ในหัวตารางด้านข้างตามแนวตั้ง ส่วนหัวตารางด้านบนเป็นสูตรคำตอบที่ link มา และเมื่อสั่ง Data Table ให้เลือกใช้ช่อง Column Input Cell
• Data Table แบบวางตัวแปรไว้ในหัวตารางด้านบนตามแนวนอน ส่วนหัวตารางด้านข้างเป็นสูตรคำตอบที่ link มา และเมื่อสั่ง Data Table ให้เลือกใช้ช่อง Row Input Cell

ตัวอย่างการใช้ Data Table แบบ 1 ตัวแปร โดยวางตัวแปรไว้ในหัวตารางด้านข้างตามแนวตั้ง

1. นำรหัสที่ต้องการหารายละเอียดพิมพ์ลงในเซลล์ H6:H10 โดยจะกำหนดให้รหัสใดขึ้นก่อนหลังก็ได้และไม่จำเป็นต้องเรียงลำดับมากน้อย
2. เซลล์ I5 และ J5 สร้างสูตร link ผลลัพธ์ของสูตรคำนวณมาจากเซลล์ I3 และ J3 ตามลำดับ
3. ให้เลือกพื้นที่ H5:J10 แล้วสั่ง Data > What-If Analysis > Data Table
4. คลิกลงในช่อง Column input cell แล้วคลิกต่อไปที่เซลล์ H3 (เพื่อกำหนดให้ Data Table ยิงรหัสที่วางไว้ตามแนวตั้งไปที่เซลล์ H3) แล้วกดปุ่ม OK
5. จะพบคำตอบปรากฏในตาราง I6:J10 และเมื่อคลิกดูจะพบสูตร {=TABLE(,H3)} ซึ่งสูตรนี้ไม่สามารถสร้างโดยการพิมพ์เอง แต่ต้องใช้คำสั่ง Data Table เท่านั้นจึงจะเกิดสูตรนี้ขึ้น และเมื่อดูในวงเล็บของสูตรจะพบ (,H3) แสดงถึงการไม่ใช้ Row Input แต่ใช้ Column Input มีเซลล์ H3 เป็นเซลล์รับค่า 

Data Table แบบ 2 ตัวแปร

ตัวอย่างนี้ต้องการหายอด Actual ของ Division และ Department ที่กรอกลงไปในเซลล์ G3 และ H3 ตามลำดับ โดยคำตอบที่ต้องการสามารถใช้สูตรใดสูตรหนึ่งจากสูตรในเซลล์ I3:I8 ซึ่งสร้างไว้เพื่อการศึกษา ดังนี้

• =DSUM(B2:E6,4,G2:H3)
• =VLOOKUP(G3&H3,D3:E6,2,0)
• =INDEX(Actual,MATCH(G3&H3,D3:D6,0))
• =SUMIF(D3:D6,G3&H3,Actual)
• =SUMPRODUCT((Division=G3)*(Department=H3)*Actual)
• =SUMPRODUCT((Division&Department=G3&H3)*Actual)

เมื่อต้องการหายอด Actual ของแต่ละ Division และ Department  จึงเข้าข่าย Data Table แบบ 2 ตัวแปร

1. พิมพ์ชื่อ Department แต่ละตัวไว้ในเซลล์ H10:K10 ซึ่งถือเป็น Row Input
2. พิมพ์ชื่อ Division แต่ละตัวไว้ในเซลล์ G11:G14 ซึ่งถือเป็น Column Input
3. ในเซลล์หัวมุมตาราง G10 สร้างสูตร =I3 เพื่อ link สูตรคำนวณหา Actual หรือจะเลือกลิงค์สูตรหา Actual จากเซลล์ใดก็ได้จากสูตรในเซลล์ I3:I8
4. จากนั้นให้เลือกพื้นที่ตั้งแต่ G10:K14 แล้วสั่ง Data > What-If Analysis >Data Table
5. คลิกลงไปในช่อง Row Input Cell แล้วคลิกต่อไปยังเซลล์ H3 ซึ่งรับชื่อ Department
6. คลิกลงไปในช่อง Column Input Cell แล้วคลิกต่อไปยังเซลล์ G3 ซึ่งเป็นเซลล์รับชื่อ Division แล้วกดปุ่ม OK จะพบคำตอบของยอด Actual แสดงในตาราง H11:K14