Excel 2010
An der VHS Wolfenbüttel wird ab dem 20.01.2014 ein Bildungsurlaub zum Thema Excel veranstaltet. Microsofts Tabellenkalkulation Excel 2010 ist Standard bei der Aufbereitung von Zahlen und Texten. In diesem Lehrgang werden mit Hilfe von Excel wichtige Kenntnisse und weitergehende Fertigkeiten zu folgenden Schwerpunkten vermittelt:
Themen: Aufbereitung von Zahlen, Texten und Formeln, Erstellung von Formeln (einfache und verschachtelte), Eingebaute Funktionen (auch versteckte!), Diagramme optimieren, Diagrammvorlagen, Daten (sortieren, filtern, gruppieren, Pivot-Tabellen), Große Datenblätter optimal nutzen und aufbereiten, Aufgaben mit Makros automatisieren (einfache Skripte mit VBA, eigene Funktionen). Alle Inhalte werden in praxisnahen Übungen durch die Teilnehmer erarbeitet.
Hier die Rahmendaten unseres Seminars:
Ort: VHS Wolfenbüttel, Raum Linux (A 1.9)
Zeiten: Mo., 20.01. - Fr., 24.01.2014; jeweils 08.00 - 15.30 Uhr
Ich werde unser Seminar an dieser Stelle mit den Inhalten begleiten...
Ihr Trainer Joe Brandes
Montag
Umsatztabelle
Montag, 20.01.2014, 08.00 - 15.30 Uhr
- Orientierungsphase, TN-Themen, Zeiten/Pausenregelungen
- Arbeitsoberfläche ab Excel 2007 / 2010
neues Design mit Ribbons ("Bändern"); Einführung in die Bänder mit Hinweis auf die Gruppen ("Kategorien")
unterschiedliche Ansichten und Zoom-Faktoren (Strg + Rollrad Maus); auch klassische Dialogfenster mit kleinem Pfeil in Ribbon-Gruppen
Tabellenblattnahmen umbenennen (mit Doppelklick oder natürlich Kontextmenü: Rechte Maustaste) - Interaktive Handbücher von Microsoft zu 2003 Versionen vs. 2010 Versionen
Positionen von Menüs und Symbolleisten in Office 2010
Link: http://tinyurl.com/2fxrxau (Kurzlink führt auf office.microsoft.com/...)
hier sind dann auch die Interaktiven Handbücher und Programmreferenzen verlinkt - Zellen können drei Inhalte aufnehmen:
Texte, Zahlen und Formeln; letztere beginnen immer mit einem "="
Zahlen werden immer sauber ohne Größenangaben und Währungen eingegeben
Eingaben sauber mit Return beenden
Spezial: Datum und Uhrzeit sind spezielle Zahlenformate (siehe Rechte Maus - Zellen formatieren...) mit Zahleninhalten mit Bezug auf den 01.01.1900 bei Microsoft Systemen; keine negativen Uhrzeitformate möglich (Hinweis auf ####### in Zellen)
erste einfache Formeln mit "Arithmetischen Operatoren" ;-) also: + - / * - Zellen in Spalten und Zeilen
Zellbezüge mit Buchstaben für Spalten und Zahlen für Zeilen
Bewegen mit Maus und Tastatur; mit Strg und Cursortasten (Pfeiltasten) bewegen an "Tabellengrenzen"
in neuen Exceltabellen: 16.384 Spalten x 1.048.576 Zeilen
schnelles Positionieren mit Namensfeld (oben links) und Hinweis auf echte Inhalte der Zellen in Bearbeitungsleiste
Spaltenbreiten geändert, Spalten ein-/ausblenden - Automatisches Ausfüllen (Benutzerdefinierte Listen)
von Monaten, Wochentagen und Zahlenformaten, Ziehen mit der Maus
Excel 2007: Excel-Optionen - Kategorie Häufig verwendet - Benutzerdefinierte Listen
Excel 2010: Excel-Optionen - Kategorie Erweitert - nach unten scrollen - Benutzerdefinierte Listen - Eingabetrick:
Zelleingaben mit ' beginnen - Zellen werden automatisch 1zu1 als Text übernommen (z.B. '007)
so lassen sich auch "Formeln" in Zellen eintragen - Zellbezüge (relativ und absolut)
relative Zellbezüge erlauben das Kopieren (Ziehen) der Formeln
absolute Zellbezüge mit Hinzufügung von $-Symbolen, so werden Zellbezüge beim Kopieren "fixiert"
Tipp: mit Cursor an Zellbezug und Druck auf F4 automatische $ - Funktionen
am Beispiel SUMME(); Eingabe mittels Maus und/oder Tastatur
MIN(), MAX(), MITTELWERT() - Benutzerdefinierte Zahlenformate
am Beispiel mit Litern: 0,00 "Liter" formatiert Zahl 15 zu 15,00 Liter
Kontextmenü
Formeleingabe
Zellbereiche
Umsatztabelle
Buchhaltung vs. Währung
FALSCH ;-)
Dienstag
Als Tabelle formatieren
Dienstag, 21.01.2014, 08.00 - 15.30 Uhr
- Rekapitulation, TN-Fragen
Hinweis auf Cobra-Shop (VHS) - leider aktuell keine sehr attraktiven MS-Angebote - Dateihandling an Tag 2
Wiederaufnahme der Arbeiten an Datei "montag.xlsx" mittels Datei - Öffnen...
Hinweis: im Seminarraum werden die Rechner nach Neustarts zurückgesetzt! Ihre PCs funktionieren da anders: siehe Startmenü und Datei - Zuletzt verwendet - Fenstermanagement (Windows / Anwendung Excel / Dokumente Arbeitsmappen)
Wechseln mittels Alt + Tab (Tabulator) oder Win + Tab (Aero-Flip Effekt)
Wechsel über die Taskleiste mit Maus und Vorschauf auf Fenster
Dokumentenfenster (Arbeitsmappen) mittels Ribbon Ansicht nebeneinander / untereinander darsgestellt - Format übertragen
über Kontextmenü und/oder Symbol "Pinsel" die Formate aus Vorlage-Zelle auf andere Zellen übertragen - Hilfe
über Funktionsassistent "fx" in Bearbeitungszeile Hilfe für Zellen mit Formeln und Funktionen nachrecherchieren
Hilfe lässt sich sonst auch über Symbol (oben rechts) bzw. Funktionstaste F1 aufrufen - Weitere Funktionen: ANZAHL(), ANZAHL2()
- Neue Datei (Arbeitsmappe)
über Ribbon Datei - Neu; Ersatz Hinweis auf Vorlagen für Arbeitsmappen - Formeln über Tabellenblätter und sogar Arbeitsmappen hinweg
Beispiele durchgespielt und die Syntax beachtet: =SUMME('Werte und Co'!B3:B5)
Tabellenblattnamen können sogar nachträglich geändert werden - aber möglichst vorher Gedanken machen und belassen - Drag & Drop
Mausaktionen mittels "Ziehen und Fallenlassen"; Praxis mit Zellen, Zellbereichen und Tabellenblättern
mittels Strg-Taste wird das Verschieben zum Kopieren - neue Spalten/Zeilen
Markieren der Spalte(n)/Zeile(n) und dann mittels Strg + "Plus" einfügen bzw. Strg + "Minus" entfernen
bei Bereichen und Strg + "Plus" erscheint zusätzliches Menü - Kommentare
Mittels Rechter Maus (Kontextmenü) Kommentar eingefügt und später auch bearbeiten bzw. löschen, ein-/ausblenden
in Ribbons: Überprüfen - Gruppe Kommentare - Einstellung Windows zu Dateitypen
mit Windows Explorer (Win + E) die Dateierweiterungen einblenden lassen;
hier Windows 7: Organisieren - Ordner- und Suchoptionen - Register Ansicht ...
Alternativ bei Windows XP: Extras - Ordneroptionen - Excel Versionen bzw. Dateitypen Excel
97-2003: klassische Versionen mit Dokumentformat .xls (Excel-Sheets)
"Alte" Office können die folgenden neuen Formate mit Hilfe eines Kompatibilitätstools von Microsoft zumindest öffnen (Link)
2007, 2010: neues Format .xlsx (und mit Makros .xlsm; Templates: ..xltx - auch hier extra mit Makros .xltm)
siehe Dialog "Datei - Speichern unter..." (Übung: Nachrüsten der Schnellzugriffsleiste)
Hinweis: beim Öffnen "alter xls" mit Excel 2010 wird Hinweis "Kompatibilitätsmodus" ausgewiesen - Als Tabelle formatieren...
neue 2007/2010er Technologie - unterschätzt als einfache "Design-Technik" zum Einfärben von Tabellenbereichen; unter der Haube aber komplett neue intelligente "Datenbank"-Technik mit einfachen und sicheren Möglichkeiten zum Sortieren und Filtern von Informationen; außerdem lassen sich diese Tabellenbereiche sinnvoll benennen und damit "sprechende" Formeln erzeugen: =SUMME(Kassenbuch[Eingang])
Tipps: Tabellen sauber benennen, Filter-Pfeile ausblenden mittels Ribbon Daten - Filter, Tabellenbereiche lassen sich auch wieder in "In Bereich konvertieren" zurückwandeln, Formate entferen mit "Radiergummi" - Zellen formatieren
Registerkarten durchgegangen: Zahlen, Ausrichtung, Schrift, Rahmen, Ausfüllen, Schutz
Tipps: manueller Zeilenumbruch mit Alt + Return, Zellen verbinden und zentrieren, Formate mischen (siehe "H20" Beispiel) - Uhrzeitformat
mittels [h]:mm werden auch Ausgaben jenseits der 24:00 Raster ausgewiesen
Zellformatierungen
Zwei Mappen
Kommentare
Excel Dateitypen
Als Tabelle formatieren
Neue Formelbezüge
Mittwoch
WENN verschachtelt
Mittwoch, 22.01.2014, 08.00 - 15.30 Uhr
- Rekapitulation, TN-Fragen
- Datum formatieren (Forts.)
Benutzerdefinierte Formate mittels T (Tage), M (Monate), J (Jahre); die Anzahl der Buchstaben definiert die Art der Darstellung für die Tage, Monate und Jahre; Beispiel für Monate (Januar): M ergibt 1 - MM ergibt 01 - MMM ergibt Jan. - MMMM ergibt Januar
Datum nach DIN: JJJJ-MM-TT - Als Tabelle formatieren...
Wiederholung zu den neuen intelligenten (und natürlich "hübschen") Tabellenlayouts
innerhalb der gestylten Tabellen ist automatisch cleveres "Copy & Paste" möglich - Zwischenablage (Forts. Copy & Paste)
Beim Einfügen ermöglicht Excel die Definition der Ziele: z.B. kann man also auch am Ziel nur reine Werte (statt Formeln) und ohne Formate einfügen - Diagramme (Teil I)
Umsatztabelle inklusive Spalten- und Zeilenbeschriftungen (Artikel, Monate) markiert und mittels Funktionstaste F11 ein "Schnelldiagramm" erstellt in Form eines neuen Diagrammblatts; Wechseln der X-Achse und Legendenzuordnung mittels neuem Ribbon Diagrammtools - Entwurf - Zeile/Spalte wechseln
Übung mit Änderung der Styles des Diagramms (Füllungen, Schriften, Abstände); Diagramm lässt sich in Tabellenblatt verschieben und dort in Größe und Eigenschaft anpassen; beim Ausdruck auf die Aktivierung des Diagramms achten, falls Sie nur das Diagramm drucken wollen 
tafel-20140122
Logische Funktion mit 3 Parametern: Prüfung / Bedingung; Dann-Wert; Sonst-Wert
Syntax beachten und Hilfestellung bei Eingabe
Verschachtelung von zwei WENN-Funktionen für drei Verzweigungen
Nutzung von Zellen für Vergleichswerte in Prüfungen und für Berechnungen in den Dann/Sonst-Zweigen verlangen wieder nach den absoluten Zellbezügen, damit sich die Funktionen auch sauber kopieren lassen (nach unten ziehen lassen)- Bedingte Formate
neue Regeln und Hervorhebungen mit Excel 2007/2010: Datenbalken, Farbskalen, Symbolsätze; alle lassen sich kombinieren, Regeln löschen und verwalten mittels des Ribbon Start - Formatvorlagen - Bedingte Formate... - SmartArt
neue intelligente und attraktive Grafikelemente für das Office (seit 2007)
Cleveres Einfügen
Achsen tauschen
WENN verschachtelt
Bedingte Formate
Datumsformatierung
SmartArt
Donnerstag
RUNDEN()
Donnerstag, 23.01.2014, 08.00 - 15.30 Uhr
- Rekapitulation, TN-Fragen
- "Fahrplan" für Do
Wiederholungsübung zu WENN(), einfache Verschachtelung mit RUNDEN(), Funktion SVERWEIS() für mehr Verzweigungen und Logik,
Große Tabellen (Ansichten optimieren/fixieren und Ausdrucke gestalten) - RUNDEN() Funktion
mittels RUNDEN(C3/C4;2) wird ein Rechnungsergebnis sauber aus 2 Dezimalstellen gerundet (aus 3,3333333 wird 3,33000000); Tests mit Hilfe in Excel und Alternativen gefunden AUFRUNDEN(), ABRUNDEN() - HEUTE() Funktion
eine einfache Möglichkeit das jeweilige aktuelle Datum einzufügen - Funktion ohne Parameter (leere Klammer)
Tipp: versuchen Sie mal JETZT() - Übung und Wiederholung WENN() Funktion
Einsatz für einen Stundenzettel - Problemlösung für "negative Uhrzeiten", die ja nicht als Uhrzeitformat darstellbar sind - SVERWEIS Funktion
verschachtelte WENN-Funktionen können nur maximal 10 Werte verzweigen (9 mal WENN verschachteln) und das will ja auch kein Mensch machen ;-)
mit SVERWEIS lassen sich aus Bereichen (Matrix) die entsprechenden Ergebniswerte aus den 2. / 3. ... Spalten auswerten:
=SVERWEIS( B4 ; Provisionstabelle!$A$2:$E6 ; 2 )
Beachten: erste Spalte aufwärts alphanumerisch sortiert und Einstiegswert (kleinster möglicher Wert) sollte in der ersten Zeile berücksichtigt werden
Verwandte Funktion: WVERWEIS() - dort das Ganze dann in Zeilen, statt in Spalten bei SVERWEIS()
Vertiefungsübung: Mietwagenabrechnung mit Nutzung der Spalten 2 bis 4 für Übernahmen in Formeln - Große Tabellen
Fixierung der Tabellenblätter für besseres Scrollen, siehe Ribbon Ansicht - Gruppe Fenster - Fenster einfrieren
Hinweis: vorher die Zelle zum Fixieren anklicken
Ausdrucksoptimierung über die Seitenansicht (Druckvorschau): Ribbon Datei - Drucken
Dialogfenster "Seite einrichten" mit Registern: Papierformat (mit Skalierung), Seitenränder (Zentrierungen), Kopfzeile/Fußzeile (vordefinierte und manuelle), Blatt (Wiederholungszeilen nicht in Druckdialogen von Excel konfigurierbar)
Anm.: Wiederholungszeilen lassen sich nur in der Tabellenbearbeitung über Ribbon Seitenlayout - Seite einrichten konfigurieren
Tipp: Direkt über Symbol "Drucktitel einstellen"
WENN()
SVERWEIS()
Drucken Dialog 2010
Skalieren
Gruppe Seite einrichten
RUNDEN()
Freitag
PivotTable
Freitag, 24.01.2014, 08.00 - 15.30 Uhr
- Rekapitulation, TN-Fragen
- "Fahrplan" für Fr
Ausdruck (manuelle Umbrüche, Druckbereiche), Sortieren und Filtern von Daten, versteckte Funktion ("Jahre" als Differenz), Summen bilden mit Extras (Datenbankfunktionen - Hinarbeiten auf Pivot), Formatvorlagen (erst mit Wiederholung zu Diagrammen, dann für die Arbeitsmappen), Makros (xlsm) - Ausdruck optimieren (Forts. Drucken)
Ansicht Seitenumbruch (Ribbon Ansicht oder Symbole unten rechts in Statuszeile) - Ändern der Umbrüche (Zeilen) per Maus; Hinweis: bei ein paar Seiten gute und schnelle Technik - bei vielen Seiten kein gutes "Werkzeug" (das geht dann eingentlich in Richtung Berichte von Datenbanken)
Manuelles Festlegen der Druckbereiche mittels Ribbon Seitenlayout; Schnelle Ausdrucksoptimierung druch Hoch-/Querformat wechseln
PDF generieren aus Excel mit Datei - Speichern und Senden - Sortieren und Filtern
nutzen der neuen Tabellenformate und Ribbon Daten; Hinweis: nach Änderung mit Sortieren lassen sich "Datensätze" nicht in Originalfolgen bringen, was Datenbanktechnik entspricht und OK ist; Hilfe: durch Tabellenblattkopien oder eine fortlaufende ID-Spalte - Datenbankfunktionen
mit DBSUMME() Summe mit Regeln erstellt: Summiere Bestellungn für Autohäuser aus "Region Mitte"; Anpassung der Suchkriterien mittels "ODER" mit mehrern Zeilen in Suchkriterium; Hinweis auf Excel-Hilfe Kategorie DB-Funktionen - Pivot-Tabellen
mittels Ribbon Einfügen - PivotTable; Aufbereitung und Analyse von Daten mit Hilfe der Assistent-unterstützten Pivot-Tabellen
Entwurf auf eigenem Tabellenblatt mit speziellen Ribbons für die Pivot-Konfiguration
Zusammenhang mit DB-Funktionen (DBSUMME, DBANZAHL); auch hier wieder Einsatz von "Als Tabelle formatieren..."
Eigenschaften von Feldern und Gesamt-PivotTable geändert (z.B. automatisches Anpassen Spaltenbreiten deaktiviert)
Hinzweis: beim Ändern der Datenbereiche für die PivotTable muss man diese manuell aktualisieren (Kontextmenü oder Schaltfläche Aktualisieren in Ribbon Optionen der PivotTable-Tools - DATEDIF (Beispiel für versteckte Funktionen in Excel)
Berechnung von Datumszelldifferenzen in Jahren (Schalter "y"), Monaten ("m") oder Kombinationen; siehe Beilage bei den TN-Unterlagen
Tipp: Suchmaschine nutzen mit Begriffen wie "Excel Jahre Differnz" - Diagramme / Vorlagen (Forts.)
Erstellen von Diagramm-Vorlagen mittels "Als Vorlage speichern" aus Diagramm-Tools Ribbon; Tipp: Standardverzeichnis für die Vorlagen (Templates) übernehmen, damit Office/Excel die Vorlagen in dem Dialog Vorlagen beim "Diagrammtyp ändern" auch gleich anbietet - Dokumentvorlagen (*.xltx bzw. *xltm)
Arbeitsmappenvorlagen in Templateverzeichnis (oder dortigen Unterverzeichnissen)
XP-System: Benutzerprofil\Anwendungsdaten\Microsoft\Templates (Windows 7: Benutzerprofil\AppData\...) - Makros (Erste Gehversuche mit Makros über Aufzeichnung von Makros)
Ribbon Ansicht - Makro - Makro aufzeichnen: Beispiel mit Transponieren von Tabellenbereichen
Makro-Dialogfenster öffnen mittels Makro - Makros anzeigen (Tastenkombination ALT + F8)
Vergabe von Tastenkombination und Symbol in Schnellzugriffsleiste (oben links)
Tipp: nicht mit Maus Tabellenbereiche markieren, sondern mit Tastatur z.B. Umschalten + Strg + Cursortaste(n) Rechts und dann Runter
Konfiguration über Excel-Optionen - Vertrauensstellungscenter - Einstellungen für Vertrauensstellungscenter
Hier: Einstellungen für Makros (möglichst unverändert lassen); Vertrauenswürdige Speicherorte
Tipp: bei den "Vertrauenswürdigen Speicherorten" finden Sie auch Ihre Verzeichnisse für Benutzer und Templates!
Tastenkombinationen: ALT+F8 öffnet Makro-Dialogfenster; ALT+F11 öffnet Visual-Basic-Editor (nur gezeigt)
OpenBook Galileo: kostenloses Online-/Offline Buch zu Excel 2007 (Link zu Makro-Kapitel) - Arbeitsmappen mit Makros als eigener Dateityp: *.xlsm
beim Öffnen muss außerdem noch die Sicherheitswarnung beachtet werden, die dann überhaupt erst die "Aktivierung" der aktiven Inhalte - sprich unsere Makros - ermöglicht. Ohne diese Aktivierung sind die Makros nicht nutzbar! - TN-Bescheinigungen, Feedback-Bögen, Letzte Fragen
- Bereitstellung Dozenten-Arbeitsmappen der Woche als Download für TN (erledigt 26.01.2014)
Dateityp mit Makros
Inhalt aktivieren
DB-Funktonen
PivotTable
DATEDIF()
Druckbereich
Bereitstellung Dozenten-Arbeitsmappen der Woche als Download für TN:
Vielen Dank für Ihr freundliches Feedback (TN-Evaluationsbögen) zum Ende des Seminars.
Ihr Trainer Joe Brandes
