Google 表格 - 将数值分配给彩色单元格，然后将 SUM 值一起放在另一列中

Question

我不确定这是否可以完成，但我希望尝试为特定颜色分配一个数值，然后对这些值求和并将结果显示在不同的列中。上图是 Green + Green + Gray = 7 和 Green + Red + Green = 6 的理想输出。我希望能够为任何单元格分配绿色 3，灰色单元格 1 和红色单元格0 然后将它们相加显示在“每月总积分”列 I 中。

Answer 1

解决方案：

您可以使用使用getBackground()的自定义公式来获取单元格背景颜色的十六进制值，并根据颜色和点之间的映射计算点的总数：

function getPoints(row, column) {

  const ss = SpreadsheetApp.getActive();

  const sh = ss.getActiveSheet();  

  const start_cell = sh.getDataRange().getCell(row, column);    

  const cells_range = start_cell.offset(0, 0, 1, 3);

  const cells_bg = cells_range.getBackgrounds().flat();    

  const colors_src_range = sh.getRange('C1:E1');

  const colors_source = colors_src_range.getBackgrounds().flat();

  const colors_pts = colors_src_range.getValues().flat().map(cv=>cv.replace( /^\D+/g, ''));

  var total_pts = 0;  

  cells_bg.forEach(cbg=>{

  colors_source.forEach( (col,index)=>{

  total_pts += cbg == col ? parseInt(colors_pts[index]) : 0

  });})

  

  return total_pts;

}

然后您可以将它用作工作表中的公式：getPoints(row, column)为此您需要指定具有颜色的最左侧单元格的坐标（在您的示例中为 C 列），它将返回颜色行的总点数。

更新：

• 脚本会自动从范围中获取十六进制值C1:E1，因此您无需手动传递这些值。

• 每种颜色的点数也由脚本使用正则表达式自动拾取。

• 代码更加优化，因为它使用getBackgrounds()而不是getBackground()。

---

例子：

例如，这个公式：

=getPoints(row(C6),column(C6))

将导致：