Domanda Riportare dati da un foglio "Storico" ad altri due diversi fogli in modo che questi si compilino in automatico

[Ciamic]

Cari amici del forum Buona Giornata
Ho dei dati in un foglio "Storico" che voglio riportare in altri due diversi fogli in modo che questi si compilino in automatico. Ho scritto i risultati attesi nei fogli Monitoraggio e Plusvalenze. Vorrei che gli stessi dati si determinassero con delle formule preimpostate.
Ringrazio in anticipo quanti mi vorranno aiutare. Grazie

 

[ipolito]

ciao Michele
come mio solito il mio limite è la comprensione, ma consultando i dati per ottenere da storico a monitoraggio potresti usare P.Q per la tua versione,

let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Data", type date}, {"Asset", type text}, {"Quantità", Int64.Type}, {"Prezzo", Int64.Type}}),
    #"Rimosse prime righe" = Table.Skip(#"Modificato tipo",1),
    #"Filtrate righe" = Table.SelectRows(#"Rimosse prime righe", each [Data] < #date(2023, 1, 1)),
    #"Raggruppate righe" = Table.Group(#"Filtrate righe", {"Asset"}, {{"Quantità", each List.Sum([Quantità]), type nullable number}, {"Prezzo", each List.Max([Prezzo]), type nullable number}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Raggruppate righe", "Data", each "01/01/2023"),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Aggiunta colonna personalizzata",{{"Data", type date}}),
    #"Riordinate colonne" = Table.ReorderColumns(#"Modificato tipo1",{"Data", "Asset", "Quantità", "Prezzo"})
in
    #"Riordinate colonne"

let
    Origine = Excel.CurrentWorkbook(){[Name="Tabella1"]}[Content],
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Data", type date}, {"Asset", type text}, {"Quantità", Int64.Type}, {"Prezzo", Int64.Type}}),
    #"Rimosse prime righe" = Table.Skip(#"Modificato tipo",1),
    #"Filtrate righe" = Table.SelectRows(#"Rimosse prime righe", each [Data] >= #date(2023, 1, 1))
in
    #"Filtrate righe"

e l'accodamento finale

let
    Origine = Table.Combine({pre_2023, post_2023})
in
    Origine

nella query 1 isolo i dati della tabella1 che siano inferiori a 01/012023 e li raggruppo tenendo conto di sommare le quantità e mantenere il massimo del prezzo.
la query isolo i dati maggiori e uguali a 01/01/2023 lasciando invariato il resto e li accodo ,
ma sono sicuro che qualcuno altro riuscirà a capire esattamente ciò di cui hai di bisogno

 

[Terio]

potresti usare P.Q

Un consiglio: la pre elaborazione della tabella storicizzala in un oggetto che poi richiami solo per i filtri ed il raggruppamento, poi, come già hai fatto, le combini.
In questa maniera il caricamento e la trasformazione dei dati la fai una sola volta e non singolarmente per ognuna delle due query.

 

[ipolito]

la pre elaborazione della tabella storicizzala in un oggetto

ciao terio, si ottimo suggerimento ma bisogna ancora vedere se è la desiderata di michele C @Ciamic

 

[Terio]

se è la desiderata

Certo, ma valeva come consiglio a te

Come sviluppa la discussione è un altro posto di maniche

Ciao.

 

[ipolito]

a te

E ti ringrazio tantissimo

 

[Sgrubak]

In questa maniera il caricamento e la trasformazione dei dati la fai una sola volta e non singolarmente per ognuna delle due query.

Purtroppo PQ non ragiona così, nella "versione free". Riesegue tutto sempre. :( Per fare quello che intendi serve un flusso di dati. Paradossale a mio avviso, ma vero.

Di sicuro avere la medesima origine può essere un vantaggio in caso di modifiche, cosi chè siano centralizzate.
In quanto ad efficenza, no comment.

 

[Ciamic]

Saluto tutti gli intervenuti e li ringrazio per gli interventi.

come mio solito il mio limite è la comprensione,

Hai compreso benissimo il caso Nucio @ipolito Sei troppo modesto per le tue immense e reali potenzialità.
Come sai il PQ non è affatto il mio forte e preferirei sviluppare la discussione sulla ricerca di eventuali formule. In ogni caso se si rende impossibile ottenere il risultato con le formule, certamente la soluzione da te proposta con l'avvertenza di @Terio che saluto è da tenere assolutamente presente anche con la limitazione esposta da @Sgrubak che saluto.

 

[Terio]

Riesegue tutto sempre

Sicuro?
Io mi ero basato, tra le altre, su questa affermazione, mentre invece hai ragione sul fatto che di default l'elaborazione sarebbe parallela.

serve un flusso di dati.

Mi sono documentato e parrebbe sufficiente un Table.Buffer alla fine del caricamento di Tabella1 o disabilitare il caricamento parallelo dalle opzioni.
Fammi sapere se ho approfondito correttamente e che cosa ne pensi, ma grazie per lo spunto che, come ho scritto e memore di quanto avevo letto, davo per scontato.

Ciao.

 

[ipolito]

formule

Ciao Michele,
Col 2016 con formule servono alcune colonne di appoggio. Mentre con p.q. sono solo click

 

[Ciamic]

Come sviluppa la discussione è un altro posto di maniche

Secondo te è fattibile costruire formule senza l'ausilio di PQ?

 

[Terio]

Secondo te è fattibile costruire formule senza l'ausilio di PQ?

Si, anche se non è consigliabile e dobbiamo scindere la discussione in due e risolvere per ora, solo il foglio Monitoraggio:
B4
=SE(RIF.RIGA(B1)<=SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0));--("1/1/"&nmAnno);SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));INDICE(Storico!$B$4:$E$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)=nmAnno);RIF.RIGA(B1)-SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));RIF.COLONNA()-1);SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0))+SOMMA(1/CONTA.SE(Storico!$C$4:$C$42;Storico!$C$4:$C$42)));--("31/12/"&nmAnno);"")))
C4
=SE(RIF.RIGA(B1)<=SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0));INDICE(Storico!$C$4:$C$42;CONFRONTA(0;CONTA.SE($C$3:$C3;Storico!$C$4:$C$42)+(ANNO(Storico!$B$4:$B$42)>=nmAnno);0));SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));INDICE(Storico!$B$4:$E$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)=nmAnno);RIF.RIGA(B1)-SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));RIF.COLONNA()-1);SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0))+SOMMA(1/CONTA.SE(Storico!$C$4:$C$42;Storico!$C$4:$C$42)));INDICE(Storico!$C$4:$C$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)>nmAnno);CONTA.SE(B$3:B4;B4)));"")))
D4
=SE(RIF.RIGA(B1)<=SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0));SOMMA.PIÙ.SE(Storico!$D$4:$D$42;Storico!$C$4:$C$42;C4;Storico!$B$4:$B$42;"<1/1/"&nmAnno);SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));INDICE(Storico!$B$4:$E$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)=nmAnno);RIF.RIGA(B1)-SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));RIF.COLONNA()-1);""))
E4
=SE(RIF.RIGA(B1)<=SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0));MAX(SE((Storico!$C$4:$C$42=C4)*(ANNO(Storico!$B$4:$B$42)<nmAnno);Storico!$E$4:$E$42));SE(RIF.RIGA(B1)<=(SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno))+SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));INDICE(Storico!$B$4:$E$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)=nmAnno);RIF.RIGA(B1)-SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));RIF.COLONNA()-1);SE(B4="";"";MAX(SE((Storico!$C$4:$C$42=C4)*(ANNO(Storico!$B$4:$B$42)>nmAnno);Storico!$E$4:$E$42)))))
sono matriciali e da tirare in basso fino ad avere celle vuote.

Sicuramente saranno semplificabili, ma rimangono abbastanza complesse, anche se in buona parte ripetitive. Non ti nascondo che stavolta la spiegazione potrebbe essere prolissa.

Ciao.

 

[Ciamic]

@Terio nel ringraziarti di questo immane lavoro di dò riscontro che le formule funzionano per il foglio monitoraggio. Poi sono da costruire quasi allo stesso modo per il foglio plusvalenze. Sto provando a capirci qualcosa ma è un lavoro titanico e seguendo il consiglio di @ipolito sto provando a sezionare la formula in colonne di appoggio per poi riassemblare il tutto. Non so se è la strada giusta; poichè ho spezzato per la formula in colonna B di monitoraggio in tre formule che compongono il se iniziale nelle sue componenti della condizione, lato vero e lato falso. La formula mi restituisce solo la testa e la coda dei valori (quelli per intenderci in verde), restituendo un valore di errore #RIF! per tutti gli altri valori interni. Questo primo tentativo è andato fallito

 

[Terio]

dò riscontro che le formule funzionano per il foglio monitoraggio

Grazie del riscontro

sono da costruire quasi allo stesso modo per il foglio plusvalenze.

Se non riesci lo deleghiamo ad un'altra discussione.

Sto provando a capirci qualcosa ma è un lavoro titanico

Immagino, ti do solo qualche spunto di riflessione:

SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0))

questa conta gli univoci per gli anni precedenti, l'ho utilizzata per marcare il primo settore verde che contiene i dati di partenza

SOMMA(--(ANNO(Storico!$B$4:$B$42)=nmAnno)

questa individua le righe che equivalgono alla parte in giallo

SOMMA(1/CONTA.SE(Storico!$C$4:$C$42;Storico!$C$4:$C$42))

questa, invece, determina il numero di univoci che si troveranno nelle ultime righe verdi

INDICE(Storico!$C$4:$C$42;CONFRONTA(0;CONTA.SE($C$3:$C3;Storico!$C$4:$C$42)+(ANNO(Storico!$B$4:$B$42)>=nmAnno);0))

la formula è un'evoluzione della classica utilizzata per estrarre gli univoci, modificata per tenere conto dei soli prodotti dell'anno precedente

INDICE(Storico!$B$4:$E$42;AGGREGA(15;6;RIF.RIGA($1:$1000)/(ANNO(Storico!$B$4:$B$42)=nmAnno);RIF.RIGA(B1)-SOMMA(--(FREQUENZA(SE(ANNO(Storico!$B$4:$B$42)<nmAnno;CONFRONTA(Storico!$C$4:$C$42;Storico!$C$4:$C$42;0));RIF.RIGA($1:$100))>0)));RIF.COLONNA()-1)

tieni conto che per questa, qui puoi leggere una descrizione accurata di come opera, con la particolarità che l'indice di estrazione deve restituire un parziale indice tenendo conto del settore giallo in cui si trova (vedi la parte evidenziata), oltre al fatto che anche la colonna viene recuperata tenendo conto che hai la A vuota.

SE(B4="";"";MAX(SE((Storico!$C$4:$C$42=C4)*(ANNO(Storico!$B$4:$B$42)>nmAnno);Storico!$E$4:$E$42)))

infine il prezzo per l'anno seguente, ricondotto all'ultimo dell'anno, l'ho recuperato con MAX avendo l'accortezza di lasciare al suo interno la matrice di valori filtrata per gli anni superiori a quello in esame (nmAnno) e per il prodotto presente nella colonna C.

Spero che queste indicazioni ti siano di ausilio, come vedi la lunghezza delle formule è conseguenza di parecchie ripetizioni.

Non so se è la strada giusta

La strada giusta è quella che riesci a percorrere, ma non sempre è la più semplice, per cui se ti servono spiegazioni puntuali chiedi pure.

Ciao.
edit
Per curiosità ho chiesto a Copilot di snellire le formule, ma ha risposto che sono troppo complesse e sfruttano le best practices per estrarre i valori corretti, ha aggiunto che le colonne di aiuto sposterebbero il problema altrove, senza risolverlo, ma, aggiungo, potrebbero essere un compromesso per aiutarti a capirle meglio.

Anticipazione (Spoiler): La differenza con il 365

Formula unica in B4
=LET(anni;ANNO(Storico!B4:B42);I;ESCLUDI(RAGGRUPPAPER(Storico!C4:C42;Storico!D4:E42;STACK.ORIZ(SOMMA;MAX);;0;;anni<nmAnno);1);f;FILTRO(Storico!B4:E42;anni=nmAnno);III;ESCLUDI(RAGGRUPPAPER(Storico!C4:C42;Storico!D4:E42;STACK.ORIZ(CONCAT;MAX);;0;;anni>nmAnno);1);STACK.VERT(STACK.ORIZ(INDICE(--("1/1/"&nmAnno);SEQUENZA(RIGHE(I);;;0));I);f;STACK.ORIZ(INDICE(--("31/12/"&nmAnno);SEQUENZA(RIGHE(III);;;0));III)))
volendo si può provare su Excel web

 

[ipolito]

Per curiosità ho chiesto a Copilot

Anche io ho chiesto a chatgpt mostrando le tue formule. Eh niente mi ha chiesto se appena puoi gliele spieghi

 

[alfrimpa]

come mio solito il mio limite è la comprensione

Per questo, oltre a mettere il risultato atteso, occorrerebbe che gli utenti spiegassero come questo si determina perché non tutti sono in grado di capirlo a colpo d’occhio.