Phân tích dữ liệu với PivotTable trong Excel
Nguyễn Văn Chức – chucnv@ud.edu.vn
Thống kê là nền tảng của khai phá dữ liệu. Vì vậy, hiểu và triển khai được một dự án phân tích dữ liệu thống kê là rất cần thiết và sẽ giúp ích rất nhiều trong quá trình xây dựng các mô hình khai phá dữ liệu phức tạp. Bài viết này nhằm giúp các bạn từng bước triển khai phân tích dữ liệu có được thông qua điều tra bằng bảng câu hỏi (Questionnaires). Bạn không cần phải là chuyên gia về phân tích dữ liệu hay thống kê. Các khái niệm về thống kê và qui trình thực hiện một nghiên cứu thống kê đơn giản như thu thập dữ liệu, mã hóa dữ liệu, phân tích dữ liệu được mô tả từng bước một cách chi tiết bằng MS Excel.
Bài viết trình bày các bước của một nghiên cứu phân tích dữ liệu từ xây dựng dữ liệu mẫu và câu hỏi nghiên cứu, mã hóa dữ liệu, các phân tích đơn giản với thống kê mô tả (Descriptive Statistic) đến các phân tích phức tạp hơn dựa vào Cross tabulation (Pivot Table), kiểm định giả thuyết (Chi-square test).
1. Dữ liệu mẫu và câu hỏi nghiên cứu (Data Example & Research Questions)
Để dễ hình dung, chúng ta bắt đầu với bảng câu hỏi đơn giản gồm 6 câu hỏi liên quan đến khách đến giải trí tại công viên ABC nhằm phân tích, đánh giá mức độ hài lòng của khách với các dịch vụ của công viên ABC. Sau đây là ý nghĩa của các biến sử dụng trong nghiên cứu:
· Family (Gia đình): Đáp viên, đối tượng trả lời câu hỏi, khách.
· Time(thời gian): Thời gian hoạt động của gia đình trong công viên. Tính bằng phút và có kiểu dữ liệu định lượng (quantitative)
· Mode: Loại hình phương tiện mà khách (gia đình) lựa chọn đi đến công viên. Có kiểu dữ liệu định danh (nominal) gồm các giá trị như sau: (1) walk, (2) car, (3) bicycle, (4) bus.
Chú ý: Mỗi gia đình chỉ chọn 1 trong 4 giá trị của Mode
· Activity (hoạt động): Chỉ các hoạt động của gia đình tại công viên, có kiểu dữ liệu định danh và gồm 6 hoạt động như sau: (1) sport, (2) picnic, (3) reading, (4) walk , (5) meditation, (6) jog.
Mỗi gia đình có thể chọn nhiều hoạt động
Satisfaction (sự hài lòng): Đo lường sự hài lòng của khách đến giải trí tại công viên đối với các dịch vụ của công viên, có kiểu dữ liệu thứ bậc (ordinal) gồm 5 mức như sau::
-2 = Rất không hài lòng (Very dissatisfied),
- 1 = Không hài lòng (dissatisfied),
0 = Không quan tâm (indifference),
1 = Hài lòng (satisfied),
2 = Rất hài lòng (Very satisfied).
· Playground (sân chơi trẻ con): Có kiểu dữ liệu định danh, có hai giá trị là Yes và No cho biết công viên có sân chơi cho trẻ con hay không.
Giả sử rằng các câu hỏi điều tra được hỏi với 12 gia đình đã đến công viên giải trí và dữ liệu ban đầu (raw data) thu được như sau:
Với những dữ liệu thu được như trên, chúng ta muốn nghiên cứu để làm tăng mức độ hài lòng của khách đến công viên. Câu hỏi nghiên cứu cần trả lời là “Làm sao nâng cao mức độ hài lòng của khách đến vui chơi tại công ty ABC?”
Để trả lời câu hỏi trên, ta phải trả lời các câu hỏi nhỏ sau:
1. Mức độ hài lòng của khách tới vui chơi tại công viên ABC là bao nhiêu?
2. Hoạt động nào được lựa chọn nhiều nhất trong công viên?
3. Khách thường đến công viên bằng phương tiện nào ?
4. Nếu có sân chơi cho trẻ em ở công viên thì liệu mức độ hài lòng của khách có tăng lên không?
5. Có mối quan hệ (tương quan- Correlation) giữa hoạt động (activity) và thời gian hoạt động của khách không?
6. Có mối quan hệ (tương quan) giữa thời gian mà khách lưu lại công viên với phương tiện (mode) mà họ sử dụng để đến công viên không?
Làm sao bạn sẽ trả lời những câu hỏi nghiên cứu này? Các bước sau đây sẽ giúp bạn trả lời các câu hỏi trên. Giả sử rằng dữ liệu thu được ở trên là các mẫu hợp lệ (dù số mẫu rất ít và chỉ dùng để minh họa).
2. Mã hóa dữ liệu từ bảng câu hỏi (Data Coding from Questionnaires)
Chúng ta đã có dữ liệu điều tra từ bảng câu hỏi và chúng ta muốn phân tích dữ liệu đó. Bước đầu tiên của phân tích dữ liệu là mã hóa dữ liệu (data coding). Mã hóa dữ liệu là quá trình chuyển đổi dữ liệu có được từ bảng câu hỏi sang định dạng phù hợp để phân tích (và có thể thực hiện tính toán trên máy tính).
Dữ liệu thu được gồm 6 biến (variable) và chỉ có 2 biến định danh (nominal) là Activity và Playground là cần chuyển đổi và 4 biến còn lại đã có kiểu dữ liệu phù hợp (kiểu số).
Biến Activities được chuyển qua kiểu nhị phân chỉ nhận 2 giá trị 1 và 0. (1 có nghĩa là hoạt động đó được chọn và 0 có nghĩa là không chọn hoạt động đó). Ta có 6 hoạt động nên ta chuyển thành 6 cột (mỗi cột tương ứng với 1 hoạt động).
Biến Playground cũng được chuyển sang dạng nhị phân (1 = yes : có sân chơi, và 0 = no: không có sân chơi)
Kết quả của bước mã hóa dữ liệu ta thu được dữ liệu đã mã hóa như sau:
3. Phân tích đơn giản với thống kê mô tả (Descriptive Statistics)
Trong phần này, chúng ta tiến hành phân tích đơn giản đầu tiên với dữ liệu có được sau khi mã hóa bằng cách sử dụng MS Excel để tính toán các thống kê mô tả trên dữ liệu đã mã hóa.
Để thực hiện tính các thống kê mô tả trong Excel ta làm như sau:
· Click menu Tools - Data Analysis – Descriptive Statistics.
· Thiết lập các tùy chọn cho hộp thoại Descriptive Statistics như hình sau và bấm OK
Kết quả của Descriptive Statistics sau một số thao tác định dạng cho dễ nhìn (bỏ đi các cột tham số giống nhau trong bảng) như sau:
Thống kê mô tả tính toán rất nhiều tham số như bảng trên và giúp ta phân tích đơn giản ban đầu về dữ liệu. Không nên lo lắng nếu không hiểu tất cả các tham số được tính từ thống kê mô tả nên, chỉ nên tập trung vào các câu hỏi nghiên cứu đã đặt ra từ đầu. (đó là lý do vì sao phải đặt ra các câu hỏi nghiên cứu trước khi tiến hành phân tích dữ liệu).
Từ dữ liệu bảng trên, ta thấy rằng mức độ hài lòng (Satisfaction) trung bình là 1 (thang đo mức độ hài lòng có 5 mức độ từ -2 đến 2). Ta có thể kết luận rằng khách khá hài lòng với điều kiện của công viên ABC. Như vậy câu hỏi nghiên cứu 1 về mức độ hài lòng của khách với công viên ABC đã được trả lời.
Cũng từ kết quả tính toán trên, ta có được tần suất sử dụng các hoạt động tại công viên ABC như sau:
Dựa vào dữ liệu về tần suất các hoạt động trên, ta có thể kết luận phần lớn gia đình tới công viên để hoạt động Picnic (27.3%) hoặc đi bộ (Walk) (22.7%). Phân tích này đã trả lời câu hỏi nghiên cứu 2 : “Hoạt động nào được lựa chọn nhiều nhất trong công viên?”
Từ số Mode của biến Mode (phương tiện) ta có thể kết luận phần lớn khách đến công viên bằng xe hơi (Mode = 2, car). Phân tích này đã trả lời câu hỏi nghiên cứu thứ 3: “Khách đến công viên bằng phương tiện nào?”
4. Phân tích dựa vào bảng chéo (Cross Tabulation)
Trong phần trên, chúng ta đã thực hiện các phân tích đơn giản ban đầu với dữ liệu bằng thống kê mô tả và tần suất từ dữ liệu. Phần này tìm hiểu làm thế nào để phân tích mối quan hệ giữa các biến trong dữ liệu điều tra từ bảng hỏi bằng cách sử dụng bảng chéo (Cross Tabulation) còn gọi là Pivot Table hay contingency table.
Sử dụng dữ liệu đã mã hóa, chúng ta muốn trả lời câu hỏi nghiên cứu tiếp theo:
- Nếu có sân chơi cho trẻ em ở công viên thì liệu mức độ hài lòng của khách có tăng lên không?
- Có mối quan hệ (tương quan) giữa hoạt động (activity) tại công viên mà khách chọn và thời gian (time) mà các gia đình lưu lại công viên không?
- Có mối quan hệ (tương quan) giữa thời gian mà khách lưu lại công viên với phương tiện (mode) mà họ sử dụng để đến công viên không?
Bởi vì các câu hỏi nghiên cứu này về mối quan hệ giữa 2 biến, chúng ta cần sử dụng kỹ thuật phân tích dựa trên Cross Tabulation để trả lời các câu hỏi này. Cross tabulation là bảng tần suất giữa hai hay nhiều biến. Nó có rất nhiều tên gọi khác nhau, các nhà thống kê gọi là Contigency Table trong khi MS Excel gọi là Pivot Table.
Để tạo Pivot Table trong Excel ta tiến hành như sau:
Menu Data – Pivot Table and Pivot Chart Report .
Chọn Next và chọn vùng dữ liệu, xem hình sau:
Bấm Next và chọn Layout button
Để trả lời mối quan hệ (relationship) giữa 2 biến Playground và Satisfaction, kéo và thả tên các biến tương ứng ở bên phải vào sơ đồ. Đặt biến Satisfaction trong hàng (row) và biến Playground trong cột (column) và kéo thả biến Satisfaction một lần nữa vào vùng Data .Nó sẽ xuất hiện Sum of Satisfaction. Sau đó, double click vào nút cuối cùng (Sum of Satisfaction ) và Pivot Table Field dialog xuất hiện. Trong mục summarized by chọn Count và click OK 2 lần.
Khi quay trở lại bước 3 của Pivot table wizard, Click Finish button.
MS excel sẽ tự động tạo ra Cross Tabulation table như sau:
Để thuận lợi cho việc phân tích, bạn có thể copy dữ liệu từ Pivot table ra và dán vào vùng khác (chú ý là sử dụng Edit - Paste Special, chọn value)
Sau khi copy và định dạng lại dữ liệu từ Pivot table, ta tính giá trị kỳ vọng (expected value) theo công thức sau (Giá trị kỳ vọng còn được gọi là giá trị độc lập vì được tính với giá thuyết H0 đúng). Để biết liệu biến Playground có quan hệ với biến Satisfaction hay không, ta thực hiện kiểm định đơn giản gọi là Chi-square test. Chi-square test cho phép kiểm tra tính độc lập của 2 biến trong Contingency table.
Công thức tính giá trị kỳ vọng
Trong đó Oij là giá trị quan sát (Observe value) tại dòng i cột j
Ý nghĩa của công thức trên là để có được giá trị độc lập tại dòng i cột j, ta nhân tổng của dòng i với tổng của cột j và chia cho tổng của tất cả dữ liệu trong bảng.
Giá trị trong bảng trên đây được lấy tổng hợp từ mẫu nên gọi là giá trị quan sát (Observe value)
Ví dụ, giá trị độc lập của ô tại dòng 3 cột 2, ta có Satisfaction = 1 và Playground = 1, ta có 2 trả lời (respondent). Ta có tổng dòng là 4 và tổng cột là 7. Tổng toàn bộ dữ liệu là 12 cho nên giá trị độc lập cho ô (cell) này là 4*7/12 = 2.333.
Thực hiện tương tự ta có giá trị độc lập cho tất cả các ô còn lại của bảng như sau:
Giá trị kỳ vọng là giá trị tính được với giả thuyết H0 đúng. (ở đây giả thuyết H0 là : Giữa Playground và Satisfaction không có mối quan hệ)
Bảng này có nghĩa là nếu biến Playground hoàn toàn (100%) độc lập với Satisfaction thì giá trị quan sát trong Pivot Table phải bằng với với những giá trị kỳ vọng trong bảng này.
Kiểm định Chi-square (Chi-squre Test): Kiểm định Chi-square dùng để kiểm định sự độc lập của 2 biến phân loại ngẫu nhiên. Nếu xác suất nhỏ hơn 0.05 (5%, mức ý nghĩa) thì có thể kết luận 2 biến có mối quan hệ. Ngược lại, không có cơ sở để kết luận giữa 2 biến có mối quan hệ.
Xác suất đó có thể được tính trong Excel bằng hàm kiểm định Chi-square như sau
Trong đó :
: Giá trị Chi-square (Chi-square value)
df: Bậc tự do (degree of freedom)
Để tính xác suất này, ta phải tính bình phương độ lệch giữa giá trị quan sát (observe value) từ Pivot Table và giá trị kỳ vọng (expected value) từ Independent table và chia cho giá trị kỳ vọng rồi tính tổng tất cả các độ lệch này trong bảng. Giá trị này gọi là giá trị Chi-square. Công thức tính như sau:
Bậc tự do (df – degree of freedom) được tính là tổng số hàng trừ 1 nhân với tổng số cột trừ 1
df = (total rows- 1)*(total columns -1)
Từ dữ liệu trong ví dụ trên ta tính tổng bình phương các độ lệch bằng 7.886 và bậc tự do df = (4-1)*(2-1)=3. Sử dụng hàm CHIDIST (7.886, 3) ta có xác suất p=0.048 (xem bảng dữ liệu sau)
Vì xác suất p=0.048 nhỏ hơn 0.05 (mức ý nghĩa 5%) nên ta có thể kết luận rằng tồn tại mối quan hệ giữa playground và Satisfaction level (nói theo lý thuyết kiểm định giả thuyết là bác bỏ H0). Phân tích này cho phép trả lời câu hỏi nghiên cứu 4 ở trên. Có nghĩa là nếu công viên có sân chơi cho trẻ em thì sẽ làm tăng mức độ hài lòng của khách.
Từ Chi-square value và bậc tự do (degree of freedom ), sử dụng hàm CHIDIST(Chi-square value, degree of freedom) để tính xác suất p và so sánh p với mức ý nghĩa α (thường α=5%) ta có thể kết luận có tồn tại mối quan hệ giữa các biến hay không?. Ngược lại nếu biết xác suất p và bậc tự do ta có thể sử dụng hàm CHIINV(probability, degree of freedom) để tìm được Chi-square value.
Ta có thể sử dụng Chi-square value tính được bằng hàm CHIINV với xác suất p=0.05 so sánh với Chi-square value tính được từ bảng dữ liệu. Nếu giá trị Chi-square tính được từ hàm CHINV nhỏ hơn Chi-square value tính được từ bảng thì ta có thể kết luận giữa các biến có mối quan hệ. Chẳn hạn trong ví dụ trên, sử dụng hàm =CHIINV(0.05,3) ta sẽ có được Chi-square value là 7.815 nhỏ hơn Chi-square value tính được từ bảng là 7.886 nên ta kết luận giữa Playground và Satisfaction có mối quan hệ.
(Nếu bạn chưa quen với kiểm định Chi-square thì xem thêm về Chi-square test tại đây)
Để trả lời câu hỏi nghiên cứu 5 : Có mối quan hệ (tương quan) giữa hoạt động (activity) mà khách chọn và thời gian hoạt động (time) của khách không? Ta làm tương tự các bước như trên để tạo ra Pivot table của Activity và Time như sau:
Kéo thả biến Time button lên column và kéo thả tất cả các Activity (từ 1 đến 6) vào vùng Data. Chọn hàm sum vì activity có kiểu dữ liệu binary (1 và 0) như sau:
Thực hiện tính toán giống như phần trả lời câu hỏi 4 ta được kết quả phân tích như sau:
Chi-square test
Vì xác xuất p=0.462 lớn hơn 0.05 nên ta kết luận không có mối quan hệ giữa hoạt động và thời gian hoạt động của khách.
Nếu ta sử dụng hàm CHIINV(0.05, 25) ta tính được Chi-square value =37.652 >25.012 nên kết luận tương tự là không có mối quan hệ giữa hoạt động và thời gian hoạt động của khách.
Đối với câu hỏi nghiên cứu cuối cùng, câu hỏi số 6: Có mối quan hệ (tương quan) giữa thời gian mà khách lưu lại công viên với phương tiện (mode) mà họ sử dụng để đến công viên không?
Ta xây dựng bảng Pivot table cho 2 biện Time và Mode như sau: Kéo thả Time vào Column và Mode và Row. Kéo thả Mode một lần nữa vào vùng Data và chọn hàm Count như sau:
Kết quả phân tích tiến hành tương tự như trên ta có:
Chi- square Test
Vì p=0.06 >0.05 nên ta kết luận không có mối quan hệ giữa phương tiện mà khách sử dụng để đi đến công viên với thời gian hoạt động của khách tại công viên.