Cách tạo biểu đồ phễu (Funnel charts) trong Google Sheets

Bản thân các biểu đồ phễu (Funnel charts) này có một chút mới lạ. Đúng, chúng  rất đẹp về mặt thẩm mỹ vì thật sự giống với một phễu thuôn nhọn chúng ta thường thấy trong các báo cáo về phễu bán hàng, phễu marketing... Trong bài viết này, chúng ta sẽ tìm hiểu các vẽ các biểu đồ phễu tuyệt đẹp trong Google Sheets. 

Chúng ta sẽ xây dựng chúng bằng các thủ thuật đơn giản với công cụ tạo biểu đồ, sau đó sử dụng thêm hai loại công thức thú vị khác nhau và cuối cùng, và quan trọng nhất, chúng ta sẽ xây dựng một biểu đồ phễu tự động với Apps Script, như được hiển thị trong hình ảnh này:

Giống như biểu đồ thác nước trong Google Sheets, những biểu đồ dạng phễu như thế này không phải là một biểu đồ có sẵn trong Sheets, vì vậy chúng ta phải tạo chúng theo cách thủ công với một giải pháp khéo léo. Rất may, chúng tương đối đơn giản, chắc chắn đơn giản hơn biểu đồ thác nước.

Đối với tất cả các ví dụ này, chúng ta sẽ sử dụng tập dữ liệu bất động sản bên dưới:
 

Ở đây, chúng ta đang tưởng tượng đại lý bất động sản thu thập dữ liệu liên quan đến kênh bán hàng của họ và họ muốn hiển thị nó ở định dạng biểu đồ hình phễu.

Để giúp bạn tiện theo dõi và thực hành, chúng tôi đã đính kèm file dữ liệu, bao gồm các công thức và biểu đồ hoàn thiện ngay bên dưới.

Tạo biểu đồ phễu với trình tạo biểu đồ có sẵn trong Google Sheets

Mẹo ở đây là sử dụng biểu đồ thanh xếp chồng với các thanh cơ sở trong suốt, để đạt được hiệu ứng nổi:

Bước 1: Tạo cột trợ giúp

Điều đầu tiên cần làm là tạo một cột trợ giúp chứa các giá trị cho các thanh trong suốt.

Di chuyển dữ liệu từ cột B sang cột C, vì vậy B1: B7 sẽ nằm trong phạm vi ô C1: C7. Trong cột trống mới, thêm tiêu đề "Helper column" và chèn công thức sau:

=(max($C$2:$C$7)-C2)/2

Công thức này xác định giá trị tối đa trong dữ liệu của chúng ta (trong trường hợp này là 636), tính toán sự khác biệt giữa giá trị lớn nhất và giá trị hiện tại, sau đó chia kết quả cho 2 để căn giữa thanh, như sau:

Bước 2: Chèn biểu đồ

Đánh dấu phạm vi dữ liệu mới, bao gồm cột trợ giúp và nhấp vào Insert > Chart....

Đảm bảo rằng bạn chọn Stacked Bar Chart  tùy chọn  Chart Types.

Bạn sẽ kết thúc với một biểu đồ như thế này:

Bước 3: Biến các cột trợ giúp thành trong suốt

Đặt các thanh cột trợ giúp trở nên trong suốt, do đó "ẩn" chúng để tạo sự xuất hiện của các thanh nổi trong biểu đồ hình phễu.

Bước 4: Thêm nhãn dữ liệu và xóa trục x

Đây là một bước quan trọng. Hãy nhìn vào trục x. Nó thực sự vô nghĩa, bởi vì các thanh của chúng ta đang trôi nổi ở giữa khu vực biểu đồ.

Chúng ta có thể khắc phục điều này bằng cách thêm nhãn dữ liệu, sau đó xóa hoàn toàn các nhãn trục x:

Vậy là xong. Biểu đồ cuối cùng của chúng ta sẽ trông giống như sau:

Cách tạo biểu đồ phễu đối xứng với hàm Sparkline trong Google Sheets

Chúng ta có thể sử dụng các công thức sparkline đối xứng để tạo biểu đồ phễu như hình bên dưới:
 

Chúng ta cũng sẽ sử dụng  bộ dữ liệu ở trên, trong phạm vi A2: B7, hãy sử dụng các công thức biểu đồ sparkline như sau:

Trong cột D:

=sparkline(B2,{"charttype","bar";"max",max($B$2:$B$7); "rtl",true;"color1","#FFA500"})

Lưu ý tùy chọn "rtl",true, chỉ định hướng của thanh trong ô, cho phép xuất hiện đối xứng.

Trong cột E:

=sparkline(B2,{"charttype","bar";"max",max($B$2:$B$7); "color1","#FFA500"})

Cách tạo biểu đồ phễu với hàm REPT () trong Google sheets

Chúng ta sẽ chuyển sang một phương pháp khác, tương tự như phương pháp sparkline. Chúng tôi sử dụng công thức REPT () và một số định dạng phông chữ thú vị để tạo biểu đồ phễu.

Biểu đồ phễu với công thức REPT

Một lần nữa, với dữ liệu trong phạm vi A1: B7, hãy thêm các công thức sau vào cột D:

=rept("|",B2/10)

Dấu "|" là ký hiệu đường ống, thường được tìm thấy phía trên phím Enter.

Giá trị 10 là hệ số tỷ lệ để đảm bảo thanh lớn nhất vừa với ô, vì vậy, hãy điều chỉnh giá trị này nếu cần cho đến khi thanh lớn nhất của bạn phù hợp. Tuy nhiên, bắt buộc phải sử dụng cùng một giá trị hệ số tỷ lệ trong mỗi công thức.

Tiếp theo, thay đổi phông chữ để làm cho các ký hiệu đường ống dày hơn - tôi thấy phông chữ Modak hoạt động khá tốt cho việc này - và sau đó đảm bảo tất cả các ô có công thức REPT đều được căn giữa:

Cách sử dụng Apps Script để tạo biểu đồ phễu tự động trong Google Sheets

Chúng ta sẽ viết một số code apps script để tự động tạo biểu đồ phễu tuyệt đẹp này.

Các bước để sử dụng mẫu này:

1. Mở file Google Sheet của bạn để chạy Apps Script.

2. Mở cửa sổ Script Editor ( Tools > Script editor...)

3. Chọn và chạy hàm onOpen() để thêm menu tùy chỉnh

4. Nhấp vào Review Permissions trong cửa sổ Authorization required

5. Xem lại các quyền, trong trường hợp này là truy cập bảng tính của bạn trong Google Drive và nhấp vào Allow:

7. Quay lại bảng tính của bạn, gọi With Apps Script và bây giờ bạn sẽ có một Custom Chart menu, với các tùy chọn cho ba biểu đồ kênh khác nhau. Đánh dấu dữ liệu của bạn (ngoại trừ tiêu đề) và sau đó chèn biểu đồ kênh mà bạn muốn:

Vậy là bạn đã có một biểu đồ phễu tuyệt đẹp cho riêng mình.

Bên dưới là chi tiết đoạn code  Apps Script trong ví dụ này

Code cho menu tùy chỉnh

Bước đầu tiên là thêm menu tùy chỉnh để chúng ta có thể chỉ cần nhấp vào nút để chèn biểu đồ kênh mà chúng ta chọn (biểu đồ nhúng, biểu đồ sparkline hoặc công thức REPT).

Dưới đây là mã soạn sẵn đơn giản từ tài liệu Apps Script

//add menu to google sheet
function onOpen() {
    //set up custom menu
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Chart')
    .addItem('Funnel chart...','funnelChart')
    .addItem('Sparkline funnel chart...','sparklineFunnelChart')
    .addItem('REPT funnel chart...','reptFunnelChart')
    .addToUi();
};

Code cho biểu đồ phễu được tạo từ công cụ tạo biểu đồ

Phương pháp này là quá trình gồm hai giai đoạn: đầu tiên, tạo cột trợ giúp để dữ liệu đã sẵn sàng và thứ hai, chèn biểu đồ bằng công cụ biểu đồ của Google Sheets.

Trong các dòng 5 - 26, chúng ta lấy dải dữ liệu, di chuyển cột giá trị sang phải và chèn giá trị mới vào cột trợ giúp, sử dụng công thức [(maxVal - d[1]) / 2]

Việc chèn biểu đồ tương đối đơn giản và hầu hết mã trong dòng 31 - 44 liên quan đến việc thiết lập các tùy chọn để làm cho biểu đồ trông theo cách chúng ta muốn.

// embedded chart builder
function funnelChart() {
    // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
    // get the range highlighted by user
  var range = sheet.getActiveRange();
  var data = range.getValues();
    // move the values one column to the right, to make space for helper column
  range.offset(0,range.getNumColumns(),range.getNumRows(),1)
    .setValues(data.map(function(d){
      return [d[1]];
  }));
    // get the max of the data
  var maxVal = Math.max.apply(Math, data.map(function(d){
      return [d[1]];
  }));
    // create the helper column
  range.offset(0,range.getNumColumns()-1,range.getNumRows(),1)
    .setValues(data.map(function(d){
      return [(maxVal - d[1]) / 2];
  }));
    // make the new funnel chart
  // To do: add the data labels to the bars with the annotations option
  // see: https://developers.google.com/chart/interactive/docs/gallery/barchart#labeling-bars
  sheet.insertChart(
    sheet.newChart()
    .addRange(sheet.getDataRange())
    .setChartType(Charts.ChartType.BAR)
    .asBarChart()
    .setColors(["none", "#FFA500"])
    .setStacked()
    .setOption("title","GAS Funnel chart")
    .setOption("hAxis.gridlines.color","none")
    .setOption("hAxis.textStyle.color","none")
    .setLegendPosition(Charts.Position.NONE)
    .setPosition(2, sheet.getLastColumn()+2,0,0)
    .build()
  );
};

Code cho biểu đồ phễu đối xứng với sparkline

Trong ví dụ này, chúng ta chỉ cần tạo các đường gấp khúc bên trái và bên phải trong các cột liền kề. Chúng ta thực hiện việc này bằng cách tạo một mảng bên trái formulasLeft và một mảng bên phải formulasRight, để chứa tất cả các công thức văn bản này. Sau đó, chúng ta sẽ lặp lại các giá trị dữ liệu ban đầu và với mỗi giá trị, tạo các công thức sparkline đã thêm vào formulasLeft và formulasRighttương ứng.

Cuối cùng, chúng ta xác định phạm vi đầu ra và dán vào các công thức trái và phải này. Sử dụng ký hiệu R1C1 để cho phép bạn định vị các công thức liên quan đến vị trí của các giá trị dữ liệu ban đầu.

Hiển thị chúng dưới dạng  biểu đồ phễu đối xứng với sparkline trong Google sheets

// sparkline chart builder
function sparklineFunnelChart() {
    // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
    // get the range and data highlighted by user
  var range = sheet.getActiveRange();  
  var data = range.getValues();
  var len = data.length;
    // sort the range by the second column of values, highest to lowest and find max value
  var sortedRange = range.sort({column: range.getColumn()+1, ascending: false});
  var maxVal = sortedRange.getValues()[0][1];
    // create array to hold sparklines left
  var formulasLeft = [];
  for (j = 0; j < len; j++) {
    var optionsLeft = '{"charttype","bar";"max",R[-' + j + ']C[-2]; "rtl",true; "color1","#FFA500"}';
    var sparklineFunnelLeft = "=SPARKLINE(R[0]C[-2]," + optionsLeft + ")";
    formulasLeft.push([sparklineFunnelLeft]);
  }
  // create array to hold sparklines right
  var formulasRight = [];
  for (i = 0; i < len; i++) {
    var optionsRight = '{"charttype","bar";"max",R[-' + i + ']C[-3]; "color1","#FFA500"}';
    var sparklineFunnelRight = "=SPARKLINE(R[0]C[-3]," + optionsRight + ")";
    formulasRight.push([sparklineFunnelRight]);
  }
    // identify range for sparkline output
  var funnelOutputLeft = sheet.getRange(range.getRowIndex(),range.getColumn()+3,len,1);
  var funnelOutputRight = sheet.getRange(range.getRowIndex(),range.getColumn()+4,len,1);
    // put the sparkline formulas into the output ranges
  funnelOutputLeft.setFormulasR1C1(formulasLeft);
  funnelOutputRight.setFormulasR1C1(formulasRight);
};

Code cho biểu đồ phễu phiên bản REPT trong Google data studio

Đoạn code  này khá giống với phiên bản sparkline ở trên và thậm chí còn đơn giản hơn vì chúng ta không cần tạo hai bộ công thức với sự đối xứng trái / phải.

Một lần nữa, chúng ta lặp lại mảng giá trị dữ liệu của mình và tạo một chuỗi văn bản công thức REPT trong một mảng mới. Sau đó, tôi xác định phạm vi đầu ra và chèn mảng công thức này.

Bước cuối cùng là định dạng đầu ra thành phông chữ và màu sắc chính xác để đạt được giao diện biểu đồ phễu mong muốn.

// REPT chart builder
function reptFunnelChart() {
    // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
    // get the range and data highlighted by user
  var range = sheet.getActiveRange();  
  var data = range.getValues();
  var len = data.length;
    // sort the range by the second column of values, highest to lowest and find max value
  var sortedRange = range.sort({column: range.getColumn()+1, ascending: false});
  var maxVal = sortedRange.getValues()[0][1];
  var scaleFactor = maxVal / 65; // trial and error to calculate that this gives a good scaling for a column width of 250
    // create array to hold REPT formulas
  var formulas = [];
  for (j = 0; j < len; j++) {
    var rept = '=REPT("|",R[0]C[-2] /' + scaleFactor +')';
    formulas.push([rept]);
  }
    // identify range for REPT output
  var reptOutput = sheet.getRange(range.getRowIndex(),range.getColumn()+3,len,1);
    // format output range for REPT funnel chart
  reptOutput.setFontFamily("Modak");
  reptOutput.setFontColor("#FFA500");
  reptOutput.setHorizontalAlignment("center");
    // set the column width to 250 for the REPT chart column
  sheet.setColumnWidth(range.getColumn()+3, 250)
    // put the sparkline formulas into the output ranges
  reptOutput.setFormulasR1C1(formulas);
};

Biểu đồ phễu với apps script trong Google Sheets

Chúng tôi hy vọng, qua bài viết này, bạn đã biết cách tạo biểu đồ phễu đẹp mắt trong Google Sheets. Biểu đồ phễu có thể được ứng dụng để theo dõi phễu marketing của bạn, giúp bạn phân tích lượng lưu lượng truy cập bạn có ở mỗi giai đoạn của Kênh marketing cũng như chất lượng của lưu lượng truy cập này và hiểu rõ hơn về cách tiếp thị tốt hơn cho từng giai đoạn cụ thể.

Bên cạnh đó, để không bỏ lỡ những thủ thuật và mẹo tin học văn phòng hữu ích khác, hãy tham gia cùng với Gitiho ngay hôm nay.