كيف يمكنك تحديد فترات التواريخ أو نطاقات الوقت المتداخلة في Excel؟
في Excel، قد يؤدي تداخل التواريخ أو نطاقات الوقت إلى تعارضات في الجدولة، أو مشكلات في تخصيص الموارد، أو انتهاك سلامة البيانات. ولذلك، يُعدّ تحديد هذه التداخلات بكفاءة أمرًا بالغ الأهمية لإدارة الجداول الزمنية، وتخطيط الفعاليات، وأنظمة الحجز، أو جداول مراحل المشاريع التي يجب ألا تتزامن فتراتها. يقدم هذا المقال إرشادات خطوة بخطوة حول الأساليب العملية المختلفة لاكتشاف التواريخ أو نطاقات الوقت المتداخلة في Excel، كما هو موضح في لقطة الشاشة أدناه.
التحقق من تداخل التواريخ/نطاق الوقت باستخدام صيغة
كود VBA - أتمتة الكشف عن تداخل التواريخ/نطاق الوقت لمجموعات البيانات الكبيرة أو لـ إنشاء تقرير
استخدم تنسيق الشروط - تمييز النطاقات المتداخلة بصريًّا مباشرةً في ورقة العمل لتسهيل التعرّف عليها
التحقق من تداخل التواريخ/نطاق الوقت باستخدام صيغة
عندما تحتاج إلى التحقق بشكل منهجي مما إذا كانت التواريخ أو نطاقات الوقت متداخلة، توفّر صيغ Excel حلاً سريعًا ومرنًا. ويُعدّ هذا الأسلوب مثاليًا لمجموعات البيانات الصغيرة إلى المتوسطة، أو عندما تحتاج إلى نتيجة منطقية (صواب أو خطأ) تُشير إلى وجود تداخلات على أساس كل صف.
حالات الاستخدام النموذجية:جدولة ورديات الموظفين، أو حجز الفعاليات، أو تتبع مراحل المشاريع، أو إدارة عمليات التأجير—حيث يمثّل كل صف فترة زمنية تبدأ بتاريخ/وقت معيّن وتنتهي بتاريخ محدّد.
القيود: على الرغم من كفاءة الصيغ في التعامل مع مجموعات البيانات المعتدلة، إلا أنها قد لا تكون الخيار الأمثل لمجموعات البيانات الضخمة جدًّا أو لإنشاء تقارير شاملة عن التداخلات عبر سجلات متعددة.
1. حدد جميع الخلايا التي تحتوي على تاريخ البدء. مع تسليط الضوء على النطاق، انقر داخلمربع الاسم (الحقل الموجود يسار شريط الصيغة) واكتب اسمًا وصفيًّا، مثلتاريخ_البدء. ثم اضغط علىEnter للتأكيد. تتيح لك هذه الخطوة الرجوع بسهولة إلى القائمة الكاملة في الصيغ. انظر لقطة الشاشة:
2. وبالمثل، حدد خلايا تاريخ الانتهاء، وأدخل اسم الخلية فيمربع الاسممثلتاريخ_الانتهاء، ثم اضغط علىEnter مرة أخرى. تساعدك تسمية النطاقات على جعل صيغك أكثر وضوحًا وقابلة لإعادة الاستخدام.
3. انقر على خلية فارغة في نفس الصف الذي يحتوي على أول سجل لديك—مثلًا، C2—حيث ترغب في ظهور نتائج التداخل، ثم أدخل الصيغة التالية:
=SUMPRODUCT((A2<enddate)*(B2>=startdate))>1استبدلA2 بالخلية التي تحتوي على تاريخ البدء لسجلّك، وB2 بتاريخ الانتهاء الخاص به. يُستخدمتاريخ_البدءوتاريخ_الانتهاءكأسماء قمت بتعريفها مسبقًا. تتحقّق هذه الصيغة مما إذا كانت الفترة الحالية تتداخل مع أي فترة أخرى في القائمة. اضغط على Enter، ثم اسحب مقبض التعبئة لأسفل عبر جميع الصفوف التي تريد التحقق منها. بالنسبة لكل صف، تشير القيمةصوابإلى أن النطاق المقابل يتداخل مع نطاق آخر على الأقل، وإلا فهذا يعني أنه لا يوجد تداخل.
تأكد من أن كلًّا منتاريخ_البدءوتاريخ_الانتهاءيشيران إلى فرز الأعمدة بأكملها التي تحتوي على قيم البداية والنهاية. عدِّل مراجع الخلايا حسب الحاجة إذا اختلفت أعمدتك أو كانت نطاقاتك تحتوي على عناوين.
ملاحظات هامة واستكشاف الأخطاء وإصلاحها:
- إذا ظهر لك خطأ #VALUE!، فتأكد من صحة اسم الخلية والمراجع الخاصة بك، وأن أعمدة التواريخ لا تحتوي على نصوص أو بيانات تواريخ/أوقات بتنسيق غير صحيح.
- يأخذ هذا الأسلوب في الاعتبار الحالات المتداخلة التي لا تكون فيها الفترات الزمنية منفصلة تمامًا. عادةً، لا يُعدّ التداخل موجودًا عندما تتلامس الفترات عند نقاط النهاية فقط (أي عندما يتطابق تاريخ انتهاء إحداها تمامًا مع تاريخ بدء الأخرى)، ولكن يمكنك تعديل رمز عدم المساواة في الصيغة لضبط هذا السلوك.
- بالنسبة إلى نطاق الوقت (بما في ذلك الساعات والدقائق)، تعمل الصيغة بنفس الطريقة المستخدمة مع التواريخ، بشرط أن تكون الخلايا منسَّقة بشكل متسق كتواريخ أو أوقات.
كود VBA - أتمتة الكشف عن تداخل التواريخ/نطاق الوقت لمجموعات البيانات الكبيرة أو لـ إنشاء تقرير
إذا كنت تعمل بانتظام مع مجموعات بيانات كبيرة وتحتاج إلى طريقة أكثر أتمتة لتحديد التداخلات—خاصةً عند إنشاء تقارير ملخّصة أو وضع علامات على جميع الإدخالات المتضاربة دفعة واحدة—فإن استخدام VBA يمكن أن يبسّط العملية بشكل كبير. يلغي هذا الأسلوب الحاجة إلى الفحوصات اليدوية، ويُعدّ مثاليًا لمئات أو آلاف الفترات، ويمكن تخصيصه لتمييز أزواج التداخل أو إدراجها في قائمة.
متى تستخدمه:موصى به للمستخدمين المتقدمين الذين يديرون قواعد بيانات جدولة كبيرة أو موارد مشتركة، أو لأي شخص يحتاج إلى إنشاء سجلات تتضمّن جميع التداخلات المكتشفة، بدلًا من الاكتفاء بعلامة صواب/خطأ لكل صف.
العيوب المحتملة:يتطلب تمكين ماكرو ومعرفةً أوليةً بلغة VBA، إضافةً إلى عمل نسخة احتياطية دقيقة للبيانات قبل التشغيل الأول لتجنب الكتابة فوق البيانات عن طريق الخطأ.
1. انقر فوقأدوات المطور > Visual Basic لفتح نافذةMicrosoft Visual Basic for Applications. بعد ذلك، انقر فوقإدراج > وحدة نمطية، والصق الكود أدناه في نافذة الوحدة النمطية:
Sub FindOverlappingDateRanges()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim lastRow As Long
Dim overlapList As String
Dim msg As String
Dim Start1, End1, Start2, End2
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' Assumes data starts in row 2
overlapList = ""
For i = 2 To lastRow
Start1 = ws.Cells(i, 1).Value
End1 = ws.Cells(i, 2).Value
If Start1 <> "" And End1 <> "" Then
For j = 2 To lastRow
If i <> j Then
Start2 = ws.Cells(j, 1).Value
End2 = ws.Cells(j, 2).Value
If Start2 <> "" And End2 <> "" Then
If Start1 < End2 And End1 > Start2 Then
overlapList = overlapList & "Row " & i & " overlaps with Row " & j & vbCrLf
End If
End If
End If
Next j
End If
Next i
If overlapList <> "" Then
msg = "The following rows have overlapping date/time ranges:" & vbCrLf & overlapList
Else
msg = "No overlapping date/time ranges found."
End If
MsgBox msg, vbInformation, "KutoolsforExcel"
End Sub2. بعد إدخال الكود، انقر فوقتشغيلأو اضغط على Enter لتنفيذه. يقوم الماكرو بمسح أزواج نطاقات التواريخ في العمود A (البداية) والعمود B (النهاية)، ويُبلّغ عن أي تداخلات يجدها. وسيعرض مربع رسالة يسرد جميع الصفوف التي تحتوي على تعارضات، مما يسهّل مراجعتها أو التحقيق فيها.
- تأكد من وجود قيم تاريخ البداية وتاريخ الانتهاء في العمودين A وB، بدءًا من الصف 2 (مع افتراض أن الصف 1 يحتوي على العناوين). وقم بتعديل النطاقات إذا كانت بياناتك مرتبة بطريقة مختلفة.
- يجب أن تحتوي جميع الخلايا في النطاق المقارَن على قيم تواريخ/أوقات صالحة، دون أي خلايا فارغة.
- احفظ نسخة احتياطية من ملفاتك المهمة قبل تشغيل أو تعديل أي كود VBA، لتجنب فقدان البيانات.
تلميح:يمكنك تحسين كود VBA لتمييز التداخلات مباشرةً في ورقة العمل عن طريق تلوين الصفوف أو كتابة النتائج في عمود مجاور.
استخدم تنسيق الشروط - تمييز النطاقات المتداخلة بصريًّا مباشرةً في ورقة العمل لتسهيل التعرّف عليها
يُعد استخدام التنسيق الشرطي وسيلةً عمليةً لوضع علامات مرئية على فترات التواريخ أو الأوقات المتداخلة مباشرةً في جدول البيانات الخاص بك. ويُعد هذا الحل مثاليًا خصوصًا في الجداول الزمنية المزدحمة أو مخططات جانت أو خطوط زمن الفعاليات، حيث ترغب في رؤية السجلات المتضاربة لمجرد نظرةٍ سريعة.
الأفضل لـ:المستخدمين الذين يبحثون عن ملاحظات فورية على الورقة أو إشارات لونية دون الحاجة إلى استخدام صيغ في كل صف أو تشغيل أي كود. مثالي للفحوصات التفاعلية والعروض التقديمية!
القيود:قد تواجه مجموعات البيانات الكبيرة بطئًا في الاستجابة؛ وعلى الرغم من تمييز التداخلات، لا يتم إنشاء أزواج أو إحصائيات مفصلة.
كيفية التطبيق:
- حدد نطاق تاريخ البدء (مثل: A2:A100) وتاريخ الانتهاء (B2:B100)، أو حدد العمودين معًا إذا كان النطاقان متجاورين.
- في علامة التبويبالصفحة الرئيسية، انقر فوقاستخدم تنسيق الشروط > قاعدة جديدة.
- اختراستخدم صيغة لتحديد الخلايا التي سيتم تنسيقها.
- أدخل هذه الصيغة في مربع الصيغة (بافتراض أن تحديدك يبدأ من الصف 2):
=SUMPRODUCT(($A2<$B$2:$B$100)*($B2>$A$2:$A$100))>1 - انقر فوقتنسيق…، واختر تعبئة اللون لتمييز النطاقات المتداخلة، ثم انقر فوقموافقلتطبيق التنسيق.
بعد تطبيق القاعدة، سيتم تمييز أي صف تتداخل فيه الفترة المحددة مع فترة أخرى في نطاقك بصريًّا، مما يسهّل اكتشاف المشكلات دون الحاجة إلى قراءة كل إدخال على حدة.
تلميح:عدّل$A$2:$A$100 و$B$2:$B$100 لتتناسب مع نطاق البيانات الفعلي الخاص بك، وتأكد من أن المراجع تتطابق مع أول صف في تحديدك.
احتياطات:إذا أردت تمييز أحد العمودين فقط (مثلًا، تاريخ البدء فقط)، فاستخدم منطق الصيغة المناسب. واحرص على مراعاة التداخل عند نقاط الحدود وفقًا لمتطلبات منطقك.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل