Formeln

Bezüge und Formeln in einem Textfeld

Roman Allgemein, Diagramme , , , , , , ,

In Diagrammen kann man beispielsweise mit der "Datenbeschriftung" Werte anzeigen lassen. Unter Umständen möchte man jedoch zusätzliche Informationen präsentieren, wie beispielsweise die prozentuale Abweichung zwischen zwei Säulen. In einem solchen Fall eignen sich meist Textfelder, da diese unkompliziert eingefügt werden können. Falls sich jedoch die Datenbasis zwischenzeitlich ändert, muss man sich über diese manuell eingegebenen Texte stets bewusst sein und diese ebenfalls anpassen. In diesem Beitrag wollen wir anschauen, wie Du Formeln oder Bezüge in ein Textfeld eingeben kannst, damit zumindest der Wert aus Deiner Datenbasis direkt übernommen wird. Allfällige Verschiebungen des Textfeldes (falls sich die Säulen auch in der Höhe verändern) müsstest Du nach wie vor von Hand vornehmen.

Um eine Formel oder einen Bezug zu erstellen, kannst Du ja einfach "=A1" in eine Zelle schreiben. Wenn Du das in einem Textfeld machst, hast Du weniger Erfolg. Der Trick ist, das Textfeld zu aktivieren und dann in der Eingabeleiste "=A1" einzugeben – und schon hast Du einen Bezug/eine Formel erstellt! Du kannst dies auch andersrum ausprobieren: Eingetragene Texte in Deinem Textfeld werden nicht in der Eingabeleiste oben angezeigt.

 

Du kannst demnach ein Textfeld in Dein Diagramm einbetten, neben Deiner Datenbasis die entsprechenden Berechnungen zu Abweichungen oder Kommentare etc. vornehmen und gemäss dem vorherigen Abschnitt Deinen Bezug/Deine Formel erstellen. Als einfaches Beispiel findest Du in der Abbildung unten eine fiktive Zahlenbasis, die Veränderung der Säulen in % sowie das entsprechende Diagramm dazu. Das Textfeld hebt die ermittelte prozentuale Veränderung hervor. Die Pfeile habe ich wie auch die Textfelder via "Einfügen" → "Formen" erstellt und entsprechend formatiert.

 DiagrammFormelinTextfeld

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.