فهرست
دیروز در ماراتن 30 عملکرد اکسل در 30 روز ما از عملکرد لذت بردیم REPT (تکرار) با ایجاد نمودار در داخل یک سلول و استفاده از آن برای شمارش ساده. دوشنبه است و بار دیگر وقت آن رسیده که کلاه فکری خود را به سر بگذاریم.
در روز بیست و یکم ماراتن به بررسی عملکرد می پردازیم جستجو (چشم انداز). این یک دوست صمیمی است VLOOKUP (VLOOKUP) و HLOOKUP (GPR)، اما کمی متفاوت عمل می کند.
بنابراین، بیایید تئوری را مطالعه کنیم و تابع را در عمل آزمایش کنیم جستجو (چشم انداز). اگر اطلاعات اضافی یا مثال هایی در مورد نحوه استفاده از این ویژگی دارید، لطفاً آنها را در نظرات به اشتراک بگذارید.
عملکرد 16: جستجو
عملکرد جستجو (LOOKUP) مقداری را از یک سطر، یک ستون یا از یک آرایه برمی گرداند.
چگونه می توانم از تابع LOOKUP استفاده کنم؟
عملکرد جستجو (LOOKUP) بسته به مقداری که به دنبال آن هستید، نتیجه را برمی گرداند. با کمک آن شما قادر خواهید بود:
- آخرین مقدار را در یک ستون پیدا کنید.
- آخرین ماه با فروش منفی را پیدا کنید.
- تبدیل موفقیت دانش آموز از درصد به نمره حرف.
نحو جستجو
عملکرد جستجو (LOOKUP) دو شکل نحوی دارد – برداری و آرایه. در شکل برداری، تابع به دنبال مقدار در ستون یا ردیف داده شده و در شکل آرایه، به دنبال مقدار در سطر یا ستون اول آرایه می گردد.
فرم برداری دارای نحو زیر است:
LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)
- lookup_value (lookup_value) – می تواند متن، عدد، بولی، نام یا پیوند باشد.
- lookup_vector (lookup_vector) - محدوده ای متشکل از یک ردیف یا یک ستون.
- نتیجه_بردار (نتیجه_بردار) - محدوده ای متشکل از یک ردیف یا یک ستون.
- محدوده های آرگومان lookup_vector (lookup_vector) و نتیجه_بردار (نتیجه_بردار) باید هم اندازه باشد.
فرم آرایه دارای نحو زیر است:
LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)
- lookup_value (lookup_value) – می تواند متن، عدد، بولی، نام یا پیوند باشد.
- جستجو با توجه به ابعاد آرایه انجام می شود:
- اگر آرایه تعداد ستون‌های بیشتری نسبت به ردیف‌ها داشته باشد، جستجو در سطر اول انجام می‌شود.
- اگر تعداد سطرها و ستون ها یکسان باشد یا ردیف های بیشتری وجود داشته باشد، جستجو در ستون اول انجام می شود.
- تابع آخرین مقدار را از سطر/ستون پیدا شده برمی گرداند.
Traps LOOKUP (نمایش)
- در عمل جستجو (Browse) هیچ گزینه ای برای جستجوی یک تطابق دقیق وجود ندارد VLOOKUP (VLOOKUP) و در HLOOKUP (GPR). اگر مقدار جستجو وجود نداشته باشد، تابع حداکثر مقداری را که از مقدار جستجو تجاوز نمی کند، برمی گرداند.
- آرایه یا بردار مورد جستجو باید به ترتیب صعودی مرتب شوند، در غیر این صورت ممکن است تابع نتیجه نادرستی را نشان دهد.
- اگر اولین مقدار در آرایه/بردار جستجو شده بزرگتر از مقدار جستجو باشد، تابع یک پیام خطا ایجاد می کند. #AT (#N/A).
مثال 1: یافتن آخرین مقدار در یک ستون
در قالب یک تابع آرایه جستجو (LOOKUP) را می توان برای یافتن آخرین مقدار در یک ستون استفاده کرد.
ارزش نقل قول کمک اکسل 9,99999999999999E + 307 به عنوان بزرگترین عددی که می توان در یک سلول نوشت. در فرمول ما به عنوان مقدار مورد نظر تنظیم می شود. فرض بر این است که چنین عدد بزرگی پیدا نخواهد شد، بنابراین تابع آخرین مقدار را در ستون D برمی گرداند.
در این مثال، اعداد در ستون D مجاز نیستند مرتب شوند، علاوه بر این، ممکن است مقادیر متنی مشاهده شوند.
=LOOKUP(9.99999999999999E+307,D:D)
=ПРОСМОТР(9,99999999999999E+307;D:D)
مثال 2: آخرین ماه را با مقدار منفی پیدا کنید
در این مثال از شکل برداری استفاده خواهیم کرد جستجو (چشم انداز). ستون D شامل مقادیر فروش و ستون E شامل نام ماه ها است. در برخی ماه‌ها، همه چیز خوب پیش نمی‌رفت و اعداد منفی در سلول‌های دارای ارزش فروش ظاهر می‌شد.
برای پیدا کردن ماه آخر با یک عدد منفی، فرمول با جستجو (LOOKUP) برای هر ارزش فروش بررسی می کند که کمتر از آن باشد 0 (نابرابری در فرمول). بعد، تقسیم می کنیم 1 در نتیجه، ما به یکی از این دو نتیجه می رسیم 1، یا یک پیام خطا #DIV/0 (#SECTION/0).
از آنجایی که مقدار مورد نظر است 2 یافت نشد، تابع آخرین مورد پیدا شده را انتخاب می کند 1و مقدار مربوطه را از ستون E برگردانید.
=LOOKUP(2,1/(D2:D8<0),E2:E8)
=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)
شرح: در این فرمول به جای استدلال lookup_vector (lookup_vector) عبارت جایگزین شد 1/(D2:D8<0)، که آرایه ای را در رم کامپیوتر تشکیل می دهد که شامل 1 و مقادیر خطا #DIV/0 (#SECTION/0). 1 نشان می دهد که سلول مربوطه در محدوده D2:D8 حاوی مقدار کمتر از 0، و خطا #DIV/0 (#DIV/0) - چه چیزی بزرگتر یا مساوی است 0. در نتیجه وظیفه ما این است که آخرین مورد را پیدا کنیم 1 در آرایه مجازی ایجاد شده و بر این اساس نام ماه را از محدوده E2:E8 برگردانید.
مثال 3: تبدیل پیشرفت دانش آموز از درصد به نمره حرف
قبلاً، ما قبلاً یک مشکل مشابه را با استفاده از تابع حل کرده ایم VLOOKUP (VPR). امروز از تابع استفاده خواهیم کرد جستجو (نمایش) به صورت برداری برای تبدیل پیشرفت دانش آموز از درصد به نمره حرف. بر خلاف VLOOKUP (VLOOKUP) برای یک تابع جستجو (نمایش) فرقی نمی کند که درصدها در ستون اول جدول باشد. شما می توانید مطلقاً هر ستونی را انتخاب کنید.
در مثال زیر، نمرات در ستون D قرار دارند و به ترتیب صعودی مرتب شده اند و حروف مربوطه آنها در ستون C در سمت چپ ستون مورد جستجو قرار دارند.
=LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)
