نحوه استفاده از VLOOKUP در Google Sheets

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

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

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

استفاده از VLOOKUP Formula Syntax

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

VLOOKUP (شرایط جستجوی شما، محدوده سلول، ارزش بازگشتی، وضعیت مرتب شده)
بیایید به ترتیب به هر یک از اینها نگاه کنیم.

شرایط جستجوی شما: به این کلید جستجو در اسناد اشاره می شود، اما این عبارتی است که می خواهید پیدا کنید. این می تواند یک عدد یا یک بیت متن (به عنوان مثال یک رشته) باشد. فقط مطمئن شوید که متن است که آن را در گیومه قرار دهید.
محدوده سلولی: که به سادگی محدوده نامیده می شود، از این برای انتخاب سلول های صفحه گسترده خود که در آن جستجو می کنید استفاده می کنید. احتمالاً این یک منطقه مستطیلی با بیش از تعداد زیادی ستون و ردیف خواهد بود، اگرچه این فرمول با یک سطر و دو ستون کار می کند.
RETURN VALUE: مقداری که می خواهید برگردانید، که شاخص نیز نامیده می شود، مهم ترین بخش تابع است و درک آن دشوارترین است.این تعداد ستون با مقداری است که می خواهید نسبت به ستون اول برگردانید. به روشی دیگر، اگر اولین ستون (جستجو شده) ستون 1 باشد، این تعداد ستونی است که می خواهید مقدار آن را از همان ردیف برگردانید.
SORTED STATE: در منابع دیگر به‌عنوان is_sorted تعیین می‌شود، و مقدار درست/نادرست در مورد مرتب‌سازی ستون جستجو شده (دوباره، ستون 1) است. این در هنگام جستجوی مقادیر عددی مهم است. اگر این مقدار روی FALSE تنظیم شود، نتیجه برای اولین ردیف کاملاً منطبق خواهد بود. اگر هیچ مقداری در ستون 1 وجود نداشته باشد که با عبارت جستجو مطابقت داشته باشد، با خطا مواجه خواهید شد. با این حال، اگر این مقدار روی TRUE تنظیم شود، نتیجه اولین مقدار کمتر یا مساوی با عبارت جستجو خواهد بود. اگر هیچ مورد منطبقی وجود نداشته باشد، دوباره با خطا مواجه خواهید شد.


عملکرد VLOOKUP در عمل
فرض کنید فهرست کوتاهی از محصولات دارید که هر کدام قیمتی مرتبط دارند. سپس، اگر می خواهید یک سلول را با قیمت یک لپ تاپ پر کنید، از فرمول زیر استفاده کنید:
=VLOOKUP("لپ تاپ"، A3:B9، 3، false)
این قیمت را برمی گرداند که در ستون 3 در این مثال ذخیره شده است، که ستون دو سمت راست از یکی با اهداف جستجو است.
بیایید نگاهی به این گام به گام بیندازیم تا فرآیند را با جزئیات توضیح دهیم.

مکان نما را در سلولی که می خواهید نتیجه ظاهر شود قرار دهید. در این مثال، B11 است (برچسب آن در A11، "قیمت لپ تاپ" است، اگرچه این در فرمول وجود ندارد).

فرمول را با علامت مساوی شروع کنید (=)، سپس تابع را وارد کنید. همانطور که گفته شد، این یک فرمول ساده خواهد بود که فقط از این تابع تشکیل شده است. در این مورد، ما از فرمول استفاده می کنیم:
=VLOOKUP("لپ تاپ"، A3:C9، 3، false)






Enter را فشار دهید. خود فرمول در صفحه‌گسترده ناپدید می‌شود (اگرچه همچنان در نوار فرمول بالا ظاهر می‌شود)، و نتیجه به جای آن نشان داده می‌شود.

در مثال، فرمول به محدوده A3 تا C9 نگاه می کند سپس به دنبال ردیف حاوی "لپ تاپ" می گردد. سپس ستون سوم را در محدوده جستجو می کند (دوباره، ستون اول را نیز شامل می شود)، و نتیجه را برمی گرداند که $1، 199 است. این باید نتیجه‌ای باشد که می‌خواهید، اما اگر عجیب به نظر می‌رسد، پارامترهایی را که وارد کرده‌اید دوباره بررسی کنید تا مطمئن شوید که درست هستند (مخصوصاً اگر فرمول را از سلول دیگری کپی و جای‌گذاری کرده باشید، زیرا محدوده سلول ممکن است به صورت یک تغییر کند. نتیجه).


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

استفاده از VLOOKUP در صفحات مختلف Google
با توجه به پارامتر CELL RANGE، می‌توانید VLOOKUP خود را نه تنها روی سلول‌های صفحه فعلی، بلکه در سایر برگه‌های کتاب کار نیز انجام دهید. از نماد زیر برای تعیین محدوده سلولی در برگه دیگری در کتاب کار فعلی خود استفاده کنید:
=VLOOKUP("لپ تاپ"، "نام برگه در گیومه تکی اگر بیش از یک کلمه باشد"!A1:B9, 3, false)
حتی می توانید به سلول های یک کتاب کار کاملاً متفاوت Sheets دسترسی پیدا کنید، اما باید از تابع IMPORTRANGE استفاده کنید. این دو پارامتر می‌گیرد: URL کتاب کار برگه‌هایی که می‌خواهید استفاده کنید، و محدوده‌ای از سلول‌ها از جمله نام Sheet همانطور که در بالا نشان داده شده است. تابعی که همه این موارد را در خود دارد ممکن است به شکل زیر باشد:
=VLOOKUP("لپ تاپ"، IMPORTRANGE("https://docs.google.com/spreadsheets/d/aLlThEnUmBeRsAnDlEtTeRs/"، "Sheet1!B7:D42")، 3، false)
در این مثال، تابع تودرتو (یعنی نتیجه تابع IMPORTRANGE) به یکی از پارامترهای تابع VLOOKUP تبدیل می شود.

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

ابتدا، عبارات جستجوی متنی را در نقل قول قرار دهید. در غیر این صورت، کاربرگ‌نگار Google متوجه می‌شود که یک محدوده نام‌گذاری شده است، و اگر نتواند آن را پیدا کند به شما خطایی می‌دهد.
اگر یکی از این فرمول‌ها را کنار می‌گذارید و جای‌گذاری می‌کنید، قوانین عادی برای به‌روزرسانی مقدار محدوده سلول همچنان اعمال می‌شوند. به عبارت دیگر، اگر فهرست ثابتی از داده‌ها دارید، مطمئن شوید که محدوده سلول را با علامت دلار (یعنی «$A$2:$B$8» به جای «A2:B8») لنگر می‌اندازید. در غیر این صورت بسته به جایی که آنها را چسبانده‌اید، فرمول تغییر می‌کند (به تصویر صفحه در ابتدای بخش توجه کنید، جایی که شماره ردیف‌ها یک عدد خاموش است).
اگر لیست خود را مرتب می کنید، به خاطر داشته باشید که در صورت مرتب کردن مجدد آن، دوباره به جستجوهای خود مراجعه کنید. اگر حالت مرتب‌شده فرمول را روی TRUE تنظیم کنید، ممکن است نتایج غیرمنتظره‌ای به شما بدهد.