Embed Size (px)
Citation preview
Excel 2000Grundlage
n
In dieser Excel-2000-Grundlagenschulung soll Ihnen die
Bedienung und Handhabungvon 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 einigensog. Shortcuts (Tastenkombinationen) beschäftigen.
Zwischenfragen und Verständnisprobleme sollen nachMö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 Tabellenkalkulations-programm entwickelt: VisiCalc, das immerhin eine Matrix von 168 Zellenver- / bearbeiten konnte.
Anfang der 80‘er-Jahre entwickelte Microsoft schliesslich MultiPlan, denVorlä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
TitelleisteMenüleiste
Symbol-leiste(n)
Namen- oder Adressfeld in der ...
... Bearbeitungs-leiste
Zeilen- & Spaltenköpfe
(Tabellen-)Blattregister
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
Zunächst wollen / müssen wir einige
Begriffsbestimmungen vornehmen, die uns im
Laufe der Schulung immer wieder begegnen werden. Wir beginnen mit der ...
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.2005123,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 (Programmier-
sprache, „Visual Basic for Applications“) nicht möglich. Excel inter-pretiert 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
alsBruchteile, bzw. Nachkommstellen interpretiert. Der 01.01.1900,
12:00 Uhrist 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,000000000000000Das 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 Uhr-zeiten:
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 fol-gendes 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ängtwiederum damit zusammen, dass Excel eine AutoFormatierung vorge-nommen hat und in A5 und A6 (Uhr-)Zeiten erkannt hat. Also stellt Exceldas Ergebnis in A7 ebenfalls als Uhrzeit dar, eine Uhrzeit grösser 23:59kann 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 Sieweitere interessante Zellformatierungen, aufdie wir dann nochnäher eingehen ...
Bevor uns die kleinen„Excel-Geister“ nun überfordern
...
Bevor uns die kleinen„Excel-Geister“ nun überfordern
...
... legen wir 5 min.Pause ein ...
Kapitel 2:Sinnvoller Aufbau von Tabellenblättern
und die daraus resultierendenMöglichkeiten von Tabellenblatt-
auswertungen
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, nutzenwir einen kleinen, aber feinen Trick: Excel erkennt das laufendeKalenderjahr anhand des Systemdatums und dies machen wir unsnun zunutze.Markieren Sie A2 und klicken Sie doppelt auf den Formatpinselin der Standardsymbolleiste.
Der Mauszeiger hat sich zum „Schweizer Kreuz“ verändert, neben dem einkleiner Pinsel zu sehen ist (und Zelle A2 hat eine umlaufende Markierung).Wenn wir nun andere Zellen markieren, wird das Format der Zelle A2 aufdie markierten Zellen übertragen. Dies funktioniert sowohl für einzelneZellen als auch für zusammenhängende Bereiche. Markieren Sie daher mitgedrückter linker Maustaste die Zellen A3:A11 und lassen die Maus los.Nun haben wir das Datumsformat auf den Bereich A3:A11 übertragen. Fürdas Erfassen der übrigen „Datümer“ reicht nun dieEingabe „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 unter-
schiedlichen 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 dieZellen 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: Das1. Quartal beinhaltet alle „Datümer“ vom 01.01.2005 bis 31.03.05. Da wirnur „Datümer“ aus 2005 in unserer Tabelle haben, sollte die Definitioneigentlich recht einfach sein: Alle „Datümer“, diekleiner 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 „BenutzerdefiniertenAutoFilter“, indem Sie die Kriterien verändern
und dies mit „Und / Oder“ kombinieren ...
Doch zurück zu unserer Datentabelle: Wir haben nun die Daten des1. 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-
Applikationenzur Verfügung ...Wir werden also nun „Karl Klammer“ oder „F1“ um Hilfe bitten,
damitwir herausbekommen, wie die Summe der Werte im 1. Quartal
gebildetwerden kann ...
Vorher jedoch noch ein Praxistipp: Bilden Sie Summen, Auswertungen, Berechnungen usw. nach Möglichkeit IMMERoberhalb Ihrer Listen. Dies hat den Vorteil, dass Ihre Liste „nachunten“ offen und flexibel ist, sie aber trotzdem in Ihren Summen-,Auswertungs- und Berechnungsfunktionen und –formeln die rich-tigen Ergebnisse angezeigt bekommen.Nehmen wir unsere Tabelle als Beispiel: Würden wir die gleicherläuterte Funktion =TEILERGEBNIS() in Zelle A12 einfügen,hätten wir keine Möglichkeit, die Liste zu erweitern, da wir unsja 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 denblauen Pfeil anklicken und den Eintrag „Alle“ auswählen ...
Der „AutoFilterPfeil“ solltenun 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 mitExcel2000 arbeiten, die Screenshots für diesesScript 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 Tabellen-
blattfunktionen mit den entsprechenden Syntaxen und Argumenten er-
lä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 erstab XL 2003 zur Verfügung ...
Wir erinnern uns: Wir wollen eine Summe bilden.Also müssen wir die Funktion ...
... richtig:
verwenden.
9Kehren 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
heran-gezogen) werden sollen. Dazu stellen wir den Mauszeiger in das
Feld„Bezug 1“ und markieren im Tabellenblatt die Zellen B3:B12 (solltendie Zellen vom Kontextmenü verdeckt sein, verschieben Sie diesesmit 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 zusam-
menzufassen, zu gruppieren und daraus Auswertungen anzuzeigen. Voraus-
setzung 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 aus-
wertbare 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 Kriterienbe-schreibung zulässt, müssen wir das Kriterium im Tabellenblatt
erstellen.Um aus den Datümern in A3:A12 die jeweiligen Monate zu „isolieren“,
ver-wenden wir die Funktion =MONAT().
Schreiben Sie in Zelle C2 den Text „Hilfsspalte für Monat“, in ZelleC3 tragen Sie die Formel „=MONAT(A3)“ (ohne „“) ein. „Ziehen“ SieC3 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 STRG-Tasten 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 E5567ein 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. MarkierenSie nun eine beliebige Zelle im Bereich A2:C12 und klicken Sie auf„Daten – Teilergebnisse“
Im Kontextmenü haben wir nunmehrere „Einstellmöglichkeiten“:Klicken Sie das Feld „Gruppierennach:“ auf. Dort finden wir unseredrei Spaltenüberschriften wieder,nach denen wir eine Gruppierungvornehmen können.Im Feld „Unter Verwendung von:“ finden wir die wichtigsten Funk-tionen wieder, die wir auch als Tabellenblattfunktionen kennen.Im Feld „Teilergebnisse addierenzu:“ können wir angeben, welcheDaten verarbeitet werden sollen.Die Checkboxen schliesslich sindnahezu selbsterklärend und sollten für unsere Auswertung nicht verändert werden.
Versuchen Sie, die Parameter so zu setzen, dass Sie die Summender „Werte“ nach Monaten erhalten (keine Angst: Selbst wenn Sienicht die gewünschten Ergebnisse erzielen, „zerschiessen“ Sie IhreDaten nicht, denn Sie können die Teilergebnisse jederzeit löschen).
Wenn Ihre Parameter so aussahen,sind Sie zum gewünschten Ergebnisgelangt ...
Bevor wir uns nun einer weiteren, nicht ganz einfachen Auswertungs-möglichkeit widmen, machen wir erstmal ...
10 min.
Pause
Heben Sie die „Teilergebnisse“ nun wieder auf.Sie sehen: Am Tabellenblattaufbau und an Ihrem Datenbestand hat
sichnichts verändert, die von Excel eingefügten Zeilen mit den
Zwischen-ergebnissen 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 setzenSie den Haken in der CheckBox „Kopie erstellen“.
Sie haben nun ein weiteres Tabellenblatt mit Namen „Tabelle2(2)“ inIhrer Arbeitsmappe.
Benennen Sie dieses nach einem Doppelklick auf den Tabellenblatt-reiter 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ötigenWechseln Sie ins Tabellenblatt „Summewenn“
Nun wollen wir uns mit einer weiteren Auswertungsmöglichkeit be-schä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.
... sind die Zellen, die Sie summieren wollen (B3:B12)
... ist das Suchkriterium als Zahl, Formel oder Text (E2)
... ist der Zellbereich, den Sie berechnen wollen (C3:C12)
Bereich: Der Bereich ist C3:C12, denn das sind die Zellen, die wir in Ab- hängigkeit des Suchkriteriums auswerten wollen. Die Onlinehilfe spricht hier vom Zellbereich, der „berechnet“ werden soll, ver- ständlicher wäre wohl „ausgewertet“
Suchkriterium: Nach welcher Zahl (Formel, Text) wollen wir auswerten: Nach der in Zelle E2 eingegebenen MonatszahlSumme_Bereich: Welche Zellen sollen summiert werden: Die Werte in den Zellen B3:B12Andersherum formuliert: Es sollen alle Zellen aus Spalte B summiert wer-den, bei denen sich in der gleichen Zeile in Spalte C der Wert aus E2 wie-derfindet (schwere Kost )
Ändern Sie nun den Wert in E2, schauen Sie, wie sich das Ergebnisin E3 verändert und prüfen Sie Ihre Ergebnisse, indem Sie sie mitden Ergebnissen im Tabellenblatt „Teilergebnisse“ vergleichen ...
Tabellenblatt „Summewenn“
Tabellenblatt„Teilergebnisse“
Die letzte Auswertungsmöglichkeit (ganz, ganz schwere Kost
), dievorgestellt 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 be-nennen Sie die Kopie in „Summenprodukt“ um. Löschen Sie die Spalten D und E im Tabellenblatt „Summenprodukt“. Geben Sie inD2 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, beidenen in der gleichen Zeile in Spalte C eine 1, eine 2 oder eine 3
vor-kommt (typischer Fall für eine Quartalsauswertung).
Schauen wir uns nun die Syntax für SUMMENPRODUKT an:
Geben Sie bitte in Zelle E4 folgendes ein:
=SUMMENPRODUKT((C3:C12>=E2)*(C3:C12<=E3)*(B3:B12))
(Achten Sie bitte genau auf die Klammersetzungen !)
(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 denenin Spalte C ein Wert grösseroder gleich dem Wert in E2zu finden ist,
ermittle die Zeilen, in denenin 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 danndie 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 Aus-wertungen / Berechnungen frei, bevor Sie mit einer Datenerfassung beginnen.
Verwenden Sie Überschriften in den Spalten, die idealerweise fettformatiert sein sollten (Erkennung für AutoFilter, Teilergebnisse undvor 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 ein-
zuwenden. 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: Standard-
funktionalitäten wie Sortieren oder auch Kopieren in verbundeneZellen 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 Über-
schrift „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 In-
formationen 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 mit-
gespeichert 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 be-nö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 beliebigenTabellenblatt Ihrer geöffneten Mappe einen Zellbereich als Datumformatieren 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, gebenSie in Zelle A1 ein Hochkomma ein, „ziehen“ Sie A1 bis A10. GebenSie in A1 „Hans“, in A2 „Peter“ und in A3 „Klaus“ ein. Markieren SieA6, geben Sie den kleinen Buchstaben „k“ ein und schauen Sie, waspassiert ...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 einekorrekte Formatierung benötigten Doppelpunkt einzugeben (man brauchtbeide Hände !). Hier kann die AutoKorrektur („Extras – AutoKorrektur“) helfen:
Sie lassen einfachzwei hintereinandereingegebene Plus-zeichen durch denDoppelpunkt er-setzen.Nun können Sie mitder Hand über demZiffernblock „schwebend“ Zeit-angaben erfassen ...
Suchen und Ersetzen: Über „Bearbeiten – Ersetzen“ können Sie in einembestimmten Bereich oder im gesamten Tabellenblatt einzelne Zeichen-folgen 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 Siehingegen eine Tilde (~, darstellbar durch ALT GR und +) vor „*“ oder „?“,so funktioniert „Suchen & Ersetzen“
Formel- oder Wertanzeige: Sie können innerhalb einer Tabellenblatteszwischen 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 derLink ausgeführt wird? „Wählen“ Sie die Zelle mit den Pfeiltasten an odermarkieren Sie die Zelle mit der Maus, halten aberdie 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 voran-gestellten „0“ und einem Leerzeichen (0 2/7).
Relative / Absolute Zellbezüge: Der Unterschied zwischen relativen undabsoluten Zellbezügen besteht darin, dass sich relative Bezüge beimKopieren / „Ziehen“ einer Funktion / Formel anpassen, absolute Bezügehingegen „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 jedenSpalten- / Zeilenbezug ein „$“-Zeichen einzufügen bzw. zu löschen. Dazuist 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 markiertund so oft F4 drückt, bis der Bezug passt ...
Aus- / Anwahl von Tabellenblättern: In Ihrer Arbeitsmappe befinden sichmehr als 16 Tabellenblätter und Sie verlieren den Überblick? Klicken Siemit der rechten Maustaste mal im Tabellenblattregister auf die Navigations-pfeile ... ... 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 inSpalte A, geben Sie in Zelle B2 die Formel „=A2“ (ohne““) ein undziehen Sie die Formel bis B9 herunter. Formatieren Sie die ZellenB2: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 Darstellungauch ü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 wirdie Gültigkeitsprüfung: Markieren Sie A1, klicken Sie in der Menüleiste auf „Daten – Gültigkeit“. Unter „Einstellungen – Zu-lassen“ wählen Sie „Liste“, als „Quelle“ tragen Sie ein: 7;16Nun können Sie noch die „Fehlermeldung“ formulieren, eineEingabe von Werten <> 7 oder <> 16 wird abgelehnt.
Kapitel 6:Shortcuts (auch „Affengriffe“ genannt)
Excel (wie auch die übrigen Applikationen der Office-Familie) lässt sichvollends ohne Maus, sondern nur über die Tastatur bedienen. Teilweisesind die dafür notwendigen Tastenkombinationen sogar schneller undbequemer als das Arbeiten mit der Maus. Einige dieser „Shortcuts“ werdenwir 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 fantastischenMö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 Bussvon-Gauvain-Str. 255442 Stromberg
www.kmbuss.de
Tel.: 0 1 71 / 6 85 92 03Fax: 0 40 / 36 03 85 23 30
skype: [email protected]: 282368468
AOL: excel2000lex
