الملخص: في هذا البرنامج التعليمي ، ستتعلم كيفية استخدام صيغة Excel للعثور على آخر تواجد لعنصر في القائمة.
في الآونة الأخيرة ، كنت أعمل على جدول أعمال الاجتماع.
لدي قائمة في Excel بها قائمة بالأشخاص وتواريخ كونهم "رؤساء اجتماعات".
أحتاج أيضًا إلى معرفة متى كان الشخص "رئيس الاجتماع" الأخير بسبب الازدواجية في القائمة (مما يعني أن الشخص قد ترأس الاجتماع عدة مرات).
هذا لأنني يجب أن أتأكد من عدم إعادة تعيين آخر رئيس.
لذلك قررت استخدام بعض وظائف Excel الرائعة للقيام بهذه المهمة.
فيما يلي النتيجة النهائية ، يمكنني تحديد اسم من القائمة المنسدلة ويعطيني تاريخ آخر تكرار لهذا الاسم في القائمة.
إذا كان لديك فهم جيد لوظائف Excel ، فأنت تعلم أنه لا توجد وظيفة Excel يمكنها القيام بذلك.هنا نصنع المعجزات.
في هذا البرنامج التعليمي ، سأوضح لك ثلاث طرق للقيام بذلك.
المحتويات
ابحث عن آخر تواجد - باستخدام دالة MAX
فيما يلي صيغة Excel التي ستُرجع القيمة الأخيرة في القائمة:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
إليك كيفية عمل هذه الصيغة:
- يتم استخدام وظيفة MAX للعثور على رقم السطر لاسم آخر مطابق.على سبيل المثال ، إذا كان الاسم هو Glen ، فسيتم إرجاعه 11 لأنه في السطر 11.نظرًا لأن قائمتنا تبدأ في الصف الثاني ، يتم طرح 1.لذلك ، آخر تواجد لـ Glen هو 10 في قائمتنا.
- يتم استخدام SUMPRODUCT للتأكد من أنك لست مضطرًا إلى استخدام Control + Shift + Enter لأن SUMPRODUCT يمكنه التعامل مع صيغ الصفيف.
- تُستخدم الدالة INDEX الآن للعثور على تاريخ آخر اسم مطابق.
ابحث عن آخر تواجد - باستخدام وظيفة LOOKUP
إليك صيغة أخرى تقوم بنفس الوظيفة:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
إليك كيفية عمل هذه الصيغة:
- قيمة البحث هي 2 (سترى لماذا .. استمر في القراءة)
- نطاق البحث هو 1 / ($ A $ 2: $ A $ 14 = $ D $ 3) - يقوم بإرجاع 1 عندما يعثر على اسم مطابق ، وخطأ في الحالات الأخرى.لذلك ينتهي بك الأمر مع مصفوفة.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- المعلمة الثالثة ([result_vector]) هي النطاق الذي تعطي النتيجة فيه ، وفي هذه الحالة التاريخ.
سبب عمل هذه الصيغة هو أن الدالة LOOKUP تستخدم تقنية مطابقة تقريبية.هذا يعني أنه إذا كان بإمكانه العثور على تطابق تام ، فسوف يعود ، ولكن إذا لم يتمكن من ذلك ، فسوف يقوم بفحص المصفوفة بأكملها حتى النهاية وإرجاع القيمة الأكبر التالية التي تكون أقل من قيمة البحث.
في هذه الحالة ، تكون قيمة البحث 2 ، وفي المصفوفة الخاصة بنا ، نحصل على 1 أو خطأ.لذلك فإنه يمسح المصفوفة بأكملها ويعيد موضع آخر 1 - آخر قيمة مطابقة للاسم.
البحث عن آخر تواجد - استخدام الوظيفة المخصصة (VBA)
اسمحوا لي أن أريكم طريقة أخرى أيضا.
يمكننا إنشاء وظائف مخصصة (تُعرف أيضًا باسم الوظائف المعرفة من قبل المستخدم) باستخدام VBA.
فائدة إنشاء وظائف مخصصة هي سهولة الاستخدام.لا داعي للقلق بشأن إنشاء صيغ معقدة في كل مرة لأن معظم العمل يحدث في الواجهة الخلفية لـ VBA.
لقد أنشأت صيغة بسيطة (تشبه إلى حد كبير صيغة VLOOKUP).
لإنشاء وظائف مخصصة ، تحتاج إلى رمز VBA في محرر VB.سأعطيك الكود والخطوات لإدخاله في محرر VB لاحقًا ، لكن دعني أوضح لك كيف يعمل أولاً:
هذه هي الصيغة التي ستمنحك النتيجة:
= LastItemLookup ($ D $ 3 ، $ A $ 2: $ B $ 14,2،XNUMX)
تأخذ الصيغة ثلاث معاملات:
- ابحث عن القيمة (سيكون هذا الاسم في الخلية D3)
- ابحث عن النطاق (سيكون هذا هو النطاق بالاسم والتاريخ - A2: B14)
- رقم العمود (هذا هو العمود الذي نريد نتيجة له)
بعد إنشاء الصيغة ووضع الكود في محرر VB ، يمكنك استخدامه مثل أي دالة ورقة عمل Excel عادية أخرى.
هذا هو رمز الصيغة:
الوظيفة LastItemLookup (قيمة البحث كسلسلة ، نطاق البحث كنطاق ، رقم العمود كعدد صحيح) خافت طويًا بالنسبة إلى i = LookupRange.Columns (1) .Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells (i، 1) ثم LastItemLookup = LookupRange.Cells (i ، ColumnNumber) قم بإنهاء وظيفة نهاية إذا التالي أنا وظيفة النهاية
فيما يلي خطوات وضع هذا الرمز في محرر VB:
- انتقل إلى علامة التبويب المطور.
- انقر فوق خيارات Visual Basic.سيؤدي هذا إلى فتح محرر VB في الخلفية.
- في جزء Project Explorer بمحرر VB ، انقر بزر الماوس الأيمن فوق أي كائن في المصنف حيث تريد إدراج التعليمات البرمجية.إذا كنت لا ترى Project Explorer ، فانتقل إلى علامة التبويب عرض وانقر فوق Project Explorer.
- انتقل إلى "إدراج" وانقر فوق "الوحدات النمطية".سيؤدي هذا إلى إدراج كائن وحدة لمصنفك.
- انسخ الكود والصقه في نافذة الوحدة النمطية.
الآن ستتوفر الصيغة في جميع أوراق المصنف.
لاحظ أنك ستحتاج إلى حفظ المصنف بتنسيق XLSM لأنه يحتوي على وحدات ماكرو.أيضًا ، إذا كنت تريد إتاحة هذه الصيغة في جميع المصنفات التي تستخدمها ، فيمكنك حفظها في مصنف الماكرو الشخصي أو إنشاء وظيفة إضافية منه.
