اکسلعمومی

نمایش نتایج تکراری در VLOOKUP با استفاده از چند تابع و ترفند در اکسل

نمایش نتایج تکراری در VLOOKUP با استفاده از چند تابع و ترفند در اکسل

همانطور که می دانید تابع VLOOKUP در اکسل همواره به اولین نتیجه در محدوده جیتجو که می رسد داده متناظر آن را نمایش می دهد و حالا اگر شما بخواهید تمام داده های متناظر با جستجو را داشته باشید چکار باید بکنید و به عبارتی می خواهید که نتایج تکراری در VLOOKUP نمایش داده شود ، به مثال زیر توجه کنید:

مثال نتایج تکراری در VLOOKUP

فرض می کنیم که تصویر زیر ره به عنوان محدوده ای از داده ها در اکسل در اختیار داریم و می خواهیم تعداد هر کالا را فراخوانی کنیم ، خب اگر داده های موجود در ستون A تکراری نداشتند که کار بسیار راحت بود و می توانستیم با VLOOKUP به راحتی تعداد هر کد مورد جستجو را نمایش دهیم ولی همانطور که در تصویر زیر می بینید بعضی از کدها تکرار هستند .

نتایج تکراری در VLOOKUPنتایج تکراری در VLOOKUP

محدودیتهای تابع vlookup

  • محدوده جستجو باید دارای دیتای غیر تکراری باشد ، در غیر اینصورت فقط اولین داده را در نظر می گیرد.
  • ستون مورد جستجو باید اولین ستون رنج انتخابی باشد ، یعنی اولین ستون سمت چپ یا اولین ستون سمت راست.

ذکر همین دو محدودیت کافیست تا بدانیم که برای مثال بالا نمی توانیم از VLOOKUP استفاده کنیم و باید با استفاده از ترفندها و فرمول دیگری در اکسل به نتیجه زیر برسیم تصویر زیر را ببنید:

محدودیتهای VLOOKUPمحدودیتهای VLOOKUP

نمایش تمام نتایج جستجو در اکسل و هایلایت کردن آنها

تصویر بالا را ببنید نه تنها همه نتایج جستجو شبیه VLOOKUP نمایش داده می شود بلکه داده های متناظر با رنگ سبز در محدوده جستجو متمایز می شوند، اگر شما هم میخواهید مانند تصویر بالا عمل کنید باید از توابع زیر استفاده نموده و مطابق آموزش پیش برویدک

تابع FIND

اگر مورد جستجوی شما در سلول وجود داشته باشد عدد مربوط به شماره کارکتر را نمایش می دهد .

تابع INDEX

شماره ردیف و شماره ستون را دریافت کرده و دیتای موجود در نقطه تلاقی دو عدد ردیف و ستون را باز می گرداند.

تابع ROW

شماره ردیف سلول جاری در اکسل را نمایش می دهد

تابع SMALL

کوچکترین داده در محدوده مشخص شده را نمایش می دهد که البته تفاوتهایی با تابع MIN دارد.

تابع ISNUMBER

اگر مقدار درون یک سلول یا نتیجه محاسباتی ، مقدار عددی بود نتیجه True و در غیر این صورت نتیجه False خواهد بود.

تابع IF

قبلا مفصل توضیح داده شده و از نظر مهمترین تابع همین تابع if اکسل می باشد.

قالب دهی شرطی Conditional Formatting

هر کدام از توابع بالا به تنهایی شاد کارهای ساده ای انجام دهند اما ترکیب آنها هم لذت بخش است و هم نتایج جالبی به همراه دارد که نمونه آن در تصویر بالا مشخص است .

منطق و سناریو ترکیب توابع بالا چیست و چه مراحلی برای نمایش نتایج جستجو باید انجام دهیم.

به طور خلاصه ما باید شماره ردیفهایی که با عبارت مورد جستجو که در این مثال در سلول F2 قرار دارند را مشخص نموده و با استفاده از تابع INDEX دیتای مورد نظر خود را استخراج کنیم .

بررسی کنیم که آیا F2 در A2 وجود دارد یا نه که با استفاده از تابع FIND مقدور است

حال اگر وجود داشت یعنی نتیجه عددی بود شماره ردیف جاری را نمایش دهد . ( ترکیب تابع ISNUMBER و تابع ROW )

حالا با تابع SMALL شماره ردیفهای به دست آمده را مرتب می کنیم .

در نهایت با استفاده از INDEX و شماره ردیفهای به دست آمده داده مورد نیاز خود را نمایش دهیم . تصویر زیر را ببینید

استفاده از ستونهای کمکی در اکسلاستفاده از ستونهای کمکی در اکسل

ضرورت استفاده از ستونهای کمکی در اکسل

بعضی جاها شما چاره ای ندارین به غیر از اینکه بخشی از محاسبات رو داخل ستونهای دیگه ای انجام بدین که این کار هم درصد خطا رو کم می کنه و هم کمک می کنه که موقع استفاده از توابع تو در تو دچار مشکل نشین ، البته می تونید با استفاده از آرایه ها در اکسل ستونهای کمکی رو به حداقل برسونید.

تصویر بالا در واقع همان تصویر قبلی است با این تفاوت که رنگ فونت ستونهای D و E به سفید تغییر داده شده بودند و دیده نمی شدند و این دو ستون کمکی در واقع وظیفه انجام عملیات توابع ذکر شده در بالا را دارند .

چگونه توابع بالا را در اکسل ترکیب کرده و به نتیجه برسیم؟

1- ابتدا دو ستون کمکی انتهای محدوده داده خود ایجاد کنید .

ستون کمکی و تابع FINDستون کمکی و تابع FIND

2- در ستون اول فرمول زیر را بنویسید.

=IF(ISNUMBER(FIND($G$2,A2)),ROW(),””)

بعد از استفاده از فرمول بالا نتیجه مانند تصویر زیر است :

ترکیب تابع FIND و تابع ISNUMBER و تابع ROW در اکسلترکیب تابع FIND و تابع ISNUMBER و تابع ROW در اکسل

نکته : فرمول بالا با فرض این است که عبارت مورد جستجو در G2 قرار دارد .

3 – در ستون دوم کمکی نتیاج به دست آمده در ستون اول را با استفاده از تابع SMALL مرتب سازی می کنیم .

=SMALL($D$2:$D$15,ROW()-1)

4- حالا در محدوده دلخواه خود که در این مثال G2 می باشد و با استفاده از تابع Index نتیج مورد نیاز را استخراج می کنیم .

=IFERROR(INDEX($A$1:$C$15,E2,2),””)

اگر از خواندن آموزش متنی و تصویری بالا در مورد نمایش نتایج تکراری در VLOOKUP با استفاده از چند تابع و ترفند در اکسل چیزی متوجه نشدید اصلا نگران نباشید ، با مشاهده ویدیو آموزشی که در ادامه قرار داده و همچنین دانلود فایل نمونه و کمی تمرین حتما آموزش بالا رو درک خواهید کرد 🙂


(1) تابع Vlookup اکسل آموزش و نکات
تابع Vlookup اکسل آموزش و نکات کاربردی گروه تخصصی اکسل پدیا


(2) تابع VLOOKUP در اکسل به همراه ویدئوی
تابع VLOOKUP در اکسل به همراه ویدئوی آموزشی آموزش اکسل - سافت پلاس


(3) تابع Vlookup در اکسل
جستجوی موارد تکراری و رفع مشکل تابع Vlookup در اکسل گروه تخصصی اکسل پدیا


(4) تابع Hlookup و Vlookup
آموزش کار با نرم افزار های آفیس و برنامه نویسی VBA معرفی و کار با تابع Hlookup و Vlookup در اکسل آموزش اکسل - سافت پلاس


(5) ترکیب توابع با تابع Vlookup و چندین
ترکیب توابع با تابع Vlookup و چندین مثال در اکسل گروه تخصصی اکسل پدیا


(6) پیدا کردن و حذف دادههای تکراری در اکسل
پیدا کردن و حذف دادههای تکراری در اکسل 7 روش - مرکز آموزش تخصصی مایکروسافت اکسل Excel


(7) استخراج مقادیر تکراری با تابع Vlookup
استخراج مقادیر تکراری با تابع Vlookup - تالانت ترکیب Vlookup و IFError


(8) تابع Vlookup اکسل با چند شرط مقدار و
تابع Vlookup اکسل با چند شرط مقدار و چسباندن چند مقدار - آموزش اکسل پیشرفته تحلیل داده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

1 × دو =

دکمه بازگشت به بالا