Fungsi teks dan rumus di excel. Bekerja dengan string di Excel

• tanda kurang dari atau sama dengan<=»;

• tanda "tidak sama".<>».

Operator penggabungan teks

Karakter khusus "&" (ampersand) digunakan untuk tujuan ini. Dengan itu, Anda dapat menghubungkan fragmen yang berbeda menjadi satu kesatuan - prinsip yang sama dengan fungsi "CONNECT". Berikut beberapa contohnya:

1. Jika Anda ingin menggabungkan teks dalam sel, maka Anda perlu menggunakan kode berikut.

1. Untuk menyisipkan beberapa karakter atau huruf di antaranya, Anda perlu menggunakan konstruksi berikut.

1. Anda tidak hanya dapat menggabungkan sel, tetapi juga karakter biasa.

Teks apa pun selain tautan harus diapit dengan tanda kutip. Jika tidak, rumus akan menimbulkan kesalahan.

Harap perhatikan bahwa kutipan digunakan persis seperti pada tangkapan layar.

Anda dapat menggunakan operator berikut untuk menentukan tautan:

• untuk membuat tautan sederhana ke rentang sel yang diinginkan, cukup menentukan sel pertama dan terakhir dari area ini, dan simbol ":" di antaranya;
• untuk menggabungkan tautan, gunakan tanda ";";
• jika perlu untuk menentukan sel yang berada di persimpangan beberapa rentang, maka "ruang" ditempatkan di antara tautan. Dalam hal ini, nilai sel "C7" akan ditampilkan.

Karena hanya itu yang termasuk dalam definisi "persimpangan himpunan". Ini adalah nama operator ini (spasi).

Penggunaan tautan

Saat bekerja di editor Excel, Anda dapat menggunakan tautan dari berbagai jenis. Namun, sebagian besar pengguna pemula hanya tahu cara menggunakan yang paling sederhana. Kami akan mengajari Anda cara memasukkan tautan semua format dengan benar.

Tautan Sederhana A1

Biasanya, jenis ini paling sering digunakan, karena jauh lebih nyaman untuk menyusunnya daripada yang lain.

• kolom - dari A ke XFD (tidak lebih dari 16384);
• baris - dari 1 hingga 1048576.

Berikut beberapa contohnya:

• sel di persimpangan baris 5 dan kolom B - "B5";
• rentang sel di kolom B mulai dari baris 5 hingga baris 25 - "B5:B25";
• rentang sel di baris 5 mulai dari kolom B hingga F - "B5:F5";
• semua sel di baris 10 - "10:10";
• semua sel dalam baris 10 sampai 15 - "10:15";
• semua sel di kolom B - "B:B";
• semua sel dalam kolom dari B ke K - "B:K";
• rentang sel dari B2 hingga F5 adalah "B2-F5".

Terkadang rumus menggunakan informasi dari lembar lain. Ia bekerja sebagai berikut.

Lembar kedua berisi data berikut.

Jika ada spasi pada nama sheet, maka pada formula harus diberi tanda kutip tunggal (apostrof).

Tautan absolut dan relatif

Editor Excel bekerja dengan tiga jenis tautan:

• mutlak;
• relatif;
• Campuran.

Mari pertimbangkan mereka dengan lebih hati-hati.

Semua contoh yang disebutkan sebelumnya milik alamat sel relatif. Tipe ini yang paling populer. Keuntungan praktis utamanya adalah editor akan mengubah referensi ke nilai yang berbeda selama migrasi. Sesuai dengan di mana tepatnya Anda menyalin rumus ini. Untuk perhitungannya, jumlah sel antara posisi lama dan baru akan diperhitungkan.

Bayangkan Anda perlu merentangkan rumus ini ke seluruh kolom atau baris. Anda tidak akan mengubah huruf dan angka secara manual di alamat sel. Ia bekerja sebagai berikut.

1. Masukkan rumus untuk menghitung jumlah kolom pertama.

1. Tekan tombol pintas Ctrl +C . Untuk mentransfer rumus ke sel berikutnya, Anda harus pergi ke sana dan menekan Ctrl + V .

Jika tabelnya sangat besar, lebih baik klik di pojok kanan bawah dan, tanpa melepaskan jari Anda, regangkan penunjuk ke ujung. Jika ada sedikit data, menyalin menggunakan hotkey jauh lebih cepat.

1. Sekarang lihat formula baru. Perubahan indeks kolom terjadi secara otomatis.

Jika Anda ingin semua tautan dipertahankan saat mentransfer rumus (yaitu, agar tidak berubah secara otomatis), Anda perlu menggunakan alamat absolut. Mereka ditetapkan sebagai "$B$2".

Hasilnya, kami melihat bahwa tidak ada perubahan yang terjadi. Semua kolom menampilkan nomor yang sama.

Jenis alamat ini digunakan jika hanya perlu memperbaiki kolom atau baris, dan tidak semuanya pada waktu yang bersamaan. Anda dapat menggunakan konstruksi berikut:

• $D1, $F5, $G3 - untuk memperbaiki kolom;
• D$1, F$5, G$3 - untuk memperbaiki baris.

Bekerja dengan formula seperti itu hanya jika diperlukan. Misalnya, jika Anda perlu bekerja dengan satu baris data konstan, tetapi hanya mengubah kolomnya. Dan yang paling penting - jika Anda akan menghitung hasilnya di sel berbeda yang tidak terletak di garis yang sama.

Faktanya adalah saat Anda menyalin rumus ke baris lain, angka di tautan akan secara otomatis berubah dengan jumlah sel dari nilai aslinya. Jika Anda menggunakan alamat campuran, maka semuanya akan tetap pada tempatnya. Ini dilakukan dengan cara berikut.

1. Mari gunakan ekspresi berikut sebagai contoh.

1. Mari pindahkan rumus ini ke sel lain. Lebih disukai tidak di baris berikutnya dan di baris lain. Sekarang Anda dapat melihat bahwa ekspresi baru berisi string yang sama (4) tetapi huruf yang berbeda, karena itu adalah satu-satunya yang relatif.

Tautan 3D

Konsep "tiga dimensi" mencakup alamat-alamat yang menunjukkan rentang lembaran. Contoh rumus terlihat seperti ini.

Dalam hal ini, hasilnya akan sesuai dengan jumlah semua sel "A5" di semua lembar, mulai dari 1 hingga 4. Saat menyusun ekspresi seperti itu, kondisi berikut harus diperhatikan:

• array tidak dapat menggunakan referensi seperti itu;
• ekspresi tiga dimensi dilarang digunakan jika terdapat perpotongan sel (misalnya, operator "ruang");
• Saat membuat rumus dengan alamat 3D, Anda dapat menggunakan fungsi berikut: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNT, COUNT, MIN, MAX, MIN, MAX, VARR, PRODUCT, VARV, VAR.V dan DISPA.

Jika Anda melanggar aturan ini, Anda akan melihat beberapa kesalahan.

Tautan berformat R1C1

Jenis tautan ini berbeda dari "A1" karena nomornya diberikan tidak hanya ke baris, tetapi juga ke kolom. Pengembang memutuskan untuk mengganti tampilan normal dengan opsi ini untuk kenyamanan dalam makro, tetapi dapat digunakan di mana saja. Berikut beberapa contoh alamat tersebut:

• R10C10 adalah tautan absolut ke sel, yang terletak di baris kesepuluh dari kolom kesepuluh;
• R - tautan absolut ke tautan saat ini (di mana rumus ditunjukkan);
• R[-2] – tautan relatif ke garis, yang terletak dua posisi di atasnya;
• R[-3]C – referensi relatif ke sel, yang terletak tiga posisi lebih tinggi di kolom saat ini (tempat Anda memutuskan untuk menulis rumus);
• RC adalah tautan relatif ke sel yaitu lima sel ke kanan dan lima baris di bawah sel saat ini.

Penggunaan nama

Program Excel untuk menamai rentang sel, sel tunggal, tabel (reguler dan ringkasan), konstanta, dan ekspresi memungkinkan Anda membuat nama unik Anda sendiri. Pada saat yang sama, tidak ada perbedaan bagi editor saat bekerja dengan rumus - dia mengerti segalanya.

Anda dapat menggunakan nama untuk perkalian, pembagian, penambahan, pengurangan, perhitungan bunga, rasio, penyimpangan, pembulatan, PPN, hipotek, pinjaman, perkiraan, lembar waktu, berbagai bentuk, diskon, gaji, senioritas, pembayaran anuitas, bekerja dengan rumus VLOOKUP , "VVD", "INTERIM.RESULTS" dan seterusnya. Artinya, Anda dapat melakukan apapun yang Anda inginkan.

Hanya satu hal yang bisa disebut syarat utama - Anda harus menentukan nama ini terlebih dahulu. Kalau tidak, Excel tidak akan tahu apa-apa tentang itu. Ini dilakukan dengan cara berikut.

1. Pilih kolom.
2. Panggil menu konteks.
3. Pilih "Beri Nama".

1. Tentukan nama yang diinginkan untuk objek ini. Dalam hal ini, Anda harus mematuhi aturan berikut.

1. Klik tombol "OK" untuk menyimpan.

Dengan cara yang sama, Anda dapat menetapkan nama ke sel, teks, atau nomor apa pun.

Anda dapat menggunakan informasi dalam tabel baik dengan bantuan nama maupun dengan bantuan tautan biasa. Seperti inilah standarnya.

Dan jika Anda mencoba memasukkan nama kami alih-alih alamat "D4: D9", Anda akan melihat petunjuknya. Cukup menulis beberapa karakter, dan Anda akan melihat mana yang paling cocok (dari basis nama).

Dalam kasus kami, semuanya sederhana - "column_3". Dan bayangkan Anda akan memiliki banyak nama seperti itu. Anda tidak akan bisa menghafal semuanya.

Menggunakan fungsi

Ada beberapa cara untuk menyisipkan fungsi di editor Excel:

• secara manual;
• menggunakan bilah alat;
• menggunakan jendela Sisipkan Fungsi.

Mari kita lihat lebih dekat setiap metode.

Dalam hal ini, semuanya sederhana - Anda menggunakan tangan, pengetahuan, dan keterampilan Anda sendiri untuk memasukkan rumus di baris khusus atau langsung di sel.

Jika Anda tidak memiliki pengalaman kerja di bidang ini, lebih baik menggunakan metode yang lebih ringan terlebih dahulu.

Dalam hal ini diperlukan:

1. Buka tab "Rumus".
2. Klik di perpustakaan mana saja.
3. Pilih fungsi yang diinginkan.

1. Segera setelah itu, jendela "Arguments and Functions" akan muncul dengan fungsi yang sudah dipilih. Anda hanya perlu meletakkan argumen dan menyimpan rumus menggunakan tombol "OK".

Penyihir Pergantian

Anda dapat menerapkannya seperti ini:

1. Jadikan sel apa pun aktif.
2. Klik ikon "Fx" atau gunakan pintasan keyboard SHIFT + F3.

1. Segera setelah itu, jendela "Sisipkan Fungsi" akan terbuka.
2. Di sini Anda akan melihat daftar besar berbagai fitur yang diurutkan berdasarkan kategori. Selain itu, Anda dapat menggunakan pencarian jika Anda tidak dapat menemukan barang yang diinginkan.

Cukup dengan mencetak satu kata yang dapat menggambarkan apa yang ingin Anda lakukan, dan editor akan mencoba menampilkan semua opsi yang sesuai.

1. Pilih fungsi apa pun dari daftar yang diusulkan.
2. Untuk melanjutkan, Anda perlu mengklik tombol "OK".

1. Anda kemudian akan diminta untuk "Argumen dan Fungsi". Anda dapat melakukannya secara manual atau cukup memilih rentang sel yang diinginkan.
2. Untuk menerapkan semua pengaturan, Anda perlu mengklik tombol "OK".

1. Akibatnya, kita akan melihat angka 6, meskipun ini sudah bisa dimengerti, karena jendela Argumen dan Fungsi menampilkan hasil awal. Data dihitung ulang secara instan ketika salah satu argumen berubah.

Menggunakan fungsi bersarang

Sebagai contoh, kami akan menggunakan rumus dengan kondisi logis. Untuk melakukan ini, kita perlu menambahkan semacam tabel.

Kemudian ikuti petunjuk di bawah ini:

1. Klik pada sel pertama. Panggil jendela "Sisipkan fungsi". Pilih fungsi "Jika". Klik "OK" untuk menempel.

1. Maka Anda perlu membuat semacam ekspresi logis. Itu harus ditulis di bidang pertama. Misalnya, Anda dapat menambahkan nilai dari tiga sel dalam satu baris dan memeriksa apakah jumlahnya lebih dari 10. Dalam kasus "benar", tentukan teks "Lebih dari 10". Untuk hasil yang salah - "Kurang dari 10". Kemudian klik OK untuk kembali ke ruang kerja.

1. Hasilnya, kita melihat yang berikut - editor memberikan bahwa jumlah sel di baris ketiga kurang dari 10. Dan ini benar. Jadi kode kita berfungsi.

1. Sekarang Anda perlu mengonfigurasi sel-sel berikut. Dalam hal ini, rumus kami hanya meluas lebih jauh. Untuk melakukan ini, pertama-tama Anda harus memindahkan kursor ke pojok kanan bawah sel. Setelah kursor berubah, Anda perlu melakukan klik kiri dan menyalinnya ke bagian paling bawah.

1. Akibatnya, editor menghitung ulang ekspresi kami untuk setiap baris.

Seperti yang Anda lihat, salinannya sangat sukses karena kami menggunakan tautan relatif yang kami bicarakan sebelumnya. Jika Anda perlu memperbaiki alamat dalam argumen fungsi, gunakan nilai absolut.

Ada beberapa cara untuk melakukannya: gunakan bilah rumus atau wizard khusus. Dalam kasus pertama, semuanya sederhana - klik di bidang khusus dan masukkan perubahan yang diperlukan secara manual. Tetapi menulis di sana sangat tidak nyaman.

Satu-satunya hal yang dapat Anda lakukan adalah menambah bidang input. Untuk melakukan ini, cukup klik ikon yang ditunjukkan atau tekan kombinasi tombol Ctrl + Shift + U.

Perlu dicatat bahwa ini adalah satu-satunya cara jika Anda tidak menggunakan fungsi dalam rumus.

Dalam hal menggunakan fungsi, semuanya menjadi lebih sederhana. Untuk mengedit, ikuti petunjuk di bawah ini:

1. Aktifkan sel dengan rumus. Klik pada ikon "Fx".

1. Setelah itu, sebuah jendela akan muncul di mana Anda dapat mengubah argumen dari fungsi yang Anda perlukan dengan cara yang sangat nyaman. Selain itu, di sini Anda dapat mengetahui dengan tepat apa hasil penghitungan ulang ekspresi baru nantinya.

1. Untuk menyimpan perubahan yang dilakukan, gunakan tombol "OK".

Untuk menghapus ekspresi, lakukan saja hal berikut:

1. Klik pada sel mana saja.

1. Klik pada tombol Hapus atau Backspace. Akibatnya, sel akan kosong.

Anda dapat mencapai hasil yang persis sama dengan menggunakan alat Hapus Semua.

Kemungkinan kesalahan saat menyusun rumus di editor Excel

Berikut ini adalah kesalahan paling umum yang dilakukan pengguna:

• Ekspresi menggunakan sejumlah besar sarang. Seharusnya tidak lebih dari 64;
• jalur ke buku kerja eksternal ditentukan dalam rumus tanpa jalur lengkap;
• Tanda kurung buka dan tutup salah tempat. Itulah mengapa di editor di bilah rumus semua tanda kurung disorot dengan warna berbeda;

• nama buku dan lembaran tidak diambil dalam tanda petik;
• angka digunakan dalam format yang salah. Misalnya, jika Anda perlu menentukan $2000, Anda hanya perlu mengetikkan 2000 dan memilih format sel yang sesuai, karena simbol $ digunakan oleh program untuk referensi absolut;

• argumen fungsi yang diperlukan tidak ditentukan. Perhatikan bahwa argumen opsional dilampirkan dalam tanda kurung siku. Segala sesuatu tanpa mereka diperlukan untuk pengoperasian penuh formula;

• Rentang sel salah. Untuk melakukan ini, Anda harus menggunakan operator ":" (titik dua).

Kode kesalahan saat bekerja dengan rumus

Saat bekerja dengan rumus, Anda mungkin melihat opsi kesalahan berikut:

• #NILAI! - Kesalahan ini menunjukkan bahwa Anda menggunakan tipe data yang salah. Misalnya, Anda mencoba menggunakan teks, bukan nilai numerik. Tentu saja, Excel tidak dapat menghitung jumlah antara dua frase;
• #NAMA? - kesalahan serupa berarti Anda salah ketik dalam ejaan nama fungsi. Atau Anda mencoba memasukkan sesuatu yang tidak ada. Anda tidak bisa melakukan itu. Selain itu, masalahnya mungkin di tempat lain. Jika sudah yakin dengan nama fungsinya, maka coba perhatikan rumusnya lebih teliti. Mungkin Anda lupa tanda kurung. Selain itu, Anda perlu mempertimbangkan bahwa fragmen teks ditunjukkan dalam tanda kutip. Jika semuanya gagal, coba buat ekspresi lagi;
• #NOMOR! - menampilkan pesan seperti itu berarti Anda memiliki masalah dengan argumen atau hasil rumus. Misalnya, jumlahnya ternyata terlalu besar atau sebaliknya - kecil;
• #DIV/0! - Kesalahan ini berarti Anda mencoba menulis ekspresi yang membagi dengan nol. Excel tidak dapat membatalkan aturan matematika. Oleh karena itu, tindakan seperti itu juga dilarang di sini;
• #T/A! - Editor dapat menampilkan pesan ini jika beberapa nilai tidak tersedia. Misalnya, jika Anda menggunakan fungsi SEARCH, SEARCH, MATCH, dan Excel tidak menemukan fragmen yang Anda cari. Atau tidak ada data sama sekali dan rumusnya tidak ada hubungannya;
• Jika Anda mencoba menghitung sesuatu dan Excel menulis kata #REF!, maka rentang sel yang salah digunakan dalam argumen fungsi;
• #KOSONG! - kesalahan ini muncul jika Anda memiliki rumus yang tidak konsisten dengan rentang yang tumpang tindih. Lebih tepatnya, jika pada kenyataannya tidak ada sel seperti itu (yang berada di persimpangan dua rentang). Cukup sering, kesalahan ini terjadi secara tidak sengaja. Cukup menyisakan satu spasi dalam argumen, dan editor akan menganggapnya sebagai operator khusus (kita telah membicarakannya sebelumnya).

Saat mengedit rumus (sel disorot), Anda akan melihat bahwa sel tersebut tidak benar-benar berpotongan.

Terkadang Anda dapat melihat banyak # karakter yang memenuhi lebar sel. Sebenarnya, tidak ada kesalahan di sini. Ini berarti Anda bekerja dengan angka yang tidak sesuai dengan sel ini.

Untuk melihat nilai yang terkandung di sana, cukup mengubah ukuran kolom.

Selain itu, Anda dapat menggunakan pemformatan sel. Untuk melakukan ini, Anda perlu mengikuti beberapa langkah sederhana:

1. Panggil menu konteks. Pilih Format Sel.

1. Tentukan jenis "Umum". Gunakan tombol "OK" untuk melanjutkan.

Berkat ini, editor Excel akan dapat menerjemahkan angka ini ke dalam format lain yang sesuai dengan kolom ini.

Contoh Penggunaan Rumus

Editor Microsoft Excel memungkinkan Anda memproses informasi dengan cara apa pun yang nyaman bagi Anda. Ada semua kondisi dan peluang yang diperlukan untuk ini. Mari kita lihat beberapa contoh rumus berdasarkan kategori. Ini akan memudahkan Anda untuk mengetahuinya.

Untuk mengevaluasi kemampuan matematika Excel, Anda perlu melakukan langkah-langkah berikut.

1. Buat tabel dengan beberapa data bersyarat.

1. Untuk menghitung jumlahnya, masukkan rumus berikut. Jika Anda hanya ingin menambahkan satu nilai, Anda dapat menggunakan operator penambahan ("+").

1. Anehnya, di editor Excel Anda tidak dapat mengambil menggunakan fungsi. Untuk deduksi, operator biasa "-" digunakan. Dalam hal ini, kodenya adalah sebagai berikut.

1. Untuk menentukan persentase angka pertama dari angka kedua, Anda perlu menggunakan konstruksi sederhana ini. Jika Anda ingin mengurangi beberapa nilai, Anda harus menuliskan "minus" untuk setiap sel.

Perhatikan bahwa simbol persen diletakkan di akhir, bukan di awal. Selain itu, saat mengerjakan persentase, Anda tidak perlu mengalikan dengan 100. Ini terjadi secara otomatis.

1. Excel dapat menambahkan, dengan mempertimbangkan beberapa kondisi sekaligus. Anda dapat menghitung jumlah sel dari kolom pertama, yang nilainya lebih besar dari 2 dan kurang dari 6. Dan rumus yang sama dapat diatur untuk kolom kedua.

=SUMIFS(B3:B9,B3:B9,">2",B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2",C3:C9,"<6")

1. Anda juga dapat menghitung jumlah elemen yang memenuhi beberapa syarat. Misalnya, biarkan Excel menghitung berapa angka yang kita miliki lebih besar dari 3.

=COUNTIF(B3:B9,">3") =COUNTIF(C3:C9,">3")

1. Hasil dari semua rumus adalah sebagai berikut.

Fungsi matematika dan grafik

Menggunakan Excel, Anda dapat menghitung berbagai fungsi dan membuat grafik di atasnya, lalu melakukan analisis grafis. Biasanya, teknik seperti itu digunakan dalam presentasi.

Sebagai contoh, mari kita coba membuat grafik untuk eksponen dan beberapa persamaan. Instruksinya adalah sebagai berikut:

1. Mari kita membuat tabel. Di kolom pertama kita akan memiliki angka asli "X", di kolom kedua - fungsi "EXP", di kolom ketiga - rasio yang ditentukan. Dimungkinkan untuk membuat ekspresi kuadrat, tetapi nilai yang dihasilkan dengan latar belakang eksponen pada grafik praktis akan hilang.

Seperti yang kami katakan sebelumnya, pertumbuhan eksponen jauh lebih cepat daripada persamaan kubik biasa.

Demikian pula, setiap fungsi atau ekspresi matematika dapat direpresentasikan secara grafis.

Semua yang dijelaskan di atas cocok untuk program modern tahun 2007, 2010, 2013 dan 2016. Editor Excel lama jauh lebih rendah dalam hal fitur, jumlah fungsi, dan alat. Jika Anda membuka bantuan resmi dari Microsoft, Anda akan melihat bahwa mereka juga menunjukkan di versi program mana fungsi ini muncul.

Dalam semua hal lainnya, semuanya terlihat hampir persis sama. Sebagai contoh, mari kita hitung jumlah beberapa sel. Untuk ini, Anda perlu:

1. Tentukan beberapa data untuk perhitungan. Klik pada sel mana saja. Klik pada ikon "Fx".

1. Pilih kategori "Matematika". Temukan fungsi "SUM" dan klik "OK".
1. Anda dapat mencoba menghitung ulang di editor lain mana pun. Prosesnya akan persis sama.

Kesimpulan

Dalam tutorial ini, kita berbicara tentang segala sesuatu yang berkaitan dengan rumus di editor Excel, dari yang paling sederhana hingga yang paling rumit. Setiap bagian disertai dengan contoh dan penjelasan terperinci. Ini dilakukan agar informasi dapat diakses bahkan untuk orang bodoh sekalipun.

Jika sesuatu tidak berhasil untuk Anda, maka Anda membuat kesalahan di suatu tempat. Mungkin Anda memiliki kesalahan ketik dalam ekspresi atau referensi sel yang salah. Hal utama yang harus dipahami adalah bahwa segala sesuatu harus didorong dengan sangat hati-hati dan hati-hati. Selain itu, semua fungsi tidak dalam bahasa Inggris, tetapi dalam bahasa Rusia.

Selain itu, penting untuk diingat bahwa rumus harus dimulai dengan simbol "=" (sama dengan). Banyak pengguna pemula melupakannya.

Contoh Berkas

Untuk memudahkan Anda memahami rumus yang dijelaskan sebelumnya, kami telah menyiapkan file demo khusus tempat semua contoh di atas dikompilasi. Anda dapat melakukannya dari situs kami secara gratis. Jika selama pelatihan Anda menggunakan tabel yang sudah jadi dengan rumus berdasarkan data yang Anda isi, Anda akan mencapai hasil lebih cepat.

Instruksi video

Jika uraian kami tidak membantu Anda, coba tonton video terlampir di bawah ini, yang menceritakan poin-poin utama secara lebih rinci. Mungkin Anda melakukan segalanya dengan benar, tetapi Anda kehilangan sesuatu. Dengan bantuan video ini, Anda harus mengatasi semua masalah. Kami berharap tutorial ini telah membantu Anda. Kunjungi kami lebih sering.

Excel menawarkan sejumlah besar fungsi yang dapat digunakan untuk memproses teks. Ruang lingkup fungsi teks tidak terbatas pada teks saja, tetapi juga dapat digunakan dengan sel yang berisi angka. Sebagai bagian dari pelajaran ini, kita akan melihat contoh dari 15 fungsi Excel yang paling umum dari kategori tersebut Teks.

MENGHUBUNG

Untuk menggabungkan isi sel di Excel, bersama dengan operator penggabungan, Anda bisa menggunakan fungsi teks MENGHUBUNG. Ini secara berurutan menggabungkan nilai sel yang ditentukan dalam satu baris.

GARIS BAWAH

Jika Excel perlu membuat semua huruf menjadi huruf kecil, mis. mengubahnya menjadi huruf kecil, fungsi teks akan membantu GARIS BAWAH. Itu tidak menggantikan karakter yang bukan huruf.

MODAL

fungsi teks MODAL membuat semua huruf menjadi kapital, mis. mengubahnya menjadi huruf besar. Sebaik GARIS BAWAH, tidak mengganti karakter yang bukan huruf.

SESUAI

fungsi teks SESUAI mengkapitalisasi huruf pertama dari setiap kata, dan mengonversi semua huruf lainnya menjadi huruf kecil.

Setiap huruf pertama yang mengikuti karakter bukan huruf juga diubah menjadi huruf besar.

DLSTR

Kiri dan kanan

Fungsi teks KIRI Dan BENAR kembalikan jumlah karakter yang diberikan, mulai dari awal atau dari akhir string. Spasi dihitung sebagai karakter.

PSTR

fungsi teks PSTR mengembalikan jumlah karakter yang ditentukan, mulai dari posisi yang ditentukan. Spasi dihitung sebagai karakter.

AKURAT

Fungsi AKURAT memungkinkan Anda untuk membandingkan dua string teks di Excel. Jika sama persis, maka TRUE dikembalikan; jika tidak, FALSE. Fungsi teks ini peka huruf besar-kecil tetapi mengabaikan perbedaan pemformatan.

Jika case tidak memainkan peran besar bagi Anda (hal ini sering terjadi), maka Anda dapat menerapkan rumus yang hanya memeriksa kesetaraan dua sel.

MEMANGKAS

Menghapus semua spasi ekstra dari teks, kecuali spasi tunggal di antara kata.

Dalam kasus di mana keberadaan ruang ekstra di akhir atau awal garis sulit dilacak, fungsi ini menjadi sangat diperlukan. Gambar di bawah ini menunjukkan bahwa isi sel A1 dan B1 persis sama, tetapi tidak demikian. Di sel A1, kami sengaja memberi spasi ekstra di akhir kata unggul. Akibatnya, fungsi AKURAT mengembalikan nilai FALSE.

Dengan menerapkan fungsi MEMANGKAS ke nilai sel A1, kami akan menghapus semua spasi ekstra darinya dan mendapatkan hasil yang benar:

Fungsi MEMANGKAS berguna untuk diterapkan pada data yang diimpor ke lembar kerja Excel dari sumber eksternal. Data semacam itu sangat sering berisi spasi ekstra dan berbagai karakter yang tidak dapat dicetak. Untuk menghapus semua karakter yang tidak dapat dicetak dari teks, Anda harus menggunakan fungsi tersebut MENCETAK.

MENGULANG

Fungsi MENGULANG mengulangi string teks beberapa kali. String diberikan sebagai argumen pertama dari fungsi, dan jumlah pengulangan sebagai yang kedua.

MENEMUKAN

fungsi teks MENEMUKAN menemukan kemunculan satu string di string lain dan mengembalikan posisi karakter pertama frasa pencarian relatif terhadap awal teks.

Fungsi ini peka huruf besar-kecil...

... dan dapat mulai melihat teks dari posisi yang ditentukan. Pada gambar di bawah, rumus mulai memindai dari karakter keempat, yaitu. c huruf “ R“. Namun dalam hal ini pun, posisi karakter dianggap relatif terhadap awal teks yang dilihat.

MENCARI

fungsi teks MENCARI sangat mirip dengan fungsinya MENEMUKAN, perbedaan utama mereka adalah itu MENCARI tidak sensitif huruf.

PENGGANTI

Mengganti teks atau karakter yang ditentukan dengan nilai yang diinginkan. Dalam fungsi teks excel PENGGANTI digunakan jika sudah diketahui sebelumnya teks mana yang perlu diganti, dan bukan lokasinya.

Rumus berikut menggantikan semua kemunculan kata "Excel" dengan "Word":

Hanya mengganti kemunculan pertama kata "Excel":

Menghapus semua spasi dari string teks:

MENGGANTI

Mengganti karakter yang terletak di lokasi yang diketahui dalam sebuah string dengan nilai yang diinginkan. Dalam fungsi teks excel MENGGANTI digunakan ketika diketahui di mana teks itu berada, sementara teks itu sendiri tidak penting.

Rumus pada contoh di bawah menggantikan 4 karakter dimulai dari posisi ketujuh dengan nilai “2013”. Sebagai contoh, rumus akan mengganti "2010" dengan "2013".

Menggantikan lima karakter pertama dari string teks, mis. kata "Excel" menjadi "Word".

Itu saja! Kami berkenalan dengan 15 fungsi teks Microsoft Excel dan melihat aksinya pada contoh sederhana. Saya harap pelajaran ini berguna bagi Anda, dan Anda mendapatkan setidaknya sedikit informasi berguna darinya. Selamat mencoba dan sukses dalam belajar Excel!

Tak jarang, saat bekerja di Excel, ada kebutuhan untuk menyisipkan teks penjelasan di samping hasil penghitungan rumus, yang memudahkan untuk memahami data ini. Tentu saja, Anda dapat memilih kolom terpisah untuk penjelasan, tetapi tidak dalam semua kasus, menambahkan elemen tambahan itu rasional. Namun, di Excel ada cara untuk menyatukan rumus dan teks dalam sel yang sama. Mari kita lihat bagaimana ini bisa dilakukan dengan menggunakan berbagai opsi.

Jika Anda hanya mencoba memasukkan teks ke dalam satu sel dengan suatu fungsi, maka dengan upaya seperti itu, Excel akan menampilkan pesan kesalahan dalam rumus dan tidak akan mengizinkan Anda memasukkannya. Namun ada dua cara untuk tetap menyisipkan teks di samping ekspresi rumus. Yang pertama menggunakan ampersand, dan yang kedua menggunakan fungsi MENGHUBUNG.

Metode 1: Menggunakan ampersand

Cara termudah untuk mengatasi masalah ini adalah dengan menggunakan simbol ampersand ( & ). Tanda ini menghasilkan pemisahan logis dari data yang berisi rumus dari ekspresi teks. Mari kita lihat bagaimana metode ini dapat diterapkan dalam praktik.

Kami memiliki tabel kecil di mana biaya tetap dan variabel perusahaan ditunjukkan dalam dua kolom. Kolom ketiga berisi rumus penjumlahan sederhana yang merangkumnya dan menghasilkan total keseluruhan. Kita perlu menambahkan kata penjelas setelah rumus di sel yang sama tempat total biaya ditampilkan "rubel".

Secara alami, tidak perlu melakukan semua langkah ini. Kami baru saja menunjukkan bahwa dengan input normal tanpa ampersand kedua dan tanda kutip dengan spasi, rumus dan data teks akan digabungkan. Anda dapat menyetel ruang yang benar bahkan saat menjalankan paragraf kedua dari panduan ini.

Saat menulis teks sebelum rumus, kami mengikuti sintaks berikut. Segera setelah tanda "=", buka tanda kutip dan tulis teksnya. Setelah itu, tutup tanda kutip. Kami memasang tanda ampersand. Kemudian, jika Anda perlu memasukkan spasi, buka tanda kutip, beri spasi, dan tutup tanda kutip. Kami mengklik tombolnya Memasuki.

Untuk menulis teks bersama dengan fungsi, dan bukan dengan rumus biasa, semua langkahnya persis sama seperti yang dijelaskan di atas.

Metode 2: Menggunakan Fungsi CONCATENATE

Anda juga dapat menggunakan fungsi untuk menyisipkan teks beserta hasil perhitungan rumusnya MENGHUBUNG. Operator ini dirancang untuk menggabungkan dalam satu sel nilai yang ditampilkan di beberapa elemen lembar. Itu termasuk dalam kategori fungsi teks. Sintaksnya adalah sebagai berikut:

CONCATENATE(teks1, teks2, ...)

Secara total, operator ini dapat memiliki dari 1 sebelum 255 argumen. Masing-masing mewakili teks (termasuk angka dan karakter lainnya) atau tautan ke sel yang memuatnya.

Mari kita lihat bagaimana fungsi ini bekerja dalam praktiknya. Misalnya, mari kita ambil tabel yang sama, tambahkan saja satu kolom lagi ke dalamnya "Total biaya" dengan sel kosong.

1. Pilih sel kolom kosong "Total biaya". Klik pada ikon "Masukkan Fungsi" terletak di sebelah kiri bilah rumus.



2. Aktivasi sedang berlangsung Penyihir Fungsi. Pindah ke kategori "Teks". Selanjutnya, pilih nama "MENGHUBUNG" dan klik tombolnya OKE.



3. Jendela Argumen Operator diluncurkan MENGHUBUNG. Jendela ini terdiri dari bidang bernama "Teks". Jumlah mereka mencapai 255 , tetapi untuk contoh kita, kita hanya membutuhkan tiga bidang. Yang pertama kita akan menempatkan teks, yang kedua kita akan menempatkan tautan ke sel yang berisi rumus, dan yang ketiga kita akan menempatkan teks lagi.

   Atur kursor di lapangan "Teks 1". Masukkan kata "Total". Anda dapat menulis ekspresi teks tanpa tanda kutip, karena program akan meletakkannya sendiri.

   Lalu kita pergi ke lapangan "Teks2". Atur kursor di sana. Di sini kita perlu menunjukkan nilai yang ditampilkan rumus, yang berarti kita harus memberikan tautan ke sel yang memuatnya. Ini dapat dilakukan hanya dengan memasukkan alamat secara manual, tetapi lebih baik letakkan kursor di bidang dan klik sel yang berisi rumus di lembar. Alamat akan ditampilkan di kotak argumen secara otomatis.

   Di lapangan "Teks3" masukkan kata "rubel".

   Setelah itu, klik tombolnya OKE.



4. Hasilnya ditampilkan dalam sel yang telah dipilih sebelumnya, tetapi, seperti yang dapat kita lihat, seperti pada metode sebelumnya, semua nilai ditulis bersama tanpa spasi.



5. Untuk mengatasi masalah ini, pilih kembali sel yang berisi operator MENGHUBUNG dan buka bilah rumus. Di sana, setelah setiap argumen, yaitu setelah setiap titik koma, tambahkan ekspresi berikut:

   Harus ada spasi di antara tanda kutip. Secara umum, ekspresi berikut akan muncul di baris fungsi:

   CONCATENATE("Jumlah";" ";D2;" ";"rubel")

   Kami mengklik tombolnya MEMASUKI. Sekarang nilai kita dipisahkan oleh spasi.



6. Anda dapat menyembunyikan kolom pertama jika Anda mau. "Total biaya" dengan formula aslinya sehingga tidak memakan tempat ekstra pada lembarannya. Menghapusnya saja tidak akan berhasil, karena ini akan merusak fungsinya MENGHUBUNG, tetapi sangat mungkin untuk menghapus elemen tersebut. Klik tombol kiri mouse pada sektor bilah koordinat kolom yang harus disembunyikan. Setelah itu, seluruh kolom disorot. Klik pada pilihan dengan tombol kanan mouse. Menu konteks diluncurkan. Pilih item di dalamnya. "Bersembunyi".



7. Setelah itu, seperti yang kita lihat, kolom yang tidak kita butuhkan disembunyikan, tetapi data di sel tempat fungsi itu berada MENGHUBUNG ditampilkan dengan benar.

Excel menawarkan penggunanya sebanyak 3 fungsi untuk bekerja dengan huruf besar dan kecil dalam teks (huruf besar dan kecil). Fungsi teks ini membuat huruf menjadi besar dan kecil, atau hanya mengubah huruf pertama dalam sebuah kata menjadi kapital.

Rumus dengan Fungsi Teks Excel

Pertama, mari kita ambil contoh 3 fungsi teks Excel:

1. UPPER - Fungsi teks ini mengubah semua huruf dalam kata menjadi huruf besar, huruf besar.
2. LOWER - Fungsi ini mengonversi semua karakter teks menjadi huruf kecil, huruf kecil.
3. PROPER - fungsinya hanya mengubah huruf pertama di setiap kata menjadi kapital, besar.

Seperti yang Anda lihat pada contoh di gambar, fungsi ini dalam argumennya tidak memerlukan apa pun selain data teks asli, yang harus diubah sesuai dengan kebutuhan pengguna.

Terlepas dari berbagai fungsi yang begitu luas, Excel masih membutuhkan fungsi yang dapat mengkapitalisasi huruf pertama hanya untuk kata pertama dalam kalimat, dan tidak di setiap kata. Namun, untuk mengatasi masalah ini, Anda dapat membuat rumus kustom Anda sendiri menggunakan fungsi teks Excel yang sama dan lainnya:

Untuk mengatasi masalah populer ini, Anda perlu menggunakan fungsi teks Excel tambahan dalam rumus: KIRI, KANAN, dan PANJANG.

Prinsip rumus mengganti huruf pertama dalam sebuah kalimat

Jika Anda mencermati sintaks rumus di atas, mudah untuk menggantinya yang terdiri dari dua bagian yang dihubungkan oleh operator &.

Sisi kiri rumus menggunakan fungsi LEFT tambahan:

Tugas dari bagian rumus ini adalah mengubah huruf pertama menjadi huruf kapital di string teks asli sel A1. Berkat fungsi LEFT, Anda bisa mendapatkan sejumlah karakter mulai dari sisi kiri teks. Fungsi membutuhkan 2 argumen untuk diisi:

1. Teks - tautan ke sel dengan teks asli.
2. Number of_characters - jumlah karakter yang dikembalikan dari sisi kiri (dari awal) teks sumber.

Dalam contoh ini, Anda hanya perlu mendapatkan 1 karakter pertama dari string teks asli di sel A1. Karakter yang dihasilkan kemudian diubah menjadi huruf kapital besar.

Sisi kanan rumus setelah operator & pada prinsipnya sangat mirip dengan sisi kiri, hanya saja ini menyelesaikan masalah yang berbeda. Tugasnya adalah mengubah semua karakter teks menjadi huruf kecil. Tetapi Anda perlu melakukan ini agar tidak mengubah huruf kapital pertama, yang menjadi tanggung jawab sisi kiri rumus. Alih-alih fungsi LEFT di sisi kanan rumus, fungsi RIGHT digunakan:

Fungsi teks RIGHT bekerja terbalik dengan fungsi LEFT. Itu juga membutuhkan debu dua argumen: teks sumber dan jumlah karakter. Tapi itu mengembalikan sejumlah surat yang diterima dari sisi kanan teks sumber. Namun, dalam kasus ini, kami tidak dapat menentukan nilai tetap sebagai argumen kedua. Lagi pula, kita tidak mengetahui terlebih dahulu jumlah karakter dalam teks sumber. Selain itu, panjang string teks sumber yang berbeda mungkin berbeda. Oleh karena itu, kita perlu menghitung panjang baris teks terlebih dahulu dan mengurangi -1 dari nilai numerik yang dihasilkan agar tidak mengubah huruf kapital pertama di baris tersebut. Lagi pula, huruf pertama diproses di sisi kiri rumus dan telah diubah sesuai kebutuhan pengguna. Oleh karena itu, tidak boleh terpengaruh oleh fungsi apa pun dari sisi kanan rumus.

Untuk menghitung panjang teks sumber secara otomatis, fungsi teks Excel - DLSTR (diuraikan sebagai panjang string) digunakan. Fungsi ini hanya membutuhkan satu argumen untuk diisi - tautan ke teks sumber. Sebagai hasil perhitungan, ia mengembalikan nilai numerik, setelah fungsi =LSTR(A1) kita kurangi -1. Yang memberi kita kesempatan untuk tidak mempengaruhi huruf kapital pertama dengan sisi kanan rumus. Hasilnya, fungsi RIGHT mengembalikan string teks tanpa satu karakter pertama untuk fungsi LOWER, yang mengubah semua karakter teks menjadi huruf kecil kecil.

Sebagai hasil dari penggabungan kedua bagian rumus dengan operator &, kami mendapatkan kalimat teks yang indah, yang menurut aturan dimulai dengan huruf kapital pertama. Dan semua huruf lainnya kecil sampai akhir kalimat. Terlepas dari panjang teksnya, dengan menggunakan rumus yang sama, kami mendapatkan hasil yang benar.