Ý nghĩa của công thức excel correl array1 array2

Calculates the correlation coefficient between two variables

The CORREL function is categorized under Excel Statistical functions. It will calculate the correlation coefficient between two variables.

As a financial analyst, the CORREL function is very useful when we want to find the correlation between two variables, e.g., the correlation between a particular stock and a market index.

Correlation Formula

=CORREL[array1, array2]

The CORREL function uses the following arguments:

1. Array1 [required argument] – This is the set of independent variables. It is a cell range of values.
2. Array2 [required argument] – This is the set of dependent variables. It is the second cell range of values.

The equation for the correlation coefficient is:

Where:

are the sample means AVERAGE[array1] and AVERAGE[array2].

So, if the value of r is close to +1, it indicates a strong positive correlation, and if r is close to -1, it shows a strong negative correlation.

How to use CORREL Function in Excel?

The CORREL function was introduced in Excel 2007 and is available in all subsequent Excel versions. To understand the uses of the function, let’s look at an example:

Correlation Example

Suppose we are given data about the weekly returns of stock A and percentage of change in a market index [S&P 500]:

The formula used to find the correlation is:

We get the result below:

The result indicates a strong positive correlation.

Things to remember about the CORREL Function

1. #N/A error – Occurs if the given arrays are of different lengths. So, if Array1 and Array2 contain different numbers of data points, CORREL will return the #N/A error value.
2. #DIV/0 error – Occurs if either of the given arrays are empty or if the standard deviation of their values equals zero.
3. If an array or reference argument contains text, logical values, or empty cells, the values are ignored; however, cells with the value zero are included.
4. The CORREL function is exactly same as the PEARSON Function, except that, in earlier versions of Excel [earlier than Excel 2003], the PEARSON function may exhibit some rounding errors. Hence, it is advisable to use the CORREL function in earlier versions of Excel. In later versions of Excel, both functions should give the same results.

Click here to download the sample Excel file

Additional resources

Thanks for reading CFI’s guide to the Excel CORREL function. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

CORREL Excel Function | Formula | How to Calculate Correlation in Excel?
Trong video về hàm CORREL [tương quan] trong Excel này, ở đây chúng ta sẽ thảo luận về công thức CORREL và cách sử dụng hàm CORREL với một ví dụ trong Excel. ———————————————- – Trong Excel, hàm CORREL được phân loại trong số các hàm thống kê. Công thức CORREL của Excel được sử dụng để xác định hệ số tương quan giữa hai yếu tố. Nó trả về hệ số tương quan array1 và array2. 𝐄𝐱𝐜𝐞𝐥 —————————————– CORREL [array1, array2] 𝐀𝐫𝐠𝐮𝐦𝐞𝐧𝐭𝐬 – ————————- array1 = Cần có một tập hợp các biến độc lập. array2 = Một tập hợp các biến phụ thuộc. ———————————————— ———————————————— – – Lỗi # 1 – # N / A – [CORREL] Hàm tương quan trong Excel theo lỗi # N / A nếu các mảng được cung cấp có độ dài khác nhau. Điều này có nghĩa là nếu array1 và array2 chứa các số điểm dữ liệu khác nhau, CORREL trả về giá trị lỗi của lỗi # N / A. # 2 – # DIV / 0 – Hàm tương quan trong Excel thông qua # DIV / lỗi 0 nếu có bất kỳ mảng nào trong số các mảng đã cho [ array1, array2] trống hoặc độ lệch chuẩn tuyệt vời của giá trị bằng 0. # 3 – [CORREL] Hàm tương quan của Excel giả định giá trị bằng không trong phép tính của nó. Để biết thêm về 𝐄𝐱𝐜𝐞𝐥, bạn có thể truy cập 𝐥𝐢𝐧𝐤 𝐡𝐞𝐫𝐞 này: – Kết nối với chúng tôi! Youtube ► LinkedIn ► Facebook ► Instagram ► Twitter ►.

” CORREL Excel Function | Formula | How to Calculate Correlation in Excel? “, được lấy từ nguồn: //www.youtube.com/watch?v=4DTXrWjbs_M

Tags: #Hàm #CORREL #trong #Excel #Công #thức #Làm #thế #nào #để #tính #toán #tương #quan #trong #Excel

Từ khóa: hàm excel,correl excel function,correl function in excel,excel correl function,correl excel formula,correl excel function example,correl excel,correlation in excel,correl function,correl formula in excel,using correl function in excel,how to use correl function in excel,correlation function,how to use correl formula in excel,correl in excel,correl formula,how to calculate a correlation in excel,correlation excel,correlation using excel,correlation analysis in excel

Luật Hùng Phát là công ty tư vấn đăng ký kinh doanh chuyên nghiệp, với hơn 10 năm kinh nghiệm cung cấp dịch vụ thành lập công ty và dịch vụ kế toán. Chúng tôi đã và đang xử lý hàng trăm hồ sơ nhanh, hồ sơ khó mà các công ty khác không làm được.

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 ý:

• Đối số phải là số, là tên, mảng, hay tham chiếu có chứa số.

• 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.

• Nếu array1 và array2 có số lượng các điểm dữ liệu không bằng nhau, CORREL[] sẽ trả về giá trị lỗi #NA!

• Nếu array1 hoặc array2 là rỗng, hoặc nếu độ lệch chuẩn có giá trị bằng 0, CORREL[] sẽ trả về giá trị lỗi #DIV/0!

• CORREL[] tính toán theo công thức sau:

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 ý:

• Array phải là số, tên. mảng hay tham chiếu đến các ô có chứa số.

• 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.

• Nếu array1 và array2 có số điểm dữ liệu khác nhau, COVAR[] sẽ trả về giá trị lỗi #NA!

• Nếu array1 hay array2 rỗng, COVAR[] sẽ trả về giá trị lỗi #DIV/0!

• COVAR[] được tính theo công thức sau:

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 ý:

• Nếu x không phải là số, FORECAST[] trả về giá trị lỗi #VALUE!

• Nếu known_y's, known_x's là rỗng hay chứa số điểm dữ liệu khác nhau, FORECAST[] trả về giá trị lỗi #NA!

• Nếu known_x's = 0, FORECAST[] trả về giá trị lỗi #DIV/0!

• Phương trình của FORECAST là:

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 ý:

• Đối số phải là số, tên, mảng, hay tham chiếu đến các ô chứa số.

• 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.

• Nếu known_y's, known_x's là rỗng hay chứa số điểm dữ liệu khác nhau, INTERCEPT[] trả về giá trị lỗi #NA!

• Phương trình giao điểm của đường hồi quy là [trong đó b là hệ số góc, xem hàm SLOPE]:

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 ý:

• Có thể mô tả đường thẳng bằng hệ số góc m và một điểm cắt b trên trục y:

- 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[].

• 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 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[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].

• 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.

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 ý:

• 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 array1 hay array2 rỗng hoặc có số điểm dữ liệu không bằng nhau, PEARSON[] sẽ trả về giá trị lỗi #NA!

• PEARSON[] được tính theo công thức sau:

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 ý:

• 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, RSQ[] sẽ trả về giá trị lỗi #NA!

• Phương trình tính trị r của đường hồi quy 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}

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 ý:

• Đối số phải là số, tên, mảng, hay tham chiếu đến các ô chứa số.

• 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.

• Nếu known_y's, known_x's là rỗng hay chứa số điểm dữ liệu khác nhau, SLOPE[] trả về giá trị lỗi #NA!

• 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] = 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