richtiges Sortieren einer Spalte mit IP Adressen
Steffen Müller
ich hab ein kleines Problem mit der Sortierfunktion bzw. der Anzeige im
Auto-Filter.
Ich habe eine Spalte mit IP Adressen. Durch die Schreibweise mit dem Punkt
zwischen den Oktetts wird die Excel-Zelle als Text formatiert und es es ist
mir nicht möglich eine vernünftige Lösung zu finden, diese technisch korrekt
zu sortieren.
Ebenfalls möchte ich durch das simple Einschalten des Autofilters
herausbekommen, welche IP noch nicht vergeben ist. Leider stehen auch hier
die IPs ducheinander.
Beispiel:
Spalteneingabe lautet:
10.100.20.1
10.100.20.2
10.100.20.10
10.100.20.7
10.100.20.5
10.100.20.6
10.100.20.8
10.100.20.9
10.100.20.11
10.100.2.5
10.100.2.16
10.100.2.1
10.100.12.10
10.100.12.13
etc.
sortiert wird:
10.100.12.10
10.100.12.13
10.100.2.1
10.100.2.16
10.100.2.5
10.100.20.1
10.100.20.10
10.100.20.11
10.100.20.2
10.100.20.5
10.100.20.6
10.100.20.7
10.100.20.8
10.100.20.9
Gibt es hierfür eine Lösung?
Ich währe sehr dankbar.
Gruß
Steffen Müller
Dimo Tabken
> Ich habe eine Spalte mit IP Adressen. Durch die Schreibweise mit dem Punkt
> zwischen den Oktetts wird die Excel-Zelle als Text formatiert und es es ist
> mir nicht möglich eine vernünftige Lösung zu finden, diese technisch korrekt
> zu sortieren.
Hi steffen,
Ich würde mir in die Tabelle 4 Blindspalten einbauen, die ggf. auch
ausgeblendet werden können (für jedes Oktett eine).
in der Mappe in ein VBA-Modul die folgende kleine Funktion:
Public Function SplitString(ByVal strText As String, strDelimiter As
String, intEbene As Integer)
Dim arrStrParts
Dim strTemp As String
intEbene = intEbene - 1
arrStrParts = Split(strText, strDelimiter)
If UBound(arrStrParts) < intEbene Then
strTemp = "--"
Else
strTemp = arrStrParts(intEbene)
End If
SplitString = strTemp
End Function
In den Blindspalten kannst Du dann mittels
=splitstring(ZelleInDerDieIPsteht;".";OktettNr.)
die IP zunächst mal zerlegen und dann kannst Du problemlos über die ersten
drei Spalten sortieren ...
Bißchen umständlich, aber eine andere Lösung fällt mir momentan nicht ein
Ich Hab es für exakt die gleiche Anforderung, die Du geschildert hast, im
Rahmen einer Serverauswertung auch mal so gemacht. Hat prima funktioniert.
--
Gruß, Dimo
'Gott ist tot!' -> Nietzsche, 1887
'Nietzsche ist tot!' -> Gott, 1900
Steffen Müller
Hoffentlich ist das nicht schon eine Nummer zu groß für mich. Werde es über
das Wochenende ausprobieren.
Mich wundert nur ein wenig, das MS nicht unter Zelle formatieren ->
Sonderformat eine Eingabe für IPs anbietet. In der heutigen Zeit...;-)
Gruß
Steffen
Thomas Ramel
Steffen Müller schrieb am 24.07.2008
> Mich wundert nur ein wenig, das MS nicht unter Zelle formatieren ->
> Sonderformat eine Eingabe für IPs anbietet. In der heutigen Zeit...;-)
Ganz so einfach ist es denn doch nicht - wie genau willst Du eine
Systematik in die Ganze Sache reinbringen?
Generell kannst Du das folgende Benuzterdefinierte Zellenformat verwenden:
000"."000"."000"."000
Allerdings, und hier ist dann die Systematik am Ende, werden IP-Adressen
leider nicht konsequent mit 3 Stellen pro Oktett geschrieben, womit eine
systematische Auswertung nicht mehr möglich ist.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-3]
Microsoft Excel - Die ExpertenTipps
Thomas Ramel
Steffen Müller schrieb am 24.07.2008
> Danke Dir für einen ersten Lösungsansatz.
> Hoffentlich ist das nicht schon eine Nummer zu groß für mich. Werde es über
> das Wochenende ausprobieren.
Alternativ kannst Du das Ganze natürlich auch mit Formeln in einzelne
Spalten aufteilen. Wenn deine Daten In A1 und folgende stehen, dann z.B.
die folgenden Formeln in der ersten Zeile und nach unten kopieren:
B1:
=--LINKS(A1;FINDEN(".";A1)-1)
C1:
=--LINKS(WECHSELN(A1;B1&".";"");FINDEN(".";WECHSELN(A1;B1&".";""))-1)
D1:
=--LINKS(WECHSELN(A1;B1&"."&C1&".";"");FINDEN(".";WECHSELN(A1;B1&"."&C1&".";""))-1)
E1:
=--WECHSELN(A1;B1&"."&C1&"."&D1&".";"")
Thomas Ramel
Thomas Ramel schrieb am 24.07.2008
> Steffen Müller schrieb am 24.07.2008
>
>> Danke Dir für einen ersten Lösungsansatz.
>> Hoffentlich ist das nicht schon eine Nummer zu groß für mich. Werde es über
>> das Wochenende ausprobieren.
>
> Alternativ kannst Du das Ganze natürlich auch mit Formeln in einzelne
> Spalten aufteilen. Wenn deine Daten In A1 und folgende stehen, dann z.B.
> die folgenden Formeln in der ersten Zeile und nach unten kopieren:
>
> B1:
> =--LINKS(A1;FINDEN(".";A1)-1)
>
> C1:
> =--LINKS(WECHSELN(A1;B1&".";"");FINDEN(".";WECHSELN(A1;B1&".";""))-1)
>
> D1:
> =--LINKS(WECHSELN(A1;B1&"."&C1&".";"");FINDEN(".";WECHSELN(A1;B1&"."&C1&".";""))-1)
>
> E1:
> =--WECHSELN(A1;B1&"."&C1&"."&D1&".";"")
Hängst Du dann in F1 noch die folgende Formel an, kannst Du nach dieser
Spalte sauber sortieren:
=TEXT(B1;"000.")&TEXT(C1;"000.")&TEXT(D1;"000.")&TEXT(E1;"000")
Peter Schleif
>
> Ich habe eine Spalte mit IP Adressen. Durch die Schreibweise mit dem Punkt
> zwischen den Oktetts wird die Excel-Zelle als Text formatiert und es es ist
> mir nicht möglich eine vernünftige Lösung zu finden, diese technisch korrekt
> zu sortieren.
Mit dieser VBA-Prozedur bekommst Du Spalte A sortiert (derzeit ohne
Kopfzeile). Falls Nachbar-Spalten mitsortiert werden sollen, müsste
man den Bereich nach rechts erweitern.
Peter
Sub ip_sort()
Dim z As Long
Dim item As Variant
Application.ScreenUpdating = False
Columns(1).Insert
For z = 1 To Cells(Rows.Count, "B").End(xlUp).Row
For Each item In Split(Trim(Cells(z, "B").Value), ".")
Cells(z, "A") = Cells(z, "A") * 256 + CInt(item)
Next
Next
Range([A1], Cells(z - 1, "B")).Sort Key1:=[A1], Header:=xlNo
Columns(1).Delete
Application.ScreenUpdating = True
End Sub
Steffen Müller
"Thomas Ramel" <t.r...@MVPs.org> schrieb im Newsbeitrag
news:1rzssxzdadr7l.14qkejw2uju6g$.dlg@40tude.net...
> Ganz so einfach ist es denn doch nicht - wie genau willst Du eine
> Systematik in die Ganze Sache reinbringen?
Ziel ist es, aus der Spalte die letzte vergebene IP schnell zu ermitteln.
Wobei insgesamt sieben verschiedene Netzwerke in einer Spalte stehen. Diese
Netzwerke unterscheiden sich aber lediglich im 3. und natürlich im 4.
Oktett.
Sodaß ich eigentlich nur diese "sortieren" müsste.
Wie gesagt, ich werde am WE an euren Lösungsansätze rumtüffteln.
Gruß
Steffen
Thomas Ramel
Steffen Müller schrieb am 24.07.2008
> "Thomas Ramel" <t.r...@MVPs.org> schrieb im Newsbeitrag
>
>> Ganz so einfach ist es denn doch nicht - wie genau willst Du eine
>> Systematik in die Ganze Sache reinbringen?
>
> Ziel ist es, aus der Spalte die letzte vergebene IP schnell zu ermitteln.
> Wobei insgesamt sieben verschiedene Netzwerke in einer Spalte stehen. Diese
> Netzwerke unterscheiden sich aber lediglich im 3. und natürlich im 4.
> Oktett.
> Sodaß ich eigentlich nur diese "sortieren" müsste.
Sorry, wenn ich mich missverständlich ausgedrückt habe - *deine* Systematik
ist IMO durchaus klar.
Ich bezog mich dabei auf die nicht einheitliche Schreibweise der IP's.
Peter Schleif
>
> Ziel ist es, aus der Spalte die letzte vergebene IP schnell zu ermitteln.
=LetzteIP(A:A) oder =LetzteIP(A1)
Function LetzteIP(r As Range) As String
Dim z As Long
Dim arr As Variant
Dim max_ip As Double
For z = 1 To Cells(Rows.Count, r.Column).End(xlUp).Row
arr = Split(Trim(Cells(z, r.Column).Value), ".")
If UBound(arr) = 3 Then
If max_ip<((arr(0)*256+arr(1))*256+arr(2))*256+arr(3) Then
max_ip=((arr(0)*256+arr(1))*256+arr(2))*256+arr(3)
LetzteIP = Trim(Cells(z, r.Column).Value)
End If
End If
Next
End Function
Dimo Tabken
> Ziel ist es, aus der Spalte die letzte vergebene IP schnell zu ermitteln.
Moinmoin Steffen,
wenn Du den Ansatz mit den Blindspalten weiter verfolgst (siehe mein erstes
Posting), dann kannst Du in den Blindspalten mit der Funktion max() bzw.
dbmax() weiter machen ...(Die Splitfunktion habe ich nochmal ein bißchen
erweitert)
Hilfsspalte 1.Oktett: einfach mit max() den höchsten Wert raussuchen
Hilfsspalten 2.-4.Oktett: mit dbmax() den höchsten Wert aus der Spalte
raussuchen, Suchkriterium ist das Ergebnis der max()- bzw. dbmax()-Funktion
des vorangegangenen Oktetts.
Ich hab mal eine Beispieldatei gebaut, die kannst Du Dir ja mal ansehen:
Als kleine Spielerei wird da die höchste IP-Adresse per Bedingter
Formatierung in der Gesamtliste hervorgehoben.
http://82.135.63.250/public/iplistesortieren/ipliste.xls
Alexander Wolff
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Steffen Müller
"Alexander Wolff" <oo...@gmx.de> schrieb im Newsbeitrag
news:g6ctth$1r3$00$1...@news.t-online.com...
> Wandle die IP's: www.xxcl.de/0046.htm
Ganz stark ;-))
Funzt auch soweit. Mußte allerdings die Funktion
=SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A1;".";":::::::::");ZEILE(1:4)*10-9;10);":";)*10^(3*(4-ZEILE($1:$4))))
um ZEILE(1:4) in ZEILE($1:$4) ersetzen, sonst kann man die Formel nicht
durchkopieren.
Ein Problem bleibt noch, wenn eine Zeile gelöscht wird. Dann sieht die
Formel plötzlich so aus
=SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A7;".";":::::::::");ZEILE($1:$3)*10-9;10);":";)*10^(3*(4-ZEILE($1:$3))))
Beachte ZEILE($1:$3)
Ergibt natürlich Unsinn. Kann man das verhindern?
Gruß
Steffen
Wolfgang Habernoll
"Steffen Müller" <sm6...@gmail.com> schrieb im Newsbeitrag
news:g6f061$p8t$01$1...@news.t-online.com...
>
> "Alexander Wolff" <oo...@gmx.de> schrieb im Newsbeitrag
> news:g6ctth$1r3$00$1...@news.t-online.com...
>> Wandle die IP's: www.xxcl.de/0046.htm
>
> Ganz stark ;-))
ja, das stimmt.
> Funzt auch soweit. Mußte allerdings die Funktion
> =SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A1;".";":::::::::");ZEILE(1:4)*10-9;10);":";)*10^(3*(4-ZEILE($1:$4))))
> um ZEILE(1:4) in ZEILE($1:$4) ersetzen, sonst kann man die Formel nicht
> durchkopieren.
>
> Ein Problem bleibt noch, wenn eine Zeile gelöscht wird. Dann sieht die
aber nur wenn du eine der ersten 4-Zeilen löscht oder?
> Formel plötzlich so aus
> =SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A7;".";":::::::::");ZEILE($1:$3)*10-9;10);":";)*10^(3*(4-ZEILE($1:$3))))
> Beachte ZEILE($1:$3)
> Ergibt natürlich Unsinn. Kann man das verhindern?
beginne mit deiner Liste ab Zeile 5 und lösche nie eine der ersten 4-Zeilen oder,
Alexanders Einverständnis vorausgesetzt, ändere die Formel wie folgt
=SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A1;".";":::::::::");{1;2;3;4}*10-9;10);":";)*10^(3*(4-{1;2;3;4})))
--
mfG
Wolfgang Habernoll
[ Win XP Home SP-2 , XL2002 ]
Bernd P
Viele Gruesse,
Bernd
Alexander Wolff
> http://www.geocities.com/davemcritchie/excel/sorttcp.htm
Hi Bernd,
http://www.mvps.org/dmcritchie/excel/sorttcp.htm als aktuelle Variante
(nicht unbedingt inhaltlich, aber von der Maintenance her)
@Wolfgang (im anderen Thread): {1.2.3.4} ist viel besser als mein ZEILE()!
Steffen Müller
"Wolfgang Habernoll" <wolfgang....@t-online.de> schrieb im Newsbeitrag
news:g6hh24$o0h$02$1...@news.t-online.com...
> beginne mit deiner Liste ab Zeile 5 und lösche nie eine der ersten
> 4-Zeilen oder, Alexanders Einverständnis vorausgesetzt, ändere die Formel
> wie folgt
>
> =SUMMENPRODUKT(WECHSELN(TEIL(WECHSELN(A1;".";":::::::::");{1;2;3;4}*10-9;10);":";)*10^(3*(4-{1;2;3;4})))
>
> --
> mfG
> Wolfgang Habernoll
Vielen Vielen Dank an Alle Mitwirkende,
Ihr hab mir richtig weitergeholfen.
Gruß
Steffen