Vẽ và phân tích biểu đồ xu hướng với Data Analysis trong Excel

Đề bài

Cho sẵn bảng số liệu sau, hãy vẽ biểu đồ và phân tích xu hướng của doanh thu theo các thời kỳ

Bước 1: Vẽ biểu đồ

Chọn vùng dữ liệu từ A2 đến M2

Bấm chọn tab Insert / Recommended Charts rồi chọn biểu đồ dạng đường biểu diễn

Sau khi tạo được biểu đồ, chúng ta thiết lập các chức năng cho biểu đồ như sau:

Axes: trục tung, trục hoành: thường biểu đồ sẽ có sẵn cả 2 trục này

Axis Titles: Tên của các trục. Chúng ta thêm vào phần tên của các trục để biểu đồ dễ hiểu hơn. Trục nằm ngang (trục hoành) là biểu diễn các tháng từ tháng 0 (quy ước là số 1 trên biểu đồ) tới tháng 11 (quy ước là số 12 trên biểu đồ); trục thẳng đứng (trục tung) là biểu diễn giá trị của doanh thu

Chart Title: tên của biểu đồ. Chúng ta xác định vị trí đặt tên của biểu đồ cho phù hợp

Gridlines: Đường kẻ mờ trong biểu đồ để dóng các trục. Chúng ta có thể chọn 2 nội dung là Primal Major Horizontal + Primal Major Vertical (đường kẻ mờ thưa hơn để dễ nhìn)

Legend: là ghi chú các đường biểu diễn. Chúng ta xác định vị trí đặt phần ghi chú này cho phù hợp

Đây là mẫu biểu đồ sau khi đã thực hiện các chức năng đầy đủ:

Bước 2: Tạo đường xu hướng

Bấm chọn (click chuột trái) đường biểu diễn doanh thu, tại đường này chúng ta click chuột phải và chọn Add Trendline (đường xu hướng)

Kết quả của đường xu hướng cho chúng ta 1 đường xu hướng kẻ mờ trong biểu đồ, và phía bên trái xuất hiện một mục Format Trendline để chúng ta định dạng cho đường xu hướng này

Như vậy chúng ta đã tạo được đường xu hướng trên biểu đồ rồi. Thật đơn giản phải không nào?

Bước 1: Mở Add-in Analysis ToolPak

Mở tab Developer, chọn mục Excel Add-ins => Đánh dấu chọn vào ô Analysis ToolPak và bấm ok

Chọn vùng B2:M2 trong bảng dữ liệu ban đầu, vào tab Data, chọn chức năng Data Analysis

Trong cửa sổ Data Analysis, chọn mục Moving Average (đường trung bình, đường xu hướng) rồi bấm ok

Thiết lập các chỉ tiêu trong Moving Average như sau:

Input Range: dữ liệu đầu vào để phân tích => vùng dữ liệu ban đầu, là chỉ tiêu doanh thu B2:M2

Interval: Số kỳ phân tích => Ví dụ chúng ta phân tích trung bình mỗi 6 tháng

Output Range: Nơi trích xuất dữ liệu kết quả phân tích => Chọn ô B3

Chúng ta thu được kết quả như sau:

Số kỳ từ 0 đến 4 (ứng với thứ tự từ 1 đến 5) có lỗi #N/A bởi chúng ta phân tích theo Interval = 6, do đó tại thời điểm số kỳ <6 thì sẽ chưa đủ cơ sở phân tích nên báo lỗi. Lỗi này chúng ta có thể hiểu được nên có thể chấp nhận nó.

Bước 3: Biểu diễn chỉ tiêu phân tích vào biểu đồ

Bấm chọn biểu đồ (1), chúng ta chọn tới tab Design (2) trong phần Chart Tools, chọn mục Select Data (3) => Mở cửa sổ Select Data Source

Trong mục Chart data range: Sửa nội dung chọn thành =Sheet1!$A$2:$M$3 (lấy thêm cả dòng 3, cho đối tượng số kỳ = 6)

Khi đó ta có biểu đồ như sau:

Click chọn đường xu hướng, sau đó bấm chuột phải và chọn Format Trend để thực hiện việc phân tích như sau:

Trong phần Format Trendline / Trenline Options => Chọn Moving Average

Khi thay đổi giá trị Period (số kỳ) ở mục Moving Average, chúng ta có thể thấy đường xu hướng biểu diễn trung bình giữa các kỳ sẽ thay đổi theo.

Khi số kỳ là 6 thì tương ứng với đường xu hướng tạo ra bởi chức năng Data Analysis

Như vậy chúng ta có thể theo dõi được xu hướng thay đổi theo từng kỳ. Và với chức năng Data Analysis chúng ta có thể hiểu được Excel đã tính các chỉ tiêu này như thế nào để có thể biểu diễn được các chỉ tiêu xu hướng.

Số kỳ càng nhỏ thì chúng ta sẽ có xu hướng càng sát với số liệu ban đầu (biên độ đi lên, đi xuống nhiều và thay đổi thường xuyên, mang tính cục bộ và giai đoạn ngắn)

Số kỳ càng lớn thì chúng ta càng nắm được xu hướng chung của toàn bộ quá trình là đang đi lên hay đi xuống (biên độ thay đổi ít, mang tính tổng thể toàn bộ quá trình)

Khi phân tích, chúng ta nên phân tích cho 1 vài đường xu hướng, với số kỳ dao động từ nhỏ đến lớn (ví dụ khoảng 3 xu hướng, cho số kỳ là 2, 4. 6) để có thể có thêm căn cứ xét đoán xem xu hướng biến động thế nào

Như vậy bài viết đã Hướng dẫn cách vẽ và phân tích biểu đồ xu hướng trong Excel với Data Analysis. Chỉ cần vài thao tác đơn giản, bạn đã có thể  vẽ và phân tích biểu đồ một cách nhanh chóng. Chúc các bạn thực hiện thao tác thành công!