Risolto Quantità consumata in un intervallo di tempo

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
Salve a tutti, avrei una domanda.
Sto cercando di calcolare le quantità consumate in un dato intervallo di tempo.

Nel file che vi riporto sotto, nel foglio "SPESA" vado ad inserire il prodotto che acquisto, la data e la quantità in kg o l a seconda del prodotto.

Nel foglio "CIBO" vorrei calcolare quanto consumo mediamente in una settimana ed in un mese di quel determinato prodotto facendo un calcolo tra le date e le quantità inserite nel foglio "SPESA".
L'idea è quella che quando inserisco il nome del prodotto, si calcola la distanza in giorni tra un acquisto e l'altro e la quantità comprata, quindi man mano la media si aggiorna in modo da avere ad esempio che consumo 1kg di pasta alla settimana, 0,86kg di pomodori, ecc

Qualcuno sa aiutarmi?
Non riesco a trovare un modo...

 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Allora, prima di tutto ti ho sistema un po di cose, ho messo nel foglio SPESE Convalide dati per i prodotti, invece per la colonna Data ho usato la Convalida dati con convalida per data, così basta che fai un doppio clic per ottenere un Datepicker (calendario virtuale).

"Nel foglio "CIBO" vorrei calcolare quanto consumo mediamente in una settimana ed in un mese di quel determinato prodotto facendo un calcolo tra le date e le quantità inserite nel foglio "SPESA"."

Non ho capito bene, ma in sostanza credo che se al 01/01/2021 acquisti un prodotto, come fai la comparazione?
Dovrai avere una colonna in cui inserirai la data del prodotto quando è finito (vedi copia di Spesa) ad esempio 11/01/2021 a questo punto avrai un conteggio esatto (sono passati 11 giorni) quantità / giorni passati = CG * 7 avrai il consumo in 7 giorni.
Come hai fatto tu non va bene, ad esempio se poi lo riacquisto 10 giorni dopo averlo usato? E in ogni modo la comparazione diventerebbe troppo difficile (se avessi 20 aceti inseriti come fai la comparazione? Come farebbe il computer a sapere quale dei 20 è quello che stai usando?)

Così invece con la colonna in più puoi inserire i tuoi prodotti quanti ne vuoi, e avere anche una cronologia dei prodotti acquistati (puoi anche mettere prezzo/supermercato...)

Quindi in pratica:

Esempio:
acquisto una bottiglia di aceto da 1,5 litri in data 01/01 e la finisco in data 11/01 sono passati 11 giorni. Quindi 1.5 / 11 = 0,14 (che corrisponde al consumo giornaliero) * 7 = 0,98 litri / settimana e al mese 4,2 litri.

Poi un giorno quando acquisterai lo stesso prodotto avrai una data diversa, fintanto non inserisci la data fine bottiglia, lui ti mantiene il dato precedente.

Mettiamo che stavolta hai acquistato una bottiglia da 1 litro, e l'hai consumata però in 15 giorni = avrai stavolta una media di 0,47 e circa 2 litri mese

Quindi ti ho sviluppato così le formule nell'altro foglio in Copia di CIBO.
=SE.ERRORE(filter('Copia di SPESA'!$D:$D;'Copia di SPESA'!$C:$C=GRANDE(filter('Copia di SPESA'!$C:$C;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>"");1);'Copia di SPESA'!$A:$A=$A2)/(GRANDE(filter('Copia di SPESA'!$C:$C;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>"");1)-GRANDE((filter('Copia di SPESA'!$B:$B;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>""));1)+1)*7)

Per 30 giorni
=SE.ERRORE(filter('Copia di SPESA'!$D:$D;'Copia di SPESA'!$C:$C=GRANDE(filter('Copia di SPESA'!$C:$C;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>"");1);'Copia di SPESA'!$A:$A=$A2)/(GRANDE(filter('Copia di SPESA'!$C:$C;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>"");1)-GRANDE((filter('Copia di SPESA'!$B:$B;'Copia di SPESA'!$A:$A=$A2;'Copia di SPESA'!$C:$C<>""));1)+1)*30)

In pratica ti va a cercare il valore massimo di una data quando il prodotto è finito di uno specifico prodotto e cerca il corrispettivo quantitativo, e lo divide per la differenza giorni (finito - acquisto) e poi moltiplica per 7 e 30 Saluto_saluto
 
Ultima modifica:
  • Like
Reactions: Giantn

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
Ciao Andrea, grazie mille per la risposta.

Effettivamente così è più accurato, anche se mi richiederebbe di aggiornare il file più spesso.
Quello che volevo creare io era una media settimanale e mensile che si aggiornava volta per volta.
Ovviamente per darmi un dato che abbia un senso, devo aver comprato almeno due volte lo stesso prodotto, quindi quello che mi dice prima è indifferente, può restituire anche errore.

Invece a quanto ho capito tu hai inserito il massimo dei dati precedenti, non la media. Giusto?

Io volevo la media perchè se ad esempio fra 3 mesi, le mie abitudini cambiano, posso tenerne traccia, invece così va a prendere sempre il massimo. Ho capito bene?

Grazie ancora 😊
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
No, preleva il dato della data più recente, ecco perché uso Grande...
Per la media devi fare un range di date da > a che calcoli gli ultimi 3 mesi con la data in vigore e il totale consumato....in questo caso bisogna un po studiarci...ma ringrazio sempre che utilizzo Sheets :felice: che è più facile da usare
 
  • Like
Reactions: Giantn

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
No, preleva il dato della data più recente, ecco perché uso Grande...
Per la media devi fare un range di date da > a che calcoli gli ultimi 3 mesi con la data in vigore e il totale consumato....in questo caso bisogna un po studiarci...ma ringrazio sempre che utilizzo Sheets :felice: che è più facile da usare
Non so se sono in grado di farlo :LOL: .
E' la prima volta che mi servono determinate formule ed è la prima volta che provo a fare una cosa così complessa.
A dire la verità non pensavo potesse essere così complessa, per cui ti capisco se non hai voglia o tempo di provarci.

Ti ringrazio in ogni caso 😊
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Non so se ho capito allora, era più semplice, tu vuoi che quando inserisci un prodotto in data 10/01/2021, e l'ultimo prodotto esempio 16/02/2021 ti sommi il totale dei prodotti acquistati, divida per i giorni dal primo prodotto all'ultimo e calcoli su 7 giorni? Come ho fatto ad esempio con SPESA3 e CIBO3?
in questo caso la formula usata è questa
=(SOMMA(filter(SPESA3!C:C;SPESA3!A:A=A2))/DATA.DIFF(MINIFS(SPESA3!B:B;SPESA3!A:A;A2);MAXIFS(SPESA3!B:B;SPESA3!A:A;A2);"D"))*7 e *30
 

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
In questo caso ho consumato 4 litri di aceto dal 10/1/2021 al 16/2/2021, quindi in 37 giorni 4 litri, questo vuol dire che in 7 giorni ne ho consumati 075l e in 30 giorni 3,24.

Poi il 16 compro altri 2 litri che supponiamo di consumare il 25 (data in cui comprerò una nuova confezione.
Adesso ho consumato 6 litri dal 10/1/2021 al 25/2/2021 quindi in 46 giorni 6 litri, questo vuol dire che in 7 giorni ne consumo 0,91l e in 30 giorni 3,91.

Spero ora sia tutto più chiaro
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Allora è ok, perfetto, funziona alla grande!!!! Era più semplice così, avevo capito una differenza negli ultimi 7 giorni e 30 giorni, e non su tutte le date...perfetto direi che ci siamo
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Ho fatto per il momento, vedi se ti va bene

Ho usato questa formula =SE.ERRORE(filter(SPESA3!C:C;SPESA3!A:A=A2;SPESA3!B:B=GRANDE(filter(SPESA3!B:B;SPESA3!A:A=A2);1))/DATA.DIFF(GRANDE(filter(SPESA3!B:B;SPESA3!A:A=A2);2);GRANDE(filter(SPESA3!B:B;SPESA3!A:A=A2);1);"D")*7)
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Con questa formula, penso abbiamo risolto :batticinque:...leggermente lunghina...Muoio_muoio meno male che c'è Sheets, altrimenti non saprei davvero come si poteva fare..ti ho aggiunto delle colonne con il totale giorni media consumi/giorno
=SE.ERRORE((sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1));1)-(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1))*(CERCA.VERT(sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B<=(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1));1;;1;FALSO); filter(SPESA_AG!$B:$C;SPESA_AG!$A:$A=$A2);2;0)/(sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1));1)-sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B<=(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1));1;;1;FALSO)))+SE.ERRORE(SOMMA(filter(SPESA_AG!$C:$C;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B<GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1);SPESA_AG!$B:$B>=sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=(GRANDE(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2);1)-B$1));1)))))
 
  • Like
Reactions: Giantn

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
Grazie Andrea. In realtà non funziona perchè ho provato a cambiare i dati che avevi impostato tu ma nulla. Comunque non importa.
Ho capito che quello che volevo fare è impossibile, quindi chiuderò qui il topic assegnandoti la miglior risposta per ringraziarti dell'enorme pazienza e disponibilità che hai avuto.

Grazie ancora.
Un abbraccio!
 
Ultima modifica:

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
:occhispalancati: :dubbioso:a me funziona alla grande, sopra nelle varie colonne puoi inserire un valore e lui dovrebbe darti la media. Ma se i giorni come intervallo non trova delle date in quel parametro (dalla più recente alla meno) ovviamente non restituisce valori...cmq va beh sono qua ancora Saluto_saluto
 

Giantn

Nuovo utente
10 Gennaio 2021
9
1
Google sheets
0
:occhispalancati: :dubbioso:a me funziona alla grande, sopra nelle varie colonne puoi inserire un valore e lui dovrebbe darti la media. Ma se i giorni come intervallo non trova delle date in quel parametro (dalla più recente alla meno) ovviamente non restituisce valori...cmq va beh sono qua ancora Saluto_saluto
Se vuoi richiedi l'accesso al file così vedi.
Ho dovuto chiuderlo perchè era un casino. Ognuno aveva creato un proprio foglio, poi non continuava e lasciava tutto lì, non si capiva chi faceva cosa ecc..
 

Andrea Guerri

Utente assiduo
19 Febbraio 2019
1.381
65
www.youtube.com
2019 + Fogli
66
Ciao, scusami ma avevo da fare in questi giorni, avevo da finire un gestionale...

Ho trovato l'errore, e giustamente se mettevi 7 giorni doveva restituirti 1 e se metti 1 ad esempio doveva restituirti un valore basso....
Non ti spaventare se vedi la funzione così complessa, in realtà prima si sviluppa su varie celle e poi si inizia ad assemblare, è più facile di quel che credi...
=SE.ERRORE(SOMMA(filter(SPESA_AG!$C:$C;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1))-CERCA.VERT(max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2));SPESA_AG!$B:$C;2)+(sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1);1;;1;VERO)-(max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1))*SE.ERRORE(CERCA.VERT((max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1);SPESA_AG!$B:$C;2)/(sortn(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2;SPESA_AG!$B:$B>=max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1);1;;1;VERO)-CERCA.VERT((max(filter(SPESA_AG!$B:$B;SPESA_AG!$A:$A=$A2))-B$1);SPESA_AG!$B:$B;1))))
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!