{= 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))}