根据单元格值更改条形颜色 [英] Change Bar color based on cell value

问题描述

我正在尝试寻找一种解决方案，以根据单元格的值或颜色自动调整图表中堆积条形的颜色.

I am trying to find a solution to automatically adjust the color of a stacked bar in a chart based on the value or color of a cell.

具体来说，当我更改F2的值时，我希望条也根据单元格的颜色或单元格的值/字符串更改颜色.

Specifically, when I change the value of F2 I want the bar to change color as well, based off the color of the cell or the value/string of the cell.

这是我的工作表: https://docs. google.com/spreadsheets/d/1QwAVPEtQ3l7iIZhLDYMT1I9eGc7HoaFsTyg1p-hi3G8/edit?usp=sharing

我已经设置了我的测试数据和研究过的解决方案，但是还没有找到一种方法可以完全满足我的需求.

I have setup my test data and researched solutions but haven't found a method to exactly do what I am looking for.

我希望找到一个解决方案，并且我倾向于Apps脚本方法，因为在条件格式"菜单中看不到任何可以控制图表序列格式的选项，并且我也没有看到条形图菜单中的任何选项都可以定位到特定的单元格.

I'm hoping to find a solution, and I am leaning towards an Apps Script method as I don't see any options in the "Conditional Format" menu that will control the chart series formatting, and I don't see any options in the bar chart menu to target a specific cell.

任何建议或指导都将不胜感激.

Any advice or guidance is greatly appreciated.

推荐答案

您可以基于对工作表的修改来修改图表.

You can modify the chart based on edits to the sheet.

使用简单的触发函数onEdit及其事件对象，可以确定是否进行了更改参考单元格的图纸编辑.如果进行了编辑，则可以访问工作表的嵌入式图表，并根据需要对其进行修改:

Using the simple trigger function onEdit with its event object, you can determine if sheet edits were made that altered your reference cell. If the edit did, then you can access the sheet's embedded charts, and modify them as you desire:

function onEdit(e) {
  if (!e) throw new Error("You ran this manually");
  const sheet = e.range.getSheet();

  // Sanity check: only care about edits on the desired sheet.
  if (sheet.getName() !== "some sheet name with your reference cell on it")
    return;
  // Sanity check: only care about edits to the reference cell:
  if (e.range.getA1Notation() !== "reference cell A1 notation here")
    return;
  // Sanity check: only care if there is a new value.
  if (e.value === undefined)
    return;
  // Example change: alter the color of the 3rd series. Assumption: e.value is a
  // valid CSS color or CSS color code. If it is numeric, write code to transform it.
  modifyChart_(sheet, 2, e.value)
}

function modifyChart_(sheet, seriesIndex, newCssColor) {
  // Assume there is only one chart on this sheet.
  const charts = sheet.getCharts();
  if (charts.length > 1)
    console.warn("Assumption invalid: more than 1 chart on the given sheet");
  const barBuilder = charts[0].modify().asBarChart();

  const option = {};
  option[seriesIndex] = {"color": newCssColor};
  barBuilder.setOption("series", option);

  // Update the chart on the sheet.
  sheet.updateChart(barBuilder.build());
}

参考文档(又名必读):

Reference documentation (aka REQUIRED reading):

• 简单触发器
  • 事件对象
  • Simple Triggers
    • Event objects

    这篇关于根据单元格值更改条形颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！