چند کاربرد از تابع Index
همونطور که قبلا توضیح دادیم، تابع Index در ترکیب با سایر توابع، یکی از توابع بسیار بسیار قوی توی اکسل هست که اگه به همه زوایا و توانایی های این تابع آشنایی داشته باشیم، قدرت ما رو در فرمول نویسی خیلی افزایش میده. در این مقاله میخوام ۶ کاربرد فوق العاده از تابع Index رو آموزش بدم:
قبل از مطالعه این ۶ کاربرد، نحوه کار با تابع Index و آشنایی با آرگومان های این تابع رو در مقاله ” Index تابع قدرتمند اکسل” رو مطالعه کنید.
کاربرد تابع Index: فراخوانی N امین سلول در یک محدوده
اولین و ساده ترین حالت کاربرد تابع Index این هست که برای فراخوانی Nامین داده از یک محدوده استفاده میشه. فرض کنید یک کمبوباکس داریم مطابق شکل ۱. همونطور که میدونیم خروجی لیست فروریز (کمبوباکس) یک عدد هست که در واقع میگه چندمین داده از لیست نتخاب شده. حالا برای اینکه با هر انتخاب، عنوان آیتم انتخاب شده رو فراخوانی کنیم (یعنی با انتخاب تابستان از لیست، بجای عدد ۲، مقدار تابستان رو در یک سلول داشته باشیم)، میتونیم از تابع Index استفاده کنیم.
=Index (A2:A5 ; A1 )
شکل ۱- کاربرد تابع Index -فراخوانی Nامین داده از یک لیست
کاربرد تابع Index: فراخوانی همه داده ها در یک ردیف یا ستون
تابع Index علاوه بر اینکه یک سلول حاصل تقاطع یک شماره ردیف و ستون رو بر میگردونه، میتونه عملیاتی رو روی یک ردیف یا یک ستون مشخص هم انجام بده. به شکل ۲ توجه کنید. وقتی آرگومان Row_Num رو خالی یا ۰ میذاریم، چون ردیفی رو مشخص نمیکنیم، پس همه ردیف ها (یعنی یک ستون) در نظر گرفته میشه. در واقع داریم به فرمول میگیم که از محدوده ۶B1:C1 کل ستون اول رو در نظر بگیر و میانگین روی این محدوده حساب کن.
شکل ۲- کاربرد تابع Index -تعیین محدوده با استفاده از تابع Index
در مورد آرگومان Column_Num هم همین اصل برقرار هست. یعنی اگر این آرگومان رو ۰ یا خالی بذاریم، کل ردیف تعیین شده در نظر گرفته میشه.
در واقع بجای اینکه مستقیم بنویسیم Average(B2:B16) این محدوده رو با تابع Index تعیین کردیم. این نوع تعیین محدوده به دلیل انعطاف پذیری بالا، در ترکیب فرمول ها و تهیه نرم افزارها کاربرد زیادی دارن.
چون خروجی این حالت تابع Index یک محدوده هست، یا حتما باید عملیات دیگه ای روش انجام بشه یا بشه قسمتی از یک فرمول دیگه و خودش به تنهایی قابل استفاده نیست.
کاربرد تابع Index: ترکیب Index با سایر توابع
با توجه به مثال های قبلی، برداشتمون اینه که تابع Index محتوای سلول رو به ما میده. اما واقعیت اینه که این تابع به یک Reference اشاره میکنه که داخلش مقدار وجود داره. در این مثال ماهیت واقعی تابع Index رو میبینیم و با توجه به Reference بودن خروجی تابع Index، میتونیم ازش در تعیین محدوده های پویا استفاده کنیم.
مثال اول: فرض کنید میخواهیم میانگین یک محدوده رو مطابق Average(A1:A10) حساب کنیم. این محدوده رو میتونیم به این شکل هم تعیین کنیم:
=Average ( A1 : Index (A1:A10,10) )
در ادامه به ارائه کاربرد این روش استفاده از Index می پردازیم:
فرض کنید میخواهیم میانگین وزن پنج نفر اول رو حساب کنیم. برای این کار اول ستون وزن رو از بزرگ به کوچک Sort میکنیم. سپس فرمول زیر رو مینویسیم. (مطابق شکل ۳)
=Average ( C2 : Index (C2:C16 ; E1) )
شکل ۳- کاربرد تابع Index -ایجاد محدوده انعطاف پذیر با تابع Index
مثال دوم: فرض کنید میخوایم یک محدوده مشخص از بین داده ها رو انتخاب کنیم و جمع محاسبه کنیم. برای این مسئله هم از ترفند بالا استفاده میکنیم و محدوده مورد نظر رو میسازیم. در واقع ما با استفاده از تابع Index ابتدا و انتهای بازه رو میسازیم. سپس با استفاده از : بازه رو تشکیل میدیم.
=Sum ( Index (B2:B13;E2) : Index (B2:B13;E3) )
شکل ۴- کاربرد تابع Index -ایجاد محدوده انعطاف پذیر با تابع Index
همونطور که در تصویر زیر میبینید، تابع Index ابتدا و انتهای بازه رو میسازه و در نهایت فرمول به Sum(B3:B9) تبدیل میشه
کاربرد تابع: محدوده پویا و داینامیک با Index
ایجاد محدوده های داینامیک و پویا از سری مباحث بسیار پر اهمیت در فرمول نویسی حرفه ای، داشبورد، ایجاد نمودارهای داینامیک و … هست. یکی از راه های ایجاد محدوده پویا تابع Offset هست که در مقاله نمودارهای پویا به آن پرداختیم. در این مقاله میخوایم با استفاده از تابع Index این کار رو انجام بدیم. فرض کنید لیستی از محصولات داریم که هر بار مواردی حذف/اضافه میشه. میخوایم لیستی داشته باشیم از نام محصولات که مطابق با تغییرات، محتوای اون لیست هم تغییر کنه. برای این کار مطابق شکل ۵ یک Name ایجاد میکنیم و فرمول زیر رو به اون نام اختصاص میدیم.
=A2 : Index ( A:A ; Counta (A:A) )
شکل ۵- کاربرد تابع Index -فرمول نویسی در Name Manger
سپس نام مورد نظر رو در Data Validation قسمت List وارد میکنیم (شکل ۶).
شکل ۶- کاربرد تابع Index -تخصیص نام مورد نظر در دیتاولیدیشن
در نهایت لیستی داریم مطابق شکل ۷ که با کم و زیاد شدن آیتم ها، موارد داخل لیست هم آپدیت میشه.
شکل ۷- کاربرد تابع Index -ایجاد لیست داینامیک و پویا
۵- جستجو (Vlookup) با ترکیب Index و Match
استفاده از ترکیب توابع Index و Match بجای Vlookup میتونه مزیت های زیر رو داشته باشه:
- مسئله رو به جلو بودن جستجو مطرح نیست و میشه جستجوی عقبگرد انجام داد. (در خصوص Backward Vlookup این مقاله رو مطالعه کنید)
- محدودیت کاراکتر برای سرچ وجود نداره
- نیازی به مرتب بودن داده ها نیست. (در Vlookup با جستجوی تخمینی، داده ها باید مرتب باشن)
- حذف و اضافه ردیف و ستون به مراتب راحت تره
- این ترکیب به مراتب سریع تر از Vlookup چندگانه است و موجب کند شدن فایل نمیشه.
مثلا فرض کنید میخوایم ببینیم فروش ماه اردیبهشت چقدر بوده است؟ بجای Vlookup از ترکیب Index و Match استفاده میکنیم.
=INDEX ( B2:B13 ; MATCH(D2;A2:A13;0) )
در واقع تابع Match برای ما تعیین میکنه که ماه اردیبهشت چندمین سلول در محدوده B2:B13 هست (در این مثال میشه ۲) و خروجی آن در آرگومان Row_num تابع Index قرار میگیره. یعنی :
=INDEX ( B2:B13 ; ۲ )
شکل ۸- کاربرد تابع Index -جستجو با استفاده از Index و Match
۶- فراخوانی یک محدوده از لیستی از محدوده ها
همونطور که تا الان متوجه شدید، تابع Index دو حالت داره: حالت Array و حالت .Referenceتا الان با حالت Array کار کردیم و آشنا شدیم. در ادامه یک مثال برای حالت Reference ارائه میدم:
تابع Index در حالت Reference میتونه محدوده های مختلفی رو بگیره و جستجو رو انجام بده. مثلا دو محدوده داریم و هر بار میخوایم بین یکی از اینها داده مورد نظر رو پیدا کنیم. به شکل ۹ دقت کنید:
=Index ( (A1:B5;D1:E5;G1:H5;J1:K5) ; ۳ ; ۲ ; ۲ )
شکل ۹- انتخاب محدوده جستجو با تابع Index (Reference)
در آرگومان اول محدوده های مختلف رو انتخاب میکنیم و با جداکننده فرمول از هم جدا میکنیم. آرگومان دوم و سوم که همون شماره ردیف و ستون مورد نظر هست.
رگومان آخر هم مشخص میکنه که از بین محدوده های انتخاب شده، جستجو بین چندمین محدوده انجام بشه.
در این فرمول می بینید که آرگومان Area_Num ، ۲ تعیین شده که در .اقع به این معنی هست که دومین محدوده در نظر گرفته بشه.پس خروجی این تابع، سلول حاصل تقاطع ردیف ۳ و ستون ۲ در محدوده دوم یعنی D1:E5 خواهد بود.
محدوده های انتخابی ممکنه هم اندازه نباشن، پس شماره ستون و ردیفی که تعیین میشه باید برای محدوده مورد نظر معنی داشته باشه تا با خطای #Ref مواجه نشیم.
حالا اگه بخوایم طوری فرمول نویسی کنیم با انتخاب فصل مورد نظر، خودش شماره محدوده رو تعیین کنه به روش زیر عمل میکنیم:
برای این کار یک لیست (Data Validation) درست میکنیم در سلول A9 که شامل عنوان چهار فصل هست، سپس فرمول زیر رو ثبت میکنیم:
=INDEX( (A1:B5;D1:E5;G1:H5;J1:K5) ; ۳ ; ۲ ; MATCH (A9 ; {“بهار”;”تابستان”;”پاییز”;”زمستان”} ;۰))
در واقع با استفاده از تابع Match شماره فصل رو استخراج میکنیم که بتونیم در آرگومان اخر تابع Index در حالت Reference ازش استفاده کنیم. با تغییر نام فصل خروجی تابع Match و در نهایت خروجی تابع Index تغییر میکنه.
در مثالهای بالا، استفاده از تابع Match میتونه خیلی کاربردی باشه. فکر میکنید در کدوم مثال و چطور میشه از این تابع در جهت بهبود فرمول های ارائه شده استفاده کرد؟ همچنین توابعی که میتونه جایگزین تابع Match بشه چی هست؟
پاسخ های خودتون رو در قالب کامنت و در ادامه همین پست ثبت کنید.
با مطالعه این مقاله مشاهده کردید که تابع Index چه توانایی های فوق العاده ای داره، مخصوصا زمانی که با توابع دیگه ترکیب میشه. پس سعی کنید خیلی خوب به این مبحث مسلط بشید. برای اطلاعات بیشتر میتونید مقاله ویژگی بسیار جالب تابع Index رو بخونید.
دانلود فایل کاربرد تابع Index در اکسل
فایل اکسل نمونه هم در انتهای آموزش قرار داده شده که بتونید تمرین کنید.
خدا قوت،خیلی عالی بود.
عالیه اموزشاتون ….و ممنون که بی چشم داشت علمتونو در اختیار بقیه قرار میدید
من میخوام یک متغیر از داده ها بسازم که نشان دهد محصول i ام از محل تولید j ام به مشتری kام میرسه این متغیر چه جوری ایجاد کنم
درود
بستگی به ساختار داده داره
ولی خب احتمالا تابع match و ترکیب با index جواب کارتون رو میده
این مقاله رو ببینید
https://excelpedia.net/index-function/
سلام فعالیت عالی هست
سلام و خسته نباشید
من ی راهنمایی میخواستم ازتون
من تو ی شیت از اکسل چند نوع هزینه وارد کردم جلو هر کدوم مبلغ
میخوام بدونم از چه تابعی باید استفاده کنم تا
تو ی شیت یا حتی اکسل دیگه
کله هزینه هامو به جز بگه مثلا هزینه آب و جدا بزنه هزینه برق و حجدا بزنه
وبقیه هزینه رو توی متفرقه بزرنه
ممنمون میشم راهنمایی بکنین مرسی
سلام
برای انجام این کار بهتره از Pivot Table استفاده کنید.
تشکر با فرمول زیر مشکلم حل شد
درود
این فرمول خیلی خوبه – در واقع داره میگه که آخرین مقدار تکراری در پایین ترین سطر از آن ستون را به من خروجی بده – :)
سلام وقت بخیر
یک شیت دارم که اطلاعات نفرات رو مینویسن و برای من میفرستن. اطلاعات نفرات فقط یک ستون ثابت داره که شماره هر فرد هست اما بقیه اطلاعات مدام تغییر میکنه. پیدا کردن نفر و تغییر اطلاعاتش خیلی زمان بر هست. می خواستم یک شیت دیگه درست کنم و هر زمان اطلاعات رو برام میفرستن همه رو داخل شیت جدید پشت سر هم پیست کنم و خود برنامه شماره نفر رو که آخرین تتغییرات هست رو پیدا کنه و تو شیت اطلاعات وارد کنه. متاسفانه با ایندکس مچ و لوک آپ این کار نمیشه چو تو یه ستون شماره تکراری دارم و آخرین شماره رو نمی تونه برام پیداکنه. ممنون میشم اگر راهنمایی کنید.
درود بر شما
برا یاینکه آخرین داده تکراری رو پیدا کنید باید از فرمول نویسی آرایه ای و ترکیب IF, small/large, Index و …. استفاده کنید
ممنون از جوابتون.
دو تا جدول دارم اولی اصلی دومی برای ورود اطلاعات تکراری. در جدول دومی شماره ردیف اضاف کردم تا برای هر نفر که شماره خاص داره بتونم موارد زیادی اطلاعات وارد کنم. و در جدول اول از فرمولی که فرمودید استفاده کردم تا آخرین ردیف رو به عنوان اطلاعات اون شخص برام بیاره اما درست کار نکرد.
امکانش هست بیشتر راهنمایی کنید؟
درود بر شما
سوال خیلی مبهمه
سلام وقت بخیر
ممنون از شما بابت در اختیار گذاشتن اطلاعاتتون
من یه فایل اکسل برای کارای برادرم ساختم که ورود و خروج ماشینها به کارش رو در اون مشخص میکنه
حالا میخوام هر راننده هم به نوبه خودش یه صفحه داشته باشه که شامل همون اطلاعاتی باشه که به صورت کامل در صفحه اول مشخص کردم
نمیدونم چطور این کارو کنم
میشه لطف کنید منو راهنمایی کنید.
پیش پیش ممنونم❤
سلام، وقت شما هم بخیر
بهترین ابزار برای شما Pivot Table هست.
گزارش های بسیار بیشتری هم میتونید با استفاده از این ابزار ایجاد کنید.
ضمن نشکر از پاسختون
تا جاییکه من میدونم تابع vlookup میتونه در جدول من فقط یک کد رو جستجو کنه ، شما فرض کنید من یک جدول دارم که اسم دونفر در ستون اول چند بار آمده مثلا ۵ بار رامین و ۵ بار رضا ، و جلوی هر اسم هم ساعت ورود و خروج همراه تاریخ ثبت شده ؛ حالا من میخوام تابعی بدم که برو بگرد و برای من فقط نام تمام رامین ها رو بطور مجزا بیار بعد ساعت ورود و خروج و تاریخ جلوی هر رامین رو هم قرار بده .
درود بر شما
یا باید از ستون کمکی استفاده کنید و شماره بزنید موارد یونیک رو. بعد شماره ها رو جستجو کنید.
یا از فرمو لنویسی آرایه ای استفاده کنید:(با فرض اینکه اسامی در ستون A هستند:
فرمول نویسی آرایه ای:
https://excelpedia.net/array-formula/
با سلام و تشکر
شما فرض کنید ما دو ستون کنار هم داریم ، در ستون اول در هر سطر یک کد داریم که هر کد بارها تکرار شده و در ستون دوم جلوی هر کد یک سری توضیحات داریم.
حالا فرض کنید ما میخواهیم فرمانی در مثلا ستون جدیدی بدهیم با این مضمون : برو در ستون اول بگرد هر جا مثلا کد ۱ بود پیدا کن بیاور با توضیحات مقابلش .
امیدوارم توانسته باشم منظورم را بیان کنم لطفا روش آن را بگید تشکر فراوان
درود بر شما
اگر درست متوجه شده باشم
تابع vlookup پاسخ این نیاز رو میده
https://excelpedia.net/vlookup-function/
با عرض سلام
ضمن تشکر از صرف وقتتان و راهنمایی های درجه۱ و کاربردی در اکسل ؛ لطفا راهنمایی فرمائید در جدول مشخصات افراد به چه صورت می توان تاریخ های تولد طی ۳۰روز آینده را براساس تاریخ روز سیستم مشخص و رنگی نمود . البته بدون فبلتر
تشکر
سلام و تشکر بابت نظر لطفتون
این مطلب به نوعی در آموزش ایجاد آلارم در اکسل آموزش داده شده. حتما مطالعه بفرمائید.
با سلام و تشکر از وب سایت عالی شما
میخواستم ببینم اگه ما یک لیست داشته باشیم از کالاهای مختلف و متنوع که در حال اضافه شدن کالاهای جدید می باشد و در حال تغییر باشه چه طور میتونیم یک لیست کلی از آخرین کالاهای موجود و تعداد اونها رو داشته باشیم که خودش به روز هم میشده باشه؟
درود بر شما
هم میتونید کد VBA بنویسید که اطلاعات یونیک رو براتون فراخوانی کنه هربار (برای این کار میتونید ماکرو ضبط کنید)
https://excelpedia.net/excel-macro/
هم میتونید فرمول آرایه ای بنویسید که بصورت خودکار لیست غیرتکراری از داده ها تهیه کنه و با استفاده از محدوده های نامگذاری شده و محدوده های پویا، این کار و انجام بدید
محدود ههای پویا رو در لینک زیر مشاهده میکنید:
https://excelpedia.net/dynamic-chart/
با عرض سلام و خدا قوت خدمت سروران عزیز
سرکار خانم مهندس حسنا خاکزاد و آقای مهندس سامان چراغی:
در فایل Excelpedia- index Tricks.xlsx که من دانلود کردم در شیت ۴-IndexMatch فرمول به نتیجه نرسیده است علت اون بررسی کردم ولی متاسفانه به نتیجه ای نرسیدم
ولی از همان شیت یه کپی گرفتم و در داخل شیت ۳-Index-bound پیست کردم و جالبه که بدونید به نتیجه رسیدم .
حال این سؤال برای من پیش آمده چرا در شیت ۴-IndexMatch فرمول به نتیجه نرسیده است ولی درشیت ۳-Index-bound فرمول نتیجه داد.
ضمناً فایل مربوطه با یه سری تغییرات در تلگرام ارسال کردم.
درود بر شما
اون شیت در حالت show formula بوده و خود فرمول رو بجای نتیجه فرمول نشون میده.
از تب Formula گزینه Show Formula، رو بزنید و نتیجه رو ببینید
سلام.من یه راهنمایی می خواستم.من تو اکسل با استفاده از ,Vlook و یا Match ,Index یه داده را فراخوانی می کنم.چطوری میتونم چند تا جواب داشته باشم؟ خیلی جستجو کردم.همیشه اولین جوابو بهم میده این فرمول ها.من شاید چند تا جواب بخوام.
درود بر شما
باید با ترکیب index, if, small,, row فرمول آرایه ای بنویسید و همه موارد مربوطه رو فراخوانی کنید. مثلا:
فرمول نویسی آرایه ای و منطقش رو از لینک زیر بخونید
https://excelpedia.net/array-formula/
سلام.امتحان کردم.باز هم نشد.فرض کنید یک لیست دارم “نام” و یک لیست “نام خانوادگی”. در لیست نام دو تا اسم تکراری “میلاد” هستش. حالا می خوام “میلاد” رو فراخوانی کنم و می خوام هر دو میلاد ها با دو “نام خانوادگی” متفاوت بیاد در صورتی که فقط “نام خانوادگی” میلاد اول میاد.ممنون میشم کمکم کنید.
همون فرمولی که دادم فرمول جستجوی موارد تکراری هست
آرایه ای بزنید
حتما اون مقاله رو بخونید که بتونید فرمول آرایه ای ثبت کنید
یکبار فرمول رو کرک کنید و مرحله به مرحله ببینید تا خوب درک کنید و بتونید روی داده هاتون اعمال کنید
اگه نشد وارد گروه تلگرامی پرسش و پاسخ بشید و اونجا مطرح کنید
تابع Small روی عدد کار میکنه و من برای حروف میخوام اینکارو انجام بدم.مانند مثال قبلی که تو پیام قبلی زدم.
خیر دو ست عزیز
ربطی به متن و عدد بودن مقدار جستجو نداره
تابع small داره روی خروجی تابع if که همون شماره ردیف مقادیر بررسی شده هست عملیات انجام میده که خروجی تابع small میشه ارگومان دوم تابع index
سلام من بارها فایل رو دانلود کردم اما متاسفانه باز نمیشه
سلام دوست عزیز
این موضوع از جهات مختلف بررسی شده و فایل به روش های مختلف دانلود شده، هیچ مشکلی نداره. مجدد امتحان بفرمایید
با عرض سلام و ادب
جهت پویا سازی یک نمودار از تابع ایندکس استفاده کردم و در قسمت name manager فرمل زیر رو اختصاص دادم
در هنگام اجرای فرمول با تخصیص مقدار به سلول E1(که توسط دیتا ولیدیشن مقدار میگیره) در بعضی مقادیر خطای #N/A و بعضی مقادیر نتیجه اشتباه و در موارد محدودی نتیجه درست است.
ممنون میسم راهنمایی کنید
پی نوشت :فایل مشابه فایل پویاسازی نمودار که با تابع offset ایجاد کرده بودید هست.
درود بر شما
آرگومان آخر Match رو ۰ بذارید
لینک کار نمیکنه
دوست عزیز
چک شد. مشکلی وجود نداره. دوباره دانلور بفرمایید و حتما از حالت ZIP در بیارید.
موفق باشید