ساخت فرم حضور و غیاب در اکسل – از صفر تا صد (+ دانلود نمونه فرم رایگان)

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

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

ساخت فرم حضور و غیاب در اکسل

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

قرار است این گونه محاسبات را برای یک کارمند در یک کاربرگ اکسل (Worksheet) اجرا کنیم. با طی کردن گام‌هایی که در ادامه این متن، مطرح می‌شود، نحوه ساخت فرم حضور و غیاب در اکسل را در طول یک هفته و برای یک کارمند، فرا خواهید گرفت.

محدودیت‌های ساخت فرم حضور و غیاب در اکسل

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

ورود مقادیر تقویمی و تاریخ در اکسل

همیشه به یاد داشته باشید که تاریخ (Date) در اکسل یک شماره سریال است. در حقیقت، تاریخ اول ژانویه سال ۱۹۰۰، به عنوان مبدا تاریخ در اکسل قرار گرفته و به آن کد یا شماره سریال ۱ اختصاص یافته است. بنابراین اگر دوم ژانویه سال ۱۹۰۰ را در نظر بگیریم، مقدار عددی برابر با ۲ دارد. در نتیجه هر تاریخ یا مقدار تقویمی، به صورت یک عدد صحیح بزرگتر از ۱ در سلول‌ها، ثبت می‌شود ولی برای نمایش آن می‌توان از قالب تاریخ (Date) استفاده کرد.

به جدولی که در تصویر ۱ دیده می‌شود، توجه کنید، در این جدول، چند تاریخ و چند عدد صحیح با یکدیگر مقایسه شده‌اند. ستون A، مقادیر وارد شده و ستون B، مقادیر طبق «قالب‌بندی» (Format) را مشخص کرده است. ستون C نیز کد قالب به کار رفته را نمایش می‌دهد. توجه داشته باشید که برای یک سلول، کد قالب‌بندی را باید در پنجره Format Cell (مانند تصویر ۴) در بخش Custom و کادر Type‌وارد کنید.

نکته: در اکسل 2016 به بعد، می‌توان از قالب نمایش شمسی برای تاریخ استفاده کرد. برای آشنایی بیشتر با این موضوع، متن تاریخ شمسی در اکسل | راهنمای کاربردی را مطالعه کنید.

ورود مقادیر زمان و ساعت در اکسل

همانطور که دیدید، تاریخ یک عدد صحیح در اکسل محسوب می‌شود. ولی در صورتی که مقداری اعشاری وارد کرده باشید، اکسل آن را می‌تواند به قالب ساعت نمایش دهد. در حقیقت عدد اعشاری، نشانگر بخشی از یک واحد یا یک روز را مشخص می‌کند. برای مثال اگر مقدار 0٫5 را به شکل ساعت نمایش دهید، ساعت ۱۲ ظهر را مشخص کرده‌اید. همچنین 0٫25 نیز نشانگر ساعت ۶ صبح است. می‌توانید از فرمول زیر برای محاسبه ساعت برحسب عدد اعشاری استفاده کنید.

$$\large \text{decimal number} \times 24 = \text{time}$$

برای مثال، همانطور که گفتیم، ۰٫۵ نشانگر ساعت ۱۲ است. پس داریم:

$$\large 0.5 \times 24 = 12$$

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

$$\large \dfrac{\text{time}} {24} = \text{decimal number}$$

به شیوه مشابه، اگر ساعت ۱۲ ظهر را در نظر بگیریم، به صورت اعشاری باید از مقدار ۰٫۵ که نشانگر نصف روز کامل (۲۴ ساعت) است برای ثبت در سلول اکسل استفاده کنیم.

$$\large \dfrac{12} {24} = 0.5$$

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

نکته: توجه داشته باشید که اگر عدد اعشاری به همراه رقم صحیح باشد، بخش اعشاری آن، مربوط به ساعت و بخش صحیح، تاریخ را تعیین می‌کند. بنابراین 2٫3 نشانگر ساعت ۷:۱۲ دقیقه روز دوم ژانویه ۱۹۰۰ خواهد بود.

به منظور روشن‌تر شدن موضوع مثال‌هایی را از تغییر ساعت به عدد و عدد به ساعت، در تصویر ۲ اجرا کرده‌ایم. در این تصویر، جدولی را مشاهده می‌کنید که براساس آن مقادیر اعشاری (بدون رقم صحیح و یا با رقم صحیح) به شکل ساعت نمایش داده شده‌اند. البته برعکس این حالت نیز دیده می‌شود. به این معنی که ساعت را به شکل عدد صحیح یا اعشاری ظاهر کرده‌ایم.

ولی موضوع مهم آن است که به یاد داشته باشید، مقداری که در سلول‌های حاوی ساعت ثبت شده، یک عدد با ارقام اعشاری است و نمایش ساعت فقط از طریق قالب‌بندی، اعمال شده است.

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

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

• برای مشاهده فیلم آموزش ترکیب چندین فایل اکسل با افزونه RDBMerge + اینجا کلیک کنید.

گام‌های ساخت فرم حضور و غیاب در اکسل

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

گام اول برای ساخت فرم حضور و غیاب در اکسل

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

نکته: البته اگر بخواهید می‌توانید روز یا ایام هفته را به کمک تغییر قالب‌بندی در ستون «ایام هفته» نیز مشخص کنید. کافی است فرمول زیر را در سلول D5 وارد کرده و قالب را طبق تصویر ۴، تنظیم کنید.

$$\large = C5$$

همانطور که در تصویر زیر می‌بینید، از آنجایی که می‌خواهیم قالب‌بندی اعداد و نمایش آن‌ها به صورت فارسی صورت گیرد، از کد fa-IR‌ استفاده شده است.

در بالای کاربرگ مربوط به فرم حضور و غیاب (تصویر ۳)، نوشته‌ای وجود دارد که نشان می‌دهد، ساعت ورود و خروج را باید به شکلی وارد کنید که ساعت خروج بزرگتر از ساعت ورود باشد. به این ترتیب اگر زمان را به صورت ۲۴ ساعتی وارد کنید، این امر محقق خواهد شد. مشخص است که این کار تضمین می‌کند که مقدار کارکرد همیشه مقداری مثبت باشد. توجه داشته باشید که زمان یا تاریخ نباید مقادیر منفی داشته باشند.

ولی فرض کنید که به اشتباه این کار صورت گیرد. به کمک اعتبار سنجی سلول‌های کاربرگ در اکسل (Data Validation) می‌توانید برای سلول‌های مربوط به ساعت خروج، شرطی بگذارید که همیشه بزرگتر از ساعت ورود ثبت شوند. این تنظیم در تصویر ۵، برای سلول G5 دیده می‌شود. واضح است که این مقایسه باید با سلول E5 صورت گیرد.

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

گام دوم: محاسبه ساعت کاری در ساخت فرم حضور و غیاب در اکسل

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

$$\large =G5 -E5$$

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

گام سوم: محاسبه کسر یا اضافه کار

به منظور بدست آوردن مقادیر اضافه کار و کسر کار، لازم است که زمان استاندارد کاری برای هر روز مشخص شده باشد. این کار را در ستون K انجام داده‌ایم. محاسبه کسر کار براساس فرمول زیر انجام می‌شود. مشخص است که از یک تابع IF استفاده کرده‌ایم. زمانی که ساعت کاری از ساعت زمان استاندارد کوچکتر باشد، کسر کار اتفاق افتاده است. بنابراین ساعت کاری را از ساعت استاندارد کسر می‌کنیم. اگر این اتفاق نیافتد و کسر کار، مقداری منفی شود، تابع IF‌ به ما این اجازه را می‌دهد که به جای ساعت منفی، عبارت خالی “” را قرار دهیم.
پس برای سلول L5 از فرمول زیر استفاده خواهیم کرد.

$$\large =\text{IF}(K5 -I5 > 0, K5 - I5,"")$$

همین عمل را برای محاسبه اضافه کار انجام می‌دهیم، ولی این بار ساعت استاندارد را از ساعت کاری کسر می‌کنیم. واضح است که اگر باز مقدار محاسبه شده، منفی باشد، به جای مقدار منفی عبارت “” به کمک تابع IF‌ قرار می‌گیرد.

$$\large =\text{IF}(I5 - K5 >0 ,I5 - K5,"")$$

گام چهارم: جمع کردن ساعت‌ها

لازم است که جمع ساعت کاری برای هر یک از ستون‌های مربوط به محاسبات صورت گرفته را هم به کاربرگ حضور و غیاب اضافه کنیم. بنابراین با استفاده از تابع SUM در سلول‌های F12, I12 , K12, L12 و M12 مجموع را محاسبه می‌کنیم.

توجه داشته باشید که اگر جمع یک دنباله از مقادیر اعشاری با قالب ساعت، بزرگتر از ۱ شود، اکسل به طور خودکار یک واحد به تاریخ اضافه کرده و باقی مانده را برحسب ساعت نشان می‌دهد. به این ترتیب نمی‌توان ساعت‌های بزرگتر از ۲۴ را نشان داد، مگر آنکه قالب جدیدی برای ساعت انتخاب کنیم. کد قالب‌بندی برای نمایش ساعت بزرگتر از ۲۴ به صورت زیر است.

[$-,300][h]:mm;@

توجه داشته باشید که عبارت [h] برای نمایش ساعت‌های بزرگتر از ۲۴ لازم است. البته به کمک پنجره Format Cell نیز می‌توان این کار را انجام داد. به تصویر ۶ توجه کنید.

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

گام پنجم: رنگ آمیزی و نهایی سازی فرم حضور و غیاب اکسل

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

البته به کمک «قالب‌بندی شرطی» (Conditional Formatting)‌ نیز می‌توانید رنگ زمینه سلول‌هایی «ساعت کسر کار» یا «ساعت اضافه کار» را به طور خودکار، قرمز یا سبز کنید.

دانلود رایگان فایل اکسل حضور و غیاب

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

• برای دانلود نمونه فرم حضور و غیاب کارکنان در اکسل + اینجا کلیک کنید.

نکته: توجه داشته باشید که مقادیر ثبت شده به صورت ساعت در اکسل را نمی‌توان با اعداد دیگر، مقایسه کرد یا براساس آن‌ها محاسبات ریاضی انجام داد. برای مثال اگر می‌خواهید براساس کارکرد ساعتی کارمندی، به او حقوق پرداخت کنید، حتما باید زمان کاری او را در ۲۴ ضرب کنید تا براساس آن و حقوق ساعتی، دستمزد او را بدست آورید. توجه داشته باشید که در آخر هم باید قالب عددی مناسب (مثلا قالب ارزی – Currency) را برای نمایش عدد بدست آمده، به کار ببرید.

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

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

• درس یکم: سازماندهی داده ها برای گردآوری، انواع داده های قابل جمع آوری، آشنایی با افزونه ها و کاربرد آن ها در اکسل، نحوه نصب کردن افزونه RDBMerge در اکسل
• درس دوم: استفاده از افزونه RDBMerge، آشنایی با بخش ها و تنظیمات افزونه RDBMerge، واکشی اطلاعات از فایل های گردآوری شده، آشنایی با LogSheet، سازماندهی و آماده سازی داده‌ها برای تهیه گزارش، تولید فرم نظرسنجی، تهیه گزارش‌ها با استفاده از «جدول محوری» (Pivot Table)

زمان این فیلم آموزشی ۱ ساعت و ۲۷ دقیقه است و مشاهده آن به تمامی کسانی که در بخش اداری یا محاسبات مالی شرکت‌ها فعالیت دارند، توصیه می‌شود. در ضمن نسخه اکسل ۲۰۱۶ در این فیلم آموزشی به کار رفته است. البته باید خودتان طبق مراحل این آموزش، افزونه RDBMerge را نصب کرده و به کار بگیرید.

• برای مشاهده فیلم آموزش ترکیب چندین فایل اکسل با افزونه RDBMerge + اینجا کلیک کنید.

خلاصه و جمع‌بندی

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