Excel/Google Sheets按相邻单元格颜色条件求和已收款总额求助

Excel 实现方法 #

方法1：名称管理器+SUMIF（无需宏） #

1. 按Ctrl+F3打开名称管理器，点击「新建」：
   • 名称设为CellColor
   • 引用位置输入=GET.CELL(38, 你的颜色单元格起始位置)（比如Sheet1!A1，需和实际颜色列起始对应）
2. 在颜色列旁插入辅助列，输入=CellColor并下拉，得到每个单元格的填充色索引号（找到绿色单元格对应的数值，比如绿色可能是10）
3. 使用SUMIF求和：

   =SUMIF(辅助列范围, 绿色索引号, 金额列范围)
   

   示例：辅助列是C1:C10，绿色索引号为10，金额列是B1:B10，公式为=SUMIF(C1:C10,10,B1:B10)

方法2：VBA自定义函数（更直接） #

1. 按Alt+F11打开VBA编辑器，插入模块，粘贴代码：

   Function SUMBYCOLOR(rngColor As Range, rngSum As Range) As Double
       Dim cell As Range
       Dim targetColor As Integer
       targetColor = rngColor.Interior.ColorIndex
       For Each cell In rngColor
           If cell.Interior.ColorIndex = targetColor Then
               SUMBYCOLOR = SUMBYCOLOR + rngSum.Cells(cell.Row - rngColor.Row + 1).Value
           End If
       Next cell
   End Function
   

2. 返回工作表直接调用：

   =SUMBYCOLOR(A1, B1:B10)
   

   其中A1是绿色单元格，B1:B10是待求和的金额范围

Google Sheets 实现方法 #

自定义脚本函数 #

1. 点击菜单栏「扩展」→「Apps脚本」，清空默认代码后粘贴：

   function sumByColor(colorCell, sumRange) {
       const ss = SpreadsheetApp.getActiveSpreadsheet();
       const targetColor = ss.getRange(colorCell).getBackground();
       const sumRng = ss.getRange(sumRange);
       const values = sumRng.getValues();
       // 颜色列在金额列左侧则用offset(0,-1)，右侧改为offset(0,1)
       const colorRng = sumRng.offset(0, -1);
       const backgrounds = colorRng.getBackgrounds();
       
       let total = 0;
       for (let i = 0; i < values.length; i++) {
           if (backgrounds[i][0] === targetColor) {
               total += values[i][0];
           }
       }
       return total;
   }
   

2. 保存并授权脚本后，返回工作表使用：

   =sumByColor("A1", "B1:B10")
   

   其中A1是绿色单元格，B1:B10是金额范围

注意事项 #

• 以上方法识别的是单元格填充色，若需按字体颜色求和，只需将代码中对应属性替换为字体颜色相关（如Excel的Font.ColorIndex、Google Sheets的getFontColor()）
• Excel的GET.CELL属于旧版宏表函数，部分版本需启用宏
• Google Sheets脚本首次使用会提示授权，按指引完成即可

内容的提问来源于stack exchange，提问作者Enalios