یکی از محبوب ترین اپراتورها در بین کاربران اکسل عملکردی است جستجو. وظایف وی شامل تعیین موقعیت موقعیت یک عنصر در یک مجموعه داده معین است. این بیشترین فایده را هنگام استفاده در اپراتورهای دیگر به همراه دارد. بیایید ببینیم چه چیزی یک تابع را تشکیل می دهد. جستجو، و چگونه می توان از آن در عمل استفاده کرد.
کاربرد اپراتور جستجو
عملگر جستجو متعلق به دسته توابع است منابع و آرایه ها. این عنصر مشخص شده را در آرایه مشخص شده جستجو می کند و در یک سلول جداگانه تعداد موقعیت آن در این محدوده را صادر می کند. در واقع ، حتی نام آن هم این موضوع را نشان می دهد. همچنین ، این تابع ، هنگامی که در ترکیب با سایر اپراتورها مورد استفاده قرار می گیرد ، تعداد موقعیت یک عنصر خاص را برای پردازش بعدی این داده ها به آنها می گوید.
نحو اپراتور جستجو به نظر می رسد:
= جستجو (search_value؛ lookup_array؛ [match_type])
حالا هریک از این سه آرگومان را جداگانه در نظر بگیرید.
"به دنبال ارزش" - این عنصری است که باید یافت. این می تواند یک شکل متنی ، عددی داشته باشد و همچنین یک ارزش منطقی به خود بگیرد. ارجاع به سلولی که حاوی هر یک از مقادیر فوق باشد نیز می تواند به عنوان این آرگومان باشد.
آرایه را مشاهده کرد آدرس محدوده ای است که مقدار جستجو در آن قرار دارد. موقعیت این عنصر در این آرایه است که اپراتور باید تعیین کند جستجو.
نوع مسابقه نشانگر مطابقت دقیق برای جستجوی یا نادرست بودن آن است. این استدلال می تواند سه معنی داشته باشد: "1", "0" و "-1". به ارزش "0" اپراتور فقط یک مسابقه دقیق را جستجو می کند. اگر مقدار مشخص شده باشد "1"سپس در صورت عدم مطابقت دقیق جستجو عنصر نزدیک به آن را به ترتیب نزولی برمی گرداند. اگر مقدار مشخص شده باشد "-1"، در صورت عدم یافتن تطابق دقیق ، تابع عنصر نزدیک به آن را به ترتیب صعودی برمی گرداند. این مهم است که اگر شما به جستجوی مقدار دقیق نیستید ، بلکه برای مقدار تقریبی هستید به گونه ای که آرایه ای که مشاهده می کنید به ترتیب صعودی طبقه بندی شده است (نوع مطابقت "1") یا نزولی (نوع مسابقه) "-1").
استدلال نوع مسابقه مورد نیاز نیست در صورت لزوم ممکن است حذف شود. در این حالت ، مقدار پیش فرض آن است "1". استدلال را اعمال کنید نوع مسابقهاول از همه ، معقول است فقط هنگام پردازش مقادیر عددی و نه متن.
در صورت جستجو در تنظیمات مشخص شده نمی توانید مورد دلخواه را پیدا کنید ، اپراتور خطایی را در سلول نشان می دهد "# N / A".
هنگام انجام جستجو ، اپراتور بین ثبات های پرونده تفاوت قائل نمی شود. اگر چندین مسابقه دقیق در آرایه وجود دارد ، پس از آن جستجو موقعیت اولین آنها را در سلول نشان می دهد.
روش 1: مکان یک مورد را در طیف وسیعی از داده های متن نمایش دهید
بیایید یک نمونه از ساده ترین مورد را هنگام استفاده بررسی کنیم جستجو می توانید محل عنصر مشخص شده را در مجموعه داده های متن مشخص کنید. درمی یابیم که این کلمه در محدوده ای که نام محصولات در آن قرار دارد ، چه موقعیتی را اشغال می کند شکر.
- سلولی را انتخاب کنید که نتیجه پردازش شده نمایش داده شود. روی نماد کلیک کنید "درج عملکرد" نزدیک خط فرمول.
- شروع کردن جادوگران عملکرد. دسته باز "لیست کامل الفبایی" یا منابع و آرایه ها. در لیست اپراتورها به دنبال نام هستیم "جستجو". پس از یافتن و برجسته کردن آن ، بر روی دکمه کلیک کنید "خوب" در پایین پنجره
- پنجره آرگومان اپراتور فعال شده است جستجو. همانطور که مشاهده می کنید ، در این پنجره ، با توجه به تعداد آرگومان ها ، سه قسمت وجود دارد. ما باید آنها را پر کنیم.
از آنجا که باید موقعیت کلمه را پیدا کنیم شکر در محدوده ، سپس این نام را در قسمت فیلد رانده کنید "به دنبال ارزش".
در زمینه آرایه را مشاهده کرد شما باید مختصات دامنه خود را مشخص کنید. می توانید آن را به صورت دستی هدایت کنید ، اما با نگه داشتن دکمه سمت چپ ماوس ، تنظیم مکان نما را در این زمینه و انتخاب این آرایه بر روی برگه آسان تر می کنید. پس از آن آدرس آن در پنجره آرگومان ها نمایش داده می شود.
در زمینه سوم نوع مسابقه شماره را بگذارید "0"، از آنجا که ما با داده های متن کار خواهیم کرد ، و بنابراین به نتیجه ای دقیق نیاز داریم.
پس از نصب تمام داده ها ، بر روی دکمه کلیک کنید "خوب".
- برنامه محاسبه را انجام می دهد و شماره سریال موقعیت را نشان می دهد شکر در آرایه انتخاب شده در سلول که در مرحله اول این دستورالعمل مشخص کردیم. شماره موقعیت برابر خواهد بود "4".
درس: جادوگر ویژگی های اکسل
روش 2: اتوماسیون برنامه اپراتور جستجو
در بالا ، ابتدایی ترین مورد استفاده از اپراتور را بررسی کردیم جستجواما حتی می تواند خودکار باشد.
- برای راحتی ، دو فیلد دیگر به برگه اضافه کنید: تنظیم و "شماره". در زمینه تنظیم رانندگی به نام شما باید پیدا کنید. حالا بگذار گوشت. در زمینه "شماره" مکان نما را تنظیم کنید و به همان روشی که در بالا مورد بحث قرار گرفت ، به پنجره آرگومان های اپراتور بروید.
- در پنجره آرگومانهای عملکرد ، در قسمت "به دنبال ارزش" آدرس سلول را که کلمه در آن نوشته شده است نشان دهید گوشت. در مزارع آرایه را مشاهده کرد و نوع مسابقه همان داده های قبلی را با روش قبلی مشخص کنید - آدرس محدوده و شماره "0" بر این اساس پس از آن بر روی دکمه کلیک کنید "خوب".
- بعد از انجام اقدامات فوق ، در این زمینه "شماره" موقعیت کلمه نمایش داده می شود گوشت در محدوده انتخاب شده در این حالت برابر است با "3".
- این روش از این جهت خوب است که اگر می خواهیم موقعیت هر نام دیگری را بیابیم ، دیگر نیازی به بارگیری مجدد یا تغییر فرمول نیست. به اندازه کافی ساده در این زمینه تنظیم به جای کلمه قبلی کلمه جستجو جدید را وارد کنید. پردازش و خروجی نتیجه پس از این به طور خودکار اتفاق می افتد.
روش 3: برای عبارات عددی از عملگر FIND استفاده کنید
حالا بیایید ببینیم که چگونه می توانید استفاده کنید جستجو برای کار با عبارات عددی
وظیفه یافتن کالاهایی در میزان فروش 400 روبل یا نزدیکترین مقدار به این مقدار به ترتیب صعودی است.
- اول از همه ، ما باید موارد موجود در ستون را مرتب کنیم "مقدار" به ترتیب نزولی این ستون را انتخاب کرده و به برگه بروید "خانه". روی نماد کلیک کنید مرتب سازی و فیلتر کردنواقع در نوار بلوک "ویرایش". در لیستی که ظاهر می شود ، را انتخاب کنید "مرتب سازی از حداکثر به حداقل".
- پس از انجام مرتب سازی ، سلول را که در آن نتیجه نمایش داده می شود انتخاب کنید و پنجره آرگومان را به همان روشی که در روش اول مورد بحث قرار گرفت ، شروع کنید.
در زمینه "به دنبال ارزش" درایو در تعدادی "400". در زمینه آرایه را مشاهده کرد مختصات ستون را مشخص کنید "مقدار". در زمینه نوع مسابقه مقدار تعیین شده "-1"، از آنجا که ما در جستجوی مقادیر مساوی یا بیشتر از جستجو هستیم. پس از انجام تمام تنظیمات ، روی دکمه کلیک کنید "خوب".
- نتیجه پردازش در یک سلول قبلاً مشخص شده نمایش داده می شود. این موقعیت است. "3". با او مطابقت دارد "سیب زمینی". در واقع ، میزان درآمد حاصل از فروش این محصول به ترتیب صعودی نزدیک به عدد 400 است و بالغ بر 450 روبل است.
به طور مشابه ، می توانید نزدیکترین موقعیت را به جستجو کنید "400" به ترتیب نزولی فقط برای این کار باید داده ها را به ترتیب صعودی و در قسمت فیلترینگ فیلتر کنید نوع مسابقه آرگومانهای تابع مقدار تعیین می کنند "1".
درس: مرتب سازی و فیلتر کردن داده ها در اکسل
روش 4: استفاده در ترکیب با سایر اپراتورها
استفاده از این عملکرد با سایر اپراتورها به عنوان بخشی از یک فرمول پیچیده مؤثر است. بیشتر اوقات در رابطه با یک عملکرد از آن استفاده می شود صفحه اول. این آرگومان محتوای دامنه مشخص شده توسط ردیف یا شماره ستون آن را در سلول مشخص شده نشان می دهد. علاوه بر این ، شماره گذاری ، با توجه به اپراتور جستجو، نه نسبت به کل صفحه ، بلکه فقط در محدوده انجام می شود. نحو این عملکرد به شرح زیر است:
= INDEX (آرایه؛ row_number؛ ستون_بعد)
علاوه بر این ، اگر آرایه یک بعدی است ، می توانید فقط از دو آرگومان استفاده کنید: شماره خط یا شماره ستون.
ویژگی پیوند ویژه صفحه اول و جستجو در این واقعیت نهفته است که دومی را می توان به عنوان استدلال از اولین استفاده کرد ، یعنی موقعیت یک ردیف یا ستون را نشان می دهد.
بیایید نگاهی بیندازیم که چگونه می توان این کار را در عمل با استفاده از کل جدول انجام داد. وظیفه ما نمایش در فیلد اضافی ورق است "محصول" نام محصول ، کل درآمد حاصل از آن 350 روبل یا نزدیکترین مقدار به این مقدار به ترتیب نزولی است. این استدلال در قسمت مشخص شده است. "مقدار تقریبی درآمد در هر برگه".
- موارد را در یک ستون مرتب کنید "مقدار درآمد" صعودی برای انجام این کار ، ستون لازم را انتخاب کرده و در داخل آن قرار بگیرید "خانه"بر روی آیکون کلیک کنید مرتب سازی و فیلتر کردن، و سپس در منوی ظاهر شده ، روی مورد کلیک کنید "مرتب سازی از حداقل تا حداکثر".
- سلول را در قسمت انتخاب کنید "محصول" و تماس بگیر جادوگر ویژگی به روش معمول از طریق دکمه "درج عملکرد".
- در پنجره ای که باز می شود جادوگران عملکرد در رده منابع و آرایه ها به دنبال نام صفحه اولآن را انتخاب کرده و بر روی دکمه کلیک کنید "خوب".
- بعد ، پنجره ای باز می شود که گزینه ای از گزینه های اپراتور را ارائه می دهد صفحه اول: برای یک آرایه یا برای مرجع. به گزینه اول نیاز داریم. بنابراین ، ما در این پنجره تمام تنظیمات پیش فرض را رها می کنیم و روی دکمه کلیک می کنیم "خوب".
- پنجره آرگومانهای عملکرد باز می شود صفحه اول. در زمینه آرایه آدرس محدوده ای را که اپراتور مشخص می کند را مشخص کنید صفحه اول نام محصول را جستجو می کنید. در مورد ما ، این یک ستون است "نام محصول".
در زمینه شماره خط تابع تو در تو واقع خواهد شد جستجو. این امر باید با استفاده از نحوی که در ابتدای مقاله به آن اشاره شد ، به صورت دستی هدایت شود. بلافاصله نام عملکرد را ضبط کنید - "جستجو" بدون نقل قول سپس براکت را باز کنید. اولین استدلال برای این اپراتور است "به دنبال ارزش". در یک صفحه در قسمت مزرعه واقع شده است "مقدار تقریبی درآمد". مختصات سلول حاوی شماره را مشخص کنید 350. ما یک قطعه می گذاریم. استدلال دوم این است آرایه را مشاهده کرد. جستجو به محدوده ای که میزان درآمد در آن واقع شده است نگاه کنید و به یکی از نزدیک ترین 350 روبل نگاه کنید. بنابراین در این حالت مختصات ستون را مشخص کنید "مقدار درآمد". باز هم ما یک قطعه می گذاریم. استدلال سوم است نوع مسابقه. از آنجا که ما به دنبال عددی برابر با تعداد داده شده یا نزدیکترین کوچکتر خواهیم بود ، در اینجا عدد را تعیین می کنیم "1". براکت ها را می بندیم.
استدلال سوم برای عملکرد صفحه اول شماره ستون آن را خالی بگذارید. پس از آن بر روی دکمه کلیک کنید "خوب".
- همانطور که مشاهده می کنید ، عملکرد صفحه اول با استفاده از اپراتور جستجو در سلول از پیش تعیین شده ، نام را نشان می دهد چای. در واقع ، میزان فروش چای (300 روبل) به ترتیب نزولی نزدیک به مقدار 350 روبل از کلیه مقادیر موجود در جدول مورد پردازش نزدیکتر است.
- اگر عدد را در قسمت تغییر دهیم "مقدار تقریبی درآمد" به دیگری ، پس از آن مطالب مربوطه به طور خودکار براساس آن بازگو می شوند "محصول".
درس: عملکرد INDEX در اکسل
همانطور که مشاهده می کنید ، اپراتور جستجو یک تابع بسیار مناسب برای تعیین تعداد توالی یک عنصر مشخص در یک مجموعه داده است. اما در صورت استفاده از فرمولهای پیچیده ، فواید آن بسیار افزایش می یابد.
