รู้จักกับ Analysis Toolpak เครื่องมือลับสำหรับงานสถิติใน Excel

ถ้าบอกว่า Microsoft Excel มี add-in ที่ช่วยเราวิเคราะห์ผลสถิติได้ถึง 19 โมเดลแบบไม่ต้องเขียนสูตรแถมใช้งานฟรีด้วย มีอยู่จริงหรอเครื่องมือดีๆแบบนี้? ตอบเลยว่ามี รู้งี้ใช้นานแล้ว! 🤩

บทความนี้แอดเขียนแนะนำวิธีใช้งาน Analysis Toolpak เบื้องต้น ลองคำนวณ descriptive statistics สร้าง correlation matrix และ linear regression ทำนายราคาบ้านใน Boston dataset ง่ายๆ

ส่วนตัวแอดใช้ Analysis Toolpak เป็นเครื่องมือหลักเวลาต้องวิเคราะห์ข้อมูลสถิติด้วย Excel อยากเป็น Data Analyst ต้องใช้ Add-in นี้ให้คล่องเลย

สำหรับเพื่อนๆที่อยากทำตาม tutorial สามารถโหลดไฟล์ตัวอย่างได้ในลิ้งนี้นะครับ

Table of Contents

Activate Add-in

วิธีเรียกใช้งาน Analysis Toolpak ให้ไปที่ File > Options แล้วเลือก Add-ins ตามรูปด้านล่าง

เสร็จแล้วจะมีไอคอน Data Analysis โผล่ขึ้นมาที่แท๊บ Data พร้อมให้เราใช้งานทางด้านขวาสุดของหน้าจอ

19 modules สถิติของ Analysis Toolpak

• Anova: Single Factor
• Anova: Two-Factor with Replication
• Anova: Two-Factor without Replication
• Correlation
• Covariance
• Descriptive Statistics
• Exponential Smoothing
• F-Test
• Fourier Analysis
• Histogram
• Moving Average
• Random Number Generation
• Rank and Percentile
• Regression
• Sampling
• t-Test: Paired Two Sample for Means
• t-Test: Two-Sample Assuming Equal Variances
• t-Test: Two-Sample Assuming Unequal Variances
• z-Test: Two Sample for Means

Know Your Dataset

ก่อนที่จะเริ่มวิเคราะห์ข้อมูล เราต้องทำความเข้าใจข้อมูลของเราก่อน EDA – Exploratory Data Analysis สิ่งที่เราควรรู้เกี่ยวกับ dataset มี 4 ข้อ

• dimension จำนวน row x column ของข้อมูล
• ข้อมูลมี missing value หรือเปล่า
• ตัวแปรอะไรเป็น dependent และ independent variables
  • dependent ตัวแปรตาม
  • independent ตัวแปรอิสระหรือตัวแปรต้น
• ค่าสถิติเบื้องต้น (summary statistics) ของคอลัมน์ที่เราสนใจ

ข้อมูลที่แอดใช้ใน tutorial นี้ชื่อ Boston มีทั้งหมด 14 columns x 506 records

โดยตัวแปร target หรือ dependent variable ที่เราสนใจคือ medv (median house values) ราคากลางของบ้านในพื้นที่นั้นๆ ส่วนคอลัมน์อื่นๆคือตัวแปรต้นหรือ independent variable ที่เราสามารถเลือกใช้งานได้

วิธีการเช็คง่ายๆว่าข้อมูลมี missing value หรือเปล่า?

ใน Excel สามารถใช้ฟังชั่น SUM() คู่กับ ISBLANK() เพื่อนับจำนวน cell ที่ไม่มีข้อมูล ถ้าผลลัพธ์ออกมาเท่ากับศูนย์แปลว่าข้อมูลครบ 100% หรือจะใช้ฟังก์ชัน COUNTBLANK() ก็ได้ผลลัพธ์เหมือนกันเลย

=SUM(ISBLANK(A1:N507) * 1)
=COUNTBLANK(A1:N507)

Descriptive Statistics

มาถึงคำถาม EDA ข้อสุดท้าย การหาค่าสถิติเบื้องต้นของคอลัมน์ที่เราสนใจ ถ้าเราใช้ Analysis Toolpak จะช่วยประหยัดเวลาในการเขียน formula เองเยอะมาก คลิกที่ Data > Data Analysis แล้วตั้งค่าตามรูปด้านล่าง

Analysis Toolpak เป็น add-in แบบ drag and drop ไม่ต้องเขียนสูตรอะไรเลย แค่เลือกตัวแปรใส่ในช่องให้ถูกต้องแล้วกด OK เพื่อรันผลได้เลย

1. เลือก Input Range ที่เราสนใจ ในตัวอย่างด้านบนคือคอลัมน์ N (ตัวแปร medv)
2. ถ้า row ที่หนึ่งของ dataset เป็นชื่อคอลัมน์ให้เราเลือก Labels in first row
3. เลือก Output Range ว่าเราอยากเอาผลสถิติไปแปะที่ cell ไหนใน Excel worksheet

Output ที่ได้จาก descriptive statistics มีค่าสถิติสำคัญที่เราใช้บ่อยๆ เช่น mean, median, mode, sd, variance เป็นต้น (ถ้าให้เขียนสูตรเองหมดนี้ใช้เวลาไม่ต่ำกว่า 5-10 นาที)

ตัวอย่าง functions ถ้าเราต้องเขียนเอง แค่คิดก็เหนื่อยแล้ว 555+ 🤣

=AVERAGE()
=STDEV()
=MEDIAN()
=MODE.SNGL()
=MIN()
=MAX()
=MAX()-MIN() // Range
=SUM()
=COUNT()

Correlation

วิธีสร้าง correlation matrix ให้กลับไปที่ Data Analysis แล้วเลือกเมนู Correlation ในช่อง input range ให้เลือกข้อมูลของเราทั้งหมดเลย A1:N507 แล้วเซฟ output ใน worksheet ใหม่ตั้งชื่อว่า COR

เราจะได้ตาราง correlation matrix มาหนึ่งตาราง เสร็จแล้ว ง่ายเหลือเชื่อ 555+

ค่า correlation จะมีค่าวิ่งอยู่ระหว่าง [-1, +1] เครื่องหมายบวกแปลว่าตัวแปรสองตัวเปลี่ยนแปลงในทิศทางเดียวกัน i.e. x เพิ่ม y เพิ่ม ส่วนเครื่องหมายลบคือเปลี่ยนแปลงในทิศทางตรงกันข้ามกัน

ตัวแปรที่มีความสัมพันธ์สูงที่สุดกับ medv คือ rm (จำนวนห้อง) มีค่า correlation = 0.69536

⭐ นักสถิติใช้ correlation matrix ในการวิเคราะห์ความสัมพันธ์ของตัวแปรเชิงปริมาณ (quantitative data) เราสามารถเรียง correlation coefficients ของตัวแปรที่สนใจ หรือใช้ conditional formatting ไฮไลท์สีแบบ heatmap ก็ได้ i.e. คะแนนสูงสีเข้ม คะแนนต่ำสีอ่อน เป็นต้น

Linear Regression

โมเดลสุดท้าย มาลองสร้างโมเดล linear regression กันบ้าง เราจะเลือกตัวแปรต้นสามตัวคือ rm age dis มาใช้ทำนายราคาบ้าน medv หน้าตาของสมการที่เราอยากได้เป็นแบบนี้

medv = f(rm, age, dis)
medv = b0 + b1*rm + b2*age + b3*dis

กลับเข้าไปที่ Data Analysis > Regression เลือก input range ตามรูปด้านล่าง

หน้าตาของ output ที่ได้จากเมนู regression จะเหมือนกับโปรแกรม IBM SPSS ที่ใช้กันเยอะๆในมหาวิทยาลัย จ่ายค่าลิขสิทธิ์กันแพงเลย จริงๆทำใน Excel ก็ได้ ยั๊ง! 555+

Linear regression ที่เราเพิ่งสร้างขึ้นมามีค่า R Square = 0.5355 ตัวแปร rm age dis มีนัยสำคัญที่ระดับ alpha = 0.05 (p-value < 0.05) หน้าตาของ final model เขียนได้แบบนี้

medv = -21.87 + 8.44*rm + (-0.09)*age + (-0.48)*dis
R Square = 0.5355, F = 192.9865, p-value = 0.0000

Note – เราสามารถใช้ function =LINEST() เพื่อหา regression coefficients และค่าสถิติอื่นๆ เช่น R Square, F และ p-value ได้เหมือนกัน แต่การแสดงผลจะไม่สวยเหมือน Analysis Toolpak

Analysis Toolpak vs. SPSS

ถ้าใครเรียนสาย social science, marketing, business & economics หรือเก็บพวกแบบสอบถามมาวิเคราะห์ ตอนอยู่มหาวิทยาลัยน่าจะเคยผ่านโปรแกรม IBM SPSS กันมาบ้าง

SPSS ย่อมาจาก Statistical Package for the Social Sciences เป็นซอฟต์แวร์สำหรับวิเคราะห์ข้อมูลสถิติ ราคาค่อนข้างสูง ถ้าซื้อตัวเต็ม full features ปีละเป็นแสนบาท (commercial use)

ปัจจุบัน users ใช้น้อยลงเยอะเพราะมี open-source software อย่าง R, Python หรือแม้แต่ Excel, Google Sheets ที่ใช้แทนกันได้ ปกติ IBM จะออกเวอร์ชันใหม่ทุกปี แต่นี้จะสองปีแล้วยังค้างที่ version 28 อยู่ 555+

Analysis Toolpak ที่เราสอนในบทความนี้ทำหลายๆอย่างได้เหมือน SPSS เลย ถ้าใครต้องทำงานวิจัย ป.ตรี/ โทในมหาวิทยาลัย แอดว่าใช้ Analysis Toolpak ให้คล่องๆก็เพียงพอให้เรียนจบได้สบายๆแล้วครับ 😆

Good Book

หนังสือ Statistical Analysis with Excel ของ Wiley มีสอนใช้ Analysis Toolpak ด้วยเผื่อใครอยากศึกษาต่อนะครับ แอดว่า for dummies series นี้อ่านง่ายดี เหมาะสำหรับผู้เริ่มต้น

Note – จริงๆมี edition 5^th ออกแล้ว แต่เนื้อหามีปรับนิดหน่อยเอง ถ้าใครอยากประหยัดเงินแนะนำซื้อเล่มสีเขียว edition 4^th ก็เพียงพอแล้ว ถูกกว่าหลายร้อยบาทเลย

Key Takeaway

• ก่อนเริ่มวิเคราะห์ข้อมูล ควรทำ EDA เพื่อเข้าใจโครงสร้างข้อมูลเบื้องต้น
• Analysis Toolpak เป็นเครื่องมือสำคัญที่ Data Analyst ควรฝึกใช้ให้คล่อง
  • Descriptive Statistics
  • Correlation
  • Linear Regression
• การใช้งานเป็นแบบ drag and drop ไม่ต้องเขียนสูตรให้ยุ่งยาก
• สามารถรันโมเดลได้ 19 แบบ มีครบทุกตัวพื้นฐาน t-test, one-way ANOVA, correlation และ linear regression ตอบโจทย์สำหรับคนที่ต้องทำ IS/ Thesis ระดับปริญญาตรี-โท