Сравнение первой части строки в Excel

Question

Я пытаюсь создать автоматизированную функцию, которая будет определять идентификацию класса для IP-адресов после того, как вы введете его в раздел ввода столбца А, проверив первый раздел адреса. Пример:

Column A  | Column B

10.250.1.1 | Класс А

(В разделе ввода указан адрес.)

Это будет сделано путем проверки первой части адреса (10).250.1.1 и сопоставления ее с классом. Диапазон классов таков:

Класс А = от 1 до 127

Класс B = от 128 до 191

Класс C = 192 до 223

Так что еще один пример будет

Column A  | Column B

194.250.1.1 | Класс С

Я попытался создать оператор IF в столбце B, который бы определял, в какой диапазон будет входить начальная часть IP-адреса в IE: 58.250.1.1. И это будет означать, что 58 соответствует классу A, поскольку он находится между 1 и 127. Однако я не уверен, как отделить "58" от всего IP, я знаю, как сравнить его, используя весь IP, а не только его часть. -

Answer 1

Вы можете сделать это с помощью одной, довольно простой формулы. Для 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.

Answer 2

Это делает то, что вы хотите, если 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

Также может быть полезно: Как добавить VBA в MS Office?

Answer 3

Вы можете сделать это, используя функцию LOOKUP. Синтаксис

LOOKUP(lookup_value, lookup_vector, [result_vector])

Приятной особенностью LOOKUP является то, что если LOOKUP не может найти точное значение lookup_value, оно соответствует наибольшему значению в lookup_vector, которое меньше или равно lookup_value.

На рисунке ниже показано несколько способов сделать это в столбцах D и E с использованием помощников в столбцах B и C.

Answer 4

VBA отлично подходит для расширенных функций, но стоит своей цены, теперь ваш код включает в себя макрос, и если у вас нет цифрового сертификата, Microsoft собирается забить вас сообщениями безопасности. Если лист предназначен только для вас, вы можете отключить это, но это сделает обмен вашим листом с другими кошмаром.

К счастью, для вашей проблемы есть простое решение в чистом Excel:

Учитывая следующую договоренность:

• IP-адреса в столбце A
• Первый байт IP-адреса в столбце B
• Класс результата в столбце 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")))