Ý 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. =CORREL(array1, array2) The CORREL function uses the following arguments: The equation for the correlation coefficient is: Where: 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 ExampleSuppose 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
Click here to download the sample Excel file Additional resourcesThanks 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? ” CORREL Excel Function | Formula | How to Calculate Correlation in Excel? “, được lấy từ nguồn: https://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) 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 ý:
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 ý:
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. 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!
- 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 ý:
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. {= 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. Lưu ý:
Với:
* 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)
Với:
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. Lưu ý:
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 ý:
với:
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. Lưu ý:
* 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. 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
|