Đường cong ROC trong Excel youtube

Đường cong này luôn nằm bên dưới đường cong y = x, đại diện cho đường đẳng thức, i. e. trong đó mọi thành viên của dân số có cùng giá trị y

Ví dụ

ví dụ 1. Vẽ đường cong Lorenz cho dữ liệu trong phạm vi A4. A23 trong Hình 1

Hình 1 – Dữ liệu cho Đường cong Lorenz và Chỉ số Gini

Việc đầu tiên chúng ta cần làm là sắp xếp dữ liệu theo thứ tự tăng dần. Điều này được thực hiện trong cột B, chẳng hạn bằng cách sử dụng công thức mảng Real Statistics =QSORT(A4. A23) trong phạm vi B4. B23. Bây giờ chúng ta tạo một bảng tần suất bằng cách đặt các giá trị dữ liệu duy nhất vào cột D. Nếu không có bản sao nào trong cột B, thì cột D giống như cột B

Tính các giá trị x

Đối với ví dụ này, rõ ràng từ cột B rằng các giá trị dữ liệu duy nhất là các giá trị từ 1 đến 10. Nếu nó không quá rõ ràng, chúng ta có thể đặt công thức mảng Real Statistics =NODUPES(B4. B23) trong phạm vi D5. D14. Trên thực tế, chúng ta có thể sử dụng công thức mảng Real Statistics =SortUnique(A4. Thay vào đó, A23, trong trường hợp đó, chúng tôi hoàn toàn không cần cột B

Các giá trị trong cột E đại diện cho các giá trị tần số tương ứng với các giá trị trong cột D. e. g. giá trị dữ liệu 1 xảy ra ba lần và do đó giá trị tần số trong ô E5 là 3. Điều này có thể thu được bằng cách sử dụng công thức mảng Excel =COUNTIF($A$4. $A$23,D5). Các giá trị tần số khác có thể thu được bằng cách đánh dấu phạm vi E5. E14 và nhấn tổ hợp phím Ctrl-D. Tổng của tất cả các giá trị tần số là 20 (ô E15), như được tính bằng =COUNT(A4. A23) hoặc =SUM(E5. E14)

Cột F chứa các tần số tích lũy. Ở đây, chúng tôi đặt công thức =E5+F4 trong ô F5, đánh dấu phạm vi F5. F14 và nhấn Ctrl-D. Nếu chúng ta chia mỗi giá trị này cho 20, chúng ta sẽ nhận được các giá trị x cho đường cong Lorenz, như thể hiện trong cột I. Ở đây, chúng ta đặt công thức =F4/E$15 vào ô I4, đánh dấu phạm vi I4. I14 và nhấn Ctrl-D

Tính các giá trị y

Bây giờ chúng tôi trình bày cách tính các giá trị y tương ứng cho đường cong Lorenz. Đầu tiên, chúng ta đặt công thức =D5*E5 vào ô G5 và =G5+H4 vào ô H5. Bây giờ chúng ta có thể điền các giá trị khác vào cột G và H như chúng ta đã làm trước đó, với ô G15 chứa tổng của tất cả các giá trị dữ liệu ban đầu, như được tính bởi =SUM(A4. A23) hoặc =SUM(G5. G14)

Các giá trị y cho đường cong Lorenz, được hiển thị trong cột J, là các giá trị dữ liệu tích lũy từ cột H chia cho tổng của tất cả các giá trị dữ liệu từ ô G15. Điều này được thực hiện bằng cách đặt công thức =H4/G$15 vào ô J4, đánh dấu phạm vi J4. J14 và nhấn Ctrl-D

Đồ thị đường cong Lorenz

Bây giờ chúng ta sử dụng khả năng lập biểu đồ của Excel để có được biểu đồ của đường cong Lorenz như trong Hình 2. Chúng tôi cũng bao gồm đường cong y = x đại diện cho đường đẳng thức

Hình 2 – Đường cong Lorenz

Phạm vi đánh dấu I4. J14 và chọn Chèn > Biểu đồ. Scatter và chọn tùy chọn Scatter with Smooth Lines. Tiếp theo, chọn Thiết kế > Dữ liệu. Chọn Data xuất hiện hộp thoại như hình 3

Hình 3 – Hộp thoại Select Data Source

Đầu tiên, nhấp vào nút Thêm ở phía bên trái của hộp thoại. Điền thông tin vào hộp thoại hiện ra như Hình 4 và bấm vào nút OK

Hình 4 – Thêm chuỗi Đẳng thức

Lưu ý rằng bạn đang nhập các giá trị cho các giá trị Chuỗi X giống như các giá trị Chuỗi Y (vì các giá trị x và y giống nhau trên dòng y = x). Khi hộp thoại trong Hình 3 xuất hiện lại, nhấp vào Chuỗi 1 ở bên trái hộp thoại và nhấp vào nút Chỉnh sửa để thay đổi nhãn Chuỗi 1 thành Lorenz

Sau khi thêm tiêu đề biểu đồ, đảm bảo rằng Chú thích được hiển thị và đảm bảo rằng trục x và trục y chạy từ 0 đến 1 (như được giải thích trong Biểu đồ Excel), bạn sẽ đến biểu đồ như trong Hình 2

Chỉ số Gini là diện tích dưới đường cong Lorenz

Chỉ số Gini bằng hai lần diện tích giữa các đường cong Bình đẳng và Lorenz. Lưu ý rằng diện tích dưới đường cong Bình đẳng là 0. 5 và diện tích dưới đường cong Lorenz có thể được tính gần đúng bằng cách thêm các diện tích hình thang như được mô tả trong Công cụ phân tích dữ liệu ROC. Diện tích của 10 hình thang được sử dụng cho Ví dụ 1 được hiển thị trong cột K của Hình 1. e. g. diện tích của hình thang thứ nhất (ô K5) được tính theo công thức =(I5-I4)*(J5+J4)/2. Để tính diện tích các hình thang khác, tô đậm dãy K5. K14 và nhấn Ctrl-D

Diện tích dưới đường cong Lorenz xấp xỉ. 3415, tổng của các diện tích này (ô K15), được tính theo công thức =SUM(K5. K14). Do đó, diện tích giữa các đường cong là. 5 –. 3245 =. 175. Do đó, chỉ số Gini gấp đôi giá trị này, cụ thể là. 351, như được hiển thị trong ô K17. Lưu ý rằng vì diện tích dưới đường cong Bình đẳng là. 5, chỉ số Gini đo tỷ lệ phần trăm nhỏ hơn mức bình đẳng hoàn hảo được biểu thị bằng dữ liệu, ví dụ 1 là 35. 1%

tính toán chỉ số Gini

Chúng ta cũng có thể tính chỉ số Gini bằng công thức

như được mô tả trong Hệ số Gini. Tính toán này được minh họa trong Hình 5.

Hình 5 – Phép tính Gini thay thế

Ở đây, cột T chứa các chỉ số từ 1 đến 20, cột U chứa dữ liệu theo thứ tự tăng dần (i. e. một bản sao của cột B từ Hình 1). Cột V chứa tích của các giá trị trong cột T và U(e. g. ô V4 chứa công thức =T4*U4) và ô V24 chứa tổng các phần tử trong cột V. Cuối cùng, chỉ số Gini của. 351 (ô Y4) được tính theo công thức

=2*V24/(T23*U24)-(T23+1)/T23

Lưu ý rằng bạn có thể tính toán chỉ số Gini bằng cách sử dụng cách sau trực tiếp từ dữ liệu thô chưa được sắp xếp, phạm vi A4. A23 từ Hình 1

=(2*TÓM TẮT(SEQ(COUNT(A4. A23)),QSORT(A4. A23,,-1))/ TỔNG (A4. A23)-(ĐẾM(A4. A23)+1))/ĐẾM(A4. A23)

Ở đây, QSORT và SEQ là các hàm Thống kê thực. Trong Excel 2019 hoặc 365, SEQ có thể được thay thế bằng hàm Excel tiêu chuẩn SEQUENCE

Chức năng trang tính

Chức năng thống kê thực. Chức năng sau được cung cấp trong Real Statistics Pack

GINI(R1). hệ số Gini cho dữ liệu trong R1

Dữ liệu trong R1 không cần sắp xếp. Ví dụ 1, =GINI(A4. A23) tạo ra giá trị trong ô Y4 của Hình 5

Người giới thiệu

Mirzaei, S. , Borzadaran G. r. m. , Aminib, M. , Jabbarib, H. (2017) Nghiên cứu so sánh các công cụ ước lượng hệ số Gini dựa trên phương pháp hồi quy. Truyền thông cho các ứng dụng và phương pháp thống kê. tập. 24, Không. 4, 339–351

Làm cách nào để tạo đường cong ROC trong Excel?

Đường cong ROC được tính như thế nào?

Đường cong ROC cho bạn thấy điều gì?