همانطور که می دانید تابع VLOOKUP در اکسل همواره به اولین نتیجه در محدوده جیتجو که می رسد داده متناظر آن را نمایش می دهد و حالا اگر شما بخواهید تمام داده های متناظر با جستجو را داشته باشید چکار باید بکنید و به عبارتی می خواهید که نتایج تکراری در VLOOKUP نمایش داده شود ، به مثال زیر توجه کنید:
مثال نتایج تکراری در VLOOKUP
فرض می کنیم که تصویر زیر ره به عنوان محدوده ای از داده ها در اکسل در اختیار داریم و می خواهیم تعداد هر کالا را فراخوانی کنیم ، خب اگر داده های موجود در ستون A تکراری نداشتند که کار بسیار راحت بود و می توانستیم با VLOOKUP به راحتی تعداد هر کد مورد جستجو را نمایش دهیم ولی همانطور که در تصویر زیر می بینید بعضی از کدها تکرار هستند .
محدودیتهای تابع VLOOKUP
- محدوده جستجو باید دارای دیتای غیر تکراری باشد ، در غیر اینصورت فقط اولین داده را در نظر می گیرد.
- ستون مورد جستجو باید اولین ستون رنج انتخابی باشد ، یعنی اولین ستون سمت چپ یا اولین ستون سمت راست.
ذکر همین دو محدودیت کافیست تا بدانیم که برای مثال بالا نمی توانیم از VLOOKUP استفاده کنیم و باید با استفاده از ترفندها و فرمول دیگری در اکسل به نتیجه زیر برسیم تصویر زیر را ببنید:
نمایش تمام نتایج جستجو در اکسل و هایلایت کردن آنها
تصویر بالا را ببنید نه تنها همه نتایج جستجو شبیه VLOOKUP نمایش داده می شود بلکه داده های متناظر با رنگ سبز در محدوده جستجو متمایز می شوند، اگر شما هم میخواهید مانند تصویر بالا عمل کنید باید از توابع زیر استفاده نموده و مطابق آموزش پیش برویدک
تابع FIND
اگر مورد جستجوی شما در سلول وجود داشته باشد عدد مربوط به شماره کارکتر را نمایش می دهد .
تابع INDEX
شماره ردیف و شماره ستون را دریافت کرده و دیتای موجود در نقطه تلاقی دو عدد ردیف و ستون را باز می گرداند.
تابع ROW
شماره ردیف سلول جاری در اکسل را نمایش می دهد
کوچکترین داده در محدوده مشخص شده را نمایش می دهد که البته تفاوتهایی با تابع MIN دارد.
تابع ISNUMBER
اگر مقدار درون یک سلول یا نتیجه محاسباتی ، مقدار عددی بود نتیجه True و در غیر این صورت نتیجه False خواهد بود.
تابع IF
قبلا مفصل توضیح داده شده و از نظر آفیس باز مهمترین تابع همین تابع if اکسل می باشد.
قالب دهی شرطی Conditional Formatting
هر کدام از توابع بالا به تنهایی شاد کارهای ساده ای انجام دهند اما ترکیب آنها هم لذت بخش است و هم نتایج جالبی به همراه دارد که نمونه آن در تصویر بالا مشخص است .
منطق و سناریو ترکیب توابع بالا چیست و چه مراحلی برای نمایش نتایج جستجو باید انجام دهیم.
به طور خلاصه ما باید شماره ردیفهایی که با عبارت مورد جستجو که در این مثال در سلول F2 قرار دارند را مشخص نموده و با استفاده از تابع INDEX دیتای مورد نظر خود را استخراج کنیم .
بررسی کنیم که آیا F2 در A2 وجود دارد یا نه که با استفاده از تابع FIND مقدور است
حال اگر وجود داشت یعنی نتیجه عددی بود شماره ردیف جاری را نمایش دهد . ( ترکیب تابع ISNUMBER و تابع ROW )
حالا با تابع SMALL شماره ردیفهای به دست آمده را مرتب می کنیم .
در نهایت با استفاده از INDEX و شماره ردیفهای به دست آمده داده مورد نیاز خود را نمایش دهیم . تصویر زیر را ببینید
ضرورت استفاده از ستونهای کمکی در اکسل
بعضی جاها شما چاره ای ندارین به غیر از اینکه بخشی از محاسبات رو داخل ستونهای دیگه ای انجام بدین که این کار هم درصد خطا رو کم می کنه و هم کمک می کنه که موقع استفاده از توابع تو در تو دچار مشکل نشین ، البته می تونید با استفاده از آرایه ها در اکسل ستونهای کمکی رو به حداقل برسونید.
تصویر بالا در واقع همان تصویر قبلی است با این تفاوت که رنگ فونت ستونهای D و E به سفید تغییر داده شده بودند و دیده نمی شدند و این دو ستون کمکی در واقع وظیفه انجام عملیات توابع ذکر شده در بالا را دارند .
چگونه توابع بالا را در اکسل ترکیب کرده و به نتیجه برسیم؟
1- ابتدا دو ستون کمکی انتهای محدوده داده خود ایجاد کنید .
2- در ستون اول فرمول زیر را بنویسید.
=IF(ISNUMBER(FIND($G$2,A2)),ROW(),””)
بعد از استفاده از فرمول بالا نتیجه مانند تصویر زیر است :
نکته : فرمول بالا با فرض این است که عبارت مورد جستجو در G2 قرار دارد .
3 – در ستون دوم کمکی نتیاج به دست آمده در ستون اول را با استفاده از تابع SMALL مرتب سازی می کنیم .
=SMALL($D$2:$D$15,ROW()-1)
4- حالا در محدوده دلخواه خود که در این مثال G2 می باشد و با استفاده از تابع Index نتیج مورد نیاز را استخراج می کنیم .
=IFERROR(INDEX($A$1:$C$15,E2,2),””)
اگر از خواندن آموزش متنی و تصویری بالا در مورد نمایش نتایج تکراری در VLOOKUP با استفاده از چند تابع و ترفند در اکسل چیزی متوجه نشدید اصلا نگران نباشید ، با مشاهده ویدیو آموزشی که در ادامه قرار داده و همچنین دانلود فایل نمونه و کمی تمرین حتما آموزش بالا رو درک خواهید کرد 🙂
[sdfile url=”https://officebaz.ir/dl/OfficebazMultiLookup.zip”]
خیلی عالی و مسلط و ساده
بهترین از این نمیشه
سلام
ممنون از سایت خوب و آموزشهای عالی شما
آخر مطلب نوشتید فایل دانلودی ولی فایلی قرار داده نشده .
حق با شماست ، قرار گرفت 🙂
سلام
لینک ویدیوی آموزشی رو چطور میتونم بگیرم؟فقط فایل اکسل رو تونستم دریافت کنم
بسیار بسیار عالی خیلی متشکرم بی نظیر بود و کارآمد متشکرم از زحمتتون
سلام
خیلی ممنون بابت فایلتون
من به یک مشکل برخوردم
وقتی در مرحله ای که میخوام از فرمول small استفاده کنم برای مثال کد کالا ۹ هستش، فرمول تمام کد کالاهایی که داخلش ۹ وجود داره برام میاره مثل ۱۹، ۲۹، ۳۹، …
لطفا کمکم کنید
سپاس
خب شما باید از کدکالاهایی استفاده کنید که امکان اشتراکشون ک باشه مثلا حداقل ۳ رقم باشه
نمایش نتایج تکراری در VLOOKUP با استفاده از چند تابع و ترفند در اکسل
با عرض سلام من یک مشکلی با این فرمول برخوردم موارد خیلی مشابه را مثل ۱۶ و ۱۱۶ را بهم در نظر میگیرند
سلام،روزتون بخیر.
وقتی اعداد رو داخل اکسل وارد میکنم تا دابل کلیک نکنم تابع VLOOKUP اون رو نمیخونه و شرح نمیده
مشکل از چیه?
ممنون میشم راهنمایی کنید.
سلام
اگر بخواهیم سر جدید به جدول اصلی اضافه کنیم ، فرمول ها یک سطر پایین تر رو میخونن؟چرا؟؟؟؟؟؟
سلام
اگه مورد جستجوی ما متن باشد مثل همون کیف لپ تاپ تو ستون B
با همین فرمول میشه یا فرمول دیگه ای داره ؟
ممنون میشم جواب بدید.
با تشکر
سلام
من یه لیست از شماره ها دارم که تعدادی تکراری داخلشون هست
برای هر شماره یه اسم ذکر شده
میخوام شماره های تکراری به همراه اسم هاشون رو پیدا کنم – تا اگر شماره تکراری با اسم یکی نبود از روی فاکتور های کاغذی اسم درست رو پیدا کنم
این کار خیلی وقت من رو میگیره .. ممنون میشم راهنمایی کنید
سلام وقت بخیر با تشکر از راهنمایی هاتون. متاسفانه من توی مراحل کار به یه مشکل برخورد میکنم. اینکه یکی همه مقادیر بعضی از کدها رو که تکراری هست نشون نمیده مثلا اگه اون کد 4 تا داشته باشه فقط دوتاشونشون میدهو اینکه مقادیر من توی دوتا شیت جداگونه ست. باید چیکار کنم. خیلی لازمش دارم اگر امکانش باشه جواب بدین ممنون میشم
ضمنا رنگ سلولایی رو هم ک پیدا میکنه سبز رنگ نمیشه اینو باید از کجا تعیین کنم؟
سلام وقت بخیر
سایت خیلی خوبی است.
خدمت شما سوالی داشتم.
سه صنعت داریم. در صنعت اول 10 بنگاه، در صنعت دوم 8 بنگاه و در صنعت سوم 6 بنگاه فعالیت می کند. فروش هر یک از این بنگاه ها را در صنعت خاص خود داریم. این صنایع به صورت پشت سر هم قرار گرفتند و داده ها از بزرگ به کوچک سورت شدند. می خواهم جمع فروش دو بنگاه اول یا چهار بنگاه اول را برای هر صنعت بدست آورم. از چه دستور ویا دستوراتی با ید استفاده کنم و چگونه؟ البته تعداد بنگاه ها و صنایع بسیار زیاد است این مثال را برای چگونگی بدست آوردن نوشتم.
بنگاه کد صنعت فروش
1 11 123
2 11 120
3 11 118
4 11 115
5 11 114
6 11 113
7 11 122
8 11 111
9 11 110
10 11 109
1 12 200
2 12 190
3 12 189
4 12 170
5 12 165
6 12 164
7 12 150
8 12 140
1 15 160
2 15 155
3 15 154
4 15 130
5 15 116
6 15 115
با تشکر
دو روزه تا 2 شب رو این موضوع کار میکنم ، مطابق تمرین شما تونستم انجام بدم ، ولی نتونستم تغییرش بدم برای کاری که خودم میخوام ازش بهره ببرم و در فرمول خودم استفاده کنم :((
سلام
قطعا یه جای کار رو اشتباه انجام میدید
دوباره تلاش کنید
با سلام
ممنون از آموزش خوبتون بسیار عالی بود اما یه مشکل اساسی داره اونم اینکه رکوردهای مشابه رو پیدا میکنه نه دقیقا همون کد رو…..یعنی مثلا ما دنبال کد 520 میگردیم بزنیم 52 کدهای 520 و 526 و….رو هم بر میگردونه
سلام بله حق با شماست یه راهکاری براش میذارم
سلام
من هم همین مشکل را دارم