Vlookup از چند شیت یا فایل

vlookup در چند شیت و فراخوانی داده ها

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

جستجو بین دو شیت

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

=VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

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

=VLOOKUP (A2 ,’محصول ۱′!A1:B13 , ۲,۰)

در واقع کافیه در حین نوشتن فرمول و انتخاب آرگومان دوم، روی شیت محصول ۱ کلیک کنیم و Table_Array رو محدوده A1:B13 انتخاب کنیم.آرگومان های تابع به شرح زیر است:Lookup_Value: مقداری که میخواهیم جستجو کنیم. در اینجا یعنی “اردیبهشت” یا سلول A2 که در اون کلمه اردیبهشت نوشته شده.Table_Array: جدولی که جستجو در اون انجام میشه. در اینجا داده های فروش مربوط به محصول ۱ در شیت به نام “محصول ۱” قرار گرفته. محدوده A1:B13Col_indx_num: شماره ستونی از محدوده جستجو که میخواهیم نمایش داده بشه. مقدار فروش ستون دوم از جدول هست پس این آرگومان عدد ۲ تعیین میشه.Range_Lookup: با گذاشتن مقدار صفر، جستجو دقیق انجام میدیم. در مورد این آرگومان میتونید در مقالات جستجوی بازه ای و Vlookup اطلاعات بیشتری کسب کنید.با همین روش میتونیم اطلاعات مربوط به هر یک از محصولات رو فراخوانی کنیم.  

جستجو بین دو فایل (Workbook)

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

=VLOOKUP(A2, [فروش.xlsx]اردیبهشت!$A$۲:$B$۶, ۲, FALSE)

Vlookup بین چند شیت با Iferror

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

=IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, “پیدا نشد“))

مثلا محصول ۱ در شش ماه اول و محصول ۲ در شش ماه دوم به فروش رسیده. حالا میخوایم در یک شیت همه اطلاعات رو تجمیع کنیم (شکل ۱).

شکل ۱- جستجو بین چند شیت با Vlookup و Iferror

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

=IFERROR (VLOOKUP (A2,’محصول ۱′!$A$۲:$B$۷,۲,۰) , IFERROR (VLOOKUP (A2,’محصول ۲′!$A$۲:$B$۷,۲,۰) ,”پیدا نشد”) )

با این فرمول اگر داده مورد نظر در شیت محصول ۱ پیدا نشه، جستجو در شیت محصول ۲ انجام میشه و اگر در شیت محصول ۲ هم مورد جستجو پیدا نشه و با خطا مواجه بشیم، عبارت “پیدا نشد” نمایش داده میشه.

جستجو بین شیت ها با استفاده از Indirect

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

• در یک محدوده اسم شیت های مورد نظر رو وارد کنیم:

شکل ۲- جستجو بین شیت ها – نامگذاری محدوده اسم شیت ها

• فرمول نویسی آرایه ای با کلید ترکیبی Ctrl+Shift+Enter ثبت میشه.
• ساختار داده ها (ترتیب ستون ها) در شیت ها باید مشابه باشند.
• چون از یک Table_Array در کل فرمول استفاده خواهیم کرد، بهتره بزرگترین محدوده بین شیت ها رو در نظر بگیریم که مطمئن باشیم همه داده ها پوشش داده شده.

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

=VLOOKUP(lookup_value, INDIRECT(“‘”&INDEX(Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” & Sheets_name & “‘!lookup_range“), lookup_value)>0), 0)) & “‘!table_array“), col_index_num, FALSE)

Sheets_name: اسم شیت هایی که در یک محدوده قرار گرفتهLookup_value: مقدار مورد جستجو. مثلا در اینجا نام ماهLookup_range: ستونی که مورد جستجو قرار میگیره و داده Lookup value در اون قرار دارهTable_array: محدوده مورد جستجو (جدول مورد نظر)Col_index_num: شماره ستون داده مورد نظر در این قسمت تعیین میشهاین فرمول رو با داده های فعلی بنویسیم به شکل زیر در میاد:

=VLOOKUP (A2, INDIRECT(“‘”&INDEX(Sheets_name, MATCH (1, –COUNTIF(INDIRECT(“‘”&Sheets_name& “‘!A2:A7”),A2) >0), 0) ) & “‘!A2:B7”),2, FALSE)

این فرمول چطور کار میکنه؟برای اینکه بتونیم درک کنیم که این فرمول چطور کار میکنه، باید به اجزا کوچکتر تجزیه کنیم.از داخلی ترین توابع یعنی Countif (Indirect(….)) شروع میکنیم:با استفاده از تابع Indirect اسم شیت ها رو به محدوده A2:A7 که محدوده جستجو هست میچسبونیم.

INDIRECT({“‘محصول ۱’!A2:A7″;”‘محصول ۲’!A2:A7”})

و حالا میتونیم مقدار مورد جستجو رو در این شیت ها بشماریم که ببینیم مقدار مورد نظر در کدوم شیت وجود داره. برای این کار از countif استفاده میکنیم.

COUNTIF ({“‘محصول ۱’!A2:A7″;”‘محصول ۲’!A2:A7”} , A2)

این فرمول تعداد A2 (داده مورد جستجو) رو در دو شیت محصول ۱ و محصول ۲ در محدوده A2:A7 محاسبه میکنه.نتیجه فرمول Countif بصورت زیر خواهد بود:

{1;0}

این یعنی مقدار A2 در شیت محصول ۱ یکبار تکرار شده و در شیت محصول ۲ اصلا وجود نداره.حالا برای اینکه نام شیتی که رکورد مورد نظر داخلش وجود داره رو پیدا کنیم، اول باید نتایج Countif که بزرگتر از ۰ هست رو پیدا کنیم. پس برای این کار نتیجه Countif رو با ۰ مقایسه میکنیم. و نتیجه بصورت False / True نمایش داده میشه.

COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0

حالا برای تبدیل مقادیر logical به مقدار عددی – –  رو قبل از تابع countif قرار میدیم.

–(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0)

نتیجه این فرمول مقدار ۰ و ۱ خواهد بود. در واقع مقدار ۱ تعیین میکنه داده مورد نظر در کدوم شیت وجود داره. در نتیجه زیر نشون میده که داده مورد نظر در شیت اول یعنی شیت محصول ۱ وجود داره و در شیت محصول ۲ (شیت دوم) وجود نداره.

{1;0}

حالا باید مکان این عدد ۱ رو تعیین کنیم. برای این کار از Match استفاده میکنیم. این فرمول مکان اولین عدد ۱ رو تعیین میکنه.

MATCH (1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0)

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

INDEX (Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0))

نتیجه فرمول زیر بصورت زیر خواهد بود:

INDEX {“محصول ۲″;”محصول ۱”}),۱)

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

INDIRECT(“‘”&INDEX(Sheets_name, MATCH(1, –(COUNTIF(INDIRECT(“‘” &Sheets_name& “‘!A2:A7”),A2)>0), 0)) & “‘!A2:B7”)

خروجی این فرمول بورت زیر خواهد بود: (نام شیت به همراه آدرس محدوده)

“‘محصول ۱’!A2:B7”

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

=VLOOKUP (A2, INDIRECT (“‘محصول ۱’!A2:B7”),۲, FALSE)

فراموش نکنید که این فرمول آرایه ای هست و باید با Ctrl+shift+Enter ثبت بشه.در ویدئو زیر نحوه محاسبه فرمول رو مشاهده میکنید:

دانلود فایل آموزش نحوه انجام Vlookup در چند شیت

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