Giải Pháp Bình Phương ít Nhất. LSM Trong Trường Hợp Mô Hình Tuyến ...

Ví dụ.

Dữ liệu thực nghiệm về giá trị của các biến X và tạiđược đưa ra trong bảng.

Do sự liên kết của chúng, hàm

Sử dụng phương pháp bình phương nhỏ nhất , tính gần đúng những dữ liệu này với sự phụ thuộc tuyến tính y = ax + b(tìm các tùy chọn một và b). Tìm xem dòng nào tốt hơn (theo nghĩa của phương pháp bình phương nhỏ nhất) sắp xếp dữ liệu thực nghiệm. Vẽ tranh.

Bản chất của phương pháp bình phương nhỏ nhất (LSM).

Vấn đề là tìm các hệ số phụ thuộc tuyến tính mà hàm của hai biến một và b chấp nhận giá trị nhỏ nhất. Đó là, với dữ liệu một và b tổng các độ lệch bình phương của dữ liệu thực nghiệm từ đường thẳng tìm được sẽ là nhỏ nhất. Đây là toàn bộ điểm của phương pháp bình phương nhỏ nhất.

Do đó, lời giải của ví dụ được rút gọn thành việc tìm cực trị của một hàm hai biến.

Suy ra công thức tìm hệ số.

Một hệ hai phương trình với hai ẩn số được biên soạn và giải. Tìm đạo hàm riêng của hàm bởi các biến một và b, chúng tôi đánh đồng các đạo hàm này bằng không.

Chúng tôi giải hệ phương trình kết quả bằng bất kỳ phương pháp nào (ví dụ phương pháp thay thế hoặc Phương pháp của Cramer) và có được các công thức để tìm các hệ số bằng phương pháp bình phương nhỏ nhất (LSM).

Với dữ liệu một và b hàm số nhận giá trị nhỏ nhất. Bằng chứng về thực tế này được đưa ra bên dưới văn bản ở cuối trang.

Đó là toàn bộ phương pháp bình phương nhỏ nhất. Công thức tìm tham số một chứa các tổng ,,, và tham số N- lượng dữ liệu thực nghiệm. Giá trị của các tổng này được khuyến nghị tính riêng. Hệ số b tìm thấy sau khi tính toán một.

Đã đến lúc nhớ lại ví dụ ban đầu.

Quyết định.

Trong ví dụ của chúng tôi n = 5. Chúng tôi điền vào bảng để thuận tiện cho việc tính toán các số tiền có trong công thức của các hệ số cần thiết.

Các giá trị trong hàng thứ tư của bảng nhận được bằng cách nhân các giá trị của hàng thứ 2 với giá trị của hàng thứ 3 cho mỗi số tôi.

Các giá trị trong hàng thứ năm của bảng nhận được bằng cách bình phương các giá trị của hàng thứ 2 cho mỗi số tôi.

Giá trị của cột cuối cùng của bảng là tổng các giá trị trên các hàng.

Chúng tôi sử dụng các công thức của phương pháp bình phương nhỏ nhất để tìm các hệ số một và b. Chúng tôi thay thế chúng bằng các giá trị tương ứng từ cột cuối cùng của bảng:

Vì thế, y = 0,165x + 2,184 là đường thẳng ước lượng mong muốn.

Nó vẫn còn để tìm ra dòng nào trong số các dòng y = 0,165x + 2,184 hoặc gần đúng hơn với dữ liệu ban đầu, tức là để ước tính bằng phương pháp bình phương nhỏ nhất.

Ước lượng sai số của phương pháp bình phương nhỏ nhất.

Để làm điều này, bạn cần tính tổng độ lệch bình phương của dữ liệu ban đầu từ các dòng này và , một giá trị nhỏ hơn tương ứng với một dòng gần đúng hơn với dữ liệu ban đầu theo phương pháp bình phương nhỏ nhất.

Kể từ đó, dòng y = 0,165x + 2,184 gần đúng với dữ liệu gốc tốt hơn.

Hình ảnh minh họa của phương pháp bình phương nhỏ nhất (LSM).

Mọi thứ trông tuyệt vời trên bảng xếp hạng. Dòng màu đỏ là dòng được tìm thấy y = 0,165x + 2,184, đường màu xanh lam là , các chấm màu hồng là dữ liệu gốc.

Trong thực tế, khi mô hình hóa các quá trình khác nhau - cụ thể là kinh tế, vật lý, kỹ thuật, xã hội - những phương pháp tính toán giá trị gần đúng của các hàm từ các giá trị đã biết của chúng tại một số điểm cố định được sử dụng rộng rãi.

Các vấn đề về tính gần đúng của các hàm dạng này thường phát sinh:

khi xây dựng các công thức tính gần đúng để tính giá trị của các đại lượng đặc trưng của quá trình đang nghiên cứu theo dữ liệu dạng bảng thu được từ kết quả thí nghiệm;

tích phân số, phân biệt, giải phương trình vi phân vân vân.;

nếu cần tính các giá trị của hàm số tại các điểm trung gian của khoảng đã xét;

khi xác định các giá trị của các đại lượng đặc trưng của quá trình nằm ngoài khoảng đang xét, cụ thể là khi dự báo.

Nếu, để mô hình hóa một quá trình nhất định được chỉ định bởi một bảng, một hàm được xây dựng mô tả gần đúng quá trình này dựa trên phương pháp bình phương nhỏ nhất, nó sẽ được gọi là hàm xấp xỉ (hồi quy) và nhiệm vụ xây dựng các hàm xấp xỉ chính nó sẽ là một bài toán gần đúng.

Bài viết này thảo luận về các khả năng của gói MS Excel để giải quyết các vấn đề như vậy, ngoài ra, các phương pháp và kỹ thuật xây dựng (tạo) hồi quy cho các hàm đã cho theo bảng (là cơ sở của phân tích hồi quy) cũng được đưa ra.

Có hai tùy chọn để xây dựng hồi quy trong Excel.

Thêm các khu vực đã chọn ( đường xu hướng- đường xu hướng) thành biểu đồ được xây dựng trên cơ sở bảng dữ liệu cho đặc tính của quá trình đã nghiên cứu (chỉ có sẵn nếu biểu đồ được xây dựng);

Sử dụng các hàm thống kê có sẵn của trang tính Excel, cho phép bạn lấy các hồi quy (đường xu hướng) trực tiếp từ bảng dữ liệu nguồn.

Thêm đường xu hướng vào biểu đồ

Đối với một bảng dữ liệu mô tả một quy trình nhất định và được biểu diễn bằng biểu đồ, Excel có một công cụ phân tích hồi quy hiệu quả cho phép bạn:

xây dựng dựa trên phương pháp bình phương nhỏ nhất và thêm năm vào sơ đồ các loại hồi quy, với các mức độ chính xác khác nhau, mô hình hóa quá trình đang nghiên cứu;

thêm một phương trình của hồi quy đã xây dựng vào sơ đồ;

xác định mức độ tuân thủ của hồi quy đã chọn với dữ liệu hiển thị trên biểu đồ.

Dựa trên dữ liệu biểu đồ, Excel cho phép bạn lấy các loại hồi quy tuyến tính, đa thức, logarit, hàm mũ, lũy thừa, được đưa ra bởi phương trình:

y = y (x)

trong đó x là một biến độc lập, thường nhận các giá trị của một dãy số tự nhiên (1; 2; 3; ...) và tạo ra, ví dụ: đếm ngược thời gian của quá trình đang nghiên cứu (đặc điểm) .

1 . Hồi quy tuyến tính tốt trong việc mô hình hóa các đối tượng địa lý tăng hoặc giảm với tốc độ không đổi. Đây là mô hình đơn giản nhất của quy trình đang được nghiên cứu. Nó được xây dựng theo phương trình:

y = mx + b

trong đó m là tiếp tuyến của hệ số góc hồi quy tuyến tínhđến trục x; b - tọa độ giao điểm của hồi quy tuyến tính với trục y.

2 . Đường xu hướng đa thức hữu ích để mô tả các đặc điểm có một số điểm cực trị riêng biệt (giá trị cao và điểm thấp nhất). Sự lựa chọn bậc của đa thức được xác định bởi số cực trị của đặc trưng đang nghiên cứu. Do đó, một đa thức bậc hai có thể mô tả tốt một quá trình chỉ có một cực đại hoặc cực tiểu; đa thức bậc ba - không quá hai cực trị; đa thức bậc 4 - không quá ba cực trị, v.v.

Trong trường hợp này, đường xu hướng được xây dựng theo phương trình:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

trong đó các hệ số c0, c1, c2, ... c6 là các hằng số mà giá trị của nó được xác định trong quá trình xây dựng.

3 . Đường xu hướng logarit được sử dụng thành công trong các đặc điểm mô hình hóa, các giá trị của chúng thay đổi nhanh chóng lúc đầu, và sau đó ổn định dần.

y = c ln (x) + b

4 . Đường xu hướng công suất cho kết quả tốt nếu các giá trị của sự phụ thuộc được nghiên cứu được đặc trưng bởi sự thay đổi liên tục trong tốc độ tăng trưởng. Ví dụ về sự phụ thuộc như vậy có thể dùng như một đồ thị về chuyển động có gia tốc đều của ô tô. Nếu không có hoặc giá trị âm, bạn không thể sử dụng đường xu hướng công suất.

Nó được xây dựng theo phương trình:

y = cxb

trong đó các hệ số b, c là hằng số.

5 . Đường xu hướng hàm mũ nên được sử dụng nếu tốc độ thay đổi trong dữ liệu liên tục tăng. Đối với dữ liệu có chứa giá trị bằng 0 hoặc âm, loại xấp xỉ này cũng không được áp dụng.

Nó được xây dựng theo phương trình:

y = cebx

trong đó các hệ số b, c là hằng số.

Khi chọn một đường xu hướng, Excel sẽ tự động tính toán giá trị của R2, đặc trưng cho độ chính xác của phép gần đúng: giá trị R2 càng gần một thì đường xu hướng càng gần đúng với quá trình đang nghiên cứu. Nếu cần, giá trị của R2 luôn có thể được hiển thị trên sơ đồ.

Được xác định theo công thức:

Để thêm đường xu hướng vào chuỗi dữ liệu:

kích hoạt biểu đồ được xây dựng trên cơ sở chuỗi dữ liệu, tức là nhấp vào trong vùng biểu đồ. Mục Biểu đồ sẽ xuất hiện trong menu chính;

Sau khi nhấp vào mục này, một menu sẽ xuất hiện trên màn hình, trong đó bạn nên chọn lệnh Thêm đường xu hướng.

Các hành động tương tự được thực hiện dễ dàng nếu bạn di chuột qua biểu đồ tương ứng với một trong các chuỗi dữ liệu và nhấp chuột phải; trong menu ngữ cảnh xuất hiện, hãy chọn lệnh Thêm đường xu hướng. Hộp thoại Đường xu hướng sẽ xuất hiện trên màn hình với tab Loại được mở (Hình 1).

Sau đó bạn cần:

Trên tab Loại, chọn loại đường xu hướng cần thiết (Tuyến tính được chọn theo mặc định). Đối với kiểu Đa thức, trong trường Độ, hãy chỉ định bậc của đa thức đã chọn.

1 . Trường Tích hợp trên Chuỗi liệt kê tất cả chuỗi dữ liệu trong biểu đồ được đề cập. Để thêm đường xu hướng vào một chuỗi dữ liệu cụ thể, hãy chọn tên của nó trong trường Được xây dựng trên chuỗi.

Nếu cần, bằng cách chuyển đến tab Tham số (Hình 2), bạn có thể đặt các tham số sau cho đường xu hướng:

thay đổi tên của đường xu hướng trong Tên của trường đường cong xấp xỉ (làm mịn).

đặt số khoảng thời gian (tiến hoặc lùi) cho dự báo trong trường Dự báo;

hiển thị phương trình của đường xu hướng trong khu vực biểu đồ mà bạn nên bật hộp kiểm hiển thị phương trình trên biểu đồ;

hiển thị giá trị của độ tin cậy gần đúng R2 trong vùng sơ đồ, bạn nên bật hộp kiểm để đặt giá trị của độ tin cậy gần đúng (R ^ 2) trên biểu đồ;

đặt điểm giao của đường xu hướng với trục Y, mà bạn nên bật hộp kiểm Giao điểm của đường cong với trục Y tại một điểm;

nhấp vào nút OK để đóng hộp thoại.

Có ba cách để bắt đầu chỉnh sửa đường xu hướng đã được tạo sẵn:

sử dụng lệnh Đường xu hướng đã chọn từ menu Định dạng, sau khi chọn đường xu hướng;

chọn lệnh Định dạng Đường xu hướng từ trình đơn ngữ cảnh, lệnh này được gọi bằng cách nhấp chuột phải vào đường xu hướng;

bằng cách nhấp đúp vào đường xu hướng.

Hộp thoại Đường xu hướng định dạng sẽ xuất hiện trên màn hình (Hình 3), chứa ba tab: Xem, Kiểu, Tham số và nội dung của hai tab cuối cùng hoàn toàn trùng khớp với các tab tương tự của hộp thoại Đường xu hướng (Hình 1-2 ). Trên tab Chế độ xem, bạn có thể đặt loại đường, màu sắc và độ dày của nó.

Để xóa một đường xu hướng đã được xây dựng, hãy chọn đường xu hướng cần xóa và nhấn phím Delete.

Ưu điểm của công cụ phân tích hồi quy được coi là:

sự dễ dàng tương đối của việc vẽ đường xu hướng trên biểu đồ mà không cần tạo bảng dữ liệu cho nó;

một danh sách khá rộng các loại đường xu hướng được đề xuất và danh sách này bao gồm các loại hồi quy được sử dụng phổ biến nhất;

khả năng dự đoán hành vi của quá trình đang nghiên cứu cho một tùy ý (trong ý thức chung) số bước tiến cũng như lùi;

khả năng thu được phương trình của đường xu hướng ở dạng phân tích;

khả năng thu được đánh giá độ tin cậy của phép gần đúng, nếu cần.

Những bất lợi bao gồm các điểm sau:

việc xây dựng đường xu hướng chỉ được thực hiện nếu có một biểu đồ được xây dựng trên một chuỗi dữ liệu;

Quá trình tạo chuỗi dữ liệu cho đặc tính đang được nghiên cứu dựa trên phương trình đường xu hướng thu được hơi lộn xộn: các phương trình hồi quy bắt buộc được cập nhật với mỗi thay đổi trong giá trị của chuỗi dữ liệu gốc, nhưng chỉ trong vùng biểu đồ , trong khi chuỗi dữ liệu được hình thành trên cơ sở xu hướng phương trình đường cũ, vẫn không thay đổi;

Trong báo cáo PivotChart, khi bạn thay đổi chế độ xem biểu đồ hoặc báo cáo PivotTable được liên kết, các đường xu hướng hiện có sẽ không được bảo toàn, vì vậy bạn phải đảm bảo rằng bố cục của báo cáo đáp ứng các yêu cầu của bạn trước khi bạn vẽ đường xu hướng hoặc định dạng báo cáo PivotChart.

Các đường xu hướng có thể được thêm vào chuỗi dữ liệu được trình bày trên biểu đồ như biểu đồ, biểu đồ, biểu đồ vùng không chuẩn hóa phẳng, biểu đồ thanh, phân tán, bong bóng và cổ phiếu.

Bạn không thể thêm đường xu hướng vào chuỗi dữ liệu trên biểu đồ 3-D, Chuẩn, Radar, Hình tròn và Bánh rán.

Sử dụng các hàm Excel tích hợp sẵn

Excel cũng cung cấp một công cụ phân tích hồi quy để vẽ các đường xu hướng bên ngoài vùng biểu đồ. Một số hàm trang tính thống kê có thể được sử dụng cho mục đích này, nhưng tất cả chúng đều cho phép bạn chỉ tạo các hồi quy tuyến tính hoặc hàm mũ.

Excel có một số hàm để xây dựng hồi quy tuyến tính, cụ thể là:

XU HƯỚNG;

• SLOPE và CUT.

Cũng như một số hàm để xây dựng đường xu hướng hàm mũ, cụ thể là:

LGRFPapprox.

Cần lưu ý rằng các kỹ thuật xây dựng hồi quy bằng cách sử dụng các hàm TREND và GROWTH thực tế là giống nhau. Điều tương tự cũng có thể nói về cặp hàm LINEST và LGRFPRIBL. Đối với bốn hàm này, khi tạo bảng giá trị, các tính năng Excel như công thức mảng sẽ được sử dụng, điều này phần nào làm xáo trộn quá trình xây dựng hồi quy. Chúng tôi cũng lưu ý rằng việc xây dựng một hồi quy tuyến tính, theo quan điểm của chúng tôi, là dễ thực hiện nhất bằng cách sử dụng các hàm SLOPE và INTERCEPT, trong đó hàm đầu tiên xác định độ dốc của hồi quy tuyến tính và hàm thứ hai xác định đoạn bị cắt bởi hồi quy trên trục y.

Ưu điểm của công cụ chức năng tích hợp để phân tích hồi quy là:

một quá trình khá đơn giản của cùng một kiểu hình thành chuỗi dữ liệu của đặc trưng đang được nghiên cứu cho tất cả các hàm thống kê cài sẵn thiết lập các đường xu hướng;

một kỹ thuật tiêu chuẩn để xây dựng các đường xu hướng dựa trên chuỗi dữ liệu đã tạo;

khả năng dự đoán hành vi của quá trình đang nghiên cứu về khối lượng bắt buộc bước tiến hoặc lùi.

Và những bất lợi bao gồm thực tế là Excel không có các hàm tích hợp để tạo các loại đường xu hướng khác (ngoại trừ tuyến tính và hàm mũ). Tình huống này thường không cho phép lựa chọn một mô hình đủ chính xác của quá trình đang nghiên cứu, cũng như có được những dự báo sát với thực tế. Ngoài ra, khi sử dụng các hàm TREND và GROW, phương trình của các đường xu hướng không được biết.

Cần lưu ý rằng các tác giả đã không đặt mục tiêu của bài báo là trình bày quá trình phân tích hồi quy với các mức độ hoàn chỉnh khác nhau. Nhiệm vụ chính của nó là thể hiện khả năng của gói Excel trong việc giải các bài toán xấp xỉ bằng cách sử dụng các ví dụ cụ thể; chứng minh những công cụ hiệu quả nào mà Excel có để xây dựng hồi quy và dự báo; minh họa cách những vấn đề như vậy có thể được giải quyết một cách tương đối dễ dàng ngay cả khi người dùng không có kiến ​​thức sâu về phân tích hồi quy.

Ví dụ về giải quyết các vấn đề cụ thể

Xem xét giải pháp của các vấn đề cụ thể bằng cách sử dụng các công cụ được liệt kê của gói Excel.

Nhiệm vụ 1

Với bảng số liệu về lợi nhuận của doanh nghiệp vận tải cơ giới năm 1995-2002. bạn cần phải làm như sau.

Xây dựng biểu đồ.

Thêm các đường xu hướng tuyến tính và đa thức (bậc hai và bậc ba) vào biểu đồ.

Sử dụng phương trình đường xu hướng, thu được dữ liệu dạng bảng về lợi nhuận của doanh nghiệp cho từng đường xu hướng trong giai đoạn 1995-2004.

Lập dự báo lợi nhuận của doanh nghiệp cho năm 2003 và 2004.

Giải pháp của vấn đề

Trong phạm vi ô A4: C11 của trang tính Excel, chúng tôi nhập trang tính được hiển thị trong Hình. 4.

Sau khi chọn phạm vi ô B4: C11, chúng tôi xây dựng một biểu đồ.

Chúng tôi kích hoạt biểu đồ đã xây dựng và theo phương pháp được mô tả ở trên, sau khi chọn loại đường xu hướng trong hộp thoại Đường xu hướng (xem Hình 1), chúng tôi lần lượt thêm các đường xu hướng tuyến tính, bậc hai và khối vào biểu đồ. Trong cùng một hộp thoại, hãy mở tab Tham số (xem Hình 2), trong Tên của trường đường cong xấp xỉ (làm mịn), hãy nhập tên của xu hướng được thêm vào và trong trường Dự báo chuyển tiếp cho: giai đoạn, hãy đặt giá trị 2, vì nó được lên kế hoạch đưa ra dự báo lợi nhuận cho hai năm tới. Để hiển thị phương trình hồi quy và giá trị độ tin cậy gần đúng R2 trong vùng sơ đồ, hãy bật hộp kiểm Hiển thị phương trình trên màn hình và đặt giá trị độ tin cậy gần đúng (R ^ 2) trên biểu đồ. Để nhận thức trực quan tốt hơn, chúng tôi thay đổi loại, màu sắc và độ dày của các đường xu hướng đã xây dựng, chúng tôi sử dụng tab View của hộp thoại Định dạng đường xu hướng (xem Hình 3). Biểu đồ kết quả với các đường xu hướng được bổ sung được hiển thị trong hình. 5.

Để có được dữ liệu dạng bảng về lợi nhuận của doanh nghiệp cho từng đường xu hướng trong giai đoạn 1995-2004. Hãy sử dụng phương trình của các đường xu hướng được trình bày trong hình. 5. Để thực hiện việc này, trong các ô của phạm vi D3: F3, hãy nhập thông tin dạng văn bản về loại đường xu hướng đã chọn: Xu hướng tuyến tính, Xu hướng bậc hai, Xu hướng khối. Tiếp theo, nhập công thức hồi quy tuyến tính vào ô D4 và sử dụng điểm đánh dấu điền, sao chép công thức này với các tham chiếu tương đối đến phạm vi ô D5: D13. Cần lưu ý rằng mỗi ô có công thức hồi quy tuyến tính từ phạm vi ô D4: D13 có một ô tương ứng từ phạm vi A4: A13 làm đối số. Tương tự, đối với hồi quy bậc hai, phạm vi ô E4: E13 được lấp đầy và đối với hồi quy bậc ba, phạm vi ô F4: F13 được lấp đầy. Do đó, một dự báo về lợi nhuận của doanh nghiệp cho năm 2003 và 2004 đã được đưa ra. với ba xu hướng. Bảng giá trị kết quả được hiển thị trong hình. 6.

Nhiệm vụ 2

Xây dựng biểu đồ.

Thêm các đường xu hướng logarit, hàm mũ và hàm mũ vào biểu đồ.

Suy ra phương trình của các đường xu hướng thu được, cũng như các giá trị của độ tin cậy gần đúng R2 cho từng đường đó.

Sử dụng phương trình đường xu hướng, có được dữ liệu dạng bảng về lợi nhuận của doanh nghiệp cho từng đường xu hướng trong giai đoạn 1995-2002.

Lập dự báo lợi nhuận cho doanh nghiệp cho năm 2003 và 2004 bằng cách sử dụng các đường xu hướng này.

Giải pháp của vấn đề

Theo phương pháp được đưa ra khi giải quyết vấn đề 1, chúng ta thu được một sơ đồ có thêm các đường xu hướng logarit, hàm mũ và hàm mũ (Hình 7). Hơn nữa, sử dụng các phương trình đường xu hướng thu được, chúng tôi điền vào bảng giá trị lợi nhuận của doanh nghiệp, bao gồm các giá trị dự đoán cho năm 2003 và 2004. (Hình 8).

Trên hình. 5 và hình có thể thấy rằng mô hình có xu hướng logarit tương ứng với giá trị thấp nhất của độ tin cậy xấp xỉ

R2 = 0,8659

Các giá trị cao nhất của R2 tương ứng với các mô hình có xu hướng đa thức: bậc hai (R2 = 0,9263) và bậc ba (R2 = 0,933).

Nhiệm vụ 3

Với bảng số liệu về lợi nhuận của doanh nghiệp vận tải cơ giới giai đoạn 1995-2002 ở nhiệm vụ 1, bạn phải thực hiện các bước sau.

Nhận chuỗi dữ liệu cho các đường xu hướng tuyến tính và hàm mũ bằng cách sử dụng các hàm TREND và GROW.

Sử dụng các hàm TREND và TĂNG TRƯỞNG, hãy lập dự báo lợi nhuận cho doanh nghiệp trong năm 2003 và 2004.

Đối với dữ liệu ban đầu và chuỗi dữ liệu nhận được, hãy xây dựng một sơ đồ.

Giải pháp của vấn đề

Hãy sử dụng trang tính của nhiệm vụ 1 (xem Hình 4). Hãy bắt đầu với Hàm TREND:

chọn dải ô D4: D11, ô này sẽ được điền các giá trị của hàm TREND tương ứng với dữ liệu đã biết về lợi nhuận của doanh nghiệp;

gọi lệnh Hàm từ menu Chèn. Trong hộp thoại Trình hướng dẫn chức năng xuất hiện, hãy chọn chức năng TREND từ danh mục Thống kê, sau đó bấm vào nút OK. Thao tác tương tự có thể được thực hiện bằng cách nhấn nút (Chức năng chèn) của thanh công cụ tiêu chuẩn.

Trong hộp thoại Đối số hàm xuất hiện, nhập phạm vi ô C4: C11 vào trường Đã biết_giá_trị_y; trong trường Giá_trị_x đã biết - phạm vi ô B4: B11;

để biến công thức đã nhập thành công thức mảng, hãy sử dụng tổ hợp phím + +.

Công thức chúng ta đã nhập vào thanh công thức sẽ giống như sau: = (TREND (C4: C11; B4: B11)).

Kết quả là, phạm vi ô D4: D11 được lấp đầy bằng các giá trị tương ứng của hàm TREND (Hình 9).

Để đưa ra dự báo về lợi nhuận của công ty cho năm 2003 và 2004. cần thiết:

chọn phạm vi ô D12: D13, nơi các giá trị được dự đoán bởi hàm TREND sẽ được nhập.

gọi hàm TREND và trong hộp thoại Đối số hàm xuất hiện, hãy nhập vào trường Giá_trị_đã biết - phạm vi ô C4: C11; trong trường Giá_trị_x đã biết - phạm vi ô B4: B11; và trong trường New_values_x - phạm vi ô B12: B13.

biến công thức này thành công thức mảng bằng cách sử dụng phím tắt Ctrl + Shift + Enter.

Công thức đã nhập sẽ giống như sau: = (TREND (C4: C11; B4: B11; B12: B13)) và phạm vi ô D12: D13 sẽ được lấp đầy bởi các giá trị dự đoán của hàm TREND (xem Hình. 9).

Tương tự, một chuỗi dữ liệu được điền bằng cách sử dụng hàm GROWTH, được sử dụng trong phân tích các phụ thuộc phi tuyến tính và hoạt động giống hệt như TREND đối tác tuyến tính của nó.

Hình 10 cho thấy bảng ở chế độ hiển thị công thức.

Đối với dữ liệu ban đầu và chuỗi dữ liệu thu được, sơ đồ được hiển thị trong hình. mười một.

Nhiệm vụ 4

Với bảng số liệu tiếp nhận hồ sơ đăng ký dịch vụ của doanh nghiệp vận tải cơ giới từ ngày 01 đến ngày 11 của tháng hiện tại, phải thực hiện các thao tác sau.

Lấy chuỗi dữ liệu cho hồi quy tuyến tính: sử dụng các hàm SLOPE và INTERCEPT; bằng cách sử dụng hàm LINEST.

Truy xuất chuỗi dữ liệu cho hồi quy theo cấp số nhân bằng cách sử dụng hàm LYFFPRIB.

Sử dụng các chức năng trên, lập dự báo về lượng nhận hồ sơ đến cơ quan dịch vụ công văn trong khoảng thời gian từ ngày 12 đến ngày 14 của tháng hiện tại.

Đối với chuỗi dữ liệu gốc và đã nhận, hãy xây dựng một sơ đồ.

Giải pháp của vấn đề

Lưu ý rằng, không giống như các hàm TREND và GROW, không có hàm nào được liệt kê ở trên (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) là hồi quy. Các hàm này chỉ đóng vai trò phụ trợ, xác định các tham số hồi quy cần thiết.

Đối với các hồi quy tuyến tính và hàm mũ được xây dựng bằng cách sử dụng các hàm SLOPE, INTERCEPT, LINEST, LGRFPRIB, sự xuất hiện của các phương trình của chúng luôn được biết trước, trái ngược với các hồi quy tuyến tính và hàm mũ tương ứng với các hàm TREND và GROWTH.

1 . Hãy xây dựng một hồi quy tuyến tính có phương trình:

y = mx + b

sử dụng các hàm SLOPE và INTERCEPT, với độ dốc của hồi quy m được xác định bởi hàm SLOPE và số hạng không đổi b - bởi hàm INTERCEPT.

Để làm điều này, chúng tôi thực hiện các hành động sau:

nhập bảng nguồn trong phạm vi ô A4: B14;

giá trị của tham số m sẽ được xác định trong ô C19. Chọn từ danh mục Thống kê, chức năng Độ dốc; nhập phạm vi ô B4: B14 trong trường giá_trị_y đã biết và phạm vi ô A4: A14 trong trường_giá_trị_x đã biết. Công thức sẽ được nhập vào ô C19: = SLOPE (B4: B14; A4: A14);

bằng phương pháp tương tự, giá trị của tham số b trong ô D19 được xác định. Và nội dung của nó sẽ như thế này: = INTERCEPT (B4: B14; A4: A14). Do đó, các giá trị của tham số m và b, cần thiết để xây dựng một hồi quy tuyến tính, sẽ được lưu trữ tương ứng trong các ô C19, D19;

sau đó ta nhập công thức hồi quy tuyến tính vào ô C4 có dạng: = $ C * A4 + $ D. Trong công thức này, các ô C19 và D19 được viết với tham chiếu tuyệt đối (địa chỉ ô không được thay đổi khi có thể sao chép). Dấu tham chiếu tuyệt đối $ có thể được nhập từ bàn phím hoặc sử dụng phím F4, sau khi đặt con trỏ vào địa chỉ ô. Sử dụng ô điều khiển điền, sao chép công thức này vào phạm vi ô C4: C17. Chúng tôi nhận được chuỗi dữ liệu mong muốn (Hình 12). Do số lượng yêu cầu là một số nguyên, bạn nên đặt định dạng số trên tab Số của cửa sổ Định dạng Ô với số vị trí thập phân là 0.

2 . Bây giờ chúng ta hãy xây dựng một hồi quy tuyến tính cho bởi phương trình:

y = mx + b

bằng cách sử dụng hàm LINEST.

Đối với điều này:

nhập hàm LINEST dưới dạng công thức mảng vào phạm vi ô C20: D20: = (LINEST (B4: B14; A4: A14)). Kết quả là chúng ta nhận được giá trị của tham số m trong ô C20 và giá trị của tham số b trong ô D20;

nhập công thức vào ô D4: = $ C * A4 + $ D;

sao chép công thức này bằng cách sử dụng điểm đánh dấu điền vào phạm vi ô D4: D17 và nhận được chuỗi dữ liệu mong muốn.

3 . Chúng tôi xây dựng một hồi quy hàm mũ có phương trình:

với sự trợ giúp của hàm LGRFPRIBL, nó được thực hiện tương tự:

trong phạm vi ô C21: D21, nhập hàm LGRFPRIBL dưới dạng công thức mảng: = (LGRFPRIBL (B4: B14; A4: A14)). Trong trường hợp này, giá trị của tham số m sẽ được xác định trong ô C21 và giá trị của tham số b sẽ được xác định trong ô D21;

công thức được nhập vào ô E4: = $ D * $ C ^ A4;

bằng cách sử dụng điểm đánh dấu điền, công thức này được sao chép vào phạm vi ô E4: E17, nơi đặt chuỗi dữ liệu cho hồi quy theo cấp số nhân (xem Hình 12).

Trên hình. 13 hiển thị một bảng trong đó chúng ta có thể xem các hàm chúng ta sử dụng với các phạm vi ô cần thiết, cũng như các công thức.

Giá trị R 2 triệu tập hệ số xác định.

Nhiệm vụ của việc xây dựng một phụ thuộc hồi quy là tìm vectơ các hệ số m của mô hình (1) mà tại đó hệ số R nhận giá trị lớn nhất.

Để đánh giá mức ý nghĩa của R, kiểm định F của Fisher được sử dụng, được tính bằng công thức

ở đâu N- cỡ mẫu (số lượng thí nghiệm);

k là số hệ số của mô hình.

Nếu F vượt quá một số giá trị quan trọng cho dữ liệu N và k và mức độ tin cậy được chấp nhận, khi đó giá trị của R được coi là đáng kể. Bảng giá trị tới hạn của F được đưa ra trong các sách tham khảo về thống kê toán học.

Vì vậy, ý nghĩa của R không chỉ được xác định bởi giá trị của nó, mà còn bởi tỷ số giữa số lượng thí nghiệm và số lượng hệ số (tham số) của mô hình. Thật vậy, tỷ lệ tương quan cho n = 2 đối với một mô hình tuyến tính đơn giản là 1 (thông qua 2 điểm trên mặt phẳng, bạn luôn có thể vẽ một đường thẳng duy nhất). Tuy nhiên, nếu dữ liệu thực nghiệm là các biến ngẫu nhiên, thì giá trị R như vậy cần được tin cậy hết sức cẩn thận. Thông thường, để thu được R đáng kể và hồi quy đáng tin cậy, nó nhằm đảm bảo rằng số lượng thử nghiệm vượt quá đáng kể số lượng hệ số của mô hình (n> k).

Để xây dựng một mô hình hồi quy tuyến tính, bạn phải:

1) chuẩn bị một danh sách gồm n hàng và m cột chứa dữ liệu thử nghiệm (cột chứa giá trị đầu ra Y phải là đầu tiên hoặc cuối cùng trong danh sách); ví dụ: chúng ta hãy lấy dữ liệu của nhiệm vụ trước đó, thêm một cột được gọi là "số chu kỳ", đánh số các khoảng thời gian từ 1 đến 12 (đây sẽ là các giá trị X)

2) vào menu Dữ liệu / Phân tích dữ liệu / Hồi quy

Nếu thiếu mục "Phân tích dữ liệu" trong menu "Công cụ", thì bạn nên chuyển đến mục "Bổ trợ" của cùng menu và chọn hộp "Gói phân tích".

3) trong hộp thoại "Hồi quy", đặt:

khoảng thời gian đầu vào Y;

khoảng đầu vào X;

khoảng thời gian đầu ra - ô phía trên bên trái của khoảng thời gian mà kết quả tính toán sẽ được đặt (nên đặt nó trên một trang tính mới);

4) nhấp vào "Ok" và phân tích kết quả.

Nó có nhiều công dụng vì nó cho phép biểu diễn gần đúng chức năng nhất định những người khác đơn giản hơn. LSM có thể cực kỳ hữu ích trong việc xử lý các quan sát và nó được sử dụng tích cực để ước tính một số đại lượng từ kết quả của các phép đo của những người khác có chứa lỗi ngẫu nhiên. Trong bài viết này, bạn sẽ học cách thực hiện các phép tính bình phương nhỏ nhất trong Excel.

Tuyên bố vấn đề trên một ví dụ cụ thể

Giả sử có hai chỉ số X và Y. Hơn nữa, Y phụ thuộc vào X. Vì OLS được chúng ta quan tâm theo quan điểm phân tích hồi quy (trong Excel, các phương pháp của nó được thực hiện bằng các hàm dựng sẵn), chúng ta nên tiến hành ngay lập tức. để xem xét một vấn đề cụ thể.

Vì vậy, hãy để X là khu vực giao dịch cửa hàng tạp hóa, đo bằng mét vuông và Y là doanh thu hàng năm, được định nghĩa bằng hàng triệu rúp.

Cần phải đưa ra dự báo về doanh thu (Y) mà cửa hàng sẽ có nếu nó có một hoặc một khu vực bán hàng khác. Rõ ràng, hàm Y = f (X) đang tăng lên, vì đại siêu thị bán được nhiều hàng hơn quầy hàng.

Vài lời về tính đúng đắn của dữ liệu ban đầu được sử dụng để dự đoán

Giả sử chúng ta có một bảng được xây dựng với dữ liệu cho n cửa hàng.

Dựa theo thống kê toán học, kết quả sẽ ít nhiều đúng nếu dữ liệu của ít nhất 5-6 đối tượng được kiểm tra. Ngoài ra, không thể sử dụng kết quả "dị thường". Đặc biệt, một cửa hàng nhỏ ưu tú có thể có doanh thu lớn hơn nhiều lần so với doanh thu của các cửa hàng lớn thuộc hạng “masmarket”.

Bản chất của phương pháp

Dữ liệu bảng có thể được hiển thị trong Máy bay cartesian dưới dạng các điểm M 1 (x 1, y 1), ... M n (x n, y n). Bây giờ lời giải của bài toán sẽ được rút gọn thành việc chọn một hàm số gần đúng y = f (x), có đồ thị đi qua các điểm M 1, M 2, .. M n càng gần càng tốt.

Tất nhiên, bạn có thể sử dụng đa thức bằng cấp cao, nhưng tùy chọn này không chỉ khó thực hiện mà còn không chính xác, vì nó sẽ không phản ánh xu hướng chính cần được phát hiện. Giải pháp hợp lý nhất là tìm đường thẳng y = ax + b, gần đúng nhất với dữ liệu thực nghiệm, hay nói đúng hơn là các hệ số - a và b.

Điểm chính xác

Đối với bất kỳ giá trị gần đúng nào, việc đánh giá độ chính xác của nó có tầm quan trọng đặc biệt. Biểu thị bằng e i sự khác biệt (độ lệch) giữa các giá trị thực nghiệm và chức năng của điểm x i, tức là e i = y i - f (x i).

Rõ ràng, để đánh giá độ chính xác của phép gần đúng, bạn có thể sử dụng tổng các độ lệch, tức là khi chọn một đường thẳng để biểu diễn gần đúng sự phụ thuộc của X vào Y, nên ưu tiên đường thẳng có giá trị nhỏ nhất của tổng e i tại tất cả các điểm đang xét. Tuy nhiên, không phải mọi thứ đều đơn giản như vậy, vì cùng với những sai lệch tích cực, thực tế sẽ có những sai lệch tiêu cực.

Bạn có thể giải quyết vấn đề bằng cách sử dụng các mô-đun độ lệch hoặc hình vuông của chúng. Phương thức cuối cùng nhận được nhiều nhất sử dụng rộng rãi. Nó được sử dụng trong nhiều lĩnh vực, bao gồm cả phân tích hồi quy (trong Excel, việc triển khai nó được thực hiện bằng cách sử dụng hai hàm tích hợp sẵn), và từ lâu đã được chứng minh là có hiệu quả.

Phương pháp bình phương tối thiểu

Trong Excel, như bạn đã biết, có một hàm autosum được tích hợp sẵn cho phép bạn tính giá trị của tất cả các giá trị nằm trong phạm vi đã chọn. Do đó, sẽ không có gì ngăn cản chúng ta tính giá trị của biểu thức (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Trong ký hiệu toán học, điều này trông giống như:

Vì quyết định ban đầu được đưa ra để tính gần đúng bằng cách sử dụng một đường thẳng, chúng tôi có:

Do đó, nhiệm vụ tìm một đường thẳng mô tả tốt nhất mối quan hệ cụ thể giữa X và Y tương đương với việc tính giá trị nhỏ nhất của một hàm có hai biến:

Điều này đòi hỏi phải cân bằng đạo hàm riêng bằng 0 đối với các biến a và b mới, đồng thời giải một hệ nguyên thủy gồm hai phương trình với 2 ẩn số có dạng:

Sau các phép biến đổi đơn giản, bao gồm phép chia cho 2 và thao tác với các tổng, chúng ta nhận được:

Ví dụ: giải nó bằng phương pháp Cramer, chúng tôi thu được điểm dừng với một số hệ số a * và b *. Đây là mức tối thiểu, tức là, để dự đoán doanh thu mà cửa hàng sẽ có khi khu vực nhất định, đường thẳng y = a * x + b * sẽ làm mô hình hồi quy cho ví dụ được đề cập. Tất nhiên cô ấy sẽ không để bạn tìm thấy kết quả chính xác, nhưng nó sẽ giúp bạn biết liệu mua tín dụng tại một cửa hàng cho một khu vực cụ thể có được đền đáp hay không.

Cách triển khai phương pháp bình phương nhỏ nhất trong Excel

Excel có một hàm để tính giá trị của các ô vuông nhỏ nhất. Cô bé có lần xem tiếp theo: "TREND" (giá trị Y đã biết; giá trị X đã biết; giá trị X mới; hằng số). Hãy áp dụng công thức tính OLS trong Excel cho bảng của chúng tôi.

Để thực hiện việc này, trong ô hiển thị kết quả của phép tính bằng phương pháp bình phương nhỏ nhất trong Excel, hãy nhập dấu “=” và chọn hàm “TREND”. Trong cửa sổ mở ra, hãy điền vào các trường thích hợp, tô sáng:

• phạm vi các giá trị đã biết cho Y (trong trường hợp này dữ liệu về kim ngạch thương mại);
• phạm vi x 1,… x n, tức là kích thước của không gian bán lẻ;
• cả nổi tiếng và giá trị không xác định x, mà bạn cần tìm hiểu quy mô của doanh thu (để biết thông tin về vị trí của họ trên trang tính, hãy xem bên dưới).

Ngoài ra, có một biến logic "Const" trong công thức. Nếu bạn nhập 1 vào trường tương ứng với nó, thì điều này có nghĩa là các phép tính sẽ được thực hiện, giả sử rằng b \ u003d 0.

Nếu bạn cần biết dự báo cho nhiều hơn một giá trị x, thì sau khi nhập công thức, bạn không nên nhấn "Enter" mà cần gõ tổ hợp "Shift" + "Control" + "Enter" ("Enter" ) trên bàn phím.

Một số tính năng

Phân tích hồi quy có thể được truy cập ngay cả bởi hình nộm. Công thức Excelđể dự đoán giá trị của một mảng các biến chưa biết - "TREND" - có thể được sử dụng ngay cả với những người chưa bao giờ nghe nói về phương pháp bình phương nhỏ nhất. Chỉ cần biết một số tính năng về công việc của nó là đủ. Đặc biệt:

• Nếu chúng ta sắp xếp phạm vi giá trị đã biết của biến y trong một hàng hoặc cột, thì mỗi hàng (cột) với giá trị đã biết x sẽ được chương trình coi như một biến riêng biệt.
• Nếu phạm vi có x đã biết không được chỉ định trong cửa sổ "XU HƯỚNG", thì trong trường hợp sử dụng hàm trong Chương trình Excel sẽ coi nó như một mảng bao gồm các số nguyên, số tương ứng với phạm vi có các giá trị cho trước của biến y.
• Để xuất một mảng các giá trị "dự đoán", biểu thức xu hướng phải được nhập dưới dạng công thức mảng.
• Nếu không có giá trị x mới nào được chỉ định, thì hàm TREND coi chúng bằng những giá trị đã biết. Nếu chúng không được chỉ định, thì mảng 1 được coi là đối số; 2; 3; 4;…, tương xứng với phạm vi với các tham số y đã cho sẵn.
• Phạm vi chứa các giá trị x mới phải bao gồm cùng hoặc hơn hàng hoặc cột, dưới dạng một dải ô với các giá trị y đã cho. Nói cách khác, nó phải tương xứng với các biến độc lập.
• Một mảng với các giá trị x đã biết có thể chứa nhiều biến. Tuy nhiên, nếu chúng tôi đang nói chuyện chỉ khoảng một, thì các phạm vi với các giá trị đã cho của x và y phải tương xứng. Trong trường hợp có nhiều biến, phạm vi có các giá trị y đã cho phải nằm trong một cột hoặc một hàng.

Chức năng DỰ BÁO

Nó được thực hiện bằng cách sử dụng một số chức năng. Một trong số đó được gọi là "PREDICTION". Nó tương tự như TREND, tức là nó cho kết quả của các phép tính bằng phương pháp bình phương nhỏ nhất. Tuy nhiên, chỉ với một X, mà giá trị của Y là không xác định.

Bây giờ bạn đã biết các công thức Excel cho hình nộm cho phép bạn dự đoán giá trị của giá trị tương lai của một chỉ số theo xu hướng tuyến tính.

Nó được sử dụng rộng rãi trong kinh tế lượng dưới hình thức giải thích kinh tế rõ ràng cho các tham số của nó.

Hồi quy tuyến tính được rút gọn để tìm một phương trình có dạng

hoặc