Domanda cambio riferimenti in formula dalla settimana 11

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao a tutti,
In questo file nelle celle N622, N672 etc ho una formula funzionante fino alla 52 settimana. Vorrei eliminare però le date in M622 e M623 e farla agganciare a M572, M573 e M574. In N623 troverete il mio tentativo con risultato sbagliato. E' in realtà una grossa sorpresa perchè pensavo bastasse solo cambiare i riferimenti di cella

Grazie

sandro
 

Allegati

Ultima modifica:

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciao S @Sandro merlino
ho scaricato il file ma da errori e quando si apre non ci sono le formuel.
Avevo ancora la copia che ti avevo mandato con le 4 soluzioni.
se vai n M622 e M623 e cancelli le formule vedrai che delle 4 soluzioni
date solo 1 va in errore le altre 3 mo.
Quindi vedi se puoi usare una delle altre 3,
 
  • Like
Reactions: Sandro merlino

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Vedevo il file cercando di ricordarmi...
Fai questa prova fino alla 9 sta a posto la 10 rimane così
alla 11 in N622 metti:

=SE(H622="";"";SE(E(H624="";N624="");SOMMA.PIÙ.SE('Sales Volume'!$N$125:$N$413;'Sales Volume'!$E$125:$E$413;">="&DATA(ANNO($M$122)+1;MESE($M$122);GIORNO($M$122)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$413;"<="&DATA(ANNO($M$123)+1;MESE($M$123);GIORNO($M$123)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$413;"<="&OGGI()+$M$124)))

poi copi la celle sulle altre fino alla fine.
e cancella le date in M

credo tu debba adattare il range vedo finisce a 413 forse perchè hai ridimensionato il file.
 
  • Like
Reactions: Sandro merlino

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Berna11
Dopo provo la tua ultima formula del precedente messaggio
Non capisco come mai non riesco ad allegare il file con le formule mi è sempre riuscito.
Te le indico comunque per controprova
Per le prime 7 settimane quelle usate sono

M122(inizio settimana anno precedente):
=SE(GIORNO(DATA($R$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($R$120;1;1);3);7)))<GIORNO(DATA($L$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($L$120;1;1);3);7)));(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7+7;(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7)

M123(fine settimana anno precedente):
=SE(GIORNO(DATA($R$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($R$120;1;1);3);7)))<GIORNO(DATA($L$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($L$120;1;1);3);7)));(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7+7;(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7)+6

M124(numero di giorni di differenza tra il 1 gennaio dei 2 anni-> 2018=lun e 2017=dom ->risultato 1)
=(GIORNO(SE(GIORNO(DATA($R$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($R$120;1;1);3);7)))<GIORNO(DATA($L$120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($L$120;1;1);3);7)));(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7+7;(DATA(R120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA(R120;1;1);3);7))+DESTRA(H99;2)*7)-7)))-GIORNO(DATA($'Sales Volume'!L120;1;((TROVA("Lun";"DomLunMarMerGioVenSab";1))/3)+RESTO(7-GIORNO.SETTIMANA(DATA($'Sales Volume'!L120;1;1);3);7)))

N122 (copiata fino a N422)(mantenendo i riferimenti M122,M123,M124)
=SE(H122="";"";SOMMA.PIÙ.SE($'Sales Volume'!$N$125:$N$1255;$'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M$122)+1;MESE($M$122);GIORNO($M$122)+(DESTRA($H99;2)*7)-7);$'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M$123)+1;MESE($M$123);GIORNO($M$123)+(DESTRA($H99;2)*7)-7);$'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$124))

Vorrebbero fare stessa cosa da marzo in giu per le settimane dalla 10 alla 52 con formula in N572 da copiare

M572 (copiato formula in M122)
M573 (copiato formula in M123)
M574(numero di giorni di differenza tra il 1 marzo dei 2 anni-> 2018=gio e 2017=mer ->risultato 1):
=SE(GIORNO.SETTIMANA($'Sales Volume'!E325;2)<GIORNO.SETTIMANA(DATA($'Sales Volume'!R320;MESE($'Sales Volume'!E325);GIORNO($'Sales Volume'.E325));2);ASS(GIORNO.SETTIMANA(DATA($'Sales Volume'!R320;MESE($'Sales Volume'!E325);GIORNO($'Sales Volume'!E325));2)-GIORNO.SETTIMANA($'Sales Volume'!E325;2)-7);GIORNO.SETTIMANA($'Sales Volume'!E325;2)-GIORNO.SETTIMANA(DATA($'Sales Volume'!R320;MESE($'Sales Volume'!E325);GIORNO($'Sales Volume'.E325));2))

N572
=SE(H572="";"";SOMMA.PIÙ.SE($'Sales Volume'!$N$125:$N$1255;$'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M572)+1;MESE($M572);GIORNO($M572));$'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M573)+1;MESE($M573);GIORNO($M573));$'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$574))

Grazie

sandro
 

alfrimpa

VBA Expert
Supermoderatore
Expert
18 Dicembre 2015
18.082
113
65
Napoli
2013
320
S @Sandro merlino

Perchè hai postato la discussione in “Altre piattaforme”?

Sul profilo dichiari Excel 2010.
 

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Alfrmpa
Ho postato su altra piattaforma perché usiamo sia Excel che OpenOffice. Openoffice e’ più limitato quindi penso, forse sbagliando, che se la formula lavora su OpenOffice lavora sicuramente con excel
Grazie
Sandro
 

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao alfrimpa
Se vuoi metto tutti e 2. Sperando che presto tutti gli altri passino a Excel. Ci provo e se non riesco ti chiedo
Grazie
Sandro
 

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciao S @Sandro merlino
scusa senza che mi metto a ricostruire il file rimettendo le formule
intaanto prova quella che ti ho mandato
devi solo modificare il fine range da

Sales Volume'!$N$125:$N$413
a
Sales Volume'!$N$125:$N$1255

tutti quelli con 413 io ti ho fatto l'esempio di N
Però, penso mi è venuto in mente dopo che ci sia un errore
proba poi ti dico.

,
 
  • Like
Reactions: Sandro merlino

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Berna 11
Ho provato a fare il controllo per gli anni 2013-2012 per la settimana dalla 11 in poi e il risultato è 0
Come al solito, spero di non aver sbagliato qualcosa
Sto provandoa fare il file

Grazie 1000

sandro
 

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Cioa S @Sandro merlino
e il risultato è 0
cosa vuol dire 0 errori oppure ti da sempre 0 invece di un totale?
Ricorda di fare la verifica anno bisestile sia come anni corrente
che come anno precedente.
Tienimi informato
Buona domenica
 
  • Like
Reactions: Sandro merlino

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Berna 11
Ho fatto alcuni controlli e ho visto che per il 2019-2018 il totale torna per la settimana 11 e 12
Per gli anni 2008-2007 e 2013-2012 non torna perché il totale é 0. Secondo me c'è la parte di formula che dice
SE(E(H624="";N624="") e visto che il 2008 e 2012 sono bisestili e quindi diverso da ""

Ps:Sto provando ad allegarti il file ma per il momento non riesco perchè mi dice che è troppo pesante

Grazie

sandro
 

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciao S @Sandro merlino
è vero che nel post #4 la formula inizia con:
=SE(H622="";"";SE(E(H624="";N624="");
io ho adattato quella che ho trovato.
Sono andato a rivedere le formule delle 4 soluzioni e, solo 1 inizia con
=SE(H622="" ..le altre non v'è traccia di =""
forse l'avrai sperimentato tu o sbaglio?
Prova togliendo: SE(E(H624="";N624="");
 

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Berna11
Sicuramente ho inserito SE(E(H624="";N624=""); in una delle mie sperimentazioni.
Ho levato questo pezzo di formula e mi diventa cosi:
=SE(H622="";"";SOMMA.PIÙ.SE('Sales Volume'!$N$125:$N$1255;'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M$122)+1;MESE($M$122);GIORNO($M$122)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M$123)+1;MESE($M$123);GIORNO($M$123)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$124))

I risultati che ho ottenuto sono questi
Confronto anno 2019-2018 ok settimana 10 e 11

Confronto anno 2008-2007 ok settimana 10 e ma per la settimana 11 la somma mi inizia da una riga sopra.
Confronto anno 2013-2012 ok settimana 10 e ma per la settimana 11 la somma mi inizia da una riga sotto.

Secondo me è perchè bisogna inserire la formula che nel post 5 è in M574 invece di M124 che calcola i giorni della settimana di differenza tra il 1/3 anno corrente e 1/3 anno precedente. Ho provato ma non torna. Forse serve qualche altro aggiustamento

Spero di essere stato chiaro. Sono sempre atua disposizione

Grazie

sandro
 

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciao sei sicuro dei controlli?
Capisco l'anno 2008-2007
anno bisestile ma non per l'anno
2013-2012 non vedo il nesso.

come detto nel post #10
Però, penso mi è venuto in mente dopo che ci sia un errore
proba poi ti dico.
e come hai fatto notare anche tu
a fine formula va variata dalla 11 settimana in poi
da +$M$124 a +$M$574.
Il resto resta invariato con la rettifica del range finale a 1255.

Se vedi cella M574 varia da 1 a 2 giorni secondo gli anni
però resta il dubbio dell'anno detto sopra in quanto M574 è 1...boh.

Il ragionamento è coretto p dovrebbe funzionare in caso contrario con questo caldo mettersi a verificare cambi anni ed importi ...
è così bello avere le due date in colonna M che indicano inizio e fine settimana... perchè eliminarle.... Lingua_lingua
 
  • Like
Reactions: Sandro merlino

Sandro merlino

Utente abituale
5 Settembre 2018
254
18
Excel 2010
1
Ciao Berna11
Femo restando che non ti devi sentire obbligato se vuoi rinunciare perchè la soluzione c'è già. Sentiti libero

Se sei curioso, mi viene di fare alcune considerazioni anche per capire il concetto anche se poi non sviluppiamo la formula

Se da N622 in giu copio questa formula:
=SE(H622="";"";SOMMA.PIÙ.SE('Sales Volume'.$N$325:$N$1255;'Sales Volume'.$E$325:$E$1255;">="&DATA(ANNO($M$572)+1;MESE($M$572);GIORNO($M$572)+(DESTRA($H599;2)*7)-7);'Sales Volume'.$E$325:$E$1255;"<="&DATA(ANNO($M$573)+1;MESE($M$573);GIORNO($M$573)+(DESTRA($H599;2)*7)-7);'Sales Volume'.$E$325:$E$1255;"<="&OGGI()+$M$574))
Dove prendo in considerazione M572,M573,M574 (formule del post 5). Come se l'anno per le 42 settimane iniziasse da marzo.
Quindi ho inizio e fine settimana della 10 settimana e la differenza tra i giorni della settimana di 1/1/anno corrente e 1/1 anno precedente. In questo caso ho visto che il risultato della cella da N622 in giu mi parte correttamente dal lunedi ma di 9 settimane piu giu. Per me è dovuto al fatto che moltiplica H599*7 e poi gli leva 7((DESTRA($H599;2)*7)-7)). che ne dici ?
Si tratta di una considerazione del CA..O o ha un piccolo fondamento ?

Grazie 1000

sandro

Ps se hai un consiglio per allegare il file che non capisco come mai risulta sempre pesante
 

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciao
figurati non mi sento obbligato è che sto lavorando come dei baraccati.
Il file non funziona le formule che invii le debbo modificare se vedi quelle sopra riportano: ($'Sales debbo togliere ($'Sales , ogni volta mi dimentico che il file arriva con il calcolo manuale e dopo prove varie mi ricordo di andare a metterlo in automatico.
Anche la formula sopra riporta:('Sales Volume'. l'ho dovuta modificare c
on.('Sales Volume'! per poterla provare.
Quindi capisci che ogni volta con questo file sono incubi.



Le formule che mi hai mandato non mi ritornano essere quelle di prima.
Esempio la formula che hai dato nel post #5 per la M124 se poi la copio fino ad M574 mi da risultato 4?? a me davano 1 o 2.

detto ciò la formula che usi dalla 1 alla 7 se non sbaglio è:

=SE(H122="";"";SOMMA.PIÙ.SE('Sales Volume'!$N$125:$N$1255;'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M$122)+1;MESE($M$122);GIORNO($M$122)+(DESTRA($H99;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M$123)+1;MESE($M$123);GIORNO($M$123)+(DESTRA($H99;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$124))

adesso variando i riferimenti M122-M123-M124 a M572-M573-M574
si ottiene la seguente:in cella N622:

=SE(H622="";"";SOMMA.PIÙ.SE('Sales Volume'!$N$125:$N$1255;'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M$572)+1;MESE($M$572);GIORNO($M$572)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M$573)+1;MESE($M$573);GIORNO($M$573)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$574))

per me questa dovrebbe essere la formula peccato che a me da risultato 0
come la tua che hai mandato sopra che è la stessa cosa (ormai sei diventato più bravo di me) anche se inizia da marzo $E$325: :

Per il DESTRA non ho capito il tuo discorso ma se usi:
=+(DESTRA($H99;2)*7)-7 a partire dalla prima settimana a scendere ti aggiunge di volta in volta 7 giorni.
Fai sapere se scopri il mistero.
 
  • Like
Reactions: Sandro merlino

Berna11

Excel Expert
Moderatore
Expert
20 Febbraio 2016
5.087
113
Latina
Excel 2010
248
Ciaoforse ho capito...
la formula destra aggiunge 7 giorni ad ogni settimana a partire dalla prima.
Se la formula la facciamo partire dalla 10 settimana bisognerebbe modificare i riferimenti che non ho considerato quali potrebbero essere.
Quindi tenendo i riferimenti iniziali della data M122-123
la formula da il risultato:

SE(H622="";"";SOMMA.PIÙ.SE('Sales Volume'!$N$125:$N$1255;'Sales Volume'!$E$125:$E$1255;">="&DATA(ANNO($M$122)+1;MESE($M$122);GIORNO($M$122)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&DATA(ANNO($M$123)+1;MESE($M$123);GIORNO($M$123)+(DESTRA($H599;2)*7)-7);'Sales Volume'!$E$125:$E$1255;"<="&OGGI()+$M$574))
Provala
 
  • Like
Reactions: Sandro merlino

Sostieni ForumExcel

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