☰

在 Excel 中比较字符串的第一部分

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 ip-address
在 Excel 中比较字符串的第一部分

我正在尝试创建一个自动化函数，该函数将在您将 IP 地址输入到 A 列的输入部分后通过检查地址的第一部分来确定 IP 地址的类别标识。示例：

Column A  | Column B

10.250.1.1 | A 类

（输入部分是地址所在的位置。）

它会通过检查地址的第一部分 (10).250.1.1 并将其与类别进行匹配来实现这一点。类别范围如下：

A 类 = 1 至 127

B 类 = 128 至 191

C 类 = 192 至 223

另一个例子是

Column A  | Column B

194.250.1.1 | C 类

我曾尝试在 B 列中创建一个 IF 语句，以确定 IP 地址的初始部分适合哪个范围 IE：58.250.1.1，并且它会认为 58 适合 A 类，因为它介于 1 和 127 之间。但是我不确定如何将“58”与整个 IP 区分开来，我知道如何通过使用整个 IP 而不是其中的一部分来进行比较。–

答案1

如果 VBa 可以的话，这将满足您的要求吗？

因此，您可以看到起始值和结束值。这是 3 个类别（因此是 A、B 和 C）的范围。然后您可以看到 classA、classB 和 classC - 我保留了这些，以防您出于任何原因需要更改值。最后是，IP 位于哪一列下（columnToLookUp最后是您想要结果的哪一列。我分别选择了 A 和 B）。

Option Explicit
Sub DoThis()

'Edit this top part as you need

Dim startClassA As Integer
startClassA = 1

Dim endClassA As Integer
endClassA = 127

Dim startClassB As Integer
startClassB = 128

Dim endClassB As Integer
endClassB = 191

Dim startClassC As Integer
startClassC = 192

Dim endClassC As Integer
endClassC = 223

Dim classA As String
classA = "Class A"

Dim classB As String
classB = "Class B"

Dim classC As String
classC = "Class C"

Dim columnToLookUp As String
columnToLookUp = "A"

Dim resultColumn As String
resultColumn = "B"

'no need to edit below this (hopefully) ***************

Dim row As Integer
row = 1

Do While (Range(columnToLookUp & row).Value <> "")
    Dim ip() As String

    ip = Split(Range(columnToLookUp & row).Value, ".")

    Dim ipSub As Integer
    ipSub = ip(0)

    If (ipSub >= startClassA And ipSub <= endClassA) Then
        Range(resultColumn & row).Value = classA
    End If

    If (ipSub >= startClassB And ipSub <= endClassB) Then
        Range(resultColumn & row).Value = classB
    End If

    If (ipSub >= startClassC And ipSub <= endClassC) Then
        Range(resultColumn & row).Value = classC
    End If


row = row + 1
Loop


End Sub

另外，可能会有帮助：如何在 MS Office 中添加 VBA？

答案2

您可以使用一个相当简单的公式来完成此操作。对于 中的 IP 地址A1，使用以下内容：

=INDEX({"Class A","Class B","Class C"},MATCH(VALUE(LEFT(A1,FIND(".",A1)-1)),{1,128,192,224},1))

具体来说：

VALUE(LEFT(A1,FIND(".",A1)-1))使用字符串函数识别地址的第一部分并将其转换为数字而不是字符串。

然后将该数字与类下限数组进行匹配{1,128,192,224}。MATCH最后一个参数1返回此数组中小于该数字的最后一个位置，例如，25将返回1，150将返回2。

然后将该位置传递给INDEX函数，该函数返回类别数组中该位置的值{"Class A","Class B","Class C"}。

此公式将返回#N/A小于 1 的值和#REF!大于 223 的值。

答案3

您可以使用 LOOKUP 函数来实现。语法如下

抬头（查找值，查找向量，[结果向量]）

LOOKUP 的优点在于，如果 LOOKUP 无法找到确切的查找值，它匹配查找向量那是小于或等于这查找值。

下图展示了几种方法，在 D 列和 E 列中使用 B 列和 C 列中的辅助程序。

在此处输入图片描述

答案4

VBA 非常适合高级功能，但需要付出代价，您的代码现在包含宏，除非您拥有数字证书，否则微软会向您发送安全消息。如果工作表仅供您使用，您可以禁用此功能，但这会使与其他人共享工作表成为一场噩梦。

幸运的是，对于您的问题，纯 Excel 中有一个更简单的解决方案：

考虑以下安排：

  • A 列中的 IP 地址
  • B 列中的 IP 地址第一个字节
  • C 列中的结果类别

桌子

B 列中的公式为：

=VALUE(LEFT(A1,FIND(".",A1,1)-1))

C 列中的公式为：
=IF(B1<128,"Class A",IF(B1<192,"Class B",IF(B1<256,"Class C","Not a valid IP")))

如果你不关心可读性，你甚至可以避免使用帮助列： 短表

在 B 列中使用：

=IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<128,"Class A",IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<192,"Class B",IF(VALUE(LEFT(A1,FIND(".",A1,1)-1))<256,"Class C","Not a valid IP")))

相关内容