اكسل

احصل على أقرب وأحدث تواريخ المشروع

Get Earliest Latest Project Dates

صيغة Excel: احصل على أقرب وأحدث تواريخ المشروعملخص

يوضح هذا المثال كيفية استرداد أقدم وأحدث التواريخ المرتبطة بالمشروع. في المثال الموضح ، الصيغ في H5 و I5 هي:



 
= MINIFS (data[Start],data[Project],G5) // earliest = MAXIFS (data[End],data[Project],G5) // latest

حيث 'البيانات' هي ملف جدول Excel كما هو موضح ، وتتطابق أسماء المشاريع في العمود G مع العمود B.

ملاحظة: MINIFS و MAXIFS متاحان فقط في اكسل 365 و Excel 2019. في الإصدارات الأخرى من Excel ، يمكنك استخدام صيغة صفيف بسيطة ، كما هو موضح أدناه.





مقدمة

المهمة هنا هي العثور على أقدم وأحدث التواريخ المرتبطة بمشروع معين. أقدم التواريخ تأتي من يبدأ العمود ، وأحدث التواريخ تأتي من نهاية عمودي.

قد تميل إلى استخدام دالة بحث مثل VLOOKUP و XLOOKUP ، أو الفهرس والمطابقة. ومع ذلك ، نظرًا لأن كل مشروع يحتوي على أكثر من إدخال واحد ، وقد لا يتم دائمًا فرز الإدخالات حسب التاريخ ، يصبح هذا أمرًا صعبًا.



الأسلوب الأفضل هو استخدام عملية الحذف: تجاهل التواريخ لمشاريع أخرى ، والعمل فقط مع التواريخ المتبقية.

تفسير

ال دالة MINIFS إرجاع ال أصغر القيمة الرقمية التي تفي بالمعايير المقدمة ، و وظيفة MAXIFS إرجاع ال أكبر القيمة الرقمية التي تلبي المعايير المقدمة.

مثل COUNTIFS و SUMIFS ، تستخدم هذه الدالات نطاق / معايير 'أزواج' لتطبيق الشروط. لكلتا الصيغتين ، نحتاج إلى شرط واحد فقط: يجب أن يساوي اسم المشروع الاسم الموجود في العمود G:

 
data[Project],G5 // condition

للحصول على باكرا جدا تاريخ البدء ، نستخدم:

 
= MINIFS (data[Start],data[Project],G5) // earliest date

هنا ، تقوم MINIFS بإرجاع ملف الحد الأدنى قيمة في يبدأ العمود حيث المشروع يساوي 'أوميغا' (من الخلية G5). حيث تواريخ Excel هي مجرد أرقام ، الحد الأدنى للتاريخ هو نفس التاريخ الأقدم.

كيف تتخلص من #value in excel

للحصول على الأحدث تاريخ الانتهاء ، نستخدم:

 
= MAXIFS (data[End],data[Project],G5) // latest date

هنا ، تقوم MAXIFS بإرجاع ملف أقصى قيمة في نهاية العمود حيث المشروع يساوي 'أوميغا'. على النحو الوارد أعلاه ، فإن القيمة القصوى هي نفس أحدث تاريخ.

بديل صيغة الصفيف

إذا لم يكن لديك MINIFS و MAXIFS ، فيمكنك استخدام صيغ صفيف بسيطة ، استنادًا إلى ملف دقيقة و الأعلى وظائف ، للحصول على نفس النتيجة. لأقرب تاريخ بدء:

 
{= MIN ( IF (data[Project]=G5,data[Start]))}

لآخر تاريخ انتهاء:

 
{= MAX ( IF (data[Project]=G5,data[End]))}

ملاحظة: كلا الصيغتين صيغ الصفيف ويجب إدخالها باستخدام control + shift + enter ، في Excel 2019 أو إصدار سابق. باستخدام Excel 365 ، يمكنك إدخال الصيغ بشكل طبيعي ، لأن صيغ الصفيف أصلية .

في كلتا الحالتين ، فإن إذا كانت الدالة تُستخدم 'لتصفية' قيم التاريخ مثل هذا:

 
 IF (data[Project]=G5,data[End]) // filter dates by project

عندما تكون G5 هي 'Omega' ، تُرجع IF تاريخ الانتهاء. وبخلاف ذلك ، تقوم IF بإرجاع FALSE. نظرًا لأننا نختبر جميع أسماء المشاريع في الجدول في نفس الوقت ، فإن النتيجة هي مجموعة من القيم مثل هذا:

 
{439364398343990FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE}

الأرقام التسلسلية الكبيرة هي تواريخ Excel مرتبطة بمشروع أوميغا. القيم الأخرى خاطئة ، لأن المشروع ليس أوميغا. نظرًا لأن MIN و MAX تمت برمجتهما لتجاهل القيم المنطقية TRUE و FALSE ، فإنهما يعملان فقط على القيم المتبقية. تُرجع الدالة MIN أصغر تاريخ (أقدم) ، وتُرجع MAX التاريخ الأكبر (الأحدث).

المرفقات ملف الحصول على أقرب وأحدث تواريخ المشروع. xlsx المؤلف ديف برونز


^