آموزش تابع VLOOKUP در اکسل برای حسابداران؛ کاربرد و مثال

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

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

تابع VLOOKUP در اکسل چیست؟

VLOOKUP مخفف عبارت Vertical Lookup است. این تابع یک مقدار را در ستون اول جدول جست‌وجو می‌کند و اطلاعات مرتبط با آن را از یکی از ستون‌های سمت راست برمی‌گرداند.

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

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

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

فرمول تابع VLOOKUP از چهار بخش تشکیل می‌شود:

VLOOKUP(مقدار جست‌وجو، محدوده جدول، شماره ستون، نوع جست‌وجو)

هر بخش وظیفه مشخصی دارد:

مقدار جست‌وجو

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

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

محدوده جدول

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

اگر جدول اطلاعات مشتریان از ستون‌های F تا H تشکیل شده باشد، محدوده جدول می‌تواند F2 باشد.

شماره ستون

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

برای مثال، در محدوده F2، ستون F شماره ۱، ستون G شماره ۲ و ستون H شماره ۳ است.

نوع جست‌وجو

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

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

مثال تابع VLOOKUP در حسابداری

فرض کنید جدولی از اطلاعات مشتریان دارید که شامل سه ستون زیر است:

کد مشتری
نام مشتری
مانده حساب

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

در این مثال، اکسل ابتدا مقدار موجود در A2 را در ستون F جست‌وجو می‌کند. سپس اطلاعات ستون دوم محدوده، یعنی نام مشتری، را نمایش می‌دهد.

اگر بخواهید مانده حساب مشتری را نشان دهید، شماره ستون را از ۲ به ۳ تغییر می‌دهید. به این ترتیب، اکسل به‌جای نام مشتری، مبلغ مانده را از ستون سوم برمی‌گرداند.

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

کاربرد VLOOKUP برای پیدا کردن قیمت کالا

یکی دیگر از کاربردهای مهم تابع VLOOKUP در اکسل، نمایش خودکار قیمت کالا است.

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

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

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

کاربرد VLOOKUP در تطبیق کد حساب و نام حساب

در بسیاری از فایل‌های حسابداری، کد حساب در یک ستون ثبت می‌شود اما کاربر نیاز دارد نام حساب را نیز مشاهده کند.

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

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

اگر می‌خواهید با ساختار ثبت‌ها آشنا شوید، مقاله «ثبت حسابداری چیست؟» را نیز مطالعه کنید.

نکات مهم هنگام استفاده از تابع VLOOKUP

برای اینکه تابع VLOOKUP درست کار کند، باید چند نکته را رعایت کنید.

اول، مقدار مورد جست‌وجو باید در اولین ستون محدوده جدول قرار داشته باشد. برای مثال، اگر کد مشتری در ستون G است اما محدوده را از ستون F انتخاب کنید، VLOOKUP نمی‌تواند کد مشتری را در ستون اول محدوده پیدا کند.

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

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

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

خطای N/A در تابع VLOOKUP چیست؟

خطای N/A یکی از رایج‌ترین خطاها در زمان استفاده از VLOOKUP است. این خطا معمولاً نشان می‌دهد مقدار موردنظر در ستون اول جدول مرجع پیدا نشده است.

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

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

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

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

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

تابع XLOOKUP انعطاف بیشتری دارد و می‌تواند جست‌وجو را در جهت‌های مختلف انجام دهد. همچنین مدیریت خطا در آن ساده‌تر است.

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

برای آشنایی با ساختار رسمی این تابع و آرگومان‌های آن، می‌توانید راهنمای Microsoft Support را نیز ببینید.

خطاهای رایج در استفاده از VLOOKUP

انتخاب نادرست شماره ستون

شماره ستون باید بر اساس محدوده انتخاب‌شده تعیین شود. اگر محدوده شما از ستون F شروع می‌شود، ستون F شماره یک محسوب می‌شود.

قرار ندادن مقدار جست‌وجو در ستون اول

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

استفاده از جست‌وجوی تقریبی برای کدها

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

ثابت نکردن محدوده جدول

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

وجود فاصله یا فرمت متفاوت در کدها

گاهی کد مشتری در یک شیت به شکل ۰۰۱۲ و در شیت دیگر به شکل ۱۲ ثبت شده است. این تفاوت می‌تواند باعث شود اکسل اطلاعات را پیدا نکند.

چه زمانی از VLOOKUP استفاده کنیم؟

VLOOKUP برای جست‌وجوی اطلاعات از جدول‌های عمودی مناسب است. استفاده از آن در موارد زیر کاربرد زیادی دارد:

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

برای آشنایی با سایر فرمول‌های کاربردی، مقاله «توابع اکسل برای حسابداران» را نیز مطالعه کنید.

سؤالات متداول

تابع VLOOKUP در اکسل چه کاری انجام می‌دهد؟

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

چرا VLOOKUP اطلاعات را پیدا نمی‌کند؟

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

آیا VLOOKUP برای کد مشتری مناسب است؟

بله. این تابع برای نمایش نام، مانده حساب یا سایر اطلاعات مشتری بر اساس کد مشتری بسیار کاربردی است.

آیا می‌توان با VLOOKUP قیمت کالا را در فاکتور نمایش داد؟

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

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

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

جمع‌بندی

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

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