Microsoft Excel

Herbers Excel/VBA-Archiv

Wert suchen und alle passenden Zeilennummern | Herbers Excel-Forum


Betrifft: Wert suchen und alle passenden Zeilennummern von: Markus
Geschrieben am: 14.08.2012 08:52:19

Guten Morgen zusammen,

die Formel unten gibt mir alle Werte wieder, die dem Suchkriterium entsprechen.

=INDIREKT("c"&KKLEINSTE(WENN((A$2:A$3000='Failure Type'!$D$17);ZEILE($2:$3000));ZEILE(A1)))

Im nächsten Schritt würde ich gerne die Zeilennummer ausgegeben bekommen, woher das Ergebnis stammt.

Und nun zu meinem eigentlichen Problem: Es kommt vor, dass als Ergebnis Werte mehrmals vorkommen können, wenn ich jetzt über

=VERGLEICH($H2;$C:$C;0)

mir die Zeilennummer ausgeben lasse, bekomm ich immer nur die erste Zeile ausgegeben, die dem Suchkriterium entspricht, wie müsste die Formel aussehen, damit ich nebem dem Suchkriterium jeweils die passende Zeile ausgegeben bekomme. Unten ist dargestellt, wie ich mir das vorstelle; bei meiner jetzigen Lösung wird bei dem Kriterium 5 als Ergebnis immer Zeile 1 ausgegeben. Wie lässt sich das ohne VBA lösen?

Zeile Kriterium
Zeile1 5 Ergebnis Zeile1
Zeile2 4 Ergbenis Zeile2
Zeile3 7 Ergebnis Zeile3
Zeile4 5 Ergebnis Zeile4
Zeile5 5 Ergbenis Zeile5
Zeile6 4 Ergebnis Zeile6

Vielen Dank schonmal für Eure Hilfe.
Ihr macht eine tolle Arbeit.

Gruß Markus

  

Betrifft: AW: Wert suchen und alle passenden Zeilennummern von: Boris
Geschrieben am: 14.08.2012 09:03:00

Hi Markus,

Du hast es doch schon in der Formel stehen:

KKLEINSTE(WENN((A$2:A$3000='Failure Type'!$D$17);ZEILE($2:$3000));ZEILE(A1))

als ARRAY(!)Formel gibt Dir doch nacheinander die Zeilennummern aus, die Du ja dann nur noch an INDIREKT übergibst.

VG, Boris


  

Betrifft: Beitrag ist von {mir}... von: {Boris}
Geschrieben am: 14.08.2012 09:06:51

...sitz nur grad an nem Mac - und da hab ich mal keine Ahnung, wie man die {} erzeugt ... (hab sie jetzt irgendwoher kopiert...).

VG, Boris


  

Betrifft: AW: Beitrag ist von {mir}... von: Markus
Geschrieben am: 14.08.2012 09:13:39

Hallo Boris,

ich benötige für einen Zwischenschritt die Zeilennummer aus der das Ergbenis stammt. So zeigt er mir ja nur das Ergebnis an, was ja auch gut ist, bloß wenn ich als Ergebnis einen Werte mehr als einmal vorkommen habe, wie bekomm ich die Zeilennummer heraus in der diese steht?

Gruß Markus


  

Betrifft: AW: Beitrag ist von {mir}... von: Markus
Geschrieben am: 14.08.2012 09:13:48

Hallo Boris,

ich benötige für einen Zwischenschritt die Zeilennummer aus der das Ergbenis stammt. So zeigt er mir ja nur das Ergebnis an, was ja auch gut ist, bloß wenn ich als Ergebnis einen Werte mehr als einmal vorkommen habe, wie bekomm ich die Zeilennummer heraus in der diese steht?

Gruß Markus


  

Betrifft: AW: Beitrag ist von {mir}... von: Markus
Geschrieben am: 14.08.2012 09:17:02

Danke Boris,

jetzt hab ich`s verstanden, manchmal sieht man den Wald vor lauter Bäumen nicht.

Gruß Markus


  

Betrifft: AW: Beitrag ist von {mir}... von: Markus
Geschrieben am: 14.08.2012 09:27:53

Ich stört noch einmal, ich bekomm zwar eine Zeile ausgegeben, wenn ich das Indirekt einfach weg lasse,
doch entspricht dieser nicht der richtigen Zeile.

Da passt etwas noch nicht. Wie müsste ich die Formel umschreiben, damit ich die Zeile bekomme, Excel hat sie ja im Hintergrund, sonst würde ich ja nicht dem Suchkriterium entsprechend mein Ergebnis erhalten.

Gruß Markus


  

Betrifft: AW: Beitrag ist von {mir}... von: {Boris}
Geschrieben am: 14.08.2012 09:35:04

Hi Markus,

kann ich nicht nachvollziehen:

=KKLEINSTE(WENN((A$2:A$3000='Failure Type'!$D$17);ZEILE($2:$3000));ZEILE(A1)))

als Arrayformel mit Strg+Shift+Enter eingeben und nach unten kopieren. Sollte Dir nacheinander aller TrefferZEILEN liefern.

VG, Boris


  

Betrifft: INDIREKT-Ersatz von: {Boris}
Geschrieben am: 14.08.2012 09:28:55

Hi Markus,

gut :-)

Kleiner Tipp am Rande:

INDIREKT gehört zu den volatilen Funktionen, die permanent eine Neuberechnung der Mappe erzeugen.
Man kann das in Deinem Fall auch mit INDEX erledigen und vermeidet dadurch die Volatilität (auch als Arrayformel):

=INDEX(C:C;KKLEINSTE(WENN((A$2:A$3000='Failure Type'!$D$17);ZEILE($2:$3000));ZEILE(A1)))

Müssen wir auf unserer Seite auch noch an einigen Stellen anpassen.

VG, Boris


  

Betrifft: AW: INDIREKT-Ersatz von: Markus
Geschrieben am: 14.08.2012 09:38:25

Hallo Boris,

hab das bei mir eingesetzt und erhalte auch die gewünschten Ergebnisse, zum Beispiel 5;4;5;3;7;5;4.

Der nächste Schritt soll ja nun sein sein, dass ich in den nächsten Spalte die Zeile angezeigt bekomm aus der das Ergebnis stammt. Bei dem Ergbenis 5 soll jetzt nun z.B. 38 und 156 stehen und nicht immer 38 weil die 5 das erste mal in Zeile 38 auftaucht.

Gruß Markus


  

Betrifft: AW: INDIREKT-Ersatz von: Markus
Geschrieben am: 14.08.2012 10:52:57

Hallo Boris,

Ich hab das jetzt so gelöst, dass ich die obrigen Abfrage für jede Spalte mit geänderten Indexspaltenbezug durchführe. Liefert die Werte die ich brauche, ist wahrscheinlich nicht die eleganteste Lösung es klappt ber und das ist wichtig.

Danke nochmal für deine Hilfe

Gruß Markus


  

Betrifft: Zeig mal... von: {Boris}
Geschrieben am: 14.08.2012 11:16:01

Hi Markus,

eine kleine Beispieldatei. Dann kann man sehen, ob es die "eleganteste" Lösung ist oder nicht ;-)

VG, Boris


Beiträge aus den Excel-Beispielen zum Thema "Wert suchen und alle passenden Zeilennummern "