วิธีที่ 1 – การคำนวณแนวโน้มกลางและความแปรปรวน
ชุดข้อมูลด้านบนแสดงคะแนนของนักเรียนชั้นมัธยมศึกษาปีที่ 1 วัดจากวิชา คณิตศาสตร์, ประวัติศาสตร์, วิทยาศาสตร์, และ วรรณคดี
1.1 การใช้ฟังก์ชัน AVERAGE
คุณเห็น คะแนนเฉลี่ย สำหรับ Ethan ในเซลล์ C20
ที่นี่เราใช้ ฟังก์ชัน AVERAGE ซึ่งส่งคืนค่าเฉลี่ยเลขคณิตของชุดข้อมูล
- ใส่สูตรต่อไปนี้ใน เซลล์ C20:
ที่นี่ ช่วงเซลล์ C9:F9 หมายถึงคะแนนของวิชาต่างๆ สำหรับ Ethan
1.2 การใช้ฟังก์ชัน AVERAGEIF และ AVERAGEIFS
ชุดข้อมูลด้านบนสาธิตการใช้งานที่หลากหลายของฟังก์ชัน AVERAGEIF และ AVERAGEIFS
คุณต้องการหาคะแนนเฉลี่ยของนักเรียนชั้นประถมศึกษาปีที่ 6 ในการทำเช่นนี้
- ใส่สูตรต่อไปนี้ในเซลล์ B22:
=AVERAGEIF(B5:B16,B19,D5:D16)
ที่นี่ เซลล์ B19 ระบุเกณฑ์ที่เราจะใช้หา คะแนนเฉลี่ย
สมมติอีกกรณีหนึ่งที่เราต้องการหา คะแนนเฉลี่ย ของนักเรียนตาม สองเกณฑ์: ระดับชั้น และ ห้อง
- ใส่สูตรต่อไปนี้ในเซลล์ C24:
=AVERAGEIFS($D$5:$D$16,$B$5:$B$16,B19,$C$5:$C$16,C19)
ที่นี่ ช่วงเซลล์ $D$5:$D$16 หมายถึงเซลล์ของคอลัมน์ คะแนน , ช่วงเซลล์ $B$5:$B$16 อ้างถึงเซลล์ของคอลัมน์ ระดับชั้น , และช่วงเซลล์ $C$5:$C$16 หมายถึงเซลล์ของคอลัมน์ ห้อง เซลล์ B19 และ C19 แทน เกณฑ์สองอย่าง
1.3 การใช้ฟังก์ชัน HARMEAN และ GEOMEAN
สมมติเรามีตัวเลข 6 ตัวเป็นข้อมูลของเรา ตัวเลขคือ 1,2,3,4,5 และ 6 ค่าเฉลี่ยฮาร์มอนิกของเราจะเป็นดังนี้
Harmonic Mean = 11+1/2+1/3+1/4+1/5+1/66 = 2.4489
ฟังก์ชัน GEOMEAN คำนวณ ค่าเฉลี่ยเรขาคณิต (Geometric Mean) ของชุดข้อมูลที่เลือก ค่าเฉลี่ยเรขาคณิตคำนวณโดยการหา รากที่ n หลังจากคูณค่า n ค่าของชุดข้อมูล ที่นี่ n คือจำนวนค่าทั้งหมดในชุดข้อมูล ตัวอย่างเช่น สมมติเรามี 5 ตัวเลขเป็นชุดข้อมูล คือ 1, 2, 3, 4, และ 5 ดังนั้น ค่าเฉลี่ยเรขาคณิต จะเป็น
Geometric Mean = 51*2*3*4*5 = 2.6051.
ภาพด้านบนสาธิตตัวอย่างการใช้งานจริงของฟังก์ชัน HARMEAN และ GEOMEAN ทั้งสอง
- สูตรสำหรับคำนวณค่าเฉลี่ยฮาร์มอนิกในเซลล์ B21 คือ:
ที่นี่ ช่วงเซลล์ C5:C17 แทนเซลล์ของคอลัมน์ คะแนนเฉลี่ย
สิ่งหนึ่งชัดเจน: ค่าของค่าเฉลี่ยฮาร์มอนิกในกรณีนี้ต่ำกว่าค่าเฉลี่ยของคะแนนเฉลี่ย ค่าเฉลี่ยเลขคณิตคือ 82.58 แต่ค่าเฉลี่ยฮาร์มอนิกคือ 82.37 นั่นหมายความว่ามันจำกัดค่าของค่าเฉลี่ยที่สูงของ คะแนนเฉลี่ย
- ในกรณีของการหาค่าเฉลี่ยเรขาคณิต เราใช้สูตรต่อไปนี้ในเซลล์ C22:
เหมือนกับค่าเฉลี่ยฮาร์มอนิก ค่าเฉลี่ยเรขาคณิต (82.47) แตกต่างจากค่าเฉลี่ยเลขคณิต (82.58) นักลงทุนใช้ค่าเฉลี่ยเรขาคณิตเพราะให้ค่าเฉลี่ยที่แม่นยำกว่าเมื่อค่าแถวถูกกำหนดในหลายช่วงเวลา
1.4 การใช้ฟังก์ชัน STANDARDIZE
ขั้นตอน:
- คำนวณค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐาน ของชุดข้อมูล
- ใส่สูตรต่อไปนี้ในเซลล์ D5:
=STANDARDIZE(C5,$C$20,$C$21)
ที่นี่ เซลล์ C5 แทน คะแนนเฉลี่ย ของ Mathew, เซลล์ $C$20 ระบุ ค่าเฉลี่ย, และเซลล์ $C$21 อ้างถึงค่า STDEV.P(ค่าเบี่ยงเบนมาตรฐาน)
คุณสามารถใช้ฟังก์ชัน MODE.SNGL, MEDIAN, VAR.S, VAR.P, STDEV.S, และ STDEV.P เพื่อวิเคราะห์ข้อมูลทางสถิติใน Excel เพิ่มเติม
วิธีที่ 2 – การคำนวณตำแหน่งสัมพัทธ์
สมมติทั้งค่าที่จัดอันดับ 5th และ 6th เหมือนกัน ในกรณีนี้ ฟังก์ชัน RANK.EQ จะส่งคืนอันดับ 5 สำหรับทั้งสองค่า และค่าอันดับถัดไปจะเป็นอันดับ 7 ที่นี่เรามี คะแนนรวม ของนักเรียนชั้นมัธยมศึกษาปีที่ 7 เป็นชุดข้อมูลของเรา
ที่นี่ คุณเห็นว่า อันดับ 10th และ 11th มีค่าเท่ากัน ดังนั้น ฟังก์ชัน RANK.EQ ส่งคืนอันดับ 10 สำหรับทั้งสองค่า
- เราได้ใช้สูตรต่อไปนี้ในเซลล์ D5.
=RANK.EQ(C5,$C$5:$C$17,0)
ที่นี่ เซลล์ C5 อ้างถึงเซลล์แรกของคอลัมน์ คะแนนรวม, และช่วงเซลล์ $C$5:$C$17 แทนเซลล์ของคอลัมน์ คะแนนรวม
ที่นี่ อันดับ 10th และ 11th มีค่าเท่ากัน ดังนั้น ฟังก์ชัน RANK.AVG ส่งคืนค่าเฉลี่ย 10.5 สำหรับทั้งสองค่า
ฟังก์ชัน RANK.AVG ยังส่งคืนอันดับสัมพัทธ์ของชุดข้อมูล แต่ในกรณีที่มีค่าเท่ากัน จะส่งคืน อันดับเฉลี่ย สำหรับค่าที่เท่ากัน ตัวอย่างเช่น สมมติค่าที่จัดอันดับ 4th และ 5th เท่ากัน ฟังก์ชัน RANK.AVG จะส่งคืนอันดับ 4.5 สำหรับทั้งสองค่า อันดับของค่าถัดไปจะเป็น 6 ตอนนี้ ให้ใช้คำแนะนำด้านล่างเพื่อใช้ฟังก์ชัน RANK.AVG ใน Excel เพื่อวิเคราะห์ข้อมูลทางสถิติ
- เราใช้สูตรต่อไปนี้ในเซลล์ E5:
=RANK.AVG(C5,$C$5:$C$17,0)
นอกจากนี้ คุณยังสามารถใช้ฟังก์ชัน PERCENTRANK.INC, PERCENTRANK.EXC, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC และ QUARTILE.EXC functions เพื่อคำนวณตำแหน่งสัมพัทธ์ของข้อมูลใน Excel
วิธีที่ 3 – การกำหนดความสัมพันธ์และการถดถอย
3.1 การใช้ฟังก์ชัน SLOPE, INTERCEPT, และ STYEX
ฟังก์ชัน STYEX ให้เราความผิดพลาดมาตรฐานของ ค่า Y สำหรับ ค่า X ที่กำหนด เราสามารถใช้มันเพื่อทำนาย ค่า Y จาก ค่า X
ขั้นตอน:
- ใส่สูตรด้านล่างในเซลล์ C21:
ที่นี่ ช่วงเซลล์ C5:C17 หมายถึงเซลล์ของคอลัมน์ ค่า Y, และช่วงเซลล์ B5:B17 อ้างถึงเซลล์ของคอลัมน์ ค่า X
- กด ENTER.
คุณจะได้ ความผิดพลาดมาตรฐาน ของ ค่า Y สำหรับ ค่า X ที่กำหนด ในเซลล์ C21.
3.2 การใช้ฟังก์ชัน CORREL
ฟังก์ชัน CORREL ช่วยให้เราหาได้ว่าชุดข้อมูลสองชุดเกี่ยวข้องกันแค่ไหน
- เราใช้สูตรต่อไปนี้ในเซลล์ C19 :
ที่นี่ ช่วง C5:C17 หมายถึงเซลล์ของคอลัมน์ คะแนนรวม, และช่วง D5:D17 อ้างถึงเซลล์ของคอลัมน์ คะแนนเฉลี่ย
4. การใช้ฟังก์ชันอาร์เรย์สำหรับการวิเคราะห์สถิติ
ที่นี่ เรามีความถี่ต่อ ช่วง Bin ตามที่แสดงในภาพต่อไปนี้ เราใช้ฟังก์ชัน FREQUENCY, หนึ่งในฟังก์ชันอาร์เรย์ที่ใช้บ่อยที่สุด เพื่อวิเคราะห์ข้อมูลใน Excel ทางสถิติ
- ใส่สูตรต่อไปนี้ในเซลล์ C17:
=FREQUENCY(C5:C14,B17:B23)
ที่นี่ ช่วง B17:B23 แทนเซลล์ เจ็ด เซลล์แรกของคอลัมน์ ช่วง Bin
คุณสามารถใช้ฟังก์ชัน MODE.MULT function, LINEST function, TREND function, และ GROWTH function เพื่อวิเคราะห์ข้อมูลทางสถิติใน Excel
หมายเหตุ: หากคุณใช้ Excel รุ่นเก่า คุณอาจต้องกด CTRL + SHIFT + ENTER เพื่อใช้สูตรอาร์เรย์ เนื่องจากเราใช้ Excel 365 การกด ENTER เพียงอย่างเดียวก็เพียงพอสำหรับเราแล้ว
วิธีที่ 5 – การใช้ Data Analysis ToolPak เพื่อคำนวณค่าเฉลี่ยเคลื่อนที่
ภาพด้านบนแสดง ค่าเฉลี่ยเคลื่อนที่ (Moving Average) ของชุดข้อมูลของเรา ตัวเลือก Data Analysis ToolPak ไม่อยู่ใน Ribbon ของ Excel โดยค่าเริ่มต้น คุณจะต้องเปิดใช้งานคุณสมบัตินี้ด้วยตนเอง คุณสามารถ ทำตามบทความนี้เพื่อเปิดใช้งาน Data Analysis ToolPak และเรียนรู้การใช้งานต่างๆ ของมัน
- ไปที่แท็บ Data จาก Ribbon >> เลือกตัวเลือก Data Analysis จากกลุ่ม Analysis
กล่องโต้ตอบ Data Analysis จะปรากฏบนแผ่นงานของคุณ ตามที่แสดงในภาพด้านบน
- ไปที่ช่อง Input Range เพื่อเลือกเซลล์ของคอลัมน์ คะแนนเฉลี่ย >> คลิกที่ช่อง Output Range และเลือกเซลล์ G5 >> คลิก OK
เครื่องมือวิเคราะห์ข้อมูลทั่วไปใน Excel
- Anova: Single Factor → วิเคราะห์ความแปรปรวนสำหรับการสังเกต 2 ครั้งขึ้นไป
- Anova: Two Factor with Replication → สำหรับแต่ละการรวมระดับของตัวแปร สร้างการวิเคราะห์ความแปรปรวนด้วยตัวแปรอิสระ 2 ตัวและการสังเกตหลายครั้ง
- Anova: Two Factor Without Replication → สำหรับแต่ละการรวมระดับของตัวแปร สร้างการวิเคราะห์ความแปรปรวนด้วยตัวแปรอิสระ 2 ตัวและการสังเกตครั้งเดียว
- Correlation → เมื่อมีการวัดมากกว่า 2 ครั้งบนตัวอย่างของคน คำนวณเมทริกซ์สัมประสิทธิ์สหสัมพันธ์สำหรับแต่ละคู่ของการวัดที่เป็นไปได้
- Covariance → เมื่อมีการวัดมากกว่า 2 ครั้งบนตัวอย่างของคน คำนวณเมทริกซ์สัมประสิทธิ์แคริแอนซ์สำหรับแต่ละคู่ของการวัดที่เป็นไปได้
- Descriptive Statistics → สร้างรายงานสรุปแนวโน้มกลาง ความแปรปรวน และคุณสมบัติอื่นๆ ของค่าในช่วงเซลล์ที่กำหนด
- Exponential Smoothing → ทำนายค่าถัดไปของลำดับ ใช้ลำดับค่าก่อนหน้าและการทำนายก่อนหน้า
- F-Test Two-Sample for Variances → เปรียบเทียบความแปรปรวนสองชุดโดยทำ F-Test
- Histogram → สร้างการแสดงตารางของการกระจายความถี่ของค่าในช่วงเซลล์ที่เลือก
- Random Number Generation → สร้างจำนวนเฉพาะของตัวเลขสุ่มตามหนึ่งใน 7 การกระจายที่เป็นไปได้
- Rank and Percentile → สร้างตารางแสดงค่าแต่ละค่าในชุดค่า พร้อมอันดับและเปอร์เซ็นไทล์
- Regression → สร้างรายงานสถิติการถดถอยเชิงเส้นที่ประยุกต์ใช้กับชุดข้อมูลที่มีตัวแปรตาม 1 ตัวและตัวแปรอิสระ 1 ตัวขึ้นไป
- Sampling → สร้างตัวอย่างค่าจากเซลล์ในช่วงที่ระบุ
คุณจะได้เครื่องมือวิเคราะห์ต่อไปนี้ใน Data Analysis ToolPak
สิ่งที่ควรจำ
- ก่อนทำการวิเคราะห์ข้อมูลใน Excel คุณต้องชัดเจนเกี่ยวกับประเภทข้อมูลของคุณ เช่น ต่อเนื่องหรือหมวดหมู่
- ต่อไป คุณต้องเลือกจากรายการเครื่องมือวิเคราะห์สถิติที่หลากหลาย เช่น t-test, ANOVA, regression, และ correlation
- เมื่อคุณทำการวิเคราะห์เสร็จสิ้น การตีความผลลัพธ์อย่างมีความหมายเป็นสิ่งสำคัญ หมายถึงการเข้าใจว่าตัวเลขหมายถึงอะไรและเกี่ยวข้องกับคำถามวิจัยของคุณอย่างไร
- สุดท้าย การตรวจสอบผลลัพธ์โดยตรวจสอบข้อผิดพลาดและให้แน่ใจว่าการวิเคราะห์ของคุณแข็งแกร่งเป็นสิ่งสำคัญ รวมถึงการตรวจสอบค่าผิดปกติ การทดสอบสมมติฐาน และการวิเคราะห์ความละเอียดอ่อน
ส่วนฝึกฝน
ใน สมุดงาน Excel เราได้เตรียม ส่วนฝึกฝน ไว้ด้านขวาของแผ่นงาน
ส่วนฝึกฝนตัวอย่างถูกจัดเตรียมไว้ในแต่ละแผ่นงานของสมุดงานฝึกฝน
ดาวน์โหลดสมุดงานฝึกฝน
ดาวน์โหลดสมุดงานต่อไปนี้และฝึกฝน
รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!
