فرمت و ظاهر اعداد در اکسل با Format Cells

در ابتدا اجازه دهید که تعریف دقیق فرمت که در پنجره Format Cells را برای شما بگویم، زیرا بسیاری از کاربران گمان می‌کنند که فرمت یعنی قالب بندی، اما این تعریف در اکسل صادق نیست بلکه در اکسل فرمت یعنی «ظاهر» و «قیافه سلول».

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

Format Cells در اکسل چیست؟

هر سلولی در اکسل یک مقدار دارد و می‌توانیم ظاهر آن سلول را تغییر دهیم تا خواناتر باشد!

ظاهر و قیافه سلول که در اکسل Format Cells گفته می‌شود مستقل از مقدار سلول است. مثلا اگر در سلولی عدد 20 را وارد کنید، در محاسبات هیچ تفاوتی ندارد که رنگ آن سلول آبی است و یا اینکه قرمز است و در هر محاسبه‌ای (اعم از فرمول، فیلتر و مرتب سازی و ... ) همان مقدار 20 در نظر گرفته می‌شود.

برای آنکه ما انسان‌ها اعداد را خواناتر ببینیم و یا بخوانیم، در اکسل قادر هستیم تا ظاهر و قیافه اعداد را به دلخواه تنظیم کنیم و در این مقاله قصد دارم که این تنظیمات دلخواه که در پنجره Format Cells برای اعداد وجود دارد را با هم بررسی کنیم.

ضرورت نیاز به Format Cells اعداد د راکسل

پرسش) اگر اساسا در اکسل چیزی به نام Format Cells نداشتیم، چه مشکلی پیش می‌آید؟

۱) هنگام پرینت گرفتن و یا خواندن اعداد بزرگ دچار مشکل می‌شدیم .

۲) اگر عددی 20 را ببینیم، متوجه نمی‌شویم که واحد اندازه گیری ما درجه سانتیگراد است و یا کیلوگرم. با فرمت سلز اکسل همه ما متوجه خواهیم شد که واحد عدد 20 چیست.

۳) کشورها و سازمان‌ها و ما آدم‌ها استاندارهای مختلفی برای نمایش اعدادی مانند تاریخ و زمان داریم. مثلا در آمریکا تاریخ به صورت 08/28/2024 تایپ می‌شود و در آلمان به صورت 2024.08.24. ما با فرمت سلز می‌توانیم با استاندارد دلخواهمان اعداد را مشاهده کنیم.

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

انتخاب سلول‌ها قبل از Format Cells

بدیهی است که اگر بخواهید ظاهر چندین سلول را به یکباره تغییر دهید، باید‌ آنها را ابتدا انتخاب کنید و سپس به پنجره Format Cells بروید. اما توجه کنید که بهتر است از انتخاب کل ستون / سطر خودداری کنید (در سایت مایکروسافت صراحتا این را نوشته است و در اینستاگرام فرساران لینک و تصویر آن را قرار داده‌ام)، زیرا اینکار باعث زیادتر شدن حجم داده‌ها و در نتیجه اشغال بیشتر RAM خواهد شد. 

به صورت کلی همانطور که در «دوره آموزش اکسل کاربردی» فرساران تاکید می‌کنیم، باید تمامی داده‌های دیتابیس‌های خود را به صورت Table‌ در آورید و توصیه می‌کنم که «ویدئوی آموزش ساخت Table در اکسل» را مطالعه کنید.

از کجا باید ظاهر سلول‌ها را تغییر دهم؟

برای تغییر Format و یا همان ظاهر یک سلول در اکسل ما به صورت کلی ۳ راه داریم:

۱) به پنجره Format Cells بروید. برای اینکار یا کلید Ctrl + 1 را بزنید و یا اینکه روی سلولی R-Click کنید و سپس گزینه Format Cells را انتخاب کنید تا وارد پنجره اصلی شوید. در اینجا تمامی گزینه‌ها را داریم.

۲) از تب Home و از گزینه های Number که در شکل زیر با فلش قرمز نمایش داده شده است، استفاده کنید. (در اینجا فقط گزینه‌های پر استفاده را داریم)

۳) از کلیدهای میانبر Ctrl + Shift + 1 و Ctrl + Shift + 2 و به همین ترتیب تا ... استفاده کنید. (من خودم از کلید Ctrl + Shift + ~ استفاده می‌کنیم که در ادامه این مقاله به شما خواهم گفت که چه فرمتی را خواهد داد.) 

فرمت General اکسل چیست؟

تمامی سلول‌های یک شیت که تازه می‌سازید، با این Format هستند. معمولا این فرمت را من در دوره‌های «آموزش اکسل کاربردی» فرســاران به ۲ روش ترجمه می‌کنیم:

الف) General یعنی طبیعی

وقتی که یک عدد (و نه یک متن) در سلول تایپ می‌شود، اکسل به صورت طبیعی آن را نمایش می‌دهد. یعنی عدد اگر اعشار دارد، آن را با اعشار نمایش می‌دهد و اگر علامت منفی دارد، آن را دقیقا همانطور نمایش می‌دهد. در ضمن اینکه اگر عددی بسیار بزرگ و یا کوچک باشد، اکسل به صورت خودکار و با در نظر گرفتن عرض ستون، آن را به صورت نماد علمی نمایش می‌دهد. یعنی ممکن است عدد ۱۰۰۰۰۰۰ در اکسل به صورت 1E+06 نمایش داده شود و آن را باید بخوانید 1 ضربدر 10 به توان 6.

ب) General یعنی هوشمند

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

🍬 ترفند:‌ برای تنظیم فرمت سلول‌ها به حالت General کلیدCtrl + Shift + ~  را بزنید. (کاراکتر ‍~ در گوشه سمت چپ بالای کیبورد شما - کنار عدد 1 - است و به آن tide می‌گویند.)

تفاوت فرمت Number و Currency و Accounting در اکسل چیست؟

معمولا کاربران اکسل وقتی با این گزینه‌ها برخورد می‌کنند، مطمئن نیستند که کدام را انتخاب کنند زیرا نمی‌دانند که فرمت در اکسل یعنی «ظاهر» و به همین دلیل هیچ مهم نیست که شما کدام یک از این فرمت‌ها را انتخاب می‌کنید. این ۳ گزینه را تست کنید و آنچه را که مناسب می‌دانید را انتخاب کنید. در ادامه این مقاله به بررسی این فرمت‌ها می‌پردازیم.  به تصویر زیر دقت کنید:

سه رقم سه رقم جدا کردن اعداد در اکسل

+ در فرمت Number با گزینه User 1000 Separator که به صورت پیش فرض علامت کاما است، می‌توانید اعداد را سه رقم سه رقم جدا کنید.

+ در فرمت Currency  و Accounting به صورت پیش فرض باید اعداد سه رقم سه رقم جدا شوند و این گزینه را ندارید.

پرسش) به صورت پیش فرض برای سه رقم جدا کردن از علامت کاما، استفاده می‌شود. آیا می‌توان آن را تغییر داد؟

پاسخ) بله اما اینکار را نکنید. باید بدانید که این تنظیمات اکسل از Region در Control Panel ویندوز (سیستم عامل شما) گرفته می‌شود.در همین مقاله این تنظیمات را بررسی خواهیم کرد.

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

Decimal یعنی اعشار و ما با گزینه Decimal Places می‌توانیم مشخص کنیم که در اکسل مایلیم چند رقم اعشار را ببینیم. 

تذکر مهم) هیچ گاه شما نمی‌توانید با تغییر فرمت در واقعیت عدد تغییری بدهید و فقط فرمت برای تنظیم ظاهر است. یعنی نمی‌توانید با تنظیم Decimal Places روی 0، اعشار را حذف/گرد کنید. در دنیای اکسل برای حذف اعشار باید از توابعی مانند TRUNC و یا ROUND استفاده کنید. مقاله «توابع گرد کردن (رند کردن) اعداد در اکسل» را مطالعه نمایید.

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

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

توجه)  باید بدانید که این تنظیمات اکسل (یعنی نحوه نمایش اعداد منفی) از Region در Control Panel ویندوز (سیستم عامل شما) گرفته می‌شود.

کاربرد مهم فرمت Number در اکسل

اگر شما عددی بزرگ مثلا ۹ رقمی را در اکسل تایپ کنید، به صورت پیش فرض در حالت نمایش علمی نمایش داده می‌شود مثلا عدد دو میلیارد اگر عرض ستون کوچک باشد، به صورت 2E+09 در می‌آید. برای اینکه اکسل را مجبور کنید که عدد را همواره به صورت کامل نمایش دهد و از نماد علمی استفاده نکنید، از Number استفاده کنید. کلید میانبر این فرمت سلز در اکسل  Ctrl + Shift + 1 است.

پرسش) چطور در اکسل شماره کارت (۱۶ رقمی) را تایپ کنم؟

پاسخ) به خاطر محدودیت حافظه در کامپیوترها، اگر عددی بیش از ۱۵ رقم داشته باشد، رقم‌های ۱۶ و به بعد آن به صورت صفر نگهداری می‌شوند یعنی می‌توانیم یک عدد ۲۰ رقمی را در اکسل تایپ کنیم اما رقم ۱۶ تا ۲۰ آن به صورت ۰ است. بنابراین قبل از تایپ شماره کارت باید فرمت سلول را در حالت TEXT در آوریم. برای دانستن در مورد این فرمت، مقاله «گزینه text در Format Cells اکسل» را مطالعه کنید.

در مورد فرمت Currency و Accounting در اکسل بیشتر بدانید

این دو فرمت برای تنظیم نحوه نمایش اعدادی که نمایانگر «پول» است در اکسل بکار می‌رود.

+  در هر ۲ می‌توانیم واحد پول را تنظیم کنیم و حتی واحدهای پولی مانند بیتکوین هم در اکسل داریم.

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

+ در فرمت Accounting حتما اعداد منفی به صورت خاصی (احتمالا داخل پرانتز و یا با رنگ منفی) نمایش داده می‌شوند زیرا در حسابداری ما چیزی مانند منفی ۱۰۰۰ تومان نداریم و یا ۱۰۰۰ تومان بستانکاریم و یا بدهکار که حالت بدهکار را در داخل پرانتز نمایش می دهیم. 

پرسش) آیا واحد پول‌های اکسل، گزینه تومان داریم؟

پاسخ) خیر نداریم. زیرا تومان واحد رسمی پول هیچ کشوری نیست. اگر بخواهید که در کنار عددی تومان را نمایش دهید، باید از گزینه Custom در Format Cells استفاده کنید که در ادامه این مقاله خواهید آموخت.

تنظیمات Region برای تغییر اعداد در اکسل

/این قسمت به زودی تکمیل می شود/ 

فرمت دلخواه برای اعداد در اکسل با Custom

همانطور که گفته شد ما مایلیم که اعدادمان را خواناتر ببینیم و در اکسل گزینه‌هایی مانند Number, Currency و ... را داریم. اما در خیلی از موارد این گزینه‌ها پاسخگوی ما نیست و می‌خواهیم که یک ظاهر خاص/ویزه و دلخواه برای اعدادمان بسازیم که در  اکسل این کار با گزینه Custom در پنجره Format Cells قابل انجام است. 

نکته ۱) توجه داشته باشید که ما باید «کد» فرمت دلخواهمان را در این پنجره (به تصویر زیر دقت کنید) بنویسیم. این کدها یک سری قواعد استاندارد است که در وب سایت مایکروسافت به جزئیات آن می‌توانیم دسترسی داشته باشیم.

🍬 ترفند:‌ برای رفتن به پنجره Format Cells کلید CTRL + 1 را بزنید.

نکته ۲) همه گزینه‌هایی که در پنجره Format Cells داریم، دارای یک کد هستند. مثلا اگر شما Number را در این پنجره بر روی سه رقم سه رقم تنظیم کنید و اعشار را ۲ تنظیم کنید، کد آن به شکل زیر خواهد بود:

#,##0.00

ترفند بسیار مهم در یافتن کد Custom

برای یافتن کد یک Format به ترتیب قدم‌های زیر را بروید:
۱) ابتدا به پنجره Format Cells بروید و یکی از گزینه‌ها را بجز Custom انتخاب تنظیم کنید و Ok را بزنید.
۲) مجدد به پنجره Format Cells برگردید و گزینه Custom را انتخاب کنید، کد همان فرمتی که تنظیم کرده‌اید را خواهید یافت.

با انتخاب Custom در پنجره Format Cells ما می‌توانیم با نوشتن کدهایی، ظاهر دلخواهی را برای نمایش فقط اعداد (و نه متن‌ها) مشخص کنیم و اگر در آن سلول عددی وارد شود، آن عدد به شکل دلخواهی که تعریف کنیم، نمایش داده می‌شود و همان ظاهر در هنگام پرینت هم خواهیم داشت. مجدد تاکید می‌کنم که در محاسبات مقدار واقعی سلول در نظر گرفته خواهد شد.

ارقام با معنی و بی معنی در Format Cells  اعداد 

یکی از مفاهیمی که باید قبل از شروع بدانید، ارقام با معنی است. فرض کنید که شما بیست عدد سیب دارید و کافی است بنویسید 20 عدد سیب داریم در اینجا 0 یک رقم با معنی است اما اگر شما سه عدد سیب داشته باشید و بنویسید 3.0 عدد سیب دارم، این 0 که بعد از ممیز است، بی معنی خواهد بود و حتی اگر بنویسید 3.00000 عدد سیب دارم بازهم همه این صفرها ارقام بی معنی هستند.

در کدهای Format Cells اکسل، نشانه # برای نمایش اعداد با معنی است و هرگز اعداد بی معنی را نمایش نمی‌دهد و نشانه 0 در این کدها برای نمایش اعداد بی معنی است.

جدول مثال‌ها و کدهای Format Cells اعداد در اکسل

در ادامه برای شما مثال‌هایی را ارائه کرده‌ایم که بر روی یک عدد خام (عدد سمت چپ) اگر یک کد را اجرا (کد وسط) کنیم، چگونه نمایش داده می‌شود (عدد سمت راست):

1234.59		->	#.#	->	1234.6

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

3		->	0.00	->	3.00
.5					0.50

کد 0 در سمت راست ممیز یعنی نمایش اعشار و اگر رقم اعشار نداشته باشد، صفرهای بی معنی نمایش داده می‌شوند و 00 یعنی تا ۲ رقم اعشار.

12 -> #.0# -> 12.0
1234.568 1234.57

کد # در سمت چپ ممیز یعنی تمام ارقام سمت چپ ممیز نمایش داده شوند. بعد از ممیز کد 0 باعث می‌شود که حتما ۱ رقم اعشار نمایش داده شود و اگر اعشاری نداشته باشد یک 0 بی معنی نمایش داده می‌شود و اگر عددی بیش از ۱ رقم اعشار داشته باشد، کد # سمت راست باعث می‌شود تا همه ارقام اعشار نمایش داده شوند.

کد ؟ در Format Cells اعداد اکسل

کد ؟ یک جانگهدار (Place Holder) است. یعنی جای یک رقم را اگر هم نباشد، نگه می‌دارد و خالی خواهد ماند. از این کد برای تراز اعداد اعشاری استفاده می‌کنیم که اعشار‌ها و یکان و دهگان و ... دقیقا زیر هم قرار گیرند: 

123.56	->	???.??	->		123.56
120.5					120.5
4.56					  4.56
12					 12

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

یکی از نکات جالب در کدهای Format Cells امکان تنظیم نحوه نمایش مقادیر مثبت، منفی، صفر و متن‌‌ها در Format Cells اکسل است. در واقع هر کد از ۴ قسمت که با علامت ; از هم جدا می‌شوند، تشکیل شده است که به ترتیب از چپ به راست نحوه نمایش اعداد مثبت، منفی، صفر و متن‌ها را مشخص می‌کند:

format adad mosbat; format adad manfi; format 0; format Text