Numero sequenziale di una colonna

[Marco]

è possibile ricavare un valore uguale al numero dell'ultima colonna occupata da un valore?

Non parlo di tutte le colonne di un foglio ovviamente ma solo tra alcune selezionate (quelle con i valori)

ESEMPIO

A B C D E F G H I L M N O P Q
1 a 1 a 3 f 4 y
1 b 2 b 3 z 5 t
1 c 2 c 3 k 6 à

OK, il risultato che a me interessa ricavare è 4, ovvero l'ultima colonna occupata dai numeri
Attenzione, dove io ho messo le lettere in realtà potrebbero esserci anche dei numeri, ho messo così qui per rendere meno complicato spiegare l'esempio.

[issdr]

quindi tu selezioni le colonne da A ad H e vuoi avere 4 come output?

--8<---------------cut here---------------start------------->8---
Sub contaColonne()
Dim colonne As Object
Set colonne = CreateObject("Scripting.Dictionary")
With Selection
On Error Resume Next
Set costanti = Nothing
Set costanti = .SpecialCells(xlCellTypeConstants, xlNumbers)
Set formule = Nothing
Set formule = .SpecialCells(xlCellTypeFormulas, xlNumbers)
On Error GoTo 0
End With
If costanti Is Nothing And formule Is Nothing Then
Exit Sub
Else
If costanti Is Nothing Then
Set intervallo = formule
Else
If formule Is Nothing Then
Set intervallo = costanti
Else
Set intervallo = Union(formule, costanti)
End If
End If
End If
For Each a In intervallo.Areas
For Each col In a.Columns
colonne(col.Column) = ""
Next col
Next a
intervallo.Select
MsgBox colonne.Count
End Sub
--8<---------------cut here---------------end--------------->8---

se non vuoi il cambio di selezione, anteponi un ' a intervallo.Select

[Marco]

non ci ho capito niente. con quegli if sembra visual basic. mi sa che devo trovare un altro modo per far uscire quel valore ma non mi viene in mente altro.
spiegare tutta la formula senza un file di esempio è difficilissimo, non perchè usa cose complicate, ma perchè c'è un sacco di roba dentro (molti intervalli) e senza averla sotto mano è facile confondersi. non si può allegare un file qui?

[issdr]

Marco wrote:

> non ci ho capito niente. con quegli if sembra visual basic. mi sa che
> devo trovare un altro modo per far uscire quel valore ma non mi viene
> in mente altro.

Alt + F11, inserichi un modulo, ci copi il codice. Selezioni le colonne
da A a quel che vuoi, Alt + F8 e lanci la macro.

se va bene la posso convertire in funzione definita dall'utente, così la
puoi mettere in una cella.

> spiegare tutta la formula senza un file di esempio è difficilissimo,
> non perchè usa cose complicate, ma perchè c'è un sacco di roba dentro
> (molti intervalli) e senza averla sotto mano è facile confondersi.

penso di aver capito cosa vuoi fare. è complicato farlo per
l'architettura del visual basic for applications. possibile si possa
fare anche via formule, ma non ho idee.

> non si può allegare un file qui?

lo carichi da qualche parte e condividi il link. rimuovi dati che non
vuoi divulgare.

[Marco]

prova a vedere se riesci a scaricarlo da qui, poi spiego
https://www.dropbox.com/scl/fi/tho8qrncb8bjzwr22mufp/conta-colonne.xlsx?dl=0&rlkey=0ahz28ua45kth1txtvsqkqbfv

[issdr]

Marco wrote:

> prova a vedere se riesci a scaricarlo da qui, poi spiego
> https://www.dropbox.com/scl/fi/tho8qrncb8bjzwr22mufp/conta-colonne.xlsx?dl=0&rlkey=0ahz28ua45kth1txtvsqkqbfv

ho applicato la macro dalla colonna G alla fine del foglio, togliendo il
valore "giorno ult ep" dall'intevallo: il risultato è 31: hai sedici
mesi, sono state contate le colonne contenenti i giorni del mese e
quelle con i relativi valori numerici ove presenti.

[Marco]

le colonne da contare sono solo le terze di ogni mese, non quelle coi giorni, il risultato deve essere 28. la formula è quella che vedi li, solo che io ho messo 15 manualmente, invece dovrebbe rilevare automaticamente qual è l'ultima colonna che ha all'interno dei valori

[issdr]

=INDICE(SCEGLI(CONTA.VALORI(G2:W2:XFD2);H3...(e a seguire)

prendila come una critica costruttiva: non ti spieghi bene. forse
dovresti concentrarti meno sullo specifico e dare più informazioni sul
problema principale che hai (o meglio, indicare le tue finalità)

https://wwwcdf.pd.infn.it/MLO/smart-questions.html

[issdr]

issdr wrote:

> =INDICE(SCEGLI(CONTA.VALORI(G2:W2:XFD2);H3...(e a seguire)

il :W2 è pleonastico

[issdr]

Marco wrote:

> le colonne da contare sono solo le terze di ogni mese, non quelle coi
> giorni, il risultato deve essere 28. la formula è quella che vedi li,
> solo che io ho messo 15 manualmente, invece dovrebbe rilevare
> automaticamente qual è l'ultima colonna che ha all'interno dei valori

ho riletto la formula con più attenzione. la mia intenzione è darti il
numero delle colonne mese meno l'ultima. andrebbe bene se sai di non
avere vuoti.

=INDICE(SCEGLI(CONTA.VALORI(G2:XFD2)-1;H3:H30;L3:L33;P3:P32;T3:T34;X3:X32;AB3:AB34;AF3:AF33;AJ3:AJ32;AN3:AN34;AR3:AR32;AV3:AV33;AZ3:AZ33;BD3:BD30;BH3:BH33;BL3:BL32;BP3:BP33);CONFRONTA.X(MAX(I3:I30;M3:M33;Q3:Q32;U3:U34;Y3:Y32;AC3:AC34;AG3:AG33;AK3:AK32;AO3:AO34;AS3:AS32;AW3:AW33;BA3:BA33;BE3:BE30;BI3:BI33;BM3:BM32;BQ3:BQ33);SCEGLI(CONTA.VALORI(G2:XFD2)-1;H3:H30;L3:L33;P3:P32;U3:U34;Y3:Y32;AC3:AC34;AG3:AG33;AK3:AK32;AO3:AO34;AS3:AS32;AW3:AW33;BA3:BA33;BE3:BE30;BI3:BI33;BM3:BM32;BQ3:BQ33);0;-1))

[Marco]

l'ho detto che non era facile senza far vedere l'esempio , ma quello che ho scritto era giusto "ultima colonna con un valore". tu mi sa che hai letto solo l'ultima parte dove scrivo di voler sostituire il 15.
Conta.valori l'avevo già provata ma così mi viene sempre 16 e se metto -1 viene sempre 15...ma se l'ultima colonna occupata è gennaio a me serve che il valore sia 12. Se fosse un valore statico non avrei problemi, ma è un valore dinamico

[Marco]

Forse ho trovato la soluzione. invece di contare i mesi in orizzontale, come avevo pensato anche io, basta contare quelli che hanno un valore maggiore di 0 nella tabella dei mesi in basso a sinistra
=conta.se(E20:E35;">0")

[issdr]

Marco wrote:

> Forse ho trovato la soluzione. invece di contare i mesi in
> orizzontale, come avevo pensato anche io, basta contare quelli che
> hanno un valore maggiore di 0 nella tabella dei mesi in basso a
> sinistra
> =conta.se(E20:E35;">0")

manda aggiornamento se riesci. intanto: puoi avere dei mesi vuoti e vuoi
venire avvisato nel caso? oppure è sempre e solo l'ult ep segnato ad
interessarti?

[Marco]

https://www.dropbox.com/scl/fi/iq1vbihg0v8hpcmqoigxt/conta-colonne.xlsx?dl=0&rlkey=gpzscxl6pocn3vkxpi2wjq4qd

La formula da guardare è quella in E8 adesso, tutta completa: in pratica il valore visulizzato deve essere la distanza dal giorno attuale al giorno contenente l'ultimo valore. Ho dovuto fare 2 formule diverse a seconda del fatto se il giorno in questione è nel mese corrente o in uno di quelli precedenti. Funzionano entrambe le formule (se le ho riportate bene...prima funzionavano), solo che non mi convince la scelta iniziale

SE(CONTA.VALORI(B20:D35)=CONTA.SE(E20:E35;">0")

In questo modo non riesco a preparare il foglio per i mesi futuri perchè se aggiungo un mese in basso a sinistra, verrà considerata sempre la seconda formula anche se in realtà l'ultimo valore è nel mese corrente (che però non verrà più considerato tale). Certo posso sempre preparare le colonne e soprattutto le formule (la cosa più lunga). Aggiungere il nome del mese li sotto è il minimo, però un po' mi scoccia doverlo fare solo quando entro in quel mese

[issdr]

Marco wrote:

> In questo modo non riesco a preparare il foglio per i mesi futuri
> perchè se aggiungo un mese in basso a sinistra, verrà considerata
> sempre la seconda formula anche se in realtà l'ultimo valore è nel
> mese corrente (che però non verrà più considerato tale). Certo posso
> sempre preparare le colonne e soprattutto le formule (la cosa più
> lunga). Aggiungere il nome del mese li sotto è il minimo, però un po'
> mi scoccia doverlo fare solo quando entro in quel mese

ho scritto questa da mettere in E8, lavora al margine, senza dover
buttare dentro intervalli, né dover cambiare alcunché quando crei le
colonne nuove. considera anche i passaggi d'anno. non devi scrivere mai
nulla sotto i giorni dei mesi più corti di 31, perché va a guardare
anche il 31 febbraio.

=OGGI()-SE.ERRORE(DATA(SE(CERCA(2;1/(2:2<>"");2:2)="GENNAIO";ANNO(OGGI())-1;ANNO(OGGI()));MESE(CERCA(2;1/(2:2<>"");2:2)&0);CONFRONTA(0;SCARTO(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)));1;2;31;1);-1));DATA(SE(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)-4))="DICEMBRE";ANNO(OGGI())-1;ANNO(OGGI()));MESE(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)-4))&0);CONFRONTA(0;SCARTO(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)-4));1;2;31;1);-1)))

fai qualche prova.

[issdr]

issdr wrote:

> fai qualche prova.

il passaggio d'anno richiede qualche riflessione in più. per ora riporto
qui la formula che permette di avere l'ultimo giorno:

=SE.ERRORE(CONFRONTA(0;SCARTO(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)));1;2;31;1);-1);CONFRONTA(0;SCARTO(INDIRETTO(INDIRIZZO(2;CONFRONTA.X("*";2:2;2;-1)-4));1;2;31;1);-1))

[issdr]

Marco wrote:

> In questo modo non riesco a preparare il foglio per i mesi futuri
> perchè se aggiungo un mese in basso a sinistra, verrà considerata
> sempre la seconda formula anche se in realtà l'ultimo valore è nel
> mese corrente (che però non verrà più considerato tale). Certo posso
> sempre preparare le colonne e soprattutto le formule (la cosa più
> lunga). Aggiungere il nome del mese li sotto è il minimo, però un po'
> mi scoccia doverlo fare solo quando entro in quel mese

questa formula da utilizzare in E8 agisce con l'unico vincolo di avere
sempre come ultima intestazione di colonna il nome del mese corrente
(che abbia registrazioni o non le abbia). puoi creare le colonne future,
ma lasciando il nome del mese vuoto. non occorre modificare alcun'altra
formula.

=OGGI()-SE.ERRORE(DATA(ANNO(OGGI());MESE(CERCA(2;1/(2:2<>"");2:2)&0);CONFRONTA(0;SCARTO(INDIRETTO("R2C"&CONFRONTA("*";2:2;-1);0);1;2;31;1);-1));DATA(SE(INDIRETTO("R2C"&CONFRONTA("*";2:2;-1);0)="GENNAIO";ANNO(OGGI())-1;ANNO(OGGI()));MESE(INDIRETTO("R2C"&CONFRONTA("*";2:2;-1)-4;0)&0);CONFRONTA(0;SCARTO(INDIRETTO("R2C"&CONFRONTA("*";2:2;-1)-4;0);1;2;31;1);-1)))

qui c'è il tuo file, ho solo sostituito E8:

https://user.fm/files/v2-2539876937819cbe68fa1b6d8913015d/conta%20colonne%20(1).xlsx

fai qualche test, ma dovrebbe andare bene.

[Marco]

c'è troppa roba che non ho mai usato e quindi non conosco (cerca, scarto, indiretto)

[issdr]

Marco wrote:

> c'è troppa roba che non ho mai usato e quindi non conosco (cerca,
> scarto, indiretto)

la logica non è complicata. individua l'ultima colonna tramite i nomi,
trova l'ultima annotazione fatta e ne restituisce l'indice (ossia il
giorno del mese); con questo costruisco una data da sottrarre alla
corrente. fai domande se vuoi.

ho automatizzato un po' il foglio, semplificando diverse formule;
funziona a partire da giugno (non ho toccato i dati pregressi). se vuoi
creare più colonne vuote, scrivi il nome del mese e rilancia la macro,
ma ricorda di cancellare i nomi dei mesi futuri quando hai
fatto. l'unica formula che dovrai estendere è quella dei conteggi nei
mesi (il nome del mese compare in automatico nell'elenco)

occhio agli inserimenti doppi che ho notato, potrebbero rompere il
giocattolo (ma puoi intervenire manualmente sulla singola colonna,
togliendo la funzione che genera l'elenco dei giorni e generandolo
manualmente)

https://user.fm/files/v2-7ae7449f86888cfa744819400be5f432/conta%20colonne%20(2).xlsm

[issdr]

> https://user.fm/files/v2-7ae7449f86888cfa744819400be5f432/conta%20colonne%20(2).xlsm

c'era un errore.

https://user.fm/files/v2-f114b6907d8568fe21a20a41a6a4674f/conta%20colonne%20(2).xlsm

va sbloccato dalle proprietà (click destro sul file)

[Marco]

Ciao, in realtà non ho capito cosa stai facendo. Immagino tu ti stia diventendo un po' con excel, a volte lo faccio anche io, ma non mi è utile. Davvero senza offesa, se non sono io a chiedere cose specifiche da cui poi posso ripartire, non mi ci ritrovo. è inutile che scarico i tuoi aggiornamenti. ho preso il primo e ho capito che è ancora così...
nel file che ho messo qui c'era solo un foglio ma in realtà in quello originale ce ne sono diversi e devo adattare le formule in base ai mesi (non sono sempre gli stessi).

Poi ho scoperto che nella mia formula c'era una falla ma non riesco a risolverla

[issdr]

Marco wrote:

> Poi ho scoperto che nella mia formula c'era una falla ma non riesco a
> risolverla

penso il calcolo dei giorni dall'ultima registrazione, che ho risolto
con la mia.

sì, ho passato un po' di tempo, mi spiace tu non ci provi neppure, ma è
un mondo libero. per inciso, se entri nella logica di alcune cose che ti
propongo, riutilizzi il codice e non passi le mezz'ore a selezionare
intervalli.

ciao

[Marco]

Ci ho provato tutto il giorno ma solo con formule e ragionamenti che conosco. Se poi faccio un ragionamento che mi sembra funzionare ma non so come metterlo su excel allora chiedo, e così imparo nuove formule. Facendo questi fogli ho imparato INDICE e CONFRONTA, che non avevo mai usato prima e mi devono ancora entrare bene in testa (visto che qui li sto usando in mezzo a tanti valori e formule lunghe, di per se sono formule semplici)
Ma se mi trovo di fronte formule che non conosco che si basano su ragionamenti che non ho fatto io, non mi ci metto nemmeno. Mi spiace se te la sei presa, ma ti posso garantire che non imparerei niente di nuovo semplicemente prendendo un file fatto da altre persone.
Il problema era andare indietro di olltre un mese....Ad esempio: mese corrente vuoto ma pure mese precedente vuoto...i giorni del mese precedente non venivano aggiunti. Ho provato a capire come farli aggiungere ma quello che ho pensato andava aggiornato poi ogni mese e non era una vera soluzione.

[issdr]

Marco wrote:

> Il problema era andare indietro di olltre un mese....Ad esempio: mese
> corrente vuoto ma pure mese precedente vuoto...i giorni del mese
> precedente non venivano aggiunti. Ho provato a capire come farli
> aggiungere ma quello che ho pensato andava aggiornato poi ogni mese e
> non era una vera soluzione.

questo è risolvibile definendo un nome dinamico. ho creato un'area che
copre l'intero intervallo delle colonne con nome del mese e colleziono
dati dalle colonne che mi servono. il tuo maxXfrequenza se n'è giovato
molto.