Gibt es in Excel eine Formel, mit der ein Feld mit einer bestimmten Zahl gesucht und angezeigt bzw. ein "Ja" oder ein "Nein gezeigt wird?

...komplette Frage anzeigen

4 Antworten

Man könnte es auch so lösen:

Angenommen die zu suchende Zahl steht in C1 und die zu durchsuchenden Zellen sind A1 bis B20 (das können auch noch mehr Spalte und Zeilen sein)

Markiere alle zu durchsuchenden Zellen -> dann Ribbon Start -> Bedingte Formatierung -> Regeln zum Hervorheben von Zellen -> Gleich ... -> wen das Dialogfenster erscheint, klickst du auf die Zelle C1 und es wird automatisch in das linke Feld =$C$1 eingetragen. Wähle eine gewünsche Formatierung und dann OK.

Jetzt werden alle bedingt formatierten Zellen, die denselben Inhalt wie Zelle C1 haben, markiert.

Bedingte Formatierung - (Computer, Programm, Mathematik)

Wenn du z. B. die Zahl 5 suchst:

=WENN(ZÄHLENWENN(A1:C1000;5)>0;"Ja";"Nein")
plaggemeier 05.11.2015, 13:27

Super, das funktioniert einwandfrei!! Weißt du auch, wie man sich anzeigen lassen kann, wo die gesuchte Zahl steht?

1
Suboptimierer 05.11.2015, 13:35
@plaggemeier
=WENNFEHLER(ADRESSE(SUMME((A1:C4=5)*ZEILE(A1:C4));SUMME((A1:C4=5)*SPALTE(A1:C4));1;1);"")

Das musst du mit Strg + Shift + Enter abschließen.
Die Formel funktioniert nur, wenn es maximal einen Treffer gibt.

2
iMPerFekTioN 05.11.2015, 13:42
@Suboptimierer

Du könntest (Solange es sich nur um eine Spalte handelt auch ein Hyperlink mit einfügen (Anklickbar und zum Ziel Springen)

=HYPERLINK("#"&ADRESSE(VERGLEICH(D4;A1:A19;1)+0;1);"Test Link")
3
Iamiam 05.11.2015, 14:53
@Suboptimierer

ohne {spez.Eingabe} (hier nur für den kleinen Bereich H32:J34, gesucht: 5)):

=ADRESSE(SUMMENPRODUKT((H32:J34=5)*ZEILE(H32:J34));SUMMENPRODUKT((H32:J34=5)*SPALTE(H32:J34)))

ist aber das gleiche Prinzip. Und ggf. ebenfalls mit WENNFEHLER(...;"-") einrahmen.

oder:

=ADRESSE(SUMMENPRODUKT((NICHT(ISTFEHLER(FINDEN(5;H32:J34;1)))*1>0)*ZEILE(H32:J34));SUMMENPRODUKT((NICHT(ISTFEHLER(FINDEN(5;H32:J34;1)))*1>0)*SPALTE(H32:J34)))

Das *1 verhindert, dass mehrere Ergebnisse summiert werden. Man kann all das auch mit Texten machen.

Da hat mich übrigens Suboptimierer draufgebracht, ich hab nur noch damit rumgespielt!

Bei wesentlich grösseren Bereichen könnte recht schnell der Arbeitsspeicher dicht machen

1
Iamiam 05.11.2015, 15:04
@Iamiam

missverständlich:

Das *1 verhindert, dass mehrere Ergebnisse summiert werden. Man kann all das auch mit Texten machen.

Es darf nur ein Ergebnis vorkommen (ggf. Wenn(Zählenwenn(H32:J34;5)<>1;"zuviele oder kein Eintrag, Suchbereich verkleinern!";obige Formel...) (sollte auch das Wennfehler überflüssig machen).

2
Ortogonn 05.11.2015, 22:14
@iMPerFekTioN

DH! Ich maaaag Hyperlink-Formeln :)

Anmerkungen zur Formel:

  •  Suchwert steht in Zelle D4,
  •  durchsucht wird A1 bis A19,
  •  angesprungen wird der erste Treffer bzw. der nächstkleinere Treffer.
  •  #NV wenn in D4 keine Zahl oder Zahl kleiner 1
1
Iamiam 05.11.2015, 23:50
@Iamiam

habs mal wieder viel zu kompliziert gemacht: obige Formel findet auch die 5 in 2,5 etc. Suchst du eine Zelle, in der die Zahl 5 und nichts anderes  steht, dann

=ADRESSE(SUMMENPRODUKT((5=H32:J34)*ZEILE(H32:J34));SUMMENPRODUKT((5=H32:J34)*SPALTE(H32:J34)))

1
iMPerFekTioN 06.11.2015, 08:14
@Ortogonn

#NV war ihm ja egal =) Bereich kann beliebig angepasst werden, sollte ja nur als Beispiel fungieren :P
Suchwert is ja auch Variabel =DD also,
Alles no Problemo!

1
Iamiam 06.11.2015, 12:01
@Suboptimierer

bei Zahlen, unbedingt. Bei Zeichenketten innerhalb anderer braucht man die Abfrage mit

NICHT(ISTFEHLER(FINDEN/suchen(5;H32:J34;1)))*1>0)*SPALTE(H32:J34)

aber das war hier ja nicht gefragt, ich hab mirs nur in einer meiner Dateien so hinterlegt - MEIN Fehler! (hatte Dich ja auch schon positiv bewertet)

0

Hast du mal die Suchen-Funktion von Excel benutzt?   ;-)

Wenn eine Zelle mit dem entsprechenden Inhalt vorhanden ist springst du dorthin, ansonsten gibt 's eine Fehlermeldung.

In der selben zeile? Dann "wenn"

In einem anderen blatt eventuell mit "sverweis"

Was möchtest Du wissen?