درباره نویسنده
قالببندی سفارشی (Custom) توسط ابزار Number Format
اکسل بهطور پیشفرض قالبهای عددی مناسبی را در اختیار کاربر میگذارد. با استفاده از قالببندی سفارشی (Custom) میتوانیم با تغییر در این قالبهای عددی، قالب دلخواه خود را ایجاد کنیم. مثل نمایش نام ماه از یک تاریخ مشخص یا اعداد منفی به رنگ قرمز.
یکی از ابزارهای کاربردی اکسل Number Format میباشد که میتوانیم به کمک آن نوع نمایش مقدار یک سلول را بدون تغییر محتوای آن تغییر دهیم. اگر هیچ یک از قالبهای آماده اکسل نیاز ما را برطرف نکرد میتوانیم با رفتن به مسیر زیر یک قالب جدید ایجاد کنیم.
- Home
-
- Number
-
- Format Cells
-
- Number
-
- Custom
ساختار قالب سفارشی
قالبهای سفارشی در اکسل یک ساختار خاص دارند که میتوانند از 1 تا 4 بخش داشته باشند، این بخشها با یک ; از یکدیگر جدا میشوند. مقادیر مثبت، منفی، صفر و عبارت متنی بخشهای مختلف این ساختار هستند.
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
- اختصاص یکی از بخشهای این ساختار ضروری بوده و بقیه اختیاری هستند.
- اگر فقط یکی از بخشهای این ساختار تعیین شود، اکسل از آن برای تمامی مقادیر استفاده میکند.
- اگر دو بخش از این ساختار تعیین شود، از اولین بخش برای مقادیر مثبت و صفر و از دومین بخش برای مقادیر منفی استفاده میشود.
به طور مثال میتوانیم از این ساختار برای ایجاد کد زیر در قسمت Type استفاده کنیم.
[Blue]#,##0.00_);[Red](#,##0.00);0.00;"Sales"@
ایجاد قالب سفارشی (Custom) در ابزار Format Cells
اگر بخواهیم در این ساختار به ازای یکی از مقادیر مقدار خالی در سلول نشان داده شود باید آن بخش را خالی بگذاریم. مثلا کد زیر به ازای مقدار صفر چیزی در سلول نشان نمیدهد.
#,##0_);[Red](#,##0);
پس نتیجه میگیریم که کد ;;; محتوای یک سلول را به طور کامل پنهان میکند. همچنین کلیدهای ترکیبی Ctrl+Shift+~ این قالب را به General تغییر میدهد.
بیشتر بخوانید: بکارگیری AutoFill و ایجاد لیست خودکار
قالببندی بر اساس اعداد
برخی کاراکترها در ساختار قالبهای سفارشی مفهوم خاصی دارند که در جدول 2 کاربرد آنها را بررسی میکنیم.
کاراکتر | شرح |
---|---|
0 | به معنای یک عدد قطعی است. وجود یک صفر به منزله نمایش مقدار عددی است با حداقل یک رقم و دو صفر نمایش عددی است با حداقل 2 رقم. بنابراین وجود یک صفر به معنای نمایش یک عدد با هر تعداد رقم میباشد.
|
# | به معنای یک عدد غیرقطعی است و کاربر را در مورد تعداد رقم اعداد ملزم نمیکند.
|
? | عملکردی مشابه با صفر و # دارد، اما به جای هر عدد خالی و صفر یک فاصله (Space) قرار میدهد. مثلا وقتی بخواهیم ممیزها زیر هم قرار بگیرند از آن استفاده میکنیم.
|
. | به معنای ممیز یا تعداد ارقام اعشاری می باشد.
|
, | ویرگول 2 کارکرد متفاوت دارد. اگر بعد از ویرگول صفر یا # باشد، مقدار عددی را سه رقم سه رقم از یکدیگر جدا میکند. اما اگر بعد از ویرگول صفر یا # نباشد، مقدار عددی را تقسیم بر 1000 میکند.
|
* | کاراکتری که بعد از * قرار بگیرد تمام فضای سلول را پر میکند.
|
_ | باعث میشود که به اندازه عرض کاراکتری که بعد از آن میآید فضای خالی در نظر گرفته شود. معمولا برای همترازی اعداد مثبت و منفی که داخل پرانتز نشان داده میشوند بهکار میرود.
|
کدهای اعداد در ابزار Format Cells
قالببندی بر اساس عبارت متنی
برای نشان دادن یک عبارت متنی به همراه اعداد، باید رشته مورد نظر را بین "" قرار دهیم. مثلا کدهای زیر مقدار 43.46K و 0.4M را برای عدد 43459.63027 نشان میدهد. در کد اول، ویرگول عدد را تقسیم بر 1,000 کرده و حرف K نشانه هزار میباشد. در کد دوم، ویرگول اول عدد را تقسیم بر 1,000 کرده و ویرگول دوم نتیجه آن را دوباره تقسیم بر 1,000 میکند. به عبارتی دیگر ویرگول اول و دوم عدد را تقسیم بر 1,000,000 میکند و حرف M نشانه میلیون میباشد.
0.00,"K"
0.00,,"M"
بیشتر بخوانید: توابع جایگزین عملگرهای ریاضی
قالببندی بر اساس تاریخ
تاریخ در اکسل از جنس عدد میباشد، بنابراین برای نمایش اعدادی که از جنس تاریخ هستند میتوانیم از کدهای جدول 3 استفاده کنیم.
کاراکتر | شرح | نتیجه |
---|---|---|
m | ماه مربوط به یک تاریخ مشخص را به صورت یک عدد تک رقمی نشان میدهد. | 1−12 |
mm | ماه مربوط به یک تاریخ مشخص را به صورت یک عدد دو رقمی نشان میدهد. | 01−12 |
mmm | نام ماه مربوط به یک تاریخ مشخص را به صورت مخفف نشان میدهد. | Jan−Dec |
mmmm | نام ماه مربوط به یک تاریخ مشخص را به صورت کامل نشان میدهد. | January−December |
mmmmm | نام ماه مربوط به یک تاریخ مشخص را با یک حرف نشان میدهد. | J−D |
d | روز مربوط به یک تاریخ مشخص را به صورت یک عدد تک رقمی نشان میدهد. | 1−31 |
dd | روز مربوط به یک تاریخ مشخص را به صورت یک عدد دو رقمی نشان میدهد. | 01−31 |
ddd | نام روز مربوط به یک تاریخ مشخص را به صورت مخفف نشان میدهد. | Sun−Sat |
dddd | نام روز مربوط به یک تاریخ مشخص را به صورت کامل نشان میدهد. | Sunday−Saturday |
yy | سال مربوط به یک تاریخ مشخص را به صورت یک عدد دو رقمی نشان میدهد. | 00−99 |
yyyy | سال مربوط به یک تاریخ مشخص را به صورت یک عدد چهار رقمی نشان میدهد. | 1900−9999 |
کدهای تاریخ در ابزار Format Cells
قالببندی بر اساس زمان
زمان در اکسل از جنس عدد میباشد، بنابراین برای نمایش اعدادی که از جنس زمان هستند میتوانیم از کدهای جدول 4 استفاده کنیم.
کاراکتر | شرح | مثال |
---|---|---|
h | ساعت مربوط به یک زمان مشخص را به صورت یک عدد تک رقمی نشان میدهد. | 0−23 |
[h] | ساعت سپری شده مربوط به یک زمان مشخص را نشان میدهد. اگر نتیجه یک فرمول بزرگتر از 24 ساعت باشد باید از این قالب استفاده کنیم. | 0−23 |
hh | ساعت مربوط به یک زمان مشخص را به صورت یک عدد دو رقمی نشان میدهد. اگر قالب شامل AM یا PM باشد، ساعت بر مبنای 12 ساعت میباشد. در غیر این صورت مبنای ساعت 24 ساعت میباشد. | 00−23 |
m | دقیقه مربوط به یک زمان مشخص را به صورت یک عدد تک رقمی نشان میدهد. قالب m باید بعد از قالب h یا hh، یا قبل از قالب ss آورده شود. در غیر اینصورت مقدار ماه به جای دقیقه نشان داده میشود. | 0−59 |
[m] | دقیقه سپری شده مربوط به یک زمان مشخص را نشان میدهد. اگر نتیجه یک فرمول بزرگتر از 60 دقیقه باشد باید از این قالب استفاده کنیم. | 0−59 |
mm | دقیقه مربوط به یک زمان مشخص را به صورت یک عدد دو رقمی نشان میدهد. قالب m باید بعد از قالب h یا hh، یا قبل از قالب ss آورده شود. در غیر اینصورت مقدار ماه به جای دقیقه نشان داده میشود. | 00−59 |
s | ثانیه مربوط به یک زمان مشخص را به صورت یک عدد تک رقمی نشان میدهد. | 0−59 |
[s] | ثانیه سپری شده مربوط به یک زمان مشخص را نشان میدهد. اگر نتیجه یک فرمول بزرگتر از 60 ثانیه باشد باید از این قالب استفاده کنیم. | 0−59 |
ss | ثانیه مربوط به یک زمان مشخص را به صورت یک عدد دو رقمی نشان میدهد. اگر بخواهیم میلیثانیه مربوط به یک زمان مشخص را نشان دهیم از قالب h:mm:ss.00 استفاده میکنیم. | 00−59 |
AM/PM−A/P | ساعت مربوط به یک زمان مشخص را بر مبنای 12 ساعت نشان میدهد. | 12AM−11PM |
کدهای زمان در ابزار Format Cells
بیشتر بخوانید: خطاهای فرمولنویسی
قالببندی بر اساس رنگ
اکسل از 8 رنگ مختلف برای تفکیک مقادیر مثبت، منفی و صفر استفاده میکند، این رنگها باید در ابتدای هر بخش و داخل [] تعریف شوند. مثلا کد زیر مقدار مثبت را به رنگ آبی و مقدار منفی را به رنگ قرمز نشان میدهد.
[Blue]0;[Red]0
این رنگها به شرح زیر میباشند:
- [Black]
- [Green]
- [White]
- [Blue]
- [Magenta]
- [Yellow]
- [Cyan]
- [Red]
قالببندی بر اساس شرط
امکان ایجاد 2 شرط منطقی برای تفکیک مقادیر عددی نیز وجود دارد. این شروط دارای 2 بخش عملگرهای منطقی و مقدار هستند که باید داخل [] تعریف شوند. مثلا کد زیر اعداد کوچکتر از 100 را به رنگ قرمز و اعداد بزرگتر یا مساوی 100 را به رنگ آبی نشان میدهد.
[Red][<100]0;[Blue][>=100]0
برای اعمال بیش از 2 شرط یا برای تغییر سایر ویژگیهای سلول مثل رنگ پسزمینه، باید از ابزار Conditional Formatting استفاده کنیم. زیرا این ابزار میتواند به کمک فرمولنویسی قالببندی را با قدرت و انعطاف بیشتری اعمال کند.