กลับหน้าแรกของคอร์ส

Excel - Data Analysis

การทำ Data Analysis ด้วย Excel

ดาวน์โหลดไฟล์ประกอบการเรียนได้ที่นี่ - What-If Analysis

ดาวน์โหลดไฟล์ประกอบการเรียนได้ที่นี่ - Solver/Analysis Toolpak

What-If Analysis

ตัว Excel เองนั้นสนับสนุนการทำ Data Analysis ผ่านการใช้ Formulas และ Functions ต่าง ๆ โดยเชื่อมโยงการผ่านการทำ Referencing หรือจะเรียกอีกแบบว่า Dynamic Model ก็ได้ เพราะหากเรามีการเปลี่ยนแปลงข้อมูลใด ๆ ข้อมูล หรือสูตรคำนวณอื่น ๆ ที่เชื่อมโยงกันก็จะอัพเดตไปโดยอัตโนมัติ ซึ่งฟีเจอร์นี้เอง นำไปสู่การทำ What-If Analysis ซึ่งเป็นการตั้งคำถามวิเคราะห์ ตัวอย่างเช่น

• จะเกิดอะไรขึ้นถ้าดอกเบี้ยเงินกู้เปลี่ยนเป็น 7.5%
• อะไรจะเกิดขึ้นถ้าเราขึ้นราคาสินค้าอีก 5%

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

จากตัวอย่างนี้ เราอาจจะทำ What-If Analysis แบบง่าย ๆ โดยตั้งคำถามดังต่อไปนี้

• จะเป็นอย่างไร ถ้าเราสามารถต่อรองราคาในการซื้อสินทรัพย์ให้ลดลงได้อีก
• จะเป็นอย่างไร ถ้าผู้ให้กู้ต้องการเงินดาวน์ถึง 20%
• จะเป็นอย่างไร ถ้าเราสามารถผ่อนชำระได้ถึง 40 ปี
• จะเป็นอย่างไร ถ้าดอกเบี้ยขึ้นไปถึง 5.5%

โดยเราสามารถที่จะปรับแก้ไขค่าในเซลล์ C4:C7 แล้วดูผลการเปลี่ยนแปลงใน Result Cells

Types of What-If Analyses

การทำ What-If Analyses นั้นแบ่งออกเป็น 3 ประเภท ดังนี้

• แบบ Manual คือการทำ What-If analysis แบบใช้สูตรผูกกับฟังก์ชั่นต่าง ๆ แล้วก็เชื่อมกันผ่าน Cell Referencing ที่เราทำการมาในบทเรียนก่อน ๆ
• แบบ Data tables คือการสร้างตารางแบบพิเศษมาเพื่อแสดงผลการคำนวณที่เปลี่ยนแปลงไปอย่างเป็นระบบ ตัวอย่างเช่น ตารางคำนวณอัตราผ่อนชำระค่างวด โดยแสดงให้เห็นถึงความสัมพันธ์ระหว่างค่างวด กับอัตราดอกเบี้ยและจำนวนระยะเวลาที่ผ่อนชำระ
• แบบ Scenario Manager คือการสร้างสถานการณ์จำลองในรูปแบบ outlines หรือ PivotTables.
• แบบ Goal Seek คือ การปรับ Input เพื่อให้ได้ผลตามเป้าหมายที่เราต้องการ

ตัวอย่าง What-If Analysis: Data table

จากตัวอย่างการกู้ยืมเงินแบบจดจำนองสินทรัพย์ เราสามารถทำ What-If Analysis แบบ Data table ได้โดยการ สร้างตารางบนพื้นที่ E2:I12 โดยให้ Interest rate นั้นเพิ่มขึ้นทีละ .25 และให้คอลัมน์ F:I แสดงผลการคำนวณตามแบบใน Result Cells

เราสามารถทำ What-If Analysis: Data table โดยการเลือกช่วงเซลล์ที่ต้องการสร้าง Data table ในกรณีนี้คือ E3:I12 แล้วไปที่ Ribbon => Data => What-if Analyis => Data Table จากนั้น เลือก C7 ซึ่งเป็นค่า Interest rate ให้เป็น Column input cell ดังภาพ

หลังจากกด OK เราก็จะได้ค่าผลลัพธ์ที่คำนวณตามอัตราดอกเบี้ยจากคอลัมน์ E ดังภาพ

จากตัวอย่างข้างต้น เป็นการทำ What-If Analysis จาก Input เดียว ทีนี้เราลองมาดูตัวอย่างแบบ 2 Inputs กันบ้าง โดยตัวอย่างนี้ เป็นการคำนวณต้นทุน และกำไรสุทธิในการโปรโมชั่นผ่านจดหมาย ซึ่งจะใช้ตัวแปรสองตัวก็คือ จำนวนจดหมาย และอัตราการตอบกลับ ซึ่งการทำ What-If Analysis จะสร้างตารางวิเคราะห์กำไรสุทธิในช่วงเซลล์ E4:M14 โปรดสังเกตที่เซลล์ E4 นั้นได้มีการทำ Reference ไปยัง Net Profit

เราสามารถทำ What-If Analysis: Data table โดยการเลือกช่วงเซลล์ที่ต้องการสร้าง Data table ในกรณีนี้คือ E4:M14 แล้วไปที่ Ribbon => Data => What-if Analyis => Data Table จากนั้น เลือก B5 ซึ่งเป็นค่า Respone rate ให้เป็น Row input cell และ B4 ซึ่งเป็นค่า Number mailed ให้เป็น Column input cell ดังภาพ

หลังจากกด OK เราก็จะได้ค่า Net Profit ที่คำนวณตาม E4 แต่ใช้ข้อมูล Number mailed และ Response reate จาก Data table เป็น 2 inputs ในการคำนวณ ดังภาพ

ตัวอย่าง What-If Analysis: Scenario Manager

เนื่องจาก Data table นั้นมีข้อจำกัดในเรื่องของจำนวน input ที่มีได้แค่สองตัว Scenario Manager นั้นสามารถใส่ Input ได้มากตามจำนวน Scenario ที่มี ดูจากตัวอย่างด้านล่าง จะเป็นการคำนวณต้นทุนและกำไรของการผลิตสินค้า A, B และ C โดยมี input คือ ค่าแรงรายชั่วโมง และต้นทุนวัตถุดิบ ซึ่งมีค่าแปรผันไปตาม scenario 3 แบบ คือ Best case, Worst case และ Most likely

ในการใช้ Scenario Manager มีขั้นตอนดังต่อไปนี้

• สร้าง Scenario โดยไปที่ Ribbon => Data => What-if Analyis => Scenario Manager แล้วจะปรากฏหน้าต่างดังภาพ

• จากนั้นให้กด + เพื่อเพิ่ม Scenario โดยให้ใส่ Scenario name และกำหนด Changin cells เป็น B2:B3 ตามภาพ

• ขั้นตอนถัดไป คือการกำหนดค่าตาม Scenario ซึ่งในกรณี Best Case คือ Hourly Cost = 30 และ Material Cost = 57

• ใส่ Scenario จนครบตามภาพ

• จากนั้นกด Summary... เพื่อทำการวิเคราะห์

• หากเลือก Scenario summary ก็จะได้ผลตามภาพ

ตัวอย่าง What-If Analysis: Goal Seek

ในการคำนวณการผ่อนชำระเงินกู้ ที่ใช้ในตัวอย่างก่อนหน้าจะเป็นการวิเคราะห์แบบไปข้างหน้า Forward แต่หากเราอยากจะกลับคำนวณย้อนกลับ Reverse เช่น เราอยากระบุว่าเราต้องการชำระเงินงวดละไม่เกิน $1,800 ก็สามารถทำได้ โดยใช้ Goal Seek โดยมีขั้นตอน ดังนี้

• ไปที่ Ribbon => Data => What-if Analyis => Goal Seek และกำหนดค่า Set cell เป็น C11 Monthly Payment ใส่ค่า To value เป็น 1800 ในกรณีนี้เราจะกำหนดให้ปรับเปลี่ยนที่ Purchase Price โดยกำหนด By changing cell เป็น C4 ดังภาพ

• เมื่อวิเคราะห์เสร็จ จะเห็นว่า Purchase Price ถูกเปลี่ยนจาก $385,500 เป็น $362,185 ดังภาพ

Solver

ในการทำ What-If Analysis ด้วย Goal Seek นั้นมีข้อจำกัด คือ ไม่สามารถกำหนดการปรับเปลี่ยนค่าในหลาย ๆ เซลล์ได้ หรืออาจจะถึงขั้นวิเคราะห์ย้อนกลับแบบหลาย ๆ Scenarios ซึ่ง Solver เป็นฟีเจอร์ของ Excel ที่เข้ามาตอบโจทย์นี้ โดย Solver สามารถที่จะ

• กำหนดให้ปรับค่าได้หลาย ๆ เซลล์
• กำหนดเงื่อนไขให้ค่าที่จะปรับในแต่ละเซลล์ได้
• หา solution ได้ทั้งแบบ maximise และ minimise
• หาคำตอบได้หลายทางเลือกให้คำโจทย์ที่ต้องการจะแก้

อย่างไรก็ตาม Solver นั้นยังทำงานไม่สมบูรณ์ใน Excel เวอร์ชั่นอื่น ๆ ที่ไม่ใช่ Windows

ดาวน์โหลดไฟล์ประกอบการเรียนได้ที่นี่ - Solver/Analysis Toolpak

ตัวอย่าง Solver - Three Products

ตัวอย่างนี้จะแสดงการคำนวณกำไรจากผลิตภัณฑ์สามตัว ซึ่งมีจำนวนหน่วย กำไรต่อหน่วย และกำไรต่อผลิตภัณฑ์ (คำนวณจากการเอาจำนวนหน่วย x กำไรต่อหน่วย) ดังภาพ

หากมองดูผิวเผินก็จะเห็นว่า Product C นั้นทำกำไรสูงสูด แต่ในสถานการณ์จริงนั้น จะมีเงื่อนไขต่าง ๆ ที่มาเป็นตัวกำหนดในการผลิต ดังนี้

• จำนวนการผลิตทั้งหมดต้องเป็น 300 หน่วย
• มีความต้องการ Product A จำนวน 50 หน่วย จากยอดสั่งซื้อเดิม
• มีความต้องการ Product B จำนวน 40 หน่วย จากการคาดการณ์
• เนื่องจาก Demands ไม่มากจึงควรผลิต Product C ไม่เกิน 40 หน่วย

จากเงื่อนไขข้างต้น เราสามารถใช้ Solver ในการคำนวณหาหน่วยการผลิตได้ ดังนี้

• ไปที่ Ribbon => Data => Solver

• จากนั้นให้ทำการกำหนดค่าพารามิเตอร์ต่าง ๆ ให้ Solver ดังนี้
  • กำหนดเป้าหมายที่ Set Objective ในที่นี้คือ Total Profit ที่ D6
  • โดยกำหนดให้ To เป็น Max ซึ่งก็คือกำไรสูงสุด
  • กำหนดให้ Changing Variable Cells เป็น B3:B5 ซึ่งก็คือ จำนวนสินค้าที่จะผลิต
  • จากนั้นกำหนดเงื่อนไขต่าง ๆ
    • Product A : B3 >= 50
    • Product B : B4 >= 40
    • Product C : B5 <= 40
    • Total Product : B6 <= 300
  • เลือก Solving Method เป็น Simplex LP

• จากนั้นกด Solve แล้ว Solver ก็จะทำการคำนวณ จำนวนการผลิตที่ให้ได้กำไรสูงสุด ภายใต้เงื่อนไขต่าง ๆ ที่กำหนด ได้ผลดังภาพ

ตัวอย่าง Solver - Investment Portfolio (Excel for Windows only)

ตัวอย่างถัดมา เป็นการจัดการ portfolio การลงทุนอย่างง่าย ๆ โดยมีเงื่อนไข ดังต่อไปนี้

• การลงทุนใน New car loans ต้องเป็น 3 เท่าของ Used car loans
• การลงทุนใน Car loans ทั้งสองแบบต้องมากกว่า 15% ของทั้งพอร์ต
• Unsecured loans ไม่ควรเกิน 25% ของ Portfolio
• อย่างน้อย 10% ของ Portfolio ควรเป็นการลงทุนใน Bank CDs
• ยอดเงินลงทุนทั้งหมดคือ $5,000,000
• การลงทุนทั้งหมดควรจะได้ผลตอบแทนในแดนบวก หรือเท่าทุน

• จากนั้นให้ทำการกำหนดค่าพารามิเตอร์ต่าง ๆ ให้ Solver ดังนี้
  • กำหนดเป้าหมายที่ Set Objective ในที่นี้คือ Total Yeild ที่ D12
  • โดยกำหนดให้ To เป็น Max ซึ่งก็คือกำไรสูงสุด
  • กำหนดให้ Changing Variable Cells เป็น C5:C9 ซึ่งก็คือ จำนวนเงินลงทุน
  • จากนั้นกำหนดเงื่อนไขต่าง ๆ
    • ยอดรวมเงินลงทุน : C10 = B1
    • การลงทุนใน New car loans : C5 >= C6*3
    • เงินลงทุนใน Auto Loan : D14 >= 0.15
    • เงินลงทุนใน Unsecured Loan : E8 <= 0.25
    • เงินลงทุนใน Bank CDs : E9 >= 0.1
  • เลือก Make Unconstrained Variables Non-Negative
  • เลือก Solving Method เป็น GRG Nonlinear

• จากนั้นกด Solve แล้ว Solver ก็จะทำการคำนวณ % Total Yeild สูงสุด ภายใต้เงื่อนไขต่าง ๆ ที่กำหนด ได้ผลดังภาพ

หมายเหตุ ตัวอย่างนี้ไม่สามารถใช้กับ Excel for Mac ได้

Analysis Toolpak

Analysis Toolpak เป็นส่วนเสริมของ Excel ที่ช่วยในการวิเคราะห์เชิง Scientific, Engineering, Statistics ซึ่งถือเป็นการวิเคราะห์ในขั้นสูง และต้องการพื้นฐานความรู้ในเชิงทฤษฎี จึงจะสามารถใช้งานได้ ส่วนการใช้งาน Analysis Toolpak ในบางกรณีอาจจะต้องทำการติดตั้งเพิ่มเติม ซึ่งสามารถหาวิธีติดตั้งได้จากอินเตอร์เน็ต

ในบทเรียนนี้จะเป็นการแนะนำการใช้งาน Analysis Toolpak อย่างง่าย ๆ

ตัวอย่าง Analysis Toolpak

ข้อมูลดังภาพด้านล่างเป็นตัวอย่างคะแนนของ นักเรียนสองกลุ่ม โดยเราสามารถทำการวิเคราะห์ Descriptive Statistics โดยการไปที่ Data => Data Analysis

เมื่อเลือก Descriptive Statistics แล้วจะต้องกรอก Input ดังนี้

• Input Range: A1:B10
• เลือก Labels in first row
• เลือก Output Rage: D1
• เลือก Summary statistics

เมื่อกด OK แล้วจะได้ตาราง Descriptive Statistics ดังภาพ

ถัดไปจะเป็นการทดสอบความแปรปรวน F-Test ของนักเรียนทั้งสองกลุ่ม เพื่อวิเคราะห์ว่า ทั้งสองกลุ่มมีความแตกต่างกัน ในเชิงสถิติหรือไม่ โดยเลือก F-Test Two-Sample for Variances ดังภาพ

จากนั้นกำหนด Input ของ F-Test ดังนี้

• Variable 1 Range: A1:A10
• Variable 2 Range: B1:B10
• เลือก Label
• Alpha: 0.05
• Output Range: D1

เมื่อกด OK แล้วก็จะได้ผลการวิเคราะห์ F-Test ดังภาพ โดยในกรณีนี้ สามารถสรุปได้ว่า ทั้งสองกลุ่มมีความแตกต่างกันในทางสถิติ โดยดูจากค่า P ในเซลล์ E9 ที่มีค่าน้อยกว่า 0.05

วิดีโอสำหรับเรียนย้อนหลัง

Clip: What-If Analysis

Clip: Solver / Analysis Toolpak

เอกสารอ้างอิง

• [1] Alexander, M., Kusleika, R. and Walkenbach, J. (2019), Excel 2019 Bible, John Wiley & Sons, Indianapolis, IN.
• [2] Data (opens new window)