تمام اسرار تابع Excel VLOOKUP برای یافتن داده ها در یک جدول و استخراج آن در جدول دیگر

2024 نویسنده: Malcolm Clapton | [email protected]. آخرین اصلاح شده: 2023-12-17 03:54

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

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

حتی اگر سال ها از تابع VLOOKUP استفاده می کنید، با احتمال زیاد این مقاله برای شما مفید خواهد بود و شما را بی تفاوت نخواهد گذاشت. به عنوان مثال، به عنوان یک متخصص فناوری اطلاعات، و سپس مدیر فناوری اطلاعات، 15 سال است که از VLOOKUP استفاده می کنم، اما تنها در حال حاضر، زمانی که شروع به آموزش اکسل به افراد به صورت حرفه ای کردم، توانستم با تمام تفاوت های ظریف کنار بیایم.

VLOOKUP مخفف آن است v عمودی NS بازرسی. به همین ترتیب، VLOOKUP - جستجوی عمودی. نام تابع به ما اشاره می کند که در ردیف های جدول جستجو می کند (به صورت عمودی - تکرار روی ردیف ها و ثابت کردن ستون)، و نه در ستون ها (به صورت افقی - تکرار روی ستون ها و ثابت کردن ردیف). لازم به ذکر است که VLOOKUP یک خواهر دارد - یک جوجه اردک زشت که هرگز به قو تبدیل نمی شود - این تابع HLOOKUP است. HLOOKUP، بر خلاف VLOOKUP، جستجوهای افقی را انجام می دهد، اما مفهوم اکسل (و در واقع مفهوم سازماندهی داده ها) به این معنی است که جداول شما دارای ستون های کمتر و سطرهای بسیار بیشتری هستند. به همین دلیل است که ما باید چندین بار بیشتر از ستون ها جستجو کنیم. اگر از تابع HLO اغلب در اکسل استفاده می کنید، احتمالاً چیزی در این زندگی متوجه نشده اید.

نحو

تابع VLOOKUP چهار پارامتر دارد:

= VLOOKUP (;; [;])، اینجا:

- مقدار مورد نظر (به ندرت) یا اشاره به یک سلول حاوی مقدار مورد نظر (اکثریت قریب به اتفاق موارد).

- ارجاع به محدوده ای از سلول ها (آرایه دو بعدی)، در ستون FIRST (!) که مقدار پارامتر آن جستجو می شود.

- شماره ستون در محدوده ای که مقدار از آن بازگردانده می شود.

- این یک پارامتر بسیار مهم است که به این سوال پاسخ می دهد که آیا ستون اول محدوده به ترتیب صعودی مرتب شده است یا خیر. اگر آرایه مرتب شده باشد، مقدار TRUE یا 1 را مشخص می کنیم، در غیر این صورت - FALSE یا 0. اگر این پارامتر حذف شود، به طور پیش فرض 1 است.

شرط می بندم که بسیاری از کسانی که تابع VLOOKUP را پوسته پوسته می دانند، پس از خواندن توضیحات پارامتر چهارم، ممکن است احساس ناراحتی کنند، زیرا عادت دارند آن را به شکلی متفاوت ببینند: معمولاً در مورد یک تطابق دقیق صحبت می کنند. جستجو (FALSE یا 0) یا اسکن محدوده (درست یا 1).

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

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

• فرمول نوع I. اگر آخرین پارامتر حذف شود یا برابر با 1 مشخص شود، VLOOKUP فرض می کند که ستون اول به ترتیب صعودی مرتب شده است، بنابراین جستجو در ردیفی متوقف می شود که بلافاصله قبل از خط حاوی مقدار بیشتر از مقدار مورد نظر قرار می گیرد … اگر چنین رشته ای یافت نشد، آخرین ردیف محدوده برگردانده می شود.

  

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

  

فرمول های گردش کار

VPR نوع I

VPR نوع II

نتیجه گیری برای فرمول های شکل I

1. از فرمول ها می توان برای توزیع مقادیر در محدوده ها استفاده کرد.
2. اگر ستون اول حاوی مقادیر تکراری باشد و به درستی مرتب شده باشد، آخرین ردیف با مقادیر تکراری برگردانده می شود.
3. اگر مقداری را جستجو کنید که آشکارا بزرگتر از آن چیزی است که ستون اول می تواند داشته باشد، می توانید به راحتی آخرین سطر جدول را بیابید که می تواند بسیار ارزشمند باشد.
4. این نمای فقط در صورتی خطای # N / A را برمی گرداند که مقداری کمتر یا مساوی با مقدار مورد نظر پیدا نکند.
5. درک اینکه اگر آرایه شما مرتب نشده باشد، درک اینکه فرمول مقادیر اشتباهی را برمی گرداند، بسیار دشوار است.

نتیجه گیری برای فرمول های نوع II

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

عملکرد VLOOKUP

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

• من به یک کامپیوتر قوی تر نیاز دارم.
• من به یک فرمول سریعتر نیاز دارم، برای مثال، بسیاری از مردم در مورد INDEX + MATCH می دانند که ظاهراً 5-10٪ سریعتر است.

و تعداد کمی از مردم فکر می کنند که به محض شروع استفاده از VLOOKUP از نوع I و اطمینان از مرتب شدن ستون اول به هر طریقی، سرعت VLOOKUP 57 برابر می شود. من با کلمات می نویسم - پنجاه و هفت بار! نه 57 درصد، بلکه 5700 درصد. من این واقعیت را کاملاً قابل اعتماد بررسی کردم.

راز چنین کار سریعی در این واقعیت نهفته است که یک الگوریتم جستجوی بسیار کارآمد را می توان روی یک آرایه مرتب شده اعمال کرد که به آن جستجوی دودویی می گویند (روش نصف کردن، روش دوگانگی). بنابراین VLOOKUP از نوع I آن را اعمال می کند و VLOOKUP از نوع II بدون هیچ بهینه سازی جستجو می کند. همین امر برای تابع MATCH که شامل یک پارامتر مشابه است و تابع LOOKUP که فقط روی آرایه های مرتب شده کار می کند و برای سازگاری با Lotus 1-2-3 در اکسل گنجانده شده است، صادق است.

معایب فرمول

معایب VLOOKUP واضح است: اولاً فقط در ستون اول آرایه مشخص شده جستجو می کند و ثانیاً فقط در سمت راست این ستون. و همانطور که می دانید، ممکن است اتفاق بیفتد که ستون حاوی اطلاعات لازم در سمت چپ ستونی باشد که در آن به دنبال آن هستیم. ترکیبی که قبلاً از فرمول های INDEX + MATCH ذکر شد، فاقد این اشکال است، که آن را به انعطاف پذیرترین راه حل برای استخراج داده ها از جداول در مقایسه با VLOOKUP (VLOOKUP) تبدیل می کند.

برخی از جنبه های به کارگیری فرمول در زندگی واقعی

جستجوی محدوده

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

جستجوی رشته های متنی

البته، VLOOKUP نه تنها به دنبال اعداد، بلکه به دنبال متن نیز می باشد. باید در نظر داشت که فرمول بین مورد شخصیت ها تمایز قائل نمی شود. اگر از حروف عام استفاده می کنید، می توانید جستجوی فازی را سازماندهی کنید. دو علامت عام وجود دارد: "?" - جایگزین هر یک از کاراکترها در یک رشته متنی، "*" - جایگزین هر تعداد از هر کاراکتر می شود.

فضاهای مبارزه

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

فرمت داده های مختلف

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

= VLOOKUP (−− D7; محصولات! $ A $ 2: $ C $ 5; 3; 0) - اگر D7 حاوی متن باشد و جدول حاوی اعداد باشد.

= VLOOKUP (D7 & ""); محصولات $ A $ 2: $ C $ 5; 3; 0) - و بالعکس.

به هر حال، می توانید متن را به چند روش به طور همزمان به یک عدد ترجمه کنید، انتخاب کنید:

• نفی مضاعف -D7.
• ضرب در یک D7 * 1.
• صفر اضافه D7 + 0.
• بالا بردن به توان اول D7 ^ 1.

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

چگونه # N / A را سرکوب کنیم

انجام این کار با عملکرد IFERROR بسیار راحت است.

به عنوان مثال: = IFERROR (VLOOKUP (D7; محصولات! $ A $ 2: $ C $ 5; 3; 0)؛ "").

اگر VLOOKUP کد خطا # N / A را برگرداند، IFERROR آن را قطع کرده و پارامتر 2 (در این مورد، یک رشته خالی) را جایگزین می کند، و اگر خطایی رخ نداد، این تابع وانمود می کند که اصلا وجود ندارد، اما فقط VLOOKUP وجود دارد که نتیجه عادی را برگرداند.

آرایه

اغلب فراموش می‌کنند که ارجاع آرایه را مطلق کنند و هنگام کشش آرایه شناور می‌شود. به یاد داشته باشید که به جای A2: C5 از $ A $ 2: $ C $ 5 استفاده کنید.

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

یک ایده حتی بهتر این است که این آرایه را به عنوان یک محدوده نامگذاری شده اعلام کنیم.

بسیاری از کاربران، هنگام تعیین یک آرایه، از ساختاری مانند A: C استفاده می کنند که کل ستون ها را مشخص می کند. این رویکرد حق وجود دارد، زیرا شما از پیگیری این حقیقت که آرایه شما شامل تمام رشته‌های مورد نیاز است نجات پیدا می‌کنید. اگر ردیف‌هایی را با آرایه اصلی به صفحه اضافه کنید، محدوده مشخص شده به صورت A: C نیازی به تنظیم ندارد. البته، این ساختار نحوی اکسل را مجبور می کند تا کمی بیشتر از تعیین دقیق محدوده کار کند، اما این سربار را می توان نادیده گرفت. ما در مورد صدم ثانیه صحبت می کنیم.

خوب، در آستانه نبوغ - برای مرتب کردن آرایه در فرم.

با استفاده از تابع COLUMN برای تعیین ستونی که باید استخراج شود

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

ایجاد یک کلید ترکیبی با & "|" &

اگر نیاز به جستجو در چندین ستون به طور همزمان باشد، لازم است یک کلید ترکیبی برای جستجو ایجاد شود. اگر مقدار بازگشتی متنی نبود (مانند قسمت "Code")، بلکه عددی بود، فرمول راحت‌تر SUMIFS برای این کار مناسب است و کلید ستون ترکیبی اصلاً مورد نیاز نخواهد بود.

این اولین مقاله من برای Lifehacker است. اگر دوست داشتید، از شما دعوت می کنم که بازدید کنید، و همچنین با خوشحالی در نظرات در مورد اسرار خود در استفاده از عملکرد VLOOKUP و موارد مشابه بخوانید. با تشکر.:)