Hướng dẫn tạo biểu đồ hình thác nước trong Google Sheets

Nội dung chính

1 Biểu đồ thác nước trong Google Sheets là gì?

1.1 Trường hợp 1: Tạo biểu đồ thác nước đơn giản
1.2 Trường hợp 2: Biểu đồ thác nước loại phức tạp

2 Cách tạo biểu đồ thác nước tự động bằng Apps Script

2.3 Các bước để thiết lập và sử dụng mẫu biểu đồ thác nước bằng Apps Script

Nếu bạn là 1 người thường xuyên sử dụng Google Sheets thì bạn chắc chắn sẽ biết được nhiều lợi ích của biểu đồ đem đến cho ta. Nhưng bạn sẽ càng ngạc nhiên hơn khi Google đã thêm loại biểu đồ thác nước vào các biểu đồ gốc trong Chart Tool của Google Sheets, giúp bạn không cần phải tạo biểu đồ thác nước theo cách thủ công (hoặc sử dụng tập lệnh ứng dụng - Apps Script) nữa.

Bây giờ bạn chỉ cần đánh dấu dữ liệu của mình, nhấp vào Insert => Chart và trong Chart Type (Loại biểu đồ), hãy chọn “Waterfall” là xong:

Nhưng trong bài viết này, mình sẽ hướng dẫn cho những ai muốn tìm hiểu kỹ về loại biểu đồ thác nước này từ cách tạo cho đến cách định dạng và từ cách thủ công đến chạy tập lệnh ứng dụng (Apps Script).

Biểu đồ thác nước trong Google Sheets là gì?

Biểu đồ thác nước thể hiện một giá trị thay đổi như thế nào so với giá trị ban đầu.

Biểu đồ thác nước sau đây cho thấy những thay đổi về số lượng nhân viên của một bộ phận, nó mô tả trực quan tác động tích lũy của việc thêm và xóa đối với giá trị ban đầu:

Biểu đồ waterfall trên hiển thị số lượng nhân viên trong bộ phận của ta vào đầu năm (thanh màu xám bên trái), số lượng người được bổ sung từ các bộ phận khác hoặc là nhân viên mới (thanh màu xanh lá), số người bị chuyển đi hoặc nghỉ hưu hoặc bị sa thải (thanh màu đỏ) và cuối cùng là số lượng nhân viên vào cuối năm (thanh màu xám bên phải).

Biểu đồ thác nước ở trên tương đối dễ tạo trong Google Sheets nhưng vẫn phải cần một số dữ liệu phức tạp để thiết lập. Lưu ý rằng tất cả các thanh đều nằm phía trên trục x (Trường hợp 1), điều này làm cho dữ liệu được thiết lập đơn giản hơn rất nhiều so với trường hợp chúng ta có kết hợp các thanh nằm ở trên lẫn dưới trục x (xem Trường hợp 2 bên dưới).

Mình sẽ chỉ cho bạn cách tạo biểu đồ thác nước ở cả hai trường hợp này, ta sẽ bắt đầu từ loại đơn giản đến phức tạp nhé.

Sau khi tạo biểu đồ thác nước từ dạng đơn giản đến phức tạp bằng các công thức theo cách thủ công thì mình sẽ chỉ cho bạn một số mã Apps Script để tự động hóa phần lớn quy trình và tăng tốc độ tạo biểu đồ thác nước phức tạp.

Trường hợp 1: Tạo biểu đồ thác nước đơn giản

Trong trường hợp này thì các thanh đều sẽ nằm phía trên so với trục x.

BƯỚC 1: Dữ liệu của dạng biểu đồ này như thế nào?

Điều gì xảy ra nếu ta chỉ cần tạo một biểu đồ từ bảng dữ liệu trên?

Ví dụ, với dữ liệu trên thì ta có thể tạo với một biểu đồ cột bình thường nhưng biểu đồ này không hiển thị rõ ràng các dữ liệu như biểu đồ thác nước. Và ta không thể thay đổi màu sắc của các thanh, vì chúng đều thuộc cùng một chuỗi dữ liệu:

BƯỚC 2: Tạo một bảng dữ liệu mới cho biểu đồ thác nước

Ta sẽ tạo một bản sao đặt kế bên với dữ liệu gốc, sau đó thêm bốn cột mới: Base, Endpoints, Positive và Negative:

BƯỚC 3: Công thức cho biểu đồ thác nước

Bạn hãy thêm các công thức sau vào 4 cột trên:

Để dễ nhìn, mình đã tô màu cho mỗi phần.

Trong hàng đầu tiên và hàng cuối cùng, tức là hàng 2 và hàng 8 trong ví dụ này, mình đặt 0 vào cột Base và giá trị "Count" (B2) trong cột Endpoints. Các cột Positive & Negative thì để trống.

Đối với các hàng giữa, tức là hàng 3 đến hàng 7, mình đều đặt chung một công thức sau trong cột Base:

=if(B3>0,sum(B$2:B2),sum(B$2:B3))

Cột Positive: =max(B3,0)

Cột Negative: =-min(B3,0)

Riêng cột Endpoints thì mình sẽ để trống.

Bạn nhớ sao chép công thức này xuống từ hàng 3 đến hàng 7 nhé!

Và sau khi bạn đã nhập công thức:

BƯỚC 4: Tạo biểu đồ cột chồng

Highlight bảng dữ liệu mới này và tạo biểu đồ nào: Insert => Chart ... Và hãy chắc chắn rằng bạn đã chọn BIỂU ĐỒ CỘT CHỒNG nhé!

BƯỚC 5: Tạo hình thác nước trong biểu đồ cột xếp chồng

Bây giờ, bạn hãy đặt màu của cột Base thành None:

BƯỚC 6: Định dạng biểu đồ thác nước

Việc còn lại là chọn màu phù hợp với các thanh, trục định dạng và tiêu đề.

Ngoài ra, bạn cũng có thể xóa chú thích vì chúng thực sự không cần thiết.

Và đây là kết quả cuối cùng:

Trường hợp 2: Biểu đồ thác nước loại phức tạp

Giả sử nếu ta có dữ liệu âm (-1; -2; -3; ...) thì sao? Ví dụ:

Thoạt nhìn, bạn sẽ thấy trường hợp này cũng không khác gì so với trường hợp trên cả.

Và bạn sẽ rõ ràng nhận thấy mình có các cột có giá trị dương thì nằm phía trên so với trục x (ví dụ: Doanh thu trong biểu đồ trên). Còn các cột có giá trị âm thì nằm phía dưới so với trục x (ví dụ: Chi phí bán hàng trong biểu đồ trên).

Vì vậy, cái phức tạp ở đây chính là CÔNG THỨC bạn nhập để xử lý trường hợp này.

BƯỚC 1: Dữ liệu của biểu đồ thác nước

So với trường hợp 1 thì trong trường hợp này, mình sẽ nhập dữ liệu phức tạp hơn (bao gồm các giá trị âm và dương):

BƯỚC 2: Sử dụng hàm MIN & MAX để định hình dữ liệu

Mình đã cố gắng tạo ra một công thức chung để chỉ cần điền giá trị thôi thì thanh có chứa giá trị đó sẽ tự động di chuyển xuống mức khớp với giá trị ta điền.

Và công thức mình tạo ra là sự kết hợp của công thức mảng, IF, SIGN, ROW,... Có thể nói rất tối ưu.

Sau nhiều hồi tham khảo, đây là những công thức mình sẽ đưa vào mẫu biểu đồ thác nước dưới đây:

Giả sử dữ liệu từ BƯỚC 1 ở trên nằm trong phạm vi A1: B8. Bây giờ, bạn hãy thêm các công thức sau vào các ô sau:

1. Dòng tiêu đề:

D1: Label
E1: Endpoints
F1: Base
G1: Positive Cols, Above
H1: Positive Cols, Below
I1: Negative Cols, Above
J1: Negative Cols, Below

2. Dòng cuối cùng:

D2: =A2
E2: =B2
F2 to J2: Leave blank

3. Dòng giữa:

D3: =A3
E3: trống
F3: =max(0,min(sum($B$2:$B3),sum($B$2:$B2))) + min(0,MAX(sum($B$2:$B3),sum($B$2:$B2)))
G3: =max(0,min(sum($B$2:$B3),B3))
H3: =min(0,G3-B3)
I3: =max(0,J3-B3)
J3: =min(0,MAX(sum($B$2:$B3),B3))

Và đây là bảng dữ liệu của ta:

BƯỚC 3: Tạo biểu đồ cột chồng

Highlight bảng dữ liệu mới này và nhấn Insert => Chart ... (BƯỚC 4 ở trên)

Thao tác này sẽ thêm biểu đồ thác nước vào trang của bạn. Sau đó, bạn có thể làm theo các BƯỚC 5 & 6 như trường hợp 1 ở trên để tạo biểu đồ thác nước hoàn chỉnh.

HƯỚNG DẪN TẠO BIỂU ĐỒ WATERFALL TRONG GOOGLE SHEETS

Cách tạo biểu đồ thác nước tự động bằng Apps Script

Như bạn đã thấy trong ví dụ trên, các công thức khá phức tạp đối với một biểu đồ, nhất là khi chúng ta có các thanh Positive & Negative và một trong số đó lại nằm phía dưới so với trục x.

Có thể nói thực hiện tất cả các thao tác trên như sao chép dữ liệu, nhập công thức và chèn biểu đồ trực tiếp vào sheet rất mất thời gian mà có khi vẫn chưa tạo ra được biểu đồ đúng như ý ta. Vì vậy, cách tốt nhất để giải quyết vấn đề này là viết một chương trình macro nhỏ bằng cách sử dụng Apps Script.

Ví dụ, ta có dữ liệu sau để hiển thị dưới dạng biểu đồ thác nước:

Sẽ mất một chút thời gian để tìm theo cách thủ công các thanh nào sẽ nằm phía trên/dưới so với trục x và thanh cơ sở cần có cho mỗi thanh. Nhưng khi mình đã viết một chương trình macro nhỏ thì chỉ mất vài giây để tạo ra biểu đồ.

Mình đã thêm đoạn mã sau vào Trình chỉnh sửa tập lệnh (Script Editor) của mình:

//add menu to google sheet
function onOpen() {
  //set up custom menu
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Waterfall Chart')
    .addItem('Insert chart...','waterfallChart')
    .addToUi();
};

// function to create waterfall chart
function waterfallChart() {
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // get the range highlighted by user
  var range = sheet.getActiveRange();
  var data = range.getValues();
  
  var newData = [['Label','Endpoints','Base','Postive Cols above','Positive Cols below',
                  'Negative Cols above','Negative Cols below']];
  
  var tempTotal = 0;
  var tempTotalPrior = 0;
  
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  for (i = 1; i < data.length; i++) {
    
    // running totals
    tempTotalPrior = tempTotal;  // assign previous total to new variable to keep track of it
    tempTotal += data[i][1];  // add up total of values so far
    
    // Endpoints
    if (i == 1 || i == data.length - 1) {
      newData.push([data[i][0],data[i][1],0,'','','','']);
    }
    
    // Non-endpoints
    else {
      
      // Base values
      var baseVal = Math.max(0,Math.min(tempTotal,tempTotalPrior)) + Math.min(0,Math.max(tempTotal,tempTotalPrior));
      
      // calculate minimum of running total and current value
      var val1 = Math.min(tempTotal,data[i][1]);
      
      // calculate maximum of running total and current value
      var val2 = Math.max(tempTotal,data[i][1]);
      
      // Postive Cols above
      // if val1 is negative, set to 0, otherwise take val1, which is min of running total and current value
      var posValAbove = Math.max(0,val1);
      
      // Postive Cols below
      // subtract current value from Positive Col Value to catch any part of column below 0. If a positive value, set to 0 by using minimum
      var posValBelow = Math.min(posValAbove - data[i][1],0);
      
      // Negative Cols below
      // if val2 is positive, set to 0, otherwise take val2, which is the max of running total and current value
      var negValBelow = Math.min(0,val2);
      
      // Negative Cols above
      // subtract current value from Negative Col Value to catch any part of column above 0. If a negative value, set to 0 by using maximum
      var negValAbove = Math.max(negValBelow - data[i][1],0);
       
      // push all new datapoints into newData array
      newData.push([data[i][0],0,baseVal,posValAbove,posValBelow,negValAbove,negValBelow]);
    }
    
  }
  
  // paste the new data into sheet
  sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length).setValues(newData);

  // get the new data for the chart
  var chartData = sheet.getRange(lastRow - data.length + 1, lastCol + 2, data.length, newData[0].length);
  
  // make the new waterfall chart
  sheet.insertChart(
    sheet.newChart()
    .addRange(chartData)
    .setChartType(Charts.ChartType.COLUMN)
    .asColumnChart()
    .setStacked()
    .setColors(['grey','none','green','green','red','red'])
    .setOption('title','Waterfall Chart')
    .setLegendPosition(Charts.Position.NONE)
    .setPosition(lastRow - data.length + 4,lastCol + 4,0,0)
    .build()
  );
}

Hàm onOpen() sẽ thêm một menu tùy chỉnh vào sheet nên bạn có thể chạy hàm Waterfallchart() từ sheet đó.

Bạn có thể cho chạy hàm bằng cách đánh dấu dữ liệu biểu đồ thác nước và sau đó chọn Waterfall Chart => Create Chart từ menu:

Thao tác này sẽ chạy hàm Chart, lấy và dán dữ liệu mới vào sheet và cuối cùng là tạo biểu đồ nháp cho bạn:

Các bước để thiết lập và sử dụng mẫu biểu đồ thác nước bằng Apps Script

1. Tự tạo bản copy riêng cho dữ liệu của bạn: File => Make a copy...

2. Mở trình chỉnh sửa tập lệnh (Script Editor) nơi mã tồn tại: Tools => Script editor...

3. Trong thanh menu, chọn onOpen (được hiển thị bởi mũi tên màu đỏ (1) trong hình bên dưới), sau đó nhấp vào hình tam giác (được hiển thị bởi mũi tên màu đỏ (2) bên dưới) để chạy tập lệnh này:

4. Quay lại trang Google Sheets của bạn và lúc này trên thanh menu của bạn sẽ xuất hiện một thẻ mới: Waterfall Chart

5. Đánh dấu dữ liệu biểu đồ thác nước của bạn trong cột A & B, sau đó nhấp vào thẻ Waterfall Chart => Insert Chart ...

Trên đây là những hướng dẫn về cách tạo biểu đồ thác nước (Waterfall Chart) trong Google Sheets từ cách thủ công đến dùng Apps Script. Ngoài ra, để nâng cao kỹ năng làm việc với Google Sheets, bạn có thể tham gia khóa học Google Sheets từ cơ bản đến nâng cao. Chi tiết tại Gitiho.com.

CÓ THỂ BẠN QUAN TÂM:

Tổng hợp các phím tắt thông dụng trong Google Sheets

Hướng dẫn liên kết Google Sheets với cơ sở dữ liệu bằng Apps Script

Hướng dẫn cách chuyển văn bản thành số trong Google Sheets

Hướng dẫn cách tạo biểu đồ động trong Google Sheets