2019 kalkulation Excel - Lehrmittel - EdulinoAusgangslage: Ein Handwerker verlangt für seine Arbeit...

Tab

elle

nka

lku

lati

on

Exc

el 3

65 2

019

Mo

du

l 5

Tabellen-kalkulation

Excel 365 2019

Tabellenkalkulation Excel 365/2019

IKA-Modul 5Excel ist das Meer der Zahlen und Tabellen, faszinierend und scheinbar uferlos. Damit der Einstieg in diese mathematische Welt gelingt und die erwünschten Resultate greifbar werden, sind grundlegende Kenntnisse im Umgang mit den vielfältigen Möglichkeiten dieses äusserst hilfreichen Programms bei der Verarbeitung und Auswertung von Zahlenmaterial wichtig und nützlich.

Das Lehrmittel «Tabellenkalkulation | Excel 2016» erklärt in einfach nachvollziehbaren und modular aufgebauten Schritten wichtige und nützliche Befehle, Formeln, Funktionen und weitere typische Anwendungen des Tabellenkalkulationsprogrammes. Jeder Lernschritt wird mit zahlreichen Aufgaben auf unterschiedlichen Niveaustufen vertieft und gefestigt. Anhand von komplexeren Aufgaben werden bereits erworbene Kenntnisse weiter vertieft, so dass der Einsatz von Excel bei der Verarbeitung und Auswertung von Zahlenmaterial in Zukunft sicher und erfolgreich eingesetzt werden kann.

Das Lehrmittel eignet sich für

– alle Profile der kaufmännischen Ausbildung (Profil B, E und E mit Berufsmatura)

– Handels- und Wirtschaftsmittelschulen

– die Vorbereitung auf die Diplomprüfung ICT-Advanced-User SIZ

Die weissen Sterne bei den Aufgaben zeigen das Anspruchsniveau und/oder den Zeitaufwand (zum Lösen) an.

Aufgabe

Inhalt1 EXCEL-GRUNDLAGEN 2

1.1 Excel-Oberfläche 2

1.2 Benutzer-Oberfläche 3Elemente auf dem Bildschirm 3Backstage: Register DATEI 5Kontextmenü und Minisymbolleiste 6Hilfe zur Selbsthilfe 7

1.3 Arbeitsmappe – Tabellenblatt (-register) 8Arbeitsmappen 8Tabellenblätter 9

1.4 Hilfreiche Werkzeuge 10Navigation – navigieren 10Markierung – markieren 11Spalten und Zeilengrössen 12Korrigieren und überarbeiten 12AutoVervollständigen 13

1.5 Zahlenwert oder Text? 14Excel verstehen: Grundtypen von Daten 14

2 FORMELN MIT GRUNDOPERATIONEN 16

2.1 Wie Excel rechnet 16Konstanten oder Zellbezüge 16

2.2 Grundrechnungsarten 17Rechengesetze (Reihenfolge, Klammern) 17Vergleichsoperatoren 18Für Profis: Zellinhalte mit Text

verknüpfen/verketten 18

2.3 Zuordnungen – Dreisätze 19Direkt proportionale Zuordnungen 19Umgekehrt/indirekt prop. Zuordnungen 22

2.4 AutoAusfüllen: Listen automatisch weiterführen 24

Mehrwert von Reihen: Relative Bezüge 25

2.5 Repetitionen: Üben und Vertiefen 27

3 TABELLENGESTALTUNG 32

3.1 Eine Zelle – fünf Ebenen 32

3.2 Schrift- und Absatzformate 33Schriftformate 33Ausrichtung – Absatzformate 33

3.3 Rahmenlinien und Schattierungen 35Rahmenlinien 35Schattierungen: Muster und Farbflächen 35Tabellenformatvorlagen 36

3.4 Zahlenformate 37Übersicht: Zahlenformate 38Bedeutung der Platzhalter 39Für Profis: Regeln für Zahlenformate 39Spezialfall: Datum und Uhrzeit 40

3.5 Seitenlayout – Drucken 41Seite einrichten 41Tabellenblätter drucken 42Seitenumbruch festlegen 46

3.6 Umgang mit Zellen, Zeilen und Spalten 47Einfügen, ergänzen 47Löschen 48Verschieben/Kopieren 49Ganze Spalten oder Zeilen verschieben 50Nach Zellinhalten suchen und ersetzen 50Zwischenhalt: Notizen 52

3.7 Umfangreiche Tabellen 53Fenster fixieren 53Ein- und ausblenden:

Zeilen, Spalten, Tabellenblätter 53Tabellen in Arbeitsmappen kopieren/

verschieben 55

3.8 Repetition: Üben und Vertiefen 56

4 RECHNEN MIT ... 62

4.1 Berechnungen mit Prozenten 62Für Hundert – von Hundert 62Prozentwert berechnen (W) 63Prozentsatz berechnen (p%) 64Grundwert berechnen (G) 64Preisaufschlag: zuzüglich

Prozentsatz berechnen (G-neu) 64Preisabschlag: abzüglich

Prozentsatz berechnen (G-neu) 65

4.3 Realtive und absolute Bezüge 70Standard: automatisch relativ 70Absolut setzen 70Gemischte Bezüge 72

4.4 Zeitberechnungen 73Jahre, Tage, Minuten und Sekunden 73Über 24 Stunden rechnen 74Dezimalstunden: Industriestunden 75

4.5 Repetitionen: Üben und vertiefen 77

Tabellenkalkulation Excel

5 DIAGRAMME 82

5.1 Zahlen grafisch auswerten 82Standard-Diagramm (F11) erstellen 83Individuelle Diagramm einfügen 83

5.2 Diagramm planen 85Wahl des Diagrammtyps 85Ihr Problem: Die Problemstellung! 88Vergleich: Entwicklung oder Anteile? 89

5.3 Diagramm verändern, anpassen 92Manipulation – Einfluss nehmen 95Diagramme mit Bildelementen 98Diagramme mit Datenwerten ergänzen 99

5.4 Spezielle Diagrammwerkzeuge 100Kombi-Diagramme

(ehemals Verbunddiagramme) 100Säulen/Balken und Sektoren anpassen 101Trendlinien 103

5.5 Repetition: Üben und vertiefen 105

6 ERGEBNISSE AUF EINEN BLICK 110

6.1 Bedingte Formatierung 110

6.2 Sortieren 113Was ist ein Datensatz? 113

6.3 Daten filtern 115AutoBerechnen: Schnell «überschlagen» 117

6.4 Repetition: Üben und vertiefen 118

7 BERECHNUNGEN MIT FUNKTIONEN 122

7.1 Grundlagen: Funktionen 122Wissenswertes über Funktionen –

zusammengefasst 123

7.2 Die 6 Standard-Funktionen 124Funktion SUMME 124Funktion MAX und MIN 125Funktion ANZAHL und ANZAHL2 125Funktion MITTELWERT 125

7.3 Zwischenhalt: externe Zellbezüge 129... innerhalb derselben Arbeitsmappe 129... aus anderen Arbeitsmappen 130Excel verstehen: Fehlermeldungen 131Falsche Zellbezüge korrigieren 131Formeln oder Ergebnisse anzeigen 132Excel überwacht Fehler in Formeln, aber ... 133

7.4 Neue Funktionen 134Funktionsassistent 134Funktion RANG.GLEICH (veraltet: RANG) 134Funktion RUNDEN: Dezimalrundung 136

Funktion RUNDEN: Einheitenrundung (nicht dezimal) 137

Kritisch: Funktion VRUNDEN 138Auf- und abrunden lassen (dezimal) 138Auf- und abrunden lassen (Einheiten,

nicht dezimal) 139

7.6 Zeit- und Datumsfunktionen 143Funktion HEUTE und JETZT 143Weitere Funktionen für Zeit und Datum 144Für Profis: Exaktes Alter berechnen 145

7.7 Ergebnisse unter der Bedingung von ... 147Funktion WENN 147Funktionen ZÄHLENWENN,

SUMMEWENN 148Ärger vermeiden: Korrekte Bezüge! 150Für Profis: Mehrere Suchkriterien 151

7.8 Funktionen verschachteln/verketten 152Für Profis: WENN-Funktionen

verschachteln 153Für Profis: WENNs-Funktion 154Für Vollprofis: UND – ODER – NICHT 155

7.9 Repetitionen: Üben und vertiefen 159

8 DATEN AUSWERTEN 166

8.1 Teilergebnisse anzeigen lassen 166Funktion TEILERGEBNIS 166Teilergebnisse mit Tabellenformatvorlagen 167

8.2 Pivot 169PivotTable – Pivot-Tabelle 169PivotChart – Pivot-Diagramm 173

8.3 Verweisfunktionen 175Funktion SVERWEIS (SVerweis) 175Funktion WVERWEIS (WVerweis) 178

8.4 Repetitionen: Üben und Vertiefen 179

9 WERKZEUGE FÜR SPEZIELLE AUFGABEN 184

9.1 Rationeller arbeiten 184Weekend farbig hervorheben 184Einfügen, aber speziell 185Dateneingabe: «Durchschlagpapier» 187

9.2 Tabelle zur Weiterbearbeitung durch andere 188

Kommentare als hilfreiche Hinweise 188Notizen als hilfreiche Hinweise 188Arbeitsmappen schützen 189Zellen schützen – nur mit Blattschutz 190Gültigkeitsregeln 191


9.3 Spezielle Berechnungen 193Zinseszins-Rechnungen 193Für Minimalisten: Zielwertsuche 194Interessant: Break-Even 195

9.4 Funktion für besondere Anlässe 198Mathematische Funktionen 198Textfunktionen 199Informationsfunktionen 200Für Profis: Excel-Vorlagen (Spreadsheets) 204

10 ANHANG 205

Stichwortverzeichnis / Index 206


Rechnen mit ...

4

4 Rechnen mit ...

4.1 Berechnungen mit Prozenten

Für Hundert – von HundertVor dem Prozentrechnen haben viele Leute Respekt. Unbegründet! Das ist viel einfacher, als Sie denken. Schauen Sie sich die folgenden Erklärungen an.

Prozentzahlen zeigen immer einen relativen Vergleich zu einem Ganzen:

– Man geht also davon aus, dass eine bestimmte Anzahl Elemente ein Ganzes bilden.

– Dieses Ganze wird als 100 % (alles, was vorhanden ist) bezeichnet.

– Berechnungen bestehen nun darin, entweder auszurechnen, wie viele Prozente eine Anzahl Elemente dieser Gesamtmenge (Ganzes) ergeben, oder wie viele Elemente einen bestimmten Prozentsatz ausmachen.

Grundwert G Prozentsatz p% Prozentwert/-ertrag W

Ausgangswert (Basis)

Der Grundwert gibt das Ganze an und entspricht 100 %.

Angabe (Anteil) in Prozenten

Der Prozentsatz entspricht einem %-Anteil vom Ganzen.

Wert vom (prozentualen) Anteil

Der Prozentwert entspricht der Grösse (Menge) des Anteils.

z. B. Kapital, Gesamtumsatz, Gesamtbevölkerung, Wahlstimmen, eingegangene Abstimmungszettel

z. B. 1.25 % Zins, Anteil in Prozenten, Rabatt und Skonto, Abschreibungssatz

z. B. Zinsertrag in CHF, mengen-mässiger Anteil der Frauen an der Weltbevölkerung

Angaben in CHF, Personen, kg Angaben in % Angaben in CHF, Personen, kg

Ausgangslage: Eine bekannte Gratis-Zeitung finanziert sich ausschliesslich durch Werbe-Inserate.

A Gehen Sie in dieser Aufgabe davon aus, dass die Zeitung einen bestimmten Anteil der Seiten mit Inseraten füllt.

a) Bei 20 Seiten sind das (3∕5 von 20 Seiten = 20 : 5 × 3) 12 Seiten. b) Bei 30 Seiten sind das (3∕5 von 30 Seiten = 30 : 5 × 3) 18 Seiten. c) Bei 40 Seiten sind das (3∕5 von 40 Seiten = 40 : 5 × 3) 24 Seiten.

Sie stimmen mit der Autorin überein:

– Der Anteil ist immer gleich gross, nämlich 3/5 (also 60 %).

– Nur die Anzahl Seiten verändert sich, abhängig von der Gesamtseitenzahl der Zeitung, nicht aber der Anteil.

– Die Berechnungen sind ausschliesslich direkt proportionale Zuordnungen (Dreisätze, siehe Kapitel 2 in diesem Lehrmittel)

– Das macht die Sache doch schon bedeutend einfacher, oder? ... und es gibt nur deren 5 davon.

B Vervollständigen Sie diesen Satz mit «mehr» oder «weniger»:

Je grösser/umfangreicher die Zeitung ist, desto ... Seiten stehen für die Werbung zur Verfügung.

Grundlagen: Überlegungen zu ProzentberechnungenAufgabe 67

62


1 Excel-Profis rechnen so!2 Das funktioniert in den folgen 4 Beispielen genau gleich.

A Öffnen Sie für die folgenden Erklärungen die Arbeitsmappe Prozentgrundlagen.

B So können Sie die Erklärungen 1 zu 1 «nachbauen».

C Bei den Beispielen in Excel können Sie sich Überlegungen in eigenen Worten notieren, so haben Sie nachher eine gute Vorlage für spätere Aufgaben.

D Drucken Sie am Schluss die Excel-Tabelle aus. Sie ist so vorbereitet, dass die Spalten- und Zeilenüberschriften sowie die Gitternetzlinien, skaliert auf 1 Seite, ausgedruckt werden.

Prozentwert berechnen (W)

Ausgangslage: Ein Handwerker verlangt für seine Arbeit und das Material netto 1500.00 (G). Auf der Rechnung wird noch 7.7 % Mehrwertsteuer (p%) hinzugerechnet.

Frage: Wie hoch ist der Mehrwertsteuerbetrag in Franken (W)?

Lösung auf Papier Lösung in Excel

1500 : 100 × 7.7

(oder: 7.7 : 100 × 1500 oder: 1500 × 7.7 : 100

Erklärung

Das %-Zeichen in der Zelle F5 bewirkt, dass bei der Berechnung automatisch durch 100 geteilt wird. Das Prozentzeichen in der Berechnungsformel D7 bewirkt, dass bei der Berechnung automatisch durch 100 geteilt wird. So müssen Sie «/100» nicht selber eingeben (excellisch korrekt1).

100 in der Formel entfernen

Für eine excellische Berechnung formen Sie die Formel so um, dass Sie die 100 wegnehmen können. Dazu müssen Sie wissen, dass eine Gleichung nie mit einer Divison beginnen kann. In diesem Beispiel heisst das: Bei den Formeln (Rechenmaschine) können Sie die 100 ohne Verfälschung entfernen und so das %-Zeichen in die Formel schreiben2:

1500 / 100 * 7.7 ergibt 1500 * 7.7 % ebenso funktioniert 7.7 / 100 * 1500 ergibt 7.7 * 1500 % oder 1500 * 7.7 / 100 ergibt wieder 1500 * 7.7 %

Fünf auf einen Streich – und das war‘sAufgabe 68

W = G × p%

%-Zeichen in ExcelDas %-Zeichen in Excel – sowohl als Zeichen in der Zelle als auch als %-Zahlenformat – hat keine unwesentliche Bedeutung: Das %-Zeichen rechnet automatisch × 100 resp. /100, wie Sie sehen werden.

Hilfe: Ergebniszelle%-Zeichen sichtbar Zahlenformat % wählen

Einheit sichtbar %-Zeichen in die Formel schreiben (z. B. CHF, Stück etc.)

63

Excel-Grundlagen

Prozentsatz berechnen (p%)

Ausgangslage: Für einen Kleinkredit von 5000.00 (G) bezahlen Sie einen Jahreszins von 475.00 (W).

Frage: Wie hoch ist der Zins in Prozenten (p%)?


100 : 5000 × 475

oder: 475 × 100 : 5000 oder 475 : 5000 × 100

Erklärung

Für die Anzeige des %-Zeichens in der Ergebniszelle F15 müssen Sie das Zahlenformat % wählen (excellisch korrekt). Das setzt voraus, dass Sie nicht mit 100 multiplizieren, da das %-Format dies bereits tut (sonst multiplizieren Sie mit 10‘000).

Grundwert berechnen (G)

Ausgangslage: Für eine Hypothek [(G): Spezialkredit für den Kauf eines Hauses oder einer Wohnung] bezahlt eine Familie einen Bankzins (p%) von 2.1 %. Das entspricht einem Jahreszins (W) von 17‘850.00.

Frage: Wie hoch ist der Kredit (G), den Sie aufgenommen haben?


17‘850 : 2.1 × 100

oder 17‘850 × 100 : 2.1 oder 100 : 2.1 × 17‘850

Erklärung

Mit dem %-Zeichen in der Berechnungsformel C24 erübrigt sich wiederum die Multiplikation mit 100, weil das %-Zeichen das erledigt (excellisch korrekt). Sobald das %-Zeichen in der Zelle F22 (Zellbezug) ist, erübrigt sich die Multiplikation mit 100, da das %-Zeichen in der Zelle F22 das bereits erledigt (excellisch korrekt).

Preisaufschlag: zuzüglich Prozentsatz berechnen (G-neu)

Merken Sie sich: Bei einem Prozentaufschlag (W) muss ein Prozentsatz (p%) zu einem Nettopreis (G-alt) dazugerechnet werden. Der Bruttopreis (G-neu) ist somit über 100 %. Das sind beispielsweise Preisaufschläge, MWSt.-Zuschläge etc.

p% = 100 / G × W

G = W : p% × 100

Gneu = Galt + Galt : 100 × p%

64


1 Die «Punkt-vor-Strich»-Regel kennt sowohl Ihre Rechenmaschine wie auch Excel – Sie auch?

Ausgangslage: Ein Handwerker verlangt für seine Arbeit und das Material netto 1500.00 (G-alt). Auf der Rechnung wird noch 7.7 % Mehrwertsteuer (p%) ausgewiesen.

Frage: Wie hoch ist die Gesamtrechnung (G-neu) in Franken?


500 + 1500 : 100 × 7.7

alternativ: 1500 : 100 × 107.7

oder 1500 : 100 × 7.7 + 1500 oder 7.7 : 100 × 1500 + 15001

Erklärung

Die gewählte Berechnung nimmt den Grundwert (G-alt) und addiert dazu 7.7 % des Grundwertes, was dem Totalpreis von 107.7 % entspricht. Der Vorteil dieser Berechnungsart liegt auf der Hand: Sie können alles mit Zellbezügen berechnen. Sollte sich der MWSt.-Satz wieder einmal ändern, ändern Sie nur den Wert in der MWSt.-Zelle und Excel zeigt das korrekte Ergebnis an.

Preisabschlag: abzüglich Prozentsatz berechnen (G-neu)

Ausgangslage: Auf ein letztjähriges MountainBike-Modell, das bisher 2450.00 (G-alt) kostete, wird ein Rabatt von 12.5 % (p%) gewährt.

Merken Sie sich: Bei einem Prozentabschlag (W) muss ein Prozentsatz (p%) von einem Grundwert (G-alt) abgezogen werden. Der neue Preis (G-neu)ist somit (vorerst) kleiner als 100 %. Dazu gehören beispielsweise Preisabschläge, Rabatte, Skonti etc.

Frage: Was kostet das MountainBike jetzt noch (G-neu)?


2450 – 2450 : 100 × 12.5

alternativ 2450 : 100 × 87.5

Erklärung

Die gewählte Berechnung nimmt den Grundwert (G-alt) und subtrahiert 12.5 % des Grundwertes, was dem Totalpreis von 87.5 % entspricht. Der Vorteil dieser Berechnungsart liegt wiederum auf der Hand: Sie können alles mit Zellbezügen berechnen. Sollte sich der Rabatt nochmals ändern, können Sie wiederum nur den Wert in der Rabatt-Zelle anpassen und Excel zeigt das korrekte Ergebnis an.

Überlegung für Profis: Warum steht in der Zelle G43 «F40*(1 – F41)» diese 1? Was bedeutet Sie?

So, das war‘s! Mehr Möglichkeiten zum Rechnen mit Prozenten gibt es nicht. Also egal, ob Sie 5, 10 oder 100 Aufgaben ausrechnen. Sie passen immer zu einer der beschriebenen Berechnungsart.

Gneu = Galt – Galt : 100 × p%

65

Excel-Grundlagen

Diagramme

5

Ihr Problem: Die Problemstellung!

Was wollen Sie mit dem Diagramm aussagen? Das ist eine Herausforderung, wie Sie in der letzten Aufgabe vielleicht festgestellt haben: Was sagen die verschiedenen Diagramm genau aus?

Schauen Sie sich dazu die folgenden Erklärungen an.

Aussage ändern durch Wechsel der Zeilen und Spalten

Standard-Säulendiagramm Zeilen und Spalten wechseln

Aussage: In der Schweiz leben – mehr Schweizerinnen als Schweizer. – mehr Ausländer als Ausländerinnen

Aussage: In der Schweiz leben – mehr Schweizer als Ausländer. – mehr Schweizerinnen als Ausländerinnen.

Aussage ändern durch Stapelung oder Wahl eines anderen Diagrammtyps

gestapeltes Säulendiagramm Kreisdiagramm

Aussage: In der Schweiz leben insgesamt mehr Schweizerrinnen und Schweizer als Ausländerinnen und Ausländer.

Aussage: In der Schweiz leben – prozentual mehr Schweizer/-innen als

Ausländer/-innen. – leben doppelt so viele Schweizer-/innen wie

Ausländer/-innen.

Balken statt Säulen (hier auf 100% angezeigt/berechnet)

Wie Sie sehen, ändert die Auswahl von Balken statt Säulen nichts an der Aussage: – In der Schweiz leben sowohl mehr

Schweizerinnen und Schweizer als Ausländerinnen und Ausländer.

88


1 ... nicht eingerechnet die fehlenden Sozialleistungen, die ihr dadurch entgangen sind (fehlende/zu tiefe AHV-Beiträge und Pensionskassen-Gutschriften)!

Ausgangslage: In der Schweiz verdienen Frauen immer noch weniger als Männer – bei gleicher Arbeit und gleicher Ausbildung – wirklich etwas zum Nachdenken. Vom Amt für Statistik der Schweiz gibt es dazu einige interessante Zahlen.

A Öffnen Sie die Excel-Arbeitsmappe Lohnvergleich.

B Berechnen Sie zuerst die verlangten Angaben in den gelb unterlegten Zellen D5 bis D7 und E5 bis E7.

C Erstellen Sie aus dieser Tabelle ein aussagekräftiges Diagramm.

D Erstellen Sie aus der 2. Tabelle (A11 bis C14) ein gestapeltes Diagramm.

E Für Schnelle Wie hoch wäre der entgangene Lohn einer Frau, die mit 20 Jahren in den Beruf einsteigt und während des gesamten Berufslebens (bis 64 Jahre) 100 % gearbeitet hat?1

A Öffnen Sie die Excel-Arbeitsmappe Erwerbstätigkeit.

B Tabelle «auswärtige Beschäftigung»: Zeigen Sie mit einem geeigneten Diagramm auf, wie viele Frauen und Männer erwerbstätig sind.

Hinweis: Als Erwerbstätig gilt nach der Erwerbstätigenstatistik (ETS des Amtes für Statistik) jede Person, die während mindestens 1 Stunde pro Woche einer beruflichen Aktivität nachgeht.

C Schreiben Sie eine kurze Beurteilung, z. B. «Die Erwerbstätigkeit von Frauen wie auch von Männern hat in den letzten 8 Jahren stetig zugenommen. Rund 5 Millionen Menschen gehen einer Erwerbstätigkeit nach.»

D Tabelle «Inländer_Ausländer»: Zeigen Sie mit einem geeigneten Diagramm auf, wie hoch der Anteil Inländer/-innen resp. Ausländer/-innen im Jahr 2018 war.

E Beschriften Sie die Anteile mit den %-Werten.

F Tabelle «Sektoren»: Zeigen Sie mit einem geeigneten Diagramm auf, in welchen Sektoren die Erwerbstätigen im Jahr 2010 und im Jahr 2018 gearbeitet haben.

Erklärung: Sektor 1: Landwirtschaft, Sektor 2: Industrie und Baugewerbe, Sektor 3: Dienstleistung

G Lässt sich dazu eine Aussage machen? Welche? Schreiben Sie sie unter das Diagramm.

H Welche Aussage lässt sich mit einem Diagramm für den Sektor 1 machen? Erstellen Sie ein passendes Diagramm.

Für Frauen (und Männer) zum NachdenkenAufgabe 97

Erwerbstätigkeiten der Schweizer BevölkerungAufgabe 98

91

Diagramme

5.5 Repetition: Üben und vertiefen

Sie repetieren: Diagramm erstellen und anpassen, Diagrammtyp nach Fragestellung auswählen, nötige Achsen- und Datenbeschriftung sowie Legende und Diagrammtitel bestimmen

A Öffnen Sie die Excel-Arbeitsmappe Erfolg.

B Tabellenblatt «Umsätze»: Berechnen Sie zuerst die fehlenden Angaben in den gelb unterlegten Zellen. – Wählen Sie passende Zahlenformate.

C Überlegen Sie sich, welche Diagrammtypen sich für die folgenden Aufgaben eignen. – Erstellen Sie die Diagramme. – Verändern Sie anschliessend das eingefügte Diagramm nach Ihrem Gutdünken (Layout, Farbe etc.)

D 1. Diagramm: Monatsumsätze von Basel für das erste Halbjahr 20xx. Welchen Diagrammtyp haben Sie gewählt, warum? Was wäre auch noch möglich gewesen?

E 2. Diagramm: Umsatzanteile aller Niederlassungen am Gesamtumsatz im Juni 20xx. Welche Diagrammtypen eignen sich dafür, warum?

F Tabellenblatt «Mitarbeitende»: Erstellen Sie das abgebildete Diagramm. – Die Datenbeschriftung soll senkrecht (Ende ausserhalb) angezeigt werden. – Die Y-Achsenskalierung beginnt bei 100. – Die Y-Achse erhält zudem eine Achsenbeschriftung: in 1000 CHF. – Die Legend verschieben Sie unter den Diagrammtitel.

G Ändern Sie die Farbe ohne die Säulen einzeln bearbeiten zu müssen (Befehl: Farbe ändern).

Diagramm-Wahl nach FragestellungAufgabe 109

105

Diagramme

Sie repetieren: Diagramm erstellen, Diagrammanpassungen wie Linienfarbe, -breite, Datenbeschriftung, Achsenbeschriftungen und -skalierung, Hintergrund von Diagramm- und Zeichnungsfläche

A Öffnen Sie die Excel-Arbeitsmappe Gold.

B Erstellen und gestalten Sie das Diagramm genau nach der Abbildung.

C Das Diagramm muss genau im gelb unterlegten Bereich neben der Tabelle Platz haben.

D Folgende Punkte müssen Sie einhalten: – Linien mind. 6 pt breit, Farben nach Zellenfarben (Höchst- und Tiefstkurs) – höchster Kurs angeschrieben (Datenbeschriftung) – Diagrammfläche schwarz – Zeichnungsfläche: Bild gold.png (im Übungsordner) korrekt eingefügt

E Ergänzen Sie die Tabelle mit den Werten für 2018: – Jahreshöchstkurs 1358.50 $ pro Unze, Jahrestiefstkurs 1174.16 $ pro Unze

F Aktualisieren Sie das Diagramm, sodass auch die neuen Werte angezeigt werden.

Sie repetieren und ergänzen Ihr Wissen: 3D-Diagramme mit Farbverlauf erstellen, andere Formen für Säulendiagramme wählen, Reihenoptionen (Abstandtiefe und -breite) anpassen

A Öffnen Sie Excel-Arbeitsmappe Verkehrs-Abo.

B Tabellenblatt «Abos nach Geschlecht»: Erstellen Sie ein Zylinder-Diagramm – Form: Zylinder (Reihenoptionen, bei markierter Säulenreihe) – Abstandtiefe und -breite: je 100% (probieren Sie auch andere Masse aus) – Farbverlauf in blau und orange, von unten nach oben (2 Farben)

GoldkursAufgabe 110

öV (öffentlicher Verkehr): Abo-VergleichAufgabe 111

106


C Für Profis Wieso gibt es in der Zelle E7 ein Total von über 100 %? Was vermuten Sie?

D Tabellenblatt «Abos nach Alter»: Erstellen Sie mit den geeigneten Werten das abgebildete Diagramm.

E Erstellen Sie ein weiteres Diagramm, das folgende Fragestellung beantwortet: Welche Altersgruppe besitzt den höchsten Anteil von Halbtax-Abos?

F Welchen Diagrammtyp haben Sie verwendet, warum?

G Überlegen Sie sich zu zweit für eine der Tabellen auf den beiden Tabellenblättern eine weitere Frage, die Sie mit einem Diagramm veranschaulichen wollen. Die Frage lautet:

H Wenn Sie Lust und Zeit haben, erstellen Sie daraus ein aussagekräftiges Diagramm.

Sie repetieren: Fragestellungen grafisch mit einem geeigneten Diagramm und ausgewählten Werten beantworten, Diagramme mit grafischen Elementen ergänzen

A Öffnen Sie die Excel-Arbeitsmappe Schweizer Bevölkerung.

B Arbeiten Sie zu zweit, indem Sie sich eine Frage stellen, die Sie anschliessend mit einem geeigneten Diagramm darstellen/beantworten (Diagrammtitel = Fragestellung).

Beispiel: Wie ist die Verteilung der Schweizer resp. der ausländischen Frauen und Männer im Kanton Luzern.

Tipp: Für einige Fragestellungen müssen Sie die vorhandenen Werte zu einer neuen Tabelle zusammenstellen (verdichten).

Ideen: Vergleich von verschiedenen Kantonen oder von Grossregionen, nur Frauen resp. nur Männer

Fragen zur Schweizer BevölkerungAufgabe 112

107

Diagramme

Formeln mitFunktionen

7

1 Zur Erinnerung: Zahlenformate sind eine Art MakeUp, sie dienen nur der gewünschten Anzeige von Werten und verändern keinen Wert (mit Ausnahme vom %-Format).

2 'Zahl' kann auch ein bereits berechneter Wert in einer Zelle oder einer Funktion (z. B. MITTELWERT) sein, die Excel vorher berechnen muss, bevor gerundet werden soll (siehe dazu: verschachtelte Funktionen).

Funktion RUNDEN: DezimalrundungBisher haben Sie Ergebnisse, die mehr als die üblichen Stellen anzeigten (z. B. bei Währungen: 2 Dezimalstellen), so belassen, auf dem Register START/Zahlen den Befehl Dezimalzahlen löschen oder einfach mit dem Zahlenformat 'Währung/Buchhaltung', 2 Dezimalstellen «optimiert» – ganz nach dem Grundsatz «aus den Augen, aus dem Sinn».

Für Excel war das nicht so; intern wurde mit der korrekten Zahl gerechnet, wie die folgende Abbildung in der Kontroll-Spalte [2 × 2 = 3.65]1 zeigt:

Damit Excel wirklich «Abstand» von den «unnötigen» (unerwünschten) Dezimalstellen nimmt, muss die Zahl gerundet werden. Auf das oben abgebildete Beispiel bezogen, müssen Sie Excel klar machen, dass Sie nur noch mit der ganzen Zahl (2) weiterrechnen wollen. Dazu runden Sie die Zahl (1.825). So zeigt Excel bei der Multiplikation mit 2 auch das korrekte Ergebnis an:

Die Funktion RUNDEN rundet einen Wert auf die angegebene Anzahl Stellen auf oder ab. Die Rundungsregeln werden eingehalten, d. h. beispielsweise bei Noten: 4.24 wird auf 4.0 abgerundet, 4.25 wird auf 4.5 aufgerundet oder 4.74 wird auf 4.5 abgerundet, 4.75 wird auf 5.0 aufgerundet.

Funktionsaufbau

Erklärung Zahl: was runden2 Anzahl_Stellen: auf wie viele Stellen runden

Stellen Erklärung Beispiel mit 3‘758.6793 km

3 3 Dezimalstellen nach dem Komma Tausendstel 3‘758.679 km

2 2 Dezimalstellen nach dem Komma Hundertstel 3‘758.68 km

1 1 Dezimalstelle nach dem Komma Zehntel 3‘758.7 km

0 0 Dezimalstellen nach dem Komma Ganzzahl 3‘759 km

–1 auf 10 km genau Zehner 3‘760 km

–2 auf 100 km genau Hunderter 3‘800 km

–3 auf 1000 km genau Tausender 4‘000 km

So zeigt Excel bei der Multiplikation mit 2 auch das korrekte Ergebnis an:

= RUNDEN(Zahl;Anzahl_Stellen)

– +0

Anzahl Vorkommastellen

auf Hunderter=RUNDEN(Zahl;–2)

auf Hundertstel=RUNDEN(Zahl;2)

Anzahl Nachkommastellen

136


A Öffnen Sie die Excel-Arbeitsmappe Dezimalrundung.

B Tabellenblatt «Zahlenformat»: Sie sehen ein ähnliches Beispiel wie auf den vorherigen Abbildungen.

C Weisen Sie den Zahlen der oberen Tabelle (B4 bis B7) die verlangten Zahlenformate zu und multiplizieren Sie diese Zahlen in Spalte C mit 2.

D Arbeiten Sie bei der unteren Tabelle mit dem Funktions-Assistenten. Multiplizieren Sie auch hier die erhaltenen Werte in Spalte C mit 2.

E Tabellenblatt «Rundungen»: Ergänzen Sie die Spalte B und C mit den verlangten Rundungen (siehe Vorgaben in Spalte A). Hinweis: Die Rundungen beziehen sich immer auf die Zelle B3, resp. C3. Tipp: Arbeiten Sie hier mit absoluten (gemischten) Bezügen, damit Sie nicht alle Funktionen neu tippen, sondern nur anpassen müssen.

F Wie heissen die korrekten Funktionen in B6 und C9?

Funktion RUNDEN: Einheitenrundung (nicht dezimal)Für nicht-dezimale Rundungen gibt es verschiedene Funktionen. Nehmen Sie die beschriebene Funktion, sie produziert im Gegensatz zur Funktion VRUNDEN keine Fehler.

Funktionsaufbau

Erklärung Zahl: was runden Einheit: Wie oft haben z. B. 5 Rappen im Betrag Platz (/0.05 für 5-Rappenrundung) oder (/ 0.5 für halbe Noten); Ganzzahl (;0): vorheriges Ergebnis auf eine ganze Zahl runden

Erklärung: Anweisung für Excel (Einzelschritte)– Runde die Zahl in der Zelle – dividiere sie durch die Einheit – runde das Ergebnis, das du mit der Division erhalten hast, auf eine ganze Zahl (;0) – multipliziere die erhaltene ganze Zahl wieder mit der Einheit (z. B. 0.5 für halbe Noten)

A Öffnen Sie die Excel-Arbeitsmappe Einheitenrundung.

B Tabellenblatt «Verkaufspreise»: Berechnen Sie die Verkaufspreise, gerundet auf 5 Rappen.

C Tabellenblatt «Noten»: Berechnen Sie die Notendurchschnitte und runden Sie sie auf 1 Dezimalstelle.

D Runden Sie die Semesternoten auf halbe Noten: Die Semesternote besteht aus den zwei Prüfungen sowie dem Durchschnitt der Blitzer (auf 1 Dezimalstelle gerundet = 3. Prüfungsnote).

E Von jeder Prüfung, jedem Blitzer und den ungerundeten und gerundeten Noten will man die höchste, tiefste Note und den exakten Durchschnitt wissen.

DezimalrundungAufgabe 139

= RUNDEN(Zahl/Einheit;0)*Einheit

EinheitenrundungAufgabe 140

L

Ü

137

Formeln mit Funktionen

1 Als Alternative zur Funktion ABRUNDEN eignet sich die Funktion GANZZAHL(Zahl), wenn Sie auf ganze Zahlen abrunden wollen: Sowohl 3.5 als auch 3.4 werden auf 3.0 abgerundet.

Kritisch: Funktion VRUNDEN Die Funktion VRUNDEN rundet eine Zahl auf eine gewünschte Einheit auf oder ab, aber ...

Funktionsaufbau

Erklärung Zahl: zu rundender Wert Vielfaches: gewünschte Einheit (z. B. 5 Rappen= 0.05)

Die Überlegungen am Anfang sind immer die gleichen: Auf welche Einheit soll der Wert gerundet werden: 5-Rappen-Rundung: 0.05 Franken oder halbe Noten: 0.5 Noten

Achtung! Sicher ist sicher: Die VRUNDEN-Funktion produziert Fehler!

Wie Sie auf der Abbildung sehen, liefert VRUNDEN nicht immer das korrekte Resultat.

Wenn Sie also sicher sein wollen, verwenden Sie die untere Funktion – oder kontrollieren das Ergebnis.

Auf- und abrunden lassen (dezimal)Ausgangslage: Sie erhalten eine Liste von Zahlen, die entweder sehr gross oder mit mehreren Dezimalstellen sehr genau sind. Für eine Präsentation müssen Sie diese Zahlen so runden, dass sie zwar noch korrekt sind, aber für das Publikum einfacher lesbar und verständlicher werden.

Beispiel 1: Im vergangenen Jahr erreichten wir eine Umsatzsteigerung von knapp 10 % – statt 9.3754 %. Beispiel 2: Im vergangenen Jahr erzielten wir einen Umsatz von über 3.5 Mio Franken – statt 3‘547‘129.–.

Dafür stellt Excel zwei Funktionen zur Verfügung:

Funktionsaufbau

Erklärung ...rundet die Zahl auf die verlangten Stellen auf

=AUFRUNDEN(903.5; 0) ergibt 904 =AUFRUNDEN(17.5038;3) ergibt 17.504 =AUFRUNDEN(17.5038;-1) ergibt 20

Erklärung ...rundet die Zahl auf die verlangten Stellen ab1

=ABRUNDEN(903.5;0) ergibt 903 =ABRUNDEN(17.5038;3) ergibt 17.503 =ABRUNDEN(17.5038;-1) ergibt 10

A Öffnen Sie die Excel-Arbeitsmappe Auf-Abrunden.

B Tabellenblatt «CH-Bevölkerung»: Runden Sie die Bevölkerungszahlen in der Spalte C auf 1 Dezimalstelle ab. Hinweis: Zahlenformat mit 3 Nachkommastellen, damit Sie die Rundungen kontrollieren können.

C Runden Sie die Bevölkerungszahlen in der Spalte D auf 1 Stelle auf.

D Tabellenblatt «Weltbevölkerung»: Die Liste der Länder mit Flächen und Bevölkerungszahlen sind zu genau. Runden Sie die Flächenangaben auf 100 km2 genau ab und die Bevölkerungszahlen auf 100‘000 genau auf.

E Wieso wird in der Zelle C10 kein Resultat (0) angezeigt?

= VRUNDEN(Zahl;Vielfaches)

= ABRUNDEN(Zahl;Anzahl_Stellen) = AUFRUNDEN(Zahl;Anzahl_Stellen)

Bereinigte ZahlenAufgabe 141

138


1 Die Funktion ABRUNDEN(Zahl/Einheit;0) geht genau gleich, macht aber mit der Einkaufsliste für ein Rezept keinen Sinn.

2 Die Funktion ABRUNDEN(ZAHL/Einheit;0)*Einheit geht ebenso genau gleich.

Auf- und abrunden lassen (Einheiten, nicht dezimal)Wie beim Runden können Sie auch auf gewünschte Einheiten auf- und abrunden. Hilfreich beispielsweise, wenn Sie die Einkaufsliste für ein Rezept erstellen müssen. Da Sie keine halben 1∕4-Liter Rahm kaufen können, müssen Sie die Menge auf ganze 1∕4-Liter Rahm aufrunden.

Funktionsaufbau1 für die Anzahl Einheiten

Erklärung Zahl: zu rundender Wert Einheit: gewünschte Einheit (z. B. ¼ Liter = 0.25) 0: auf Ganzzahl runden (ergibt die Anzahl)

Funktionsaufbau2 für die Menge

Erklärung Zahl: zu rundender Wert Einheit: gewünschte Einheit (z. B. ¼ Liter = 0.25) 0: auf Ganzzahl runden *Einheit: ergibt die Menge

A Öffnen Sie die Excel-Arbeitsmappe Ganze.

Tabellenblatt «American Cookies»: Erstellen Sie die Einkaufsliste für das Rezept. Beachten Sie dabei: Angenommen Sie brauchen lediglich 25 g Butter. Im Supermarkt können Sie nicht von einer verpackten Portion Butter (z. B. 200g) 25 g von einer Portion Butter abschneiden und an der Kasse bezahlen.

B Berechnen Sie nebst der Anzahl Verkaufseinheiten (Spalte F) auch die Einkaufsmengen (Spalte H).

C Tabellenblatt «Support-Abrechnung»: Das Support-Team berechnet die aufgewendete Zeit im Viertelstunden-Takt, d. h. bei einem Support-Aufwand von 20 Minuten berechnet es 2 Zeiteinheiten zu je ¼ Stunde à 46.25.

D Berechnen Sie die anzurechnenden Zeiteinheiten (ganze Zahl) sowie die Kosten pro Tag.

E Berechnen Sie die effektiv aufgewendete Zeit, die gesamten Zeiteinheiten und das Total der Januar-Kosten.

Für Schnelle – Es wird ein Rabatt von 5 % gewährt. Anschliessend wird die Mehrwertsteuer von 7.7 % dazugerechnet. – Wie hoch ist jetzt die Rechnung für den Kunden? – Wie hoch ist sie auf 50 Rappen abgerundet?

= AUFRUNDEN(Zahl/Einheit;0)

= AUFRUNDEN(Zahl/Einheit;0)*Einheit

Ganze Einheiten berechnen lassenAufgabe 142

139

Formeln mit Funktionen

Für Profis: WENNs-FunktionHinweis: Diese Funktion ist ab Office 365 und Office 2019 verfügbar.

Wie Sie im vorherigen Beispiel gesehen haben, braucht es ziemlich viel Konzentration, damit bei der Verkettung mehrerer WENN-Bedingungen keine Klammer vergessen geht. Dafür eignet sich die WENNS-Funktion.

Funktionsaufbau

ErklärungWahrheitstest1: Bedingung, die zutrifft (WAHR) oder nicht zutrifft (FALSCH) Wert_wenn_wahr1: Überprüfung, ob Bedingung erfüllt ist etc.

Achtung – Beachten Sie, dass Sie die Bedingungen in der richtigen Reihenfolge angeben (entweder von oben nach unten

oder von unten nach oben). Excel prüft der Reihe nach und stoppt bei der ersten Bedingung, die zutrifft.

– Die bisherige SONST-Bedingung «greift» hier nicht. Sie werden aber schnell eine Lösung dafür finden.

A Öffnen Sie die Arbeitsmappe WENNs. Hinweis: Es handelt sich um dieselben Tabellen wie in Aufgabe 159.

B Tabellenblatt «Noten_Bewertung»: Bei der Note 6 soll «sehr gut», bei der Note 5 «gut», 4 «genügend» stehen. Alle weiteren Notenwerte unter 4 werden mit «ungenügend» quittiert.

C Wie lautet die korrekte WENNS-Funktion?

D Tabellenblatt «Punkt_Bewertung»: Wenn die Kandidatinnen und Kandidaten mindestens 81 Punkte erreicht haben, erhalten sie die Bewertung «sehr gut», für mind. 61 Punkte «gut», für min. 53 Punkte «bestanden», unter 53 Punkten haben sie nicht bestanden.

E Tabellenblatt «Jubiläumsgeschenk»: Sie kennen die Tabelle bereits. Die Geschäftsleitung hat entschieden, dass die Ausgaben zu hoch sind. Darum bekommen jetzt Kundinnen und Kunden, ... – deren Bestellmenge über 1‘200.– liegt, einen 10%-Gutschein. – deren Bestellmenge zwischen 1200.– und 500.– liegt, eine Taschenagenda. – deren Bestellmenge kleiner als 500.– beträgt, eine (vorgedruckte) Jubiläumskarte.

F Kunden ohne Bestellung sollen aus der Kundenkartei gelöscht werden (Eintrag: «aus Kundenkartei entfernen»).

G Wählen Sie für den Eintrag «aus Kundenkartei entfernen» folgende bedingte Formatierung: rote Zellfüllung mit weisser Schriftfarbe.

H Lassen Sie sich nur diejenigen Kunden anzeigen, die aus der Kundenkartei entfernt werden sollen. Womit haben Sie das gelöst?

= WENNS(Wahrheitstest1;Wert_ wenn_wahr1;Wahrheitstest2;Wert_wenn_wahr2; ...)

WENNs – statt verschachtelnAufgabe 160

154


5Ta

bel

len

kalk

ula

tio

n E

xcel

365

201

9M

od

ul 5ISBN 978-3-906244-27-3

Zelle Zeile MITTELWERT() Formel Spalte

Tabellenblatt Funktion Kreisdiagramm Tabellen-

register WENN() Arbeitsmappe absolut Zellbezug relativ

Funktion SUMME() verknüpfen Balkendiagramm MIN() dann...

sonst Klammern Bezug Zahlenformat MAX() Buchhaltung oder

Währung RUNDEN() Datumsformat Prozentrechnen Punkt vor Strich

Zeitberechnungen VRUNDEN() Pivot-Tabelle AUFRUNDEN() addieren radi-

zieren ABRUNDEN() Bedingungen ZÄHLENWENN() subtrahieren SUMMEWENN()

multiplizieren transponieren dividieren potenzieren sortieren filtern Top 10 ...

2019 kalkulation Excel - Lehrmittel - EdulinoAusgangslage: Ein Handwerker verlangt für seine Arbeit...

Documents

Transcript of 2019 kalkulation Excel - Lehrmittel - EdulinoAusgangslage: Ein Handwerker verlangt für seine Arbeit...