Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Excel: Werte suchen und in einer Zelle ausgeben

Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 14:05:59
Arno89
Hallo zusammen.
Ich habe die folgende Matrix vor mir liegen
Userbild
Ich möchte nun in der Spalte B alle Zellen mit dem Wert 2 finden und anschließend die dazugehörigen ID's aus Spalte A in einer Zelle ausgeben, so wie manuell in B9 gemacht. Über die WENN/DANN Funktion komme ich soweit, dass ich die ID's jeweils einzeln in einer Spalte ausgebe mit =WENN(B2:B6=2; A2:A6; ""). Aber ich würde die gerne alle direkt in einer Zelle haben. Mit dem sVerweis wird mir ja nur das erste Suchergebnis "2" ausgegeben. Natürlich könnte ich das bei dieser recht kleinen Matrix auch manuell machen, aber würde die Formel dann später auch auf größere Matrizen anwenden. Wenn jemand des Rätsels Lösung weiß, bitte melden.
Vielen Dank im Voraus
Anzeige

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 14:26:04
Daniel
Hi
enthält deine Excelversion die Funktion TEXTVERKETTEN?
damit könnte es gehen, wie genau, kann ich dir nicht sagen weil meine Excelversion das nicht hat.
ansonsten wird's schwierig, da müsste man was mit VBA programmieren.
Gruß Daniel
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 14:35:32
Arno89
Die Funktion VERKETTEN ist in meiner Version vorhanden, habe aber noch keine Idee wie ich es damit lösen kann, außer die Werte erst mit einer WENN Funktion auszulesen und dann manuell die Verketten Funktion erstellen, also für die Zellen in denen die Werte dann stehen. Ist aber mega umstädnlich. Mit VBA kenn ich mich leider überhaupt nicht aus.
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 14:46:23
Daniel
Hi
probier mal sowas wie
=Textverketten(",";wahr;Wenn(B2:B6=2;A2:A6;""))

die Eingabe muss mit STRG+SHIFT+ENTER abgeschlossen werden, da es sich um eine Matrixformel handelt.
Gruß Daniel
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 15:00:25
Arno89
ok kommando zurück. Ich dachte die von dir gemeinte Funktion "Textverketten" zu haben, habe aber nur die Funktion "Verketten". Das ist leider etwas anderes :/
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 16:31:09
Michael
Hallo!
Wie Daniel schon erwähnt hat, da müsste dann etwas eigenes programmiert werden.
Hier ein schneller Versuch (also nichts allzu universelles) von mir, aber evtl. reicht Dir das ja schon: https://www.herber.de/bbs/user/112353.xlsm
Beispiele sind in der Tabelle, schau's Dir an; den Code zur Zellformel (UserDefinedFunction) findest Du im allgemeinen Modul1 (mit Alt + F11 die VBA-Entwicklungsumgebung öffnen, in diesem Fenster siehst Du dann links oben eine Art Explorer-Ansicht - da auf Modul1 doppelklicken). Dieser Code muss natürlich immer in der jeweiligen Mappe vorhanden sein, in der Du diese Funktion dann in einer Zelle nutzen willst (bzw. in der persönlichen Makroarbeitsmappe).
Kommst Du damit zurecht?
LG
Michael
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 17:40:55
Arno89
Hi Michael,
Ja das Hilft mir weiter! Habe das Makro in meine Datei eingefügt die passenden Zellenwerte hinzugefügt. Läuft so wie es soll. Besten Dank
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 18:37:15
Arno89
hätte noch ne kurze Frage. kann ich in meiner Datei so was die automatische Zeilennummerierung auch auf Spalten anwenden?
Also wenn ich in einer Zelle 1 und darunter 2 eingebe, kann ich die Spalte ja soweit ziehen wie ich will und erhalten die zahlen 3,4,5,6,7... mit Zellenbezügen funktioniert das ja auch.
Es wäre nun interessant zu wissen ob das auch für Spalten funktioniert.
Also auf meine Matrix bezogen, das ich die Formel einfach runterziehe und sich die Spalte ändert. Vereinfacht z.B. A$5, B$5,C§5... usw. da stehen. Wenn ich das normal runterziehe ohne absolute Zellenbzüge, ändert sich halt nur die Zahl aber nicht der Buchstabe
Anzeige
AW: Versuche mal das-siehe Datei
22.03.2017 20:53:10
Arno89
Also mit der Datei kann ich nicht so viel anfangen
So soll es am Ende aussehen, also die Formeln
Userbild
Aber wie gesagt, ich weiß nicht, wie ich das hinbekomme, dass sich in der Formel beim runterziehen die Spaltennummer ändert. Meine Idee war es eine Hilfstabelle anzulegen mit B,C,D,E,F und dann in der Formel darauf zu referenzieren, also ungefähr so: =IDMatch((=A8)$2:(=A8)$6;$A$2:$A$6;$K$1), mit der Annahme, das A8 den Wert "C" enthält. Aber ich musste feststellen, dass das syntaktisch nicht funktioniert...
Anzeige
Das sollte nur zeigen...
23.03.2017 08:25:38
robert
wie man die Spaltenbezeichnung in Zeilen machen kann.
Machen- und an Deine Datei anpassen musst Du schon selbst......
Aber wie ich sehe, hast Du schon wieder Änderungen geplant,
daher bin ich dann raus :-)
Gruß
robert
AW: Versuche mal das-siehe Datei
23.03.2017 08:53:03
Rainer
Hallo Arno,
probiere mal die Formeln zu transponieren:
Die Formel in I1 nicht nach "unten" ziehen, sondern nach rechts.
Die Zellen J bis x markieren, Suchen nach "=" und Ersetzen mit "XXX".
Die Zellen kopieren und transponiert einfügen (I2 bis Ix).
Die Zellen markieren, Suchen nach "XXX" und Ersetzen mit "=".
Gruß,
Rainer
Anzeige
AW: Versuche mal das-siehe Datei
23.03.2017 11:41:26
Arno89
Danke Rainer, die Idee mit dem transponieren war hilfreich ;)
AW: Versuche mal das-siehe Datei
22.03.2017 22:31:12
Arno89
Habe das Ergebnisse jetzt in einer Zeile und nicht in einer Spalte ausgegeben und dann mittels =INDIREKT(ADRESSE... in eine Spalte übertragen. nicht ganz sauber die Lösung, aber funktioniert.
AW: Excel: Werte suchen und in einer Zelle ausgeben
22.03.2017 22:41:03
Arno89
Noch ne Frage zu deinem Makro, was muss ich daran verändern, damit es auch funktioniert, wenn ich meine Werte anstatt aus den Spalten A und B, aus den Zeilen 1 und 2 bekommen bzw. vergleichen möchte?
Wenn ich in Excel anstatt =IDMatch(B2:B6;A2:A6;K1)einfach =IDMatch(B2:F2;B1:F1;K1) verwende, zeigt er mir #BEZUG! an.
Danke im Voraus ;)
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
23.03.2017 15:23:14
Michael
Hallo!
Wenn ich in Excel anstatt =IDMatch(B2:B6;A2:A6;K1)einfach =IDMatch(B2:F2;B1:F1;K1) verwende, zeigt er mir #BEZUG! an.
Ja, weil meine Funktion diesen Fall aktuell nicht abdeckt - ich hab Dir ja geschrieben, dass dies nichts Universelles ist, sondern eine Funktion für genau die Aufgabe, die Du ursprünglich gepostet hast.
Hier eine Variante, die horizontale und vertikale Bereich abdeckt (aber auch das ist keine perfekte Lösung für alle Anwendungen!): https://www.herber.de/bbs/user/112373.xlsm
LG
Michael
Anzeige
AW: Excel: Werte suchen und in einer Zelle ausgeben
23.03.2017 15:46:56
Arno89
Hi Michael
Vielen Dank, läuft perfekt. Habe noch die =WENNFEHLER Funktion mit der =IDMatch Funktion kombiniert, damit Zellen für die kein Treffer gefunden wurde leer bleiben und nicht #WERT! anzeigen. Damit läuft es aber so, wie ich es mir vorgestellt habe, also nochmals danke dafür ;)
Gerne und viel Erfolg noch! LG und owT
23.03.2017 15:59:14
Michael
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Werte suchen und in einer Zelle ausgeben in Excel


Schritt-für-Schritt-Anleitung

Um in Excel Werte zu suchen und in einer Zelle auszugeben, kannst du die folgende Schritt-für-Schritt-Anleitung befolgen. Diese Anleitung geht davon aus, dass du eine Excel-Version verwendest, die die Funktion TEXTVERKETTEN unterstützt. Falls du diese nicht hast, kannst du auch die VERKETTEN-Funktion verwenden, jedoch ist die Anwendung etwas umständlicher.

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in einer klar strukturierten Tabelle vorliegen. Zum Beispiel in Spalte A die IDs und in Spalte B die Werte, die du suchen möchtest.

  2. Formel eingeben: Benutze die folgende Formel, um die IDs, die mit dem Wert 2 in Spalte B übereinstimmen, in einer einzigen Zelle auszugeben:

    =TEXTVERKETTEN(",";WAHR;WENN(B2:B6=2;A2:A6;""))

    Hinweis: Diese Formel ist eine Matrixformel. Du musst sie mit STRG + SHIFT + ENTER abschließen.

  3. Ergebnisse anzeigen: Die IDs, die mit dem gesuchten Wert übereinstimmen, werden in einer Zelle durch Kommas getrennt angezeigt.


Häufige Fehler und Lösungen

  • Fehler #WERT!: Dies kann auftreten, wenn die Matrixformel nicht korrekt eingegeben wurde. Stelle sicher, dass du STRG + SHIFT + ENTER verwendest.
  • Funktion nicht gefunden: Wenn die Funktion TEXTVERKETTEN nicht verfügbar ist, verwende die VERKETTEN-Funktion, um die Ergebnisse manuell zu verketten, was jedoch umständlicher ist.
  • #BEZUG! Fehler: Dies kann geschehen, wenn du die Bereiche in der Formel nicht korrekt definiert hast. Überprüfe die Zellreferenzen.

Alternative Methoden

Falls du keine Matrixformeln verwenden möchtest oder deine Excel-Version nicht die Funktion TEXTVERKETTEN unterstützt, gibt es auch alternative Methoden:

  1. VBA-Lösungen: Du kannst ein einfaches VBA-Makro schreiben, das die IDs sucht und in einer Zelle ausgibt. Hier ist ein einfaches Beispiel:

    Function IDMatch(Werte As Range, IDs As Range, Suchwert As Variant) As String
       Dim Ergebnis As String
       Dim i As Long
    
       For i = 1 To Werte.Count
           If Werte.Cells(i).Value = Suchwert Then
               Ergebnis = Ergebnis & IDs.Cells(i).Value & ", "
           End If
       Next i
    
       IDMatch = Left(Ergebnis, Len(Ergebnis) - 2) ' entfernt das letzte Komma
    End Function

    Um das Makro zu verwenden, öffne die VBA-Entwicklungsumgebung mit ALT + F11 und füge den Code in ein neues Modul ein.

  2. Hilfstabellen: Du kannst auch Hilfstabellen verwenden, um die Ergebnisse zuerst in einer Spalte auszugeben und dann die Werte mithilfe der VERKETTEN-Funktion zusammenzuführen.


Praktische Beispiele

  • Beispiel 1: Wenn du in Spalte B nach dem Wert 3 suchst und die IDs in Spalte A hast, kannst du die Formel wie folgt anpassen:

    =TEXTVERKETTEN(",";WAHR;WENN(B2:B6=3;A2:A6;""))
  • Beispiel 2: Wenn du mehrere Werte in einer Zelle anzeigen möchtest, passe die Formel an, um verschiedene Suchwerte zu berücksichtigen:

    =TEXTVERKETTEN(",";WAHR;WENN((B2:B6=2)+(B2:B6=3);A2:A6;""))

Tipps für Profis

  • Kombination mit WENNFEHLER: Verwende die WENNFEHLER-Funktion, um sicherzustellen, dass leere Zellen anstelle von Fehlern angezeigt werden:

    =WENNFEHLER(TEXTVERKETTEN(",";WAHR;WENN(B2:B6=2;A2:A6;""));"")
  • Dynamische Bereiche: Überlege, dynamische Bereiche mit der BEREICH.VERSCHIEBEN-Funktion zu verwenden, damit deine Formel automatisch aktualisiert wird, wenn du Daten hinzufügst.


FAQ: Häufige Fragen

1. Wie kann ich in einer anderen Tabelle suchen? Um in einer anderen Tabelle zu suchen, musst du den Tabellennamen in der Formel angeben, z. B. Tabelle2!A2:A6.

2. Kann ich auch nach Textwerten suchen? Ja, du kannst auch nach Textwerten suchen, indem du die gleiche Formel verwendest und den Suchwert entsprechend anpasst, z.B. =TEXTVERKETTEN(",";WAHR;WENN(B2:B6="Text";A2:A6;"")).

3. Wie kann ich die Ergebnisse sortieren? Um die Ergebnisse zu sortieren, kannst du die SORTIEREN-Funktion verwenden, bevor du sie mit TEXTVERKETTEN verkettest.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige