quiz dell'"Angelo"

ninai

Excel Expert
Moderatore
Expert
11 Luglio 2015
2.501
63
56
Barcellona P.G.
2010 PC
69
Ciao a tutti
Giusto come augurio di Pasquetta e soprattutto per distogliervi dall'ingozzamento tradizionale, propongo un quiz che mi è balenato in testa.

inserita una data in A1, in A2 inserire una formula che ,trascinata in basso, restituisca tutti gli anni successivi nei quali, in quella data , vi sarà lo stesso giorno della settimana.
esempio:
A1: 28/3/2016 Lunedì, tutti glia nni successivi nei quali il 28/3 ricadrà di Lunedì.
da A2 a A6 dovrà restituire: 2022,2033,2039,2044, 2050, 2061

Ovviamente dovrà valere per qualunque data e per qualunque anno iniziale. niente VBA e vedremo a chi la fa più corta.

per comodità , le date le faremo rientrare nel secolo 2000-2100 o comunque all'interno dello stesso secolo
 

cromagno

Excel/VBA Expert
Supermoderatore
Expert
9 Agosto 2015
6.426
83
39
Sardegna
2013 (64 bit)
203
Ciao ninai,
oramai ti stai specializzando nei "quiz festivi" :255:

Propongo una prima soluzione, che non sarà sicuramente la più corta ma servirà per iniziare la "sfida"....:32:

Formula matriciale da mettere nella cella A2 e copiare in basso:

=PICCOLO(SE(GIORNO.SETTIMANA(DATA(ANNO($A$1)+RIF.RIGA($1:$100);MESE($A$1);GIORNO($A$1)))=GIORNO.SETTIMANA($A$1);ANNO($A$1)+RIF.RIGA($1:$100);"");RIF.RIGA(A1))
 

Allegati

Rubik72

Excel/VBA Expert
Supermoderatore
Expert
12 Dicembre 2015
5.392
83
47
Cosenza
Excel 2016
165
Sono tentato a rispondere con il VBA, ma mi astengo:166::

=ANNO($A$1)+AGGREGA(15;6;RIF.RIGA($1:$100)/(GIORNO.SETTIMANA(DATA(ANNO($A$1)+RIF.RIGA($1:$100);MESE($A$1);GIORNO($A$1)))=GIORNO.SETTIMANA($A$1));RIGHE($1:1))
 

Rubik72

Excel/VBA Expert
Supermoderatore
Expert
12 Dicembre 2015
5.392
83
47
Cosenza
Excel 2016
165
Scusate ma non resisto. E' troppo divertente far arrabbiare @ninai:167:
Codice:
Option Explicit


Sub Genera()
Dim iRow As Long
Dim iCount As Long


If Not IsDate(Range("A1")) Then Exit Sub



iRow = 1
For iCount = Year(Range("a1")) + 1 To 2100
    If Weekday(Range("a1")) = Weekday(DateSerial(iCount, Month(Range("a1")), Day(Range("a1")))) Then
        iRow = iRow + 1
        Cells(iRow, 1) = iCount
    End If
Next


End Sub
Prima di essere bannato, vi auguro Buon Lunedì dell'Angelo a tutti:43:
 

ninai

Excel Expert
Moderatore
Expert
11 Luglio 2015
2.501
63
56
Barcellona P.G.
2010 PC
69
le soluzioni proposte sembrano corrette ma, visto che Rubik mi vuole far diventare cattivo, aggiungo che (oltre al VBA), non si possono usare neanche range (tipo rif.riga(1:100) o altri intervalli, ma solo singole celle.
 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.842
113
Como
2011MAC 2016WIN
353
Mhmm ... :256: .. visto che ninai, che saluto, ha messo l'ultima restrizione (vietati i range ma solo celle singole), mi limito a estrarre i dati senza aggregarli e quindi uso questa formula da trascinare in basso:


=SE(GIORNO.SETTIMANA(DATA.MESE($A$1;RIF.RIGA(A1)*12))=2;DATA.MESE($A$1;RIF.RIGA(A1)*12);"")
 

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
5.437
63
75
Catania
Excel2010
125
Salve a tutta questa bella gente.

@ninai
come sai (e sapete tutti) le formule mi fanno venire ... l'orticaria. Ma voglio sempre provare.
Sapendo che sei un patito di una formula in particolare, ho cercato di applicarla ma mi dà lo stesso risultato di un semplice SE. Dove sta l'errore? I risultati copiando la formula in basso sono giusti ma occupano 100 righe.

Codice:
=MATR.SOMMA.PRODOTTO(--SE(GIORNO.SETTIMANA(DATA((ANNO($A$1)+RIF.RIGA());MESE($A$1);GIORNO($A$1)))=GIORNO.SETTIMANA($A$1);ANNO($A$1)+RIF.RIGA()))
che dà lo stesso di
Codice:
=SE(GIORNO.SETTIMANA(DATA((ANNO($A$1)+RIF.RIGA());MESE($A$1);GIORNO($A$1)))=GIORNO.SETTIMANA($A$1);ANNO($A$1)+RIF.RIGA();"")
Fammi sapere (anche se io sono ... fuori concorso). Ciao,
Mario
 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.842
113
Como
2011MAC 2016WIN
353
Mi spiace ges, non mi risulta corretta e poi gli anni devono stare in celle consecutive
Ciao ninai, d'accordo sul fatto che i risultati non sono contigui ma - per ora - non mi è venuto in mente una formula senza usare il range (come indicazioni), ma sicuro che non sia corretta? Mi sembra che restituisca tutti i lunedì dal 2016 in avanti.
 

cromagno

Excel/VBA Expert
Supermoderatore
Expert
9 Agosto 2015
6.426
83
39
Sardegna
2013 (64 bit)
203
le soluzioni proposte sembrano corrette ma, visto che Rubik mi vuole far diventare cattivo, aggiungo che (oltre al VBA), non si possono usare neanche range (tipo rif.riga(1:100) o altri intervalli, ma solo singole celle.
Ciao ninai,
probabilmente con questa "restrizione" hai anche dato un indizio su che funzione/logica utilizzare ma per adesso non so proprio da dove iniziare senza usare i range :240:

Tanto per fare un esempio....
anche i range di funzioni tipo CONFRONTA non sono ammessi?
es.
=CONFRONTA(A1;B3:B300;0)
 

ninai

Excel Expert
Moderatore
Expert
11 Luglio 2015
2.501
63
56
Barcellona P.G.
2010 PC
69
salve ragazzi...., non so se procedere alla chiusura , non vorrei che qualcuno di voi stia per "partorire" e lo "traumatizzo" :176::176:

devo ammettere che ho avuto un'ispirazione non excelliana, infatti inizialmente avevo pure io proceduto con matrici, tipo:
=TESTO(PICCOLO(SE((RIF.RIGA($31000:$82000)>$A$1)*(GIORNO(RIF.RIGA($31000:$82000))=GIORNO($A$1))*(MESE(RIF.RIGA($31000:$82000))=MESE($A$1))*(GIORNO.SETTIMANA(RIF.RIGA($31000:$82000))=GIORNO.SETTIMANA($A$1));RIF.RIGA($31000:$82000));RIGHE($B$2:B2));"aaaa")

Poi, dopo che rubik mi ha fatto "arrabbiare" con il VBA :176::176:

ho voluto adottare un algoritmo alternativo ed ho trovato altre due soluzioni, senza alcun range, con una lunghezza di 183 caratteri una ed 96 l'altra (credo ancora accorciabile).

lascio aperta la discussione ancora un po, perchè sento "odore" di scossa......


ges
forse ci siamo capiti male, si vugliono ottenere tutta la sequenza degli anni nei quali una determinata data ricade nello stesso giono della settimana, ad esempio oggi 29/03/2016 è martedì, quali saranno gli anni futuri nei quali il 29/3 sarà un martedì????

cromagno
nessun range solo celle singole, neanchè un ":"

marius
scusami ma non sono riuscito a seguirti, vado e vengo dal pc
 

cromagno

Excel/VBA Expert
Supermoderatore
Expert
9 Agosto 2015
6.426
83
39
Sardegna
2013 (64 bit)
203
Ciao a tutti,

son riuscito a buttar giù una prima stesura della formula (valida se non si passa da un secolo all'altro, altrimenti bisognerebbe aggiungere una o due condizioni).

Il fatto è che per ora mi risulta una lunghezza di 489 caratteri :171:
...anche se son convinto che si possa ridurre di circa 1/4 sistemando meglio i SE e utilizzando solo una volta la verifica per l'anno bisestile.

Comunque, nella cella A2 (da copiare poi in basso):

=DATA(ANNO(A1)+SE(O(RESTO(ANNO(A1);400)=0;E(RESTO(ANNO(A1);4)=0;RESTO(ANNO(A1);100)<>0));SE(E(MESE(ANNO(A1))>2;MESE(ANNO(A1))<=12);6;5);SE(O(RESTO(ANNO(A1)+1;400)=0;E(RESTO(ANNO(A1)+1;4)=0;RESTO(ANNO(A1)+1;100)<>0));SE(E(MESE(ANNO(A1))>2;MESE(ANNO(A1))<=12);5;11);SE(O(RESTO(ANNO(A1)-1;400)=0;E(RESTO(ANNO(A1)-1;4)=0;RESTO(ANNO(A1)-1;100)<>0));SE(E(MESE(ANNO(A1))>2;MESE(ANNO(A1))<=12);6;"");SE(VAL.PARI(ANNO(A1));SE(E(MESE(ANNO(A1))>2;MESE(ANNO(A1))<=12);11;6);""))));MESE(A1);GIORNO(A1))

P.S.
Se si vuole visualizzare solo l'anno si può agire dal "Formato cella..." oppure modificare qualcosina nella formula...
 

Allegati

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.842
113
Como
2011MAC 2016WIN
353
Penso che sei sulla strada giusta Cromagno ... anch'io stavo lavorando su DATA(ANNO) + RESTO .... credo sia l'unica senza Range visto che non si può usare INDICE/CONFRONTA/AGGREGA ecc. ... ma non è detto che Ninai non ci sorprenda! :90:
 

ninai

Excel Expert
Moderatore
Expert
11 Luglio 2015
2.501
63
56
Barcellona P.G.
2010 PC
69
e sì che siete sulla strada giusta.........

comincio a dare la mia, quella lunga:
=DATA(ANNO(C1)+6-((RESTO(ANNO(C1);4)=0)*(MESE(C1)<3)+(RESTO(ANNO(C1);4)=3)*(MESE(C1)>2))+5*((RESTO(ANNO(C1);4)=3)*(MESE(C1)<3)+(RESTO(ANNO(C1);4)=2)*(MESE(C1)>2));MESE(C1);GIORNO(C1))
 

cromagno

Excel/VBA Expert
Supermoderatore
Expert
9 Agosto 2015
6.426
83
39
Sardegna
2013 (64 bit)
203
e sì che siete sulla strada giusta.........

comincio a dare la mia, quella lunga:
=DATA(ANNO(C1)+6-((RESTO(ANNO(C1);4)=0)*(MESE(C1)<3)+(RESTO(ANNO(C1);4)=3)*(MESE(C1)>2))+5*((RESTO(ANNO(C1);4)=3)*(MESE(C1)<3)+(RESTO(ANNO(C1);4)=2)*(MESE(C1)>2));MESE(C1);GIORNO(C1))
Già questa "lunga" mi piace :255:

Comunque, ho fatto delle prove con la mia e la tua formula....
La tua formula funziona perfettamente (sempre nell'arco dello stesso secolo), mentre quella proposta da me da risultati errati se il mese della data iniziale è "gennaio" o "febbraio"... mi sa che dovrò cercare un'altra "guida" :251:

Solo per capire meglio... nella tua formula la logica principale è capire in che "posizione" si trova l'anno rispetto all'anno bisestile???
Esempio:
RESTO(ANNO(C1);4)=3
???
 

cromagno

Excel/VBA Expert
Supermoderatore
Expert
9 Agosto 2015
6.426
83
39
Sardegna
2013 (64 bit)
203

ninai

Excel Expert
Moderatore
Expert
11 Luglio 2015
2.501
63
56
Barcellona P.G.
2010 PC
69
come promesso, ecco la soluzione corta che mi è venuta, come scritto prima, l'ispirazione mi è venuta dal link pubblicato e sfruttando le 8 combinazioni possibili fra la distanza dell'anno con quello bisestile (0-1-2-3) e le due casistiche (mese prima o dopo marzo).

=DATA(ANNO(A1)+SCEGLI(RESTO(ANNO(A1);4)*2+1+(MESE(A1)>2);5;6;6;6;6;11;11;5);MESE(A1);GIORNO(A1))

allego file con tutte le soluzioni.

https://www.dropbox.com/s/3a8ypqvfxtzz4o5/ripetizione giorni negli anni.xlsx?dl=0

ovviamente è ulteriormente accorciabile, se non usassimo DATA() per estrarre anno.
 

Sostieni ForumExcel

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