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.

Tabelle mit den Ausgaben
Die Ausgaben für die Nebenkostenabrechnung in Excel

Folgende Excel Formeln wurden ich in den einzelnen Zellen 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.

Nebenkostenabrechnung Tabelle
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.

Nebenkostenabrechnung Daten
Daten für die Nebenkostenabrechnung

Danach sind alle anderen Werte zu ermitteln:

Nebenkostenabrechnung Tabelle
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