Trong hướng dẫn này, bạn sẽ tìm thấy mô tả chi tiết về tất cả các tùy chọn đường xu hướng có sẵn trong Excel và khi nào nên sử dụng chúng. Bạn cũng sẽ tìm hiểu làm thế nào để hiển thị một phương trình đường xu hướng trong biểu đồ và tìm độ dốc của đường xu hướng.
Nó rất dễ dàng để thêm một đường xu hướng trong Excel. Thách thức thực sự duy nhất là chọn loại đường xu hướng phù hợp nhất với loại dữ liệu bạn đang phân tích. Trong hướng dẫn này, bạn sẽ tìm thấy mô tả chi tiết về tất cả các tùy chọn đường xu hướng có sẵn trong Excel và khi nào nên sử dụng chúng. Nếu bạn đang tìm cách chèn đường xu hướng trong biểu đồ Excel, vui lòng xem hướng dẫn được liên kết ở trên.
Các loại đường xu hướng Excel
Khi thêm một đường xu hướng trong Excel, bạn có 6 tùy chọn khác nhau để lựa chọn. Ngoài ra, Microsoft Excel cho phép hiển thị một phương trình đường xu hướng và giá trị bình phương R trong biểu đồ:
- Phương trình đường xu hướng là một công thức tìm ra một dòng phù hợp nhất với các điểm dữ liệu.
- Giá trị bình phương R đo lường độ tin cậy của đường xu hướng – R gần hơn2 là 1, đường xu hướng phù hợp với dữ liệu càng tốt.
Dưới đây, bạn sẽ tìm thấy một mô tả ngắn gọn về từng loại đường xu hướng với các ví dụ biểu đồ.
Đường xu hướng tuyến tính
Đường xu hướng tuyến tính là tốt nhất để được sử dụng với các tập dữ liệu tuyến tính khi các điểm dữ liệu trong biểu đồ giống với một đường thẳng. Thông thường, một đường xu hướng tuyến tính mô tả sự tăng hoặc giảm liên tục theo thời gian.
Ví dụ, đường xu hướng tuyến tính sau đây cho thấy doanh số tăng đều đặn trong 6 tháng. Và R2 giá trị 0,9855 cho thấy mức độ phù hợp khá tốt của các giá trị đường xu hướng ước tính với dữ liệu thực tế.
Đường xu hướng theo cấp số nhân
Đường xu hướng theo cấp số nhân là một đường cong minh họa cho sự tăng hoặc giảm giá trị dữ liệu với tốc độ tăng dần, do đó đường này thường cong hơn ở một bên. Loại đường xu hướng này thường được sử dụng trong các ngành khoa học, ví dụ để hình dung sự gia tăng dân số của con người hoặc sự suy giảm trong quần thể động vật hoang dã.
Xin lưu ý rằng đường xu hướng theo cấp số nhân có thể được tạo cho dữ liệu chứa các giá trị 0 hoặc âm.
Một ví dụ điển hình của đường cong hàm mũ là sự phân rã trong toàn bộ quần thể hổ hoang dã trên trái đất.
Đường xu hướng logarit
Dòng phù hợp nhất logarit thường được sử dụng để vẽ dữ liệu nhanh chóng tăng hoặc giảm và sau đó tắt. Nó có thể bao gồm cả giá trị tích cực và tiêu cực.
Một ví dụ về đường xu hướng logarit có thể là tỷ lệ lạm phát, đầu tiên là ngày càng cao nhưng sau một thời gian ổn định.
Đường xu hướng đa thức
Đường xu hướng cong đa thức hoạt động tốt đối với các tập dữ liệu lớn với các giá trị dao động có nhiều hơn một lần tăng và giảm.
Nói chung, một đa thức được phân loại theo mức độ của số mũ lớn nhất. Mức độ của đường xu hướng đa thức cũng có thể được xác định bởi số lần uốn trên biểu đồ. Thông thường, một đường xu hướng đa thức bậc hai có một uốn cong (đồi hoặc thung lũng), một đa thức bậc ba có 1 hoặc 2 uốn cong, và một đa thức bậc bốn có tới 3 uốn cong.
Khi thêm một đường xu hướng đa thức trong biểu đồ Excel, bạn chỉ định mức độ bằng cách nhập số tương ứng vào Đặt hàng hộp trên Định dạng Xu hướng khung, là 2 theo mặc định:
Ví dụ, xu hướng đa thức bậc hai được thể hiện rõ trên biểu đồ sau cho thấy mối quan hệ giữa lợi nhuận và số năm sản phẩm đã có mặt trên thị trường: tăng vào đầu, đỉnh ở giữa và giảm gần cuối.
Đường xu hướng điện
Đường xu hướng công suất rất giống với đường cong hàm mũ, chỉ có điều nó có vòng cung đối xứng hơn. Nó thường được sử dụng để vẽ các phép đo tăng ở một tỷ lệ nhất định.
Đường xu hướng công suất không thể được thêm vào biểu đồ Excel có chứa giá trị 0 hoặc âm.
Ví dụ, hãy vẽ một đường xu hướng công suất để trực quan hóa tốc độ phản ứng hóa học. Lưu ý giá trị R bình phương 0,9918, có nghĩa là đường xu hướng của chúng tôi phù hợp với dữ liệu gần như hoàn hảo.
Di chuyển đường xu hướng trung bình
Khi các điểm dữ liệu trong biểu đồ của bạn có nhiều thăng trầm, một đường xu hướng trung bình di động có thể làm dịu các biến động cực đoan trong các giá trị dữ liệu để hiển thị một mẫu rõ ràng hơn. Đối với điều này, Excel tính toán trung bình di chuyển của số lượng thời gian bạn chỉ định (2 theo mặc định) và đặt các giá trị trung bình đó làm điểm trong dòng. Cao hơn Giai đoạn = Stage Giá trị, dòng càng mượt.
Một ví dụ thực tế tốt là sử dụng đường xu hướng trung bình động để tiết lộ sự biến động của giá cổ phiếu mà nếu không sẽ khó quan sát.
Để biết thêm thông tin, vui lòng xem: Cách thêm đường xu hướng trung bình di chuyển vào biểu đồ Excel.
Phương trình và công thức đường xu hướng Excel
Phần này mô tả các phương trình mà Excel sử dụng cho các loại đường xu hướng khác nhau. Bạn không phải xây dựng các công thức này theo cách thủ công, chỉ cần nói với Excel để hiển thị phương trình đường xu hướng trong biểu đồ.
Ngoài ra, chúng tôi sẽ thảo luận về công thức để tìm độ dốc của đường xu hướng và các hệ số khác. Các công thức giả định rằng bạn có 2 bộ biến: biến độc lập x và biến phụ thuộc y. Trong bảng tính của bạn, bạn có thể sử dụng các công thức này để có được dự đoán y các giá trị cho bất kỳ giá trị đã cho nào của x.
Để thống nhất, chúng tôi sẽ sử dụng cùng một bộ dữ liệu với các giá trị thay đổi một chút cho tất cả các ví dụ. Tuy nhiên, xin lưu ý rằng nó chỉ dành cho mục đích trình diễn. Trong bảng tính thực của bạn, bạn nên chọn loại đường xu hướng tương ứng với kiểu dữ liệu của bạn.
Phương trình và công thức đường xu hướng tuyến tính
Phương trình đường xu hướng tuyến tính sử dụng các phương pháp bình phương nhỏ nhất để tìm kiếm dốc và đánh chặn các hệ số sao cho:
y = bx + a
Ở đâu:
- b là dốc của một đường xu hướng.
- một là y-chặn, đó là giá trị trung bình dự kiến của y khi tất cả x các biến bằng 0. Trên biểu đồ, đó là điểm mà đường xu hướng đi qua y trục.
Đối với hồi quy tuyến tính, Microsoft Excel cung cấp các hàm đặc biệt để lấy hệ số độ dốc và chặn.
Độ dốc của đường xu hướng
b: =SLOPE(y,x)
Y-đánh chặn
a: =INTERCEPT(y,x)
Giả sử x phạm vi là B2: B13 và y phạm vi là C2: C13, các công thức thực tế diễn ra như sau:
=SLOPE(C2:C13, B2:B13)
=INTERCEPT(C2:C13,B2:B13)
Các kết quả tương tự có thể đạt được bằng cách sử dụng LỚN hoạt động như một công thức mảng. Đối với điều này, chọn 2 ô liền kề trong cùng một hàng, nhập công thức và nhấn Ctrl + Shift + Enter để hoàn thành nó:
=LINEST(C2:C13,B2:B13)
Như được hiển thị trong ảnh chụp màn hình bên dưới, các hệ số độ dốc và giao thoa được trả về bởi các công thức hoàn toàn phù hợp với các hệ số trong phương trình đường xu hướng tuyến tính được hiển thị trong biểu đồ, chỉ có hệ số sau được làm tròn đến 4 chữ số thập phân:
Phương trình và công thức đường xu hướng hàm mũ
Đối với đường xu hướng theo cấp số nhân, Excel sử dụng phương trình sau:
y = aebx
Ở đâu một và b được tính hệ số và e là hằng số toán học e (cơ sở của logarit tự nhiên).
Các hệ số có thể được tính bằng cách sử dụng các công thức chung sau:
a: =EXP(INDEX(LINEST(LN(y), x), 1, 2))
b: =INDEX(LINEST(LN(y), x), 1)
Đối với tập dữ liệu mẫu của chúng tôi, các công thức có dạng như sau:
a: =EXP(INDEX(LINEST(LN(C2:C13), B2:B13), 1, 2))
b: =INDEX(LINEST(LN(C2:C13), B2:B13), 1)
Phương trình và công thức đường xu hướng logarit
Đây là phương trình đường xu hướng logarit trong Excel:
y = a * ln (x) + b
Ở đâu một và b là hằng số và ln là hàm logarit tự nhiên.
Để lấy các hằng số, hãy sử dụng các công thức chung này, chỉ khác nhau trong đối số cuối cùng:
a: =INDEX(LINEST(y, LN(x)), 1)
b: =INDEX(LINEST(y, LN(x)), 1, 2)
Đối với tập dữ liệu mẫu của chúng tôi, chúng tôi sử dụng những dữ liệu sau:
a: =INDEX(LINEST(C2:C13, LN(B2:B13)), 1)
b: =INDEX(LINEST(C2:C13, LN(B2:B13)), 1, 2)
Phương trình và công thức đường xu hướng đa thức
Để tìm ra đường xu hướng đa thức, Excel sử dụng phương trình này:
y = b6x6 + Cược + b2x2 + b1x + a
Ở đâu b1Giáo dụcb6 và một là hằng số.
Tùy thuộc vào mức độ của đường xu hướng đa thức của bạn, hãy sử dụng một trong các bộ công thức sau đây để lấy các hằng số.
Đường xu hướng đa thức bậc hai (bậc 2)
Phương trình: y = b2x2+ b1x + a
b2: =INDEX(LINEST(y, x^{1,2}), 1)
b1: =INDEX(LINEST(y, x^{1,2}), 1, 2)
a: =INDEX(LINEST(y, x^{1,2}), 1, 3)
Đường xu hướng đa thức hình khối (bậc 3)
Phương trình: y = b3x3 + b2x2+ b1x + a
b3: =INDEX(LINEST(y, x^{1,2,3}), 1)
b2: =INDEX(LINEST(y, x^{1,2,3}), 1, 2)
b1: =INDEX(LINEST(y, x^{1,2,3}), 1, 3)
a: =INDEX(LINEST(y, x^{1,2,3}), 1, 4)
Các công thức cho đường xu hướng đa thức bậc cao hơn có thể được xây dựng bằng cách sử dụng cùng một mẫu.
Đối với tập dữ liệu của chúng tôi, 2thứ đặt hàng bộ xu hướng đa thức tốt hơn, vì vậy chúng tôi đang sử dụng các công thức sau:
b2: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1)
b1: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1, 2)
a: =INDEX(LINEST(C2:C13, B2:B13^{1,2}), 1, 3)
Phương trình và công thức đường xu hướng công suất
Đường xu hướng công suất trong Excel được vẽ dựa trên phương trình đơn giản này:
y = rìub
Ở đâu một và b là các hằng số, có thể được tính bằng các công thức sau:
a: =EXP(INDEX(LINEST(LN(y), LN(x),,), 1, 2))
b: =INDEX(LINEST(LN(y), LN(x),,), 1)
Trong trường hợp của chúng tôi, các công thức sau đây có tác dụng:
a: =EXP(INDEX(LINEST(LN(C2:C13), LN(B2:B13),,), 1, 2))
b: =INDEX(LINEST(LN(C2:C13), LN(B2:B13),,), 1)
Phương trình đường xu hướng Excel sai – lý do và cách khắc phục
Nếu bạn nghĩ rằng Excel đã vẽ đường xu hướng không chính xác hoặc công thức đường xu hướng được hiển thị trong biểu đồ của bạn là sai, hai điểm sau đây có thể làm sáng tỏ tình huống này.
Phương trình đường xu hướng Excel chỉ đúng trong biểu đồ phân tán
Công thức đường xu hướng Excel chỉ nên được sử dụng với Đồ thị XY (phân tán) bởi vì chỉ trong biểu đồ này, cả trục y và trục x đều được vẽ dưới dạng giá trị số.
Trong biểu đồ đường, cột và đồ thị thanh, các giá trị số chỉ được vẽ trên trục y. Trục x được biểu thị bằng một chuỗi tuyến tính (1, 2, 3, Tập) bất kể nhãn trục là số hay văn bản. Khi bạn tạo đường xu hướng trong các biểu đồ này, Excel sẽ sử dụng các giá trị x giả định đó trong công thức đường xu hướng.
Các số được làm tròn trong phương trình đường xu hướng Excel
Để chiếm ít không gian hơn trong biểu đồ, Excel hiển thị rất ít chữ số có nghĩa trong phương trình đường xu hướng. Đẹp về mặt thiết kế, nó làm giảm đáng kể độ chính xác của công thức khi bạn cung cấp thủ công x các giá trị trong phương trình.
Một sửa chữa dễ dàng là hiển thị nhiều vị trí thập phân hơn trong phương trình. Ngoài ra, bạn có thể tính toán các hệ số bằng cách sử dụng công thức tương ứng với loại đường xu hướng của bạn và định dạng các ô công thức để chúng hiển thị đủ số vị trí thập phân. Đối với điều này, chỉ cần nhấp vào Tăng số thập phân nút trên Trang Chủ trong tab Con số nhóm.
Đó là cách bạn có thể tạo các loại đường xu hướng khác nhau trong Excel và nhận phương trình của chúng. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
Bạn cũng có thể quan tâm:
Source link
The post Các loại đường xu hướng, phương trình và công thức Excel appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/cac-loai-duong-xu-huong-phuong-trinh-va-cong-thuc-excel.html
Không có nhận xét nào:
Đăng nhận xét