تغییر نحوه محاسبه از خودکار به دستی فقط برای شیت مشخص

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

همانطور که میدانید در اکسل از طریق تب Formulas و گزینه Calculation Options میشود نحوه محاسبات رابه صورت دستی درآورد اما مشکل اینجاست که با اینکار تمام شیت ها به حالت محاسبات دستی تغییر پیدا میکنند. اگر بخواهیم تنها یک شیت اکسل را به حالت محاسبه دستی تغییر دهیم و نحوه محاسبه در بقیه شیت ها اتوماتیک باشیت کافیست به روش زیر عمل کنیم:

ابتدا روی شیت مدنظر راست کلیک میکنیم و از منویی که باز میشه گزینه View Code را انتخاب میکنیم

 

با اینکار وارد پنجره Visual Basic Editor میشویم. در قسمت چپ گزینه EnableCalculation رو پیدا میکنیم و مقدار آن را برابر با False قرار میدهیم:

 

با اینکار فقط شیت مدنظر ما بصورت محاسبه دستی تبدیل میشود و بقیه شیت ها بصورت محاسبه خودکار باقی میمانند. حالا کافیست که هر بار که نیاز به آپدیت شدن محاسبات شیت داشتیم از تب Formulas گزینه Calculate Sheet را انتخاب کنیم. برای برگرداندن نحوه محاسبات شیت به حالت اتوماتیک هم کافیست دوباره گزینه EnableCalculation را به مقدار True تغییر دهیم.

چگونه خطای فرمول ها را اصلاح كنیم

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

 

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

 

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

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

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

خطا را اصلاح كنید 

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

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

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

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

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

ردیابی را مشاهده كنید

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

 استفاده از Trace Error

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

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

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

تابع FORECAST

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

قاعده کلی استفاده از تابع FORECAST اکسل به صورت زیراست:

(محدوده مقادیر متغیر در دوره‌های گذشته، محدوده مقدار تابع برای متغیرهای گذشته، مقدار متغیر که برای آن قصد پیش بینی  تابع را داریم)FORECAST

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

برای اینکار ابتدا باید پارامترهای مختلف تابع FORECAST رادر جدول مشخص کنیم.

محدوده مقادیر متغیر در دوره‌های گذشته:                                                                             از آنجایی  که مقادیر یازده ماه گذشته را داریم پس این پارامتر شامل محدوده A2:A12 یعنی عددهای 1 تا 11 میشود.

محدوده مقدار تابع برای متغیرهای گذشته:                                                                           این محدوده باید مقدار تابع یعنی تعداد فروش رو به ازای عددهای یک تا یازده به ما نشون بده. پس این محدوده رنج B2:B12 را شامل میشود.

مقدار متغیر که برای آن قصد پیش بینی  تابع را دار یم :                                                           میخواهیم مقدار تعداد فروش رو برای ماه 12 حساب کنیم پس مقدار متغیری که ما میخواهیم  پیش بینی کنیم سلول A13 است.

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

FORECAST(A13;B2:B12;A2:A12) 

                                            

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

                                                                                                                                       خطوط شبكه ای كه در برگه های كاری Excel مشاهده می كنید كمی گمراه كننده می باشند. معمولاً، این خطوط چاپ نمی شوند و اگر هم آنها را چاپ كنید كمرنگ به نظر می رسند. برای اینكه سلول ها خطوط مرزی داشته باشند از گزینه هایBorder استفاده كنید. می توانید خط مرزی را به یك سلول یا به محدوده ای از سلول ها اضافه كنید. می توانید خط مرزی را در فقط یك سمت سلول قرار دهید و یا پیرامون تمام جهات سلول. اگر خطوط مرزی سلول ها به دلخواه شما نباشند از یك الگوی پس زمینه مانند تركیب رنگ، جلوه ویژه یا رنگ آمیزی استفاده كنید، ولی به یاد داشته باشید كه پس زمینه ای كه زیاد شلوغ باشد، باعث می شود كه خواننده به سختی داده ها را ببیند.

 كادر محاوره ای Format Cells را باز كنید  

 

سلول یا محدوده ای كه می خواهید خط مرزی یا الگو را به آن اضافه كنید انتخاب كنید و از منوی Format ، گزینه Cellsرا انتخاب كنید تا كادر محاوره ای Format Cells باز شود.

روی برگه Border كلیك كنید. از لیستStyle ، یك شیوه انتخاب كنید و از لیستColor برای انتخاب رنگ برای خط مرزی استفاده كنید.

 

 از Presets استفاده كنید  

 

وقتی كه شیوه خط مشخص شد، یكی از گزینه‌های Presets را كه در بالای برگه قرار دارند انتخاب كنید. برای بكاربردن یك خط مرزی حول لبه های خارجی سلول یامحدوده انتخاب شده، روی Outline كلیك كنید. برای بكار بردن خطوط شبكه، داخل سلول‌های داخلی محدوده، رویInside كلیك كنید.

برای سفارشی كردن خط مرزی، از دكمه های Border استفاده كنید و مشخص كنید كه كدام قسمت های سلول های انتخاب شده باید خط مرزی داشته باشند. برای اضافه كردن یك خط مرزی به آن قسمت، روی دكمه مورد نظر كلیك كنید. برای مشاهده نتیجه، به قسمت پیش‌نمایش دقت كنید، در صورت نیاز، بقیه خطوط مرزی را نیز اضافه كنید.

 از برگه Patterns استفاده كنید 

برای بكار بردن الگو روی سلول های انتخاب شده، روی برگه Patterns كلیك كنید. برای پر كردن سلول های انتخاب شده با رنگ، از صفحه Color یك رنگ انتخاب كنید. برای تركیب كردن دو رنگ در الگو، روی دكمه لیست كشویی Pattern كلیك كنید و رنگ دوم را انتخاب كنید. دوباره لیست را باز كنید تا یك الگو انتخاب كنید. برای مشاهده نتیجه انتخاب ها، به قسمتSample نگاه كنید.

انتخاب های فرمت دهی را اعمال كنید 

روی Ok كلیك كنید تا كادر محاوره‌ای Format Cells بسته شود و تنظیمات جدید در برگه كاری اعمال شوند.

*توجه *

چاپ كردن خطوط شبكه

بصورت پیش فرض، خطوط شبكه Excel چاپ نمی‌شوند. برای اینكه آنها را چاپ كنید، از منوی File ، گزینه Page Setup را انتخاب كنید. در كادر محاوره ای Page Setup ، روی برگهSheet كلیك كنید. در قسمت Print ، گزینه Gridlines را انتخاب كنید و روی Ok كلیك كنید. حال برگه كاری را چاپ كنید تا خطوط شبكه را مشاهده كنید.

استفاده از دكمه های Formatting

برای اضافه كردن خط مرزی به یك سلول، آن را انتخاب كنید، روی فلش دكمه Borders در نوار ابزار Formatting كلیك كنید و یك شیوه برای خط مرزی انتخاب كنید. برای اضافه كردن رنگ به پس زمینه سلول، روی فلش دكمه Fill Color كلیك كنید و از صفحه رنگ، یك رنگ انتخاب كنید. اگر بخواهید رنگ فونت را تغییر دهید، روی فلش دكمه Fill Color كلیك كنید و یك رنگ انتخاب كنید.

 

رمزگذاری روی فایل‌های اکسل

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

برای اینکار کافیست روی دکمه File در گوشه بالا سمت راست کلیک کنید. پس از باز شدن بخش BackStage ، روی دکمه Protect Workbook کلیک کنید و از منوی باز شده گزینه Encrypt With Password را کلیک کنید.

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

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

شناخت اتفاقات عجیب در اکسل

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

1- فلش آبی رنگی در شیت اکسل که پاک نمی شود!

 

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

دلیل این امر اصلاحا Circular References است. یعنی فرمولی که دچار یک حلقه یا loop شده است.

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

مثلادر سلول B3 ما فرمولی نوشته ایم که به سلول C7 ربط دارد و البته !!!! در C7 فرمولی نوشته ایم که به سلول B3 ربط دارد و اکسل سردرگم می شود . بدیهی است که شما باید فرمول ها را اصلاح نمایید.

2- آدرس عجیب و غریب سلول ها در فرمول نویسی!

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

تعجب نکنید این یک روش آدرس دهی است . مثلا سلول A1 که در سطر 1 و ستون 1 اکسل واقع شده است، آدرس آن می شود R1C1 و همینطور الی آخر.

دلیل این موضوع آن است که تنظیمات اکسل را به اشتباه تغییر داده اید. پس EXCEL OPTIONS بروید وR1C1 reference style را از حالت انتخاب خارج کنید.

 

3- هنگام کپی کردن فرمول ، اعداد تکرار می شوند!!!

این حالت وقتی پیش می آید که شما محاسبات اکسل را از حالت Automatic خارج کرده اید و به حالت Manual در آورده اید.

در این حالت اکسل فرمولها را آپدیت نمی کند و منتظر می ماند که شما به صورت دستی آنها را آپدیت کنید که البته کلید میانبر آن F9 است. در Status Bar نرم افزار اکسل شما می توانید کلمه Calculate را مشاهده نمایید (تصویر قبل را ببینید) که یعنی این شیت احتیاج به محاسبه دارد و اعدادش آپدیت نیست.

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

برای تنظیم این حالت (یعنی محاسبات اتوماتیک انجام شود و یا دستی) می توانید از دو مسیر زیر اقدام کنید :

Formual --> Calculation Options

یا

Excel Options --> Formulas --> Calculation Options

 

4- نمودار اکسل خالی (سفید) است!

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

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

5- صفحات سفید زیادی Print می شود

در اکسل به صورت پیش فرض کل منطقه ای که استفاده شده است چاپ می شود. برای آنکه بدانید این منطقه کجاست کلید CTRL+End را بزنید تا به آخرین سلول بروید.

 

 

 

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

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

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

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

• منطق تاریخ و زمان در اکسل
• کلیدهای میانبر format cells
• مفهوم ساعت در اکسل
• مثال: محاسبه دستمزد بر اساس ساعت کارکرد
• مثال: جمع ساعات کارکرد در یک ماه
• نکاتی در مورد ساعت و زمان اکسل
• نحوه تایپ صحیح تاریخ میلادی در اکسل
• تنظیمات control panel

ابزار paste picture link در اکسل

ابزار paste picture link در اکسل ابزاری بسیار جالب و پر استفاده جهت حفاظت از محتوای فایل اکسل می باشد.

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

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

سپس در روبان Home دکمه Paste را باز کرده و در زیر منوی as picture گزینه paste as picture را انتخاب کنید.

 

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

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

 

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

                                                                                                                                         ماکرو (Macro) یک برنامه کامپیوتری است که فایل Excel اضافه می‌شود. این برنامه به زبان VBA نوشته شده است و از آن پس در در آن فایل خاص می‌توان از آن برنامه استفاده کرد.

Macro به چه کاری می‌آیند؟

ماکروها یا همان برنامه ها به فایل ما قابلیت جدید و خاصی اضافه می‌کنند.                                             

منظور از فعال سازی یک Macro چیست؟

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

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

حال اگر فایلی داشته باشیم که حاوی ماکرو است، و می دانیم که ماکروی آن مورد اعتماد است، باید آنرا از حالت قرنطینه (Block) در آوریم یا اصطلاحا «ماکروی آنرا فعال کنیم».

 

چگونه فایلی که ماکرو دارد را بشناسیم؟

اگر فایل اکسل با پسوند XLSM بود ، بدانید که آن فایل Macro دارد. دقت داشته باشید که پسوند فایل‌های عادی XLSX است.

در ضمن اگر آیکون فایل اکسلی دارای علامت ! بود، یعنی آن فایل XLSM است و یعنی که Macro دارد.

 

فعال کردن ماکرو در Excel 2007 و Excel 2010 تفاوت اندکی دارد.

فعال کردن ماکروی یک فایل در Excel 2007

بلافاصله بعد از باز کردن یک فایل در Excel 2007 پیغام  را در بالای صفحه Excel خواهید دید:

 اگر این پیغام هشدار را مشاهده نکردید، در ادامه همین مقاله ، دلیل آن و نحوه فعال کردن «پیغام هشدار» را مطالعه نمایید.

با زدن بر روی گزینه Options، پنجره ای باز می شودکه می‌توانید Macro  فایل را فعال کنید.

گزینه Enable this Content را انتخاب کنید و سپس کلید Ok را بزنید.

در Excel 2007 هر بار که فایل را باز می‌کنید ، حتما باید ماکروی آنرا فعال کنید. البته در ادامه همین مقاله در مورد فعال کردن همیشگی ماکروها ، را خواهید آموخت.

فعال کردن ماکروی یک فایل در Excel 2010

 در Excel 2010 کار کمی ساده تر است.

به محض باز کردن یک فایل که Macro دارد، پیغام زیر را خواهید دید. کافیست که روی گزینه Enable Content کلیک کنید.

 

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

 

 در Excel 2010 اگر یکبار ماکروی فایلی را فعال کنید، در دفعات بعدی لازم نیست که اینکار را انجام دهید و در دفعات بعدی، اکسل به صورت هوشمند، آن را به صورت ماکرو فعال (Macro Enabled) باز خواهد کرد.

چرا پیغام هشدار فعال کردن ماکرو ، نمایش داده نمی‌شود?

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

برای تغییر این تنظیم به مسیر زیر بروید:

File --> Excel Options --> Trust Center --> Trust Center Setting --> Macro Setting

و حالت تنظیم را به «ماکرو رو فعال نکن اما هشدار را نشان بده» ، تغییر دهید.

 

چند نکته در خصوص ماکروها:

1- به یاد داشته باشید که اگر فایلی که Macro دارد را  در حالت xlsx ذخیره کنید، ماکروی آن کاملا حذف می‌شود. البته Excel پیغام هشدار مناسبی به شما نمایش می‌دهد.

2- با وجود اینکه گزینه «نمایش هشدار» در Excel 2010 را فعال کرده اید، باز هم پیغامی نشان داده نشد، گزینه زیر را نیز بررسی نمایید که در حالت "show the messange" تنظیم شده باشد.

Excel 2010: 

File --> Excel Options --> Trust Center --> Trust Center Setting --> Message Bar

توابع جستجو ومرجع در اکسل

توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions مانند توابع ریاضی و توابع متنی که پیشتر با آنها آشنا شدیم، از جمله توابع عمومی اکسل محسوب میشوند که تمام کاربران اکسل به آنها احتیاج دارند، توابع جستجو و مرجع در فایل های اکسلی که محتوی بانک های اطلاعاتی میباشند بسیار کاربرد دارد، برخی از توابع جستجو و مرجع در اکسل مانند توابع VLOOKUP و HLOOKUP بسیار معروف و پرکاربرد هستند. همواره در فرمول ها و توابع اکسل میتوان از سلول های دیگر به عنوان آرگومان استفاده نمود.                                                            

معرفی توابع جستجو و مرجع در اکسل

توابع جستجو و مرجع در اکسل یا Lookup & Reference Functions توابعی هستند که در بانک های اطلاعات در اکسل به صورت گسترده مورد استفاده قرار میگیرند، کارهایی مانند جستجو در بانک های اطلاعاتی، ارجاع دادن و بسیاری موارد دیگر از جمله کاربردهای توابع جستجو و مرجع در اکسل میباشند. در این مقاله برخی از توابع “مرجع” مورد بررسی قرار میگیرند .

 

توابع مرجع در اکسل

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

 

توابع  COLUMN و  COLUMNS در اکسل

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

COLUMN (reference)

  

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد چهار خواهد بود یعنی شماره ستون سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره سمت چپ ترین ستون خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره ستون سلولی که تابع در آن نوشته شده است میباشد.

تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.

توابع  ROW و  ROWS در اکسل

دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:                      

ROW (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.                                                          تابع COLUMNS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد ستون های یک آرایه را بدست آورد.                                                                                       

توابع  ROW و  ROWS در اکسل

دقیقاً مانند تابع COLUMN عمل میکند ولی این تابع اطلاعات ردیف (Row) را به ما میدهد. از تابع ROW در اکسل میتوان شماره سطر یک سلول را به دست آورد، سینتکس این تابع به صورت زیر است:

ROW (reference)

تنها آرگومان این تابع نام سلول است، به عنوان مثال اگر در عبارت فوق بجای reference بنویسیم D10 خروجی تابع عدد ده خواهد بود یعنی شماره سطر سلول D10.            

 

اگر مجموعه ای از سلول ها (Range) را بجای reference  قرار دهیم خروجی تابع شماره بالا ترین سطر خواهد بود. و اگر جای reference را خالی بگذاریم خروجی تابع برابر شماره سطر سلولی که تابع در آن نوشته شده است میباشد.

تابع ROWS مخصوص آرایه ها میباشد، با استفاده از این تابع میتوان تعداد سطر های یک آرایه را بدست آورد.

تابع ADDRESS در اکسل

از تابع ADDRESS در اکسل برای بدست آورد آدرس یک سلول در Worksheet استفاده میشود، Syntax این تابع به صورت زیر است:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• در ساختار این تابع، آرگومان اول و دوم آرگومان های اجباری و عدد هستند و به ترتیب شماره سطر و ستون سلولی هستند که میخواهیم آدرس آن را بدست بیاوریم، آرگومان سوم، چهارم و پنجم آرگومان های اختیاری هستند.
• آرگومان سوم در مورد نسبی یا مطلق بودن آدرس سلول تعیین تکلیف میکند. این آرگومان میتواند خالی بماند و یا یکی از اعداد ۱، ۲، ۳ یا ۴ باشد، اگر خالی یا عدد یک باشد آدرس دهی به صورت مطلق خواهد بود و اگر عدد ۴ باشد آدرس دهی به صورت نسبی میباشد، اگر میخواهیم آدرس سلول به نحوی بیان شود که سطر به صورت مطلق و ستون نسبی باشد، عدد ۲ و برای برعکس این حالت عدد ۳ را بجای آرگومان سوم قرار دهید.
• آرگومان چهارم هم یک آرگومان اختیاری و یک مقدار منطقی (Logical Value) میباشد، اگر این آرگومان خالی بماند و یا True باشد، آدرس سلول به فرمت آشنای A1 یعنی شماره سطر عدد و شماره ستون حرف بیان میشود و اگر این آرگومان False باشد فرمت بیان آدرس سلول به صورت R1C1 میشود که سطر و ستون هر دو عدد هستند.
• و نهایتاً، آرگومان پنجم نام شیت در برگیرنده ی سلول را در صورتی که تمایل داشته باشیم در آدرس سلول ذکر شود را مشخص میکند.

به مثال زیر توجه کنید:

ADDRESS(8;13;3;FALSE;”Sheet1″)= Sheet1!R[8]C13

ADDRESS(8;13;3;TRUE;”Sheet1″)= Sheet1!$M8

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

تابع AREAS در اکسل

در اکسل، Area به یک محدوده ی پیوسته از سلول ها شامل یک یا چندین سلول که در حالت انتخاب قرار دارد گفته میشود، مثلاً سلول A1 به تنهایی میتواند یک Area باشد، همینطور محدوده ی A1:C5 هم اگر توسط کاربر انتخاب شود یک Area است. تابع AREAS تعداد Area های یک محدوده (reference) را مشخص میکند. سینتکس این تابع به صورت زیر است:

AREAS (reference)

در عبارت بالا reference تنها میتواند یک Area باشد، برای انتخاب چند Area باید از پرانتز استفاده کنیم، به مثال های زیر توجه نمایید:

AREAS(B2:D4) =1

AREAS((B2:D4,E5,F6:I9)) = 3

AREAS(B2:D4B2) = 1

در مثال سوم دو Area وجود دارند، اولی B2:D4 و دومی B2 است، در واقع منظور از این نوع نوشتن Area ها بدست آوردن Area فصل مشترک دو Area اصلی میباشد که یک Area است و خروجی این تابع عدد یک میباشد، اگر دو Area قرار گرفته به عنوان reference با هم فصل مشترک نداشته باشند، خروجی تابع خطای !NULL# خواهد بود.

 

تابع CHOOSE در اکسل

سینتکس تابع CHOOSE به صورت زیر است:

CHOOSE(index_num,value1,value2,…)

در عبارت بالا، آرگومان اول عددی است بین ۱ و ۲۵۴ که در واقع شماره مشخصه (Index Number) مربوط به یکی از ۲۵۴ مقدار داده شده به تابع در آرگومان های دوم تا دویست و پنجاه و پنجم میباشد.

آرگومان های Value1، Value2،… تا Value254 میتواند یک فهرست دلخواه کاربر باشد (مانند نام پرسنل، شماره اقلام، آدرس سلول و…) که به ترتیب شماره مشخصه (Index Number) یک تا ۲۵۴ را به خود اختصاص میدهند، خروجی تابع CHOOSE آیتم مربوط به Index_num مورد نظر کاربر میباشد.

اگر عدد قرار گرفته در آرگومان اول کمتر از یک یا بیشتر از تعداد Value ها باشد، خروجی تابع خطای !VALUE# خواهد بود. و اگر عدد Index_num یک عدد اعشاری باشد تنها ارقام صحیح آن عدد در نظر گرفته میشود.

به مثال های زیر توجه کنید:

SUM (CHOOSE (2,A1:A10,B1:B10,C1:C10)) = SUM(B1:B10)

CHOOSE (2,A1:A10,”Excelpro”,۱۳)) = Excelpro

تابع INDEX در اکسل

این تابع دو حالت آرایه ای (Array) و Reference دارد، حالت اول مربوط به آرایه ها میباشد که در مورد آن بعداً بحث خواهیم نمود، سینتکس تابع INDEX به صورت زیر است:

INDEX(reference,row_num,column_num,area_num)

• این تابع چهار آرگومان دارد، reference که آرگومان اول میباشد آدرس یک یا چند محدوده یا Area میباشد، توجه داشته باشید مانند آنچه در تابع AREAS یاد گرفتیم در اینجا هم اگر بخواهیم به چند محدوده یا Area به صورت همزمان اشاره کنیم نیاز به استفاده از پرانتز داریم.
• آرگومان دوم و سوم به ترتیب یک شماره سطر و ستون مربوط به reference میباشد که کاربر میخواهد به آن اشاره کند، مثلاً فرض کنید شما محدوده ای B2:E8 را به عنوان reference به تابع معرفی کرده اید، حال میخواهید مقدار سلول C5 را بدانید، بجای آرگومان دوم عدد ۴ و بجای آرگومان سوم عدد ۲ را قرار دهید (زیرا در محدوده ی انتخاب شده B2:E8 سلول C5 در سطر چهارم و در ستون دوم محدوده قرار دارد)، در ضمن اگر reference معرفی شده تنها دارای یک سطر یا ستون باشد میتوانید آرگومان دوم یا سوم را خالی بگذارید.
• همانطور که گفته شد، میتوانید چند Area را در محل reference به تابع معرفی کنید، در این صورت آرگومان چهارم تعیین میکند کدام Area مورد نظر شما است، به عنوان مثال اگر سه Area به تابع معرفی کنید و آرگومان چهارم عدد دو باشد، تابع در Area دوم به دنبال سطر و ستون مورد نظر شما میگردد

اگر شماره سطر و سلول قرار گرفته به عنوان آرگومان دوم و سوم در محدوده ی Area مورد نظر نباشد خروجی تابع برابر خطای !REF# خواهد بود.

توابع اطلاعات در اکسل

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

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

توابع اطلاعات در اکسل و یا Information Functions در واقع ابزارهای گزارش گیری از وضعیت سلول ها میباشند، در بانک های اطلاعاتی و در رفع اشکال فایل های اکسل، توابع اطلاعات بسیار کاربرد دارند. با استفاده از این توابع میتوان به محتوای سلول ها و یا به اطلاعاتی مفید در مورد آنها پی برد و یا میتوان از نوع خطای رخ داده در سلول با خبر شد. توابع اطلاعات در اکسل را به سه دسته تقسیم میشود ، توابع مربوط به “بدست آوردن اطلاعات” و “توابع دیگر” در این مقاله مورد بررسی قرار میگیرند و در مقاله ی بعدی به معرفی توابع “بررسی محتوای سلول ها” پرداخته میشود.

توابع بدست آوردن اطلاعات در اکسل

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

 

تابع CELL در اکسل

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

CELL (info_type, [reference])

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

آرگومان اول یک داده ی متنی میباشد که نوع اطلاعات مورد نیاز را تعیین میکند

تابع ERROR.TYPE در اکسل

تابع ERROR.TYPE یک آرگومان دارد و Syntax آن به صورت زیر است:

ERROR.TYPE (error_val)

خروجی این تابع عددی متناظر با نوع خطا آرگومان این تابع میباشد، در صورتی که آرگومان این تابع خطا نباشد خروجی تابع ERROR.TYPE خطای N/A# خواهد بود. از این تابع میتوان برای تشخیص نوع خطاها و تصمیم گیری بر اساس آن استفاده نمود و یا اینکه مثلاً با استفاده از یک ساختار IF برای هر نوع خطا یک رشته متنی در نظر بگیرید که به کاربر توضیح دهد چه چیزی را باید اصلاح کند.                                                                 تابع TYPE در اکسل

از این تابع برای بدست آوردن نوع داده استفاده میشود، سینتکس تابع TYPE به صورت زیر است:

TYPE (value)

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

 

تابع SHEET در اکسل

تابع SHEET در اکسل شماره شیت سلول مرجع را ارائه میدهد، “شماره شیت” یعنی اینکه شیت مورد نظر چندمین شیت در مجموعه شیت های موجود میباشد. سینتکس این تابع به صورت زیر است:

SHEET (value)

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

• شیت شامل تمام انواع شیت های اکسل مانند Chart Sheet، Macro Sheet، Dialog Sheet و Worksheet میشود و شیت های Visible، Hidden و Very Hidden را در بر میگیرد.
• اگر آرگومان تابع SHEET یک آرگومان معتبر نباشد، خروجی تابع خطای !REF# خواهد بود.
• اگر آرگومان تابع نام یک شیت نا معتبر (مثلاً نوشتن نام اشتباه یا نوشتن نام شیتی که وجود ندارد) باشد، خروجی تابع خطای N/A# خواهد بود.

تابع SHEETS در اکسل

از این تابع برای بدست آوردن تعداد شیت ها استفاده میگردد. سینتکس تابع SHEETS به صورت زیر است:

SHEETS (reference)

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

• شیت شامل تمام انواع شیت های اکسل مانند Chart Sheet، Macro Sheet، Dialog Sheet و Worksheet میشود و شیت های Visible، Hidden و Very Hidden را در بر میگیرد.
• اگر آرگومان تابع SHEETS یک آرگومان معتبر نباشد، خروجی تابع خطای !REF# خواهد بود.

تابع INFO در اکسل

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

INFO (type_text)

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

 

توابع دیگر اطلاعات در اکسل

تابع NA در اکسل

این تابع یک تابع بدون آرگومان میباشد و خطای N/A# را نمایش میدهد. نمایش این خطا برای کاربر به معنی “هیچ داده ای موجود نیست (No Value is Available)” میباشد. در فرمول نویسی ها گاهی میتوان بجای خالی گذاشتن سلول ها این خطا را استفاده کرد تا کاربر گمان نکند که سلول اشتباهاً خالی مانده. سینتکس این تابع به صورت زیر است:

NA ( )

تابع N در اکسل

این تابع یک آرگومان دریافت میکند و عدد معادل آن آرگومان را به عنوان خروجی میدهد:

• اگر آرگومان این تابع یک عدد باشد، خروجی تابع همان عدد است.
• اگر آرگومان ورودی تابع تاریخ یا ساعت باشد، خروجی تابع N عدد معادل با آن تاریخ و ساعت میباشد.
• اگر ورودی تابع داده ی منطقی True یا False باشد، خروجی تابع ۱ یا صفر است.
• اگر آرگومان ورودی تابع یک خطا باشد، خروجی تابع هم همان خطا خواهد بود.
• در مواردی غیر از موارد فوق، خروجی تابع N عدد صفر است.

 

 

 

Consolidate در اکسل

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

معرفی ابزار Consolidate در اکسل

ابزار Consolidate در اکسل در تب Data و در گروه Data Tools قرار گرفته است.                  

کار با ابزار Consolidate در اکسل

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

برای تهیه گزارش فروش سالیانه شرکت بالا توسط ابزار Consolidate در اکسل، ابتدا یک شیت خالی ایجاد میکنیم و یک سلول را انتخاب مینماییم، سپس بر روی ابزار Consolidate کلیک میکنیم. پنجره Consolidate در اکسل در زیر نشان داده شده است، در این پنجره و در منوی Function تصمیم میگیریم که چه نوع عملیاتی باید بر روی داده های عددی انجام گیرد، میتوان هر یک از عملیات های SUM، COUNT، AVERAGE، MAX، MIN، PRODUCT، COUNT NUMBERS، STDDEV، STDDEVP، VAR، VARP را بر روی داده های عددی انجام داد.                                                                                                                                   برای اضافه کردن جداول گزارشات فصلی، بر روی دکمه Reference در قسمت Reference  کلیک نمایید و محدوده مورد نظر را انتخاب کنید، پس از انتخاب محدوده ی مورد نظر بر روی دکمه Add کلیک کنید تا آن محدوده در قسمت All References اضافه گردد. (برای این مثال ابتدا محدوده ی جدول در شیت یک را انتخاب کنید، آن را Add نمایید و سپس محدوده ی جدول در شیت دو را انتخاب و Add کنید و به همین ترتیب چهار جدول مربوط به فصول را از چهار شیت متفاوت انتخاب و Add نمایید.)

 

 

• برای حذف یک محدوده از قسمت All References آن محدوده را انتخاب و بر روی دکمه ی Delete کلیک نمایید

 

 

 چهار محدوده ار فایل اکسل نمونه انتخاب می کنیم و عملیات SUM قرار است بر روی آنها صورت گیرد. اگر جداول انتخاب شده، مانند جداول این نمونه، دارای عنوان سطر (در ستون سمت چپ) وعنوان ستون (در سطر اول) باشند گزینه های Top Row و Left Column را تیک میزنیم.

 

اگر گزینه ی Create Link to Source Data را تیک بزنیم، با تغییر داده ها در هریک از جداول مرجع (References)، اطلاعات خروجی ابزار Consolidate نیز تغییر خواهند کرد و جدول خروجی به صورت خودکار به روز رسانی خواهد شد.

 

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

 

Data Validation در اکسل

                                                                                                                                            ابزار Data Validation در اکسل یکی از پر کاربرد ترین ابزار ها در فرمول نویسی ها، Formula Sheet ها، نرم افزار های محاسباتی نوشته شده توسط اکسل و… میباشد. توسط ابزار Data Validation در اکسل میتوانید کاربر را طوری محدود کنید که فقط داده های مورد نظر شما را در سلول های تعیین شده وارد نماید، به عنوان مثال میتوانید کاربر را محدود کنید در سلولی فقط بتواند عددی زوج وارد کند، یا اینکه فقط اعداد بزرگتر از ۱۳ قابل وارد شدن در سلول باشند یا اینکه کاربر فقط یکی از گزینه های مورد نظر شما را بتواند در سلول بنویسد و بسیاری محدودیت های دیگر که در ادامه با آنها و روش اعمال آنها آشنا میشویم.                                                     

محدودیت ها در Data Validation در اکسل

برای استفاده از ابزار Data Validation ابتدا سلول مورد نظر را انتخاب نمایید و سپس از تب Data و در گروه Data Tools بر روی دکمه Data Validation کلیک کنید،

گزینه ی اول (Data Validation…) پنجره Data Validation را باز میکند که برای ایجاد محدودیت ها توسط این ابزار به کار برده میشود. با کلیک بر روی گزینه ی دوم (Circle Invalid Data) سلول هایی از صفحه اکسل که ابزار Data Validation در مورد آنها به کار رفته و محتوی داده ای نا معتبر میباشد مشخص میشوند. برای مشخص کردن سلول های دارای داده ی نا معتبر،  دور آن ها خطی قرمز ترسیم میگردد که با کلیک بر روی گزینه ی سوم (Clear Validation Circle) خطوط رسم شده پاک میشوند.

در تب اول پنجره Data Validation در اکسل میتوان تنظیمات مربوط به Validation Criteria را انجام داد، در کادر Allow ابتدا نوع داده یا نوع محدودیت ورود داده به سلول (داده معتبر) را تعیین مینماییم، داده های معتبر را به سه دسته تقسیم کرده ایم، دسته اول شامل گزینه های Whole Number (عدد)، Decimal (عدد علمی)، Date و  Time (تاریخ و زمان) و Text Length (طول رشته متنی) میباشد. دسته دوم گزینه List و دسته سوم Custom است.

به عنوان مثال، اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر میباشند.

در کادر دوم (Data) جزییات مربوط به گزینه ی انتخاب شده در کادر Allow تعیین میگردد، جزییات مربوط به گزینه های قرار گرفته در دسته اول (شامل Whole Number، Decimal، Date، Time و Text Length) یکسان میباشد زیرا همگی ماهیتی عددی دارند. در مثال بالا با فرض تعیین Whole Number یا هریک از گزینه های دسته اول در کادر Allow، در این قسمت اعداد و محدوده های مجاز برای ورود به سلول را به ترتیب زیر تعیین میکنیم:

• اگر میخواهیم اعداد بین دو عدد خاص معتبر باشند گزینه ی Between،
• برای معتبر بودن اعداد خارج از بازه دو عدد خاص گزینه ی Not Between،
• برای معتبر بودن تنها یک عدد خاص گزینه ی Equal to،
• برای معتبر بودن اعدادی غیر از یک عدد خاص گزینه ی Not Equal to،
• برای معتبر بودن اعداد بزرگتر از یک عدد خاص گزینه ی Greater Than،
• برای معتبر بودن اعداد کوچکتر از یک عدد خاص گزینه ی Less Than،
• برای معتبر بودن اعداد بزرگتر مساوی یک عدد خاص گزینه ی Greater than or Equal to،
• و در نهایت برای معتبر بودن اعداد کوچکتر مساوی یک عدد خاص گزینه ی Less than or Equal to.

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

        گزینه ی لیست (List) در کادر Allow، در صورتی که بخواهید کاربر تنها داده هایی خاص که شما تعیین میکنید را بتواند در سلول وارد کند کاربرد دارد، با انتخاب گزینه ی List، کادری به عنوان Source نشان داده میشود که میتوانید لیست داده های دلخواه که در سلول های اکسل نوشته شده است را انتخاب کنید همچنین با تیک زدن گزینه In-Cell Dropdown با انتخاب سلول مورد نظر توسط کاربر، لیست تعیین شده نشان داده میشود که کاربر میتواند گزینه ی دلخواه را از میان آنها انتخاب کند.                                                 

تعیین محدودیت دلخواه در Data Validation

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

برای اینکار پس از انتخاب سلول مورد نظر از پنجره Data Validation و در قسمت Allow گزینه ی Custom را انتخاب نمایید. همانند آنچه در Conditional Formatting و ایجاد قوانین دلخواه دیدیم، در اینجا نیز فرمول نوشته شده حتماً باید خروجی Boolean داشته باشد یعنی خروجی فرمول حتماً باید True یا False باشد. بنابراین توابع Is که جز توابع اطلاعات (Information Functions) میباشند در این مقوله بسیار کاربرد دارند از طرفی توابع منطقی (Logical Functions) مانند توابع IF، AND و OR بسیار مهم و کاربردی میباشند.

                             

گروه Formula Auditing در اکسل

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

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

ابزار های Formula Auditing در اکسل

• در گروه Formula Auditing در اکسل با انتخاب یک سلول و کلیک کردن بر روی گزینه ی Trace Dependents اکسل از سلول انتخاب شده فلش هایی به سمت سلول های دیگر اکسل رسم میکند به شرطی که سلول انتخاب شده در فرمول نوشته شده در آن سلول ها استفاده شده باشد، به این ترتیب به راحتی میتوان فهمید که سلول انتخاب شده در کدام فرمول ها مورد استفاده قرار گرفته است.
• برعکس حالت فوق، برای اینکه بفهمیم در فرمول نوشته شده در یک سلول کدام سلول ها مورد استفاده قرار گرفته اند، میتوانیم بر روی سلول حاوی فرمول کلیک کرده و گزینه ی Trace Precedents را انتخاب کنیم.
• برای حذف فلش های رسم شده  میتوانیم بر روی گزینه ی Remove Arrows کلیک نماییم.
• در اکسل به صورت پیشفرض نتیجه ی فرمول های نوشته شده در یک سلول به کاربر نشان داده میشود و برای مشاهده ی فرمول ها باید از Formula Bar استفاده کرد. با انتخاب گزینه ی Show Formulas در گروه Formula Auditing در اکسل، فرمول های اکسل در سلول ها نمایش داده میشوند.

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

برای بررسی روند اجرای یک فرمول در اکسل میتوان از گزینه ی Evaluate Formula در گروه Formula Auditing در اکسل استفاده کرد. ابتدا سلولی را که قصد بررسی فرمول نوشته شده در آن را دارید انتخاب کنید و سپس بر روی گزینه ی Evaluate formula کلیک کنید. با کلیک بر روی گزینه ی Evaluate میتوانید گام به گام روند اجرای فرمول را بررسی نمایید.

به عنوان مثال، در سلول A2 دمای هوا را درج نمایید، با نوشتن فرمول زیر در سلول B2 با توجه به دمای هوای ذکر شده در شده در سلول A2 دمای هوا را توصیف کنید:

=IF(A2<=0;”Freezing”;IF(AND(A2>0;A2<=8);”Cold”;IF(AND(A2>8;A2<=15);”Cool”;IF(AND(A2>15;A2<=25);”Warm”;IF(A2>25;”Hot”)))))

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

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

با کلیک کردن بر روی گزینه ی Step In در مراحل Evaluate کردن فرمول، مقدار های جایگزین شده برای متغیرها و آرگومان های فرمول (که آدرس سلول میباشند) نشان داده میشود، مثلاً اگر زیر A1 خط کشیده شده باشد با کلیک کردن بر روی Step In مقدار سلول A1 نشان داده میشود و با کلیک کردن بر روی گزینه ی Step Out میتوان به Evaluate کردن فرمول ادامه داد.

بررسی کردن خطاها در اکسل

با انواع خطاها در اکسل قبلاً آشنا شدیم، اگر سلولی دارای خطا باشد، با انتخاب آن سلول و کلیک بر روی دکمه ی Error Checking در گروه Formula Auditing در اکسل میتوان خطای رخ داده را بررسی کرد.

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

از طرفی در صورتی که در هریک از سلول های اکسل فرمولی نوشته شده باشد که از خود آن سلول در فرمول استفاده شده باشد (مثلاً در A1 بنویسیم A1*5=) خطای Circular اتفاق میفتد و خروجی فرمول صفر نشان داده میشود، برای اطلاع از خطاهای Circular رخ داده در شیت اکسل میتوان بر روی گزینه ی Circular Reference کلیک نمود.                                           

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

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

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

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

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

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

  

   

  توابع IS و توابع ترکیبی IF در اشکال زدایی فرمول ها

   توابع بر اساس محتوا در اکسل شامل سه تابع ISODD، ISEVEN و ISFORMULA و توابع IS هستند. توابع IS در اکسل شامل توابع ISBLANK، ISERR، ISERROR، ISLOGICAL، ISNA، ISNONTEXT، ISNUMBER، ISREF و ISTEXT میباشند. تمامی این توابع ساختار یکسانی دارند و خروجی آنها Boolean میباشد، توابع IS در اکسل تنها یک ورودی میگیرند و آن را برای شرط خاصی بررسی میکنند و خروجی تمام این توابع تنها TRUE یا FALSE میباشد.

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

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

  با استفاده از تابع TYPE میتوانیم نوع داده های وارد شده به سلول و یا خروجی فرمول ها را بررسی نماییم و در صورت مغایرت با نوع مورد نظر خود اقدام مناسب را صورت دهیم.

  فرض کنید قرار است کاربر در سلول A1 تعداد کالای مورد درخواست را وارد کند و این عدد در فرمول های دیگر برای محاسبات پارامترهای مورد نیاز استفاده شود، حال اگر کاربر A1 را خالی بگذارد و یا سهواً داده ای غیر عددی در آن وارد نماید مسلماً فرمول های ما با خطا مواجه خواهند شد، برای این کار میتوانیم در سلول A2 فرمول مشابه فرمول زیر بنویسیم:

  =IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data, Number Only please”; “Let’s Do Next Step”)

  در ساختار فرمول بالا از تابع OR (توابع منطقی در اکسل) و توابع TYPE و ISBLANK استفاده شده، به این ترتیب در صورتی که سلول A1 خالی باشد و یا داده ای غیر عددی در آن وارد گردد پیامی مبنی بر خطا بودن نوع داده ی ورودی به کاربر نشان داده میشود. اگر قرار بود عدد وارد شده در A1 به عنوان ورودی تابع POWER (توابع ریاضی) باشد، مسلماً غیر عددی بودن A1 باعث بروز خطا در فرمول میگردید، در چنین موردی ساختاری مانند ساختار زیر را میتوان استفاده کرد:

  =IF(OR(ISBLANK(A1);TYPE(A1)<>1);”Wrong Data in A1″;POWER(A1;2))

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

  همانطور که از مثال های بالا متوجه شدیم از ترکیب تابع IF و توابع IS و دیگر توابع اطلاعات در اکسل میتوان ساختارهای بسیار مفید در جلوگیری از ورود خطاها و داده های اشتباه در فرمول ها تشکیل داد. در این میان دو تابع ISERR و ISERROR از اهمیت ویژه ای برخوردارند، زیرا این دو تابع در نهایت میتوانند از نشان داده شدن پیام های خطا در فایل اکسل شما جلوگیری کنند و به عبارتی خطاها را به دام اندازند. ساختارهای زیر کمک میکنند که با استفاده از این توابع و تابع IF از نشان داده شدن پیام خطا در فایل های اکسل خود جلوگیری نمایید:

  =IF(ISERROR(A1);”This Formula Contains Error”; “Acceptable Outcome”)

  حتی در فرمول نویسی حرفه ای تر میتوان به کاربر با توجه به نوع خطای اتفاق افتاده پیام متناسب داد، مثلاً در صورت بروز خطای N/A# در خروجی تابع VLOOKUPحتماً میتوان این نتیجه را گرفت که عبارت مورد نظر در جدول مورد جستجو وجود نداشته، این پیام را میتوان از ساختاری مشابه ساختار زیر به کاربر داد:

  =IF(ERROR.TYPE(A1)=7;”The Required Item is not in Table”;””)

  در فرمول بالا در صورت بروز خطای خطای N/A# پیامی برای کاربر نمایش داده میشود و او را در جریان قرار میدهد، حتی میتوان ساختار زیر را مستقیماً در سلول A1 استفاده کرد:

  =IF(ERROR.TYPE(VLOOKUP(“Excelpro”;A2:C13;2;FALSE))=7;”The Required Item is not in Table”;VLOOKUP(“Excelpro”;A2:C13;2;FALSE))

  در ساختار بالا عبارت Excelpro در محدوده A2:C13 جستجو میشود و در صورت پیدا نشدن پیامی متناسب به کاربر داده میشود.

   

  استفاده از گروه Formula Auditing در اشکال زدایی فرمول ها

  گروه Formula Auditing در تب Formulas برای رصد کردن وضعیت فرمول ها در اکسل مورد استفاده قرار میگیرد.

Go To در اکسل

                                                                                                                                       به صورت خلاصه، ابزار Go To در اکسل برای انتخاب محدوده ها (Range) کار برد دارد. فرض کنید شما مشغول فرمول نویسی یا رفع اشکال فرمول ها در یک Spreadsheet حرفه ای هستید یا اینکه میخواهید فرمول های نوشته شده در یک فرمول شیت را بررسی کنید. برای اینکار، در وهله ی اول نیاز به شناخت سلول های حاوی فرمول دارید. یا فرض کنید در یک فایل اکسل طولانی میخواهید به آخرین سلول را فعال نمایید و یا یک محدوده پیوسته از اکسل (Area) را انتخاب کنید. ابزار Go To در اکسل از جمله Tools های کاربردی میباشد که در ادامه با آن آشنا خواهیم شد.

معرفی Go To در اکسل

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

لازم به ذکر است پنجره Go To Special هم به عنوان قسمتی از ابزار Go To به شمار می آید که در این مقاله مورد بررسی قرار میگیرد.

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

 

کار با ابزار Go To در اکسل

در تب Home و در گروه Editing، بر روی منوی Find & Select کلیک نمایید. با کلیک بر روی گزینه ی Go To، پنجره Go To باز میشود،(با فشردن کلید Ctrl + G میتوان پنجره Go To را باز کرد):

 در پنجره Go To، تمام نام های تعریف شده در فایل اکسل فعال نشان داده میشوند که کاربر میتواند با کلیک بر روی هریک، سلول های مربوط به آنها را انتخاب نماید.  در قسمت Reference میتوان نام هر محدوده ی دلخواه در فایل اکسل را وارد نموده با فشردن کلید Ok آن محدوده را انتخاب کرد. از طرفی کلیک کردن بر روی دکمه Special در پنجره Go To در اکسل و یا انتخاب گزینه Go To Special از منوی Find & Select پنجره Go To Special را باز میکند .

پنجره Go To Special در اکسل

پنجره Go To Special در اکسل برای انتخاب سلول های اکسل به کار میرود.

 

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

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

گزینه Formulas: سلول های حاوی فرمول را انتخاب میکند، اگر تنها گزینه ی Number انتخاب شده باشد، سلول های حاوی فرمول که خروجی آنها عدد باشد انتخاب میگردد. گزینه ی Text، سلول های حاوی فرمول که خروجی آنها داده متنی باشد را انتخاب میکند. گزینه Logical سلول های حاوی فرمول که خروجی آنها داده منطقی باشد را انتخاب میکند و در نهایت گزینه Error سلول ها حاوی فرمول که خروجی آنها خطا باشد را انتخاب مینماید. از این گزینه میتوان برای مشخص کردن سلول های حاوی فرمول در بحث رفع اشکال فرمول ها در اکسل استفاده نمود.

گزینه Blanks: سلول های خالی را انتخاب میکند. از این گزینه، پس از انتخاب سلول های خالی، میتوان با کلیک روی دکمه های Delete Row یا Delete Column در منوی Delete ، گروه Cells و در تب Home برای Delete کردن سطر ها و ستون ها استفاده کرد.

گزینه Current Region: سلول های محدوده (Area) مربوط به سلول انتخاب شده را انتخاب میکند، برای انتخاب تمام سلول های یک جدول یا یک بانک داده از این گزینه میتوان استفاده نمود.

گزینه Objects: تمام اشیا را انتخاب میکند، مانند شکل ها، نمودار ها، Smart Art ها و غیره.

 

گزینه Row Differences: فرض کنید تعدادی سطر داده داریم که در هریک از آنها فرضاً سه ستون قرار دارد، فرض کنیم داده های این سه ستون قرار است یکسان باشند (یعنی هر سطر داده های یکسان داشته باشد)، ، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا کردن و انتخاب داده های غیر یکسان در هر سطر، کافیست از گزینه Row Difference در پنجره Go To Special استفاده کنید. قبل باز کردن پنجره Go To Special محدوده مورد نظر را انتخاب کنید.

گزینه Column Differences: فرض کنید تعدادی ستون داده داریم که در هریک از آنها فرضاً سه سطر قرار دارد، فرض کنیم داده های این سه سطر قرار است یکسان باشند (یعنی هر ستون داده های یکسان داشته باشد)، شما میخواهید آنها را با هم مقایسه کنید و داده های متفاوت را مشخص نمایید. برای پیدا کردن و انتخاب داده های غیر یکسان در هر ستون، کافیست از گزینه Column Difference در پنجره Go To Special استفاده کنید. قبل باز کردن پنجره Go To Special محدوده مورد نظر را انتخاب کنید.

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

گزینه Dependents: تمام سلول هایی که در آنها از سلول فعال استفاده شده است را انتخاب میکند، با انتخاب گزینه Direct Only، تنها سلول هایی که سلول فعال مستقیماً در آنها استفاده شده است انتخاب میگردند و با انتخاب گزینه All Levels تمام سلول هایی که در فرمول نوشته شده در آنها (مستقیم و غیر مستقیم) سلول فعال استفاده گردیده انتخاب میگردند.

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

گزینه Visible Cells Only: سلول های Visible را انتخاب میکند، یعنی سطر ها و ستون های Hide شده را انتخاب نمیکند. این گزینه زمانی کار میکند که سطرها و ستون های Hide شده وجود داشته باشد.

گزینه Conditional Formats: سلول هایی که ابزار Conditional Formatting در مورد آنها به کار رفته انتخاب میکند.

گزینه Data Validation: سلول هایی که ابزار Data Validation برای آنها استفاده شده است را انتخاب میکند. انتخاب گزینه Same به ما این امکان را میدهد که سلول هایی را که Data Validation در آنها با محدودیتی مشابه محدودیت به کار رفته برای سلول فعال استفاده شده است را انتخاب کنیم و گزینه All تمام سلول هایی که این ابزار در آنها استفاده شده است را انتخاب میکند.

Sort در اکسل

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

معرفی ابزار Sort در اکسل

ابزار Sort در اکسل (Custom Sort) در تب Data و در گروه Sort & Filter قرار گرفته است

 

کار با ابزار Sort در اکسل

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

میخواهیم جدول را بر مبنای ستون دوم (Item Name) و به ترتیب حروف الفبا مرتب کنیم، برای انجام اینکار ابتدا سلولی از جدول مورد نظر را انتخاب کنید و سپس بر روی آیکون Sort در تب Data کلیک نمایید، پنجره Sort باز میشود.

در Sort Level موجود در پنجره Sort، در کادر Sort by نام ستونی (فیلد) از جدول خود که میخواهید مرتب سازی بر اساس آن صورت گیرد (ستون Item Name) را انتخاب نمایید و در قسمت های Sort On و Order با توجه به نوع داده های ستون مورد نظر، روش Sort را انتخاب کنید. در اینجا میخواهیم ستون Item Name را به ترتیب حروف الفبا مرتب کنیم.

پنجره Sort نشان داده شده در بالا تنها دارای یک Sort Level میباشد، یعنی جدول تنها بر مبنای ستون دوم و به صورت صعودی مرتب شده است ، در پنجره Sort در اکسل با استفاده از دکمه ی Add Level میتوان یک Sort Level جدید ایجاد نمود.

همانطور که گفته شد، در پنجره Sort در اکسل میتوان با استفاده از Add Level یک جدول را در چند سطح (Level) مرتب سازی (Sort) کرد. به خروجی جدول  توجه کنید، ستون دوم طبق خواست ما به صورت صعودی مرتب شده است، اما اگر بخواهیم برای یک کالا، نام فروشنده ها نیز مرتب شود باید چه کنیم! یعنی اگر بخواهیم مثلاً برای کالای Eraser اسم فروشنده های این کالا در ستون سوم به ترتیب حروف الفبا باشد. برای این کار باید یک Level به پنجره Sort اضافه نماییم.

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

برای اضافه کردن Level های بعدی میتوان همچنان از Add Level یا Copy Level استفاده کرد، برای پاک کردن یک Level از گزینه Delete Level و برای جابجا کردن ترتیب Level ها میتوان از دکمه های Move Up و Move Down استفاده کرد.

با کلیک کردن روی دکمه Option وارد پنجره تنظیمات Sort در اکسل میشویم، در اینجا میتوانیم در مورد Case Sensitive بودن Sort ها و Orientation آنها تصمیم بگیریم.

انواع Sort در اکسل

ستون های جداول در اکسل را میتوان بر اساس Value (یعنی محتوای سلول ها)، Cell Color (رنگ سلول ها)، Font Color (رنگ فونت سلول ها) و Cell Icon (آیکون سلول ها) مرتب سازی کرد. اگر ستون داده ها بر اساس Value مرتب شود برای داده های عددی گزینه های Smallest to Largest و Largest to Smallest و برای داده های متنی گزینه های A to Z و Z to A وجود دارد.

گزینه ی Custom List داده های متنی را بر اساس لیست های دلخواه، که قبلاً روش وارد کردن آنها در اکسل را یاد گرفته ایم، مرتب میکند.

          

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

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

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

برای ایجاد سری ها در اکسل میتوان از فرمول نویسی استفاده نمود ، سری های عددی (برای داده های نوع عددی) در اکسل به دو دسته ی کلی Linear و Growth تقسیم میشوند، از طرفی سری نوع Date (برای داده های نوع تاریخ و زمان) با توجه به کاربرد ان در اکسل تعریف شده است. کاربرد و روش ایجاد هر سری در ادامه توضیح داده خواهد شد.

سری ها در اکسل

دو نوع سری عددی در اکسل میتوان تعریف کرد، نوع اول سری Linear میباشد که در آن هر جمله از سری (Series) برابر است با جمله ی قبلی بعلاوه یک عدد ثابت و نوع دوم سری Growth است که در آن هر جمله برابر است با جمله قبلی ضرب در یک عدد ثابت، این دو سری برای داده های عددی تعریف شده اند و برای کارهایی مانند ایجاد شماره سطر و یا ایجاد سری های ریاضی به کار میروند. نوع دیگری سری در اکسل وجود دارد به نام سری Date، هر عددی در اکسل (در یک بازه مشخص) در واقع میتواند سریال نامبر یک تاریخ و زمان در اکسل باشد. سری نوع Date در واقع این تاریخ ها را ایجاد مینماید، مثلاً اگر  بخواهید تمام روزهای هفته از یک تاریخ خاص تا تاریخ خاصی دیگر را پشت سر هم در یک ستون درج کنید میتوانید از سری نوع Date استفاده کنید.

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

برای تعریف سری ها در اکسل از تب Home و در گروه Editing منوی Fill را انتخاب کنید:

 

برای ایجاد سری ها، ابتدا جمله اول سری را در یک سلول بنویسید، آن سلول را انتخاب نموده و مسیر توضیح داده شده در بالا را بپیمایید. برای سری های نوع Date دقت داشته باشید که این سری را میتوانید برای Days یا روزها، Weekday یا روزهای هفته (که روزهای شنبه و یکشنبه در این سری وارد نمیشوند)، Month یا ماه ها , Years یا سالها نوشته شود و هر ماه و سال دقیقاً منطبق با تعداد روزهای آن در تقویم حساب میگردد.   

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

برای تعریف سری ها در اکسل از تب Home و در گروه Editing منوی Fill را انتخاب کنید:

 

برای ایجاد سری ها، ابتدا جمله اول سری را در یک سلول بنویسید، آن سلول را انتخاب نموده و مسیر توضیح داده شده در بالا را بپیمایید. برای سری های نوع Date دقت داشته باشید که این سری را میتوانید برای Days یا روزها، Weekday یا روزهای هفته (که روزهای شنبه و یکشنبه در این سری وارد نمیشوند)، Month یا ماه ها , Years یا سالها نوشته شود و هر ماه و سال دقیقاً منطبق با تعداد روزهای آن در تقویم حساب میگردد.

 

لیست در اکسل

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

در آینده و در مبحث Sort کردن، یاد میگیریم که چطور میتوان جداول را بر اساس Custom List ها مرتب کرد. مبحث ایجاد لیست دلخواه در اکسل از این نظر دارای اهمیت میباشد.

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

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

برای وارد کردن لیست داخواه خود در اکسل ۲۰۰۷ ،  وارد پنجره Excel Option شده و در تب Popular بر روی دکمه ی Edit Custom List کلیک نمایید. در نسخه های جدیدتر اکسل این دکمه در قسمت General و تب Advanced قرار گرفته است.

 

برای وارد کردن لیست دلخواه در اکسل میتوانید از دکمه Add و استفاده کنید و یا با استفاده از دکمه Import لیست خود را از سلول های اکسل وارد نمایید.

 

 

 

 

Find & Replace در اکسل

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

برای باز کردن پنجره Find & Replace در اکسل میتوان از کلید میانبر Ctrl + F استفاده کرد و یا در تب Home و در گروه Editing بر روی منوی Find & Select کلیک نمود.

لازم به ذکر است اگر یک محدوده (شامل چندین سلول) از اکسل انتخاب شود و سپس پنجره Find & Replace فعال گردد، عملیات جستجو و جایگزینی به صورت پیشفرض صرفاً برای آن محدوده انجام میگیرد، ولی در صورتی که یک سلول در حالت انتخاب قرار داشته باشد عملیات Find & Replace در اکسل به صورت پیشفرض برای تمام آن شیت انجام میشود.

پنجره Find & Replace در اکسل

 

د. با کلیک کردن بر روی Option جزییات پنجره Find & Replace در اکسل نمایش داده میشود.

شاید تا کنون با این نکته مواجه نشده باشید که علاوه بر محتوای سلول ها در اکسل میتوان فرمت آنها را نیز جستجو کرد، مثلاً شما میخواهید در فایل اکسل به دنبال سلول هایی بگردید که فونت آنها Bold شده است.

• برای جستجوی یک فرمت خاص (شامل رنگ، فونت، حاشیه و…) سلول، بر روی منوی فرمت (Format) در سمت راست و بالای پنجره Find & Replace کلیک نمایید و از منوی باز شده با انتخاب گزینه ی Format و باز شدن پنجره Find Format فرمت مورد نظر را انتخاب کنید. و یا در همین منو با انتخاب گزینه ی Choose Format From Cell… فرمت دلخواه خود را از یک سلول در اکسل انتخاب نمایید و آن را جستجو کنید.
• اگر گزینه ی Match Case تیک زده شود، ابزار Find & Replace در اکسل به صورت Case Sensitive عمل مینماید.
• در صورت تیک زدن گزینه ی Match Entire Cell Contents تمام محتوای سلول با عبارت مورد جستجو مقایسه میگردد و تمام کاراکترهای نوشته داخل سلول باید با کاراکترهای عبارت مورد جستجو یکسان باشد.

کاراکترهای Wildcard شامل ? و * تنها در صورتی میتوانند توسط ابزار Find & Replace در اکسل مورد استفاده قرار بگیرند که گزینه ی Match Entire Cell Contents تیک خورده باشد.

• کادر Within در پنجره Find & Replace در اکسل محل جستجو را نشان میدهد، جستجو میتواند در شیت فعال و یا در تمام Workbook صورت پذیرد.
• منوی Search صرفاً الگوی جستجو را تعیین میکند که سطری باشد یا ستونی
• همانطور که قبلاً گفته شد، توسط ابزار Find & Replace در اکسل میتوان در فرمول ها، مقادیر و یا کامنت ها جستجو کرد. منوی Look in تعیین میکند جستجو در میان Values (سلول هایی که در آنها مقادیر ثابتی نوشته شده است) یا Formulas (سلول هایی که در آنها فرمول نوشته شده است) و یا کامنت ها (Comments) صورت گیرد.

Replace در اکسل

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

 

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

در اکسل ۲۰۱۰ به بعد به کاربران این امکان داده شده که علاوه بر رسم انواع نمودار که قبلاً وجود داشته در سلول های اکسل نیز نمودار رسم کنند، این نوع نمودارها که در یک سلول اکسل رسم میشوند Sparkline نام دارند .                                                                                                                                                                                                                                                                  

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

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

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

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

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

 

پس از انتخاب نمودارهای Sparkline رسم شده با استفاده از تب Design میتوان ظاهر نمودار ها را ویرایش کرد، نقاط Max و Min توسط نقطه های سیاه رنگ مشخص شده و سایر نقاط به رنگ قرمز هستند. از طرفی در تب Design میتوان نمودار ها را با هم Group کرد و یا نوع آنها را تغییر داد، همچنین برای پاک کردن نمودار های Sparkline در تب Design باید گزینه ی Clear را انتخاب کنیم.

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

این نوع نمودار نیز کاربردی مشابه نمودار لاین (Line) دارد، تنها با این تفاوت که نمودار لاین بیشتر برای داده های پیوسته و نمودار ستونی (Column) برای داده های گسسته معنا دارد ولی به کار بردن هریک از این دو نمودار کاملاً بستگی به نیاز شما و نوع داده های شما دارد،نمودار ستونی را برای همان داده ها میتوان رسم نمود، مراحل رسم نمودار لاین و ستونی کاملاً مشابه میباشد و تنها تفاوت در انتخاب نوع نمودار است.

این نوع نمودار برای نشان دادن صرفاً منفی و مثبت یا برنده بازنده (Win/Lose) مناسب میباشد، برخلاف دو نمودار Line و Column بزرگی و کوچکی اعداد اصلاً مهم نیست و تنها مثبت یا منفی بودن آنها اهمیت دارد، داده های مثبت رو به بالا و داده های منفی رو به پایین رسم میشوند.

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

 

 

Scenario Manager در اکسل

                                                                                                                   منوی What if Analysis در اکسل در تب Data و در گروه Data Tools قرار گرفته است. با کلیک کردن بر روی منوی What if Analysis در اکسل میتوان به سه ابزار Goal Seek، Data Table و Scenario Manager برسیم

 در نوشتن گزارش ها حتماً به مواردی بر خواهیم خورد که برای مساله مورد بررسی با چند سناریو (Scenario) رو به رو هستیم. فرض کنیم در مورد جدولی صحبت میکنیم که در آن به ازای شرایط مختلف تولید خروجی های متفاوتی وجود دارد. در این حالت هر مجموعه شرایط تولید میتواند یک سناریو باشد که خروجی مخصوص به خود دارد. ابزار Scenario Manager در اکسل به کاربران کمک میکند که سناریو های مختلف را به راحتی در فایل های اکسل نمایش دهند و گزارشات خروجی را بر اساس سناریو های مختلف مشاهده نمایند. در ادامه با Scenario Manager بیشتر آشنا خواهیم شد.

ابزار Scenario Manager در اکسل

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

در مثال بالا میخواهیم تمام سناریو ها را  در جدول اول نشان دهیم، برای اینکار از منوی What if Analysis بر روی گزینه ی Scenario Manager کلیک میکنیم. پنجره Scenario Manager در اکسل باز میشود، برای تعریف سناریو ها بر روی دکمه Add کلیک میکنیم تا پنجره Add Scenario باز شود.

برای مثال بالا چهار سناریو باید تعریف کنیم، نام سناریو ها را به ترتیب Low Quality، Medium Quality، High Quality و Premium Quality میگذاریم، در هر سناریو “تعداد تولید شده”، “کیفیت” و “قیمت فروش” متفاوت میباشد. هزینه ی تولید تابعی از قیمت فروش میباشد و بنابراین نیازی به تغییر آن در هر سناریو نیست.

طبق جدول بالا، سلول های C6، C7 و C8 سلول های متغیر (Changing Cells) میباشند که در پنجره Add Scenario در کادر دوم انتخاب میشوند، کادر اول نام سناریو و کادر سوم کامنت کاربر در مورد آن سناریو میباشد. پس از تکمیل پنجره Add Scenario برای سناریوی اول کادر به صورت زیر خواهد بود:

 

با کلیک کردن بر روی Ok وارد پنجره Scenario Value میشویم و این پنجره را بر اساس داده های جدول زیر پر میکنیم. در پنجره Scenario Value برای هر Changing Cell یک کادر در نظر گرفته شده که کاربر میتواند مقدار مختص با آن Changing Cell در سناریو مربوطه را در آن وارد نماید.

نکته مهم اینکه، در فایل مثال نام سلول های C6، C7 و C8 را به نام های معنا دار نامگذاری نموده ایم تا خروجی های ابزار Scenario Manager در اکسل ملموس تر و شکیل تر شوند، سلول C6 را Quantity، C7 را Quality و C8 را Price نامگذاری کرده ایم

پس از کلیک کردن بر روی دکمه Ok کار تعریف سناریوی اول به پایان میرسد، با کلیک کردن بر روی دکمه Add سناریوهای دوم، سوم و چهارم را طبق داده ها تکمیل میکنیم.

برای اضافه کردن سناریوهای جدید دکمه Add، برای پاک کردن یک سناریو دکمه Delete، برای ویرایش یک سناریو دکمه Edit، در صورت تعریف سناریوها در شیت های مختلف میتوان از دکمه Merge برای دیدن تمام سناریو ها در همین پنجره استفاده نمود و در نهایت با کلیک کردن روی دکمه Summary میتوان یک گزارش از سناریو ها در یک شیت جداگانه در غالب Scenario Summary Table یا Scenario Pivot Table Report کرد و بر اساس جدول بدست آمده نمودار مورد نیاز را ترسیم نمود.  ابزار Scenario Manager در اکسل به صورت Scenario Summary Table به همراه نمودار رسم شده بر اساس آن را نشان میدهد.

توابع متنی در اکسل(قسمت اول){توابع جداسازی ویکپارچه سازی ستون وتوابع ویرایش متنی}

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

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

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

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

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

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

تابع CONCATENATE در اکسل

تابع CONCATENATE مانند تابع & (AND) در اکسل میماند، از این تابع برای به هم چسبندن متن ها (متغیرهای رشته ای) استفاده میگردد. با استفاده از این تابع میتوانیم تا تعداد ۲۵۵ آرگومان را در کنار یکدیگر به عنوان یک رشته متنی (Text String) قرار دهیم. آرگومان های این تابع میتوانند اعداد، متن ها و آدرس سلول ها باشند. Syntax تابع CONCATENATE به همراه مثالی از کاربرد آن در ادامه آمده است:

CONCATENATE(text1, [text2], …)

CONCATENATE(“Excel”, “Pro”)=ExcelPro

تابع RIGHT در اکسل

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

RIGHT(text,num_chars)

آرگومان اول رشته متنی و آرگومان دوم تعداد کاراکتر مورد نظر برای جدا کردن میباشد.

تابع LEFT در اکسل

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

تابع MID در اکسل

از تابع MID برای جدا کردن تعداد کاراکتر دلخواه از وسط (محل دلخواه) یک رشته ی متنی استفاده میشود، Syntax این تابع مانند زیر است:

MID(text,start_num,num_chars)

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

تابع REPT در اکسل

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

REPT(text,number_times)

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

 

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

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

تابع TRIM در اکسل

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

TRIM(text)

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

تابع CLEAN در اکسل

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

CLEAN(text)

تابع LOWER در اکسل

تابع LOWER برای تبدیل حروف انگلیسی بزرگ (Uppercase) به کوچک (Lowercase) در یک رشته متنی مورد استفاده قرار میگیرد.

LOWER(text)

تابع UPPER در اکسل

تابع UPPER برای تبدیل حروف انگلیسی کوچک(Lowercase) به بزرگ (Uppercase)در یک رشته متنی مورد استفاده قرار میگیرد.

UPPER(text)

تابع PROPER در اکسل

حرف اول یک رشته ی متنی را تبدیل به حرف بزرگ (Uppercase) میکند، علاوه بر این، هر حرفی که بعد از یک کاراکتر غیر حرفی (any character other than a letter) قرار داشته باشد را نیز بزرگ میکند و تمامی حروف باقی مانده را به حروف کوچک (Lowercase) تبدیل میکند.

PROPER(text)

PROPER(“excelrpo”)=Excelpro

PROPER(“excelpro.ir”)=Excelpro.Ir

 

Paste در اکسل

                                                                                                                                    حتماً برای شما پیش آمده که در اکسل نیاز به کپی کردن سلول یا سلول هایی داشته باشید، یکی از ساده ترین و ابتدایی ترین کارها در اکسل کپی کردن یک سلول در جای دیگر میباشد. اما همانطور که میدانیم سلول ها در اکسل خواص زیادی دارند، سلول ها در اکسل میتوانند دارای فرمول (Formula) باشند، میتوانند دارای کامنت (Comment) باشند، سلول ها در اکسل میتوانند دارای محدودیت ورود داده (Data Validation) و یا Conditional Formatting باشند، در کنار اینها عرض سلول ها (Column Width)، فرمت آنها (Cell Formats) و افقی یا عمودی بودن چند سلول همگی مواردی هستند که با کپی کردن سلول میتوان آن ها را نیز کپی نمود. ابزار مخصوصی که برای اینکار قرار داده شده است، ابزار Paste در اکسل میباشد.یادگیری کامل Paste در اکسل نیاز به مقدماتی دارد، لازم است ابتدا با مباحثی شامل، کامنت ها (Comments) و نحوه ی تعریف آنها، ابزار ایجاد محدودیت در ورود داده ها به سلول (Data Validation)، روش تغییر فرمت سلول ها یا Cell Formats ،Conditional Formatting و اصول فرمول نویسی در اکسل آشنا باشیم.

منوی Paste در اکسل

در کل، برای Paste در اکسل، یا به عبارت بهتر، Paste کردن سلول هایی که کپی (Copy) شده اند چندین حالت وجود دارد، منوی Paste در تب Home و در گروه Clipboard دربردارنده ی امکانات Paste در اکسل میباشد. شکل زیر منوی Paste در اکسل را نشان میدهد و نمودار شاخه ای رسم شده، مبحث Paste در اکسل را به صورت خلاصه نمایش میدهد:

 

برای کپی کردن یک سلول از جایی به جای دیگر، پس از انتخاب سلول، کلید Ctrl+C را میفشاریم، و یا میتوانیم بر روی سلول کلیک راست نموده و گزینه ی Copy را انتخاب کنیم. اما برای Paste کردن، علاوه بر فشردن کلید Ctrl+V و کلیک راست و انتخاب گزینه ی Paste در سلول مقصد، میتوانیم در تب Home و در گروه Clipboard بر روی منوی Paste کلیک نماییم. منوی Paste در اکسل دارای چند گروه و گزینه میباشد

 

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

 

گروه Paste در منوی Paste در اکسل

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

• Paste: در صورتی که بخواهید تمام خواص (فرمت و مقدار) یک سلول کپی شده به سلول مقصد منتقل شود این گزینه را انتخاب نمایید، این گزینه معادل گزینه ی All در پنجره Paste Special میباشد.
• Formulas: برای کپی کردن فرمول یک سلول از جایی به جای دیگر از این گزینه استفاده مینماییم، این گزینه معادل گزینه Formulas در پنجره Paste Special میباشد.
• Keep Source Formatting: برای کپی کردن یک سلول از جایی به جای دیگر در حالتی که فرمت اولیه سلول کپی شده نیز منتقل شود به کار میرود، این گزینه در سلول های اکسل تفاوتی با گزینه ی Paste و گزینه All در پنجره Paste Special ندارد.
• No Borders: با انتخاب این گزینه Border های (خطوط حاشیه سلول) سلول کپی شده به مقصد منتقل نمیشوند. این گزینه معادل گزینه All Except Borders در پنجره Paste Special میباشد.
• Keep Source Column Width: این گزینه سلول کپی شده را به مقصد منتقل میکند و علاوه بر این، عرض ستون آن سلول در مبدا را نیز به مقصد انتقال میدهد.
• Formula & Number Formatting: این گزینه فرمت عددی و فرمول سلول کپی شده را به مقصد انتقال میدهد، این گزینه معادل گزینه ی Formulas & Number Formats در پنجره Paste Special میباشد.
• Transpose:  با انتخاب این گزینه، جای سطر و ستون سلول های کپی شده در مقصد عوض میشود، یعنی مثلاً سلول های عمودی، افقی میشوند و برعکس. این گزینه معادل گزینه Transpose در پنجره Paste Special میباشد.

گروه Paste Values در منوی Paste در اکسل

بخش دوم از منوی Paste در اکسل، گروه Paste Value میباشد که دارای سه گزینه است، در ادامه هر گزینه معرفی شده است:

• Values: اگر بخواهیم تنها اعداد (نتیجه فرمول) از سلول کپی شده به مقصد منتقل شود، این گزینه را انتخاب میکنیم. این گزینه معادل گزینه ی Values در پنجره Paste Special میباشد.
• Values & Number Formatting: اگر بخواهیم فرمت اعداد و مقادیر از سلول کپی شده به مقصد منتقل شوند، این گزینه مناسب میباشد. این گزینه معادل با گزینه ی Values & Number Formats در پنجره Paste Special میباشد.
• Values & Source Formatting: برای کپی کردن تنها مقادیر سلول ها و فرمت آنها به مقصد جدید، میتوان از این گزینه استفاده نمود

گروه Other Paste Options در منوی Paste در اکسل

بخش سوم از منوی Paste در اکسل، گروهOptions  Other Paste میباشد که دارای چهار گزینه است، در ادامه هر گزینه معرفی شده است:

• Formatting: با انتخاب این گزینه میتوان فرمت یک سلول را به سلولی دیگر منتقل کرد، این گزینه معادل گزینه Formats در پنجره Paste Special میباشد، همینطور، دکمه Format Painter در تب Home و در گروه Clipboard نیز میتواند برای کپی کردن فرمت یک سلول برای سلول های دیگر مورد استفاده قرار بگیرد.
• Past Link: با انتخاب این گزینه لینک سلول کپی شده در سلول مقصد قرار میگرد، به عنوان مثال اگر شما سلول A1 را با استفاده از این گزینه کپی کنید، در سلول مقصد عبارت =A1 نوشته میشود. این گزینه معادل دکمه Paste Link در پنجره Paste Special در اکسل میباشد.
• Picture: اگر از این گزینه استفاده نماییم، عکسی از سلول های کپی شده در مقصد قرار داده میشود، عکس بدست آمده تمام خواص عکس ها در اکسل را دارد.
• Linked Picture: این گزینه نیز مانند بالا، عکسی از سلول های کپی شده در مقصد قرار میدهد با این تفاوت که عکس مورد نظر به محتوای سلول کپی شده لینک میباشد و با تغییر مقدار سلول کپی شده، عکس قرار گرفته در مقصد نیز تغییر میکند.

پنجره Special  Paste در منوی Paste در اکسل

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

• Comments: این گزینه کامنت نوشته شده برای سلول کپی شده را برای سلول مقصد کپی مینماید، به این ترتیب اگر چند سلول از فایل اکسل شما کامنت تکراری داشته باشد نیازی به نوشتن کامنت برای تک تک سلول ها نیست و از این طریق میتوان کامنت سلولی را برای سلول های دیگر کپی کرد. این روش تنها راه کپی کردن کامنت ها در اکسل میباشد.
• Validation: برای کپی کردن Validation یک سلول به سلول دیگر یا به عبارتی، برای اینکه محدودیت ورود داده به سلولی را برای سلول دیگر نیز قرار دهیم میتوانیم از این گزینه استفاده نماییم، لازم به ذکر است تنها راه کپی کردن Validation در اکسل این راه میباشد.
• Column Width: برای اینکه در سلول مقصد، عرض ستون برابر با سلول کپی شده باشد میتوان از این گزینه استفاده نمود. به این ترتیب نیازی به تغییر دستی عرض ستون ها نیست و تنها کافیست با استفاده از این گزینه عرض ستون ها را از جایی به جای دیگر کپی کرد.
• All Merging Conditional Formats: این گزینه هم مانند گزینه All عمل مینماید، با این تفاوت که اگر سلول های مقصد یا مبدا داراConditional Formatting باشند، Conditional Formatting آنها با یکدیگر ادغام میگردد، در صورتی که اگر به صورت عادی Paste شود، Conditional Formatting سلول های مقصد از بین میرود.

قسمت دوم از پنجره Paste Special، قسمت Operation میباشد، در این قسمت میتوان تعیین کرد که یک عملیات ریاضی بین محتوای سلول های کپی شده و سلول های مقصد انجام شود، به عنوان مثال اگر میخواهید محتوای سلول های کپی شده و سلول های مقصد (در محل سلول های مقصد) با یکدیگر جمع شوند، از این قسمت گزینه ی Add را انتخاب نمایید، برای تفریق Subtract، ضرب Multiply، و برای تقسیم گزینه ی Divide را انتخاب کنید.

و در نهایت، در قسمت سوم از پنجره Paste Special در اکسل، با تیک زدن گزینه ی Skip Blanks سلول های خالی کپی شده از مبدا، به مقصد منتقل نمیشوند.

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

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

قابلیت دیگر، کاربرد گزینه Pick From Drop-down List است. از این گزینه برای وارد کردن اقلام تکراری استفاده می شود. پساز پر پرکردن یکی از سلول ها مثلا6B روی آن کلیک راست می کنیم و سپس بر روی گزینه Pick From Drop-down List کلیک می کنیم. در این صورت منویی باز می شود که کلیه اقلامی که تاکنون در این ستون وارد شده، نشان داده می شود. با انتخاب گزینه مورد نظر از این منو داده مربوطه در سلول انتخاب شده وارد خواهد شد.                                                                                                           

  ايجاد فرم ورود اطلاعات
منظور از فرم ورود اطلاعات، امکانی است که اکسل برای سهولت در وارد کردن داده های لیست ارایه می دهد. برای استفاده از این قابلیت، باید از دستور Form استفاده کنید. این دستور از جمله دستوراتی است که در نوارهای اصلی اکسل وجود ندارد و باید یک دکمه میانبر از آن را در نوار Quick Access اضافه کرد. برای این منظور با کلیک راست بر روی Quick Access، گزینه Customize Quick Access Toolbar را کلیک کنید. سپس از منوی باز شده گزینه More commands را انتخاب کنید.                                                                             در این صورت پنجره Excel Option باز می شود:                                                                            

اکنون از منوی Choose commands from گزینه Commands Not in the Ribbon را انتخاب کنید تا دستوراتی که در نوار اکسل نیستند، در لیست نمایش داده شوند.

از لیست باز شده، گزینه Form را انتخاب کرده و روی دکمه Add کلیک کنید.

اکنون روی دکمه OK کلیک کنید. در این صورت دکمه Form به دکمه های نوار Quick Access اضافه می شود. بازمی گردیم به مثال لیست. یکی از سلول های لیست را انتخاب کرده و روی دکمه Form کلیک کنید تا فرم ورود اطلاعات باز شود.

همانطور که مشاهده می کنید، فرم ورود اطلاعات به ازای هر فیلد، دارای یک کادر متنی یا عددی است که به راحتی می توان اطلاعات رکورد را در آن وارد کرد. در این فرم گزینه New برای ایجاد یک رکورد جدید، گزینه Delete برای حذف رکورد فعلی، گزینه Find Prev برای نمایش رکورد قبلی، گزینه Find Next برای نمایش رکورد بعدی، گزینه Criteria برای مرور رکوردهای خاص و گزینه Close برای بستن فرم ورود اطلاعات استفاده می شود.

مرتب کردن سطر‌ها و ستون های لیست
در اکسل می توان داده های لیست را بر حسب هر یک از فیلدهای ستون ها مرتب کرد. این مرتب سازی می تواند براساس حروف الفبا یا براساس نوع فیلد باشد. فرض کنید می خواهیم لیست زیر را براساس ستون «نام خانوادگی» به ترتیب حروف الفبا مرتب کنیم.
برای مرتب کردن این لیست بر اساس ستون نام خانوادگی، ابتدا یکی از سلول های این ستون را انتخاب کرده و سپس زبانه Data و در بخش Sort & Filter روی دکمه Sort A to Z کلیک می کنیم. از آنجا که اکسل به خوبی ساختار لیست را تشخیص می دهد، رکوردهای آن را براساس ستون «نام خانوادگی» از «الف» تا «ی» مرتب می کند و نتیجه آن لیست زیر خواهد بود. همانطور که در تصویر زیر می بینید، دکمه Sort Z to A نیز حروف را از «ی» تا «الف» مرتب می کند.

 ال فرض کنید لیست فوق را به شکلی نیاز داریم که ابتدا رشته آیتی، و سپس رشته کامپیو‌تر را صورت مرتب شده (براساس نام خانوادگی) نشان دهد. به این ترتیب لازم است لیست را بر اساس دو ستون مرتب کنیم: ابتدا بر اساس ستون رشته تحصیلی و سپس براساس ستون نام خانوادگی.
برای اینکار یکی از سلول های لیست را انتخاب کرده و از زبانه تب Data گروه Sort & Filter روی دکمه Sort کلیک می کنیم تا کادر Sort باز شود.

بخش Sort by ستونی است که مرتب سازی براساس داده های آن انجام می شود. بخش Values نیز به عنوان مبنای مرتب سازی (مقدار سلول، رنگ سلول و...) در نظر گرفته می شود و از بخش Order نیز برای صعودی یا نزولی بودن مرتب سازی استفاده می شود.
اکنون در بخش Sort by ستون مبنای مرتب سازی را مشخص می کنیم. در این منو به دلیل تشخیص لیست توسط اکسل، عناوین ستون ها دیده می شود.

برای تعیین ستون دوم که باید مبنای مرتب سازی قرار گیرد، روی دکمه Add Level کلیک می کنیم. ردیف جدیدی با عنوان Then by به کادر Sort اضافه می شود.

بار دیگر منوی Column را از ردیف Then by باز کرده

در پایان روی دکمه OK کلیک می کنیم. نتیجه مرتب سازی براساس دو ستون «رشته تحصیلی» و «نام خانوادگی»  لیست زیر خواهد شد.

   فیلتر کردن اطلاعات
در لیست های طولانی برای مشاهده داده های خاص می توان از قابلیت Filter استفاده کرده و نمایش اطلاعات را براساس یک یا چند داده خاص محدود کرد. فرض کنید در مثال فوق، لیست متنوعی از رشته های تحصیلی را داشتیم و می خواستیم در این لیست فقط اطلاعات کسانی را که در رشته کامپیو‌تر تحصیل کرده اند، مشاهده کنیم. برای اینکار یکی از سلول های لیست را انتخاب کرده و از زبانه Data بخش Sort & Filter بر روی دکمه Filter کلیک کنید. اگر روی فلش پایین رونده در ستون رشته تحصیلی کلیک کنید، خواهید دید که در بخش Text Filters گزینه Select All انتخاب شده است. با کلیک در کادر انتخاب گزینه Select All انتخاب فعلی لغو شده و می توانید تنها گزینه «کامپیو‌تر» را انتخاب کنید.
پس از کلیک روی دکمه OK، داده های لیست فیلتر شده و تنها رکوردهایی که از «کامپیو‌تر» هستند، نمایش داده می شوند.                                                                                                                     کنترل اعتبار داده‌های ورودی
اکسل قادر است که اعتبار یا درستی داده ها را در هنگام وارد کردن آنها، کنترل کند.
برای اینکار باید قبل از ورود داده ها، برای سلول های مربوطه، «شرایط اعتبار» را تعریف کرد. برای کنترل اعتبار داده های ورودی، از زبانه Data بر روی گزینه Data Validation کلیک کرده و از منوی باز شده گزینه Data Validation… را انتخاب کنید. اکنون کادر Data Validation باز می شود. با استفاده از این کادر می توان شرایط مجاز ورود داده ها، پیغام های ورودی و پیغام های خطا را تعریف کرد.                                                   برای تعریف شرایط مجاز در پنجره فوق، از زبانه Settings استفاده می شود.
گزینه Allow در این قسمت، لیستی از شرایط مختلف را ارائه می دهد.                                                 

در این پنجره، از گزینه Any Value برای دریافت بدون قید و شرط تمام داده ها، از گزینه Whole Number برای دریافت اعداد، از گزینه Decimal برای دریافت اعداد اعشاری، از گزینه List برای دریافت اقلام نامبرده، از گزینه Date برای دریافت تاریخ، از گزینه Time برای دریافت ساعت، از گزینه Text length برای دریافت متن با تعداد کاراکترهای مشروط و از گزینه Custom نیز برای دریافت محتوای برابر با فرمول استفاده می شود.
پس از انتخاب گزینه مناسب از لیست Allow بخش Data در زیر آن فعال می شود. در این بخش می توانید دامنه مجاز را با استفاده از عملگرهای مقایسه ای لیست Data تعیین کنید.

پس از تعریف شرایط مجاز، می توانید یک پیغام تعریف کنید که در حین ورود داده ها، شرایط مجاز را به کاربر یادآوری کند. برای این منظور بر روی زبانه Input Message کلیک کرده و در قسمت Title عنوان پیغام و در قسمت Input message متن پیغام را وارد کنید.

از زبانه آخر (Error Alert) نیز برای نوشتن پیغام خطا استفاده می شود. با تعريف محدوده مجاز داده ها، در صورت ورود داده اشتباه، اين خطا توسط اکسل به كاربر اعلام خواهد شد. برای نوشتن پیغام خطا به دلخواه خود در قسمت Title عنوان پيغام و در قسمت Message Error متن پيغام را وارد كنيد.

                

شکل و عکس در اکسل

                                                                                                                                          کاربران بنا به نیاز خود می توانند در فایل های اکسل خود شکل و عکس قرار دهند. همانند نرم افزار Word، اکسل هم امکانات بسیاری برای درج و ویرایش عکس ها (Pictures)و همینطور شکل ها (Shapes) دارد.

شکل و عکس در اکسل

اشیایی که میتوان به فایل های اکسل اضافه نمود شامل نمودارها (Charts)، شکل ها (Shapes)، عکس ها (Pictures)، سمبل ها (Symbols) و Smart Art ها و Screenshot و Word Art و Equation ها و Clip Art ها میباشد. در اکسل میتوان داده ها را در قالب نمودار (Charts) به نمایش گذاشت، مبحث نمودارها در اکسل به صورت کامل تمام انواع نمودارها و روش ترسیم آنها را مورد بررسی قرار داده است. در اکسل امکان ترسیم نمودارهای Smart Art وجود دارد که در مقاله ای جداگانه توضیح داده شده است.

Clip Art ها در نسخه های اکسل ۲۰۱۰ به بعد از اکسل خارج شده اند و در این مقاله کلیپ آرت (Clip Art) در اکسل ۲۰۰۷ بررسی شده است. Equation و Screenshot از نسخه ۲۰۱۰ به بعد به اکسل اضافه شده اند و در ادامه ی این مقاله معرفی شده اند. همچنین در این مقاله روش وارد کردن و ویرایش عکس و شکل ها در اکسل و نحوه ترسیم فلوچارت ها توضیح داده شده است. د

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

عکس (Picture) در اکسل

در ادامه با نحوه ی وارد کردن عکس ها در اکسل و ویرایش آنها در اکسل ۲۰۰۷ آشنا میشویم،

برای قرار دادن عکس ها در اکسل میتوانید تب Insert و در گروه Illustration بر روی گزینه ی Picture کلیک نمایید. با انتخاب این گزینه شما می توانید عکس دلخواه خود را وارد اکسل نمایید، به عنوان مثال اینکار برای وارد کردن لوگوی شرکت خود در فایل اکسل کاربرد دارد، این کار بسیار ساده است.

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

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

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

برای فشرده کردن عکس (Compress Picture) ، رنگ عکس (Recolor)، روشنایی عکس (Brightness)،وضوح عکس (Contrast)، عوض کردن عکس با عکسی دیگر (Change Picture) و یا از بین بردن کلیه ی ویرایش های انجام شده بر روی عکس (Reset Picture) در اکسل استفاده می گردد.

در قسمت منوی Recolor با انتخاب گزینه ی Set Transparent Color میتوانید یک رنگ از رنگ های موجود در عکس خود را به حالت شفاف (Transparent) در بیاورید، به عنوان مثال اگر در عکس بالا، رنگ سفید لوگو به عنوان Transparent انتخاب گردد، تمام قسمت های سفید رنگ به صورت شفاف درآمده و اطلاعات نوشته شده در زیر آن ها مشخص میشود.

گروه Picture Styles در فرمت عکس اکسل

در این گروه می توانید شکل نمایش عکس خود را عوض کرده (Picture Shapes)، یا برای عکس خود در اکسل حاشیه تعریف نمایید (Picture Border) و یا اینکه به عکس خود Effect دهید، همینطور میتوانید از قالب های پیشنهاد شده اکسل (Picture Styles) برای عکس های خود استفاده نمایید.

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

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

در صورتی که فایل اکسل شما حاوی تعداد زیادی عکس باشد، استفاده از Selection Pan برای ویرایش آنها بسیار مفید خواهد بود.

در گروه Arrange در قسمت Align میتوانید شکل ها و عکس ها ی موجود را مرتب سازی نمایید، در قسمت Group می توانید چند شکل را به هم متصل و یکپارچه کنید و در قسمت Rotate امکان چرخاندن شکل ها و عکس ها در اکسل به شما داده می شود. Bring to Front و Send to back هم برای رو آمدن و یا زیر رفتن شکل ها استفاده می گردند.

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

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

 

شکل (Shape) در اکسل

استفاده از شکل ها در اکسل و Word بسیار کاربردی میباشد، خطوط و کادرهای قرار داده شده در عکس های موجود در این مقاله همگی از تعریف شکل بوجود آمده اند، شکل ها بسیار متنوع و گسترده می باشند. برای دسترسی به شکل ها در اکسل در تب Insert و در گروه Illustration بر روی گزینه Shapes کلیک نمایید.

تنظیمات و تغییر فرمت شکل ها در اکسل همانند عکس ها می باشد که پیشتر توضیح داده شد. با این تفاوت که در تب فرمت مربوط به شکل ها، گروه Insert Shapes در قسمت سمت چپ قرار گرفته که در واقع میانبری برای وارد کردن شکل ها میباشد و گروه Word Art Style هم برای تغییر فرمت نوشته های داخل شکل ها استفاده می گردد.

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

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

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

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

برای لینک کردن یک شکل با یک سلول در اکسل، ابتدا بر روی شکل کلیک نموده و در نوار فرمول اکسل خود عبارتی مشابه A1= را بنویسید، بدیهی است بجایA1 میتوانید سلول دلخواه خود را قرار دهید.

نماد ها (Symbols) در اکسل

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

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

در اکسل ۲۰۱۰ به بعد برای وارد کردن نماد ها در فایل اکسل میتوانیم در تب Insert و در گروه Symbols بر روی گزینه ی Symbol کلیک نماییم و در اکسل نسخه ۲۰۰۷ در گروه Text میتوانیم دکمه Symbol را انتخاب کنیم.

Word Art در اکسل

ورد آرت (Word Art) در اکسل همانند آنچه در نرم افزار Word تجربه کردیم، صرفاً ابزاری برای زیباتر نوشتن عناوین، سرفصل ها و غیره می باشد که کار با آن ها بسیار ساده و قابل درک است. در اکسل در تب Insert و در گروه Text بر روی گزینه Word Art کلیک نمایید این ابزار را در فایل های اکسل خود استفاده کنید.

Clip Art در اکسل

در واقع عکس هایی متشکل از تعداد زیادی شکل میباشند که تصویری نمادین از چیزی یا فعلی و یا بیان حالتی خاص است، تنظیمات مربوط به آن ها همانند تمام موارد فوق میباشد و امری ساده است. این ابزار در نسخه های اکسل ۲۰۱۰ به بعد از این نرم افزار حذف شده است اما همچنان امکان وارد کردن آنها در فایل های اکسل وجود دارد.

در واقع در جایی که کاربران نیاز به قرار دادن تصویری نمادین برای ارسال مفهومی در فایل های اکسل خود دارند می توانند از کتابخانه ی بزرگ Clip Art اکسل استفاده نمایند، به عنوان مثال در کنار شماره تماس خود تصوری نمادین از یک تلفن قرار دهید. کافیست در تب Insert و در گروه Illustration بر روی گزینه Clip Art کلیک نمایید و در کادر جستجو عبارت Phone و یا عبارت مشابه را بنویسید.

 Equation در اکسل

در نسخه های اکسل ۲۰۱۰ به بعد میتوان در اکسل دقیقاً مانند Word معادله نوشت، برای نوشتن معادلات ریاضی و مهندسی در اکسل و Word تنها کافیست بر روی گزینه ی Equation در گروه Symbols کلیک نماییم، کار کردن با این ابزار دقیقاً مانند Word میباشد و کمی تمرین میتواند شما را در استفاده از آن حرفه ای نماید.

Screenshot در اکسل

امکان گرفتن Screenshot از صفحه ی کامپیوتر شما در اکسل نسخه های ۲۰۱۰ به بعد اضافه شده است، این کار دقیقاً مانند Print Screen در ویندوز میباشد که حتماً قبلاً با آن کار کرده اید، با استفاده از ابزار Screenshot در اکسل میتوانیداز صفحه نمایش کامپیوتر خود عکس بگیرید و آن عکس را مستقیماً وارد فایل اکسل خود نمایید.

 

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

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

حالا به وسیله اطلاعات خام یک نمودار از نوع میله‌ای یا خطی می‌کشیم.                                             اکنون مقدارهای تجمعی با نوعِ نمودار مناسب نمایش داده شده است. روی خط مقدارهای دوره‌ای کلیک کنید تا انتخاب شوند؛ می‌دانید که وقتی انتخاب شده باشد با گیره‌های کوچکی که دور همه نمودارها نمایش داده می‌شوند می‌شوداآن را تشخیص داد. بعد از آن از تب Design اکسل 2007 روی Change Chart Type کلیک کنید. با این کار یک کادر محاوره باز می‌شودو انواع نمودارها نشان میدهد ؛ یکی از نمودارهای میله‌ای مناسب را انتخاب کنید و کادر محاوره را تایید کنید تا از آن خارج شوید. توجه داشته باشید که هر نموداری رانمیشود با نمودار دیگرترکیب کرد؛ مثلا نمی‌توانید یک نمودار دو بعدی و یک نمودار سه‌بعدی را ترکیب کنید.

نمودار ترکیبی ساخته شد. ولی کارما تموم نشده است؛

کارهایی که انجام شده:
1. کمرنگ کردن خطوط راهنمای افقی برای افزایش تمرکز بیننده
2. متناسب کردن پررنگی و کمرنگی عناصر
3. تنظیم اندازه فونت‌ها
4. تنظیم حداکثر مقدار محور عمودی

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

روی مقدارهای دوره‌ای کلیک راست کنید و از منویی که باز می‌شود Format Data Series را انتخاب کنید. در قسمت Series Options کادر محاوره دو گزینه Primary Axis و Secondary Axis است که پیش‌فرض همان اولی است. دومی راانتخاب کنیدو کادر محاوره را تایید کنید تا ازآن خارج شوید:

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

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

 2) بعد از ذخیره کردن این فایل، ویزیو را باز کرده و create organization chart را می زنیم. سپس از تب Org chart گزینه import را کلیک میکنیم.

3)بعد از انتخاب گزینه اول و زدن next به مرحله بعد می رویم. بعد از انتخاب گزینه دوم (یعنی اینکه تعیین میکنیم که فایل دیتابیس ما از نوع اکسل است) گزینه next را میزنیم. وارد مرحله بعد میشویم. در جدول ادرس فایل اکسلی که قبلا ذخیره کرده ایم از ما خواسته می شود. 

4) بعد از زدن next به مرحله بعد می رویم. حالا باید تعیین کنیم که کدام ستون گزارش دهنده است و کدام ستون گزارش گیرنده (نام ستون گزارش دهنده را در Name و سرستون مربوط به گزارش گیرنده را در Report to وارد می کنیم) .
 5) بعد از زدن Next و ورود به مرحله بعد، تعیین میکنیم که کدام ستون ها از فایل مورد نظر می خواهیم نمایش داده شود،

6) بعد از زدن گزینه Finish نمودار سازمانی ساخته خواهد شد

خطایی ناشناخته از اکسل، چرا جمع اعداد صفر می‌شود؟

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

علائم ظاهری این خطا دراکسل و نحوه بیان آن از طرف شما

* هر بار که اکسل را باز می‌کنم یک پیغام می‌دهد و با OK کردن نیز رفع نمی‌شود و بار دیگر نشان داده می‌شود.
* یک فلش آبی رنگ روی صفحه اکسل می‌آید و اصلا انتخاب و پاک نمی‌شود.
* نتیجه فرمول SUM عدد صفر می‌شود و این در حالی است که  کلی عدد در محدوده Sum داریم.

 

توضیح Circular Reference در Excel

 فرض کنید که در یک اداره رفته‌اید و باید نامه‌ای امضا شود، آقای کارمند الف می‌گوید " نامه شما ابتدا باید به  تایید آقای جیم برسید " و وقتی که به اتاق آقای "جیم" می‌روید، می‌گوید "این نامه ابتدا باید توسط آقای الف کارشناسی و تایید شود".
و نتیجه این کار پاس‌کاری شما یا قرار گرفتن در یک "دور باطل" یا به قول ما کامپیوتری‌ها Loop خواهد بود که در اکسل به این حالت Circular Reference می‌گویند.

 

Circular Reference در Excel چگونه ایجاد می‌شود

در خانه B2 فرمولی را بنویسید که اشاره به خانه E2 داشته باشد (مهم نیست چه فرمولی ، اما در آن حتما E2 آمده باشد) سپس در خانه E2 فرمولی را بنویسید که اشاره به خانه B2 داشته باشد.
بعد زدن کلید Enter در سلول دوم اکسل بلافاصله پیغامی نشان می‌دهد و اگر کلید OK را بزنید Help نرم افزار اکسل باز شده و Circular Reference را توضیح می‌دهد.

آوردن فلش آبی رنگ

بعد اینکه این حالت را ایجاد کردید، اگر خانه E2 را ویرایش کنید (روی E2 کلید F2 را بزنید و سپس Enter) این فلش به صورت خودکار نمایش داده می‌شود و بیانگر ایجاد  Circular Reference دراکسل است.                   

خطای هنگام باز شدن این فایل

این فایل را ذخیره کنید و ببنید و سپس باز نمایید، مشاهده می‌کنید که در هر بار بازشدن فایل پیغام خطای Circular Reference نمایش داده می‌شود.

روش دیگری برای تشخیص Circular Reference

هموار در Status Bar می‌توانید آدرس اولین سلولی که دچارCircular Reference  شده است را ببنید. که به محض رفع کردن خطای این سلول ، آدرس سلولهای بعدی را نشان می‌دهد.

  

روش پیدا کردن همه Circular Reference

از ریبون (به نوار ابزار Excel 2007 ریبون می‌گویند) گزینه Formula انتخاب کنید

نتیجه فرمولهایی که به سلول‌های Circular Reference اشاره دارند، همواره صفر است

در مثال زیر سلول C5 دچار CR شده است (فرمول آن در سلول کناری برای راهنمایی به صورت متنی کمرنگ نوشته شده) و همانطور که می‌بینید نتیجه سلول C9 که جمع سلولهای بالایی است عدد صفر شده است و این به دلیل خطای CR است.

 تا اینجا با مفهوم Circular Reference دراکسل آشنا شده‌اید و حال به نحوه رفع این خطای آشنا خواهیم شد.

روش رفع خطا

دو راه کلی برای رفع این خطا وجود دارد که راه اول پاک کردن یا اصلاح فرمولی است که باعث CR شده است که سلولی که باعث CR شده است را ویرایش کرده و فرمول آنرا طوری اصلاح می‌کنیم که باعث Loop نشود.
راه حل دیگر فعال کردن گزینه Iteration در Excel Options است. در این وضعیت ما CR را کنترل می‌کنیم و نه حذف. بدین ترتیب که به اکسل می‌گوییم که اگر در جایی دچار Loop شد، این Loop را 100 بار اجرا کن (این حلقه را 100  بار ادامه دهید) و سپس کار را تمام کنید

برای فعال کردن وضعیت Iteration در Excel Options گروه Formula و سپس Enable iterative Calculation را در حالت فعال قرار می‌دهیم و تعداد دفعات اجرای را تعیین می‌کنیم.

استفاده از این تکنیک برای ساختن Loop در اکسل اصلا توصیه نمی‌شود زیرا شما باید Calculation رادر حالت Manual قرار دهید و ... و در ضمن ابزارهای کنترلی برای خارج شدن از Loop به سختی ساخته می‌شوند و این در حالی است که در VBA این کار به سادگی با دستوراتی مانند DO-LOOP  /  FOR-NEXT قابل اجراست و اگر شما در کتابهای مرجع Excel و یا اینترنت جستجو کنید خواهید دید که از این قابلیت سخنی به میان نرفته است و دلیل آن همان است که ذکر شد

                         

ابزار ساخت فهرست Sheet ها در اکسل

                                                                                                                                    بسیاری از فایلهای Excelحاوی ده‌ها worksheet می‌باشند که کاربران روزانه صدها کلیک برای جابجایی بین worksheet‌های اکسل انجام می‌دهند و معمولا هنگامی که تعداد worksheet‌های یک فایل اکسل زیاد می‌شود ، یافتن یک worksheet نیز کاری سخت خواهد شد .

یکی از روش‌های بسیار مفید برای این دسته از فایلها ساخت فهرستی از تمامی worksheet‌ها است که با کلیک بر روی هر نام یک worksheet، آن worksheet فعال شود. اینکار با ابزار Hyperlink در اکسل به سادگی قابل انجام است (کلید میانبر ساخت و ویرایش Hyperlink در اکسل Ctlr+k است.)

 

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

این برنامه علاوه بر ساخت فهرست Sheetها و لینک کردن آنها به Sheet مربوطه ، به صورت جداگانه در هر Sheet فایل شما یک دکمه در خانه A1 قرار می‌دهد که با کلیک بر روی آن به "فهرست" باز می‌گردید . در شکل زیر دکمه قرمز رنگ "Home" اینکار را انجام می دهد. در ضمن این دکمه در Print چاپ نخواهد شد در عکس زیر دکمه قرمز رنگ با کلمه Home را می توانید مشاهده کنید.

 

 

تمامی این برنامه به زبان ویژوال بیسیک اکسل نوشته شده است و در صورت نیاز می توانید آنرا تغییر و یا توسعه دهید. برای وارد شدن به محیط ویژوال بیسیک اکسل کلید Alt+F11 را بزنید

ویژگی‌های نسخه 2

1- یک پنجره به کاربر نمایش داده می شود که در آن می تواند دکمه‌های Home (بازگشت به فهرست) را ایجاد / حذف نماید.

2- کاربر می تواند هر دکمه / عکس / آیکون دلخواه را به عنوان دکمه خانه (بازگشت به فهرست) را همه شیت های فایل اضافه کند.

 

 

نحوه اجرای ابزار ساخت فهرست شیت‌های اکسل

1- ابتدا فایل این ابزار را که از قسمت فایلهای ضمیمه دانلود کرده اید، را باز کنید.

2- مطمئن شوید که ماکروی آن را فعال کرده اید.

3- فایل اکسل مورد نظر خود را که می خواهید برای آن "فهرستی" بسازید را باز کنید.

هر دو فایل (فایل که دانلود کرده اید و فایل خود که می خواهید برای آن فهرست بسازید) باید همزمان باز باشند.

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

4- کلید ALT+F8 را بزنید تا در پنجره ای که باز خواهد شد، لیست همه ماکروها را مشاهده نمایید.

5- گزینه "Build_Sheet_Navigator" را انتخاب کنید و کلید Run را بزنید تا ماکروی "ساخت فهرست شیت ها" اجرا شود .

6- حال در پنجره باز شده تنظیمات مورد نظر خود را با زدن هر دکمه اعمال کنید.

 

 

نحوه استفاده 

1- با زدن دکمه "ایجاد فهرست" برای شما یک شیت ایجاد می شود و در آن لینک تمامی شیت‌ها را خواهید داشت.

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

 

2- درج دکمه‌های خانه

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

قبل از کلیک بر روی دکمه خانه مطمئن شوید که در شیت "فهرست" هستید. 
زیرا این برنامه به گونه ای طراحی شده است که در هر شیتی که باشید "دکمه های خانه" به صورت خودکار به آن شیت لینک می شوند.

در هر زمانی می توانید دکمه های خانه را به راحتی حذف نمایید . برای اینکار کافیست که روی دکمه "حذف دکمه‌های خانه" کلیک کنید. 

 

3- ایجاد دکمه‌های خانه دلخواه

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

الف) یک عکس / آیکون / شکل دلخواه خود را در یک شیت داشته باشید.

ب) آن عکس / آیکون / شکل را به شیت فهرست خود لینک کنید. 

ج) آن عکس / آیکون/ شکل را انتخاب نمایید. 

د) برنامه را اجرا کنید و سپس گزینه "کپی دکمه من" را کلیک کنید تا این دکمه در همه شیت ها به راحتی کپی گردد.

 

 به راحتی می توانید با گزینه"حذف دکمه من" تمامی دکمه هایی را که در شیت ها ایجاد شده است را حذف کنید.

 

تابع جدید AGGREGATE در EXCEL 2010

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

بنابراین ابتدا تابع SUBTOTAL را در EXCEL بررسی می نماییم:

 تابع SUBTOTAL در فرمول نویسی:

کار تابع SUBTOTAL انجام عملیاتهایی مانند جمع، ضرب، میانگین، شمارش و ... است اما با ویژگیهای زیر:

• این تابع به فیلتر حساس است: یعنی هنگامیکه سطرها فیلتر می شوند آنها را محاسبه نمایند در حالیکه توابع دیگر مانند SUM این ویژگی را ندارند.
• این تابع به سطرهای HIDE شده حساس است: می توان تابع SUBTOTAL را طوری تنظیم کرد که سطر و ستون ها که در حالت مخفی قرار دارند را محاسبه کند و یا نکند.
• این تابع به خودش حساس است: یعنی اگر به این تابع آدرس سلولهایی را بدهیم که برخی از آنها دارای فرمول SUBTOTAL هستند، آن سلولها را محاسبه نمی کند

یعنی سلولهای A1:A10 را جمع بزنید (کد 109 یعنی SUM)   / اگر فیتلر بودند آنهایی که نمایش داده نمی شوند را محاسبه نکنید / اگر در A1:A10 به تابع SUBTOTAL  برخورد کردیید آن را نیز محاسبه نکنید.

 

تابع AGGREGATE در فرمول نویسی:

این فرمول در Excel تمامی ویژگیهای بالا را دارد علاوه بر اینکه:

•  کارهای بیشتری را نسبت به تابع SUBTOTAL انجام دهد مثلا : محاسبه میانه و یا کوچکترین SMALL و بزرگترین LARGE
• می تواند به خطاهای EXCEL حساس باشد:

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