Panggantian téks bulk sareng rumus

Anggap anjeun gaduh daptar dimana, kalayan tingkat "straightforwardness" anu béda-béda, data awal ditulis - contona, alamat atanapi nami perusahaan:

Éta jelas katingal yén kota atanapi perusahaan anu sami aya di dieu dina varian motley, anu, écés, bakal nyiptakeun seueur masalah nalika damel sareng tabel ieu di hareup. Sareng upami anjeun mikir sakedik, anjeun tiasa mendakan seueur conto tugas anu sami ti daérah sanés.

Ayeuna bayangkeun yén data bengkok sapertos sering sumping ka anjeun, nyaéta ieu sanés carita "sacara manual ngalereskeun éta, hilap", tapi masalah rutin sareng dina sajumlah ageung sél.

Naon anu kedah dilakukeun? Entong sacara manual ngagentos téks bengkok 100500 kali sareng anu leres ngalangkungan kotak "Panggihan sareng Ganti" atanapi ku ngaklik Ctrl+H?

Hal kahiji anu aya dina pikiran dina kaayaan sapertos kitu nyaéta ngadamel pergantian massa dumasar kana buku rujukan anu tos disusun pikeun cocog sareng pilihan anu salah sareng leres - sapertos kieu:

Hanjakalna, ku Prévalénsi atra tina tugas sapertos kitu, Microsoft Excel henteu ngagaduhan padika anu diwangun pikeun ngarengsekeunana. Pikeun mimitian, hayu urang terang kumaha ngalakukeun ieu nganggo rumus, tanpa ngalibetkeun "artileri beurat" dina bentuk makro dina VBA atanapi Power Query.

Kasus 1. Bulk ngagantian pinuh

Hayu urang mimitian ku kasus anu kawilang saderhana - kaayaan dimana anjeun kedah ngagentos téks bengkok anu lami ku anu énggal. pinuh.

Anggap urang gaduh dua tabel:

Dina kahiji - ngaran variegated aslina pausahaan. Dina kadua - buku rujukan tina susuratan. Upami urang mendakan nami perusahaan dina tabel kahiji naon waé kecap tina kolom Pikeun mendakan, teras anjeun kedah ngagentos nami bengkung ieu ku anu leres - tina kolom ngagentos méja lookup kadua.

Pikeun genah:

• Duanana tabel dirobah jadi dinamis ("pinter") ngagunakeun potong kompas keyboard Ctrl+T atawa tim Lebetkeun - Méja (Selapkeun - Méja).
  
• Dina tab nu nembongan Constructor (Desain) méja munggaran ngaranna data, jeung tabel rujukan kadua - Substitusi.

Pikeun ngajelaskeun logika rumus, hayu urang balik saeutik ti kajauhan.

Nyandak perusahaan munggaran tina sél A2 sabagé conto sareng samentawis hilap ngeunaan sésa perusahaan, hayu urang coba pikeun nangtukeun pilihan mana tina kolom. Pikeun mendakan papanggih di dinya. Jang ngalampahkeun ieu, pilih mana wae sél kosong dina bagian bébas tina lambaran jeung asupkeun fungsi dinya Pikeun manggihan (MANGGIH):

Pungsi ieu nangtukeun lamun substring dibikeun kaasup (argumen kahiji nyaéta sakabéh nilai tina kolom Pikeun mendakan) kana téks sumber (perusahaan munggaran tina tabel data) jeung kudu kaluaran boh angka ordinal karakter ti mana téks ieu kapanggih, atawa kasalahan lamun substring nu teu kapanggih.

Trik di dieu nyaeta saprak urang dieusian teu hiji, tapi sababaraha nilai salaku argumen kahiji, fungsi ieu ogé bakal balik salaku hasilna teu hiji nilai, tapi hiji Asép Sunandar Sunarya ti 3 elemen. Upami anjeun teu gaduh versi panganyarna tina Office 365 anu ngadukung susunan dinamis, teras saatos ngalebetkeun rumus ieu sareng ngaklik asup Anjeun bakal ningali susunan ieu katuhu dina lambar:

Upami anjeun gaduh vérsi Excel sateuacana, teras saatos ngaklik asup urang ngan bakal ningali nilai munggaran ti Asép Sunandar Sunarya hasil, ie kasalahan #VALUE! (#VALUE!).

Anjeun teu kedah sieun 🙂 Nyatana, rumus kami tiasa dianggo sareng anjeun masih tiasa ningali sadayana hasil tina upami anjeun milih fungsi anu diasupkeun dina bar rumus sareng pencét tombol F9(ngan tong hilap pencét Escbalik deui ka rumus):

Asép Sunandar Sunarya hasilna hartina dina ngaran parusahaan bengkung aslina (GK Morozko OAO) sadaya nilai dina kolom Pikeun mendakan kapanggih ngan kadua (Morozko), sareng mimitian ti karakter ka-4 sakaligus.

Ayeuna hayu urang tambahkeun fungsi kana rumus urang Témbongkeun(NINGALI KA LUHUR):

Fungsi ieu ngagaduhan tilu argumen:

1. nilai nu dipikahoyong - anjeun tiasa nganggo jumlah anu cukup ageung (hal utama nyaéta yén éta ngaleuwihan panjang téks naon waé dina data sumber)
   
2. Viewed_vector - rentang atawa Asép Sunandar Sunarya mana urang keur pilari nilai nu dipikahoyong. Ieu mangrupikeun fungsi anu diwanohkeun sateuacana Pikeun manggihan, nu ngabalikeun array {#VALUE!:4:#VALUE!}
   
3. Vektor_hasil - rentang ti mana urang hayang mulangkeun nilai lamun nilai nu dipikahoyong kapanggih dina sél pakait. Ieu mangrupikeun nami anu leres tina kolom ngagentos tabel rujukan urang.
   

Fitur utama sareng henteu atra di dieu nyaéta fungsina Témbongkeun lamun euweuh patandingan pasti, sok néangan pangleutikna pangcaketna (saméméhna) nilai. Ku alatan éta, ku nangtukeun mana wae angka hefty (contona, 9999) salaku nilai nu dipikahoyong, urang bakal maksakeun Témbongkeun panggihan sél nu angka pangleutikna pangcaketna (4) dina array {#VALUE!:4:#VALUE!} jeung balikkeun nilai pakait tina véktor hasil, nyaéta ngaran parusahaan bener tina kolom. ngagentos.

Nuansa kadua nyaéta, sacara téknis, rumus kami mangrupikeun rumus array, sabab fungsi Pikeun manggihan mulih salaku hasilna teu hiji, tapi hiji Asép Sunandar Sunarya tilu nilai. Tapi saprak fungsi Témbongkeun ngarojong arrays out of the box, mangka urang teu kudu ngasupkeun rumus ieu salaku rumus array klasik - ngagunakeun potong kompas keyboard Ctrl+ngarobah+asup. Hiji basajan bakal cukup asup.

Éta hungkul. Miharep anjeun meunang logika.

Tetep mindahkeun rumus rengse ka sél munggaran B2 kolom tetep - sareng tugas urang direngsekeun!

Tangtu, kalawan tabel biasa (teu pinter), rumus ieu ogé jalan hébat (ngan ulah poho ngeunaan konci F4 sareng ngalereskeun tautan anu relevan):

Kasus 2. ngagantian parsial bulk

Kasus ieu rada trickier. Deui urang gaduh dua tabel "pinter":

Tabél munggaran kalayan alamat ditulis bengkok anu kedah dilereskeun (kuring disebut Data2). Tabél kadua nyaéta buku rujukan, numutkeun éta anjeun kedah ngagentos parsial substring di jero alamatna (kuring disebut tabel ieu. Pangganti2).

Beda dasar di dieu nyaéta yén anjeun ngan ukur kedah ngagentos sempalan data asli - contona, alamat anu munggaran ngagaduhan anu salah. “St. Petersburg" dibeulah katuhu “St. Petersburg", ninggalkeun sesa alamat (kode pos, jalan, imah) sakumaha anu kasebut.

Rumus rengse bakal kasampak kawas kieu (pikeun betah persépsi, kuring dibagi kana sabaraha garis ngagunakeun Alt+asup):

Karya utama di dieu dilakukeun ku fungsi téks Excel standar SUBSTITUT (GANTIKAN), anu ngagaduhan 3 argumen:

1. Sumber téks - alamat bengkok munggaran ti kolom Alamat
2. Naon anu kami pilari - di dieu kami nganggo trik sareng fungsina Témbongkeun (NINGALI KA LUHUR)ti cara saméméhna pikeun narik nilai tina kolom Pikeun mendakan, nu kaasup salaku sempalan dina alamat melengkung.
3. Naon anu kudu diganti - dina cara anu sami urang mendakan nilai anu leres anu cocog sareng éta tina kolom ngagentos.

Lebetkeun rumus ieu kalawan Ctrl+ngarobah+asup teu diperlukeun di dieu boh, sanajan éta, kanyataanna, rumus Asép Sunandar Sunarya.

Sareng jelas katingali (tingali kasalahan #N / ​​A dina gambar sateuacana) yén rumus sapertos kitu, pikeun sadayana kaanggunan, ngagaduhan sababaraha kalemahan:

• fungsi SUBSTITUTE sensitip huruf, jadi "Spb" dina garis penultimate teu kapanggih dina tabel ngagantian. Pikeun ngajawab masalah ieu, anjeun tiasa nganggo fungsi ZAMENIT (GANTIKAN), atawa preliminarily mawa duanana tabel kana register sarua.
• Upami téks mimitina leres atanapi di jerona euweuh sempalan pikeun ngaganti (garis panungtungan), lajeng rumus urang throws kasalahan. moment ieu bisa neutralized ku intercepting tur ngaganti kasalahan ngagunakeun fungsi IFERROR (IFERROR):

  

• Lamun téks aslina ngandung sababaraha fragmen tina diréktori sakaligus, teras rumus urang ngan ukur ngagentos anu terakhir (dina garis ka-8, Ligovsky «jalan gede« dirobah jadi "pr-t", Tapi "S-Pb" on “St. Petersburg" henteu deui, sabab “S-Pb” leuwih luhur dina diréktori). masalah ieu bisa direngsekeun ku ngajalankeun ulang rumus urang sorangan, tapi geus sapanjang kolom tetep:

  
  

Henteu sampurna sareng pajeujeut di tempat-tempat, tapi langkung saé tibatan ngagantian manual anu sami, sanés? 🙂

PS

Dina artikel salajengna, urang bakal terang kumaha nerapkeun substitusi bulk sapertos nganggo makro sareng Power Query.

• Kumaha fungsi SUBSTITUTE dianggo pikeun ngaganti téks
  
• Milarian patandingan téks anu pas ngagunakeun fungsi EXACT
• Pilarian sareng substitusi sensitip huruf (VLOOKUP sensitip huruf)