ما لیستی از سفارشات با شماره و نام کالاها را داریم. مثلاً می‌خواهم تمام کالاهایی که در آن گنجانده شده است را با شماره سفارش از جدول بیرون بکشم. کم و بیش مثل این:
ویژگی شگفت انگیز VLOOKUP (VLOOKUP) در چنین شرایطی فقط تا حدی کمک می کند، زیرا می تواند داده ها را تنها با اولین بازی پیدا شده استخراج کند، یعنی فقط به ما می دهد سیب ها. برای یافتن و استخراج همه موارد از جدول، بهتر است از فرمول آرایه استفاده کنید. شبیه این یکی:
=شاخص($B$2:$B$16;کمترین(IF(2 دلار اروپا=A2: A16;LINE(B2:B16)-1;»»);LINE()-5))
باید به صورت زیر وارد شود:
- سلول هایی را انتخاب کنید که در آن نتایج باید نمایش داده شوند (در مثال ما، این محدوده D6:D20 است)
- وارد کنید (فرمول را در سلول اول کپی کنید) محدوده
- فشار کلیدهای Ctrl + تغییر + وارد
تفریق واحد به صورت قطعه STRING(B2:B16)-1 به دلیل سربرگ جدول انجام می شود. به همین دلیل، برای جبران تغییر در محدوده حاصل نسبت به محدوده اصلی، عدد پنج در قطعه کم می شود. STRING()-5
برای پنهان کردن #NUM! خطایی که در سلول های خالی در محدوده حاصل D6:D20 ظاهر می شود، می توانید از توابع بررسی خطا IF و EOSH استفاده کنید و فرمول ما را با فرمول کمی پیچیده تر جایگزین کنید:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
در اکسل 2007، یک تابع راحت تر IFERROR ظاهر شد - به شما امکان می دهد مشکل را فشرده تر حل کنید:
=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
در نسخه انگلیسی اکسل، این توابع به شکل زیر خواهند بود:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- با استفاده از تابع VLOOKUP برای یافتن داده ها در یک جدول
- یک نسخه بهبود یافته از تابع VLOOKUP2 که می تواند در هر ستون و نه فقط مقدار اول جستجو کند
- عملکردهای VLOOKUP2 و VLOOKUP3 از افزونه PLEX
- فرمول های آرایه چیست و چه کاربردی دارند؟