Ottimizzare formula excel 365

Question

Buongiorno,

ho una tabella che ha tre campi con circa 20.000 righe.

Le celle del primo campo contengono del testo separato da un punto e virgola mentre quelle del secondo campo dei valori numerici sempre separati da punto e virgola; infine il terzo campo solo valori numerici.

Con la seguente formula ottengo il risultato desiderato:

Volevo sapere se fosse possibile ottimizzare la formula, in quanto per ottenere il risultato devo attendere circa un minuto.

grazie.

Antonino

https://onedrive.live.com/personal/dd405b0d5a6097c5/_layouts/15/Doc.aspx?sourcedoc=%7B40CEA8E7-9A88-4B7C-B9ED-968FB0AF7007%7D&file=FormulaExcel36

LET(
SplitText;LAMBDA(r;A.COL(TESTO.SUCCESSIVO(";"&TESTO.PRECEDENTE(r&";";";";SEQUENZA(;MAX(LUNGHEZZA(r)));;;NON.DISP());";";-1);3)));
Codici;A2:A20001;
Valori;C2:C20001;
RAGGRUPPAPER(
SplitText(Codici);

SplitText(B2:B20001)*INDICE(Valori;CONFRONTA.X(SEQUENZA(SOMMA(LUNGHEZZA(Codici)-LUNGHEZZA(SOSTITUISCI(Codici;";";""))+1));SCAN(0;LUNGHEZZA(Codici)-LUNGHEZZA(SOSTITUISCI(Codici;";";""))+1;LAMBDA(a;x;a+x));1));

SOMMA;;0
)).

Answer 1

Ciao,

se ho capito qual è il risultato voluto, che dovrebbe essere la somma dei valori "pesati" per ciascun cliente del campo Valore, ti propongo l'utilizzo di Power Query che con il mio PC ottiene gli stessi valori presenti nel tuo file in un paio di secondi eseguendo l'aggiornamento della query.

Qui il file con una tabella con gli stessi tuoi dati e un tabella frutto della query:

https://www.dropbox.com/scl/fi/jpsf11cn4vzqugbvmrlz0/FormulaExcel365.xlsx?rlkey=d52ohev00uo8ja4b26497ke0i&dl=0

Questo il codice M della query (che fa riferimento alla Tabella1):

let

Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],

Formati = Table.TransformColumnTypes(Origine,{{"Cliente", type text}, {"Peso", type text}, {"Valore", type number}}),

SostituitoValori = Table.ReplaceValue(Formati,".",",",Replacer.ReplaceText,{"Peso"}),

SuddividiClienti = Table.ExpandListColumn(Table.TransformColumns(SostituitoValori, {{"Cliente", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Cliente"),

Indice = Table.AddIndexColumn(SuddividiClienti, "Indice", 0, 1, Int64.Type),

Mod = Table.AddColumn(Indice, "Mod", each Number.Mod([Indice],5)),

ListaPeso2 = Table.AddColumn(Mod, "Peso2", each List.Range( Text.Split([Peso],";") , [Mod] , 1)

),

EspansaListaPeso2 = Table.ExpandListColumn(ListaPeso2, "Peso2"),

FormatoPeso2 = Table.TransformColumnTypes(EspansaListaPeso2,{{"Peso2", type number}}),

ValoriPesati = Table.AddColumn(FormatoPeso2, "Valore Pesato", each [Valore]*[Peso2]),

RimosseColonne = Table.RemoveColumns(ValoriPesati,{"Peso", "Valore", "Indice", "Mod", "Peso2"}),

RaggruppatoClienti = Table.Group(RimosseColonne, {"Cliente"}, {{"Valore", each List.Sum([Valore Pesato]), type number}}),

OrdinatiClientiPerNome = Table.Sort(RaggruppatoClienti,{{"Cliente", Order.Ascending}})

in

OrdinatiClientiPerNome

Il tutto si basa sul fatto che, come da esempio, i nomitavi presenti siano sempre 5 con i relativi pesi.

Prova a vedere se ti può essere utile.

ciao

Answer 2

Prova il codice M qui sotto. Ho chiamato la tabella Table1.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Filtered = Table.SelectRows(Source, each [Cliente] <> null and [Peso] <> null),

AddIndex = Table.AddIndexColumn(Filtered, "RowID", 1, 1, Int64.Type),

AddClienteList = Table.AddColumn(AddIndex, "ClienteList", each Text.Split([Cliente], ";")),

AddPesoList = Table.AddColumn(AddClienteList, "PesoList", each List.Transform(Text.Split([Peso], ";"), Number.FromText)),

AddCombined = Table.AddColumn(AddPesoList, "Combined", each List.Zip({[ClienteList], [PesoList]})),

ExpandCombined = Table.ExpandListColumn(AddCombined, "Combined"),

ExtractCliente = Table.AddColumn(ExpandCombined, "ClienteNum", each Number.FromText([Combined]{0}), Int64.Type),

ExtractPeso = Table.AddColumn(ExtractCliente, "PesoNum", each [Combined]{1}, type number),

AddWeighted = Table.AddColumn(ExtractPeso, "Weighted", each [PesoNum] * [Valore], type number),

Grouped = Table.Group(AddWeighted, {"ClienteNum"}, {{"Total", each List.Sum([Weighted]), type number}}),

Sorted = Table.Sort(Grouped, {{"ClienteNum", Order.Ascending}}),

Renamed = Table.RenameColumns(Sorted, {{"ClienteNum", "Cliente"}})

in

Renamed

Per quanto riguarda la formula, l'esecuzione richiederà molto tempo, dato che hai 20.000 righe per una sola colonna. Di seguito ti fornisco una formula simile alla tua, che richiede anch'essa molto tempo per produrre i risultati attesi.

Per questo motivo, ritengo che Power Query tramite codice M sia la migliore alternativa.

=LET(

c; A2:A20001;

p; B2:B20001;

len; LUNGHEZZA(c)-LUNGHEZZA(SOSTITUISCI(c; ";"; ""))+1;

joinc; TOCOL(TESTO.DOPO(";" & TESTO.PRIMA(c & ";"; ";"; SEQUENZA(;;MAX(LUNGHEZZA(c)));;; VAL.NON.DISP()); ";";-1); 3);

joinp; TOCOL(TESTO.DOPO(";" & TESTO.PRIMA(p & ";"; ";"; SEQUENZA(;;MAX(LUNGHEZZA(p)));;; VAL.NON.DISP()); ";";-1); 3);--RAGGRUPA.PER(TESTO(joinc; "#000"); joinp*CONFRONTA(SEQUENZA(SOMMA(len)); SCANSIONE(0; len; LAMBDA(a;x; a+x)); 1); SOMMA;; 0))

Spero di esserti stato d'aiuto.

Buona giornata.

Answer 3

ciao

ho provato a scaricare il file ma

accesso negato

serve l'utorizzazione

Answer 4

Questa risposta è stata tradotta automaticamente. Pertanto, potrebbe contenere errori grammaticali o frasi insolite.

Ciao @Antonino Messina,

Grazie per aver contattato la Community di Domande e Risposte di Microsoft.

In base alla tua richiesta, potrei chiederti di condividere il file Excel in modo che io possa esaminare i dati e fornirti il ​​supporto più appropriato per aiutarti. Poiché potrebbe riguardare informazioni personali, puoi condividerlo tramite messaggio privato.

Ti preghiamo di comprendere che la nostra risposta iniziale non sempre risolve il problema immediatamente. Tuttavia, con il tuo aiuto e informazioni più dettagliate, possiamo lavorare insieme per trovare una soluzione.

Grazie per la comprensione e la collaborazione, non esitare a rispondere con i dettagli richiesti in modo che possiamo continuare ad assisterti.

Attendo con ansia una tua risposta!

---

Se la risposta è utile, clicca su "Accetta risposta" e votala. Se hai ulteriori domande su questa risposta, clicca su "Commenta".

Nota: segui i passaggi nella nostra documentazione per abilitare le notifiche e-mail se desideri ricevere la notifica e-mail relativa a questa discussione.