使用SWITCH to with SPARKLINES对主条进行不同颜色的着色

Question

我正在寻找最好的方法来改变颜色或主条的迷你图的基础上它的价值。这是用于预算工作表的。当它仍然在年度总预算之内时，我希望主条变成蓝色，当它恰好是年度总预算时，它是绿色的，当它超过全年预算时，它是红色的。我试着使用切换功能，但它不起作用。任何想法和帮助都非常感谢。以下是我正在尝试的方法，但没有奏效。E24是我希望迷你图显示的单元格。H是实际支出的单元格，F是预算金额的单元格。

非常感谢您为实现这一目标所提供的帮助。

= SWITCH(E24, ABS(H24)<F24,(SPARKLINE(H24,{"charttype","bar";"max",F24;"color1","#2E86DE"})),
ABS(H24)>F24,(SPARKLINE(H24,{"charttype","bar";"max",F24;"color1","#de4c60"})),
ABS(H24)=F24,(SPARKLINE(H24,{"charttype","bar";"max",F24;"color1","#69C569"})),
SPARKLINE(H24,{"charttype","bar";"max",F24;"color1","#000000"}))

Answer 1

尝试嵌套IF，示例：

=IF(B3 > B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "blue";  "ymin", 0}), 
 IF(B3 < B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "green"; "ymin", 0}), 
 IF(B3 = B1, SPARKLINE(B2:B3,
             {"charttype", "column"; "color", "red"; "highcolor", "pink";  "ymin", 0}), )))

在B3中粘贴

=IF(G3=0, SPARKLINE(E3, {"charttype", "bar"; "max", C3; "color1", "#6AA84E"}),
 IF(G3>0, SPARKLINE(E3, {"charttype", "bar"; "max", C3; "color1", "#2E86DE"}),
 IF(G3<0, SPARKLINE(E3, {"charttype", "bar"; "max", C3; "color1", "#CC0000"}), )))

​

​

Answer 2

以下是有效的方法。:)

=IF(ABS(H17) > F17, SPARKLINE(H17,
         {"charttype","bar"; "max",F17; "color1","#DE4C60"}),  
 IF(ABS(H17) < F17, SPARKLINE(H17,
         {"charttype","bar"; "max",F17; "color1","#2E86DE"}), 
 IF(ABS(H17) = F17, SPARKLINE(H17,
         {"charttype","bar"; "max",F17; "color1","#69C569"}), )))