نحوه استفاده از VLOOKUP ، INDEX ، ... در اکسل که باید بدانید

توابع VLOOKUP، INDEX، MATCH، ... توابع کاملاً پیچیده ای هستند اما در داده کاوی در اکسل موثر هستند . در این مقاله ، من به طور مفصل شما را راهنمایی می کنم که چگونه از VLOOKUP و توابع مربوطه به طور موثر استفاده کنید.

1. عملکرد VLOOKUP

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

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

که در آن :

+ Lookup_value : مقداری برای جستجو.

+ Table_array : دو یا چند ستون داده.

+ Col_index_num : تعداد ستون هایی که داده ها را می کشد.

+ Range_lookup : تعیین کنید که جستجو کاملاً دقیق (نادرست) است یا فقط نسبتاً دقیق (درست است یا حذف شده).

مثال : ما از تابع VLOOKUP برای یافتن پاداش هر کارمند استفاده می کنیم. مقداری که باید جستجو شود Cell Position C2 است. آرایه ای که برای شناسایی وجود دارد آرایه F1: G3 است. هنگام وارد کردن فرمول = VLOOKUP (C2 ، $ F $ 1: $ G 3،2،0 $) (1) نتیجه STAFF 2000 و ROOMER 5000 (2) است.

2. عملکرد INDEX

Usage : مرجعی را به سلول داخل آرایه بر اساس تعداد ردیف ها و ستون هایی که مشخص کرده اید برمی گرداند.

فرمول : = INDEX (آرایه ، شماره ردیف ، [شماره ستون])

که : آرایه (الزامی): یک محدوده سلول یا یک ثابت آرایه.

مثال : هنگامی که نیاز به دریافت مقدار ردیف اول ، ستون دوم جدول داریم ، از تابع INDEX استفاده می کنیم . آرایه آرایه ای است که باید تشخیص دهیم B2 است: C5؛ Row_num خط 1 است ؛ Column_num ستون 2 است. هنگام وارد کردن فرمول INDEX (B2: C5،1،2،1) (1) نتیجه "HEAD OF ROOM" (2) است.

3. عملکرد MATCH

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

فرمول : = MATCH (مقدار_نمایه ، آرایه_جستجو ، [نوع_تطبیق])

که در آن :

+ lookup_value (لازم است) : مقداری که می خواهید در آرایه جستجو مطابقت داشته باشد. تغییر عدد مقدار جستجو می تواند یک مقدار (عدد ، متن یا مقدار منطقی) یا ارجاع سلول به یک عدد ، متن یا مقدار منطقی باشد.

+ lookup_array (الزامی) : محدوده سلول هایی که باید جستجو شوند.

+ match_type (اختیاری) : شماره -1 ، 0 یا 1. آرگومان نوع match چگونگی مطابقت اکسل با مقدار جستجو را با مقادیر موجود در آرایه جستجو مشخص می کند. مقدار پیش فرض این آرگومان 1 است.

به عنوان مثال : هنگامی که باید بدانیم CLOTH در چه موقعیتی قرار دارد ، از تابع MATCH استفاده می کنیم . هنگام وارد کردن فرمول = MATCH (FF2 & "*" ، "B2: B7" ، 0) (1) ، نتیجه 3 (2) است.

3. عملکرد غیر مستقیم

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

فرمول : = INDIRECT (ref_text ، [a1])

که در آن :

+ Ref_text (الزامی) : ارجاع به سلول حاوی سبک مرجع A1 ، سبک مرجع R1C1 یا ارجاع به سلول به عنوان رشته متنی.

+ اگر متن مرجع مرجع سلول معتبری نیست ، INDIRECT مقدار # REF! مقدار خطا را برمی گرداند . .

+ اگر ref_text به کتاب کار دیگری (یک مرجع خارجی) مراجعه کند ، آن کتاب کار باید باز باشد. اگر کتاب کار منبع باز نباشد ، INDIRECT مقدار # REF! خطا را برمی گرداند . .

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

= INDIRECT ("B2"، TRUE) * INDIRECT ("C2"، TRUE) (1) نتیجه 700 * 100 = 70000 (2) را می دهد.

4. عملکرد OFFSET

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

فرمول : = OFFSET (مرجع ، ردیف ها ، ستون ها ، [ارتفاع] ، [عرض])

که در آن :

+ مرجع (لازم) : منطقه مرجعی که می خواهید فاصله مرجع را بر اساس آن قرار دهید. محدوده مرجع باید به یک سلول یا طیف وسیعی از سلولهای مجاور اشاره داشته باشد. در غیر این صورت OFFSET مقدار خطای #VALUE را برمی گرداند . .

+ ردیف ها (لازم است) : تعداد ردیف های بالا یا پایین ، که می خواهید سلول بالا سمت چپ به آنها اشاره کند. ردیف ها می توانند مثبت باشند (که به معنی زیر مرجع شروع است) یا منفی (که به معنای بالاتر از مرجع شروع است).

+ Cols (مورد نیاز) : تعداد ستون هایی در سمت چپ یا راست ، که می خواهید سلول در گوشه بالا سمت چپ نتیجه به آنها ارجاع داده شود. ستون ها می توانند مثبت یا منفی باشند.

+ ارتفاع (اختیاری) : ارتفاعی که به تعداد ردیف می خواهید برای مرجع برگشتی داشته باشید. ارتفاع باید یک عدد مثبت باشد.

+ Width (اختیاری) : عرض ، به تعداد ستون هایی که می خواهید برای مرجع برگشتی داشته باشید. عرض باید یک عدد مثبت باشد.

مثال : برای محلی سازی مقادیر از OFFSET استفاده می کنیم و سپس از تابع SUM برای محاسبه مجموع استفاده می کنیم. سلولی که ما به آن مراجعه می کنیم A1 است. در مقایسه با A1 ، ردیف های محدوده مرجع زیر 1 سلول است ، بنابراین ردیف ها 1 است. در مقایسه با A1 ، Cols سمت چپ 1 سلول است ، بنابراین Cols 1 است. ارتفاع ما 3 خط می گیریم. عرض 2 ستون بدست می آوریم. بنابراین نتیجه جمع کردن تمام مقادیر در منطقه 2025 است.

5. عملکرد TRANSPOSE

طریقه استفاده : یک محدوده افقی سلول را به یک دامنه عمودی و بالعکس تبدیل می کند ، یعنی ردیف ها را به ستون و ستون ها را به ردیف تبدیل می کند.

فرمول : = TRANSPOSE (آرایه)

در حال انجام

+ مرحله 1 : سلولهای خالی را انتخاب کنید.

+ مرحله 2 : Enter = TRANSPOSE (B2: C4).

+ مرحله 3 : محدوده سلول های اصلی را وارد کنید.

+ مرحله 4 : در آخر ، CTRL + SHIFT + ENTER را فشار دهید.

مثال : هنگامی که نیاز داریم لیست قیمت کل محصول را از حالت عمودی به افقی تبدیل کنیم ، از تابع TRANSPOSE با آرایه مقادیر B2 به C4 استفاده می کنیم. هنگام وارد کردن فرمول = TRANSPOSE (B2: C4) (1) نتیجه را مطابق شکل نشان می دهد (2).

6. عملکرد HYPERLINK

موارد استفاده : ایجاد پیوند پیوندی به سندی که در اینترانت یا اینترنت ذخیره شده است.

فرمول : = HYPERLINK (پیوند_محل ، [نام دوستانه]).

که در آن :

Link_location می تواند به مکانی در یک سند ، مانند یک سلول خاص یا محدوده نامگذاری شده در یک صفحه کار یا در یک کتاب کار اکسل ، یا به یک نشانک در یک سند Microsoft Word اشاره داشته باشد.

مسیر می تواند به یک فایل ذخیره شده در هارد دیسک باشد. این مسیر همچنین می تواند یک مسیر جهانی نامگذاری (UNC) در سرور باشد (در Microsoft Excel برای ویندوز).

مثال : پیوندهای طولانی و گیج کننده برای کاربر آزار دهنده هستند. بنابراین لطفاً برای سهولت استفاده از رابط از HYPERLINK استفاده کنید. وارد کردن فرمول = HYPERLINK (A1 ، "LINK GOOGLE") (1) منجر به پیوند GOOGLE (2) می شود.

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