ابحث عن مراجعة الملف الأخير

{= MAX ( IF ( ISERROR ( SEARCH (H5&'*',files)),0, ROW (files)- ROW ( INDEX (files,1,1))+1))}

للعثور على موضع (صف) آخر مراجعة للملف في جدول ، يمكنك استخدام صيغة تستند إلى العديد من وظائف Excel: MAX و IF و ISERROR و ROW و INDEX.

في المثال الموضح ، الصيغة في الخلية H6 هي:

كيفية حساب متوسط ​​الصف في Excel

{= MAX (IF (ISERROR (SEARCH (H5 & '*'، files)))، 0، ROW (ملفات) -ROW (INDEX (ملفات ، 1،1)) + 1))}

حيث 'الملفات' هي النطاق المسمى C4: C11.

ملاحظة: هذه صيغة مصفوفة ويجب إدخالها باستخدام control + shift + enter.

مفهوم

في هذا المثال ، لدينا عدد من إصدارات الملفات المدرجة في جدول مع التاريخ واسم المستخدم. لاحظ أن أسماء الملفات مكررة ، باستثناء الكود المُلحق في النهاية لتمثيل الإصدار ('CA' ، 'CB' ، 'CC' ، 'CD' ، إلخ.).

بالنسبة لملف معين ، نريد تحديد الموضع (رقم الصف) للمراجعة الأخيرة. هذه مشكلة صعبة ، لأن رموز الإصدار في نهاية أسماء الملفات تجعل من الصعب مطابقتها مع اسم الملف. أيضًا ، بشكل افتراضي ، ستُرجع صيغ تطابق Excel المباراة الأولى ، وليس المطابقة الأخيرة ، لذلك نحتاج إلى التغلب على هذا التحدي ببعض الأساليب الصعبة.

في جوهر هذه الصيغة ، نبني قائمة بأرقام الصفوف لملف معين. ثم نستخدم الدالة MAX للحصول على أكبر رقم صف ، والذي يتوافق مع آخر مراجعة (آخر تواجد) لهذا الملف.

للعثور على جميع تكرارات ملف معين ، نستخدم وظيفة SEARCH ، التي تم تكوينها باستخدام حرف البدل العلامة النجمية (*) لمطابقة اسم الملف ، مع تجاهل رموز الإصدار. سيؤدي SEARCH إلى ظهور خطأ VALUE عندما لا يتم العثور على النص ، لذلك نقوم بإتمام البحث في ISERROR:

 
 ISERROR ( SEARCH (H5&'*',files))

ينتج عن هذا مصفوفة من قيم TRUE و FALSE مثل هذا:

{FALSETRUEFALSEFALSETRUETRUEFALSETRUE}

ماذا لو تفوق جدول بيانات التحليل

إنه أمر محير ، لكن TRUE تمثل خطأ (لم يتم العثور على النص) ، ويمثل FALSE تطابقًا. يتم إدخال نتيجة الصفيف هذه في دالة IF كاختبار منطقي. للقيمة إذا كانت TRUE ، نستخدم الصفر ، وللقيمة إذا كانت صحيحة ، نقدم هذا الرمز ، الذي يولد أرقام الصفوف النسبية للنطاق الذي نعمل معه:

 
 ROW (files)- ROW ( INDEX (files,1,1))+1)

تقوم الدالة IF بعد ذلك بإرجاع مصفوفة من القيم مثل هذا:

{10340070}

تمثل جميع الأرقام باستثناء الصفر مطابقات لـ 'filename1' - أي رقم الصف داخل النطاق المسمى 'files' حيث يظهر 'filename1'.

أخيرًا ، نستخدم الدالة MAX للحصول على القيمة القصوى في هذه المصفوفة ، وهي 7 في هذا المثال.

استخدم INDEX مع رقم الصف هذا لاسترداد المعلومات المتعلقة بالمراجعة الأخيرة (على سبيل المثال ، اسم الملف الكامل ، التاريخ ، المستخدم ، إلخ).

بدون نطاق مسمى

تجعل النطاقات المسماة إعداد صيغة أكثر تعقيدًا أمرًا سريعًا وسهلاً ، حيث لا يتعين عليك إدخال عناوين الخلية يدويًا. ومع ذلك ، في هذه الحالة ، نستخدم وظيفة إضافية (INDEX) للحصول على الخلية الأولى من النطاق المسمى 'ملفات' ، مما يعقد الأمور قليلاً. بدون النطاق المسمى ، تبدو الصيغة كما يلي:

 
{= MAX ( IF ( ISERROR ( SEARCH (H5&'*',C4:C11)),0, ROW (C4:C11)- ROW (C4)+1))}