مخفی و آشکار کردن سطرها، ستون‌ها و برگه‌ها در اکسل

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

روش مخفی کردن:

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

سپس از سربرگ Home به کادر گروه Cells مراجعه کرده و بر روی گزینه Format کلیک کنید و از کادر باز شده آبشاری آن گزینه Hide & Unhide را انتخاب نمایید. با کلیک بر روی این گزینه، کادری باز خواهد شد که گزینه‌های آن و کارکرد هرکدام عبارتند از:

• گزینه های مربوط به مخفی کردن سطر، ستون و برگه انتخاب شده:

Hide Rows: با انتخاب این گزینه سطر(های) انتخاب شده به حالت مخفی تغییر می‌کنند. البته پس از انتخاب سطر(های) مورد نظرتان، با راست کلیک کردن هم می‌توانید گزینه Hide را مشاهده کنید و پس از کلیک بر روی آن، سطر و یا سطرهای انتخاب شده را مخفی کنید.

Hide Columns: با انتخاب این گزینه ستون‌ (های) انتخاب شده به حالت مخفی تغییر می‌کنند. مشابه حالت قبل گزینه Hide را منوی راست کلیک نیز می‌توانید مشاهده کنید.

Hide Sheet: با انتخاب این گزینه قادر خواهید بود تا برگه سند (صفحه اکسل) انتخاب شده را به حالت مخفی درآورید.

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

روش آشکار کردن:

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

• گزینه های مربوط به آشکار کردن سطر ستون و برگه پنهان شده:

Unhide Rows: این گزینه برای آشکار کردن سطر (های) پنهان شده کاربرد دارد.

Unhide Columns: از این گزینه برای آشکار کردن ستون (های) انتخاب شده استفاده می‌گردد.

...Unhide Sheet: با انتخاب این گزینه قادر خواهید بود تا برگه پنهان شده را آشکار سازید.

• آشکار کردن برگه‌ی مخفی شده در اکسل:

اگر در یک فایل برگه‌ای مخفی شده باشد گزینه Unhide Sheet حالت فعال دارد و براحتی پس از انتخاب این گزینه، پنجره‌ی Unhide ظاهر می‌شود که برگه‌های مخفی شده را نشان می‌کند و با انتخاب هر یک از آن‌ها و کلیک روی گزینه OK آن برگه آشکار می‌شود.

اما اگر برگه‌ای مخفی نشده باشد گزینه Unhide Sheet حالت غیر فعال دارد.

• آشکار کردن ردیف یا ستون مخفی شده در اکسل:

اگر می‌خواهید که تمام ردیف‌ها یا ستون‌های مخفی شده در یک برگه آشکار شوند کافیست با استفاده از شرتکات Ctrl A و یا با کلیک روی دکمه Select All، کل برگه را انتخاب کنید.

سپس با کلیک روی گزینه‌های Unhide Rows و Unhide Column تمام ردیف‌ها یا ستون‌های مخفی شده در برگه‌ی فعال آشکار می‌شوند. اما اگر می‌خواهید ردیف یا ستون مشخصی که مخفی شده است را آشکار کنید شرایط کمی متفاوت است.

اگر ستون یا ستون‌های مخفی شده به غیر ستون اول (ستون A) باشد مثلا ستون B، حال با انتخاب ستون‌های قبل و بعد از آن یعنی ستون‌های A و C و سپس انتخاب گزینه Unhide Columns ستون یا ستون‌های مخفی شده آشکار می‌شوند. البته از طریق منوی راست کلیک هم می‌توانید گزینه Unhide را انتخاب کنید.

اما اگر ستون مخفی شده ستون اول باشد چون قبل از آن ستونی وجود ندارد نمی‌توان آن را انتخاب کرد. برای حل این مشکل در قسمت نشان داده شده (Name Box) عبارت A1 را تایپ  فعال شود Unhide Columns را انتخاب کنید.

1- با استفاده از کد تغییر فرمت در اکسل

سلول یا سلول‌هایی که می‌خواهید اعداد درون آن‌ها فارسی شود را انتخاب کنید و سپس راست کلیک کرده و گزینه Format Cells را انتخاب کنید تا پنجره Format Cells باز شود. در قسمت Custom، یکی از کدهای زیر را وارد کنید و روی Ok کلیک کنید.

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

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic for applications window ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

سپس  کلید F5 را فشار دهید. پس از این کار پنجره Tarfandha-En2Faظاهر می‌شود.

محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا نتیجه زیر حاصل شود.

• تبدیل اعداد فارسیبه انگلیسی در excel :

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

1- با استفاده از کد تغییر فرمت

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

2- با استفاده ماکرو

کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic for applications window ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

سپس  کلید F5 را فشار دهید تا پنجره Tarfandha-Fa2Enظاهر شود. مشابه حالت قبل محدوده مورد نظرتان را انتخاب کنید و روی Ok کلیک کنید تا تغییرات انجام شود.

توجه: اعداد درون کد ماکرو در واقع کد یونیکد اعداد فارسی و انگلیسی هستند

در نرم‌افزار Microsoft Excel امکان Copy و Paste کردن سلول‌ها در محیط نرم‌افزار به آسانی وجود دارد. اما فرض کنید قصد دارید سلول‌ها را در محیط یک‌ نرم‌افزار دیگر نمایش دهید. با ما همراه باشید

 EXCEL  امکان کپی کردن سلول‌ها در قالب یک تصویر را فراهم می‌کند. در این   انجام این کار می‌پردازیم.

 کپی کردن سلول ها به عنوان عکسل در اکسل

• ابتدا فایل مورد نظر خود را در Microsoft Excel فراخوانی کنید.
• سپس سلول‌های مورد نظر خود را به حالت انتخاب دربیاورید.
• اکنون در تب Home، بر روی فلش کنار دکمه‌ی Copy کلیک کنید.
• حالا در منوی باز شده بر روی Copy as Picture را انتخاب کنید.

• در پنجره‌ی باز شده از فعال بودن گزینه‌های As show on screen و Bitmap اطمینان حاصل کرده و سپس بر روی دکمه‌ی OK کلیک کنید.
• با این کار تصویر سلول‌های انتخابی به Clipboard کپی شده است.
• اکنون می‌توانید درون نرم‌افزار مورد نظر خود (نظیر Paint یا Microsoft Word)، این تصویر را Paste کنید.

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

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

 چگونگی تغییر رنگ خطوط راهنمای اکسل

برای تغییر رنگ خط های راهنما (Gridlines) ابتدا بر روی تب "File" کلیک کنید.

در منوی سمت  چپ صفحه ای که باز می شود بر روی بخش “Options” کلیک کنید.

پنجره ای به اسم “Excel Options” باز می شود. بر روی بخش “Advanced” در لیست سمت چپ کلیک کنید.

در تنظیماتی که باز می شود به قسمت “Display options for this worksheet” بروید. بر روی دکمه ی کنار “Gridline color” کلیک کرده و رنگ دلخواه خود را انتخاب نمایید. همچنین توجه داشته باشید که تیک گزینه ی “Show gridlines” فعال باشد.

توجه: رنگ خطوط راهنما می تواند برای هر کاربرگ (worksheet) موجود در کارنامه ی (workbook) شما متفاوت باشد. بصورت پیش فرض کاربرگی که اخیراً انتخاب شده است برای تغییر رنگ در نظر گرفته می شود. برای اعمال تغییرات روی کاربرگ های دیگر کافیست بر روی کشوی باز شونده ای که در کنار عنوان این قسمت دارد کلیک کنید..

خطوط راهنمای کاربرگ شما با رنگی که خودتان انتخاب کرده اید ظاهر می شود..

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

تکنیک اول:

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

برای این منظور ابتدا جدول مورد نظر را ایجاد می کنیم.

برای ایجاد شماره ردیف خودکار از فرمول ("",=IF(B3””,MA $A$1:A2)+1= استفاده می کنیم. قسمت MA $A$1:A2)+1 برای تولید اعداد اتوماتیک استفاده می شود. در اینجا ما قصد داریم چنانچه در ستون شرح یعنی سلول B3 مقداری را وارد کردیم آنگاه شماره ردیف ایجاد گردد و اگر ستون شرح خالی بماند شماره ردیف نیز خالی باشد. پس ما در اینجا فقط شرح اقلام را نوشته و مشاهده می گردد که به محض وارد کردن شرح، شماره ردیف نیز ایجاد می گردد.

توجه داشته باشید در فرمول ذکر شده سلول A1 به صورت مطلق اما سلول A2 به صورت نسبی تعریف شده است.                                                                                                                         خطوط جدول (Border) در اکسل

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

برای این منظور از سربرگ Conditional Formatting استفاده می کنیم. ابتدا تب Conditional Formatting را باز کرده و بر روی New Rule…  کلیک می کنیم. پنجره New Formatting Rule ظاهر می شود.

از قسمت   Select a rule type

عبارت

  Use a formula to determine which cells to format

را انتخاب کرده و در کادر پایین Format values where this formula is true شرط مورد نظر را به این صورت تایپ کرده""$A3=  (یعنی اگر سلول A3 خالی نباشد) و بر روی دکمه Format کلیک می کنیم. پنجره format cell در اکسل باز می شود. از تب Border دکمه Outline را انتخاب کرده و بر روی Ok کلیک می کنیم.

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

ویژگی مرتب (sort) کردن در برنامه Excel این محدودیت را دارد که اجازه مشخص کردن بیش از سه داده برای مرتب کردن (سورت کردن) را نمی دهد. در بیشتر موارد همین سه داده کافیست اما شرایطی پیش می آید که امکان مرتب کردن با بیش از سه داده بسیار مفید خواهد بود. در این قسمت راهی برای دور زدن این محدودیت به شما نشان داده می شود.
برای این مثال، فرض می کنیم داده های مرتبطی در ستون های A، B، C، D و E داریم و می خواهیم آن ها را ابتدا بر طبق ستون A، سپس B، سپس C، سپس D و پس از آن E مرتب کنیم. برای انجام این کار، لازم است امکان سورت کردن وارونه (Backward) را داشته باشیم. به عبارت دیگر، ابتدا بر طبق آخرین مورد سورت کرده و سپس به ترتیب تا اولین داده سورت می کنیم.
ستون های A تا E را انتخاب کرده و سپس مسیرData → Sortرا دنبال کنید. سورت کردن را با این ترتیب مشخص کنید که ابتدا ستون C، پس از آن D و سپس E مرتب شوند. روی sort کلیک کنید. اکنون ستون های A تا E را انتخاب کرده و مسیر Data → Sort را دنبال کنید. این بار ابتدا ستون A و سپس ستون B را مرتب کنید. روی Sort کلیک کرده و پس از آن همه چیز مرتب می شود. با این کار برنامه Excel به جای سه مورد داده، مرتب کردن را با پنج مورد داده انجام می دهد.

اگر می خواهید این کار به صورت خودکار انجام شود، می توانید از یک ماکرو استفاده کنید که هم داده های انتخابی را سورت کرده و هم بر اساس فرمت کردن ردیف اول ستون های انتخاب شده، تخمین می زند که داده های شما دارای عنوان ستون هستند یا نیستند. اگر عنوان ها Bold باشند، برنامه Excel تشخیص می دهد که آن ها عنوان ستون ها هستند و مرتبشان نمی کند. در عوض، از اولین ستون سمت چپ تا آخرین ستون سمت راست، تا 256 ستون، مرتب می شود. کُد ماکرو مورد نیاز شما باید در مدول استاندارد قرار داده شود. برای رفتن به این قسمت، مسیر Tools → Macro → Visual Basic Editorرا دنبال کرده (Alt/Options-F11)، سپس Insert → Module را انتخاب و کد زیر را وارد کنید:
 Sub SortBy )
Dim l As Long
For l = Selection.Columns.Count To 1 Step -1
    Selection.Sort Key1:=Selection.Cells(2, l), _
     Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next l
End Sub
برای برگشتن به فایل، روی آیکون Excel کلیک کرده یا Alt+Q را فشار دهید. با یک بار وارد کردن این کد می توانید در هر بار استفاده از Excel، تعداد ستون بیشتری از حد استاندارد را مرتب کنید.

مرتب کردن تصادفی (Random)در اکسل
شما می توانید با استفاده از برنامه ی Excelسه برندهیکم، دوم و سوم را به طور تصادفی (random) و از یک لیست دلخواه در صفحه ی گسترده ی خودتان انتخاب کنید. آسان ترین و بهترین روش برای این کار استفاده از تابع RAND و ترکیب آن با امکانات مرتب کردن (سورت کردن) است.
فرض کنیم یک جدول سه ستونی در صفحه گسترده ی خود دارید که از ستون Bشروع شده و به ترتیب شامل نام، سن و شماره شناسنامه است. می توانید تابع RAND در اکسل را در خانهA2 قرار داده و در خانه های پایینی آن به تعداد دلخواه، تا پایان جدول، کپی کنید. به محض انجام این کار، هر خانه در ستون A دارای تابع RAND خواهد بود که به شکل خودکار (اتوماتیک) یک عدد تصادفی (رندوم) را بر می گرداند که با استفاده از آن می توان جدول را سورت کرد. به عبارت دیگر، می توان ستون های A، B، C و D را به ترتیب صعودی یا نزولی و با استفاده از ستون A، مرتب کرد که به این ترتیب سه داده اول، سه برنده ی مورد نظر خواهند بود.
تابع RAND یک تابع به اصطلاح نا پایدار است که با انجام هر تغییری در فایل، به عنوان مثال وارد کردن داده ها در هر قسمت یا تکرار یک سری محاسبات با فشار دادن F9، محاسبات را دوباره انجام می دهد. پس بهتر است قبل از هر کار دیگر، برنده ها را در جایی دیگر یادداشت کنید.
البته می توانید از این خصوصیت تابع RAND به نفع خودتان استفاده کرده یک ماکرو را ثبت کنید که داده هایی را بی درنگ بعد از هر محاسبه مجدد، مرتب کرده  و تابع RAND را مجبور کند تا به مجموعه دیگری از اعداد رندوم برگردد. پس از آن شما می توانید این ماکرو را به یک دکمه پیوست (attach) کرده و هر بار که بخواهید سه برنده را نشان دهید، فقط باید روی همان دکمه کلیک کرده و سه نام بالایی را انتخاب کنید.
برای مثال، فرض کنیم در ستون های B، C و D داده هایی داریم و در ردیف 1، عنوان های ستون ها را قرار داده ایم. در ابتدا، عنوان RAND را در خانهA1 قرار می دهیم. عبارت =RAND() را در خانهA2 وارد کرده و به تعداد نیاز در خانه های پایین آن کپی می کنیم. سپس هر خانه را انتخاب و مسیر Developer → Code → Record Macro (در نسخه های قبل از 2007، مسیر Tools → Macro → Record New Macro …) را دنبال می کنیم.
ستون های A، B، C و D را انتخاب کرده و F9 را فشار می دهیم (برای محاسبه ی مجدد در Mac از علامت Office استفاده کنید). با دنبال کردنSort & Filter options → Data → Sort، داده ها بر طبق ستون A مرتب می شوند. پس از آن باید ثبت کردن ماکرو را متوقف کنیم.
سپس، مسیر Control Options → Developer → Insert (برای نسخه های قبل از 2007، مسیر View → Toolbars → Forms) را دنبال می کنیم. یک دکمه را از نوار ابزار Forms انتخاب کرده و در یک جای دلخواه صفحه (worksheet) قرار می دهیم. ماکرویی که ثبت کرده ایم را به این دکمه اختصاص داده و روی OK کلیک می کنیم (اگر می خواهید می توانید نام دکمه را از Button 1 به هر نام دیگری تغییر دهید).
می توانید ستون A را انتخاب و آن را مخفی (Hide) کنید. لزومی ندارد که هر کاربری این ستون را ببیند. هر بار که روی این دکمه کلیک کنید، داده های شما به طور تصادفی مرتب شده و تنها کافیست مطابق شکل 6، سه داده ی بالایی (برنده ها) را بخوانید.
 تابع RAND در Excel 2003 و Excel 2007 یک ایراد بزرگ دارد. اگرچه در فایل Help این برنامه ها به روشنی بیان شده که عدد تصادفی برگشت داده شده بین 0 و 1 خواهد بود، همیشه در مواردی که این تابع برای چندینخانه استفاده می شود، چنین نیست. در برخی موارد تابع RAND عددی کمتر از 0 را بر می گرداند. در سایت http://support.microsoft.com/default.aspx?kbid=828795، دلیل Microsoft برای تغییر الگوریتم آورده شده است.
دستکاری (Manipulate) داده ها با فیلتر پیشرفته (Advanced Filter)
اگر با ابزار AutoFilter برنامه ی Excel آشنایی دارید، با محدودیت های آن نیز آشنا هستید. اگر به دستکاری داده ها در مقیاس وسیعی نیاز دارید، استفاده از ابزار فیلتر پیشرفته (Advanced Filter)، همان راهی است که باید انتخاب کنید.
با تمام محدودیت ها، AutoFilters یک راه مناسب برای نمایش داده هایی است که در یک محدوده ی مشخص قرار دارند (در معیارهای مشخصی صدق می کنند). اگرچه در برخی موارد نمی توانید با استفاده از امکانات استاندارد AutoFilters، اطلاعات مورد نظرتان را جمع آوری کنید. ابزار Advanced Filter که یکی از امکانات متنوع و چند کاره در Excel است، امکان دستکاری بیشتر در داده ها را برای شما فراهم می کند. وقتی از ابزار Advanced Filter استفاده می کنید، جدول شماباید در فرمت جدول کلاسیک تنظیم شده باشد.
برای استفاده از ابزار Advanced Filter لازم است یک کپی از عنوان های ستون های جدول خود را در جایی بالای داده ها داشته باشید. همیشه باید دست کم سه ردیف خالی بالای جدول داده ها داشته باشید. برای اطمینان از اینکه عنوان های شما به درستی یکسان بوده و بدون توجه به تغییر عنوان های ستون ها یکسان باقی می مانند، همیشه عنوان ستون ها را با یک فرمول مرجع ساده مانند =A4 که در آن A4 شامل یک عنوان ستون است، ارجاع بدهید. این فرمول را به تعداد عنوان های ستون موجود در جدول خود کپی کنید. این فرمول نویسی ساده شما را از پویا (دینامیک) بودن عنوان ها در فیلتر پیشرفته مطمئن می کند. درست زیر این عنوان ها، معیارهای استفاده از فیلتر پیشرفته را قرار دهید. برای اطلاع بیشتر به قسمت Advanced Filters Criteria در Excel Help مراجعه کنید.
در زمان کار با فیلتر پیشرفته به خاطر داشته باشید که برای دو یا چند معیاری که به طور مستقیم در زیر عنوان قرار گرفته اند باید از OR استفاده کرد. اگر می خواهید از AND استفاده کنید، عنوان ستون ها و معیار باید دو بار، پهلو به پهلو، ظاهر شوند. شکل 7 چگونگی استفاده از عملگر OR و شکل 8 چگونگی استفاده از عملگر AND برای فیلتر کردن داده ها را نشان می دهد.
استفاده ی ساده ای از فیلتر پیشرفته در هر دو مثال نشان داده شده است و در هر دو مورد می توان به جای فیلتر پیشرفته از AutoFilter استفاده کرد. در ادامه مثال هایی زده می شوند که در آن ها امکان استفاده از AutoFilter وجود ندارد و باید از فیلتر پیشرفته استفاده شود.
توجه به این نکته مهم است که در زمان استفاده از یک فرمول برای معیار، نباید در بالای معیار از یک عنوان یکسان با داده های داخل جدول استفاده کرد. برای مثال اگر لیستی از داده های عددی در ستون A دارید که از خانه A5 شروع می شود (یعنی خانهA4 عنوان ستون است) و می خواهید تمام عدد های بیشتر از متوسط را از آن خارج کنید، باید از معیاری مانند:
 =A5>AVERAGE($A$5:$A$500)
استفاده کنید. اگر معیار در خانهA2 قرار گرفته، محدوده ی داده عبارت از $A$1:$a$2 است اما $A$1 نمی تواند شامل عنوان یکسانی با آنچه برای یکی از لیست ها استفاده شده، باشد. یا باید خالی بوده یا به کلی متفاوت باشد.
همچنین توجه به این نکته نیز اهمیت دارد که تمام فرمول های مورد استفاده باید بازگشت TRUE یا FALSE داشته باشند. محدوده ی تابع متوسط با اضافه کردن علامت دلار ($) قطعی می شود در حالی که ارجاع به خانه A5، یک مرجع وابسته (نسبی) است. توجه به این نکته ضروری است چرا که در زمان استفاده از فیلتر پیشرفته (Advanced Filter)، برنامه Excel، خانهA5 را یک مرجع نسبی در نظر گرفته و لیست را در هر زمان به اندازه ی یک ورودی پایین برده و آن راTRUE یا FALSE نشان می دهد. اگر TRUE برگردانده شود، متوجه می شود که باید نشان داده شود اما اگر FALSE برگردد، در معیار مورد نظر قرار نگرفته پس نشان داده نمی شود.
فرض کنیم چند نام در محدوده ی SAS3:$A$500 و با عنوان های شروع شده از A4 تکرار شده اند. همچنین فرض کنیم تعدادی از عنوان ها نیز چند بار تکرار شده اند. برای جدا کردن نام هایی که بیشتر از یک بار تکرار شده اند، باید از Advanced Filter و فرمول زیر به عنوا معیار استفاده کنید:
 =COUNTIF($A$5:$A$500,A5)>1
با استفاده از Advanced Filterو گزینه ی Copy to Another Location، لیستی که تازه ایجاد شده، شامل تمامنام هایی خواهد بود که بیشتر از یک بار در لیست اصلی تکرار شده اند (شکل 9 را ببینید). تعداد زیادی از این نام ها چندین بار تکرار شده اند اما می توانید به سادگی این لیست جدید را با استفاده ی مجدد از Advanced Filter، فیلتر کرده و این بار ازUnique Records Onlyاستفاده کنید (شکل 10 را ببینید). این بار نام هایی به شما نشان داده خواهد شد که بیش از یک بار در لیست آمده اند.
کسانی که از Advanced Filter استفاده می کنند این سوال را مطرح می کنند که چگونه می توان Excel را مجبور کرد که داده ها را بدون هیچ کم و کاستی با معیاری که آن ها مشخص کرده اند، فیلتر کند. اگر معیار شما Dave باشد و از Advanced Filter روی یک لیست طولانی استفاده کنید، Excel در کنار اسم Dave، اسامی مانند Davey، Dave J، Dave K و غیره را نیز نشان می دهد. به بیان دیگر، هر اسمی که با Dave شروع شود در این معیار قرار می گیرد. برای اینکه Excel مجبور به پیدا کردن عبارت دقیق، در این مثال فقط نام Dave، شود باید از معیار =”=Dave” استفاده کنید.

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

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

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

به تب FORMULAS رفته و show formulas را کلیک کنید.

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

به تب Home بروید وقسمت Format در منوی کشویی Format Cells را کلیک کنید.

پنجره Format Cells باز می شود و برای مخفی کردن آن به تب Protection بروید و تیک Hidden بزنید و دکمه OKرا کلیک کنید.

برای پایان دادن به مخفی کردن سلول شما باید از ورق محافظت کنید وFormat را کلیک کنید و در منوی کشویی Protect Sheet را کلیک کنید.

پنجره Protect Sheet باز می شود و اگر می خواهید کاربران به فرمول ها دسترسی کند و می خواهید آن فرمول رمز گذاری کنید شما باید در قسمت Password to unprotect sheet رمز عبور را تعیین کنید و دکمه OK را کلیک کنید.

رمز عبور را دوباره وارد کنید و دکمه OK را کلیک کنید.

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

برای نشان دادن فرمول در نوار فرمول دیگر،قسمت Format در منوی کشویی Unprotect Sheet را کلیک کنید.

پنجره Unprotect Sheet باز می شود و رمز عبور را وارد کنید.

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

تابع، یک فرمول از پیش نوشته شده در برنامه است. آرگومان های (متغییرهای) مورد نیاز به تابع داده می شود و تابع، نتیجه را محاسبه می کند. برای تابعSUM، تنها چیزی که باید مشخص کنید محدوده ای است که می خواهید جمع کنید نوار ابزارStandard، امکان دستیابی سریع به SUM و نیز توابع پر استفاده دیگر مانند AVERAGE (محاسبه معدل)، MIN (کوچکترین عدد)، MAX (بزرگترین عدد) وCount (محاسبه تعداد آیتم ها) را فراهم می کند. در کار عملی16 «چگونه از توابع استفاده کنیم» یاد می‌گیرید که چگونه از توابع دیگر استفاده کنید.

 یک محل برای نتیجه، انتخاب کنید  

سلولی که می خواهید نتیجه تابع در آن ظاهر شود را انتخاب کنید (معمولاً در پایان یک سطر یا ستون از اعداد).

 روی AutoSum کلیک کنید  

در نوار ابزار Standard، روی دکمه AutoSum کلیک کنید.

 فرمول SUM درج می شود  

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

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

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

 با استفاده از AutoSum، معدل حساب کنید  

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

 ورود اطلاعات را کامل کنید  

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

*توجه  *

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

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

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

برای جمع کردن مقادیر بیش از یک محدوده، روی دکمه AutoSum کلیک کنید، محدوده اول را انتخاب کنید، کلید کاما (,) را بفشارید و بعد محدوده بعدی را انتخاب کنید. بین تمام محدوده های بعدی هم باید کاما تایپ کنید.

 قابلیت AutoFill با پر کردن خودکار مدخل های تکراری یا یک سری داده (مانند Apri،May، June) می تواند ورود داده ها را به میزان چشمگیری سرعت بخشد. با AutoFill به سرعت می توانید لیستی از اسم روزها یا ماه ها، یک سری اعداد یا یک لیست از متن های مشابه ایجاد کنید. اسم روزها و ماه ها و مخفف های سه حرفی آنها در Excel وجود دارند. می‌توانید لیست های سفارشی(مثلاً از اسم افراد یا محصولات) ایجاد کنید و AutoFill آنها را پر کند.

 یک لیست از ماه‌ها ایجاد کنید  

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

 دسته پر کردن را بکشید  

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

  AutoFill Options را انتخاب کنید  

دکمه ماوس را در انتهای سطر یا ستونی که می خواهید پر کنید رها کنید. سری مورد نظر به ترتیب لازم در سلول ها درج می شود. دکمه Auto Fill Options ظاهر می شود. روی فلش آن کلیک کنید تا لیستی از گزینه ها باز شود. از این لیست برای انتخاب چگونگی پُر شدن سلول ها استفاده کنید. برای مثال، می توانید به Excel دستور دهید که بدون کپی کردن فرمت، یک سری ایجاد کنید. برای این کار، گزینه Fill Without Formatting را انتخاب کنید.

 یک سری متنی ایجاد کنید  

برای ایجاد یک سری متنی مانند Division 1، Division 2 و، داخل یک سلول کلیک کنید و اولین مورد در این سری را تایپ کنید. سلول را انتخاب کنید و دسته پر کردن را در جهتی که می خواهید بکشید.

 دسته را رها کنید  

دسته را رها کنید تا سری متنی در سلول‌های انتخاب شده پر شود. اگر لازم باشد، روی فلش دکمه AutoFill Options کلیک کنید و یکی از گزینه ها مانند Copy Cells را انتخاب کنید تا داده ها را بدون ایجاد سری، کپی کند.

 یک سری عددی ایجاد کنید  

برای ایجاد یک سری عددی، دو عدد اول را در سلول های مجاور تایپ کنید، این دو سلول را انتخاب کنید و دسته را برای پر کردن سری، بکشید. AutoFill بصورت خودکار، بقیه سری را بر اساس دو عدد اول پر می کند. برای مثال، اگر در سلول اول تایپ کنید5 و در سلول دوم تایپ کنید. 15، یک سری ایجاد خواهید کرد که در هر سلول، 5 عدد به عدد قبلی اضافه می شود.

*توجه *

 کپی کردن داده ها

برای پر کردن یک لیست با یک مدخل کپی شده، آن مدخل را تایپ کنید، آن را انتخاب کنید و دسته را برای کپی کردن آن مدخل بکشید. اگر Excel یک سری ایجاد کند، روی دکمه AutoFill Options کلیک کنید و Copy Cells را انتخاب کنید.

کپی کردن یک فرمول با استفاده از  AutoFill در excel

از AutoFill می توان برای کپی کردن یک فرمول در یک جدول استفاده کرد. آدرس سلول ها در فرمول طوری تنظیم می شوند که فرمول، بدرستی سلول ها را محاسبه کند
ایجاد یک لیست سفارشی در اکسل

برای ایجاد یک لیست سفارشی، کل لیست را در یک برگه کاری تایپ کنید. این لیست را انتخاب کنید و از منوی Tools، گزینه Options را انتخاب کنید. در برگه Custom Lists، گزینه Import را انتخاب کنید و روی Ok کلیک کنید. لیست در پنجره Custom Lists ذخیره می شود. حال با تایپ کردن یکی از مدخل های لیست و کشیدن دسته پر کردن، می‌توانید این لیست را در یک صفحه کاری پر کنید.                                                         امید واریم از آموزش اکسل پیشرفته امروز نیز لذت برده باشید.                                                              موفقیت هایتان مستدام