Cách xây dựng biểu đồ thác nước

Tôi ngày càng thường xuyên gặp gỡ báo cáo của các công ty khác nhau và nghe các thực tập sinh yêu cầu giải thích cách xây dựng sơ đồ phân tầng của các sai lệch - nó cũng là một “thác nước”, nó cũng là một “thác nước”, nó cũng là một “cây cầu ”, Nó cũng là một“ cầu nối ”, v.v. Nó trông giống như thế này:

Nhìn từ xa, nó thực sự giống như một thác nước trên sông núi hoặc một cây cầu treo - ai nhìn thấy gì 🙂

Điểm đặc biệt của sơ đồ như vậy là:

• Chúng ta thấy rõ ràng giá trị ban đầu và cuối cùng của tham số (cột đầu tiên và cột cuối cùng).
• Những thay đổi tích cực (tăng trưởng) được hiển thị bằng một màu (thường là màu xanh lá) và những người tiêu cực (từ chối) đối với những người khác (thường là đỏ).
  
• Đôi khi biểu đồ cũng có thể chứa các cột tổng phụ (màu xámhạ cánh trên các cột trục x).
  

Trong cuộc sống hàng ngày, các sơ đồ như vậy thường được sử dụng trong các trường hợp sau:

• Hình ảnh màn hình động lực học bất kỳ quá trình nào trong thời gian: dòng tiền (cash-flow), các khoản đầu tư (chúng ta đầu tư vào một dự án và thu được lợi nhuận từ nó).
• Hình ảnh kế hoạch thực hiện (cột ngoài cùng bên trái trong sơ đồ là dữ kiện, cột ngoài cùng bên phải là kế hoạch, toàn bộ sơ đồ phản ánh quá trình của chúng ta để tiến tới kết quả mong muốn)
• Khi bạn cần hình ảnh hiển thị các yếu tốảnh hưởng đến tham số của chúng tôi (phân tích giai thừa của lợi nhuận - nó bao gồm những gì).
  

Có một số cách để xây dựng một biểu đồ như vậy - tất cả phụ thuộc vào phiên bản Microsoft Excel của bạn.

Phương pháp 1: Dễ nhất: Kiểu tích hợp sẵn trong Excel 2016 và mới hơn

Nếu bạn có Excel 2016, 2019 trở lên (hoặc Office 365), thì việc tạo biểu đồ như vậy không khó - các phiên bản Excel này đã có sẵn loại này theo mặc định. Chỉ cần chọn một bảng có dữ liệu và chọn trên tab Chèn (Chèn) Lệnh cascading (Thác nước):

Kết quả là, chúng ta sẽ nhận được một sơ đồ gần như được tạo sẵn:

Bạn có thể đặt ngay các màu tô mong muốn cho các cột âm và dương. Cách dễ nhất để làm điều này là chọn các hàng thích hợp Tăng lên и giảm trực tiếp trong chú giải và bằng cách nhấp chuột phải vào chúng, chọn lệnh Điền (Lấp đầy):

Nếu bạn cần thêm các cột có tổng phụ hoặc tổng cột cuối cùng vào biểu đồ, thì cách thuận tiện nhất là thực hiện việc này bằng cách sử dụng các hàm TIÊU ĐỀ (TIÊU ĐỀ) or UNIT (ĐỒNG Ý). Họ sẽ tính toán số tiền được tích lũy từ đầu bảng, đồng thời loại trừ khỏi nó các tổng số tương tự ở trên:

Trong trường hợp này, đối số đầu tiên (9) là mã của phép toán tổng kết toán học và đối số thứ hai (0) khiến hàm bỏ qua các tổng đã được tính toán cho các quý trước trong kết quả.

Sau khi thêm các hàng với tổng, nó vẫn chọn tổng số cột đã xuất hiện trên sơ đồ (thực hiện hai lần nhấp chuột liên tiếp vào cột) và bằng cách nhấp chuột phải vào chuột, chọn lệnh Đặt làm tổng số (Đặt làm tổng số):

Cột đã chọn sẽ nằm trên trục x và tự động đổi màu thành màu xám.

Trên thực tế, đó là tất cả - sơ đồ thác nước đã sẵn sàng:

Phương pháp 2. Phổ quát: cột vô hình

Nếu bạn có Excel 2013 hoặc các phiên bản cũ hơn (2010, 2007, v.v.), thì phương pháp được mô tả ở trên sẽ không hoạt động với bạn. Bạn sẽ phải đi vòng quanh và cắt bỏ biểu đồ thác nước bị thiếu khỏi một biểu đồ xếp chồng thông thường (tổng hợp các thanh chồng lên nhau).

Mẹo ở đây là sử dụng các cột chống trong suốt để nâng các hàng dữ liệu màu đỏ và xanh lục của chúng ta lên đúng chiều cao:

Để xây dựng một biểu đồ như vậy, chúng ta cần thêm một vài cột bổ trợ có công thức vào dữ liệu nguồn:

• Đầu tiên, chúng ta cần tách cột ban đầu của mình bằng cách tách các giá trị âm và dương thành các cột riêng biệt bằng cách sử dụng hàm IF (NẾU).  
• Thứ hai, bạn sẽ cần thêm một cột ở phía trước các cột núm vú giả, trong đó giá trị đầu tiên sẽ là 0 và bắt đầu từ ô thứ hai, công thức sẽ tính toán chiều cao của các cột hỗ trợ rất trong suốt đó.
  

Sau đó, nó vẫn chọn toàn bộ bảng ngoại trừ cột ban đầu Dòng chảy và tạo một biểu đồ xếp chồng thông thường trên Inset - Biểu đồ (Chèn - Biểu đồ cột):

Nếu bây giờ bạn chọn các cột màu xanh lam và làm cho chúng ẩn đi (nhấp chuột phải vào chúng - Định dạng hàng - Tô - Không tô), sau đó chúng tôi chỉ nhận được những gì chúng tôi cần. 

Ưu điểm của phương pháp này là sự đơn giản. Trong minuses - sự cần thiết phải đếm các cột phụ.

Phương pháp 3. Nếu chúng ta sa vào vận đỏ, mọi thứ càng khó khăn hơn

Thật không may, phương pháp trước chỉ hoạt động thích hợp cho các giá trị dương. Nếu ít nhất ở một số khu vực, thác nước của chúng ta đi vào vùng tiêu cực, thì độ phức tạp của nhiệm vụ sẽ tăng lên đáng kể. Trong trường hợp này, cần phải tính toán từng hàng (giả, xanh lục và đỏ) riêng biệt cho các phần âm và dương với các công thức:

Để không bị ảnh hưởng nhiều và không phải phát minh lại bánh xe, bạn có thể tải xuống một mẫu làm sẵn cho một trường hợp như vậy trong tiêu đề của bài viết này.

Phương pháp 4. Kỳ lạ: dải lên xuống

Phương pháp này dựa trên việc sử dụng một yếu tố đặc biệt ít được biết đến của biểu đồ phẳng (biểu đồ và đồ thị) - Dải lên xuống (Thanh trên xuống). Các dải này kết nối các điểm của hai biểu đồ theo từng cặp để hiển thị rõ ràng điểm nào trong hai điểm cao hơn hoặc thấp hơn, được sử dụng tích cực khi hình dung thực tế của kế hoạch:

Dễ dàng nhận ra rằng nếu chúng ta loại bỏ các đường của biểu đồ và chỉ để lại các dải lên xuống trên biểu đồ, thì chúng ta sẽ nhận được cùng một “thác nước”.

Để có cấu trúc như vậy, chúng ta cần thêm hai cột bổ sung vào bảng của mình bằng các công thức đơn giản sẽ tính toán vị trí của hai biểu đồ vô hình được yêu cầu:

Để tạo "thác nước", bạn cần chọn một cột có tháng (đối với chữ ký dọc theo trục X) và hai cột bổ sung Lịch trình 1 и Lịch trình 2 và xây dựng một biểu đồ thông thường cho những người mới bắt đầu sử dụng Chèn - Đồ thị (Chèn - Dòng Сhart):

Bây giờ, hãy thêm các dải lên xuống vào biểu đồ của chúng tôi:

• Trong Excel 2013 và mới hơn, điều này phải được chọn trên tab xây dựng Lệnh Thêm phần tử biểu đồ - Biên độ tăng-giảm (Thiết kế - Thêm phần tử biểu đồ - Thanh lên xuống)
• Trong Excel 2007-2010 - chuyển đến tab Bố cục - Thanh tăng trước-Giảm dần (Bố cục - Thanh trên xuống)

Biểu đồ sau đó sẽ trông giống như sau:

Nó vẫn còn để chọn các đồ thị và làm cho chúng trong suốt bằng cách nhấp vào chúng lần lượt bằng nút chuột phải và chọn lệnh Định dạng chuỗi dữ liệu (Định dạng chuỗi). Tương tự, bạn có thể thay đổi màu sọc đen và trắng trông khá tồi tàn thành màu xanh lá cây và đỏ để cuối cùng có được bức ảnh đẹp hơn:

Trong các phiên bản mới nhất của Microsoft Excel, chiều rộng của các thanh có thể được thay đổi bằng cách nhấp vào một trong các biểu đồ trong suốt (không phải thanh!) Bằng nút chuột phải và chọn lệnh Định dạng chuỗi dữ liệu - Khoảng trống bên (Chuỗi định dạng - Độ rộng khoảng trống).

Trong các phiên bản Excel cũ hơn, bạn phải sử dụng lệnh Visual Basic để sửa lỗi này:

1. Đánh dấu sơ đồ đã xây dựng
2. Nhấn phím tắt Khác+F11để vào Visual Basic Editor
3. Nhấn phím tắt Ctrl+Gđể mở bảng điều khiển gỡ lỗi và nhập lệnh trực tiếp lập tức (thường nằm ở phía dưới).

4. Sao chép và dán lệnh sau vào đó: ActiveChart.ChartGroups (1) .GapWidth = 30 và báo chí đăng ký hạng mục thi:

Tất nhiên, bạn có thể thử với giá trị tham số nếu muốn. Chiều rộng khoảng cáchđể đạt được giải phóng mặt bằng mong muốn:

• Cách tạo biểu đồ dấu đầu dòng trong Excel để trực quan hóa KPI  
• Có gì mới trong biểu đồ trong Excel 2013
• Cách tạo biểu đồ “trực tiếp” tương tác trong Excel