ویلوکاپ در اکسل چیست؟ | آموزش تابع VLOOKUP با مثال

ویلوکاپ در اکسل چیست؟

VLOOKUP که مخفف عبارت Vertical Lookup به معنی «جست‌وجوی عمودی» است، یکی از پرکاربردترین توابع جست‌وجو در اکسل محسوب می‌شود و به‌ویژه برای افرادی که در ابتدای مسیر کار با مجموعه‌داده‌های بزرگ هستند، بسیار کاربردی است. این تابع به شما امکان می‌دهد مقداری را در یک ستون جست‌وجو کنید و مقدار متناظر با آن را از ستون دیگری در همان ردیف برگردانید.

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

پیام اسفندیاری

علوم داده

وبینار معرفی مسیر شغلی تحلیل‌گر داده

۴,۰ (۴۵ رای) ۱۵۹۶ نفر ۷۴ ساعت و ۴ دقیقه

رایگان

بیشتر

VLOOKUP چه کاربردی دارد؟

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

ساختار تابع VLOOKUP در اکسل

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

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

در ادامه، ساختار این تابع و کاربرد هر یک از پارامترهای آن را بررسی می‌کنیم.

Lookup_value

Lookup_value مقداری است که می‌خواهید جست‌وجو کنید. این مقدار باید در اولین ستون محدوده سلول‌هایی قرار داشته باشد که در پارامتر table_array مشخص کرده‌اید. برای مثال، اگر محدوده table_array شامل سلول‌های B2:D7 باشد، مقدار مورد جست‌وجو (lookup_value) باید در ستون B قرار داشته بگیرد. lookup_value می‌تواند یک مقدار مشخص یا مرجع یک سلول باشد.

Table_array

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

Col_index_num

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

Range_lookup

این بخش تابع ویلوکاپ در اکسل مشخص می‌کند که اگر فرمول نتواند مقدار موردنظر را به‌صورت دقیق پیدا کند، چه نتیجه‌ای برمی‌گرداند. اگر این آرگومان را روی TRUE تنظیم کنید، در صورت پیدا نشدن تطابق دقیق، یک تطابق تقریبی ارائه می‌شود؛ اما اگر آن را روی FALSE قرار دهید، در صورت پیدا نشدن تطابق دقیق، فرمول خطا برمی‌گرداند.

 آموزش VLOOKUP در اکسل با مثال

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

گام اول: آماده‌سازی داده‌ها

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

گام دوم: وارد کردن فرمول VLOOKUP در اکسل

سلولی را انتخاب کنید که می‌خواهید قیمت محصول پس از وارد کردن شناسه محصول در آن نمایش داده شود.

• انتخاب سلول برای نمایش نتیجه: روی سلولی که می‌خواهید نتیجه تابع VLOOKUP در آن نمایش داده شود کلیک کنید. در این مثال، سلول D2 را انتخاب می‌کنیم.
• شروع فرمول: در سلول D2 عبارت زیر را وارد و فرمول را با استفاده از مقدار جست‌وجو از یک سلول دیگر، برای مثال A2، تکمیل کنید.

=VLOOKUP(

گام سوم: تعیین مقدار جست‌وجو

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

می‌توانید از آدرس یک سلول مانند A2 کمک بگیرید یا مقدار موردنظر را مستقیما داخل علامت ” ” وارد کنید. برای مثال “۰۰۱”.در این مثال، اگر می‌خواهید شناسه محصول را جست‌وجو کنید، می‌توانید فرمول را به یکی از شکل‌های زیر شروع کنید:

=VLOOKUP(A2;

=VLOOKUP("001";

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

گام چهارم:  تعیین محدوده جدول (Table Array)

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

محدوده داده‌ای را که می‌خواهید جست‌وجو در آن انجام شود انتخاب کنید. برای مثال، A1:C4 که شامل شناسه‌ها در ستون A و قیمت‌ها در ستون C است. برای انتخاب محدوده، روی اولین سلول کلیک کنید و نشانگر ماوس را روی سلول‌های موردنظر بکشید. یا محدوده را به صورت اولین آخرین سلول:اولین سلول مشخص کنید. پس از انتخاب محدوده جدول، یک ویرگول (,) یا نقطه‌ویرگول (;) قرار دهید تا به بخش بعدی فرمول بروید.

گام پنجم: تعیین شماره ستون (Column Index Number)

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

=VLOOKUP(D2;A1:C4;3;

پس از وارد کردن شماره ستون، یک ویرگول (,) یا نقطه‌ویرگول (;) قرار دهید تا به بخش بعدی فرمول بروید.

آرگومان col_index_num در تابع VLOOKUP مشخص می‌کند که پس از پیدا شدن مقدار موردنظر در اولین ستون آرایه جدول، اطلاعات از کدام ستون table_array بازگردانده شود. در این مثال، شماره ستون ۳ به اکسل می‌گوید که پس از پیدا کردن شناسه محصول در ستون اول، مقدار مربوط به آن را از ستون سوم، یعنی ستون قیمت‌ها برگرداند. به این ترتیب، VLOOKUP اطلاعات موردنظر را از ستون صحیح استخراج می‌کند.

علی سعیدی

علوم داده

کارگاه داستان‌سرایی داده

(کمتر از ۵ رای) ۱۵ نفر نامشخص

بیشتر

گام ششم: انتخاب نوع تطابق (Range Lookup)

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

• تطابق دقیق یا FALSE: فقط زمانی نتیجه را برمی‌گرداند که مقدار موردنظر دقیقا در جدول وجود داشته باشد. در غیر این صورت، خطای #N/A نمایش داده می‌شود. این گزینه برای جست‌وجوهای دقیق، مانند شناسه محصولات یا قیمت‌ها، گزینه مناسبی است.
• تطابق تقریبی یا TRUE: برای جست‌وجوی تقریبی استفاده می‌شود و معمولا زمانی کاربرد دارد که داده‌ها به ترتیب مناسب، مانند ترتیب صعودی، مرتب شده باشند.

گام هفتم: تکمیل و اجرای فرمول ویلوکاپ در اکسل

در این مرحله، فرمول VLOOKUP را تکمیل و نتیجه را در جدول مشاهده می‌کنیم. این مرحله نشان می‌دهد که فرمول به‌درستی تنظیم شده باشد و اطلاعات موردنظر را نمایش می‌دهد.

کلید Enter را فشار دهید.

فرمول کامل در سلول D2 باید به شکل زیر باشد:

=VLOOKUP(A2;A1:C4;3;FALSE)

نتیجه را مشاهده کنید.

پس از فشردن Enter، سلول D2 باید مقدار ۵۰۰۰۰ را نمایش دهد. این مقدار، قیمت محصولی با شناسه ۰۰۱ است.

ویلوکاپ بین دو شیت اکسل

به کمک فرمول ویلوکاپ در اکسل می‌توانید اطلاعات دو شیت متفاوت را بررسی کنید. برای مثال فرض کنید دو شیت دارید. در sheet1 شناسه کارمندان و نام آن‌ها نوشته شده است.

و در sheet2 شناسه کارمندان و ایمیل آن‌ها نوشته شده است.

گام اول: تنظیم VLOOKUP در Sheet1

به Sheet1 با نام اطلاعات کارکنان بروید و سلول C2 را که در کنار اولین شناسه کارمند قرار دارد، انتخاب کنید.

گام دوم: وارد کردن فرمول ویلوکاپ در اکسل

فرمول زیر را در سلول C2 در Sheet1 وارد کنید:

=VLOOKUP(A2;Sheet2!A:B;2;FALSE)

در ادامه، هر بخش از این فرمول را به زبان ساده توضیح می‌دهیم:

• A2: به سلول A2 در Sheet1 اشاره دارد که شناسه کارمند موردنظر در آن قرار گرفته است.
• Sheet2!A:B: به اکسل می‌گوید که جست‌وجو را در ستون‌های A و B در Sheet2 انجام دهد. علامت ! نشان می‌دهد که داده‌ها در یک صفحه دیگر قرار دارند.
• ۲: مشخص می‌کند که نتیجه موردنظر، یعنی آدرس ایمیل، در ستون دوم محدوده انتخاب‌شده در Sheet2 قرار دارد.
• FALSE: کمک می‌کند VLOOKUP فقط یک تطابق دقیق برای شناسه کارمند پیدا کند.

مرحله ۳: اجرای فرمول و کپی کردن آن

حالا فرمول VLOOKUP را اجرا و آن را برای سایر ردیف‌ها نیز کپی می‌کنیم تا اطلاعات تمام کارکنان به‌صورت خودکار از Sheet2 دریافت شود. پس از وارد کردن فرمول در سلول C2، کلید Enter را فشار دهید. در این حالت، برای شناسه کارمند ۱۰۱، نتیجه akbari@email.com نمایش داده می‌شود.

مربع کوچک گوشه پایین سمت راست سلول را بگیرید و به سمت پایین بکشید تا فرمول در سلول‌های دیگر ستون C نیز کپی شود.

استفاده از ویلوکاپ بین دو فایل اکسل

می‌توانید با فرمول ویلوکاپ در اکسل اطلاعات دو فایل اکسل متفاوت را بررسی کنید.

گام اول: باز کردن هر دو فایل

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

در این مثال فایل اطلاعات کامندان را با اطلاعات فروش مقایسه می‌کنیم.

گام دوم: تنظیم VLOOKUP در فایل اول

حالا باید فایل اصلی را برای استفاده از VLOOKUP بسازید. ابتدا سلول موردنظر را انتخاب کنید تا اطلاعات فایل دوم در آن نمایش داده شود.

به Workbook1 با نام فروش بروید و سلول C2 در کنار اولین شناسه تراکنش را انتخاب و فرمول زیر را وارد کنید.

=VLOOKUP(A2;'[اطلاعات کارمندان.xlsx]Sheet1'!$A$1:$B$4;2;FALSE)

در ادامه، هر بخش از این فرمول را به زبان ساده توضیح می‌دهیم:

• A2: مقدار موردنظر برای جست‌وجو (Lookup Value) است. در این مثال، شناسه تراکنش در فایل Sales Data.xlsx قرار دارد.
• ‘[Employee Sales.xlsx]Sheet1’!$A$1:$B$3: محدوده جدول در فایل دیگر است. علامت $ باعث می‌شود این محدوده هنگام کپی کردن فرمول ثابت باقی بماند.
• ۲: شماره ستونی را مشخص می‌کند که می‌خواهیم نتیجه از آن استخراج شود؛ در این مثال، نام کارمند در ستون دوم قرار دارد.
• FALSE: مشخص می‌کند که VLOOKUP باید فقط تطابق دقیق برای شناسه تراکنش پیدا کند.

پس از وارد کردن فرمول در سلول C2، کلید Enter را فشار دهید. اگر فرمول را به‌درستی وارد کرده باشید، سلول C2 برای شناسه تراکنش T001 نام اکبری را نمایش می‌دهد. مربع کوچک گوشه پایین سمت راست سلول را بگیرید و به سمت پایین بکشید تا فرمول در سلول‌های دیگر ستون C نیز کپی شود.

 کپی کردن فرمول ویلوکاپ برای سلول‌های دیگر

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

امیر هنرمند

علوم داده

هوشمند سازی کسب و کار با ابزار Microsoft Power BI

۴,۰ (۱۸۸ رای) ۱۳۱۶ نفر ۵۰۸ ساعت و ۵۴ دقیقه

بیشتر

VLOOKUP برای جست‌وجوی دقیق و تقریبی

در بیشتر موارد، هنگام استفاده از تابع VLOOKUP از تطابق دقیق استفاده می‌شود. به‌ویژه زمانی که مقدار جست‌وجو یک مقدار منحصربه‌فرد باشد. از آنجا که مقدار موردنظر برای جست‌وجو در سمت چپ‌ترین ستون جدول قرار دارد، در این مثال از VLOOKUP با حالت تطابق دقیق استفاده می‌کنیم.

در این مثال، VLOOKUP باید شناسه فروش T102 یا مقدار موجود در سلول E2 را در سمت چپ‌ترین ستون آرایه جدول یعنی محدوده A:C جست‌وجو کند.

از آنجا که مقدار موردنظر برای بازگرداندن در ستون سوم جدول قرار دارد، عدد ۳ را وارد می‌کنیم تا به اکسل بگوییم مقدار موجود در همان ردیف و در ستون سوم آرایه جدول را برگرداند.

با وارد کردن FALSE به‌عنوان آرگومان چهارم، به اکسل اعلام می‌کنیم که فقط تطابق دقیق را جست‌وجو کند. در نتیجه، اکسل مقدار ۲۰۰ را برمی‌گرداند که مقدار متناظر صحیح برای شناسه فروش T102 است.

اگر مقدار موردنظر برای جست‌وجو در سمت چپ‌ترین ستون جدول وجود نداشته باشد، اکسل خطای #N/A را نمایش می‌دهد؛ زیرا مقدار موردنظر پیدا نشده است.

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

برای مثال، فرض کنید درآمد سالانه ۳۹۰۰۰ در سمت چپ‌ترین ستون جدول وجود ندارد. در این شرایط، با استفاده از TRUE می‌توانیم از اکسل بخواهیم نزدیک‌ترین مقدار را پیدا کند. از آنجا که مقدار ۳۹۰۰۰ در جدول وجود ندارد، اکسل بزرگ‌ترین مقدار کمتر از ۳۹۰۰۰ را در نظر می‌گیرد که در این مثال ۳۰۰۰۰ دلار است و نرخ مالیات متناظر با آن، یعنی ۸ درصد را برمی‌گرداند. که به‌صورت ۰/۰۸ نشان داده شده است.

اگر در این حالت از FALSE استفاده کنیم، چون مقدار ۳۹۰۰۰ در سمت چپ‌ترین ستون جدول وجود ندارد، اکسل خطای #N/A را نمایش می‌دهد.

خطاهای رایج در VLOOKUP

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

خطای N/A#

یکی از دلایل رایج کار نکردن تابع ویلوکاپ در اکسل این است که اعداد به‌صورت متن ذخیره شده‌اند یا متن به‌صورت عدد در اکسل ذخیره شده است. حتی اگر این مقادیر ظاهرا یکسان باشند، اکسل آن‌ها را متفاوت در نظر می‌گیرد. این تفاوت باعث می‌شود فرمول نتواند مقدار موردنظر را پیدا کند. برای رفع این مشکل، مطمئن شوید که مقدار مورد جست‌وجو و مقادیر موجود در جدول، قالب یکسانی داشته باشند. یعنی هر دو به‌صورت متن یا هر دو به‌صورت عدد ذخیره شده باشند.

به‌علاوه اگر در داده‌ها فاصله‌های اضافی یا پنهان وجود داشته باشد، تابع VLOOKUP ممکن است به‌درستی عمل نکند. حتی اگر مقادیر در صفحه ظاهرا یکسان به نظر برسند. این فاصله‌های اضافی معمولا هنگام کپی کردن داده‌ها از سیستم‌ها یا منابع دیگر ایجاد می‌شوند. برای رفع این مشکل، می‌توانید از تابع TRIM یا ابزار Find and Replace برای حذف سریع فاصله‌های اضافی استفاده کنید.

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

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

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

خطای !REF#

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

بنابراین، همیشه بررسی کنید که محدوده table_array هر دو ستون جست‌وجو و نتیجه را به‌طور کامل دربر بگیرد. اگر مقدار col_index_num بیشتر از تعداد ستون‌های موجود در table_array باشد، اکسل خطای !REF# را نمایش می‌دهد.

 خطای !VALUE#

 داده نامناسبی داشته باشد. در مورد تابع VLOOKUP، سه دلیل رایج برای نمایش خطای !VALUE# وجود دارد:

• دلیل اول: مقدار مورد جست‌وجو بیشتر از ۲۵۵ کاراکتر است. توجه داشته باشید که تابع VLOOKUP نمی‌تواند مقادیری را که بیش از ۲۵۵ کاراکتر دارند جست‌وجو کند. اگر مقدار مورد جست‌وجو از این حد بیشتر باشد، خطای !VALUE# نمایش داده می‌شود.

• دلیل دوم: مسیر کامل فایل اکسل حاوی داده‌های مورد جست‌وجو وارد نشده است. اگر می‌خواهید داده‌ها را از یک فایل اکسل دیگر بگیرید، باید مسیر کامل آن فایل را در فرمول وارد کنید. به‌طور دقیق‌تر، باید نام فایل به همراه پسوند آن را داخل براکت مربعی [ ] قرار دهید و سپس نام شیت را همراه با علامت تعجب ! مشخص کنید. اگر نام فایل یا نام شیت، یا هر دو، دارای فاصله یا کاراکترهای غیرالفبایی باشند، باید کل مسیر را داخل علامت نقل‌قول تکی ‘ ‘ قرار دهید. برای مثال یک فرمول واقعی در مثال زیر مشخص شده است. این فرمول مقدار موجود در سلول A2 را در ستون B از شیت Sheet1 در فایل New Prices جست‌وجو می‌کند و مقدار متناظر را از ستون D برمی‌گرداند.

=VLOOKUP($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;FALSE)

• دلیل سوم: مقدار col_index_num کمتر از ۱ است. ممکن است در حالت عادی بعید به نظر برسد که کسی عمداً عددی کمتر از ۱ را برای تعیین ستون موردنظر وارد کند. بااین‌حال، این اتفاق ممکن است زمانی رخ دهد که مقدار col_index_num توسط یک تابع دیگر که در فرمول VLOOKUP استفاده شده، تولید شود. بنابراین، اگر مقدار col_index_num کمتر از ۱ باشد، فرمول نیز خطای !VALUE# را برمی‌گرداند.

محدودیت‌های ویلوکاپ

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

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

تفاوت VLOOKUP و XLOOKUP در اکسل

هر دو تابع XLOOKUP و VLOOKUP برای جست‌وجوی یک مقدار در یک محدوده و برگرداندن نتیجه مرتبط با آن استفاده می‌شوند. تابع XLOOKUP از سال ۲۰۱۹ به‌عنوان جایگزینی برای VLOOKUP معرفی شد و در نسخه‌های قدیمی‌تر اکسل پشتیبانی نمی‌شود. به زبان ساده، XLOOKUP نسخه‌ای انعطاف‌پذیرتر و بهبودیافته از تابع VLOOKUP است. فرمول VLOOKUP را در بخش‌های قبلی این مطلب توضیح دادیم. فرمول XLOOKUP به‌شکل زیر است:

Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode],[search_mode]

• Lookup_array: محدوده‌ای که می‌خواهید برای پیدا کردن مقدار موردنظر در آن جست‌وجو کنید.
• Return_array: محدوده‌ای که نتیجه مرتبط با مقدار پیدا‌شده در آن قرار دارد و باید برگردانده شود.
• [if_not_found]: گزینه‌ای اختیاری برای تعیین متن دلخواه در صورتی که مقدار موردنظر پیدا نشود.
• [match_mode]: گزینه‌ای اختیاری است که برای مشخص کردن نوع تطابق، مانند تطابق دقیق یا نزدیک‌ترین مقدار کوچک‌تر مورد استفاده قرار می‌گیرد.
• [search_mode]: گزینه‌ای اختیاری برای تعیین نحوه جست‌وجو. است. برای مثال، جست‌وجو از اولین مورد فهرست یا از آخرین مورد.

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

برای مثال، فرض کنید می‌خواهیم مبلغ کمیسیون اصغری را که یکی از فروشندگان و در ستون C است، پیدا کنیم.

در این مثال، تابع VLOOKUP خطای N/A# را نمایش می‌دهد. زیرا ستون مبلغ کمیسیون (ستون B) در سمت چپ ستون نام فروشنده (ستون C) قرار دارد و VLOOKUP نمی‌تواند به سمت چپ ستون جست‌وجو کند. در مقابل، XLOOKUP می‌تواند مبلغ کمیسیون اصغری را پیدا کند، زیرا امکان جست‌وجوی مقادیر در هر دو سمت چپ و راست ستون موردنظر را دارد.

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

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

در این حالت، تابع VLOOKUP به دلیل پیدا نکردن نام جابری در ستون فروشندگان، خطای N/A# را نمایش می‌دهد. اما XLOOKUP، با وجود اینکه نمی‌تواند جابری را پیدا کند، این امکان را به ما می‌دهد که متن دلخواهی را برای این حالت تعیین کنیم. در این مثال، برای زمانی که نام یک فروشنده در فهرست پیدا نشود، پیام «در فهرست وجود ندارد» را نمایش داده‌ایم.

تفاوت سوم این است که تابع VLOOKUP به شما اجازه نمی‌دهد نوع جست‌وجو را مشخص کنید. درحالی‌که در XLOOKUP می‌توانید تعیین کنید جست‌وجو از اولین یا آخرین مورد فهرست انجام شود و همچنین ترتیب جست‌وجو را به‌صورت صعودی یا نزولی مشخص کنید.

برای مثال، فرض کنید می‌خواهید مبلغ کمیسیون اکبری در جدیدترین سال ۱۴۰۵ را پیدا کنید.

در این حالت، تابع VLOOKUP اولین مورد پیدا‌شده را برمی‌گرداند که مربوط به سال ۱۴۰۰ است. اما با استفاده از قابلیت Search Mode در تابع XLOOKUP می‌توانیم کد ۱- را وارد کنیم تا تابع، فهرست را از پایین به بالا جست‌وجو کند. در نتیجه، اولین تطابقی که پیدا می‌شود مربوط به کمیسیون دیوید در سال ۱۴۰۵خواهد بود.

VLOOKUP یا XLOOKUP، کدام را انتخاب کنیم؟

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

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

احمد حاجی تراب

علوم داده

مبانی مهندسی داده

۴,۰ (۱۰۴ رای) ۳۶۴ نفر ۲۳۷ ساعت و ۲۰ دقیقه

بیشتر

نکات مهم برای استفاده بهتر از VLOOKUP

• مقدار موردنظر برای جست‌وجو را در اولین ستون قرار دهید: تابع VLOOKUP مقدار موردنظر را فقط در سمت چپ‌ترین ستون محدوده انتخاب‌شده (table_array) جست‌وجو می‌کند.
• برای تطابق دقیق از FALSE استفاده کنید: اگر می‌خواهید یک مقدار دقیق را پیدا کنید، آرگومان چهارم را روی FALSE قرار دهید. این حالت برای شناسه‌ها، شماره کارکنان، کد محصولات و سایر مقادیر منحصربه‌فرد کاربرد زیادی دارد.
• در استفاده از TRUE دقت کنید: مقدار TRUE برای تطابق تقریبی استفاده می‌شود و در صورت مشخص نکردن آرگومان چهارم، حالت پیش‌فرض VLOOKUP است. در این حالت، اولین ستون جدول باید به ترتیب صعودی مرتب شده باشد تا نتیجه اشتباهی برگردانده نشود.
• قالب داده‌ها را بررسی کنید: مطمئن شوید مقدار موردنظر برای جست‌وجو و مقادیر موجود در ستون جست‌وجو، قالب یکسان یا سازگاری دارند. برای مثال، اگر یک عدد به‌صورت متن ذخیره شده باشد، ممکن است VLOOKUP نتواند آن را پیدا کند.
• هنگام کپی کردن فرمول از ارجاع مطلق استفاده کنید: اگر قصد دارید فرمول VLOOKUP را در سلول‌های دیگر کپی کنید، با استفاده از علامت $ محدوده جدول را ثابت نگه دارید. برای مثال، $A$2:$C$100 باعث می‌شود محدوده جست‌وجو هنگام کپی کردن فرمول تغییر نکند.
• شماره ستون را به‌درستی وارد کنید: آرگومان col_index_num شماره ستون موردنظر برای برگرداندن نتیجه را مشخص می‌کند. شمارش ستون‌ها از سمت چپ‌ترین ستون محدوده table_array و با عدد ۱ شروع می‌شود.
• خطای N/A# را بررسی کنید: این خطا معمولاً نشان می‌دهد که VLOOKUP نتوانسته مقدار موردنظر را پیدا کند. ابتدا بررسی کنید که مقدار واقعاً در ستون جست‌وجو وجود داشته باشد و نوع تطابق انتخاب‌شده نیز مناسب باشد.
• در صورت نیاز از IFERROR استفاده کنید: اگر می‌خواهید به‌جای خطاهایی مانند N/A# یک پیام دلخواه نمایش داده شود، می‌توانید VLOOKUP را با IFERROR ترکیب کنید. برای مثال:

 =IFERROR(VLOOKUP(A2,$D$2:$E$100,2,FALSE),"Not Found")

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

 مسیر یادگیری علم داده با آکادمی همراه اول

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

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

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

در ادامه مسیر، یادگیری مباحثی مانند آمار، مصورسازی داده، SQL و یادگیری ماشین اهمیت بیشتری پیدا می‌کند. پس از یادگیری این مباحث، انجام پروژه‌های واقعی می‌تواند مهارت شما را تقویت کند و درک بهتری از کاربرد علم داده در مسائل مختلف به وجود آورد.

بنابراین، می‌توانید مسیر خود را از مفاهیم پایه و ابزارهایی مانند Excel آغاز کنید، سپس به سراغ Python و تحلیل داده بروید و در مراحل بعدی، مباحث پیشرفته‌تر را یاد بگیرید. استفاده از آموزش‌های آکادمی همراه اول در کنار تمرین مستمر و انجام پروژه‌های عملی، می‌تواند این مسیر را هدفمندتر و قابل‌پیگیری‌تر کند.

نتیجه‌گیری

تابع VLOOKUP در اکسل یکی از کاربردی‌ترین توابع برای جست‌وجو و استخراج اطلاعات از جداول است و می‌تواند زمان موردنیاز برای پیدا کردن داده‌های مرتبط را به‌طور قابل‌توجهی کاهش دهد. با یادگیری ساختار این تابع و استفاده صحیح از آرگومان‌های آن، می‌توانید اطلاعات موردنظر را بر اساس یک مقدار مشخص از جدول استخراج کنید. همچنین آشنایی با خطاهای رایج VLOOKUP و تفاوت آن با توابعی مانند XLOOKUP کمک می‌کند در پروژه‌های مختلف انتخاب مناسب‌تری داشته باشید. با تمرین مثال‌های مختلف، استفاده از VLOOKUP به یکی از مهارت‌های کاربردی شما در کار با داده‌ها و گزارش‌های اکسل تبدیل خواهد شد.

 پرسش‌های متداول (FAQ)

در این بخش از مطلب ویلوکاپ در اکسل به تعدادی از پرسش‌های متداول در مورد این موضوع پاسخ می‌دهیم. 

 چرا VLOOKUP مقدار را پیدا نمی‌کند؟

اگر VLOOKUP مقدار موردنظر را پیدا نمی‌کند و خطای N/A# نمایش می‌دهد، معمولا یکی از این دلایل وجود دارد: مقدار جست‌وجو در اولین ستون محدوده انتخاب‌شده قرار ندارد، نوع داده‌ها در دو ستون یکسان نیست (مثلاً یک مقدار به‌صورت متن و دیگری به‌صورت عدد ذخیره شده)، فاصله یا کاراکتر اضافی در داده‌ها وجود دارد یا از FALSE برای تطابق دقیق استفاده کرده‌اید، درحالی‌که مقدار دقیق در جدول وجود ندارد. همچنین در حالت تطابق تقریبی (TRUE)، مرتب نبودن داده‌ها می‌تواند باعث برگرداندن نتیجه نادرست شود.

تفاوت VLOOKUP و XLOOKUP چیست؟

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

چگونه خطای N/A# در VLOOKUP را برطرف کنیم؟ 

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

منابع

• GoSkils
• GeeksforGeeks
• Excel Master Consultant
• CareerPrinciples