ถ้าบอกว่า 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
เพื่อรันผลได้เลย
- เลือก Input Range ที่เราสนใจ ในตัวอย่างด้านบนคือคอลัมน์ N (ตัวแปร medv)
- ถ้า row ที่หนึ่งของ dataset เป็นชื่อคอลัมน์ให้เราเลือก Labels in first row
- เลือก 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 5th ออกแล้ว แต่เนื้อหามีปรับนิดหน่อยเอง ถ้าใครอยากประหยัดเงินแนะนำซื้อเล่มสีเขียว edition 4th ก็เพียงพอแล้ว ถูกกว่าหลายร้อยบาทเลย
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 ระดับปริญญาตรี-โท
Leave a Reply