Excel-Tipps 51 bis 100 ff.

Pivot-Tabellen mit intelligenter Tabelle

Die Basisdaten-Liste sollte über START " ALS TABELLE FORMATIEREN (oder STRG + t) als intelligente Tabelle formatiert werden (vgl. Excel-Tipps 41 bis 45). Durch die damit einhergehende Objektorientierung entfällt die manuelle Eingabe des Datenbereichs bei der Pivotierung, was insbesondere später bei etwaigen (täglichen, wöchentlichen oder monatlichen) Aktualisierungen (Werte kommen hinzu oder fallen weg) von Vorteil sein kann – eine ansonsten wahrscheinlich notwendige Anpassung des Datenbereichs im Zellbezug ist damit nicht mehr notwendig. Dies spart Zeit und vermindert zudem das Risiko mit alten Datenbeständen zu arbeiten.

Dipl.-Kfm. Daniel Unrein, Leiter Corporate Controlling in einem internationalen Dienstleistungs-Unternehmen, Verfasser des bei Vahlen erschienenen Fachbuchs „Excel im Controlling”

Excel-Tipps von Daniel Unrein

Die ersten 50 Excel-Tipps finden Sie hier.

51. Tabellenfilter schnell an-/ausschalten

Wer viel mit Listen arbeitet, wird regelmäßig Filter anwenden (müssen). Schneller, als diese über das Menü DATEN → FILTERN aufzurufen, geht es – wie immer – mit einer Tastenkombination. Um schnell einen Filter hinzufügen bzw. auszuschalten, einfach in die Liste klicken und

STRG + SHIFT+ L

drücken.

52. Formeln mit dem Funktions-Assistenten eingeben – Schritt 1

Neben der direkten Eingabe von Formeln können Sie auch die menügestützte Variante über den Funktionsassistenten verwenden. Dies eignet sich insbesondere für Anfänger, die den Aufbau gerade von komplexeren Funktionen mit mehreren Argumenten nicht kennen oder auch bei der Eingabe von verschachtelten Funktionen Probleme haben. Der Funktionsassistent kann unter FORMELN → FUNKTION EINFÜGEN oder durch Klick auf die „fx-Schaltfläche“ in der Formel-Eingabeleiste aufgerufen werden (vgl. auch die untere Abb.):

• Im Bereich FUNKTION SUCHEN können Sie den Namen der gesuchten Funktion eingeben und diesen mit Klick auf die Schaltfläche OK suchen.
• Der Bereich KATEGORIE AUSWÄHLEN bietet Ihnen die Möglichkeit, die gesuchte Funktion aus einer bestimmten Kategorie auszuwählen. Die Unterteilung aller vorhandenen Funktionen in mehrere Kategorien dient der Übersichtlichkeit. Unter „Zuletzt verwendet“ finden Sie die Funktionen, die Sie tatsächlich auch als letztes verwendet haben.
• Je nach gewählter Kategorie finden Sie im Bereich FUNKTION AUSWÄHLEN eine Liste aller Funktionen, die es in dieser Kategorie gibt. Mit einem Klick auf eine Funktion wählen Sie diese aus.

53. Formeln mit dem Funktions-Assistenten eingeben – Schritt 2

Nachdem im Tipp der letzten Woche der erste Schritt, die Funktionseingabe, erläutert wurde, geht es im zweiten Tipp zum Funktionsassistenten um die konkrete Eingabe der Argumente der Funktion, die im ersten Schritt gewählt wurde (hier: LINKS, siehe vorherigen Tipp). Gerade bei neuen Formeln kann die hierin vorhandene Beschreibung und Erklärung der jeweiligen Argumente gegebenenfalls hilfreich sein. Auch die Hilfefunktion steht hier zur Verfügung.

Alternativ gelangen Sie in dieses Menü bei der konkreten Eingabe einer Formel – wenn diese bereits von Excel erkannt wurde – durch Klick auf die „fx-Schaltfläche“ oder auch mittels der Tastenkombination STRG+a (siehe auch Tipp 15).

54. Formel- bzw. Arbeitsblatt-Zusammenhänge bei fremden Dateien verstehen

Insbesondere beim Empfang von fremden Date(ie)n kann es vorteilhaft sein, sich einen ersten Überblick zu verschaffen und in Erfahrung zu bringen, in welchen Zellen sich welche Formeln/Funktionen bzw. Funktionalitäten befinden. Formel- bzw. Arbeitsblatt-Zusammenhänge lassen sich beispielsweise unter START → SUCHEN UND AUSWÄHLEN im Menü INHALTE AUSWÄHLEN erkennen. Beim Klicken auf „Formeln“ werden nur die Zellen markiert, in denen sich Formeln bzw. Funktionen befinden. Zudem lassen sich auch noch mehr spezifische Details zu Formeln wie Fehler- oder Wahrheitswerte (WAHR/FALSCH) abfragen. Oder es kann auch nach Zellen gesucht werden, die Konstanten, bedingte Formatierungen, Datenüberprüfungsregeln, Kommentare oder Textwerte enthalten. Auch sich im Arbeitsblatt befindliche Leerzellen oder Objekte wie Diagramme können durch dieses Menü identifiziert bzw. ausgewählt werden – sehr hilfreich gerade bei fremden Dateien! 

Nachdem im Tipp der letzten Woche der erste Schritt, die Funktionseingabe, erläutert wurde, geht es im zweiten Tipp zum Funktionsassistenten um die konkrete Eingabe der Argumente der Funktion, die im ersten Schritt gewählt wurde (hier: LINKS, siehe vorherigen Tipp). Gerade bei neuen Formeln kann die hierin vorhandene Beschreibung und Erklärung der jeweiligen Argumente gegebenenfalls hilfreich sein. Auch die Hilfefunktion steht hier zur Verfügung.

55. Überblick über die verwendeten Formeln verschaffen

Insbesondere beim Erhalten von fremden Date(ie)n ist es vorteilhaft, sich einen ersten Überblick zu verschaffen und in Erfahrung zu bringen, in welchen Zellen sich welche Formeln/Funktionen bzw. Berechnungen befinden. Unter FORMELN → FORMELN → ANZEIGEN kann die Formelsicht aktiviert werden: Statt den Formelergebnissen werden dann die Formeln angezeigt, die zu diesen führen. Dies stellt einen ersten zweckmäßigen Schritt zur Orientierung in fremden Dateien dar. Es kann sich zudem anbieten, ein neues Fenster zu öffnen, um so beide Sichten (d.h. sowohl die gewohnte Formelergebnis- als auch die Formelansicht) unter- oder nebeneinander gleichzeitig zu betrachten (vgl. Abb.).

Extra-Tipp: Auch die Funktion FORMELTEXT, die Ihnen ab Excel 2013 zur Verfügung steht, kann für solche Zwecke hilfreich sein. Diese gibt eine Formel als eine Zeichenfolge zurück bzw. den in der Bearbeitungsleiste angezeigten Eintrag der Zelle, die als Bezug gewählt wurde.

56. Formelzusammenhänge verstehen

Unter FORMELN → FORMELÜBERWACHUNG gibt es die Funktionalitäten SPUR ZUM VORGÄNGER bzw. NACHFOLGER:

• SPUR ZUM VORGÄNGER zeigt zu einem Formelergebnis, welche Zellen in die Berechnung eingeflossen sind (siehe auch die untere Abb.)
• SPUR ZUM NACHFOLGER zeigt für eine Zelle/Formel, in welche weiteren Formeln diese eingeflossen ist.

Dies ist sehr praktisch, um sich gerade bei fremden Arbeitsblättern einen Überblick zu verschaffen. Auch kann man sich vor dem Löschen einer Zelle vergewissern, ob sie nicht noch irgendwo in Formeln verwendet wird oder aber bei einem Fehler schauen, welche Zellen an der Berechnung beteiligt sind.

57. Formeln/Dateien auf Fehler prüfen

Es kann sich oft empfehlen (egal ob in fremden oder eigenen Dateien), die Korrektheit der Formeln im Arbeitsblatt zu prüfen. Hierzu ist im Menü FORMELN die Funktionalität FEHLERÜBERPRÜFUNG zu starten:

Es kann dann mit Schaltflächen zwischen allen im Arbeitsblatt befindlichen Formeln navigiert und entschieden werden, ob etwaige Fehler bearbeitet oder ignoriert werden sollen. Auch eine Hilfe-Funktionalität steht hier zur Verfügung. Im Zweifel können alle Berechnungsschritte bis zum Formelergebnis nachvollzogen werden.

58. Formeln schützen

Formeln sind meist der empfindlichste Teil eines Excel-Modells. Aus Versehen vorgenommene Änderungen können dabei große Auswirkungen auf die Ergebnisse einer Kalkulation haben. Unter Umständen kann es sehr schwierig sein, den Fehler zu finden. Es kann sich daher empfehlen, Formeln zu sperren und den Arbeitsblattschutz zu aktivieren, so dass niemand Änderungen an den Formeln vornehmen kann. Bei Aktivierung des Blattschutzes können nur noch Zellen angesprochen werden, die nicht das Attribut GESPERRT besitzen. Standardmäßig blockiert Excel allerdings das ganze Tabellenblatt. Es kann sich aber auch eine Beschränkung auf die wirklich schützenswerten Zellen erfolgen. Dazu ist es zuerst erforderlich, dieses Attribut zunächst für alle Zellen auszuschalten und dann gezielt für die relevanten (hier die Formel-)Zellen wieder einzuschalten und erst dann den Blattschutz zu aktivieren. Ist dies gelungen, zeigt Excel eine Fehlermeldung an, wenn versucht wird, eine Formel in der Tabelle zu ändern oder zu löschen. Im Detail ist wie folgt vorzugehen:

1. Das Zellformat GESPERRT – das standardmäßig gesetzt, aber nicht aktiv ist – ist auf dem ganzen Tabellenblatt zu entfernen. Hierzu ist zuerst mit STRG+a das ganze Blatt zu markieren und dann mit STRG+1 (siehe auch Tipp Nr. 49) der Dialog zur Zellenformatierung zu öffnen. In diesem Menü ist in der Registerkarte „Schutz“ das Kästchen GESPERRT zu deaktivieren und mit OK zu bestätigen.
2. Nun sind alle Zellen zu markieren, die Formeln enthalten. Hierzu mit der Taste F5 den Dialog GEHE ZU öffnen. Unter INHALTE… ist FORMELN zu wählen. Damit wurden alle Formelzellen der Tabelle markiert.
3. Nun ist das in Schritt 1 deaktivierte GESPERRT wieder zu aktivieren, um das Zellenformat nur für die im obigen Schritt markierten Formel-Zellen zu setzen.
4. Zuletzt ist nun der Blattschutz unter ÜBERPRÜFEN → BLATT SCHÜTZEN zu aktivieren. Falls die gesperrten Zellen zumindest markiert und kopiert werden dürfen, können die Einstellungen unter „Blatt schützen“ bleiben wie sie sind. Andernfalls ist die Box vor GESPERRTE ZELLEN AUSWÄHLEN zu deaktivieren. Im Feld „Kennwort zum Aufheben des Blattschutzes“ kann zum Aufheben des Blattschutzes ein Kennwort eingegeben werden.

59. Formeln richtig einsetzen – Input-Parameter auslagern

Annahmen und Parameter (wie Wachstumsraten, Wechselkurse, Provisions- und Steuersätze etc.), auf die sich Formeln beziehen, sind auf Input-Blätter oder -Zellen auszulagern und nicht direkt in den Formeln zu verwenden (siehe gelbe Markierung der Provisionsberechnung bei Verkäufer 1 in der Abbildung). Das ist übersichtlicher, und die Formeln können so auch viel leichter angepasst und verstanden werden.

60. Formeln unverändert übertragen

Wollen Sie eine Formel aus einer Zelle in eine andere Zelle kopieren, werden – bei Verwendung von relativen Bezügen – diese entsprechend auf die neue Zelle angepasst. Ist dies nicht gewünscht, dann ist die Zelle am besten mit STRG+x auszuschneiden und an der gewünschten Stelle wieder einzufügen. Dadurch unterbleibt eine Anpassung auch bei relativen Bezügen.

61. Zeit- und Datumseingabe einfach gemacht – mit Kürzeln und Funktionen

Um das aktuelle Datum aufzurufen, können Sie einfach STRG +. [Punkt] drücken.

Für die aktuelle Zeit hingegen STRG+SHIFT+. [Punkt]

Je nach Anwendungszweck eignen sich diese Tastenkürzel vielleicht nicht immer. Soll sich das Datum beispielsweise jeden Tag bei Aufruf der Datei ändern, können Sie die Funktion =HEUTE() einsetzen.

Extra-Tipp: Soll auch noch die aktuelle Zeit ergänzt werden, bietet sich hingegen die Funktion =JETZT() an.

62. Überschreiten von Deadlines automatisch markieren

Mit der bereits aus dem vorherigen Tipp bekannten Funktion HEUTE – in Kombination mit der bedingten Formatierung – kann beispielsweise hervorgehoben werden, ob das aktuelle Datum größer (oder kleiner, gleich etc.) als ein anderes Datum (etwa eine gegebene Deadline in einem Projekt oder Reporting-Kalender) ist.

Unter START → BEDINGTE FORMATIERUNG → NEUE REGEL → FORMEL ZUR ERMITTLUNG DER ZU FORMATIERENDEN ZELLE VERWENDEN geht dies im unteren Beispiel durch

=HEUTE()>D2 (siehe auch die Abb.)

Das in Rot gefärbte Datum („06. Dez“) signalisiert nun die Überschreitung der Deadline und damit dringenden Handlungsbedarf. Gezielt eingesetzt, können somit überfällige Aktivitäten gut hervorgehoben werden.

63. Jahre, Monate und Tage in Excel

Mit der bereits aus dem vorherigen Tipp bekannten Funktion HEUTE in Kombination mit TAG, MONAT oder JAHR kann – unter Bezug auf die Zelle, in der HEUTE steht (hier: G1) – ermittelt/ausgelesen werden, welcher aktuelle Monat, Tag oder welches aktuelle Jahr es ist. Dieses „Zerlegen“ eines Datums funktioniert selbstverständlich auch bei beliebig anderen Daten. Ohne Zellverweise ergibt es den aktuellen Tag, Monat oder Jahr.

Extra-Tipp: Mit der Funktion DATUM, können Sie (z.B. über die obigen Funktionen) zerlegte Teile eines Datums auch wieder zusammensetzen.

64. Mit der Funktion MONAT und der Funktion WAHL die Monatszahlen in Monate umwandeln

Die bereits bekannte Funktion MONAT wandelt ein Datum in einen Monat um. Der Monat wird dabei als ganze Zahl ausgegeben (siehe den vorherigen Tipp Nr. 63), die einen Wert von 1 (Januar) bis 12 (Dezember) annehmen kann. Soll die Ziffernangabe in einen Monatsnamen umgewandelt werden, so kann u.a. mit der Funktion WAHL (gehört zu den Verweisfunktionen und verwendet einen Index, um einen Wert aus der Liste der Werteargumente zurückzugeben) gearbeitet werden.

Hierzu müssen als Wert1 bis Wert12 die einzelnen Monate (von Januar bis Dezember) des Jahres als Werteargumente von WAHL definiert werden. Dann gibt WAHL den entsprechenden Monat zurück, wenn eine Zahl zwischen 1 und 12 als Index verwendet wird. Im Beispiel werden die jeweiligen Index-Werte von WAHL also dynamisch über MONAT kreiert.

65. Wochentag

Über die Tabellenfunktion WOCHENTAG (Eingabe von „=Wochentag“ in eine Zelle) können Sie überprüfen, welcher Wochentag an einem gewählten Datum ist. Für jeden Tag gibt Excel eine Zahl aus – einen Wert von 7 (für Samstag) bzw. einen Wert von 1 (für Sonntag) (siehe auch die Tabelle unten).

Durch Verknüpfen mit FORMELN (wie beispielsweise WENN) können Sie beispielsweise herausfinden, ob ein Wochentag vorliegt oder das Datum am Wochenende liegt. Auf diese Weise könnten Sie dann beispielsweise mit variablen Stundensätzen für Arbeitstage und Wochenenden rechnen.

Reminder: Falls eine Umwandlung der ausgegebenen Zahl in den Tag gewünscht ist, können Sie dann beispielsweise wieder mit WAHL (siehe vorherigen Tipp), mit WENN oder auch über die oben abgebildete Hilfsliste und einem SVERWEIS arbeiten.

66. Kalenderwoche in Excel ermitteln

Geht es um Termine, meist in der Zukunft, dann ist die Kalenderwoche hierzulande häufig eine der wichtigsten Angaben. Dementsprechend ist es zweckmäßig, die korrekte Excel-Funktion hierfür zu kennen. Mit Excel 2013 hat eine neue Funktion (und zwar ISOKALENDERWOCHE, siehe unten) Einzug gehalten, die uns dies erleichtert.

Denn bis einschließlich Excel 2010 stand in Excel nur die Formel KALENDERWOCHE zur Verfügung. Diese Funktion rechnet allerdings nach amerikanischem Standard. In Amerika ist die erste Kalenderwoche immer diejenige Woche, die den 01. Januar enthält. In Deutschland hingegen ist die erste Woche des Jahres laut ISO (Norm ISO 8601) als die Woche definiert, in die mindestens 4 Tage fallen (vereinfacht: die Woche, die den 04. Januar enthält).

Um mit KALENDERWOCHE zum korrekten Ergebnis zu kommen, muss(te) hierzulande noch ein Parameter (21) eingegeben werden =KALENDERWOCHE(Datum;21)

Mit der neuen Excel 2013-Funktion ISOKALENDERWOCHE fällt dies nun weg. Diese gibt immer die richtige, also die für Deutschland offiziell geltende Kalenderwoche aus (siehe Abb.).

67. Die „versteckte“ Funktion DATEDIF

Über die „versteckte“ Funktion DATEDIFF (taucht nicht in der Autovervollständigung auf) können Sie eine genaue Datumsdifferenz gemäß den Angaben unten ermitteln.

Durch Angabe der beiden Daten und „d“ können Sie beispielsweise ermitteln, wie viele Tage noch zwischen den beiden Daten liegen.

 Die Berechnung der Zahl der zwischen zwei Terminen liegenden Kalendertage kann einfach mit einer Subtraktion erfolgen. Allerdings erlaubt DATEDIF eben noch weitere Möglichkeiten der Anzeige (siehe oben). Sollen hingegen nur die dazwischenliegenden Arbeitstage gezählt werden, gibt es weitere Möglichkeiten (siehe hierzu den nächsten Tipp).

68. Berechnen der zur Verfügung stehenden Arbeitstage zwischen zwei Daten

Bei Projektplanungen oder zur Einhaltung von Lieferterminen brauchen Sie keinen Kalender zur Hand zu nehmen, um die Zahl der tatsächlichen Arbeitstage unter Berücksichtigung von Wochenenden und Feiertagen zu ermitteln. In Microsoft Excel gibt es eine Funktion, die die Nettoarbeitstage zwischen zwei Datumsangaben errechnet. Für die Berechnung der Dauer einer Datumsdifferenz mit der Anzahl der dazwischenliegenden Arbeitstage kann die Funktion NETTOARBEITSTAGE verwendet werden.

Bei Anwendung des optionalen Arguments FREIE_TAGE könnten neben den Wochenenden auch noch individuell festlegbare Feiertage berücksichtigt werden.

Extra-Tipp: In Microsoft Excel gibt es noch die Funktion NETTOARBEITSTAGE.INTL (https://support.office.com/de-de/article/NETTOARBEITSTAGE-INTL-Funktion-a9b26239-4f20-46a1-9ab8-4e925bfd5e28?CorrelationId=9430ca93-e4d9-4649-b0e9-2b747e8ad408&ui=de-DE&rs=de-DE&ad=DE). Diese Funktion hat einen weiteren Parameter, mit dem Sie auch die Wochenendtage frei definieren können, beispielsweise falls der Samstag als Arbeitstag gelten soll.

69. Rechnen mit Arbeitstagen

Neben der Berechnung der Differenz der zur Verfügung stehenden Arbeitstage zwischen zwei Daten (beispielsweise wollen Sie zwischen Beginn und Ende eines Projekts die genaue Anzahl der Arbeitstage bestimmen, siehe vorheriger Tipp), gibt es noch weitere Abfragemöglichkeiten. So kann es zweckmäßig sein, von einem Ausgangsdatum ausgehend die zur Verfügung stehenden Arbeitstage für eine Tätigkeit oder Ähnliches zu ermitteln und hinzuzurechnen – und so zum Enddatum (beispielsweise für einen Projekt- oder Reporting-Kalender) zu gelangen.

Hierfür steht ab Excel 2007 die Funktion ARBEITSTAG zur Verfügung (siehe unten). Standardmäßig werden hier Samstag und Sonntag als Wochenendtage angenommen; FREIE_TAGE, also beispielweise Feiertage, können zudem optional angegeben werden. Die Ausgabe erfolgt als serielles Datum. Sie müssen die Zelle also als „Datum“ formatieren, wenn Sie einen Datumswert sehen möchten.

Im Beispiel soll der Januar-Abschluss – vom 31.01.2016 (Zelle X3) an – in 8 Arbeitstagen beendet sein; es ergibt sich der 10.02.2016 als End-Datum (siehe Abb.).

Extra-Tipp: Nicht in jedem Land gibt es die gleichen Wochenendtage und nicht in jedem Beruf hat die Woche fünf Arbeitstage. Microsoft hat daher in Excel 2010 die Funktion ARBEITSTAG.INTL entwickelt, bei der sich nun auch die Wochenendtage frei definieren lassen (https://support.office.com/de-DE/article/ARBEITSTAG-INTL-Funktion-A378391C-9BA7-4678-8A39-39611A9BF81D).

70. Funktion MONATSENDE

Die Funktion MONATSENDE gibt die fortlaufende Zahl des letzten Tags des Monats zurück, der eine bestimmte Anzahl von Monaten (zweites Argument der Funktion) vor bzw. nach dem Ausgangsdatum (erstes Argument der Funktion) liegt. So können Sie beispielsweise Abschluss-/Rückzahlungs- oder Fälligkeitstermine berechnen, die auf den letzten Tag eines Monats fallen.

Im Beispiel soll – ausgehend von einem (über die Funktion HEUTE ermittelten, siehe auch Tipp 61) bestimmten Datum (bei Erstellung war dies der 20.1.2016) – das Enddatum des festgelegten sowie des Vor- und Folge-Monats ermittelt werden. Das zweite Funktionsargument zeigt dabei die Zahl der Monate an, die vor oder nach dem angegebenen Datum liegen. Ein positiver Wert für Monate ergibt dabei ein Datum in der Zukunft, ein negativer Wert ein Datum in der Vergangenheit (siehe die Abb. unten). Zudem können Sie mit MONATSENDE den jeweils ersten Tag des Monats (sowie selbstverständlich auch die weiteren Tage) berechnen, indem Sie – ganz simpel und intuitiv – einfach einen Tag/eine Eins addieren.

71. Verketten von Text mit einem Datum mittels TEXT

Um Texte mit einem korrekt dargestellten Datum oder einer Zahl zu verketten, müssen Sie die Zahl vorher in einen Text umwandeln. Verwenden Sie hierfür die Funktion TEXT; für das richtige Funktionieren müssen Sie im zweiten Argument der Funktion die Art der Formatierung zwischen Anführungszeichen darstellen (siehe für ein Beispiel die Abb. unten). Vollständige Ansicht der Formel (mit Doppelpunkt nach dem Artikel „der:“!):

="Heute ist der: "&TEXT(HEUTE();"TT.MM.JJJJ")

72. Benutzerdefinierte Formatierung von Daten

Wer häufig Berichte erstellt, muss sich mit der Fragestellung auseinandersetzen, wie Monate und Jahre dargestellt werden sollen. Excel bietet geniale individuelle Möglichkeiten zur Formatierung eines Datums. Mithilfe der benutzerdefinierten Formatierung kann der Inhalt der Zellen mit Datumsformatierung zudem unabhängig der Formatierung ganz „normal“ in weiteren Funktionen/Funktionalitäten verwendet werden. Die benutzerdefinierte Formatierung ist ein ergiebiges Werkzeug, um die Anzeige von Zahlen, Datumsangaben und Texten an die eigenen Wünsche anzupassen.

Das Menü ZELLEN FORMATIEREN ist über STRG+1 (oder unter START → FORMAT → ZELLEN FORMATIEREN) aufzurufen. Je nach Eingabe des Zahlenformats ergibt sich eine unterschiedliche Anzeige des Datums (hier der 26.01.2016, siehe Abb. unten).

73. Automatische Listen mit Wochentagen erstellen

Durch Ziehen am Ausfüllkästchen (rechts unten in der Zellmarkierung) können Werte wahlweise fortgeschrieben oder auch kopiert werden. Das bringt Zeitersparnis und macht die Funktion AUTOAUSFÜLLEN zu einem der besten Werkzeuge bei der Eingabe von Werten und Daten. Zur Nutzung von AUTOAUSFÜLLEN bei Daten gelten die folgenden Regeln:

• Um Datumswerte fortzuschreiben, einfach mit linker Maustaste am Datenkreuz ziehen.
• Beim Ziehen mit der linken Maustaste und gedrückter STRG wird ein Datumswert hingegen kopiert.
• Eine spezielle Funktion ist das Ziehen mit der rechten Maustaste. Bei Loslassen der Maus öffnet sich das Kontextmenü, welches – in Abhängigkeit vom Datentyp – verschiedene Ausfüll-Optionen bereitstellt. Bei Datumswerten bestehen verschiedene Optionen; besonders hilfreich ist „nur Wochentage ausfüllen“. Es werden dann Daten (wie gewohnt) fortgeschrieben, aber dabei die Wochenenden (weil z.B. arbeitsfrei) übersprungen (vgl. die untere Abb.).

74. Aus klein mach groß – mit Textformeln

Oft liegen Daten nicht in der gewünschten Form vor, beispielsweise wenn im Rahmen des Imports keine oder nur teilweise die Möglichkeit bestand, Einfluss auf die zu importierenden Daten auszuüben.

Um Daten in die benötigte Form zu bringen, z.B. in die im Unternehmen übliche Darstellungsweise oder durch Ausblenden/Bereinigen von Fehlern (wie etwa Leerzeichen), sind sog. Textfunktionen besonders hilfreich. Mittels Textfunktionen können Sie aus Zellen die benötigten Informationen extrahieren, umwandeln oder auch Informationen aus verschiedenen Zellen in eine zusammenfassen.

Ein typisches Problem bei der Umwandlung von Daten ist die Groß- und Kleinschreibung. Zur Umsetzung einer korrekten Groß- und Kleinschreibung kann die Funktion GROSS2 verwendet werden. Bei dieser wird der Anfangsbuchstabe groß und der Rest des Textes klein geschrieben – genau so, wie es im Regelfall erwünscht ist. GROSS(Text) hingegen wandelt den kompletten Text in Großbuchstaben um. KLEIN(Text) ist dabei das Pendant zu GROSS(Text) und wandelt den kompletten Text in Kleinbuchstaben um (siehe auch die Abb.)

75. Daten aus einer Zelle auslesen – mit den Textformeln LINKS/RECHTS

Häufig liegen Daten nicht in der gewünschten Form vor, beispielsweise wenn im Rahmen des Imports keine oder nur teilweise die Möglichkeit bestand, Einfluss auf die zu importierenden Daten auszuüben. Um Daten in die benötigte Form zu bringen (z.B. in die im Unternehmen übliche Darstellungsweise oder durch Ausblenden/Bereinigen von Fehlern wie etwa Leerzeichen), sind sog. Textfunktionen besonders hilfreich. Mittels Textfunktionen können Sie aus Zellen die benötigten Informationen extrahieren, umwandeln oder auch Informationen aus verschiedenen Zellen in eine zusammenfassen.

Sollen Daten aus einer Zelle extrahiert werden, sind insbesondere die Funktionen LINKS (Text; [Anzahl_Zeichen]) und RECHTS(Text; [Anzahl_Zeichen]) nützlich – vor allem dann, wenn Zeichenfolgen nach einem bestimmten Schema aufgebaut sind (PLZ Ort, ISBN-Nr. usw.), Diese beiden Textfunktionen geben einen Wert (String) zurück, der ausgehend von der rechten bzw. linken Seite einer Zeichenfolge, eine angegebene Anzahl von Zeichen enthält.

In der unteren Abbildung werden die in einer Zelle vorliegenden Artikel-Nummern („M“ steht für männlich, „W“ für weiblich) in zwei Zellen separiert, um beispielsweise besser Formeln oder Pivot-Auswertungen erstellen zu können.

76. Daten aus einer Zelle auslesen – mit TEIL

Sehr vergleichbar den Textformeln LINKS/RECHTS (siehe Excel-Tipp-Nr. 75) können Sie auch die Textfunktion TEIL anwenden. Diese Funktion gibt – nomen est omen (sinngemäß: „Der Name ist Programm“) – den Teil eines Textes zurück, der sich aus der Vorgabe von einem Startpunkt und Länge der Teilzeichenfolge ergibt. Auf diese Weise können Sie einen bestimmten Teil mitten aus einer längeren Zeichenfolge „herausschneiden“ – und sind damit sogar noch etwas flexibler beim Auslesen.

An dem Beispiel aus dem Tipp Nr. 75 sieht die Anwendung von TEIL dann – wie in der folgenden Abbildung gezeigt – aus. Hierbei werden die in einer Zelle vorliegenden Artikel-Nummern („M“ steht für männlich, „W“ für weiblich) in zwei Zellen aufgeteilt.

77. Daten zusammenfügen

Mittels Textfunktionen können Sie aus Zellen die benötigten Informationen extrahieren, umwandeln oder auch Informationen aus verschiedenen Zellen in eine zusammenfassen.

Um Werte aus unterschiedlichen Spalten zusammenzufügen, stehen prinzipiell zwei Möglichkeiten zur Auswahl:

• Es muss zwischen jedem zu verknüpfenden Objekt der Operator & eingegeben werden.
• VERKETTEN; diese speziell für diese Aufgabenstellung vorgefertigte Excel-Funktion verbindet die Objekte durch einfache Zellenauswahl miteinander zu einer einzigen Textzeichenfolge. Die verknüpften Elemente können aus Text, Zahlen, Zellbezügen oder einer Kombination dieser Elemente bestehen.

Im (aus den vorherigen Tipps bekannten) Beispiel soll nun die extrahierte Artikel-Nummer (Kodierung männlich/weiblich plus Nummer) wieder in einer Zelle zusammengefügt werden (vgl. hierzu die Abbildung unten).

78. Daten ERSETZEN

Mittels Textfunktionen können Sie aus Zellen die benötigten Informationen extrahieren (herausziehen), umwandeln oder auch Informationen aus verschiedenen Zellen in eine zusammenfassen.

Sehr nützlich ist bespielweise auch ERSETZEN. Wie fast immer bei den Textfunktionen ist der Name Programm: Ein bestimmter Teil eines Texts kann durch einen anderen Text „ersetzt“ werden.

ERSETZEN(Text, Erstes_Zeichen, Anzahl_Zeichen, Neuer_Text) benötigt die (Start-)Position und die Länge der Zeichenfolge, die ausgetauscht werden soll. Es kann verwendet werden, wenn bekannt ist, an welcher Stelle im String der zu ersetzende Text steht (ist er das nicht, kann WECHSELN, siehe nachfolgende Tipps, verwendet werden.

Gerade bei unerwünschten Zeichen (oder auch zur Separierung von Daten) kann es zweckmäßig sein, einen Textbestandteil durch Nichts (““) zu ersetzen.

Soll aus dem Beispiel zum vorangegangenen 77. Excel-Tipp nur der Unterstrich weggenommen werden, kann hierzu ERSETZEN – wie in der folgenden Abbildung ersichtlich – eingesetzt werden.

79. Störende Leerzeichen mit GLÄTTEN/WECHSELN entfernen

Oft liegen Daten nicht in der gewünschten Form vor, beispielsweise wenn im Rahmen des Imports keine oder nur teilweise Möglichkeit bestand Einfluss auf die zu importierenden Daten auszuüben. Ein häufiges Problem gerade bei der Weiterverarbeitung von Daten sind Leerzeichen. Um das Problem mit eventuell störenden Leerzeichen – beispielsweise beim Einsatz des SVERWEIS – zu umgehen, kann es sich empfehlen, die Datenmatrix vor dessen Anwendung mit Textfunktionen zu bereinigen. So können beispielsweise die Funktionen GLÄTTEN oder WECHSELN die potenzielle Fehlerquelle des Vorhandenseins unerwünschter Leerzeichen bei Anwendung von Verweisfunktionen ausschließen. 

Die Funktion GLÄTTEN (Text) dient dazu überzählige Leerzeichen vor und nach einen Text – bzw. mehr als eines zwischen Textfragmenten – zu entfernen.

WECHSELN dient dazu einen bestimmtem Text bzw. ein bestimmtes Zeichen (hier eben exemplarisch das Leerzeichen) komplett aus einer Zelle zu entfernen bzw. durch einen neuen Wert, in diesem Fall durch Nichts (““), auszuWECHSELN

Sollen nur die überflüssigen – und nicht alle Leerzeichen am Stringanfang und -ende – ersetzt werden, ist WECHSELN überfordert und GLÄTTEN die bessere Wahl.

80. Mit WECHSELN Texte/Umlaute in Listen/Texten ersetzen

Sind Listen mit oder ohne deutsche Umlaute zu pflegen? Gerade im internationalen Kontext sollen Umlaute wie ä, ö, ü, ß vielleicht besser in ae, oe, ue und ss umgewandelt werden. Denn bestimmte Software kann mit Umlauten nichts anfangen. Selbstverständlich soll dies möglichst einfach gehen – eine manuelle Lösung ist dabei wie fast immer nicht empfehlenswert, dafür aber die Funktion WECHSELN.

Zur Erinnerung an den letzten Tipp: WECHSELN dient dazu, einen bestimmtem Text bzw. ein bestimmtes Zeichen (hier die Umlaute) komplett aus einer Zelle zu entfernen bzw. durch einen neuen Wert auszuWECHSELN. Angewendet sieht das dann wie folgt aus:

=WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(C3;"ü";"ue");"ö";"oe");"ä";"ae");"Ü";"Ue");"Ö";"Oe");"Ä";"Ae");"ß";"ss")

Sie können diese Formelsyntax selbstverständlich auch für andere Zeichen verwenden. Neben den deutschen Umlauten gibt es ja auch in anderen Sprachen und Regionen spezielle Zeichen, die sich mit manchen IT-Systemen nicht vertragen, wie beispielsweise die französischen oder spanischen Auszeichnungen über Vokalen.

81. Europäische und amerikanische Zahlenformate tauschen (ab Excel 2013)

Texte können Sie mit der Funktion ZAHLENWERT ab Excel 2013 in Zahlen zum Rechnen (abhängig vom Gebietsschema) umwandeln. Zudem können Sie verschiedene Zahlenformate (Komma oder Punkt als Dezimal bzw. Gruppentrennzeichen) einfach mit dieser Funktion umwandeln.

Die Funktion hat drei Argumente:

• Text/der in eine Zahl zu konvertierende Text ist erforderlich.
• Dezimal-Trennzeichen ist optional. Das Zeichen, das zum Trennen der ganzen Zahl von den Nachkommastellen des Ergebnisses verwendet wird (in der Regel „,“ oder „.“).
• Gruppen-Trennzeichen ist optional. Das Zeichen, das zum Trennen von Zahlengruppen verwendet wird, z.B. zwischen Tausender und Hunderter oder zwischen Millionen und Tausender (in der Regel „,“ oder „.“)..

Haben Sie in Zelle A1 also ein amerikanisches Zahlenformat mit einem Komma als Gruppen-Trennzeichen und einem Punkt als Dezimal.Teichen (5,500.55) und wollen das in die gängige europäische Schreibweise umwandeln, so muss die Formel für die Konvertierung wie folgt (siehe auch die untere Abbildung) lauten: =ZAHLENWERT(A1;".";",")

82. Verwenden von Platzhaltern in Formeln

Die Verwendung von Platzhaltern erlaubt eine flexiblere Suche. Platzhalter helfen Ihnen dabei, gleichzeitig nach Varianten von Wörtern, mehreren Wörtern oder ähnlichen Gruppen von Wörtern zu suchen. Es muss dabei nicht nach einem konkreten Begriff, sondern es kann auch nach Zeichenfolgen, die mit einem bestimmten Buchstaben oder einer Zeichenfolge beginnen bzw. auch enden dürfen, in Formeln wie z.B. ZÄHLEN/SUCHENWENN(s) gesucht oder in Tabellen gefiltert werden. Eine als Suchkriterium angegebene Zeichenfolge darf dabei die Platzhalterzeichen Fragezeichen (?) und Sternchen (*) enthalten. Platzhalter „*“ steht dabei für beliebig viele Zeichen, während der Platzhalter „?“ nur für ein Zeichen steht:

• B* sucht nach allem, was mit einem B beginnt und beliebig viele Zeichen enthält (BER/BERLIN etc.).
• B? sucht nach allem, was mit B beginnt und noch genau ein weiteres Zeichen enthält.
• B?? sucht daher nach allem, was mit B beginnt und noch genau zwei weitere beliebige Zeichen enthält, also insgesamt drei Zeichen (BER).
• ?? beantwortet die Frage, welche der Suchkriterien genau zwei Zeichen enthalten.
• ??* beantwortet die Frage, welche der Suchkriterien genau zwei oder mehr Zeichen enthalten.
• *x beantwortet die Frage, welche der Suchkriterien mit dem Zeichen x aufhören.

Dies kann je nach Anwendung nützlich sein – beispielsweise bei uneinheitlichen Schreibweisen in Listen (BER/ BERLIN) oder auch wenn Ihre Artikel-/Produktkategorien oder Ähnliches systematisch aufgebaut sind. Hier ein abschließendes Beispiel – diesmal in Kombination mit der Funktion ZÄHLENWENN, bei dem alle Produktcodes gesucht (und gezählt) werden, die mit TB beginnen und beliebig viele Zeichen (aufgrund des *) haben.

83. Minima und Maxima ermitteln

Zur Ermittlung von Rangfolgen – z.B. zur Identifizierung der Top- oder Flop X-Produkte – kann auf verschiedene Funktionen zurückgegriffen werden. Mithilfe von MIN lässt sich der kleinste Wert ermitteln. Mit MAX kann hingegen der größte Wert aus einer Tabelle ausgegeben werden.

Alternativ geht dies auch farblich mittels der bedingten Formatierung (OBERE/UNTERE REGEL).

84. Bildung von sortierten Rangfolgen

Soll nicht nur (wie im vorherigen Tipp gezeigt) der größte oder kleinste Wert, sondern der zweit-, dritt-, viert-größte/kleinste Wert usw. ermittelt und beispielsweise eine sortierte Reihe erstellt werden, kann dies am besten mit den Funktionen KKLEINSTE und KGRÖSSTE gelöst werden. KKLEINSTE (Matrix;k)/= KGRÖSSTE (Matrix;k) Gibt den kleinsten/größten Wert einer Datengruppe – abhängig vom gewünschten Rang/k – zurück (vgl. die untere Abbildung). Mit diesen Funktionen können sowohl sortierte Rangfolgen gebildet als auch Top-/Flop-Analysen durchgeführt werden.

Extra-Tipp: Wie im vorherigen Tipp gezeigt, können Sie mithilfe der bedingten Formatierung (OBERE/UNTERE REGEL) auch mehrere (z.B. die Top 5-)Werte hervorheben.

85. Ermittlung von Rangpositionen

Soll nicht (wie im vorherigen Tipp Nr. 84) nach bestimmten (Top-/Flop-)Werten gesucht oder eine sortierte Reihenfolge gebildet, sondern die konkrete Position eines Werts innerhalb einer Rangfolge ermittelt werden, steht RANG.GLEICH( Zahl;Bezug;Reihenfolge) zur Verfügung.

Das Argument „Reihenfolge“ ist dabei optional. Es gibt an, in welcher Reihenfolge der Rang ausgegeben werden soll:

• 0, absteigend sortiert; die Zahl 2 hat in der Zahlenreihe [4,5,3,2,1] bei aufsteigender Sortierung [1,2,3,4,5] den Rang 2;
• andere Werte als 0, aufsteigend sortiert; die Zahl 2 hat dann bei absteigender Sortierung [5,4,3,2,1] den Rang 4.

Wie zu erwarten, wird analog zum vorherigen Tipp/Beispiel bei aufsteigender Sortierung Rang „1“ an den Maximalwert (1.605.200) (und bei absteigender Sortierung entsprechend der letzte Rang „8“) vergeben (siehe die untere Abb.).

Extra-Tipp:

Zudem gibt es auch noch RANG.MITTELW. Die beiden Funktionen unterscheiden sich dann, wenn in einer Zahlenreihe bestimmte Werte mehrmals vorkommen – also mehrere Werte die gleiche Rangzahl aufweisen. Während RANG.MITTELW die durchschnittliche Rangzahl zurückgibt, wird mit RANG.GLEICH die niedrigste Rangzahl ausgegeben. Die Syntax beider Funktionen ist allerdings gleich.

86. Tabellen transponieren/Zeilen & Spalten vertauschen (Teil 1)

Es kommt vor, dass Reports/Tabellen/Listen in einer anderen Darstellung, in der Zeilen und Spalten in der Darstellung vertauscht sind, benötigt werden. Dann muss die Ausgangstabelle transponiert werden. Ist dies nur ein einmaliges Problem, geht dies ohne Schwierigkeiten über Kopieren und dann INHALTE EINFÜGEN → TRANSPONIEREN.

87. Tabellen transponieren/Zeilen & Spalten vertauschen (Teil 2)

Es kommt vor, dass Reports/Tabellen/Listen in einer anderen Darstellung, in der Zeilen und Spalten in der Darstellung vertauscht sind, benötigt werden. Dann muss die Ausgangstabelle transponiert werden. Ist dies nur ein einmaliges Problem, geht dies ohne Schwierigkeiten über Kopieren und dann INHALTE EINFÜGEN → TRANSPONIEREN (siehe auch den vorherigen Tipp).

Sollte eine solche Fragestellung allerdings regelmäßiger bzw. für sich ändernde Daten auftreten, so ist es selbstverständlich eleganter, nicht mit Kopien, sondern mit Formeln, die auf den Originalbereich verweisen, zu arbeiten. Die beste Möglichkeit stellt hierbei die Array-Formel MTRANS dar; diese gibt eine transponierte Version einer Quellmatrix zurück. MTRANS muss in einen Zielbereich, der über genau das umgekehrte Zeilen- und Spaltenverhältnis – aber die insgesamt selbe Zellenanzahl – wie die zu transponierende Matrix verfügt, eingegeben werden:

Wenn sich die Daten in der Quelle über fünf Zeilen und 3 Spalten erstrecken, müssen für den Zielbereich drei Zeilen und fünf Spalten markiert werden. Dieser Zielbereich muss sogleich von Anfang an markiert werden. Dann kann die Formel {=MTRANS (Originalbereich)} eingegeben und entsprechend mit STRG + SHIFT + EINGABE abgeschlossen werden. In der Folge erhält jede einzelne Zelle dieselbe Formel, wobei diese als Array-Formel effizienzsteigernd eben nur einmal eingegeben werden muss.

Im folgenden Beispiel (siehe Abbildung) enthält die zu transponierende Tabelle 15 Zeilen und 5 Spalten, der Zielbereich der transponierten Liste benötigt damit also 5 Zeilen und 15 Spalten. Der Zellbereich muss ausgewählt werden, bevor die Formel (MTRANS((A1:E15) eingeben wird.

88. Bezüge in Formeln mit SUCHEN/ERSETZEN anpassen

Für ein häufiges Problem gibt es eine einfache Lösung: Wie können Sie innerhalb von Formeln in einem Excel-Sheet mit vielen Formeln/Daten schnell und einfach den Bezug ändern? Beispielsweise soll nun in Ihrem Übersichtsblatt/Report/Dashboard mit allen Formeln auf das Datenblatt „Februar 2018“ statt auf „Januar 2018“ zurückgegriffen werden. Die Bezüge in jeder Formel per Hand zu ändern, würde sehr viel (zu viel) Zeit in Anspruch nehmen. Sie können das aber – zum Beispiel – ganz einfach (und mit etwas Vorsicht) mittels SUCHEN und ERSETZEN lösen:

• Markieren Sie den Daten-/Formelbereich oder gegebenenfalls das gesamte Blatt mit STRG+a.
• DRÜCKEN Sie STRG+h zum Aufrufen des Dialogs.
• Geben Sie unter SUCHEN „Januar“ ein.
• Geben Sie unter ERSETZEN Sie „Februar“ ein.
• Klicken Sie auf ERSETZEN bzw. ALLE ERSETZEN.

89. AUTOSUMME

Neben der klassischen SUMME, steht unter START auch noch die „AutoSumme“ zur Verfügung. Im Ergebnis gibt es hierbei keinen Unterschied. Diese Variante der Formelerstellung ist allerdings häufig schneller, insbesondere wenn die Zahlen in geordneter Form unter- oder nebeneinanderstehen. AUTOSUMME ermittelt automatisch den Bereich, der in die Berechnung einbezogen werden soll und setzt eine Summe darunter – per Knopfdruck und häufig mit dem gewünschten Ergebnis, ohne größere Nacharbeiten zu benötigen.
Übrigens, die Schaltfläche „AutoSumme“ kann mehr als nur das Addieren von Zellbereichen. Wenn Sie auf den Pfeil rechts neben der Schaltfläche AUTOSUMME klicken, können Sie wählen, welche Rechenoperation verwendet werden soll (z.B. „Mittelwert“ oder „Max“ und „Min“).

90. MEDIAN – oft der bessere Mittelwert

Eine klassische Rechen-Operation ist das Bilden eines Durchschnitts mit der Funktion MITTELWERT. Ohne an dieser Stelle zu tief in die Statistik einsteigen zu wollen: Der Mittelwert ist sehr abhängig von Ausreißern in den Daten. Das heißt: Besondere große oder kleine Werte können ihn stark beeinflussen; stärker jedenfalls als beispielweise den Median. Letzterer gilt in der Statistik daher als robuster gegenüber solchen Extremwerten. Für bestimmte Fragestellungen mag daher diese Art der Mittelwertbildung besser geeignet sein.
Es kann nicht schaden, sowohl den Mittelwert als auch den Median zu berechnen und sich zu überlegen, welcher der so ermittelten Werte die aktuelle Aufgabenstellung besser beantwortet. Auch für die Berechnung des Medians steht in Excel die entsprechende Funktion MEDIAN zur Verfügung. Ebenfalls ist nur der Zellbereich, für den der Median bestimmt werden soll, zu wählen. Der Median einer Auflistung von Zahlenwerten ist dabei der Wert, der an der mittleren (zentralen) Stelle steht, wenn man die Werte der Größe nach sortiert. Excel erledigt diese Berechnung natürlich selbstverständlich für Sie.

91. Mit Vorzeichen arbeiten

Die Funktion VORZEICHEN informiert – namensgetreu – über das Vorzeichen einer Zahl/Zelle, gibt also Auskunft darüber, ob eine Zahl positiv oder negativ ist (in der Abb. beginnend mit der Bezugnahme auf Spalte L und Zeile 4). Die Funktion gibt folgende Werte zurück:

• 1, wenn die Zahl positiv ist;
• 0 (Null), wenn die Zahl 0 ist;
• -1, wenn die Zahl negativ ist.

Das mag im Einzelfall eine wertvolle Information an sich sein. Ebenso kann diese Information selbstverständlich durch andere Funktionen (beispielsweise WENN-Funktionen) weiterverarbeitet werden.

Beim Arbeiten mit Vorzeichen steht zudem noch die Funktion ABS zur Verfügung. Diese liefert als Ergebnis immer den absoluten Wert einer Zahl – und damit stets eine positive Zahl.

 
Extra-Tipp: Mit ABS können Sie also negative Zahlen in positive umwandeln. Dies ist beispielsweise beim falschen Import von Zahlen oder zu Darstellungszwecken in Diagrammen bzw. Tabellen bedeutsam. Sollen hingegen alle Vorzeichen umgedreht werden, dann ist es übrigens am besten, die gesamte Spalte *-1 zu rechnen (z.B. „=L4*-1“).

92. Quartal berechnen/aus Datum ableiten

Eine grundlegende Datums-Funktion steht leider nicht standardmäßig in Excel zur Verfügung – die Berechnung des Quartals auf Basis eines gegebenen Datums. Wie so häufig gibt es hierfür aber einen Trick: Über die Formel in der Abbildung unten können Sie zu jedem beliebigen Datum das Quartal ermitteln.

Die Funktion AUFRUNDEN wurde in Tipp 29 und die Funktion MONAT in Tipp 63 dargestellt.

93. TEILERGEBNISSE erstellen

Sobald Zahlen nach bestimmten Merkmalen in Listen gruppiert sind, kommt häufig die Frage nach entsprechenden Zwischenergebnissen auf. Eine manuelle Aufsummierung ist insbesondere bei großen Tabellen nicht nur zeitaufwendig, sondern auch sehr fehleranfällig. Hierfür gibt es die Excel-Funktionalität DATEN → GLIEDERUNG → TEILERGEBNIS. Mit dieser lassen sich Teilergebnisse nach Kategorien relativ einfach erstellen. Siehe hierzu die Abbildung unten, in der die Gruppierung nach Kunden erfolgt.

94. Mit TEILERGEBNIS nur gefilterte Werte summieren

Die Excel-Funktion TEILERGEBNIS ist eine gute Hilfe für die Berechnung von Teilergebnissen bzw. Zwischenergebnissen. Wann ist dies erforderlich? Nun es gibt zwei Hauptanwendungsfälle:

• Sie operieren in längeren Listen mit Filtern und wollen nur Auskunft über den Teil der gefilterten Informationen erhalten, oder
• Sie wollen mit Zwischenergebnissen in einer nach mehreren Merkmalen gruppierten (nach Kunden, Land, Artikel, Monat/Quartal, Verkaufsgebiet etc.) Liste arbeiten wollen (siehe den vorherigen Tipp).

Mit den normalen Standard-Formeln haben Sie hier ein Problem, da standardmäßig alle Daten berechnet werden und eben nicht nur die Gefilterten. Dies führt mitunter zur Mehrfach-Zählung oder mehrfach erstellten manuellen Bezügen.

Um Teilergebnisse berechnen zu können, die von Excel bei der Gesamtsumme ignoriert werden, benötigen wir deshalb die Funktion TEILERGEBNIS. Diese erkennt automatisch, wenn es geschachtelte Teilergebnisse gibt, sodass die Zwischensummen im Gesamtergebnis nicht doppelt gezählt werden. Auch können durch TEILERGEBNIS verschiedene Aggregatfunktionen (wie ANZAHL, MITTELWERT, MIX, MAX etc.) und nicht nur SUMME verwendet werden. Außerdem können Sie mit TEILERGEBNIS festlegen, ob etwaige ausgeblendete Zeilen in die Berechnung einbezogen oder ignoriert werden sollen. Verwenden Sie die Funktions-Konstanten 1 bis 11, wenn Sie das Teilergebnis von ausgeblendeten und nicht ausgeblendeten Zahlen in einer Liste berechnen möchten (siehe die untere Abbildung).

Mit den Funktionskonstanten 101 bis 111 ignoriert die Funktion TEILERGEBNIS hingegen Datenwerte, die manuell ausgeblendet wurden.

95. AGGREGAT

Eine Alternative zu TEILERGEBNIS (siehe vorheriger Tipp 94) stellt AGGREGAT dar. Die ab Excel 2010 existierende Funktion AGGREGAT ist dabei sogar noch flexibler. Mit AGGREGAT können wie bei TEILERGEBNIS ebenfalls verschiedene (allerdings noch ein paar mehr) Aggregat-Funktionen (wie Summe, Anzahl, Mittelwert, Min, Max etc.) auf eine Liste oder Datenbank angewendet werden. Zudem bietet AGGREGAT noch mehr Optionen, wenn es darum geht, ausgeblendete Zeilen sowie Fehlerwerte zu ignorieren.

Unter Optionen bestimmen Sie, welche Elemente in der Berechnung ignoriert werden sollen (ausgeblendete Zeilen, Fehlerwerte, andere TEILERGEBNIS- und AGGREGAT-Funktionen sowie „Nichts ignorieren“). Ausgeblendete Zeilen beziehen sich dabei sowohl auf ausgeblendete, gefilterte als auch auf gruppierte Zellen. Insbesondere die Option, auch Fehlerwerte ignorieren zu können, ist sehr hilfreich, da die normalen Standard-Funktionen hieran scheitern: Ein Fehlerwert in einem Bereich wird auch zu einem Fehlerwert in einer Funktion führen.

Die Abbildung unten zeigt eine Anwendung von AGGREGAT als Summe, wobei mit den existierenden sieben verschiedenen Optionen bestimmte Werte aus der Berechnung ausgeschlossen werden.

96. Bei langsamen Dateien volatile Funktionen dosiert einsetzen

Volatile bzw. schwankungsanfällige Funktionen (wie die insbesondere im Reporting sinnvoll einsetzbaren Funktionen INDIREKT und BEREICH.VERSCHIEBEN) werden jedes Mal neu kalkuliert, wenn irgendwo in der Datei Daten eingegeben/gelöscht/kopiert/sortiert/gefiltert etc. werden – auch wenn die Aktion diese Funktion gar nicht betrifft. In der Folge werden entsprechend auch alle von diesen volatilen Funktionen abhängigen Zellen neu berechnet.

Normalerweise prüft Excel, welche Zellen betroffen sind und rekalkuliert/berechnet dann nur diese neu – damit nicht die gesamte Datei neu berechnet werden muss. Dies ist bei volatilen Funktionen anders. Das ist nicht zwangsläufig ein Problem, und es kann gut sein, dass viele Ihrer Dateien hiervon gar nicht nachteilig betroffen sind. Öffnet und arbeitet eine Datei aber sehr langsam, ist es durchaus möglich, dass ein Grund hierfür der übermäßige Einsatz von volatilen Funktionen ist. In solchen Fällen können/sollten Sie diese – je nach Fragestellung – durch nicht-volatile Funktionen wie etwa INDEX, VERGLEICH oder WAHL ersetzen. Folgende Funktionen gelten ebenfalls als volatil:

• HEUTE
• JETZT
• ZUFALLSZAHL/ZUFALLSBEREICH. 

97. HYPERLINK als Formel erstellen

Bei einem Hyperlink (EINFÜGEN-LINK oder per Tastenkürzel Strg + k) handelt es sich um eine Verknüpfung in einer Excel-Datei, über die durch Klicken in eine andere Arbeitsmappe gesprungen bzw. auch eine (neue) Datei oder Website geöffnet werden kann. In umfangreichen Excel-Reports oder Dashboards stellen komfortable Navigationsmöglichkeiten ein Qualitätskriterium dar (siehe auch Tipp 21).

Etwas unbekannter – aber auch flexibler – ist die Möglichkeit, Hyperlinks über eine Funktion erstellen zu können. Eine sehr gute und für die Praxis hilfreiche Einsatzmöglichkeit der Hyperlink-Funktion ist ein dynamischer Link, der auf die letzte bzw. die nächste leere Zelle einer Tabelle verlinkt. Da der Link dynamisch ist, passt er sich so einer Änderung der Tabellenlänge, beispielsweise bei einer (regelmäßigen) Erweiterung von Einträgen, automatisch an. Dies ist besonders bei langen Listen praktisch, die fortlaufend ergänzt werden, da es mühevolles Scrollen (und die dafür benötigte Zeit) erspart. So kann der nächste Eintrag direkt eingegeben werden.

HYPERLINK setzt sich aus drei wesentlichen Teilen zusammen:

• der Name der Datei, hier: [Bestoftherest.xlsx];
• dann der Name des Tabellenblatts mit Spalte (hier „‘Hyperlink‘!B“);
• der Zeilenbezug.

Die Angaben des Zeilenbezugs müssen im Fall dynamisch ermittelt werden. Dies geht über den Operator „&“ verknüpft mit ANZAHL2. ANZAHL2 zählt alle Einträge in Spalte B: Die Verknüpfung von Spalte B mit der Anzahl der Einträge ergibt damit die letzte eingetragene Zeile/Zelle in dieser Spalte. Das in ANZAHL2 enthaltene „+1“ erhöht diesen Wert eben um genau 1, so dass der Link zur ersten leeren Zelle nach der letzten befüllten Zelle führt (hier also zu Zelle B12, vgl. die Abb. unten.).

Als „Anzeigename“ wurde zudem optional "hier klicken um Ihren Eintrag zu machen" gewählt.

98. UMWANDELN

Diese Funktion ist relativ unbekannt, dabei doch für viele Fragestellungen durchaus nützlich. Sie wandelt eine Zahl aus einer Maßeinheit in eine andere um. Beispielsweise kann UMWANDELN eine Tabelle mit Entfernungen in Meilen in eine Tabelle mit Entfernungen in Kilometern umwandeln; Gleiches gilt für die Umwandlung von Kilogramm in Pfund, von Litern in Gallonen oder bei der Temperatur von Grad Fahrenheit in Grad Celsius. Demnach brauchen Sie die Umrechnung nicht im Kopf haben bzw. manuell über die Umrechnungseinheit durchführen.

Die Möglichkeiten sind vielfältig und können im Detail am einfachsten in der Excel-Hilfe bzw. bei Eingabe der Maßeinheiten nachvollzogen werden; unter anderem stehen Maßeinheiten in den folgenden Bereichen zur Verfügung:

• Gewicht und Masse
• Entfernung
• Zeit
• Druck
• Kraft
• Energie
• Leistung
• Temperatur
• Geschwindigkeit
• Volumen/Flüssigkeitsmaße
• Information (Bit und Byte).

In der Abbildung (unten) werden verschiedene typische Umwandlungen alternativer Mess-Systeme exemplarisch durch Einsatz dieser Funktion aufgezeigt.

99. Vergleichen von Informationen mit IDENTISCH

Die kleine, aber feine Funktion IDENTISCH(TEXT1;TEXT2) prüft, ob zwei Zeichenfolgen identisch sind. In diesem Fall wird WAHR zurückgegeben. Andernfalls vermerkt die Funktion den Wert FALSCH. Wir können also überprüfen, ob in zwei Zellen der gleiche Wert steht. Dies ist besonders dann hilfreich, wenn Sie mit sehr vielen Werten arbeiten, bei denen mit bloßem Auge nicht einfach oder schnell ersichtlich ist, ob es sich um dieselben Werte handelt. Der klassische Anwendungsfall sind zwei längere Listen, in denen Werte abgeglichen werden müssen.

Wir machen dies nachfolgend anhand eines vereinfachten Beispiels deutlich. Wie in der Abbildung unten ersichtlich, beachtet IDENTISCH die Groß- und Kleinschreibung, ignoriert aber Formatierungsunterschiede.

100. WIEDERHOLEN

Sog. Tabellen-Diagramme können Aussagen verdeutlichen, indem sie umfangreiche Zahlenkolonnen in einer besser verständlichen Grafik zusammenfassen bzw. eine Tabelle durch zusätzliche, für den Empfänger – im Vergleich zu reinen Zahlendarstellungen – besser wahrnehmbare visuelle Informationen ergänzen. Neben Excel-Funktionalitäten, wie Sparklines (Miniaturgrafiken als Instrument zur Darstellung von Trendentwicklungen) und der bedingten Formatierung, geht dies gleichermaßen sehr gut mit der Textfunktion WIEDERHOLEN. Diese steht auch in älteren Excel-Versionen zur Verfügung.

WIEDERHOLEN wiederholt ein geeignetes Textzeichen um eine zu definierende Anzahl (x-mal). Symbole (wie Kreise, Pfeile, Blöcke, Dollarzeichen etc.) können dabei über die Tastatur, das Symbol-Menü (START → EINFÜGEN → SYMBOLE) oder das Verwenden von Symbol-Schriftarten (wie Webdings oder Wingdings) erzeugt werden. Der Multiplikator repräsentiert die zugrunde liegenden Daten, die durch Wiederholung des Symbols dargestellt werden sollen. Der Multiplikator kann eingegeben, verlinkt oder auch über Formeln ermittelt werden. Um sich an den zur Verfügung stehenden Platz (je nach Breite der Spalte) anzupassen oder eine Normierung von Daten umzusetzen, kann – je nach Anwendungsfall – auch ein Anpassungsfaktor festgelegt werden, damit die Balken genau in die gewünschte Spaltenbreite passen.

Über die Tabellenfunktion WIEDERHOLEN können Sie beispielsweise Ratings visualisieren oder (durch Wahl eines sich nicht wiederholenden, sondern durchgängigen Zeichens (hier: Symbol „Full Block“)) auch flexibel Datenbalken in Tabellen zur Darstellung von Beträgen (z.B. Soll-Ist-Abweichungen) erstellen (siehe die Abb. unten).

101. Nutzen und Anwendungs-Voraussetzungen von Pivot-Tabellen

In den folgenden Excel-Tipps sollen viele nützliche Hinweise zu Pivot-Tabellen vorgestellt werden. Pivot-Tabellen dienen der Gruppierung von Daten und ermöglichen die Zusammenfassung großer Datenmengen in übersichtlichen Reports. Darüber hinaus können durch die Interaktivität der Funktion (d.h. Wechselbeziehung zwischen zwei oder mehreren beliebigen Größen) auch Daten aus verschiedenen Betrachtungswinkeln analysiert und visualisiert werden.

Pivot-Tabellen sind ein wirkungsvolles Instrument zur Datenanalyse, mit dem Sie Daten gruppieren, ausblenden, filtern und auswerten können – und das ohne den Datenbestand selber, also die Excel-Tabelle, verändern zu müssen. Die zu analysierenden Basis- oder Rohdaten müssen allerdings die in der unteren Abbildung gezeigten Anforderungen erfüllen, um für die weitere Verarbeitung mittels einer Pivot-Tabelle geeignet zu sein.

102. Pivot-Tabellen als Vorlage ab Excel 2013 

Das Erstellen einer Pivot-Tabelle ist grundsätzlich recht einfach – viel einfacher als die meisten denken zumindest. Manchmal kann es jedoch schwer sein, einen Anfang zu finden, insbesondere wenn besonders viele Daten vorliegen.

Hierfür gibt es ab Excel 2013 eine zusätzliche Unterstützungsmöglichkeit durch die Funktionalität „empfohlene PivotTable“ (unter EINFÜGEN). Diese empfiehlt mehrere Möglichkeiten, wie die vorhandenen Daten mit Pivot zusammengefasst werden können. Außerdem gibt es eine kurze Vorschau des Feldlayouts. Für Pivot-Beginner kann es also durchaus eine Alternative sein, mit solch einer empfohlenen Tabelle zu starten und diese dann Stück für Stück an die eigenen Analyse-Zwecke anzupassen.

103. Pivot-Tabellen – den Drill-Down nutzen

Pivot-Tabellen besitzen eine Drill-Down-Funktion: Nach einem Doppelklick auf einen bestimmten, durch Pivot aggregierten Zahlenwert können Sie sehen, aus welchen einzelnen Ursprungswerten sich dieser jeweils zusammensetzt. Excel erstellt dann ein neues Arbeitsblatt in der Datei, in dem alle beteiligten Daten als Kopie aufgelistet werden. Dies kann zum einen zum Beantworten von Detailfragen des Managements, wie sich ein Wert zusammensetzt, sehr hilfreich sein. Zum anderen bietet es sich auch als Überprüfungsmöglichkeit an, ob Pivot/Excel wirklich richtig rechnet bzw. ob die Daten-Qualität den Erfordernissen einer Pivot-Tabelle entspricht.
Zur Nutzung von Pivot-Tabellen vgl. u.a. die Excel-Tipps 101 und 102.

104. Pivot-Tabellen – Formatierungen nach Aktualisierung beibehalten

Normalerweise werden manuell vorgenommene Formatierungen in Pivot-Tabellen nach einer Aktualisierung wieder entfernt. Dies ist meist unerwünscht und kann mitunter sehr viel unnötigen Aufwand bedeuten. Um die Formatierung beizubehalten, ist unter PivotTable-OPTIONEN der Haken zu setzen unter ZELLFORMATIERUNG BEI AKTUALISIERUNG BEIBEHALTEN.

105. Pivot-Tabellen – einfach sortieren

Sie wollen in einem Pivot-Bericht ein spezielles Produkt (eine Gesellschaft, einen Kunden oder Zulieferer) oben oder unten sehen? – Kein Problem! Markieren Sie einfach das gewünschte Produkt und ziehen Sie es mit der Maus (an dem Kreuz) an die gewünschte Stelle – schon wird der Pivot-Bericht wie gewünscht umsortiert.

Um einen Pivot-Bericht (nach Größe oder Alphabet) zu sortieren, einfach die rechte Maustaste klicken und auf Sortieren gehen (siehe die Abb. unten).

106. Pivot-Tabellen – Den (Berichts-)Filter gezielt einsetzen (Teil 1)

Mit dem Filter kann eine Vorselektion in einem Pivot-Bericht vorgenommen werden, beispielsweise um sich so nur die gerade interessierende(n) Produktkategorie(n) anzeigen zu lassen. So können (etwa im Rahmen eines Meetings mit verschiedenen Vertriebsteams) immer nur die gerade interessierenden Produkte/Kunden besprochen/analysiert werden. Mithilfe des (Berichts-)Filters lassen sich folglich die Anzeige großer Datenmengen leichter verwalten und nur der gerade interessierende Teil der Daten eines Berichts in den Vordergrund stellen.

Das benötigte Datenlabel (hier „Kategorie“) ist in den „Filter“ oben links zu ziehen. Hiermit kann der gesamte Bericht gemäß dem im Berichtsfilter ausgewählten Element gefiltert werden (siehe die Abb. unten).

107. Pivot-Tabellen – Den (Berichts-)Filter gezielt einsetzen (Teil 2)

Nicht jeder soll jede Information sehen können, sondern eben nur die für seinen Zuständigkeitsbereich relevante. Stellen Sie sich vor: Sie haben einen Pivot-Bericht kreiert, der viele Details über einzelne Produktkategorien (hier: Getränke, Gewürze etc.) enthält. Zugleich wollen Sie dieselbe Zusammenfassung noch einmal separat für jede Kategorie erhalten, beispielsweise um den nach Produktkategorien spezialisierten Vertriebsteams jeweils nur die für sie relevanten Details zu den einzelnen Produktkategorien weiterschicken zu können. Ähnliches kann in der Praxis für viele Fragestellungen gelten, beispielsweise auch für Informationen zu Kunden, Regionen, Standorten, Gesellschaften etc.

Für diese Fragestellung hat Pivot eine einfache und ungemein schnelle Lösung zu bieten – und zwar mit dem (Berichts-)Filter (siehe den vorherigen Tipp Nr. 106) in Verbindung mit der Funktionalität BERICHTSFILTERSEITEN ANZEIGEN. Es ist dabei wie folgt vorzugehen:

Das benötigte Datenlabel (hier „Kategorie“) ist in den „Filter“ oben links zu ziehen (siehe vorheriger Tipp).

In Verbindung mit BERICHTSFILTERSEITEN ANZEIGEN (unter PIVOTTABLE → TOOLS → ANALYSIEREN → OPTIONEN) wird für das gefilterte Datenlabel (hier: Produktkategorie) eine Extra-Seite für alle darin enthaltenen Werte – im Beispiel also alle vorhandenen Produktkategorien – in genau dem gleichen Pivot-Format ausgegeben. Dies sollte hiermit auch erreicht werden – die Arbeitsblätter für die Produktkategorien können nun einzeln aufgerufen, ausgedruckt oder auch elektronisch an die relevanten Adressaten weitergeleitet werden. Ziel erfüllt!

108. Flexibel Werte aggregieren mit Pivot

Sie können natürlich nicht nur Summen mit Pivot bilden, sondern auch andere Berechnungen (ähnlich zu alternativ möglichen Formellösungen) durchführen. So lassen sich etwa Durchschnitte berechnen oder auch Minima/Maxima ermitteln. Hierzu muss die Berechnungsfunktion im Kontextmenü des entsprechenden Datenlabels im Feld WERTE der PivotTable-Feldliste angepasst werden – und zwar über WERTFELDEINSTELLUNGEN → WERTE ZUSAMMENFASSEN NACH (hier: „Mittelwert“, vgl. auch die Abb. unten).

109. Anteile berechnen mit Pivot

Neben der Anpassung der Berechnungsfunktion (siehe vorheriger Tipp Nr. 108) kann auch die Berechnungsoption (der Bezugspunkt für die Berechnung) angepasst werden. Dies eignet sich insbesondere zur Durchführung prozentualer Anteilsberechnungen.

Um beispielsweise zu ermitteln, welchen Anteil die einzelnen (Produkt-)Kategorien am Gesamtumsatz (in den jeweiligen Quartalen) haben, ist unter WERTFELDEINSTELLUNGEN → WERTE ANZEIGEN ALS die gewünschte Option (hier „% des Spaltengesamtergebnisses“) zu wählen (vgl. auch die untere Abb.).

110. Duplikate entfernen mit Pivot

Nach DATEN → DUPLIKATE ENTFERNEN (siehe Tipp 19) ist Pivot die einfachste Lösung, um aus einer (gegebenenfalls sehr langen) Liste mit mehrmals vorkommenden Werten/Texten eine Liste aus Unikaten – also nur einmal vorkommenden Werten – zu erstellen. Hierzu ist die Liste einfach in eine Pivot-Tabelle (EINFÜGEN → PIVOTTABLE) zu überführen und die zu bereinigende Spalte (hier: „Kategorie“) in die ZEILEN zu ziehen.

111. Duplikate zählen mit Pivot

Um Duplikate (mehrmals vorkommende Werte/Texte) nicht nur zu entfernen (siehe den vorherigen Tipp, Nr. 110), sondern auch deren Vorkommen/die Anzahl ihres Auftretens zu zählen, ist wie folgt vorzugehen:

• Liste einfach in eine Pivot-Tabelle (EINFÜGEN → PIVOTTABLE) überführen
• Die zu bereinigende Spalte/das Datenlabel (hier: „Kategorie“) in die ZEILEN ziehen (siehe vorheriger Tipp, Nr. 110);
• Dieselbe Spalte/das Datenlabel (hier: „Kategorie“) auch in die WERTE ziehen und ANZAHL einstellen (siehe die Abb. unten).

112. Pivot-Tabellen – Daten gruppieren

Um einzelne Tagesdaten (z.B. die Tages-Umsätze) in eine gewünschte/benötigte Monats- oder Quartals-Darstellung umzuwandeln bzw. zu aggregieren, eignet sich die GRUPPIEREN-Funktionalität von Pivot. Dies geht – gerade auch im Vergleich zu etwaigen Formel-Lösungen – sehr einfach. Hierzu ist das Datenlabel „Datum“ in die ZEILEN zu ziehen und zu GRUPPIEREN (auf einen Datumswert stellen, rechte Maustaste klicken). In der Folge erscheint das Menü aus der unteren Abbildung, und es ist für eine Quartals-Darstellung entsprechend „Quartale“ auszuwählen und mit OK zu bestätigen.

Extra-Tipp: Liegen Werte über mehrere Jahre vor, so müssen Sie unbedingt immer noch „Jahre“ bei der Gruppierung mit anklicken. Ansonsten werden einfach die Monate oder Quartale unabhängig von ihrem Jahr zusammengefasst.

113. Pivot-Tabellen – Texte/Kategorien gruppieren

Genau wie im Excel-Tipp 112 „Daten gruppieren“ (von Tagen zu Quartalen) können Sie mit Pivot auch Texte gruppieren. So können in unserem Beispielsreport (siehe Abb. unten) beispielsweise die Produktkategorien (dies gilt prinzipiell auch für alle anderen zur Verfügung stehenden Datenlabel) noch weiter gruppiert werden – falls dies notwendig sein sollte.

Beispielhaft sollen „Öl“, „Saucen“ und „Suppen“ in die Kategorie „Sonstiges“ aggregiert werden. Hierzu können Sie die drei Einträge (innerhalb der erstellten Pivot-Tabelle!) markieren und mit der rechten Maustaste GRUPPIEREN wählen (vgl. Abb.). Die so entstehende neue „Gruppe1“ kann dann einfach – wie gewünscht/benötigt – benannt werden.

114. Größenklassen mit Pivot gruppieren

Eine in der Praxis übliche Analysefunktionalität ist das Gruppieren von Zahlen/Bildung von Größenklassen. Dies ist beispielsweise sinnvoll, wenn Sie sich speziell Kunden/Produkte mit nur kleinen – oder eben nur großen – Umsatzerlösen, Deckungsbeiträgen, Absatzmengen etc. anschauen wollen. Das Bilden solcher Zahlengruppen oder -kategorien funktioniert ebenfalls über GRUPPIEREN (siehe auch die beiden Tipps zuvor). Es ist dabei wie folgt vorzugehen:

• Die zu gruppierenden Zahlen/Werte (Umsätze etc.), für die Größenklassen gebildet werden sollen, müssen hierfür in die ZEILEN und WERTE geschoben werden.
• Es ist nun auf einen Wert in den ZEILEN zu gehen, die rechte Maustaste zu drücken und GRUPPIEREN zu wählen.
• Das daraufhin erscheinende Menü kann dann zu Steuerung der Größenklassen verwendet werden. Und zwar können hier Start- und Endwerte sowie die einzelnen Klassengrößen ausgewählt werden. In unserem Fall sollen zur Illustration alle Umsätze größer 100.000 bis zu maximal 1 Mio. (und zwar in 100 T-Schritten) abgebildet werden (vgl. die untere Abb.).

Das führt nun zur Gruppierung der Umsätze nach diesen Vorgaben:
Umsätze kleiner 100.000 werden in einer Gruppe gruppiert und Umsätze größer 1 Mio. ebenfalls. Dazwischen werden die Gruppen jeweils in Schritten von 100.000 gebildet.

115. Pivot-Tabellen – Datenanalyse mithilfe des Datenschnitts Zeitachse (ab Excel 2013)

Ab Excel 2013 steht in Pivot noch ein weiterer Datenschnitt-Typ (siehe bereits BC-Tipp 45), die sog. Zeitachse (oder auch Timeline), zur Verfügung. Wie der Name schon andeutet, können damit Datenfilter über beliebige Zeitschienen gesetzt werden. Das heißt: Mit der Zeitachse können Daten einfach und schnell auf Basis eines vorgegebenen Zeitfensters gefiltert werden. Die Timeline kann – je nach Datenmaterial und Analysezweck – auf Jahre, Quartale, Monate oder sogar Tage gestellt werden (vgl. die untere Abb.).

Diese Funktion ist sehr nützlich (und wie die „normalen“ Datenschnitte) vergleichsweise einfach und intuitiv einsetzbar – aber eben wesentlich flexibler für Datumsanalysen.

Die Timeline wird unter PIVOTTABLE → TOOLS → ANALYSIEREN → FILTERN → ZEITACHSE EINFÜGEN eingefügt.

116. Pivot-Tabellen – eigene Felder berechnen

Für Kalkulationen, das Ausweisen von Berechnungen, die sich nicht direkt in den Roh-Daten befinden (z.B. prozentuale Abweichungen von Ist- und Plan-Umsätzen), gibt es prinzipiell zwei Möglichkeiten:

• Entweder werden sie im Vorfeld berechnet und dann als bereits vorhandenes Datenlabel im Rahmen der Auswahl des Datenbereichs für die Pivot-Tabelle erfasst,

• oder sie werden durch Einfügen eines berechneten Datenlabels in der Pivot-Tabelle aufgenommen.

  Das Einfügen eines berechneten Felds direkt in der Pivot-Tabelle bietet die Möglichkeit, die Rohdaten auf das notwendige bzw. exportierte Maß zu beschränken und ist daher empfehlenswert.

Die Definition und das Einfügen eines berechneten Datenfelds erfolgen dabei unter: EINFÜGEN " PIVOTTABLE " TOOLS " FELDER, ELEMENTE UND GRUPPEN " BERECHNETES FELD. Durch diese Auswahl erscheint ein Menü, in dem der Name (hier: „Delta Plan/Ist“) und die Formel zur Berechnung (hier: Umsatz-Ist/Umsatz-Plan-1) hinterlegt werden kann (vgl. die untere Abb.).

Nach Hinzufügen des Labels „Delta Plan/Ist“ erscheint dieses in der PivotTable-Feldliste (genau wie alle ursprünglich vorhandenen Datensätze). Durch die Zuordnung des berechneten Felds unter WERTE erscheinen anschließend die relativen Ist-Abweichungen in der Pivot-Tabelle.

Dipl.-Kfm. Daniel Unrein, Leiter Corporate Controlling in einem internationalen Dienstleistungs-Unternehmen, Verfasser des bei Vahlen erschienenen Fachbuchs „Excel im Controlling”

BC 2015 / 2016 / 2017 / 2018 / 2019 / 2020 / 2021  / 2022 / 2023 / 2024

becklink372380