نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، گرافیک و فناوری اطلاعات فعالیت دارم.

  • قالب‌بندی سفارشی (Custom) توسط ابزار Number Format

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

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

  • Home
  • Number
  • Format Cells
  • Number
  • Custom

ساختار قالب سفارشی

قالب‌های سفارشی در اکسل یک ساختار خاص دارند که می‌توانند از 1 تا 4 بخش داشته باشند، این بخش‌ها با یک ; از یکدیگر جدا می‌شوند. مقادیر مثبت، منفی، صفر و عبارت متنی بخش‌های مختلف این ساختار هستند.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
  1. اختصاص یکی از بخش‌های این ساختار ضروری بوده و بقیه اختیاری هستند.
  2. اگر فقط یکی از بخش‌های این ساختار تعیین شود، اکسل از آن برای تمامی مقادیر استفاده می‌کند.
  3. اگر دو بخش از این ساختار تعیین شود، از اولین بخش برای مقادیر مثبت و صفر و از دومین بخش برای مقادیر منفی استفاده می‌شود.

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

[Blue]#,##0.00_);[Red](#,##0.00);0.00;"Sales"@

ایجاد قالب سفارشی (Custom) در ابزار Format Cells

ایجاد قالب سفارشی (Custom) در ابزار Format Cells

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

#,##0_);[Red](#,##0);

پس نتیجه می‌گیریم که کد ;;; محتوای یک سلول را به طور کامل پنهان می‌کند. همچنین کلیدهای ترکیبی Ctrl+Shift+~ این قالب را به General تغییر می‌دهد.

قالب‌بندی بر اساس اعداد

برخی کاراکترها در ساختار قالب‌های سفارشی مفهوم خاصی دارند که در جدول 2 کاربرد آنها را بررسی می‌کنیم.

کاراکترشرح
0

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

  1. الگو: 0000
  2. ورودی: 43
  3. نتیجه: 0043
#

به معنای یک عدد غیرقطعی است و کاربر را در مورد تعداد رقم اعداد ملزم نمی‌کند.

  1. الگو: #.##
  2. ورودی: 43459.63027
  3. نتیجه: 43459.63
?

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

  1. الگو: #.???
  2. ورودی:
    • 43459.63
    • 43459.630
  3. نتیجه:
    •  43459.63
    • 43459.630
.

به معنای ممیز یا تعداد ارقام اعشاری می باشد.

  1. الگو: 0.00
  2. ورودی: 43459.63027
  3. نتیجه: 43459.63
,

ویرگول 2 کارکرد متفاوت دارد. اگر بعد از ویرگول صفر یا # باشد، مقدار عددی را سه رقم سه رقم از یکدیگر جدا می‌کند. اما اگر بعد از ویرگول صفر یا # نباشد، مقدار عددی را تقسیم بر 1000 می‌کند.

  1. الگو: 0,0.00,
  2. ورودی: 43459.63027
  3. نتیجه: 43.46
*

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

  1. الگو: 0.00*-
  2. ورودی: 43459.63027
  3. نتیجه: 43459.63-----
_

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

  1. الگو: 0.00_);(0.00)
  2. ورودی:
    • -43459.63027
    • 43459.63027
  3. نتیجه:
    • (43459.63027)
    •  43459.63027

کدهای اعداد در ابزار 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

این رنگ‌ها به شرح زیر می‌باشند:

  1. [Black]
  2. [Green]
  3. [White]
  4. [Blue]
  5. [Magenta]
  6. [Yellow]
  7. [Cyan]
  8. [Red]

قالب‌بندی بر اساس شرط

امکان ایجاد 2 شرط منطقی برای تفکیک مقادیر عددی نیز وجود دارد. این شروط دارای 2 بخش عملگرهای منطقی و مقدار هستند که باید داخل [] تعریف شوند. مثلا کد زیر اعداد کوچکتر از 100 را به رنگ قرمز و اعداد بزرگتر یا مساوی 100 را به رنگ آبی نشان می‌دهد.

[Red][<100]0;[Blue][>=100]0

برای اعمال بیش از 2 شرط یا برای تغییر سایر ویژگی‌های سلول مثل رنگ پس‌زمینه، باید از ابزار Conditional Formatting استفاده کنیم. زیرا این ابزار می‌تواند به کمک فرمول‌نویسی قالب‌بندی را با قدرت و انعطاف بیشتری اعمال کند.