Вопрос или проблема
У меня есть огромный документ Excel, который содержит список IP-адресов, но также есть записи, которые имеют подсети. Например, я могу увидеть “IP” “/16”, и первые два октета будут первыми двумя октетами подсетированного IP. Я хочу узнать, есть ли способ в Excel проверить, совпадают ли первые два октета IP с одной из строк с подсетью /16. В основном, я хочу знать, есть ли способ найти записи, которые перекрывают друг друга. Например, 192.168.1.1 и 192.168.0.0 /16, тогда 192.168.1.1 будет выделен красным или как-то еще. Я знаю, что нужно использовать функцию if, или по крайней мере, я думаю так, но не могу разобраться, как разделить два октета и сравнить их, а также учесть тот факт, что тот, с которым нужно сравнивать, должен иметь “/16” в той же строке. Любая помощь будет очень полезна, и если это совсем не имеет смысла, напишите в комментариях, и я постараюсь прояснить. Спасибо!
Я думаю, что самое простое – сначала разбить IP на октеты. Затем вы можете использовать функцию объединения, чтобы заново объединить 1-й и 2-й октеты. С этого момента вы можете использовать условное форматирование, vlookup или любой другой метод, который вам нравится, чтобы сравнить значения.
Я искал некоторое руководство по манипуляции с IP для подобного личного проекта и нашел следующую статью крайне полезной.
Я обнаружил, что формула для второго октета была немного багованной, поэтому я её доработал.
# Вернуть Октет 1 IP в ячейке A2
=LEFT(A2,FIND(".",A2)-1)
# Вернуть Октет 2 IP в ячейке A2
=LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
# Вернуть Октет 3 IP в ячейке A2
=LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
# Вернуть Октет 4 IP в ячейке A2
=RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))))
=LEFT(A2,IFERROR(FIND(“.”,SUBSTITUTE(TRIM(SUBSTITUTE(A2,”.”,” “,1)),”.”,” “,1)),0))
Отталкиваясь немного от ответа Дэвида Кеннеди, скажем, подсеть в A2 отформатирована как 192.168.1.1/30 и работает только для подсетей от /32 до /24
Первый октет B2: =LEFT(A2,FIND(".",A2)-1)
Второй октет C2: =LEFT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-1)
Третий октет D2: =LEFT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))))-1)
Четвертый октет E2: =LEFT(RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2)))))))),FIND("/",RIGHT(RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))),(LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(RIGHT(A2,(LEN(A2)-FIND(".",A2))),LEN(RIGHT(A2,(LEN(A2)-FIND(".",A2))))-FIND(".",RIGHT(A2,(LEN(A2)-FIND(".",A2)))))))))-1)
Конец подсети F2: =E2+(2^(32-RIGHT(A2,LEN(A2)-FIND("/",A2))))-1
int версия первого IP подсети G2: =VALUE(CONCAT(TEXT(B2,0),TEXT(C2,"000"),TEXT(D2,"000"),TEXT(E2,"000")))
int версия последнего IP подсети H2: =VALUE(CONCAT(TEXT(B2,0),TEXT(C2,"000"),TEXT(D2,"000"),TEXT(F2,"000")))
После этого вы можете сравнить начало, чтобы увидеть, попадает ли оно в одну из перечисленных подсетей, самый простой способ будет следующим: A4 – это ваш IP, который вы хотите проверить, находится ли он в подсети, отформатированной как 192.168.1.3:
int ip B4 : =VALUE(CONCATENATE(TEXT(MID(A4,1,FIND(".",A4,1)-1),"000"),TEXT(MID(A4,FIND(".",A4,1)+1,FIND(".",A4,1+FIND(".",A4,1))-FIND(".",A4,1)-1),"000"),TEXT(MID(A4,FIND(".",A4,1+FIND(".",A4,1))+1,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))-FIND(".",A4,1+FIND(".",A4,1))-1),"000"),TEXT(MID(A4,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))+1,FIND(".",A4,1+FIND(".",A4,1+FIND(".",A4,1)))-FIND(".",A4,1+FIND(".",A4,1))+1),"000")))
Если в подсети C4: =AND(B4>=G$2,B4<=H$2)
В этом конкретном случае я бы проверил первый и последний указанные IP, чтобы посмотреть, попадает ли он внутри октета вообще
| 192.168.1.1/30 | 192 | 168 | 1 | 1 | 4 | 192168001001 | 192168001004 |
|---|---|---|---|---|---|---|---|
| 192.168.1.3 | 192168001003 | TRUE | |||||
| 192.168.1.5 | 192168001005 | FALSE |
Попробуйте это для четвертого октета.
TRIM(RIGHT(SUBSTITUTE(A2,”.”,REPT(” “,15)),15))
Ответ или решение
Поиск IP-адресов, охватываемых подсетями в документе Excel
С учетом поставленной задачи по проверке IP-адресов на соответствие подветвям с использованием Excel, мы можем использовать комбинацию формул и условного форматирования для выделения пересечений. Давайте шаг за шагом рассмотрим процесс выполнения этой задачи.
Шаг 1: Подготовка данных
Эффективная работа с IP-адресами и подсетями требует, прежде всего, разбивки данных. Предположим, у вас в столбце A содержатся IP-адреса и подсети. Например:
А
1 192.168.1.1
2 192.168.0.0/16
3 10.0.0.5
4 10.0.0.0/8Шаг 2: Разделение IP-адресов на октеты
Для обработки IP-адресов, мы можем разбить их на отдельные октеты. Мы используем статические формулы в Excel, чтобы выделить каждый октет:
- Первый октет (К ячейке B1):
=LEFT(A1, FIND(".", A1) - 1) - Второй октет (К ячейке C1):
=MID(A1, FIND(".", A1) + 1, FIND(".", A1, FIND(".", A1) + 1) - FIND(".", A1) - 1) - Третий октет (К ячейке D1):
=MID(A1, FIND(".", A1, FIND(".", A1) + 1) + 1, FIND(".", A1, FIND(".", A1, FIND(".", A1) + 1) + 1) - FIND(".", A1, FIND(".", A1) + 1) - 1) - Четвертый октет (К ячейке E1):
=MID(A1, FIND(".", A1, FIND(".", A1, FIND(".", A1) + 1) + 1) + 1, LEN(A1) - FIND(".", A1, FIND(".", A1, FIND(".", A1) + 1) + 1))
Шаг 3: Определение диапазонов подсетей
Если в ячейках есть подсети, их тоже нужно разбить на составляющие части. Для подсетей в формате 192.168.0.0/16 введите аналогичные формулы для вычисления начального IP-адреса и маски.
- Начальный IP-адрес подсети (К ячейке F1):
=LEFT(A2, FIND("/", A2) - 1) - Маска подсети (К ячейке G1):
=RIGHT(A2, LEN(A2) - FIND("/", A2))
Шаг 4: Сравнение IP-адресов с подсетями
Теперь, когда у нас есть все октеты для IP-адресов и подсетей, проведем сравнение. Необходимо проверить, входят ли IP-адреса в диапазоны подсетей.
Для каждой записи IP-адреса используем следующую формулу в столбце H (например, для IP в A1):
=AND(B1=LEFT(F2, FIND(".", F2, FIND(".", F2) + 1) - 1), C1=MID(F2, FIND(".", F2) + 1, FIND(".", F2, FIND(".", F2) + 1) - FIND(".", F2) - 1))Эта формула проверяет соответствие первых двух октетов IP-адреса подсети.
Шаг 5: Условное форматирование
Чтобы визуально выделить совпадения, используйте условное форматирование:
- Выделите диапазон с IP-адресами.
- Перейдите в "Условное форматирование" и выберите "Создать правило".
- Выберите "Использовать формулу для определения формата".
- Введите формулу, которая будет проверять соответствие, и задайте цвет выделения.
Заключение
Использование Excel для анализа и сопоставления IP-адресов с подсетями – задача, которая требует тщательной обработки данных и применения формул. Следуя предложенному плану, вы сможете эффективно находить конфликты и пересечения между IP-адресами и подсетями. Не забудьте сохранить вашу работу, чтобы зафиксировать все изменения и результаты анализа.