Ofisning asosiy maqsadi Excel dasturlari- hisob-kitoblarni amalga oshirish. Ushbu dastur hujjati (Kitob) raqamlar, matn yoki formulalar bilan to'ldirilgan uzun jadvalli ko'plab varaqlarni o'z ichiga olishi mumkin. Avtomatlashtirilgan tezkor qidiruv ulardagi kerakli hujayralarni topish imkonini beradi.

Oddiy qidiruv

Qiymatni qidirish uchun Excel elektron jadvali, "Uy" yorlig'ida "Topish va almashtirish" vositasining ochiladigan ro'yxatini ochishingiz va "Topish" bandini bosishingiz kerak. Xuddi shu effektga Ctrl + F klaviatura yorliqlari yordamida erishish mumkin.

Eng oddiy holatda, paydo bo'lgan "Topish va almashtirish" oynasida siz kerakli qiymatni kiritishingiz va "Hammasini topish" tugmasini bosishingiz kerak.

Ko'rib turganingizdek, qidiruv natijalari dialog oynasining pastki qismida paydo bo'ldi. Topilgan qiymatlar jadvalda qizil rang bilan chizilgan. Agar "Hammasini top" o'rniga "Keyingisini topish" tugmasini bossangiz, birinchi navbatda ushbu qiymatga ega bo'lgan birinchi katak qidiriladi va yana bosganingizda ikkinchisi qidiriladi.

Matnni qidirish xuddi shu tarzda amalga oshiriladi. Bunda qidirilayotgan matn qidiruv satriga kiritiladi.

Agar ma'lumotlar yoki matn butun Excel jadvalida qidirilmasa, avval qidiruv maydoni tanlanishi kerak.

Kengaytirilgan qidiruv

Aytaylik, siz 3000 dan 3999 gacha bo'lgan barcha qiymatlarni topmoqchisiz. Bu holda siz qidiruv satriga 3??? ni kiritasiz. Joker belgi "?" boshqasini almashtiradi.

Qidiruv natijalarini tahlil qilib, shuni ta'kidlash mumkinki, dastur to'g'ri 9 ta natija bilan bir qatorda, qizil rang bilan belgilangan kutilmagan natijalarni ham ishlab chiqardi. Ular hujayra yoki formulada 3 raqamining mavjudligi bilan bog'liq.

Noto'g'ri natijalarga e'tibor bermasdan, olingan natijalarning ko'pchiligidan mamnun bo'lishingiz mumkin. Lekin Excel 2010 da qidirish funksiyasi ancha aniq ishlashi mumkin. Muloqot oynasidagi Variantlar vositasi shu maqsadda.

"Options" tugmasini bosish orqali foydalanuvchi kengaytirilgan qidiruvlarni amalga oshirish imkoniyatiga ega. Avvalo, sukut bo'yicha "Formulalar" ga o'rnatilgan "Qidiruv maydoni" bandiga e'tibor qarataylik.

Bu shuni anglatadiki, qidiruv, shu jumladan qiymat emas, balki formula bo'lgan hujayralarda ham amalga oshirildi. Ularda 3 raqamining mavjudligi uchta noto'g'ri natija berdi. Qidiruv doirasi sifatida "Qiymatlar" ni tanlasangiz, siz faqat ma'lumotlarni qidirasiz va formulalar hujayralari bilan bog'liq noto'g'ri natijalar yo'qoladi.

Birinchi qatorda qolgan yagona noto'g'ri natijadan xalos bo'lish uchun kengaytirilgan qidiruv oynasida siz "Butun hujayra" bandini tanlashingiz kerak. Shundan so'ng, qidiruv natijasi 100% aniq bo'ladi.

Ushbu natijaga darhol "Butun hujayra" bandini tanlash orqali erishish mumkin (hatto "Qidiruv maydoni" da "Formula" qiymatini qoldirib ham).

Endi "Izlash" bandiga murojaat qilaylik.

Agar sukut bo'yicha "Vaqda" o'rniga "Ish kitobida" ni tanlasangiz, siz izlayotgan hujayralar varag'ida bo'lish shart emas. Skrinshotda foydalanuvchi qidiruvni 2-bo'sh varaqda boshlaganini ko'rsatadi.

Kengaytirilgan qidiruv oynasining keyingi elementi ikkita ma'noga ega "Ko'rish" dir. Standart "satrlar bo'yicha" dir, ya'ni hujayralar satr bo'yicha skanerlanadi. Boshqa qiymatni tanlash - "ustunlar bo'yicha" - faqat qidiruv yo'nalishini va natijalar ketma-ketligini o'zgartiradi.

Hujjatlarni qidirishda Microsoft Excel, siz boshqa joker belgidan ham foydalanishingiz mumkin - "*". Agar "?" har qanday belgini nazarda tutgan bo'lsa, u holda "*" bitta emas, balki istalgan miqdordagi belgilarni almashtiradi. Quyida Luiziana uchun qidiruvning skrinshoti keltirilgan.

Ba'zan qidirishda belgilar holatini hisobga olish kerak. Agar louisiana so'zi bosh harf bilan yozilsa, qidiruv natijalari o'zgarmaydi. Ammo kengaytirilgan qidiruv oynasida "Match case" ni tanlasangiz, qidiruv muvaffaqiyatsiz bo'ladi. Dastur Luiziana va Luiziana so'zlarini boshqacha ko'rib chiqadi va tabiiyki, ularning birinchisini topa olmaydi.

Qidiruv turlari

Moslarni qidiring

Ba'zan jadvalda takroriy qiymatlarni aniqlash kerak bo'ladi. Mosliklarni qidirish uchun avval qidiruv diapazonini tanlashingiz kerak. Keyin, "Uslublar" guruhidagi "Uy" yorlig'ida "Uy" yorlig'ini oching. Shartli formatlash" Keyinchalik, "Hujayralarni ajratib ko'rsatish qoidalari" va "Qiymatlarni takrorlash" bandlarini ketma-ket tanlang.

Natija quyidagi skrinshotda ko'rsatilgan.

Agar kerak bo'lsa, foydalanuvchi mos keladigan hujayralarning vizual displey rangini o'zgartirishi mumkin.

Filtrlash

Qidiruvning yana bir turi filtrlashdir. Faraz qilaylik, foydalanuvchi B ustunida 3000 dan 4000 gacha bo'lgan raqamli qiymatlarni topmoqchi.

Ko'rib turganingizdek, faqat kiritilgan shartni qanoatlantiradigan qatorlar ko'rsatiladi. Qolganlarning hammasi vaqtincha yashiringan. Dastlabki holatga qaytish uchun 2-bosqichni takrorlang.

Misol tariqasida Excel 2010 dasturi yordamida turli xil qidiruv imkoniyatlari muhokama qilindi.Boshqa versiyalardagi Excelda qanday qidirish mumkin? 2003-versiyada filtrlashga o'tishda farq bor. "Ma'lumotlar" menyusida siz "Filtr", "Avtomatik filtr", "Shart" va "Maxsus avtomatik filtr" buyruqlarini ketma-ket tanlashingiz kerak.

Video: Excel jadvalida qidirish

Har xil qo'shni bo'lmagan diapazonlarda yoki hatto alohida varaqlarda tarqalgan qismlarga bo'lingan bir nechta jadvallarni qidirishdan ko'ra, katta bo'lsa-da, butun jadval yoki qo'shni katakchalar bo'ylab qidirish osonroq. Agar shunday qilsangiz ham avtomatik qidiruv bir vaqtning o'zida bir nechta jadvallar bo'ylab muhim to'siqlar paydo bo'lishi mumkin. Ammo barcha ma'lumotlarni bitta jadvalda tashkil qilish qiyin, ba'zan deyarli imkonsizdir. Yoniq aniq misol Biz Excelda bir nechta jadvallarni bir vaqtda qidirish uchun to'g'ri echimni ko'rsatamiz.

Bir vaqtning o'zida bir nechta diapazonlarda qidiruv

Vizual misol uchun, keling, bitta varaqning qo'shni bo'lmagan diapazonlarida joylashgan uchta oddiy alohida jadvalni yarataylik:

20 dona mahsulot ishlab chiqarish uchun zarur bo'lgan miqdorni qidirishingiz kerak. Afsuski, bu ma'lumotlar turli ustunlar va qatorlarda. Shuning uchun, birinchi navbatda, ushbu mahsulotlarni ishlab chiqarish uchun qancha vaqt ketishini tekshirishingiz kerak (birinchi jadval).

Olingan ma'lumotlarga asoslanib, siz darhol boshqa jadvalda qidirishga o'tishingiz va ma'lum bir ishlab chiqarish hajmiga qancha ishchi jalb qilinishi kerakligini topishingiz kerak. Olingan natijani uchinchi jadvaldagi ma'lumotlar bilan solishtirish kerak. Shunday qilib, uchta jadval bo'yicha bitta qidiruv operatsiyasida biz darhol kerakli xarajatlarni (miqdorni) aniqlaymiz.

Oddiy Excel foydalanuvchisi VLOOKUP kabi formulaga asoslangan funksiyalardan foydalangan holda yechim izlaydi. Va u 3 bosqichda (har bir jadval uchun alohida) qidiruvni amalga oshiradi. Ma'lum bo'lishicha, siz maxsus formuladan foydalanib, faqat 1 bosqichda qidiruvni amalga oshirib, darhol tayyor natijaga erishishingiz mumkin. Buning uchun:

1. E6 katakka qidiruv so'rovi uchun shart bo'lgan 20 qiymatini kiriting.
2. E7 katakka quyidagi formulani kiriting:

20 dona uchun ishlab chiqarish qiymati. ma'lum bir mahsulot.

VLOOKUP formulasi bir nechta jadvallarda qanday ishlaydi:

Ushbu formulaning ishlash printsipi asosiy VLOOKUP funktsiyasi uchun barcha argumentlarni ketma-ket qidirishga asoslanadi (birinchi). Birinchidan, uchinchi VLOOKUP funksiyasi birinchi jadvalda E6 katak qiymati sifatida ko'rsatilgan mahsulotning 20 qismini ishlab chiqarish uchun zarur bo'lgan vaqtni qidiradi (keyinchalik kerak bo'lsa o'zgartirilishi mumkin). Keyin ikkinchi VLOOKUP funksiyasi asosiy funksiyaning birinchi argumenti qiymatini qidiradi.

Uchinchi funktsiyani qidirish natijasida biz ikkinchi funktsiyaning birinchi argumenti bo'lgan 125 qiymatini olamiz. Barcha parametrlarni olgandan so'ng, ikkinchi funktsiya ishlab chiqarish uchun zarur bo'lgan ishchilar sonini ikkinchi jadvalda ko'rib chiqadi. Natijada, 5 qiymati qaytariladi, keyinchalik u asosiy funktsiya tomonidan qo'llaniladi. Qabul qilingan barcha ma'lumotlarga asoslanib, formula hisoblashning yakuniy natijasini qaytaradi. Ya'ni, ma'lum bir mahsulotning 20 dona ishlab chiqarish uchun zarur bo'lgan 1750 dollar miqdori.

Ushbu printsipdan foydalanib, siz bir nechta varaqlardan VLOOKUP funktsiyasi uchun formulalardan foydalanishingiz mumkin.

Xayrli kun, aziz Xabro aholisi!

Vaqti-vaqti bilan ba'zilarimiz (ehtimol ba'zilaridan ko'proq) kompilyatsiya va tahlildan tortib, kichik hajmdagi ma'lumotlarni qayta ishlash vazifasiga duch kelamiz. uy byudjeti va ish, o'qish va hokazolar uchun har qanday hisob-kitoblar bilan yakunlanadi. Ehtimol, buning uchun eng mos vosita Microsoft Excel (yoki ehtimol boshqa analoglar, lekin ular kamroq tarqalgan).

Qidiruv menga Habré-da shunga o'xshash mavzudagi bitta maqolani berdi - "Google SpreadSheet-da formulalardan foydalangan holda Talmud". Bu Excelda ishlash uchun asosiy narsalarning yaxshi tavsifini beradi (garchi bu Excelning o'zi haqida 100% bo'lmasa ham).

Shunday qilib, so'rovlar/vazifalarning ma'lum bir to'plamini to'plab, ularni yozish va taklif qilish g'oyasi paydo bo'ldi. mumkin bo'lgan echimlar(hammasi mumkin emas, lekin tezda natija beradi).

Foydalanuvchilar duch keladigan eng keng tarqalgan muammolarni hal qilish haqida gaplashamiz.

Yechimlarning tavsifi quyidagicha tuzilgan: boshlang'ich vazifani o'z ichiga olgan holat berilgan, u asta-sekin murakkablashadi va har bir bosqich uchun tushuntirishlar bilan batafsil yechim beriladi. Funksiyalarning nomlari rus tilida beriladi, lekin rus tilidagi asl nomi birinchi eslatmada qavs ichida beriladi. Ingliz tili(chunki tajribaga ko'ra, foydalanuvchilarning aksariyati rus tilidagi versiyasini o'rnatgan).

Case_1: Mantiqiy funktsiyalar va mos keladigan funktsiyalar
"Menda jadvalda qiymatlar to'plami bor va ma'lum bir shart / shartlar to'plami bajarilganda, ma'lum bir qiymat ko'rsatilishi kerak" (c) Foydalanuvchi

Ma'lumotlar odatda jadval shaklida taqdim etiladi:

Vaziyat:

• agar "Miqdor" ustunidagi qiymat 5 dan katta bo'lsa,
• keyin "Natija" ustunida "Buyurtma shart emas" qiymatini ko'rsatishingiz kerak,

Formulaning sintaksisi quyidagicha:
AGAR(mantiqiy_ifoda, [haqiqat_agar_qiymat], [noto'g'ri_agar_qiymat])

• Mantiqiy ifoda TRUE yoki FALSE deb baholanadigan ifodadir.
• Qiymat_agar_to'g'ri - mantiqiy ifoda rost bo'lsa, chop etiladigan qiymat
• Value_if_false - agar mantiqiy ifoda noto'g'ri bo'lsa, chop etiladigan qiymat

=IF(C5>5, “Buyurtma shart emas”, “Buyurtma kerak”)

Vaziyat murakkabroq bo'ladi, masalan, 2 yoki undan ortiq shartning bajarilishi:

• agar "Miqdor" ustunidagi qiymat 5 dan katta bo'lsa va "Tur" ustunidagi qiymat "A" bo'lsa

• Mantiqiy_qiymat1-2 va boshqalar. - sinovdan o'tkaziladigan shart, uning bahosi TRUE yoki FALSE qiymatini beradi

=AGAR(VA(C2>5,B2=“A”),1,0)

Keling, vazifani murakkablashtirishga harakat qilaylik - yangi shart:

• agar "Miqdor" ustunidagi qiymat 10 va "Tur" ustunidagi qiymat "A" bo'lsa.
• yoki Miqdor ustunidagi qiymat 5 dan katta yoki teng, Tur qiymati esa B
• keyin "Natija" ustunida "1" qiymatini ko'rsatishingiz kerak, aks holda "0".

=AGAR(YOKI(VA(C2=10,B2=“A”); AND(C2>=5,B2=“B”)),1,0)

Endi keyingi holatga o'tamiz:
Tasavvur qilaylik, "Shart" ustunidagi qiymatga qarab, "Natija" ustunida ma'lum bir shart ko'rsatilishi kerak, quyida qiymatlar va natija o'rtasidagi muvofiqlik mavjud.
Vaziyat:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=AGAR(A2=1,“A”, AGAR(A2=2,“B”, AGAR(A2=3,“C”, AGAR(A2=4,“D”,0))))

Ko'rib turganingizdek, bunday formulani yozish nafaqat juda qulay va mashaqqatli, balki tajribasiz foydalanuvchi xatolik yuz berganda uni tahrirlash uchun biroz vaqt talab qilishi mumkin.
Ushbu yondashuvning nochorligi shundaki, u kam sonli shartlar uchun qo'llaniladi, chunki ularning barchasini qo'lda kiritish kerak bo'ladi va bizning formulamiz katta o'lchamlarga "shishirilishi" kerak; ammo, yondashuv qiymatlarning to'liq "omnivorligi" bilan ajralib turadi. va foydalanishning ko'p qirraliligi.

Muqobil yechim_1:
CHOOSE formulasidan foydalanish
Funktsiya sintaksisi:
SELECT(indeks_raqam, qiymat1, [qiymat2], ...)

• Index_number - tanlangan qiymat argumentining soni. Indeks raqami 1 dan 254 gacha bo'lgan raqam, formula yoki 1 dan 254 gacha bo'lgan raqamni o'z ichiga olgan katakka havola bo'lishi kerak.
• Qiymat1, qiymat2,... - 1 dan 254 ta qiymat argumentlarigacha bo'lgan qiymat, bulardan “SELECT” funksiyasi indeks raqamidan foydalanib, bajariladigan qiymat yoki harakatni tanlaydi. Argumentlar raqamlar, hujayra havolalari, maxsus nomlar, formulalar, funktsiyalar yoki matn bo'lishi mumkin.

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=TANLOV(A2, “A”, “B”, “C”, “D”)

• 1 = A
• 2 = B
• 3 = B
• 5 = G

=TANLOV(A31, “A”, “B”, “C”, “D”)

Muqobil yechim_2:
Bu erda biz eng mashhurlaridan biriga keldik Excel funktsiyalari, uni o'zlashtirish har qanday ofis xodimini avtomatik ravishda "tajribali excel foydalanuvchisi"ga aylantiradi /sarcasm/.
Formula sintaksisi:
VLOOKUP(qidiruv_qiymati, jadval, ustun_raqami, [interval_qidiruv])

• Qidiruv_qiymati - funksiya tomonidan qidiriladigan qiymat.
• Jadval - bu ma'lumotlarni o'z ichiga olgan hujayralar diapazoni. Aynan shu hujayralarda qidiruv amalga oshiriladi. Qiymatlar matn, raqamli yoki mantiqiy bo'lishi mumkin.
• Ustun_raqami - "Jadval" argumentidagi ustun raqami, agar mos keladigan bo'lsa, qiymat olinadi. Ustunlar umumiy varaqlar panjarasi (A.B,C,D va boshqalar) bo'ylab emas, balki "Jadval" argumentida ko'rsatilgan massiv ichida hisoblanishini tushunish muhimdir.
• Interval_lookup - funksiya aniq moslikni yoki taxminiy moslikni topishi kerakligini aniqlaydi.

Keyin, jadvalning ishchi qismida, ilgari to'ldirilgan ma'lumotnomaga havola bilan formula yoziladi. Bular. katalogda, “D” ustunida “A” ustunidagi qiymat qidiriladi va moslik topilsa, “B” ustunida “E” ustunidagi qiymat ko'rsatiladi.
Formula sintaksisi:
Natijani B2 katakchaga chiqarish:

Endi ma'lumotlarni bir jadvaldan boshqasiga olish kerak bo'lgan vaziyatni tasavvur qiling, lekin jadvallar bir xil emas. Quyidagi misolga qarang

Ko'rinib turibdiki, ikkala jadvalning "Mahsulot" ustunlaridagi qatorlar bir-biriga mos kelmaydi, ammo bu "VLOOKUP" funksiyasidan foydalanishga to'sqinlik qilmaydi.
Natijani B2 katakchaga chiqarish:

• Malumot - bu ustun raqamini qaytarmoqchi bo'lgan katak yoki katakchalar diapazoni.

=VLOOKUP($A3,$H$3:$M$6, USTUN(),0)

Biz mavzuni ishlab chiqishda davom etamiz va shartni murakkablashtiramiz: tasavvur qiling-a, bizda mahsulotlar bo'yicha turli xil ma'lumotlarga ega ikkita katalog bor va biz "Katalog" da qaysi katalog turi ko'rsatilganiga qarab natijalar bilan jadvaldagi qiymatlarni ko'rsatishimiz kerak. ustun
Vaziyat:

• Agar "Katalog" ustunida 1 raqami ko'rsatilgan bo'lsa, ma'lumotlar "Katalog_1" jadvalidan, agar raqam 2 bo'lsa, belgilangan oyga muvofiq "Katalog_2" jadvalidan olinishi kerak.

Darhol aqlga keladigan yechim quyidagicha:

=AGAR($B3=1; VLOOKUP($A3,$G$3:$I$6; USTUN()-1,0); VLOOKUP($A3,$K$3:$M$6; USTUN()-1;0 ))

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Bonus: “qidiruv_qiymati” argumentidagi ikki yoki undan ortiq xususiyatga asoslangan VLOOKUP.
Vaziyat:

• Tasavvur qilaylik, har doimgidek, bizda jadval ko'rinishidagi ma'lumotlar majmuasi mavjud (agar bo'lmasa, biz unga ma'lumotlarni taqdim etamiz), biz ma'lum xususiyatlar asosida massivdan qiymatlarni olishimiz va ularni boshqa jadval shaklida joylashtirishimiz kerak. .

dan ko'rinib turganidek jadval shakllari, har bir element nafaqat nomga ega (bu noyob emas), balki ma'lum bir sinfga tegishli va o'zining qadoqlash variantiga ega.
Nom, sinf va qadoqlash kombinatsiyasidan foydalanib, biz yangi xarakteristikani yaratishimiz mumkin, buning uchun ma'lumotlar bilan jadvalda biz qo'shimcha "Qo'shimcha xarakteristika" ustunini yaratamiz, uni quyidagi formuladan foydalanib to'ldiramiz:

=BOGLASH (H3;"_";I3;"_";J3)

=XAOTO(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

Ushbu uslub ko'proq xususiyatlar uchun ishlatilishi mumkin, yagona shart - bu hosil bo'lgan kombinatsiyalarning o'ziga xosligi, agar bu bajarilmasa, natija noto'g'ri bo'ladi.

Case_3 Massivdagi qiymat qidirilmoqda yoki VLOOKUP bizga yordam bera olmasa

Keling, hujayralar massivi bizga kerakli qiymatlarni o'z ichiga oladimi yoki yo'qligini tushunishimiz kerak bo'lgan vaziyatni ko'rib chiqaylik.
Vazifa:

• "Qidiruv sharti" ustunida qiymat mavjud va siz uning "Qidiruv massivi" ustunida mavjudligini aniqlashingiz kerak.

Ko'rib turganimizdek, bu erda "VLOOKUP" funktsiyasi kuchsiz, chunki Biz aniq moslikni izlamaymiz, aksincha hujayrada kerakli qiymatning mavjudligini qidiramiz.
Muammoni hal qilish uchun bir nechta funktsiyalarning kombinatsiyasidan foydalanish kerak, xususan:
"AGAR"
"XATO bo'lsa"
"PASTI"
"TOPISH"

Har bir inson haqida, biz avvalroq "IFERROR" funktsiyasini muhokama qildik, shuning uchun "IFERROR" funksiyasiga o'tamiz.

IFERROR(qiymat, xato_qiymat)

• Qiymat xatolar uchun tekshiriladigan argumentdir.
• Xato_qiymati - formulani hisoblashda xatolik yuzaga kelsa, qaytariladigan qiymat. Mumkin quyidagi turlari xatolar: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? va #EMPTY!.

"PASTI"

• Matn - kichik harfga aylantirilgan matn.

Endi FIND funksiyasining sintaksisini batafsil ko'rib chiqamiz.

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

• Search_text - topilishi kerak bo'lgan matn.
• Search_text - qidirilayotgan matnni topmoqchi bo'lgan matn.
• Start_position - qidiruvni boshlash uchun belgi. Matndagi "ko'rish_matn" ning birinchi belgisi 1 raqami bilan raqamlangan. Agar raqam ko'rsatilmagan bo'lsa, u 1 ga o'rnatiladi.

=AGAR(IFERROR(FIND(LINE(A2), LINE(E2),1),0)=0,“muvaffaqiyatsiz”,“bingo!”)

1. LOWER(A2) – A2 katakdagi Search_Text argumentini kichik harfli matnga o‘zgartiradi
2. FIND funksiyasi LOWER(E2) funksiyasi yordamida kichik harfli matnga ham o'zgartiriladigan Search_Text massividagi Search_Text argumentini qidirishni boshlaydi.
3. Agar funktsiya moslikni topsa, ya'ni. mos keladigan so'z/qiymatning birinchi belgisining seriya raqamini qaytaradi, "IF" formulasidagi TRUE sharti ishga tushadi, chunki natijada olingan qiymat nolga teng emas. Natijada, "Natija" ustunida "Bingo!" qiymati ko'rsatiladi.
4. Biroq, funktsiya moslikni topmasa, ya'ni. mos keladigan so'z/qiymatning birinchi belgisining seriya raqami ko'rsatilmaydi va qiymat o'rniga xato qaytariladi, "IFERROR" formulasiga kiritilgan shart ishga tushiriladi va "0" ga teng qiymat qaytariladi. "IF" formulasida FALSE shartiga, chunki natijada olingan qiymat "0" dir. Natijada, "natija" ustunida "fail" qiymati ko'rsatiladi.

Yuqoridagi rasmdan ko'rinib turibdiki, "LOW" va "FIND" funktsiyalari tufayli biz kerakli qiymatlarni katakdagi belgilar va joylashuvdan qat'i nazar topamiz, ammo biz 5-qatorga e'tibor berishimiz kerak.
Qidiruv so'zi "111" ga o'rnatilgan, ammo qidiruv massivida "1111111 cookie" qiymati mavjud, ammo formula "Bingo!" Buning sababi, "111" qiymati "1111111" qiymatlari qatoriga kiritilgan, natijada moslik topiladi. Aks holda bu holat ishlamaydi.

Case_4 Bir nechta shartlar asosida massivdagi qiymatni qidirish yoki VLOOKUP bizga yordam bera olmasa

Keling, "Natija bilan jadval" dan qiymatni topishingiz kerak bo'lgan vaziyatni tasavvur qilaylik ikki o'lchovli massiv Bir nechta shartlar uchun "Katalog", ya'ni "Ism" va "Oy" qiymati.
Vazifaning jadval shakli quyidagicha ko'rinadi:

Vaziyat:

• Ma'lumotlarni "Ism" va "Oy" shartlarining mos kelishiga muvofiq natija bilan jadvalga kiritish kerak.

INDEX funksiyasining sintaksisi

INDEX(massiv, satr_raqami, [ustun_raqami])

• Massiv - agar qidiruv shartlari mos bo'lsa, qiymatlari ko'rsatiladigan hujayralar diapazoni.
• Agar massiv faqat bitta satr yoki bitta ustundan iborat bo'lsa, mos ravishda satr_raqami yoki ustun_raqami argumenti ixtiyoriydir.
• Agar massiv bir nechta satr va bir ustunni egallasa va satr_raqami va ustun_raqami argumentlaridan faqat bittasi berilgan boʻlsa, INDEX funksiyasi butun qator yoki massiv argumentining butun ustunidan iborat massivni qaytaradi.
• Line_number - qiymatni qaytarmoqchi bo'lgan massivdagi qatorning raqami.
• ustun_raqami - qiymatni qaytarmoqchi bo'lgan massivdagi ustun raqami.

MATCH funksiyasi sintaksisi

MATCH(qidiruv_qiymati, qidirish_massivi, [mos_turi])

• Qidiruv_qiymati - qidiruv_massivi argumentidagi qiymatlarga mos keladigan qiymat. Qidiruv_qiymati argumenti qiymat (raqam, matn yoki mantiqiy) yoki bunday qiymatni o'z ichiga olgan hujayraga havola bo'lishi mumkin.
• Looked_array - qidiruv amalga oshiriladigan katakchalar diapazoni.
• match_type ixtiyoriy argumentdir. Raqam -1, 0 yoki 1.

Formulaning bir qismi:

MATCH($A4,$I$4:$I$7,0)

MATCH(B$3,$J$3:$L$3,0)

Shunday qilib, biz nuqta koordinatalarini oldik (1; #N/A) “INDEX” funksiyasi “Masiv” argumentida qidirish uchun foydalanadi.
B4 katak uchun to'liq yozilgan funksiya quyidagicha ko'rinadi:

=INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0))

=INDEX($J$4:$L$7,1,#Yo'q))

Ushbu salbiy ta'sirni zararsizlantirish uchun biz ilgari o'qigan "IFERROR" funktsiyasidan foydalanamiz va xatolik yuz berganda qaytarilgan qiymatni "0" ga almashtiramiz, keyin formula quyidagicha ko'rinadi:

=XATO(INDEX($J$4:$L$7, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

Rasmda ko'rib turganingizdek, "#N/A" qiymatlari endi natijalar jadvalidagi qiymatlardan foydalangan holda keyingi hisob-kitoblarimizga xalaqit bermaydi.

Case_5 Raqamlar oralig'ida qiymat topish

Tasavvur qilaylik, ma'lum bir diapazonga kiritilgan raqamlarga ma'lum bir belgi berishimiz kerak.
Vaziyat:
Mahsulotning narxiga qarab, unga ma'lum bir toifa berilishi kerak
Agar qiymat diapazonda bo'lsa

• 0 dan 1000 gacha = A
• 1001 dan 1500 gacha = B
• 1501 yildan 2000 yilgacha = B
• 2001 yildan 2500 gacha = G
• 2501 dan ortiq = D

LOOKUP funksiyasi satr, ustun yoki massivdagi qiymatni qaytaradi. Funktsiya ikkita sintaktik shaklga ega: vektor va massiv shakli.

QIZDIRISh(qidiruv_qiymati, qidirish_vektori, [natija_vektori])

• qidiruv_qiymati - QIDIRISh funksiyasi birinchi vektorda qidiradigan qiymat. Qidiruv_qiymati raqam, matn, mantiqiy, nom yoki qiymatga havola bo'lishi mumkin.
• Watch_vector - bu bir qator yoki bitta ustundan iborat diapazon. Lookup_vector argumentidagi qiymatlar matn, raqamlar yoki mantiqiy bo'lishi mumkin.
• view_vector argumentidagi qiymatlar o'sish tartibida bo'lishi kerak: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; aks holda LOOKUP funksiyasi noto'g'ri natija berishi mumkin. Kichik va katta harflar ekvivalent deb hisoblanadi.
• natija_vektor - bu bitta satr yoki ustundan iborat diapazon. Natija_vektori qidiruv_vektori bilan bir xil o'lchamga ega bo'lishi kerak.

=KOʻRISH(E3,$A$3:$A$7,$B$3:$B$7)

"Ko'rish_vektori" va "Natija_vektori" argumentlari massiv ko'rinishida yozilishi mumkin - bu holda siz ularni Excel varag'ida alohida jadvalda ko'rsatishingiz shart emas.
Bunday holda, funktsiya quyidagicha ko'rinadi:
Natijani B3 katakchaga chiqarish:
=KO'RISH(E3;(0;1001;1501;2001;2501);("A","B","C","D","D"))

Case_6 Xarakteristikalar bo'yicha raqamlar yig'indisi

Muayyan xususiyatlar asosida raqamlarni jamlash uchun siz uchta turli funktsiyadan foydalanishingiz mumkin:
SUMIF - faqat bitta atribut bo'yicha yig'indi
SUMIFS - bir nechta xususiyatlar bo'yicha yig'indi
SUMPRODUCT - bir nechta xususiyatlar bo'yicha yig'indi
Bundan tashqari, "SUM" formulasi massivga ko'tarilganda "SUM" va massiv formulasi funksiyasidan foydalanish imkoniyati mavjud:
((=SUM(()*()))
ammo bu yondashuv juda noqulay va "SUMPRODUCT" formulasi bilan to'liq funksionallikda qoplanadi
Endi "SUMPRODUCT" sintaksisi haqida batafsil ma'lumot uchun:

SUMPRODUCT(1-massiv, [massiv2], [massiv3],...)

• Massiv1 birinchi massiv bo'lib, uning tarkibiy qismlarini ko'paytirish va keyin natijalar qo'shilishi kerak.
• Massiv2, massiv3… - 2 dan 255 gacha massivlar, ularning komponentlarini ko'paytirish va keyin natijalarni qo'shish kerak.

• Har bir mahsulot uchun ma'lum bir davr uchun jo'natishning umumiy qiymatini toping:

Ma'lumotlar bilan jadvaldan ko'rinib turibdiki, xarajatlarni hisoblash uchun narxni miqdorga ko'paytirish va olingan qiymatni tanlash shartlarini qo'llagan holda, natija bilan jadvalga o'tkazish kerak.
Biroq, SUMPROIZ formulasi formula ichida bunday hisob-kitoblarni amalga oshirishga imkon beradi.
Natijani B4 katakchaga chiqarish:

=XUSM MAHSULOT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Keling, formulani qismlarga bo'lib ko'rib chiqaylik:
– natija bilan jadvaldagi “Ism” ustunidagi ma’lumotlar bilan jadvalning “Ism” ustuniga tanlash shartini o‘rnating.

($K$3:$K$11>=B$3)*($K$3:$K$11 - biz vaqt oralig'i asosida shart qo'yamiz, sana joriy oyning birinchi kunidan katta yoki unga teng, lekin keyingi oyning birinchi kunidan kamroq. Xuddi shunday, shart natija bilan jadvalda, massiv ma'lumotlar bilan jadvalda.
– jadvaldagi “Miqdor” va “Narx” ustunlarini ma’lumotlar bilan ko‘paytiring.
Ushbu funktsiyaning shubhasiz afzalligi - ro'yxatga olish shartlarining bepul tartibi, ular istalgan tartibda yozilishi mumkin, bu natijaga ta'sir qilmaydi.
Natija:

Keling, shartni murakkablashtiramiz va "cookie" nomini tanlash faqat "kichik" va "katta" sinflarida bo'lishi va "rulo" nomi uchun "murabbo" sinfidan tashqari hamma narsa bo'lishi kerakligi haqidagi talabni qo'shamiz:

Natijani B4 katakchaga chiqarish:

=SUMPRODUCT(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Cookie fayllarini tanlash formulasiga yangi shart qo'shildi:

(($I$3:$I$11=“kichik”)+($I$3:$I$11=“katta”))

– ko‘rib turganingizdek, bir ustundagi ikki yoki undan ortiq shartlar “+” belgisi yordamida alohida guruhga ajratiladi va shartlar qo‘shimcha qavslar ichiga olinadi.
Rolik bo'yicha tanlash formulasiga yangi shart ham qo'shildi:

=SUMPRODUCT(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “Murabbo bilan”);($L$3:$L$11)*($K$3:$K$11))

Bu:

($I$3:$I$11<>"murabbo bilan")

– aslida, bu formulada xuddi cookie-fayllar orqali tanlashda boʻlgani kabi tanlash shartini yozish mumkin edi, lekin keyin formulada uchta shartni sanab oʻtish kerak boʻladi, bu holda istisno yozish osonroq boʻladi. - "murabbo bilan" teng emas, buning uchun biz "qiymatidan foydalanamiz"<>».
Umuman olganda, agar xususiyatlar/sinflar guruhlari oldindan ma'lum bo'lsa, unda barcha shartlarni funktsiyaga yozib, uni shishirgandan ko'ra, ma'lumotnomalar yaratib, ularni ushbu guruhlarga birlashtirgan ma'qul.
Natija:

Xo'sh, biz qisqacha qo'llanmamizning oxiriga keldik, bu aslida ancha uzoqroq bo'lishi mumkin edi, lekin maqsad hali ham eng keng tarqalgan vaziyatlarga yechim berish edi, balki alohida (lekin juda qiziqroq holatlar) yechimini tasvirlash emas edi. ).
Umid qilamanki, qo'llanma kimgadir Excel yordamida muammolarni hal qilishda yordam beradi, chunki bu mening mehnatim behuda emasligini anglatadi!

Vaqtingiz uchun rahmat!

IN Microsoft hujjatlari Ko'p sonli maydonlardan iborat Excel ko'pincha ma'lum ma'lumotlarni, qator nomini va hokazolarni topishga muhtoj. To'g'ri so'z yoki iborani topish uchun juda ko'p qatorlarni ko'rib chiqish juda noqulay. O'rnatilgan Microsoft Excel qidiruvi vaqt va asablarni tejashga yordam beradi. Keling, u qanday ishlashini va undan qanday foydalanishni aniqlaylik.

Microsoft Excel-dagi qidiruv funksiyasi Topish va almashtirish oynasi orqali kerakli matn yoki raqamli qiymatlarni topish imkoniyatini taqdim etadi. Bundan tashqari, ilova ilg'or ma'lumotlarni qidirish imkoniyatlarini taqdim etadi.

1-usul: Oddiy qidiruv

Excelda oddiy ma'lumotlarni qidirish sizga qidiruv oynasiga kiritilgan belgilar to'plamini (harflar, raqamlar, so'zlar va h.k.) o'z ichiga olgan barcha katakchalarni kattalarni hisobga olmasdan topish imkonini beradi.

2-usul: Belgilangan hujayra diapazoni bo'yicha qidirish

Agar sizda juda katta jadval bo'lsa, unda bu holda butun varaqni qidirish har doim ham qulay emas, chunki qidiruv natijalari ma'lum bir holatda kerak bo'lmagan juda ko'p natijalarni o'z ichiga olishi mumkin. Qidiruv maydonini faqat ma'lum bir hujayralar oralig'ida cheklashning bir usuli mavjud.

3-usul: Kengaytirilgan qidiruv

Yuqorida aytib o'tilganidek, oddiy qidiruv paytida qidiruv natijalari har qanday shaklda, har qanday shaklda ketma-ket qidiruv belgilar to'plamini o'z ichiga olgan mutlaqo barcha hujayralarni o'z ichiga oladi.

Bundan tashqari, natijalarga nafaqat ma'lum bir katakning tarkibi, balki u tegishli bo'lgan elementning manzili ham kiritilishi mumkin. Masalan, E2 katakchasida A4 va C3 kataklarining yig'indisi bo'lgan formula mavjud. Bu yig'indi 10 ga teng va E2 katakchada aynan shu raqam ko'rsatiladi. Ammo, agar biz qidiruvda "4" raqamini kiritsak, natijalar orasida xuddi shu E2 katakchasi bo'ladi. Bu qanday sodir bo'lishi mumkin? E2 katakchasi formula sifatida A4 katak manzilini o'z ichiga oladi, u faqat kerakli 4 raqamini o'z ichiga oladi.

Ammo bu va boshqa nomaqbul qidiruv natijalarini qanday qilib kesib tashlashimiz mumkin? Aynan shu maqsadlar uchun Excel kengaytirilgan qidiruvi mavjud.

1. Oynani ochgandan keyin "Topish va almashtirish" yuqorida tavsiflangan har qanday usuldan foydalanib, tugmani bosing "Tanlovlar".



2. Oynada qidiruvni boshqarish uchun bir qator qo'shimcha vositalar paydo bo'ladi. Odatiy bo'lib, ushbu vositalarning barchasi oddiy qidiruv bilan bir xil holatda, lekin agar kerak bo'lsa, o'zgartirishlar kiritilishi mumkin.

   

   Odatiy bo'lib, funktsiyalar "Garish ishi" Va "butun hujayralar" o'chirilgan, ammo agar biz tegishli elementlarning yonidagi katakchalarni belgilasak, unda bu holda, natijani yaratishda kiritilgan holat va aniq moslik hisobga olinadi. Agar siz kichik harfli so'zni kiritsangiz, bu so'zning imlosini katta harf bilan o'z ichiga olgan katakchalar, sukut bo'yicha bo'lgani kabi, qidiruv natijalarida endi ko'rinmaydi. Bundan tashqari, agar funktsiya yoqilgan bo'lsa "butun hujayralar", keyin natijalarga faqat aniq nomni o'z ichiga olgan elementlar qo'shiladi. Misol uchun, agar siz "Nikolaev" qidiruv so'rovini o'rnatsangiz, "Nikolaev A.D." matnini o'z ichiga olgan hujayralar qidiruv natijalariga qo'shilmaydi.

   

   Odatiy bo'lib, qidiruv faqat faol Excel varag'ida amalga oshiriladi. Ammo parametr bo'lsa "Qidirmoq" lavozimiga o'tasiz "Kitobda", keyin qidiruv ochiq faylning barcha varaqlari bo'ylab amalga oshiriladi.

   

   Parametrda "Ko'rib chiqish" qidiruv yo'nalishini o'zgartirishingiz mumkin. Odatiy bo'lib, yuqorida aytib o'tilganidek, qidiruv satr bo'yicha amalga oshiriladi. Kalitni holatiga o'tkazish "Ustunlar bo'yicha", siz birinchi ustundan boshlab qidiruv natijalarini yaratish tartibini o'rnatishingiz mumkin.

   

   Ustun ichida "Qidiruv maydoni" qaysi aniq elementlar qidirilayotganligi aniqlanadi. Odatiy bo'lib, bu formulalar, ya'ni hujayra ustiga bosganingizda formulalar qatorida ko'rsatiladigan ma'lumotlar. Bu so'z, raqam yoki hujayra havolasi bo'lishi mumkin. Shu bilan birga, dastur qidiruvni amalga oshirayotganda natijani emas, balki faqat havolani ko'radi. Ushbu ta'sir yuqorida muhokama qilingan. Natijalar bo'yicha, formulalar qatorida emas, balki katakchada ko'rsatilgan ma'lumotlarga ko'ra aniq qidirish uchun siz kalitni joydan siljitishingiz kerak. "formulalar" holatiga "Qiymatlar". Bundan tashqari, eslatmalar bo'yicha qidirish mumkin. Bunday holda, kalitni holatiga o'tkazing "Eslatmalar".

   

   Tugmani bosish orqali yanada aniqroq qidiruvni belgilashingiz mumkin "Format".

   

   Bu hujayralarni formatlash oynasini ochadi. Bu yerda siz qidiruvda ishtirok etadigan katakchalar formatini belgilashingiz mumkin. Raqam formati, hizalama, shrift, chegara, to'ldirish va qo'riqlash, ulardan istalgan biri yoki ularning kombinatsiyasi bo'yicha cheklovlar o'rnatishingiz mumkin.

   

   Agar siz ma'lum bir katak formatini ishlatmoqchi bo'lsangiz, oynaning pastki qismidagi tugmani bosing "Ushbu katak formatidan foydalaning...".

   

   Shundan so'ng, pipetka shaklida vosita paydo bo'ladi. Undan foydalanib, siz foydalanmoqchi bo'lgan formatini tanlashingiz mumkin.

   

   Qidiruv formati sozlangandan so'ng tugmani bosing "KELISHDIKMI".

   

   Ba'zida ma'lum bir ibora bo'yicha emas, balki qidiruv so'zlari boshqa so'zlar va belgilar bilan ajratilgan bo'lsa ham, istalgan tartibda joylashgan kataklarni topish kerak bo'ladi. Keyin bu so'zlar har ikki tomonda "*" belgisi bilan ta'kidlanishi kerak. Endi qidiruv natijalari ushbu so'zlarni o'z ichiga olgan barcha hujayralarni istalgan tartibda ko'rsatadi.



3. Qidiruv sozlamalari o'rnatilgandan so'ng, tugmani bosing "Hammasini toping" yoki "Keyingisini top" qidiruv natijalariga o'tish uchun.

Ko'rib turganingizdek, Excel juda oddiy, ammo ayni paytda juda funktsional qidiruv vositalari to'plamidir. Oddiy chiyillash uchun qidiruv oynasiga qo'ng'iroq qiling, unga so'rov kiriting va tugmani bosing. Ammo, shu bilan birga, ko'p sonli turli xil parametrlar va qo'shimcha sozlamalar bilan individual qidiruvni sozlash mumkin.

Aytaylik, siz uning raqamidan foydalangan holda xodimning telefon kengaytmasini topishingiz kerak, shuningdek, savdo miqdori uchun komissiya nisbatini to'g'ri baholashingiz kerak. Roʻyxatdagi aniq maʼlumotlarni tez va samarali topish uchun maʼlumotlarni qidirasiz va maʼlumotlardan toʻgʻri foydalanilayotganligini avtomatik tekshirasiz. Ma'lumotlarni ko'rganingizdan so'ng, siz hisob-kitoblarni amalga oshirishingiz va natijalarni qaytarish qiymatlarini ko'rsatishingiz mumkin. Ma'lumotlar ro'yxatida qiymatlarni qidirish va natijalarni ko'rsatishning bir necha yo'li mavjud.

Ushbu maqolada

Ro'yxatdagi qiymatlarni vertikal ravishda aniq moslik bo'yicha toping

Bu vazifani bajarish uchun VLOOKUP funksiyasidan yoki INDEX va MATCH funksiyalarining kombinatsiyasidan foydalanishingiz mumkin.

VLOOKUP funksiyasidan foydalanishga misollar

VLOOKUP funksiyasi.

INDEX va MATCHES misollari

Nimani anglatadi:

=INDEX(siz C2:C10 qiymatini qaytarishingiz kerak, u MATCHga mos keladi(B2:B10 massividagi "Karam" birinchi qiymati))

Formula C2:C10 katakchasidagi birinchi qiymatni qidiradi karam(B7 da) va C7 da qiymatni qaytaradi ( 100 ) - mos keladigan birinchi qiymat karam.

Qo'shimcha ma'lumot olish uchun INDEX funksiyasi va MATCH funksiyasiga qarang.

Taxminiy moslik bo'yicha vertikal ravishda ro'yxatdagi qiymatlarni toping

Buning uchun VLOOKUP funksiyasidan foydalaning.

Muhim: Birinchi qatordagi qiymatlar o'sish tartibida tartiblanganligiga ishonch hosil qiling.

Yuqoridagi misolda VLOOKUP funksiyasi A2:B7 oralig'ida 6 ta kechikishi bo'lgan talabaning ismini qidiradi. Jadvalda hech qanday yozuv yo'q 6 kechiktiriladi, shuning uchun VLOOKUP funksiyasi 6 dan past bo'lgan keyingi eng yuqori moslikni qidiradi va ism bilan bog'liq 5 qiymatini topadi Deyv, va shuning uchun qaytib keladi Deyv.

Qo'shimcha ma'lumot uchun VLOOKUP funksiyasiga qarang.

Noma'lum o'lchamlar ro'yxatida aniq mos keladigan vertikal qiymatlarni topish

Bu vazifani bajarish uchun OFFSET va MATCH funksiyalaridan foydalaning.

Eslatma: Ushbu yondashuv ma'lumotlar har kuni yangilanadigan tashqi ma'lumotlar oralig'ida bo'lsa ishlatiladi. Siz B ustunida Narx borligini bilasiz, lekin server qancha ma'lumot qatorini qaytarishini bilmaysiz va birinchi ustun alifbo tartibida tartiblanmagan.

C1 diapazonning yuqori chap katakchasi (boshlang'ich katak deb ham ataladi).

Mos ("apelsinlar"; C2: C7; 0) C2:C7 oralig'ida to'q sariq rangni qidiradi. Siz diapazonga boshlang'ich katakchani qo'shmasligingiz kerak.

1 - qaytish qiymati qaytarilishi kerak bo'lgan boshlang'ich katakchaning o'ng tomonidagi ustunlar soni. Bizning misolimizda qaytarish qiymati D ustunida, Sotish.

Ro'yxatdagi qiymatlarni gorizontal ravishda aniq moslik bo'yicha toping

Bu vazifani bajarish uchun GLOOKUP funksiyasidan foydalaniladi. Quyida bir misol keltirilgan.

LOOKUP funksiyasi ustunni qidiradi Sotish va belgilangan diapazondagi 5-satrdagi qiymatni qaytaradi.

Qo'shimcha ma'lumot olish uchun QIZDIRISh funksiyalariga qarang.

Qidiruv ustasi yordamida qidirish formulasini yarating (faqat Excel 2007 uchun)

Eslatma: Qidiruv ustasi plaginini Excel 2010 da toʻxtatib qoʻygan. Bu funksiya Funktsiyalar ustasi va mavjud qidiruv va mos yozuvlar (mos yozuvlar) funksiyalari bilan almashtirildi.

Excel 2007 da Izlash ustasi satr va ustun sarlavhalari bo'lgan ish varaqlari ma'lumotlariga asoslangan qidirish formulasini yaratadi. Qidiruv ustasi bir ustundagi qiymatni bilsangiz, qatordagi boshqa qiymatlarni topishga yordam beradi va aksincha. Qidiruv ustasi yaratgan formulalarda indeks va MATCH dan foydalanadi.