فهرست
همه جستجوی کلاسیک و توابع جایگزینی نوع VPR (VLOOKUP), GPR (HLOOKUP), بیشتر در معرض (همخوانی داشتن) و امثال آنها یک ویژگی مهم دارند - آنها از ابتدا تا انتها جستجو می کنند، یعنی از چپ به راست یا از بالا به پایین در داده های منبع. به محض یافتن اولین تطابق، جستجو متوقف می شود و تنها اولین مورد از عنصر مورد نیاز ما پیدا می شود.
اگر لازم باشد نه اولین، بلکه آخرین مورد را پیدا کنیم، چه کنیم؟ مثلا آخرین تراکنش برای مشتری، آخرین پرداخت، آخرین سفارش و غیره؟
روش 1: یافتن آخرین ردیف با فرمول آرایه
اگر جدول اصلی ستونی با تاریخ یا شماره سریال یک ردیف (سفارش، پرداخت ...) نداشته باشد، در واقع وظیفه ما یافتن آخرین ردیفی است که شرایط داده شده را برآورده می کند. این را می توان با فرمول آرایه زیر انجام داد:
اینجا:
- عملکرد IF (اگر) تمام سلول های یک ستون را یک به یک بررسی می کند مشتری و شماره خط را اگر حاوی نام مورد نیاز ما باشد نمایش می دهد. شماره خط روی برگه توسط تابع به ما داده می شود LINE (ROW)، اما از آنجایی که ما به شماره ردیف در جدول نیاز داریم، علاوه بر این باید 1 را کم کنیم، زیرا یک هدر در جدول داریم.
- سپس تابع MAX (حداکثر) حداکثر مقدار را از مجموعه اعداد ردیف تشکیل شده، یعنی تعداد آخرین خط مشتری انتخاب می کند.
- عملکرد شاخص (فهرست مطالب) محتویات سلول را با آخرین عدد پیدا شده از هر ستون جدول مورد نیاز دیگر برمی گرداند (کد سفارش).
همه اینها باید به عنوان وارد شوند فرمول آرایه، یعنی:
- در آفیس 365 با آخرین به روز رسانی های نصب شده و پشتیبانی از آرایه های پویا، می توانید به سادگی فشار دهید وارد.
- در تمام نسخه های دیگر، پس از وارد کردن فرمول، باید میانبر صفحه کلید را فشار دهید کلیدهای Ctrl+تغییر+وارد، که به طور خودکار بریس های فرفری را در نوار فرمول به آن اضافه می کند.
روش 2: جستجوی معکوس با تابع جدید LOOKUP
من قبلاً یک مقاله طولانی با یک ویدیو در مورد یک ویژگی جدید نوشتم VIEW (XLOOKUP)، که در آخرین نسخه های آفیس برای جایگزینی VLOOKUP قدیمی ظاهر شد (VLOOKUP). با کمک BROWSE، کار ما کاملاً ابتدایی حل می شود، زیرا. برای این تابع (برخلاف VLOOKUP)، می توانید به صراحت جهت جستجو را تنظیم کنید: از بالا به پایین یا پایین به بالا - آخرین آرگومان آن (-1) مسئول این است:
روش 3. رشته ای را با آخرین تاریخ جستجو کنید
اگر در داده‌های مبدأ ستونی با شماره سریال یا تاریخی داشته باشیم که نقش مشابهی را ایفا می‌کند، آنگاه وظیفه اصلاح می‌شود - ما باید نه آخرین (پایین‌ترین) خط را که مطابقت دارد، بلکه خطی با آخرین ( حداکثر) تاریخ.
من قبلاً به تفصیل درباره نحوه انجام این کار با استفاده از توابع کلاسیک صحبت کرده ام و اکنون بیایید سعی کنیم از قدرت توابع جدید آرایه پویا استفاده کنیم. برای زیبایی و راحتی بیشتر، ما همچنین جدول اصلی را با استفاده از میانبر صفحه کلید به یک جدول "هوشمند" تبدیل می کنیم. کلیدهای Ctrl+T یا دستورات صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول).
با کمک آنها، این "زوج قاتل" مشکل ما را بسیار زیبا حل می کند:
اینجا:
- ابتدا عملکرد FILTER (فیلتر) فقط آن سطرها را از جدول ما در ستون انتخاب می کند مشتری - نامی که ما نیاز داریم
- سپس تابع GRADE (مرتب سازی) ردیف‌های انتخابی را بر اساس تاریخ به ترتیب نزولی مرتب می‌کند و آخرین معامله در بالا قرار دارد.
- عملکرد شاخص (فهرست مطالب) اولین ردیف را استخراج می کند، یعنی آخرین معامله ای را که نیاز داریم برمی گرداند.
- و در نهایت، تابع FILTER خارجی ستون های 1 و 3 اضافی را از نتایج حذف می کند (کد سفارش и مشتری) و فقط تاریخ و مبلغ را باقی می گذارد. برای این کار از آرایه ای از ثابت ها استفاده می شود. {0;1;0;1}، تعیین می کنیم که کدام ستون ها را می خواهیم (1) یا نمی خواهیم (0) نمایش داده شود.
روش 4: یافتن آخرین تطابق در 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