Qual è la funzione per ottenere il numero di riga corrente e il nome della colonna corrente per una cella in Excel?
Risposte (16)
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'.
Soluzione trovata!
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.
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.
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),"")
Leggermente manuale ma meno VBA e una formula più semplice:
- In una riga di Excel, ad esempio la cella A1, inserire il numero di colonna
=column() - Nella riga sottostante, inserire
=Address(1,A1) Questo fornirà il risultato
$A$1Una volta copiati i valori, trovare e sostituire
$e1con il vuoto.
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.
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.
=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.
La seguente formula funziona indipendentemente da dove la si mette (vi restituirà il nome della colonna).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")