فرمول های جستجو در اکسل | آموزش فرمول HLOOKUP در اکسل

فرمول HLOOKUP برای جستجوی افقی (Horizontal Lookup) در یک ردیف خاص از جدول استفاده می‌شود. این فرمول مقدار مشخصی را در سطر اول محدوده جستجو کرده و مقدار متناظر از یک سطر دیگر را برمی‌گرداند.

ساختار فرمول HLOOKUP

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

lookup_value: مقدار مورد جستجو (مثلاً یک کد یا نام)

table_array: محدوده‌ای که در آن جستجو انجام می‌شود

row_index_num: شماره سطری که مقدار موردنظر از آن بازگردانده می‌شود

range_lookup: مانند فرمول VLOOKUP عمل می‌کند

مثال ۱: یافتن قیمت یک محصول از جدول افقی

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

می‌خواهیم قیمت “تبلت” را پیدا کنیم:

=HLOOKUP(“تبلت”, A1:E3, 2, FALSE)

این فرمول مقدار “تبلت” را در سطر اول (A1:E1) پیدا کرده و مقدار ردیف دوم (A2:E2) را برمی‌گرداند.
خروجی: 10,000,000

نکات مهم درباره HLOOKUP

جستجو فقط در سطر اول محدوده انجام می‌شود و مقدار متناظر از سطرهای پایین‌تر بازمی‌گردد.

برای جستجوهای عمودی (VLOOKUP) بهتر است از INDEX + MATCH استفاده شود.

اگر مقدار جستجو در سطر اول یافت نشود، فرمول خطای #N/A برمی‌گرداند. برای مطالعه نکات بیشتر میتوانید به فرمول های کاربردی اکسل و  فرمول های منطقی در اکسل مراجعه کنید.

فرمول های جستجو در اکسل | آموزش فرمول XLOOKUP در اکسل

فرمول XLOOKUP یکی از جدیدترین و پیشرفته‌ترین توابع جستجو در اکسل است که جایگزین VLOOKUP و HLOOKUP شده است. این تابع قابلیت جستجوی افقی و عمودی دارد و نسبت به VLOOKUP انعطاف‌پذیرتر است.

ساختار فرمول XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

lookup_value: مقدار مورد جستجو

lookup_array: محدوده‌ای که مقدار در آن جستجو می‌شود

return_array: محدوده‌ای که مقدار متناظر از آن بازگردانده می‌شود

if_not_found (اختیاری): مقدار جایگزین در صورت پیدا نشدن مقدار

match_mode :(اختیاری)

0 پیش‌فرض: جستجوی دقیق
1- نزدیک‌ترین مقدار کوچکتر
1 نزدیک‌ترین مقدار بزرگ‌تر
2 : جستجوی با wildcard مثلاً * و ?

search_mode ( اختیاری)

1 پیش‌فرض : جستجو از بالا به پایین

-1 جستجو از پایین به بالا

2 جستجوی دودویی (فقط برای داده‌های مرتب‌شده

مثال کاربردی فرمول های جستجو در اکسل

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

حالا می‌خواهیم در سلول E2 کد محصول را وارد کنیم و در سلول‌های F2 و G2 نام و قیمت آن نمایش داده شود.

1. یافتن نام محصول بر اساس کد محصول

=XLOOKUP(E2, A2:A6, B2:B6, “موجود نیست”)

این فرمول مقدار E2 را در A2:A6 جستجو کرده و مقدار متناظر از B2:B6 را نمایش می‌دهد.
اگر مقدار پیدا نشود، متن “موجود نیست” نمایش داده می‌شود.

۲. یافتن قیمت محصول بر اساس کد محصول

=XLOOKUP(E2, A2:A6, C2:C6, “نامشخص”)

مقدار E2 در محدوده A2:A6 جستجو شده و مقدار مربوطه از C2:C6 بازگردانده می‌شود. اگر مقدار پیدا نشود، مقدار “نامشخص” نمایش داده می‌شود.
نتیجه جستجو

نتیجه: اگر مقدار E2 را 102 بگذاریم، اکسل مقدار “گوشی موبایل” را در F2 و مقدار 15,000,000 را در G2 نمایش می‌دهد.

مزایای فرمول XLOOKUP نسبت به فرمول VLOOKUP

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

قابلیت جستجوی از پایین به بالا (search_mode = -1)

قابلیت جایگزین کردن مقدار در صورت عدم وجود داده (if_not_found)

امکان جستجو با wildcard برای تطبیق متون مشابه

مثال پیشرفته‌تر: یافتن قیمت نزدیک‌ترین محصول موجود

=XLOOKUP(E2, A2:A6, C2:C6, “نامشخص”, -1)

اگر کد دقیق یافت نشد، نزدیک‌ترین مقدار کوچکتر انتخاب می‌شود.

فرمول های جستجو در اکسل | آموزش فرمول CHOOSEدر اکسل

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

ساختار فرمول CHOOSE

CHOOSE(index_num, value1, value2, value3, …)

index_num: عددی که مشخص می‌کند کدام مقدار انتخاب شود (مثلاً ۱ برای مقدار اول، ۲ برای مقدار دوم و …)

value1, value2, …: مقادیر موجود در لیست

index_num باید عدد صحیح بین ۱ تا تعداد مقادیر باشد، در غیر این صورت خطای #VALUE! برمی‌گردد.

مثال کاربردی فرمول های جستجو در اکسل

فرض کنید در یک شرکت، روزهای هفته را بر اساس شماره روز ذخیره کرده‌ایم و می‌خواهیم نام روز را از طریق CHOOSE نمایش دهیم. این کار با فرمول CHOOSE انجام می‌شود.

فرمول CHOOSE برای نمایش نام روز بر اساس شماره

در سلول B2 فرمول زیر را وارد کنید و به پایین کپی کنید:

=CHOOSE(A2, “شنبه”, “یکشنبه”, “دوشنبه”, “سه‌شنبه”, “چهارشنبه”, “پنج‌شنبه”, “جمعه”)

مقدار A2 مشخص می‌کند کدام مقدار از لیست انتخاب شود.

اگر مقدار A2 برابر 1 باشد، مقدار “شنبه” نمایش داده می‌شود.

اگر مقدار A2 برابر 5 باشد، مقدار “چهارشنبه” نمایش داده می‌شود.

مزایای فرمول CHOOSE

ساده و خوانا برای انتخاب مقدار از یک لیست کوچک

مناسب برای دسته‌بندی داده‌ها

می‌توان مقادیر را از سلول‌های دیگر دریافت کرد (مثلاً B2, C2, D2 به جای مقدار مستقیم)

نکته: اگر به جستجو در لیست‌های بزرگ نیاز دارید، بهتر است از VLOOKUP یا XLOOKUP استفاده کنید.

فرمول های جستجو در اکسل | آموزش فرمول INDEX در اکسل

فرمول INDEX یکی از قدرتمندترین توابع جستجو در اکسل است که مقدار یک سلول را بر اساس ردیف و ستون مشخص‌شده از یک محدوده بازمی‌گرداند. این تابع در ترکیب با MATCH برای جستجوهای پیشرفته استفاده می‌شود.

ساختار فرمول INDEX

INDEX(array, row_num, [column_num])

Array: محدوده‌ای که مقدار از آن استخراج می‌شود

row_num: شماره ردیف مقدار موردنظر در محدوده

column_num :(اختیاری): شماره ستون مقدار موردنظر (درصورتی‌که محدوده شامل چندین ستون باشد)

اگر column_num مشخص نشود، INDEX مقدار ستون اول را برمی‌گرداند.

مثال ۱: استخراج مقدار از یک محدوده تک‌ستونی

فرض کنید لیستی از نام دانش‌آموزان داریم و می‌خواهیم نام دانش‌آموزی که در ردیف سوم است را استخراج کنیم.

استخراج نام دانش‌آموز در ردیف سوم

=INDEX(B2:B6, 3)

این فرمول مقدار موجود در ردیف سوم ستون B ( یعنی “سارا”) را بازمی‌گرداند.

مثال ۲: استخراج مقدار از یک جدول دو بعدی

فرض کنید یک جدول قیمت محصولات داریم و می‌خواهیم مقدار قیمت مربوط به یک ردیف و ستون خاص را دریافت کنیم.

یافتن قیمت محصول در ردیف ۴

=INDEX(B2:C6, 4, 2)

این فرمول مقدار ردیف ۴ و ستون ۲ را در محدوده B2:C6 برمی‌گرداند که 5,000,000 (قیمت ساعت هوشمند) است.

فرمول های جستجو در اکسل | آموزش فرمول MATCH در اکسل

فرمول MATCH برای پیدا کردن موقعیت یک مقدار در یک محدوده (آرایه) استفاده می‌شود. برخلاف VLOOKUP و XLOOKUP که مقدار متناظر را برمی‌گردانند، MATCH فقط شماره ردیف یا ستون مقدار را می‌دهد.

ساختار فرمول MATCH

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: مقدار مورد جستجو

lookup_array: محدوده‌ای که مقدار در آن جستجو می‌شود

match_type : اختیاری

1 نزدیک‌ترین مقدار کوچک‌تر (محدوده باید صعودی مرتب شده باشد)

0 جستجوی دقیق (رایج‌ترین روش)

1- نزدیک‌ترین مقدار بزرگ‌تر (محدوده باید نزولی مرتب شده باشد)

مثال کاربردی از فرمول MATCH

فرض کنید لیستی از کدهای محصول داریم و می‌خواهیم شماره ردیف یک کد خاص را پیدا کنیم.

یافتن شماره ردیف یک کد محصول

=MATCH(103, B2:B6, 0)

این فرمول مقدار 103 را در محدوده B2:B6 جستجو کرده و موقعیت آن را بازمی‌گرداند.

خروجی 3 خواهد بود، زیرا مقدار 103 در سومین ردیف محدوده (B4) قرار دارد.

مثال پیشرفته: استفاده از MATCH در INDEX

فرمول MATCH معمولاً در ترکیب با فرمول INDEX برای جستجوهای پیشرفته استفاده می‌شود.
جدول قیمت محصولات:

یافتن قیمت محصول با INDEX و MATCH

اگر بخواهیم قیمت محصول 103 را پیدا کنیم:

=INDEX(C2:C6, MATCH(103, A2:A6, 0))

مزایای فرمول MATCH

سریع‌تر و انعطاف‌پذیرتر از فرمول VLOOKUP

می‌توان آن را با INDEX ترکیب کرد تا جستجوی پویا داشته باشیم

قابلیت جستجو در ردیف‌ها و ستون‌ها (برخلاف VLOOKUP که فقط در ستون اول جستجو می‌کند)

فرمول های جستجو در اکسل | آموزش فرمول OFFSET در اکسل

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

ساختار فرمول Offset

OFFSET(reference, rows, cols, [height], [width])

Reference: سلول یا محدوده مرجع (نقطه شروع)

rows:تعداد ردیف‌هایی که باید به بالا (+) یا پایین (-) حرکت کند

cols: تعداد ستون‌هایی که باید به راست (+) یا چپ (-) حرکت کند

height (اختیاری): ارتفاع محدوده خروجی (تعداد ردیف‌ها)

width (اختیاری): عرض محدوده خروجی (تعداد ستون‌ها)

نکته: اگر height و width مشخص نشوند، مقدار پیش‌فرض ۱×۱ (یک سلول) در نظر گرفته می‌شود.

مثال ۱: یافتن مقدار یک سلول با جابجایی از نقطه مرجع

فرض کنید جدولی از نمرات دانش‌آموزان داریم:

یافتن نمره فیزیک رضا با OFFSET

=OFFSET(B1, 2, 1)

توضیح:

نقطه شروع 1B (سرستون درس ریاضی)

2 ردیف پایین‌تر → 3B (نمره ریاضی رضا)

1 ستون سمت راست → 3C (نمره فیزیک رضا)

خروجی:16

مثال ۲: ایجاد محدوده دینامیک برای میانگین نمرات

اگر بخواهیم میانگین نمرات سارا را محاسبه کنیم:

=AVERAGE(OFFSET(B3, 0, 0, 1, 3))

توضیح:

نقطه شروع B3 (ریاضی سارا)

حرکت 0 ردیف و 0 ستون

محدوده به اندازه 1 ردیف و 3 ستون انتخاب می‌شود (B3:D3)

خروجی (20+19+18)/3 =19

مزایای فرمول OFFSET

محدوده‌های پویا ایجاد می‌کند

در ترکیب با SUM و AVERAGE بسیار کاربردی است

برای ایجاد لیست‌های خودکار و تحلیل‌های پویا مفید است