المحتويات
جميع وظائف البحث والنوع الكلاسيكية VPR (البحث), GPR (هلوكوب), أكثر انكشافاً (مباراة) وهؤلاء أمثالهم لديهم ميزة مهمة واحدة - يبحثون من البداية إلى النهاية ، أي من اليسار إلى اليمين أو من أعلى إلى أسفل في بيانات المصدر. بمجرد العثور على أول تطابق مطابق ، يتوقف البحث ويتم العثور فقط على أول ظهور للعنصر الذي نحتاجه.
ماذا نفعل إذا كنا بحاجة إلى إيجاد ليس الأول ، بل الأخير؟ على سبيل المثال ، آخر معاملة للعميل ، وآخر دفعة ، وآخر طلب ، وما إلى ذلك؟
الطريقة 1: البحث عن الصف الأخير باستخدام صيغة صفيف
إذا كان الجدول الأصلي لا يحتوي على عمود به تاريخ أو رقم تسلسلي لصف (أمر ، دفع ...) ، فإن مهمتنا ، في الواقع ، هي العثور على الصف الأخير الذي يلبي الشرط المحدد. يمكن القيام بذلك باستخدام صيغة الصفيف التالية:
هنا:
- المسمى الوظيفي IF (إذا) يتحقق من جميع الخلايا في عمود واحدًا تلو الآخر التسجيل كعميل ويعرض رقم السطر إذا كان يحتوي على الاسم الذي نحتاجه. يتم إعطاء رقم السطر على الورقة من خلال الوظيفة خط (صف)، ولكن نظرًا لأننا نحتاج إلى رقم الصف في الجدول ، يتعين علينا أيضًا طرح 1 ، لأن لدينا رأسًا في الجدول.
- ثم الوظيفة MAX (ماكس) يختار الحد الأقصى للقيمة من مجموعة مكونة من أرقام الصفوف ، أي رقم السطر الأحدث للعميل.
- المسمى الوظيفي INDEX (فهرس) تُرجع محتويات الخلية مع الرقم الأخير الذي تم العثور عليه من أي عمود جدول مطلوب آخر (رمز الطلب).
كل هذا يجب إدخاله على شكل صيغة الصفيف، بمعنى آخر:
- في Office 365 مع تثبيت آخر التحديثات ودعم المصفوفات الديناميكية ، يمكنك الضغط ببساطة أدخل.
- في جميع الإصدارات الأخرى ، بعد إدخال الصيغة ، سيتعين عليك الضغط على اختصار لوحة المفاتيح CTRL+تغير+أدخل، والتي ستضيف تلقائيًا أقواس معقوفة في شريط الصيغة.
الطريقة 2: عكس البحث باستخدام وظيفة LOOKUP الجديدة
لقد كتبت بالفعل مقالة طويلة مع مقطع فيديو حول ميزة جديدة أنظر إلى (زلوكوب)الذي ظهر في أحدث إصدارات Office ليحل محل VLOOKUP القديم (البحث). بمساعدة BROWSE ، تم حل مهمتنا بشكل أساسي تمامًا ، لأن. لهذه الوظيفة (على عكس VLOOKUP) ، يمكنك تحديد اتجاه البحث صراحة: من أعلى لأسفل أو من أسفل لأعلى - وسيطتها الأخيرة (-1) مسؤولة عن هذا:
الطريقة الثالثة. ابحث عن سلسلة ذات تاريخ آخر
إذا كان لدينا في البيانات المصدر عمود برقم تسلسلي أو تاريخ يلعب دورًا مشابهًا ، فسيتم تعديل المهمة - لا نحتاج إلى العثور على آخر سطر (أدنى) مع تطابق ، ولكن السطر الذي يحتوي على أحدث ( كحد أقصى) التاريخ.
لقد ناقشت بالفعل بالتفصيل كيفية القيام بذلك باستخدام الوظائف الكلاسيكية ، والآن دعونا نحاول استخدام قوة وظائف الصفيف الديناميكية الجديدة. لمزيد من الجمال والراحة ، نقوم أيضًا بتحويل الجدول الأصلي إلى طاولة "ذكية" باستخدام اختصار لوحة المفاتيح CTRL+T أو أوامر الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول).
بمساعدتهم ، يحل هذا "الزوجان القاتل" مشكلتنا بأمان شديد:
هنا:
- الوظيفة أولا منقي (منقي) يختار فقط تلك الصفوف من جدولنا حيث توجد في العمود التسجيل كعميل - الاسم الذي نحتاجه.
- ثم الوظيفة درجة (فرز) يفرز الصفوف المحددة حسب التاريخ بترتيب تنازلي ، بحيث تكون أحدث صفقة في الأعلى.
- المسمى الوظيفي INDEX (فهرس) يستخرج الصف الأول ، أي يُرجع آخر صفقة نحتاجها.
- وأخيرًا ، تزيل وظيفة التصفية الخارجية العمودين الأول والثالث الإضافيين من النتائج (رمز الطلب и التسجيل كعميل) ويترك فقط التاريخ والمبلغ. لهذا ، يتم استخدام مصفوفة من الثوابت. {0؛ 1؛ 0؛ 1}، تحديد الأعمدة التي نريد (1) أو التي لا نريد (0) عرضها.
الطريقة الرابعة: البحث عن آخر تطابق في Power Query
حسنًا ، من أجل الاكتمال ، دعنا نلقي نظرة على حل لمشكلة البحث العكسي باستخدام الوظيفة الإضافية Power Query. بمساعدتها ، يتم حل كل شيء بسرعة كبيرة وبشكل جميل.
1. دعنا نحول جدولنا الأصلي إلى جدول "ذكي" باستخدام اختصار لوحة المفاتيح CTRL+T أو أوامر الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول).
2. قم بتحميله في Power Query باستخدام الزر من الجدول / النطاق علامة التبويب البيانات (بيانات - من جدول / نطاق).
3. نقوم بفرز (من خلال القائمة المنسدلة للمرشح في الرأس) جدولنا بترتيب تنازلي للتاريخ ، بحيث تكون أحدث المعاملات في المقدمة.
4... في علامة التبويب تحول اختر فريق مجموعة من (تحويل - تجميع حسب) وقم بتعيين التجميع حسب العملاء ، وكوظيفة تجميعية ، حدد الخيار كل الخطوط (كل الصفوف). يمكنك تسمية العمود الجديد بأي شيء تريده - على سبيل المثال التفاصيل.
بعد التجميع ، سنحصل على قائمة بالأسماء الفريدة لعملائنا وفي العمود التفاصيل - جداول بكل حركات كل منها ، حيث يكون السطر الأول هو آخر معاملة ، وهو ما نحتاجه:
5. أضف عمودًا محسوبًا جديدًا باستخدام الزر عمود مخصص علامة التبويب إضافة عمود (إضافة عمود - إضافة عمود مخصص)وأدخل الصيغة التالية:
هنا التفاصيل - هذا هو العمود الذي نأخذ منه الجداول حسب العملاء ، و 0،XNUMX،XNUMX {} هو رقم الصف الذي نريد استخراجه (يبدأ ترقيم الصفوف في Power Query من الصفر). نحصل على عمود مع السجلات (القيد ) ، حيث يكون كل إدخال هو الصف الأول من كل جدول:
يبقى توسيع محتويات جميع السجلات باستخدام الزر ذي الأسهم المزدوجة في رأس العمود آخر صفقة اختيار الأعمدة المطلوبة:
... ثم احذف العمود الذي لم تعد هناك حاجة إليه التفاصيل عن طريق النقر بزر الماوس الأيمن على عنوانه - إزالة الأعمدة (إزالة الأعمدة).
بعد تحميل النتائج على الورقة من خلال الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ...) سنحصل على مثل هذا الجدول الجميل مع قائمة المعاملات الأخيرة ، كما أردنا:
عند تغيير بيانات المصدر ، يجب ألا تنسى تحديث النتائج بالنقر بزر الماوس الأيمن عليها - الأمر تحديث وحفظ (ينعش) أو اختصار لوحة المفاتيح CTRL+قديم+F5.
- تعد وظيفة LOOKUP تابعة لـ VLOOKUP
- كيفية استخدام وظائف الصفيف الديناميكية الجديدة SORT و FILTER و UNIC
- البحث عن آخر خلية غير فارغة في صف أو عمود باستخدام الدالة LOOKUP