Как отсортировать ip адреса в excel
Обычно мы используем функцию сортировки для сортировки строки в Excel. Но если есть некоторые IP-адреса, необходимые для сортировки, порядок сортировки может быть неправильным, если использовать функцию сортировки напрямую, как показано ниже. Теперь у меня есть несколько способов быстрой и правильной сортировки IP-адресов в Excel.
| Неверная сортировка по функции сортировки | Правильная сортировка |
 |  |
Сортировать IP-адрес по формуле
Сортировать IP-адрес по VBA
Сортировка IP-адреса по тексту в столбцы
Сортировка IP-адреса по формуле
Используйте формулу для заполнения IP-адреса, а затем выполните сортировку.
1. Выделите ячейку рядом с IP-адресом и введите эту формулу
нажмите клавишу Enter и перетащите маркер заполнения над ячейками, чтобы применить эту формулу.
2. Скопируйте результаты формулы и вставьте их как значение в следующий столбец. См. Снимок экрана:
3. оставьте вставленное значение выделенным и нажмите Данные > Сортировать от А до Я .
4. В диалоговом окне Sort Waring не снимайте флажок Развернуть выделение .
5. нажмите Сортировать . Теперь IP-адреса отсортированы по возрастанию.
Вы можете удалить вспомогательные столбцы.
Сортировка IP-адреса по VBA
Вот код VBA Это тоже может вам помочь.
1. Нажмите клавиши Alt + F11 , чтобы открыть окно Microsoft Visual Basic для приложений .
2. Нажмите Insert > Module , скопируйте и вставьте код в пустой скрипт.
VBA: Fill IP-адрес
3. Затем нажмите Инструменты > Справочник и отметьте Microsoft VBScript Regular Expressions 5.5 во всплывающем диалоговом окне.
 |
 |
4. Нажмите OK и нажмите F5 , появится диалоговое окно с напоминанием о выборе диапазона для работы.
5. Нажмите ОК . Затем IP-адреса были заполнены нулями.
6. Выберите IP-адреса и нажмите Данные > Сортировать от А до Я , чтобы отсортировать их.
Сортировать IP-адрес по тексту в столбцы
Фактически, функция текста в столбцы может сделаю вам одолжение и в Excel.
1. Выберите используемые ячейки и нажмите Данные > Текст в столбцы . См. Снимок экрана:
2. В диалоговом окне Мастер преобразования текста в столбцы сделайте следующее:
Установите флажок с разделителями и нажмите Далее ;
Отметьте Другое и введите . в текстовое поле и нажмите Далее ;
Выберите ячейку рядом с IP-адресом, чтобы разместить результат. Нажмите Готово .
 |
 |
 |
3. Выберите все ячейки, содержащие IP-адреса и разделенные ячейки, и нажмите Данные > Сортировка .
4. В диалоговом окне Сортировка нажмите Добавить уровень , чтобы отсортировать данные из столбца B в E (разделенные ячейки). См. Снимок экрана:
5. Нажмите ОК . Теперь столбцы отсортированы.
Вы когда-нибудь пробовали разделить IP-адреса на отдельные столбцы в таблице Excel? Возможно, функция Text to Column поможет вам быстро решить эту задачу, но в этой статье я расскажу о некоторых формулах для достижения этой задачи в Excel.
Разделить октеты IP-адресов на отдельные столбцы в Excel
Обычно в Excel функции LEFT, MID, LEN и FIND могут помочь вам извлечь каждый октет из IP-адреса в разные столбцы. Пожалуйста, выполните следующие действия:
Разделите первый октет IP-адресов:
Чтобы извлечь первый октет IP-адреса, примените приведенную ниже формулу в пустую ячейку:
Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все первые октеты IP-адресов были извлечены, см. Снимок экрана:
Пояснение к формуле:
НАЙТИ (".", A2) -1: Эта функция НАЙТИ возвращает положение первой точки из ячейки A2, вычитание 1 означает исключение самой точки, вы получите результат 2.
LEFT(A2,FIND(".",A2)-1)=LEFT(A2, 2):Функция LEFT используется для извлечения 2 символов из левой части строки в ячейке A2.
Разделите второй октет IP-адресов:
Чтобы получить второй октет IP-адресов, скопируйте или введите следующую формулу в пустую ячейку:
Затем перетащите дескриптор заполнения вниз к ячейкам, чтобы заполнить эту формулу, и вы получите нужный результат, см. Снимок экрана:
Пояснение к формуле:
1. НАЙТИ (".", A2) +1: Эта функция НАЙТИ возвращает позицию первой точки из ячейки A2, добавляет 1 означает извлечение из следующего символа, вы получите результат число 4. Эта часть формулы распознается как аргумент start_num функции MID.
2. FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1: Эта часть распознается как аргумент num_chars в функции MID.
- НАЙТИ (".", A2; НАЙТИ (".", A2) +1) = НАЙТИ (".", A2,4) : Эта функция НАЙТИ вернет позицию второй точки. И вы получите цифру 6.
- FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1=6-3-1 : Вычесть положение первой точки из положения второй точки, а затем вычесть 1 из результата означает удалить ведущую точку. И результат 2.
3. MID(A2,FIND(".",A2)+1,FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1)=MID(A2, 4, 2): Наконец, эта функция MID используется для извлечения двух символов, которые начинаются с четвертого символа из ячейки A2.
Разделите третий октет IP-адресов:
Чтобы извлечь третий октет IP-адресов, функции MID и FIND также могут оказать вам услугу, введите или скопируйте следующую формулу в пустую ячейку:
=MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1))Затем перетащите дескриптор заполнения вниз, чтобы получить нужные результаты, и только третий октет IP-адресов был разделен, см. Снимок экрана:
Пояснение к формуле:
1. FIND(".",A2,FIND(".",A2)+1)+1: Эта вложенная функция FIND используется для получения позиции второй точки в ячейке A2, добавляя 1 означает извлечение из следующего символа, и вы получите число 7. Эта часть распознается как аргумент start_num функции MID.
2. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1): Эта часть формулы распознается как аргумент num_chars функции MID.
3.MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-(FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 7, 1): Эта функция MID получит 1 символ, который начинается с седьмого символа из ячейки A2.
Разделите четвертый октет IP-адресов:
Приведенная ниже формула может помочь извлечь последний октет IP-адреса, скопируйте или введите формулу в пустую ячейку:
=MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))Затем перетащите маркер заполнения, чтобы заполнить эту формулу в другие нужные ячейки, вы получите результат, как показано ниже:
Пояснение к формуле:
1. FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1: Эта вложенная функция FIND используется для получения позиции третьей точки в ячейке A2, добавляя 1 означает извлечение из следующего символа, и вы получите число 9. Эта часть распознается как аргумент start_num функции MID.
2. LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1):
- LEN (A2) : Функция ДЛСТР возвращает общее количество символов в ячейке A2. Он получит номер 11.
- LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)=11-8 : Из общей длины ячейки A2 вычитается позиция третьей точки, чтобы получить номер оставшегося символа. Это получит номер 3.
3. MID(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)+1,LEN(A2)-FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1))=MID(A2, 9, 3): Наконец, эта функция MID получит 3 символа, которые начинаются с девятого символа из ячейки A2.
Используемые относительные функции:
- LEFT :
- Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.
- MID :
- Функция MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.
- FIND :
- Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой.
- LEN :
- Функция LEN возвращает количество символов в текстовой строке.
Другие статьи:
- Разделить текст и числа в ячейке в Excel
- Предположим, что данные ячейки смешаны с текстом и числами, как вы можете разделить их на отдельные ячейки столбца? Этот туториал покажет вам подробные шаги, чтобы разобраться с формулами.
- Разделить размеры на две части в Excel
- В этой статье объясняется, как использовать формулу для разделения размеров в ячейке на две части без единиц измерения (индивидуальная длина и ширина).
- Разделение размеров на отдельные длины, высоту и ширину
- В этой статье объясняется, как разделить размеры в ячейке на три части (отдельные измерения, которые включают длину, высоту и ширину).
- Разделить текстовую строку на определенный символ в ячейке в Excel
- В этом руководстве объясняется, как разделить строку текста по определенному символу на отдельные ячейки столбца с формулами в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
В настоящее время я работаю с большим списком IP-адресов (их тысячи).
Однако, когда я сортирую столбец, содержащий IP-адреса, они не сортируются интуитивно понятным или легким для понимания способом.
Например, если я введу IP-адреса следующим образом:
И тогда, если я сортирую в порядке возрастания, я получаю это:
Можно ли отформатировать ячейки так, чтобы, например, IP-адрес 17.255.253.65 появлялся после 1.128.96.254 и до 103.236.162.56 при сортировке в порядке возрастания?
Если нет, есть ли другой способ для меня достичь этой конечной цели?
Как вы, возможно, поняли, ваши IP-адреса рассматриваются как текст, а не как цифры. Они сортируются как текстовые, что означает, что адреса, начинающиеся с "162", будут предшествовать адресам, начинающимся с "20". (потому что символ "1" предшествует символу "2".
Если ваши IP-адреса находятся в столбцах A, добавьте столбцы BE, как показано ниже.
в ячейке B2 и скопируйте его в столбцы BE во всех строках, чтобы получить четыре части каждого IP-адреса. Теперь рассортируйте весь диапазон по столбцам от B до E (в указанном порядке), как показано ниже:
Если вы не хотите видеть вспомогательные столбцы (BE), вы можете их скрыть.
Самое простое, трехэтапное решение, которое я могу предложить вам .
Выберите столбец IP-адрес, примените команду « Текст к столбцу» .
В соседней колонке напишите эту формулу
= СЦЕПИТЬ (В3, "", С3, "", D3, "", Е3)
Наконец сортировка в порядке возрастания.
Проверьте снимок экрана.
NB:
Красный - это оригинальный IP-адрес (в столбце А).
Зеленый после применения текста к столбцу (столбец от B до E).
После нанесения черного цвета происходит конкатенация и сортировка (столбец F).
Причина заключается в том, что изначально IP-адрес очень прост: текстовые данные, и Excel не принимает формат ячейки, чтобы превратить его в номер.
Надеюсь, это поможет вам.
Простой пример:
Вы можете отсортировать по столбцу «Сортируемый» и скрыть его.
Вот ответ, который займет только 1 столбец вашей таблицы и преобразует адрес IPv4 в нумерацию с основанием 10.
Поскольку вы помещаете свои данные в столбец "M", это начинается в ячейке M2 (метка M1). Инкапсуляция в виде кода дает один ужасный беспорядок, поэтому я использовал blockquote:
Не совсем легко читаемая формула, но вы можете просто скопировать и вставить в свою ячейку (предпочтительно N2 или что-то еще в той же строке, что и ваш первый IP-адрес). Это предполагает правильное форматирование IP-адреса, так как исправление ошибок в формуле сделает его еще хуже при разборе человеком.
У меня есть довольно большая (более 200 строк) таблица Excel, в которой перечислены элементы в моей сети (например, принтеры, серверы, рабочие станции, сетевые проекторы и т. д.), а одним из первых столбцов является поле IP-адрес, которое принимает form 192.168.x.y . Моя проблема при попытке сортировки заключается в том, что она идет (как пример) из 192.168.0.85 в 192.168.0.9 . То, что я хотел бы видеть, это сортировка на основе первых 3 октетов, затем последний октет логически (т.е. .1 , .2 , .3 и т. Д.). Это возможно? Если да, то как?
3 ответа
Как упоминается в комментариях nixda, вспомогательные столбцы сделают это возможным. После этого у вас есть два варианта поддержки листа:
- Добавить все новые IP-адреса в столбцах разделения.
- Повторите процедуру «Текст-столбцы» для новых дополнений.
Выберите свой столбец IP и нажмите Data > Text-to-Columns
Сохраните все столбцы, сохраните их как Общие, щелкните значок диапазона, чтобы отредактировать раздел Destination .
Выберите столбцы, в которых должен появиться новый текст. Нажмите клавишу Enter.
Убедитесь, что ваш диапазон выбран и нажмите Data > Sort . Введите критерии сортировки. Продолжайте добавлять уровни для каждого октета.
Вот конечный результат:
Я понимаю, что это старый пост, но в интересах предоставления рабочего решения я представляю следующее.
Просто поместите эту формулу в соседнюю ячейку и обновите ссылки, чтобы указать на ячейку, содержащую ваш IP-адрес (A1 в этом примере). Это даст результат, аналогичный 010.121.008.030, который затем можно отсортировать (правильно) в алфавитном порядке. Затем установите ширину нового столбца в ноль и вуаля. Время, чтобы насладиться чашечкой кофе.
Читайте также: