excel 5

1. Absoluter Bezug

In der folgenden Tabelle berechnen Sie zuerst mit einem absoluten Bezug auf die 8 Prozent den Preis mit der Mehrwertsteuer.

  • Berechnen Sie im Feld C2 alle Preise mit 8% MwSt.
  • Kopieren Sie die Formel bis C25

2. WENN

  • Bestimmen Sie im Feld I2 mit der WENN Funktion, dass ab einem Lagerbestand weniger als 10 Stück in der Spalte „Bestellen“ die Meldung „Ja“ erscheint. Wenn keine Bestelung gemacht werden soll kommt die Meldung „Ok“.
  • Kopieren Sie die Formel bis I25

3. Zellenformatierung

  • Formatieren Sie die Preise als CHF im Buchhaltungsformat (linksbündig).

4. Sortieren

Nach Grösse

  • Sortieren Sie die Artikelnummern in absteigender Reihenfolge.
  • Sortieren Sie die Artikelnummern in aufsteigender Reihenfolge.

Alphabetisch

  • Sortieren Sie die Lieferanten von A-Z und dann von Z-A.

5. Filtern (Ausblenden)

  • Zeigen Sie nur einen Lieferanten, z.B. Mettler, an
  • Zeigen Sie die Produkte an, die exportiert werden dürfen
  • Zeigen Sie die Produkte an, die nicht exportiert werden dürfen
  • Zeigen Sie alle Produkte von von Kamber, die exportiert werden dürfen
  • Zeigen Sie alle Produkte von von Kamber, die nicht exportiert werden dürfen
  • Speichern Sie die Datei mit dem Namen „Excel5-Übungen“.

Wenn Sie möchten, dass beim Scrollen die oberste Zeile fixiert wird, dann klicken Sie im Menü Ansicht auf Fenster fixieren > Oberste Zeile fixieren.

6. Heiz- und Nebenkosten Abrechnung (Verknüpfung auf Tabellenblatt)

Für ein Mehrfamilienhaus erstellen wir eine jährliche Heiz-und Nebenkostenabrechnung.
Erstellen Sie zuerst eine Tabelle mit den Kosten für Strom, Gas, Wasser und Dienstleistungen.

  • Geben Sie dieser Tabelle den Namen HK-NK Kosten.

  • Geben Sie der nachfolgenden Tabelle den Namen HK-NK Verteilung.

Das Total der HK-NK Kosten wird für die Berechnung in der HK-NK-Verteilung verwendet. Wir gehen davon aus, dass alle MieterInnen für 12 Monate in der Wohnung sind, um zu überprüfen, ob der Anteil in Spalte G korrekt berechnet wurde.

Stimmt das Resultat mit 16559 in der Tabelle HK-NK-Kosten überein, können Sie die Mietdauer in der Tabelle HK-NK Verteilung beliebig verändern.

  • Der Verteilerschlüssel entspricht der Wohnungsgrösse.
  • Die Summe der Wohungsgrössen entspricht 100% der Heiz- und Nebenkosten.
  • Die Akonto Zahlung ergibt such durch die monatlich bezahlten HK-NK multipliziert mit der Mietdauer.
  • Beim Anteil in Spalte G wird die Verteilung in Spalte C und die Mietdauer in Spalte D in die Berechnung miteinbezogen.
  • Beim Berechnen des Anteils verwenden wir das Total in der HK-NK Kosten. Hier machen wir eine Verknüpfung auf das Tabellenblatt =’HK-NK Kosten‘!

7. VRUNDEN

Mit dieser Funktion kann z.B. ein ungerader Betrag auf den nächsten Fünfer oder Zehner auf- oder abgerundet werden. Stellen Sie die Beträge als Währung im Buchhaltungsformat dar.

  • Welche Formel schreiben Sie in die Zelle F4? ____________________________
  • In G4 runden wir das Resultat in F4 auf CHF 0.05: =VRUNDEN(F4;0.05)

8. RUNDEN

Alternativ zu VRUNDEN können Sie auch die Funktion RUNDEN verwenden.

Die Formel für das Runden auf CHF 0.05 lautet: =RUNDEN(F4*2;1)/2

9. Repetition

Wir machen noch einmal eine Übung mit den Funktionen: ANZAHL / ZÄHLENWENN / WENN / VRUNDEN / SUMME

  • Wie viele Artikel wurden verkauft?
  • Wie viele Artikel wurden für den Bereich Esszimmer verkauft?
  • Wie viele Artikel wurden für den Bereich Wohnzimmer verkauft?
  • Wie viele Artikel wurden für den Bereich Schlafzimmer verkauft?
  • Kontrollieren Sie ob die Anzahl Möbel korrekt ist.
  • Was ist der Endpreis aller Möbel mit MWSt und Rabatt gerundet?

10. Zusätzliche Funktionen

  • HEUTE() -> Ermittelt das aktuelle Datum
  • JETZT()  -> Ermittelt das aktuelle Datum und Uhrzeit
  • ANZAHL(), ANZAHL2() -> Zählt Zahlen in einem Bereich, resp. nicht leere Zellen
  • MAX(), MIN() -> Ermittelt den höchsten, resp. tiefsten Zahlenwert eines Bereichs