Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

Ich erinnere mich noch an den Moment, als ich erkannte, dass ich seit sieben Jahren Excel-Tabellen falsch gemacht hatte. Es war 2019, und ich saß mit meinem Team in einem Besprechungsraum in einem mittelständischen Logistikunternehmen und sah zu, wie unsere CFO manuell Daten zwischen drei verschiedenen Excel-Dateien kopierte und einfügte, um unseren Quartalsbericht zu erstellen. Der Prozess dauerte vier Stunden. Vier Stunden ihrer $150/Stunde Zeit, jedes einzelne Quartal, weil sich niemand die Mühe gemacht hatte, VLOOKUP zu lernen.

Dieser Tag hat alles für mich verändert. Ich bin Sarah Chen, und ich habe die letzten 11 Jahre als Beraterin für Finanzoperationen verbracht und mit über 200 Unternehmen zusammengearbeitet, um deren Daten-Workflows zu optimieren. Ich habe gesehen, wie Unternehmen buchstäblich Tausende von Stunden mit Aufgaben in Excel-Tabellen verschwenden, die mit nur einer Handvoll von Formeln automatisiert werden könnten. Die Ironie? Die meisten Menschen denken, sie müssten Hunderte von Funktionen beherrschen, um „gut in Excel“ zu sein. Das müssen sie nicht. Nach meinen Erfahrungen decken etwa 20 Formeln etwa 95% der realen Geschäftsszenarien ab.

Dies ist kein umfassendes Nachschlagewerk für jede jemals erstellte Excel-Funktion. Dies ist das bewährte Arsenal, das ich genutzt habe, um meinen Kunden schätzungsweise über 12.000 Stunden manueller Arbeit zu ersparen. Dies sind die Formeln, die tatsächlich wichtig sind, wenn Sie versuchen, die Bücher abzuschließen, Verkaufsdaten zu analysieren oder herauszufinden, warum Ihre Bestandszahlen nicht übereinstimmen.

Die Grundlage: Grundrechenarten, die alles antreiben

Bevor wir fancy werden, lassen Sie uns über die drei Formeln sprechen, die das Rückgrat von buchstäblich jedem Finanzmodell bilden, das ich jemals erstellt habe. Diese scheinen zwar offensichtlich, aber ich habe beobachtet, wie leitende Analysten sie auf eine Weise vermasseln, die Unternehmen echtes Geld kostet.

SUMME ist das Arbeitstier. Die Syntax ist kinderleicht: =SUMME(A1:A10) summiert alles in diesem Bereich. Aber hier ist, was die meisten Leute nicht wissen: Sie können SUMME mit mehreren nicht zusammenhängenden Bereichen verwenden. =SUMME(A1:A10, C1:C10, E5) funktioniert einwandfrei und ist viel sauberer, als individuelle Zellreferenzen aufzuschreiben. Ich habe einmal ein Finanzmodell geprüft, in dem jemand =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 geschrieben hatte, anstatt SUMME zu verwenden. Als sie eine Zeile einfügen mussten, war die Formel kaputt. Seien Sie nicht diese Person.

DER DURCHSCHNITT ist ebenso unkompliziert, aber überraschend leistungsstark. =DURCHSCHNITT(B2:B50) gibt Ihnen den Durchschnitt Ihres Datensatzes. Die zentrale Erkenntnis hier ist zu verstehen, wann man DURCHSCHNITT versus DURCHSCHNITTSWENN (das wir später behandeln werden) verwenden sollte. In meiner Beratertätigkeit habe ich festgestellt, dass etwa 60% der Male, wenn Menschen DURCHSCHNITT verwenden, tatsächlich ein bedingter Durchschnitt benötigt wird. Wenn Sie den durchschnittlichen Umsatz berechnen, aber Nullen oder Ausreißer ausschließen möchten, wird der einfache DURCHSCHNITT Sie in die Irre führen.

ANZAHL und ANZAHL2 sind Zwillinge mit unterschiedlichen Persönlichkeiten. ANZAHL zählt nur Zellen mit Zahlen, während ANZAHL2 jede nicht leere Zelle zählt. Diese Unterscheidung ist wichtiger, als Sie denken. Wenn ich die Datenqualität überprüfe, verwende ich =ANZAHL2(A:A)-ANZAHL(A:A), um sofort herauszufinden, wie viele Zellen in Spalte A Text anstelle von Zahlen enthalten. Wenn diese Zahl höher als erwartet ist, weiß ich, dass es ein Problem bei der Dateneingabe gibt. Diese einfache Prüfung hat meinen Kunden Tausende von Dollar an Buchhaltungsfehlern eingebracht.

Suchen-Funktionen: Die s

Wenn ich nur drei Formeln beibringen könnte, wären zwei davon Suchfunktionen. Diese sind der Unterschied zwischen dem manuellen Abgleich von Daten für 30 Minuten und dem Schreiben einer Formel, die dies sofort in 30 Sekunden erledigt.

„Aus meiner Erfahrung mit über 200 Unternehmen decken 20 Formeln etwa 95% der realen Geschäftsszenarien ab. Die meisten Menschen verschwenden Zeit damit, Hunderte von Funktionen zu meistern, die sie niemals verwenden werden.“

VLOOKUP ist der Klassiker, und trotz neuerer Alternativen ist es immer noch unglaublich nützlich. Die Syntax ist =VLOOKUP(suchwert, tabelle_array, spalten_index_nummer, [bereich_suchen]). Hier ist ein reales Beispiel von letztem Monat: Ich hatte einen Kunden mit 5.000 Kundendatensätzen in einem Blatt und 5.000 Transaktionsdatensätzen in einem anderen. Sie mussten Kundennamen mit Transaktions-IDs abgleichen. =VLOOKUP(A2, Customers!A:D, 3, FALSE) tat in drei Sekunden, was manuell Stunden gedauert hätte.

Das Kritische, was jeder falsch macht: das vierte Argument. FALSE bedeutet exakte Übereinstimmung, TRUE bedeutet ungefähre Übereinstimmung. Für 99% der Geschäftsanwendungsfälle möchten Sie FALSE. Ich habe ganze Finanzberichte gesehen, die beschädigt wurden, weil jemand es als TRUE belassen hat (oder es weggelassen hat, was standardmäßig TRUE ist) und ungefähre Übereinstimmungen anstelle von exakten erhalten hat.

XLOOKUP ist der moderne Ersatz und ehrlich gesagt, es ist in fast jeder Hinsicht besser. =XLOOKUP(suchwert, such_array, rückgabe_array, [wenn_nicht_gefunden], [vergleichsmodus], [suchmodus]) klingt kompliziert, ist in der Praxis aber sauberer. Das Killer-Feature? Es kann nach links suchen, was VLOOKUP nicht kann. Ich habe kürzlich das gesamte Berichtssystem eines Kunden von VLOOKUP auf XLOOKUP migriert und dessen Formel-Fehler um etwa 40% reduziert, da XLOOKUP fehlende Daten eleganter behandelt.

INDEX-VERGLEICH ist die Wahl des Power-Users. Es handelt sich tatsächlich um zwei kombinierte Formeln: =INDEX(rückgabe_bereich, VERGLEICH(suchwert, suchbereich, 0)). Diese Kombination ist flexibler als VLOOKUP und schneller bei großen Datensätzen. Wenn ich mit Tabellen über 50.000 Zeilen arbeite, schneidet INDEX-VERGLEICH merklich besser ab. Die Lernkurve ist steiler, aber sobald Sie es verstehen, werden Sie nie wieder zurückgehen. Ich verwende es für komplexe Finanzmodelle, in denen ich Werte basierend auf zwei Kriterien gleichzeitig nachschlagen muss.

Bedingte Logik: Lassen Sie Ihre Tabellen denken

Hier verwandeln sich Tabellen von Rechenmaschinen in Entscheidungswerkzeuge. Bedingte Formeln ermöglichen es Ihnen, Logik in Ihre Daten einzubauen, und sie sind absolut entscheidend für jede Art von automatisiertem Reporting.

WENN ist die Einstiegsdroge zur Automatisierung von Tabellen. =WENN(logischer_Test, wert_wenn_wahr, wert_wenn_falsch) ermöglicht es Ihnen, verzweigte Logik zu erstellen. Ein einfaches Beispiel: =WENN(B2>1000, "Hoher Wert", "Standard") kategorisiert Kunden basierend auf dem Kaufbetrag. Aber die wahre Kraft zeigt sich durch das Verschachteln von WENNs. Ich habe Provisionsrechner mit fünf verschachtelten WENN-Anweisungen erstellt, die automatisch die Boni von Vertriebsmitarbeitern basierend auf mehreren Kriterien bestimmen. Der Schlüssel ist, sie leserlich zu halten – verwenden Sie die richtige Einrückung und Kommentare.

WENND ist die sauberere Alternative zu verschachtelten WENNs, die in neueren Tabellenversionen verfügbar ist. Anstatt =WENN(A1>90, "A", WENN(A1>80, "B", WENN(A1>70, "C", "F"))), schreiben Sie =WENND(A1>90, "A", A1>80, "B", A1>70, "C", WAHR, "F"). Es ist lesbarer und weniger anfällig für Klammerfehler. Ich bin vor etwa drei Jahren auf WENND umgestiegen und meine Zeit für die Fehlersuche in Formeln hat sich halbiert.

SUMMEWENN und SUMMEWENNS sind kraftvolle Bedingungsfunktionen zum Summieren. =SUMMEWENN(bereich, kriterium, [summenbereich]) summiert Werte, die einer Bedingung entsprechen. =SUMMEWENNS erweitert dies auf mehrere Bedingungen. Reales Beispiel: Ich hatte einen Einzelhandelskunden, der die gesamten Umsätze nach Region und Produktkategorie berechnen musste. =SUMMEWENNS(Umsätze!C:C, Umsätze!A:A, "West", Umsätze!B:B, "Elektronik") gab ihnen genau das. Bevor sie SUMMEWENNS lernten, verwendeten sie Pivot-Tabellen für alles, was übertrieben und langsamer zu aktualisieren war.

ANZAHLWENN und ANZAHLWENNS funktionieren auf die gleiche Weise, zählen aber statt zu summieren. Ich verwende diese ständig für Datenqualitätsprüfungen. =ANZAHLWENN(A:A, "Fehler") sagt mir, wie viele Zellen in Spalte A das Wort "Fehler" enthalten. =ANZAHLWENNS(A:A, ">100", B:B, "<50") zählt Zeilen, in denen Spalte A über 100 UND Spalte B unter 50 ist. Diese Formeln haben mir unzählige Stunden manueller Datenvalidierung erspart.