Merubah Angka Menjadi Bulan

Dalam postingan sebelumnya sudah dibahas mengenai cara merubah tanggal menjadi hari dan bulan. Nah dalam kesempatan ini kita akan membahas mengenai contoh rumus untuk merubah angka menjadi bulan. Dengan memahami tehnik ini, kita bisa dengan mudah mengkonversi bilangan 1 s/d 12 menjai text bulan “January” s.d “December”. Fungsi yang dapat digunakan dalam formula ini adalah fungsi TEXT dan fungsi DATE. Untuk memastikan supaya nama bulan dalam bahasa Indonesia, maka kode “[$-421]” perlu ditambahkan dalam parameter fungsi TEXT.  Dengan kode tersebut maka kita bisa merubah angka 1,2 s/d 12 menjadi “Januari”, “Februari” s/d “Desember” dalam bahasa Indonesia.

Contoh soal:

Anggaplah terdapat sebuah tabel terdiri atas 2 kolom. Kolom pertama berisi bilangan 1 s/d 12 dan kolom ke-2 berisi Nama Bulan. Data Nama bulan di kolom ke-2 tidak boleh diketik secara langsung. Namun harus diformulasikan dengan mengambil referensi dari kolom pertama. Contoh soal dapat dilihat dalam gambar di bawah ini.

Sebagaimana sudah dibahas dalam artikel merubah tanggal menjadi hari dan bulan, kita bisa merubah data berjenis tanggal menjadi nama bulan dengan rumus =TEXT(tanggal,"mmmm"). Dalam kasus tersebut diasumsikan sudah tersedia argumen tanggal yaitu data yang memang ber-type tanggal (date) dan diinput secara tepat mengikuti kaidah penulisan tanggal di excel.

Sedangkan dalam konteks pembahasan kali ini, di dalam tabel hanya tersedia bilangan 1 s/d 12, bukan data bertype tanggal. Karena angka tersebut akan dikonversi menjadi nama bulan, maka kita harus memperlakukannya sebagai bulan. Proses ini dapat dibantu menggunakan fungsi DATE yang nantinya digunakan untuk menempati parameter value tanggal dalam fungsi TEXT.

=DATE(tahun,bulan,hari)

Argumen tahun dan hari dapat kita ganti dengan sembarang angka positif, sedangkan argumen bulan dapat diganti dengan nomor urut bulan yang ingin kita rubah menjadi nama bulan. Fungsi DATE ini akan menghasilkan data bertype tanggal dengan bulan yang ditentukan.

Misalnya: anggaplah kita gunakan angka 1 untuk menggantkan parameter tahun dan tanggal. Maka rumus DATE dapat dituliskan sebagai berikut:

=DATE(1,1,1) ⇒ hasilnya adalah tanggal 1/1/2017  (format "d/m/yyyy")

=DATE(1,2,1) ⇒ hasilnya adalah tanggal 1/2/2017  (format "d/m/yyyy")

=DATE(1,3,1) ⇒ hasilnya adalah tanggal 1/3/2017  (format "d/m/yyyy")

Hasil dari fungsi DATE di atas kemudian dapat diolah lagi menggunakan fungsi TEXT sehingga dapat dikonversi menjadi nama bulan. Pada akhirnya rumus dapat ditulis sebagai berikut:

=TEXT(DATE(1,1,1), "mmmm") ⇒ Hasilnya "January"

=TEXT(DATE(1,2,1), "mmmm") ⇒ Hasilnya "February"

=TEXT(DATE(1,3,1), "mmmm") ⇒ Hasilnya "March"

Atas dasar contoh di atas dan jika bilangan nomor urut bulan diletakan di sel A2,  maka rumus dapat dituliskan sebagai berikut:

=TEXT(DATE(1,A2,1), "mmmm") 

Ketik rumus tersebut di sel B2 kemudian copy dan paste ke sel di bawahnya, sehingga hasilnya akan nampak seperti screenshot di bawah ini.

Catatan: Dari contoh di atas, rumus menghasilkan nama bulan dalam bahasa Inggris. Hal ini karena nama bulan yang diperoleh dengan kode "mmmm" akan tergantung pada language setting komputer yang anda gunakan. Jika komputer anda menggunakan setting Indonesia, mungkin hasilnya akan berbeda dengan contoh gambar di atas.

Untuk memaksa rumus supaya menghasilkan nama bulan dalam bahasa indonesia maka kita bisa memodifikasi format dengan menambahkan kode "[$-421]", sehingga rumus di sel B2 dapat kita tuliskan sebagai berikut:

=TEXT(DATE(1,A2,1), "[$-421]mmmm") 

Copy rumus di atas sampai baris akhir data, maka hasilnya dapat kita lihat seperti screenshot di bawah ini

Sampai pada tahap ini, kita sudah berhasil mengkonversi bilangan nomor urut bulan menjadi nama bulan dalam bahasa Indonesia.

Demikian pembahasan singkat mengenai contoh rumus untuk merubah angka menjadi bulan dan bagaimana cara memaksa rumus supaya menghasilkan nama bulan dalam bahasa Indonesia. Harapannya, postingan ini bisa bermanfaat bagi saya sendiri dan juga bagi pembaca.

Contoh Rumus Excel Lainnya:

• Merubah Tanggal Menjadi Hari dan Bulan
• Menghitung Banyaknya Data Tertentu
• Rumus VLOOKUP Dinamis dengan Fungsi INDIRECT
• Konversi Nomor Urut Kolom Menjadi Huruf

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 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