Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.05.2024 23:36:08
IFNA Funktion in WENNNV übertragen
16.05.2024 19:31:55
Zellenfarbe beim.Filtern ändern
16.05.2024 19:29:08
Mit INDIREKT() auf andere Tabellenblätter referenzieren
16.05.2024 19:06:22
Monatssumme / Zahlweise / Kalenderjahr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1964to1968
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1964to1968
1964to1968
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suche mit berechnetem (relativem) Bezug einschränken

Suche mit berechnetem (relativem) Bezug einschränken
23.02.2024 09:10:20
Jürgen
Guten Morgen,

ich habe einen monatlichen Finanzplan von Jänner 22 (F1) bis Dezember 27 (BY1)
In der Zeile 119 wird die Summe der einzelnen Positionen des jeweiligen Monats gebildet, die den jeweiligen Kontostand im Monat darstellt.
In Zelle B121 kann ich mit Hilfe von B121=KKLEINSTE(F119:BY119;1) den niedrigsten Kontostand identifizieren.

Nun zu meiner Frage:
Wie kann ich in der Formel B121=KKLEINSTE(F119:BY119;1) die Suche auf den Bereich aktuelles Monat (also im Feb 24 AE119) bis Dez 27 (BY119) einschränken?
(ich muss eine Aussage über Gegenwart und Zukunft treffen, ob/wann im Lauf der Zeit eine Kontounterdeckung besteht)

Händisch eingegeben würde die Formel im
Feb. 24 also B121=KKLEINSTE(AE119:BY119;1)
Mrz. 24 also B121=KKLEINSTE(AF119:BY119;1)
usw., lauten.

Ich hab mit INDIREKT und Co. experimentiert, bin aber gescheitert.

Hat jemand eine Idee wie man das lösen kann? (Excel bevorzugt, da ich ehrlich gesagt in VBA ein ziemlicher Nichtswisser bin)

Besten Dank und Liebe Grüße
Jürgen

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Suche mit berechnetem (relativem) Bezug einschränken
23.02.2024 09:28:38
daniel
Hi
im Prinzip so:

=KKleinste(Index(F119:BY119;Vergleich(A1;F$1:BY$1;0)):BY119;1)

wobei du in A1 das Startdatum eintragen musst, in der gleichen Art und weise, wie die Überschrift in den Zellen F1:BY1 steht.

Gruß Daniel
AW: Suche mit berechnetem (relativem) Bezug einschränken
23.02.2024 10:25:16
Jürgen
Hi Daniel, danke für Deine rasche Antwort - aber so ganz löst das leider mein Problem nicht.

Wahrscheinlich habe ich es nicht gut genug ausgedrückt, hab jetzt eine vereinfachte Bsp-Datei hochgeladen und hoffe dass mein Wunsche nach "Automatisierung" somit klarer wird.

LG Jürgen
AW: Suche mit berechnetem (relativem) Bezug einschränken
23.02.2024 10:52:54
daniel
doch, das passt schon.
wenn du den Vergleich mit 0 als drittem Parameter schreibst, musst du das Datum so schreiben, wie in der Datumszeile 1, also immer als 1.1 des jeweiligen Monats (bitte nicht von der Formatierung täuschen lassen, es zählt der unformatierte wert.

also entweder das Datum für die Ausgabe so berechnen:
=Monatsende(Heute();-1)+1 oder =Datum(Jahr(Heuter());Monat(Heute());1)

oder du verwendest den Vergleich mit drittem Paramater = 1:

also : Vergleich(Heute();Datumsbereich;1)

damit sollte es funktionieren.
Anzeige
AW: mit einer anderen Funktion möglich ...
23.02.2024 09:32:01
neopa C
Hallo Jürgen,

... in meiner älteren XL-Version würde ich es mit AGGREGAT() lösen in Deiner gibt es noch andere Möglichkeiten. Konkreter aber nur, wenn Du eine Beispieldatei einstellst.


Gruß Werner
.. , - ...
AW: mit einer anderen Funktion möglich ...
23.02.2024 10:28:39
Jürgen
Hallo Werner,

danke für Dein rasches Feedback. Vereinfachte Bsp-Datei ist hochgeladen.
Bin gespannt auf Deine Lösung.

LG Jürgen
AW: Suche mit berechnetem (relativem) Bezug einschränken
23.02.2024 10:18:46
Jürgen B. aus W
Hi, mein Fehler.

Habe nun eine (vereinfachte) Beispieldatei hochgeladen.
https://www.herber.de/bbs/user/167262.xlsx

Daher ändern sich die Zellen in meiner Frage wie folgt:

ich habe einen monatlichen Finanzplan von Jänner 22 (D1) bis Dezember 27 (BW1)
In der Zeile 4 wird die Summe der einzelnen Positionen des jeweiligen Monats gebildet, die den jeweiligen Kontostand im Monat darstellt.
In Zelle B7 kann ich mit Hilfe von B7=KKLEINSTE(D4:BW4;1) den niedrigsten Kontostand identifizieren.

Nun zu meiner Frage:
Wie kann ich in der Formel B7=KKLEINSTE(D4:BW4;1) die Suche auf den Bereich aktuelles Monat (also im Feb 24 AC1) bis Dez 27 (BW1) einschränken?
(ich muss eine Aussage über Gegenwart und Zukunft treffen, ob/wann im Lauf der Zeit eine Kontounterdeckung besteht)

Händisch eingegeben würde die Formel im
Feb. 24 also B7=KKLEINSTE(AC4:BW4;1)
Mrz. 24 also B7=KKLEINSTE(AD4:BW4;1)
Okt. 25 also B7=KKLEINSTE(AW4:BW4;1)
usw., lauten.

Hoffe das hilft Euch dabei mir zu helfen - DANKE!
Anzeige
AW: für den aktuellen Monat z.B. wie folgt ...
23.02.2024 10:29:37
neopa C
Hallo Jürgen,.

... so: =AGGREGAT(15;6;D4:BW4/(TEXT(D1:BW1;"MM")=TEXT(HEUTE();"MM"));1)

Gruß Werner
.. , - ...
Guter Ansatz - noch nicht ganz im Ziel...
23.02.2024 11:03:28
Jürgen
Hallo Werner,

vielen Dank - allerdings ergibt die Lösung bei mir nun den niedrigsten Februar-Wert (Februar 2026) und nicht den niedrigsten Wert aller Monate (Juli 25) in der Zukunft.

Ich habe in der Formel nun MM gegen JJ und = gegen >= ersetzt, dann stimmt es für die Beispieldatei.
=AGGREGAT(15;6;D4:BW4/(TEXT(D1:BW1;"JJ")>=TEXT(HEUTE();"JJ"));1)

Prima Lösungsansatz also von Dir, nochmals danke dafür - aber:
In der beiliegenden Beispieldatei 2 (https://www.herber.de/bbs/user/167263.xlsx) ist der Jänner 2024 nun der schlechteste Monat, aber eben nicht mehr relevant weil schon in der Vergangenheit.
Hier zeigt mir die Formel mit dem Jahr als Kriterium nun aber eben diesen Jänner an, weil sie nur auf das Jahr schaut und nicht auf das "ganze Datum".

Wie/wo muss ich die UND-Verknüpfung einbauen, dass bei der Abfrage sowohl (TEXT(D1:BW1;"JJ")>=TEXT(HEUTE();"JJ") als auch (TEXT(D1:BW1;"MM")>=TEXT(HEUTE();"MM") gilt? Meine Versuche ergeben immer nur #ZAHL als Ergebnis...

Besten Dank im Voraus für weiteren Input.
Anzeige
AW: nein, es war meinerseits falsch ...
23.02.2024 12:13:18
neopa C
Hallo Jürgen,

... ich hatte nicht richtig gelesen. Hatte dies dann auch dann auch selbst gemerkt, allerdings meine Korrektur im falschen thread eingestellt und zwar im thread hier: https://www.herber.de/forum/archiv/1964to1968/1966477_Berechnetes_Enddatum_ggf_minus_Anzahl_P_am_Freitag.html in meinem Beitrag um 10:51. Sorry.

Damit sollte das von Dir gewünschte ermittelt werden. Mit der hier im thread zuvor eingestellten Formel wird der niedrigste Wert des jeweiligen (Heute-)Monats aller Jahre ermittelt.

Gruß Werner
.. , - ...
Anzeige
You made my day!
23.02.2024 12:36:03
Jürgen
Werner - Du bist mein Held!

Danke, Dein Vorschlag ist prima.

Hab den nun noch mit einer Hilfszelle und dort der Formel von "Franz (aus dem Salzkammergut" ergänzt
E126=MIN(DATUM(JAHR(HEUTE());MONAT(HEUTE())-1+{0.1}+(TAG(HEUTE()+1)=1);{1.0}*TAG(HEUTE())*(TAG(HEUTE()+1)>1)))
damit ich auch das jeweils aktuelle Monat noch in der Abfrage drinnen habe.

Im Endeffekt schaut die Formel nun so aus
=AGGREGAT(15;6;F119:BW119/(F12:BY12>=E126);1)
wobei in Zeile 119 die Summen, in Zeile 12 das Datum und E126 wie oben geschrieben die Hilfszelle ist.


Vielen Dank an diese Community, die mir schon oft weitergeholfen hat und special thanks to Werner aka neopa C.
Anzeige
AW: bitteschön owT
23.02.2024 13:32:15
neopa C
Gruß Werner
.. , - ...
AW: hierzu noch nachgefragt ....
23.02.2024 14:03:45
neopa C
Hallo Jürgen,

.. die Formel von Franz (FP) hast Du woher? Wenn Du den aktuellen Monat in die Ermittlung mit einbezogen haben willst, reicht hier anstelle der Berechnung in der Hilfszelle E126 doch auch einfach ohne diese: =HEUTE()-TAG(HEUTE()) also gesamt so: =AGGREGAT(15;6;F119:BW119/(F12:BY12>HEUTE()-TAG(HEUTE()));1)

Gruß Werner
.. , - ...
AW: hierzu noch nachgefragt ....
23.02.2024 14:17:10
Jürgen
Hallo Werner,

die Lösung kommt auch diesem Forum:
https://www.herber.de/forum/archiv/852to856/852413_Datum_Minus_1_Monat.html

Die Komplexität der Formel soll auch die diversen schwierigen Monatssprünge (31->30, 29, 28) fehlerfrei berücksichtigen

Deine Formel habe ich jetzt ausprobiert und sie scheint mir eigentlich logisch, einfach und funktioniert.
Also nochmals danke!
Anzeige

Links zu Excel-Dialogen

Frame

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige