Excel-使用公式的条件格式无法正常工作,但公式本身可以正常工作 [英] Excel - Conditionnal formatting with formula not working but the formula itself works
问题描述
我正在尝试根据文件路径的扩展名来格式化一列文件路径(例如 .css )。
I'm trying to format a column of file paths depending on their extension (e.g. .css).
文件路径如下所示(第3列)
The said column of file paths looks like the following (3rd column)
在另一张表中,我有一个名为 FileExtensionsTbl
的下表,其中每个列均包含条件格式的扩展名。例如,CSS列中的所有扩展名都将用于匹配特定样式,例如所有以 .css
结尾的文件都将匹配CSS条件格式。
In another sheet, I have the following table named FileExtensionsTbl
in which every column contains the extension for a conditional format. For example, all the extensions in column CSS would be used to match a specific style like all files ending with .css
would match the CSS conditional format.
我想出了一个公式在与文件路径相同的工作表中使用时有效。
I came up with a formula that works when used in the same sheet where the file paths are.
此处为公式:
= NOT(ISNA (MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(。; RC3)+1)); FileExtensionsTbl [CSS]; 0)))
Here is the formula:
=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); FileExtensionsTbl[CSS]; 0)))
这是结果(第4列):
Here is the result (4th column):
问题是我无法找到一种使它用作条件格式公式的方法。
The problem is that I can't figure out a way to make it work as a formula for conditional formatting.
这是我尝试以条件格式使用它时遇到的错误:
Here is the error I get when I try to use it in conditional formatting:
注意:我正在使用R1C1引用符号(相对)
Note: I'm using the R1C1 reference notation (relative)
推荐答案
似乎您不能在条件格式公式中使用结构化引用。两个建议:
It seems you can't use structured references in conditional formatting formulas. Two suggestions:
1)用其列引用替换 FileExtensionsTbl [CSS]
。假设CSS类型在 A 列中的文件扩展名页面上,您的公式将为:
1) Replace FileExtensionsTbl[CSS]
by its column reference. Assumed CSS types are on the sheet File Extensions in column A your formula would be:
=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); 'File Extensions'!A:A; 0)))
2)或使用此技巧,并用 INDIRECT
:
=NOT(ISNA(MATCH(LOWER(RIGHT(RC3; LEN(RC3)-FIND(".";RC3)+1)); INDIRECT("FileExtensionsTbl[CSS]"); 0)))
这篇关于Excel-使用公式的条件格式无法正常工作,但公式本身可以正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!