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. Bonusberechnung
In der Firma Olsen GmbH sind 5 Aussendienstmitarbeiter angestellt. Im Jahr 2020 wurden von den Mitarbeitern folgende Verkaufsumsätze generiert:
Spalte A – Name
A2 -Keller – A3 – Leonhardt – A4 – Meier – A5 – Wirsch – A6 – Weiermann – A8 -Umsatzdurchschnitt – A9 – Mindestumsatz
Spate B – Umsatz 2020
B2 – 345000 – B3 – 350000 – B4 – 349000 – B5 – 358000 – B6 – 376000 – B8 – (Formel) – B9 – 350000
Spalte C – Ferientage 2021
Spalte D – Umsatzziel 2021
Spalte E – Umsatz 2021
E2 – 367100 – E3 – 373100 – E4 – 373500 – E5 -373600 – E6 -368300
Spalte F -Bonus 2021
Spalte G – Umsatzveränderung 2020/21
- Erstellen Sie eine Berechnung, die bestimmt, dass die Mitarbeiter, die im Jahr 2020 mehr als 350‘000 Umsatz gemacht haben, im Jahr 2021 pro 5000 Fr. je einen Ferientag geschenkt bekommen.
- Der Wert kann auf- oder abgerundet werden.
- Die Formel in D2 lautet?
- Erstellen Sie in der gleichen Tabelle das Umsatzziel für 2021 pro Mitarbeiter (Berechnung: Durchschnittlicher Umsatz 2020 plus 5 Prozent des Umsatzes 2020 des betreffenden Mitarbeiters).
- Die Formel in E2 lautet?
- Die zusätzlichen Ferientage im Jahr 2021 sollen in ganzen Tagen angezeigt werden (ohne Dezimalstellen).
- Welcher Mitarbeiter bekommt am meisten Ferientage?
7. Umsatzziel
- Erstellen Sie eine =WENN Bedingung, die das Umsatzziel überprüft.
- Wird der Umsatz im Jahr 2021 erreicht (grösser/gleich), wird die Meldung „Bonus Ja“ angezeigt, ansonsten soll „Bonus Nein“ angezeigt werden.
- Die Formel in G2 beginnt mit =WENN(
- Welche Mitarbeiter erhalten einen Bonus?
- Fügen Sie eine Spalte hinzu, wo die Umsatz Veränderung der Mitarbeiter in den Jahren 2020 und 2021 in Prozenten vergleicht.
- Bei welchen Mitarbeitern gab es einen Umsatzrückgang?
- Erstellen Sie ein Balkendiagramm, das die Umsätze der einzelnen Mitarbeiter im Jahr 2020 und 2021 miteinander vergleicht.
8. 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.
Spalte A – Rechnung
A2 – Artikel Nr. – A3 -1001 – A4 – 1014 – A5 – Total
Spalte B
B2 – Bezeichnung – B3 – Legobausteiln – B4 – Holzlastwagen
Spalte C
C2 – Preis / Stück – C3 – 3.50 – C4 – 25.00
Spalte D
D2– Menge – D3 – 3 – D4 – 5
Spalte E – Rabattsatz
E2 – Bruttopreis
Spalte F – 2.5%
F2 – Nettopreis
Spalte G
G2 – Gerundet
- Die Formel in Zelle F4 lautet: =E4-(E4*$F$1)
- In G4 runden wir das Resultat in F4 auf CHF 0.05: =VRUNDEN(F4;0.05)
9. 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
10. 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?
11. Zusätzliche Funktionen
Erstellen Sie selbständig oder in der Gruppe eine Excel Tabelle, wo Sie folgende Funktioen anwenden:
- HEUTE() -> Ermittelt das aktuelle Datum
- JETZT() -> Ermittelt das aktuelle Datum und Uhrzeit
- ANZAHL(), ANZAHL2() -> Zählt Zahlen in einem Bereich, resp. nichtleere Zellen
- MAX(), MIN() -> Ermittelt den höchsten, resp. tiefsten Zahlenwert eines Bereichs
12. Arbeitsmappen vernknüpfen
Öffnen Sie die Datei „Heiz- und Nebenkosten Abrechnung“. An diesem Beispiel wird erklärt, wie Sie Information von verschiedenen Arbeitmappen in Formeln integrieren.
