Häufig gibt es in Excel-Berechnungen einige Zellen, die in mehreren Formeln weiterverwendet werden.
Um diese Formeln leichter lesbar zu machen und sie auch leichter schreiben zu können, haben Sie die Möglichkeit, einzelne Zellen und ganze Zellbereiche mit Namen zu versehen. Beispielsweise benennen Sie den Bereich $A$5:$A$55 mit „Umsatz“, kann die Summe über diese Zellen dann einfach mit =SUMME(Umsatz) berechnet werden.
Markieren Sie dazu einfach die benötigten Zelle(n) und schreiben dann in das Namenfeld (in der Bearbeitenleiste links) den gewünschten Namen. Vergessen Sie hier nicht, die Eingabe mit der Enter-Taste zu beenden. Wenn Sie dann das Aufklappfeld rechts am Namenfeld klicken, finden Sie den vergebenen Namen in der Liste. Sie können diese Liste zusätzlich als GoTo-Liste verwenden, denn nach der Auswahl werden die zuvor markierten Zellen wieder markiert angezeigt. Auch wenn dazu das Blatt gewechselt werden muss.
Umfassende Unterstützung für die Namensvergabe bietet die Gruppe „Definierte Namen“ auf der Registerkarte Formeln. Sie finden dort den Namensmanager, der alle Namen mit deren Zellbezügen, Gültigkeitsbereichen und aktuellen Werten auflistet. Hier können Sie auch neue Namen erstellen, Namen umbenennen, Bezüge korrigieren sowie Namen löschen.
Daneben gibt es noch einige Schaltflächen, um Namen auf verschiedene Weisen effektiv zu vergeben. Haben Sie die zu benennenden Zellen markiert, klicken Sie auf „Namen definieren“. Excel analysiert die darüber liegende und die linke Zelle, ob eine passende Benennung zu finden ist und schlägt diese meist vor. Haben Sie die Zelle mit einer geeigneten Benennung schon mitmarkiert, wie beispielsweise die Spaltenbezeichnungen einer Tabelle, dann klicken Sie auf „Aus Auswahl erstellen“. Excel fragt noch einmal nach, ob die Namen aus der oberen Zeile oder der linken Spalte verwendet werden sollen, und vergibt entsprechend die Namen. Dabei wird die Beschriftungszeile oder -spalte nicht mit in den Bezug genommen.
Sie können also mit zwei Klicks eine ganze Spalte mit Eingangsgrößen aus den Beschriftungen benennen, oder eine komplette Tabelle mit Bereichsnamen für jede Spalte versehen.
Register „Daten“, Gruppe „Sortieren und Filtern“, Schaltfläche „Erweitert…“
Dieser Tipp ist für die Controlling-Profis unter Ihnen, die regelmäßig das Erweiterte Filter (Spezialfilter) anwenden. Speichern Sie verschiedene Filterkriterien in einem Datenblatt, um entweder die Datenbank-Funktionen von Excel zu nutzen, oder um auch nur eine größere Tabelle zu filtern, ohne jedes Mal die Filterkriterien in die Autofilter der Spalten neu eingeben zu müssen.
Für das erweiterte Filter muss des Dialogformular des Spezialfilters ausgefüllt werden. In diesem Formular sind die Adressen der Quelltabelle (Listenbereich in diesem oder in einem anderen Blatt), des Kriterienbereiches und gegebenenfalls des Zielbereichs sowie die Duplikatebereinigung anzugeben.
Spezialfilter-Formular, ausgefüllt mit Bereichsnamen anstelle von Adressbezügen
Häufig werden aufgezeichnete Makros verwendet. Sie werden anschließend einer Schaltfläche zugewiesen. Vor dem Aufzeichnen empfiehlt es sich, die drei Bereiche mit Namen zu versehen. Im aufgezeichneten Makro stehen dann die Bereichsnamen in der VBA-Zeile. Der VBA-Code wird dadurch unabhängig von Zeilen und Spalten.
Nun zum Tipp
Für eine besonders flexible Abfrage verwenden Sie einen Kriterienbereich mit etwas mehr Zeilen und Spalten als gewohnt, beispielsweise mit 3 Spalten und 3 Zeilen (oder mehr). Benennen Sie den Bereich der neun Kriterienfelder und ihrer drei Spaltenköpfe etwa mit krit3x3. Geben Sie dann krit3x3 in das Spezialfilter-Dialogformular als Kriterienbereich ein. Schließen Sie den Vorgang mit Klick auf die OK-Schaltfläche ab.
Zeichnen Sie diesen Vorgang des Dialogausfüllens und des OK mit dem Makrorekorder auf. Der VBA-Code heißt dann etwa
Sub Filter3x3()
Range("Rohdaten").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Zieltabelle"), _
CriteriaRange:=Range("krit3x3"), _
Unique:=False
End Sub
Mehr wird zum Filtern nicht benötigt. Setzen Sie sich eine Schaltfläche oder ein Icon in das Rechenblatt und weisen ihm die Filterprozedur zu.
Flexibler Kriterienbereich und Schaltfläche zum Auslösen des Filterbefehls
Nun lassen sich die Spalten im Kriterienbereich krit3x3 beliebig umbenennen, und Sie können mehrere Kriterien einfügen. Kriterien in einer Zeile sind UND-Kriterien, die in verschiedenen Zeilen sind ODER-Kriterien. Für nicht verwendete Zeilen geben Sie FALSCH, XXX oder etwas anderes, nicht Vorhandenes ein. Dann klicken Sie auf Ihre Schaltfläche und erhalten das Filterergebnis.
Der benannte Bereich krit3x3 ist farblich hervorgehoben, beschriftet und mit einer Schaltfläche ergänzt. Im Bild werden nur zwei Zeile für das Kriterium benötigt. Denken Sie daran: Was in einer Zeile steht, sind UND-Filter (gleichzeitige Erfüllung), was in einer anderen Zeile steht, sind ODER-Kriterien (alternative Bedingung).
Gemeint ist das kleine schwarze Kreuz, in welches sich der Mauszeiger verwandelt, wenn er sich der Ecke unten rechts an einer Zelle im Ecxel-Arbeitsblatt nähert. Anfänglich gehört etwas Fingerspitzengefühl dazu, die Ecke genau zu treffen und dann das schwarze Kreuz mit der linken Maustaste festzuhalten und es in einer der Himmelsrichtungen zu ziehen.
Die Autoausfüllfunktion
Klickt man das kleine schwarze Kreuz an und hält es mit der linken Maustaste fest, kann man damit über die davor, dahinter, darüber oder darunter liegenden Zellen streifen und dann loslassen. Dabei bleibt das ursprüngliche Feld umrandet und an seiner Stelle. Die nun überstrichen Felder sind ebenfalls markiert. Während des Überstreichens erscheint häufig ein Smart-Info-Feldchen mit den gerade erreichten Zwischenwerten. Nach dem Loslassen steht am Ende des markierten Bereiches ein Smart-Info-Feld mit den Optionen der Auto-Ausfüllen-Funktion.
Hier folgt nun eine Reihe von Anwendungsfällen der Auto-Ausfüllen-Funktion.
Ein Smart-Tag wird angezeigt
Nummerierten Ausdruck nach unten oder nach rechts fortschreiben
Ausdruck, der auf eine ganze Zahl endet, fortschreiben
Nach „Quartal 1“ folgt „Quartal 2“ und so weiter , nach „Quartal 4“ folgt „Quartal 1“.
Alle Ausdrücke, die als benutzerdefinierte Liste festgelegt sind, werden fortgeschrieben; beispielsweise Wochentagnamen, Monatsnamen und deren Abkürzungen.
Datumswerte, egal in welcher Formatierung, werden fortgeschrieben. Dabei kann in Tagen, Wochen, Monaten und Jahren weitergezählt werden.
Formate werden fortgeschrieben, auch ausschließlich, wenn Sie das in den Optionen so auswählen.
Beim Formatieren eines Titels über mehrere Spalten reicht häufig der Unterstrich der Formatvorlage nicht aus.
Formeln werden unter Berücksichtigung von absoluten Adressen fortgeschrieben, wobei relative Adressen sich entsprechend verändern.
Es können zwei Zellen markiert werden. Bei Zahlenwerten wird mit der Differenz der beiden jeder Folgewert berechnet.
Werden mehr als zwei Werte markiert, werden alle Folgewerte mit Hilfe der linearen Regression berechnet.
Werden zwei oder mehr Textwerte markiert, werden diese kontinuierlich wiederholt.
Werden Funktionen nach rechts oder links erweitert, die Kopfbereichsbezüge von sogenannten intelligenten Tabellen enthalten, werden diese wie relative Bezüge behandelt, verändern sich also. Zum Vergleich: Benannte Bereiche haben immer absolute Adressen, ihre Bezüge werden beim Autoausfüllen beibehalten.
Zahlen, die in Argumenten von Funktionen stehen, werden vom kleinen Schwarzen nicht verändert. Benötigt man hier eine mitlaufende Zahl, sollte man den Einsatz der Funktionen SPALTE () oder ZEILE () prüfen.
Weil die Verwendung der Auto-Ausfüllen-Funktion mit dem kleinen schwarzen Kreuz so vielseitig wie die eines schwarzen Kleides ist, hat sie bei uns den Namen „das kleine Schwarze“ bekommen.
Es gibt in den aktuellen Excel-Versionen leider keine Funktion, mit der man den Namen des aktuellen Benutzerkontos, den Windows-Anmeldenamen ermitteln kann. Nicht mal den Office-Benutzernamen, mit dem jeder seine Excel-Kopie personalisieren kann – in den Excel-Optionen im Blatt Allgemein.
Ehe Sie hier VBA-Funktionen bemühen, die teilweise auf die zu deklarierende advapi.dll zurückgreifen, hilft eine Funktion aus Excel der Version 4. In neueren Excel-Versionen werden die alten, EXCEL4-Makro genannten Funktionen nicht mehr in einer Zelle funktionieren, aber in Excel 2010 und 2013 geht das noch, allerdings nur im Namensmanager. Gemeint ist die Funktion DATEI.ZUORDNEN(36), der Parameter 36 steht für den Windows-Benutzernamen.
Unser Tipp:
Legen Sie im Namensmanager einen neuen Namen, beispielsweise „Benutzer“, fest und schreiben in die Bezugszeile
=DATEI.ZUORDNEN(36)
Benötigen Sie den Namen dann in einer Zelle oder in einer Formel im Datenblatt, um etwa Berechtigungen zu prüfen, setzen Sie den definierten Namen mit Gleichheitszeichen ein, also
=Benutzer
Anwendung der festgelegten Namen
VBA-Alternativen
Schreiben Sie in einen Standardmodul je eine VBA-Funktion mit den folgenden Zuweisungen:
Function BenutzerNameO() As String
BenutzerNameO = Application.UserName
End Function
(der Name aus den Optionen) oder
Function BenutzerNameW() As String
BenutzerNameW = Environ("Username")
End Function
(der Windows-Anmeldename)
Diese Funktionen erscheinen nach dem Speichern auf der Excel-Seite im Funktionsassistenten unter „benutzerdefinierte Funktionen“ und können in jeder Zelle verwendet werden:
Gerade habe ich einen Artikel von Alfred Müllerschön gelesen, der das Schwinden einer strategischen Personalentwicklung beschreibt, da die Unternehmen nur noch eine mittelfristige Planung betreiben. Absatzmärkte und Personalangebote ändern sich derzeit so rapide, dass flexibles Reagieren auf der Tagesordnung ist. Folglich sei es auch wichtig, eher auf die Anpassung des Wissensstandes an die aktuellen Herausforderungen am konkreten Arbeitsplatz abzuzielen, sowie auf die Abstimmung der Kompetenz der Führungskräfte oder Projektleiter hinsichtlich des Führungs- und Projektmanagementverständnisses innerhalb eines Unternehmens.
Ob nicht dennoch die Unternehmen übrig bleiben, die neben den aktuellen Herausforderungen, dem steigenden Einsatz von Leiharbeitern und den ständigen Aufgabenwechseln auch eine strategische Ausrichtung und wachsende innerbetriebliche Kompetenz auf ihrem Alleinstellungsmerkmal verfolgen, will ich hier gar nicht diskutieren.
Meine Antwort ist das Angebot von Einzeltrainings und Floorworking. Dazu gehört auch die Befähigung der zu Trainierenden, sich mit Online-Trainings fehlende Kompetenzen zu erschließen. Im Floorworking erfolgt nicht nur die Beratung Einzelner. Kleingruppen können sich moderiert hinsichtlich der internen Abläufe und des Einsatzes bestimmter und geeigneter Werkzeuge der vorhandenen Office-Software abstimmen. Dabei gewinnt die individuelle Kompetenz und die Schlagkraft des Teams.
Ich habe es in etlichen Firmentrainings erlebt, wie Einzelne und Teams aus Controlling, Produktionsvorbereitung und Management hier sehr gut und zu ihrer eigenen Zufriedenheit sich auf die aktuellen Aufgaben ausgerichtet haben. Sprechen Sie mich an, wenn Sie meiner Kompetenz vertrauen wollen.
Sie haben schon zig Ordnungssysteme ausprobiert und keins gibt Ihnen genug Sicherheit, zu jeder Zeit abgelegte Informationen oder Dokumente schnell wieder zu finden?
Dann versuchen Sie doch mal das PPP-Systemoder das A-P-DOK-System! Heute hier nur zum PPP-System.
Die oberste Ebene strukturieren Sie nach Ihren Lebenshüten:
Familie
Dokumente (für jedes Familienmitglied, …)
Dokumente Wohnung, Versicherungen, Gartenhaus, Auto …
Gesellschaft (Elternsprecher, Lesepaten, Nachbarschaft, Vereine, Politik, …)
Freunde (Facebook,…)
Jeder Lebenshut sollte seinen eigenen Ordner haben, die Sie gut farblich oder an der Beschriftung unterscheiden können. Legen Sie auch auf Ihrem Datenspeicher diese Ordner an, vielleicht beziehen Sie auch verschiedene Cloudordner ein. Setzen Sie die Reihenfolge nach Ihrer Gewichtung! Da Computer-Apps meistens alphabetisch sortieren, setzen Sie Ordnungsnummern an den Anfang. Bewährt hat sich auch eine Untergliederung der Unterordner mit Jahreszahlen.
Wichtig
Legen Sie für Festplatten, Cloudordner und Postfächer immer dieselbe Struktur fest – sie werden sich besser zurechtfinden.
Sie können dann sehr leicht entscheiden, in welchen Postfach-Ordner sie die E-Mail und in welchen Ordner sie die Anlagen speichern werden. Selbst in der Smartphone App von Outlook stehen Ihnen die Ordner zur Verfügung.
Wenn der Infobereich nicht nur für Administratoren da ist
Wie häufig nutzen Sie die Taskleiste? Sie befindet sich meist an der unteren Seite des Bildschirms. Durch Anheften der Programme, die man häufig benutzt, lassen sich die Zugriffsklickzahlen sehr verringern. Das rechte Ende ist der Informationsbereich. Da passt nicht nur das Datum und die Uhrzeit hinein.
Gelegentlich legen die Administratoren fest, welche Symbole dort angezeigt werden. Dann haben Sie meist auch keine Zugriffsrechte. Dann kann ich Ihnen mit diesem Tipp auch nicht helfen.
Haben Sie jedoch Zugriff, so lohnt es sich, dort etwas aufzuräumen. Programme und Apps, die im Hintergrund laufen, hinterlassen dort meist einen Eintrag, um Sie bei bestimmten Situationen mit einem Pop-up-Fenster zu informieren. Beispielsweise zeigt Outlook dort an, wenn neue E-Mails eingegangen sind. Gestern wollten wir jedoch alle ablenkenden Benachrichtigungen ausschalten! Die Wege sind bei Windows 7 und Windows 10 unterschiedlich.
Windows 10
Windows 7
Der Infobereich in der Taskleiste rechts.
Der Infobereich in der Taskleiste rechts.
Diese zwei Einträge im Taskzeilen-Manager sind für den Infobereich zuständig.
Mit einfachen Ein-Aus-Schaltern können Sie jede Anwendung sichtbar machen oder aber auch verschwinden lassen.
Wählen Sie den unteren Link, um in folgender Maske die Standardsymbole einzustellen.
In den rot umrandeten Feldern lassen sich nun die Einstellungen vornehmen.
Lassen Sie sich die Benachrichtigung das Outlook-Programm ausblenden. Viel Spaß beim Durchprobieren.
Wenn ich ungestört arbeiten möchte oder mich erholen möchte, ohne dass sich alle Probleme gleich wieder in den Vordergrund drängen können, versuche ich alle Störungen von mir fern zu halten. Dazu gehört auch, alle Nachrichtendienste „stumm“ zu schalten. Für Outlook heißt das, die Meldungsfenster abzuschalten.
Dazu klicken Sie folgendes:
Register Datei – Optionen (mit der Tastatur: Alt D O) , runter auf E-Mail und dann im dritten Abschnitt Nachrichteneingang den Haken bei Desktop-Benachrichtigung entfernen.
Verwenden Sie die Smartphone-App von Office365, so finden Sie die Konfiguration der Benachrichtigungsfunktion unter dem Zahnrad-Symbol, dem Menüpunkt „Einstellungen“. Die dritte Gruppe enthält das Menü zum Einstellen der Benachrichtigung : Benachrichtigung: Keine oder nur bei Nachrichten mit Relevanz, Sound: Keinen oder Outlook E-Mail, Vibrieren (Ein / Aus).
Das Ausschalten der Benachrichtigungsfenster funktioniert natürlich nur dann gut, wenn man dennoch regelmäßig seine E-Mail-Nachrichten liest: 2-3 mal täglich im normalen Business-Alltag. Ansonsten schalten Sie Ihre Abwesenheitsassistenten aktiv.
Am Wochenende, in der Freizeit oder im Urlaub wird die Frequenz natürlich von Ihren Kommunikationsbedürfnissen geprägt. Man kann den PC oder das Smartphone auch mal ausschalten.
Nachdem nun das neue Jahr auch mit einigen Ausfällen zu kämpfen hatte, kommt der Zug ins Rollen. Ganz konkret stehen jetzt zwei VBA-Kurse an, für die ich mir noch Teilnehmer wünsche:
Am 30. und 31.3. 2017 findet der Excel-VBA-Kurs beim TÜV Rheinland statt, und am 3. und 4. April der Word-VBA-Kurs an derselben Adresse (Friedrichstraße 50 in Berlin-Mitte). Die Inhalte finden Sie auf den Themen-Seiten zu Excel und Word.
Wenn Sie also über Automatisierung Ihrer Abläufe nachdenken, sich in die Dokumente Ihrer Vorgänger einarbeiten möchten und die Grundlagen dazu erlernen möchten – melden Sie sich an: Eine E-Mail an mich oder das TÜV-Rheinland-Anmeldeformular ausfüllen – beides ist möglich. Ich freue mich auf Sie.
also. Zum Vergleich: Benannte Bereiche haben immer absolute Adressen, ihre Bezüge werden beim Autoausfüllen beibehalten.
