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

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

آموزش تصویری تبدیل عدد به حروف در اکسل (A تا Z)

طبق آنچه که در وب ‌سایت رسمی مایکروسافت آمده است، اکسل به‌طور پیش‌فرض تابعی ندارد که اعداد را به‌صورت کلمات انگلیسی در یک ورک‌شیت نمایش دهد؛ اما می‌توانید با قراردادن کد تابع SpellNumber زیر در یک ماژول VBA (Visual Basic for Applications)، این قابلیت را اضافه کنید. این تابع به شما امکان می‌دهد مقادیر دلار و سنت را با یک فرمول به کلمات تبدیل کنید، به‌طوری‌که ۲۲.۵۰ به‌صورت «بیست و دو دلار و پنجاه سنت» خوانده شود. این قابلیت می‌تواند برای استفاده از اکسل به‌عنوان الگویی برای چاپ چک بسیار مفید باشد.

اگر می‌خواهید مقادیر عددی را به فرمت متنی تبدیل کنید، بدون اینکه آن‌ها را به‌صورت کلمات نمایش دهید، به‌جای آن از تابع TEXT استفاده کنید.

در ادامه بخوانید: آموزش vba در اکسل

ایجاد تابع SpellNumber برای تبدیل عدد به حروف در اکسل

از میانبر صفحه کلید Alt + F11 برای بازکردن ویرایشگر Visual Basic (VBE) استفاده کنید. همچنین با نمایش زبانه Developer در روبان خود هم می‌توانید به ویرایشگر Visual Basic دسترسی پیدا کنید.

روی تب Insert کلیک کنید و سپس Module را انتخاب کنید.

خطوط کد زیر را کپی و در ماژول بازشده جای‌گذاری کنید. این کد که به‌عنوان تابع تعریف‌شده توسط کاربر (UDF) شناخته می‌شود، وظیفه تبدیل اعداد به متن را در سراسر ورک‌شیت شما خودکار می‌کند.

Option Explicit

‘Main Function

Function SpellNumber(ByVal MyNumber)

Dim Dollars, Cents, Temp

Dim DecimalPlace, Count

ReDim Place(9) As String

Place(2) = ” Thousand ”

Place(3) = ” Million ”

Place(4) = ” Billion ”

Place(5) = ” Trillion ”

‘ String representation of amount.

MyNumber = Trim(Str(MyNumber))

‘ Position of decimal place 0 if none.

DecimalPlace = InStr(MyNumber, “.”)

‘ Convert cents and set MyNumber to dollar amount.

If DecimalPlace > 0 Then

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ “00”, 2))

MyNumber = Trim(Left(MyNumber, DecimalPlace – 1))

End If

Count = 1

Do While MyNumber <> “”

Temp = GetHundreds(Right(MyNumber, 3))

If Temp <> “” Then Dollars = Temp & Place(Count) & Dollars

If Len(MyNumber) > 3 Then

MyNumber = Left(MyNumber, Len(MyNumber) – 3)

Else

MyNumber = “”

End If

Count = Count + 1

Loop

Select Case Dollars

Case “”

Dollars = “No Dollars”

Case “One”

Dollars = “One Dollar”

Case Else

Dollars = Dollars & ” Dollars”

End Select

Select Case Cents

Case “”

Cents = ” and No Cents”

Case “One”

Cents = ” and One Cent”

Case Else

Cents = ” and ” & Cents & ” Cents”

End Select

SpellNumber = Dollars & Cents

End Function

‘ Converts a number from 100-999 into text

Function GetHundreds(ByVal MyNumber)

Dim Result As String

If Val(MyNumber) = 0 Then Exit Function

MyNumber = Right(“000” & MyNumber, 3)

‘ Convert the hundreds place.

If Mid(MyNumber, 1, 1) <> “0” Then

Result = GetDigit(Mid(MyNumber, 1, 1)) & ” Hundred ”

End If

‘ Convert the tens and ones place.

If Mid(MyNumber, 2, 1) <> “0” Then

Result = Result & GetTens(Mid(MyNumber, 2))

Else

Result = Result & GetDigit(Mid(MyNumber, 3))

End If

GetHundreds = Result

End Function

‘ Converts a number from 10 to 99 into text.

Function GetTens(TensText)

Dim Result As String

Result = “” ‘ Null out the temporary function value.

If Val(Left(TensText, 1)) = 1 Then ‘ If value between 10-19…

Select Case Val(TensText)

Case 10: Result = “Ten”

Case 11: Result = “Eleven”

Case 12: Result = “Twelve”

Case 13: Result = “Thirteen”

Case 14: Result = “Fourteen”

Case 15: Result = “Fifteen”

Case 16: Result = “Sixteen”

Case 17: Result = “Seventeen”

Case 18: Result = “Eighteen”

Case 19: Result = “Nineteen”

Case Else

End Select

Else ‘ If value between 20-99…

Select Case Val(Left(TensText, 1))

Case 2: Result = “Twenty ”

Case 3: Result = “Thirty ”

Case 4: Result = “Forty ”

Case 5: Result = “Fifty ”

Case 6: Result = “Sixty ”

Case 7: Result = “Seventy ”

Case 8: Result = “Eighty ”

Case 9: Result = “Ninety ”

Case Else

End Select

Result = Result & GetDigit _

(Right(TensText, 1)) ‘ Retrieve ones place.

End If

GetTens = Result

End Function

‘ Converts a number from 1 to 9 into text.

Function GetDigit(Digit)

Select Case Val(Digit)

Case 1: GetDigit = “One”

Case 2: GetDigit = “Two”

Case 3: GetDigit = “Three”

Case 4: GetDigit = “Four”

Case 5: GetDigit = “Five”

Case 6: GetDigit = “Six”

Case 7: GetDigit = “Seven”

Case 8: GetDigit = “Eight”

Case 9: GetDigit = “Nine”

Case Else: GetDigit = “”

End Select

End Function

کلید Alt + Q را فشار دهید تا به اکسل بازگردید. تابع SpellNumber اکنون آماده استفاده است.

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

در ادامه بخوانید: محاسبه میانگین در اکسل

استفاده از تابع SpellNumber در سلول‌های جداگانه

فرمول زیر را در سلولی که می‌خواهید عدد نوشتاری را نمایش دهید، تایپ کنید:

=SpellNumber(A1)

A1 سلولی است که حاوی عددی است که می‌خواهید تبدیل کنید. همچنین می‌توانید مقدار را به‌صورت دستی مانند زیر تایپ کنید:

=SpellNumber(22.50)

برای تأیید فرمول، Enter را فشار دهید.

ذخیره ورک‌شیت تابع SpellNumber

اکسل نمی‌تواند ورک‌شیت با توابع ماکرو را در قالب استاندارد ورک‌شیت بدون ماکرو (.xlsx) ذخیره کند. اگر روی File > Save کلیک کنید، یک باکس VB project باز می‌شود. روی No کلیک کنید.

شما می‌توانید فایل خود را به‌عنوان  Excel Macro-Enabled Workbook (.xlsm) ذخیره کنید تا فایل خود را در قالب فعلی نگه دارید.

• روی File > Save As کلیک کنید.
• روی منوی کشویی Save as type کلیک کنید و Excel Macro-Enabled Workbook را انتخاب کنید.
• روی Save کلیک کنید.

 

تبدیل شماره ستون به حروف در اکسل

اگر می‌خواهید شماره ستون را به حروفی از A تا Z تبدیل کنید (ستون‌های تک‌حرفی) می‌توانید از یک فرمول بسیار ساده استفاده کنید. این فرمول به شما امکان می‌دهد تا شماره ستون را به حرف متناظر آن تبدیل کنید.

فرمول ساده برای تغییر شماره ستون به حروف

برای تبدیل شماره ستون به حرف از تابع CHAR استفاده می‌کنیم. همانطور که در آموزش فرمول نویسی در اکسل وب‌سایت ۲۴ وب لرن گفته شده است، توابع اکسل چیزی جز فرمول‌های ازپیش‌تعریف‌شده که محاسبات مورد نیاز را در پس‌زمینه انجام می‌دهند، نیستند.

تابع CHAR یک کاراکتر را براساس کد ASCII آن بازمی‌گرداند. برای تبدیل عدد به حروف در اکسل وقتی عدد شماره ستون است، می‌توانید از فرمول زیر استفاده کنید:

CHAR(64 + col_number)

به‌عنوان‌مثال، برای تبدیل عدد ۱۰ به حرف ستون، فرمول به این صورت است:

=CHAR(64 + 10)

همچنین می‌توانید یک عدد را در یک سلول وارد کنید و به آن سلول در فرمول خود ارجاع دهید:

=CHAR(64 + A2)

این فرمول، شماره ستون موجود در سلول A2 را به حرف متناظر آن تبدیل می‌کند.

در اینجا، عدد ۶۴ کد ASCII برای کاراکتر قبل از ‘A’ است؛ بنابراین با اضافه‌کردن شماره ستون به ۶۴، کد ASCII حرف متناظر ستون را به دست می‌آورید.

تبدیل شماره ستون به حروف (هر ستون)

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

فرمول زیر به شما کمک می‌کند تا شماره ستون را به حرف متناظر آن تبدیل کنید:

SUBSTITUTE(ADDRESS(1, col_number, 4), “1”, “”)

با قراردادن حرف ستون در سلول A2، فرمول به این شکل می‌شود:

=SUBSTITUTE(ADDRESS(1, A2, 4), “1”, “”)

در این فرمول، A2 سلولی است که شماره ستون مورد نظر در آن قرار دارد.

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

ابتدا، شما یک آدرس سلول با شماره ستون مورد نظر خود ایجاد می‌کنید. برای این کار، آرگومان‌های زیر را به تابع ADDRESS ارائه می‌دهید:

• ۱ برای آرگومان row_num (شماره سطر مهم نیست، بنابراین می‌توانید از هر عددی استفاده کنید)؛
• A2 (سلول حاوی شماره ستون) برای آرگومان column_num؛
• ۴ برای آرگومان abs_num برای بازگرداندن یک مرجع نسبی.

با پارامترهای بالا، تابع ADDRESS رشته متنی “A1” را به‌عنوان نتیجه بازمی‌گرداند.

از آنجایی که ما فقط به حرف ستون نیاز داریم، شماره سطر را با کمک تابع SUBSTITUTE حذف می‌کنیم که در متن “A1” به‌دنبال “۱” (یا هر شماره سطری که در تابع ADDRESS هاردکدنویسی کرده‌اید) می‌گردد و آن را با یک رشته خالی (“”) جایگزین می‌کند.

 

استفاده از تابع سفارشی برای تبدیل عدد به حروف در اکسل

اگر به‌طور مرتب نیاز به تبدیل شماره ستون به حروف دارید، ایجاد یک تابع user-defined (UDF) می‌تواند به شما کمک کند. این تابع سفارشی عملکرد تبدیل را به‌صورت ساده و سریع انجام می‌دهد.

برای ایجاد این تابع، کد زیر را در محیط VBA اکسل وارد کنید:

Public Function ColumnLetter(col_num)

ColumnLetter = Split(Cells(1, col_num).Address, “$”)(1)

End Function

در این فرمول، از ویژگی Cells برای اشاره به یک سلول در سطر ۱ و شماره ستون مشخص‌شده و ویژگی Address  برای بازگرداندن یک رشته حاوی یک مرجع مطلق به آن سلول مانند ($A$1) استفاده می‌کنیم. سپس، تابع Split رشته برگردانده‌شده را به عناصر جداگانه با استفاده از علامت $ به‌عنوان جداکننده تقسیم می‌کند و ما عنصر (۱) را که حرف ستون است، بازمی‌گردانیم.

پس‌از واردکردن کد فوق می‌توانید از تابع ColumnLetter برای تبدیل شماره ستون به حروف استفاده کنید. به‌عنوان‌مثال، اگر شماره ستون در A2 قرار دارد، فرمول زیر حروف متناظر آن را بازمی‌گرداند:

=ColumnLetter(A2)

این تابع مانند توابع داخلی اکسل کار می‌کند و می‌تواند در فرمول‌های پیچیده‌تر هم استفاده شود.

اگر بخواهید حرف ستون یک سلول خاص را پیدا کنید، می‌توانید از ترکیب توابع COLUMN و ADDRESS استفاده کنید. این ترکیب به شما کمک می‌کند تا شماره ستون سلول مورد نظر را به حرف متناظر آن تبدیل کنید.

فرمول کامل به شکل زیر خواهد بود:

SUBSTITUTE(ADDRESS(1, COLUMN(cell_address), 4), “1”, “”)

به‌عنوان‌مثال، بیایید حرف ستون سلول C5 را پیدا کنیم:

=SUBSTITUTE(ADDRESS(1, COLUMN(C5), 4), “1”, “”)

بدیهی است که نتیجه “C” است.

دریافت حرف ستون سلول فعلی

اگر می‌خواهید حرف ستون سلول فعلی (سلولی که فرمول در آن قرار دارد) را پیدا کنید، تنها تفاوت با مثال قبلی این است که تابع COLUMN را بدون هیچ آرگومانی استفاده می‌کنید:

=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), “1”, “”)

این فرمول به‌صورت خودکار ستونی را که سلول فعلی در آن قرار دارد، تشخیص می‌دهد و حرف متناظر آن را بازمی‌گرداند. این روش زمانی مفید است که بخواهید فرمول‌های پویا ایجاد کنید که به‌طور خودکار با تغییر مکان سلول‌ها تطبیق یابند.

 

چگونه می‌توانید به یک متخصص اکسل تبدیل شوید؟

در این مقاله با مفاهیم و تکنیک‌های تبدیل عدد به حروف در اکسل آشنا شدید؛ اما اگر می‌خواهید بر مهارت‌های پیشرفته اکسل تسلط کامل پیدا کنید و صفر تا صد اکسل را در کمترین زمان ممکن یاد بگیرید، از صفحه محصول دوره آموزش اکسل مقدماتی تا پیشرفته ۲۴ وب لرن دیدن کنید.

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

پس‌از گذراندن دوره آموزش جامع ۲۴ وب لرن به‌راحتی می‌توانید جداول پیچیده را در چند ثانیه تحلیل ‌کنید، گزارش‌های عالی با نمودارهای حرفه‌ای ارائه ‌دهید و فرآیندهای زمان‌بر را با ماکروها خودکارسازی ‌کنید. این دوره با ۶۰% تخفیف ویژه، سرمایه‌گذاری هوشمندانه‌ای برای آینده شغلی شماست و پشتیبانی ۲۴ساعته و دسترسی مادام‌العمر به به‌روزرسانی‌ها، خیال شما را از یادگیری کامل راحت می‌کند.

فرصت محدود است! همین حالا از صفحه محصول دیدن کنید و قدم در مسیر تبدیل‌شدن به متخصص اکسل بگذارید.

 

سوالات متداول

چگونه می‌توانم شماره ستون را به‌صورت خودکار به حرف تبدیل کنم؟

شما می‌توانید از تابع `CHAR` برای ستون‌های تک‌حرفی (A تا Z) یا از ترکیب توابع `ADDRESS` و `SUBSTITUTE` برای هر ستونی استفاده کنید. همچنین با ایجاد یک تابع سفارشی در VBA می‌توانید این کار را به‌راحتی انجام دهید.

چگونه می‌توانم حرف ستون سلول فعلی را پیدا کنم؟

برای پیدا کردن حرف ستون سلول فعلی، می‌توانید از فرمول زیر استفاده کنید:

=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), “1”, “”)

این فرمول به‌طور خودکار حرف ستون سلول فعلی را بازمی‌گرداند.

آیا می‌توانم از شماره ستون برای ایجاد مراجع دینامیک در فرمول‌ها استفاده کنم؟

بله، با تبدیل شماره ستون به حرف و استفاده از تابع `INDIRECT` می‌توانید مراجع دامنه دینامیک ایجاد کنی و از آن‌ها در فرمول‌هایی مانند `XLOOKUP` استفاده کنید.

سخن پایانی

در این مقاله به بررسی روش‌های مختلف تبدیل عدد به حروف در اکسل پرداختیم. از توابع ساده داخلی گرفته تا توابع سفارشی VBA، ابزارها و فرمول‌هایی معرفی شد که به شما کمک می‌کند شماره ستون‌ها را به حروف الفبایی تبدیل کنید. با استفاده از این ابزارها قادر خواهید بود فرمول‌های پویا و کارآمدتری ایجاد کنید و گزارش‌های دقیق‌تر و شفاف‌تری ارائه دهید.