VLOOKUP Excel bilan bir nechta ma'lumot maydonlarini toping

Excelning VLOOKUP funktsiyasini COLUMN funktsiyasi bilan birlashtirib, ma'lumotlar bazasi yoki ma'lumotlar jadvalining bir qatoridan bir nechta qiymatlarni qaytarish imkonini beradigan qo'ng'iroq formulasini yaratishimiz mumkin.

Yuqoridagi rasmda ko'rsatilgan misolda, qidirish formulasi narxlari, parcha raqami va etkazib beruvchilardan - turli apparat qurilmalari bilan bog'liq bo'lgan barcha qiymatlarni qaytarishni osonlashtiradi.

01dan 10gacha

VLOOKUP Excel bilan bir nechta qiymatlarni qaytaring

Quyida keltirilgan bosqichlarni bajarish, yuqoridagi rasmda ko'riladigan qidirish formulasini yaratadi va u bir ma'lumotdan bir nechta qiymatlarni qaytaradi.

Qidiruv formulasi COLUMN funktsiyasini VLOOKUP ichida joylashgan bo'lishi kerakligini talab qiladi.

Funktsiyani joylashtirish birinchi funktsiyaning argumentlaridan biri sifatida ikkinchi funktsiyani kiritishni o'z ichiga oladi.

Ushbu qo'llanmada COLUMN funktsiyasi VLOOKUP uchun ustun indekslari argumenti sifatida kiritiladi.

O'quv qo'llanmasidagi oxirgi qadam tanlangan qism uchun qo'shimcha qiymatlarni olish uchun qidirish formulasini qo'shimcha ustunlarga ko'chirishni o'z ichiga oladi.

O'quv qo'llanmasi

• Ta'lim ma'lumotlarini kiritish
• Ma'lumotlar jadvali uchun nomlangan maydonni yaratish
• VLOOKUP funktsiyasini ishga tushirish
• Mutlaq hujayra zahiralari yordamida Izlash qiymatlari parametrlarini kiritish
• Jadval qatori o'zgarishlarini kiritish
• Ichki joylashish funktsiyasini kiritish
• VLOOKUP funktsiyasini bajarish
• Qidiruv formulasini to'ldirish qo'lini nusxalash
• Ma'lumotlarni qidirish formulasi bilan olish

02 ning 10

Ta'lim ma'lumotlarini kiriting

O'quv qo'llanmasidagi birinchi qadam ma'lumotlarni Excel sahifasiga kiritishdir .

Öğreticideki amallarni bajarish uchun yuqoridagi rasmda ko'rsatilgan ma'lumotlarni quyidagi hujayralarga kiriting .

• D1 dan G1 hujayralariga ma'lumotlarni yuqori oralig'ini kiriting
• D4 dan G10 hujayralariga ikkinchi qatorni kiriting

Ushbu o'rganish davomida yaratilgan qidirish mezonlari va qidirish formulasi ish sahifasining 2- satriga kiritiladi.

O'qituvchi rasmda ko'rilgan formatlashni o'z ichiga olmaydi, lekin bu qidirish moslamasi qanday ishlashiga ta'sir qilmaydi.

Yuqoridagi holatlarga o'xshash formatlash variantlari haqidagi ma'lumotni ushbu asosiy Excel formatlash bo'yicha qo'llanmada topish mumkin .

O'quv qadamlar

1. Yuqoridagi rasmda ko'rinib turganidek, ma'lumotlarni D1 dan G10 hujayralariga kiriting

03 dan 10gacha

Ma'lumotlar jadvali uchun nomlangan maydonni yaratish

Ismlar oralig'i formulada ma'lumotlarning bir qatoriga murojaat qilishning oson yo'li. Ma'lumotlar uchun hujayra zikrlarini yozishdan ko'ra, siz faqat intervalli nomini kiritishingiz mumkin.

Nom qatorini ishlatish uchun ikkinchi afzallik, formulalar ishchi varagichdagi boshqa hujayralarga nusxa ko'chirilgan bo'lsa ham, bu oraliq uchun havolalarning o'zgarmasligi hisoblanadi.

Raqamli ismlar, shuning uchun formulalarni nusxalashda xatolarni bartaraf etish uchun mutlaq hujayra zikrlarini ishlatishning muqobilidir.

Eslatma: Tarmoq nomida ma'lumotlar uchun sarlavhalar yoki maydon nomlari mavjud emas (4-satr), faqat ma'lumotlar o'zi.

O'quv qadamlar

1. Belgilab qo'yilsinki, D5-dan G10 hujayralariga ishchi varag'i uchun ularni tanlash
2. A ustuni yuqorida joylashgan Od qutisini bosing
3. Od qutisiga "Jadval" (tirnoq belgilanmagan) kiriting
4. Klaviaturadan ENTER tugmasini bosing
5. D5 dan G10 gacha bo'lgan hujayralar endi "Jadval" qatoriga ega. Keyinchalik VLOOKUP jadval qatori argumenti uchun o'quv qo'llanmasida foydalanamiz

04/10

VLOOKUP dialog oynasini ochish

Bizning qidirish formulasini to'g'ridan-to'g'ri ishchi varaqadagi hujayra ichiga kiritish mumkin bo'lsa-da, ko'pchilik sintaksisni to'g'ri saqlashni qiyinlashtiradi - ayniqsa, ushbu qo'llanmada biz foydalanadigan murakkab formula uchun.

Shu bilan muqobil, bu holda VLOOKUP dialog oynasini ishlatishdir. Deyarli barcha Excel funktsiyalarining funktsiyalarining har bir parametrini alohida satrda kiritishingizga imkon beruvchi dialog oynasi mavjud.

O'quv qadamlar

1. Ishchi varaqdagi E2 uyasiga bosing - ikki o'lchovli qidirish formulasining natijalari ko'rsatiladigan joy
2. Ipning Formulalar yorlig'ini bosing
3. Funktsiya ochiladigan ro'yxatni ochish uchun chiziqdagi Izlash va Yo'nalish opsiyasini bosing
4. Funktsiya muloqot qutisini ochish uchun ro'yxatda VLOOKUP tugmasini bosing

10dan 10gacha

Mutlaq hujayra zahiralari yordamida Izlash qiymatlari parametrlarini kiritish

Odatda, qidirish qiymati ma'lumotlar jadvali birinchi ustunidagi ma'lumotlar maydoniga mos keladi.

Bizning misolimizda, qidirish qiymati ma'lumot izlashni istagan apparat qismining nomini anglatadi.

Qidiruv qiymati uchun ruxsat etilgan turdagi ma'lumotlar quyidagilar:

• matnli ma'lumotlar
• mantiqiy qiymat (faqat ROST yoki FALSE)
• raqam
• ish varag'idagi qiymatga hujayra mos yozuvlar

Ushbu misolda, biz uning qismini topish uchun hujayra moslamasini kiritamiz - D2 uyasi.

Mutlaq hujayra zikrlari

O'quv qo'llanmasining keyingi bosqichida E2 uyali aloqa tizimida qidirish moslamasini F2 va G2 xujayralariga ko'chiramiz.

Odatda, formulalar Excelga ko'chirilganda, hujayra zahiralari yangi manzilini aks ettirish uchun o'zgaradi.

Agar bu ro'y beradigan bo'lsa, D2 - qidirish qiymati uchun hujayra mos yozuvi - formula F2 va G2 hujayralarida xatoliklar yaratilganda ko'chiriladi.

Xatlarni oldini olish uchun biz D2 mos yozuvlar raqamini mutlaq hujayra namunasiga aylantiramiz .

Mutlaq hujayra arizalari formulalar ko'chirilganda o'zgarmaydi.

Mutlaq hujayra arizalari klaviaturada F4 tugmachasini bosish orqali yaratiladi. Buning uchun $ D $ 2 kabi hujayra mos yozuvlar atrofida dollar belgilari qo'shiladi

O'quv qadamlar

1. Aloqa qutisidagi search_value qatoriga bosing
2. Ushbu hujayra mos yozuvlarini lookup_value qatoriga qo'shish uchun D2 uyasiga bosing. Bu biz ma'lumot olishni istagan qism nomini yozadigan hujayra
3. Qo'shish nuqtasini ko'chirmasdan, D2 ni mutlaq hujayra mos yozuvi $ D $ 2 ga aylantirish uchun klaviaturada F4 tugmasini bosing
4. VLOOKUP funktsiyasi dialog oynasini darsning keyingi bosqichi uchun ochiq qoldiring

06 dan 10gacha

Jadval qatori o'zgarishlarini kiritish

Jadval qatori, qidirish formula bizga kerakli ma'lumotlarni topish uchun izlagan ma'lumotlar jadvali .

Jadval qatorida kamida ikkita ma'lumot ustuni bo'lishi kerak.

• birinchi ustunda qidirish qiymati argumenti (o'quvchining oldingi bosqichi)
• ikkinchisiga va har qanday qo'shimcha ustunlarga, biz ko'rsatgan ma'lumotni topish uchun qidirish moslamasi tomonidan tekshiriladi.

Jadval qatori mustaqil o'zgaruvchisi ma'lumotlar jadvali uchun yoki hujayra nomlari uchun hujayra zikrlarini o'z ichiga olgan oraliq sifatida kiritilishi kerak.

Ushbu misol uchun o'qitishning 3-bosqichida yaratilgan intervalli nomdan foydalanamiz.

O'quv qadamlar

1. Aloqa qutisidagi table_array qatoriga cherting
2. Ushbu mustaqil o'zgarish uchun intervalli nomini kiritish uchun "jadval" (tirnoqlardan tashqari) ni kiriting
3. VLOOKUP funktsiyasi dialog oynasini darsning keyingi bosqichi uchun ochiq qoldiring

07 dan 10gacha

COLUMN funktsiyasini joylashtirish

Odatda VLOOKUP ma'lumotlar jadvalining bitta ustunidan faqat ma'lumotni qaytaradi va bu ustun sütun indeks raqami argumenti bilan o'rnatiladi.

Biroq, bu misolda ma'lumotlarimizni qaytarib berishni istagan uchta ustun bor, shuning uchun biz qidirish formulasini tahrir qilmasdan kolonnaning indeks raqamini osongina o'zgartirishga yo'l kerak.

Bu erda COLUMN funktsiyasi kiradi. Ustun indekslari argumenti sifatida kirib, uni qidirish formula D2 xujayrasidan E2 va F2 hujayralariga keyinchalik o'rganib chiqqan ko'chirma sifatida ko'chiriladi.

Nesting funktsiyalari

COLUMN funktsiyasi, VLOOKUPning ustun indekslari argumenti sifatida ishlaydi .

Bu VLOOKUP ichidagi COLUMN funktsiyasini dialog oynasining Col_index_num satrida joylashtirish orqali amalga oshiriladi.

COLUMN Funktsiyasini qo'lda kiritish

Funktsiyalarni joylashtirishda Excel bizga argumentlarni kiritish uchun ikkinchi funktsiyaning dialog oynasini ochishga ruxsat bermaydi.

Shuning uchun COLUMN funktsiyasi Col_index_num satriga qo'lda kiritilishi kerak.

COLUMN funktsiyasi faqat bitta dalilga ega - bu hujayra mos yozuvi bo'lgan Reference argumenti.

COLUMN funktsiyasining argumentini tanlash

COLUMN funktsiyasi vazifasi Reference argumenti sifatida berilgan ustun sonini qaytarishdir.

Boshqacha qilib aytganda, ustunning harfini A ustunida birinchi ustun, ikkinchi B ustuni va boshqalar bilan o'zgartiradi.

Biz qaytarilgan ma'lumotlarning birinchi sohasi ma'lumot jadvalining ustunidagi ikkita element bo'lganligi sababli - biz B ustidagi har qanday xonada hujayra uchun mos yozuvlar parametrini 2-raqamni olish uchun tanlab olamiz. Col_index_num argumenti.

O'quv qadamlar

1. VLOOKUP funktsiyasi oynasida Col_index_num satriga bosing
2. Funktsional nomlar ustunini va keyinchalik ochiq-oydin yumaloq bracket " ( "
3. Hujjat argumenti sifatida u uyali ma'lumotnomani kiritish uchun ish sahifasida B1 uyasiga bosing
4. COLUMN funktsiyasini bajarish uchun yopiladigan yumaloq qavs kiriting "
5. VLOOKUP funktsiyasi dialog oynasini darsning keyingi bosqichi uchun ochiq qoldiring

08 dan 10gacha

VLOOKUP oralig'i qidirish parametrini kiritish

VLOOKUPning Range_lookup argumenti VLOOKUP ning Lookup_valuega to'liq yoki taxminiy mosligini topishni xohlaysizmi yoki yo'qligini ko'rsatadigan mantiqiy qiymat (faqat ROST yoki FALSE).

• ROST yoki ushbu mustaqil o'zgaruvchi ifodalangan bo'lsa, VLOOKUP yoki to'liq qidirish bilan Lookup_value qiymatini qaytaradi, yoki to'liq mos kelmasa, VLOOKUP keyingi eng katta qiymati qaytaradi. Buni amalga oshirish uchun formuladan foydalanib, Table_array ning birinchi ustunidagi ma'lumotlar ortib boruvchi tartibda tartibga solinishi kerak.
• FALSE bo'lsa, VLOOKUP faqat Lookup_valuega to'liq mos keladigan usulni ishlatadi. Agar qidirish qiymatiga mos keladigan Table_array ning birinchi ustunida ikki yoki undan ortiq qiymat bo'lsa, topilgan birinchi qiymat ishlatiladi. To'liq mos kelmasa, # N / A xatosi qaytariladi.

Ushbu qo'llanmada aniq bir apparat elementi haqida aniq ma'lumot izlayotganimiz sababli, biz Range_lookup-ni Faliqga tenglashtiramiz.

O'quv qadamlar

1. Muloqot oynasidagi Range_lookup qatorini bosing
2. VLOOKUP'ning biz qidirayotgan ma'lumotlarga to'liq mos kelishini xohlaganligini ko'rsatish uchun ushbu satrda noto'g'ri so'zni kiriting
3. Qidiruv formulasini bajarish va dialog oynasini yopish uchun OK ni bosing
4. Hali hujayra D2 ga qarab qidirish mezonlariga kirgani yo'q, chunki E2 hujayrasida # N / A xato bo'ladi
5. O'quv qo'llanasining so'nggi bosqichida qidirish mezonlarini qo'shsak, ushbu xato tuzatiladi

09 dan 10 ga

Qidiruv formulasini to'ldirish qo'lini nusxalash

Qidiruv formulasi ma'lumotlar jadvalining bir nechta ustunidan bir vaqtning o'zida ma'lumotlarni olish uchun mo'ljallangan.

Buni amalga oshirish uchun, qidirish formulasi ma'lumot istagan barcha sohalarda bo'lishi kerak.

Ushbu qo'llanmada ma'lumot jadvalining 2, 3 va 4 ustunlaridan ma'lumotni olishni istaymiz, ya'ni Lookup_value nomi sifatida nomdagi qismni kiritganimizda narx, parcha raqami va etkazib beruvchining nomi.

Ma'lumotlarni ishchi varaqdagi muntazam naqsh bilan joylashtirilganligi sababli biz E2 hujayrasida qidirish moslamasini F2 va G2 xujayralariga nusxalashimiz mumkin.

Formuladan nusxa ko'chirilganligi sababli Excel formulaning yangi manzilini aks ettirish uchun COLUMN funktsiyasidagi (B1) nisbatan hujayra mos yozuvlarini yangilaydi.

Bundan tashqari, Excel $ D $ 2 mutlaq hujayra arizasini o'zgartirmaydi va formulalar ko'chirildi deb nomlangan intervalli jadvalidan .

Excelga ma'lumotlar nusxasini olishning bir nechta usuli bor, lekin, ehtimol, eng oson yo'li to'ldirish qo'lini ishlatishdir .

O'quv qadamlar

1. Faol hujayraning bajarilishi uchun qidirish moslamasi mavjud bo'lgan E2 uyasiga bosing
2. Sichqoncha ko'rsatkichini pastki o'ng burchakdagi qora kvadrat ustiga qo'ying. Pointer ortiqcha belgiga " + " o'zgaradi - bu plomba tutqichi
3. Sichqonchaning chap qismidagi tugmani bosing va to'ldirish tutamacını G2 uyasiga qarab harakatlantiring
4. Sichqoncha tugmasini bosib, F3 uyasi ikki o'lchovli qidirish formulasini o'z ichiga olishi kerak
5. To'g'ri bajarilsa, F2 va G2 kameralari endi E2 hujayrasida mavjud bo'lgan # N / A xatoni ham o'z ichiga olishi kerak

10 dan 10 gacha

Qidiruv mezonlarini kiritish

Qidiruv moslamasi zarur hujayralarga ko'chirilgach, u ma'lumotlar jadvalidan ma'lumotlarni olish uchun ishlatilishi mumkin.

Buni amalga oshirish uchun Lookup_value hujayra (D2) ichiga olishni istagan element nomini yozing va klaviaturada ENTER tugmasini bosing.

Tugatganingizdan so'ng, qidirish formulasini o'z ichiga olgan har bir hujayra siz izlayotgan apparat elementi haqidagi boshqa ma'lumotlarni o'z ichiga olishi kerak.

O'quv qadamlar

1. Mehnat sahifasida D2 uyasiga bosing
2. Vidjetni D2 uyasiga kiriting va klaviaturada ENTER tugmasini bosing
3. Quyidagi ma'lumotlar E2 va G2 hujayralarida ko'rsatilishi kerak:
   • E2 - $ 14.76 - vidjet narxi
   • F2 - PN-98769 - vidjet uchun parcha raqami
   • G2 - Widgets Inc. - widgetlar uchun yetkazib beruvchining nomi
4. VLOOKUP qatori formulasini boshqa qismlarning nomini D2 uyasiga kiritish va natijalarni E2 va G2 hujayralarida kuzatish orqali sinab ko'ring.

#REF kabi bir xato xabari bo'lsa ! E2, F2 yoki G2 hujayralarida paydo bo'ladigan bo'lsa, ushbu VLOOKUP xato xabarlari bu muammoning qaerdaligini aniqlashga yordam beradi.