ساخت صورت جریان نقدی در اکسل
ساخت صورت جریان نقدی در اکسل : چرا باید جریان نقدی کسب‌وکار را کنترل کنیم؟...
آموزش حسابداری در اکسل
تفاوت VLOOKUP و XLOOKUP در اکسل؛ کدام تابع بهتر است؟
فهرست مطالب در این مقاله:
مقدمه تفاوت VLOOKUP و XLOOKUP در اکسل:
اگر با اکسل کار می‌کنید، احتمالاً برای پیدا کردن اطلاعات یک مشتری، کالا، فاکتور یا کارمند در میان حجم زیادی از داده‌ها به تابع جست‌وجو نیاز پیدا کرده‌اید. دو تابع پرکاربرد برای این کار، VLOOKUP و XLOOKUP هستند.
تابع VLOOKUP سال‌ها یکی از ابزارهای اصلی کاربران اکسل بوده است؛ اما تابع XLOOKUP با قابلیت‌های بیشتر و ساختار انعطاف‌پذیرتر، بسیاری از محدودیت‌های VLOOKUP را برطرف کرده است.
اما آیا همیشه باید از XLOOKUP استفاده کنیم؟ آیا VLOOKUP دیگر کاربردی ندارد؟
در این مقاله، تفاوت VLOOKUP و XLOOKUP را به زبان ساده بررسی می‌کنیم، ساختار هر دو فرمول را توضیح می‌دهیم و با مثال‌های حسابداری نشان می‌دهیم در چه شرایطی انتخاب هرکدام منطقی‌تر است.
تابع VLOOKUP چیست؟
تابع VLOOKUP برای جست‌وجوی یک مقدار در اولین ستون یک جدول و بازگرداندن اطلاعات از یکی دیگر از ستون‌های همان جدول استفاده می‌شود.
برای مثال، فرض کنید فهرستی از کالاها دارید که شامل کد کالا، نام کالا و قیمت واحد است. با وارد کردن کد کالا، می‌توانید نام یا قیمت آن را به‌صورت خودکار پیدا کنید.
ساختار تابع VLOOKUP
=VLOOKUP(lookup_value,table_array,col_index_num,FALSE)اجزای این فرمول عبارت‌اند از:
lookup_value: مقداری که می‌خواهید جست‌وجو کنید.table_array: محدوده جدولی که اطلاعات در آن قرار دارد.col_index_num: شماره ستونی که نتیجه از آن برگردانده می‌شود.FALSE: درخواست تطبیق دقیق مقدار جست‌وجو.
برای جست‌وجوی کد مشتری، کالا یا سند مالی معمولاً تطبیق دقیق انتخاب مناسبی است.
مثال حسابداری با VLOOKUP
فرض کنید جدول زیر در شیت Products قرار دارد:
| کد کالا | نام کالا | قیمت واحد |
|---|---|---|
| 1001 | کاغذ A4 | 2,500,000 |
| 1002 | خودکار | 150,000 |
| 1003 | زونکن | 800,000 |
فرض کنید کد کالا را در سلول A2 وارد می‌کنید و می‌خواهید نام کالا را در سلول B2 نمایش دهید.
فرمول زیر را وارد کنید:
=VLOOKUP(A2,Products!A:C,2,FALSE)اکسل کد موجود در A2 را در ستون اول محدوده Products!A:C جست‌وجو می‌کند و مقدار متناظر از ستون دوم، یعنی نام کالا، را برمی‌گرداند.
برای دریافت قیمت واحد کافی است شماره ستون را از 2 به 3 تغییر دهید:
=VLOOKUP(A2,Products!A:C,3,FALSE)این روش در فایل‌های خرید، فروش، انبار و حسابداری کاربرد زیادی دارد.
تابع XLOOKUP چیست؟
تابع XLOOKUP نسخه جدیدتر و انعطاف‌پذیرتری از توابع جست‌وجو در اکسل است. این تابع می‌تواند در یک محدوده جست‌وجو کند و مقدار متناظر را از محدوده دیگری برگرداند؛ حتی اگر محدوده نتیجه در سمت چپ محدوده جست‌وجو قرار داشته باشد.
ساختار تابع XLOOKUP
=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found)اجزای فرمول:
lookup_value: مقدار مورد جست‌وجوlookup_array: محدوده‌ای که باید در آن جست‌وجو انجام شودreturn_array: محدوده‌ای که نتیجه از آن برگردانده می‌شودif_not_found: نتیجه دلخواه در صورت پیدا نشدن مقدار
مزیت مهم این تابع آن است که برای تعیین محدوده جست‌وجو و محدوده نتیجه، به شماره ستون نیاز ندارید.
مثال حسابداری با XLOOKUP
فرض کنید همان جدول کالاها را دارید و کد کالا در سلول A2 وارد می‌شود.
برای دریافت نام کالا بنویسید:
=XLOOKUP(A2,Products!A:A,Products!B:B,"کالا یافت نشد")برای دریافت قیمت کالا:
=XLOOKUP(A2,Products!A:A,Products!C:C,"کالا یافت نشد")اگر کد کالا در جدول وجود نداشته باشد، به‌جای خطای معمول #N/A پیام «کالا یافت نشد» نمایش داده می‌شود.
این قابلیت در فایل‌های حسابداری که کاربران مختلف با آن کار می‌کنند، به خواناتر شدن خروجی کمک می‌کند.
تفاوت VLOOKUP و XLOOKUP در یک نگاه
| ویژگی | VLOOKUP | XLOOKUP |
|---|---|---|
| محل جست‌وجو | اولین ستون محدوده جدول | محدوده جست‌وجوی مستقل |
| بازگرداندن نتیجه از سمت چپ | به‌صورت مستقیم امکان‌پذیر نیست | امکان‌پذیر است |
| نیاز به شماره ستون | دارد | ندارد |
| پیام سفارشی در صورت نبود نتیجه | معمولاً با IFERROR | با آرگومان if_not_found |
| تطبیق دقیق | با FALSE |
به‌صورت پیش‌فرض |
| جست‌وجو از آخر به اول | به‌صورت مستقیم پشتیبانی نمی‌شود | پشتیبانی می‌شود |
| سازگاری با نسخه‌های قدیمی اکسل | گسترده‌تر | محدود به نسخه‌های پشتیبانی‌کننده |
| بازگرداندن چند ستون | محدودتر | در نسخه‌های پشتیبانی‌کننده امکان‌پذیر است |
در مجموع، XLOOKUP برای بسیاری از کاربردهای جدید گزینه انعطاف‌پذیرتری است؛ اما سازگاری فایل با نسخه اکسل مخاطبان نیز باید در انتخاب تابع در نظر گرفته شود.
تفاوت اول: جست‌وجو از چپ به راست و از راست به چپ
یکی از محدودیت‌های مهم VLOOKUP این است که مقدار جست‌وجو باید در اولین ستون محدوده جدول قرار داشته باشد و نتیجه از ستونی در سمت راست آن برگردانده شود.
برای مثال، فرض کنید جدول شما به این شکل است:
| نام کالا | کد کالا | قیمت |
|---|---|---|
| کاغذ A4 | 1001 | 2,500,000 |
| خودکار | 1002 | 150,000 |
| زونکن | 1003 | 800,000 |
اگر کد کالا در سلول E2 باشد و بخواهید نام کالا را پیدا کنید، VLOOKUP در محدوده A نمی‌تواند مستقیماً از ستون B جست‌وجو کند و نتیجه را از ستون A برگرداند.
اما XLOOKUP به‌راحتی این کار را انجام می‌دهد:
=XLOOKUP(E2,B2:B4,A2:A4,"یافت نشد")در این فرمول، اکسل کد کالا را در ستون B جست‌وجو می‌کند و نام کالا را از ستون A برمی‌گرداند.
نتیجه: اگر ساختار جدول شما ثابت نیست یا لازم است از ستونی به ستون سمت چپ آن جست‌وجو کنید، XLOOKUP انتخاب ساده‌تری است.
تفاوت دوم: نیاز به شماره ستون
در VLOOKUP باید شماره ستونی را مشخص کنید که نتیجه از آن برگردانده می‌شود.
برای مثال:
=VLOOKUP(A2,Products!A:C,3,FALSE)عدد 3 یعنی نتیجه از سومین ستون محدوده انتخاب‌شده برگردانده شود.
اگر بعداً ستونی به جدول اضافه شود یا ساختار محدوده تغییر کند، ممکن است لازم باشد شماره ستون را اصلاح کنید.
در XLOOKUP نیازی به شماره ستون نیست:
=XLOOKUP(A2,Products!A:A,Products!C:C,"یافت نشد")محدوده جست‌وجو و محدوده نتیجه به‌صورت مستقل مشخص می‌شوند. این ساختار خواناتر است و در بسیاری از فایل‌های قابل توسعه، نگهداری فرمول‌ها را آسان‌تر می‌کند.
تفاوت سوم: مدیریت مقدارهای پیدا نشده
هنگامی که VLOOKUP نتواند مقدار موردنظر را پیدا کند، معمولاً خطای #N/A نمایش داده می‌شود.
برای مدیریت این خطا می‌توانید از IFERROR استفاده کنید:
=IFERROR(VLOOKUP(A2,Products!A:C,2,FALSE),"کالا یافت نشد")در XLOOKUP، آرگومان چهارم برای همین منظور در نظر گرفته شده است:
=XLOOKUP(A2,Products!A:A,Products!B:B,"کالا یافت نشد")هر دو روش قابل استفاده هستند؛ اما در XLOOKUP نیازی به نوشتن تابع جداگانه برای مدیریت نبود نتیجه ندارید.
توجه کنید که IFERROR تمام خطاهای فرمول را می‌تواند پوشش دهد؛ بنابراین اگر هدف فقط مدیریت نبود مقدار است، پیام عمومی آن ممکن است خطاهای دیگری را هم پنهان کند.
تفاوت چهارم: جست‌وجوی دقیق و تقریبی
در VLOOKUP، آرگومان آخر تعیین می‌کند که جست‌وجو دقیق باشد یا تقریبی:
=VLOOKUP(A2,Products!A:C,2,FALSE)در این مثال، FALSE یعنی مقدار باید دقیقاً پیدا شود.
در XLOOKUP، تطبیق دقیق به‌صورت پیش‌فرض استفاده می‌شود:
=XLOOKUP(A2,Products!A:A,Products!B:B,"یافت نشد")این ویژگی برای کد پرسنلی، شماره فاکتور، کد کالا و شناسه مشتری اهمیت دارد؛ زیرا معمولاً نباید اطلاعات یک شناسه متفاوت به‌عنوان نتیجه برگردانده شود.
اگر از VLOOKUP برای جست‌وجوی تقریبی استفاده می‌کنید، باید به نوع مرتب‌سازی داده‌ها و آرگومان انتخابی توجه داشته باشید.
تفاوت پنجم: جست‌وجو از آخر به اول
گاهی ممکن است یک کد مشتری یا کالا چند بار در جدول تکرار شده باشد و بخواهید آخرین رکورد مطابق آن را پیدا کنید.
XLOOKUP امکان تعیین جهت جست‌وجو را دارد. برای جست‌وجو از آخر به اول می‌توانید از آرگومان‌های اختیاری آن استفاده کنید:
=XLOOKUP(A2,Products!A2:A100,Products!C2:C100,"یافت نشد",0,-1)در این فرمول، مقدار -1 در آرگومان آخر باعث می‌شود جست‌وجو از آخرین مقدار به سمت اولین مقدار انجام شود.
این قابلیت زمانی کاربرد دارد که ترتیب رکوردها معنا داشته باشد؛ برای مثال، وقتی می‌خواهید آخرین قیمت ثبت‌شده برای یک کد کالا را پیدا کنید. البته اگر داده‌ها مربوط به سوابق قیمت هستند، بهتر است تاریخ ثبت نیز بررسی شود تا آخرین ردیف الزاماً با جدیدترین تاریخ اشتباه گرفته نشود.
مثال کاربردی: دریافت خودکار اطلاعات مشتری در فایل حسابداری
فرض کنید جدولی به نام Customers دارید که ستون‌های آن شامل کد مشتری، نام مشتری و شماره تماس است.
| کد مشتری | نام مشتری | شماره تماس |
|---|---|---|
| C101 | علی رضایی | 09120000001 |
| C102 | مریم احمدی | 09120000002 |
| C103 | رضا محمدی | 09120000003 |
اگر کد مشتری در سلول A2 وارد شود، می‌توانید با XLOOKUP نام او را نمایش دهید:
=XLOOKUP(A2,Customers!A:A,Customers!B:B,"مشتری یافت نشد")برای نمایش شماره تماس:
=XLOOKUP(A2,Customers!A:A,Customers!C:C,"مشتری یافت نشد")با این روش، هنگام ثبت فاکتور فروش، لازم نیست نام مشتری و شماره تماس او را هر بار دستی وارد کنید.
اگر از VLOOKUP استفاده می‌کنید، فرمول مشابه برای دریافت نام مشتری به این شکل است:
=IFERROR(VLOOKUP(A2,Customers!A:C,2,FALSE),"مشتری یافت نشد")هر دو فرمول می‌توانند نتیجه موردنظر را ارائه دهند، به شرط آنکه ساختار داده‌ها و نسخه اکسل با تابع انتخاب‌شده سازگار باشد.
آیا XLOOKUP همیشه بهتر از VLOOKUP است؟
خیر؛ انتخاب تابع به نیاز شما بستگی دارد.
چه زمانی XLOOKUP را انتخاب کنیم؟
XLOOKUP معمولاً انتخاب مناسب‌تری است اگر:
- از نسخه جدید اکسل استفاده می‌کنید.
- به جست‌وجو در هر جهت نیاز دارید.
- می‌خواهید فرمول‌ها خواناتر باشند.
- لازم است پیام مشخصی برای نبود نتیجه نمایش داده شود.
- می‌خواهید محدوده جست‌وجو و نتیجه مستقل باشند.
- قصد دارید فایل حسابداری خود را در آینده توسعه دهید.
چه زمانی VLOOKUP انتخاب مناسبی است؟
VLOOKUP همچنان مفید است اگر:
- فایل باید در نسخه‌های قدیمی اکسل اجرا شود.
- ساختار جدول ثابت و ساده است.
- کاربران فایل با VLOOKUP آشنایی دارند.
- فرمول‌های موجود فایل به‌خوبی کار می‌کنند و نیازی به بازنویسی آن‌ها نیست.
پیش از جایگزین کردن VLOOKUP با XLOOKUP، نسخه اکسل کاربران نهایی را بررسی کنید. XLOOKUP در برخی نسخه‌های قدیمی‌تر در دسترس نیست و استفاده از آن ممکن است باعث نمایش خطای #NAME? شود.
نکات مهم برای جلوگیری از خطا در VLOOKUP و XLOOKUP
برای اینکه فرمول‌های جست‌وجو در فایل‌های حسابداری دقیق‌تر عمل کنند، این موارد را بررسی کنید:
- نوع داده‌ها یکسان باشد: عددی که به‌صورت متن ذخیره شده با مقدار عددی ممکن است تطابق نداشته باشد.
- فاصله‌های اضافی را کنترل کنید: فاصله ابتدا یا انتهای کد و نام می‌تواند مانع تطبیق شود.
- شناسه‌های تکراری را بررسی کنید: اگر کد تکراری باشد، نتیجه تابع ممکن است اولین تطابق را برگرداند؛ در XLOOKUP می‌توان جهت جست‌وجو را تغییر داد.
- محدوده جست‌وجو را درست انتخاب کنید: اشتباه در ستون یا محدوده نتیجه باعث بازگرداندن اطلاعات نادرست می‌شود.
- از پیام‌های خطای قابل فهم استفاده کنید: پیام‌هایی مانند «کد مشتری یافت نشد» برای کاربران روشن‌تر از نمایش مستقیم خطا هستند.
- فرمول را با داده‌های نمونه آزمایش کنید: نتیجه را با اطلاعات اصلی جدول مقایسه کنید تا مطمئن شوید فرمول درست عمل می‌کند.
در فایل‌های حسابداری، صرفاً حذف خطا از ظاهر گزارش کافی نیست؛ باید مطمئن شوید مقدار برگشتی واقعاً به رکورد موردنظر تعلق دارد.
پرسش‌های متداول درباره تفاوت VLOOKUP و XLOOKUP
آیا XLOOKUP جایگزین VLOOKUP شده است؟
XLOOKUP در بسیاری از کاربردها جایگزین انعطاف‌پذیرتری است، اما VLOOKUP همچنان برای فایل‌های قدیمی و محیط‌هایی که XLOOKUP را پشتیبانی نمی‌کنند، کاربرد دارد.
چرا XLOOKUP در اکسل من کار نمی‌کند؟
ممکن است نسخه اکسل شما از این تابع پشتیبانی نکند. اگر پس از وارد کردن فرمول خطای #NAME? مشاهده کردید، نسخه اکسل را بررسی کنید یا از تابعی سازگار مانند VLOOKUP استفاده کنید.
آیا XLOOKUP می‌تواند چند ستون را برگرداند؟
در نسخه‌های پشتیبانی‌کننده از آرایه‌های پویا، می‌توان محدوده چندستونی را به‌عنوان محدوده نتیجه انتخاب کرد تا چند ستون اطلاعات برگردانده شود؛ البته باید فضای کافی برای نمایش نتایج وجود داشته باشد.
برای فایل اکسل حسابداری کدام تابع مناسب‌تر است؟
اگر کاربران از نسخه‌های جدید اکسل استفاده می‌کنند، XLOOKUP معمولاً انتخاب خوبی برای دریافت اطلاعات مشتری، کالا و کارکنان است. اگر سازگاری با نسخه‌های قدیمی اهمیت دارد، VLOOKUP می‌تواند انتخاب مناسب‌تری باشد.
آیا استفاده از XLOOKUP باعث افزایش سرعت اکسل می‌شود؟
لزوماً نه. عملکرد فایل به حجم داده‌ها، تعداد فرمول‌ها، محدوده‌های ارجاع‌شده و ساختار workbook بستگی دارد. بهتر است از محدوده‌های مناسب استفاده کنید و در فایل‌های بزرگ، عملکرد را با داده‌های واقعی آزمایش کنید.
جمع‌بندی
تفاوت اصلی VLOOKUP و XLOOKUP در انعطاف‌پذیری و شیوه تعریف محدوده جست‌وجو است. VLOOKUP به شماره ستون نیاز دارد و نتیجه را از ستون‌های سمت راست محدوده جست‌وجو برمی‌گرداند؛ در مقابل، XLOOKUP محدوده جست‌وجو و نتیجه را جداگانه مشخص می‌کند و امکانات بیشتری برای مدیریت نبود نتیجه و جهت جست‌وجو دارد.
اگر از نسخه جدید اکسل استفاده می‌کنید، XLOOKUP معمولاً گزینه مناسب‌تری برای ساخت فایل‌های حسابداری جدید است. بااین‌حال، برای فایل‌هایی که باید در نسخه‌های قدیمی‌تر اجرا شوند، VLOOKUP همچنان کاربرد دارد.
بهترین انتخاب، تابعی است که ضمن پاسخ‌گویی به نیاز شما، با نسخه اکسل کاربران سازگار باشد و اطلاعات مالی را به‌شکل درست و قابل کنترل برگرداند.
