برتری داشتن, وبسایت و پروفایل شخصی داود جمیری

نحوه استفاده از عملکرد VLOOKUP در Microsoft Excel [+ آموزش تصویری]

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

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

استفاده از عملکرد VLOOKUP مایکروسافت اکسل آسانتر از آن است که فکر می کنید. علاوه بر این ، فوق العاده قدرتمند است و قطعاً چیزی است که شما می خواهید در زرادخانه سلاح های تحلیلی خود داشته باشید. “https://cta-redirect.hubspot.com/cta/redirect/53/9ff7a4fe-5293-496c-acca-566bc6e73f42” target = “_ blank”> Download 9 Excel Templates for Marketers [Free Kit] VLOOKUP دقیقاً چه کاری انجام می دهد؟ در اینجا توضیح ساده وجود دارد: عملکرد VLOOKUP مقدار مشخصی را در داده های شما جستجو می کند ، و پس از شناسایی این مقدار ، می تواند اطلاعات دیگری را که با آن مقدار مرتبط هستند پیدا و نمایش دهد.

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

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

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

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

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

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

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

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

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

مثال VLOOKUP

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

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

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

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

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

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

Using VLOOKUP: Adding a New Column in Excel

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

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

Using VLOOKUP: Inserting VLOOKUP Function

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

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

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

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

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

Using VLOOKUP: Entering Lookup Value

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

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

4 آرایه جدول صفحه گسترده ای را که داده های مورد نظر شما در آن قرار دارد وارد کنید. Using VLOOKUP: Designating Table Array

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

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

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

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

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

Using VLOOKUP: Designating Col_Index_Num

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

Using VLOOKUP: Designating Range Lookup as True or False

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

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

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

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

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

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

Using VLOOKUP: Values Populated 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 نمی تواند با مقادیر مطابقت داشته باشد.

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

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

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

New Call-to-Action دهانه>

نوشته ها مرتبط

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

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