17 erweiterte Excel-Formeln – ein Muss für alle Profis

Wenn Sie häufig Ihre Hände beugen und Stunden damit verschwenden, Dinge manuell in Excel zu erledigen, verpassen Sie, wie leistungsfähig Excel sein kann, wenn Sie wissen, wie man es richtig verwendet. Microsoft Excel ist bekannt für seine Formeln, die effizient mit einer großen Anzahl von Daten in der Zelle arbeiten können, ohne dass viel manuelles Eingreifen in den Prozess erforderlich ist.

Wir haben 17 fortschrittliche Excel-Formeln zusammengestellt, die eine unheimliche Fähigkeit haben, langwierige, alltägliche, manuelle Aufgaben in ein paar Sekunden Arbeit zu verwandeln. Dies sind Formeln, die jeder Profi zur Hand haben sollte, um seine eigene kostbare Zeit zu sparen oder seinen Chef in einem kritischen Moment zu beeindrucken.

Gehen Sie die Liste durch und sagen Sie uns Ihren Favoriten!

Die Lernenden erhalten eine durchschnittliche Gehaltserhöhung von 58 %, wobei die höchste bis zu 400 % beträgt.

1. INDEX-ÜBEREINSTIMMUNG

Formel = INDEX(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

Eine hervorragende Alternative zur SVERWEIS- oder WVERWEIS-Formel in Excel, die einige Nachteile bei der Durchführung von Suchaufgaben hat. Der INDEX MATCH ist eine Kombinationsformel aus 2 separaten Funktionen:

INDEX: Diese Formel gibt den Wert einer Zelle in einer Tabelle basierend auf der Spalten- und Zeilennummer zurück.

MATCH: Diese Formel gibt die Position einer Zelle in einer Zeile oder Spalte zurück.

Ein Beispiel für die Kombination der INDEX- und MATCH-Formeln ist: Wenn Sie die Größe einer Person basierend auf ihrem Namen nachschlagen und zurückgeben, können Sie diese Formel verwenden, um beide Variablen (in diesem Fall Name und Größe) mithilfe der INDEX MATCH-Formel zu ändern .

Quelle

Lassen Sie es uns Schritt für Schritt aufschlüsseln:

So kombinieren Sie INDEX und MATCH

• Geben Sie INDEX ein
• Wählen Sie den Bereich aus, den Sie indizieren möchten
• Sperren Sie den Bereich mit F4
• Suchen Sie die Zeile, in der Sie nach den Daten suchen möchten
• Geben Sie MATCH mit der Informationsreihe ein und sperren Sie den Bereich mit F4
• Geben Sie 0 für die genaue Übereinstimmung ein, schließen Sie die Klammern
• Drücke Enter

Interessante Ideen für Data Science-Projekte

Jetzt haben Sie einen vollständig dynamischen und funktionalen Satz von Spalten und Zeilen im Blatt, in dem das gesamte Durchkämmen der Zellen und Zeilen nach den richtigen Daten automatisch erfolgt.

2. DURCHSCHNITT

Um die Durchschnittsformel auszuführen, um den Mittelwert eines beliebigen Zahlenbereichs zu finden, müssen Sie die folgenden Schritte ausführen:

Geben Sie die Werte, Zellen oder Zellenskalen ein, die Sie im Format berechnen.

Die Formel muss mit =AVERAGE(Zahl1, Zahl 2 usw.) beginnen.

Wenn Sie den Durchschnitt einer Reihe von Zellen im Blatt berechnen möchten, müssen Sie die folgenden Schritte ausführen:

• Geben Sie die Werte, Zellen oder Zellenskalen, die Sie berechnen, in das Format ein, genau wie oben
• Die Formel sollte wie folgt aussehen: =AVERAGE(Startwert: Endwert).

Wenn Sie sich fragen, wie Sie die Werte für den Zellbereich eingeben, können Sie den Bereich auf Ihrem Blatt mit der Maus auswählen und mit F4 sperren. Dadurch erledigt Excel den Rest der Arbeit für Sie, ohne die Zahl mit der Anzahl der Elemente in der Gruppe zu summieren und zu dividieren.

3. SUMMEWENN

Diese Formel in Excel wird als SUMMEWENN(Bereich, Kriterien, [Summenbereich]) bezeichnet. Dies würde die Summe der Werte innerhalb des gewünschten Zellbereichs ergeben, die die von Ihnen festgelegten Anforderungen erfüllen würden. Beispiel: =SUMMEWENN(C3: C12, „>70.000) würde die Summe der Werte zwischen den Zellen von C3 und C12 nur aus den Zellen zurückgeben, die einen Wert über 70.000 haben.

Bei Finanz-, Gehalts- oder auch Kostenkalkulationen benötigen Sie den Wert von Leads, die durch die von Ihnen festgelegte Bedingung bestimmten Mitarbeitern zugeordnet sind. Dies manuell zu tun ist sehr zeitaufwändig und verlangsamt die Dinge.

Die Formel für die obige Bedingung kann =SUMIF(Bereich, Kriterien, [sum_range]) lauten, wobei Bereich als Bereich des Blatts definiert werden kann, aus dem Sie die Werte auswählen müssen.

[sum_range] ist als zusätzlicher oder optionaler Bereich definiert, den Sie zusätzlich zum ersten eingegebenen Bereich addieren werden.

Hier ist ein Beispiel:

Quelle

4. OFFSET KOMBINIERT MIT SUMME

Alleine ist die OFFSET-Funktion möglicherweise nicht praktisch, aber in Kombination mit anderen Diensten können Sie eine komplexe Formel mit schnelleren Ergebnissen erhalten, wenn Sie eine dynamische Rolle erstellen möchten, die eine beliebige variable Anzahl von Zellen summieren kann, die reguläre SUM-Formel, das heißt static muss mit der OFFSET-Funktion kombiniert werden.

Um also die Summe der Werte in variablen Zellen herauszufinden, sollte die Formel lauten:

=SUMME(B4:OFFSET(B4,0,E2-1))

Quelle

Hier wird die Endreferenz der SUM-Funktion durch die OFFSET-Funktion ersetzt. Die in B4 beginnende SUMME-Formel endet mit einer Variablen und ist eine OFFSET-Formel, die bei B4 beginnt und sich mit dem Wert in E2 („3“) minus eins fortsetzt. Dies hilft der Methode, sich über zwei Zellen zu bewegen und die Daten von drei Jahren zu summieren. In der obigen Referenz können Sie sehen, dass die Zelle F7 die Summe der Zellen B4 enthält: D4 = 15.

5. WENN UND

Diese Formel ist praktisch, wenn Sie bestimmte Bedingungen schaffen müssen, um einen Haufen Daten zu durchkämmen. Die IF-Anweisung hilft bei der Verwendung der erweiterten Excel-IF-Funktion zum Erstellen eines neuen Felds basierend auf diesen Bedingungen auf einer bereits vorhandenen Spur.

Wenn Sie beispielsweise die Mitarbeiter mit Gehältern über 50.000 und einer Mitarbeiter-ID größer als 3 markieren möchten, lautet die Formel:

WENN(UND(E4>3,F4>50000)1,0)

Quelle

Da es in den obigen Daten keine echten Fälle gibt, würde die Formel den Wert 0 zurückgeben.

6. VERKETTEN

Wenn Sie viele Textzeichenfolgen in Ihrem Datenblatt haben und die Daten in einer Zeile erscheinen lassen möchten, ist diese Formel Ihre erste Wahl. Wenn Sie beispielsweise die Mitarbeiter-ID und den Mitarbeiternamen in einer einzigen Spalte darstellen möchten, sollte die Methode wie folgt aussehen:

=VERKETTEN(B3, C3)

Quelle

7. PMT

Diese Funktion hilft Ihnen, die zukünftigen Zahlungen für einen Kredit unter Berücksichtigung eines bestimmten Zinssatzes, Kapitalbetrags und einer bestimmten Laufzeit des Kredits zu ermitteln. Hier ist, was Sie brauchen, um zu beginnen:

• Die Laufzeit des Darlehens
• Das Anfangskapital (Geld) des Darlehens
• Der zukünftige Wert
• Die Art des Darlehens

Wenn Sie nun die monatliche Zahlung für einen Kapitalbetrag ermitteln möchten, lautet die Formel dafür:

= PMT (Rate, pro, PV, [fv], [Typ])

Lassen Sie uns dies anhand eines Beispiels verstehen:

Quelle

=PMT(C2/12.B2.A2)

Und das Beste daran ist, dass Sie die untere rechte Ecke der PMT-Zellen über die Felder ziehen können, um automatisch den monatlichen Zahlungsbetrag für alle Felder zu berechnen!

Beliebte Arten von Data-Science-Jobs

8. TRIMMEN

Dies ist eine der am häufigsten verwendeten Formeln in Excel, die unerwünschten Platz in den Feldern bereinigt. Beispiel: Wenn Sie die Leerzeichen am Anfang eines Namens entfernen müssen, können Sie dies mit der TRIM-Funktion tun:

=TRIMM(C6)

Quelle

Dies würde Ihnen den Wert von Chandan Kale ohne zusätzliche Leerzeichen an der Vorderseite oder am Ende zurückgeben.

9. LEN

Dies ist eine Funktion, die Ihnen die Anzahl der Zeichen in einer Textfolge mitteilt. Eine der aufregendsten Möglichkeiten, dies zu verwenden, ist beispielsweise, wenn Sie die Spender hervorheben möchten, die über 1.000 US-Dollar gespendet haben, indem Sie die Anzahl der Ziffern in der Spendenspalte zählen, lautet die Formel:

=LEN(B2)

Quelle

Und wenn Sie alle Zellen in der Spalte „Spende“ hervorheben möchten, müssen Sie Folgendes tun:

• Wählen Sie im Menü die Registerkarte Startseite
• Klicken Sie auf Bedingte Formatierung (in der Symbolleiste)
• Wählen Sie Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen

Quelle

Wenn Sie hier die Bedingung „>3“ angeben, erhalten alle Beträge über 1.000 $ die eindeutige Formatierung, die Sie auswählen können, indem Sie auf die Option „Format“ klicken.

10. WÄHLEN

Dies ist eine großartige Funktion für die Szenarioanalyse in der Finanzmodellierung. Sie können zwischen einer bestimmten Anzahl von Optionen wählen und die von Ihnen gewählte „Wahl“ zurückgeben. Wenn Sie beispielsweise basierend auf Annahmen drei verschiedene Werte für das Umsatzwachstum im nächsten Jahr haben, können Sie mithilfe der Funktion AUSWÄHLEN den Betrag gemäß Ihren Anforderungen zurückgeben.

Die Formel lautet:

=WÄHLEN(C7,D3,D4,D5)

Quelle

11. ZELLE, LINKS, MITTE und RECHTS

Alle oben genannten Funktionen können auf viele Arten kombiniert werden, um einige erweiterte Formeln zu erhalten.

• Die CELL-Funktion wird verwendet, um verschiedene Arten von Informationen über den Inhalt der Zelle zurückzugeben
• Der LEFT-Dienst wird verwendet, um Text vom Anfang der Zelle zu ersetzen.
• Die MID-Funktion kann Text von jedem der Startpunkte der Zelle zurückgeben.
• Die RIGHT-Funktion gibt nur Text vom Ende der Zelle zurück.

Quelle

12. XNPV und XIRR

Für alle Analysten, die auf Investment Banking, Aktienresearch oder andere Bereiche der Unternehmensfinanzierung stoßen, die eine Diskontierung von Cashflows erfordern, werden Ihnen diese Formeln mit Sicherheit viel Zeit und Ärger ersparen!

Fragen und Antworten zu Data Science-Interviews

Wenn Sie beispielsweise den gegenwärtigen Nettowert (NPV) für eine Investition unter Verwendung einer bestimmten Diskontierungsrate und Cashflows, die in unregelmäßigen Abständen auftreten, berechnen möchten, verwenden Sie die folgende Funktion.

=XNPV(Abzinsungssatz, Cashflows, Termine)

Quelle

Andererseits gibt Ihnen die XIRR-Funktion die interne Rendite (wobei Abfluss = Zufluss) für eine Reihe von Cashflows an, die in unregelmäßigen Abständen auftreten, wie z. B.:

13. GRAF ( )

Analysten sind oft auf Trab, wenn sie Schwierigkeiten haben, die Anzahl der Zellen mit Werten (Zahlen, Fehler, Text, logische Werte) und der Zellen, die leer sind, zu finden. Die Funktion ZÄHLENA( ) kann Ihnen dabei helfen, den Namen von nicht leeren Zellen in einem ausgewählten Bereich herauszufinden. Die Formel zum Zählen von Werten für ein Datenblatt mit den Spalten A1-A10 lautet beispielsweise:

=ZAHLA(A1: A10)

Quelle

14. FV

Diese Formel ist praktisch, wenn Sie Geld in etwas investieren und seinen Wert kennen möchten. Die Anforderungen der FV-Formel sind:

• Der Zinssatz des Darlehens
• Anzahl der Zahlungen
• Die Zahlung für jeden Zeitraum
• Aktuelles Startguthaben
• Art des Darlehens

Zum Beispiel, wenn Sie mehrere CD-Rohlinge vergleichen möchten und eine Erbschaft von 20.000 US-Dollar haben, die Sie in eine CD investieren können. Die Zinssätze werden im Dezimalformat dargestellt; Zahlungen sind null. Die Formel für das Szenario lautet:

=FV(A2/12,B2,C2,D2)

Quelle

Die Ergebnisse werden sein:

Quelle

15. RANDZWISCHEN

Diese Funktion hilft, eine Zahl innerhalb eines vordefinierten Zahlenbereichs zufällig auszuwählen. Sobald Sie die niedrigsten und höchsten Zahlen in die Formel eingegeben haben, kann Excel die richtigen Daten aus den Feldern auswählen, an die die Namen im Bereich angehängt sind, und zufällig daraus auswählen. Die Methode für das Szenario ist:

=RANDBETWEEN(Startpunkt, Endpunkt)

Quelle

16. KLEIN

Die SMALL-Funktion in Excel gibt numerische Werte basierend auf der Position des Werts in einer Liste zurück, die nach Wichtigkeit geordnet ist. Diese Funktion hilft, die „n-ten kleinsten Werte“ aus einem Array oder einem Bereich von Zellen abzurufen, z. B. den kleinsten Wert, den zweitniedrigsten Wert, den drittniedrigsten Wert usw.

Die Syntax für die Formel lautet

=KLEIN (Zeiten,Bereich)

Zum Beispiel,

Quelle

Da die SMALL-Funktion automatisch ist, müssen Sie einen Bereich und eine Ganzzahl für „nth“ angeben, um den Rangwert anzugeben. Die offiziellen Namen für diese Argumente sind 'array' und 'k'.

17. VIERTEL

Diese Funktion gibt das Quartil (jede von vier gleichen Gruppen) in einem bestimmten Datensatz zurück und kann den Mindestwert, das erste Quartil und den Höchstwert des zweiten Quartils zurückgeben. Diese Funktion liefert die Quartilsmenge der Felder in einem Array. Die Funktion gibt einen numerischen Wert entsprechend dem angeforderten Perzentil zurück.

Syntax: =QUARTILE (Array, Quart)

Quelle

FAZIT:

Microsoft Excel ist am Arbeitsplatz des 21. Jahrhunderts ziemlich unvermeidlich, aber der Trick ist, dass es nicht so einschüchternd sein muss. Machen Sie es zu Ihrem Freund und beobachten Sie, wie Ihre Produktivität in die Höhe schießt!