Penggantian teks pukal dengan formula

Katakan anda mempunyai senarai di mana, dengan pelbagai tahap "keterusterangan", data awal ditulis - contohnya, alamat atau nama syarikat:

Ia jelas dilihat bahawa bandar atau syarikat yang sama hadir di sini dalam varian beraneka ragam, yang, jelas sekali, akan menimbulkan banyak masalah apabila bekerja dengan jadual ini pada masa hadapan. Dan jika anda berfikir sedikit, anda boleh menemui banyak contoh tugas yang serupa dari kawasan lain.

Sekarang bayangkan bahawa data bengkok seperti itu datang kepada anda dengan kerap, iaitu ini bukan cerita "baikinya secara manual, lupakannya" sekali sahaja, tetapi masalah secara tetap dan dalam sejumlah besar sel.

Apa nak buat? Jangan gantikan teks bengkok 100500 kali secara manual dengan yang betul melalui kotak "Cari dan Ganti" atau dengan mengklik Ctrl+H?

Perkara pertama yang terlintas di fikiran dalam situasi sedemikian ialah membuat penggantian beramai-ramai mengikut buku rujukan yang telah disusun sebelumnya untuk memadankan pilihan yang salah dan betul – seperti ini:

Malangnya, dengan kelaziman tugas sedemikian yang jelas, Microsoft Excel tidak mempunyai kaedah terbina dalam yang mudah untuk menyelesaikannya. Sebagai permulaan, mari kita fikirkan cara melakukan ini dengan formula, tanpa melibatkan "artileri berat" dalam bentuk makro dalam VBA atau Power Query.

Kes 1. Penggantian penuh pukal

Mari kita mulakan dengan kes yang agak mudah – situasi di mana anda perlu menggantikan teks bengkok lama dengan yang baharu. sepenuhnya.

Katakan kita mempunyai dua jadual:

Dalam yang pertama – nama asal pelbagai syarikat. Dalam kedua - buku rujukan surat-menyurat. Jika kita dapati dalam nama syarikat dalam jadual pertama apa-apa perkataan dari lajur Untuk mencari, maka anda perlu menggantikan sepenuhnya nama bengkok ini dengan yang betul – daripada lajur Pengganti jadual carian kedua.

Untuk kemudahan:

• Kedua-dua jadual ditukar kepada dinamik (“pintar”) menggunakan pintasan papan kekunci Ctrl+T atau pasukan Sisipkan – Jadual (Sisipkan — Jadual).
  
• Pada tab yang muncul Pembina (Reka Bentuk) jadual pertama dinamakan Tarikh, dan jadual rujukan kedua - Penggantian.

Untuk menerangkan logik formula, mari kita pergi sedikit dari jauh.

Mengambil syarikat pertama dari sel A2 sebagai contoh dan melupakan sementara tentang syarikat yang lain, mari cuba tentukan pilihan mana dari lajur Untuk mencari bertemu di sana. Untuk melakukan ini, pilih mana-mana sel kosong di bahagian kosong helaian dan masukkan fungsi di sana UNTUK MENCARI (CARI):

Fungsi ini menentukan sama ada subrentetan yang diberikan disertakan (hujah pertama ialah semua nilai daripada lajur Untuk mencari) ke dalam teks sumber (syarikat pertama daripada jadual data) dan harus mengeluarkan sama ada nombor ordinal aksara dari mana teks itu ditemui, atau ralat jika subrentetan tidak ditemui.

Caranya di sini ialah kerana kami tidak menentukan satu, tetapi beberapa nilai sebagai hujah pertama, fungsi ini juga akan kembali sebagai hasilnya bukan satu nilai, tetapi tatasusunan 3 elemen. Jika anda tidak mempunyai versi terkini Office 365 yang menyokong tatasusunan dinamik, maka selepas memasukkan formula ini dan mengklik pada Masukkan anda akan melihat tatasusunan ini betul-betul pada helaian:

Jika anda mempunyai versi Excel sebelumnya, maka selepas mengklik pada Masukkan kita hanya akan melihat nilai pertama daripada tatasusunan hasil, iaitu ralat #VALUE! (#VALUE!).

Anda tidak perlu takut 🙂 Sebenarnya, formula kami berfungsi dan anda masih boleh melihat keseluruhan susunan hasil jika anda memilih fungsi yang dimasukkan dalam bar formula dan tekan kekunci F9(jangan lupa tekan Escuntuk kembali kepada formula):

Susunan keputusan yang terhasil bermakna dalam nama syarikat yang bengkok asal (GK Morozko OAO) daripada semua nilai dalam lajur Untuk mencari didapati hanya yang kedua (Morozko), dan bermula dari aksara ke-4 berturut-turut.

Sekarang mari tambah fungsi pada formula kami VIEW(LIHAT):

Fungsi ini mempunyai tiga hujah:

1. Nilai yang dikehendaki – anda boleh menggunakan mana-mana nombor yang cukup besar (perkara utama ialah ia melebihi panjang mana-mana teks dalam data sumber)
   
2. Viewed_vector – julat atau tatasusunan di mana kita mencari nilai yang dikehendaki. Berikut ialah fungsi yang diperkenalkan sebelum ini UNTUK MENCARI, yang mengembalikan tatasusunan {#VALUE!:4:#VALUE!}
   
3. vektor_keputusan – julat dari mana kita ingin mengembalikan nilai jika nilai yang dikehendaki ditemui dalam sel yang sepadan. Berikut ialah nama yang betul daripada lajur Pengganti jadual rujukan kami.
   

Ciri utama dan tidak jelas di sini ialah fungsi VIEW jika tiada padanan tepat, sentiasa cari nilai terkecil (sebelumnya) terdekat. Oleh itu, dengan menyatakan sebarang nombor yang besar (contohnya, 9999) sebagai nilai yang dikehendaki, kami akan memaksa VIEW cari sel dengan nombor terkecil terdekat (4) dalam tatasusunan {#VALUE!:4:#VALUE!} dan kembalikan nilai yang sepadan daripada vektor hasil, iaitu nama syarikat yang betul daripada lajur Pengganti.

Nuansa kedua ialah, secara teknikal, formula kami ialah formula tatasusunan, kerana fungsi UNTUK MENCARI kembali sebagai hasil bukan satu, tetapi tatasusunan tiga nilai. Tetapi sejak fungsi VIEW menyokong tatasusunan di luar kotak, maka kita tidak perlu memasukkan formula ini sebagai formula tatasusunan klasik - menggunakan pintasan papan kekunci Ctrl+Beralih+Masukkan. Yang mudah sudah memadai Masukkan.

Itu sahaja. Semoga anda mendapat logiknya.

Ia kekal untuk memindahkan formula siap ke sel pertama B2 lajur Tetap – dan tugas kami selesai!

Sudah tentu, dengan jadual biasa (tidak pintar), formula ini juga berfungsi dengan baik (hanya jangan lupa tentang kuncinya F4 dan membetulkan pautan yang berkaitan):

Kes 2. Penggantian separa pukal

Kes ini lebih rumit sedikit. Sekali lagi kami mempunyai dua jadual "pintar":

Jadual pertama dengan alamat bertulis yang bengkok yang perlu diperbetulkan (saya memanggilnya Data2). Jadual kedua ialah buku rujukan, mengikut mana anda perlu membuat penggantian separa subrentetan di dalam alamat (saya memanggil jadual ini Penggantian2).

Perbezaan asas di sini ialah anda perlu menggantikan hanya serpihan data asal - contohnya, alamat pertama mempunyai salah “St. Petersburg” di sebelah kanan “St. Petersburg”, meninggalkan seluruh alamat (kod pos, jalan, rumah) sebagaimana adanya.

Formula siap akan kelihatan seperti ini (untuk memudahkan persepsi, saya membahagikannya kepada berapa banyak baris yang digunakan Alt+Masukkan):

Kerja utama di sini dilakukan oleh fungsi teks Excel standard PENGGANTI (GANTIAN), yang mempunyai 3 hujah:

1. Teks sumber – alamat bengkok pertama dari lajur Alamat
2. Apa yang kami cari – di sini kami menggunakan helah dengan fungsi tersebut VIEW (LIHAT)dari cara sebelumnya untuk menarik nilai dari lajur Untuk mencari, yang disertakan sebagai serpihan dalam alamat melengkung.
3. Perkara yang perlu diganti – dengan cara yang sama kita dapati nilai yang betul sepadan dengannya daripada lajur Pengganti.

Masukkan formula ini dengan Ctrl+Beralih+Masukkan tidak diperlukan di sini sama ada, walaupun ia, sebenarnya, formula tatasusunan.

Dan jelas dilihat (lihat ralat #N/A dalam gambar sebelumnya) bahawa formula sedemikian, untuk semua keanggunannya, mempunyai beberapa kelemahan:

• fungsi SUBSTITUTE adalah sensitif huruf besar-besaran, jadi "Spb" dalam baris kedua terakhir tidak ditemui dalam jadual gantian. Untuk menyelesaikan masalah ini, anda boleh menggunakan fungsi tersebut ZAMENIT (GANTIKAN), atau bawa kedua-dua jadual ke daftar yang sama.
• Jika teks pada mulanya betul atau di dalamnya tiada serpihan untuk diganti (baris terakhir), maka formula kami membuang ralat. Momen ini boleh dinetralkan dengan memintas dan menggantikan ralat menggunakan fungsi IFERROR (IFERROR):

  

• Jika teks asal mengandungi beberapa serpihan daripada direktori sekaligus, maka formula kami hanya menggantikan yang terakhir (dalam baris ke-8, Ligovsky «Avenue« ditukar menjadi "pr-t", Tetapi “S-Pb” on “St. Petersburg” tidak lagi, kerana “S-Pb” lebih tinggi dalam direktori). Masalah ini boleh diselesaikan dengan menjalankan semula formula kita sendiri, tetapi sudah di sepanjang lajur Tetap:

  
  

Tidak sempurna dan menyusahkan di tempat, tetapi jauh lebih baik daripada penggantian manual yang sama, bukan? 🙂

PS

Dalam artikel seterusnya, kami akan memikirkan cara untuk melaksanakan penggantian pukal sedemikian menggunakan makro dan Power Query.

• Bagaimana fungsi SUBSTITUTE berfungsi untuk menggantikan teks
  
• Mencari Padanan Teks Tepat Menggunakan Fungsi EXACT
• Carian dan penggantian sensitif huruf besar-besaran (VLOOKUP sensitif huruf besar-besaran)