Chuck có một trang tính, trong một cột, chứa một loạt địa chỉ IP. Chúng có định dạng quen thuộc là 192.168.2.1. Nếu anh ta sắp xếp các địa chỉ, chúng không được sắp xếp theo số. Ví dụ: Excel đặt 192.168.1.100 giữa 192.168.1.1 và 192.168.1.2. Chuck tự hỏi liệu có cách nào để sắp xếp một cột địa chỉ IP để chúng xuất hiện theo trình tự thích hợp hay không.

Điều này xảy ra vì Excel xem địa chỉ IP dưới dạng văn bản, không phải dưới dạng số hoặc một chuỗi số. Có một số cách bạn có thể giải quyết vấn đề, một vài trong số đó tôi sẽ thảo luận trong mẹo này. Bạn nên chọn cách tiếp cận phù hợp với nhu cầu của mình, như được xác định bởi dữ liệu của bạn và cách bạn sử dụng dữ liệu đó.

Một cách tiếp cận là đảm bảo rằng mỗi octet địa chỉ IP của bạn bao gồm ba chữ số. (Một octet là từng phần của địa chỉ IP, được phân tách bằng dấu chấm.) Ví dụ: thay vì địa chỉ như 192.168.1.1, bạn sẽ sử dụng 192.168.001.001. “Tấm đệm phía trước” này mỗi octet có số 0 và nếu tất cả địa chỉ IP của bạn ở định dạng này, chúng sẽ sắp xếp chính xác.

Nếu bạn muốn sử dụng một công thức để đảm bảo phần đệm phía trước của mỗi octet, bạn có thể sử dụng như sau:

=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")

Công thức này khá dài, nhưng nó vẫn là một công thức đơn lẻ. Đặt nó vào cột bên cạnh các địa chỉ IP đầu tiên của bạn (giả sử địa chỉ đó nằm trong ô A1) và sau đó sao chép nó xuống nhiều hàng theo yêu cầu. Khi bạn sắp xếp, hãy sắp xếp theo cột B và các địa chỉ sẽ theo trình tự thích hợp.

Nếu bạn làm việc với nhiều địa chỉ IP, bạn có thể muốn tạo một hàm do người dùng xác định sẽ chèn trước mỗi octet của địa chỉ IP bằng các số 0 và sau đó trả về một IP được định dạng đầy đủ. Sau đây sẽ thực hiện nhiệm vụ:

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

Sau đó, trong Excel, bạn có thể sử dụng UDF theo cách này, giả sử địa chỉ IP ban đầu của bạn trong ô A1:

=FormatIP(A1)

Một cách tiếp cận khác là chỉ cần chia địa chỉ IP thành các cột riêng biệt, đặt mỗi octet vào cột riêng của nó. Điều này rất dễ thực hiện nếu bạn sử dụng công cụ Text to Columns, theo cách sau:

  1. Đảm bảo có ba cột trống ở bên phải địa chỉ IP của bạn.

  2. Chọn dải ô chứa địa chỉ IP.

  3. Hiển thị tab Dữ liệu của dải băng.

  4. Bấm vào công cụ Văn bản thành Cột, trong nhóm Công cụ Dữ liệu. Excel khởi động Trình hướng dẫn Chuyển đổi Văn bản thành Cột. (Xem Hình 1.)

  5. Chọn nút radio Được phân cách.

  6. Nhấp vào Tiếp theo.

  7. Chọn hộp kiểm bên cạnh Khác và ở bên phải hộp kiểm, nhập dấu chấm.

  8. Nhấp vào Kết thúc.

Sau khi hoàn tất, bạn có thể sắp xếp bốn cột như cách bạn thường sắp xếp số. Sau đó, khi bạn muốn đặt các địa chỉ IP lại với nhau, bạn có thể sử dụng công thức như sau:

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

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13481) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.