14 wesentliche Microsoft Excel-Funktionen für die Datenanalyse

1.. Verkettet

= Concatenat ist eine der wichtigsten Funktionen für die Datenanalyse, da Sie Text, Zahlen, Daten usw. aus mehreren Zellen zu einem kombinieren können. Um diese Funktion zu verwenden, folgen Sie der folgenden Syntax:

=CONCATENATE(Cell 1, Cell 2)

Zelle 1 und Zelle 2 beziehen sich auf die Zellen mit Werten, die Sie zusammenführen möchten. Es ist auch praktisch zum Erstellen der Tracking-Parameter für Marketingkampagnen, zum Erstellen von API-Abfragen, zum Hinzufügen von Text zu einem Zahlenformat und für viele andere Dinge.

2. Len

=LEN ist eine weitere praktische Funktion zur Datenanalyse, die im Wesentlichen die Anzahl der Zeichen in einer bestimmten Zelle ausgibt. Die Funktion ist hauptsächlich beim Erstellen von Titel-Tags oder Beschreibungen mit einer Zeichenbeschränkung verwendbar. Die Syntax ist unten:

=LEN(Cell)

Die Zelle in der obigen Syntax kann einen Wert wie A1, B2 usw. haben. Die Funktion kann auch nützlich sein, wenn die Unterschiede zwischen verschiedenen eindeutigen Identifikatoren ermittelt werden, die häufig langwierig und nicht in der richtigen Reihenfolge sind.

3.. Vlookup

=SVERWEIS kann Daten aus einer Tabelle mit einem Eingabewert abgleichen. Die Funktion bietet zwei Matching-Modi - Exakt und Approximate - gesteuert durch den Suchbereich. Wenn Sie den Bereich auf FALSE festlegen, wird nach einer genauen Übereinstimmung gesucht. Wenn Sie ihn jedoch auf TRUE festlegen, wird nach einer ungefähren Übereinstimmung gesucht. Unten ist die Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Die Elemente dieser Funktion sind wie folgt unterteilt:

• Lookup_value: Dies ist das Element, nach dem Sie suchen müssen.
• table_array : Bereich der Zellen, die die Daten enthalten, die Sie suchen möchten.
• col_index_num: Spaltennummer im table_array, die den Wert enthält, den Sie zurückgeben möchten.
• [range_lookup]: Optionales Argument, das angibt, wie die Funktion den Lookup-Wert findet. TRUE für ungefähre Übereinstimmungen, FALSE für genaue Übereinstimmungen.

4. INDEX/MATCH

Wie bei der Vlookup -Funktion sind die Index- und Übereinstimmungsfunktionen für die Suche nach bestimmten Daten basierend auf einem Eingabewert nützlich.

Wenn der Index und der Übereinstimmung zusammen verwendet werden, können die Einschränkungen des Vlookup bei der Bereitstellung der falschen Ergebnisse überwunden werden (wenn Sie nicht vorsichtig sind). Wenn Sie diese beiden Funktionen kombinieren, können sie die Datenreferenz bestimmen und nach einem Wert in einem einzeldimensionalen Array suchen. Das gibt die Koordinaten der Daten als Zahl zurück. Unten finden Sie die Syntax:

=INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))

Im obigen Beispiel wollte ich die Anzahl der Aufrufe im Januar ermitteln. Dafür habe ich die Formel verwendet:

 =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3)

Hier,

• A2: C13 ist die Datensäule, von der ich die Formel zurückgeben soll.
• Jan ist der Wert, den ich übereinstimmen möchte.
• A2:A13 ist die Spalte, in der die Formel Jan findet.
• 0 bedeutet, dass die Formel eine genaue Übereinstimmung für den Wert finden soll.

Wenn Sie eine ungefähre Übereinstimmung finden möchten, müssen Sie die 0 durch 1 oder -1 ersetzen. 1 findet also den größten Wert weniger als oder gleich dem Lookup-Wert, und -1 findet den kleinsten Wert weniger als dem Suchwert. Beachten Sie, dass die Formel 1, wenn Sie nicht 0, 1 oder -1 verwenden, standardmäßig 1 verwendet.

5. MINIFS/MAXIFS

= Minifs und=maxifs ähneln den Funktionen=min und=max, außer dass Sie den minimalen/maximalen Wert von Werten nehmen und sie nach bestimmten Kriterien abgleichen. Die Funktion sucht also im Wesentlichen nach den minimalen/maximalen Werten und entspricht ihnen mit Eingabekriterien. Hier ist die Syntax:

=MINIFS(min_range, criteria_range1, criteria1,…)

=MAXIFS(max_range, criteria_range1, criteria1,…)

Im obigen Beispiel wollte ich die Mindestwerte auf der Grundlage des Geschlechts des Schülers finden. Dafür habe ich die Formel verwendet:

=MINIFS (C2:C10, B2:B10, "M") 

Ich habe das Ergebnis 27. Hier,

• C2: C10 ist die Säule, in der die Formel nach den Bewertungen sucht.
• B2:B10 ist eine Spalte, in der die Formel nach dem Kriterium (dem Geschlecht) sucht.
• m ist die Kriterien.

Ebenso habe ich die Formel unten für die Höchstpunktzahl verwendet und das Ergebnis 100 erhalten.

=MAXIFS(C2:C10, B2:B10, "M")

6. Durchschnitt

Mit der Funktion =AVERAGEIFS können Sie anhand eines oder mehrerer Kriterien einen Durchschnitt für einen bestimmten Datensatz ermitteln. Bei der Verwendung dieser Funktion sollten Sie bedenken, dass jedes Kriterium und jeder Durchschnittsbereich unterschiedlich sein kann.

In der=durchschnittlichen Funktion müssen die Kriterien und der Summenbereich jedoch denselben Größenbereich aufweisen. Unten finden Sie die Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1,…)

Im obigen Beispiel wollte ich die durchschnittliche Punktzahl basierend auf dem Geschlecht des Schülers finden. Dafür habe ich die Formel unten verwendet und 56,8 bekommen. Hier,

• C2: C10 ist der Bereich, in dem die Formel nach dem Durchschnitt sucht.
• B2: B10 ist der Kriterienbereich.
• m ist die Kriterien.

=AVERAGEIFS(C2:C10, B2:B10, "M") 

7. Countifs

Wenn Sie die Instanzen zählen möchten, in denen ein Datensatz bestimmte Kriterien erfüllt, müssen Sie die Funktion=countifs verwenden. Mit dieser Funktion können Sie Ihrer Abfrage unbegrenzte Kriterien hinzufügen, sodass es am einfachsten ist, die Zählung basierend auf den Eingabekriterien zu finden. Hier ist die Syntax:

=COUNTIFS(criteria_range1, criteria1,…)

In diesem Beispiel wollte ich die Anzahl der männlichen oder weiblichen Studenten finden, die vorbeikamen (d. H.>=40). Dafür habe ich die folgende Formel verwendet:

=COUNTIFS(B2:B10, "M", C2:C10, ">=40")

Hier,

• B2:B10 ist der Bereich, in dem die Formel nach dem ersten Kriterium (Geschlecht) sucht.
• m ist das erste Kriterium.
• C2: C10 ist der Bereich, in dem die Formel nach dem zweiten Kriterium (Markierungen) sucht.
• >=40 ist das zweite Kriterium.

8. SUMMENPRODUKT

Mit der Funktion =SUMPRODUCT können Sie Bereiche oder Arrays miteinander multiplizieren und anschließend die Summe der Produkte zurückgeben. Es ist eine vielseitige Funktion und kann zum Zählen und Summieren von Arrays wie COUNTIFS oder SUMIFS verwendet werden, jedoch mit zusätzlicher Flexibilität. Sie können auch andere Funktionen innerhalb von SUMPRODUCT verwenden, um die Funktionalität weiter zu erweitern. Hier ist die Syntax:

=SUMPRODUCT(array1, [array2], [array3],…)

In diesem Beispiel wollte ich die Gesamtzahl aller verkauften Produkte finden. Ich habe die Formel verwendet:

=SUMPRODUCT(B2:B8, C2:C8) 

Hier,

• B2:B8 ist das erste Array (die Menge der verkauften Produkte).
• C2: C8 ist das zweite Array (der Preis für jedes Produkt). Die Formel multipliziert dann die Menge jedes Produkts, das nach seinem Preis verkauft wird, und fügt alles hinzu, um den Gesamtumsatz zu liefern.

9. TRIMMEN

Die=Trim -Funktion ist besonders nützlich, wenn Sie mit einem Datensatz mit mehreren Leerzeichen oder unerwünschten Zeichen arbeiten. Mit der Funktion können Sie diese Leerzeichen oder Zeichen einfach aus Ihren Daten entfernen und genaue Ergebnisse erhalten, während Sie andere Funktionen verwenden. Hier ist die Syntax:

=TRIM(text)

In diesem Beispiel wollte ich alle zusätzlichen Leerzeichen zwischen den Wörtern Maus und Pad in A7 entfernen. Dafür habe ich die Formel verwendet:

 =TRIM(A7)

10. Suche/Suche

Abgerundet werden die Fund-/Suchfunktionen abgerundet, mit denen Sie spezifischen Text in einem Datensatz isolieren können. Beide Funktionen sind in dem, was sie tun, mit Ausnahme eines Hauptunterschieds ähnlich-die Find-Funktion gibt nur Fall-sensitive Übereinstimmungen zurück. In der Zwischenzeit hat die=Suchfunktion keine solchen Einschränkungen. Hier ist die Syntax für Find bzw. Suche:

=FIND(find_text, within_text, [start_num])

=SEARCH(find_text, within_text, [start_num])

In diesem Beispiel wollte ich feststellen Formel unten, die das Ergebnis 1 lieferte 1.

=FIND(A2, B2)

Wenn ich stattdessen herausfinden wollte, wie oft gui in Guiding Tech vorkam, müsste ich die Formel =SEARCH verwenden, da die Groß-/Kleinschreibung nicht beachtet wird. Diese Funktionen sind besonders nützlich bei der Suche nach Anomalien oder eindeutigen Identifikatoren.

11. Text

Die=Textfunktion verwandelt Zahlen in Text. Dies ermöglicht es, sie in einem bestimmten Format anzuzeigen. Sie können diese Funktion verwenden, um Klarheit und Konsistenz sicherzustellen. Hier ist die Syntax:

=TEXT(value, "format_text")

Im obigen Beispiel verwende ich die folgende Formel, um den Wert in G1 in Wörter umzuwandeln.

=TEXT(G1, "mmmm d, yyyy")

12. IFERROR

Die Funktion =IFERROR ist ein Fehlerbehandler, mit dem Sie angeben können, was das Excel-Programm bei einem Fehler anzeigen soll. Es hilft, viele #-Fehlerzeichen auf Ihren Blättern zu vermeiden. Hier ist die Syntax:

=IFERROR(value, value_if_error)

Im obigen Beispiel verwenden wir die folgende Formel, um eine Nachricht anzuzeigen, wenn ein Fehler vorliegt.

=IFERROR(A1/B1, "Division by zero error")

13. Filter

Die=Filterfunktion extrahiert spezifische Datensätze basierend auf einem oder mehreren Kriterien. Sie können Ergebnisse dynamisch filtern, ohne Änderungen an den Daten vorzunehmen. Daher können Sie verschiedene Teilmengen Ihrer Daten untersuchen und analysieren. Hier ist die Syntax:

=FILTER(array, include, [if_empty])

Wir können das folgende Beispiel verwenden, um Werte in Spalte B anzuzeigen, die Umsatz entsprechen.

=FILTER(A1:C10, B1:B10 = "Sales")

14. SORTIEREN

Die Funktion =SORT organisiert Daten in aufsteigender oder absteigender Reihenfolge. Hier ist die Syntax:

=SORT(array, sort_col1, sort_order1, [sort_col2, sort_order2], ...)

Im folgenden Beispiel sortieren wir Daten im Bereich A1:C10, 3 bezieht sich auf die dritte Spalte im Bereich und wir verwenden -1 > um in absteigender Reihenfolge zu sortieren.

=SORT(A1:C10, 3, -1)

Möglicherweise finden Sie bei der Verwendung dieser Funktionen auch einige Excel-Verknüpfungen hilfreich.