Menggabungkan Nama Depan dan Belakang

Pada artikel sebelumnya sudah dibahas contoh rumus untuk memisahkan nama depan dan nama belakang. Nah, pada kesempatan ini, kita akan membahas kebalikannya:  Rumus untuk menggabungkan nama depan dan nama belakang. Untuk tujuan tersebut, setidaknya ada dua metode yang dapat digunakan: Yang pertama menggunakan operator “&” atau ampersand, dan yang kedua menggunakan fungsi CONCATENATE.

Contoh Soal.

Sudah tersedia sebuah tabel dalam lembar kerja excel. Tabel tersebut terdiri atas 3 kolom. Kolom ke-1 berisi data nama depan, kolom ke-2 berisi data nama belakang, sedangkan kolom ke-3 berisi nama lengkap. Kolom pertama dan kedua diisi dengan cara diinput manual. Sedangkan kolom ketiga berisi rumus untuk menggabungkan kata yang ada di kolom pertama dan kedua.

Bagaimana rumus yang harus kita buat untuk diletakan di kolom ke-3 atau kolom nama lengkap?

Sebagaimana sudah disinggung pada pembuka, setidaknya ada 2 jenis rumus yang dapat kita gunakan:

Rumus 1: Menggabungkan nama depan dan belakang menggunakan opearator “&” atau ampersand.

Operator “&” atau tanda ampersand bekerja layaknya operator matematika. Hanya saja operator matematika bekerja pada data type bilangan, sedangkan operator “&” bekerja pada data type text. Operator ini digunakan untuk menggabungkan kata, rumus umumnya dapat dituliskan seperti ini:

=text1&text2&text3&…

Artinya text1 akan disambungkan dengan text2, text3 dan seterusnya.

Dalam kaitan penggabungan nama depan dan nama belakang, maka rumus tersebut dapat dituliskan sebagai berikut:

=nama_depan&spasi&nama_belakang

Misalnya:

Nama_depan = Galih

Nama_belakang = Putra

Tanda spasi  harus ada diantara nama depan dan belakang

Maka rumus untuk menggabungkan “Galih”, tanda spasi “ “ dan “Putra” adalah:

="Galih"&" "&"Putra"

Perhatikan: untuk menuliskan nama depan dan belakang secara langsung dalam rumus,  maka nama depan dan belakang tersebut harus didalam tenda petik dua. Jika tidak, maka kemungkinan rumus akan error #NAME?.

Kembali ke contoh tabel, maka rumus yang dapat diketik di kolom ke-3 di sel C2 adalah:

=A2&" "&B2

Selanjutnya rumus tersebut tinggal di copy saja ke sel di bawahnya.

Hasilnya: maka nama depan dan nama belakang sudah dapat digabungkan:

Rumus 2: Menggabungkan nama depan dan belakang menggunakan fungsi CONCATENATE

Cara kedua yang juga cukup mudah sederhana adalah menggunakan fungsi CONCATENATE. Fungsi ini digunakan untuk menggabungkan kata atau text, dengan syntax berikut:

=CONCATENATE(text1,text2,text3,…) 

Untuk kasus penggabungan nama depan dan belakang, text atau string yang harus digabungkan adalah nama_depan, spasi dan nama_belakang. Secara umum dapat dituliskan:

=CONCATENATE(nama_depan,spasi,namabelakang)

Misalnya:

Nama_depan = Budi

Nama_belakang = Cahyadi

Diantara nama depan dan nama belakang harus ada spasi " "

Maka rumus untuk menggabungkan "Budi", tanda spasi " " dan "Cahyadi" adalah:

=CONCATENATE("Budi"," ","Cahyadi")

Jika diterapkan dalam tabel contoh, dimana text nama_depan dan nama_belakang menggunakan referensi sel, maka kita bisa menuliskan rumus di sel C2 sebagai berikut:

=CONCATENATE(A2," ",B2)

Rumus tersebut selanjutnya tinggal dicopy saja ke sel di baris selanjutnya sehingga nama depan dan nama belakang yang ada dikolom pertama dan kedua dapat digabungkan semuanya, seperdi digambarkan dalam screenshot di bawah ini.

Catatan:

Operator “&” dan fungsi CONCATENATE merupakan fitur excel yang digunakan untuk menggabungkan text. Yang dimaksud text disini tidak saja alfabet, tetapi juga termasuk karakter angka,  simbol termasuk tanda spasi yang merupakan karakter yang tidak nampak  bentuknya secara visual. Silahkan dipraktekan penggunaan Operator “&” dan fungsi CONCATENATE untuk menggabungkan text lainnya, bukan hanya nama depan dan belakang  saja.

Demikian pembahasan singkat mengenai rumus untuk menggabungkan nama depan dan nama belakang. Dari dua cara yang dijelaskan di atas silahkan dipilih mana yang lebih nyaman anda menggunakannya. Kedua-duanya sama-sama mudah dan simple.

Semoga bermanfaat.

Barangkali anda juga tertarik untuk mengintip artikel lainya:

Memisahkan Nama Depan dan Belakang

Menghitung Data Unik dengan SUMPRODUCT

Memisahkan Nama Depan dan Belakang

Ada beberapa fitur excel yang dapat digunakan untuk memisahkan nama depan dan nama belakang.  Salah satu di antaranya adalah menggunakan rumus atau formula. Pembahasan kali ini akan mengambil contoh sederhana bagaimana memisahkan nama depan dan belakang menggunakan rumus. Namun diasumsikan bahwa setiap nama terdiri atas 2 kata saja, yaitu nama depan dan nama belakang.

Contoh Soal

Misalnnya di lembar kerja excel sudah tersedia sebuah tabel yang terdiri atas 3 kolom. Kolom pertama berisi nama lengkap, kolom ke-2 berisi data nama depan dan kolom ke-3 berisi data nama belakang. Kolom ke-2 dan ke-3 tidak boleh diketik secara manual, tetapi harus menggunakan rumus yang mengambil referensi dari kolom pertama.

Bentuk tabel contoh dapat dilihat seperti gambar berikut:

Perhatikan gambar di atas pada kolom A atau kolom Nama Lengkap. Kita dapat mengetahui bahwa Kunci untuk memisahkan nama depan dan nama belakang adalah sebuah spasi. Artinya kita harus mengetahui posisi spasi dalam text nama lengkap supaya kita bisa memecah nama tersebut menjadi dua bagian.

Untuk mengetahui posisi spasi dalam text nama lengkap, bisa digunakan fungsi FIND, yang secara umum dapat dituliskan:

 =FIND(spasi,nama_lengkap)

Dimana parameter spasi = tanda spasi, sedangkan nama_lengkap adalah data nama lengkap yang dibangun oleh beberapa karakter dan salah satunya berupa karakter spasi. Untuk rumus praktikalnya dapat dicontohkan sebagai berikut:

=FIND(" ","Andi Rahman")

Rumus di atas akan menghasilkan bilangan 5 yang merupakan posisi nomor urut spasi pada text “Andi Rahman”. Tanda petik menunjukan sebuah string, artinya text yang memang harus dibaca sebuah text. Jika tidak ada tanda petik, maka rumus akan error #NAME? karena text nama akan terbaca sebagai nama sel atau nama range.

Selanjutnya: setelah posisi spasi sudah dapat diketahui, maka kita bisa menentukan text nama depan dengan nama belakang. Sekali lagi sy sampaikan contoh disini diasumsikan nama lengkap terdiri atas dua kata, atau yang dimaksud nama depan adalah satu kata sebelum tanda spasi.

Rumus Untuk Mengambil Nama Depan

Cara 1: Mengambil nama depan menggunakan fungsi MID dan FIND

Kita mulai penjelasannya dari fungsi MID, perhatikan rumus umum berikut:

=MID(nama_lengkap,1,jumlah_karakter_nama_depan)

Rumus tersebut dapat diterjemahkan dalam sebuah kalimat: mengambil bagian text dari string nama_lengkap dimulai dari karakter ke-1 sebanyak jumlah_karakter_nama_depan. Karena jumlah karakter nama depan adalah sama dengan posisi spasi dikurangi 1 maka , rumus tersebut juga dapat dituliskan sebagai berikut:

=MID(nama_lengkap,1, posisi_spasi-1)

Seperti dijelaskan pada bagian awal, posisi_spasi dapat dicari dengan bantuan fungsi FIND, yaitu =FIND(spasi,nama_lengkap), sehingga rumus diatas dapat juga dituliskan sebagai berikut:

=MID(nama_lengkap,1, FIND(spasi,nama_lengkap),-1)

Dan secara praktikal menggunakan contoh tabel kasus di atas, maka rumus dapat dituliskan seperti di bawah ini:

=MID(A2,1,FIND(" ",A2)-1)

Cara 2 : Mengambil nama depan menggunakan fungsi LEFT dan FIND

Secara umum, rumusnya dapat dituliskan begini:

=LEFT(nama_lengkap,jumlah_karakter_nama_depan)

Artinya mengambil text dari nama_lengkap mulai dari sebelah kiri sebanyak jumlah_karakter_nama_depan.

Karena jumlah_karakter_nama_depan juga sama dengan posisi spasi dikurangi 1 yang diperoleh melalui rumus FIND, maka rumusnya juga bisa jadi seperti ini:

=LEFT(nama_lengkap,FIND(" ",nama_lengkap)-1)

Secara praktikal menggunakan contoh tabel yang disediakan di atas, maka rumus dapat dituliskan sebagai berikut:

=LEFT(A2,FIND(" ",A2)-1)

Rumus Untuk Mengambil Nama Belakang:

Cara 1: Mengambill Nama Belakang Menggunakan Rumus MID dan FIND

Rumus umum:

=MID(nama_lengkap,FIND(" ",nama_lengkap)+1,100)

Yang dapat diterjemahkan sebagai berikut: mengambil string yang merupakan bagian dari string nama_lengkap dimulai dari posisi spasi di tambah 1, sejumlah 100 karakter. Posisi spasi dicari menggunakan fungsi FIND. Bilangan 100 mengasumsikan bahwa panjang nama belakang tidak melebihi 100 karakter.

Rumus tersebut dapat dituliskan di lembar kerja excel seperti berikut:

=MID(A2,FIND(" ",A2)+1,100)

Cara 2: Mengambil Nama Belakang Menggunakan Rumus RIGHT, FIND dan LEN

=RIGHT(nama_lengkap,jumlah_karakter_nama_belakang)

Jumlah karakter nama belakang sendiri harus dicari menggunakan rumus yaitu panjang karakter nama lengkap dikurangi posisi spasi. Panjang text nama_lengkap dicari menggunakan fungsi LEN, sedangkan posisi spasi dicari menggunakan fungsi FIND, sehingga rumus akan seperti ini:

=RIGHT(nama_lengkap,LEN(nama_lengkap)-FIND(" ",nama_lengkap))

Secara praktikan menggunakan tabel contoh dapat diketikan dengan rumus berikut 

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Sampai disini saya kira sudah cukup pembahasan mengenai cara memisahkan nama depan dan nama belakang. Ada beberapa alternative yang bisa digunakan, silahkan dicoba.

Terimakasih. 

Baca juga:

Menghitung data antara dua tanggal

Menghitung data antara dua bilangan

Menghitung Data di Antara 2 Bilangan

Salah satu contoh praktis menghitung banyaknya data di antara dua bilangan adalah menghitung jumlah orang yang memiliki umur atau usia pada rentang tertentu. Ada beberapa alternative rumus yang dalam digunakan. 3 contoh rumus diantaranya akan dibahas dalam kesempatan ini. Masing-masing rumus menggunakan fungsi yang berbeda yaitu: COUNTIFS, COUNTIF, dan SUMPRODUCT. 

Contoh Soal:

Untuk contoh soal ini, kita gunakan tabel data yang sangat sederhana saja supaya lebih mudah difahami. Anggaplah ada sebuah tabel yang terdiri atas 2 kolom. Kolom pertama berisi item nama peserta sebuah perlombaan, sedangkan kolom kedua berisi data umur peserta lomba tersebut dalam satuan tahun. Tugas selanjutnya adalah bagaimana menghitung jumlah orang yang berumur antara 15 s.d 20 tahun.

Cara “pertama” yang cukup “disukai” adalah dengan menghitung menggunakan jari, mata dan mulut. Alias mata sedikit menyipit menatap monitor sambil mengerutkan kening, jari menggerakan mouse dan mulut bergumam, menghitung data yang sesuai :  Satu,… dua…, tiga…

Ups, tapi bukan cara itu yang dimaksud. Bayangkan saja jika kita ada memiliki 1000 baris data saja. Betapa lelahnya jika kita harus menghitungnya satu persatu.

Ada 3 contoh rumus yang akan saya tawarkan kepada anda disini (seperti jualan aja yah..)

Cara 1: Menggunakan Rumus COUNTIFS.

Secara general rumusnya dapat dituliskan sebagai berikut:

=COUNTIFS(range_bilangan,kriteria_bawah,range_bilangan,kriteria_atas)

Jika dihubungkan dengan contoh tabel di atas.

• Range_bilangan adalah kolom tabel yaitu range B2:B10
• Kriteria_bawah adalah kriteria komparasi dengan nilai terbawah, karena kita menginginkan bilangan umur minimal 15 tahun, maka dapat dituliskan ">=15"
• Kriteria_atas adalah kriteria komparasi dengan nilai teratas, karena kita menginginkan bilangan umur maksimum 20 tahun, maka dapat dituliskan "<=20"

Pada akhirnya, rumus dapat dituliskan seperti berikut ini:

=COUNTIFS(B2:B10,">=15",B2:B10,"<=20")

Atau terlihat seperti gambar dalam screenshot di bawah ini :

Perhatikan gambar di atas: highlight kuning pada tabel umur dimaksudkan untuk mengecek secara manual apakah rumus sudah bekerja dengan benar sesuai yang diharapkan. Jika kita hitung secara manual maka kita dapatkan 6 orang yang umurnya antara 15 s.d 20 tahun. Hasil yang sama juga diberikan oleh rumus. 

Cara 2: Menggunakan Rumus COUNTIF.

Jika anda masih menggunakan excel versi lama (2003 atau yang lebih tua), anda tidak akan menemukan fungsi COUNTIFS pada excel versi tersebut. Maka sebagai alterntivenya bisa digunakan fungsi COUNTIF (tanpa huruf S di ujung).

Rumus General:

Dalam hal ini menggunakan dua buah fungsi COUNTIF

• COUNTIF ke-1 untuk menghitung Jumlah bilangan yang lebih atau sama dengan batas bawah
• COUNTIF ke-2 untuk menghitung Jumlah bilangan yang lebih dari batas atas

Hasil yang diinginkan nantinya akan berupa pengurangan dari COUNTIF ke-1 dikurangi COUNTIF KE-2.

(COUNTIF ke-1) – (COUNTIF ke-2)

Atau:

=COUNTIF(range_bilangan,kriteria_bawah)-COUNTIF(range_bilangan,kriteria atas)

• Range_bilangan adalah kolom tabel yaitu range B2:B10
• Kriteria_bawah (pada COUNTIF ke-1) adalah kriteria komparasi dengan nilai terbawah, karena kita menginginkan bilangan umur minimal 15 tahun, maka dapat dituliskan ">=15"
• Kriteria_atas (pada COUNTIF ke-2) adalah kriteria komparasi dengan nilai teratas, karena dijadikan sebagai pengurang maka yang harus dihitung oleh COUNTIF ke-2 adalah bilangan umur yang lebih dari 20, atau dituliskan ">20"

Pada akhirnya rumus dapat diketik sebagai berikut:

=COUNTIF(B2:B10,">=15")-COUNTIF(B2:B10,">20")

Cara 3: Menggunakan Rumus SUMPRODUCT

Cara selanjutnya adalah menggunakan fungsi SUMPRODUCT. Jika anda belum begitu familiar dengan fungsi SUMPRODUCT. Silahkan dibaca terlebih dahulu artikel: Cara Mudah Memahami Rumus SUMPRODUCT

Jika sudah, mari kita lanjukan ke contoh kasus menghitung jumlah orang pada range umur 15-20 tahun. Adapun rumusnya secara general dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(range_bilangan>=batas_bawah),--(range_bilangan<= 20))

Atau jika parameter-parameternya diganti dengan referensi dan kritera yang ditetapkan dalam contoh tabel, maka rumusnya menjadi seperti di bawah ini.

=SUMPRODUCT(--(B2:B10>=15),--(B2:B10<=20))

Demikian 3 contoh rumus untuk menghitung jumlah data di antara dua bilangan (a.k.a menghitung jumlah orang dalam rentang usia tertentu). Semoga bermanfaat.

Artikel terkait:

Menghitung Data Antara Dua Tanggal

Cara mudah memahami rumus SUMPRODUCT

Menghitung Data Unik Dengan SUMPRODUCT

Menghitung Data Antara Dua Tanggal

Salah satu cara untuk menghitung banyaknya data antara dua tanggal adalah dengan menggunakan rumus COUNTIFS. Mulai diperkenalkan pada excel 2017, fungsi COUNTIFS ini memiliki keunggulan dibandingkan dari fungsi pendahulunya COUNTIF (Tanpa S). COUNTIFS mampu melakukan penjumlahan bersyarat banyak kriteria dengan cukup satu fungsi saja. Hal ini mustahil dapat dilakukan oleh fungsi COUNTIF. 

Mari kita lihat contoh rumus COUNTIFS untuk menghitung jumlah sel atau data di antara dua tanggal yang ditentukan

Contoh Kasus

Anggaplah kita memiliki list data berupa data tanggal lahir sejumlah orang. Kemudian hendak diketahui ada berapa sih jumlah orang yang lahir diantara tanggal tertentu, Misalnya ditanyakan berapa jumlah orang dalam list yang lahir di antara tanggal 1 Januari 2000 sampai dengan tanggal 31 Desember 2013. Perhatikan screenshot berikut:

Untuk menyelesaikan tugas di atas, kita bisa menggunakan rumus fungsi COUNTIFS, sesuai pembahasan topik fungsi exel kali ini. 

Secara general penulisan rumus IF untuk kasus ini adalah:

=COUNTIFS(rangeTanggal,">="&tanggal_bawah,rangeTanggal,"<="&tanggal_atas)

Contoh Rumus 1 : Menuliskan tanggal langsung dalam rumus.

Untuk mengikutkan tanggal terbawah dan tanggal teratas dalam rumus COUNTIFS, diperlukan bantuan fungsi lainnya. Dalam hal ini kita gunakan fungsi DATE. 

Fungsi DATE akan menghasilkan data tanggal lengkap dengan memasukan parameter year (tahun), month (bulan) dan day (hari)

=DATE(tahun, bulan, hari)

Tahun = bilangan tahun, bulan = nomor urut bulan, hari = nomor urut hari dalam sebulan.

Sehingga untuk menghitung jumlah orang yang lahir dari tanggal 1 Januari 2000 s.d 31 Desember 2018, kita menggunakan fungsi DATE untuk menentukan tanggal terbawah dan tanggal teratas.

• Tanggal terbawah = 1 Januari 2000,  rumusnya =DATE(2000,1,1)
• Tanggal teratas = 31 Januari 2018, rumusnya   =DATE(2003,12,31)

Kemudian rumus tersebut digabung dengan rumus COUNTIFS, sehingga dapat dituliskan seperti berikut:

=COUNTIFS(rangeTanggal,">="&DATE(2000,1,1),rangeTanggal,"<="&DATE(2003,12,31))

Dikarenakan parameter rangeTanggal terletak pada range B2:B10 (lihat contoh tabel di atas), maka kemudian rumus tersebut dapat diketikan seperti di bawah ini.

=COUNTIFS(B2:B10,">="&DATE(2000,1,1),B2:B10,"<="&DATE(2003,12,31))

Penggunaan rumus tersebut di lembar kerja excel digambarkan dalan screenshot di bawah ini

Penting diperhatikan mengenai cara penulisan tanda lebih dari atau sama dengan (">=") dan operator kurang dari atau sama dengan ("=<"). Cara penulisan tanda tesebut harus di dalam tanda petik dan kemudian digabungkan (concatenate) dengan tanggal teratas atau terbawah menggunakan bantuan operator ampersand (&). Untuk tanggal terbawah karena kita ingin menghitung jumlah tanggal diatasnya, maka digunakan tanda lebih dari atau sama dengan (">="), dan sebaliknya untuk tanggal teratas.

Contoh Rumus 2 : Menuliskan tanggal dengan referensi sel.

Sangat disarankan untuk menggunakan referensi sel sebagai tempat menuliskan tanggal terbawah dan teratas. Ini dimaksudkan supaya lebih mudah dimodifikasi jika di kemudian, kita perlu merubah tanggal terbawah dan tanggal teratasnya.

Misalnya tanggal terbawah diletakan pada sel E3, dan tanggal teratas diletakan di sel E4, maka rumus tadi dapat dimodifikasi menjadi seperti berikut ini:

=COUNTIFS(B2:B10,">="&E3,B2:B10,"<="&E4)

Berikut contoh penampakannya dalam spreadsheet.

Dari gambar di atas kita dapat menyimpulkan bahwa rumus ke-2 lebih ringkas karena tidak membutuhkan fungsi tambahan (fungsi DATE). Selain itu, parameter tanggal terbawah dan tanggal teratas akan lebih mudah dimodifikasi tanpa harus merubah rumus.

Sangat Penting: Baik contoh rumus ke-1 maupun ke-2, harus dipastikan cara penulisan tanggal sudah benar. Perhatikan kolom tanggal lahir (kolom B). data tanggal dalam tabel tersebut harus benar-benar data ber-type tanggal, bukan type text. Secara general (jika tidak ada edit alignment) data type tanggal akan nampak rata kanan. Jika tidak, maka kemungkinan data tersebut ber-type text dan akan error jikak diproses dengan rumus yang sudah dijelaskan di atas.

Demikian pembahasan singkat mengenai bagaimana menggunakan fungsi COUNTIFS untuk menghitung jumlah data diantara dua tanggal.

Semoga bermanfaat.

Terimakasih.

Artikel Terkait:

Menghitung Jumlah Data Unik

Menghitung Jumlah Text dengan Case Sensitive

Rumus VLOOKUP untuk Tanggal

Rumus SUMPRODUCT EXACT Menghitung Jumlah Text Case Sensitive

Terbongkar, satu lagi kegunaan fungsi SUMPRODUCT: Menghitung jumlah text secara case sensitive atau peka huruf besar/kecil. Namun untuk membuat rumus perhitungan jumlah text dengan case sensitive ini, fungsi SUMPRODUCT tidak dapat berdiri sendiri, tetapi harus dengan bantuan fungsi lainnya, yaitu fungsi EXACT. Bagaimana cara membuatnya? Tidak terlalu sulit. Mari kita lihat dan buktikan.

Sebelum melangkah ke contoh, alangkah baiknya mengingat dulu terminologi fungsi yang akan digunakan.

Fungsi SUMPRODUCT digunakan untuk Mengalikan komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut. Syntax fungsi ini adalah SUMPRODUCT(array1, [array2], [array3], ...) , dimana array1, array2 dan seterusnya adalah array atau sekumpulan data yang komponen-komponennya ingin dikalikan dan dijumlahkan. Jika SUMPRODUCT hanya memiliki satu parameter array, maka hasil yang diperoleh adalah penjumlahan elemen-elemen yang terdapat dalam satu array tersebut.

Fungsi EXACT digunakan untuk membandingkan dua string teks dan akan mengembalikan TRUE jika kedua string itu sama persis, jika tidak akan mengembalikan FALSE. EXACT bersifat sensitive atau peka terhadap huruf besar kecil tapi mengabaikan perbedaan pemformatan. Syntax fungsi ini =EXACT(text1, text2), dimana text1 dan text2 adalah dua string yang akan dibandingkan

Contoh Rumus Menghitung Text Case Sensitive

Baiklah supaya lebih mudah difahami, mari kita pelajari melalui contoh. Misalnya sudah tersedia text “DKI JAKARTA” pada range A1:A6, namun cara penulisan “DKI JAKARTA” tersebut berbeda-beda huruf besar atau kecilnya.  Selanjutnya kita hanya ingin menghitung text  “DKI JAKARTA” dengan  huruf besar semuanya.

Sebagaimana sudah disampaikan pada bagian awal, untuk keperluan ini, kita akan menggunakan rumus yang berupa gabungan fungsi SUMPRODUCT dan EXACT.

Adapun rumusnya dapat dituliskan sebagai berikut:

=SUMPRODUCT(--EXACT("DKI JAKARTA",A1:A6))

Jika text ("DKI JAKARTA") diletakan dalam sebuah sel sebagai referensi, maka rumusnya dapat diganti menjadi :

=SUMPRODUCT(--EXACT(A10,A1:A6))

Sebagai gambaran penerapannya dalam lembar kerja excel dapat dilihat dalam screenshot di bawah ini.

Cara Kerja Rumus SUMPRODUCT dan EXACT

Bagaimana excel bisa membedakan huruf besar dan kecil serta dapat memberikan informasi jumlah text case sensitive yang tepat kepada usernya yang menggunakan formula SUMPRODUCT EXACT? Mari kita perhatikan penjelasannya.

Silahkan di test terlebih dahulu, cara kerja contoh rumus di atas dengan melakukan konversi parameter-parameter rumus seperti  yang diperlihatkan dalam gambar di bawah ini.

Keterangan: untuk mengkoversi parameter rumus, silahkan di seleksi porsi rumus (highlight hitam),  kemudian tekan tombol F9.

• Fungsi EXACT digunakan untuk membandingkan string "DKI JAKARTA" dengan string yang ada pada setiap sel dalam range A1:A6. Pengecekan perbandingan ini besifat case sensitif. Jika persis sama maka akan menghasilkan nilai TRUE, dan jika tidak sama atau tidak persis sama maka akan  menghasilkan nilai FALSE. Hal ini bisa dilihat setelah rumus EXACT  di konversi maka hasilnya berupa array {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}  urutan TRUE dan FALSE mengikuti hasil pengecekan text pada range A1:A6. 

• Selanjutnya, array berisi data TRUE dan FALSE harus dikonversi lagi menjadi 1  untuk TRUE  dan untuk FALSE, supaya dapat dikalkulasi selanjutnya. Untuk proses ini digunakan bantuan tanda double unary atau tanda double minus (--). Sehingga array menjadi {0;1;1;0;1;0}

• Langkah akhir, adalah tugas SUMPRODUCT untuk mengeksekusi parameter array {0;1;1;0;1;0}. Karena hanya ada satu array, maka SUMPRODUCT tidak mengalikan elemen, tetapi langsung menjumlahkannya. Ini pendapat pribadi saya, apa yang terjadi di dalam system saya tidak mengetahuinya, apakah mengalikan elemen array dengan bilangan 1 kemudian menjumlahkannya, atau bypass langsung menjumlah masing-masing elemennya. Yang jelas jika SUPRODUCT hanya memiliki satu parameter array saja maka hasilnya sama dengan penjumlahan elemen-elemen dalam satu array. Jadi jumlah text "DKI JAKARTA" = 0 + 1 + 1 + 0 + 1 + 0 = 3

Saya kira cukup disini pembahasan mengenai cara menggunakan rumus SUMPRODUCT dan EXACT untuk menghitung jumlah text atau string secara case sensitive (peka huruf besar dan kecil).

Semoga bermanfaat. 

Artikel Terkait:

Rumus SUMPRODUCT Menghitung Data Unik

Rumus SUMPRODUCT Penjumlahan Bersyarat

Cara Mudah Memahami Rumus SUMPRODUCT

Referensi

https://support.office.com/id-id/article/sumproduct-fungsi-sumproduct-16753e75-9f68-4874-94ac-4d2145a2fd2e

https://support.office.com/id-id/article/exact-fungsi-exact-d3087698-fc15-4a15-9631-12575cf29926

Menghitung Jumlah Data Unik Dengan SUMPRODUCT

Fungsi SUMPRODUCT juga ternyata dapat digunakan untuk menghitung jumlah data unik pada excel. Hal ini tentu saja menambah daftar kemampuan fungsi SUMPRODUCT, disamping untuk melakukan penjumlahan bersyarat seperti yang sudah dibahas dalam artikel sebelumnya. Namun untuk menghitung data unik, fungsi SUMPRODUCT tidak dapat berdiri sendiri.  Fungsi ini harus digabung dengan fungsi excel lainnya yaitu COUNTIF. Bagaimana caranya? Cukup ikuti langkah-langkah dan contoh sederhana yang akan dijelaskan dalam artikel ini.

Contoh Kasus.

Misalnya kita memiliki data list daftar belanja. Namun dalam list tersebut ada beberapa item barang belanjaan yang muncul lebih dari satu kali. Dalam kata lain ada duplikat data. 

Anggaplah daftar belanja tersebut adalah buah-buahan, seperti dalam gambar di bawah ini.

Dengan mengandalkan ketelitian mata, sebenarnya kita bisa menghitung secara manual, ada berapa  sich sebenarnya jenis buah-buahan dalam  list di atas. Dengan sedikit upaya kita bisa mengetahui 4 jenis buah yaitu Mangga, Jeruk, Sirsak dan  Pisang). 

Jika list data hanya sedikit dan perhitungan diperlukan hanya untuk sekali waktu saja, memang cara hitung manual tidak terlalu bermasalah. Lain halnya jika kita mempunyai list data yang besar, dan perlu dilakukan analisa data unik supaya tidak terjadi double perhitungan.

Untuk contoh, kita gunakan list data yang kecil, supaya lebih mudah membuat gambar screenshotnya, karena kalau kebesaran, tidak muat layar komputernya 😊😊🙏🙏

Kembali ke contoh. Bagaimana rumus untuk menghitung jumlah jenis buah dalam list belanja.

Kita bisa mengetikan rumusnya sebagai berikut:

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))

Rumus di atas memberikan hasil 4.

Cara Kerja Rumus.

Sebagaimana kita ketahui bawha fungsi SUMPRODUCT bekerja dengan cara mengalikan komponen-komponen sejajar dalam array. Jika hanya ada satu array maka hasilnya akan sama dengan penjumlah elemen dalam array itu sendiri atau sama dengan hasil rumus SUM.

Sedangkan fungsi COUNTIF digunakan untuk menghitung masing-masing barang. Hasil perhitungan dengan COUNTIF tersebut kemudian dijadikan sebagai penyebut dalam pecahan dengan pebilang 1. Ini dimaksudkan supaya pada saat dijumlahkan nantinya menjadi 1.  

Misalnya jika ada 4 mangga rumus COUNTIF mengembalikan nilai 4. Karena ada 4 mangga maka ¼ + ¼ ¼ + ¼ = 1. Ini artinya berapapun kemunculan duplikat dalaml list, maka akan dihitung sebagai satu.

Silahkan diperika cara kerja rumus tadi dengan menkonversi parameter formula menjadi array  menggunakan bantuan tombol F9.

• Seleksi bagian rumus COUNTIF(A2:A12,A2:A12) kemudian tekan F9 dan tekan enter. Maka seketika rumus akan berumah menjadi.

=SUMPRODUCT(1/{4;2;4;1;4;4;2;4;4;4;4})

• Bilangan dalam tanda kurung kurawal merupakan jumlah kemunculan data yang sama dalam list.

Sekarang: seleksi bagian rumus  1/{4;2;4;1;4;4;2;4;4;4;4} kemudian tekan F9 sehingga rumus akan berubah menjadi:

=SUMPRODUCT({0.25;0.5;0.25;1;0.25;0.25;0.5;0.25;0.25;0.25;0.25})

Terakhir: SUMPRODUCT menjumlah elemen-elemen array tersebut, dan hasil akhirnya adalah 4 data unik.

'=0,25+0,5+0,25+1+0,25+0,25+0,5+0,25+0,25+0,25+0,25

= 4

Menghitung Jumlah Data Unik Dengan Mengabaikan Data Kosong

Jika kita perhatikan contoh rumus sebelumnya, kemungkinan akan menghasilan nilai error #DIV/0! apabila ada salah satu atau lebih sel yang kosong dalam referensi. 

Kenapa bisa error? Untuk mengecek kenapa error, silahkah hapus konten salah satu sel, misalnya seperti gambar dibawah ini:

Kemudian kembali ke sel yang berisi rumus : 

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12)).

Pada formula bar, seleksi menggunakan kursor, bagian rumus COUNTIF(A2:A12,A2:A12), lalu tekan F9 sehingga rumus akan menjadi =SUMPRODUCT(1/{4;1;4;2;4;2;0;3;4;3;3})

Perhatikan elemen-elemen array dalam tanda kurung kurawal. Ternyata salah satunya bernilai 0 yang mencerminkan sel kosong. 

Karena dalam perhitungan nilai unik ini kita menempatkan elemen array hasil COUNTIF sebagai pembagi, maka pembagian dengan bilangan 0 akan menyebabkan error #DIV/0!

Solusi:

Untuk mengatasi tersebut,  supaya rumus mengabaikan sel kosong dan terbebas dari error, maka kita dapat memodifikasinya menjadi:

=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))

Dari gambar diatas, dapat disimpukan rumus ke-2 lebih disarankan daripada rumus pertama.

Demikian pembahasan mengenai cara menghitung data unik menggunakan SUMPRODUCT dan COUNTIF.  Silahkan dicoba dan semoga bermanfaat.

Terimakasih.

Baca juga artikel terkait lainnya:

Rumus SUMPRODUCT untuk Penjumlahan Bersyarat

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT

Rumus SUMPRODUCT Untuk Penjumlahan Bersyarat

Ada beberapa alternatif rumus excel yang dapat digunakan untuk melakukan penjumlahan bersyarat, salah satunya adalah dengan menggunakan Fungsi SUMPRODUCT. Mengapa menggunakan SUMPRODUCT? karena fungsi ini tersedia, baik versi excel baru, maupun versi lama bersama dengan fungsi SUMIF. Dibandingkan dengan fungsi SUMIF, SUMPRODUCT lebih unggul dalam kaitan kemampuannya yaitu dapat menyelesaikan tugas penjumlahan bersyarat dengan kriteria lebih dari satu jenis.

Penjelasan mengenai cara menggunakan fungsi SUMPRODUCT sendiri sudah disampaikan dalam artikel sebelumnya: Cara Mudah Memahami Rumus SUMPRODUCT. Silahkan dilihat-lihat dulu jika anda belum begitu familiar dengan fungsi ini.

Jika anda sudah mulai familiar dengan fungsi SUMPRODUCT, mari kita lanjutkan ke pembahasan cara Menggunakan Fungsi SUMPRODUCT utuk Penjumlahan Bersyarat. Model penjumlahan seperti ini sering disebut juga Penjumlahan Dengan Kriteria atau Penjumlahan Dengan Kondisi (conditional sum)

Contoh Kasus.

Anggaplah kita memiliki data penjualan produk buah-buahan yang disimpan dalam sebuah tabel terdiri atas tiga kolom. Kolom pertama berisi data tanggal, kolom kedua berisi data Nama Barang, dan kolom Ketiga berisi data Nilai penjualan. Bentuk tabel digambarkan dalan screenshot di bawah ini.

Selanjutnya tugas kita adalah melakukan rekapitulasi penjumlahan dengan syarat atau kriteria sebagai berikut:

1. Menjumlahkan nilai penjualan buah mangga di semua tanggal (Kondisi Tunggal)

2. Menjumlahkan nilai penjualan buah mangga pada tanggal 2 dan 3 April 2018 (Beberapa Kondisi)

Mari kita intip sati per satu.

Contoh 1: Penjumlahan Dengan Kondisi / Kriteria Tunggal Untuk Menghitung Penjualan Buah Mangga.

Untuk kasus ini, hanya ada satu kriteria atau kondisi yang diperlukan, yaitu Buah Mangga. Secara umum rumus SUMPRODUCT ini dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(nama_buah=”buah mangga”), penjualan)

Karena kita menggunakan tabel dimana nama buah terdapat pada range B2:B13 dan data nilai penjumlahan ada di range C2:C13, maka rumus untuk menghitung nilai penjumlahan buah mangga di semua tanggal adalah sebagai berikut:

=SUMPRODUCT(--(B2:B13="Buah Mangga"),C2:C13)

Cara Kerja :

• Rumus SUMPRODUCT akan mengecek konten setiap sel pada range B2:B3 apakah berisi text “Buah Mangga”. Jika iya maka akan menghasilkan nilai TRUE, jika sebaliknya maka bernilai FALSE.  Fungsi SUMPRODUCT juga akan mengambil konten nilai penjualan dari setiap sel pada range C2:C13.

• Jika dikonversi menjadi array maka rumus di atas akan nampak seperti di bawah ini.

SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}),{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Nilai TRUE dan FALSE, supaya bisa diproses selanjutnya maka harus dikonversi dahulu menjadi data numerik. Untuk tugas ini dilimpahkan ke double unary. Perhatikan 2 buah tanda minus (--) sebelum argumen pertama. Tanda inilah yang disebut double unary. Operator ini berguna untuk merubah TRUE menjadi 1, sedangkan FALSE menjadi 0, sehingga dalam tampilan array, rumus tadi akan nampak seperti ini.

=SUMPRODUCT({1;0;0;1;0;0;1;0;0;1;0;0},{1000000;1500000;2000000;1000000;2000000;2500000;1500000;3000000;2000000;1000000;2000000;2500000})

• Ingat kembali mengenai tugas fungsi SUMPRODUCT yaitu melakukan perkalian komponen-komponen terkait dalam array yang diberikan, kemudian mengembalikan jumlah dari setiap hasil perkalian tersebut. Sehingga jika dihitung dengan operasi matematika penjumlahan, rumus di atas dapat dituliskan seperti berikut:

= (1x1.000.000) + (0x1.500.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000) + (1x1.500.000) + (0x3.000.000) + (0x2.000.000) + (1x1.000.000) + (0x2.000.000) + (0x2.500.000)

=1.000.000 + 0 + 0 + 1.000.000 + 0 + 0 + 1.500.000 + 0 + 0 + 1.000.000 + 0 + 0

= 4.500.000

Contoh 2: Penjumlahan dengan kriteria ganda untuk menghitung total penjualan buah mangga pada tanggal 2 s.d 3 April 2018

Untuk menglakukan penjumlahand alam contoh ke-2 ini, kita perlu mempertimbangkan 3 kondisi:

Kondisi ke-1 : Tanggal terbawah, dalam  hal ini tgl 2 April 2018

Kondisi ke-2 : Tanggal teratas, dalam hal ini adalah tanggal 3 April 2018

Kondisi ke-3 : Nama buah yang akan dijumlahkan nilai penjualannya.

Rumus Umum

=SUMPRODUCT(--(tanggal>=tanggal_terbawah),--(tanggal<=tanggal_teratas),--(nama_buah="Buah Mangga"),C2:C13)

Penting: 

• Penulisan tanggal pada kolom tanggal harus benar yaitu harus berupa tanggal, jangan berupa text yang nampak seperti tanggal.
• Untuk menuliskan tanggal dalam rumus SUMPRODUCT, kita bisa menggunakan bantuan fungsi DATE. Fungsi ini dapat dituliskan dengan syntax DATE(tahun,bulan,tanggal).

Setelah kedua poin diatas difahami, maka selanjutnya kita bisa membuat rumus SUMPRODUCT untuk menghitung jumlah nilai penjualan buah mangga pada tanggal 2 s.d 3 April 2018. 

Adapun rumusnya dapat dituliskan sebagai berikut:

=SUMPRODUCT(--(A2:A13>=DATE(2018,4,2)),--(A2:A13<=DATE(2018,4,3)),--(B2:B13="Buah Mangga"),C2:C13)

=2.500.000

Cara Kerja:

• Rumus sumproduct akan mengecek 3 kondisi yang harus terpenuhi, ketiga kondisi tersebut berupa array bernilai TRUE dan FALSE hasil pengecekan tanggal bawah, tanggal atas dan nama buah. TRUE dan FALSE tersebut kemudian dikonversi menjadi bilangan 1 dan 0 oleh operatur double unary (--). 
• Fungsi SUMPRODUCT kemudian melakukan perkalian masing-masing komponen array yang terkait (sebaris). Jika ada angka 0 dalam salah satu komponen yang dikalikan, maka akan dihasilkan bilangan 0.  Ini artinya semua syarat harus terpenuhi atau bernilai 1  supaya nilai penjumlahan dapat terhitung. Terakhir fungsi SUMPRODUCT menjumlahkan semua hasil perhitungan tadi. Dan hasilnya adalah 2.500.000

Tips: Anda dapat menggunakan tombol shortcut F9 untuk mengecek masing-masing elemen array dalam rumus SUMPRODUCT. Cukup seleksi salah satu argumen fungsi, kemudian tekan F9. Cara penggunakan shortcut F9 untuk mengecek array ini sudah dijelaskan pada pembahasan rumus SUMPRODUCT sebelumnya yang berjudul : Cara Mudah Memahami Rumus SUMPRODUCT.

Demikian pembahasan singkat mengenai cara menggunakan fungsi SUMPRODUCT untuk membuat rumus penjumlahan bersyarat. 

Semoga bermanfaat.

Silahkan dilihat juga contoh rumus excel lainnya:

Cara Mudah Memahami Rumus SUMPRODUCT

Fungsi SUMPRODUCT