تعیین ستون الفبایی در اکسل

Question

با استفاده از تابع COLUMN می توانید به راحتی ستون عددی سلول را تعیین کنید. تنها کاری که باید انجام دهید این است که فرمولی مانند این را در یک سلول قرار دهید و نتیجه یک مقدار است که در آن A=1، B=2 و غیره است:

=COLUMN()

اگر به جای یک مقدار عددی، یک مقدار الفبایی بخواهید چه؟ این را می توان به هر یک از چندین روش مختلف انجام داد. به عنوان مثال، فرمول زیر برای 26 ستون اول، A تا Z بسیار خوب کار می کند:

=CHAR(COLUMN()+64)

این کار به این دلیل کار می‌کند که حروف A تا Z از کدهای کاراکتر 65 تا 90 استفاده می‌کنند. وقتی COLUMN مقداری را برای ستون‌های A تا Z (1 تا 26) برمی‌گرداند، می‌توان آن را به 64 اضافه کرد تا حروف آن ستون‌ها، 65 تا 90 را به دست آورد.

البته، اگر می‌خواهید حروف ستون‌ها را فراتر از Z بدانید، این راه‌حل کار نمی‌کند. از آنجایی که یک ستون در اکسل می‌تواند تا سه رقم داشته باشد (اکسل می‌تواند از ستون‌ها تا XFD استفاده کند)، رویکرد متفاوتی برای یافتن حروف ستون است. به ترتیب:

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

تابع ADDRESS آدرس یک سلول خاص را برمی گرداند. در این حالت، آدرس سلول در ردیف اول ستون فعلی را برمی گرداند. بنابراین، اگر فرمول در سلول BF27 باشد، BF1 را برمی گرداند. این فرمول از تابع LEFT برای برگرداندن تعداد صحیح نویسه های سمت چپ در آدرس، منهای عدد 1 برای ردیف استفاده می کند.

یک نسخه حتی کوتاهتر از فرمول به جای تابع LEFT به تابع SUBSTITUTE متکی است:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

البته در صورت تمایل می توانید از یک راه حل مبتنی بر کلان نیز استفاده کنید. ماکرو زیر با ستون های یک، دو یا سه کاراکتری کار می کند:

Function AlphaCol(c As Range) As String
    Dim sTemp As String
    Dim ad1 As String

    ad1 = c.Address
    sTemp = Mid(ad1, InStr(ad1, "$") + 1, InStr(2, ad1, "$") - 2)
    AlphaCol = sTemp
End Function

ماکرو یک تابع تعریف شده توسط کاربر است، به این معنی که شما می توانید آن را در کاربرگ های خود با افزودن این مورد به هر سلولی استفاده کنید:

=AlphaCol(J12)

سلولی که در فراخوانی تابع به آن ارجاع داده می شود، یک سلول (هر سلولی) در ستون است که حرف آن را می خواهید بدانید. تابع آن آدرس را برای آن سلول پیدا می کند و همه چیز را به جز تعیین ستون حذف می کند. یک رشته متنی برگردانده می‌شود که از تعیین ستون تشکیل شده است.