Come calcolare la media di più risultati di ricerca con Vlookup in Excel?

Quando è necessario trovare e fare la media di diversi valori associati allo stesso elemento di ricerca, utilizzare una formula diretta è uno dei modi più veloci e flessibili. La funzione MEDIA.SE o una formula matriciale gestisce facilmente questo compito senza creare colonne aggiuntive.

Inserisci la seguente formula in una cella vuota (ad esempio, F2):

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

Premi il tasto Invio dopo aver digitato la formula. Questo ti darà immediatamente la media di tutti i valori nella colonna C dove il valore corrispondente nella colonna A corrisponde al tuo valore di ricerca situato nella cella E2. Vedi l'illustrazione qui sotto:

Spiegazione dei parametri e suggerimenti:

• A1:A24: L'intervallo contenente i tuoi valori di ricerca.
• E2: Il valore specifico che desideri cercare.
• C1:C24: L'intervallo da cui desideri mediare i valori corrispondenti.

Approccio alternativo (per gli utenti a proprio agio con le formule matriciali):

Inserisci la seguente formula in una cella vuota e usa Ctrl + Maiusc + Invio per confermare:

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

Le formule matriciali elaborano ciascun confronto individualmente, il che è utile nelle versioni di Excel che non supportano array dinamici. Assicurati attentamente che gli intervalli abbiano esattamente le stesse dimensioni per evitare errori.

Scenari pratici e note:
- Ideale per set di dati che non sono filtrati e hanno bisogni di ricerca semplici.
- Se uno degli intervalli include celle vuote, queste verranno ignorate nel calcolo della media.
- Nelle tabelle dinamiche o quando si aggiungono dati, considera l'uso di riferimenti di tabella per formule più robuste.
- Fai attenzione agli errori accidentali di corrispondenza degli intervalli di celle, che sono una fonte comune di medie errate o errori.

Se hai spesso bisogno di riassumere e aggregare dati basati su duplicati, Kutools per Excel fornisce una soluzione pratica tramite la sua utilità Unione Avanzata Righe. Questo strumento può combinare o calcolare rapidamente valori come media, somma o conteggio per record corrispondenti in un unico passaggio, rendendolo altamente adatto per set di dati più grandi o report regolari.

1. Evidenzia l'intervallo della tua tabella dati, inclusa sia la colonna di ricerca che i valori da mediare. Poi vai su Kutools > Testo > Unione Avanzata Righe. Vedi lo screenshot:

2. Nella finestra di dialogo che appare:

• Seleziona la colonna con i tuoi valori di ricerca e fai clic su Chiave Principale.
• Scegli la colonna con i tuoi valori target, poi fai clic su Calcola > Media.
• Imposta regole di combinazione o calcolo per altre colonne se necessario - ad esempio combinando testo con virgole o applicando somma, max o min.

3. Fai clic su Ok per applicare le impostazioni.

Le righe con valori di ricerca duplicati sono ora unite e i valori nella colonna designata vengono automaticamente mediati per ogni valore di ricerca unico. Ciò è particolarmente utile per preparare report di riepilogo o condensare i dati.

Consiglio pratico: Utilizzare Unione Avanzata Righe riduce al minimo i calcoli manuali e il rischio di errori. Lo strumento è ideale per gli utenti che elaborano regolarmente dati con valori di ricerca ricorrenti e vogliono riepiloghi azionabili rapidamente. Controlla sempre che le colonne corrette siano assegnate prima di combinare, soprattutto se cambia la struttura dei dati.

Demo: calcola la media di più risultati di ricerca con Kutools per Excel

Le Tabelle Pivot offrono un approccio dinamico e visivo per riassumere e analizzare i dati. Utilizzando una Tabella Pivot, puoi raggruppare automaticamente le voci in base al loro valore di ricerca e visualizzare la media di una colonna target per ciascun gruppo, fornendo un riepilogo interattivo che si aggiorna man mano che i tuoi dati cambiano.

Scenari più efficaci: Questo approccio è adatto quando hai bisogno di un riepilogo generale per tutti i valori di ricerca contemporaneamente, piuttosto che concentrarti su un singolo valore di ricerca. Le Tabelle Pivot sono anche eccellenti per l'esplorazione rapida dei dati, la generazione di report e quando desideri presentare i risultati in un formato ordinabile ed espandibile.

Istruzioni:

• Seleziona l'intero set di dati, inclusi le intestazioni.
• Vai su Inserisci > Tabella Pivot > Da Tabella o Intervallo. Scegli di posizionare la Tabella Pivot su un nuovo foglio di lavoro o su uno esistente, secondo necessità.
• Nel pannello Campi Tabella Pivot, trascina la colonna contenente i tuoi valori di ricerca nell'area Righe.
• Trascina la colonna che desideri mediare nell'area Valori. Fai clic sul campo valore, seleziona Impostazioni Campo Valore, quindi imposta il tipo di calcolo su Media.

Questo produce una tabella di riepilogo che elenca ogni valore di ricerca unico con la sua media calcolata per i dati associati. Puoi facilmente modificare il raggruppamento, filtrare o approfondire i dettagli secondo necessità.

Vantaggi: Nessuna formula richiesta, supporta aggiornamenti dinamici, adatta per reporting ed esplorazione dei dati.

Svantaggi: Passaggi extra necessari per aggiornare dopo i cambiamenti dei dati, meno adatta per estrarre un singolo valore direttamente in altre formule, e la configurazione iniziale richiede una conoscenza di base delle Tabelle Pivot.

Suggerimenti per la risoluzione dei problemi: Se i valori appaiono come conteggi o somme invece di medie, controlla l'impostazione del calcolo del campo. Per i migliori risultati, assicurati che le colonne abbiano intestazioni appropriate e chiarisci eventuali nomi di colonne duplicati prima di creare la Tabella Pivot.

Per gli utenti avanzati e coloro che gestiscono dati che si aggiornano regolarmente, utilizzare una macro VBA consente di automatizzare il processo di calcolo della media tra tutte le voci che corrispondono a un valore di ricerca. Questo metodo scorre attraverso i tuoi dati per trovare ogni corrispondenza e calcolare la media, rendendolo adatto per set di dati grandi o quando hai bisogno di un flusso di lavoro ripetibile.

Scenari applicabili e note: VBA è ideale quando devi frequentemente eseguire il calcolo della media, vuoi automatizzare i report o hai bisogno di un approccio flessibile che possa essere personalizzato per layout di dati insoliti. Le macro VBA funzionano meglio quando sei a tuo agio nell'abilitare le macro nella tua cartella di lavoro e richiedi output personalizzati.

1. Vai alla scheda Sviluppatore, scegli Visual Basic o premi Alt + F11 per aprire l'editor VBA, poi fai clic su Inserisci > Modulo. Copia e incolla il codice seguente nel nuovo modulo:

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. Dopo aver incollato il codice, chiudi l'editor VBA. Per eseguire la macro, torna a Excel, premi il tasto F5, oppure fai clic su Esegui. Quando richiesto, seleziona la colonna di ricerca, la colonna dei valori da mediare e inserisci il valore di ricerca. La macro visualizzerà la media calcolata in una finestra di messaggio.

Suggerimenti pratici e precauzioni: Assicurati che le colonne di ricerca e valori abbiano lo stesso numero di righe e che non ci siano righe vuote all'interno delle aree selezionate. Le voci con valori non numerici nella colonna target verranno ignorate. Per la migliore automazione, regola gli intervalli denominati o la logica della macro secondo necessità per il layout del tuo foglio di lavoro.

Risoluzione dei problemi: Se incontri "Nessuna corrispondenza trovata", controlla gli spazi iniziali/finali o le incongruenze di tipo di dati nella tua colonna di ricerca. Assicurati che le macro siano abilitate per l'esecuzione.

Calcola la media/tasso di crescita annuale composto in Excel

Calcola la media mobile/media rotante in Excel

Media al giorno/mese/trimestre/ora con tabella pivot in Excel