Сортировка IP адресов в Excel

Опубликовано: 17.11.2016
Автор: Виталий Бочкарев
Поддержать автора статьи по этой ссылке

Для того, чтобы отсортировать IP адреса в Excel, нужно создать дополнительные ячейки, в которые вынести составные части адреса по которым делать сортировку.

Выделение составных частей можно сделать по следующим формулам:

A2=111.222.333.444
B2=VALUE(LEFT(A2;SEARCH(".";A2;1)-1))
C2=VALUE(MID(A2;SEARCH(".";A2;1)+1;SEARCH(".";A2;SEARCH(".";A2;1)+1)-SEARCH(".";A2;1)-1))
D2=VALUE(MID(A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1;SEARCH(".";A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1)-SEARCH(".";A2;SEARCH(".";A2;1)+1)-1))
E2=VALUE(MID(A2;SEARCH(".";A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1)+1;IFERROR(SEARCH("/";A2;SEARCH(".";A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1)+1)-SEARCH(".";A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1)-1;LEN(A2)-SEARCH(".";A2;SEARCH(".";A2;SEARCH(".";A2;1)+1)+1))))

Здесь A2 - исходный IP адрес (например 111.222.333.444), B2 - первая часть адреса (в примере это 111), C2 - вторая часть адреса (в примере это 222), D2 - третья часть адреса (в примере это 333), E2 - четвертая часть адреса (в примере 444).

Примечание. Адреса со значением 111.222.333.444 не существует, так как максимальное значение составляющей IP адреса - это 255. Этот адрес указан лишь для понимания работы формул вычленения составляющих адреса.

В дополнение к вычленению составляющих IP адреса формула в ячейке E2 содержит фильтрацию подсети, отделенную от адреса знаком слэш (например 192.168.1.0/24).

В Excel все вышесказанное выглядит так

Servers - IP addresses sorting

Сортировка устанавливается следующими значениями

Servers - IP addresses sorting

Ниже можно найти XLSX файл с примером сортировки IP адресов.

Вложения
Метки