فرمت سفارشی اعداد اکسل (جادوی نمایش اعداد – بخش اول)

شمسی سازی پریماورا (P6) یا شمسی کردن تاریخ های پریماورا
اردیبهشت ۲۴, ۱۳۹۵
فرمت سفارشی اعداد اکسل (جادوی نمایش اعداد – بخش دوم)
خرداد ۷, ۱۳۹۵
نمایش همه

فرمت سفارشی اعداد اکسل (جادوی نمایش اعداد – بخش اول)

جادوی نمایش اعداد در اکسل

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

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

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

+1,200
-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

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

+43%
-54%

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

0%_);(0%)

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

 45%
(54%)

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

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

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

 
#,##0,

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

#,##0,,

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

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

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

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

Article11-4

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

 

پرواضح است که خواندن اعداد در حالت دوم برای کاربر، در مواقعی که واقعاً لازم نیست تمام ارقام با دقت زیاد نمایش داده شود بسیار راحت‌تر است.

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

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

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

#,##0, “k”

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

188k
318k

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

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

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

188k
(318k)

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

 
#,##0.00,, “m”

با ترفند قبلی و با اضافه کردن دو رقم اعشار، وقتی‌که اعداد به ضریب میلیون گرد می‌شوند، می توان با نمایش دو رقم اعشار دقت اعداد نمایش داده‌شده را بالا برد.

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

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

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

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

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

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

به‌عنوان‌مثال در دستورالعمل زیر هرگاه در سلولی عدد صفر قرارگرفته باشد عبارت «n/a» نمایش داده می‌شود:

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

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

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

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

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

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

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

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

ممنون

مطالب مرتبط:

8
دیدگاه بگذارید

avatar
4 Comment threads
4 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
5 Comment authors
صحراییان سید محسنعلی رضااحسانعباسابراهیم Recent comment authors
  Subscribe  
جدیدترین قدیمی ترین بیشترین رای
مطلع شدن از
ابراهیم
میهمان
ابراهیم

با سلام. سوالی که دارم این هست که چطور میتوان چند عدد رو که در یک سلول در چند ردیف وارد کرده ایم، کاما دار کنیم؟

عباس
میهمان
عباس

برای تبدیل به میلیون ریال اعداد منفی هم میشه از فرمول زیر در در FORMAT CELL استفاده کرد
(,,###,#);,,###,#

احسان
میهمان
احسان

درود و خسته نباشید
میخوام این فرمت رو پیاده کنم
اعداد اگر زیر میلیون بودن خودشن رو نشون بده
اگر میلیون بودن M جلوشون نشون بده
اگر میلیارد بودن B جلوشون نشون بده
اگر منفی بودن فرمتشون قرمز با پرانتز باشه
اگر مثبت بودن سبز باشه

علی رضا
میهمان
علی رضا

سلام خسته نباشید در صورتی که بخوایم اعداد مثبت رو از اعداد منفی به صورت رنگ مشخص جدا کنیم از کدام فورمل استفاده میکنیم؟ممنون

ثبت نام دوره رایگان
بیش از ۹۰ دقیقه مطلب آموزشی کاربردی
مدرسان
از ۳ مدرس متخصص بیاموزید
کاملاً عملی
درک فضای کار واقعی
ارائه راهکار
راهی برای خروج از فضای خشک تئوریکال و نظری
ثبت نام در دوره کارآموزی مجازی مدیر پروژه
فقط کافیست فرم زیر را کامل کنید
ثبت نام در دوره کارآموزی مجازی مدیر پروژه
فقط کافیست فرم زیر را کامل کنید
برای عضویت در خبرنامه، لطفا اطلاعات زیر را تکمیل نمایید
یا با زدن بر روی دکمه زیر به کانال تلگرام ما بپیوندید
عضویت در کانال تلگرام
تایید ایمیل
کاربر گرامی هم اکنون ایمیلی برای شما ارسال می گردد. لطفا جهت تکمیل فرآیند عضویت، آن را تایید کنید.
برای دانلود کافی است نام و ایمیل خود را وارد نمایید:
ارسال لینک دانلود
کاربر گرامی! هم اکنون ایمیلی برای شما ارسال گردید. لطفا جهت دانلود فایل مزبور، به آدرس ایمیل خود مراجعه نمایید.
در بخش مقالات مطالب مفیدی قرار دارد. پیشنهاد می کنم به آنجا هم مراجعه نمایید.