Phương pháp 1 – Tính xu hướng và độ biến thiên trung tâm
Điểm tập dữ liệu trên của học sinh lớp 7 được cho dựa trên Toán , Lịch sử , Khoa học , và Văn học chủ đề.
1.1 Sử dụng hàm TRUNG BÌNH
Bạn thấy Điểm trung bình dành cho Ethan trong ô C20 .
Ở đây, chúng tôi đã sử dụng hàm TRUNG BÌNH , trả về giá trị trung bình số học 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ủa các môn học khác nhau dành cho Ethan .
1.2 Sử dụng hàm AVERAGEIF và AVERAGEIFS
Tập dữ liệu trên thể hiện cách sử dụng đa dạng của AVERAGEIF và TRUNG BÌNH chức năng.
Bạn muốn tìm điểm trung bình cộng của học sinh lớp 6. Để làm được điều này,
- Nhập công thức sau vào ô B22:
=AVERAGEIF(B5:B16,B19,D5:D16)
Ở đây, ô B19 cho biết tiêu chí dựa vào đó chúng tôi sẽ tìm thấy Điểm trung bình .
Hãy giả sử một tình huống khác mà 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à Phần .
- 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 biểu thị các ô của Dấu cột, phạm vi ô $B$5:$B$16 đề cập đến các ô của Lớp cột và phạm vi ô $C$5:$C$16 cho biết các ô của Phần cột. Ô 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 hài hòa của chúng ta sẽ như sau.
Harmonic Mean = 11+1/2+1/3+1/4+1/5+1/66 = 2.4489
GEOMEAN hàm tính toán Trung bình Hình học của tập dữ liệu đã chọn. Giá trị 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 các giá trị của một tập dữ liệu. Đây, n là tổng số giá trị trong một tập dữ liệu. Ví dụ:giả sử chúng ta có 5 số làm tập dữ liệu của chúng tôi. Đây là 1, 2, 3, 4 và 5. Vì vậy, Trung bình Hình học sẽ là,
Geometric Mean = 51*2*3*4*5 = 2.6051.
Hình ảnh trên minh họa một ví dụ thực tế về việc sử dụng cả HARMEAN và GEOMEAN chức năng.
- 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ủa Điểm trung bình cột.
Một điều rõ ràng:trong trường hợp này, giá trị trung bình hài hòa thấp hơn giá trị trung bình của Điểm trung bình. Giá trị trung bình số học 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ị của giá trị lớn Điểm trung bình .
- Trong trường hợp tìm giá trị trung bình hình học, chúng tôi đã sử 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 giá trị trung bình số học (82,58) . Các nhà đầu tư sử dụng giá trị trung bình hình học vì nó cung cấp giá trị trung bình chính xác hơn bất cứ khi nào giá trị hàng được đưa ra trong nhiều khoảng thời gian.
1.4 Áp dụng hàm STANDARDIZE
Các bước :
- Ctính giá trị 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 biểu thị giá trị trung bình , và ô $C$21 đề cập đến STDEV.P(Độ lệch chuẩn) giá trị.
Bạn có thể sử dụng MODE.SNGL , TRUNG BÌNH , VAR.S , VAR.P , STDEV.S , và STDEV.P có chức năng phân tích thống kê sâu hơn dữ liệu trong Excel.
Phương pháp 2 – Tính toán vị thế tương đối
Giả sử cả thứ 5 và thứ 6 -giá trị xếp hạng là như nhau. Trong trường hợp đó, RANK.EQ hàm sẽ trả về thứ hạng 5 cho cả hai giá trị và giá trị xếp hạng tiếp theo sẽ là thứ hạng 7 . Ở đây, chúng ta có Tổng số điểm thứ 7 Học sinh lớp làm tập dữ liệu của chúng tôi.
Ở đây, bạn có thể thấy rằng thứ 10 và thứ 11 các giá trị đã bị ràng buộc. Vì vậy, RANK.EQ hàm trả về thứ hạng 10 cho cả hai giá trị.
- Chúng tôi đã á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 Tổng điểm cột và phạm vi ô $C$5:$C$17 đại diện cho các ô của Tổng điểm cột.
Đây, thứ 10 và giá trị thứ 11 bằng nhau, do đó RANK.AVG hàm trả về trung bình 10,5 cho cả hai giá trị.
RANK.AVG hàm cũng trả về thứ hạng tương đối của tập dữ liệu. Tuy nhiên, trong trường hợp bằng nhau, nó sẽ trả về thứ hạng trung bình đối với các giá trị bị ràng buộc. Ví dụ:giả sử thứ 4 và các giá trị xếp hạng thứ 5 bằng nhau. Vì vậy, RANK.AVG hàm sẽ trả về thứ hạng 4,5 cho cả hai giá trị. Thứ hạng của giá trị tiếp theo sẽ là 6 . Bây giờ, hãy sử dụng hướng dẫn được nêu bên dưới để sử dụng RANK.AVG hàm trong Excel để phân tích thống kê dữ liệu.
- Chúng tôi đã sử 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ể sử dụng PERCENTRANK.INC , PERCENTRANK.EXC , PERCENTILE.INC , PERCENTILE.EXC , Hàm QUARTILE.INC và QUARTILE.EXC để tính toán trạng thái tương đối của dữ liệu trong Excel.
Phương pháp 3 – Xác định mối tương quan và hồi quy
3.1 Sử dụng các hàm SLOPE, INTERCEPT và STYEX
Chức năng STYEX cung cấp cho chúng tôi lỗi tiêu chuẩn về Giá trị Y cho Giá trị X nhất định . Chúng ta có thể sử dụng nó để dự đoán Giá trị Y từ Giá trị X .
Các bước :
- Nhập công thức bên dưới vào ô C21:
Ở đây, phạm vi ô C5:C17 cho biết các ô của Giá trị Y cột và phạm vi ô B5:B17 đề cập đến các ô của Giá trị X cột.
- Nhấn ENTER .
Bạn sẽ gặp Lỗi Tiêu chuẩn của Giá trị Y cho Giá trị X nhất định trong ô C21.
3.2 Áp dụng hàm CORREL
Hàm CORREL giúp chúng tôi tìm ra mức độ liên quan chặt chẽ giữa hai bộ dữ liệu.
- Chúng tôi đã sử dụng công thức sau trong C19 ô:
Ở đây, phạm vi C5:C17 cho biết các ô của Tổng điểm cột và phạm vi D5:D17 đề cập đến các ô của Điểm trung bình cột.
4. Áp dụng hàm mảng để phân tích thống kê
Ở đây, chúng tôi có tần số theo từng Phạm vi thùng, như được minh họa trong hình ảnh sau đây. Chúng tôi đã sử dụng hàm FREQUENCY , một trong những hàm mảng được sử dụ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ác ô của Phạm vi Bin cột.
Bạn có thể sử dụng chức năng MODE.MULT , Hàm LINEST , Hàm XU HƯỚNG và hàm TĂNG TRƯỞNG để phân tích thống kê dữ liệu trong Excel.
Lưu ý: Nếu bạn đang sử dụng phiên bản Excel cũ hơn, bạn có thể cần nhấn CTRL + SHIFT + ENTER để sử dụng công thức mảng. Khi chúng tôi sử dụng Excel 365, chỉ cần nhấn ENTER sẽ làm được cho chúng tôi.
Phương pháp 5 – Sử dụng ToolPak phân tích dữ liệu để tính toán đường trung bình động
Hình ảnh trên thể hiện Trung bình trượt của tập dữ liệu của chúng tôi. Công cụ phân tích dữ liệu tùy chọn không có trong Ribbon của Excel theo mặc định. Bạn sẽ cần phải kích hoạt tính năng này theo cách 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 về các cách sử dụng khác nhau của nó .
- Đi tới Dữ liệu tab từ Ribbon>> chọn Phân tích dữ liệu tùy chọn từ Phân tích nhóm.
Phân tích dữ liệu hộp thoại sẽ xuất hiện trên bảng tính của bạn, như minh họa trong hình trên.
- Đi tới Phạm vi đầu vào để chọn các ô của Điểm trung bình cột>> nhấp vào Phạm vi đầu ra trường và chọn ô G5>> nhấp vào OK .
Một số công cụ phân tích dữ liệu phổ biến trong Excel
- Anova:Yếu tố đơn lẻ → Nó phân tích phương sai của hai hoặc nhiều quan sát.
- Anova:Hai yếu tố với sự nhân rộng → Đối với mỗi sự kết hợp cấp độ của các biến, nó tạo ra một phân tích phương sai với hai biến độc lập và các quan sát khác nhau.
- Anova:Hai yếu tố không cần nhân rộng → Đối với mỗi sự kết hợp mức độ của các biến, nó tạo ra một phân tích phương sai với hai biến độc lập và một quan sát duy nhất.
- Tương quan → Khi có nhiều hơn hai phép đo trên một mẫu người, một ma trận hệ số tương quan cho từng cặp phép đo có thể có sẽ được tính toán.
- Hiệp phương sai → Khi có nhiều hơn hai phép đo trên một mẫu người, một ma trận hệ số hiệp phương sai được tính toán cho từng cặp phép đo có thể có.
- Thống kê mô tả → Nó tạo ra một báo cáo tóm tắt xu hướng trung tâm, tính biến thiên và các thuộc tính khác của các giá trị trong một phạm vi ô xác định.
- Làm mịn hàm mũ → Nó dự đoán giá trị tiếp theo của một chuỗi, sử dụng chuỗi các giá trị trước đó và các dự đoán trước đó.
- Hai mẫu thử nghiệm F cho phương sai → Nó so sánh hai phương sai bằng cách thực hiện F-Test.
- Biểu đồ → Nó xây dựng một mô tả dạng bảng về phân bố tần số của các giá trị trong phạm vi ô đã chọn.
- Tạo số ngẫu nhiên → Dựa trên một trong bảy phân bố tiềm năng, tạo ra một lượng số ngẫu nhiên cụ thể.
- Xếp hạng và Phần trăm → Nó tạo một bảng hiển thị từng giá trị trong một tập hợp các giá trị cùng với thứ tự và thứ hạng phần trăm của nó.
- Hồi quy → Điều này tạo ra một báo cáo thống kê hồi quy tuyến tính được áp dụng cho một tập hợp dữ liệu bao gồm một biến phụ thuộc và một hoặc nhiều biến độc lập.
- Lấy mẫu → Nó tạo ra một mẫu giá trị từ các ô trong phạm vi được 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 bất kỳ phân tích dữ liệu nào trong Excel, bạn phải hiểu rõ về loại dữ liệu của mình, 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ác công cụ phân tích thống kê, chẳng hạn như t-test, ANOVA, hồi quy và tương quan.
- Sau khi bạn tiến hành phân tích, điều quan trọng là phải diễn giải kết quả một cách có ý nghĩa. Điều này có nghĩa là hiểu ý nghĩa của các con số 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, việc xác thực kết quả của bạn bằng cách kiểm tra lỗi và đảm bảo rằng phân tích của bạn chính xác là điều quan trọng. Điều này bao gồm việc kiểm tra các ngoại lệ, kiểm tra các giả định và tiến hành phân tích độ nhạy.
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 trang tính.
Phần thực hành mẫu được cung cấp trong mỗi bài tập của Sách bài tập thực hành.
Tải xuống Sách bài tập thực hành
Tải xuống bài tập sau đây và thực hành.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!