Penggantian teks massal dengan rumus

Misalkan Anda memiliki daftar di mana, dengan berbagai tingkat "lurus", data awal ditulis - misalnya, alamat atau nama perusahaan:

Jelas terlihat bahwa kota atau perusahaan yang sama hadir di sini dalam varian beraneka ragam, yang, jelas, akan menciptakan banyak masalah ketika bekerja dengan tabel ini di masa depan. Dan jika Anda berpikir sedikit, Anda dapat menemukan banyak contoh tugas serupa dari daerah lain.

Sekarang bayangkan bahwa data bengkok seperti itu datang kepada Anda secara teratur, yaitu ini bukan cerita satu kali "perbaiki secara manual, lupakan saja", tetapi masalah secara teratur dan dalam sejumlah besar sel.

Apa yang harus dilakukan? Jangan secara manual mengganti teks yang bengkok 100500 kali dengan yang benar melalui kotak "Temukan dan Ganti" atau dengan mengklik Ctrl+H?

Hal pertama yang terlintas dalam pikiran dalam situasi seperti itu adalah membuat penggantian massal sesuai dengan buku referensi yang telah dikompilasi sebelumnya tentang pencocokan opsi yang salah dan benar – seperti ini:

Sayangnya, dengan prevalensi yang jelas dari tugas seperti itu, Microsoft Excel tidak memiliki metode built-in yang sederhana untuk menyelesaikannya. Untuk memulainya, mari cari tahu cara melakukannya dengan rumus, tanpa melibatkan "artileri berat" dalam bentuk makro di VBA atau Power Query.

Kasus 1. Penggantian penuh massal

Mari kita mulai dengan kasus yang relatif sederhana – situasi di mana Anda perlu mengganti teks lama yang bengkok dengan yang baru. sepenuhnya.

Katakanlah kita memiliki dua tabel:

Yang pertama – nama-nama perusahaan yang beraneka ragam. Yang kedua - buku referensi korespondensi. Jika kita menemukan nama perusahaan di tabel pertama, kata apa pun dari kolom Mencari, maka Anda harus sepenuhnya mengganti nama bengkok ini dengan yang benar – dari kolom Pengganti tabel pencarian kedua.

Untuk kenyamanan:

• Kedua tabel diubah menjadi dinamis ("pintar") menggunakan pintasan keyboard Ctrl+T atau tim Sisipkan – Tabel (Sisipkan — Tabel).
  
• Pada tab yang muncul Pembina (Rancangan) tabel pertama bernama Data, dan tabel referensi kedua – substitusi.

Untuk menjelaskan logika rumusnya, mari kita bahas sedikit dari jauh.

Mengambil perusahaan pertama dari sel A2 sebagai contoh dan untuk sementara melupakan perusahaan lainnya, mari kita coba menentukan opsi mana dari kolom Mencari bertemu di sana. Untuk melakukan ini, pilih sel kosong di bagian bebas lembar dan masukkan fungsi di sana MENCARI (TEMUKAN):

Fungsi ini menentukan apakah substring yang diberikan disertakan (argumen pertama adalah semua nilai dari kolom Mencari) ke dalam teks sumber (perusahaan pertama dari tabel data) dan harus menampilkan nomor urut karakter dari mana teks ditemukan, atau kesalahan jika substring tidak ditemukan.

Triknya di sini adalah karena kami menetapkan bukan hanya satu, tetapi beberapa nilai sebagai argumen pertama, fungsi ini juga akan mengembalikan bukan satu nilai, tetapi array 3 elemen. Jika Anda tidak memiliki versi terbaru Office 365 yang mendukung array dinamis, maka setelah memasukkan rumus ini dan mengklik Enter Anda akan melihat larik ini tepat di lembar:

Jika Anda memiliki versi Excel sebelumnya, maka setelah mengklik Enter kita hanya akan melihat nilai pertama dari larik hasil, yaitu kesalahan #VALUE! (#NILAI!).

Anda tidak perlu takut Faktanya, rumus kami berfungsi dan Anda masih dapat melihat seluruh rangkaian hasil jika Anda memilih fungsi yang dimasukkan di bilah rumus dan menekan tombol F9(jangan lupa tekan Escuntuk kembali ke rumus):

Array hasil yang dihasilkan berarti bahwa dalam nama perusahaan asli yang bengkok (GK Morozko OAO) dari semua nilai dalam kolom Mencari hanya menemukan yang kedua (Morozko), dan mulai dari karakter ke-4 berturut-turut.

Sekarang mari kita tambahkan fungsi ke rumus kita BACA(LIHATLAH):

Fungsi ini memiliki tiga argumen:

1. Nilai yang diinginkan – Anda dapat menggunakan jumlah yang cukup besar (yang utama adalah bahwa itu melebihi panjang teks apa pun dalam data sumber)
   
2. Dilihat_vektor – rentang atau larik tempat kita mencari nilai yang diinginkan. Berikut adalah fungsi yang diperkenalkan sebelumnya MENCARI, yang mengembalikan larik {#VALUE!:4:#VALUE!}
   
3. vektor_hasil – rentang dari mana kita ingin mengembalikan nilai jika nilai yang diinginkan ditemukan di sel yang sesuai. Berikut adalah nama yang benar dari kolom Pengganti tabel referensi kami.
   

Fitur utama dan tidak jelas di sini adalah fungsinya BACA jika tidak ada yang sama persis, selalu cari nilai terkecil (sebelumnya) terdekat. Oleh karena itu, dengan menentukan angka yang besar dan kuat (misalnya, 9999) sebagai nilai yang diinginkan, kami akan memaksa BACA temukan sel dengan angka terkecil terdekat (4) dalam larik {#VALUE!:4:#VALUE!} dan kembalikan nilai yang sesuai dari vektor hasil, yaitu nama perusahaan yang benar dari kolom Pengganti.

Nuansa kedua adalah, secara teknis, rumus kita adalah rumus array, karena fungsi MENCARI kembali sebagai hasil bukan satu, tetapi larik tiga nilai. Tapi karena fungsinya BACA mendukung array di luar kotak, maka kita tidak perlu memasukkan rumus ini sebagai rumus array klasik – menggunakan pintasan keyboard Ctrl+perubahan+Enter. Yang sederhana saja sudah cukup Enter.

Itu saja. Semoga Anda mendapatkan logikanya.

Tetap mentransfer formula yang sudah jadi ke sel pertama B2 kolom Tetap - dan tugas kita terpecahkan!

Tentu saja, dengan tabel biasa (tidak pintar), rumus ini juga berfungsi dengan baik (jangan lupa kuncinya F4 dan memperbaiki tautan yang relevan):

Kasus 2. Penggantian sebagian massal

Kasus ini sedikit lebih rumit. Sekali lagi kami memiliki dua tabel "pintar":

Tabel pertama dengan alamat yang ditulis miring yang perlu diperbaiki (saya menyebutnya Data2). Tabel kedua adalah buku referensi, yang menurutnya Anda perlu melakukan penggantian sebagian substring di dalam alamat (saya menyebut tabel ini Substitusi2).

Perbedaan mendasar di sini adalah Anda hanya perlu mengganti sebagian dari data asli – misalnya, alamat pertama salah “St. Petersburg” di sebelah kanan “St. Petersburg”, biarkan sisa alamat (kode pos, jalan, rumah) apa adanya.

Rumus yang sudah jadi akan terlihat seperti ini (untuk memudahkan persepsi, saya membaginya menjadi berapa banyak baris menggunakan lain+Enter):

Pekerjaan utama di sini dilakukan oleh fungsi teks Excel standar PENGGANTI (PENGGANTI), yang memiliki 3 argumen:

1. Teks sumber – alamat bengkok pertama dari kolom Alamat
2. Apa yang kami cari – di sini kami menggunakan trik dengan fungsi BACA (LIHATLAH)dari cara sebelumnya untuk menarik nilai dari kolom Mencari, yang disertakan sebagai fragmen dalam alamat melengkung.
3. Apa yang harus diganti – dengan cara yang sama kami menemukan nilai yang benar yang sesuai dengannya dari kolom Pengganti.

Masukkan rumus ini dengan Ctrl+perubahan+Enter juga tidak diperlukan di sini, meskipun sebenarnya merupakan rumus array.

Dan terlihat jelas (lihat kesalahan #N/A pada gambar sebelumnya) bahwa formula seperti itu, dengan segala keanggunannya, memiliki beberapa kelemahan:

• fungsi PENGGANTI peka huruf besar-kecil, sehingga “Spb” pada baris kedua dari belakang tidak ditemukan pada tabel pengganti. Untuk mengatasi masalah ini, Anda dapat menggunakan fungsi ZAMENIT (MENGGANTI), atau sebelumnya bawa kedua tabel ke register yang sama.
• Jika teks awalnya benar atau di dalamnya tidak ada fragmen untuk diganti (baris terakhir), maka rumus kita menimbulkan kesalahan. Momen ini dapat dinetralkan dengan mencegat dan mengganti kesalahan menggunakan fungsi KESALAHAN (KESALAHAN):

  

• Jika teks aslinya mengandung beberapa fragmen dari direktori sekaligus, maka rumus kami hanya menggantikan yang terakhir (di baris ke-8, Ligovsky «kesempatan« diubah menjadi "pr-t", Tetapi “S-Pb” on “St. Petersburg” tidak lagi, karena “S-Pb” lebih tinggi di direktori). Masalah ini dapat diselesaikan dengan menjalankan kembali rumus kita sendiri, tetapi sudah di sepanjang kolom Tetap:

  
  

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

PS

Di artikel berikutnya, kita akan mengetahui cara menerapkan substitusi massal seperti itu menggunakan makro dan Power Query.

• Cara kerja fungsi SUBSTITUTE untuk mengganti teks
  
• Menemukan Kecocokan Teks yang Tepat Menggunakan Fungsi EXACT
• Pencarian dan substitusi peka huruf besar/kecil (VLOOKUP peka huruf besar/kecil)