Come calcolare la media dei risultati restituiti da più funzioni CERCA.VERT in Excel?

Quando devi trovare e calcolare la media di diversi valori associati allo stesso elemento di ricerca, utilizzare una formula diretta è uno dei metodi più rapidi e flessibili. La funzione MEDIA.SE o una formula matriciale gestisce facilmente questa operazione senza dover 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: otterrai immediatamente la media di tutti i valori nella colonna C per cui il valore corrispondente nella colonna A coincide con il tuo valore di ricerca inserito nella cella E2. Guarda l’illustrazione qui sotto:

Spiegazione dei parametri e suggerimenti:

• A1:A24: L'intervallo che contiene il tuo intervallo di valori da cercare.
• E2: Il valore specifico che vuoi cercare.
• C1:C24: L'intervallo da cui desideri calcolare la media dei valori corrispondenti.

Approccio alternativo (per utenti esperti con formule matriciali):

Inserisci la seguente formula in una cella vuota e premi contemporaneamente Ctrl+Maiusc+Invioper confermare:

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

Le formule matriciali elaborano ogni confronto singolarmente, un vantaggio nelle versioni di Excel che non supportano gli array dinamici. Assicurati con attenzione che gli intervalli abbiano esattamente la stessa dimensione per evitare errori.

Scenari pratici e note:
- Ideale per set di dati non filtrati e con esigenze di ricerca semplici.
- Se uno degli intervalli contiene celle vuote, queste vengono ignorate nel calcolo della media.
- In tabelle pivot o quando si aggiungono nuovi dati, valuta l’uso di riferimenti strutturati a tabelle per formule più affidabili.
- Fai attenzione ai disallineamenti accidentali negli intervalli di celle: una causa frequente di medie errate o errori.

Se hai spesso bisogno di riepilogare e aggregare dati basati su valori duplicati, Kutools per Excel offre una soluzione pratica grazie alla sua utility Unione avanzata righe. Questo strumento combina o calcola in un solo passaggio valori come la media, la somma o il conteggio per record corrispondenti, risultando ideale per set di dati di grandi dimensioni o report periodici.

1. Evidenzia l'intervallo della tua tabella di dati, includendo sia la colonna di ricerca che i valori da mediare. Quindi vai su Kutools > Contenuto > Unione avanzata righe. Vedi lo screenshot:

2. Nella finestra di dialogo visualizzata:

• Seleziona la colonna con il tuo intervallo di valori da cercare e fai clic su Chiave primaria.
• Seleziona la colonna con i tuoi valori di destinazione, quindi fai clic su Calcola > Media.
• Imposta, se necessario, regole di combinazione o calcolo per altre colonne—ad esempio unendo testo con virgole oppure applicando somma, massimo o minimo.

3. Fai clic su Ok per applicare le impostazioni.

Le righe con intervalli di valori di ricerca duplicati sono ora unite e i valori nella colonna designata vengono automaticamente mediati per ciascun valore di ricerca univoco, risultando particolarmente utile per preparare report di sintesi o ridurre i dati.

Suggerimento pratico: utilizzare Unione avanzata righe riduce i calcoli manuali e il rischio di errori. Questo strumento è perfetto per chi elabora regolarmente dati con intervalli di valori di ricerca ricorrenti e desidera sintesi immediate. Prima di combinare, assicurati sempre che le colonne corrette siano assegnate, soprattutto se la struttura dei dati è cambiata.

Demo: calcola la media di più risultati di CERCA.VERT con Kutools per Excel

Le tabelle pivot offrono un approccio dinamico e visivo per riepilogare e analizzare i dati. Grazie a una tabella pivot, puoi raggruppare automaticamente le voci in base al loro valore di ricerca e visualizzare la media di una colonna di destinazione per ciascun gruppo, ottenendo un riepilogo interattivo che si aggiorna non appena i dati cambiano.

Scenari in cui è più efficace: Questo approccio è particolarmente indicato quando serve un riepilogo complessivo per tutti i valori dell’intervallo di ricerca contemporaneamente, anziché concentrarsi su un singolo valore. Le tabelle pivot, inoltre, sono eccellenti per esplorare rapidamente i dati, generare report e presentare i risultati in un formato ordinabile ed espandibile.

Istruzioni:

• Seleziona l’intero set di dati, incluse le intestazioni.
• Vai su Inserisci > Tabella pivot > Da tabella o intervallo. Scegli se posizionare la tabella pivot in un nuovo foglio di lavoro o in uno esistente, in base alle tue esigenze.
• Nel riquadro Campi tabella pivot, trascina la colonna che contiene il tuo intervallo di valori da cercare nell'area Righe.
• Trascina la colonna di cui vuoi calcolare la media nell'area Valori. Fai clic sul campo dei valori, seleziona Impostazioni campo, quindi imposta il tipo di calcolo su Media.

Il risultato è una tabella di sintesi che mostra ogni valore di ricerca univoco insieme alla relativa media, calcolata sui dati associati. Puoi modificare in modo semplice il raggruppamento, applicare filtri o approfondire i dettagli quando necessario.

Vantaggi: Non richiede formule, supporta aggiornamenti dinamici ed è ideale per reportistica ed esplorazione dati.

Svantaggi: Richiede passaggi aggiuntivi per l'aggiornamento dopo aver modificato i dati, è meno adatto per estrarre direttamente un singolo valore all'interno di altre formule e la configurazione iniziale presuppone una conoscenza di base delle tabelle pivot.

Suggerimenti per la risoluzione dei problemi: Se i valori vengono visualizzati come conteggi o somme anziché come medie, controlla l’impostazione di calcolo del campo. Per ottenere risultati ottimali, assicurati che le colonne abbiano intestazioni appropriate e risolvi eventuali duplicati nei nomi delle colonne prima di creare la tabella pivot.

Per utenti avanzati e per chi gestisce dati soggetti a frequenti aggiornamenti, l’utilizzo di una macro VBA permette di automatizzare il calcolo della media per tutte le voci corrispondenti a un determinato valore di ricerca. Questo metodo analizza i dati alla ricerca di ogni corrispondenza e ne calcola automaticamente la media, risultando ideale per set di dati di grandi dimensioni o quando serve un flusso di lavoro ripetibile.

Scenari applicabili e note: VBA è la scelta ideale quando devi calcolare frequentemente la media, automatizzare report o adottare un approccio flessibile per gestire layout di dati non convenzionali. Le macro VBA offrono i migliori risultati se sei a tuo agio con l’abilitazione delle macro nella cartella di lavoro e hai bisogno di output personalizzati.

1. Vai alla scheda Sviluppo, scegli Visual Basic oppure premi i tasti Alt + F11 per aprire l'editor VBA, quindi 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, chiudere l'editor VBA. Per eseguire la macro, tornare a Excel e premere il tasto F5 oppure fare clic su Esegui. Quando richiesto, selezionare la colonna di ricerca, la colonna dei valori da mediare e inserire il valore da cercare: la macro visualizzerà immediatamente la media calcolata in una finestra di messaggio.

Suggerimenti pratici e precauzioni: Assicurati che le colonne di ricerca e dei valori abbiano lo stesso numero di righe e che non siano presenti righe vuote nelle aree selezionate. Le voci con valori non numerici nella colonna di destinazione verranno ignorate. Per un’automazione ottimale, adatta gli intervalli denominati o la logica della macro in base al layout del tuo foglio di lavoro.

Risoluzione dei problemi: Se viene visualizzato il messaggio «Nessuna corrispondenza trovata», controlla la presenza di spazi iniziali o finali e di incongruenze nei tipi di dati nella colonna di ricerca. Assicurati che le macro siano abilitate per l'esecuzione.

Calcolare la media o il tasso di crescita annuo composto in Excel

Calcolare la media mobile (rolling average) in Excel

Calcolare la media per giorno/mese/trimestre/ora con Tabella pivot in Excel