代码之家 › 专栏 › 技术社区 › Nixxen

条件格式基于单独列中的值,基于单独工作表中的数据验证

gs-conditional-formatting formula google-sheets

Nixxen · 技术社区 · 8 年前

我正在制作一份电子表格日志,其中包括:
-表1中的主要条目
-表2上的名称和数据的静态列表

在sheet1中,我根据sheet2中的名称通过数据验证输入值。随后,我在sheet2添加一个值。该值需要与sheet2列出的最小值和最大值进行比较,然后根据与sheet2中值的%偏差通过条件格式进行颜色编码。例如:

在表1中,我有以下数据:

     A                          B
1    value                      OG 
2    British Brown Ale          1.045

价值 British Brown Ale 通过从数据验证列表填充的下拉列表输入。在里面 B2 我对“OG”有一个值 1.045 .

在表2中,数据如下所示:

     A                          B          C
1    value                      min OG     max OG 
2    British Brown Ale          1.040      1.052

在“英国棕色啤酒”一行:

在第2列(“最小OG”)中,我的值为 1.040
在第3列(“最大OG”)中,我的值为 1.052

预期结果

我需要做:

从1.040到1.052 100%的范围内,
sheet1!B2 如果例如在该范围之外高达10%,则其将被颜色编码为黄色,
并且超过10%将被颜色编码为红色。

我需要脚本来实现这一点吗?

1 回复 | 直到 8 年前

Max Makhrov TheMaster 8 年前

只使用公式是可能的。最好的方法是只使用 ArrayFormula 以便自动生成所有公式espand。

表2

在单元D2中:

=ArrayFormula(OFFSET(B2,,,COUNTA(A2:A))/1.1)

在细胞E2中:

=ArrayFormula(OFFSET(C2,,,COUNTA(A2:A))*1.1)

结果:

表一

在单元C2中:

=ArrayFormula(VLOOKUP(OFFSET(A2,,,COUNTA(A2:A)),sheet1!A:E,2,0))

在单元D2中:

=ArrayFormula(VLOOKUP(OFFSET(A2,,,COUNTA(A2:A)),sheet1!A:E,3,0))

在细胞E2中:

=ArrayFormula(VLOOKUP(OFFSET(A2,,,COUNTA(A2:A)),sheet1!A:E,4,0))

在单元F2中:

=ArrayFormula(VLOOKUP(OFFSET(A2,,,COUNTA(A2:A)),sheet1!A:E,5,0))

并且一个公式具有颜色:

=ArrayFormula(if(--(OFFSET(B2,,,COUNTA(A2:A))<OFFSET(E2,,,COUNTA(A2:A)))+B2>F2,"red",if((OFFSET(B2,,,COUNTA(A2:A))>=OFFSET(C2,,,COUNTA(A2:A)))*(+OFFSET(B2,,,COUNTA(A2:A))<=OFFSET(D2,,,COUNTA(A2:A))),"green","yellow")))

结果:

表1的条件格式规则

首先,漆成绿色:

=$G1="green"

下一步,漆成黄色:

=$G1="yellow"

下一步,漆成红色:

=$G1="red"

则wou可以隐藏额外的列。

结果:

我认为,在CF中只使用3个巨大的ArrayFormula就可以做到这一点,而不需要做额外的列,但很难构建一个。