2. دالات التاريخ و الوقت
قبل التطرق لبعض دوال Excel الخاصة بالوقت و التاريخ ، يجب معرفة كيفية تدبير التاريخ والوقت من طرف Excel.
كيف يعالج Excel التواريخ والأوقات؟ مفهوم الرقم التسلسلي
عند كتابة قيم بتنسيقات معينة ، يمكن لـ Excel التعرف عليها كتواريخ وأوقات. هذه بعض الأمثلة :
- 14/05/2018
- 03:50
- 17:20 03/08/2018
- 1:15:30
- الخ ...
لن يتعرف Excel على "20/15/2018" كتاريخ. فهو ليس تاريخًا صحيحا.
كيف يمكنك معرفة ما إذا كان Excel قد تعرف على ما كتبت كتاريخ ووقت؟
أطلب منك إدخال هذه القيم في ورقة Excel كما يلي:
ثم حدد النطاق من A2 إلى A6 وقم بتحويل تنسيقه إلى "عام". أذكرك كيف:
- حدد النطاق من A2 إلى A6
- استخدم الزر تنسيق في المجموعة خلايا من علامة التبويب الرئيسية للشريط
- انقر فوق تنسيق خلايا في القائمة التي تظهر
- يظهر مربع الحوار تنسيق خلايا. انقر فوق علامة التبويب رقم
- أخيرًا ، انقر فوق عام في القائمة الفئة. تم فوق الزر موافق.
هذه هي النتيجة:
تم تحويل القيم التي تعرف عليها Excel كتواريخ وأوقات إلى أرقام.
كيف يعالج Excel التواريخ والأوقات؟
يقوم Excel بحفظ التواريخ والأوقات كأرقام مع الأجزاء العشرية. يسمى الرقم الخاص بوقت و تاريخ بالرقم التسلسلي.
إذن ، بالنسبة إلى Excel ، التواريخ والأوقات تسجل كأرقام. على سبيل المثال ، عندما تكتب "14/05/2018" ، يقوم Excel بحفظه كرقم ويعرضه كما كتبته أو بتنسيق آخر . و يمكنك بعد ذلك تغيير تنسيقه كما تريد. مثلا أن تعرض السنة بإثنان أو أربعة أرقام.
و يمكنك حتى إدخال رقم في خلية تم تحويل تنسيقها إلى تاريخ ووقت. وفي هذه الحالة تعرض Excel التاريخ المقابل للعدد الذي أدخلته.
فكيف إذن هو التقابل بين الوقت/التاريخ والعدد الرقم التسلسلي؟
أولاً ، الجزء الصحيح يقابل التاريخ والجزء العشري يقابل الوقت.
لنشرح هذا التقابل بواسطة مثال. القيمة 43315.7258680556 هي الرقم التسلسلي المقابل للتاريخ/الوقت 17:25:15 03/08/2018.
إذن الجزء العشري 0.7258680556 هو الساعة و الدقيقة و الثانية. لحساب الساعة ، اضربه في 24:
0.7258680556 * 24 = 17.42083333
الجزء الصحيح "17" من هذه القيمة هو الساعة. الجزء العشري من هذه القيمة ، سأضربه في 60:
0.42083333 * 60 = 25.25
الجزء الصحيح "25" من هذه القيمة هو الدقيقة. الجزء العشري من هذه القيمة ، سأضربه في 60:
0.25 * 60 = 15
15 هو عدد الثواني.
بالنسبة للتواريخ:
القيمة 1 تقابل 01/01/1900. علاوة على ذلك ، لا يقوم Excel بمعالجة التواريخ السابقة لـ 01/01/1900.
الفرق بين الأرقام التسلسلية ليومين متتابعين هو 1.
يعد حساب اليوم والشهر والسنة من الرقم التسلسلي أمرًا معقدًا بعض الشيء لأنك يجب أن تأخذ في الاعتبار عدد الأيام في كل شهر ومراعاة السنوات الكبيسة.
تتوفر دوال Excel لإرجاع مكونات الوقت و التاريخ من رقمه التسلسلي.
الدالتان TODAY و NOW
بناء الدالة
TODAY()
NOW()
الوسيطات
هتان الدالتان لا تأخذان أي وسيطة.
النتاج
ترجع الدالة TODAY تاريخ اليوم
ترجع الدالة NOW التاريخ والوقت الحاليين
مثال
الدالتان DATE و TIME
بناء الدالة
DATE(Year,Month,Day)
TIME(Hour,Minute,Second)
الوسيطات
Year : عدد بين 1900 و 1999 الذي يحدد السنة للتاريخ الذي سيتم إرجاعه كنتيجة
Month : رقم من 1 إلى 12 الذي يحدد الشهر للتاريخ الذي سيتم إرجاعه كنتيجة
Day : رقم من 1 إلى 31 الذي يحدد اليوم للتاريخ الذي سيتم إرجاعه كنتيجة
Hour : رقم من 0 إلى 23 الذي يحدد الساعة للوقت الذي سيتم إرجاعه كنتيجة
Minute : رقم من 0 إلى 59 الذي يحدد الدقيقة للوقت الذي سيتم إرجاعه كنتيجة
Second : رقم من 0 إلى 59 الذي يحدد الثانية للوقت الذي سيتم إرجاعه كنتيجة
النتاج
ترجع الدالة DATE تاريخ يمثل السنة والشهر واليوم التي أعطيت كوسيطات
ترجع الدالة TIME وقت يمثل الساعة والدقيقة والثانية التي أعطيت كوسيطات
مثال
في جدول بيانات لدينا عناصر التاريخ في 3 أعمدة ، فالعمود A للسنة ، و B للشهر ، و C لليوم. نريد الحصول في العمود D على التاريخ المقابل.
الصيغة المراد كتابتها في D2 هي:
=DATE(A2,B2,C2)
لاحظ أنه عندما يكون اليوم أكبر من 30 أو الشهر أكبر من 12 فإن الدالة DATE لا ترجع خطأ. بل يتحول الفائض إلى شهور و سنوات .
و كذلك عندما تكون الثانية أو الدقيقة أكبر من 60 فإن الدالة TIME لا ترجع خطأ. بل يتحول الفائض إلى دقائق و ساعات .
الدالة DAYS
ترجع الدالة DAYS عدد الأيام بين تاريخين.
بناء الدالة
DAYS(End_date,Start_date)
الوسيطات
End_date : تاريخ الانتهاء من الفترة التي سيتم حسابها
Start_date : تاريخ البدء من الفترة التي سيتم حسابها
النتاج
ترجع الدالة DAYS عدد الأيام بين تاريخين. إذا كان End_date أقدم من Start_date ، يكون النتاج عدد سالب.
ملاحظة : نظرًا لأن التواريخ في Excel هي أرقام ، يمكننا حساب عدد الأيام بين تاريخين بواسطة الصيغة :
end_date - start_date
و لكن هل هناك اختلاف مع نتاج الدالة DAYS؟
الإجابة هي نعم عندما يحتوي أحد التواريخ أو كلا التاريخين على الوقت لأن DAYS لا تحسب الوقت وتُرجع دائمًا عدد صحيح.
مثال
هذا جدول بيانات حيث سجل تاريخ الانتهاء من مرحلتين من العملية. نريد حساب المدة بين المرحلتين. الصيغة في C2 هي:
=DAYS(B2,A2)
الدالة EDATE
كيفية إضافة عدد من الأيام إلى تاريخ؟
أذكر أن التواريخ في Excel يتم تمثيلها بأرقام تسمى الأرقام التسلسلية وأن الفرق بين الأرقام التسلسلية ليومين متتابعين هو 1. لذلك ، لإضافة عدد من الأيام إلى تاريخ ، ما عليك سوى إضافة عدد الأيام إلى التاريخ.
على سبيل المثال ، القيمة في الخلية A2 من ورقة عمل هي تاريخ ، و نريد أن نحصل في الخلية B2 على التاريخ بعد أسبوع. في الخلية B2 ، اكتب الصيغة:
= A2 + 7
أذكر أنه يمكن عرض التاريخ الوارد في A2 بأي تنسيق ، ولكن يجب أن يتعرف Excel عليه كتاريخ.
وكيفية إضافة عدد من الشهور إلى تاريخ؟ هل يكفي إضافة هذا الرقم مضروبا في 30؟
الجواب هو أن النتيجة في هذه الحالة لن تكون دقيقة. والسبب هو أن الأشهر ليست كلها 30 يومًا. على سبيل المثال ، إذا أضفت 30 إلى "01 تموز" ، فسوف أحصل على "31 تموز" وليس "01 آب". و كذلك ، إذا أضفت 30 إلى "01 شباط" ، فسوف تحصل على "02 آذار" أو "03 آذار".
لإضافة عدد من الشهور و تكون النتيجة نفس اليوم من الشهر ، وفر Excel الدالة EDATE.
بناء الدالة
EDATE(start_date,months)
الوسيطات
start_date : التاريخ الذي نريد إضافة عدد من الشهور إليه
months : عدد الشهور التي نريد إضافتها
النتاج
ترجع الدالة EDATE التاريخ بعد start_date بعدد الأشهر المحددة بالوسيطة months إذا كانت هذه الوسيطة موجبة. و ترجع التاريخ قبل start_date بعدد الأشهر المحددة بالوسيطة months إذا كانت هذه الوسيطة سالبة.
ملاحظة : لإضافة سنوات إلى تاريخ ، استخدم الدالة EDATE ، و في الوسيطة الثانية عدد السنوات مضروبة في 12.
مثال
في ورقة العمل التالية ، يتم احتساب تاريخ الترقية وهو بعد عامين ونصف من تاريخ التوظيف. الصيغة في الخلية C2 هي:
=EDATE(B2;30)
الدوال YEAR و MONTH و DAY و WEEKDAY و HOUR و MINUTE و SECOND
بناء الدالة
YEAR(Serial_number)
MONTH(Serial_number)
DAY(Serial_number)
WEEKDAY(Serial_number)
HOUR(Serial_number)
MINUTE(Serial_number)
SECOND(Serial_number)
الوسيطة
Serial_number : تاريخ (و هو ممثل في Excel برقم يسمى الرقم التسلسلي) نريد إرجاع أحد مكوناته السنة أوالشهر أو اليوم من الشهر أو اليوم من الأسبوع أوالساعة أوالدقيقة أو الثانية.
النتاج
ترجع الدالة YEAR السنة للتاريخ المعطى كوسيطة.
ترجع الدالة MONTH الشهر للتاريخ المعطى كوسيطة: عدد من 1 إلى 12
ترجع الدالة DAY اليوم من الشهر للتاريخ المعطى كوسيطة: عدد من 1 إلى 31
ترجع الدالة WEEKDAY اليوم من الأسبوع للتاريخ المعطى كوسيطة: عدد من 1 إلى 7
ترجع الدالة HOUR الساعة للتاريخ المعطى كوسيطة: عدد من 0 إلى 23
ترجع الدالة MINUTE الدقيقة للتاريخ المعطى كوسيطة: عدد من 0 إلى 59
ترجع الدالة SECOND الثانية للتاريخ المعطى كوسيطة: عدد من 0 إلى 59
تمارين
تعليقات القارئ
