آشنایی با تابع VLOOKUP/ نحوه استفاده از VLOOKUP در اکسل

تابع VLOOKUP می تواند به شما کمک کند داده های عظیمی را همانند سازی کنید

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

هماهنگ سازی داده های عظیمی در اکسل یک کار زمان بر است. از همین رو یک عملکردی در این خصوص ایجاد شده است که به شما کمک می کند این کار را به آسانی انجام دهید.

نحوه استفاده از فرمول VLOOKUP در اکسل

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

من می دانم ، "عملکرد VLOOKUP" به نظر عجیب ترین و پیچیده ترین چیز است. اما وقتی خواندن این مقاله را به پایان برسانید ، تعجب می کنید که چگونه بدون آن از Excel استفاده کرده اید.

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

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

VLOOKUP چگونه کار می کند؟

VLOOKUP مخفف Vertical lookup (جستجوی عمودی) است. در اکسل ، این به معنای جستجوی داده ها به صورت عمودی در یک صفحه گسترده ، با استفاده از ستون های صفحه گسترده - و یک شناسه منحصر به فرد در آن ستون ها - به عنوان اساس جستجوی شما است. هنگامی که داده های خود را جستجو می کنید ، باید به صورت عمودی در هر کجا که این داده ها قرار دارند فهرست شوند.

فرمول همیشه در سمت راست جستجو می کند

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

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

فرمول برای بازیابی داده ها به یک شناسه منحصر به فرد نیاز دارد

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

یک شناسه منحصر به فرد بخشی از اطلاعاتی است که هر دو منبع داده شما به اشتراک می گذارند و - همانطور که از نامش مشخص است - منحصر به فرد است (یعنی شناسه فقط با یک رکورد در پایگاه داده شما مرتبط است). شناسه های منحصر به فرد شامل کدهای محصول ، واحدهای نگهداری سهام (SKU) و مخاطبین مشتری است.

نحوه استفاده از VLOOKUP در اکسل

ستونی از سلول ها را که می خواهید با داده های جدید پر کنید مشخص کنید.

"Function" (Fx)> VLOOKUP را انتخاب کرده و این فرمول را در سلول مشخص خود وارد کنید.

مقدار جستجو را که می خواهید داده های جدید برای آن بازیابی شود وارد کنید.

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

شماره ستون داده هایی را که می خواهید Excel به آنها بازگردد وارد کنید.

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

روی " Done" (یا "Enter") کلیک کنید و ستون جدید خود را پر کنید.

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

1. ستونی از سلول هایی را که می خواهید با داده های جدید پر کنید مشخص کنید.

در مرحله اول ستون مورد نظر خود را مشخص کنید

به یاد داشته باشید ، شما به دنبال بازیابی داده ها از یک برگه دیگر و ذخیره آن در این برگه هستید. با در نظر گرفتن این موضوع ، برای درآمد مکرر ماهانه ، ستونی را در کنار سلولهایی که اطلاعات بیشتری در مورد آنها می خواهید با عنوان مناسب در سلول بالا ، مانند "MRR" ، برچسب گذاری کنید. این ستون جدید جایی است که داده هایی که واکشی می کنید به آنجا خواهد رفت.

2. "Function" (Fx)> VLOOKUP را انتخاب کرده و این فرمول را در سلول مشخص خود وارد کنید.

در سمت چپ نوار متن بالای صفحه گسترده روی Fx کلیک کنید

در سمت چپ نوار متن بالای صفحه گسترده ، یک نماد عملکرد کوچک را مشاهده می کنید که شبیه یک اسکریپت است: Fx. روی اولین سلول خالی زیر عنوان ستون خود کلیک کنید و سپس روی نماد این عملکرد کلیک کنید. کادری با عنوان Formula Builder یا Insert Function در سمت راست صفحه شما ظاهر می شود (بسته به نسخه Excel شما).

"VLOOKUP" را از لیست گزینه های موجود در فرمول ساز جستجو کرده و انتخاب کنید. سپس ، OK یا Insert Function را انتخاب کنید تا ساخت VLOOKUP شما آغاز شود. سلولی که در حال حاضر در صفحه گسترده خود مشخص کرده اید باید به این شکل باشد:

VLOOKUP()

همچنین می توانید این فرمول را به صورت دستی با وارد کردن متن پررنگ در بالا دقیقاً در سلول مورد نظر خود وارد کنید.

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

3. مقدار جستجو را که می خواهید داده های جدید را برای آن بازیابی کنید وارد کنید.

 در این مرحله روی سلول مورد نظر خود کلیک کنید

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

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

4. آرایه جدول صفحه گسترده را که داده های مورد نظر شما در آن قرار دارد وارد کنید.

قسمت هایی که در تصویر مشخص شده است را پر کنید

در کنار فیلد "آرایه جدول" ، محدوده سلول هایی را که می خواهید جستجو کنید و برگه ای که این سلول ها در آن قرار دارند را با استفاده از قالب نشان داده شده در تصویر بالا وارد کنید. ورودی بالا به این معنی است که داده های مورد نظر ما در صفحه گسترده ای با عنوان "Pages" وجود دارد و می توان آنها را در هر جایی بین ستون B و ستون K یافت.

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

در اکسل می توان چند شیت جداگانه درست کنید

به عنوان مثال ، اگر داده های شما در "Sheet2" بین سلولهای C7 و L18 قرار داشته باشد ، ورودی آرایه جدول شما "Sheet2! C7: L18" خواهد بود.

5. شماره ستون داده هایی را که می خواهید Excel به آنها بازگردد وارد کنید.

در زیر فیلد آرایه جدول ، "column index number" آرایه جدولی را که در حال جستجو هستید وارد می کنید. به عنوان مثال ، اگر بر ستون های B تا K تمرکز کرده اید اما مقادیر خاصی که می خواهید در ستون K وجود دارد ، "10" را در قسمت "column index number" وارد می کنید ، زیرا ستون K ستون دهم از سمت چپ است.

در این مرحله شماره ستون مورد نظر خود را بنویسید

6. جستجوی محدوده خود را برای پیدا کردن مطابقت دقیق یا تقریبی مقدار جستجوی خود وارد کنید.

محدوده جست و جوی خود را مشخص کنید

در شرایطی مانند وضعیت ما ، که مربوط به درآمد ماهانه است ، می خواهید از جدولی که در آن جستجو می کنید تطابق دقیق پیدا کنید. برای انجام این کار ، "FALSE" را در قسمت "range search" وارد کنید. این به اکسل می گوید که می خواهید فقط درآمد دقیق مربوط به هر مخاطب فروش را پیدا کنید.

برای پاسخ به سوال مهم شما: بله ، می توانید به Excel اجازه دهید به جای یک تطابق دقیق ، به دنبال یک تطابق تقریبی باشد. برای انجام این کار ، به سادگی TRUE را به جای FALSE در قسمت چهارم نشان داده شده در بالا وارد کنید.

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

7. روی «انجام شد» (یا «ورود») کلیک کنید و ستون جدید خود را پر کنید.

برای اینکه به طور رسمی مقادیر مورد نظر خود را در ستون جدید خود از مرحله 1 وارد کنید ، پس از پر کردن فیلد "محدوده جستجو" ، روی "انجام شد" یا "ورود" ( بسته به نسخه Excel خود) کلیک کنید. این اولین سلول شما را پر می کند. ممکن است از این فرصت استفاده کرده و در صفحه گسترده دیگر جستجو کنید تا مطمئن شوید این مقدار صحیح است.

بعد از اتمام مراحل ستون اول پر می شود

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

ستون های دیگر را نیز با این راه حل می توانید پر کنید

VLOOKUPs به عنوان یک ابزار بازاریابی قدرتمند

بازاریابان باید داده های منابع مختلف را تجزیه و تحلیل کنند تا تصویری کامل از تولید مشتری (و بیشتر) بدست آورند. Microsoft Excel ابزار کاملی برای انجام دقیق و مقیاس بالا ، به ویژه با عملکرد VLOOKUP است.

گردآوری : بخش کامپیوتر و اینترنت