Hồi quy tuyến tính với các thanh lỗi excel

Tôi đang cố gắng thực hiện phân tích thống kê trên một tập hợp các điểm dữ liệu (x, y) với các thanh lỗi ngang và dọc khác nhau trong Excel 2010. Tôi cần một dòng phù hợp nhất (hồi quy tuyến tính). Tôi đã cố gắng làm điều đó với LINEST, tuy nhiên LINEST chỉ chấp nhận các giá trị x và y làm đối số và không xem xét các thanh lỗi trên các điểm dữ liệu đó. Do đó, các điểm dữ liệu có thanh lỗi rộng sẽ làm lệch đường phù hợp nhất nhiều hơn mức cần thiết.  

Tôi cần một hàm Excel 2010 khác, tập hợp các hàm hoặc đầu vào thủ công cho phép tôi thực hiện phân tích hồi quy tuyến tính, cho đầu ra tương tự như LINEST, xem xét lỗi trên các điểm dữ liệu mà nó chấp nhận làm đầu vào, mang lại độ dốc .  

Cảm ơn bạn

Có một giải pháp tương đối đơn giản cho vấn đề này. tính toán “giới hạn tài chính” dựa trên “hồi quy nghịch đảo” [Draper và Smith 1981]. Ý tưởng là tạo các đường bao tin cậy cho dòng thực và sau đó tìm phạm vi giá trị $X$ trong đó các đường bao này chứa phản hồi mục tiêu

Sau khi giới thiệu một số ký hiệu (dự định khớp với ký hiệu đó trong Draper & Smith), câu trả lời này thực hiện phân tích sơ bộ về tình huống, minh họa ý tưởng bằng một biểu đồ dữ liệu mô phỏng và trình bày các công thức. Nó kết thúc bằng một cuộc thảo luận ngắn (trong đó trình bày một phép tính gần đúng đơn giản) và tham chiếu đến nguồn chính của giải pháp này, sách hồi quy của Draper & Smith

(Nguồn gốc của câu trả lời này là một báo cáo tôi đã viết cách đây nhiều năm liên quan đến việc giám sát liên tục nồng độ trong môi trường. $X_i$ là thời gian và $Y_i$ là nồng độ nhật ký. Các vấn đề về (a) giám sát để xác định khi nào một giá trị sẽ đạt đến mục tiêu định trước và (b) hiệu chuẩn hệ thống đo lường--trong đó $X_i$ là các giá trị đã biết và $Y_i$ là phản hồi của thiết bị--là hai tình huống . )

Hãy thiết lập ký hiệu. Dữ liệu là $(X_i, Y_i)$, $i=1, 2, \ldots, n$. mô hình là

$$Y_i = \beta_0 + \beta_1 X_i + \varepsilon_i$$

đối với các tham số không xác định $\beta_0$ (phần chặn) và $\beta_1$ (độ dốc) và các biến số Bình thường, trung bình bằng 0 độc lập $\varepsilon_i$ với phương sai chung (không xác định) $\sigma^2$. Hồi quy bình phương nhỏ nhất thông thường thu được các ước tính $b_0$, $b_1$ và $s$ của các ẩn số $\beta_0$, $\beta_1$ và $\sigma$. Các phép tính đưa vào các ước tính đó bao gồm phương tiện $\bar X$ và $\bar Y$ cũng như tổng bình phương độ lệch của $X_i$,

$$S_{XX} = \sum_{i=1}^n (X_i - \bar{X})^2. $$

Phân tích

Để bắt đầu phân tích, hãy lưu ý rằng đường hồi quy nhất thiết phải đi qua điểm trung bình $(\bar{X}, \bar{Y})$, biểu thị phản hồi trung bình $\bar Y$ đạt được ở tọa độ trung bình $\bar . Ngoài ra, trục hoành $\bar Y$ có phân phối chuẩn, không tương quan với độ dốc ước tính $b_1$ và có sai số chuẩn giảm xuống 0 khi lượng dữ liệu tăng lên. Giá trị của $X$ cho bất kỳ $Y_0$ cụ thể nào có thể được ước tính bằng cách bắt đầu từ đây và ngoại suy, mang lại ước tính là

$$\hat{X}_0 = (\bar{Y} - Y_0)/b_1 + \bar X. $$

Bước thứ hai là lưu ý rằng đối với bất kỳ giá trị nào $X$, chúng tôi có thể tính toán giới hạn độ tin cậy trên cho phản hồi phù hợp tại $X$. Nhu cầu về giới hạn độ tin cậy phát sinh từ sự không chắc chắn về giá trị của các hệ số $\beta_0$ và $\beta_1$. chúng tôi không chắc chắn chính xác về giao điểm thực và độ dốc thực, vì vậy đường thực thực sự có thể nằm trong một loạt các đường có thể. Phản hồi được trang bị tại $X$ có thể được viết

$$\hat{Y}(X) = \bar{Y} - b_1(X - \bar{X})$$

và lỗi tiêu chuẩn của giá trị được trang bị đó bằng

$$\operatorname{se}(\hat{Y}(X)) = s\left(\frac{1}{n} + \frac{(X - \bar{X})^2}{S_{XX . $$

Giá trị phù hợp được phân phối chuẩn, do đó giới hạn độ tin cậy trên của độ tin cậy $1 - \alpha$ có thể được tạo ở dạng

$$\operatorname{UCL}(X) = \hat{Y}(X) + t(n-2, \alpha) \operatorname{se}(\hat{Y}(X))$$

và giới hạn tin cậy thấp hơn (LCL) được xây dựng tương tự. (Như thường lệ, $t$ đề cập đến điểm phần trăm của phân phối $t$ của Sinh viên. ) Khi $X$ thay đổi, các cung hyperbol theo dõi UCL và LCL nằm bên trên và bên dưới đường được trang bị

Trục hoành vẽ các giá trị $X$ trong khi trục tung vẽ các giá trị $Y$. Các cung hyperbol được hiển thị dưới dạng các đường cong màu xanh lá cây (LCL) và màu vàng (UCL). Các giới hạn fiducial được tìm thấy bằng cách giao các cung này với một đường nằm ngang ở độ cao $Y_0\,$ được chỉ định là "Mục tiêu" trong chú giải. UCL kết quả được hiển thị với biểu tượng kim cương. Hình minh họa này sử dụng dữ liệu mô phỏng. điều này cho phép chúng tôi xem dữ liệu bổ sung có thể thay đổi hợp lý như thế nào so với những gì các tính toán khiến chúng tôi mong đợi. (Lý do tại sao các giá trị "được quan sát" và "được mô phỏng" được kết nối trực quan là vì điều này cho thấy biểu đồ nồng độ so với. thời gian của một quá trình có lẽ là liên tục. )

Dung dịch

Để tìm “giới hạn tín dụng trên” hoặc “giới hạn tin cậy nghịch đảo cho $X$ cho trước $Y_0$” ([Draper & Smith 1981] phần 1. 7), tìm nghiệm lớn nhất $X$ của phương trình

$$Y_0 = \operatorname{UCL}(X),$$

nếu một giải pháp như vậy tồn tại. Điều này có thể được giải quyết bằng công thức bậc hai, đưa ra

$$\operatorname{UCL}(X) = \bar{X} + \frac{D_0 + g\sqrt{D_0^2 + (1-g^2)S_{XX}/n}}{1-g^

ở đâu

$$D_0 = (\bar Y - Y_0) / b_1$$

là giá trị ước tính của $X$ tương ứng với $Y_0$,

$$g^2 = \frac{t^2 s^2}{b_1^2 S_{XX}}$$

là một tính toán phụ trợ, và

$$t = t(n-2, \alpha). $$

Giới hạn tin cậy thấp hơn trên $X$ có được bằng cách sử dụng căn bậc hai âm $–g$ trong $(1)$. (Những công thức này tương đương với [Draper và Smith] phương trình 1. 7. 6. Tôi viết $g^2$ ở đây thay cho $g$ của họ. Phiên bản này dễ tính toán hơn một chút với. )

Thảo luận

Không tồn tại giới hạn tin cậy. Chúng chỉ có thể được tìm thấy khi có niềm tin chắc chắn rằng hệ số góc thực sự khác không. Draper & Smith gợi ý rằng việc tính toán giới hạn độ tin cậy cho $X$ “không có nhiều giá trị thực tế” trừ khi $g^2 < 0. 2 đô la, mặc dù họ không đưa ra bất kỳ lời biện minh nào cho tuyên bố bao trùm như vậy

Khi $g^2$ tương đối nhỏ, một phép tính gần đúng tốt có được bằng cách khai triển $(1)$ trong một chuỗi lũy thừa theo căn bậc hai dương của nó $g$ và dừng sau số hạng tuyến tính, thu được

$$\operatorname{UCL}(X) \approx \bar{X} + D_0 + g\sqrt{D_0^2 + S_{XX}/n} + \cdots\tag{2}. $$

Lưu ý rằng $g^2$ nhỏ khi, so với phương sai ước tính $s^2$, hệ số ước tính $b_1$ lớn, phương sai của $X_i$ (nghĩa là $S_{XX}/n$ . Nói tóm lại, bất kỳ sự kết hợp nào của độ dốc tuyệt đối lớn, dải rộng trong $X_i$, lượng dữ liệu lớn, biến thiên tương đối nhỏ xung quanh một đường cong tuyến tính và/hoặc nhu cầu tin cậy khiêm tốn sẽ đảm bảo xấp xỉ $(2)$ là tốt . Cũng xin lưu ý rằng khi dữ liệu bổ sung được thu thập trong phạm vi giá trị $X$ bao trùm giới hạn độ tin cậy, $\operatorname{UCL}(X)$ hội tụ thành $\bar{X}+D_0$ , giá trị ước tính, dưới dạng một

Người giới thiệu

Draper, NR và H Smith, 1981. Phân tích hồi quy ứng dụng, Phiên bản thứ hai. John Wiley & Sons, New York

Bạn có thể có các thanh lỗi trên biểu đồ đường không?

Có thể sử dụng Excel để hồi quy tuyến tính không?

Một đường hồi quy có thể được hiển thị trên biểu đồ thanh không?