
همونطور که قبلا گفتیم یکی از مهم ترین و پرکاربردترین توابع جستجو، تابع Index هست. اغراق نیست اگه بگم قسمت اعظمی از فرمول نویسی حرفه ای به تسلط به تابع Index وابسته هست. پس اگه میخواید یک فرمول نویس حرفه ای باشید از این تابع جادویی غافل نشید.
با آرگومان ها و منطق عملکرد تابع Index آشنا شدیم. همچنین یک حالت ترکیبی با تابع Match رو قبلا با هم دیدیم. حالا میخوایم به ویژگی بسیار جالب و کاربردی این تابع بپردازیم:
با صفر قرار دادن هر یک از آرگومان های ردیف یا ستون، میتونیم یک محدوده رو در نظر بگیریم. یعنی چی؟
فرض کنید میخواهیم بیشترین فروش در شعبه ۲ مشخص بشه. به تابع زیر دقت کنید:
=Index(A1:F13,3,0)
این تابع، ردیف سوم از محدوده A1:F13 رو در نظر میگیره و چون ستون رو ۰ قرار دادیم، کل ستون های مربوط به اون ردیف رو در نظر میگیره. بعبارتی، این فرمول محدوده A3:F13 رو در حافظه خود نگه میداره. برای حل سوال خودمون که میخواستیم بیشترین فروش شعبه ۳ رو پیدا کنیم، طبق شکل ۱ این فرمول رو مینویسیم:
=Max(Index(A1:F13,3,0))

شکل ۱- کار با محدوده ها
حتما این سوال براتون پیش میاد که چرا مستقیم نمی نویسیم Max(A3:F13) ؟
خب مثال زیر رو ببینید، متوجه جواب سوال میشید.
فرض کنید در یک سل میخوایم شعبه رو انتخاب کنیم و هر بار با توجه به انتخاب ما، بیشترین فروش مربوط به همون شعبه رو به ما بده. این مستلزم اینه که هر بار ردیف مربوط به شعبه مورد نظر داخل تابع Max قرار بگیره:

قبلا ترکیب Index و Match رو با هم بررسی کردیم. در این مسئله، از طریق تابع Match تشخیص میدیم که هر شعبه چندمین ردیف از محدوده داده هاست. آرگومان ستون رو هم که صفر قرار دادیم. یعنی هر بار میاد میبینه شعبه مورد نظر ما چندمین ردیف هست، بعد کل ردیف مربوط به اون شعبه رو داخل Max قرار میده. به این ترتیب بیشترین فروش مربوط به هر شعبه فراخوانی میشه.
=Max(Index(A1:F13,Match(H3,A1:A13,0),۰))
نحوه محاسبه این فرمول رو در زیر می بینید:

این ویژگی برای ردیف هم صادقه. یعنی با صفر قرار دادن آرگومان ردیف، کل ستون مربوطه در نظر گرفته میشه. به فرمول Max(Index(A1:F13,0,0)) دقت کنید. این تابع بیشترین مقدار کل محدوده A1:F13 رو به ما میده. چون صفر گذاشتن آرگومان ردیف، یعنی همه ستون ها و صفر گذاشتن آرگومان ستون، یعنی همه ردیف ها. پس یعنی همه محدوده رو در نظر بگیر.
این ویژگی تابع Index در ترکیب توابع و انتخاب محدوده های مختلف و در نتیجه انعطاف پذیری و هوشمندی هر چه بیشترفرمول نویسی خیلی اهمیت داره. باز هم تاکید میکنم، تابع Index از توابع بسیار بسیار انعطاف پذیر دسته تابع جستجو هست که تسلط به اون برای هر کسی که می خواد حرفه ای بشه، لازمه. در پست های بعدی به بیان سایر ویژگی های Index خواهم پرداخت.
آموزش ویدئویی ترکیب تابع Index و Match
چند آموزش مرتبط با این تابع که توصیه میکنم حتما ببینی:
درود بر اساتید محترم
خیلی آموزش کاربردی و خوبی بود
مطالب را کاملا شیوا وساده بیان میکنید ضمن اینکه ویدیو هم داره که کمک خوبیه.
اکسل مثل یک جادو میمونه که وقتی یادش میگیری مسحور قدرتش میشی
بسیار ممنونم از سایت خوبتون
باسلام مجدد خدمت آقای چراغی عزیز و خانم خاکزاد بزرگوار
واقعا ممنونم از شما ممنونم.من توابع v lookup ؛index و match رو کامل یاد گرفتم و استفاده کردم فقط یه مشکلی هست اخر سر که دو تابع رو ترکیب میکنم هر کاری کردم نتونستم مثل شما که تو یه سلول برای مثال شعبه یک رو نوشتین و با استفاده از کشویی اون سلول شعبه های دیگه رو انتخاب کنم ممنون میشم راهنماییم کنین
درود بر شما
تابعی که نوشتید و بذارید تا بررسی بشه.
ی جای کار و اشتباه میکنید.
سلام و وقت بخیر
من میخوام ی دیتابیس بسازم ی سوال دارم ، در ستون اصلی تاریخ هست و در ستون دوم نام تجهیزات ، در ستون سوم وزن تجهیز و …. قرار داره . نحوه ورود داده اینطور هست ک طبق تاریخ ، نام تجهیز و وزن و … وارد می کنم ولی ممکن هست ک در یک تاریخ چند تجهیز وارد شده باشه . چگونه میشه تابعی از ترکیب index(match نوشت ک تمامی تجهیزات مربوط ب یک تاریخ فراخوانی بشه ؟ ممنون میشم راهنمایی کنید
درود بر شما
حتما دقت کنید که ساختار دیتابیس داشته باشید.
یعنی اگر در یک تاریخ چند تجهیز وارد میشه، برای هر تجهیز یک ردیف اختصاص بدید. (مثلا اگر در یک تاریخ ۳ تجهیز دارین، ۳ ردیف اختصاص بدین و تاریخ یکسان بزنید و اسم هر تجهیز در یکم ردیف باشه)
بعد میتونید براحتی با پیوت گزارش بگیرید
یا اینکه فرمول نویسی آرایه ای باید استفاده کنید (در هر دو مورد نکته در خصوص دیتابیس استاندارد هست)
موفق باشید
خیلی مطالبتون کاربردی و خوبه
ممنون