メインコンテンツへスキップ

ExcelでIPアドレスを低いものから高いものにすばやく並べ替える方法は?

通常、Excelで文字列を並べ替えるには、並べ替え関数を使用します。 ただし、並べ替えに必要なIPアドレスがいくつかある場合は、下のスクリーンショットに示すように、並べ替え機能を直接使用すると、並べ替え順序が間違っている可能性があります。 これで、ExcelでIPアドレスをすばやく正しく並べ替える方法がいくつかあります。

ソート機能によるソートが間違っている 正しい並べ替え
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")

入力します キーを押し、塗りつぶしハンドルをセルにドラッグして、この数式を適用します。
doc ソート ip 3

2.数式の結果をコピーして、次の列に値として貼り付けます。 スクリーンショットを参照してください:

doc ソート ip 4
doc ソート ip 5

3.貼り付けた値を選択したままにして、をクリックします 且つ > ZをZにソートする.
doc ソート ip 6

4。 の中に Waringを並べ替える ダイアログ、維持 選択範囲を拡大 チェックしました。
doc ソート ip 7

5。 クリック 並び替え。 これで、IPアドレスが低から高にソートされました。
doc ソート ip 2

ヘルパー列を削除できます。


IPアドレスをVBAで並べ替える

これもあなたを助けることができるVBAコードです。

1。 押す Altキー+ F11 を有効にするためのキー アプリケーション向け Microsoft Visual Basic 窓。

2。 クリック インセット > モジュール、コードをコピーして空白のスクリプトに貼り付けます。

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。 次に、をクリックします 工具 > 参照、チェック MicrosoftVBScript正規表現5.5 ポップダイアログで。

doc ソート ip 9
doc ソート ip 10

4。 クリック OK を押し F5 キーを押すと、作業範囲を選択するよう促すダイアログが表示されます。
doc ソート ip 11

5。 クリック OK。 その後、IP アドレスはゼロで埋められます。

6. IPアドレスを選択し、をクリックします 且つ > ZをZにソートする 並べ替えるために。


IPアドレスをテキストから列に並べ替える

実際、Text to Columns 機能は Excel でも役に立ちます。

1.使用するセルを選択し、をクリックします 且つ > テキストから列へ。 スクリーンショットを参照してください:
doc ソート ip 12

2。 の中に テキストを列ウィザードに変換する ダイアログ、以下のようにします:

チェック 区切り文字付き、クリック Next;

チェック その他 とタイプ . テキストボックスに入力し、 Next;

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


最高のオフィス生産性向上ツール

人気の機能: 重複を検索、強調表示、または識別する   |  空白行を削除する   |  データを失わずに列またはセルを結合する   |   数式なしのラウンド ...
スーパールックアップ: 複数の基準の VLookup    複数の値の VLookup  |   複数のシートにわたる VLookup   |   ファジールックアップ ....
詳細ドロップダウン リスト: ドロップダウンリストを素早く作成する   |  依存関係のドロップダウン リスト   |  複数選択のドロップダウンリスト ....
列マネージャー: 特定の数の列を追加する  |  列の移動  |  Toggle 非表示列の表示ステータス  |  範囲と列の比較 ...
注目の機能: グリッドフォーカス   |  デザインビュー   |   ビッグフォーミュラバー    ワークブックとシートマネージャー   |  リソースライブラリ (自動テキスト)   |  日付ピッカー   |  ワークシートを組み合わせる   |  セルの暗号化/復号化    リストごとにメールを送信する   |  スーパーフィルター   |   特殊フィルター (太字/斜体/取り消し線をフィルター...) ...
上位 15 のツールセット12 テキスト 工具 (テキストを追加, 文字を削除する、...)   |   50+ チャート 種類 (ガントチャート、...)   |   40+ 実用的 (誕生日に基づいて年齢を計算する、...)   |   19 挿入 工具 (QRコードを挿入, パスから画像を挿入、...)   |   12 変換 工具 (数字から言葉へ, 通貨の換算、...)   |   7 マージ&スプリット 工具 (高度な結合行, 分割セル、...)   |   ... もっと

Kutools for Excel で Excel スキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、生産性を向上させ、時間を節約するための300以上の高度な機能を提供します。  最も必要な機能を入手するにはここをクリックしてください...

kteタブ201905


Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 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