Data Analysis with Excel: Analysis ToolPak

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Analysis ToolPak trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Analysis ToolPak

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ê .

Để 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.

Hộp thoại sau sẽ xuất hiện bên dưới.

5. Ví dụ: chọn Histogram và click OK để tạo Histogram trong Excel.

Histogram in Excel

Ví dụ này sẽ hướng dẫn bạn cách tạo biểu đồ trong Excel.

1. Đầu tiên, bạn nhập bin numbers (upper levels) trong 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 có 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.

Lưu ý: Excel sử dụng quy tắc tham chiếu thông thường của Scott để tính số bins và bin width.

16. Click chuột phải vào horizontal axis, sau đó click Format Axis.

Format Axis pane xuất hiện.

17. Xác định histogram bins. Để xác định, chúng ta sẽ sử dụng các số bin giống như trước đây (xem hình đầu tiên trên trang này).

• Bin width: 5.
• Number of bins: 6.
• Overflow bin: 40.
• Underflow bin: 20.

Kết quả:

Chúng ta đã tạo biểu đồ sau bằng cách sử dụng Analysis ToolPak (bước 1-12).

Kết luận: các bin labels thì trông khác nhau, nhưng biểu đồ thì giống nhau. ≤20 giống với 0-20, (20, 25] giống với 21-25,...

Mẹo: bạn cũng có thể sử dụng pivot tables để dễ dàng tạo frequency distribution trong Excel.

Descriptive Statistics in 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 descriptive statistics cho những đ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ả:

Anova

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ử.

H₀: μ₁ = μ₂ = μ₃

H₁: at least one of the means is different.

Để 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ỏ đây là 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.

F-Test in Excel

Ví dụ này sẽ hướng dẫn bạn cách thực hiện 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 populations 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.

H₀: σ₁² = σ₂²
H₁: σ₁² ≠ σ₂²

Để 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 populations là không bằng nhau.

t-Test in Excel

Ví dụ này hướng dẫn bạn cách thực hiện t-Test trong Excel. 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 populations 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 populations 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 (H₀: μ₁ - μ₂ = 0).

7. Click vào hộp Output Range và chọn ô E1.

8. Click OK.

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ể.

Moving Average in Excel

Ví dụ này sẽ hướng dẫn bạn cách tính moving average of a time series trong Excel. Đường trung bình động đơn giản (moving average ) 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ể 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.

Exponential Smoothing in Excel

Ví dụ này sẽ hướng dẫn bạn cách áp dụng exponential smoothing to a time series trong Excel. Exponential smoothing được sử dụng để làm mịn các điểm bất thường 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ế.

Correlation in Excel

Hệ số tương quan (giá trị từ -1 đến +1) cho bạn biết hai variables 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 để nhanh chóng 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 đồ.

Regression in Excel

Ví dụ này hướng dẫn bạn cách chạy phân tích linear regression 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 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 tiếp) càng phù hợp với dữ liệu.

Significance F and 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 tải tài liệu của series bằng cách điền vào form bên dưới nhé!

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 thì 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 nhé!