[excel] identificare l'ultima colonna piena

[ramset64]

Come faccio ad identificare l'ultima colonna piena di un foglio?
se utilizzo conta.valori(a1:cc1) ottengo il numero relativo all'ultima colonna che contiene dati, ma poi come faccio ad associare quel numero alla lettera che identifica la colonna?
In pratica in un determinato foglio mi serve conoscere la lettera identificativa delle ultime 3 colonne che contengono dati.
Si può fare?
Grazie

[Tribuno]

Ciao ramset64,

per l'ultima colonna
=SINISTRA(INDIRIZZO(1;CONTA.VALORI(A1:CC1);4);LUNGHEZZA(INDIRIZZO(1;CONTA.VALORI(A1:CC1);4))-1)

per la penultima colonna
=SINISTRA(INDIRIZZO(1;CONTA.VALORI(A1:CC1)-1;4);LUNGHEZZA(INDIRIZZO(1;CONTA.VALORI(A1:CC1)-1;4))-1)

per la terzultima colonna
=SINISTRA(INDIRIZZO(1;CONTA.VALORI(A1:CC1)-2;4);LUNGHEZZA(INDIRIZZO(1;CONTA.VALORI(A1:CC1)-2;4))-1)

Ciao
Tribuno

[ramset64]

Grazie Tribuno, molto gentile.
Ciao

[ramset64]

Tribuno approfitto un attimo... trovata la lettera dell'ultima colonna non riesco poi ad inserirla nella formule...
se volessi ad esempio sapere il massimo valore presente nell'ultima colonna ( =max(s:s) )
se ad s sostituisco la tua formula che mi restitisce la colonna s non funziona... come cavolo si fa?

Grazie, ciao

[Anthony47]

Lo sapevo che il problema non era identificare l' ultima colonna...
Se l' oggetto su cui lavori e' uno dei fogli riepilogo che conosco (cioe' tutte le righe e colonne hanno le stesse dimensioni), allora una formula come questa calcola il max dell' ultima colonna usata

Codice: Seleziona tutto

=MAX(SCARTO(A1;0;CONTA.VALORI(1:1)-1;65000))

Se devi lavorare su altre colonne, modifica quel "-1" in valori piu' negativi.

Ciao.

[ramset64]

No, Antony mi serviva proprio identificare la colonna e solo che poi non riesco ad inserirla nei calcoli....

=SOMMA(SE(ANNO(A2:A36)=V19,S2:S36))/SOMMA(--(ANNO(A2:A36)=2009))
In questa formula io devo sostituire la lettera S con la formula che identifica l'ultima colonna piena del foglio2.

Già che ci sono, ANNO(A2:A36), come faccio a sostituire il 36 con una etichetta.... 36 in realtà rappresenta il numero dei giorni che io ho identicato con "day". se in un cella scrivo ="A"&Day ottengo effettivamente A36, se invece inserisco quella stringa nella formula sopraindicata mi da errore, come lo devo scrivere?

Ciao e grazie

[ramset64]

umh... scusatemi ma conosco poco excel e meno vba.... diciamo che ho capito come utilizzare la funzione scarto .... mi resta però da capire come sostituire la parte numerica di una cella con una etichetta.....es. max (A2:A36), come detto 36 è uguale a "day", come sostituisco il 36 con day?
grazie e scusatemi ancora

[Anthony47]

La formula e' introdotta con Contr-Maiusc-Enter?

Comunqe prova questa, sempre basata su SCARTO:

Codice: Seleziona tutto

=SOMMA(SE(ANNO(SCARTO(A2;0;0;day;1))=V19;SCARTO(S2;0;0;day;1)))/SOMMA(--(ANNO(SCARTO(A2;0;0;day;1))=2009))


(Contr-Maiusc-Enter)
Insomma usi il valore della cella day per determinare l' altezza del range restituito da Scarto.

Ciao.

[ramset64]

La formula e' introdotta con Contr-Maiusc-Enter?

Si. A proposito, come si inserisce una formula di questo tipo con vba? Ho provato a metterla tra parentesi graffe ma mi da errore.

Comunqe prova questa, sempre basata su SCARTO:

Codice: Seleziona tutto

=SOMMA(SE(ANNO(SCARTO(A2;0;0;day;1))=V19;SCARTO(S2;0;0;day;1)))/SOMMA(--(ANNO(SCARTO(A2;0;0;day;1))=2009))


(Contr-Maiusc-Enter)
Insomma usi il valore della cella day per determinare l' altezza del range restituito da Scarto.
Ciao.

Si perfetto grazie. Siete mitici!
Ciao

[Anthony47]

Quando non sai come si codifica una cosa procedi con la registrazione di una macro; nel caso in oggetto scoprirai la proprieta' FormulaArray.
Ancora oggi il 50% del mio codice nasce da un registra macro.

Ciao.

[ramset64]

Quando non sai come si codifica una cosa procedi con la registrazione di una macro; nel caso in oggetto scoprirai la proprieta' FormulaArray.
Ancora oggi il 50% del mio codice nasce da un registra macro.

Ciao.

Che stupido non ci avevo pensato.... grazie.
Purtroppo però la formula non è quella che mi serve perchè cambiandola di cella cambia il risultato e non dovrebbe essere cosi. Il valore Day non cambia da riga a riga.
Questa è come dovrebbe essere:
=SOMMA(SE(ANNO(A2:A36)=V19,S2:S36))/SOMMA(--(ANNO(A2:A36)=2009))
Questa è come finora abbiam pensato con la formula scarto (supponendo che non conosciamo l'ultima colonna piena):
=SOMMA(SE(ANNO(SCARTO(A2;0;0;day;1))=2009;SCARTO(SCARTO(A1;0;CONTA.VALORI(1:1)-1);0;0;day;1)))/SOMMA(--(ANNO(SCARTO(A2;0;0;day;1))=2009))

Ho mal interpretato il tutto o la formula non si adatta?

Una volta identificata l'ultima colonna piena (quindi S) non è possibile in qualche modo aggiungergli il numero di riga?
es. (S&2), semplificherebbe molto la cosa.

Grazie, ciao

[Anthony47]

La formula SCARTO che ti ho dato si adatta sia alla larghezza colonne che al contenuto di day; hai esperienza diversa?

Ciao.

[ramset64]

Direi di si.... la formula cosi come sopra descritta dovrebbe sommarmi tutti i valori della colonna s di un determinato anno e dividerla per il numero di giorni (day). In realtà questo risultato ha diverso valore a seconda della cella dove la inserisco mentre dovrebbe restutuire sempre lo stesso risultato a prescindere da dove inserisco la formula.
ciao

[Anthony47]

Ho reso assoluti gli indirizzi della base scarto, poi c' era uno "SCARTO(SCARTO" che non mi quandrava.

Codice: Seleziona tutto

=SOMMA(SE(ANNO(SCARTO($A$2;0;0;day;1))=2009;SCARTO($A$1;1;CONTA.VALORI($1:$1)-1;day;1)))/SOMMA(--(ANNO(SCARTO($A$2;0;0;day;1))=2009))


(Contr-Maiusc-Enter)

Ri-prova...

Ciao.

[ramset64]

Adesso sembra funzionare ma..... come detto nei post precedenti, la colonna S è una colonna "sconosciuta", quindi non posso utilizzarla in questo modo.
Grazie, ciao

[Anthony47]

Mi sono perduto...
La colonna S non so che cosa sia; la formula fa i calcoli "sull' ultima colonna che trova" che sia la J o la X o financo la S, su un numero di righe che e' definito in "day".

Devi fare una cosa differente? Se Si, che cosa?

Ciao.

[ramset64]

Sono io che mi sono perso. Scusami Anthony.
La formula è corretta e funziona, non riesco però ad inserirla da vba (e mi serve inserirla da li purtroppo).

Se scrivo:
Range(Vola).FormulaLocal = "=SE(" & Anno & "="""","""",SOMMA(SE(ANNO(SCARTO(Foglio2!$A$2,0,0,Day,1))=" & Anno & ",SCARTO(Foglio2!$A$1,1,CONTA.VALORI(Foglio2!$1:$1)-1,Day,1)))/SOMMA(--(ANNO(SCARTO(Foglio2!$A$2,0,0,Day,1))=" & Anno & ")))"
Il dato viene inserito correttamente (ma ovviamente il risultato è sbagliato).

Se scrivo:
Range(Vola).FormulaArray = "=SE(" & Anno & "="""","""",SOMMA(SE(ANNO(SCARTO(Foglio2!$A$2,0,0,Day,1))=" & Anno & ",SCARTO(Foglio2!$A$1,1,CONTA.VALORI(Foglio2!$1:$1)-1,Day,1)))/SOMMA(--(ANNO(SCARTO(Foglio2!$A$2,0,0,Day,1))=" & Anno & ")))"
Mi viene restituito #NOME?

Probabilmente formula array va inserita in altro modo.
Ciao

[Flash30005]

Se la formula di Anthony ti funziona (premendo Ctrl+Maiu+Enter) e ti occorre il codice Vba da inserire in una macro
Inizi la registrazione di una macro
entri nella cella con la formula (come per editarla)
premi CTRL+Maiu+Enter
Fermi la registrazione della macro
vai in modifica della macro e trovi (se non mi sono sbagliato) questo:

Codice: Seleziona tutto

    Selection.FormulaArray = _
        "=SUM(IF(YEAR(OFFSET(R2C1,0,0,day,1))=2009,OFFSET(R1C1,1,COUNTA(R1)-1,day,1)))/SUM(--(YEAR(OFFSET(R2C1,0,0,day,1))=2009))"


Ciao

P.s. Un consiglio: non usare variabili che hanno nomi uguali alle parole chiave del vba

[ramset64]

Grazie Flash... risolto e grazie ovviamente anche al grande Anthony.
Ciao

[ramset64]

Raga, già che ci siamo....
con questa formula dico ad un grafico di plottarmi la colonna A che contiene valori:
=SCARTO(Calcoli!$A$1;0;0;CONTA.NUMERI(Calcoli!$A:$A)+1)
Ho provato a sostituire il valore a con la funzione scarto per ottenere il grafico della quart'ultima colonna piena anzichè della colonna A senza però riuscire, come va modificata?

Grazie, Ciao