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

شاید برای شما نیز این مورد پیش آمده باشد که بخواهید در اکسل اطلاعات لیست شده را پس از مدتی بازبینی و تصحیح نموده و پس از این کار ردیف مورد نظر در اکسل را از مابقی ردیف‌ها با استفاده از رنگ‌ها متمایز کنید. البته این کار با استفاده از انتخاب سل‌های مورد نظر و استفاده از Fill Color در Home > Font در اکسل 2007 به راحتی انجام می‌شود. ولی نکته اینجاست که چنانچه سل‌های مورد نظر زیاد باشد و نیاز به وقت زیاد برای این کار باشد تکلیف چیست؟ راهکار ساده‌ای در اکسل با استفاده از قابلیت Conditional Formattingوجود دارد
با این آموزش اکسل همراه باشید


محافظت سلولها در برابر تغییرات در اکسل
1- قفل کردن یا از حالت قفل خارج کردن (lock / unlock) سلولهای مورد نظردر شیت

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


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

به مسیر format cell>protection>lock بروید

تب lock * را تیک بزنید.

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


2- انجام عملیات محافظت شیت (Protect Sheet)
در excel 2007 برای اینکار باید ابتدا به منوی review بروید.(در اکسل 2003 به منوی tools بروید) protect sheet را انتخاب کنید

برای اینکه بعدا بتوانید از حالت protect sheet(unprotect)خارج شوید می توانید پسورد بگذارید
گزینه هایی در زیر عبارت Allow all user of this worksheet وجود دارد که عملیات را درباره سلولهای قفل شده نمایش داده می شود و می توانید آنها را هم با توجه به سلولهای قفل شده یا نشده انتخاب کنید و کاربر را محدود کنید.

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

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


به مسیر review>protection> allow users to edit range می رویم.(در excel 2003 اینکار از منوی tools امکانپذیر است)

با استفاده از گرینه new مناطقی را که میخواهیم اضافه می کنیم

و درنهایت password *میدهیم

هنوز کار تمام نشده و مثل حالت قبلی باید sheet * را محافظت کنیم.

برای دسترسی به این سلول از ما پسورد خواسته می شود.

محافظت فایلهای اکسل workbook می توانید review /protection/protect workbook (در اکسل 2003 tools/protection/protect را انتخاب کنید تا از فایلهای اکسل نیز محافظت کنید


محافظت از یک workbook با استفاده از کلمات عبور
وقتی که protect workbook, protect sheetیاprotect and share workbook را انتخاب می کنید یک کلمه عبور برای غیرفعال کردن محافظت(unprotect) باید استفاده شود. می توانید از تنها ازی یک کلمه عبور برای هر workbook یا worksheet که محافظت کرده اید استفاده کنید.
قفل کردن یک workbook در 2 حالت structure و window انجام می گیرد.فروش درب ضد سرقت (http://****************)




در حالت structure قابلیتهای زیر(درو اقعی بیشتر عملیات کار با شیت) از یک شیت گرفته می شود:



جابجا کردن sheet
تغییر نام و رنگ تب sheet
حذف و اضافه کردن sheet
hide و unhide کردنsheet 

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

نوار ابزار Formula Auditing را باز کنید  

 وقتی که خطاها رخ می دهند، یکی از قدرتمند ترین ابزارها برای اصلاح آنها، نوار ابزار Formula Auditing است. برای باز کردن آن، از منوی Tools، گزینه Formula Auditing و بعد گزینه Show Formula Auditing Toolbar را انتخاب کنید.

خطاها را مرور کنید 

 برای بررسی هر سلولی که با مثلث خطا مشخص شده است، روی دکمه Error Checking در نوار ابزار Formula Auditing کلیک کنید،Excel اولین سلول خطا را متمایز می کند و گزینه هایی برای اصلاح آنها در اختیار شما قرار می دهد.

خطا را اصل

 در اینجا، Excel متوجه شده است که یک سلول از فرمولی استفاده می کند که با فرمول‌های سلول‌های پیرامون، متفاوت است. این ممکن است یک خطا باشد یا نباشد. اگر بخواهید می توانید به Excel بگویید که این خطا را نادیده بگیرد. برای این کار، روی دکمه Ignore Error کلیک کنید. ولی، این یک خطاست. بنابراین روی Copy Formula from Above کلیک کنید. با کلیک کردن روی Edit in Formula Bar خودتان می توانید فرمول را تغییر دهید.

 خطاهای بعدی را نیز اصلاح کنید 

 Excel، سلول خطای بعدی را متمایز می کند. گزینه مورد نظر را از کادر محاوره ای Error Checking انتخاب کنید. آنقدر ادامه دهید تا تمام خطاها را مشاهده کنید. وقتی که Excel خطاها را بررسی می کند، یک پیغام نشان می دهد. روی Ok کلیک کنید.

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

تمام اشتباهات، منجر به خطا نمی شوند. اگر چیزی درست به نظر نرسد، (مانند میانگین فروش) می توانید با نمایش دادن آدرس های مرجوعی فرمول، مقادیر مورد استفاده در آن را ردیابی (دنبال) کنید. روی سلولی که می خواهید ارزیابی کنید کلیک کنید و بعد روی دکمه Trace Precedents کلیک کنید. اگر لازم باشد، دوباره روی این دکمه کلیک کنید تا مقدار این سلول ها را دنبال کنید

 فلش‌های ردیابی را نمایش می دهد که به منبع های فرمول اشاره می کنند و به شما کمک می کنند تا خطا را دنبال کنید. در اینجا، من اشتباهاً جمع کل در ستون G را در محدوده هایی که در تابع AVERAGE بکار بردم وارد کردم! بعد از اینکه خطا را پیدا کردید، اصلاحات لازم را انجام دهید و با کلیک کردن روی Remove All Arrows، فلش های ردیابی را حذف کنید.

*توجه*

 استفاده از Trace Error

اگر یک مثلث سبزرنگ، وجود خطا در فرمولی که تایپ کرده اید را نشان دهد، با بردن اشاره گر ماوس به سمت چپ سلول و متمایز کردن دکمه Trace Error، کلیک کردن روی فلشی که ظاهر می شود و انتخاب یکی از گزینه ها می توانید فوراً آن را بررسی کنید.

ردیابی وابسته ها

با کلیک کردن روی یک سلول و کلیک کردن روی دکمه Trace Dependents درنوار ابزار Formula Auditing می توانید بررسی کنید که آیا مقدار آن سلول در یکی از فرمول ها استفاده شده است یا خیر و چگونه.

ارزیابی فرمول

برای ارزیابی یک فرمول، روی دکمه Evaluate Formula در نوار ابزار Formula Auditing کلیک کنید تا یک کادر محاوره ای باز شود که شما را در تک تک قسمت های فرمول جلو می برد. برای محاسبه قسمت خط کشی شده فرمول، روی Evaluate کلیک کنید. هر اندازه که لازم است روی Evaluate کلیک کنید.

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

روش اول) انتقال و یا ذخیره فایل اکسل حاوی کد ماکرو در پوشه XLStart

روش دوم) اضافه کردن فایل حاوی کد ماکرو به عنوان Add-in به اکسل

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

البته باید به این نکته دقت کنید که فایل‌های اکسل حاوی کدهای ماکرو را نمی‌توان مشابه فایل‌های معمولی اکسل ذخیره کرد بلکه باید آن‌ها را در قالب Macro-Enabled ذخیره نمود.

اما پوشه XLStart در کجا قرار دارد؟

در نسخه‌های مختلف ویندوز مسیر پوشه XLStart متفاوت است:

ویندوز XP

C:\Documents andSettings\user name>\Application Data\Microsoft\Excel\XLStart

ویندوز Vista

C:\Users\user name>\AppData\Local\Microsoft\Excel\XLStart

ویندوز 7، 8 و 10   

C:\Users\user name>\AppData\Roaming\Microsoft\Excel\XLSTART

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

روش الف: در پائین پنجره Microsoft Visual Basic در بخش Immediate، عبارت زیر را کپی کنید. اگر این بخش قابل مشاهده نیست کلیدهای Ctrl + G را بفشارید تا بخش پنجره Immediate فورا ظاهر شود.

? application.StartupPath

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

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

%APPDATA%\Microsoft\Excel\XLSTART

با باز شدن این پوشه می‌توانید آدرس آنرا در نوار آدرس مشاهده کنید.

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

البته اگر قصد دارید یک ماکرو را با استفاده از قابلیت ضبط ماکرو (مراجعه به تب View، قسمت Macros و انتخاب گزینه Record Macro) ایجاد کنید در این حالت می‌توانید در همان ابتدا و قبل از ساخت ماکرو، مسیر ذخیره آن‌را پوشه XLStart قرار داد تا پس از ذخیره کردن به صورت خودکار در این پوشه قرار گیرد.

برای اینکار پس از انتخاب گزینه Record Macro، پنجره Record Macro  ظاهر می‌شود. پس از وارد کردن نام و کلید میانبر، برای اینکه فایل اکسل در پوشه XLStart ذخیره شود در قسمت Store macro in گزینه Personal Macro Workbook را انتخاب و در پایان روی دکمه OK کلیک کنید.

با انتخاب گزینه Personal Macro Workbook، اکسل  فایل مخفی (hidden) که حاوی این ماکرو است ایجاد می‌کند. بدین ترتیب هر زمان که شما اکسل را باز کنید این ماکرو نیز قابلیت اجرا دارد.
نکته: البته با توجه به اینکه این ماکرو در یک فایل مخفی است برای اینکه بتوانید هر گونه تغییری در آن ایجاد کنید یا آنرا حذف کنید لازم است در ابتدا آنرا از حالت hiden خارج کنید. اینکار از مسیر View>Unhide امکان‌پذیر است. بعد از انجام تغییرات باید آنرا از همان مسیر Hide نمایید.

پس از ذخیره فایل ماکروی ایجاد شده، این فایل با نام Personal.xlsb در پوشه XLStart قرار می‌گیرد. تمام فایل‌های اکسلی که در پوشه XLStart باشند بطور خودکار با باز شدن هر فایل اکسل باز می‌شوند.

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

روش دوم) اضافه کردن فایل حاوی کد ماکرو به عنوان Add-in به اکسل

برای اضافه کردن یک فایل حاوی کد ماکرو به عنوان Add-in به اکسل باید از فرمت xlam اکسل استفاده کرد. این فرمت بسیار شبیه به فرمت xlsm هست و برای تهیه Add-Inها در اکسل استفاده می‌شود. برای ذخیره فایل‌ حاوی ماکرو با فرمت xlam باید از لیست انواع پسوندهای ذخیره سازی (Save as type) گزینه Excel Add-In را انتخاب نمود.

پس از ذخیره فایل با پسوند xlam باید آن‌را در نرم افزار اکسل به صورت یک Add-In اضافه کرد. برای اینکار مثل تصویر زیر در پنجره تنظیمات اکسل وارد بخش Add-Ins شوید و در قسمت پایین پس از انتخاب گزینه Excel Add-Ins در بخش Manage روی دکمه Go کلیک کنید.

با انتخاب دکمه GO پنجره Add-Ins باز می‌شود. حال روی دکمه Browse کلیک کنید و فایل xlam مد نظرتان را انتخاب نموده و روی دکمه Ok کلیک کنید. با اینکار این فایل به عنوان یک Add-In به اکسل اضافه می‌شود و می‌توان از ماکروی آن در تمام فایل‌های اکسل استفاده کرد.

اکثر کاربران نرم‌افزار اکسل برای یک بار هم که شده با برازش منحنی برای داده‌های x و y یا Trend Line برخورد داشته‌اند. در واقع Trend Line به شما کمک می‌کند تا علاوه بر تشخیص روند تغییر داده‌ها، بتوانید تا حدودی وضعیت داده‌ها را پیش‌بینی (Forecasting) کنید. در ادامه مطلب با آموزش اکسل  امروز همراه شوید تا علاوه بر جزئیات Trend Line، با توابع کاربردی اکسل برای برازش منحنی نیز آشنا گردید.

از Trend Line فقط می‌توان در منحنی‌های Area،Bar،Column،Line و XY استفاده کرد.به خاطر داشته باشید که نمی‌توانید در نمودارهای 3D،Radar،Pie،Doughnut و Bubble از Trend Line استفاده کنید.

برای اضافه‌ کردن Trend Line، پس از راست کلیک کردن روی منحنی داده‌ها، گزینه‌ی Add Trendline را انتخاب کنید تا پنجره Format Trendline باز شود.

در قسمت راست پنجره Format Trendline یعنی قسمت Trendline Options، بخش‌های زیر وجود دارد:

بخش اول: بخش Trend/Regression Type انواع Trendline‌ها را نشان می‌دهد که به شرح زیر است:

   - Exponential / نمایی؛ با فرمول Y=C.e^bx که b و c اعداد ثابت هستند.

          * نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند، قابل استفاده نیست!

   - Linear / خطی؛ با فرمول Y=m.x+b که m شیب خط و b عدد ثابت (عرض از مبدا) است.

   - Logarithmic / لگاریتمی؛ با فرمول Y=c.Lnx+b که c و b اعداد ثابت هستند. 

          * نکته: هنگامی که داده‌های x شامل اعداد منفی یا صفر باشند، خطا ظاهر می‌شود!

   - Polynomial / چند جمله‌ای؛ با فرمول Y=b+c₁x+c₂x²+c₃x³+...+c_nxⁿ که در آن c عدد ثابت است.

   - Power / توانی؛ با فرمول Y=C.x^b که b و c اعداد ثابت هستند.

* نکته: هنگامی که داده‌های y شامل اعداد منفی یا صفر باشند قابل استفاده نیست!

   - Moving Average / میانگین متحرک؛ با فرمول F_t=(A_t+A_t-1+...+A_t-n+1)/n

بخش دوم، بخش TrendLine Name می‌باشد.

بخش سوم، بخش Forecast یا پیش‌بینی می‌باشد که بر اساس نوع معادلات انتخابی در بخش اول، yهای قبل و یا بعد متناظر با xهای داده شده را پیش بینی می‌کند.

Set Intercept هم برای تعیین عرض از مبداء دلخواه می‌باشد.

با تیک زدن دو گزینه آخر یعنی Display Equation on chart و Display R-squared value on chart، به ترتیب معادله و ضریب رگرسیون (R²) متناظر با نوع Trendline انتخاب شده، روی نمودار نمایش داده می‌شود. در رگرسیون خطی ضریب رگرسیون مجذور ضریب همبستگی (R) است.

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

1- معادله‌ای مناسب است که ضریب رگرسیون آن نزدیک به یک باشد مثلا 0.99.

2- اکسل، ضرایب معادله‌ را تا 2 رقم اعشار نمایش می‌دهد. برای اینکه بتوانید با استفاده از معادله، y متناظر با یک x را محاسبه کنید برای دقت بیشتر باید از معادله‌ای استفاده کنید که تعداد ارقام اعشاری بیشتری داشته باشد. راست کلیک کنید و گزینه Format trendline label را انتخاب کنید.

در پنجره باز شده زیر در قسمت Category گزینه Number را انتخاب و در قسمت Decimal places تعداد ارقام بعد از ممیز را افزایش دهید. دکمه Close را بزنید و از معادله جدید استفاده کنید.

علاوه بر استفاده گرافیکی از ابزار Trend Line، می‌توان از توابع اکسل نیز اطلاعات مفیدی بدون رسم نمودار به دست آورد.
1- تابع Slope: محاسبه شیب رگرسیون خطی.

=SLOPE(Known Y values, Known X values)

برای مثال زیر شیب خط تقریبا 2.15 می‌باشد.

=SLOPE(B2:B6,A2:A6) = 2.15

2- تابع Intercept: محاسبه عرض از مبدا رگرسیون خطی.که برای مثال بالا تقریبا 0.47- می باشد.

=INTERCEPT(Known Y values, Known X values)

=INTERCEPT(B2:B6,A2:A6) = -0.47

یعنی در واقع معادله رگرسیون خطی این مثال برابر است با:        y = 2.15*x -0.47

3- تابع Forecast: برای پیش‌بینی y متناظر با یک x جدید بر مبنای رگرسیون خطی.

=FORECAST(New X Value, Known Y values, Known X values)
=FORECAST(15,B2:B6,A2:A6) = 31.778

4- تابع GROWTH: برای پیش بینی y متناظر با یک x جدید بر مبنای رگرسیون نمائی.

=GROWTH(Known Y Values, Known X Values, New X Values, Const)
=GROWTH(B2:B6,A2:A6,15,TRUE) = 48.68

عبارت Const در تابع GROWTH، دارای دو حالت True (محاسبه b) و False (مقدار 1 برای b) می‌باشد.

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

1- تنظیم مجموعه داده ها

1- اولین ردیف (A1 1)  شامل سر ستونهاست

2- سپس داده را ذیل سر ستونها می نویسیم

3- هیچ ردیف خالی در بین داده ها وجود ندارد

4- ردیف خالی در انتهای مجموعه داده ها و ستون خالی نیز در انتهای ستون داده هاست                          2- تنظیم محدودهء شرط ها (اختیاری)

شما می توانید قاعده ای برای داده هائی که باید پس از اعمال فیلتراسیون قابل رویت باقی بمانند تنظیم کنید. همچنین قادر خواهید بود که  یک یا چند شرط  را اعمال کنید.

1- در این مثال سلولهای F1:F2  محدوده  شرط است که می تواند بیش از این تعداد نیز باشد

2- سر ستون F1  دقیقا با سر ستون D1  در مجموعه  داده ها یکی است

3- سلول F2  شامل شرط است. عملگر

پس از اعمال فیلتر پیشرفته تنها رکوردهایی با total>500 قابل رویت خواهند بود .

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

3- تنظیم محدوده استخراج شده بعد از اعمال فیلتر (اختیاری)

اگر هدف شما کپی کردن داده ها فیلتر شده در محل دیگریست، می توانید ستونهائی که قصد استخراجشان را دارید مشخص کنید

(اگر قصد استخراج همه ستونها را دارید این مرحله را به خود excel  واگذارید.)

1- سلول بالا و چپ را برای داده های استخراج شده انتخاب کنید

2- سرستونهای (فیلدهائی) که قصد استخراجشان را دارید تایپ کنید آنها دقیقا همان فیلدهائی باید باشند که در مجموعه داده ها وجود دارند.

4- اجرای فیلتراسیون پیشرفته اکسل

1- در مجموعه داده ها یک سلول را انتخاب کنید

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

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

4- پیشفرض اکسل بطور اتوماتیک نشان دادن لیست داده ها در همان sheet  یست که مجموعه داده ها وجود دارند وگرنه copy to anather lacation  را تیک کنید

5- محدوده شرط(یا شروط) را انتخاب کنید

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

7- OK  را کلیک کنید

فیلتر کردن داده ها بصورت منحصر بفرد (unique) در اکسل

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

توجه: لیست باید شامل سرستون باشد وگرنه ممکن است اولین آیتم بصورت دوبل ظاهر شود

1- یک سلول را در مجموعه داده ها انتخاب کنید

2- در ریبون data  مجموعهء sort &filter  ، tab ِ "advance filter" را به منظور باز شدن پنجره advance filter کلیک کنید

3- Copy to anather location  را انتخاب کنید

4- ستون (ستونها)ی را که می خواهید از آنها استخراج uniqe صورت گیرد انتخاب کنید

5- Criteria range  را خالی بگذارید

6- سلولی را برای شروع کپی در محلی دیگر انتخاب کنید

7- قسمت unique record only  را تیک کنید

8- OK  را کلیک کنید

استخراج داده ها در شیت‌ دیگر

اگر داده ها در برگه ای باشند شما می توانید در برگه ای دیگر داده های مورد نظرتان را زاآن اسنخراج کنید در این مثال داده ها در sheet1 و نتیجه در sheet2 کپی می شود.

1-    به sheet2  بروید

2-    سلولی را در بخش استفاده نشده برگه انتخاب کنید (در اینجا C4)

3-    Advance filter  را کلیک کنید

4-    Copy to anather location  را انتخاب کنید

5-    در جعبهء the list range کلیک کنید

6-     در  sheet1 محدوده داده ها را انتخاب کنید

7-    در بخش  criteria range  کلیک کنید (اختیاری)

8-    محدوده شرط را انتخاب کنید

9-    در باکس copy to  کلیک کنید

10-      درsheet2  یک سلول که می خواهید کپی داده ها از آنجا اغاز شود یا سرستونهائی که تایپ کرده اید را انتخاب کنید

11-      در صورت تمایل unique record only  نیز تیک کنید(اختیاری)

12-      OK  را کلیک کنید

چیدمان محدوده شرطها دراکسل

مقایسه گزینه AND و OR

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

در این مثال مشتری magamart  ، محصول cookies   و total  500

و اگر قصد استفادهء همزمان از عملگرهای AND  و OR  را دارید محدوده شروط را بشکل زیر تنظیم کنید

این شکل بیان گر این است که مشتری باید megamart  و محصول باید cookies  باشد یا 

محصول باید cookies  و total  باید 500

استفاده از wildcards

علامت (*)

جایگزین چند کاراکتر می شود در این مثال هر مشتری که نامش شامل "mart" باشد از فیلتر اکسل عبور می کند

علامت؟

که بجای یک کاراکتر می نشیند در این مثال همه محصولاتی که شامل c?oke  هستند از فیلتر عبور می کنند coke  یا  cake

علامت ~

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

ملاحظه می کنید که در اولی تمام آنچه که با good  شروع  و به  eats  ختم می شود از فیلتر عبور کرده اما در دومی eats  از فیلتر عبور کرده و نه  treats

مثالی برای فیلتر کردن فیلد تاریخ.

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

استخراج آیتم ها با متنی خاص

وقتی شما یک متن را در شرط قرار می دهید اکسل همه آیتم هایئ که شامل این متن است را از فیلتر عبور می دهد مثلا اگر شرط "Ice"  باشد  "ice milk" "Ice" "Ice cream"استخراج می شوند. برای پرهیز از چنین اتفاقی در صورت نیاز مانند شکل عمل کنید

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

1-از تب Data بر روی گزینه Filter کلیک نمایید.

2- بر روی علامت پیکان ستون مورد نظر کلیک نمایید. قصد داریم با فیلتر کردن ستون جزئیات تجهیزات تنها محصولات یک برند خاص را فیلتر کنیم.

3- عبارت برند مورد نظر خود را در کادر جستجو وارد نمایید. در این مثال قصد داریم محصولات برند Saris را فیلتر کنیم بنابراین عبارت Saris را در کادر جستجو تایپ و بر روی Ok کلیک نمایید.

فیلتر کردن پیشرفتهText دراکسل

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

1- از تب Data بر روی گزینه Filter کلیک نمایید.

2- بر روی علامت پیکان ستون مورد نظر کلیک نمایید. در این مثال قصد داریم با فیلتر کردن ستون جزئیات تجهیزات تنها نوعی از تجهیزات را نمایش دهیم.

3- از زیر منوی TextFilter گزینه Does Not Contain… را انتخاب نمایید.

5- در پنجره Custom AutoFilter عبارتcam را همانند تصویر زیر در قسمت مربوطه وارد و بر روی Ok کلیک نمایید. با انجام این کار اطلاعاتی که حاوی عبارت cam نیستند نمایش داده خواهد شد.

فیلتر کردن پیشرفته Number

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

1- از تب Data بر روی دکمه فرمان Filter کلیک نمایید.

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

3- از منوی Number Filters بر روی گزینه Between کلیک نمایید.

4- در این مثال قصد داریم محصولاتی که شناسه آنها بزرگتر از 3000 و کوچکتر از 4000 هست را فیلترنماییم پاعداد را وارد و بر روی Ok کلیک نمایید.

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

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

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

کدهای ویژوال بیسیک نوشته شده در فضای ماکرو نویسی اکسل می توانند اطلاعات بسیاری را با سلول های اکسل تبادل نمایند، و پارامترهای بسیاری را در مورد سلول های اکسل تعیین نمایند، در ادامه به برخی موارد استفاده از سلول اکسل در ماکرو نویسی اشاره شده است:

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

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

  سلول های اکسل

  برای اشاره به سلول اکسل در ماکرو نویسی ویژوال بیسیک می توان از شی Range Object) Range) و یا از پروپرتی Cells استفده نمود.

  استفاده از شی Range در اشاره به سلول های اکسل

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

  
  دقت کنید که عبارت قرار گرفته در درون پرانتز شی Range متغیری از نوع رشته می باشد.

  به صورت خلاصه تر هم می توان از شی Range برای اشاره به سلول ها استفاده نمود، برای اینکار نیازی به نوشتن کلمه ی Range و گذاشتن علامت “Quotation Mark” قبل و بعد از نام محدوده نیست،

  استفاده از پروپرتی Cells در اشاره به سلول های اکسل

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

  استفاده از پروپرتی های Row و Column در اشاره به سلول های اکسل

  از پروپرتی های Row و Column هم برای اشاره کردن به سلول های اکسل می توان استفاده نمود، به این تفاوت که این پروپرتی ها سطر ها و ستون ها را می توانند مورد اشاره قرار دهند و نه سلول ها را، در ادامه مثال هایی از کاربرد Row و Column برای اشاره به سلول های اکسل توسط کدهای ویژوال بیسیک آورده شده است.

  اشاره به یک محدوده ی نامگذاری شده در محیط اکسل توسط کدهای ویژوال بیسیک

  در مبحث نامگذاری محدوده ها در اکسل یاد گرفتیم که چگونه می توانیم برای یک یا چند سلول اکسل خود یک نام اختصاص دهیم، دانستیم که نامگذاری محدوده ها در اکسل فرمول نویسی ها را برای ما راحت تر نموده و فایل های اکسل ما را گویا تر می کند.

  در کدهای ویژوال بیسیک شما می توانید از نام های اختصاص داده شده برای سلول ها در اکسل برای اشاره به آن ها استفاده نمایید. برای این کار محدوده ای از اکسل به نام ExcelRange که در فایل اکسل MyWorkBook و در Sheet1 قرار دارد را در نظر بگیرید، کدهای زیر نحوه ی اشاره به این محدوده را نمایش می دهند و همگی یک کار را انجام می دهند.

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(1) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(2). اکسل 2013 شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید.

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

در صورتیکه بتوانید در کار کردن با این 11 ترفند اکسل مهارت پیدا کنید، به شما قول می دهیم جزء دسته دوم باشید.

 Vlookup  در اکسل

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

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

فرمول Vlookup را در منوی فرمول ها فرابخوانید و سلولی که شامل شماره ارجاع شماست (1) وارد کنید، سپس محدوده سلول های موجود در برگه یا کتاب کاری که لازم است داده ها را از آنها استخراج کنید، وارد نمایید (2) شماره ستون مربوط به نقطه داده ای که بدنبالش هستید را وارد کنید (3) و در صورتی که نزدیکترین آیتم به مرجع را می خواهید، True و در صورتی که آیتم دقیق را میخواهید، False را وارد نمائید.

ساخت چارت ها  در  اکسل

برای ساخت یک چارت، داده ها را همراه با هدرهای ستون(1) در اکسل وارد نمائید و Insert > Chart > Chart Type را انتنخاب نمائید(2).

اکسل  شامل بخش چارت های پیشنهادی (Recommended Charts) (3)همراه با طرح های مبتنی بر نوع داده هایست که با آن ها کار می کنید. پس از ساختن نسخه عمومی یک چارت، منوهای Chart Tools را برای اختصاصی کردن آن باز کنید. گزینه های جالبی را می بینید که می توانید برای کارتان از آن ها استفاده کنید.

IF and IF ERROR در اکسل 

IF and IFERROR دو نوع از مفیدترین فرمول های IF در اکسل هستند. IF به شما امکان می دهد از فرمول های شرطی استفاده کنید که از یک سو صحیح (true) بودن یک چیز خاص را محاسبه می کند و از سوی دیگر غلط (false) بودن آن چیز خاص را. مثلاً می توانید دانش آموزانی که امتیاز 80 یا بالاتر را در یک امتحان گرفته اند شناسایی کنید. به این ترتیب که اگر امتیاز درون ستون C بالای 80 باشد، سلول می باید ‘Pass’ را گزارش دهد و اگر امتیاز 79 یا زیر 79 باشد، باید «Fail» را گزارش نماید.

IFERROR نوع دیگری از فرمول استاندارد IF است و اگر فرمول مورد استفاده شما سعی در بازگرداندن خطا داشته باشد، به شما امکان می دهد تا یک مقدار خاص (یا مقدار خالی) را بازگردانید. مثلاً اگر Vlookup را در برگه یا جدول دیگری اجرا می کنید، فرمول IFERROR می تواند در هر نمونه که Vlookup قادر به پیدا کردن مرجع نیست، فیلد را خالی تحویل دهد.

 PivotTable

PivotTable یک جدول خلاصه یا summary است که به شما امکان شمارش، معدل گرفتن، جمع کردن و اجرای محاسبات دیگر را براساس نقاط ارجاعی که وارد کرده اید، می دهد. اکسل 2013، شامل Recommended Pivot Table است تا ساخت جدولی که داده های مورد نیاز شما را نمایش می دهد، آسانتر شود.

مثلاً، برای شمارش تعداد قبول شده ها و رد شده های یک آزمون در یک چارت، ستون Pass/Fail خود را در تب Row Labels و سپس دوباره در بخش Values از (PivotTable (2 خود قرار دهید. می توانید متغییرهای دیگر را از کادر کرکره ای Values انتخاب نمائید(3). همچنین می توانید جدول های فرعی بسازید که داده ها را به صورت طبقه بندی شده، مثلاً تعداد Pass/Fail براساس جنسیت خلاصه می کنند.

 PivotChart

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

اکسل 2013، شامل بخش Recommended PivotCharts است که در زیر آیکن Recommended Charts در قسمت Charts تب Insert لیست شده اند. با چرخاندن اشاره گر ماوس روی آن گزینه می توانید پیش نمایش یک چارت را ببینید. برای ساخت دستی PivotChart، آیکن PivotChart را در تب Insert انتخاب نمائید.

Flash Fill

Flash Fill بهترین ویژگی جدید اکسل2013 است که یکی از آزاردهنده ترین مشکلات این برنامه را حل می کند: چگونه بخشهایی از اطلاعات مورد نیاز خود را از یک سلول بهم پیوسته استخراح نماییم. مثلاً در گذشته، وقتی در ستونی با نام هایی در فرمت «Last ,First» کار می کردید، یا مجبور بودید همه چیز را به صورت دستی تایپ کنید یا یک workaround(پیچیده) بسازید.

فرض کنید که با فیلد نام اشخاص در اکسل 2013 کار می کنید. شما نام کوچک اولین شخص را در یک فیلد سریعا در کنار فیلدی که با آن کار می کنید (1)، تایپ می کنید و Home > Fill > Flash Fill را انتخاب می کنید. در پاسخ، اکسل به طور خودکار نام اول تمام افرادی که در فیلد موجود ثبت شده اند را استخراج می کند و فیلد جدید را با این نام ها پر می کند.

 Quick Analysis

ابزار جدید Quick Analysis اکسل 2013، مقدار زمان لازم جهت ساخت چارت های با مجموعه داده های ساده را کاهش می دهد. وقتی شما داده های خود را انتخاب می کنید، آیکنی در گوشه پائین راست صفحه ظاهر می شود (1)، آن آیکن را کلیک کنید تا منوی Quick Analysis ظاهر شود (2).

این منو ابزارهایی را برای Formatting، Charts، Totals، Tables و Sparklines ارائه می دهد. با چرخاندن اشاره گر ماوس روی هر گزینه، می توانید پیش نمایش از آن بسازید.

 Power View

Power view یک ابزار تعاملی کشف و تجسم سازی داده هاست که می تواند حجم بزرگی از داده ها را از فایل های داده ای خارجی، استخراج و تجزیه و تحلیل کند. در اکسل 2013، به Insert>Reports بروید(1) تا از Power View استفاده کنید (2).

گزارشات ساخته شده با Power View، آماده نمایش هستند همراه با مدهایی برای خواندن و نمایش تمام صفحه. شما می توانید حتی یک نسخه تعاملی را در PowerPoint صادر کنید. راهنمای آموزشی (tinyurl.com/nqyLvnl(BI در سایت مایکروسافت به شما کمک می کند تا در این زمینه یک کارشناس شوید.

 Conditional Formatting

عملکرد گسترده فرمتینگ شرطی اکسل، شناسایی نقاط داده ای مورد نظر را آسان می سازد. این ویژگی در تب Home در نوار وظیفه (1) قرار دارد. محدوده سلول هایی که میخواهید فرمت شوند را انتخاب نمائید و Conditional Formatting را کلیک نمائید (2). زیر منوی(Highlight Cells Rules (3 همانند یک میزبان برای ویژگی هایی که اغلب از آن ها استفاده می کنید، عمل می کند.

تبدیل ستون ها به سطرها (و بالعکس)

گاه ممکن است بخواهید داده ها را از فرمت ستون به فرمت سطر(یا بالعکس) تبدیل کنید. برای اینکار، سطر یا ستونی که میخواهید تبدیل در آن ها انجام دهید را کپی کنید، سلول مقصد را راست کلیک نمائید و Paste Special را انتخاب نمائید. سپس کادری با برچسب Transpose که در پائین پنجره ظاهر می شود را تیک بزنید و ok را کلیک نمائید. اکسل بقیه کارها را انجام می دهد.

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

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

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

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

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 و تمام برگه‌ها می‌شود

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

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

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

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

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

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

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

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

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

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

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

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

ساختار فرمول های اکسل

فرمول نویسی در اکسل دارای عناصر زیر است:

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

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

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

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

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

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

  • برای انتخاب داده ها در اکسل می توان از shift استفاده کرد.
  • برای کپی یک فرمول می توان دو بار بر روی گوشه سمت راست سلول کلیک کرد.
  • با استفاده از کلید F4 می توان یک سلول را قفل کرد.
  • با استفاده از علامت & می توان داده های متنی را با هم ترکیب کرد.
  • با استفاده از تابع RAND() می توان اعداد تصادفی بین صفر و یک ایجاد کرد و با فشردن کلید F9 می توان این عدد را تغییر داد.
  • با استفاده از توابع LEFT، RIGHT و LEN می توان داده های مورد نظر را پاک کرد.
  • با استفاده از داده CountIF می توان تعداد دفعات تکرار هر داده را مشخص کرد.
  • با استفاده از کلید Ctrl + می توان بین سلول ها جا به جا شد.
  • با زدن کلید Alt و = پس از سطر یا ستون می توان جمع داده ها را مشاهده کرد.
  • برای جا به جایی شیت ها می توان از کلید CTRL+PGUP و یا CTRL+PGDN استفاده کرد.
  • برای تنظیم فرمت اعداد می توان از CTRL+SHIFT به همراه اعداد یک تا پنج استفاده کرد.
  • هنگام کار با چند فایل به جای جداگانه باز کردن فایل ها می توان تمام فایل ها را انتخاب کرد و با زدن Enter تمام آن ها را به صورت همزمان باز کرد.
  • با دوبار کلیک می توان اسم صفحات را تغییر داد.
  • با استفاده از AutoCorrect می توان ورود اطلاعات را سرعت بخشید.
  • علاوه بر استفاده از Ctrl+A، با استفاده از دکمه ای در گوشه صفحه می توان تمام داده ها را انتخاب کرد.
  • یکی دیگر از نکات مهم تبدیل تاریخ شمسی به میلادی در اکسل است. با توجه به اینکه اکسل فقط از تاریخ میلادی پشتیبانی می کند، برای تبدیل تاریخ می توان از توابع مختلف که به این منظور تهیه شده است استفاده کرد.
  • برای تغییر فونت اعداد در اکسل پس از انتخاب تمام داده ها می توان فونت آن ها را همزمان تغییر داد