10
Excel Werte suchen und ausgeben Verschachtelte Formeln
Hallo.
Ich habe eine recht große Excel Tabelle. Auf der rechten Seite soll nun eine Tabelle entstehen, deren Werte sich auf alles bezieht, was in Spalte C <-10 ist.
Folgendes soll in Spalte F Passieren: Ist w (Spalte A) > 0,1 und T/W (Spalte C)<-10, so suche in den 10 vorherigen Zeilen von Spalte B nach dem maximalen Wert und gib diesen aus.
Spalten E und G sollen dann den zugehörigen Wert zu Spalte F angeben. Außerdem möchte ich die rechte Tabelle gerne ohne Leerzeilen haben.
Ich habe mich mit den Funktionen SVERWEIS, WENN und WENNFEHLER schon so verrannt, dass ich auf keine anständige Ausgabe komme.
Vielen Dank für die Hilfe.
Ich habe eine recht große Excel Tabelle. Auf der rechten Seite soll nun eine Tabelle entstehen, deren Werte sich auf alles bezieht, was in Spalte C <-10 ist.
Folgendes soll in Spalte F Passieren: Ist w (Spalte A) > 0,1 und T/W (Spalte C)<-10, so suche in den 10 vorherigen Zeilen von Spalte B nach dem maximalen Wert und gib diesen aus.
Spalten E und G sollen dann den zugehörigen Wert zu Spalte F angeben. Außerdem möchte ich die rechte Tabelle gerne ohne Leerzeilen haben.
Ich habe mich mit den Funktionen SVERWEIS, WENN und WENNFEHLER schon so verrannt, dass ich auf keine anständige Ausgabe komme.
Vielen Dank für die Hilfe.
Auf Facebook teilen
Auf X (Twitter) teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 566228
Url: https://administrator.de/forum/excel-werte-suchen-und-ausgeben-verschachtelte-formeln-566228.html
Ausgedruckt am: 23.04.2025 um 06:04 Uhr
10 Kommentare
Neuester Kommentar
Mach dir Hilfsspalten und teile die Aufgabe in einzelne einfache Formeln.
Die erste Hilfsspalte z.B. U wäre dann z-B. Wenn Spalte A größer als 0.1, dann eine 1 als OK anzeigen.
Zweite Hilfsspalte V: Wenn C kleiner 10, dann eine 1 als OK anzeigen.
Dritte Hilfsspalte W: Wenn U+V =2, dann eine 1 als OK anzeigen
Vierte Spalte X: Wenn W=1, dann Ergebnis = Summe von Zeile()-1 bis Zeile()-10
Die erste Hilfsspalte z.B. U wäre dann z-B. Wenn Spalte A größer als 0.1, dann eine 1 als OK anzeigen.
Zweite Hilfsspalte V: Wenn C kleiner 10, dann eine 1 als OK anzeigen.
Dritte Hilfsspalte W: Wenn U+V =2, dann eine 1 als OK anzeigen
Vierte Spalte X: Wenn W=1, dann Ergebnis = Summe von Zeile()-1 bis Zeile()-10
Moin,
Dein Freund heißt BEREICH.VERSCHIEBEN
Das funktioniert aber erst ab der 12ten Zeile, weil es sonst im Wahrzweig eine hässliche Fehlermeldung gibt, da die Zellen unterhalb von Zeile 1 ja nicht exisitieren. Für die Zeilen davor wäre dann das zweite Argument von BEREICH.VERSCHIEBEN entsprechend anzupassen.
Wenn die Tabelle wirklich groß ist, dann kann das ein wenig dauern. Die Funktion ist nicht gerade perfomant.
hth
Erik
Dein Freund heißt BEREICH.VERSCHIEBEN
=WENN(UND(A12>0,1;C12<-10);MAX(BEREICH.VERSCHIEBEN(C12;-10;1;10;1));"") Das funktioniert aber erst ab der 12ten Zeile, weil es sonst im Wahrzweig eine hässliche Fehlermeldung gibt, da die Zellen unterhalb von Zeile 1 ja nicht exisitieren. Für die Zeilen davor wäre dann das zweite Argument von BEREICH.VERSCHIEBEN entsprechend anzupassen.
Wenn die Tabelle wirklich groß ist, dann kann das ein wenig dauern. Die Funktion ist nicht gerade perfomant.
hth
Erik
So gehe ich da gerade dran. Ersteinmal an einer kleineren "Übungstabelle", die die entsprechenden Eigenschaften aufweist.
Spalte E: =WENN(A2>0,1;A2;"X") (Ausgabe des Wertes falls es zutrifft und X falls nicht.)
Spalte F: =WENN(A2>0,1;WENN(C2<=-10;C2;"Y");"X") (Mit X oder Y, so sehe ich direkt ob das erste oder zweite Kriterium nicht erfüllt ist)
Spalte G: =WENN(A13>0,1;WENN(C13<=-10;MAX(B3:B12);"Y");"X") das Max aus den vorherigen Zeilen, falls die Kriterien zutreffen. Ansonsten wieder X bzw Y je nach nicht erfülltem Kriterium.
Jetzt habe ich noch 2 Probleme:
1. So funktioniert das erst ab Zeile 10, es soll aber in der ganzen Tabelle funtionieren. Dh. stehen keine 10 Werte für das Maximum zur verfügung soll er die nehmen die da sind auch wenn es weniger als 10 sind.
2. Am Ende möchte ich eine Tabelle ohne X oder Y haben. Es sollen nur die Werte auftauchen, die die Kriterien erfüllen. Dh. in diesem Fall wäre die Tabelle nur 5 Werte groß.
Und anschließend soll Excel mir noch zu den gefundenen Werten in Spalte G den zugehörigen Wert aus der linken Tabelle angeben.
Vor Zeile 12 funktioniert der Bezug nicht:
Spalte E: =WENN(A2>0,1;A2;"X") (Ausgabe des Wertes falls es zutrifft und X falls nicht.)
Spalte F: =WENN(A2>0,1;WENN(C2<=-10;C2;"Y");"X") (Mit X oder Y, so sehe ich direkt ob das erste oder zweite Kriterium nicht erfüllt ist)
Spalte G: =WENN(A13>0,1;WENN(C13<=-10;MAX(B3:B12);"Y");"X") das Max aus den vorherigen Zeilen, falls die Kriterien zutreffen. Ansonsten wieder X bzw Y je nach nicht erfülltem Kriterium.
Jetzt habe ich noch 2 Probleme:
1. So funktioniert das erst ab Zeile 10, es soll aber in der ganzen Tabelle funtionieren. Dh. stehen keine 10 Werte für das Maximum zur verfügung soll er die nehmen die da sind auch wenn es weniger als 10 sind.
2. Am Ende möchte ich eine Tabelle ohne X oder Y haben. Es sollen nur die Werte auftauchen, die die Kriterien erfüllen. Dh. in diesem Fall wäre die Tabelle nur 5 Werte groß.
Und anschließend soll Excel mir noch zu den gefundenen Werten in Spalte G den zugehörigen Wert aus der linken Tabelle angeben.
Vor Zeile 12 funktioniert der Bezug nicht:
Moin nochmal,
Ja klar, weil es Zeile -1 nicht gibt. Hier eine angepasste Version meiner Lösung:
hth
Erik
Zitat von @KarstenM:
1. So funktioniert das erst ab Zeile 10, es soll aber in der ganzen Tabelle funtionieren. Dh. stehen keine 10 Werte für das Maximum zur verfügung soll er die nehmen die da sind auch wenn es weniger als 10 sind.
1. So funktioniert das erst ab Zeile 10, es soll aber in der ganzen Tabelle funtionieren. Dh. stehen keine 10 Werte für das Maximum zur verfügung soll er die nehmen die da sind auch wenn es weniger als 10 sind.
Ja klar, weil es Zeile -1 nicht gibt.
Hier eine angepasste Version meiner Lösung:=WENN(UND(A2>0,1;C2<-10);MAX(BEREICH.VERSCHIEBEN(C2;WENN(ZEILE()<=12;-(ZEILE()-2);-10);1;WENN(ZEILE()<=12;ZEILE()-2;10);1));"") hth
Erik
Vielen Dank, erikro. Leider funktioniert das nicht...
Fehlermeldung oder Fehlerbild?
Bei deinem 2. Vorschlag gibt es keine Fehlermeldung. Die ausgegebenen Werte stimmen einfach nicht. Aber dein Hinweis zu Bereich.verschieben hilft sehr. Es hat sich ergeben, dass die ersten 10 Zeilen sowieso nicht relevant sind. Daher gehts weiter mit deinem ersten Vorschlag.
Also der Befehl an sich funktioniert gut, nur bleibt das Problem der Leeren Zellen. Da die gesamten Spalten durchsucht werden müssen habe ich die entsprechendes Einträge durch bspw. $A:$A ersetzt.
Das Ziel wäre, dass Excel die Spalten durchsucht und dann nur die Ergebnisse zusammengefasst heraus gibt. Ich möchte nicht den Befehl über 370 Zeilen "runterziehen" müssen. Das wäre nicht zweckmäßig.
Also der Befehl an sich funktioniert gut, nur bleibt das Problem der Leeren Zellen. Da die gesamten Spalten durchsucht werden müssen habe ich die entsprechendes Einträge durch bspw. $A:$A ersetzt.
Das Ziel wäre, dass Excel die Spalten durchsucht und dann nur die Ergebnisse zusammengefasst heraus gibt. Ich möchte nicht den Befehl über 370 Zeilen "runterziehen" müssen. Das wäre nicht zweckmäßig.
Moin,
das Zipfelchen der Formel, das ist sehe, scheint mir falsch. Poste mal die gesamte Formel. Das mit dem UND($A:$A>0,1) wird nichts.
Und was spricht dagegen, das nach unten auszufüllen? Strg-Umsch-PfeilUnten, Strg-U ist doch schnell getippt.
Oder Du formatierst das Ganze als Tabelle. Dann wird bei jedem neuen Eintrag automatisch die Formel mit übertragen und die Bezüge kann man als Spaltenbezüge formulieren. Das geht dann auch nicht aus Versehen durcheinander.
Liebe Grüße
Erik
das Zipfelchen der Formel, das ist sehe, scheint mir falsch. Poste mal die gesamte Formel. Das mit dem UND($A:$A>0,1) wird nichts.
Und was spricht dagegen, das nach unten auszufüllen? Strg-Umsch-PfeilUnten, Strg-U ist doch schnell getippt.
Oder Du formatierst das Ganze als Tabelle. Dann wird bei jedem neuen Eintrag automatisch die Formel mit übertragen und die Bezüge kann man als Spaltenbezüge formulieren. Das geht dann auch nicht aus Versehen durcheinander.
Liebe Grüße
Erik
Das ist die ganze Formel und die Befehle $A:$A>0,1 und $C:$C<=-10 funtionieren doch gut. Das Ergebnis für T ist jedenfalls richtig.
Da hast du natürlich auch wieder recht... ändert nur nichts daran, dass die Einträge nicht direkt untereinander stehen.
Und was spricht dagegen, das nach unten auszufüllen? Strg-Umsch-PfeilUnten, Strg-U ist doch schnell getippt.
Da hast du natürlich auch wieder recht... ändert nur nichts daran, dass die Einträge nicht direkt untereinander stehen.
Zitat von @KarstenM:
Das ist die ganze Formel und die Befehle $A:$A>0,1 und $C:$C<=-10 funtionieren doch gut. Das Ergebnis für T ist jedenfalls richtig.
Das ist die ganze Formel und die Befehle $A:$A>0,1 und $C:$C<=-10 funtionieren doch gut. Das Ergebnis für T ist jedenfalls richtig.
Echt? Das geht? Na dann.
Da hast du natürlich auch wieder recht... ändert nur nichts daran, dass die Einträge nicht direkt untereinander stehen.
Noch ein Grund, das als Tabelle zu formatieren. Dann kannst Du die Zeilen mit leeren Zellen wegfiltern.
