MS-EXCEL2000 - Grundlagen (Powerpoint - Klaus
Werbung
Excel 2000 Grundlagen In dieser Excel-2000-Grundlagenschulung soll Ihnen die Bedienung und Handhabung von Microsoft Excel 2000 etwas näher gebracht werden. Sie werden lernen, wie man Tabellenblätter sinnvoll aufbaut, Sie werden einige Funktionen (Formeln) kennenlernen und den AutoFilter mit seinen Auswertungsmöglichkeiten anwenden. Zudem werden wir uns mit Formatierungen und einigen sog. Shortcuts (Tastenkombinationen) beschäftigen. Zwischenfragen und Verständnisprobleme sollen nach Möglichkeit sofort geklärt werden, scheuen Sie sich daher bitte nicht, rechtzeitig „den Mund aufzumachen“ ! Noch ein paar Worte zu diesem Schulungsscript: Es soll Ihnen zum einen ein „Begleiter“ durch diese Schulung sein, zum anderen soll es Ihnen aber auch als Nachschlagewerk für ihre tägliche Arbeit mit Excel dienen. Wann immer Sie während der Schulung auf dieses Symbol stossen hat Ihr Trainer Pause, denn Sie sind gefordert, selbständig an Ihrer Übungsmappe zu arbeiten ... Stossen Sie auf dieses Symbol ist dies ein Hinweis auf einen Praxistipp, der Ihnen die Arbeit mit Excel deutlich vereinfachen kann / soll ... Lang, lang ist‘s her (ein kurzer historischer Anriss): Bereits im Jahr 1979 wurde ein funktionierendes Tabellenkalkulationsprogramm entwickelt: VisiCalc, das immerhin eine Matrix von 168 Zellen ver- / bearbeiten konnte. Anfang der 80‘er-Jahre entwickelte Microsoft schliesslich MultiPlan, den Vorläufer der heutigen Excel-Applikation. Versionenhistorie Quelle: Bert Körn, http://www.excelabc.de Versionenhistorie Quelle: Bert Körn, http://www.excelabc.de Versionenhistorie Quelle: Bert Körn, http://www.excelabc.de Nun geht‘s aber richtig los: Kapitel 1: Begriffsbestimmungen Titelleiste Menüleiste Symbolleiste(n) Namen- oder Adressfeld in der ... ... Bearbeitungsleiste Zeilen- & Spaltenköpfe (Tabellen-)Blattregister Zunächst wollen / müssen wir einige Begriffsbestimmungen vornehmen, die uns im Laufe der Schulung immer wieder begegnen werden. Wir beginnen mit der ... Statuszeile Eine Excel-Arbeitsmappe besteht aus mehreren Tabellenblättern, die wiederum aus einzelnen Zellen bestehen. Insgesamt stehen uns in einem Tabellenblatt 256 Spalten und 65.536 Zeilen zur Verfügung, das ergibt eine „Matrix“ aus immerhin 16.777.216 Zellen ... Kapitel 2: Datum, Uhrzeit, Formatierungen In jede dieser Zellen können beliebige Zeichenfolgen eingegeben / erfasst werden, Excel erkennt im Normalfall sogar das gewünschte Format (so werden z.B. „Datümer“ – in Abhängigkeit der Ländereinstellung in der Windows-Systemsteuerung – richtig dargestellt, Zahlen erscheinen rechtsbündig, Text wird linksbündig dargestellt). 16.07.2005 123,45 Excel-Schulung Wenn Sie Excel noch nicht geöffnet haben, so holen Sie dies nun nach. Ein paar Hinweise zum Arbeiten mit „Datümern“: Die „Excel-Zeitrechnung“ beginnt am 01.01.1900, ein Rechnen mit „Datümern“ vor diesem Stichtag ist ohne Einsatz von VBA (Programmiersprache, „Visual Basic for Applications“) nicht möglich. Excel interpretiert den 01.01.1900 als Zahl 1, den 02.01.1900 als Zahl 2 usw. Für Excel ist also der 16.07.2005 die Zahl 38.549. Uhrzeiten werden als Bruchteile, bzw. Nachkommstellen interpretiert. Der 01.01.1900, 12:00 Uhr ist für Excel die Zahl 1,5. Diese Behauptung (ich könnte Ihnen ja viel erzählen ) wollen wir nun überprüfen: Geben Sie im Tabellenblatt1 Ihrer geöffneten Arbeitsmappe in Zelle A1 das Datum 16.07.2005 ein, in Zelle B1 tragen Sie bitte die Formel „=A1“ (ohne „“) ein. Markieren Sie nun B1 und wählen Sie in der Menüleiste den Eintrag „Format - Zellen“ aus (alternativ: Rechtsklick auf die Zelle – Zellen formatieren). Wählen Sie nun den Reiter „Zahlen“ und unter „Kategorie“ „Zahl“ aus . Wenn Sie alles richtig gemacht haben, sollte Ihr Tabellenblatt nun so aussehen: Als nächstes werden wir die Aussage prüfen, dass Excel Uhrzeiten als Nachkomma- oder Dezimalstellen verarbeitet: Geben Sie in Zelle A2 folgendes ein „13:23“ (ohne „“), in B2 geben Sie ein „=A2“ (auch wieder ohne „“). Formatieren Sie B2 als Zahl. Sie sehen: Excel hat 13 Uhr 23 Minuten tatsächlich in die Dezimalzahl 0,56 umgewandelt. Aber: Ändern Sie die Uhrzeit in A2 einmal auf 13:24 ab. Sie sehen, dass sich der Wert in B2 nicht verändert hat. Dies liegt an der Formatierung der Zelle B2, die nicht genügend Stellen nach dem Komma darstellen kann. Markieren Sie B2 und wählen Sie erneut die Zahlenformatierung. Klicken Sie nun aber auf den Eintrag „Benutzerdefiniert“ und tragen Sie unter „Typ“ ein: 0,000000000000000 Das Ergebnis sollte nun so aussehen: Ändern Sie nun die Uhrzeit in A2 wieder auf 13:23 ab und schauen Sie, wie sich Zelle B2 verändert ... Sie haben nun kennengelernt, dass eine korrekte Zellformatierung enorm wichtig für die richtige Darstellung von Zahlenwerten ist. Dies wollen wir anhand eines weiteren Beispiels nochmals verdeutlichen: Geben Sie in Zelle A3 folgendes ein: =123,456*12,3456 und schauen Sie sich das Ergebnis an Formatieren Sie A3 nun ins benutzerdefinierte Format 0,00 Nun werden wir das Ganze auf die Spitze treiben und Excel dazu bringen, „falsch“ zu rechnen (das treibt jeden Buchhalter in den Wahnsinn ) Geben Sie in Zelle A4 „2,349“, in B4 „2,355“ und in C4 die Formel „=A4+B4“ ein. Formatieren Sie alle drei Zellen mit zwei Nachkommastellen. Wie bereits angedroht: Das Ergebnis ist frustrierend ! Aber: Schauen Sie hin und wieder einmal in die Bearbeitungszeile, denn dort sehen Sie die „echten“ Zellinhalte und nicht das, was Ihnen durch Zellformatierungen vorgegaukelt wird ... Als Erkenntnis halten wir fest: Zellinhalte und Zelldarstellungen können durchaus voneinander abweichen, daher kommt der korrekten Zellformatierung eine enorme Bedeutung zu ! Wenn Sie monatliche Tabellen erstellen müssen, in der in einer beliebigen Spalte das Datum des Monatsersten bis zum Monatsletzten erfasst werden muss, so tragen Sie das Datum des Monatsersten in die erste Zelle ein, „ziehen“ Sie dann die Zelle mit der rechten Maustaste nach unten und staunen Sie über das aufspringende Kontextmenü ... Auch wenn Sie Zahlen in der ersten Zelle haben: „Ziehen“ Sie die Zelle nach unten und „spielen“ Sie mal mit den Auswahlmöglichkeiten, die Ihnen das Kontextmenü anbietet, es tun sich ungeahnte Möglichkeiten auf ... Eine weitere Anmerkung zum Rechnen mit „Datümern“ und Uhrzeiten: Wenn Sie Zeiten darstellen wollen, die grösser als 24 Stunden sind, werden Sie zunächst kein Glück haben: Geben Sie in Zelle A5 folgendes ein: „13:25“ (ohne „“), geben Sie in Zelle A6 ein: „22:00“. In Zelle A7 geben Sie ein „=A5+A6“. Schauen Sie, was passiert: Eigentlich sollte das Ergebnis 35:25 lauten, aber: Excel hat eine Zeit grösser 24 Std. erkannt und frecherweise 24 Std. abgezogen. Dies hängt wiederum damit zusammen, dass Excel eine AutoFormatierung vorgenommen hat und in A5 und A6 (Uhr-)Zeiten erkannt hat. Also stellt Excel das Ergebnis in A7 ebenfalls als Uhrzeit dar, eine Uhrzeit grösser 23:59 kann Excel aber nicht so ohne weiteres darstellen. Daher müssen wir eine passende Formatierung vornehmen: Markieren Sie Zelle A7 und führen Sie einen Rechtsklick mit der Maus aus. Im nun erscheinenden Kontextmenü wählen Sie „Zellen formatieren ...“ Geben Sie im Feld „Typ“ ein: „[h]:mm“ (ohne „“) und klicken Sie auf OK Am Ende des Schulungsscripts finden Sie weitere interessante Zellformatierungen, auf die wir dann noch näher eingehen ... Bevor uns die kleinen „Excel-Geister“ nun überfordern ... ... legen wir 5 min. Pause ein ... Kapitel 2: Sinnvoller Aufbau von Tabellenblättern und die daraus resultierenden Möglichkeiten von Tabellenblattauswertungen Aufbau von Tabellenblättern Excel bietet standardmässig zahlreiche Möglichkeiten, Daten (Texte, Zahlen, Uhrzeiten, „Datümer“ etc.) in einem Tabellenblatt auszuwerten. Denken wir an den AutoFilter, die Sortieroption oder auch an die Möglichkeit, Teilergebnisse anzuzeigen. Dazu ist es aber zwingend notwendig, dass sich vergleichbare Daten in einer Spalte befinden (gleiche Datenart, gleiche Formatierung). Wechseln Sie in Ihrer geöffneten Arbeitsmappe nun bitte ins Tabellenblatt 2 und erstellen Sie eine Tabelle wie unten angezeigt: Geben Sie in Zelle A2 das vorgegebene Datum ein (02.01.2005). Um die weiteren Datümer bequem eingeben zu können, nutzen wir einen kleinen, aber feinen Trick: Excel erkennt das laufende Kalenderjahr anhand des Systemdatums und dies machen wir uns nun zunutze. Markieren Sie A2 und klicken Sie doppelt auf den Formatpinsel in der Standardsymbolleiste. Der Mauszeiger hat sich zum „Schweizer Kreuz“ verändert, neben dem ein kleiner Pinsel zu sehen ist (und Zelle A2 hat eine umlaufende Markierung). Wenn wir nun andere Zellen markieren, wird das Format der Zelle A2 auf die markierten Zellen übertragen. Dies funktioniert sowohl für einzelne Zellen als auch für zusammenhängende Bereiche. Markieren Sie daher mit gedrückter linker Maustaste die Zellen A3:A11 und lassen die Maus los. Nun haben wir das Datumsformat auf den Bereich A3:A11 übertragen. Für das Erfassen der übrigen „Datümer“ reicht nun die Eingabe „9.3“, „21.4“, „5.2“ usw. (jeweils ohne „“). Excel fügt die Jahreszahl nun selbständig hinzu ... Diese „Datenbank“ werden wir nun auf verschiede Arten und mit unterschiedlichen Techniken auswerten. Sortieren Sie die Daten nun einmal aufsteigend nach dem Datum ... (Markierung auf A2:B11, Daten, Sortieren, nach Datum, aufsteigend) Excel bietet uns nun über die Funktionalität „AutoFilter“ die Möglichkeit, die erfassten Daten auszuwerten. Legen Sie über Daten – Filter – AutoFilter einen Filter auf die Zellen A1:B1 Klicken Sie nun auf den kleinen schwarzen „Nach-Unten_Pfeil“ in Zelle A1 Uns interessiert die Daten, die im 1. Quartal 2005 erfasst wurden. Dazu wählen wir (Benutzerdefiniert...) aus Nun müssen wir das entsprechende Filterkriterium definieren: Das 1. Quartal beinhaltet alle „Datümer“ vom 01.01.2005 bis 31.03.05. Da wir nur „Datümer“ aus 2005 in unserer Tabelle haben, sollte die Definition eigentlich recht einfach sein: Alle „Datümer“, die kleiner als 01.04.2005 sind ... Also wählen wir hier ... den Eintrag „ist kleiner als“ aus und definieren hier ... die Bedingung (kleiner als) „01.04.2005“ Das Ergebnis der gefilterten Liste sollte dann so aussehen: „Spielen“ Sie mit den Bedingungen des „Benutzerdefinierten AutoFilter“, indem Sie die Kriterien verändern und dies mit „Und / Oder“ kombinieren ... Doch zurück zu unserer Datentabelle: Wir haben nun die Daten des 1. Quartals herausgefiltert. Wie können wir nun eine Summe der „Werte“ bilden? Nun, hier hilft uns die Tabellenblattfunktion „=TEILERGEBNIS()“. Mit Tabellenblattfunktionen hat sicher jeder von Ihnen bereits gearbeitet, aber haben Sie dafür schon einmal „Karl Klammer“ oder „F1“, den genialen Roboter als Assistenten zu Hilfe gerufen ? Wenn nein, sollten Sie dies unbedingt mal tun, denn gerade in diesem Bereich von MS Office hat Microsoft © gute Arbeit geleistet. Klicken Sie in der Menüleiste auf das Fragezeichen und wählen Sie den Eintrag „Office-Assistenten anzeigen“ aus ... Anmerkung: Dieser Assistent steht Ihnen in allen Office-Applikationen zur Verfügung ... Wir werden also nun „Karl Klammer“ oder „F1“ um Hilfe bitten, damit wir herausbekommen, wie die Summe der Werte im 1. Quartal gebildet werden kann ... Vorher jedoch noch ein Praxistipp: Bilden Sie Summen, Auswertungen, Berechnungen usw. nach Möglichkeit IMMER oberhalb Ihrer Listen. Dies hat den Vorteil, dass Ihre Liste „nach unten“ offen und flexibel ist, sie aber trotzdem in Ihren Summen-, Auswertungs- und Berechnungsfunktionen und –formeln die richtigen Ergebnisse angezeigt bekommen. Nehmen wir unsere Tabelle als Beispiel: Würden wir die gleich erläuterte Funktion =TEILERGEBNIS() in Zelle A12 einfügen, hätten wir keine Möglichkeit, die Liste zu erweitern, da wir uns ja die Funktion in A12 „zerschiessen“ würden. Markieren Sie daher die Zeile 1 und fügen Sie eine neue Zeile ein (Klick auf den Zeilenkopf, Einfügen, Zeilen) ... Heben Sie nun den AutoFilter auf Zelle A2 auf, indem Sie den blauen Pfeil anklicken und den Eintrag „Alle“ auswählen ... Der „AutoFilterPfeil“ sollte nun wieder schwarz erscheinen ... Markieren Sie nun Zelle B1 und klicken Sie auf Einfügen – Funktion Bei Ihnen wird das Kontextmenü etwas anders aussehen, da Sie mit Excel2000 arbeiten, die Screenshots für dieses Script aber unter Office2003 erstellt wurden ... Das Handling ist aber identisch ... Wählen Sie als „Kategorie“ „Alle“ aus und im Feld „Funktion auswählen“ den Eintrag „Teilergebnis“. Klicken Sie dann auf OK. Nun rufen wir „Karl Klammer“ bzw. „F1“ um Hilfe, indem wir auf „Hilfe für diese Funktion“ (bei Ihnen unter XL2k auf das Fragezeichen an dieser Stelle) klicken ... Nun wird die sog. „Onlinehilfe“ (OH) eingeblendet, in der ALLE Tabellenblattfunktionen mit den entsprechenden Syntaxen und Argumenten erläutert und anhand von Beispielen beschrieben werden. Für die von uns ausgewählte Funktion sieht‘s ungefähr so aus: Hinweis: Die Funktionen 101 bis 111 stehen erst ab XL 2003 zur Verfügung ... Wir erinnern uns: Wir wollen eine Summe bilden. Also müssen wir die Funktion ... ... richtig: 9 verwenden. Kehren wir also ins Tabellenblatt zurück und tragen im Feld „Funktion“ die „9“ ein: 9 Nun müssen wir das sog. 2. Argument definieren, nämlich den Bezug. Excel will wissen, welche Zellen addiert (zur Funktion SUMME herangezogen) werden sollen. Dazu stellen wir den Mauszeiger in das Feld „Bezug 1“ und markieren im Tabellenblatt die Zellen B3:B12 (sollten die Zellen vom Kontextmenü verdeckt sein, verschieben Sie dieses mit gedrückter linker Maustaste in einen ungenutzten Bereich des Tabellenblattes). Klicken Sie dann auf OK ... Legen Sie nun den benutzerdefinierten Filter wieder auf das 1. Quartal 2005 und schauen Sie sich das Ergebnis in B1 an ... Nun werden wir uns mit einer weiteren Auswertungsmöglichkeit vertraut machen, der Funktionalität TEILERGEBNIS. Heben Sie dazu den Filter auf dem Datum auf. Die Funktionalität TEILERGEBNIS ist in der Lage, gleichartige Daten zusammenzufassen, zu gruppieren und daraus Auswertungen anzuzeigen. Voraussetzung hierfür ist aber, dass die zu gruppierenden Daten in der Spalte untereinander angeordnet sind. Wenn wir eine Summenauswertung nach Monaten erstellen wollen, müssen wir zunächst dafür sorgen, dass wir auswertbare Kriterien schaffen, die dann gruppiert werden können. In unserem Beispiel ist ein auswertbares Kriterium der jeweilige Monat aus den Zellen A3:A12. Da TEILERGEBNIS aber keine Formeln für die Kriterienbeschreibung zulässt, müssen wir das Kriterium im Tabellenblatt erstellen. Um aus den Datümern in A3:A12 die jeweiligen Monate zu „isolieren“, verwenden wir die Funktion =MONAT(). Schreiben Sie in Zelle C2 den Text „Hilfsspalte für Monat“, in Zelle C3 tragen Sie die Formel „=MONAT(A3)“ (ohne „“) ein. „Ziehen“ Sie C3 nun bis Zelle C12 herunter. Kennen Sie das Phänomen, dass beim "Formeln-nach-unten-ziehen" die Markierung viel zu weit nach unten gezogen wird, weil Excel "zu schnell" ist? Sie wollen beispielsweise eine Formel aus E 1 bis E 5.567 ziehen, die Markierung läuft Ihnen jedoch bis E 19.234 davon? Beim "Hochziehen" läuft Ihnen die Maus ebenfalls davon und Sie "treffen„ E 5.567 nicht? Hier vier Möglichkeiten, um Abhilfe zu schaffen: 1. Sie blenden die nicht benötigten Zeilen (also 5.568 bis 65.536) aus und ziehen dann die Formel nach unten. 2. Sie markieren E 1 und klicken auf "Kopieren". Dann markieren Sie E 2, halten die SHIFT-Taste gedrückt und fahren mit der "Pfeil-nach-unten"-Taste bis zur Zelle E 5.567. Nun lassen Sie beide Tasten los und drücken ENTER... 3. Sie schreiben einen beliebigen Wert in E 5.567. Dann markieren Sie E 1, klicken auf "Kopieren" und markieren E2. Nun drücken Sie mit gedrückten SHIFT- und STRGTasten einmal die "Pfeil-nach-unten"-Taste. Der Bereich E 2 : E 5.567 ist markiert, mit ENTER wird die Formel kopiert... 4. Markieren Sie E1, klicken Sie auf "kopieren", drücken Sie F8, geben Sie ins Adressfeld der Bearbeitungsleiste E5567 ein und drücken Sie 2 x ENTER ... Zurück in unser Tabellenblatt. Dies sollte nun so aussehen: Löschen Sie die Funktion =TEILERGEBNIS() in Zelle B1. Markieren Sie nun eine beliebige Zelle im Bereich A2:C12 und klicken Sie auf „Daten – Teilergebnisse“ Im Kontextmenü haben wir nun mehrere „Einstellmöglichkeiten“: Klicken Sie das Feld „Gruppieren nach:“ auf. Dort finden wir unsere drei Spaltenüberschriften wieder, nach denen wir eine Gruppierung vornehmen können. Im Feld „Unter Verwendung von:“ finden wir die wichtigsten Funktionen wieder, die wir auch als Tabellenblattfunktionen kennen. Im Feld „Teilergebnisse addieren zu:“ können wir angeben, welche Daten verarbeitet werden sollen. Die Checkboxen schliesslich sind nahezu selbsterklärend und sollten für unsere Auswertung nicht verändert werden. Versuchen Sie, die Parameter so zu setzen, dass Sie die Summen der „Werte“ nach Monaten erhalten (keine Angst: Selbst wenn Sie nicht die gewünschten Ergebnisse erzielen, „zerschiessen“ Sie Ihre Daten nicht, denn Sie können die Teilergebnisse jederzeit löschen). Wenn Ihre Parameter so aussahen, sind Sie zum gewünschten Ergebnis gelangt ... Bevor wir uns nun einer weiteren, nicht ganz einfachen Auswertungsmöglichkeit widmen, machen wir erstmal ... 10 min. Pause Heben Sie die „Teilergebnisse“ nun wieder auf. Sie sehen: Am Tabellenblattaufbau und an Ihrem Datenbestand hat sich nichts verändert, die von Excel eingefügten Zeilen mit den Zwischenergebnissen sind wieder verschwunden. Legen Sie eine 1:1-Kopie der Tabelle an, indem Sie auf „Bearbeiten – Blatt verschieben / kopieren“ klicken. Im Kontextmenü wählen Sie unter „Zur Mappe“ Mappe1 aus, „Einfügen vor“ Tabelle3 und setzen Sie den Haken in der CheckBox „Kopie erstellen“. Sie haben nun ein weiteres Tabellenblatt mit Namen „Tabelle2(2)“ in Ihrer Arbeitsmappe. Benennen Sie dieses nach einem Doppelklick auf den Tabellenblattreiter um in „Summewenn“ Wechseln Sie ins Tabellenblatt2, benennen Sie dieses um in „Teilergebnisse“ und führen Sie dort erneut „Teilergebnisse“ aus, da wir die Zwischensummen gleich für die Prüfung der Funktionen =SUMMEWENN() und =SUMMENPRODUKT() benötigen Wechseln Sie ins Tabellenblatt „Summewenn“ Nun wollen wir uns mit einer weiteren Auswertungsmöglichkeit beschäftigen, der Funktion =SUMMEWENN() Geben Sie bitte in Zelle D2 „Monat:“, in D3 „Summe:“ und in E1 „1“ ein (jeweils ohne „“). Mit „Karl Klammer“ und „F1“ haben wir ja schon Bekanntschaft gemacht, also rufen wir die „alten Freunde“ erneut zu Hilfe, um =SUMMEWENN() zu parametrieren. ... ist der Zellbereich, den Sie berechnen wollen (C3:C12) ... ist das Suchkriterium als Zahl, Formel oder Text (E2) ... sind die Zellen, die Sie summieren wollen (B3:B12) Bereich: Der Bereich ist C3:C12, denn das sind die Zellen, die wir in Abhängigkeit des Suchkriteriums auswerten wollen. Die Onlinehilfe spricht hier vom Zellbereich, der „berechnet“ werden soll, verständlicher wäre wohl „ausgewertet“ Suchkriterium: Nach welcher Zahl (Formel, Text) wollen wir auswerten: Nach der in Zelle E2 eingegebenen Monatszahl Summe_Bereich: Welche Zellen sollen summiert werden: Die Werte in den Zellen B3:B12 Andersherum formuliert: Es sollen alle Zellen aus Spalte B summiert werden, bei denen sich in der gleichen Zeile in Spalte C der Wert aus E2 wiederfindet (schwere Kost ) Ändern Sie nun den Wert in E2, schauen Sie, wie sich das Ergebnis in E3 verändert und prüfen Sie Ihre Ergebnisse, indem Sie sie mit den Ergebnissen im Tabellenblatt „Teilergebnisse“ vergleichen ... Tabellenblatt „Summewenn“ Tabellenblatt „Teilergebnisse“ Die letzte Auswertungsmöglichkeit (ganz, ganz schwere Kost ), die vorgestellt werden soll, ist die Tabellenblattfunktion =SUMMENPRODUKT() Hierbei handelt es sich um eine der mächtigsten Tabellenblattfunktionen, die wir im Excel-Standard vorfinden (zugegebenermassen aber auch um einer der kompliziertesten – zumindest solange, bis man die Syntax begriffen und verinnerlicht hat). Legen Sie eine Kopie des Tabellenblatts „Summewenn“ an und benennen Sie die Kopie in „Summenprodukt“ um. Löschen Sie die Spalten D und E im Tabellenblatt „Summenprodukt“. Geben Sie in D2 ein: „Von-Monat:“, in D3: „Bis-Monat:“ und in D4: „Summe:“. Geben Sie in E2 eine „1“ ein und in E3 eine „3“. Nun stellen wir uns die Frage: Was wollen wir tun? Die Antwort lautet: Wir wollen die Werte aus Spalte B summieren, bei denen in der gleichen Zeile in Spalte C eine 1, eine 2 oder eine 3 vorkommt (typischer Fall für eine Quartalsauswertung). Geben Sie bitte in Zelle E4 folgendes ein: =SUMMENPRODUKT((C3:C12>=E2)*(C3:C12<=E3)*(B3:B12)) (Achten Sie bitte genau auf die Klammersetzungen !) Schauen wir uns nun die Syntax für SUMMENPRODUKT an: (C3:C12>=E2) (C3:C12<=E3) (B3:B12) Die Formel: =SUMMENPRODUKT((C3:C12>=E2)*(C3:C12<=E3)*(B3:B12)) Der Versuch einer Erklärung: Ermittle die Zeilen, in denen in Spalte C ein Wert grösser oder gleich dem Wert in E2 zu finden ist, ermittle die Zeilen, in denen in Spalte C ein Wert kleiner oder gleich dem Wert in E3 zu finden ist, und bilde dann die Summe der Zellen in Spalte B, auf die beide Bedingungen zutreffen (Werte in Spalte C grösser oder gleich 1 und Werte in Spalte C kleiner oder gleich 3). Andersherum und etwas plastischer ausgedrückt: Filtere alle Zeilen, in denen in Spalte C ein Wert zwischen 1 und 3 zu finden ist und bilde dann die Summe der „verbleibenden“ Werte aus Spalte B Diese „schwere Kost“ will erst einmal verdaut werden, daher ... 10 min. Pause Zusammenfassung: Erfassen Sie gleichartige Daten nach Möglichkeit immer unter-einander, ohne Zwischensummen zu bilden. Lassen Sie nach Möglichkeit die ersten 2-3 Zeilen für spätere Auswertungen / Berechnungen frei, bevor Sie mit einer Datenerfassung beginnen. Verwenden Sie Überschriften in den Spalten, die idealerweise fett formatiert sein sollten (Erkennung für AutoFilter, Teilergebnisse und vor allem für den Spezialfilter) Kapitel 3: Was Sie unbedingt vermeiden sollten Jeder von uns ist bestrebt, optisch ansprechende Tabellenblätter („Designertabellen“) zu erstellen. Dagegen ist absolut nichts einzuwenden. Allerdings sollten Sie eine „Unart“ unbedingt vermeiden: Zentrieren und Verbinden von Zellen !!! Gegen das Zentrieren von Zellen, beispielsweise um Überschriften „mittig“ über Spalten anzuordnen, spricht nichts. Sehr wohl aber gegen das Verbinden von Zellen: Standardfunktionalitäten wie Sortieren oder auch Kopieren in verbundene Zellen sind nicht mehr möglich, Zellbezüge in Formeln verlieren ihre Referenz, für „Anfänger“ unerklärbare Fehlermeldungen sind die Folge ! Um Überschriften „mittig“ zu positionieren (hier im Beispiel die Überschrift „Zurückgelegte Motorrad-Kilometer“), markieren Sie C3:F3, klicken Sie dann in der Menüleiste auf „Format – Zellen“, wählen Sie die Registerkarte „Ausrichtung“ und dort im Feld „[Textausrichtung] Horizontal“ den Eintrag „Über Auswahl zentrieren“ Das Ergebnis Wenn Sie feststellen, dass Sie in einer Tabelle ganze Zeilen oder Spalten nicht mehr benötigen, löschen Sie nicht nur die Inhalte (z.B. mit der ENTF- oder DEL-Taste), sondern die gesamte Zeile / Spalte (Bearbeiten – Zellen löschen). Excel merkt sich, welche Zellen in Bearbeitung sind / waren. Diese Informationen werden zusammen mit der Arbeitsmappe gespeichert und treiben die Mappengrösse unnötig in die Höhe. Beim Löschen der Zellen werden diese Info‘s aber ebenfalls gelöscht und müssen nicht mehr mitgespeichert werden. Schauen Sie sich dazu eine kleine Videodemonstration an ... Sie können natürlich auch einzelne Zellen physisch löschen, indem Sie die Zellen auswählen und über „Bearbeiten – Zellen löschen“ entscheiden, wie die Zellen gelöscht werden sollen. Achten Sie also darauf, dass Sie nicht benötigte Zellen löschen, statt nur die Inhalte zu entfernen, da sich Excel die Formatierungen der nicht benötigten Zellen merkt, speichert und die Mappengrösse daher unnötig „aufbläht“. Kapitel 4: Tipps & Tricks Erfassen von „Datümern“: Wenn Sie „Datümer“ des aktuellen Jahres erfassen müssen, formatieren Sie die Zellen vor der Erfassung als „Datum“. Für die Erfassung reicht es dann aus, Tag und Monat getrennt durch das „÷“-Zeichen oder das „Minus“-Zeichen auf Ihrem Ziffernblock einzugeben („16÷7“ oder „16-7“ – jeweils ohne „“) und ENTER zu drücken. Probieren Sie es selbst einmal aus, indem Sie in einem beliebigen Tabellenblatt Ihrer geöffneten Mappe einen Zellbereich als Datum formatieren und dann beliebige „Datümer“ erfassen STRG (CTRL) und . liefert Ihnen das aktuelle Tagesdatum, STRG (CTRL) und SHIFT und . liefert die aktuelle Uhrzeit. Ausnutzen der „Vorschlagfunktion“: Fügen Sie ein neues Tabellenblatt in Ihre Arbeitsmappe ein, geben Sie in Zelle A1 ein Hochkomma ein, „ziehen“ Sie A1 bis A10. Geben Sie in A1 „Hans“, in A2 „Peter“ und in A3 „Klaus“ ein. Markieren Sie A6, geben Sie den kleinen Buchstaben „k“ ein und schauen Sie, was passiert ... Drücken Sie ENTER, markieren Sie Zelle A9 und drücken Sie ALT und die „Pfeil-unten“-Taste gleichzeitig ... So macht „Exceln“ Spass ... Ausnutzen der „AutoKorrektur“: Um Uhrzeit- oder Stundenangaben zu erfassen, ist es lästig, den für eine korrekte Formatierung benötigten Doppelpunkt einzugeben (man braucht beide Hände !). Hier kann die AutoKorrektur („Extras – AutoKorrektur“) helfen: Sie lassen einfach zwei hintereinander eingegebene Pluszeichen durch den Doppelpunkt ersetzen. Nun können Sie mit der Hand über dem Ziffernblock „schwebend“ Zeitangaben erfassen ... Suchen und Ersetzen: Über „Bearbeiten – Ersetzen“ können Sie in einem bestimmten Bereich oder im gesamten Tabellenblatt einzelne Zeichenfolgen durch andere Zeichenfolgen ersetzen. Dies schlägt allerdings fehl, wenn Sie nach „*“ oder „?“ suchen: Diese Zeichen werden von Excel als „Joker“ oder „Wildcards“ interpretiert und Excel ersetzt ALLES. Setzen Sie hingegen eine Tilde (~, darstellbar durch ALT GR und +) vor „*“ oder „?“, so funktioniert „Suchen & Ersetzen“ Formel- oder Wertanzeige: Sie können innerhalb einer Tabellenblattes zwischen der Formelanzeige und der Wertanzeige hin- und herschalten, indem Sie STRG (CTRL) und # drücken: Hyperlink: Sie müssen einen Hyperlink im Tabellenblatt bearbeiten, können die Zelle aber nicht mit der Maus aktivieren, da sonst sofort der Link ausgeführt wird? „Wählen“ Sie die Zelle mit den Pfeiltasten an oder markieren Sie die Zelle mit der Maus, halten aber die linke Maustaste etwa 2 sec. gedrückt. Rechnen mit Brüchen: Wenn Sie im Tabellenblatt mit Brüchen (2/7 oder 1/3) rechnen müssen / wollen, erfassen Sie die Bruchzahl mit einer vorangestellten „0“ und einem Leerzeichen (0 2/7). Relative / Absolute Zellbezüge: Der Unterschied zwischen relativen und absoluten Zellbezügen besteht darin, dass sich relative Bezüge beim Kopieren / „Ziehen“ einer Funktion / Formel anpassen, absolute Bezüge hingegen „starr“ auf „fest verdrahtete“ Zellen referieren. Wie können wir, je nach Notwendigkeit, die Bezüge von „absolut“ auf „relativ“ und umgekehrt ändern? Nun, eine Möglichkeit besteht darin, in der Bearbeitungsleiste vor jeden Spalten- / Zeilenbezug ein „$“-Zeichen einzufügen bzw. zu löschen. Dazu ist es aber erforderlich, den Mauszeiger genau an die Stelle zu positionieren, an der eingefügt bzw. gelöscht werden soll. Einfacher geht‘s, indem man den Bezug in der Bearbeitungszeile markiert und so oft F4 drückt, bis der Bezug passt ... Aus- / Anwahl von Tabellenblättern: In Ihrer Arbeitsmappe befinden sich mehr als 16 Tabellenblätter und Sie verlieren den Überblick? Klicken Sie mit der rechten Maustaste mal im Tabellenblattregister auf die Navigationspfeile ... ... und dann auf „Weitere Blätter...“ Wir haben etwas verdient, und zwar ... 10 min. Pause Kapitel 5: Benutzerdefinierte und „Besondere“ Zellformatierungen Erfassen Sie in einem neuen Tabellenblatt die Daten / Werte in Spalte A, geben Sie in Zelle B2 die Formel „=A2“ (ohne““) ein und ziehen Sie die Formel bis B9 herunter. Formatieren Sie die Zellen B2:B9 anschliessend „benutzerdefiniert“ so, wie in Spalte C angegeben Mit Hilfe von Sonder- und Benutzerdefinierten Formaten können wir das, was tatsächlich in einer Zelle steht (Zellinhalt), anders anzeigen lassen. Genialerweise können wir eine solche Darstellung auch über Formeln erreichen: Geben Sie in einem neuen Tabellenblatt in Zelle B2 folgende Formel ein: =WENN(A1=7;"Ermässigter Umsatzsteuersatz";"Voller Umsatzsteuersatz") Geben Sie in Zelle A1 nun abwechselnd die Zahlen 7 und 16 ein Um nur die Eingaben von 7 oder 16 zuzulassen, verwenden wir die Gültigkeitsprüfung: Markieren Sie A1, klicken Sie in der Menüleiste auf „Daten – Gültigkeit“. Unter „Einstellungen – Zulassen“ wählen Sie „Liste“, als „Quelle“ tragen Sie ein: 7;16 Nun können Sie noch die „Fehlermeldung“ formulieren, eine Eingabe von Werten <> 7 oder <> 16 wird abgelehnt. Kapitel 6: Shortcuts (auch „Affengriffe“ genannt) Excel (wie auch die übrigen Applikationen der Office-Familie) lässt sich vollends ohne Maus, sondern nur über die Tastatur bedienen. Teilweise sind die dafür notwendigen Tastenkombinationen sogar schneller und bequemer als das Arbeiten mit der Maus. Einige dieser „Shortcuts“ werden wir nun kennenlernen: Damit sind wir am Ende der Grundlagenschulung angekommen, ich darf mich für Ihre Aufmerksamkeit und Mitarbeit bedanken. Ich hoffe, ich konnte Sie ein wenig neugieriger auf die fantastischen Möglichkeiten von Excel machen und wünsche Ihnen nun „frohes Schaffen“ mit Excel. Denken Sie daran: Es ist noch kein Meister vom Himmel gefallen, viele unserer Errungenschaften sind durch „Try and Error“ entstanden. Scheuen Sie sich daher nicht, „Neues“ in Excel auszuprobieren und zu versuchen, Aufgaben in Excel zu lösen. Klaus-Martin Buss von-Gauvain-Str. 2 55442 Stromberg [email protected] www.kmbuss.de Tel.: 0 1 71 / 6 85 92 03 Fax: 0 40 / 36 03 85 23 30 skype: Kl@us-M. ICQ: 282368468 AOL: excel2000lex
