Phân tích tương quan trong Excel. Một ví dụ về thực hiện phân tích tương quan

Phân tích tương quan là một phương pháp nghiên cứu phổ biến được sử dụng để xác định mức độ phụ thuộc của giá trị thứ nhất vào giá trị thứ hai. Bảng tính có một công cụ đặc biệt cho phép bạn thực hiện loại nghiên cứu này.

Bản chất của phân tích tương quan

Cần xác định mối quan hệ giữa hai đại lượng khác nhau. Nói cách khác, nó tiết lộ giá trị thay đổi theo hướng nào (nhỏ hơn / lớn hơn) tùy thuộc vào những thay đổi trong giây.

Mục đích của phân tích tương quan

Sự phụ thuộc được thiết lập khi việc xác định hệ số tương quan bắt đầu. Phương pháp này khác với phân tích hồi quy, vì chỉ có một chỉ số được tính bằng cách sử dụng tương quan. Khoảng thay đổi từ +1 thành -1. Nếu nó là số dương, thì sự gia tăng của giá trị đầu tiên góp phần vào sự gia tăng của giá trị thứ hai. Nếu âm, thì giá trị thứ nhất tăng lên sẽ làm giảm giá trị thứ hai. Hệ số càng cao, một giá trị ảnh hưởng đến giá trị thứ hai càng mạnh.

Quan trọng! Ở hệ số 0, không có mối quan hệ nào giữa các đại lượng.

Tính toán hệ số tương quan

Hãy phân tích phép tính trên một số mẫu. Ví dụ: có dữ liệu dạng bảng, trong đó chi tiêu cho khuyến mại quảng cáo và khối lượng bán hàng được mô tả theo tháng trong các cột riêng biệt. Dựa vào bảng, chúng ta sẽ tìm ra mức độ phụ thuộc của sản lượng tiêu thụ vào số tiền chi cho hoạt động xúc tiến quảng cáo.

Phương pháp 1: Xác định mối tương quan thông qua trình hướng dẫn hàm

CORREL - một chức năng cho phép bạn thực hiện phân tích tương quan. Hình thức chung - CORREL (massiv1; massiv2). Hướng dẫn chi tiết:

1. Cần phải chọn ô mà nó được lên kế hoạch để hiển thị kết quả của phép tính. Nhấp vào “Chèn Hàm” ở bên trái trường văn bản để nhập công thức.

2. Trình hướng dẫn chức năng sẽ mở ra. Ở đây bạn cần tìm ĐÚNG, nhấp vào nó, sau đó nhấp vào “OK”.

3. Cửa sổ đối số sẽ mở ra. Trong dòng “Array1”, bạn phải nhập tọa độ của các khoảng của giá trị thứ nhất. Trong ví dụ này, đây là cột Giá trị Bán hàng. Bạn chỉ cần chọn tất cả các ô có trong cột này. Tương tự, bạn cần thêm tọa độ của cột thứ hai vào dòng “Array1”. Trong ví dụ của chúng tôi, đây là cột Chi phí Quảng cáo.

4. Sau khi nhập tất cả các phạm vi, hãy nhấp vào nút “OK”.

Hệ số được hiển thị trong ô được chỉ ra khi bắt đầu hành động của chúng tôi. Kết quả thu được là 0,97. Chỉ số này phản ánh sự phụ thuộc nhiều của giá trị thứ nhất vào giá trị thứ hai.

Phương pháp 2: Tính toán tương quan bằng cách sử dụng công cụ phân tích

Có một phương pháp khác để xác định mối tương quan. Ở đây, một trong các hàm được tìm thấy trong gói phân tích được sử dụng. Trước khi sử dụng, bạn cần kích hoạt công cụ. Hướng dẫn chi tiết:

1. Chuyển đến phần "Tệp".

2. Một cửa sổ mới sẽ mở ra, trong đó bạn cần nhấp vào phần “Cài đặt”.
3. Nhấp vào “Tiện ích bổ sung”.
4. Chúng tôi tìm thấy yếu tố "Quản lý" ở dưới cùng. Ở đây bạn cần chọn “Excel Add-in” từ menu ngữ cảnh và nhấp vào “OK”.

5. Một cửa sổ tiện ích bổ sung đặc biệt đã mở ra. Đặt dấu kiểm bên cạnh phần tử "Gói phân tích". Chúng tôi nhấp vào “OK”.
6. Kích hoạt thành công. Bây giờ chúng ta hãy đi đến Dữ liệu. Khối "Phân tích" đã xuất hiện, trong đó bạn cần nhấp vào "Phân tích dữ liệu".
7. Trong cửa sổ mới xuất hiện, chọn phần tử “Tương quan” và nhấp vào “OK”.

8. Cửa sổ cài đặt phân tích xuất hiện trên màn hình. Trong dòng “Khoảng đầu vào”, cần nhập phạm vi của tất cả các cột tham gia phân tích. Trong ví dụ này, đây là các cột "Giá trị bán hàng" và "Chi phí quảng cáo". Cài đặt hiển thị đầu ra ban đầu được đặt thành Trang tính mới, có nghĩa là kết quả sẽ được hiển thị trên một trang tính khác. Theo tùy chọn, bạn có thể thay đổi vị trí đầu ra của kết quả. Sau khi thực hiện tất cả các cài đặt, hãy nhấp vào “OK”.

Điểm số cuối cùng đã ra. Kết quả giống như trong phương pháp đầu tiên - 0,97.

Định nghĩa và tính toán hệ số tương quan bội trong MS Excel

Để xác định mức độ phụ thuộc của một số đại lượng, nhiều hệ số được sử dụng. Trong tương lai, các kết quả được tóm tắt trong một bảng riêng biệt, được gọi là ma trận tương quan.

Hướng dẫn chi tiết:

1. Trong phần "Dữ liệu", chúng tôi tìm thấy khối "Phân tích" đã biết và nhấp vào "Phân tích dữ liệu".

2. Trong cửa sổ xuất hiện, nhấp vào phần tử “Tương quan” và nhấp vào “OK”.
3. Trong dòng “Khoảng thời gian đầu vào”, chúng tôi lái xe trong khoảng thời gian cho ba hoặc nhiều cột của bảng nguồn. Phạm vi có thể được nhập theo cách thủ công hoặc chỉ cần chọn nó bằng LMB, và nó sẽ tự động xuất hiện ở dòng mong muốn. Trong “Phân nhóm”, hãy chọn phương pháp phân nhóm thích hợp. Trong “Tham số đầu ra” chỉ định vị trí nơi kết quả tương quan sẽ được hiển thị. Chúng tôi nhấp vào “OK”.

4. Sẳn sàng! Ma trận tương quan được xây dựng.

Hệ số tương quan cặp trong Excel

Hãy tìm cách vẽ chính xác hệ số tương quan cặp trong bảng tính Excel.

Tính toán hệ số tương quan cặp trong Excel

Ví dụ, bạn có các giá trị x và y.

X là biến phụ thuộc và y là biến độc lập. Cần phải tìm ra hướng và độ mạnh của mối quan hệ giữa các chỉ số này. Hướng dẫn từng bước:

1. Hãy tìm các giá trị trung bình bằng cách sử dụng hàm TIM.

2. Hãy tính toán từng х и xavg, у и trung bình bằng cách sử dụng toán tử «-».

3. Chúng tôi nhân sự khác biệt được tính toán.

4. Chúng tôi tính tổng các chỉ số trong cột này. Tử số là kết quả được tìm thấy.

5. Tính các mẫu số của sự khác biệt х и x-trung bình, y и y-trung bình. Để làm điều này, chúng tôi sẽ thực hiện bình phương.

6. Sử dụng chức năng TỰ ĐỘNG TÓM TẮT, tìm các chỉ số trong các cột kết quả. Chúng tôi thực hiện phép nhân. Sử dụng chức năng ROOT bình phương kết quả.

7. Chúng tôi tính toán thương số bằng cách sử dụng các giá trị của mẫu số và tử số.

8. CORREL là một chức năng tích hợp cho phép bạn ngăn chặn các phép tính phức tạp. Chúng tôi đi đến “Trình hướng dẫn chức năng”, chọn CORREL và chỉ định các mảng chỉ số х и у. Chúng tôi xây dựng một biểu đồ hiển thị các giá trị thu được.

Ma trận hệ số tương quan theo cặp trong Excel

Hãy phân tích cách tính hệ số của các ma trận ghép đôi. Ví dụ, có một ma trận gồm bốn biến.

Hướng dẫn từng bước:

1. Chúng tôi đi đến "Phân tích dữ liệu", nằm trong khối "Phân tích" của tab "Dữ liệu". Chọn Tương quan từ danh sách xuất hiện.
2. Chúng tôi thiết lập tất cả các cài đặt cần thiết. “Khoảng thời gian đầu vào” - khoảng thời gian của tất cả bốn cột. “Khoảng thời gian đầu ra” - nơi chúng tôi muốn hiển thị tổng số. Chúng tôi nhấp vào nút “OK”.
3. Một ma trận tương quan được xây dựng ở nơi đã chọn. Mỗi giao điểm của một hàng và một cột là một hệ số tương quan. Số 1 được hiển thị khi các tọa độ khớp với nhau.

Hàm CORREL để xác định mối quan hệ và mối tương quan trong Excel

CORREL - hàm dùng để tính hệ số tương quan giữa 2 mảng. Hãy xem xét bốn ví dụ về tất cả các khả năng của chức năng này.

Ví dụ về cách sử dụng hàm CORREL trong Excel

Ví dụ đầu tiên. Có một tấm bảng ghi thông tin về mức lương trung bình của nhân viên công ty trong suốt mười một năm và tỷ giá hối đoái là đô la. Cần xác định mối quan hệ giữa hai đại lượng này. Bảng trông như thế này:

Thuật toán tính toán trông giống như sau:

Điểm được hiển thị gần bằng 1. Kết quả:

Xác định hệ số tương quan của tác động của các hành động đến kết quả

Ví dụ thứ hai. Hai nhà thầu đã tiếp cận hai đại lý khác nhau để được trợ giúp về chương trình khuyến mại kéo dài mười lăm ngày. Mỗi ngày, một cuộc thăm dò xã hội đã được thực hiện để xác định mức độ hỗ trợ cho từng người nộp đơn. Bất kỳ người được phỏng vấn nào cũng có thể chọn một trong hai người nộp đơn hoặc phản đối tất cả. Cần xác định mức độ ảnh hưởng của mỗi đợt khuyến mại đối với mức độ hỗ trợ người nộp đơn, công ty nào hiệu quả hơn.

Sử dụng các công thức dưới đây, chúng tôi tính toán hệ số tương quan:

• =CORREL (A3: A17; B3: B17).
• = CORREL (A3: A17; C3: C17).

Kết quả:

Từ kết quả thu được, rõ ràng là mức độ hỗ trợ cho người đăng ký đầu tiên tăng lên theo mỗi ngày quảng cáo, do đó, hệ số tương quan tiếp cận 1. Khi quảng cáo được khởi chạy, người đăng ký khác có một số lượng lớn sự tin tưởng, và 1 ngày có một xu hướng tích cực. Sau đó, mức độ tin tưởng giảm xuống và đến ngày thứ mười lăm, nó giảm xuống dưới các chỉ số ban đầu. Điểm thấp cho thấy rằng sự thăng tiến đã tác động tiêu cực đến việc hỗ trợ. Đừng quên rằng các yếu tố đồng thời khác không được xem xét ở dạng bảng cũng có thể ảnh hưởng đến các chỉ số.

Phân tích mức độ phổ biến của nội dung theo mối tương quan của lượt xem video và lượt đăng lại

Ví dụ thứ ba. Một người để quảng cáo video của riêng họ trên lưu trữ video YouTube sử dụng mạng xã hội để quảng cáo kênh. Anh ấy nhận thấy rằng có một số mối quan hệ giữa số lượt đăng lại trên mạng xã hội và số lượt xem trên kênh. Có thể dự đoán hiệu suất trong tương lai bằng cách sử dụng các công cụ bảng tính không? Cần xác định tính hợp lý của việc áp dụng phương trình hồi quy tuyến tính để dự đoán số lượt xem video phụ thuộc vào số lần đăng lại. Bảng có các giá trị:

Bây giờ cần xác định mối quan hệ giữa 2 chỉ tiêu theo công thức dưới đây:

0,7; IF (CORREL (A3: A8; B3: B8)> 0,7; "Mối quan hệ trực tiếp mạnh mẽ"; "Mối quan hệ nghịch đảo mạnh mẽ"); "Mối quan hệ yếu hoặc không có") 'class =' ​​công thức '>

Nếu hệ số kết quả cao hơn 0,7 thì sử dụng hàm hồi quy tuyến tính sẽ thích hợp hơn. Trong ví dụ này, chúng tôi thực hiện:

Bây giờ chúng tôi đang xây dựng một biểu đồ:

Chúng tôi áp dụng phương trình này để xác định số lượt xem ở 200, 500 và 1000 lượt chia sẻ: = 9,2937 * D4-206,12. Chúng tôi nhận được kết quả sau:

Chức năng DỰ BÁO cho phép bạn xác định số lượt xem tại thời điểm hiện tại, nếu có, ví dụ: hai trăm năm mươi lượt đăng lại. Chúng tôi áp dụng: 0,7; PREDICTION (D7; B3: B8; A3: A8); "Các giá trị không liên quan") 'class =' ​​công thức '>. Chúng tôi nhận được kết quả sau:

Các tính năng của việc sử dụng hàm CORREL trong Excel

Chức năng này có các tính năng sau:

1. Các ô trống không được tính đến.
2. Các ô chứa thông tin kiểu Boolean và Text không được tính đến.
3. Phủ định kép “-” được sử dụng để tính các giá trị lôgic ở dạng số.
4. Số lượng ô trong các mảng được nghiên cứu phải khớp, nếu không, thông báo # N / A sẽ được hiển thị.

Đánh giá ý nghĩa thống kê của hệ số tương quan

Khi kiểm định ý nghĩa của hệ số tương quan, giả thuyết vô hiệu là chỉ báo có giá trị bằng 0, trong khi phương án thay thế thì không. Công thức sau được sử dụng để xác minh:

Kết luận

Phân tích tương quan trong bảng tính là một quá trình đơn giản và tự động. Để thực hiện, bạn chỉ cần biết nơi đặt các công cụ cần thiết và cách kích hoạt chúng thông qua cài đặt chương trình.