4.1k Aufrufe
Gefragt in Tabellenkalkulation von jeremy Mitglied (677 Punkte)
Hallo Leute,

ich stoße mit meinen Excel Kenntnissen leider mal wieder ans Ende und hoffe durch wie in der Vergangenheit Hilfe zu erfahren.

Anhängend ist eine Datei zur Kosten-Einnahmen-Überwachung.

Dort gibt es unter anderem die Blätter Januar bis Dezember und das Blatt Jahresbericht. Im Blatt Jahresbericht rechnet er in den Zellen G22:G33 die Monatsmieten nach Eingang zusammen. Funktioniert einwandfrei.
In den Zellen E22:E33 & E52:E63 soll er eigentlich den Forecast berechnen, allerdings geht diese Rechnung sowie ich sie will nur mit "wenn, und, dann...". Da hört mein Latein leider auf...

Ich sage mal was er tun soll:

Bsp. Zelle E22 (Jahresbericht)

-Aus der Zelle B4 geht hervor, wie weit (bis zu welchem Datum) die aktuellen "ist" Zahlen eingepflegt sind.
Dieses Datum ändert sich mit jeder neuen Eingabe.

-ist der Monat der Zelle B4 Januar, nimm die Jahres-Soll-Miete Netto aus dem Januar (G22),
ist der Monat der Zelle B4 Februar, addiere die Jahres-Soll-Miete aus dem Januar und Februar(G22 Januar + G22 Februar)
ist der Monat der Zelle B4 März, addiere die Jahres-Soll-Mieten aus dem Januar, Februar und März usw.

Und diese Formel möchte ich dann auf die Zellen E22:E33 &E52:E63 runterkopieren können.


Kann mir diesbezüglich jemand behilflich sein???


http://www.file-upload.net/download-4240020/test.xls.html

www.file-upload.net/download-4240020/test.xls.html



Danke Jeremy

13 Antworten

0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

habe Dir in den grün markierten Bereichen die Formeln angepasst.
Die Formeln in Spalte E arbeiten unabhängig von den Eintragungen in B4, sondern summieren immer von Januar bis einschließlich aktueller Monat.


Download

Gruß
Rainer
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo Rainer,

wie immer vielen Dank für deine schnelle und zuverlässige Hilfe !!!
Leider stimmt dies noch nicht so ganz..., obwohl der Ansatz völlig supi war und du schon weiter und einfacher gedacht hast...

Ich bin leider zu doof um deine Formel zu verstehen (vielleicht kannst du sie mir ja mal bei Gelegenheit grob erklären), aber die Summen sind falsch.
Normalerweise müsste im "Jahresbericht" in E22 die Summe aus
Januar E22 und Februar E 22 stehen, also 475,36.
Du bist glaube ich vom Datum heute ausgegangen...

Aber in der Liste sind ja erst 2 Monate "Mieteingänge" zu verzeichnen, in G22:G33. Und deswegen benötige ich ja das Soll nur aus diesen beiden Monaten (E22:E33 & E52:E63) und nicht vom heutigen Datum ausgehend...
Deswegen war der Bezug zu B4 gemacht.

Ich möchte sehen können, wie viele Mieteinahmen habe ich aktuell (G22:G33 in den einzelnen Monaten) und wieviel Soll habe ich in den einzelnen Monaten dafür geplant...


Kann man dies ändern ???


Jeremy
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

Die Formeln in Spalte E arbeiten unabhängig von den Eintragungen in B4, sondern summieren immer von Januar bis einschließlich aktueller Monat.


....diesen Satz schrieb ich nicht umsonst, da zwischen "Wollen" und "Können", besonders bei komplexen Formeln, oft ein grösserer Spalt, vorhanden ist.
Werde noch mal alles überdenken, kann Dir aber nicht versprechen, den Spalt zu schließen.

Gruß
Rainer
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

ich glaube mir ist ein Licht aufgegangen.

Download

Gruß
Rainer
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo Rainer,

ich bin wie immer von den Socken, was du da jedes mal hin bekommst. Ich weiß zwar nicht was die Formel macht, aber sie sieht schonmal sehr abenteuerlich aus und nach ersten Tests funktioniert sie anscheinend tadellos.

WAHNSINN !!!

Ich werde das leider nie können, aber wenn du wirklich mal kurz Zeit hast, würde ich mich sehr freuen, wenn du mir wirklich nur mal grob sagen kannst was die Formel macht und wie sie das Ergebnis errechnet.

Aber auf diesem Wege ein GANZ GANZ GROßES Dankenschön an dich für die perfekte Hilfe !!!

Ich wünsche Dir ein erholsames und schönes Osterfest und verbleibe nochmals mit den besten Dankesgrüßen


Jeremy
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

zunächst auch Dir Frohe Oster und...

Ich werde das leider nie können

… das liegt allerdings allein an Dir :-)

Auch ich bin kein Formelautomat und ein Excelgen wurde mir auch nicht
in die Wiege gelegt, man muss halt etwas tun um Excel zu verstehen.
Mein Geheimnis ist Excelformeln.de was ich nur jeden ans Herz
legen kann und auch schon öfter geschrieben habe.

Auch die verwendete Formel stammt von dieser Seite aus dem Bereich Summenfunktionen
und ist von mir nur an Deine Belange angepasst worden.
In E22 des Jahresberichtes lautet diese…
{=SUMME(SUMME((ZEILE($1:$12)<=MONAT($B$4))*SUMMEWENN(INDIREKT(TEXT(DATUM(1;ZEILE($1:$12);1);"MMMM")&"!e"&ZEILE());"<1e+99")))}

… und wird als Matrixformel ausgeführt.

Sie besteht aus 2 Argumenten, welche in diesen Falle eigenständige Funktionen darstellen
1. Argument: SUMME((ZEILE($1:$12)<=MONAT($B$4))
2. Argument: SUMMEWENN(INDIREKT(TEXT(DATUM(1;ZEILE($1:$12);1);"MMMM")&"!e"&ZEILE());"<1e+99"))

Das 1. Argument erzeugt 12 Wahrheitswerte indem die Zahlen 1 bis 12 ZEILE($1:$12) mit dem Monat aus B4 <=MONAT($B$4) verglichen werden.
Das Ergebnis ist diese Matrixkonstante {WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}

Diese Matrixkonstante kannst Du Dir anzeigen lassen, indem Du in der Eingabeleiste den Formelteil ZEILE($1:$12)<=MONAT($B$4) markierst und F9 drückst.

Im 2. Argument wird zum einen eine Matrixkonstante mit allen Werten aus Zelle E22 der Monate Jan-Dez erzeugt
markiere diesen Teil INDIREKT(TEXT(DATUM(1;ZEILE($1:$12);1);"MMMM")&"!e"&ZEILE()) und drücke F9, Du erhältst
{-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68;-237,68},
zum anderen eine Matrixkonsante
{"Januar";"Februar";"März";"April";"Mai";"Juni";"Juli";"August";"September";"Oktober";"November";"Dezember"},sie bestimmt
aus welchen Tabellen die Werte gelesen werden und kann durch markieren von TEXT(DATUM(1;ZEILE($1:$12);1);"MMMM") und F9
angezeigt werden.
Die äußere Summenfunktion der Formel bildet nun die Summe aus allen Werten in Matrixkontante 2 (-237,68 und -237,68),
die den Positionen von WAHR in Matrixkonstante 1 und Matrixkonstante 3 (Januar und Februar) entsprechen.

Bei Fragen bitte melden.

Gruß
Rainer
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo Rainer,

als allererstes mal ein RIESEN DANK für deine Erklärung. Du hast dir eine Wahnsinns Arbeit gemacht und dafür
mein allerherzlichsten Dank !!!!!!!!!!!!!!!!!!!

Leider bin ich seit ein paar 6 Tagen beruflich unterwegs und komme hier leider noch nicht dazu mir das ganze mal in Ruhe
anzuschauen und versuchen das gezeigte zu lernen und im Kopf zu speichern. Nach meiner Rückkehr nächste Woche werde
ich mir das aber sofort vornehmen.

Zwecks des besagten Sheets hätte ich da noch eine kleine Anfrage, bei der du mir vielleicht auf die Ferne noch weiterhelfen könntest.
In den Sheet welches du als letztes hochgeladen hast würde ich noch eine weitere Formel benötigen, vielleicht kannst du mir noch ein
weiteres mal behilflich sein.

Im Reiter "Jahresbericht" in den Zellen B110 (und darunterfolgende) sowie C110 (und darunterfolgende) muss noch folgendes berechnet werden:

Zelle B110:


Wenn in B105 ein Datum aus dem Monat Januar steht, dann nimm die Zahl aus "Reiter Leerstand" F8, wenn in B105 ein Datum aus dem Monat Februar steht, dann nimm die Zahl aus "Reiter Leerstand" F8 + G8, wenn in B105 ein Datum aus dem Monat März steht, dann nimm die Zahl aus "Reiter Leerstand" F8 + G8 + H8 u.s.w.

Zelle C110:

Wenn in B105 ein Datum aus dem Monat Januar steht, dann nimm die Zahl aus "Reiter Leerstand" F21, wenn in B105 ein Datum aus dem Monat Februar steht, dann nimm die Zahl aus "Reiter Leerstand" F21 + G21, wenn in B105 ein Datum aus dem Monat März steht, dann nimm die Zahl aus "Reiter Leerstand" F21 + G21 + H21 u.s.w.


Besteht die Möglichkeit, bei dieser Formel erneut eine Hilfestellung von Dir zu bekommen ???


Mit besten Grüßen

Jeremy
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

Formel für B110:

=SUMMENPRODUKT((SPALTE($F:$Q)-4<=$B$105)*(Leerstand!$F8:$Q8))

Formel für C110:

=SUMMENPRODUKT((SPALTE($F:$Q)-4<=$B$105)*(Leerstand!$F21:$Q21))

Gruß
Rainer
0 Punkte
Beantwortet von jeremy Mitglied (677 Punkte)
Hallo Rainer,

danke für die schnelle Antwort...

Leider funktioniert es nicht so, wie gewollt. Ich habe die Formel eingesetzt und er zeigt mir in B112 als Beispiel
-313,00 € an. Diese sind ja aber im Reiter &quot;Leerstand&quot; erst im März drinnen, dürften also im Reiter &quot;Jahresbericht&quot;
noch gar nicht auftauchen, da dort in B105 ja noch ein Datum aus dem Monat Februar steht...

Habe ich es schlecht oder falsch erklärt ???

Prinzipiell soll es wie bei der ersten Formel von Dir funktionieren, anhand des Datums sollen die möglichen Zellen
aus &quot;Leerstand kummuliert werden...


LG Jeremy
0 Punkte
Beantwortet von rainberg Profi (14.9k Punkte)
Hallo Jeremy,

verstanden hatte ich es schon nur falsch umgesetzt und nicht getestet.

Formel für B110
=WENN(ISTNV(SUMMENPRODUKT((BEREICH.VERSCHIEBEN(Leerstand!$E8;;1;;MIN(VERGLEICH(0;Leerstand!$F8:$Q8;-1);MONAT($B$105))))));"";SUMMENPRODUKT((BEREICH.VERSCHIEBEN(Leerstand!$E8;;1;;MIN(VERGLEICH(0;Leerstand!$F8:$Q8;-1);MONAT($B$105))))))

Formel für C110
=WENN(ISTNV(SUMMENPRODUKT((BEREICH.VERSCHIEBEN(Leerstand!$E21;;1;;MIN(VERGLEICH(0;Leerstand!$F21:$Q21;-1);MONAT($B$105))))));"";SUMMENPRODUKT((BEREICH.VERSCHIEBEN(Leerstand!$E21;;1;;MIN(VERGLEICH(0;Leerstand!$F21:$Q21;-1);MONAT($B$105))))))


Gruß
Rainer
...