Phương pháp đồ thị bình phương bé nhất. Sử dụng các hàm Excel tích hợp

Phương pháp bình phương nhỏ nhất

trên bài học cuối cùng chủ đề chúng ta sẽ làm quen với ứng dụng nổi tiếng nhất FNP, tìm thấy ứng dụng rộng rãi nhất trong các lĩnh vực khoa học và hoạt động thực tiễn. Nó có thể là vật lý, hóa học, sinh học, kinh tế học, xã hội học, tâm lý học, v.v. Theo ý muốn của số phận, tôi thường phải giải quyết vấn đề kinh tế, vì vậy hôm nay tôi sẽ sắp xếp cho bạn một vé đến đất nước tuyệt vờiđược phép kinh tế lượng=) … Làm thế nào để bạn không muốn điều đó?! Ở đó rất tốt - bạn chỉ cần quyết định! …Nhưng điều chắc chắn bạn muốn là học cách giải quyết vấn đề bình phương nhỏ nhất. Và những độc giả đặc biệt siêng năng sẽ học cách giải chúng không chỉ CHÍNH XÁC mà còn RẤT NHANH ;-) Nhưng trước tiên tuyên bố chung của vấn đề + ví dụ đồng hành:

cho vào một số lĩnh vực chủ đề các chỉ tiêu có biểu hiện định lượng được điều tra. Đồng thời, có mọi lý do để tin rằng chỉ báo phụ thuộc vào chỉ báo. Giả định này có thể được giả thuyết khoa học và được dựa trên cảm giác thông thường cơ bản. Tuy nhiên, hãy để khoa học sang một bên và khám phá những lĩnh vực ngon miệng hơn - cụ thể là các cửa hàng tạp hóa. Biểu thị bởi:

– không gian bán lẻ của một cửa hàng tạp hóa, m2,
- doanh thu hàng năm của một cửa hàng tạp hóa, triệu rúp.

Nó là khá rõ ràng những gì nhiều diện tích hơn cửa hàng, doanh thu của nó càng lớn trong hầu hết các trường hợp.

Giả sử rằng sau khi tiến hành quan sát / thí nghiệm / tính toán / khiêu vũ với tambourine, chúng ta có sẵn dữ liệu số:

Với các cửa hàng tạp hóa, tôi nghĩ mọi thứ đều rõ ràng: - đây là khu vực của cửa hàng thứ nhất, - doanh thu hàng năm của nó, - khu vực của cửa hàng thứ 2, - doanh thu hàng năm của nó, v.v. Nhân tiện, không nhất thiết phải có quyền truy cập vào tài liệu phân loại- đầy đủ ước tính chính xác doanh thu có thể đạt được bằng cách thống kê toán học . Tuy nhiên, đừng để bị phân tâm, quá trình gián điệp thương mại đã được trả tiền =)

Dữ liệu dạng bảng cũng có thể được viết dưới dạng điểm và được mô tả theo cách thông thường đối với chúng tôi. hệ Descartes .

chúng tôi sẽ trả lời Câu hỏi quan trọng: bạn cần bao nhiêu điểm nghiên cứu định tính?

Càng to càng tốt. Bộ chấp nhận tối thiểu bao gồm 5-6 điểm. Ngoài ra, với lượng dữ liệu ít, không nên đưa các kết quả “bất thường” vào mẫu. Vì vậy, ví dụ, một cửa hàng nhỏ ưu tú có thể giúp thực hiện các đơn đặt hàng lớn hơn "đồng nghiệp của họ", do đó làm sai lệch Mô hình chung, đó là để được tìm thấy!

Nếu nó khá đơn giản, chúng ta cần chọn một chức năng, lịch trình mà đi càng gần càng tốt với các điểm . Một chức năng như vậy được gọi là xấp xỉ (xấp xỉ - xấp xỉ) hoặc chức năng lý thuyết . Nói chung, ở đây ngay lập tức xuất hiện "ứng viên" rõ ràng - đa thức bằng cấp cao, có đồ thị đi qua TẤT CẢ các điểm. Nhưng tùy chọn này phức tạp và thường đơn giản là không chính xác. (bởi vì biểu đồ sẽ luôn luôn "gió" và phản ánh kém xu hướng chính).

Do đó, chức năng mong muốn phải đủ đơn giản và đồng thời phản ánh đầy đủ sự phụ thuộc. Như bạn có thể đoán, một trong những phương pháp để tìm các chức năng như vậy được gọi là bình phương nhỏ nhất. Đầu tiên, hãy phân tích bản chất của nó một cách tổng quát. Hãy để một số hàm xấp xỉ dữ liệu thử nghiệm:


Làm thế nào để đánh giá độ chính xác của xấp xỉ này? Chúng ta cũng hãy tính toán sự khác biệt (độ lệch) giữa thực nghiệm và giá trị chức năng (chúng tôi nghiên cứu bản vẽ). Ý nghĩ đầu tiên xuất hiện trong đầu là ước tính tổng lớn bao nhiêu, nhưng vấn đề là sự khác biệt có thể âm. (Ví dụ, ) và những sai lệch do tổng kết như vậy sẽ triệt tiêu lẫn nhau. Do đó, như một ước tính về độ chính xác của xấp xỉ, nó gợi ý chính nó lấy tổng mô-đun sai lệch:

hoặc ở dạng gấp: (dành cho ai chưa biết: là biểu tượng tổng và - biến phụ - "bộ đếm", nhận giá trị từ 1 đến ) .

Xấp xỉ các điểm nghiệm với các hàm khác nhau, ta sẽ được những nghĩa khác nhau, và rõ ràng, khi tổng này nhỏ hơn, hàm đó chính xác hơn.

Một phương pháp như vậy tồn tại và được gọi là phương pháp mô đun nhỏ nhất. Tuy nhiên, trong thực tế nó đã trở nên phổ biến hơn nhiều. phương pháp bình phương nhỏ nhất, trong đó có thể giá trị âmđược loại bỏ không phải bằng mô đun, mà bằng cách bình phương các độ lệch:

, sau đó các nỗ lực được hướng đến việc lựa chọn một hàm sao cho tổng bình phương độ lệch càng nhỏ càng tốt. Trên thực tế, do đó tên của phương pháp.

Và bây giờ chúng tôi trở lại với một cái khác tâm điểm: như đã lưu ý ở trên, chức năng được chọn sẽ khá đơn giản - nhưng cũng có nhiều chức năng như vậy: tuyến tính , hypebol , số mũ , logarit , bậc hai vân vân. Và, tất nhiên, ở đây tôi muốn ngay lập tức "thu hẹp lĩnh vực hoạt động." Chọn loại hàm nào để nghiên cứu? nguyên thủy nhưng tiếp nhận hiệu quả:

- Cách dễ nhất để vẽ điểm trên hình vẽ và phân tích vị trí của chúng. Nếu chúng có xu hướng nằm trên một đường thẳng, thì bạn nên tìm phương trình đường thẳng với các giá trị tối ưu và . Nói cách khác, nhiệm vụ là tìm các hệ số SUCH - sao cho tổng các bình phương độ lệch là nhỏ nhất.

Ví dụ, nếu các điểm nằm dọc theo cường điệu, thì rõ ràng là hàm tuyến tính sẽ cho một xấp xỉ kém. Trong trường hợp này, chúng tôi đang tìm kiếm các hệ số "thuận lợi" nhất cho phương trình hyperbola - những người cho tổng bình phương tối thiểu .

Bây giờ lưu ý rằng trong cả hai trường hợp chúng ta đang nói về chức năng của hai biến, có đối số là tùy chọn phụ thuộc tìm kiếm:

Và về bản chất, chúng ta cần giải một bài toán chuẩn - tìm cực tiểu của hàm hai biến.

Nhớ lại ví dụ của chúng tôi: giả sử rằng các điểm "cửa hàng" có xu hướng nằm trên một đường thẳng và có mọi lý do để tin rằng sự hiện diện phụ thuộc tuyến tính doanh thu từ không gian bán lẻ. Hãy tìm các hệ số SUCH "a" và "be" sao cho tổng bình phương độ lệch là nhỏ nhất. Mọi thứ như thường lệ - đầu tiên đạo hàm riêng cấp 1. Dựa theo quy luật tuyến tính bạn có thể phân biệt ngay dưới biểu tượng tổng:

Nếu bạn muốn sử dụng thông tin nàyđối với một bài luận hoặc một bài báo học kỳ - Tôi sẽ rất biết ơn về liên kết trong danh sách các nguồn, bạn sẽ tìm thấy những tính toán chi tiết như vậy ở một số nơi:

Hãy tạo một hệ thống tiêu chuẩn:

Chúng tôi giảm mỗi phương trình bằng một "hai" và, ngoài ra, "chia nhỏ" các khoản tiền:

Ghi chú : độc lập phân tích tại sao có thể bỏ "a" và "be" ra khỏi biểu tượng tổng. Nhân tiện, chính thức điều này có thể được thực hiện với tổng

Hãy viết lại hệ thống ở dạng "áp dụng":

sau đó thuật toán giải quyết vấn đề của chúng tôi bắt đầu được rút ra:

Chúng ta có biết tọa độ của các điểm không? Chúng tôi biết. tổng chúng ta có thể tìm thấy? Một cách dễ dàng. Chúng tôi sáng tác đơn giản nhất hệ hai phương trình tuyến tính với hai ẩn số("a" và "beh"). Chúng tôi giải quyết hệ thống, ví dụ, Phương pháp Cramer, kết quả là điểm dừng. kiểm tra điều kiện đủ cho một cực trị, chúng ta có thể xác minh rằng tại thời điểm này hàm đạt chính xác tối thiểu. Việc xác minh được liên kết với các tính toán bổ sung và do đó chúng tôi sẽ để nó ở hậu trường. (nếu cần có thể xem khung còn thiếunơi đây ) . Chúng tôi rút ra kết luận cuối cùng:

Hàm số cách tốt nhất (ít nhất là so với bất kỳ khác hàm tuyến tính) đưa các điểm thí nghiệm lại gần hơn . Nói một cách đại khái, đồ thị của nó đi càng gần những điểm này càng tốt. trong truyền thống kinh tế lượng hàm xấp xỉ kết quả cũng được gọi là phương trình cặp hồi quy tuyến tính .

Vấn đề đang được xem xét có một lượng lớn giá trị thực tiễn. Trong tình huống với ví dụ của chúng tôi, phương trình cho phép bạn dự đoán loại doanh thu nào ("yig") sẽ có mặt tại cửa hàng với giá trị này hoặc giá trị khác của khu vực bán hàng (một hoặc một ý nghĩa khác của "x"). Vâng, dự báo kết quả sẽ chỉ là dự báo, nhưng trong nhiều trường hợp, nó sẽ khá chính xác.

Tôi sẽ chỉ phân tích một vấn đề với các số "thực", vì không có khó khăn gì trong đó - tất cả các phép tính đều ở mức chương trình giáo dục lớp 7-8. Trong 95 phần trăm các trường hợp, bạn sẽ được yêu cầu chỉ tìm một hàm tuyến tính, nhưng ở phần cuối của bài viết, tôi sẽ chỉ ra rằng việc tìm các phương trình cho hyperbola, số mũ và một số hàm khác không còn khó khăn nữa.

Trên thực tế, việc phân phát những món quà đã hứa vẫn còn - để bạn học cách giải những ví dụ như vậy không chỉ chính xác mà còn nhanh chóng. Chúng tôi nghiên cứu kỹ tiêu chuẩn:

Một nhiệm vụ

Kết quả của việc nghiên cứu mối quan hệ giữa hai chỉ số, đã thu được các cặp số sau:

Sử dụng phương pháp bình phương nhỏ nhất, tìm hàm tuyến tính gần đúng nhất với kinh nghiệm (có kinh nghiệm) dữ liệu. Tạo một bản vẽ trong đó bằng Cartesian hệ chữ nhật tọa độ để xây dựng điểm nghiệm và đồ thị của hàm số gần đúng . Tìm tổng bình phương độ lệch giữa giá trị thực nghiệm và lý thuyết. Tìm hiểu xem chức năng có tốt hơn không (theo phương pháp bình phương nhỏ nhất)điểm thực nghiệm gần đúng.

Lưu ý rằng các giá trị "x" là các giá trị tự nhiên và điều này có ý nghĩa quan trọng đặc trưng mà tôi sẽ nói sau một chút; nhưng tất nhiên, chúng có thể là phân số. Ngoài ra, tùy thuộc vào nội dung của một nhiệm vụ cụ thể, cả hai giá trị "X" và "G" đều có thể âm hoàn toàn hoặc một phần. Chà, chúng tôi đã được giao một nhiệm vụ "không có khuôn mặt" và chúng tôi bắt đầu nó dung dịch:

Chúng tôi tìm thấy các hệ số của hàm tối ưu như một giải pháp cho hệ thống:

Đối với mục đích của một ký hiệu nhỏ gọn hơn, biến "bộ đếm" có thể được bỏ qua, vì rõ ràng là phép tính tổng được thực hiện từ 1 đến .

Sẽ thuận tiện hơn khi tính toán số tiền cần thiết ở dạng bảng:


Các phép tính có thể được thực hiện trên máy vi tính, nhưng sẽ tốt hơn nhiều nếu sử dụng Excel - vừa nhanh hơn vừa không có lỗi; xem một video ngắn:

Vì vậy, chúng tôi nhận được như sau hệ thống:

Tại đây bạn có thể nhân phương trình thứ hai với 3 và trừ số thứ 2 từ số hạng phương trình thứ nhất theo số hạng. Nhưng đây là điều may mắn - trong thực tế, các hệ thống thường không có năng khiếu và trong những trường hợp như vậy, nó tiết kiệm được Phương pháp Cramer:
, nên hệ có nghiệm duy nhất.

Hãy làm một kiểm tra. Tôi hiểu rằng tôi không muốn, nhưng tại sao lại bỏ qua những sai lầm mà bạn hoàn toàn không thể bỏ qua? Thay nghiệm tìm được vào vế trái của mỗi phương trình của hệ:

bên phải nhận được phương trình tương ứng, nghĩa là hệ đã giải đúng.

Do đó, hàm xấp xỉ mong muốn: – từ tất cả các chức năng tuyến tính dữ liệu thử nghiệm được xấp xỉ tốt nhất bởi nó.

không giống dài sự phụ thuộc của doanh thu của cửa hàng vào khu vực của nó, sự phụ thuộc được tìm thấy là đảo ngược (nguyên tắc "càng nhiều - càng ít"), và sự thật này ngay lập tức được tiết lộ bởi tiêu cực hệ số góc . Hàm cho ta biết khi chỉ tiêu nào đó tăng 1 đơn vị thì giá trị của chỉ tiêu phụ thuộc giảm trung bình bằng 0,65 đơn vị. Người ta nói, giá kiều mạch càng cao thì càng ít bán.

Để vẽ đồ thị hàm gần đúng, chúng tôi tìm thấy hai giá trị của nó:

và thực hiện bản vẽ:

Đường thẳng được xây dựng được gọi là đường xu hướng (cụ thể là, một đường xu hướng tuyến tính, tức là trong trường hợp chung xu hướng không nhất thiết phải là một đường thẳng). Mọi người đều quen thuộc với cụm từ "bắt kịp xu hướng" và tôi nghĩ rằng thuật ngữ này không cần bình luận thêm.

Tính tổng bình phương độ lệch giữa các giá trị thực nghiệm và lý thuyết. Về mặt hình học, đây là tổng bình phương độ dài của các đoạn "đỏ thẫm" (hai trong số đó nhỏ đến mức bạn thậm chí không thể nhìn thấy chúng).

Hãy tóm tắt các tính toán trong một bảng:


Chúng có thể được thực hiện lại theo cách thủ công, đề phòng tôi sẽ đưa ra một ví dụ cho điểm đầu tiên:

nhưng nó hiệu quả hơn nhiều để làm theo một cách nào đó:

Hãy nhắc lại: ý nghĩa của kết quả là gì? Từ tất cả các chức năng tuyến tính hàm có số mũ nhỏ nhất, nghĩa là trong họ của nó, đây là xấp xỉ tốt nhất. Và ở đây, nhân tiện, không phải ngẫu nhiên. câu hỏi cuối cùng vấn đề: điều gì sẽ xảy ra nếu hàm số mũ được đề xuất nó sẽ tốt hơn để ước tính các điểm thử nghiệm?

Hãy tìm tổng độ lệch bình phương tương ứng - để phân biệt chúng, tôi sẽ chỉ định chúng bằng chữ "epsilon". Kỹ thuật này hoàn toàn giống nhau:

Và một lần nữa cho mọi phép tính lửa cho điểm đầu tiên:

Trong Excel, chúng tôi sử dụng chức năng tiêu chuẩn kinh nghiệm (Có thể tìm thấy cú pháp trong Trợ giúp Excel).

Sự kết luận: , nghĩa là hàm mũ xấp xỉ điểm thực nghiệm kém hơn so với đường thẳng .

Nhưng cần lưu ý ở đây rằng "tệ hơn" là không có nghĩa là chưa, chuyện gì thế. Bây giờ tôi đã xây dựng một đồ thị của hàm số mũ này - và nó cũng đi qua gần các điểm - đến mức nếu không có nghiên cứu phân tích thì khó có thể nói hàm nào chính xác hơn.

Điều này hoàn thành giải pháp và tôi quay lại câu hỏi về các giá trị tự nhiên của đối số. Trong các nghiên cứu khác nhau, theo quy luật, kinh tế hoặc xã hội học, tháng, năm hoặc các khoảng thời gian bằng nhau khác được đánh số bằng chữ "X" tự nhiên. Ví dụ, xét bài toán sau:

Chúng tôi có dữ liệu sau về doanh thu bán lẻ của cửa hàng trong nửa đầu năm:

Sử dụng căn chỉnh phân tích đường thẳng, tìm doanh số bán hàng cho tháng 7.

Vâng, không vấn đề gì: chúng tôi đánh số các tháng 1, 2, 3, 4, 5, 6 và sử dụng thuật toán thông thường, kết quả là chúng tôi nhận được một phương trình - điều duy nhất khi nói đến thời gian thường là chữ "te " (mặc dù nó không quan trọng). Phương trình kết quả cho thấy rằng trong nửa đầu năm, doanh thu tăng trung bình 27,74 CU. mỗi tháng. Nhận dự báo cho tháng 7 (tháng #7): EU.

Và các nhiệm vụ tương tự - bóng tối là bóng tối. Ai có nhu cầu có thể sử dụng Dịch vụ bổ sung, cụ thể là của tôi máy tính excel (bản thử), cái mà giải quyết vấn đề gần như ngay lập tức! Phiên bản làm việc của chương trình có sẵn đổi lại hoặc là thanh toán tượng trưng.

Vào cuối buổi học thông tin ngắn về việc tìm kiếm sự phụ thuộc của một số loại khác. Trên thực tế, không có gì đặc biệt để nói, vì cách tiếp cận cơ bản và thuật toán giải pháp vẫn giữ nguyên.

Chúng ta hãy giả sử rằng vị trí của các điểm thử nghiệm giống như một hyperbola. Sau đó, để tìm các hệ số của hyperbola tốt nhất, bạn cần tìm giá trị cực tiểu của hàm - những ai muốn có thể chi tiêu tính toán chi tiết và đưa ra một hệ thống tương tự:

Từ quan điểm kỹ thuật chính thức, nó được lấy từ hệ thống "tuyến tính" (hãy đánh dấu nó bằng dấu hoa thị) thay thế "x" bằng . Chà, số tiền tính toán, sau đó là các hệ số tối ưu "a" và "be" trong tầm tay.

Nếu có mọi lý do để tin rằng các điểm được sắp xếp dọc theo một đường cong logarit, sau đó để tìm kiếm các giá trị tối ưu và tìm giá trị tối thiểu của hàm . Về mặt hình thức, trong hệ thống (*) nên được thay thế bằng:

Khi tính toán trong Excel, hãy sử dụng hàm LN. Tôi thú nhận rằng sẽ không khó để tôi tạo ra các máy tính cho từng trường hợp đang xem xét, nhưng sẽ vẫn tốt hơn nếu bạn tự "lập trình" các phép tính. Video hướng dẫn để giúp đỡ.

Với sự phụ thuộc hàm mũ, tình hình phức tạp hơn một chút. Để làm giảm vấn đề để trường hợp tuyến tính, lấy logarit của hàm và sử dụng tính chất của logarit:

Bây giờ, so sánh hàm thu được với hàm tuyến tính , chúng ta đi đến kết luận rằng trong hệ thống (*) phải được thay thế bởi , và - bởi . Để thuận tiện, ta ký hiệu:

Xin lưu ý rằng hệ thống được giải quyết đối với và , và do đó, sau khi tìm ra nghiệm, bạn không được quên tìm chính hệ số đó.

Để xấp xỉ điểm thực nghiệm parabol tối ưu , nên được tìm thấy cực tiểu của hàm ba biến . Sau khi thực hiện các hành động tiêu chuẩn, chúng tôi nhận được "hoạt động" sau hệ thống:

Vâng, tất nhiên, có nhiều số tiền hơn ở đây, nhưng không có khó khăn gì khi sử dụng ứng dụng yêu thích của bạn. Và cuối cùng, tôi sẽ cho bạn biết cách kiểm tra và xây dựng nhanh chóng bằng Excel dòng mong muốn xu hướng: tạo biểu đồ phân tán, chọn bất kỳ điểm nào bằng chuột và nhấp chuột phải chọn tùy chọn "Thêm đường xu hướng". Tiếp theo, chọn loại biểu đồ và trên tab "Tùy chọn" kích hoạt tùy chọn "Hiển thị phương trình trên biểu đồ". ĐƯỢC RỒI

Như mọi khi, tôi muốn hoàn thành một bài báo cụm từ đẹp, và tôi gần như đã gõ "Hãy hợp thời trang!". Nhưng với thời gian, anh ấy đã thay đổi quyết định. Và không phải vì nó là công thức. Tôi không biết mọi người thế nào, nhưng tôi không muốn chạy theo xu hướng cổ xúy của Mỹ và đặc biệt là châu Âu =) Vì vậy, tôi mong mỗi bạn hãy giữ vững lập trường của mình!

http://www.grandars.ru/student/vysshaya-matematika/metod-naimenshih-kvadratov.html

Phương pháp bình phương nhỏ nhất là một trong những phương pháp phổ biến nhất và phát triển nhất do tính đơn giản và hiệu quả của các phương pháp ước lượng các tham số của mô hình kinh tế lượng tuyến tính. Đồng thời, cần thận trọng nhất định khi sử dụng nó, vì các mô hình được xây dựng bằng cách sử dụng nó có thể không đáp ứng một số yêu cầu về chất lượng của các tham số và do đó, không phản ánh “tốt” các mô hình phát triển quy trình.

Chúng ta hãy xem xét quy trình ước tính các tham số của mô hình kinh tế lượng tuyến tính bằng phương pháp bình phương nhỏ nhất một cách chi tiết hơn. Một mô hình như vậy ở dạng tổng quát có thể được biểu diễn bằng phương trình (1.2):

y t = a 0 + a 1 x 1t +...+ a n x nt + ε t .

Dữ liệu ban đầu khi ước lượng các tham số a 0 , a 1 ,..., a n là véc tơ giá trị của biến phụ thuộc y= (y 1 , y 2 , ... , y T)" và ma trận giá trị của các biến độc lập

trong đó cột đầu tiên, bao gồm các cột, tương ứng với hệ số của mô hình .

Phương pháp bình phương nhỏ nhất được đặt tên dựa trên nguyên tắc cơ bản là các ước lượng tham số thu được trên cơ sở của nó phải thỏa mãn: tổng bình phương của lỗi mô hình phải nhỏ nhất.

Ví dụ giải bài toán bằng phương pháp bình phương bé nhất

Ví dụ 2.1. Doanh nghiệp thương mại có một mạng lưới bao gồm 12 cửa hàng, thông tin về các hoạt động được trình bày trong Bảng. 2.1.

Ban lãnh đạo công ty muốn biết quy mô doanh thu hàng năm phụ thuộc vào không gian bán lẻ của cửa hàng như thế nào.

Bảng 2.1

Giải pháp bình phương nhỏ nhất. Hãy để chúng tôi chỉ định - doanh thu hàng năm của cửa hàng thứ -, triệu rúp; - diện tích cửa hàng bán thứ nghìn m 2 .

Hình.2.1. Biểu đồ phân tán cho ví dụ 2.1

Để xác định dạng của mối quan hệ hàm số giữa các biến và xây dựng biểu đồ phân tán (Hình 2.1).

Dựa trên biểu đồ phân tán, chúng ta có thể kết luận rằng doanh thu hàng năm phụ thuộc thuận vào khu vực bán hàng (nghĩa là y sẽ tăng cùng với mức tăng trưởng của ). Phần lớn hình dạng phù hợp kết nối chức năng - tuyến tính.

Thông tin để tính toán thêm được trình bày trong Bảng. 2.2. Sử dụng phương pháp bình phương nhỏ nhất, chúng tôi ước tính các tham số của mô hình kinh tế lượng tuyến tính một yếu tố

Bảng 2.2

Bằng cách này,

Do đó, với diện tích giao dịch tăng thêm 1 nghìn m 2, với các điều kiện bình đẳng doanh thu trung bình hàng năm tăng 67,8871 triệu rúp.

Ví dụ 2.2. Ban lãnh đạo doanh nghiệp nhận thấy rằng doanh thu hàng năm không chỉ phụ thuộc vào diện tích bán hàng của cửa hàng (xem ví dụ 2.1) mà còn phụ thuộc vào lượng khách trung bình. Các thông tin liên quan được trình bày trong bảng. 2.3.

Bảng 2.3

Dung dịch. Biểu thị - số lượng khách truy cập trung bình vào cửa hàng thứ mỗi ngày, nghìn người.

Để xác định dạng của mối quan hệ hàm số giữa các biến và xây dựng biểu đồ phân tán (Hình 2.2).

Dựa trên biểu đồ phân tán, chúng ta có thể kết luận rằng doanh thu hàng năm có quan hệ tỷ lệ thuận với số lượng khách truy cập trung bình mỗi ngày (nghĩa là y sẽ tăng cùng với mức tăng của ). Dạng phụ thuộc hàm là tuyến tính.

Cơm. 2.2. Biểu đồ phân tán ví dụ 2.2

Bảng 2.4

Nhìn chung, cần xác định các tham số của mô hình kinh tế lượng hai yếu tố

y t \u003d a 0 + a 1 x 1t + a 2 x 2t + ε t

Thông tin cần thiết cho các tính toán tiếp theo được trình bày trong Bảng. 2.4.

Hãy để chúng tôi ước tính các tham số của mô hình kinh tế lượng hai yếu tố tuyến tính bằng phương pháp bình phương nhỏ nhất.

Bằng cách này,

Đánh giá hệ số = 61,6583 cho thấy, các yếu tố khác không đổi, với diện tích giao dịch tăng thêm 1 nghìn m 2, doanh thu hàng năm sẽ tăng trung bình 61,6583 triệu rúp.

Ước tính của hệ số = 2,2748 cho thấy rằng, các yếu tố khác không đổi, với sự gia tăng số lượng khách trung bình trên 1 nghìn người. mỗi ngày, doanh thu hàng năm sẽ tăng trung bình 2,2748 triệu rúp.

Ví dụ 2.3. Sử dụng thông tin trình bày trong bảng. 2.2 và 2.4, ước lượng tham số của mô hình kinh tế lượng đơn nhân tố

đâu là giá trị trung tâm của doanh thu hàng năm của cửa hàng thứ - triệu rúp; - giá trị trung tâm của số lượng khách truy cập trung bình hàng ngày vào cửa hàng thứ t, nghìn người. (xem ví dụ 2.1-2.2).

Dung dịch. thông tin thêm, cần thiết để tính toán, được trình bày trong bảng. 2.5.

Bảng 2.5

Sử dụng công thức (2.35), chúng tôi thu được

Bằng cách này,

http://www.cleverstudents.ru/articles/mnk.html

Thí 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, xấp xỉ dữ liệu này phụ thuộc tuyến tính y=ax+b(tìm thông số một và b). Tìm xem trong hai 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ử nghiệm. Vẽ tranh.

Dung dịch.

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 số tiền được bao gồm trong các công thức của các hệ số cần thiết.

Các giá trị ở hàng thứ 4 của bảng có được bằng cách nhân các giá trị của hàng thứ 2 với các 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 thu đượ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.

Các giá trị của cột cuối cùng của bảng là tổng của 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:

Do đó, y=0,165x+2,184 là đường thẳng xấp xỉ mong muốn.

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

Bằng chứng.

Vì vậy mà khi tìm thấy một và b hàm nhận giá trị nhỏ nhất, điều cần thiết là tại thời điểm này ma trận dạng bậc hai vi phân cấp hai của hàm đã được xác định tích cực. Hãy cho thấy nó.

Vi phân bậc hai có dạng:

Đó là

Do đó, ma trận của căn thức bậc hai có dạng

và giá trị của các phần tử không phụ thuộc vào một và b.

Hãy để chúng tôi chứng minh rằng ma trận là xác định dương. Điều này đòi hỏi các góc phụ phải dương.

Góc nhỏ của đơn hàng đầu tiên . Bất đẳng thức là nghiêm ngặt, vì các điểm

Thí 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, xấp xỉ những dữ liệu này với sự phụ thuộc tuyến tính y=ax+b(tìm thông số một và b). Tìm xem trong hai 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ử nghiệm. Vẽ tranh.

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

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

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

Dẫn xuất của các công thức tìm hệ số.

Một hệ thống hai phương trình với hai ẩn số được biên soạn và giải quyết. Tìm đạo hàm riêng của hàm số 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 Cramer) và thu được các công thức tìm 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 của 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 tổng ,, và tham số N- lượng dữ liệu thực nghiệm. Các giá trị của các khoản tiền này được khuyến nghị tính riêng. hệ số bđược tìm thấy sau khi tính toán một.

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

Dung dịch.

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 số tiền được bao gồm trong các công thức của các hệ số cần thiết.

Các giá trị ở hàng thứ 4 của bảng có được bằng cách nhân các giá trị của hàng thứ 2 với các 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 thu đượ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.

Các giá trị của cột cuối cùng của bảng là tổng của 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:

Do đó, y=0,165x+2,184 là đường thẳng xấp xỉ mong muốn.

Nó vẫn còn để tìm ra dòng nào y=0,165x+2,184 hoặc xấp xỉ tốt hơn dữ liệu gốc, 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 bình phương độ lệch của dữ liệu gốc 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 gốc theo phương pháp bình phương nhỏ nhất.

Vì , thì dòng y=0,165x+2,184 xấp xỉ dữ liệu ban đầu tốt hơn.

Đồ họa minh họ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. Đường màu đỏ là đường được tìm thấy y=0,165x+2,184, đường màu xanh 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 quy trình khác nhau - cụ thể là kinh tế, vật lý, kỹ thuật, xã hội - một hoặc một phương pháp khác để tính 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ề xấp xỉ các chức năng của loại này thường phát sinh:

khi xây dựng các công thức 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ả của thí nghiệm;

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

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

khi xác định 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 quy 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 quy trình này dựa trên phương pháp bình phương nhỏ nhất, thì 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ỉ sẽ tự nó là một vấ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 dạng bảng thiết lập chức năng(là cơ sở của phân tích hồi quy).

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

Thêm hồi quy đã chọn (đường xu hướng) vào biểu đồ được xây dựng trên cơ sở bảng dữ liệu cho đặc tính quy trình được nghiên cứu (chỉ khả dụng nếu biểu đồ được tạo);

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 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 bảng dữ liệu mô tả một quy trình nào đó và được biểu diễn bằng sơ đồ, 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 biểu đồ các loại hồi quy, với 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 hồi quy đã xây dựng vào sơ đồ;

xác định mức độ phù hợp 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 nhận các loại hồi quy tuyến tính, đa thức, logarit, hàm mũ, hàm mũ, đượ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 rất tốt trong việc mô hình hóa các tính năng 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 quá 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 của hồi quy tuyến tính với 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 rất hữu ích để mô tả các đặc điểm có một số điểm cực đoan riêng biệt (mức cao và mức thấp). Việc 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 bốn - 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ố có giá trị được xác định trong quá trình thi công.

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

y = c ln(x) + b

4 . Đường xu hướng năng lượng 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 về tốc độ tăng trưởng. Một ví dụ về sự phụ thuộc như vậy có thể đóng vai trò là đồ thị chuyển động có gia tốc đều của ô tô. Nếu có giá trị bằng 0 hoặc âm trong dữ liệu, bạn không thể sử dụng đường xu hướng lũy ​​thừa.

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 chứa giá trị 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 dòng xu hướng excel tự động tính toán giá trị của R2, đặc trưng cho độ chính xác của phép tính gần đúng: giá trị của R2 càng gần với sự thống nhất, thì đường xu hướng xấp xỉ quá trình đang nghiên cứu càng đáng tin cậy. Nếu cần, giá trị của R2 luôn có thể được hiển thị trên sơ đồ.

Xác định theo công thức:

Để thêm một đường xu hướng vào một 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 khu vực 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ũng dễ dàng thực hiện 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, 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 loại Đa thức, trong trường Độ, chỉ định bậc của đa thức đã chọn.

1 . Trường Sê-ri tích hợp liệt kê tất cả các sê-ri dữ liệu trong biểu đồ được đề cập. Để thêm một đườ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 Chuỗi được tạo sẵn.

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 trường Tên của đườ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 xấp xỉ R2 trong khu vực sơ đồ mà bạn nên bật hộp kiểm để đặt giá trị của độ tin cậy xấp xỉ (R^2) trên sơ đồ;

đặt giao điểm của đường xu hướng với trục Y, tại đó 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ừ menu ngữ cảnh, đượ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 Định dạng Đường xu hướng sẽ xuất hiện trên màn hình (Hình 3), chứa ba tab: Xem, Loại, 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 Dạng xem, bạn có thể đặt loại đường kẻ, màu sắc và độ dày của đường kẻ.

Để xóa một đường xu hướng đã được tạo, 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 xem xét là:

tương đối dễ dàng 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 phạm vi ý 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, nếu cần thiết, để có được đánh giá về độ tin cậy của xấp xỉ.

Những nhược điểm bao gồm các điểm sau:

việc xây dựng một đườ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 loạt dữ liệu;

quá trình tạo chuỗi dữ liệu cho đặc tính đang nghiên cứu dựa trên các phương trình đường xu hướng thu được cho nó hơi lộn xộn: các phương trình hồi quy mong muốn được cập nhật với mỗi thay đổi về giá trị của chuỗi dữ liệu gốc, nhưng chỉ trong khu vực 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ũ, 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 giữ lại, 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ẽ các đường xu hướng hoặc định dạng khác cho 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 các biểu đồ, chẳng hạn như biểu đồ, biểu đồ tần suất, biểu đồ vùng phẳng không chuẩn hóa, biểu đồ thanh, phân tán, bong bóng và chứng khoán.

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

Sử dụng tích hợp hàm excel

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 khu vực 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ỉ xây dựng 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;

• DỐC và CẮT.

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

LGRFPkhoảng.

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ế giống nhau. Điều tương tự cũng có thể nói về cặp chức năng 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 của Excel như công thức mảng được sử dụng, điều này phần nào làm lộn xộn quá trình xây dựng hồi quy. Chúng tôi cũng lưu ý rằng theo chúng tôi, việc xây dựng hồi quy tuyến tính 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ụ hàm tích hợp để phân tích hồi quy là:

một quy 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 tính đang nghiên cứu cho tất cả các chức năng thống kê tích hợp 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 được tạo;

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

Và những nhược điểm 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ũ). Hoàn cảnh 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ư thu được các 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 sẽ 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 thiện 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ác ví dụ cụ thể; chứng minh những công cụ hiệu quả mà Excel có để xây dựng hồi quy và dự báo; minh họa các vấn đề như vậy có thể được giải quyết tương đối dễ dàng như thế nào ngay cả đối với 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 cho các vấn đề cụ thể bằng cách sử dụng các công cụ được liệt kê trong gói Excel.

Nhiệm vụ 1

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

Xây dựng một 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 các 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 đối với 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 bảng tính Excel, chúng tôi nhập bảng tính như trong Hình. bốn.

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

Chúng tôi kích hoạt biểu đồ đã xây dựng và sử dụng 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 trường Tên của đường cong gần đúng (được làm nhẵn), nhập tên của xu hướng sẽ được thêm vào và trong trường Dự báo chuyển tiếp cho: thời kỳ, hãy đặt giá trị 2, vì nó được lên kế hoạch 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ị của độ tin cậy xấp xỉ R2 trong khu vực 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ị của độ tin cậy xấp xỉ (R^2) trên sơ đồ. Để có 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 được vẽ, chúng tôi sử dụng tab Chế độ xem 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 thêm vào đượ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 đối với từng đường xu hướng trong giai đoạn 1995-2004. Hãy sử dụng các 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 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 dấu tô, 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. Vì vậy, một dự báo đã được thực hiện cho lợi nhuận của doanh nghiệp cho năm 2003 và 2004. 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 một biểu đồ.

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

Rút ra các 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 xu hướng.

Sử dụng các 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 đối với 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 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 trong việc giải quyết vấn đề 1, chúng tôi 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 xí nghiệp vận tải cơ giới 1995-2002 đã cho ở task 1, các em 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 XU HƯỚNG và TĂNG TRƯỞNG, hãy 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 sơ đồ.

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

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

chọn phạm vi ô D4:D11, cần đ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 hàm xuất hiện, chọn hàm XU HƯỚNG từ danh mục Thống kê, sau đó nhấp 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, hãy nhập phạm vi ô C4:C11 vào trường Các_giá_trị_biết; trong trường known_values_x - 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 trong thanh công thức sẽ có dạng: =(TREND(C4:C11;B4:B11)).

Do đó, 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).

Dự báo 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 vào.

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 known_values_y - phạm vi ô C4:C11; trong trường known_values_x - 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 phím tắt Ctrl + Shift + Enter.

Công thức đã nhập sẽ có dạng: =(TREND(C4:C11;B4:B11;B12:B13)) và phạm vi ô D12:D13 sẽ được lấp đầy bằng 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 chính xác giống như TREND đối ứng tuyến tính của nó.

Hình 10 hiển thị 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ơ đồ hiển thị trong hình. mười một.

nhiệm vụ 4

Với bảng dữ liệu về việc nhận đơn đăng ký dịch vụ của dịch vụ điều độ của doanh nghiệp vận tải cơ giới trong khoảng thời gian từ ngày 1 đế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 hàm SLOPE và INTERCEPT; sử dụng chức năng LINEST.

Truy xuất chuỗi dữ liệu cho hồi quy hàm mũ bằng hàm LYFFPRIB.

Sử dụng các chức năng trên, lập dự báo về việc tiếp nhận hồ sơ cho 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 ban đầu và nhận được, hãy xây dựng 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àm 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á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 đến, ngược lại 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 hệ số góc của hồi quy m được xác định bởi hàm SLOPE và hằng số 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 vào trường giá_trị_đã_biết và phạm vi ô A4:A14 vào trường giá_trị_x_biết. Công thức sẽ được nhập vào ô C19: =SLOPE(B4:B14;A4:A14);

sử dụng một 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ư sau: = INTERCEPT(B4:B14;A4:A14). Do đó, các giá trị của các tham số m và b, cần thiết để xây dựng hồi quy tuyến tính, sẽ được lưu trữ tương ứng trong các ô C19, D19;

thì ta nhập công thức hồi quy tuyến tính vào ô C4 theo dạng: =$C*A4+$D. Trong công thức này, các ô C19 và D19 được viết với các 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 núm điều khiển điền, sao chép công thức này vào phạm vi ô C4:C17. Ta đượ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ờ hãy xây dựng một hồi quy tuyến tính được đưa ra bởi phương trình:

y=mx+b

sử dụng chức năng 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 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 chức năng LGRFPRIBL, nó được thực hiện tương tự:

trong phạm vi ô C21:D21, hãy 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 chuỗi dữ liệu cho hồi quy hàm mũ sẽ được định vị (xem Hình 12).

Trên hình. Hình 13 hiển thị một bảng nơi chúng ta có thể thấy 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 gọi là hệ số xác định.

Nhiệm vụ của việc xây dựng 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 đạt giá trị lớn nhất.

Để đánh giá mức độ quan trọng của R, F-test của Fisher được sử dụng, được tính theo công thức

ở đâu N- cỡ mẫu (số 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, thì giá trị của R được coi là đáng kể. Bảng giá trị tới hạn của F được cho trong các sách tham khảo về thống kê toán học.

Do đó, tầm quan trọng của R không chỉ được xác định bởi giá trị của nó mà còn bởi tỷ lệ 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ử nghiệm là các biến ngẫu nhiên, thì giá trị R như vậy phải được tin cậy hết sức cẩn thận. Thông thường, để có được R đáng kể và hồi quy đáng tin cậy, nó nhằm mục đích đảm bảo rằng số lượng thí nghiệm vượt quá đáng kể số lượng hệ số mô hình (n>k).

Để xây dựng tuyến tính mô hình hồi quy cần thiết:

1) chuẩn bị danh sách 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ụ: hãy lấy dữ liệu của tác vụ trước, thêm một cột có tên là "số kỳ", đánh số các số kỳ 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 mục "Phân tích dữ liệu" trong menu "Công cụ" bị thiếu, thì bạn nên chuyển đến mục "Phần bổ sung" của cùng một menu và chọn hộp "Gói phân tích".

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

khoảng đầ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 sẽ đặt kết quả tính toán (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ả.

Bản chất của phương pháp bình phương nhỏ nhất là trong việc tìm ra các tham số của một mô hình xu hướng mô tả đúng nhất xu hướng phát triển của một hiện tượng ngẫu nhiên nào đó trong thời gian hoặc không gian (xu hướng là đường đặc trưng cho xu hướng phát triển đó). Nhiệm vụ của phương pháp bình phương nhỏ nhất (OLS) là tìm không chỉ một số mô hình xu hướng mà còn tìm ra mô hình tốt nhất hoặc mô hình tối ưu. Mô hình này sẽ tối ưu nếu tổng độ lệch chuẩn giữa các giá trị thực tế quan sát được và các giá trị tính toán tương ứng của xu hướng sẽ là giá trị cực tiểu (nhỏ nhất):

ở đâu - độ lệch chuẩn giữa giá trị thực quan sát được

và giá trị xu hướng tính toán tương ứng,

Giá trị thực tế (quan sát được) của hiện tượng đang nghiên cứu,

Giá trị ước tính của mô hình xu hướng,

Số lần quan sát hiện tượng đang nghiên cứu.

MNC hiếm khi được sử dụng riêng lẻ. Theo quy định, nó thường chỉ được sử dụng như một kỹ thuật cần thiết trong các nghiên cứu tương quan. Cần nhớ rằng cơ sở thông tin MNC chỉ có thể đáng tin cậy chuỗi thống kê và số lượng quan sát không được ít hơn 4, nếu không, các thủ tục làm mịn LSM có thể mất đi ý nghĩa thông thường.

Bộ công cụ OLS được rút gọn thành các thủ tục sau:

Thủ tục đầu tiên. Nó cho biết liệu có bất kỳ xu hướng nào thay đổi thuộc tính kết quả hay không khi đối số nhân tố được chọn thay đổi, hay nói cách khác, liệu có mối liên hệ nào giữa " tại " và " X ».

Thủ tục thứ hai. Nó được xác định đường (quỹ đạo) nào có thể mô tả hoặc mô tả xu hướng này tốt nhất.

Thủ tục thứ ba.

Thí dụ. Giả sử chúng ta có thông tin về năng suất hoa hướng dương trung bình của trang trại đang nghiên cứu (Bảng 9.1).

Bảng 9.1

Do trình độ công nghệ sản xuất hướng dương ở nước ta không thay đổi nhiều trong hơn 10 năm qua, nên rất có thể biến động về năng suất trong giai đoạn phân tích phụ thuộc rất nhiều vào biến động của điều kiện thời tiết, khí hậu. Có thật không?

Thủ tục MNC đầu tiên. Giả thuyết về sự tồn tại của xu hướng thay đổi năng suất hoa hướng dương tùy thuộc vào sự thay đổi của điều kiện thời tiết và khí hậu trong 10 năm được phân tích đang được thử nghiệm.

TẠI ví dụ này mỗi " y » nên lấy sản lượng hướng dương, và cho « x » là số năm quan trắc trong kỳ phân tích. Kiểm định giả thuyết về sự tồn tại của bất kỳ mối quan hệ nào giữa " x " và " y » có thể được thực hiện theo hai cách: thủ công và sử dụng chương trình máy tính. Tất nhiên, nếu có công nghệ máy tính vấn đề này tự giải quyết. Tuy nhiên, để hiểu rõ hơn về bộ công cụ OLS, nên kiểm tra giả thuyết về sự tồn tại của mối quan hệ giữa " x " và " y » thủ công, khi chỉ có cây bút và máy tính thông thường. Trong những trường hợp như vậy, giả thuyết về sự tồn tại của một xu hướng được kiểm tra tốt nhất một cách trực quan theo vị trí hình ảnh đồ họa của chuỗi động lực học được phân tích - trường tương quan:

Trường tương quan trong ví dụ của chúng tôi nằm xung quanh một đường tăng dần. Bản thân điều này cho thấy sự tồn tại của một xu hướng nhất định trong sự thay đổi năng suất hoa hướng dương. Không thể chỉ nói về sự hiện diện của bất kỳ xu hướng nào khi trường tương quan trông giống như một vòng tròn, một vòng tròn, một đám mây thẳng đứng hoặc nằm ngang nghiêm ngặt hoặc bao gồm các điểm phân tán ngẫu nhiên. Trong mọi trường hợp khác, cần khẳng định giả thuyết về sự tồn tại của mối quan hệ giữa " x " và " y và tiếp tục nghiên cứu.

Thủ tục MNC thứ hai. Nó được xác định đường (quỹ đạo) nào có thể mô tả hoặc mô tả rõ nhất xu hướng thay đổi năng suất hoa hướng dương trong giai đoạn được phân tích.

Với sự sẵn có của công nghệ máy tính, việc lựa chọn xu hướng tối ưu diễn ra tự động. Với quá trình xử lý "thủ công", việc lựa chọn chức năng tối ưu được thực hiện, theo quy luật, theo cách trực quan - theo vị trí của trường tương quan. Đó là, theo loại biểu đồ, phương trình của đường được chọn, phù hợp nhất với xu hướng thực nghiệm (với quỹ đạo thực tế).

Như bạn đã biết, trong tự nhiên có rất nhiều loại phụ thuộc chức năng, do đó, việc phân tích trực quan dù chỉ một phần nhỏ trong số chúng là vô cùng khó khăn. May mắn thay, trong thực tế kinh tế, hầu hết các mối quan hệ có thể được mô tả chính xác bằng parabol, hyperbola hoặc đường thẳng. Về vấn đề này, với tùy chọn "thủ công" để chọn chức năng tốt nhất, bạn chỉ có thể giới hạn bản thân trong ba kiểu máy này.

Parabol bậc hai: :

Dễ dàng nhận thấy rằng trong ví dụ của chúng ta, xu hướng thay đổi năng suất hoa hướng dương trong 10 năm được phân tích được đặc trưng tốt nhất bởi một đường thẳng, vì vậy phương trình hồi quy sẽ là một phương trình đường thẳng.

Thủ tục thứ ba. Các thông số được tính toán phương trình hồi quyđặc trưng cho dòng này, hay nói cách khác, một công thức phân tích được xác định mô tả Mô hình tốt nhất xu hướng.

Tìm giá trị của các tham số của phương trình hồi quy, trong trường hợp của chúng tôi, các tham số và , là cốt lõi của LSM. Quá trình này được giảm xuống để giải quyết hệ thống phương trình bình thường.

(9.2)

Hệ phương trình này được giải khá dễ dàng bằng phương pháp Gauss. Nhớ lại rằng kết quả của giải pháp, trong ví dụ của chúng tôi, các giá trị của các tham số và được tìm thấy. Như vậy, phương trình hồi quy tìm được sẽ có dạng như sau:

Không tìm thấy câu trả lời cho câu hỏi của bạn? Nhìn vào đây

Nhà nguyện như một nguyên mẫu của báo chí

Những câu trích dẫn về Napoléon - dslinkov — LiveJournal

Tôi báo thù Người đàn ông trên chiếc máy ủi đã phá hủy thành phố