В редакторе 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 | C |
| 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.