Pada artikel sebelumnya Anda telah mengenal beberapa rumus Excel untuk kerja. Selanjutnya mari kita selami rumus Excel tingkat lanjut. Ini merupakan kumpulan fungsi Excel yang jarang terjamah manusia. Eh, mungkin tidak juga sih hehe. Namun, yang jelas rumus Excel berikut hanya dikuasai oleh sedikit orang.
Bagi Anda yang penasaran maka dapat menyimak pembahasan berikut sampai tuntas. Jangan lupa untuk mempraktikkan langkah-langkah penggunaan rumus berikut agar Anda dapat memahami cara kerjanya.
Praktik membantu Anda agar terbiasa untuk menggunakan rumus tersebut. Selain itu, ini adalah kesempatan bagi Anda mencari pendekatan-pendekatan lain yang bisa ditempuh menggunakan rumus tertentu. Mudahnya, Anda bisa bereksperimen dan menemukan cara kreatif sendiri!
Well then, inilah beberapa rumus Excel tingkat lanjut yang dapat Anda gunakan untuk berbagai kebutuhan olah data maupun sortir data.
Rumus Excel Tingkat Lanjut Agar Urusan Olah Data Semakin Efektif
HLOOKUP
Fungsi HLOOKUP adalah fungsi yang bisa Anda gunakan untuk mencari data secara horizontal.
Rumus HLOOKUP
=HLOOKUP(nilai lookup; rentang tabel, nomor baris; TRUE/FALSE)
Penggunaan Rumus Excel Tingkat Lanjut – HLOOKUP
Misalnya, Anda memiliki tabel seperti berikut ini.
Untuk mencari berapa jumlah alat pel yang dimiliki kelas X, maka Anda bisa memasukkan fungsi HLOOKUP berikut pada sel B7.
=HLOOKUP(C2; A2:E5; 2; TRUE)
Mari bedah fungsi di atas.
=HLOOKUP menerima beberapa nilai, yakni nilai lookup, rentang tabel, nomor baris dan TRUE atau FALSE.
Perhatikan kembali fungsi HLOOKUP yang Anda masukkan. C2 merujuk pada nilai lookup, yakni Alat Pel. Kemudian, A2:E5 merupakan rentang tabel. Seperti yang Anda ketahui, tabel tersebut membentang mulai dari A2 hingga E5 bukan?
Adapun, 2 merujuk pada nomor baris pada tabel tersebut. Perhatikan bahwa Anda ingin mencari jumlah alat pel yang dimiliki kelas X. Sementara, kelas X berada di baris ke – 2 maka baris itulah yang Anda masukkan dalam fungsi HLOOKUP.
Selanjutnya nilai TRUE atau FALSE. Tidak perlu bingung, TRUE menghasilkan pencarian nilai yang tidak identik. Dengan demikian, mirip saja cukup. Adapun, FALSE menghasilkan pencarian nilai yang identik atau sama persis (exact match).
Dalam kasus ini, Anda bebas memilih TRUE ataupun FALSE karena fungsi HLOOKUP tetap mengembalikan hasil yang sama.
Hasil yang muncul ketika Anda menggunakan nilai TRUE lalu menekan Enter adalah 2.
Hasilnya sesuai ya? Jumlah alat pel kelas X memang 2 buah. Selanjutnya mari gunakan nilai FALSE untuk kelas XI. Jangan lupa, masukkan angka 3 pada nilai baris karena kelas XI berada di baris ke-3.
Fungsi HLOOKUP pun mengembalikan nilai 2. Ternyata hasilnya juga sesuai. Jumlah alat pel kelas XI memang 2 buah.
Berikutnya, Anda bisa terapkan fungsi HLOOKUP pada kolom B9 seperti berikut.
Hasilnya, fungsi HLOOKUP menampilkan nilai 1. Bagaimana, sesuai kan?
VLOOKUP
Selain HLOOKUP, ada pula fungsi VLOOKUP yang berfungsi untuk menemukan data secara vertikal.
Rumus VLOOKUP
=VLOOKUP(nilai lookup; rentang tabel; nomor kolom; TRUE/FALSE)
Penggunaan Rumus Excel Tingkat Lanjut – VLOOKUP
Misalnya Anda memiliki tabel berikut.
Untuk mengisi data gaji Joko, Bio dan Mono maka Anda bisa menggunakan fungsi VLOOKUP. Berikut caranya.
Pada sel C7 masukkan fungsi VLOOKUP berikut ini.
=VLOOKUP(B7; A1:C4; 2;TRUE)
Mari bedah fungsi VLOOKUP di atas.
VLOOKUP menerima beberapa nilai, diantaranya adalah nilai lookup, rentang tabel, nomor kolom dan nilai TRUE atau FALSE. Untuk nilai lookup, rentang tabel serta TRUE dan FALSE, prinsipnya sama seperti penjelasan pada fungsi HLOOKUP.
Adapun yang membedakan adalah nomor kolom. Nomor kolom ini merupakan nomor kolom dalam tabel Anda.
Setelah memasukkan fungsi VLOOKUP tersebut maka Anda akan melihat gaji Joko tampil di layar, yakni sebesar Rp. 3.000.000.
Selanjutnya, Anda bisa memasukkan fungsi VLOOKUP di sel C8.
=VLOOKUP(B8; A1:C4; 2;TRUE )
Maka, gaji Bio akan muncul sesuai golongannya, yakni sebesar Rp. 5.000.000.
Selanjutnya, Anda bisa masukkan fungsi VLOOKUP di sel C9. Tetapi, kali ini tidak menggunakan cara biasa, melainkan cara berikut.
=VLOOKUP([@Golongan];Table1[#All];2;TRUE)
Hasilnya yang muncul tetap akurat. Hal ini membuktikan bahwa Anda bisa menggunakan sekurangnya 2 cara berbeda untuk menuliskan fungsi VLOOKUP. Jika Anda mau, hal ini juga bisa diterapkan untuk fungsi HLOOKUP lho.
PROPER
Selain fungsi HLOOKUP dan VLOOKUP, ada juga fungsi PROPER. Anda dapat menggunakan fungsi PROPER untuk mengubah huruf pertama pada teks ke dalam huruf kapital, misalnya mengubah “baki membaca buku” menjadi “Baki Membaca Buku”.
Rumus PROPER
=PROPER(sel)
Penggunaan Rumus Excel Tingkat Lanjut – PROPER
Misalnya Anda mengetik beberapa nama dalam huruf kecil di sel A2-A4, maka Anda bisa mengubah setiap awalan kata menjadi huruf kapital menggunakan fungsi PROPER.
Ketikkan fungsi =PROPER(A2) untuk mengubah setiap awalan kata yang tercetak di sel A2 ke dalam huruf kapital. Hasilnya nanti akan tampil di sel B2. Lakukan hal yang sama untuk mengubah data di sel A3 dan A4.
Cara selanjutnya adalah mengganti nama sel menjadi nama header. Tabel ini memiliki 2 header, yakni Nama Lengkap dan Aplikasi Fungsi PROPER. Dalam hal ini, header yang digunakan adalah Nama Lengkap. Contohnya sebagai berikut.
Anda bebas memilih cara manapun hasilnya seperti berikut ini.
SQRT
SQRT berfungsi untuk menghasilkan nilai akar dari suatu bilangan. Misalnya, pada kolom A2 tersimpan angka 25. Maka, Anda bisa menggunakan fungsi SQRT untuk mencari akar dari nilai yang tersimpan di kolom A2 tersebut.
Rumus SQRT
=SQRT(sel/angka)
Penggunaan Rumus Excel Tingkat Lanjut – SQRT
Misalnya Anda memiliki daftar beberapa nilai seperti tabel berikut. Anda dapat mencari nilai akar dari nilai tersebut menggunakan fungsi SQRT.
Sebelumnya, perlu dicatat terlebih dulu bahwa fungsi SQRT menerima input berupa sel maupun bilangan. Jadi Anda bisa menuliskan =SQRT(B8) maupun =SQRT(100).
Melanjutkan tabel di atas, Anda bisa menuliskan fungsi SQRT seperti berikut.
=SQRT(A12)
Anda bisa masukkan fungsi tersebut pada kolom B12 seperti berikut.
Selanjutnya, Anda akan memperoleh hasil seperti ini.
Selanjutnya terapkan fungsi tersebut pada kolom di bawahnya. Tetapi, jangan lupa untuk menyesuaikan nama selnya, yakni =SQRT(A13) untuk kolom B13, =SQRT(A14) untuk kolom (B14) dan seterusnya.
Maka, hasilnya seperti berikut ini.
COUNTIF
Jika Anda membutuhkan fungsi yang dapat menghitung jumlah sel dengan kriteria yang sama, maka Fungsi COUNTIF adalah pilihannya. Fungsi ini dapat menghitung jumlah sel yang seragam. Hal ini sangat bagus untuk mendukung kebutuhan sortir data.
Rumus COUNTIF
=COUNTIF(rentang; kriteria)
Penggunaan Rumus Excel Tingkat Lanjut – COUNTIF
Misalnya, Anda memiliki data sebagai berikut.
Jika ingin menghitung berapa banyak orang yang lebih menyukai Javascript, maka Anda bisa menggunakan fungsi COUNTIF. Berikut rumus COUNTIF yang dapat Anda masukkan ke dalam kolom B33.
=COUNTIF(A18:B31;”Javascript”)
Maka hasilnya adalah 6.
CEILING
Anda bisa menggunakan fungsi CEILING untuk membulatkan bilangan pada kelipatan sepuluh ke bilangan atas terdekat.
Rumus CEILING
=CEILING(sel/angka; signifikan)
Maksud signifikan adalah satuan pembulatan, misalnya pembulatan ke puluhan terdekat, ratusan terdekat, ribuan terdekat dan sebagainya.
Penggunaan Rumus Excel Tingkat Lanjut – CEILING
Misalnya Anda memiliki tabel berikut.
Lalu, Anda dapat membulatkan harga item menggunakan rumus CEILING berikut. (Tuliskan pada kolom C18)
=CEILING(B18; 10)
Maka, pada kolom C18 muncul hasil berikut.
Selanjutnya masukkan fungsi CEILING pada kolom C19 dan C20 maka hasilnya seperti berikut ini.
CONCATENATE
Selanjutnya ada fungsi CONCATENATE yang bisa Anda gunakan untuk menggabungkan data. Misalnya Anda ingin menggabungkan data pada kolom C1 dan C2, dan sebagainya.
Rumus CONCATENATE
=CONCATENATE(sel 1:sel 2)
Penggunaan Rumus Excel Tingkat Lanjut – CONCATENATE
Pada tabel berikut, Anda bisa menggabungkan nilai pada kolom item dengan kolom warna.
Coba masukkan rumus berikut pada kolom C18.
=CONCATENATE(A18;B18)
Maka, hasilnya seperti berikut ini.
Selanjutnya terapkan fungsi CONCATENATE pada kolom lainnya maka Anda akan memperoleh hasil seperti berikut ini.
POWER
Anda bisa menggunakan fungsi POWER untuk menghasilkan bilangan pangkat n. Anda dapat mengetahui nilai dari 9 pangkat 10 dan sebagainya.
Rumus POWER
=POWER(sel/bilangan;sel/pangkat)
Penggunaan Rumus Excel Tingkat Lanjut – POWER
Misalnya terdapat 2 kolom masing-masing berisi bilangan dan pangkat. Maka, Anda bisa mendapatkan hasil pangkatnya menggunakan fungsi POWER. Masukkan rumus berikut.
=POWER(A18;B18)
Maka, Anda akan memperoleh hasil berikut.
Coba terapkan pada kolom selanjutnya maka hasilnya adalah sebagai berikut.
Fungsi POWER dapat menghemat waktu bukan? Anda tidak perlu menghitung hasil tersebut secara manual. Cukup masukkan rumus dan tekan Enter, maka hasilnya langsung tersedia.
PRODUCT
Meski namanya PRODUCT, tetapi fungsi ini bukan untuk mencari produk. Fungsi PRODUCT bertugas untuk melakukan perkalian pada data yang terdapat pada suatu tabel.
Rumus PRODUCT
=PRODUCT(sel/bilangan; sel/bilangan2, …)
Tanda titik-titik (…) memiliki arti “dan seterusnya”.
Penggunaan Rumus Excel Tingkat Lanjut – PRODUCT
Misalnya Anda memiliki 2 kolom yang terdiri dari bilangan 1 dan bilangan 2. Lalu, Anda ingin mengkalikan kedua bilangan tersebut. Maka, Anda dapat menggunakan fungsi PRODUCT untuk mempermudah pekerjaan.
Masukkan rumus berikut pada kolom C18 di dalam tabel ini.
=PRODUCT(A18;B18)
Maka, hasilnya seperti berikut ini.
Anda bisa terapkan rumus di atas untuk kolom lainnya untuk mendapatkan hasil perkalian bilangan lain. Tetapi, sesuaikan dulu nama selnya ya.
COLUMN
Fungsi COLUMN bermanfaat untuk mencari informasi berupa nomor kolom tempat Anda meletakkan nilai. Jadi, Anda bisa menggunakan fungsi ini untuk mencari tahu di kolom ke-berapakah data tertentu berada.
Rumus COLUMN
=COLUMN(referensi)
Referensi dalam bentuk nama sel
Penggunaan Rumus Excel Tingkat Lanjut – COLUMN
Misalkan terdapat data seperti berikut ini.
Anda dapat menggunakan fungsi COLUMN untuk mengetahui nomor kolom tempat data tersebut berada. Cukup masukkan rumusnya, misal Anda ingin meneliti kolom tempat Fulan17 berada maka rumusnya:
=COLUMN(C5)
Maka hasilnya pun seperti berikut ini.
Yup, sesuai dengan yang terlihat. Fulan17 berada di kolom C yang merupakan kolom ke – 3 dalam data tersebut. Melalui cara ini Anda dapat mengetahui tujuan perjalanan Fulan17, yakni Yogyakarta.
Itulah beberapa contoh rumus Excel tingkat lanjut yang bisa Anda pelajari untuk mempermudah kegiatan olah data. Tak hanya meringkas pekerjaan, namun rumus-rumus di atas juga dapat Anda gunakan untuk meminimalisir kesalahan.
Jika artikel ini bermanfaat, silakan simpan dan tinjau kembali saat Anda memerlukannya. Selain itu, Anda juga dapat membagikan artikel ini ke media sosial untuk membantu lebih banyak orang. Semoga bermanfaat ya!
