Excel-Minis 20171128

Excel-Minis sind an dieser Stelle immer Kurzbeiträge, natürlich zum Thema Excel.

Für heute habe ich drei kleine Themen vorbereitet, vielleicht hast du Spaß damit.

1. Zählen, wie oft ein Monat in einem Bereich vorkommt

Diese Aufgabe stammt aus einer Gruppenanfrage in den sozialen Medien.

Angenommen, du hast eine Spalte einer Excel-Tabelle, die mit Datumsangaben gefüllt ist.

ExcelMinis1

Die Aufgabe ist, zu zählen, wie oft z.B. der Monat August in diesem Bereich vorkommt. Den gesuchten Monat trägst du z.B. in E6 ein.

Verwende dazu die SUMMENPRODUKT-Funktion in Zelle E8 mit dieser Formel:

=SUMMENPRODUKT((MONAT(B6:B20)=$E$6)*1)

ExcelMinis2

Willst du zusätzlich die gezählten Werte sichtbar machen, kannst du das mit der bedingten Formatierung umsetzen. Markiere den Bereich B6:B20, gehe über Start / Formatvorlagen auf Bedingte Formatierung / Neue Regel und wähle „Formel zur Ermittlung der zu formatierenden Zellen“ aus.

Trage als Formel ein:

=MONAT($B6)=$E$6

und wähle eine Formatierung aus, im Beispiel war das Ausfüllen mit blauer Farbe.

2. Das älteste Datum und den zugehörigen Betrag finden

Für das Beispiel hast du eine Tabelle mit zwei Spalten vorliegen. In der ersten Spalte steht jeweils ein Datum, in der zweiten ein Geldbetrag. Die Listen sind unsortiert.

ExcelMinis3

Gesucht ist das älteste oder zweitälteste oder drittälteste usw. Datum und der dazugehörige Betrag.

Das älteste Datum findest du mit der statistischen Funktion KKLEINSTE so, setze die Formel in F5 ein:

=KKLEINSTE(B3:B19;$F$3)

Das Argument k soll in Zelle F3 stehen. Suchst du das zweitälteste Datum, ersetzt du k=1 durch k=2.

Die Formel KKLEINSTE setzt du zum Ausgeben des dazugehörigen Betrages in F7 in die Funktion BEREICH.VERSCHIEBEN ein.

=BEREICH.VERSCHIEBEN($B$2;VERGLEICH(KKLEINSTE(B3:B19;$F$3);B3:B19;0);1)

ExcelMinis4

3. Eine Senkrechte in ein Diagramm einfügen

In einem Liniendiagramm werden die monatlichen Umsätze dargestellt. Du möchtest beim jeweiligen Berichtsmonat dynamisch eine senkrechte Linie einfügen, als Suchhilfe sozusagen.

Eine senkrechte Linie kannst du durch ein Punktdiagramm erzeugen, das du zusätzlich ins Diagramm einfügst. Dafür benötigst du eine zweite Datenreihe.

Du hast diese beiden Datenreihen zur Verfügung, in B2 ist der gewünschte Berichtsmonat eingetragen:

ExcelMinis5

Erstelle zuerst das Punktdiagramm. Markiere dazu B18:C19 und erstelle über Einfügen / Diagramme / Punkte ein Diagramm „Punkte mit geraden Linien“. Klicke die Datenreihe an und rufe mit rechts „Daten auswählen“ auf. Klicke auf Datenreihe 1 und auf Bearbeiten. Fülle die Maske so aus:

ExcelMinis6

Klicke OK, nun hast du das Punktdiagramm als senkrechte Linie vorliegen.

ExcelMinis7

Anschließend erstellst du das Liniendiagramm mit den Plan- und Ist-Werten.

Klicke dazu in den Diagrammbereich, rufe mit der rechten Taste „Daten auswählen“ auf und füge eine die Datenreihe „Plan“ ein. Fülle die Dialogbox so aus:

ExcelMinis8

Füge jetzt analog die Datenreihe „Ist“ (bis zum Berichtsmonat) ein.

ExcelMinis11

Richte im Diagramm die x-Achsenbeschriftung noch auf die Monate 1 bis 12 ein.

Formatiere die Diagrammlinie evtl. noch hinsichtlich Stärke und Farbe.

Das fertige Diagramm mit einer Senkrechten sieht dann etwa so aus:

ExcelMinis10

Nimm bei Bedarf weitere Formatierungen vor, z.B. horizontale und vertikale Gitternetzlinien, Zeichnungsfläche, Diagrammfläche.

Je nach Eingabe des Berichtsmonats in B2 wandert die senkrechte Linie nach rechts oder links.

Die Planreihe zeigst du vollständig, die Istreihe bis zum aktuellen Berichtsmonat (Achtung: das erfolgt in diesem Beispiel nicht dynamisch, die ausgewählten Daten müssen jeweils angepasst werden).

Die Senkrechte zeigt dir, bis zu welchem Monat du gerade berichtest. Bei mehreren Datenreihen mit unterschiedlichen Bearbeitungsständen kann das hilfreich sein.

3 Kommentare zu „Excel-Minis 20171128“

  1. Cool, eine senkrechte Linie ins Diagramm einzufügen. Hatte bisher mit Einfugen-Formen-Linie, Linie manuell über Diagramm gelegt

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s