Phân Tích Dữ Liệu Với Excel: Bộ Công Cụ Analysis ToolPak

Trong bài viết này, chúng ta sẽ tìm hiểu về chủ đề “Analysis ToolPak” trong Excel. Bộ công cụ Analysis ToolPak trên Excel sẽ giúp bạn tiết kiệm thời gian và đơn giản hóa các bước phân tích dữ liệu tài chính, thống kê. Bạn sẽ tìm hiểu cách tải và sử dụng bộ công cụ này để phân tích dữ liệu hiệu quả.

Tải Và Cài Đặt Analysis ToolPak

Để tải bộ công cụ Analysis ToolPak, bạn hãy thực hiện các bước sau:

1. Trên tab File, click Options.
2. Trong Add-ins, chọn Analysis ToolPak và click vào nút Go.
   
3. Kiểm tra Analysis ToolPak và click OK.
4. Trên tab Data, trong nhóm Analysis, click Data Analysis.

Tạo Biểu Đồ Histogram Trong Excel

Một ví dụ sẽ hướng dẫn bạn cách tạo biểu đồ Histogram trong Excel.

1. Đầu tiên, bạn nhập các giá trị bin numbers (upper levels) vào phạm vi C4: C8.
2. Trên tab Data, trong nhóm Analysis, click Data Analysis.
3. Chọn Histogram và click OK.
4. Chọn phạm vi A2: A19.
5. Click vào ô Bin Range và chọn phạm vi C4: C8.
6. Click vào nút tùy chọn Output Range, click vào hộp Output Range và chọn ô F3.
7. Kiểm tra Chart Output.
8. Click OK.
   
9. Click vào legend ở phía bên phải và nhấn Delete.
10. Dán nhãn đúng cách cho các bins.
11. Để loại bỏ khoảng cách giữa các bars, hãy click chuột phải vào một bars, click Format Data Series và thay đổi Gap Width thành 0%.
12. Để thêm đường viền, click chuột phải vào một bar, click Format Data Series, click vào Fill & Line icon, click Border và chọn màu.
    Kết quả:
    

Nếu bạn sử dụng Excel 2016 trở lên, bạn chỉ cần sử dụng loại biểu đồ Histogram.
13. Chọn phạm vi A1: A19.
14. Trên tab Insert, trong nhóm Charts, click vào biểu tượng Histogram.
15. Click Histogram.
Kết quả: Một biểu đồ có 3 bins.

Thống Kê Mô Tả Trong Excel

Bạn có thể sử dụng bộ công cụ Analysis ToolPak để tạo thống kê mô tả (descriptive statistics). Ví dụ, bạn có điểm của 14 người tham gia trong một bài kiểm tra. Để tạo thống kê mô tả cho các điểm số này, bạn hãy thực hiện các bước sau:

1. Trên tab Data, trong nhóm Analysis, click Data Analysis.
2. Chọn Descriptive Statistics và click OK.
3. Chọn phạm vi A2: A15 làm Input Range.
4. Chọn ô C1 làm Output Range.
5. Hãy đảm bảo rằng Summary statistics được kiểm tra.
6. Click OK.

Kết quả:

Phân Tích Phương Sai (ANOVA) Trong Excel

Phân tích phương sai (ANOVA) là một công cụ phân tích được sử dụng trong thống kê phân tách biến thiên quan sát tổng hợp được tìm thấy bên trong một tập dữ liệu chia thành hai phần: các yếu tố hệ thống và các yếu tố ngẫu nhiên. Dưới đây, bạn sẽ thấy mức lương của những người có bằng kinh tế, y học và lịch sử.

H0: μ1 = μ2 = μ3
H1: ít nhất một trong các giá trị trung bình khác nhau.

Để thực hiện ANOVA, bạn hãy thực hiện các bước sau:

1. Trên tab Data, trong nhóm Analysis, click Data Analysis.
2. Chọn Anova: Single Factor và click OK.
3. Click vào hộp Input Range và chọn phạm vi A2: C10.
4. Click vào hộp Output Range và chọn ô E1.
5. Click OK.

Kết quả:

Kết luận: nếu F > F crit, bạn sẽ bác bỏ giả thuyết vô hiệu. Trong trường hợp: 15,196 > 3,443, chúng ta sẽ bác bỏ giả thuyết vô hiệu. Tuy nhiên, ANOVA sẽ không cho bạn biết sự khác biệt nằm ở đâu mà bạn cần sử dụng t-Test để kiểm tra.

Kiểm Định F-Test Trong Excel

F-Test được sử dụng để kiểm tra giả thuyết không (null hypothesis) rằng phương sai của hai population là bằng nhau. Chúng ta sẽ có ví dụ về giờ học của 6 sinh viên nữ và 5 sinh viên nam.

H0: σ1^2 = σ2^2
H1: σ1^2 ≠ σ2^2

Để thực hiện F-Test, bạn hãy thực hiện các bước sau:

1. Trên tab Data, trong nhóm Analysis, click Data Analysis.
2. Chọn F-Test Two-Sample for Variances và click OK.
3. Click vào hộp Variable 1 Range và chọn phạm vi A2: A7.
4. Click vào hộp Variable 2 Range và chọn phạm vi B2: B6.
5. Click vào hộp Output Range và chọn ô E1.
6. Click OK.

Kết quả:
Quan trọng: hãy chắc chắn rằng phương sai (variance) của Variable 1 cao hơn phương sai của Variable 2. Trường hợp, 160 > 21,7. Nếu không ra được kết quả như vậy, bạn hãy hoán đổi dữ liệu để ra kết quả tỷ lệ của Variable 1 so với Variable (F = 160 / 21,7 = 7,373).

Kết luận: nếu F > F Critical one-tail, chúng ta sẽ bác bỏ giả thuyết không (null). Trong trường hợp này là 7,373 > 6,256. Vì thế, chúng ta sẽ bác bỏ giả thuyết không (null). Phương sai (variances) của hai population là không bằng nhau.

Kiểm Định t-Test Trong Excel

Kiểm định t-Test được sử dụng để kiểm tra giả thuyết không (null) rằng giá trị trung bình của hai population là bằng nhau. Chúng ta sẽ có ví dụ về giờ học của 6 sinh viên nữ và 5 sinh viên nam.

Để thực hiện t-Test, bạn hãy thực hiện các bước sau:

1. Đầu tiên, bạn hãy thực hiện F-Test để xác định xem phương sai (variances) của hai population có bằng nhau hay không.
2. Trên tab Data, trong nhóm Analysis, click Data Analysis.
3. Chọn t-Test: t: Two-Sample Assuming Unequal Variances và click OK.
4. Click vào hộp Variable 1 Range và chọn phạm vi A2: A7.
5. Click vào hộp Variable 2 Range và chọn phạm vi B2: B6.
6. Click vào ô Hypothesized Mean Difference và nhập 0 (H0: μ1 – μ2 = 0).
7. Click vào hộp Output Range và chọn ô E1.

Kết quả:

Kết luận: Chúng ta sẽ làm một phép thử two-tail (inequality). Nếu t Stat <-t Critical two-tail hoặc t Stat > t Critical two-tail, chúng ta sẽ bác bỏ giả thuyết không (null). Trong trường hợp -2.365 < 1.473 < 2.365. Chúng ta sẽ không bác bỏ giả thuyết không (null). Sự khác biệt quan sát được giữa các sample có nghĩa là (33 – 24,8) không đủ thuyết phục để nói rằng số giờ học trung bình giữa sinh viên nữ và sinh viên nam là khác nhau đáng kể.

Tính Moving Average Trong Excel

Tính moving average của một time series (chuỗi thời gian) trong Excel thể hiện giá trung bình trong một khoảng thời gian.

1. Đầu tiên, chúng ta hãy nhìn vào time series.
   
2. Trên tab Data, trong nhóm Analysis, click Data Analysis.
3. Chọn Moving Average và click OK.
4. Click vào hộp Input Range và chọn phạm vi B2: M2.
5. Click vào hộp Interval và nhập 6.
6. Click vào hộp Output Range và chọn ô B3.
7. Click OK.
8. Vẽ biểu đồ của các giá trị này.
   

Giải thích: bởi vì chúng ta đã đặt khoảng thời gian là 6, moving average là giá trị trung bình của 5 điểm dữ liệu trước đó và điểm dữ liệu hiện tại. Biểu đồ cho thấy xu hướng ngày càng tăng. Excel không thể tính toán moving average cho 5 điểm dữ liệu đầu tiên vì không có đủ các điểm dữ liệu trước đó.

9. Lặp lại các bước từ 2 đến 8 cho interval = 2 và interval = 4.
   

Áp Dụng Exponential Smoothing Trong Excel

Exponential smoothing được sử dụng để làm mịn các điểm bất thường trong một chuỗi thời gian nhằm dễ dàng nhận ra xu hướng.

1. Đầu tiên, chúng ta hãy nhìn vào time series.
   
2. Trên tab Data, trong nhóm Analysis, click Data Analysis.
3. Chọn Exponential Smoothing và click OK.
4. Click vào hộp Input Range và chọn phạm vi B2: M2.
5. Click vào hộp Damping factor và nhập 0,9. (Văn học thường nói về hằng số làm mịn α (alpha). Giá trị (1- α) được gọi là damping factor.)
6. Click vào hộp Output Range và chọn ô B3.
7. Click OK.
8. Vẽ biểu đồ của các giá trị này.
   

Giải thích: Vì chúng ta đặt alpha thành 0,1, điểm dữ liệu trước đó có trọng số tương đối nhỏ trong khi giá trị làm mịn trước đó có trọng số lớn (tức là 0,9). Kết quả là, đỉnh núi và thung lũng được giãn ra. Biểu đồ cho thấy một xu hướng ngày càng tăng. Excel không thể tính toán giá trị smoothed cho điểm dữ liệu đầu tiên vì không có điểm dữ liệu trước đó. Giá trị được smoothed cho điểm dữ liệu thứ hai bằng với điểm dữ liệu trước đó.

9. Lặp lại các bước từ 2 đến 8 cho alpha = 0,3 và alpha = 0,8.
   

Kết luận: Anpha càng nhỏ (damping factor càng lớn) thì các đỉnh núi và thung lũng được giãn ra. Alpha càng lớn (damping factor càng nhỏ), các giá trị smoothed càng gần với điểm dữ liệu thực tế.

Tính Hệ Số Tương Quan Trong Excel

Hệ số tương quan (correlation coefficient) cho bạn biết hai biến có liên quan đến nhau như thế nào. Chúng ta có thể sử dụng hàm CORREL hoặc Analysis Toolpak trong Excel để tìm hệ số tương quan giữa hai biến.

• Hệ số tương quan (correlation coefficient) +1 cho thấy mối tương quan thuận. Khi biến X tăng thì biến Y cũng tăng. Và khi biến X giảm thì biến Y cũng giảm.
  
• Hệ số tương quan (correlation coefficient) -1 cho thấy mối tương quan nghịch. Khi biến X tăng thì biến Z giảm. Và ngược lại, khi biến X giảm thì biến Z tăng.
  
• Hệ số tương quan gần 0 cho thấy không có mối tương quan nào.

Sử dụng Analysis Toolpak trong Excel để tạo hệ số tương quan giữa nhiều biến, bạn hãy thực hiện các bước sau:

1. Trên tab Data, trong nhóm Analysis, click Data Analysis.
2. Chọn Correlation và click OK.
3. Ví dụ: chọn phạm vi A1: C6 làm Input Range.
   
4. Kiểm tra Labels ở hàng đầu tiên.
5. Chọn ô A8 làm Output Range.
6. Click OK.

Kết quả:

Kết luận: biến A và biến C có tương quan thuận (0,91). Còn biến A và biến B không có sự tương quan (0,19). Các biến B và biến C cũng không có sự tương quan (0,11). Bạn có thể xác minh những kết luận này bằng cách nhìn vào biểu đồ.

Phân Tích Hồi Quy Trong Excel

Hướng dẫn bạn về cách chạy phân tích hồi quy tuyến tính trong Excel và cách diễn giải Summary Output.

Câu hỏi lớn đặt ra là liệu có mối quan hệ giữa Số lượng bán ra, giá và quảng cáo. Nói cách khác: chúng ta có thể dự đoán được Số lượng bán ra nếu chúng ta biết giá và quảng cáo không?

1. Trên tab Data, trong nhóm Analysis, click Data Analysis.
2. Chọn Regression và click OK.
3. Chọn Y Range (A1: A8). Đây là biến dự báo (còn gọi là biến phụ thuộc).
4. Chọn X Range (B1: C8). Đây là các biến giải thích (còn gọi là biến độc lập). Các cột này phải liền kề nhau.
5. Kiểm tra Labels.
6. Click vào hộp Output Range và chọn ô A11.
7. Kiểm tra Residuals.
8. Click OK.

Excel tạo ra Summary Output (làm tròn đến 3 chữ số thập phân).

R Square

R Square bằng 0,962 đã rất phù hợp. 96% sự thay đổi về số lượng bán ra được giải thích bởi các biến độc lập là giá và quảng cáo. Càng gần 1, regression line càng phù hợp với dữ liệu.

Significance F và P-values

Để kiểm tra xem kết quả có đáng tin cậy (có ý nghĩa thống kê) hay không, bạn hãy nhìn vào Significance F (0.001). Nếu giá trị này nhỏ hơn 0,05, thì không sao. Nhưng nếu Significance F lớn hơn 0,05, thì nên ngừng tập hợp các independent variables. Xóa một biến có P-value cao (lớn hơn 0,05) và chạy lại regression cho đến khi Significance F giảm xuống còn 0,05.

Hầu hết hoặc tất cả các P-values phải dưới 0,05. Trong ví dụ này là trường hợp (0,000, 0,001 và 0,005).

Coefficients

Regression line là: y = Số lượng bán ra = 8536.214-835.722 Giá + 0.592 Quảng cáo. Nói cách khác, với mỗi unit tăng giá, Số lượng bán ra sẽ giảm đi 835.722 units. Còn đối với mỗi Quảng cáo tăng thì Số lượng bán ra cũng tăng 0,592 units.

Bạn cũng có thể sử dụng các hệ số này để dự báo. Ví dụ: nếu giá bằng 4 đô la và Quảng cáo bằng 3000 đô la, bạn có thể đạt được Số lượng đã bán là 88536.214 -835.722 4 + 0.592 3000 = 6970.

Residuals

Residuals cho biết các điểm dữ liệu thực tế cách các điểm dữ liệu dự đoán bao xa (sử dụng phương trình). Ví dụ: điểm dữ liệu đầu tiên bằng 8500. Sử dụng phương trình, điểm dữ liệu dự đoán bằng 8536.214 -835.722 2 + 0.592 2800 = 8523.009, cho residual là 8500 – 8523.009 = -23.009.

Bạn cũng có thể tạo một scatter plot của những residuals.

Tài Liệu Của Series

Để chuẩn bị cho việc thực hành, bạn có thể tải tài liệu của series bằng cách điền vào form bên dưới. Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab!

Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp, bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây.

Bạn có thể tham khảo bài viết này để thấy trọn bộ series về chủ đề Data Analysis with Excel.