مشکل در برگرداندن مقدار یک ستون در صورت تکراری بودن نتیجه در ترکیب تابع index و match

[reza41]

سلام
من یک فایل اکسل دارم که مثلا در ستون اول نام دانش آموز نوشته شده و در ستون دوم نمره درس همون دانش آموز ثبت شده.
با استفاده از توابع large و small سه نفر اول و سه نفر آخر رو مشخص کردم.
برای اینکه اسم اونا رو نشون بده از ترکیب دو تابع index و match استفاده کردم.
تا اینجای کار اشکالی نیست.
اشکال از اونجا شروع میشه که مثلا سه نفر آخر هر سه تا یک نمره گرفته باشند مثلا هر سه تا 5 گرفتن. خوب وقتی از ترکیب دو تابع index و match استفاده می کنیم در هر سه تا اسم اولین نفری که اون نمره رو گرفته نشون میده.
برای حل این مشکل چه کار باید کرد؟
نمونه فایل هم پیوست کردم.
test.xlsx

اخطار: این یک موضوع قدیمی است به دلیل قدیمی بودن موضوع، ممکن است برخی فایل های ضمیمه به درستی کار نکنند. لطفا در صورت عدم ضرورت، از بالا آوردن موضوعات قدیمی خودداری نمایید.

[iranweld]

با سلام

در فایل پیوست با دو ستون و از فرمول Rank برای رتبه بندی استفاده گردید و با دستور lookup سایر دیتا فراخوانی گردید

[abootorab]

سلام
من یک فایل اکسل دارم که مثلا در ستون اول نام دانش آموز نوشته شده و در ستون دوم نمره درس همون دانش آموز ثبت شده.
با استفاده از توابع large و small سه نفر اول و سه نفر آخر رو مشخص کردم.
برای اینکه اسم اونا رو نشون بده از ترکیب دو تابع index و match استفاده کردم.
تا اینجای کار اشکالی نیست.
اشکال از اونجا شروع میشه که مثلا سه نفر آخر هر سه تا یک نمره گرفته باشند مثلا هر سه تا 5 گرفتن. خوب وقتی از ترکیب دو تابع index و match استفاده می کنیم در هر سه تا اسم اولین نفری که اون نمره رو گرفته نشون میده.
برای حل این مشکل چه کار باید کرد؟
نمونه فایل هم پیوست کردم.
test.xlsx

با درود و تشکر از جناب iranweld
با استفاده از vb واستون تابعی به نام IndexMatch تهیه کردم که از این تابع در محیط اکسل نیز میتونید استفاده کنید.

شکل کلی تابع IndexMatch:

IndexMatch(match_value;match_array;lookup_array;[sev_index];[pos]) 

match_value (اجباری): مقداری که مورد جستجو قرار میگیرد.

match_array (اجباری): محدوده ای که مقدار مورد نظر در آن جستجو میشود.

lookup_array (اجباری): محدوده متناظر که متناظر مقدار جستجو شده را برای ما برمیگرداند.

sev_index (اختیاری): زمانیکه مقدار مورد جستجو بیشتر از یک مورد باشد بکار می آید و ما با دادن این مقدار تعیین میکنیم چندمین مقدار را برگرداند. (در صورتیکه این مقدار را وارد نکنیم بصورت پیشفرض اولین جستجوی یافت شده را برمیگرداند.)

pos (اختیاری): با ورود این آرگومان تعیین میکنیم عملیات جستجو از ابتدای محدوده شروع شود و یا از انتها. مقدار 0 برای شروع جستجو از ابتدای محدوده و مقدار 1 جهت شروع جستجو از انتهای محدوده. (در صورتیکه این مقدار را وارد نکنیم بصورت پیشفرض جستجو از ابتدای محدوده شروع میکند)

توضیح: در صورتیکه هردو sev_index و pos را وارد نکنیم عملکرد این تابع دقیقا عملکرد ترکیب توابع MACTH و INDEX خود اکسل را دارد.

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

[reza41]

سلام
با تشکر از شما دوست عزیز و بزرگوار
سوالی که پیش میاد اینه که از این تابع میشه در سلول هایی استفاده کرد که از قبل میدونیم مقدار تکراری وجود داره.
یعنی نمیشه مثلا در چند سلول پشت سر هم این تابع رو وارد کرد که به صورتی که اگه مقدار مورد جستجو تکراری بود که اول و دوم و ... رو ثبت کنه و اگه تکراری نبود عدد متناظر رو ثبت کنه
در همین نمونه ای که شما برام فرستادین اگه نمره حمید که در حقیقت سومین نفری هست که 5 گرفته تبدیل بشه به 7 که باز هم در حقیقت دو نفر به آخر هست، تابعی که شما نوشتین خطا میده و در این جا باید از همون ترکیب index و match استفاده کرد. یعنی ما از قبل باید بدونیم کیا تکراری هستن تا براشون از تابع شما استفاده کنیم و کدوما تکراری نیستن تا از تابع خود اکسل استفاده کنیم.
برای این مشکل باید چه کار کرد؟

[abootorab]

سلام
با تشکر از شما دوست عزیز و بزرگوار
سوالی که پیش میاد اینه که از این تابع میشه در سلول هایی استفاده کرد که از قبل میدونیم مقدار تکراری وجود داره.
یعنی نمیشه مثلا در چند سلول پشت سر هم این تابع رو وارد کرد که به صورتی که اگه مقدار مورد جستجو تکراری بود که اول و دوم و ... رو ثبت کنه و اگه تکراری نبود عدد متناظر رو ثبت کنه
در همین نمونه ای که شما برام فرستادین اگه نمره حمید که در حقیقت سومین نفری هست که 5 گرفته تبدیل بشه به 7 که باز هم در حقیقت دو نفر به آخر هست، تابعی که شما نوشتین خطا میده و در این جا باید از همون ترکیب index و match استفاده کرد. یعنی ما از قبل باید بدونیم کیا تکراری هستن تا براشون از تابع شما استفاده کنیم و کدوما تکراری نیستن تا از تابع خود اکسل استفاده کنیم.
برای این مشکل باید چه کار کرد؟

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

شیت2 فایل ضمیمه شده رو ببینید. اگه خواسته شما یر از این بود و یا مشکلی داشتین در خدمتم.

[reza41]

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

[abootorab]

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

با درود مجدد
دوست عزیز مثل اینکه شما فایل رو درست بررسی نکردین!!!
در نمونه جدولی که در شیت2 براتون تهیه کردم عمل سورت بصورت خودکار انجام میشه، فقط کافیه اسامی و اعداد رو وارد کنید و همینطور که میدونید چون از Table استفاده کردم به هر تعداد اسامی و نمره در سطرهای بعدی اضافه کنید عمل سورت و سایر بصورت خودکار انجام میشه و نیازی به کار اضافه ندارین.

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

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

در هرصورت بنده بر اساس خواسته فعلیتون فایل رو تهیه کردم. اگر خواسته هاتون رو بصورت کامل بیان کنید حتما سریعتر و بهتر به جواب میرسین.
لطف کنید دوباره فایل پست قبل رو بررسی کنید!