是否向excel公式if语句添加or函数？

Question

我有一个if语句，它搜索一个单元格，看看它是否包含多个值中的一个，如果该值存在，则显示来自另一个单元格的值。

因此，在单元格A1中，我有以下代码：

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),CONCATENATE(HYPERLINK(AV10)),"")

此代码在单元格G10中搜索SLR、SMR和BRL。

如果该值存在，则显示单元格AV10中的值

我想添加一个or-语句，这样我就可以有多个if-语句，如下所示

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),CONCATENATE(HYPERLINK(AV10)),""), OR,
 IF(COUNT(SEARCH({"TTT","RRR","BBB"},G10)),CONCATENATE(HYPERLINK(AV11)),""), OR, 
 IF(COUNT(SEARCH({"ZZZ","XXX","YYY"},G10)),CONCATENATE(HYPERLINK(AV12)),""), OR, 
 IF(COUNT(SEARCH({"LLL","MMM","QQQ"},G10)),CONCATENATE(HYPERLINK(AV11)),""), OR, 
 IF(COUNT(SEARCH({"UUU","KKK","PPP"},G10)),CONCATENATE(HYPERLINK(AV13)),""),"ERROR")

因此，我们这里有一个很大的if语句，它检查一个单元格是否包含一组三个值"SLR", "SMR", OR "BRL"，如果包含，则以AV10格式显示链接；如果单元格不包含这三个值，但inStead包含"TTT", "RRR", "BBB"，则以AV11格式显示值，依此类推

有谁能帮我一下吗？

提前感谢

Answer 1

通常的做法是将if语句的else部分中的空字符串替换为下一个if语句。那么单词ERROR就出现在最后一条if语句的最后一个else中。如下所示：

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),CONCATENATE(HYPERLINK(AV10)),
 IF(COUNT(SEARCH({"TTT","RRR","BBB"},G10)),CONCATENATE(HYPERLINK(AV11)),
 IF(COUNT(SEARCH({"ZZZ","XXX","YYY"},G10)),CONCATENATE(HYPERLINK(AV12)),
 IF(COUNT(SEARCH({"LLL","MMM","QQQ"},G10)),CONCATENATE(HYPERLINK(AV13)),
 IF(COUNT(SEARCH({"UUU","KKK","PPP"},G10)),CONCATENATE(HYPERLINK(AV14)),
 "ERROR")))))

我不知道为什么你会有一个连接函数--它连接了它的参数，但是每个参数只有一个参数，所以它实际上什么也做不了。除非我不明白你做了什么，否则这应该做同样的事情：

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),HYPERLINK(AV10),
 IF(COUNT(SEARCH({"TTT","RRR","BBB"},G10)),HYPERLINK(AV11),
 IF(COUNT(SEARCH({"ZZZ","XXX","YYY"},G10)),HYPERLINK(AV12),
 IF(COUNT(SEARCH({"LLL","MMM","QQQ"},G10)),HYPERLINK(AV13),
 IF(COUNT(SEARCH({"UUU","KKK","PPP"},G10)),HYPERLINK(AV14),
 "ERROR")))))

如果AV10包含=HYPERLINK("\\UKSH000-FILE06\Purchasing\New Supplier Set-Ups\assets\EMAIL_PLANT.msg","Send Email")，则=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),HYPERLINK(AV10),"")公式将创建单词Send Email的超链接，但单击它们不是有效地址。

因此，您真正想要做的是将AV10更改为=HYPERLINK("\\UKSH000-FILE06\Purchasing\New Supplier Set-Ups\assets\EMAIL_PLANT.msg")或\\UKSH000-FILE06\Purchasing\New Supplier Set-Ups\assets\EMAIL_PLANT.msg，并更改我提供给您的论坛：

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),HYPERLINK(AV10,"Send Email"),
 IF(COUNT(SEARCH({"TTT","RRR","BBB"},G10)),HYPERLINK(AV11,"Send Email"),
 IF(COUNT(SEARCH({"ZZZ","XXX","YYY"},G10)),HYPERLINK(AV12,"Send Email"),
 IF(COUNT(SEARCH({"LLL","MMM","QQQ"},G10)),HYPERLINK(AV13,"Send Email"),
 IF(COUNT(SEARCH({"UUU","KKK","PPP"},G10)),HYPERLINK(AV14,"Send Email"),
 "ERROR")))))

现在，计算出的超链接显示为Send Email，但地址正确(当您将鼠标悬停在单元格上时，可以在工具提示中看到地址)。如果您不喜欢让AV10 - AV14显示实际地址，并且希望它们也显示发送电子邮件，那么该公式需要在AV10 - AV14中提取超链接的地址。

根据http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/的说法，没有内置的函数，但是如果你在那个页面上添加VBA函数，那么AV10可以保持为=HYPERLINK("\\UKSH000-FILE06\Purchasing\New Supplier Set-Ups\assets\EMAIL_PLANT.msg","Send Email")，你的公式将如下所示：

=IF(COUNT(SEARCH({"SLR","SMR","BRL"},G10)),HYPERLINK(HLink(AV10),"Send Email"),
 IF(COUNT(SEARCH({"TTT","RRR","BBB"},G10)),HYPERLINK(HLink(AV11),"Send Email"),
 IF(COUNT(SEARCH({"ZZZ","XXX","YYY"},G10)),HYPERLINK(HLink(AV12),"Send Email"),
 IF(COUNT(SEARCH({"LLL","MMM","QQQ"},G10)),HYPERLINK(HLink(AV13),"Send Email"),
 IF(COUNT(SEARCH({"UUU","KKK","PPP"},G10)),HYPERLINK(HLink(AV14),"Send Email"),
 "ERROR")))))

现在一切都显示发送电子邮件，但所有的超链接应该可以工作。