چک لیست های مرتبط در اکسل

در این مثال 3 چک لیست گروه سنی، شغل و علاقه مندی ساخته شده و با فرمول نویسی به یکدیگر لینک شده اند. به طور مثال تنها با گذاشتن تیک های مربوطه و با صرف کمترین زمان شما می توانید تعداد پزشکان بین 20 تا 24 سال علاقه مند به سینما را پیدا کنید.

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

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

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

برای ساخت چک باکس در اکسل  ابتدا باید تب DEVELOPER را در تب های اصلی اکسل فعال کنیم. برای این کار در اکسل 2013 از تب 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

"یادآوری: تابع   ([MATCH(lookup_value, lookup_array, [match_type از سه قسمت تشکیل شده؛ قسمت اول که سلول مورد نظر ما برای تطابق است، قسمت دوم بازه ای که سلول مورد نظر برای تطابق، در آن جستجو می شود و قسمت سوم که اختیاریست، برای دقت جستجو بکار می رود که با سه عدد 0 و 1 و 1- اجرا می شود؛ اگر صفر بگذاریم دقیقا سلول مورد نظر را پیدا می کند، اگر 1 بگذاریم، درصورتی که سلول ما در بازه جستجو موجود نباشد، بزرگترین عددی که به رقم مورد نظر ما نزدیک تر و از آن کوچکتر است را پیدا می کند و در صورت استفاده از 1-،  اگر سلول ما در بازه جستجو موجود نباشد، کوچکترین عددی که به رقم مورد نظر ما نزدیک تر و از آن بزرگتر است را پیدا می کند. در صورت یافتن تطابق در بازه جستجو، تابع MATCH جایگاه ترتیبی سلول مورد نظر را در بازه جستجو نشان می دهد. (به طور مثال سلول مورد نظر، پنجمین سلول در بازه جستجو می باشد.)

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

اگر بخوایم یک بازه یا آرایه را در بازه ای دیگر جستجو کنیم کار کمی پیچیده شده و باید فرمول را به صورت آرایه ای بنویسیم. همانطور که در شکل زیر می بینید برای یافتن جایگاه اعداد ستون Iدر ستون G هر سه خانه ستون K را انتخاب کرده و فرمول شکل زیر را بصورت آرایه ای می نویسیم و در نتیجه با یک فرمول آرایه ای جایگاه هر سه عدد را در بازه جستجو بدست می آوریم. ایرادی که در بالا ذکر شد در این مثال نیز صادق است و تنها اولین جایگاه 2 را در جواب به ما می دهد یعنی جایگاه اول بازه جستجو این در حالیست که 2 در جایگاه پنجم نیز تکرار شده است. "

ابتدا برای باز شدن گره فرمول بالا به سراغ فرمول (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 منظور شده و در محاسبات استفاده می شود.

در این شکل، وکیل بدلیل نداشتن تیک و عدم رعایتشرط فرمول IFاز بازه جستجو حذف شده که اثر آن در بازه نتیجه MATCH و بازه نتیجه ISNUMBER قابل مشاهده است.

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 باید نوشته شود.