ترکیب دو تابع INDEX و MATCH در اکسل

                                                                                                                                       تابع INDEX و تابع MATCH دو تا از توابع بسیار قوی در اکسل هستند که ترکیب و استفاده مناسب از آنها میتواند  در نوشتن فرمول‌های بهتر کمک زیادی کند. در تابع INDEX یک محدوده را مشخص میکنیم و با دادن شماره سطر و ستون دلخواه یک سلول را برمیگردانیم.  اگر به جای تعیین دستی مقدار سطر و ستون، اینکار را با استفاده از نتیجه تابع MATCH انجام دهیم میتوانیم کارهای زیادی در اکسل انجام دهیم و خیلی از مشکلات فرمول نویسی  را به راحتی حل کنیم. با استفاده از ترکیب دو تابع INDEX و MATCH در اکسل میتوانیم شماره سطر یا ستون مقداری را جستجو کنیم و مقدار متناظرش را در یک سطر یا ستون دیگر  برگردانیم.                                                                                                                                              جدولی را درنظر بگیرید                                                                                                              اگربخواهیم در آن جدول بر اساس جستجوی کد محصول 1398، نام محصول را بدست بیاوریم میتوانیم از تابع VLOOKUPبه شکل (VLOOKUP(1398;A:B;2;FALSE استفاده کنیم.                  

در این حالت دیگر تابع VLOOKUP کارایی ندارد و نمیتوانیم با استفاده از این تابع بر اساس جستجوی کد محصول، نام محصول را بدست بیاوریم  زیرا  نمیشود پارامتر سوم تابع VLOOKUP را -1 قرار داد و به عقب حرکت کرد.در اسیمن موارد  میتوانیم با استفاده از ترکیب دو تابع INDEX و MATCH  این مشکل را حل کنیم.

1. ابتدا تابع INDEX را مینویسیم و پارامتر اول آن را A:A قرار میدهیم،زیران میخواهیم نام محصول رامشخص کنیم و نام محصول هم در ستون A قرار دارد.
2. در حالت عادی میتوانستیم شماره سطر رابه تابع INDEX بدهیم. شماره سطر عدد 1398 برابر 4 است اما ما نمیخواهیم این شماره سطر را دستی وارد کنیم. بلکه میخواهیم شماره سطر به صورت خودکار توسط اکسل پیدا  شود. اینکار رابا تابع MATCH انجام میدهیم. تابع (MATCH(1398;B:B;0 دقیقاً اینکار را انجام میدهد.
3. در نهایت تابع بخش 2 را در تابع INDEX  قرار میدهیم تا تابع به شکل زیر کامل شود.

INDEX(A:A;MATCH(1398;B:B;0))

میتوانیم به جای عدد 1398 یک سلول یا هر کد دیگری را قرار دهیم و تابع بالا نام محصول متناظر با آن کد را برای ما پیدا میکند.

                         

ترسیم نمودار پارتو در اکسل

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

در اولین گام باید اطلاعات موجود در جدول برحسب میزان توقف به صورت نزولی مرتب شوند. برای اینکار ستون میزان توقف را انتخاب میکنیم و از تب HOME گزینه sort & filter را انتخاب میکنیم و گزینه sort largest to smallest راکلیک میکنیم.

در پنجره ای که باز میشود گزینه Expand the selection را انتخاب میکنیم تا نام عوامل هم مطابق با میزان توقف مرتب شوند.

بعد از آن یک ستون تجمعی درست میکنیم که برای هر پارامتر عامل توقف میزان توقف را به صورت تجمعینشان می دهیم. این میزان توقف تجمعی برای بیشترین عامل توقف برابر زمان توقف  و برای کم اهمیت ترین عامل بروز توقف برابر جمع مجموع تمام توقفها خواهد بود. برای اینکار در مقدار سلول C2 رو  B2=  قرار میدهیم و مقدار سلول C3 رو  C2+B3=  قرار میدهیم.و فرمول سلول C3 را در سلول های C4 تا C6 کپی میکنیم.

اکنون  باید یک ستون برای محاسبه درصد تجمعی عوامل بروز توقف ایجاد کنیم. برای اینکار باید توقف تجمعی هر عامل رو بر توقف کل تقسیم کنیم پس مقدار سلول D2 رو  C2/$C$6=  قرار میدهیم. برای اینکه هنگام کپی کردن فرمول، سلول C6 در مخرج کسر ثابت بماند از مقداردهی مطلق استفاده میکنیم. مقدار سلول D2 را در سلول های D3 تا D6 کپی میکنیم.

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

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

یکی از مستطیل های آبی رنگ مربوط به میزان توقف را با ماوس انتخاب میکنیم و بعد راست کلیک میکنیم و گزینه Change Series Chart Type را کلیک میکنیم. در پنجره ای که باز میشود برای سری درصد تجمعی تیک Secondary Axis را فعال میکنیم و Chart Type را هم به گزینه Line with Markers تغییر میدهیم.  تغییرات را ok میکنیم.

برای اینکه نمودار ظاهر زیباتری پیدا کند. محور سمت راست نمودار را باید به صفر تا صد تغییردهیم. برای اینکار محور سمت راست را انتخاب کرده و راست کلیک میکنیم. در منوی باز شده روی گزینه Format Axis کلیک میکنیم و در قسمت Axis Options گزینه Maximum را از 1/2 به 1 تغییر میدهیم.

 

تفاوت فرمت XLSB و XLSM در اکسل

                                                                                                                                          فرمت پیش فرض نرم افزار اکسل برای ذخیره اطلاعاتXLSX است و در صورتی که فایل حاوی کد VBA یا ماکرو باشد برای ذخیره آن معمولاً از فرمت XLSM استفاده میشود. در اکسل یک فرمت دیگر هم برای ذخیره فایل های حاوی ماکرو وجود دارد که کمتر از آن استفاده میشود وآن هم فرمت XLSBاست.

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

مزایای فرمت XLSB به نسبت XLSM:

• سرعت بیشتر در اجرای فایل توسط نرم افزار اکسل
• حجم کمتر در مقایسه با فرمت XLSM

معایب فرمت XLSB به نسبت XLSM:

• عدم سازگاری با سایر نرم افزارهای آفیس مثل Open Office و غیره

افزایش سرعت و کاهش حجم فایل های اکسل

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

1. استفاده بیش از حد از توابع Volatile: توابع Volatile توابعی هستند که با هر بار محاسبه مجدد ورکبوک اونها هم مجدداً محاسبه میشوند. برخی از این توابع شامل RAND، RANDBETWEEN ،NOW ،TODAY ،OFFSET ،CELL ،INDIRECT میشوند. حالا اگر  از این توابع در فایل زیاد استفاده کرده باشید احتمالاً میتوانید حدس بزنید با هر بار تغییر دادن مقدار یک سلول چه اتفاقی می افتد. بنابراین بهتراست تا حد امکان از این توابع زیاد استفاده نشود و از توابع دیگر مثل Index، Sumif و … استفاده شود
2. استفاده زیاد از Conditional formatting: فرمول های موجود در Conditional formatting ها هم با هر بار محاسبه ورکشیت دوباره محاسبه میشه. بنابراین باید در استفاده از اونها هم دقت زیادی کرد.
3. وجود سلول های بدون استفاده در شیت ها: گاهی سلول هایی که توسط اکسل به عنوان سلول های فعال یک شیت لحاظ میشود با سلول هایی که در آن شیت فعال هستند متفاوت است. در
   برای اینکه بفمیم اکسل کدام سلول را به عنوان آخرین سلول فعال شیت در نظر گرفته است میتوانیم از کلید ترکیبی ctrl+end استفاده کنیم. اگر سلولی که اکسل به عنوان سلول آخر در نظر گرفته است اشتباه باشد باعث میشودحجم فایل بدون دلیل بالا برود. برای  حل این مشکل باید سطرها و ستون های اضافه را انتخاب و پاک کنید. بعد فایل را ذخیره کنید. این مساله بخصوص در مورد افزایش حجم فایل ها خیلی تاثیر گذار است
4. لینک کردن به سایر ورکبوک ها: لینک کردن یک ورکبوک به ورکبوک های دیگر باعث کاهش سرعت میشود. بهتراست تا جایی که امکان دارد اطلاعات را در یک ورکبوک قرار دهید یا تا جایی که میشود به ورکبوک های خارجی کمتری لینک کنید.
5. وجود شیت های بیش از حد: بهتراست تا حد ممکن ورکبوک هایی که میسازیم تعداد شیت های کمتری داشته باشد. این مساله هم در افزایش سرعت فایل اکسل بی تاثیر نیست.

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

Data Table اکسل ابزاری مفید برای تحلیل متغیرها

Data Tableاکسل یکی از ابزارهای تحلیل حساسیت در اکسل به شمار میرود

با استفاده ابزار Data Table اکسل میتوانیم تاثیر تغییرات یک یا دو متغیر را روی متغیر وابسته مورد نظرمان را بررسی کنیم.

 

تحلیل تک متغیره در Data Table اکسل:

از این حالت زمانی استفاده میکنیم که بخواهیم نتیجه تغییرات صرفاً یک متغیر را در فرمول مشاهده کنیم. فرض کنید هزینه سوخت خودرو به ازای هر کیلومتر 240 واحد هست و ما میخواهیم تاثیرات مسافت طی شده خودرو را بر هزینه سوخت آن به ازای مسافت های یک تا ده کیلومتر تحلیل کنیم. برای اینکار ابتدا فرمول A1*240= را در سلول A2 مینویسیم. سپس اعداد 1 تا 10 رو در سلول های B1 تا K1 قرار میدهیم. حالا سلولی که فرمول را در آن نوشتیم یعنی سلول A2 را به همراه سلول های B1 تا K1 که مقادیر متغیر در آنها قرار دارند را انتخاب میکنیم و از تب DATA و بخش Data Tools را گزینه What-If Analysis کلیک میکنیم ودر منویی که باز میشود گزینه Data Table را انتخاب میکنیم.                                                                                                                                                                                     

با انتخاب گزینه پنجره ای باز میشود.

حالا روی فلش قرمز رنگ گزینه Row Input Cell کلیک میکینم. با کلیک روی این گزینه پنجره زیر باز میشود که ما باید درآن سلول مربوط به متغیر مورد نظررا انتخاب کنیم که این سلول همان سلول A1 است.

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

تحلیل دو متغیره در Data Table اکسل:

در بخش قبل حالت تک متغیره ابزار Data Table اکسل را بررسی کردیم. حالا میخواهیم حالت دو متغیره Data Table را امتحان کنیم بنابراین به یک مثال کاملتر احتیاج داریم. فرض کنید رشد یا کاهش فروش سازمان ما تابع دو متغیر مقدار افزایش تبلیغات و میزان افت کیفی محصول باشه به طوری که با هر واحد افزایش تبلیغات 17درصد به فروش سازمان افزوده بشه و با هر واحد کاهش کیفیت محصول 30درصد  از فروش سازمان کم بشه. میخوایم اثرات تغییر این دو متغیر رو روی افزایش یا کاهش فروش سازمان بررسی کنیم. دامنه بررسی ما برای متغیر کاهش کیفیت محصول بین 0.5 تا 3 واحد و برای متغیر افزایش تبلیغات بین 0 تا 5 واحد هست. برای شروع ابتدا در سلول A3 فرمول افزایش فرمول تغییرات فروش سازمان رو که A1*-0.3+A2*0.17= مینویسیم. با توجه به فرمولی که ما نوشتیم سلول A1 معرف متغییر کاهش کیفیت و سلول A2 معرف متغییر افزایش تبلیغات است. حالا در سلول های B3 تا G3 اعداد مربوط به دامنه تغییرات کاهش کیفیت یعنی اعداد 0.5 تا 3 و در سلول های A4 تا A9 اعداد مربوط به دامنه تغییرات افزایش تبلیغات یعتی 0 تا 5 را قرار میدهیم

حالا مثل حالت تک متغیره از تب DATA گزینه Data Table را انتخاب میکنیم و در بخش Row Input Cell سلول A1 و در بخش  Column Input Cell سلول A2 رو قرار میدهیم و پنجره Data Table را OK میکنیم. با اینکار درصد تغییرات فروش برای مقادیر مختلف افزایش تبلیغات و کاهش کیفیت محصول در جدول نمایش داده میشود

فعال کردن تب DEVELOPER در اکسل

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

ابتدا از منوی فایل روی گزینه Options کلیک کنید:

در پنجره ای که باز میشود از سمت چپ رو گزینه Customize Ribbon کلیک کنید و بعد در سمت راست تیک گزینه Developer رافعال کنید و بعد گزینه یok را کلیک کنید.

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

در این تب علاوه بر گزینه های مربوط به کار با ماکرو، گزینه های دیگری مثل دسترسی به Visual Basic Editor ، ایجاد کنترل ها روی شیت اکسل و … وجود دارد که برای پیاده سازی کدهای VBA  به آنها نیاز خواهیم داشت.  برای شخصی سازی کردن بیشتر PivotTable اکسل تنظیماتی رامیتوانیم روی این ابزار اعمال کنیم.

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

 

• تب Analyze: که میتوانیم تنظیمات را درباره بخش های مختلف pivotTable درآن انجام دهیم، تغییراتی را روی فیلدهای جدول اعمال کنیم
• تب Design: که مربوط به انجام تغییرات ظاهری PivotTable مثل رنگ ردیف‌ها، رنگ سرتیترها و … است.

روی تب Analyze کلیک کنید. این تب بخش های مختلفی داره که که عمده تنظیمات مهم PivotTable را دربرمیگیرد. اولین بخش مربوط به تنظیمات کلی PivotTable است.

1. PivotTable Name: در این بخش میتوانیم یک نام برای PivotTable  انتخاب کنیم. این نام در بخش های مختلف مثل کدنویسی VBA استفاده میشود.

2. Options: با انتخاب این گزینه منوی زیر باز میشود که سه گزینه دارد.

• Options: با انتخاب این گزینه پنجره تنظیمات PivotTable اکسل باز میشود.
• Show Report Filter Pages: این گزینه از فیلدهایی که در بخش فیلتر  وجود دارند.
• Generate GetPivotData: که مربوط به تنظیمات نوع رفرنس دادن به سلول های PivotTable است.

اکنون روی گزینه Options کلیک کنید.

 مهم ترین و کاربردی ترین تنظیمات این پنجره شامل موارد زیر است:

1. تب Layout & Format:

• For error values show: با انتخاب این گزینه میتوانیم عبارتی را تعیین کنیم که در صورت وجود خطا در مقادیر موجود در PvotTable به جای آن مقدار نمایش داده شود
• For empty cells show: با انتخاب این گزینه میتوانیم عبارتی را تعیین کنیم که در صورت وجود مقدار خالی در PvotTable به جای سلول خالی نمایش داده شود.
• Autofit column widths on update: اگر این گزینه فعال باشد، با هر بار Refresh کردن PivotTable ، عرض ستون ها با توجه به مقادیر جدید به صورت خودکار تنظیم شود.
• Preserve cell formatting on update: اگر این گزینه فعال باشد فرمت بندی هایی که روی  رنگ و … سلول ها انجام دادیم با Refresh کردن Pivottable از بین نمی رود.

 2. تب Total & Filters:

• Show grand totals for rows: در صورتی که این گزینه فعال باشد جمع مقادیر موجود در PivotTable در انتهای هر سطر نمایش داده میشود.
• Show grand totals for columns: در صورتی که این گزینه فعال باشد جمع مقادیر موجود در PivotTable در انتهای هر ستون نمایش داده میشود.

3. تب Display:

• Show expand/collapse buttons: اگر این گزینه را غیر فعال کنیم علامت های مثبت و منفی در کنار سطرهای PivotTable نمایش داده نمیشود.
• Display field cptions and filter drop downs: اگر این گزینه رو غیرفعال کنیم نام فیلد ها و همینطور دکمه فیلتر کردن مخفی میشود.
• Field List: در این بخش میتوانیم نحوه مرتب شدن لیست فیلدها را مشخص کنیم. منظور از لیست فیلدها  بخش بالا و سمت راست PivotTable است که نام فیلدها در آن نمایش داده میشود و میتوانیم آنها را به بخش های مختلف اختصاص دهیم. اگر گزینه Sort A to Z را انتخاب کنیم فیلدها به ترتیب نام مرتب میشوند و در صورتی که گزینه Sort in datasource order را انتخاب کنیم فیلدها به همان ترتیبی که در شیت مرجع ما قرار دارند نماش داده میشوند.این گزینه زمانی که فیلدهای زیادی داریم میتواند خیلی مفید باشد و در پیدا کردن فیلد موردنظرمان کمک کند.

4. تب Printing:

• Print expand/collapse buttons when displayed on PivotTable: با انتخاب این گزینه علامت های مثبت و منفی کنار هر سطر هنگام پرینت PivotTable چاپ میشوند.
• Repeat row labels on each printed page و Set print titles: در صورتی که این دو گزینه را فعال کنیم و PivotTable ما هنگام چاپ بیش از یک صفحه باشد، عنوان فیلدها در هر صفحه بالای سطرها چاپ میشود.

5. تب Data:

• Save source data with file: اگر این گزینه فعال باشد و  برای ایجاد PivotTable  از منبع خارجی (مثل access و SQL Server) اسفاده کرده باشیم، اطلاعات اتصال به مبع در درون فایل اکسل ذخیره میشود.
• Refresh data when opening file: با انتخاب این گزینه زمانی که  فایل اکسل راباز میکنیم PivotTable  به صورت خودکار Refresh میشود.
• Enable show details: در صورتی که این گزینه فعال باشد کاربر میتواند با راست کلیک کردن روی هر بخش از اطلاعات PivotTable و انتخاب گزینه Show details، اطلاعات مربوط به آن سلول را در شیت اصلی کهPivotTable را بر اساس آن ساخته ایم ببیند. اگر بخواهیم این امکان راغیر فعال کنیم کافیست تیک مربوط به این گزینه را برداریم.

6. تب Alt Text:

در این بخش میتوانیم توضیحات و اطلاعاتی راجع به PivotTable  ارائه دهیم تا کسانی که نمیتوانندبه دلیل معلولیت و … PivotTable رو ببینند بتوانند از این توضیحات استفاده کنند

نکاتی درمورد Sheet های اکسل

در اکسل برای  Sheet ها نکاتی وجود دارد که شامل موارد زیر است  :                                                                                                                                                                                                                                                               1- تغییر نام Sheet ها

2- اضافه کردن Sheet ها

3- حذف Sheet ها

4- انتقال Sheet ها در یک پوشه

5- کپی کردن Sheet ها در یک پوشه

6- انتقال یک Sheet از book هائی به book دیگر

7- کپی کردن Sheet های از یک کار پوشه به کار پوشه دیگر

 

تغییر نام Sheet ها :
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Format
۳- انتخاب گزینه Sheet
۴- انتخاب گزینه Rename
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Rename
روش سوم :
دابل کلیک بر روی Sheet
 
 
اضافه کردن Sheet ها :
روش اول :
۱- انتخاب Sheet که می خواهیم Sheet جدید قبل از آن قرار گیرد.
۲- انتخاب منوی Insert
۳- انتخاب گزینه Work Sheet
۴- OK
روش دوم :
۱- راست کلیک بر روی Sheet هایی که می خواهیم Sheet جدید قبل از آن قرار گیرد .
۲- انتخاب گزینه Insert
۳- انتخاب گزینه Work Sheet
۴- Ok
 

حذف Sheet ها :
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Edit
۳- انتخاب کزینه Delete Sheet
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Delete
 
انتقال Sheet ها در یک پوشه
روش اول :
۱- انتخاب Sheet
۲- انتخاب منوی Edit
۳- انتخاب گزینه move or copy sheet
۴- در پنجره باز شده در کادر Before sheet نام Sheet هایی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم یا اگر می خواهیم به انتها انتقال دهیم ، Move to end را انتخاب می کنیم .
۵- OK
روش دوم :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه Move or Copy
۳- در پنجره باز شده در کادر Before Sheet نام Sheet یی را که می خواهیم این Sheet قبل از آن قرار گیرد انتخاب می کنیم .
۴- OK
 
 
کپی کردن Sheet ها در یک پوشه :
مانند انتقال Sheet ها می باشد فقط کافی است در انتها در کنار Create a copy تیک بزنیم .
 
 
انتقال یک Sheet از book هائی به book دیگر :
۱- راست کلیک بر روی Sheet
۲- انتخاب گزینه move or copy sheet
۳- در کادر To Bookنام فایل مورد نظر را که می خواهیم Sheet را به آن انتقال دهیم انتخاب می کنیم .
۴- در قسمت Before Sheet هم نام Sheet هایی راکه می خواهیم Sheet انتقالی قبل از آن قرار گیرد ، انتخاب می کنیم .
۵- OK
 
 
کپی کردن Sheet های از یک کار پوشه به کار پوشه دیگر :
تمام مراحل ماند انتقال است فقط در کادر Create a copy تیک می زنیم .
 
 
تذکر : باید توجه داشته باشیم که Book ئی که می خواهیم فایل را به آن منتقل یا کپی کنیم حتما باز باشد .

کاربرد format painter در اکسل

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

برای اینکار پس از انتخاب آیتمی که قصد داریم قالب بندی آن را روی آیتم های دیگر اعمال کنیم، از تب Home روی آیکون Format Painter کلیک میکنیم.

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

فرض کنید میخواهیم فرمت سطرهای سوم تا پنجم را هم مانند سطر دوم کنیم. برای اینکار ابتدا سطر دوم یعنی سلول های A1 تا C1 را انتخاب میکنیم. بعد از تب Home ابزار Format painter رو انتخاب میکنیم. حالا توسط ابزار Format Painter سطرهای سوم تا پنجم یعنی سلول های A2 تا C5 رو انتخاب میکنیم. با انجام اینکار فرمت سطر دوم روی سطرهای سوم تا پنجم اعمال میشه. یعنی فونت اونها تغییر میکند، حاشیه سلولها به رنگ مشکی درمی آید و رنگ پس زمینه ستون دوم هر سطر خاکستری میشود.

نکته: اگر بخواهیم قالب بندی روی آیتم های مختلف اعمال کنیم میتوانیم روی آیکون Format Painter دوبار کلیک کنیم. با اینکار می تونیم قالب بندی را روی چند آیتم مختلف اعمال کنیم و نیازی به انتخاب دوباره ابزار Format Painter بعد از هر بار اعمال قالب بندی نباشد.

ثبت توضیحات با ابزار کامنت گذاری در اکسل

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

اگر بخواهیم برای نحوه استفاده از  فایل کامنت بگذاریم و توضیح دهیم که بخش های مختلف فایل چه نقشی دارند.  ابتدا روی سلول مربوط به عدد اول یعنی سلول B1 کلیک میکنیم. بعد از تب REVIEW و بخش Comments روی گزینه New Comment کلیک میکنیم.                                                                     با کلیک کردن روی این گزینه یک کادر باز میشودکه میتوانید داخلش توضیحاتی رابرای سلولی که انتخاب کردید بنویسید.                                                                                                            

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

• New Comment یا Edit Comment: اولین گزینه در بخش Comments این گزینه است. با انتخاب این گزینه میتوانید برای سلول انتخابی خود کامنت اضافه کنید. در صورتی که سلول انتخاب شده توسط  در حال حاضر دارای کامنت باشد این گزینه به Edit Comment تغییر میکند و با کلیک روی آ ن میتوانید کامنتی که ایجاد کردید را ویرایش کنید.
• ِDelete: با انتخاب این گزینه کامنت سلول انتخاب شده حذف میشود.
• Previous: در صورتی که در فایل اکسل  چند کامنت قرار داده شده باشد با انتخاب این گزینه کامنت قبلی  نمایش داده میشود.
• Next: در صورتی که در فایل اکسل ا چند کامنت قرار داده شده باشه با انتخاب این گزینه کامنت بعدی ب نمایش داده میشود.
• Show/Hide Comment: در حالت عادی کامنت ها با بردن ماوس روی سلول مربوط به آن   نمایش داده میشود.                                                                                                                                              اگر بخواهیم کامنتی به صورت ثابت نشون داده شود از این گزینه استفاده میکنیم. اگر بخواهیم دوباره نحوه نمایش کامنت به حالت اولیه برگردد کافیست یک بار دیگه روی این گزینه کلیک کنیم.
• Show All Comments: با انتخاب این گزینه تمام کامنت های موجود در فایل اکسل نمایش داده میشوند .                                                                                                                اگر بخوایم این حالت رو غیر فعال کنیم کافیست یک بار دیگه روی این گزینه کلیک کنیم.

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

 

                                      

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

 

ترفند شماره 1  اکسل: جمع زدن سریع با کلید ALT  و =

کافی است که در انتهای یک سطر یا ستونی سلولی را انتخاب کنید و این کلید = +  ALT را بزنید تا تابع SUM برای شما نوشته شود.                                                                                                               

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

جالب است بدانید که کلیدهای CTRL + SHIFT و اعداد 1 تا 5 برای فرمت سل اعداد بکار می‌روند. خود من بیشترین استفاده را از کلید CTRL+SHIFT+1 می‌کنم که این کلید باعث نمایش یک عدد در حالت Number می‌شود.

 

ترفند شماره 3 اکسل: نمایش همه فرمول‌ها

با زدن کلید    `  +  CTRL حالت شیت اکسل عوض می‌شود و تمامی فرمول ها را به شما نماش می‌دهد. فراموش نکنید که با دوباره زدن همین کلید مجدد به حالت عادی شیت بر خواهید گشت.

نکته: علامت    `    را در سمت چپ عدد 1 کیبورد کامیپوتر باید پیدا کنید. روی این کلید علامت ~ هم می بینید.)

ترفند شماره 4 اکسل: پرش به ابتدای یک سطر یا ستون از لیست با کیبورد

اگر فایل شما پر از اطلاعات باشد و مرتبا بخواهید به انتهای یا ابتدای لیست خود بروید شک نکنید که باید از کلیدهای ↓  + CTRL  برای رفتن به انتهای ستون (سطر) و کلید ↑  + CTRL برای رفتن به ابتدای لیست هایتان استفاده کنید.

 

 

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

شک نکنید که کپی کردن و یا درگ کردن زیادی کاری سخت برای من حساب می‌شود. اگر یک لیست دارید و یک فرمول هم در آن لیست استفاده شده است که باید در سایر سطرها کپی شود، خوب کافیست که Double-Click کنید روی  نقطه کوچک (به نقطه مربع شکل کوچکی که هنگام انتخاب یک سلول می بینید اصطلاحا Fill Handle می گویند)

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

 

ترفند شماره 6 اکسل: اضافه و حذف یک سطر یا ستون

تقریبا یک کار مهم در اکسل اضافه و یا کم کردن سطر و ستون ها است . برای اینکار می توانید به راحتی از کلید- +  CTRL   (کنترل و منها) برای حذف و برای اضافه کردن  =  +  CTRL+SHIFT (کنترل شیفت مساوی) برای اضافه کردن استفاده کنید.

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

ترفند شماره 7 اکسل: تنظیم عرض ستون‌ها 

 اگر بخواهید اندازه عرض ستون‌های یک لیست اکسل طوری تنظیم شود که متناسب با نوشته های آن ستون باشد (یعنی عرض ستون برابر شود با عرض بزرگترین نوشته) آن وقت کافیست که بر روی مرز بین دو ستون Double-click کنید.

 

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

وقتی که شیت های شما زیاد است و می خواهید یک شیت خاص را از روی ظاهرش (نه اسم آن) پیدا کنید باید تک تک شیت ها را ببیند. خوب یک کلید عالی برای جابجا شدن بین شیت های اکسل داریم Ctrl + PageUP و Ctrl  + pageDown .

 

ترفند شماره 9 اکسل: Double-click بر روی ابزار Format Painter

تقریبا همه کاربران اکسل، آتلوک و ورد با ابزار Format Painter آشنا هستند و می دانند که این ابزار برای copy-paste کردن فرمت (تنظیمات ظاهر) استفاده می شود و جالب است که اکثر آنها نمی دانند که اگر روی این ابزار Double-Click کنند ، می توانند چندی بار از آن در جاهای مختلف استفاده نمایند.

                                     

ایجاد محدوده پویا برای نمودارها

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

برای این کار ما ابتدا باید دو محدوده متغیر ، یکی برای محور X و یکی برای محور  Y تعریف کنیم. پس از تب FORMULAS گزینه NAME MANAGER را انتخاب می کنیم و یک محدوده جدید ایجاد می کنیم. بعد از اینکه یک اسم در نظر گرفتیم برای محدوده در قسمت انتخاب محدوده مورد نظر این فرمول رو وارد می کنیم :

=OFFSET(Sheet1!$A$4;0;0;COUNTA(Sheet1!$A$4:$A$100);1)

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

مشابه همین کار را برای محور Y انجام میدهیم تا محدوده های پویا را ایجاد کرده باشیم.

اکنون دو محدوده پویا داریم یکی برای محور X و یکی برای Y کافیست نمودار ستونی که داریم را براساس این دو محدوده ایجاد کنیم.

برای اینکار بعد از اینکه یک نمودار ستونی ایجاد کردید نمودار را انتخاب و از تب DESIGN که مربوط به نمودار است گزینه SELECT DATA را انتخاب کنید.

برای محور X ها گزینه EDIT رو انتخاب کنید ودر قسمت SERIS VALUES به جای محدوده SELECT شده اسم محدوده ای که ایجاد کردید را وارد کنید فقط قبل از اسم محدوده حتما اسم شیت را هم وارد کنید یعنی به این شکل : 

='1.xlsx'!X

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

سلول های اکسل وتنظیمات آنها

                                                                                                                                            یک سلول می تواند یک «مقدار» یا یک «فرمول» را بپذیرد. برای وارد کردن داده در یک سلول، کافیست که بر روی آن سلول کلیک چپ کنید و سپس داده مورد نظر را تایپ کنید. برای اینکه داده مورد نظر را در نهایت به سلول اختصاص دهید، باید پس از وارد کردن، یکی از کلیدهای Enter یا Tab از صفحه کلید را فشار دهید.
در صورتی که کلید Enter را انتخاب کنید، بعد از اینکه داده به سلول اختصاص داده شد، مکان نما در سلول زیرین قرار می گیرد. در صورتی که کلید Tabرا انتخاب کنید، داده به سلول اختصاص داده شده و مکان نما در سلول بعدی قرار میگیرد .                                                                                                                                    ویرایش سلول ها
این نوار برای ویرایش یک سلول نیز کاربرد دارد. برای ویرایش سلول، کافیست که بر روی سلول کلیک کنید و با استفاده از نوار فرمول، داده موجود در سلول را تغییر دهید.                                                                  ه همین ترتیب می توانید با استفاده از کلید Shift یکسری سلول را به صورت ستونی انتخاب کنید. باید توجه داشت که علاوه بر استفاده مستقیم از صفحه کلید برای انتخاب سلول ها، می توان از ماوس نیز برای انتخاب دسته ای سلول استفاده کرد. کافیست با نگه داشتن کلید چپ و حرکت بر روی سلول ها و در پایان با رها کردن کلیک چپ، عملیات انتخاب تعدادی از سلول ها را آزمایش کنید. به این روش Drag کردن نیز می گویند.

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

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

جایگزینی (Paste) یک سلول
برای جایگزینی یک سلول کپی شده، کافیست سلول جدید را انتخاب، سپس با کلیک راست از گزینه های موجود، گزینه Paste را انتخاب کنید.

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

 

نکته: برای انجام عملیات بالا، می توان از صفحه کلید نیز استفاده کرد. برای کپی کردن از کلیدهای Ctrl+C در ویندوز (Cmd+C در مک)، برای جایگزین کردن از کلیدهای Ctrl+V در ویندوز (Cmd+V در مک) و همچنین برای برش و انتقال یک سلول از گزینه های Ctrl+X در ویندوز (Cmd+X در مک) استفاده می شود.

استفاده از گزینه های Copy و Cut و Paste در زبانه Home
اگر به گزینه های زبانه Home توجه کنید، اولین بخش، بخشی است به اسم Clipboard که می توان عملیات رونویسی، برش و جایگزینی را از این طریق نیز انجام داد.

 

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

اما اگر بخواهیم سلول انتخاب شده به همراه محتویاتش را به طور کامل حذف کنیم، از گزینه های موجود که در تصویر بالا نشان داده شده، به جای گزینه Clear Contents، گزینه Delete را انتخاب می کنیم. وقتی یک سلول را به طور کامل حذف می کنیم، به جای سلول مورد نظر یک حفره ایجاد می شود که باید سلول های اطراف جایگزین این فضای خالی شوند. برای پرکردن این فضای خالی، نرم افزار اکسل پیش از حذف سلول از ما می پرسد که سلول های اطراف چگونه جایگزین این فضای خالی شوند. به عبارت دیگر همزمان با انتخاب گزینه Delete پنجره ای مانند تصویر زیر نیز باز می شود. در زیر به بررسی گزینه های این پنجره می پردازیم:

گزینه Shift Cell Left سلول سمت راست را جایگزین سلول پاک شده می کند.
گزینه Shift Cells Up سلول زیرین را جایگزین سلول پاک شده می کند.
گزینه Entire Row سطر زیرین سلول پاک شده را به جای سطری که سلول پاک شده در آن قرار دارد، منتقل می کند.
گزینه Entire Column ستون سمت راست سلول پاک شده را به جای ستونی که خانه پاک شده در آن قرار دارد، منتقل می کند.

قابلیت رونویسی سلول ها در سلول های همسایه
با کلیک بر روی هر سلول، یک مربع سیاه کوچک در گوشه سمت راست سلول دیده می شود. این مربع دستگیره Auto Fill نام دارد. اگر ماوس را بر روی این علامت نگه دارید، اشاره گر ماوس به شکل علامت مثبت دیده خواهد شد. در صورتی که این دستگیره را به جهت های مجاور (چپ- راست، بالا و پایین) بکشید (Drag کنید)، خواهید دید که محتویات سلول در در سلول های انتخاب شده کپی خواهند شد. این قابلیت زمانی کاربرد دارد که بخواهید داده های تکراری در سلول های مجاور وارد کنید.

دستگیره Auto Fill در مورد اعداد قابلیت ویژه ای دارد. از این قابلیت برای افزایش و یا کاهش عددها در یک صفحه گسترده استفاده می شود. اگر هنگام کشیدن دستگیره Auto Fill کلید CTRL را هم نگه دارید، این قابلیت اجرا خواهد شد. در صورتی که دستگیره را به سمت راست یا پایین بکشید، عددها افزایش پیدا خواهند کرد. در صورتی که دستگیره به سمت سلول های قبلی (سمت چپ) یا بالا کشیده شود، عددها کاهش می یابند.

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

ویرایش سلول ها
یک سلول را می توان به سه روش ویرایش کرد. روش اول این است که بر روی سلول دو بار کلیک کنید. در این صورت محتویات درون آن آماده تغییر می شود و می توانید داده ها را ویراش کنید. دومین روش استفاده از نوار فرمول است. برای کاربرد نوار فرمول رجوع کنید به در درس اول و شماره ۵ از بخش «معرفی بخش ها و زبانه های اصلی»؛ محتویات هر سلول را می توانید در نوار فرمول مشاهده کنید و آن را ویرایش کنید.
سومین روش ویرایش سلول ها، استفاده از کلید F۲ است. اگر این کلید را فشار دهید، سلول مورد نظر در حالت ویرایش قرار می گیرد.

اضافه کردن سطر و ستون
برای اضافه کردن سطر و ستون از زبانه Home و در بخش Cells بر روی گزینه Insert کلیک می کنیم. عبارت Insert Sheet Rows برای اضافه کردن سطر و عبارت Insert Sheet Columns برای اضافه کردن ستون به کار می رود.

حذف کردن سطر و ستون
برای حذف کردن سطر و ستون از زبانه Home و در بخش Cells بر روی گزینه Delete کلیک می کنیم. عبارت Delete Sheet Rows برای حذف کردن سطر و عبارت Delete Sheet Columns برای حذف کردن ستون به کار می رود.

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

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

در روش عددی، ابتدا بر روی سطر یا ستون مورد نظر کلیک راست کنید. از گزینه های موجود، گزینه Row Height را برای تغییر سطر و یا گزینه Column Width را برای تغییر ستون انتخاب کنید. در هر دو حالت پنجره ای باز می شود که اندازه فعلی را نشان می دهد. می توانید اندازه جدید را که دقیق تر از حالت قبلی خواهد بود، وارد کنید.

ذخیره پرونده اکسل
در پایان این درس به شیوه ذخیره سازی یک پرونده می پردازیم.
برای ذخیره یک پرونده بر روی دکمه Office «گوشه بالا و سمت چپ» کلیک کنید و از پنجره باز شده، و با کلیک بر روی گزینه Save as مکانی که می خواهید پرونده مورد نظرتان را ذخیره کنید را انتخاب کنید. (از گزینه های مقابل Save as، نخستین گزینه را انتخاب کنید.) از بخش File name نیز می توانید یک نام برای سند خود انتخاب کنید و در پایان گزینه Save را کلیک کنید.
توجه: کلیدهای میانبر ذخیره سازی در اکسل Ctrl+S در ویندوز و Cmd+S در مک هستند.

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

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

 ابتدا یک شیت جدید باز کنید. سپس  زیر روی Sheet1 راست کلیک کنید و از منوی باز شده گزینه View Code را انتخاب کنید.

 

با انتخاب این گزینه پنجره Visual Basic for Applications باز میشودکه از آن میتوانیم برای کدنویسی VBA در اکسل استفاده کنیم. در اینجا میتاونیم کدهای مربوط به کار با تاریخ شمسی در اکسل رو به فایل اکسل خود اضافه کنیم.

برای اینکار ابتدا  از منوی Insert گزینه Moduleرا انتخاب کنید.

 

با اینکار یک Module به فایل اکسل اضافه میشود که می توانیم کدهای مربوط به تاریخ شمسی را در آن قرار میدهیم. برای اینکه کدها راداخل ماژولی که ایجاد کردیم قرار دهیم از بخش Project Explorer را Module1 دوبار کلیک میکنیم.

 

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

اما قبل از استفاده از این توابع بهتراست فایل را به صورت صحیح ذخیره کنیم.  پسوندی که اکسل برای ذخیره فایل ها به صورت پیش فرض از آن استفاده میکند در نسخه های قدیمی تر xls و در نسخه های جدیدتر xlsx هست. این پسوندها قابلیت ذخیره سازی کدهای VBA رو ندارند. به این معنی که اگر شما فایل را  با این پسوندها دخیره کنید کدهای VBA که به فایل اضافه کردید ذخیره نمی شوند و دفعه بعد که فایل را باز کنید تمام کدها از فایل حذف شده است و عملاً فایل شما به درستی کار نمیکند.برای رفع این مشکل  باید فایل را با یکی از پسوندهای xlsm یا xlsb ذخیره کنیم. برای اینکه فایل را با پسوند xlsm ذخیره کنیم ودر پنجره Save As نوع پسوند رو در قسمت Save As Type برابر (Excel Macro-Enabled Workbook (*.xlsm قرار دهیم.

اگر تمام مراحل را به درستی انجام داده باشید باید به توابع کار با تاریخ شمسی دسترسی داشته باشید. برای آزمایش این مساله روی یکی از سلول ها شروع به فرمول‌نویسی کنید. اگر با نوشتن عبارت sh= در فرمول سلول تایع shamsi در گزینه ها نمایش داده شد، توابع شمسی به درستی در فایل اکسل شما فعال شدند و شما میتوانید از آنها استفاده کنید                                                                                                 رفع مشکل ؟ شدن کاراکترهای فارسی در VBA: اگر کاراکترهای فارسی در پنجره Visual Basic Editor به صورت ؟؟؟ نمایش داده میشود کافیست  فایل shamsi-date.txt را به جای نوت پد با Wordpad باز کنید و پس از کپی کردن کدها از داخل Wordpad آنها را داخل visual basic editor پیست کنید.

 

تابع Shamsi: این تابع تاریخ جاری سیستم راتبدیل به تاریخ شمسی میکند.

 

تابع Rooz: این تابع روز مربوط به یک تاریخ مشخص را برمیگرداند.

 

تابع Mah: این تابع ماه مربوط به یک تاریخ مشخص را بر می گرداند.

 

تابع Sal: این تابع سال مربوط به یک تاریخ مشخص را بر می گرداند.

 

تابع Kabiseh: این تابع مشخص میکند که آیا سال موردنظر کبیسه است  یانه. در صورتی که سال موردنظر کبیسه باشه عدد 1 و در غیر اینصورت عدد 0 به عنوان نتیجه تابع برگردانده میشود.

 

تابع ValidDate: این تابع تاریخ شمسی که به آن داده شده را  بررسی میکندو در صورتی که تاریخ داده شده معتبر و درست باشد true و در غیر اینصورت false رو برمیگرداند .      

تابع AddDay:  این تابع تعداد روز مشخصی را به یک تاریخ اضافه میکند.

تابع DayWeek: این تابع نام روز تاریخ شمسی که به آن دادده شده را برمی گرداند.

 

تابع Dat: این تابع تاریخ شمسی رو به همراه روز هفته برمیگرداند.

 

تابع Diff: این تابع اختلاف روزهای بین دو تاریخ شمسی را نشان میدهد.

 

تابع DayWeekNo: این تابع عدد مربوط به روز هفته یک تاریخ شمسی را بر می گرداند.

 

تابع MahName: این تابع عدد مربوط به یک ماه را دریافت میکند و نام ماه مربوط به آن را برمیگرداند.

 

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

 

تابع MahDays: این تابع تعداد روزهای یک ماه را برمیگرداند.

 

تابع Make_Date: این تابع سال، ماه و روز تاریخ شمسی را با علامت “/” از هم  جدا میکند.

 

تابع NextMah: این تابع مقدار سال و ماه رابه عنوان ورودی میگیره و ماه بعد رابه عنوان نتیجه برمیگرداند.

 

تابع PreviousMah: این تابع مقدار سال و ماه را به عنوان ورودی میگیرد و ماه قبل را به عنوان نتیجه برمیگرداند.

 

تابع SubtractDay: این تابع تعداد روز مشخصی را از یک تاریخ شمسی کم میکند.

 

تابع Firstday: این تابع یک سال و ماه شمسی رو میگیره و شماره اولین روز آن ماه رو برمیگرداتد.

 

ثابت(FREEZE) کردن سطرها و ستون ها در اکسل

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

برای اینکار میتوانیم از بخش Freeze Panes اکسل استفاده کنیم. از تب VIEW و بخش Freeze Panes روی گزینه Freeze Top Row کلیک میکنیم.

 

برای غیرفعال کردن این حالت هم کافیست از مسیر قبلی یعنی تب VIEW و بخش Freeze Panes را گزینه Unfreeze Panes کلیک کنیم تا فایلبه حالت قبل برگردد.

 

اگر بخواهیم به جای اولین ستون، اولین سطر رو به حالت ثابت در بیاوریم کافیست در بخش Freeze Panes روی گزینه Freeze First Column کلیک می کنیم

ثابت(FREEZE) کردن سطرها و ستون ها در اکسل

  اگر بخواهیم سطر اول و ستونهای اول تا چهارم رو ثابت کنیم بنابراین باید سلولی را انتخاب کنیم که شمار سطر آن 2 و شماره ستون آن 5 باشه که این سلول E2 هست. بعد از انتخاب سلول E2، از تب VIEW و بخش Freeze Panes  روی گزینه Freeze Panes کلیک میکنیم.

حالا اگر در فایلمون به سمت پایین یا چپ اسکرول کنیم چهار ستون اول و سطر اول از دید ما خارج نمی شوند  ثابت باقی می مانند

برای برگرداندن فایل به حالت عادی  میتوانیم روی گزینه Unfreeze Panes کلیک کنیم.

کلیدهای میانبر پرکاربرد برای انجام فعالیت‌ها در اکسل

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

1. CTRL+PGDOWN: این کلید میانبر اکسل شیت بعدی اکسل را فعال میکند.
2. CTRL+PGDOWN: این کلید میانبر شیت قبلی اکسل را فعال میکنر.
3. CTRL+کلیدهای چهار جهت اصلی: ترکیب کلید کنترل با هر یک از چهار جهت اصلی باعث میشود به آخرین بخش اطلاعات در جهتی که از کلیدش استفاده کردید برید. به عنوان مثال اگر شما 10 سطر اطلاعات داشته باشید و یکی از سلول های سطر اول فعال باشه با استفاده از ترکیب کلید کنترل و جهت پایین به سطر دهم خواهید رفت. این کلیدها زمانی که حجم اطلاعات شما خیلی زیاده و برای رسیدن به اول یا آخر اطلاعات باید کلی اسکرول کنید خیلی مفیداست.
4. CTRL+HOME: این کلید میانبر باعث میشود تا اولین سلول شیت یعنی سلول A1 فعال شود.
5. CTRL+1: این کلید میانبر اکسل پنجره مخصوص فرمت کردن سلول رو نمایش میدهد.
6. CTRL+SHIFT+L: این کلید میانبر ابزار فیلتر رو برای اطلاعات شیت اکسل فعال میکند.
7. F4: یکی از مفیدترین کلیدهای میانبر اکسل است. زمانی که  در اکسل فرمول نویسی میکنید با کلید F4 میتوانید حالت های مختلف نسبی یا مطلق بودن داده ها رافعال کنید.
8. CTRL+’: این کلید میانبر فرمول سلول بالایی را در سلولی که انتخاب کردیم کپی میکند.
9. CTRL+T: این کلید میانبر محدوده ای رو که در اکسل انتخاب کردیم به جدول یا همون TABLE تبدیل میکند.
10. CTRL+S: این کلید میانبر برای ذخیره کردن فایل استفاده میشود.
11. CTRL+N: این کلید میانبر یک ورکبوک جدید باز میکند.
12. CTRL+O: این کلید میانبر پنجره ای رابرای باز کردن فایل اکسل باز میکند.
13. CTRL+W: این کلید میانبر اکسل ورکبوک فعلی رو که در حال کار با اون هستیم می بندد.
14. CTRL+F: با استفاده از این کلید میانبر پنجره جستجو در اکسل باز میشود.
15. F9: این کلید باعث میشه که محاسبات شیت، PIVOT TABLE یا DATA TABLE ما مجددا انجام شود. زمانی که ما نحوه محاسبات خودمون رو دستی قرار میدیم این کلید کاربرد زیادی دارد.
16. ALT+F11: این کلید میانبر اکسل پنجره ویرایشگر Visual Basic را که برای ماکرونویسی مورده استفاده قرار میگیردباز میکند.
17. CTRL+SPACE: با استفاده از این کلید میانبر میتونیم کل ستونی راکه در حال ویرایش یکی از سلول های آن هستیم انتخاب کنیم.
18. SHIFT+SPACE: با استفاده از این کلید میانبر میتونیم کل سطری راکه در حال ویرایش یکی از سلول های آن هستیم انتخاب کنیم.
19. ALT+=: این کلید میانبر برای استفاده از قابلیت AUTOSUM اکسل استفاده میشود. اگر تعدادی سلول را انتخاب کنبم و از این کلید میانبر استفاده کنیم اکسل به صورت خودکار جمع مقادیر آنها در انتهای سلول ها اضافه میکند.
20. CTRL+B: این کلید میانبر اکسل برای ضخیم کردن  نوشته های سلول استفاده میشود.
21. CTRL+F1: با استفاده از این کلید ترکیبی میتونیم ریبون یا همون نوار ابزار قسمت بالای اکسل رو مخفی یا ظاهر کنیم. با مخفی کردن ریبون تعداد سطرهایی که میتونیم یکجا ببینیم افزایش پیدا میکند و میتوانیم بهتر روی کار با فایل تمرکز کنیم.
22. CTRL+END: با استفاده از این کلید ترکیبی میتونیم آخرین سلول حاوی اطلاعات را انتخاب کنیم.
23. ALT+ENTER: با استفاده از این کلید میتونیم زمانی که میخواهیم داخل یک سلول چند خط مطلب بنویسیم برویم به خط بعد.
24. CTRL+D: این کلید ترکیبی فرمول اولین سلول محدوده انتخاب شده را در تمام سلول های پایینتر کپی میکند.
25. CTRL+ALT+V: این کلید پنجره Paste Specialرا باز میکند که برای Paste کردن محتویات اطلاعات حافظه موقت با فرم های خاص استفاده میشود. مثلاً زمانی که میخوایم به جای فرمول، مقدار سلول Paste شود.
26. CTRL+SHIFT+END: این کلید ترکیبی از سلول فعلی تا آخرین سلول شیت را انتخاب میکند.
27. CTRL+SHIFT+کلیدهای جهت دار: با استفاده از این کلید میانبر میتوانیم از سلول فعلی تا آخرین محدوده شیت که اطلاعات دارد را انتخاب کنیم. فرض کنید سلولی که فعاله A5 باشه و ما تا ستون Z اطلاعات داشته باشیم. حالا اگر از CTRL و SHIFT و جهت چپ به صورت همزمان استفاده کنیم سلولهای A5 تا Z5 انتخاب میشود.
28. CTRL+SHIFT+%: این کلید ترکیبی فرمت سلولهای انتخابی رو به فرمت درصد تغییر میدهد.
29. CTRL+0: با استفاده از این کلید میانبر اکسل، ستون یا ستون های انتخاب شده مخفی و ظاهر میشوند.
30. CTRL+9: با استفاده از این کلید میانبر، سطر یا سطرهای انتخاب شده مخفی و ظاهر میشوند.

 

آشنایی با نحوه رسم نمودار در اکسل

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

آماده سازی داده ها

برای رسم نمودار در اکسل ابتدا باید داده های مربوط به اون نمودار رو در اختیار داشته باشیم. در نرم افزار اکسل باتوجه به اینکه بخوایهم چه نوع نموداری رو رسم کنیم، جدولی که برای رسم نمودار نیاز داریم متفاوت است. فرمت جدول و نحوه قرارگیری داده های موردنیاز برای رسم انواع مختلف نمودار به شرح زیر است                                                                                                                                            نمودارهای میله ای، ستونی، خطی، مساحتی یا area، surface و رادار:                                                                                                                                                                                          نمودارهای pie و doughnut :                                                                                                                                                                                                                                            نمودار حبابی و XY:                                                                                                                                                                                                                                                      نمودار stock:                                                                                                                                                                                                                                                                      رسم نمودار:                                                                                                                           

برای رسم نمودار از داده های جدول بالا ابتدا تمام داده ها رو انتخاب میکنیم و بعد از تب Insert و بخش chart، روی نوع نمودار مدنظرمون (در اینجا نمودار میله ای) کلیک میکنیم.

 

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

 

اعمال تغییرات بعد از رسم نمودار در اکسل

بعد از اینکه نمودار را  رسم کردیم شاید لازم باشد تا تغییراتی روی نمودارما ن اعمال کنیم تا ظاهر نمودار مطابق با خواسته ما باشد.  برای اینکار ابتدا نموداری که رسم کردیم را انتخاب میکنیم. با اینکار دو تب Design و Format  نمایش داده میشود.

 

تب Design:

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

Add Chart Element:

 

با استفاده از این بخش میتوانیم اجزای مختلفی مثل خط روند، خطوط راهنما و … را به نمودار  اضافه کنیم. گزینه های این بخش شامل موارد زیر است:

Axes: از این گزینه برای نمایش یا عدم نمایش محورهای مختصات استفاده میشود.

Axis Titles: از این گزینه برای نمایش یا عدم نمایش عنوان محورهای مختصات استفاده میشود.

Chart Title: از این گزینه برای نمایش یا عدم نمایش عنوان نمو دار استفاده میشود.

Data Labels: با استفاده از این گزینه میتونیم مقادیر عددی هر نقطه از نمودار رو روی نمودار نشون دهیم.

Data Table: از این گزینه برای نمایش جدولی داده های نمودار در پایین نمودار استفاده میشود.

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

Gridlines: این گزینه برای ترسیم خطوط راهنما در پس زمینه نمودار استفاده میشود.

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

 

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

Trendline: از این گزینه برای رسم انواع خطوط روند استفاده میشود. خطوط روند میتونند برای پیش بینی مقادیر آینده استفاده شوند.

Up/Down Bars: اگر نمودار شما دارای دو سری باشد، با انتخاب این گزینه خطوطی بین نقاط متناظر دو نمودار رسم میشود  که فاصله بین اونها رو نشون میدهد.

Quick Layout:

 

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

Chart Styles:

 

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

Switch Row/Column:

 

اگر از این گزینه استفاده کنیم، جای سری ها و گروه های ما تغییر خواهند کرد. برای اینکه درک مفهوم راحت تر شود نموداری که ایجاد کردیم رو در نظر بگیرید:

 

Select Data:

 

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

Change Chart Type:

با استفاده از این گزینه میتوانیم نوع نمودار رو تغییر دهیم. به عنوان مثال نمودارمون رو از میله ای به خطی و … تغییر دهیم.

Move Chart:

 

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

 

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

تب Format:

از گزینه های موجود در این تب برای اعمال تغییرات ظاهری جزئی تر در نمودار استفاده میشود. این گزینه ها به شرح زیراست:

بخش current selection:

 

در این بخش میتوانیم تنظیمات مربوط به بخش های مختلف نمودار رو تغییردهیم. در قسمت اول بخش موردنظر را  انتخاب میکنیم. به عنوان مثال با انتخاب گزینه Chart Title در بخش اول، میتوانیم روی عنوان نمودارتغییرات موردنظرمان را اعمال کنیم.

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

 

Reset to Match Style: با انتخاب این گزینه تمام تغییرات اعمال شده روی بخشی که انتخاب کردیم کنسل میشه و تمام تنظیمات و قالب های بخش مورد نظر ر به حالت پیش فرض برمیگردد.

Insert Shapes:

 

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

Shape Styles:

 

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

WordArt Styles:

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

Arrange:

با استفاده از گزینه های این بخش میتاونیم layout نمودار را تغییردهیم.

گزینه های Bring Forward و Send Backward: با استفاده از این گزینه ها میتوانیم نمودار را بالا یا پایین یک شی دیگه قرار دهیم.

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

Group: از این گزینه برای گروه کردن چند نمودار میتوانیم استفاده کنیم. زمانی که چند نمودار یا تصویر را با هم گروه میکنیم میتوانیم نمودار ها  به صورت همزمان با هم جابجا کنیم یا تغییر اندازه دهیم.

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

Size: 

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

نحوه ایجاد Table در اکسل و مزایای آن

                                                                                                                                        در حالت عادی ما اطلاعات خود را در سلول های موجود در هر شیت وارد میکنیم. زمانی که ما حجم اطلاعات زیادی داریم که به شکل یک جدول هستند، یعنی سرستون های مشخصی دارند و در سطرهای بعدی اطلاعات ثبت میشوند شاید بهتر باشد که محدوده سلول های خودرا به یک Table تبدیل کنیم.

نحوه تبدیل یک محدوده به Table در اکسل:

تبدیل یک محدوده به Table کار بسیار ساده ای است.  کافیست محدوده موردنظر را انتخاب کنید.

بعد از تب Home روی گزینه Format as Table کلیک کنید و یکی از قالب های مدنظرتون رو انتخاب کنید. راه دیگر این است  که پس از انتخاب محدوده موردنظر کلید ترکیبی CTRL+T رو فشار بدید.

با اینکار پنجره مربوط به ایجاد Table باز میشه.

در قسمت اول میتوانید در صورت نیاز محدوده ای که قراراست  به Table تبدیل شود را تغییر دهید. در صورتی که جدول شما دارای سرستون است  گزینه My table has headers را فعال کنید. در غیر اینصورت این گزینه را غیرفعال کنید تا اکسل به صورت خودکار برای جدول شما سرستون ایجاد کند .

در نهایت با کلیک روی دکمه OK محدوده شما به تبدیل به Table میشه و به شکل زیر در میاد

نحوه تبدیل Table به محدوده عادی در اکسل:

در صورتی که بخوایم Table رو به یک محدوده عادی تبدیل کنیم باید ابتدا روی یکی از سلول‌های Table کلیک کنیم. بعد  از قسمت رویبون روی تب Design کلیک کنیم و گزینه Convert to Range رو کلیک کنیم.

 

مزایای استفاده از Table در اکسل:

تبدیل یک محدوده عادی به Table مزایای زیادی داره که در زیر اونها رو با هم مرور میکنیم.

1.مرتب سازی و فیلتر کردن راحت

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

2.امکان قالب بندی سریع و راحت

وقتی که اطلاعات خودرا را تبدیل به Table میکنیم به  راحتی میتوانیم فرمت و رنگ آنها را تغییر دهیم. برای اینکار کافیست  از تب Home روی گزینه Format As Table کلیک کنیم و یکی از قالب هارا   انتخاب کنیم. همینطور میتوانیم از قالب‌هایی که رنگ آنها خط در میان تغییر میکند برای خوانایی بهتر اطلاعات استفاده کنیم.

3.اضافه شدن خودکار اطلاعات وارد شده به جدول

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

4.تعریف NameRange و خوانایی بهتر در فرمول نویسی

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

محدوده عادی:

5.قابلیت اضافه کردن جمع کل، میانگین و … در انتهای ستون

یکی دیگر  ازمزیت های Tableدر اکسل این هست که میتوانید جمع، میانگین و … مربوط به هر ستون رو در انتهای هر ستون نمایش بدید. برای اینکار ابتدا یکی از سلول های Table رو انتخاب کنید.

با اینکار ستونی مثل تصویر زیر به انتهای جدولتون اضافه میشه

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

به عنوان مثال میتوانید برای نمایش حاصل جمع کل اعداد ستون از Sum، برای نمایش میانگین از Average و … استفاده کنید. در صورتی که بخواید از توابعی غیر از توابع موجود در این لیست استفاده کنید کافیه گزینه More Functions رو انتخاب کنید و در پنجره ای که باز میشه تابع مدنظرتون رو انتخاب کنید.

6.ثابت ماندن سرستون ها در هنگام اسکرول کردن

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

7.آپدیت خودکار فرمول‌ها

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

8.آپدیت خودکار نمودارها با اضافه شدن اطلاعات جدید به Table

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

9.قابلیت انتخاب تمام جدول با یک کلیک

برای انتخاب کل سلول های یک Table کافیه نشانگر ماوس خودتون رو به گوشه راست یا چپ جدول ببرید(بسته به تنظیمات جهت شیت) و کلیک کنید. با اینکار تمام Table شما انتخاب خواهد شد.

اکسل 2016 چه حرف تازه‌ای برای گفتن دارد؟

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

ویژگی‌های جدید

1-شش چارت جدید
مجازی‌سازی و ترسیم دقیق اطلاعات و آمار باعث می‌شود تحلیل مناسبی از داده‌ها به دست آید. در اکسل 2016، شش چارت جدید به‌صورت تعاملی با نرم‌افزارهای دیگر در خدمت کاربران قرار دارند. این چارت‌ها به شیوه بهینه‌سازی و کارآمد توانایی ارائه تصویر درست از آمارها را دارند. برای دسترسی به این چارت‌ها ابتدا به زبانه Insert بروید. در بخش Charts، نماد مربوط به این زبانه‌ها را مشاهده خواهید کرد . روش دیگری که دسترسی آسان به این چارت‌ها را امکان‌پذیر می‌سازد، با استفاده از گزینه Recommended Charts و انتخاب گزینه All Charts است. 

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

به‌طور مثال، اگر چارت Stacked Column را در پنجره ظاهر شده انتخاب کنید، داده‌های انتخابی در دو ردیف اکسل به شیوه مناسبی سازمان‌دهی و به کاربر نشان داده می‌شوند (شکل 3).

2-محاوره‌های کارآمد
قبل از آن‌که توانایی تجزیه و تحلیل داده‌ها را داشته باشید، ابتدا باید بتوانید به داده‌‌های تجاری خود دسترسی داشته باشید. اکسل 2016 همراه با یک سری از وظایف از پیش آماده در اختیار کاربران قرار گرفته است تا سرعت دسترسی و تبدیل داده‌ها افزایش یابد. این ابزارها به شما اجازه می‌دهند تا اطلاعات‌ شما همواره در هر مکانی که به آن‌ها نیاز دارید، در اختیار شما قرار داشته باشند. این قابلیت‌ جدید در گذشته تنها به‌صورت یک افزونه جداگانه و پیش ساخته به‌نام Power Query در اختیار کاربران قرار داشت، به‌ طوری که کاربران باید آن‌ را جداگانه دریافت می‌کردند. اکسل 2016 همراه با ویژگی جدیدی به‌نام Get & Transform  عرضه شده است. این ویژگی در زبانه Data و در قالب گزینه New Query در اختیار کاربران قرار دارد. ویژگی جدید به گونه‌ای در نظر گرفته شده است که استخراج داده‌ها از منابع مختلفی همچون فایل‌ها (Wrokbook، سی‌اس‌وی، اکس‌ام‌ال و متن)، بانک اطلاعاتی (اکسس، اس‌کیوال، اوراکل، بانک اطلاعاتی آی‌بی‌ام، PostgreSQL ،MySQL ،Sybase و Teradata)، آژر، منابع دیگر (وب، اکتیو دایرکتوری، Handoop ،Exchange و...) و محاوره‌های ترکیبی به دست آید .

3-پیش‌بینی قدرتمندتر
در نسخه‌های قدیمی اکسل، تنها پیش‌بینی خطی یک متغیر بر اساس مقادیر قبلی آن متغیر در دسترس بود، اما در اکسل 2016 تابع Forecast توسعه یافته و توانایی پیش‌بینی بر مبنای Exponential Smoothing (از قبیلFORECASE.EST ) را دارد. این تابع تنها با یک کلیک ساده در دسترس کاربران قرار دارد. برای این منظور به زبانه Data بروید و در گروه Forecast گزینه Forecast sheet را انتخاب کنید. با انتخاب این گزینه مجازی‌سازی قدرتمندی از مجموعه داده‌ها ساخته می‌شود .

4-نقشه‌های سه‌بعدی
ابزار مجازی‌ساز سه‌بعدی جغرافیایی Power Map اکنون تغییر نام داده است و در اکسل 2016 به‌صورت پیش ساخته در اختیار مصرف‌کنندگان قرار دارد. ابزار جدید اکنون 3D Maps نامیده شده است و همراه با دیگر ابزارهای مجازی‌ساز در زبانه Insert در کنار دیگر ابزارهای تجسم‌کننده قرار دارد .

5-بهبود عملکرد PivotTable
اکسل همواره به دلیل انعطاف‌پذیری و ابزارهای قدرتمند تحلیلی آن شناخته شده و نزد کاربران محبوب است. PivotTable یکی از این موارد است. این تجربه تحلیل قدرتمند همراه با اکسل 2010 و 2013 با معرفی ویژگی PivotTable و Data Model در اختیار کاربران قرار گرفت. این ابزارها این توانایی را دارند تا در سریع‌ترین زمان ممکن مدل‌های پیچیده‌ای از داده‌ها را تولید کنند و در سریع‌ترین زمان ممکن به محاسبه میلیون‌ها رکورد بپردازند. اما اکسل 2016 باز هم بهره‌وری و کارایی PivotTable را بهبود بخشیده است. در اکسل 2016 به‌جای آن‌که تمرکز بیش‌تری بر مدیریت داده‌ها داشته باشید، می‌توانید بر تحلیل دقیق‌تر داده‌ها متمرکز شوید. از جمله ویژگی‌های جدید اضافه شده به اکسل به موارد زیر می‌توان اشاره کرد:

Automatic relationship detection
اکسل 2016 می‌داند شما چه زمانی نیازمند تحلیل دو یا چند جدول هستید، به ‌طوری که این جداول را به یکدیگر پیوند می‌دهد و شما را از این موضوع آگاه می‌سازد. در نتیجه، دیگر به کشف و ساخت ارتباط میان جداول مورد استفاده برای مدل‌های داده‌ای Workbook نیاز نخواهد بود. 

Automatic time grouping
این ویژگی به شما کمک می‌کند، فیلد‌های مبتنی بر زمان (سال و ماه) را برای شناسایی خودکار و گروه‌بندی آن‌ها استفاده کنید. زمانی ‌که آن‌ها را با یکدیگر گروه‌بندی می‌کنید، به‌سادگی با کشیدن و رها کردن آن‌ها به PivotTable همه آن‌‌ها در تحلیل‌ها در دسترس خواهند بود. 

Creating, editing and deleting custom measures
ساخت، ویرایش و شناسایی مقیاس‌ها اکنون به‌طور مستقیم از درون فهرست فیلدهای PivotTable در دسترس کاربران قرار دارد. این ویژگی باعث می‌شود تا به میزان قابل توجهی در زمان صرفه‌‌جویی شود، به دلیل این‌که دیگر نیازی به زمان برای تحلیل‌ها وجود ندارد.

PivotChart drill-down buttons
به شما اجازه بزرگ‌نمایی و کوچک‌نمایی در ساختارهای سلسله مراتبی درون داده‌ها را می‌دهد.

Smart rename
ویژگی Smart rename به شما این توانایی را می‌دهد تا نام جداول و ستون‌ها را به مدل داده‌ای Workbook تغییر دهید. با هر تغییر اکسل 2016، جداول مرتبط و محاسبات روی Workbook را به‌طور خودکار به‌روزرسانی می‌کند. این به‌روزرسانی شامل همه فرمول‌های DAX و تمام برگه‌ها می‌شود (شکل 7).

6-انتشار تحلیل‌های خود با استفاده از Power BI
یک گزارش زمانی به‌عنوان گزارشی کامل شناخته می‌شود که با افراد واجد شرایط به اشتراک گذاشته شده باشد. زمانی‌ که تحلیل‌های داده‌ای خود را کامل کردید، می‌توانید با استفاده از ابزار Power BI این گزارش را تنها با یک کلیک ساده با همکاران و گروه‌های کاری به اشتراک بگذارید. وقتی گزارشی با Power BI به اشتراک گذاشته شد، می‌توانید از مدل‌های داده‌ای برای ساخت سریع گزارش‌ها و داشبورد‌های تعاملی استفاده کنید .

7-فرمت‌بندی سریع شکل‌ها
این ویژگی تعداد سبک‌های پیش‌فرض را با معرفی سبک Preset در اکسل افزایش می‌دهد (شکل 9).

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

Tell me
ویژگی Tell me در نوار ریبون اکسل 2016 کمک می‌کند تا در سریع‌ترین زمان ممکن به فرامین مورد نیاز خود دسترسی پیدا کنید. ما عملکرد این کادر متنی را در مقاله‌های قبلی مورد بررسی قرار دادیم.

Insights
Smooth Lookup به دلیل این‌که ویژگی سراسری به شمار می‌رود، در نتیجه در همه نرم‌افزارهای مجموعه آفیس در اختیار کاربران قرار دارد. ارائه محتوای غنی و ساخت یافته به شما کمک می‌کند در کم‌ترین زمان ممکن محتوای مورد نیاز خود را در پانل Insights مشاهده کنید. 

Ink Equations
همان ‌گونه که پیش‌تر اشاره کردیم، Equations برای نوشتن دقیق‌تر معادلات ریاضی مورد استفاده قرار می‌گیرد. اگر از یک دستگاه لمسی یا قلم لمسی استفاده می‌کنید، به‌راحتی توانایی نوشتن معادلات ریاضی را دارید. اکسل این توانایی را دارد تا معادلات نوشته شده را به متن تبدیل کند. البته اگر از یک دستگاه لمسی استفاده نمی‌کنید، ماوس راه‌حل دیگری در این زمینه به شمار می‌رود.

9-به اشتراک‌گذاری ساده
با کلیک روی دکمه Share، به‌سادگی توانایی به اشتراک‌گذاری صفحات گسترده را در شیرپوینت، وان‌درایو یا وان‌درایو ویژه کسب‌وکارها خواهید داشت. 

10-مجهز شده به ویژگی DLP
حفاظت از داده‌ها ویژگی ارزشمند سازمانی به شمار می‌رود. اکنون اکسل 2016 به ویژگی DLP (سرنام Data Loss Protection) تجهیز شده است. این ویژگی به گونه‌ای عمل می‌کند که یک اسکن بی‌درنگ را بر مبنای محتوا و مجموعه سیاست‌های از پیش تعریف شده روی داده‌های حساس (از قبیل شماره کارت‌های اعتباری، شماره تأمین اجتماعی و شماره حساب‌های مربوط به ایالات متحده) اجرا می‌کند. در نتیجه، دیگر نگرانی از بابت از دست رفتن داده‌ها نخواهید داشت.

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

مطمئنا شما نیز پس از نصب بسته نرم افزاری آفیس ، با نرم افزار Microsoft Office Excel برخورد داشته اید. اکسل نرم افزاری قدرتمند برای محاسبه ، مدیریت و تحلیل داده ها می باشد. بسیاری از کاربران عملیات آماری و اعدادی خود را با این نرم افزار انجام میدهند. اکنون قصد داریم به معرفی 3 ترفند بسیار کاربردی در اکسل بپردازیم. این ترفندها عبارتند از "کشیدن چارت در چند ثانیه" ، "کپی کردن Chart Formats" و "لینک کردن Autoshape و Textbox با اطلاعات داخل سلول". یقینا استفاده از این ترفند میتواند کار با اکسل را برای شما آسان تر و بهینه تر نماید:


کشیدن چارت در اکسل در چند ثانیه:
در واقع در دو مرحله بسیار ساده شما میتوانید به سرعت یک چارت در اکسل بکشید:
1- یک سلول را در میان جدولی که میخواهید اطلاعات آن به شکل چارت رسم شود انتخاب کنید.
2- کلید F11 را فشار دهید.
چارت شما آماده است ! اکسل چارت شما را در یک Chart Sheet جدید ایجاد میکند. شما میتوانید سایر تنظیمات را طبق روش معمول انجام دهید.

کپی کردن Chart Formats در اکسل:
همانگونه که میدانید تنظیمات مربوط به فرمت چارتها در اکسل بسیار وقت گیر و گاه کسل کننده است. آیا تا به حال چارتی را در اکسل ایجاد کرده اید که به نظرتان فرمت مناسب و ایده آلی داشته باشد و دوست داشته باشید سایر چارتهایی که ایجاد میکنید یا قبلا کشیده اید همین فرمت را داشته باشند؟ در این ترفند روش بسیار ساده ای را برای کپی کردن Chart Formats حتی بین فایلهای مختلف معرفی خواهیم کرد.
1- چارتی که  فرمت دلخواهتان را دارد انتخاب (Select) کنید.
2- با فشردن دو کلید Ctrl+C چارت را کپی کنید.
3- چارت مقصد که میخواهید فرمت آن را تغییر دهید را انتخاب (Select) کنید.
4- از منوی Edit بالای صفحه گزینه Paste Special را انتخاب کنید.
5- در پنجره ظاهر شده گزینه Formats را انتخاب کرده و Ok کنید. به همین سادگی کلیه فرمتهای چارت شما کپی شد.

لینک کردن Autoshape و Textboxبا اطلاعات داخل سلول در اکسل:
آیا تا به حال به مرتبط کردن یک Text Box یا یک Autoshape به یک سلول نیاز پیدا کرده اید؟ با این ترفند شما می توانید اطلاعات یک سلول را به یک Text Box یا یک Autoshape بصورت لینک منتقل کنید.
1- اولا یک Text Box یا یک Autoshape رسم کنید.
2- حالا Text Box یا Autoshape ایجاد شده را انتخاب (Select) کنید.
3- روی Formula Bar (نوار بالای صفحه که فرمولها در آن نمایش داده میشوند) کلیک کنید و علامت = را تایپ کنید.
4- حال بر روی سلول مورد نظرتان کلیک کنید و کلید Enter را فشار دهید. همانطور که مشاهده می کنید اطلاعات سلول مورد نظرتان به Text Box یا Autoshape بصورت لینک منتقل شده است. حالا میتوانید از روشهای معمول Format دلخواهتان را به Text Box یا Autoshape بدهید.

 

معرفی اکسل و کاربردهای آن

معرفی نرم افزار اکسل

نرم افزار اکسل از جمله کاربردی ترین نرم افزارهای Microsoft Office می باشد، اکسل یک نرم افزار صفحه گسترده (Spread Sheet) است، به این معنی که در واقع اکسل جدولی بسیار بزرگ است که کاربر می تواند بنا به نیاز خود برای هر سلول از این جدول داده تعریف کند، فرمول نویسی کند، ظاهر هر سلول را به دلخواه تغییر دهد و یا محدودیت های دسترسی برای هر سلول تعریف کند.  آموزش اکسل به منظو ر قابلیت های بیشمار نرم افزار اکسل و  استفاده از آنها، بکارگیری این نرم افزار را در حوزه ی کارهای مهندسی، اداری، مالی و … آموزش اکسل را  به امری اجتناب ناپذیر تبدیل نموده است.

کاربرد ها ی نرم افزار اکسل

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

 

وب سایت آموزش اکسل

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

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

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