Qual è la funzione per ottenere il numero di riga corrente e il nome della colonna corrente per una cella in Excel?
Provare la seguente funzione:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
``` &001
Spiegazione: `ADDRESS(row_num, column_num, [abs_num])`. `[abs_num] = 4` = indirizzo relativo. Ciò significa che non ci sono '$' nel valore restituito. Per la colonna 'AB', l'`ADDRESS` restituirà 'AB1'. Il sostituto rimuove il '1'.
A tale scopo è possibile utilizzare le funzioni ROW e COLUMN. Se si omette l'argomento per queste formule, si utilizza la cella corrente. Queste possono essere usate direttamente con la funzione OFFSET , o qualsiasi altra funzione in cui si possa specificare sia la riga che la colonna come valori numerici.
Ad esempio, se si inserisce =ROW() nella cella D8, il valore restituito è 8. Se si inserisce =COLUMN() nella stessa cella, il valore restituito è 4.
Se si vuole la lettera di colonna, si può usare la funzione CHAR. Sconsiglio l'uso di lettere per rappresentare la colonna, perché le cose si complicano quando si passa a nomi di colonne a due lettere (dove il solo uso di numeri è comunque più logico).
Indipendentemente da ciò, se si vuole comunque ottenere la lettera di colonna, si può semplicemente aggiungere 64 al numero di colonna (64 è un carattere in meno di A), quindi nell'esempio precedente, se si imposta il valore della cella a =CHAR(COLUMN()+64), il valore restituito sarebbe D. Se si volesse che il valore di una cella fosse la posizione della cella stessa, la formula completa sarebbe =CHAR(COLUMN()+64) & ROW().
Solo un FYI, ho ottenuto 64 da una tabella ASCII. Si poteva anche usare la formula CODE, quindi la formula aggiornata usando questa sarebbe stata =CHAR(COLUMN() + CODE("A") - 1). Bisogna sottrarre 1, poiché il valore minimo di COLUMN è sempre 1, e quindi il valore minimo di ritorno dell'intera formula sarebbe B.
Tuttavia, questo non funzionerà con colonne di due lettere. In questo caso, è necessaria la seguente formula per analizzare correttamente le colonne di due lettere:
=IF(COLUMN()>26,IF(RIGHT(CHAR(IF(MOD(COLUMN()-1,26)=0,1,MOD(COLUMN()-1,26))+64),1)="Y",CHAR(INT((COLUMN()-1)/26)+64) & "Z",CHAR(INT((COLUMN()-1)/26)+64) & CHAR(IF(MOD(COLUMN(),26)=0,1,MOD(COLUMN(),26))+64)),CHAR(COLUMN()+64))&ROW()
Non sono sicuro che ci sia un modo più semplice per farlo o meno, ma so che funziona da cella A1 a ZZ99 senza problemi. Tuttavia, questo illustra il motivo per cui è meglio evitare l'uso di identificatori di colonna basati sulle lettere, e attenersi a formule puramente numeriche (ad esempio, utilizzando il numero di colonna invece della lettera con OFFSET).
Prova questo
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
``` &001
Questo ti dà un'intestazione di colonna esatta, senza $ ecc.
Leggermente manuale ma meno VBA e una formula più semplice:
=column() =Address(1,A1) Questo fornirà il risultato $A$1
Una volta copiati i valori, trovare e sostituire $ e 1 con il vuoto.
Per ottenere il nome della colonna ho usato le seguenti formule.
Per una cella particolare:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Per la cella attuale:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Provare questa variante. Funziona su colonne di 3 lettere e non lascia un “$” sul fronte:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Digitare questo in qualsiasi cella:
Español :
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
Inglese :
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
``` &001
È possibile sostituire la colonna() con il numero di riga.
Un altro modo possibile sarebbe quello di utilizzare qualcosa di simile:
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
``` &001
Dove `colid` si riferisce ad una gamma _nominata_ che creereste altrove all'interno della cartella di lavoro che comprende due colonne adiacenti con più righe: la prima colonna contenente i numeri da 1 a n corrispondenti al numero `COLUMN()`, la seconda contenente le lettere A - ZZ, o comunque molti riferimenti di colonna che desiderate ospitare. L'`ROW()` va bene così com'è per restituire il numero di riga.
Quindi, se si dovesse copiare la stringa di cui sopra nella cella A1 di 'MySheet2', la si valuterebbe come `=MySheet1!A1`, e si restituirebbe il valore trovato nella cella corrispondente di `MySheet1`.
Questo vi permetterebbe, ad esempio, di usare `MySheet1` come area di lavoro, per cancellare e reinserire nuovi dati, mentre qualsiasi formattazione o calcolo in `MySheet2` che si riferisca a tali contenuti continuerà a funzionare correttamente con i nuovi set di dati del foglio di lavoro a schede di destinazione.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
La formula dell'indirizzo funziona restituendo il nome della colonna e della riga. Il formato restituito sarà sempre $(Column Letters)$(Row Numbers) - ad esempio $AA$2 o $XAA$243556
Se sappiamo che il $ avverrà sempre al primo carattere, possiamo usare la prima formula intermedia per iniziare a tirare i caratteri dopo il primo segno $ (cioè il secondo carattere).
Poi, troviamo il segno $ successivo (come sappiamo ce ne saranno solo due) e sappiamo quanti caratteri ci sono tra il primo e il secondo segno del dollaro. Il resto è una semplice sottrazione.
Ecco una VBA, formula definita dall'utente, soluzione. Funziona con colonne di 1, 2, & 3 lettere.
Mettere quanto segue in un modulo di codice:
Function COLUMNLETTER(Optional rng As Range) As String
'Returns the Column Letter of the top left cell in rng.
If rng Is Nothing Then Set rng = Application.Caller
COLUMNLETTER = Left(rng.Address(0, 0), IIf(rng.Column > 26, IIf(rng.Column > 702, 3, 2), 1))
End Function
=COLUMNLETTER() in qualsiasi cella restituirà la lettera di colonna della cella. =COLUMNLETTER(B3) in qualsiasi cella restituirà B.
Questa funzione definita dall'utente funziona alla grande quando si creano formule generiche all'interno della funzione INDIRETTA.
La seguente formula funziona indipendentemente da dove la si mette (vi restituirà il nome della colonna).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")