مثال های VLOOKUP پیشرفته: جستجوی چند معیاره

در قسمت دوم آموزش ما در مورد تابع VPR (VLOOKUP) در اکسل، ما چند نمونه را تجزیه و تحلیل خواهیم کرد که به شما کمک می کند تا تمام قدرت را هدایت کنید VPR برای حل جاه طلبانه ترین وظایف اکسل. در مثال‌ها فرض می‌شود که شما قبلاً از نحوه عملکرد این ویژگی اطلاعات اولیه دارید. اگر نه، ممکن است علاقه مند باشید که با قسمت اول این آموزش شروع کنید، که نحو و کاربرد اصلی را توضیح می دهد. VPR. خوب، بیایید شروع کنیم.

• جستجو در اکسل با چندین معیار
• با استفاده از VLOOKUP مقادیر 2، 3 و غیره را استخراج می کنیم
• تمام تکرارهای مقدار مورد نظر را بازیابی کنید
• XNUMXD جستجو بر اساس سطر و ستون شناخته شده
• استفاده از چندین VLOOKUP در یک فرمول
• جایگزینی دینامیک داده ها از جداول مختلف

جستجو در اکسل با چندین معیار

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

مثال 1: جستجو بر اساس 2 معیار مختلف

فرض کنید ما لیستی از سفارشات داریم و می خواهیم پیدا کنیم مقدار کالا (تعداد)، بر اساس دو معیار - نام مشتری (مشتری) и продукта Название (تولید - محصول). موضوع از آنجایی پیچیده می شود که هر یک از خریداران چندین نوع کالا را سفارش داده اند، همانطور که در جدول زیر مشاهده می شود:

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

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- این فرمول نتیجه را برمی گرداند 15مربوط به محصول سیب ها، زیرا اولین مقداری است که مطابقت دارد.

یک راه حل ساده وجود دارد - یک ستون اضافی ایجاد کنید که در آن تمام معیارهای مورد نظر ترکیب شود. در مثال ما، اینها ستون ها هستند نام مشتری (مشتری) и продукта Название (تولید - محصول). فراموش نکنید که ستون ادغام شده باید همیشه سمت چپ ترین ستون در محدوده جستجو باشد، زیرا این ستون سمت چپ است که تابع VPR هنگامی که به دنبال یک ارزش است، به بالا نگاه می کند.

بنابراین، یک ستون کمکی به جدول اضافه می‌کنید و فرمول زیر را روی تمام سلول‌های آن کپی می‌کنید: =B2&C2. اگر می خواهید رشته خواناتر باشد، می توانید مقادیر ترکیبی را با فاصله جدا کنید: =B2&» «&C2. پس از آن می توانید از فرمول زیر استفاده کنید:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

سلول کجاست B1 حاوی مقدار الحاقی آرگومان است lookup_value (lookup_value) و 4 - بحث و جدل col_index_num (column_number)، یعنی تعداد ستون حاوی داده هایی که باید بازیابی شوند.

مثال 2: VLOOKUP بر اساس دو معیار با جدولی که در صفحه دیگری مشاهده می شود

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

مانند مثال قبلی، به یک ستون کمکی در جدول Lookup با مقادیر ترکیب شده نیاز دارید. این ستون باید سمت چپ ترین ستون در محدوده جستجو باشد.

بنابراین فرمول با VPR می تواند اینگونه باشد:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

در اینجا، ستون های B و C به ترتیب شامل نام مشتریان و نام محصول و پیوند هستند سفارشات!$A&$2:$D$2 جدولی را برای جستجو در یک برگه دیگر تعریف می کند.

برای خوانایی بیشتر فرمول، می توانید به محدوده نمایش یک نام بدهید و سپس فرمول بسیار ساده تر به نظر می رسد:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

برای اینکه فرمول کار کند، مقادیر در سمت چپ ترین ستون جدولی که به آن نگاه می کنید باید دقیقاً به همان روشی که در معیارهای جستجو ترکیب شده است، ترکیب شوند. در شکل بالا، مقادیر u2bu2band را با هم ترکیب کردیم، به همان روشی که باید در آرگومان اول تابع (BXNUMX& "" & CXNUMX) انجام دهید.

یاد آوردن! عملکرد VPR محدود به 255 کاراکتر، نمی تواند مقداری را که بیش از 255 کاراکتر طول دارد جستجو کند. این را در نظر داشته باشید و مطمئن شوید که طول مقدار مورد نظر از این حد تجاوز نکند.

موافقم که افزودن ستون کمکی ظریف ترین و همیشه قابل قبول ترین راه حل نیست. شما می توانید همین کار را بدون ستون کمکی انجام دهید، اما این به فرمول بسیار پیچیده تر با ترکیبی از توابع نیاز دارد شاخص (INDEX) و MATCH (بیشتر در معرض دید).

با استفاده از VLOOKUP مقادیر 2، 3 و غیره را استخراج می کنیم

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

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

ساده ترین راه اضافه کردن یک ستون کمکی قبل از ستون است نام مشتری و آن را با نام مشتریان با شماره تکرار هر نام پر کنید، به عنوان مثال، جان دو 1, جان دو 2 و غیره. ترفند شماره گذاری را با استفاده از تابع انجام خواهیم داد COUNTIF (COUNTIF)، با توجه به اینکه نام مشتریان در ستون B قرار دارد:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

پس از آن می توانید از عملکرد عادی استفاده کنید VPRبرای یافتن سفارش مورد نیاز مثلا:

• پیدا کردن دومین کالای سفارش داده شده توسط مشتری دن براون:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• پیدا کردن دومین کالای سفارش داده شده توسط مشتری دن براون:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

در واقع، همانطور که در شکل زیر نشان داده شده است، می توانید به جای متن، یک مرجع سلول را به عنوان مقدار جستجو وارد کنید:

اگر فقط به دنبال آن هستید 2-e تکرار، می توانید بدون ستون کمکی با ایجاد یک فرمول پیچیده تر این کار را انجام دهید:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

در این فرمول:

• 2 دلار فارنهایت - یک سلول حاوی نام خریدار (بدون تغییر است، لطفا توجه داشته باشید - پیوند مطلق است)؛
• B $ – ستون نام مشتری;
• Table4 – میز شما (این مکان همچنین می تواند یک محدوده معمولی باشد)؛
• C16 دلار - سلول انتهای جدول یا محدوده شما.

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

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

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

همانطور که اشاره شد VPR نمی تواند تمام مقادیر تکراری را از محدوده اسکن شده استخراج کند. برای انجام این کار، به فرمول کمی پیچیده تر نیاز دارید که از چندین تابع اکسل تشکیل شده است، مانند شاخص (فهرست مطالب)، کوچک (کوچک) و ROW (خط)

به عنوان مثال، فرمول زیر تمام تکرارهای مقدار سلول F2 را در محدوده B2:B16 پیدا می کند و نتیجه را از همان ردیف های ستون C برمی گرداند.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

این فرمول آرایه را در چندین سلول مجاور مانند سلول ها وارد کنید F4: F8همانطور که در شکل زیر نشان داده شده است. تعداد سلول ها باید برابر یا بیشتر از حداکثر تعداد تکرارهای ممکن مقدار جستجو شده باشد. فراموش نکنید که کلیک کنید Ctrl + Shift + Enterتا فرمول آرایه را به درستی وارد کنید.

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

1 بخش:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - مقدار سلول F2 را با هر یک از مقادیر در محدوده B2:B16 مقایسه کنید. اگر مطابقت پیدا شد، عبارت STRING(C2:C16)-1 شماره خط مربوطه (مقدار -1 به شما امکان می دهد خط سرصفحه را درج نکنید). اگر هیچ منطبقی وجود نداشته باشد، تابع IF (IF) یک رشته خالی را برمی گرداند.

نتیجه عملکرد IF (اگر) چنین آرایه افقی وجود خواهد داشت: {1,"",3,"",5,"","","","","","",12,"","",""}

2 بخش:

ROW()-3

СТРОКА()-3

در اینجا تابع ROW (LINE) به عنوان یک شمارنده اضافی عمل می کند. از آنجایی که فرمول در سلول های F4:F9 کپی می شود، عدد را کم می کنیم 3 از نتیجه تابع برای بدست آوردن مقدار 1 در سلول F4 (خط 4، 3 را کم کنید) تا به دست آید 2 در سلول F5 (خط 5، تفریق 3) و غیره.

3 بخش:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

عملکرد کوچک (SMALL) برمی گردد نه آه کوچکترین مقدار در آرایه داده در مورد ما، کدام موقعیت (از کوچکترین) برای بازگشت توسط تابع تعیین می شود ROW (خط) (بخش 2 را ببینید). بنابراین، برای یک سلول F4 تابع SMALL({آرایه},1) بازده دومین (کوچکترین) عنصر آرایه، به عنوان مثال 1. برای سلول F5 بازده دومین کوچکترین عنصر در آرایه، یعنی 3، و غیره

4 بخش:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

عملکرد شاخص (INDEX) به سادگی مقدار یک سلول خاص در یک آرایه را برمی گرداند C2: C16. برای سلول F4 تابع INDEX ($C$2:$C$16) بر خواهد گشت سیب هابرای F5 تابع INDEX ($C$2:$C$16) بر خواهد گشت شیرینی ها و الی آخر.

5 بخش:

IFERROR()

ЕСЛИОШИБКА()

در نهایت فرمول را داخل تابع قرار می دهیم IFERROR (IFERROR)، زیرا بعید است که از پیام خطا راضی باشید #AT (#N/A) اگر تعداد سلول‌هایی که فرمول در آنها کپی می‌شود کمتر از تعداد مقادیر تکراری در محدوده مورد مشاهده باشد.

XNUMXD جستجو بر اساس سطر و ستون شناخته شده

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

بنابراین، بیایید به جدول خود برویم و یک فرمول با یک تابع بنویسیم VPR، که اطلاعاتی در مورد هزینه فروش لیمو در ماه مارس پیدا خواهد کرد.

چندین راه برای انجام جستجوی XNUMXD وجود دارد. گزینه ها را بررسی کنید و بهترین گزینه را انتخاب کنید.

توابع VLOOKUP و MATCH

می توانید از یک سری توابع استفاده کنید VPR (VLOOKUP) و بیشتر در معرض (MATCH) برای یافتن مقدار در تقاطع فیلدها продукта Название (رشته) و ماه (ستون) آرایه مورد نظر:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

فرمول بالا یک تابع منظم است VPR، که به دنبال تطابق دقیق مقدار "Lemons" در سلول های A2 تا A9 است. اما از آنجایی که نمی‌دانید فروش مارس در کدام ستون است، نمی‌توانید شماره ستون را برای آرگومان تابع سوم تنظیم کنید. VPR. در عوض، تابع استفاده می شود بیشتر در معرضبرای تعریف این ستون

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

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

• ما به دنبال شخصیت های "مار" - استدلال هستیم lookup_value (مقدار_جستجو)؛
• جستجو در سلول های A1 تا I1 - آرگومان lookup_array (آرایه_جستجو)؛
• برگرداندن تطابق دقیق - آرگومان مطابقت_نوع (مطابقت_نوع).

با استفاده از 0 در آرگومان سوم، توابع را می گویید بیشتر در معرض به دنبال اولین مقداری باشید که دقیقاً با مقدار مورد نظر مطابقت دارد. این معادل مقدار است نادرست (FALSE) برای استدلال چهارم VPR.

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

عملکرد SUMPRODUCT

عملکرد SUMPRODUCT (SUMPRODUCT) مجموع حاصل از آرایه های انتخاب شده را برمی گرداند:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

توابع INDEX و MATCH

در مقاله بعدی من این توابع را با جزئیات توضیح خواهم داد، بنابراین در حال حاضر فقط می توانید این فرمول را کپی کنید:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

محدوده های نامگذاری شده و عملگر تقاطع

اگر به تمام فرمول های پیچیده اکسل علاقه ندارید، ممکن است این روش بصری و به یاد ماندنی را دوست داشته باشید:

1. جدول را انتخاب کنید، برگه را باز کنید فرمول (فرمول ها) و کلیک کنید ایجاد از انتخاب (ایجاد از انتخاب).
2. جعبه ها را علامت بزنید ردیف بالا (در خط بالا) و ستون چپ (در ستون سمت چپ). مایکروسافت اکسل از مقادیر موجود در ردیف بالا و ستون سمت چپ صفحه گسترده، نام‌هایی را به محدوده‌ها اختصاص می‌دهد. اکنون می توانید با استفاده از این نام ها مستقیماً بدون ایجاد فرمول جستجو کنید.
3. در هر سلول خالی، بنویسید =row_name column_name، برای مثال مانند این:

   =لیمو مارس

   … یا برعکس:

   = لیمو مار

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

هنگامی که نامی را وارد می کنید، Microsoft Excel یک راهنمای ابزار با لیستی از نام های منطبق را نشان می دهد، درست مانند زمانی که یک فرمول را وارد می کنید.

4. رسانه ها و مطبوعات وارد و نتیجه را بررسی کنید

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

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

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

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

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

1. یک تابع بنویسید VPR، که نام محصول را در جدول پیدا می کند جدول جستجو 1با استفاده از SKU، به عنوان مقدار مورد نظر:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   اینجا New_SKU - محدوده نامگذاری شده $A:$B در جدول جدول جستجو 1, 2 – این ستون B است که حاوی نام کالاها است (تصویر بالا را ببینید)

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

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   اینجا قیمت - محدوده نامگذاری شده $A:$C در جدول جدول جستجو 2, 3 ستون C حاوی قیمت است.

شکل زیر نتیجه ای که با فرمولی که ایجاد کردیم را نشان می دهد:

جایگزینی دینامیک داده ها از جداول مختلف با استفاده از VLOOKUP و INDIRECT

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

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

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

اگر فقط دو گزارش از این قبیل دارید، می توانید از یک فرمول ساده و شرم آور با توابع استفاده کنید VPR и IF (IF) برای انتخاب گزارش مورد نظر برای جستجو:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

جایی که:

• $ 2 دلار یک سلول حاوی نام محصول است. توجه داشته باشید که ما در اینجا از ارجاعات مطلق استفاده می کنیم تا هنگام کپی کردن فرمول در سلول های دیگر، مقدار جستجو را تغییر دهیم.
• D3 دلار یک سلول با نام منطقه است. ما از یک مرجع ستون مطلق و یک مرجع ردیف نسبی استفاده می کنیم زیرا قصد داریم فرمول را در سلول های دیگر همان ستون کپی کنیم.
• FL_Sales и CA_Sales - نام جداول (یا محدوده های نامگذاری شده) که حاوی گزارش های فروش مربوطه است. برای مثال، می‌توانید از نام‌های معمول برگه‌ها و مراجع محدوده سلولی استفاده کنید 'ورق FL'!$A$3:$B$10، اما محدوده های نامگذاری شده بسیار راحت تر هستند.

با این حال، زمانی که چنین جداول زیادی وجود دارد، تابع IF بهترین راه حل نیست در عوض، می توانید از تابع استفاده کنید غیر مستقیم (INDIRECT) تا محدوده جستجوی مورد نظر را برگردانید.

همانطور که احتمالا می دانید، تابع غیر مستقیم برای برگرداندن پیوند داده شده توسط یک رشته متن استفاده می شود، که دقیقاً همان چیزی است که اکنون به آن نیاز داریم. بنابراین، به طور جسورانه در فرمول بالا عبارت را با تابع جایگزین کنید IF برای پیوند با تابع غیر مستقیم. در اینجا یک ترکیب است VPR и غیر مستقیم عالی کار می کند با:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

جایی که:

• $ 2 دلار – این یک سلول با نام محصول است، به دلیل پیوند مطلق بدون تغییر است.
• D3 دلار سلولی است که اولین قسمت نام منطقه را شامل می شود. در مثال ما، این FL.
• _حراجی - قسمت مشترک نام همه محدوده ها یا جداول نامگذاری شده. هنگامی که با مقدار موجود در سلول D3 ترکیب می شود، نام کاملاً واجد شرایط محدوده مورد نیاز را تشکیل می دهد. در زیر برخی از جزئیات برای کسانی که در این عملکرد تازه کار هستند آورده شده است غیر مستقیم.

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

ابتدا، اجازه دهید نحو تابع را به شما یادآوری کنم غیر مستقیم (غیر مستقیم):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

اولین آرگومان می تواند یک مرجع سلول (سبک A1 یا R1C1)، یک نام محدوده یا یک رشته متن باشد. آرگومان دوم تعیین می کند که چه سبک پیوندی در آرگومان اول وجود دارد:

• A1اگر استدلال باشد کد واقعی (درست) یا مشخص نشده است.
• R1C1، اگر FAS E (نادرست).

در مورد ما، پیوند دارای سبک است A1، بنابراین می توانید استدلال دوم را کنار بگذارید و روی اولی تمرکز کنید.

پس بیایید به گزارش های فروش خود برگردیم. اگر به خاطر داشته باشید، هر گزارش یک جدول جداگانه است که در یک برگه جداگانه قرار دارد. برای اینکه فرمول به درستی کار کند، باید جداول (یا محدوده) خود را نامگذاری کنید، و همه نام ها باید یک قسمت مشترک داشته باشند. به عنوان مثال، مانند این: CA_Sales, FL_Sales, TX_Sales و غیره همانطور که می بینید، "_Sales" در همه نام ها وجود دارد.

عملکرد غیر مستقیم مقدار موجود در ستون D و رشته متن "_Sales" را به هم وصل می کند و بدین ترتیب می گوید VPR در کدام جدول جستجو کنید اگر سلول D3 حاوی مقدار "FL" باشد، فرمول جدول را جستجو می کند FL_Sales، اگر "CA" - در جدول CA_Sales و الی آخر.

نتیجه توابع VPR и غیر مستقیم به شرح زیر خواهد بود:

اگر داده ها در کتاب های مختلف اکسل قرار دارند، باید نام کتاب را قبل از محدوده نامگذاری شده اضافه کنید، به عنوان مثال:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

اگر تابع غیر مستقیم به کتاب کار دیگری اشاره دارد، آن کتاب کار باید باز باشد. اگر بسته باشد، تابع یک خطا گزارش می دهد. #مرجع! (#SSYL!).