Lösung für die Aufgabe Nebenkostenabrechnung in Excel

Auf dieser Seite ist eine Beispiellösung für die Aufgabe über Nebenkostenabrechnung in Excel.

Es sind mehrere Mehrfamilienhäuser vorhanden. Es soll eine Excel Arbeitsmappe mit Excel erstellt werden, über die die Nebenkostenabrechnung leichter erstellt werden kann. Man beginnt mit den voraussichtlichen Ausgaben für Heizmaterial, Strom, Wasser, Hausmeister, Reparaturen, Straßenreinigung, Gartenanlagen, Fahrstuhl Wartung, Reinigung Hausflur/Keller.

Texte in Spalte A, Kosten in Spalte B, prozentuale Aufteilung in Spalten C,D und E. Die Zeilen 12 bis 15 wurden für weitere Eintragungen vorgesehen. Es sind von der nachfolgenden Darstellung nur die grauen Zellen mit Werten zu versehen, in alle anderen Zellen sind die Werte zu ermitteln.

: Die Ausgaben für die Nebenkostenabrechnung in Excel

Folgende Excel Formeln wurden ich in den einzelnen Zellen verwendet: verwendet:

Für Summe in Zeile 17

B17 gleich = SUMME ( B3 : B16 )
G17, H17, I17 J17 entsprechend B17

Für Summe % in Spalte F

F3 gleich = WENN ( D3 = „“ ; „“ ; SUMME ( C3 : E3 ) )
F4 bis F16 entsprechend F3

Für m² in Spalte G (Kosten)

G3 gleich = WENN ( D3 = „“ ; „“ ; B3 * D3 )
G4 bis G16 entsprechend G3

Für Zähler Spalte H

H3 gleich = WENN ( E3 = „“ ; „“ ; B3 * E3 )
H4 bis H16 entsprechend H3

Für Warmwasser Spalte I

I3 gleich = WENN ( C3 = „“ ; „“ ; B3 * C3 )
I4 bis I16 entsprechend I3

Für Wasser Spalte J

J5 gleich = C5

Für folgende Zellen wurden Excel Namen vergeben:

G16 NAME gleich KostQm
H16 NAME gleich KostZähler
I16 NAME gleich KostWarmw
J16 NAME gleich KostWasser

Der Vorschlag wurde nachfolgend für ein Mehrfamilienhaus dargestellt.

: Tabelle mit der Nebenkostenabrechnung

Folgende Spalten wurden vorgesehen:

Wohnungs-Nr.
m² Wohnfläche
Abschlag (alt)
Heizungsverbrauch nach Einheiten
Wasserzähler (alt)
Wasserzähler (neu)
Verbrauch Wasser
Warmwasserzähler (alt)
Warmwasserzähler (neu)
Verbrauch Warmwasser
Kosten für Wohnfläche
Kosten für Heizung nach Einheiten
Kosten für Warmwasser
Kosten gesamt
Zahlung – Guthaben
Abschlag Monat (neu)

Weiterhin wurden 15 Wohnungen für die Tabelle vorgesehen und mit den Wohnungs- Nummern 1 bis 15 versehen. Da es einfacher ist, die vorgenommenen Formeln zu prüfen, wurden für 10 Wohnungen beliebige Werte eingegeben Geben Sie bitte die nachfolgenden Werte ein. Alle anderen Werte müssen ermittelt werden.

: Daten für die Nebenkostenabrechnung

Danach sind alle anderen Werte zu ermitteln:

: Ermittlung der Nebenkostenabrechnung mit folgender Tabelle

Folgende Formeln wurden in den einzelnen Zellen verwendet: verwendet:

Für Summe in Zeile 18

B18 gleich = SUMME ( B3 : B17 )
Alle weiteren Zellen in Zeile 18 entsprechend B18

Verbrauch Wasser Spalte G

G3 gleich = WENN ( F3 = „“ ; „“ ; F3 – E3 )
G4 bis G17 entsprechend G3

Verbrauch Warmwasser Spalte J

J3 gleich = WENN ( H3 = „“ ; „“ ; H3 – I3 )
J4 bis J17 entsprechend J3

Für die weiteren Berechnungen müssen wir die Summen der betroffenen Wohnungen mit einfließen lassen. Die Summen haben wir bereits ermittelt, die Zellen versehen wir mir entsprechenden Namen. Für folgende Zellen wurden NAMEN vergeben:

B18 NAME gleich Wohnfl
D18 NAME gleich Heizung
G18 NAME gleich Wasser
J18 NAME gleich WarmWasser

Mit diesen und den bisherigen NAMEN kann weitergerechnet werden.

Kosten für Wohnfläche Spalte K

K3 gleich = WENN ( B3 = „“ ; „“ ; B3 * KostQm / Wohnfl )
K4 bis K17 entsprechend K3

Kosten für Heizung nach Einheiten Spalte L

L3 gleich = WENN ( B3 = „“ ; „“ ; D3 * KostZähler / Heizung )
L4 bis L17 entsprechend L3

Kosten für Wasser Spalte M

M3 gleich = WENN ( B3 = „“ ; „“ ; G3 * KostWasser / (Wasser + WarmWasser ) )
M4 bis M17 entsprechend M3

Kosten für Warmwasser Spalte N

N3 gleich = WENN ( B3 = „“ ; „“ ; J3 * KostWasser / ( Wasser + WarmWasser ) + J3 * KostWarmw / WarmWasser )
N4 bis N17 entsprechend N3

Kosten gesamt Spalte O

O3 gleich = WENN ( B3 = „“ ; „“ ; SUMME ( K3 : N3 ) )
O4 bis O17 entsprechend O3

Zahlung – Guthaben Spalte P

P3 gleich = WENN ( B3 = „“ ; „“ ; C3 * 12 – O3 )
P4 bis P17 entsprechend P3

Abschlag Monat (neu) Spalte Q

Q3 gleich = WENN ( B3 = „“ ; „“ ; AUFRUNDEN ( O3 / 12 ; -1 ) )
K4 bis K17 entsprechend K3