Wat is de functie om het huidige regelnummer en de huidige kolomnaam voor een cel in Excel te krijgen?
Probeer de volgende functie:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Uitleg: ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4 = relatief adres. Dat betekent dat er geen ‘$’s in de geretourneerde waarde zitten. Voor kolom 'AB’ geeft ADDRESS ‘AB1’ terug. De vervanger verwijdert de ‘1’.
U kunt hiervoor de ROW en COLUMN functies gebruiken. Als u het argument voor die formules weglaat, wordt de huidige cel gebruikt. Deze kunnen direct worden gebruikt met de OFFSET-functie , of een andere functie waarbij u zowel de rij als de kolom als numerieke waarden kunt opgeven.
Als u =ROW() bijvoorbeeld in cel D8 invoert, is de teruggegeven waarde 8. Als u =COLUMN() in dezelfde cel invoert, is de teruggegeven waarde 4.
Als u de kolomletter wilt gebruiken, kunt u de CHAR-functie gebruiken. Ik raad het gebruik van letters om de kolom weer te geven niet aan, omdat het lastig wordt om in kolommen met dubbele letters te komen (waar alleen het gebruik van cijfers logischer is).
Hoe dan ook, als je toch de kolomletter wilt krijgen, kun je eenvoudigweg 64 toevoegen aan het kolomnummer (64 is een teken minder dan A), dus in het vorige voorbeeld, als je de waarde van de cel instelt op =CHAR(COLUMN()+64), zou de waarde die je terugkrijgt D zijn. Als je wilt dat de waarde van een cel de cellocatie zelf is, zou de volledige formule =CHAR(COLUMN()+64) & ROW() zijn.
Gewoon een FYI, ik heb 64 van een ASCII-tabel. Je kon ook de CODE formule gebruiken, dus de geüpdatete formule die dit zou gebruiken zou =CHAR(COLUMN() + CODE("A") - 1) zijn. Je moet er 1 van aftrekken omdat de minimumwaarde van COLUMN altijd 1 is, en dan zou de minimumopbrengstwaarde van de hele formule B zijn.
werkt echter niet met tweeletterige kolommen. In dat geval heb je de volgende formule nodig om tweeletterige kolommen goed te parseren:
=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()
Ik weet niet zeker of er een makkelijkere manier is om het te doen of niet, maar ik weet dat dat werkt van cel A1 tot ZZ99 zonder problemen. Dit illustreert echter waarom het het beste is om het gebruik van op letters gebaseerde kolom-identificaties te vermijden, en om vast te houden aan zuivere op getallen gebaseerde formules (bijv. het gebruik van het kolomnummer in plaats van de letter met OFFSET).
Probeer deze
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
Dit geeft je een exacte kolomkop, zonder enige $ enz.
Iets handiger maar minder VBA en een eenvoudiger formule:
=column()
in - In de rij hieronder voer je =Address(1,A1)
in - Dit levert het resultaat $A$1 op Zodra de waarden zijn gekopieerd, vind je $ en 1 en vervang je &007 door blanco.
Om de kolomnaam te krijgen heb ik de volgende formules gebruikt.
Voor een bepaalde cel:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Voor de huidige cel:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Probeer deze variatie. Het werkt op 3-letterige kolommen, en laat geen “$” achter op de voorkant:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Typ dit in een willekeurige cel:
Español :
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
Engels :
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
U kunt kolom() vervangen door rijnummer.
Een andere mogelijke manier zou zijn om zoiets als dit te gebruiken:
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
waarbij colid verwijst naar een naam bereik dat u elders in het werkboek zou creëren en dat bestaat uit twee aangrenzende kolommen met meerdere rijen: de eerste kolom met de cijfers 1 tot en met n die overeenkomen met het COLUMN()-nummer, de tweede met de letters A - ZZ, of hoe veel kolomverwijzingen u ook wenst op te nemen. De ROW() is prima links, want het is om het rijnummer terug te geven.
Dus als je de bovenstaande string zou kopiëren naar cel A1 van ‘MySheet2’, zou het evalueren als =MySheet1!A1, en de waarde die het gevonden heeft in de corresponderende cel van MySheet1 teruggeven.
Dit zou u bijvoorbeeld in staat stellen om MySheet1 te gebruiken als een werkgebied, om nieuwe gegevens te verwijderen en opnieuw in te voeren, terwijl elke opmaak of berekening in MySheet2 die naar deze inhoud verwijst, correct zal blijven werken met de nieuwe datasets van het werkblad met de doeltabellen.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
De adresformule werkt door de kolom- en rijnaam terug te geven. Het teruggegeven formaat zal altijd $(Column Letters)$(Row Numbers) zijn - bijvoorbeeld $AA$2 of $XAA$243556
Als we weten dat er altijd $ zal voorkomen bij het eerste teken, kunnen we de eerste middenformule gebruiken om te beginnen met het trekken van karakters na het eerste $ teken (dus het 2e teken).
Dan vinden we het volgende $ teken (zoals we weten zullen er slechts twee zijn) en weten we hoeveel karakters er zijn tussen het eerste en het tweede dollarteken. De rest is een eenvoudige aftrekking.
Hier is een VBA, user defined formula, oplossing. Het werkt met 1, 2, & 3 letter kolommen.
Zet het volgende in een code module:
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 een willekeurige cel geeft de kolomletter van de cel terug. =COLUMNLETTER(B3) in een willekeurige cel geeft B.
Deze User Defined Function werkt geweldig bij het maken van generieke formules binnen de INDIRECT-functie.
De volgende formule werkt ongeacht waar je het plaatst (het geeft je de kolomnaam terug).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")