Phương pháp tính hệ số cho phương trình bậc hai cho người học excel cơ bản (1 người xem)

[nguyễn thanh tùng3288]

Xin chào các bạn! tôi có vướng mắc một vấn đề về cách tính phương trình bậc hai, tôi có 3 cặp tọa độ X,Y và cần tìm số của a, b, c.
Ví dụ: X1=300, Y1=5
X2=330, Y2=8
X3=410, Y3=12
tôi cần tìm X4 tại Y=10.
nếu vẽ đồ thị trên excel và hiển thị công thức đồ thị tôi vẫn có được các số liệu a, b, c cần tìm và nhập lại công thức tính sẽ tìm được X4, nhưng do file làm việc giải quyết rất nhiều bài toán trên nên nhập lại công thức sẽ rất mất thời gian, xin nhờ các bạn hướng dẫn tôi cách tính toán.
Xin chân thành cám ơn!

 

[Phuocam]

Xin chào các bạn! tôi có vướng mắc một vấn đề về cách tính phương trình bậc hai, tôi có 3 cặp tọa độ X,Y và cần tìm số của a, b, c.
Ví dụ: X1=300, Y1=5
X2=330, Y2=8
X3=410, Y3=12
tôi cần tìm X4 tại Y=10.
nếu vẽ đồ thị trên excel và hiển thị công thức đồ thị tôi vẫn có được các số liệu a, b, c cần tìm và nhập lại công thức tính sẽ tìm được X4, nhưng do file làm việc giải quyết rất nhiều bài toán trên nên nhập lại công thức sẽ rất mất thời gian, xin nhờ các bạn hướng dẫn tôi cách tính toán.
Xin chân thành cám ơn!

Bạn có thể giải thích cách tính chi tiết để có được X4 không?

 

[VetMini]

Bạn có thể giải thích cách tính chi tiết để có được X4 không?

ax2 + bx + c = y
(1) (300*300)a + 300b + c = 5
(2) (330*330)a + 330b + c = 8
(3) (410*410)a + 410b + c = 12
Giải hệ thống trên. Tìm ra a,b,c. Tuy nhiên tôi chưa chắc giải được.

Nếu dùng VBA thì dùng hàm Applicxation.LinEst( mảng y, Application.Power( mảng x, Array(1, 2)) )
Cái array cuói cùng xác định rằng ta muốn dùng bậc 2 để tính thông số a, b, c

 

[HieuCD]

Xin chào các bạn! tôi có vướng mắc một vấn đề về cách tính phương trình bậc hai, tôi có 3 cặp tọa độ X,Y và cần tìm số của a, b, c.
Ví dụ: X1=300, Y1=5
X2=330, Y2=8
X3=410, Y3=12
tôi cần tìm X4 tại Y=10.
nếu vẽ đồ thị trên excel và hiển thị công thức đồ thị tôi vẫn có được các số liệu a, b, c cần tìm và nhập lại công thức tính sẽ tìm được X4, nhưng do file làm việc giải quyết rất nhiều bài toán trên nên nhập lại công thức sẽ rất mất thời gian, xin nhờ các bạn hướng dẫn tôi cách tính toán.
Xin chân thành cám ơn!

Theo hướng dẫn của bạn @VetMini tìm các tham số a, b, c. Tính X bạn tự làm
Chọn 3 ô ở 3 dòng, nhập

=MMULT(MINVERSE(B2:D4),E2:E4)

Ctrl+Shift+Enter
Xem cách làm cụ thể trong file

 

[ptm0412]

Hàm Linest hồi quy bậc 2

 

[Binbo2020]

Nhiều hàm lạ quá nhỉ lần đầu nhìn thấy, làm kiểu toán học thuần cho dễ đọc vậy

 

[ptm0412]

Nhiều hàm lạ quá nhỉ lần đầu nhìn thấy, làm kiểu toán học thuần cho dễ đọc vậy

Những hàm lạ lạ này Excel viết ra để thay thế cho việc giải bằng toán học thuần. Cụ thể là hàm Linest áp dụng phương pháp bình phương bé nhất của toán học để hồi quy, không phải dùng đến bút chì và giấy.

 

[ptm0412]

Nói thêm: Hàm Linest có thể dùng để hồi quy nhiều dạng:
- Linear (tuyến tính) ax + b
- Exponential (a.e^bx)
- Polynomial (đa thức bậc n) ax^n + bx^(n-1) + ... z
- Logarithmic a.ln(x) +b
- Power a.x^b

 

[nguyễn thanh tùng3288]

Hàm Linest hồi quy bậc 2

View attachment 259895

bạn có thể cho mình xin file excel không ạ? xin cám ơn!

 

[nguyễn thanh tùng3288]

Đây là file excel mình đang làm, có trình bày chi tiết vướn mắc, nhờ các bạn giúp đỡ ạ!!!

 

[Binbo2020]

Đây là file excel mình đang làm, có trình bày chi tiết vướn mắc, nhờ các bạn giúp đỡ ạ!!!

Cái này thì công thức bài #4 #5 đều làm được rồi mà, sao lại hỏi lại nhỉ

 

[nguyễn thanh tùng3288]

Cái này thì công thức bài #4 #5 đều làm được rồi mà, sao lại hỏi lại nhỉ

Dạ mình đã làm rồi nhưng không được nên up file lên đây nhờ các bạn giúp ạ!

 

[ptm0412]

Dạ mình đã làm rồi nhưng không được nên up file lên đây nhờ các bạn giúp ạ!

Công thức rõ ràng trong hình chụp rồi, máy của tôi là Excel 365 nên viết trong 1 ô, nếu không phải 365 thì tô khối 3 ô theo hàng ngang, gõ công thức rồi Ctrl Shift Enter

 

[nguyễn thanh tùng3288]

Công thức rõ ràng trong hình chụp rồi, máy của tôi là Excel 365 nên viết trong 1 ô, nếu không phải 365 thì tô khối 3 ô theo hàng ngang, gõ công thức rồi Ctrl Shift Enter

Dạ xin cám ơn ạ

 

[VetMini]

Công thức rõ ràng trong hình chụp rồi, máy của tôi là Excel 365 nên viết trong 1 ô, nếu không phải 365 thì tô khối 3 ô theo hàng ngang, gõ công thức rồi Ctrl Shift Enter

Nếu không muốn nó gộp 3 ô lại thành mảng thì dùng hàm index. Tính lâu hơn (3 lần) nhưng tách rời được.
a = Index(công thức, 1)
b = Index(công thức, 2)
c = Index(công thức, 3)

 

[ptm0412]

Theo nội dung trong file, dùng hàm Linest tôi ra kết quả ở 3 ô O7:Q7 như hình. Kết quả tính toán lại chính xác ở 3 ô R4:R6
Còn Trend line equation của bạn ra kết quả khác, và khi kiểm tra kết quả y = ax^2 + bx + c thì ra kết quả không giống.
Lý do là bạn đảo ngược trục X, Y của đồ thị




Nếu đúng X là X và Y là Y thì so khớp equation và linest:

 

[nguyễn thanh tùng3288]

Nếu không muốn nó gộp 3 ô lại thành mảng thì dùng hàm index. Tính lâu hơn (3 lần) nhưng tách rời được.
a = Index(công thức, 1)
b = Index(công thức, 2)
c = Index(công thức, 3)

Dạ xin cảm ơn ạ

 

[Phuocam]

Thử dùng hàm FORECAST thì kết quả gần giống với kết quả mong muốn của bạn:

=FORECAST(O15,P4:P6,O4:O6)

 

[nguyễn thanh tùng3288]

Thử dùng hàm FORECAST thì kết quả gần giống với kết quả mong muốn của bạn:

=FORECAST(O15,P4:P6,O4:O6)

Dạ cảm ơn bạn, mình đã dùng và trả kết quả không chính xác ạ

 

[ptm0412]

Thử dùng hàm FORECAST thì kết quả gần giống với kết quả mong muốn của bạn:

=FORECAST(O15,P4:P6,O4:O6)

Hàm Forecast là dự báo theo hồi quy tuyến tính (bậc 1), không phải dự báo theo hồi quy đa thức bậc 2

 

[ptm0412]

Cách dùng hàm Linest cho 5 loại hồi quy

 

[HieuCD]

Cách dùng hàm Linest cho 5 loại hồi quy

View attachment 260004

Với 2 dạng dưới công thức sẽ như thế nào bạn ?
a*x^b+c
a*e^bx +c

 

[ptm0412]

Với 2 dạng dưới công thức sẽ như thế nào bạn ?
a*x^b+c
a*e^bx +c

Nói thực lòng là tôi không biết. Cách dùng trong bài 21 tôi đọc tài liệu của Microsoft và chỉ có vậy.
Kể cả 2 tham số (3 và 4) của hàm Linest tôi cũng chưa thử dùng vì chưa hiểu ý nghĩa của nó. Vời mức độ sử dụng ở bài 21, tôi đã đối chiếu so khớp với equation của trend line của biểu đồ

 

[HieuCD]

Cách dùng hàm Linest cho 5 loại hồi quy

View attachment 260004

Hồi quy phi tuyến dùng phương pháp đổi biến số trở thành hồi quy tuyến tính nhằm đơn giản cách tính các tham số, nhưng có nhược điểm là độ tin cậy không cao so với cách điều tiết trực tiếp từ đường hồi quy gốc. Trong file minh họa 1 ví vụ về sai số của hàm Linest khá lớn so với cách dùng công cụ Solver

 

[nguyễn thanh tùng3288]

Cám ơn các bạn đã giúp đỡ tôi trong bài viết này, tôi không phải là người giỏi về excel nên không hiểu nhiều những cái các bạn nói, chỗ ptm0412 đã chỉ ra tôi nhầm trục tọa độ, nay tôi xin up lại file lần nữa ở hai dạng trục tọa độ và xin nhờ các bạn chỉnh trực tiếp trên file vì tôi đã tự làm tối qua đến nay vẫn không ra kết quả! xin chân thành cảm ơn!

 

[ptm0412]

Khi dùng

Hồi quy phi tuyến dùng phương pháp đổi biến số trở thành hồi quy tuyến tính nhằm đơn giản cách tính các tham số, nhưng có nhược điểm là độ tin cậy không cao so với cách điều tiết trực tiếp từ đường hồi quy gốc. Trong file minh họa 1 ví vụ về sai số của hàm Linest khá lớn so với cách dùng công cụ Solver

Khi dùng Linest (vẽ trend line trên đồ thị), cần phân tích dữ liệu, chọn loại hồi quy, thử và chọn lại mới tìm được dạng hồi quy gần đúng nhất. Ví dụ 2 biểu đồ trong file của anh, chọn hồi quy đa thức bậc 2 sẽ gần đúng nhất, bình phương độ chặt (R- squared) gần bằng 1).

Bài đã được tự động gộp: 4/6/21

chỗ ptm0412 đã chỉ ra tôi nhầm trục tọa độ, nay tôi xin up lại file lần nữa ở hai dạng trục tọa độ

O9 =INDEX(LINEST($P$4:$P$6,$O$4:$O$6^{1,2}),ROW(A1))
Copy xuống O10 và O11
(Hai sheet công thức y hệt nhau, tuy nhiên sheet có hình 2 truy xuất đúng giá trị khi X = 6, Y = 1.35, sheet 1 phải truy xuất ngược Y = 6 ra X và phải giải phương trình)

 

[HieuCD]

Khi dùng

Khi dùng Linest (vẽ trend line trên đồ thị), cần phân tích dữ liệu, chọn loại hồi quy, thử và chọn lại mới tìm được dạng hồi quy gần đúng nhất. Ví dụ 2 biểu đồ trong file của anh, chọn hồi quy đa thức bậc 2 sẽ gần đúng nhất, bình phương độ chặt (R- squared) gần bằng 1).

View attachment 260031

Bài đã được tự động gộp: 4/6/21

O9 =INDEX(LINEST($P$4:$P$6,$O$4:$O$6^{1,2}),ROW(A1))
Copy xuống O10 và O11
(Hai sheet công thức y hệt nhau, tuy nhiên sheet có hình 2 truy xuất đúng giá trị khi X = 6, Y = 1.35, sheet 1 phải truy xuất ngược Y = 6 ra X và phải giải phương trình)

View attachment 260034

View attachment 260035

Mình chỉ đưa ví dụ hạn chế của hàm Linest khi tính các tham số của hồi quy phi tuyến, chưa xét dạng nào phù hợp nhất vì dữ liệu chỉ có 3 dòng, đường bậc 2 chắc chắn có R=1

 

[ptm0412]

Mình chỉ đưa ví dụ hạn chế của hàm Linest khi tính các tham số của hồi quy phi tuyến, chưa xét dạng nào phù hợp nhất vì dữ liệu chỉ có 3 dòng, đường bậc 2 chắc chắn có R=1

Tôi xem kỹ thì đúng là trường hợp chỉ có 3 dòng, Linest có sai số lớn hơn solver
Tuy nhiên biểu đồ anh vẽ có chỗ sai: giá trị point thứ 3 đang có giá trị (4, 49.93), giá trị đúng là (3, 49.93)



Sửa lại thì solver khép kín Y hơn là Linest



Ngoài ra tôi còn test thử trường hợp 6 dòng dữ liệu, và sự biến thiên của dữ liệu gần với hàm số mũ hơn, để khi Linest Power (hoặc chọn trend line của biểu đồ), phù hợp với dữ liệu hơn. Sau khi test thì dù cho tổng bình phương sai số 2 phương pháp khác nhau nhiều, nhưng cả 2 đều khép sát đường dữ liệu gốc, trừ point cuối.

 

[HieuCD]

Tôi xem kỹ thì đúng là trường hợp chỉ có 3 dòng, Linest có sai số lớn hơn solver
Tuy nhiên biểu đồ anh vẽ có chỗ sai: giá trị point thứ 3 đang có giá trị (4, 49.93), giá trị đúng là (3, 49.93)

View attachment 260063

Sửa lại thì solver khép kín Y hơn là Linest

View attachment 260064

Ngoài ra tôi còn test thử trường hợp 6 dòng dữ liệu, và sự biến thiên của dữ liệu gần với hàm số mũ hơn, để khi Linest Power (hoặc chọn trend line của biểu đồ), phù hợp với dữ liệu hơn. Sau khi test thì dù cho tổng bình phương sai số 2 phương pháp khác nhau nhiều, nhưng cả 2 đều khép sát đường dữ liệu gốc, trừ point cuối.

View attachment 260065

Kiểm tra lại, dùng Linest điều tiết đường hồi quy phải đổi biến Y bằng hàm log như a.x^b và a.e^bx có các tham số hồi quy không chính xác và độ tin cậy đường hồi quy không cao, các đường hồi quy còn lại đường hồi quy chính xác

 

[ptm0412]

Kiểm tra lại, dùng Linest điều tiết đường hồi quy phải đổi biến Y bằng hàm log như a.x^b và a.e^bx có các tham số hồi quy không chính xác và độ tin cậy đường hồi quy không cao, các đường hồi quy còn lại đường hồi quy chính xác

Nếu để so sánh Linest với solver thì: Linest tính toán bằng phương pháp tính bình phương bé nhất (hoặc phương pháp gì khác và có lẽ chỉ tính 1 lần), còn solver là cách tính lặp cho đến khi sai số nhỏ nhất, nên chắc chắn solver phải chính xác hơn Linest

 

[HieuCD]

Nếu để so sánh Linest với solver thì: Linest tính toán bằng phương pháp tính bình phương bé nhất (hoặc phương pháp gì khác và có lẽ chỉ tính 1 lần), còn solver là cách tính lặp cho đến khi sai số nhỏ nhất, nên chắc chắn solver phải chính xác hơn Linest

Hiện nay theo mình biết có 2 phương pháp điều tiết đường hồi quy là phương pháp bình phương bé nhất (bình phương sai số bé nhất) và phương pháp Newton, trong đó phương pháp bình phương bé nhất dể hơn và được sử dụng phổ biến, nó lập hệ phương trình chuẩn tắc tìm tham số hồi quy qua việc tìm cực trị bằng cách cho đạo hàm bậc 1 của từng tham số = 0
Hàm Linest và Solve mình lập đều dựa trên bình phương sai số bé nhất, Linest giải trực tiếp dựa trên hệ phương trình chuẩn tắc nên chính xác tuyệt đối với điều kiện không đổi biến Y để lập hệ phương trình chuẩn tắc, Solver là phương pháp tìm gần đúng nên có sai số cho phép nhất định
Phương pháp bình phương sai số bé nhất có nhược điểm là bình phương sai số sẽ khuếch đại sai số, để tìm chính xác sai số phải dựa vào tổng chênh lệch tuyệt đối biến Y của đường hồi quy và giá trị thực, cách nầy không tìm được hệ phương trình chuẩn tắc nên không được áp dụng