Phương pháp 1 – Tính xu hướng trung tâm và độ biến thiên
Tập dữ liệu trên cho điểm của học sinh lớp 7 dựa trên các môn Toán, Lịch sử, Khoa học, và Văn học .
1.1 Sử dụng hàm AVERAGE
Bạn thấy Điểm trung bình cho Ethan trong ô C20.
Ở đây, chúng ta đã dùng hàm AVERAGE, trả về trung bình cộng của tập dữ liệu.
- Nhập công thức sau vào ô C20:
Ở đây, phạm vi ô C9:F9 đề cập đến điểm các môn của Ethan.
1.2 Sử dụng hàm AVERAGEIF và AVERAGEIFS
Tập dữ liệu trên minh họa các ứng dụng đa dạng của hàm AVERAGEIF và AVERAGEIFS .
Bạn muốn tìm trung bình điểm của học sinh lớp 6. Để làm điều này,
- Nhập công thức sau vào ô B22:
=AVERAGEIF(B5:B16,B19,D5:D16)
Ở đây, ô B19 chỉ tiêu chí dựa trên đó chúng ta sẽ tìm Điểm trung bình.
Giả sử tình huống khác nơi chúng ta muốn tìm Điểm trung bình của học sinh dựa trên hai tiêu chí: Lớp và Ban.
- Nhập công thức sau vào ô C24:
=AVERAGEIFS($D$5:$D$16,$B$5:$B$16,B19,$C$5:$C$16,C19)
Ở đây, phạm vi ô $D$5:$D$16 chỉ các ô cột Điểm , phạm vi ô $B$5:$B$16 đề cập đến các ô cột Lớp , và phạm vi ô $C$5:$C$16 chỉ các ô cột Ban . Ô B19 và C19 đại diện cho hai tiêu chí.
1.3 Sử dụng hàm HARMEAN và GEOMEAN
Giả sử chúng ta có sáu số làm dữ liệu. Các số là 1,2,3,4,5 và 6. Khi đó giá trị trung bình điều hòa sẽ như sau.
Trung bình điều hòa = 11+1/2+1/3+1/4+1/5+1/66 = 2.4489
Hàm GEOMEAN tính Trung bình hình học của tập dữ liệu được chọn. Trung bình hình học được tính bằng cách tìm căn bậc n sau khi nhân n giá trị của tập dữ liệu. Ở đây, n là tổng số giá trị trong tập dữ liệu. Ví dụ, giả sử chúng ta có 5 số làm tập dữ liệu. Đây là 1, 2, 3, 4, và 5. Vì vậy, Trung bình hình học sẽ là,
Trung bình hình học = 51*2*3*4*5 = 2.6051.
Hình ảnh trên minh họa ví dụ thực tế dùng cả hai hàm HARMEAN và GEOMEAN .
- Công thức tính trung bình điều hòa trong ô B21 là:
Ở đây, phạm vi ô C5:C17 đại diện cho các ô cột Điểm trung bình .
Một điều rõ ràng: giá trị trung bình điều hòa, trong trường hợp này, thấp hơn giá trị trung bình của Điểm trung bình. Trung bình cộng là 82.58, nhưng trung bình điều hòa là 82.37. Điều đó có nghĩa là nó giới hạn giá trị lớn của Điểm trung bình.
- Trong trường hợp tìm trung bình hình học, chúng ta đã dùng công thức sau trong ô C22:
Giống như trung bình điều hòa, trung bình hình học (82.47) khác với trung bình cộng (82.58). Nhà đầu tư dùng trung bình hình học vì nó cung cấp giá trị trung bình chính xác hơn khi các giá trị hàng được cho qua nhiều kỳ.
1.4 Áp dụng hàm STANDARDIZE
Các bước:
- Tính Trung bình và Độ lệch chuẩn của tập dữ liệu.
- Nhập công thức sau vào ô D5:
=STANDARDIZE(C5,$C$20,$C$21)
Ở đây, ô C5 đại diện cho Điểm trung bình của Mathew, ô $C$20 chỉ Giá trị Trung bình, và ô $C$21 đề cập đến giá trị STDEV.P(Độ lệch chuẩn).
Bạn có thể dùng các hàm MODE.SNGL, MEDIAN, VAR.S, VAR.P, STDEV.S, và STDEV.P để phân tích thống kê dữ liệu trong Excel hơn nữa.
Phương pháp 2 – Tính vị trí tương đối
Giả sử cả giá trị thứ 5 và 6 cùng giá trị. Trong trường hợp đó, hàm RANK.EQ sẽ trả về hạng 5 cho cả hai giá trị, và giá trị hạng tiếp theo sẽ là hạng 7. Ở đây, chúng ta có Tổng điểm của Học sinh Lớp 7 làm tập dữ liệu.
Ở đây, bạn có thể thấy giá trị thứ 10 và thứ 11 bị hòa. Vì vậy, hàm RANK.EQ trả về hạng 10 cho cả hai giá trị.
- Chúng ta đã áp dụng công thức sau trong ô D5.
=RANK.EQ(C5,$C$5:$C$17,0)
Ở đây, ô C5 đề cập đến ô đầu tiên của cột Tổng điểm, và phạm vi ô $C$5:$C$17 đại diện cho các ô cột Tổng điểm.
Ở đây, giá trị thứ 10 và 11 bị hòa, nên hàm RANK.AVG trả về trung bình 10.5 cho cả hai giá trị.
Hàm RANK.AVG cũng trả về hạng tương đối của tập dữ liệu. Nhưng, trong trường hợp hòa, nó sẽ trả về hạng trung bình cho các giá trị hòa. Ví dụ, giả sử giá trị thứ 4 và 5 bị hòa. Vì vậy, hàm RANK.AVG sẽ trả về hạng 4.5 cho cả hai giá trị. Hạng của giá trị tiếp theo sẽ là 6. Bây giờ, hãy dùng hướng dẫn dưới để sử dụng hàm RANK.AVG trong Excel để phân tích thống kê dữ liệu.
- Chúng ta đã dùng công thức sau trong ô E5:
=RANK.AVG(C5,$C$5:$C$17,0)
Hơn nữa, bạn cũng có thể dùng các hàm PERCENTRANK.INC, PERCENTRANK.EXC, PERCENTILE.INC, PERCENTILE.EXC, QUARTILE.INC and QUARTILE.EXC functions để tính vị trí tương đối của dữ liệu trong Excel.
Phương pháp 3 – Xác định Tương quan và Hồi quy
3.1 Sử dụng hàm SLOPE, INTERCEPT, và STYEX
Hàm STYEX cho ta sai số chuẩn của Giá trị Y cho Giá trị X đã cho. Chúng ta có thể dùng nó để dự đoán Giá trị Y từ Giá trị X.
Các bước:
- Nhập công thức dưới vào ô C21:
Ở đây, phạm vi ô C5:C17 chỉ các ô cột Giá trị Y, và phạm vi ô B5:B17 đề cập đến các ô cột Giá trị X.
- Nhấn ENTER.
Bạn sẽ có Sai số chuẩn của Giá trị Y cho Giá trị X đã cho trong ô C21.
3.2 Áp dụng hàm CORREL
Hàm CORREL giúp chúng ta tìm ra mức độ hai tập dữ liệu liên quan chặt chẽ.
- Chúng ta đã dùng công thức sau trong ô C19 :
Ở đây, phạm vi C5:C17 chỉ các ô cột Tổng điểm, và phạm vi D5:D17 đề cập đến các ô cột Điểm trung bình.
4. Áp dụng hàm Mảng cho Phân tích thống kê
Ở đây, chúng ta có tần suất đối với mỗi Phạm vi Bin, như được minh họa trong hình sau. Chúng ta đã dùng hàm FREQUENCY, một trong những hàm mảng phổ biến nhất, để phân tích thống kê dữ liệu trong Excel.
- Nhập công thức sau vào ô C17:
=FREQUENCY(C5:C14,B17:B23)
Ở đây, phạm vi B17:B23 đại diện cho bảy ô đầu tiên của cột Phạm vi Bin.
Bạn có thể dùng hàm MODE.MULT function, LINEST function, TREND function, và GROWTH function để phân tích thống kê dữ liệu trong Excel.
Lưu ý: Nếu bạn dùng phiên bản Excel cũ, bạn có thể cần nhấn CTRL + SHIFT + ENTER để dùng công thức mảng. Vì chúng ta dùng Excel 365, chỉ cần nhấn ENTER là đủ.
Phương pháp 5 – Sử dụng Data Analysis ToolPak để tính Trung bình động
Hình trên biểu diễn Trung bình động của tập dữ liệu. Tùy chọn Data Analysis ToolPak không có trong Ribbon Excel mặc định. Bạn cần kích hoạt tính năng này thủ công. Bạn có thể theo dõi bài viết này để kích hoạt Data Analysis ToolPak và tìm hiểu các ứng dụng của nó.
- Đi đến thẻ Dữ liệu từ Ribbon >> chọn Phân tích dữ liệu từ nhóm Phân tích .
Hộp thoại Phân tích dữ liệu sẽ xuất hiện trên bảng tính, như hình trên.
- Đi đến trường Input Range để chọn ô cột Điểm trung bình >> nhấn vào trường Output Range và chọn ô G5 >> nhấn OK.
Một số công cụ Phân tích dữ liệu phổ biến trong Excel
- Anova: Single Factor → Phân tích phương sai cho hai hoặc nhiều quan sát.
- Anova: Two Factor with Replication → Với mỗi tổ hợp cấp độ biến, nó tạo phân tích phương sai với hai biến độc lập và nhiều quan sát.
- Anova: Two Factor Without Replication → Với mỗi tổ hợp cấp độ biến, nó tạo phân tích phương sai với hai biến độc lập và một quan sát đơn lẻ.
- Correlation → Khi có hơn hai phép đo trên mẫu người, ma trận hệ số tương quan cho mỗi cặp phép đo có thể được tính.
- Covariance → Khi có hơn hai phép đo trên mẫu người, ma trận hệ số hiệp phương sai được tính cho mỗi cặp phép đo.
- Descriptive Statistics → Nó tạo báo cáo tóm tắt xu hướng trung tâm, độ biến thiên và các thuộc tính khác của giá trị trong phạm vi ô xác định.
- Exponential Smoothing → Nó dự đoán giá trị tiếp theo của chuỗi, dùng chuỗi giá trị trước và dự đoán trước.
- F-Test Two-Sample for Variances → So sánh hai phương sai bằng F-Test.
- Histogram → Xây dựng biểu diễn bảng tần suất phân phối giá trị trong phạm vi ô chọn.
- Random Number Generation → Tạo số lượng ngẫu nhiên cụ thể dựa trên bảy phân phối tiềm năng.
- Rank and Percentile → Tạo bảng hiển thị mỗi giá trị trong tập giá trị cùng với hạng thứ tự và phân vị.
- Regression → Tạo báo cáo thống kê hồi quy tuyến tính áp dụng cho tập dữ liệu có một biến phụ thuộc và một hoặc nhiều biến độc lập.
- Sampling → Tạo mẫu giá trị từ ô trong phạm vi chỉ định.
Bạn sẽ nhận được các công cụ phân tích sau trong Data Analysis ToolPak.
Những điều cần nhớ
- Trước khi thực hiện phân tích dữ liệu trong Excel, bạn phải rõ ràng về loại dữ liệu, ví dụ: liên tục hoặc phân loại.
- Tiếp theo, bạn phải chọn từ danh sách phong phú công cụ phân tích thống kê, như t-test, ANOVA, hồi quy và tương quan.
- Sau khi thực hiện phân tích, việc diễn giải kết quả có ý nghĩa là quan trọng. Điều này có nghĩa là hiểu những con số có ý nghĩa gì và chúng liên quan như thế nào đến câu hỏi nghiên cứu của bạn.
- Cuối cùng, xác thực kết quả bằng cách kiểm tra lỗi và đảm bảo phân tích vững chắc là quan trọng. Bao gồm kiểm tra giá trị ngoại lai, kiểm tra giả định và thực hiện phân tích nhạy cảm.
Phần thực hành
Trong Sổ làm việc Excel, chúng tôi đã cung cấp Phần Thực hành ở bên phải bảng tính.
Một phần thực hành mẫu được cung cấp trong mỗi bảng tính của Sổ làm việc thực hành.
Tải Sổ làm việc thực hành
Tải sổ làm việc sau và thực hành.
Nhận BÀI TẬP EXCEL NÂNG CAO MIỄN PHÍ kèm Giải pháp!