如何通过GAS仅对选中行应用百分位数条件格式？

原全列条件格式代码 #

之前为K列所有行按百分位数标记颜色的代码：

var rule1 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=$K2>=percentile(K$2:K,0.9)")  
.setBackground("#c9daf8")
.setRanges([range1])
.build();
var rule2 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=$K2>=percentile(K$2:K,0.75)")
.setBackground("#d9ead3")
.setRanges([range1])
.build();
var rule3 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=$K2>percentile(K$2:K,0.1)")
.setBackground("#fce8b2")
.setRanges([range1])
.build();
var rule4 = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied("=$K2<=percentile(K$2:K,0.1)")
.setBackground("#dd7e6b")
.setRanges([range1])
.build();

问题所在 #

你尝试选中8-15行时使用的公式.whenFormulaSatisfied("=$K8>=percentile(K8:K15,0.75)")存在两个核心问题：

• 固定行号导致基准值错误：公式里用了$K8固定行号，当条件格式应用到8-15行所有单元格时，每一行都会用K8的值去和K8:K15的百分位比较，而不是当前行的K值，导致所有行的判断基准完全一致，结果自然错误。
• 数据范围未固定：K8:K15是相对引用，当条件格式应用到不同行时，这个范围会自动偏移（比如应用到K9时，范围会变成K9:K16），导致百分位计算的不是你选中的8-15行数据。

正确解决方案 #

公式编写规则 #

针对选中的行区间（比如8-15行），正确的公式应该是：

"=$K2>=percentile(K$8:K$15,0.75)"

• $K2：保持列固定、行相对的引用，当条件格式应用到K8-K15时，会自动对应到当前行的K值（K8、K9…K15）。
• K$8:K$15：固定行号的绝对引用，确保百分位计算始终基于你选中的8-15行数据，不会随单元格位置偏移。

动态适配选中行的代码实现 #

为了让按钮能自动适配任意选中行，需要动态获取选中范围的起止行，生成对应公式和格式范围：

function formatSelectedRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selection = sheet.getSelection();
  var targetRange = selection.getActiveRange();
  
  // 获取选中范围的起止行，限定到K列
  var startRow = targetRange.getRow();
  var endRow = targetRange.getLastRow();
  var kColumn = 11; // K列对应索引11
  var formatRange = sheet.getRange(startRow, kColumn, endRow - startRow + 1);
  
  // 清除原有条件格式，避免冲突
  formatRange.clearConditionalFormatRules();
  
  // 动态生成百分位计算的固定范围
  var percentileRange = `K$${startRow}:K$${endRow}`;
  
  // 创建条件格式规则
  var rule1 = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$K2>=percentile(${percentileRange},0.9)`)
    .setBackground("#c9daf8")
    .setRanges([formatRange])
    .build();
  
  var rule2 = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$K2>=percentile(${percentileRange},0.75)`)
    .setBackground("#d9ead3")
    .setRanges([formatRange])
    .build();
  
  var rule3 = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$K2>percentile(${percentileRange},0.1)`)
    .setBackground("#fce8b2")
    .setRanges([formatRange])
    .build();
  
  var rule4 = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(`=$K2<=percentile(${percentileRange},0.1)`)
    .setBackground("#dd7e6b")
    .setRanges([formatRange])
    .build();
  
  // 应用规则（注意顺序：优先级高的规则放前面）
  sheet.setConditionalFormatRules([rule1, rule2, rule3, rule4]);
}

关键注意事项 #

• 规则顺序：条件格式是按顺序匹配的，百分位阈值更高的规则（如90%）要放在最前面，否则会被低阈值的规则覆盖。
• 清除原有规则：每次执行前清除目标范围的原有条件格式，避免新旧规则冲突。
• 选中范围处理：代码默认处理单个连续选中范围，如果需要支持多范围选中，可以扩展getActiveRangeList()的遍历逻辑。

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