Перейти до основного матеріалу

Як швидко відсортувати IP-адресу від низької до високої в Excel?

Загалом, ми використовуємо функцію сортування для сортування рядка в Excel. Але якщо для сортування потрібні деякі IP-адреси, порядок сортування може бути неправильним за допомогою функції Сортування безпосередньо, як показано на знімку екрана нижче. Тепер у мене є кілька способів швидко та правильно сортувати IP-адреси в Excel.

Неправильно Сортувати за функцією сортування Правильне сортування
doc сортування ip 1 doc сортування ip 2

Сортувати IP-адресу за формулою

Сортувати IP-адресу за VBA

Сортувати IP-адресу за текстом у стовпці


Сортувати IP-адресу за формулою

Використовуйте формулу для заповнення IP-адреси, а потім сортуйте.

1. Виберіть комірку, сусідню з IP-адресою, і введіть цю формулу

=TEXT(LEFT(A1,FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND( ".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1, FIND(".",A1,FIND(".",A1,1)+1)+1)-FIND(".",A1,FIND(".",A1,1)+1)-1), "000") & "." & TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND( ".",A1,1)+1)+1)),"000")

press Що натомість? Створіть віртуальну версію себе у клавішу та перетягніть маркер заповнення по клітинках, щоб застосувати цю формулу.
doc сортування ip 3

2. Скопіюйте результати формули та вставте їх як значення у наступний стовпець. Дивіться знімок екрана:

doc сортування ip 4
doc сортування ip 5

3. залиште вставлене значення вибраним і натисніть дані > Сортувати від А до Я.
doc сортування ip 6

4 В Сортувати Уорінг діалогове вікно, зберегти Розгорніть вибір перевірено
doc сортування ip 7

5. клацання сортувати. Тепер IP-адреси відсортовані від низької до високої.
doc сортування ip 2

Ви можете видалити допоміжні стовпці.


Сортувати IP-адресу за VBA

Ось код VBA, який також може вам допомогти.

1. прес Alt + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.

2. клацання Insert > Модулі, скопіюйте та вставте код у порожній сценарій.

VBA: Заповніть IP-адресу

Sub FormatIP()
'UpdatbyExtendoffice20171215
    Dim xReg As New RegExp
    Dim xMatches As MatchCollection
    Dim xMatch As Match
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim xArr() As String
    On Error Resume Next
    Set xRg = Application.InputBox("Select cells:", "KuTools For Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    With xReg
        .Global = True
        .Pattern = "\d{1,3}\.+\d{1,3}\.+\d{1,3}\.+\d{1,3}"
        For Each xCell In xRg
            Set xMatches = .Execute(xCell.Value)
            If xMatches.Count = 0 Then GoTo xBreak
            For Each xMatch In xMatches
                xArr = Split(xMatch, ".")
                For I = 0 To UBound(xArr)
                    xArr(I) = Right("000" & xArr(I), 3)
                    If I <> UBound(xArr) Then
                        xArr(I) = xArr(I) & "."
                    End If
                Next
            Next
            xCell.Value = Join(xArr, "")
xBreak:
        Next
    End With
End Sub

doc сортування ip 8

3 Потім натисніть Tools > Посилання, і перевірити Регулярні вирази Microsoft VBScript 5.5 у діалоговому вікні.

doc сортування ip 9
doc сортування ip 10

4. клацання OK і натисніть F5 клавіша, вискакує діалогове вікно, щоб нагадати вам вибрати діапазон для роботи.
doc сортування ip 11

5. клацання OK. Тоді IP-адреси були заповнені нулем.

6. Виберіть IP-адреси та натисніть дані > Сортувати від А до Я щоб їх сортувати.


Сортувати IP-адресу за текстом у стовпці

Насправді функція «Текст у стовпці» може зробити вам користь і в Excel.

1. Виділіть клітинки, якими ви користуєтеся, та клацніть дані > Текст у стовпці. Дивіться знімок екрана:
doc сортування ip 12

2 В Перетворення макета тексту в колонки діалогове вікно, виконайте вказані нижче дії

перевірити Розмежовано, і клацніть МАЙБУТНІ;

перевірити Інше і тип . у текстове поле та натисніть МАЙБУТНІ;

Виберіть комірку поруч з IP-адресою, щоб розмістити результат. Клацніть обробка.

doc сортування ip 13
doc сортування ip 14
doc сортування ip 15

3. Виділіть усі комірки, що містять IP-адреси та розділені комірки, та натисніть дані > сортувати.
doc сортування ip 16

4 В сортувати діалогове вікно, клацнувши Додати рівень для сортування даних зі стовпця B до E (розділені комірки). Дивіться знімок екрана:
doc сортування ip 17

5. клацання OK. Тепер стовпці відсортовані.
doc сортування ip 18


Найкращі інструменти продуктивності офісу

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Traducido al español:
=TEXTO(IZQUIERDA(A1,ENCONTRAR(".",A1,1)-1),"000") & "." & TEXTO(EXTRAE(A1,ENCONTRAR( ".",A1,1)+1,ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)-ENCONTRAR(".",A1,1)-1),"000") & "." & TEXTO(EXTRAE(A1,ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)+1,ENCONTRAR(".",A1, ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)+1)-ENCONTRAR(".",A1,ENCONTRAR(".",A1,1)+1)-1), "000") & "." & TEXTO(DERECHA(A1,LARGO(A1)-ENCONTRAR(".",A1,ENCONTRAR(".",A1,ENCONTRAR( ".",A1,1)+1)+1)),"000")
This comment was minimized by the moderator on the site
Thanks for your help on translating the formula.
This comment was minimized by the moderator on the site
This is great! Very much appreciated. It would be nice to add to the VB code to reverse (put back) the IP's in the original format (without the extra leading 0's) after having sorted them. :) I would be very interested in that where you can run this script, sort results, then revert back to original format.
This comment was minimized by the moderator on the site
thank you, very useful !
This comment was minimized by the moderator on the site
la formule traduite en Francais :
=TEXTE(GAUCHE(I6;TROUVE(".";I6;1)-1);"000") & "." & TEXTE(STXT(I6;TROUVE( ".";I6;1)+1;TROUVE(".";I6;TROUVE(".";I6;1)+1)-TROUVE(".";I6;1)-1);"000") & "." & TEXTE(STXT(I6;TROUVE(".";I6;TROUVE(".";I6;1)+1)+1;TROUVE(".";I6; TROUVE(".";I6;TROUVE(".";I6;1)+1)+1)-TROUVE(".";I6;TROUVE(".";I6;1)+1)-1); "000") & "." & TEXTE(DROITE(I6;NBCAR(I6)-TROUVE(".";I6;TROUVE(".";I6;TROUVE( ".";I6;1)+1)+1));"000")
This comment was minimized by the moderator on the site
Merci pour la traduction !
This comment was minimized by the moderator on the site
Why does the formula not work for the last octet? It adds zeros to the 3rd octet but not the last? So frustrating.
This comment was minimized by the moderator on the site
I have tested the formula before I post it, it can work for the last octer. Have you checked the formula you pasted is correct?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations