☰

Excel 公式用于比较一个单元格中的单个值与另一个单元格中的多个值

tag-icon • tag-icon microsoft-excel worksheet-function vba
Excel 公式用于比较一个单元格中的单个值与另一个单元格中的多个值

我在 A 列中有一个值，我想将其与 B 列中的多个值进行比较，并根据该值将答案放在 C 列中。

例如，使用下表，它在 B 列中搜索小于或等于 12 的值，并将答案以相同的顺序放在 C 列中。

Column A     Column B            Column C
12           0,12,13,14          Yes, Yes, No, No    
101          101,102,103,104     Yes, No, No, No

我如何在 Excel 中执行此操作？

答案1

这正是您想要的。

Option Explicit

Sub DoTheThing()

Dim row As Integer
row = 1 ' WHAT IS THE STARTING ROW

Do While (Range("A" & row).Value <> "")

    Dim vals() As String
    vals = Split(Range("B" & row).Value, ",")

    Dim lookUpValue As String
    lookUpValue = Range("A" & row).Value

    Dim result As String
    result = ""

    Dim i As Integer

    For i = 0 To UBound(vals)

        If CSng(lookUpValue) >= CSng(vals(i)) Then
            result = result & "Yes, "
        Else
            result = result & "No, "
        End If

    Next i

    result = Trim(result)
    result = Left(result, Len(result) - 1)

    Range("C" & row).Value = result

    row = row + 1
Loop

End Sub

我的工作表看起来像

在此处输入图片描述

在我运行 VBa 之后

在此处输入图片描述

Excel 一直将列格式化为number。它必须保留为Text！

答案2

我的做法是先将问题分解成多个列，每个列代表一个问题。例如：

     A  B                C  D   E    F   G    H    I   J    K    L   M   N
1   12  0,12,13,14       2  5   8    0   12   13   14  Yes  Yes  No  No  Yes, Yes, No, No
2  101  101,102,103,104  4  8  12  101  102  103  104  Yes  No   No  No  Yes, No, No, No

这些是 C1 到 N1 的表达式：

C1 =FIND(",",B1)               D1 =FIND(",",B1,C1+1)      E1 =FIND(",",B1,D1+1)
F1 =LEFT(B1,C1-1)+0            G1 =MID(B1,C1+1,D1-C1-1)+0
H1 =MID(B1,D1+1,E1-D1-1)+0     I1 =RIGHT(B1,LEN(B1)-E1)+0
J1 =IF(F1<=$A1,"Yes","No")     K1, L1, M1 (copy from J1)
N1 =J1&", "&K1&", "&L1&", "&M1

如果情况不明显，“+0”是一种将文本值强制转换为数字的方便方法，这样 I、J、K 和 L 中的比较就是作为数字比较而不是文本比较完成的。

对于 C2 到 N2，从 C1 到 N1 复制。

如果您不想使用额外的列，您可以将多列版本的结果合并为单个列中的一个巨大而复杂的表达式。分几个步骤完成会更容易。例如，第一步是将 FIND 表达式与字符串表达式组合起来。以下是一些代码：

F =LEFT(B1,FIND(",",B1)-1)+0
G =MID(B1,FIND(",",B1)+1,FIND(",",B1,FIND(",",B1)+1)-FIND(",",B1)-1)+0
H =MID(B1,FIND(",",B1,FIND(",",B1)+1)+1,FIND(",",B1,FIND(",",B1,FIND(",",B1)+1)+1)-FIND(",",B1,FIND(",",B1)+1)-1)+0
I =RIGHT(B1,LEN(B1)-FIND(",",B1,FIND(",",B1,FIND(",",B1)+1)+1))+0

这些非常可怕，因为 E 的用途使用了 D，而 D 又使用了 C，而 G、H 和 I 又多次使用了这些。将所有中间结果放入隐藏列可以节省大量重复的表达式。

如果您想在 B 列中超过四个逗号分隔的数字，情况会变得更糟，但添加列的方式应该非常明显。

允许 B 具有可变数量的逗号分隔数字并不那么明显。诀窍是添加一些 IF 语句，测试错误条件。这引出了最后一点，即这不包括除 Excel 内置功能之外的任何错误检查。一个强大的电子表格应该至少包括一些错误检查。

答案3

我会为此使用 Power Query 插件。它具有拆分和合并命令，可以将分隔文本（例如 0、12、13、14）转换为列表并转回。

我已经构建了一个原型，您可以查看或下载它 - 在我的 One Drive 中是“Power Query 演示 - 将一个单元格中的单个值与其他单元格中的多个值进行比较.xlsx”：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

在该文件中，我包含了两个解决方案 - 一个用于使用“小于或等于”进行比较，另一个用于使用“之间”逻辑进行比较。

需要几个查询步骤才能到达那里，并且对于某些步骤，生成的代码需要编辑。但对于 90% 的步骤，您只需在 Power Query UI 中单击即可。

相关内容