ساعت و زمان در اکسل — راهنمای کاربردی (+ دانلود فیلم آموزش رایگان)

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

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

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

محاسبات برمبنای اعدادی که برحسب ساعت هستند با اعداد معمولی متفاوت است. در شیوه نوشتن و انجام عملیات ریاضی، از سیستم عدد نویسی اعشاری (Decimal) یا ده-دهی استفاده می‌کنیم. ولی ساعت با توجه به تقسیم‌بندی روز به ۲۴ قسمت مساوی، مبنایی متفاوت در نمایش و محاسبات دارد. در حقیقت محاسبات ساعت باید بر اساس عدد نویسی برمبنای ۲۴ یا ۱۲ (نصف شبانه روز) صورت گیرد. همچنین دقیقه و ثانیه هم بر مبنای ۶۰ نوشته و محاسبه می‌شوند.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

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

نحوه نمایش ساعت و زمان در اکسل

برای ثبت مقدارهای مربوط به زمان در قالب ساعت و دقیقه و ثانیه، از جداکننده با علامت «:» استفاده می‌شود. برای مثال اگر بخواهیم ساعت ۱۰ و ۳۴ دقیقه و ۴۵ ثانیه را در سلولی ثبت کنیم از قالب ۱۰:۳۴:۴۵ استفاده خواهیم کرد. ولی اگر هنگام ورود، ساعت را از مقدار ۲۴ یا دقیقه و ثانیه را از ۶۰ بزرگتر وارد کنیم، اکسل محتویات را به شکل ساعت درک نکرده و آن را به عنوان یک محتوای متنی در نظر می‌گیرد.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

از قالب‌بندی سلول یا دستور Format Cell به منظور تغییر ظاهر مقادیر عددی استفاده می‌کنیم. به منظور دسترسی به پنجره Format Cell، پس از انتخاب ناحیه از سلول‌های کاربرگ که مقادیر عددی دارند. از برگه Home قسمت Number را انتخاب و از فهرست کشویی، نوع یا قالب نمایش اعداد را انتخاب کنید. گزینه‌های این لیست به مانند تصویر ۱ ظاهر خواهد شد. البته این کار را با کلیک راست و اجرای دستور Format Cells نیز می‌توانید انجام دهید.

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

جدول ۱: معرفی چند قالب برای نمایش تاریخ

نکته: نمایش و ترتیب قرارگیری ساعت، دقیقه و ثانیه با توجه به موقعیت جغرافیایی تنظیم شده در مرکز کنترل (Control Panel) ممکن است تغییر کند. گزینه‌هایی که در لیست قالب‌بندی ساعت با علامت * مشخص شده‌اند، از تنظیمات سیستم عامل (Control Panel) تبعیت می‌کنند.

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

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

به این ترتیب واضح است که  عدد اعشاری ۰٫۵ باید بیانگر ساعت ۱۲ ظهر باشد. همچنین ساعت شش بعد از ظهر نیز به صورت یک عدد کسری به شکل ۰٫۷۵ در خواهد آمد. این تبدیل را به کمک قالب‌بندی سلول در اکسل از عدد به ساعت و برعکس می‌توان در تصویر ۲ و ۳ مشاهده کرد.

نکته: اگر ساعت بزرگتر از ۲۴ شود در حقیقت، عدد اعشاری دارای قسمت صحیح خواهد شد. در این صورت مقدار عدد صحیح بیانگر تاریخ و قسمت اعشاری هم ساعت را نشان می‌دهد. برای مثال عدد ۲۵٫۷۵ یک روز و ۰٫۷۵ روز را مشخص می‌کند؛ این یعنی که ساعت برابر با شش بعد از ظهر روز بعد است. ولی به کمک قالب‌بندی می‌توانید در صورت لزوم، نمایش ساعات بزرگتر از ۲۴ را هم اجرا کنید.

فرمول‌نویسی محاسبات ساعت و زمان در اکسل

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

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

کلیک کنید

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

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

$$ \large D1 : C1 -  B1 $$

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

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

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

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

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

$$ \large E6: = D6 * 24 * 5000 $$

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

فقط به این موضوع نیز توجه داشته باشید که در سلول D8 ممکن است مقدار عددی توسط اکسل با قالب ساعت نمایش داده شود. برای مشاهده مقدار درست، قالب نمایش برای این سلول را به صورت General درآورید.

توابع مربوط به محاسبات ساعت و زمان در اکسل

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

جدول ۲: معرفی توابع مرتبط با محاسبات ساعت و تاریخ در اکسل به همراه کارکرد پارامترهای آن‌ها

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

مثال ۱

فرض کنید می‌خواهیم از اکسل به عنوان یک زمان‌سنج (StopWatch) استفاده کنیم. ساعت شروع را در خانه A2 وارد می‌کنیم. فرض کنید این ساعت برابر با ۸:۴۵ صبح است. حال در سلول A3 فرمولی به شکل زیر وارد می‌کنیم.

$$ \large A3: = NOW() - A2 $$

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

به این ترتیب فاصله زمانی بین ۸:۴۵ دقیقه با زمان جاری سیستم محاسبه می‌شود. اگر می‌خواهید صدم ثانیه نیز مشاهده شود، قالب سلول A3 را به دلخواه و طبق جدول ۱ تغییر دهید. با فشردن هر بار کلید F9، محاسبات مربوط به سلول‌های کاربرگ، از نو انجام شده و زمان جاری سیستم از زمان شروع کسر می‌شود. در نتیجه فاصله زمان جاری از ساعت ۸:۴۵ دقیقه با زدن کلید F9 محاسبه و نمایش داده می‌شود.

مثال ۲

به منظور استخراج ساعت از یک عدد اعشاری (یا حتی زمان) از تابع Hour استفاده می‌کنیم. چنین کاری در تصویر ۵ مشاهده می‌شود. همانطور که می‌بینید سلول A2 که حاوی فرمول زیر است،  براساس ساعت بزرگتر از ۲۴، قالب‌بندی شده است.

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

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