Tabellenkalkulation. Inhalt (1) Einführung in die Tabellenkalkulation Berechnungen in Excel...

Tabellenkalkulation

Inhalt (1)

Einführung in die Tabellenkalkulation

Berechnungen in Excel– Formeln und Bezüge

– Funktionen

Arbeiten mit Datenlisten– Sortieren und Filtern

Inhalt (2)

Funktionen– SVERWEIS– Wenn

Diagramme

Interaktion

Pivot-Tabellen– Pivot-Tabellen-Assistent– Layouteinstellungen,Feldeinstellungen

Einführung

Zweck einer Tabellenkalkulation

Datenblatt enthält – Text, Zahlen und Berechnungsvorschriften

(Formeln)

Dynamische Berechnungen– Bei Eingangsdatenänderungen ändern sich

automatisch die Ausgangsdaten

Speichern von Daten in einer Matrix

Einführung (1)

Spalten werden in Excel mit Großbuchstaben bezeichnet z.B. „D“

Zeilen werden mit Zahlen bezeichnet z.B. „23“

Eine Zelle ergibt sich aus der Kombination von Zeile und Spalte z.B. „D23“– wird als Zellbezug bezeichnet– „Adresse“, wird in Formeln verwendet

Einführung (2)

Inhalt einer Zelle– Text (mit Formatierungen analog zu Word)– Zahlen– Formeln (Berechnungsvorschriften) - ergeben

wieder Text oder Zahlen

Formatierung– über Format/Zellen - insbesondere für

Zahlenformate

Einführung (3)Zelle: Bestehend aus Zahl der Zeile und Buchstaben der Spalte

Formeln und Bezüge

Formeln und Bezüge(1)

Berechnungen werden in Excel mit Formeln durchgeführt z.B.=B6*B8 Multiplikation der Zellen B6 mit B8Jede Formel beginnt mit einem „=„-ZeichenKomplexe Formeln werden von Excel automatisch über den Funktionsassistenten bereitgestelltenthalten Funktionen, Bezüge, Werte

Formeln und Bezüge (2)

Formeln können in der Bearbeitungszeile oder in der betreffenden Zelle selbst eingetragen werden

Formeln und Bezüge (3)

1. Bezüge sind die „Adressen“ der Zellen

2. Es gibt drei verschiedene Bezüge1. relative Bezüge

2. absolute Bezüge

3. gemischte Bezüge

Formeln mit relativem Bezug

Schreibweise: z.B. D5

Formeln mit relativem Bezug können problemlos kopiert werden

beim Verschieben von Formeln wird diese angepasst

Formeln mit relativem Bezug

9

=D9*2

12

=B5*2

Die Formel aus B7 wird nach C11 kopiert. Sie passt sich aufgrund des relativen Bezuges an

Übung

Das Unternehmen „Web-Content“ hat sieben Angestellte. Berechnen sie das Gehalt der jeweiligen Angestellten. Die notwendigen Daten finden Sie in der Datei „Bezuege.xls“

Formeln mit absolutem Bezug

Schreibweise: z.B. $D$5

Dollar Zeichen vor dem Spaltenbuchstaben und vor der Zeilennummer

Kopiert man eine Formel mit einem absoluten Bezug verändert sich diese nicht

Formeln mit absolutem Bezug

=$F$5*7

=$F$5*7

17

Die Formel aus C7 wird nach D11 kopiert. Sie passt sich aufgrund des absoluten Bezuges nicht an

Übung

Im Unternehmen „Web-Content“ ist es üblich das jeder Mitarbeiter € 20/Monat in die Betriebskassa einbezahlt. Berechen das Gehalt jedes Angestellten nach Einbezahlung der € 20 in die Betriebskassa. Die notwendigen Daten finden Sie in der Datei „Bezuege.xls“

Formeln mit gemischtem Bezug

Schreibweise: z.B. $C6

Dollarzeichen vor Spaltenbuchstabe oder Zeilennummer

Kopieren einer Formel ist möglich – absoluter Teil der Formel ändert sich nicht

Formeln mit gemischtem Bezug

13

9

=$D10*3

=$D6*3

Die Formel aus B8 wird nach C12 kopiert. In Zelle B8 wird die Zahl aus Zelle D6 mit 3 multipliziert. Die Formel kann kopiert werden, da die zu multiplizierenden Zahlen in der Spalte D stehen, welche als absolut angeben wurde

Funktionen

Funktionen - allgemein

Excel verfügt über mehr als 300 Funktionen

Eingeben von Funktionen erfolgt mittels Funktionsassistenten– Einfügen– Funktionen oder– über Shortcut

Funktionsassistent

Auswahl der einzelnen Funktionen nach Name und Kategorie

Syntax (1)

Neben dem Funktionsassistenten bietet Excel auch die Möglichkeit Rechen-operationen durch Eintippen der entsprechenden Formel durchzuführen.

Formeln können entweder in die Befehls-zeile oder in die Zelle, in der später das Ergebnis erscheinen soll, eingegeben werden.

Syntax (2)

Die zur Berechnung verwendeten Zeichen werden als Operatoren bezeichnetdie wichtigsten Operatoren:– „+“ Addition– „-“ Differenz– „*“ Produkt– „/“ Division– „^“ Potenz

Syntax (3)

Jede Funktion kann Argumente besitzen, werden in Klammer angegeben (oder bei Assistenten in die entsprechenden Felder).

Die Trennung aneinandergereihter Argumente erfolgt durch „ ; “, z.B. =Summe(1;2;3;)

Zellbereiche werden z.B. durch „D23:D45“ dargestellt.

Funktionen – „Summe“

Funktion – „Summe“ (1)

Summenbildung der Gewinne:

Funktion – „Summe“ (2)

summieren von Zahlen

Vorgehensweise:– aufrufen des Funktionsassistenten– auswählen der Kategorie „Math&Trigonom“– auswählen der Funktion „Summe“– oder einfach die Formel eintippen

Auswahl der Funktionskategorie „Math. &Trigonom.“

Auswahl der benötigten Funktion „Summe“

Funktion – „Summe“ (3)

Funktion – „Summe“ (4)

Ergebnis der Summenbildung:

Formel mit der die Summe errechnet wurde

Funktionen – „Mittelwert“

Funktion – „Mittelwert“ (1)

Berechnung des mittleren Gewinns

Funktion – „Mittelwert“ (2)

Bildung von Durchschnittswerten z.B. Durchschnittsgewinn mehrerer FilialenVorgehensweise: – aufrufen des Funktionsassistenten– auswählen der Kategorie „Statistik“– auswählen der Funktion „Mittelwert“– oder Eingabe der Formel

Funktion – „Mittelwert“ (3)

Ergebnis aus der Berechnung des mittleren Gewinns

Formel mit der der Mittelwert errechnet wurde

Übung

Öffnen Sie die Datei „Umsatz.xls“ und berechnen Sie für jeden Kunden den Gesamtumsatz sowie den mittleren Umsatz aller drei Jahre, und den Gesamtumsatz und den mittleren Umsatz aller drei Jahre über alle Kunden.

Funktionen – „Summewenn“

Funktion – „Summewenn“ (1)

findet Anwendung, wenn aus einer Datenbank Daten nach einem bestimmten Kriterium summiert werden sollen z.B. Summe der Gehälter bzw. Summe der Löhne

Vorgehensweise– aufrufen des Funktionsassistenten

– auswählen der Kategorie „Math&Trigonom“

– auswählen der Funktion „Summewenn“

Zellbereich, welcher berechnet werden soll

Funktion – „Summewenn“ (2)

Kriterium nach dem die Summe gebildet werden sollZahlenbereich der

addiert werden soll

Manuell einzugebende Formel

Kontaktfenster des Funktionsassistenten

Funktion – „Summewenn“ (3)

3 auszufüllende Felder im Kontaktfenster– Das erste Argument gibt den Bereich an für den

bestimmte Bedingungen erfüllt sein müssen– Das zweite Argument gibt an welche

Merkmalsausprägungen vorliegen sollen– Das dritte Argument gibt die Feldbezeichnung

für die Werte an, die addiert werden sollen

Übung

Ergänzen Sie die Datei „Umsatz.xls“ um den Umsatz in Wien und den Umsatz in Linz im Jahr 2001.

Sortieren

Sortieren (1)

Die Funktion „Sortieren“ ermöglicht das Sortieren von Daten in einer Tabelle nach bestimmten Kriterien

Vorgehensweise:– Selektieren des Bereiches– Daten– Sortieren– Auswahl des „Sortierschlüssels“

Sortieren (2)

Sortieren der Filialen in der folgenden Tabelle nach dem Alphabet:

Angabe des zu sortierenden Feldes in Excel

Angabe der Sortierrichtung

Sortieren (3)

Sortieren (4)

Vorgehensweise:– Daten– Sortieren– Aufsteigend

Sortieren (5)

Ergebnis des Sortiervorganges:

Übung

Sortieren Sie die Kunden in der Datei „Umsatz.xls“ aufsteigend nach ihrer Kundennummer.

SVERWEIS

Funktion – „SVERWEIS“ (1)

Der SVERWEIS ist eine Suchfunktion

– sucht nach einem Wert in der ersten Spalte einer Matrix

– liefert den Wert einer beliebigen Spalte als Ergebnis

Funktion – „SVERWEIS“ (2)

Vorgehensweise

– aufrufen des Funktionsassistenten– auswählen der Kategorie „Matrix“– auswählen der Funktion „SVERWEIS“

Funktion – „SVERWEIS“ (3)Eingabe des Objektes nach welchem in Spalte 1 der nachfolgend angebenen Matrix gesucht werden soll

Angabe des Bereiches in dem gesucht werden soll

Angabe der Spalte, welche die gewünschten Werte enthält.

Eingabe des Wortes „FALSCH“ notwendig, da eine exakte Übereinkunft gewünscht ist

Funktion – „SVERWEIS“ (4)

Die Funktion sucht immer den größten Wert, der in einer aufsteigend sortierten Tabelle kleiner oder gleich dem Suchkriterium ist

Funktion – „SVERWEIS“ (5)

Im Falle einer gewünschten exakten Übereinstimmung muss die automatische Sortierfunktion des „SVERWEIS“ ausgeschaltet werden:– Attribut „Bereich Verweis“ auf FALSCH

Übung

In der Datei „Mitglieder.xls“ finden Sie eine Liste von Mitgliedern. Finden Sie heraus wie hoch der Mitgliedsbeitrag ist den Vogel bezahlt, wie viel ihm das Abonnement kostet und ob und wie viel Geld er noch für eine Spende erübrigt.

Wenn

Funktion – Wenn (1)

„Wenn“ dient zur logischen Unterscheidung– überprüft, ob eine Bedingung erfüllt ist, und

gibt je nachdem etwas aus– Ausgabe kann Text (in „“), Zahl oder eine

Formel sein

Funktion – Wenn (2)

Bestandteile (Argumente)– Prüfung: logischer Ausdruck, z.B. D5>10– Dann_Wert: Ausgabe, wenn Prüfung

erfolgreich– Sonst_Wert: Ausgabe, wenn Prüfung nicht

erfolgreich

Übung

Geben Sie zu jedem Kunden in der Datei „Umsatz.xls“ an, ob der Gesamtumsatz aller 3 Jahre ausreichend ist oder nicht. Dies ist er, wenn er 1.000.000 EURO überschreitet.

Diagramme

Grundsätzliches

Diagramme sollten übersichtlich und gut lesbar sein

Farbliche Abstimmung – keine zu bunten und somit nicht mehr aussagekräftigen Diagramme

Diagrammüberschriften sollten immer den gezeigten Inhalten entsprechen

Verwendung

Diagramme werden immer zur grafischen Veranschaulichung von Daten verwendet

Komplexe Sachverhalte können anhand von Diagrammen vereinfacht gezeigt bzw. präsentiert werden

Diagramm bleibt mit den Daten verbunden: Daten ändern sich - Diagramm ändert sich

Vorgehensweise (1)

Einfügen

Diagramm (od. Shortcutsymbol )

Der Diagrammassistent erscheint

Auswählen eines geeigneten Diagrammtyps

Markieren des Datenbereiches aus dem das Diagramm erstellt werden soll

Dient zur Auswahl des einzelnen Diagrammtyps

Vorgehensweise (2)

Diagrammbeispiele

Ausgangstabelle:

Säulendiagramm

Gewinn 2001

€ 0

€ 200.000

€ 400.000

€ 600.000

€ 800.000

€ 1.000.000

€ 1.200.000

Braunau Ried Leoben Kagenfurt

Gewinn 2001

Balkendiagramm

Gewinn 2001

€ 0 €200.000

€400.000

€600.000

€800.000

€1.000.00

0

€1.200.00

0

Braunau

Ried

Leoben

Kagenfurt

Gewinn 2001

Kreisdiagramm

Gewinn 2001

Braunau

Ried

Leoben

Kagenfurt

3D - Balkendiagramm

€ 0 € 200.000 € 400.000 € 600.000 € 800.000€ 1.000.000

Braunau

Ried

Leoben

Kagenfurt

Gewinn 2001

Gewinn 2001

Übung

Erstellen Sie ein Diagramm zu den Daten in der Datei „Umsatz.xls“.

Interaktion

Interaktion

Verwendung von insbesondere Diagrammen, die mit Excel erstellt wurden, in anderen Programmen wie z.B. Word

2 grundsätzliche Möglichkeiten– mit Verbindung– ohne Verbindung

Interaktion

Kopieren– in Excel markieren und Bearbeiten-Kopieren

(STRG-C) bzw. in Word Bearbeiten-Inhalte Einfügen

– reine Kopie, keine Verbindung mehr

Verknüpfen– wie kopieren, jedoch beim Einfügen Option

Verknüpfen aktivieren, Änderungen wirken sich aus

Übung

Fügen Sie das Diagramm aus der vorangegangenen Übung in ein neues Word-Dokument ein. Es soll dabei mit den Daten verbunden bleiben.

Pivot-Tabellen

Verwendung

Pivot-Tabellen sind Häufigkeitsauszählungen

Pivot-Tabellen lassen eine komfortable Datenanalyse zu

Pivot-Tabellen-Assistent

der Pivot-Tabellen-Assistent unterstützt bei der Erstellung von Pivot-Tabellen

Vorgehensweise:– Daten– Pivot-Tabellenbericht

Auswahl der Datenquelle, die analysiert werden soll

Erstellen einer Pivot-Tabelle (1)

Auswahl des zu analysierenden Datenbereiches.

Erstellen einer Pivot-Tabelle (2)

Erstellen einer Pivot-Tabelle (3)

Durch einfaches ziehen der einzelnen Buttons mit der Maus kann das Layout der Pivot-Tabelle bestimmt werden

Erstellen einer Pivot-Tabelle (4)

Zeile– Kategorien– mehrere Elemente: Sub-Kategorien

Spalte– analog zu Zeilen

Daten– zusammengefasste Werte (durch Doppelklick

kann Art geändert werden)

Wählen Sie aus wo (in welchem Datenblatt) das Ergebnis der Analyse dargestellt werden soll

Erstellen einer Pivot-Tabelle (5)

Pivot-Tabelle als Ergebnis der vorangegangen Schritte

Erstellen einer Pivot-Tabelle (6)

Datenfeld

Beschriftungsfeld

Sortieren einer Pivot-Tabelle

Sortieren einer Pivot-Tabelle (1)

Die Funktion „Sortieren“ ermöglicht es Daten welche in einer Pivot-Tabelle dargestellt sind zu sortierenVorgehensweise:– Beschriftungsfeld markieren– Daten– Sortieren– oder Shortcut

Sortieren einer Pivot-Tabelle (2)

Kontaktfenster in welchem die Sortieroptionen eingegeben werden können

Layout- und Feldeinstellungen

Layouteinstellungen (1)

Felder können im nachhinein leicht entfernt oder hinzugefügt werden

Wenn nötig ändert Excel automatisch die zugrundeliegende Funktion (z.B. die Funktion „Summe“ wird in die Funktion „Anzahl“ umgewandelt)

Layouteinstellungen (3)

Durch einfaches ziehen der einzelnen Buttons mit der Maus kann das Layout der Pivot-Tabelle verändert werden

Feldeinstellungen (1)

Dient der Veränderung der in der Pivot-Tabelle verwendeten Felder

Vorgehensweise– markieren des betreffenden Datenfeld– klicken des rot markierten Buttons

Feldeinstellungen (2)

Um den rot markierten Bereich zu erhalten muss die Schaltfläche „Optionen“ gedrückt werden

Mit der Schaltfläche „Zahlen“ kann der Wertebereich der Pivot-Tabelle formatiert werden

Mit der Schaltfläche „Ausblenden“ kann das aktuelle Datenfeld entfernt werden

Feldeinstellungen (3)

Vergleichsvariable

Zum prozentuellen Vergleich vorhandener Werte mit einer bestimmten Variable

interessierende Ausprägung der Variable

Feldeinstellungen (4)

Ausblenden des aktuellen Beschriftungsfeldes

Änderung der Feldbeschriftung

Aufrufen des Kontaktfensters durch Doppelklick auf das entsprechende Beschriftungsfeld

Übung

In der Datei „Kunden.xls“ finden Sie Kunden und deren Einkäufe. Erledigen Sie die auf den weiteren Datenblättern zu findenden Aufgaben.