Cách tạo biểu đồ thác nước trong Excel

Hướng dẫn này sẽ trình bày cách tạo biểu đồ thác nước trong tất cả các phiên bản Excel: 2007, 2010, 2013, 2016 và 2022.

Biểu đồ thác nước (còn được gọi là biểu đồ cầu, biểu đồ gạch bay, biểu đồ bậc thang hoặc biểu đồ Mario) là một biểu đồ phân tích trực quan tác động tích lũy mà một loạt các giá trị âm hoặc dương liên tiếp đã đóng góp vào kết quả cuối cùng.

Thông thường, cột đầu tiên và cột cuối cùng đại diện cho các giá trị ban đầu và cuối cùng được phân tách bằng các điều chỉnh trung gian mô tả sự liên tiếp của các bước cần thực hiện để đi từ điểm A đến điểm B.

Loại biểu đồ này đã được sử dụng rộng rãi bởi các chuyên gia trong tất cả các ngành công nghiệp chính để thực hiện phân tích định lượng, từ giải mã báo cáo tài chính đến theo dõi hàng tồn kho của cửa hàng.

Biểu đồ cầu được giới thiệu là loại biểu đồ tích hợp sẵn chỉ trong Excel 2016, có nghĩa là người dùng các phiên bản Excel trước không có lựa chọn nào khác ngoài việc tạo biểu đồ từ đầu.

Vì lý do đó, trong hướng dẫn này, bạn sẽ học cách tạo biểu đồ Mario này theo cách thủ công từ đầu và bằng cách chuyển sang loại biểu đồ gốc thực hiện tất cả công việc khó khăn cho bạn.

Bắt đầu

Ngay ra khỏi cổng, chúng ta cần đưa một số dữ liệu thô vào bức tranh. Vì vậy, với mục đích minh họa, giả sử bạn bắt đầu phân tích dòng tiền ròng hàng quý của một công ty hư cấu nào đó.

Với tất cả những điều đó, hãy xem xét dữ liệu sau:

Để giúp bạn có thể điều chỉnh biểu đồ theo nhu cầu của riêng mình, hãy nói chi tiết hơn về bảng:

• Khoảng thời gian: Cột này đại diện cho một chuỗi các giá trị âm và dương được vẽ trên biểu đồ.
• Dòng tiền ròng: Cột này được tạo thành từ các giá trị tương ứng. Đầu tiên (B2) và cuối cùng (B13) ô chứa giá trị ban đầu và giá trị cuối cùng trong khi các số còn lại ở giữa đại diện cho các thay đổi định lượng theo thời gian.

Bây giờ bạn đã biết các yếu tố cơ bản của biểu đồ, chúng ta có thể chuyển sang phần thực tế.

Cách tạo biểu đồ thác nước trong Excel 2016+

Đầu tiên, hãy để tôi chỉ cho bạn một giải pháp cho những người đang tìm kiếm con đường ít kháng cự nhất.

Bằng cách sử dụng mẫu tích hợp sẵn, được cung cấp sẵn, bạn sử dụng Excel 2016 hoặc 2022-nó sẽ không khiến bạn mất nhiều hơn một vài bước đơn giản để thiết lập biểu đồ thác nước.

Tuy nhiên, tất cả đều đi kèm với giá của các tùy chọn tùy chỉnh hạn chế và các vấn đề tương thích tiềm ẩn; các phiên bản cũ hơn của Excel chỉ đơn giản là không thể xử lý các loại biểu đồ gốc gần đây hơn.

Điểm mấu chốt: bất cứ khi nào bạn định gửi một biểu đồ đi xung quanh, việc xây dựng biểu đồ theo cách thủ công luôn là cách an toàn hơn.

Bước # 1: Vẽ biểu đồ thác nước.

Để bắt đầu, hãy tạo biểu đồ thác nước mặc định dựa trên dữ liệu thực tế của bạn. Cái hay của phương pháp này là bạn không phải nhảy qua bất kỳ vòng lặp nào:

1. Đánh dấu tất cả dữ liệu (A1: B13).
2. Đi đến Chèn chuyển hướng.
3. Chọn “Chèn biểu đồ Thác nước, Phễu, Cổ phiếu, Bề mặt hoặc Radar" cái nút.
4. Lựa chọn "Thác nước."

Ngay lập tức, biểu đồ gạch bay của bạn sẽ xuất hiện:

Bước # 2: Đặt cột tổng phụ và tổng.

Bước tiếp theo của chúng tôi là tách giá trị ban đầu và giá trị cuối cùng khỏi “khối hình bay”.

Nhấp đúp và sau đó nhấp chuột phải vào các cột đại diện cho các điểm dữ liệu “Số dư chuyển đổi" và "Số dư Hiện tại" và lựa chọn "Đặt là Tổng.”

Về mặt kỹ thuật, bạn có thể muốn dừng ngay tại đó và để nguyên biểu đồ. Nhưng biểu đồ tầng này không cho chúng ta biết gì về dữ liệu được vẽ trên đó.

Tại sao giải quyết cho ít hơn? Thay vào đó, bạn có thể cải thiện đáng kể biểu đồ Mario mới tạo của mình bằng cách thay đổi một vài thứ ở đây và ở đó.

Bước # 3: Thay đổi bảng màu.

Một cách hiệu quả để cải thiện biểu đồ của bạn là để bảng màu phù hợp với bạn chứ không phải ngược lại. Tô màu lại chuỗi dữ liệu theo cách giúp truyền tải nội dung của biểu đồ.

1. Bấm đúp và sau đó bấm chuột phải vào mục chú giải đại diện Chuỗi "Giảm".
2. Chọn “Lấp đầy" cái nút.
3. Nhặt màu đỏ từ bảng màu.

Theo cách tương tự, tô màu lại Chuỗi "Tăng" sang màu xanh lá cây và Chuỗi "Tổng số" sang màu xanh lam. Khi đó, biểu đồ tầng của bạn sẽ trông như thế này:

Bước # 4: Điều chỉnh phạm vi trục tung cho phù hợp với dữ liệu thực tế của bạn.

Để phóng to các cột nổi để biết thêm chi tiết, hãy sửa đổi tỷ lệ trục tung. Nhấp chuột phải vào trục tung chính và nhấp vào “Định dạng trục.”

bên trong Định dạng trục ngăn tác vụ, hãy làm theo các bước đơn giản sau:

1. Chuyển sang Tùy chọn trục chuyển hướng.
2. Đặt Giới hạn tối thiểu đến "50,000.”
3. Đặt Giới hạn tối đa đến "110,000.”

Bước # 5: Tinh chỉnh các chi tiết.

Thông thường, càng có nhiều yếu tố cấu thành biểu đồ, biểu đồ càng lộn xộn. Vì vậy, hãy loại bỏ các yếu tố không cần thiết bằng cách loại bỏ chú giải và đường lưới.

Nhấp chuột phải vào chú giải biểu đồ và chọn “Xóa bỏ”Từ menu bật lên. Lặp lại quy trình tương tự cho các đường lưới.

Cuối cùng, thay đổi tiêu đề biểu đồ và bạn có thể gọi nó là một ngày!

Cách tạo biểu đồ thác nước trong Excel 2007, 2010 và 2013

Hướng dẫn này sẽ kết thúc ngay tại đây nếu phương pháp hiển thị ở trên tương thích với tất cả các phiên bản Excel. Thật không may, đó không phải là trường hợp.

Vì lý do đó, bạn có thể muốn xem xét vẽ biểu đồ theo cách thủ công từ đầu, ngay cả khi bạn sử dụng Excel 2016 hoặc 2022.

Rõ ràng, cần nhiều thời gian hơn để ghép biểu đồ lại với nhau theo cách đó, nhưng phần thưởng xứng đáng với nỗ lực.

Đến cuối phần này, bạn sẽ học cách tạo biểu đồ cầu động, có thể tùy chỉnh này tương thích với tất cả các phiên bản Excel:

Không cần thêm lời khuyên nào nữa, hãy bắt đầu cho quả bóng lăn.

Bước # 1: Chuẩn bị dữ liệu biểu đồ.

Nói thẳng ra, bước này là khó nhất.

Nói một cách đơn giản, biểu đồ thác nước tùy chỉnh của chúng tôi sẽ được tạo dựa trên biểu đồ cột xếp chồng lên nhau. Nhưng để giải quyết vấn đề, chúng ta cần thêm một số dữ liệu trợ giúp bổ sung vào phương trình.

Để thuận tiện cho bạn, hãy xem nhanh cách sắp xếp dữ liệu biểu đồ của bạn vào cuối giai đoạn chuẩn bị.

Đối với những người đang bước những bước đầu tiên trên con đường thành thạo Excel, bảng này có vẻ rất khó khăn, nhưng trên thực tế, bất kỳ ai cũng có thể xử lý công việc bằng cách làm theo các hướng dẫn chi tiết được nêu dưới đây.

Hãy bắt đầu bằng cách thêm ba cột trợ giúp có tên “Vô hình” (cột B), “Tăng” (cột C), và "Giảm bớt” (cột D), sẽ chứa các giá trị giả để định vị dữ liệu thực tế của bạn trên biểu đồ trong tương lai.

Và nếu cột Tăng và Giảm bớt khá tự giải thích, vấn đề là gì cột ẩn? Về cơ bản, các giá trị này sẽ tạo thành cơ sở vô hình của biểu đồ cột xếp chồng lên nhau trong tương lai để nâng các giá trị thực tế lên độ cao thích hợp.

Hãy đối phó với cột tăng đầu tiên: Nhập công thức sau vào ô C2 và kéo nó xuống bằng cách sử dụng tay cầm điền để thực thi nó cho các ô còn lại (C3: C13):

1	= IF (E2> 0, E2, 0)

Bây giờ, hãy nhập công thức nhân bản vào ô đầu tiên của cột Giảm (cột D) và sao chép nó xuống:

1	= IF (E2 <= 0, -E2, 0)

Hai xuống, một để đi. Sau khi tìm thấy dữ liệu biểu đồ cần thiết, bây giờ bạn có thể thiết lập cột Ẩn để lập biểu đồ chính xác biến động dòng tiền bằng cách nhập công thức sau vào ô thứ hai của cột (B3) và sao chép nó xuống B12:

1	= B2 + C2-D3

LƯU Ý: Bạn cần để lại ô đầu tiên và ô cuối cùng (B2 và B13) của cột trống!

Đừng quên định dạng tất cả dữ liệu biểu đồ dưới dạng tiền tệ (Trang chủ> Nhóm số> Đơn vị tiền tệ).

Tại thời điểm này, đây là cách bảng mở rộng của bạn sẽ trông như thế nào:

Là một điều chỉnh cuối cùng, hãy thiết lập thêm một cột khác có tên là “Nhãn”Sẽ chứa các nhãn dữ liệu tùy chỉnh ưa thích.

Khi bạn đã thiết lập cột này, hãy nhập công thức này vào ô thứ hai của nhãn cột (F3) và sao chép nó xuống, để lại ô đầu tiên (F2) và ô cuối cùng (F13) trống:

1	= IF (E3 <= 0, TEXT (-E3, "$ #, ##") & CHAR (10) & TEXT (-E3 / SUM (B3: D3), "(#% ↓)"), TEXT (E3, "$ #, ##") & CHAR (10) & TEXT (E3 / SUM (B3: D3), "(#% ↑)"))

Những người làm việc với các định dạng số khác nên áp dụng công thức này thay thế:

1	= IF (E3 <= 0, TEXT (-E3, "#, ##") & CHAR (10) & TEXT (-E3 / SUM (B3: D3), "(#% ↓)"), TEXT (E3, " #, ## ") & CHAR (10) & TEXT (E3 / SUM (B3: D3)," (#% ↑) "))

Bước # 2: Xây dựng biểu đồ cột xếp chồng.

Chúng tôi đã vượt qua khó khăn, vì vậy thời gian để xây dựng biểu đồ thác nước của chúng tôi cuối cùng đã đến.

1. Chọn tất cả dữ liệu trong các cột Khoảng thời gian, Vô hình, Gia tăng, và Giảm bớt (A1: D13).
2. Đi đến Chèn chuyển hướng.
3. Nhấp chuột "Chèn biểu đồ cột hoặc thanh.”
4. Lựa chọn "Cột xếp chồng.”

Excel sẽ tập hợp biểu đồ đơn giản này và cuối cùng sẽ được chuyển thành biểu đồ thác nước tuyệt đẹp:

Bước # 3: Ẩn sê-ri “Ẩn mặt”.

Trước khi chúng ta chuyển sang phần còn lại của biểu đồ, hãy ẩn chuỗi dữ liệu bên dưới đẩy các cột nổi lên trên cùng. Nhấp chuột phải vào bất kỳ cột nào trong số các cột màu xanh lam đại diện cho Loạt bài "Vô hình" và mở Định dạng chuỗi dữ liệu bảng công tác.

Khi đó, hãy ẩn các cột trợ giúp:

1. Đi đến Điền vào dòng tab-the Lấp đầy trong các phiên bản Excel cũ hơn.
2. Dưới "Lấp đầy," lựa chọn "Không có điền.”
3. Dưới "Biên giới," nhấp chuột "Không có dòng nào.”

Bước # 4: Điều chỉnh bảng màu.

Trong cùng một Định dạng chuỗi dữ liệu ngăn tác vụ, tô màu lại từng chuỗi dữ liệu bằng cách làm theo các bước tương tự được hiển thị ở trên (Tùy chọn chuỗi > Lấp đầy > Điền đặc > Màu sắc) để nó có thể được hiểu một cách trực quan trong nháy mắt:

1. Vì Chuỗi "Giảm", nhặt màu đỏ.
2. Vì Chuỗi "Tăng", nhặt màu xanh lá.
3. Ngoài ra, nhấp đúp vào các cột “Số dư chuyển đổi” và “Số dư Hiện tại” và tô màu lại chúng màu xanh dương.

Đây là những gì bạn sẽ có vào thời điểm này:

Bước # 5: Thay đổi độ rộng khoảng trống thành “20%”.

Bây giờ, hãy tăng chiều rộng của các cột để có cảm nhận hình ảnh tốt hơn.

1. Mở Định dạng chuỗi dữ liệu ngăn tác vụ (bấm chuột phải vào một trong các chuỗi dữ liệu và chọn Định dạng chuỗi dữ liệu) và chuyển sang Tùy chọn chuỗi chuyển hướng.
2. Thay đổi "Chiều rộng khoảng cách" đến "20%.”

Bước # 6: Điều chỉnh phạm vi trục tung.

Một lần nữa, tỷ lệ trục nên được sửa đổi để phóng to dữ liệu kỹ hơn. Để tóm tắt nhanh quá trình cho những người đã bỏ qua phần đầu tiên của hướng dẫn này, đây là cách bạn thực hiện:

1. Nhấp chuột phải vào trục tung và chọn “Định dạng trục.”
2. Trong ngăn tác vụ, hãy mở Tùy chọn trục chuyển hướng.
3. Dưới "Bounds," thay đổi "Tối thiểu" đến "50,000" và "Tối đa" đến "110,000.”

Bước # 7: Thêm và định vị các nhãn dữ liệu tùy chỉnh.

Các nhãn dữ liệu tùy chỉnh đó đã chờ đợi quá lâu nên cuối cùng chúng ta hãy đưa chúng vào sử dụng. Nhấp chuột phải vào bất kỳ cột nào và chọn “Thêm nhãn dữ liệu.”

Ngay lập tức, các nhãn dữ liệu mặc định được gắn với các giá trị của trình trợ giúp sẽ được thêm vào biểu đồ:

Nhưng đó không chính xác là những gì chúng ta đang tìm kiếm. Để khắc phục sự cố, hãy thay thế thủ công các nhãn mặc định bằng các giá trị tùy chỉnh mà bạn đã chuẩn bị trước.

1. Bấm đúp vào nhãn dữ liệu bạn muốn thay đổi.
2. Vào "=" vào Thanh công thức.
3. Đánh dấu một ô tương ứng trong nhãn cột.

Thay thế các nhãn dữ liệu còn lại bằng cách lặp lại quy trình tương tự cho mọi cột nổi.

Khi đó, bạn sẽ cần phải kéo các nhãn đến vị trí tương ứng của chúng: tất cả các giá trị dương phải được đặt phía trên các cột màu xanh lá cây tương ứng trong khi các giá trị âm phải được di chuyển dưới các cột màu đỏ.

Sau khi bạn đã giải quyết thành công các nhãn, biểu đồ Mario của bạn sẽ biến đổi thành một cái gì đó như sau:

Bước # 8: Làm sạch vùng biểu đồ.

Cuối cùng, xóa chú giải biểu đồ và đường lưới không mang lại gì cho bảng (Kích chuột phải > Xóa bỏ). Thay đổi tiêu đề biểu đồ và ở đó bạn có biểu đồ thác nước của mình!