Excel Statistics

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

ถ้าบอกว่า Excel มี add-in ที่ช่วยเราวิเคราะห์ผลสถิติได้ถึง 15 โมเดลแบบไม่ต้องเขียนสูตรแถมใช้งานฟรีด้วย มีอยู่จริงหรอเครื่องมือดีๆแบบนี้? ตอบเลยว่ามี รู้งี้ใช้นานแล้ว !! วันนี้เราจะแนะนำวิธีใช้งาน Analysis Toolpak เบื้องต้น ลองสร้าง correlation matrix และ linear regression ทำนายราคาบ้านง่ายๆ

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

Activate Add-in

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

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

Know Your Dataset

ขั้นตอนแรกก่อนที่เราจะเริ่มทำ data analysis คือการทำความเข้าใจข้อมูลของเราก่อน สิ่งที่เราควรรู้เกี่ยวกับ dataset มี 4 ข้อ

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

ข้อมูลที่เราจะลองทำในตัวอย่างวันนี้ชื่อว่า Boston ที่เราใช้สอนเวลาทำ machine learning เบื้องต้น (regression problem) มีทั้งหมด 14 columns x 506 records โดยตัวแปร target/ dependent ที่เราสนใจคือ medv หรือ median value ราคากลางของบ้านในพื้นที่นั้นๆ ส่วนคอลั่มอื่นๆคือตัวแปร independent

วิธีการเช็คง่ายๆว่าข้อมูลมี missing value หรือเปล่า? ใน Excel สามารถใช้ฟังชั่น sum() คู่กับ isblank() เพื่อนับจำนวน cell ที่ไม่มีข้อมูล ถ้าผลลัพธ์ออกมาเท่ากับศูนย์แปลว่าข้อมูลครบ 100%

=SUM(ISBLANK(A1:N507))

Descriptive Statistics

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

คำนวณค่าสถิติเบื้องต้นของคอลั่ม medv

การใช้งาน Data Analysis Tool มีแค่สามขั้นตอนง่ายๆ ดังนี้

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

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

Correlation

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

สร้างตาราง correlation matrix

ง่ายอะไรเบอร์นี้ รู้งี้ใช้นานแล้ว ! ค่า correlation จะมีค่าวิ่งอยู่ระหว่าง [-1, +1] เครื่องหมายบวกแปลว่าตัวแปรสองตัวเปลี่ยนแปลงในทิศทางเดียวกัน i.e. x เพิ่ม y เพิ่ม ส่วนเครื่องหมายลบคือเปลี่ยนแปลงในทิศทางตรงกันข้ามกัน ตัวแปรที่มีความสัมพันธ์สูงที่สุดกับ medv คือ rm (จำนวนห้อง) มีค่า correlation = 0.6953

Linear Regression

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

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

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

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

ค่า regression coefficients อยู่ในตารางล่างสุด

linear regression ที่เราเพิ่งสร้างขึ้นมามีค่า R Square = 0.5355 ตัวแปร rm, age, dis มีนัยสำคัญที่ระดับ alpha = 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

Key Takeaway

  • Analysis Toolpak เป็นเครื่องมือสำคัญที่ data analyst ควรฝึกใช้ให้คล่อง
  • สามารถรันโมเดลได้ 15 แบบ มีครบทุกตัวพื้นฐาน t-test, one-way ANOVA, correlation และ linear regression ตอบโจทย์สำหรับคนที่ต้องทำ IS/ Thesis ระดับปริญญาตรี-โท

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.