تبدیل عدد به حروف در اکسل؛ آموزش فرمولی بدون ماکرو
برای تبدیل عدد به حروف فارسی در اکسل، می‌توانید از یک جدول واژه‌ها و فرمول‌های معمولی استفاده کنید؛ بدون VBA و بدون فعال‌کردن ماکرو. در نمونهٔ این آموزش، مقدار ۱۲۵۰۰۰ به «صد و بیست و پنج هزار» تبدیل می‌شود و با تغییر سلول ورودی، خروجی هم تغییر می‌کند. اگر فقط چند عدد دارید، ابزار تبدیل عدد به حروف آی‌کیت راه سریع‌تری برای بررسی نتیجه است.
این روش برای کسی مناسب است که می‌خواهد چندین ردیف عدد را در همان کاربرگ به متن فارسی تبدیل کند. هم ساختار فایل نمونه را می‌بینید و هم دستور ساخت آن را دارید؛ بنابراین می‌توانید فرمول‌ها را بررسی و در فایل خودتان استفاده کنید.
نتیجهٔ نهایی و ساختار فایل نمونه
فایل نمونه دو برگه دارد: Convert برای ورود عدد و دیدن خروجی، و Words برای ساخت نام عددهای صفر تا ۹۹۹. در Convert، ردیف ۷ عنوان ستون‌ها و ردیف‌های ۸ تا ۵۷ برای ۵۰ ورودی آماده شده‌اند. ستون‌های کمکی هم در همان برگه قابل بررسی‌اند.
| ستون | کاربرد | قابل تغییر توسط کاربر |
|---|---|---|
| A | عدد ورودی | بله |
| B | واحد اختیاری، مثل ریال | بله |
| C | خروجی حروفی | فرمول دارد |
| D | وضعیت ورودی | فرمول دارد |
| E تا H | گروه‌های میلیارد، میلیون، هزار و یکان‌ها | فرمول دارند |
| I تا L | عبارت هر گروه | فرمول دارند |
برای نمونه، A8 برابر با ۱۲۵۰۰۰ و B8 برابر با «ریال» است؛ C8 باید صد و بیست و پنج هزار ریال را نشان دهد. با تغییر B8 به «تومان»، فقط همین واژه عوض می‌شود؛ مقدار ۱۲۵۰۰۰ تقسیم یا ضرب نمی‌شود.
مثال دیگر، ۲٬۰۳۰٬۰۰۵ است که به دو میلیون و سی هزار و پنج تبدیل می‌شود. همین مثال نشان می‌دهد چرا صفرهای آغازینِ گروه‌های ۰۳۰ و ۰۰۵ باید هنگام گروه‌بندی حفظ شوند، هرچند در خواندن نام گروه، جدا تلفظ نمی‌شوند.
پیش‌نیازها و سازگاری را بشناسید
این نمونه با قالب xlsx ساخته شده و ماکرو ندارد. از تابع‌هایی مانند IF، ISBLANK، ISNUMBER، INT، MOD، INDEX و AND و عملگر اتصال متن & استفاده می‌کند؛ به تابع‌های جدیدی مانند LAMBDA یا افزونهٔ جانبی وابسته نیست.
پیش از واردکردن فهرست اصلی، چند عدد ساده مثل ۰، ۱۰۵ و ۱۰۰۶ را در فایل امتحان کنید. اگر خروجی درست بود، یکی از عددها را تغییر دهید و مطمئن شوید نتیجه هم به‌روز می‌شود. در ادامه، روش پیدا‌کردن خطاهای رایج را می‌بینید.
فرمول‌های زیر با نام انگلیسی تابع‌ها و جداکنندهٔ ویرگول نوشته شده‌اند. اگر تنظیمات اکسل شما جداکنندهٔ آرگومان را نقطه‌ویرگول می‌خواهد، هنگام ورود دستی فرمول‌ها جداکننده را متناسب با همان تنظیمات تغییر دهید. گیومه‌های داخل فرمول باید همان علامت مستقیم انگلیسی باشند.
قدم اول: جدول واژه‌های پایه را بسازید
یک برگه با نام دقیق Words ایجاد کنید. جدول زیر را کپی و در سلول D1 بچسبانید؛ ستون‌ها به‌ترتیب رقم، یکان، عددهای ۱۰ تا ۱۹، دهگان و صدگان هستند. خانه‌های خالی را با صفر یا خط تیره جایگزین نکنید.
این جدول کوچک منبع ساخت نام هر گروه سه‌رقمی است. برای مثال، ۵۰۲ از پانصد و دو ساخته می‌شود و ۱۹ نام مستقل «نوزده» دارد. به همین دلیل عددهای ۱۰ تا ۱۹ در یک ستون جدا قرار گرفته‌اند.
رقم یکان ۱۰ تا ۱۹ دهگان صدگان
0 ده
1 یک یازده صد
2 دو دوازده بیست دویست
3 سه سیزده سی سیصد
4 چهار چهارده چهل چهارصد
5 پنج پانزده پنجاه پانصد
6 شش شانزده شصت ششصد
7 هفت هفده هفتاد هفتصد
8 هشت هجده هشتاد هشتصد
9 نه نوزده نود نهصددر A1 بنویسید «عدد سه‌رقمی» و در B1 بنویسید «نام فارسی». در A2 مقدار ۰ و در A3 مقدار ۱ بگذارید؛ هر دو را انتخاب و دنباله را تا A1001 ادامه دهید، به‌طوری‌که آخرین مقدار ۹۹۹ باشد. سپس فرمول زیر را در B2 وارد و تا B1001 کپی کنید.
=IF(A2=0,"",IF(A2>=100,INDEX($H$2:$H$11,INT(A2/100)+1),"")&IF(AND(A2>=100,MOD(A2,100)>0)," و ","")&IF(AND(MOD(A2,100)>=10,MOD(A2,100)<20),INDEX($F$2:$F$11,MOD(A2,10)+1),IF(MOD(A2,100)>=20,INDEX($G$2:$G$11,INT(MOD(A2,100)/10)+1),"")&IF(AND(MOD(A2,100)>=20,MOD(A2,10)>0)," و ","")&IF(MOD(A2,10)>0,INDEX($E$2:$E$11,MOD(A2,10)+1),"")))این فرمول ابتدا صدگان را می‌خواند، سپس باقی‌ماندهٔ کمتر از صد را بررسی می‌کند. برای ۱۰ تا ۱۹ از ستون نام‌های مستقل استفاده می‌شود؛ برای بقیهٔ عددها، دهگان و یکان کنار هم قرار می‌گیرند. شرط‌های «و» مانع می‌شوند در ابتدا یا انتهای عبارت، پیوند اضافی ظاهر شود.
ردیف مربوط به صفر در این جدول خروجی خالی دارد؛ چون یک گروهِ صفر نباید عبارتی مانند «صفر هزار» تولید کند. تبدیل خودِ عدد صفر به «صفر» را در فرمول نهایی جدا انجام می‌دهیم.
قدم دوم: ورودی و وضعیت آن را آماده کنید
برگهٔ دوم را Convert نام بگذارید و عنوان ستون‌های A تا D را در ردیف ۷، «عدد ورودی»، «واحد اختیاری»، «خروجی حروفی» و «وضعیت» بنویسید. عدد را در A8 و واحد دلخواه را در B8 قرار دهید. برای وضعیت، فرمول زیر را در D8 وارد کنید.
=IF(ISERROR(A8),"خطای سلول ورودی",IF(ISBLANK(A8),"",IF(NOT(ISNUMBER(A8)),"ورودی عددی لازم است",IF(A8<0,"عدد منفی پشتیبانی نمی‌شود",IF(A8>999999999999,"بیش از ۱۲ رقم",IF(A8<>INT(A8),"اعشار پشتیبانی نمی‌شود","معتبر"))))))ورودی خالی وضعیت خالی دارد؛ صفر یک ورودی معتبر است. ورودی متنی، منفی، اعشاری و عدد بیشتر از دامنه، پیام مشخص می‌گیرند. اگر سلول ورودی خودش خطای محاسباتی داشته باشد، همان مسئله با پیام «خطای سلول ورودی» نشان داده می‌شود.
این کنترل، متن عددی را خودکار به عدد تبدیل نمی‌کند. مثلاً اگر ۱۲۵۰۰۰ به‌صورت متن ذخیره شده باشد، ابتدا باید مشکل نوع داده را اصلاح کنید. ظاهرِ شبیه عدد، به‌تنهایی برای این فایل کافی نیست.
قدم سوم: چهار گروه عددی را جدا کنید
عنوان‌های E7 تا H7 را به‌ترتیب «میلیارد»، «میلیون»، «هزار» و «یکان‌ها» بگذارید. فرمول متناظر هر سلول را از جدول زیر در ردیف ۸ وارد کنید. INT بخش صحیح تقسیم و MOD باقی‌مانده را به دست می‌آورد؛ بنابراین هر خروجی در محدودهٔ صفر تا ۹۹۹ است.
| سلول | فرمول |
|---|---|
| E8 | =IF($D8="معتبر",MOD(INT($A8/1000000000),1000),"") |
| F8 | =IF($D8="معتبر",MOD(INT($A8/1000000),1000),"") |
| G8 | =IF($D8="معتبر",MOD(INT($A8/1000),1000),"") |
| H8 | =IF($D8="معتبر",MOD(INT($A8/1),1000),"") |
برای ورودی ۲٬۰۳۰٬۰۰۵، نتیجهٔ این چهار سلول به‌ترتیب ۰، ۲، ۳۰ و ۵ است. صفرِ گروه میلیارد هنگام ساخت عبارت کنار گذاشته می‌شود. شرط D8 هم باعث می‌شود ورودی نامعتبر وارد مسیر تبدیل نشود.
قدم چهارم: عبارت هر گروه و خروجی نهایی را بسازید
عنوان‌های I7 تا L7 را عبارت میلیارد، عبارت میلیون، عبارت هزار و عبارت یکان‌ها بگذارید. چهار فرمول زیر را به‌ترتیب در I8، J8، K8 و L8 قرار دهید. INDEX نام گروه را از Words می‌خواند. چون جدول از عدد صفر شروع شده، شمارهٔ ردیف جست‌وجو یک واحد از مقدار گروه بیشتر است.
I8
=IF($D8<>"معتبر","",IF(E8=0,"",INDEX('Words'!$B$2:$B$1001,E8+1)&" میلیارد"))J8
=IF($D8<>"معتبر","",IF(F8=0,"",INDEX('Words'!$B$2:$B$1001,F8+1)&" میلیون"))K8
=IF($D8<>"معتبر","",IF(G8=0,"",INDEX('Words'!$B$2:$B$1001,G8+1)&" هزار"))L8
=IF($D8<>"معتبر","",IF(H8=0,"",INDEX('Words'!$B$2:$B$1001,H8+1)&""))اکنون این فرمول را در C8 بگذارید. عبارت‌های غیرخالی را با «و» به هم پیوند می‌دهد، صفر را جدا می‌خواند و در صورت وجود واحد، آن را در پایان اضافه می‌کند.
=IF(D8<>"معتبر","",IF(A8=0,"صفر",I8&IF(J8="","",IF(I8="",""," و ")&J8)&IF(K8="","",IF(OR(I8<>"",J8<>"")," و ","")&K8)&IF(L8="","",IF(OR(I8<>"",J8<>"",K8<>"")," و ","")&L8))&IF(B8="",""," "&B8))با ورودی ۶۰۰۰۷، گروه هزار «شصت هزار» و گروه آخر «هفت» می‌شود؛ نتیجه شصت هزار و هفت است. اگر نتیجه خالی بود، اول D8 را بررسی کنید. خروجی خالی برای ورودی نامعتبر، به معنای تبدیل موفق نیست؛ علت در ستون وضعیت نوشته می‌شود.
تبدیل چند ردیف و افزودن واحد مبلغ
پس از درست‌بودن نتیجهٔ ردیف ۸، محدودهٔ C8:L8 را انتخاب و فرمول‌ها را به ردیف‌های بعدی کپی کنید. مرجع‌های مربوط به ورودی از A8 به A9 و ردیف‌های بعدی تغییر می‌کنند، اما محدودهٔ ثابت واژه‌ها در Words با علامت‌های $ ثابت می‌ماند. در فایل نمونه، ۵۰ ردیف از قبل آماده است.
اگر می‌خواهید داده‌های اصلی را حفظ کنید، ستون‌های A و B را همراه فرمول‌ها روی ورودی‌های موجود کپی نکنید. عددها را در A و واحدها را در B وارد کنید. برای خروجیِ بدون واحد، B را خالی بگذارید.
واژهٔ ریال یا تومان یک پسوند است. اگر می‌خواهید مقدارِ یک واحد را به واحد دیگری تبدیل کنید، آن محاسبه باید پیش از تبدیل حروفی و در یک سلول مشخص انجام شود؛ صرف عوض‌کردن متن B، تبدیل واحد نیست. همچنین قالب نمایشی هزارگان، مثل نمایش ۱۲۵۰۰۰ به صورت ۱۲۵٬۰۰۰، نوع دادهٔ عددی را به متن تغییر نمی‌دهد.
ورودی متنی و خطاهای رایج را اصلاح کنید
عدد ذخیره‌شده به‌صورت متن با «عدد به حروف» دو مسئلهٔ جداست. تبدیل متن "125000" به مقدار عددی ۱۲۵۰۰۰ فقط نوع داده را اصلاح می‌کند؛ سپس فرمول‌های این آموزش، عبارت فارسی را می‌سازند. برای متن‌های قابل تفسیر در تنظیمات اکسل، می‌توانید از فرمان Convert to Number یا تابع VALUE در یک ستون جدا استفاده کنید.
اگر عددها با رقم فارسی یا جداکنندهٔ هزارگان به‌صورت متن ذخیره شده‌اند، ابتدا آن‌ها را به مقدار عددی تبدیل کنید. نتیجهٔ تبدیل متن به تنظیمات اکسل و شکل داده بستگی دارد؛ قبل از جایگزین‌کردن ستون اصلی، چند مقدار را با دادهٔ اولیه مقایسه کنید.
| نشانه | علت محتمل | اقدام |
|---|---|---|
| «ورودی عددی لازم است» | سلول متن است | نوع داده را اصلاح و مقدار را کنترل کنید |
| «اعشار پشتیبانی نمی‌شود» | بخش کسری وجود دارد | این نمونه فقط برای عدد صحیح است |
| «بیش از ۱۲ رقم» | مقدار از دامنه بالاتر است | عدد را قطع نکنید؛ به روش با دامنهٔ مناسب نیاز دارید |
| #REF! | برگه یا محدودهٔ واژه‌ها حذف یا جابه‌جا شده | نام Words و محدودهٔ ثابت را بازبینی کنید |
| فرمول محاسبه نمی‌شود | ورودی فرمول به‌صورت متن یا تنظیم محاسبهٔ دستی | قالب سلول و حالت Automatic را بررسی کنید |
اگر فقط خروجی ثابت می‌خواهید، پس از بررسی نتیجه می‌توانید آن را با Paste Special → Values در محل دیگری بچسبانید. در این حالت خروجیِ چسبانده‌شده دیگر با تغییر ورودی به‌روز نمی‌شود؛ نسخهٔ فرمول‌دار را برای استفاده‌های بعدی نگه دارید.
پیش از کار با فهرست اصلی، این نمونه‌ها را امتحان کنید
چند ورودی با حالت‌های متفاوت وارد کنید تا مطمئن شوید فرمول‌ها درست کپی شده‌اند و ستون وضعیت هم کار می‌کند:
| ورودی | نتیجهٔ مورد انتظار |
|---|---|
| سلول خالی | خروجی و وضعیت خالی |
| ۰ | صفر |
| ۱۰۵ | صد و پنج |
| ۱۰۰۶ | یک هزار و شش |
| ۲٬۰۳۰٬۰۰۵ به‌صورت عدد | دو میلیون و سی هزار و پنج |
| عدد منفی | پیام «عدد منفی پشتیبانی نمی‌شود» |
| عدد اعشاری | پیام «اعشار پشتیبانی نمی‌شود» |
| عدد ذخیره‌شده به‌صورت متن | پیام «ورودی عددی لازم است» |
بعد یکی از ورودی‌ها را به ۹۹ تغییر دهید؛ خروجی باید «نود و نه» شود. واحد را هم یک بار اضافه و یک بار پاک کنید. اگر نتیجه تغییر نکرد، فرمول همان ردیف و حالت محاسبهٔ Automatic را بررسی کنید. عددی که فقط با قالب هزارگان نمایش داده می‌شود معتبر است؛ اما متنی مثل "1,250" باید ابتدا به مقدار عددی تبدیل شود.
سؤال‌های رایج
آیا این روش به ماکرو یا افزونه نیاز دارد؟
آیا عدد منفی و اعشاری هم تبدیل می‌شود؟
چرا عددی که در سلول دیده می‌شود متنی تشخیص داده شده است؟
آیا تغییر ریال به تومان در ستون واحد، مبلغ را تبدیل می‌کند؟
چطور بیش از ۵۰ ردیف تبدیل کنم؟
چرا خروجی با تغییر عدد به‌روز نمی‌شود؟
جمع‌بندی
برای تبدیل گروهی عدد به حروف در اکسل، ابتدا نوع و دامنهٔ ورودی را مشخص کنید، سپس عدد را به گروه‌های سه‌رقمی بشکنید و نام هر گروه را از جدول واژه‌ها بخوانید. فرمول نهایی، بخش‌های غیرصفر را به هم وصل می‌کند. اگر تعداد عددها کم است، ابزار آنلاین آی‌کیت برای بررسی سریع مناسب است؛ برای چند ردیف در یک کاربرگ، ساختار فرمولی این آموزش قابل استفاده و بررسی است.
