Microsoft Excel มีฟีเจอร์ให้ใช้มากมาย หนึ่งในฟีเจอร์ที่มีประโยชน์มากก็คือการทำ What-if analysis ซึ่งก็มีคุณสมบัติตรงตามชื่อเลย นั่นก็คือการหาผลลัพธ์เมื่อเปลี่ยน input ไปเรื่อยๆ
เราแบ่งบทความเกี่ยวกับ What-if analysis เป็นบทความย่อยตามนี้
- วิธีใช้ What-if analysis ใน excel สร้าง Data Table
- วิธีใช้ What-if กับ Goal Seek บน Microsoft Excel
ในวันนี้เราจะมาดูวิธีการสร้าง What-if Analysis Data Table กัน โดยตัวอย่างที่เราจะใช้วันนี้จะเป็นการคำนวณหาค่าดัชนีมวลกาย หรือ BMI (Body Mass Index) โดยเจ้า BMI จะมีสูตรตามข้างล่างนี้เลย
ดัชนีมวลกาย (BMI) = น้ำหนักตัว (กิโลกรัม) / ส่วนสูง (เมตร) x ส่วนสูง (เมตร)สำหรับคนที่อยากได้ข้อมูลเกี่ยวกับ BMI เพิ่มเติม สามารถไปอ่านได้ที่นี่ หรือจะไปหาอ่านในเว็บอื่นๆก็ได้
เรามาดูวิธีการใช้ What-if analysis Data Table กันเลยดีกว่า
ขั้นแรก เราจะมาหาค่า BMI ด้วยตัวอย่างน้ำหนักที่ 70 กิโลกรัม และความสูงที่ 170 เซนติเมตร (1.7 เมตร) ให้ใส่ค่าน้ำหนักและความสูงไปที่เซลล์ C2 และ C3 ตามลำดับ จากนั้นให้ใส่สูตรสำหรับ BMI ไปที่เซลล์ C5 เป็นค่าตามนี้
=C2/(POWER(C3, 2))
จากนั้นที่เซลล์ B6:B12 ให้เราใส่ตัวเลขน้ำหนักที่เราอยากนำมาใช้คำนวณค่า BMI สำหรับคนที่สูง 170 เซนติเมตร ในตัวอย่างนี้เราใช้ตัวเลข 50 – 80 แต่ผู้อ่านจะใช้ตัวเลขอะไรก็ได้ และจะใช้กี่ตัวเลขก็ได้
หลังจากเตรียมข้อมูลและสูตรเสร็จแล้ว ให้เลือกตารางในช่วง B5:C12 แล้วกดเลือกที่เมนู Data -> What-if Analysis -> Data Table…
Excel จะโชว์หน้าต่างขึ้นมาให้เลือก ในตัวอย่างนี้เราตั้งตัวแปรน้ำหนักเอาไว้ในแนวตั้งดังนั้นให้เราใส่ค่า C2 (น้ำหนัก) ไปบนช่อง Column input cell:
หลังจากนั้นให้กดปุ่ม OK เพียงเท่านี้เราก็จะได้ผลลัพธ์เป็นค่า BMI ในกรณีที่เรามีน้ำหนักตัว 50, 55, 60, …,80 ตามรูปข้างล่าง
คราวนี้ถ้ากรณีที่เราอยากรู้ว่าถ้าค่าความสูงก็เปลี่ยนแปลงไปด้วยแล้วจะมีค่า BMI เป็นเท่าไหร่ เราสามารถที่จะเพิ่มตัวแปรความสูงไปอีกมิติในแกนนอนได้ โดยการใส่ข้อมูลตามรูปข้างล่าง
จะเห็นว่าเราทำการย้ายช่องสูตร BMI จาก C5 มาที่ B5 จากนั้นเราใส่ตัวอย่างข้อมูลความสูงที่เราอยากจะใช้หาค่า BMI กรณีที่มีส่วนสูงต่างๆที่เราสนใจ เมื่อใส่ข้อมูลตามตารางข้างบนเสร็จแล้ว ให้เลือกเซลล์ B5:J12 แล้วเลือกเมนู Data -> What-if Analysis -> Data Tabel… ตามรูปข้างล่าง
คราวนี้ให้เราเลือกใส่ค่าทั้ง Row Input Cell และ Column Input Cell เป็น C3 และ C2 ตามลำดับ ในที่นี้จะเห็นว่าค่าตัวแปรของน้ำหนักจะเป็นข้อมูลในแนวดิ่งเราจึงใส่ C2 ซึ่งเป็นน้ำหนักที่เราใช้คำนวณสูตรใน B5 ไว้ที่ Column Input Cell และใส่ C3 ซึ่งเป็นข้อมูลส่วนสูงไว้ที่ Row Input Cell เพราะข้อมูลส่วนสูงอยู่ในแนวนอน
สุดท้ายให้กด OK แล้วเราก็จะได้ผลลัพธ์ตามรูปด้านล่าง
เพียงเท่านี้เราก็ได้ตารางค่า BMI สำหรับน้ำหนักและส่วนสูงต่างๆแล้ว การนำ What-f analysis มาใช้หาค่า BMI เป็นแค่การประยุกต์ใช้อย่างหนึ่ง เราสามารถนำเทคนิคนี้ไปประยุกต์ใช้ในงานอื่นๆ ได้อีกหลากหลาย เช่นการตั้งราคาสินค้า เป็นต้น