توابع کلیدی اکسل - تابع VLOOKUP

تابع VLOOKUP برای بازیابی اطلاعات از یک جدول براساس یک مقدار جستجو استفاده می‌شود، به این ترتیب که با جستجوی عبارتی در ستون اول جدول , مقدار مربوطه را از تقاطع ردیف آن و ستون مشخص شده باز می‌گرداند. VLOOKUP از تطبیق تقریبی و دقیق و از نویسه‌های جایگزین (* ?) برای مطابقت بخشی و جزئی پشتیبانی می‌کند.

     از کجا شروع کنم      

هدف تابع

جستجو و بازیابی یک مقدار براساس آیتمی از جدول

خروجی تابع

مقداری از ستون جدول که شماره آن ستون مشخص شده

ترکیب عمومی تابع

=  VLOOKUP ( lookup_value ,t able_array , column_index_num , [range_lookup] )

ورودی تابع (مولفه‌ها‌)

lookup_value – مقداری که می‌خواهیم از ستون اول جدول پیدا کنیم.
table_array– محدوده جدولی که می خواهید جستجو انجام شود
column_index_num – شماره ستونی در جدول که می‌خواهیم جواب از آن ستون باشد.
range_lookup – [اختیاری] در این مولفه تعیین می‌کنید که اگر تابع مقدار مورد نظر را پیدا نکرد چه کند ، آیا نزدیکترین مقدار را به ترتیب نزولی نتیجه دهد یا اینکه نه دقیقا همان مورد را جستجو کند. برای نزدیکترین مقدار ، True و برای مقدار دقیق ، False را وارد کنید. مقدار پیش‌فرض برای این مولفه (اگر آن را خالی بگذارید) True می‌باشد.

نحوه استفاده از تابع

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

ویژگی‌های کلیدی

• مقادیر موجود در ستون اول یک جدول عمودی را جستجو می‌کند
• داده‌ها را از هر ستونی که در سمت راست ستون جستجو قرار دارد، برمی‌گرداند
• از حالت‌های تطبیق دقیق و نزدیکترین تطبیق پشتیبانی می‌کند
• نزدیکترین تطبیق پیش‌فرض است (اگر تطبیق دقیق مورد انتظار باشد، خطرناک است)
• از نویسه‌های جایگزین (* ?) برای تطبیق جزئی پشتیبانی می‌کند (فقط در حالت تطبیق دقیق)
• به حروف کوچک و بزرگ لاتین حساس نیست
• در صورت وجود مقادیر تکراری، اولین مطابقت را بر می‌گرداند
• در تمام نسخه‌های اکسل کار می‌کند

مهم: VLOOKUP به طور پیش‌فرض یک تطبیق تقریبی انجام می‌دهد. 

VLOOKUP با داده‌های عمودی کار می‌کند. "V" در VLOOKUP به معنای " Vertical " -عمودی- است. هدف VLOOKUP جستجو و بازیابی اطلاعات در جدولی است که به صورت عمودی سازماندهی شده است:

برای مثال، با جدول بالا، می‌توانید از VLOOKUP برای یافتن مبلغ یک سفارش مشخص به صورت زیر استفاده کنید:

با شماره سفارش ۱۰۰۵ به عنوان مقدار جستجو در سلول H3، نتیجه ۱۲۵ می‌شود. VLOOKUP ستون اول جدول را اسکن می‌کند، مقادیر ستون را با شماره سفارش ۱۰۰۵ مطابقت می‌دهد و مبلغ مربوط به این شماره سفارش را از ستون مبلغ ، یعنی مقدار۱۲۵ بر می‌گرداند. فرمول سلول I3 به شرح زیر است:

= VLOOKUP ( H3 , B2:F7 , 5 , FALSE )

فعلاً تنها به این موارد توجه کنید:
1. مقدار جستجو به صورت H3 و آدرس سلول ارائه می‌شود. اگر این مقدار تغییر کند، VLOOKUP نتیجه جدیدی را بر می‌گرداند.
2. جدول جستجو به صورت محدوده B2:F7 ارائه شده است که شامل کل جدول است.
3. مقادیر جستجو (شماره‌های سفارش) در ستون اول جدول قرار دارند.
4. مقادیر نتیجه (مبالغ سفارش) در ستون پنجم جدول قرار دارند.
5. شماره ستون به صورت مقدار عددی (5) داده شده است.

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

VLOOKUP از شماره ستون‌ها استفاده می‌کند

تابع VLOOKUP از شماره ستون‌ها برای نشان دادن ستونی که باید از آن مقدار بازیابی شود استفاده می‌کند. وقتی از VLOOKUP استفاده می‌کنید، تصور کنید که هر ستون جدول شماره‌گذاری شده و از 1 شروع می‌شود:

برای بازیابی یک مقدار از یک ستون مشخص، کافیست شماره ستون را در مولفه column_index_num وارد کنید. برای مثال ، برای بازیابی نام در سلول H3، همانطور که در تصویر مشاهده شد، از عدد ۲ برای column_index_num استفاده می‌کنیم. با تغییر تنها شماره ستون ، می‌توانیم با درخواست ستون‌های ۲، ۳ و ۴، نام، نام خانوادگی و استان محل سکونت را بازیابی کنیم:

= VLOOKUP ( H2 , B2:E7 , 2 , FALSE )   //   نام
= VLOOKUP ( H2 , B2:E7 , 3 , FALSE )   //   نام خانوادگی
= VLOOKUP ( H2 , B2:E7 , 4 , FALSE )   //   استان محل سکونت

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

VLOOKUP فقط به سمت راست نگاه می‌کند

VLOOKUP فقط می‌تواند به سمت راست نگاه کند. به عبارت دیگر، فقط می‌توانید داده‌های سمت راست اولین ستون جدول ارائه شده به VLOOKUP را بازیابی کنید. به عنوان مثال، برای جستجوی اطلاعات بر اساس شماره پرسنلی در جدول زیر، باید محدوده D2:G7 را به عنوان جدول ارائه دهیم ، و این بدان معناست که تنها می‌توانیم استان ، بخش و ایمیل را جستجو کنیم:

این یک محدودیت اساسی برای VLOOKUP است - ستون اول جدول باید حاوی مقادیر جستجو باشد و VLOOKUP فقط می‌تواند به ستون‌های سمت راست دسترسی داشته باشد. برای بازیابی اطلاعات در سمت چپ (یا راست) یک ستون جستجو، می‌توانید از تابع XLOOKUP یا ترکیب توابع INDEX و MATCH استفاده کنید.

تابع XLOOKUP نسخه بهبود یافته تابع VLOOKUP است که برای کاربران اکسل ۳۶۵ در دسترس است.

VLOOKUP دو حالت تطبیق دارد

VLOOKUP دو حالت تطبیق دارد: تطبیق دقیق و تطبیق تقریبی. آخرین مولفه که range_lookup نام دارد، حالت تطبیق را کنترل می‌کند. کلمه "range" در این مولفه به "محدوده مقادیر" اشاره دارد - وقتی range_lookup برابر با TRUE باشد ، VLOOKUP به جای یک مقدار دقیق ، نزدیکترین مطابقت را باز می‌گرداند. وقتی range_lookup برابر با FALSE باشد، VLOOKUP فقط یک تطبیق دقیق را مجاز می‌داند:

= VLOOKUP ( شماره ستون , محدوده جدول , مقدار جستجو ,TRUE )   //   نزدیکترین تطبیق 
= VLOOKUP ( شماره ستون , محدوده جدول , مقدار جستجو , FALSE )   //   تطبیق دقیق 

نکته حرفه‌ای: همچنین می‌توانید برای تطبیق دقیق، عدد صفر (0) و برای نزدیکترین تطبیق ، عدد 1 را وارد کنید. اکسل عدد 0 را به عنوان FALSE و عدد 1 را به عنوان TRUE ارزیابی می‌کند، بنابراین 0 و 1 معادل‌های فشرده‌تری هستند.

مثال تطبیق دقیق VLOOKUP

در بیشتر موارد، احتمالاً می‌خواهید از VLOOKUP در حالت تطبیق دقیق استفاده کنید. این حالت زمانی منطقی است که یک مورد منحصر به فرد برای استفاده به عنوان مقدار جستجو داشته باشید، به عنوان مثال، عنوان فیلم در این داده‌ها:

فرمول موجود در H4 برای یافتن سال تولید ، بر اساس تطبیق دقیق عنوان فیلم ، به صورت زیر است:

= VLOOKUP (H2 , B3:E7 , 2 , FALSE )   //   FALSE = تطبیق دقیق

VLOOKUP بر اساس عبارت "داستان اسباب‌بازی" در H2 یک مطابقت در ردیف چهارم جدول پیدا می‌کند و در نتیجه عدد ۱۹۹۵ را بر می‌گرداند.

مثال نزدیکترین تطبیق VLOOKUP

در برخی موارد، به جای مطابقت دقیق ، به نزدیکترین مطابقت نیاز خواهید داشت. اگر می‌خواهید مقداری را از یک بازه عددی بدست آورید انتخاب True برای مولفه چهارم مناسب است. در برگه زیر، می‌خواهیم از امتیازت ستون C برای اختصاص نمره با استفاده از جدول کلید سمت راست در محدوده F3:G7 که "key" نامگذاری شده است ، استفاده کنیم. در اینجا، باید از VLOOKUP در حالت تطبیق تقریبی استفاده کنیم ، زیرا در بیشتر موارد ، امتیازات دقیق ستون F در ستون C وجود ندارد. فرمول VLOOKUP در سلول D3 طوری پیکربندی شده است که با تنظیم آخرین مولفه روی TRUE نزدیکترین مطابقت را انجام دهد:

= VLOOKUP ( C3 , $F$3:$G$7 , 2 , TRUE )   //   TRUE = نزدیکترین مطابقت

همانطور که فرمول کپی می‌شود، VLOOKUP مقادیر ستون F را برای اختصاص نمره در ستون D براساس امتیازات ستون C اسکن می‌کند. اگر یک مطابقت دقیق پیدا شود ، VLOOKUP نمره مربوطه در آن ردیف را بر می‌گرداند. اگر مطابقت دقیق پیدا نشود ، VLOOKUP در اولین امتیاز بزرگتر متوقف می‌شود، سپس "به عقب برمی‌گردد" و نمره را از ردیف قبلی بر می‌گرداند.

احتیاط: برای تطبیق تقریبی، جدول ارائه شده به VLOOKUP باید به ترتیب صعودی بر اساس ستون اول مرتب شود. اگر جدول مرتب نشده باشد، VLOOKUP ممکن است نتایج نادرست یا غیرمنتظره‌ای را برگرداند، این مساله در بخش بعدی توضیح داده شده است.

VLOOKUP پیش‌فرض‌های خطرناکی دارد

مهم است است به خاطر داشته باشید که VLOOKUP به طور پیش‌فرض یک تطبیق تقریبی (نزدیکترین مطابقت) انجام می‌دهد ، زیرا مولفه range_lookup اختیاری است و به طور پیش‌فرض روی TRUE تنظیم شده است. اگر انتظار یک تطبیق دقیق داشته باشید، همانطور که در برگه زیر مشاهده می‌شود، می‌تواند مشکلات بزرگی ایجاد کند. 

در این مثال ، هدف جستجوی مبلغ فاکتور مشخص شده در سلول H3 است. فرمول I3 به صورت زیر است:

= VLOOKUP ( H3 , B2:F7 , 5 )   //   به طور پیش فرض تطبیق تقریبی است

خلاصه: اگر مقداری برای range_lookup وارد نکنید، VLOOKUP یک تطبیق تقریبی انجام می‌دهد. در این حالت ، VLOOKUP می‌تواند نتایجی را برگرداند که به نظر درست می‌آیند اما در واقع کاملاً اشتباه هستند. به همین دلیل، توصیه می‌کنم همیشه مقداری برای range_lookup تعیین کنید تا یادآوری باشد که چه انتظاری دارید.

VLOOKUP اولین مطابقت را بر می‌گرداند

اگر موارد تکراری در ستون جستجو جدول وجود داشته باشد، VLOOKUP فقط اولین مطابقت را پیدا می‌کند. در برگه زیر، VLOOKUP طوری پیکربندی شده است که قیمت رنگ "سبز" را دریافت کند. سه ردیف برای رنگ سبز وجود دارد و VLOOKUP قیمت ردیف اول را که 17.00 دلار است ، بر می‌گرداند. فرمول سلول F4 به شرح زیر است:

= VLOOKUP ( E4 , B4:C10 , 2 , FALSE )   

نکته: برای بازیابی آخرین مطابقت در مجموعه‌ای از داده‌ها ، می‌توانید از تابع XLOOKUP که برای انجام "جستجوی معکوس" پیکربندی شده است، استفاده کنید. برای بازیابی همه مطابقت‌ها، از تابع FILTER استفاده کنید.

VLOOKUP و تطبیق جزئی با نویسه‌های جایگزین

تابع VLOOKUP از نویسه‌های جایگزین پشتیبانی می‌کند که امکان انجام تطبیق جزئی روی یک مقدار جستجو را فراهم می‌کند. برای استفاده از نویسه‌های جایگزین با VLOOKUP باید مقدار FALSE یا صفر (0) را برای range_lookup وارد کنید. در برگه زیر، فرمول H4 پس از تایپ "علمداری" در سلول H2 ، نام "محمد" را بازیابی می‌کند. توجه داشته باشید که نویسه جایگزین ستاره (*) به مقدار جستجو در داخل فرمول VLOOKUP متصل شده است:

= VLOOKUP ( "*" & H2 , B3: E17, 2 , FALSE )

VLOOKUP و جستجوی دوطرفه

داخل تابع VLOOKUP، شماره ستون (column_index_num) معمولاً به صورت یک عدد ثابت درج می‌شود. با این حال، می‌توانید با استفاده از تابع MATCH برای یافتن ستون مورد نظر ، استفاده کنید. این تکنیک امکان می‌دهد یک جستجوی دوطرفه پویا ایجاد کنید که هم در ردیف‌ها و هم در ستون‌ها جستجو می‌کند. در برگه زیر، VLOOKUP طوری پیکربندی شده است که جستجو را بر اساس نام و ماه با استفاده از فرمول زیر انجام دهد:

= VLOOKUP ( H3 , B4:E8 , MATCH ( H4 , B3:E3 , 0 ) , 0  )

تابع MATCH، موقعیت "اردیبهشت" را در محدوده B3:E3 تعیین می‌کند و عدد 3 را به عنوان شماره ستون به VLOOKUP بر می‌گرداند. 

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

VLOOKUP با معیارهای چندگانه

تابع VLOOKUP به طور ذاتی معیارهای چندگانه را مدیریت نمی‌کند. با این حال ، می‌توانید از یک ستون کمکی برای اتصال چندین فیلد به یکدیگر استفاده کنید و از این فیلدها به عنوان معیارهای چندگانه در داخل VLOOKUP استفاده کنید. در مثال زیر، ستون B یک ستون کمکی است که نام و نام خانوادگی را با کمک  فرمول زیر به هم پیوند می‌دهد:

= C3 & D3   //   ستون کمکی

تابع VLOOKUP طوری پیکربندی شده است که همین کار را برای ایجاد یک مقدار جستجو انجام دهد. فرمول H6 به صورت زیر است:

=VLOOKUP ( H2 & H3 , B3:E17 , 4 , 0 )

توجه: INDEX و MATCH و XLOOKUP برای جستجوهایی که بر اساس چندین معیار انجام می‌شوند، مناسب هستند. به مثال زیر توجه کنید:

ترکیب INDEX + MATCH جایگزین  VLOOKUP

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

تابع INDEX و MATCH امکان می‌دهد که هر ستون دلخواه جدول را جستجو کنید. این فرمول بسیار متنوع است و من ترجیح می‌دهم از INDEX + MATCH به جای VLOOKUP استفاده کنم.

فرمول سلول H3 :

= INDEX ( E3:E15 , MATCH ( G3 , B3:B15 , 0 ) )

تابع MATCH مقدار مورد نظر (AA-1611) را در ستون مشخص شده (محدوده سلولی B3:B15) جستجو کرده و موقعیت نسبی آن (ردیف 11 ستون) را باز می‌گرداند. سپس تابع INDEX مقدار ردیف 11 ستون مشخص شده (محدوده سلولی E3:E15)  را به عنوان نتیجه جستجو باز می‌گرداند.

مدیریت خطاهایVLOOKUP 

اگر از VLOOKUP استفاده کنید، ناگزیر با خطای N/A # مواجه خواهید شد. خطای N/A # به عبارت ساده به معنای "یافت نشد" است. به عنوان مثال ، در برگه زیر، مقدار جستجو "داستان اسباب‌بازی ۲" در ستون جستجو 0ستون اول جدول) وجود ندارد و هر سه فرمول VLOOKUP مقدار N/A # را بر می‌گرداند:

خطای N/A # مفید است زیرا به شما می‌گوید که چیزی اشتباه است. دلایل مختلفی وجود دارد که چرا VLOOKUP ممکن است خطای N/A # را بر گرداند ، از جمله:

• مقدار جستجو در جدول وجود ندارد
• مقدار جستجو اشتباه تایپ شده یا حاوی فاصله اضافی است
• حالت تطبیق دقیق است، اما باید تقریبی باشد
• محدوده جدول به درستی وارد نشده است
• فرمول کپی شده است و آدرس جدول قفل نبوده است

برای مدیریت خطای NA و بازگرداندن یک مقدار دلخواه ، می‌توانید از تابع IFNA به صورت زیر استفاده کنید:

فرمول سلول H4 :

 = IFNA ( VLOOKUP ( H2 , B3:E7 , 2 , FALSE )  , ” پیدا نشد " )

این پیام را می‌توان به دلخواه تنظیم کرد. برای اینکه وقتی VLOOKUP مقدار N/A # را بر می‌گرداند، چیزی  نمایش داده نشود ، می‌توانید از یک رشته خالی ("") به صورت زیر استفاده کنید.

= IFNA ( VLOOKUP ( H2 , B3:E7 , 2 , FALSE )  , ” " )

همچنین می‌توانید از تابع IFERROR برای مدیریت خطای VLOOKUP  استفاده کنید. با این حال ، در استفاده از IFERROR احتیاط کنید، زیرا نه تنها خطای N/A # بلکه خطاهای دیگر را هم مدیریت کرده و نشان نمی‌دهد. 

نکات عملکردی VLOOKUP

VLOOKUP در حالت تطبیق دقیق ، در جدول داده‌های بزرگ می‌تواند کند باشد. از سوی دیگر ، در حالت تطبیق تقریبی بسیار سریع است ، اما اگر به یک تطبیق دقیق نیاز دارید خطرناک است زیرا ممکن است مقدار نادرستی را برگرداند. در نسخه‌ مدرن اکسل ، ساده‌ترین راه حل، استفاده از تابعی مانند XLOOKUP است که از جستجوی دو طرفه فوق‌العاده سریع پشتیبانی می‌کند. همچنین می‌توانید از INDEX و XMATCH استفاده کنید، زیرا XMATCH گزینه جستجوی دو طرفه سریع را نیز دارد. اگر در نسخه قدیمی اکسل بدون XLOOKUP یا XMATCH گیر کرده‌اید، گزینه دیگر ایجاد یک فرمول تطبیق دقیق سریع با دو بار استفاده از VLOOKUP است. این یک ترفند جالب است، اما من از این رویکرد استفاده نمی‌کنم مگر اینکه تنها گزینه شما باشد. من همچنین از استفاده از آدرس کل ستون‌ ها مانند A:A در VLOOKUP خودداری می‌کنم. از آنجایی که یک فایل اکسل حاوی بیش از ۱ میلیون ردیف است ، استفاده از آدرس کل ستون‌ ها می‌تواند در موارد خاص با پردازش میلیون‌ها سلول اضافی توسط اکسل ، مشکلات عملکردی بزرگی ایجاد کند.

نکات

• VLOOKUP به طور پیش‌فرض یک تطبیق تقریبی انجام می‌دهد.
• VLOOKUP به حروف کوچک و بزرگ حساس نیست.
• Range_lookup تطبیق را کنترل می‌کند. FALSE = دقیق ، TRUE یا حذف شود = تقریبی (پیش‌فرض).
• VLOOKUP نزدیکترین مقدار مساوی یا کمتر از مقدار lookup_value را باز می‌گرداند.
•  ستون اول جدول مشخص شده در مولفه table_array باید به ترتیب صعودی مرتب شود.
•  برای یک تطبیق دقیق مقدار range_lookup صفر یا FALSE باشد.
• VLOOKUP یک تطبیق دقیق انجام می‌دهد ، نیازی به مرتب‌سازی table_array نیست.

لطفا دیدگاه خود را در کادر پایین با ما به اشتراک بگذارید.

آیا می‌خواهید:

• برنامه‌های قدرتمند و انعطاف پذیر با اکسل ایجاد کنید.
• فرصت های شغلی جدید کسب کنید.

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