Hàm LOGEST()

Dựa vào bảng minh họa cho biết thứ tự thống kê hồi quy phụ trả về, suy ra được các trị m1, m2 và b như ở các ô E15:F17.

Áp dụng phương trình của đường cong trong hồi quy tuyến tính bội, với x1 = 12 và x2 = 25, bằng công thức tại ô A13:

A13 = F17 * (F16^B13) * (F15^C13) = 279.720291 ≈ 280

Vậy khi x1 = 12 và x2 = 25 thì có thể dự báo được y = 280

Hàm LOGEST()



Trong phân tính thống kê, LOGEST tính đường cong hàm mũ phù hợp với dữ liệu được cung cấp, rồi trả về một mảng các giá trị mô tả đường cong đó. Do kết quả trả về là một mảng, nên LOGEST() thường được nhập với dạng công thức mảng.

Phương trình của đường cong trong hồi quy tuyến tính đơn là:

Phương trình của đường cong trong hồi quy tuyến tính bội là:

Trong đó, trị phụ thuộc y là hàm của các trị độc lập x, các trị m là các hệ số tương ứng với mỗi giá trị x, và b là hằng số (const). Nhớ rằng y, x, m cũng có thể là các vectơ. Mảng mà LOGEST() trả về là:


Cú pháp: = LOGEST(known_y's, known_x's, const, stats)

Known_y's : Một tập hợp các giá trị y đã biết, trong mối quan hệ y = b*m^x.

- Nếu mảng known_y's nằm trong một cột, thì mỗi cột của known_x's được hiểu như là một biến độc lập.

- Nếu mảng known_y's nằm trong một dòng, thì mỗi dòng của known_x's được hiểu như là một biến độc lập.


Known_x's : Một tập hợp tùy chọn các giá trị x đã biết, trong mối quan hệ y = b*m^x.

- Mảng known_x's có thể bao gồm một hay nhiều biến. Nếu chỉ một biến được sử dụng, known_x's và known_y's có thể có hình dạng bất kỳ, miễn là chúng có kích thước bằng nhau. Nếu có nhiều biến được sử dụng, known_y's phải là một vectơ (là một dãy, với chiều cao là một dòng, hay với độ rộng là một cột)

- Nếu bỏ qua known_x's, known_x's sẽ được giả sử là một mảng {1, 2, 3, ...} với kích thước bằng với known_y's.


Const : Là một giá trị logic cho biết có nên cho hằng số b bằng 1 hay không

- Nếu const là TRUE (1) hoặc bỏ qua, b được tính bình thường.

- Nếu const là FALSE (0), b được gán bằng 0, và các giá trị m sẽ được điều chỉnh để y = m^x.


Stats : Là một giá trị logic cho biết có trả về thống kê hồi quy phụ hay không

- Nếu stats là FALSE (0) hoặc bỏ qua, LOGEST() chỉ trả về các hệ số m và hằng số b.

- Nếu stats là TRUE (1), LOGEST() trả về thống kê hồi quy phụ, và mảng được trả về sẽ có dạng:

Thống kê hồi quy phụ như sau:

Bảng minh họa sau đây cho biết thứ tự thống kê hồi quy phụ trả về:

Lưu ý:

* Đồ thị dữ liệu càng giống đường cong hàm mũ, đường tính được càng giống với dữ liệu. Như hàm LINEST(), hàm LOGEST cũng trả về một mảng các giá trị để mô tả mối quan hệ giữa các giá trị đó; sự khác biệt giữa hai hàm này là, LINEST() dùng cho đường thẳng, còn LOGEST() dùng cho đường cong hàm mũ.

* Khi chỉ có một biến độc lập x, có thể tìm hệ số góc m và trị b trên trục y (tung độ) một cách trực tiếp bằng cách dùng các công thức sau đây:

Hệ số góc m: = INDEX(LOGEST(known_y's, known_x's), 1)

Điểm cắt (hay tung độ) b: = INDEX(LOGEST(known_y's, known_x's), 2)

Cũng có thể dùng phương trình y = b*m^x để dự đoán giá trị tương lai của y, tuy nhiên Excel đã cung cấp hàm GROWTH() để làm điều này rồi.

* Khi nhập hằng mảng cho đối số, như known_y's chẳng hạn, dùng dấu phẩy để phân cách các trị trên cùng một dòng, và dấu chấm phẩy để phân cách các dòng khác nhau. Nhưng cần chú ý là các ký tự phân cách (dấu phẩy và dấu chấm phẩy) còn tùy thuộc vào các thiết lập trong hệ thống bạn đang sử dụng (các thiết lập cho List seperator trong Customize Regional Opitions của Control Panel).

* Chú ý rằng các trị y dự đoán được từ phương trình hồi quy có thể không đúng nếu vượt ra ngoài dãy giá trị dùng để xác định hàm.

* Các phương pháp kiểm tra phương trình bằng LOGEST() cũng tương tự như các phương pháp dùng cho LINEST(). Tuy nhiên, thống kê mà LOGEST() trả về lại dựa vào mô hình tuyến tính sau:

Nên nhớ điều này khi tính toán các thống kê hồi quy phụ, đặc biệt là các trị sei và seb, vì chúng được so sánh với ln mi và ln b, chứ không phải là so sánh với mi và b.


Ví dụ:

Có một bảng dữ liệu sau. Với số liệu này, dự báo giá trị y khi x1 = 12 và x2 = 25 ?

Ở đây giả sử các đại lượng y, x1 và x2 có mối quan hệ hàm mũ với nhau:

Cách giải:

Chọn khối cell A15:C19, gõ công thức mảng:

= LOGEST(A2:A12, B2:C12, 1, 1)

Ta sẽ có kết quả như hình sau:

Dựa vào bảng minh họa cho biết thứ tự thống kê hồi quy phụ trả về, suy ra được các trị m1, m2 và b như ở các ô E15:F17.

Áp dụng phương trình của đường cong trong hồi quy tuyến tính bội, với x1 = 12 và x2 = 25, bằng công thức tại ô A13:

A13 = F17 * (F16^B13) * (F15^C13) = 279.720291 ≈ 280

Vậy khi x1 = 12 và x2 = 25 thì có thể dự báo được y = 280

Hàm TREND()

Trả về các trị theo xu hướng tuyến tính. Làm cho một đường thẳng (dùng phương pháp bình phương tối thiểu) thích hợp với các mảng known_y's và known_x's, và TREND() trả về các giá trị y theo đường thẳng đó.

TREND() là một hàm cho ra kết quả là một mảng, do đó nó phải được nhập ở dạng công thức mảng.


Cú pháp: = TREND(known_y's, known_x's, new_x's, const)

Known_y's : Một tập hợp các giá trị y đã biết, trong mối quan hệ y = mx + b.

- Nếu mảng known_y's nằm trong một cột, thì mỗi cột của known_x's được hiểu như là một biến độc lập.

- Nếu mảng known_y's nằm trong một dòng, thì mỗi dòng của known_x's được hiểu như là một biến độc lập.


Known_x's : Một tập hợp tùy chọn các giá trị x đã biết, trong mối quan hệ y = mx + b.

- Mảng known_x's có thể bao gồm một hay nhiều tập biến. Nếu chỉ một biến được sử dụng, known_x's và known_y's có thể có hình dạng bất kỳ, miễn là chúng có kích thước bằng nhau. Nếu có nhiều biến được sử dụng, known_y's phải là một vectơ (là một dãy, với chiều cao là một dòng, hay với độ rộng là một cột)

- Nếu bỏ qua known_x's, known_x's sẽ được giả sử là một mảng {1, 2, 3, ...} với kích thước bằng với known_y's.


New_x's : Là các giá trị x mới, dùng để TREND() trả về các giá trị y tương ứng.

- New_x's phải gồm một cột (hay một dòng) cho mỗi biến độc lập, giống như known_x's. Vì thế, nếu known_y's nằm trong một cột đơn, thì known_x's và new_x's phải có cùng số lượng các cột; nếu known_y's nằm trên một dòng đơn, thì known_x's và new_x's phải có cùng số lượng các dòng.

- Nếu bỏ qua new_x's, new_x's sẽ được giả sử giả sử là giống như known_x's.

- Nếu bỏ qua cả known_x's và new_x's sẽ được giả sử là mảng {1, 2, 3, ...} với kích thước bằng với known_y's.


Const : Là một giá trị logic cho biết có nên ép hằng số b để nó bằng 0 hay không (trong mối quan hệ y = mx + b).

- Nếu const là TRUE (1) hoặc bỏ qua, b được tính bình thường.

- Nếu const là FALSE (0), b được gán bằng 0, khi đó các giá trị m sẽ được điều chỉnh để y = mx.


Lưu ý:

* Có thể dùng TREND() để làm thích hợp các đường cong đa thức bằng việc quy vào biến có nhiều lũy thừa khác nhau. Ví dụ, giả sử cột A chứa các trị y và cột B chứa các trị x. Khi đó có thể nhập x^2 trong cột C, x^3 trong cột D, v.v... và tính hồi quy các cột B, C, D... theo cột A.

* Khi nhập hằng mảng cho đối số, như hằng mảng cho known_y's chẳng hạn, dùng dấu phẩy để phân cách các trị trên cùng dòng, và dấu chấm phẩy để phân cách các dòng.


Ví dụ 1:

Đây mà một bảng dữ liệu về lợi tức từ tháng thứ nhất đến tháng thứ mười hai của một đơn vị. Xem hai ví dụ về hàm TREND() như sau:

Để tìm giá trị tương ứng với các giá trị đã có, chọn cả khối cell C2:C13, nhập công thức mảng:

{=TREND(B2:B13, A2:A13)}

Để tìm các giá trị lợi tức dự đoán cho các tháng từ 13 đến 17, chọn khối cell B15:B19, nhập công thức mảng:

{=TREND(B2:B13, A2:A13, A15:A19)}


Ví dụ 2: Đây là ví dụ đã nói đến trong bài Hàm FORECAST()

Dựa vào bảng phân tích lợi nhuận dựa theo giá thành ở bảng sau. Hãy ước lượng mức lợi nhuận khi giá thành = $270,000 ?

Thay vì dùng hàm FORECAST(), ta có thể dùng TREND():

A11 = TREND(A2:A10, B2:B10, B11) = $288,811

Qua ví dụ này, ta thấy rằng, dùng hàm TREND() hay hàm FORECAST() cũng sẽ có kết quả tương đương. Hai hàm này chỉ khác nhau ở chỗ đặt giá trị dùng để dự đoán trong công thức, TREND() thì đặt giá trị này (new_x's) ở cuối, còn FORECAST() thì đặt giá trị này (x) ở đầu.