Come calcolare la mediana se ci sono più condizioni in Excel?

Calcolare la mediana di un set di dati in Excel è un'operazione frequentemente richiesta nell'analisi e nella creazione di report. Sebbene trovare la mediana per un intervallo semplice possa essere eseguito rapidamente utilizzando le funzioni standard di Excel, spesso si presentano situazioni in cui è necessario ottenere il valore mediano solo dai dati che soddisfano criteri specifici multipli – ad esempio, trovare l'importo mediano delle vendite per un determinato prodotto in una data specifica all'interno di un grande set di dati. Gestire operazioni complesse e condizionali come queste con sole funzioni tradizionali può essere difficile. In questo tutorial, introdurremo diverse soluzioni pratiche per calcolare la mediana con più condizioni in Excel, esplorando sia approcci basati su formule che sull'automazione utilizzando VBA per esigenze avanzate.

• Calcola la mediana se soddisfa più condizioni
• Codice VBA - Calcola la mediana con più condizioni

Calcola la mediana se soddisfa più condizioni

Supponiamo di avere un intervallo di dati come mostrato di seguito, e il tuo compito è determinare il valore mediano che soddisfa due criteri: ad esempio, determinare il valore mediano della colonna B dove la colonna A ha il valore "a" e la colonna C ha la data "2-gen". Questo scenario è particolarmente comune nei report di vendita, nei risultati dei test della classe e in altre analisi di dati aziendali o accademici dove è necessario filtrare per più categorie.

Per chiarezza, prepariamo il foglio di lavoro come segue: nel tuo foglio di Excel, inserisci i tuoi criteri e crea un layout simile all'immagine qui sotto. Qui, la colonna E elenca i criteri per la colonna A, e la riga 1 delle colonne F e successive rappresenta i criteri di data dalla colonna C.

Per calcolare la mediana che soddisfa più criteri, puoi utilizzare una formula matrice che sfrutta le funzioni MEDIANA e SE per costruire un elenco filtrato di valori in base ai tuoi criteri. Ecco come farlo:

1. Fai clic sulla cella F2, dove desideri che appaia il risultato della mediana, e inserisci la seguente formula:

=MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12)))

Questa formula funziona controllando, per ogni riga, se il valore nella colonna A corrisponde alla condizione in E2 e se il valore nella colonna C corrisponde all'intestazione in F1. Se entrambe le condizioni sono soddisfatte, raccoglie il valore nella colonna B per il calcolo della mediana.

2. Dopo aver inserito la formula, premi Ctrl + Maiusc + Invio (non solo Invio), poiché questa è una formula matrice. Excel circonderà automaticamente la formula con parentesi graffe { } per indicare una formula matrice.

3. Trascina la maniglia di riempimento dall'angolo in basso a destra di F2 per copiare la formula in altre celle pertinenti dove sono richieste mediane in base a diverse condizioni, come mostrato di seguito:

Spiegazioni dei parametri e suggerimenti per l'uso: Nella formula, $A$2:$A$12 è l'intervallo contenente la prima condizione (come i nomi dei prodotti), $C$2:$C$12 è l'intervallo per la seconda condizione (come le date), e $B$2:$B$12 è l'intervallo contenente i valori numerici per i quali vuoi la mediana. Regola questi intervalli secondo necessità per il tuo foglio di lavoro. Usa sempre riferimenti assoluti ($ simboli) per assicurarti che gli intervalli non si spostino quando copi la formula.

Precauzioni: Se nessun valore soddisfa entrambe le condizioni, la formula restituirà un errore #NUM!. Per evitare confusione, puoi nidificare la formula all'interno di SE.ERRORE per restituire un valore vuoto o un messaggio personalizzato:

=IFERROR(MEDIAN(IF($A$2:$A$12=$E2,IF($C$2:$C$12=F$1,$B$2:$B$12))),"No match")

Assicurati che i tuoi dati non contengano celle vuote o valori non numerici nella colonna della mediana, poiché ciò potrebbe influenzare i risultati.

Questo approccio basato su formule è adatto quando hai condizioni relativamente semplici (tipicamente fino a due o tre criteri). È veloce da impostare e non richiede competenze di programmazione. Tuttavia, per filtri complessi con condizioni dinamiche o set di dati più grandi, mantenere o modificare le formule matrice può diventare complicato.

Codice VBA - Calcola la mediana con più condizioni

Per scenari in cui è necessario automatizzare il calcolo della mediana condizionale – come quando ci sono molte condizioni, set di dati grandi o i criteri stessi cambiano frequentemente – una soluzione VBA può offrire un'alternativa pratica. Utilizzando VBA, puoi creare una macro riutilizzabile che calcola la mediana in base a qualsiasi numero di condizioni. Le soluzioni basate su VBA sono particolarmente utili se vuoi semplificare analisi ripetitive o sviluppare processi personalizzati di Excel per report e dashboard.

Segui questi passaggi per utilizzare VBA per il calcolo della mediana condizionale:

1. Fai clic su Strumenti di sviluppo > Visual Basic. Si aprirà una nuova finestra Microsoft Visual Basic for Applications. Fai clic su Inserisci > Modulo, quindi incolla il codice seguente nel Modulo:

Sub ConditionalMedian()
    Dim DataRange As Range
    Dim CriteriaRange1 As Range
    Dim CriteriaRange2 As Range
    Dim OutputRange As Range
    Dim Criteria1 As Variant
    Dim Criteria2 As Variant
    Dim TempArr() As Double
    Dim i As Long
    Dim j As Long
    Dim count As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set DataRange = Application.InputBox("Select the range containing median values (e.g., B2:B12):", xTitleId, "", Type:=8)
    Set CriteriaRange1 = Application.InputBox("Select the first criteria range (e.g., A2:A12):", xTitleId, "", Type:=8)
    Criteria1 = Application.InputBox("Enter the first criteria value (e.g., a):", xTitleId, "", Type:=2)
    Set CriteriaRange2 = Application.InputBox("Select the second criteria range (e.g., C2:C12):", xTitleId, "", Type:=8)
    Criteria2 = Application.InputBox("Enter the second criteria value (e.g.,2-Jan):", xTitleId, "", Type:=2)
    Set OutputRange = Application.InputBox("Select the cell to output the result:", xTitleId, "", Type:=8)
    
    count = 0
    For i = 1 To DataRange.Rows.count
        If StrComp(CStr(CriteriaRange1.Cells(i, 1).Value), CStr(Criteria1), vbTextCompare) = 0 And _
           CStr(CriteriaRange2.Cells(i, 1).Value) = CStr(Criteria2) Then
            ReDim Preserve TempArr(count)
            TempArr(count) = DataRange.Cells(i, 1).Value
            count = count + 1
        End If
    Next i
    
    If count = 0 Then
        OutputRange.Value = "No match"
    Else
        Call QuickSort(TempArr, LBound(TempArr), UBound(TempArr))
        If count Mod 2 = 1 Then
            OutputRange.Value = TempArr(count \ 2)
        Else
            OutputRange.Value = (TempArr(count \ 2) + TempArr(count \ 2 - 1)) / 2
        End If
    End If
End Sub

Sub QuickSort(arr() As Double, first As Long, last As Long)
    Dim i As Long
    Dim j As Long
    Dim pivot As Double
    Dim temp As Double
    
    i = first
    j = last
    pivot = arr((first + last) \ 2)
    
    Do While i <= j
        Do While arr(i) < pivot
            i = i + 1
        Loop
        
        Do While arr(j) > pivot
            j = j - 1
        Loop
        
        If i <= j Then
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            i = i + 1
            j = j - 1
        End If
    Loop
    
    If first < j Then
        QuickSort arr, first, j
    End If
    
    If i < last Then
        QuickSort arr, i, last
    End If
End Sub

2. Fai clic sul pulsante (o premi F5) per eseguire il codice. Ti verrà chiesto di selezionare ciascuno degli intervalli necessari e di inserire i tuoi criteri. Dopo aver completato le richieste, il risultato (la mediana che soddisfa tutti i criteri) verrà visualizzato nella cella di destinazione che hai specificato.

Questa macro ti consente di selezionare flessibilmente l'intervallo di valori, gli intervalli dei criteri, i valori dei criteri e dove visualizzare il risultato ogni volta che viene eseguita. Puoi anche adattare facilmente il codice per includere più condizioni, se necessario.

Suggerimenti e risoluzione dei problemi: Quando utilizzi soluzioni VBA, assicurati che tutti gli intervalli selezionati abbiano lunghezze uguali e che i criteri corrispondano al tipo di dati e alla formattazione corretti (ad esempio, testo vs. date). Se nessun valore soddisfa i criteri, l'output visualizzerà "Nessuna corrispondenza". Per la massima stabilità, salva la cartella di lavoro prima di eseguire la macro e abilita sempre le macro quando richiesto. Questa soluzione VBA è adatta per utenti familiari con le impostazioni di sicurezza delle macro e per l'uso in flussi di lavoro Excel automatizzati.

In sintesi, l'approccio VBA automatizza calcoli complessi della mediana che sono ingombranti o difficili da eseguire con sole formule. È particolarmente adatto quando si ha a che fare con condizioni variabili, ricalcoli frequenti e set di dati di grandi dimensioni.

Articoli correlati:

• Calcola la mediana in un intervallo