[Risolto]Inserire valori del giorno prima anno corrente e ..

[sandro merlino]

Ciao gioh66,
se sostituisco le ultime due formule che mi hai fatto per K126 e L126 il risultato è
err:512 Formula troppo lunga Compilatore: il numero totale dei segni interni (ovvero operatori, variabili, parentesi) nella formula supera 8192.


Grazie 1000
Sandro

[gioh66]

Non mi pare, copiando la formula in una casella (senza il segno =) e usando la funzione LUNGHEZZA() mi da 1172 caratteri.

[sandro merlino]

Ciao gioh66,
Non capisco cosa sia ma purtroppo è uno dei problemi che a volte mi capita soprattutto con formule complicate si ripete spesso.
La mia versione di openoffice spezza la tua formula
eccola:
=SE(OGGI()=DATA(ANNO(OGGI());12;31);INDICE(sv!Q$125:Q$1255;CONFRONTA(DATA(ANNO(OGGI());1;1);sv!$F$125:$F$1255;0));SE(E(ANNO.BISESTILE(DATA(ANNO(OGGI())-1;1;1));OGGI()=DATA(ANNO(OGGI());12;31));INDICE(sv!Q$125:Q$1255;CONFRONTA(DATA(ANNO(OGGI());1;2);sv!$F$125:$F$1255;0));SE(E(ANNO.BISESTILE(DATA(ANNO(OGGI());1;1));OGGI()=DATA(ANNO(OGGI());12;31));INDICE(sv!H$125:H$1255;CONFRONTA(DATA(ANNO(OGGI());1;1);sv!$F$125:$F$1255;0)); SE(O(E(ANNO.BISESTILE(DATA(ANNO(OGGI());1;1));OGGI()>DATA(ANNO(OGGI());2;29));E(ANNO.BISESTILE(DATA(ANNO(OGGI())-1;1;1));OGGI()<DATA(ANNO(OGGI());3;1)));INDICE(sv!Q$125:Q$1255;CONFRONTA(OGGI()+1;sv!$F$125:$F$1255;0));SE(O(E(GIORNO.SETTIMANA(DATA(ANNO(OGGI());12;30);2)=1;GIORNO.SETTIMANA(DATA(ANNO(OGGI())-1;12;30);2)=3);E(GIORNO.SETTIMANA(DATA(ANNO(OGGI());12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO(OGGI())-1;12;31);2)=2));INDICE(sv!I$125:I$1255;CONFRONTA(DATA(ANNO(OGGI());1;1);sv!$F$125:$F$1255;0));SE(E(GIORNO.SETTIMANA(DATA(ANNO(OGGI());12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO(OGGI())-1;12;31);2)=3);INDICE(sv!I$125:I$1255;CONFRONTA(DATA(ANNO(OGGI());1;2);sv!$F$125:$F$1255;0));INDICE(sv!Q$125:Q$1255

Grazie 1000

Sandro

[unlucky83]

buonasera
La situazione è:
file pesante perchè vengono impiegate formule lunghissime
le formule probabilmente sono accorciabili impiegando celle ausiliarie e ciò in alcuni casi eviterebbe di ripetere dei calcoli ( quasi certamente anche nelle formattazioni condizionali sei costretto ad adoperare le formule lunghe)
il layout del foglio non agevola la scrittura compatta delle formule
la possibilità di rivedere la struttura del foglio è praticamente zero ed è quasi zero la possibilità di impiegare le celle di appoggio (utili per le formattazioni condizionali).
Già così mi sembra un circolo vizioso da cui mi sembra difficile uscire

Inoltre mettiamoci dentro anche il fatto che la versione del programma opensource sembra debba essere per forza openoffice, che presenta la limitazione della lunghezza delle formule.
Stavo seguendo in silenzio la discussione perchè ho visto due validi utenti cercare di capire cosa vuoi ottenere e come darti una mano. Mi dispiace notare che gli complichi la vita pasticciando con le versioni degli allegati. Al momento evito di entrare nel discorso su come darti realmente una mano perchè nella precedente discussione mi hai messo in seria difficoltà a comprenderti e in questa sono restio ad inserirmi perchè ho visto che stai facendo quasi la stessa cosa con gioh66
ed Eramad.
Noto che stai andando meglio con gli interventi, ma proponendo un esercizio complicato devi essere più che chiaro...cristallino
Cerca di mantenere la discussione ordinata e lineare in modo che sia comprensibile e che susciti interesse in chi potrebbe intervenire dandoti un valido aiuto, ma soprattutto nel rispetto di chi già si sta impegnando attivamente.

[sandro merlino]

Ciao unlucky83,
Ti ringrazio per la spiegazione che hai dato sul problema della formula.
Per quanto riguarda le persone che stanno provando ad aiutarmi che sono gioh66 ed Eramad confermo la loro massima disponibilità e competenza.
Cercherò di migliorare per quanto riguarda la chiarezza dei miei messaggi ma per me è difficile spiegare qualcosa che non conosci completamente.

Per quanto riguarda il problema in corso ho fatto questo:
Ho lasciato l'ultima versione del file che gioh66 ha fatto con le date sulla colonna F perché ripeto che in G e M ho problemi di numero di formattazioni.
In DM H126 e DM126 funzionano (sulla base dei controlli che ho fatto. In futuro quando il file sarà completato ne farò altri)
Per le formule in K126 e L126 Eramad mi ha dato una spiegazione molto interessante nel precedente messaggio del 17 aprile 2018
I miei limiti non mi permettono di convertire la formula considerando che era stata fatta sulle colonne G e M impostate come date.

Lui dice:

=CERCA.VERT(OGGI()-365;SV.J14:O468;2;0)

nel file nominato inserimento dati per confronto - N°4.ods


Scusate ancora per il disturbo

Grazie 1000

Sandro

[gioh66]

Prendendo spunto dalle formule di Ermand e se ti va bene di aggiungere una colonna di supporto, che io ho messo in S del foglio SV, si possono accorciare le formule.
Dunque nella colonna F restano le date per l'anno corrente, mentre in S ho messo le formule per le date dell'anno passato. La formula è come quella di colonna F, solo che l'anno è preso dalla colonna R. Una volta fatto il compilamento gli ho dato un colore del testo bianco per nascondere i valori.
Con questa modifica, le formule per i controlli nel foglio DM diventano:
in H137

Codice: Seleziona tutto

=SE($J$131=DATA(ANNO($J$131);1;1);INDICE(SV.$N$125:$N$1255;CONFRONTA(DATA(ANNO($J$131);12;31)-365;SV.$S$125:$S$1255;0));CERCA.VERT($J$131-1;SV.$F$125:$K$1255;3;0)) 

in I137

Codice: Seleziona tutto

=SE($J$131=DATA(ANNO($J$131);1;1);INDICE(SV.$Q$125:$Q$1255;CONFRONTA(DATA(ANNO($J$131);12;31)-365;SV.$S$125:$S$1255;0));CERCA.VERT($J$131-1;SV.$F$125:$K$1255;6;0))

in k137

Codice: Seleziona tutto

=SE(O(E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31)-365;2)=2);E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;30);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;30)-365;2)=3));CERCA.VERT(DATA(ANNO($J$131);1;1);SV.$F$125:$K$1255;3;0);SE(E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31)-365;2)=3);CERCA.VERT(DATA(ANNO($J$131);1;2);SV.$F$125:$K$1255;3;0);INDICE(SV.$N$125:$N$1255;CONFRONTA(DATA(ANNO($J$131);MESE($J$131);GIORNO($J$131))-365;SV.$S$125:$S$1255;0))))

in L137

Codice: Seleziona tutto

=SE(O(E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31)-365;2)=2);E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;30);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;30)-365;2)=3));CERCA.VERT(DATA(ANNO($J$131);1;1);SV.$F$125:$K$1255;3;0);SE(E(GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31);2)=1;GIORNO.SETTIMANA(DATA(ANNO($J$131);12;31)-365;2)=3);CERCA.VERT(DATA(ANNO($J$131);1;2);SV.$F$125:$K$1255;3;0);INDICE(SV.$Q$125:$Q$1255;CONFRONTA(DATA(ANNO($J$131);MESE($J$131);GIORNO($J$131))-365;SV.$S$125:$S$1255;0))))

[sandro merlino]

Ciao gioh66,
Ci provo e poi ti dico. Gli farò vedere le conversazioni per dimostrargli che è molto difficile senza aggiungere colonne.
Temo però che la risposta sarà negativa e a quel punto sarò nei guai.

Da incompetente mi è venuta un'idea ovviamente maccheronica.
E' possibile fare una formula che fa questi controlli:
1) trova la data con oggi()-1 nell'intervallo F125:F1255
2)confronta le colonne G e M della riga della data con oggi()-1
3)
a)se nella riga della data con oggi()-1 i giorni sono uguali riporta il valore della colonna N della riga della data con oggi()-1 in K126 oppure della colonna Q della riga della data con oggi()-1 in L126 del foglio DM
b)se nella riga della data con oggi()-1 i giorni sono diversi di un giorno (esempio martedì in G e mercoledì in M) riporta il valore della colonna N della riga della data con oggi() in K126 oppure della colonna Q della riga della data con oggi() in L126 del foglio DM
c)se nella riga della data con oggi()-1 i giorni sono diversi di due giorni (esempio lunedi in G e mercoledì in M) riporta il valore della colonna N della riga della data con oggi()+1 in K126 oppure della colonna Q della riga della data con oggi()+1 in L126 del foglio DM

Considerando sempre il discorso che I miei superiori vogliono al 31/12 questo calcolo che secondo me non è veritiero: Esempi

Anno corrente/30 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente

Anno corrente/31 lunedi -anno passato/martedi -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente

Anno corrente/31 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 2 gennaio anno corrente

Non ti preoccupare puoi dirmene di tutti i colori non mi offendo.
Conosco i miei limiti ed è per questo che spero in un aiuto da tutti voi

Grazie 1000

Sandro

[sandro merlino]

Ciao Gioh66,
Purtroppo come ti avevo detto i capi non vogliono questa soluzione.
Chiarisco che non voglio confondere le idee a nessuno anzi vorrei una soluzione da poter consegnare anche domani. Purtroppo faccio quello che posso e anche voi come sempre mi state dando una grande mano. Purtroppo ancora manca un pochino all'obiettivo.
Vi spiego quello che ho fatto:
Sono tornato indietro e ho ripreso il file che mi sembrava piu corretto perchè nonostante debba fare
sempre i controlli le formule dovrebbero essere corrette tranne quelle relative a 31 dicembre anno corrente e 1 gennaio anno successivo (considerando che devo calcolare un giorno in meno)
Per quanto riguarda queste 2 date che sarebbe il problema piu volte detto:

Considerando sempre il discorso che I miei superiori vogliono al 31/12 questo calcolo che secondo me non è veritiero: Esempi
Anno corrente/30 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente
Anno corrente/31 lunedi -anno passato/martedi -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente
Anno corrente/31 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 2 gennaio anno corrente

ho sviluppato queste due formule (accrocchi per voi) che ho messo in

K127:
=SE(O(E((F1254=30/12/SV.L120);SV.G1254=SV.M1255);SV.N1255;E((F1254=30/12/SV.L120);SV.G1254=SV.G125);SV.H125;E((F1255=31/12/SV.L120);SV.G1255=SV.G125);SV.H125;E((F1255=31/12/SV.L120);SV.G1255=SV.G126);SV.H126))

L127:=SE(O(E((F1254=30/12/SV.L120);SV.G1254=SV.M1255);SV.Q1255;E((F1254=30/12/SV.L120);SV.G1254=SV.G125);SV.K125;E((F1255=31/12/SV.L120);SV.G1255=SV.K125);SV.K125;E((F1255=31/12/SV.L120);SV.G1255=SV.G126);SV.K126))

che secondo me potrebbero unite a quelle in K126 e L126 risolvere il problema dei due giorni detti precedentemente

Ho provato ad unirle nelle celle K128 e L128 ma con scarso risultato

Spero che tu o altri mi possa dare una mano perchè capisco che ti sei rotto ampiamnte

Allego il file

Grazie 1000

Sandro

[sandro merlino]

Ciao gioh66,
Mi sono accorto che le formule che avevo inserito ne file nel foglio DM in K127 e L127 erano sbagliate quindi le ho corrette
Ora dovrevbbero funzionare e riguardano i giorni 30-31/12 e considerano il caso che i giorni delle colonne G e M siano
uguali, con uno slittamento di un giorno (es:lunedi-martedi etc),con uno slittamento di due giorni (es:lunedi-mercoledi etc).
Nel foglio DM in K128 e L128 ho unito le mie formule con quelle tue.
Considerando che quelle tue avevamo notato che avevano il problema di restituire #N/D nel caso il confronto dovesse eccedere l'anno per i giorni 30-31/12
ho inserito la mia formula prima in modo che a quelle date non ci arrivase.
Ora vorrei che, che se ancora vuoi, mi aiutassi a rivedere il tutto e a fare arrivare la formula fino al 29/12 perchè per 30-31/12 le ho fatte io
Nel foglio DM in J131 c'è una data che mi serve per fare i controlli in K128 e L128. Come posso sostituirla nella formula finale se funziona ?
Mettere la mia formula alla fine della tua.

Scusate se sono pressante ma sto arrancando per arrivare alla fine

Allego il file

Grazie 1000

Sandro

[gioh66]

Ho ripreso in mano il file per fare le formule basandomi solo sulla colonna F, per la ricerca e durante le varie prove mi sono accorto che le condizioni

Codice: Seleziona tutto

Anno corrente/30 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente 
Anno corrente/31 lunedi -anno passato/martedi -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente
Anno corrente/31 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 2 gennaio anno corrente

non sono realistiche.
Se guardi un calendario ti accorgi che se il 31 dicembre cade di lunedì, l'anno precedente sarà o domenica o sabato, a seconda degli anni bisestili, e mai un giorno successivo.
Detto la formula in k126 diventa così

Codice: Seleziona tutto

=SE(O(E(ANNO.BISESTILE(OGGI()-365);OGGI()<DATA(ANNO(OGGI());3;1));E(ANNO.BISESTILE(OGGI());OGGI()>=DATA(ANNO(OGGI());3;1)));CERCA.VERT(OGGI()+1;SV.F225:Q1255;9;0);CERCA.VERT(OGGI();SV.F225:Q1255;9;0))

e in L126

Codice: Seleziona tutto

=SE(O(E(ANNO.BISESTILE(OGGI()-365);OGGI()<DATA(ANNO(OGGI());3;1));E(ANNO.BISESTILE(OGGI());OGGI()>=DATA(ANNO(OGGI());3;1)));CERCA.VERT(OGGI()+1;SV.F225:Q1255;12;0);CERCA.VERT(OGGI();SV.F225:Q1255;12;0))

Quello che mi manca è invece il confronto con il primo giorno dell'anno, cioè se lo devi confrontare con il 31 dicembre dell'anno precedente, questa data poi con cosa la confronti? Secondo il tuo foglio la data prima del 1/1/18 è il 31/12/17 e andrebbe confrontato con il corrispondente giorno della settimana dell'anno prima (2016), ma dove prendi il dato?
Ps.: un'altra cosa che ti faccio notare è che con la struttura del tuo file, se l'anno corrente è bisestile hai uno slittamento del giorno della settimana di una posizione fino al 29/2 e di due posizioni successivamente, fino al 28/2 dell'anno successivo, dopo tale data lo slittamento torna di una riga fino 29/2 del prossimo anno bisestile.

[sandro merlino]

Ciao gioh66,
Tu dici:

Ho ripreso in mano il file per fare le formule basandomi solo sulla colonna F, per la ricerca e durante le varie prove mi sono accorto che le condizioni
Anno corrente/30 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente
Anno corrente/31 lunedi -anno passato/martedi -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente
Anno corrente/31 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 2 gennaio anno corrente

Questo è quello che deve fare il calcolo alla fine dell'anno

Tu dici:

non sono realistiche.
Se guardi un calendario ti accorgi che se il 31 dicembre cade di lunedì, l'anno precedente sarà o domenica o sabato, a seconda degli anni bisestili, e mai un giorno successivo.
Detto la formula in k126 diventa così

Se guardi il calendario i giorni dell'anno corrente possono corrispondere allo stesso giorno (es domenica-domenica), ad un giorno dopo (es domenica-lunedi) a due giorni dopo (domenica-martedi). E' per questo che se guardi il giorno 29/12 di qualunque anno non avrai mai problemi per il confronto con l'anno precedente. I problemi sorgono quando come detto prima
Anno corrente/30 lunedi -anno passato/mercoledì -> nelle celle dell'anno precedente K e L deve essere inserito i dati del 1 gennaio anno corrente . Capisco che potrebbe sembrare strano ma la settimana è fatta in questo modo (es 31/12/2017 domenica -1/1/2018 lunedi) quindi per il calcolo passeremo a H125 e K125. Se è lo stesso giorno oppure lo scarto è di un giorno si trovano i dati sempre nello stesso anno
Stesso concetto per il 31/12 anno corrente

Per chiarimenti fammi sapere

Tu dici:

Quello che mi manca è invece il confronto con il primo giorno dell'anno, cioè se lo devi confrontare con il 31 dicembre dell'anno precedente, questa data poi con cosa la confronti? Secondo il tuo foglio la data prima del 1/1/18 è il 31/12/17 e andrebbe confrontato con il corrispondente giorno della settimana dell'anno prima (2016), ma dove prendi il dato?

Questo non lo devo mai fare. Forse non mi ero spiegato bene in precedenza. Si parte con i confronti dal 1/1/ corrente anno con i valori dell'anno precedente stesso giorno (come ho detto prima possono essere: stessa riga, riga sotto, 2 righe sotto) fino ad arrivare al 30/12 dove ci sono le problematiche dette sopra

Tu dici:

Ps.: un'altra cosa che ti faccio notare è che con la struttura del tuo file, se l'anno corrente è bisestile hai uno slittamento del giorno della settimana di una posizione fino al 29/2 e di due posizioni successivamente, fino al 28/2 dell'anno successivo, dopo tale data lo slittamento torna di una riga fino 29/2 del prossimo anno bisestile.

Per questo i capi non mi hanno mai detto niente. Vogliono il confronto giorno del mese (colonna F)e corrispondente (stesso giorno settimana anno corrente e precedente colonne G e M)

Non allego file perchè usiamo lo stesso

Grazie 1000

Sandro

[unlucky83]

Provo ad inserirmi
Allego file in cui ho aggiunto il foglio3 per capire bene quali date dell'anno corrente devono corrispondere a quelle dell'anno passato.
Forse faccio un ragionamento troppo semplice io, ma invece di partire dalla data attuale e andare indietro di 365 per poi aggiungere uno o due giorni tenendo conto dell'anno bisestile, non si può direttamente partire dalla data attuale e sottrarre 364 giorni (52*7=364)? Mi sembra che le corrispondenze tra i due anni così siano quelle volute.
Per condurre prove sul foglio3, basta modificare gli anni delle due celle evidenziate. In alto ho messo una tabella di sintesi per le corrispondenze del primo e dell'ultimo dell'anno, mentre in basso il calendario completo.
Se fosse corretto ciò che penso, credo che le formule del foglio2 siano facili da modificare.
Spero di essere stato utile.

[sandro merlino]

Ciao unlucky83
Non ho capito il ragionamento che hai fatto per arrivare a questo risultato ma non mi importa.
Super complimenti per quello che hai fatto. Mi si intrecciavano gli occhi solo a leggerlo, figurati a farlo. Hai capito al 99,99% quello che i capi volgliono
Ho verificato 3 anni a confronto e mi sembravano corretti.
Faccio alcune precisazioni:

Se l'anno in C18 è 2016 va bene che la colonna A arrivi a giorni 366 perchè noi vogliamo controllare l'anno corrente e l'anno corrente è di 366 giorni

Se l'anno in C18 è 2017 non va bene che la colonna A arrivi a giorni 366 perchè a noi non interessa che l'anno precedente sia bisestile. Deve arrivare a 365 giorni.
Se il calcolo fosse susseguente come noi vogliamo in dove vedo 366 dovrebbe esserci il calcolo relativo al 1/1/2018 che mi perderei

Mi sembra che tu abbia capito che se considero l'anno corrente come es il 2018 e sono a fine anno 31 lunedi e il lunedi che devo considerare per l'anno precedente è il 1/1/2018
Puo capitare anche per il 30/12 in altri confronti

Mi scriveresti per cortesia le formule finali nel foglio DM in H126, I126, K126, L126
Non vorrei fare danni.
Non vedo l'ora di consegnarlo

Grazie 1000
Sandro

[unlucky83]

Ciao unlucky83
Non ho capito il ragionamento che hai fatto per arrivare a questo risultato ma non mi importa.

Vado indietro sottraendo 364 giorni. Vedi celle E3 e F20 del foglio che ho aggiunto.

Faccio alcune precisazioni:
Se l'anno in C18 è 2016 va bene che la colonna A arrivi a giorni 366 perchè noi vogliamo controllare l'anno corrente e l'anno corrente è di 366 giorni
Se l'anno in C18 è 2017 non va bene che la colonna A arrivi a giorni 366 perchè a noi non interessa che l'anno precedente sia bisestile. Deve arrivare a 365 giorni.
Se il calcolo fosse susseguente come noi vogliamo in dove vedo 366 dovrebbe esserci il calcolo relativo al 1/1/2018 che mi perderei

E' un elenco di esempio, tu non vuoi una colonna con tutte le date dell'anno, quindi che sia di 365, 366 o 4000 giorni non ti interessa.

Mi scriveresti per cortesia le formule finali nel foglio DM in H126, I126, K126, L126
Non vorrei fare danni.
Non vedo l'ora di consegnarlo

Le formule del secondo foglio in H126 e in I126 sono già corrette. Le riporto a titolo informativo e per capire meglio come modificarle per ottenere le formule delle altre due celle.
In H126

Codice: Seleziona tutto

=INDICE($SV.$H$125:$H$1256;CONFRONTA(OGGI()-1;$SV.$F$125:$F$1256;0))

In I126

Codice: Seleziona tutto

=INDICE($SV.$K$125:$K$1256;CONFRONTA(OGGI()-1;$SV.$F$125:$F$1256;0))

In K126 va:

Codice: Seleziona tutto

=SE(ANNO(OGGI()-1)=ANNO(OGGI()-1-364);
INDICE($SV.$H$125:$H$1256;CONFRONTA(Data(ANNO(OGGI()-1);MESE(OGGI()-1-364);Giorno(OGGI()-1-364));$SV.$F$125:$F$1256;0));
INDICE($SV.$N$125:$N$1256;CONFRONTA(Data(ANNO(OGGI()-1);MESE(OGGI()-1-364);Giorno(OGGI()-1-364));$SV.$F$125:$F$1256;0))
)

In L126 va:

Codice: Seleziona tutto

=SE(ANNO(OGGI()-1)=ANNO(OGGI()-1-364);
INDICE($SV.$K$125:$K$1256;CONFRONTA(Data(ANNO(OGGI()-1);MESE(OGGI()-1-364);Giorno(OGGI()-1-364));$SV.$F$125:$F$1256;0));
INDICE($SV.$Q$125:$Q$1256;CONFRONTA(Data(ANNO(OGGI()-1);MESE(OGGI()-1-364);Giorno(OGGI()-1-364));$SV.$F$125:$F$1256;0))
)

Allego file con formule modificate in foglio2. Come piace a te, testale cambiando la data del sistema operativo

Editato: 29/05/2018 22:33 Allegato rimosso. Il msg è già esaustivo presentando le formule

[sandro merlino]

Ciao unlucky83,
Scusa il ritardo ma mi hanno dirottato su altri progetti di calcolo.
Devo sempre iniziare a fare I controlli
Conto di concludere la cosa al piu presto e mettere risolto al piu presto

Grazie 1000

Sandro

[sandro merlino]

Ciao unlucky83,
Finalmente dovrebbero concedermi un po di tempo per concludre questo argomento.
Ho ripreso il tuo ultimo file. Come ben sai preferisco fare i controlli cambiando la data quindi penso di non sbagliare
modificando solo le celle del foglio SV inserendo in L120,L220,L320,L420,L520,L620,L720,L820,L920,L1020,L1120,L1220 la formula

Codice: Seleziona tutto

=TESTO(OGGI();"aaaa")

e in R120,R220,R320,R420,R520,R620,R720,R820,R920,R1020,R1120,R1220 la formula

Codice: Seleziona tutto

=TESTO(OGGI();"aaaa")-1

Per evitare che ci siano collegamenti a me sconosciuti ho inserito posso cancellare accanto ad alcune celle e quindi essere sicuro che le celle che devo guardare sono solo H126,I126,K126,L126 del foglio DM .
Ho inziato i controlli e tutti tornano ma mi sembra di avere dato involontariamente un indicazione sbagliata. Me ne sono accorto solo ora che un collega me lo ha fatto notare e spero che si possa rimediare facilmente. Se non si può fare fammi sapere.
Spero di non intrecciarmi ed essere chiaro

Quando sono all'inizio l'anno il primo giorno cioè 1/1/2019 nel foglio DM devo prendere il giorno precedente. Quindi in H126 andrà il valore N1255 e in I126 andrà il valore Q1255.
In K126 andrà il valore di N125 e in L126 andrà il valore di Q125 perchè io dovrei confrontare il 31/12/2018 con il 31/12/2017 che non ho ma siccome il 31/12/2018 è lunedi e il 31/12/2017 è domenica si deve passare al 1/1/2018 per confrontare lunedi con lunedi

Quando sono all'inizio l'anno il primo giorno cioè 1/1/2017 nel foglio DM devo prendere il giorno precedente. Quindi in H126 andrà il valore N1255 e in I126 andrà il valore Q1255.
In K126 andrà il valore di N126 e in L126 andrà il valore di Q126 perchè io dovrei confrontare il 31/12/2016 con il 31/12/2015 che non ho ma siccome il 31/12/2016 è sabato e il 31/12/2015 è giovedi si deve passare al 1/1/2016 per confrontare sabato con sabato

Se invece il giorno del (31/12 /anno precedente) fosse uguale al (31/12/2 anni precedenti) il risultato in K126 e N126 dovrebbe essere "Non Disponibile"

Per dubbi sono a tua disposizione



Grazie 1000

sandro

[unlucky83]

Per evitare che ci siano collegamenti a me sconosciuti ho inserito posso cancellare accanto ad alcune celle e quindi essere sicuro che le celle che devo guardare sono solo H126,I126,K126,L126 del foglio DM .

Puoi cancellarle.

Ho inziato i controlli e tutti tornano ma ...

Bene che tutti tornino, vediamo che cosa si deve aggiungere...

Quando sono all'inizio l'anno il primo giorno cioè 1/1/2019 nel foglio DM devo prendere il giorno precedente. Quindi in H126 andrà il valore N1255 e in I126 andrà il valore Q1255.

e questo lo fa. (1)

In K126 andrà il valore di N125 e in L126 andrà il valore di Q125 perchè io dovrei confrontare il 31/12/2018 con il 31/12/2017 che non ho ma siccome il 31/12/2018 è lunedi e il 31/12/2017 è domenica si deve passare al 1/1/2018 per confrontare lunedi con lunedi

e questo lo fa. (2)

Quando sono all'inizio l'anno il primo giorno cioè 1/1/2017 nel foglio DM devo prendere il giorno precedente. Quindi in H126 andrà il valore N1255 e in I126 andrà il valore Q1255.

e questo lo fa. (3)

In K126 andrà il valore di N126 e in L126 andrà il valore di Q126 perchè io dovrei confrontare il 31/12/2016 con il 31/12/2015 che non ho ma siccome il 31/12/2016 è sabato e il 31/12/2015 è giovedi si deve passare al 1/1/2016 per confrontare sabato con sabato

E qui correttamente ti confronta sabato con sabato, ma 31/12/2016 con 2/1/2016. Lo fa (4)

Se invece il giorno del (31/12 /anno precedente) fosse uguale al (31/12/2 anni precedenti) il risultato in K126 e N126 dovrebbe essere "Non Disponibile"

ohhhhhhhhhhhhh, bene, questa è l'informazione nuova. Mi fa piacere dirti che non essendo multipli di 7 sia 365 sia 366, è impossibile che due ultimi dell'anno consecutivi cadano nello stesso giorno della settimana. Quindi è inutile modificare la formula per farti retituire "Non Disponibile" al verificarsi di un evento che potrà verificarsi l'anno del mai. (vedi allegato, foglio "fineanno"))
Probabilmente volevi dire qualcosa di diverso, anche perché se capitasse "giorno 31/12 /AAAA"="giorno 31/12 /AAAA-1" perché riceve come risposta "Non Disponibile" dato che il valore di SV.N1255 e SV.Q1255 sono disponibili?
Rimango in attesa di altre info.
Buona Domenica

Editato: 22:36 Allegato rimosso. Mostra solo che gli ultimi dell'anno consecutivi cadono sempre in giorni differenti

[sandro merlino]

Ciao unlucky83,

Ti volevo chiedere se nel foglio DM nelle formule in H126, I126, K126, L126 posso cambiare 1256 con 1255
Per quanto riguarda la mia richiesta che diceva:

Se invece il giorno del (31/12 /anno precedente) fosse uguale al (31/12/2 anni precedenti) il risultato in K126 e N126 dovrebbe essere "Non Disponibile"

hai chiarito ottimamente nel foglio FineAnno. Non mi vergogno a dirti che pensavo potesse accadere che i 2 giorni della settimana fossero uguali
Tu dici:

Quando sono all'inizio l'anno il primo giorno cioè 1/1/2019 nel foglio DM devo prendere il giorno precedente. Quindi in H126 andrà il valore N1255 e in I126 andrà il valore Q1255.
e questo lo fa. (1)

Questo a me non torna e ti dico i controlli come sono stati fatti su 3 anni:
cambiato la data del computer a 1/1/2017
cambiato nel foglio test il valore di A3 e C18 con 2017
Nel foglio DM nelle celle H126, I126, K126, L126 il valore risultava #N/D

cambiato la data del computer a 1/1/2018
cambiato nel foglio test il valore di A3 e C18 con 2018
Nel foglio DM nelle celle H126, I126, K126, L126 il valore risultava #N/D

cambiato la data del computer a 1/1/2019
cambiato nel foglio test il valore di A3 e C18 con 2019
Nel foglio DM nelle celle H126, I126, K126, L126 il valore risultava #N/D

Se la data del computer è 2/1/2017, 2/1/2018, 2/1/2019 il risultato torna

Secondo me qui non mi sono spiegato bene perchè forse è qui il nocciolo della questione
Quando tu dici:

Probabilmente volevi dire qualcosa di diverso, anche perché se capitasse "giorno 31/12 /AAAA"="giorno 31/12 /AAAA-1" perché riceve come risposta "Non Disponibile" dato che il valore di SV.N1255 e SV.Q1255 sono disponibili?

In realtà non sono disponibili entrambi ma solo uno e sarebbe quello relativo al 31/12/AAAA-1 il 31/12/AAAA-2 non ce l'ho. Io non devo confrontare il 31/12/AAAA con 31/12/AAAA-1. Non è mica per questo motivo che la formula che ho controllato al 1/1/2017, 1/1/2018, 1/1/2019 potrebbe non tornare ?(se sbaglio correggimi nuovamente )

Esempio:
Se sono per esempio al 1/1/2018 come data odierna.
Devo confrontare il 31/12/AAAA-1 lo trovo nel foglio SV nelle celle N1255 e Q1255 con il valore n1255 e q1255 che devo confrontare con 31/12/AAAA-2 che non ho. La cosa che ho saputo da te e che ci risolve il problema è che i 2 giorni della settimana non possono essere uguali e quindi il valore che mi serve lo prendo in N125 o N126 oppure in Q125 o Q126 a seconda del medesimo giorno della settimana

Riallego il file precedente

Grazie 1000

Sandro

[unlucky83]

Buongiorno.
Facciamo chiarezza. Il Foglio TEST serve per fare i test della formula senza BISOGNO DI CAMBIARE la data del sistema operativo e i valori di A3 e C18 di questo foglio influenzano solo il foglio TEST, non hanno alcun effetto su foglio DM. In poche parole TU non dovresti usare ne foglio TEST ne foglio fineanno. Questo perchè ai tuoi capi non interessano fogli o celle aggiuntive e quindi ti ho messo in foglio DM già le formule comandate dalla data di oggi.

In conclusione il vero test lo dovresti fare cambiando solo la data del sistema operativo e osservare cosa ti succede in foglio SV e DM
Se imposti la data del sistema operativo al primo gennaio 2019 le formule in DM danno errore per un semplice motivo, perchè ti cambia il foglio SV, diventando tabelle 2019 e 2018. Le formule vorrebbero lavorare con 2018 2017, valori che non vengono trovati.

In realtà non sono disponibili entrambi ma solo uno e sarebbe quello relativo al 31/12/AAAA-1 il 31/12/AAAA-2 non ce l'ho. Io non devo confrontare il 31/12/AAAA con 31/12/AAAA-1. Non è mica per questo motivo che la formula che ho controllato al 1/1/2017, 1/1/2018, 1/1/2019 potrebbe non tornare ?(se sbaglio correggimi nuovamente )

E proprio come hai intuito.

Quindi ti chiedo, quando verrà realmente il giorno 1 gennaio 2019, avrai già tutti i valori dell'anno 2019 pronti nella tabella di sinistra? Io penso proprio di no, quindi ti modifico la formula in SV.L120 e in SV.R120 in modo da lasciarti le tabelle di SV riferite agli anni 2018 e 2017

Codice: Seleziona tutto

=TESTO(OGGI()-1;"aaaa")
=TESTO(OGGI()-1;"aaaa")-1

Esempio:
Se sono per esempio al 1/1/2018 come data odierna.
Devo confrontare il 31/12/AAAA-1 lo trovo nel foglio SV nelle celle N1255 e Q1255 con il valore n1255 e q1255 che devo confrontare con 31/12/AAAA-2 che non ho. La cosa che ho saputo da te e che ci risolve il problema è che i 2 giorni della settimana non possono essere uguali e quindi il valore che mi serve lo prendo in N125 o N126 oppure in Q125 o Q126 a seconda del medesimo giorno della settimana

Qua non sò che dirti, non sò quando e come dovrai lavorare con il file, se alla fine manterrai la formula in SV.L120 sul documento finale, cosa che per me non ha senso, dato che con i valori inseriti per l'anno 2018, aprendo il file tra 10 anni li leggerò riferiti al 2028. Vedi te come andare avanti.

Ti volevo chiedere se nel foglio DM nelle formule in H126, I126, K126, L126 posso cambiare 1256 con 1255

Questo lo puoi fare.

Ti riallego il file che presenta solo la modifica delle celle contenente gli anni di foglio SV.

[sandro merlino]

Ciao unlucky83,
Purtroppo l'ultima modifica non è piaciuta.
Ho ripreso il file precedente per continuare a trovare la soluzione. Non voglio come altre volte mi è stato detto fare confusione ma è solo una necessità.
Preciso per l'ennesima volta che non vedo l'ora di finire questa cosa e passare alla successiva.
Ti allego il file dove ho inserito nella cella H130 e I130 una piccola modifica alle formule di H126 e I126 (utilizzando una parte di formula che avevi usato tu) che a me sembra che funzioni. Tu che ne dici? Ci potrebbero essere dei problemi ?
Dovrebbe entrare in azione solo se siamo all' 1/1/AAAA che inserisce i dati del 31/12/AAAA-1 per poi continuare con la tua formula.

[unlucky83]

Purtroppo l'ultima modifica non è piaciuta.

Qual'è l'ultima modifica che non è piaciuta? il foglio DM ha solo le celle che hai proposto te

Inoltre manca il tuo allegato

[sandro merlino]

Ciao unlucky83,
Scusa ma non avevo finito non mi aspettavo una risposta cosi veloce
La modifica della quale ti stavo parlando è questa:

Quindi ti chiedo, quando verrà realmente il giorno 1 gennaio 2019, avrai già tutti i valori dell'anno 2019 pronti nella tabella di sinistra? Io penso proprio di no, quindi ti modifico la formula in SV.L120 e in SV.R120 in modo da lasciarti le tabelle di SV riferite agli anni 2018 e 2017

Come ti dicevo allego il file dove ho inserito nella cella H130 e I130 una piccola modifica alle formule di H126 e I126 (utilizzando una parte di formula che avevi usato tu nel foglio SV, colonna F) Tu che ne dici? Ci potrebbero essere dei problemi ?
Dovrebbe entrare in azione solo se siamo all' 1/1/AAAA che inserisce i dati del 31/12/AAAA-1 per poi continuare con la tua formula. Il concetto è che analizza le eccezioni per poi andare alla formula importante.
Per quanto riguarda in K130 e L130 se per te va bene vorrei fare la stessa cosa ma purtroppo non sono riuscito a unire le formule per provarle ne con la concatenazione ne in altro modo. Il risultato era sempre errore. L'ordine utilizzato delle celle de foglio DM era: Q117,Q118,Q111,Q112,Q114 e l'altro R117,R118,R111,R112,R114
Volevo inserire nella formula prima quello che succede quando arrivo al 30/12/AAAA poi quando arrivo al 31/12/AAAA per poi passare alla tua formula. Ho inserito anche quelle che non accadranno mai che ovviamente si possono levare.
Aspetto il tuo importante parere al riguardo e se possibile la formula totale che unisce quelle parziali

Grazie 1000

Sandro

[unlucky83]

Qual'è l'ultima modifica che non è piaciuta? il foglio DM ha solo le celle che hai proposto te

[sandro merlino]

Ciao unlucky83
La formla di sv.L120 e sv.R120.
Preferiscono i risultati di questo file perché pensano che manca poco a trovare le formule che danno il risultato da loro voluto.
Grazie 1000

Sandro

[unlucky83]

Ma quando il file sarà finito, le formule in sv.L120, sv.R120 e le corrispondenti non verranno eliminate? Non le stai usando solo per testare le formule di DM?

[sandro merlino]

Ciao unlucky83,
Per il momento è previsto che rimangano e vengano modificati gli altri dati.
Poi le cose si provano, si modificano, cambiano i cosidetti manager e si ricambia. Che ti posso dire ....
Grazie 1000

Sandro

[unlucky83]

voglio capire cosa succederà il primo gennaio. Chi aprirà il file finale deve trovarsi in SV difronte alle tabelle dell'anno 2019 (vuota) e dell'anno precedente 2018, oppure l'anno 2018 e l'anno 2017? Inoltre, qualcuno aprirà il file il primo gennaio?

[sandro merlino]

Ciao unlucky83,
Se consideriamo 1/1/2019
Si stampa lo schema che stiamo costruendo
Dopo di che si copiano i dati dalle colonne di sinistra perche sono le colonne dei dati del 2019 che al momento non sappiamo e si mettono in quelle di destra che sono i dati del 2018 che conosciamo

Grazie 1000

Sandro

[unlucky83]

Credo che ti sei impicciato. Chi apre il file il 1/1/2019 copia la colonna di sinistra che contiene i dati del 2018 e li incolla nella colonna di destra, poi la colonna di sinistra si svuota. Con le formule di L120 e R120 si tralascia di cambiare manualmente l'anno di riferimento. Così ha senso.

[sandro merlino]

Ciao unlucky83,
Volevo dire che copio i dati da 125 a 155, 225 a 255, etc la parte delle formule le lascio sempre e una volta trovate non si toccano piu.Ecco perchè vogliono tutto automatico.Si cambiano solo i valori mai le formule

Grazie 1000

Sandro