Excel-Tipps: Diagramme trotz fehlender Werte zeichnen
Problem:
Sie wollen eine Datenreihe in Excel als Diagramm darstellen. Allerdings fehlen einige Werte, und die Diagrammlinie ist daher unterbrochen. Die Excel-Tabellenkalkulation soll die fehlenden Werte selbst ergänzen.
Lösungs-Tipps:
Standardmäßig ignoriert die Excel-Tabellenkalkulation leere Zellen und zeichnet daher für diese auch keine Diagrammlinie. Sie können aber die Einstellung in Excel so ändern, dass entweder eine Nullstelle erscheint oder die Linie verbunden wird.
Tipp für Excel 2003: Hier nehmen Sie die Einstellung global für alle Arbeitsblätter vor. Gehen Sie auf „Extras, Optionen“ und die Registerkarte „Diagramm“. Hinter „Leere Zellen“ stehen drei Optionen bereit: „werden nicht geschrieben (übersprungen)“, „Werden als Nullwert gezeichnet“ und „werden interpoliert“. Die Einstellung können Sie hier für jede Arbeitsmappe einzeln vornehmen. Dazu wählen Sie das Excel-Diagramm aus und öffnen „Entwurf, Daten auswählen“. Klicken Sie auf „Ausgeblendete und leere Zellen“, und aktivieren Sie dann die Option „Null“ oder „Datenpunkte mit einer Linie verbinden“.
Calc: Bei der Tabellenkalkulation aus OpenOffice.org wählen Sie eine Linie in einem Diagramm aus und gehen im Kontextmenü auf „Objekteigenschaften“. Auf der Registerkarte „Optionen“ können Sie sich für „Lücke lassen“, „Null annehmen“ oder „Linie fortsetzen“ entscheiden.
Dieser Artikel basiert auf einem Beitrag der CW-Schwesterpublikation PC-Welt.
Nur Zellen mit Formeln schützen
Problem:
Excel bietet einen Blattschutz. Dieser wirkt zwar nicht bei Hacker-Angriffen, kann aber gut versehentliche Eingaben abfangen. Standardmäßig blockiert der Schutz allerdings das ganze Blatt.
Lösung:
Wenn Sie mit Excel eine komplexe Arbeitsmappe mit vielen Formeln und eventuell sogar Makros erstellt haben, dann möchten Sie sicher sein, dass die Formeln nicht versehentlich verändert werden. Zellen ohne Formeln sollen dabei aber zugänglich bleiben. Wir zeigen Ihnen, wie Sie den Blattschutz von Excel gezielt auf die wirklich schützenswerten Zellen beschränken.
Schritt 1: Zunächst müssen Sie das Zellformat „Gesperrt“ – das standardmäßig gesetzt, aber nicht aktiv ist – auf dem ganzen Blatt entfernen. Markieren Sie also mit "Strg-A" das Blatt: Dann öffnen Sie mit "Strg-1" den Dialog zur Zellenformatierung, gehen dort auf die Registerkarte„Schutz“, deaktivieren die Klickbox vor„Gesperrt“ und bestätigen mit „OK“.
Schritt 2: Nun markieren Sie alle Zellen, die Formeln enthalten. Dazu öffnen Sie mit "Strg-G" den Dialog „Gehe zu“. Klicken Sie dort auf „Inhalte…“ und aktivieren Sie die Option „Formeln“.
Sschritt 3: Aktivieren Sie die Klickbox „Gesperrt“, die Sie in Schritt 1 deaktiviert haben. So setzen Sie das Zellenformat nur für die Formel-Zellen.
Schritt 4: Zuletzt aktivieren Sie den Blattschutz . Unter Excel 2000-2003 geht das über „Extras, Schutz“. In Excel 2007 wählen Sie „Überprüfen, Blatt schützen“. Falls die gesperrten Zellen zumindest markiert und kopiert werden dürfen, lassen Sie die Einstellungen im „Blatt schützen“- Dialog, wie sie sind. Andernfalls deaktivieren Sie die Klickbox vor „Gesperrte Zellen auswählen“.
Formelauswahl in einer Zelle anlegen
Problem:
Wenn Sie in einem Tabellenblatt verschiedene Auswertungen neben- oder untereinander platzieren, geht schnell die Übersicht verloren. Übersichtlicher wäre es, sich die benötigte Berechnung wahlweise in ein- und derselben Zelle anzeigen zu lassen.
Lösung:
Mit einer Kombination aus einer Drop-down-Liste und definierten Formelnamen können Sie diese Aufgabe so lösen, dass Sie nur zwei Zellen benötigen.
Schritt 1: Geben Sie irgendwo im ungenutzten Bereich des Tabellenblatts, in dem sich die auszuwertenden Daten befinden, alle gewünschten Formeln untereinander als Text ein – ohne das einleitende Gleichheitszeichen. Diese Ausgangsliste sieht dann etwa so aus:
Summe(A:A)
Mittelwert(A:A)
Max(A:A)
Min(A:A)
…
Schritt 2: Markieren Sie die Zelle, in der die Drop-down-Liste für die Formelauswahl erscheinen soll, und wählen Sie „Daten, Gültigkeit“ (Excel 2007: „Daten, Datenüberprüfung“). Im Drop-down-Feld „Zulassen“ klicken Sie auf „Liste“, unter „Quelle“ geben Sie per Mausmarkierung die angelegte Formelliste an. Nach „OK“ im Dialog „Gültigkeitsprüfung“ (Excel 2007: „Datenüberprüfung“) ist die Drop-down-Liste angelegt
Schritt 3: Markieren Sie die Zelle mit der Drop-down-Liste und gehen Sie auf „Einfügen, Namen, Definieren“ (Excel 2007: Register „Formeln, Namen definieren). Vergeben Sie dort einen Namen wie „Multiformel“.
Schritt 4: Wiederholen Sie den Schritt 3 und vergeben im Dialog „Namen definieren“ einen weiteren Namen – etwa „Ergebnis“. Tragen Sie dieses Mal im Feld „Bezieht sich auf“ folgende Formel ein:
=Auswerten(Multiformel)
Der Name „Multiformel“ entspricht unserem Beispiel. Wenn Sie in Schritt 3 einen anderen Namen gewählt haben, tragen Sie diesen hier ein.
Schritt 5: Schließlich markieren Sie die Zelle, die das Ergebnis der Formelauswahl anzeigen soll, und tragen dort
=Ergebnis
ein, also den Namen, den Sie in Schritt 4 vergeben haben.
Benutzerdefinierte Funktionen in Excel
Problem:
Excel aktualisiert eingebaute Funktionen nach Zelländerungen automatisch. Dies möchten Sie auch für selbst erstellte Funktionen haben.
Lösung:
Während Excel eingebaute Funktionen wie „WENN“ oder „ANZAHL“ nach Zelländerungen automatisch aktualisiert, erscheint der aktuelle Wert einer benutzerdefinierten Funktion nur, wenn Sie die Zelle mit der Funktion explizit anklicken. Sie können erzwingen, dass Excel eine selbsterstellte „Function“ aus einem VBA-Modul automatisch aktualisiert, wenn Sie zu Beginn des Codes – also nach dem Funktionsnamen – diese Zeile hinzufügen:
Application.Volatile
Setzen Sie die Methode dosiert ein: Die Excel- Mappe wird davon deutlich langsamer.
Zeilenumbrüche per "Suchen und Ersetzen" einfügen
Problem:
Eine Zelle enthält eine Aufzählung von Elementen, die zum Beispiel durch Kommata voneinander getrennt sind. Um die Tabelle übersichtlicher zu gestalten, können Sie die Trennzeichen dazwischen durch einen Zeilenumbruch ersetzen.
Lösung:
Die Excel-Funktion "Suchen und Ersetzen" kann auch mit Sonderzeichen wie einem Zeilenumbruch umgehen. Anders als bei Word können Sie die Sonderzeichen aber nicht bequem auswählen. Sie müssen selbst wissen, wie Sie Excel dazu überreden, den Zeilenumbruch zu verwenden.
Öffnen Sie über "Bearbeiten, Ersetzen" (2007: "Start, Bearbeiten, Suchen und Auswählen, Ersetzen") das Fenster "Suchen und Ersetzen". Im Feld neben "Suchen nach" tragen Sie das Trennzeichen, beispielsweise ein Komma gefolgt von einem Leerzeichen ein. Im Feld "Ersetzen durch" drücken Sie die Tastenkombination <Strg>-<J>.
Lassen Sie sich nicht irritieren: Das Feld bleibt nämlich leer, nur der Cursor erscheint leicht verändert. Trotzdem können Sie den Suchvorgang starten, und Excel wird wie gewünscht das alte Trennzeichen durch einen Zeilenumbruch ersetzen. Derselbe Trick funktioniert natürlich auch umgekehrt, wenn Sie einen vorhandenen Zeilenumbruch durch ein anderes Trennzeichen ersetzen wollen.
Excel-Zellüberwachung sinnvoll nutzen
Problem:
Eine Alternative zur „Kamera“-Funktion bietet die Funktion „Formelüberwachung“ ab Excel XP. Diese Möglichkeit finden Sie unter „Extras,
Lösung:
Der Dialog „Überwachungsfenster“ bietet die Schaltfläche „Überwachung hinzufügen“, mit der Sie beliebig viele Zellen überwachen und anzeigen können. Auch hier sehen Sie eventuelle Änderungen der überwachten Zellen sofort. Zur Auswahl der gewünschten Zellen verwenden Sie am einfachsten die Maus. Der Vorteil des Überwachungsfensters: Ganz nach Bedarf können Sie die Anzeige über „Extras, Formelüberwachung, Überwachungsfenster anzeigen“ schnell ausblenden und auf demselben Weg wieder hervorholen – samt der Auswahl aller zu überwachenden Zellen. Das Überwachungsfenster hat andererseits eine Reihe von Nachteilen:
1. Bereiche, die aus mehreren Zellen bestehen, können Sie zwar auf einmal zur Überwachung hinzufügen, Excel löst sie aber im Überwachungsfenster in eine relativ schwer lesbare Liste von einzelnen Zellen auf.
2. Die Formatierungen der Ausgangszellen werden im Überwachungsfenster grundsätzlich nicht angezeigt.
3. Im Unterschied zur „Kamera“ kann das Überwachungsfenster nur Zellbereiche der aktuellen Arbeitsmappe wiedergeben, die Kamera fotografiert auch Zellen anderer Mappen. Für einen übersichtlichen Vergleich von verstreuten Excel-Daten ist daher die „Kamera“- Funktion meist vorzuziehen.
So drucken Sie Tabellenbereiche
Problem:
Sie möchten mehrere Bereiche eines Tabellenblattes drucken. Wenn Sie die entsprechenden Zellen markieren und dann beim Drucken die Option „Markierung“ auswählen, druckt Excel die Zellbereiche aber jeweils auf ein eigenes Blatt. Mit der richtigen Methode lässt sich das vermeiden.
Lösung:
Die Problemstellung ist weit verbreitet: Microsoft rät daher auf seiner Website, alle Zeilen und Spalten auszublenden, die für den Ausdruck nicht benötigt werden.
Das geht über „Hilfe und Anleitungen, Office 2003, Excel 2003, Drucken, Problembehandlung beim Drucken“. Wenn Sie dieselben Bereiche öfter ausdrucken wollen, ist dieser Weg aber viel zu umständlich. Und falls Sie etwa Bereiche ausgeben wollen, die sich in Zeilen oder Spalten überschneiden, funktioniert dieses Verfahren überhaupt nicht.
Wir empfehlen Ihnen stattdessen, die in unserem Tipp „Zellbereiche mit ‚Kamera‘ abbilden“ (siehe nächste Seite!) vorgestellte Methode auch für den Druck zu verwenden.
Stellen Sie mit Hilfe solcher verknüpften Bilder auf einem eigenen Tabellenblatt eine Druckansicht aller Tabellenbereiche zusammen, die Sie für den Ausdruck brauchen. Wichtige Voraussetzung für den Druck: Sorgen Sie dafür, dass im Kontextmenü unter „Grafik formatieren“ auf der Registerkarte „Eigenschaften“ die Option „Objekt drucken“ aktiviert ist. So können Sie in Zukunft ohne weitere Vorarbeiten bequem dieses Tabellenblatt mit der Übersicht ausdrucken – und zwar mit den jeweils aktuellen Daten und allen Formatierungen der Originalzellen.
Zellbereiche mit Kamera abbilden
Problem:
Um einen besseren Überblick über Ihre Excel-Tabelle zu bekommen oder bestimmte Werte schnell vergleichen zu können, benötigen Sie die Zusammenfassung eines Tabellenbereichs an einer anderen Stelle. Oft haben die Ausschnitte verschiedene Layouts mit unterschiedlichen Spaltenbreiten. Deswegen ist eine direkte Verknüpfung mit den Ausgangszellen nicht praktikabel, weil dabei die Zellformatierung nicht übertragen wird. Wir nennen einen Weg, wie sich dieses Problem umgehen lässt.
Lösung:
Eine elegante Möglichkeit, einen Tabellenbereich einschließlich Formatierungen an anderer Stelle anzuzeigen, bietet die versteckte Excel-Funktion „Kamera“. Anders als der Name vermuten lässt, fügt diese nicht etwa starre Abbilder von Zellbereichen ein, sondern passt die Zellinhalte und Formatierungen stets dynamisch an Änderungen der Ursprungszellen an.
Vorbereitung: Aktivieren Sie die „Kamera“, indem Sie sie in eine Symbolleiste einfügen. Öffnen Sie dazu „Ansicht, Symbolleisten, Anpassen“. Markieren Sie auf der Registerkarte „Befehle“ unter „Kategorien“ den Eintrag „Extras“, und ziehen Sie dann das Symbol des Befehls „Kamera“ in eine beliebige Symbolleiste.
Verknüpfte Bilder erstellen: Markieren Sie den gewünschten Zellbereich, und wählen Sie das „Kamera“-Symbol in der Symbolleiste. Mit der Maus klicken Sie an die Stelle in der Tabelle, an der Sie das verknüpfte Bild einfügen wollen. Einzige Einschränkung: Mehrere nicht zusammenhängende Zellbereiche lassen sich nicht auf einmal einbauen, da die Mehrfachmarkierung mit der <Strg>-Taste hier nicht funktioniert.
Direkter Weg: Benötigen Sie eine solche Verknüpfung nur einmalig, können Sie sich das Einfügen des neuen Icons in die Symbolleiste sparen und stattdessen direkt ein verknüpftes Bild einfügen. Markieren Sie dazu den Zellbereich, den Sie verknüpfen wollen, und kopieren Sie ihn in die Zwischenablage. Wechseln Sie dann zu dem Zellbereich, in dem Sie das Bild einfügen wollen. Er kann wie beim Einsatz des Kamera-Symbols auf einem anderen Tabellenblatt liegen. Während Sie das „Bearbeiten“- Menü öffnen, halten Sie die <Shift>-Taste gedrückt. Dadurch finden Sie in dem Menü den zusätzlichen Befehl „Verknüpftes Bild einfügen“.
Dynamische Bilder: Verknüpfte Bilder können Sie mit der Maus unabhängig von der Zellgröße der restlichen Tabelle skalieren und beliebig verschieben. Die Bilder übernehmen alle Formatierungen aus den verknüpften Zellen dynamisch. Unter Umständen müssen Sie dazu – etwa zur Aktualisierung von bedingten Formatierungen – mit der Taste <F9> eine Neuberechnung auslösen.
Diagramm erweitern/reduzieren
Problem:
Wenn Sie in Excel ein sauber formatiertes Diagramm nachträglich um Werte erweitern oder reduzieren wollen, müssen Sie nicht ganz von vorne beginnen.
Lösung:
Klicken Sie stattdessen mit der rechten Maustaste in das Diagramm, und wählen Sie „Datenquelle“. Klicken Sie dann in den Tabellenbezug neben „Werte“, und markieren Sie den neuen Wertebereich. Falls Sie das Diagramm erweitern, benötigen Sie meist noch im selben Dialog die „Beschriftung der Rubrikenachse“: Klicken Sie in das Feld, und markieren Sie den neuen Bezug für die Beschriftung.
Mappen automatisch laden
Problem:
Beim Start von Excel öffnet die Software üblicherweise eine leere Mappe. Arbeiten Sie regelmäßig mit denselben Mappen, könnnen Sie diese automatisch beim Programmstart laden.
Lösung:
Wer immer mit denselben Excel-Mappen arbeitet, kann sich das Laden der Dateien vereinfachen. Unter „Extras, Optionen, Allgemein“ (Excel 2007: „Excel-Optionen, Erweitert, Allgemein“) können Sie einen Ordner eintragen, dessen Dateien Excel beim Start automatisch lädt. Das Feld lautet in jeder Excel-Version anders(„Startordner“, „Beim Start … laden“). Der Ordner sollte nur Excel-Dateien enthalten, weil das Office-Programm auch andere Dateitypen zu laden versucht.
Gestreifter Tabellenhintergrund
Um in einer umfangreichen Tabelle für bessere Übersicht zu sorgen, möchten Sie sie mit Zebrastreifen unterlegen. Die Hintergrundfarben der Zeilen sollen sich also abwechseln.
Excel’s bedingte Formatierungen erlauben es, für Zeilen mit geraden und ungeraden Nummern unterschiedliche Hintergrundfarben festzulegen. Dazu markieren Sie erst den Bereich, der das Streifenmuster erhalten soll. Gehen Sie dann auf "Format, Bedingte Formatierung". Im Dialogfenster stellen Sie nun die erste Option von "Zellwert ist" auf "Formel ist" und tippen dahinter den Ausdruck
=rest(zeile();2)=0
ein, um alle Zeilen mit gerader Nummer anzusprechen. Klicken Sie dann darunter auf "Format", und wählen Sie über "Muster" die Hintergrundfarbe aus. Falls Sie den ungeraden Zeilen ebenfalls eine Farbe verpassen möchten, klicken Sie auf "Hinzufügen", und geben Sie als zweite Bedingung die Formel
=rest(zeile();2)=1
ein. Ein Klick auf "OK" setzt die Formatierung um.
Pure Formel kopieren
Wenn Sie eine Formel von einer Zelle in eine andere kopieren, passt Excel die standardmäßig verwendeten relativen Zellbezüge automatisch an. Wie lässt sich dieser Automatismus umgehen, um eine Formel mit genau denselben Bezügen in eine andere Zelle zu kopieren?
Die meisten Anwender behelfen sich bei dieser Aufgabe damit, die Formel in der Bearbeitungsleiste zu markieren und dort zu kopieren. Meist ist der Umweg über die Bearbeitungsleiste aber gar nicht nötig: Excel bietet eine spezielle Tasten-kombination zum Kopieren einer Formel aus der darüber liegenden Zelle ohne Anpassung der Zellbezüge. Die in der Online-Hilfe beschriebenen Hotkeys <Strg>-<'> (Kopieren einer Formel aus der Zelle über der aktiven Zelle) und <Strg>-<Shift>-<"> (Kopieren des Wertes aus der Zelle über der aktiven Zelle) bewirken nichts.
Verwenden Sie stattdessen <Strg>-<,>, um die Formel der Zelle über der aktiven Zelle zu übernehmen. Der Hotkey <Strg>-<Shift>-<,> macht das Gleiche bei Werten.
Übrigens: Der Hotkey <Strg>-<,> nützt Ihnen auch dann, wenn die Kopie der Formel nicht direkt unter dem Original, sondern in einer beliebigen anderen Zelle landen soll: Kopieren Sie zunächst die Formel mit <Strg> - <,> in die Zelle direkt darunter und verschieben Sie sie anschließend an den gewünschten Ort. Beim Verschieben bleiben nämlich im Gegensatz zum Kopieren die relativen Zellbezüge unverändert.
Liste ohne Duplikate erstellen
Sie haben eine Liste, in der Einträge mehrfach vorkommen. Jetzt benötigen Sie eine Zusammenstellung, in der auch die mehrfach genannten Einträge nur einmal aufgeführt sind. So möchten Sie beispielsweise aus einer Produktliste alle Hersteller zusammenstellen.
Selbes Tabellenblatt: Wenn sich die Liste ohne Duplikate auf demselben Tabellenblatt befinden soll wie die ursprüngliche Liste, können Sie die Aufgabe ganz bequem mit einem Spezialfilter erledigen. Markieren Sie dazu die Spalte, deren Werte auf Duplikate untersucht werden sollen, also etwa die Spalte mit den Herstellerangaben. Wählen Sie "Daten, Filter, Spezialfilter". Im Dialogfeld "Spezialfilter" aktivieren Sie die Option "An eine andere Stelle kopieren". Unter "Listenbereich" muss die Spalte stehen, deren Werte Sie filtern wollen. Tragen Sie unter "Kopieren nach" die Zelle ein, in der die Ergebnisliste beginnen soll. Den "Kriterienbereich" lassen Sie leer. Bevor Sie das Dialogfeld mit "OK" schließen, aktivieren Sie noch die Klickbox "Keine Duplikate".
Auf diesem Weg funktioniert es aber nicht, als Zielbereich eine Spalte auf einem anderen Tabellenblatt anzugeben. Falls Sie es trotzdem mit der üblichen Syntax versuchen, sehen Sie die irreführende Fehlermeldung, dass Excel nur gefilterte Daten in das aktive Blatt kopieren kann.
Neues Tabellenblatt: In den meisten Fällen ist aber aus Gründen der Übersichtlichkeit genau diese Kopiermöglichkeit auf ein anderes Tabellenblatt wünschenswert. Damit Sie nicht mühsam von Hand die gefilterte Liste auf ein anderes Tabellenblatt kopieren müssen, sollten Sie folgenden undokumentierten Trick kennen: Wechseln Sie vor dem Filtern auf das Tabellenblatt, auf dem die gefilterte Liste erscheinen soll. Im Spezialfilter-Dialogfenster ("Daten, Filter, Spezialfilter") akzeptiert Excel als Listenbereich nämlich - im Gegensatz zum Kriterienbereich - auch Verweise auf andere Tabellenblätter. Voraussetzung: Sie haben vorher für den gewünschten Bereich einen Namen festgelegt ("Einfügen, Namen, Definieren").
Geben Sie unter "Listenbereich" im Spezialfilter-Dialogfeld ein Gleichheitszeichen gefolgt vom Bereichsnamen ein, also etwa "=Hersteller". Als "Kriterienbereich" tragen Sie die erste Zelle der Spalte ein, in der die gefilterte Liste erscheinen soll. Überprüfen Sie, dass "An eine andere Stelle kopieren" und "Keine Duplikate" aktiviert sind, und bestätigen Sie dann mit "OK".
Achtung: Die Spezialfilter-Funktion arbeitet nur einwandfrei, wenn Ihre ursprüngliche Liste in der ersten Zeile Überschriften enthält.
Doppelte Eingaben verhindern
In Excel-Arbeitsmappen soll häufig in bestimmten Spalten jeder Eintrag nur einmal vorkommen - etwa eine Auftragsnummer. Sie möchten deshalb von vornherein verhindern, dass hier zweimal derselbe Inhalt eingegeben werden kann.
Sie können das Einfügen von Duplikaten durch Gültigkeits-Prüfungen unterbinden. Markieren Sie dazu zunächst den Bereich, für den dieser Check geschehen soll. Unser Beispiel geht davon aus, dass Sie in den Zellen A1 bis A50 Duplikate verhindern möchten. Sie müssen also die unten genannte Formel entsprechend Ihren Bedürfnissen anpassen.
Markieren Sie den Bereich A1 bis A50. Nun öffnen Sie "Daten, Gültigkeit" und dort die Registerkarte "Einstellungen". In der Drop-down-Liste unter "Zulassen" wählen Sie "Benutzerdefiniert" und geben unter "Formel"
=zählenwenn($A$1:$A$50;A1)=1
ein. Öffnen Sie nun die Registerkarte "Fehlermeldung", und aktivieren Sie die Klickbox neben "Fehlermeldung anzeigen, wenn ungültige Daten eingegeben wurden". Wählen Sie ferner unter "Typ" den Eintrag "Stopp", und geben Sie unter "Titel" und "Fehlermeldung" den Text der Meldung ein, der erscheinen soll, wenn ein Duplikat eingetragen wird.
"Top 10"-Listen erstellen (I)
Sie möchten in einer Excel-Tabelle die Zeilen mit den größten Werten herausfinden. Ihre umfangreiche Liste ist allerdings nach anderen Kriterien angelegt.
Excel bietet einen fertigen Filter für diese Aufgabe: Wählen Sie dazu den Menübefehl "Daten, Filter, Autofilter". Excel definiert dann die erste Zeile der Liste als Spalten-Überschriften und versieht diese jeweils mit einem Drop-down-Pfeil. Klicken Sie in der Spalte auf den Drop-down-Pfeil, die die auszuwertenden Daten enthält. In der Auswahl, die dann aufklappt, markieren Sie den Eintrag "Top 10". Im Dialogfenster "Top-10-AutoFilter" legen Sie fest, ob sie die größten oder die kleinsten Werte anzeigen wollen, und wie viele Werte Sie anzeigen wollen. Alternativ kann Excel auch die ersten oder letzten Prozent anzeigen, also etwa in einer Spalte mit Noten die besten zehn Prozent eines Prüfungsjahrgangs. Um wieder alle Elemente Ihrer Liste anzuzeigen, wählen Sie im Drop-down-Feld des Auto-Filters den Eintrag "Alle". Ganz abschalten können Sie den Auto-Filter, indem Sie erneut auf "Daten, Filter, Autofilter" klicken.
"Top 10"-Listen erstellen (II)
Sie möchten in Excel die Zeilen mit den größten Werten in einer Spalte anzeigen. Dabei reicht aber die Lösung aus dem vorhergehenden Tipp nicht aus, weil Ihre Top-Ten-Liste dynamisch arbeiten soll. Sie möchten beispielsweise auf einem separaten Tabellenblatt die fünf höchsten Rechnungsbeträge aufgelistet bekommen. Excel soll die Top-5-Liste aber immer anpassen, sobald auf dem ersten Tabellenblatt neue Einträge hinzukommen.
In diesem Fall verwenden Sie die Funktion KGRÖSSTE, die mit der Syntax
=KGRÖSSTE(Bereich;Rang)
den Wert zurückgibt, der im angegebenen Bereich den entsprechenden Rang einnimmt. KGRÖSSTE(A:A;3) gibt beispielsweise den drittgrößten Wert der Spalte A zurück. Passen Sie dabei die Spaltenangabe (im Beispiel also "A:A") so an, dass sie sich auf die Spalte des ersten Tabellenblatts bezieht, in der die Rechnungsbeträge stehen.
Wollen Sie mit diesem Verfahren eine größere Anzahl von Werten bestimmen, also etwa die ersten 30, empfehlen wir einen Trick, der es Ihnen erspart, die Rangangabe jeweils einzeln einzutippen. Geben Sie zunächst als Formel
=KGRÖSSTE(A:A;ZEILE())
ein. ZEILE() liefert als Ergebnis die Nummer der Zeile, in der sich die Formel befindet - also Rang "1" , wenn die Formel in Zeile 1 steht und so fort. Falls die Liste nicht ganz oben in Zeile 1 beginnt, ziehen Sie einfach die Zahl der Zeilen bis zum Listenbeginn ab. Wenn Excel also etwa in der vierten Zeile den höchsten Wert ermitteln soll, geben Sie "=KGRÖSSTE(A:A;ZEILE()-3)"ein, um tatsächlich mit Rang 1 zu beginnen. Ziehen Sie dann die Formel einfach mit der Maus nach unten.
Excel/Word/Windows XP: Hyperlinks in Office-Programmen
Sie verwenden in Excel und Word platzsparende Hyperlinks, um alle möglichen Inhalte wie Bilder, weiterführende Texte oder Tabellen in die Hauptdatei einzubinden. Bei einigen Dateitypen funktioniert der Klick auf den Hyperlink sofort, bei anderen müssen Sie erst eine lästige Sicherheitswarnung mit "OK" wegklicken.
Die Ursache für diese Bremse suchen Sie vergeblich dort, wo sie auftritt, nämlich in den Office-Komponenten. Es handelt sich nämlich um einen Windows-Sicherheitsmechanismus, der an Excel & Co. weitergegeben wird. Da Hyperlinks primär für Web- und damit auch für Download-Adressen gedacht sind, reagieren diese auch bei lokalen Dateien so, als würde ein Download vorausgehen. Ob ein Dateityp nach dem Download ungefragt geladen werden darf, entscheiden die "Editflags" des jeweiligen Dateityps in der Windows-Registry.
Im Windows-Explorer finden Sie die fragliche Einstellung unter "Extras, Ordneroptionen, Dateitypen". Suchen Sie hier den störrischen Typ auf, zum Beispiel "JPG", und klicken Sie auf "Erweitert". Wenn Sie dort die Option "Öffnen nach dem Download bestätigen" deaktivieren, verzichtet Office künftig auf die störende Warnmeldung. Diese liberale Einstellung gilt dann allerdings auch für tatsächliche Web-Downloads und vermindert somit die Sicherheit. Für Bild-, Musik-, Text- und Tabellenformate ist das aber vertretbar.
Nach mehr als drei Kriterien sortieren
Sie möchten eine Liste nach fünf Kriterien sortieren. Excel sieht aber bekanntlich im Dialog "Daten, Sortieren" nur drei Sortierkriterien vor. Wenn Sie eine Liste nach mehr als drei Kriterien sortieren wollen, können Sie das folglich nicht mit einem Sortiervorgang erledigen. Stattdessen müssen Sie in einem ersten Schritt nach dem vierten und fünften Kriterium sortieren, dann nach den ersten dreien. Wichtig ist, dass Sie mit den beiden am wenigsten wichtigen Kriterien beginnen.
Wenn Sie also eine Liste mit Rechnungen zuerst nach dem Besteller, dann nach dem Lieferanten, nach dem Artikel, dem Datum und als letztes nach dem Preis sortieren wollen, geben Sie beim ersten Sortiervorgang unter "Sortieren nach" das Datum und bei "Anschließend nach" den Preis ein.
Schließen Sie diesen Sortiervorgang ab, und starten Sie einen neuen. Diesmal geben Sie unter "Sortieren nach" den Besteller, bei "Anschließend nach" den Lieferanten und bei "Zuletzt nach" den Artikel ein. Bei diesem Sortiervorgang sortiert Excel die Daten nach den drei genannten Kriterien. Treten dabei Datensätze auf, bei denen die ersten drei Kriterien sich nicht unterscheiden, übernimmt Excel sie bei diesem Sortiervorgang in unveränderter Reihenfolge - also in der des ersten Sortiervorgangs. Die Liste ist also nach den beiden Durchgängen wie gewünscht nach allen fünf Kriterien sortiert.
Vorlage für alle neue Dateien
Excel bietet unter "Extras, Optionen, Allgemein" nur spärliche Vorgaben für das Aussehen neuer Dateien an: Sie können lediglich Schriftart und -grad bestimmen. Sie möchten aber weitere Einstellungen wie etwa einen Zoomfaktor festlegen oder eine automatische Sicherungskopie anfordern.
Wenn Sie eine ganz bestimmte Vorlagendatei nach Ihren Wünschen gestalten, erhält jede Excel-Datei, die nach Klick auf den Button "Neu" entsteht, deren Einstellungen. Auch die "Mappe1", die Excel beim Starten automatisch anzeigt, folgt diesen Vorgaben.
Vorlagendatei: Die globale Vorlage muss den Namen Mappe.XLT tragen. Wenn Sie allein mit dem PC arbeiten, können Sie die Datei unter "\Programme\Office\ Office<nn>\Xlstart" anlegen (<nn> steht für die Versionsnummer Ihres MS Office - etwa 11 für Office 2003). Teilen sich mehrere Personen diesen Rechner, verwenden Sie das Verzeichnis "\Dokumente und Einstellungen\<User-Konto>\Anwendungsdaten\Microsoft\ Excel\XLStart". Für <User-Konto> tragen Sie den Namen ein, mit dem Sie sich bei Windows anmelden. Eine Mappe.XLT im erstgenannten Ordner hat Vorrang vor jener im User-Ordner. Beide haben in jedem Fall Vorrang vor allen Einstellungen unter "Extras, Optionen, Allgemein".
Gestalten nach Wunsch: Sie können in einer leeren Excel-Datei den gewünschten Zoomfaktor einstellen, Schriftart und -größe aller oder bestimmter Zellbereiche formatieren und im "Speichern unter"-Dialog mit "Extras, Allgemeine Optionen" eine Sicherungskopie anfordern. Danach klicken Sie sich in einen der beiden genannten Zielordner, verwenden als Dateiname "Mappe" und als Dateityp "Mustervorlage (*.XLT)".
Falls Sie Ihre Mappe.XLT später ändern wollen, starten Sie diese nicht mit Doppelklick (dabei ensteht eine neue Datei), sondern mit Rechtsklick und "Öffnen".
Zellinhalte bedingt verstecken
Eine Zelle in der Arbeitsmappe ist mit einer Formel gefüllt, die einen Fehlerwert oder eine Null anzeigt, solange eine bestimmte Zelle in derselben Zeile in einer anderen Spalte noch leer ist. Das sorgt für Verwirrung - Sie möchten diesen Fehlerwert abfangen, damit die Zelle leer bleibt, solange die Formel kein Ergebnis liefert.
Sie können den Fehler abfangen, indem Sie Ihre Formel mit der Funktion ISTFEHLER verketten. Wenn Sie bei Formeln Fehler erwarten, stecken Sie die Formel in diese Bedingung:
=WENN(ISTFEHLER(<Formel>); ""; <Formel>)
Falls die Formel eine Null ausgibt, die Sie unterdrücken möchten, genügt dieses WENN:
=WENN(<Formel> = 0; ""; <Formel>)
Ersetzen Sie <Formel> durch die Formel, die in der Zelle steht.
Wenn es Ihnen nur um die Optik geht, geht, lassen sich Fehlerausgaben von Formeln auch mit einer dynamischen bedingten Formatierung verstecken. Der Trick: Mit der Formatierung weisen Sie den betreffenden Zellen die gleiche Schrift- und Hintergrundfarbe zu, etwa Weiß auf Weiß. Der Inhalt ist damit unsichtbar. Markieren Sie die Zellen, und wählen Sie "Format, Bedingte Formatierung". Statt des standardmäßig eingeblendeten "Zellwert ist" wählen Sie die Option "Formel ist". In das Feld für die Formel geben Sie für die Nullwerte
=<Formel>=0
ein und bei den Fehlerwerten diesen Ausdruck:
=ISTFEHLER(<Formel>)
Wählen Sie die gewünschten Farbstil, und bestätigen Sie mit "OK".