Rumus yang digunakan untuk mencari jumlah total adalah

Microsoft Office Excel telah menyediakan begitu banyak fungsi-fungsi default yang bisa kita manfaatkan untuk berbagai keperluan. Jika Microsoft Excel memiliki rumus untuk menghitung data yang mengandung data yang sama (double/duplikat), bagaimana dengan rumus Excel yang digunakan untuk menghitung jumlah data yang berbeda (count unique value excel)? Menghitung jumlah data unik berarti mencacah banyak data, namun jika pada data tersebut memiliki duplikat hanya dihitung satu kali saja. Data unik ini bisa saja berupa teks maupun angka, juga campuran dari keduanya.

Untuk menghitung data unik dengan cara yang akan DQLab bahas kali ini adalah bagaimana mengkolaborasikan beberapa fungsi yang bisa dimanfaatkan di Microsoft Excel. Fungsi yang akan digunakan adalah SUM, COUNTIF, dan SUMPRODUCT. Untuk definisi dan fungsi dari masing-masing rumus tersebut bisa kamu baca pada artikel DQLab sebelum ini. Pada artikel kali ini, kita akan mencoba mengkolaborasikan atau menggabungkan beberapa rumus di atas untuk menyelesaikan sebuah permasalahan yang bisa dipecahkan menggunakan Microsoft Excel. Simak pembahasan berikut ini, ya!

1. Menghitung Jumlah Data yang Berbeda/Unik

Berikut ini adalah beberapa teknik menggunakan fungsi-fungsi yang telah disebutkan tadi. Pada contoh kasus, kita akan menggunakan data penjualan produk seperti pada tabel di atas. Untuk kriteria pertama ini, kita akan menghitung data unik menggunakan gabungan fungsi SUM dan COUNTIF seperti berikut:

Syntax: =SUM(1/COUNTIF(RangeData; Range Data)), 

dan diakhiri dengan CTRL+SHIFT+ENTER (Array formula).

Misalnya kita akan menghitung berapa jenis buah yang dijual pada data tersebut, maka caranya adalah,

Syntax: {=SUM(1/COUNTIF(B2:B21; B2:B21))}

Baca juga : Rumus Excel yang Paling Sering Digunakan dalam Dunia Kerja

2. Menghitung Jumlah Data yang Berbeda dengan Kriteria Tertentu

Atau contoh lainnya, kita akan menghitung produk unik yang dijual oleh Tom, caranya adalah,

Syntax:{=SUM(IF("Tom"=$D$2:$D$21;1/(COUNTIFS($D$2:$D$21;"Tom";$B$2:$B$21;$B$2:$B$21));0))}

Artinya, berdasarkan data tersebut, Tom menjual 5 Jenis Buah yaitu Apple, Banana, Grape, Peach, dan Mango.

3. Menghitung Jumlah Data yang Berbeda dan Mengandung Cell Kosong

Jika pada range data yang kita hitung nilai uniknya terdapat sel kosong (blank cells). Maka solusinya adalah dengan menambahkan (&"") pada kriteria fungsi COUNTIF, sehingga rumus Excelnya menjadi:

Syntax: =SUM(1/COUNTIF(RangeData;RangeData&""))

dan diakhiri dengan CTRL+SHIFT+ENTER (Array formula).

Contoh kasusnya seperti berikut,

Syntax: {=SUM(1/COUNTIF(B2:B21;B2:B21&""))}

Jika dicermati, ternyata dengan menggunakan rumus Excel diatas, sel yang kosong (Blank Cells) juga ikut terhitung. Data unik pada contoh diatas seharusnya 7 (Apple, Peach, Orange, Banana, Grape, Melon, dan Cherry), namun hasil rumus diatas 8 sebab sel kosong juga ikut terhitung sehingga plus 1 menjadi 8 Jenis.

4. Menghitung Jumlah Data Berbeda dan Mengabaikan Cell Kosong

Jika tujuannya adalah untuk mengabaikan sel kosong (Blank Cells), maka kamu bisa menggunakan rumus Excel berikut dibawah ini,

Syntax: =SUM((RangeData<>"")/COUNTIF(RangeData;RangeData&""))

dan diakhiri dengan CTRL+SHIFT+ENTER (Array formula).

Contoh penerapannya seperti yang terlihat dalam gambar berikut:

Seperti yang terlihat pada gambar diatas dengan menggunakan rumus array:

Syntax: {=SUM((B2:B21<>"")/COUNTIF(B2:B21;B2:B21&""))}

Kita bisa menghitung jumlah nilai unik dari sebuah range data dan mengabaikan sel yang kosong (sel kosong tidak dihitung) dan menghasilkan 7 jenis produk.

Baca juga : Kenali Rumus IF pada 3 Kondisi dengan Excel

5. Terapkan Fungsi Excel Lainnya Bersama DQLab

Pengolahan data adalah skill wajib untuk menjadi praktisi data. Berbagai tools dapat menjadi pilihan untuk mengolah hingga menyajikan data dalam bentuk yang menarik dan mudah dipahami. Salah satunya yaitu Excel yang menyediakan berbagai fitur dan fungsi untuk aktivitas tersebut. Yuk, eksplorasi Excel dan tingkatkan kemampuan dalam mengolah data dengan Excel bareng DQLab! Dengan modul yang menarik dan mentor profesional akan membantu meningkatkan skillmu. Daftar di DQLab.id untuk mulai belajar Excel.

Penulis: Salsabila Miftah Rezkia

Editor: Annissa Widya Davita

Bagi sebagian orang yang tidak biasa menggunakan Microsoft Excel, menjadi kesulitan tersendiri saat harus bekerja menggunakan perangkat ini. Padahal jika kamu tahu rumus Microsoft Excel sebenarnya ini bisa membantu pekerjaan menjadi lebih mudah terutama dalam mengolah data atau angka.

Rumus Microsoft Excel adalah fungsi yang akan membantu mengidentifikasi hubungan antara nilai dan sel di dalam spreadsheet, melakukan penghitungan matematis menggunakan nilai tersebut dan mengembalikan nilai yang dihasilkan dalam sel pilihanmu.  Beberapa rumus yang bisa kamu lakukan melalui Ms Excel adalah pembagian, pengalian, penjumlahan, pengurangan, persentase, menghitung rata-rata, untuk memasukkan tanggal atau waktu dan sebagainya. Seperti apa bentuk rumus Microsoft Excel tersebut? Simak jawabannya di sini.

Baca juga: Meningkatkan produktivitas kerja pada 2020 dengan deretan tools ini

Pahami dahulu dasar-dasar fungsi Microsoft Excel

 
Salah satu fungsi dasar excel adalah untuk membuat spreadsheet - EKRUT

Sebelum membahas lebih spesifik tentang rumus Microsoft Excel yang bisa membantu pekerjaanmu, ada baiknya kamu tahu lebih dulu dasar-dasar fungsi dari Microsoft Excel itu sendiri, di antaranya:

Baca juga: 9 Data analytics tools terbaik sesuai kebutuhanm

20 Rumus Microsoft Excel yang paling umum

 
Rumus Microsoft Excel juga sering disebut sebagai function yang kemudian tinggal disisipi rumus - EKRUT

Jika kamu kebingungan menggunakan Microsoft Excel atau tidak mengetahui sama sekali tentang rumus perangkat ini, sebenarnya di versi Microsoft Excel terbaru telah menyediakan kemudahan mempelajari Excel di bilah bagian formulas.

Di bilah ini kamu bisa mempelajari dan menyisipkan rumus Excel ke dalam sel tertentu di spreadsheet yang ingin kamu hitung. Semakin sering menggunakan rumus Excel ini, maka akan semakin mudah untuk mengingatnya sehingga kamu bisa mengoperasikannya secara manual. 

Perlu diingat bahwa rumus Microsoft Excel sering juga ditulis dengan function. Jadi bila ingin menghitung data, kamu bisa dengan memasukkan perintah insert function juga. Untuk memasukkan rumus, pengguna harus memasukkan tanda sama dengan (=) kemudian diikuti dengan tag dan rumus yang ingin diketahui nilainya. Berikut beberapa rumus Microsoft xcel yang penting kamu tahu: 

1. SUM

Formula SUM adalah rumus Excel paling dasar yang bisa kamu tahu. Rumus ini memungkinkan kamu untuk menemukan jumlah atau total dari suatu nilai atau tabel yang ingin dihitung. Seperti ini bentuk rumus formatnya =SUM (nilai 1, nilai 2). Nilai yang kamu masukkan ke dalam SUM di atas, bisa berupa angka aktual atau angka di sel tertentu di spreadsheet yang dimaksud. 

Contoh untuk menemukan SUM dari 30 dan 80, kamu hanya perlu mengetik di sel spreadsheet dengan =SUM (30,80) lalu tekan enter dan sel akan menunjukkan total kedua angka yakni menjadi 110. Sementara untuk menemukan jumlah nilai dalam sel a dan sel b, kamu bisa menggunakan format rumus =SUM(sel a:sel b) 

2. Rumus pengurangan

Rumus Microsoft Excel pengurangan juga bisa menggunakan format SUM. - Pexels

Sementara itu untuk rumus pengurangan kamu bisa melakukannya dengan format =SUM (A1,-B1). Dari format ini terlihat hanya perlu menambahkan nada - dari sel yang ingin dikurangi nilainya. 

Contoh, jika kamu ingin mengetahui hasil pengurangan sel A1 10 dengan sel B1 6, maka formatnya adalah =SUM (A1,-B1) lalu enter maka hasilnya harusnya adalah 4. Rumus pengurangan ini sebenarnya tidak ada dalam rumus Excel, tapi bukan berarti tidak bisa dilakukan. Selain menggunakan format rumus di atas kamu juga bisa menggunakan format langsung tanpa menuliskan SUM yakni =A1-B1

3. Rumus perkalian dan pembagian

Untuk memasukkan format rumus perkalian, cukup dengan memasukkan rumus tanda sama dengan (=) diikuti dengan sel yang ingin dikalikan kemudian menyisipkan tanda bintang (*) di depan sel lainnya lalu enter. Sederhananya seperti ini format fungsinya =A1*B1. 

Sementara itu rumus pembagian di Microsoft Excel yakni dengan memasukkan sel yang ingin dibagi dengan menyisipkan tanda garis miring (/) di depan sel yang ingin dibagi. Sederhananya format pembagian yaitu =A1/B1. Contoh, jika sel A adalah 5 dan sel B adalah 10. Keduanya harus dibagi untuk mengetahui besaran nilainya dengan format =A1/B2. Maka nilai yang harusnya muncul adalah 0.5.

4. Rumus rata-rata 

Average merupakan Rumus Microsoft Excel untuk mencari nilai rata-rata.. - Pexels

Mengetahui rumus nilai rata-rata adalah dengan memasukkan nilai sel, atau rentang sel yang kamu hitung rata-rata dalam format =AVERAGE (A1,B1, C1, D1, dan seterusnya) atau kamu juga bisa memakai format =AVERAGE (nilai sel awal : nilai sel akhir) yang ingin diketahui. 

5. Rumus SUMIF 

Format rumus SUMIF ini digunakan bila kamu ingin mengetahui nilai sesuatu berdasarkan identifikasi dari beberapa kriteria. Contoh, kamu hanya ingin menghitung gaji karyawan tertentu tetapi hanya jika jumlahnya ada di range tertentu.  

Menghitung seperti ini bila dilakukan manual akan sangat menyita waktu, makanya kamu bisa menggunakan rumus =SUMIF (range, criteria [sum range]). Maksud dari range ini adalah rentang yang sedang ingin diuji menggunakan kriteria kamu. Sementara kriteria adalah sel yang digunakan untuk memfilter nilai itu sendiri dan sum range adalah sebuah kisaran opsional yang boleh ada atau tidak.

6. AND

Rumus Microsoft Excel AND untuk menentukan kondidi benar atau salah.. - Pexels

Fungsi logika rumus Microsoft Excel AND adalah untuk menentukan apakah semua kondisi dalam sebuah tes TRUE. AND mengembalikan BENAR jika semua argumennya mengevaluasi ke BENAR, dan mengembalikan SALAH jika satu atau beberapa argumen mengevaluasi ke SALAH. 

Rumus logika ini juga dapat memperluas penggunaan fungsi lain yang  melakukan uji logika, misalnya fungsi IF. Dengan menggunakan fungsi AND sebagai argumen logical_tes dari fungsi IF, kamu bisa menguji banyak kondisi berbeda. Sebagai contoh dalam penggunaan fungsi AND adalah =AND(logika1, [logika2], ...). Logika1 diperlukan karena merupakan kondisi pertama yang ingin diuji dan dapat mengevaluasi TRUE atau FALSE. Sedangkan logika2 dan lainnya adalah kondisi tambahan.

7. NOT

Kamu bisa menggunakan rumus Microsoft Office NOT, ketika ingin memastikan satu nilai tidak sama dengan yang lain. Penggunaan umum fungsi NOT adalah memperluas kegunaan fungsi lain yang menjalankan pengujian logika, misalnya IF. Rumus fungsi NOT adalah =NOT(logika), dengan keterangan logika sebagai argumen yang diperlukan untuk menilai atau ekspresi yang bisa dievaluasi ke TRUE atau FALSE. Jika logikanya adalah False, NOT akan mengembalikan TRUE, juga sebaliknya.

8. OR

Rumus Microsoft Excel OR dapat memperluas fungsi lainnya. - Pexels

Penggunaan umum fungsi rumus Microsoft Excel OR adalah memperluas fungsi-fungsi lain yang menjalankan pengujian logika. Fungsi OR akan mengembalikan BENAR jika semua argumennya mengevaluasi ke BENAR, kondisi yang sama juga berlaku pada argumen SALAH.

Rumus Microsoft Excel OR dapat memperluas fungsi IF saat pengujian logika. Dengan fungsi  ini, OR sebagai logical_test dari fungsi IF, kamu dapat menguji lebih dari satu kondisi yang berbeda. Rumus fungsi OR yaitu =OR(logika1, [logika2], ...), dengan argumen logika1 diperlukan sebagai kondisi pertama untuk mengevaluasi BENAR maupun SALAH. Logika2 sebagai kondisi tambahan yang bisa kamu uji hingga 255 kondisi.

9. SINGLE IF

Rumus Microsoft Excel IF merupakan salah satu fungsi yang paling banyak digunakan. Dalam fungsi ini kamu bisa membuat perbandingan logis antara nilai dan perkiraan. Sehingga, pernyataan IF dapat memiliki 2 hasil. Hasil pertama jika perbandingan kamu Benar dan hasil kedua jika perbandingan Salah. Syntax dari rumus Microsoft Excel ini adalah =IF(logical_test,[value_if_true],[value_if_false]).

Dengan keterangan logical_test (diperlukan) sebagai kondisi yang ingin diuji. Value_if_true (diperlukan) merupakan nilai yang ingin kamu kembali jika hasil logical_test adalah Benar. Value_if_false merupakan argumen opsional jika nilai yang ingin dikembalikan adalah Salah.

10.  MULTI IF

Rumus Microsoft Excel IF untuk memeriksa beberapa kondisi yang diinginkan . - Pexels

Fungsi rumus Microsoft Excel IFS adalah untuk memeriksa apakah satu atau beberapa kondisi terpenuhi dan mengembalikan nilai yang sesuai dengan kondisi Benar pertama. Fungsi IFS dapat menggantikan beberapa pernyataan fungsi IF yang bertumpuk, dan lebih mudah dibaca dengan beberapa kondisi.

Sintaks fungsi IFS adalah =IFS([Sesuatu Benar1, Nilai jika Benar1, Sesuatu Benar2, Nilai jika Benar2, Sesuatu Benar3, Nilai jika Benar3). Fungsi ini memungkinkan kamu untuk menguji hingga 127 kondisi yang berbeda.

11. AREAS

Fungsi AREAS sebagai rumus Microsoft Excel adalah untuk Mengembalikan jumlah area dalam sebuah referensi. Area adalah rentang sel berdekatan atau sel tunggal. Sintaks dari rumus ini adalah =AREAS(reference).

Sebagai contoh dari rumus Microsoft Excel ini adalah sebagai berikut:

Agar rumus Microsoft Excel ini menunjukkan hasil, pilih data dan tekan F2, lalu tekan Enter. Jika diperlukan kamu bisa menyesuaikan lebar kolom untuk melihat semua data dengan lebih mudah.

12. CHOOSE

CHOOSE dapat memilih satu dari 254 nilai jumlah index. - Pexels

CHOOSE dalam rumus Microsoft Excel adalah untuk memilih satu dari 254 nilai berdasarkan jumlah indeks. Misalnya, jika nilai1 sampai nilai30 adalah hari-hari dari bulan tersebut, CHOOSE akan mengembalikan salah satu hari ketika angka antara 1 dan 30 digunakan sebagai index_num.

Rumus Microsoft Excel ini memiliki sintaks =CHOOSE(index_num, value1, [value2], ...). Dimana index_num diperlukan untuk menentukan argumen nilai mana yang dipilih, value1 juga diperlukan, dan value selanjutnya bersifat opsional.

13.  VLOOKUP

Jika kamu ingin mencari tipe sepatu dengan nomor komponen, atau menemukan nama karyawan berdasarkan ID, kamu bisa menggunakan rumus Microsoft Excel VLOOKUP. Fungsi rumus Microsoft Excel ini adalah untuk menemukan berbagai hal di tabel atau rentang menurut baris.

Rumusnya adalah =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Sebagai contoh, =VLOOKUP("Jati",B2:E7,2,FALSE).

14. MATCH

Rumus Microsoft Excel MATCH untuk mencari nomor urutan. - Pexels

Fungsi MATCH dalam rumus Microsoft Excel memiliki sintaks =MATCH(lookup_value, lookup_array, [match_type]). Rumus tersebut digunakan untuk mencari suatu nilai tertentu pada range, list, atau tabel, untuk kemudian mengembalikan (menghasilkan atau menampilkan) posisi relatif nilai tersebut di range, list, atau tabel.

Hasil akhir fungsi MATCH yaitu nomor urutan yang dicari. Sebagai contoh urutan produk sabun, deterjen, pewangi, maka posisi relatif pewangi ada di nomor 3.

15. COUNT IF

Rumus Microsoft Excel COUNTIF merupakan fungsi statistik yang digunakan untuk menghitung jumlah sel yang memenuhi kriteria. Misalnya berapa kali wilayah tertentu muncul dalam data alamat karyawan. Fungsi COUNTIF berbentuk, =COUNTIF(Tempat Anda ingin mencari?, Apa yang ingin dicari?). Misalnya =COUNTIF(A2:A5,"Depok"), kamu perlu mengetahui bahwa COUNTIF mengabaikan huruf kapital atau kecil pada string teks.

16. COUNTA

Rumus Microsoft Excel COUNTA tidak akan menghitung sel yang kosong.. - Pexels

Fungsi COUNTA dalam rumus Microsoft Excel adalah untuk menghitung jumlah sel yang tidak kosong dalam suatu rentang. Fungsi ini dapat menghitung sel yang berisi setiap tipe informasi, nilai kesalahan, dan teks kosong, COUNTA tidak menghitung sel kosong.

Rumus Microsoft Excel ini adalah =COUNTA(value1, [value2], ...). Value1 diperlukan, sebagai argumen pertama yang mewakili nilai yang ingin Anda hitung. Value2 dan seterusnya bersifat opsional, sebagai argumen tambahan yang ingin kamu hitung, hingga maksimal 255 argumen.

17. Rumus Microsoft Excel DAY

Fungsi Rumus Microsoft Excel Days digunakan untuk menghitung jumlah hari antara dua tanggal. Selain itu, rumus ini berfungsi untuk melakukan countdown event tertentu, atau mengetahui masa kadaluarsa suatu produk. Sintaksnya adalah =DAYS(end_date, start_date)

18. Rumus Microsoft Excel DATE

Rumus Microsoft Excel DATE untuk menampilkan suatu tanggal. - Pexels

Rumus Microsoft Excel DATE memiliki sintaks =DATE(year,month,date). Fungsi ini digunakan untuk menampilkan nomor seri sekuensial yang menampilkan suatu tanggal. Menggunakan sangat berguna jika kamu menemukan kondisi dimana tahun, bulan, dan tanggal terdapat pada sel terpisah.

19. LOWER

LOWER merupakan rumus Microsoft Excel yang bisa mengkonversi semua huruf besar menjadi huruf kecil dalam string teks. Dengan rumus =LOWER(text), teks berisi huruf kecil yang ingin dikonversi. Dalam sebuah teks LOWER tidak merubah karakter yang bukan huruf.

20. UPPER

UPPER merupakan rumus Microsoft Excel untuk mengkonversi huruf menjadi besar. - Pexels

Berkebalikan dari LOWER, UPPER digunakan untuk mengonversi teks menjadi huruf besar. Rumus Microsoft Excel yang harus dimasukkan adalah =UPPER(text). Teks diperlukan untuk mengkonversi huruf yang kamu inginkan. Misalnya =UPPER(A2), maka akan mengembalikan semua teks huruf besar di sel A2.

Memiliki kemampuan lebih dalam menguasai rumus Microsoft Excel juga dapat kamu sebutkan dalam CV. Yuk, segera daftarkan diri kamu di EKRUT untuk mendapatkan karir yang lebih baik.

Baca juga: 8 Tools gratis untuk membuat infografik

Demikian beberapa contoh rumus Microsoft Excel yang bisa kamu coba untuk mempermudah pekerjaan, dengan begitu pekerjaanmu akan terasa lebih mudah. 

Last update: 28 Mei 2021

Sumber: 

• support.microsoft.com
• idntimes.com
• hubspot.com