google-sheets数据透视表中如何用零填充空白或缺少单元格? [英] How to fill with zeros empty or missing cells in a google-sheets pivot table?

问题描述

我有一张表，其中如果缺少一行，则表示该行的值为零.

I have a table where if a row is missing it means the value for that row is zero.

如此

2020-10-12标签L2具有所有显示网址的所有0值.

2020-10-12 label L2 has all 0 values for all display url.

2020-10-12标签L3具有除d2之外的所有显示网址的所有0值.

2020-10-12 label L3 has all 0 values for all display url except d2.

如何将这些丢失的数据填充到数据透视表中?

How can I fill this missing data in the pivot table?

我希望图表是模拟的，而不是每个系列都谨慎.

I want the graph to be analog and not discreet for each series.

我失败的尝试:

推荐答案

说明:

• 您可以用零填充原始数据的空单元格，结果数据透视表将包含零而不是空单元格.

  Explanation:

  • You can fill in with zeros the empty cells of the raw data and as a result the pivot table will contain zeros instead of empty cells.

    在以下脚本中，选择原始数据所在的工作表名称，然后选择要检查空单元格的确切数据范围.

    In the following script, select the sheet name where the raw data is located and choose the exact data range where you want to check for empty cells.

    该脚本将获取所选范围的所有值，并将为空单元格添加零.您可以使用 map()完成此操作:

    The script will get all the values of the selected range and it will add zeros to the empty cells. You can use map() to accomplish this operation:

    const data=range.getValues().map(row=>row.map(r=>r==''? 0:r)); 
    

  根据具体情况调整Sheet1和A1:I20:

  function myFunction() {
  
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName('Sheet1'); // select the name of the Sheet
    const cords = 'A1:I20'; // select the range you want to paste zeros
    const range = sh.getRange(cords);
    const data=range.getValues().map(row=>row.map(r=>r==''? 0:r)); 
    range.setValues(data);
  }
  

  这篇关于google-sheets数据透视表中如何用零填充空白或缺少单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！