Vonhoegen Excel 2021

Das Handbuch zur Software
5. Auflage 2021
ISBN: 978-3-8421-0847-9
Verlag: Vierfarben
Format: EPUB
Kopierschutz: 0 - No protection

E-Book, Deutsch, 1079 Seiten

Ein Handbuch randvoll mit praktischen Lösungen zu allen nur denkbaren Aufgaben mit Excel 2021 und Excel 365! Sie erfahren unter anderem, wie Sie Tabellen gestalten, Formeln zur Berechnung einsetzen, Analysen erstellen und Ihre Daten und Ergebnisse grafisch aufbereiten und mit anderen teilen. Auch Einsteiger werden hier Schritt für Schritt von den allerersten Grundlagen und Funktionen bis zu den anspruchsvollen Anwendungen angeleitet. Somit eignet sich das Handbuch gleichermaßen zum Erlernen des Programms wie als zuverlässiges Nachschlagewerk für die tägliche Arbeit. Auch für Excel 2019, 2016, 2013 und 2010 geeignet.

Aus dem Inhalt:

• Basiswissen für die Arbeit mit Excel 2021
• Excel auf dem Touchscreen bedienen
• Aufbau von Kalkulationstabellen
• Mit Formeln arbeiten
• Tabellenblätter gestalten
• Auswertungen und Was-wäre-wenn-Analysen
• Zielwertsuche und Solver nutzen
• Daten grafisch präsentieren
• Diagramme optimal einsetzen
• Datenvisualisierung mit Sparklines
• Tabellen grafisch aufbereiten
• Dokumente für die Veröffentlichung vorbereiten
• Arbeitsmappen veröffentlichen
• Excel-Daten im Web
• Arbeit im Team
• Tabellenfunktionen
• Informationen als Tabellen ordnen und verwalten
• Datenabfragen und -auszüge
• Pivot-Tabellen und -Diagramme
• Arbeit mit externen Daten
• Daten mit anderen Anwendungen austauschen
• Routineaufgaben mit Makros automatisieren
• Visual Basic für Applikationen
• Excel-App

Helmut Vonhoegen arbeitet seit 1992 als freischaffender Autor und IT-Berater. Er hat inzwischen über sechzig Bücher und zahlreiche Artikel in Fachzeitschriften zu aktuellen Themen verfasst. Die Schwerpunkte sind Office-Anwendungen, Windows, Webprogrammierung und XML. Sein Antrieb ist es, verständliche und gut lesbare Computerbücher zu schreiben.

Autoren/Hrsg.

Vonhoegen, Helmut

Inhaltsverzeichnis

Vorwort ... 27  1.  Basiswissen für die Arbeit mit Excel ... 30  1.1 ... Einsteigen mit einem Kostenvergleich ... 30  1.2 ... Ein paar Grundbegriffe vorweg ... 42  1.3 ... Excel starten und beenden ... 48  1.4 ... Baustelle für Tabellen und Diagramme ... 52  1.5 ... Dateiformate ... 82  1.6 ... Optionen für die Arbeit mit Excel ... 89  1.7 ... Add-Ins für Office ... 102  1.8 ... Umgang mit Dokumenten ... 104  1.9 ... Speichern in der Cloud ... 136  1.10 ... Besonderheiten der 64-Bit-Version ... 139  1.11 ... Hilfe in Excel ... 140  2.  Aufbau von Kalkulationstabellen ... 143  2.1 ... Planung und Design von Kalkulationsmodellen ... 143  2.2 ... Navigation und Bereichsauswahl ... 148  2.3 ... Effektive Dateneingabe und Datenänderung ... 161  2.4 ... Daten automatisch erzeugen ... 182  2.5 ... Prüfung der Dateneingabe ... 193  2.6 ... Arbeitsblätter neu organisieren und umbauen ... 198  2.7 ... Effektiver Umgang mit Arbeitsmappen ... 225  3.  Mit Formeln arbeiten ... 244  3.1 ... Der Aufbau von Formeln ... 244  3.2 ... Eingabe von Formeln und Funktionen ... 253  3.3 ... Relative und absolute Bezüge ... 271  3.4 ... Beschreibende Bereichsnamen ... 278  3.5 ... Matrixformeln ... 291  3.6 ... Qualität sichern und Fehler vermeiden ... 297  3.7 ... Tabellen mit Formeln verknüpfen ... 311  3.8 ... Auswirkungen der Zellbearbeitung auf Formeln ... 317  3.9 ... Kontrolle der Neuberechnung ... 319  4.  Tabellenblätter gestalten ... 326  4.1 ... Wahl des Zahlenformats ... 329  4.2 ... Schriftgestaltung und Ausrichtung ... 347  4.3 ... Rahmen und Muster ... 363  4.4 ... Blätter mit Designs schmücken ... 371  4.5 ... Blätter und Mappen schützen ... 376  4.6 ... Einheitliche Gestaltung mit Formatvorlagen ... 381  4.7 ... Tabellenformatvorlagen ... 388  4.8 ... Datenanalyse mit bedingten Formaten ... 394  4.9 ... Mehr Übersicht durch Gliederungsebenen ... 403  4.10 ... Dateneingabe über Steuerelemente ... 411  5.  Auswertung und Prognose ... 414  5.1 ... Berechnungen ohne Formeln ... 414  5.2 ... Ergebnisse konsolidieren ... 416  5.3 ... Add-In für statistische Datenanalyse ... 420  5.4 ... Was wäre, wenn ... ... 424  5.5 ... Planspiele mit Szenarios ... 428  5.6 ... Prognose aufgrund vorhandener Daten ... 439  5.7 ... Automatische Datenanalyse ... 443  6.  Optimierung ... 445  6.1 ... Zielwertsuche ... 445  6.2 ... Lösungen mit dem Solver suchen ... 447  7.  Daten grafisch präsentieren ... 461  7.1 ... Grafische Auswertung mit Diagrammen ... 461  7.2 ... Diagrammtypen in Excel ... 466  7.3 ... Von der Tabelle zum Diagramm ... 468  7.4 ... Zur Verknüpfung von Tabelle und Diagramm ... 480  7.5 ... Diagramme optimieren ... 482  7.6 ... Diagramme gestalten ... 490  8.  Diagramme optimal einsetzen ... 521  8.1 ... Standarddiagramme ... 521  8.2 ... Wertdifferenzierung mit Flächen- und Spannweitendiagrammen ... 529  8.3 ... Mehrfachverteilung und Zyklen -- Ring- und Netzdiagramme ... 531  8.4 ... Wertebeziehungen -- xy-Diagramme und Blasendiagramme ... 534  8.5 ... Kombidiagramme ... 537  8.6 ... 3D-Optik und echte 3D-Diagramme ... 540  8.7 ... 3D-Oberflächendiagramme -- für kontinuierliche Darstellungen ... 547  8.8 ... Neuere Diagrammtypen ... 549  8.9 ... Dreidimensionale Karten ... 555  9.  Datenvisualisierung mit Sparklines ... 559  9.1 ... Merkmale und Verwendungsmöglichkeiten ... 559  9.2 ... Einfügen von Sparklines ... 560  9.3 ... Darstellungsvarianten ... 563  9.4 ... Bearbeiten von Sparklines ... 564

10.  Tabellenblätter grafisch aufbereiten ... 569  10.1 ... Vorgegebene und freie Formen zeichnen ... 570  10.2 ... Feinarbeit an grafischen Objekten ... 580  10.3 ... Organigramme im Schnellgang ... 598  10.4 ... Grafiken übernehmen und bearbeiten ... 599  10.5 ... Piktogramme nutzen ... 607  10.6 ... 3D-Modelle und 3D-Karten ... 609  10.7 ... Freihandzeichnen ... 613  10.8 ... Bilder aus dem Netz fischen ... 616  10.9 ... Schnappschüsse von Tabellen ... 617

11.  Dokumente für die Veröffentlichung vorbereiten ... 619  11.1 ... Dokumentinspektion ... 619  11.2 ... Dokumente verschlüsseln ... 626

12.  Arbeitsmappen veröffentlichen ... 627  12.1 ... Vorbereitung von Arbeitsblättern zum Druck ... 627  12.2 ... Druckerauswahl und Druckereinstellungen ... 650  12.3 ... Tabellen per E-Mail versenden ... 654  12.4 ... Erstellen einer PDF- oder XPS-Kopie ... 656

13.  Excel-Daten im Web ... 658  13.1 ... Von Excel zu HTML und zurück ... 659  13.2 ... Daten für das Web bereitstellen ... 661  13.3 ... Dokumente mit Hyperlinks verknüpfen ... 664  13.4 ... Verarbeiten von XML-Daten ... 673

14.  Gemeinsame Arbeit an Arbeitsmappen ... 686  14.1 ... Teamarbeit in lokalen Netzen ... 686  14.2 ... Teamarbeit über OneDrive ... 693

15.  Tabellenfunktionen ... 698  15.1 ... Mit Excel 2013 eingeführte Funktionen ... 698  15.2 ... Neue Funktionen seit Excel 2016 ... 700  15.3 ... Aufbau und Einsatz von Funktionen ... 701  15.4 ... Finanzmathematische Funktionen ... 703  15.5 ... Datums- und Zeitfunktionen ... 715  15.6 ... Mathematische Funktionen ... 721  15.7 ... Statistische Funktionen ... 735  15.8 ... Nachschlage- und Verweisfunktionen ... 755  15.9 ... Datenbankfunktionen ... 767  15.10 ... Cube-Funktionen ... 770  15.11 ... Textfunktionen ... 772  15.12 ... Logische Funktionen ... 778  15.13 ... Informationsfunktionen ... 787  15.14 ... Technische Funktionen ... 788  15.15 ... Webfunktionen ... 795

16.  Informationen als Tabellen ordnen und verwalten ... 797  16.1 ... Möglichkeiten für den Einsatz von Tabellen ... 798  16.2 ... Die Struktur von Tabellen definieren ... 799  16.3 ... Tabellenbereiche ... 803  16.4 ... Daten sortieren ... 813  16.5 ... Daten in Gruppen zusammenfassen ... 823

17.  Datenabfragen und Datenauszüge ... 828  17.1 ... Relevante Daten herausfiltern ... 828  17.2 ... Komplexe Abfragen mit Spezialfiltern ... 842  17.3 ... Weiterverarbeitung gefilterter Daten ... 851  17.4 ... Berechnungen mit Datenbankfunktionen ... 852

18.  Pivot-Tabellen und -Diagramme ... 854  18.1 ... Datenanalyse mit Pivot-Tabellen ... 857  18.2 ... Dynamische Diagramme aus Pivot-Tabellen ... 904  18.3 ... Datenmodelle mit mehreren Tabellen ... 907  18.4 ... Power Pivot ... 910  18.5 ... Veröffentlichen auf Power BI ... 921

19.  Arbeit mit externen Daten ... 923  19.1 ... Access-Daten importieren ... 926  19.2 ... Zugriff auf SQL-Server-Datenbanken ... 931  19.3 ... Abfrage von XML-Dateien ... 934  19.4 ... Einlesen einer CSV-Datei ... 937  19.5 ... Arbeit mit verknüpften Datentypen ... 940  19.6 ... Direkte Abfragen im Internet ... 944

20.  Export und Import von Dateien ... 954  20.1 ... Unterstützte Dateiformate ... 954  20.2 ... Import von Textdateien ... 958  20.3 ... Texte auf Spalten verteilen ... 963

21.  Daten mit anderen Anwendungen austauschen ... 964  21.1 ... Austausch über die Zwischenablage ... 964  21.2 ... Dateien dynamisch verknüpfen ... 969  21.3 ... Einsatz von verknüpften Objekten ... 971  21.4 ... Ausgeliehene Leistungen mit eingebetteten Objekten ... 972

22.  Routineaufgaben mit Makros automatisieren ... 977  22.1 ... Makros aufzeichnen ... 977  22.2 ... Makros in den Arbeitsablauf einbinden ... 985  22.3 ... Eine Tabelle per Makro kippen ... 989  22.4 ... Makros für die Diagrammgestaltung ... 992  22.5 ... Makros von älteren Excel-Versionen ... 994

23.  Visual Basic für Applikationen ... 995  23.1 ... Grundlagen von VBA ... 995  23.2 ... Die Entwicklungsumgebung ... 1015  23.3 ... Ein- und Ausgabe ... 1035  23.4 ... Entwurf von Formularen ... 1043  23.5 ... Tabellenfunktionen in VBA ... 1054

Anhang.  Funktionstasten, Tasten und Tastenkombinationen ... 1059  A.1 ... Allgemeine Tastenfunktionen im Überblick ... 1059  A.2 ... Editiertasten und -tastenkombinationen ... 1064  A.3 ... Navigations- und Markierungstasten ... 1065  Stichwortverzeichnis ... 1067

Leseproben

1    Basiswissen für die Arbeit mit Excel

Die Benutzerumgebung eines Programms erschließt sich meist am schnellsten, wenn ein erster Versuch unternommen wird, etwas Brauchbares darin zu produzieren. Deshalb beginnt dieses Kapitel mit einem Beispiel. Danach werden einige grundlegende Begriffe und Zusammenhänge in Excel erläutert, damit Sie für den Rest des Buches eine sichere Basis haben.

1.1    Einsteigen mit einem Kostenvergleich

Eine ganz typische Anwendung für Excel ist die Kalkulation von Anschaffungen. Angenommen, Sie wollen in Ihrer Firma oder in einer Arbeitsgruppe ein kleines drahtloses Netz von Arbeitsplätzen aufbauen – mit einem Desktop-PC, fünf Notebooks und drei Tablets. Für die Vernetzung wird zusätzlich noch ein Access Point mit integriertem Router benötigt. Für den Internetzugang soll ein DSL-Anschluss verwendet werden, damit alle Geräte ins Web gelangen können. Sie wollen wissen, wie viel Sie sich leisten können, was das Nötigste kostet und was die Extras. Bei den Notebooks sollen die Kosten für die Ausstattungsmerkmale 15- oder 17-Zoll-Monitore, 500-GB- oder 1-TB-Festplatten, bei den Tablets die Ausstattung mit oder ohne Tastatur verglichen werden.

1.1.1    Zuerst die Beschriftungen

Zunächst erstellen Sie das Grundgerüst für die Kalkulation, indem Sie die Zeilen und Spalten der Tabelle beschriften.

1

Öffnen Sie Excel über das Programmsymbol. Nach dem Einblenden des Excel-Logos wird das Excel-Fenster aufgebaut. Zunächst erscheint ein Startbildschirm, der eine Schaltfläche für eine leere Arbeitsmappe und einen schnellen Zugriff auf die zuletzt bearbeiteten Arbeitsmappen anbietet, außerdem Links zu Vorlagen. Dieser Startbildschirm lässt sich über Datei • Optionen unter Allgemein • Startoptionen auch abschalten. In diesem Fall bietet Excel gleich eine neue Arbeitsmappe an.

2	Anders als in älteren Versionen öffnet Excel für jede Arbeitsmappe ein eigenes Anwendungsfenster. In der Titelleiste erscheint als Vorgabe der Name »Mappe1«.

3

Zunächst sollten Sie Ihre Anschaffungsliste eingeben, am besten in der ersten Spalte. Bewegen Sie den Mauszeiger auf die zweite Zelle in der ersten Spalte, und drücken Sie die linke Maustaste. Anstatt mit der Maus zu arbeiten, können Sie auch die Richtungstasten verwenden. Auf einem Touchscreen tippen Sie die entsprechende Zelle einfach an. Die Zelle wird mit einem stärkeren Rahmen markiert und damit ausgewählt.

4

In diese Zelle geben Sie zunächst eine Überschrift für die ganze Tabelle ein, etwa: Hardwarekosten für eine Arbeitsgruppe. Klicken Sie zuerst im Menüband auf der am Anfang immer sichtbaren Registerkarte Start in der Gruppe Schriftart auf das Symbol Fett, tippen Sie dann die Überschrift ein, und beenden Sie den Eintrag mit (¢). Der Zellzeiger wird automatisch eine Zelle tiefer gesetzt.

5

Markieren Sie die Zelle A5, und tragen Sie in der Spalte Zelle für Zelle die Bezeichnungen für die einzelnen Komponenten ein, die benötigt werden. Um die Aufpreise für die zusätzlichen Ausstattungsmerkmale der Notebooks oder für verschiedene Typen von Tablets zu erfassen, richten Sie entsprechende Zeilen dafür ein. Beenden Sie jeden Eintrag mit (¢).

6	Um die Ausstattungsvarianten für die Notebooks etwas deutlicher abzuheben, können Sie mit einem Einzug arbeiten. Markieren Sie mit gedrückter Maustaste die Zellen A7 bis A8, und verwenden Sie dann in der Gruppe Ausrichtung das Symbol Einzug vergrößern 1.

7	Zum Abschluss führen Sie den Mauszeiger auf die Linie zwischen den Spaltenköpfen A und B 2. Drücken Sie die linke Maustaste, und ziehen Sie den Zeiger so weit nach rechts, bis alle Texte der Komponenten in die Spalte passen.

8	Wählen Sie die Zelle A4, und beginnen Sie hier mit der Beschriftung der Spalten. Beenden Sie jeden Eintrag mit (ê) oder (î).

9	Markieren Sie den Bereich A4 bis F4, und benutzen Sie wieder das Symbol Fett.

1.1.2    Daten eintragen

Wenn die Beschriftung der Zeilen und Spalten abgeschlossen ist, können Sie mit der Eingabe der Daten beginnen:

1	Wählen Sie die Zelle B5, und tragen Sie untereinander jeweils die Anzahl der notwendigen Komponenten ein. Bei den Notebooks können Sie sich entscheiden, ob die Zusatzausstattung jeweils für alle oder nur für einen Teil der Geräte durchgerechnet werden soll.

2	Da die vorgegebene Spaltenbreite hier zu groß ist, positionieren Sie den Zellzeiger zwischen den Spaltenköpfen B und C, und führen Sie einen Doppelklick oder Doppeltipp aus. Die Spaltenbreite wird der benötigten Breite angepasst.

3	Markieren Sie die Zelle C5, um die Namen der Hersteller einzutragen. Beenden Sie Ihre Eingaben jeweils mit (¢). Wenn Komponenten von einem bereits aufgeführten Hersteller kommen, können Sie einfach den Eingabevorschlag übernehmen, den Excel macht, sobald der erste Buchstabe gleich ist.

4

Unter Modell geben Sie die jeweiligen Typenbezeichnungen ein. In der Preisspalte können nun die Beträge eingetragen werden, die Ihnen von den jeweiligen Anbietern bekannt sind. Markieren Sie anschließend die Zellen E5 bis E12, und verwenden Sie das Symbol Buchhaltungszahlenformat in der Gruppe Zahl.

1.1.3    Was kostet das?

Sind alle Preise eingegeben, kann berechnet werden, was die Minimalausstattung kostet und wie hoch der Preis für die zusätzlichen Wünsche ist. Dazu müssen zunächst die Einzelpreise mit der vorgesehenen Anzahl multipliziert werden.

1

Wählen Sie die Zelle F5 aus. Geben Sie ein Gleichheitszeichen ein, und markieren Sie anschließend die Zelle E5. Geben Sie ein Sternchen für die Multiplikation ein, und wählen Sie anschließend die Zelle B5. Beenden Sie die Formel mit dem Häkchen in der Bearbeitungsleiste 1, sodass die Zelle anschließend ausgewählt bleibt. Das Ergebnis der Formel, also das, was in der Schulmathematik normalerweise auf der anderen Seite des Gleichheitszeichens erscheinen würde, ist das, was die Zelle schließlich anzeigt.

2

Setzen Sie den Mauszeiger genau auf das Ausfüllkästchen der markierten Zelle, also den kleinen Würfel in der unteren rechten Ecke des Zellrahmens, und führen Sie einen Doppelklick aus. Excel füllt die Spalte mit Kopien der Formel in Zelle F5 bis zu Zelle F11, wobei sich das Programm an der Länge der Spalte D – soweit sie Werte enthält – orientiert. Dabei werden die Zellbezüge zeilenweise angepasst. Excel blendet unten die Schaltfläche Auto-Ausfülloptionen ein. Wenn Sie den kleinen Pfeil benutzen, werden verschiedene Ausfüllvarianten angeboten. In diesem Fall ist die Voreinstellung Zellen kopieren aber die richtige. Sie müssen also keine Veränderung vornehmen. Auf dem Touchscreen ist die Vorgehensweise etwas anders. Sie halten den Finger oder Stift einen Moment auf der Zelle F5. Es erscheint ein horizontales Kontextmenü. Tippen Sie auf das Symbol AutoAusfüllen. Excel blendet unter der rechten Ecke der markierten Zelle ein Ausfüllsymbol ein. Ziehen Sie dieses bis zur Zelle F11.

3	Die Spalte F übernimmt das Euro-Format in diesem Fall...

Über Autor(innen)

Vonhoegen, Helmut
Helmut Vonhoegen arbeitet seit 1992 als freischaffender Autor und IT-Berater. Er hat inzwischen über sechzig Bücher und zahlreiche Artikel in Fachzeitschriften zu aktuellen Themen verfasst. Die Schwerpunkte sind Office-Anwendungen, Windows, Webprogrammierung und XML. Sein Antrieb ist es, verständliche und gut lesbare Computerbücher zu schreiben.