Ushbu maqolada formulalar sintaksisi va funksiyadan foydalanish tasvirlangan QIDIRMOQ Va QIDIRMOQ Microsoft Excelda.

Tavsif

Funksiyalar QIDIRMOQ VA QIDIRMOQ bitta matn qatorini boshqasi ichida toping va birinchi matn qatorining boshlang'ich pozitsiyasini qaytaring (ikkinchi matn satrining birinchi belgisidan boshlab). Masalan, "printer" so'zida "n" harfining o'rnini topish uchun siz quyidagi funktsiyadan foydalanishingiz mumkin:

SEARCH("n", "printer")

Bu funksiya qaytadi 4 , chunki "n" "printer" so'zining to'rtinchi belgisidir.

Boshqa so'zlarda ham so'zlarni topishingiz mumkin. Masalan, funktsiya

SEARCH("baza", "ma'lumotlar bazasi")

qaytadi 5 , chunki "baza" so'zi "ma'lumotlar bazasi" so'zining beshinchi belgisidan boshlanadi. Funktsiyalardan foydalanish mumkin QIDIRMOQ Va QIDIRMOQ boshqa matn qatoridagi belgi yoki matn qatorining o‘rnini aniqlash va keyin funksiyalar yordamida matnni qaytarish PSTR Va PSTRB yoki uni funksiyalar bilan almashtiring ALMINTIRISH Va ALMINTIRISH. Ushbu funktsiyalar ushbu maqolada ko'rsatilgan.

Muhim:

Bu xususiyatlar barcha tillarda mavjud bo'lmasligi mumkin.

SEARCH funksiyasi har bir belgi uchun ikki baytni hisoblaydi, agar standart til BDC yoqilgan til bo‘lsa. Aks holda, SEARCH funksiyasi SEARCH funksiyasi bilan bir xil ishlaydi va har bir belgi uchun bir baytni hisoblaydi.

BDCS-ni qo'llab-quvvatlaydigan tillarga yapon, soddalashtirilgan xitoy, an'anaviy xitoy va koreys kiradi.

Sintaksis

SEARCH(qidiruv_matn, ko'rilgan_matn, [boshlang'ich_pozitsiya])

SEARCHB(qidiruv_matn, ko'rilgan_matn, [boshlang'ich_pozitsiya])

SEARCH va SEARCHB funksiyalarining argumentlari quyida tasvirlangan.

Qidiruv_matn Majburiy. Siz topmoqchi bo'lgan matn.

Ko'rilgan_matn Majburiy. Argument qiymatini topish uchun matn qidiruv_matn .

Boshlanish_pozitsiyasi Ixtiyoriy. Argumentdagi belgilar soni ko'rilgan_matn , qidiruvni qaerdan boshlashingiz kerak.

Izoh

Funksiyalar QIDIRMOQ Va QIDIRMOQ katta-kichik harf sezgir emas. Agar katta-kichik harflarga sezgir bo'lishingiz kerak bo'lsa, funksiyalardan foydalaning TOPING Va KECHA.

Munozarada qidiruv_matn Joker belgilardan foydalanishingiz mumkin: savol belgisi ( ? ) va yulduzcha ( * ). Savol belgisi har qanday belgiga, yulduzcha esa har qanday belgilar ketma-ketligiga mos keladi. Agar savol belgisi yoki yulduzcha topmoqchi bo'lsangiz, tilde ( ~ ).

Argument qiymati bo'lsa qidiruv_matn topilmadi, #VALUE! xato qiymati qaytarildi.

Agar argument bo'lsa boshlang'ich_pozitsiyasi qoldirilsa, keyin 1 ga o'rnatiladi.

Agar Boshlanish_pozitsiyasi 0 dan ko'p bo'lmagan yoki argument uzunligidan kattaroq ko'rilgan_matn , #VALUE! xato qiymati qaytarildi.

Dalil boshlang'ich_pozitsiyasi ma'lum miqdordagi belgilarni o'tkazib yuborish uchun ishlatilishi mumkin. Funktsiya deb faraz qilaylik QIDIRMOQ"MDS0093.Erkaklar kiyimi" matn qatori bilan ishlash uchun foydalanish kerak. Matn satrining tavsiflovchi qismida "M" ning birinchi takrorlanishini topish uchun argumentni o'rnating: boshlang'ich_pozitsiyasi Qiymat 8 matnning o'sha qismida qidiruv amalga oshirilmasligi uchun ishlab chiqarish raqami(bu holda - "MDS0093"). Funktsiya QIDIRMOQ sakkizinchi belgidan qidirishni boshlaydi, argumentda ko'rsatilgan belgini topadi qidiruv_matn , keyingi holatda va 9 raqamini qaytaradi. Funktsiya QIDIRMOQ har doim boshidan sanab, belgilar raqamini qaytaradi ko'rilayotgan matn , shu jumladan, agar argument qiymati bo'lsa, o'tkazib yuboriladigan belgilar boshlang'ich_pozitsiyasi 1 dan ortiq.

Misollar

Quyidagi jadvaldan namunaviy ma'lumotlarni nusxalang va uni yangi Excel ish varag'ining A1 katakchasiga joylashtiring. Formulalar natijalarini ko'rsatish uchun ularni tanlang va F2 tugmasini bosing, keyin Enter tugmasini bosing. Agar kerak bo'lsa, barcha ma'lumotlarni ko'rish uchun ustunlar kengligini o'zgartiring.

Ma'lumotlar
Daromad: marja
Bu yerda "boss".
Formula	Tavsif	Natija
SEARCH("va";A2;6)	A2 katak qatoridagi birinchi "va" ning o'rni, oltinchi belgidan boshlab.
QIDIRISH(A4;A3)	"Daromad: Marja" qatoridagi "chekka" qatorining boshlang'ich pozitsiyasi (qidirilgan qator A4 katagida joylashgan) (qidirilgan katak A3).
ALGISH(A3,SEARCH(A4,A3),6,"tovush")	A3 katakdagi “chekka” so‘zining o‘rnini aniqlash va shu belgi va keyingi beshta belgini “volume” matn qatori bilan almashtirish orqali “chekka” so‘zini “vom” so‘zi bilan almashtiradi.	Daromad: hajm
PSTR(A3;SEARCH(" ";A3)+1,4)	Daromad qatoridagi birinchi boʻsh joydan keyingi toʻrtta birinchi belgini qaytaradi: Chegara (A3 katak).
QIDIRISH("""";A5)	Birinchi pozitsiyani qo'sh tirnoq(") A5 katakchasida.
PSTR(A5;SEARCH("""";A5)+1;SEARCH("""";A5;SEARCH("""";A5)+1)-SEARCH("""";A5)-1)	Faqat A5 katakchasidagi qo'sh tirnoq ichiga olingan matnni qaytaradi.

VB loyihasi bilan ishlash (12)
Shartli formatlash (5)
Roʻyxatlar va diapazonlar (5)
Makroslar (VBA protseduralari) (63)
Turli (39)
Excel xato va kamchiliklari (3)

Yacheykadagi ro‘yxatdagi istalgan so‘zni toping

Aytaylik, siz yetkazib beruvchi/mijoz/mijozdan mahsulotlar ro‘yxati bilan to‘ldirilgan jadvalni oldingiz:

Va bu jadvalni mavjud mahsulot katalogingizdagi maqola raqamlari/mahsulot kodlari bilan solishtirish kerak:

Ko'rib turganingizdek, bizning katalogimizda faqat nomsiz maqolalar mavjud. Maqola raqamlariga qo'shimcha ravishda, mijoz ham mahsulot nomiga ega, ya'ni. juda ko'p keraksiz narsalar. Va sizning katalogingizda qaysi mahsulotlar mavjudligini va qaysi biri yo'qligini tushunishingiz kerak:


Excelda bunday qidirish va taqqoslash uchun standart formulalar mavjud emas. Albatta, siz VLOOKUPni joker belgilar bilan avval bir jadvalga, keyin esa boshqasiga qo'llashingiz mumkin. Ammo agar bunday operatsiyani qayta-qayta bajarish kerak bo'lsa, unda har bir jadval uchun bir nechta formulalar yozish, ochig'ini aytganda, comme il faut emas.
Shuning uchun men bugun hech qanday qo'shimchalarsiz formulani ko'rsatishga qaror qildim. manipulyatsiyalar bunday taqqoslashni amalga oshirishga yordam beradi. Buni o'zingiz aniqlash uchun men faylni yuklab olishni maslahat beraman:
Faylni yuklab oling:

(49,5 Kb, 13 249 yuklab olish)

Ushbu fayldagi "Buyurtma" varag'ida mijozdan olingan jadval va "Katalog" varag'ida bizning maqolalarimiz mavjud.
Misol faylidan foydalangan holda formulaning o'zi quyidagicha ko'rinadi:

BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
=QIZIRISH(2,1/QIDIRUV(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11)
agar matnda katalogdan kamida bitta maqola bo'lsa, ushbu formula maqola nomini qaytaradi va #Yo'q (#Yo'q) agar maqola katalogda topilmasa.
Ushbu formulani har xil qo'shimchalar bilan takomillashtirishdan oldin (masalan, keraksiz #N/A olib tashlash), keling, uning qanday ishlashini aniqlaymiz.
LOOKUP funksiyasi ko'rsatilgan qiymatni (2) ko'rsatilgan diapazonda qidiradi (massiv - ikkinchi argument). Diapazon odatda hujayralar massividan iborat, lekin VIEW funksiyasi bizga kerak bo‘lgan birinchi xususiyatga ega – u ikkinchi argument sifatida yozilgan istalgan ifodani to‘g‘ridan-to‘g‘ri massivga aylantirishga harakat qiladi. Boshqacha qilib aytganda, u ushbu argumentdagi ifodani baholaydi, biz ifodani ikkinchi argument sifatida almashtiramiz: 1/SEARCH(Directory!$A$2:$A$11;A2) . QIDIRUV qismi (Katalog!$A$2:$A$11;A2) A2 katakdagi Katalog roʻyxatidan har bir qiymatni navbatma-navbat izlaydi (Mijoz jadvalidagi nom). Agar qiymat topilsa, topilgan qiymatning birinchi belgisining pozitsiya raqami qaytariladi. Agar qiymat topilmasa, xato qiymati #VALUE! (#VALUE!) qaytariladi. Endi ikkinchi xususiyat: funktsiya ma'lumotlarni massivda o'sish tartibida joylashtirishni talab qiladi. Agar ma'lumotlar boshqacha joylashgan bo'lsa, funktsiya izlangan qiymatdan kattaroq, lekin unga iloji boricha yaqinroq qiymat topguncha massivni skanerlaydi (garchi ma'lumotlar bunga imkon bersa ham, aniqroq qidirish uchun bu hali ham yaxshi bo'ladi) ro'yxatni o'sish tartibida tartiblash uchun). Shuning uchun avval 1 ni SEARCH(Directory!$A$2:$A$11,A2) ifodasiga ajratamiz: (0,0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!: #QIYMAT !:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT !}
Xo'sh, kerakli qiymat sifatida biz funktsiyaga raqam beramiz 2 - odatda massivda topilishi mumkin bo'lganidan aniqroq kattaroq son (chunki har qanday raqamga bo'lingan bitta raqam ikkidan kam bo'ladi). Va natijada biz katalogdagi oxirgi mos keladigan qatordagi pozitsiyani olamiz. Shundan so'ng VIEW funksiyasi ushbu pozitsiyani eslab qoladi va ushbu pozitsiya uchun ushbu massivda yozilgan Katalog!$A$2:$A$11 (uchinchi argument) massividan qiymatni qaytaradi.
Har bir hujayra uchun funktsiyani hisoblash bosqichlarini o'zingiz ko'rishingiz mumkin, bu erda men tushunish uchun bosqichlarni biroz kengaytirilgan shaklda taqdim etaman:

1. =BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
2. =KO'RISH(2;
   1/(55:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;!}
   Katalog!$A$2:$A$11)
3. =KO'RISH(2;(0.0181818181818182:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT!:#QIYMAT!:#VALUE!;Каталог!$A$2:$A$11)!}
4. =KO'RISH(2;
   1;
   ("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX29270)
5. "FM2-3320"

Endi funksiyani biroz aniqlaymiz va yana bir nechta amallarni bajaramiz
Amalga oshirish 1:
Maqola raqamlari va #N/A oʻrniga topilgan pozitsiyalar uchun “Ha” va etishmayotganlar uchun “Katalogda topilmadi” koʻrsatiladi:
=AGAR(ENDI(KOʻRISH(2,1/SEARCH(Katalog!$A$2:$A$11,A2)));“Katalogda topilmadi”,“Ha”)
=AGAR(ISNA(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2))),,"Katalogda topilmadi","Ha")
Funktsiyaning ishlashi oddiy - biz LOOKUP ni ajratdik, shuning uchun faqat UND va IF qoladi.
UNM (ISNA) qaytadi TO'G'RI agar uning ichidagi ifoda xato qiymatini qaytarsa #Yo'q (#Yo'q) Va FALSE agar ichidagi ifoda bu xato qiymatini qaytarmasa.
IF (IF) ikkinchi argumentda ko'rsatilgan narsani qaytaradi, agar birinchidagi ifoda teng bo'lsa TO'G'RI va uchinchi dalil bilan ko'rsatilgan narsa, agar birinchi argumentning ifodasi bo'lsa FALSE.

Amalga oshirish 2:
#N/A oʻrniga biz “Katalogda topilmadi” koʻrsatamiz, lekin agar maqolalar topilsa, biz ushbu maqolalarning nomlarini koʻrsatamiz:
=XATO(KO'RISH(2,1/QIZISH(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11);"Katalogda yo'q")
=XATO(QIZIRISH(2,1/QIZISH(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11),"Katalogda yoʻq")
Men ushbu maqolada IFERROR funksiyasi haqida batafsil gapirib berdim: Formulali hujayradagi xato o'rniga 0 ni qanday ko'rsatish mumkin.
Xulosa qilib aytganda, agar funktsiyaning birinchi argumentida ko'rsatilgan ifoda har qanday xatoning qiymatini qaytarsa, u holda funktsiya ikkinchi argument tomonidan yozilgan narsani qaytaradi (bizning holimizda "Katalogda topilmadi" matni). Agar ifoda xatoni qaytarmasa, u holda IFERROR funksiyasi birinchi argumentdagi ifoda tomonidan olingan qiymatni yozadi (bizning holimizda bu maqolaning nomi bo'ladi).

Amalga oshirish 3
Bu nafaqat qaysi maqolaga mos kelishini aniqlash, balki ushbu maqola uchun mahsulot narxini ko'rsatish kerak (narxlarning o'zi Katalog varag'ining B ustunida joylashgan bo'lishi kerak):
=XATO(KO'RISH(2,1/QIZISH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11);"")
=XATO(QIZIRISH(2,1/QIZISH(Katalog!$A$2:$A$11,A2), Katalog!$B$2:$B$11),"")

Bir nechta muhim eslatmalar:

• maqola raqamlari ko'rsatilgan varaqdagi ma'lumotlarda bo'sh katakchalar bo'lmasligi kerak. Aks holda, yuqori ehtimollik bilan formula qidiruv shartlariga mos keladigan emas, balki bo'sh katakning qiymatini qaytaradi.
• Formula shunday izlaydiki, har qanday moslik topiladi. Masalan, raqam maqola sifatida yoziladi 1 , va nom qatorida butun 1 dan tashqari, ham bo'lishi mumkin, 123 , 651123 , FG1412NM va h.k. Ushbu elementlarning barchasi uchun 1-sonli maqola tanlanishi mumkin, chunki u har bir sarlavhada mavjud. Odatda, agar 1-modda ro'yxat oxirida joylashgan bo'lsa, bu sodir bo'lishi mumkin

Shuning uchun tavsiya etiladi Formuladan foydalanishdan oldin ro'yxatni tartiblang ortib boruvchi (eng kichikdan kattaga, A dan Z gacha).

Maqolaning boshida biriktirilgan misolda siz tahlil qilingan barcha variantlarni topasiz.

Agar siz barcha nomlarni ko'rsatishingiz kerak bo'lsa, mening MulTEx plaginimdagi CONTAIN_ONE_OF funksiyasidan foydalanishingiz mumkin.

Maqola yordam berdimi? Havolani do'stlaringiz bilan baham ko'ring! Video darslar

("Bottom bar":("textstyle":"statik","textpositionstatic":"pastki","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"lefts","detexteffects :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:"duration"OutCubic01", ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":6010"texteffect2":6010", textcss":"display:blok; to‘ldirish:12px; matnni tekislash:chap;","textbgcss":"displey:blok; pozitsiya:mutlaq; tepa:0px; chap:0px; kenglik:100%; balandlik:100% ; fon rangi:#333333; shaffoflik:0,6; filtr:alfa(shaffoflik=60);","titlecss":"displey:blok; pozitsiya: nisbiy; shrift:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; rang:#fff;","descriptioncss":"displey:blok; pozitsiya: nisbiy; shrift:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; rang: #fff; margin-top:8px;","buttoncss":"displey:blok; pozitsiya: nisbiy; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive" "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Ish varag'ida kerakli ma'lumotlarni topish juda qiyin katta miqdor ma'lumotlar. Biroq, Topish va almashtirish dialog oynasi ma'lumotni topishni ancha osonlashtiradi. Bundan tashqari, u bir oz bor foydali xususiyatlar, bu haqda ko'p foydalanuvchilar bilmagan.

Buyruqni ishga tushiring Bosh sahifa tahrirlash Topish-ni toping va belgilang(yoki bosing Ctrl+F) dialog oynasini ochish uchun Toping va almashtiring. Agar siz ma'lumotlarni almashtirishingiz kerak bo'lsa, buyruqni tanlang Bosh sahifa tahrirlash O‘zgartirish-ni toping va belgilang(yoki bosing Ctrl+H). Siz bajaradigan aniq buyruq ikkita yorliqning qaysi birida dialog oynasi ochilishini aniqlaydi.

Agar ochilgan dialog oynasida bo'lsa Toping va almashtirish uchun tugmani bosing Variantlar, keyin qo'shimcha ma'lumot qidirish variantlari ko'rsatiladi (21.1-rasm).

Ko'pgina hollarda, qidiruvda aniq matnni emas, balki taxminiy matnni ko'rsatish yaxshiroqdir, masalan, devor kalitlari egalari. Misol uchun, mijoz Ivan Smirnov haqidagi ma'lumotlarni topish uchun siz, albatta, qidiruv maydoniga aniq matnni kiritishingiz mumkin. Biroq, ehtimol siz hech narsa topa olmaysiz, chunki siz mijozning ismini boshqacha kiritgan bo'lishingiz mumkin, masalan, Ivan Smirnov yoki I. Smirnov yoki hatto familiyada xatoga yo'l qo'ygan bo'lishingiz mumkin. Bunday nomni qidirishda joker belgilardan foydalanish yaxshidir.

Kirish iv*smir* dalada Toping va keyin tugmani bosing Hammasini toping. Joker belgilardan foydalanish nafaqat siz kiritgan so'zlar sonini kamaytiradi, balki ish varag'ida mavjud bo'lsa, mijozlar ma'lumotlarini topishingizni ham ta'minlaydi. Albatta, qidiruv natijalari sizning qidiruvingiz maqsadiga mos kelmaydigan yozuvlarni o'z ichiga olishi mumkin, ammo bu hech narsadan yaxshiroqdir.

Muloqot oynasi yordamida qidirishda Toping va almashtiring ikkita joker belgidan foydalanish mumkin:

• ? - har qanday belgiga mos keladi;
• * - har qanday belgilar soniga mos keladi.

Bundan tashqari, ushbu joker belgilar raqamli qiymatlarni qidirishda ham ishlatilishi mumkin. Misol uchun, agar siz qidiruv satrida belgilasangiz 3* , natijada 3 dan boshlanadigan qiymatni o'z ichiga olgan barcha katakchalar ko'rsatiladi va agar siz 1?9 ni kiritsangiz, 1 bilan boshlangan va 9 bilan tugaydigan barcha uch xonali yozuvlarni olasiz.

Savol belgisi yoki yulduzcha izlash uchun uning oldiga tilda (~) qo'ying.
Masalan, quyidagi qidiruv qatori *NONE* matnini topadi: -*N0NE~*
Tilda belgisini topish uchun qidiruv satriga ikkita tilda qo'ying.

Qidiruvingiz to'g'ri ishlamayapti deb hisoblasangiz, quyidagi uchta sozlamani tekshiring (ular ba'zan o'z-o'zidan o'zgaradi).

• Belgilash katagi Gugurt ishi- uni shunday o'rnatingki, qidirilayotgan matnning registrlari registrga mos keladi berilgan matn. Misol uchun, agar siz qidiruvga Ivan so'zini kiritsangiz va belgilangan katakchani belgilasangiz, qidiruv natijalarida Ivan so'zi ko'rinmaydi.
• Belgilash katagi Butun hujayra- uni qidirish satrida ko'rsatilgan matnni aniq o'z ichiga olgan katakchani topish uchun sozlash. Misol uchun, qidiruv satriga Excel so'zini yozsangiz va katakchani belgilasangiz, Microsoft Excel iborasini o'z ichiga olgan katakchani topa olmaysiz.
• Ochiladigan ro'yxat Qidiruv maydoni- ro'yxatda uchta element mavjud: qiymatlar, formulalar va eslatmalar. Misol uchun, agar siz qidiruv satrida va ochiladigan ro'yxatda 900 raqamini kiritsangiz Qidiruv maydoni Agar siz qiymat elementini tanlasangiz, qidiruv natijalarida 900 qiymatini o'z ichiga olgan katakchani ko'rmaysiz, agar u formuladan foydalangan holda olingan bo'lsa.

Esda tutingki, qidiruv tanlangan hujayralar oralig'ida amalga oshiriladi. Agar siz butun varaqni qidirishingiz kerak bo'lsa, qidirishni boshlashdan oldin faqat bitta katakchani tanlang.

Bundan tashqari, oynadan foydalanishni unutmang Toping va almashtiring formatlangan holda topib bo'lmaydi raqamli qiymatlar. Misol uchun, agar siz qidiruv satriga kirsangiz $5* , valyuta formatlash qoʻllanilgan va $54.00ga oʻxshash qiymat topilmaydi.

Sanalar bilan ishlash qiyin bo'lishi mumkin, chunki Excel juda ko'p sana formatlarini qo'llab-quvvatlaydi. Agar siz standart formatlash qo'llaniladigan sanani qidirayotgan bo'lsangiz, Excel ular formatlangan bo'lsa ham sanalarni topadi turli yo'llar bilan. Misol uchun, agar tizimingiz m/d/y sana formatidan foydalansa, 10/*/2010 qidiruv qatori 2010 yil oktyabr oyidagi barcha sanalarni qanday formatlanganidan qat'iy nazar topadi.

Bo'sh maydondan foydalaning tomonidan almashtirildi ish varag'idan ba'zi ma'lumotlarni tezda olib tashlash uchun. Masalan, maydonga - * kiriting Toping va maydonni tark eting O'zgartiring bo'sh. Keyin tugmani bosing Hammasini almashtiring Excel ish varag'idagi barcha yulduzlarni topib olib tashlashi uchun.

Salom do'stlar. Qiymat uchun Excel elektron jadvalida qanchalik tez-tez moslikni qidirishingiz kerak? Misol uchun, siz katalogdagi odamning manzilini yoki narxlar ro'yxatida mahsulot narxini topishingiz kerak. Agar shunday vazifalar yuzaga kelsa, bu post aynan siz uchun!

Men har kuni shunga o'xshash protseduralarni bajaraman va quyida tavsiflangan funktsiyalarsiz men haqiqatan ham juda qiyin bo'lardim. E'tibor bering va ularni ishingizda qo'llang!

Excel jadvali, VLOOKUP va GLOOKUP funksiyalarida qidiring

Ushbu funktsiyalarning oddiy foydalanuvchi hayotidagi rolini ortiqcha baholash qiyin. Endi siz ma'lumotlar jadvalida mos yozuvni osongina topishingiz va mos keladigan qiymatni qaytarishingiz mumkin.

VLOOKUP funksiyasining sintaksisi: =VLOOKUP( qidiruv_qiymati; qidiruv_jadval; chiqish_ustun_raqami; [mapping_type]). Keling, dalillarni ko'rib chiqaylik:

• Qidiruv qiymati- biz qidiradigan qiymat. Bu talab qilinadigan dalil;
• Qidiruv jadvali- qidiruv amalga oshiriladigan hujayralar qatori. Qidirilgan qiymatlar ustuni ushbu massivda birinchi bo'lishi kerak. Bu ham talab qilinadigan dalildir;
• Ko'rsatish uchun ustun raqami- ustunning seriya raqami (massivdagi birinchisidan boshlab), agar kerakli qiymatlar mos kelsa, funktsiya ma'lumotlarni ko'rsatadi. Majburiy argument;
• Mos keladigan tur– kuchsiz moslik uchun “1” (yoki “TRUE”), toʻliq moslik uchun “0” (“YOLGON”) ni tanlang. Argument ixtiyoriy, agar qoldirilsa, qidiruv amalga oshiriladi qat'iy bo'lmagan moslik.

VLOOKUP yordamida aniq moslikni topish

Keling, VLOOKUP funksiyasi mos keladigan tur FALSE bo'lganda, aniq moslikni qidirganda qanday ishlashiga misolni ko'rib chiqaylik. B5: E10 massivida ma'lum bir kompaniyaning asosiy vositalari, ularning balans qiymati, inventar raqami va joylashuvi ko'rsatilgan. B2 katakchada jadvaldagi inventar raqamini topishingiz va uni C2 katakka joylashtirishingiz kerak bo'lgan nom mavjud.

Excelda VLOOKUP funksiyasi

Formulani yozamiz: =VLOOKUP(B2,B5:E10,3,FALSE) .

Bu erda birinchi argument jadvalda B2 katagidan qiymatni izlash kerakligini ko'rsatadi, ya'ni. "Faks" so'zi. Ikkinchi dalil, qidirish uchun jadval B5: E10 oralig'ida ekanligini aytadi va birinchi ustunda "Faks" so'zini izlash kerak, ya'ni. B5:B10 massivida. Uchinchi argument dasturga hisoblash natijasi massivning uchinchi ustunida joylashganligini aytadi, ya'ni. D5: D10. To'rtinchi dalil FALSE, ya'ni. to'liq moslik talab qilinadi.

Shunday qilib, funktsiya B2 yacheykadan “Faks” qatorini oladi va uni B5:B10 massivida yuqoridan pastga qarab qidiradi. Moslik topilgach (8-qator), funktsiya D ustunidan mos keladigan qiymatni qaytaradi, ya'ni. D8 tarkibi. Aynan shu narsa bizga kerak edi, muammo hal qilindi.

Agar qidirilayotgan qiymat topilmasa, funktsiya qaytadi.

VLOOKUP yordamida noaniq moslikni topish

VLOOKUP-dagi ushbu parametr tufayli biz kerakli natijani topish uchun murakkab formulalardan qochishimiz mumkin.

B5: C12 massivi kredit summasiga qarab kreditlar bo'yicha foiz stavkalarini ko'rsatadi. B2 katakchasida biz kredit miqdorini ko'rsatamiz va C2 ​​da bunday operatsiya uchun stavkani olishni xohlaymiz. Vazifa qiyin, chunki miqdor har qanday bo'lishi mumkin va massivda ko'rsatilganlarga to'g'ri kelishi dargumon; aniq moslikni qidirish mos emas:

Keyin biz qat'iy bo'lmagan qidiruv uchun formulani yozamiz: =VLOOKUP(B2,B5:C12,2,TRUE). Endi, B ustunida keltirilgan barcha ma'lumotlardan dastur eng yaqin kichigini qidiradi. Ya'ni, 8000 miqdori uchun 5000 qiymati tanlanadi va tegishli foiz ko'rsatiladi.

Excelda qat'iy bo'lmagan VLOOKUP qidiruvi

Funktsiya to'g'ri ishlashi uchun jadvalning birinchi ustunini o'sish tartibida tartiblash kerak. Aks holda, bu noto'g'ri natijalar berishi mumkin.

GLOOKUP funksiyasi VLOOKUP funksiyasi bilan bir xil sintaksisga ega, lekin natijani ustunlar emas, balki satrlarda qidiradi. Ya'ni, jadvallarni yuqoridan pastga emas, balki chapdan o'ngga qarab skanerlaydi va ustunni emas, ko'rsatilgan qator raqamini ko'rsatadi.

VIEW funksiyasi yordamida ma'lumotlarni qidirish

LOOKUP funksiyasi VLOOKUP funksiyasiga o‘xshash ishlaydi, lekin boshqa sintaksisga ega. Ma'lumotlar jadvalida bir necha o'nlab ustunlar mavjud bo'lganda foydalanaman va VLOOKUP dan foydalanish uchun siz chiqish ustunining sonini qo'shimcha ravishda hisoblashingiz kerak. Bunday hollarda VIEW funksiyasi vazifani osonlashtiradi. Shunday qilib, sintaksis: =KO'RISH( qidiruv_qiymati; array_to_search; Ko'rsatish_to_massivi) :

• Qidiruv qiymati– ma’lumotlar yoki qidiriladigan ma’lumotlarga havola;
• Qidirish uchun massiv- biz shunga o'xshash qiymatni qidirayotgan bitta satr yoki ustun. Ushbu massivni o'sish tartibida tartiblashimiz kerak;
• Ko'rsatish uchun massiv- natijalarni ko'rsatish uchun ma'lumotlarni o'z ichiga olgan diapazon. Tabiiyki, u qidiruv massivi bilan bir xil o'lchamda bo'lishi kerak.

Shu tarzda yozayotganda siz natijalar qatoriga nisbatan nisbiy bo'lmagan havola berasiz. Va siz to'g'ridan-to'g'ri unga ishora qilasiz, ya'ni. birinchi navbatda chiqish ustunining sonini hisoblashning hojati yo'q. Biz VLOOKUP funksiyasi uchun birinchi misolda VIEW funksiyasidan foydalanamiz (asosiy vositalar, inventar raqamlari): =KOʻRISH(B2,B5:B10,D5:D10). Muammo muvaffaqiyatli hal qilindi!

Microsoft Excelda KO'RISH funksiyasi

Nisbiy koordinatalar bo'yicha qidirish. MATCH va INDEX funksiyalari

Ma'lumotlarni qidirishning yana bir usuli - MATCH va INDEX funktsiyalarini birlashtirish.

Ulardan birinchisi massivdagi qiymatni qidirish va uning seriya raqamini olish uchun ishlatiladi: SEARCH( qidiruv_qiymati; ko'rilgan_massiv; [Mos keladigan tur] ). Funktsiya argumentlari:

• Qidiruv qiymati- talab qilinadigan argument
• Ko'rish uchun massiv- biz moslikni qidirayotgan bitta qator yoki ustun. Majburiy argument
• Mos keladigan tur– aniq moslikni izlash uchun “0”, eng yaqin kichikroq moslik uchun “1”, eng yaqin kattaroq moslik uchun “-1” ni belgilang. Funktsiya ro'yxatning boshidan oxirigacha qidirganligi sababli, eng yaqin kichigini qidirganda, qidiruv ustunini kamayish tartibida tartiblang. Va ko'proq narsani qidirganda, uni o'sish tartibida tartiblang.

Kerakli qiymatning o'rni topildi, endi siz uni funksiyadan foydalanib ekranda ko'rsatishingiz mumkin INDEKS( Massiv; qator_raqami; [ustun_raqami]) :

• Massiv– argument qaysi katak massividan qiymatni tanlash kerakligini belgilaydi
• Qator raqami– ko'rsatmoqchi bo'lgan qatorning seriya raqamini (massivning birinchi katagidan boshlab) ko'rsating. Bu erda siz qiymatni qo'lda yozishingiz yoki boshqa funktsiyani hisoblash natijasidan foydalanishingiz mumkin. Masalan, SEARCH.
• Ustun raqami– ixtiyoriy argument, agar massiv bir nechta ustunlardan iborat bo'lsa, ko'rsatiladi. Agar argument o'tkazib yuborilsa, formula jadvalning birinchi ustunidan foydalanadi.

Endi natijaga erishish uchun ushbu funktsiyalarni birlashtiramiz:

Excelda MATCH va INDEX funksiyalari

Bu Excelda mavjud ma'lumotlarni qidirish va ko'rsatish usullari. Bundan tashqari, siz ulardan hisob-kitoblarda foydalanishingiz, taqdimotlarda foydalanishingiz, ular bilan operatsiyalarni bajarishingiz, ularni boshqa funktsiyalarga argument sifatida ko'rsatishingiz va hokazo.

Bilim va ko'nikmalaringiz qanday o'sib, mustahkamlanib borayotganini his qilyapsizmi? Keyin to'xtamang, o'qishda davom eting! Keyingi postda biz ko'rib chiqamiz: bu qiyin va qiziqarli bo'ladi!

Ko'pincha savol tug'iladi « Excelda qanday topish mumkin»? Excel-da siz har qanday ma'lumotni topishingiz mumkin: matn, matnning bir qismi, raqam, telefon raqami, elektron pochta. manzil, familiya, formula, eslatma, hujayra formati va boshqalar.
Excelda satr va ustunning kesishmasidagi katakchani toping – « Excelda satr va ustun kesishmasidagi katakchani qanday topish mumkin» ( Excelda INDEX funksiyasi).
Excel-da toping va boshqa joyga o'ting(masalan, formada)bir vaqtning o'zida bir nechta ma'lumotlar - "Excelda bir vaqtning o'zida bir nechta ma'lumotlarni topish" maqolasiga qarang (Excelda VLOOKUP funksiyasi).
Yoki havolasi bo'lgan katakchani toping Excel formulasi, havolani almashtirish uchun qarang « Excel formulalaridagi boshqa varaqlarga havolalarni o'zgartiring».
Toping Excel xujayralari eslatma bilan - "Excel-ga eslatma qo'shish" maqolasi .
Uchun tezkor qidiruv klaviatura yorlig'i mavjud - Ctrl+F. bosing Ctrl tugmasi va uni ushlab turganda F tugmasini bosing Qidiruv oynasi paydo bo'ladi.
Siz qidiruv oynasini shunday ochishingiz mumkin: "Uy" yorlig'ida "Topish va tanlash" tugmasini bosing.
"Topish" yorlig'idagi "topish" katagiga siz izlayotgan so'zni yozing (so'zning bir qismi mumkin) va "keyingisini topish" tugmasini bosing. Birinchi shunday so'z topiladi. Keyin "keyingisini topish" tugmasini bosing va qidiruv ikkinchi shunday so'zga o'tadi.

Va agar siz bir vaqtning o'zida barcha bunday so'zlarni ko'rsatishingiz kerak bo'lsa, "hammasini topish" tugmasini bosing va qidiruv oynasining pastki qismida hujayra manzilini ko'rsatadigan ro'yxat paydo bo'ladi. Jadvaldagi kerakli so'zga o'tish uchun qidiruv oynasi ro'yxatida kerakli so'zni bosing.

Agar qidiruv hech narsa topmagan bo'lsa va siz bu ma'lumotlar aniq borligini bilsangiz, jadval kataklaridan chekinishni olib tashlashga harakat qiling. Hujayralardagi chekinishni qanday olib tashlash mumkin, maqolaga qarang " Excel matn. Format".
Excelda raqamni topish qidiruv shartlarini kichik tuzatishni talab qiladi - amal qiladi Excelda kengaytirilgan qidiruv.
Maslahat.
Agar siz stol bilan uzoq vaqt ishlasangiz va ko'pincha bir so'zdan ikkinchisiga qidirishga o'tishingiz kerak bo'lsa.Keyin har safar qidiruv oynasini yopmaslik, balki uni jadvalning xalaqit bermaydigan qismiga o'tkazish qulayroqdir. Siz uni ekran ostiga ko'chirishingiz mumkin, faqat qidiruv so'zi uchun ("topish") kirish katakchasini qoldirib, Enter tugmasini bosing.
Agar siz boshqa sahifaga o'tsangiz ham, ushbu qidiruv dialog oynasi doimo ekranda qoladi. Ushbu oynadan foydalanib, istalgan sahifada qidirishingiz mumkin, shunchaki uni faollashtirishingiz kerak sahifani oching. Buning uchun "topish" qatoridagi kursorni bosing.
Kengaytirilgan qidiruv uchun Options tugmasini bosing va kerakli qidiruv variantini tanlang.
Masalan, "Qiymat" ni tanlang. Keyin u raqamlarni, telefon raqamini va hokazolarni qidiradi.
Agar siz bir xil og'irlikdagi barcha so'zlarni topishingiz kerak bo'lsa, lekin holatlarda (sut, sut, sut va boshqalar), biz joker belgilar bilan formula yozamiz. Bu haqda "Excel'dagi joker belgilar" ga qarang.
Excel funktsiyasi "Topish va ajratib ko'rsatish" nafaqat ma'lumotlarni topishga, balki uni almashtirishga ham yordam beradi. "Formulani havolalarni o'zgartirmasdan Excelda qanday nusxalash mumkin" maqolasiga qarang.
Jadvaldagi ma'lumotlarni qayta ishlashga xalaqit beradigan qo'shimcha bo'shliqlarni qanday olib tashlash mumkin, "Excel'da qo'shimcha bo'shliqlarni qanday olib tashlash" maqolasini o'qing.
Excelda istalgan ma'lumotni faqat Qidiruv funksiyasi yoki formulalar bilan emas, balki shartli formatlash funksiyasi bilan ham topishingiz mumkin. Bu haqda "Excelda shartli formatlash" maqolasini o'qing.
Shuningdek, siz "Topish va tanlash" funksiyasi haqida "" maqolasida o'qishingiz mumkin.Excelda filtrlash".