نویسنده
درباره نویسنده
بهنام خیرالهی
تعداد پست ها 64

درباره:

دانش آموخته مقطع کارشناسی دانشگاه Staffordshire University در رشته علوم کامپیوتر گرایش امنیت و مقطع کارشناسی دانشگاه Asia Pacific University در رشته فناوری اطلاعات هستم. از سال 1392 به ایران برگشتم و به صورت تخصصی در زمینه طراحی وب، طراحی گرافیک و برنامه نویسی NET. فعالیت دارم.

  • روش‌های محاسبه زمان

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

مفهوم و منطق محاسباتی زمان

در اکسل زمان (ساعت) هم مثل تاریخ به صورت عدد ذخیره شده و مبنای محاسبات آن 24 ساعت می‌باشد که مقدار آن برابر با 1 است. پس نتیجه می‌گیریم که محاسبات ساعت نسبت به روز سنجیده می‌شود. مثلا عدد متناظر با ساعت 20:11 برابر با 0.84 می‌باشد. اگر داده‌هایی که از جنس زمان هستند را به صورت General یا Number قالب‌بندی کنیم، به عدد تبدیل می‌شوند.

زمانعدد
18:480.84,097
12:000.5
24:001

مفهوم مقدار زمان در اکسل

توابع محاسبه زمان

تابع Hour

تابع Hour جزء توابع تاریخ و زمان می‌باشد که ساعت مربوط به یک زمان مشخص را فراخوانی می‌کند.

ساختار تابع

HOUR(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس زمان باشد.

آرگومان‌های تابع Hour

ویژگی‌های تابع

  1. ساعتی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون زمان‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقداری از جنس تاریخ بدون در نظر گرفتن زمان در آرگومان serial_number تعریف شود، مقدار صفر به عنوان نتیجه تابع برگردانده می‌شود.
  4. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات زمان باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با زمان 20:11:20 قرار داده‌ایم. تابع Hour را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار ساعت از زمان 20:11:20 را فراخوانی کرده و عدد 20 را به عنوان نتیجه برمی‌گرداند.

=HOUR(A1)

بیشتر بخوانید: عملگرهای محاسباتی

تابع Minute

تابع Minute جزء توابع تاریخ و زمان می‌باشد که دقیقه مربوط به یک زمان مشخص را فراخوانی می‌کند.

ساختار تابع

MINUTE(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس زمان باشد.

آرگومان‌های تابع Minute

ویژگی‌های تابع

  1. ساعتی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون زمان‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقداری از جنس تاریخ بدون در نظر گرفتن زمان در آرگومان serial_number تعریف شود، مقدار صفر به عنوان نتیجه تابع برگردانده می‌شود.
  4. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات زمان باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با زمان 20:11:20 قرار داده‌ایم. تابع Minute را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار دقیقه از زمان 20:11:20 را فراخوانی کرده و عدد 11 را به عنوان نتیجه برمی‌گرداند.

=Minute(A1)

تابع Second

تابع Second جزء توابع تاریخ و زمان می‌باشد که ثانیه مربوط به یک زمان مشخص را فراخوانی می‌کند.

ساختار تابع

SECOND(serial_number)
آرگومانشرح
serial_numberاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد، آدرس سلول یا تابعی از جنس زمان باشد.

آرگومان‌های تابع Second

ویژگی‌های تابع

  1. ساعتی که مستقیما در آرگومان serial_number تعریف می‌شود باید بین "" قرار بگیرد.
  2. چون زمان‌ها به صورت عدد ذخیره می‌شوند، آن عدد می‌تواند در آرگومان serial_number تعریف شود.
  3. اگر مقداری از جنس تاریخ بدون در نظر گرفتن زمان در آرگومان serial_number تعریف شود، مقدار صفر به عنوان نتیجه تابع برگردانده می‌شود.
  4. اگر مقدار آرگومان serial_number خارج از مبنای محاسبات زمان باشد، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید مقدار سلول A1 را برابر با زمان 20:11:20 قرار داده‌ایم. تابع Second را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع مقدار ثانیه از زمان 20:11:20 را فراخوانی کرده و عدد 20 را به عنوان نتیجه برمی‌گرداند.

=SECOND(A1)

تابع Time

تابع Time جزء توابع تاریخ و زمان می‌باشد که یک زمان را به عنوان نتیجه تابع برمی‌گرداند.

ساختار تابع

TIME(hour,minute,second)
آرگومانشرح
hourاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول باشد. ساعت مربوط به زمان در این آرگومان تعیین می‌شود.
minuteاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول‌ باشد. دقیقه مربوط به زمان در این آرگومان تعیین می‌شود.
secondاختصاص این آرگومان ضروری بوده و می‌تواند شامل عدد یا آدرس سلول‌ باشد. ثانیه مربوط به زمان در این آرگومان تعیین می‌شود.

آرگومان‌های تابع Time

ویژگی‌های تابع

  1. اگر مقدار آرگومان hour بزرگتر از 23 باشد، این مقدار بر 24 تقسیم شده و باقیمانده آن به عنوان ساعت در نظر گرفته می‌شود.
  2. اگر مقدار آرگومان minute بزرگتر از 59 باشد، این مقدار به ساعت و دقیقه تبدیل می‌شود.
  3. اگر مقدار آرگومان second بزرگتر از 59 باشد، این مقدار به ساعت، دقیقه و ثانیه تبدیل می‌شود.
  4. آرگومان‌ها می‌توانند یک عدد بین صفر تا 32,767 باشند. اگر مقدار آرگومان‌ها کوچکتر از صفر یا بزرگتر از 32,767 باشند، خطا #Num! به عنوان نتیجه تابع برگردانده می‌شود.
مثال

فرض کنید می‌خواهیم مقدار سلول‌های A2، B2 و C2  را با هم ترکیب کرده و آنها را به عنوان زمان در سلول D2 قرار دهیم. تابع Time را به صورت زیر در سلول D2 تعریف می‌کنیم. این تابع مقدار سلول A2 را به عنوان ساعت، مقدار سلول B2 را به عنوان دقیقه و مقدار سلول C2 را به عنوان ثانیه در نظر گرفته و زمان 20:11:20 را به عنوان نتیجه برمی‌گرداند.

=TIME(A2,B2,C2)

محاسبه زمان با تابع Time

محاسبه زمان با تابع Time

تابع Timevalue

تابع Timevalue جزء توابع تاریخ و زمان می‌باشد که یک رشته نشان‌دهنده زمان را به یک داده از جنس زمان تبدیل می‌کند.

ساختار تابع

TIMEVALUE(time_text)
آرگومانشرح
time_textاختصاص این آرگومان ضروری بوده و می‌تواند شامل رشته یا آدرس سلول باشد.

آرگومان‌های تابع Timevalue

ویژگی‌های تابع
  1. اگر آرگومان time_text داری مقداری از جنس تاریخ باشد، آن مقدار در نظر گرفته نمی‌شود.
  2. مقادیری که از جنس زمان هستند بخشی از یک مقدار از جنس تاریخ می‌باشند که با یک عدد اعشاری نشان داده می‌شوند.
مثال

فرض کنیم می‌خواهیم رشته 20:11:20 را به یک داده از جنس زمان تبدیل کنیم. تابع Timevalue را به صورت زیر در یک سلول تعریف می‌کنیم. این تابع عدد 0.8,412 که متناظر با زمان 20:11:20 می‌باشد را به عنوان نتیجه برمی‌گرداند.

=TIMEVALUE("20:11:20")

محاسبات مربوط به زمان

فرض کنید می‌خواهیم اضافه‌کاری افراد را محاسبه کنیم. برای محاسبه میزان اضافه‌کاری، با فرض اینکه حداقل میزان کارکرد 8:20 می‌باشد فرمول زیر را در سلول D2 تعریف می‌کنیم. این فرمول مقدار 8:20 را از میزان کارکرد در سلول C2 کم کرده و مقدار 1:20 را به عنوان نتیجه برمی‌گرداند. با استفاده از ویژگی AutoFill این فرمول را برای دیگر سلول‌ها درگ می‌کنیم.

=C2-TIME(8,20,0)

محاسبه میزان اضافه‌کاری

محاسبه میزان اضافه‌کاری

برای محاسبه مجموع زمان‌های اضافه‌کاری، تابع Sum را در سلول B9 به صورت زیر تعریف می‌کنیم. این تابع مقدار 22:08 را به عنوان نتیجه برمی‌گرداند.

=SUM(D2:D8)

اگر مجموع زمان‌های اضافه‌کاری بزرگتر از 23:59 باشد، مقدار محاسبه شده به درستی نشان داده نمی‌شود. برای اصلاح مقدار محاسبه شده باید قالب سلول B9 را به روی Custom تنظیم کرده و از الگوی [hh]:mm استفاده کنیم.

با فرض اینکه هر ساعت اضافه کاری معادل 70,749 ریال می‌باشد، فرمول زیر را در سلول B10  تعریف می‌کنیم. این حاصل‌ضرب با قالب ساعت نشان داده می‌شود، آن را به Number تغییر می‌دهیم.

=B9*70749

پس از تغییر قالب میزان حاصل‌ضرب درست نمی‌باشد، زیرا اکسل مجموع ساعت را به روز تبدیل می‌کند. به عبارتی دیگر مجموع زمان‌های اضافه‌کاری را بر 24 ساعت تقسیم کرده و حاصل آن را در مبلغ 70,749 ریال ضرب می‌کند. برای به دست آوردن نتیجه درست باید حاصل آن را در عدد 24 ضرب کنیم، برای این‌کار فرمول زیر را جایگزین فرمول قبل می‌کنیم. این فرمول 22:08 را در مبلغ 70,749 ریال ضرب کرده و حاصل آن را در عدد 24 ضرب می‌کند و مقدار 1,565,911 ریال را به عنوان نتیجه محاسبه می‌کند.

=B9*70749*24

محاسبه مبلغ اضافه‌کاری

محاسبه مبلغ اضافه‌کاری