Monat: Juni 2015

Dynamische Titel (Formel "TEXT")

Roman Formeln , , , ,

Im kürzlich veröffentlichten Beitrag Dynamische Ergebnisse (Formel "Teilergebnis") wurde gezeigt, wie auf angewandte Filter reagiert werden kann, sodass stets das korrekte Ergebnis geliefert wird. Die herkömmliche SUMME-Formel berücksichtigt den aktiven Filter nicht, wohingegen sich TEILERGEBNIS nur auf die noch angezeigten Daten bezieht.

Wir verwenden hier wiederum die Verkäufe, wie in nachfolgendem Bild ersichtlich:

Teilergebnis1

Ziel ist es nun, oberhalb der Überschrift (Datum; Kundennummer; Umsatz; Absatz) einen Titel einzufügen. Dieser soll folgendermassen lauten: "Verkäufe von 10.01.2014 bis 11.02.2014". Desweiteren soll der Titel dynamisch sein, damit direkt auf den aktiven Filter reagiert und stets der korrekte Zeitraum angegeben wird.

Text und Formel verketten

In diesem Abschnitt beziehe ich mich auf bereits im Blog Inhalte aus Zellen miteinander verketten gezeigte Inhalte, wo wir sowohl Text und Formeln/Bezüge miteinander verknüpft haben. Nach dem Einfügen einer neuen Zeile geben wir vorerst in Zelle A1 folgendes ein:

="Verkäufe von "

Um nun das tiefste Datum zu ermitteln, verwenden wir die Formel "TEILERGEBNIS", mit der Funktion "MIN" (Minimum). In der Zelle A1 haben wir demzufolge:

="Verkäufe von "&TEILERGEBNIS(5;A3:A9)

Wichtig ist hier das Kaufmännische-Und-Zeichen (&), welches uns ermöglicht, sowohl Text ("Verkäufe von ") und dann eben auch Bezüge oder Formeln (eingeläutet mit dem &-Zeichen) miteinander zu kombinieren.

Nach dieser Eingabe siehst Du, dass der Titel noch nicht vollständig ist, wir erhalten als Resultat "Verkäufe von 41649". Du bist jedoch auf dem richtigen Weg - der 10. Januar 2014 ist einfach noch nicht korrekt formatiert worden! Es ist hier zu erwähnen, dass jedes Datum als Zahl im System hinterlegt ist, beginnend beim 1. Januar 1900 - probier es doch mal aus: Gib in einer beliebigen Zelle "01.01.1900" ein und formatiere diesen Wert in eine Zahl um - Du erhälst "1". Dies erlaubt es uns, mit Daten zu rechnen (beispielsweise Rechnungsdatum +30 = Fälligkeitsdatum). Aber zurück zum Thema: Wie kannst Du nun das Datum umformatieren? Hier kommt die Formel "TEXT" zum Zuge.

Die Formel "TEXT"

Mittels der Formel "TEXT" kann das Format eines Wertes beliebig angepasst werden. In unserem Falle für ein Datum bedeutet dies: Das Datum 10.01.2014 (oder die Zahl 41649) kann beliebig umformatiert werden. Die Formelsyntax von "TEXT" verlangt nach einem Wert [Bezug (Datum oder Zahl)] und nach dem gewünschten Format. Der Bezug ist in unserem Beispiel das Datum 10.01.2014. Als Textformat kannst Du nun z.B. “T. MMMM JJJ“ eingeben. Das Resultat: "10. Januar 2014". Auch die erfassten Punkte und Leerschläge wurden akzeptiert und berücksichtigt. Doch was bedeuten diese "T, M & J" im Detail?

T steht für Tag, M für Monat und J für Jahr. Ich liste hier auf, welche Formate möglich sind:

T = Tag ohne vorhergehende Null (1., 2., 10., 20. Januar)

TT = Tag mit vorhergehender Null (01., 02., 10., 20. Januar)

TTT = Tag ausgeschrieben, abgekürzt (Mo, Di, Mi, Do, Fr, Sa, So)

TTTT = Tag ausgeschrieben (Montag, Dienstag, Mittwoch, …)

 

M = Monat ohne vorhergehende Null (1., 2., 10., 12. 2014)

MM = Monat mit vorhergehender Null (01., 02., 10., 12. 2014)

MMM = Monat ausgeschrieben, abgekürzt (Jan, Feb, Mrz, …)

MMMM = Monat ausgeschrieben (Januar, Februar, März, …)

 

J oder JJ = Jahr, abgekürzt (14, 15, 16)

JJJ oder JJJJ = Jahr, nicht abgekürzt (2014, 2015, 2016)

Bezogen auf unseren dynamischen Titel ist nun also die Formel "TEILERGEBNIS(...)" mit obiger Formel zu kombinieren. Das Teilergebnis entspricht dem Wert der TEXT-Formel, für welches das Format ("TT.MM.JJJ") bestimmt wird. In Zelle A1 steht demnach:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"TT.MM.JJJ")

Nun haben wir - auf den Filter reagierend - stets den tiefsten Wert/das tiefste Datum ermittelt. Um "Verkäufe von ... bis ..." zu erlangen, wäre die Formel analog oben zu erweitern. Wir gehen wiederum Schritt für Schritt vor und erweitern den Titel erstmal um den Textteil " bis ". Zu beachten ist, dass die Formel mit einem &-Zeichen und der Text mit Gänsefüsschen abzugrenzen ist. In Zelle A1 steht danach:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"T.MM.JJJ")&" bis "

Der letzte Schritt ist nun, noch das Maximum mittels Teilergebnis zu ermitteln. Am einfachsten kopierst Du den Teil "&TEXT(TEILERGEBNIS [...] "T.MM.JJJ")" und setzt diesen ans Ende der Formel in der Zelle A1. Die Funktion für MAX in der Formel TEILERGEBNIS ist die Nummer 4, für MIN ist es die Nummer 5. Dies ist noch anzupassen, danach hast Du einen dynamischen Titel gesetzt, der auf den Filter reagiert und Dir stets den korrekten Zeitraum angibt. Die fertigte Formel in A1 lautet:

="Verkäufe von "&TEXT(TEILERGEBNIS(5;A3:A9);"TT.MM.JJJ")&" bis "&TEXT(TEILERGEBNIS(4;A3:A9);"TT.MM.JJJ")

Du könntest nun den Datumstyp beliebig anpassen und beispielsweise den Wochentag integrieren, indem das Format in "TTTT, TT. MMMM JJJ" geändert wird. Das Resultat: Freitag, 10. Januar 2014. Daraus wird ersichtlich: Du kannst mit der Formel "TEXT" jederzeit - und schnell - den Wochentag ermitteln, indem Du als Format einfach "TTTT" wählst.

Grundsätzlich siehst Du, habe ich die Formel Schritt für Schritt aufgebaut - es wird nur unnötig kompliziert, wenn man mehrere Angaben miteinander als Formel anzugeben versucht. Ich werde in einem späteren Blog zeigen, wie ich beim Erstellen von ganzen Berichten jeweils vorgehe. Nun wünsche ich Dir jedoch erstmal viel Erfolg beim Formatieren von Daten und dem Erstellen von dynamischen Titeln und Berichten. Hinterlasse mir doch bei Fragen und Anregungen ungeniert einen Kommentar.

Dynamische Ergebnisse (Formel "Teilergebnis")

Roman Formeln , , ,

Deine Excel-Tabellen müssen dynamisch sein. Was bedeutet das? Und weshalb und vor allem wie kann man das bewerkstelligen?

Hast Du schon einmal einen Bericht oder Daten erhalten, darauf einen Filter angewandt und das angezeigte Ergebnis konnte einfach nicht stimmen (sprich es hat sich nicht dem Filter entsprechend angepasst)? Schlimmer sind jedoch Fälle, bei denen man nicht einmal bemerkt, dass von einer falschen Datenbasis ausgegangen wird und man deshalb das angezeigte Total weiterverwendet. In diesem Beitrag schauen wir an, wie Du Deine Ergebnisse dynamisch gestalten kannst - es soll also stets von den angezeigten Daten ausgegangen werden. Dazu verwenden wir die Formel "TEILERGEBNIS".

Die Formel TEILERGEBNIS im Vergleich

Damit Du siehst, was ich genau mit meiner Einleitung meinte, schauen wir uns nachfolgende Grafik an.

Teilergebnis1

Es sind Verkaufsdaten zu den jeweiligen Tagen erfasst. Unterhalb wird das Total mittels der normalen Formel "SUMME" ermittelt. Wendet nun der Empfänger dieser Daten den Filter an und lässt sich beispielsweise nur den Kunden "30001" anzeigen, wird die Formel nach wie vor dasselbe Ergebnis wie in der oberen Grafik darstellen. Die "SUMME"-Formel erkennt nicht, dass ein Filter gesetzt worden ist und hält eisern am angegebenen Bereich (C2:C8) fest.

Teilergebnis2

Verwendest Du hingegen die intelligentere Formel "TEILERGEBNIS", wird der Filter ebenfalls berücksichtigt. Die Formelsyntax beinhaltet eine Funktion und einen Bezug. Die Funktion ist eine Zahl, stellvertretend für die jeweilige Formel (ANZAHL, MAX, MIN etc.) - SUMME hat die Zahl 9. Sobald Du "=TEILERGEBNIS(" eingibst, werden die verfügbaren Formeln angezeigt. Desweiteren ist der Bereich anzugeben, welcher gleich ist wie bei der herkömmlichen SUMME-Formel. Für die Spalte "Umsatz" (C) lautet die Formel demnach:

=TEILERGEBNIS(9;C2:C8)

Teilergebnis3

Nun kannst Du oder der Empfänger Deiner Daten willkürlich filtern - und es wird stets die richtige Summe gezogen werden! Solche Absicherungen sind wichtig, denn es stellt die Richtigkeit der Angaben sicher und stärkt das Vertrauen des Empfängers der Daten!

Dynamische Titel und Weiterführendes

Im Beitrag Dynamische Titel (Formel "TEXT") ist die Formel "TEXT" erläutert und es wird Dir gezeigt, wie Du dynamische Titel setzen kannst. Da wird - auf den Filter reagierend - ein Titel definiert, sodass jeweils das Von- und Bis-Datum stets korrekt angezeigt wird.

Inhalte aus Zellen miteinander verketten

Roman Formeln , , , , ,

In einem vorherigen Blog (Textfragmente aus einer Zelle ziehen) habe ich Dir gezeigt, wie man mittels "Text in Spalten" oder Formeln wie "LINKS" oder "RECHTS" einzelne Teile aus einer Zelle holen kann. In diesem Beitrag zeige ich Dir zwei Möglichkeiten, wie Du separate Felder miteinander verketten und kombinieren kannst.

Die Formel "VERKETTEN"

Verwenden wir wieder die gleichen Artikeldaten wie im oben erwähnten Beitrag und nehmen nun die Lieferantenperspektive ein. Das heisst, unser Kunde möchte die Datensätze übermittelt haben - und zwar sollen sämtliche Artikelangaben durch ein Minuszeichen getrennt in einem Feld stehen.

Unsere Rohdaten sehen demzufolge so aus:

Verketten

In der Zelle D1 fügen wir nun die Formel "=VERKETTEN" ein. Sobald wir die Klammer öffnen, sehen wir die Formelsyntax. Die einzelnen Textfragmente sind durch ein Semikolon (;) getrennt. Das erkennst Du in der Formelsyntax, wo "Text1;[Text2];..." angegeben ist. Die korrekte Formel, um die einzelnen Zellen miteinander zu verknüpfen (und die Inhalte mit einem Minuszeichen abzugrenzen) lautet:

=VERKETTEN(A1;"-";B1;"-";C1)

Wichtig zu beachten ist, dass wir das Trennzeichen (Minuszeichen) manuell angeben müssen. Dabei handelt es sich um einen Text - und Text ist innerhalb von Formeln jeweils mit "Gänsefüsschen" ("") einzugrenzen. Deshalb sind die Minuszeichen oben zwischen Anführungs- und Schlusszeichen aufgeführt.

Bezüge und Text in einer Formel kombiniert

Es besteht auch die Möglichkeit, ohne der Formel "VERKETTEN" ans Ziel zu gelangen. Ich zeige Dir hier, wie Du in Formeln Bezüge und Texte miteinander kombinieren kannst: Im oberen Abschnitt habe ich erwähnt, dass Texte in Formeln mit Anführungs- und Schlusszeichen einzugrenzen sind. Formeln und Bezüge sind mit dem Kaufmännischen-Und-Zeichen (&) anzugeben. Geben wir also folgende Formeln ein:

=A1&"-"&B1&"-"&C1

Das erhaltene Resultat ist dasselbe wie unter der Verwendung von "VERKETTEN". Wir beginnen mit Text1 (Feld A2) und geben dann ein &-Zeichen an, um den Bezug zu dieser Zelle zu beenden. Mit den "Gänsefüsschen" signalisieren wir einen Text (das Minuszeichen), ehe wir wiederum mit dem Kaufmännischen-Und den nächsten Bezug (Zelle B2) angeben usw. ...

Abschliessend könnte man noch sämtliche durch Formeln verkettete/kombinierte Zellen markieren und kopieren. Die Anforderung unseres Kunden war ja, sämtliche Artikeldaten in je einer Zelle aufzuführen. Nach dem Kopieren kannst Du einfach über den gleichen Bereich via Rechtsklick → "Inhalte einfügen..." die Werte einfügen (Werteeinfuegen) - so werden die Formeln direkt entfernt und mit Text überschrieben. Nun kannst Du noch die Spalten A bis C löschen und die Stammdaten an Deinen Kunden übermitteln.

Wie erwähnt: Das jeweils erhaltene Resultat ist dasselbe und es gibt hier kein "Richtig" oder "Falsch", beide illustrierten Varianten führen ans Ziel - Du kannst selbst entscheiden, welchen Weg Du sinnvoller findest.