Wie kann man mehrere VLOOKUP-Ergebnisse in Excel mitteln?

Wenn Sie mehrere Werte finden und mitteln müssen, die mit demselben Suchbegriff verknüpft sind, ist die Verwendung einer direkten Formel eine der schnellsten und flexibelsten Möglichkeiten. Die AVERAGEIF-Funktion oder eine Array-Formel bewältigt dies leicht, ohne dass zusätzliche Spalten erstellt werden müssen.

Geben Sie die folgende Formel in eine leere Zelle ein (z. B. F2):

=AVERAGEIF(A1:A24,E2,C1:C24)

Drücken Sie die Eingabetaste Taste, nachdem Sie die Formel eingegeben haben. Dies gibt Ihnen sofort den Durchschnitt aller Werte in Spalte C, wobei der entsprechende Wert in Spalte A mit Ihrem in Zelle E2 befindlichen Suchwert übereinstimmt. Siehe die Abbildung unten:

Parametererklärung und Tipps:

• A1:A24: Der Bereich, der Ihre Suchwerte enthält.
• E2: Der spezifische Wert, nach dem Sie suchen möchten.
• C1:C24: Der Bereich, aus dem Sie die übereinstimmenden Werte mitteln möchten.

Alternative Vorgehensweise (für Benutzer, die mit Array-Formeln vertraut sind):

Geben Sie die folgende Formel in eine leere Zelle ein und bestätigen Sie mit Strg + Umschalt + Enter:

=AVERAGE(IF(A1:A24=E2,C1:C24))

Array-Formeln verarbeiten jeden Vergleich einzeln, was in Versionen von Excel hilfreich ist, die keine dynamischen Arrays unterstützen. Achten Sie sorgfältig darauf, dass die Bereiche genau dieselbe Größe haben, um Fehler zu vermeiden.

Praktische Szenarien und Hinweise:
- Am besten geeignet für Datensätze, die nicht gefiltert wurden und einfache Suchanforderungen haben.
- Wenn einer der Bereiche leere Zellen enthält, werden diese bei der Durchschnittsberechnung ignoriert.
- Bei dynamischen Tabellen oder beim Hinzufügen von Daten sollten Sie Tabellenreferenzen verwenden, um robustere Formeln zu erstellen.
- Achten Sie auf versehentliche Fehlanpassungen von Zellbereichen, die häufig Ursache für falsche Durchschnittswerte oder Fehler sind.

Wenn Sie häufig Daten basierend auf Duplikaten zusammenfassen und aggregieren müssen, bietet Kutools für Excel eine praktische Lösung durch das Hilfsprogramm 'Advanced Combine Rows'. Dieses Tool kann Werte wie Durchschnitt, Summe oder Anzahl für übereinstimmende Datensätze in einem Schritt kombinieren oder berechnen, was es besonders für größere Datensätze oder regelmäßige Berichte geeignet macht.

1. Markieren Sie den Bereich Ihrer Datentabelle, einschließlich der Suchspalte und der zu mittelnden Werte. Gehen Sie dann zu Kutools > Text > Erweiterte Zeilen zusammenführen. Siehe den Screenshot:

2. Im erscheinenden Dialogfeld:

• Wählen Sie die Spalte mit Ihren Suchwerten aus und klicken Sie auf Primärschlüssel.
• Wählen Sie die Spalte mit Ihren Zielwerten aus, dann klicken Sie auf Berechnen > Durchschnitt.
• Legen Sie Kombinations- oder Berechnungsregeln für andere Spalten fest – zum Beispiel Text mit Kommas kombinieren oder Summe, Maximal- oder Minimalwert anwenden.

3. Klicken Sie auf OK, um die Einstellungen zu übernehmen.

Die Zeilen mit doppelten Suchwerten werden nun zusammengeführt, und die Werte in der angegebenen Spalte werden für jeden eindeutigen Suchwert automatisch gemittelt. Dies ist besonders hilfreich für die Erstellung von Zusammenfassungsberichten oder das Kondensieren von Daten.

Praktischer Tipp: Die Verwendung von Advanced Combine Rows minimiert manuelle Berechnungen und das Potenzial für Fehler. Das Tool ist am besten für Benutzer geeignet, die regelmäßig Daten mit wiederkehrenden Suchwerten verarbeiten und schnell nutzbare Zusammenfassungen benötigen. Überprüfen Sie immer, ob die richtigen Spalten zugewiesen sind, bevor Sie sie kombinieren, insbesondere wenn sich die Datenstruktur ändert.

Demo: Mitteln mehrerer VLOOKUP-Ergebnisse mit Kutools für Excel

Pivot-Tabellen bieten einen dynamischen und visuellen Ansatz zur Zusammenfassung und Analyse von Daten. Mit einer Pivot-Tabelle können Sie Einträge automatisch nach ihren Suchwerten gruppieren und den Durchschnitt einer Zielspalte für jede Gruppe anzeigen, wodurch Sie eine interaktive Zusammenfassung erhalten, die sich aktualisiert, wenn sich Ihre Daten ändern.

Effektivste Szenarien: Dieser Ansatz ist gut geeignet, wenn Sie eine Gesamtübersicht für alle Suchwerte auf einmal benötigen, anstatt sich auf einen einzelnen Suchwert zu konzentrieren. Pivot-Tabellen sind auch hervorragend für schnelle Datenexploration, Berichtserstellung und wenn Sie Ihre Ergebnisse in einem sortierbaren, erweiterbaren Format präsentieren möchten.

Anweisungen:

• Wählen Sie Ihr gesamtes Dataset aus, einschließlich der Überschriften.
• Gehen Sie zu Einfügen > Pivot-Tabelle > Aus Tabelle oder Bereich. Wählen Sie, ob Sie die Pivot-Tabelle auf einem neuen Arbeitsblatt oder einem bestehenden platzieren möchten.
• Ziehen Sie im PivotTable-Felder-Bedienfeld die Spalte, die Ihre Suchwerte enthält, in den Zeilenbereich.
• Ziehen Sie die Spalte, die Sie mitteln möchten, in den Werte-Bereich. Klicken Sie auf das Wertefeld, wählen Sie Wertfeldeinstellungen, und legen Sie den Berechnungstyp auf Durchschnitt fest.

Dies führt zu einer Zusammenfassungstabelle, die jeden eindeutigen Suchwert mit seinem berechneten Durchschnitt für die zugehörigen Daten auflistet. Sie können die Gruppierung, den Filter oder Details leicht ändern, wenn nötig.

Vorteile: Keine Formeln erforderlich, unterstützt dynamische Updates, geeignet für Berichterstellung und Datenexploration.

Nachteile: Zusätzliche Schritte erforderlich, um nach Datenänderungen zu aktualisieren, weniger geeignet zum direkten Extrahieren eines einzelnen Wertes in andere Formeln, und die ursprüngliche Einrichtung erfordert grundlegende Kenntnisse über Pivot-Tabellen.

Tipps zur Fehlerbehebung: Wenn Werte als Anzahl oder Summe statt als Durchschnitt angezeigt werden, überprüfen Sie die Feldeinstellungen. Für beste Ergebnisse stellen Sie sicher, dass die Spalten passende Überschriften haben und klären Sie doppelte Spaltennamen vor der Erstellung der Pivot-Tabelle.

Für fortgeschrittene Benutzer und solche, die Daten verwalten, die regelmäßig aktualisiert werden, ermöglicht die Verwendung eines VBA-Makros die Automatisierung des Mittelungsprozesses für alle Einträge, die einem Suchwert entsprechen. Diese Methode durchläuft Ihre Daten, um jedes Treffer zu finden und den Durchschnitt zu berechnen, was sie für große Datensätze oder wenn Sie einen wiederholbaren Workflow benötigen, geeignet macht.

Anwendbare Szenarien und Hinweise: VBA ist ideal, wenn Sie häufig Durchschnittsberechnungen durchführen, Berichte automatisieren möchten oder einen flexiblen Ansatz benötigen, der an ungewöhnliche Datenlayouts angepasst werden kann. VBA-Makros funktionieren am besten, wenn Sie bequem Makros in Ihrer Arbeitsmappe aktivieren und benutzerdefinierte Ausgaben benötigen.

1. Gehen Sie zur Registerkarte Entwickler, wählen Sie Visual Basic oder drücken Sie Alt + F11, um den VBA-Editor zu öffnen, dann klicken Sie auf Einfügen > Modul. Kopieren und fügen Sie den unten stehenden Code in das neue Modul ein:

Sub AverageVlookupMatches()
    Dim lookupCol As Range
    Dim avgCol As Range
    Dim lookupValue As Variant
    Dim total As Double
    Dim count As Long
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set lookupCol = Application.InputBox("Select the lookup column", xTitleId, Selection.Address, Type:=8)
    Set avgCol = Application.InputBox("Select the column to average", xTitleId, , Type:=8)
    lookupValue = Application.InputBox("Enter lookup value", xTitleId, , Type:=2)
    
    Application.ScreenUpdating = False
    total = 0
    count = 0
    
    For i = 1 To lookupCol.Rows.Count
        If lookupCol.Cells(i, 1).Value = lookupValue Then
            If IsNumeric(avgCol.Cells(i, 1).Value) Then
                total = total + avgCol.Cells(i, 1).Value
                count = count + 1
            End If
        End If
    Next i
    
    If count > 0 Then
        MsgBox "Average of all matches: " & total / count, vbInformation, "Result"
    Else
        MsgBox "No matches found.", vbExclamation, "Result"
    End If
    
    Application.ScreenUpdating = True
End Sub

2. Nachdem Sie den Code eingefügt haben, schließen Sie den VBA-Editor. Um das Makro auszuführen, kehren Sie zu Excel zurück, drücken Sie die F5-Taste oder klicken Sie auf Ausführen. Wenn Sie dazu aufgefordert werden, wählen Sie die Suchspalte, die zu mittelnde Werte-Spalte und geben Sie den Suchwert ein. Das Makro zeigt den berechneten Durchschnitt in einem Meldungsfeld an.

Praktische Tipps und Vorsichtsmaßnahmen: Stellen Sie sicher, dass Ihre Such- und Werte-Spalten die gleiche Anzahl von Zeilen haben und es keine leeren Zeilen innerhalb der ausgewählten Bereiche gibt. Einträge mit nicht-numerischen Werten in der Zielspalte werden ignoriert. Passen Sie für die beste Automatisierung Namensbereiche oder Makrologik bei Bedarf an Ihr Arbeitsblattlayout an.

Fehlerbehebung: Wenn Sie "Keine Übereinstimmungen gefunden" sehen, überprüfen Sie auf führende/nachfolgende Leerzeichen oder Inkonsistenzen bei Datentypen in Ihrer Suchspalte. Stellen Sie sicher, dass Makros zur Ausführung aktiviert sind.

Durchschnittliche/Verbund-Jahreswachstumsrate in Excel berechnen

Gleitender/Rollierender Durchschnitt in Excel berechnen

Durchschnitt pro Tag/Monat/Quartal/Stunde mit Pivot-Tabelle in Excel