جستجو برای:
سبد خرید 0
  • صفحه اصلی
  • دوره های آموزشی اکسل
  • آموزش اکسل
    • آموزش اکسل مقدماتی
    • آموزش اکسل پیشرفته
    • آموزش اکسل در حسابداری
    • آموزش ترفند اکسلی
    • پاسخگویی به سوالات اکسلی
  • فرمول ها و توابع اکسلی
  • آموزش گوگل شیت
  • درباره ما
مطالب کمیاب در اکسل و حسابداری

ورود

گذرواژه خود را فراموش کرده اید؟

ثبت نام

  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir
مطالب کمیاب در اکسل و حسابداری
  • صفحه اصلی
  • دوره های آموزشی اکسل
  • آموزش اکسل
    • آموزش اکسل مقدماتی
    • آموزش اکسل پیشرفته
    • آموزش اکسل در حسابداری
    • آموزش ترفند اکسلی
    • پاسخگویی به سوالات اکسلی
  • فرمول ها و توابع اکسلی
  • آموزش گوگل شیت
  • درباره ما
آخرین اطلاعیه ها
جهت نمایش اطلاعیه باید وارد سایت شوید
شروع کنید
0

وبلاگ

مطالب کمیاب در اکسل و حسابداری > فرمول ها و توابع اکسل > آموزش ترکیب 2 تابع Index و Match +مثال ویک نکته طلایی

آموزش ترکیب 2 تابع Index و Match +مثال ویک نکته طلایی

ارسال شده توسط محمود بنی اسدی (مدیر سایت)
فرمول ها و توابع اکسل
ترکیب دو تابع MATCH و INDEX

ترکیب دو تابع index و match

تابع index و match دوتا از توابع مهم اکسلی می باشند که برای جستجوی پیشرفته مورد استفاده قرار می گیرند.
این توابع می‌توانند جایگزین بسیار خوبی برای vlookup باشند .زیرا vlookup دارای یک سری محدودیت‌ها بوده ولی این توابع دیگر هیچ محدودیتی نداشته و من پیشنهاد می‌کنم از این به بعد هر جایی که نیاز به vlookup بود از ترکیب دوتا تابع index و match استفاده کنید.

فهرست مطالب

دلایل و نکات مهمی که بهتر است بجای تابع vlookup از این دو تابع استفاده گردد به شرح زیر می باشد .

  1. تابع vlookup نمی تواند به عقب حرکت کند و ستون قبل از ستون حاوی داده مورد جستجو را استخراج کند.
  2. در صورتی که یک ستون در بین محدوده انتخابی در تابع vlookup حذف یا اضافه شود نتیجه تابع اشتباه خواهد شد.
  3. در حجم داده زیاد ،سرعت تابع vlookup کمتر است.

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

ترکیب تابع index و match

تابع index

در این مرحله لازم است تابع index را بنویسیم ( در مقاله آموزش صفر تا 100 تابع index در اکسل،همراه با مثال کاربردی ، بطور جامع برای شما این تابع را توضیح داده اییم و اگر آشنایی با این تابع ندارید حتما آن را مطالعه فرمایید )

index(array;Row_num;[column_num])

قسمت اول باید Array یا محدوده مون رو انتخاب کنیم، محدوده منطقه‌ها مد نظر ما هست
جداکننده را گذاشته و سپس قسمت بعدی فرمول از ما می پرسد ، سطر چندم رو می‌خواهی؟
ما فرضاً کارشناس فروش آقای هادی نژاد‌حسن را می خواهیم که در سطر هفتم نوشته شده است بعد از آن پرانتز رو بسته و Enter

در اینجا من از روی این فیلد داده‌ای نگرفتم و نتیجه‌ام رو بصورت دستی وارد کردیم که در سطر چندم هست.

ترکیب تابع index و match

تابع MATCH

و اما سوال اینجاست که چطور می ‌توانیم این را به سلول کارشناس فروش لینک دهیم؟
ما یک تابعی داریم به اسم تابع match که این تابع می‌تواند درون یک محدوده به ما بگوید که یک داده‌ی مشخص چندمین سلول هست.

MATCH(lookup_value, lookup_array, [match_type])

به این صورت نوشته می‌شه که مساوی match پرانتز باز.
در وهله اول ا ما می پرسد دنبال چه داده‌ای می‌گردید یا همان Lookup_Value شما چیست ؟
من کارشناس فروشم رو براش انتخاب می‌کنم و جدا کننده رامی‌گذارم.
سپس از ما میپرسد ، این اسم رو در کدوم محدوده انتخاب کنم؟ من اینجا ستون کارشناس فروش رو انتخاب می‌کنم.
و نکته ایی که حتما به آن توجه کنید آن است که همیشه باید برای تابع match در قسمت Lookup_Array یا یک ستون رو انتخاب کنید یا اگر می‌خواهید سطری شماره ستون رو بهتون بده یک سطر رو انتخاب کنید. نمی‌توانید بیشتر از یک ستون یا یک سطر را انتخاب کنید و جدا کننده را گذاشته و قسمت آخر را همیشه صفر می‌گذاریم. پرانتز رو می‌بندم. Enter .

ترکیب تابع index و match

ترکیب تابع index و match

در این قسمت ما توانستیم با استفاده از فرمول match متوجه شویم که هادی نژد حسن در محدوده هفتم داده ما قرار دارد .
پس من خیلی راحت می‌توانم این تابع را کپی کنم Ctrl+c و داخل تابع index قسمت دومش که دستی تایپ کرده بودم را با این فرمول جایگزین کنم تا بر اساس نام کارشناس فروش شماره ردیفش را پیدا کند و ستون متناظرش را به من نتیجه بدهد. Enter . که باید نتیجه کلمه غرب باشد .

ترکیب تابع index و match

حالا اگر بخواهیم سود را استخراج کنیم به چه صورت می‌توانیم عمل کنیم؟
می‌نویسیم مساوی index پرانتز باز، محدوده‌ای که سود ما در اون قرار داره کجاست؟
. جداکننده را گذاشته سپس از ما می پرسد سطر چندم رو می‌خواهی؟ که همون جور که قبلاً گفتم با تابع match ما سطرش رو بهش می‌دیم، match پرانتز باز، match کن. چی رو match کنه؟ نام کارشناس فروش ما رو. کاما Lookup_Array کجاست؟
کارشناس فروش‌ها کجان؟ این محدوده. دقت کنید محدوده‌ای که برای index انتخاب می‌کنید و محدوده‌ای که برای match انتخاب می‌کنید باید ارتفاعشون یکی باشه. یعنی اگر این از D6 تا D15 است کارشناس فروش ما باید حتماً از B6 تا B15 باشه نمی‌تونه از B5 باشه تا B15 یا از B6 باشه تا B17 کاما، قسمت آخر ، صفر پرانتز بسته. تابع match رو پرانتزش رو بستم و پرانتز تابع index رو هم می‌بندم اینجا به من سود رو نشون می‌ده.

ترکیب تابع index و match

کار دیگری که ما می‌خواهیم با این تابع انجام دهیم  این است که یه کم پیشرفته‌تر باهاش عمل کنیم به این صورت که به ما بگوید که اگر شخصی اینجا سود رو انتخاب کرد سود رو به ما نشون بده و درآمد رو انتخاب کرد درآمد رو به ما نشون بده و اینجا هم بتونه شخص کارشناس فروش‌هاش رو عوض کنه و نتیجه رو اینجا ببینه.
به چه صورت عمل می‌کنیم؟
در ابتدا مساوی index کن پرانتز را باز می کنیم سپس محدوده ما کجاست؟ اینبار چون ممکن است  درآمد باشه و هم ممکنه سود باشه من هر دوی این ستون ها را باید انتخاب کنم.
کاما سطر چندم؟ خوب سطرش رو گفتم که با تابع match پیدا می‌کنیم. match پرانتز باز آقای نژادحسن رو بگرد.
کاما در کجا؟ در این کارشناسای فروش، کاما صفر، پرانتز بسته.
خوب کاما رو بگذارم می‌رم قسمت سوم تابع index که قبلاً توضیح دادم در قسمت سوم باید بگم که حالا تو این دو تا ستون رو به من دادی ستون چندم رو می‌خوای؟
خوب من چجوری می‌تونم بفهمم که ستون چندم؟ باید از روی فیلد کنار اون و این مسئله رو متوجه بشم پس دوباره می تونم match کنم .
چی رو match کنم؟ این سلول کنارش که توش از لیست کشویی کاربر من قراره درآمد یا سود رو انتخاب کنه
کاما کجا بیاد match کنه ؟ توی این دو تا فیلد و کاما صفر.

ترکیب تابع index و match

ببینید در این قسمت کاربر من وقتی مثلاً درآمد رو انتخاب کرد تابع match میاد به این دو تا سلول نگاه می‌کنه می بینه که درآمد اولین ستون نتیجه‌اش می‌شه یک. پس در نتیجه کل این قسمت نتیجه‌اش می‌شه یک و این یک رو می‌ذاره توی تابع index و نتیجه ما درست می‌شه. پرانتز رو می‌بندم. Enter.
و  اگر من بجای درآمد سود رو انتخاب کنم خیلی راحت سود رو به من می‌ده اینجا هم اگر بجای کارشناس فروشم یه کارشناس فروش دیگه انتخاب کنم خیلی راحت سودش رو به من می‌ده.

ترکیب تابع index و match

نکته طلایی

این فرمول با توجه به اینکه یه کم طولانی‌تر هست، من یه ترفندی که استفاده می‌کنم به این صورت می باشد که چارجوب این فرمول رو نوشته و آن را قرار می‌دم در قسمت AutoCorrect اکسل قرار می دهم تا خیلی سریع اکسل بتونه نتیجه را به من بدهد.
چطور این کار رو انجام بدهیم؟
ببینید من چارچوبش رو اینجا نوشتم که
مساویه با index و SelectReturnColumn یعنی ستونی که می‌خواد به ما بده و match کنه قسمت‌های مختلفش رو حالا با تعریف خودم نوشتم.
این رو کپی می‌کنم میام قسمت File ، Options ،و دراینجا قسمت Proofing ، AutoCorrect Options…
اینجا یک حالت اختصاری رو براش قرار می‌دم که من iii براش قرار دادم و گفتم که هر وقت من iii رو نوشتم این چارچوب رو برای من بیا نشون بده. خوب ok رو می‌زنم.
حالا دیگه اگر بخوام تابع‌ام رو بنویسم خیلی راحت‌ترم. اینجا بدون هیچ مساوی iii می‌نویسم space می‌زنم تابعم نوشته می‌شه بعد توی قسمت‌های مختلف باید جایگزین کنم. مثلاً Array که این قسمت هست برای index می‌شه کجا؟ می‌شه سودم. بعد match رو خودش نوشته. خوب داخل match ، Lookup_Value ام چیه؟ اینه و Lookup_Value ام چیه؟ Lookup_Array ام این کارشناس فروش‌هاست. تمام. این نتیجه رو به ما داد.

ترکیب تابع index وmatch
ترکیب تابع index وmatch

جهت مشاهده آموزش ویدئویی ترکیب تابع index و match بر روی لینک زیر کیلیک کرده و آن را داخل پیج ایسنتاگرامی ( softwaretrain ) مشاهده فرمایید .

آموزش ویدئویی ترکیب تابع index و match
برچسب ها: MATCHآموزش تابع MATCHآموزش توابع اکسلترفند اکسلتکنیک های اکسل
آواتار محمود بنی اسدی (مدیر سایت)
درباره محمود بنی اسدی (مدیر سایت)

فارغ التحصیل کارشناسی ارشد حسابداری، ده سال سابقه تدریس اکسل در سطوح مختلف از قبیل فرمول نویسی، ابزارهای هوش تجاری، ترفندها و ... ، نویسنده شش مقاله در سطح ملی و ISI

نوشته های بیشتر از محمود بنی اسدی (مدیر سایت)
جدید تر آموزش صفر تا 100 تابع index در اکسل همراه با مثال
قدیمی تر آموزش جامع رسم نمودار در اکسل با کمک توابع بصورت رایگان+مثال

دیدگاهتان را بنویسید لغو پاسخ

نوشته های اخیر
  • صفر تا صد آموزش تابع IF در اکسل با 4 مثال کاربردی
  • آموزش ثابت کردن سطر و ستون ها در صفحه اکسل و چاپ
  • 4نکته کاربردی اکسل که هر کاربر باید بداند!
  • آموزش Pivot Table در اکسل همراه با 1 نکته طلایی
  • آموزش هایلایت سطر انتخاب شده در اکسل با 2 تابع
  • استخراج داده ها با چندین شرط به کمک Power Pivot
  • ایجاد لیست داده های یکتا به کمک Power Pivot در اکسل
  • تفاوت 2 تابع SUM و SUBTOTAL هراه با مثال و ترفند اکسلی
  • کاربرد فازی لوکاپ و برتری آن به Vlookup
  • آموزش رایگان 6 ترفند اکسلی که فقط حرفه ایی ها می دانند !
محصولات
  • محاسبه-کارکرد1401 نرم افزار محاسبه کارکرد سال1401 در اکسل
    390,000 تومان 280,000 تومان
  • حقوق-دستمزد-1401 نرم افزار پیشرفته محاسبه حقوق و دستمزد 1401
    390,000 تومان 290,000 تومان
  • نرم افزار پیشرفته حقوق و دستمزد 1400 نرم افزار پیشرفته حقوق و دستمزد 1400
    290,000 تومان
  • نرم افزار محاسبه کارکرد سال1400 نرم افزار محاسبه کارکرد سال1400
    260,000 تومان
  • نمودار سود و زیان به سبکی زیبا نمودار سود و زیان به سبکی زیبا
    50,000 تومان
  • ایجاد لیست کشویی جستجوگر اکسل ایجاد لیست کشویی جستجوگر اکسل
    70,000 تومان 35,000 تومان
  • نرم افزار پیشرفته حقوق و دستمزد نرم افزار پیشرفته حقوق و دستمزد
    160,000 تومان 149,000 تومان
  • نرم افزار محاسبه کارکرد سال99 نرم افزار محاسبه کارکرد سال99
    120,000 تومان 99,000 تومان
  • آموزش ساخت نمودار گیج (Gauge) آموزش ساخت نمودار گیج (Gauge)
    15,000 تومان
  • دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی دوره آموزش پایه ای برنامه نویسی در پاورکوئری و پاور بی آی
    نمره 5.00 از 5

    590,000 تومان
دسته‌های محصولات
  • آموزش پاور بی آی
  • دوره اکسل در حسابداری
  • دوره صفر تا صد اکسل
  • دوره گوگل شیت
  • دوره های رایگان
  • سایر
مطالب کمیاب در اکسل و حسابداری

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

تماس باما
  • آدرس: جنت آباد جنوبی، مجتمع سمرقند، طبقه ۸ ، واحد ۸۰۷
  • ۰۹۲۱۷۷۰۹۵۶۹
  • Info@arshad-hesabdar.ir

فهرست سفارشی
  • صفحه اصلی
  • قوانین و مقررات
تمامی حقوق برای سایت مطالب کمیاب در اکسل و حسابداری محفوظ می باشد.
اشتراک گذاری در شبکه های اجتماعی
ارسال به ایمیل
https://arshad-hesabdar.ir/?p=8788
لیست علاقه مندی ها 0
صفحه لیست علاقه مندی را باز کنید به خرید ادامه دهید
صفحه اینستاگرام
صفحه یوتیوب
صفحه اینستاگرام

اینستاگرام سافت ور

صفحه یوتیوب

کانال یوتیوب سافت ور

Instagram Youtube
مرورگر شما از HTML5 پشتیبانی نمی کند.

سوالی دارید؟ از ما بپرسید، کارشناسان ما در اسرع وقت با شما تماس می گیرند.

Logo

مطالب کمیاب در اکسل و حسابداری

  • ۰۹۲۱۷۷۰۹۵۶۹
  • شنبه تا چهارشنبه از ساعت 8 تا 17
  • Info@arshad-hesabdar.ir