lordofremixes
Apr 09, 2013, updated at 13:43:52 (UTC)
view11222
view19
0
Goto Top

Excel 2010 - Komplizierte bedingte Formatierung anwenden

GermanQuestionMicrosoft OfficeMicrosoft

Hallo zusammen,

kurze Vorgeschichte. Ich habe in Excel 2 Spalten (Kontonummer, BLZ) mit tausenden von Daten.
Ich habe jetzt gesehen, dass in manchen Werte statt einer Ziffer 0 z.B. der Buchstabe O eingetragen wurde.

Hallo,

ich habe in Excel 2 Spalten (Kontonummer, BLZ) mit tausenden von Daten untereinander stehen.
Ich habe jetzt gesehen, dass in manchen Werte statt einer Ziffer 0 z.B. der Buchstabe O eingetragen wurde. Oder statt eienr 1 ein I eingetragen wurde.
Kann ich irgendwie, eventuell mit der bedingten Formatierung, alle Zellen rot markieren, die einen oder mehrere Buchstaben enthalten (also alles außer Zahlen)?

Beispiel:
BLZ
62040000
64240000
6587777p
6o782345

Die 3. und 4. sollten dann rot farbig hinterlegt sein.

Kann mir bitte jemand sagen, wie ich das hinbekomme`?

Vielen Dank!!

Gruß
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 204709

Url: https://administrator.de/contentid/204709

Printed on: April 26, 2024 at 00:04 o'clock

19 Comments
Latest comment
Goto Top
Member: colinardo
colinardo Apr 09, 2013 updated at 14:16:50 (UTC)
Goto Top
Hi lordofremixes,

Das könntest du schnell mit VBA machen. ALT-F11 drücken, Code in die entsprechende Tabelle einfügen, den Zell-Bereich noch in Zeile 7 des Codes an deine Gegebenheiten anpassen. Cursor in der Prozedur bewegen und F5 drücken.

Code für inkorrekte deutsche BLZ mit 8 Stellen:
Sub SearchInvalidBLZ()
    Dim myRegExp
    Dim cell As Range
    Set myRegExp = CreateObject("vbscript.regexp")  
    myRegExp.Pattern = "^\d{8}$"  
    myRegExp.IgnoreCase = True
    For Each cell In Range("A2:A10000").Cells  
        If Not myRegExp.Test(cell.Value) Then
            cell.Interior.Color = RGB(255, 0, 0)
        End If
    Next
End Sub

Grüße Uwe
Member: lordofremixes
lordofremixes Apr 09, 2013 at 14:05:07 (UTC)
Goto Top
Hallo Uwe!

Kannst du mir eventuell noch sagen, wie ich Zelle 5 anpassen muss, damit er nach alles Buchstaben sucht?

Gruß
Member: Ravers
Ravers Apr 09, 2013 at 14:05:22 (UTC)
Goto Top
Moin,

willst du die Zahlen in Rot haben oder nur herausfinden welche falsch sind?
Da sollte die Sortierfunktion ausreichen.
Oder, da vermutlich nur I und O`s vorkommen (alter Hase hat vermutlich das Sheet gemacht, Vatti machte es auch immer so face-wink )
Suchen und Ersetzen - wunderbare Funktion.

Natürlich auch mit VBA machbar ;)
Member: lordofremixes
lordofremixes Apr 09, 2013 at 14:09:00 (UTC)
Goto Top
Zahlen sollen in rot sein (Korrekturen / Abgleich)
Sortierfunktion bringt nichts, da alle Buchstaben a,b,c,, x,y,z, in jeder Position der Zelle stehen kann.
Shit, von VBA hab ich keine Ahnung.
Member: colinardo
colinardo Apr 09, 2013 updated at 14:14:19 (UTC)
Goto Top
Sorry das war Zeile 7
Einfach den Zellbereich in dem gesucht werden soll angeben, also z.B. mit Range("A1:A5") sucht er in Spalte A von Zeile 1-5. Die ganze Zeile A wird z.B. so ausgewählt: Range("A:A")
For Each cell In Range("A2:A10000").Cells
den Rest erledigt das Script, es markiert alle Zellen die nicht einer Bankleitzahl entsprechen [also nur 8 DIGITS und keine Buchstaben] (Regex in Zeile 5)
Member: lordofremixes
lordofremixes Apr 09, 2013 at 14:37:07 (UTC)
Goto Top
Für die BLZ ist das schon genau die richtige Lösung!
Member: colinardo
colinardo Apr 09, 2013 updated at 15:01:34 (UTC)
Goto Top
Für die Kontonummer ebenfalls leicht umbaubar !!
Ich gehe davon aus das die Kontonummer maximal 10 DIGITS lang ist.
dazu Zeile 5 so abändern, und deinen Zellen-Range in dem die Kontonummern stehen anpassen:

myRegExp.Pattern = "^\d{1,10}$"
Member: lordofremixes
lordofremixes Apr 09, 2013 at 14:59:19 (UTC)
Goto Top
Hallo,

da hat er jetzt alles markiert:

Sub SearchInvalidKontoNr()
Dim myRegExp
Dim cell As Range
Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.Pattern = "^\d{,10}$"
myRegExp.IgnoreCase = True
For Each cell In Range("C2:C10000").Cells
If Not myRegExp.Test(cell.Value) Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next
End Sub
Member: colinardo
colinardo Apr 09, 2013 at 15:02:11 (UTC)
Goto Top
Sorry da hat eine Zahl gefehlt, habe es oben korrigiert...
Member: lordofremixes
lordofremixes Apr 09, 2013 at 15:05:27 (UTC)
Goto Top
Super, danke!!!!!!!!!!!
Member: colinardo
colinardo Apr 09, 2013 updated at 15:26:57 (UTC)
Goto Top
Für alle die doch nur eine Formel mit einer bedingten Formatierung benutzen können bzw. dürfen, hier ist sie für die Prüfung der BLZ auf unkorrekte Zeichen:
In der Formel den Text "A1" ersetzen durch die Zelle in der die erste Bankleitzahl steht
=WENN(UND(CODE(TEIL(A1;1;1))>=48;CODE(TEIL(A1;1;1))<=57);WENN(UND(CODE(TEIL(A1;2;1))>=48;CODE(TEIL(A1;2;1))<=57);WENN(UND(CODE(TEIL(A1;2;1))>=48;CODE(TEIL(A1;2;1))<=57);WENN(UND(CODE(TEIL(A1;3;1))>=48;CODE(TEIL(A1;3;1))<=57);WENN(UND(CODE(TEIL(A1;4;1))>=48;CODE(TEIL(A1;4;1))<=57);WENN(UND(CODE(TEIL(A1;5;1))>=48;CODE(TEIL(A1;5;1))<=57);WENN(UND(CODE(TEIL(A1;6;1))>=48;CODE(TEIL(A1;6;1))<=57);WENN(UND(CODE(TEIL(A1;7;1))>=48;CODE(TEIL(A1;7;1))<=57);WENN(UND(CODE(TEIL(A1;8;1))>=48;CODE(TEIL(A1;8;1))<=57);FALSCH;WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR);WAHR)
Für bedingte Formatierungen gibt die Formel bei fehlerhafter BLZ WAHR zurück ansonsten FALSCH
(ich krieg gleich nen Klammeraffen)face-wink
Grüße Uwe
Member: Biber
Biber Apr 09, 2013 at 15:57:16 (UTC)
Goto Top
Min alle,

von Excel habe ich ja nur Grundkenntnisse. Und VBA mit RegEx kenne ich nur aus dem PC-WELT-Sonntagsrätsel.
Aber würde es für diese Lollipop-Anforderung nicht vollkommen reichen, die Bankleitzahl und die Kontonummer so wie vor der Erweiterung der maximalen Formellänge auf 1023 Zeichen mit "=ISTZAHL(A1)" zu prüfen?

Vielleicht bin ich da zu altmodisch...

Grüße
Biber
Member: colinardo
colinardo Apr 09, 2013 at 17:40:38 (UTC)
Goto Top
@Biber
stimmt, da sieht man vor lauter Birnen den Baum nicht mehr face-wink dann muss er aber die Zellen mit einem benutzerdefinierten Format formatieren, denn Kontonummern können auch führende Nullen haben. Denn wenn die Zelle als Text formatiert ist funktioniert deine Formel nicht.

G. Uwe
Member: Biber
Biber Apr 09, 2013 updated at 19:24:49 (UTC)
Goto Top
Moin @colinardo,

Denn wenn die Zelle als Text formatiert ist funktioniert deine Formel nicht
Da geb ich dir recht - habe es gerade probiert.
Eine (Kontonummer) "00000815" in Zelle A1 als Text formatiert liefert bei =ISTZAHL(A1) tatsächlich FALSCH.

Aber auch da kann ich mir helfen. Ich ändere die Formel auf =ISTZAHL(A1*1) und dann passt es wieder.

Und =ISTZAHL(zelle*1) passt sowohl bei BLZ wie Kontonummern.

Grüße
Biber
[Edit] P.S. Und wenn die Zusatzbedingung bei BLZ und/oder Kontonummern eine exakte Länge, z.B. 8 Zeichen sein muss, dann eben ergänzen auf
=ISTZAHL(zelle*1)*Länge(Glätten(zelle))=8

...dann bliebe nur die Lücke, das ein gehässiger User ein Plus/Minuszeichen mit eingeben hat oder eine Kontonummer "22222^22" oder ähnlich Gemeines.
[/Edit]
Member: lordofremixes
lordofremixes Apr 09, 2013 at 18:39:57 (UTC)
Goto Top
Hallo Biber,

das hatte ich auch erst, kam mir aber irgendwie zu einfach vor..

In meinem Fall wäre dass dann wohl, =ISTZAHL($A1), aber ich kann dir eins dazu sagen (also zumindest in unserem Fall):
Die Formel scheint bei 2000 Datensätze einen "an der Waffel" zu haben, es gibt Datensätze, die zu 100 % korrekt sind und markiert werden, und widerum Falsche, werden ganz normal markiert.
Wollt ich nur mal kurz loswerden...
Member: lordofremixes
lordofremixes Apr 09, 2013 at 18:42:14 (UTC)
Goto Top
wobei ich
=ISTZAHL($A1*1)*Länge(Glätten(zelle))=8

noch nicht ausprobiert habe face-sad
Member: Biber
Biber Apr 09, 2013 updated at 19:24:08 (UTC)
Goto Top
Moin lordofremixes,

na ja, ich gebe zu, ich würde auch (wenn es mein Excel-Sheet wäre) natürlich eine wasserdichte Prüfung einbauen (also meinetwegen genau 8 Stellen lang und nur aus Ziffern bestehend).

@colinardos Formel von heute, 09.04.2013 um 17:11 Uhr kann ich bei meinem ollen Excel 2002 nicht nehmen, da sagt dat Dingen mir was von "zu großer Verschachtelungstiefe".

Aber auch dafür habe ich einen Workaround.

Wenn ich alle Zellen per "Bedingter Formatierung" in "Grün" markieren wollte, die eine Länge von 8 Zeichen nur aus Ziffern bestehend haben, dann würde ich den Bereich der BLZ/Kontonummern markieren (Beispiel: von $A$2:$A4000) und als Formel eingeben:
=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8
... und als Muster "Grün" wählen
Oder alternativ
=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))<>8
... und als Muster "Rot" wählen

Falls nicht über "Bedingte Formatierung", sondern in einer separaten Spalte als WAHR/FALSCH angezeigt werden soll, dann muss es als Matrixformel eingegeben werden.

Heispiel: in Hilfsspalte "Z" in Zelle "Z2" eingeben:
=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8
... mit Strg-Shift-Enter bestätigen und anch unten kopieren.

Wenn es richtig ist, wird die Formel angezeigt als
{=SUMME((CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))>=48)*(CODE(TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1))<=57))=8}


Falls ihr eine kürzere und ebenso wasserdichte Formel-Prüfung habt, bin ich für Ideen offen. face-wink

Grüße
Biber
[Edit]
P.S. Habe inzwischen selbst eine kürzere Matrixformel gefunden für die o.g. Prüfung:
=SUMME(--ISTZAHL(1*TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1)))=8
bzw. für "--"-Meider
=SUMME(1*ISTZAHL(1*TEIL(A2&"MfGBiber";ZEILE(A$1:$A$8);1)))=8

Wieder mit Strg-Shift-Enter eingeben (wenn nicht als "Bedingte Formatierung" verwendet).

Irgendwann muss ich mal mit diesem Excel-zeugs auseinandersetzen, glaube ich.

[/Edit]
Member: lordofremixes
lordofremixes Apr 09, 2013 at 19:38:11 (UTC)
Goto Top
Hallo Biber,
die Lösungen werden ich morgen alle ausprobieren (habe leider die Datei grad nicht zur Hand) und ich finde die Lösungen auch höchst informativ ^^

Fakt ist aber auch, (ist mir leider erst jetzt eingefallen) , BLZ in D ok, 8 stellig. aber international? und Kontonummer, gibt es da wegen der Normierung über Regeln?
Ich klär das morgen früh gleich ab und dann probiere ich alles aus.
Die Lösungen sind aber auf den ersten Blick echt spitze.

Danke schonmal
Member: Biber
Biber Apr 12, 2013 at 13:48:37 (UTC)
Goto Top
Moin lordofremixes,

ich weiss nicht, ob du dich noch erinnerst... neulich trafen sich hier zu deinem Problem ein paar hilfsbereite User.
Das kurze Brainstorming brach vor drei Tagen ab, als einer der Plauderer schrieb "..die Lösungen werden ich morgen alle ausprobieren... .

Seitdem ist es hier ziemlich ruhig.
Gibt es denn noch Bewegung?

Grüße
Biber
GermanQuestionMicrosoft OfficeMicrosoft
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 Comments