Suchen und Abrufen einer gesamten Spalte
Um eine gesamte Spalte durch das Abgleichen eines bestimmten Werts abzurufen, wird Ihnen eine INDEX- und MATCH- Formel helfen.
Suchen und Abrufen einer gesamten Spalte basierend auf einem bestimmten Wert
Summieren einer gesamten Spalte basierend auf einem bestimmten Wert
Weitere Analyse einer gesamten Spalte basierend auf einem bestimmten Wert
Suchen und Abrufen einer gesamten Spalte basierend auf einem bestimmten Wert
Um eine Liste der Q2-Umsätze entsprechend der obigen Tabelle zu erhalten, können Sie zunächst die MATCH-Funktion verwenden, um die Position der Q2-Umsätze zurückzugeben, und diese wird an INDEX weitergegeben, um die Werte an dieser Position abzurufen.
Allgemeine Syntax
=INDEX(Rückgabebereich;0;MATCH(Suchwert;Sucharray;0))
√ Hinweis: Dies ist eine Array-Formel, die Sie mit Strg + Umschalt + Enter eingeben müssen.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel die Q2-Umsatzliste zurückgeben soll. Hier bezieht es sich auf den Umsatzbereich.
- Suchwert: Der Wert, den die Kombinationsformel verwendet, um die entsprechenden Umsatzinformationen zu finden. Hier bezieht es sich auf das angegebene Quartal.
- Sucharray: Der Zellbereich, in dem der Suchwert abgeglichen werden soll. Hier bezieht es sich auf die Quartalsüberschriften.
- Abgleichstyp 0: Zwingt MATCH dazu, den ersten Wert zu finden, der genau dem Suchwert entspricht.
Um eine Liste der Q2-Umsätze zu erhalten, kopieren Sie bitte die folgende Formel in die Zelle I6, drücken Sie Strg + Umschalt + Enter, und doppelklicken Sie dann auf die Zelle und drücken Sie F9, um das Ergebnis zu erhalten:
=INDEX(C5:F11;0;MATCH("Q2";C4:F4;0))
Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu gestalten:
=INDEX(C5:F11;0;MATCH(I5;C4:F4;0))
Erklärung der Formel
=INDEX(C5:F11;0;MATCH(I5;C4:F4;0))
- MATCH(I5;C4:F4;0): Der Abgleichstyp 0 zwingt die MATCH-Funktion, die Position von Q2, dem Wert in I5, im Bereich C4:F4 zurückzugeben, welche 2 ist.
- INDEX(C5:F11C5:F11;0;MATCH(I5;C4:F4;0)) = INDEX(C5:F11C5:F11;0;2): Die INDEX-Funktion gibt alle Werte in der 2. Spalte des Bereichs C5:F11 in einem Array wie folgt zurück: {7865;4322;8534;5463;3252;7683;3654}. Beachten Sie, dass Sie zum Sichtbarmachen des Arrays in Excel doppelt auf die Zelle klicken müssen, in der Sie die Formel eingegeben haben, und dann F9 drücken müssen.
Summieren einer gesamten Spalte basierend auf einem bestimmten Wert
Da wir nun die Umsatzliste zur Hand haben, wäre es für uns einfach, das Gesamtumsatzvolumen von Q2 zu ermitteln. Alles was wir tun müssen, ist die SUM-Funktion zur Formel hinzuzufügen, um alle Umsatzwerte aus der Liste zu summieren.
Allgemeine Syntax
=SUM(INDEX(Rückgabebereich;0;MATCH(Suchwert;Sucharray;0)))
In diesem speziellen Beispiel, um das Gesamtumsatzvolumen von Q2 zu erhalten, kopieren Sie bitte die folgende Formel in die Zelle I8, und drücken Sie Enter, um das Ergebnis zu erhalten:
=SUM(INDEX(C5:F11;0;MATCH(I5;C4:F4;0)))
Erklärung der Formel
=SUM(INDEX(C5:F11;0;MATCH(I5;C4:F4;0)))
- MATCH(I5;C4:F4;0): Der Abgleichstyp 0 zwingt die MATCH-Funktion, die Position von Q2, dem Wert in I5, im Bereich C4:F4 zurückzugeben, welche 2 ist.
- INDEX(C5:F11;0;MATCH(I5;C4:F4;0)) = INDEX(C5:F11;0;2): Die INDEX-Funktion gibt alle Werte in der 2. Spalte des Bereichs C5:F11 in einem Array wie folgt zurück: {7865;4322;8534;5463;3252;7683;3654}.
- SUM(INDEX(C5:F11;0;MATCH(I5;C4:F4;0))) = SUM({7865;4322;8534;5463;3252;7683;3654}): Die SUM-Funktion summiert alle Werte im Array und erhält dann das Gesamtumsatzvolumen von Q2, $40.773.
Weitere Analyse einer gesamten Spalte basierend auf einem bestimmten Wert
Für zusätzliche Verarbeitung der Q2-Umsatzliste können Sie einfach andere Funktionen wie SUMME, MITTELWERT, MAX, MIN, GROSS usw. zur Formel hinzufügen.
Zum Beispiel, um ein durchschnittliches Umsatzvolumen während Q2 zu erhalten, können Sie die folgende Formel verwenden:
=MITTELWERT(INDEX(C5:F11;0;MATCH(I5;C4:F4;0)))
Um den höchsten Umsatz während Q2 herauszufinden, verwenden Sie eine der folgenden Formeln:
=MAX(INDEX(C5:F11;0;MATCH(I5;C4:F4;0)))
ODER
=GROSS(INDEX(C5:F11;0;MATCH(I5;C4:F4;0));1)
Verwandte Funktionen
Die Excel INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.
Die Excel MATCH-Funktion sucht nach einem bestimmten Wert in einem Zellbereich und gibt die relative Position des Werts zurück.
Verwandte Formeln
Suchen und Abrufen einer gesamten Zeile
Um eine gesamte Zeile von Daten durch das Abgleichen eines bestimmten Werts abzurufen, können Sie die INDEX- und MATCH-Funktionen verwenden, um eine Array-Formel zu erstellen.
Exakte Übereinstimmung mit INDEX und MATCH
Wenn Sie Informationen über ein bestimmtes Produkt, einen Film oder eine Person in Excel finden müssen, sollten Sie die Kombination aus INDEX- und MATCH-Funktionen gut nutzen.
Ungefähre Übereinstimmung mit INDEX und MATCH
Es gibt Zeiten, in denen wir in Excel ungefähre Übereinstimmungen finden müssen, um die Leistung der Mitarbeiter zu bewerten, die Noten der Schüler zu berechnen, die Portokosten basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial sprechen wir darüber, wie man die INDEX- und MATCH-Funktionen verwendet, um die benötigten Ergebnisse abzurufen.
Groß-/Kleinschreibung-sensitives Suchen
Sie wissen vielleicht, dass Sie die INDEX- und MATCH-Funktionen kombinieren oder die VLOOKUP-Funktion verwenden können, um Werte in Excel zu suchen. Allerdings sind die Suchvorgänge nicht groß-/kleinschreibungssensitiv. Um also eine groß-/kleinschreibungssensitive Übereinstimmung durchzuführen, sollten Sie die EXACT- und CHOOSE-Funktionen nutzen.
Die besten Tools zur Büroproduktivität
Kutools für Excel - Hilft Ihnen, aus der Menge hervorzustechen
Kutools für Excel bietet über 300 Funktionen, sodass das, was Sie benötigen, nur einen Klick entfernt ist...
Office Tab - Aktiviert tabbed Lesen und Bearbeiten in Microsoft Office (inklusive Excel)
- In einer Sekunde zwischen Dutzenden offener Dokumente wechseln!
- Reduziert Hunderte von Mausklicks für Sie jeden Tag, verabschieden Sie sich von der Maushand.
- Steigert Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
- Bringt effiziente Tabs ins Office (inklusive Excel), genau wie bei Chrome, Edge und Firefox.