consiglio per calcolo media filtrata

[Guido Cei]

Salve a tutti/e,

avrei necessità di effettuare una semplice media aritmetica di una serie di valori associati a dei codici.
Nel caso alcuni di questi codici fossero identici dovrei prima effettuare la semisomma (o comunque la media aritmetica) dei valori corrispondenti al medesimo codice e poi effettuare la media complessiva (utilizzando come denominatore la numerosità del blocco di dati sintetizzato).

Ho impostato una colonna di "filtro" per arrivare ai dati sui quali calcolare la media che desidererei ottenere utilizzando la seguente formula:

=SE(E(A2<>A1;A2<>A3);B2;SE(E(A2=A3;A2<>A1);SOMMA.SE($A$2:$A$39;A2;$B$2:$B$39)/CONTA.SE($A$2:$A$39;A2);""))

vorrei sapere se per caso esiste una via più efficiente di realizzare il medesimo risultato (ossia calcolare il tutto in un'unica cella) e se da occhi più esperti riscontrate degli eventuali errori che la procedura in questione può generare (dalle prove che ho fatto sembrerebbe tornare).

Grazie ancora per l'aiuto.

qui il file di es:
https://1drv.ms/x/s!AkByiT4K8MzxyEkaBiX1iQVphqvu

[casanmaner]

Premetto che una soluzione in un'unica cella non riesco a vederla, ma magari chi più bravo di me invece la vede, nella tua formula vedo un problema nel caso in cui due codici uguali siano tra loro posizionati in righe differenti intervallate da altri codici.
Ma magari è un'evenienza che non si verifica nel tuo caso concreto.

[Guido Cei]

Grazie della risposta!

il problema che citi è giusto e avevo provato a risolverlo ma senza grandi risultati.
E' un'evenienza che non dovrebbe capitare ma della quale non sono del tutto sicuro.
Avevo provato a ragionare sul somma.se() / conta.se() ma non riesco a trovare un passaggio con il quale evitare il riconteggio di un valore.
L'unica è ordinando le celle dei codici perchè siano consequenziali.

[casanmaner]

I tuoi dati partono da A2.
Prova quindi questa possibile soluzione:
=SE(CONTA.SE($A$1:A1;A2)=0;SOMMA.SE($A$2:$A$40;A2;$B$2:$B$40)/CONTA.SE($A$2:$A$40;A2);"")

[casanmaner]

O meglio, rimanendo nell'ambito dell'intervallo,
=SE(CONTA.SE($A$2:A2;A2)=1;SOMMA.SE($A$2:$A$40;A2;$B$2:$B$40)/CONTA.SE($A$2:$A$40;A2);"")

[casanmaner]

Come dicevo io non sono così bravo da riuscire a vedere un'unica formula utilizzando le funzioni native di Excel e quindi provo tramite una FDU (funzione definita dall'utente) tramite VBA e con l'ausilio delle Dictionary.
Il file dovrà essere salvato in formato xlsm e in un modulo standard del progetto VBA.
Vedi qui un file di esempio (con i tuoi dati originari):
https://www.dropbox.com/s/eia7y7qiodk0r1p/consiglio%20per%20calcolo%20media%20filtrata.xlsm?dl=0

Questa la FDU presente nel Modulo1
'---
Function MediaSenzaRipetizioni(IntervalloCodici As Range, IntervalloValori As Range) As Double
Dim i As Long
Dim arrCodici As Variant, arrValori As Variant
Dim dictValoriCodici As Object, dictContaCodici As Object
Dim strCod As String
Dim valCod As Double
Dim objDict As Variant
Dim strKey As String
Dim SommaMediaCodiciUnivoci As Double
Dim bArrVerticale As Boolean
Dim idArr As Long
Set dictValoriCodici = CreateObject("Scripting.Dictionary")
Set dictContaCodici = CreateObject("Scripting.Dictionary")
arrCodici = IntervalloCodici.Value
arrValori = IntervalloValori.Value
If UBound(arrCodici, 1) > UBound(arrCodici, 2) Then
bArrVerticale = True
idArr = 1
Else
bArrVerticale = False
idArr = 2
End If
For i = LBound(arrCodici, idArr) To UBound(arrCodici, idArr)
If bArrVerticale Then
strCod = arrCodici(i, 1)
valCod = arrValori(i, 1)
Else
strCod = arrCodici(1, i)
valCod = arrValori(1, i)
End If
If Not dictValoriCodici.Exists(strCod) Then
dictValoriCodici.Add strCod, valCod
dictContaCodici.Add strCod, 1
Else
dictValoriCodici(strCod) = dictValoriCodici(strCod) + valCod
dictContaCodici(strCod) = dictContaCodici(strCod) + 1
End If
Next i
For Each objDict In dictValoriCodici.keys
SommaMediaCodiciUnivoci = SommaMediaCodiciUnivoci + _
dictValoriCodici(objDict) / dictContaCodici(objDict)
Next objDict
MediaSenzaRipetizioni = SommaMediaCodiciUnivoci / dictValoriCodici.Count
End Function
'---

L'argomento IntervalloCodici rappresenta l'intervallo di celle dove sono presenti i codici.
L'argomento IntervalloValori rappresenta l'intervallo di celle dove sono presenti i valori numerici.
Gli intervalli, composti ciascuno da almeno due celle, devono avere identica dimensione e entrambi devono essere o intervalli verticali (1 colonna per tot righe) o intervalli orizzontali (1 riga per tot colonne).
Meglio di questo mi sa che non sono in grado di fare :)
Prova a vedere come funziona se vuoi.

[Guido Cei]

Fantastico, mi sembrano ottime entrambe le soluzioni.
Grazie per il tempo speso per aiutarmi e consigliarmi.

Cerco di capire meglio il funzionamento della FDU per riuscire ad essere più autonomo in futuro per eventuali modifiche/integrazioni.

Buona giornata.