在条件格式中使用基于数组的公式 [英] Using an array based formula in conditional formatting

问题描述

我要使用excel进行的操作有点不合常规，但是我需要一种方法来清除人们导入到TechExcel的DevTrack中的数据。如果您尝试导入的内容与项目中已存在的内容并非100％准确，DevTrack会为字段创建值，从而导致格式略有不同的重复值。

What I am trying to do with excel is a bit unorthodox, but I need a method of cleaning the data people import in to TechExcel's DevTrack. DevTrack creates values for fields if what you attempt to import is not 100% accurate to what exists already in the project, leading to duplicate values with slightly different formatting.

我想到的是拥有一个验证器 excel表格；人们复制/粘贴他们想要导入的内容，任何与数据验证表不匹配的内容都会突出显示。

What I have come to is having a "validator" excel sheet; people copy/paste what they want to import, and anything that doesn't match a data validation table is highlighted.

数据验证本身不起作用，因为复制/粘贴将其覆盖，并且浪费了手动输入数据的时间。

Data validation itself does not work because copy/paste overwrites it and it is a waste of time to enter data manually.

我设法使用以下公式部分实现了条件格式设置：
= ISERROR（MATCH（C5，D：D，0 ））

I have managed to get conditional formatting working partially using this formula: =ISERROR(MATCH(C5,D:D,0))

但是它不区分大小写。我需要区分大小写。

However it is not case sensitive. I need case sensitivity.

因此，我设法创建了另一个公式，如果没有匹配项，则显示true，但是确切的要求它是一个数组公式，它似乎不适用于条件格式。

So I managed to create another formula which shows true if there is no match, however exact requires it to be an array formula which doesn't seem to work with conditional formatting.

因此，如果使用ctrl + shift + enter将其输入到单元格中，则此方法有效，但不能作为条件规则使用。
{= ISERROR（MATCH（TRUE，EXACT（C5，D：D），0））}

So this works if it is entered in to a cell with ctrl+shift+enter, but doesn't work as a conditional rule. {=ISERROR(MATCH(TRUE,EXACT(C5,D:D),0))}

这是我要进行测试的简化版本。没有大小写匹配时，该公式是正确的，但我无法使用ctrl + shift + enter以条件格式输入该公式。如果按我希望的方式工作，则小写的瓜将突出显示红色。

Here is a simplified version of what I'm looking at for testing. That formula is true when there is no case match, but I can't enter it in conditional formatting with ctrl+shift+enter. If this worked as I want it to, the lower case "melon" would highlight red.

有没有办法使此工作正常，还是有其他方法可以做到这一点？

Is there a way to get this working, or is there an alternative method to do this?

如果有关系，我正在使用Office 365中的最新版excel。

If it matters, I'm using the latest version of excel in office 365.

推荐答案

使用无需CSE即可进行数组处理的函数。例如SUMPRODUCT或AGGREGATE（等等）。

Use a function that produces array processing without CSE. Examples are SUMPRODUCT or AGGREGATE (among others).

'to show TRUE for case-sensitive matches
=SIGN(AGGREGATE(15, 6, ROW($D$5:$D$9999)/EXACT($C5, $D$5:$D999), 1))
'to show TRUE on no case-sensitive match
=ISERROR(AGGREGATE(15, 6, ROW($D$5:$D$9999)/EXACT($C5, $D$5:$D999), 1))

SIGN包装器是不必要的，但对我来说，这是视觉上的提醒，我正在寻找布尔值。

The SIGN wrapper is unnecessary but for me it is a visual reminder that I'm looking for a boolean.

您不能使用D5：INDEX（D：D，MATCH（ zzz， D：D）），并且您不不想在AGGREGATE（或SUMPRODUCT）中使用完整的列引用，因此我选择了$ D $ 5：$ D $ 9999。

You cannot use dynamic ranges like D5:INDEX(D:D, MATCH("zzz", D:D)) in a CFR and you do not want to use full column references in AGGREGATE (or SUMPRODUCT) so I opted for $D$5:$D$9999.

这篇关于在条件格式中使用基于数组的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！