قرار دادن چک باکس در اکسل ساده است اما فرمول نویسی چک باکس در اکسل بسته به هدف پروژه، ممکن است ساده یا پیچیده باشد. در این مقاله آموزش Excel یک پروژه مرتبط با چک باکس را بررسی می‌کنیم که هدف اصلی، محاسبه و نمایش زمان تیک زدن چک باکس است. به عنوان مثال می‌توانید برای ورود و خروج کارمندان یا زمان آغاز و پایان کار افراد تیم، یک فایل اکسل مشترک بسازید و زمانی که هر شخص کار را آغاز می‌کند، در چک باکس شروع کار تیک بزند و زمانی که کار تکمیل می‌شود در کادر پایان کار تیک اضافه کند.
در ادامه مطلب با ما همراه باشید تا یکی از توانمندی‌های جالب اکسل که فرمول نویسی پیشرفته چک باکس برای امور خاص است را بررسی کنیم.
نوشتن بعضی از فرمول‌ها برای چک باکس، بسیار ساده است. شاید نوشتن فرمول شمارش چک باکس در اکسل را بلد باشید یا با توابع شرطی نظیر IF، فرمول‌های مفید و کاربردی برای چک باکس نوشته باشید. برای نوشتن فرمول زمان تیک خوردن یک چک باکس، لازم است که تنظیمات Excel را تغییر بدهید! در ادامه روش کار را به صورت کامل و تصویری توضیح می‌دهیم.
ساخت جدول اطلاعات در اکسل با استایل جدولی
فرض کنید در یک کار تیمی، برای هر شخص کار خاصی تعیین شده و قرار است که زمان شروع و پایان کار و مدت زمان که اختلاف بین زمان شروع و پایان است، در یک جدول ساده جمع‌آوری شود. در این مثال شماره تسک‌ها در ستون A و نام افراد در ستون B تایپ شده و زمان شروع در ستون D و زمان پایان در ستون F نمایش داده می‌شود. ستون C و E برای نمایش چک‌باکس شروع و پایان کار در نظر گرفته شده است. در نهایت ستون G مدت زمان تکمیل هر تسک را نمایش می‌دهد.
برای بهبود ظاهر فایل اکسل و جلوگیری از تیک زدن اشتباه در ردیف‌های دیگر، بهتر است ردیف‌های جدول اکسل به صورت یکی در میان، رنگ متفاوت داشته باشد. برای این منظور از گزینه‌ی Format as Table در تب Home بالای صفحه استفاده کنید و یکی از استایل‌ها را انتخاب کنید.
گام بعدی مشخص کردن محدوده‌ی جدول است. می‌توانید محدوده جدول را با کلیک و درگ به کمک موس انتخاب کنید یا آدرس سلول ابتدا و انتهایی را به صورت دستی وارد کنید. البته فراموش نکنید که بین اولین آدرس و آخرین آدرس از علامت دو نقطه یا : استفاده کنید.
با توجه به اینکه سطر اول، عنوان ستون‌ها است، تیک گزینه‌ی My Table Has Headers را بزنید که استایل این ردیف متفاوت شود.
به این ترتیب استایل جدول زیباتر و حرفه‌ای‌تر می‌شود:
فرمت سلول نمایش زمان در Excel
گام بعدی مشخص کردن فرمت برخی از سلول‌های جدول است، منظورمان سلول‌هایی است که قرار است زمان را نمایش بدهند. لذا سلول‌های ستون D و F و G را با نگه داشتن کلید Ctrl و کلیک و درگ کردن به کمک موس، انتخاب کنید. اگر ستون‌ها بسیار طولانی است، می‌توانید کلید Shift را نگه دارید و روی اولین و آخری سلول از ستون موردنظر کلیک کنید تا کل سلول‌های آن انتخاب شود.
برای انتخاب فرمت عددهای داخل سلول، در تب Home از نوار ابزار بالای صفحه، روی فلش منوی کرکره‌ای Number Format کلیک کنید و سپس روی گزینه‌ی Time کلیک کنید. حالت پیش‌فرض این است که ابتدا ساعت و سپس دقیقه و ثانیه نمایش داده می‌شود. این فرمت به صورت اختصاری، HH:MM:SS است. در صورت نیاز می‌توانید روی More Number Formats کلیک کنید و در پنجره‌ی فرمت سلول، گزینه Custom یا سفارشی را انتخاب کنید و فرمت موردنظر خود را تعریف کنید. به عنوان مثال برای مخفی کردن ثانیه در سلول های اکسل می‌توانید فرمت زمان را به صورت HH:MM در کادر فرمت وارد کنید که ساعت و دقیقه است.
قرار دادن چک باکس در اکسل 2016 و نسخه‌های جدیدتر
برای اضافه شدن گزینه Check Box در نوار ابزار بالای صفحه، لازم است که تنظیمات پیش‌فرض اکسل را تغییر بدهید.
برای فعالسازی گزینه CheckBox در تب Insert اکسل به صورت زیر عمل کنید:
- روی نوار ابزار بالای صفحه راست‌کلیک کنید.
- گزینه‌ی Customize the Ribbon را انتخاب کنید.
- در پنجره‌ی باز شده در ستون سمت راست، روی فلش کنار تب Insert کلیک کنید.
- روی دسته Controls کلیک کنید.
- روی Add در وسط پنجره کلیک کنید تا در تب اینسرت، گزینه‌ی مربوط به چک‌باکس اضافه شود.
اکنون اولین سلول در ستون شروع کار را انتخاب کنید و سپس روی گزینه‌ی Checkbox در تب Insert کلیک کنید.
به این ترتیب چک باکس داخل سلول اکسل قرار می‌گیرد. اکنون روی مربع کوچک گوشه‌ی پایین سلول کلیک کنید و آن را به پایین بکشید یا روش سریع‌تر این است که روی این مربع کوچک، دبل‌کلیک کنید. به این ترتیب چک‌باکس در ردیف‌های بعدی نیز اضافه می‌شود.
همین فرآیند را برای اولین سلول ستون پایان کار و مدت زمان کار تکرار کنید.
فعالسازی محاسبات تکرارشونده یا Iterative Calculations در اکسل
همان‌طور که در مقدمه اشاره کردیم، برای فرمول نویسی چک باکس در این پروژه آموزشی اکسل، لازم است که تنظیمات نرم‌افزار اکسل را تغییر بدهیم تا زمان تیک خوردن چک‌باکس‌ها ثبت شود. برای فعالسازی محاسبات تکرارشونده یا Iterative Calculations به صورت زیر عمل کنید:
- روی منوی File کلیک کنید.
- روی گزینه‌ی Options ستون کناری کلیک کنید.
- پنجره‌ی Excel Options باز می‌شود. روی دسته Formula کلیک کنید.
- در سمت راست تیک گزینه‌ی Enable Iterative Calculation را بزنید.
- روی OK کلیک کنید تا تنظیمات ذخیره شود.
دقت کنید که محاسبات تکرارشونده برای هر نوع فایل و پروژه برنامه نویسی اکسل مناسب نیست و علاوه بر اینکه سرعت محاسبات کمتر می‌شود، گاهی مشکلات عجیب و خاص نیز پیش می‌آید. برای کار با سایر فایل‌های اکسل می‌توانید گزینه محاسبات تکرارشوند را غیرفعال کنید.
فرمول نویسی چک باکس در اکسل برای ثبت زمان تیک زدن
به فرمولی نیاز داریم که در صورت تیک خوردن چک‌باکس در ستون C، زمان تیک خوردن در ستون D نمایش داده شود.
فرمولی که در سلول D2 می‌نویسیم، به صورت زیر است که شاید پیچیده و عجیب به نظر برسد اما با مطالعه کردن توضیحات بعدی، عملکرد این فرمول روشن می‌شود:
=IF([@Started],IF([@[Start Time]]="",NOW(),[@[Start Time]]),"")
در ابتدای فرمول از تابع شرطی IF استفاده شده است که آرگومان اول در این تابع، شرط است. شرط موردبحث، تیک داشتن یا تیک نداشتن سلول C2 است که با توجه به ساخت جدول و نام‌گذاری ستون‌ها، به شکل @Started در فرمول نوشته می‌شود.
=IF([@Started],
شرط بعدی این است که آیا سلول D2 که در آن فرمول می‌نویسیم، در وضعیت فعلی خالی است یا خیر. اگر خالی است به این معنی است که قبلاً چک باکس تیک نخورده و لذا ساعتی ثبت نشده است. لذا اولین بار است که تیک زده می‌شود. این شرط نیز با توجه به نام‌گذاری ستون‌ها به صورت [@[Start Time]]="" نوشته می‌شود.
=IF([@Started],IF([@[Start Time]]="",
با توجه به خالی بودن D2، زمان فعلی با استفاده از تابع NOW درج می‌شود.
=IF([@Started],IF([@[Start Time]]="",NOW(),
اگر سلول D2 از قبل پر شده باشد، به این معنی است که قبلاً تیک زده شده و لذا مقدار قبلی سلول D2 یعنی [@[Start Time]] درج می‌شود.
=IF([@Started],IF([@[Start Time]]="",NOW(),[@[Start Time]]),
در نهایت آخرین آرگومان اولین IF فرمول ما، خالی گذاشتن D2 است که با عبارت "" انجام می‌شود.
=IF([@Started],IF([@[Start Time]]="",NOW(),[@[Start Time]]),"")
پس از نوشتن فرمول چک باکس در سلول D2، زمانی که Enter را فشار دهید، فرمول در سلول‌های بعدی کپی می‌شود. در واقع با انتخاب سلول‌ها در کادر فرمول، فرمولی که نوشته‌اید نمایش داده می‌شود.
اکنون می‌توانید همین فرآیند را برای ستون F تکرار کنید. البته فراموش نکنید که نام ستون‌ها در فرمول نوشته شده را تغییر بدهید. فرمول به صورت زیر تغییر می‌کند:
=IF([@Completed],IF([@[End Time]]="",NOW(),[@[End Time]]),"")
با زدن کلید Enter فرمول در سلول‌های بعدی ستون F تکرار می‌شود.
در نهایت به ستون G می‌رسیم که محاسبه اختلاف زمان در اکسل است. برای این کار می‌توانید از فرمول ساده‌ی زیر استفاده کنید:
=SUM([@[End Time]]-[@[Start Time]])
ولیکن در صورت تیک نداشتن چک باکس‌ها با ارور روبرو می‌شوید. می‌توانید با تابع IFERROR مشکل را حل کنید. لذا خروجی تابع SUM را داخل تابع IFERROR قرار می‌دهیم و به جای آرگومان دوم، عبارت خالی یا "" را وارد می‌کنیم. به این ترتیب در صورت ارور داشتن، چیزی در سلول حاصل‌جمع چاپ نمی‌شود.
=IFERROR(SUM([@[End Time]]-[@[Start Time]]),"")
با زدن کلید Enter، فرمول در سلول‌های بعدی ستون G تکرار می‌شود.
اکنون می‌توانید فایل این پروژه اکسل را تست کنید و اگر همه چیز درست بود، در صورت نیاز برای بهبود ظاهر جدول و نام‌گذاری ستون‌ها و تسک‌ها و تغییر سایز چک باکس در اکسل اقدام کنید.
بد نیست با استفاده از Conditional Formatting یا فرمت شرطی، کاری کنید که چک باکس دارای تیک، به رنگ سبز نمایش داده شود.
howtogeekاینتوتک