قالب عددی سفارشی: ریال و تومان، میلیون، رنگ و شرط، ارقام فارسی

نمایش را عوض کنید، مقدار را نه

Number Format فقط «لباس» عدد است؛ مقدار ذخیره‌شده دست نمی‌خورد. این یعنی می‌توانید مبلغ را به ریال ذخیره کنید، به هزار تومان نمایش دهید و همچنان با آن محاسبه کنید. Ctrl+1 پنجره‌ی Format Cells را باز می‌کند و در دسته‌ی Custom می‌توانید کد قالب دلخواه بنویسید.

ساختار چهاربخشی

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

قالب‌های آماده برای کار ایرانی

#,##0" ریال"                        → 12,500,000 ریال
#,##0" تومان"                       → 1,250,000 تومان   (داده‌ی تومانی)
#,##0," هزار تومان"                 → داده‌ی 1,250,000 نمایش: 1,250 هزار تومان
#,##0,," میلیون ریال"               → داده‌ی 125,000,000 نمایش: 125 میلیون ریال
#,##0;[Red](#,##0);"-"              → منفی قرمز داخل پرانتز، صفر به‌صورت خط تیره
[>=1000000000]#,##0,,," میلیارد";[>=1000000]#,##0,," میلیون";#,##0
;;;                                 → پنهان کردن کامل محتوا (مقدار هنوز هست)
0000000000                          → کد ملی با صفرهای ابتدایی (اگر عدد ذخیره شده باشد)
"کد: "@                             → پیشوند برای متن

دقت کنید: تبدیل ریال به تومان تقسیم بر ده است و قالب عددی راهی برای «تقسیم بر ۱۰» ندارد. اگر گزارش باید تومانی باشد، یا داده را از ابتدا تومانی ذخیره کنید، یا یک ستون محاسباتی =A2/10 بسازید. آن‌چه قالب عددی خوب انجام می‌دهد «هزار تومان» و «میلیون تومان» روی داده‌ی تومانی است.

ارقام فارسی بدون تغییر داده

کد زبان در ابتدای قالب، سیستم رقم را عوض می‌کند. [$-3000000] یعنی ارقام «عربی-هندی توسعه‌یافته» که همان ارقام فارسی (۰۱۲۳) است؛ [$-2000000] ارقام عربی (٠١٢٣) را نشان می‌دهد.

[$-3000000]#,##0                    → ۱۲,۵۰۰,۰۰۰
[$-3000000]#,##0" تومان"            → ۱۲,۵۰۰,۰۰۰ تومان
[$-3000000]0.0%                     → ۱۲.۵%
=TEXT(A2,"[$-3000000]#,##0")        → همان نمایش، اما به‌صورت متن (برای چسباندن در جمله)

نکته‌هایی که کمتر کسی می‌داند

• ;;; محتوا را پنهان می‌کند اما در نوار فرمول دیده می‌شود. برای پنهان‌سازی واقعی باید در تب Protection گزینه‌ی Hidden را هم بزنید و شیت را محافظت کنید (فصل ۷).
• ستاره در قالب، کاراکتر بعدی را تا پر شدن عرض سلول تکرار می‌کند: #,##0*. مبلغ را با نقطه‌چین تا لبه‌ی سلول می‌کشد، مثل فاکتورهای قدیمی.
• زیرخط (_) به اندازه‌ی عرض کاراکتر بعدی فاصله می‌گذارد؛ #,##0_) اعداد مثبت را با اعداد منفی پرانتزدار هم‌تراز می‌کند.
• قالب‌های سفارشی داخل همان فایل ذخیره می‌شوند؛ برای داشتنشان در همه‌ی فایل‌ها آن‌ها را در یک Cell Style تعریف کنید و Style را با Merge Styles به فایل دیگر ببرید.
• جداکننده‌ی هزارگان و اعشار از تنظیمات ویندوز می‌آید، نه از قالب. اگر فایل روی سیستمی با تنظیمات منطقه‌ای متفاوت نقطه و کاما را جابه‌جا نشان داد، مشکل از Region ویندوز یا File ← Options ← Advanced ← Use system separators است.