مقدمه

«فرمت سفارشی اعداد» یا به اصطلاح Custom Number Format یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود.
مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون به مشکل نمایش اعداد در اکسل و یا تغییر فرمت اعداد افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.

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

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

چند سوال مقدماتی

آیا تا حالا با مشکل نوشتن اعداد منفی در اکسل مواجه شده اید و آیا این اعداد آن گونه که خواستید، نمایش داده شده اند؟
آیا با معظل نمایش اعداد علمی در اکسل روبرو شده اید؟
آیا تا کنون خواسته اید که فرمت پیش فرض اعداد را در اکسل تغییر دهید؟ اگر بخواهید آن را تغییر دهید چه کار می کنید؟

معرفی یک کتاب خوب در زمینه اکسل و فرمت سفارشی اعداد

همه این مطالب به تغییر فرمت اعداد در اکسل بر می گردد و یه مدت پیش که به دنبال یک مطلب می‌گشتم اتفاقی به کتاب Excel® Dashboards and Reports, 2nd Edition از نویسنده پرآوازه اکسل John Walkenbach و Mike Alexander برخورد کردم که حیفم اومد اون رو با شما در میان نگذارم.
این مطلب در فصل دوم کتاب با عنوان ارتقا بخشیدن گزارش‌ها از طریق فرمت‌ سفارشی اعداد، قرار دارد که توصیه می‌کنم حتماً کتاب اصلی را مطالعه نمایید.

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

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

مفاهیم پایه‌ای فرمت سفارشی اعداد

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید

(البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

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

فرمت سلول‌ها در اکسل

3. بر روی گزینه Custom در لیست کلیک کنید تا فرمت سفارشی اعداد باز شود.

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

فرمت سفارشی اعداد در اکسل

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

از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

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

در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار داده‌شده است.

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

+#,##0;-#,##0

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

+1,200

-15,000,000

اصلاح فرمت برای نمایش درصد

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد.

فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

می‌توان زیباتر عمل نمود و دستورالعمل نوشتاری را به‌صورت زیر وارد کرد:

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

درصورتی‌که در دستورالعمل از نقطه کاما استفاده نکنید نشان‌دهنده این است که فقط یک فرمت را انتخاب کرده‌اید و این فرمت هم برای اعداد مثبت و هم اعداد منفی اعمال می‌شود.
ممنون

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و …

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

#,##0,

درصورتی‌که بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:

#,##0,,

به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

گرد کردن اعداد به صورت ضریبی از میلیون

شکل بالا به‌اندازه کافی گویاست ولی برای درک تفاوت این دو فرمت به تصویر زیر که مقایسه فرمت قبل و بعد از اعمال ضریب میلیارد برای هزینه دیسیپلین مکانیکال در یک پروژه است، توجه کنید:

قبل و بعد از گرد کردن اعداد به صورت ضریبی از میلیارد

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

اضافه کردن یک کلمه جهت نمایش بهتر اعداد

در صورت نیاز می‌توان گرد کردن اعداد به ضرایب هزار، میلیون و … را با اضافه کردن یک حرف یا کلمه به انتهای فرمت مشخص‌تر نمود:

#,##0, “k”

با این کار اعداد بدین‌صورت نمایش داده می‌شود

188k

318k

از این تکنیک برای نمایش اعداد مثبت و منفی نیز می‌توان استفاده نمود:

#,##0, "k";(#,##0, "k")

بعد از اعمال این فرمت اعداد مثبت و منفی بدین‌صورت نمایش داده می‌شود:

188k

(318k)

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

#,##0.00,, “m”

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

گرد کردن اعداد به صورت ضریبی از بیلیون با نماش حروف

مخصوصا برای مدیران داشتن یک عدد کلی ملاک است و نیازی نیست که تا ریال آخر اعداد ارائه شود.
پرواضح است برای ارائه گزارش مالی این اعداد باید تا ریال آخر ارائه گردد.

مخفی کردن و در نظر نگرفتن صفر

علاوه بر فرمت اعداد مثبت و منفی اکسل این قدرت را دارد که فرمت اعداد صفر نیز تغییر دهد.
کافی است یک کاما نقطه (;) دیگر اضافه‌شده و فرمت موردنظر خود را برای اعمال به عدد صفر در نظر بگیرید.
به‌صورت پیش‌فرض هر فرمتی که بعد از نقطه کامای دوم در نظر گرفته می‌شود به عدد صفر اعمال می‌شود.
به‌عنوان‌مثال در دستورالعمل زیر هرگاه در سلولی عدد صفر قرارگرفته باشد عبارت «n/a» نمایش داده می‌شود:

#,##0_);(#,##0); “n/a”

درصورتی‌که بخواهید کلاً عدد صفر نمایش داد نشود می‌توانید از این دستورالعمل استفاده کنید:

#,##0_);(#,##0);

همان‌طور که عنوان شد این فرمت فقط بر نمایش سلول‌ها تأثیرگذار است و ماهیت خود اعداد را تغییر نمی‌دهد.
برای درک مطالب شکل زیر توجه کنید:

فرمت سفارشی اعداد برای درصدهای مثبت، منفی و صفر

سخن پایانی قسمت اول

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