0

نحوه استفاده از عملکرد VLOOKUP در Microsoft Excel [+ Video Tutorial]

بازدید 10


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

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

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

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

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

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

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

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

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

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

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

خوب ، توضیح کافی: بیایید نمونه دیگری از VLOOKUP را در عمل ببینیم!

مثال VLOOKUP

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

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

برای مرجع شما ، عملکرد VLOOKUP به این صورت است:

VLOOKUP (مقدار جستجوی_ ، جدول_آرایه ، col_index_num ، range_lookup)

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

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

استفاده از VLOOKUP: افزودن ستون جدید در Excel

به یاد داشته باشید ، شما می خواهید داده ها را از یک صفحه دیگر بازیابی کرده و در این صفحه واریز کنید. با توجه به این نکته ، برای درآمد مکرر ماهانه یک ستون در کنار سلول هایی که می خواهید اطلاعات بیشتری در مورد آنها داشته باشید با عنوان مناسب در سلول بالا مانند “MRR” برچسب گذاری کنید. این ستون جدید مکانی است که داده های شما در حال واکشی است.

2. “عملکرد” ​​(Fx)> VLOOKUP را انتخاب کرده و این فرمول را در سلول برجسته شده خود وارد کنید.

استفاده از VLOOKUP: درج عملکرد VLOOKUP

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

“VLOOKUP” را از لیست گزینه های موجود در فرمول ساز جستجو کرده و انتخاب کنید. سپس ، را انتخاب کنید خوب یا درج عملکرد برای شروع ساخت VLOOKUP خود. سلولی که در حال حاضر در صفحه گسترده خود برجسته کرده اید اکنون باید به این شکل باشد: “= VLOOKUP ()

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

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

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

استفاده از VLOOKUP: وارد کردن مقدار جستجو

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

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

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

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

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

استفاده از VLOOKUP: یک برگ دیگر

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

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

در زیر قسمت آرایه جدول ، “شماره شاخص ستون” آرایه جدول مورد جستجو را وارد می کنید. به عنوان مثال ، اگر روی ستونهای B از طریق K تمرکز کرده اید (وقتی در قسمت “آرایه جدول” وارد می شوید “B: K”) ، اما مقادیر خاصی که می خواهید در ستون K باشد ، “10” را وارد می کنید قسمت “شماره شاخص ستون” ، از آنجا که ستون K ستون 10 از سمت چپ است.

با استفاده از VLOOKUP: تعیین Col_Index_Num

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

استفاده از VLOOKUP: تعیین محدوده جستجو به عنوان درست یا غلط

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

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

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

7. روی “انجام شده” (یا “ورود”) کلیک کنید و ستون جدید خود را پر کنید.

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

استفاده از VLOOKUP: جمع کردن مقادیر

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

استفاده از VLOOKUP: مقادیر پر شده

VLOOKUP کار نمی کند؟

اگر مراحل بالا را دنبال کرده باشید و VLOOKUP شما هنوز کار نکند ، این مسئله با مشکل شما روبرو خواهد شد:

  • نحو (یعنی فرمول را چگونه ساختار داده اید)
  • ارزش های (یعنی آیا داده هایی که جستجو می کند خوب است و به درستی قالب بندی شده است)

عیب یابی نحو VLOOKUP

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

  • آیا منظور از مقدار جستجوی مناسب برای شناسه اصلی آن است؟
  • آیا محدوده آرایه جدول صحیحی را برای مقادیر مورد نیاز برای بازیابی مشخص می کند
  • آیا برگه صحیح را برای محدوده مشخص می کند؟
  • آیا آن برگه درست نوشته شده است؟
  • آیا با استفاده از نحو صحیح به صفحه مراجعه می شود؟ (به عنوان مثال Pages! B: K یا ‘Sheet 1’! B: K)
  • آیا شماره ستون صحیح مشخص شده است؟ (به عنوان مثال A 1 ، B 2 و غیره است)
  • آیا درست یا غلط مسیر صحیح نحوه تنظیم ورق شماست؟

عیب یابی مقادیر VLOOKUP

اگر نحو مشکلی نداشته باشد ، چگونه ممکن است در مقادیری که سعی دارید خود آنها را دریافت کنید مشکلی داشته باشید. این اغلب به صورت # خطای N ​​/ A جایی که VLOOKUP نمی تواند یک مقدار ارجاع شده پیدا کند.

  • آیا مقادیر به صورت عمودی و از راست به چپ قالب بندی می شوند؟
  • آیا مقادیر با نحوه مراجعه شما مطابقت دارند؟

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

با توجه به این مثال ، URL ها باید به صورت قالب در هر دو صفحه مطابقت داشته باشند. اگر یک برگ شامل “https: //” در مقدار داشته باشید در حالی که ورق دیگر “https: //” را حذف می کند ، VLOOKUP نمی تواند با مقادیر مطابقت داشته باشد.

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

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

یادداشت سردبیر: این پست در اصل در مارس 2019 منتشر شد و برای جامعیت به روز شده است.

دعوت به عمل جدید



منبع

نظرات کاربران

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

*

code

16  +    =  20