مرتب سازی آدرس های IP

Question

Chuck یک کاربرگ دارد که در یک ستون شامل یک سری آدرس IP است. اینها در قالب آشنای 192.168.2.1 هستند. اگر او آدرس ها را مرتب کند، آنها به صورت عددی مرتب نمی شوند. به عنوان مثال، اکسل 192.168.1.100 را بین 192.168.1.1 و 192.168.1.2 قرار می دهد. چاک نمی داند آیا راهی برای مرتب سازی ستون های آدرس های IP وجود دارد تا در ترتیب مناسب ظاهر شوند.

این به این دلیل اتفاق می افتد که اکسل یک آدرس IP را به صورت متن می بیند، نه به عنوان یک عدد یا یک سری اعداد. چند راه برای حل مشکل وجود دارد که در این نکته به چند مورد از آنها اشاره خواهیم کرد. شما باید رویکردی را انتخاب کنید که برای نیازهای شما مناسب است، همانطور که توسط داده های شما و نحوه استفاده از آن داده ها تعریف شده است.

یک روش این است که مطمئن شوید هر اکتت از آدرس های IP شما از سه رقم تشکیل شده است. (یک اکتت هر قسمت از آدرس IP است که با نقطه از هم جدا شده است.) برای مثال، به جای آدرسی مانند 192.168.1.1، از 192.168.001.001 استفاده کنید. این "پدهای جلو" هر اکتت با صفر است و اگر تمام آدرس های 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")

این فرمول بسیار طولانی است، اما هنوز یک فرمول واحد است. آن را در ستون کنار اولین آدرس های IP خود قرار دهید (با فرض اینکه آدرس در سلول A1 باشد) و سپس آن را به تعداد ردیف های مورد نیاز کپی کنید. هنگامی که مرتب سازی خود را انجام می دهید، بر اساس ستون B مرتب کنید و آدرس ها به ترتیب مناسب خواهند بود.

اگر با آدرس های IP زیادی کار می کنید، ممکن است بخواهید یک تابع تعریف شده توسط کاربر ایجاد کنید که هر اکتت آدرس IP را با صفر در جلو قرار می دهد و سپس یک IP کاملا فرمت شده را برمی گرداند. موارد زیر وظیفه را انجام خواهند داد:

Function FormatIP(IPAddr As String) As String
    Dim Dot1 As Integer
    Dim Dot2 As Integer
    Dim Dot3 As Integer
    Dim Octet1 As String
    Dim Octet2 As String
    Dim Octet3 As String
    Dim Octet4 As String

    Dot1 = InStr(1, IPAddr, ".", vbTextCompare)
    Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare)
    Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare)

    Octet1 = Left(IPAddr, Dot1-1)
    Octet2 = Mid(IPAddr, Dot1+1, Dot2-Dot1-1)
    Octet3 = Mid(IPAddr, Dot2+1, Dot3-Dot2-1)
    Octet4 = Mid(IPAddr, Dot3+1, Len(IPAddr))

    FormatIP = Right("000" & Octet1, 3) & "."
    FormatIP = FormatIP & Right("000" & Octet2, 3) & "."
    FormatIP = FormatIP & Right("000" & Octet3, 3) & "."
    FormatIP = FormatIP & Right("000" & Octet4, 3)
End Function

پس در اکسل، با فرض اینکه آدرس IP اصلی خود را در سلول A1 قرار دهید، می توانید از UDF به این شکل استفاده کنید:

=FormatIP(A1)

روش دیگر این است که به سادگی آدرس های IP را به ستون های جداگانه تقسیم کنید و هر اکتت را در ستون خود قرار دهید. اگر از ابزار Text to Columns استفاده می کنید، این کار آسان است، به این ترتیب:

شکل 1. ابتدای جادوگر تبدیل متن به ستون.

1. مطمئن شوید که سه ستون خالی در سمت راست آدرس های IP شما وجود دارد.
2. محدوده سلول های حاوی آدرس های IP را انتخاب کنید.
3. تب Data روبان را نمایش دهید.
4. روی ابزار Text to Columns در گروه Data Tools کلیک کنید. اکسل جادوگر تبدیل متن به ستون را شروع می کند. (شکل 1 را ببینید.)
5. دکمه رادیویی Delimited را انتخاب کنید.
6. روی Next کلیک کنید.
7. چک باکس کنار Other را انتخاب کنید و در سمت راست کادر، یک نقطه را وارد کنید.
8. بر روی Finish کلیک کنید.

پس از اتمام، می توانید چهار ستون را همانطور که معمولاً اعداد را مرتب می کنید مرتب کنید. سپس، هنگامی که می خواهید آدرس های IP را دوباره کنار هم قرار دهید، می توانید از فرمولی مانند زیر استفاده کنید:

=A1 & "." & B1 & "." & C1 & "." & D1