Excel's kolommen

Voorbeeldbestanden bij dit artikel:
  • Kolomnamen en -nummers
  • Inleiding

    Hier een pagina over de kolommen van Excel. Het merendeel van deze pagina is door de gemiddelde Excel gebruiker wel geweten, maar er staan toch nog heel wat interessante weetjes en VBA-code. Voor ieder wat wils eigenlijk!

    Hoeveel kolommen telt een Excel werkblad eigenlijk? Wel, het zijn er 256, dit is 2^8 (computers werken binair, dus 0 of 1, en dit zijn 2 mogelijkheden). Velen onder ons hebben reeds Excel 2007 (Excel 12) of Excel 2010 (Excel 13). Die nieuwste versies van Excel hebben veel meer kolommen: 16384 (2^14). Microsoft heeft hiermee eindelijk erkend dat 256 kolommen toch net iets te weinig is. Het liet bijvoorbeeld niet toe om 366 dagen naast mekaar in 1 rij te zetten.

    In de namen van de kolommen zit een duidelijke structuur. Eerst A, B, C, ..., Z. Vervolgens een A met nog eens A, B, C, ..., Z eraan vastgeplakt. Dan een B met A, B, C, ..., Z eraan vastgeplakt, dan hetzelfde voor de C, enz. Het stopt dus met kolom IV (of kolom XFD vanaf 2007). Als je mij niet gelooft, ga dan maar eens in een leeg blad staan en druk Control + . De functie =KOLOM() in een cel zetten geeft het kolomnummer.

    Excel opties

    Excel laat toe om te kiezen wat je op je scherm krijgt. Via Extra > Opties > weergave kan je een aantal opties beheren m.b.t. de kolommen. Onder andere of Rij- en kolomkoppen getoond moeten worden. Bij Extra > Opties > Algemeen kan je i.p.v. kolomnamen (A, B, C, …, IV) ook kolomnummers tonen. Dat is de optie verwijzingstype R1K1. Een laatste instelling is het tonen van rijen en kolommen Van rechts naar links. Dat vind je op het tabblad Internationaal. Best wel raar als je dat eens uitprobeert!

    Ook via de overige menu’s in Excel kan je acties uitvoeren op kolommen. Bekijk maar eens Opmaak > Kolom. Je kan ze verbergen of zichtbaar maken (selecteer ze eerst), of versmallen of verbreden. Invoegen > Kolommen laat je toe om kolommen in te voegen. Via Bewerken > Verwijderen kan je ze weer verwijderen. Allemaal redelijk logisch, niet? Al zullen de meesten (mezelf incluis) wel het contextmenu gebruiken dat je krijgt bij het klikken met de rechtermuisknop op een kolomtitel.

    Stukjes VBA-code

    Er is code die ik schreef om de kolomnamen (letters) om te zetten in het corresponderende getal. Dus A wordt een 1, B een 2, Z een 26, AA een 27, AB een 28, enz. Zie bovenaan in de downloads sectie.

    Function ColNameToNumber(ByVal sColName As String) As Long
    ' Wim Gielis
    ' 06/18/2007
    ' VBA-code to convert a column name to a number
    ' (not limited to 256 columns)
    ' Also on http://www.wimgielis.com
    Dim sLength As Integer Dim i As Integer Dim Numb As Long Dim sTempNumb As Integer Const iCnt As Byte = 26 sColName = UCase(sColName) sLength = Len(sColName) For i = 2 To sLength Numb = Numb + iCnt ^ (i - 1) Next For i = 1 To sLength sTempNumb = Asc(UCase(Mid(sColName, i, 1))) - 64 - 1 Numb = Numb + sTempNumb * iCnt ^ (sLength - i) Next ColNameToNumber = Numb + 1
    End Function

    Indien je minder dan een getal wil omzetten dat ten hoogste 256 is (of als je met Excel 2007 werkt), dan lukt dat met een veel simpeler trucje:

    Function ColNameToNumberSimpel(ByVal sColName As String) As Long
    ColNameToNumberSimpel = Cells(1, sColName).Column
    End Function

    Het kan zelfs met de hand: klik op de letter A van kolom A, en sleep tot en met de kolom waarvan je het nummer wil kennen. Bekijk dan in de gele tooltip hoeveel kolommen je geselecteerd hebt. Dat is het nummer dat je zoekt!

    Wat nu volgt, is de code voor het omgekeerde: een kolomnummer omzetten in de corresponderende kolomnaam. Dus 1 wordt een A, 2 een B, 26 een Z, 27 een AA, 28 een AB, enz. De code staat ook in bovengenoemd bestand.

    Function ColNumberToName(ByVal lNumb As Long) As String
    ' Wim Gielis
    ' 06/18/2007
    ' VBA-code to convert a column number to a name
    ' (not limited to 256 columns)
    ' Also on http://www.wimgielis.com
    Dim iMultiples As Byte Dim lRest As Long Dim str As String Dim i As Integer Dim iPower As Integer Dim getal As Long Const iCnt As Byte = 26 Do iPower = iPower + 1 getal = getal + iCnt ^ iPower Loop While getal < lNumb lRest = lNumb i = 1 Do Until i = iPower + 1 iMultiples = Int((lRest - (iCnt ^ (iPower - i - 1))) / (iCnt ^ (iPower - i))) lRest = lRest - iMultiples * (iCnt ^ (iPower - i)) str = str & Chr(64 + iMultiples - (i = iPower)) i = i + 1 Loop ColNumberToName = str
    End Function

    Ook deze code kan gemakkelijker als je met weinig kolommen werkt of met Excel 2007 werkt:

    Function ColNumberToNameSimpel(ByVal lNumb As Long) As String
    ColNumberToNameSimpel = Substitute(Cells(1, lNumb).Address(0, 0), "1", "")
    End Function
    of nog:
    Function ColNumberToNameSimpel_2(ByVal lNumb As Long) As String
    ColNumberToNameSimpel_2 = Split(Cells(1, lNumb).Address, "$")(1)
    End Function

    Elders op het web

    Ik las op de schitterende Excel weblog van Dick Kusleika een pagina waar Dick aan zijn lezers vraagt om zulke code te schrijven. Zelfs notoire Excel MVP’s hadden er wat moeite mee! Ik heb Dick ondertussen mijn oplossing eens toegeschoven. Als jullie een betere oplossing hebben, mail me gerust.




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links