Raqamni qidirish uchun Excelda bo'lgani kabi. Qarorni qidirish Excel-da qidirish - Xizmatni qidirish Excel-dagi echimlarni qidirish

Muayyan mezonlarni qondiradigan Eksel stol qiymatlarini optimallashtirish murakkab jarayon bo'lishi mumkin. Yaxshiyamki, Microsoft qo'shimchalarni taklif qiladi Muammolarni hal qilishraqamli optimallashtirish uchun. Garchi ushbu xizmat Barcha muammolarni hal qila olmaydi, u vositasi sifatida foydali bo'lishi mumkin agar .. bo'lsa nima bo'ladi.Ushbu post qo'shimchalarga bag'ishlangan Muammolarni hal qilishexcel-da.

Sistratura Muammolarni hal qilishexcelning barcha versiyalarida mavjud. E'tibor bering, ekran rasmlari sizning versiyangizga mos kelmasligi mumkin. Ba'zi funktsiyalar Sigoterhod versiyasiga qarab o'z joylarini o'zgartirishi mumkinligiga qaramay, funktsional imkoniyat deyarli o'zgarmadi.

Echimlarni qidirish nima

Qarorlarni qidirish - Excel qo'shimchalari, bu maqsadli hujayralarning qiymatlarini o'zgartirish orqali echimni topishga yordam beradi. Maqsad ma'lum maqsad qiymatini minimallashtirish, maksimal darajada oshirish yoki yaratishi mumkin. Muammo kiritilgan kirish mezonlarini yoki foydalanuvchi tomonidan belgilangan cheklovlarni sozlash orqali hal qilinadi.

Excel echimlarni qidirishda

Sistratura Qarorlarni qidirishexcel bilan birga keladi, ammo sukut o'chirilgan. Uni yoqish uchun yorliqqa o'ting. Fayliguruh Parametrlar.Dialog oynasida dialog oynasida Parametrlar,tanlamoq Qo'shish -\u003e Boshqarish: qo'shishExcel -\u003e boring.Oynada Sistraturamaydonga qarama-qarshi belgi qo'ying Eritma eritmasijmem. OK.

Endi yorliqda Ma'lumotlarpaydo bo'ldi yangi guruh Tahliltugmachasini bosing Qidiruv echimlari.

Solutionlarni qanday izlashning misoli

Ushbu xabar foydalanish misoliga asoslanadi. . Fayl barcha Excel versiyalariga mos keladi.

Muammoni aniqlash

Aytaylik, bizda 8 ochkodan iborat ma'lumotlar to'plami bor, ularning har biri o'z qiymatiga mos keladi.

... va biz qadriyatlarni ikki guruhga birlashtirishimiz kerak, shunda ushbu guruhlarning qadriyatlari taxminan bir-biriga to'g'ri keladi.

Avval har bir elementni ba'zi guruhga aniqlamoqchisiz.

Paragrafning guruhga qo'shilishini ko'rsatish uchun, biz ularga uylanamiz (1), aks holda nol (0).

Keyingi ustunda biz guruhdagi har bir elementning qadriyatlarini umumlashtiramiz va keyin ustunning oxirida umumlashtiramiz.

Shuningdek, har bir guruhdagi har bir elementning qiymatini hal qilishimiz kerak, chunki biz ushbu mahsulotga mos keladigan guruh qiymatini ko'paytiramiz.

Va nihoyat, biz guruhlarning miqdorini kamaytirishimiz va ular o'rtasidagi farq bilan ishlashimiz kerak.

Bizning vazifamiz guruhlar summasi o'rtasidagi farqni minimallashtiradi.

Endi biz har bir guruhdagi elementlarga tayinlashimiz mumkin, chunki bu qo'llarni C va D. Excel-ni G11 hujayrasida guruhlarning summasida farq qiladi.

Muammo shundaki, mumkin bo'lgan kombinatsiyalar soni 2 8, i.e. 256 savolga javob berish. Agar siz ularning har biri uchun 5 soniya sarf qilsangiz, biz tezlikka bardosh berishimiz va eng yaxshi kombinatsiyani eslab qolishimiz va eng yaxshi kombinatsiyani eslab qolishimiz mumkinligini taxmin qilsangiz, u 21,3 daqiqa vaqtni oladi.

Bu erda Echimlarni qidirishilovani topadi.

Excel-da maqbul echimni qidiring

Xizmatni qo'llash uchun Eritma eritmasi Biz bir nechta talablar, qoidalar va cheklovlarni, sifatli javobni to'g'ri javob topishga imkon beradigan cheklovlarni aniqlashimiz kerak.

Bizning qoidalarimiz

Bizning asosiy talabimiz ikki guruh o'rtasidagi farqni minimallashtirishdir. Bizning misolda u G11 hujayrasida joylashgan B minus Group A. Bizga iloji boricha G11 hujayrasida qiymat kerak, ammo 0 dan ko'proq yoki unga teng.

Shuningdek, biz bilamizki, element A guruhida yoki B guruhida bo'lishi mumkinligini bilamiz, u fraktsion bo'lishi mumkin emas. Shunday qilib, har bir element uchun ikkita cheklovga egamiz:

Birinchisi: ustundagi elementning qiymati Natijabittaga teng bo'lishi kerak.

Ikkinchidan: Guruhdagi elementlarning qiymatlari butun son bo'lishi kerak.

Biz ham bilamizki, elementlarning umumiy soni 8 boshqa cheklovdir. Ushbu cheklovlardan qanday foydalanish kerak Keyingi bo'limda muhokama qilamiz.

Izlash dialog oynasi

Ushbu bo'limda qo'shimcha oynada tasvirlangan. Echimlarni qidirishva muammoni aniqlash uchun undan foydalanish.

Bo'sh eritma oynani ayting

To'ldirilgan eritma Qidiruv oynasi

Maqsad xususiyatini optimallashtirish

Bu biz muammoni hal qilishga harakat qilayotgan maqsadli hujayra. Bizning G11 maqsadli hujayramiz guruhlarning farqidir.

Oldin

Bu erda biz maqsadli funktsiyadan qanday natijalarga erishishni istaymiz.

Biz ikkala guruhning miqdori ikkalasini bir-biriga yaqinlashtirishni xohlaymiz, i.e. 0 ga teng bo'lishi uchun, bu g'alati tuyulishi mumkin, ammo biz farqni kamaytirishimiz shart emas, chunki barcha elementlar A guruhida joylashgan. Bu G11 hujayrasining qiymatiga olib keladigan a guruhga joylashtiriladi.

Cheklovlarni qo'llashning yana bir usuli - bu G11 ni o'zgartirish \u003d ABS (G10-F10).Shu bilan birga, biz markerni o'rnatamiz Eng kam,maqsad funktsiyasiga erishish natijasida.

Ammo endi biz formulaga e'tibor qaratamiz \u003d G10-F10.va markerni 0 ga teng qiymatga qo'ying.

O'zgaruvchi hujayralarini o'zgartirish

O'zgaruvchan hujayralar - Sigristrug'i vazifani hal qilishga urinishga harakat qiladi. Bizning holatda, bu ma'lum guruhning majburiy elementi: $ C $ 2: $D $ 9.

Cheklovlarga muvofiq

Cheklovlar chegaralangan qoidalar mumkin echimlar Muammolar.

Biz ro'yxatimizda bir nechta cheklovlarni qo'shishimiz kerak:

1. To'plamda Jamihar bir element 1 ga teng bo'lishi kerak
2. Guruh elementlari butun son bo'lishi kerak
3. Ustun qiymatlarining yig'indisi Jami8 bo'lishi kerak.

Cheklovlarni qo'llash uchun tugmachani bosing Qo'shmoq

Siz .. qila olasiz; siz ... mumkin O'zgartirmoqyoki O'chirmoqagar siz biron bir cheklovni tanlab, muloqot oynasida tegishli tugmalarni bossangiz, cheklash.

Yuklab olish / tejash variantlarini yuklab oling

Solutionlarni qidirish xizmati Sigirmalarni tejash va yuklashga imkon beradi. Buning uchun derazada tugma bor Yuklab olish / Saqlash.Parametrlar oldinroq ko'rsatiladigan doirada saqlanadi. Ushbu yondashuv sizga parametrlarni tezda sozlash va o'zgartirish imkonini beradi. Eritma topish.

Excel-da eng maqbul echimni qidiring

Ogohlantirish !!! Qo'shimcha echim izlash - bu kompleks hisoblash moslamasi, shuning uchun ishga tushirishdan oldin ish daftarchasini saqlang.

Modelni boshlashdan oldin, xizmatning to'g'ri ishlashini ta'minlash uchun yana bir nechta parametrlarni o'rnatishingiz kerak. Asosiy dialog oynasida marker maydonning ro'parasida ekanligiga ishonch hosil qiling Cheklovlarsiz emas cheklovlarsiz o'zgaruvchini yarating.Xuddi shu oynada bosing Parametrlar.

Vaqti-vaqti bilan o'zgarishi kerak bo'lgan ikkita parametr:

Aniqlikni cheklash:0 dan 1 gacha bo'lgan qiymat, bu erda bu ko'rsatkich qanchalik katta bo'lsa, cheklash

Butun sonli tobora yuqori:butun sonni cheklash huquqiga qanchalik uzoqda ekanligini ko'rsatadi.

Ishlash modeli

Qo'shimcha tugmachasini boshlash uchun Echimni topingasosiy oyna.

Status satrida siz kattaligining ichki ishini namoyish etadigan bir qator statik ma'lumotlarni ko'rasiz. Qoida tariqasida, ular tezda o'zgaradilar va ularni o'qish qiyin. Agar model murakkab bo'lsa, ish bir muncha vaqt to'xtashi mumkin, qo'shimchalar odatda ushbu muammolar o'z-o'zidan tiklanadi.

Keyin Echimlarni qidirishuning ishini tugatadi, Excel dialog oynasini ko'rsatadi Natijalar Qidiruv natijalari Ba'zi ma'lumotlar bilan. E'tibor berish uchun birinchi narsa bu yozuv Yechim mumkin og'ishda topiladi.Agar eritma topilgan bo'lsa, ishchi kitob hujayralari taklif etilayotgan qaror bilan o'zgaradi.

Endi siz 4 ta variantni tanlash uchun 4 ta variantingiz bor:

- manba qiymatlarini tiklang

Hisobotni ishga tushirish

Siz ro'yxatdagi hisobotlarni tanlash orqali hisobotni yaratishingiz mumkin. Yangi varaq yaratiladi. Natija to'g'risidagi hisobot1.

E'tibor bering, siz o'rnatgan cheklovlarga qarab turli xil xabarlar mavjud bo'ladi.

Skriptni tejang

Model skript nomini kiritmoqchi bo'lgan joyda va tugmani bosing OK.

Barcha stsenariylar mavjud Skript dispetcheribu yorliqda Ma'lumotlarguruhda Ma'lumotlar bilan ishlash -\u003e Tahlil qilish -\u003e\u003e Skript menejeri.

Aytaylik, sizning hisobotingiz ustunlarning ko'pligi bo'yicha ko'p miqdordagi ma'lumotlar bo'lgan stol mavjud. Bunday jadvallarning vizual tahlili juda qiyin. Hisobot bilan ishlash bo'yicha topshiriqlardan biri ma'lum bir oyga tegishli qatorlar va ustunlar sarlavhalari va ustunlaridagi ma'lumotlarni tahlil qilishdir. Bir qarashda, bu juda oddiy ish, ammo uni bitta standart funktsiya yordamida hal qilib bo'lmaydi. Ha, albatta, siz "uy" - "tahrirlash" - "Find" - "Ctrl + F" ni yoqish uchun qidirish oynasini yoqish uchun excel varag'i. Yoki jadval uchun shartli formatlashni yarating. Ammo keyin olingan natijalar bilan qo'shimcha hisoblash bo'lmaydi. Shuning uchun tegishli formulani yaratish va to'g'ri qo'llash kerak.

Excel massividagi qidiruv qiymatlari

Vazifani hal qilish sxemasi shu tarzda ko'rinadi:

• b1 kamerasida biz sizni qiziqtirgan ma'lumotlarga kiramiz;
• b2 hujayrasida ustun sarlavhasi aks etadi, unda B1 hujayra qiymatini o'z ichiga oladi
• b3 kamerasida chiziq nomi ko'rsatiladi, bu B1 hujayra qiymatini o'z ichiga oladi.

Aslida, siz Excel-da koordinatalarni qidirishingiz kerak. Bu nima uchun? Ko'pincha biz stolning koordinatalarini qiymati bo'yicha olishimiz kerak. Biroz narsa matritsaning daromadli tahliliga o'xshaydi. Ikki so'zda aniq misol shu kabi ko'rinadi. Raqamlardagi belgilangan maqsad - manba qiymati, siz kimni va qachon eng yaqin bo'lganini aniqlashingiz kerak. Masalan, biz oddiy ma'lumot matritsasidan uch chorakda ko'rsatilgan tovarlar soni bo'yicha ko'rsatilganidek, rasmda ko'rsatilganidek, to'rtdan ko'rsatilganidek. Barcha raqam ko'rsatkichlari bir-biriga to'g'ri kelishi juda muhimdir. Agar qo'lda yaratish va to'ldirishni xohlamasa excel stoli Bo'sh varaqdan, keyin maqola oxirida tayyor bo'lgan misol bilan yuklab olinishi mumkin.

Biz har xil murakkablikni hal qilish va maqola oxirida - yakuniy natijalarni izchil ko'rib chiqamiz.

Excel ustunidagi qidirish qiymatlari

Avval qiymat bo'yicha stol ustunlarini sarlavhalar olib borishni o'rganing. Buning uchun ushbu qadamlarga amal qiling.

1. B1 CLEL-da 5277-jadvaldan olingan qiymatni kiriting va kirish maydonining o'qilishi uchun ko'k rangda ulaning (keyingi qismida esa B1 hujayralariga boshqa raqamlar kiritiladi).
2. C2 kamerasida biz ushbu qiymatni o'z ichiga olgan ustun sarlavhasini olish uchun formulaga kiramiz:
3. Tasdiqlash formulasiga kirgandan so'ng, CTRL + Shift + Enter tugmachasini bosing, chunki formulalar qatorda amalga oshirilishi kerak. Agar hamma narsa qirralar atrofida formulas qatorida to'g'ri bajarilgan bo'lsa, jingalak qavs paydo bo'ladi ().

Excel satridagi qidiruv qiymatlari

Endi biz bir xil qiymat uchun qator raqamini olamiz (5277). Buning uchun C3 hujayrasida quyidagi formulalarni kiriting:

Qayta tasdiqlash uchun formulaga kirgandan so'ng, Ctrl + Shift + tugmachasini bosing va natijani oling:

9-raqamli formula 9 - jadvalning tegishli qiymatiga muvofiq varaq satrining sarlavhasini topdi. Natijada bizda bor to'liq manzil D9 qiymatlari.



Qanday qilib ustun sarlavhasini va jadvalda satr nomini olish mumkin

Endi koordinatalarni to'liq emas, balki joriy jadvalni qanday olishni o'rganing. Qisqasi, biz D9 o'rniga 5277 qiymatini topishimiz kerak:

• ustunlar stollari - mart;
• satr uchun - mahsulot4.

Ushbu muammoni hal qilish uchun biz allaqachon olingan qiymatlar bilan C2 va C3 hujayralarida formulani ishlatamiz. Buning uchun buni amalga oshirish:

Natijada, jadvalning ichki koordinatalari pul hisoblanmoqda; Mahsulot 4:

Excel diapazonidagi bir xil qiymatlarni qidiring

Jadval qiymatlari orasida dublikatlar mavjudligini boshqarish uchun, nusxalarning mavjudligi va ularning miqdorini hisoblash haqida bizga xabar beradigan formula yarating. Buning uchun biz E2 kamerasiga formulani joriy etamiz:

Bundan tashqari, jadval qismi doirasi uchun biz shartli formatlash qoidasini yaratamiz:

Sarlavhalar uchun formulalar nusxalari paydo bo'lishi chempionati gorizontal ravishda (chapdan o'ngga) sarlavhani oladi. Chiziqning ismini (raqami) olish uchun formula vertikal ravishda (yuqoridan pastgacha) birinchi dublikatdan olish formulasini oladi. Tuzatish uchun ushbu echim 2 usul mavjud:

Bunday holda, biz formulalarni yoki bir yoki boshqasini o'zgartiramiz, lekin birdaniga ikkitasi emas. Eslatib o'tamiz, eski formula C3 hujayrasida qolishi kerak:

Bu erda vertikal birinchi dublikat koordinatalari to'g'ri ko'rsatilgan (yuqoridan pastgacha) - i7 varaq va avgust uchun; Stol uchun mahsulot2. Keling, keyingi yakuniy misol uchun ushbu imkoniyatni qoldiraylik.

Excel oralig'idagi eng yaqin qiymatni qidiring

Ushbu jadval hali ham mukammal emas. Axir, tahlil qilinganda, siz uning barcha qadriyatlarini aniq bilishingiz kerak. Agar B1 hujayrasida yaratilgan raqam stolda topilmasa, unda xato qaytariladi - # ma'nosi! Idealki, manba raqamlari jadvalida yo'qligida formulalar jadvalning eng yaqin qiymatini tanlagan bo'lishi mumkin. F1 Cell-da jadvallarni tahlil qilish uchun bunday dasturni yaratish uchun yangi formulani kiriting:

Shundan keyin u quyidagicha boshqa barcha formulalarda B1 o'rniga havolani o'zgartiring F1 bo'lishi kerak! Shuningdek, siz havolani o'zgartirishingiz kerak shartli formatlash. Tanlang: "Uy" - "uslublar" - "Shartli formatlash" - "Qoidalarni boshqarish" - "Boshqaruvni o'zgartirish". Va bu erda parametrlarda B1 o'rniga F1 ni ko'rsating. Dasturning ishlashini tekshirish uchun jadvalda B1 kamerasiga bo'lmagan raqamni kiriting, masalan: 8000. Bu yakuniy natijaga olib keladi:

Endi siz biron bir boshlang'ich qiymatni kiritishingiz mumkin va dasturning o'zi jadvalni o'z ichiga olgan eng yaqin raqamni tanlaydi. Shundan so'ng, ko'k sarlavhasini va joriy qiymat uchun satrning nomini ko'rsatadi. Masalan, agar siz 5000 raqamiga kirsangiz, biz yangi natijaga erishamiz:

Bizning Excel-dagi bizning dasturimiz 4965 manba uchun eng yaqin qiymatni topdi - 5000. Bunday dastur foydali bo'lishi mumkin avtomatik echim Biznesni rejalashtirish uchun turli xil tahlil vazifalari, maqsadlarni belgilash, echimini izlash va boshqalarni qidirish. Olingan chiziqlar va ustunlar sizga yangi formulas Excel yordamida ushbu turdagi ma'ruzalarni hisoblash imkoniyatlarini yanada kengaytirishga imkon beradi.

Windows uchun Online, tashqi klaviatura yordamida muvaffaqiyatli ishlashi mumkin. Mobility yoki yomon ko'rish vositasi bo'lgan foydalanuvchilar uchun asosiy kombinatsiya bilan ishlashdan ko'ra qulayroq bo'lishi mumkin sensorli ekran yoki sichqoncha. Ushbu maqolada ishlashda mavjud bo'lgan asosiy kombinatsiyalar tasvirlangan excel dasturi Windows uchun onlayn.

Izohlar:

Ushbu moddadagi kalitlarning yorliqlari AQShda qabul qilingan klaviaturaga mos keladi. Boshqa klaviatura uchun klaviatura yorliqlari bu erda ko'rsatilganlardan farq qilishi mumkin.

Bir necha kalitlar bir vaqtning o'zida bosilgan kombinatsiyalarda, ularning ismlari ortiqcha (+) belgida bo'linadi. Agar tugmachalar ketma-ket bosilsa, ular vergul (,) bilan ajratilgan.

Tez-tez ishlatiladigan kalit kombinatsiyasi

Bu erda Excel Online uchun eng keng tarqalgan yorliqlar ro'yxati keltirilgan.

Harakat qilmoq
Ma'lum bir katakka o'tish
Ko'chib yurish	Sahifani pastga yoki pastga o'q
Yuqoriga harakatlanmoq	Sahifa yoki o'q
Nusxa ko'chirmoq
Kesish
Bekor qilish
Kitobni ochish
Kitobni yopish
Jasur shrift
Ochish kontekst menyusi	Windows klaviaturasi: Kontekstual qo'ng'iroq tugmasi windows menyusi+ F10. Windows kontekstida qo'ng'iroq tugmasi Alt va CTRL tugmalari orasida qoladi. Boshqa klaviatura: SHIFT + F10
Yordamchi
	CTRL + F yoki Shift + F3
Qidiruvni takrorlang (pastga)
Qayta ishlashni takrorlang (yuqoriga)
Kiritish jadvali

Kirish tugmachalari: lenta bilan ishlash uchun klaviatura yorliqlari

Endcel online-da kirish tugmachalari mavjud - lenta bo'ylab harakatlanish uchun yorliq tugmachalari. Ular Excelning klassik versiyasida bo'lgani kabi ishlatiladi va ishlashda vaqtni tejashga imkon beradi.

Windows uchun Excel 2016da bunday yorliqlar boshlandi alt alt.. Excel onlaynda barcha bunday kombinatsiyalar "Alt +" g'alaba tugmachalari bilan boshlanadi, ular bilan yorliqqa to'g'ri keladigan kalit bosiladi. Masalan, "sharh" yorlig'i ALT + WIN + R tugmalarini ochadi.

Agar siz Onlaynda Excel-dan foydalansangiz kompyuter Mac.Ishni boshlash uchun Ctrl + Opt-ni bosing.

Lagega borish uchun, "Alt + g'alaba" tugmachalarini bosing yoki Ctrl + F6 tugmalarini bosing.

Tape Tabs oralig'ida borish uchun yorliq tugmasini bosing.

Lentani yashirish va ishlash uchun ko'proq joy olish uchun Ctrl + F1 tugmachalarini bosing. Lentani yana ko'rsatish uchun ushbu harakatni takrorlang.

Lenta uchun kirish tugmachalari yordamida

Lenta yorlig'iga borish uchun quyidagi kirish tugmalaridan birini bosing.

Harakat qilmoq
"Nima qilishni xohlaysiz?" Lentada qidirish so'zini kiritish uchun.
Nazorat ko'rinishi bilan ishlash uchun "Fayl" yorlig'iga o'ting.
"Uy" yorlig'iga o'ting va matn va raqamlarni formatlash yoki boshqa vositalardan foydalanish (masalan, qidirish).
"Qo'shish" yorlig'iga o'ting va funktsiyalar, jadvallar, diagrammalar, giperhavolalar yoki yozuvlarni kiritish.
"Ma'lumotlar" yorlig'iga o'tish va ma'lumotlarni ma'lumot bilan ishlash uchun ma'lumotlardan foydalanish.
"Sharh" yorlig'iga o'ting va o'qishni yoki eslatmalar bilan ishlashni tekshiring.
Ko'rishlar yorlig'ini oching, kitob yoki to'shak yoki sarlavhalar ustunini ta'minlash, qatorlar yoki ustunlar.

Yorliqlar va lenta menyusi bilan ishlash

Ushbu jadvaldagi kalitlarning yorliqlari sizga yorliqlar va lenta menyusida ishlash vaqtini tejashga yordam beradi.

Excel onlayn uchun klaviatura yorliqlari

Eslatma: Agar Excel onlayn-dagi elektron pochta orqali o'qish rejimida ochilgan bo'lsa, tahririyatlar ishlamaydi. Tahrirlash rejimiga o'tish uchun diqqatni varaqdan tashqariga siljiting (Ctrl + F6 tugmalarini bosing) va keyin ro'yxatga o'tish uchun yorliq tugmasini bosing Kitobni tahrirlash. Kosmik tugmachasini, keyin pastga o'q tugmachasini bosing va keyin buyruqni tanlang Onlayn Onlaynga o'zgartirish.

Excel Online buyruqlari: hujayralarni tahrirlash

Harakat qilmoq	Kalitlar
Stolli satrlarni yuqoridan joylashtiring	ALT + meni, portlovchi moddalar va
Chapdagi stol ustunlarini joylashtiring	Alt + men, bb, l win + i win + l
Kesish
Nusxa ko'chirmoq
Bekor qilish
Qayta takrorlash
Bormoq yangi satr Kamerada
Jadvalni joylashtiring
Funktsiyalarni kiritish
Shrift hajmini oshiring
Shriftning hajmi pasayishi
Filtrdan foydalanish
Takroriy filtr

Excel Online buyruqlari: Ma'lumotlar kiritish

Excel Online buyruqlari: Hujayradagi ma'lumotlarni tahrirlash

Harakat qilmoq	Kalitlar
Tanlangan katakni tahrirlash
Nogiron foydalanuvchilar uchun texnik yordam Microsoft barcha mijozlarimizni olishini ta'minlash uchun intilmoqda eng yaxshi mahsulotlar va texnik xizmat ko'rsatish. Agar sizda cheklangan imkoniyatlar bo'lsa yoki siz bilan bog'liq muammolarga qiziqsangiz maxsus xususiyatlarMicrosoft nogironligi bilan bog'laning texnik yordam. Microsoft nogironligi Javoblar Futaxassislari ko'plab mashhur maxsus xususiyatlar bilan tanish va ingliz, ispan, frantsuz, shuningdek Amerika imo-ishorasini qo'llab-quvvatlashi mumkin. Microsoft nogironligiga murojaat qiling, mintaqangiz uchun aloqa ma'lumotlarini bilib oling. Agar siz davlat muassasasi yoki tijorat tashkilotining vakili bo'lsangiz, iltimos, murojaat qiling

Ushbu darslik funktsiyalarning asosiy afzalliklari haqida gapiradi. INDEKS va Qidiruv taxtasi Ularni taqqoslagandan ko'ra ko'proq jozibador qiladi Podsho. Siz funktsiyalarni osongina engishga yordam beradigan formulalarning bir nechta misollarini ko'rasiz Podsho kuchsiz.

So'nggi paytlarda biz Funktsiya foydalanuvchilarini aniqlashtirish uchun barcha imkoniyatlarni yaratdik Podsho va ilg'or foydalanuvchilar uchun yanada murakkab formulalar misollarini ko'rsating. Endi biz sizni ishlatishdan tortib olmasangiz, harakat qilamiz Podsho, hech bo'lmaganda shou muqobil usullar Excel-da vertikal qidiruvni amalga oshirish.

Nega biz bunga muhtojmiz? - so'raysiz. Ha, chunki Podsho - Bu Exceldagi yagona qidiruv funktsiyasi emas va uning ko'p cheklovlari sizga ko'p vaziyatlarda kerakli natijani olishingizga xalaqit berishi mumkin. Boshqa tomondan, funktsiyalar INDEKS va Qidiruv taxtasi - ko'proq egiluvchan va ularni ko'proq jalb qiladigan bir qator xususiyatlarga ega Podsho.

Indeks va qidirish haqida asosiy ma'lumotlar

Ushbu o'quv qo'llanmaning vazifasi sifatida - xususiyatlarni ko'rsatish INDEKS va Qidiruv taxtasi Excel-da vertikal qidiruvni amalga oshirish uchun biz sintaksis va dasturda qatnashmaymiz.

Bu erda siz mazmunni tushunish uchun kerakli minimal ishlaysiz, shunda biz foydalanishning afzalliklarini ko'rsatadigan formulalarning batafsil misollarini tahlil qilamiz INDEKS va Qidiruv taxtasi o'rniga Podsho.

Indeks - sintaksis va ariza

Funktsiya Indeks. (Indeks) Excel-da belgilangan qatorga va ustun raqamlariga ko'ra qatordan foydalanadi. Funktsiya shunday sintaksisga ega:

Har bir tortishuv juda oddiy izohga ega:

• massiv. (Array) - bu qiymatni olish uchun zarur bo'lgan bir qator hujayralar.
• qator_num (raqam_name) - bu sizning qiymatni olishni xohlagan qatorda qator raqam. Agar ko'rsatilmagan bo'lsa, argument talab qilinadi. ustun_num (_stolbet raqami).
• ustun_num (Rumm_Number) - bu qatorda bir qatorda, siz ekstraksiya qilishni xohlaysiz. Agar ko'rsatilmagan bo'lsa, argument talab qilinadi. qator_num (raqam_stroke)

Agar ikkala argumenti ko'rsatilsa, unda funktsiya INDEKS Belgilangan qatorlar va ustunning kesishmasida joylashgan katakdan qiymatni qaytaradi.

Bu yerda eng oddiy misol Vazifalar Indeks. (INDEKS):

Indeks (A1: C10,2,3)
\u003d Indeks (a1: c10; 2; 3)

Formula diapazonda qidirishni amalga oshiradi A1: C10 va hujayra qiymatini qaytaradi 2-y. Satr I. 3 m ustun, ya'ni hujayradan C2..

Juda sodda, to'g'rimi? Biroq, amalda, siz qaysi qatorni va sizga kerakligini bilmaydi va shuning uchun sizga yordam kerak Qidiruv taxtasi.

Sintaksisni va dastur funktsiyasini qidiring

Funktsiya O'yin (Izlashni) Excel-da hujayralar oralig'ida ko'rsatilgan qiymatni qidirib, ushbu qiymatdagi ushbu qiymatning nisbiy pozitsiyasini qaytaradi.

Masalan, agar diapazonda bo'lsa B1: B3. Nyu-York, Parij, London qadriyatlari mavjud, so'ngra keyingi formula raqamni qaytaradi 3 "London" bu ro'yxatdagi uchinchi element.

Uchrashuv ("London", B1: B3.0)
\u003d Qidiruv kengashi ("London"; B1: B3; 0)

Funktsiya O'yin (Toping) bunday sintaksisga ega:

Uchrashuv (qarash_value, Lowup_array,)
Qidiruv joylashuvi (istalgan_statsion; ko'rilgan_missiv; [typ_station])

• qidirish_value. (Istalgan) - bu siz qidirayotgan raqam yoki matn. Argument qiymati, shu jumladan hujayraga mantiqiy yoki havola bo'lishi mumkin.
• qaramoqli_array. (Qarang) - qidiruv amalga oshiriladigan hujayralar oralig'i.
• match_type. (tur turi) - Ushbu dalillar haqidagi ushbu dalillar funktsiyalari Qidiruv taxtasiAniq yoki taxminiy o'yinni topishni xohlaysizmi:
  • 1 yoki ko'rsatilmagan - maksimal qiymatga, kamroq yoki teng bo'lganga mos keladi. Ko'rib chiqilgan hududda o'sish buyurilishi kerak, ya'ni kichikroqdan ko'proqgacha.
  • 0 - Istalganlarga teng birinchi qiymatni topadi. Kombinatsiya uchun INDEKS/Qidiruv taxtasi Har doim aniq tasodif kerak, shuning uchun uchinchi argument funksiyasi Qidiruv taxtasi Teng bo'lishi kerak 0 .
  • -1 - Kichik qiymatni, ko'proq yoki teng qiymatga teng. Ko'rib chiqilgan maydon kamayib borayotgan tartibda, ya'ni kichikroqdan kattaroq.

Bir qarashda funktsiyaning foydasi Qidiruv taxtasi Shubha tug'diradi. Elementning chegarasini kim bilishi kerak? Biz ushbu buyumning ma'nosini bilishni xohlaymiz!

Kerakli qiymatning nisbiy pozitsiyasi (i.e., liniya raqami va / yoki ustun), biz dalillar uchun aniqlanishimiz kerakligini eslayman qator_num (raqam_) va / yoki ustun_num (raqam_stall) funktsiyalari Indeks. (INDEKS). Qanday qilib funktsiyani eslaysiz INDEKS Ushbu qiymatni belgilangan qatorlar va ustunning kesishganligida qaytarishi mumkin, ammo qaysi qator va ustun biz bilan qiziqishini aniqlay olmaydi.

Qanday qilib indeks va Excel-da eksponent-ni ishlatish

Endi siz ushbu ikki funktsiya haqida asosiy ma'lumotlarni bilasiz, menimcha, qanday ishlashi aniq bo'ladi Qidiruv taxtasi va INDEKS Birgalikda ishlashi mumkin. Qidiruv taxtasi belgilangan hujayralar qatoriga kerakli qiymatning nisbiy pozitsiyasini va INDEKS Ushbu raqamni (yoki raqamlar) ishlatadi va natijani tegishli katakdan qaytaradi.

Bu mutlaqo aniq emasmi? Tasavvur qiling INDEKS va Qidiruv taxtasi Ushbu shaklda:

Indeks ((match (match ( ikkilamchi qiymat,biz qidirayotgan ustun,0))
\u003d Indeks ( olib tashlanadigan ustun; (Qidiruv kengashi ( ikkilamchi qiymat;biz qidirayotgan ustun;0))

Menimcha, bu misolni tushunish osonroq bo'ladi. Aytaylik, sizda davlat kapitallarining ro'yxati bor:

Keling, poytaxtlardan biri, masalan, Yaponiyaning quyidagi formulasidan foydalanib, populyatorni topamiz:

Indeks ($ d $ 2: $ d $ 10, Match ("Yaponiya", $ 2: $ b $ 10,0)))
\u003d Indeks ($ d $ 2: $ 10); Qidiruv kengashi ("Yaponiya"; $ 2; 0)

Endi ushbu formulani har bir elementni nima qilishini ko'rib chiqaylik:

• Funktsiya O'yin (Izlashni) Ustunda "Yaponiya" ni qidiradi B.xususan - hujayralarda B2: B10va raqamni qaytaradi 3 Uchinchi o'rinda ro'yxatdagi "Yaponiya" dan.
• Funktsiya Indeks. (Indeks) Foydalanadi 3 Argument uchun qator_num (raqam_stroke), qaysi satr qiymatini qaytarishingiz kerakligini ko'rsatadi. Ular. Bu oddiy formulani chiqaradi:

  Indeks ($ d $ 2: $ d $ 10.3)
  \u003d Indeks ($ d $ 2: d $ 10; 3)

  Formulada quyidagilar haqida aytilgan: hujayralarda qidiring D2. oldin Detr va qiymatni uchinchi qatordan, ya'ni hujayradan olib tashlang D4.Hisob ikkinchi qatordan boshlanganligi sababli.

Bu natija Excelda bo'ladi:

Muhim! Funktsiyani ishlatadigan qatordagi qatorlar va ustunlar soni Indeks. (Indeks) dalillarning qadriyatlariga mos kelishi kerak qator_num (raqam_) va ustun_num (raqam_stall) funktsiyalari O'yin (Qidirmoq). Aks holda, formulalar natijasida xato bo'ladi.

To'xtang, to'xtating ... Nega biz shunchaki funktsiyani ishlatmasligimiz mumkin Vloukup. (D)? Labirintlarni aniqlashga urinish uchun vaqt o'tkazish mantiqiymi? Qidiruv taxtasi va INDEKS?

VLookUp ("Yaponiya", $ 2: $ d $ 2,3)
\u003d Vd ("Yaponiya"; $ 2: $ d $ 2; 3)

Bunday holda, hech qanday ma'no yo'q! Ushbu misolning maqsadi faqat qanday qilib funktsiyalarni tushunishingiz mumkin, shunda siz qanday funktsiyalarni tushunishingiz mumkin Qidiruv taxtasi va INDEKS Juftlikda ishlash. Keyingi misollar sizga to'plamning haqiqiy kuchini ko'rsatadi INDEKS va Qidiruv taxtasiqaysi oson vaziyatlarga duch keladigan qiyin vaziyatlarga duch keladi Podsho U o'lik uchida bo'ladi.

Nima uchun indeks / qidirish taxtasi PRD-ga qaraganda yaxshiroq?

Vertikal qidiruv uchun foydalanish uchun qaysi formulalar Guru Excel bunga ishonishadi INDEKS/Qidiruv taxtasi Bundan yaxshi Podsho. Biroq, ko'plab Excel foydalanuvchilari hali ham foydalanishga murojaat qilishadi Podshochunki Ushbu xususiyat ancha oson. Bu juda kam odamlar o'tishning barcha afzalliklarini to'liq anglab etmoqda Podsho Bir guruhda INDEKS va Qidiruv taxtasiVa hech kim yanada murakkab formulani o'rganishga vaqt ajratishni xohlamaydi.

4 Excel-da qidiruv kengash / indeksidan foydalanishning asosiy afzalliklari:

1. O'ng qoldirgan holda. Ma'lumki, har qanday vakolatli Excel foydalanuvchisi, Podsho Bu chapga ko'rinmaydi, ya'ni kerakli qiymat o'rganish oralig'ining o'ta chap ustunida bo'lishi kerakligini anglatadi. Agar bo'lsa Qidiruv taxtasi/INDEKSQidiruv ustuni chapda va qidiruv maydonchasining o'ng tomonida bo'lishi mumkin. Masalan: ushbu imkoniyatni amalda ko'rsatadi.

2. Xavfli qo'shing yoki ustunlarni olib tashlang. Funktsiya bilan formulalar Podsho Agar siz qidiruv stoliga ulansangiz yoki uni o'chirsangiz yoki qo'shsangiz, ishlamang yoki xato qiymatini qaytaring. Funktsiya uchun Podsho Har qanday kiritilgan yoki masofaviy ustun, sintaksisning natijasini o'zgartiradi Podsho Ma'lumotlarni ajratib olmoqchi bo'lgan butun oralig'ini va aniq ustun raqamini belgilashni talab qiladi.

Masalan, agar sizda stol bo'lsa A1: C10va ustundan ma'lumotlarni olish kerak B.Keyin qiymatni belgilashingiz kerak 2 Argument uchun col_index_num (raqam_stall) funktsiyalari Podsho, mana bunday:

VLookUp ("qidiruv qiymati", A1: C10,2)
\u003d Qiymat ("qidiruv qiymati"; A1: C10; 2)

Agar keyinchalik ustunlar orasiga yangi ustun qo'ying A. va B., tortishuvning ahamiyati o'zgarishi kerak 2 ustida 3 Aks holda, formulani bir xil bo'lgan ustundan qaytaradi.

Ishlatish Qidiruv taxtasi/INDEKSSiz sinov diapazoni natijani buzmasdan o'chirib tashlashingiz yoki qo'shishingiz mumkin, chunki kerakli qiymatni o'z ichiga olgan ustun aniqlanadi. Darhaqiqat, bu katta ustunlik, ayniqsa, katta ma'lumotlar bilan ishlashda. Siz ishlatiladigan har bir funktsiyani tuzatish uchun tashvishlanmasdan, ustunlarni qo'shishingiz va o'chirishingiz mumkin. Podsho.

3. Kerakli qiymat hajmi bo'yicha cheklov yo'q. Ishlatish Podsho255 ta belgidan iborat qiymat uzunligining uzunligi cheklanganligini eslang, aks holda siz xatoga yo'l qo'yasiz #Value! (# Anglatadi!). Shunday qilib, agar jadvalda uzun chiziqlar bo'lsa, unda faqat amaldagi echim foydalanish kerak INDEKS/Qidiruv taxtasi.

Aytaylik, siz ushbu formulani ishlatasiz Podshohujayralarni qidirmoqda B5. oldin Detr Hujayrada ko'rsatilgan qiymat A2.:

VLookUp (A2, B5: D10,3, FALSE)
\u003d Vd (A2; B5: 3; yolg'on)

Agar qiymat kamerada bo'lsa, formula ishlamaydi A2. 255 belgidan oshgan. Buning o'rniga, siz shunga o'xshash formuladan foydalanishingiz kerak INDEKS/Qidiruv taxtasi:

Indeks (d110, mos (TREA, Indeks (B5: B5: A2.0), 0))
\u003d Indeks (d5: d10; qidiruv kengashi (indeks (b5: b1: a2; 0); 0))

4. Yuqori tezlik. Agar siz kichik jadvallar bilan ishlayotgan bo'lsangiz, Excel tezligidagi farq sezilarli bo'lmasligi mumkin, ayniqsa so'nggi versiyalar. Agar siz minglab satrlar va yuzlab qidiruv formulalarini o'z ichiga olgan katta jadvallar bilan ishlayotgan bo'lsangiz, Excel ishlatilganda ancha tez ishlaydi Qidiruv taxtasi va INDEKS o'rniga Podsho. Umuman olganda, bunday almashtirish tezlikni oshiradi excel asarlari ustida 13% .

Ta'sir Podsho Excelning ishlashi ayniqsa sezilarli bo'lsa ish daftarchasi kabi yuzlab murakkab massila formulalarini o'z ichiga oladi PRP + summalar. Gap shundaki, massivdagi har bir qiymatni tekshirish alohida funktsiyalarni talab qiladi Podsho. Shuning uchun ko'proq qiymatlar qatorida qator mavjud va massivning formulasi qancha ko'p bo'lsa, stolingiz, sekinroq ishlaydigan Excel.

Boshqa tomondan, funktsiyalar bilan formulalar Qidiruv taxtasi va INDEKS Shunchaki qidiruvni amalga oshiradi va natijaga qaytaradi, shunga o'xshash ishni sezilarli darajada tezroq bajaradi.

Indeks va qidirish - formulalar misollari

Endi siz funktsiyalarni o'rganishga arziydigan sabablarni tushunasiz Qidiruv taxtasi va INDEKSKeling, eng qiziqarli tomonga o'taylik va nazariy bilimlar amaliyotda qanday qo'llanilishi mumkinligini bilib olaylik.

Qidiruv va indeks yordamida chap tomonda qanday qidirish kerak

Har qanday darslik Podsho Bu shuni anglatadiki, ushbu xususiyat chapga ko'rinmaydi. Ular. Agar ustunni ko'rsangiz, qidiruv oralig'ida haddan tashqari qolmagan bo'lsa, unda olish uchun hech qanday imkoniyat yo'q Podsho Kerakli natijasi.

Vazifalar Qidiruv taxtasi va INDEKS Excel ancha moslashuvchan, va ular hali ham u erda olib tashlanishi kerak bo'lgan ustun mavjud. Masalan, biz stolga davlatlar va aholi poytaxtlari bilan qaytamiz. Bu safar biz formulani yozamiz Qidiruv taxtasi/INDEKSRossiya (Moskva) poytaxti (Moskva) aholisida qanday o'rin egallaydi.

Quyidagi rasmda ko'rinib turibdiki, formulalar bu vazifani mukammal darajada engib chiqmoqda:

Indeks ($ 2: $ 2: $ 10, match ("Rossiya", $ 2: $ b $ 10,0))

Endi ushbu formula qanday ishlashini tushunishda hech qanday muammo bo'lmasligingiz kerak:

• Birinchidan, biz funktsiyadan foydalanamiz O'yin Ro'yxatdagi "Rossiya" mavqeini topadigan (joylashuv):

  Match ("Rossiya", $ 2: $ b $ 10,0)))
  \u003d Qidiruv kengashi ("Rossiya"; $ 2: $ 10; 0)

• Keyinchalik funktsiya uchun diapazonni o'rnating Indeks. (Indeks) dan siz ekstrakti kerak. Bizning holatimizda, u A2: A10..
• Keyin ikkala qismni ham ulang va formulani oling:

  Indeks ($ 2 $ 2: $ 10); o'yin ("Rossiya"; $ 2; $ 10; 0))
  \u003d Indeks ($ 2: $ 10) $ 10; Qidiruv kengashi ("Rossiya"; $ 2; 0)

Savol: To'g'ri echim har doim mutlaq aloqalardan foydalaniladi INDEKS va Qidiruv taxtasiShunday qilib, qidiruv punklari formulani boshqa hujayralarga nusxalashda harakat qilmaydi.

Indeks va Excel, Max, Max, indeks yordamida hisob-kitoblar (SRVNV, MIN)

Boshqalarni qo'yishingiz mumkin excel funktsiyalari ichida INDEKS va Qidiruv taxtasi, masalan, o'rtacha qiymatga, maksimal yoki eng yaqin narsani topish. Bu erda stolga qo'llanilgan formulalar uchun ba'zi imkoniyatlar mavjud:

1. Mix (Max). Formula - bu maksimal ustun D. C. Xuddi shu qatordan:

Indeks ($ C $ 2: $ c $ 10, mos ($ 10), $ 2: D $ 10,0))
\u003d Indeks ($ C $ 2: $ c $ 10; Qidiruv taxtasi ($ 10: $ 10); $ 10; d $ 10; 0))

Natij: Pekin.

2. Min. (Min). Formulalar ustunda minimaldir D. va ustundan qiymatni qaytaradi C. Xuddi shu qatordan:

Indeks ($ c $ 2: $ c $ 10, mos ($ 10), $ 2: D $ 10.0))
\u003d Indeks ($ C $ 2: $ c $ 10; qidiruv kengashi (MIN ($ 10: $ 10); $ 10; d $ 10; 0)))

Natija: Lima.

3. O'rtacha. (SRNVOV). Formula oralig'ida o'rtacha ko'rsatkichni hisoblab chiqadi D2: D10Keyin unga eng yaqin keladi va qiymatni ustundan qaytaradi C. Xuddi shu qatordan:

Indeks ($ c $ 2: $ c $ 10, mos (o'rtacha $ 2: d $ 10), $ 2: D $ 10,1)).
\u003d Indeks ($ C $ 2: $ c $ 10; Qidiruv kengashi ($ 10: D $ 10); $ 10; 1))

Natija: Moskva

SRVNOV funktsiyasi yordamida indeks va qidiruv bilan birgalikda eslab qolish kerak

Funktsiyadan foydalanish Srnsoke Kombinatsiyalangan S. INDEKS va Qidiruv taxtasiUchinchi funktsiya sifatida argumenti sifatida Qidiruv taxtasi Ko'pincha bu ko'rsatish kerak bo'ladi 1 yoki -1 Agar ko'rish mumkin bo'lgan oraliqda o'rtacha qiymatga teng qiymatni o'z ichiga olgan bo'lsa. Agar siz bunday qiymat borligiga amin bo'lsangiz, qo'ying 0 Aniq tasodifni qidirish.

• Agar siz ko'rsatsangiz 1 Qidiruv ustunidagi qiymatlar ko'tarilishni buyurilishi kerak va formula maksimal qiymatni kichikroq yoki teng darajada qaytaradi.
• Agar siz ko'rsatsangiz -1 Qidiruv ustunidagi qiymatlar kamayib boriladigan tartibda buyurtma berilishi kerak va qaytarilgan mablag 'minimal qiymat, ko'proq yoki teng bo'ladi.

Bizning misolda ustundagi qiymat D. To'lovni buyurdi, shuning uchun biz taqqoslash turidan foydalanamiz 1 . Formula INDEKS/QidirishZ. "Moskva" ni qaytaradi, chunki Moskva shahri aholisi o'rtacha qiymatdan eng kam (12 269 006).

Qanday qilib indeksni qidirish va ma'lum bir chiziq va ustun bilan qidirishni qidirish

Ushbu formula ikki o'lchovli qidiruvga teng. Podsho Va ma'lum bir satr va ustunning kesishganida qiymatni topishga imkon beradi.

Ushbu misolda formula INDEKS/Qidiruv taxtasi Bu biz allaqachon ushbu darsda muhokama qilgan formulalarga juda o'xshash bo'ladi, faqat farq bor. Bilasizmi?

Sintaksis funktsiyasini qanday eslaysiz Indeks. (Indeks) uchta dalildan foydalanishga imkon beradi:

Indeks (massiv, satr_num,)
Indeks (massiv; server raqami; [raqam raqami_stolits])

Sizlardan kimni taxmin qilsangiz, tabriklayman!

Keling, formula shablonini yozayotganimizdan boshlaylik. Buning uchun formulani allaqachon tanish bo'lgan formulani oling. INDEKS/Qidiruv taxtasi va unga boshqa funktsiyani qo'shing Qidiruv taxtasiBu ustun raqamini qaytaradi.

Indeks (sizning jadvalingiz) (match (, ustun0)), (mos (, qidirish uchun satr,0))
\u003d Indeks (sizning stolingiz) (mos keladi (mos) ( vertikal qidirish qiymati,ustun0)), (mos (mos ( gorizontal qidirish uchun qiymat,qidirish uchun satr,0))

E'tibor bering, ikki o'lchovli qidiruv uchun siz argumentdagi butun stolni belgilashingiz kerak massiv. (massiv) funktsiyalar Indeks. (INDEKS).

Va endi ushbu naqshni amalda sinab ko'ramiz. Quyida siz dunyodagi eng ko'p aholi bo'lgan mamlakatlarning ro'yxatini ko'rasiz. 2015 yilda AQSh aholisini bilish bizning vazifamiz.

Xo'sh, formulani yozaylik. Agar men Excel funktsiyalari bilan Excel funktsiyalari bilan murakkab formulani yaratishim kerak bo'lsa, men har birida alohida sarmoya kiritdim.

Shunday qilib, ikki funktsiyadan boshlaylik Qidiruv taxtasibu funktsiya uchun chiziq raqamlarini va ustunni qaytaradi INDEKS:

• Ustunni qidiring - Biz ustunga qaraymiz B.yoki masofada B2: B11., hujayrada ko'rsatilgan qiymat H2. (AQSH). Funktsiya quyidagicha ko'rinadi:

  Mos ($ 1 $ 2, $ 1: $ B $ 11.0)
  \u003d Izlash kompaniyasi ($ h $ 2) $ 1: $ B $ 11; 0)

  4 Chunki "AQSh" ustundagi ro'yxatning 4-elementidir B. (Sarlavhani o'z ichiga olgan holda).

• Satrni qidirish - Biz hujayra qiymatini qidirmoqdamiz H3. (2015) satrda 1 , ya'ni hujayralarda A1: E1:

  Mos ($ 3 $ 3, $ 1: e $ 1 $ 3)
  \u003d Izlash kompaniyasi ($ soat 3; $ 1: $ 1; 0)

  Ushbu formula natijasi bo'ladi 5 "2015 yil" 5-ustunda.

Endi ushbu formulalarni funktsiyaga joylashtiring INDEKS va Voola:

Indeks ($ 1 $ 1: e $ $ 1, $ 1, $ 1, $ 3, $ 3, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1, $ 1).
\u003d Indeks ($ 1 $ 1); Qidiruv taxtasi ($ 2; $ 2; $ 1; $ 3; $ 1; $ 1) 1; 0)))

Agar funktsiyalarni almashtirsangiz Qidiruv taxtasi Ular qaytaradigan qadriyatlar, formula oson va tushunarli bo'ladi:

Indeks ($ 1 $ 1: e $ e $ 1 dollar))
\u003d Indeks ($ 1 $ 1: e $ e) $ 11; 5))

Ushbu formula chorrahada qiymatni qaytaradi. 4-chi Iqiboq I. 5-chi Oraliqdagi ustun A1: E11, ya'ni hujayra qiymati E4.. Shunchaki? Ha!

Indeks va qidirish bilan bir nechta mezonlarni qidiring

Darslikda Podsho Biz funktsiya bilan formulaga misol ko'rsatdik Podsho Bir nechta mezonlarni qidirish. Biroq, bunday yechimni jiddiy cheklash yordamchi ustunni qo'shish edi. Yaxshi yangiliklar: formula INDEKS/Qidiruv taxtasi Ikki ustunda qiymatlar bo'yicha qidirishi mumkin, yordamchi ustunni yaratmasdan!

Aytaylik, bizda buyruqlar ro'yxati bor va biz ikkita mezon bo'yicha miqdorni topishni istaymiz xaridor nomi (Mijoz) va mahsulot (Mahsulot). Ishni bitta xaridor bir vaqtning o'zida bir nechta turli xil mahsulotlarni, shuningdek, varaqdagi xaridorlarning ismlarini sotib olish mumkinligi bilan murakkablashadi Qidirish jadvali Tasodifiy tartibda joylashgan.

Bu erda bunday formula INDEKS/Qidiruv taxtasi Vazifani hal qiladi:

(\u003d Indeksi ("qidiruv jadvali"! $ 2: $ c $ 13, mos (1, (A2 \u003d "" Tungi jadval "! $ 2: $ 13) *
(B2 \u003d "qidiruv jadvali"! $ 2: $ b $ 13), 0), 3)), 3))
(\u003d Indeks ("qidiruv jadvali"! $ 2: $ c $ 13; Qidiruv kompaniyasi (1; A2 \u003d "" Tungi stol "! $ 2: $ 13) *
(B2 \u003d "qidiruv jadvali"! $ 2: $ B $ 13); 0); 3))

Ushbu formulalar ilgari muhokama qilgan boshqa odamlar tomonidan murakkabroq, ammo funktsiyalarni bilish bilan qurollangan. INDEKS va Qidiruv taxtasi Siz uni engib chiqasiz. Eng qiyin qismi bu funktsiya. Qidiruv taxtasiMenimcha, avval tushuntirish kerak.

Uchrashuv (1, (A2 \u003d "qidiruv jadvali" $ 2: $ 13), 0 $ 13), 0) $ 2: $ b $ 13)
Izlash kompaniyasi (1; (a2 \u003d "qidiruv jadvali"! $ 2: $ 13); 0 $ 13) * $ 2: $ b $ 13)

Yuqoridagi formulada kerakli qiymat - bu qiymat 1 va qidiruv massivi ko'payish natijasidir. Xo'sh, nima uchun ko'payishimiz kerak va nima uchun? Barchasini tartibda tahlil qilaylik:

• Ustunda birinchi qiymatni oling A. (Mijoz) varaqda Asosiy jadval va varaqdagi stoldagi xaridorlarning barcha ismlari bilan taqqoslang Qidirish jadvali (A2: A13).
• Agar tasodif topilsa, tenglama qaytadi 1 (Haqiqat) va agar bo'lmasa - 0 (Yolg'on).
• Keyinchalik biz ustun qiymatlar uchun ham shunday qilamiz. B. (Mahsulot).
• Keyin olingan natijalar (1 va 0) natijalarni o'gikamiz. Agar tasodiflar ikkala ustunda topilgan bo'lsa (I.E. Ikkala mezonlar to'g'ri), siz olasiz 1 . Agar ikkala mezon yolg'on bo'lsa yoki ulardan bittasi bajarilsa - olasiz 0 .

Endi nima uchun so'raganimizni tushunasiz 1 kerakli qiymat sifatida? Bu funktsiya qilish huquqi Qidiruv taxtasi Ikkala mezon bajarilsa, pozitsiyani qaytardi.

Eslatma: Bunday holda, uchinchi o'rinni funktsiyaning majburiy dalilidan foydalanish kerak. INDEKS. Bu kerak, chunki Birinchi dalilda biz butun stolni o'rnatdik va siz olib tashlash kerak bo'lgan ustun funktsiyalarini belgilashi kerak. Bizning holatda, bu ustun C. (Sum) va shuning uchun biz kiritdik 3 .

Va nihoyat, chunki Biz har bir katakni massivda tekshirishimiz kerak, bu formulalar qator formula bo'lishi kerak. Siz uni o'ralgan qavslarga ko'ra ko'rishingiz mumkin. Shuning uchun, formulaga kirishni tugatsangiz, bosishni unutmang CTRL + Shift + Enter.

Agar hamma narsa to'g'ri bajarilgan bo'lsa, natijani quyidagi rasmda sifatida olasiz:

Agar Excelda bo'lsa, indeks va qidirish taxtasi

Ehtimol siz noto'g'ri qiymatga ega bo'lmaganingiz uchun (va bir necha marta), masalan, ko'rib chiqilmagan massivda emas INDEKS/Qidiruv taxtasi Xato haqida xabarlar # N / a (# N / d) yoki #Value! (# Anglatadi!). Agar siz bunday xabarni tushunarli bo'lishini istagan narsangizga almashtirishni istasangiz, siz formulani bilan kiritishingiz mumkin INDEKS va Qidiruv taxtasi funktsiyada Agar xizmat ko'rsatiladi.

Sintaksis funktsiyasi Agar xizmat ko'rsatiladi Juda sodda:

Iferror (qiymat, qiymat_if_icur)
Agar yordamchi bo'lsa (qiymat; qiymat_if_ xatosi)

Argument qayerda qiymati (qiymati) - bu xato uchun tasdiqlangan qiymat (bizning holatda - formulani yakuni INDEKS/Qidiruv taxtasi); Dalil qiymat_if_Error (Xatoning qiymati) - bu siz qaytarishni istagan qiymatingiz, agar formulada xato bo'lsa.

Masalan, siz funktsiyani kiritishingiz mumkin Agar xizmat ko'rsatiladi Shunday qilib, shu tarzda:

Iferror (indeks ($ 1: e $ 1 $ 1, $ 1, $ 1, $ 1, $ 3, $ 1, $ 1: $ 1 $ 1: $ 1: $ 1,0) )))
"Tasodiflar topilmadi. Qayta urinib ko'ring!" Agar indeks ($ 1: $ 1; $ 1; $ 1; $ 1; $ 1); $ 11; ($ G $ 3; $ 1: $ e $ 1; 0));
"Taxminanlar topilmadi. Qayta urinib ko'ring!")

Va endi, agar kimdir noto'g'ri qiymatga ega bo'lsa, formulani ushbu natijaga olib keladi:

Agar siz xato holatida bo'lsangiz, katakni bo'sh qoldirsangiz, ikkinchisining ikkinchi argument funktsiyasining qiymati kabi tirnoq ("") dan foydalanishingiz mumkin Agar xizmat ko'rsatiladi. Mana bunday:

Iferror (indeks (leyd (leyti, mos keladigan (istalgan_stasy, 0), ")
Agar xato (indeks (leyti (Izoh; Istalgan_station; Ko'rilgan_maskiv; 0); ")

Ushbu darslikda tasvirlangan kamida bitta formula sizga foydali bo'lib tuyuldi deb umid qilaman. Agar siz ushbu darsdagi ma'lumotlar orasida munosib echimni topa olmagan boshqa qidiruv vazifalarga duch kelsangiz, sharhlarda muammongizni o'zingiz tasvirlab berishingiz va barchamiz birgalikda hal qilishga harakat qilamiz.

Ushbu maqolada funktsiya sintaksisi muhokama qilinadi Excel qidirish, shuningdek funktsiyani qanday ishlatish Misollar bilan Excel qidirish formula

Shuningdek, ushbu maqolada formulalar kombinatsiyasi muhokama qilinadi Excel indeksi.

Microsoft Excel-da bir qator hujayralarda ma'lum qiymatni topishga yordam beradigan turli xil qidiruv xususiyatlari mavjud va Qidiruv taxtasi - ulardan biri. Ingliz tilini qidirish Uchrashuv, asosan, elementlarning hujayralar oralig'ida elementning nisbiy pozitsiyasini aniqlaydi. Biroq, funktsiya Excel qidirish Boshqa funktsiyalar bilan birgalikda ko'proq ish qila oladi.

Funktsiya Excel-ni qidirish - sintaksis va foydalanish

Excel qidirish funktsiyasi Belgilangan kameralar oralig'ida belgilangan qiymatni qidiring va ushbu qiymatning nisbiy pozitsiyasini qaytaradi.

Sintaksis qidiruv kengashi formulalari quyidagicha:

Qidiruv joylashuvi (istalgan_statsion; ko'rilgan_missiv; [typ_station])

Skeeping_net (Majburiy argument) - siz topishni xohlagan qiymat. Bu raqamli, matn yoki mantiqiy qiymati, shuningdek hujayraga havola bo'lishi mumkin.

Ko'rinadigan_maskiv (Majburiy argument) - qidiruv hujayralari.

Typ_SPECKICE (Ixtiyoriy argument) - muvofiqlik turini belgilaydi. Bu quyidagi qiymatlardan biri bo'lishi mumkin: 1, 0, -1.

• 1 yoki yo'qolgan (sukut bo'yicha) - topadi eng katta qiymat Qidiruv qiymatidan kam yoki teng massivda. Qidiruv massivi Keng yoki Z. dan eng kichik yoki undan yuqori darajagacha bo'lgan tartibda saralash kerak.
• 0 - bir qatorda birinchi qiymatni topadi aniq teng Qidiruv qiymati. Saralash talab qilinmaydi.
• -1 - topadi eng kichik qiymat Qidiruv qiymatidan katta yoki teng massivda. Qidiruv massivi eng katta yoki z dan to gacha gacha bo'lgan tartibda tartiblash kerak.

Ushbu xususiyatni yaxshiroq aniqlash uchun, keling, oddiy yozamiz qidiruv kengashining formulasiQuyidagi ma'lumotlarga asoslanib: B ustunidagi talabalarning ismlari va ularning natijalari eng yuqori darajaga qadar saralangan. Boshqalar orasida aniq talaba borligini aniqlash (masalan, talaba vinogradov), ushbu oddiy formuladan foydalaning:

Izlash kompaniyasi (E1; A2: A8; 0)

Agar xohlasangiz, qidirish qiymatini hujayraga joylashtirishingiz mumkin (E1) bu misol) va Excel qidirish formulasidagi ushbu katakka murojaat qiling:

Funktsiya Excel-ni qidirish - Excel-da qidirish funktsiyasidan foydalanishning misoli

Yuqoridagi rasmda ko'rib turganingizdek, talabalar familiyasi o'zboshimchalik bilan tartibda joriy etiladi, shuning uchun biz bahsni o'rnatdik typ_SPECKICE 0 ga teng (aniq o'yin), chunki faqat ushbu turdagi muvofiqlik Qidiruv massivida saralash qiymatlarini talab qilmaydi. Texnik jihatdan, Qidiruv kengashining formulasi uzum talabasining tegishli holatini o'quv oralig'ida qaytaradi. Ammo hisob-kitoblar eng yuqori darajadan eng pastgacha saralanganligi sababli, shuningdek, barcha talabalar orasida beshinchi hisobdagi uzumlari haqida aytilgan.

Excel qidirish funktsiyalari

Siz shunchaki ko'rganingizdek, Excel-da qidirish taxtasidan foydalaning. Biroq, har qanday Excel funktsiyasida bo'lgani kabi, u bir nechta xususiyatlarga ega:

• Funktsiya qidirish kompaniyasi qaytib keladi nisbiy pozitsiya Qatorda qidiruv qiymatlari va ma'nosi emas.
• Qidiruv taxtasi ro'yxatga bog'liq emasMaterial qiymatlar bilan ishlashda kichik va katta harflarni ajrata olmaydi degani nimani anglatadi.
• Agar qidiruv massivida kerakli qiymatning bir nechta holatlari bo'lsa, birinchi qiymatning pozitsiyasi qaytariladi.
• Agar ko'rilgan massivda kerakli qiymat topilmasa, xato # n / d xato qaytariladi.

Excel qidiruvidan foydalanib - formulalar misollari

Endi siz asosiy xususiyatlarni bilasiz funktsiyalar Excelni qidirishKeling, asoslar doirasidan tashqarida bo'lgan formulalarning yana bir necha misollarini ko'rib chiqaylik.

WildCard belgilari bilan Excel qidirish formulasi

Boshqa ko'plab Excel funktsiyalari singari, Qidiruv taxtasi Quyidagi jokerlarni tan oladi:

• Savol belgisi (?) - har qanday belgini almashtiradi
• Yulduzcha (*) - har qanday belgilar ketma-ketligini almashtiradi

Eslatma. Jokerlar faqat ishlatilishi mumkin qidiruv kengashi formulalari parametr bilan typ_SPECKICE 0 ga teng.

Qidiruv kengashining formulasi Wildcard belgilari bilan, siz barcha matnli satrni qidirishingiz kerak bo'lgan vaziyatlarda foydalidir, faqat bir nechta belgilar yoki chiziqning bir qismi. Buni tasvirlash uchun quyidagi misolni ko'rib chiqing.

Aytaylik, sizda o'tgan haftada kompaniyalar ro'yxati va ularning savdo ma'lumotlari mavjud. Siz ma'lum bir kompaniyaning nisbiy pozitsiyasini topishni xohlaysiz (sotish tartibida saralangan tartibda saralangan), ammo siz uning ismini aniq eslay olmaysiz, garchi siz bir nechta birinchi harflarni eslay olmaysiz.

Kompaniyalarning ismlari A2 oralig'ida joylashgan deb taxmin qilib: A8 va siz "Sharq" formulasi bilan quyidagi nominatsiyadan boshlanyapsiz:

Avtoturargoh ("Sharqiy *"; A2: A8; 0)

Bizning qidiruv kengashining formulasi Ko'proq universal, siz istalgan katakchada (ushbu misolda) va ushbu hujayraga jo'shqin qiymatga ega bo'lgan holda kirishingiz mumkin, masalan:

Izlash kompaniyasi (E1 va * * '; A2: A8; 0)

Quyidagi rasmda ko'rsatilganidek, formula "Sharq-Auto" kompaniyasiga mos keladigan 2 pozitsiyani qaytaradi:

Funktsiya Accel-dagi funktsiyasi - masalan, Excelni qidirish funktsiyasi

Qidiruv qiymatidagi faqat bitta belgi almashtirish uchun, joker operatoridan foydalaning "?", Masalan:

Izlash kompaniyasi ("b? Ko'ylagi"; A2: A8; 0)

Yuqoridagi formula "Beyker" nomiga mos keladi va 5 ga teng nisbiy pozitsiyasini qaytaradi.

Formula Tinglash uchun qidiruv

Ushbu maqolaning boshida aytib o'tilganidek, excel qidirish funktsiyasi Yuqori va pastki registr belgilarini ajratmaydi. Formulaning reestriga rioya qilish, foydalanish Qidiruv taxtasi Belgilangan funktsiya bilan birgalikda, ular hujayralarni, shu jumladan belgi reyestri.

Excel-dagi ma'lumotlarga mos keladigan formula formulasiga sezgirlik quyidagicha:

Izlash kompaniyasi (haqiqat; haqiqat) (istalgan_station; viewed_Massive); 0)

Ushbu formulalar mantig'i quyidagicha.

• Uchrashuv funksiyasi qidirilgan massivning har bir elementi bilan qidiruv qiymatini taqqoslaydi. Agar taqqoslangan hujayralar aniq bo'lsa, funktsiya haqiqatni qaytaradi yoki boshqacha tarzda noto'g'ri.
• Bundan tashqari, qidiruv kengashi funktsiyasi har bir qiymatga to'g'ri keladi (bu har bir qiymat) bir-biriga to'g'ri keladigan va birinchi tasodifning pozitsiyasini qaytaradi

EslatmaBu CTRL + Shift + ni talab qiladigan massiv formulas, to'g'ri ishlov berish uchun bosing.

E1 uyasi va qidiruv massivi A2: A8, formula shu kabi ko'rinadi:

Izlash kompaniyasi (haqiqat; A2: A8; E1); 0)

Quyidagi rasm namoyishlari qidiruv kengashining formulasi Excel-dagi registrni hisobga olgan holda:

Excel qidirish funktsiyasi - Masalan Excel qidirish funktsiyasi

Xayollar va farqlarga 2 ta ustunni taqqoslang (Ie va qidiruv)

Tasodif va farqlar bo'yicha ikkita ro'yxatni tekshirish - Exceldagi eng keng tarqalgan vazifalardan biri va buni amalga oshirish mumkin turli xil usullar. AGT / Qidiruv formulasidan foydalanish ulardan biri:

Agar (BMT (1-ro'yxatdagi 1-raqam; 2-sonli ro'yxat); "1 ro'yxatida yo'q"; "" "

1-sonli ro'yxatdagi har qanday qiymat uchun 1-ro'yxatda yo'q bo'lib, formulani "1 ro'yxatida 1" qaytaradi. Va bu shunday bo'ladi:

• Qidiruv funktsiyasi ro'yxatdagi 1-ro'yxatdagi qiymatni qidirishni amalga oshiradi. 2. Agar qiymat topilgan bo'lsa, u nisbiy pozitsiyani qaytaradi, aks holda # N / D xatosi.
• Efddagi EFD funktsiyasi faqat bitta narsani bajaradi: # H / D xatolarining mavjudligini tekshiradi (bu "mavjud emas" degan ma'noni anglatadi). Agar belgilangan qiymat xato # n / d xato bo'lsa, funktsiya haqiqatni qaytaradi, aks holda yolg'on. Bizning holatda, haqiqat 1 ro'yxatdagi qiymat 2-ro'yxatda topilmadi (qidiruv kompaniyasi # n / e xatoni qaytaradi).
• Boshqa foydalanuvchilar uchun natijalarni haqiqat / yolg'on formatida sharhlash qiyin bo'ladi, shunda biz 1-sonli ro'yxatga kiritilmagan qiymatlar uchun "1 ro'yxatida 1 ro'yxatda" matnini namoyish qilish qiyin bo'ladi.

Aytaylik, biz B ustunidagi qadriyatlar bilan qiymatlarni taqqoslashimiz kerak deb taxmin qiling, formula quyidagi shaklni oladi (bu erda B2 eng yuqori hujayra):

Agar (B2; a; 0)); "1 ro'yxatida yo'q"; ""

Esingizda bo'lsa, Excel qidirish funktsiyasi registrni hisobga olmaydi. Registrni ajratib ko'rsatishni majburlash uchun mos keladigan argumentga mos kelishini kiriting va Ctrl + Shift + tugmachasini bosib, massivning ushbu formulani yakunlash uchun:

Agar (BMT (qulf (qulf (a; a; B2); 0); "1 ro'yxatida yo'q"; ""

Quyidagi rasmda ikkala formulalarni namoyish etadi:

Excel qidirish funktsiyasi - Taqqoslash - bu Picak-dagi 2 tagogramm

Excel indeksi va qidiruv

Funktsiya bilan Excel qidirish Biz allaqachon tanishmiz. Endi kombinatsiyadan qanday foydalanishni ko'rib chiqaylik Qidiruv kengashi indeksi Excel-da. Keling, sintaksis funktsiyasi indeksidan boshlaylik.

Sintaksis va Funktsiya indeksi

Funktsiya Excel indeksi Belgilangan chiziqlar va ustunlar asosida bir qatorda qiymatni qaytaradi. Sintaksis funktsiyasi indeksi oddiy:

Indeks (massiv; server raqami; [raqam raqami_stolits])

Mana har bir parametrning juda oddiy izohi:

• bir qator - bu qiymatni qaytarishni xohlagan hujayralar oralig'i.
• rumm_strok - qatorni qaytarishni xohlagan qatorda qator raqami. Agar ushbu parametr berilsa ,_stolb raqami talab qilinadi.
• trek raqami sizning qiymatingizni qaytarishni xohlagan massivdagi ustun raqami. Agar ushbu parametr o'chirilgan bo'lsa, server raqami talab qilinadi.

Agar ikkala parametr bo'lsa, ikkala parametr ishlatilgan_strok va raqami_stall, indeks funktsiyasi belgilangan qator va ustunning kesishganligidagi hujayradagi qiymatni qaytaradi.

Bu erda formulani indeksining eng oddiy namunasi:

Indeks (A1: C10; 2; 3)

Formula A1 dan C10 gacha bo'lgan hujayralarda qidiriladi va ikkinchi qatorda hujayra qiymatini va uchinchi ustunda, ya'ni C2 hujayralarida qaytaradi.

Juda oson, to'g'rimi? Biroq, haqiqiy ma'lumotlar bilan ishlashda, siz qanday qatorlar va ustunlar kerakligini bilishingiz mumkin emas, shuning uchun sizga yordam xususiyatlari kerak Qidiruv taxtasi.

Excel indeksi Excel

Endi siz sintaksisni va ushbu ikki funktsiyaning asoslarini bilganingizda, ehtimol siz allaqachon qanday qilib tushunishni ishlab chiqqansiz Excel qidiruv Kompaniyasining indeksi.

Qidiruv kengashi funktsiyasi Belgilangan hujayralardagi qidiruv qiymatining nisbiy pozitsiyasini belgilaydi. Lekin funktsiya indeksi Bu raqamni oladi va tegishli katakka qiymatni qaytaradi.

Kombinatsiya Excel indeksi shu tarzda ifodalash mumkin:

Indeks (qaytish qiymati, qidiruv kengashi (qidirish qiymati, qidiruv ustuni, 0))

Yaxshi tushunish uchun vizual misolni ko'rib chiqing. Aytaylik, bizda bunday aholiga ega bo'lgan davlatlar ro'yxati bor:

Excel qidiruv funktsiyasi Excel-da - Formula indeksi uchun manba ma'lumotlari Excel uchun qidiruv

Masalan, Rossiya, Rossiya quyidagilardan foydalangan holda topamiz formula Qidiruv indeksi:

Indeks ($ c $ 2: $ c $ 11; qidiruv kompaniyasi ("Rossiya"; $ 2; $ 11; 0))

Endi ushbu formulaning har bir tarkibiy qismini aslida bajarayotganini ko'rib chiqaylik:

• Qidiruv funktsiyasini qidirish B2: B2-sonli hujayralarda "Rossiya" qiymatini qidirishni amalga oshiradi.
• Indeks xususiyati ikkinchi parametrni (raqamli) olib boradi, bu qaysi satrning qiymatini qaytarishni ko'rsatadi va oddiy formulaga aylanadi va oddiy formulaga aylanadi \u003d indeks ($ C $ 2: $ C $ 2, 7). Ya'ni, formula C2-C1111 hujayralarida qidiruv va ettinchi satrda hujayra qiymatini qaytaradi, ya'ni C8 hujayrasi, chunki biz ikkinchi qatordan hisoblashni boshlaymiz.

Va bu erda biz Excelga kirishimiz natijasi:

Excel Indeks formulasidagi Excel qidirish funktsiyasi

Eslatma! Massiv indeksidagi qatorlar va ustunlar soni parametrlar sonidagi va / yoki / yoki raqam raqami funktsiyasining mos ravishda. Aks holda, formulani noto'g'ri natijaga qaytaradi.