هر آنچه درباره index و match باید بدانید.
هر آنچه درباره index و match باید بدانید.
به غیر از VLOOKUP ،توابع INDEX و MATCH کاربردی ترین ابزار در Excel برای انجام جستجوها می باشد.ترکیب INDEX و MATCH قدرتمند و انعطاف پذیر است و شما می توانید آن را در انواع فرمول ها، از ابتدایی تا بسیار پیشرفته استفاده کنید.
در حالی که VLOOKUP به شما اجازه می دهد تا جستجو را با یک تابع انجام دهید، INDEX و MATCH به دو تابع نیاز دارند، یکی در داخل دیگری قرار می گیرد. بسیاری از کاربران این گیج کننده هست برایشان، چون آنها ترکیب توابع در Excel را نمی دانند، بنابراین از INDEX و MATCH استفاده می کنند.
حتماً باید. ترکیب توابع، کلید ساخت فرمول های پیشرفته تر در Excel را بلد باشیم.
مثالی از ترکیب توابع در اکسل
این مقاله به روش ساده طریقه استفاده از INDEX و MATCH برای انجام جستجو را بیان می کند.گام به گام با هم جلو می رویم ، ابتدا توضیح INDEX، سپس MATCH، بعد از آن طریقه ترکیب دو تابع را بررسی می کنیم تا یک جستجوی دو طرفه پویا ایجاد کنیم.
INDEX – مقدار را براساس مکان تعیین شده نشان می دهد.
عملکرد INDEX در اکسل فوق العاده انعطاف پذیر و قدرتمند است، و آن را در تعداد زیادی از فرمول های اکسل، به ویژه فرمول های پیشرفته می توانید پیدا کنید.
اما INDEX واقعا چه کار می کند؟
به طور خلاصه، INDEX مقدار یک مکان مشخص در یک لیست یا جدول را برمی گرداند.
INDEX(array, row_num, [column_num])
. به عنوان مثال، جدول سیارات منظومه شمسی را داریم ، و شما می خواهید نام سیاره چهارم، مریخ را با فرمول پیدا کنید.
شما می توانید با این فرمول ساده با تابع INDEX انجام دهید:
=INDEX(B3:B11,4)
[the_ad id=”25562″]
INDEX سلول چهارم را در محدوده B3:B11 پیدا کرده و مقدار سلول B6 را باز می گرداند.
INDEX و جستجوی دوبعدی
اگر می خواهید قطر مریخ را دریافت کنید چه؟
در این حالت، به INDEX شماره ردیف و ستون را می دهیم و در یک آرایه بزرگ (محدوده) داده ها به دنبالشان می گردیم. فرمول INDEX برای این منظور محدوده B3: D11 با شماره ردیف 4 و شماره ستون 2 می باشد.
=INDEX(B3:D11,4,2)
چگونه INDEX می تواند برای بازیابی یک مقدار در یک جدول دوبعدی استفاده شود؟
به صورت خلاصه ، INDEX موقعیت مکانی سلول در محدوده مشخص شده دریافت و مقدار آن را به صورت خروجی نمایش می دهد.هنگامی که محدوده یک بعدی است، شما فقط نیاز به یک شماره ردیف دارید. هنگامی که محدوده دو بعدی است، شما باید ردیف و ستون را مشخص کنید.
در این مرحله، شما ممکن است فکر کنید ” چگونه مکان سلول مد نظرمان را در صفحه اکسل همیشه به صورت دستی بدهیم؟ “
دقیقا درسته. ما نیاز به یک راه برای تعیین موقعیت چیزهایی که دنبالشان هستیم و آن تابع Match هست.
[the_ad id=”25577″]
MATCH – موقعیت را در یک لیست پیدا میکند.
تابع MATCH برای یک هدف طراحی شده است: موقعیت عددی یک آیتم را در یک لیست پیدا کند.
برای مثال، ما می توانیم از MATCH برای بدست آوردن موقعیت کلمه “هلو” در این لیست میوه ها مانند این استفاده کنیم:
=MATCH(“هلو”,B3:B9,0)
MATCH عدد 3 را باز می گرداند، از آنجا که “هلو” سومین مورد در محدوده است. اهمیتی ندارد لیست تان افقی یا عمودی باشد.
اگر ما لیست میوه ها را به صورت افقی مرتب کنیم، نتیجه یکسانی را با این فرمول MATCH دریافت می کنیم:
=MATCH(“هلو”,C4:I4,0)
تابع MATCH با محدوده افقی. همان نتیجه، 3 را باز می گرداند.
نوع Match – دقیق یا تقریبی؟
با تابع MATCH، اولین آرگومان مقدار مدنظر برای جستجو است، دوم محدوده جستجو است و آرگومان سوم نوع تطابق است.
MATCH(lookup_value, lookup_array, [match_type])
نوع (Match (match_type مهم است و کنترل تطبیق دقیق یا تقریبی را برعهده دارد.
. جدول زیر گزینه های زیر را خلاصه می کند:
Match type | رفتار | جزییات |
1 | تقریبی | تابع بزرگترین مقدار که کوچکتر یا برابر با مقدار جستجو است را پیدا می کند. محدوده جستجو باید به ترتیب صعودی مرتب شود. |
0 | دقیق | تابع اولین مقداری که دقیقا برابر با مقدار جستجو هست را پیدا می کند. آرایش محدوده نیازی به مرتب سازی نیست. |
-1 | تقریبی | تابع کوچکترین مقدار بزرگتر یا برابر مقدار جستجو را پیدا می کند. محدوده باید به ترتیب نزولی طبقه بندی شود. |
تقریبی | هنگامی که نوع مطابقت حذف شود، پیش فرض 1 (تقریبی) مدنظر قرار می گیرد. |
برای جستجوی دقیق Match، شما باید صفر را مشخص کنید.
با این حال، اگر شما نیاز به یک مقایسه تقریبی با مقدار جستجو در یک مقیاس داشته باشید، می توانید از 1 یا -1 استفاده کنید، و شما باید محدوده مدنظر را با توجه به نیازهای خود مرتب کنید، [AZ] زمانی که نوع مطابقت 1 است ، [ZA] هنگامی که نوع تطبیق -1 است.
احتیاط: مانند VLOOKUP، تابع MATCH در حالت پیشفرض یک و به صورت تقریبی می باشد. تطبیق تقریبی زمانی مفید است که بتوانید بهترین بررسی را در یک مقیاس از مقادیر پیدا کنید، اما این می تواند یک فاجعه باشد، زمانی که نیاز به یک بررسی دقیق دارید.
توصیه می کنم از تنظیمات “دقیق” استفاده کنید تا مشکلات احتمالی روبرو نشوید.
ترکیب MATCH و INDEX
حالا که ما مبانی INDEX و MATCH را گفتیم ، چگونه می توانیم دو توابع را در یک فرمول ترکیب کنیم؟
داده های زیر را در نظر بگیرید، یک لیستی از فروشندگان و فروش ماهانه را برای سه ماه به شما نشان می دهد: تیر ، مرداد، و شهریور.
میخواهیم یک فرمول را بنویسیم که مقدار فروش را برای ماه مرداد برای هر فروشنده ارائه دهد از بحث بالا، می توانیم آدرس را به INDEX بدهیم تا مقدار را به ما بدهد. برای مثال، برای بازگرداندن مقدار فروش مرداد مسعود، این فرمول INDEX این کار را انجام می دهد:
=INDEX(C3:E11,5,2)
در اینجا تابع INDEX مقدار سطر 5، ستون 2 از محدوده C3: E11 را به ما نشان خواهد داد. اما ما قطعا نمی خواهیم مکان مدنظرمان را دستی به تابع بدهیم بلکه ما یک جستجوی پویا را می خواهیم. کارمان را مرحله به مرحله انجام می دهیم. در مرحله اول شماره ستون را ثابت نگه می داریم و مقدار عددی 2 را به آن می دهیم و شماره ردیف را داینامیک انتخاب می کنیم.
چطور این کار را انجام خواهیم داد؟
تابع MATCH برای پیدا کردن جایگاه مسعود در لیست افراد کار خواهد کرد. در اینجا یک فرمول اصلاح شده، با استفاده از تابع MATCH، در داخل INDEX است. توجه داشته باشید که تعداد 5 را با MATCH جایگزین کردیم:
=INDEX(C3:E11,MATCH(“مسعود”,B3:B11,0),2)
هنگامی که MATCH”مسعود” را در B3: B11 پیدا می کند، شماره 5 را به طور مستقیم به INDEX برمی گرداند:
[the_ad id=”25574″]
=INDEX(C3:E11,5,2)
کاری که ما می خواهیم انجام دهیم این است تا هر نامی را انتخاب می کنیم مقدار آن به عنوان خروجی نمایش داده شود.
فرمول زیر نمونه بسیار ساده ای از این ایده را نشان می دهد.
=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)
MATCH از نام فروشنده در H2 برای پیدا کردن یک شماره ردیف برای INDEX استفاده می کند. INDEX موقعیت عددی می خواهد که این مقدار را MATCH پیدا می کند.
حالا شماره ستون را حل کنیم.
جستجوی دوطرفه با INDEX و MATCH
در بالا، ما از تابع MATCH برای پیدا کردن شماره ردیف برای هر فروشنده داده شده استفاده کردیم.در مثال بالا ما آمدیم و شماره ستون را ثابت نگه داشتیم و مثال مان را بررسی کردیم. چگونه می توانیم فرمول را به طور کامل پویا کنیم و فروش را برای هر فروشنده معین در هر ماه معین بازگردانیم؟ ترفند استفاده از MATCH یک بار برای گرفتن موقعیت ردیف و یک بار برای گرفتن موقعیت ستون است.
از مثال های بالا، ما متوجه می شویم MATCH با هر دو آرایه افقی و عمودی کار می کند. این بدان معنی است که ما می توانیم موقعیت ماه را با MATCH پیدا کنیم. به عنوان مثال، این فرمول موقعیت شهریور را برمی گرداند که 3 است.
=MATCH(“شهریور”,C2:E2,0)
البته نمی خواهیم به هیچ وجه از کد های پیچیده استفاده کنیم، پس اجازه دهید که نام ماه را بروزرسانی کنیم و از MATCH برای پیدا کردن شماره ستونی که نیاز داریم استفاده کنیم.
جستجوی دو طرفه با INDEX و MATCH کاملا داینامیک است و فرمول به شکل زیر است:
=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0))
اولین فرمول MATCH عدد 5 را به INDEX به عنوان شماره ردیف باز می گرداند، فرمول دوم MATCH عدد 3 را به INDEX به عنوان شماره ستون باز می گرداند. هنگامی که MATCH اجرا می شود، فرمول ساده تر می شود:
=INDEX(C3:E11,5,3)
توجه: می توانید از Data Validationرا برای ایجاد منو های کشویی ساده برای فروشنده و ماه استفاده کنید.
اما VLOOKUP
اولین نکته: INDEX + MATCH می تواند همه کارهای VLOOKUP را انجام دهد و حتی بیشتر. این یک روش انعطاف پذیر تر است تا همه چیز را جستجو نماید. در اینجا چند مزیت نسبت به VLOOKUP وجود دارد:
- INDEX و MATCH می توانند به سمت چپ یا راست در یک جدول حرکت نماید، VLOOKUP فقط می تواند در سمت راست حرکت نماید.
- INDEX و MATCH می توانند با محدوده افقی یا عمودی کار کنند، VLOOKUP تنها می تواند از محدوده های عمودی استفاده کند
- INDEX و MATCH می توانند با اطلاعات طبقه بندی شده در نزولی کار کنند، VLOOKUP تنها می تواند مطابق اطلاعات مرتب شده به ترتیب صعودی
- اگر مجموعه ای از داده های بزرگ داشته باشید، INDEX و MATCH می تواند سریعتر باشد
با این حال، هیچ دلیلی وجود ندارد که از VLOOKUP استفاده نکنید. این تابع عالی با مزایای خود است:
- VLOOKUP پیکربندی ساده است و نیاز به یک تابع دارد
- VLOOKUP یک نقطه شروع عالی برای یادگیری جستجو در اکسل است
- VLOOKUP برای دیگران آسان تر است تا متوجه طریقه فرمول نویسی شوند.
- VLOOKUP یک فرمول شناخته شده است.
[the_ad id=”25570″]
دلایلی که ممکن است بخواهید از INDEX + MATCH به جای VLOOKUP استفاده کنید:
- مقدار جستجو در سمت راست اطلاعاتی است که شما باید به آن مراجعه کنید
- محدوده داده افقی است و برای HLOOKUP مناسب نیست
- داده ها به ترتیب نزولی مرتب شده اند
- مجموعه داده ها بسیار بزرگ است و سرعت عمل یک عامل است
- شما دوست دارید. INDEX + MATCH 🙂
من معمولا به VLOOKUP متصل میشوم و در صورت نیاز سراغ INDEX + MATCH می روم.
درباره حسین صابری
عضو هیئت علمی دانشگاه آزاد - ارشد حسابداری (دانشجوی دکتری حسابداری) - کارشناس رسمی قوه قضاییه (حسابداری و حسابرسی) - برنامه نویس و مدرس کامپیوتر
نوشته های بیشتر از حسین صابریمطالب زیر را حتما مطالعه کنید
فایل حقوق دستمزد 98 و اکسل محاسبه مالیات حقوق 98
معرفی توابع جدید در اکسل ۲۰۱۹
همانطور که می دانید مجموعه آفیس هر سه سال یک بار نسخه جدید خود را عرضه می کند و در ورژن های جدید امکانات جدیدی را قرار می دهند که کار با نرم افزارها را برای کاربران راحت تر کند
در اکسل ۲۰۱۹ چندین ابزار کاربردی اضافه شده است که در این قسمت مب خواهیم به معرفی چند تابع جالب و بسیار کاربردی آن بپردازیم. توابعی که در این بخش یاد خواهیم گرفت در نسخه OFFICE 365 وجود داشتند اما چون این نسخه به صورت غیررایگان عرضه شده بود بسیاری از کاربران امکان پرداخت هزینه آن را نداشته و از این توابع محروم بودند
توابعی که به معرفی آنها می پردازیم شامل موارد زیر می باشند:
CONCAT
TEXTJOIN
MINIFS
MAXIFS
IFS
SWITCH
محاسبه مالیات حقوق تحت اکسل
ورود سریع تر اطلاعات
ورود سریعتر داده ها در اکسل به کمک قابلیت Auto Correctشاید برای شما پیش آمده باشد که در یک زمان ورود اطلاعات یک فهرست، به صورت مداوم مجبور به تکرار یک کلمه یا جمله هستید. بهترین راه برای این کار استفاده از ویژگی Auto Correct است. به عنوان نمونه ما در ورود اطلاعات یک جدول کلمه @talant_ir را دائما تکرار می کنیم، و تصمیم داریم با ورود عبارت @t به صورت خودکار کلمه @talant_ir تکرار شود. برای اینکار از مسیر ذیل استفاده می کنیم.
محاسبه بهای تمام شده به روش FIFO
بسیاری از حسابداران محاسبات مربوط به بهای تمام شده را به صورت دستی و به صورت کاردکس بالا انجام می دهند که این کار زمانبر و پر از اشتباه خواهد بوددر این آموزش می خواهیم محاسبات را با استفاده از نرم افزار اکسل انجام دهیم و دقت و سرعت محاسبات را بالا ببریم.
ساخت Pivot Table
1 دیدگاه
به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.
سلام خسته نباشید
من یک لیست از نفرات و نمره ها رو دارم و میخوام رتبه بندی نفراتی که بالاترین نمره رو میگیرند همیشه بصورت خودکار انجام بشه
الان با فرمول Large به ترتیب نمرات انجام میشه
با ترکیب فرمول MATCH+INDEX هم نفراتی که نمره متعلق به اونهاست پیدا میشه
فقط مشکل اینجاست که نفراتی که نمره مشابه دارند، نام اولین نفری که پیدامیکنه رو برای بقیه تکرار میکنه.
نام نمره
علی 40
حسن 30
محمد 40
محسن 52
نتیجه اینطوری میاد:
محسن 52
علی 40
علی 40
حسن 30
اگر امکان داره فرمول رو اصلاح کنید برام:
=INDEX($A$2:B13,MATCH(F3,$B$2:B15,0),1)
باتشکر