فرمت سل در اکسل : راهنمای جامع

این راهنما در 4 خرداد 1398 بروزرسانی شد.

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

در این آموزش به بررسی ساخت فرمت سل سفارشی ( custom format cell )در اکسل می پردازیم.

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

فرمت هایی که به راحتی و بدون هیچ زحمتی اعداد را :

1. به صورت سه رقم سه رقم از هم جدا کنید
2. به اعداد دارای شرایط خاص رنگ بندی و امکانات خاص دهید
3. اعداد را به صورت درصدی نمایش دهید
4. اعداد دارای اعشار را در یک ستون به صورتی قرار دهید که علامت اعشار در یک راستا باشد
5. برای واحد های پولی واحد پول به صورت خودکار اضافه شود
6. تعداد اعشار دلخواه بدون تایپ داشته باشید
7. اعداد را به تعداد عدد دلخواه گرد نمایید
8. و هزاران حالت دیگر که با دانستن الفبای فرمت نویسی می توانید آنها را با توجه به کسب و کار خویش آماده نمایید.

1- فرمت سل های پیش فرض اکسل

در قسمت اول فرمت سل های پیش فرض اکسل را با هم بررسی می کنیم سپس سراغ فرمت نویسی سل خواهیم شد.

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

2- چگونه فرمت سل دلخواه را بسازیم؟

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

1. سلول یا ناحیه سلول ها را انتخاب کنید و کلید ترکیبی Ctrl+1 را بزنید تا پنجره فرمت ها باز شود.
2. در Category، Custom را انتخاب کنید.
3. کد فرمت مدنظرتان را در کادر  Type بنویسید.
4. OK را کلیک کنید تا فرمت ساخته شده ذخیره شود.

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

اما وقتی به فرمت های موجود نگاه می کنید کد های عجیب و غریبی می بینید؛ حالا باید چکار کنیم؟ 

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

1-2- اجزای فرمت عددی اکسل

خب؛ حالا که قصد یادگیری کد های فرمت را دارید، بگذارید از قدم صفر کد های فرمت را با هم بررسی کنیم.

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

POSITIVE; NEGATIVE; ZERO; TEXT

1. فرمت اعداد مثبت
2. فرمت اعداد منفی
3. فرمت برای صفر
4. فرمت برای مقادیر متنی

2-2- قوانین فرمت نویسی اکسل

1. فرمت سل سفارشی فقط حالت نمایش را تغییر می دهد یعنی اینکه مقدار داخل سلول به چه صورت نمایش داده شود فقط تغییر می کند و مقدار ذخیره شده در سلول هیچ تغییری نخواهد کرد.
2. زمانی که فرمتی را سفارش سازی می کنید، این فرمت به صورت کپی از فرمت اصلی ذخیره می شود. فرمت اصلی حذف یا تغییری نمی کند.
3. لازم نیست هر چهار بخش فرمت در یک فرمت مورد استفاده قرار گیرد.
4. اگر فرمت شامل یک بخش باشد این فرمت برای همه چهار نوع مورد استفاده قرار می گیرد.
5. اگر فرمت شامل دو بخش باشد، اولین بخش برای اعداد مثبت و صفر، بخش دوم برای اعداد منفی استفاده می شود.
6. برای اینکه بخشی از مقادیر ورودی از دید کاربران مخفی باشد می توانید بخش مربوط به آنها را خالی گذارید. به طور مثال اگر می خواهید اعداد صفر و منفی برای کاربران نمایش داده نشود می توانید از کد زیر استفاده کنید.

General; ; ; General

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

• برای اینکه فرمتی را خواسته باشید حذف کنید، آن را انتخاب کنید و دکمه Delete را بزنید.

3-2- متغیر عددی و متنی در فرمت سل

برای شروع چهار متغیر اصلی که در فرمت نویسی به آنها نیاز داریم را با هم بررسی کنیم.

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

مثال زیر را برای تمرین بیشتر بررسی کنید.

4-2- نکات و دستورالعمل فرمت نویسی اکسل

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

3- چگونه اعداد اعشاری را کنترل کنیم؟

مکان نقطه اعشار توسط ” . (period) ” مشخص می شود. تعداد رقم اعشار بوسیله متغیر عددی صفر معمولا مشخص می شود. برای مثال:

• ۰  یا # اعداد را بدون اعشار نشان می دهد.
•  ۰٫۰ یا #.۰ اعداد را با یک رقم اعشار نمایش می دهند.
• ۰٫۰۰ یا #.۰۰ اعداد را با دو رقم اعشار نشان می دهند.
• و…

فرقی که بین ۰  و #  می باشد در این است که اگر در سمت چپ نقطه اعشار # باشد عدد وارده اگر کمتر از یک باشد با نقطه اعشار شروع می شود.

به طور مثال فرمت  #.۰۰ عدد وارده کوچکتر از یک۰٫۲۵  را به صورت .۲۵ نمایش می دهد در صورتی که اگر در ۰٫۰۰ باشد عدد را به صورت ۰٫۲۵ نمایش خواهد داد.

4- سه رقم سه رقم کردن اعداد در اکسل

برای اینکه فرمتی بسازیم تا سه رقم سه رقم برایمان اعداد را جدا کند، فرمت مدنظر باید دارای کاما (,) باشد. به مثال های زیر توجه کنید:

اگر این کامایی که استفاده کردیم توسط” #” یا” “۰ یا “؟” محصور شده بود که به شکل بالا سه رقم سه رقم از هم جدا می شوند.

ولی اگر بعد از کاما هیچ متغیر دیگری نباشد کاما در این حالت به صورت مقیاس هزارتایی عمل می کند.

یعنی عدد را تقسیم بر ۱۰۰۰ می کند. اگر تعداد کاما دو تا باشد دو بار تقسیم بر ۱۰۰۰ می کند و به همین ترتیب ادامه دارد.

خب حالا که کاربرد های کاما آشنا شدیم بیاییم و خروجی را زیباتر کنیم.

گفتیم اگر در انتهای فرمت کاما بگذاریم عدد تقسیم بر ۱۰۰۰ می شود. می خواهم تا به عدد نماد هزارتایی هم اضافه کنم.

5- چگونه تومان و ریال به اعداد در اکسل اضافه کنیم؟

دو راه برای نمایش متن در سلول وجود دارد:

• برای یک کاراکتر، قبل از آن از بک اسلش (\) استفاده کنید.
• برای چند کاراکتر (رشته متنی) از دابل کوتیشن ” ” استفاده نمایید.

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

• برای اینکه خوانایی فرمت بهتر باشد از فاصله قبل از “\” استفاده کنید.

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

به طور مثال می خواهید برای اعدادی که مثبت هستند متن “Up” و برای اعداد منفی متن “Down” به همراه عدد نمایش داده شود.

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

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

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

6- چگونه صفر اول شماره موبایل را در اکسل بنویسیم؟

اگر خواسته باشید عدد به این شکل وارد کنید ۰۰۰۸ اکسل به طور پیش فرض این مقدار را برابر با ۸ می داند درحالی که قصد شما آن نبوده است.

حالا اگر ما به این مدل نیاز داشته باشیم باید چه کار کنیم؟

ساده ترین و راحت ترین راه انتخاب فرمت متن (Text Format)  می باشد. ولی اگر می خواهید همه سلول ها دارای تعداد مشخصی عدد باشند و می خواهید از صفرهای بی ارزش استفاده کنید می توانید در فرمت مدنظر به تعداد ارقام لازم صفر بزنید. به شکل زیر توجه نمایید.

تکنیک: اگر می خواهید شماره تلفن، کدپستی یا شماره خاصی را وارد کنید که شامل صفرهای بی ارزشی می باشد ساده ترین راه استفاده از Special formats می باشد یا اینکه با توجه به مثال فوق فرمت مدنظر تان را به صورت سفارشی بسازید. برای مثال فرمت عددی با ۷ کاراکتر عددی به صورت ۰۰۰۰۰۰۰ یا اینکه فرمت عددی به این شکل باشد

۰۰۰-۰۰۰۰-۰۰-۰

7- محاسبه درصد در اکسل

برای آنکه عدد وارده به صورت درصدی از  100 باشد و همراه با علامت % نیز باشد از علامت % در فرمت استفاده کنیم.
مثال های زیر برای بررسی چند حالت از این نوع فرمت می باشد.

8- اعداد کسری در فرمت سل اکسل

اعداد کسری و مخلوط صورتی از اعداد اعشاری می باشد با این توضیح که عدد وارده شامل عدد صحیح باضافه مقدار اعشار آن به صورت کسری می باشد. به طور مثال ۲٫۵  به شکل   ½ ۲ نمایش داده می شود.

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

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

در شکل زیر حالت های مختلف را در مثالی بررسی کردیم.

نکته ی جالبی که وجود دارد؛ برای آنکه حرفه ای باشید فرمت اعداد مخلوط را به نحوی بنویسید که “/” آنها همه در یک راستا باشد. برای آن از کاراکتر “؟” در فرمت نویسی استفاده کنید.

8- اعداد علمی در اکسل

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

9- چگونه اعداد منفی را در پرانتز قرار دهیم؟

در ابتدای بحث گفتیم که هر فرمت شامل ۴ بخش می باشد و همچنین گفتیم که  اگر فرمتی شامل دو بخش باشد بخش اول آن برای اعداد مثبت و صفر و بخش دوم آن برای اعداد منفی درنظر گرفته خواهد شد.

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

برای اینکه عدد منفی در درون پرانتز قرار گیرد؛ خیلی راحت می توانیم کد آن را به شکل زیر نوشت:
 #.۰۰; (#.۰۰)

در ادامه این موضوع را بررسی خواهیم نمود ولی برای اینکه از لحاظ نمایش بهتر باشد برای اعداد مثبت هم به اندازه ی یک پرانتز فاصله و جای خالی ایجاد می کنیم با استفاده از (_

10- چگونه به جای مقدار صفر در سلول Dash یا سلول را خالی بگذاریم؟

ابتدا واضح تر بگویم که قرار است چه کاری را انجام دهیم. اگر با Accounting format کار کرده باشید، این نوع فرمت به جای عدد صفر در سلول مقدار “-” قرار می دهد.

حالا می خواهیم بررسی کنیم به چه نحوی می توانیم به این صورت   “-” بزاریم یا اصلا هیچ مقداری قرار نگیرد.

در ابتدای بحث که گفتیم هر فرمت شامل چهار بخش هست و بخش سوم آن را گفتیم که مربوط به عدد صفر می باشد.

حالا برای اینکه مقدار داخل سلول زمانی که صفر بود به صورت “-” نمایش داده شود باید بخش سوم آن را مقدار “-” قرار دهیم.

برای اینکه مقداری برای آن درنظر گرفته نشود در بخش سوم مقداری را تایپ نمی کنیم.

11- ایجاد تورفتگی با فرمت اکسل

اگر می خواهید داده هایتان به صورت شیک نمایش داده شود و همچنین نمی خواهید داده ها به طرفین سلول چسبیده باشند می توانید از “_” استفاده کنید. این کاراکتر به اندازه کاراکتر بعد از خودش فضای خالی ایجاد می کند.

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

• تورفتگی از سمت چپ :    )_
• تورفتگی از سمت راست: (_

اغلب این مورد برای اعداد مثبت استفاده می شود تا با پرانتز اعداد منفی برابر شود و از لحاظ زیبایی بهتر نمایش داده شود.

در طی چند مثال زیر این تکنیک را با هم بررسی کنیم:

12- چگونه می توان رنگ فونت را بر اساس مقدار سلول در اکسل تغییر داد؟

 یکی از بهترین و ساده ترین راه ها برای رنگ متن ها استفاده از فرمت رنگی هست.

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

همچنین کد رنگ های زیر را هم می توانید در فرمت نویسی استفاده نمایید.

طریقه استفاده از کد رنگ ها همانند رنگ های عادی به این شکل [COLOR 24] می باشد.

13- فرمت تاریخ و زمان در اکسل

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

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

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

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

اکسل زمان را به صورت یک رشته اعداد در سلول ذخیره می کند که فرمت انتخابی ما باعث می شود تا به حالت مدنظر نمایش داده شود.

1-13- تاریخ در اکسل

همه تاریخ ها به صورت اعداد صحیح که شماره روز هستند از  January 1, ۱۹۰۰ که به صورت عدد ۱ تا December 31, 9999  که به صورت عدد ۲۹۵۸۴۶۵ هست ذخیره می شود.

با این توضیح پس:

عدد ۲ نمایانگر     2-Jan-1900

عدد ۳ نمایانگر ۳-Jan-1900

۴۲۷۳۶ هم نمایانگر ۱- Jan – 2017  می باشد چون از ۴۲۷۳۶ روز از اول سال ۱۹۰۰ می گذرد.

در نسخه های بالاتر نرم افزار اکسل ابتدای تاریخ شمسی را ۱۱-۱۰-۱۲۷۸ می داند.

2-13- زمان در اکسل

زمان در اکسل به صورت عدد اعشاری ذخیره می شود. این عدد اعشاری از ۰٫۰ شروع شده و تا ۰٫۹۹۹۹۹ ادامه دارد. عدد صفر برای ساعت ۰:۰:۰ و عدد ۰٫۹۹۹۹۹ برای ساعت ۲۳:۵۹:۵۹  خواهد بود.

برای مثال :

۰٫۲۵ ساعت ۶ صبح خواهد شد و ۰٫۵ ساعت ۱۲ ظهر همچنین ساعت ۱۲:۵۹:۵۹ ۰٫۵۴۱۶۵۵۰۹۳ خواهد شد.

3-13- تاریخ و زمان در اکسل

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

 January   1, 1900 6:00 AM به صورت ۱٫۲۵ نمایش داده می شود.

January 1, 2015 12:00 PM به صورت ۴۲۷۳۶٫۵  خواهد بود.

4-13- یک سوال: چگونه تاریخ را به عدد تبدیل کنیم؟

دو راه برای این موضوع وجود دارد:

۱-حالت اول اینکه بر روی سلولی که تاریخ و زمان در آن هست کلیک کنید و سپس از طریق Format Cell فرمت آن را به حالت general تبدیل نمایید. در این حالت مقدار تاریخ به عدد تبدیل خواهد شد.

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

تابع ()DATEVALUE مقدار تاریخ را به عدد تبدیل می کند. آرگومان این تابع تاریخی خواهد بود که می خواهیم تبدیل به عدد نماییم.

 =DATEVALUE(“1/1/2017”)

تابع ()TIMEVALUE نیز زمان را به عدد تبدیل می کند. برای مثال:

=TIMEVALUE (“6:30 AM”)

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

نکته ای که بد نیست بدانید اینکه تاریخ قبل از مبدایی که اکسل در نظر گرفته یعنی همان ۱-۱-۱۹۰۰ قابل قبول آن نیست. پس نمی توانیم از تبدیل ماقبل این تاریخ را تبدیل به عدد کنیم.

5-13- فرمت پیش فرض اکسل برای تاریخ

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

این حالات پیش فرض با توجه به تنظیمات سیستم عامل تان می باشد که می توانید با تغییر تنظیمات سیستم عامل تان این ها را دستخوش تغییر کنید.

چگونه می توان حالت های مختلف را برای این نوع فرمت انتخاب کرد؟

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

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

یا اینکه به طور از پنجره Format Cells حالت مدنظر را انتخاب نمود.

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

اما موضوع اصلی مان یعنی ساخت فرمت زمانی دلخواه خودمان!

مثل بقیه فرمت نویسی ها در پنجره Format Cells که با زدن کلید ترکیبی crtr+1  یا اینکه از تب home و بخش Number این پنجره را انتخاب نمایید.

سپس از تب Number بخش Custom را کلیک نمایید تا وارد بحث فرمت نویسی شویم.

6-13- فرمت سل سفارشی زمان و تاریخ در اکسل

ابتدا نکات اصلی مربوط به فرمت نویسی زمان و تاریخ را بررسی کنیم سپس سراغ چند مثال برویم.

برای نمایش زمان کدهای زیر وجود دارد:

در دو جدول فوق از m استفاده شده است حالا اکسل چگونه متوجه شود که منظورمان ماه بوده است یا دقیقه؟

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

برای نگارش فرمت می توانید از جداکننده های comma (,) dash (-), slash (/), colon (:) و یا هر جدا کننده  دیگری استفاده نمایید. برای مثال به جدول زیر توجه کنید.

حالا فرض کنید که می خواهید محاسبات مربوط به ساعت کاری یک مجموعه یا فردی را در طی هفته انجام دهید. این موضوع می تواند بر حسب دقیقه یا ساعت باشد. چون در حالت عادی محدوده ساعت بین ۰ تا ۲۴ می باشد و محدوده دقیقه و ثانیه بین ۰ تا ۶۰ می باشد برای برطرف نمودن این مشکل می توانیم از حالت زیر استفاده نماییم.

حالت ۱ :

[h]:mm:ss

در این حالت ساعت از محدوده ۲۴ ساعته خارج می شود و می تواند مقدار بیشتری را اختیار نماید. نکته ای که  وجود دارد نمی توانیم برای دقیقه و ثانیه براکت بگذاریم.

حالت دوم:

[m]:ss

در حالت دوم دقیقه از محدوده ۶۰ دقیقه ای خارج می شود. حتی اگر برای سلولی مقدار ساعت هم وارد کنیم مقدارش تبدیل به دقیقه می شود. در اینجا هم نمی توانیم برای ثانیه براکت درنظر بگیریم.

حالت سوم:

[s]

در این حالت کل خروجی به ثانیه نمایش داده می شود.

بیشتر بدانید:

1. آموزش اقساط وام و سود در اکسل
2. آموزش pivot table