Как отсортировать IP-адреса в Excel’e

В редакторе MS Excel, вплоть до версии 2008 (для MacOS), отсутствует такой тип данных, как IP-адрес.
При сортировке по возрастанию Эксель выдает нечто такое:

<> A B
1 host j 1.2.3.4
2 host x 1.2.3.40
3 host b 1.2.3.5
4 host k 11.2.2.3

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

Формула перевода IP-адреса в десятичную форму для англоязычной версии Excel:

=((VALUE(LEFT(B1; FIND(“.”; B1)-1)))*256^3)+((VALUE(MID(B1; FIND(“.”; B1)+1; FIND(“.”; B1; FIND(“.”; B1)+1)-FIND(“.”; B1)-1)))*256^2)+((VALUE(MID(B1; FIND(“.”; B1; FIND(“.”; B1)+1)+1; FIND(“.”; B1; FIND(“.”; B1; FIND(“.”; B1)+1)+1)-FIND(“.”; B1; FIND(“.”; B1)+1)-1)))*256)+(VALUE(RIGHT(B1; LEN(B1)-FIND(“.”; B1; FIND(“.”; B1; FIND(“.”; B1)+1)+1))))

Формула перевода IP-адреса в десятичную форму для русскоязычной версии Excel:

=(((ЗНАЧЕН(ЛЕВСИМВ(B1;ПОИСК(“.”;B1)-1))))*256^3)+((ЗНАЧЕН(ПСТР(B1; ПОИСК(“.”; B1)+1; ПОИСК(“.”; B1; ПОИСК(“.”; B1)+1)-ПОИСК(“.”; B1)-1)))*256^2)+((ЗНАЧЕН(ПСТР(B1; ПОИСК(“.”; B1; ПОИСК(“.”; B1)+1)+1; ПОИСК(“.”; B1; ПОИСК(“.”; B1; ПОИСК(“.”; B1)+1)+1)-ПОИСК(“.”; B1; ПОИСК(“.”; B1)+1)-1)))*256)+(ЗНАЧЕН(ПРАВСИМВ(B1; ДЛСТР(B1)-ПОИСК(“.”; B1; ПОИСК(“.”; B1; ПОИСК(“.”; B1)+1)+1))))

В итоге получаем следующее:

 <> A B
1 host j 1.2.3.4 16909060
2 host b 1.2.3.5 16909061
3 host x 1.2.3.40 16909096
4 host k 11.2.2.3 184680963

Формула вставлена в ячейку C1.

Написано на основе статьи на mvps.org.

 

Leave a Reply

Your email address will not be published. Required fields are marked *