Zum Inhalt springen
Frage 3. August 2011 · 26 Antworten

Bedingte Formatierung mit ODER

Hi Forum,

Ich möchte mit der bedingten Formatierung ohne VBA Zellen einfärben, die bestimmte Wörter enthalten. Ich weiß, dass Excel nur drei Bedingungen kennt. Ist es aber möglich, pro Bedingung nach mehreren Wörtern zu suchen. Beispiel

=SUCHEN(„AMAZON*“;C10:smiley:1006)

Mit dieser Formel sollen alle Zellen die „AMAZON“ beinhalten z.B. rot eingefärbt werden. Wie müßte die Formle lauten, um alle Zellen die „AMAZON“ oder „GOOGLE“ enthalten rot einzufärben?

Verwendet: Excel 2003

Mit bestem Dank für die Mühe, Michael

Diese Frage stammt aus dem Jahr 2011. Weißt du es heute besser?

Aktuelle Antwort ergänzen

26 Antworten

Hi,

wenn das Wort nur in der Zelle steht:
=SUCHEN(A1;„Amazongoogle“;1)

stehen mehrer Wörter in der Zelle
=ODER(ISTZAHL(SUCHEN(„Amazon“;A1;1));ISTZAHL(SUCHEN(„Google“;A1;1)))

Wenn noch mehr Wörter gesucht werden lohnt es sich ne Liste anzufertigen und der nen Namen zu verpassen!

VG REné

0 Hilfreich
Antwort auf silex1_a7ada1zum Beitrag ↑

Hi, wenn das Wort nur in der Zelle steht: =SUCHEN(A1;„Amazongoogle“;1) stehen mehrer Wörter in der Zelle =ODER(ISTZAHL(SUCHEN(„Amazon“;A1;1));ISTZAHL(SUCHEN(„Google“;A1;1))) Wenn noch mehr Wörter gesucht werden lohnt es …

Hallo Rene,

1000 Dank, das haut prima hin. Ich wäre aber noch neugierig, wie genau das mit der Liste für noch mehr Wörtern gehen würde.

Mit bestem Dank für deine Geduld, Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Rene, 1000 Dank, das haut prima hin. Ich wäre aber noch neugierig, wie genau das mit der Liste für noch mehr Wörtern gehen würde. Mit bestem Dank für deine Geduld, Michael

1000 Dank, das haut prima hin. Ich wäre aber noch neugierig,
wie genau das mit der Liste für noch mehr Wörtern gehen würde.

Hallo Michael,

das könnte so sein:

=Zählenwenn(A1;Liste)>0
oder
=Zählenwenn(Liste;A1)>0

Sorry, ungetestet verwechsel ich da permanent die Reihenfolge.

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

1000 Dank, das haut prima hin. Ich wäre aber noch neugierig, wie genau das mit der Liste für noch mehr Wörtern gehen würde. Hallo Michael, das könnte so sein: =Zählenwenn(A1;Liste)>0 oder =Zählenwenn(Liste;A1)>0 Sorry, u …

Hallo Reinhard,

mein Gott, wenn man einmal mit Excel anfängt! Ich habe entsprechend deinem Vorschlag in einem neuem Arbeitsblatt einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt. Leider bekommen ich bei dem Versuch, in der Bedingten Formatierung diese Liste durch einfaches anclicken der Liste einzufügen:
=Zählenwenn(A1;[Importmakro.xls]Listen!$A$1)>0
den Fehler: Bezüge auf andere Tabellen oder Arbeitsmappen dürfen in dem Kriterium Bedingte Formatierung nicht verwendet werden. Auch wenn die Liste in der selben Mappe liegt
=Zählenwenn(A1;Listen!$A$1)>0 bekomme ich diesen Fehler.
Sinnvollerweise müßte ich diese Listen aber sowieso in einer anderen Mappe führen. Läßt sich das irgendwie einrichten?

Gruß Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Reinhard, mein Gott, wenn man einmal mit Excel anfängt! Ich habe entsprechend deinem Vorschlag in einem neuem Arbeitsblatt einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt. Leider bekommen ich bei dem V …

Hallo Michael,

mein Gott, wenn man einmal mit Excel anfängt! Ich habe
entsprechend deinem Vorschlag in einem neuem Arbeitsblatt
einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt.

das ist ja prinzipiell korrekt. Mit „Liste“ ist aber gemeint daß du für diese Liste einen Namen vergibst.
Angenommen diese Liste, also eine einpsltige Tabelle, ist in Tabelle2, dann
in Excel ab 2007: Formeln—Namen definieren…
früheres Excel: Einfügen–namen…
einen Namen, z.B. Liste" vergeben für
=Tabelle2!A1:A10
oder
=Tabelle2!A:A

Dann kannst du in Tabelle1 einen Zellbereich markieren, dann auf bed. Formatierung gehen und
=ZÄHLENWENN(Liste;A1)
eingeben. (A1 ist dabei die oberste zelle des markierten Bereiches.

Sinnvollerweise müßte ich diese Listen aber sowieso in einer
anderen Mappe führen. Läßt sich das irgendwie einrichten?

Ja. Das klappt dann wenn die andere Mappe geöffnet ist.
Um das sicherzustellen und diese andere mappe nicht immer manuell starten zu müssen bietet sich dafür die personl.xls an bzw. ein Add-In (xla)

Dann würde die Namens-Formel so aussehen:
=[Bestell3.xls]FiBu!A1:A10

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo Michael, mein Gott, wenn man einmal mit Excel anfängt! Ich habe entsprechend deinem Vorschlag in einem neuem Arbeitsblatt einer anderen Mappe eine Liste mit Schlüsselwörtern angelegt. das ist ja prinzipiell korrekt …

Hallo Reinhard,

gibt´s eigentlich auch ein Excel ohne Formeln? Okay, ganz im Ernst: Per Namen greift die bedingte Formatierung jetzt auf die Listen zu, das ist ein Fortschritt. Aber !! was immer ich auch anstelle, mit dieser Formel wird immer nur der erste Listeneintrag erfasst:

=ZÄHLENWENN(A1;Test)>0

Die Liste Test umfasst vier Zeilen
=Tabelle2!$A$1:blush:A$4
z.B. *Google*, *Amazon*, *Ebay*, *Paypal*. Steht Google in der ersten Zeile, werden alle Zellen mit Google eingefärbt - die anderen Zellen mit Einträgen der Liste aber nicht. Tausche ich und bringe Ebay in die erste Zeile, dann werden die Ebay Zellen eingefärbt, Google Zellen bleiben weiß.

Ich bin zuversichtlich, du hast auch dafür einen Rat.

Mit bestem Dank im Voraus, Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Reinhard, gibt´s eigentlich auch ein Excel ohne Formeln? Okay, ganz im Ernst: Per Namen greift die bedingte Formatierung jetzt auf die Listen zu, das ist ein Fortschritt. Aber !! was immer ich auch anstelle, mit di …

Grüezi Michael

;Aber !! was immer ich

auch anstelle, mit dieser Formel wird immer nur der erste
Listeneintrag erfasst:

=ZÄHLENWENN(A1;Test)>0

Ich glaube dir ist hier passiert was Reinhard für sich beansprucht hat - Du hast die Parameter in der Formel vertauscht… :wink:

Die Liste Test umfasst vier Zeilen
=Tabelle2!$A$1:blush:A$4
z.B. *Google*, *Amazon*, *Ebay*, *Paypal*. Steht Google in der
ersten Zeile, werden alle Zellen mit Google eingefärbt - die
anderen Zellen mit Einträgen der Liste aber nicht. Tausche ich
und bringe Ebay in die erste Zeile, dann werden die Ebay
Zellen eingefärbt, Google Zellen bleiben weiß.

Du suchst hier den Begriff, der in ‚Test‘ steht (und da wird dann die erste Zelle genommen) in A1.

Die korrekte Syntax lautet (sowas steht auch jeweils in der Hilfe zur Funktion):
ZÄHLENWENN(Bereich; Suchkriterien)

Für dich daher:
=ZÄHLENWENN(Test;A1)>0

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
0 Hilfreich
Antwort auf Thomas_Ramelzum Beitrag ↑

Grüezi Michael ;Aber !! was immer ich auch anstelle, mit dieser Formel wird immer nur der erste Listeneintrag erfasst: =ZÄHLENWENN(A1;Test)>0 Ich glaube dir ist hier passiert was Reinhard für sich beansprucht hat - Du ha …

Hallo und einen Gruß an den Schweizer Frühaufsteher,

ich hatte vergessen zu schreiben, dass ich vor meiner Nachfrage schon beide Varianten ausprobiert hatte, also sowohl
=Zählenwenn(A1;Test)>0
als auch
=Zählenwenn(Test;A1)>0

Dabei findet
=ZÄHLENWENN(A1;Test)>0
wie beschrieben jeweils nur den ersten Listeneintrag, wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.

Wie schon gesagt: Name „Test“ ist definiert für das Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*
Was kann denn hier noch falsch laufen ?

Gruß Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo und einen Gruß an den Schweizer Frühaufsteher, ich hatte vergessen zu schreiben, dass ich vor meiner Nachfrage schon beide Varianten ausprobiert hatte, also sowohl =Zählenwenn(A1;Test)>0 als auch =Zählenwenn(Test;A …

Grüezi Michael

wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.

Wie schon gesagt: Name „Test“ ist definiert für das
Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*
Was kann denn hier noch falsch laufen ?

Hmmm, was genau steht denn in A1 in dem Tabellenblatt auf dem du die Bedingte Formatierung anwenden willst?

Oder wo genau ist die Zelle in der dein Begriff enthalten ist?
Die Addresse in der Formel solltest Du natürlich auf deinen Bereicht (deine Zelle) anpasse, das liegt IMO auf der Hand.

Damit =ZÄHLENWENN(Test;A1)>0 vergleichst Du den Inhalt der Zelle A1 mit deiner Liste…

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo und einen Gruß an den Schweizer Frühaufsteher, ich hatte vergessen zu schreiben, dass ich vor meiner Nachfrage schon beide Varianten ausprobiert hatte, also sowohl =Zählenwenn(A1;Test)>0 als auch =Zählenwenn(Test;A …

Dabei findet
=ZÄHLENWENN(A1;Test)>0
wie beschrieben jeweils nur den ersten Listeneintrag,
wohingegen
=ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt.

Wie schon gesagt: Name „Test“ ist definiert für das
Tabellenblatt Listen,also
=Listen!$A$1:blush:A$4
und A1 bis A4 enthalten der Reihe nach
*Google*
*Amazon*
*paypal*
*Live*

Hallo Michael,

ich kann das so nicht nachstellen um den Fehler zu erreichen, schau mal hier, da sind in den beiden gerahmten Bereichen deine beiden Formelvarianten wobei natürlich die obere die richtige ist.

Gruß
Reinhard

0 Hilfreich
Antwort auf Thomas_Ramelzum Beitrag ↑

Grüezi Michael wohingegen =ZÄHLENWENN(Test;A1)>0 überhaupt keine Treffer bringt. Wie schon gesagt: Name „Test“ ist definiert für das Tabellenblatt Listen,also =Listen!$A$1:blush:A$4 und A1 bis A4 enthalten der Reihe nach …

Hallo Ihr lieben Helfer Reinhard und Thomas,

mittels der Beispieltabelle (die natürlich funktioniert) habe ich den Fehler gefunden: Die Listeneinträge erlauben für die Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die Schlüsselwörter oft nicht allein in der Zelle stehen (darum hatte ich auch meine Beispiele immer in *xxx* gesetzt).
Erstaunlicherweise sind bei der Umkehrung
=ZÄHLENWENN(A1;Test)>0 aber Wildcards möglich.
Problem also scheinbar erkannt - aber leider noch nicht gelöst: Gibt es die segensreiche Verbindung Liste mit Wildcards - und wie sieht sie aus?

Gruß Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Ihr lieben Helfer Reinhard und Thomas, mittels der Beispieltabelle (die natürlich funktioniert) habe ich den Fehler gefunden: Die Listeneinträge erlauben für die Formel =ZÄHLENWENN(Test;A1)>0 keine Wildcards - die …

mittels der Beispieltabelle (die natürlich funktioniert) habe
ich den Fehler gefunden: Die Listeneinträge erlauben für die
Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die
Schlüsselwörter oft nicht allein in der Zelle stehen

Hallo michael,

um das Ganze zu beschleunigen und abzukürzen, lade mit
http://www.file-upload.net
mal meine Beispielmappe hoch in der du Wildcards benutzt.

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

mittels der Beispieltabelle (die natürlich funktioniert) habe ich den Fehler gefunden: Die Listeneinträge erlauben für die Formel =ZÄHLENWENN(Test;A1)>0 keine Wildcards - die ich aber unbedingt benötige, weil die Schlüss …

Hallo Reinhard,

ich nehme dein Angebot gern an.

http://www.file-upload.net/download-3639251/kwverkn-…

Ich habe auf dem Blatt Listen, Spalte C die Schlüsselwörter jeweils mit ** eingegeben, dafür den Namen Wildcardtest definiert und dann die Beispieltabellen kopiert und in den Kopien jeweils die Bedingte Formatierung auf
=ZÄHLENWENN(Wildcardtest;A1)>0 bzw.
=ZÄHLENWENN(A1;Wildcardtest)>0 geändert.

Daraufhin werden die Schlüsselwörter wie von mir beschrieben eingefärbt oder eben auch nicht.

Vielen Dank für die Geduld, Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Ihr lieben Helfer Reinhard und Thomas, mittels der Beispieltabelle (die natürlich funktioniert) habe ich den Fehler gefunden: Die Listeneinträge erlauben für die Formel =ZÄHLENWENN(Test;A1)>0 keine Wildcards - die …

Grüezi Michael

Auch für dich gilt hier:

Dann schildere doch mal detailliert, was denn die Anforderungen sind.

Welche Inhalte habe deine Zellen im ersten Tabellenblatt - womit ist da zu rechnen?

Und wonach genau willst Du denn nun suchen - offenbar ob einer der Begriffe im zweiten Tabellenblatt Teil des Inhalts einer Zelle auf dem ersten Tabellenblatt ist?

Das geht nämlich auch aus deiner Mappe nicht wirklich hervor, da hat es von allem etwas drin, aber nichts wirklich Konkretes.

mittels der Beispieltabelle (die natürlich funktioniert) habe
ich den Fehler gefunden: Die Listeneinträge erlauben für die
Formel
=ZÄHLENWENN(Test;A1)>0
keine Wildcards - die ich aber unbedingt benötige, weil die
Schlüsselwörter oft nicht allein in der Zelle stehen (darum
hatte ich auch meine Beispiele immer in *xxx* gesetzt).

Und warum um aller Welten Willen sagst Du uns das nicht?
Woher wollen wir wissen, dass Du nicht einfach den Begriff in deinem Beitrag hervorheben wolltest?

Merke:
Jede Antwort kann nur so detailliert und fundiert sein wie es auch deine Fragestellung ist (mal von ein paar Sternstunden und der Kristallkugel abgesehen…).

Erstaunlicherweise sind bei der Umkehrung
=ZÄHLENWENN(A1;Test)>0 aber Wildcards möglich.

Bitte lies erstmal die Online-Hilfe zur Funktion ZÄHLENWENN() aufmerksam und so lange durch bis Du verstanden hast wie die Sache funktioniert und welcher Parameter wofür dient - und dann vergiss bitte allerschnellstens diese ‚Umkehr-Versuche‘ mit der Formel!

Nochmals zusammengefasst:

Du hast in einem Tabellenblatt Inhalte in Zellen die (auch) aus mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und nicht ausschliesslich) ein Wort aus einer Liste enthält, die auf einem zweiten Tabellenblatt erstellt worden ist.

Richtig so?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
0 Hilfreich
Antwort auf Thomas_Ramelzum Beitrag ↑

Grüezi Michael Auch für dich gilt hier: Dann schildere doch mal detailliert, was denn die Anforderungen sind. Welche Inhalte habe deine Zellen im ersten Tabellenblatt - womit ist da zu rechnen? Und wonach genau willst Du …

Hallo Thomas,

>>>Du hast in einem Tabellenblatt Inhalte in Zellen die (auch) aus
mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und
nicht ausschliesslich) ein Wort aus einer Liste enthält, die auf
einem zweiten Tabellenblatt erstellt worden ist.

Richtig so?

ja, genauso!

Gruß Michael

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Thomas, >>>Du hast in einem Tabellenblatt Inhalte in Zellen die (auch) aus mehreren Begriffen bestehen können. Du willst nun diese Zellen formatieren die (unter anderem und nicht ausschliesslich) ein Wort aus einer …

Grüezi Michael

>>>Du hast in einem Tabellenblatt Inhalte in Zellen die (auch)
aus
mehreren Begriffen bestehen können.
Du willst nun diese Zellen formatieren die (unter anderem und
nicht ausschliesslich) ein Wort aus einer Liste enthält, die
auf einem zweiten Tabellenblatt erstellt worden ist.

Richtig so?

ja, genauso!

Endlich *seufz*

Das kannst Du so mit normalen und/oder einigermassen einfachen Formeln nicht ‚erschlagen‘, da hier in die falsche Richtung gesucht wird (darum war, als Du die Parameter vertauscht hast, ein Teilerfolg zu verbuchen).

Füge die folgenden VBA-Zeilen in ein allgemeines Modul der Mappe ein:

  • Rechtsklick auf einen Tabellenblattreiter

  • Code anzeigen

  • Im VBA-Editor Menü:‚Einfügen‘

  • Modul

  • In dieses leere Fenster die Code-Zeilen 1:1 kopieren

  • Mit ALT+Q den VBA-Editor verlassen (vielleicht

Nun kannst Du deinen Bereich im ersten Tabellenblatt markieren und die folgende Formel verwenden um die Bedingte Formatierung zu aktivieren (den Bezug auf die aktive Zelle anpassen):

=tr_SearchWord(E12;Test)

Wenn Du noch einen 3.Parameter setzt, kannst Du das Ganze auch mit Berücksichtigung der Gross/Klein Schreibweise tun:

=tr_SearchWord(E12;Test;WAHR)

Und hier noch die Code-Zeilen:

Public Function tr\_SearchWord(rngZelle As Range, \_
 rngListe As Range, \_
 Optional GrossKlein As Boolean = False) As Boolean
Dim rngWord As Range
 If GrossKlein = True Then
 For Each rngWord In rngListe.Cells
 If InStr(1, rngZelle, rngWord) \> 0 Then
 tr\_SearchWord = True
 Exit Function
 End If
 Next rngWord
 Else
 For Each rngWord In rngListe.Cells
 If InStr(1, UCase(rngZelle), UCase(rngWord)) \> 0 Then
 tr\_SearchWord = True
 Exit Function
 End If
 Next rngWord
 End If
 tr\_SearchWord = False
End Function

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -

noch speichern)

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo Reinhard, ich nehme dein Angebot gern an. http://www.file-upload.net/download-3639251/kwverkn-… Ich habe auf dem Blatt Listen, Spalte C die Schlüsselwörter jeweils mit ** eingegeben, dafür den Namen Wildcardtest de …

http://www.file-upload.net/download-3639251/kwverkn-…

Hallo Michael,

wie wäre es denn mit Vba?
Alt+F11, Einfügen—Modul, Code reinkopieren, Editor schließen.

Bei bedingter Formatierung dann Formel ist
=Finde(E1)

wobei dann in Tabelle2 die Suchwörter untereinander in A1:Ax stehen sollen ohne Sternchen.

Gruß
Reinhard

Option Explicit

Function Finde(ByRef Zelle As Range) As Boolean
Dim Zei As Long
With Worksheets("Listen")
 For Zei = 1 To .Cells(Rows.Count, 1).End(xlUp).Row
 If Zelle.Value "" And InStr(UCase(Zelle.Value), UCase(.Cells(Zei, 1).Value)) \> 0 Then
 Finde = True
 Exit Function
 End If
 Next Zei
End With
End Function
0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

http://www.file-upload.net/download-3639251/kwverkn-… Hallo Michael, wie wäre es denn mit Vba? Alt+F11, Einfügen—Modul, Code reinkopieren, Editor schließen. Bei bedingter Formatierung dann Formel ist =Finde(E1) wobei dan …

Hallo Reinhard,

da VBA mein Stiefkind ist…eher ein Kind, was ich zur Adoption frei gab…hier was mit Formeln:

=VERWEIS(9^9;SUCHEN(Test;E12))

0 Hilfreich
Antwort auf silex1_a7ada1zum Beitrag ↑

Hallo Reinhard, da VBA mein Stiefkind ist…eher ein Kind, was ich zur Adoption frei gab…hier was mit Formeln: =VERWEIS(9^9;SUCHEN(Test;E12))

=VERWEIS(9^9;SUCHEN(Test;E12))

Hallo René,

hast du die Formel „geboren“?

Erklär sie mir mal bitte.
Wenn nicht heute dann kommste morgen zu spät, denn da werde ich sie auuuuuuusgiebig testen um auf ihr Geheimnis zu kommen :smile:)
Kann doch gar nicht sein daß so eine winzige Formel funktioniert und ich weiß nicht warum.

Naja, ich hab glaub gestern SUCHEN(Test;E12) einzeln getestet, kam immer eine Fehlermeldung.
May be ich hab da einen Fehler gemacht.

9^9 sehe ich als große zahl an, sonst nix. Wird aber morgen gleich mitgetestet und erstmal durch 4711 ersetzt.
Wenn du Jungspund überhaupt weißt wofür 4711 steht/stand *lächel*

Schönes Rest-WE für dich
Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

=VERWEIS(9^9;SUCHEN(Test;E12)) Hallo René, hast du die Formel „geboren“? Erklär sie mir mal bitte. Wenn nicht heute dann kommste morgen zu spät, denn da werde ich sie auuuuuuusgiebig testen um auf ihr Geheimnis zu kommen …

Hallo Reinhard,

nee, geboren habe ich sie nicht. War aus nem Beitrag, wo ich auch mitgetüftelt hatte und dies war dann das Endergebnis von Janny77.

Der VERWEIS() baut ne Matrix aus dem „Test“. Fehlerwerte werden so ignoriert, bis es was gefunden hat.

VERWEIS() ist zu noch ganz anderen Sachen im Stande. Auch der VERGLEICH() kann bei so manchen Sachen helfen.

Und 4711 ist doch „Kölnisch Wasser“ :wink:

Und warum nannten die 4711? (Ich kenn die Antwort, Du auch?)

VG René

0 Hilfreich
Antwort auf silex1_a7ada1zum Beitrag ↑

Hallo Reinhard, nee, geboren habe ich sie nicht. War aus nem Beitrag, wo ich auch mitgetüftelt hatte und dies war dann das Endergebnis von Janny77. Der VERWEIS() baut ne Matrix aus dem „Test“. Fehlerwerte werden so ignor …

Hallo Renè,

nee, geboren habe ich sie nicht. War aus nem Beitrag, wo ich
auch mitgetüftelt hatte und dies war dann das Endergebnis von
Janny77.

also warst/bist du einer von den Hebammen :smile:

Der VERWEIS() baut ne Matrix aus dem „Test“. Fehlerwerte
werden so ignoriert, bis es was gefunden hat.

Aber in =VERWEIS(6;SUCHEN(Test;E12))
liefert doch Suchen() „#Wert!“ bei allen Zellen!?
Auf diesen fehler greift dann Verweis zu, da hängt’s bei mir noch warum die Formel trotzdem funktioniert.

Zu der 6, die Zahl muß min so groß sein wie die Anfangsposition des Suchwortes in der Zelle.

Und 4711 ist doch „Kölnisch Wasser“ :wink:
Und warum nannten die 4711? (Ich kenn die Antwort, Du auch?)

*fluch*, ja, ich wußte es mal, das Wissen hat sich aber verflüchtigt :smile:

Gruß
Reinhard

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo Renè, nee, geboren habe ich sie nicht. War aus nem Beitrag, wo ich auch mitgetüftelt hatte und dies war dann das Endergebnis von Janny77. also warst/bist du einer von den Hebammen :smile: Der VERWEIS() baut ne Matr …

Hallo liebe Experten,

ich wollte mich nur bedanken für die Hilfestellung zu meiner Anfrage. Sowohl die VBA Lösung von Thomas wie auch die Formel von Silex1 funktionieren genau wie ich es benötige. Viel Spaß beim weitertüffteln.

Gruß Michael

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo Renè, nee, geboren habe ich sie nicht. War aus nem Beitrag, wo ich auch mitgetüftelt hatte und dies war dann das Endergebnis von Janny77. also warst/bist du einer von den Hebammen :smile: Der VERWEIS() baut ne Matr …

Hallo Reinhard,

Aber in =VERWEIS(6;SUCHEN(Test;E12))
liefert doch Suchen() „#Wert!“ bei allen Zellen!?
Auf diesen fehler greift dann Verweis zu, da hängt’s bei mir
noch warum die Formel trotzdem funktioniert.

Weil es in der Liste keine Leerzelle gibt (und nicht geben darf!) und somit nur ein #NV zurück geliefert wird. Erweitere mal die Liste um ne Leerzelle und Du siehst was ich meinte!

Zu der 6, die Zahl muß min so groß sein wie die
Anfangsposition des Suchwortes in der Zelle.

Daher 9^9, denn wo das Wort steht in einem langen Text ist ja nicht vorher zusehen.

*fluch*, ja, ich wußte es mal, das Wissen hat sich aber
verflüchtigt :smile:

Es war/ist die Hausnummer des Erfinders/der Firma!:wink:

VG René

0 Hilfreich
Antwort auf silex1_a7ada1zum Beitrag ↑

Hallo Reinhard, Aber in =VERWEIS(6;SUCHEN(Test;E12)) liefert doch Suchen() „#Wert!“ bei allen Zellen!? Auf diesen fehler greift dann Verweis zu, da hängt’s bei mir noch warum die Formel trotzdem funktioniert. Weil es in …

Hallo Renè

Aber in =VERWEIS(6;SUCHEN(Test;E12))
liefert doch Suchen() „#Wert!“ bei allen Zellen!?
Auf diesen fehler greift dann Verweis zu, da hängt’s bei mir
noch warum die Formel trotzdem funktioniert.

Weil es in der Liste keine Leerzelle gibt (und nicht geben
darf!) und somit nur ein #NV zurück geliefert wird. Erweitere
mal die Liste um ne Leerzelle und Du siehst was ich meinte!

okay, das mit den Leerzeilen mach ich später.
Du bist bei #NV was die Gesamtformel zurückliefert (ggfs.)
Ich hänge aber am #WERT als Ergebnis von Suchen() fest.

Bislang gehe ich davon aus daß immer von innen nach außen hin „aufgelöst/berechnet“ wird.

D.h. (Fkt=Funktion)
=Fkt1(Argumente zu Fkt1;Fkt2(Argumentenliste für Fkt2))
wird dann so in dieser Reihenfolge berechnet:
erst
=Fkt1(Argumente zu Fkt1;Ergebnis von Fkt2)
dann wird in der Formelzelle das Ergebnis der gesamtformel, also Fkt1 angezeigt.

Im konkreten Fall wäre der zweite Schritt in jeder Zelle
=VERWEIS(6;#WERT!)

Ist dir klar warum mir grad nix kalr ist? :smile:
Diese zugegebenerweise nette kleine und hilfreiche Formel sabotiert derzeit meinen jahrelangen Glauben ich hätte ein Stückchen von Excels Formelumgang kapiert :frowning:(

Zu der 6, die Zahl muß min so groß sein wie die
Anfangsposition des Suchwortes in der Zelle.

Daher 9^9, denn wo das Wort steht in einem langen Text ist ja
nicht vorher zusehen.

Ja, jetzt klar, sicher weiß ich wrum man gelegentlich in eine Formel 999 oder 9^9 o.ä. einbaut.
In dem Fall hat es aber eweng gedauert bis mir klar wurde daß dies mit dem Treffer, also der Anfangsposition des Suchwortes zu tun hat.
Darauf zu kommen ist mir aber sehr schwer gefallen weil mich blockiert unheimlihcdie Tatsache daß in allen Zellen die Funktion Suchen() #Wert zurückliefert.

*fluch*, ja, ich wußte es mal, das Wissen hat sich aber
verflüchtigt :smile:

Es war/ist die Hausnummer des Erfinders/der Firma!:wink:

Ist ja unfair so lange Straßen zu haben *gg*
Wenn ich sage daß wußte ich mal so ist das ungelogen, ist in der Tat so.
Vor vielen Jahren war ich sehr regelmäßig abends in einem Raum eines Chats. Waren immer von so 50 Stammgästen immer so 10-30 da (je nach Wochentag usw.) Und jedesmal wurde ein „Kwiss“ oder mehrere durchgeführt.

Irgedneiner hatte 20 fragen ausgearbeitet, die nach nacheinander gestellt wurde. Punktevergabe nach Schnelligkeit der richtigen Antwort und punkte vergabe ähnlich Formel1, der Schnellste 12 Pkte, der zweite 8, dann 6 usw. bis dann irgendwann alle anderen richtigen Antworten nur noch 1 Pkt zählten.
Alle Antworten galten die vor dem „Stop“ kam das irgendwann geschrieben wurde.
hat Laune gemacht.

Und Minimum war mindestens 2-3mal darunter auch in den 20 Fragen viele Fragen zu Firmennamen/produktnamen.
Wie „Warum heißt haribo haribo?“ usw.

Übrigens sah ich da zum ersten Mal in meinem Leben eine Exceltabelle *lächel* Die hatte ein ehemaliger Stammgast verfasst, damit konnte dann derjenige der unser „Wärter“ war die Pkte pro Mitmacher mitnotieren, auswerten und am Ende dann die Gewinner präsentieren.

Und, damals lief es zu 95-98% sauber, also 95-98% haben nicht gegoogled, einige konnten das nicht lassen aber das erkennt man wenn man jahrelang zusammen so etwas tut.
Naja, hat allgemeines Schulterzucken ausgelöst.

So, dieser kleine Ausflug ging ja im sehr weiten iws. *gg* auch um Excel also keinerlei OT erkennbar :smile:

Gruß
Reinhard

0 Hilfreich
Antwort auf Anonym_78cc72a08f91zum Beitrag ↑

Hallo liebe Experten, ich wollte mich nur bedanken für die Hilfestellung zu meiner Anfrage. Sowohl die VBA Lösung von Thomas wie auch die Formel von Silex1 funktionieren genau wie ich es benötige. Viel Spaß beim weitertü …

Danke für die Rückmeldung, Gruß Reinhard o.w.T.

0 Hilfreich
Antwort auf Reinhard_fb8cb7zum Beitrag ↑

Hallo Renè Aber in =VERWEIS(6;SUCHEN(Test;E12)) liefert doch Suchen() „#Wert!“ bei allen Zellen!? Auf diesen fehler greift dann Verweis zu, da hängt’s bei mir noch warum die Formel trotzdem funktioniert. Weil es in der L …

Hallo Reinhard,

Im konkreten Fall wäre der zweite Schritt in jeder Zelle
=VERWEIS(6;#WERT!)

Nein! Und hier liegt Dein Denkfehler!

Bei SUCHEN() würde es so aussehen:
=SUCHEN(#WERT!)
bei VERWEIS() aber ist es so:
=VERWEIS(6;{#WERT!;#WERT!;#WERT!;#WERT!})
(markiere mal in dem Verweis SUCHEN(Test;E12) und drücke F9)
der Verweis reiht so zusagen alles aneinander!

Suchvektor ist ein Bereich, der nur eine Zeile oder Spalte enthält. Zulässige Elemente von Suchvektor sind Zeichenfolgen (Texte), Zahlen oder Wahrheitswerte.
Und somit ignoriert er die Fehler :smile:

Verkürzt man den Vektor auf nur eine Zelle, gehts aber auch in die Binsen, aber dazu würde ja dann wieder SUCHEN() genügen!

VG René

0 Hilfreich

Deine Antwort

Zum Antworten bitte anmelden oder kostenlos registrieren.