The 10 Spreadsheet Formulas That Handle 90% of Real Work \u2014 CSV-X.com

Ich erinnere mich noch an den Tag, an dem mir klar wurde, dass ich seit sieben Jahren mit Tabellenkalkulationen falsch umgehe. Es war 2019, und ich saß in einem engen Konferenzraum bei einem mittelgroßen Logistikunternehmen in Cleveland und beobachtete, wie deren Betriebsleiter manuell Daten zwischen drei verschiedenen Excel-Dateien für das vermutlich dreißigste Mal in diesem Monat kopierte und einfügte. Jeder Einfügevorgang dauerte etwa 90 Sekunden. Er tat dies ungefähr 200 Mal pro Monat. Das sind 300 Stunden pro Jahr — fast zwei volle Monate Arbeit — für eine Aufgabe, die mit einer einzigen Formel automatisiert werden könnte.

Mein Name ist Marcus Chen, und ich habe die letzten 14 Jahre als Business Systems Analyst gearbeitet und mit allen gearbeitet, von Fortune 500-Unternehmen bis hin zu zähen Startups mit fünf Mitarbeitern. Ich habe in meiner Karriere über 3.000 Tabellenkalkulationen überprüft, und ich kann Ihnen mit absoluter Sicherheit sagen, dass die meisten Menschen etwa 5% der verfügbaren Möglichkeiten nutzen. Aber: Sie müssen nicht alle 400+ Funktionen in Excel oder Google Sheets beherrschen. Sie müssen genau zehn beherrschen.

Diese zehn Formeln bewältigen etwa 90% der echten Geschäftstätigkeiten. Ich spreche nicht von exotischer Finanzmodellierung oder statistischer Analyse. Ich spreche über den täglichen Betrieb: Bestandsverfolgung, Berechnung von Provisionen, Verwaltung von Projektzeitplänen, Analyse von Verkaufsdaten und Abstimmung von Konten. Die Arbeit, die tatsächlich die Rechnungen bezahlt.

In den letzten fünf Jahren habe ich 847 Fachleute in 23 Branchen geschult und ihre Nutzungsmuster von Formeln verfolgt. Die Daten sind bemerkenswert konsistent: Diese zehn Formeln machen 87-93% der gesamten Formelnutzung in Produktions-Tabellenkalkulationen aus. Alles andere ist entweder spezialisiertes Arbeiten oder, häufiger, jemand verwendet eine komplizierte Formel, wenn eine einfache ausreichen würde.

Die Grundlage: VLOOKUP (Oder sein moderner Cousin, XLOOKUP)

Beginnen wir mit der Formel, die meine Karriere verändert hat. VLOOKUP ist das Schweizer Taschenmesser der Tabellenverarbeitung, und wenn Sie nur eine Formel aus diesem Artikel lernen, dann machen Sie es diese. In meinen Tracking-Daten macht VLOOKUP und seine Varianten etwa 31% der gesamten Formelnutzung in Geschäftstabellen aus.

Folgendes macht VLOOKUP: Es sucht einen Wert in einer Tabelle und gibt einen entsprechenden Wert aus einer anderen Spalte zurück. Klingt einfach, aber es löst eine enorme Anzahl von realen Problemen. Müssen Sie die Namen der Kunden mit ihren Kontonummern abgleichen? VLOOKUP. Möchten Sie Preisdaten aus Ihrer Hauptproduktliste abrufen? VLOOKUP. Versuchen Sie, zwei Berichte aus verschiedenen Systemen abzugleichen? Richtig.

Die grundlegende Syntax lautet: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Ich gebe Ihnen ein echtes Beispiel von einem Kunden, mit dem ich letztes Jahr gearbeitet habe. Sie betrieben ein Großhandelsvertriebsunternehmen mit 4.200 SKUs. Jede Woche erhielten sie Verkaufsdaten aus ihrem POS-System mit Produktcodes, aber ohne Produktnamen oder Kategorien. Jemand suchte manuell jeden Code in ihrer Hauptproduktliste und gab die Informationen ein. Das dauerte jede Woche etwa 6 Stunden.

Mit VLOOKUP reduzierten wir dies auf null Stunden. Die Formel stellte automatisch jeden Produktcode mit der Hauptliste überein und zog den Namen, die Kategorie, den Lieferanten und die Kostendaten ein. Die Formel sah so aus: =VLOOKUP(A2,ProductMaster!A:E,2,FALSE). Diese eine Formel sparte ihnen 312 Stunden pro Jahr.

Wenn Sie jetzt Microsoft 365 oder Excel 2021 verwenden, haben Sie Zugriff auf XLOOKUP, das im Wesentlichen der intelligentere, jüngere Bruder von VLOOKUP ist. Es ist flexibler, leichter zu lesen und bricht nicht, wenn Sie Spalten einfügen. Die Syntax ist sogar einfacher: =XLOOKUP(lookup_value, lookup_array, return_array). Ich empfehle, XLOOKUP zu lernen, wenn Sie Zugriff darauf haben, aber VLOOKUP funktioniert weiterhin einwandfrei und ist universell kompatibler.

Der häufigste Fehler, den ich bei VLOOKUP sehe, ist, dass Leute vergessen, das vierte Argument auf FALSE (oder 0) zu setzen. Dieses Argument steuert, ob Sie eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung wünschen. Für 95% der Geschäftsanwendungen möchten Sie FALSE für eine genaue Übereinstimmung. TRUE zu verwenden oder dieses Argument wegzulassen, kann zu äußerst falschen Ergebnissen führen, die schwer zu erkennen sind.

Das Arbeitstier: SUMIF und SUMIFS

Wenn VLOOKUP das Schweizer Taschenmesser ist, dann ist SUMIF das zuverlässige Pickup-Truck der Tabellenformeln. Es macht etwa 18% der Formelnutzung in meinem Datensatz aus, und das aus gutem Grund: Es löst das unglaublich häufige Problem „Addiere alle Zahlen, die bestimmten Kriterien entsprechen“.

Nach der Analyse von über 3.000 Tabellenkalkulationen in 23 Branchen ist das Muster unbestreitbar: zehn Formeln bewältigen 90% der realen Geschäftstätigkeit. Alles andere ist entweder spezialisiert oder überkompliziert.

Die grundlegende Syntax von SUMIF lautet: =SUMIF(range, criteria, [sum_range])

Hier ist ein Szenario, dem ich ständig begegne: Sie haben einen Verkaufsbericht mit 500 Transaktionen, und Sie müssen die Gesamtverkäufe für die Region Nordosten kennen. Ohne SUMIF addieren Sie entweder manuell Zahlen (fehleranfällig und zeitaufwändig) oder filtern und kopieren Daten an einen anderen Ort (unordentlich und schwer zu pflegen). Mit SUMIF ist es eine einzige Formel: =SUMIF(B:B,"Northeast",C:C).

Die wahre Kraft kommt jedoch mit SUMIFS (beachten Sie das S), mit dem Sie mehrere Kriterien verwenden können. Ich arbeitete mit einem Fertigungsunternehmen, das die Gesamtherstellungskosten nach Produktlinie, nach Quartal und nach Standort berechnen musste. Sie hatten eine Tabelle mit 8.700 Zeilen Produktionsdaten. Vor SUMIFS erstellten sie Pivot-Tabellen für jede mögliche Kombination, die sie benötigen könnten. Mit SUMIFS konnten sie jede Kombination auf Abruf erhalten.

Die Formel sah so aus: =SUMIFS(E:E,A:A,"Widget A",B:B,"Q2",C:C,"Facility 3"). Diese einzelne Formel ersetzte einen 45-minütigen Prozess des Filterns, Kopierens und Berechnens. Sie mussten diese Berechnungen etwa 30 Mal pro Monat durchführen, also sprechen wir von einer Einsparung von ungefähr 22 Stunden Arbeit monatlich.

Hier ist ein Profi-Tipp, für den ich Jahre gebraucht habe, um ihn herauszufinden: Sie können Platzhalter in Ihren Kriterien verwenden. Müssen Sie alle Produkte summieren, die mit „PRO“ beginnen? Verwenden Sie =SUMIF(A:A,"PRO*",B:B). Das Sternchen ist ein Platzhalter, der mit beliebigen Zeichen übereinstimmt. Das ist äußerst nützlich, wenn Sie mit inkonsistenten Dateneingaben umgehen müssen.

Eine weitere leistungsstarke Technik ist die Verwendung von Zellreferenzen für Ihre Kriterien, anstatt sie fest zu codieren. Anstelle von =SUMIF(A:A,"Northeast",B:B) verwenden Sie =SUMIF(A:A,D2,B:B), wobei D2 „Northeast“ enthält. Dies macht Ihre Tabelle dynamisch und einfacher zu aktualisieren. Ich habe gesehen, dass diese einzelne Änderung die Wartungszeit für Tabellenkalkulationen in Organisationen, die regelmäßig ihre Analysekriterien ändern müssen, um 40% reduziert hat.

Der Entscheidungsfinder: IF-Anweisungen

IF-Anweisungen sind der Logikmotor von Tabellenkalkulationen und machen etwa 16% der Formelnutzung aus. Sie ermöglichen es Ihnen, Entscheidungen auf Basis von Bedingungen zu treffen, was für fast jeden Geschäftsprozess grundlegend ist. Die grundlegende Syntax ist wunderschön einfach: =IF(logical_test, value_if_true, value_if_false)

Aber lassen Sie sich von der Einfachheit nicht täuschen. Ich habe gesehen, dass IF-Anweisungen Probleme lösen, die von einfachen Provisionsberechnungen bis hin zu komplexen Genehmigungsworkflows reichen. Eines meiner Lieblingsbeispiele stammt von einem Immobilienunternehmen, für das ich 2021 beratend tätig war. Sie mussten die Provisionen der Agenten basierend auf einer gestuften Struktur berechnen: 3% für Verkäufe unter 500.000 $, 3,5% für Verkäufe zwischen 500.000 $ und 1.000.000 $ und 4% für Verkäufe über 1.000.000 $.

Die Formel lautete: =IF(B2<500000,B2*0.03,IF(B2<1000000,B2*0.035,B2*0.04)). Diese verschachtelte IF-Anweisung regelte automatisch alle drei Stufen. Vor der Implementierung stellte ihr Buchhaltungsteam manuell jede Verkaufstransaktion ein und wandte den korrekten Satz an. Bei 340 Transaktionen pro Monat nahm das etwa 8 Stunden Arbeit in Anspruch. Mit der Formel reduzierte sich dieser Aufwand auf null.

Hier ist etwas Wichtiges, das ich gelernt habe: Die meisten Menschen bleiben bei einfachen IF-Anweisungen stehen, aber die wahre Kraft liegt darin, IF mit anderen Funktionen zu kombinieren. Zum Beispiel ermöglicht die Kombination von IF mit AND oder OR, mehrere Bedingungen zu testen. Müssen Sie Bestellungen markieren, die sowohl über 10.000 $ als auch von neuen Kunden stammen? =IF(AND(B2>10000,C2="New"),"Priority","Standard").

Ich habe mit einem Kundenserviceteam gearbeitet, das Supportanfragen basierend auf drei Faktoren priorisieren musste: Kundenstufe (Premium, Standard, Basis), Schwere des Problems (Hoch, Mittel, Niedrig) und Zeit seit der Einreichung. Wir haben eine Formel erstellt, die verschachtelte IF-Anweisungen kombiniert mit AND-Funktionen verwendet, die automatisch...