تبدیل عدد به حروف در اکسل – آموزش تصویری و گام به گام

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

فرض بر این است که با اکسل تا حدودی آشنایی دارید. ولی برای کسب اطلاعات بیشتر پیشنهاد می‌شود،‌ مطالب مرتبط با این متن مانند تابع VLOOKUP در اکسل و کاربردهای آن — راهنمای جامع و توابع INDEX و MATCH در اکسل برای جست‌وجوی سریعتر را مطالعه کنید. همچنین خواندن فارسی کردن اعداد در اکسل — به زبان ساده و آموزش اکسل — به زبان ساده نیز خالی از لطف نیست.

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

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

در روش اول، به کمک فرمول نویسی و به شکل ابتدایی، روال کار را مشخص کرده و به کمک مثالی، مسئله را حل می‌کنیم. ولی به دلیل پیچیدگی موضوع، ممکن است فرمول ارائه شده، برای هر گونه تبدیلی موثر نباشد. به یاد داشته باشید که این تکنیک را فقط برای مشخص کردن سطح مسئله و روش حل آن مطرح کرده‌ایم.

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

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

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

فیلم آموزش تبدیل عدد به حروف در اکسل + افزونه کاربردی FarsiTools (رایگان) در فرادرس

کلیک کنید

به این منظور در اکسل بهتر است از تابع mid استفاده کنیم که از هر قسمت یک عبارت، به تعداد دلخواه، جدا کرده و نمایش می‌دهد.

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

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

نکته: تابع MID از سمت چپ، عمل جداسازی را انجام می‌دهد، هر چند که با شمارش ارقام از سمت راست، باید هزارگان در رقم چهارم باشد.

فرمول و تابع MID در سطر سبز رنگ مشخص است. قالب پارامتری تابع MID به صورت زیر است. این تابع براساس سه پارامتر دارد.

MID(text, start_num, num_chars)

• پارامتر اول (text): این پارامتر، یک زیر رشته را مشخص می‌کند.
• پارامتر دوم (start_num): نقطه شروع استخراج حروف، توسط این پارامتر تعیین می‌شود.
• پارامتر سوم (num_chars): تعداد ارقام قابل استخراج از پارامتر اول در محل تعیین شده در پارامتر دوم، را مشخص می‌کند.

بنابراین اگر به سلول A4 دقت کنید، تابع به کار رفته، سلول A1 را هدف قرار داده از مکانی که در موقعیت A3 تعیین شده (یعنی حرف اول) به اندازه یک حرف استخراج می‌کند.

=MID($A$1,A3,1)*1000

نکته: آدرس A1 را به آن جهت به صورت مطلق ($$\text{\$A\$1}$$) درآورده‌ایم، که با کپی کردن فرمول، آدرس A1 ثابت باقی بماند. ولی از جهتی، آدرس سلول A3 را به صورت نسبی حفظ کرده‌ایم، تا با کپی کردن سلول وابسته، آدرس آن به همان نسبت تغییر کند و تابع MID، ارقام بعدی عدد را استخراج کند.

رقم بدست آمده توسط تابع MID‌ در همان مکان، ضرب می‌کنیم تا برای مثال رقم ۷ به صورت ۷۰۰۰ درآید. البته این کار، این امکان را هم می‌دهد که خروجی تابع MID، به جای عبارت متنی، به صورت عددی درآید.

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

البته اگر صفر در این جستجو قرار گرفته باشد، تابع VLOOKUP، پیغام خطا داده که توسط تابع IFERROR این خطا با فضای خالی (NULL) که به صورت ""‌ تعیین شده، جایگزین می‌شود. تنها بخشی که مانده، قرار دادن یک عبارت «و» در بین هر یک از اسامی ارقام است. در سلول D1 یک «و» نوشته‌ایم که در مرحله بعد، به هر قسمت از بخش‌های ترکیب شده اضافه می‌کنیم.

اضافه کردن حرف ربط «و» باید با توجه به اینکه آیا لازم است آن را اضافه کنیم یا نه، انجام شود. واضح است که در ابتدا و انتهای عبارت، به حرف ربط احتیاجی نیست. پس  اگر سمت راست سلول مجاور هر عبارت متنی ساخته شده، خالی باشد، احتیاجی به اضافه کردن «و» به آن عبارت وجود ندارد. به همین جهت از یک IF کمک گرفته‌ایم که این وضعیت را بررسی می‌کند.

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

این بار فرمول نوشته شده را امتحان می‌کنیم. فرض کنید، به جای ۷۴۲۱، مقدار ۷۰۲۱ درون سلول قرار گرفته بود. باید در سلول مقصد، هفت هزار و بیست و یک نوشته شود. در تصویر زیر نتیجه به همراه ناحیه‌ای که برای تابع VLOOKUP به عنوان جدول جستجو مشخص کردیم، دیده می‌شود. خوشبختانه، روشی که به کار بردیم به درستی عمل می‌کند.

البته همه چیز به همین سادگی نیز نیست. برای مثال در نظر بگیرید که به جای 7421 از 7419 استفاده کنیم. این بار فرمول و محاسبات ما به صورت «هفت هزار و چهارصد و ده و نه» نتیجه را نمایش می‌دهد. زیرا اعداد ۱۱ تا ۱۹ به شکل استاندارد دهگان به همراه یکان خوانده نمی‌شوند. پس باید به کمک یک شرط، زمانی که دهگان ۱ است، قضیه را مورد بررسی قرار دهیم.

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

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

اگر افزونه Farsitools را دریافت و نصب کنید، تابع horofi در اختیارتان قرار می‌گیرد. به کمک این تابع می‌توانید هر عدد صحیحی که کمتر از ۱۵ رقم داشته باشد را به حروف تبدیل کرده و نمایش دهید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

از فهرست File گزینه Options را انتخاب کرده و از پنجره Excel Options در کادر سمت چپ، گزینه Add-ins را انتخاب کنید. در پایین صفحه با کلیک روی دکمه Go به بخش مدیریت Excel Add-ins بروید.

در پنجره محاوره‌ای Add-ins دکمه Browse را کلیک کرده و آدرس فایل دریافتی را مشخص کنید. توجه داشته باشید که این فایل باید FarsiTools.xla و با پسوند xla. باشد که بتوان از ماکرو و برنامه‌های VBA آن استفاده کرد.

در نهایت، با فعال سازی این افزونه (مطابق با تصویر زیر)، تابع horofi قابل دسترس خواهد بود.

در ادامه قطعه کدی را مشاهده می‌کنید که برای تعیین تابع horofi در این افزونه مورد استفاده قرار گرفته است. روالی که صورت گرفته تقریبا با عملیاتی که در قسمت قبل انجام دادیم، مطابقت دارد. البته طول رشته عددی نیز در این بین براساس متغیر S محاسبه شده و در L ذخیره شده است. مقدار L‌ نباید از ۱۵ بیشتر باشد در غیر اینصورت عدد قابل نمایش نیست و با مقدار «بسیار بزرگ» در سلول مربوط به فرمول، مواجه خواهید شد.

کاربرگی که در تصویر زیر می‌بینید، نحوه استفاده از این تابع را مشخص کرده است. کافی است برای نمایش حرفی عددی که در سلول A1 نوشته شده، در سلول B1‌ به صورت زیر از این تابع استفاده کنید.

B1 := horofi(A1)

بنابراین اگر عدد ۷۴۲۱ را در سلول A1 نوشته باشید، عبارت «هفت هزار و چهارصد و بيست و يك» را در سلول B1 مشاهده خواهید کرد.

کدی که در ادامه متن مشاهده می‌کنید، مربوط به تابع horofi است که به زبان VBA نوشته شده است.

نکته: توجه داشته باشید تابع ChrW برای به کارگیری حروف فارسی و نمایش به صورت یونی‌کد است. این تابع در محیط Visual Basic اکسل قابل استفاده است.

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

با به کارگیری تابع horofi، دیگر مشکلاتی که در حالت قبل با آن برخورد کردیم، پیش نمی‌آید و نمایش عدد با هر تعداد ارقام (کمتر از ۱۵ رقم) به درستی انجام می‌شود.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

به منظور دسترسی به فایل یا کارپوشه محاسباتی این مطلب، می‌توانید با کلیک کردن در اینجا، فایل مورد نظر را با نام number to letter worksheet.zip بارگذاری کرده و با قالب فشرده (zip) دریافت کنید. برای استفاده البته باید آن را از حالت فشرده خارج کرده، در اکسل بازیایی (Open) نمایید.

خلاصه و جمع‌بندی

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

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