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

فرمت سفارشی اعداد
فرمت سفارشی اعداد اکسل (جادوی نمایش اعداد – بخش اول)
اردیبهشت 31, 1395
جریان نقدینگی
جریان نقدینگی دغدغه همیشگی مدیر پروژه
خرداد 14, 1395
نمایش همه

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

مقدمه

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

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

یک‌بار دیگه تأکید می‌کنم که این مطالب در فصل دوم کتاب Excel® Dashboards and Reports, 2nd Edition با عنوان ارتقا بخشیدن گزارش‌ها از طریق فرمت‌ سفارشی اعداد اکسل مطرح شده و سعی دارم این مقاله را به سبک مدیر پروژه عنوان نمایم. دوستان با مراجعه به مطالب اصلی کتاب می‌توانند از دیگر مطالب آن استفاده نمایند.

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

اعمال رنگ‌بندی در فرمت سفارشی اعداد اکسل

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

به‌عبارت‌دیگر یکی از کارهایی که می‌توان برای تغییر نحوه‌ی نمایش اعداد در فرمت سفارشی اعداد استفاده نمود، تغییر رنگ آن است.

در مثال زیر اعداد به‌صورت درصد نمایش داده می‌شود به صورتی که اعداد مثبت آبی‌رنگ و با علامت + و اعداد منفی با رنگ قرمز و با علامت – همانند مطلب عنوان‌شده در مقاله قبل در کادر مربوط به سفارشی نمودن اعداد عبارت ذیل را وارد نمایید:

[Blue]+0%;[Red]-0%

به‌عبارت‌دیگر برای اعمال رنگ موردنظر خود کافی است «عنوان رنگ» را در براکت [] قرار دهید.

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

به‌جای عنوان رنگ در فرمت سفارشی اعداد اکسل کافی است در براکت کلمه «COLOR» به‌علاوه کد آن را وارد نمایید.

به‌عنوان‌مثال در فرمت درصدی زیر اعداد مثبت با رنگ سبز و علامت + و در مقابل اعداد منفی با رنگ قرمز و علامت – نمایش داده می‌شوند:

[COLOR10]+0%;[COLOR3]-0%

کدهای رنگ در اکسل

چطور می‌توان فهمید از کدام کد باید استفاده نمود؟ شما می‌توانید با دریافت این فایل اکسل، کد هر رنگ که در فایل قرار دارد را ببینید. در شکل زیر کد هر رنگ نشان داده‌شده است.

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

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

تفاوت کد رنگ و فرمت سفارشی سلولها (Conditional Formatting)

شاید این سؤال پیش آید که چه مزیتی رنگ‌بندی فرمت سفارشی اعداد نسبت به Conditional Formatting دارد؟ در زیر به دو مزیت آن اشاره شده است:

  • ابزار Conditional Formatting جزء تفکیک‌ناپذیر سلول‌های اکسل است و فقط با آن کار می‌کند.
  • هر جزئی از اکسل که از فرمت سفارشی اعداد استفاده می‌کند به‌صورت خودکار خود را با آن هماهنگ می‌کند. به‌عبارت‌دیگر این قابلیتی است که فرمت سفارشی اعداد دارد ولی Conditional Formatting ندارد.

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

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

هزینه-درآمد با اعمال فرمت سفارشی اعداد

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

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

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

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

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

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

جالب است بدانید اکسل به شما اجازه می‌دهد که این رفتار پیش‌فرض را بر اساس شرایط جدید بازنویسی کنید. شرایط در دو براکت باز و بسته []قرار می‌گیرد.
در دستورالعمل زیر، رنگ آبی به اعداد بالای ۵۰۰ و رنگ قرمز به اعداد زیر ۵۰۰ و “n/a” به اعدادی که مساوی با ۵۰۰ هستند اختصاص یافته است:

[Blue][>500]#,##0;[Red][<500]#,##0;”n/a”

یکی از راه‌های مفید استفاده از فرمت سفارشی اعداد برای تبدیل اعداد به هزار یا میلیون یا … (بسته به بزرگی اعداد) است. در این مثال اعداد بزرگ‌تر یا مساوی با ۱,۰۰۰,۰۰۰ با گرد شدن به‌صورت میلیون و دو رقم اعشار به همراه نمایش m و اعداد بزرگ‌تر یا مساوی ۱,۰۰۰ با گرد شدن به‌صورت هزار و نمایش k و خارج از این محدوده با – نمایش داده می‌شود.

[>=1000000]#,##0.00,,”m”;[>=1000]#,##0,”k”;-

سخن پایانی

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

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

خوشحال میشم دوستان تجارب خودشون را از این فرمت با من و دیگر دوستان به اشتراک بگذارند.

دانلود فایل

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

1 1 vote
امتیازدهی به مقاله
عضویت
مطلع شدن از
guest
14 نظرات
قدیمی ترین
جدیدترین بیشترین رای
Inline Feedbacks
View all comments
مرتضی
مرتضی
4 سال قبل

با سلام
آیا برای نمودارها(چارت)هم امکان کد نویسی هست
بعنوان مثال در یک چارت قسمت منفی و مثبت با رنگ ها تفکیک گردد(قسمت منفی قرمز و مثبت سبز)
ممنون

محمدرضا
محمدرضا
4 سال قبل

با عرض سلام
مطلب کامل و بسیار کاربردی بود. مدت زیادی دنبالش میگشتم. ازتون خیلی ممنونم.

در فرمت اعداد اکسل گاهی سومین نقطه ویرگول هم مشاهده میشه، مقادیر بعد از اون چیه و اینکه کاراکتر های ؟ و @ چه کاربردی داره؟

ممنون

trackback
3 سال قبل

[…] بخش دوم با نکات بیشتری از تنظیم فرمت‌های سفارشی اعداد آشنا […]

امیرحسین
امیرحسین
2 سال قبل

سلام باتشکراز سایت خوبتون میخواستم بپرسم ما که با این روش فرمت سلول هارو اونجور ک میخوایم تغییر میدیم اما این فرمت رو من مثلا ۳۰۰تا سلول با ارقام بزرگ دارم این محدوده ۳۰۰تایی رو درگ میکنم فرمتشو تغییر میدم اما عددها تغییر نمیکنن مگر اینکه روی سلول مورد نظر دوتاکلیک بکنم و سلول رو برام باز کنه بعد که اینترو میزنم محتوا به اون فرمتی که من میخوام تغییر میکنه میخواستم بدونم راهی هست ک نخوام روی تک تک سلول ها کلیک کنم؟چون خیلی زمان میبره
باتشکر

محسن
محسن
2 سال قبل

سلام
راهی هست که در حین تایپ عدد ، ارقام سه رقم سه رقم جدا شوند تا نوشتن اعدا راحت تر گردد. منظودم حین تایپ عدد است و میدونم که بعد از تایپ این امکان هست. حین تایپ ؟؟

بابک
بابک
1 سال قبل

سلام و خسته نباشید ، مقاله شما خیلی به من کمک کرد ، من فرمت زیر رو برای قرار دادن حرف اختصاری K,M,B برای هزار میلیون و میلیارد انجام دادم ، ولی نتونستم همزمان در فرمول برای اعداد منفی هم همین کار و انجام بدم , و اعداد منفی فقط با K نمایش داده میشه ، آیا محدودیتی در تعداد شروط در کاستوم هست ؟ اگر راهنمایی کنید ممنون میشم

[<1000000]0.0," K";[<1000000000]0.0,," M";0.0,,," B"
mehdi
mehdi
1 سال قبل

با سلام. آیا از طریق فرمت کاستوم راهی برای رنگی کردن ارقام خاصی از اعداد یک خانه وجود دارد؟ مثلا ۲۳۴۵ اگر تایپ شد رقم سوم و چهارم قرمز شود به این صورت که ۲۳قرمز و ۴۵ مشکی باقی بماند؟

Entez
Entez
1 سال قبل

با سلام
آیا امکانش هست که با فرمت سل عدد به صورت کیلومتراژ نمایش داده شه
مثلا ۲۵۶۰ بشه ۵۶۰+۲؟