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

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

 سه جدول را ایجاد می کنیم و در کنار هر ردیف آن یک چک باکس می سازیم. در جدول ها به ترتیب؛ گروه سنی، نوع مشاغل و نوع علاقه مندی را در ستون 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 باید نوشته شود