Comparando a primeira parte de uma string no Excel

Question

Estou tentando criar uma função automatizada que determinará a identificação de classe para os endereços IP depois que você os insere na seção de entrada da coluna A, verificando a primeira seção do endereço. Exemplo:

Column A  | Column B

10.250.1.1 | Class A

(A seção de entrada é onde o endereço está.)

Ele faria isso verificando a primeira parte do endereço (10) .250.1.1 e fazendo a correspondência com a classe. As aulas variam assim:

Classe A = 1 a 127

Classe B = 128 a 191

Classe C = 192 a 223

Então, outro exemplo seria

Column A  | Column B

194.250.1.1 | Class C

Eu tinha tentado criar uma instrução IF na Coluna B que determinaria qual faixa a parte inicial do endereço IP se encaixaria no IE: 58.250.1.1 E calcularia que 58 se encaixa na Classe A, já que ela está entre 1 e 127. No entanto, estou inseguro sobre como separar o "58" de todo o IP, eu sei como compará-lo usando o IP inteiro, mas não apenas uma seção dele. -

    

Answer 1

Isso faz o que você quer, se o VBa estiver OK?

Você pode ver os valores inicial e final. Este é o intervalo para cada uma das três classes (daí o A, B e C). Então você pode ver classA, classB e classC - eu mantive estes caso você precisava alterar o valor por qualquer motivo. A última é, em qual coluna o IP está (a columnToLookUp e por fim qual coluna você quer os resultados. Eu escolhi A e B respectivamente).

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

Além disso, pode ser útil: Como eu adiciono o VBA no MS Escritório?

    

Answer 2

Você pode fazer isso com uma única fórmula bastante direta. Para o endereço IP em A1 , use o seguinte:

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

Quebrando:

VALUE(LEFT(A1,FIND(".",A1)-1)) usa funções de string para identificar a primeira parte do endereço e lançá-lo como um número, não como uma string.

Esse número é então correspondido à matriz de limites inferiores da classe {1,128,192,224} . MATCH com o último argumento 1 retorna a última posição nessa matriz que é menor que o número, por exemplo, 25 retornaria 1 , 150 retornaria 2 .

Essa posição é passada para a função INDEX , que retorna o valor nessa posição na matriz de categorias {"Class A","Class B","Class C"} .

Esta fórmula retornará #N/A para valores menores que 1 e #REF! para valores maiores que 223.

    

Answer 3

Você pode fazer isso usando a função LOOKUP. A sintaxe é

LOOKUP ( lookup_value, lookup_vector, [result_vector])

O bom de LOOKUP é que, se LOOKUP não encontrar o lookup_value exato, ele corresponderá ao maior valor no lookup_vector que é menor que ou igual a o lookup_value .

A figura abaixo mostra algumas maneiras de fazer isso, nas colunas D e E, usando ajudantes nas colunas B e C.

    

Answer 4

O VBA é ótimo para funções avançadas, mas tem um preço, seu código agora inclui macro e, a menos que você tenha um certificado digital, a microsoft vai martelá-lo com mensagens de segurança. Se a planilha é apenas para você, você pode desativá-la, mas isso tornará o compartilhamento de sua planilha com outro pesadelo.

Felizmente, para o seu problema, existe uma solução mais simples no Excel puro:

Considerando a seguinte disposição:

• Endereços IP na coluna A
• Primeiro byte do endereço IP na coluna B
• Classe de resultado na coluna C

AfórmulanacolunaBseria:

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

A fórmula na coluna C seria:
=IF(B1<128,"Class A",IF(B1<192,"Class B",IF(B1<256,"Class C","Not a valid IP")))

Se você não se incomoda com a legibilidade, pode até evitar a coluna de ajuda:

UsandonacolunaB:

=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")))