Office: Excel 365 2 Formeln zusammenführen

Hallo Zusammen,
ich bitte um Eure Hilfe.

Ich habe Spalten der Jahre 1987 bis 2019
Für diese Jahre gibt es verschiedene Sollwerte (Durchschnittswerte) Für 2019 = 11.000
1. Formel:
In einer Hilfsspalte ermittle ich zeilenweise den Durchschnitt (Sollwert).
Die Formeln (=Summe(C4:C4/365)) bis (=Summe(C4:C365/365)) zeigen mir in welcher Zeile der Wert (im Jahr 2019 = 11.000) erreicht bzw. überschritten wird.
2. Formel:
=VERGLEICH(10999;R:R;1)+(ZÄHLENWENN(R:R;10999)=0)
Mit dieser zweiten Formel ermittle ich die Zeilen-Nr. in der Hilfsspalte und damit das Datum, in der Wert überschritten wird.
Das funktioniert auch gut.
Um nicht für all diese Jahre nun Hilfsspalten einfügen zu müssen, suchte ich eine Formel, die die beiden Einzelformeln zusammenfasst. Das ist mir leider nicht gelungen.
Könnt Ihr mir helfen?
Danke.
kn

:)

 

Zu Deiner Datei kann ich nichts schreiben, was wohl daran liegt das ich nicht auf fremde Rechner schaue. Ich baue keine Datei nach.

Sollte die Datei verlinkt werden?

Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Muster*, Test*, Mappe*, Beispiel*, Fehler*, Kalender*, UserForm* usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Es sollte ein aussagekräftiger Name sein.

Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/...d.php?t=322895
änderrn.

Das ist nur meine Meinung zu dem Thema.


Bitte berücksichtige das Themen die Erledigt sind als Erledigt markiert werden können. Also mache es unten links mit Klick auf den Schalter "als erledigt setzen", falls Problem gelöst.
Der Zustand des Beitrages wird dann in der Übersicht angezeigt und man braucht sich diese Beiträge nicht mehr ansehen.





Meine Homepage Homeseite - Hajo's Excelseiten

 

Hallo Hajo,
anbei habe ich die die Original Daten entkoppelt für das Jahr 2019 beigefügt.
Gruss
Ich hoffe, Du kannst mir helfen.
kn

 

Deine Datei hat einen entscheidenen Nachteil es steht nirgends das Datum als Zahl.

 

Hallo kn,

das kann evtl. mit einer Matrixformel gehen. Aber mit Matrixformeln kenne ich mich kaum aus. Weiterhin geht dies mit einem Makro und vermutlich würde ich es mit Power Query hinbekommen. Dann ist aber das Ergebnis immer eine separate Tabelle. Diese ist dann aber einzeilig und kjönnte somit über an den Beginn deiner Tabelle plaziert werden.

Matrixformel haben übrigens die Eigenschaft, sehr schnell she träge zu werden.

 

Moin,
ich habe es mit dem Jahr 2019 (nur für mich selbst) ausprobiert und bin mit Power Query zum Ergebnis gekommen, dass am 12.11. der SOLL-Wert mit dem Mittelwert 11.014,59 überschritten worden ist. Du siehst: es lohnt sich, neue Wege zu erkunden.

Bei der Gelegenheit hatte ich dann auch die Anzahl der realen Tage im Jahr berechnen lassen, schließlich kennt das Jahr 2019 im Gegensatz zu 2020 keinen 29. Februar *wink.gif* . Ich räume aber ein, dass das nicht mit ein paar Mausklicks machbar ist und ich brauchte auch etwas Recherche, um die kumulierten Summen bzw. deren Mittelwert festzustellen; aber es lohnt sich, immer tiefer in die Materie einzusteigen … *cool.gif*

Je nach Aufbau der gesamten Daten ist das in toto gewiss auch mit Power Query machbar. Versuch macht kluch! *grins

 

Hallo Zusammen,
Hajo, die Tabelle ist vorgegeben, ich finde das mit dem Datum auch nicht glücklich gelöst. Ich brauche aber nur die Zeile, in der Wert erreicht wird. Aus der Zeile das Datum zu ermitteln ist dann wieder relativ einfach.
WS-53: Ich kenne mich ein wenig mit Matrix Formeln aus, aber es ist mir nicht gelungen, eine Formel zu "stricken", die ein korrektes Ergebnis liefert.
Drogist: Mit Power Query kenne ich mich leider nicht aus.
Ich ahne es langsam, das scheint komplizierter zu sein als ich es vermutet habe.
Das Problem mit dem neuen Excel ist auch, dass viele der schönen neuen Formeln in alten Excel Systemen nicht zur Verfügung stehen.
Vielleicht hat jemand doch noch einen guten Ansatz?
Danke
kn

 

Hallo,

anbei mal eine VBA-Lösung; den 29.2. habe ich entfernt.

Gruß
Aloys

 

Hallo,

da du in der Formel eine Mischung aus Array und normaler Formel bräuchtest, würde ich das über eine kleine UDF machen ...

Den Code in ein allgemeines Modul.
Code:

In der Tabelle kannst du dann als Formel schreiben (z.B. oberhalb der Jahreszahl):
Formel in C1: =VERGLEICHSZAHL(B4:C369;11000)
Das gibt dir in deinem Fall "12.11." aus.

Sabina

 

Hallo,

ich habe es nun einmal für 5 Jahre mit Power Query realisiert.

 

Hallo Zusammen,
vielen Dank für die beiden Lösungen.
Aloys, da ich die Jahre von 1978 bis jetzt auswerten soll und da auch einige Schaltjahre enthalten sind, kann auf die Schaltjahre leider nicht verzichten.
Sabina, Deine Lösung gefällt mir sehr gut, da sie sich auf eine Zeile beschränkt. Frage 1: wie sieht es in Deiner Formel mit den Schaltjahren aus?
Frage 2: Ich habe nun zwei Jahre (2017 & 2016) entdeckt, da wurde der vorgegebene Durchschnittswert nicht erreicht. Kann das zu einem Problem führen?
Vielen Dank, Ihr habt beide eine sehr gute Lösung gefunden.
kn

 

Hallo Sabina,
könntest Du das Funktionsmakro bitte so verändern, dass die Zeile ausgegeben wird und nicht das Datum? Das nächste Jahr steht in Spalte F, da funktioniert das Makro leider nicht mehr und auch nicht in den weiteren Spalten I, L, O etc.
Danke
kn

 

Hallo kn,

Das ist mir auch klar und auch leicht zu realisieren
Du brauchst nur mal aufzeigen, wie weitere Jahre dargestellt werden.

Gruß
Aloys

 

Hallo Aloys,
die Jahre stehen in den Spalten C, F, I, L, O, T usw..
Der 29. Feb. ist immer vorhanden (wie in meinem Beispiel), er wird in den Jahren ohne Schaltjahr auf Null gesetzt.
Wie gesagt, ich brauche nur die Zeilen-Nr., in der der jeweilige Wert erreicht wird. Ein Bezug auf das Jahr ist eigentlich nicht notwendig?
Es gibt nur eine Variable i der der jeweiligen Spalte und das ist der vorgegebene Wert, der über die Jahre unterschiedlich vorgegeben wurde.
Wenn ich die Zeilen-Nr. habe werte ich das Datum in einer separaten Tabelle aus und zeige in einem Diagramm die Schwankungen an.
Mein Anliegen war, auf die Hilfsspalte zu verzichten. Sie ist nur extrem aufwendig zu realisieren.
Ideal wäre ein Makro oder eine Formel, die man am Ende der Spalten (Zeile 376 z.B.) einsetzt, um die Zeilen-Nr. zu erhalten.
Danke für Deine Mühe!!
kn

 

Hallo,

Müßte es nicht "R" statt "T" heißen oder haben die Spalten unterschiedlichen Abstand ?
Wieviel Spalten gibt es (Größenordnung) ?

Was heißt das konkret ?
Gibt es nur einen Sollwert für alle Jahresspalten, und wenn ja, wo steht er ?
Andernfalls - wo stehen die Jahres-spezifischen Sollwerte ?

Gruß
Aloys