Upaminipun sampeyan duwe dhaptar sing, kanthi tingkat "straightforwardness" sing beda-beda, data awal ditulis - contone, alamat utawa jeneng perusahaan:
 |  |
Katon kanthi jelas yen kutha utawa perusahaan sing padha ana ing kene ing varian motley, sing, mesthi, bakal nggawe akeh masalah nalika nggarap tabel kasebut ing mangsa ngarep. Lan yen sampeyan mikir sethithik, sampeyan bisa nemokake akeh conto tugas sing padha saka wilayah liyane.
Saiki bayangake manawa data bengkong kasebut teka sampeyan kanthi rutin, yaiku iki dudu crita "mbenerake kanthi manual, lali", nanging masalah kanthi rutin lan akeh sel.
Apa sing kudu ditindakake? Aja ngganti teks bengkong kanthi manual 100500 kaping kanthi sing bener liwat kothak "Ganti lan Ganti" utawa kanthi ngeklik Ctrl+H?
Wangsulan: Bab ingkang pisanan sing teka ing atine ing kahanan kaya mengkono iku kanggo nggawe panggantos massal miturut buku referensi sing wis dikompilasi saka cocog pilihan sing salah lan bener - kaya iki:
Sayange, kanthi prevalensi sing jelas babagan tugas kasebut, Microsoft Excel ora duwe cara sing gampang dibangun kanggo ngrampungake. Kanggo miwiti, ayo dipikirake carane nindakake iki nganggo rumus, tanpa nglibatake "seni meriem abot" ing wangun makro ing VBA utawa Power Query.
Kasus 1. Panggantos lengkap akeh
Ayo dadi miwiti karo kasus sing relatif prasaja - kahanan ing ngendi sampeyan kudu ngganti teks bengkong lawas karo anyar. kanthi.
Ayo kita duwe rong tabel:
Ing pisanan - jeneng variegated asli perusahaan. Ing kaloro - buku referensi korespondensi. Yen kita nemokake ing jeneng perusahaan ing tabel pisanan sembarang tembung saka kolom Kanggo nemokake, banjur sampeyan kudu ngganti jeneng bengkong iki kanthi bener - saka kolom Ganti Tabel goleki kapindho.
Kanggo penak:
- Loro-lorone tabel diowahi dadi dinamis ("pinter") nggunakake trabasan keyboard Ctrl+T utawa tim Lebokake - Tabel (Sisipake - Tabel).
- Ing tab sing katon Constructor (Desain) Tabel pisanan jenenge Data, lan tabel referensi kapindho - Substitutions.
Kanggo njlentrehake logika rumus, ayo rada adoh.
Njupuk perusahaan pisanan saka sel A2 minangka conto lan sementara lali babagan perusahaan liyane, ayo nyoba nemtokake pilihan saka kolom kasebut. Kanggo nemokake ketemu ing kono. Kanggo nindakake iki, pilih sel kosong ing bagean gratis saka sheet lan ketik fungsi kasebut KANGGO NGGOLEKI (GOLEK):
Fungsi iki nemtokake manawa substring sing diwenehake kalebu (argumen pisanan yaiku kabeh nilai saka kolom Kanggo nemokake) menyang teks sumber (perusahaan pisanan saka tabel data) lan kudu ngasilake nomer ordinal karakter saka teks kasebut, utawa kesalahan yen substring ora ditemokake.
Trik ing kene yaiku amarga kita ora nemtokake siji, nanging sawetara nilai minangka argumen pisanan, fungsi iki uga bakal bali minangka asil ora siji nilai, nanging array saka 3 unsur. Yen sampeyan ora duwe versi paling anyar saka Office 365 sing ndhukung array dinamis, banjur sawise ngetik rumus iki banjur klik ing Ketik sampeyan bakal weruh array iki ing sheet:
Yen sampeyan duwe versi Excel sadurunge, banjur sawise ngeklik Ketik kita mung bakal weruh nilai pisanan saka larik asil, IE kesalahan #VALUE! (#VALUE!).
Sampeyan ora kudu wedi 🙂 Nyatane, rumus kita bisa digunakake lan sampeyan isih bisa ndeleng kabeh asil yen sampeyan milih fungsi sing dilebokake ing garis rumus banjur pencet tombol kasebut. F9(aja lali pencet Esckanggo bali menyang rumus):
Array asil asil tegese ing jeneng perusahaan bengkong asli (GK Morozko OAO) kabeh nilai ing kolom Kanggo nemokake ketemu mung kapindho (Morozko), lan diwiwiti saka karakter kaping 4 saurutan.
Saiki ayo nambah fungsi menyang rumus kita Deleng(TUNGGAL):
Fungsi iki nduweni telung argumen:
- Nilai sing dikarepake - sampeyan bisa nggunakake nomer sing cukup gedhe (sing utama yaiku ngluwihi dawa teks apa wae ing data sumber)
- Dideleng_vektor – sawetara utawa Uploaded ngendi kita nggoleki nilai sing dikarepake. Punika fungsi ingkang sampun dipuntepangaken KANGGO NGGOLEKI, sing ngasilake larik {#VALUE!:4:#VALUE!}
- vektor_asil – sawetara saka ngendi kita arep kanggo bali Nilai yen nilai sing dikarepake ditemokake ing sel sing cocog. Ing ngisor iki jeneng sing bener saka kolom kasebut Ganti tabel referensi kita.
Fitur utama lan ora jelas ing kene yaiku fungsi kasebut Deleng yen ora ana sing cocog, tansah goleki nilai paling cilik (sadurunge).. Mulane, kanthi nemtokake nomer sing gedhe (contone, 9999) minangka nilai sing dikarepake, kita bakal meksa Deleng temokake sel kanthi nomer paling cedhak (4) ing larik {#VALUE!:4:#VALUE!} lan baliake nilai sing cocog saka vektor asil, yaiku jeneng perusahaan sing bener saka kolom Ganti.
Nuansa kapindho yaiku, sacara teknis, rumus kita minangka rumus array, amarga fungsi KANGGO NGGOLEKI bali minangka asil ora siji, nanging Uploaded telung nilai. Nanging wiwit fungsi Deleng ndhukung larik metu saka kothak, banjur kita ora kudu ngetik rumus iki minangka rumus larik klasik - nggunakake trabasan keyboard Ctrl+shift+Ketik. Sing prasaja bakal cukup Ketik.
Mekaten. Muga-muga sampeyan entuk logika.
Iku tetep kanggo nransfer rumus rampung menyang sel pisanan B2 kolom Telpon – lan tugas kita wis ditanggulangi!
Mesthi, karo tabel biasa (ora pinter), rumus iki uga dianggo apik (mung aja lali bab tombol F4 lan ndandani pranala sing cocog):
Kasus 2. Panggantos sebagean akeh
Kasus iki rada trickier. Maneh kita duwe rong tabel "pinter":
Tabel pisanan kanthi alamat sing ditulis kanthi bengkong sing kudu didandani (aku nyebat Dhata2). Tabel nomer loro minangka buku referensi, miturut sampeyan kudu nggawe panggantos parsial saka substring ing alamat kasebut (aku nelpon tabel iki Pengganti2).
Bentenane dhasar ing kene yaiku sampeyan mung kudu ngganti fragmen data asli - contone, alamat pertama duwe salah “St. Petersburg” ing sisih tengen “St. Petersburg”, ninggalake alamat liyane (kode pos, dalan, omah) kaya apa.
Rumus sing wis rampung bakal katon kaya iki (kanggo gampang dingerteni, aku dibagi dadi pirang-pirang baris nggunakake Alt+Ketik):
Karya utama ing kene ditindakake kanthi fungsi teks Excel standar SUBSTITUT (GANTIKAN), sing nduweni 3 argumen:
- Teks sumber - alamat bengkong pisanan saka kolom Alamat
- Apa kita looking for - kene kita nggunakake trick karo fungsi Deleng (TUNGGAL)saka cara sadurunge kanggo narik nilai saka kolom Kanggo nemokake, sing kalebu minangka fragmen ing alamat sing mlengkung.
- Apa sing kudu diganti - kanthi cara sing padha, kita nemokake nilai sing cocog karo kolom kasebut Ganti.
Ketik rumus iki karo Ctrl+shift+Ketik ora perlu kene uga, sanajan iku, nyatane, rumus array.
Lan katon kanthi jelas (pirsani kesalahan #N / A ing gambar sadurunge) yen rumus kasebut, kanggo kabeh keanggunan, duwe sawetara kekurangan:
- fungsi SUBSTITUTE sensitif huruf cilik, supaya "Spb" ing baris penultimate ora ditemokake ing tabel panggantos. Kanggo ngatasi masalah iki, sampeyan bisa nggunakake fungsi kasebut ZAMENIT (GANTIKAN), utawa preliminarily nggawa loro tabel menyang register padha.
- Yen teks pisanan bener utawa ing ora ana pecahan sing bisa diganti (baris pungkasan), banjur rumus kita mbuwang kesalahan. Wayahe iki bisa neutralized dening intercepting lan ngganti kasalahan nggunakake fungsi IFERROR (IFERROR):
- Yen teks asli ngandhut sawetara fragmen saka direktori bebarengan, banjur rumus kita mung ngganti sing pungkasan (ing baris 8, Ligovsky «Avenue« diganti dadi "pr-t", Nanging "S-Pb" on “St. Petersburg” ora maneh, amarga "S-Pb” luwih dhuwur ing direktori). Masalah iki bisa ditanggulangi kanthi nglakokake rumus kita dhewe, nanging wis ana ing kolom kasebut Telpon:
Ora sampurna lan rumit ing panggonan, nanging luwih apik tinimbang panggantos manual sing padha, ta? 🙂
PS
Ing artikel sabanjure, kita bakal nemtokake cara ngetrapake substitusi akeh kasebut nggunakake makro lan Power Query.
- Cara fungsi SUBSTITUTE kanggo ngganti teks
- Nemokake Cocokake Teks Persis Nggunakake Fungsi EXACT
- Panelusuran lan substitusi sensitif huruf cilik (VLOOKUP sensitif huruf cilik)