Quelle est la fonction pour obtenir le numéro de ligne actuel et le nom de la colonne actuelle pour une cellule dans Excel ?
Essayez la fonction suivante :
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Explication : ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4 = adresse relative. Cela signifie qu'il n'y a pas de ‘$’ dans la valeur retournée. Pour la colonne “AB”, le ADDRESS renvoie “AB1”. Le substitut supprime le “1”.
Pour ce faire, vous pouvez utiliser les fonctions ROW et COLUMN. Si vous omettez l'argument pour ces formules, la cellule actuelle est utilisée. Celles-ci peuvent être utilisées directement avec la fonction OFFSET , ou toute autre fonction où vous pouvez spécifier à la fois la ligne et la colonne comme valeurs numériques.
Par exemple, si vous entrez =ROW() dans la cellule D8, la valeur retournée est 8. Si vous entrez =COLUMN() dans la même cellule, la valeur retournée est 4.
Si vous voulez la lettre de la colonne, vous pouvez utiliser la fonction CHAR. Je ne recommande pas l'utilisation de lettres pour représenter la colonne, car les choses se compliquent lorsque l'on passe à des noms de colonne à deux lettres (où il est de toute façon plus logique d'utiliser des chiffres).
Quoi qu'il en soit, si vous souhaitez toujours obtenir la lettre de la colonne, vous pouvez simplement ajouter 64 au numéro de la colonne (64 étant un caractère de moins que A), donc dans l'exemple précédent, si vous fixez la valeur de la cellule à =CHAR(COLUMN()+64), la valeur renvoyée serait D. Si vous vouliez que la valeur d'une cellule soit l'emplacement de la cellule elle-même, la formule complète serait =CHAR(COLUMN()+64) & ROW().
Juste pour info, j'ai obtenu 64 à partir d'une table ASCII. Vous pouvez également utiliser la formule CODE, ainsi la formule mise à jour en utilisant ceci serait =CHAR(COLUMN() + CODE("A") - 1). Vous devez soustraire 1 puisque la valeur minimale de COLUMN est toujours 1, et que la valeur de retour minimale de toute la formule serait donc B.
Cependant, cela ne fonctionnera pas avec des colonnes de deux lettres. Dans ce cas, vous avez besoin de la formule suivante pour analyser correctement les colonnes de deux lettres :
=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()
Je ne sais pas s'il y a une manière plus simple de le faire ou non, mais je sais que cela fonctionne de la cellule A1 à ZZ99 sans problème. Cependant, cela illustre pourquoi il vaut mieux éviter d'utiliser des identifiants de colonne basés sur des lettres et s'en tenir à des formules purement numériques (par exemple, utiliser le numéro de colonne au lieu de la lettre avec OFFSET).
Essayez ceci
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
Cela vous donne l'en-tête de colonne exact, sans aucun $ etc.
Légèrement manuel mais moins VBA et une formule plus simple :
=column() =Address(1,A1) $A$1 Une fois les valeurs copiées, trouvez et remplacez $ et 1 par un blanc.
Pour obtenir le nom de la colonne, j'ai utilisé les formules suivantes :
Pour une cellule particulière :
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Pour la cellule actuelle :
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Essayez cette variante. Elle fonctionne sur des colonnes de 3 lettres, et ne laisse pas de “$” au début :
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Tapez ceci dans n'importe quelle cellule :
Español :
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
English :
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
Vous pouvez remplacer la colonne() par le numéro de ligne.
Une autre possibilité serait d'utiliser quelque chose comme ceci :
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
Où colid fait référence à une plage nommé que vous créeriez ailleurs dans le classeur, comprenant deux colonnes adjacentes à plusieurs lignes : la première colonne contenant les chiffres 1 à n correspondant au numéro COLUMN(), la seconde contenant les lettres A - ZZ, ou le nombre de références de colonne que vous souhaitez adapter. Le ROW() est très bien laissé tel quel pour renvoyer le numéro de ligne.
Donc si vous copiez la chaîne ci-dessus dans la cellule A1 de “Ma feuille 2”, elle sera évaluée comme =MySheet1!A1 et renverra la valeur qu'elle a trouvée dans la cellule correspondante de MySheet1.
Cela vous permettrait, par exemple, d'utiliser MySheet1 comme zone de travail, de supprimer et de réinsérer de nouvelles données, tandis que tout formatage ou calcul dans MySheet2 qui se réfère à ces contenus continuera à fonctionner correctement avec les nouveaux ensembles de données de la feuille de travail à onglet cible.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
La formule d'adresse fonctionne en renvoyant le nom de la colonne et de la ligne. Le format retourné sera toujours $(Column Letters)$(Row Numbers) - par exemple $AA$2 ou $XAA$243556
Si nous savons que $ se trouvera toujours au premier caractère, nous pouvons utiliser la première formule du milieu pour commencer à tirer les caractères après le premier signe $ (c'est-à-dire le 2ème caractère).
Ensuite, nous trouvons le signe $ suivant (comme nous savons qu'il n'y en aura que deux) et nous savons combien de caractères il y a entre le premier et le deuxième signe $. Le reste est une simple soustraction.
Voici une VBA, formule définie par l'utilisateur, solution. Elle fonctionne avec des colonnes de 1, 2 et 3 lettres.
Mettez ce qui suit dans un module de code :
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() dans n'importe quelle cellule retournera la lettre de la colonne de la cellule. =COLUMNLETTER(B3) dans n'importe quelle cellule retournera B.
Cette fonction définie par l'utilisateur fonctionne très bien lors de la création de formules génériques dans la fonction INDIRECTE.
La formule suivante fonctionne quel que soit l'endroit où vous la mettez (elle vous rendra le nom de la colonne).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")