آموزش رایگان استفاده همزمان توابع offset،MatchوVLOOKUPدر اکسل ،تابع INDEX و تابع MATCH دو تا از توابع بسیار قوی در اکسل هستند که ترکیب و استفاده مناسب از اونها میتونه به ما در نوشتن فرمولهای مناسب تر کمک زیادی کنه. با توجه ب اینکه قبلاً با تابع INDEX آشنا شدیم در این تابع یک محدوده رو مشخص میکنیم و با دادن شماره سطر و ستون دلخواه یک سلول رو برمیگردونیم. حالا اگر به جای تعیین دستی مقدار سطر و ستون، اینکار رو با استفاده از نتیجه تابع MATCH انجام بدیم میتونیم خیلی از مشکلات فرمول نویسی برطرف کنیم. با استفاده از ترکیب دو تابع INDEX و MATCH در اکسل می توان شماره سطر یا ستون مقداری رو جستجو کنیم و مقدار متناظرش را در یک سطر یا ستون دیگر برگردانیم. تابع MATCH یک مقدار رو در یک محدوده جستجو میکنه و در صورت پیدا کردن اون مقدار در محدوده مورد نظر، موقعیت مقدار مورد نظر رو بر میگردونه.پاپولی ویدیو آموزشی توابع مفید اکسل را به شما عزیزان ارائه می دهد.
آموزش رایگان استفاده همزمان توابع offset،MatchوVLOOKUPدر اکسل
فرمول VLookup مقداری از هریک از ستونهای مورد نظر جدول داده ای را برمیگرداند که این کار به سادگی با تغییر عدد آرگومان سوم امکان پذیر است. با وجود سادگی اشکالاتی جزئی وجود دارد.
- چگونه میتوان عدد ستونی را که داده موردنظر درآن وجود دارد را مشخص کرد ؟
- چگونه میتوان عدد ستون موردنظر خود که در آرگومان سوم قراردارد را مشخص کرد ؟
- چگونه میتوان به راحتی شماره ستون موردنظر در فرمولهای چندگانه موجود در کاربرگی که شامل فرمولهای پیچیده است یا در کاربرگی که شامل چندین فرمول VLookup است، را تغییر داد ؟
- با اینکه میدانیم ستون مقداری که فرمول درپی جستجوی آن است بایستی اولین ستون جدول باشد. برای رفع این مشکل چه باید کرد؟
راه حل:
فرمول Match را بجای آرگومان سوم فرمول VLookup بنویسید.
[1]
اینجاست که میتونیم با استفاده از ترکیب دو تابع INDEX و MATCH این مشکل رو حل کنیم و به هدفمون برسیم.
با ذکر مثال فرضی: ابتدا تابع INDEX رو مینویسیم و پارامتر اول اون رو A:A قرار میدیم، چون میخوایم نام محصول رو مشخص کنیم و نام محصول هم در ستون A قرار داره.
در حالت عادی میتونستیم شماره سطر رو به تابع INDEX بدیم. شماره سطر عدد 1398 برابر 4 هست اما ما نمیخوایم این شماره سطر رو دستی وارد کنیم. بلکه میخوایم شماره سطر به صورت خودکار توسط اکسل پیدا بشه. اینکار رو با تابع MATCH انجام میدیم. تابع (MATCH(1398;B:B;0 دقیقاً اینکار رو برای ما انجام میده. یعنی عدد 1398 رو در ستون B پیدا میکنه و شماره سطر اون رو که برابر 4 هست برمیگردونه.
در نهایت تابع بخش 2 رو در تابع INDEX خودمون قرار میدیم تا تابعمون به شکل زیر کامل بشه.
INDEX(A:A;MATCH(1398;B:B;0
آموزش رایگان استفاده همزمان توابع offset،MatchوVLOOKUPدر اکسل
کاربرد توابع Vlookup ,Match
این توابع این امکان را فراهم می آورد که توسط excel در مجموع اطلاعات، به جزیی ترین اطلاعات به راحتی دست یابیم، فرض کنید در یک شیت اطلاعاتی از مشتری ها شامل شماره تماس ، نام ، شهر و سن را در محدوده A1:.B5000 فراهم آورده اید ، می خواهید بدانید شماره تماس 09120000000 متعلق به چه کسی است .
تابع Vlookup شماره تلفن مورد نظر را ستون Tel جستجو می کند و با توجه به محدوده جستجو نام متناظر با شماره را یافته بر می گرداند ، اما تابع Match شماره را در ستون Tel جستجو کرده و شماره سل متناظر با آن را بر میگرداند .
نکته: اگر در قسمت Lookup Array فرمول Match ارایه افقی وارد کنیم تابع محل ستون آیتم مورد جستجو را بر می گرداند ، این خصوصیت باعث می شود باترکیب توابع Match و Vlookup فرمول های پویاتر و کارآمد تری را به کار بگیریم.
کاربرد تابع offset
در واقع تابع offset فضای وسیع تر را نمایش میدهد همانند آینه محدب عمل میکنه
Offset بخشی از یک محدوده بزرگ را برمیگرداند فرمولی:
=OFFSET(A1;5;5;2;3)
آموزش رایگان استفاده همزمان توابع offset،MatchوVLOOKUPدر اکسل
با توجه به اینکه فرمول match موقعیت مکانی یه داده را بازمیگرداند ، می تواند به عنوانی یکی از آرگومان ها فرمول offset بکار رود ، نکته دیگر اینکه فرمول های vlookup و match در صورت عدم وجود داده مورد نظر خطاری #value را باز می گردانند همچنین فرمول offset بدون بکار بردن و ترکیب با سایر توابع مانند sum این خطا را باز خواهد گرداند .
تابع Match اکسل یکی از توابع مهم این نرم افزار هست که خیلی از مواقع میتونه مشکلات رو حل کنه. این تابع یک مقدار رو در یک محدوده جستجو میکنه و در صورت پیدا کردن اون مقدار در محدوده مورد نظر، موقعیت مقدار مورد نظر رو بر میگردونه. در ادامه با کاربرد این تابع بیشتر اشنا میشیم.
قاعده کلی تابع Match اکسل به صورت زیر هست:
(نوع جستجو [اختیاری]، محدوده جستجو، مقدار موردنظر برای جستجو) MATCH
