|
PC-Tipps Excel (Teil 2)
Makros nach einer vorgegebenen Zeit starten Sie können in Excel mit Hilfe des Befehls BeiZeit dafür sorgen, dass die Tabellenkalkulation eine bestimmte Zeitspanne nach dem Start eines Makros ein weiteres Makro startet. Eine praktische Anwendung für diesen Befehl wäre z.B., eine Meldung nur kurz anzuzeigen. Das unten angeführte Makro Textfeld_anzeigen erzeugt in der aktuellen Arbeitsmappe ein Textfeld mit dem Inhalt Guten Morgen. Nach der in der zweitletzten Zeile eingetragenen Zeit - im Beispiel sind es zehn Sekunden - wird das Makro Textfeld_löschen gestartet, das den Gruß wieder entfernt. Wenn Sie eine andere Zeitspanne bis zum Start des zweiten Makros wünschen, müssen Sie den Zeitwert entsprechend ändern. Damit das zweite Makro nach genau einer Stunde aktiv wird, müsste der Zeitwert 01:00:00 eingestellt werden, um es nach 22 Minuten und 10 Sekunden zu starten, auf 00:22:10. Makrolisting zum Erzeugen eines Textfeldes: Sub Textfeld_einfügen() AktivesBlatt.TextfeldListe.Hinzufügen(80,25; 80,25; 434,25; 94,5).Auswählen Auswahl.Zeichen.Text = "Guten Morgen!" Mit Auswahl.Zeichen(Start:=1; Länge:=13).Schriftart Auswahl.Innenbereich.FarbIndex = 20 Auswahl.HorizontaleAusrichtung = xlZentriert Auswahl.VertikaleAusrichtung = xlZentriert .Name = "Arial" .Schriftstil = "Standard" .Grösse = 24 Ende Mit Anwendung.BeiZeit Jetzt + Zeitwert("00:00:10"); "Textfeld_löschen" Ende Sub Makrolisting zum Löschen des Textfeldes: Sub Textfeld_löschen() AktivesBlatt.TextfeldListe(1).Auswählen Auswahl.Löschen Ende Sub Mappen konfigurieren Excel bietet grundsätzlich eine feste Anzahl von Arbeitsblättern in einer Mappe. Diese Anzahl ist jedoch kein Gesetz: Gehen Sie in Extras und Optionen. Dort finden Sie das Register Allgemein, in dem Sie die Voreinstellung Blätter in Arbeitsmappe nach Ihren Wünschen konfigurieren. Matrix-Angabe im Funktionsassistent Viele Funktionen von Excel verlangen die Angabe einer Matrix, auf die sich eine bestimmte Rechenoperation bezieht. Ein Beispiel ist etwa die Funktion SVERWEIS, die Sie nach Einfügen | Funktion in der Kategorie: Matrix finden. Im Funktionsassistenten dieser Funktion müssen Sie im Feld Matrix einen Zellbereich für die Formel angeben. Wenn sich dieser Bereich auch noch auf einem anderen Tabellenblatt der Mappe befindet, müssen Sie entweder zuerst zu diesem Tabellenblatt wechseln und dort den Bereich markieren oder die fehleranfälligere Variante wählen und den Bezug von Hand eintippen. Viel komfortabler können Sie diese Funktionen verwenden, wenn Sie den relevanten Zellbereichen Namen zuweisen. Dafür markieren Sie einfach den gewünschten Bereich in der Tabelle und klicken danach in das Feld links neben der Eingabezeile von Excel, in dem normalerweise der Name der gerade aktiven Zelle erscheint. Dort tragen Sie einen Namen für den markierten Zellbereich ein und bestätigen mit der Taste [Return]. Diesen Namen können Sie ab sofort verwenden und ihn im Funktionsassistenten anstelle der "Bezugsadresse" in das Feld Matrix eintragen. Auch wenn Sie sich auf einen Zellbereich einer anderen Tabellenseite beziehen, funktioniert diese Methode, denn jeder Name darf in Excel nur einmal auftauchen. Verwechslungen sind also ausgeschlossen. Minimum suchen Manchmal ist es ärgerlich, wenn bei der Suche nach einem Minimalwert in einer Tabelle immer nur Nullen erscheinen. Die folgende kleine Zeile führt zur Ignorierung von Nullwerten bei der Anwendung der MIN-Funktion auf den Bereich A1 bis A10: =MIN(WENN(A1:A10;>0;A1:A10)). Denken Sie aber daran, die Zeile als Matrixformel für den Bereich A1:A10 einzugeben. Das bedeutet, dass Sie während der Bestätigung der Eingabe [Strg] Umschalt gedrückt halten. Minuszeiten in Excel darstellen Möchte man in Excel Arbeitszeiten von Mitarbeitern erfassen, steht man häufig vor dem Problem, dass die Tabellenkalkulation Schwierigkeiten hat, negative Zeiten, die etwa beim Vergleich von Soll- und Istzeiten leicht anfallen können, richtig darzustellen. Versucht man einen größeren von einem kleineren Zeitwert abzuziehen, füllt Excel die Zelle mit Gittern, die sich nur durch eine Standardformatierung als Zahl, nicht aber als Zeit beseitigen lassen. Dezimalwerte sind jedoch in Zeiterfassungstabellen meist nicht erwünscht. Befriedigende Ergebnisse erhält man erst dann, wenn man diese Zahlen in eine Hilfszeile setzt. Möchten Sie in Zeile 10 die Mehrstunden und in Zeile 11 die Minderstunden darstellen, um mit diesen Werten weiterzurechnen, verwenden Sie die Zeile 9 als Hilfszeile, die in der fertigen Tabelle ausgeblendet wird. In dieser Zeile erfolgt die Berechnung Ist- minus Soll-Stunden. Solange dieser Wert positiv ist, hat Excel kein Problem, doch ist er negativ, kommt es zum oben beschriebenen Effekt. Mit dem Format | Zellen | Zahlen | Uhrzeit lässt sich dieser Wert nicht darstellen. Wohl aber kann er zur weiteren Berechnung herangezogen werden, dann, wenn die Funktion ABS verwendet wird. Sie entbindet eine Zahl ihres Vorzeichens, schafft also den hinderlichen negativen Wert aus der Welt. Damit in Zeile 10 die täglichen Überstunden und in Zeile 11 die zuwenig geleisteten Stunden erscheinen, tragen Sie dort folgende Formeln ein: in Zelle B10 die Formel =WENN(B9>0;(B9);0 und in Zelle B11 =WENN(B9<0;ABS(B9);0). Kopieren Sie diese Formeln über sämtliche Spalten der Woche und formatieren Sie anschließend die Zeilen 10 und 11 mit Format | Zellen | Zahl | Uhrzeit im gewünschten Format. Eine Besonderheit werden Sie noch in der Summenspalte feststellen: Excel ist so "intelligent", Zeiten nur bis zu 24 Stunden zu addieren und danach wieder bei 0 zu beginnen. Versuchen Sie also, eine Summe aus den 5 Arbeitstagen a 8 Stunden zu bilden, wird Excel beharrlich die Stundenzahl 16:00 ausgeben. Sie können die, in diesem Fall unerwünschte, Eigenmächtigkeit umgehen, indem Sie diejenigen Zellen, die Zeitensummen über 24 Stunden anzeigen sollen, mit einem benutzerdefinierten Format ausgeben. Wählen Sie dazu Format | Zellen | Zahlen | Benutzerdefiniert und geben Sie dann unter Formate die Zeichenfolge [h]:mm ein. Durch die eckigen Klammern im Zellenformat wird Excel angewiesen, Stundenwerte auch über 24 Stunden hinaus auszugeben. Mittelwert ohne Nullen berechnen Ich möchte in Excel den Mittelwert berechnen, ohne jedoch Nullwerte in der Zahlenreihe zu berücksichtigen. Welche Formel kann ich dazu einsetzen? Excel bietet die Standardfunktion MITTELWERT() an, die jedoch Nullwerte in die Berechnung einbezieht. Nur über eine Array-Formel können Sie Nullwerte aus der Mittelwertberechnung ausschließen. Das folgende Beispiel erläutert eine derartige Berechnung für eine Zahlenreihe mit den Werten 0, 1, 0, -2, 5, 0, 6 und dem Namen Test. Eine WENN-Funktion fragt zunächst ab, ob die Werte in der Zahlenreihe Testungleich 0 sind. Falls ja, summiert die Formel alle Werte ungleich 0 und dividiert durch die Anzahl dieser Werte: {=SUMME(WENN(Test<>0;Test;0))/SUMME(WENN(Test<>0;1;0))}. Da es sich bei dieser Formel um eine Array-Formel handelt, müssen Sie deren Eingabe mit [Strg] Umschalt [Return] abschließen, damit Excel automatisch die beiden geschweiften Klammern setzen kann. Das Ergebnis des obigen Beispiels ist 2,5. Die Mittelwert-Berechnung mit der Funktion =MITTELWERT(TEST) liefert dagegen das nicht erwünschte Ergebnis 1,428. Monatsnamen Ich habe eine Spalte mit Monatsnamen und möchte mit den Monaten rechnen. Excel unterstützt aber nur eine siebenfache Verschachtelung der Funktion Wenn. Eine vollständige Zuordnung der Monatsnamen zu Zahlen ist deshalb nicht möglich. Gibt es dennoch einen Weg, basierend auf den Namen zu rechnen? Die Begrenzung auf die siebenfache Verschachtelung hängt mit der Konstruktion der Excel-Tabellenfunktionen zusammen und stellt deshalb eine unüberwindliche Schwelle dar. Das muss Sie aber nicht daran hindern, mit den Monatsnamen zu rechnen. Mit Hilfe der Funktion Monat(Argument) und einem kleinen Trick erreichen Sie das gewünschte Ziel sogar schneller als mit der Verschachtelung. Die Funktion Monat errechnet aus einer Anzahl von Tagen den entsprechenden Monat und gibt eine fortlaufende Zahl zwischen 1 und 12 zurück. So erzeugt beispielsweise das Argument 360 das Ergebnis 12. Die Funktion akzeptiert neben reinen Zahlen aber auch Zeichenketten, die wie ein Datum aussehen. So erzeugt das Argument 15. Mai das Ergebnis 5. Diesen Umstand können Sie sich zunutze machen. Interpretieren Sie den Monatsnamen als Zeichenkette und stellen Sie noch eine Zahl zwischen 1 und 28, gefolgt von einem Punkt und einem Leerzeichen voran. Dann kann die Funktion die entsprechende Monatszahl ermitteln. Wenn Sie beispielsweise in der Zelle A1 den Monatsnamen Januar stehen haben, schreiben Sie folgenden Funktionsaufruf in Zelle B1, um das Ergebnis 1 zu erhalten: =MONAT ("1. "&A1) Damit rufen Sie die Funktion MONAT auf und übergeben ihr die Zeichenkette 1. Januar. Als Rückgabewert erhalten Sie 1. Auf diese Weise gibt die Funktion für alle Monatsnamen die fortlaufende Nummer von 1 bis 12 an. Ohne die vorangestellte Tagesangabe in Form einer Zahl und dem Leerzeichen erhalten Sie eine Fehlermeldung. Monatspläne erstellen Möchte man z.B. für Schicht- oder Kalenderpläne in der ersten Spalte einer Tabelle alle Tage eines Monats aufführen, steht man vor einem Problem: Schreibt man in die Zelle A1 das Datum, also den ersten des Monats, und in A2 die Formel A1+1 und kopiert man diese Formel bis zur Zelle A31, fügt Excel für Monate mit nur 30 Tagen in Zelle A31 bereits den ersten des Folgemonats ein. Soll Excel diese Felder nur bis zu den Tagen ausfüllen, die der Monat tatsächlich enthält, verwenden Sie die Formel =WENN(D1="";"";WENN (MONAT (A1 + 1) > MONAT (A1) ; "" ; A1+1)). Schreiben Sie den ersten des Monats, also z.B. 01.11.07, in die Zelle A1 und die Formel in Zelle A2. Kopieren Sie dann die Formel nach unten bis zur Zelle A31 und markieren Sie die gesamte Spalte. Falls der Monat des Datums in A1 weniger als 31 Tage hat, bleiben die letzten Felder leer. Damit das Datum sichtbar wird, geben Sie diesen Zellen über Format | Zelle | Zahlen | Datum ein beliebiges Datumsformat. Navigieren mit Hyperlinks Der Wechsel zu einem entfernten Bereich in einer umfangreichen Arbeitsmappe kann sehr mühsam und zeitraubend sein. Mit Hilfe der Hyperlink-Funktionen von Excel und ein wenig Vorarbeit können Sie jedoch sehr schnell und bequem innerhalb einer einzelnen Arbeitsmappe und auch zwischen verschiedenen Dateien navigieren.
Für die Navigation zwischen diesen Bereichen richten Sie sich ganz am Anfang der Tabelle einige Zeilen neu ein. Beschreiben Sie in diesen Zellen Ihr Sprungziel, indem Sie z.B. Umsatz Januar 08 in die Zelle A1 schreiben. Lassen Sie diese Zelle markiert und wählen Sie Einfügen | Hyperlink; alternativ können Sie auch [Strg] [K] betätigen. Sie gelangen in die Dialogbox Hyperlink hinzufügen, in der Sie in dem Bereich Name einer Stelle in der Datei (Optional): die Schaltfläche Durchsuchen betätigen. Im folgenden Fenster klicken Sie auf Festgelegter Name und wählen aus der Liste das gewünschte Ziel per Doppelklick aus. Bestätigen Sie mit OK, und der Hyperlink ist bereit für den ersten Test.
Sie können sogar Hyperlinks über mehrere Dateien hinweg anlegen. Dafür wählen Sie in der Dialogbox Hyperlink hinzufügen nach einem Klick auf Durchsuchen im Bereich Verknüpfung zu Datei oder URL eine Excel-Arbeitsmappe aus und können dann im unteren Fensterbereich wie bereits beschrieben ein Sprungziel in dieser Datei auswählen. So entsteht mit der Zeit ein komplexes und bequem zu bedienendes Netz aus allen vorhandenen Informationen. Falls Sie in den ersten Zeilen der ersten Tabelle eine Art Navigationsfeld einrichten wollen, sollten Sie auch dafür einen Namen festlegen, um es jederzeit bequem erreichen zu können. Wenn Sie in Excel auf die kleine nach unten weisende Schaltfläche links neben der Eingabezeile klicken, erhalten Sie eine Liste aller in der Tabelle definierten Namen. Auch auf diesem Weg können Sie jederzeit aus allen Tabellen Ihrer Arbeitsmappe zu allen Sprungzielen oder auch zum Navigationsfeld gelangen.
Zu diesem Zweck klicken Sie auf eine Zelle unter dem Navigationsbereich und wählen Fenster | Fixieren. Direkt über der markierten Zelle erscheint eine schwarze horizontale Linie. Nur die Zeilen unterhalb dieser Linie können gescrollt werden. Die darüberliegenden Zeilen bleiben sichtbar. So entsteht eine Art Frame, wie Sie ihn aus dem Internet kennen. Negative Zeiten darstellen Wenn Sie die Option 1904-Datumswerte im Menü Extras | Optionen | Berechnen aktivieren, stellt Excel negative Zeiten problemlos dar. Allerdings ist diese Einstellung mit einem kleinen Haken verbunden: Das Programm kann nicht mehr mit Zeiten vor dem 1.1.1904 rechnen. Neuberechnen von Mappen Wer viel mit Tabellen arbeitet und Zeit sparen möchte, kann das Neuberechnen von Arbeitsmappen auf Taste legen. Dazu wählen Sie Extras | Optionen. Im Register "Berechnen" aktivieren Sie dann "Auf Befehl". Von nun an genügt schon [Umschalt] [F9], um Arbeitsmappen erneut zu berechnen. Nicht löschbare Copyright-Vermerke in Arbeitsmappen integrieren Wenn man sich schon die Mühe gemacht hat, eine komplexe Excel-Arbeitsmappe zu erarbeiten, dann möchte man sie vielleicht vor der Weitergabe an Dritte mit einem Copyright-Vermerk versehen. Mit einem Trick können Sie dafür sorgen, dass der Empfänger der Datei die Arbeitsmappe zwar nach seinen Wünschen verändern kann, der Copyright-Vermerk für ihn jedoch unantastbar und somit unlöschbar ist. Möglich ist das über einen Blattschutz, der sich nur auf Ihren Copyright-Vermerk bezieht. Damit Sie keine Zellen aus dem Schutz ausklammern müssen, fügen Sie Ihren Copyright-Vermerk innerhalb eines Objekts ein. Über Ansicht | Symbolleisten öffnen Sie nach einem Klick auf den Eintrag Steuerelement-Toolbox eine Auswahl verschiedener Objekte wie Textfelder und Buttons. Aus dieser Auswahl können Sie z.B. ein Textfeld, besser aber noch ein Bezeichnungsfeld für Ihren Vermerk verwenden. Der Vorteil des Bezeichnungsfeldes: Sie können mit Hilfe von Umschalt [Return] einen Zeilenumbruch erzeugen, im Textfeld ist das nicht möglich. Um Ihren Text einzutragen, klicken Sie das eingefügte Feld mit der rechten Maustaste an und wählen Bezeichnungsfeld-Objekt | Bearbeiten. Nachdem Sie den gewünschten Text eingegeben haben, klicken Sie einmal außerhalb des Feldes, um es zu deaktivieren. Bevor Sie den Blattschutz aktivieren, sollten Sie sicherstellen, dass keine weiteren Objekte wie z.B. Schaltflächen, Diagramme oder Bilder in der Tabelle vorhanden sind. Ist das der Fall und wollen Sie verhindern, dass sie ebenfalls geschützt werden, klicken Sie diese Objekte mit der rechten Maustaste an und wählen aus dem Popup-Menü Objekt formatieren. Je nach Objektart kann dieser Aufruf variieren, bei einer Schaltfläche lautet er z.B. Steuerelement formatieren. In jedem Fall landen Sie in einer Dialogbox mit der Registerzunge Schutz. Damit das Objekt von der späteren Sperre ausgeschlossen wird, müssen Sie auf diesem Registerblatt das Häkchen aus dem Feld Gesperrt entfernen. Wenn Sie dieses Häkchen bei allen veränderbaren Objekten entfernt haben, wählen Sie Extras | Schutz | Blatt und markieren in der folgenden Dialogbox nur den Eintrag Objekte mit einem Häkchen. In das Eingabefeld geben Sie nun ein Kennwort ein, das Sie im nächsten Schritt zur Sicherheit wiederholen müssen. Es ist jetzt nicht mehr möglich, das so geschützte Feld zu verschieben oder zu löschen. Wenn Sie es selbst verändern wollen, müssen Sie das Arbeitsblatt zuerst wieder mit dem Menüpunkt Extras | Schutz | Blattschutz aufheben und der Eingabe des Kennwortes aus der Fessel befreien. Notizen und Kommentare ausdrucken Wenn man in Excel zu einzelnen Zellen Notizen eingefügt hat, kann es vorkommen, dass diese auch auf dem Ausdruck benötigt werden. Möchten Sie in Excel Ihre Notizen mit dem Arbeitsblatt auf dem Drucker ausgeben, dann wählen Sie Datei | Seite einrichten und aktivieren die Option Notizen. Die Notizen werden auf einem Extrablatt hinter der eigentlichen Tabelle ausgedruckt. Allerdings fehlt in dieser Liste normalerweise die Angabe der Zellbezüge. Wenn Sie möchten, dass auf dem Ausdruck neben dem Inhalt der Notiz auch die Information erscheint, auf welche Zelle sich die Notiz bezieht, müssen Sie im Menü Datei | Seite einrichten außerdem die Option Zeilen- und Spaltenköpfe aktivieren. In Excel funktioniert der Ausdruck der Kommentare ähnlich. Wählen Sie Datei | Seite einrichten und geben Sie auf dem Registerblatt Tabelle im Feld Kommentare an, wie die Kommentare auf dem Ausdruck erscheinen sollen. Wenn Sie sich für die Option Am Ende des Blattes entscheiden, erhalten Sie auf einem Extrablatt eine ähnliche Liste wie in Excel. Sie können aber auch dafür sorgen, dass die Kommentare ähnlich wie Post-Its direkt auf der Tabelle dargestellt werden. Dafür wählen Sie die Option Wie auf dem Blatt angezeigt. Weil die Kommentare jedoch normalerweise nur dann sichtbar sind, wenn Sie den Mauszeiger darüber bewegen, ist mit dieser Einstellung allein noch nicht viel gewonnen. Damit die Kommentare tatsächlich auf dem Ausdruck erscheinen, müssen Sie sie mit Ansicht | Kommentare sichtbar machen. In diesem Moment werden alle Kommentare in der Tabelle eingeblendet und erscheinen auch so auf dem Ausdruck. Darüber hinaus finden Sie eine Symbolleiste zur Verwaltung der Kommentare vor. Wenn Sie einige der Kommentare lieber verbergen wollen, dann klicken Sie in dieser Symbolleiste zunächst auf Alle Kommentare ausblenden. Anschließend klicken Sie jede Zelle, deren Anmerkung sichtbar sein soll, gezielt an und betätigen danach die Schaltfläche Kommentar anzeigen. Nur sichtbare Zellen kopieren Wurden in Excel einige Spalten oder Zeilen ausgeblendet, werden diese dennoch grundsätzlich in die Markierung mit einbezogen. Dies ist z.B. dann besonders unangenehm, wenn man in einer Tabelle der Übersichtlichkeit halber bestimmte Bereiche ausblendet und versucht, nur die sichtbaren Bereiche in eine neues Tabellenblatt zu kopieren. Wurden komplette Zeilen oder Spalten kopiert, tauchen in der Kopie die ausgeblendeten Zellen wieder als solche auf. Wurden nur Bereiche innerhalb des Tabellenblattes markiert, erscheinen in der Kopie wieder alle, auch die ausgeblendeten, Zellen. Eine Option "Nur sichtbare Zellen kopieren" enthält Excel nicht. Dem kann jedoch mit einem kleinen Makro abgeholfen werden. Legen Sie mit Einfügen | Makro | Visual Basic-Modul ein neues Makroblatt an und tragen Sie folgende Zeilen ein: Sub NurSichtbarKopieren() Auswahl.BesondereZellen (xlSichtbar).Kopieren Ende Sub Damit das Makro leicht über eine Tastenkombination aufgerufen werden kann, sollten Sie über Extras | Makros Ihr Makro anwählen und mit Optionen einen Shortcut zuweisen. Weil die Tastenkombination für das Kopieren [Strg] [C] lautet, bietet sich die gleiche Kombination in Verbindung mit der Umschalt-Taste für den Start des Makros an. Setzen Sie dafür ein Häkchen vor die Option Shortcut und schreiben Sie in das Eingabefeld ein C. Wollen Sie nur die nicht ausgeblendeten Zellen kopieren, starten Sie das Makro mit [Strg] Umschalt [C], für das normale Kopieren verwenden Sie wie gewohnt [Strg] [C]. Nur volle Tausender darstellen Bei Tabellen mit sehr großen Zahlen ist es oft praktisch, die Zahlen auf Tausender gerundet darzustellen. Damit Excel jedoch mit den Originalzahlen weiterrechnet, ist eine Rundung hier oft ungeeignet. Über ein benutzerdefiniertes Zahlenformat erreichen Sie, dass Excel in der Bildschirm- und Druckausgabe auf Tausender rundet, jedoch mit den ungerundeten Zahlen rechnet.
Nur Werte jeder zweiten Spalte addieren In Tabellen ist es häufig nötig, nur jeden zweiten oder n-ten Wert in eine Summe einzubeziehen. Dies kann man z.B. erreichen, indem man alle betroffenen Zellbezüge einzeln in die Formel aufnimmt. Die Formel könnte also =SUMME(A1+A3+A5) lauten. Diese Methode hat jedoch Nachteile: Sie ist umständlich und vor allem bei umfangreichen Tabellen sehr zeitaufwendig. Außerdem dürfen Sie bei der Funktion Summe nur maximal 30 Argumente verwenden. Einfacher und schneller können Sie diese Aufgabe mit dem Bezug auf einen Zellbereich statt auf einzelne Zellen lösen. Das gelingt mit einer Matrixformel. Die Formel {=SUMME(WENN(REST(SPALTE(A6:H6);2)=0;A6:H6))} ist auf die Addition jeder zweiten Zelle des Zellbereichs A6 bis H6 ausgelegt. Ersetzen Sie die 2 hinter dem Semikolon durch eine 3, wird jede dritte Spalte in die Summe aufgenommen. Die Funktion Spalte liefert die Spaltennummern des Bereichs. Die Spalte A hat also den Wert 1, Spalte B die 2, Spalte C die 3 und so weiter. Beachten Sie bitte, dass die Spaltennummern absolut sind. Wenn der relevante Zellbereich erst bei der Spalte D beginnt, liefert die Funktion Spalte nicht den Wert 1, sondern 4. Falls die Werte in Zeilen stehen, müssen Sie in der Formel die Funktion Spalte durch Zeile ersetzen, um die Zeilennummer zu erhalten. Mit der Kombination der Funktionen Wenn und Rest werden die passenden Spalten herausgefiltert. Nur wenn die Spaltennummer geteilt durch den hinter dem Semikolon angegebenen Wert den Rest 0 liefert, also durch den Wert teilbar ist, wird Sie für die Addition verwendet. Weil es sich bei dieser Formel um eine Matrixformel handelt, müssen Sie die Eingabe und jede spätere Änderung mit [Strg] Umschalt [Return] bestätigen. Nur Zahlen einer bestimmten Größe anzeigen Um die Übersicht in einer Tabelle zu erhöhen, ist es manchmal erforderlich, bestimmte Zahlen zu verbergen. Meistens muss jedoch das Rechenergebnis auch diese nicht angezeigten Zahlen beinhalten. Das kann z.B. der Fall sein, wenn man nur Umsätze über einem bestimmten Betrag anzeigen möchte, aber für das Ergebnis die Summe aller Zahlen benötigt. Dies gelingt über zwei verschiedene Wege: Entweder über ein benutzerdefiniertes Zellenformat oder über einen Autofilter. Haben Sie z.B. eine Tabelle vorliegen, in der Sie nur die Zeilen anzeigen wollen, deren Wert in der Spalte Betrag 100 übersteigt, ist der Autofilter normalerweise die bessere Alternative. Markieren Sie in diesem Fall den Bereich der Spalte, aus dem Sie die Zahlen unter 100 ausschließen wollen und klicken Sie auf Daten | Autofilter. Ein Mausklick auf das Pfeilsymbol in der markierten Spalte bringt eine Liste der markierten Werte der Spalte und verschiedene in Klammer gesetzte Optionen zutage. Wählen Sie hier (Benutzerdefiniert) und tragen Sie in das erste oberste Feld das Zeichen >= ein. Schreiben Sie daneben 100. Dieser Filter legt fest, dass nur die Zeilen angezeigt werden, deren Wert in der markierten Spalte 100 oder höher ist. Allerdings verbirgt Excel in diesem Fall die kompletten Zeilen, wenn der Wert unter 100 liegt. Möchten Sie alle Zeilen sehen, aber in den Zellen mit dem Betrag nur die Beträge über 100 anzeigen, benutzen Sie lieber ein benutzerdefiniertes Zellformat. Markieren Sie dafür den gewünschten Zellbereich und wählen Sie Format | Zelle | Benutzerdefiniert. Löschen Sie unter Formate den Eintrag Standard und schreiben Sie statt dessen die Zeichenfolge [<100]";Standard. Mit diesem Format werden alle Werte unter 100 verborgen, doch der Rest der Zeile ist weiterhin sichtbar. Excel bezieht die verborgenen Werte weiterhin in das Ergebnis mit ein. Möchten Sie beispielsweise nur Werte zwischen 0 und 100 darstellen, so verwenden Sie das Format [<0]";[>100]"; Standard. Per Makro die letzte Zelle einer Spalte anspringen Excel enthält standardgemäß keine Funktion, um innerhalb einer Makroroutine oder während der normalen Arbeit in der Tabelle die letzte Zelle einer Spalte anzuspringen. Möchten Sie aber bei umfangreichen Tabellen per Mausklick zur letzten Zelle einer Spalte springen oder innerhalb eines Makros den letzten Eintrag finden, verwenden Sie folgendes einzeilige Makro; diese Routine springt zunächst die letzte Zelle der aktuellen Spalte an und sucht dann von unten nach oben nach der ersten Zelle, die nicht leer ist: Sub Spaltenende() Wenn IstLeer(ZelleListe(16384; AktiveZelle.Spalte)) Dann ZelleListe(16384;AktiveZelle.Spalte).Ende(xlNachOben).Auswählen Sonst ZelleListe(16384; AktiveZelle.Spalte).Auswählen Ende Wenn Ende Sub Per Mausklick den markierten Bereich zoomen Excel enthält im Menü Ansicht | Zoom die Option An Markierung anpassen. Wählt man diese Ansichtsart, wird die Ansicht so stark vergrößert, dass der markierte Zellbereich das komplette Fenster füllt. An diese sehr praktische Art der Ansicht gelangt man jedoch über das Menü nur sehr umständlich. Mit der unteren Makrozeile können Sie diese Funktion bequem über eine Schaltfläche aufrufen. Im Untermenü Ansicht | Symbolleisten | Anpassen wählen Sie im Bereich Kategorien den Eintrag Benutzerdefiniert. Ziehen Sie dann eine beliebige Schaltfläche in eine der Symbolleisten und weisen Sie ihr in der daraufhin erscheinenden Dialogbox dieses Makro zu: Sub Zoom() AktivesFenster.Zoom = Wahr Ende Sub Quartalsberechnung Ich benutze Excel für Abrechnungen. Dabei benötige ich oft für ein Tagesdatum das entsprechende Quartal. In Excel habe ich bisher keine Formel dafür gefunden. Excel besitzt keine vorgefertigte Funktion zur Berechnung des Quartals. Allerdings lässt sich eine solche Funktion leicht selbst schreiben. Das Ergebnis ist eine Formel, die mit einer Kombination aus den Funktionen WENN und MONAT arbeitet. Die Funktion MONAT ermittelt den Zahlenwert des Monats aus einem Tagesdatum, und die Funktion WENN ermittelt durch Prüfung dieses Wertes das Quartal und gibt einen entsprechenden Text als Ergebnis aus. Falls Ihr Datum in der Zelle D4 steht, sieht die benötigte Formel folgendermaßen aus: =WENN(MONAT(D1)>9;"Quartal IV";WENN(MONAT(D1)>6;"Quartal III";WENN(MONAT(D1)>3;"Quartal II";"Quartal I"))) Wenn Sie allerdings den Wert des Quartals zum Weiterrechnen benötigen, müssen Sie eine andere Berechnung durchführen, die als Ergebnis eine Zahl liefert. Diese Formel ist weniger aufwendig und sieht dann so aus: =AUFRUNDEN((MONAT(D4))/3;0) Als Ergebnis erhalten Sie aber nur die Zahlenwerte 1, 2, 3 oder 4. Eine Ergänzung in der nächsten Spalte mit dem Text Quartal ist möglich und reicht optisch auch völlig aus. Rechter Einzug für Zahlen Als Textausrichtung steht unter anderem die Option "Links (Einzug)" zur Verfügung. Ich benötige aber immer wieder einen rechten Einzug, da die Zahlen nicht direkt am rechten Spaltenrand erscheinen sollen. Mit Excel 2002 haben die Entwickler diesen Wunsch vieler Anwender endlich in die Tat umgesetzt. Hier steht als Ausrichtung auch ein Eintrag "Rechts (Einzug)" zur Verfügung. In Excel 97 und 2000 kann man einen rechten Einzug erzwingen: Weisen Sie den Werten in Ihrer Tabelle zunächst das gewünschte Zahlenformat zu. Wählen Sie das Menü FORMAT-ZELLEN an und wechseln Sie auf die Registerkarte "Zahlen". Markieren Sie in der Liste KATEGORIE den Eintrag "Benutzerdefiniert". Im Eingabefeld TYP erscheint nun die komplette Definition des Zahlenformates, das Sie gerade zugewiesen haben. Sie ändern diese Definition jetzt nur ein wenig ab, indem Sie am Ende ein oder mehrere Unterstriche (_) hinzufügen. Ein Unterstrich steht dabei für die Breite eines Zeichens. Wichtig dabei zu wissen: das Zahlenformat von Excel besteht aus mehreren Abschnitten, die jeweils durch ein Semikolon voneinander getrennt sind. Fügen Sie die gewünschte Anzahl von Unterstrichen jeweils am Ende der ersten drei Abschnitte ein und bestätigen Sie mit OK. Schaltflächen nach Maß Gefällt Ihnen die eine oder andere Standardschaltfläche von Excel nicht? Kein Problem, Sie können diese Schaltflächen beliebig verändern. Klicken Sie dafür mit der rechten Maustaste auf eine Symbolleiste und wählen Sie aus dem Kontextmenü den Eintrag Anpassen. Während die Dialogbox Anpassen aktiv ist, klicken Sie mit der rechten Maustaste auf die Schaltfläche, deren Design Ihnen ein Dorn im Auge ist und wählen Schaltflächensymbol bearbeiten. Sie landen direkt im Schaltflächeneditor, mit dem Sie im Handumdrehen z.B. den Pinsel der Schaltfläche Format übertragen in rote Farbe tauchen können. Schrägstrich: Eingabe wird verhindert Möchte man in Excel als erstes Zeichen einer Zelle einen Schrägstrich eingeben, wird dies nicht ohne weiteres gelingen. Nach Betätigen von Umschalt [7] in einer leeren Zelle verhält sich Excel so, als würden Sie die Taste [Alt] gedrückt halten. Das Programm versucht also, ein Menü zu öffnen, sobald Sie eine weitere Taste drücken. Wenn Sie in einer Zelle als erstes Zeichen einen Schrägstrich eingeben wollen, müssen Sie deshalb zunächst [F2] betätigen. Damit schalten Sie in Excel in den Editiermodus um. Jetzt nimmt es die Eingabe des Schrägstrichs klaglos an. Excel ist übrigens nicht immer so einfach zu bewegen, ein Zeichen anzunehmen. In besonders schwierigen Fällen können Sie immer das Zeichen Umschalt [#] als erstes Zeichen verwenden. Dieser kleine Oberstrich ist in der Zellenansicht nicht sichtbar. Er sorgt jedoch dafür, dass Excel den Rest der Eingabe genau so akzeptiert, wie Sie ihn eingeben. Sicherheitslücke in der Vorschaufunktion Bei all jenen, die gerne und viel mit Excel arbeiten, sammelt sich mit der Zeit eine Fülle von Arbeitsmappen an. Manchmal helfen auch die langen Dateinamen von Win95 nicht weiter, wenn man eine bestimmte Datei sucht. Für den kompletten Durchblick ist deshalb eine Vorschau sinnvoll. Sie erhalten Sie, indem Sie im Menü Datei den Befehl Eigenschaften wählen und das Kästchen Vorschaugrafik speichern ankreuzen. Sobald Sie das nächste Mal im Menü Datei | Öffnen die Ansichtsart Vorschau wählen, erhalten Sie beim Anklicken der Datei, die mit einer Vorschaugrafik ausgestattet wurde, einen Blick auf die linke obere Ecke der ersten Tabelle. Allerdings ist diese Vorschau auch dann sichtbar, wenn Sie die Datei mittels Kennwort geschützt haben. Falls Sie verhindern wollen, dass andere Personen die Datei betrachten können, wählen Sie mit Datei | Speichern unter die Optionen und können hier unter Lese-/Schreibkennwort ein Passwort vergeben. Während die Schnellansicht von Win95 bei so geschützten Dateien die Ansicht verweigert, können Neugierige mit Hilfe der Vorschau im Menü Datei | Öffnen dennoch einen Blick in die Verschlusssachen werfen. Spaltenbreite beim Kopieren erhalten Ich betreibe eine kleine Hausverwaltung unter Excel. Dafür erhält jede Partei eine eigene Tabelle. Um nicht für jede neue Partei alle Rahmendaten erneut eingeben zu müssen, kopiere ich diese aus einer Ausgangstabelle. Beim Kopieren verliere ich aber immer die in der Ausgangstabelle festgelegten Spaltenbreiten. Lässt sich das vermeiden? Um diese Eigenschaften ebenfalls zu kopieren, sollten Sie anstelle der zu kopierenden Zellen die betreffenden Spalten markieren. Das reduziert gleichzeitig die Markierungsarbeiten. Sie klicken nur noch auf die betreffenden Spaltenköpfe. Für Ihr Problem bietet es sich sogar an, gleich das gesamte Arbeitsblatt mit den Rahmendaten zu kopieren. Um eine neue Partei anzulegen, klicken Sie dazu mit der rechten Maustaste auf die Registerkarte des Arbeitsblatts mit den Rahmendaten. Aus dem Kontextmenü wählen Sie den Befehl Verschieben / Kopieren. Im folgenden Dialog geben Sie die Position des neuen Arbeitsblatts an. Achten Sie dabei vor allem darauf, dass der Name der Arbeitsmappe stimmt. Sonst kopieren Sie das neue Arbeitsblatt in eine andere Mappe. Dann brauchen Sie nur noch das Kontrollkästchen Kopieren am unteren Rand des Dialogs zu markieren, und schon befindet sich eine Kopie Ihrer Rahmendaten inklusive der Formatierung an der gewünschten Position. Spaltenüberschriften fixieren Die Spaltenköpfe von Excel lassen sich zwar nicht beschriften, Sie können jedoch die erste Zeile zur Beschriftung nutzen und mit Hilfe einer Fixier-Funktion dafür sorgen, dass Excel den Bereich genauso wie die Spaltenköpfe immer anzeigt. Dann sind die Spaltenüberschriften auch in der x-ten Zeile sichtbar. Excel erlaubt sowohl das Fixieren vertikaler als auch horizontaler Abschnitte. Um einen vertikalen Ausschnitt zu fixieren, markieren Sie den linken Rand des abzutrennenden Bereichs. Wenn Sie beispielsweise die Spalte A konstant anzeigen möchten, markieren Sie die Spalte B. Um einen horizontalen Ausschnitt zu fixieren, gehen Sie analog vor: Markieren Sie die Zeile unterhalb des Bereichs, den Sie abteilen möchten. Falls Sie sowohl eine horizontale als auch eine vertikale Trennung wünschen, markieren Sie die Zelle rechts unterhalb des abzuteilenden Bereichs, z.B. für B2 also C3. Nachdem Sie den Rand des flexiblen Bereichs markiert haben, führen Sie den Befehl Fenster Fixieren aus. Wenn Sie durch den Datenbestand scrollen, zeigt Excel den fixierten Bereich immer an. So sehen Sie Ihre Zeilen- und Spaltenüberschriften sowohl in der zweiten als auch in der tausendsten Zeile. Um eine Fixierung wieder aufzuheben, benutzen Sie den Befehl Fenster Fixierung aufheben. Falls Sie eine Fixierung ändern möchten, müssen Sie diese zunächst aufheben und anschließend neu anlegen. Standard-Einträge per Mausklick aus Kopf- und Fußzeile entfernen Excel gibt bei neu angelegten Dateien in der Mitte der Kopfzeile standardmäßig den Dateinamen und in der Mitte der Fußzeile die Seitenzahlen aus. Meistens ist das auch recht praktisch, aber manchmal sind diese Einträge nicht nötig und müssen einigermaßen umständlich über Datei | Seite einrichten | Kopf- und Fußzeile entfernt werden. Statt dieser Prozedur können Sie auch das unten abgedruckte Makro verwenden. Entscheiden Sie sich, je nachdem, welche Makrosprache Sie verwenden, für eines der beiden Listings und richten Sie das Makro als Schaltfläche in Ihrer Symbolleiste ein. Wenn Sie von nun an leere Kopf- und Fußzeilen wünschen, starten Sie das Makro mit Hilfe dieser Schaltfläche. Es entfernt die von Excel in der Mitte der Kopf- und Fußzeile eingetragenen Platzhalter in der aktuellen Datei. Sub Kopfengl() With ActiveSheet.PageSetup .CenterHeader = "” .CenterFooter = "” End With End Sub Störender Rahmen in Listen Wenn Sie eine Liste über den Befehl Beschriftung betiteln und dann die Ansicht des Tabellenblatts auf 39 Prozent oder weniger verkleinern, fügt Excel den Beschriftungen einen blauen Rahmen hinzu. Damit der Rahmen nicht gedruckt oder angezeigt wird, müssen Sie die Ansicht des Tabellenblatts auf mehr als 39 Prozent vergrößern. Summe über mehrere Tabellen Wenn Sie in einer großen Arbeitsmappe mit mehr als 100 Tabellen jeweils einen Wert pro Tabelle aus einer bestimmten Zelle summieren wollen, eignet sich die normalerweise eingesetzte Formel =Tabelle1!B6+Tabelle2!B6+... nicht mehr, weil der Eingabeaufwand viel zu groß ist. Alternativ können Sie folgendermaßen vorgehen: Schreiben Sie in die Zelle der Auswertungstabelle, die später das Ergebnis enthalten soll, die Summen-Funktion =SUMME(
Nachdem alle Tabellen markiert sind, klicken Sie in die Zelle, die Sie in allen Tabellen summieren wollen. Dann geben Sie die schließende Klammer für die Summen-Formel ein und drücken [Enter]. Wenn es sich z.B. um die Zelle B6 handelt, sieht die Formel folgendermaßen aus: =SUMME(Tabelle1:Tabelle4!B6). Symbolleiste endgültig löschen Symbolleisten lassen sich zwar grundsätzlich an Arbeitsmappen oder Add-Ins binden, das bedeutet aber nur, dass sie beim Laden der entsprechenden Datei hinzugefügt werden. Schließt man die Datei, bleibt die Symbolleiste sichtbar. Auch das Löschen der Datei hilft nicht weiter, denn Excel fügt die Symbolleiste dem eigenen internen Satz von Symbolleisten hinzu. Nur dort kann man sie löschen: Menü Extras-Anpassen wählen und auf die Registerkarte "Symbolleisten" wechseln. Hier den Namen der lästigen Symbolleiste markieren und auf Löschen klicken. Symbolleisten auf anderen PC übertragen Haben Sie die Leisten in Excel per Kontextmenü an Ihre Wünsche angepasst, dann sollten Sie mit Start | Suchen nach der Datei EXCEL8.XLB suchen. Sichern Sie diese Datei und damit Ihre Einstellungen. Sie können die Konfiguration auf andere Rechner übertragen, indem Sie die dortige Datei gleichen Namens mit Ihrer Version überschreiben. Symbolleisten mit Makro ein- und ausblenden Die Schaltfläche Rahmen und Schattierungen erledigt bereits das, was Sie mit Hilfe dieses Tipps für jede beliebige Symbolleiste erreichen können: Ein Klick auf diese Schaltfläche blendet die Symbolleiste Rahmen und Schattierungen ein, ein weiterer blendet sie aus. Mit dem unten aufgeführten Makro können Sie die Symbolleiste Diagramm je nach Bedarf per Mausklick ein- oder ausblenden. Ändern Sie den Makrotext für andere Symbolleisten einfach ab, in dem Sie den Namen der Symbolleiste Diagramm durch den Namen der gewünschten Symbolleiste ersetzen. Um jederzeit Zugang zu diesen nützlichen Makros zu haben, binden Sie sie als Schaltfläche in eine Ihrer immer sichtbaren Symbolleisten ein. Sub Symbol() Wenn SymbolleisteListe("Diagramm”).Sichtbar = Falsch Dann SymbolleisteListe("Diagramm”).Sichtbar = Wahr Sonst SymbolleisteListe("Diagramm”).Sichtbar = Falsch Ende Wenn Ende Sub Tabellennamen im Dokument ausgeben Die Kopfzeile einer Excel-Tabelle enthält standardgemäß einen Platzhalter, der den Namen der aktuellen Tabelle ausgibt. Häufig wird jedoch die Kopfzeile für andere Zwecke, z.B. für Firmenlogos oder Projektüberschriften benötigt. In diesem Fall ist es manchmal erforderlich, den Namen der Tabelle innerhalb des Arbeitsblattes auszugeben. Dies ist über eine Verknüpfung verschiedener Excel-Funktionen möglich. Die Funktion =ZELLE("Dateiname") schreibt den Pfad, Dateinamen und die aktuelle Tabellenbezeichnung in eine Zelle. Möchte man jedoch nicht den kompletten Dateinamen, sondern nur die Bezeichnung des Tabellenblattes ausgeben, ist eine Verknüpfung mit den Funktionen RECHTS, LÄNGE und FINDEN nötig. Um den Tabellennamen in einer Zelle der Tabelle auszugeben, verwenden Sie die Formel =RECHTS ( ZELLE ("Dateiname");LÄNGE(ZELLE("Dateiname")) - FINDEN("]" ; ZELLE("Dateiname"))). RECHTS gibt an, dass nur der rechte Bereich der mit ZELLE ermittelten Zeichenfolge verwendet werden soll. FINDEN sucht nach dem letzten Zeichen der zu entfernenden Zeichenfolge, LÄNGE ist für die Zeichenanzahl der Zeichenfolge verantwortlich. Beachten Sie jedoch bitte, dass diese Formel erst nach dem ersten Speichern der Tabelle oder Arbeitsmappe den Tabellennamen ausgibt. Vor dem Speichern erscheint in der Zelle #WERT. Tabellenüberschriften immer im Blick Bei umfangreichen Tabellen wandern in Excel die Zeilen- und Spaltenüberschriften beim Scrollen aus dem Blickfeld. Damit die Zellbereiche mit den Überschriften ständig zu sehen sind, enthält Excel eine Funktion zum Fixieren der Zellen, die ihren festen Platz auf dem Bildschirm immer behalten sollen. Möchten Sie z.B. die obersten drei Zeilen der Tabelle fixieren, um sie stets im Blick zu haben, markieren Sie die gesamte vierte Zeile mit einem Klick auf die graue Schaltfläche mit der 4 und wählen aus dem Menü Fenster den Eintrag Fixieren. Eine schwarze Linie markiert nun die untere Kante der Fixierung. Genauso können Sie auch die äußersten linken Spalten im Auge behalten, indem Sie die erste Spalte rechts vom Bereich markieren und Fenster | Fixieren wählen. Sollen sowohl die ersten Zeilen als auch die ersten Spalten fixiert bleiben, setzen Sie die Markierung auf die linke oberste Ecke des Bereiches mit den nicht gesperrten Daten. Wählen Sie jetzt Fixieren aus dem Menü Fenster, dann erscheinen die schwarzen Linien oberhalb und links von dieser Zelle, und nur noch der Zellbereich rechts und unterhalb dieser Zelle wird gescrollt. Teilwerte in eine Summe einbeziehen In manchen Tabellen ist es notwendig, nur bestimmte Bereiche in die Berechnung aufzunehmen. Bei einer tabellarischen Aufstellung von Quartalsumsätzen ist es z.B. denkbar, dass Summen über die einzelnen Quartale gebildet werden sollen. Mit der Funktion SUMMEWENN ist das sehr einfach. Möchten Sie die Summen eines bestimmtes Quartals bilden, durchsuchen Sie mit der Formel =SUMMEWENN(B5:N5;”III”;B8:M8) den Bereich B5 bis N5, also die Zeile mit den Quartalsangaben, nach der Zeichenfolge III, durch die das 3. Quartal gekennzeichnet ist. Die Summe wird über die Zellen im Bereich B8:N8 gebildet, vorausgesetzt, dass sich darüber die gesuchte Zeichenfolge befindet. Diese Formel ist sehr flexibel und Sie können in Abhängigkeit von jeder beliebigen Zeichenfolge bestimmte Zellbereiche addieren. Texte verknüpfen Ich habe in der ersten Spalte eine Postleitzahl und in der zweiten Spalte den zugehörigen Ort. In der dritten Spalte soll nun die Postleitzahl erscheinen, gefolgt von dem Ort. Für solche Operationen sollten Sie den Verknüpfungs-Operator & von Excel einsetzen. Um den Inhalt von Zelle A1 mit dem Inhalt der Zelle B1 zu verknüpfen, geben Sie etwa folgende Formel in die Zelle C1 ein: =A1&B1. Um die Postleitzahl vom Ort zu trennen, fügen Sie noch ein Leerzeichen ein: =A1&" "&B1. Nun erweitern Sie den markierten Tabellenbereich mit der Maus auf all jene Zellen, in denen diese Verknüpfung erscheinen soll. Textimport-Assistenten von Excel umgehen Beim Importieren von TXT-Dateien nach Excel muss man sich für jede Datei durch den Textimport-Assistenten kämpfen. Gibt es einen Weg, den Assistenten zu umgehen, und TXT-Dateien einfach nur mit den Standardeinstellungen zu importieren? Ein kleiner aber effektiver Trick hilft weiter: Man wählt das Menü DATEI-ÖFFNEN, markiert die gewünschte TXT-Datei und hält beim Klicken auf OK die Umschalt-Taste gedrückt. Der Assistent wird dann nicht aufgerufen und die Datei ohne Umwege direkt geöffnet. Tippfehler und doppelte Arbeit Excel kann für Sie Listen speichern, wenn Sie diese regelmäßig in fast unveränderter Form eintippen müssen. Um eine solche Liste zu speichern, markieren Sie sie zunächst mit der Maus und klicken auf Extras | Optionen. Gehen Sie dann ins Register AutoAusfüllen und klicken Sie auf Importieren. Excel speichert die Liste nach dieser Aktion im Feld Listeneinträge ab. Um sie zu sichern, klicken Sie nun auf Hinzufügen und dann auf den Button OK. Sollten Sie die Liste künftig brauchen, so geben Sie deren erstes Wort ein und klicken auf das schwarze Ausfüllkästchen. Überschriften beim Drucken auf alle Seiten übernehmen Oft erstrecken sich Tabellen in Excel über mehrere Seiten. Besonders in Listen, deren Inhalt sich häufig ändert, ist es mühsam, die Spaltenüberschriften von Hand einzufügen. Weil Excel die Seitenumbrüche automatisch vornimmt, müsste man immer eine extra Zeile mit den Überschriften einfügen und bei Änderungen verschieben, falls man diesen Tip nicht kennt. Excel enthält eine Funktion, mit der Sie einen Bereich für die Spalten- oder Zeilenüberschrift festlegen können. Wechseln Sie zu diesem Zweck im Menü Datei | Seite einrichten in das Register Tabelle und setzen Sie den Cursor in das Feld Wiederholungszeilen. Markieren Sie dann im Dokument alle Zeilen, die die Überschriften enthalten. Mit der gleichen Methode können Sie nach einem Klick in das Eingabefeld Wiederholungsspalten alle Spalten markieren, die Überschriften enthalten. Allerdings werden Sie die Veränderungen, die Sie am Dokument vorgenommen haben, nicht sofort sehen, sie sind nur auf dem Ausdruck und in der Seitenansicht von Excel sichtbar. Auf die normale Tabellenansicht haben diese Einstellungen keinen Einfluss. VBA-Funktionen allgemein verfügbar machen Wie erreicht man, dass Funktionen ohne Dateinamen eingesetzt werden können, wenn man sie in den Tabellen nutzen will? z.B. =VBAFunktionen.xls!MeineFunktion(12;G6) Der Name der Arbeitsmappe muss der Funktion immer dann vorangestellt werden, wenn man sie in einer Datei verwendet, in der der entsprechende VBA-Code nicht vorhanden ist. Man hat zwei Möglichkeiten, um die Eingabe zu vereinfachen: Die Datei mit den selbst definierten Funktionen als Add-In abspeichern (XLA-Datei). Wenn man diese Datei dann über den Add-In-Manager lädt (Menü Extras laden), stehen alle darin enthaltenen Funktionen allen Arbeitsmappen zur Verfügung, ohne dass man noch einen Dateinamen voranstellen muss. Alternativ dazu hat man die Möglichkeit, gezielt einen Verweis auf die Arbeitsmappe mit den Funktionen zu erstellen. Dazu verwendet man das Menü Extras-Verweise in der VBA-Entwicklungsumgebung. Danach kann man die Funktionsnamen ohne Zusätze in Formeln verwenden. Diese Technik bietet sich immer dann an, wenn man VBA-Funktionen nur in Einzelfällen benötigt und sie in anderen Dateien nicht zur Verfügung stehen soll. Verknüpfungen Ich habe eine alte Excel-Arbeitsmappe kopiert und anschließend alle Daten gelöscht, die Formeln jedoch beibehalten. Wenn ich jetzt allerdings diese Tabelle öffne, erscheint immer die Meldung 'Diese Datei enthält automatische Verknüpfungen zu einer anderen Arbeitsmappe'. Wie kann ich diese Verknüpfungen ebenfalls löschen? Um die Verknüpfung zu löschen, müssen Sie nur die Zelle ausfindig machen, die die Verknüpfung enthält, und die als Formel hinterlegte Verknüpfung löschen. Dazu klicken Sie auf die Zelle und drücken einmal die Taste [Entf]. Normalerweise zeigt Ihnen Excel die betreffende Zelle während der Nachfrage zur Aktualisierung. Sollten Sie allerdings die Zelle nicht gleich ausfindig machen können, brechen Sie die Aktualisierung ab und rufen den Befehl Bearbeiten Verknüpfungen auf. Im folgenden Dialog bekommen Sie eine Übersicht aller Verknüpfungen Ihrer Arbeitsmappe angezeigt. Merken Sie sich den Namen der verknüpften Datei und schließen dann den Dialog. Um die Zelle aufzuspüren, wählen Sie nun den Befehl Bearbeiten Suchen. In das Textfeld Suchen nach geben Sie den Namen der verknüpften Datei ein. Achten Sie darauf, dass das Kombinationsfeld Suchen in auf den Eintrag Formeln eingestellt ist. Wenn Sie anschließend auf den Button Weitersuchen klicken, ermittelt Excel die Zelle mit der Verknüpfung, die Sie jetzt wie oben bereits beschrieben löschen können. Vor- und Nachnamen aufteilen I In einer Tabellenspalte sind die Vor- und Nachnamen der Adressaten durch ein Leerzeichen getrennt eingegeben. Jetzt möchten Sie die Namen und Vornamen getrennt auf zwei Spalten aufteilen, da das für die Verwendung in Serienbriefen flexibler ist. Vorausgesetzt, die kombinierten Namen stehen in der ersten Spalte ab Zelle A1, und die geteilten Vor- und Nachnamen sollen in Spalte 2 und 3 stehen, lösen Sie das Problem recht einfach mit der folgenden Formel: Zelle A1: Vorname [Leerzeichen] Nachname Zelle B1: =LINKS (A1;FINDEN (" ";A1;1)-1) Zelle C1: =RECHTS (A1;LÄNGE (A1)-FINDEN (" ";A1)) Die Formeln bedienen sich der Excel-Funktion FINDEN, um die Namen anhand des Leerzeichens zu trennen. Vergessen Sie nicht, jeweils das Leerzeichen zwischen den Anführungszeichen einzugeben. Wenn Sie die beiden Formeln in den Spalten nach unten kopieren, stehen in Spalte B die Vornamen und in Spalte C die Nachnamen. Vor- und Nachnamen aufteilen II Um die Position des Leerzeichens zu bestimmen, können Sie auch die Funktion SUCHEN verwenden. Sie ermittelt das erste Vorkommen einer Zeichenkette oder eines Zeichens innerhalb einer anderen Zeichenkette. Diese Position übergeben Sie dann an die Funktion TEIL. Sie schneidet aus einem Text ab einer bestimmten Position eine geforderte Anzahl Stellen heraus und liefert diesen Text als Ergebnis zurück. Wenn also der aufzuteilende Name in Zelle A1 steht, erhalten Sie mit folgender einfachen Formel jeweils den Vornamen: =TEIL (A1;1;SUCHEN (" ";A1;1)-1) Entsprechend errechnet die nachstehende Suchformel den Nachnamen: =TEIL (A1;SUCHEN (" ";A1;1)+1; LÄNGE (A1)-SUCHEN (" ";A1;1)) Vergessen Sie dabei nicht, zwischen die Anführungszeichen jeweils das Leerzeichen einzugeben. Um den Vornamen auszulesen, beginnt die erste Formel an der ersten Stelle in der jeweiligen Zeichenkette und berücksichtigt alle Zeichen, die vor dem Leerzeichen stehen. Der Nachname beginnt entsprechend eine Position hinter der Leerstelle und geht bis zum Ende der gesamten Zeichenkette. Das Ende errechnet sich aus dem absoluten Wert für die Länge der Zeichenkette (Funktion LÄNGE) abzüglich des Werts für die Position des Leerzeichens nach dem Vornamen. Währungsangaben anders darstellen Excel verwendet für Zellen, die mit Format | Zellen | Währung formatiert wurden, im Standard das Währungsformat Zahl DM. Möchte man jedoch das ebenfalls gängige Format DM Zahl verwenden, gelingt dies normalerweise nur umständlich über benutzerdefinierte Formate. Soll Excel dieses Währungsformat dauerhaft anbieten, lohnt sich auf jeden Fall die Änderung der Ländereinstellungen. Um diese zu verändern, wechseln Sie nach einem Klick auf die Schaltfläche Start zu Einstellungen | Systemsteuerung und doppelklicken auf das Symbol Ländereinstellungen. Im Register Währung finden Sie das von Excel verwendete Standardformat. Unter Position des Währungssymbols stellen Sie in der Liste das gewünschte Format ein. Das insektenartige Zeichen steht dabei stellvertretend für das landesübliche Währungssymbol. Ändern Sie auch noch das Format für negative Zahlen, und ab sofort bietet Ihnen Excel beim Formatieren von Zellen das gewünschte Währungsformat an. Web-Abfragen Excel bietet die Möglichkeit, Web-Abfragen auszuführen und das Resultat der Abfrage sofort in ein Tabellenblatt einzufügen. So können direkt aus Excel heraus beispielsweise Internet-Suchmaschinen eingesetzt bzw. andere Informationen und Zahlen aktuell und weltweit in einer Tabelle zugänglich gemacht werden. Eine Web-Abfrage realisieren Sie mit einer Textdatei (*.IRQ), die aus drei oder vier Befehlszeilen besteht. Beispieldateien dazu befinden sich im Office-Ordner im Verzeichnis \Abfragen. Der Zugriff selbst erfolgt per Daten | Externe Daten | Web-Abfrage ausführen.... Werte um 10 Prozent reduzieren Sie verringern alle Werte eines Bereichs um 10 Prozent, wenn Sie in eine Zelle 0,9% eingeben. Dann kopieren Sie die Zelle, markieren den Bereich, wählen aus dem Bearbeiten-Menü die Funktion Inhalte einfügen und aktivieren Multiplizieren. Werteanzahl und Summe je nach Auswahlkriterium Sie haben eine Liste mit Bestellungen angelegt und möchten wissen, wie viele Bestellungen in einem bestimmten Preissegment liegen, z.B. alle Bestellungen mit einem Umfang zwischen 100 und 200 Euro. Die Lösung ist eine Arrayformel, in der Sie die Zellinhalte mit Ihrem Auswahlkriterium vergleichen und anschließend die Summe aus allen Vergleichsergebnissen bilden. Angenommen, Sie haben verschiedene Bestellsummen im Bereich C2:C20 eingegeben. Nun wollen Sie sich in Zelle E2 anzeigen lassen, wie viele dieser Bestellungen ein Volumen zwischen 100 und 200 Euro haben. Tragen Sie in E2 folgende Formel ein:
Damit die Formel als Arrayformel erkannt wird, müssen Sie sie mit der Tastenkombination Strg + Umschalt + Return abschließen. Die Eingabe ist korrekt, wenn Excel die Formel in der Bearbeitungszeile mit geschweiften Klammern ({...}) umgibt. Wenn Sie wollen, können Sie die Funktionalität erweitern und in einer weiteren Zelle die Summe aller Bestellungen innerhalb eines bestimmten Wertebereichs ermitteln. Um auszurechnen, wie hoch die Summe der Bestellungen zwischen 100 und 200 Euro ist, geben Sie in Zelle E3 diese Arrayformel (!) ein:
Wörter und Zeichenfolgen in einer Zelle zählen Mit einer Formel können Sie sofort feststellen, wie viele Wörter eine Zelle in Ihrer Arbeitsmappe enthält, auch dann, wenn zwischen zwei Wörtern hier und da mehr als ein Leerzeichen steht. Um die Wörter in A1 zu zählen, verwenden Sie die Formel =LÄNGE(GLÄTTEN(A1))-LÄNGE(WECHSELN(A1;” ";””))+1&” Wörter” LÄNGE ermittelt die Anzahl aller Zeichen in der Zelle A1. Dabei sorgt die Funktion GLÄTTEN dafür, dass zwischen zwei Wörtern immer nur ein Leerzeichen gezählt wird, auch wenn sich mehrere dazwischen befinden. Mit der Kombination LÄNGE und WECHSELN zählen Sie die Anzahl der Zeichen, die keine Leerzeichen sind. Wenn Sie diese beiden Werte voneinander abziehen, erhalten Sie also die Anzahl der Leerzeichen in der Zelle. Und die Anzahl der Leerzeichen plus 1 ergibt wiederum die Anzahl der Wörter. Zahlen als Brüche darstellen In Excel können Sie Zahlen über Format | Zellen | Zahlen | Bruch in den verschiedensten Bruchschreibweisen darstellen. Eine Möglichkeit fehlt allerdings: Die Zahl grundsätzlich als Bruch abzubilden. Möchten Sie z.B., dass 1,25 als 5/4 und 1,33 als der Bruch 4/3 dargestellt wird, eignet sich dafür keine der von Excel standardmäßig angebotenen Schreibweisen. Über ein benutzerdefiniertes Format können Sie diese Möglichkeit jedoch einrichten. Klicken Sie dafür im Register Zahlen der Dialogbox Zellen auf den Eintrag Benutzerdefiniert und geben im Feld Formate die Zeichenfolge ?/? ein. Excel gibt mit diesem Format jede Zahl in Form eines Bruchs mit der kleinstmöglichen einstelligen Zahl im Nenner aus. Zahlen in Klammern Versucht man in Excel, eine Zahl in einer runden Klammer darzustellen, wandelt Excel diese Zahl automatisch in eine negative um. Möchte man eine Klammer verwenden und dennoch mit der positiven Zahl rechnen, gelingt dies über ein benutzerdefiniertes Zahlenformat. Markieren Sie alle Zellen, die Sie mit Klammern versehen wollen, und wechseln Sie in das Menü Format | Zellen. In Kategorien klicken Sie auf Benutzerdefiniert und geben unter Formate: "("0")" ein. Die Zahlen erscheinen in Klammern und bleiben dennoch positiv. Zahlen in Längen- und Breitengrade umwandeln Die Eingabe von Längen- und Breitengraden in Excel ist sehr mühsam, weil außer den Zahlen auch die Angaben E für Ost oder N für Nord und die Minuten und Sekundenstriche eingegeben werden müssen. Mit einem Trick können Sie beide Koordinaten des Punktes der Landkarte gemeinsam in einer Zahl eingeben und Excel die Umwandlung in die Koordinaten erledigen lassen. Tragen Sie in B3 den Längen- und den Breitengrad als Zahl im Textformat ein. Weil Excel bei Feldern im Zahlenformat automatisch führende Nullen entfernt, müssen Sie vor die Zahl das Zeichen Umschalt [#] setzen. Schreiben Sie also in die Zelle zunächst das Zeichen ' und dahinter den Längen- und den Breitengrad in einer Zahl. In den Zellen A8 und A12 halbieren Sie diese Zahl und geben Sie danach in B8 und B12 die Formel ein, die diesen Zahlen die Angabe E für Ost und N für Nord hinzufügt sowie die Minuten- und Sekundenstriche ergänzt. Da Excel mit diesem Wert nicht weiterrechnen kann, möchten Sie die Koordinaten unter Umständen durch Doppelpunkte getrennt darstellen. Verwenden Sie in diesem Fall die untenstehenden Formeln für die Zellen C8 und C12. Achten Sie aber unbedingt darauf, dass Sie die Zeichen E, N, :, ', und '', innerhalb von doppelten Anführungszeichen verwenden. Formeln für die Umwandlung in Längen- und Breitengrade: A8: =RECHTS(B3;6) B8: =TEIL(A8;1;2)&"E"&TEIL(A8;3;2)&"'"&TEIL(A8;5;2)&"''" C8: =TEIL(A8;1;2)&":"&TEIL(A8;3;2)&":"&TEIL(A8;5;2) A12: =LINKS(B3;6) B12: =TEIL(A12;1;2)&"N"&TEIL(A12;3;2)&"'"&TEIL(A12;5;2)&"'" C12: =TEIL(A12;1;2)&":"&TEIL(A12;3;2)&":"&TEIL(A12;5;2) Zahlen ohne Formel potenzieren Multipliziert man eine Zahl mit sich selbst, erhält man als Ergebnis das Quadrat der Zahl. Möchten Sie eine ganze Liste von Zahlen potenzieren, können Sie das z.B. mit einer zweiten Hilfsspalte und einer Formel erreichen. Sie können die Zahlen aber auch direkt und ohne Formeln mit sich selbst multiplizieren. Markieren Sie in Excel alle Zahlen, die Sie in die Quadratzahl verwandeln wollen, und kopieren Sie sie mit [Strg] [C] in die Zwischenablage. Lassen Sie die Markierung bestehen und wählen Sie das Menü Bearbeiten | Inhalte einfügen. Im Bereich Rechenoperation markieren Sie die Option Multiplizieren und bestätigen mit OK. Im markierten Bereich erscheinen nun die in die Potenz erhobenen Werte. Zahlenkolonnen mit unterschiedlich vielen Nachkommastellen am Komma ausrichten Befindet sich in einer Spalte eine Zahlenkolonne mit unterschiedlich vielen Nachkommastellen, ist die Übersichtlichkeit schnell dahin. Möchten Sie die Nachkommastellen nicht angleichen, indem Sie sie z.B. auf zwei oder vier Dezimalstellen runden, stehen die Kommas nicht untereinander. Um Kolonnen von Zahlen, die eine unterschiedliche Anzahl von Dezimalstellen aufweisen, am Komma auszurichten, gehen Sie folgendermaßen vor: Markieren Sie alle Zellen, die Sie ausrichten wollen und wählen Sie im Menü Format den Eintrag Zellen. Im Register Zahlen scrollen Sie dann im Feld Kategorie: nach unten und markieren den Eintrag Benutzerdefiniert. Schreiben Sie nun in die Eingabezeile Formate: die Zeichenfolge 0,?????. Nach einem Klick auf die Schaltfläche OK richten sich alle markierten Zahlen am Komma aus. Allerdings sollten Sie bei der Anzahl der Fragezeichen darauf achten, dass sie mit der höchsten Anzahl der Nachkommastellen im markierten Bereich übereinstimmt. Verwenden Sie zu viele Fragezeichen, sind die Zahlen nach links versetzt, setzen Sie zu wenig Fragezeichen, werden überzählige Dezimalstellen in der Ansicht abgeschnitten, also nicht gerundet, aber um die letzten Stellen beraubt dargestellt. Zahlenwerte einer Tabelle über die Diagrammbalken ändern Glaube keiner Statistik, die du nicht selbst gefälscht hast! Doch auch ausgeklügelte Tricks können manchmal nicht verhindern, dass eine Diagrammsäule in der Umsatzstatistik gar zu popelig wirkt. In diesem Fall können Sie das Ergebnis auch rein visuell etwas schönen. Normalerweise wirken sich in Excel Änderungen der Zahlen, die einem Diagramm zugrunde liegen, auf das Diagramm aus. Bei den Balken- und Säulendiagrammen funktioniert aber auch der umgekehrte Weg. Wenn Sie also die Werte ein wenig "nach Gefühl" anpassen wollen, aktivieren Sie das Diagramm mit einem Doppelklick und markieren die gewünschte Säule mit einem Mausklick. Mit Hilfe der Markierungen können Sie nun die Länge der einzelnen Säulen oder Balken verändern und manipulieren so die dem Diagramm zugrundeliegenden Zahlen. Allerdings funktioniert diese Manipulation nur bei den zweidimensionalen Säulen- und Balkendiagrammen - die 3D-Kollegen sind "fälschungssicher". Zeilen und Spalten einer Tabelle austauschen Oft merkt man erst nach dem Fertigstellen einer umfangreichen Tabelle, dass die Spalten viel besser als Zeilen und die Zeilen besser als Spalten dargestellt wären. Es ist jedoch nicht erforderlich, die Tabelle neu zu erstellen oder ein aufwendiges Makro zum Vertauschen der Zellen und Spalten zu schreiben. Sie können Sie durch einfaches Kopieren und Einfügen nach Belieben drehen. Markieren Sie dafür die ursprüngliche Tabelle und kopieren Sie sie mit [Strg] [C] in die Zwischenablage. Setzen Sie dann den Mauszeiger in die Zelle, in der die oberste linke Zelle der neuen Tabelle erscheinen soll und wählen Sie Bearbeiten | Inhalte einfügen. Aktivieren Sie in der folgenden Dialogbox die Option Transponieren und die ehemaligen Spalten werden nun in Zeilen und die Zeilen in Spalten angeordnet. Zeilenhöhe und Spaltenbreite ändern In Excel gibt es mehrere Methoden, die Höhe von Zeilen oder die Breite von Spalten zu verändern. Die einfachste Methode ist wohl die Verwendung der Maus, indem man über den Rand der Schaltfläche mit der Zeilen- oder Spaltenbeschriftung fährt und die Größe durch Halten und Ziehen mit der Maus verändert. Was so für einzelne Zeilen und Spalten gelingt, funktioniert allerdings auch mit mehreren, unzusammenhängenden Bereichen einer Excel-Tabelle. Halten Sie dazu einfach die [Strg]-Taste gedrückt, während Sie mit der Maus auf alle Zeilenschaltflächen klicken, die Sie markieren wollen. Lassen Sie dann die [Strg]-Taste los und korrigieren Sie die Höhe einer der soeben markierten Zeilen. Sobald Sie die linke Maustaste loslassen, erhalten alle markierten Zeilen diese Höhe. Das gleiche Prinzip können Sie auch für die Änderung der Spaltenbreiten anwenden. Diese Methode ist auch dann praktisch, wenn Sie versehentlich eine Dimension geändert haben und sie wieder auf das Maß der anderen Zeilen oder Spalten angleichen wollen. In diesem Fall markieren Sie alle Zeilen oder Spalten, die die gleiche Höhe oder Breite haben sollen, inklusive derjenigen, die versehentlich aus der Reihe tanzt. So bringen Sie alle markierten Bereiche im Handumdrehen auf dasselbe Maß. Zeilenhöhe und Spaltenbreite ermitteln Wollen Sie in Excel die Breite einer Spalte ermitteln, müssen Sie nicht extra über Format | Spalte | Breite ein kleines Fenster öffnen, das Ihnen die Spaltenbreite mitteilt. Viel schneller erhalten Sie diese Information, wenn Sie mit der Maus im Spaltenkopf zwischen diese und die nächste Spalte klicken. Mit der gleichen Methode können Sie auch die Höhe einer Zeile in Erfahrung zu bringen, ohne ein Menü bemühen zu müssen. Zeitdifferenz berechnen Wie kann man die Differenz von 08:00 Uhr minus 10:00 Uhr = - 02:00 Uhr berechnen? Geben Sie in das Ergebnisfeld folgende Formel ein: =WENN(A4-A3<0;"-"&TEXT((A4-A3)*-1;"[hh]:mm");A4-A3) Das Ergebnis wird korrekt (-02:00) angezeigt. Dies wird jedoch als Text angezeigt, weitere Berechnungen sind damit nicht möglich. Zeitwerte in Minuten umrechnen Wenn Sie in Excel Zeiten wie beispielsweise "5 Stunden und 12 Minuten" eingeben wollen, schreiben Sie normalerweise in eine Zelle 5:12. Excel erkennt anhand des Doppelpunktes selbständig, dass es sich hier um eine Zeitangabe handelt, und formatiert die Zelle entsprechend. In manchen Fällen ist es jedoch erforderlich, eine mehrstündige Zeitspanne nicht in Stunden und Minuten, sondern komplett als Minutenwert auszugeben. Möchten Sie einen Zeitraum in Minuten anzeigen, benötigen Sie eine entsprechende Formel. Mit der Formel =Stunde(A1)* 60+Minute(A1) erhalten Sie die Anzahl der Minuten des Zeitraums in der Zelle A1. Zellbereiche per Doppelklick ausfüllen Wenn Sie in einer Excel-Zelle eine Formel geschrieben haben, können Sie diese ganz einfach nach unten kopieren, indem Sie den Mauszeiger über die rechte untere Ecke der Zelle bewegen, die Maustaste drücken und bei gedrückter Maustaste einen Bereich aufziehen, in den Sie die Formel kopieren wollen. Dieses sehr praktische automatische Ausfüllen passt die Zellbezüge automatisch an und eignet sich sowohl zum Kopieren nach unten, als auch nach rechts. Allerdings ist es etwas mühsam, umfangreiche Zellbereiche auf diese Weise auszufüllen. Möchten Sie eine Formel über einen sehr großen Zellbereich nach unten kopieren, ist es praktischer, auf die rechte untere Ecke der Zelle, die die Formel enthält, doppelt zu klicken. Excel füllt die Spalte dann so weit nach unten mit der Formel aus, bis links daneben eine leere Zelle auftaucht. Wenn Sie also die erste Spalte von Hand ausfüllen, erkennt Excel automatisch, wie weit es die anderen Spalten bei einem Doppelklick ausfüllen muss. Zellbezug bei Formeln Die intelligente Verwaltung von Zellbezügen von Excel macht dem Anwender manchmal bei ganz einfachen Aktionen das Leben schwer. Möchte man z.B. erreichen, dass sich eine Formel immer auf die Spalte A bezieht, und im Fall, dass diese Spalte gelöscht wird, die Zellen der nachrückenden Spalte B verwendet werden, ist dies mit den gängigen Bezugsarten nicht zu erreichen. Verwendet man den relativen Bezug =A1+12, so gibt diese Formel beim Löschen der Spalte A die Fehlermeldung #BEZUG! aus. Setzt man den Zellbezug mit =$A$1+12 absolut, erhält man das gleiche Ergebnis. Möchten Sie, dass sich eine Formel grundsätzlich auf eine bestimmte Zelle einer Excel-Tabelle bezieht und den Wert nach dem Löschen der Spalte aus der nachrückenden Spalte nimmt, so verwenden Sie folgende Schreibweise: =INDIREKT("A1")+12. In diesem Fall bezieht sich die Formel immer auf die aktuelle Zelle A1. Dies gilt auch dann, wenn sie gelöscht und deshalb durch eine nachrückende Zelle ersetzt wird. Zellbezüge schnell absolut setzen In Formeln wird zwischen absoluten und relativen Zellbezügen unterschieden. Relative Bezüge gleicht Excel beim Kopieren oder Verschieben einer Formel immer an die neue Position der Formel an. Soll sich die Formel immer an einer bestimmten Zelle orientieren, egal, wohin die Formel kopiert oder verschoben wird, kommt das $-Zeichen zum Einsatz, das diese Bezüge absolut setzt. Möchte man die Zelle A1 in einer Formel absolut setzen, wäre die Schreibweise $A$1. Das erste $-Zeichen setzt die Spalte A absolut, das zweite die Zeile 1. Es gibt auch Kombinationen aus absoluten und variablen Zellbezügen, bei denen etwa die Zeile absolut und die Spalte relativ gesetzt wird. Wenn Sie häufig mit absoluten Zellbezügen arbeiten, müssen Sie nicht jedesmal die $-Zeichen von Hand setzen. Bewegen Sie einfach den Cursor über den gewünschten Bezug und betätigen die Funktionstaste [F4]. Excel setzt diese Formel absolut, indem es jeweils ein $-Zeichen vor die Spalten- und Zeilenangabe einfügt. Nochmaliges Betätigen setzt nur die Zeile absolut, ein drittes Mal die Spalte und mit dem vierten Drücken der Taste [F4] wird der Bezug wieder relativ, also frei von $-Zeichen. Sie können die [F4]-Taste auch zum nachträglichen Bearbeiten umfangreicher Formeln verwenden. Setzen Sie einfach den Cursor auf die Zeile, die Sie bearbeiten wollen, und bringen Sie die Formel mit der Taste [F2] in den Bearbeitungsmodus. Setzen Sie nun den Mauszeiger auf den ersten Zellbezug, den Sie ändern wollen, und betätigen die Taste [F4]. So können Sie nachträglich bei jedem Bezug in der Formel entscheiden, ob und auf welche Art Sie ihn absolut setzen wollen. Zellen ein- und ausblenden Excel bietet in den Symbolleisten nicht alle Funktionen an, sondern hat noch einige Asse im Ärmel. Besonders praktisch: Die Schaltfläche Sichtbare Zellen markieren, die Ihnen die Arbeit mit ausgeblendeten Zeilen oder Spalten sehr erleichtert. Wenn Sie diese Schaltfläche ausprobieren wollen, klicken Sie eine Symbolleiste mit der rechten Maustaste an und wählen aus dem Kontextmenü Benutzerdefiniert. Markieren Sie unter Kategorien den Eintrag Werkzeug und ziehen Sie dann die zweite Schaltfläche der zweiten Reihe per Drag-and-Drop in eine der vorhandenen Symbolleisten. Schließen Sie jetzt die Dialogbox Benutzerdefiniert und Sie können Ihre neue Funktion testen. Angenommen, Sie haben in einer umfangreichen Tabelle einige Zeilen oder Spalten ausgeblendet und möchten nun alle Spaltenbreiten der sichtbaren Spalten markieren. Die ausgeblendeten Spalten sollen von den Änderungen unbeeinflußt bleiben. Markieren Sie für diesen Fall die gesamte Tabelle mit [Strg] [A] und klicken Sie dann auf die neue Schaltfläche. Alle Änderungen, die Sie nun an den Spaltenbreiten vornehmen, betreffen nur noch die sichtbaren, also nicht ausgeblendeten Zellen. Zweites Beispiel: Sie haben eine Tabelle mit Werten, die Sie addieren wollen. Einige Zeilen sind ausgeblendet, weil sie im Moment nicht von Belang sind. Wenn Sie jetzt eine Summe bilden, werden normalerweise auch die Werte der ausgeblendeten Zeilen mit in die Berechnung aufgenommen. Wenn Sie aber auf den Button Summe klicken, anschließend den Zellbereich markieren, danach auf die neue Schaltfläche Sichtbare Zellen markieren klicken und erst dann mit [Return] bestätigen, werden die ausgeblendeten Werte ignoriert. Oder Sie möchten nur mit dem sichtbaren Bereich einer Tabelle weiter arbeiten. In diesem Fall müssen Sie nicht alle ausgeblendeten Zeilen und Spalten löschen. Markieren Sie einfach den Bereich der Tabelle, der Sie interessiert und betätigen Sie die neue Schaltfläche. Wenn Sie nun die Markierung mit [Strg] [C] in die Zwischenablage kopieren und mit [Strg] [V] in einem neuen Tabellenblatt einfügen, erscheinen dort nur noch die sichtbaren Zellen. Zellen im Zirkelbezug suchen Ein Zirkelbezug liegt vor, wenn sich eine Formel direkt oder indirekt auf die Zelle bezieht, in der sie steht. Stößt Excel in einer Arbeitsmappe auf einen Zirkelbezug, verweigert es die Berechnung. Dann hilft die Zirkelbezug-Symbolleiste mit Optionen zur Bearbeitung. Um sie einzublenden, wählen Sie Extras | Anpassen, klicken auf die Registerkarte Symbolleisten und aktivieren das Kontrollkästchen Zirkelbezug. Die Statusleiste zeigt Zirkelb. an, gefolgt vom Hinweis auf eine Zelle, die im Zirkelbezug enthalten ist. Klicken Sie in der Zirkelbezug-Leiste auf die erste Zelle im Feld Zirkelbezug analysieren. Prüfen Sie diese Formel. Können Sie nicht feststellen, ob hier die Ursache für den Zirkelbezug ist, untersuchen Sie die nächste Zelle. Fahren Sie mit der Überprüfung und Korrektur fort, bis in der Statusleiste die Anzeige Zirkelb. verschwindet. Wird Zirkelb. ohne Angabe einer Zelle angezeigt, ist der Zirkelbezug nicht im aktiven Tabellenblatt aufgetreten. Dann müssen Sie die Suche ausdehnen. Einige technische und wissenschaftliche Formeln erfordern Zirkelbezüge. In solchen Fällen müssen Sie die Anzahl der Iterationen ändern. Dazu klicken Sie im Menü Extras auf Optionen | Berechnen. Aktivieren Sie nun das Kontrollkästchen Iteration und legen dann die maximale Anzahl von Iterationen und den Änderungswert fest. Zellinhalte trennen Mit Hilfe der Funktion Daten | Text in Spalten können Sie Zellinhalte aufteilen. Sie müssen vorher lediglich dafür sorgen, dass die Zellen rechts neben den Zellen, die Sie aufsplitten wollen, frei sind. Dann markieren Sie die Zellen oder die gesamte Spalte und wählen die Funktion Text in Spalten im Menü Daten. Im daraufhin erscheinenden Textassistenten klicken Sie zunächst auf Weiter und aktivieren dann die Option Leerzeichen im Bereich Trennzeichen. In der Vorschau im unteren Bereich des Fensters erkennen Sie bereits, dass die Daten in zwei Spalten aufgeteilt wurden. Mit einem Klick auf die Schaltfläche Ende in der rechten unteren Fensterecke können Sie den Vorgang bestätigen, und der Zellinhalt wird auf die markierten und die direkt rechts angrenzenden Zellen verteilt. Zellnotizen in ganzer Länge eintippen Excel erlaubt es, über Einfügen und Notiz in markierte Zellen kurze Anmerkungen zu schreiben, die beispielsweise die Formeln kommentieren oder einzelne Geschäftszahlen interpretieren. Zwar können Sie einiges an Text in diese Notizen eingeben, doch Excel schneidet später oft etwas von Ihren Anmerkungen ab. Haben Sie etwa eine komplizierte Formel kommentiert, so ist dieser Effekt natürlich ärgerlich. Aus diesem Grund sollten Sie keine zu langen Wörter eintragen oder derartige Begriffe sowohl mit einem Bindestrich als auch einem Leerzeichen trennen. Excel stellt die Notiz dann umbrochen dar, und Sie erkennen alle Ihre persönlichen Einträge sofort wieder. In Excel nutzen Sie alternativ das Kommando Kommentar einfügen im Kontextmenü jeder Zelle. Damit öffnen Sie ein Notizfenster, das in seiner Größe skalierbar ist, also keine speziell angepassten Fonts benötigt. Zellnotizen über das Kontextmenü eingeben Mit der Notizfunktion von Excel können Sie Kommentare und Hinweise für einzelne Zellen erstellen. Zellnotizen sind z.B. praktisch, um dem Benutzer der Tabelle Eingabehinweise oder Erläuterungen von Formeln zu vermitteln. An einem roten Punkt in der oberen rechten Ecke der Zelle erkennt man, dass sie eine Notiz enthält, bewegt man den Mauszeiger über die Zelle, erscheint die Notiz. In Excel ist es jedoch ziemlich umständlich, eine Zelle mit einer Notiz zu versehen. Man muss die Zelle anklicken und anschließend im Menü Einfügen den Eintrag Notiz ansteuern, bevor man die Nachricht schließlich eingeben kann. Viel praktischer ist es, die Notizfunktion in das Kontextmenü der rechten Maustaste aufzunehmen, damit die Funktion nach einem rechten Mausklick auf eine Zelle direkt angewählt werden kann. Dafür müssen Sie zunächst mit Einfügen | Makro | Visual Basic-Modul ein neues Makro mit dem unten stehenden Inhalt anlegen. Damit das Makro im Kontextmenü der Zellen erscheint, müssen Sie es dort aufnehmen. Bleiben Sie dafür im Modulblatt und wechseln Sie mit Extras zum Menü-Editor. Im Auswahlfeld Menüleisten wählen Sie zunächst Kontextmenüs 1 aus und klicken dann im Bereich Menüs auf Zelle (Tabellen). Markieren Sie unter Menüelemente den Punkt Ende des Menüs und betätigen Sie die Schaltfläche Einfügen. Das Kontextmenü soll vor dem neuen Eintrag einen Teilungsstrich erhalten. Schreiben Sie deshalb in das Feld Titel ein Minuszeichen und setzen Sie dann die Markierung nochmals auf Ende des Menüs. Betätigen Sie wieder Einfügen und schreiben Sie Notizen in das Feld Titel. Aus dem Klappmenü Makro wählen Sie das zuvor angelegte Makro Notiz Einblenden aus und bestätigen anschließend mit OK. Wenn Sie jetzt mit der rechten Maustaste auf eine Zelle klicken, gelangen Sie über Notizen direkt in die Dialogbox Einfügen | Notizen. Makro für die deutsche Version: Sub NotizEinblenden() Anwendung.DialogListe(xlDialogNotiz).Zeigen Ende Sub Makro für die englische Version: Sub NotizEinblenden() Application.Dialogs(xlDialogNote).Show End Sub Zirkelbezüge vermeiden Ich möchte unter Excel zum aktuellen Wert in der Zelle A1 kontinuierlich die veränderten Werte einer anderen Zelle addieren. Wenn ich jedoch in die Zelle A1 die Formel =A1+B1 eingebe, meldet Excel einen Zirkelbezug und bricht ab. Excel muss an einer Stelle mit seinen Berechnungen anfangen können. In Ihrem Beispiel benötigt Excel aber bereits zur Berechnung des ersten Wertes für die Zelle A1 auch den aktuellen Wert aus eben dieser Zelle. Sie sind wahrscheinlich und ganz logisch davon ausgegangen, dass die Formel einen zeitlichen Ablauf hat: Das Ergebnis in A1 soll die Summe zweier Werte darstellen, von denen einer vorher schon in der Zelle A1 gestanden hat. Eine Tabellenkalkulation wie Excel kann jedoch immer nur die Werte aller Zellen zu genau einem Zeitpunkt unterscheiden. Bei Formeln, die dieser Logik widersprechen, beißen sich die Berechnungen quasi selbst in den Schwanz. Genau dann liegt auch ein Zirkelbezug vor, auf den Excel mit einer entsprechenden Meldung hinweist. Auf Wunsch kann Excel den Bezug sogar grafisch mit Linien und Pfeilen verdeutlichen, was besonders bei großen Arbeitsblättern von Vorteil ist. Um den Zirkelbezug in Ihrem Fall zu umgehen, benötigen Sie einen Speicher, der den Wert aus A1 oder die Formel für die Zeit der Berechnung zwischenlagert. Dieser Speicher kann beispielsweise eine weitere Zelle sein, die Sie mit einem Makro verwenden. Um ein neues Makro anzulegen, führen Sie den Befehl Extras Makro Makros aus und geben in das Feld Makroname den gewünschten Namen ein. Anschließend klicken Sie auf die Schaltfläche Erstellen und geben die Zeilen aus dem untenstehenden Listing ein. Die erste Zeile bewegt die Einfügemarke relativ zur aktuellen Position eine Zelle nach unten (im Beispiel von B1 nach B2) und fügt dort eine Formel zur Addition der Werte aus den Zellen links und direkt darüber ein (im Beispiel =A1+B1). Anschließend kopiert das Makro den Inhalt der aktiven Zelle in die Zwischenablage und wechselt in die Zelle für den Ergebniswert (im Beispiel A1). Dort überschreibt das Makro den Inhalt mit dem Wert der soeben kopierten Summe. Zum Abschluss löscht das Makro noch die zur Berechnung erzeugte Formel, um alles wieder sauber aufzuräumen. Wenn Sie das Makro mehrfach hintereinander ausführen, erhalten Sie den gewünschten Effekt für das kontinuierliche Aufsummieren des Wertes in A1. Dafür sollten Sie das Makro mit einer Tastenkombination verknüpfen. Klicken Sie dazu im Makro-Dialog erst auf den Makronamen und dann auf die Schaltfläche Optionen. Die Zelladressen sind im Beispielmakro relativ. Sie können es dadurch an jeder Stelle in einer Excel-Tabelle verwenden, solange das Eingabefeld direkt rechts vom Ergebnisfeld liegt. Falls Ihnen dies nicht zusagt, passen Sie jeweils die Parameter der Methode Range im Makro Ihren Wünschen an. Der erste Parameter bestimmt immer die vertikale und der zweite die horizontale Bewegung. Sub Zirkelbezug() ActiveCell.Offset(1,0).Range("A1").Select ActiveCell.FormulaR1C1="=SUM(R[-1]C[-1]:R[-1]C)" ActiveCell.Select Selection.Copy ActiveCell.Offset(-1,-1).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveCell.Offset(1,1).Range("A1").Select Application.CutCopyMode = False Selection.ClearContents ActiveCell.Offset(-1,0).Range("A1").Select End Sub Zugriff auf bestimmte Tabellen der Arbeitsmappe In umfangreichen Arbeitsmappen mit zahlreichen Tabellenblättern ist die Bewegung zwischen den einzelnen Seiten mit Hilfe der Laufbalken oft etwas mühsam. Möchten Sie besonders flott von einer zur anderen Tabelle wechseln, klicken Sie die Pfeiltasten, mit denen Sie sich normalerweise zu entfernten Tabellen bewegen, mit der rechten Maustaste an. In dem sich darauf öffnenden Pop-up-Menü erscheint eine Liste aller Tabellenblätter der Arbeitsmappe. Wählen Sie darin mit Hilfe eines Mausklicks den gewünschten Namen aus, und Sie landen sofort in der entsprechenden Tabelle. Zwischensummen nicht in Gesamtsumme einbeziehen Wenn Sie in Zahlenkolonnen hin und wieder Zwischensummen einfügen wollen, ergibt sich ein rechnerisches Problem: Wenn die Tabellenkalkulation Excel die Summe aus allen Zellen oberhalb der aktiven Zelle bilden soll, werden Zwischensummen, die sich in diesem Bereich befinden, ebenfalls hinzugezählt. Für korrekte Ergebnisse müssten Sie also bei jeder Summenformel die Zwischenergebnisse subtrahieren. Das lässt sich verhindern, indem man die Zwischensummen nicht in derselben Spalte wie die Zahlenkolonne, sondern in einer anderen Spalte berechnet. Falls das bei Ihnen nicht möglich ist und Sie gezwungen sind, die Zwischensummen in dieselben Spalten wie die Beträge zu schreiben, können Sie mit einer speziellen Formel erreichen, dass die Zwischensummen einer Spalte beim Summieren ignoriert und somit nicht mitgezählt werden. Definieren Sie dafür sämtliche Zwischensummen als Text. Möchten Sie z.B. eine Zwischensumme der Beträge in den Zellen F10 bis F17 bilden, so lautet die Formel =TEXT(SUMME(F10:F17);"#.##0,00") Am Ende der Formel wird noch definiert, in welchem Zahlenformat die Zwischensumme dargestellt wird. Wenn Sie mit Hilfe dieser Formel die Zwischensummen einer Zahlenkolonne ermitteln, dann werden diese Beträge von der Summenformel ignoriert. Trotzdem können Sie mit diesem Ergebnis rechnen: Wenn Sie die Summe nicht mit der Funktion =SUMME(A1:A2), sondern mit Hilfe des Plus-Zeichens, also in der Form =A1+A2, bilden, erkennt Excel die Ergebnisse als Zahl an und bezieht sie in die Berechnung ein. |
zurück