Frage von WarpDiva, 68

Excel Formel: Bestimmte Zahlen markieren?

Guten Morgen zusammen und einen "schönen" Start in die Woche..

Ich muss eine Excel Liste zusammenschustern. Dafür habe ich eine Liste von Kunden mit PLZ. Ferner habe ich einen separaten Sheet mit gewissen PLZ. Nun will ich, dass in der ersten Liste nur solche aufgezeigt werden, welche die PLZ vom anderen Sheet beinhaltet.

Nur habe ich bis jetzt nicht rausgefunden, wie dies mit einer einfachen Formel zu meistern sei... kann mir da jemand aushelfen? Vielen Dank

Ergänzung: Hier der Link zur Tabelle https://1drv.ms/x/s!AtJxCMvA1TDghcZfPKXvwt09DqFK9g

Da ich in der Schweiz wohne, gibt es bei uns lediglich 4 Zahlen in der PLZ. Ich suche PLZ zwischen 3200 und 4900 jedoch nur bestimmte, welche im anderen Sheet ersichtlich sind. 

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Ninombre, Community-Experte für Excel, 26

Irgendwie gab es in den Antworten und Kommentaren wohl einige Missverständnisse

Bedingte Formatierung mit Formel ist der richtige Weg.

Bereich auf dem Tabellenblatt mit Kunden markieren, für den die bedingte Formatierung gelten soll. Als Formel

=ZÄHLENWENN(Tabelle2!A$1:A$100;B1)>0

Tabelle2 ist der Name des Tabellenblatts mit der separaten Liste von PLZ -> entsprechend anpassen in der Formel

A1:A100 wäre der Bereich in dem diese PLZ in der separaten Liste stehen. Auch entsprechend anpassen, aber darauf achten, dass zwischen Spalte und Zeile ein $ steht.

B1 ist die erste Zelle auf dem Tabellenblatt mit den Kundendaten, in der eine PLZ steht. Auch das Anpassen.

Ich gehe davon aus, dass die vierstelligen PLZ nur ein Beispiel waren oder für Österreich/Schweiz und nicht die 5stelligen PLZ irgendwie manipuliert werden müssen.

Kommentar von DeeDee07 ,

Funktioniert die BF mittlerweile tabellenübergreifend? Meines Wissens geht das nicht, man musste das mit einer Namensdefinition umgehen (siehe Kommentar bei gfntom)

Kommentar von DeeDee07 ,

Ansonsten gäbe es noch diese Variante http://excelformeln.de/tips.html?welcher=109

Anscheinend funktioniert das in neuen Versionen auch direkt tabellenübergreifend.

Kommentar von Ninombre ,

Excel 2010 murrt nicht bei tabellenübergreifende Abfrage in der bed. Formatierung

Antwort
von gfntom, 28

Versuche es mit der "bedingten Formatiereung" (zu finden in Lasche "Start") -> Bedingte Formatierung -> Regeln zum Hervorheben von Zellen -> weitere Regeln -> Formeln zur Ermittlung der zu formatierenden Zellen verwenden

Dort eintragen:
=ZÄHLENWENN(A1:A100;B1)
und die Markierung auswählen

In dem Beispiel stehen die PLZ von A1-A100, B1 ist die Zelle, die hervorgehoben werden soll. Diese Formatvorschrift kann dann auf die anderen Zellen übertragen werden.

Kommentar von WarpDiva ,

Danke für die Antwort :-)

Das Problem dabei ist, dass bestimmte PLZ gesucht werden, also 4710, 4700 aber nicht 4702 (als Beispiel) somit geht die bedingte Formatierung mit Wert 1-100 nicht, da zum Beispiel 75 und 66 nicht dazugehören. 

Kommentar von gfntom ,

Versteh ich nicht. Du schreibst, du hast einen Sheet in dem die PLZ stehen - ganau jenen Bereich gibst du statt "A1:A100" ein.
1-100 sind keine Werte sondern Zellenbezüge.

Wenn also deine relevanten PLZs  Auf Sheet2 C25 bis D39 stehen, so gibst du anstatt meines Beispiels von "A1:A100" ein "Sheet2!C25:D39".

Wenn dieses Sheet allerdings PLZs beinhaltet, die nicht markiert werden sollen, so kann Excel natürlich nicht erraten, welche relevant sind.

Nun will ich, dass in der ersten Liste nur solche aufgezeigt werden, welche die PLZ vom anderen Sheet beinhaltet.

Dass in dem Sheet nicht alle PLZ relevant sind, hast du nirgends erwähnt.

Du musst also einen Bereich erstellen, der die nur relevanten PLZs enthält und diese in die Zählenwenn-Formel oben eintragen.

Kommentar von WarpDiva ,

Hm dann war ich wohl auf dem richtigen Weg... Die Sache ist nur, wenn ich bei bedingter Formatierung auf den Sheet verweise, gibt es eine Fehlermeldung und es kommt folgender Text.

Direkte Verweise auf einen Arbeitsblattbereich können in der Formel Bedingte Formatierung nicht verwendet werden. Ändern Sie den Verweis zu einer einzelnen Zelle, oder verwenden Sie einen Verweis mit einer Arbeitsblattfunktion, zum Beispiel =SUMME(A1:E5).

Kommentar von gfntom ,

Seltsam, bei mir funktioniert das.

Ich habe folgende Funktion in die bedingte Formatierung eingefügt, um O630 (auf Tabelle2) zu markieren.

Wenn einer der Werte in Tabelle5!A1:A9 gleich dem Wert in O630 ist, wird die Markierung aktiv.

=ZÄHLENWENN(Tabelle5!A1:A9;O630)

Kommentar von DeeDee07 ,

Es gibt einen Trick, wie du in der Bedingte Formatierung trotzdem auf andere Blätter zugreifen kannst:

Definiere für den Bereich wo die PLZ stehen einen Namen. Diesen Namen. https://support.office.com/de-de/article/Definieren-und-Verwenden-von-Namen-in-F...

Dann verwendest du diesen Namen in der Formel der BF anstatt des Zellbezugs.

Antwort
von Funfroc, 30

Hallo,

dein Kommentar in der Antwort von gfntom ist elementar, um dir helfen zu können.

Du suchst nicht nach einer bestimmten PLZ, sondern nach PLZ, mit den vordersten 4 Ziffern.

Demnach würde die bedingte Formatierung so aussehen:

=ISTZAHL(SVERWEIS(LINKS(A1;4)*1;E:E;1;0))

Muss entsprechend auf deine Liste angepasst werden.

LG, Chris

Kommentar von WarpDiva ,

Vielen Dank für den Kommentar :-) Nun, ich bin aus der Schweiz, da haben wir nur 4 Zahlen in der PLZ :-) tatsächlich befindet sie sich im Bereich zwische 3200 und 4900 von daher würde deine Formel glaube ich weniger passen.

Ich hatte das mit der bedingten Formatierung bereits versucht. aber da kam dieser Text als Fehler: Direkte Verweise auf einen Arbeitsblattbereich können in der Formel Bedingte Formatierung nicht verwendet werden. Ändern Sie den Verweis zu einer einzelnen Zelle, oder verwenden Sie einen Verweis mit einer Arbeitsblattfunktion, zum Beispiel =SUMME(A1:E5).

Ich habe mal die Excel Tabelle auf One Drive hochgeladen.

In Erklärungen war ich leider noch nie richtig gut, vielleicht seht ihr so, was ich genau meine. 

https://1drv.ms/x/s!AtJxCMvA1TDghcZfPKXvwt09DqFK9g

Kommentar von Funfroc ,

Hallo,

ok, das mit der Schweiz ist dann die Zusatzinformation, die es wieder zum "normalen" Fall macht :-)

Dann wäre dies die Formel für die bedingte Formatierung.

=ZÄHLENWENN('Aarburg 20km Umkreis PLZ'!A:A;C1)>0

Zelle C1 auswählen, Formel als Regel eintragen, "wird angewendet auf" auf C:C einstellen.

LG, Chris

Edit: anhand deines Uploads gehe ich davon aus, dass es sich um 2 Tabellenblatter einer Datei handelt. Dass sollte gehen.

Kommentar von WarpDiva ,

Super, danke :-) So zeigt es mir mit WAHR / FALSCH an, welche Werte stimmen. Genial, vielen Dank :-) 

Gibt es evt. auch die Möglichkeit, es farblich zu markieren oder die "Falschen" auszublenden?

Kommentar von Funfroc ,

Hallo,

ja, dass ist dann die bedingte Formatierung, auf die ich eigentlich schon hinaus wollte.

Du klickst C1 an. --> Klickst in der Bearbeitungsleiste auf den Button Bedingte Formatierung. --> Dann auf Regeln verwealten. --> Dann auf Neue Regel. --> Dann auf Formel zur Ermittlung der zu formatierenden Zellen verwenden. --> Gibst dort die Formel ein. --> Klickst auf Formatieren und legst das Format fest. (Dieses wird angewendet, wenn WAHR zutrifft) --> OK. --> Bei "wird angewendet auf" gibst du C:C ein. --> Klick auf übernehmen.

LG, Chris

Antwort
von Muhtant, 30

Das Stichwort was du suchst und was dir hilft, nennt sich "Bedingte Formatierung" :)

Keine passende Antwort gefunden?

Fragen Sie die Community