Formeln

In dieser Themengruppe sind Anleitungen zur Anwendung von einzelnen Formeln eingeordnet.

Mittels Formel den Tabellenblatt-Name ausgeben

Roman Formeln , , , , , , ,

Mit einer Zusammensetzung von diversen Formeln kann der Name des aktuellen Tabellenblatts ausgegeben werden. Die komplette Formel lautet:

=TEIL(ZELLE("dateiname");SUCHEN("]";ZELLE("dateiname"))+1;100)

Das Ergebnis dieser Formel ist bei meiner Datei: "MeinTabellenblatt". [Beachte, dass dies nur bei bereits abgespeicherten Dateien funktioniert.]

Die drei verschiedenen verwendeten Formeln bewirken folgendes; beginnen wir mit "=ZELLE":

In dieser Funktion wird zuerst nach dem "Infotyp" gefragt - wir wollen, dass der Dateiname der aktiven Zelle ausgegeben wird. Das Ergebnis: C:\....\...\[MeineDatei.xlsx]MeinTabellenblatt. Nicht zwingend ist eine "Referenz" nach dem Infotyp als zweites Argument innerhalb der Formel. Man könnte da auf eine andere Zelle verweisen; auch auf ein anderes Tabellenblatt - und könnte so schlussendlich mit leichtem Modifizieren der obigen Formel den Namen eines anderen Tabellenblattes ausgeben lassen.

Die Formel "=SUCHEN" haben wir im Beitrag "Textfragmente aus einer Zelle ziehen" genauer betrachtet: Es wird damit die Position des Suchtextes (in unserem Beispiel die eckige Klammer "]" nach dem Dateinamen) ausgemacht, ab welcher (+1) die "=TEIL"-Funktion einen entsprechenden Text ausgibt. Die 100 zum Schluss der obigen Formel ist lediglich eingebaut, dass bestimmt genügend Zeichen ausgegeben werden (also ein Tabellenname mit bis zu 100 Zeichen). Diese hohe Zahl ist unkritisch, da keine ungewünschten Leerzeichen an den Text angefügt werden.

Summe nach Zellfarbe bilden

Roman Allgemein, Formeln, VBA (Makros) , , , , ,

Eine SUMMEWENN-Funktion berücksichtigt zwar Kriterien und bildet bei Erfüllen dieser die Summe der jeweiligen Werte, beispielsweise die Zellfarbe kann allerdings nicht ohne weiteres als Kriterium erkannt und berücksichtigt werden. Dazu benötigt es einen zusätzlichen Schritt, welchen wir in diesem Beitrag betrachten:

Im Menüband gehst Du unter "Formeln" auf "Name definieren". Gib dort als Name "Farbe" ein - dies ist später der Name einer Formel, welche wir in eine Hilfsspalte eingeben werden (Du kannst daraufhin "=Farbe" in eine Zelle eingeben). Bei "Bezieht sich auf" gibst Du folgendes ein:

=ZELLE.ZUORDNEN(63;INDIREKT("ZS(-1)";))

Zusammengefasst sieht Dein definierter Name wie folgt aus:

ZellfarbeName

[Der Bezug ZS(-1) ist ein Referenzbezug: Dieser bedeutet, dass Du auf die selbe Zeile (Z), allerdings auf eine Spalte (S) nach links (-1) zugreifst.]

Wenn Du nun eine Spalte rechts Deiner farbigen Zelle "=Farbe" eingibst, wirst Du eine Zahl erhalten - diese bezieht sich auf die Zellfarbe. Wenn Du die Zellfarbe änderst, wird sich auch das Resultat in der Zelle nebenan verändern, sobald Du die "=Farbe"-Formel aktualisierst (oder F9 auf der Tastatur zur Kalkulation des gesamten Tabellenblatts drückst). Nun kannst Du die bekannte SUMMEWENN-Formel verwenden und alle Werte summieren, welche in der Spalte nebenan den entsprechenden Farbcode aufweisen. In diesem Beispiel erstelle ich die Summe aller orangen Zellen (mit dem Farbcode 44):

SummewennZellfarbe

(10 + 2 + 3 + 5 + 10 = 30)

Wichtig zu beachten ist, dass Du Deine Datei daraufhin als .xlsm (Makro-Datei) abspeichern musst, damit die "=Farbe"-Funktion auch zu einem späteren Zeitpunkt wieder funktionsfähig ist.

Ranking erstellen mittels KGRÖSSTE

Roman Formeln , , , ,

Angenommen wir möchten ein Ranking der umsatzstärksten Produkte erstellen - beispielsweise eine Top 5. Ich stelle Dir hier eine Formel vor, mit der Du die höchsten fünf Umsatzwerte extrem schnell ermitteln kannst. Den grössten Wert – das Maximum – zu bestimmen, ist kein Problem. Dazu eignet sich theoretisch die allbekannte Formel "MAX". Doch wie finden wir nun heraus, welches die nächstbesten Umsätze sind? Die Formel "KGRÖSSTE" liefert dieses Resultat. Bei der Eingabe werden wir nach zwei Dingen gefragt: Dem Datenbereich (Array) und einem "k". Das "k" steht für den k-grössten Wert aus dem gewählten Array. Eine "2" gibt demzufolge den zweithöchsten Wert aus, eine "3" den drittgrössten, usw. – und genau damit können wir unser Ranking erstellen!

Möchtest Du auch die k-kleinsten Werte ermitteln? Die Formel dazu lautet "KKLEINSTE".

Falls Du noch einen Schritt weitergehen möchtest, kannst Du versuchen, neben dem Umsatzwert auch den Produktnamen herauszufinden. Mittels SVERWEIS oder SVERWEIS nach links (siehe diesen Beitrag hier) kannst Du Deine Formel "KGRÖSSTE" als Suchwert verwenden und dann aus Deiner Umsatzliste den Produktnamen ausgeben lassen.