Monat: November 2016

Häufigster Wert eines Bereichs ausgeben (auch Text)

Roman Formeln , , , , , , , ,

Mit den Formeln Anzahl oder Zählenwenn können bestimmte Werte gezählt werden. Doch was, wenn unzählige verschiedene Werte in einem Bereich aufgeführt sind und wir nur den häufigsten davon auslesen wollen? Nachfolgend sind unsere Daten, aus welchen wir den häufigsten Wert auslesen möchten (die Farben hatte ich vorgängig mit bedingten Formatierungen erstellt):

datenhaeufigsterwert

Nebenan möchten wir nun ermitteln, welches denn der häufigste Wert ist. Aufgrund der Farben - und da die Liste nur 11 Angaben umfasst - könnten wir auch von Auge ermitteln, dass es "Wert2" ist. Dennoch: Wir möchten dies mit einer Formel ermitteln. Diese lautet:

{=INDEX(A1:A11;VERGLEICH(MAX(ZÄHLENWENN(A1:A11;A1:A11));ZÄHLENWENN(A1:A11;A1:A11);0))}

Die geschweifte Klammer ist nicht selbst einzugeben - es handelt sich um eine Matrixformel! Schliesse die Formel mit CTRL + SHIFT + ENTER ab, um die Matrixformel zu bestätigen.

Das Resultat: Wert2 ist am häufigsten im Bereich "A1:A11". Sobald Du die Werte änderst (bspw. mehr "Wert3" im Bereich aufführst), zeigt Dir die Formel sofort den neuen Wert an.

Kalender 2017

Roman Allgemein , , , , , , ,

Bald ist wieder ein Jahr um - und Vorbereitungs- und Planungsaufgaben für die kommenden 12 Monate starten von Neuem. Ich biete Euch hier einen simplen Kalender für das Jahr 2017 an, welchen Ihr gerne für Eure Bedürfnisse gebrauchen und anpassen könnt:

kalender_2017

Kalender_2017.xlsx

Für detailliertere - und eventuell auch längerfristigere - Planungen findest Du nachfolgend zudem einen "Jumbographen" für die Jahre 2017 bis 2021. Dieser lässt eine detailtreue Planung und das Arbeiten mit Legenden zu. Die einzelnen Jahre können oberhalb der Spaltenbeschriftung ein-/ausgeblendet werden.

jumbograph_2017-2021

Jumbograph_2017-2021.xlsx

Viel Spass damit und eine erfolgreiche Vorbereitung für das neue Kalenderjahr!

Pivot-Datenquelle automatisch erweitern

Roman Formeln, Pivot , , , , , , , , , , ,

Pivot-Tabellen aggregieren innert kürzester Zeit immense Datenmengen. Sobald man der Datenquelle weitere Informationen anfügt, wird dies standardmässig nicht direkt erkannt - der neue Bereich ist jeweils manuell anzupassen:

pivotdatenquelleaendern

Es besteht jedoch die Möglichkeit, die Datenquelle automatisch erweitern zu lassen. Dies funktioniert im Grunde genommen, wie wir das bereits in diesem Beitrag hier mittels Namensgebung für Bereiche behandelt haben. Unserer Pivot-Datenquelle teilen wir auch hier zuerst einen Namen zu: Markiere die gesamte Datenquelle und schreibe ins Namensfeld (links neben der Bearbeitungsleiste) Deinen gewünschten Namen, bspw. "Datenbasis":

pivotdatenquellenamensfeld

Gehst Du nun via "Einfügen" → "PivotTable" kannst Du direkt bei "Tabelle/Bereich:" den definierten Namen ("Datenbasis") eingeben:

(Beachte: Unsere Pivot wird in einem neuen Tabellenblatt sein, nicht im selben wie die Datenbasis.)

Nun ist aber noch nicht konfiguriert, dass sich der Bereich automatisch erweitern soll, sobald weitere Daten angefügt werden (weitere Zeilen, weitere Spalten). Hier kommt die Formel "BEREICH.VERSCHIEBEN" zum Einsatz. Gehe via "Formeln" auf "Namensmanager" und bearbeite Deinen bereits angelegten Namen. In der Maske gibst Du bei "Bezieht sich auf:" die nachfolgende Formel ein:

=BEREICH.VERSCHIEBEN(Datenbasis!$A$1;;;ANZAHL2(Datenbasis!$A:$A);ANZAHL2(Datenbasis!$1:$1))

Auf dem Tabellenblatt mit ebenfalls dem Namen "Datenbasis", beginnend bei Zelle "A1" wird einerseits nach unten (Spalte A:A) gezählt, wie viele Einträge vorhanden sind. Dasselbe passiert auf der Zeile 1.

pivotdatenquellenamensmanager

Der Bereich wird entsprechend daraufhin erweitert, sobald neue Einträge angefügt werden. (Beachte, dass die Pivot-Tabelle noch via Rechtsklick → "Aktualisieren" aufzufrischen ist.)