Hallo allerseits,
Folgendes Problem mit Excel würde ich gerne lösen:
Ich habe eine einfache Tabelle, eine Spalte mit Erträgen in einer anderen Spalte die dazugehörigen Datumsangaben der Erträge.
Ich möchte nun in einer weiteren Spalte nur den Ertragswert vom jeweils 1. Tag des jeweiligen Monats ausgegeben haben.
Meine Tabelle sieht einfach so aus:
In Spalte B finde ich das Datum (aufgeführt sind alle Tage des Monats), in Spalte C den jeweiligen Tagesertrag.
Und in Spalte D möchte ich nun denjenigen Ertrag aufgelistet haben, der zum 1. des jeweiligen Monats anfällt.
Wie kann ich dies erreichen?
Freundliche Grüße,
Baumschrat
Hallo,
Wie kann ich dies erreichen?
die Funktion Tag() sollte Dir helfen. Sie wirft - angewendet auf ein Datumsfeld - die Zahl des Tages innerhalb eines Monats aus. Der Rest läßt sich mit einer Wenn()-Funktion lösen.
Also z.B. =WENN(TAG(A1)=1;B1) - Daten in Spalte A, auszuwerfende Werte in Spalte B.
Gruß
Christian
Hallo,
wenn ich das richtig lese und die Spalte mit dem datum sortiert ist,
reicht ein simpler SVerweis()
Gruß Holger
Danke für die schnellen Antworten!
Ja, gerne würde ich einen SVERWEIS machen, aber mit welchen Argumenten muss ich diesen füttern?
Von welchem Monat ich den Ertrag des ersten Tages wissen möchte, löse ich mit:
DATUM(JAHR(Z1);MONAT((Z1));1)
Dabei steht in Z1 der gewünschte Monat.
Jetzt soll zu diesem 1. Tag des in Z1 angegebenen Monats der Ertrag mit SVERWEIS ausgeworfen werden.
Könnt ihr mir das erklären?
Vielen Dank schonmal!
Baumschrat
hallo,
wenn in Z1 bereits ein Datum zB. [1.3.2012] drin steht, dann ist das dein Suchbegriff:
=SVERWEIS(Z1;$B$2:blush:C$367;2;1)
wenn in Z1 zB. das heutige Datum steht: [14.03.2012] und du möchtest den Monatsanfang:
=SVERWEIS(DATUM(JAHR(Z1);MONAT(Z1);1);$B$2:blush:C$367;2;1)
Gruß Holger
Danke! Okay, ich komme der Sache schon näher.
Ich hab es nun so gemacht:
=SVERWEIS(Z1;Tabelle2!B:B;7;1)
Erläuterung:
- In Z1 steht, wie gesagt, das gesuchte Datum.
- Der Bezug ist tabellenübergreifend, deswegen „Tabelle2!“. In Spalte B sind die Datumsangaben.
- Argument „7“, weil sieben Spalten weiter die Erträge stehen.
- Für das letzte Argument habe ich einfach mal 1 genommen, obgleich ich da nicht sicher bin, ob das so richtig ist. Ich weiß nicht, was dieses Argument bedeutet.
Ausgabe ist: #BEZUG!
Was habe ich falsch gemacht?
Hallo,
Tabelle2!B:B ist auf jeden Fall Falsch!
Da muss die gesamte Matrix stehen,
wenn es bis zur 7. Spalte gehen soll also
Tabelle2!B:H
Die erste Spalte [B] zählt beim Spaltenindex übrigens mit!
Für das letzte Argument habe ich einfach mal 1 genommen, obgleich ich da nicht sicher bin, ob das so richtig ist. Ich weiß nicht, was dieses Argument bedeutet.
Hier mal ein Link dazu
Vielleicht kannst du ja ein Beispiel hoch laden wo man sehen kann, was du da machst ist vielleicht einfacher zu verstehen wo der fehler liegt:
File-Upload.net
Gruß Holger
Hallo Baumschrat,
=SVERWEIS(Z1;Tabelle2!B:B;7;1)
- Argument „7“, weil sieben Spalten weiter die Erträge stehen.
Ausgabe ist: #BEZUG!
der Spaltenindex, hier 7, bezieht sich auf den
durchsuchten Zellbereich, der muß mindest so viele
Spalten haben wie der Index sonst kommt „Bezug“.
Also =SVERWEIS(Z1;Tabelle2!B: H ;7; 0 )
- Für das letzte Argument habe ich einfach mal 1 genommen,
obgleich ich da nicht sicher bin, ob das so richtig ist. Ich
weiß nicht, was dieses Argument bedeutet.
0 bedeutet wird der genaue Wert von Z1 nicht in B:B
gefunden kommt „#NV“
1 bedeutet, wird der genaue Wert von Z1 nicht gefunden
so wird der größte der kleineren Werte als Z1 in B:B gefunden.
Ist Z1 kleiner als der oberste Wert
Ggfs. kann dann auch #NV kommen.
Du mußt entscheiden ob du als vierten Parameter die
0 oder 1 nimmst.
Ersetze mal den 1.2.2012 in Holger’s Liste durch
den 4.2.2012
Bei 0 kommt dann #NV
Bei 1 kommt dann das Sverweisergebnis für den 31.1.2012
Probiere einfach rum, dann wird es leichter zu verstehen.
Gruß
Reinhard
OT DATUM(JAHR(Z1);MONAT(Z1);1) kürzen
=SVERWEIS(DATUM(JAHR(Z1);MONAT(Z1);1);$B$2:blush:C$367;2;1)
Hallo Holger,
DATUM(JAHR(Z1);MONAT(Z1);1)
kann man kürzer schreiben:
Z1-TAG(Z1)+1
Gruß
Reinhard
DATUM(JAHR(Z1);MONAT(Z1);1)
kann man kürzer schreiben:
Z1-TAG(Z1)+1
hallo Reinhard,
das weiß ich schon, aber mir geht es darum das die Formel einigermaßen verständlich bleibt. Wer SVerweis() nicht sicher anwenden kann, versteht nicht was dort passiert.
Dann verwende ich lieber die lange Version.
Gruß Holger
Zwei Suchkriterien mit SVERWEIS?
Alles klar, super, ich danke Euch beiden!
Das klappt nun schon mal.
Kann ich in SVERWEIS auch zwei Suchkriterien angeben?
Also bspw. eine Kontonummer und ein Datum?
Es geht so einiges mit den Funktionen.
Dazu schau dir mal diese Seite an
Speziell deine Frage dieses Beispiel
beachte bei allen Formeln mit {} geschweiften Klammern = Matrixformeln
siehe Hinweis im ersten Link oben!!
Gruß Holger
Hallo Holger,
Danke für die Antwort!
Dazu schau dir mal diese Sejavascript: void(0);ite an
Speziell deine Frage dieses Beispiel
beachte bei allen Formeln mit {} geschweiften Klammern =
Matrixformeln
siehe Hinweis im ersten Link oben!!
Okay, das Problem ist nur, dass das so wie im obigen Link bei mir gar nicht zu funktionieren scheint.
Ich hab die Tabelle im Beispiel nachgebildet und dieselbe Funktion
=INDEX(C:C;VERGLEICH(„x“&„y“;A1:A99&B1:B99;0))
eingetippt.
Die {Klammern} wurden gar nicht automatisch erzeugt, und die Ausgabe war lediglich: #WERT!
Freundliche Grüße,
Baumschrat
Okay, das Problem ist nur, dass das so wie im obigen Link bei mir gar nicht zu funktionieren scheint.
bei mir funktioniert der Link
der obere Link zeigt die Startseite von
http://www.excelformeln.de/formeln.html
Ich zitiere mal das Vorgehen bei einer Matrixformel:
Diese {Klammern} werden nicht eingegeben sondern zum Abschluß der Formel durch gleichzeitig Strg Shift Enter erzeugt (statt Enter allein). Wenn Du hier einen Array kopierst, diese Klammern also löschen, und die Formel wie beschrieben abschließen.
also Formel eingeben dann nicht Return sondern
Tastenkombination > [Strg]+[Shift]+[Return]
Gruß Holger
DATUM(JAHR(Z1);MONAT(Z1);1)
kann man kürzer schreiben:
Z1-TAG(Z1)+1
das weiß ich schon, aber mir geht es darum das die Formel
einigermaßen verständlich bleibt. Wer SVerweis() nicht sicher
anwenden kann, versteht nicht was dort passiert.
Dann verwende ich lieber die lange Version.
Hallo Holger,
bezogen auf den Anfrager stimme ich dir voll und ganz zu.
Da ich aber das mit der kurzen Version nicht kannte dachte ich vielleicht kennst du das auch nicht.
Genauso wie dieses was ich nahezu zeitgleich von Erich las.
Schreib mal bitte bei Interesse
A1: März
A2: =(A1&-2012)
Das Ergebnis in A2 ist für Excel schon irgendwie ein Datum obwohl
man A2 nicht als Datum formatieren kann, bzw. als sonstwas.
Aber eingebaut in weitergehende Formeln (darum ging es als ich davon las) kann man damit rechnen.
Oder auch direkt in A2 ein „richtigeres“ Datum erhalten:
=(A1&-2012)+0
Man kann auch Schaltjahrgenau den vorherigen Monatsletzten erhalten mit
=(A1&-2012)-1
Selbstverständlich, ist wie extreme Fachsprache Laien gegenüber zu benutzen, es unsinnig, sowas einem „Beginner“ vorzusetzen.
Der hat seine Last erstmal SVerweis zu verinnerlichen und den
Unterschied Datum(), DAtwert u.v.m.
Ich für mich würde egal wie genial sie sein mögen, solche exotischen Formeln nur benutzen wenn ich mir zutraue auch in einem
Jahr problemlos die Formel lesen/verstehen zu können.
Das trifft bei mir auf die Tag-Formel zu, aber bei
(A1&-2012) stutze ich in einem Jahr, eher früh genauso wie vor Tagen
als ich das zum ersten mal las. Stutzen = ausprobieren , Varianten
testen um zumindest in etwa zu kapieren warum wiseo die Formel funktioniert.
Der Hintergrund ist mir weiterhin sehr nebelulös, evtls. hat das was mit dem Bindestrich als amerik. Datumstrenner zu tun.
Gruß
Reinhard
Okay, das Problem ist nur, dass das so wie im obigen Link bei
mir gar nicht zu funktionieren scheint.
Ich hab die Tabelle im Beispiel nachgebildet und dieselbe
Funktion
=INDEX(C:C;VERGLEICH(„x“&„y“;A1:A99&B1:B99;0))
eingetippt.
Die {Klammern} wurden gar nicht automatisch erzeugt, und die
Ausgabe war lediglich: #WERT!
Hallo Baumschrat,
excelformeln.de kenne ich schon jahrelang, Holger sicher auch schon lange.
Bislang gab es da für mich keine deren Formel die nicht funktionierte.
Wenn was schieflief habe ich falsch umgesetzt auf andere Tabellenverhältnisse.
Wenn du nun abewr deren Beispieltabellen nachbaust und es klappt nicht
so gehe ich erstmal davon aus, dein Nachbau stimmt nicht.
Kann manches sein, andere Zellformatierungen, Schwieigkeiten
im Umgang mit Matrixformeln.
Das ist alles nichts schlimmes. Das passiert JEDEM, grad in der Anfangszeit von Excel.
Und grad bei Array/matrixformeln auch noch später *selbstweiß*
Um das jetzt abzukürzen und letztlich dir auch schneller und zielgenauer helfen zu können lade bitte eine Beispielmappe
hoch mit dem Link zu fileupload den dir Holger in seinem Beitrag
um 13:08 zeigte.
Evtl. erkennen wir dann was du übersehen hast.
Gruß
Reinhard
1 „Gefällt mir“
Beispielmappe
Hallo Reinhard und Holger,
Gerne. Ich habe eine Beispielmappe hier hochgeladen:
http://www.dateiupload.com/files/IPSPlTNLzD.xlsx
(Leider hat bei mir file-upload.net nicht funktioniert)
Was das Problem ist, habe ich in die Mappe reingeschrieben. 
Vielen Dank für Eure Hilfe, und freundliche Grüße,
Baumschrat
Hallo,
hier dein Beispiel
Ich habe den Bereichen Kontonummer; Datum; und Ertrag einen Namen vergeben, damit die Bereiche in den Formeln besser zu erkennen sind.
Siehe hier als Übersicht
Bitte beachte:
Matrix verstehen
Namen verstehen
Beides verlinkt in der Übersicht
Gruß Holger
1 „Gefällt mir“
Wow, Danke!
Echt vielen Dank für die Hilfe!
Das sieht schon mal sehr gut aus und scheint mein Problem fast zu lösen.
Zwei Fragen dazu:
-
Wie hast Du denn in dieser Tabelle den tabellenübergreifenden Bezug hergestellt? Ich sehe davon überhaupt nichts.
-
Du verwendest die Funktion MIN.
Wird damit nicht automatisch nur der niedrigste Wert ausgegeben?
Sorry, mein Fehler, meine Beispielmappe war zu unvollständig.
Das heißt, wenn ich nun eine Tabelle habe, in der Daten von mehreren Jahren vorliegen und ich will von all deren Monaten die Erträge zum jeweils ersten des Monats ausgegeben haben, wie kann ich das denn erreichen?
Gruß,
Baumschrat
Zwei Fragen dazu:
- Wie hast Du denn in dieser Tabelle den
tabellenübergreifenden Bezug hergestellt? Ich sehe davon
überhaupt nichts.
Die Namen die ich für Kontonummer ec. vergeben haben gelten Blatt-übergreifend. Wenn du deine Tabelle hast, kannst du übrigens die Namen links oben im Namensfeld auswählen, du bist dort direkt verlinkt.
- Du verwendest die Funktion MIN.
Wird damit nicht automatisch nur der niedrigste Wert
ausgegeben?
Richtig, ich suche ja das erste [frühste] Datum welches zur Kontonummer passt.
Das frühste Datum hat den niedrigsten Wert.
Trage mal irgendwo in die Liste das Datum 31.12.2010 ein, dann wird dieses Datum verwendet. Die Liste muss nicht sortiert sein
Sorry, mein Fehler, meine Beispielmappe war zu unvollständig.
Das heißt, wenn ich nun eine Tabelle habe, in der Daten von
mehreren Jahren vorliegen und ich will von all deren Monaten
die Erträge zum jeweils ersten des Monats ausgegeben haben,
wie kann ich das denn erreichen?
Dann benötigst du das frühste Datum des jeweiligen Monats
Die Suche nach dem Datum muss dann erwetert werden:
schau mal
Gruß Holger
1 „Gefällt mir“