
مشکل تابع VLOOKUP در جستجو موارد تکراری
جستجوی داده از جمله جستجو موارد تکراری در اکسل از پرتکرارترین مسائل و یکی از کارکردهای اصلی این نرم افزار به شمار میره. در واقع جستجوی داده اساس گزارشگیری هست و هر بار که بخوایم از بین یک سری داده، داده هایی با مشخصات دلخواه رو فراخوانی کنیم، با مسئله جستجو سر و کار داریم. جستجو کردن داده ها در اکسل، حالت ها، شرایط و روش های بسیار متنوعی داره و اینکه کدوم روش بهتره، کاملا بستگی به شرایط مسئله و نوع کاربرد اون داره. گاهی اوقات باید حتما فرمول نویسی کنیم، گاهی کدنویسی VBA برای کاری که میخوایم بهتره، بعضی مواقع بهتره از ابزارهایی مثل پیوت تیبل و پاورکوئری استفاده کنیم. پس همونطور که مشخصه، روش ها و امکانات جستجو در اکسل بسیار متنوع هست. هر روش رو با مزایا و معایبش باید یاد بگیریم که بتونیم در زمان مناسب، روش بهینه رو برای حل مسئله پیدا کنیم. از بین مسائل مربوط به جستجو در اکسل، یکی از مهم ترین و پرچالش ترین مسائل، جستجوی داده های تکراری هست. چون همونطور که میدونیم توابع جستجو مثل Vlookup همیشه به اولین مورد که برسه، همون رو به عنوان خروجی به ما میده و در واقع داده های تکراری رو نمایش نمیده. در این مقاله میخوایم به حل این مسئله بپردازیم و با فرمول نویسی، داده های تکراری رو جستجو کنیم. برای اینکه بتونیم این مسئله رو حل کنیم باید با چند تابع مهم و منطق فرمول نویسی آرایه ای آشنا باشیم:
قبل از ادامه این آموزش، حتما مقالات بالا رو مطالعه کنید. حالا بریم سراغ شرح مسئله: در جدول شکل ۱ داده هایی داریم راجع به فروش محصولات مختلف در زمان های متفاوت و به خریداران مختلف. حالا میخواهیم نام هر محصول رو که انتخاب میکنیم لیست همه فروش های مربوط به این محصول رو ببینیم.
شکل ۱ – جستجوی داده تکراری – ساختار داده ها
گام اول: شناسایی مواردی که باید در گزارش بیایند
در گام اول، باید بتونیم جای محصولات مورد نظرمون رو پیدا کنیم. برای این کار از تابع If و بصورت آرایه ای استفاده میکنیم. توجه داشته باشید که فرمول نویسی آرایه ای با Ctrl+Shift+Enter ثبت میشه:
=IF(A2:A40=F2, ROW(A2:A40) ,””)
شکل ۲ – جستجوی داده تکراری – پیدا کردن مکان سلول های معادل شرط مورد نظر
خروجی این فرمول مکان (شماره ردیف) سلول هایی است که در اونها نوشته شده محصول ۱ (سلول F2). در واقع اگر شرط مورد نظر رو پیدا کنه، بجاش شماره ردیف اون سلول رو میذاره، و در غیر اینصورت خروجی خالی خواهد بود. حالا اگر این فرمول رو دیباگ کنیم، نتیجه بصورت زیر خواهد بود:
شکل ۳- جستجوی داده تکراری – نتیجه فرمول آرایه IF
در شکل ۳ مشاهده میکنیم که عدد ۲، ۱۳ و ۳۴ خروجی این فرمول هست. این اعداد نشان دهنده شماره ردیف سلول هایی است که نوشته شده محصول ۱ (شرط مورد نظر در سلول F2)
گام دوم: تخصیص شماره به ردیف های مورد نظر
حالا باید شماره ردیف های مشخص شده رو یکی یکی فراخوانی کنیم. وقتی میخوایم از بین یک مجموعه عدد، اعداد رو از کوچک به بزرگ مشخص کنیم، از تابع Small استفاده میکنیم. پس فرمول به شرح زیر تغییر خواهد کرد:
=SMALL(IF(A2:A40=F2,ROW(A2:A40),””),ROW(a1))
این فرمول میاد از بین مجموعه اعدادی که خروجی IF بود یعنی {۳۴,۱۳,۲}، اعداد رو یکی یکی از کوچک به بزرگ بهمون میده. در واقع وقتی این فرمول رو مینویسیم و درگ میکنیم، خروجی بصورت شکل ۴ خواهد بود:
شکل ۴ – جستجوی داده تکراری – فراخوانی اعداد بدست آمده
گام سوم: فراخوانی داده های تکراری
حالا که شماره ردیف این داده ها رو داریم و شماره ستون داده مورد نظر هم مشخص هست، کافیه با استفاده از تابع Index داده مورد نظر رو فراخوانی کنیم. مثلا میخوایم تاریخ های مربوط به محصول ۱ رو پیدا کنیم. برای این کار خروجی فرمول بالا رو میذاریم توی Index:
=INDEX($A$1:$C$40,SMALL(IF($A$2:$A$40=$F$2,ROW($A$2:$A$40),””),ROW(A1)),۲)
آرگومان اول، Array: کل دیتابیس مورد نظر ما هست که جستجو رو در اون انجام میدیم. آرگومان دوم، Row_num: خروجی تابع Small هست و شماره ردیف داده های مورد نظر ما رو نشون میده. آرگومان سوم، Column_num: شماره ستون داده مورد نظر، یعنی تاریخ رو در دیتابیس نمایش میده. پس با اینکار تاریخ های مربوط به محصول مورد نظر رو فراخوانی کردیم. حالا کافیه همون فرمول رو با Column_num شماره ۳ بنویسیم و اسم مشتری رو فراخوانی کنیم.
شکل ۵ – جستجوی داده تکراری – فراخوانی داده های تکراری
در نهایت برای اینکه فرمول رو برای ۱۰ ردیف درگ کنیم و با خطا مواجه نشیم، فرمول بالا رو با تابع IFERROR ترکیب میکنیم.
=IFERROR(INDEX($A$1:$C$40,SMALL(IF($A$2:$A$40=$F$2,ROW($A$2:$A$40),””),ROW(A1)),۲),“”)
نتیجه فرمول آرایه ای بالا بصورت زیر خواهد بود: یکبار برای اینکه بصورت کلی عملکرد این فرمول رو ببینیم و درک بهتری داشته باشیم، به ویدئو زیر دقت کنید. مرحله به مرحله فرمول رو دیباگ میکنیم و نتیجه مشخص میشه.
بصورت کلی، اول مکان (شماره ردیف) داده های معادل با شرط مورد نظر پیدا میشه و بعد با تابع small، یکی یکی فراخوانی میشه و در نهایت شماره ردیف بدست آمده و ستون مورد نظر در تابع Index داده مورد نظر رو فراخوانی میکنند.
در این مقاله روش جستجو موارد تکراری با استفاده از فرمول نویسی آرایه ای و بدون سلول کمکی تشریح شد. یک روش دیگه هم برای جستجوی موارد تکراری هست، برای کسایی که از ورژن ۲۰۲۱ اکسل استفاده میکنند، که استفاده از تابع فیلتر در اکسل هست. پیشنهاد میکنیم برای یاد گرفتن این روش مقاله تابع Filter در اکسل ۲۰۲۱ و آفیس ۳۶۵ را مطالعه کنید.
در ادامه، فایل نمونه رو دانلود کنید و سعی کنید فرمول رو دیباگ کرده و بعد از یادگیری، چندین بار خودتون روی داده های مختلف انجام بدید. فراموش نکنید فرمول نویسی آرایه ای پیشرفته ترین سطح فرمول نویسی در اکسل هست و حتما نکات مربوط به این نوع فرمول نویسی رو مطالعه کنید و موقع اجرا در نظر داشته باشید.
دانلود فایل این آموزش
برای دانلود فایل این آموزش روی دکمه زیر کلیک کنید:
خبلی عالی بود. درود بر شما
سلام من با گزینه find دفعه ی اول سرچ می کنم .اطلاعات رو برام پیدا می کنه برای دفعه دوم به دنبال چیز دیگری می گردم .ارور می دهد.
درود
گزینه find منظور، ابزار find هست یا تابع؟
چه اروری؟
با سلام فایل جهت دانلود به ایمیل ارسال نشد.
درود
فولدر اسپم رو چک بفرمایید
با سلام و درود
من این فرمول رو چند بار نوشتم و عالی عمل میکنه و ممنونم بخاطر آموزشش،فقط خواستم بدونم اگر ما دوتا دیتابیس جدا داشته باشیم که بخوایم موارد تکراری رو ازشون استخراج کنیم این فرمول به چه صورت باید نوشته بشه ؟
مثل وقتی که دوتا جدول داریم که توی اولی ورود کالا ثبت میشه و در جدول دوم فروش همون کالا.و ما میخوایم کاردکس موجودی کالایی رو در شیت دیگه ای ببینیم.
درود بر شما
باید با یک روش، دیتابیس ها رو یکی کنید
حالا یا با پاورکوئری
یا در گوگل شیت و با استفاده Vstack و …
سلام وقت بخیر
من این فرمول رو نوشتم ولی توی گام اول تابع Row خروجی درست نمیده که بخوام فرمول رو تا آخر پیش ببرم،یعنی بجای شمارش داده مورد نظر توی ستون مد نظر کل اون ستون رو شمارش میکنه.دیتابیس من داخل جدول هست و من فرمول رو هم در یک جدول دیگه نوشتم و هم توی رنج معمولی ولی خروجی نامیده.و هر بار با Ctrl+shift+enter ثبتش میکنم.
لطفاً راهنمایی کنید.ممنون
درود بر شما
Row کارش تولید عدد برای تعیین شماره ردیف مورد نظره
احتمالا دیتابیس شما با این موضوع همخوان ینداره
فرمول رو دیباگ کنید تا مفهوم row ر ومتوجه بشید که چه کار یانجام میده توی فرمول بعد میتونید اصلاح کنید
چون بدون دیدن دیتابیس و نحوه فرمول نویسی نمیشه نظر دیگه ای داد.
سلام این اموزش خوب بود ولی یک بار نوشتم و مثل vlookup عمل کرد و فقط سطر اول رو برای من مشخص کرد
خوشحال میشم راهنماییم کنید.(ورژن اکسل هم ۲۰۱۶ است)
درود
فرمول آرایه ای است و باید با ctrl shift enter در ورژن ۲۰۱۶ که فرمودید ثبت بشه
فایل نمونه رو دانلود کنید تا متوجه جزئیات بشید
سلام، خدا قوت
اگه بخواهیم در یک جدول مثلا ۸ ستون و ۴۰ ردیف، با شرط اینکه اگر مقادیر ردیف ۱ و ستون ۱،۲،۳ آن با هر ردیف در ستونهای ۴،۵،۶ برابر شد مقدار ستون ۷ از همین ردیف که برابری حاصل شده است را در ردیف ۱ ستون ۸ بنویسد، بهترین راهکار چی هست؟ ضمن اینکه تمام مقادیر ستون ۱ تا ۳ و ستون ۴ تا ۶ یکتا هستند.
درود
واقعیت اینه که متوجه نشدم!
اما بنا به شرایط نهایتا با فمرول نویس یارایه ای هر مدل جستجویی رو میتونید انجام بدید
سلام . وقت بخیر . بهترین راه برای جلوگیری از ورود داده های تکراری در یه محدوده خاص در زبان vba اکسل چیه؟ منظورم اینه که ما مثلا دو تا سلول رو کپی و پیست مکنیم. دفعه بعد سلول سوم هم اضافه میشه. اما اگه بخوایم سلول سوم رو کپی و پیست کنیم باز هم سلول های اول و دوم که قبلا کپی پیست شده بودن ، دوباره کپی پیست میشن. ممنون میشم راهنمایی بفرمایید
درود
اگر منظور از طریق کپی پیست هست میتونید clipboard رو فعال کنید که بتونید انتخاب کنید
اما اگر منظور جلوگیری از ورود دااده تکراری در وی بی است، میتونید با شرط های hf و حلقه چک کنید که تکرار نشده باشه
با سلام و تشکر از این آموزش.. اگر شروع این جدول از سلول دیگری غیر از A1 باشه مثلا A15 و یا سلولی در ستونهای دیگر… باز هم باید با تغییر محدوده جستجو به جواب برسه.. اما این اتفاق نمیافته و خطا میده…!! دلیل خاصی داره؟ با تشکر
درود بر شما
بله چون شماره ردیف رو به عنوان خروجی نمایش میده باید اختلاف رو کم کنید
مثلا اولین داده ای که پیدا میکنه، ردیف ۵۰ هست، د رحالیکه کل محدوده سرچ ۲۰ ردیفه. قاعدتا ردیف ۵۰ در محدوده ۲۰ ردیفه معن ینداره
پس باید یا اختلاف از همم کم بشه
یا محدوده index از ردیف ۱ حساب بشه
با سلام
در جستجوی ماتریسی چگونه باید عمل کنیم؟
در مطالب شما جستجو با سطری و یا ستونی به تنهایی هستند ولی وقتی هر دو با هم باشند چگونه می شود؟
سلام
در این حالت از ترکیب تابع Index باید استفاده کنید.
با سلام
فرض کنید در ستون اول ۱۰۰ سوال نوشتیم
در ستون های بعدی در سطر اول نام کلاس مثلا کلاس اول ، کلاس دوم ، کلاس سوم
در زیر هر کلاس هر کدام از سوالات را که می خواهیم به ترتیب دلخواه شماره و ستاره می گذاریم
می خواهیم وقتی یک کلاس را فراخوانی می کینم ، آن سوالات دلخواه برای آن کلاس مورد نظر به ترتیب زیر بیاید:
ستاره دارها اول و بدون شماره بیایند
شماره دار ها به ترتیب شماره ای که به انها دادیم همراه با همان شماره بیایند
با تشکر
درود بر شما
باید از ترکیب همین توابع جستجو در همین مقاله استفاده کنید
مطلب رو یاد بگیرید و با مسئله خودتون تطبیق بدید
روی چیدت دیتابیس خوب فکر کنید که بتونید از توابعی که بهش مسلطید استفاده کنید
سرکار خانم خاکزاد
باسلام
به استحضار میرساند فایل اکسلی دارم که از تعداد دو شیت تشکیل شده است که در شیت اول یک ستون کد ملی و یک ستون مبلغ که با فرمول VLOOKUP می خواهم اعدادی را در شیت دو م جایگزین کنم که در این شیت هم کد ملی و یک ستون خالی که بایستی مبالغ از شیت اول جایگزین شوند از آنجائیکه تعدادی از کدهای ملی تکراری می باشند این فرمول یک مبلغ را برای کدهای ملی که تکرار شده اند را درج می نماید
میخواستم راهنمایی بفرمائید
درود بر شما
همین مقاله که کامنت گذاشتید برای این موضوعه
مطالعه بفرمایید و مسئله رو حل کنید
سلام من اگه بخوام برای این فرمول دو تا if بزارم چطور میشه؟؟؟ یعنی دو شرطی کنم … با and هرکاری میکنم نمیشه
درود
بستگی به سوالتون داره
اعمال چند شرط دقیقا با همون and/or میشه
میتونید سوال رو واضح بگید تا بهتر راهنمایی بشید
سلام بسیار عالی دست شما درد نکند
ولی من اطلاعات تکراری را بجای اینکه زیر هم بیاورد میخواهم جلو آن باشد . بعلت اینکه رکورد تکراری من کد ملی هست و میخواهم تعداد دفعات و عنوان خواسته شده جلو کد ملی در یک شیت دیگری باشد
ممنون میشم کسی پاسخ مرا بدهد
درود
منطق فرمول نویسی رو یاد بگیرید، بعد در هر جهتی مایل بودید بنویسید و درگ کنید!
سلام و وقت بخیر
من جدولی دارم که دقیقا همین کاری که در این آموزش داده شده رو میخوام روی فایل انجام بدم. با این تفاوت که به جای سه ستون، دو ستون دارم.
گام به گام طبق آموزش دارم پیش میرم اما خروجی به من نمیده. حتی فایل آموزشی رو هم دانلود کردم و روی اون هم امتحان کردم. میشه راهنمایی بفرمایید که اشکال کار کجاست؟
درود
اگر دقیق اجرا شده باشه که مشکلی نباید باشه!
باید فرمولتون و ببینیم تا مشخص بشه
اما این نکته رو در نظر داشته باشید که این فرمول با Ctrl+shift+enter کار میکنه.