VLOOKUP و XLOOKUP برای حسابدارها: جست‌وجوی درست در جدول‌های مالی
بیشتر فایل‌های حسابداری یک کار تکراری دارند: پیدا کردن یک مقدار در جدول دیگر. نام حساب از روی کد حساب، نرخ از روی کد کالا، شمارهٔ بیمهٔ پرسنل از روی کد پرسنلی. دو تابع اصلی این کار در اکسل VLOOKUP و XLOOKUP هستند.
VLOOKUP: قدیمی و همه‌جا هست
ساختارش این است:
=VLOOKUP(مقدار جست‌وجو; جدول; شمارهٔ ستون; FALSE)مثلاً اگر کد حساب در A2 باشد و جدول کدینگ در برگهٔ «کدینگ» از ستون A تا C:
=VLOOKUP(A2;کدینگ!$A:$C;2;FALSE)نام حساب (ستون دوم) را برمی‌گرداند.
XLOOKUP: نسخهٔ بهتر همان کار
در اکسل ۲۰۲۱ و Microsoft 365، XLOOKUP همین کار را ساده‌تر و امن‌تر انجام می‌دهد:
=XLOOKUP(A2;کدینگ!$A:$A;کدینگ!$B:$B;"پیدا نشد")چرا بهتر است:
- ستون جست‌وجو لازم نیست اولین ستون باشد. می‌توانی از راست جدول به چپش جست‌وجو کنی.
- شمارهٔ ستون ندارد. اگر ستونی وسط جدول اضافه شود، فرمول خراب نمی‌شود؛ در VLOOKUP شمارهٔ ستون جابه‌جا می‌شود و جواب غلط می‌دهد.
- پیش‌فرضش تطابق دقیق است و پیام «پیدا نشد» را خودش می‌گیرد.
سه اشتباه پرتکرار
- فاصلهٔ اضافه یا عدد متنی. کد «۱۰۱» که به‌صورت متن ذخیره شده با عدد ۱۰۱ برابر نیست. با
TRIMو تبدیل نوع داده یکدستش کن. - ارجاع نسبی به جدول. وقتی فرمول را پایین می‌کشی، جدول هم پایین می‌رود. از
$استفاده کن یا جدول را با Ctrl+T به «Table» تبدیل کن. - پنهان‌کردن خطا با IFERROR.
IFERROR(...;0)خطا را صفر نشان می‌دهد و کد حساب جاافتاده دیده نمی‌شود. بهتر است پیام «پیدا نشد» بگذاری و آن ردیف‌ها را بررسی کنی.
کدام را انتخاب کنم؟
| موقعیت | پیشنهاد |
|---|---|
| فایل باید در اکسل ۲۰۱۶ و قدیمی‌تر هم باز شود | VLOOKUP یا INDEX/MATCH |
| اکسل ۲۰۲۱ یا ۳۶۵ | XLOOKUP |
| جست‌وجو با دو شرط (مثلاً کد پرسنل و ماه) | XLOOKUP با ستون کمکی یا FILTER |
اگر فایلت هزاران ردیف دارد و کند شده، معمولاً مشکل از ارجاع به کل ستون (A:A) در چند هزار فرمول است؛ محدوده را به «Table» تبدیل کن تا فقط ردیف‌های واقعی خوانده شوند.
فایل اکسلت جست‌وجو و گزارش دقیق می‌خواهد؟
برآورد رایگان است؛ فرم را پر کن تا تماس بگیرم.