Jika Anda pernah berada dalam situasi di mana Anda bekerja dengan spreadsheet (lainnya) di Excel atau Google Docs dan tidak tahu cara menentukan apakah sel tertentu berisi kata kunci atau frasa yang Anda cari, maka
Anehnya, tidak ada aplikasi yang menyediakan fungsi pencarian SUBSTRING bawaan, tetapi kita dapat dengan mudah mereplikasi fungsi yang sama tanpa terlalu banyak kesulitan
Fungsi PENCARIAN Tepercaya
Pada intinya, kita dapat menentukan apakah sebuah sel berisi teks tertentu dengan memanfaatkan fungsi SEARCH. Argumennya sama untuk Excel dan Google Docs dan terlihat seperti ini
SEARCH(find_text, within_text, [start_at])
- find_text_ adalah teks yang Anda cari
- within_text adalah teks atau sel tempat Anda mencari
- start_at adalah argumen opsional yang memungkinkan Anda untuk menentukan nomor karakter within_text pada titik mana untuk memulai pencarian. Secara default ini dianggap 0, artinya akan mencari seluruh string
Contoh Sederhana
Kami memiliki spreadsheet dasar yang dibuat untuk contoh ini yang mencantumkan buku dan penulis terkaitnya
JudulPengarangThe HobbitJ. R. R. TolkienThe SilmarillionJ. R. R. TolkienMoby-DickHerman MelvilleSekarang mari tambahkan sel lain ke samping untuk teks yang ingin kita =SEARCH($F$1, A2) 0, bersama dengan dua kolom baru tempat kita akan menjalankan fungsi SEARCH di atas untuk melihat apakah nilai tersebut (=SEARCH($F$1, A2) 2 atau =SEARCH($F$1, A2) 3) berisi teks =SEARCH($F$1, A2) 0
TitleAuthorTitle MatchAuthor MatchCari. TolkienThe HobbitJ. R. R. TolkienERROR8 The SilmarillionJ. R. R. TolkienERROR8 Moby-DickHerman MelvilleERRORERRORRumus sebenarnya di dalam kolom =SEARCH($F$1, A2) _5 terlihat seperti berikut, menggunakan fungsi SEARCH kami untuk mencari sel kolom =SEARCH($F$1, A2) 7 dari baris tersebut untuk teks di dalam kotak =SEARCH($F$1, A2) 8, yang dalam hal ini hanyalah nama belakang =SEARCH($F$1, A2) 9
=SEARCH($F$1, A2) _
Masalah yang kita lihat di sini adalah fungsi SEARCH mengembalikan kesalahan jika tidak ditemukan kecocokan. Sementara kata =SEARCH($F$1, A2) _9 ditemukan sebagai bagian dari =SEARCH($F$1, A2) 3 sel pertama dan kedua (dimulai dari karakter nomor 8), ia gagal menemukan kecocokan di sel lain dan dengan demikian menghasilkan kesalahan yang buruk, yang tidak ada gunanya bagi kita
Menambahkan Fungsi ISNUMBER
Solusi paling sederhana di sini adalah membungkus fungsi =ISNUMBER(SEARCH($F$1, A2)) 3 di atas dalam fungsi lain yang akan mengubah output dari fungsi SEARCH menjadi nilai boolean (benar atau salah). Untuk tujuan ini, kami akan menggunakan fungsi =ISNUMBER(SEARCH($F$1, A2)) 5. Seperti namanya, fungsi ini hanya memeriksa apakah nilai yang diberikan sebenarnya adalah angka atau bukan, dan mengembalikan nilai =ISNUMBER(SEARCH($F$1, A2)) 6 atau =ISNUMBER(SEARCH($F$1, A2)) 7 sesuai. Jadi =ISNUMBER(SEARCH($F$1, A2)) _8 akan mengembalikan =ISNUMBER(SEARCH($F$1, A2)) 6, sementara SUBSTRING0 akan mengembalikan =ISNUMBER(SEARCH($F$1, A2)) 7
Sekarang fungsi SEARCH_ kita di atas dibungkus dengan =ISNUMBER(SEARCH($F$1, A2)) 5 dan terlihat seperti ini
=ISNUMBER(SEARCH($F$1, A2))
Memperbarui semua sel di tabel kami dengan demikian menghilangkan kesalahan jelek dan memberikan hasil =ISNUMBER(SEARCH($F$1, A2)) 6 atau =ISNUMBER(SEARCH($F$1, A2)) 7 sederhana di kolom SUBSTRING6
Itu dia. Kombinasi fungsi yang sederhana namun efektif untuk memungkinkan Anda dengan mudah menentukan apakah sel tertentu berisi substring teks dan mengembalikan nilai boolean yang berguna sebagai hasilnya
Menu Tampilan 'Tampilkan rumus' (Ctrl+`) di Lembar Dokumen menampilkan rumus dalam Lembar (cobalah, jika belum dicoba). Bagaimana dengan mendapatkan formula tanpa mengubah mode Tampilan Spreadsheet?
Cara normal (cara manual tanpa menggunakan rumus) untuk mengekstrak rumus dari sel adalah sebagai berikut
- Klik sel yang berisi rumus yang akan disalin. Ini akan membuka/menampilkan formula di bilah Formula. Anda dapat menyalin rumus dari sana
- Cara lain adalah dengan mengklik dua kali atau menekan F2 di sel dan menyorot rumus lalu menyalinnya
Anda dapat menempelkan nilai (klik kanan pada sel mana pun untuk melihat opsi tempel nilai) seperti rumus yang disalin dalam sel. Tetapi metode ini memiliki satu kelemahan. Rumus teks yang ditempel ini tidak akan mencerminkan perubahan apa pun yang Anda buat di rumus asli nanti
Jika Anda menggunakan fungsi FORMULATEXT untuk mendapatkan teks rumus dari sel, teks rumus yang dikembalikan akan diperbarui berdasarkan pengeditan selanjutnya pada rumus sumber
Fungsi FORMULATEXT Google Sheets – Sintaks dan Contoh
Sintaksis
FORMULATEXT(cell)Argumen
sel – referensi sel untuk mendapatkan rumus sebagai teks
Formula Google Sheets untuk Mengembalikan Formula dari Sel sebagai String Teks
Lihat contoh berikut
Rumus di sel C2 adalah =A2*A2. Anda bisa mendapatkan rumus itu sebagai teks di sel D2 menggunakan rumus =FORMULATEXT(C2)
Dengan cara ini Anda dapat mengekstrak rumus dari sel sebagai string teks di Google Sheets. Apakah ada manfaat menggunakan fungsi ekstraktor formula ini di Google Sheets?
Ya. Berikut adalah beberapa contoh kehidupan nyata atau Anda dapat mengatakan manfaat
Manfaat Menggunakan Fungsi Formulatext untuk Mendapatkan Teks Formula di Google Sheets
Berikut adalah beberapa manfaat dari fungsi Pencarian ini (ya, Formulatext dikategorikan dalam Pencarian) di Google Sheets
- Untuk melihat beberapa formula sekaligus. Anda hanya dapat melihat formula sel yang aktif di bilah Formula (jika Anda mengklik sekali) atau di dalam sel yang sama (jika Anda mengklik dua kali). Fungsi Formulatext di Google Sheets membantu Anda mendapatkan formula di sel yang berbeda. Selain itu, dengan menggunakan fungsi ini Anda dapat melihat beberapa rumus sekaligus
- Untuk menguji apakah sel berisi rumus. Fungsi Formulatext akan mengembalikan kesalahan N/A jika 'sel' tidak berisi rumus. Harap perhatikan bahwa sudah ada fungsi yang didedikasikan untuk ini – Cara Menggunakan Fungsi ISFORMULA di Google Sheets
- Dalam pemformatan bersyarat. Anda dapat menyorot sel yang berisi fungsi tertentu menggunakan fungsi ini. Saya akan merincinya di tutorial lain dan menautkannya di sini nanti
Memperbarui. Cara Menyoroti Sel yang Mengandung Fungsi Tertentu di Google Sheets
Menghapus Tanda = dari Formula yang Diekstraksi di Google Sheets
OKE. Sekarang Anda tahu cara mendapatkan rumus dari sel sebagai string teks di Google Sheets. Bagaimana dengan menghapus tanda sama dengan dari rumus yang dikembalikan?
Menggunakan Regexextract Anda dapat menghapus tanda = di depan string rumus yang diekstrak
=REGEXEXTRACT(FORMULATEXT(C2),"^.(.*)$")Fungsi FORMULATEXT bukan fungsi larik. Jadi jika Anda menggunakannya dalam larik/rentang, rumus hanya akan mengembalikan teks rumus dari sel paling pertama dari rentang tersebut.