Chip (Workshop-Ausgabe 2001) [PDF]

Zitiervorschau

Mit CD!

W O R K S H O P 1. Workshop-Ausgabe 2001 DM

19,90

ÖS sfr hfl

165,19,90 25,-

56814

lfr/bfr 480,Lit 24.000 Ptas 2.000

Sofort loslegen! 145 Musterdateien, Makros & Vorlagen auf CD

o- s e id op V 17orksh W D: fC u A

Excel Schnell einsteigen So kommen Sie sofort mit Excel zurecht

Charts gestalten So gelingen blitzschnell perfekte Diagramme

Formeln einsetzen So meistern Sie auch komplexe Berechnungen

Makros einbinden So nutzen Sie Excel noch effizienter

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN »

SERVICE Inhalt

EDITORIAL

» Gewusst wie: Excel ganz praktisch Jeder kennt es, fast jeder hat es, aber seine Funktionsvielfalt wird in der Regel nur unzureichend genutzt. Die wenigsten Anwender können von sich beAndreas Vogelsang, haupten, Excel wirkLeitender Redakteur lich auszureizen. Dabei ist ehrfürchtige Zurückhaltung gegenüber der komplexen Software gar nicht angebracht. Dieses CHIP-Sonderheft hilft Ihnen mit zahlreichen Workshops, Excel effizient einzusetzen. Wichtige, interaktive Arbeitsunterlagen finden Sie auf der Heft-CD. 17 moderierte Video-Workshops zeigen Ihnen den richtigen Umgang mit Excel, und weit über 100 Musterdateien und Vorlagen ermöglichen Ihnen, die Heft-Workshops sofort in die Praxis umzusetzen. Viel Erfolg!

EXCEL-TUNING 4 12 14 18

Komfortabler arbeiten mit Musterdateien Datums- und Zeitwerte berechnen Formulare mit Schaltern entwerfen Schneller ausfüllen mit der Maus

CHARTS 22 26 30 32 34

Excel-Diagramme: Zahlen perfekt veranschaulichen Diagramme: Wie Zahlen und Bilder zusammenpassen Landkarten: Zahlen geographisch einordnen Überzeugungsarbeit: Schlechte Zahlen kaschieren Grafiken: So peppen Sie Ihre Diagramme auf

FUNKTIONEN 36 42 46 50 58

Datenbank-Funktion: Mittel- und Extremwerte Pivot-Tabellen: Große Datenmengen auswerten Web-Export: Excel im Internet Solver: Rechnen mit mehreren Variablen Excel 2002: Alle wichtigen Funktionen

IMPRESSUM Chefredakteur: Thomas Pyczak Ltg. CHIP-Sonderpublikationen: Andreas Vogelsang Redaktion: Andreas Vogelsang (verantw.), Dr. Günter Neumann Autoren dieser Ausgabe: Karl Dreyer, Ischta Lehmann CD-Produktion: Thomas Pelkmann Produktmanagement Sonderpublikationen: Sabine Eckl-Thurl (Ltg.), Angelika Reinhard Grafische Gestaltung: Dana Fidlerova Titel: Volker Hildebrand Bildredaktion: Iris Klaus, Martina Siegmund Geschäftsführer: Hans-Günther Beer, Dr. Markus Witt Objektleitung Sonderpublikationen: Peter Deppner Herstellung: Dieter Eichelmann Verlag: Vogel Burda Communications GmbH, Poccistraße 11, 80336 München,Tel. (089) 746 42-0, Fax: (089) 74 60 56-0 Die Inhaber- und Beteiligungsverhältnisse lauten wie folgt: Alleinige Gesellschafterin: chip Holding GmbH mit dem Sitz in Poccistraße 11, 80336 München, an der mit je 50 Prozent beteiligt sind: Vogel GmbH mit dem Sitz in MaxPlanck-Straße 7/9, 97082 Würzburg; Burda GmbH mit dem Sitz in Hauptstraße 130, 77652 Offenburg Druck: AVD Goldach, CH-9403 Goldach Nachdruck: © 2001 by Vogel Burda Communications GmbH. Nachdruck nur mit schriftlicher Genehmigung der Redaktion erlaubt. Christiane Bertsch , Tel. (089) 746 42-124 Anzeigenverkauf: Brigitta und Karl Reinhart, Tel. (089) 46 47 29, Fax: (089) 46 38 15 Bereichsleiter Vertrieb: Michael Mair Vertrieb Einzelverkauf: ASV Vertriebs GmbH, Süderstr. 77, 20097 Hamburg, Tel. (040) 34 72 40 41

FORMELN/BEZÜGE 60 65 66 68 70 72

Variable Bezüge: Euro-fähiges Kassenbuch anlegen Excels Detektiv: Fehler suchen und beseitigen Zellen formatieren: Bedingte Formatierung Verweise: Bezüge über das Arbeitsblatt hinaus Automatismen: 3D-Bezüge herstellen Matrix-Formeln: Eingabe-Kontrolle anlegen

LÖSUNGEN 74 84 86 92 94 96

Tipps & Tricks: Mehr machen mit Excel Makros: Aufzeichnen und einbinden Makro-Praxis I: Haushaltsbuch entwerfen Makro-Praxis II: Festplatten-Analyse Makro-Praxis III: Analoge Uhr einbinden Makro-Praxis IV: „Vier gewinnt“ im Excel-Sheet

Schreiben Sie uns! Anregungen, Fragen, Kritik? C -Sonderpublikationen [email protected] Fax: (089) 74642-368

SERVICE 10 98

Alles über die Heft-CD zu dieser Ausgabe Vorschau: Das nächste CHIP-Sonderheft

CHIP | WORKSHOP

3

4 INHALT Musterdateien anlegen Perfekte Excel-Dateivorlagen

12

Jahreskalender 2002 Datums- und Zeitwerte

14

Schalter einsetzen Excel-Formulare in der Praxis

18

Automatisches Ausfüllen Schneller arbeiten mit Excel

Perfekte Dateivorlagen

Komfortabler arbeiten mit Musterdateien Words Formatvorlagen, die so genannten DOTs, kennt und schätzt jeder. Dass auch Excel den Umgang mit Musterdateien beherrscht, wissen hingegen nur wenige. Wir erklären Ihnen Schritt für Schritt, wie Sie sich eine perfekte XLT-Vorlage basteln.

M

usterdateien bieten sich an, wenn Sie oft mit gleichen Tabellenstrukturen und gleichen Zellformatierungen, jedoch unterschiedlichen Werten rechnen müssen. Sie ersparen sich viel Zeit, indem Sie nur noch die neuen Zahlen in das Vorlagen-Sheet eingeben. Excel „merkt“ sich die Formatierungen, die erforderlichen Berechnungen führt es im Idealfall automatisch durch. Als Anwendungen kommen Fahrtenbücher, Angebote an Geschäftskunden, Haushaltsbücher, Stundenpläne und vieles mehr in Frage.

1

Mustervorlage „Rechnung.xlt“ laden und bearbeiten

Wie gut Excel mit Mustervorlagen umgehen kann, beweist Ihnen eine Rechnungsvorlage, die bereits auf jedem Office-

CHIP | WORKSHOP

Rechner installiert ist. Sie öffnen die Datei „Rechnung.xlt“, und zwar über den Befehl „Datei“, „Neu“. Klicken Sie beispielsweise doppelt auf das Dateisymbol im Register „Tabellenvorlagen“ (Excel 2000: Arbeitsblattlösungen). Im folgenden Dialog bestätigen Sie unbedingt die Option „Makros aktivieren“, sonst funktioniert die Vorlage nicht. Makroviren brauchen Sie hier ausnahmsweise nicht zu befürchten. Diese Rechnungsvorlage erinnert kaum noch an ein Excel-Sheet. Dennoch können und müssen Sie einzelne Zellen verändern, wenn Sie tatsächlich später mit dieser Vorlage arbeiten wollen; denn die XLT-Datei(en) sowohl in Excel 97 als auch in Excel 2000 enthalten Fehler. Zum einen sind alle Zellen, die Preise enthalten, derzeit ausschließlich auf DM-Werte formatiert, zum anderen geht Excel noch

von einem Mehrwertsteuersatz von 15 Prozent aus. Einen Teil der Änderungen nehmen Sie über den Button „Anpassen“ rechts oben im Sheet vor. Excel öffnet eine weitere Arbeitsmappe, in der Sie persönliche Angaben zu Ihrer Firma und zu den Mehrwertsteuern machen können. Die Umrechnung von DM- in Euro-Werte müssen Sie dann jedoch per Hand erledigen, indem Sie beispielsweise eine weitere Spalte einfügen. Beide aktuellen Office-Versionen haben in den weiteren Registern übrigens zahlreiche ältere Vorlagen aufgelistet, die allerdings mit den gleichen Schwächen behaftet sind. Aus diesem Grund ist es in manchen Fällen einfacher und schneller, sich eine eigene Vorlage zu basteln, bevor Sie zuviel Zeit mit Korrekturen an bestehenden

Composing: D. Fidlerova

4

EXCEL-TUNING

5

» CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN » SERVICE

Musterdateien

3

Vorlagen aufwenden. In diesem Workshop sollen die wichtigsten Elemente und Funktionen einer neuen Mustervorlage am Beispiel eines Angebotsschreibens erläutert werden. 2

Neue Formatvorlagen einstellen

Sie rechnen meist mit DM-Beträgen? Die voreingestellte Textgröße von „10 Punkt“ ist Ihnen zu klein, und Sie wollen die Zahlen in den Zellen grundsätzlich „mittig“ setzen? Dann sollten Sie eine entsprechende Formatvorlage definieren. Künftig kostet Sie dann die passende Formatierung lediglich einen Mausklick. Wählen Sie „Format, Formatvorlage“. Sie entdecken im neuen Dialogfenster vier Voreinstellungen, wenn Sie im DropDown-Feld nach unten klicken: Dezimal, Prozent, Standard, Währung. Eine auf Ihre Wünsche zugeschnittene Vorlage erstellen Sie, indem Sie einen der aufgeführten Titel im Eingabefeld „Formatvorlagenname“ mit einem aussagekräftigen Namen überschreiben. In Excel 2000 würde sich eine Vorlage für Euro-Werte anbieten. In Excel 97 ist diese Währung leider noch nicht aufgeführt. Klicken Sie dazu rechts auf die Schaltfläche „Ändern“.

Im Register „Zahlen“ suchen Sie unter „Kategorie“ eine passende Einheit für die Vorlage – etwa Währung. Im Register „Ausrichtung“ wählen Sie beispielsweise „Zentriert“ im Feld „Horizontal“. Dann stehen später alle Werte mittig in den Zellen, falls Sie diese Formatvorlage anwenden. Im Register „Schrift“ ist es sinnvoll, eine andere Größe (etwa „11“) und eine andere Farbe auszusuchen, wenn Sie die Werte hervorheben möchten. Die Voreinstellungen der Karteireiter „Rahmen“, „Muster“ und „Schutz“ müssen Sie nicht unbedingt korrigieren. Bestätigen Sie Ihre Korrekturen zweimal mit „OK“. Die Formvorlage ist nun gespeichert. Und so wenden Sie sie künftig an: Markieren Sie einen beliebigen Zellbereich. Dabei spielt es keine Rolle, ob die Zellen leer sind oder bereits Werte enthalten. Wählen Sie dann wiederum „Format, Formatvorlage“. Suchen Sie Ihr Muster aus und bestätigen Sie mit „OK“. Excel wendet die Zellformatierung sofort an. k

Autoformat einsetzen

Zellen markieren, unterschiedlich einfärben, Rahmen- ein und ausblenden, die Schriftgröße einstellen und die perfekte Spaltenbreite ermitteln – all das sind wesentliche Arbeitsschritte bei der Gestaltung einer optisch ansehnlichen Tabelle. Diese Arbeit nimmt Ihnen Excel gern mit Hilfe zweier Mausklicks ab. Markieren Sie mit der Maus einen beliebigen Zellbereich (im Beispiel haben wir den Bereich von A8 bis H20 gewählt) und klicken Sie anschließend auf „Format“, „Autoformat“. Excel zeigt Ihnen daraufhin in einem kleinen Vorschaufenster vorbereitete Muster mit passend formatierten Zeilen, Spalten und Zellen. Vor allem die Vorschläge „Farbig 1“ und „3DEffekt 2“ verdienen Ihre Aufmerksamkeit. Wählen Sie einen der Vorschläge aus und bestätigen Sie dann mit „OK“. Excel wendet im Handumdrehen die gewünschte Formatierung auf den von Ihnen markierten Zellbereich an. Mit den unterschiedlichen Autoformat-Einstellungen geben Sie Ihrer Tabelle nun optisch den Feinschliff. Auswirkungen auf die Zellinhalte und deren Formatierungen, etwa Zähleinheiten, haben die Autoformat-Einstellungen nicht. Wie Sie die „auf einen Schlag“ ändern, zeigt der nächste Schritt.

Wenn Sie

1

über „Da-

ein neues Sheet anlegen, entdecken Sie im Register „Vorlagen“ das Muster „Rechnung.xlt“. Um es zu benutzen, müssen Sie unbedingt die veralteten Mehrwertsteuersätze korrigieren. tei“, „Neu“

Unter

2

„Forma-

te“, Autofor-

finden Sie ansprechende Tabellenmuster, die Sie per Mausklick auf einen von Ihnen markierten Zellenbereich anwenden. mate“

CHIP | WORKSHOP

6

Wie

4

bei

Word-Mustervorlagen

richtet man auch hier Kopf-/Fußzeilen ein. Das Datum integrieren Sie per Klick auf den Button „Datum“.

Für eine Mustervorlage müssen Sie auch die (noch) leeren Zellen vorformatieren, etwa auf Währung. Jedoch hat nur Excel 2000 bereits den Euro im Programm.

3

4

Kopf- und Fußzeilen definieren

Falls Sie in Excel eine Mustervorlage benötigen, die Sie auch mal oder ausschließlich an Kunden verschicken, sollten Sie Wert auf eine optisch ansprechende und briefähnliche Formatierung des Tabellenblattes legen. Kopf- und Fußzeilen wie aus Word bekannt sind dabei unverzichtbar. So geht’s: Klicken Sie auf „Ansicht“, „Kopf- und Fußzeile“. Im Register „Kopfzeile/Fußzeile“ entscheiden Sie sich zunächst für die Option „Benutzerdefinierte Kopfzeile“. In der Dialogbox „Kopfzeile“ stellt Excel drei Eingabeboxen zur Auswahl. Tragen Sie dort Text ein, platziert Excel die Zeilen je nach Box am linken oberen Seitenrand, am rechten oberen Seitenrand oder oben in der Mitte. Klicken Sie beispielsweise mit der Maus in die Box „Linker Abschnitt“ und dann auf das Icon mit den Kalenderblättern, setzt Excel automatisch das aktuelle Datum ins linke obere Eck der Mustervorlage. Das Datum wird danach bei jeder geöffneten Datei aktualisiert, die auf dieser Mustervorlage beruht. In „Mittlerer Abschnitt“ tragen Sie Ihren Namen oder Firmennamen ein. Excel gibt Ihnen die Möglichkeit, den Text über die Schriftart und die Schriftgröße zu variieren. Machen Sie davon Gebrauch. In die Box „Rechter Abschnitt“ gehört Ihre Anschrift. Schließen Sie die Arbeit an der Kopfzeile mit „OK“ ab. Die Fußzeile bestücken Sie auf Wunsch genauso. Die Dialogboxen zum Editieren des Textes sind völlig identisch. Wollen Sie allerdings keine Bankverbindung angeben, sollten Sie diesen Bereich leer lassen. 5

Bilder und Grafiken einfügen

Vor allem, wenn Ihre Vorlage später als offizielles Anschreiben dienen soll, etwa als Angebot, muss eine Grafik als Blickfang her. In der Regel sollten Sie Ihr Fir-

CHIP | WORKSHOP

menlogo rechts oben im Sheet platzieren. In der Beispieldatei „Muster.xlt“ soll das Bild eines vorher geladenen Luxusautos eingebunden werden. Die zugehörigen Excel-Befehle lauten „Einfügen“, „Grafik aus Datei“. Im Dialog „Bild einfügen“ bestimmen Sie den Ordner und die passende Bilddatei. Excel setzt die Grafik sofort in das Sheet. Mit Hilfe der Maus ändern Sie die Größe und die exakte Position. Aber Excel kann noch mehr. Sie erkennen eine kleine Toolbox inmitten Ihres Arbeitsblattes. Wenn Sie mit der Maus über die Symbole fahren, erklären sich die Werkzeuge. Hier haben Sie die Möglichkeit, das Bild auf Ihre Bedürfnisse zuzuschneiden, den Kontrast oder die Helligkeit zu verändern. Am interessantesten ist die Funktion „Grafik formatieren“, die sich hinter dem Symbol mit Pinsel und Farbeimer verbirgt. Wenn Sie darauf mit der Maus klicken, haben Sie alle wesentlichen Bearbeitungsoptionen in einem komfortablen Dialogfeld, dessen Funktionen Sie über die Register steuern: Vergrößern, verkleinern, Kontrast, Helligkeit, Einfärben, Rahmen und vieles mehr. Für Logos bietet sich vor allem die Option

„Wasserzeichen“ an, die Ihr eingebettetes Bild nahezu transparent erscheinen lässt. Neben „fremden“ Grafiken greift Excel auf Wunsch auf hauseigene Bildchen zurück. Gehen Sie über „Einfügen“, „Grafik“, „ClipArt“, stellt Ihnen die Office-Anwendung in einem Fenster vorinstallierte Grafiken vor. Sie sind recht schlicht, können aber für Illustrationen – vor allem bei Diagrammen – wirkungsvoll sein, da sie keinerlei Erklärungen benötigen. Ein passendes Symbol für Ihre Zwecke finden Sie (vielleicht), wenn Sie in die Zeile für „Clips suchen“ einen Suchbegriff wie „Auto“, „Sport“ oder „Tier“ eingeben und mit „OK“ bestätigen. Neben den Autoformen und WordArtElementen kann Excel 2000 im Gegensatz zu Excel 97 sogar Bilder unmittelbar von einer Digitalkamera oder einem Scanner importieren. 6

Mustervorlage zuschneiden

Ob die eingebundene Grafik, Ihre Kopfund Fußzeilen und natürlich auch das zentrale Tabellenelement perfekt sitzen, kontrollieren Sie über die Befehle „Datei“, „Seitenansicht“. Beispielsweise könnten

Über

5

„Grafik“,

„Einfügen“,

betten Sie Bilder ins Sheet ein. Platzierung und Größe bestimmen Sie per Maus. Außerdem ändert Excel auf Wunsch Helligkeit und Kontrast oder versieht das Bild mit einem WasserzeichenEffekt. „Datei“

EXCEL-TUNING

7

» CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN » SERVICE

Musterdateien

sich die neue Grafik und die Kopfzeile überlappen. Es gibt nun zwei Möglichkeiten zur Korrektur. Sie schließen das Fenster über den Button „Schließen“ und verrücken das Bild. In unserem Beispiel wäre das unmöglich, da das Bild unbedingt oberhalb der Tabelle stehen muss, nach oben hin aber auf den ersten Blick kaum Spielraum ist. Den schaffen Sie sich, indem Sie den vorgesehenen Platz für den oberen und unteren Rand verändern. Die meisten Dokumente haben unten oder oben einen Platz von rund zwei Zentimetern. Den können Sie aber beliebig verändern. Klicken Sie zunächst auf den Button „Ränder“. Kopf und Fußzeilen erscheinen nun von dünnen Linien eingerahmt. Falls der Text des Kopfes nicht ganz lesbar ist, könnte es sein, dass er vom Bild überlappt wird. Fahren Sie mit der Maus langsam über den Rahmen. Wenn sich das Lupensymbol des Cursors in ein Fadenkreuz verwandelt, können Sie den entsprechenden Bereich beliebig vergrößern oder verkleinern, indem Sie die Linie bei gedrückter Maustaste nach oben oder unten schieben. Im nächsten Schritt legen Sie auch gleich die Druckeinstellungen für die Vorlage fest. Klicken Sie zu diesem Zweck auf den Schalter „Layout“. Die Dialogbox „Seite einrichten“ springt auf. Achten Sie unter „Ausrichtung“ darauf, dass das richtige Papierformat eingestellt ist – in unserem Beispiel „Querformat“. Die Druckqualität lassen Sie auf „600 dpi“, falls Bilder eingebunden und gedruckt werden sollen, ansonsten reichen auch „300 dpi“. Die restlichen Register „Seitenränder“, „Kopfzeile/Fußzeile“, „Tabelle“ können Sie unberücksichtigt lassen. Anschließend klicken Sie auf „Seitenumbruchvorschau“. Excel zeigt Ihnen anhand blauer Linien, wo die Druckmarkierungen liegen. Die unterbrochene Linie lässt sich nicht verschieben, wohl aber die durchgezogene. Auf Wunsch können Sie somit den druckbaren Bereich auf der Seiet vergrößern oder verkleinern. Über den Befehl „Ansicht“, „Normal“ kehren Sie in den Tabellenmodus zurück. 7

Bedingte Formatierung und Kommentare

Das ist Ihnen bestimmt auch schon mal passiert: In der ellenlangen Formel ist kein Fehler zu finden, trotzdem zeigt die Zielzelle statt eines Ergebnisses „#WERT!“ an.

Den von

6

Excel vor-

gesehenen Be-

für Kopfoder Fußzeile korrigieren Sie, indem Sie die Begrenzungslinien mit Hilfe der Maus verschieben. reich

Auch die

6

Druck-

einstellungen

für Ihre Musterdatei legen Sie in diesem Dialog fest. Achten Sie unbedingt auf das korrekte Seitenformat.

Die blau

6

gefärb-

stehen für den druckbaren Bereich Ihres Sheets. Verschieben Sie mit der Maus die Linien, ändert sich der Bereich. ten Linien

Eine mögliche Fehlerquelle: Sie dividieren einen Wert durch „0“. Das passiert nicht mehr, wenn Sie die Zelle mit der „falschen“ Werten, also etwa der „0“, so formatieren, dass Sie bestimmte Werte nicht mehr annehmen soll. Excel kann das zwar nicht vollends unterbinden, vor einer unerwünschten Eingabe allerdings optisch

warnen. Die Funktion „Bedingte Formatierung“ kümmert sich in Excel darum. Sinnvoll ist der Einsatz der bedingten Formatierung vor allem, um BudgetGrenzen festzulegen, zum Beispiel in einem Haushaltsplan. In diesem Beispiel soll die bedingte Formatierung helfen, dass in den Zellen k

CHIP | WORKSHOP

8

für „Werktage“ nur Werte zwischen „1“ und „5“ eingegeben werden dürfen. Und so geht’s: Klicken Sie auf „Format“, „Bedingte Formatierung“. Unterhalb von „Bedingung 1“ legen Sie fest, ob es sich bei der betreffenden Zelle um eine Formel respektive um ein Formelergebnis oder einen reinen Zellwert handelt. Rechts daneben stellen Sie den Operator ein: größer, kleiner und so fort. Anschließend folgt/folgen die Zelle(n) mit dem oder den Zielwerten. In diesem Beispiel wählen Sie als Operator „zwischen“ und als erste Zielzelle „1“ und als zweite „5“. Mehr Bedingungen benötigen Sie nicht. Allerdings müssen Sie Excel nun noch erklären, wie es Zellen kenntlich machen soll, die diesen Bedingungen entsprechen. Klicken Sie zu diesem Zweck auf „Format“. Über die Register „Schrift“, „Rahmen“ und „Muster“ formatieren Sie die betreffenden Zellen, auf die Ihre Definitionen zutreffen – alle anderen bleiben unverändert! In dem von uns gewählten

8

Kompletter Blattschutz: Es ist unmöglich, auch nur eine einzige Eingabe im Sheet vorzunehmen, bevor Sie das Blatt nicht wieder freigegeben haben.

Beispiel werden sämtliche Zellen, deren Wert für Werktage tatsächlich zwischen 1 und 5 liegt, schwarz eingefärbt. Die Zellen, die weiterhin eine Null enthalten, bleiben dagegen weiß. Hin und wieder kann es sinnvoll sein, für eine Zielzelle mehrere Bedingungen zu formulieren. Beispielsweise erstellen Sie sich einen Terminkalender und wollen alle Termine, die außerhalb der Bürozeit liegen, anders färben als die in der regulären Arbeitszeit. Dann können Sie im Dialog „Bedingte Formatierung“ über den Button „Hinzufügen“ zwei Kriterien Ihrer Wahl definieren: die Bereiche zwischen 9

Über die

7

Funk-

tion „Bedingte Formatierung“

prüft Excel automatisch einzelne Werte, die innerhalb eines Toleranzbereiches liegen, und färbt diese ein.

und 12 sowie die „Zeit“ zwischen 13 und 18. Nur diese würden dann etwa schwarz eingefärbt, alles andere erscheint rot. Und Sie haben noch ein Chance, optisch auf bestimmte Zellen hinzuweisen – über Kommentare. Diese sind allerdings unabhängig vom Inhalt der Zelle. Die Kommentarbox erscheint, wenn Sie mit der Maus über eine entsprechend definierte Zelle fahren. Sie erkennen solche Zellen an dem kleinen Eselsohr in der rechten oberen Ecke der Zelle. Und so legen Sie Kommentare an: Klicken Sie auf eine beliebige Zelle. Wählen Sie aus der Menüzeile „Einfügen“, „Kommentar“. Sogleich erscheint eine kleine gelbe Box neben der Zelle mit einem blinkenden Cursor. Hier geben Sie einen Text ein. Sollten andere Leute an dem Sheet arbeiten, könnte es sich um einen Hinweis zum Ausfüllen der Zelle handeln. Oder Sie weisen so auf einen sehr wichtigen Wert in Ihrer Tabelle hin. Tipp: Auch der Text im Kasten lässt sich über die gewohnten Werkzeuge formatieren. Sie können die Farbe des Textes ändern oder einzelne Begriffe „fetten“. 8

Mit Kom-

7

menta-

ren heben Sie die Bedeutung bestimmter Zellen optisch hervor oder geben Hinweise zum Ausfüllen.

CHIP | WORKSHOP

Blatt und Zellen schützen

Im letzten Schritt war gezeigt, dass Excel vor falscher Zellbelegung warnen kann. Excel kann aber auch eine Eingabe in Zellen komplett verhindern. Das ist immer dann sinnvoll, wenn ganze Mappen nur zur Ansicht dienen oder Bereiche vor dem Überschreiben geschützt werden sollen. Im ersten Fall lautet der Befehl „Extras“, „Schutz“, „Blatt (Arbeitsmappe)“. Excel fragt im folgenden Dialog, welche Elemente „verschlossen“ werden sollen. Unter „Kennwort (optional)“ können Sie ein Passwort vergeben. In diesem Fall müssen Sie es in einer weiteren Abfrage bestätigen. Das komplette Sheet ist nun geschützt. Eine weitere Eingabe ist nur dann möglich, wenn Sie das Blatt über „Extras“, „Schutz“, „Blattschutz aufheben“ wieder freigeben. Jedoch funktionieren nun auch keine Formeln und Schalter mehr.

EXCEL-TUNING

9

» CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN » SERVICE

Musterdateien

Ein selektiver Schutz bestimmter Bereiche und Zellen ist hingegen nur über einen Trick möglich. In unserem Beispiel sollen sämtliche Zellen mit Formeln und Schaltern – also alle interaktiven Zellen – vom Schutz ausgeschlossen werden. Die Zellen ober- und unterhalb der Tabelle, die noch im druckbaren Bereich liegen, sollen hingegen vor dem (versehentlichen) Überschreiben geschützt werden. Markieren Sie dafür alle Zellen, die „offen“ bleiben, im Beispiel den kompletten Tabellenbereich mit den drei Summenzellen am unteren Ende. Wählen Sie nun „Format“, „Zellen“. Im Register „Schutz“ klicken Sie das Häkchen vor „Gesperrt“ weg. Aktivieren Sie den Blattschutz über „Extras“, „Schutz“, „Blatt“. Vergeben Sie noch ein Kennwort – fertig. Nur noch die Tabelle kann ohne Eingabe eines Passwortes bearbeitet werden. 9

Selekti-

8

Heben Sie die Sperrung bestimmter Zellen vor dem globalen Blattschutz auf. Schutz:

Excel

9

gibt Ih-

nen die Mög-

eigene Symbolleisten zu konstruieren und damit die Flut an Funktionen auf ein sinnvolles Maß zu reduzieren. lichkeit,

Symbolleiste individuell anpassen

Die Vielfalt an Funktionen und damit auch an Icons in der Symbolleiste ist nicht nur für Einsteiger verwirrend. Rund 80 Prozent der Funktionen, so schätzen Experten, bleiben in Word, Excel & Co. fast gänzlich ungenutzt. Aus diesem Grund hat Excel 2000 seine Menüs bereits abgespeckt. „Seltene“ Befehle finden Sie im Hauptmenü nur noch dann, wenn Sie das zugehörige Untermenü mit Hilfe des kleinen Doppelpfeils aufklappen. Aber Sie können der Menüleiste auch eine individuelle Diät verpassen. Jedoch gelten diese Änderungen dann nicht nur für Ihre Vorlagendatei, sondern für alle geöffneten Excel-Arbeitsblätter. Den „gewohnten“ Status stellen Sie später mit einem Mausklick wieder her. Und so geht’s: Wählen Sie „Ansicht“, „Symbolleisten“, „Anpassen“. Im Dialog „Anpassen“ klicken Sie auf die Schaltfläche „Neu“. Vergeben Sie dann einen aussagekräftigen Namen für die neue Leiste. Excel platziert eine kleine graue Box neben das Dialogfeld. Wechseln Sie im Dialog „Anpassen“ auf den Karteireiter „Befehle“. Mit gedrückter linker Maustaste wählen Sie die erforderlichen Funktionen nacheinander aus den jeweiligen Kategorien aus und lassen die Symbole auf die kleine graue Fläche rechts daneben fallen. Excel konstruiert daraus im Nu eine neue Symbolleiste. Haben Sie Ihre Auswahl beendet, klicken Sie auf „Schließen“.

ver

Ihre

10

Vorlage

Sie als Mustervorlage mit der Endung XLT im Ordner „Tabellenvorlagen“ speichern. müssen

Über „Ansicht, „Symbolleiste“ können Sie auf Wunsch künftig Ihre individuelle Leiste sowie die von Excel vorgegebenen nach Belieben ein- oder ausblenden. 10

Vorlagen korrekt ablegen

Nach all der Arbeit können Sie nur noch einen einzigen Fehler machen: die Datei

als normales XLS-File speichern. Vorlagen müssen immer die Endung „XLT“ tragen. Und damit auch nach dem Befehl „Datei“, „Neu“ Ihre Vorlage sofort gelistet wird, müssen Sie sie im richtigen Ordner ablegen. Der heißt „Tabellenvorlagen“ und befindet sich in Ihrem Office-Installationsverzeichnis. Ischta Lehmann

CHIP | WORKSHOP

10 DIE KATEGORIEN

» »

Video-Workshops 17 Workshops rund um die wichtigsten Excel-Funktionen in Wort und Bild

T ROTZ

» » » » »

CHIP-Shop Zur Auswahl: Die aktuellen Top-Produkte von CHIP für Haushalt, Freizeit und Büro

F ÜLLE

DES

ÜBERSICHTLICH :

Die Oberfläche der Heft-CD. Hier haben Sie Zugriff auf alle Daten und Dateien.

Übungsdateien Zum Vertiefen der VideoWorkshops: So können Sie Excel sofort anwenden. Makros 70 Makros, Vorlagen und Tools für naheliegende und exotische Excel-Anwendungen

DER

I N HALTS

Dateien zum Heft Kein Abtippen: Die WorkshopDateien aus dem Heft stehen für den Direkteinsatz bereit.

A NSEH N LICH : 17 VideoWorkshops – sympathisch moderiert – machen Sie mit Excel schnell vertraut.

Alles über die Heft-CD

Excel – mal in XXL Kalkulieren, rechnen, schätzen: Wer glaubt, dass Mathematik und Kalkulation langweilig sind, kennt Excel noch nicht richtig. Was Sie mit dem Programm alles machen können, erleben Sie auf der CD zum Heft.

W

ir haben uns für Sie in der Excel-Szene umgesehen und zahlreiche wertvolle, praxisfertige Tools auf die Heft-CD gepackt, die Ihnen in Büro und Haushalt die Arbeit erleichtern werden. Egal, ob Sie Ihre Business-Kalkulationen machen, Ihr Haushaltsbuch künftig digital führen oder Ihre Aktienkurse professionell verfolgen wollen: Excel kann das – und die Werkzeuge dazu liegen auf der Heft-CD!

So starten Sie die Heft-CD Eigentlich reicht es schon, die CD in Ihr CD-ROM-Laufwerk einzulegen. Der Rest geschieht in der Regel automatisch. Sollte allerdings die Autorun-Funktion Ihres Laufwerks ausgeschaltet sein, öffnen Sie den Windows-Explorer und von dort aus die Datei „START.EXE“ aus dem RootVerzeichnis der CD-ROM. Eine Installation ist nicht nötig.

CHIP | WORKSHOP

17 Video-Workshops zum Mitmachen Dass Excel auch was fürs Auge bietet, liegt nicht nur an der sympathischen Moderatorin, die Ihnen in 17 VideoWorkshops die Grundfunktionen von Excel nahebringt. Wenn Sie lernen und verstehen wollen, wie Excel funktioniert, finden Sie anschauliche Erläuterungen zu Themen wie „Arbeitsblätter“, „Tabellen“, „Formeln und Bezüge“, „Chart-Darstellung“, „3D-Bezüge“, „Matrix-Funktionen“ oder „Pivot-Assistenten“. In der CD-Rubrik „XL Makros“ (unter „M“) finden Sie 81 passende Musterdateien zu den Video-Workshops und den Artikeln im Heft, mit denen Sie das Gelernte praktisch umsetzen können.

Noch mehr Dateien zum Heft Auf der CD finden Sie noch weitere Übungsdateien zu den Workshops im

DIE HIGHLIGHTS DER CD

» Tools für Excel For Excel Formelsammlung mit mehr als 200 Formeln rund um Algebra, Geometrie, Physik und Technik

Herbers Excel-Lösungen Viele hundert Lösungen sowie Tipps & Tricks zu Excel – von einfachen Bedienhinweisen bis zu komplexen VBA-Codes: Mehr muss kein Mensch zu Excel wissen!

Lottozahlen-Generator Nie mehr selbst entscheiden: Excel übernimmt die Verantwortung, die richtigen Lottozahlen für Sie zu tippen.

Tastaturschablone Die Schablone bietet Ihnen den schnellen Überblick über die wichtigsten Tastenfunktionen von Excel.

Andis Excel-Spiele Kaum zu glauben: Excel macht Spaß!

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN »

SERVICE

Inhalt der Heft-CD

V A A T T (S ): 64 Vorlagen für alle Fälle, 81 Musterdateien und zahlreiche Tools finden Sie auf der Heft-CD. ON

Heft: „4GEWINNT.XLS“ zeigt Ihnen beispielsweise, dass Mathematik nicht unbedingt langweilig sein muss. Die Präzision des Programms dokumentiert die sekundengenauen Uhr in „CLOCKXL5.XLS“, und die weiteren Fähigkeiten von Excel, die erheblich über das reine Kalkulieren hinausgehen, lernen Sie beispielsweise mit der Datei „SCHALTER.XLS“ kennen. Näheres zu diesen

WI E

und den anderen Übungsdateien finden Sie in den einzelnen Artikeln des Hefts.

Excel-Makros und Beispieldateien Beim Öffnen der Dateien fragt Sie Excel, ob Sie die enthaltenen Makros aktivieren wollen. Das können Sie bedenkenlos tun, denn die Beispieldateien sind selbstverständlich auf Viren überprüft worden. Anhand von fast 60 Beispielen zeigt Ihnen

UTO B I S

WI E

AX

TEU ER

CHIP dabei, welche Anwendungsvielfalt in Excel steckt. Sie können unter anderem Aktienkurse berechnen, Altersbäume Ihrer Belegschaft „pflanzen“, Angebote verschiedener Firmen vergleichen oder auch Arbeitszeiten ausrechnen und verwalten. Im privaten Bereich haben Sie etwa die Möglichkeit, den Kraftstoffverbrauch Ihres Autos oder Darlehensraten zu berechnen, sich von Excel wecken zu lassen, Kassenoder Haushaltsbücher zu führen, sich Lottozahlen mit (hoffentlich!) hoher Ziehungswahrscheinlichkeit vorhersagen zu lassen oder Schecks und Überweisungen mit Hilfe von Excel auszufüllen. Doch das ist noch lange nicht alles, was Ihnen die Heft-CD bietet. Alleine „Herbers Excel-Lösungen“ bestehen aus einer Vielzahl nützlicher Makros, Tipps & Tricks sowie Lösungsmodellen für Probleme, von deren Existenz Sie bisher vielleicht noch nicht einmal etwas geahnt haben. Und schließlich zeigen Ihnen „Andis Excel-Spiele“ (Teil 1 und 2) anhand von immerhin sieben Beispielen, dass Arbeiten auch Spaß machen kann. Den wünschen wir Ihnen mit der Heft-CD!

Fehlersuche: Heft-CD defekt? Sollte die CD in Ihrem CD-ROM-Laufwerk weder automatisch noch manuell starten, probieren Sie die CD zunächst einmal in einem anderen Laufwerk aus. Sollte auch das nicht funktionieren, tauschen wir die CD gerne um. Vergessen Sie bitte nicht, uns bei der Reklamation gleich Ihre Postanschrift mitzuteilen.

CHIP | WORKSHOP

11

12

1

Excel bietet Ihnen eine große Anzahl unterschiedlicher Datumsformate für Ihre Zellen.

2

Wenn Sie im Dialogfeld als Zellenformat „TTTT“ eingeben, liefert Excel den Namen des Wochentags.

Datums- und Zeitwerte berechnen

Der Jahreskalender 2002 – in Excel-lenter Form Über die Datumsfunktionen von Excel wurde bislang nicht allzu viel geschrieben. Dieser Workshop zeigt Ihnen, wie Sie mit Excel einen komfortablen Kalender zusammenstellen und mit Zeitspannen und Datumswerten rechnen.

N

icht nur beim Rechnen mit Zeitwerten leisten die Kalender- und Datums-Funktionen in Excel gute Dienste. Ein Jahreskalender für 2002 ist mit Excel schnell angelegt.

1

Kalender gestalten

Formatieren Sie zunächst alle leeren Spalten mit „Format“, „Spalte“, „Breite“ und tragen Sie den Wert „8“ ein. Über „Zeile“, „Höhe“ wählen Sie den Wert „20“ für die Höhe der Zellen. Schreiben Sie nun in die Zelle A2 „Januar“ und markieren Sie sie. Mit Hilfe des Ausfüllkästchens ziehen Sie einen Bereich über die Zellen A3 bis A13 auf. Excel ergänzt die anderen Monatsnamen selbstständig. Für die Tage in der ersten Zeile gehen Sie ähnlich vor. Tragen Sie „1“ und „2“ in die Zellen B1 beziehungsweise C1 ein und lassen Sie Excel die Reihe bis zum Wert 31 vervollständigen. Für die Spalten- und Zeilentitel wählen Sie beliebige Hintergrundfarben oder über „Format, „AutoFormat“ den Typ „Standard 3“.

CHIP | WORKSHOP

Formatieren Sie nun den Zellbereich, der die gesammelten Datumswerte enthalten soll. Zunächst markieren Sie den Zellbereich zwischen A2 und AF13. Wählen Sie anschließend „Format“, „Zellen“. Auf der Registerkarte „Zahlen“ entscheiden Sie sich unter „Kategorie“ für „Datum“ und unter „Formate“ für „3.4“ –

den zweiten Eintrag von oben. In Excel 2000 lautet der Eintrag „14.4“. Klicken Sie dann auf die Registerkarte „Ausrichtung“. Unter „Horizontal“ stellen Sie „Rechts“ ein. Bei „Vertikal“ suchen Sie den Eintrag „Oben“. Auf der Registerkarte „Schrift“ wählen Sie den Grad „8“ und bestätigen die Anweisungen mit „OK“.

PROFI-TIPP

» Tagesgenaue Zinsen berechnen Wenn Sie wissen wollen, wieviel Zinsen ein angelegter Betrag bis heute gebracht hat, geben Sie die Summe, etwa 10.000 Mark, in eine Zelle ein, die Sie als „Währung“ formatieren. Eine weitere Zelle formatieren Sie als „Prozentzahl“ und tragen den Zinssatz ein, etwa 4,5 Prozent. Legen Sie eine Zelle für das Ausgangsdatum fest und achten Sie auf die korrekte Formatierung. Darunter brauchen Sie eine Zelle für das Enddatum. Schreiben Sie die Formel =Heute () hinein. Excel aktualisiert dann die Rechnung automatisch.

Der Zinswert berechnet sich auf zwei Ebenen. Der erste Rechenschritt ist das Produkt von Kapital und Zinsfuß geteilt durch 360, denn Excel geht von 360 Tagen pro Jahr aus. Das Ergebnis multiplizieren Sie mit der Anzahl der Zinstage. Dafür brauchen Sie die Funktion TAGE360, die exakt berechnet, wie viele Tage zwischen dem Ausgangsdatum und dem gegenwärtigen Tag liegen. Diese Formel in der Funktionszeile der Statusleiste errechnet die Zinseinnahmen: =B3*B4/360*TAGE360(B6;B7;WAHR)

EXCEL-TUNING

13

» CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN » SERVICE

Kalender-Funktionen

3

Excel wandelt mit der Formel =Monat ([Datum]) komplette Datumswerte in zweistellige Monatszahlen um.

Schreiben Sie nun in die Zelle A2 „1.1“, ändert Excel den Wert in ein komplettes Datum. Im Formelfeld der Statuszeile sehen Sie aber, dass Excel 97 fälschlicherweise vom Jahr 1900 ausgeht. Das korrigieren Sie, indem Sie die Jahreszahl überschreiben. Anschließend können Sie mit markierter Zelle A2 die komplette JanuarReihe aufziehen, so dass alle Daten korrekt eingetragen werden. Wenn Sie das Ganze für den Monat „Februar“ mit „1.2“ wiederholen, werden Sie feststellen, dass Excel nun erkennt, dass der Februar auch 28 Tage haben kann. Machen Sie in der beschriebenen Weise weiter, bis Sie beim 31. Dezember in der Zelle AF13 angelangt sind. 2

Wochentage ausgeben

Auch wenn Sie statt des Datums lieber den Wochentag sehen wollen, kann Excel Ihnen helfen. Falls Sie etwa wissen wollen, auf welche Wochentage Weihnachten 2002 und Neujahr 2003 fallen oder ob Ihr nächster Geburtstag auf einen Freitag oder Samstag fällt, markieren Sie dazu einfach die entsprechenden Zellen. Mit gedrückter [Strg]-Taste markieren Sie übrigens auch nicht zusammenliegende Zellen. Wählen Sie dann den Befehl „Format“, „Zellen“. Unter „Kategorie“ gehen Sie auf „Benutzerdefiniert“. Überschreiben Sie den Text in der Zeile unter „Formate“ mit „TTTT“. Bestätigen Sie mit „OK“, und Excel wandelt die Datumsangaben in Wochentage um. Als Berechnungsgrundlage für Excel bleibt der Datumswert in allen Zellen allerdings unverändert bestehen.

4

Mit der Funktion EDATUM berechnen Sie über die Eingabe der dazwischenliegenden Monate ein exaktes Datum.

Mit Tagen und Monaten rechnen 3

Wenn Kinder wissen wollen, wie viele Tage noch bis zum nächsten Geburtstag vergehen, geben Sie eine Miniformel in den ExcelKalender ein: Formatieren Sie zunächst eine freie Zelle als reguläres Zahl-Format, (nicht Datums-Format). Tragen Sie dann Ihrer Zinseinnahmen ermitteln Sie eine Subtraktion in mit der Excel-Funktion „Tage360“. folgender Form ein: = [Zelle1] - [Zelle1] Künftige Kalenderdaten in Excel liefert sofort die korrekte Anzahl 4 Excel 97 berechnen der Tage. Diese Rechnung funktioniert auch mit Datumswerten, die nicht im Ihr neues Auto haben Sie geleast und Kalender stehen. So können Sie beispiels- müssen ab heute noch 38 Monate lang weise problemlos berechnen, wie viele zahlen. Falls Sie wissen wollen wann die Tage noch bis zum nächsten Jahresurlaub letzte Rate fällig wird, greifen Sie auf die oder dem Beginn der nächsten Olympi- Formel EDATUM zurück. Sie berechnet schen Spiele vergehen. ein exaktes Kalenderdatum über die AnNicht nur tageweise, auch monats- gabe eines Ausgangsdatums und die Zahl weise kann Excel rechnen. Dafür wandelt der zu überbrückenden Monate. die Formel =Monat([Datum]) ein Kalen=EDATUM ([Zelle]; [Anzahl Monate]) derdatum in den entsprechenden MoFalls Sie die Zielzelle nicht als Datumsnatswert um, also etwa „8“ für August, wert formatieren, gibt Excel automatisch „9“ für September und so fort. Solche die Summe der Tage aus. Werte-Umwandlungen sind beispielsweiEbenso einfach finden Sie heraus, in se dann sinnvoll, wenn Sie in zahlreichen welche Kalenderwochen etwa die wichZellen Datumswerte haben, die Sie dann tigsten Messen des Jahres fallen. über eine Wenn-Abfrage halbjahresweiSchreiben Sie in die Formelzeile: se ordnen wollen. Eine mögliche Formel =KALENDERWOCHE sieht dann so aus: Tragen Sie in Klammern nur die Zelle =WENN(D18=0

ter“

Die

5

Maxi-

malwerte

zahlenmäßig nicht höher liegen als die Randbedingungen, um das Budget nicht zu sprengen. Dafür sorgt der logische Operator „0 =M5>0 =K5>0 Weisen Sie die Änderung über das Ausfüllrechteck, die rechte Maustaste und die Option „Formate ausfüllen“ jeweils bis an das untere Spaltenende zu. aus fremden Zellen Geben Sie nun 5 für die Ausgestaltung übernehmen. Hier meldet ein dunden gleichen Wert in klerer Ton eine Rundungsdifferenz in einer entfernten Spalte. den Spalten „Einnahmen DM“ und „Ausgaben DM“ ein, wird er einmal unter- mel ist“ und fügen in die Formel-Zeile legt und einmal nicht, da bei Einnahmen über [Ctrl]+[V] den Inhalt der Zwischendie Aufrundung zu einem Plus führt, ablage ein. bei Ausgaben aber die Abrundung des Setzen Sie nun die gesamte Formel, Wertes. beginnend hinter dem Gleichheitszeichen, in Klammern und fügen Sie am EnFormatierung aus de der Zeile „>0“ ein. Die Auswirkungen 6 Formeln ableiten auf die Formatierung sind die gleichen Während in Schritt 5 die Ergebnisse einer wie beim Bezug auf die Zelle, in der das Formel in einer fremden Zelle abgefragt Ergebnis der Formel ausgegeben wurde. Die Formel darf auch länger werden und bewertet werden, lässt sich dies durch Einsatz der kompletten Formel umgehen. als der angezeigte Fensterbereich. Bei BeUm das Vorgehen zu überprüfen, ge- darf wird der Ausschnitt durch das kleine hen Sie in der Musterdatei „Tabelle05.xls“ Fenster gescrollt. Ein Nachteil des direkin die Zelle L4. Kopieren Sie die in der ten Einsatzes von Formeln in der BedingStatusleiste hinterlegte Formel über ten Formatierung ist allerdings, dass sie [Ctrl]+[C] in die Zwischenablage. Ver- nicht von der Suchen/Ersetzen-Funktion lassen Sie die Statuszeile unbedingt mit gefunden werden und auch die automati[Esc] und wählen Sie das Feld B5 an. sche Fehlersuche sie nicht berücksichtigt. Dort stellen Sie die Bedingung auf „ForKarl Dreyer In den Bedingungen

Über Formeln lassen sich Werte

CHIP | WORKSHOP

68

Verweise organisieren

Bezüge über das Arbeitsblatt hinaus Gewinnen Sie mehr Überblick über Ihr Datenmaterial, indem Sie es auf mehrere Arbeitsblätter und Arbeitsmappen verteilen. Wie Sie Informationen aus anderen Tabellenblättern und externen Mappen abfragen.

E

xcel verwaltet mehrere Tabellenblätter in einer Mappe. Da bietet es sich an, Einzelanalysen auf unterschiedlichen Blättern unterzubringen und in der Auswertung auf die Felder, Formeln und Ergebnisse der anderen Tabellenblätter Bezug zu nehmen. 1

Strukturieren Sie die Arbeitsmappe

Bereits beim Start bietet Excel in einer neuen Arbeitsmappe drei Tabellen in Form von Registerzungen am unteren Bildschirmrand an. Sie tragen die Bezeichnungen „Tabelle 1“ bis „Tabelle 3“. Mit der rechten Maustaste öffnen Sie ein Menü, in dem sich die Option „Umbenennen“ befindet. Für die Beispiel-Anwendung benennen Sie die erste Tabelle in „Gesamtüberblick“ um, die beiden folgenden in „Region 1“ und „Region 2“. Mit „Einfügen“, „Tabelle“ fügen Sie drei neue Arbeitsblätter mit den Namen „Region 3“, „Region 4“ und „Hilfsdaten“ ein. Eine mit Basisdaten gefüllte Version dieser Datei finden Sie als Musterdatei „Tabellenverweis02.xls“ auf der Heft-CD. 2

Verweise über Feldnamen organisieren

Hier sind zunächst nur die DM-Ergebnisse der einzelnen Regionen aufgeführt, die auf Euro umzusetzen sind. Um sicherzustellen, dass überall der gleiche Faktor gilt, wechseln Sie auf das Tabellenblatt „Hilfsdaten“ und geben den Wert in die Zelle A1 ein. Im „Namen“-Feld ändern Sie dann die Vorgabe A1 in „Eurofaktor“. Kehren Sie auf das Tabellenblatt „Region 1“ zurück und geben Sie dort in die Zelle B10 die Formel „=B4*Eurofaktor“ ein. Die so definierte Formel können Sie mit dem Ausfüllkästchen auf die rest-

CHIP | WORKSHOP

Ein typischer Fall für eine Arbeitsmappe mit mehreren Tabellenblättern, deren Ergebnisse auf einem Hauptblatt zusammengefasst werden. Für mehr Übersichtlichkeit benennen Sie die Tabellenblätter um (Lupe) und fügen neue Tabellenblätter ein.

1

lichen Felder B10 bis E13 übertragen. Markieren Sie anschließend den Bereich B10 bis E13 und kopieren Sie ihn in die Zwischenablage. Sie können ihn nun auf den Datenblättern Region 2 bis Region 4 ab Zelle B10 einfügen. Da sich auch an dieser Stelle die Quelldaten immer im gleichen Bereich B4 bis E7 befinden, passen die Formeln, die ohne Verweise auf andere Blätter definiert wurden, problemlos in die Struktur. 3

Verweise über Formeln organisieren

Kehren Sie nun zum ersten Tabellenblatt „Gesamtüberblick“ zurück. Dort fehlen noch die Verweise auf die Einzeldaten. In Feld B4 sollen die Ergebnisse aller Schuhverkäufe in allen Regionen aus dem 1. Quartal addiert werden. Wählen Sie dazu zunächst das Feld aus und geben Sie „=Summe (“ ein. Wechseln Sie gleich darauf zum Tabellenblatt „Region 1“ und

wählen Sie die Zelle B4 aus. Da Sie immer noch im Formeleingabe-Modus sind, können Sie auch gleich ein „;“-Zeichen eintippen und zum Blatt „Region 2“ wechseln, um dort wiederum die Zelle B4 auszuwählen. Abgesetzt durch ein „;“Zeichen, verfahren Sie ebenso mit den entsprechenden Bereichen auf den Blättern „Region 3“ und „Region 4“. Beenden Sie die Formeldefinition mit der Schließen-Klammer „)“ und einem Druck auf [Enter]. Sie kehren so zur Tabelle „Gesamtüberblick“ zurück, wo das Ergebnis in das Feld B4 eingetragen wird. 4

Verweise mit Bereichen in Formeln

In der Musterdatei „Tabellenverweis02. xls“ befindet sich eine weitere Übersicht, in der das 1. und 2. Quartal in allen vier Regionen zusammengefasst werden soll. Das Vorgehen ist ähnlich wie in Schritt 3, nur zeigen Sie nicht auf eine Zelle, son-

EXCEL-TUNING » CHARTS » FUNKTIONEN »

FORMELN/BEZÜGE

69

» LÖSUNGEN » SERVICE

Verweise organisieren

dern ziehen jeweils den Zellbereich der beiden Quartale auf. Bei der Duplizierung der so gewonnenen Formeln sollten Sie allerdings besondere Aufmerksamkeit walten lassen. Ziehen Sie die Formel aus der Zelle H4 mit dem Ausfüllkästchen auf die Zelle H5, stimmen die Bereichsangaben nicht mehr. Da Sie bei der Formelauswahl immer zwei Zellen untereinander zusammengefasst haben, müsste der Verweis in der Formel von B4:B5 auf B6:B7 erhöht werden. Da Sie aber die Formel nur eine Zeile herunterziehen, werden die Zähler auf B5:B6 erhöht, wodurch die Filialergebnisse des 2. und 3. Quartals addiert werden. Helfen Sie sich mit folgendem Trick: Ziehen Sie die Formel zwei Zeilen tiefer, markieren Sie die Zellen der mittleren Zeile und löschen Sie sie über „Bearbeiten“, „Zellen löschen“, wobei die Zeile mit der richtigen Formel hochrutscht. Nach rechts lassen sich die Formeln hingegen ohne weiteres per Ausfüllrechteck aufziehen. Da die vier Artikelgruppen nicht zusammengefasst wurden, ist der Erhöhungszähler um 1 korrekt. 5

Diagramme mit entfernten Bezügen

Genau wie Zellen mit Bezügen auf andere Tabellenseiten ausgestattet sein können, gelingt dies auch bei Diagrammen. In der Musterdatei „Tabellenverweis01.xls“ findet sich auf dem Blatt „Gesamtüberblick“ sowohl ein Chart, das seine Daten aus den dort hinterlegten Tabellen bezieht, als auch ein Chart, das die Ergebnisse des Tabellenblatts „Region 1“ anzeigt. Um dies zu ermöglichen, rufen Sie den Diagramm-Assistenten ohne vorherige Auswahl des Datenbereichs von dem Tabellenblatt aus auf, auf dem das Diagramm stehen soll. Im zweiten Schritt des Assistenten wählen Sie „Datenbereich“ aus und aktivieren das kleine Icon rechts neben der Eingabezeile. Damit verschwindet der Assistent vorübergehend, und Sie können zum gewünschten Tabellenblatt wechseln, um dort den Datenbereich zu markieren. 6

Diagramme aus mehreren Blättern

Problematischer wird es, wenn Sie Werte aus mehreren Tabellenblättern in einem Chart verwenden wollen, wie es das rech-

4

Vorsicht mit dem Ausfüllkästchen! Weil hier zwei übereinander liegende Zellen in den Ursprungsdaten zusammengefasst wurden (B6:B7), stimmt die Formel nicht.

Daten lassen sich auch aus externen Dateien abfragen. Im Bild werden in den Zellen A1 bis A3 diverse Umrechnungsfaktoren aus einer zentralen Datei geladen. Änderungen lassen sich so zentral eingeben.

7

nicht geladen (Lupe), zeigt der Bezug auf ihre Position auf der Festplatte. Ist die Referenzdatei

te Chart in der Musterdatei „Tabellenverweis01.xls“ zeigt. Hier werden die Umsatzergebnisse aller Regionen quartalsmäßig gestapelt. Um diese Darstellung zu erreichen, legen Sie auf dem Tabellenblatt zunächst eine Hilfstabelle an. Damit die Ergebnisse dynamisch auf Änderungen in den regionalen Tabellenblättern reagieren können, dürfen die Daten nicht einfach zusammenkopiert werden. Vielmehr darf diese Tabelle nur Verweise auf die entsprechenden Bereiche der Ursprungstabelle besitzen. Am einfachsten gelingt dies, wenn Sie die Tabellen zunächst kopieren und dabei untereinander anordnen. Im Feld für das 1. Quartal „Schuhe“ legen Sie den Verweis an und ziehen die Formel durch das Ausfüllkästchen nach unten und in die Breite. Anschließend markieren Sie die einzelnen Blöcke. Beispiel „Schuhe Region 1“: Schneiden Sie diese mit [Ctrl]+[X] aus und platzieren Sie die Abschnitte in einer neuen Leiste so nebeneinander, wie sie im Diagramm erscheinen sollen. Benutzen Sie die Kombination [Ctrl]+ [X] und nicht [Ctrl]+[C], da beim Kopieren und Einfügen anders als beim

Ausschneiden und Einfügen die Formeln verändert werden. 7

Verweise auf externe Mappen

So wie sich Bezüge auf andere Arbeitsblätter in der Mappe definieren lassen, gelingt dies auch mit externen Mappen. Um die Referenz herzustellen, laden Sie diese zunächst in Excel. Wechseln Sie dann zu dem Blatt zurück, von wo aus der Bezug aufgebaut werden soll. Geben Sie ein „=“-Zeichen ein und wechseln Sie über „Fenster“ auf die Referenzdatei, wo Sie eines oder mehrere Felder markieren. Beim nächsten Öffnen der Datei erkennt Excel die Verknüpfungen und fragt, ob die Bezüge aktualisiert werden sollen. Bestätigen Sie dies, sucht Excel die Datei an ihrer letzten Fundstelle. Ist sie dort nicht vorhanden, erscheint ein Auswahlfenster, in dem Sie die neue Position angeben. Sofern Sie die Dateien wie die Musterdatei „Tabellenverweis03.xls“ und die Musterdatei „Zentrale Faktoren.xls“ weitergeben wollen, kopieren Sie sie einfach in den gleichen Ordner. Excel durchsucht ihn beim Start der Datei mit den Verweisen automatisch. Karl Dreyer

CHIP | WORKSHOP

70

Automatismen

3D-Bezüge herstellen Mit 3D-Bezügen können Sie den gleichen Zellbereich auf beliebig vielen Tabellenblättern berechnen. In diesem Workshop erfahren Sie, wie Sie 3D-Bezüge herstellen und durch einfaches Suchen & Ersetzen komplette Formelbezüge neu zuweisen.

N

ichts ist langweiliger, als die immer gleichen Formeln an andere Bezugsbereiche anpassen zu müssen. Mit Excels Automatismen vermeiden Sie viel Tipparbeit. Laden Sie am besten gleich die mit Verbrauchszahlen gefüllte Musterdatei „3D-Bezüge03.xls“. 1

Tabellenblätter optimieren

Die Monatsblätter im Beispiel müssen mit minimalen Abweichungen elfmal wiederholt werden. Wenn Sie diese richtig anlegen, lassen sich die Änderungen der Beschriftung besonders einfach handhaben. Wechseln Sie auf das Tabellenblatt „Januar“ und geben Sie in die Zelle A1 den Titel „Futterplan“ ein. In der Zelle B1 wird der Monat als Kürzel vermerkt: „Jan“. Wenn Sie nun in den Zellen B bis E Äpfel, Birnen, Bananen und Brote eingeben, verwenden Sie jeweils die Formel =„Äpfel_„&B1. Sie ergänzt den Text um die Monatsangabe in Feld B1. Markieren Sie den Bereich A1 bis E3 und kopieren ihn auf die anderen Monatsblätter, ändern Sie nur noch in B1 den Monat. In der Zelle A4 geben Sie jeweils den Ersten des Monats an und ziehen ihn über das Ausfüllkästchen bis zum Monatsende herunter. 2

Bereichsnamen automatisiert festlegen

Um auf der Auswertungsseite nicht immer wieder Bereiche aus den Monatsblättern markieren zu müssen, vergeben Sie Bereichsnamen, etwa einen für die Spalte „Äpfel_Jan“. Durch die automatisierte Namensvergabe aus dem ersten Schritt, die den Monat in den Spaltennamen integriert, unterscheidet sich dieser klar vom Bereich „Äpfel_Feb“. Aber auch die Anordnung der Zellen ist bewusst gewählt. Ziehen Sie einen Bereich von „Äpfel_Jan“ bis zur Zelle E34 auf. Wählen Sie dann „Einfügen“, „Na-

CHIP | WORKSHOP

Greifen die

1

auf 3D-Bereichsnamen zurück, lassen sich die Werte von mehreren hundert Tabellenblättern rasch summieren.

Alle Auswertungen dieses Blatts

1

basieren auf

Namens- und 3D-Bereichen.

men“, „Erstellen“. Im aufspringenden Menü aktivieren Sie nun „Namen erstellen aus oberster Zelle“. Excel nennt dann den Bereich B4 bis B34 „Äpfel_Jan“ und den Bereich C4 bis C34 „Birnen_Jan“. Dieser Befehl versieht also gleich vier Bereiche auf einem Tabellenblatt mit den in den Kopfzeilen per Formel generierten Namen. 3

Formeln

Wählen Sie in diesem Fall den Befehl „Einfügen“, „Namen“, „Festlegen“. Im Feld „Namen“ in der Arbeitsmappe geben Sie ein: „Äpfel_Alle“. Vor dem nächsten Schritt sollten Sie im Feld „Bezieht sich auf“ ein =-Zeichen eintragen. Wählen Sie nun das Tabellenblatt „Januar“ als erstes zu berücksichtigendes Tabellenblatt an.

3D-Verweise in die Tabelle einbauen

Während sich nach Schritt 2 die Ergebnisse der Produkte pro Monat einfach abfragen lassen, würde eine Formel, die auf zwölf Monatsbereiche zugreift, sehr lang. Um dies zu verhindern, setzen Sie zusätzlich 3D-Verweise ein. Voraussetzung: Die Zellen tragen auf allen Tabellenblättern Inhalte gleicher Bedeutung, wie dies bei den zwölf Monatsblättern der Fall ist.

1

die Monatsblätter unterscheiden sich nur in den Monatsnamen und den Zahlen.

EXCEL-TUNING » CHARTS » FUNKTIONEN »

FORMELN/BEZÜGE

71

» LÖSUNGEN » SERVICE

Automatismen

Klicken Sie dann mit gedrückter [Shift]Taste auf das Dezember-Blatt und markieren Sie den Bereich B4 bis B34. Im Bezugsfeld erscheint nun die Bereichsangabe =‘Januar:Dezember‘! $B$4: $B$34, die alle Äpfel-Bereiche auf den Monatsblättern einschließt. Da der Januar 31 Tage hat, sind auch die im Februar und allen anderen kürzeren Monaten nicht benutzten Felder enthalten. Verfahren Sie mit den Bereichen für „Birnen_Alle“, „Bananen_Alle“ und „Brote_Alle“ ebenso. 4

Bereichswerte auswerten

Wechseln Sie nun zum Tabellenblatt „Gesamt“ und füllen Sie dort die vorformatierten Auswertungsfelder mit den entsprechenden Formeln auf. Um in Feld B9 das Jahresergebnis an Äpfeln auszugeben, benutzen Sie die Formel: =Summe(Äpfel_Alle) Ähnlich verfahren Sie in den Zellen E9 bis E12, wo die Quartalsergebnisse erscheinen sollen. An dieser Stelle haben Sie nun zwei Möglichkeiten: Die erste besteht im Einsatz der Formeln =Summe(Äpfel_Jan;Äpfel_Feb;Äpfel_Mrz) beziehungsweise der angepassten Varianten für die drei weiteren Quartale. Die zweite Methode besteht darin, dass Sie über „Einfügen“, „Namen“, „Festlegen“ neue 3D-Verweise definieren sowie zusätzliche Bereiche für die Apfel-Spalten der Monate Januar bis März und entsprechend für die anderen Quartale. Der Name des Bezugs könnte „Äpfel_1_ Quart“ lauten. Die definierten Namensbereiche dürfen sich beliebig überschneiden. In diesem Fall lautet die Formel in Zelle E9 =Summe(Äpfel_1_Quart).

PROFI-TIPP

» 3D-Bereiche korrigieren und löschen Nachträgliche Änderungen an 3D-Bereichen nehmen Sie über „Einfügen“, „Namen“. „Festlegen“ vor. Im Auswahlfenster steht ein Löschbefehl bereit. Zum Ändern wählen Sie den Bereichsnamen an, geben im Feld „Bezieht sich auf“ ein =-Zeichen ein und markieren den gewünschten Bereich erneut.

Monatsergebnisse im Detail abfragen 5

Die Zellen A14 bis D16 sind für die Auflistung der einzelnen Monatsergebnisse vorgesehen. Damit die Zahlen mit dem entsprechenden Monatsnamen ausgegeben werden, benutzen Sie in Zelle A14 die Formel: =„Jan: „&Summe(Äpfel_Jan) Allerdings erkennt das Ausfüllkästchen die Monatsangaben in dieser Form nicht als ReihenIndikator. Beim Vervielfältigen der Formel auf die Felder bis D16 wird also nur die angegebene Formel kopiert. Die Änderungen auf die anderen Monatsnamen müssen Sie für jede Formel selbst vornehmen.

3D-Bereiche sind Verweise, die den gleichen Bereich auf mehreren Tabellenblättern umfassen. Es wird ein Bereich von Tabellenblättern und nur ein Zellbereich benannt, der auf allen Blättern markiert ist.

3

Ändern durch Suchen & Ersetzen 6

Dass die Arbeit dennoch den Aufwand wert war, zeigt dieser Schritt. Marin Formeln, lassen sie sich 6 durch Suchen & Ersetzen blitzschnell umformulieren. kieren Sie den Bereich A9 bis E16, kopieren Sie ihn und fügen Sie ihn ab Zelle A20 wieder Vorsicht bei Bewegung ein. Sie bekommen damit eine identische 7 im 3D-Bereich Kopie der Auswertung für die Äpfel. Markieren Sie diesen Bereich und ru- So hilfreich 3D-Bezüge sind, bergen sie fen Sie „Bearbeiten“, „Ersetzen“ auf. Un- doch auch Gefahren. Fügen Sie etwa eine ter „Suchen nach“ geben Sie „Äpfel“ ein, weitere Tabelle ein oder ändern Sie die unter „Ersetzen durch“ „Birnen“. Wählen Position einer vorhandenen, so dass sie Sie „Alle ersetzen“, und blitzschnell sind zwischen die definierten Tabellenblätter die Formeln der Äpfel-Auswertung auf rutscht, bezieht Excel die auf den neuen die Birnen-Auswertung umgebaut. Blättern befindlichen Informationen in Ebenso verfahren Sie, um die Bana- die Auswertung der Tabellenbereiche ein, nen- und Brote-Auswertungen anzupas- egal, ob sie passen oder nicht. sen. Dieser Trick gelingt allerdings nur Ein weiteres Problem: 3D-Formeln indurch den Einsatz einheitlicher Bereichs- nerhalb von 3D-Bereichen können nachbezeichnungen, bei einfachen Bezügen träglich rekursiv werden, verweisen also versagt er. auf sich selbst, wenn Sie neue, größere Auf die Änderungen reagieren alle ih- Bereiche bilden. Am sichersten fahren rerseits bezugnehmenden Bereiche. Die Sie beim Einsatz von 3D-Bereichen daTabellen zeigen nicht nur die neuen Wer- her, wenn Sie, wie in der Beispieldatei, te, sondern ändern auch ihre Achsen- die Auswertungs- und Datentabellen auf beschriftung, wie die ausgefüllte Muster- eigenen Blättern anlegen. datei „3D-Bezüge02.xls“ zeigt. Karl Dreyer Stehen Bereichsnamen

CHIP | WORKSHOP

72

Matrix-Formeln

Effizient arbeiten mit Matrix-Formeln Mit Matrix-Formeln können Sie mehrere Operationen gleichzeitig vornehmen. Dieser Workshop zeigt Ihnen, wie Sie eine Eingabekontrolle anlegen, bei der alle richtigen Eingaben in einer Matrix-Liste hinterlegt sind.

D

urch den Einsatz von Matrix-Formeln in Excel haben Sie eine einfache Möglichkeit, die Werte aus zwei Bereichen der Tabelle in Beziehung zu setzen.

1

So funktionieren MatrixFormeln

Das einfachste Beispiel für eine MatrixFormel ist die MMULT-Anweisung. Legen Sie, wie in der Musterdatei „Matrix02.xls“ im gelben Bereich gezeigt, eine Zeile mit mehreren Zahlen und, wie im grünen Bereich angedeutet, eine Spalte mit unterschiedlichen Werten an. Mit Hilfe der Matrix-Formel sollen nun alle Zahlen der Zeile mit allen Zahlen der Spalte multipliziert werden. Wählen Sie dazu den blau markierten Bereich aus und geben Sie die folgende Formel ein: =MMULT(A2:A6;B1:F1) A2:A6 legt den ersten Bereich, der auch als Matrix bezeichnet wird, fest, während B1:F1 die zweite Matrix angibt. Entscheidend ist, dass Sie die Eingabe der Formel nicht mit der [Enter]-Taste abschließen, sondern die Kombination [Ctrl]+[Shift]+[Enter] benutzen. Erst so

MATRIX-TIPP

» Operatoren suchen Das Gemeinsame von Matrix-Formeln besteht darin, dass die Formeln statt eines Wertes zwei oder mehr Werte-Bereiche, die Matrix, in Beziehung setzen. Wie dies geschieht, hängt vom gewählten Operator ab. Welche Operatoren Matrix-tauglich sind, können Sie sich über „?“, „Inhalt und Index“, „Suchen“ unter dem Stichwort „Matrix“ anzeigen lassen. Alle Fundstellen in Großbuchstaben stellen Matrixfähige Operatoren dar.

CHIP | WORKSHOP

2

Die Matrix-Funktion „Identisch“ vergleicht die Eingabe in diesem Feld mit der Liste aller gültigen Antworten. Stimmt der Wert überein, erscheint WAHR.

richtig funtioniert, muss eine Invertierung (eine WerteWandlung in der Wahrheitsumkehrung) zwischengeschaltet werden. Damit die UN D-Matrix-Formel

wird die Formel zur Matrix-Formel für den zuvor ausgewählten Bereich. Excel kennzeichnet dies, indem es die Formel in geschweifte Klammern setzt. Dass der Zielbereich genau unter und neben der Ausgangsmatrix liegt, dient nur der besseren Verständlichkeit. Sie können den Zielbereich auch an einer ganz anderen Stelle aufziehen; wichtig ist nur, dass er so viele Zellen hoch ist, wie Zellen in der ersten Matrix vorhanden sind, und so viele Zellen breit, wie Zellen in der zweiten Matrix ausgewählt wurden. Die Ziel-Matrix könnte somit auch auf einem anderen Tabellenblatt liegen. 2

Matrix-Formel als Eingabekontrolle

Eine praktische Einsatzmöglichkeit für Matrix-Formeln zeigt die Eingabekontrolle aus der Musterdatei „Matrix01.xls“. Bei ihr sollen, wie in der Abbildung gezeigt, die Bezeichnungen von Obstsorten

in ein Eingabefeld eingetragen werden. Die Eingabe wird nach ihrem Abschluss daraufhin überprüft, ob der eingegebene Begriff in der Liste der zulässigen Antworten enthalten ist. Ist das der Fall, gibt Excel den Logik-Wert WAHR aus. Um dies zu erreichen, legen Sie zunächst das Feld fest, in das die Eingabe erfolgen soll. Weisen Sie ihm anschließend über das Namensfeld links oben über der Tabelle den Namen „Eingabe“ zu. Im nächsten Schritt legen Sie in einer beliebigen Spalte eine Liste mit zulässigen Antworten wie etwa Birne, Apfel oder Banane an. Nun können Sie die Matrix-Formel mit Hilfe des Operators IDENTISCH anlegen. Markieren Sie zunächst einen Bereich, der genauso viele Zellen enthält wie die Liste mit den richtigen Antworten. In der Musterdatei befindet sie sich im Bereich D9 bis D14. Die Formel lautet: =IDENTISCH(Eingabe, C9:C14)

EXCEL-TUNING » CHARTS » FUNKTIONEN »

FORMELN/BEZÜGE

73

» LÖSUNGEN » SERVICE

Matrix-Formeln

Sie wird nach Abschluss der Eingabe mit der Kombination [Ctrl]+[Shift]+ [Enter] automatisch in geschweifte Klammern gesetzt und bewirkt, dass Excel alle Einträge der Liste mit dem Wert aus der Zelle „Eingabe“ vergleicht. Sind sie nicht identisch, wird der Wert FALSCH ausgegeben. Überprüfen Sie die Anzeige durch Eingabe eines der definierten Begriffe. 3

Matrix-Formel zur Ergebnisauswertung

Da es bei vielen Anwendungen nicht sinnvoll ist, dem Anwender die Liste mit den möglichen korrekten Antworten zu präsentieren, in der er nach einem Feld mit dem Eintrag WAHR Ausschau halten muss, ist für eine unmittelbare Erfolgsrückmeldung ein anderer Weg empfehlenswerter. Dazu müssen die Ergebnisse der Ziel-Matrix „Identisch“ in den Zellen D11 bis D16 ausgewertet werden. Nur wenn an dieser Stelle ein WAHR erscheint, ist die Eingabe korrekt. Für eine solche Überprüfung bietet Excel den Operator UND. Allerdings meldet er nur dann ein WAHR zurück, wenn alle Felder in der Matrix auf WAHR stehen. Bei einer korrekten Angabe steht aber nur eine Zelle im Bereich D11 bis D16 auf WAHR, so dass die UNDMatrix, wie in Zelle D18 gezeigt, immer auf FALSCH stehenbleibt. Das Problem lösen Sie durch eine Umkehrung der Logik, wie sie die Zellen E11 bis E16 zeigen. Hinter der Umkehrung steht eine einfache Formel mit einem WENN-Operator: =WENN(D11=WAHR;FALSCH; WAHR) Aus WAHR wird FALSCH, und wenn der Wert nicht WAHR lautete, wird er zu WAHR. Diese Formel ziehen Sie mit Hilfe

des Ausfüllkästchens bis zur Zelle E16. Somit ergibt sich ein spiegelbildliches Abbild vom Bereich D11 bis D16. Bei einer Falscheingabe bleiben alle Werte auf WAHR; nur wenn die Spalte D eine WAHR-Meldung ausgibt, wird sie zu einem einsamen FALSCH in der invertierten Spalte. Für die UND-Matrix ist dies dann trotz des irreführenden Textes der rechte Zeitpunkt zum Umspringen. Damit wird ein eindeutiges Signal gesetzt, das immer dann anspringt, wenn eine der möglichen Antworten erkannt wurde. Dieses Signal kann nun zur Rückmeldung an den Anwender dienen. 4

steht ein Wert der Identisch-Matrix auf FALSCH.

Zusammengefasste Auswertung

Das Signal kann etwa für eine Farb-Codierung des Eingabefeldes benutzt werden. Ist die Eingabe falsch, steht also die Zelle E18 aufgrund der Einschränkungen der UND-Funktion auf WAHR, soll das Feld rot hinterlegt werden. Bei positivem Ergebnis, dem Wert FALSCH, hingegen soll es grün unterlegt sein. Damit ist aber noch nicht der Fall berücksichtigt, dass noch keine Eingabe erfolgt ist. Dieses Manko fangen Sie über eine Eigenheit der Zuweisung von Bedingten Formaten auf. Wählen Sie die Zelle D6 an und öffnen Sie über „Format“, „Bedingte Formatierung“ das abgebildete Einstellungsmenü. In der ersten Zeile fragen Sie nicht das Ergebnis der Matrix-Auswertung ab, sondern den aktuellen Zellen-Wert. Wenn der Null ist, was einer leeren Zelle entspricht, wählen Sie Gelb als Hintergrund. Erst in den folgenden Bedingungen wird die Matrix-Analyse abgerufen, wobei die Zelle E18 den Zellen-Namen „Auswertung“ erhält, um eine verständlichere Formel-Syntax zu ermöglichen. 5

Wenn der aktuelle Zellenwert Null ist, also noch keine Eingabe vorgenommen wurde, erscheint Gelb als Hintergrundfarbe der Zelle.

4

Bei einer falschen Antwort

2

Einschränkungen bei Matrix-Formeln

Während sich normale Formeln jederzeit ändern lassen, erscheint bei Anwahl einer Matrix-Formel nur eine Warnmeldung. Eine Änderung ist nicht möglich. Um dennoch Korrekturen vorzunehmen, kopieren Sie den Formel-Text in eine andere Zelle außerhalb des Matrix-Bereichs, löschen den Matrix-Bereich, fügen die Formel neu ein und ändern

Die invertierte Matrix steht komplett auf WAHR, weshalb die invertierte Und-Matrix auch auf WAHR steht.

3

Fehlt eine Eingabe, hält die Matrix-Abfrage dies auch für eine Fehleingabe.

4

sie. Erst nach dem Abschluss der Korrektur mit [Ctrl]+[Shift]+[Enter] wird der Schutz erneuert. Einschränkungen gelten aber auch bei den definierten Matrix-Bereichen. Die Liste mit zulässigen Antworten lässt sich erst nach dem Löschen der Matrix-Formel erweitern, um einer neuen Matrix-Formel zugeordnet zu werden. Karl Dreyer

CHIP | WORKSHOP

74 INHALT

74

Tipps & Tricks für Excel 21 Top-Tipps und -Lösungen

84

Mit Makros arbeiten Aufzeichnen und einbinden

86

Haushaltsbuch anlegen Ausgabendisziplin mit Excel

92

Die Festplatte analysieren Makro druckt Ordnerstruktur

94

Eine Uhr im Arbeitsblatt Zeitansage per Excel-Makro

96

Pausenspiele programmieren „Vier gewinnt“ im Excel-Sheet

&

Tipps Tricks

Mit dem richtigen Know-how klappt’s auch mit Excel 97 und 2000. Die folgenden 21 Top-Tipps helfen Ihnen, mit Diagrammen, Funktionen, Formeln & Co. besser zurecht zu kommen.

TIPPS & TRICKS 1 Excel 97, 2000 74 Daten als Datenbank zusammenfassen 2 Excel 97: 75 Sprache der Rechtschreibung ändern

|

Daten aus Arbeitsblättern als Datenbank zusammenfassen

Sub Datensammlung() Dim i, y, y As Variant x = Sheets.Count Sheets.Add After:= t Work-sheets(Sheets.Count) Sheets(x + 1).Select Sheets(x + 1).Name = t

Ich habe 150 Überweisungen auf verschiedenen Arbeitsblättern in einer Excel-Datei gespeichert. Jetzt will ich diese Daten in eine Datenbank übertragen und daraus eine Übersicht anlegen. Die Export-Funktionen für DatenbankFormate unterstützen jedoch nur einzelne Tabellen. Wie bekomme ich eine Übersicht meiner Überweisungen?

„Datenbank“

Sie müssen hierzu die Daten zunächst in Excel in einer neuen Tabelle zusammenfassen. Damit haben Sie bereits eine Übersicht. Diese Tabelle können Sie beispielsweise als dBase-Datei exportieren oder auch als Textdatei, die Sie danach in eine andere Datenbank importieren. Unter der Voraussetzung gleich aufgebauter Arbeitsblätter kann Ihnen ein Makro automatisch die gewünschte Übersicht in einer neuen Tabelle erzeugen. Rufen Sie dazu den Befehl „Extras“, „Makro“, „Makros“ auf. Legen Sie als Makro-Namen zum Beispiel „Datensammlung“ fest und klicken Sie auf „Erstellen“. Im VBA-Editor geben Sie folgende Makro-Zeilen ein:

Next i

Frage:

For i = 1 To x Sheets(x + 1).Cells(i, t 1) = i y = Sheets(i).Cells(1, 1) Sheets(x + 1).Cells(i, t 2) = y y = Sheets(i).Cells(3, 4) Sheets(x + 1).Cells(i, t 3) = y

Antwort:

CHIP | WORKSHOP

End Sub

Zunächst fügt das Makro eine neue Tabelle am Ende der Arbeitsmappe ein und nennt sie „Datenbank“. Nun durchläuft es in einer Schleife alle Arbeitsblätter. Zuerst erhält der Datensatz in der neuen Tabelle eine laufende Nummer. Dann liest das Makro die benötigten Daten aus den betreffenden Zellen der einzelnen Tabellen jeweils in die Variable „y“ ein. Der nächste Befehl schreibt sie in die entsprechende Zeile der Übersichtstabelle. Die Befehle innerhalb der Schleife müssen Sie für Ihren Zweck anpassen und in gleicher Form für alle benötigten Datenfelder der Überweisungen erweitern.

3 Excel 97, 2000: 75 Fortlaufende Datensätze verwalten 4 Excel 97, 2000: Überschriften wiederholen

76

5 Excel 97, 2000: 76 Daten in Diagrammen formatieren 6 Excel 97, 2000: Kopf- und Fußzeilen entfernen

76

7 Excel 97, 2000: 77 Falsche Kopfbezeichnungen korrigieren 8 Excel 97: Bedingte Formatierung

77

9 Excel 97, 2000: Zellinhalte kolorieren

77

10 Excel 97, 2000: 78 Farbpalette einer Arbeitsmappe ändern 11 Excel 97, 2000: Zellen automatisch hervorheben

78

12 Excel 97: 79 Zugriff auf PAGEFILE.SYS verhindern 13 Excel 97, 2000: Berechnung von „null hoch null“

79

14 Excel 97, 2000: 79 Zelle nach Maximumwert ermitteln 15 Excel 97, 2000: 79 Felder abhängig von Variablen addieren 16 Excel 97, 2000: 80 Zelleninhalte von Tabellen vergleichen 17 Excel 97, 2000: Identische Datensätze aufspüren

81

18 Excel 97: 81 Tabellen mit VBA als Text exportieren 19 Excel 97, 2000: 82 Diagramme als HTML-Datei exportieren 20 Excel 97, 2000: 82 Zellinhalte als Kommentar drucken 21 Excel 97, 2000: 82 Zufallszahlen ohne Duplikate erzeugen

Foto: E. Heuer

1

Excel 97, 2000

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

75

» SERVICE

Tipps & Tricks

2

|

Excel 97

Sprache der Rechtschreibung umstellen Frage: Unter Excel 97 ist beim Durchführen der Rechtschreibprüfung offensichtlich die Sprache „Deutsch“ voreingestellt. Wie kann ich die Sprache hier denn umstellen?

Sprachschwierigkeit: Die globale Offenheit ist bei Excel auf die Kommunikation durch Zahlen begrenzt.

2

Schlüssel „Spelling“ und wählen den Befehl „Bearbeiten“, „Neu“, „Schlüssel“. Geben Sie die Bezeichnung „1033“ ein und bestätigen Sie mit „OK“. Unter diesem Schlüssel erzeugen Sie auf gleiche Weise einen weiteren Schlüssel „Normal“. Für den Schlüssel geben Sie mit dem Befehl „Bearbeiten“, „Neu“, „Zeichenfolge“ die Bezeichnung „Dictionary“ ein. Mit dem Befehl „Bearbeiten“, „Ändern“ weisen Sie nun dieser Zeichenfolge den Wert „C:\Programme\Gemeinsame Dateien\Microsoft\Shared\Proof\ mssp2_en.lex“ zu. Analog erzeugen Sie die Zeichenfolge „Engine“ und weisen dieser Zeichenfolge den Wert „C:\Programme\Gemeinsame Dateien\Microsoft\ Shared\Proof\mssp2 32.dll“ zu. Nun navigieren Sie zu „HKEY _LOCAL _MACHINE\SOFTWARE\MICROSOFT\ OFFICE\8.0\NEWUSER\SETTINGS\EX CEL\SPELLCHECKER“. Auch hier ändern Sie bei „Speller“ den Wert von „1031\ Normal“ auf „1033\Normal“. Damit ist die Sprache auf amerikanisches Englisch umgestellt. Für andere Sprachen gelten übrigens folgende Zahlen-Codes: 3Spanisch: „1034“

Antwort: Excel 97 bietet Ihnen keine direkte Möglichkeit, über das Menü die Sprache für die Rechtschreibprüfung einzustellen. Bei der normalen Installation ist Deutsch als Standard eingerichtet. Für die Umstellung auf eine Fremdsprache ist ein ziemlich mühsames, manuelles Editieren der Registry erforderlich. Zuvor sollten Sie Sicherungskopien der versteckten Dateien USER.DAT und SYSTEM.DAT aus dem Windows-Ordner anlegen. Schließen Sie zunächst Excel. Starten Sie dann den Registrierungs-Editor. Gehen Sie zu dem Schlüssel „HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\8.0\EXCEL\SPELLCHECKER“. Klicken Sie im rechten Fensterteil auf den Eintrag „Speller“ und führen Sie den Befehl „Bearbeiten“, „Ändern“ aus. Für amerikanisches Englisch ist der Wert „1031\Normal“ jetzt auf „1033\ Normal“ zu ändern. Im nächsten Schritt navigieren Sie zu „HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\SHAREDTOOLS\ PROOFING TOOLS\SPELLING“ und prüfen, ob dort bereits der Schlüssel „1033“ existiert. Falls er fehlt, markieren Sie den

3Französisch: „1036“ 3Italienisch: „1040“ 3Holländisch: „1043“ 3Schwedisch: „1054“ 3Britisches Englisch: „2057“ 3Portugiesisch: „2070“

3

3

|

Excel 97, 2000

Fortlaufende Datensätze verwalten Eine Tabelle soll fünf feste Zeilen zur Dateneingabe, 17 variable Zeilen für fortlaufende Datensätze und weitere zwölf feste Zeilen zur Dateneingabe anzeigen. Dabei sind aber bis zu 70 fortlaufende Datensätze einzugeben, und Excel kann ein Fenster nicht zweimal teilen. Die Datensätze sollen sich zum Drucken komplett sortieren lassen. Tipp: Am besten lassen sich die Tabellen für den Druck oder die Anzeige aufbereiten, wenn Sie eine Eingabetabelle und speziell formatierte Druck- beziehungsweise Anzeigetabellen aufbauen. Legen Sie dazu zunächst mit „Einfügen“, „Tabelle“ eine zusätzliche Tabelle für die Dateneingabe an. Klicken Sie anschließend zweimal auf die Registerzunge am unteren Tabellenrand und geben Sie ihr den Namen „Eingabe“. Dann definieren Sie am Anfang des Tabellenblattes die beiden festen Bereiche für die Dateneingabe. Die Position der Bereiche in der Tabelle sowie die Formatierung der Zellen spielen dabei keine Rolle. Als Orientierungshilfe können Sie daneben noch angeben, welche Daten dort künftig eingetragen werden sollen. Weiter

Praktisch umsortiert: Eine Aufteilung der Tabellen für Eingabe und Druck erhält die Sortierfähigkeit sämtlicher Daten und liefert fertig layoutete Druckergebnisse.

CHIP | WORKSHOP

76

unten in der Tabelle bereiten Sie einen Bereich für die fortlaufende Eingabe der Datensätze vor. Auf diese Weise haben Sie alle Datensätze in einer Tabelle und können später problemlos den Datenbereich markieren und den Befehl „Daten“, „Sortieren“ aufrufen. In einer zweiten Tabelle beginnen Sie nun mit der Gestaltung und Formatierung der Zellen für Anzeige und Druck. Als Zellinhalte verwenden Sie Formeln, die Ihnen die gewünschten Werte aus der Eingabetabelle liefern. Beginnen Sie in der Zelle „A1“ mit einer Formel in folgender Form: =WENN(Eingabe!B3=“„;“„t ;Eingabe!B3)

Im gezeigten Beispiel beginnt der Bereich mit den einmalig einzugebenden Feldern in Zelle „B3“ in der Tabelle „Eingabe“. Mit der Wenn-Funktion vermeiden Sie dabei die Anzeige von Nullwerten, falls die Zelle in der Eingabetabelle leer sein sollte. Verwenden Sie nun die Befehle „Bearbeiten“, „Ausfüllen“, „Unten“ und „Bearbeiten“, „Ausfüllen“, „Rechts“, um den gesamten ersten Bereich mit entsprechenden Formeln auszufüllen. Wiederholen Sie den gesamten Ablauf anschließend für den unten stehenden zweiten Bereich mit den einmalig einzugebenden Werten. Nehmen Sie dann noch allgemeine Formatierungen wie etwa Zahlenformate für den Bereich der fortlaufenden Datensätze vor. Klicken Sie auf das Feld an der linken oberen Ecke des Tabellenblattes, um die gesamte Tabelle zu markieren. Führen Sie den Befehl „Bearbeiten“, „Kopieren“ aus, wechseln Sie zu einer neuen, leeren Tabelle und drücken Sie die Eingabetaste. Wiederholen Sie den Vorgang, bis Sie ausreichend vorbereitete Tabellenblätter für Ihre Datensätze haben. Bei 70 Datensätzen zu jeweils 17 Zeilen brauchen Sie also fünf solche Tabellen. Nun müssen Sie noch nacheinander auf allen Drucktabellen in den Bereichen der Datensätze entsprechende WennFunktionen eingeben, die sich auf die fortlaufend eingegebenen Daten in der Eingabetabelle beziehen. Nun ist Ihre neue Excel-Arbeitsmappe fertig. In der Eingabetabelle können Sie künftig fortlaufend Datensätze ein-

CHIP | WORKSHOP

geben, diesen Bereich markieren und anschließend sortieren. Die anderen Tabellen zeigen dabei stets die druckbereite Sicht auf diese Datenbestände.

4

|

Excel 97, 2000

Überschriften auf Druckseiten wiederholen Sie wollen eine mehrseitige Excel-Tabelle drucken. Die Spaltenüberschriften sollen sich auf jeder Seite wiederholen und damit die Zuordnung der Daten erleichtern. Sie wollen die Angaben aber nicht mehrmals in die Tabelle schreiben, sondern Excel soll diese gleich automatisch einfügen.

5

|

Excel

Datenreihen in Diagrammen unterschiedlich formatieren Frage: Ich will in einem einzigen ExcelDiagramm parallel sowohl eine Datenreihe als Säule als auch eine andere Datenreihe als Linie darstellen. Wie funktioniert das?

Antwort: Legen Sie zunächst einmal das Säulendiagramm an. Im nächsten Schritt markieren Sie die Datenreihe, die Sie als Linie darstellen wollen. Führen Sie anschließend den Befehl „Diagramm“, „Diagrammtyp“ aus.

Tipp: Öffnen Sie zunächst das betreffende Tabellenblatt in Excel. Dort führen Sie nun den Befehl „Datei“, „Seite einrichten“ aus und aktivieren anschließend die Registerkarte „Tabelle“. Klicken Sie nun im Bereich „Drucktitel“ in das Eingabefeld „Wiederholungszeilen“. Markieren Sie im nächsten Schritt in Ihrer Tabelle die Zeile mit den Spaltenüberschriften. Klicken Sie dann auf „OK“. In der Seitenansicht und im Ausdruck der Tabelle fügt Excel nun auf jeder Seite die ausgewählten Spaltenüberschriften als oberste Zeile ein. Auf diese Weise können Sie auch in Tabellen, die breiter sind als eine Seite, mit der Funktion „Wiederholungsspalte“ Zeilenbezeichnungen automatisch wiederholen. Die Zeilenbezeichnungen erscheinen dann jeweils an den Zeilenanfängen einer neuen Seite.

5

Überlagerung: Um Diagramme in Excel zu kombinieren, ändern Sie einfach den Typ für die Datenreihen.

Markieren Sie nun den Typ „Linie“ und wählen Sie den gewünschten Untertyp aus. Im Bereich „Optionen“ müssen Sie nun noch das Kontrollkästchen „Auf Auswahl anwenden“ aktivieren und auf „OK“ klicken – fertig. Allerdings werden Sie auch feststellen, dass Sie nicht alle Darstellungstypen in Excel miteinander kombinieren können. www.chip.de/forum/office

6

|

Excel 97, 2000

Definierte Kopf- und Fußzeilen entfernen Frage: Wie kann ich in einem Excel-

4

Wiederkehrend: Titelzeilen und -spalten lassen sich auf allen Seiten einheitlich automatisch wiederholen.

Dokument enthaltene benutzerdefinierte Kopf- oder auch Fußzeilen einfach herauslöschen?

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

77

» SERVICE

Tipps & Tricks

Antwort: Zum Entfernen von benutzerdefinierten Kopf- und Fußzeilen gehen Sie auf die gleiche Art und Weise vor wie beim Einrichten. Öffnen Sie zunächst das Menü „Ansicht“ und rufen Sie den Befehl „Kopf- und Fußzeile“ auf. Aktivieren Sie im Fenster „Seite einrichten“ die Registerkarte „Kopfzeile/ Fußzeile“. Sie finden dort die beiden Dropdown-Menüs „Kopfzeile“ und „Fußzeile“. Klicken Sie dort jeweils auf den Auswahlpfeil an der rechten Seite. Blättern Sie in der Auswahl ganz nach oben und wählen Sie die Einstellung „(keine)“. Um die neue Einstellung zu übernehmen, beenden Sie den Dialog mit „OK“. Anschließend sind die Kopf- und Fußzeilen verschwunden.

8

|

9

Excel 97

Zelle abhängig von einer anderen Zelle einfärben

Zellinhalte durch Farben anschaulich machen

Sie wollen in Excel ein Textfeld („B1“) abhängig vom Wert eines anderen Feldes („A1“) mit einem farbigen Hintergrund versehen.

Sie wollen den Zellhintergrund abhängig vom Wert in der Zelle farbig gestalten. Mit der bedingten Formatierung von Excel erreichen Sie aber auch mit verknüpften Bedingungen maximal vier Farben. Sie möchten jedoch dafür eine komplette Farbskala mit mehreren Abstufungen verwenden.

Tipp: Diese Aufgabe können Sie mit der

sehentlich die Darstellung der Zellbezüge verändert. Führen Sie den Befehl „Extras“, „Optionen“ aus. Überprüfen Sie anschließend in der Registerkarte „Allgemein“ unter „Einstellungen“, ob im Kontrollkästchen „Z1S1 Bezugsart“ ein Haken gesetzt ist. Wenn ja, löschen Sie ihn – damit ist das Problem gelöst.

so genannten bedingten Formatierung lösen. Die Bedingung kann sich dabei auf den Wert einer anderen Zelle beziehen. Markieren Sie zunächst die Zelle „B1“ und führen Sie den Befehl „Format“, „Bedingte Formatierung“ aus. Im Kombinationsfeld für die Bedingung „1“ wählen Sie den Eintrag „Formel ist“ und geben rechts im Eingabefeld die logische Formel „=(A1 >0)“ ein. Klicken Sie anschließend auf den Button „Format“ und wählen Sie in der Registerkarte „Muster“ die grüne Farbe für den Hintergrund. Beenden Sie dann den Dialog mit „OK“. Nun klicken Sie auf den Button „Hinzufügen“ und definieren eine zweite Bedingung für die Zellendarstellung. Als Bedingung „2“ verwenden Sie wieder den Eintrag „Formel ist“ und geben im Feld rechts daneben die logische Formel „=(A1 35 Then k = 35 With Cells(i, 2).Interior .ColorIndex = CI(Intt (k / 4) + 6) .Pattern = xlSolid End With Next i End Sub

Zunächst definiert das Makro nun die nötigen Variablen. Als nächstes müssen Sie die richtige Zuordnung Ihrer Farbskala zu den internen Nummern des Color-Index finden. Dazu verwenden Sie einfach das Datenfeld „CI“. Das enthält fortlaufend den internen Color-Index, den Excel für die modifizierten Standardfarben verwendet. Danach richten Sie eine oder mehrere Schleifen ein, um Ihren Datenbereich zu durchlaufen. Für jedes Datenfeld lesen Sie zunächst den Wert in die Variable „k“ ein. Die beiden folgenden Zeilen begrenzen die möglichen Werte auf die Randwerte Ihrer Farbskala. Anschließend müssen Sie nur noch den Hintergrund der jeweiligen Zelle entsprechend ändern. Dafür verwenden Sie einfach eine spezielle Excel-Formel, die nun automatisch sämtliche ExcelDaten in fortlaufende Nummern für Ihre individuell angelegte Farbskala umrechnet.

CHIP | WORKSHOP

Die Farbreihenfolge bei neu angelegten Diagrammen gefällt Ihnen nicht. Sie wollen sowohl die Reihenfolge als auch die Farben entsprechend Ihren Wünschen ändern.

Tipp: Excel kann innerhalb einer Arbeitsmappe 56 verschiedene Farben verwalten und einsetzen. Wenn Sie nicht mit den Standardfarben arbeiten, speichert Excel Ihre selbst definierte Farbpalette als Bestandteil der Arbeitsmappe. Zum Anpassen der Farben rufen Sie zunächst den Befehl „Extras“, „Optionen“ auf und aktivieren die Registerkarte „Farbe“. Die Palette gliedert sich in 40 Standardfarben sowie jeweils acht Farben, die Excel primär für Diagramm-Objekte und Diagramm-Linien verwendet. Markieren Sie das zu ändernde Farbfeld und klicken Sie danach auf die Funktion „Bearbeiten“. Im folgenden Dialog können Sie nun die Farbauswahl detailliert festlegen. Dazu stehen Ihnen die Registerkarten „Standard“ und „Anpassen“ zur Verfügung. In „Standard“ können Sie aus einer größeren Anzahl vordefinierter Farben wählen, in „Anpassen“ suchen Sie die Farben nach den Farbanteilen aus. Wiederholen Sie den Vorgang anschließend für alle zu ändernden Farben. Falls Ihnen das Ergebnis nicht gefällt, können Sie mit einem Klick auf den Button „Standard“ alle Änderungen auch wieder rückgängig machen und einfach

die Standardpalette von Excel aktivieren. Schließlich haben Sie noch die Möglichkeit, eine bereits definierte Farbpalette aus einer anderen Arbeitsmappe zu übernehmen. Dazu öffnen Sie beide Arbeitsmappen. In der neuen Arbeitsmappe führen Sie den Befehl „Extras“, „Optionen“ aus und wechseln in die Registerkarte „Farbe“. Nun können Sie im Kombinationsfeld „Farben kopieren aus“ die erste Arbeitsmappe auswählen und mit einem Klick auf „OK“ die Farbpalette von dieser übernehmen.

11

|

Excel 97, 2000

Zellen automatisch hervorheben Sie nehmen mit einer Excel-Tabelle einen Preisvergleich verschiedener Produktanbieter vor. Zur schnellen Übersicht wollen Sie den jeweils günstigsten Anbieter farblich hervorheben.

11

Günstiger Vergleich: Die bedingte Formatierung zeigt schnell alle preiswerten Anbieter auf den ersten Blick.

Tipp: Die Lösung bietet die bedingte For-

10

Farbenfroh: Je nach Bedarf kann Excel in jeder Arbeitsmappe 56 frei definierbare Farben unterstützen.

matierung in Excel. Die Produkte sind in Zeilen, die Anbieter in Spalten angeordnet. Die letzte Spalte zeigt die Minimumpreise der Produkte. Beispiel: In den Zellen „B3“ bis „F3“ stehen die Produktpreise. Mit der Formel „=MIN(B3:F3)“ haben Sie in der Zelle „G3“ das beste Angebot ermittelt. Markieren Sie als erste Zelle „B3“ und wählen Sie den Befehl „Format“, „Bedingte Formatierung“. Im Bereich „Bedingung 1“ wählen Sie im ersten Feld „Zellwert ist“ und im nächsten Feld den Operator „gleich“. Klicken Sie in das rechts daneben

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

79

» SERVICE

Tipps & Tricks

liegende Eingabefeld und markieren Sie in der Tabelle die Zelle „G3“ oder geben Sie manuell die Formel „=$G$3“ ein. Nach dem Klick auf „Format“ können Sie die Formatierung der hervorgehobenen Zelle festlegen. Beenden Sie den Dialog durch Klicken auf „OK“. Wiederholen Sie den Vorgang nacheinander für alle Zellen, in denen Preisangebote enthalten sind. Alternativ: Mit dem Befehl „Bearbeiten“,

13

|

Excel 97, 2000

Berechnung von „null hoch null“ definieren Bei Berechnungen von Wahrscheinlichkeiten unter Excel treten gelegentlich Werte wie „0^0“ auf. Excel liefert den Fehlerwert „#ZAHL!“, das Ergebnis ist aber „1“. Wie kann ich dieses Problem umgehen? Frage:

In die Ergebnisformel bauen Sie mit der Wenn-Funktion eine Abfrage ein. Die Formel sieht so aus: Antwort:

Bedingt abhängig: Eingabewerte regeln über Bedingungen die Darstellungen in den anderen Zellen.

11

„Ausfüllen“, „Rechts“ können Sie die Formatierungen schnell innerhalb einer Zeile übertragen. Allerdings gehen dabei die bereits eingegebenen Preise verloren. Beim Ausfüllen der Zellen nach unten bleibt der Bezug in der bedingten Formatierung auf die Zelle „G3“ erhalten, da sie mit dem Dollarzeichen fest angegeben ist. Sie müssen alle Zellen nochmals bearbeiten und den Bezug anpassen.

einer Formel lösen. In unserem Beispiel stehen in den Feldern „B1“ bis „B4“ vier Zahlen. Wenn sich die größte Zahl in „B3“ befindet, soll die Ergebniszelle den Wert aus „A3“liefern. Geben Sie in die Ergebniszelle folgende Formel ein:

=WENN(exponent=0; 1; basis^t exponent)

Die Werte „exponent“ und „basis“ ersetzen Sie in dieser Formel durch die entsprechenden Zellen. www.chip.de/forum/office

14

|

Excel 97, 2000

=INDEX(A1:A4; t VERGLEICH(MAX(B1:B4);B1:B4;0);1)

Zunächst ermittelt „MAX(B1:B4)“ den Maximalwert aus dem Datenbereich. Dieser Wert dient im Folgenden als Suchkriterium für den Vergleich, der sich auf die Suchmatrix „B1:B4“ erstreckt. Mit dem Vergleichstyp „0“ liefert die Funktion die Position des ersten Wertes aus der Suchmatrix, der mit dem Suchkriterium identisch ist. Die Matrix-Variante der Funktion INDEX liefert schließlich den Wert aus der Wertematrix „A1:A4“, der sich in der ersten Spalte der durch den Vergleich ermittelten Zeile befindet.

15

|

Excel 97, 2000

Bestimmte Zelle nach Maximumwert ermitteln

Felder abhängig von Variablen addieren

Sie wollen in einer Ergebniszelle den Inhalt einer anderen Zelle anzeigen lassen, die links neben dem Maximum eines Datenbereichs steht.

Um die Werte eines Bereichs abhängig von den Werten eines zweiten Bereichs zu addieren, nutzen Sie die Funktion SUMME/WENN. Bei festen Kriterien funktioniert alles problemlos. Mit variablen Vorgaben scheitert die Funktion allerdings.

www.chip.de/forum/office97

12

|

Excel 97

Zugriff auf die Datei PAGEFILE.SYS verhindern

Durch die Kombination von drei Funktionen lässt sich die Aufgabe mit Tipp:

Ich arbeite unter Windows NT 4.0. Seit einiger Zeit versucht Excel 97 erfolglos, beim Programmstart auf die Datei PAGEFILE.SYS zuzugreifen. Wie kann ich das verhindern? Frage:

Die PAGEFILE.SYS ist die Auslagerungsdatei von Windows. Um den Zugriff darauf abzustellen, wählen Sie „Start“, „Ausführen“ und geben dies ein: 1. „Excel /unregserver“: Damit löschen Sie die Registry-Einträge, 2. „Excel /regserver“: Schreibt die Einträge in der Registry neu, 3. „Excel /o“: Öffnet Excel in der Originalkonfiguration. Antwort:

www.chip.de/forum/office

Verschachtelte Bezüge: Mit der Kombination von drei verschiedenen Funktionen

14 ermitteln Sie den Zellinhalt, der neben dem höchsten Wert in einer Spalte steht.

CHIP | WORKSHOP

80

15

Felder vergleichen: Bestimmte Vergleiche mit dem Inhalt einer anderen Zelle funktionieren erst durch eine spezielle Excel-Verkettung mit dem Operator „&“.

Mit folgendem Kniff können Sie auch Variablen verwenden. Die Funktion SUMME/WENN benötigt drei jeweils durch ein Semikolon getrennte Argumente. Zunächst geben Sie den Zellbereich an, dessen Inhalt die Funktion prüfen soll. Als zweites Argument folgt ein Vergleichskriterium in Anführungszeichen. Zuletzt geben Sie noch den Zellbereich für die Summenberechnung an. Mit einem fest vorgegebenen Kriterium sieht der Funktionsaufruf zum Beispiel folgendermaßen aus: Tipp:

=SUMMEWENN(A1:A4;“>16000“;B1:B4)

Für einen variablen Vergleichswert, zum Beispiel in Zelle „A6“, müssen Sie vor dem Namen der Zelle ein kaufmännisches Und-Zeichen einfügen. Der Befehl sieht folgendermaßen aus: „“>“&A6“. Die Eingabe „“>A6“„ funktioniert nicht, da Excel den Wert als Zeichenfolge interpretiert anstatt den Wert mit dem Inhalt der Zelle zu vergleichen.

16

|

zeln vergleichen. Zudem haben Sie aber auch noch die Möglichkeit, weitere Auswertungen des Vergleichsergebnisses vorzunehmen. Voraussetzung für die Prozedur ist allerdings: Die Excel-Tabellen der verschiedenen Mitarbeiter sind gleich aufgebaut und liegen zum Beispiel als Dateien DATEI1.XLS und DATEI2.XLS im gleichen Ordner. Ist dies der Fall, legen Sie nun zunächst eine neue Excel-Datei an, die die gleiche Struktur aufweist wie die beiden anderen Tabellen. Positionieren Sie nun den Cursor auf dem leeren Tabellenblatt in der linken oberen Zelle des zu vergleichenden Datenbereichs. Dort geben Sie nun folgende Formel ein: =WENN([DATEI1.XLS]Tabelle1!B2=t [DATEI2.XLS]Tabelle1!B2;0;1)

Diese Beispielformel geben Sie in der Zelle „B2“ ein. Sie vergleicht die Inhalte

Excel 97, 2000

Zelleninhalte zweier Tabellen vergleichen Sie arbeiten oft mit großen Excel-Tabellen, in die mehrere Personen auch außerhalb einer Firma hineinschreiben. Nun soll der Inhalt zweier Tabellen verglichen werden, um Änderungen unterscheiden zu können. Diese Aufgabe können Sie am besten mit Hilfe einer dritten Tabelle lösen. Damit kann Excel selber alle Zellen einTipp:

CHIP | WORKSHOP

Auf einen Blick: Rot unterlegte

16 Zellen zeigen die Unterschiede in zwei Excel-Tabellen an.

der Zellen „B2“ in den jeweiligen Tabellen mit der Bezeichnung „Tabelle1“ in den beiden Dateien DATEI1.XLS und DATEI2.XLS. Bei gleichen Zellinhalten liefert sie den Wert „0“, bei unterschiedlichen Zellinhalten das Ergebnis „1“. Damit Sie etwaige Unterschiede später schnell finden, können Sie die betreffenden Zellen mit Hilfe der bedingten Formatierung auch etwa rot unterlegt darstellen. Führen Sie dazu den Befehl „Format“, „Bedingte Formatierung“ aus. Im zweiten Kombinationsfeld des Dialogfensters wählen Sie nun die Einstellung „größer als“ und geben im rechten Eingabefeld den Wert „0“ ein. Nun klicken Sie auf den Button „Format“ und aktivieren die Registerkarte „Muster“, um die Farbe für den Zellenhintergrund festzulegen. Beenden Sie anschließend beide Dialoge mit einem Klick auf „OK“. Nun müssen Sie noch den Vergleich auf den erforderlichen Bereich ausdehnen. Markieren Sie dazu zunächst in der ersten Zeile den Datenbereich und rufen Sie anschließend den Befehl „Bearbeiten“, „Ausfüllen“, „Rechts“ auf. Bringen Sie jetzt den Cursor nacheinander jeweils in die oberste Zelle des Datenbereichs einer Spalte, markieren Sie den Datenbereich innerhalb der Spalte und führen Sie den Befehl „Bearbeiten“, „Ausfüllen“, „Unten“ aus. Sie sehen nun auf den ersten Blick, an welchen Stellen sich die beiden Tabellen unterscheiden. Als Gesamtauswertung können Sie für große Tabellen auch die Summe über den gesamten Datenbereich berechnen und in einer anderen Zelle ablegen. Falls diese Summe Null ist, sind die Zellinhalte beider Tabellen vollkommen identisch. Wenn Sie häufig gleich aufgebaute Excel-Tabellen vergleichen müssen, können Sie die in diesem Beispiel angelegte Tabelle stets wiederverwenden. Sie müssen dann lediglich die zu vergleichenden Dateien in denselben Ordner kopieren und entsprechend umbenennen. Um die neuen Tabellen aufzunehmen, öffnen Sie zunächst die Vergleichstabelle. Dabei fragt Sie Excel, ob Sie die Zellbezüge auf die externen Dateien aktualisieren wollen. Sobald Sie diese Frage mit „Ja“ bestätigt haben, startet Excel automatisch den Vergleich auch mit den neuen Files.

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

81

» SERVICE

Tipps & Tricks

17

|

Excel 97, 2000

Identische Datensätze in einer Tabelle finden In einer umfangreichen Excel-97-Tabelle sind zahlreiche Datensätze mehrfach vorhanden. Sie wollen sämtliche Dubletten suchen, um sie zu löschen. Allerdings kann das einige Stunden dauern. Dieser Tipp erspart Ihnen viel Zeit. Diese Aufgabe lässt sich mit Hilfe einer verschachtelten Vergleichsfunktion in wenigen Schritten lösen. Sortieren Sie zunächst die Daten in der Tabelle. Dazu setzen Sie den Cursor an eine beliebige Position im Datenbereich und rufen den Befehl „Daten“, „Sortieren“ auf. Sie können bis zu drei Spalten (Datensatzelemente) auswählen, nach denen wahlweise aufsteigend oder absteigend sortiert wird. Sie sollten nur solche Datensatzelemente als Sortierkriterien verwenden, mit denen sich die einzelnen Datensätze leicht unterscheiden lassen. Eine fortlaufende Nummerierung gehört zum Beispiel nicht zu den eigentlichen Daten und ist als Sortierkriterium ungeeignet. Als nächstes vergleichen Sie in einer leeren Spalte hinter den Datenfeldern die drei Felder, die Sie zuvor als Sortierkriterien ausgewählt haben. Fügen Sie dazu in eine neue Spalte eine verschachtelte Wenn-Funktion ein. In unserem Beispiel stehen die gewählten Sortierkriterien des ersten Datensatzes in den Zellen „B2“ bis „D2“. Geben Sie nun in der nächsten leeren Spalte in die Zelle „F2“ folgende Funktion ein: Tipp:

=WENN(B2=B3;WENN(C2=C3;WENNt (D2=D3;“doppelt“;“„);“„);“„)

Wenn alle verglichenen Felder beider Datensätze identisch sind, erscheint die Meldung „doppelt“ im Testfeld. Um diese Kontrollfunktion in die gesamte Spalte zu übertragen, markieren Sie diese über den gesamten Bereich der Datensätze und führen dann den Befehl „Bearbeiten“, „Ausfüllen“, „Unten“ aus. Abschließend klicken Sie in die Spalte und wählen den Befehl „Daten“, „Filter“, „AutoFilter“ aus. Wenn Sie im Kombinationsfeld der ersten Zeile den Eintrag „doppelt“ wählen, erhalten Sie automatisch eine Listendarstellung aller mehrfach vorhandenen Datensätze. Die können Sie nun überprüfen und zeilenweise löschen. www.chip.de/forum/office97

Sub exportCSV() On Error GoTo Err_exportCSV Dim mySection As Object t ‘ mySection Dim myRow As Object t ‘ Zeile Dim myCell As Object t ‘ Zelle Dim strSeparator As String Dim strFile As String Dim strTemp As String Const DlgMeldung = „Geben t Sie bitte Pfad und Dateit namen der Zieldatei ein!“ Const DlgTitel = „Eingabe t der Zieldatei“ Const Trennzeichen As t String = „;“

18

|

Excel 97

Tabellen mit VBA als Textdateien exportieren

strFile = InputBox(Dlgt Meldung, DlgTitel, „“) strSeparator = „“ Set mySection = t ActiveSheet.UsedRange Open strFile For Output As #1

Mit Hilfe eines VBA-Programms wollen Sie eine Excel-Tabelle im Textformat speichern. Dabei sollen die Spalten durch ein Semikolon getrennt sein. Das Problem: Das VBA-Programm nutzt nur Kommata zum Unterteilen.

For Each myRow In t mySection.Rows For Each myCell In t myRow.Cells If InStr(1, myCell.Text,t „;“) > 0 Then strTemp = strTemp & t

Das ist ein Fehler von Excel 97 beim Speichern von CSV-Dateien, die als Trennzeichen das Semikolon verwenden. Während das Programm beim Speichern über „Datei“, „Speichern unter“ mit dem Strichpunkt arbeitet, nutzt das englische VBA fälschlicherweise das englische Trennzeichen, ein Komma. In Excel 2000 ist dieses Problem gelöst. Um auch mit Excel 97 per VBA eine Textdatei mit Semikola als Trennzeichen zu erzeugen, müssen Sie Ihre ExportRoutine wie folgt anpassen (s. rechts): Tipp:

strSeparator & „“„“ & t CStr(myCell.Text) & „“„“ Else strTemp = strTemp & t strSeparator & CStrt (myCell.Text) End If strSeparator = Trennzeichen Next Print #1, strTemp strTemp = „“ strSeparator = „“ Next Close #1 Set mySection = Nothing Exit_exportCSV: Exit Sub Err_exportCSV: MsgBox Err.Description Resume Exit_exportCSV

17

Dubletten schnell aufgespürt: Identische Datensätze werden mit Hilfe einer automatischen Filterfunktion gekennzeichnet. Sie können sie ohne Aufwand löschen.

End Sub

CHIP | WORKSHOP

82

möchte nun auch die Inhalte der Zellen selbst als Kommentar zur Zelle drucken. Ist das denn möglich?

Antwort: Ja, das funktioniert auf ganz ähnliche Weise mit einem kleinen Makro. So geht’s: Rufen Sie den Befehl „Extras“, „Makro“, „Makros“ auf, legen Sie einen Namen wie etwa „Zellwert_ in_Kommentar“ fest und klicken Sie auf den Button „Erstellen“. Nun geben Sie die folgenden Anweisungen in den VBA-Editor ein:

19

Diagramm-Export: Wandeln Sie die XLS-Datei in HTML um – Diagramme werden einzeln als GIF-Datei gespeichert.

19

|

Excel 97, 2000

Diagramme als HTML-Datei exportieren Sie wollen ein Excel-Diagramm in eine HTML-Datei umwandeln. Das ermöglicht Ihnen, das Diagramm in ein anderes Programm zu exportieren, um es etwa als Bitmap-Grafik zu verwenden.

Sub Zellwert_in_Kommantar() ActiveCell.Select ActiveCell.ClearComments

20

|

Excel 97, 2000

Zellinhalte als Kommentar zur Zelle drucken Frage: In einer früheren Ausgabe haben Sie erklärt, wie sich Formeln als Kommentare zur Zelle drucken lassen. Ich

CHIP | WORKSHOP

=GANZZAHL(ZUFALLSZAHL()*t 1000000+1)

Markieren Sie den Bereich bis zur Zeile 5.200 und kopieren Sie mit „Bearbeiten“, „Ausfüllen“, „Unten“ die Formel. Nun enthält die erste Spalte die gesuchten Zufallszahlen. Um Zahlendoppler auszuschließen, schreiben Sie in die Zelle „C1“

ActiveCell.AddComment ActiveCell.Comment.t

=WENN(B1=B2;“doppelt“;“„)

Text Text:=ActiveCell.Text End Sub

Das Makro wählt die aktive Zelle aus, löscht eventuell vorhandene Kommentare und fügt mit Hilfe der Funktion „ActiveCell.Text“ den Inhalt der aktiven Zelle als Kommentar hinzu.

Tipp: Zunächst legen Sie wie gewohnt ein Diagramm im Excel-Format an. Anschließend wandeln Sie Ihre Datei mit dem Befehl „Datei“, „Als HTML speichern“ in das HTML-Format um. Ein Assistent führt Sie durch den Export-Vorgang. Excel exportiert jedes Element der Arbeitsmappe einzeln. Je nach Größe kann dies einige Sekunden dauern. Excel speichert außer der HTML-Datei auch mehrere GIF-Dateien. Die haben den gleichen Basisnamen, ergänzt durch eine fortlaufende Nummerierung. Jede GIF-Datei enthält ein einzelnes Diagramm, auf das Sie zugreifen können. Wenn Sie die Diagramme als BitmapGrafik weiterverwenden wollen, öffnen Sie die GIF-Dateien mit einem Bildbearbeitungsprogramm.

rator erzeugen. Das birgt jedoch die Gefahr, dass einzelne Zahlen mehrfach auftreten. Mit einigen Hilfsmitteln kann man das aber auch verhindern. Um die Zahlen zu erzeugen, geben Sie in der Zelle „A1“ die folgende Formel ein:

20

Kommentiert: Mit einem Makro lässt sich der Zellinhalt automatisch als Kommentar hinterlegen.

Um die Kommentare zu drucken, rufen Sie den Befehl „Datei“, „Seite einrichten“ auf, aktivieren die Registerkarte „Tabelle“ und wählen im Kombinationsfeld „Kommentare“ die Option „Am Ende des Blattes“.

21

|

Excel 97, 2000

und kopieren die Formel ebenfalls bis zur Zeile 5.200 nach unten. Schreiben Sie nun in die Zelle „D1“ die Funktion =ZÄHLENWENN(C:C;“doppelt“)

Kopieren Sie alle Werte der Spalte „A“ mit „Bearbeiten“, „Inhalte einfügen“, „Werte“ in die Spalte „B“. Sortieren Sie die Daten in Spalte „B“ in aufsteigender Reihenfolge. Falls keine Dubletten auftreten, zeigt das Feld „D1“ den Wert Null an. In diesem Fall sind die Zufallszahlen fertig. Da die Werte der Spalte „A“ inzwischen neu berechnet sind, können Sie die Zahlen aus Spalte „B“ verwenden. Falls Dubletten in Ihren Zahlen aufgetreten sind, wiederholen Sie das Kopieren und Prüfen aus der inzwischen bereits neu berechneten Spalte „A“. Schließlich kopieren Sie die Werte aus der Spalte „B“ in eine freie Spalte und geben der Spalte das Format “000000“. Dann erscheinen alle Zahlen mit sechs Stellen und bei Bedarf mit führenden Nullen.

Viele Zufallszahlen ohne Duplikate erzeugen Frage: Für ein Gewinnspiel benötige ich 5.200 zufällige, sechsstellige Zahlen. Um diese Zahlen in einen Word-Serienbrief zu integrieren, brauche ich diese Zahlen in Excel.

Antwort: Zahlen in beliebiger Menge lassen sich in Excel mit dem Zufallsgene-

21

Alles Zufall: Excel kann beliebig viele Zufallszahlen generieren und das Auftreten von Dubletten ausschließen.

84

Makros aufzeichnen

Effektiv mit Makros arbeiten Um häufig wiederkehrende Arbeitsschritte zu automatisieren, sollten Sie sie als Makros aufzeichnen. Dieser Beitrag beschreibt die unterschiedlichen Aufzeichnungsmethoden, um Zellen oder Bereiche zu bearbeiten, und macht Sie mit den Speicher-Optionen bekannt.

M

akros sollten immer dann zum Einsatz kommen, wenn eine Funktion immer wieder gestartet werden soll. Dazu zeichnen Sie die Bearbeitungsschritte in Excel auf.

1

Makro-Aufzeichnung vorbereiten

Zunächst bereiten Sie eine Mustertabelle vor, die alle Eigenschaften aufweist, auf die das Makro Einfluss nehmen wird. Soll es etwa Text suchen, sollte er zumindest einmal in der Tabelle vorkommen, damit es während der Makro-Aufzeichnungen nicht zu Fehlermeldungen kommt. Über „Extra“, „Makro“, „Aufzeichnen“ starten Sie den Makro-Assistenten. Als Standardnamen gibt er „Makro 1“ vor. Per Tastenkombination können Sie einen Buchstaben angeben, mit dem Sie das Makro später direkt starten können. Legen Sie im nächsten Schritt fest, wo das Makro gespeichert werden soll. Wählen Sie „Diese Arbeitsmappe“, steht das Makro nur in der aktuellen Excel-Mappe zur Verfügung. Da sich die meisten Ma-

SICHERHEITS-TIPP

» So schützen Sie sich vor Makro-Viren Makros können auch Änderungen an Programmen und dem Dateisystem vornehmen, was sich die Programmierer von Computer-Viren zunutze machen. Daher liefert Excel beim Öffnen eines Dokuments, das Makros enthält, eine Warnmeldung. Sie können dann entscheiden, ob die Makros aktiviert werden sollen oder nicht. Letzteres stoppt die Ausbreitung von Makro-Viren. In Dokumenten unbekannter Herkunft sollte dies zumindest so lange der Fall sein, bis ein Virenscanner die Datei auf gefährliche Makros geprüft hat oder der Quelltext von Makros auf Fehler durchsucht ist.

CHIP | WORKSHOP

1

Excel fragt vor der Makro-Aufzeichnung auch die Tastenkombination zum Starten des Makros ab.

kros auf konkrete Inhalte beziehen, ist dies meist die geeignete Wahl. Wenn Sie hingegen ein Makro anlegen, das relativ unabhängig vom Inhalt ist, etwa das gesamte Arbeitsblatt mit Linien durchzieht, speichern Sie es unter „Persönliche Makro-Arbeitsmappe“, um es in allen Projekten immer wieder nutzen zu können. Abschließend geben Sie im Feld noch eine Kurzbeschreibung der geplanten Funktion des Makros ein. So lassen sich mehrere Makros besser verwalten und auch noch nach längerer Zeit besser verstehen. 2

Achtung, Makro-Aufnahme!

Mit dem letzten „OK“ beginnt die MakroAufzeichnung. Gleichzeitig blendet Excel eine kleine Leiste mit zwei Icons ein. Das linke, punktförmige beendet die Aufzeichnung, das rechte regelt, wie Bezüge aufzuzeichnen sind. Nicht gedrückt protokolliert Excel fixierte Bezüge, gedrückt relative Bezüge. Wie sich dies für das Makro auswirkt, zeigt die Musterdatei „Makro01.xls“. Starten Sie zunächst über [Strg]+[A] das Makro mit den fixierten Bezügen. Es sucht nach dem nächsten Vorkommen von „Willi“, ändert das Format der Zelle und füllt die folgenden drei Zellen der

Zeile grün auf. Rufen Sie das Makro erneut auf, wird zwar der nächste „Willi“ umgefärbt, doch beim Grün scheint sich nichts getan zu haben. In Wirklichkeit füllt es die Zellen C6 bis E6 jedesmal neu mit Grün auf, da sich die fixierte MakroAufzeichnung diese Zellen gemerkt hat. Rufen Sie über [Strg]+[B] das zweite Makro auf, wird der nächste „Willi“ umgefärbt und diesmal auch die drei Zellen daneben, egal wo sich der „Willi“ findet. Uberprüfen Sie das durch mehrmaligen Aufruf. Das fixierte Aufzeichnen kommt also immer dann zum Einsatz, wenn in einer Tabelle an einer ganz bestimmten Stelle etwas geändert werden soll. Die relative Methode empfiehlt sich, wenn sich abhängig von einer Sprungstelle die Umgebung ändern soll. Zwischen beiden Methoden können Sie durch Anklicken des Icons „Relativer Bezug“ wechseln. Während der Aufzeichnung des Makros können Sie alle Arbeiten durchführen, die Sie auch sonst mit Excel erledigen. Wartezeiten werden nicht berücksichtigt, Befehle erst protokolliert, wenn sie ausgeführt sind. Ein Tippfehler bei der Eingabe des Suchbegriffs fällt also im Makro nicht auf. Karl Dreyer

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

85

» SERVICE

Arbeiten mit Makros

Makros einbinden

Makros in Tabellen integrieren Auch über grafische Symbole oder selbstdefinierte Icons in einer Symbolleiste lassen sich Makros starten. Dieser Beitrag zeigt Ihnen, wie Sie einem Grafikobjekt ein Makro zuweisen und Makro-Aufrufe in eigene Symbolleisten integrieren, die sich verschieben und fixieren lassen.

D

as beste Makro ist nutzlos, wenn der Empfänger einer Excel-Datei nichts von seiner Existenz weiß. Daher installieren Sie Schalter für die Makros dort, wo der Makro-Aufruf sinnvoll ist (vgl. Musterdatei „Makro02.xls“).

1

Makros an Grafiken und Texte hängen

Jedes Makro lässt sich auch über grafische Symbole starten. Zeichnen Sie ein Makro auf und erzeugen Sie über „Einfügen“, „Grafik“, „Autoformen“, „Blockpfeile“ einen Pfeil. Lassen Sie ihn nun entweder auf eine Zelle zeigen, in der wie im Beispiel rechts eine kurze Erklärung zum Makro zu sehen ist. Sie können den Pfeil aber auch mit der rechten Maustaste anklicken, um Text hinzufügen zu können. Die Verbindung von Makro und Grafikobjekt erfolgt ebenfalls über die rechte Maustaste. Ist der Text „Makro zuweisen“ ausgegraut, wurde das Grafikelement nicht richtig angewählt. Schieben Sie es mit der rechten Maustaste an eine andere Stelle und rufen Sie anschließend ohne

PROFI-TIPP

» Die Grenzen beim Makro-Aufruf in Excel Nicht alle Makros lassen sich problemlos in Symbolleisten aufnehmen. Insbesondere bei Suchbefehlen und dem Aktualisieren von Pivot-Tabellen ist es entscheidend, dass das Makro von dem Tabellenblatt aus gestartet wird, auf das es sich bezieht. Dies gilt auch, wenn Sie bei Beginn der Makro-Aufzeichnung explizit auf das entsprechende Tabellenblatt wechseln. Werden diese Makros dann von einer anderen Seite gestartet, führt Excel die Sprunganweisung nicht korrekt aus, so dass es beim Start der eigentlichen Prozedur zu einem ärgerlichen Fehler kommt.

Benutzen Sie mehrere Makros oder Tabellenblätter, stellen Sie der Mappe ein Blatt mit allen Makro-Aufrufen voran.

1

weitere Mausbewegung über die rechte Taste die „Optionen“ auf. Nun ist der Befehl „Makro zuweisen“ zugänglich und öffnet ein Fenster mit allen verfügbaren Makros. Es ist auf „Makros in: Alle offenen Arbeitsmappen“ eingestellt. Um sicherzustellen, dass die zugewiesenen Makros nach dem Öffnen der zugehörigen Mappe wirklich verfügbar sind, ändern Sie die Vorgabe vor der Auswahl in „Diese Arbeitsmappe“. Nun wählen Sie das Makro aus und weisen es zu. Wählen Sie nun die Grafik mit der rechten Maustaste an, kopieren sie und fügen sie wieder ein, ist der Makro-Aufruf auch in der Kopie vorhanden. So lassen sich identische Aufrufe, etwa der Verweis auf die Startseite mit einer Liste aller Makros, überall in der Mappe wiederholen. 2

Makros in Symbolleisten integrieren

Sie können Makros auch in Symbolleisten unterbringen, die sich wie die mitgelieferten Leisten auf dem Bildschirm verschieben und am Rand andocken lassen. Wählen Sie „Extras“, „Anpassen“, „Symbolleisten“ und legen Sie über „Neu“ eine eigene

Leiste an, die als leere, fast rechteckige Leiste auf dem Bildschirm erscheint. Wechseln Sie auf die Registerkarte „Befehle“ und wählen Sie die „Kategorie: Makros“ aus. Schieben Sie das SmileySymbol „Schaltfläche anpassen“ auf die leere Symbolleiste, um den Platzhalter für das erste Makro anzulegen. Wählen Sie es in der neuen Leiste gleich mit der rechten Maustaste an. Über „Makro zuweisen“ wählen Sie zunächst das über diesen Button zu startende Makro aus und geben im Feld „Name“ den Namen des Makros an. Wählen Sie „Nur Text“, damit in der Symbolleiste nur die vergebenen Namen der Makros erscheinen, oder „Schaltflächensymbol und Text“, um Icon und Text anzuzeigen. Das Icon läßt sich über „Schaltflächensymbol ändern“ aus einer Liste aussuchen oder über „Schaltflächensymbol bearbeiten“ komplett neu zeichnen. Schließen Sie das Fenster „Anpassen“, wird die Symbolleiste aktiv. Sie können sie wie alle anderen Leisten über „Ansicht“, „Symbolleisten“ an- und ausschalten. Karl Dreyer

CHIP | WORKSHOP

86

Haushaltsbuch mit Excel-Makros

Auf Heller und Pfennig – Überblick dank Excel D

as Konto mal wieder in den roten Zahlen? Erst gestern 400 Mark vom Geldautomaten geholt und schon wieder Ebbe im Portemonnaie? Dann wird es Zeit für eine effiziente Einnahmen- und Ausgabenkontrolle. Excel ist prädestiniert für Rechenprojekte dieser Art. Für die meisten ZahlenAbfragen reichen die Standard-Funktionen. Für die komfortable Eingabe und die eine oder andere komplizierte WerteAbfrage müssen aber auch ein paar kurze Excel-Makros formuliert werden. Pro-

CHIP | WORKSHOP

grammierkenntnisse sind dafür allerdings nicht erforderlich, denn in diesem Workshop erfahren Sie alles Wesentliche Schritt für Schritt.

1

Arbeitsblätter bereitstellen

Sie brauchen insgesamt vier Arbeitsblätter: eines für die Auflistung Ihre laufenden Ausgaben, eines für die Summen der Ausgaben, eines für die Bilanz und schließlich ein letztes für die Visualisierung Ihres Privat-Budgets anhand von Diagrammen.

Dazu klicken Sie mit der rechten Maustaste auf einen der drei RegisterEinträge und bestätigen im Kontextmenü die Anweisung „Einfügen“ mit einem Mausklick. Mit der linken Maustaste verschieben Sie übrigens das Tabellenblatt an die richtige Stelle innerhalb der Reihenfolge aller Blätter. Anschließend vergeben Sie die passenden Namen („Ausgaben“, „Summen Ausgaben“, „Bilanz“ und „Diagramm“), indem Sie ebenfalls im Kontextmenü den Befehl „Umbenennen“ wählen.

Foto: K. Satzinger; Composing: V. Hildebrand

Ein perfekt gestaltetes Haushaltsbuch anzulegen, ist weder für Excel noch für Sie ein Problem. Und mit den passenden Makros werden auch komplizierte Auswertungen zum Kinderspiel. Dieser Workshop zeigt Ihnen, wie das Haushaltsbuch funktioniert.

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

87

» SERVICE

Excel-Makros

2

Das Formular für Ausgaben anlegen

Beginnen Sie mit dem „Formular“, in das Sie Ihre täglichen, wöchentlichen und monatlichen Ausgaben eintragen. In der ersten Spalte folgen die Datumsangaben, damit Sie nachvollziehen können, wann Sie wieviel ausgegeben haben. Außerdem können Sie so besser kontrollieren, ob Sie die teuren Dinge des Lebens sinnvollerweise am Monatsanfang beschaffen. Formatieren Sie dafür alle Zellen (bis auf die Spaltenüberschriften und Zwischenüberschriften der Spalte A) als Datumswerte. Bei den Auswertungen müssen Sie mit den Datumsangaben rechnen können. Das erledigt die Formel =Monat([Zelle]). Fügen Sie in die Spalte „B“ daher als Spaltenüberschrift „Monat“ ein und kopieren Sie diese Formel in alle Zeilen, die keine Spaltenüberschriften enthalten. Stellen Sie beim Kopieren sicher, dass die relativen Zellbezüge erhalten bleiben, die Formel also dynamisch an alle weiteren Zeilen angepasst wird. In die erste Zeile schreiben Sie dann die Spaltentitel für die Tagesausgaben in die Spalten C bis I. Die „Tagesausgaben“ umfassen Käufe, die Sie täglich in unterschiedlicher Höhe vornehmen, etwa Nahrungsmittel oder Zeitschriften. Einige Zeilen darunter definieren Sie in gleicher Art und Weise Spalten für typische Wochenausgaben, beispielsweise für Bekleidung, Bücher oder CDs. Lassen Sie darunter ebenfalls einige Zeilen für Ihre Eingaben frei. Darunter wiederum platzieren Sie die Monatsausgaben. Die können Sie im Normalfall bereits vorher absehen, da es sich um so genannte Fixbeträge handelt. Deswegen brauchen Sie dafür nur eine Zeile zur Eingabe. In die letzte Spalte schreiben Sie „Gesamt“ oder „Summe“. Wählen Sie dann eine Summen-Zelle an, klicken Sie auf das Summen-Symbol „Sigma“ in der Menüleiste und markieren Sie mit der Maus alle zu addierenden Zellen pro Zeile. Kopieren Sie diese Formel für alle weiteren Zeilen, die später Eingaben enthalten könnten. Formatieren Sie anschließend die gleichen Zellen auf DM-Beträge. In Excel 2000 können Sie auch Euro-Formatierungen einsetzen. Dann suchen Sie über „Format“, „Autoformat“ ein passendes Layout für Ihre Tabelle. Excel bietet sehr attraktive Tabellenvorlagen. Achten Sie aber darauf,

dass Sie sich nicht für allzu bunte Varianten entscheiden, wenn Sie die Seiten später drucken. Apropos Drucken: Zum Schluss geben Sie über „Datei“, „Seitenansicht“ an, dass das Dokument später im Querformat gedruckt werden soll. 3

Den Auswertungsbogen gestalten

Schneller gestalten Sie den Auswertungsbogen im zweiten Arbeitsblatt, mit dem Sie alle Ausgaben monatsweise addieren. Das ist sinnvoll, weil Sie die Summen später den monatlichen Einnahmen gegenüberstellen können. Diagramme können Sie allerdings trotzdem tages- oder wochenweise anfertigen, wie Sie im Verlauf des Workshops noch erfahren. Um die Eingabe der Formeln über sämtliche Arbeitsblätter hinweg zu erleichtern, vergeben Sie für inhaltlich zusammenhängende Zellbereiche so genannte Bereichsnamen. Dazu markieren

Sie zunächst alle Zellen für Tagesausgaben der Spalte „Nahrungsmittel“ samt Spaltentitel. Anschließend wählen Sie aus dem Hauptmenü den Befehl „Einfügen“, „Namen“, „Festlegen“ (in Excel 2000: „Einfügen“, „Namen“, „Definieren“). Im folgenden Dialog schlägt Excel bereits einen passenden Namen vor: „Nahrungsmittel“. Bestätigen Sie mit „OK“ und wiederholen Sie das Ganze für alle Kategorien von Posten und Zeitangaben. Wichtig: Bei der Bereichsdefinition der Monatswerte in der Spalte B müssen Sie für die vier Abschnitte „Tagesausgaben“, „Wochenausgaben“, „Monatsausgaben“ und „Sonderausgaben“ einzelne Bereiche festlegen, sonst rechnet Excel falsch. Im Beispiel haben wir fortlaufende Bereichsnamen wie „Monat“, „Monat_1“, „Monat_2“ und „Monat_3“ gewählt. Beschriften Sie nun das Blatt, indem Sie in die erste Zeile und damit ab der Spalte „B“ als Titel die Monatszahlen k

Über

1

das

Kontextme-

legen Sie neue Arbeitsblätter an, löschen Seiten oder ändern die Bezeichnungen. nü

Im er-

2

sten

listen Sie nur die Ausgaben. Excels Autoformate helfen Ihnen, die Tabelle optisch schnell in Form zu bringen. Blatt

CHIP | WORKSHOP

88

Wenn

3

Sie Be-

reiche defi-

lassen sich Berechnungen auch über mehrere Arbeitsblätter hinweg bequem vornehmen. nieren,

Die

3

SUM-

MEWEN N-

holt sich zunächst die passenden Werte aus dem ersten Arbeitsblatt und addiert sie dann monatsweise. Formel

gaben summieren. Das Beispiel beginnt im November, also steht an dieser Stelle die Zahl „11“. Die Formel summiert auf Grundlage des ersten Tabellenblattes alle Werte für „Nahrungsmittel“, für die die Monatswerte des Spaltentitels aus dem zweiten Arbeitsblatt (also die Zahlen „1“ bis „12“) identisch sind, mit den übergebenen Monatswerten des ersten Arbeitsblattes (also die Einträge in der Spalte „Monat“ des ersten Tabellenblattes). Da Sie zuvor die Bereichsnamen festgelegt haben, brauchen Sie in der Formel nicht mit kryptischen Zellenkoordinaten zu operieren, sondern können tatsächlich „Monat“ beziehungsweise „Nahrungsmittel“ in die Formel schreiben. Excel gibt sofort das korrekte Ergebnis aus. Kopieren Sie die Formel in die übrigen freien Zellen der Matrix, wobei Sie selbstverständlich den Bereichsnamen in der Formel jeweils dem Zeilentitel aus der ersten Spalte anpassen, also für „Nahrungsmittel“ etwa „Haushaltswaren“ eingeben. Falls Sie die Formel mit [Strg]+[C] kopieren, achten Sie auch bei der Monatszahl in der Mitte der Formel unbedingt auf korrekte Zellbezüge. 4

von „1“ bis „12“ eintragen. In die Spalte „A“ schreiben Sie dann von oben nach unten als Zeilentitel die Ausgabenposten. Der Übersichtlichkeit wegen ist in der Beispieldatei jeweils eine Zeile zwischen Tages-, Wochen- und Monatsausgaben leer geblieben. Das ist allerdings für die Berechnung nicht nötig. Fügen Sie eine Spalte für die Summe aller Ausgabenposten an und eine Zeile für die Summe aller Monatsausgaben. Formatieren Sie die Matrix anschließend über den Menübefehl „Format“, „Autoformat“. Im Arbeitsblatt „Ausgaben“ werden die Datumswerte der Spalte „A“ als ganze Zahlen (Monatswerte) in Spalte „B“ mit der Formel „=Monat ([Zelle])“ übergeben. Das ist dann sinnvoll, falls Sie nicht jeden Tag einkaufen, in der ersten Spalte also Daten aus unterschiedlichen Monaten geführt werden. Für die Auswertung ist nämlich genau dieser Monatswert wichtig.

CHIP | WORKSHOP

Schreiben Sie in jede Monatszelle für die Zeile „Nahrungsmittel“ folgende Formel „=SUMMEWENN (MONAT; [„Zelle Spaltentitel“], Nahrungsmittel)“. Für „Zelle Spaltentitel“ geben Sie eine Zahl von „1“ bis „12“ ein, je nachdem aus welchem Monat Sie gerade die Aus-

Das Blatt „Bilanz“ definieren

Im nächsten Schritt legen Sie ein drittes Arbeitsblatt an, das die monatlichen Ausgaben für ein Jahr sowie die monatlichen Einnahmen führt. Geben Sie daher dem Blatt zunächst den Namen „Bilanz“. Im oberen Bereich der Tabelle schaffen Sie Platz für eine kleine Matrix, die die monatlichen Einnahmen führt. Dieses Beispiel basiert auf der Annahme, dass Sie ein konstantes Monatsgehalt erzielen.

Mögli-

4

che

Einnahmen

aus Urlaubsoder Weihnachtsgeld erfassen Sie mit einem komfortablen Kombinationsfeld.

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

89

» SERVICE

Excel-Makros

Wählen Sie über „Format“, „Autoformat“ ein passendes Zellen-Layout für diesen Bereich, etwa das Modell „3D-Effekt2“. Überschreiben Sie diese Tabelle mit „Einnahmen“. In der Zeile darunter folgt Ihr Netto-Gehalt. Unterhalb davon definieren Sie ein Feld für Zusatzleistungen, beispielsweise Weihnachts- oder Urlaubsgeld. Natürlich können Sie dort auch Prämien oder Gratifikationen eintragen. Ist die Auswahl möglicher Zusatzleistungen begrenzt, sollten Sie mit einem Schalter-Element arbeiten. Dafür schreiben Sie zunächst in drei beliebige Zellen außerhalb der Matrix untereinander „Urlaubsgeld“ und „Weihnachtsgeld“. Blenden Sie dann die Formularleiste über „Ansicht“, „Symbolleisten“, „Formular“ ein. Klicken Sie dort auf das Icon „Kombinationsfeld“ und ziehen Sie per Maus den Schalter über der Zelle A5 auf. Über das Kontextmenü suchen Sie dann den Befehl „Steuerelement formatieren“. Im folgenden Dialog geben Sie im Listenbereich die Zellen an, die die Einträge „Weihnachtsgeld“ und „Urlaubsgeld“ enthalten. Oberhalb der beiden Textzellen sollten Sie eine leere Zelle mit einschließen. Das hat den Vorteil, dass das Schalter-Element nicht ständig mit einem der beiden Einträge belegt ist. Zunächst bleibt das Kombinationsfeld leer, und erst auf Ihren Mausklick erscheinen die beiden Meldungen. Unter „Ausgabeverknüpfung“ tragen Sie die Zelle ein, auf die Sie das SchalterElement gelegt haben, in diesem Beispiel A5. Schließlich aktivieren Sie noch das Kästchen „3D-Effekt“. Der Schalter funktioniert bereits. Mit den unterschiedlichen Ausgabewerten können Sie aber noch nicht rechnen. Die Ausgabewerte sind: „1“ für „leer“, „2“ für „Urlaubsgeld“ und „3“ für „Weihnachtsgeld“. Mit einer etwas komplizierten WENN-Abfrage koppeln Sie diese Einträge gleich mit den passenden Summen. Schreiben Sie in die Zelle neben den Schalter die folgende Formel: "=WENN(A5=1;"";(WENN(A5=2; 2000;3000)))" Diese Formel prüft, welcher Ausgabewert in der Zelle A5 steht. Bei einer „1“ gibt es keinen Eintrag in der Nachbarzelle, bei einer „2“ folgt „2000“, bei einer „3“ 3000. Diese Beträge müssen Sie na-

So

4

sieht

das fertige

Arbeitsblatt mit Übernahme der Summen aus den anderen Arbeitsblättern aus.

Mit der

5

Beding-

ten Formatie-

heben Sie automatisch Zellwerte optisch hervor, wenn sie bestimmte Werte überoder unterschreiten. rung

türlich an Ihre individuellen Bezüge anpassen. Abschließend formatieren Sie diese Zelle als Währungszelle mit DMBeträgen. Weiter unten im Tabellenblatt legen Sie eine Matrix für die monatlichen Einnahmen sowie die zuvor summierten Ausgaben der zwölf Monate an. In einer dritten Spalte bestimmten Sie die Differenzen, indem Sie für jeden Monat die Ausgaben von den Einnahmen abziehen. Um die summierten Ausgaben aus dem vorangehenden Blatt zu übernehmen, schreiben Sie als Zell-Adresse etwa „='Summe Ausgaben'!L29“. „Summe Ausgaben!“ steht dabei für das Arbeitsblatt, „L29“ gibt den Zellbezug an. Wiederholen Sie dies für alle Monate. 5

Negativen Kontostand kenntlich machen

Falls Ihr Kontostand mal ins Minus rutscht, sollte Ihr Sheet das sofort optisch hervorheben. Dafür hat Excel ein hervor-

ragendes Instrument zu bieten, die Bedingte Formatierung. Klicken Sie zunächst auf die Zelle, die bei bestimmten Werten ihr Aussehen verändern soll, in diesem Fall die Differenz aus Einnahmen und Ausgaben in Zelle D20. Starten Sie das Format-Werkzeug über „Format“, „Bedingte Formatierung“. Bei „Bedingung 1“ definieren Sie die Optik, wenn das Budget am Ende des Monats über „0“ liegt. Neben „Zellwert ist“ suchen Sie im benachbarten Schalter den Listeneintrag „größer als“. Rechts daneben tragen Sie nur die Zahl „0“ ein (ohne Anführungszeichen!). Über „Format“ definieren Sie, wie die Zahl erscheinen soll, etwa im Fettdruck und in der Farbe „Schwarz“. Unter „Bedingung 2“ sollte in den Feldern stehen: Zellwert ist kleiner als „0“. Über den Button „Format“ bestimmen Sie hier, dass der Text in Rot erscheinen soll, um auf den negativen Kontostand aufmerksam zu machen. k

CHIP | WORKSHOP

90

6

Makro: Frühzeitige Budgetwarnung

Allerdings kann die Ergebniszelle in D20 lediglich Hiobsbotschaften überbringen, indem es die negativen Werte rot einfärbt. Mit einem kleinen Makro hingegen können Sie sich warnen lassen, bevor Ihr Konto in die Miesen rutscht. Die Aufgabe: Sie wollen ein Programm schreiben, das einen Zellwert auf dessen Betrag hin überprüft, um gegebenenfalls eine Warnmeldung auszugeben. In diesem Beispiel soll das Makro die Summe der November-Ausgaben im Arbeitsblatt „Summe Ausgaben“ in der Zelle „L29“ in Erfahrung bringen. Starten Sie dazu über die Tastenkombination [Alt]+[F11] den Visual Basic Editor. Klicken Sie auf das Icon für „Code anzeigen“ unterhalb der Zeile „Projekt VBA-Projekt“. Im rechten Fenster öffnet

sich unter dem Titel „Haushaltsbuch.xls“ ein neues Eingabefeld. An dieser Stelle tragen Sie nun Zeile für Zeile den folgenden Programmcode ein: Sub Schaltfläche_BeiKlick() Sheets("Summe Ausgaben"). t Activate

Zur Erklärung: Hinter „Sub“ folgt der Name des Makros, in diesem Beispiel „Schaltfläche_BeiKlick()“. Der Name deutet bereits an, dass das Makro später mit einer Schaltfläche im Sheet verknüpft wird. Sie können aber auch einen beliebigen anderen Namen wählen. Die beiden Anweisungen

Range("L29").Select Dim Budget

Sheets("Summe Ausgaben"). t

Budget = Application.Cells t

Activate

(29, 12)

Range("L29").Select

Select Case Budget Case 0 Case Is 3000 MsgBox

"Budget

überschritten!"

Dim Budget

End Select

Budget = Application.Cells

End Sub

(29, 12)

Der

6

VBA-Pro-

im Modul sieht so aus. Achten Sie auf die exakte Übernahme der Interpunktion. grammtext

definieren Sie die Zelle in Zeile 29, Spalte 12 zur „Budget“-Zelle. Das muss Excel wissen, um den Abgleich in der korrekten Zelle vornehmen zu können. Dann folgt die eigentliche Fallunterscheidung: Select Case Budget Case 0 Case Is 3000 MsgBox

Mit dem

6

Makro

überprüfen Sie zunächst den Zellwert und lassen Excel dann eine Messagebox mit einem Hinweis ausgeben.

CHIP | WORKSHOP

"Budget

überschritten!"

Liegt der Betrag in der Zelle unterhalb von 3.000, folgt die Dialogmeldung „Alles im grünen Bereich“, andernfalls erscheint die Warnung „Budget überschritten“. Die Texte in den Anführungszeichen können Sie selbstverständlich nach Belieben ändern. Mit End Select End Sub

beenden Sie zunächst die Fallunterscheidung, später auch das Makro. Speichern Sie und wechseln Sie vom VBA-Modus zurück zum Arbeitsblatt. Ziehen Sie aus der Symbolleiste für Formulare im Arbeitsblatt „Summe Ausgaben“ eine Schaltfläche an einer passenden Stelle auf. Sofort öffnet sich der Dialog „Zuweisen“. Wenn Sie alles richtig gemacht haben, steht unterhalb

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

91

» SERVICE

Excel-Makros

7

Mit Diagrammen können Sie die Kostenentwicklungen schnell visualisieren.

von „Makros“ lediglich ein einziger Eintrag, nämlich „Schaltfläche_BeiKlick“. Den bestätigen Sie mit „OK“. Klicken Sie danach zweimal nacheinander in das Schalter-Element, und der Cursor blinkt hinter der Beschriftung. Nun können Sie den Text des Schalters anpassen. In diesem Fall wäre „BudgetPrüfung November“ die passende Beschriftung des Buttons. Über das Kontextmenü und den Befehl „Steuerelement formatieren“ haben Sie aber auch später Gelegenheit, die Optik des Schalters zu ändern. Wenn

7

Über die Toolbox am oberen Rand verändern Sie den Diagrammtyp oder färben wichtige Werte ein.

Sie fertig sind, klicken Sie an eine beliebige Stelle im Sheet und betätigen dann den Schalter. Wenn die Meldung „Alles im grünen Bereich“ erscheint, haben Sie alle Anweisungen korrekt umgesetzt. 7

Diagramme einfügen

Je mehr sich Ihre Tabellen füllen, desto schwieriger wird es, die Übersicht über die einzelnen Posten zu behalten. Ein Diagramm gibt über die laufenden und bereits getätigten Ausgaben besseren Aufschluss. Reservieren Sie ein weiteres Arbeitsblatt nur für diese Grafik. In die-

EXCEL-TIPP

» Die Spaltentitel fixieren Wenn Sie im Arbeitsblatt „Ausgaben“ ren“. Sowohl die Posten in der ersten Zeile mehrere Monate lang Tag für Tag Einga- als auch die Zeitwerte in den ersten beiben vorgenommen haben, wächst das den Spalten sind nun ständig sichtbar. Sheet in die Länge. Die Gefahr dabei: Sie sehen die Spaltentitel nicht mehr und finden sich im Zellengewirr nur noch schwer zurecht. Das verhindern Sie, indem Sie bestimmte Bereiche fixieren. In diesem Beispiel bleiben die oberste Zeile sowie die ersten beiden Spalten stets sichtbar, egal welche Zelle im Blatt Sie ansteuern. Und das geht so: Markieren Sie im Blatt „Ausgaben“ den Zellbereich Wenn Sie Zeilen fixieren, bleiben die Spaltentitel C2 bis J2. Wählen Sie dann auch beim Herunterscrollen stets sichtbar. den Befehl „Fenster“, „Fixie-

sem Beispiel heißt das Arbeitsblatt „Chart Ausgaben November“. Um Kostenentwicklungen zu visualisieren, sollten Sie bevorzugt mit Kurven-Diagrammen arbeiten. Aber auch Balken-Diagramme können „Kostenfresser“ schnell identifizieren. Starten Sie den Diagramm-Assistenten über „Einfügen“, „Diagramm“. Unter „Diagrammtyp“ entscheiden Sie sich in diesem Beispiel für „Typ 3 Linie“. Im rechten Feld aktivieren Sie ein beliebiges Modell und klicken auf „Weiter“. Im zweiten Schritt müssen Sie den Datenbereich definieren. Dazu klicken Sie auf das kleine farbige Quadrat mit dem roten Diagonalpfeil. Der Dialog verkleinert sich auf einen schmalen Streifen. Wechseln Sie mit dem Maus-Cursor in das Blatt „Summe Ausgaben“ und markieren die Zellen A3 bis L27. Die oberen beiden Reihen im Sheet sollten Sie nicht einbeziehen, weil Excel sonst aus dem Spaltentitel „Monat“ einen eigenen (natürlich unsinnigen) Wert auf der XAchse erzeugt. Bestätigen Sie mit „Weiter“ und wählen Sie im weiteren Verlauf einen Diagrammtitel sowie passende Achsenbeschriftungen, etwa „Posten“ für die XAchse und „Betrag“ für die Y-Achse. Abschließend sollten Sie die Legende löschen, da sie inhaltlich wenig hergibt. Klicken Sie die Box mit der rechten Maustaste an und bestätigen Sie den Befehl „Markierung löschen“. Ischta Lehmann

CHIP | WORKSHOP

92

Hilfreiche Makros

Alle meine Ordner – Excel analysiert die Festplatte Sicher, auch der Windows Explorer zeigt die komplette Verzeichnisstruktur der Festplatte an – aber er druckt sie nicht. Ein passendes Excel-Makro dagegen informiert Sie nicht nur über sämtliche Ordner und Dateien, sondern kann die Platten-Analyse auch zu Papier bringen.

D

as Makro verbirgt sich in der Datei „Festplatte.xls“ und funktioniert mit Excel 97 und 2000. Es listet neben der Hauptordner-Ebene bis zu sieben Unterordner. Außerdem sehen Sie die Anzahl der Dateien sowie das älteste und neueste File des Ordners. 1

Das Makro starten

Laden Sie zunächst die Datei „Festplatte.xls“ von der Heft-CD. Sie sehen, dass nur die Zeilen 2 und 3 mit Oberbegriffen gefüllt sind. Das Makro behandelt diese Oberbegriffe als Spaltentitel und wird später die darunter liegenden Zellen füllen. Es gibt zwei Arten, das Makro zu starten. Die einfachste: Sie gehen auf „Extras“ und suchen das Kommando „Tree“, das Sie ganz unten finden. Eleganter ist der Start über ein eigenes Icon. Dazu wählen Sie den Menübefehl „Ansicht“, „Symbolleisten“, „Anpassen“. In der Registerkarte „Befehle“ aktivieren Sie den Eintrag „Makros“. Ziehen Sie das Symbol mit dem Smiley im rechten Fenster mit der Maus auf eine beliebige Stelle in der Symbolleiste. Klicken Sie dann mit der rechten Maustaste auf „Makro zuweisen“. Im folgenden Dialog wählen Sie den Eintrag „Create Tree“ aus und bestätigen mit „OK“. Künftig können Sie das Makro mit einem Mausklick auf das neue SmileySymbol in der Menüleiste starten. 2

Arbeitsblatt konfigurieren

Eines vorweg: Es ist nicht sinnvoll, dieses Makro in andere Arbeitsblätter einzubinden, obwohl das technisch sehr wohl möglich wäre. Dann aber müssten Sie im Visual-Basic-Modul unbedingt die SheetBezüge ändern, also den Namen der Ar-

CHIP | WORKSHOP

Sie können alle Makros mit kleinen Symbolen belegen und in der Menüzeile als Icon verankern. Dazu benötigen Sie den Menübefehl „Ansicht“, Symbolleisten“, „Anpassen“. In der Registerkarte „Befehle“ aktivieren Sie den Eintrag „Makros“.

1

Die

2

Zellen

der Spaltenüberschrif-

können Sie nach Belieben ändern, ohne Laufzeitfehler beim Makro zu riskieren. ten

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

93

» SERVICE

Excel-Makros

beitsmappe anpassen, auf den das Makro Bezug nimmt. Das Makro ist so geschrieben, dass es nur in der Datei „Festplatte.xls“ im Arbeitsblatt „Verzeichnisstruktur“ funktioniert. Die Spaltenüberschriften in den Zeilen 1 bis 2 sind allerdings nicht Bestandteil der Rechenprozedur, und die Titel sind nicht im Makro verankert. Hier können Sie nach Belieben die Zellen und Zeilen formatieren, den Schrifttyp ändern oder die Bezeichnungen austauschen. Achten jedoch nach Abschluss aller Änderungen darauf, dass Sie keinen neuen Dateinamen vergeben. 3

Das

4

Makro

liest die Festplatte aus

und übergibt die Inhalte in die leeren Zellen. Diese werden mit der „StartCell. Offset“-Anweisung vorformatiert.

Interaktive Anweisungen: Message-Boxes

Das Makro startet nach dem Aufruf mit einem Dialogfeld, einer so genannten Message-Box. Die trägt den Titel „Verzeichnisstruktur in Excel“. Die zugehörige Aufforderung lautet: „Geben Sie den Laufwerksbuchstaben ein“. Als Ausgangspfad, als so genannter Default-Path, ist im Makro der Laufwerksbuchstabe „C:“ eingetragen. Diese Voreinstellung können Sie ändern. Wählen Sie „Extras“, „Makro“, „Makros“. Im Dialogfenster „Makro“ aktivieren Sie den Eintrag „Create Tree“ und klicken dann rechts auf „Bearbeiten“. Nun öffnet sich der Visual-Basic-Editor. Das zu bearbeitende Makro heißt „Create Tree“ und beginnt mit der Zeile „Sub Create Tree ()“. In der vierten Zeile wird der Ausgangspfad mit dem Befehl „DefaultPath“ definiert. Hier finden Sie die Voreinstellung „C:\“. Überschreiben Sie diesen Buchstaben mit einem anderen, schlägt Excel künftig im ersten Dialog ein anderes Laufwerk vor.

Das

5

liest nicht

nur Dateien ohne Windows-Attribute, sondern auch versteckte Files und Systemdateien aus.

werden über die Dim-Anweisungen die Variablen definiert, etwa die folgenden Schleifen. Unterhalb des Kommentars „Start new directory entry on spreadsheet“ beginnt das Makro, die von der Platte ausgelesenen Infos in die leeren Zellen zu übernehmen. Die „StartCell.Offset-Anweisungen“ etwa formatieren die zu füllenden Zellen als Zahlen- oder Datumszellen. 5

Auslesen versteckter Files und weitere Anweisungen

Das Makro ist so gestaltet, dass auch so genannte versteckte Files gelistet werden. Die Ausgabe in Das sind Dateien, denen Windows Attri4 formatierte Zellen bute gibt, um sie vor dem Löschen oder Bevor das Makro die Anweisungen zum Überschreiben zu schützen. In diesem Beispiel sorgt die Zeile Auslesen der Ordner und Dateien erhält, „ThisFile = Dir(PathName & "*.*", vbNormal + vbHidden + vbSystem)“ dafür, dass neben versteckten Dateien (vbHidden) auch Systemdateien (vbSystem) aufgezählt werden. mit einer Dialogbox, die den Anschließend folgen 3 gewünschten Laufwerksbuchstaben abfragt. im Programmtext AnDas Makro startet

Makro

Wenn Sie zweimal nacheinan-

6

, fragt dieser Dialog, ob Sie abbrechen wollen. der Escape drücken

weisungen zum Auslesen der ältesten und neuesten Datei. Unterhalb des Kommentars „Store names of recursive subdirectories“ folgen die Befehle zum Auslesen und Listen der Unterordner. 6

Das Makro unterbrechen

Wenn Sie nach Makro-Start zweimal die Escape-Taste drücken, erscheint ein weiterer Dialog mit der Frage, ob Sie „Fortfahren“ wollen. Hier können Sie mit dem Button „Abbrechen“ die gesamte Prozedur beenden. Dieser Dialog ist im Makro unter „Error-Handler“ festgeschrieben. Die „Case“-Anweisungen sorgen dafür, dass je nach der Entscheidung des Anwenders die Berechnung abgebrochen oder an der aktuellen Stelle fortgesetzt wird. Ischta Lehmann

CHIP | WORKSHOP

94

Aus Excels Trickkiste

Wem die Stunde schlägt – Excels Zeitansage Ein besonders originelles Beispiel für die Makro-Programmierung in Excel ist die eingebundene analoge Uhr, die per Mausklick startet. Wie das Makro funktioniert, wie Sie andere Zeitzonen einstellen und wie Sie Zeiger und Zifferblatt individuell gestalten, beschreibt dieser Beitrag.

W

ider Erwarten haben Sie es hier nicht mit einem ellenlangen Makro zu tun. Denn Excels Zeitansage basiert nicht auf VBA-Anweisungen, sondern auf einem ausgefeilten Diagramm mit einigen mathematischen Funktionen (Sinus und Cosinus). Die Ausgangszahlen zu Wellenberechnungen liefert die Funktion JETZT() in der Zelle A23. Diese Zelle wurde mit dem Bereichsnamen „NowVal“ besetzt. Aus den Wellen erzeugt Excel dann scheinbar eine kreisrunde Uhr, die im Sekundentakt die Zeit aktualisiert. Das ist aber genau genommen eine kleine Täuschung. Denn Excel berechnet lediglich das Diagramm aus den Werten in den Zellen A22 bis C43. Für die permanente Aktualisierung sorgt das Makro mit einer Schleife.

1

Das kleine Makro mit der Schleifenanweisung „Loop“ dient lediglich dazu, das Uhren-Diagramm im Arbeitsblatt „Clock“ pausenlos zu aktualisieren.

PROFI-TIPP

1

» Eine Alarm-Funktion einbinden Mit einer kleinen Formel verwandeln Sie das Sheet sogar in einen Wecker. Allerdings „piept“ Excel nicht, sondern blendet eine Zelle ein, sobald ein bestimmter Zeitwert erreicht ist. Angenommen, Sie brauchen in etwa 30 Minuten den Hinweis, dass Sie nun Ihre Arbeit beenden sollen. Dazu formatieren Sie zunächst eine (leere) Zelle neben dem Diagramm mit einem großen Schrifttyp und einer auffälligen Hintergrundfarbe, etwa Rot. Tragen Sie dann folgende Formel ein: =WENN(B24>=30;"Aufhören!";"") Erklärung: Zelle B24 enthält den aktuellen Minutenwert. Sobald dieser bei „30“ oder darüber liegt, erscheint automatisch die Meldung „Aufhören!. Vorher passiert nichts.

CHIP | WORKSHOP

Wie das Uhr-Makro funktioniert

Öffnen Sie den Quellcode über „Extras“, „Makro“, „Makros“. Im Dialog „Makro“ aktivieren Sie den einzigen Eintrag „ClockVBA“ und klicken auf „Bearbeiten“. Nun erscheint der Programmcode: Die Anweisung „Application.EnableCancelKey = xlEr-rorHandler“ erlaubt dem Anwender, die Prozedur mit der [Esc]-Taste zu unterbrechen. Mit „Active Sheet.Calculate“ beginnt der Rechenprozess. „Loop“ heißt soviel wie „Schleife“, so dass die Prozedur nach der Beendigung wiederholt wird. So lässt sich das Diagramm pausenlos aktualisieren. 2 Die Funktion zur Ausgabe des

2

Stundenwertes lautet in diesem Beispiel STUNDE(NowVal).

Berechnungen ausblenden, Weltzeit einstellen

Im Zellenbereich zwischen A23 und C42 liegen sämtliche Werte, die das Diagramm braucht, um im Zusammenhang

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

95

» SERVICE

Excel-Makros

mit dem Makro ständig die Uhrzeit zu ermitteln und optisch darzustellen. Für die korrekte Position sämtlicher Uhrzeiger sorgen mehrere Pi-, Sinusund Cosinus-Funktionen. Sie sollten diese Funktionen keinesfalls löschen oder überschreiben. Wenn Sie das hektische Zahlenspiel stört, färben Sie die Texte über das Icon „Schriftfarbe“ ganz einfach weiß, und die Zellen werden sofort unsichtbar. Allerdings können Sie mit einer minimalen Änderung auch ganz andere Zeitzonen einstellen. Wollen Sie beispielsweise wissen, was die Stunde in Las Vegas geschlagen hat? Kein Problem: Korrigieren Sie die Funktion in Zelle A24 „=STUNDE(NowVal)“ in dieser Form: "=STUNDE(NowVal)-9“, und das UhrenDiagramm ändert sich sofort. 3

Uhrzeiger verändern

Die Parameter für den Minutenzeiger liegen in den Zellen B27, B28, C27 und

die Uhr

lässt sich wie jedes ExcelDiagramm ändern. An dieser Stelle wird die Farbe der Zeichnungsfläche angepasst.

Optisch

3

aufge-

Die spartanische Excel-Uhr erhält mit dem passenden Hintergrund eine persönliche Note. wertet:

Diagrammfläche bearbeiten

Zunächst erinnert die Uhr überhaupt nicht an ein Excel-Diagramm. Das ändert sich, wenn Sie bei gedrückter [Strg]-Taste in das Schaubild klicken. Die Uhr erhält dann sofort den für Diagramme typischen Rahmen, mit dem Sie die Grafik vergrößern oder neu platzieren können. Rufen Sie anschließend über das Kontextmenü den Befehl „Diagrammobjekt bearbeiten“ auf, und die Zeichnungsfläche wird mit einem Rahmen hervorgehoben. In der Diagramm-Toolbox klicken Sie dann auf das Icon „Zeichnungsfläche formatieren“. Über den Button „Fülleffekte“ verpassen Sie dem Zeigerblatt einen neuen Anstrich. Mehr zum Thema „Diagramme formatieren“ lesen Sie ab s 22 in diesem Sonderheft. Außerdem sollten Sie die Hinweistexte zum Starten und Stoppen der Uhr optimieren. Dazu öffnen Sie wieder das Kontextmenü und wählen den Befehl „Diagrammobjekt bearbeiten“. Klicken Sie dann auf den Textrahmen für einen der beiden Einträge. Über die SchriftWerkzeuge verbessern Sie das Erscheinungsbild der Anweisungen. Ein Tipp: Die Uhr bekommt eine unverwechselbare Note, wenn Sie über „Grafik auswählen“ in der Registerkarte „Fülleffekte“ ein Bild als Hintergrund in die Zeichnungsfläche einbinden. 4

Auch

3

Der Mi-

4

nuten-

ist nichts anderes als eine variable Datenreihe. Daher können Sie über die DiagrammToolbox problemlos optische Korrekturen vornehmen. zeiger

C28. In der Diagramm-Toolbox sind diese Parameter als „Reihe2“ niedergelegt. Nach dem Markieren des Zeigers ändern Sie dessen Optik über das Kommando „Datenreihen formatieren“. An dieser Stelle legen Sie eine neue Farbe fest und geben dem Zeigerende auf Wunsch auch

eine auffällige Markierung. Wichtig ist noch, dass Sie die Voreinstellung in der Registerkarte „Datenbeschriftung“ nicht ändern. Wäre „Werte anzeigen“ aktiv, würde Excel die Uhr mit wenig aussagekräftigen Kommazahlen versehen. Ischta Lehmann

CHIP | WORKSHOP

96

1

Nach einem Klick auf den Button „Regeln“ erscheint eine Message-Box mit einer kurzen Erklärung des Spielverlaufs.

3

Die „Sub“-Anweisungen des Makros haben eigene Titel, wie in diesem Beispiel „aufmalen“.

Pausenspiele mit Excel

„Vier gewinnt“ im Excel-Sheet Eine kleine Spielpause während der Arbeit mit Excels Zahlenwerk tut gut – umso mehr, als Sie bei der Gelegenheit auch Ihre Programmierkenntnisse erweitern können. Dieser Mini-Workshop zeigt Ihnen, wie Sie mit Makro-Unterstützung auf einem Excel-Sheet „Vier gewinnt“ spielen.

D

as beliebte Steckspiel „Vier gewinnt“ können Sie auch mit Excel spielen. Alles, was Sie dazu brauchen, ist die Datei „4gewinnt.xls“ sowie einen Spielpartner. Wie das Makro und seine Bestandteile funktionieren und wie Sie das Spiel starten, zeigen die folgenden Schritte.

1

Wie das Programm abläuft

Starten Sie die Datei „4gewinnt.xls“ von der Heft-CD. Das Makro sorgt dafür, dass Sie sofort zum dritten Arbeitsblatt, dem „Spielfeld“, gelangen. Im Bereich A1:G6 sehen Sie die Spiele-Matrix mit den grau unterlegten Feldern. Daneben finden Sie zwei Buttons, die per Mausklick MakroAnweisungen starten. Hinter „Regeln“ verbirgt sich eine Message Box mit dem Titel „Spielregeln“ und einer kurzen Erklärung des Spielablaufs. Klicken Sie auf „Spielstart“, beginnt das komplett dialoggesteuerte Game. Das Makro gibt Ihnen also ständig Anweisungen, welcher Schritt zu tun ist, etwa die Spielernamen einzugeben oder einen Spielstein in eine Reihe (Spalte) zu legen.

CHIP | WORKSHOP

Schafft es ein Spieler, vier Steine hintereinander in einer Reihe zu platzieren, folgt eine Gewinnmeldung mit dem Namen des Siegers. Danach ermittelt eine Zählanweisung im Makro, wie viele Runden beide Spieler benötigt haben, und nennt das Ergebnis in einem Nachrichtenfeld. Zuletzt wird die Gewinnreihe im Spielfeld mit einem Muster gefärbt. 2

den Buttons liegen, ändern Sie damit allerdings nicht. Auf die gleiche Weise editieren Sie, falls gewünscht, den Dialog im zweiten Arbeitsblatt „Dialog2“.

Anweisungstafeln bearbeiten

Sämtliche Hinweistafeln (Message Boxes) können Sie editieren, ohne die Funktionsfähigkeit des Makros zu gefährden. Den Dialog mit der Abfrage der Spielernamen finden Sie im ersten Arbeitsblatt mit dem Titel „Dialog1“. Klicken Sie das Modul zweimal nacheinander an (kein Doppelklick!). Sie können nun die Kästen für die Buttons verschieben, vergrößern oder verkleinern. Wenn Sie in die Textfelder klicken, verwandelt sich das Feld in eine Eingabezeile. Sie ergänzen den vorhandenen Text nach Belieben, überschreiben oder löschen ihn. Die Funktionen, die hinter

Mit einer Zählanweisung lässt sich ermitteln, wie viele Versuche beide Spieler gebraucht haben.

1

Das Dialogfeld im ersten Arbeitsblatt können Sie editieren, etwa um die Texte in den Buttons zu ändern.

2

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE »

LÖSUNGEN

97

» SERVICE

Excel-Makros

3

3

Die Anweisungen unterhalb von „Sub aufmalen ( )“ sind für die optische Gestaltung des Spielfeldes zuständig.

Das Spielfeld gestalten

Bevor Sie das Visual-Basic-Modul in Excel über „Extras“, „Makro“, „Makros“ und „Bearbeiten“ öffnen, stoßen Sie zunächst scheinbar auf eine ganze Batterie voller Makros. Aber die hängen natürlich alle zusammen. Deswegen reicht es aus, im Dialogfeld „Makro“ einen beliebigen Eintrag zu aktivieren, dann „Bearbeiten“ zu wählen und im folgenden VBA-Editierfeld zum passenden Abschnitt zu scrollen. Wenn Sie allerdings wissen, welchen Titel die so genannte „Sub“-Anweisung trägt, können Sie bereits im „Makro“Dialogfeld von Excel den passenden Abschnitt gezielt ansteuern. In diesem Beispiel wollen Sie das Spielfeld neu einfärben. Die Anweisungen dazu verbergen sich hinter dem Makro-Namen „aufmalen“. Aktivieren Sie den Eintrag und klicken Sie dann auf „Bearbeiten“. Die Anweisungen für die Gestaltung des Spielfeldes beginnen mit der Definition des passenden Arbeitsblattes und des Zellbereichs für das Spielfeld („Select.Range“). Im weiteren Verlauf bestimmt das Makro die Art und die Platzierung der Rahmen mit einer ganzen Reihe von „Selection.Borders“-Anweisungen. Deren Werte sollten Sie möglichst nicht ändern. Scrollen Sie statt dessen nach unten bis zum Eintrag: Range("A1:g6").Select Selection.ClearContents With Selection.Interior .ColorIndex = 15

4

Wenn der Spieler eine falsche Spalte für seinen Zug wählt, gibt das Makro eine launige Fehlermeldung aus.

Die Zahl 15 steht für die Farbe Grau. Wenn Sie statt dessen ein frischeres Gelb bevorzugen, tragen Sie an dieser Stelle die Zahl „19“ ein. 4

So lässt Excel die Steine in die Reihen fallen

Beim Brettspiel lassen Sie die Spielsteine in enge Kanäle fallen. Excel setzt dieses Prinzip digital um, als Kanäle dienen die Spalten von A bis G. Wenn Sie beispielsweise aufgefordert werden, Ihren nächsten Zug mitzuteilen, startet Excel im Hintergrund einen Abschnitt des Makros, der mit dem oben genannten Dialogfeld verknüpft ist. Anhand einer „If“-Anweisung übersetzt es dabei die Buchstaben der Spalten in VBA-verständliche Zahlen und macht damit klar, in welcher Spalte welche Zelle eingefärbt werden muss, um den Zug zu visualisieren. Und falls Sie einen Buchstaben eintippen, der im Alphabet hinter G steht, reagiert Excel mit der Bemerkung: „Vielleicht sollten wir das Alphabet noch ein wenig üben...“

Steine in eine Reihe gesetzt hat. Und diese Prüfroutine wiederholt sich mit jedem neuen Zug. Bei diesem Vorgang wird der Spielstatus über vier Routinen gecheckt: „Prüfen waagerecht“, „Prüfen senkrecht“ sowie zwei Prozeduren zum Prüfen der diagonal benachbarten Zellbereiche („prüfenwaagerecht 1; prüfensenkrecht 1; prüfenluro 1; prüfenrulo 1“). Die Zahl „1“ hinter den Routinen steht für den „Spieler Nummer eins“. Hat der Partner einen Stein gesetzt, wiederholt sich natürlich die Routine. Die Prüfschleifen agieren über ein Suchverfahren: Ermittelt wird immer der letzte Stein derselben Farbe in einer Reihe, dann werden die Steine addiert und schließlich abgeglichen, ob bereits ein Spieler gewonnen hat. Ischta Lehmann

Wie das Makro die Steine zählt 5

Excel braucht zahlreiche Prüfroutinen, um zu ermitteln, ob ein Spieler bereits vier

5

Die Prüfroutinen untersuchen nach jedem Zug, ob ein Spieler bereits vier Steine in eine Reihe gebracht hat.

CHIP | WORKSHOP

98

EXCEL-TUNING » CHARTS » FUNKTIONEN » FORMELN/BEZÜGE » LÖSUNGEN »

SERVICE Vorschau

DAS NÄCHSTE SONDERHEFT AB 20. NOVEMBER

CHIP Mobile

1.111 Hits für PDAs Die PDA-Szene gerät in Bewegung: Während Psion die Segel streicht, muss Palm neuerdings um die Marktführerschaft bangen, nachdem die Pocket-PCs mit Windows CE kräftig Boden gutgemacht haben. In der dritten Ausgabe von CHIP-Mobile stellen wir Ihnen alle aktuellen PDAs, die ersten Smartphones und interessantes Zubehör vor. Dazu gibt es Infos zum brandneuen Betriebssystem „Pocket PC 2002“ von Microsoft. Ein Highlight ist einmal mehr der Software-Katalog mit PDA-Programmen aus den Kategorien Business, Hobby, Tools und Spiele. Von den 1.111 Programmen für Palm-OS und Windows CE, die Sie auf der Heft-CD finden, werden die besten ausführlich im Heft beschrieben.

TOP-THEMEN IM ÜBERBLICK 3

Marktübersicht: Aktuelle PDAs für Palm-OS und Windows CE, die ersten Smartphones, interessantes Zubehör

3

Praxis: Workshops und PDA-Tipps

3

Software-Katalog: Die besten Programme für Palm und Windows CE