Salom do'stlar. Excel jadvalidagi qiymatni qanchalik tez-tez izlash kerak? Masalan, ma'lumotnomada odamning manzilini yoki narxlar ro'yxatida - buyumning narxini topishingiz kerak. Agar bunday vazifalar duch kelsa - bu xabar siz uchun!

Men har kuni shunga o'xshash protseduralarni amalga oshiraman va quyida tavsiflangan funktsiyalarsiz men juda qiyin bo'lgan bo'lardim. E'tibor bering va ularni o'zingizning ishingizda qo'llang!

Excel jadvalini qidirish, VLOOKUP va HLOOKUP funktsiyalari

Ushbu funktsiyalarning oddiy foydalanuvchi hayotidagi rolini deyarli baholab bo'lmaydi. Endi ma'lumotlar jadvalida mos yozuvni osongina topishingiz va mos keladigan qiymatni qaytarishingiz mumkin.

VLOOKUP funktsiyasi uchun sintaksis: \u003d VLOOKUP ( Izlash_ qiymati; jadval_for_search; ustun_ raqami; [match_type]). Dalillarni ko'rib chiqing:

Qidirilayotgan qiymat - izlash kerak bo'lgan qiymat. Bu talab qilinadigan dalil;
Qidiruv jadvali - qidiruv olib boriladigan kataklar qatori. Kerakli qiymatlarga ega ustun ushbu massivda birinchi bo'lishi kerak. Bu ham talab qilinadigan dalil;
Ustun raqami ko'rsatildi - ustunning tartib raqami (massivda birinchisidan boshlab), agar kerakli qiymatlar mos keladigan bo'lsa, funktsiya ma'lumotlar chiqaradi. Kerakli argument;
Uchrashuv turi - qat'iy bo'lmagan o'yin uchun "1" (yoki "Haqiqat") ni, to'liq o'yin uchun "0" ("FALSE") ni tanlang. Argument ixtiyoriy, agar siz uni tashlab qo'ysangiz, qidiruv amalga oshiriladi bo'sh gugurt.

VLOOKUP yordamida aniq moslikni topish

VLOOKUP funktsiyasining mos keladigan turi "FALSE" bo'lganida qanday ishlashini aniq misolni qidirib topamiz. B5: E10 massivida ma'lum bir kompaniyaning asosiy vositalari, ularning balans qiymati, inventarizatsiya raqami va joylashuvi ko'rsatilgan. B2 katakka jadvaldagi inventarizatsiya raqamini topib, uni C2 katakchaga joylashtirish kerak bo'lgan nom berilgan.

Excelda VLOOKUP funktsiyasi

Quyidagi formulani yozamiz: \u003d VLOOKUP (B2; B5: E10; 3; FALSE).

Bu erda birinchi argument jadvalni B2 katakchadan qiymatini izlash kerakligini bildiradi, ya'ni. so'zi "Faks". Ikkinchi dalil, qidiruv jadvali B5: E10 oralig'ida ekanligini va birinchi ustundan "Faks" so'zini qidirishingiz kerakligini aytadi, ya'ni. B5: B10 qatorida. Uchinchi dalil dasturga hisoblash natijasi massivning uchinchi ustunida joylashganligini aytadi, ya'ni. D5: D10. To'rtinchi argument FALSE, ya'ni. to'liq o'yin talab qilinadi.

Shunday qilib, funktsiya B2 katakchadan "Faks" qatorini oladi va uni B5: B10 massivida yuqoridan pastgacha qidiradi. Match topilgandan so'ng (8-qator), funktsiya D ustunidan tegishli qiymatni qaytaradi, ya'ni. D8 ning tarkibi. Aynan shu narsa bizga kerak edi, muammo hal qilindi.

Agar kerakli qiymat topilmasa, funktsiya qaytadi.

VLOOKUP yordamida loyqa o'yinni topish

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

B5 qatori: C12 kredit miqdoriga qarab kreditlar bo'yicha foiz stavkalarini ko'rsatadi. B2 katakchada biz kredit miqdorini ko'rsatamiz va biz bunday operatsiya uchun C2 stavkasini olishni xohlaymiz. Vazifa shundan iboratki, yig'indisi har qanday narsa bo'lishi mumkin va massivda ko'rsatilganiga to'g'ri kelishi ehtimoldan yiroq emas; aniq moslik bo'yicha qidirish mos emas:

Keyin bo'shashmasdan qidirish uchun formulani yozamiz: \u003d VLOOKUP (B2, B5: C12, 2, TRUE)... Endi dastur B ustunida keltirilgan barcha ma'lumotlarning eng kichik qismini qidiradi. Ya'ni, 8000 miqdorida 5000 qiymati tanlanadi va tegishli foiz ko'rsatiladi.

Excelda VLOOKUP qidiruvi

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

HLOOKUP funktsiyasi VLOOKUP funktsiyasi bilan bir xil sintaksisga ega, ammo natijani ustunlardan emas, balki qatorlardan izlaydi. Ya'ni, u jadvallarni yuqoridan pastga emas, balki chapdan o'ngga qarab skanerlaydi va ustunni emas, balki belgilangan qator raqamini ko'rsatadi.

BROWSE funktsiyasidan foydalangan holda ma'lumotlarni topish

LOOKUP funktsiyasi VLOOKUP-ga o'xshash ishlaydi, ammo boshqa sintaksisga ega. Ma'lumotlar jadvalida bir necha o'nlab ustunlar mavjud bo'lganda foydalanaman va VLOOKUP-dan foydalanish uchun qo'shimcha ustun sonini hisoblash kerak. Bunday hollarda VIEW funktsiyasi vazifani osonlashtiradi. Shunday qilib, sintaksis: \u003d QO'QISH ( Izlash_ qiymati; Array_for_search; Array_to_display) :

Qidirilayotgan qiymat - qidiriladigan ma'lumotlar yoki ma'lumotlarga havola;
Qidiruv qatori - biz shunga o'xshash qiymatni qidiradigan bitta satr yoki ustun. Ushbu qatorni o'sish tartibida tartiblashimiz kerak;
Ko'rsatish uchun qator- natijalarni ko'rsatish uchun ma'lumotlarni o'z ichiga olgan diapazon. Tabiiyki, u qidiruv qatori bilan bir xil darajada bo'lishi kerak.

Ushbu yozuv bilan siz natija qatoriga nisbiy bo'lmagan ma'lumotnoma berasiz. Va siz to'g'ridan-to'g'ri unga ishora qilasiz, ya'ni. chiqish ustunining sonini oldindan hisoblashning hojati yo'q. VLOOKUP funktsiyasi uchun birinchi misolda LOOKUP funktsiyasidan foydalanamiz (asosiy vositalar, inventarizatsiya raqamlari): \u003d KO'RISH (B2, B5: B10, D5: D10)... Muammo muvaffaqiyatli hal qilindi!

Microsoft Excel-dagi "VIEW" funktsiyasi

Nisbiy koordinatalar bo'yicha qidirish. SEARCH va INDEX funktsiyalari

Ma'lumotlarni qidirishning yana bir usuli bu SEARCH va INDEX funktsiyalarini birlashtirishdir.

Ulardan birinchisi massivdagi qiymatni qidirish va uning tartib raqamini olish uchun ishlatiladi: SEARCH ( Izlash_ qiymati; Ko'rilgan_array; [Uchrashuv turi] ). Funktsiya argumentlari:

Qidirilayotgan qiymat - kerakli dalil
Ko'zdan kechirish uchun qator - biz o'yinni qidiradigan bitta qator yoki ustun. Kerakli argument
Uchrashuv turi - aniq moslikni qidirish uchun "0" belgisini belgilang, "1" - eng past darajaga, "-1" - eng yuqori darajaga. Funktsiya ro'yxatning boshidan oxirigacha qidirganligi sababli, eng kichigini qidirishda, qidiruv ustunini kamayish tartibida saralash. Va ko'proq narsani qidirayotganda, uni o'sish tartibida tartiblang.

Kerakli qiymatning pozitsiyasi topildi, endi uni funktsiya yordamida ekranda namoyish etishingiz mumkin INDEKS( Array; Qator raqami; [Ustun_sahifa]) :

Array - argument qiymatni qaysi kataklar qatoridan tanlashni bildiradi
Qator raqami- ko'rsatmoqchi bo'lgan qatorning tartib 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 necha ustunlardan iborat bo'lsa ko'rsatilgan. Agar argument tashlansa, formulada jadvalning birinchi ustunidan foydalaniladi.

Keling, natijani olish uchun ushbu funktsiyalarni birlashtiraylik:

Excelda SEARCH va INDEX funktsiyalari

Excelda ma'lumotlarni topish va namoyish qilish usullari. Bundan tashqari, siz ularni hisob-kitoblarda ishlatishingiz, taqdimotda foydalanishingiz, ular bilan operatsiyalarni bajarishingiz, boshqa funktsiyalarni argument sifatida belgilashingiz va h.k.

Sizning bilimlaringiz va ko'nikmalaringiz qanday o'sib borayotganini va kuchayib borayotganini his qilyapsizmi? Unda to'xtamang, o'qishni davom eting! Keyingi xabarda biz ko'rib chiqamiz: bu qiyin va qiziqarli bo'ladi!

Xayrli kun, aziz aholi!

Vaqti-vaqti bilan, ba'zilarimiz (va ba'zilaridan ham kattaroqlari) kichik hajmdagi ma'lumotlarni qayta ishlash, uy byudjetini tuzish va tahlil qilishdan va ish, o'qish va hokazolar uchun har qanday hisob-kitoblar bilan yakunlash bilan shug'ullanishimiz kerak. Ehtimol, buning uchun eng mos vosita Microsoft Excel (yoki ehtimol boshqa analoglari, ammo ular kamroq tarqalgan).

Qidiruv natijasida menga Xabreyda shunga o'xshash mavzuda faqat bitta maqola berildi - "Talmud formulalar bo'yicha Google SpreadSheet-da". Bu Excelda ishlash uchun asosiy narsalarning yaxshi tavsifini beradi (garchi bu excelning o'zi haqida 100% bo'lmasa ham).

Shunday qilib, ma'lum bir so'rovlar / vazifalar havzasini to'plagan holda, ularni yozish va mumkin bo'lgan echimlarni taklif qilish g'oyasi paydo bo'ldi (barcha mumkin bo'lganlari bo'lmasa ham, lekin tezda natijalar beradi).

Bu foydalanuvchilar duch keladigan eng keng tarqalgan vazifalarni hal qilishga qaratilgan.

Yechimlarning tavsifi quyidagicha tuzilgan - asta-sekin murakkablashib boradigan dastlabki vazifani o'z ichiga olgan ish, har bir qadam uchun tushuntirishlar bilan batafsil echim berilgan. Funktsiyalar nomlari rus tilida beriladi, ammo ingliz tilidagi asl ism birinchi so'zda qavs ichida beriladi (chunki tajribaga ko'ra foydalanuvchilarning aksariyati ruscha versiyasini o'rnatgan).

Case_1: Mantiqiy funktsiyalar va qidirish funktsiyalari
"Plitada bir qator qadriyatlarim 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,

Bu erda bizga "IF" (IF) formulasi yordam beradi, bu mantiqiy formulalarga ishora qiladi va biz oldindan formulaga yozib qo'yadigan har qanday qiymatlarni keltirib chiqarishi mumkin. Iltimos, har qanday matn qiymatlari tirnoq yordamida yozilganligiga e'tibor bering.

Sintaksis formulasi quyidagicha:
IF (mantiqiy_test, [qiymat_if_ haqiqat], [qiymat_if_false])

Log_expression - bu TRUE yoki FALSE natijalariga olib keladigan ibora.
Value_if_true - mantiqiy ifoda to'g'ri bo'lsa chiqadigan qiymat
Value_if_false - mantiqiy ifoda noto'g'ri bo'lsa ko'rsatiladigan qiymat

Yechim formulasi sintaksisi:

\u003d IF (C5\u003e 5; "Buyurtma talab qilinmaydi"; "Buyurtma talab qilinadi")

Chiqish natijasida biz natijani olamiz:

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

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

Bunday holda, biz faqat bitta "IF" formulasidan foydalanish bilan cheklanib qolmaymiz, uning sintaksisiga boshqa formulani qo'shish kerak. Va bu yana bir mantiqiy VA formula bo'ladi.
Sintaksis formulasi quyidagicha:
VA (boolean1, [boolean2], ...)

Boolean1-2 va boshqalar. - tekshirilgan holat, uning hisoblanishi TRUE yoki FALSE qiymatini beradi

Natijani D2 katakchaga chiqarish:

\u003d IF (AND (C2\u003e 5; B2 \u003d "A"); 1; 0)

Shunday qilib, biz 2 formuladan foydalangan holda, muammomizga echim topamiz va natijaga erishamiz:

Vazifani murakkablashtirishga harakat qilaylik - yangi shart:

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

Eritma sintaksisi quyidagicha bo'ladi:
Natijani D2 katakchaga chiqarish:

\u003d IF (OR (VA (C2 \u003d 10; B2 \u003d "A"); VA (C2\u003e \u003d 5; B2 \u003d "B")); 1; 0)

Yozuvdan ko'rinib turibdiki, AND formulasidan foydalangan holda bitta OR sharti va ikkita shart IF formulasiga kiritilgan. Agar 2-darajadagi shartlardan kamida bittasi "Haqiqat" qiymatiga ega bo'lsa, unda "1" natijasi "Natija" ustunida ko'rsatiladi, aks holda u "0" bo'ladi.
Natija:

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

1 \u003d A
2 \u003d B
3 \u003d B
4 \u003d G

"IF" funktsiyasidan foydalangan holda muammoni echishda sintaksis quyidagicha bo'ladi:

\u003d IF (A2 \u003d 1; "A"; IF (A2 \u003d 2; "B"; IF (A2 \u003d 3; "C"; IF (A2 \u003d 4; "D"; 0)))))

Natija:

Ko'rib turganingizdek, bunday formulani yozish nafaqat noqulay va noqulay, balki tajribasiz foydalanuvchi xatoga yo'l qo'ygan taqdirda uni tahrirlashi uchun biroz vaqt ketishi mumkin.
Ushbu yondashuvning nochorligi shundaki, u oz miqdordagi shartlar uchun amal qiladi, chunki ularning barchasi qo'lda yozilishi va bizning formulamizni katta hajmlarga "shishirishi" kerak bo'ladi, ammo yondashuv to'liq "hamma narsaga yaroqli" qadriyatlar va foydalanishning universalligi bilan ajralib turadi.

Muqobil echim_1:
"CHOOSE" formulasidan foydalanib,
Funktsiya sintaksisi:
Tanlash (indeks_num, qiymat1, [qiymat2], ...)

Index_number - tanlanadigan 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 katakcha ma'lumotnomasi bo'lishi kerak.
Value1, value2, ... - bu SELECT funktsiyasi indeks raqami yordamida bajariladigan amalni yoki harakatni tanlaydigan 1 dan 254 gacha bo'lgan qiymat argumentlari. Argumentlar raqamlar, katakchalar, aniq ismlar, formulalar, funktsiyalar yoki matn bo'lishi mumkin.

Uni ishlatishda biz darhol belgilangan qiymatlarga qarab shartlarning natijalarini kiritamiz.
Vaziyat:

1 \u003d A
2 \u003d B
3 \u003d B
4 \u003d G

Formula sintaksisi:

\u003d TANLASH (A2; "A"; "B"; "C"; "D")

Natijada yuqoridagi IF zanjirli echimi bilan bir xil bo'ladi.
Ushbu formulani qo'llashda quyidagi cheklovlar qo'llaniladi:
"A2" katakchasida faqat raqamlar ko'rsatilishi mumkin (indeks raqami) va natija qiymatlari 1 dan 254 gacha o'sish tartibida ko'rsatiladi.
Boshqacha qilib aytganda, funktsiya faqat "A2" katakchada 1 dan 254 gacha bo'lgan sonlar o'sish tartibida ko'rsatilgan bo'lsa va shu formuladan foydalanishda ma'lum cheklovlar qo'yilsa ishlaydi.
O'sha. agar 5 raqamini ko'rsatganda "G" qiymati ko'rsatilishini istasak,

1 \u003d A
2 \u003d B
3 \u003d B
5 \u003d G

u holda formulalar quyidagi sintaksisga ega bo'ladi:
Natijani B2 katakchaga chiqarish:

\u003d TANLOV (A31; "A"; "B"; "C" ;; "D")

Ko'rib turganingizdek, biz formuladagi "4" qiymatini bo'sh qoldirib, "G" natijasini "5" seriya raqamiga o'tkazishimiz kerak.

Muqobil echim_2:
Bu erda biz har qanday ofis xodimini avtomatik ravishda "tajribali excel foydalanuvchisi" / sarcasm / ga aylantiradigan eng mashhur Excel funktsiyalaridan biriga keldik.
Formula sintaksisi:
VLOOKUP (qidiruv_olami, jadval, ustun_sahmari, [qator_viziti])

Lookup_value - bu funktsiya qidiradigan qiymat.
Jadval - bu ma'lumotlarni o'z ichiga olgan kataklar qatori. Aynan shu hujayralarda qidiruv amalga oshiriladi. Qiymatlar matnli, raqamli yoki mantiqiy bo'lishi mumkin.
Column_number - bu "Jadval" argumentidagi ustun raqami, agar u mos keladigan bo'lsa, qiymat chiqadi. Ustunlar varaqning umumiy panjarasi bo'yicha (A.B, C, D va boshqalar) emas, balki "Jadval" argumentida ko'rsatilgan qator ichida hisoblanishini tushunish muhimdir.
Range_lookup - funktsiya aniq mosligini yoki taxminiy mosligini topishini aniqlaydi.

Muhim: "VLOOKUP" funktsiyasi moslikni faqat birinchi noyob yozuv orqali qidiradi, agar kerakli_value "Table" argumentida bir necha marta mavjud bo'lsa va har xil qiymatlarga ega bo'lsa, u holda "VLOOKUP" funktsiyasi faqat BIRINCHI o'yinni topadi, qolgan barcha o'yinlarning natijalari ko'rsatilmaydi. (VLOOKUP) ma'lumotlar bilan ishlashning yana bir yondashuvi, ya'ni "ma'lumotnomalar" ni shakllantirish bilan bog'liq.
Yondashuvning mohiyati "Seek_value" argumentini ma'lum natijaga moslashtirish uchun "ma'lumotnoma" ni yaratish, unda asosiy qatordan alohida bo'lib, unda shartlar va ularga mos qiymatlar yoziladi:

Keyin, jadvalning ishchi qismida, ilgari to'ldirilgan ma'lumotnomaga havola bilan allaqachon formula yozilgan. O'sha. "D" ustunidagi ma'lumotnomada qiymat "A" ustunidan izlanadi va moslik topilganda "B" ustunida "E" ustunidan qiymat ko'rsatiladi.
Formula sintaksisi:
Natijani B2 katakchaga chiqarish:

Natija:

Keling, jadvallarni bir xil bo'lmagan holda jadvalga ma'lumotlarni tortish kerak bo'lgan vaziyatni tasavvur qilaylik. Quyidagi misolga qarang

Ikkala jadvalning "Mahsulot" ustunlaridagi satrlar bir-biriga to'g'ri kelmasligini ko'rishingiz mumkin, ammo bu "VLOOKUP" funktsiyasidan foydalanish uchun to'siq emas.
Natijani B2 katakchaga chiqarish:

Ammo hal qilishda biz yangi muammoga duch kelamiz - biz yozgan formulani "B" ustunidan "E" ustunigacha "cho'zishda" biz "ustun_number" argumentini qo'lda almashtirishimiz kerak bo'ladi. Bu juda mashaqqatli va noshukur vazifa, shuning uchun bizning yordamimizga yana bir funktsiya keladi - "COLUMN" (COLUMN).
Funktsiya sintaksisi:
KOLON ([link])

Malumot - bu ustun raqamini qaytarishni istagan katak yoki kataklar oralig'i.

Agar siz quyidagi yozuvlardan foydalansangiz:

u holda funktsiya joriy ustun sonini ko'rsatadi (formulasi yozilgan katakchada).
Natijada "VLOOKUP" funktsiyasida ishlatilishi mumkin bo'lgan raqam paydo bo'ldi, biz undan foydalanamiz va quyidagi formulani kiritamiz:
Natijani B2 katakchaga chiqarish:

\u003d VLOOKUP ($ A3; $ H $ 3: $ M $ 6; COLUMN (); 0)

COLUMN funktsiyasi joriy ustun sonini aniqlaydi, bu Column_num argumenti yordamida qidiruvdagi qidiruv ustunining sonini aniqlash uchun ishlatiladi.
Bundan tashqari, siz qurilishni ishlatishingiz mumkin:

"1" raqami o'rniga istalgan natijani olish uchun istalgan raqamdan foydalanishingiz mumkin (shuningdek, uni chiqarib tashlabgina qolmay, balki hosil bo'lgan qiymatga qo'shishingiz mumkin), agar biz kerakli raqam bilan ustundagi ma'lum bir katakka murojaat qilmoqchi bo'lmasangiz.
Natijada natija:

Biz mavzuni ishlab chiqishda davom etamiz va shartni murakkablashtiramiz: tasavvur qiling-a, bizda mahsulotlarda har xil ma'lumotlarga ega ikkita katalog mavjud va natijada jadvaldagi qiymatlarni "Katalog" ustunida qanday katalog ko'rsatilganiga qarab ko'rsatish kerak.
Vaziyat:

Agar "Ma'lumotnoma" ustunida 1 raqami ko'rsatilgan bo'lsa, ma'lumotlar "Malumotnoma_1" jadvalidan, agar raqam 2 bo'lsa, u holda "Ma'lumotnoma_2" jadvalidan ko'rsatilgan oyga muvofiq chiqarilishi kerak

Darhol xayolga keladigan echim quyidagicha:

\u003d IF ($ B3 \u003d 1; VLOOKUP ($ A3; $ G $ 3: $ I $ 6; COLUMN () - 1; 0); VLOOKUP ($ A3; $ K $ 3: $ M $ 6; COLUMN () - 1; 0 ))

ijobiy tomonlari: ma'lumotnomaning nomi har qanday narsa bo'lishi mumkin (matn, raqamlar va ularning kombinatsiyasi), kamchiliklar - agar 3 dan ortiq variant mavjud bo'lsa, unchalik mos emas.
Agar katalog raqamlari har doim raqamlar bo'lsa, quyidagi echimdan foydalanish mantiqan to'g'ri keladi:
Natijani C3 katakchaga chiqarish:

\u003d VLOOKUP ($ A3; TANLOV ($ B3; $ G $ 3: $ I $ 6; $ K $ 3: $ M $ 6); COLUMN () - 1; 0)

ijobiy tomonlari: formulada 254 ta ma'lumotnomalar nomlari bo'lishi mumkin, minuslari - ularning nomi aniq raqamli bo'lishi kerak.
SELECT funktsiyasidan foydalangan holda formulaning natijasi:

Bonus: "lookup_value" argumentidagi ikki yoki undan ortiq funktsiyalar uchun VLOOKUP.
Vaziyat:

Tasavvur qiling, har doimgidek bizda jadvallar ko'rinishidagi ma'lumotlar majmuasi mavjud (agar bo'lmasa, unda biz ularga ma'lumot keltiramiz), qatordan ma'lum mezonlarga muvofiq qiymatlarni olish va ularni boshqa jadval shaklida joylashtirish kerak.

Ikkala jadval ham quyida ko'rsatilgan:

Jadval shakllaridan ko'rinib turibdiki, har bir pozitsiya nafaqat nomga ega (bu noyob emas), balki ma'lum bir sinfga tegishli va o'ziga xos qadoqlash variantiga ega.
Ism va sinf va qadoqlash kombinatsiyasidan foydalangan holda biz yangi atributni yaratishimiz mumkin, buning uchun jadvaldagi ma'lumotlar "Qo'shimcha atribut" qo'shimcha ustunini yaratamiz, uni quyidagi formuladan foydalanib to'ldiramiz:

"&" Belgisidan foydalanib, biz uchta belgini birlashtiramiz (so'zlar orasidagi ajratuvchi har qanday bo'lishi mumkin, shuningdek umuman bo'lmasligi mumkin, asosiysi qidirish uchun shunga o'xshash qoidadan foydalanish)
Formulaning analogi CONCATENATE funktsiyasi bo'lishi mumkin, bu holda u quyidagicha ko'rinadi:

\u003d CONCATENATE (H3; "_"; I3; "_"; J3)

Ma'lumotlar bilan jadvaldagi har bir yozuv uchun qo'shimcha funktsiya yaratilgandan so'ng, biz ushbu xususiyat uchun quyidagicha ko'rinadigan qidirish funktsiyasini yozishga kirishamiz:
Natijani D3 katakchaga chiqarish:

\u003d IFERROR (VLOOKUP (A2 & "_" & B2 & "_" $ G $ 2: $ K $ 6; 5; 0); 0)

"VLOOKUP" funktsiyasida "axtardigan_value" argumenti sifatida biz bir xil uchta atributdan foydalanamiz (name_class_packaging), lekin biz uni to'ldirish uchun jadvalda allaqachon olamiz va to'g'ridan-to'g'ri argumentga kiritamiz (variant sifatida qo'shimcha ustundagi argument uchun qiymatni tanlashimiz mumkin) to'ldirish uchun jadval, lekin bu harakat haddan tashqari ko'p bo'ladi).
Sizga shuni eslatib qo'yamanki, IFERROR funktsiyasidan foydalanish kerakli qiymat hali topilmasa kerak, va VLOOKUP funktsiyasi "# N / A" qiymatini aks ettiradi (quyida batafsil ma'lumot).
Natijada quyidagi rasmda keltirilgan:

Ushbu texnikadan ko'proq funktsiyalar uchun foydalanish mumkin, faqat bitta shart - bu hosil bo'lgan kombinatsiyalarning o'ziga xosligi, agar u kuzatilmasa, natija noto'g'ri bo'ladi.

Case_3 Massivdagi qiymatni qidiring yoki VLOOKUP bizga yordam bera olmaganida

Hujayra qatorida biz uchun kerakli qiymatlar mavjudligini tushunish zarur bo'lgan vaziyatni ko'rib chiqing.
Vazifa:

qiymat "Izlash sharti" ustunida ko'rsatilgan va "Izlash uchun massiv" ustunida mavjudligini aniqlash kerak.

Vizual ravishda hamma narsa quyidagicha:

Ko'rib turganimizdek, "VLOOKUP" funktsiyasi bu erda kuchsizdir, chunki biz aniq moslikni emas, balki katakchada kerakli qiymat mavjudligini qidirmoqdamiz.
Muammoni hal qilish uchun siz bir nechta funktsiyalar kombinatsiyasidan foydalanishingiz kerak, ya'ni:
"IF A"
"ESLIOSHIBKA"
"STROCHN"
"TOPMOQ"

Hammasi uchun "IF" ni biz allaqachon ajratib qo'yganmiz, shuning uchun "IFERROR" funktsiyasiga o'tamiz.

IFERROR (qiymat, qiymat_on_error)

Qiymat - bu xatolar uchun tekshiriladigan argument.
Error_value - formulani baholashda xatolikka qaytarilgan qiymat. Quyidagi turdagi xatolar mumkin: # N / A, #VALUE!, #REF!, # DIV / 0!, #NUM!, #NAME? va # Bo'sh!

Muhim: ma'lumot formalari va ma'lumotnomalar bilan ishlashda ushbu formula deyarli doimo talab qilinadi, chunki tez-tez sodir bo'ladiki, kerakli qiymat qidiruvda bo'lmaydi va bu holda funktsiya xato qaytaradi. Agar katakchada xato ko'rsatilsa va katak, masalan, hisoblashda qatnashsa, u holda xato bilan ham muvaffaqiyatsiz bo'ladi. Bundan tashqari, formulada xato bo'lgan hujayralarga turli xil qiymatlar berilishi mumkin, bu ularni birlashtirishni osonlashtiradi. Shuningdek, xatolik yuz berganda, siz boshqa funktsiyalarni bajarishingiz mumkin, bu massivlar bilan ishlashda juda qulay va juda tarvaqaylab sharoitlarni hisobga olgan holda formulalarni yaratishga imkon beradi.

"POWER"

Matn - kichik harfga o'zgartirilgan matn.

Muhim: LOWER funktsiyasi harfli bo'lmagan belgilar o'rnini bosmaydi.
Formuladagi roli: FIND funktsiyasi qidirib topilganligi va katta-kichikligi sezgir bo'lganligi sababli, butun matnni bitta holatga keltirish kerak, aks holda "choy" "choy" ga teng bo'lmaydi va hokazo. Agar registr qiymati qiymatlarni izlash va tanlash uchun shart bo'lmasa, bu juda muhimdir, aks holda "LOWER" formulasidan foydalanilmasligi mumkin, shuning uchun qidiruv yanada aniqroq bo'ladi.

Endi FIND funktsiyasi sintaksisiga oid ko'proq ma'lumotlar.

FIND (search_text, crawled_text, [start_position])

Search_text - bu topish kerak bo'lgan matn.
Searched_text - bu qidirayotgan matnni topadigan matn.
Start_position - bu qidiruvni boshlaydigan belgi. "View_text" matnidagi birinchi belgi 1 raqami. Agar raqam ko'rsatilmagan bo'lsa, u sukut bo'yicha 1 ga teng.

Eritma formulasining sintaksisi quyidagicha bo'ladi:
Natijani B2 katakchaga chiqarish:

\u003d IF (IFERROR (FIND (LOWER (A2); LOWER (E2); 1); 0) \u003d 0; "fail"; "bingo!")

Keling, formulaning mantig'ini harakatlar bilan tahlil qilaylik:

LOWER (A2) - A2 katakchadagi "Lookup_text" argumentini kichik matnga o'zgartiradi
FIND funktsiyasi "LOWER (E2)" funktsiyasi orqali o'zgartirilgan "Viewed_text" massivida o'zgartirilgan "Look for_text" argumentini qidirishni boshlaydi, shuningdek kichik matnga o'tkaziladi.
Agar, funktsiya moslikni topsa, ya'ni. mos keladigan so'z / qiymatning birinchi belgisining tartib raqamini qaytaradi; "IF" formulasidagi TRUE sharti ishga tushiriladi, chunki natijada nolga teng emas. Natijada, "Natija" ustunida "Bingo!" Qiymati aks etadi.
Agar funktsiya mos kelmasa, ya'ni mos keladigan so'z / qiymatning birinchi belgisining tartib raqami ko'rsatilmagan va qiymat o'rniga xato qaytarilgan, "IFERROR" formulasidagi shart ishga tushirilgan va "0" ga teng qiymat qaytarilgan, bu "IF" formulasidagi FALSE shartiga mos keladi, chunki natijada olingan qiymat "0" dir. Natijada, "muvaffaqiyatsiz" qiymati "Natija" ustunida ko'rsatiladi.

Yuqoridagi rasmdan ko'rinib turibdiki, "LOWER" va "FIND" funktsiyalari tufayli biz katakchada va joylashuvidan qat'i nazar kerakli qiymatlarni topamiz, ammo siz 5-qatorga e'tibor berishingiz kerak.
Qidiruv atamasi "111", ammo qidiruv qatorida "1111111 cookies" yozilgan, ammo formulada "Bingo!" Bu "111" qiymati "1111111" qiymatlari qatoriga kiritilganligi sababli sodir bo'ladi, natijada moslik topiladi. Aks holda, bu shart ishlamaydi.

Case_4 Bir nechta shartlar bo'yicha massivdagi qiymatni qidirish yoki VLOOKUP bizga yordam bera olmay qolganda

Bir necha shartlar bo'yicha, ya'ni "Ism" va "Oy" qiymatlari bo'yicha "Ma'lumotnoma" ikki o'lchovli qatorida "Natija bilan jadval" dan qiymatni topish kerak bo'lgan vaziyatni tasavvur qiling.
Vazifaning jadval shakli quyidagicha bo'ladi:

Vaziyat:

Natija bilan jadvalda "Ism" va "Oy" shartlarining mos kelishiga muvofiq ma'lumotlarni mahkamlash kerak.

Bunday muammoni hal qilish uchun "INDEX" va "SEARCH" funktsiyalarining kombinatsiyasi mos keladi

INDEX funktsiyasi sintaksisi

INDEX (qator, satr_num, [ustun_num])

Array - bu qidiruv shartlari mos keladigan bo'lsa, qiymatlar ko'rsatiladigan kataklar oralig'i.
Agar massivda faqat bitta satr yoki bitta ustun mavjud bo'lsa, mos ravishda satr_num yoki column_num ixtiyoriydir.
Agar massiv bir nechta satr va bitta ustunni egallasa va "satr_num" va "ustun_num" argumentlaridan faqat bittasi ko'rsatilgan bo'lsa, u holda INDEX funktsiyasi butun qator yoki "qator" argumentining butun ustunidan iborat qatorni qaytaradi.
Line_number - bu qiymatni qaytarishni istagan qatordagi qator.
Column_number - bu qiymatni qaytarishni istagan qatordagi ustun soni.

Boshqacha qilib aytganda, funktsiya "Array" argumentida ko'rsatilgan qatordan "Row_num" va "Column_num" argumentlarida ko'rsatilgan koordinatalar kesishmasida joylashgan qiymatni qaytaradi.

MATCH funktsiyasi uchun sintaksis

MATCH (qarash_mavzusi, qidirish_arrasi, [match_type])

Lookup_value - bu lookup_array qiymatlariga mos keladigan qiymat. Lookup_value qiymat (raqam, matn yoki mantiqiy) yoki bunday qiymatni o'z ichiga olgan katakka havola bo'lishi mumkin.
Lookup_array - bu qidirish uchun kataklar oralig'i.
Collation_type - bu ixtiyoriy argument. -1, 0 yoki 1 raqami.

MATCH funktsiyasi belgilangan elementni kataklar oralig'ida qidiradi va shu elementning diapazondagi nisbiy holatini qaytaradi.
"INDEX" va "SEARCH" funktsiyalarining kombinatsiyasidan foydalanishning mohiyati shundan iboratki, biz "koordinata o'qlari" bo'ylab qiymatlarning koordinatalarini nomlari bo'yicha qidiramiz.
Y o'qi Nom ustuni, X o'qi Oylar qatori bo'ladi.

Formulaning bir qismi:

Qidirish ($ A4; $ I $ 4: $ I $ 7; 0)

Y o'qidagi sonni qaytaradi, bu holda u 1 ga teng bo'ladi, chunki "A" qiymati kerakli diapazonda mavjud va ushbu diapazonda nisbiy "1" pozitsiyasiga ega.
formulaning bir qismi:

Qidirish (B $ 3; $ J $ 3: $ L $ 3; 0)

# N / A ni qaytaradi, chunki "1" qiymati ko'rilgan oraliqda emas.

Shunday qilib, biz "INDEX" funktsiyasi "Array" argumentini qidirishda foydalanadigan nuqta (1; # N / A) koordinatalarini oldik.
B4 katak uchun to'liq yozilgan funktsiya quyidagicha bo'ladi:

\u003d INDEX ($ J $ 4: $ L $ 7; SEARCH ($ A4; $ I $ 4: $ I $ 7,0); SEARCH (B $ 3; $ J $ 3: $ L $ 3,0))

Darhaqiqat, biz kerakli qiymat koordinatalarini bilsak, funktsiya quyidagicha bo'lar edi:

\u003d INDEX ($ J $ 4: $ L $ 7; 1; # yo'q)))

ColumnNumber # N / A bo'lganligi sababli, B4 katak uchun natija mos keladi.
Natijadan ko'rinib turibdiki, natija bilan jadvaldagi barcha qiymatlar mos yozuvlarga to'g'ri kelmaydi va natijada biz jadvaldagi ba'zi qiymatlar "# N / A" shaklida ko'rsatilishini ko'rmoqdamiz, bu esa keyingi hisob-kitoblar uchun ma'lumotlardan foydalanishni murakkablashtiradi.
Natija:

Ushbu salbiy ta'sirni zararsizlantirish uchun biz avval o'qigan "IFERROR" funktsiyasidan foydalanamiz va xatoga qaytarilgan qiymatni "0" bilan almashtiramiz, shunda formulaga o'xshash bo'ladi

\u003d IFERROR (INDEX ($ J $ 4: $ L $ 7; SEARCH ($ A4; $ I $ 4: $ I $ 7,0); SEARCH (B $ 3; $ J $ 3: $ L $ 3,0)); 0)

Natija namoyishi:

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

Case_5 Raqamlar oralig'idagi qiymatni qidirish

Tasavvur qiling, biz ma'lum bir diapazondagi raqamlarga ma'lum bir xususiyatni berishimiz kerak.
Vaziyat:
Mahsulot qiymatiga qarab, unga ma'lum bir toifani berish kerak.
Agar qiymat oralig'ida bo'lsa

0 dan 1000 \u003d A gacha
1001 dan 1500 \u003d B gacha
1501 dan 2000 gacha \u003d V
2001 yildan 2500 gacha \u003d Y
2501 dan ortiq \u003d D

LOOKUP funktsiyasi qator, ustun yoki massivdan qiymatni qaytaradi. Funktsiya ikkita sintaktik shaklga ega: vektor va massiv.

LOOKUP (qidiruv_vali, qidirish_vektori, [natija_vektori])

Lookup_value - LOOKUP birinchi vektorda qidiradigan qiymat. Lookup_value raqam, matn, mantiqiy nom, ism yoki qiymatga mos yozuvlar bo'lishi mumkin.
Lookup_vector - bu bitta satr yoki bitta ustun oralig'i. Lookup_vector-ning qiymatlari matn, raqamlar yoki mantiqiy qiymatlar bo'lishi mumkin.
Lookup_vector-dagi qiymatlar o'sish tartibida bo'lishi kerak: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; aks holda, LOOKUP noto'g'ri natijani qaytarishi mumkin. Katta va kichik harflar teng deb hisoblanadi.
Result_Vector - bitta qator yoki ustun oralig'i. Result_vector view_vector bilan bir xil o'lchamda bo'lishi kerak.

\u003d QO'YISH (E3, $ A $ 3: $ A $ 7, $ B $ 3: $ B $ 7)

"Viewed_vector" va "Vector_result" argumentlari qator shaklida yozilishi mumkin - bu holda siz ularni Excel varag'idagi alohida jadvalda ko'rsatishingiz shart emas.
Bunday holda, funktsiya quyidagicha bo'ladi:
Natijani B3 katakchaga chiqarish:
\u003d LOOKUP (E3; (0; 1001; 1501; 2001; 2501); ("A"; "B"; "C"; "D"; "D"))

Case_6 Atributlar bo'yicha raqamlarni yig'ish

Uchta funktsiyadan ma'lum mezonlarga asoslanib raqamlarni yig'ish uchun foydalanish mumkin:
SUMIF - faqat bitta atributni jamlaydi
SUMIFS - bir nechta funktsiyalar bo'yicha yig'indilar
SUMPRODUCT - bir nechta funktsiyalar bo'yicha yig'indilar
Bundan tashqari, SUM va massiv formulasi funktsiyasidan foydalanadigan variant mavjud, bu erda SUM formulasi massivga ko'tariladi:
((\u003d SUM (() * ()))
ammo bu yondashuv juda noqulay va "SUMPRODUCT" formulasi bilan funktsional jihatdan bir-biriga to'liq mos keladi.
Endi "SUMPRODUCT" sintaksisida batafsil:

SUMPRODUCT (massiv1, [massiv2], [massiv3], ...)

Array1 - bu sizning komponentlaringizni ko'paytirmoqchi bo'lgan va natijalarni qo'shmoqchi bo'lgan birinchi qator.
Array2, array3… - 2 dan 255 gacha bo'lgan massivlar, ularning tarkibiy qismlari ko'paytirilishi va keyin qo'shilishi kerak.

Vaziyat:

Muayyan davr uchun har bir mahsulot uchun jo'natmalar qiymatiga qarab umumiy miqdorni toping:

Ma'lumotlar bilan jadvaldan ko'rinib turibdiki, xarajatlarni hisoblash uchun siz narxni miqdorga ko'paytirishingiz kerak va natijada tanlov shartlarini qo'llagan holda natija bilan jadvalga o'tkaziladi.
Biroq, SUMPROIZ formulasi bunday hisob-kitoblarni formulada amalga oshirishga imkon beradi.
Natijani B4 katakchaga chiqarish:

\u003d SUMPRODUCT (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ K $ 3: $ K $ 11\u003e \u003d B $ 3) * ($ K $ 3: $ K $ 11
Keling, formulani parcha-parcha tahlil qilaylik:
- biz tanlov shartini jadvaldagi "Ism" ustuniga natijalar bilan jadvaldagi "Ism" ustunidagi ma'lumotlar bilan o'rnatdik
($ K $ 3: $ K $ 11\u003e \u003d B $ 3) * ($ K $ 3: $ K $ 11 - vaqtni belgilash sharti, sana joriy oyning birinchi kunidan katta yoki unga teng, ammo keyingi oyning birinchi kunidan kam. Xuddi shunday, shart natija bilan jadvalda, massiv ma'lumotlar bilan jadvalda.
- jadvaldagi "Miqdor" va "Narx" ustunlarini ma'lumotlar bilan ko'paytiramiz.
Ushbu funktsiyaning shubhasiz afzalligi - bu yozish shartlarining erkin tartibi, ular har qanday tartibda yozilishi mumkin, bu natijaga ta'sir qilmaydi.
Natija:
Endi shartni murakkablashtiramiz va "cookie" nomi bo'yicha tanlov faqat "kichik" va "katta" sinflar tomonidan va "rulon" nomi bilan amalga oshiriladi, degan talabni qo'shib qo'yamiz, faqat "murabbo" sinfidan tashqari:
Natijani B4 katakchaga chiqarish:
\u003d SUMPRODUCT (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 11
Cookie fayllarini tanlash formulasiga yangi shart qo'shildi:
(($ I $ 3: $ I $ 11 \u003d "kichik") + ($ I $ 3: $ I $ 11 \u003d "katta"))
- ko'rib turganingizdek, bitta ustundagi ikki yoki undan ortiq shartlar "+" belgisi yordamida alohida guruhga ajratiladi va shartlarni qo'shimcha qavs ichiga oladi.
Bulochka uchun tanlov formulasiga yangi shart ham qo'shildi:
\u003d SUMPRODUCT (($ A5 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 11 "Jam bilan"); ($ L $ 3: $ L $ 11) * ($ K $ 3: $ K $ 11))

bu:
($ I $ 3: $ I $ 11<>"Murabbo bilan")
- aslida, bu formulada cookie fayllari bilan bir qatorda tanlov shartlarini ham yozish mumkin edi, ammo keyin formulada uchta shartni sanab o'tish kerak bo'ladi, bu holda istisno yozish osonroq bo'ladi - buning uchun "murabbo bilan" teng emas, buning uchun biz qiymatdan foydalanamiz "<>».
Umuman olganda, agar xususiyatlar / sinflar guruhlari oldindan ma'lum bo'lsa, ularni barcha guruhlarni funktsiyaga yozib qo'yishdan ko'ra, ma'lumotnomalarni yaratib, ularni ushbu guruhlarga birlashtirish yaxshiroqdir.
Natija:
Mana, biz qisqa qo'llanmamizning oxiriga keldik, bu aslida ancha kattaroq bo'lishi mumkin edi, ammo maqsad hali ham eng ko'p uchraydigan holatlarga echim topish edi va muayyan (ammo juda ham qiziqarli holatlar) echimini tavsiflash emas edi.
Umid qilamanki, qo'llanma kimgadir Excel yordamida muammolarni hal qilishda yordam beradi, chunki bu mening ishim behuda ketmaganligini anglatadi!
Vaqtingiz uchun tashakkur!

Aytaylik, sizning hisobotingizda ko'plab ma'lumotlar va ko'plab ustunlar mavjud jadval mavjud. Bunday jadvallarni vizual tahlil qilish juda qiyin. Va hisobot bilan ishlashning vazifalaridan biri - ma'lum bir oyga tegishli qatorlar va ustunlar sarlavhalari bo'yicha ma'lumotlarni tahlil qilish. Bir qarashda, bu juda oddiy vazifa, ammo uni bitta standart funktsiya yordamida hal qilib bo'lmaydi. Ha, albatta, vositadan foydalanishingiz mumkin: "HOME" - "Tahrirlash" - CTRL + F ni qidirish oynasini Excel varag'idagi qiymatlarni qidirish uchun chaqirish. Yoki, jadval uchun shartli formatlash qoidasini yarating. Ammo keyin olingan natijalar bilan qo'shimcha hisob-kitoblarni amalga oshirish mumkin bo'lmaydi. Shuning uchun tegishli formulani yaratish va to'g'ri qo'llash kerak.

Excel massivida qiymatni topish

Muammoni hal qilish sxemasi quyidagicha ko'rinadi:

b1 katagiga bizni qiziqtirgan ma'lumotlarni kiritamiz;

b2 yacheykada B1 yacheykaning qiymati joylashgan ustun sarlavhasi aks etadi

b3 yacheykada B1 yacheykaning qiymatini o'z ichiga olgan qator nomi ko'rsatiladi.

Aslida, Excelda koordinatalarni qidirishingiz kerak. Bu nima uchun? Ko'pincha jadvalning koordinatalarini qiymatlari bo'yicha olishimiz kerak. Matritsani orqaga qarab tahlil qilish kabi bir oz. Qisqacha aytganda aniq bir misol shunga o'xshash ko'rinadi. Raqamdagi maqsad - bu boshlang'ich qiymat, siz ushbu maqsadga kim va qachon yaqinligini aniqlashingiz kerak. Masalan, biz quyidagi rasmda ko'rsatilgandek, uch chorak davomida sotilgan buyumlar sonini hisobot qiladigan oddiy ma'lumotlar matritsasidan foydalanamiz. Barcha raqamlar bir-biriga mos kelishi muhimdir. Agar siz Excel jadvalini noldan yaratishni va to'ldirishni xohlamasangiz, unda maqolaning oxirida siz tayyor misolni yuklab olishingiz mumkin.

Turli xil murakkablikdagi echimlarni ketma-ket ko'rib chiqamiz va maqolaning oxirida - yakuniy natija.

Excel ustunidan qiymatni topish

Birinchidan, jadval ustuni sarlavhalarini qiymat bo'yicha qanday olishni o'rganamiz. Buning uchun quyidagi amallarni bajaring:

B1 katakka 5277-jadvaldan olingan qiymatni kiriting va kirish maydonining o'qilishi uchun uning fonini ko'k rang bilan belgilang (bundan keyin biz yangi qiymatlar bilan tajriba o'tkazish uchun B1 katakchaga boshqa raqamlarni kiritamiz).

C2 katagiga quyidagi qiymatni o'z ichiga olgan jadval ustunining sarlavhasini olish uchun formulani kiriting:

Tasdiqlash uchun formulani kiritgandan so'ng, CTRL + SHIFT + Enter tugmalar birikmasini bosing, chunki formulani massivda bajarish kerak. Agar hamma narsa to'g'ri bajarilgan bo'lsa, formulalar satrining chetlarida jingalak qavslar () paydo bo'ladi.

Excel satrida qiymatni topish

Endi biz bir xil qiymat uchun chiziq raqamini olamiz (5277). Buning uchun C3 katakchaga quyidagi formulani kiriting:

Tasdiqlash formulasini kiritgandan so'ng, CTRL + SHIFT + Enter tugmalar birikmasini yana bir marta bosing va natijani oling:

Formula 9 raqamini qaytarib berdi - jadval satrining sarlavhasini mos jadval qiymati bo'yicha topdi. Natijada, biz D9 qiymatining to'liq manziliga egamiz.

Jadvalning ustun sarlavhasi va satr nomini qanday olish mumkin

Endi butun varaqning emas, balki joriy jadvalning qiymati bo'yicha koordinatalarini olishni o'rganamiz. Qisqacha aytganda, biz D9 o'rniga 5277 qiymati bo'yicha sarlavhalarni topishimiz kerak:

jadval ustuni uchun - mart;

satr uchun - 4-modda.

Ushbu muammoni hal qilish uchun biz C2 va C3 katakchalarda allaqachon olingan qiymatlar bilan formuladan foydalanamiz. Buning uchun biz buni qilamiz:

Natijada jadvalning ichki koordinatalari qiymat bo'yicha olinadi - mart; 4-modda:

Excel oralig'ida takrorlanadigan qiymatlarni topish

Jadval qiymatlari orasida dublikatlarning mavjudligini nazorat qilish uchun biz dublikatlar borligi to'g'risida xabar beradigan va ularning sonini hisoblaydigan formulani yaratamiz. Buning uchun formulani E2 katakchaga kiriting:

Bundan tashqari, jadval qismi oralig'ida shartli formatlash qoidasini yarataylik:

Ko'rib turganingizdek, agar dublikatlar mavjud bo'lsa, sarlavha formulasi birinchi nusxadagi sarlavhani gorizontal ravishda (chapdan o'ngga) oladi. Va satr nomini (raqamini) olish formulasi vertikal ravishda (yuqoridan pastgacha) birinchi nusxadagi raqamni oladi. Ushbu echimni tuzatishning ikkita usuli mavjud:

Bunday holda, biz formulalarni bir yoki ikkinchisini o'zgartiramiz, lekin bir vaqtning o'zida ikkitasini emas. Eslatib o'tamiz, eski formulalar C3 katakchada qolishi kerak edi:

Bu erda birinchi dublikatning koordinatalari vertikal ravishda (yuqoridan pastgacha) ko'rsatiladi - varaq va avgust uchun I7; Jadval uchun mahsulot2. Keling, ushbu variantni keyingi so'nggi misol uchun qoldiramiz.

Excel oralig'ida eng yaqin qiymatni toping

Ushbu jadval hali ham mukammal emas. Axir, tahlil qilayotganda uning barcha qiymatlarini aniq bilishingiz kerak. Agar formula jadvaldagi B1 katakchaga kiritilgan raqamni topmasa, unda xato qaytariladi - #VALUE! Jadvalda boshlang'ich raqam yo'q bo'lganda, formulaning o'zi jadvalning eng yaqin qiymatini tanlasa, bu juda yaxshi bo'ladi. Jadvallarni tahlil qilish uchun shunday dastur yaratish uchun F1 katakchaga yangi formulani kiriting:

Keyin quyidagilar boshqa barcha formulalarda havolani o'zgartirish o'rniga B1 o'rniga F1 bo'lishi kerak! Shuningdek, siz havolani shartli formatlashda o'zgartirishingiz kerak. Tanlang: "HOME" - "Uslublar" - "Shartli formatlash" - "Qoidalarni boshqarish" - "Qoidalarni tahrirlash". Va bu erda parametrlarda B1 o'rniga F1 belgilang. Dasturning ishlashini tekshirish uchun B1 katakchasiga jadvalda bo'lmagan raqamlarni kiriting, masalan: 8000. Bu yakuniy natijaga olib keladi:

Endi siz istalgan boshlang'ich qiymatni kiritishingiz mumkin va dastur o'zi jadvalga eng yaqin raqamni tanlaydi. Keyin u joriy qiymat uchun ustun sarlavhasi va satr nomini ko'rsatadi. Masalan, 5000 raqamini kiritsangiz, biz yangi natijaga erishamiz:

Excel dasturimiz dastlabki qiymati 5000 uchun eng yaqin 4965 qiymatini topdi. Bunday dastur biznesni rejalashtirishda turli xil analitik vazifalarni avtomatik ravishda hal qilish, maqsadlarni belgilash, oqilona echim topish va boshqalar uchun foydali bo'lishi mumkin. Natijada olingan qatorlar va ustunlar yangi Excel formulalari yordamida ushbu turdagi hisobotlarni hisoblash imkoniyatlarini yanada kengaytirishga imkon beradi.

Ushbu o'quv qo'llanmada funktsiyalarning asosiy afzalliklari haqida so'z boradi INDEKS va QIDIRMOQ bilan solishtirganda ularni yanada jozibali qiladigan Excelda VLOOKUP... Siz funktsiyani bajaradigan ko'plab murakkab muammolarni osonlikcha engishingizga yordam beradigan formulalarning bir nechta misollarini ko'rasiz VLOOKUP kuchsiz.
So'nggi bir nechta maqolalarda biz yangi boshlagan foydalanuvchilarga funktsiya asoslarini o'rgatish uchun ko'p ishladik. VLOOKUP va rivojlangan foydalanuvchilar uchun yanada murakkab formulalar namunalarini ko'rsating. Endi biz sizni ishlatishdan qaytarmasak, harakat qilamiz VLOOKUP, keyin hech bo'lmaganda Excelda vertikal qidirishni amalga oshirishning muqobil usullarini ko'rsating.

Buning nima keragi bor? - deb so'raysiz. Ha, chunki VLOOKUP Excelda yagona qidirish funktsiyasi emas va uning cheklanganligi ko'p holatlarda kerakli natijalarni olishingizga to'sqinlik qilishi mumkin. Boshqa tomondan, funktsiyalar INDEKS va QIDIRMOQ - yanada moslashuvchan va bir qator xususiyatlarga ega bo'lib, ular bilan solishtirganda ularni yanada jozibali qiladi VLOOKUP.

INDEX va SEARCH haqida asosiy ma'lumotlar

Ushbu o'quv qo'llanmaning maqsadi funktsiyalarning imkoniyatlarini ko'rsatishdir INDEKS va QIDIRMOQ Excelda vertikal qidirishni amalga oshirish uchun biz ularning sintaksisiga va qo'llanilishiga to'xtalmaymiz.

Biz bu erda mohiyatni tushunish uchun zarur bo'lgan minimal miqdorni beramiz va undan keyin foydalanishning afzalliklarini ko'rsatadigan formulalar misollarini batafsil tahlil qilamiz INDEKS va QIDIRMOQ o'rniga VLOOKUP.

INDEX - sintaksis va funktsiyani qo'llash

Funktsiya INDEKS Excelda (INDEX) berilgan qator va ustunlar sonidagi massivdan qiymatni qaytaradi. Funktsiya quyidagi sintaksisga ega:

Har bir dalil juda oddiy tushuntirishga ega:

qator (massiv) - bu qiymat chiqariladigan kataklar oralig'i.

qator_num (line_number) - bu qiymatni chiqaradigan qatordagi qator. Agar ko'rsatilmagan bo'lsa, unda dalil talab qilinadi. ustun_num (ustun_ raqami).

ustun_num (column_number) - bu qiymatni chiqaradigan qatordagi ustun soni. Agar ko'rsatilmagan bo'lsa, unda dalil talab qilinadi. qator_num (satr_ raqami)

Agar ikkala argument ham ko'rsatilgan bo'lsa, u holda funktsiya INDEKS belgilangan satr va ustun kesishmasidagi katakchadan qiymatni qaytaradi.
Bu erda funktsiyalarning eng oddiy misoli keltirilgan INDEKS (INDEKS):

INDEX (A1: C10,2,3)
\u003d INDEX (A1: C10; 2; 3)
Formula oraliqni qidiradi A1: C10 va katakning qiymatini qaytaradi 2-chi chiziq va 3m ustun, ya'ni hujayradan C2.

Juda sodda, to'g'rimi? Biroq, amalda siz har doim qaysi qator va ustun kerakligini bilmayapsiz va shuning uchun funktsiya yordamiga muhtojsiz QIDIRMOQ.

MATCH - sintaksis va funktsiyalarni qo'llash

Funktsiya O'YIN Excelda (MATCH) katakchalar oralig'ida ko'rsatilgan qiymatni qidiradi va bu qiymatning diapazondagi nisbiy holatini qaytaradi.

Masalan, agar diapazonda bo'lsa B1: B3 Nyu-York, Parij, London qiymatlarini o'z ichiga oladi, keyin quyidagi formula raqamni qaytaradi 3 chunki "London" ro'yxatning uchinchi elementidir.

O'YIN ("London", B1: B3,0)
\u003d Qidirish ("London"; B1: B3; 0)
Funktsiya O'YIN (MATCH) quyidagi sintaksisga ega:

MATCH (qidirish_qiymati, qidirish_arrasi,)
MATCH (look__value; lookup_array; [match_type])
qidirish_olami (lookup_value) bu siz qidirayotgan raqam yoki matn. Argument qiymat bo'lishi mumkin, shu jumladan mantiqiy yoki katak ma'lumotnomasi.

izlash_array (lookup_array) - qidirish kerak bo'lgan kataklar oralig'i.

match_type (match_type) - bu argument funktsiyani bildiradi QIDIRMOQaniq yoki taxminiy o'yinni topishni xohlaysizmi:
1 yoki ko'rsatilmagan - kerakli qiymatdan kam yoki teng bo'lgan maksimal qiymatni topadi. Skanerlanayotgan massiv o'sish tartibida, ya'ni eng kichigidan kattasiga qadar bo'lishi kerak.

0 - kerakli qiymatga teng birinchi qiymatni topadi. Kombinatsiya uchun INDEKS/QIDIRMOQ har doim aniq moslik kerak, shuning uchun funktsiyalarning uchinchi argumenti QIDIRMOQ teng bo'lishi kerak 0 .

-1 - kerakli qiymatdan katta yoki teng bo'lgan eng kichik qiymatni topadi. Skanerlanayotgan massiv kamayish tartibida, ya'ni eng kichigidan kichikigacha bo'lishi kerak.

Bir qarashda funktsiyaning foydalari QIDIRMOQ shubha ostida. Elementning diapazondagi o'rnini kim bilishi kerak? Biz ushbu elementning ma'nosini bilmoqchimiz!

Sizga eslatib qo'yamanki, kerakli qiymatning nisbiy pozitsiyasi (ya'ni qator va / yoki ustun raqami) aynan biz argumentlar uchun belgilashimiz kerak. qator_num (line_number) va / yoki ustun_num (column_number) funktsiyasi INDEKS (INDEKS). Esingizda bo'lsa, funktsiya INDEKS berilgan qator va ustunning kesishgan joyidagi qiymatni qaytarishi mumkin, ammo bizni qaysi qator va ustun qiziqtirayotganligini aniqlay olmaydi.

Excelda INDEX va SEARCH-dan qanday foydalanish

Endi siz ushbu ikki funktsiya haqida asosiy ma'lumotlarni bilganingizdan so'ng, qanday qilib funktsiyalar bajarilishi allaqachon aniq bo'lib qolganiga ishonaman QIDIRMOQ va INDEKS birgalikda ishlashlari mumkin. QIDIRMOQ berilgan katakchadagi kerakli qiymatning nisbiy holatini belgilaydi va INDEKS ushbu raqamdan (yoki raqamlardan) foydalanadi va natijani mos keladigan katakchadan qaytaradi.

Hali aniq emasmi? Funktsiyalar bilan tanishtirish INDEKS va QIDIRMOQ ushbu shaklda:

INDEX (, (MATCH ( kerakli qiymat,biz ko'rib turgan ustun,0))
\u003d INDEX ( ajratib olinadigan ustun; (QIDIRMOQ ( kerakli qiymat;biz ko'rib turgan ustun;0))
O'ylaymanki, misol bilan tushunish yanada osonroq bo'ladi. Sizda quyidagi davlat poytaxtlari ro'yxati bor deylik:

Quyidagi formuladan foydalanib, masalan, Yaponiya poytaxtlaridan birining aholisini topaylik:

INDEX ($ D $ 2: $ D $ 10, MATCH ("Yaponiya", $ B $ 2: $ B $ 10.0))
\u003d INDEX ($ D $ 2: $ D $ 10; SEARCH ("Yaponiya"; $ B $ 2: $ B $ 10; 0))
Endi ushbu formulaning har bir elementi nima qilishini ko'rib chiqamiz:

Funktsiya O'YIN (MATCH) ustundan "Yaponiya" ni qidiradi B, xususan - hujayralarda B2: B10va raqamni qaytaradi 3 chunki "Yaponiya" ro'yxatda uchinchi o'rinda turadi.

Funktsiya INDEKS (INDEX) foydalanadi 3 argument uchun qator_num (line_number), bu qiymatni qaysi qatordan qaytarish kerakligini belgilaydi. O'sha. oddiy formula olinadi:
INDEX ($ D $ 2: $ D $ 10.3)
\u003d INDEX ($ D $ 2: $ D $ 10,3)
Formulada shunday deyilgan: dan hujayralarni qidirish D2 oldin D10 va qiymatni uchinchi qatordan, ya'ni katakchadan ajratib oling D4chunki hisoblash ikkinchi qatordan boshlanadi.

Excelda natija:

Muhim! Massivdagi funktsiya ishlatadigan qatorlar va ustunlar soni INDEKS (INDEX), argumentlarning qiymatlariga mos kelishi kerak qator_num (line_number) va ustun_num (column_number) funktsiyasi O'YIN (QIDIRMOQ). Aks holda, formulaning natijasi noto'g'ri bo'ladi.
To'xtang, to'xtang ... nega biz shunchaki funktsiyadan foydalana olmaymiz VLOOKUP (VLOOKUP)? Labirintlarni aniqlash uchun vaqtni behuda sarflashning foydasi bormi? QIDIRMOQ va INDEKS?

VLOOKUP ("Yaponiya", $ B $ 2: $ D $ 2.3)
\u003d VLOOKUP ("Yaponiya"; $ B $ 2: $ D $ 2; 3)
Bunday holda, bu mantiqsiz! Ushbu misolning maqsadi faqat namoyish qilish uchun mo'ljallangan, shunda siz qanday ishlashini tushunishingiz mumkin QIDIRMOQ va INDEKS juft bo'lib ishlamoq. Quyidagi misollar sizga to'plamning haqiqiy kuchini ko'rsatadi. INDEKS va QIDIRMOQbu erda ko'plab qiyin vaziyatlarni osonlikcha hal qiladi VLOOKUP qoqilib ketdi.

Nima uchun INDEX / SEARCH VLOOKUP dan yaxshiroq?

Vertikal qidirish uchun qaysi formuladan foydalanishni hal qilishda, Excel guruslarining ko'pchiligi bunga ishonishadi INDEKS/QIDIRMOQ ga qaraganda ancha yaxshi VLOOKUP... Biroq, ko'plab Excel foydalanuvchilari hali ham foydalanmoqdalar VLOOKUPberi bu funktsiya ancha sodda. Buning sababi shundaki, juda kam odam almashtirishning afzalliklarini to'liq tushunadi VLOOKUP to'plamda INDEKS va QIDIRMOQ, va hech kim murakkabroq formulani o'rganish uchun vaqt sarflashni xohlamaydi.

Excelda SEARCH / INDEX-dan foydalanishning 4 asosiy afzalliklari:

1. O'ngdan chapga qidirish. Har qanday savodli Excel foydalanuvchisi biladi, VLOOKUP chap tomonga qarab bo'lmaydi, ya'ni kerakli qiymat tekshirilgan diapazonning eng chap ustunida bo'lishi kerak. Agar bo'lsa QIDIRMOQ/INDEKS, qidiruv ustuni qidiruv doirasining chap tomonida ham, o'ng tomonida ham bo'lishi mumkin. Misol: ushbu xususiyatni amalda ko'rsatib beradi.

2. Ustunlarni xavfsiz tarzda qo'shish yoki olib tashlash. Funktsiyasi bo'lgan formulalar VLOOKUP o'chirsangiz yoki qidiruv jadvaliga ustun qo'shsangiz, ishlashni to'xtating yoki noto'g'ri qiymatlarni qaytaring. Funktsiya uchun VLOOKUP kiritilgan yoki o'chirilgan har qanday ustun formulaning natijasini o'zgartiradi, chunki sintaksis VLOOKUP butun diapazonni va ma'lumotlarni chiqarishni istagan maxsus ustun raqamini ko'rsatishingizni talab qiladi.

Masalan, jadvalingiz bo'lsa A1: C10, va siz ustundan ma'lumotlarni chiqarishni xohlaysiz B, keyin siz qiymatni belgilashingiz kerak 2 argument uchun col_index_num (column_number) funktsiyasi VLOOKUP, mana bunday:

VLOOKUP ("qidiruv qiymati", A1: C10,2)
\u003d VLOOKUP ("qidiruv qiymati"; A1: C10; 2)
Agar keyinchalik ustunlar orasiga yangi ustun qo'shsangiz A va B, keyin argumentning qiymatini o'zgartirish kerak bo'ladi 2 kuni 3 aks holda formula siz kiritgan ustundan natijani qaytaradi.

Foydalanish QIDIRMOQ/INDEKS, Siz natijani buzmasdan ustunlarni olib tashlashingiz yoki o'rganilayotgan qatorga qo'shishingiz mumkin, chunki kerakli qiymatni o'z ichiga olgan ustun to'g'ridan-to'g'ri aniqlanadi. Darhaqiqat, bu juda katta afzallik, ayniqsa, katta hajmdagi ma'lumotlar bilan ishlashga to'g'ri kelganda. Siz foydalanadigan har qanday xususiyatni tuzatish haqida qayg'urmasdan ustunlarni qo'shishingiz va olib tashlashingiz mumkin VLOOKUP.

3. Istalgan qiymatning o'lchamida chegara yo'q. Foydalanish VLOOKUP, 255 belgidan iborat kerakli qiymat uzunligining chegarasi haqida eslang, aks holda siz xatoga yo'l qo'yishingiz mumkin #QIMMAT! (# QIMMAT!). Shunday qilib, agar jadval uzun satrlarni o'z ichiga olgan bo'lsa, faqat bitta to'g'ri echimdan foydalanish kerak INDEKS/QIDIRMOQ.

Siz ushbu formuladan foydalaning VLOOKUPdan hujayralarga qarab B5 oldin D10 katakchada ko'rsatilgan qiymat A2:

VLOOKUP (A2, B5: D10,3, FALSE)
\u003d VLOOKUP (A2, B5: D10, 3, FALSE)
Agar qiymat katakchada bo'lsa, formula ishlamaydi A2 255 belgidan uzunroq. Buning o'rniga siz shunga o'xshash formuladan foydalanishingiz kerak INDEKS/QIDIRMOQ:

INDEX (D5: D10, MATCH (Haqiqat, indeks (B5: B10 \u003d A2,0), 0))
\u003d INDEX (D5: D10; Qidirish (HAQIQ; INDEX (B5: B10 \u003d A2; 0); 0))
4. Ishning yuqori tezligi. Agar siz kichik elektron jadvallar bilan ishlasangiz, Excelning ishlashidagi farq juda katta ehtimollik bilan, ayniqsa so'nggi versiyalarida bo'ladi. Agar siz minglab qatorlar va yuzlab qidiruv formulalarini o'z ichiga olgan katta jadvallar bilan ishlasangiz, Excel foydalanishda juda tez ishlaydi QIDIRMOQ va INDEKS o'rniga VLOOKUP... Umuman olganda, bunday almashtirish Excel tezligini oshiradi 13% .

Ta'sir VLOOKUP Excelning ishlashi, ayniqsa, ish daftarida yuzlab murakkab qator formulalari bo'lsa, ayniqsa seziladi VLOOKUP + SUM... Haqiqat shundaki, massivning har bir qiymatini tekshirish uchun alohida funktsiya chaqiruvi kerak VLOOKUP... Shuning uchun, massiv qancha ko'p qiymatlarni o'z ichiga oladi va jadvalingizdagi qancha massiv formulalari bo'lsa, Excel shunchalik sekin ishlaydi.

Boshqa tomondan, funktsiyalari bo'lgan formula QIDIRMOQ va INDEKS u shunchaki qidiradi va natijani qaytaradi, xuddi shu ishni ancha tez bajaradi.

INDEX va SEARCH - formulalar namunalari

Endi siz funktsiyalarni o'rganishga arziydigan sabablarni tushunasiz QIDIRMOQ va INDEKS, keling, eng qiziqarli narsalarga o'tamiz va nazariy bilimlarni amalda qanday qo'llashingiz mumkinligini ko'rib chiqamiz.

SEARCH va INDEX yordamida chap tomondan qidirish

Har qanday o'quv qo'llanma VLOOKUP bu funktsiya chapga qaray olmasligini ta'kidlaydi. O'sha. agar qidirilayotgan ustun qidiruv doirasidagi chap tomoni bo'lmasa, u holda kirish imkoniyati yo'q VLOOKUP kerakli natija.

Vazifalar QIDIRMOQ va INDEKS Excel juda moslashuvchan va ular olinadigan qiymat bo'lgan ustun qayerda bo'lishiga ahamiyat bermaydilar. Masalan, davlat poytaxtlari va aholisi bilan yana stolga qaytaylik. Bu safar biz formulani yozamiz QIDIRMOQ/INDEKSRossiya poytaxti (Moskva) aholining qaysi o'rnini egallashini ko'rsatib beradi.

Quyidagi rasmda ko'rib turganingizdek, formula buni juda yaxshi bajaradi:

INDEX ($ A $ 2: $ A $ 10, MATCH ("Rossiya", $ B $ 2: $ B $ 10.0))

Endi ushbu formulaning qanday ishlashini tushunishda sizda muammolar bo'lmasligi kerak:

Birinchidan, biz funktsiyadan foydalanamiz O'YIN Ushbu ro'yxatda "Rossiya" pozitsiyasini topadigan (MATCH):
MATCH ("Rossiya", $ B $ 2: $ B $ 10.0))
\u003d Qidirish ("Rossiya"; $ B $ 2: $ B $ 10; 0))

Keyinchalik, funktsiya oralig'ini o'rnatdik INDEKS (INDEX), undan qiymat olinadigan. Bizning holatimizda shunday A2: A10.

Keyin ikkala qismni birlashtiramiz va formulani olamiz:
INDEX ($ A $ 2: $ A $ 10; MATCH ("Rossiya"; $ B $ 2: $ B $ 10; 0))
\u003d INDEX ($ A $ 2: $ A $ 10; SEARCH ("Rossiya"; $ B $ 2: $ B $ 10; 0))

Tezda: To'g'ri echim har doim uchun mutlaq havolalardan foydalanishdir INDEKS va QIDIRMOQformulani boshqa katakchalarga ko'chirishda qidirish diapazonlari yo'qolmasligi uchun.
Excelda INDEX va SEARCH yordamida hisob-kitoblar (AVERAGE, MAX, MIN)

Boshqa Excel funktsiyalarini joylashtirishingiz mumkin INDEKS va QIDIRMOQ, masalan, minimal, maksimal yoki o'rtacha qiymatga yaqinroqni topish. Jadvalga tegishli ba'zi formulalar:

1. MAX (MAX). Formula ustun ichida maksimal miqdorni topadi D. C xuddi shu qator:

INDEX ($ C $ 2: $ C $ 10, MATCH (MAX ($ D $ 2: I $ 10), $ D $ 2: D $ 10.0))
\u003d INDEX ($ C $ 2: $ C $ 10; SEARCH (MAX ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))
Natija: Pekin

2. MIN (MIN). Formula ustunda minimal qiymatni topadi D. va ustundan qiymatni qaytaradi C xuddi shu qator:

INDEX ($ C $ 2: $ C $ 10, MATCH (MIN ($ D $ 2: I $ 10), $ D $ 2: D $ 10.0))
\u003d INDEX ($ C $ 2: $ C $ 10; SEARCH (MIN ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))
Natija: Lima

3. O'RTA (O'RTA). Formulada o'rtacha qiymat oralig'ida hisoblanadi D2: D10, keyin unga eng yaqinini topadi va qiymatni ustundan qaytaradi C xuddi shu qator:

INDEX ($ C $ 2: $ C $ 10, MATCH (O'RTA ($ D $ 2: D $ 10), $ D $ 2: D $ 10,1))
\u003d INDEX ($ C $ 2: $ C $ 10; Qidirish (O'RTA ($ D $ 2: D $ 10); $ D $ 2: D $ 10; 1))
Natija: Moskva

O'RNATISH funktsiyasidan INDEX va SEARCH bilan foydalanishda eslash kerak bo'lgan narsalar

Funktsiyadan foydalanish O'RTA bilan birgalikda INDEKS va QIDIRMOQ, funktsiyaning uchinchi argumenti sifatida QIDIRMOQ ko'pincha siz ko'rsatishingiz kerak bo'ladi 1 yoki -1 agar siz ko'rayotgan oraliqda o'rtacha qiymatga teng qiymat mavjudligiga ishonchingiz komil bo'lmasa. Agar sizda bunday qiymat borligiga amin bo'lsangiz, qo'ying 0 aniq o'yinni topish uchun.

Agar siz aniqlasangiz 1 , qidirish ustunidagi qiymatlar ortib boruvchi tartibda saralanishi kerak va formula maksimal qiymatni o'rtacha qiymatdan kam yoki unga teng ravishda qaytaradi.

Agar siz aniqlasangiz -1 , qidiruv ustunidagi qiymatlar kamayish tartibida bo'lishi kerak va o'rtacha qiymatdan katta yoki unga teng bo'lgan minimal qiymat qaytariladi.

Bizning misolimizda ustundagi qiymatlar D. o'sish tartibida buyurtma qilingan, shuning uchun biz mos keladigan turdan foydalanamiz 1 ... Formula INDEKS/QidiruvZ "Moskva" ni qaytaradi, chunki Moskva shahrining aholisi o'rtacha ko'rsatkichga eng yaqin (12,269,006).

Ma'lum qator va ustunni izlash uchun INDEX va SEARCH-dan qanday foydalanish

Ushbu formula ikki o'lchovli qidirishga teng VLOOKUP va ma'lum bir satr va ustun kesishmasida qiymatni topishga imkon beradi.
Ushbu misolda, formula INDEKS/QIDIRMOQ bitta farq bilan biz ushbu qo'llanmada muhokama qilgan formulalarga juda o'xshash bo'ladi. Qaysi birini taxmin qilasiz?

Yodingizda bo'lsa, funktsiya sintaksisidir INDEKS (INDEX) uchta dalilga ruxsat beradi:

INDEX (qator, satr_num,)
INDEX (qator; satr_num; [ustun_num])
Va buni taxmin qilganlarni tabriklayman!

Formula shablonini yozishdan boshlaymiz. Buning uchun biz allaqachon tanish bo'lgan formulani olamiz INDEKS/QIDIRMOQ va unga yana bitta funktsiyani qo'shing QIDIRMOQustun raqamini qaytaradigan.

INDEX (Sizning jadvalingiz, (MATCH (, qidirish uchun ustun, 0)), (MATCH (, qidiriladigan satr,0))
\u003d INDEX (Sizning jadvalingiz, (MATCH ( vertikal qidirish uchun qiymat,qidirish uchun ustun, 0)), (MATCH ( gorizontal qidirish uchun qiymat,qidiriladigan satr,0))
Shuni esda tutingki, ikki o'lchovli qidirish uchun siz argumentda butun jadvalni ko'rsatishingiz kerak qator (massiv) funktsiyalari INDEKS (INDEKS).
Endi ushbu namunani sinovdan o'tkazamiz. Quyida siz dunyodagi eng ko'p aholiga ega mamlakatlar ro'yxatini ko'rishingiz mumkin. Bizning vazifamiz 2015 yilda AQSh aholisini aniqlash.

Xo'sh, keling formulani yozamiz. Excelda ichki funktsiyalar bilan murakkab formulani yaratish kerak bo'lganda, avval har bir ichki joylashtirilgan faylni alohida-alohida yozib olaman.

Shunday qilib, ikkita funktsiyadan boshlaylik QIDIRMOQbu funktsiya uchun qator va ustun raqamlarini qaytaradi INDEKS:

Ustunni qidiring - biz ustunga qaraymiz B, aniqrog'i oralig'ida B2: B11, katakda ko'rsatilgan qiymat H2 (AQSH). Funktsiya quyidagicha bo'ladi:
MATCH ($ H $ 2, $ B $ 1: $ B $ 11.0)
\u003d Qidirish ($ H $ 2; $ B $ 1: $ B $ 11; 0)
4 chunki "AQSh" ustunning to'rtinchi ro'yxati B (unvon bilan birga).

Mag'lubiyat uchun MATCH - biz hujayra qiymatini qidirmoqdamiz H3 (2015) navbatda 1 , ya'ni hujayralarda A1: E1:
MATCH ($ H $ 3, $ A $ 1: $ E $ 1.0)
\u003d SEARCH ($ H $ 3; $ A $ 1: $ E $ 1; 0)

Ushbu formulaning natijasi bo'ladi 5 chunki "2015" 5-ustunda.

Endi biz ushbu formulalarni funktsiyaga kiritamiz INDEKS va voila:

INDEX ($ A $ 1: $ E $ 11, MATCH ($ H $ 2, $ B $ 1: $ B $ 11.0), MATCH ($ H $ 3, $ A $ 1: $ E $ 1.0))
\u003d INDEX ($ A $ 1: $ E $ 11; SEARCH ($ H $ 2; $ B $ 1: $ B $ 11; 0); SEARCH ($ H $ 3; $ A $ 1: $ E $ 1; 0))
Agar funktsiyalarni almashtirsak QIDIRMOQ ular qaytaradigan qiymatlarda formula oson va sodda bo'ladi:

INDEX ($ A $ 1: $ E $ 11,4,5))
\u003d INDEX ($ A $ 1: $ E $ 11; 4; 5))
Ushbu formula chorrahadagi qiymatni qaytaradi 4-chi torlar va 5-chi oraliqdagi ustun A1: E11, ya'ni hujayra qiymati E4... Faqat? Ha!

INDEX va SEARCH yordamida bir nechta qidiruv

O'quv qo'llanmasida VLOOKUP funktsiyasi bo'lgan formulaning namunasini ko'rsatdik VLOOKUP bir nechta mezonlarni izlash uchun. Biroq, ushbu echimning muhim cheklovi yordamchi ustunni qo'shish zarurati edi. Yaxshi yangiliklar: formula INDEKS/QIDIRMOQ yordamchi ustun yaratishga hojat qoldirmasdan ikki ustundagi qiymatlar bo'yicha qidirish mumkin!

Deylik, bizda buyurtmalar ro'yxati bor va biz uning miqdorini ikkita mezon bo'yicha topmoqchimiz - xaridorning ismi (Buyurtmachi) va mahsulot (Mahsulot). Bir xaridor bir vaqtning o'zida bir nechta turli xil mahsulotlarni sotib olishi mumkinligi va xaridorlarning ismlari varaqdagi jadvalda ekanligi masalani murakkablashtirmoqda. Qidiruv jadvali alohida tartibda joylashtirilgan.

Bu formula INDEKS/QIDIRMOQ muammoni hal qiladi:

(\u003d INDEX ("Izlash jadvali"! $ A $ 2: $ C $ 13, MATCH (1, (A2 \u003d "qidirish jadvali"! $ A $ 2: $ A $ 13) *
(B2 \u003d "Izlash jadvali"! $ B $ 2: $ B $ 13), 0), 3))
(\u003d INDEX ("Izlash jadvali"! $ A $ 2: $ C $ 13; MATCH (1; (A2 \u003d "qidirish jadvali"! $ A $ 2: $ A $ 13) *
(B2 \u003d "Izlash jadvali"! $ B $ 2: $ B $ 13); 0); 3))
Ushbu formula biz ilgari muhokama qilganlardan boshqasiga qaraganda ancha murakkab, ammo funktsiyalarni bilish bilan qurollangan INDEKS va QIDIRMOQ Siz uni mag'lub qilasiz. Eng qiyin qismi bu funktsiya QIDIRMOQMenimcha, avval buni tushuntirish kerak.

MATCH (1, (A2 \u003d "Izlash jadvali"! $ A $ 2: $ A $ 13), 0) * (B2 \u003d "Izlash jadvali"! $ B $ 2: $ B $ 13)
MATCH (1; (A2 \u003d "Izlash jadvali"! $ A $ 2: $ A $ 13); 0) * (B2 \u003d "Izlash jadvali"! $ B $ 2: $ B $ 13)
Yuqorida ko'rsatilgan formulada kerakli qiymat 1 qidiruv qatori esa ko'paytma natijasidir. Yaxshi, nimani ko'paytirishimiz kerak va nima uchun? Keling, uni tartibda ko'rib chiqaylik:

Biz ustunda birinchi qiymatni olamiz A (Xaridor) bitta varaq uchun Asosiy jadval va uni varaqdagi jadvaldagi barcha mijozlarning ismlari bilan taqqoslang Qidiruv jadvali (A2: A13).

Agar moslik topilsa, tenglama qaytadi 1 (HAQIQA), agar bo'lmasa - 0 (YOLG'ON).

Keyinchalik, biz ustun qiymatlari uchun ham xuddi shunday qilamiz B (Mahsulot).

Keyin natijalarni ko'paytiramiz (1 va 0). Ikkala ustunda ham mosliklar topilgan bo'lsa (ya'ni ikkala mezon ham to'g'ri bo'lsa), olasiz 1 ... Agar ikkala mezon ham noto'g'ri bo'lsa yoki ulardan bittasi bajarilsa, siz olasiz 0 .

Endi nima uchun so'raganimizni tushundingiz 1 , kerakli qiymat nima? Funktsiyasi to'g'ri QIDIRMOQ har ikkala mezon bajarilgandagina pozitsiyani qaytargan

Eslatma: Bunday holda, funktsiya uchun uchinchi ixtiyoriy argumentdan foydalanishingiz kerak INDEKS... Bu kerak, chunki birinchi argumentda biz butun jadvalni o'rnatdik va funktsiyani qiymatni qaysi ustundan chiqarishni aytib berishimiz kerak. Bizning holatlarimizda bu ustun C (Sum), va biz kirdik 3 .
Va nihoyat, buyon biz qatordagi har bir katakchani tekshirishimiz kerak, bu formula massiv formulasi bo'lishi kerak. Buni uni o'rab turgan jingalak qavslar orqali ko'rishingiz mumkin. Shunday qilib, formulangizni kiritib bo'lgach, bosishni unutmang Ctrl + Shift + Enter.

Agar hamma narsa to'g'ri bajarilgan bo'lsa, natijani quyidagi rasmdagi kabi olasiz:

Excelda IFERROR bilan birgalikda INDEX va SEARCH

Ehtimol siz allaqachon sezganingizdek (va bir necha bor), agar siz noto'g'ri qiymatni kiritsangiz, masalan, ko'rilgan qatorda bo'lmagan bo'lsa, formulalar INDEKS/QIDIRMOQ xato haqida xabar beradi # Yo'q (# N / A) yoki #QIMMAT! (# QIMMAT!). Agar siz bunday xabarni tushunarliroq narsa bilan almashtirmoqchi bo'lsangiz, formulani qo'shishingiz mumkin INDEKS va QIDIRMOQ funktsiyaga IFERROR.

Funktsiya sintaksisi IFERROR juda oddiy:

IFERROR (qiymat, qiymat_if_error)
IFERROR (qiymat; qiymat_if_error)
Bahs qaerda qiymat (qiymat) - bu xato uchun tekshirilgan qiymat (bizda formulaning natijasi) INDEKS/QIDIRMOQ); va dalil qiymat_if_xato (value_if_error) - agar formula xatoga yo'l qo'ysa, qaytariladigan qiymat.

Masalan, funktsiyaga kiritishingiz mumkin IFERROR shunga o'xshash:

IFERROR (INDEX ($ A $ 1: $ E $ 11, MATCH ($ G $ 2, $ B $ 1: $ B $ 11.0), MATCH ($ G $ 3, $ A $ 1: $ E $ 1.0)),
"Mosliklar topilmadi. Qayta urinib ko'ring!") \u003d IFERROR (INDEX ($ A $ 1: $ E $ 11; SEARCH ($ G $ 2; $ B $ 1: $ B $ 11; 0)); SEARCH ($ G $ 3; $ A $ 1 : $ E $ 1; 0));
"Hech narsa topilmadi. Qayta urinib ko'ring!")
Va endi, agar kimdir noto'g'ri qiymatni kiritsa, formula quyidagi natijani beradi:

Agar siz xato bo'lsa, katakchani bo'sh qoldirishni ma'qul ko'rsangiz, funktsiya ikkinchi argumenti qiymati sifatida tirnoq ("") dan foydalanishingiz mumkin IFERROR... Mana bunday:

IFERROR (INDEX (qator, MATCH (lookup_value, lookup_array, 0), "")
IFERROR (INDEX (qator; SEARCH (lookup_value; lookup_array; 0); "")
Umid qilamanki, ushbu qo'llanmada kamida bitta formulani foydali deb topdingiz. Agar siz ushbu darsdagi ma'lumotlar orasida mos echimni topa olmagan boshqa qidiruv muammolariga duch kelsangiz, sharhlaringizda muammoning tavsifini bering va biz hammamiz uni birgalikda hal qilishga harakat qilamiz.
Shartli formatlash (5)
Ro'yxatlar va qatorlar (5)
Makrolar (VBA protseduralari) (63)
Turli xil (39)
Excel xatolari va nosozliklari (4)
Boshqa jadvaldagi qiymatni qanday topish yoki VLOOKUP kuchi

Aslida, ushbu maqolada men nafaqat imkoniyatlar haqida gapirishni istayman vLOOKUP funktsiyalari, lekin men ham tegmoqchiman QIDIRMOQ, VLOOKUP bilan chambarchas bog'liq bo'lgan funktsiya sifatida. Ushbu xususiyatlarning har biri ijobiy va salbiy tomonlariga ega. Xulosa qilib aytganda, VLOOKUP bitta ustunda joylashgan qiymatlar to'plami orasida biz ko'rsatgan ma'lum bir qiymatni izlaydi. Ehtimol, ko'pincha VLOOKUP-ga ehtiyoj, ma'lumotlarni taqqoslash, boshqa jadvalda ma'lumotlarni topish, ba'zi bir mezonlarga asoslanib ma'lumotlarni bitta jadvaldan boshqasiga qo'shish kerak bo'lganda paydo bo'ladi.
VLOOKUP qanday ishlashini yaxshiroq bilish uchun amaliy misoldan boshlash yaxshiroqdir. Bunday jadval mavjud:
shakl.1

va birinchi jadvaldan har bir familiya uchun ikkinchi sanada almashtirish kerak. Uchta yozuv uchun bu muammo emas va uni qo'l bilan bajarish - barchasi aniq. Haqiqiy hayotda bular minglab yozuvlarga ega jadvallar va almashtirishni qo'lda qidirish bir soatdan ko'proq vaqt talab qilishi mumkin. Bundan tashqari, yana bir nechta malhamda uchish: ikkala jadvalda to'liq ismlar nafaqat boshqacha tartibda joylashtirilgan, balki jadvaldagi yozuvlar soni ham har xil, shuning uchun jadvallar ham turli varaqlarda / kitoblarda joylashgan. Ishonamanki, qo'lni almashtirish umuman mumkin emasligiga ishontirganman. Ammo VLOOKUP (VLOOKUP) bu erda almashtirib bo'lmaydigan bo'ladi. Bunday holda, deyarli hech narsa qilish kerak bo'lmaydi - faqat ikkinchi jadvalning C ustunining birinchi katagiga yozing (bu erda birinchi jadvaldagi sanalarni almashtirishingiz kerak) ushbu formula:
\u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)
Siz formulani to'g'ridan-to'g'ri katakchaga yozishingiz yoki funktsiyalar menejeri yordamida toifani tanlashingiz mumkin Adabiyotlar va massivlar VLOOKUP va kerakli mezonlarni alohida ko'rsatib bering. Endi nusxalash ( Ctrl+C) formulali katakka, ma'lumotlarning oxirigacha S ustunidagi barcha katakchalarni tanlang va ( Ctrl+V).

Birinchidan, ishlashning asosiy printsipi: VLOOKUP jadval argumentining birinchi ustunida argument ko'rsatilgan qiymatni qidiradi. Qidiruv_olami ... Kerakli qiymat topilganda, funktsiya topilgan qiymatga qarama-qarshi qiymatni qaytaradi, ammo argument tomonidan ko'rsatilgan ustundan Ustun raqami ... Birozdan keyin intervalli ko'rish bilan shug'ullanamiz. VLOOKUP faqat bitta qiymatni qaytarishi mumkin - bu mezonga mos keladigan birinchi qiymat. Agar kerakli qiymat topilmasa (jadvalda emas), keyin funktsiya natijasi bo'ladi # Yo'q ... Bundan qo'rqmang - bu hatto foydalidir. Qaysi yozuvlar etishmayotganligini aniq bilib olasiz va shu bilan siz ikkita jadvalni bir-biri bilan taqqoslashingiz mumkin. Ba'zan siz ko'rasiz: ma'lumotlar ikkala jadvalda ham mavjud, ammo VLOOKUP # N / A ni beradi. Shunday qilib jadvallaringizdagi ma'lumotlar bir xil emas. Ulardan ba'zilari ortiqcha sezilmaydigan bo'shliqlarga ega (odatda qiymatdan oldin yoki keyin) yoki kirill alifbosi lotin belgilariga aralashgan. Bir xil # Yo'q mezonlari raqamlar bo'lsa va kerakli jadvalda ular matn sifatida yozilgan bo'lsa bo'ladi (qoida tariqasida bunday katakning yuqori chap burchagida yashil uchburchak paydo bo'ladi)va finalda - raqamlar sifatida. Yoki aksincha.
VLOOKUP argumentlarining tavsifi
$ A2 - argument Qidiruv_olami (uni chaqiramiz Mezon qisqa). Bu biz izlayotgan narsadir. O'sha. ikkinchi jadvalning birinchi yozuvi uchun S.A.Petrov bo'ladi. Bu erda siz mezon matnini to'g'ridan-to'g'ri belgilashingiz mumkin (bu holda u tirnoq belgilarida bo'lishi kerak - \u003d VLOOKUP ("Petrov S.A"; Sheet1! $ A $ 2: $ C $ 4; 3; 0)) yoki ushbu matn bilan katakka havola (misol vazifasida bo'lgani kabi)... Kichkina nuance bor: shuningdek, belgilar belgilaridan foydalanishingiz mumkin: "*" va "?". Agar siz faqat satrning bir qismidan qiymatlarni topishingiz kerak bo'lsa, bu juda qulay. Masalan, siz "Petrov S.A" ni to'liq kiritolmaysiz, faqat familiya va yulduzcha - "Petrov *" ni kiritishingiz mumkin. Keyin "Petrov" bilan boshlangan har qanday yozuv ko'rsatiladi. Agar siz satrning istalgan joyida "Petrov" familiyasi paydo bo'lgan yozuvni topishingiz kerak bo'lsa, uni quyidagicha belgilashingiz mumkin: "* Petrov *". Agar siz Petrov familiyasini topishni istasangiz va uning ismi va otasining ismi qanday bosh harflar bilan yozilgan bo'lishidan qat'iy nazar (agar to'liq ism Ivanov II shaklida yozilgan bo'lsa), mana shu narsa shunchaki: "Ivanov?.?." ... Ko'pincha har bir satr o'z qiymatini ko'rsatishi kerak (A familiyasi ustunida va barchasini topishingiz kerak). Bunday holda, har doim A ustunidagi katakchalarga havolalar ko'rsatiladi, masalan, A1 katakchada shunday yozilgan: Ivanov. Bundan tashqari, Ivanov boshqa jadvalda ekanligi ma'lum, ammo familiyadan keyin ham ism, ham otasining ismi (yoki boshqa biron bir narsa) yozilishi mumkin. Ammo biz faqat familiyadan boshlanadigan qatorni topishimiz kerak. Keyin quyidagicha yozish kerak: A1 & "*". Ushbu yozuv "Ivanov *" ga teng bo'ladi. A1 Ivanovning ta'kidlashicha, ampersand (&) ikkita matn qiymatini bitta satrga birlashtirish uchun ishlatiladi. Qo'shtirnoqdagi yulduzcha (formulada matn bo'lishi kerak). Shunday qilib, biz quyidagilarni olamiz:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"Ivanov *"
Agar izlash uchun juda ko'p qiymatlar mavjud bo'lsa, bu juda qulay.
Agar mag'lubiyatda hech bo'lmaganda biror joyda so'z bor-yo'qligini aniqlash kerak bo'lsa, ikkala tomonga ham yulduzcha qo'ying: "*" & A1 & "*"
Sheet1! $ A $ 2: $ C $ 4 - argument Jadval... Yacheykalar qatorini belgilaydi. Faqatgina diapazonda ma'lumotlar katakchasidan to oxirigacha ma'lumotlar bo'lishi kerak. Bu misolda ko'rsatilgan oraliq bo'lishi shart emas. Agar 100 qator bo'lsa, u holda Sheet1! $ A $ 2: $ C $ 100. Uch narsani eslab qolish muhimdir: birinchidan, bu Jadval har doim siz izlayotgan ustun bilan boshlanishi kerak Mezon ... Va boshqa hech narsa. Aks holda, hech narsa topilmaydi yoki natija siz kutganingizdan butunlay farq qiladi. Ikkinchisi: tortishuv Jadval "sobit" bo'lishi kerak ... Bu nimani anglatadi. Dollar belgilarini ko'rasiz - $? Bu langar (aniqrog'i, bu mutlaq intervalli ma'lumotnomasi deb ataladi)... Qanday qilib amalga oshirildi. Havola matnini ajratib ko'rsatish (faqat bitta diapazon - bitta mezon) va tugmasini bosing F4 ustunlar nomi va satr raqamlari oldida dollarlar paydo bo'lguncha. Agar bu bajarilmasa, formulani nusxalashda Jadval argumenti "chiqib ketadi" va natija yana noto'g'ri bo'ladi. Va oxirgi - jadvalda ustunlarni o'z ichiga olishi kerak (biz ko'rib turganimizdan) oxirigacha (biz qiymatlarni qaytarishimiz kerak). Misolda Sheet1! $ A $ 2: $ C $ 4 - shuning uchun D (4) ustunidan qiymatni qaytarib bo'lmaydi, chunki jadvalda faqat uchta ustun mavjud.

3 - Ustun raqami ... Bu erda biz faqat argumentda ustun raqamini belgilaymiz Jadval, natijada biz almashtirishimiz kerak bo'lgan qiymatlar. Misolda, bu qabul qilish sanasi - ya'ni. ustun 3. Agar bo'lim kerak bo'lsa, biz 2 ni ko'rsatamiz va agar biz bitta jadvalning familiyasi boshqasida bor-yo'qligini taqqoslashimiz kerak bo'lsa, unda biz 1-raqamni ham ko'rsatishimiz mumkin. Muhim: argument Ustun raqami argumentdagi ustunlar sonidan oshmasligi kerak Jadval ... Aks holda, formula xatoga olib keladi. #LINK!... Masalan, $ B $ 2: $ C $ 4 oralig'i ko'rsatilgan bo'lsa va siz C ustunidan ma'lumotlarni qaytarishingiz kerak bo'lsa, unda 2 to'g'ri bo'ladi. dalil Jadval($ B $ 2: $ C $ 4) faqat ikkita ustunni o'z ichiga oladi - B va S. Agar biz 3-ustun sonini ko'rsatishga harakat qilsak (varaqdagi hisob nima), biz xatoga yo'l qo'yamiz #LINK!beri ko'rsatilgan oraliqda oddiygina uchinchi ustun yo'q.

Amaliy maslahat: agar Jadval argumenti juda ko'p ustunlarga ega bo'lsa va natijani oxirgi ustundan qaytarishingiz kerak bo'lsa, unda ularning sonini hisoblash umuman zarur emas. Siz buni quyidagicha belgilashingiz mumkin: \u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN (Sheet1! $ A $ 2: $ C $ 4); 0). Aytgancha, bu holda, Sheet1! ortiqcha sifatida olib tashlanishi mumkin: \u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN ($ A $ 2: $ C $ 4); 0).

0 - Intervalli ko'rish juda qiziqarli dalil. HAQIQ yoki FALSE bo'lishi mumkin. Darhol savol tug'iladi: nega mening formulamda 0 mavjud? Bu juda oddiy - formulalardagi Excel 0ni FALSE, 1 ni HAQIQ sifatida qabul qilishi mumkin. Agar siz VLOOKUP-da 0 yoki FALSE qiymatiga teng ushbu parametrni ko'rsatsangiz, u holda ko'rsatilgan Mezonga to'liq moslik izlanadi. Bu belgilar bilan hech qanday aloqasi yo'q ("*" va "?"). Agar siz 1 yoki TRUE dan foydalansangiz (yoki oxirgi argumentni umuman ko'rsatmasangiz, chunki u sukut bo'yicha To'g'ri), unda ... Bu juda uzoq hikoya. Qisqasi - VLOOKUP mos keladigan eng o'xshash qiymatni qidiradi Mezon ... Ba'zan juda foydali. Ammo, agar siz ushbu parametrdan foydalansangiz, unda jadval argumentidagi ro'yxat o'sish tartibida tartiblangan bo'lishi kerak. Men sizning e'tiboringizni Interval_View argumenti HAQI yoki 1 bo'lgan taqdirda saralash zarurligiga qarataman, agar 0 yoki FALSE bo'lsa, saralash kerak emas.

Ko'pchilik, ehtimol rasmda to'liq ism uchun bo'limlarni buzganimni payqagan bo'lishi mumkin. Bu yozuvdagi xato emas. Maqolaga ilova qilingan misol dalilni qo'lda o'zgartirmasdan qanday qilib ularni va sanalarni bitta formula bilan almashtirishingiz mumkinligini ko'rsatadi Ustun raqami... Menimcha, bunday misol foydali bo'lishi mumkin.

VLOOKUP-da # N / A (# N / A) xatosidan qanday qochish kerak?
Yana bir keng tarqalgan muammo shundaki, agar ko'pchilik mos kelmasa, ko'p odamlar # N / A ni ko'rishni xohlamaydilar. Bu erda aylanib o'tish oson:
\u003d IF (UND (VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0))); ""; VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)))
Endi VLOOKUP mos kelmasa, katak bo'sh bo'ladi.
Excel 2007 va undan yuqori versiyalarining foydalanuvchilari IFERROR dan foydalanishlari mumkin:
\u003d IFERROR (VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3,0); "")

Va'da qilingan izlash

Ushbu funktsiya parametr bilan belgilangan qiymatni qidiradi Qidiruv_olami tortishuvda View_array ... Va funktsiya natijasi - topilgan qiymatning pozitsiya raqami Ko'rinadigan_array... Bu qiymatning o'zi emas, balki pozitsiya raqami. Printsipial jihatdan, men uni bir xil tafsilotlarda tasvirlamayman, chunki asosiy fikrlar aynan bir xil. Agar biz uni yuqoridagi jadvalga tatbiq qilmoqchi bo'lsak, shunday bo'lar edi:
\u003d SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0)
$ A2 - Lookup_value. Bu erda hamma narsa VLOOKUP bilan bir xil. Joker belgilarga ham ruxsat berilgan va aynan bir xil dizayndagi.

Sheet1! $ A $ 2: $ A $ 4 - qarash uchun massiv. VLOOKUP-dan asosiy farq shundaki, siz faqat bitta ustunli qatorni belgilashingiz mumkin. Bu biz qidiradigan ustun bo'lishi kerak Qidiruv_olami ... Agar siz bir nechta ustunni ko'rsatmoqchi bo'lsangiz, funktsiya xato qaytaradi.

Match_type (0) - VLOOKUP bilan bir xil Intervalli ko'rish ... Xuddi shu xususiyatlar bilan. U faqat kerakli yoki eng kattadan eng kichigini qidirish qobiliyatidan farq qiladi. Ammo men ushbu maqolada bu haqda to'xtamayman.

Asosiy tartib bilan. Lekin biz pozitsiya raqamini emas, balki qiymatning o'zini qaytarishimiz kerak. Bu shuni anglatadiki, sof shaklda SEARCH bizga mos kelmaydi. Hech bo'lmasa bitta o'zi. Agar siz uni INDEX funktsiyasi bilan birgalikda ishlatsangiz, bu bizga kerak va yana ko'p narsalar.
\u003d INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2)
Ushbu formula VLOOKUP bilan bir xil natijani beradi.

INDEX funktsiyasining argumentlari
Sheet1! $ A $ 2: $ C $ 4 - qator. Ushbu dalil sifatida biz qiymatlarni olishni istagan oralig'ini aniqlaymiz. Bitta ustun yoki bir nechta bo'lishi mumkin. Agar bitta ustun bo'lsa, unda funktsiyaning oxirgi argumenti ko'rsatilmasligi kerak. Aytgancha - bu argument MATCH funktsiyasining View_array argumentida ko'rsatganimiz bilan umuman mos kelmasligi mumkin.

Keyingi qator: RowNumber va ColumnNumber. Biz qatorda pozitsiya raqamini qaytaradigan SEARCH o'rnini Line_Number-ga almashtiramiz. Hammasi shunga qurilgan. INDEX qatorning qiymatini qatorning ko'rsatilgan satrida (Row_Number) va ko'rsatilgan ustunda (Column_number) qaytaradi, agar bir nechta ustun bo'lsa. Ushbu to'plamda INDEX funktsiyasining Array argumentidagi satrlar soni va MATCH funktsiyasining Look-up_array argumentidagi satrlar soni mos kelishi kerakligini bilish muhimdir. Va xuddi shu qatordan boshlang. Agar siz boshqa maqsadlarga intilmasangiz, bu odatiy holatlarda bo'ladi.
VLOOKUP-da bo'lgani kabi, kerakli qiymat topilmasa, INDEX # N / A qiymatini qaytaradi. Va siz ham bunday xatolar ustida ishlashingiz mumkin:
Excelning barcha versiyalari uchun (shu jumladan 2003 va undan oldingi versiyalar):
\u003d IF (UND (SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0)); ""; INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2))
2007 va undan yuqori versiyalar uchun:
\u003d IFERROR (INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2); "")

255 belgidan uzunroq mezon bilan ishlash
INDEX-POISKPOS-ning VLOOKUP-ga nisbatan yana bir afzalligi bor. Gap shundaki, VLOOKUP qiymatlarni izlay olmaydi chiziq uzunligi 255 dan ortiq belgidan iborat... Bu kamdan-kam hollarda bo'ladi, lekin shunday bo'ladi. Siz, albatta, VLOOKUP-ni aldashingiz va mezonni kesishingiz mumkin:
\u003d VLOOKUP (MID ($ A2; 1; 255); MID (Sheet1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
ammo bu massiv formulasi. Bundan tashqari, bunday formula har doim ham kerakli natijani bermaydi. Agar dastlabki 255 ta belgi jadvaldagi dastlabki 255 ta belgiga o'xshash bo'lsa va u holda belgilar boshqacha bo'lsa, formulalar endi buni ko'rmaydi. Va formula faqat matn qiymatlarini qaytaradi, bu raqamlarni qaytarish kerak bo'lgan hollarda juda qulay emas.

Shuning uchun hiyla-nayrangli formuladan foydalanish yaxshiroqdir:
\u003d INDEX (Sheet1! $ A $ 2: $ C $ 4; SUMPRODUCT (SEARCH (TRUE; Sheet1! $ A $ 2: $ A $ 4 \u003d $ A2; 0)); 2)
Bu erda men o'qish uchun formulalarda bir xil diapazonlardan foydalanganman, ammo yuklab olish misolida ular bu erda ko'rsatilganidan farq qiladi.
Formulaning o'zi SUMPRODUCT funktsiyasining tarkibidagi ba'zi funktsiyalarni katta hisob-kitoblarga aylantirish qobiliyatiga asoslangan. Bunday holda, MATCH kriteriya satrdagi qiymatga teng bo'lgan satrning holatini izlaydi. Siz bu erda joker belgilarni ishlata olmaysiz.

Maqolaga ilova qilingan misolda siz barcha tavsiflangan holatlardan foydalanishga oid misollarni va INDEX va MATCH ba'zida VLOOKUP-dan afzalroq bo'lganligini topasiz.

Masalan yuklab oling
(26,0 KiB, 14 615 ta yuklab olish)
Maqola yordam berdimi? Do'stlaringizga havolani ulashing! Video darslari
("Pastki satr" :( "textstyle": "statik", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: mutlaq; top: 0px; left: 0px; width: 100%; height: 100% ; fon rangi: # 333333; shaffoflik: 0,6; filtr: a lpha (xiralik \u003d 60); "," titlecss ":" displey: blok; pozitsiya: nisbiy; shrift: qalin 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; 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! muhim; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))