Excel VLOOKUP funktsiyasining barcha sirlari jadvaldagi ma'lumotlarni topish va uni boshqasiga chiqarish uchun

2024 Muallif: Malcolm Clapton | [email protected]. Oxirgi o'zgartirilgan: 2023-12-17 04:14

Maqolani o'qib bo'lgach, siz nafaqat Excel elektron jadvalida ma'lumotlarni qanday topish va uni boshqasiga chiqarishni, balki VLOOKUP funksiyasi bilan birgalikda ishlatilishi mumkin bo'lgan usullarni ham o'rganasiz.

Excelda ishlayotganda, ko'pincha bitta jadvalda ma'lumotlarni topish va uni boshqasiga chiqarish kerak bo'ladi. Agar siz hali ham buni qanday qilishni bilmasangiz, maqolani o'qib chiqqandan so'ng, siz nafaqat buni qanday qilishni o'rganasiz, balki qanday sharoitlarda tizimdan maksimal ishlashni siqib chiqarishingiz mumkinligini bilib olasiz. VLOOKUP funktsiyasi bilan birgalikda ishlatilishi kerak bo'lgan juda samarali usullarning aksariyati ko'rib chiqiladi.

Agar siz VLOOKUP funksiyasidan yillar davomida foydalanayotgan bo'lsangiz ham, yuqori ehtimollik bilan ushbu maqola siz uchun foydali bo'ladi va sizni befarq qoldirmaydi. Misol uchun, IT mutaxassisi, keyin esa IT bo'yicha rahbar bo'lganim uchun men VLOOKUP-dan 15 yildan beri foydalanaman, lekin men odamlarga Excelni professional asosda o'rgatishni boshlaganimdan keyingina barcha nuanslarni hal qila oldim.

VLOOKUP ning qisqartmasi hisoblanadi vvertikal NStekshirish. Xuddi shunday, VLOOKUP - Vertikal Izlash. Funktsiya nomining o'zi bizga uning ustunlarda emas (gorizontal ravishda - ustunlar ustida takrorlash va qatorni tuzatish) jadval qatorlarida (vertikal - satrlar bo'ylab takrorlash va ustunni tuzatish) qidirishini ko'rsatadi. Shuni ta'kidlash kerakki, VLOOKUP-ning singlisi bor - hech qachon oqqushga aylanmaydigan xunuk o'rdak - bu HLOOKUP funktsiyasi. VLOOKUP dan farqli o'laroq, HLOOKUP gorizontal qidiruvlarni amalga oshiradi, ammo Excel kontseptsiyasi (va haqiqatan ham ma'lumotlarni tashkil qilish kontseptsiyasi) jadvallaringiz kamroq ustunlar va ko'proq qatorlarga ega ekanligini anglatadi. Shuning uchun biz ustunlar bo'yicha ko'ra satrlar bo'yicha ko'p marta qidirishimiz kerak. Agar siz Excelda HLO funksiyasidan tez-tez foydalansangiz, bu hayotda biror narsani tushunmagan bo'lishingiz mumkin.

Sintaksis

VLOOKUP funksiyasi to‘rtta parametrga ega:

= VLOOKUP (;; [;]), Bu yerga:

- kerakli qiymat (kamdan-kam hollarda) yoki kerakli qiymatni o'z ichiga olgan katakchaga havola (holatlarning aksariyati);

- BIRINCHI (!) ustunida parametr qiymati qidiriladigan katakchalar diapazoniga (ikki o'lchovli massiv) havola;

- qiymat qaytariladigan diapazondagi ustun raqami;

- bu diapazonning birinchi ustuni o'sish tartibida tartiblanganmi degan savolga javob beradigan juda muhim parametrdir. Agar massiv tartiblangan bo'lsa, biz TRUE yoki 1 qiymatini belgilaymiz, aks holda - FALSE yoki 0. Agar bu parametr o'tkazib yuborilsa, u sukut bo'yicha 1 ga teng.

Ishonchim komilki, VLOOKUP funksiyasini xira deb biladiganlarning ko'pchiligi to'rtinchi parametr tavsifini o'qib chiqqandan so'ng, o'zlarini noqulay his qilishlari mumkin, chunki ular buni biroz boshqacha ko'rinishda ko'rishga odatlangan: odatda ular qachon aniq moslik haqida gapirishadi. qidiruv (FALSE yoki 0) yoki diapazonni skanerlash (ROQIQ yoki 1).

Endi siz oxirigacha aytilganlarning ma'nosini his qilmaguningizcha, keyingi xatboshini bir necha marta o'qishingiz kerak. U erda har bir so'z muhim. Misollar buni tushunishga yordam beradi.

VLOOKUP formulasi qanday ishlaydi?

• Formula turi I. Agar oxirgi parametr oʻtkazib yuborilsa yoki 1 ga teng boʻlsa, VLOOKUP birinchi ustun oʻsish tartibida tartiblangan deb taxmin qiladi, shuning uchun qidiruv quyidagi qatorda toʻxtaydi. darhol kerakli qiymatdan kattaroq qiymatni o'z ichiga olgan satrdan oldin keladi … Agar bunday qator topilmasa, diapazonning oxirgi qatori qaytariladi.

  

• Formula II. Agar oxirgi parametr 0 sifatida ko'rsatilgan bo'lsa, VLOOKUP massivning birinchi ustunini ketma-ket skanerlaydi va parametr bilan birinchi aniq moslik topilganda darhol qidiruvni to'xtatadi, aks holda # N / A (# N / A) xato kodi. qaytariladi.

  

Formulalar ish jarayonlari

VPR I turi

VPR II turi

I shakldagi formulalar uchun xulosalar

1. Formulalar qiymatlarni diapazonlar bo'ylab taqsimlash uchun ishlatilishi mumkin.
2. Agar birinchi ustun ikki nusxadagi qiymatlarni o'z ichiga olgan bo'lsa va to'g'ri tartiblangan bo'lsa, takroriy qiymatlarga ega bo'lgan oxirgi satrlar qaytariladi.
3. Agar siz birinchi ustunni o'z ichiga olishi mumkin bo'lgan qiymatdan aniq kattaroq qiymatni qidirsangiz, jadvalning oxirgi qatorini osongina topishingiz mumkin, bu juda qimmatli bo'lishi mumkin.
4. Ushbu ko'rinish # N / A xatosini faqat kerakli qiymatdan kam yoki unga teng bo'lmasa, qaytaradi.
5. Agar massiv tartiblanmagan bo'lsa, formula noto'g'ri qiymatlarni qaytarishini tushunish juda qiyin.

II turdagi formulalar uchun xulosalar

Agar kerakli qiymat massivning birinchi ustunida bir necha marta sodir bo'lsa, formula keyingi ma'lumotlarni olish uchun birinchi qatorni tanlaydi.

VLOOKUP ishlashi

Siz maqolaning eng yuqori nuqtasiga yetdingiz. Oxirgi parametr sifatida nol yoki bittani ko'rsatsam, qanday farq bo'ladi? Asosan, har bir kishi, albatta, nolni ko'rsatadi, chunki bu juda amaliy: siz massivning birinchi ustunini tartiblash haqida tashvishlanishingiz shart emas, siz qiymat topilgan yoki topilmaganligini darhol ko'rishingiz mumkin. Ammo varaqingizda bir necha ming VLOOKUP formulalari mavjud bo'lsa, VLOOKUP II sekin ekanligini sezasiz. Shu bilan birga, odatda hamma o'ylashni boshlaydi:

• Menga kuchliroq kompyuter kerak;
• Menga tezroq formula kerak, masalan, ko'p odamlar INDEX + MATCH haqida bilishadi, bu go'yoki 5-10% ga tezroq.

Va kamdan-kam odamlar, siz I turdagi VLOOKUP-dan foydalanishni boshlashingiz va birinchi ustunning har qanday usul bilan tartiblanganligiga ishonch hosil qilishingiz bilan VLOOKUP tezligi 57 marta oshadi deb o'ylashadi. Men so'z bilan yozyapman - ELLI YETTI MARTA! 57% emas, balki 5700%. Men bu haqiqatni juda ishonchli tekshirdim.

Bunday tezkor ishning siri ikkilik qidiruv (yarmga ajratish usuli, dixotomiya usuli) deb ataladigan tartiblangan massivda juda samarali qidiruv algoritmini qo'llash mumkinligidadir. Shunday qilib, I turdagi VLOOKUP uni qo'llaydi va II turdagi VLOOKUP hech qanday optimallashtirishsiz qidiradi. Xuddi shunday parametrni o'z ichiga olgan MATCH funksiyasi va faqat tartiblangan massivlarda ishlaydigan va Lotus 1-2-3 bilan muvofiqligi uchun Excelga kiritilgan LOOKUP funksiyasi uchun ham xuddi shunday.

Formulaning kamchiliklari

VLOOKUP ning kamchiliklari aniq: birinchidan, u ko'rsatilgan massivning faqat birinchi ustunida, ikkinchidan, faqat ushbu ustunning o'ng tomonida qidiradi. Va siz tushunganingizdek, kerakli ma'lumotlarni o'z ichiga olgan ustun biz qidiradigan ustunning chap tomonida bo'lishi mumkin. Yuqorida aytib o'tilgan INDEX + MATCH formulalarining kombinatsiyasi ushbu kamchilikdan mahrum, bu uni VLOOKUP (VLOOKUP) bilan solishtirganda jadvallardan ma'lumotlarni olish uchun eng moslashuvchan echimga aylantiradi.

Formulani hayotda qo'llashning ba'zi jihatlari

Diapazonni qidirish

Assortimentni qidirishning klassik tasviri - buyurtma hajmi bo'yicha chegirmani aniqlash vazifasi.

Matn satrlarini qidiring

Albatta, VLOOKUP nafaqat raqamlarni, balki matnni ham qidiradi. Shuni yodda tutish kerakki, formulada belgilarning holati o'rtasida farq yo'q. Agar joker belgilardan foydalansangiz, loyqa qidiruvni tashkil qilishingiz mumkin. Ikkita joker belgilar mavjud: "?" - matn qatoridagi istalgan bitta belgi o'rnini bosadi, "*" - har qanday belgilar sonining o'rnini bosadi.

Jang joylari

Ko'pincha qidiruvda qo'shimcha bo'shliqlar muammosini qanday hal qilish kerakligi haqida savol tug'iladi. Agar qidiruv jadvali hali ham ulardan tozalanishi mumkin bo'lsa, VLOOKUP formulasining birinchi parametri har doim ham sizga bog'liq emas. Shuning uchun, agar hujayralarni qo'shimcha bo'shliqlar bilan yopish xavfi mavjud bo'lsa, uni tozalash uchun TRIM funktsiyasidan foydalanishingiz mumkin.

Turli xil ma'lumotlar formati

Agar VLOOKUP funktsiyasining birinchi parametri raqamni o'z ichiga olgan, lekin matn sifatida hujayrada saqlanadigan katakka tegishli bo'lsa va massivning birinchi ustunida to'g'ri formatdagi raqamlar mavjud bo'lsa, qidiruv muvaffaqiyatsiz bo'ladi. Qarama-qarshi vaziyat ham mumkin. 1-parametrni kerakli formatga o'tkazish orqali muammoni osongina hal qilish mumkin:

= VLOOKUP (−− D7; Mahsulotlar! $ A $ 2: $ C $ 5; 3; 0) - agar D7 matn va jadvalda raqamlar bo'lsa;

= VLOOKUP (D7 & ""); Mahsulotlar! $ A $ 2: $ C $ 5; 3; 0) - va aksincha.

Aytgancha, siz matnni bir vaqtning o'zida bir nechta usulda raqamga tarjima qilishingiz mumkin, tanlang:

• Ikki tomonlama inkor -D7.
• Bitta D7 * 1 ga ko'paytirish.
• Nol qo'shimcha D7 + 0.
• Birinchi quvvatga ko'tarish D7 ^ 1.

Raqamni matnga aylantirish Excelni ma'lumotlar turini o'zgartirishga majbur qiladigan bo'sh satr bilan birlashtirish orqali amalga oshiriladi.

# N / A ni qanday bostirish kerak

Buni IFERROR funksiyasi bilan bajarish juda qulay.

Masalan: = IFERROR (VLOOKUP (D7; Mahsulotlar! $ A $ 2: $ C $ 5; 3; 0); "").

Agar VLOOKUP xato kodini # N / A qaytarsa, IFERROR uni to'xtatib qo'yadi va 2-parametrni (bu holda bo'sh satr) o'rniga qo'yadi va agar xato bo'lmasa, bu funksiya uni umuman yo'q deb ko'rsatadi, lekin oddiy natijani qaytargan faqat VLOOKUP mavjud.

Massiv

Ko'pincha ular massivning havolasini mutlaq qilishni unutishadi va massivni cho'zishda "suzadi". A2: C5 o'rniga $ A $ 2: $ C $ 5 dan foydalanishni unutmang.

Malumot massivini ish kitobining alohida varag'iga joylashtirish yaxshidir. U oyoq ostiga tushmaydi va u xavfsizroq bo'ladi.

Ushbu massivni nomlangan diapazon sifatida e'lon qilish yanada yaxshi g'oya bo'ladi.

Ko'pgina foydalanuvchilar massivni belgilashda butun ustunlarni ko'rsatib, A: C kabi konstruktsiyadan foydalanadilar. Ushbu yondashuv mavjud bo'lish huquqiga ega, chunki siz massivingiz barcha kerakli satrlarni o'z ichiga olganligini kuzatishingiz shart emas. Agar siz varaqga asl massiv bilan qatorlar qo'shsangiz, A: C sifatida belgilangan diapazonni sozlash shart emas. Albatta, bu sintaktik konstruktsiya Excelni diapazonni aniq belgilashdan ko'ra biroz ko'proq ishni bajarishga majbur qiladi, ammo bu yukni e'tiborsiz qoldirish mumkin. Biz soniyaning yuzdan bir qismi haqida gapiramiz.

Xo'sh, daho yoqasida - massivni shaklda tartibga solish.

Chiqariladigan ustunni belgilash uchun COLUMN funksiyasidan foydalanish

Agar VLOOKUP yordamida ma'lumotlarni olinayotgan jadval qidirish jadvali bilan bir xil tuzilishga ega bo'lsa-da, lekin oddiygina kamroq qatorlarni o'z ichiga olgan bo'lsa, VLOOKUP-dagi USTUN () funksiyasidan avtomatik ravishda olinadigan ustunlar sonini hisoblashingiz mumkin. Bunday holda, barcha VLOOKUP formulalari bir xil bo'ladi (birinchi parametr uchun sozlangan, u avtomatik ravishda o'zgaradi)! E'tibor bering, birinchi parametr mutlaq ustun koordinatasiga ega.

& "|" & bilan kompozit kalit yaratish

Agar bir vaqtning o'zida bir nechta ustunlar bo'yicha qidirish kerak bo'lsa, unda qidiruv uchun kompozit kalit yaratish kerak. Agar qaytarish qiymati matnli bo'lmasa ("Kod" maydonida bo'lgani kabi), lekin raqamli bo'lsa, unda qulayroq SUMIFS formulasi bunga mos keladi va kompozit ustun kaliti umuman talab qilinmaydi.

Bu mening Lifehacker uchun birinchi maqolam. Agar sizga yoqqan bo'lsa, men sizni tashrif buyurishga taklif qilaman, shuningdek, VLOOKUP funksiyasidan foydalanish sirlari va shunga o'xshash narsalar haqida sharhlarda mamnuniyat bilan o'qib chiqing. rahmat.:)