วิธีทำ Excel ให้เป็น SUPER EXCEL ทำสูตรให้เป็น SUPER FUNCTIONS

วิธีทำ Excel ให้เป็น SUPER EXCEL ทำสูตรให้เป็น SUPER FUNCTIONS นี้เป็นอาวุธลับที่มีมานานตั้งแต่ Excel รุ่นแรก แต่ถูกเก็บไว้ในเมนูที่ไม่น่าจะอยู่ตรงนั้น เลยเหมือนถูกซ่อนไว้ลับมาก ชื่อที่ตั้งไว้บนเมนูก็ไม่เห็นจะตรงกับอะไรๆที่ SUPER เสียอีก
.
ก่อนอื่นขอเล่าเหตุการณ์ที่มักพบว่าเคยเจอกันทั้งนั้นมาให้ดูว่าจะใช้ Excel ง่ายๆได้ยังไง โดยไม่ต้องไปแตะ VBA หรือไปใช้สูตรอะไรที่ยุ่งยากแม้แต่น้อย
.
1. เคยไหมที่พอสร้างสูตร VLookup เพื่อหาค่าไว้แล้ว พอจะหาค่าอื่นก็ต้องสร้างสูตร VLookup แบบเดิมๆซ้ำอีกหรือ copy สูตรไปใช้ที่อื่นเพื่อใช้หาค่าใหม่ หรือถ้าอยากจะทำให้ VLookup สูตรเดิมนั่นแหละเปลี่ยนไปหาคำตอบจาก column อื่นก็ต้องเสียเวลาไปเปลี่ยนเลขที่ column
.
2. เคยไหมต้องสร้างชีทเพื่อแสดงรายงานหน้าตาแบบเดิมๆซ้ำแล้วซ้ำอีกเพียงเพื่อดูว่าแต่ละเดือนกำไรหรือขาดทุนเป็นเท่าไร การที่มีชีทจำนวนมากทำให้แฟ้มมีขนาดใหญ่ขึ้นและตรวจสอบความถูกต้องยากมากว่าทุกชีทคำนวณหาคำตอบแบบเดียวกันหรือไม่
.
3. เคยไหมที่เมื่อต้องการปรับสูตรที่ใช้อยู่ก็ต้องเสียเวลาไปแก้สูตรทุกสูตรให้เหมือนกัน
.
4. เคยห่วงไหมว่าสูตรที่ copy ไปใช้ต่อในตารางนับร้อยนับพันเซลล์นั้น จะถูกมือดีไปแก้สูตรบางเซลล์ให้ต่างไปจากเดิม
.
5. ทุกคนต้องเคยเจอแน่ๆ ตรงที่ไม่รู้ว่าสูตรที่ตัวเองสร้างขึ้นมานั้น จะหาคำตอบได้ถูกต้องเสมอไปไหม
.

ตัวอย่างแบบง่ายๆ

 

สมมติว่า ที่เซลล์ F3 สร้างสูตร VLookup เพื่อตัดเกรดไว้แล้ว หาได้ว่า 75 คะแนนสอบได้เกรด B จากนั้นพอจะตัดเกรดสำหรับคนอื่นอีก ไม่จำเป็นต้องสร้างสูตร VLookup ซ้ำอีกเลย แต่ให้กรอกคะแนนที่สอบได้ของคนอื่นไว้ตามตารางขวามือ แล้วลิงก์ผลลัพธ์จาก F3 ไปวางไว้ที่เซลล์ I2 แล้วทำตามขั้นตอนนี้

1. ให้เลือกพื้นที่ตารางด้านขวา H2:I16
2. คลิกเลือกเมนู Data Table จาก Data > What-IF Analysis
3. ในช่อง Column Input ให้คลิกเลือกเซลล์ E3 แล้วคลิก OK
4. Excel จะหาเกรดให้กับคะแนนให้เอง โดยสร้างเป็นสูตร {=TABLE(,E3)} ซึ่งสูตรนี้สร้างเองไม่ได้ ต้องให้ Excel สร้างขึ้นเท่านั้น

ลองไปแก้ไขสูตรต้นตอที่เซลล์ F3 ไปเป็นสูตรอื่น อะไรก็ได้ จะพบว่าตารางด้านขวาหาค่าใหม่ตามให้ทันที

Download ตัวอย่างได้จาก https://drive.google.com/file/d/1fZDHl5jD4uuOMcJbddA02fbZ-PsVPS2e/view?usp=drive_link 

ตัวอย่างซับซ้อนมากขึ้น 

ใช้ในงานทางคำนวณด้าน Uncertainty ของเครื่องมือวัด โดยลิงก์ข้อมูลผลจากการวัดแต่ละ Case เป็นตัวแปร 10 กว่าตัวมาจากชีท Input แล้ววิเคราะห์ผลออกมาแสดงเป็นรายงานในชีท Output

แทนที่จะต้องสร้างชีท Output เพื่อแสดงรายงานแต่ละ Case ก็สามารถใช้ชีท Output เพียงชีทเดียวแสดงรายงานของ Case ใดก็ได้ และเมื่อต้องการสรุปผลการวัดของทุก Case ก็ใช้ Data Table ช่วยสรุปให้ตามตารางด้านขวาของภาพนี้

Download ตัวอย่างได้จาก https://docs.google.com/spreadsheets/d/1YPhJx2Q9Cf8jBc0XCk3m0JWutAeftuLU/edit?usp=sharing&ouid=116915095293786385908&rtpof=true&sd=true

ตัวอย่างนี้เป็นส่วนหนึ่งในคลาส Sensitivity Analysis
https://www.excelexperttraining.com/private/all-courses-list/planning/sensitivity-analysis

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

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

หมายเหตุ

ตารางคำนวณที่สร้างจาก Data Table นี้ ไม่จำเป็นต้องปล่อยให้เสียเวลาคำนวณตลอดเวลา ให้เปลี่ยนจาก Automatic ไปเป็น Partial (หรือเดิมทีใช้คำว่า Automatic except for Data Table) จากนั้นให้กดปุ่ม F9 เมื่อต้องการให้คำนวณ