Hàm Excel TREND và các cách khác để phân tích xu hướng

Hướng dẫn cho thấy cách tính xu hướng trong Excel bằng cách sử dụng hàm TREND, cách tạo xu hướng trên biểu đồ và hơn thế nữa.

Ngày nay khi công nghệ, thị trường và nhu cầu của khách hàng đang thay đổi nhanh chóng, điều quan trọng là bạn phải di chuyển theo xu hướng và không chống lại chúng. Phân tích xu hướng có thể giúp bạn xác định các mẫu cơ bản trong các chuyển động dữ liệu trong quá khứ và hiện tại và hành vi trong tương lai của dự án.

Hàm Excel TREND

Hàm Excel TREND được sử dụng để tính toán đường xu hướng tuyến tính thông qua một tập hợp các giá trị y phụ thuộc nhất định và, tùy chọn, một tập hợp các giá trị x độc lập và trả về các giá trị dọc theo đường xu hướng.

Ngoài ra, hàm TREND có thể mở rộng đường xu hướng trong tương lai để dự đoán các giá trị y phụ thuộc cho một tập hợp các giá trị x mới.

Cú pháp của hàm Excel TREND như sau:

TREND (được biết đến, [known_x’s], [new_x’s], [const])

Ở đâu:

Được biết (bắt buộc) – một tập hợp các giá trị y phụ thuộc mà bạn đã biết.

Được biết (tùy chọn) – một hoặc nhiều bộ giá trị x độc lập.

  • Nếu chỉ có một biến x được sử dụng, thì có thể biết phạm vi của bất kỳ hình dạng nào nhưng kích thước bằng nhau.
  • Nếu một vài biến x được sử dụng, thì phải biết một vectơ (một cột hoặc một hàng).
  • Nếu được bỏ qua, biết_x’s được coi là mảng các số sê-ri {1,2,3, …}.

Mới (tùy chọn) – một hoặc nhiều bộ giá trị x mới mà bạn muốn tính xu hướng.

  • Nó phải có cùng số lượng cột hoặc hàng như đã biết.
  • Nếu được bỏ qua, nó được coi là bằng với know_x’s.

hăng sô (tùy chọn) – một giá trị logic chỉ định cách hằng số một trong phương trình y = bx + a nên được tính.

  • Nếu TRUE hoặc bỏ qua, hằng số một được tính toán bình thường.
  • Nếu SAI, hằng số một bị buộc về 0 và các giá trị b được điều chỉnh để phù hợp với phương trình y = bx.

Cách hàm TREND tính toán đường xu hướng tuyến tính

Hàm Excel TREND tìm dòng phù hợp nhất với dữ liệu của bạn bằng cách sử dụng phương pháp bình phương nhỏ nhất. Phương trình của dòng như sau.

Đối với một phạm vi giá trị x:

y = bx + a

Đối với nhiều phạm vi giá trị x:

y = b1x1 + b2x2 + Cược + bnxn + một

Ở đâu:

  • y – biến phụ thuộc bạn đang cố gắng tính toán.
  • x – biến độc lập bạn đang sử dụng để tính toán y.
  • a – chặn (cho biết đường thẳng cắt trục y và bằng giá trị của y khi x bằng 0).
  • b – độ dốc (biểu thị độ dốc của đường).

Phương trình cổ điển này cho dòng phù hợp nhất cũng được sử dụng bởi Chức năng LINESTphân tích hồi quy tuyến tính.

Hàm TREND như một công thức mảng

Để trả về nhiều giá trị y mới, hàm TREND phải được nhập dưới dạng công thức mảng. Đối với điều này, chọn tất cả các ô mà bạn muốn kết quả xuất hiện, nhập công thức và nhấn Ctrl + Shift + Enter để hoàn thành nó Khi bạn thực hiện việc này, công thức sẽ được đặt trong {dấu ngoặc nhọn}, đây là dấu hiệu trực quan của công thức mảng. Vì các giá trị mới được trả về dưới dạng một mảng, bạn sẽ không thể chỉnh sửa hoặc xóa chúng riêng lẻ.

Ví dụ về công thức Excel TREND

Thoạt nhìn, cú pháp của hàm TREND có vẻ quá phức tạp, nhưng các ví dụ sau sẽ giúp mọi việc dễ dàng hơn nhiều.

Công thức TREND để phân tích xu hướng chuỗi thời gian trong Excel

Giả sử bạn đang phân tích một số dữ liệu trong một khoảng thời gian liên tiếp và bạn muốn phát hiện ra một xu hướng hoặc mẫu.

Trong ví dụ này, chúng tôi có các số tháng (giá trị x độc lập) trong A2: A13 và số bán hàng (giá trị y phụ thuộc) trong B2: B13. Dựa trên dữ liệu này, chúng tôi muốn xác định xu hướng chung trong chuỗi thời gian bỏ qua các ngọn đồi và thung lũng.

Để hoàn thành, chọn phạm vi C2: C13, nhập công thức bên dưới và nhấn Ctrl + Shift + Enter để hoàn thành nó:

=TREND(B2:B13,A2:A13)

Để vẽ đường xu hướng, chọn giá trị bán hàng và xu hướng (B1: C13) và lập biểu đồ đường (Chèn tab> Biểu đồ nhóm> Biểu đồ đường hoặc khu vực).

Kết quả là, bạn có cả các giá trị số cho dòng phù hợp nhất được trả về bởi công thức và biểu thị trực quan của các giá trị đó trong biểu đồ:

Công thức Excel TREND để phân tích xu hướng chuỗi thời gian

Dự đoán xu hướng trong tương lai

Để dự đoán xu hướng cho tương lai, bạn chỉ cần đưa một tập hợp các giá trị x mới vào công thức TREND của bạn.

Đối với điều này, chúng tôi mở rộng chuỗi thời gian của mình với một vài số tháng nữa và thực hiện dự báo xu hướng bằng cách sử dụng công thức này:

=TREND(B2:B13,A2:A13,A14:A17)

Ở đâu:

  • B2: B13 được biết đến
  • A2: A13 được biết đến
  • A14: A17 là new_x

Nhập công thức trên vào các ô C14: C17 và nhớ nhấn Ctrl + Shift + Enter để hoàn thành nó một cách thích hợp. Sau đó, tạo biểu đồ đường mới cho tập dữ liệu mở rộng (B1: C17).

Ảnh chụp màn hình bên dưới hiển thị các giá trị y mới được tính toán và đường xu hướng mở rộng:

Công thức để dự đoán một xu hướng trong tương lai

Công thức Xu hướng Excel cho nhiều bộ giá trị x

Trong trường hợp khi bạn có hai hoặc nhiều bộ giá trị x độc lập, hãy nhập chúng vào các cột riêng biệt và cung cấp toàn bộ phạm vi đó cho được biết đến đối số của fucntion TREND.

Ví dụ: với know_x1 các giá trị trong B2: B13, đã biết_x2 các giá trị trong các giá trị C2: C13 và know_y trong D2: D13, bạn sử dụng công thức sau để tính xu hướng:

=TREND(D2:D13,B2:C13)

Ngoài ra, bạn có thể nhập new_x1 và new_x2 các giá trị trong B14: B17 và C14: C17 tương ứng và nhận các giá trị y được chiếu theo công thức này:

=TREND(D2:D13,B2:C13,B14:C17)

Nếu được nhập chính xác (với Ctrl + Shift + Enter phím tắt), các công thức xuất kết quả sau:

Công thức TREND cho nhiều bộ giá trị x

Hàm TREND là phương thức chiếu xu hướng phổ biến nhất nhưng không phải là duy nhất trong Excel. Dưới đây tôi sẽ mô tả ngắn gọn một vài kỹ thuật khác.

DỰ BÁO Excel vs TREND

“Xu hướng” và “dự báo” là những khái niệm rất gần nhau, nhưng vẫn có một sự khác biệt:

  • Khuynh hướng là một cái gì đó đại diện cho những ngày hiện tại hoặc quá khứ. Ví dụ: bằng cách phân tích các số bán hàng gần đây, bạn có thể xác định xu hướng dòng tiền và hiểu cách doanh nghiệp của bạn đã hoạt động và hiện đang hoạt động.
  • Dự báo là một cái gì đó liên quan đến tương lai. Ví dụ: bằng cách phân tích dữ liệu lịch sử, bạn có thể dự đoán các thay đổi trong tương lai và dự đoán nơi các hoạt động kinh doanh hiện tại sẽ đưa bạn đến.

Về mặt Excel, sự khác biệt này không quá rõ ràng vì hàm TREND không chỉ có thể tính toán các xu hướng hiện tại mà còn trả về các giá trị y trong tương lai, tức là dự báo xu hướng.

Sự khác biệt giữa TREND và FORECAST trong Excel như sau:

  • Hàm FORECAST chỉ có thể dự đoán các giá trị trong tương lai dựa trên các giá trị hiện có. Hàm TREND có thể tính toán cả xu hướng hiện tại và tương lai.
  • Hàm FORECAST được sử dụng như một công thức thông thường và trả về một giá trị y mới cho một giá trị x mới. Hàm TREND được sử dụng làm công thức mảng và tính toán nhiều giá trị y cho nhiều giá trị x.

Khi được sử dụng để dự báo chuỗi thời gian, cả hai chức năng đều tạo ra như nhau tuyến tính khuynh hướng/dự báo bởi vì tính toán của họ dựa trên cùng một phương trình.

Vui lòng xem ảnh chụp màn hình bên dưới và so sánh kết quả được trả về bởi các công thức sau:

=TREND(B2:B13,A2:A13,A14:A17)

=FORECAST(A14,$B$2:$B$13,$A$2:$A$13)

Công thức FORECAST và TREND trong Excel

Để biết thêm thông tin, xin vui lòng xem Sử dụng hàm FORECAST trong Excel.

Vẽ đường xu hướng để hình dung xu hướng

Đường xu hướng thường được sử dụng để quan sát xu hướng chung trong dữ liệu hiện tại của bạn cũng như dự đoán các chuyển động dữ liệu trong tương lai.

Để thêm xu hướng vào biểu đồ hiện có, bấm chuột phải vào chuỗi dữ liệu, rồi bấm Thêm Trendline Điều này sẽ tạo mặc định đường xu hướng tuyến tính cho dữ liệu hiện tại và mở Định dạng Xu hướng khung nơi bạn có thể chọn một loại đường xu hướng khác.

Đến dự báo một xu hướng, chỉ định số lượng thời gian theo Dự báo trên Định dạng Xu hướng khung:

  • Để dự đoán xu hướng trong tương lai, hãy nhập số lượng thời gian trong Ở đằng trước cái hộp.
  • Để ngoại suy một xu hướng vào quá khứ, hãy nhập số mong muốn vào Phía sau cái hộp.

Đến hiển thị phương trình đường xu hướng, kiểm tra Hiển thị phương trình trên biểu đồ cái hộp. Để chính xác hơn, bạn có thể hiển thị nhiều chữ số hơn trong phương trình đường xu hướng.

Tạo đường xu hướng trong Excel

Như được hiển thị trong hình bên dưới, kết quả của phương trình đường xu hướng hoàn toàn phù hợp với các số được trả về bởi các công thức FORECAST và TREND:

Phương trình đường thẳng tuyến tính

Để biết thêm thông tin, xin vui lòng xem Cách thêm đường xu hướng trong Excel.

Xu hướng mượt mà với đường trung bình

Một kỹ thuật đơn giản khác có thể giúp bạn thể hiện xu hướng được gọi là di chuyển trung bình (còn gọi là Trung bình lăn hoặc là chạy trung bình). Phương pháp này làm mịn các biến động ngắn hạn trong chuỗi thời gian mẫu và làm nổi bật các mô hình hoặc xu hướng dài hạn hơn.

Bạn có thể tính trung bình di chuyển theo cách thủ công với các công thức của riêng bạn hoặc để Excel tự động tạo một đường xu hướng cho bạn.

Để hiển thị một đường xu hướng trung bình trên biểu đồ, đây là những gì bạn cần làm:

  1. Bấm chuột phải vào chuỗi dữ liệu và bấm Thêm đường xu hướng.
  2. Trên Định dạng Xu hướng khung, chọn Di chuyển trung bình và chỉ định số lượng thời gian mong muốn.

Di chuyển đường xu hướng trung bình

Đó là cách bạn sử dụng hàm TREND để tính xu hướng trong Excel. Để xem kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống mẫu của chúng tôi Sổ làm việc Excel TREND. 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

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *