Formeln

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

Suchen & Ersetzen eines Multiplikationszeichens/Platzhalters (*)

Roman Allgemein, Formeln , , , , , , , , , , ,

Suchen & Ersetzen (CTRL + H) ist eine tolle Funktion, um innert kürzester Zeit beliebig viele Formeln und Werte anzupassen. Schauen wir uns fürs Verständnis diese eine Beispielformel an:

=A2*'C:\Dokumente\Währungen\[Währungskurse.xlsx]August'!$B$2

Aus einer externen Datei werden Währungskurse gezogen und bestimmte Werte mittels Multiplikation umgerechnet. Der Monat "August" kann nun simpel durch "September" ersetzt werden - so sind direkt alle Deine Bezüge auf den neuen Monat angepasst:

monatersetzen

Beachte: Ist nur eine einzelne Zelle selektiert, wird im gesamten Tabellenblatt gesucht und ersetzt! Selektiere also stets einen Bereich, um sicherzugehen, dass keine unerwünschten Angaben verändert werden!

Gehen wir nun jedoch davon aus, dass Du Multiplikationszeichen in Deiner Formel verwendest und auch diese ersetzen möchtest. Als Beispiel: Du multiplizierst alle Deine Werte mit 1'000 (*1000) und möchtest dies fortan nicht länger tun. Das Sternchen (*) wird dient auch als allgemeiner Platzhalter und der vorangehende Teil Deiner Formeln würde deshalb ebenfalls ersetzt werden. Excel benötigt eine exakte Eingabe - dass wir eben auch das Sonderzeichen (*) ersetzen wollen! Dies funktioniert, indem Du eine Tilde (~) vor Dein *1000 setzt. Ersetzen tun' wir diesen Wert durch nichts (den Bereich "Ersetzen durch:" einfach leer lassen):

suchenachtilde

Zellen mit Formeln schützen

Roman Allgemein, Formeln , , , , , , , ,

Nehmen wir an, Du möchtest ein Spesenformular generieren. Unbedingt vorhanden sein muss eine Kilometerabrechnung für Fahrten mit einem Privatwagen sowie unterschiedliche Mehrwertsteuersätze. Im besten Fall werden solche Berechnungen direkt im Formular hinterlegt, ohne dass der Benutzer viel überlegen und selbst kalkulieren muss - somit reduzierst Du potentielle Fehler im Vornherein. Ob man dadurch gewisse Checks vernachlässigt und etwas übersieht, das ist ein ganz anderes Thema. Wie dem auch sei: Da an allen Formeln nichts mehr geändert werden soll, wollen wir diese Zellen spezifisch schützen. Dazu gehst Du wie folgt vor:

  • Markiere zuerst Dein gesamtes Tabellenblatt ("CTRL + A" oder im linken oberen Rand neben Spalte A und Zeile 1) und gehe mittels Rechtsklick in die Optionen "Zellen formatieren". Unter dem Register "Schutz" kannst Du zunächst für sämtliche Zellen das "Gesperrt" entfernen:

ZellenFormatierenGesperrt

  • Als Nächstes stelle bitte sicher, dass nach wie vor alle Zellen markiert sind. Gehe dann mittels "CTRL + G" in den "Gehe zu ..."-Dialog und dort auf "Inhalte..." (alternativ kannst Du mit der Maus via "Start" → "Suchen und Auswählen" → "Inhalte auswählen" dorthin gelangen). Wähle die Option "Formeln" aus und klicke auf OK. Nun sind in Deinem gesamten Tabellenblatt alle Zellen mit Formeln selektiert worden.

InhalteFormeln

  • Gehe wie in Schritt 1 erneut in die Zellenformatierung (das geht übrigens auch mit der Tastenkombination "CTRL + 1") und setze den Haken bei "Gesperrt" für diese Zellen im Register "Schutz" erneut.
  • Daraufhin aktivierest Du den Blattschutz im Menüband unter "Überprüfen" → "Blattschutz aktivieren" und entfernst den Haken bei "Gesperrte Zellen auswählen":

BlattSchutzGesperrteZellen

Nach diesen wenigen Schritten können generell die Zellen mit Formeln nicht mehr bearbeitet und selektiert werden.

Verweis (SVERWEIS) mit mehreren Suchkriterien erstellen

Roman Formeln , , , , , , , , , , , ,

Wir hatten schon einmal über den "SVERWEIS" nach links, respektive den "WVERWEIS" nach oben gesprochen. Den Beitrag dazu findest Du unter diesem Link. Mittels den dort verwendeten Formeln können wir auch einen Verweis mit mehreren Suchkriterien erstellen. Das eignet sich insbesondere bei dynamischen Berichten. Schauen wir uns ein Beispiel und die zu verwendende Tabelle an:

WechselkursTabelle1

Unser Verweis soll uns in der Zelle "E3" einen Kurswert ausgeben, der zwei Kriterien entspricht: Der Währung und dem Monat; Entsprechend dieser beiden Selektionen soll in der Matrix unterhalb der Wechselkurs ermittelt werden. Die beispielhaft verwendeten Kurse zum Schweizer Franken stammen aus einem originellen Tool von www.oanda.com.

Als Grundgerüst verwenden wir die eingangs verlinkte Formel "=VERWEIS" und "=VERGLEICH" in Kombination. Die komplette Formel, um unseren oben definierten Wechselkurs ausgeben zu können, lautet:

=INDEX(C6:G8;VERGLEICH(C3;B6:B8;0);VERGLEICH(D3;C5:G5;0))

... und nun zur Formel selbst und deren Bestandteile: Die erste Angabe (C6:G8) ist unserer Bereich mit den Wechselkursen - dieser beinhaltet alle möglichen Resultate unserer Suche. Das heisst, ein Wert aus diesem Bereich wird schlussendlich von unserer Formel in "E3" ausgegeben. Die Index-Formel verlangt daraufhin nach der "Zeile", also in welcher Zeile des Bereichs/Arrays sich der Wert befindet. Diese müssen wir allerdings erst noch ermitteln - es könnte ja einer von drei Währungen sein. Deshalb stellen wir einen "VERGLEICH" von "C3" gegenüber den Möglichkeiten innerhalb von "B6:B8" her. Ein zweiter Vergleich wird für die Monate durchgeführt, womit die korrekte Spalte ermittelt wird. Der Suchablauf verläuft wie folgt:

WechselkursTabelleVorgehen1

Strukturieren wir unsere Basis-Tabelle etwas um, kommen wir mit der obigen Formel leider nicht ganz ans Ziel. Dies ist die neue Ausgangslage:

WechselkursTabelle2

Unsere beiden Suchkriterien bleiben zwar dieselben, sie stehen nun aber nicht mehr in Spalte und Zeile, sondern nur noch in nebeneinanderliegenden Spalten. Unser Verweis muss deshalb so abgeändert werden, dass zuerst nach der korrekten Währung und dann zudem nach dem entsprechenden Monat gesucht wird. In der VERGLEICH-Formel werden dann die einzelnen Suchkriterien mittels Kaufmännischem-Und (&) aneinandergereiht ("C3&D3"). Dasselbe wird daraufhin für jeden entsprechenden Suchbereich gemacht ("B6:B20&C6:C20"). Die Reihenfolge der Suchkriterien spielt keine Rolle, sie muss nicht zwingend der Sequenz unserer obigen Definition entsprechen. Dies ist die komplette Formel in Zelle "E3":

{=INDEX(D6:D20;VERGLEICH(C3&D3;B6:B20&C6:C20;0))}

Fällt Dir etwas auf? Genau! Die geschweiften Klammern weisen auf eine sogenannte Matrixformel hin. Speziell daran ist, dass diese nicht selbst eingeben werden, sondern die Formel (ohne geschweifgten Klammern) wird mittels CTRL+Shift+Enter bestätigt! Der Suchvorgang in Matrixformel entspricht dem Iterationsverfahren - es wird zuerst das erste Kriterium (USD) überprüft, dann wird geschaut, ob auf der dazugehörigen Zeile auch der korrekte Monat steht. Falls nein, wird weiter in der ersten Suchspalte nach "USD" gesucht, usw. Das nachfolgende Bild veranschaulicht diesen Ablauf:

WechselkursTabelleVorgehen2

Je nach Datenstruktur kann also eine unterschiedliche Formel zum Zuge kommen - es lohnt sich auf jeden Fall, beide dieser Arten zu verstehen. (P.S.: Zum selben Ergebnis wie mit der vorgestellten Matrixformel würdest Du in diesem Fall auch mittels "=SUMMEWENNS" gelangen. Die komplette Formel lautet: "=SUMMEWENNS(D6:D20;B6:B20;C3;C6:C20;D3)")