ساختار Pivot Table یا گزارشات حرفه ای در اکسل

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

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

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

سپس به تب کمکی DESIGN که بواسطه ایجاد جدول در  اکسل ایجاد شده است میرویم و روی گزینه Summarize With PivotTable کلیک می نماییم.

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

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

برای اینکار فیلد ماه را در Rows قرار دهید و فروش را در Values می توانید سایر فیلدها را در Filters و یا Columns قرار دهید.

حالا روی یکی از سلولهای مرتبط با ستون Row Lables که تاریخهای روزانه در آن قرار دارد کلیک راست نمایید و گزینه Group را انتخاب کرده و هر یک از گروه های مورد نظرتان(ماه، فصل، سال) را انتخاب نمایید. می توانید همه موارد مذکور را هم انتخاب کنید.

اگر میخواهید گزارشات هفتگی بسازید باید گزینه Days را انتخاب کنید و قسمت Number of days را عدد هفت تعیین کنید. در نتیجه براحتی گزارشی به تفکیک سریهای زمانی مختلف ایجاد نموده اید.

نکته دوم:  نقش Filter در PivotTable در اکسل

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

هر فیلدی را که میخواهید از آن Pivottable مشابه بسازید در فیلد فیلتر قرار دهید. در اینجا ما هم محصولات، هم شهرها و هم ویزیتورها را در فیلتر قرار داده ایم. سپس در تب Analyze روی قسمت Options کلیک کرده و دومین گزینه یعنی Show Report Filter Pages را انتخاب نمایید.

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

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

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

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

فرض کنید می خواهید حاشیه سود را طی سه سال و در فصول مختلف محاسبه کنید برای اینکار باید میزان سود در هر بازه زمانی را بر فروش همان دوره تقسیم کنید برای اینکار به تب Analyze بروید روی گزینه Fields, Items & Sets کلیک نمایید. اولین گزینه Calculated Fields را انتخاب نمایید.

در پنجره ای که باز می شود باید دو قسمت را تکمیل نمایید. در قسمت Name برای شاخصی که میخواهید محاسبات آنرا انجام دهید یک نام بگذارید در اینجا ما “حاشیه سود” نوشتیم و در قسمت Formula باید با استفاده از فیلدهای موجود در لیست پایین آن محاسبات را انجام دهید که ما در اینجا سود/زیان را بر فروش تقسیم کردیم. برای تقسیم از علامن / روی کیبرد استفاده کنید. بعد از اینکه ok کردید فیلد جدید خودبخود به PivotTable اضافه می شود.

نکته: دقت کنید اگر جنس شاخص تان از نوع درصد باشد مثل مثالی که ما در اینجا زدیم باید فرمت اعدادتان را به % یا Percentage تغییر دهید.

نکته چهارم: ساختار نمودارهای داینامیک در اکسل

آیا تا بحال با خودتان فکر کرده اید که چه می شد اگر می توانستید نمودارهایی بسازید که با اعمال فیلتر روی دیتاها نمودارتان نیز تغییر می کرد؟ بله درست است PivotTable این امکان را به شما می دهد. برای اینکار کافیست در تب Insert روی PivotChart کلیک نموده و نمودار مورد نظرتان را ایجاد نمایید. سپس در همان تب روی گزینه Slicer کلیک کرده و اسلایسرهای مورد نیازتان را بسازید.

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

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

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

ابتدا بر روی گزینه File در محیط اکسل  کلیک و سپس بر روی Options کلیک نمایید.

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

دکمه Edit Custom Lists در قسمت انتهایی بخش Advanced  است آن را پیدا کرده و کلیک کنید.

برای ایجاد لیست سفارشی جدید، آیتم‌های مورد نظر خود  را در قسمت  list entries وارد کرده و بر دکمه Add کلیک کنید. در اینجا شماره آرماتورها را که از موارد و لیست های پرکاربرد هستند را به لیست خود اضافه می‌کنیم

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

نحوه استفاده 

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

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

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

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

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

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

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

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

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

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

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

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

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

عملیات منطقی یعنی با استفاده از NOT، یا (OR)، و (AND) و در نهایت IF رابطه‌ای تعریف کنید. اگر محاسبات سلولی نیاز به عملیات منطقی دارد، استفاده از توابع منطقی اکسل تنها انتخاب شماست که در ادامه کاربردشان را شرح می‌دهم.

تابع  IFدر اکسل

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

=IF(گزاره اگر شرط غلط باشد,گزاره اگر شرط درست باشد,شرط)

اگر عبارت شرط درست باشد، “گزاره اگر شرط درست باشد” و در غیر این صورت، “گزاره اگر شرط غلط باشد” اجرا می‌شود. دستتان برای نوشتن شرط باز است. مثلا عبارات A1C16 یا “D5=”alpha، می‌توانند پارامتر اول IF باشند.

به عنوان مثالی از IF فرض کنید صفحه‌ی اکسلی داریم که ستون E آن، تعدادی عدد است. برحسب این که کدام عدد از 1600 بیشتر است و کدام عدد کمتر، می‌خواهیم در تقاطع همان سطر و ستون F به ترتیب true یا false نمایش داده شود. برای شکل در خانه‌یF2 کلیلک کرده و مقابل fx در بالای صفحه می نویسیم

=IF(E2>1600,”true”,”false”)

نوشتن فرمول IF در ستون F

خانه‌ی F2 را در خانه‌های هم ستونش paste  می‌کنیم تا برای آن سلول‌ها هم مطابق این رابطه عمل شود.

نتیجه‌ی فرمول IF برای سلول‌های ستون F

اگر کمی برنامه‌نویسی کار کرده باشید، می‌دانید که با ترکیب AND,OR,IF و دیگر توابع می‌توان عبارات پیچیده‌تری نوشت.

تابع NOT در اکسل:

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

تابع AND:

اگر کل گزاره‌های ورودی AND برقرار باشند (همگی درست باشند)، خروجی معتبر (درست یا true) خواهد بود و در غیر این صورت (غلط یا false) برمی‌گرداند.ساختار کلی AND به صورت زیر است:

=AND(شرط2,شرط1,… ) 

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

در صفحه‌ی اکسلی مطابق شکل می‌خواهیم دانش آموزانی که SAT آن‌ها بین 1050 و 1950 است، مشخص شوند. به این منظور در ستون F این دانش‌آموزان کلمه‌ی TRUE و برای بقیه‌ی دانش‌آموزان، FAlSE نمایش بدهیم. بنابراین برای ستون F جلوی عبارت fx می‌نویسیم

=AND(E2>1050, E2

نوشتن فرمول AND در ستون F

نتیجه‌ای که پس از زدن Enter نمایش داده می‌شود.

نتیجه‌ی فرمول AND برای سلول‌های ستون F

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

=AND(E2>1050, E2

در این حالت جلوی اسم دانش آموزانی که اهل Wisconsin هستند و SAT آن‌ها بین 1050 و 1950 است، TRUE نوشته می‌شود.

تابع OR:

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

=OR(شرط2,شرط1,….)

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

=OR(D2=”Minnesota”, D2=”Wisconsin”, D2=”Iowa”, D2=”Nebraska”, D2=”Missouri”, D2=”North Dakota”, D2=”South Dakota”, D2=”Indiana”, D2=”Michigan”, D2=”Ohio”, D2=”Illinois”, D2=”Kansas”)

در این صورت تنها سطرهایی از ستون F مقدار TRUE می‌گیرند که ستون D آن‌ها یکی از شهرهای فوق باشد.

نتیجه‌ی فرمول OR برای سلول‌های ستون F

اکسل 2013 تابع دیگری دارد که شبیه OR  عمل می کند و نام آن XOR (اکسکولوسیو اُر) است. اگر تعداد پارامترهای ورودی XOR که درست هستند زوج باشد، خروجی XOR صفر و در غیر این صورت یک است.

ترکیب توابع IF, NOT, AND و OR  در excel:

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

=OR(AND(D2=”California”, E2>1950), AND(D2=”Oregon”, AND(E2>1050, E2

OR ای است که از سه قسمت تشکیل شده است.

AND(D2=”California”, E2>1950)

AND(D2=”Oregon”, AND(E2>1050, E2

AND(OR(D2=”Washington”, D2=”Nevada”), E2

اولین AND ساختاری ساده دارد. دومین AND اگر دانش‌آموز اهل Oregon باشد و SAT او بین 1050 و 1950 باشد خروجی درست برمی‌گرداند. سومین AND هنگامی که دانش‌آموز اهل Washington یا Nevada باشد و SAT او کمتر از 1050 باشد خروجی TRUE برمی‌گرداند. حال چون هر سه AND فوق در یک ساختار OR قرار گرفته‌اند اگر یکی از آن‌ها صحیح برگردانند، در ستون F مربوطه مقدار TRUE نوشته می‌شود و در غیر این صورت FALSE می‌شود.

نتیجه‌‌ی تابع ترکیبی را در شکل زیر ببینید.

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

1.دستور =RAND() این دستور شماره های تصادفی بین 0 و 1 ارائه می نماید. با زدن دگمه F9 هر بار شماره تازه می دهد.

2.دستور =RAND()*100 این دستور شماره های تصادفی بین 0 و 100 ارائه می دهد.

3.دستور =RANDBETWEEN(1;500) این دستور شماره تصادفی بین 1 و 500 ارائه می دهد.               دستور  COMBINدر اکسل:

دوست دارید تعداد حالتهای دو نفره از 10 نفر را محاسبه نمایید، اکسل با دستور COMBIN این کار را برایتان انجام می دهد.

فرمول محاسبه: =COMBIN(10;2)

محاسبه لگاریتم:

برای محاسبه لگاریتم در مبنای ده از دستور =LOG(100) استفاده نمایید. برای محاسبه لگاریتم 10 در مبنای 2 دستور =LOG(10;2) را بنویسید.

محاسبه جذر یک عدد در اکسل :

از دستور =SQRT(10) استفاده نمایید.

دستور SUMIF:

در بین یک سری از اعداد می خواهید مقادیر تعریف شده را با هم جمع نمایید. برای مثال می خواهید مقادیر بیشتر از 15 را جمع نمایید. برای این محاسبه دستور =SUMIF(B2:B50;”>15”)را بنویسید. شاید بخواهید مقادیر مربوط به شخصی خاص را در یک ستون دیگر جمع نمایید، برای این کار از دستور
=SUMIF (A1:A50;”HASAN”;B1:B50) استفاده نمایید.

دستور SUMIFS:

گاهی لازم می شود در یک ستون مقادیری با معیارهای تعریف شده برای داده های موجود در ستونهای دیگر را با هم جمع نمایید. فرض نمایید مقادیری از ستون A را می خواهید جمع نمایید که مقادیر متناظر آنها در ستون B بیشتر از 10 و در ستون D کمتر از 5 باشد. برای انجام این محاسبه از دستور
=SUMIF(A1:A20;B1:B20;”>10”;D1 20;” استفاده نمایید.

دستور POWER:

برای به دست آوردن نتیجه 15 به توان 16 از دستور =POWER(15;16) استفاده نمایید.

دستور SUMX2MY2:

این دستور تفاضل مجذورات ستون X و ستون Y را با هم جمع می نماید. برای این محاسبه از دستور =SUMX2MY2(A1:A20;B1:B20) استفاده نمایید.

دستور SUMX2PY2  در اکسل:

این دستور جمع مجذورات ستون X و ستون Y را با هم جمع می نماید. برای این محاسبه از دستور =SUMX2PY2(A1:A20;B1:B20) استفاده نمایید.

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

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

ایجاد چک باکس  با استفاده از تب DEVELOPER   در اکسل
برای ساخت چک باکس ابتدا باید تب DEVELOPER را در تب های اصلی اکسل فعال کنیم. برای این کار در اکسل  از تب FILE بر روی Options کلیک کرده و در پنجره  پیشرو از منوی سمت چپ گزینه Customize Ribbon را انتخاب می کنیم. در سمت چپ در منوی آبشاری با زدن تیک گزینه Developer را فعال می کنیم.

به شیت گزارش باز می گردیم، از تب Developer قسمت Controls و از جعبه ابزار Insert قسمت Form Controls گزینه Check Box را انتخاب می کنیم.

در قسمتی که چک لیست هارا ایجاد کردیم، در کنار جدول ها و درست مقابل هر یک از ردیف های آن، یک چک باکس رسم می کنیم. پس از رسم چک باکس بر روی آن کلیک راست کرده و از منو، قسمت Edit Text، عبارت Check را از جلوی چک باکس حذف می کنیم. سپس از همان منو، پنجره Format Controls … را باز کرده و از تب Control (مطابق شکل زیر) در قسمت Cell link آدرس سلول سمت راست چک باکس را برای لینک کردن (F4) می دهیم. این کار را برای تمامی چک باکس ها انجام می دهیم. در صورت صحیح انجام شدن موارد ذکر شده در صورت تیک دار کردن چک باس، سلول مجاور TRUE را نشان داده و در صورت برداشتن تیک FALSE می شود.

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

در فرمول بالا از ترکیب توابع IF، SUMPRODUCT، ISNUMBER، MATCH به صورت توابع آرایه ای استفاده شده است. برای راحتی کار فرمول را 3 تکه کرده ایم.
1) DATABASE!$B$3:$B$12=C4:
فرمول بالا قصد شمردن تعداد گروه سنی در هر ردیف چک لیست را از روی دیتا بیس را دارد.

در این فرمول؛ گروه سنی 24- 20 سال واقع در سلول C4، با گروه های سنی واقع در ستون B در شیت دیتا بیس مقایسه شده و در صورت مطابق بودن، جواب TRUE دریافت می شود. تنها بدلیل آرایه ای بودن فرمول، قابلیت چک کردن یک سلول (C4) را با سلول های بازه دیگر(ستون B دیتابیس) داریم. در صورتی که اگر فرمول، معمولی نوشته می شد تنها اجازه مقایسه دو سلول را داشتیم. در روش آرایه ای این مقایسه در حافظه موقت برنامه، سلول به سلول (مطابق شکل بالا) انجام شده و تعداد تطابق ها بدست می آید (در شکل بالا 2 سلول می باشد) و در محاسبات فرمول اصلی استفاده می شود. (در فرمول مسئله ما تابع SUMPRODUCT از این 2 تطابق پیدا شده استفاده می کند.)

2) ((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

این فرمول در مسئله ما نقش شمردن تعداد مشاغل در هر ردیف چک لیست، از روی دیتابیس را دارد.
تابع کلیدی در فرمول بالا MATCH می باشد، (MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

ابتدا برای باز شدن گره فرمول بالا به سراغ فرمول (IF($F$10:$F$13,$C$10:$C$13می رویم. این فرمول در قسمت دوم تابع MATCH و به عنوان بازه جستجو استفاده می شود. ولی با توجه به شرطی که گذاشته شده هر شغلی نمی تواند جزء سلول های بازه جستجو باشد و تنها شغل هایی که در چک لیست مشاغل تیک بخورند شرط بالا را رعایت کرده و در بازه جستجو قرار می گیرند.

  تمامی سلول های بازه F10 تا F13 شرط را رعایت کرده و TRUE می باشند. پس سلول های نظیر آنها در ستون C جزء بازه جستجو می باشند و درصورتی که تیک هریک از چک باکس های بازه F10 تا F13 را برداریم، سلول نظیر آن از بازه جستجو  حذف می گردد.
حال که تکلیف بازه جستجو مشخص شد، به فرمول MATCH باز می گردیم. قسمت اول فرمول ویا سلول های مورد نظرما برای تطابق؛ شامل مشاغل آورده شده در ستون مشاغل دیتابیس می شود. که با جستجوی سلول به سلول آنها به کمک آرایه ای نوشتن فرمول می توان از بازه جستجو مشخص شده است

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

((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

حالا از فرمول شماره 2 تنها تابع ISNUMBER باقی مانده است. کار این تابع؛ تبدیل نتایج بدست آمده در بازه نتیجه MATCH به صورتTRUE و FALSE می باشد. در صورتی که در بازه نتیجه MATCH، ارور #N/A نباشد ویا به عبارتی عدد باشد، ISNUMBER به ما نتیجه TRUE یا 1 و در صورت ظاهر شدن ارور به ما نتیجه FALSE ویا 0 می دهد.
در کل می توان از فرمول 2 نتیجه گرفت که اگر در چک لیست گزینه ای تیک دار نباشد، فرمول اصلی برای آن صفر منظور کرده و در محاسبات استفاده نمی کند و در صورت تیک دار بودن، 1 منظور شده و در محاسبات استفاده می شود.

3) ((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0

این فرمول دقیقا مانند فرمول شماره 2 عمل کرده در مسئله ما نقش شمردن تعداد علاقه مندی در هر ردیف چک لیست از روی دیتابیس را دارد. فقط در آدرس دهی باید آدرس های مربوط به علاقه مندی را منظور کرد در نهایت با ترکیب سه فرمول فوق، سه نتیجه به صورت سه بازه 0 و 1 یا TRUE و FALSE بدست می آید که با قرار دادن آنها در تابعSUMPRODUCT، نتیجه نهایی (برای چک باکس اول 2 می شود) بدست می آید.

*(((SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0

(((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

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

*(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0=

("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

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

1) چک لیست گروه سنی = کپی فرمول در خانه های D4 تا D8
(((IF(F4,SUMPRODUCT((DATABASE!$B$3:$B$12=C4)*(ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

2) چک لیست شغل = کپی فرمول در خانه های D10 تا D14

(((IF(F10,SUMPRODUCT((DATABASE!$C$3:$C$12=C10)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$D$3:$D$12,IF($F$16:$F$18,$C$16:$C$18,""),0)

3) چک لیست علاقه مندی = کپی فرمول در خانه های D16 تا D18
(((IF(F16,SUMPRODUCT((DATABASE!$D$3:$D$12=C16)*(ISNUMBER(MATCH(DATABASE!$B$3:$B$12,IF($F$4:$F$7,$C$4:$C$7),0}=

{("",((((ISNUMBER(MATCH(DATABASE!$C$3:$C$12,IF($F$10:$F$13,$C$10:$C$13,""),0)

همانطور که ملاحظه می کنید؛ این فرمول برای تمام چک لیست ها، مشابه می باشد و تنها در آدرس دهی تفاوت دارد. این فرمول را می توان برای هر تعداد چک لیست، با هر تعداد ردیف، بسط داد و تنها باید دقت داشت که به ازای هر چک لیست یک فرمول MATCH باید نوشته شود

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

پنهان سازی سلول های اکسل

در اکسل نمی توان یک سلول را بصورت کامل پنهان ساخت اما می توان آن را بصورت ظاهری جایگزین نمود. بدین منظور سلول یا سلول های (با نگه داشتن کلید Ctrl یا Shift) خود را انتخاب کنید. بعد از آن کلیک راست کرده و بر روی گزینه ی Format Cells کلیک کنید.

در پنجره ای که نمایش داده می شود مطمئن شوید که تب Number انتخاب شده است. در پنل Category، قسمت Custom را انتخاب کرده و سپس در فیلد Type عبارت ;;; را تایپ نمایید. توجه داشته باشید که شما قادر خواهید بود بعداً جهت نمایش دوباره محتوا، این نوع فیلد را به حالت اولیه باز گردانید. سرانجام بر روی دکمه ی OK کلیک کنید.

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

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

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

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

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

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

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

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

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

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

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

 نوار ابزار Drawing را باز کنید 

 ابزارهای ترسیم آفیس، در Word، Excel و Power Point قابل دستیابی هستند. برای مشاهده آنها، نوار ابزار Drawing را باز کنید. (روی یکی از نوارهای ابزار کلیک راست کنید و از منوی حاصله، Drawing را انتخاب کنید. یا، روی دکمه Drawing در نوار ابزار Standard کلیک کنید.) توجه کنید که این نوار ابزار بصورت پیش فرض در Power Point نمایش داده می‌شود.

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

 در نوار ابزار Drawing، روی ابزار شکل مورد نظر کلیک کنید. برای مثال، برای رسم یک مستعطیل، روی ابزار Rectangle کلیک کنید. اشاره‌گر ماوس به شکل علامت عدسی میکروسکوپ در می‌آید و Drawing Canvas ظاهر می‌شود.

 شکل مورد نظر را رسم کنید  

 اشاره‌گر ماوس را داخل Drawing Canvas بکشید و شکل مورد نظر را رسم کنید. وقتی که این شکل به اندازه و شکل مورد نظر رسید، دکمه ماوس را رها کنید. حال می‌توانید در صورت لزوم، این شیء را تغییر اندازه دهید، بچرخانید، جابجا کنید یا فرمت دهی کنید.

 اندازه Drawing Canvas را تغییر دهید  

 یرای تنظیم کردن اندازه Drawing Canvas تا به اندازه شکلی که ترسیم کرده‌اید در آید، روی یکی از دسته‌های آن کلیک کنید و بکشید یا یکی از گزینه‌های نوار ابزار Drawing Canvas را انتخاب کنید. برای مثال، می‌توانید اندازه چارچوب Drawing Canvas را تغییر دهید تا دقیقاً دور شکلی که ترسیم کرده‌اید را بگیرد یا اندازه Canvasرا زیاد کنید تا فضای بیشتری دور شکل را فرا بگیرد.

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

 با کلیک کردن روی دکمه text Wrapping در نوار ابزار Drawing Canvas می‌توانید شیوه خط جدید را برای شکل مشخص کنید. برای مثال، اگر بخواهید که شکل، پشت متن قرار گیرد، روی دکمه text Wrapping در نوار ابزار Drawing Canvas کلیک کنید و از لیست کشویی، گزینه Behind text را انتخاب کنید.

 Drawing Canvas را غیر فعال کنید  

 وقتی که شکل مورد نظر را روی Drawing Canvas رسم کردید، خارج از چارچوب آن کلیک کنید. چارچوب Drawing Canvas و نوار ابزار Drawing Canvas بسته می‌شود.

* توجه  *

 استفاده از  Auto Shapeدر excel

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

ترسیم بدون Drawing Canvas

لازم نیست که حتماً داخل چارچوب Drawing Canvas رسم کنید، اگر چه برای سازماندهی اشکالی که رسم می‌کنید سودمند است. شما می‌توانید اشکال را در هر کجای سند خود ترسیم کنید.

غیر فعال کردن  Drawing Canvas در اکسل

برای غیر فعال کردن قابلیت Drawing Canvas، از گزینهTools گزینهOptions را انتخاب کنید تا کادر محاوره‌ای Options باز شود. در برگه General، کادر انتخاب Automatically Create Drawing Canvrs When Inserting Autoshapes را از حالت انتخاب خارج کنید.

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

در ابتدا که اکسل را باز می کنید، ریبون بر روی تب (tab) اصلی یعنی Home قرار دارد،در این آموزش  اکسل هدف ما یادگیری  چکونگی بستن  ریبون  یا به عبارتی آن را مینیمایز minimize  کنید یا چگونه عناصر دلخواه را به آن اضافه یا کم کنیم به عبارتی آن را کاستومایز کنیم

تب ها در ریبون

تب های  excelبه صورت زیر هستند:

File, Home, Insert, Page layout, Formulas, Data, Review و View.

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

کوچک کردن نوار ریبون در اکسل (Minimize the Ribbon)

شما می توانید این نوار را کوچک کنید تا فضای بیشتری داشته باشید، برای این کار هر جایی از ریون کلیک راست کنید و روی Minimize the Ribbon کلیک کنید.

البته از کلید های Ctrl+F1 هم می توانید استفاده کنید.

نتیجه به صورت زیر است:

تغییر در نوار ریبون (Customize the Ribbon) در اکسل

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

 کلیک راست کنید و گزینه Customize را انتخاب کنید

در این پنجره دو ستون دارید، ستون سمت چپ امکاناتی که اکسل دارد، البته این امکانات دسته بندی شده اند، همانطور که در شکل زیر می بینید الان دسته Popular Commands انتخاب است که ابزار های پرکاربرد را به شما نشان می دهد، می توانید انرا تغییر دهید تا تمامی ابار ها و کامند ها به شما نشان داده شوند و در کنار این ستون، ستون سمت راست نمایانگر ریبون هست به همراه دسته بندی تب ها، خب می توانید تعیین کنید کدام امکان در کدام تب قرار بگیرد یا با کلیک بر روی دکمه New Tab می توانید تبی جدید ایجاد کنید:

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

مثلا Quickprint را در سمت چپ انتخاب کرده و new group را در سمت راست انتخاب کنید و با زدن کلید add گزینه QuickPrint به سمت راست در زیر مجموعه new Group اضافه می شود.

البته درون تب ها را می توان گروه بندی کرد در شکل زیر تب new Tab  یک گروه زیر مجموعه خودش دارد که new group است. نام این ها را نیز می توان تغییر دارد، هر کدام را خواستید انتخاب کنید و rename را بزنید، برای ساخت گروه جدید هم هر تبی را خواستید انتخاب کنید و روی new group کلیک کنید.

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