فرمت سلول ها در اکسل

سلام دوستان عزیز وقتتون بخیر؛ آرزوی اوقات خوشی رو براتون داریم 🙂 در این جلسه از سری آموزش های پویش پایدار در خدمتتون هستیم برای آموزش فرمت سلول ها در اکسل (Format Cells).

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

همانطورکه در جلسه سوم این دوره آموزشی ذکر شد، اکسل 17,179,869,184 سلول داره (خیلی زیاده) و در حالت پیش فرض تمامی این سلول ها در فرمت General قرار دارند. وقتی میگیم حالت پیش فرض یعنی تنظیمات اولیه ای که زمان باز کردن نرم افزار وجود داره. اما فرمت یعنی چی؟ فرمت نحوه نمایش محتوای داخل سلول رو مشخص میکنه. فرمت یک سلول میتونه عمومی (General)، عددی (Number)، درصدی (Percentage)، متن (Text)، تاریخ (Date)، ساعت (Time)، واحد پولی (Currency)، عدد کسری (Fraction)، عدد علمی (Scientific) و یا حالت های دلخواه دیگه (Custom) باشه که در ادامه این آموزش همه این موارد رو بررسی میکنیم.

نحوه تغییر فرمت یک یا چند سلول در اکسل

همونطورکه گفتیم همه سلول های اکسل در حالت پیش فرض در فرمت General قرار دارن. برای تغییر فرمت سلول ها بعد از انتخابشون، چند راه وجود داره:

1) در بخش Number در زبانه Home کشویی وجود داره که با باز کردن اون میتونید فرمت مورد نظرتون رو انتخاب کنید.

 

 

2) روی سلول های انتخاب شده کلیک راست کنید و گزینه Format Cells رو انتخاب کنید تا پنجره تحت همون عنوان باز بشه و از زبانه Number این پنجره میتونید فرمت مورد نظرتون رو انتخاب کنید. کلید میانبر باز شدن این پنجره CTRL+1 هست.

 

 

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

 

انواع فرمت سلول ها در اکسل

حالا میخوایم راجع به هر کدوم از انواع فرمت هایی که در پنجره Format Cells وجود داره به ترتیب صحبت کنیم:

فرمت General

زمانیکه پنجره Format Cells رو باز میکنید، در توضیحات فرمت General این جمله وجود داره: “General Format Cells Have no Specific Nember Format”. به این معنی که “سلول های با فرمت عمومی فرمت عددی خاصی ندارند”. در واقع زمانیکه یک سلول در این فرمت قرار داره، اگر کاراکترهای عددی داخل سلول وارد کنید، اکسل اون سلول رو به عنوان عدد میبینه. اگر حتی یک کاراکتر متن داخل سلول باشه، اکسل اون رو به عنوان متن تشخیص میده و به نوعی فرمت خودکار هست. شاید براتون سوال پیش بیاد که اینجوری خیلی خوبه و دیگه چه نیازی به تغییر فرمت هست. فرض کنید شما بخواید یک کد ملی یا شماره موبایل که همه کاراکترهای اون عددی هستن رو داخل یک سلول وارد کنید و ابتدای اون کد ملی یا شماره موبایل کاراکتر 0 وجود داشته باشه. وقتی در سلول وارد کنید، 0 اول کد ملی یا شماره موبایل رو برمیداره. چرا؟ چون همه کاراکترها عدد بودن و فرمت General هم محتوای داخل سلول رو عدد تشخیص میده و ابتدای عدد هم صفر قرار نمیگیره.

مثال های اینچنینی زیاد هستن و در این مواقع نیاز هست تا به صورت دستی فرمت سلول رو تغییر بدیم. پس در ادامه به توضیح سایر فرمت ها میپردازیم:

فرمت Number در اکسل

همونطورکه از نام این فرمت مشخصه فقط برای سلول های حاوی اعداد کاربرد داره. مثلاً اگر بخواید عدد وارد شده در سلول در صورتی که منفی باشه به رنگ قرمز دربیاد یا تعداد اعشار رو مشخص کنید از این فرمت استفاده میکنید. گزینه های فرمت Number به شرح زیر هستن:

Decimal Places: تعداد ارقام اعشار عدد وارد شده در سلول رو مشخص میکنه. برای مثال اگر تعداد ارقام اعشار یک سلول رو 2 وارد کرده باشید و عدد 12.5349 رو داخل سلول تایپ کنید، 12.53 در سلول نمایش داده میشه. دقت کنید که نمایش سلول به این صورت هست و در محاسبات و فرمول نویسی های اکسل عدد به همان صورت 12.5349 در نظر گرفته میشود. عدد وارد شده در Formula Bar به طور کامل نمایش داده میشود اما در خود سلول تا 2 رقم اعشار.

Use 1000 Seperator (,): اگر تیک این مورد را فعال کنید، اعداد وارد شده در سلول سه رقم سه رقم با علامت کاما (,) جدا میشن. مثلاً 125000000 به صورت 125,000,000 نمایش داده میشه.

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

1234.10- : حالت پیش فرض اکسل این مورد است و اگر در سلول یک عدد منفی وارد کنیم، به همان صورت نمایش میدهد.

1234.10 : با انتخاب این گزینه، زمانیکه یک عدد منفی در سلول وارد کنید، علامت – را از پشت آن برداشته و رنگ عدد را قرمز میکند. مثلاً اگر در سلول 153- را وارد کنید، به صورت 153 نمایش میدهد.

(1234.10) : با انتخاب این گزینه، زمانیکه یک عدد منفی در سلول وارد کنید، علامت – را از پشت آن برداشته و عدد را داخل پرانتز قرار میدهد. مثلاً اگر در سلول 153- را وارد کنید، به صورت (153) نمایش میدهد.

(1234.10) : با انتخاب این گزینه، زمانیکه یک عدد منفی در سلول وارد کنید، علامت – را از پشت آن برداشته و عدد را داخل پرانتز قرار میدهد و رنگ آن را قرمز میکند. مثلاً اگر در سلول 153- را وارد کنید، به صورت (153) نمایش میدهد.

 

فرمت Currency (واحد پولی) در اکسل

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

 

Decimal Places: مشابه گزینه موجود در فرمت Number، تعداد ارقام اعشار را از این قسمت برای واحد پولی تعیین میکنیم.

Symbol: نماد واحد پولی را از این کشو انتخاب میکنیم و پس از انتخاب نماد مورد نظر، زمانیکه در سلول عددی تایپ کنید، آن نماد چسبیده به عدد درج میشود.

Negative Number: مشابه توضیحات این گزینه در فرمت Number عمل میکند.

 

فرمت Accounting (حسابداری) در اکسل و تفاوت آن با فرمت Currency

فرمت Accounting مشابه فرمت Currency است با چند تفاوت به شرح زیر:

1) در فرمت Currency نماد چسبیده به عدد درج میشه اما در فرمت Accounting نماد در سمت چپ سلول و عدد در سمت راست سلول درج میشه. البته این تفاوت فقط در واحدهای پولی با نماد انگلیسی وجود داره و زمانیکه واحد پولی ریال (یا سایر نمادهای به زبان فارسی) رو انتخاب کنیم، تفاوتی بین دو فرمت وجود نداره.

 

 

2) در فرمت Currency امکان تعیین نحوه نمایش اعداد منفی به اشکال مختلف ذکر شده وجود داره اما در فرمت Accounting اگر عدد منفی وارد کنیم داخل پرانتز به رنگ مشکی نمایش داده میشه و حالت دیگه ای قابل انتخاب نیست.

 

 

فرمت Date (تاریخ) در اکسل

برای توضیح این فرمت لازمه کمی با منطق تاریخ در اکسل آشنا باشیم. مبدأ تاریخ در اکسل 0 ژانویه سال 1900 میلادی است (1/0/1900) و تاریخ های ماقبلش برای اکسل تعریف نشده. اگر شما داخل یک سلول یک تاریخ پس از 0 ژانویه 1900 وارد کنید، اکسل به طور خودکار فرمت آن سلول رو Date قرار میده. اما اگر داخل یک سلول عدد وارد کنید و فرمت سلول رو روی Date قرار بدید، اکسل با منطقی که دارد آن عدد را تبدیل به تاریخ متناظرش میکنه. منطقش هم به این صورته که عدد 0 متناظر تاریخ 1/0/1900 هست و هر یک واحد عددی برابر یک روز در نظر گرفته میشه. پس عدد 1 متناظر تاریخ 1/1/1900 هست. در هر صورت اگر فرمت تاریخ رو برای یک سلول انتخاب کردید، گزینه های اون فرمت به شرح زیره:

 

 

Type: در این بخش میتونید نحوه نمایش تاریخ رو تعیین کنید. مثلاً اگر تاریخ 12/16/2022 رو در سلول وارد کنید و در بخش تایپ گزینه Wendnesday, March 14, 2012 رو انتخاب کنید. این تاریخ به صورت Friday, December 16, 2022 نمایش داده میشه. بقیه موارد رو هم میتونید امتحان کنید و نتیجه اش رو ببینید.

Locale (Location): در این کشو میتوانیم موقعیت جغرافیایی که تاریخ براساس آن درج شده است را وارد میکنیم. در بین گزینه های این کشو، Persian هم وجود دارد.

 

فرمت Time (زمان) در اکسل

در مورد فرمت Time هم بهتره ابتدا منطق کار با ساعت در اکسل رو بدونیم. منطق ساعت در اکسل مشابه منطق تاریخ در اکسل هست. برای مثال زمانیکه شما در یک سلول عدد 0.33333 رو وارد کنید و فرمت اون رو به Date تغییر بدید. اکسل 8:00 رو نمایش میده. چرا؟ چون از نظر اکسل وقتی عدد رو در فرمت ساعت قرار دادید، 0.33333 یعنی یک سوم روز بعد از تاریخ 0 ژانویه سال 1900 که میشه ساعت 8 اون روز.

گزینه های فرمت Time به شرح زیر هستن:

 

Type: در این بخش میتونید نحوه نمایش زمان رو تعیین کنید. مثلاً اگر 8:00 رو در سلول وارد کنید و در بخش تایپ گزینه 1:30PM رو انتخاب کنید. این زمان به صورت 8:00AM نمایش داده میشه. بقیه موارد رو هم میتونید امتحان کنید و نتیجه اش رو ببینید.

Locale (Location): در این کشو میتونیم موقعیت جغرافیایی که زمان براساس آن درج شده است را وارد میکنیم. در بین گزینه های این کشو هم Persian وجود دارد.

 

فرمت Percentage (درصدی) در اکسل

اگر بخواید اعدادی که در سلول ها وارد می کنید به صورت درصدی نمایش داده بشن از این فرمت استفاده می کنید. برای مثال اگر فرمت یک سلول رو Percentage قرار بدید و عدد 3 رو در سلول وارد کنید، به صورت 300% نشون میده. اگر بخواید 3% رو مشاهده کنید باید عدد 0.03 رو وارد کنید. در فرمت Percentage هم میتونید با استفاده از گزینه Decimal Places تعداد ارقام اعشار رو تعیین کنید.

 

 

فرمت Fraction (کسری) در اکسل

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

 

 

Up to one digit (1/4) : با انتخاب این گزینه، صورت و مخرج بخش کسری عدد وارد شده در سلول تک رقمی نمایش داده میشن. مثلاً اگر عدد 124.6546 رو داخل سلول وارد کنید و این گزینه رو انتخاب کنید، عدد به صورت 3/4 124 (124 و سه چهارم) نمایش داده میشه (چون 0.6546 به 3/4 نزدیکه). اگر 124.2527 رو در سلول تایپ کنیم به صورت 1/4 124 (124 و یک چهارم) نمایش میده.

Up to two digit (21/25) : با انتخاب این گزینه، صورت و مخرج بخش کسری عدد وارد شده در سلول دو رقمی نمایش داده میشن. مثلاً اگر عدد 124.6546 رو داخل سلول وارد کنید و این گزینه رو انتخاب کنید، عدد به صورت 36/55 124 نمایش داده میشه. اگر 124.2527 رو در سلول تایپ کنیم به صورت 23/91 124 نمایش میده. حتما خودتون در اکسل امتحان کنید.

As Halves (1/2) : زمانیکه این گزینه را انتخاب کنید، در صورتیکه قسمت اعشاری عدد وارد شده در سلول بین 0.25 تا 0.75 باشد، قسمت کسری 1/2 نمایش داده میشود. اگر قسمت اعشاری عدد کوچکتر از 0.250 باشد، فقط جز صحیح عدد نمایش داده میشود و اگر قسمت اعشاری عدد بزرگتر از 0.75 باشد، جز صحیح عدد به علاوه یک نمایش داده میشود. مثلاً اگر عدد 35.376 رو داخل سلول وارد کنید، به صورت 1/2 35 نمایش میده. اگر عدد 35.1475 رو وارد کنید، به صورت 35 نمایش میده و اگر عدد 35.894 رو وارد کنید، 36 رو نمایش میده.

As quarters (2/4) : با انتخاب این فرمت برای یک سلول، بازه بندی نمایش بخش کسری به صورت جدول زیر است:

 

 

زمانیکه این گزینه را انتخاب کنید، در صورتیکه قسمت اعشاری عدد وارد شده در سلول کوچکتر از 0.125 باشه، جز صحیح عدد وارد شده نمایش داده میشه. اگر بخش اعشاری عدد بین 0.125 تا 0.375 باشه، قسمت کسری به صورت 1/4 نمایش داده میشه. اگر بخش اعشاری عدد بین 0.375 تا 0.625 باشه، بخش اعشاری به صورت 2/4، اگر بین 0.625 تا 0.875 باشه به صورت 3/4 نمایش داده میشه و اگر بخش اعشاری عدد وارد شده در سلول بزرگتر از 0.875 باشه، جز صحیح عدد وارد شده بعلاوه یک در سلول نمایش داده میشه.

مثلاً اگر عدد 35.376 رو داخل سلول وارد کنید، به صورت 1/2 35 نمایش میده. اگر عدد 35.1475 رو وارد کنید، به صورت 35 نمایش میده و اگر عدد 35.894 رو وارد کنید، 36 رو نمایش میده.

As eighths (4/8) : با انتخاب این فرمت برای یک سلول، بازه بندی نمایش بخش کسری به صورت جدول زیر است:

 

 

در صورتیکه بخش اعشاری عدد وارد شده در سلول کوچکتر از 0.063 باشه، جز صحیح عدد نمایش داده میشه. اگر بخش اعشاری بین 0.063 تا 0.188 باشه، قسمت کسری به صورت 1/8، اگر بین 0.188 تا 0.313 باشه، به صورت 2/8، اگر بین 0.313 تا 0.437 باشه، به صورت 3/8، اگر بین 0.437 تا 0.562 باشد، به صورت 4/8، اگر بین 0.563 تا 0.688 باشد به صورت 5/8، اگر بین 0.688 تا 0.813 باشد، به صورت 6/8، اگر بین 0.813 تا 0.938 باشد، به صورت 7/8 نمایش داده میشود و اگر بخش اعشاری عدد وارد شده در سلول اکسل بزرگتر از0.938 باشد، جز صحیح عدد وارد شده بعلاوه یک در سلول نمایش داده میشود.

مثلاً اگر عدد 27.56352 رو در یک سلول با فرمت As eighths (4/8) وارد کنید، به صورت 5/8 27 نمایش داده میشه. اگر عدد 27.9762 رو در سلول وارد کنید، به صورت 28 نمایش داده میشه. حتما این موارد را خودتون در اکسل امتحان کنید تا تمرین بشه.

As sixteenths (8/16) : این فرمت هم بازه بندی های 1/16 داره و مطابق توضیحات بالا میتونید با ورود اعداد مختلف در اکسل کارکرد اون رو متوجه شوید.

 

فرمت Scientific (علمی) در اکسل

در علوم مختلف برای نمایش اعداد بسیار بزرگ یا بسیار کوچک از اعداد علمی استفاده میشه. برای تبدیل یک عدد به عدد علمی ابتدا باید بعد از رقم اول یک ممیز قرار بدیم و به تعداد اعداد بعد از ممیز، توان 10 در نظر بگیریم. مثلا نمایش عدد 473636737338 به صورت عدد علمی،  4.7363×10¹¹ هست. اما در اکسل به جای 10 حرف E (مخفف exponential) نمایش داده میشه (4.7363E+11). در اکسل اگر داخل یک سلول عددی 12 رقمی یا بیشتر وارد کنید، به طور خودکار اون رو در فرمت عدد علمی نمایش میده. برای مثال اگر عدد 686366685747 رو در یک سلول وارد کنید، به صورت 6.86367E+11 نمایش داده میشه. علامت E+11 یعنی 10 به توان 11. همچنین اگر عدد خیلی کوچیک باشه، مثل 0.00000003625، اون رو به صورت 3.625E-08 نمایش میده (E-08 یعنی 10 به توان منفی 8). حالا اگر بخواید خودتون به صورت دستی یک عدد رو در فرمت عدد علمی نمایش بدید باید از پنجره Format Cells گزینه Scientific رو انتخاب کنید.

 

فرمت Text (متن) در اکسل

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

 

 

فرمت Special در اکسل

این فرمت مختص ورود داده هایی از قبیل شماره تلفن، کد پستی (Zip Code)، شماره تأمین اجتماعی (Social Security Number) هست که البته لازم به ذکره خیلی برای کاربرای ایرانی اکسل کاربرد نداره. مثلاً اگر فرمت یک سلول رو روی Phone Number قرار بدید و عدد 633634773 رو در اون سلول وارد کنید، به صورت 4773-363 (63) نمایش میده و این نوع نمایش مربوط به شماره تلفن ها در ایالت متحده آمریکاست. اگر از کشوی پایین فرمت Special گزینه Location رو روی Persian قرار بدید هم چیزی برای نمایش وجود نداره و در واقع فرمت های ایرانی براش تعریف نشده (تا الان).

 

فرمت Custom (سفارشی) در اکسل

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

0%;[Blue](#,##0%)

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

 

ممنون از توجه شما و با آروزی موفقیت های چشمگیر شما عزیزان 🙂

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