D i p l . - I n g .
W o l f g a n g S c h u r i a n , B A 9 5 6 0 Fe ld kirch e n | A u stria +4 3 (0 ) 6 7 6 / 7 2 4 6 8 1 4
© Fotolia 2014
BUSINESS – SOFTWARE
GRUNDLAGEN TABELLEN-
KALKULATIONEN
1. Grundlagen Tabellenkalkulationen
2. Grafische Datenaufbereitung
3. Funktionen und Pivot-Tabellen
1.1 Einstieg in Tabellenkalkulationen 1.2 Formeln
1.3 Aussehen von Tabellenblättern
Erstellen Sie eine Tabelle zur Auflistung von Komponenten für einen digitalen Arbeitsplatz:
-> https://support.office.com <-
Einzug vergrößern Zeilen- Spaltenbreite verändern
Spaltenbeschriftung
Buchhaltungszahlenformat
Beschriftung: Daten eingeben:
Auto-Ausfüllen Multiplikation
Summe bilden
Tabelle ausdrucken:
* Umbruchvorschau,
* Seite einrichten (Blatt->Druck- breich->Drucktitel),
* Kopf- und Fußzeile,
* Seitenlayoutansicht,
Arbeitsmappe: Ist vergleichbar mit einem Aktenordner. Arbeitsmappen enthalten z.B. Tabellenblätter, Diagramme, Tabellen usw.
Tabellenblatt, In Excel 2019 wird eine Arbeitsmappe mit einem Tabellenblatt Arbeitsblatt: geöffnet. Eine Arbeitsmappe kann unterschiedliche Blatttypen
enthalten. Die Tabellenblattbezeichnung kann textuell und farblich verändert werden. Bis zu 31 Zeichen sind erlaubt.
Tabellenblatt und Zelladresse: Die Einteilung der Blätter erfolgt in Spalten und Zeilen. Ab Excel 2019 sind ca. 16.000 Spalten möglich. Die Zeilen sind von 1 durchgehend
nummeriert, bis zu ca. 1.000.000 Zeilen können bearbeitet werden (je nach Arbeitsspeicher).
Daten werden in Zellen eingegeben. Ca. 32.000 Zeichen in Zellen und bis zu 8.000 Zeichen in Formeln können verarbeitet werden. Zellen haben eindeutige Zelladressen.
Z.B.: [Tabellenkalkulationen.xlsx] Tabelle1! A 5
Datei Blatt Spalte Zeile
In den Zellen werden zwei Datentypen verwendet.
Konstanten werden fix in Zellen eingegeben. Z.B. Texte, Zahlen, Datumsangaben usw.
Mit Formeln können Rechenvorgänge vorgenommen bzw. Daten manipuliert werden.
Formeln beginnen stets mit einem = Zeichen und können Operatoren, Zellbezüge, Konstanten, Funktionen usw. enthalten.
Ist in einer Zelle eine Formel eingetragen, so ist die Formel in der Bearbeitungsleiste ersichtlich und das Ergebnis in der Zelle.
Formel Bedeutung
= A1 Wert der Zelle A1 wird ausgegeben
=A1+A2 Die Summe der beiden Zellen wird berechnet
=A1*A2 Die Zellen werden multipliziert
=LINKS(A1;10) Die ersten 10 Zeichen der Zelle A1 werden angezeigt
=FORMELTEXT(A3)Die Formel einer Zelle wird als Text ausgegeben
Add-Ins einbinden
Komplexere Funktionen werden in Excel mit Add-Ins eingebunden. Durch Add-Ins werden meist zusätzliche Funktionen im Menüband sichtbar.
Aktivierte und deaktivierte Add-Ins werden unter Datei -> Optionen -> Add-Ins angezeigt. Über die Schaltfläche “Los“ werden Excel-Add-Ins, COM-Add-Ins usw. aktiviert bzw. deaktiviert.
Integrierte Excel-Add-Ins
Analyse Funktionen - Finanzmathematische, technische Funktionen. Statistische Analysen
Analyse-Funktionen VBA - Technische VBA-Funktionen
Euro Currency Tools - Schaltfläche für das EURO-Format.
Funktion EUROCONVERT()
Solver - Lösen von Gleichungen und Ungleichungen mit mehreren Unbekannten
Integrierte COM-Add-Ins
Microsoft Power Map - Visualisieren von Daten mit Kartenmaterial. Schaltfläche
„3D-Karte“ wird im Register „Einfügen“ angezeigt Microsoft PowerPivot - Register „PowerPivot“ wird eingefügt
Microsoft Power View - Schnelle Auswertungen aus einer oder mehreren Tabellen werden möglich. Der Befehl „Power View“ kann über
„Menüband anpassen“ angezeigt werden
Microsoft bietet einen Store für Add-Ins an, um Excel-Funktionen zu erweitern. Unter Einfügen -> Add-Ins abrufen wird der Office-Add-Ins Store geöffnet.
Das Add-In „People Graph“ stellt
erweiterte Visualisierungsfunktionen zur Verfügung.
Kennwortschutz
Werden personenbezogene Daten verarbeitet empfiehlt es sich, die Daten vor unbefugten Zugriff zu schützen.
Sofern die gesamte Datei geschützt werden soll, gibt es die Möglichkeit im Dialogfeld Speichern unter -> Mehr Optionen... -> Tools -> Allgemeine Optionen
ein Kennwort für den Zugangsschutz (Kennwort zum Öffnen:) und für den Schreibschutz (Kennwort zum Ändern:) zu vergeben.
Der Zugangsschutz sperrt die gesamte Arbeits- mappe. Der Inhalt kann weder gelesen, noch verändert werden.
Beim Schreibschutz kann der Inhalt gelesen, jedoch sind Änderungen nur mit dem Speichern der Datei unter einem anderen Namen möglich.
Weitere Schutz-Funktionen sind unter Überprüfen ->
Schützen möglich.
Es können die Struktur einer Arbeitsmappe geschützt werden oder einzelne Funktionen bis hin zu Zellen mit einem Kennwort versehen werden.
In der Registerkarte „Start“ und der Gruppe „Bearbeiten“ wird der Befehl Autosumme mit den unterschiedlichen Funktionen, wie Mittelwert, Anzahl usw. zur automatischen
Gruppierung von Zellen bereit gestellt.
AutoBerechnung:
Mittelwert, Anzahl und Summe werden in der Statusleiste automatisch angezeigt.
Ändern der Anzeige mit rechtem Mausklick in der Statusleiste und
Auswählen der Funktion.
Daten ausfüllen:
Unter Start-> Bearbeiten-> Ausfüllen-> Datenreihe kann die automatische Datenbefüllung
angepasst werden.
Kommen Einträge in einer Liste mehrmals vor, so ist die AutoVervollständigung sehr hilfreich.
In Textzellen ->Rechter Mausklick ->
Dropdown-Auswahlliste
Daten verschieben, kopieren
Auf andere Blätter oder Mappen verschieben
Bereich markieren -> Alt-Taste drücken und mit dem Rahmen auf das neue Blatt oder die Mappe verschieben
Gesamtes Tabellenblatt in eine andere Mappe kopieren:
Strg-Taste drücken und Tabellenblatt mit Maus in die neue Mappe einfügen Verschieben, kopieren über die Zwischenablage
Zellen markieren -> Strg X (ausschneiden) oder Strg C (kopieren) drücken -> neue Zeile mit Strg + einfügen - > Daten mit Strg V einfügen
Daten transponieren
Daten aus Spalten werden durch das Transponieren in Zellen verteilt. Sehr breite Tabellen können dadurch vertikal angeordnet werden.
Markieren der Tabelle -> Kopieren -> Zelle zum Einfügen der Daten auswählen -> Start, Einfügen, Inhalte einfügen -> Transponieren
Zur Diskussion über Dateninhalte wurde die Funktion Kommentare bereit gestellt.
Dadurch können Chats für einzelne Zellen geführt werden.
Überprüfen ->
Neuer Kommentar
Die vormaligen Kommentare werden ab Excel 2019 unter Überprüfen -> Notizen geführt.
1.1 Einstieg in Tabellenkalkulationen 1.2 Formeln
1.3 Aussehen von Tabellenblättern
die Formel einmal gebildet, errechnet sie fortwährend das Ergebnis.
Formeltypen
Arithmetische Formelnerrechnen das Ergebnis, das in der Zelle angezeigt wird.
=A1+D3
=5*2
Zeichenfolgen-Formelnverknüpfen Zellen und deren Zeichenfolgen miteinander
=“Sonnenaufgang um: “&A3
=“Tag “&“und “&“Nacht “
Logische Formeln geben den Wert Wahr oder Falschaus. Dazu werden
Vergleichsoperatoren, wie <, >, = usw. angewendet. Der angezeigte Wert Wahr hat den
nummerischen Wert 1 und Falsch hat den Wert 0. Mit den Werten können Rechenoperationen durchgeführt werden.
Funktionsformelnenthalten Funktionen.
Z.B. =SUMME(A1:B4)
Excel stellt eine Reihe von Funktionen zur Verfügung (ca. 450), die über
Shift + F3 oder oder Formeln ->
Funktionsbibliothekaufrufbar sind.
Absolute und relative Bezüge Relative Bezüge:
Z.B. =C3
Excel erstellt für Zellen standardmäßig relative Bezüge. Das heißt, programmtechnisch wird intern auf die Position der Zelle Bezug genommen. C3 ist somit die dritte Zelle von links in der dritten Zeile. Wird diese Zelle kopiert oder versetzt, so ändert sich auch der Bezug zur Position und auch der Wert der Zelle .
Absolute Bezüge:
Z.B. =$C$3
Bei absoluten Bezügen auf eine Zelle wird immer genau auf die jeweilige Zelle verwiesen. Beim Kopieren oder Versetzen wird immer der selbe Wert beibehalten.
Anstatt des $-Zeichens kann auch mit der Funktionstaste F4 ein absoluter Bezug hergestellt werden.
Bezugsarten:
Beispiel:
Bezug Beschreibung
C3 Relativer Bezug auf Zelle C3
$C$3 Absoluter Bezug auf Zelle C3
$C3 Gemischter Bezug, die Spalte ist absolut, die Zeile relativ C$3 Gemischter Bezug, die Spalte ist relativ, die Zeile absolut
Priorität der Operatoren
Die Operation mit der höchsten Priorität wird zuerst abgearbeitet. Operationen mit der selben Priorität werden von links nach rechts ausgeführt.
Priorität Operator Bedeutung Verkettungsoperator
11 & Text-Verküpfung
Vergleichsoperator
12 = gleich
13 <> ungleich
14 > größer als
15 < kleiner als
16 <= kleiner oder gleich
17 >= größer oder gleich
Priorität Operator Bedeutung Bezugsoperatoren
1 : Bereich
2 Leer Schnittmenge
3 ; Verbindung
Arithmetische Operatoren
4 - Vorzeichen
5 % Prozent
6 ^ Potenzierung
7 * Multiplikation
8 / Division
9 + Addition
10 - Subtraktion
Regeln zur Eingabe von Formeln:
- Das erste Zeichen einer Formel ist immer ein = Zeichen
- Zeichenfolgen müssen in doppelten Anführungszeichen gegeben werden, z.B. “Text“
- Leerzeichen dürfen nicht zwischen Funktion und Klammer ( gesetzt werden
Bereichsnamen
Zellen und Bereiche müssen nicht mit Adressen angegeben werden, es können auch Namen vergeben werden.
Formeln -> Definierte Namen Vorteile:
* Namen sind verständlicher, man kann sie sich leichter merken. Benannte Bereiche können immer wieder verwendet werden, ohne die Adresse einzugeben.
=A10-B20-D40
=Umsatz-Zinsen-AfA
* Ist ein Bereichsname in mehreren Formeln eingetragen und der Bereich ändert sich, so muss nicht jede Formel geändert werden.
* Zum benannten Bereich gelangt man ganz einfach mit F5 oder mit Strg + G
Summierung und Schnittmengen
Die Summierung von Bereichsverknüpfungen erfolgt mit dem Semikolon.
z.B. =SUMME(C3;C6;C9)
Zur Bildung der Schnittmenge von Spalten und Zeilen wird ein Leerzeichen verwendet.
z.B. = SUMME(A:B 5:7)
Leerzeichen
Darüber hinaus gibt es auch die Möglichkeit vorhandene Daten und Modelle zu analysieren.
Schnellvariante für Berechnungen
Um Zahlenkolonen mit einem Wert und einem der vier
Grundrechnungsarten zu berechnen, kann mittels Start -> Zwischenablage ->
Einfügen -> Inhalte einfügen, die Berechnung direkt eingefügt werden.
Strg C
1 3
2
Zellen markieren
4
1.1 Einstieg in Tabellenkalkulationen 1.2 Formeln
1.3 Aussehen von Tabellenblättern
Einsatzgebiets gestaltet sein.
Ein Kundenangebot oder ein Bericht für die Geschäftsführung erfordert ein anderes Design, als eine Tabelle zur internen Verwendung.
Publikationen im Internet sollen Barrierefreiheit unterstützen und ein Ausdruck auf Papier benötigt andere Schriften, als eine Projektion an der Wand.
• Die Informationen in einer Tabelle sollen gut lesbar sein
• Es soll ersichtlich sein, was zusammengehört und welche Bereiche getrennt sind
• Wesentliche Informationen sollen auf den ersten Blick erkennbar sein
• Ein einheitliches Design erzeugt einen guten Eindruck und erhöht die Wiedererkennung
Unformatiert
Formatiert
Sämtliche Zahlenformate stehen im Register Start und der Gruppe Zahl zur Verfügung.
Mit Strg 1 wird das Dialogfeld Zellen formatieren geöffnet.
Das Löschen von Zahlen- formaten kann unter
Start -> Bearbeiten ->
Löschen -> Formate löschen
ausgewählt werden.
Tabellenblätter designen
Bildhintergründe: Seitenlayout -> Seite einrichten -> >Hintergrund
Designvorlage verwenden: Seitenlayout -> Designs -> Metropolitan
Zellen werden durch ihre Werte mit grafischen Formatierungen hervorgehoben.
Start -> Formatvorlagen -> Bedingte Formatierung
Gliederung von Daten
Für große Datentabellen ist es oft vorteilhaft Bereiche zu gliedern. Excel stellt unter Daten -> Gliederung Funktionen zur Gruppierung von Daten zur Verfügung.
Zu beachten sind Teilergebnisse in den jeweiligen Spalten und Reihen, um eine ansprechende Übersicht bei der Komprimierung zu erhalten.
Die Funktion Gruppierung erfordert das manuelle Einfügen von Zwischenergebnissen.
Um Zwischenergebnisse automatisiert zu erzeugen wird die Funktion Teilergebnis in der Gruppe Gliederung angewendet.