Menyetel tanda hubung di Microsoft Excel

Excel sering harus memproses string teks dengan satu atau lain cara. Sangat sulit untuk melakukan operasi seperti itu secara manual bila jumlah baris lebih dari seratus. Untuk kenyamanan, Excel memiliki sekumpulan fungsi yang cukup bagus untuk bekerja dengan kumpulan data string. Dalam artikel ini saya akan menjelaskan secara singkat fungsi yang diperlukan untuk bekerja dengan string dari kategori "Teks" dan akan mempertimbangkan beberapa contoh.

Fungsi dari kategori "Teks"

Jadi, mari kita lihat fungsi dasar dan berguna dari kategori "Teks", sisanya dapat ditemukan sendiri.

BATTEXT (Nilai) - fungsi yang mengubah angka menjadi tipe teks;
DLSTR (Nilai) adalah fungsi pembantu, sangat berguna saat bekerja dengan string. Mengembalikan panjang string, mis. jumlah karakter yang terkandung dalam string;
MENGGANTIKAN (Teks lama, posisi awal, jumlah karakter, teks baru) - mengganti jumlah karakter yang ditentukan dari posisi tertentu dalam teks lama dengan yang baru;
PENTING (Teks) - mengubah teks menjadi angka;
LEVSIMV (String, Jumlah karakter) - fungsi yang sangat berguna, ini mengembalikan jumlah karakter yang ditentukan, mulai dari karakter pertama;
BAIK (String, Jumlah karakter) - analog dari fungsi LEVSIMV, dengan satu-satunya perbedaan bahwa karakter dikembalikan dari karakter terakhir string;
MENCARI (teks yang akan dicari, teks yang kita cari, posisi awal) - fungsi mengembalikan posisi dari mana teks pencarian mulai muncul. Karakter peka huruf besar / kecil. Jika Anda perlu peka huruf besar kecil, gunakan fungsi tersebut CARI... Hanya posisi kejadian pertama dalam string yang dikembalikan!
PENGGANTI (teks, teks lama, teks baru, posisi) - fungsi yang menarik, sekilas terlihat seperti fungsi MENGGANTIKANtapi fungsinya PENGGANTI dapat mengganti semua kejadian dalam string dengan substring baru jika argumen "posisi" dihilangkan;
PSTR (Teks, Posisi Awal, Jumlah Karakter) - fungsinya mirip dengan LEVSIMV, tetapi mampu mengembalikan karakter dari posisi yang ditentukan:
KOPEL (Teks1, Teks 2 .... Teks 30) - fungsi ini memungkinkan Anda menghubungkan hingga 30 baris. Selain itu, Anda juga dapat menggunakan simbol " & ”, Ini akan terlihat seperti ini“ \u003d ”Text1” & ”Text2” & ”Text3” ”;

Ini sebagian besar adalah fungsi yang umum digunakan saat bekerja dengan string. Sekarang mari kita lihat beberapa contoh untuk mendemonstrasikan bagaimana beberapa fungsi bekerja.

Contoh 1
Diberikan satu set garis:

Diperlukan untuk mengekstrak tanggal, nomor faktur dari baris ini, serta menambahkan bidang bulan untuk memfilter baris berdasarkan bulan.

Mari ekstrak nomor faktur ke dalam kolom B. Untuk melakukan ini, kita akan menemukan apa yang disebut simbol atau kata kunci. Dalam contoh kami, Anda dapat melihat bahwa setiap nomor faktur diawali dengan "Tidak", dan panjang nomor faktur adalah 6 karakter. Mari gunakan fungsi FIND dan PSTR. Kami menulis rumus berikut di sel B2:

= PSTR(A2; MENCARI("Tidak."; A2) +1; 6)

Mari kita analisis rumusnya. Dari baris A2 dari posisi berikutnya setelah karakter yang ditemukan "Tidak", kami mengekstrak 6 karakter nomor tersebut.

Sekarang mari kita ekstrak tanggalnya. Semuanya sederhana di sini. Tanggal terletak di akhir baris dan panjangnya 8 karakter. Rumus C2 adalah sebagai berikut:

= BAIK(A2; 8)

tetapi tanggal yang diekstraksi akan menjadi string, untuk mengubahnya menjadi tanggal yang diperlukan setelah ekstraksi, teks harus diubah menjadi angka:

= PENTING(BAIK(A2; 8))

lalu, setel format tampilan di sel, seperti yang dijelaskan di artikel "".

Dan terakhir, untuk kenyamanan pemfilteran baris lebih lanjut, kami akan memperkenalkan kolom bulan, yang akan kami dapatkan dari tanggal. Hanya untuk membuat satu bulan, kita perlu membuang hari itu dan menggantinya dengan "01". Formula untuk D2:

= PENTING(KOPEL("01"; BAIK(A2; 6))) atau \u003d PENTING("01"& BAIK(A2; 6))

Format sel " MMMM YYYY". Hasil:

Contoh 2
Di barisan " Contoh bekerja dengan string di Excel"semua spasi harus diganti dengan" _ ", cukup tambahkan" MS "sebelum kata" Excel ".

Rumusnya adalah sebagai berikut:

=PENGGANTI(MENGGANTIKAN(A1; CARI("excel"; A1); 0; "MS"); ""; "_")

Untuk memahami rumus ini, pecahkan menjadi tiga kolom. Mulailah dengan PENELUSURAN, yang terakhir adalah PENGGANTI.

Semua. Jika Anda memiliki pertanyaan, tanyakan, jangan ragu

Fungsi berikut menemukan dan mengembalikan bagian string teks atau membuat string besar dari yang kecil: FIND, SEARCH, RIGHT, LEFT, MID, SUBSTITUTE, REPT, REPLACE, CONCATENATE.

Fungsi FIND dan SEARCH

Fungsi FIND dan SEARCH digunakan untuk menentukan posisi satu string teks di tempat lain. Kedua fungsi mengembalikan nomor karakter dari mana kemunculan pertama string pencarian dimulai. Kedua fungsi ini bekerja dengan cara yang sama, kecuali bahwa FIND peka huruf besar dan kecil dan SEARCH menerima karakter wildcard. Fungsi memiliki sintaks berikut:

\u003d TEMUKAN (lookup_text; lookup_text; start_position)
\u003d SEARCH (lookup_text; lookup_text; start_position)

Argumen search_text menentukan string teks yang akan ditemukan, dan argumennya dilihat_teks - teks tempat pencarian dilakukan. Salah satu argumen ini bisa berupa string karakter, diapit tanda kutip ganda, atau referensi sel. Argumen opsional posisi awal menentukan posisi dalam teks yang dilihat dari mana pencarian dimulai. Argumen posisi awal harus digunakan ketika lookup_text berisi beberapa kemunculan teks pencarian. Jika argumen ini dihilangkan, Excel mengembalikan posisi kejadian pertama.

Fungsi-fungsi ini mengembalikan nilai kesalahan ketika search_text tidak terdapat dalam teks yang sedang dilihat, atau start_num kurang dari atau sama dengan nol, atau posisi awal melebihi jumlah karakter dalam teks yang sedang dilihat, atau posisi awal lebih besar dari posisi kemunculan terakhir teks pencarian.

Misalnya untuk menentukan letak huruf "g" pada baris "Pintu garasi", Anda harus menggunakan rumus:

TEMUKAN ("f"; "Pintu garasi")

Rumus ini mengembalikan 5.

Jika Anda tidak tahu persis urutan karakter dari teks yang Anda cari, Anda dapat menggunakan fungsi SEARCH dan memasukkannya ke dalam string search_text karakter wildcard: tanda tanya (?) dan tanda bintang (*). Tanda tanya cocok dengan satu karakter yang diketik secara acak, dan tanda bintang menggantikan urutan karakter apa pun pada posisi yang ditentukan. Misalnya, untuk menemukan posisi nama Anatoly, Alexey, Akaki dalam teks di sel A1, Anda perlu menggunakan rumus:

PENCARIAN ("A * d"; A1)

Fungsi KANAN dan KIRI

Fungsi RIGHT mengembalikan karakter paling kanan dari string argumen, sedangkan fungsi LEFT mengembalikan karakter pertama (kiri). Sintaksis:

\u003d KANAN (teks, num_chars)
\u003d KIRI (teks, num_chars)

Argumen karakter menentukan jumlah karakter yang akan diambil dari argumen teks ... Fungsi-fungsi ini peka spasi dan oleh karena itu jika argumennya teks berisi spasi di awal atau akhir baris, gunakan fungsi TRIM dalam argumen fungsi.

Argumen jumlah karakter harus lebih besar dari atau sama dengan nol. Jika argumen ini dihilangkan, Excel menganggapnya sebagai 1. Jika karakter lebih banyak karakter dalam argumen teks , lalu seluruh argumen dikembalikan.

Fungsi PSTR

Fungsi MID mengembalikan sejumlah karakter tertentu dari string teks, dimulai dari posisi yang ditentukan. Fungsi ini memiliki sintaks berikut:

\u003d MID (teks, posisi_mulai, jumlah karakter)

Argumen teks adalah string teks yang berisi karakter yang akan diekstrak, posisi awal adalah posisi karakter pertama yang akan diekstrak dari teks (relatif terhadap awal baris), dan karakter adalah jumlah karakter yang akan diekstrak.

REPLACE dan fungsi SUBSTITUTE

Kedua fungsi ini menggantikan karakter dalam teks. Fungsi REPLACE menggantikan bagian dari string teks dengan string teks lain dan memiliki sintaks:

\u003d REPLACE (old_text; start_position; jumlah karakter; new_text)

Argumen old_text adalah string teks untuk menggantikan karakter. Dua argumen berikutnya menentukan karakter yang akan diganti (relatif terhadap awal baris). Argumen teks_baru menentukan string teks untuk disisipkan.

Misalnya, sel A2 berisi teks "Vasya Ivanov". Untuk meletakkan teks yang sama di sel A3, mengganti namanya, Anda perlu memasukkan fungsi berikut ke dalam sel A3:

REPLACE (A2; 1; 5; "Petya")

Dalam fungsi SUBSTITUTE, posisi awal dan jumlah karakter yang akan diganti tidak ditentukan, tetapi teks pengganti ditentukan secara eksplisit. Fungsi SUBSTITUTE memiliki sintaks berikut:

\u003d SUBSTITUTE (teks; old_text; new_text; entry_number)

Argumen entry_number bersifat opsional. Ini menginstruksikan Excel untuk hanya mengganti kemunculan string yang diberikan old_text .

Misalnya, sel A1 berisi teks "Nol kurang dari delapan". Anda perlu mengganti kata "nol" dengan "nol".

PENGGANTI (A1; "o"; "y"; 1)

Angka 1 dalam rumus ini menunjukkan bahwa hanya "o" pertama di baris sel A1 yang perlu diubah. Jika argumen entry_number dihilangkan, Excel menggantikan semua kemunculan string old_text per baris teks_baru .

Fungsi REPEAT

Fungsi REPT memungkinkan Anda mengisi sel dengan serangkaian karakter yang diulang beberapa kali. Sintaksis:

\u003d REPEAT (teks, pengulangan)

Argumen teks adalah string karakter yang dikalikan dengan tanda petik. Argumen pengulangan menunjukkan berapa kali untuk mengulang teks. Jika argumen pengulangan adalah 0, fungsi REPEAT membiarkan sel kosong, dan jika itu bukan bilangan bulat, fungsi ini membuang tempat desimal.

Fungsi CONCATENATE

Fungsi CONCATENATE setara dengan pernyataan teks & dan digunakan untuk menggabungkan string. Sintaksis:

\u003d CONCATENATE (teks1, teks2, ...)

Anda dapat menggunakan hingga 30 argumen dalam satu fungsi.

Misalnya, sel A5 berisi teks "paruh pertama tahun ini", rumus berikut mengembalikan teks "Total untuk paruh pertama tahun ini":

HUBUNGKAN ("Total untuk"; A5)

Banyak pengguna Excel mengalami kesulitan yang cukup besar saat mencoba memberi tanda hubung pada lembar kerja. Faktanya adalah bahwa program memahami tanda hubung sebagai tanda minus, dan segera mengubah nilai dalam sel menjadi rumus. Oleh karena itu, masalah ini menjadi sangat mendesak. Mari kita lihat cara membuat tanda hubung di Excel.

Seringkali saat mengisi berbagai dokumen, laporan, deklarasi, Anda perlu menunjukkan bahwa sel yang sesuai dengan indikator tertentu tidak berisi nilai. Untuk tujuan ini, biasanya menggunakan tanda hubung. Untuk program Excel, kemungkinan ini ada, tapi agak bermasalah untuk mengimplementasikannya untuk pengguna yang tidak siap, karena tanda hubung segera diubah menjadi rumus. Untuk menghindari transformasi ini, Anda perlu melakukan tindakan tertentu.

Metode 1: Memformat Rentang

Cara paling terkenal untuk memberi tanda hubung di sel adalah dengan memberinya format teks. Namun, opsi ini tidak selalu membantu.

Setelah itu, sel yang dipilih akan diberi properti format teks... Semua nilai yang dimasukkan ke dalamnya tidak akan dianggap sebagai objek untuk kalkulasi, tetapi sebagai teks biasa. Sekarang di area ini Anda dapat memasukkan simbol "-" dari keyboard dan itu akan ditampilkan persis sebagai tanda hubung, dan tidak akan dianggap oleh program sebagai tanda minus.

Ada satu opsi lagi untuk memformat ulang sel menjadi tampilan teks. Untuk melakukan ini, buka tab "Utama", Anda perlu mengklik daftar drop-down format data, yang terletak di pita di kotak alat "Jumlah"... Daftar tipe pemformatan yang tersedia terbuka. Dalam daftar ini, Anda hanya perlu memilih item "Teks".

Metode 2: Menekan Tombol Enter

Tetapi metode ini tidak berfungsi di semua kasus. Seringkali, bahkan setelah prosedur ini, ketika Anda memasukkan karakter "-" alih-alih karakter yang dibutuhkan oleh pengguna, semua referensi yang sama untuk rentang lainnya muncul. Selain itu, ini tidak selalu nyaman, terutama jika sel dengan tanda hubung di tabel bergantian dengan sel yang berisi data. Pertama, dalam hal ini, Anda harus memformat masing-masing secara terpisah, dan kedua, sel tabel ini akan memiliki format berbeda, yang juga tidak selalu dapat diterima. Tapi Anda bisa melakukannya dengan cara lain.

Metode ini bagus karena kesederhanaannya dan fakta bahwa ia bekerja dengan segala jenis pemformatan. Namun, pada saat yang sama, saat menggunakannya, Anda harus berhati-hati dalam mengedit konten sel, karena karena satu tindakan yang salah, rumus mungkin muncul lagi, bukan tanda hubung.

Metode 3: Sisipkan simbol

Cara lain untuk menulis tanda hubung di Excel adalah dengan memasukkan simbol.

Setelah itu, tanda hubung akan tercermin di sel yang dipilih.

Ada opsi lain untuk metode ini. Berada di jendela "Simbol", buka tab "Karakter spesial"... Di daftar yang terbuka, pilih item Tanda hubung... Klik pada tombol tersebut "Tempel"... Hasilnya akan sama dengan versi sebelumnya.

Metode ini bagus karena Anda tidak perlu takut dengan gerakan mouse yang salah. Simbol tidak akan berubah menjadi rumus. Selain itu, secara visual tanda hubung yang diberikan dengan cara ini terlihat lebih baik daripada karakter pendek yang diketik dari keyboard. Kerugian utama dari opsi ini adalah kebutuhan untuk melakukan beberapa manipulasi sekaligus, yang menyebabkan kerugian sementara.

Metode 4: Tambahkan karakter ekstra

Selain itu, ada cara lain untuk membuat tanda hubung. Benar, secara visual opsi ini tidak akan dapat diterima untuk semua pengguna, karena ini mengasumsikan keberadaan dalam sel, selain tanda "-", satu simbol lagi.

Ada sejumlah cara untuk menyetel tanda hubung dalam sel, pilihan yang dapat dibuat pengguna sesuai dengan tujuan penggunaan dokumen tertentu. Kebanyakan orang mencoba untuk mengubah format sel pada upaya pertama yang gagal untuk menempatkan karakter yang diinginkan. Sayangnya, ini tidak selalu berhasil. Untungnya, ada opsi lain untuk menyelesaikan tugas ini: pergi ke baris lain menggunakan tombol Memasukkan, penggunaan simbol melalui tombol di pita, penggunaan tanda tambahan "'". Masing-masing metode ini memiliki kelebihan dan kekurangannya sendiri, yang telah dijelaskan di atas. Tidak ada opsi universal yang paling cocok untuk memasang tanda hubung di Excel dalam semua kemungkinan situasi.