2.6.7. Hàm thống kê (Statistical functions)
Tác giả: Bùi Nguyễn Triệu Tường (BNTT - GPE)
Tổng hợp: ongtrungducmx25 (GPE)
NHÓM HÀM
VỀ TƯƠNG QUAN VÀ HỒI QUY TUYẾN TÍNH
Hàm CORREL()
Trả về hệ số tương quan của hai mảng array1 và array2.
Thường được dùng để xác định mối quan hệ của hai đặc tính. Ví dụ,
bạn có thể khảo sát mối quan hệ giữa nhiệt độ trung bình của một
nơi với việc sử dụng các máy điều hòa nhiệt độ.
Hệ số tương quan chỉ ra mối quan hệ tuyến tính giữa hai mảng. Hệ
số tương quan dương (> 0) có nghĩa là hai mảng sẽ đồng biến; hệ số
tương quan âm (< 0) có nghĩa là hai mảng sẽ nghịch biến.
Cú pháp:
= CORREL(array1, array2)
Array1,
array2
: Các mảng dữ liệu để tính hệ số tương quan.
Lưu ý:
-
Nếu
đối số là mảng hay tham chiếu có chứa text, giá trị logic, ô rỗng,
thì các giá trị này sẽ được bỏ qua; tuy nhiên những ô chứa giá trị
0 (zero) vẫn được tính.
Ví dụ:
Tính hệ số tương quan giữa hai mảng dữ liệu sau:
(A1:A5) = {3, 2, 4, 5, 6}
(B1:B5) = {9, 7, 12, 15, 17}
CORREL(A1:A5, B1:B5)
= 0.997054
Hàm COVAR()
Trả về hiệp phương sai (hay còn gọi là đồng phương sai -
covariance).
Hiệp phương sai là trung bình của tích các cặp sai lệch, nghĩa là
tính tính số các độ lệch của mỗi cặp dữ liệu, rồi tính trung bình
của các tích đó.
Cú pháp:
= COVAR(array1, array2)
Array1,
array2:
Là dãy thứ nhất và dãy thứ hai (chứa những số nguyên, và có số
điểm dữ liệu giống nhau) để tính hiệp phương sai.
Lưu ý:
-
Nếu
Array là mảng hay tham chiếu có chứa các giá trị text,
logic, hay ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô
chứa giá trị zero (0) thì vẫn được tính.
Ví dụ:
Có hai dãy sau: Data1 = {3, 2, 4, 5, 6} và Data2 = {9, 7, 12, 15,
17}
COVAR({3, 2, 4, 5, 6}, {9, 7, 12, 15, 17})
= 5.2
Hàm FORECAST()
Tính toán, hay dự đoán, ước lượng một giá trị tương lai bằng cách
sử dụng các giá trị hiện có. Từ những giá trị hiện có, giá trị mới
được dự đoán bằng phương pháp hồi quy tuyến tính. Có thể dùng hàm
này để dự đoán mức bán hàng trong tương lai, nhu cầu đầu tư, hay
khuynh hướng tiêu thụ.
Cú pháp:
= FORECAST(x, known_y's, known_x's)
x
: Điểm dữ liệu dùng để dự đoán giá trị mới.
known_y's
: Mảng hay dữ liệu phụ thuộc.
known_x's
: Mảng hay dữ liệu độc lập.
Lưu ý:
Với:
Ví dụ:
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 ?
Mức lợi nhuận tương ứng với giá thành = $270,000 sẽ là:
A11
= FORECAST(B11, A2:A10, B2:B10)
= $288,811
Hàm GROWTH()
Tính toán sự tăng trưởng dự kiến theo hàm mũ bằng cách sử dụng dữ
kiện hiện có. GROWTH() trả về các giá trị y từ các giá trị
x được chỉ định bằng cách sử dụng các giá trị x hiện
có.
GROWTH() 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:
= GROWTH(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
= 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.
- Nếu có bất kỳ số nào trong known_y's là 0 hay là số âm,
GROWTH() sẽ trả về giá trị lỗi #NUM!
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 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 để GROWTH() 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 1 hay không (trong mối quan hệ y = b*m^x).
- Nếu const là TRUE (1) hoặc bỏ qua, b được tính
bình thường.
- Nếu const là FALSE (0), v được gán bằng 1, khi đó các giá
trị m sẽ được điều chỉnh để y = m*x.
Lưu ý:
-
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ụ:
Đây mà một bảng mô tả mức tăng trưởng doanh thu của một đơn vị từ
tháng thứ 11 đến tháng thứ 16.
Dựa theo mức tăng trưởng này, dự đoán doanh thu của tháng thứ 17
và 18 ?
Chọn cả hai ô B9:B10, nhập công thức mảng:
{= GROWTH(B2:B7, A2:A7, A9:A10)}
Ta sẽ có kết quả doanh thu dự đoán của tháng thứ 17 (B9) = 320,197
và tháng thứ 18 (B10) = 468,536
Hàm INTERCEPT()
Tìm điểm giao của một đường thẳng với trục y bằng cách sử
dụng các trị x và y hiện có. Trong dự báo hồi quy
tuyến tính đơn, đường thằng này gọi là Đường thẳng hồi quy,
được vẽ theo các trị x và y đã biết, và giao điểm
dựa vào cơ sở trên đường thẳng hồi quy này.
Hàm INTERCEPT() thường được dùng khi muốn xác định một biến phụ
thuộc khi biến độc lập bằng zero (0). Ví dụ, dùng để dự đoán điện
trở kim loại tại 0 độ C khi các điểm dữ liệu được lấy từ nhiệt độ
phòng hay cao hơn.
Cú pháp:
= INTERCEPT(known_y's, known_x's)
Known_y's
: Tập hợp các dữ liệu phụ thuộc.
Known_x's
: Tập hợp các dữ liệu độc lập.
Lưu ý:
-
Nếu
các đối số là mảng hay tham chiếu có chứa các giá trị text, logic,
hay ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa
giá trị zero (0) thì vẫn được tính.
Với:
-
Giải thuật của hàm INTERCEPT() và hàm
SLOPE() thì khác với giải
thuật của hàm
LINEST(). Sự khác nhau giữa
chúng là có thể dẫn đến những kết quả khác nhau đối với những dữ
liệu cùng nằm trên một đường thẳng và chưa được xác định. Ví dụ,
nếu những điểm dữ liệu của đối số known_y's là 0 và của
known_x's là 1:
* INTERCEPT() và
SLOPE() sẽ trả về lỗi #DIV/0!
bởi vì giải thuật của INTERCEPT() và
SLOPE() được thiết kế để tìm
ra một và chỉ một đáp án, mà trong trường hợp này thì kết quả trả
về có nhiều hơn một đáp án.
*
LINEST() trả về kết quả là 0
bởi vì giải thuật của
LINEST() được thiết kế để tìm
ra tất cả những đáp án đúng với những dữ liệu , mà trong trường
hợp này thì kết quả trả về có nhiều hơn một đáp án cho những dữ
liệu cùng nằm trên một đường thẳng, và trong trường hợp này thì có
ít nhất một đáp án được tìm thấy.
Ví dụ 1:
Với tập hợp known_y's = {2, 3, 9, 1, 8} và known_x's
= {6, 5, 11, 7, 5}. Không cần dùng đồ thị, tính tọa độ của điểm mà
đường thẳng hồi quy sẽ cắt trục tung (trục y) ?
INTERCEPT({2, 3, 9, 1, 8}, {6, 5, 11, 7, 5})
= 0.04387097
Tọa độ của điểm mà đường thẳng hồi quy sẽ cắt trục tung (trục y)
là (0.04387097, 0)
Ví dụ 2:
(xem Ví dụ 2 của bài Hàm
SLOPE)
Hàm LINEST()
Trong phân tích hồi quy, LINEST() dùng phương pháp bình phương
tối thiểu (least squares) để tính đường thẳng thích hợp nhất
với dữ liệu được cung cấp, rồi trả về một mảng các giá trị mô tả
đường thẳng đó. Do kết quả trả về là một mảng, nên LINEST() thường
được nhập với dạng công thức mảng.
LINEST() thường được dùng cho phương pháp hồi quy tuyến tính đơn
hoặc hồi quy tuyến tính bội.
Phương trình của đường thẳng trong hồi quy tuyến tính đơn là:
Phương trình của đường thẳng 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à LINEST() trả về là:
LINEST() cũng có thể trả về thống kê hồi quy phụ.
Cú pháp:
= LINEST(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
= 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 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 0
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 = mx.
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, LINEST() chỉ trả về các
hệ số m và hằng số b.
- Nếu stats là TRUE (1), LINEST() 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
ý:
-
Hệ số góc = (y2-y1)/(x2-x1), với (x1,y1) và (x2,y2) là hai điểm
trên đường thẳng;
- Điểm cắt b trên trục y là giá trị của y tại
điểm mà đường thẳng cắt trục y.
Phương trình của đường thằng là y= mx + b. Một khi đã biết
được giá trị m và b, chúng ta có thể tính bất kỳ
điểm nào thuộc đường thằng bằng cách thêm giá trị y hay
x vào phương trình đó. Bạn cũng có thể sử dụng hàm TREND().
Hệ
số góc m: = INDEX(LINEST(known_y's,
known_x's), 1)
Điểm cắt b: = INDEX(LINEST(known_y's,
known_x's), 2)
-
Độ
chính xác của đường thẳng do LINEST() tính ra còn tùy thuộc vào độ
tán xạ trong dữ liệu. Dữ liệu càng tuyến tính, hàm LINEST() mô
phỏng đường thẳng càng chính xác. LINEST() dùng phương pháp bình
phương tối thiểu để xác định các điểm thích hợp nhất cho dữ liệu.
Khi chỉ có một biến độc lập x, những tính toán để tìm m
và b dựa vào công thức sau:
Với:
-
Hàm
LINEST() có thể tính được đường thẳng tốt nhất từ dữ liệu được
cung cấp; hàm LOGEST() có thể tính được hàm mũ tốt nhất từ dữ liệu
được cung cấp. Tuy nhiên chúng ta cần xác định xem trong hai kết
quả nhận được, kết quả nào thích hợp với dữ liệu được cung cấp
hơn. Có thể tính TREND(known_y's, known_x's) cho đường
thẳng và GROWTH(known_y's, known_x's) cho đường hàm mũ.
Những hàm này, không có đối số new_x's, trả về một mảng giá
trị dự đoán y. Từ đó chúng ta có thể so sánh các trị dự
đoán được với các trị thực; có thể vẽ lên biểu đồ hai loại đường
này để so sánh trực quan hơn.
-
Trong phân tích hồi quy, Excel tính cho mỗi điểm một sai phân bình
phương giữa trị ước lượng x và trị thực y của điểm
đó. Tổng các sai phân này gọi là tổng bình phương thặng dư.
Sau đó Excel tính tổng các sai phân bình phương giữa các trị thực
y và trung bình các trị y, kết quả này gọi là
tổng bình phương toàn phần (= tổng bình phương hồi quy + tổng
bình phương thặng dư). So với tổng bình phương toàn phần, nếu tổng
bình phương thặng dư càng nhỏ, thì hệ số định trị r2 càng
lớn. Đây là cách mà kết quả nhận được từ phân tích hồi quy giải
thích mối quan hệ giữa các biến.
-
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).
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 PEARSON()
Trả về hệ số tương quan momen tích Pearson, r, một
đại lượng vô hướng nằm trong khoảng [-1, 1], phản ánh sự mở rộng
quan hệ tuyến tính giữa hai tập số liệu.
Cú pháp:
= PEARSON(array1, array2)
Array1:
Là tập hợp các giá trị độc lập.
Array2:
Là tập hợp các giá trị phụ thuộc.
Lưu ý:
-
Nếu
đối số là mảng hay tham chiếu có chứa các giá trị text, logic, hay
ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa giá
trị zero (0) thì vẫn được tính.
với:
Ví dụ:
Cho tập hợp các giá trị độc lập = {9, 7, 5, 3, 1}
và tập hợp các giá trị phụ thuộc = {10, 6, 1, 5, 3}
Hệ số tương quan tích momen Pearson đối với hai tập số liệu
trên là:
r
= PEARSON({9, 7, 5, 3, 1}, {10, 6, 1, 5, 3})
= 0.699379
Hàm RSQ()
Tính bình phương hệ số tương quan momen tích Pearson, thông
qua các điểm dữ liệu trong known_y's và known_x's.
Trị bình phương r có thể hiểu là tỷ lệ phương sai trong
thuộc tính y với phương sai trong thuộc tính x. Để
biết thêm thông tin, xem thêm hàm
PEARSON().
Cú pháp:
= RSQ(known_y's, known_x's)
known_y's,
known_x's:
Là mảng hay dãy các điểm dữ liệu.
Lưu ý:
-
Nếu
đối số là mảng hay tham chiếu có chứa các giá trị text, logic, hay
ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa giá
trị zero (0) thì vẫn được tính.
với:
Ví dụ:
Cho hai tập hợp các điểm dữ liệu là {2, 3, 9, 1, 8, 7, 5} và {6,
5, 11, 7, 5, 4, 4}
Bình phương hệ số tương quan tích momen Pearson đối với hai
tập số liệu trên là:
= RSQ({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4})
= 0.05795
Hàm SLOPE()
Tìm hệ số góc của đường thẳng hồi quy bằng cách sử dụng các điểm
dữ liệu trong known_y's và known_x's.
Ở bài hàm
INTERCEPT(), tôi có viết:
phương trình giao điểm của đường thẳng hồi quy là: (trong đó b
là hệ số góc):
Với:
Hàm SLOPE() chính là hàm để xác định cái b ở trên.
Cú pháp:
= SLOPE(known_y's, known_x's)
Known_y's
: Tập hợp các dữ liệu phụ thuộc.
Known_x's
: Tập hợp các dữ liệu độc lập.
Lưu ý:
-
Nếu
các đối số là mảng hay tham chiếu có chứa các giá trị text, logic,
hay ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa
giá trị zero (0) thì vẫn được tính.
-
Giải thuật của hàm SLOPE() và hàm
INTERCEPT() thì khác với giải
thuật của hàm
LINEST(). Sự khác nhau giữa
chúng là có thể dẫn đến những kết quả khác nhau đối với những dữ
liệu cùng nằm trên một đường thẳng và chưa được xác định. Ví dụ,
nếu những điểm dữ liệu của đối số known_y's là 0 và của
known_x's là 1:
* SLOPE() và
INTERCEPT() sẽ trả về lỗi
#DIV/0! bởi vì giải thuật của SLOPE() và
INTERCEPT() được thiết kế để
tìm ra một và chỉ một đáp án, mà trong trường hợp này thì kết quả
trả về có nhiều hơn một đáp án.
*
LINEST() trả về kết quả là 0
bởi vì giải thuật của
LINEST() được thiết kế để tìm
ra tất cả những đáp án đúng với những dữ liệu , mà trong trường
hợp này thì kết quả trả về có nhiều hơn một đáp án cho những dữ
liệu cùng nằm trên một đường thẳng, và trong trường hợp này thì có
ít nhất một đáp án được tìm thấy.
Ví dụ 1:
Với tập hợp known_y's = {2, 3, 9, 1, 8} và known_x's
= {6, 5, 11, 7, 5}. Không cần dùng đồ thị, tính hệ số góc của
đường thẳng hồi quy ?
SLOPE({2, 3, 9, 1, 8}, {6, 5, 11, 7, 5})
= 0.305555556
Ví dụ 2:
Đây là ví dụ đã nói đến ở 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 ?
Ta sẽ dùng hàm SLOPE() kết hợp với hàm
INTERCEPT() để tính, bằng
phương pháp dự báo hồi quy tuyến tính đơn (y = ax + b), với các dữ
liệu phụ thuộc là Lợi nhuận, và các dự liệu độc lập là Giá thành:
a
= SLOPE(A2:A10, B2:B10)
= -0.24021693
b
= INTERCEPT(A2:A10, B2:B10)
= 353,669.9277
x = 270,000
y = (ax + b) = (-0.24021693)*(270,000) + (353,669.9277) =
288,811 (làm tròn không lấy số lẻ)
Vậy, khi giá thành bằng $270,000 thì mức lợi nhuận (ước lượng) là
$288,811
Để ý rằng, kết quả này bằng với kết quả của hàm
FORECAST()
Hàm STEYX()
Trả về sai số chuẩn của trị dự đoán y đối với mỗi trị x
trong hồi quy. Sai số chuẩn là thước đo lượng sai số trong dự đoán
y đối với mỗi trị x.
Cú
pháp:
= STEYX(known_y's,
known_x's)
known_y's:
Là mảng hay dãy các điểm dữ liệu phụ thuộc.
known_x's: Là mảng
hay dãy các điểm dữ liệu độc lập.
Lưu ý:
·
Các
đối số phải là số, tên. mảng hay tham chiếu đến các ô có chứa số.
·
Nếu
đối số là mảng hay tham chiếu có chứa các giá trị text, logic, hay
ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa giá
trị zero (0) thì vẫn được tính.
·
Nếu
known_y's hay known_x's rỗng hoặc có số điểm dữ liệu
không bằng nhau, STEYX() sẽ trả về giá trị lỗi #NA!
·
Phương trình tính sai số chuẩn của trị dự đoán y là:
với:
Ví dụ:
Cho
hai tập hợp các điểm dữ liệu là {2, 3, 9, 1, 8, 7, 5} và {6, 5,
11, 7, 5, 4, 4}
Sai số chuẩn của trị dự đoán y đối với mỗi trị x
trong hồi quy của hai tập số liệu trên là:
= STEYX({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4})
= 3.305719
 Top
|
