Contoh Kasus: Mendapatkan Nilai Tertinggi dari Kumpulan Tanggal Yang Sama

Sering sekali dari suatu kumpulan data tanggal dan nilai, kita ingin mengambil nilai maksimum dari tanggal tersebut.

Sebagai contoh, dari data pada gambar berikut untuk tanggal 3 Oktober 2013 (10/3/2013) kita ingin mendapatkan nilai 212 sebagai nilai terbesarnya.

Nilai maksimum untuk tanggal 10/3/2013

Sedangkan untuk tanggal Y kita ingin mendapatkan YMax.

Nilai Maksimum untuk tanggal 8/16/2013

Berikut adalah contoh rumus array formula untuk mendapatkan max dari tanggal 10/3/2013 - diketik dulu rumusnya dan diakhiri penekanan tombol CTRL+SHIFT+ENTER. Rumus ini berlaku untuk kondisi data seperti pada gambar di atas.

=MAX(IF(B2:B10=DATEVALUE("10/3/2013"),D2:D10,0))

Dan jika kita menggunakan filter tanggal yang kita masukkan pada kolom F, maka kita masukkan formulanya kira-kira sebagai berikut (ingat diakhiri dengan penekanan tombol CTRL+SHIFT+ENTER).

=MAX(IF($B$2:$B$10=F2,$D$2:$D$10,0))

Penggunaan Formula dengan Filter pada kolom B (klik untuk memperbesar gambar)

Untuk Anda yang ingin mendownload filenya silahkan klik link di bawah ini, file tersebut terdapat di user group Facebook kami.

Array Formula - Mendapatkan Nilai Tertinggi dari Group Tanggal Yang Sama.xlsx

Semoga Bermanfaat !

Cara Menghitung Umur dengan Fungsi DATEDIF

DATEDIF adalah suatu fungsi pada Excel untuk menghitung perbedaan jarak antara kedua tanggal. Perhitungan yang sangat rumit ini - karena jumlah hari pada tiap bulan berbeda -  menjadi sangat sederhana dengan fungsi rumus ini.

Nah, pada artikel sebelumnya ditunjukkan cara yang cukup panjang untuk menghitung umur atau usia seseorang. Kali ini kita akan coba menggunakan rumus DATEDIF menghitung ulang usia seseorang berdasarkan tanggal lahirnya.

Syntax dari fungsi DATEDIF adalah sebagai berikut:

=DATEDIF(tanggal_1, tanggal_2, jenis_interval)

dimana:

• tanggal_1 adalah tanggal awal.
• tanggal_2 adalah tanggal akhir, dan tanggal akhir ini harus lebih besar nilainya dari tanggal_1.
• jenis_interval adalah teks yang menyatakan jenis interval atau jarak tanggal yang diinginkan, terdiri dari:
• d  :    menghitung total jarak hari secara keseluruhan.
• m :    menghitung total jarak bulan secara keseluruhan.
• y  : menghitung total jarak tahun secara keseluruhan.
• md: menghitung total jarak hari secara pada bulan dan tahun yang sama.
• yd: menghitung total jarak hari pada tahun yang sama.
• ym: menghitung total jarak bulan pada tahun yang sama.

Sebagai contoh, kita akan menghitung umur untuk bayi yang lahir pada tanggal 21-Maret-2007 dengan tanggal hari ini (tanggal penulisan adalah 3 Oktober 2013). Dengan berbagai jenis interval maka penggunaan rumus dan hasilnya adalah sebagai berikut:

• =DATEDIF("3/21/2007", today(),"d") akan menghasilkan nilai jarak total 2388 (hari)
• =DATEDIF("3/21/2007", today(),"m") akan menghasilkan nilai jarak total 78 (bulan)
• =DATEDIF("3/21/2007", today(),"y") akan menghasilkan nilai jarak total 6 (tahun)
• =DATEDIF("3/21/2007", today(),"md") akan menghasilkan nilai jarak 12 (hari) dengan anggapan ada pada  bulan dan tahun yang sama.
• =DATEDIF("3/21/2007", today(),"yd") akan menghasilkan nilai jarak 196 (hari) dengan anggapan ada pada tahun yang sama.
• =DATEDIF("3/21/2007", today(),"ym") akan menghasilkan nilai jarak 6 (bulan) dengan anggapan ada pada tahun yang sama.

Dan berikut adalah contoh screenshot lengkap untuk perhitungan usia untuk berbagai jarak tanggal. File tersebut dapat Anda download di user group Facebook. Semoga bermanfaat.

Hasil Perhitungan Umur dengan Fungsi DATEDIF

Cara Menghitung Umur dengan Excel berdasarkan Tanggal Lahir

Menghitung UMUR dan USIA dari informasi tanggal lahir adalah salah satu yang sering ditanyakan kepada penulis baik pada sesi training maupun ditemukan di forum-forum online.

Perhitungan usia yang kelihatannya gampang ini ternyata cukup kompleks karena tidak seperti perhitungan numerik, tanggal memiliki jumlah hari tiap bulan yang berbeda-beda (28, 29, 30 dan 31).

Berikut adalah screenshot solusi dari BelajarExcel.info dengan berbagai kombinasi tanggal lahir dan tanggal pengamatan.  Hasil N/A adalah karena tanggal pengamatan lebih kecil dari tanggal lahir.

Solusi ini menggunakan rumus IF dan fungsi tanggal YEAR, MONTH dan DATE. Selain itu Anda dapat menggunakan DATEDIF, namun kami akan tunjukkan di artikel lain. File ini dapat Anda download di koleksi file Excel user group kami.

Hasil Final Perhitungan Usia (klik untuk memperbesar tampilan gambar)

Dan berikut adalah rumus lengkapnya untuk perhitungan umur berdasarkan tahun, bulan dan hari.

C3 = TANGGAL PENGAMATAN
B3 = TANGGAL LAHIR

RUMUS UNTUK TAHUN
=================
=IF(C3>=B3,
IF(
OR(AND(DAY(C3)<DAY(B3),OR(MONTH(C3)-1=MONTH(B3),MONTH(C3)=MONTH(B3))), MONTH(C3)<MONTH(B3)),
IF(YEAR(C3)>YEAR(B3),YEAR(C3)-YEAR(B3)-1,YEAR(C3)-YEAR(B3)),
YEAR(C3)-YEAR(B3)
)
,"N/A ") & " tahun"

RUMUS UNTUK BULAN
=================
=IF(C3>=B3,
 IF(
  DAY(C3)<DAY(B3),
 IF(MONTH(C3)>MONTH(B3),MONTH(C3)-MONTH(B3)-1, MONTH(C3)+11-MONTH(B3)),
 IF(MONTH(C3)>=MONTH(B3),MONTH(C3)-MONTH(B3), MONTH(C3)+12-MONTH(B3))
  ), "N/A") & " bulan"

RUMUS UNTUK MENCARI HARI
========================
=IF(C3>=B3,
IF(
DAY(C3)>=DAY(B3), 
DAY(C3)-DAY(B3), 
C3-DATE(YEAR(C3),MONTH(C3)-1,DAY(B3))
), 
"N/A ") & " hari"

Info Tambahan: Kombinasi tanggal yang digunakan untuk testing tersebut dibuat berdasarkan matrix berikut.

Penggunaan ISBLANK pada Excel

Fungsi ISBLANK digunakan untuk melakukan pengecekan apakah suatu cell berisi nilai kosong atau null. Jika memang kosong atau null, maka akan mengembalikan nilai TRUE (BENAR), sebaliknya mengembalikan FALSE (SALAH).

Sebagai contoh, pada Gambar.1 berikut cell C3 berisi rumus =ISBLANK(B3). Rumus ini digunakan untuk melakukan pengecekan apakah cell B3 kosong. Ternyata cell B3 berisi teks "Satu", dengan demikian tidak kosong dan fungsi ISBLANK(B3) akan mengembalikan nilai FALSE.

Gambar.1 Contoh Pengecekan ISBLANK yang mengembalikan nilai FALSE

Sedangkan pada Gambar.2 berikut cell C4 berisi rumus =ISBLANK(B4). Rumus ini digunakan untuk melakukan pengecekan apakah cell B4 kosong. Dan ternyata hasilnya memang kosong. Dengan demikian fungsi ISBLANK(B4) akan mengembalikan nilai TRUE.

Gambar.2 Contoh Pengecekan ISBLANK yang mengembalikan nilai TRUE

Demikian contoh penggunaan sederhana dari ISBLANK. Semoga bisa bermanfaat bagi para pengunjung sekalian. Saran, kritik dan komentar apapun dengan senang hati kami terima demi kemajuan situs ini. Terima kasih.

Tanda $ (Absolute Reference) pada Excel

Simbol $ digunakan sebagai referensi absolut dari alamat cell (kolom ataupun baris)

Bagi kita yang baru bekerja dengan Excel, tanda $ yang terdapat pada rumus-rumus Excel terasa membingungkan. Kenapa simbol ini kadang muncul, kadang malah tidak sama sekali.

Artikel tutorial berikut ini mencoba menjawab hal tersebut denan memberikan contoh secara langsung, langkah demi langkah, apa dan bagaimana tanda $ atau absolute reference digunakan di dalam Excel 2007.

Download contoh file dari penggunaan_simbol_dollar.xlsx.

1. Jalankan program Microsoft Excel 2007 dan bukalah file penggunaan_simbol_dollar.xlsx yang telah Anda download tersebut.
2. Tempatkan cell pada alamat E2, ketikkan rumus = C2 * D2, dan tekan Enter. Hasil perkalian yang didapatkan adalah nilai 1,500,000.
3. Tempatkan cell kembali pada alamat E2, klik fill handle pada cell tersebut dan tarik (drag) sampai ke E4.
   
   
   
   Hasilnya adalah perkalian yang dinamis, dimana rumus yang di-copy dengan cara penarikan fill handle ke cell E3 dan E4 bukan berisi = C2 * D2, melainkan = C3 * D3 (pada cell E3) dan = C4 * D4 (pada cell E4).
   
   Ini menunjukkan bahwa Excel akan menyesuaikan alamat cell, dimana setiap perpindahan baris akibat dari drag fill handle akan mengakibatkan perpindahan nomor baris alamat pada rumus cell.
4. Sekarang tempatkan cell pada alamat F2, ketikkan rumus = C$2 * D$2, dan tekan Enter.
   
   
   
   Perhatikan kita memasukkan tanda $ pada alamat baris. Jika susah mengetik tanda dollar tersebut, tekan F4 dua kali sehingga tanda dollar akan berada pada posisi yang tepat.
5. Klik fill handle pada cell F2 dan tarik (drag) sampai ke F4.
   
   
   
   Terlihat bahwa semua cell (F3 dan F4) mengandung rumus perkalian dengan alamat baris yang tidak berubah (C2 dan D2). Ini dimungkinkan karena adanya tanda dollar pada alamat baris tersebut, sehingga ketika kita copy formula tersebut dengan fill handle, maka perpindahan baris tidak menghasilkan perubahan.
6. Selesai.

Berikut adalah keterangan sekaligus kesimpulan dari praktek di atas :

1. Tanpa penggunaan tanda dollar ($) pada alamat cell, maka duplikasi cell yang mengandung rumus dan alamat akan disesuaikan dengan perubahan baris (ataupun kolom).
   
   Penurunan 1 baris akan mengakibatkan penambahan alamat baris sebesar 1 cell, penurunan 2 baris akan mengakibatkan penambahan 2 cell, dan seterusnya.
2. Dengan penggunaan awalan tanda $ pada alamat cell, maka duplikasi cell tidak akan mengakibatkan perubahan alamat cell. Berikut adalah 3 contoh variasi penulisan prefix dan penjelasannya :
• A$1 : alamat kolom A bisa berubah sesuai duplikasi, tetapi alamat baris 1 akan tetap (absolut).
• $A1 : alamat kolom A tidak bisa berubah (absolut) tetapi alamat baris 1 bisa berubah.
• $A$1 : alamat kolom A maupun baris 1 tidak akan mengalami perubahan ketika diduplikasi ke cell lain.

Demikian artikel tutorial singkat tapi cukup padat mengenai penggunaan tanda $. Semoga bisa bermanfaat bagi kita semua.

Jenis-jenis File pada Excel - Mencakup Versi 2013

Hi Para Pengunjung BelajarExcel.info Ysh,

Topik hari ini adalah.... Apakah Anda sering bingung dengan jenis file Excel lain yang tersedia selain .xls dan .xlsx ?

Anda tidak sendirian kok, dan Anda tentunya juga penasaran tentang perbedaan dan manfaatnya. Untuk membantu Anda mengenali jenis-jenis file apa saja yang ada dan fungsinya, penulis mencoba membuat daftar catatan kecil mengenai berbagai tipe file penting Excel sebagai berikut:

Excel 2007 File Open Dialog

1. xls : file workbook pada Excel versi 5.0/95, 1997 - 2003.
2. xlsx : file workbook pada Excel 2007, 2010, dan 2013.
   Catatan : File ini tidak dapat menyimpan perintah macro.
3. xlsb : file workbook dalam bentuk binary pada Excel 2007, 2010, dan 2013. 
4. xlsm : file workbook dengan macro pada Excel versi 2007, 2010, dan 2013.
5. xlt : file template pada Excel versi 1997 - 2003.
6. xltx : file template pada (tanpa macro).
7. xltm : file template pada  Excel (dengan macro).
8. xla : adalah file plugin yang dibuat dengan VBA, terdapat pada Excel versi 2003, 2007, 2010, dan 2013.
9. xlam : workbook dengan tambahan add-in macro pada Excel versi 2007, 2010, dan 2013.
10. xml : file berformat XML (Extensible Markup Language).

Sumber Referensi

• http://office.microsoft.com/en-001/excel-help/file-formats-that-are-supported-in-excel-HP010014103.aspx

3 Hal Penting Penggunaan Tanggal di Excel

Perhitungan yang melibatkan tanggal dan jam merupakan pekerjaan yang tidak terhindarkan pada Excel. Namun ada 3 hal yang harus Anda ingat ketika bekerja dengan tanggal, sebagai berikut :

1. Rentang Waktu 0 Januari 1900 s/d 31 Desember 9999

Fakta ini hampir tidak ada yang tahu, dimana Excel hanya menerima tanggal yang dimulai 0 Januari 1900. Benar sekali, Anda tidak salah baca... tanggalnya dimulai dari 0 !

Tanggal awal ini adalah tanggal fiktif, yang kemudian dilanjutkan dengan 1 Januari 1900, 2 Januari 1900, dan seterusnya.

Sedangkan Tanggal akhir yang dapat diterima adalah tanggal 31 Desember 9999. Sangat cukup untuk penggunaan praktis.

Namun jika Anda ingin menghitung tanggal kelahiran tokoh-tokoh sejarah yang lahir sebelum 1900 dan juga perhitungan tanggal scientific yang mencapai jutaan tahun tentu tidak bisa dilakukan.

2. Memasukkan Tanggal Sesuai Regional Settings

Jika Anda memasukkan teks tanggal 12/08/2011, apakah ini berarti kita memasukkan entri 12 Agustus 2011 ? Atau 8 Desember 2011 ?

Entri pertama menggunakan format Indonesia, sedangkan entri kedua menggunakan format Amerika (US).

Ini adalah kesalahpahaman yang banyak terjadi di lapangan, terutama ketika komputer yang digunakan berbeda konfigurasi pada Regional Settings. 

Regional Settings ini dapat Anda akses pada control panel Windows. Pastikan bahwa kolaborasi dokumen Anda dilakukan pada konfigurasi yang sama.

Dialog Regional Settings

3. Memasukkan Tanggal menggunakan Angka Bilangan Bulat (integer)

Tanggal pada Excel dapat dimasukkkan dengan angka bulat (integer) yang dimulai dari 0 s/d 2,958,465. Dimana angka 0 mewakili 0 Januari 1900, dan 2,958,465 mewakili 31 Desember 9999.

Bacaan Lainnya

• Rumus / Formula pada Excel 2007

Merubah Tampilan PivotTable Excel 2007 Menyerupai Excel 2003

Untuk Anda yang sudah terbiasa menangani data dengan Excel 2003 ke bawah, tentunya akan bingung dengan tampilan baru Excel 2007. Salah satunya adalah tampilan Pivot Table.

Pada tampilan standar PivotTable di Excel 2007, kita tidak dapat :

• men-drag and drop field dari area PivotTable
• mengatur filter dan sorting dari area PivotTable

dimana semuanya harus dilakukan dari panel PivotTable Field List.

Hal ini tentunya cukup mengganggu, namun jangan khawatir karena pada Excel 2007 kita dapat merubah tampilan PivotTable ini menjadi seperti versi sebelumnya atau kembali ke classic view.

Berikut adalah langkah-langkahnya :

1. Klik area PivotTable kita
2. Pada Ribbon, pilih tab PivotTable Tools | Options.
   
   
3. Pada group PivotTable, klik  Options | Options.
   
   
4. Pada dialog PivotTable Options, klik tab "Display" dan pastikan opsi Classic PivotTable layout (enables dragging of fields in the grid) dipilih.
   
   
5. Klik tombol OK.
6. Kini tampilan PivotTable Anda akan sama dengan tampilan Excel versi 2003 ke bawah.
   
   
7. Selesai.