Catatan Belajar Rumus Excel / Libre Calc / Google Sheet

JagoTekno.com - Di sini saya ingin mencatat berbagai rumus excel sederhana yang bermanfaat untuk pekerjaan saya.

Rumus Excel menggabungkan 2 cell kemudian membuatnya secara berurutan pada satu cell

Misalnya saya punya daftar nama di kolom A1. Kemudian pada kolom B berisi data nama yang merupakan qq dari kolom A.

Kemudian pada kolom C saya ingin menggabungkan kedua baris (row) tersebut tetapi bagian qq berada di bawah nama (Kolom A).

Maka rumusnya menjadi seperti ini.

=A1&(CHAR(10)&"qq : "&B1)

Di sini kita hanya menggunakan fungsi char(10)

Char(10) sendiri maksudnya memberikan baris baru pada satu cell. Sama seperti ketika mengetik manual dengan bantuan tombol ALT+Enter

Maka setelah rumus tersebut dimasukkan maka hasilnya akan menjadi seperti ini.

rumus excel menggabungkan 2 data ke satu cell dengan baris baru

Menampilkan data qq dari Sheet1 di Sheet lain menggunakan Google Sheet

Selanjutnya mari kita modifikasi data di atas sehingga menjadi lebih keren ditampilkannya.

Misalnya saya punya data di Sheet1 seperti ini :

Sheet1

Kemudian saya ingin menampilkannya di Sheet2 sebagai laporan.

Setiap nama pada kolom qq dipisahkan dengan tanda titik dua ;.

Tantangannya adalah menampilkan data kolom qq secara berurutan langsung pada 1 cell.

Sheet2

Cantik sekali bukan?

Ini rumusnya, silahkan disesuaikan saja.

=Sheet1!E2 & if(Sheet1!F2="", "", char(10) & char(10) & "qq :  -  " & SUBSTITUTE(Sheet1!F2,";",char(10) & "        - " ))

Rumus Excel menjumlahkan data jika menemukan kondisi tertentu

Kemudian jika saya ingin menjumlahkan cell tertentu apabila menemukan kondisi tertentu pada cell lain.

Untuk lebih jelasnya, pelajari saja gambar berikut :

rumus excel menambahkan data jika menemukan kondisi tertentu pada cell lain.

Pada gambar di atas, rumus itu hanya akan berfungsi apabila terdapat kata ikan pada kolom B.

Copy paste dari kolom ke baris Excel

Jika anda menemukan banyak baris data kemudian ingin diubah menjadi kolom, maka caranya di excel adalah dengan melakukan paste Transpose.

Paste transpose bisa dilakukan dari kolom ke baris maupun sebailknya, baris ke kolom.

Mengubah Text menjadi Kolom Libre Office

Mungkin anda bekerja dengan data yang sangat banyak pada satu kolom tertentu. Setiap cell terdiri dari text yang panjang, lalu anda ingin mengubah isi cell tersebut terpisah ke dalam tiap-tiap kolom.

Caranya sangat mudah, gunakan saja fitur Text to column di LibreOffice.

Pertama select cell yang diinginkan pada contoh di atas Cell B1.

Kemudian klik menu Data > Text to Column

Kemudian ubah pengaturan nya sesuai dengan data anda. Di sini saya ingin setiap kata terpisah jika menemukan semicolon/ titik dua.

Klik OK, maka data pada cell tersebut berhasil diextract ke kolom.

Find and replace karakter tertentu menjadi baris baru (Excel only)

Sama seperti di atas, jika kita bekerja dengan banyak data yang tergabung ke dalam satu baris saja, kemudian kita ingin memisahkannya ke dalam baris baru. Data tidak akan pindah ke cell lain.

Kita bisa melakukan fitur find and replace. Kemudian mengganti semua karakter semicolon (titk dua) menjadi baris baru.

Perintah untuk melakukan baris baru di excel adalah char(10) atau ALT+Enter

Untuk mengaplikasnnya ke Find and replace, cukup isi bagian Replace dengan mengetik Ctrl+J.

Fungsi Replace karakter menjadi baris baru dengan Ctrl+J hanya berlaku di Microsoft Excel.

Otomatis menambahkan karakter tentu setelah menginput angka di cell

Misalnya saya ingin menambahkan tulisan M2 (Meter persegi) pada cell tertentu.

Sehingga saya tidak perlu selalu mengetik angka dan diikuti m2.

Caranya mudah, disini kita melakukan format cell.

Klik kanan cell tertentu atau lakukan seleksi untuk keseluruhan cell lalu pilih Format Cell.

Kemudian pada format code sesuaikan seperti ini

#,##0 [$M2]

Menambahkan meter persegi (m2) secara otomatis pada setiap cell tertentu.

Jadi selanjutnya saya hanya perlu input nomor saja.

Cara lain menambahkan karakter setelah input angka

Metodenya adalah dengan dengan melakukan copy paste ke kolom yang berbeda kemudian menambahkan & lalu diikugi karakter yang diinginkan. Lihat gambar.

Menampilkan nomor urut otomatis

Biasanya kita membuat nomor urut pada excel dengan cara ini :

=A1+1

Ada cara lain, ketik saja :

=ROW(A1)

Rumus nomor urut otomatis angka romawi

=ROMAN(ROW(A1))