Wie du in Pivottabellen den Beschriftungsfilter einsetzt

Eine Pivottabelle erstellen

Um eine Pivottabelle erstellen zu können, ist zunächst eine Datensammlung in einer Datenbank erforderlich.

Wie es dann weiter geht, habe ich in meinem Beitrag zu Absatzmengenplanung gezeigt.

In diesem Beitrag will ich dir die Funktionsweise des Beschriftungsfilters erklären.

Zunächst erstellst du die Datenbank für deine Pivottabelle. Darin enthalten sind Informationen zur Region, zum Filialort, zum Produkt sowie zugehörige Mengenangaben.

PivotSchrift1

Daraus erstellst du eine Pivottabelle. Du erinnerst dich?

Menü Einfügen / Tabellen / PivotTabel / PivotTable

Der Berichtsfilter soll die Region enthalten. In die Zeilenbeschriftungen schiebst du die Orte, in die Spaltenbeschriftungen die Produkte. Der Wertebereich schließlich soll die Mengen enthalten.

PivotSchrift2

Auf eine Spalte Gesamtergebnis kannst du in der Zeile verzichten, denn es macht keinen Sinn, Betten und Stühle zu addieren. Klicke dazu mit der rechten Maustaste in die Pivottabelle und entferne in den PivotTable-Optionen den Haken unter Summen & Filter.

Wie gewohnt, kannst du mit den Filtern der Pivottabelle nun Regionen, Orte und Produkte auswählen.

Einen Beschriftungsfilter setzen

Der Beschriftungsfilter ermöglicht es dir zusätzlich, z.B. nur alle Orte anzuzeigen, die ein „o“ enthalten. Klicke dazu auf den Pfeil neben Zeilenbeschriftungen.

PivotSchrift3

Wähle dort „Enthält“ und gib ein „o“ ein.

PivotSchrift4

Bestätige mit OK und die Pivottabelle zeigt nur noch diese Daten:

PivotSchrift5

Alle weiteren möglichen Varianten siehst du im Screenshot oben.

 

Sparklines, die kleinen Diagramme in Excel

Was sind Sparklines?

Der Google-Übersetzer übersetzt das Wort „sparkline“ vom Englischen ins Deutsche mit „Sparkline“, „spark line“ aber mit „Funkenlinie“.

Das hilft noch nicht allzu viel, um zu erklären, was Sparklines sind. Aber es gibt die Excel-Hilfe. In diesem Fall gebe ich gern zu, dass ich hier gute Erläuterungen gefunden habe.

Sparklines sind danach sehr kleine Diagramme im Zellenhintergrund eines Arbeitsblattes. Dadurch ist es möglich, zusätzlich zur Sparkline auch noch einen Text in der Zelle unterzubringen. Sie sind kleine Objekte, wie die richtigen Diagramme.

Mit Sparklines lassen sich z.B. saisonale Auf- und Abschwünge von Datenreihen darstellen. Es lassen sich erste oder letzte Werte, der höchste oder niedrigste Wert hervorheben u.v.a.m.

Sparklines haben den Vorteil, dass sie klein sind und in einer Zelle untergebracht werden können. Werden sie unmittelbar neben der Wertereihe angeordnet, ist der Zusammenhang mit der Wertereihe unmittelbar erkennbar. Trends der Wertereihen werden so klar dargestellt.

Wie werden Sparklines erstellt?

Folgende Tabelle zeigt die mengenmäßigen Quartalsverkäufe von drei Filialen eines Unternehmens für ein Produkt.

Sparklines1

Sparklines können sowohl für die Zeilen als auch für die Spalten erstellt werde. Du willst dich auf die Zeilen beschränken und die Sparklines unmittelbar in den Zellen rechts der Tabelle unterbringen.  In R4 schreibst du als Überschrift „Entwicklung“, damit wird die Spalte Bestandteil der Tabelle, denn diese ist intelligent.

Gehe nun auf R5 und wähle im Menü Einfügen unter Sparklines z.B. die Linie aus.

Sparklines2

Fülle den entsprechende Datenbereich wie gezeigt aus. Der Positionsbereich enthält bereits einen Eintrag, da du dich in R5 befindest. Nach dem OK befindet sich in R5 ein Sparkline.

Verfahre in R6 und R7 ebenso, die Tabelle sieht nun so aus:

Sparklines3

Auf den ersten Blick siehst du, wie sich die Quartalswerte entwickelt haben.

Wie lassen sich die Sparklines gestalten?

Ein Sparkline ist als Linie erstellt. Berechtigterweise fragst du dich: Ist das alles? Oder was kann ich noch damit machen?

Klicke auf die Zelle mit dem Sparkline und dann in der Menüzeile unter den Sparklinetools auf Entwurf. In der Gruppe „Typ“ findest du die drei Möglichkeiten, ein Sparkline darzustellen:

Sparklines4

Die Gruppe „Formatvorlagen“ zeigt dir mögliche Vorlagen, die sich aber nur farblich unterscheiden.

Da die Dateireihe für die Beispiel-Sparkline auch eine negativen Wert enthält, ist es sinnvoll, eine Achse einzufügen. Ruf dazu in der Gruppe „Gruppieren“ das Menü „Achse“ auf und klicke „Achse anzeigen“ an.

Die Sparklines sehen dann so aus:

Sparklines5

Interessant ist die Gruppe „Anzeigen“. Darin kannst du bestimmte Punkte durch Hakensetzung markieren, so den Höchstpunkt, den Tiefpunkt, negative Werte, den ersten oder letzten Punkt und mit „Markieren“ alle Punkte in der Liniendarstellung.

Sparklines6

In der Gruppe „Formatvorlagen“ findest du noch Menüs zur Auswahl der Sparkline- bzw. der Datenpunktfarbe.

In der Gruppe „Sparkline“ gibt es ein Menü „Daten bearbeiten“. Darin kannst du den Quelldatenbereich für ein Sparkline neu definieren.

Schließlich gibt es in der Gruppe „Gruppieren“ das Menü „Löschen“, mit dem du ausgewählte Sparklines oder Sparklinegruppen löschen kannst.

Was noch fehlt?

Du kannst die Zelle, in der sich ein Sparkline befindet, wie jede andere Zelle formatieren. Zumindest kann ein Rahmen gesetzt und eine Ausfüllfarbe ausgesucht werden.

Prüfung, ob vertikale und horizontale Summen identisch sind

Enthält eine Tabelle mehrere Spalten und mehrere Zeilen, Zeilensummen und Spaltensummen sowie eine Gesamtsumme, vertraue ich oftmals darauf, alle Formeln zur Summierung richtig eingetragen zu haben.

Wie darüber die Kontrolle behalten werden kann, ist Gegenstand dieses Beitrages.

Die folgende Tabelle zeigt beispielsweise die produzierten Mengen aus sechs Werken für die ersten sechs Monate eines Jahres.

Spaltensumme1

Die Zellen B11:G11 sollten richtigerweise die Mengen der Werke 1 bis 6 addieren.

Die Zellen H5:H10 sollten ebenso richtig die Mengen der Monate Januar bis Juni addieren.

Und schließlich soll in Zelle H11 entweder die Summe von B11:G11 oder die Summe der Werte aus H5:H10 gebildet werden.

Die Summe aus B11:G11 und die Summe aus H5:H10 müssen identisch sein. In H11 kann aber nur eine Formel eingetragen werden. So kann es durchaus passiert sein, das irgendeine Summe einen Wert aus der Spalte oder aus der Zeile „vergessen“ hat. Ein solcher Fehler ist aber nicht sofort sichtbar.

Wie lässt sich im Arbeitsblatt eine Kontrolle darüber einflechten?

Eine Möglichkeit ist, irgendwo auf dem Arbeitsblatt, z.B. in I11, eine der folgenden Formeln einzutragen:

  • =WENN(SUMME(H5:H10)=SUMME(B11:G11);“OK“;“Fehler“)
  • =IDENTISCH(SUMME(H5:H10);SUMME(B11:G11))
  • =SUMME(H5:H10)=SUMME(B11:G11)

Die zweite und dritte Formel geben als Ergebnis ein „WAHR“ oder ein „FALSCH“ heraus.

Darüber hinaus kann durch eine bedingte Formatierung die Zelle H11 manipuliert werden, so dass sofort ersichtlich ist, ob die Summenformeln richtig sind.

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)<>SUMME(B11:G11)

formatieren / ausfüllen: rot

und als zweite Formel:

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)=SUMME(B11:G11)

formatieren / ausfüllen: grün

Ergänzend kann unterhalb von H11, z.B. in H13, noch eine kurze Erläuterung des Fehlers konstruiert werden. Dazu kannst du diese Formel verwenden:

=WENN(SUMME(H5:H10)<SUMME(B11:G11);“Zeilensumme ist kleiner“;WENN(SUMME(B11:G11)<SUMME(H5:H10);“Spaltensumme ist kleiner“;“OK“))

Folgende Tabelle enthält in der Summe des Monats April (gelb markiert) einen Fehler. Und der wird angezeigt:

Spaltensumme2

Wenn ein Fehler vorhanden ist und dies auch angezeigt wird, müssen die Zeilen-, Spalten- und Gesamtsummen nur überprüft und korrigiert werden. Dann ist H11 wieder grün.

Wie Wirtschaftsgüter linear abgeschrieben werden

In der Funktionsbibliothek von Excel findest du in der Kategorie Finanzmathematik mehrere Funktionen für Abschreibungen.

Was sind Abschreibungen?

Abschreibungen sind im betriebswirtschaftlichen Verständnis Ausdruck für den Werteverzehr eines Wirtschaftsgutes. D.h. der Gebrauch eines Wirtschaftsgutes führt zu einer Abnutzung bis es nicht mehr zu gebrauchen ist.

Die wertmäßige Abnutzung ist Bestandteil der Kosten eines Unternehmens und findet in der Erfolgsrechnung seinen Niederschlag. Die Ansammlung der über die Erlöse realisierten Abschreibungen dient nach Ablauf der Nutzungsdauer der erneuten Anschaffung eines Wirtschaftsgutes.

Die Betriebswirtschaft kennt mehrere Abschreibungsmethoden. In diesem Beitrag soll es nur um die lineare Abschreibung gehen. Excel bietet hierfür die Funktion LIA.

Zunächst jedoch zeige ich dir den tabellarischen Abschreibungsverlauf. Du kannst es auch Abschreibungsplan nennen.

Angenommen, du hast eine Maschine zum Preis von 84.000 € gekauft und willst sie acht Jahre nutzen. Der Restwert am Ende der Nutzungsdauer soll 0 € sein, d.h. du gehst davon aus, dass du nicht einmal mehr einen Schrotterlös erzielen wirst.

AfALIA1

Wie wurde gerechnet?

In C10 gibst du die Formel

=($C$5-$C$6)/$C$7

ein und ziehst sie bis C17. Damit erhältst du die jährliche Abschreibung.

In D10 schreibst du =C10

In D11 schreibst du =D10+C11 und ziehst sie bis D17.

In Spalte E schließlich zeigst du den Restbuchwert.

Schreibe in E10 die Formel =$C$5-D10 und ziehe sie bis E17 herunter.

Zum Ende von Jahr 8 ist der Restbuchwert 0 bzw. zeigt den veranschlagten Restwert, etwa so:

AfALIA2

Wie verwendest du nun die Funktion LIA?

Die Syntax für die Funktion lautet gemäß Excel-Hilfe:

=LIA(Ansch_Wert;Restwert;Nutzungsdauer)

wobei

  • „Ansch_Wert“ die Anschaffungskosten des Wirtschaftsgutes
  • „Restwert“ der Wert nach Ablauf der Nutzungsdauer, oft auch als „Schrottwert“ bezeichnet
  • „Nutzungsdauer“ die Anzahl der Perioden, über die das Wirtschaftsgut abgeschrieben wird

Mit der Formel beziehe ich mich auf die obigen Vorgaben:

ohne Restwert

=LIA($C$5;$C$6;$C$7)   Ergebnis: 10.500,00 € pro Jahr

mit Restwert

=LIA($C$5;$C$6;$C$7)   Ergebnis: 9.787,50 € pro Jahr

Ebenso kann die Formel in die Tabellen oben in C10:C17 eingesetzt werden.

Statt die Nutzungsdauer in Jahren anzugeben, ist auch der Ansatz von Monaten möglich. Aus 8 Jahren werden 96 Monate.

ohne Restwert
=LIA($C$5;$C$6;96)   Ergebnis: 875,00 € pro Monat

mit Restwert
=LIA($C$5;$C$6;96)   Ergebnis: 815,63 € pro Monat

Das Thema ist hiermit aber noch nicht ausgeschöpft. In einem späteren Beitrag wird es um die Abschreibung auf Wiederbeschaffungswerte gehen.

VBA-Methoden: Eine Datei schließen

Wenn eine Datei geöffnet und bearbeitet wurde, soll sie irgendwann auch wíeder geschlossen werden. Das wiederum kann manuell oder mit einem Makro erledigt werden.

Das manuelle Schließen war schon im Beitrag „VBA-Methoden: Eine Datei speichern“ ein Thema. Dabei wurde vor dem Schließen eine Speicherung mit einem Makro vorgenommen.

Wenn du eine Datei mit einem Makro schließen willst, gibt es auch hierbei verschiedene Möglichkeiten.

Einfaches Schließen einer Datei

Für das Schließen der aktiven Datei wird die Methode Close genutzt. Das geht so:

Sub SchließenDatei1()
ActiveWorkbook.Close
End Sub

Wenn du vorher nicht gespeichert hast, bekommst du diese Mitteilung:

Datei_schließen1

Erst wenn du dann „Speichern“ klickst, wird die Datei geschlossen. Das kannst du umgehen, wenn du den Speichern-Befehl in deinen Code mit einbaust. Zwei Möglichkeiten hast du hierfür:

Sub SchließenDatei2()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

oder so:

Sub SchließenDatei3()
ActiveWorkbook.Close SaveChanges:=True
End Sub

Nach dem Close-Befehl wird zusätzlich die Anweisung gegeben, dass die Speicherung von Veränderungen vorgenommen wird.

Schließen einer zusätzlich geöffneten Datei

Wie im Beitrag „VBA_Methoden: Eine Datei öffnen“ erläutert, hast du z.B. eine weitere Datei manuell oder mit einem Makro geöffnet. Du hast deine Bearbeitungen vorgenommen und willst die Datei nun über dein Makro wieder schließen. Eine Speicherung siehst du ebenfalls vor.

Sub SchließenDatei4()
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

oder so:

Sub SchließenDatei5()
Workbooks(„VBA_Datenblatt.xlsx“).Close SaveChanges:=True
End Sub

Öffnen, Bearbeiten, Speichern und Schießen einer zusätzlichen Datei

Jetzt willst du die Vorgänge vom Öffnen bis zum Schließen einer zusätzlichen Datei mit einem Makro ausführen lassen.

Im Arbeitsblatt „Tabelle1“ der Zusatzdatei soll in Zelle B10 der Text „Bearbeitung ist erfolgt“ eingetragen werden. Die Bearbeitung wird mit einer MsgBox bestätigt.

Sub SchließenDatei6()
Workbooks.Open Filename:=“C:\Excel\VBA_Datenblatt.xlsx“
Worksheets(„Tabelle1“).Range(„B10″)=“Bearbeitung ist erfolgt“
MsgBox(„Eintrag erfolgt“)
Workbooks(„VBA_Datenblatt.xlsx“).Save
Workbooks(„VBA_Datenblatt.xlsx“).Close
End Sub

Schließen einer Datei nach dem Speichern

Die Zusatzdatei soll sofort geschlossen werden, wenn gespeichert wurde.

Sub SchließenDatei7()
If Workbooks(„VBA_Datenblatt.xlsx“).Saved=True Then
Workbooks(„VBA_Datenblatt.xlsx“).Close
Else
MsgBox(„Datei ist nicht gespeichert“)
End If
End Sub


Wurde nicht gespeichert, kann in diesem Fall auch nicht geschlossen werden. Du erhälst deshalb die Mitteilung „Datei ist nicht gespeichert“ mittels einer MsgBox.

Dynamische Datenbeschriftung ohne Nullwerte

Im Beitrag zur bedingten Formatierung von Diagrammen hast du eine zweite Datenreihe, die Ergebnis einer WENN-Abfrage war, ins Diagramm gebracht.

Indem du über Datenreihen formatieren die Reihenachsenüberlappung auf 100% eingestellt hast, entstand bei den Werten, die die Bedingung erfüllten, der Eindruck einer jeweils einzelnen Säule.

Weiterlesen „Dynamische Datenbeschriftung ohne Nullwerte“

Datenüberprüfung: Eine Zeitangabe zulassen

Wenn du in einer oder mehreren Zellen nur den Eintrag von Zeitangaben zulassen willst, gehst du auch hier im Menü Daten auf die Datenüberprüfung.

Es erscheint wieder das bekannte Menü. Diesmal wählst du unter „Zulassen“ die Option „Zeit“ aus.

DatenZeit1

Nur der Vollständigkeit halber, mit Zeit ist eine Uhrzeit gemeint.

Im nächsten Schritt musst du wohl oder übel präziser werden. Excel will es genauer wissen. Unter „Daten“ kannst du eine von acht Optionen wählen.

DatenZeit2

Wenn du sekundengenau arbeiten willst oder musst, musst du die Uhrzeit auch vollständig vorgeben, d.h., es sind Stunden, Minuten und Sekunden anzugeben, etwa so: 00:00:00.

Du entscheidest dich z.B. für „zwischen“ und willst die volle Bandbreite austesten.

Als Anfangszeit trägst du „00:00:00“, als Endzeit „24:00:00“ ein. Nach dem OK erhältst du eine Fehlermeldung. Warum?

Weil 24:00:00 schon wieder 00:00:00 des nächsten Tages ist. 24:00:00 ist hier tabu.

Du kannst daher alle Zeiten zwischen 00:00:00 und 23:59:59 eintragen. Eine Sekunde muss an 24 Stunden immer fehlen.

Du musst aber die Sekunden nicht eingeben, wenn dir die Minute genügt. Excel trägt die Sekunden trotzdem ein.

Trägst du z.B. 14:00 ein, wird in der Maske dann 14:00:00 stehen.

Wie sieht es bei einer anderen Auswahl für Daten aus?

„Nicht zwischen“ 05:00:00 und 10:00:00 heißt, nur Zeiten von 00:00:00 bis 04:59:59 und von 10:00:01 bis 23:59:59 können eingegeben werden.

„Gleich“ 08:05:07 heißt, exakt nur diese Zeit darf dort erscheinen, keine andere. Schon bei 08:05:08 gibt es wieder Fehleralarm.

Ebenso muss bei den anderen möglichen Auswahlen unter Daten genau auf die für zulässig erklärten Zeiten geachtet werden.

 

Nun, ist diese Variante, Daten zuzulassen, ein praktisches Thema für dich? Arbeitest du damit?

Lasse es uns wissen.