excel - Excel中带有减号的查找功能的说明

Question

在寻找从字母数字字符串中提取数字的解决方案时，我在MrExcel论坛中偶然发现了以下公式。我很难理解公式中LOOKUP和RIGHT函数前面的减号在做什么。公式为

=-LOOKUP(1,-RIGHT(TRIM(LEFT(D1,FIND("g/L",D1)-1)),{1,2,3,4,5,6}))

有人可以向我解释该公式的含义以及为什么其中有减号吗？

Answer 1

TL; DR
简短的答案是减号乘以-1

首先由RIGHT函数返回的数组中的项，故意在数组中生成#VALUE!错误（因为字符串不能相乘），并且

第二个相反的事实是，候选对象从LOOKUP公式（数组中的数字作为字符串）的答案现在为负，但是一旦整个公式计算完毕，就必须为正。


关于MrExcel的原始问题
该论坛上的OP要求：

在寻找最佳公式以从可变长度字母数字字符串中提取VOC含量值方面寻求帮助。 VOC含量是“ g / L”之前的数字。

数据样本是：
SEALANT-RETAINING COMPOUND SINGLE COMPONENT ANAEROBIC, SCR40372A, *Loctite 242, 10 ML BOTTLE,146 g/L VOC
SEALANT-THREAD LOCK *HIGH STR *MIL-S-22473 *GRADE AA *50 ML BOTTLE * * *SCR802269A\ * Loctite 089 Weld Sealant125.3 g/L VOC
他们想要的答案是146或125.3。
在线程中，一个应答器（pgc01）发布以下公式：
=-LOOKUP(1,-RIGHT(TRIM(LEFT(D1,FIND("g/L",D1)-1)),{1,2,3,4,5,6}))
（其中D1将包含该字符串）
简化的例子
原始线程正在从占位符为g/L的未知长度的字符串中提取未知长度的数值。放在一边，让我们简单地考虑以下字符串：
foobar123
foobar1234
foobar12345
挑战所在是在不知道数字长度的情况下获得数字部分（数字不会超过6位）。您不能只正常使用RIGHT，因为您不知道要在第二个参数中输入多少个字符。我们不使用VBA，所以我们需要一种遍历文本并弄清楚什么是数字和非数字字符串的方法，以便我们可以提取数字部分。
公式分析
所以现在您要看的公式就是：
=-LOOKUP(1,-RIGHT("foobar123",{1,2,3,4,5,6}))
分解公式：

RIGHT("foobar123",{1,2,3,4,5,6})-在整个公式的上下文中，这将返回结果数组3，23，123，r123，ar123，bar123。您通常无法在Excel中直接直接看到它，但是如果您使用“插入函数”向导来检查LOOKUP公式，则可以。



在RIGHT函数的前面添加减号的作用是将数组的每个项目乘以-1，这现在为我们提供了-3，-23，-123，#VALUE!，< cc>，#VALUE!。错误值是有目的的，因为我们现在可以推断出字符串的数字部分是3位数字，因为任何其他正确的权利都会产生错误，即您不能将字母字符乘以-1并得到有意义的结果。



接下来使用#VALUE!函数在此数组中找到1，即LOOKUP。数组中所有数值结果均为负数，因此所有结果均小于1。=LOOKUP(1, etc)函数将返回数组中小于或等于查找值（见下文）的最大值。将是具有最多数字的结果，因此是正确的“捕获”，因为我们正在从右侧搜索字符串的数字部分。它暗示该行为只是跳过了LOOKUP错误。因此，在我们的示例中，这将返回#VALUE!。

From MSDN:

数组形式
LOOKUP的数组形式在数组的第一行或第一列中查找指定的值，并从数组的最后一行或最后一列的相同位置返回一个值。当您要匹配的值位于数组的第一行或第一列时，请使用这种形式的LOOKUP。要指定列或行的位置时，请使用其他形式的LOOKUP。
提示通常，最好使用HLOOKUP或VLOOKUP函数，而不要使用LOOKUP的数组形式。提供此形式的LOOKUP是为了与其他电子表格程序兼容。
如果LOOKUP找不到lookup_value，则使用数组中小于或等于lookup_value的最大值。
如果lookup_value小于第一行或第一列中的最小值（取决于数组维），则LOOKUP返回错误值＃N / A。


最后，-123函数之前的减号运算符将评估结果乘以-1，得到LOOKUP。

测验
123给出=-LOOKUP(1,-RIGHT("foobar123",{1,2,3,4,5,6}))
123给出=-LOOKUP(1,-RIGHT("foobar12345",{1,2,3,4,5,6}))
12345给出=-LOOKUP(1,-RIGHT("foobar123456789",{1,2,3,4,5,6}))（因为456789函数的第二个参数中的数组最多为6。
由于RIGHT位，=-LOOKUP(1,-RIGHT("foobar123baz",{1,2,3,4,5,6}))给出#N/A。
因此，在MrExcel的原始线程中，使用查找字符串中的baz项并将其左侧作为确保某些未知数（最多6个）最右边字符的方式。数字。
结论
某些人可能此时会想退出VBA和正则表达式工具，但对于想要或需要使用公式进行所有操作的人来说，这提供了一种进行模糊匹配以识别文本数据中重要信息的方法。