Mundarija
Manba matnida kalit so'zlarni qidirish ma'lumotlar bilan ishlashda eng keng tarqalgan vazifalardan biridir. Keling, quyidagi misoldan foydalanib, uning yechimini bir necha usul bilan ko'rib chiqaylik:
Faraz qilaylik, sizda va menda kalit so'zlar ro'yxati - avtomobil markalarining nomlari - va barcha turdagi ehtiyot qismlarning katta jadvali mavjud bo'lib, unda tavsiflar ba'zan bir vaqtning o'zida bir yoki bir nechta shunday markalarni o'z ichiga olishi mumkin, agar ehtiyot qism bir nechta bo'lsa. avtomobil markasi. Bizning vazifamiz qo'shni hujayralardagi barcha aniqlangan kalit so'zlarni berilgan ajratuvchi belgi (masalan, vergul) orqali topish va ko'rsatishdir.
1-usul. Power Query
Albatta, avval biz klaviatura yorliqlari yordamida jadvallarimizni dinamik (“aqlli”)ga aylantiramiz Ctrl+T yoki buyruqlar Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash), ularga nom bering (masalan Pullarи Zaxira buyumlar) va yorliqda tanlash orqali Power Query muharririga birma-bir yuklang Ma'lumotlar - Jadvaldan/diapazondan (Ma'lumotlar - Jadvaldan/diapazondan). Agar sizda Excel 2010-2013 ning eski versiyalari bo'lsa, unda Power Query alohida plagin sifatida o'rnatilgan bo'lsa, kerakli tugma yorliqda bo'ladi. Quvvat so'rovi. Agar sizda Excel 365 ning yangi versiyasi bo'lsa, tugma Jadval/diapazondan hozir u erga qo'ng'iroq qildi Barglari bilan (Vaqtdan).
Power Query-da har bir jadvalni yuklaganimizdan so'ng, biz Excelga buyruq bilan qaytamiz Bosh sahifa — Yopish va yuklash — Yopish va yuklash… — Faqat ulanish yaratish (Uy — Yopish va yuklash — Yopish va yuklash… — Faqat ulanish yaratish).
Endi dublikat so'rov yarataylik Zaxira buyumlarustiga sichqonchaning o'ng tugmachasini bosing va tanlang Takroriy so'rov (Dublikat so'rov), so'ngra olingan nusxa so'rovining nomini o'zgartiring Natijalar va biz u bilan ishlashda davom etamiz.
Harakatlarning mantiqiyligi quyidagicha:
- Kengaytirilgan yorlig'ida Ustun qo'shish jamoani tanlang Maxsus ustun (Ustun qo'shish - Maxsus ustun) va formulani kiriting = Brendlar. Bosgandan keyin OK Biz yangi ustunga ega bo'lamiz, bu erda har bir katakda kalit so'zlarimiz - avtomobil ishlab chiqaruvchilari ro'yxati bilan ichki jadval bo'ladi:
- Barcha ichki o'rnatilgan jadvallarni kengaytirish uchun qo'shilgan ustunning sarlavhasidagi ikkita strelkali tugmani ishlating. Shu bilan birga, ehtiyot qismlar tavsifi bo'lgan qatorlar markalar sonining ko'pligiga ko'payadi va biz "ehtiyot qismlar-brend" ning barcha mumkin bo'lgan juftliklarini olamiz:
- Kengaytirilgan yorlig'ida Ustun qo'shish jamoani tanlang Shartli ustun (shartli ustun) va manba matnida kalit so'z (brend) mavjudligini tekshirish uchun shart qo'ying (qism tavsifi):
- Qidiruv katta-kichikligini sezmaslik uchun formulalar qatoriga uchinchi argumentni qo'lda qo'shing Compare.OrdinalIgnoreCase hodisani tekshirish funksiyasiga Matn. Tarkibida (agar formulalar paneli ko'rinmasa, uni yorliqda yoqish mumkin Review):
- Olingan jadvalni filtrlaymiz, oxirgi ustunda faqat bittasini qoldiramiz, ya'ni mos keladi va keraksiz ustunni olib tashlaymiz. Voqealar.
- Buyruq bilan bir xil tavsiflarni guruhlash Guruh tomonidan tab O'tkazish (O'zgartirish - guruhlash bo'yicha). Birlashtirish operatsiyasi sifatida tanlang Barcha qatorlar (Barcha qatorlar). Chiqishda biz har bir ehtiyot qism uchun barcha tafsilotlarni, shu jumladan bizga kerak bo'lgan avtomobil ishlab chiqaruvchilarning markalarini o'z ichiga olgan jadvallar ustunini olamiz:
- Har bir qism uchun baholarni chiqarish uchun yorliqda boshqa hisoblangan ustun qo'shing Ustun qo'shish - Maxsus ustun (Ustun qo'shish - Maxsus ustun) va jadvaldan iborat formuladan foydalaning (ular bizning ustunimizda joylashgan Tafsilotlar) va chiqarilgan ustun nomi:
- Olingan ustunning sarlavhasidagi ikkita strelkali tugmachani bosamiz va buyruqni tanlaymiz Qiymatlarni ajratib oling (Qiymatlarni chiqarish)istalgan cheklovchi belgi bilan shtamplarni chiqarish uchun:
- Keraksiz ustunni olib tashlash Tafsilotlar.
- Olingan jadvalga undagi yo'qolgan qismlarni qo'shish uchun tavsiflarda hech qanday brend topilmagan bo'lsa, biz so'rovni birlashtirish protsedurasini bajaramiz. natija asl so'rov bilan Zaxira buyumlar tugma Aralashtirmoq tab Bosh sahifa (Uy - so'rovlarni birlashtirish). Ulanish turi - Tashqi qo'shilish o'ng (O'ng tashqi birlashma):
- Qolgan narsa qo'shimcha ustunlarni olib tashlash va qolganlarini nomlarini o'zgartirish - va bizning vazifamiz hal qilindi:
2-usul. Formulalar
Agar sizda Excel 2016 yoki undan keyingi versiyasi bo'lsa, bizning muammomiz yangi funksiyadan foydalangan holda juda ixcham va oqlangan tarzda hal qilinishi mumkin. ARALASHTIRMOQ (TEXTJOIN):
Bu formula ortidagi mantiq oddiy:
- vazifa qidirish (TOPISH) qismning joriy tavsifida navbat bilan har bir brendning paydo bo'lishini qidiradi va brend topilganidan boshlab belgining seriya raqamini yoki #VALUE xatosini qaytaradi! agar brend tavsifda bo'lmasa.
- Keyin funksiyadan foydalaning IF (Agar) и EOSHIBKA (XATO) biz xatolarni bo'sh matn qatori "" bilan almashtiramiz va belgilarning tartib raqamlarini tovar nomlari bilan almashtiramiz.
- Natijada paydo bo'lgan bo'sh katakchalar va topilgan brendlar ushbu funktsiya yordamida berilgan ajratuvchi belgi orqali bitta satrga yig'iladi. ARALASHTIRMOQ (TEXTJOIN).
Tezlashtirish uchun unumdorlikni taqqoslash va quvvat so'rovini buferlash
Ishlashni tekshirish uchun dastlabki ma'lumotlar sifatida 100 ta ehtiyot qismlar tavsifi jadvalini olaylik. Unda biz quyidagi natijalarga erishamiz:
- Formulalar bo'yicha qayta hisoblash vaqti (2-usul) - 9 sek. formulani birinchi marta butun ustunga nusxalashda va 2 sek. at takroriy (buferlash ta'sir qiladi, ehtimol).
- Power Query so'rovini yangilash vaqti (1-usul) ancha yomonroq - 110 soniya.
Albatta, ko'p narsa ma'lum bir shaxsiy kompyuterning uskunasiga va Office-ning o'rnatilgan versiyasiga va yangilanishlarga bog'liq, ammo umumiy rasm, menimcha, aniq.
Power Query so'rovini tezlashtirish uchun qidiruv jadvalini buferlaymiz Pullar, chunki u so'rovlarni bajarish jarayonida o'zgarmaydi va uni doimiy ravishda qayta hisoblash kerak emas (Power Query de facto kabi). Buning uchun biz funktsiyadan foydalanamiz Jadval.Bufer o'rnatilgan Power Query tilidan M.
Buning uchun so'rovni oching Natijalar va yorliqda Review tugmasini bosing Murakkab muharrir (Koʻrish — Kengaytirilgan muharrir). Ochilgan oynada yangi o'zgaruvchiga ega qator qo'shing Marki 2, bu bizning avtomobil ishlab chiqaruvchi katalogimizning buferlangan versiyasi bo'ladi va bu yangi o'zgaruvchidan keyinroq quyidagi so'rovlar buyrug'ida foydalaning:
Bunday takomillashtirishdan so'ng, so'rovimizni yangilash tezligi deyarli 7 marta - 15 soniyagacha oshadi. Bu mutlaqo boshqacha 🙂
- Power Query-da noaniq matn qidirish
- Ommaviy matnni formulalar bilan almashtirish
- List.Accumute funksiyasi bilan Power Query-da matnni ommaviy almashtirish