Excel Formel gesucht

[weisz]

Hallo zusammen,

ich bin auf der Suche nach einer Formel, bei der ich abhängig von einem Datum und einer Seetiefe einen Messwert erhalte.
Datum, Tiefe und Ausgabewert sind in Spalten nebeneinander angeordnet.
(Datum | Messtiefe | Messwert)
Ich habe bereits Filter angewendet, um z.B. Februar und -5m zu filtern.
Dann erhalte ich alle Messwerte über 40 Jahre untereinander.
Filter 1: Februar // Filter 2: -5m

(Februar ´60 | -5m | 297)
(Februar ´61 | -5m | 296)
(Februar ´62 | -5m | 293)
...

Es wäre aber natürlich eleganter wenn eine Formel den Job erledigt und die Daten, bzw. den Mittelwert der Messdaten über die vielen Jahre ausgibt. Ich habe unheimlich viele Daten auszuwerten und da wird das mit den Filtern zäh.
Mit SVerweis und Index habe ich ein bisschen was ausprobiert, bin aber nicht zum Ergebnis gekommen.

Vielen Dank für die Unterstützung.
Ich freue mich auch über kritische Meinungen dazu.

 

[steve1da]

Hola,

=summenprodukt((monat(A2:A500)=2)*(B2:B500=-5)*(C2:C500))/summenprodukt((monat(A2:A500)=2)*(B2:B500=-5))

Gruß,
steve1da

 

[weisz]

Vielen Dank!

Ich hab das Gefühl, da fehlt nicht viel.
Hab ich was falsches in der Formel übernommen?

 

[steve1da]

Hab ich was falsches in der Formel übernommen?

Ja. Du hast am Ende falsch geklammert.

 

[weisz]

Danke für die schnelle Antwort, aber das scheint nicht zu funktionieren. Mit der originalen Klammersetzung wird mir nur der Hinweis angezeigt, dass die Formel um eine Klammer ergänzt werden muss :/
Aber danke für deine Hilfe!

Ich bitte um Verzeihung.
Die Formel funktioniert bestens!
Ich hab meine Tabelle umgestellt, jetzt klappts.
Danke!

 

[weisz]

Ich muss mich wirklich herzlich bedanken @steve1da, die Formel hat mir sehr geholfen!
Ich konnte sie schon ein paar mal modifizieren, aber leider scheint es ein Problem bei Stunden und/oder Tagen zu geben.

Weiß jemand, wo der Fehler liegt?
(Bild im Anhang)

 

[steve1da]

Tag() gibt eine Zahl von 1-31 aus. Das vergleichst du mit einem Datum. Dass kann nicht klappen. Entweder Datum mit Datum vergleichen oder Tag mit Tag.
Bei der Uhrzeit ist es das gleiche Problem.

 

[weisz]

(Ein spätes) Danke für die Rückmeldung!

Eine Frage dazu:
Kann es sein, dass Excel, beim "Jahr" Probleme hat?
Ich habe das Datum, übernehme in die nächste Spalten = Jahr / = Monat / = Tag
Mit der Summenprodukt-Formel komme ich aber nicht ans Ergebnis und das liegt augenscheinlich an dem Abgleich mit dem Jahr.

Wenn ich das Datum als Jahresangabe verwende, erhalte ich zumindest ein Ergebnis, allerdings leider ein falsches (346 statt 270).


Grüße
weisz

 

[steve1da]

Bildchen helfen jetzt nicht mehr weiter weil hierauf nicht alle Daten ersichtlich sind und auch keine Formatierungen. Eine Excel-Beispieldatei wäre hilfreicher. Am besten mit dem Ergebnis was du erwartest, natürlich händisch eingegeben.
Edit: habs mir nochmal angeschaut. Du hast doch schon den Monat und den Tag aus dem Datum extrahiert. Dann darfst du da nicht nochmal mit Monat(C6) abfragen, ebenso beim Tag. Natürlich bekommst du falsche Ergebnisse.
Du solltest dir dringend anschauen, was ein Datum in Excel genau ist und was Monat(), Tag() genau machen.
Ein Datum ist eine fortlaufende Zahl in Excel, beginnend mit 1, dem 01.01.1900. Daraus holt sich Tag() und Monat() den richtigen Wert, aber eben aus dieser fortlaufenden Zahl. Jetzt hast du aus dem 01.01.1960 den Tag geholt, also 1. Und jetzt schreibst du quasi: =Monat(1). Also die Monatsangabe aus der laufenden Zahl 1 für Excel. Das wird immer der Januar sein, weil es halt der 01.01.1960 ist.

Also entweder:....monat(A2:A22461)=I6.... oder C2:C22461=I6.....

Kann es sein, dass Excel, beim "Jahr" Probleme hat?

Das "Jahr" ist hier übrigens das einzige was du richtig abgefragt hast ;-)

Frage von mir: in deinem jetzigen Beispiel sind deine Datumswerte einzigartig. Was soll da der Mittelwert sein als das Messergebnis selber?

Noch ein letzter Edit Wenn du das Jahr auch noch aus dem Datum extrahierst, reicht hier auch eine ganz normale Mittelwertwenns() Formel, bedeutend schnelle als Summenprodukt()/Summenprodukt()

 

[weisz]

Danke einmal mehr.

Dass das Datum in Excel eine laufende Zahl ist, war mir neu - macht aber Sinn wie Du es erklärst

Deine Antwort auf die Datumsfunktion bezogen verstehe ich.
Allerdings begreife ich nicht, woran es bei Bild 3 bzw. 4 hängt.

Zu dem Problem ist jetzt auch die gewünschte Test-Excel-Tabelle zusammengestellt.

Mit dem Mittelwert arbeite ich am ende in der Grafik, der ist da beim Zwischenzeilen löschen nur hängengeblieben.

 

[steve1da]

Sorry, aber ich schreib hier fast nen Roman und in der Datei ist immer noch alles falsch, sogar die vorher richtige Abfrage auf das Jahr ist jetzt falsch.
Versteh ich nicht.

 

[weisz]

Dann versuch ich nochmal, deinen Beitrag besser zu verstehen und mich besser zu erklären.
links steht das Datum, daneben sind Jahr, Monat und Tag aufgelistet.
Wenn ich dich richtig verstehe, ist in allen drei Feldern die gleiche Info, weil überall die gleiche laufende Zahl ausgeworfen wird.

Weil ich eine Aufschlüsslung nach Tag Monat und Jahr will (in der diese Information der laufenden Nummer nicht enthalten ist), habe ich versucht, aus dem Datum alle einzelnen Felder zu extrahieren, die ich für den Abgleich der Formel benötige.
So habe ich die Funktionsweise des Systems nach deiner ersten Antwort interpretiert.

Wenn ich nur mit dem Datum arbeite und das abgleiche, komme ich nicht aufs Ergebnis sondern mal wieder auf einen Fehler.

Aber die Erfahrung hier zeigt, dass ich einfach deinen Beitrag noch nicht verstanden hab.
Ich versuch noch die richtigen Schlüsse zu ziehen - danke auf jeden Fall!

 

[steve1da]

Wenn ich dich richtig verstehe, ist in allen drei Feldern die gleiche Info, weil überall die gleiche laufende Zahl ausgeworfen wird.
Tust du leider nicht. Du ziehst dir aus dem Datum das Jahr als Zahl, den Monat als Zahl und den Tag als Zahl.
Das vergleichst du ebenfalls mit Zahlen, beim Monat 1-12, beim Tag 1-31.
Ich versuch es mal anders zu erklären.
Schreib mal in die Zelle den 01.05.2022. Mit =Monat(A1) in Zelle A2 erhältst du 5, weil es der 5. von 12 Monaten ist. Wenn du jetzt =Monat(A2) schreibst, erhältst du 1, weil der Monat des 5. Tages der Excel Zeitrechnung der 05.01.1900, also Januar - Januar =1.
Ich habe es doch auch schon geschrieben:

Also entweder:....monat(A2:A22461)=I6.... oder C2:C22461=I6.....

 

[weisz]

jetzt hab ichs verstanden!

Edit:
Die Formel funktioniert jetzt natürlich mal wieder, klasse!
Seltsam, dass das nicht am Anfang funktioniert hat. Ende Januar hatte ich das Problem mit der Unterscheidung Datum und Jahr, seitdem habe ich es wohl strikt getrennt.

Vielen Dank - es läuft!

P.S. das mit der Wenns-Formel kommt dann das nächste mal!