Aytaylik, sizda turli darajadagi "to'g'rilik" bilan dastlabki ma'lumotlar - masalan, manzillar yoki kompaniya nomlari yozilgan ro'yxat mavjud:
 |  |
Ko'rinib turibdiki, xuddi shu shahar yoki kompaniya bu erda rang-barang variantlarda mavjud, bu kelajakda ushbu jadvallar bilan ishlashda juda ko'p muammolarni keltirib chiqarishi aniq. Va agar siz biroz o'ylab ko'rsangiz, boshqa sohalardan shunga o'xshash vazifalarning ko'plab misollarini topishingiz mumkin.
Endi tasavvur qiling-a, bunday egri ma'lumotlar sizga muntazam ravishda keladi, ya'ni bu bir martalik "qo'lda tuzat, unut" hikoyasi emas, balki muntazam ravishda va ko'p sonli hujayralardagi muammo.
Nima qilish kerak? Egri matnni 100500 marta "Topish va almashtirish" oynasi orqali yoki tugmani bosish orqali to'g'risiga almashtirmang. Ctrl+H?
Bunday vaziyatda aqlga kelgan birinchi narsa, noto'g'ri va to'g'ri variantlarga mos keladigan oldindan tuzilgan ma'lumotnomaga muvofiq ommaviy almashtirishni amalga oshirishdir - bu kabi:
Afsuski, bunday vazifaning keng tarqalganligi bilan Microsoft Excelda uni hal qilishning oddiy o'rnatilgan usullari mavjud emas. Boshlash uchun, keling, buni VBA yoki Power Query-da makroslar ko'rinishidagi "og'ir artilleriya" ni jalb qilmasdan, formulalar bilan qanday qilishni aniqlaylik.
Holat 1. Ommaviy to'liq almashtirish
Keling, nisbatan oddiy holatdan boshlaylik - eski egri matnni yangisiga almashtirish kerak bo'lgan vaziyat. to'liq.
Aytaylik, bizda ikkita jadval bor:
Birinchisida - kompaniyalarning asl rang-barang nomlari. Ikkinchisida - yozishmalar uchun ma'lumotnoma. Agar biz birinchi jadvaldagi kompaniya nomidan ustundan biron bir so'zni topsak Topmoq, keyin bu egri nomni to'g'ri - ustundan to'liq almashtirishingiz kerak O'zgartirish ikkinchi qidiruv jadvali.
Qulaylik uchun:
- Ikkala jadval ham klaviatura yorlig'i yordamida dinamik ("aqlli")ga aylantiriladi Ctrl+T yoki jamoa Qo'shish - Jadval (Qo'shish - Jadval).
- Ko'rsatilgan yorliqda konstruktor (Dizayn) nomidagi birinchi jadval ma'lumotlar, va ikkinchi mos yozuvlar jadvali - O'zgartirishlar.
Formulaning mantiqini tushuntirish uchun, keling, bir oz uzoqdan boraylik.
Misol tariqasida A2 katagidan birinchi kompaniyani olib, qolgan kompaniyalarni vaqtincha unutib, ustundan qaysi variantni aniqlashga harakat qilaylik. Topmoq u yerda uchrashadi. Buning uchun varaqning bo'sh qismidagi istalgan bo'sh katakchani tanlang va u erda funktsiyani kiriting TOPMOQ (TOPISH):
Ushbu funktsiya berilgan pastki qator kiritilganligini aniqlaydi (birinchi argument ustundagi barcha qiymatlardir Topmoq) manba matnga (ma'lumotlar jadvalidagi birinchi kompaniya) va matn topilgan belgining tartib raqamini yoki pastki qator topilmasa, xatoni chiqarishi kerak.
Bu erda hiyla shundaki, biz birinchi argument sifatida bir emas, balki bir nechta qiymatlarni ko'rsatganimiz sababli, bu funktsiya ham natijada bitta qiymat emas, balki 3 ta elementdan iborat massivni qaytaradi. Agar sizda dinamik massivlarni qo'llab-quvvatlaydigan Office 365 ning eng so'nggi versiyasi bo'lmasa, ushbu formulani kiritib, tugmani bosgandan so'ng. kiriting siz ushbu massivni to'g'ridan-to'g'ri varaqda ko'rasiz:
Agar sizda Excelning oldingi versiyalari bo'lsa, ustiga bosing kiriting biz natijalar massividan faqat birinchi qiymatni ko'ramiz, ya'ni xato #VALUE! (#VALUE!).
Siz qo'rqmasligingiz kerak 🙂 Aslida, bizning formulamiz ishlaydi va agar siz formulalar qatorida kiritilgan funksiyani tanlab, tugmachani bossangiz, barcha natijalar qatorini ko'rishingiz mumkin. F9(shunchaki bosishni unutmang Escformulaga qaytish uchun):
Olingan natijalar qatori asl egri kompaniya nomini bildiradi (GK Morozko OAJ) ustundagi barcha qiymatlardan Topmoq faqat ikkinchisini topdi (Morozko), va ketma-ket 4-belgidan boshlab.
Endi formulamizga funksiya qo'shamiz VIEW(QIDIRMOQ):
Bu funksiya uchta argumentga ega:
- Istalgan qiymat - har qanday etarlicha katta raqamdan foydalanishingiz mumkin (asosiysi, u manba ma'lumotlaridagi istalgan matn uzunligidan oshib ketadi)
- Ko'rilgan_vektor - biz kerakli qiymatni qidirayotgan diapazon yoki massiv. Bu erda ilgari kiritilgan funktsiya TOPMOQ, bu {#VALUE!:4:#VALUE!} massivni qaytaradi
- vektor_natijalar - agar kerakli qiymat mos keladigan katakda topilsa, biz qiymatni qaytarmoqchi bo'lgan diapazon. Mana ustundan to'g'ri nomlar O'zgartirish bizning ma'lumot jadvalimiz.
Bu erda asosiy va noaniq xususiyat - bu funktsiya VIEW agar aniq moslik bo'lmasa, har doim eng yaqin eng kichik (oldingi) qiymatni qidiring. Shuning uchun, istalgan katta raqamni (masalan, 9999) kerakli qiymat sifatida belgilab, biz majburlaymiz VIEW {#VALUE!:4:#VALUE!} massivida eng yaqin eng kichik raqami (4) boʻlgan katakchani toping va natija vektoridan mos qiymatni, yaʼni ustundan toʻgʻri kompaniya nomini qaytaring. O'zgartirish.
Ikkinchi nuance, texnik jihatdan bizning formulamiz massiv formulasi, chunki funktsiya TOPMOQ natija sifatida bitta emas, balki uchta qiymatdan iborat massivni qaytaradi. Ammo funktsiyadan beri VIEW massivlarni qutidan tashqarida qo'llab-quvvatlasa, biz ushbu formulani klassik massiv formulasi sifatida kiritishimiz shart emas - klaviatura yorliqlari yordamida Ctrl+smena+kiriting. Oddiy bittasi etarli bo'ladi kiriting.
Ana xolos. Umid qilamanki, siz mantiqni tushunasiz.
Tayyor formulani ustunning birinchi B2 katagiga o'tkazish qoladi belgilangan - va bizning vazifamiz hal qilindi!
Albatta, oddiy (aqlli emas) jadvallar bilan bu formula ham ajoyib ishlaydi (shunchaki kalit haqida unutmang. F4 va tegishli havolalarni tuzatish):
Holat 2. Ommaviy qisman almashtirish
Bu holat biroz murakkabroq. Yana ikkita "aqlli" stolimiz bor:
Tuzatish kerak bo'lgan egri yozilgan manzilli birinchi jadval (men uni chaqirdim Ma'lumotlar 2). Ikkinchi jadval ma'lumotnoma bo'lib, unga ko'ra siz manzil ichidagi pastki qatorni qisman almashtirishingiz kerak (men ushbu jadvalni chaqirdim O'zgartirishlar 2).
Bu erda asosiy farq shundaki, siz asl ma'lumotlarning faqat bir qismini almashtirishingiz kerak - masalan, birinchi manzil noto'g'ri. “Sent. Peterburg" o'ngda “Sent. Peterburg", qolgan manzilni (pochta indeksi, ko'cha, uy) qoldirib.
Tayyor formula shunday ko'rinadi (idrok qilish qulayligi uchun men uni nechta qatorga ajratdim Alt+kiriting):
Bu erda asosiy ish standart Excel matn funksiyasi tomonidan amalga oshiriladi Substitut (O'RNISHA), unda 3 ta argument mavjud:
- Manba matni - Manzil ustunidagi birinchi egri manzil
- Biz qidirayotgan narsa - bu erda biz funktsiya bilan hiyla ishlatamiz VIEW (QIDIRMOQ)ustundan qiymatni tortib olishning oldingi usulidan Topmoq, bu egri manzilga bo'lak sifatida kiritilgan.
- Nima bilan almashtirish kerak - xuddi shu tarzda biz ustundan unga mos keladigan to'g'ri qiymatni topamiz O'zgartirish.
Ushbu formula bilan kiriting Ctrl+smena+kiriting Bu erda ham kerak emas, garchi u aslida massiv formulasi bo'lsa ham.
Va aniq ko'rinib turibdiki (oldingi rasmda # N/A xatolariga qarang) bunday formulaning barcha nafisligiga qaramay, bir nechta kamchiliklari bor:
- vazifa SUBSTITUTE katta-kichik harflarga sezgir, shuning uchun oxirgi qatordagi "Spb" almashtirish jadvalida topilmadi. Ushbu muammoni hal qilish uchun siz funktsiyadan foydalanishingiz mumkin ZAMENIT (ALSHISH), yoki oldindan ikkala jadvalni bir xil registrga keltiring.
- Agar matn dastlab to'g'ri bo'lsa yoki unda almashtirish uchun hech qanday parcha yo'q (oxirgi qator), keyin formulamiz xatoga yo'l qo'yadi. Funktsiyadan foydalangan holda xatolarni ushlab turish va almashtirish orqali ushbu momentni zararsizlantirish mumkin XATO (XATO):
- Agar asl matn mavjud bo'lsa bir vaqtning o'zida katalogdan bir nechta fragmentlar, keyin bizning formulamiz faqat oxirgisini almashtiradi (8-qatorda, Ligovskiy «Avenue« ga o'zgartirildi "pr-t", Lekin "S-Pb" on “Sent. Peterburg" endi emas, chunki “S-Pb” katalogida yuqoriroq). Bu muammoni o'z formulamizni qayta ishga tushirish orqali hal qilish mumkin, lekin allaqachon ustun bo'ylab belgilangan:
Joylarda mukammal va noqulay emas, lekin bir xil qo'lda almashtirishdan ancha yaxshi, to'g'rimi? 🙂
PS
Keyingi maqolada biz makrolar va Power Query yordamida bunday ommaviy almashtirishni qanday amalga oshirishni aniqlaymiz.
- SUBSTITUTE funksiyasi matnni almashtirish uchun qanday ishlaydi
- EXACT funksiyasidan foydalanib, aniq matn mosliklarini topish
- Katta-kichik harflarni hisobga olgan holda qidirish va almashtirish (katta harflar sezgir VLOOKUP)