چندین معیار را در ردیف یا ستون جستجو کنید

فصل 1:

مبانی INDEX & MATCH

کارشناسان اکسل به طور کلی VLOOKUP را با INDEX و MATCH جایگزین می کنند.

برخلاف VLOOKUP که فقط به سمت راست جستجو می‌کند، INDEX و MATCH می‌توانند در هر دو جهت - چپ و راست نگاه کنند.

INDEX & MATCH می‌تواند جستجوهای دو طرفه را با نگاه کردن در امتداد ردیف‌ها و در امتداد ستون‌ها برای یافتن تقاطع درون یک ماتریس انجام دهد.

INDEX & MATCH کمتر مستعد خطا است.

فرض کنید یک VLOOKUP دارید که در آن مقدار نهایی که می خواهید برگردانده شود در ستون N است.

مقدار جستجوی شما در ستون A است.

شما باید کل محدوده A تا N را برجسته کنید و سپس عدد شاخص خود را 14 ارائه کنید.

اگر هر یک از ستون‌های میانی را حذف کردید، باید آن شماره فهرست را به‌روزرسانی کنید.

هنگام استفاده از INDEX & MATCH نیازی نیست نگران این موضوع باشید.

در مجموع، INDEX و MATCH نسبت به VLOOKUP انعطاف پذیرتر است.

آنچه هست

INDEX و MATCH توابع "تودرتو" هستند - دو تابع با هم استفاده می شوند.

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

سینتکس INDEX به صورت زیر است:

• محدوده ای که مقدار بازگشتی در آن قرار دارد.
• تعداد ردیف هایی که باید به سمت پایین حرکت کنند (شاخص ردیف)
• تعداد ستون هایی که باید به سمت راست حرکت کنید (ستون شاخص). این آرگومان اختیاری است. اگر فقط یک ستون دارید، می‌توانید آرگومان ستون را خالی بگذارید، در غیر این صورت، باید تعداد ستون‌هایی را برای جابه‌جایی در محدوده مشخص کنید.

در اینجا یک مثال از INDEX بدون MATCH آمده است:

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

تابع MATCH برای برگرداندن موقعیت یا آدرس مقدار جستجو به تابع INDEX طراحی شده است. این همان چیزی است که MATCH را با سایر توابع متمایز می کند. VALUE را در سلول بر نمی گرداند، اما POSITION سلول را در یک محدوده مشخص برمی گرداند.

نحو تابع MATCH به شرح زیر است:

• مانند VLOOKUP، Match به یک مقدار جستجو (هدف) نیاز دارد. کاربر می تواند به یک سلول ارجاع دهد یا مستقیماً مقدار را در فرمول تایپ کند.
• Lookup_array: "لیست" (محدوده) که مقادیر بازگشتی در آن قرار دارند.
• Match_type: 0 برای "دقیق"

در مثال زیر، تابع MATCH مقدار 4 را برمی گرداند. 200 4 ردیف پایین تر از محدوده مشخص شده است.

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

نکته: اگر مسابقه شما حاوی چندین معیار است ، باید معیارهای متعدد را به یک خیابان یک طرفه تبدیل کنید. شما می توانید این کار را با بسته بندی عملکرد مسابقه در یک شاخص دیگر یا با تبدیل فرمول خود به عملکرد آرایه - CSE - انجام دهید. برای تماشای این کار در عمل ، به سخنرانی Index & Match در آموزش پیشرفته Excel Xelplus. com/advancedexcelcourse مراجعه کنید

کنار هم قرار دادن

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

این فرمول را می توان بیشتر برای یافتن موجودیت با بالاترین فروش دستکاری کرد. برای انجام این کار ، به سادگی عملکرد حداکثر () را در داخل تابع مسابقه لانه کنید تا مکان بالاترین تعداد را پیدا کنید.

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

راه آسان برای به خاطر سپردن نحو شاخص و مسابقه است

= فهرست (محل دامنه پاسخ من ، مطابقت (مقداری که من به دنبال آن هستم ، محدوده جستجو برای یافتن مقدار ، 0))

جستجوی ماتریس

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

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

نحو عملکرد مسابقه مسابقه شاخص عبارت است از:

= شاخص (دامنه کاملی که پاسخ شما را می توان در آن یافت ، مطابقت داشته باشد (مقداری که در ردیف ها به دنبال آن هستم ، دامنه ای که برای یافتن مقدار ، 0) ، مطابقت دارم (مقداری که من در آن جستجو می کنمستون ها ، دامنه ای که باید برای یافتن مقدار ، 0))

ساختمان روی شاخص و عملکرد مطابقت

با قرار دادن INDEX و MATCH در فرمول های دیگر می توانید محاسبات پیچیده تر و پویاتری ایجاد کنید. مثال زیر نشان می دهد که چگونه می توانید INDEX و MATCH را در تابع SUMIFS قرار دهید. به این ترتیب می‌توانید مجموع ستون فروش یا ستون حجم را بسته به انتخاب «تعداد» یا «فروش» در G4 نشان دهید. معیارهای ارزش جمعی که استفاده می کند West_01 در ستون A، 2014 در B و Jan در C است. هنگامی که مقدار G4 را از "Volume" به "Sales" تغییر دادید، نتایج به SUM مقادیر از ستون فروش تغییر می کند. این را می توان در گزارش های داشبورد پویا استفاده کرد.

فیلم و کتاب کار

INDEX & MATCH یکی از توابع مورد علاقه من است زیرا می توان از آن در بسیاری از موقعیت های پیچیده استفاده کرد. درک کامل این عملکرد چیزی است که گوروها را از افراد توانا جدا می کند.

می توانید کتاب کار را از اینجا دانلود کنید.

قسمت 2 این راهنما را بررسی کنید، جایی که به کاربردهای پیشرفته تر INDEX & MATCH می پردازیم.

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

از این تکنیک ها در گزارش های خود استفاده کنید

ارزش بی نظیر!

23 نظر

[…] شما با Index و Match آشنا هستید و می دانید که چگونه یک مشکل جستجوی ماتریس را حل کنید. شما می خواهید مقداری را در داخل ماتریس با […]

بسیار آموزندهلذت بردن از یادگیری نحو Index و Match و نحوه کنار هم قرار دادن آنها می تواند به جستجوهای عالی منجر شود…….

در این مورد به کمک نیاز دارید. من یک برگه برای فاکتور و یک برگه دیگر با نرخ های چند sku دارم.

وقتی 2 ستون در برگه یک با مقداری متن و مقادیر وارد می کنم، می خواهم فرمولی را برای متن وارد شده در 2 ستون در صفحه دوم بررسی کنم و مقدار آن را از ستون ششم در صفحه اول دریافت کنم.

نظر بدهید به اشتراک بگذارید و مشترک شوید

کتاب الکترونیکی رایگان کجاست

سلام نارش – پس از ثبت نام لینک را دریافت خواهید کرد.

لیلا – فهرست (مطابقت)… چیزهای عالی! با تشکر برای به اشتراک گذاری. من زیاد از آن استفاده می‌کنم، اما متوجه شده‌ام که به نظر می‌رسد مقدار داده‌ای که برای جستجو وجود دارد، عملکرد را از بین می‌برد. آیا می توانید این را تایید کنید؟

خواهش میکنم. من با چنین محدودیتی برخورد نکرده ام. چند ردیف / ستون داده؟

من می خواهم بدانم وقتی طول آرایه Index Array & Lookup یکسان نیست، فرمول Index/Match به درستی انجام می شود. در غیر این صورت روشی برای جلوگیری از چنین خطا وجود دارد.

سلام آجیت – طول ناحیه INDEX و MATCH باید یکسان باشد، در غیر این صورت MATCH آدرس اشتباه را به INDEX برمی گرداند. فرمول صحیح در مورد شما، واقعاً به تنظیمات و آنچه که به دنبال آن هستید بستگی دارد.

عاشق کار شما لیلا، من بسیاری از آموزش های ویدئویی شما را تماشا کرده ام!

هر فرصتی که می توانید یک فیلم دقیق دیگر را در مورد راه حل های فهرست / مطابقت / افست / sumproduct با نگاهی به ظرافت های خلاصه داده ها برای محدوده تاریخ خاص انجام دهید (به عنوان مثال 12 ماه گذشته که در آن 5 سال داده در ستون ها وجود دارد)؟به خصوص با جداول/منابع ساختاری ، به نظر من چند روش برای انجام کارها وجود دارد اما انجام آن به خوبی دشوار است ...

سلام لیلا ، سلام به همه ،

با تشکر از به اشتراک گذاشتن این همه چیزهای عالی!

من کمی جستجوی پیچیده ای برای انجام دادن دارم: می گویم من لیستی از شناسه های منحصر به فرد را در Col1 دارم و اطلاعاتی که به دنبال آن هستم ، بسته به مورد ، در Col. 3 یا Col. 4 است.(داده های سرهنگ 3 & Col. 4 مجزا است ، بدون تکرار). من همچنین لیستی از تمام مقادیر جستجوی مورد علاقه خود را دارم (برخی از سرهنگ 3 ، برخی از کلین 4). آیا راهی برای استفاده از فهرست (مطابقت ()) برای جستجوی اطلاعات وجود دارد؟

دوباره متشکرم!

سلام - اگر منطق را در جایی دارید که کدام ستون را جستجو کنید ، می توانید آن را کار کنید. در جایی می توانید یک جدول با مقادیر جستجو و کدام ستون مورد نیاز خود داشته باشید - می توانید در آن جدول یک جستجوی را انجام دهید ، ستون را بگیرید و از آن در فرمول فهرست خود استفاده کنید. یا می توانید از ستون های یاور استفاده کنید. دو فرمول بنویسید - یکی که مقدار Col 3 و دیگری Col 4 را بدست می آورد - و سپس در یک ستون نهایی ، یکی را که می خواهید بر اساس جدول اصلی نشان دهید ، بفهمید. سپس می توانید دو ستون یاور را پنهان کنید.

برخلاف گفته های موجود در بالای این پست که چرا برخی از متخصصان به اصطلاح اکسل به طور کلی Vlookup را با شاخص و مطابقت جایگزین می کنند ، امکان پذیر است (و نسبتاً آسان) داشتن VLookup یک مقدار را از یک ستون به سمت چپ از سمت چپ بازگرداندستون جستجو!

فرض کنید موارد زیر: • مقادیر جستجو در D1: D5 است • مقادیر برگشتی مربوطه در A1: A5 • فرمول Vlookup در A10 است • مقدار جستجو در D10 است

فرمول زیر در A10 مقدار تطبیق را از A1: A5 برای مقدار جستجو در D10 برمی گرداند:

= Vlookup (D10 ، انتخاب کنید (، $ 1 $: 5 دلار 5 دلار ، $ 1 دلار: $ 5 $) ، 2 ، false)

همانطور که مشاهده می کنید ، عملکرد انتخاب با آرگومان آرایه آن سوئیچ جادویی را فراهم می کند. این فرمول VLOOKUP/انتخاب بسیار شبیه به یک دسته کوچک موسیقی جاز/مسابقه است ، و همچنین به این معنی است که فقط ستون جستجو و ستون ارزش بازگشت ، بدون توجه به اینکه در ورق قرار دارند ، ارجاع می شوند. این همچنین از سایر مواردی که برای وارد کردن یا حذف ستون در آنچه که معمولاً در محدوده ای که به عنوان آرگومان Table_Array مشخص شده است ، جلوگیری می کند ، در جایی که شما از یک شماره شاخص استفاده کرده اید ، جلوگیری می کند (دومی در هر رویدادی عمل خوبی نیست ،از آنجا که باید از ستون یا ستون استفاده شود تا شماره شاخص یک آرگومان پویا باشد.)

مقایسه یک VLOOKUP برهنه (یک عملکرد) با یک شاخص/مسابقه (دو عملکرد) به سادگی ناعادلانه است!

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

دو انگشت شست برای خلاقیت.

من در فرمول اکسل جدید هستم. من این آرایه فرمول را دارم:

= ifError (شاخص ($ 10 $: $ al 500 $ ، کوچک (if ($ 2 $ = $ f 10 $: $ f 500 $ ، $ 10 $: $ 10 $ 500 $) -min (ردیف 10 $: $ f500 دلار))+1 ، "") ، ستون (a1)) ، "-")

این داده های عصاره فرمول از AL10: AL500 BASE بر روی معیارهایی که در E2 وارد می کنم که به مجموعه ای از داده ها در F10: F500 اشاره دارد.

مشکل این است که نتیجه به نظر می رسد شامل یک مقدار تکراری نیز هست.

آیا می توانم می دانم که اگر بخواهم فرمول فقط نتیجه منحصر به فرد تولید کند ، راه حلی وجود دارد؟

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

پیشاپیش متشکرم

از سوال شما سپاسگزاریم. برای کمک به پاسخ به سؤال خود ، پیوند زیر به انجمن فناوری Microsoft Excel بهترین مکان برای مطرح کردن سوال شما خواهد بود. اگر یک پرونده نمونه برای بارگذاری دارید ، این امر به توسعه یک دوره عمل بسیار کمک می کند.

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

با بیش از 25K عضو و تقریباً 30K پست ، راه حل شما یا آماده و انتظار است یا امکان پاسخ سریعتر از آنچه ممکن است ممکن است باشد.

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

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

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

با تشکر - تیم Xelplus

من از یک مسابقه با شاخص پیچیده استفاده می کنم و نتیجه را در ستون 10 (10 بیش از) پیدا کردم ، سپس نیاز به اضافه کردن یک ستون در جدول دارم. نتیجه من (10) اکنون اشتباه است. چگونه می توانم بدون بازنویسی همه فرمول ها این کار را برطرف کنم؟

از سوال شما سپاسگزاریم. برای کمک به پاسخ به سؤال خود ، پیوند زیر به انجمن فناوری Microsoft Excel بهترین مکان برای مطرح کردن سوال شما خواهد بود. اگر یک پرونده نمونه برای بارگذاری دارید ، این امر به توسعه یک دوره عمل بسیار کمک می کند.

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

با بیش از 25K عضو و تقریباً 30K پست ، راه حل شما یا آماده و انتظار است یا امکان پاسخ سریعتر از آنچه ممکن است ممکن است باشد.

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

تیم Xelplus

به لطف نمودارها و آموزش های مشترک ، کل فرآیند حداکثر ساده می شود. من سعی می کنم دستورالعمل های شما را دنبال کنم.