جدید ترین عناوین خبری امروز
Registering new users is currently not allowed.
آموزش کاربردی و تصویری اکسل

آموزش کاربردی اکسل – معرفی Index و Match در نرم افزار اکسل با مثال تصویری

در این مقاله از مجله قرمز، به این موضوع می‌پردازیم که دو تابع Index و Match (ایندکس و مچ) در اکسل چه کاربردی دارند. اگر می‌خواهید نرم افزار اکسل را فرا بگیرید یا در آن حرفه‌ای تر شوید، Index و Match دو تابع مهم و پر کاربرد هستند که در این مطلب به معرفی آن‌ها می‌پردازیم. این دو تابع به همراه یکدیگر در نقشی شبیه Vlookup ظاهر می‌شوند و کار جستجو را انجام می‌دهند.

در این مطلب، ما ابتدا تابع Index و سپس تابع Match را به تنهایی توضیح می‌دهیم (برای جستجوی تک بخشی یا به عبارتی، به دنبال ۱ وجه گشتن) و در نهایت استفاده‌ی ترکیبی از دو تابع Index و Match (برای جستجوی دو بخشی یا به دنبال دو وجه گشتن) را برای شما بیان خواهیم کرد. با ما همراه باشید.

درباره توابع Index و Match

برای پیدا کردن یا جستجوی مقادیر در اکسل، علاوه بر تابع Vlookup که کاربرد زیادی دارد، دو تابع Index و Match نیز بسیار پر کاربرد هستند. تقریباً همه‌ی افرادی که با نرم افزار اکسل کار می‌کنند، چه در سطح مبتدی و چه پیشرفته، از این دو تابع به ناگزیر استفاده خواهند کرد. بنابراین، یادگیری این دو تابع می‌تواند کمک بسیار زیادی به فهم شما از اکسل و حرفه‌ای‌تر شدنتان در این حوزه بنماید. البته باید ذکر شود که تابع Vlookup به شما کمک می‌کند که جستجویتان را با فقط نوشتن همین تابع انجام دهید در حالی که دو تابع Index و Match برای جستجوی مقادیر مورد نظرتان، معمولاً به همراه همدیگر به کار می‌روند نه به صورت مجزا، که در این مطلب توضیحات آن را خواهیم داد.

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

معرفی تابع Index در نرم افزار اکسل

تابع یا فانکشن ایندکس (Index) در اکسل، بسیار جالب و پر کاربرد است و این تابع را در تعداد بسیار زیادی از فرمول‌هایی که با اکسل نوشته می‌شود به خصوص توابع پیشرفته – می‌توانید مشاهده کنید. اما، این تابع ایندکس دقیقاً چه کاری انجام می‌دهد؟

به صورت خلاصه، تابع Index در داده‌های شما در مکان مشخص – لیستتان و یا جدول – می‌گردد و مقادیر مورد نظر شما را برایتان پیدا می‌کند.

الف) جستجوی یک وجهی: برای مثال، فرض کنید شما در اکسل یک لیستی از کل سیاره‌هایی که در جهان شناخته شده‌اند نوشته‌اید و اکنون می‌خواهید نام چهارمین سیاره (مریخ) برای شما نشان داده شود و برای این کار، می‌خواهید یک فرمول بنویسید. با استفاده از فانکشن یا تابع Index، می‌توانید این کار را به راحتی به صورت زیر انجام بدهید:

معرفی تابع Index در نرم افزار اکسل

مشاهده کردید که تابع ایندکس، در بین محدوده‌ای که برای آن تعیین کردید جستجو می‌کند و چهارمین مقدار (یعنی مریخ) را برای شما نمایش می‌دهد.

ب) جستجوی دو وجهی: در مثال فوق، اگر می می‌خواستیم قطر سیاره‌ی مریخ را هم برای ما نمایش دهد چه باید می‌کردیم؟ در واقع هم می‌خواهیم چهارمین سیاره را برای ما پیدا کند و نام آن را نمایش بده و هم می‌خواهیم قطر متناظر با آن را نیز به ما ارائه دهد. در این مورد، ما باید به تابع Inedx هم یک عدد مشخص کننده‌ی جستجو در سطرها بدهیم (برای پیدا کردن چهارمین سیاره که مریخ است) و هم یک عدد برای جستجو در بین ستون‌ها (برای اینکه در ستون قطر، بگردد و قطر متناظر با مریخ را برای ما نمایش دهد).

مراحل را در شکل زیر مشاهده می‌کنید:

کاربرد ایندکس در اکسل

خلاصه‌ی کاربرد تابع یا فانکشن ایندکس (Index): با استفاده از تابع ایندکس، اکسل مقادیر مورد نظرمان را از مکان مشخصی (جدول یا لیست) بر حسب شماره‌ی سطر یا ستون جستجو می‌کند. وقتی جستجو در یک بُعد یا وجه است (حالت الف در بالا)، کافی است شماره‌ی سطر را وارد کنیم. اما وقتی جستجو در سطر و ستون است (حالت ب در بالا) باید هم عدد مربوط به سطر و هم عدد مربوط به ستون مورد نظر را وارد کنیم.

شاید شما هم با این سوال مواجه شده‌اید که این کارها برای چیست؟ آیا ما همیشه می‌دانیم که در جستجوی مقدار مورد نظرمان در سطر فلان و ستون فلان هستیم؟! البته که نه! و سوال شما کاملاً به جا است. باید راهی پیدا کرد که خودش مکانِ مقادیری که ما به دنبال آن هستیم را اتوماتیک پیدا کند و لازم نباشد ما به دنبال آن بگردیم. برای این کار، تابع Match استفاده می‌شود که توضیحات آن را در ادامه با هم مشاهده می‌کنیم. در آخر نیز همان طور که پیشتر اشاره شد، ترکیب دو تابع Index و Match را مشاهده خواهیم کرد. 

معرفی تابع Match در نرم افزار اکسل

تابع Match، برای یک هدف به وجود آمده است: این که جایگاه عددی یک آیتم در یک لیست را به شما بدهد. برای مثال، ما می‌توانیم در لیست زیر، از تابع Match استفاده کنیم تا به ما بگوید که کلمه‌ی “هلو” در چندمین سطر آمده است.

معرفی تابع Match در نرم افزار اکسل

همان طور که مشاهده می‌کنید، نرم افزار به ما گفت که کلمه‌ی “هلو” در سومین سطر (از بازه‌ای که انتخاب کردیم) مشاهده می‌شود.

نکته ۱: اگر به دنبال یافتن کلمه‌ای انگلیسی در لیست خودتان هستید، این نکته را هم خوب است بدانید که تابع Match نسبت به حروف کوچک و بزرگ حساس نیست.

نکته ۲: برای تابع یا فانکشن مچ (Match)، مهم نیست که لیست مقادیر شما ستونی نوشته شده یا سطری؛ اگر لیست شما به صورت سطری هم نوشته شده بود، باز هم نتیجه بدون تفاوت بود:

تابع مچ در اکسل، هم به صورت سطری و هم ستونی جستجو را انجام می‌دهد.

نکته ۳: در تابع Match، ابتدا Lookup Value، سپس Lookup array و بعد از آن Match Type دیده می‌شود که البته مورد سوم را می‌توانید انتخاب نکنید و کارتان را با همان دو مورد اول پیش ببرید. اما مورد سوم به چه کار می‌آید؟

Match Type در اکسل

مورد سوم یا همان Match Type، حائز اهمیت است و از شما می‌پرسد که آیا مقداری را که به دنبال آن است، دقیقاً همان چیزی باشد که شما برایش تعریف کرده‌اید و یا مشابهش هم می‌تواند باشد (Exact یا Approximate

منظور از ۱: تقریبی؛ تابع Match، بزرگ‌ترین آیتمی را که کوچکتر و یا مساوی با مقداری است که شما وارد کردید و در جستجوی آن هستید، برایتان نمایش می‌دهد. دامنه‌ی مقادیر در این حالت باید به ترتیب صعودی مرتب شود.

منظور از ۰: دقیقاً؛ تابع Match، اولین آیتمی را که دقیقاً برابر با مقداری است که شما وارد کردید، برایتان نمایش می‌دهد. در این حالت لازم نیست که لیست شما به صورت صعودی مرتب شده باشد.

منظور از ۱-: تقریبی؛ ابع Match، کوچک‌ترین آیتمی را که بزرگتر و یا مساوی با مقداری است که شما وارد کردید، برایتان نمایش می‌دهد. دامنه‌ی مقادیر در این حالت باید به ترتیب نزولی مرتب شود.

آموزش تصویری تابع مچ Match در اکسل

توجه داشته باشید که اگر Match Type را انتخاب نکنید، اکسل به صورت پیشفرض را برای شما در نظر می‌گیرد.

استفاده‌ی همزمان یا ترکیبی از دو تابع Index و  Match

اکنون که دو تابع Index و Match را به صورت مجزا بررسی کردیم و با آن‌ها آشنا شدیم، وقت آن رسیده که کاربرد ترکیب این دو تابع را بدانیم. چگونه می‌توانیم از دو تابع Index و Match، در یک فرمول واحد استفاده کنیم؟

داده‌های زیر را در نظر بگیرید. در جدول، میزان فروشِ شرکت‌های مختلف در سه ماه فروردین، اردیبهشت و خرداد نشان داده شده است.

آموزش اکسل کاربردی برای کارمندان

فرض کنید که ما می‌خواهیم فرمولی را بنویسیم که میزان فروش را در ماه اردیبهشت برای هر شرکت دلخواه به ما بدهد. بر طبق چیزهایی که تا به حال یاد گرفتیم، می‌دانیم که با استفاده از تابع INDEX می‌توانیم به این مقصود دست پیدا کنیم. برای مثال، برای این که میزان فروش در ماه اردیبهشت برای شرکت E را بدانیم، از این فرمول استفاده می‌کنیم:

آموزش گام به گام استفاده از دو تابع Index و  Match

همان طور که مشاهده می‌شود، ما از اکسل خواستیم که در محدوده داده‌های انتخاب شده، شرکت E را جستجو کند (پنجمین سطر در میان محدوده‌ی انتخابی) و ببیند که میزان فروش آن در ماه اردیبهشت (سومین ستون در میان محدوده‌ی انتخابی) چه قدر بوده است. می‌بینیم که عدد ۵۱۹۴ ریال به درستی نشان داده شد.

اما، ما به دنبال یک جستجوی دینامیک هستیم؛ به چه معنا؟ به این معنا که می‌خواهیم با تغییر دادن نام شرکت، اکسل به طور اتوماتیک میزان فروش هر شرکت در هر ماه” را برای ما بگردد و پیدا کند و نمایش دهد. برای این منظور، ابتدا باید به اکسل بفهمانیم که سطر را فقط سطر پنجم در نظر نگیرد و در تمامی سطرها جا به جا شود. در واقع با این کار می‌خواهیم دینامیک ایجاد کنیم. چگونه این کار را انجام بدهیم؟ از تابع Match استفاده می‌کنیم! J در زیر، فرمول اصلاح شده را می‌بینید که تابع Match در درون تابع Index دیده می‌شود. توجه داشته باشید که ما عدد ۵ را (نمایش دهنده‌ی سطر مربوط به شرکت E) با تابع Match جایگزین کردیم.

استفاده‌ی همزمان یا ترکیبی از دو تابع Index و Match

تا به اینجا، ما با وارد کردن تابع Match به درون تابع Index (به جای سطر ۵)، سطر را دینامیک کردیم؛ یعنی به اکسل فهماندیم که سطر ما به صورت ثابت ۵ نیست و تو باید در میان کلیه‌ی سطرها جا به جا شوی. اکنون می‌خواهیم به اکسل بفهمانیم که نام شرکت هم ثابت (E) نیست و باید در کل نام‌های شرکت جا به جا شوی و در میان آن‌ها بگردی. برای این کار، باید اکسل بفهمد که “هر نامی” وارد شد، به دنبال مشخصات خواسته شده برود. برای این کار، فرمول را به صورت زیر اصلاح می‌کنیم:  

آموزش اکسل حرفه ای- استفاده‌ی همزمان یا ترکیبی از دو تابع Index و  Match

در این صورت، تابع Match به سلول H4 نگاه می‌کند و نام هر شرکتی که در آنجا نوشته شده بود را در جدول می‌گردد و میزان فروش در ماه اردیبهشت برای آن شرکت را نمایش می‌دهد. با تغییر نام شرکت، میزان فروش هم اتوماتیک عوض می‌شود.

یادآوری: تابع Index، به دنبال مکان به صورت عددی و مشخص می‌گردد. تابع Match، آن مکان‌ها را پیدا می‌کند.

جستجوی دو جانبه با استفاده از دو تابع Index و Match

فرض کنید در مثال بالا، ما بخواهیم که جستجو کاملاً دینامیک باشد. به این معنا که نام هر شرکتی را که وارد کنیم، میزان فروش آن در تمامی ماه‌ها را به صورت مجزا نشان دهد (در بالا، میزان فروش را فقط در یک ماه اردیبهشت نمایش می‌داد). ترفندی که برای این کار وجود دارد، این است که از تابع Match دو بار استفاده کنیم. یک بار برای دینامیک کردن سطر (جا به جا شدن در سطرهای نام شرکت ها) و یک بار برای دینامیک کردن ستون (جا به جا شدن در ستون های نام ماه ها).

در این مطلب توضیح داده شد که تابع Match هم در میان مقادیر به صورت افقی جستجو می‌کند و هم به صورت عمودی. این بدان معناست که برای دینامیک کردن نام ماه‌ها، می‌توانیم از تابع Match استفاده کنیم تا به صورت افقی در بین ستون‌ها (ماه ها) جا به جا شود. برای مثال، اگر بخواهیم مکان ماه خرداد را برای ما پیدا کند، می‌نویسیم:

=Match (““خرداد,B2:D2,۰)

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

جستجوی دو جانبه با استفاده از دو تابع Index و Match

اکنون ما یک جستجوی دو جانبه و کاملاً دینامیک را داریم که با تغییر نام شرکت، یا ماه، می‌توانیم میزان فروش را مشاهده کنیم. بار دیگر به فرمول نهایی که در تصویر بالا قابل مشاهده است نگاه کنید. تابع Match اول، به جای سطر پنجم که در ابتدا نوشته بودیم و سطر نشان دهنده‌ی شرکت E بود و ثابت بود، نوشته شده است و تابع Match دوم، به جای ستون اردیبهشت که در ابتدا تعیین کرده بودیم نوشته شده و این قسمت را دینامیک می‌کند.

این مطلب از آموزش اکسل در مورد کاربرد توابع Index و Match نیز به پایان رسید. در آموزش‌های بعدی، توابع پر کاربرد دیگری را برای شما معرفی خواهیم کرد.

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

دوست عزیز، اگر هر گونه سوال و یا انتقاد و پیشنهادی در مورد آموزش اکسل در مجله قرمز دارید، می‌توانید در قسمت نظرات در پایین این مطلب با ما در میان بگذارید. همچنین اگر دوست دارید مطلبی را در مورد اکسل آموزش ببینید و نیاز به کمک دارید، در قسمت نظرات برای ما بنویسید تا مطلب آموزشی متناسب با نیاز شما را برایتان تهیه و در مجله قرمز قرار بدهیم. از این که همراه ما هستید سپاسگزاریم.

ترجمه و تالیف اختصاصی مجله قرمز

با کمک از سایت exceljet.net

دسته ها:
دیدگاه ها 0
2000
  Subscribe  
اعلام کردن به

مقالات زیبایی

رنگ‌های زیبای زمستانی برای آرایش ناخن‌های شما در این فصل
رنگ‌های زیبای زمستانی برای آرایش ناخن‌های شما در این فصل
83
با روش‌های جدید مراقبت پوستی در سال 2019 آشنا شوید
با روش‌های جدید مراقبت پوستی در سال ۲۰۱۹ آشنا شوید
209
لاک ناخن ماه تولد شما برای سال 2019 چه رنگی است؟
لاک ناخن ماه تولد شما برای سال ۲۰۱۹ چه رنگی است؟
277
چند ترفند آرایش و زیبایی برای صرفه جویی در وقت شما
چند ترفند آرایش و زیبایی برای صرفه جویی در وقت شما
258
ناخن‌های نقره‌ای یک سبک کلاسیک برای آرایش ناخن‌های شما
ناخن‌های نقره‌ای یک سبک کلاسیک برای آرایش ناخن‌های شما
198
بهترین و مناسب‌ترین مدل مو برای جنس موها کدام است؟
بهترین و مناسب‌ترین مدل مو برای جنس موها کدام است؟
452

مقالات سلامت

معرفی داروی پرازوسین
داروی پرازوسین و معرفی نکات کلیدی در مصرف این داروی فشار خون
99
سونوگرافی و انواع کاربردهای آن
سونوگرافی (ultrasound)؛ انواع کاربردها و آمادگی قبل از آن چیست؟
85
آنوریسم آئورت شکمی
آنوریسم آئورت شکمی ؛ علتها، علائم، تشخیص و درمان
212
تست TIBC آزمایش خون
تست TIBC آزمایش خون چیست و مقدار نرمال آن چقدر است؟
434
معرفی داروی یونسا
داروی یونسا و معرفی نکات کلیدی در مورد مصرف آن
231
گلو درد و بهترین غذاها در مواجهه با آن
گلو درد و بهترین مواد غذایی برای مصرف در هنگام ابتلای به این عارضه
414

مقالات آشپزی

ترافل ولنتاین
ترافل ولنتاین با ۷ نوع مختلف شکری، لیمویی و شکلاتی
95
نان مسطح خمیرترش
طرز تهیه چند نوع نان مسطح با خمیرترش و استارتر خمیرترش با آرد سبوسدار
171
طرز تهیه لوبیا
طرز تهیه لوبیا پلو با لوبیا سبز و گوشت چرخ کرده به صورت تصویری
455
حلوای لبو
حلوای لبو ؛ گرمابخش شیرین سرمای زمستان با گزینه‌های وگان
248
یک صبحانه کامل
شش خوراکی طلایی برای یک صبحانه کامل
598
انواع کباب
خوشمزه‌ترین انواع کباب در دنیا که طعمی به یاد ماندنی دارند
476

مقالات تغذیه

گلو درد و بهترین غذاها در مواجهه با آن
گلو درد و بهترین مواد غذایی برای مصرف در هنگام ابتلای به این عارضه
414
رژیم غذایی قلیایی
رژیم غذایی قلیایی چه فوائدی دارد و غذاهای قلیایی چه هستند؟
166
افزایش تعداد گلبولهای قرمز
افزایش تعداد گلبولهای قرمز با تغذیه و مکمل چگونه ممکن است؟
157
غذاهای مناسب فشار خون بالا و مواد غذایی مضر
غذاهای مناسب فشار خون بالا و مواد غذایی مضری که باید حذف شوند
172
پاکسازی روده بزرگ
پاکسازی روده بزرگ با بهترین غذاها و آنهایی که باید اجتناب کرد
595
غذاهایی برای جوانی پوست
۱۳ غذایی که بطور مستقیم بر جوانی پوست اثر گذار هستند
215