نکات کاربردی اکسل در حسابداری

کاربردی ترین گزارشات سازمانی گزارشاتی هستند که به مدیران اجازه می دهند عارضه های سازمان خود را بخوبی بشناسند و بر مبنای آن تصمیم گیری نمایند. اما چه رویکردهایی مناسب تهیه گزارشات مدیریتی هستند؟ آیا جلسات تصمیم گیری زمان ارایه همه معضلات سازمان است؟ بطور مثال اگر میخواهید در خصوص افزایش هزینه های سازمان، افت فروش، کاهش میزان انگیزه پرسنل و … صحبت نمایید تمامی مسایل را مرور می کنید؟ مطمناً مهمترین مسایل در اولویت هستند. یکی از این رویکردها و مهمترین آنها اصل پارتو می باشد. (می توانید در مورد اصل پارتو در ویکی پدیا بخوانید. مقاله ای جالب تحت عنوان “این بیست درصد نفرین شده” نیز در وبسایت گروه متمم وجود دارد.)

اما چطور می توان نمودار پارتو را در اکسل رسم کرد؟درآموزش اکسل امروز با ما همراه باشید:

گام اول: دیتاهایتان را بدرستی تنظیم نمایید.

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

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

گام دوم:  از جدول داده هایتان یک نمودار ستونی بسازید.

گام سوم:  ستون سهم تجمعی را copy کرده و روی نمودار paste نمایید تا سری اعداد تجمعی نیز به نمودار اضافه گردد. سپس به منوی Change Chart Type بروید و تیک قسمت Secondary Axis مربوط به سری تجمعی را بزنید. ضمنا نوع نمودار مربوط به سهم تجمعی باید به صورت Line باشد.

در Excel این توانایی را داریم که قالب بندی یا فرمت یک سلول را تغییر داده و فرمت آن سلول را مثلاً تاریخ یا درصد یا زمان یا متن یا.... کنیم. همچنین می توانیم دور سلول کادر بیاندازیم یا رنگ زمینه آنرا عوض کنیم. برای قالب بندی خانه ها در Excel مراحل زیر را طی می کنیم:

روش اول:

1- انتخاب منوی Format
2- انتخاب گزینه Cells

روش دوم:

بر روی خانه های مورد نظرکلیک راست کرده و گزینه Format Cells را انتخاب می کنیم.
با اجرای یکی ازسه روش بالا پنجره ای باز می شود که دارای Tab های زیر است:

روش سوم:

فشردن کلیدهای ctrl +1

الف - Number:

توسط این Tab می توانیم نوع اطلاعات ورودی را تعیین کنیم. این Tab شامل گزینه های زیر می باشد:
- Sample: هر فرمتی را که انتخاب کنیم، بر روی محتوای سلول انتخابی نمایش می دهد.
- Category: در این قسمت نوع داده را مشخص می کنیم که شامل انواع زیر می باشد:

1- General: این گزینه عددها را به صورت رشته ای از رقمهای متوالی و بدون هر گونه قالب بندی نشان می دهد. و اگر عددی در سلول جا نشودآنرا به صورت نمایی نشان می دهد.

2- Number: مقادیر را بصورت رشته ای از رقمهای متوالی نشان می دهد. در این حالت اگر عدد در سلول نگنجد سلول بزرگتر می شود. همچنین در این قسمت می توانیم تنظیمات زیر را انجام دهیم:
- Decimal Places: در این قسمت می توانیم تعداد ارقام بعد از اعشار را تعیین کنیم.
- Negative Number: در این حالت می توانیم مشخص کنیم که عدد منفی به همان صورت نشان داده شود یا به رنگ قرمز یا سیاه با علامت منفی نشان داده شود. یا به رنگ قرمز بدون علامت منفی نشان داده شود. (توجه داشته باشید که این گزینه فقط روی اعداد منفی عمل می کند.)
- Use 1000 Separator: اگر در کنار بگذاریم اعداد را سه رقم، سه رقم از سمت راست جدا کرده و علامت (،)این گزینه می گذارد.

3- Currency: مقادیر را همراه با سمبل پول رایج نشان می دهد. در این حالت می توان تنظیمات زیر را انجام داد:
- Symbol: نوع واحد پول را مشخص می کنید. در این قسمت واحد پول کشورهای مختلف نمایش داده شده و می توانیم واحد دلخواه خود را انتخاب کنیم.
- Negative Number: اعداد منفی چطور نمایش داده شوند.

4- Accounting: مانند حالت Currency است. این قالب بندی، قالب بندی حسابداری می باشد و علامت پولی در منتهاالهیه سمت چپ آن نوشته می شود.

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

6- Time: زمان را با قالب بندی های خاص زمان نشان می دهد. انواع قالب زمانی در این قسمت وجود دارند. ما می توانیم مدلی را انتخاب کنیم که ساعت را از 1 تا 12 با برچسب صبح و بعد از ظهر نمایش دهد و یا مدلی را انتخاب کنیم که ساعت را از 1 تا 24 نمایش دهد. و یا....

7- Percentage: عددها را همراه با علامت درصد نشان می دهد.

نکته
در تمام قالب بندی ها بجز Percentage فرقی نمی کند که ابتدا اطلاعات را در سلول وارد کنیم یا اول قالب بندی را تنظیم کنیم. ولی در حالت Percentage اگر ابتدا عدد را نوشته و سپس فرمت را Percentage کنیم، عدد را در 100 ضرب می کند. ولی اگر ابتدا فرمت را Percentage کنیم، و بعد عدد را بنویسیم، تغییری در عدد نمی دهد.

8- Fraction: مقادیر را بصورت عدد صحیح نشان می دهد که بدنبال آن نزدیک ترین کسر به مقدار واقعی ظاهر می شود. برای نوشتن یک عدد مخلوط کافی است ابتدا قسمت صحیح را نوشته سپس یک فاصله بدهیم و بعد صورت کسر را نوشته و بعد علامت (/) را بگذاریم و بعد مخرج کسر را بنویسیم. اگر عدد 6 5/10 را بنویسیم، پس از Enter کردن مقدار سلول 6 1/2 می شود. اگر بخواهیم همان مقدار اولیه باقی بماند، کافی است، در قسمت Fraction مقدار 3/10 را انتخاب کنیم. در این حالت نگاه می کند ببیند مخرج اصلی باید درچند ضرب شود تا مخرج انتخابی شود سپس صورت را هم در همان عدد ضرب می کند.

9- Scientific: مقادیر را با قالب بندی علمی نشان می دهد.

10- Text: مقادیر را به همان صورتی که وارد شده اند نشان می دهد. اگر فرمولی را به صورت متن قالب بندی کرده باشیم، Excel آن را به صورت متن نشان می دهد و مقادیر آنرا محاسبه نمی کند.
(کاردرکارگاه 7 تمرین 8 تا 13)

11- Special: مقادیر را با استفاده از قواعد قالب بندی خاص (مانند کد پستی، کد پستی به اضافه چهار رقم، یا شماره تلفن، شماره تامین اجتماعی) نشان می دهد. مثلاً اگر در یک سلول یک شماره تلفن (در حالت 10 رقمی) تایپ کنیم، با انتخاب این فرمت به صورت قالب بندی تلفن در می آید. مثلاً اگر شماره IT (3116681184) را وارد می کنیم، به فرمت تلفن در آمده (1184-668 (311)) و می فهمیم که 311 کد شهرستان، 668 کد محله و 1184 شماره تلفن IT می باشد.

12- Custom: برای ایجاد یک قالب بندی جدید از این گزینه استفاده می کنیم که خارج از بحث ما می باشد.

ب- Alignment:

توسط این Tab می توانیم جهت قرار گرفتن اطلاعات در سلول را تعیین کنیم. این Tab شامل قسمتهای زیر است:
1- Horizontal: محل قرار گرفتن افقی متن را تعیین می کند. این قسمت شامل گزینه های زیر است:
• General: هم ترازی پیش فرض
• Left: هم ترازی داده ها را به سمت چپ (استفاده برای ارقام)
• Center: هم ترازی داده ها در مرکز
• Right: هم ترازی داده ها به سمت راست (برای متون)
• Fill: تمام سلول را با متنی که در آن نوشته شده است، پر می کند.
• Justify: یک تراز مناسب برای سلول در نظر می گیرد. معمولاً برای زمانی است که اطلاعات در سلول نگنجد.
• Center Across Selection: وسط متن را در وسط سلولهای انتخابی می گذارد.

2- Vertical: محل قرار گرفتن اطلاعات را بطور عمودی تنظیم می کند. این گزینه شامل قسمتهای زیر است:
• Bottom: هم ترازی داده ها در پایین سلول
• Top: هم ترازی داده ها در بالای سلول
• Center: هم ترازی داده ها در وسط یا مرکز سلول
• Justify: داده ها در داخل سلول هم تراز می شوند. (بدین معنی که داده ها در داخل سلول بصورت مساوی جاسازی می شوند. مانند متون موجود در روزنامه ها)

3- Orientation: شامل قسمتهای زیر است:

• زاویه متن را نسبت به افق بوسیله ماوس می توان تعیین کرد.
• Degrees: زاویه متن را نسبت به افق بوسیله تایپ زاویه یا با کلید های Increase و Decrease تعیین کرد.

4- Text Control: شامل قسمتهای زیر است:

• Wrap Text: اگر اندازه متن بیشتر از سلول باشد توسط این گزینه می توان آنرا شکست. در نتیجه ارتفاع سطر افزایش می یابد.
• Shrink to Fit: اگر اندازه متن از سلول بیشتر باشد با انتخاب این گزینه متن به اندازه ای کوچک می شود که در داخل سلول بگنجد.
• Merge Cells: اگر اندازه متن از سلول بیشتر باشد می توان سلولهایی را که متن اشغال کرده را انتخاب نمود و سپس در کنار این گزینه تیک زد. این عمل باعث می شود که این سلول ها بهم پیوسته و یک سلول شوند.

ج- Font:

توسط این قسمت می توان نوع خط، اندازه، رنگ و... خطوط را تعیین نمود. این Tab شامل گزینه های زیر است:

1- Font: در این قسمت نوع خط را تعیین می کنیم.
2- Font Style: در این قسمت می توانیم تعیین کنیم که خط کج (Italic) یا تو پر(Bold) یا کج و تو پر (Bold Italic) و یا معمولی (Regular) باشد.
3- Size: در این قسمت سایز قلم را تعیین می کنیم.
4- Underline: در این قسمت می توانیم تعیین کنیم که زیر متن خط کشیده شود یا نه. و نوع خط را تعیین می کنیم.
• None: خط نمیکشد.
• Single: خط تکی زیر متن میکشد.
• Double: دو خطی زیر متن میکشد.
• Single Accounting: تک خط زیر کشیده ترین حرف کشیده می شود.(یعنی تمام حروف بالای خط نوشته می شوند.)
• Double Accounting: دو خطی زیر تمام حروف و زیر کشیده ترین حرف کشیده می شود.

5- Color: در این قسمت رنگ خطوط را متن را تعیین می کنیم.

6- Effect: شامل قسمتهای زیر است:
• Strike Through: بر روی متن خط میکشد.
• Super Script: برای بالا نویسی استفاده می شود. اگر بخواهیم A2 بنویسیم کافی است A2 را نوشته. سپس 2 را انتخاب کنیم و این گزینه را فعال کنیم.
• Sub Script: برای زیر نویسی استفاده می شود.

7- Preview: پیش نمایشی از انتخابات را نشان می دهد.
د- Border: توسط این Tab می توانیم حاشیه بندی انجام دهیم. که شامل قسمتهای زیر است:
1- Style: در این قسمت نوع خط حاشیه را مشخص می کنیم.
2- Color: در این قسمت رنگ حاشیه را مشخص می کنیم.
3- Presets: در این قسمت محل خطوط را مشخص می کنیم.
• None: حاشیه نمیگدارد.
• Outline: دور سلولها حاشیه می گذارد.
• Inside: داخل سلولهای انتخابی را حاشیه می گذارد.
4- Border: در این قسمت می توانیم تعیین کنیم که کدام قسمت از سلول حاشیه داشته باشد. نیز می توانیم تعیین کنیم که فقط سمت چپ سلول خط بیفتد و یا فقط بالا و یا پایین سلول حاشیه داشته باشد.

نکته
برای انداختن حاشیه در اکسل ابتدا رنگ و نوع خط را تعیین می کنیم و سپس بر روی Icon های مورد نیاز برای افتادن حاشیه Click می کنیم.
ه - Patterns: در این قسمت می توانیم رنگ زمینه را تعیین کنیم. که شامل گزینه های زیر است:
• Color: در این قسمت رنگ مورد نظر را انتخاب می کنیم.
• Pattern: در این قسمت می توانیم از الگوها (هاشورها) استفاده کنیم. همچنین رنگ هاشور را تعیین کنیم.

حذف قالب بندی سلولها:

همانطور که در فصل سوم گفته شد برای پاک کردن قالب بندی سلول کافی است سلول یا سلولهای مورد نظر را انتخاب کرده سپس از منوی Edit زیر منوی Clear، گزینه Formatting را انتخاب می کنیم.

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

روش اول:

1- بر روی Paste Function , Icon ()کلیک می کنیم.
2- در پنجره باز شده در قسمت Function Category نوع تابع را مشخص می کنیم. کلیه توابع در 8 دسته طبقه بندی شده اند. در دسته All همه توابع قرار دارند و در دسته Most Recently Used توابعی که به تازگی از آنها استفاده شده است قرار دارند. اگر گروه تابع را ندانیم می توانیم آنرا در All پیدا کنیم.
3- در قسمت Function name تابع را مشخص می کنیم.
4- در پنجره ای که باز می شود به دو طریق می توانیم عمل کنیم:

الف: در کادر Number 1،محدوده آدرس اعداد را وارد کنیم. برای وارد کردن محدوده آدرس کافی است ابتدا آدرس اولین خانه محدوده را وارد کرده سپس عملگر آدرس (:) را تایپ کنیم و بعد آدرس آخرین سلول محدوده را وارد کنیم. همچنین می توانیم بر روی Collapse Dialog این کادر کلیک کرده و محدوده را با Drag انتخاب کنیم.
ب: در هر کادر یک آدرس وارد کنیم. برای این کار در کادر اول آدرس اولین سلول را تایپ کرده. بر روی کادر دوم کلیک کرده،آدرس دومین سلول را تایپ می کنیم. و الی آخر.... معمولاً زمانی از این روش استفاده می کنیم که سلولها پراکنده باشند.

5- OK را می زنیم.

روش دوم:

1- منوی Insert را انتخاب می کنیم.
2- زیر منوی Function را انتخاب می کنیم.
3- بقیه مراحل مانند مرحله 2 به بعد روش اول است.

روش سوم:

1- در خانه تابع را همراه با آرگومانهای آن تایپ می کنیم. (توابع و آرگومانهایش در قسمت بعد توضیح داده شده اند.)
2- اگر قبل از آن = گذاشته باشیم که Enter می کنیم در غیر این صورت بر روی = در نوار فرمول Click می کنیم.

روش چهارم:

1- در یک خانه (خانه ای که می خواهیم حاصل نوشته شود) علامت = را تایپ می کنیم یا علامت = در خط فرمول را می زنیم.
2- در کادر اول نوار فرمول (Name Box) توابع ظاهر می شوند، کافی است بر روی drop down آن Click کنیم تا همه توابع را ببینیم. تابع مورد نظر را انتخاب می کنیم.
3- بقیه مراحل مانند مرحله 4 به بعد روش اول است.

چند تابع معروف

1- تابع مجموع (SUM): برای فعال کردن این تابع یا آنرا تایپ کرده یا به یکی از روشهای گفته شده در بالا عمل می کنیم. در پنجره Function Category گروه Math & Trig را انتخاب می کنیم و در پنجره Function name،SUM را انتخاب می کنیم فرمت کلی این دستور به شکل زیر است: (از این دو فرمت زمانی که بخواهیم فرمول را تایپکنیم استفاده می کنیم.)

1- SUM -number 1: number 2
2- SUM -number 1;number2;..

نکته
معمولاً برای زمانی که اطلاعات در سلولهای مجاور باشند از فرمول اول و اگر اطلاعات در سلولهای غیر مجاور و پراکنده باشند از فرمول دوم استفاده می کنیم.

2- تابع میانگین(Average): از این تابع برای محاسبه میانگین استفاده می شود.این تابع جزء گروه Statistical است. شکل کلی آن بصورت زیر است:
1- Average - number 1 ; number 2 ;...
2- Average - number 1: number 2

3- تابع ماکزیمم (Max) در اکسل: ‌از این تابع برای پیدا کردن ماکزیمم چند مقدار استفاده می کنیم: این تابع نیز جزء گروه Statistical است. و شکل کلی آن به صورت زیر است:
1- Max - number 1 ; number 2 ; ….
2- Max - number 1: number 2

4- تابع مینیمم (Min): از این تابع برای محاسبه میانگین اعداد استفاده می شود و جز توابع گروه Statistical می باشد. شکل کلی آن به صورت زیر است:
1- Min - number 1 ; number 2 ; ….

2- Min - number 1: number 2

5- تابع شمارنده (Count): از این تابع برای شمارش تعداد خانه های پر شامل اعداد استفاده می شود. این تابع نیز جز توابع گروه Statistical می باشد و فرم کلی آن به صورت زیر است:
1- Count - value 1 ; value 2 ; ….
2- Count - value 1: value 2

6- تابع Round: از این تابع برای گرد کردن اعداد استفاده می شود و جز توابع گروه Math & Trig می باشد و شکل کلی آن به صورت زیر است:
Round - number 1 ; num_digits
قسمت Number برای معرفی عدد و قسمت Num_digits معرف تعداد ارقامی است که باید گرد شوند،‌ Excel هنگام گرد کردن نگاه به اولین رقم بعد از رقم حذفی می کند اگر کوچکتر از 5 بود که خود اعداد را می نویسد ولی اگر بزرگتر از 5 بود یک رقم به آخرین رقمی که میماند اضافه می کند.

Auto sum در اکسل:

توسط این دستور می توانیم تعدادی خانه را با هم جمع کنیم در این حالت کافی است در خانه ای Click کنیم، Excel خانه های در محدوده خانه انتخابی ما را در نظر می گیرد و مجموع آنها را حساب می کند و خانه های انتخابی را به ما نشان می دهد. اگر این خانه ها مورد نظر ما نبودند، خودمان محدوده را انتخاب می کنیم. برای فعال کردن این دستور کافی است آیکون Auto sum () را از نوار ابزار Standard انتخاب کنیم.

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

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

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

گام اول: با اطلاعات زیر شروع می کنم.

ترکیب چارت های مختلف در اکسل

در اینجا می خواهیم چارتی داشته باشیم که ترکیبی از ستون (Column) و خط (Line) باشد. پس با یک نمودار معمولی ستونی شروع می کنیم (ضمن اینکه هیچ تفاوتی ندارد که با کدام نوع چارت شروع کنیم).

گام دوم: داده های گام اول را انتخاب کنید. از زبانه Insert در ریبون بالا insert a column chart را انتخاب نمایید.

حالا یک نمودار ستونی با دو سری داریم. که هر دو با یک محور عمودی (Axis) نمایش داده شده است. تمام داده های ما همین است اما این چارت برای ما اطلاعات خیلی معناداری را نشان نمی دهد. زیرا مقیاس در گردش مالی (Transactions) و مقدار فروش (Units Sold) متفاوت است. گام بعدی تغییر نوع چارت گردش مالی (Total Transactions) از ستونی به خطی است.

گام 3: یکی از سری ها را انتخاب نمایید. که در اینجا ما سری (Total Transactions) را انتخاب می کنیم. شما از راههای متعدد موجود می توانید یکی را برای انتخاب سری مورد نظر انتخاب کنید.

گزینه 1: بر روی سریها روی چارت کلیک نمایید. (لطفا روی legend کلیک نکنید. روی ستونهای قرمز روی چارت کلیک کنید).

گزینه 2: بر روی ریبون روی زبانه Chart Layout کلیک نمایید. (دقت نمایید که این زبانه فقط در هنگام انتخاب چارت فعال می شود و در بقیه موارد قابل رویت نیست. پس اگر این زبانه را نمی بینید روی چارت دوباره کلیک کنید). در قسمت انتهایی چپ ریبون Current Selection را می بینید. که در آن یک منو dropdown به نام  Chart Area وجود دارد.                                                                                                           

از داخل این منو سریی که می خواهید در آن تغییر ایجاد کنید انتخاب نمایید. (در اینجا (Total Transactions.

گام 4: نوع چارت برای سری انتخاب شده را تغییر دهید. برای این کار ابتدا بر روی ریبون به زبانه Design بروید. سپس در قسمت انتهایی سمت چپ روی Change Chart Type کلیک نمایید.

این کار باعث رویت پنجره Change Chart Type خواهد شد. در این حالت نوع چارت دلخواه را انتخاب نمایید مثلات چارت خطی را انتخاب می کنیم.

اکنون چارتی داریم با دو نوع نمودار! می توانیم هر تعداد که بخواهیم نمودارهای مختلف را در این چارت با هم ترکیب کنیم.

اضافه نمودن یک محور دیگر (Secondary Axis) در نمودار در اکسل 

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

گام 5: در این مرحله سری داده هایی که می خواهید در محور دوم قرار گیرد را انتخاب نمایید. در اینجا ما سری Total Transactions را انتخاب می کنیم. این کار را مانند گام 3 انجام دهید.

گام 6: دوباره به زبانه Layout بروید. و در Current Selection گزینه Format Selection را انتخاب نمایید.

گام 7: با کلیک Format Selection پنجره زیر باز خواهد شد. در این پنجره Primary Axis انتخاب شده است. شما Secondary Axis را انتخاب کنید و پنجره را ببندید.

فوق العاده ست! با موفقیت توانستید محور دوم را در چارت ایجاد کنید!

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

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

گام 8: در قسمت Layout روی ریبون، بر Show Legend at Bottom روی قسمت Legend کلیک کنید.

گام 9: محور عمودی را تغییر دهید تا علامت دلار را نمایش دهد. برای این کار بر روی لیبل محور عمودی کلیک راست کنید و Format Axis را انتخاب کنید.

سپس زبانه Number را در قسمت چپ انتخاب کنید. در لیست Category گزینه Currency را انتخاب نمایید. اگر نمی خواهید که اعداد اعشاری وجود داشته باشند، دو صفر اضافی و اعشار را حذف نمایید. سپس Add را بزنید و پنجره را ببندید.

گام 10: اضافه کردن عنوان برای محورها. دوباره به زبانه Layout بروید و Axis Titles را انتخاب کنید. از لیست dropdown، Primary Vertical Axis را انتخاب نمایید. هر کدام را می خواهید انتخاب کنید. من Rotated Title را انتخاب کردم.

گام 11: به چارتمان یک عنوان هم اضافه می کنیم. بازهم گزینه Layout را در ریبون انتخاب می کنیم. سپس بر روی گزینه Chart Title کلیک می نماییم. هر عنوانی که می خواهید انتخاب کنید. نتیجه نهایی کار بدست آمد!

با تکرار مراحل بالا و سری های بیشتر شما می توانید حتی چارتهای بسیار پیچیده تر بسازید

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

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

مرحله اول:

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

مرحله دوم:

ابتدا سلولی که میخواهید منوی کشویی شود را انتخاب و از سربرگ Data  گزینه Data validation  را انتخاب و Data Validation… را اجرا نمایید.

مرحله سوم:

در پنجره باز شده منوی کشویی Allow را بر روی List  قرار داده و از قسمت Source  بر روی روی Icon  انتخاب سلول  کلیک نمایید.

مرحله چهارم:

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

مشاهده می کنید که در منوی Source سلول های انتخابی شما قرار دارد. حال بر روی OK کلیک نمایید.

لیست کشویی شما ساخته شد. و با هر تغییری در متن سلول های اصلی، متن سلول های کشویی شما نیز تغییر می کند.

1. شش مدل نمودار جدید

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

نمودار آبشاری:

نمودار هیستوگرام:

نمودار پارتو:

نمودار Box & Whisker:

نمودار Treemap:

نمودار Sunbrust:

2. قابلیت پیشرفته استفاده از Query

در اکسل 2016 Addon قدرتمند Power Query به هسته اصلی نرم افزار اکسل اضافه شده و به کاربر امکان اتصال به دیتابیس های مختلف و ورود اطلاعات از انواع مختلف دیتابیس ها مثل MySQL را می دهد.

3. افزایش امکانات پیش بینی

در اکسل 2016 برخلاف نسخه های قبلی اکسل که تنها قابلیت پیش بینی خط را داشتند، امکان پیش بینی نمایی و … نیز وجود دارد و توایعی برای این منطور به کتابخانه توابع اکسل اضافه شده است. همینطور ابزار one-click forcasting جهت نمایش پیش بینی داده ها به اکسل اضافه شد ه است. در این ابزار امکان تعیین پارامترهای پیش بینی مثل تغییرات فصلی، دامنه اطمینان و … هم وجود دارد.

4.نقشه سه بعدی

یکی از Addon های قدرتمند اکسل در نسخه های قبلی این نرم افزار PowerMap بود که امکان نمایش داده ها رو به صورت جغرافیایی داشت. حالا در نسخه جدید اکسل این Addon که نام اون به 3D Map تغییر کرده جزئی از هسته اصلی نرم افزار اکسل شده و به صورت پیش فرض در اختیار کاربر قرار گرفته است.

5. بهبود در ابزار Pivot Table

یکی از پر کاربردترین ابزارهای اکسل ابزار Pivot Table هست. این ابزار هم در نسخه جدید اکسل دستخوش تغییرات شده و قابلیت های آن  افزایش پیدا کرده. چند مورد از این تغییرات به شرح زیر هستند:

• شناسایی و گروه بندی خودکار فیلدهای تاریخ
• امکان جستجو در فیلدها
• اضافه شدن دکمه هایی جهت نمایش جزئیات بیشتر در PivotChart
• شناسایی خودکار رواط بین جداول مختلف موجود در Pivot Table

6. امکان ایجاد slicer با قابلیت انتخاب همزمان چند آیتم در excel

ابزار slicer یکی از ابزارهای مفید اکسل برای فیلتر سریع داده هاست. در نسخه های قبلی اکسل تنها امکان فیلتر کردن یکی از گزینه های slicer وجود داشت. با ارائه نسخه جدید اکسل، انتخاب همزمان چند گزینه در slicer امکان پذیر است

7. جستجوی سریع گزینه های نرم افزار با استفاده از قابلیت Tell me

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

به عنوان مثال با وارد کردن عبارت waterfall پزینه های مربوط به رسم نمودار آبشاری نمایش داده می شود:

8. جستجوی اطلاعات درباره موارد دلخواه در اینترنت

با استفاده از قابلیت جدید smart lookup میتونید اطلاعات مختلفی از طریق اینترنت درباره کلمات و عبارات موجود درفایل بدست بیارید.

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

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

10. بهبود اشتراک گذاری فایل

در اکسل 2016 در قسمت بالا و سمت راست نرم افزار اکسل دکمه share قرار گرفته که به کاربر اجازه می دهد  تا به راحتی اطلاعاتش را به اشتراک بگذارید.

11. امکان دسترسی به تغییرات قبلی فایل ها

در نسخه جدید اکسل در صورتی که فایل های را  در Sharepoint یا اکانت تجاری OneDrive ذخیره کنید این امکان وجود دارد که سابقه تغییرات هر فایل رامشاهده کنید و در صورت نیاز نسخه های قدیمی تر رو بازگردانی کنید.

12. تم های جدید

در نسخه جدید اکسل سه تم به نام های Colorful، Dark Gray و White وجود داره که می توانید بسته به سلیقه خود از آنها استفاده کنید.

13. ارسال اطلاعات به Power BIدر اکسل

در صورتی که از Power BI استفاده می کنید، می توانید با استفاده از گزینه اضافه شده به Backstage اکسل، فایل خود را    به راحتی در Power BI به اشتراک بگذارید.

ویژگی ردیابی تغییرات (Track Changes) در اکسل

وقتی ویژگی ردیابی تغییرات (Track Changes) در اکسل را فعال می کنید، هر سلولی را که ویرایش کنید، هایلایت می شود و حاشیه ای در اطراف آن ظاهر می شود. در این وضعیت انتخاب هر سلول باعث می شود تا تاریخچه تغییرات آن را بتوانید مشاهده کنید. این ویژگی شما و سایر کسانی را که بر روی فایل اکسل کار می کنند را قادر می سازد تا قبل از اینکه سلولی را ویرایش کنند، بتوانند مروری بر روی تغییرات آن داشته باشند، و حتی ممکن است این مرور منجر به تجدید نظر شما بگردد.                                                                                                               

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

اگر برگه اکسل شما دارای جدول باشد نمی توانید از ویژگی ردیابی تغییرات استفاده کنید. برای حذف جدول آن را انتخاب کنید، و سپس از تب Design دستور Convert to Range را انتخاب کنید.

روش فعال کردن ویژگی ردیابی تغییرات (Track Changes) در اکسل

➊ در تب Review دستور Track Changes را کلیک کنید، سپس از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➋ پنجره Highlight Changes نمایان می شود. گزینه Track changes while editing را تیک بزنید. گزینه Highlight changes on screen را هم بررسی کنید، اگر تیک نداشت آن را هم حتما تیک بزنید. سپس بر روی OK کلیک کنید.



➌ اگر اکسل پیغام زیر را دارد OK کنید.



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

➎ یکی از سلولهای مورد ویرایش قرار گرفته را انتخاب کنید تا بتوانید خلاصه ای از تغییرات انجام شده روی آن را ببینید. در مثال زیر، ما مقدار سلول D14 را تغییر داده ایم.

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

روش فهرست کردن تغییرات در یک برگه اکسل جداگانه

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

➊ برگه اکسل خود را ذخیره کنید.

➋ در تب Review، بر روی دستور Track Changes کلیک کنید و از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➌ صفحه Highlight Changes نمایان می شود. گزینه List changes on a new sheet را تیک بزنید و سپس ok کنید.



➍ تغییرات اعمال شده در یک برگه که نامش History می باشد فهرست می گردند.

برای حذف تاریخچه از فایل اکسل شما می توانید فایل اکسل را مجددا ذخیره کنید و یا گزینه List changes on a new sheet را که در بالا تیک زدید، اینبار بدون تیک کنید.

مرور تغییرات در اکسل

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

روش مرور ردیابی تغییرات در اکسل

➊ در تب Review دستور Track Changes را کلیک کنید، سپس از کادر باز شده گزینه Accept/Reject Changes (پذیرش یا رد تغییرات) را انتخاب کنید.



➋ اگر به شما هشدار ذخیره سازی داده شد، فایل اکسل خود را ذخیره کنید.

➌ یک صفحه ظاهر می شود. اول مطمئن شوید که گزینه When تیک خورده باشد و در کادر مقابل آن گزینه Not yet reviewed انتخاب شده باشد. حالا OK کنید.



➍ یک صفحه ظاهر می شود. به ازاء هر تغییری که در فایل اکسل داده شده است شما می توانید پذیرش (Accept) و یا رد کردن (Reject) را انتخاب کنید. اکسل بصورت اتوماتیک تک تک تغییرات را به شما نشان می دهد تا شما همه را بررسی کنید.



➎ حتی بعد از مرور تغییرات و تایید یا رد آنها، همچنان ردیابی تغییرات در برگه شما نمایان می باشد. برای اینکه آنها را نبینید می توانید ویژگی ردیابی تغییرات را غیر فعال کنید. در تب Review دستور Track Changes را کلیک کنید و از کادر باز شده گزینه Highlight Changes را انتخاب کنید.



➏ یک پنجره باز می شود. گزینه Track changes while editing را بدون تیک کنید و ok کنید.



➐ دکمه Yes را کلیک کنید تا عملیات عیر فعال کردن ردیابی تغییرات صورت پذیرد و همینطور فایل اکسل شما از حالت اشتراک گذاری خارج شود.

برای اینکه تمامی تغییرات انجام شده را بصورت یکجا تایید و یا رد کنید، گزینه های Accept All برای پذیرش کلی و همینطور Reject All برای رد کردن کلی می توانند به شما کمک کنند.

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

نقش اکسل در رد یابی تغییرات وثبت توضیحات

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

افزودن توضیحات در اکسل

➊ سلولی را که قصد درج توضیحات برای آن را دارید، انتخاب کنید. در اینجا ما سلول D17 را انتخاب می کنیم.



➋ در تب Review دستور New Comment را کلیک کنید.



➌ یک کادر برای وارد کردن توضیحات نمایان می شود. توضیحاتتان را تایپ کنید، سپس در هر جایی به غیر از داخل کادر توضیحات کلیک کنید تا کادر توضیحات بسته شود.



➍ توضیحاتی که تایپ کرده اید به سلول اضافه می شود. وقتی یک سلول دارای توضیحات باشد در سمت راست و بالای آن یک مثلث قرمز کوچک نمایان می شود.



➎ اگر سلول را دوباره انتخاب کنید توضیحات را خواهید دید.

ویرایش توضیحات در اکسل

➊ ابتدا سلول دارای توضیحات را انتخاب کنید.

➋ در تب Review بر روی دستور Edit Comment کلیک کنید.



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

نمایش یا مخفی کردن توضیحات در اکسل

➊ ابتدا تب Review و سپس دستور Show All Comments را انتخاب کنید. اجرای دستور Show All Comments باعث می شود تا همه توضیحات نمایان شوند.



➋ برای مخفی کردن توضیحات مجددا بر روی همان دستور Show All Comments کلیک کنید تا همه توضیحات مخفی شوند.

همچنین اگر هر سلول را انتخاب کنید دستور Show/Hide Comment توضیحات مربوط به آن را نمایان یا مخفی می کند.

 حذف توضیحات درEXCEL

➊ ابتدا سلولی که دارای توضیحات باشد و قصد حذف توضیحاتش را دارید انتخاب نمایید. در اینجا ما سلول E13 را انتخاب می کنیم.


➋ حالا ابتدا تب Review و سپس دستور Delete را در گروه Comments کلیک کنید.



➌ توضیحات مربوطه حذف می گردد.

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

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

=CONCATENATE(

، بعد ازخواندن تابع وقت آن رسیده که ورودی های آن را مشخص کنیم. قصد داریم ابتدا نام (ستون B) و سپس نام خانوادگی (ستون A) درج شوند. پس دو ورودی تابع برای این سلول به ترتیب B2 و A2 خواهند بود.

برای شناسایی این سلول ها به تابع دو راه خواهید داشت. اول اینکه آدرس دو سلول را تایپ کرده و با علامت کاما (,) از یکدیگر جدا کنید. یعنی در آخر، تابع نوشته شده در سلول بصورت زیر خواهد بود:

=CONCATENATE(B2,A2)

 همچنین می توانید بدون نیاز به تایپ و تنها با کلیک روی سلول های موردنظر خود آنها را انتخاب کنید. بنابراین در این مثال ما ابتدا پس از نوشتن نام تابع و علامت پرانتز باز، به ترتیب بر روی ستون های B2 و A2 کلیک کرده ایم. بعد از اضافه کردن سلول های خود، دکمه ی Enter را فشار دهید.

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

برای درج فاصله می توانید بدین صورت عمل کنید که ابتدا آدرس سلول اول،یک کاما، سپس عبارت " "، یک کاما و سپس آدرس سلول دوم را تایپ کنید. یعنی دستور تابع بصورت زیر خواهد بود:

=CONCATENATE(B2," ",A2)

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

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

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

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

توجه داشته باشید که می توانید همچنین از عملگر & برای ترکیب کلمات سلول ها استفاده کنید. یعنی از دستور =B2&" "&A2 به جای =CONCATENATE(B2,” “,A2) استفاده کنید.

  در  اکسل جهت طراحی خطوط یک جدول راهکار های متعددی موجود می باشید

باما همراه باشید تا با دو روش از آن آشنا شویم

روش اول

1- جدول مورد نظر رو انتخاب کنید
2- روی منوی Home کلیک کنید
3- در قسمت Font ابزار Border رو پیدا کنید
4- روی فلش کنار ابزار کلیک کنین
5- روی فلش کنار گزینه‌ی Line Style کلیک کنید
6- خط نقطه چین مورد نظر رو انتخاب کنید
7- دوباره روی فلش کنار ابزار کلیک کنید
8- گزینه‌ای که همه‌ی خطوط جدول رو مشخص می‌کنه (All Borders) رو انتخاب کنید
9- دوباره روی فلش کنار ابزار کلیک کنید
10- روی فلش کنار گزینه‌ی Line Style کلیک کنید
11- خط دوتایی رو انتخاب کنید
12- دوباره روی فلش کنار ابزار کلیک کنید
13- گزینه‌ای که خط دور جدول رو بدن خطوط داخلی رامشخص می‌کند (Outside Borders) را انتخاب کنید

روش دوم

1- جدول مورد نظر رو انتخاب کنید
2- روی منوی Home کلیک کنید
3- در قسمت Font ابزار Border را پیدا کنید
4- روی فلش کنار ابزار کلیک کنید
5- گزینه‌ی More Borders را انتخاب کنید
6- در قسمت Line و Style خطوط دوتایی رااتخاب کنید
7- از قسمت Presets گزینه‌ی Outline را انتخاب کنید
8- حالا از قسمت Line و Style خطوط نقطه چین رو انتخاب کنید
9- از قسمت Presets گزینه‌ی Inside را اتنخاب کنید
10- OK کنین و تغییرات را ببینید

چگونگی تغییر  خطوط جدول در اکسل

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

جدول را انتخاب کنید  

نقطه درج را در جدول قرار دهید تا جدول برای ویرایش انتخاب شود.

 Table Properties را باز کنید

روی جدول کلیک راست کنید و گزینه ‏Table Properties را انتخاب کنید تا کادر محاوره‌ای Table Properties باز شود.

 اندازه خطوط جدول را مشخص کنید  

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

 انتخاب رنگ در excel

از منوی Color، یک رنگ برای خطوط جدول انتخاب کنید. برای ایجاد خطوط دو رنگی، از منوهای Light Border و Dark Border استفاده کنید. بعد از اتمام کار، روی OK کلیک کنید بخاطر داشته باشید که اگر از یک زمینه برای صفحه استفاده کنید، این رنگ‌ها بصورت خودکار تنظیم می‌شوند.

 خطوط سلول را تغییر دهید  

همچنین می‌‌توانید خطوط تک تک سلول‌ها را تغییر دهید. برای این کار، داخل سلول مورد نظر کلیک کنید.

 Cell Properties را انتخاب کنید  

روی سلول انتخاب شده کلیک راست کنید و Cell Properties را انتخاب کنید. کادر محاوره‌ای Cell Properties که شبیه کادر محاوره‌ای Table Properties در مرحله 3 است باز می‌شود.

 تغییر دادن خطوط جدول در اکسل

در قسمت Borders، منوهای رنگ، درست مانند منوهای رنگ در مرحله 4 کار می‌کنند. برای اینکه خطوط، فقط یک رنگ داشته باشند یکی از رنگ‌های منوی Color را انتخاب کنید. و برای اینکه خطوط، دو رنگی شوند، رنگ‌های منوهای Light Border و Dark Border را انتخاب کنید. بعد روی OK کلیک کنید.

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

با ما همراه باشید تا با تعدای از آنها آشنا شویم

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

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

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

مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

«فرمت سفارشی اعداد» یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود و مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون با شیوهی نمایش اعداد در اکسل افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.

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

مفاهیم پایه‌ای فرمت سفارشی اعداد در اکسل

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

همان‌طور که نشان داده‌شده است، تعداد اعشار صفر، استفاده از کاما برای جداکننده هزارگان و همچنین استفاده از پرانتز برای نمایش اعداد منفی استفاده شده است.

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

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

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

از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و …

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

#,##0,

درصورتی‌که بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

«فرمت سفارشی اعداد» یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود و مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون با شیوهی نمایش اعداد در اکسل افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.

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

یه مدت پیش دنبال یک مطلب می‌گشتم که اتفاقی به کتاب Excel® Dashboards and Reports, 2nd Edition از نویسنده پرآوازه اکسل John Walkenbach و Mike Alexander برخورد کردم که حیفم اومد اون رو با شما در میان نگذارم.

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

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

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

1. بر روی محدوده مورد نظرتون که می‌خواهید فرمت اعداد اون رو عوض کنید راست کلیک کنید و آیتم Format Cells رو انتخاب کنید تا پنجره Format Cells باز شود.
2. بر روی زبانه یا تب Number بروید و از لیستی که در پایین نشان داده‌شده گزینه Number رو انتخاب کنید (البته ناگفته نماند این روشی که ارائه می‌شود محدود به انتخاب Number نمی‌شه و هر گزینه‌ای که در لیست قرار داره رو می‌شه انتخاب کرد و سپس به مراحل بعدی رفت).

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

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

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

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

 مشخص است که دستور زبان فرمت انتخاب شده قبل به‌صورت

#,##0_);(#,##0)

می باشد. از این فرمت نترسید چند لحظه آینده همه آن‌ها را خودتان اصلاح خواهید نمود.

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

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

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

به‌راحتی می‌توان فرمت ارائه‌شده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:

+#,##0;-#,##0

در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده می‌شود.

+1,200

-15,000,000

به‌راحتی می‌توان این فرمت را برای نشان دادن اعداد به‌صورت درصد اصلاح کرد. فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:

+0%;-0%

این دستورالعمل نوشتاری، اعداد را به‌صورت زیر نمایش می‌دهد:

+43%

-54%

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

0%_);(0%)

با این تغییرات اعداد قبلی به این صورت نمایش داده می‌شود:

 45%

(54%)

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

خلاصه کردن اعداد بزرگ از طریق گرد کردن به ضرایب هزار، میلیون و … در اکسل

در قسمت قبل برای نمای اعداد بزرگ از جداکننده هزارگان استفاده شد. برای نمایش واضح‌تر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … می‌توان صرفه نظر کرد. برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید. در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):

#,##0,

درصورتی‌که بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:

#,##0,,

برای درک بهتر مطلب به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:

گرد کردن اعداد به صورت ضریبی از میلیون در اکسل

قبل و بعد از گرد کردن اعداد به صورت ضریبی از میلیارد

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

نکته حائز اهمیت این است که اکسل تمام ارقام حذف‌شده را در محاسباتش در نظر می‌گیرد و فقط برای نمایش خروجی، ارقام را بر اساس فرمت در نظر گرفته‌شده اصلاح می‌کند.

خیلی از مواقع دیدم که دوستان برای این کار (گرد کردن اعداد به ضرایب هزارگان) اعداد را بر 1,000 و یا 1,000,000 و … تقسیم می‌کند و این در حالی است که در هرجایی که بخواهند از این اعداد برای محاسباتشان استفاده کنند باید دوباره آن اعداد را به فرمت اولیه بازگردانند و به‌عبارت‌دیگر باید در هرجایی یکپارچگی محاسباتشان در اکسل را حفظ نمایند که می‌تواند بسیار وقت‌گیر و در بعضی مواقع باعث بروز اشتباه در محاسباتشان گردد.

در صورت نیاز می‌توان گرد کردن اعداد به ضرایب هزار، میلیون و … را با اضافه کردن یک حرف یا کلمه به انتهای فرمت مشخص‌تر نمود:

#,##0, “k”

با این کار اعداد بدین‌صورت نمایش داده می‌شود

188k

318k

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

#,##0, "k";(#,##0, "k")

بعد از اعمال این فرمت اعداد مثبت و منفی بدین‌صورت نمایش داده می‌شود:

(318k)

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

#,##0.00,, “m”

با ترفند قبلی و با اضافه کردن دو رقم اعشار، وقتی‌که اعداد به ضریب میلیون گرد می‌شوند، می توان با نمایش دو رقم اعشار دقت اعداد نمایش داده‌شده را بالا برد.

به‌عنوان‌مثال اگر که هزینه، درآمد و سود دیسیپلین سیویل به صورت ضریب میلیارد (نه رقم) گرد شده و برای بالا بردن دقت از دو رقم اعشار استفاده شده است: