Office: (Office 365) Nebenkosten Personenanzahl

Hallo zusammen,
erst mal an alle hier ein gutes neues Jahr.

kaum hat das Jahr begonnen habe ich eine Frage.
Was bei vielen der Kalender ist, ist bei mir mal wieder ein Problem bei unsere Nebenkostenabrechnung.

In der Tabelle "wer wohnte wo" liste ich die Mieter des Abrechnungsjahr auf (Siehe beigefügte Datei)
Auch dort werden die Personen gezählt die aktuell im ganzen Haus wohnten. Jetzt gab es dieses Abrechnungsjahr zwar keine Mieterwechsel aber bei dem Mieter (EG) hat sich, im April, die Personenanzahl geändert (von 2 auf 3 Personen)
In der Tabelle "Verbrauchsberechnung" soll aber nur der Mietername (EG) einmal auftauchen mit der Mietzeit des ganzen Jahres (Ergebnis ist händisch eingetragen).
Ich hoffe ich konnte mein Problem einigermaßen verdeutlichen.
Kann mir jemand ein denk Anstoß geben? VBA ist auch möglich.
Gruß und Danke
Martin

 

Hallo,

1) ich habe das Arbeitsblatt "Verbrauchsberechnung " umbenannt in "Verbrauchsberechnung"

2) ich habe im Arbeitsblatt "Verbrauchsberechnung" in den Zellbereich A9:C12 folgende Formeln eingefügt:
Dynamische Array-Formel in A9:
=EINDEUTIG(FILTER(tab_wer_wohnt_wo[Name];(tab_wer_wohnt_wo[Wohnung]=$B$5)*(tab_wer_wohnt_wo[von]>=$A$3)*(tab_wer_wohnt_wo[bis]<=$B$3);"-"))
Formel in B9 und folgende:
=WENNFEHLER(MAX($A$3;MIN(FILTER(tab_wer_wohnt_wo[von];(tab_wer_wohnt_wo[Wohnung]=$B$5)*(tab_wer_wohnt_wo[Name]=A9))));"")
Formel in C9 und folgende:
=WENNFEHLER(MIN(MAX(FILTER(tab_wer_wohnt_wo[bis];(tab_wer_wohnt_wo[Wohnung]=$B$5)*(tab_wer_wohnt_wo[Name]=A9)));$B$3);"")

in ähnlicher Weise auch in die Bereiche A18:C21 und A27:C30

Ich hoffe, die Formeln sind ausreichend, denn aufgrund des kleinen Datenbestandes in Tabelle "tab_wer_wohnt_wo" war es für mich nicht ganz einfach vorherzusehen, was formelmäßig wichtig ist.

Geänderte Datei liegt bei

 

Moin,

nur eine kleine Anmerkung, die aus der Erfahrung, dass sehr viele Nebenkostenabrechnung leider falsch sind, für Dich Cherished aber wichtig sein könnte: Deine EG-Wohnung wurde vom 01.01. bis zum 31.03. zu zweit bewohnt, nicht bis zum 01.04. Denn am 01.04. waren es bereits drei Personen.

Mit Deiner bisherigen Angabe rechnest du den 01.04. doppelt ab.

Grüße

 

Noch als Ergänzung:
Eine solche Abrechnung würde ich tatsächlich auf genau einem Tabellenblatt realisieren. Weniger bunt, aber in erster Linie mal als Datenbasis.

Die Zähler sind dabei erst einmal das konstante Maß aller Dinge. Deren Anzahl und Funktion werden sich vermutlich kaum verändern. Dann kommen Datum der Ablesung und Zählerstand. Die sind natürlich dynamisch. Und letztlich die Anzahl der Bewohner pro Partei, ebenfalls veränderlich.

Elegant ließe sich das über eine UserForm lösen, in der jeweils nur Änderungen eingetragen werden. Wenn also Ma & Pa im EG einen Aprilscherz gezeugt haben, trägst Du das Datum und die neue Personenanzahl in die Userform ein.

Den Rest macht dann VBA und XL. Die detaillierten Abrechnungen pro Wohnung natürlich genauso. Und die dürfen dann (generiert auf einfachen Knopfdruck) auch wieder bunt sein.

Ach ja: Und natürlich skalierbar ... Es könnte ja sein, dass aus den drei Wohnungen 13 werden

Grüße

 

Hallo Exl121150
habe deine Formel mal in meine Original Datei eingefügt und wie erwartet funktioniert sie. Hat aber etwas gedauert bis ich geschnallt habe das man die Formel NICHT nach unten ziehen braucht

Hallo d'rBastler

damit hast natürlich recht danke für den Hinweis
Das hier war nur ein Auszug um mein Problem darzustellen. Meine Abrechnung ist komplexer und Daten werden da über Userformen eingetragen.
Ich finde es so übersichtlicher, auch wenn sich excel Profis die Haare raufen

Das wäre super aber das wird leider nicht passieren.
Gruß
Martin

 

Moin Martin,
auch wenn ich mit der Selbstbezichtigung "XL Profi" extrem vorsichtig bin: Aber UserForms finde ich super! Und werde den Teufel tun, mir deswegen die (immer noch zahlreich vorhandenen und langen) Haare zu raufen.

Mit USF kann man elegant Fehl/ende Eingaben abfangen, auch XL ungeübten Usern sagen, wo's langgeht und somit eine sichere Datenbasis pflegen. Achja: Die Skalierbarkeit lohnt sich auch schon bei zwölf Wohnungen

Schöne Grüße

 

Ich finde UserForms … bescheiden!
Schließlich sind es lediglich "Krüppel", die eine Professionalität vorgaukeln.
Anders als Formulare in Access kennen die erst mal nur Text und Boolean.
(Insider: noch nicht einmal Integer)
Heißt: die erfordern ein intensives Debugging, welches in plain Excel auf der GUI gelöst werden kann.
@d'r Bastler
Versuche mal, die Formel von Anton revisionssicher mit einem UF nachzubilden …

 

Moin Ralf,
was ich sehr schön finde auf dieser Welt, dass man unterschiedlicher Meinung sein kann und diese auch ohne andere zu verunglimpfen äußern kann.

Deshalb mal folgende Frage, lieber Ralf: Wie beurteilst Du denn USFs, die professionell erstellt (also nicht gebastelt, wie von mir ) sind und dank entsprechend programmierter Fehlerfallen ebenfalls zu einem revisionssicheren Ergebnis kommen? Sind das dann auch krüppelige (ein -wie ich finde- diskriminierender Begriff) Gaukler?

Die Rückmeldungen von Kunden und Nutzern (also keinen Coder-Profis) zu USFs sind in aller Regel positiv, nicht nur im Office-Bereich. Schließlich haben sich solche Masken in vielen Bereichen erfolgreich etabliert. Wirf mal einen Blick in Deinen bevorzugten Online-Shop.

Die Zuverlässigkeit und vielfältige Leistungsfähigkeit der Formeln in XL hat meinen vollen Respekt und ich bin täglich überrascht wie ein (für mich undurchdringbares) Gewirr aus kaum nachvollziehbaren Begriffen und Klammern (angeblich einfach) zu richtigen Ergebnissen führen kann. Aber wehe, ein Nutzer fummelt dann mal daran herum...

Und z.B. das kann man mit einer modalen USF relativ leicht verhindern.

Schönes Wochenende! und schöne Grüße