Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1848to1852
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wertesuch- und Ausgabefunktion

Wertesuch- und Ausgabefunktion
08.10.2021 16:47:42
Anna
Hallo zusammen,
ich habe folgendes Problem: Ich habe eine relativ große Matrix (90-Zeilen, 60-Spalten). Ich möchte, dass Excel in jeder Spalte (ab der 3. Spalte) den von oben gesehen ersten negativen Wert sucht, dann wieder eine Zeile nach oben hüpft und mir dann den Wert aus der 1. Spalte in einer anderen Zelle anzeigt. So habe ich am Ende in allen 60 Zellen über meinen 60 Spalten einen Wert stehen.
Zum Nachvollziehen des Problems habe ich mal eine kleinere Matrix gebastelt.
https://www.herber.de/bbs/user/148513.xlsx
Die orange hinterlegten Zahlen sollen mittels einer Funktion automatisch ausgegeben werden. Beispiel für Spalte "Hund": Suche in der Spalte B von Zeile 6 an den ersten negativen Wert, gehe dann wieder eine Zeile nach oben und geben dann den entsprechenden Wert aus der Spalte A in der Zelle B3 aus.
Mit dem S- bzw. W-Verweis komme ich hier leider nicht weiter.
Ich freue mich auf Eure Hilfe, vielen Dank!

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. mit AGGREGAT() ...
08.10.2021 16:53:35
neopa
Hallo Anna,
... in B3: =WENNFEHLER(AGGREGAT(15;6;$A5:$A15/(B6:B15&lt0);1);"") und nach rechts ziehend kopieren.
Gruß Werner
.. , - ...
AW: noch eine Alternative mit VERWEIS() ...
09.10.2021 09:12:47
neopa
Hallo Anna,
... für den Fall, dass in jeder Spalte ein negativer Wert vorkommt in B3: =VERWEIS(9;1/(B6:B16&gt=0);$A6:$A16) und nach rechts kopieren.
Für den Fall dass in einer Spalte kein negativer Wert vorkommt, hatte ich die AGGREGAT()-Formel mit WENNFORMEL() geklammert, welches Du da auch weglassen kannst, wenn immer ein negativer Wert vorkommt.
Meine beiden Formeln haben den Vorteil, dass in A6:A15 beliebige Zahlenwerte stehen können, die dann als Ergebnis ermittelt werden.
Gruß Werner
.. , - ...
Anzeige
AW: wobei hierzu noch anzumerken ist ...
09.10.2021 09:35:47
neopa
Hallo,
... dass meine letzte Aussage im Beitrag "dass in A6:A15 beliebige Zahlenwerte stehen können" genau genommen nur für die VERWEIS()-Formel gilt.
Bei der AGGREGAT()-Formel gilt das nur beschränkt, nämlich dann wenn diese beliebigen Werte aufwärts sortiert in Spalte A stehen.
Gruß Werner
.. , - ...
{=INDEX(A:A;VERGLEICH(WAHR;B6:B15<0;0)+4)}
09.10.2021 10:19:20
WF
Deine Verweisformel ergibt 0 (null) ?
Das Aggregatzeug solltest Du vergessen.
WF
AW: Korrektur meines Schreibfehlers ...
09.10.2021 12:10:31
neopa
Hallo,
... die VERWEIS()-Formel sollte natürlich korrekt: =VERWEIS(9;1/(B5:B15&gt=0);$A5:$A15) geschrieben sein.
Geschrieben hatte ich ja schon: "... für den Fall, dass in jeder Spalte ein negativer Wert vorkommt "
Gruß Werner
.. , - ...
Anzeige
sollte man also nicht verwenden
09.10.2021 20:14:23
WF
wie Du selber schreibst, gibt's Müll, wenn eine Spalte keine Negativzahl enthält.
Sie redet von 60 Spalten - das sind 60 Fehlerquellen.
AW: typisch ...
10.10.2021 10:47:47
neopa
Hallo WF,
... dass Du für Deine Lösungsansätze öfters Annahmen triffst, ähnliches aber anderen insbesondere mir nicht zugestehen willst
Für die von Anna gestellte Fragestellung hatte ich eine den gegebenen Bedingung entsprechende völlig korrekte Formellösung aufgestellt, die ich Deiner Meinung nach "vergessen" soll, nur weil Du eine mE unbegründete Aversion gegenüber AGGREGAT() hast. Danach hat Du zwei alternative Lösungsansätze mit zum einen getroffener Annahmen eingestellt.
Mein danach zusätzlich mit einer Annahme eingestellten alternativen VERWEIS()-Lösungsansatz für evtl. andere Datenwerte in A6:A15 tust Du nun einfach als "Müll" ab, obwohl Du erstens selber die Annahme getroffen hast, dass wenn Negativwerte vorliegen Anna Fehlerwerte angezeigt bekommen soll und zweitens Du auch nicht wissen kannst, ob Anna anstelle dessen nicht doch den Datenwert aus A15 ausgewiesen haben möchte, wie die VERWEIS()-Formel ergibt.
Warten wir doch ab, welche Bedingungen bzw. welche Ergebnisse dafür dann gelten sollen.
Gruß Werner
.. , - ...
Anzeige
AW: z.B. mit AGGREGAT() ...
12.10.2021 12:38:24
Anna
Vielen Dank, Werner! Deine Formel funktioniert einwandfrei!
Leider ist meine Tabelle jetzt noch etwas komplizierter geworden:
1. Es gibt auch Spalten, die keinen negativen Wert enthalten. In diesem Fall soll einfach beim letzten Spaltenwert in Zeile 15 der Wert aus A15 angegeben werden.
2. Es gibt auch Spalten, die mit negativen Werten anfangen. Die Zellen in Zeile 3 sollen aber trotzdem nur alle Zellen ausgeben, die positiv sind. D.h. z.B. für die Spalte F (Tiger): Gehe so lange nach unten bis der Wert positiv ist (F8). Dann gehe so lange nach unten bis der Wert negativ ist (F10). Springe dann in Spalte A und rechne Zeile 10 (erster positiver Wert) minus Zeile 3 (erster negativer Wert) und zeige das Ergebnis in Zelle F3 an (hier 7).
Ich freue mich über jeden hilfreichen Kommentar!
Gruß Anna
P.S. Der erste negative Wert ist nicht immer -1.
Anzeige
AW: zu Deiner 2. Zusatzsfrage ...
14.10.2021 14:43:35
neopa
Hallo Anna,
... in Deinen Angaben dazu liegen mE Widersprüche vor, deren Aufklärung durch eine neue Datei Deinerseits gelöst werden kann.
Zeige mal in einer neuen Datei an an ein bis zwei Beispielen konkret, welches Ergebnis/se Du ermittelt haben willst.
Gruß Werner
.. , - ...
AW: bezogen auf Deine neue Beispieldatei ...
15.10.2021 12:50:30
neopa
Hallo Anna,
... und Deine Ergebnisvorgabe reicht einfach: =ZÄHLENWENN(B6:B15;"&gt=0") und nach rechts ziehend kopieren. In dem konkreten Fall ist es egal was in A6:A15 steht. Wenn aber da andere Werte stehen können, auf die Du Dich beziehen willst, solltest Du angeben, was dann genau ermittelt werden soll.
Gruß Werner
.. , - ...
Anzeige
AW: bezogen auf Deine neue Beispieldatei ...
15.10.2021 14:43:05
Anna
Vielen Dank! :)
AW: gerne owT
15.10.2021 15:18:39
neopa
Gruß Werner
.. , - ...
AW: gerne owT
21.10.2021 20:26:09
Anna
Und wieder einmal ist meine Tabelle etwas komplexer geworden: In Spalte A lauten die Zahlen nun nicht mehr 1,2,3..., sondern 10, 20, 30, 40, 60, 80 (die Zahlen sind aufsteigend sortiert, haben aber unterschiedliche Intervalle). Wie kann ich meine Formel verändern, damit die richtigen Ergebnisse herauskommen? Hier meine neue Beispieldatei: https://www.herber.de/bbs/user/148738.xlsx. Vielen Dank schonmal für jegliche Hilfe! :)
in B3: {=VERGLEICH(WAHR;B6:B15<0;0)-1}
08.10.2021 19:51:18
WF
Eingabe als ARRAYFORMEL
ohne { }
=VERGLEICH(WAHR;INDEX(B6:B15 in Deinem Beispiel ist die erste negative Zahl immer -1 > da langt:
=VERGLEICH(-1;B6:B15;0)-1
WF
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige