Fungsi - Fungsi Pada MS Excel
Friday, April 5, 2013
Edit
Fungsi Row dan Rows di Excel
Pada posting kali ini saya akan mengupas tentang fungsi Row dan Rows pada Microsoft Excel.
1. Fungsi Row()
Fungsi Row pada Microsoft Excel adalah untuk mengambil data baris dari suatu referensi.
Syntax:
=Row([Referensi])
Contoh:
Rumus =Row(C4) akan menghasilkan nilai 4.
Rumus =Row() akan menghasilkan nilai di mana rumus tersebut ditulis. Misalnya rumus ini ditulis pada baris ke 7 maka nilai =Row() adalah 7.
2. Fungsi Rows()
Fungsi Rows pada Microsoft Excel adalah untuk menghitung jumlah baris dari suatu range atau array.
Syntax:
=Rows([Referensi])
Referensi dapat berupa range atau data array
Contoh:
Rumus =ROWS(C1:E4) menghasilkan nilai 4.
Rumus =ROWS({1,2,3;4,5,6}) akan meghasilkan nilai 2.
Fungsi EDATE (EDATE Function)
Anda dapat menggunakan fungsi EDATE untuk menambah atau mengurangi bulanke atau dari tanggal.
Fungsi EDATE membutuhkan dua nilai (juga disebut sebagai argumen): tanggalmulai dan jumlah bulan yang Anda ingin menambah atau mengurangi. Untukmengurangi bulan, masukkan angka negatif sebagai argumen kedua (misalnya, =EDATE ("2/15/10", -5). Hasil rumus ini adalah tanggal 9/15/09.
Anda dapat menentukan nilai dari tanggal mulai baik dengan mengacu ke sel yang berisi nilai tanggal atau dengan memasukkan tanggal dalam tanda kutip, seperti "2/15/10".
Sebagai contoh, misalkan Anda ingin menambahkan 16 bulan kedata tanggal 16 Oktober 2009.
Fungsi EDATE membutuhkan dua nilai (juga disebut sebagai argumen): tanggalmulai dan jumlah bulan yang Anda ingin menambah atau mengurangi. Untukmengurangi bulan, masukkan angka negatif sebagai argumen kedua (misalnya, =EDATE ("2/15/10", -5). Hasil rumus ini adalah tanggal 9/15/09.
Anda dapat menentukan nilai dari tanggal mulai baik dengan mengacu ke sel yang berisi nilai tanggal atau dengan memasukkan tanggal dalam tanda kutip, seperti "2/15/10".
Sebagai contoh, misalkan Anda ingin menambahkan 16 bulan kedata tanggal 16 Oktober 2009.
- Pada sel A5, isikan data 10/16/09.
- Pada sel B5, isikan rumus =EDATE(A5,16).Fungsi menggunakan nilai dalamsel A5 sebagai tanggal.
- Pada sel C5, isikan rumurs =EDATE("10/16/09", 16).Dalam hal ini, fungsimenggunakan nilai tanggal yang Anda masukkan secara langsung,"10/16/09."Sel B5 dan C5 berdua harus menunjukkan tanggal2/16/11.Catatan Tergantung pada format sel yang berisi formula yang Anda masukkan, Excel akan menampilkan hasil sebagai nomor seri, dalam hal ini,2/16/11 mungkin ditampilkan sebagai 40590. Excel menyimpan tanggalsebagai nomor seri berurutan sehingga mereka dapat digunakan dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor urut 1, dan 1 Januari 2010 adalah nomor urut 40.179 karena 40.178 hari setelah 1 Januari1900.
- Jika hasil Anda muncul sebagai nomor seri, pilih B5 sel dan C5 dan lanjutkan dengan langkah-langkah berikut.
- Pada tab Home, dalam kelompok Cells, klik Format, kemudian klik Format Cells.
- Pada Format Cells kotak dialog, klik tab Number.
- Pada Category, klik Date, dan kemudian klik OK. Nilai di setiap sel akan muncul sebagai tanggal bukan dari nomor seri.
Fungsi Workday di Excel
Fungsi Workday di Excel menghasilkan angka yang mewakili tanggal yang menunjukkan jumlah hari kerja sebelum atau setelah tanggal (tanggal awal). Hari kerja mengecualikan akhir pekan dan setiap tanggal diidentifikasi sebagai hari libur. Gunakan hari kerja untuk mengecualikan akhir pekan atau hari libur ketika Anda menghitung tanggal jatuh tempo faktur, waktu pengiriman yang diharapkan, atau jumlah hari pekerjaan yang dilakukan.
Sintaks
WORKDAY(tanggal_mulai, hari, libur)
Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
Tanggal_mulai adalah tanggal yang merupakan tanggal mulai.
Hari adalah jumlah hari nonweekend dan nonholiday sebelum atau setelah tanggal_mulai. Sebuah nilai positif bagi hari menghasilkan tanggal di masa mendatang, sebuah nilai negatif menghasilkan tanggal masa lalu.
Libur adalah daftar opsional dari satu atau lebih tanggal untuk mengecualikan dari kalender kerja. Daftar ini dapat berupa kisaran sel yang berisi tanggal atau array konstanta (array:. Digunakan untuk membangun formula tunggal yang menghasilkan beberapa hasil atau yang beroperasi pada sekelompok argumen yang diatur dalam baris dan kolom Sebuah saham rentang sebuah array rumus umum; sebuah array konstan adalah sekelompok konstanta yang digunakan sebagai argumen) dari nomor seri yang mewakili tanggal.
Keterangan
Contoh
=WORKDAY(A2,A3)
Akan menghasilkan nilai 4/30/2009, jika cell A2 bernilai 10/01/2008 dan A3 bernilai 151.
Sintaks
WORKDAY(tanggal_mulai, hari, libur)
Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
Tanggal_mulai adalah tanggal yang merupakan tanggal mulai.
Hari adalah jumlah hari nonweekend dan nonholiday sebelum atau setelah tanggal_mulai. Sebuah nilai positif bagi hari menghasilkan tanggal di masa mendatang, sebuah nilai negatif menghasilkan tanggal masa lalu.
Libur adalah daftar opsional dari satu atau lebih tanggal untuk mengecualikan dari kalender kerja. Daftar ini dapat berupa kisaran sel yang berisi tanggal atau array konstanta (array:. Digunakan untuk membangun formula tunggal yang menghasilkan beberapa hasil atau yang beroperasi pada sekelompok argumen yang diatur dalam baris dan kolom Sebuah saham rentang sebuah array rumus umum; sebuah array konstan adalah sekelompok konstanta yang digunakan sebagai argumen) dari nomor seri yang mewakili tanggal.
Keterangan
- Microsoft Excel menyimpan tanggal sebagai nomor seri urut sehingga mereka dapat digunakan dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor urut 1, dan 1 Januari 2008 adalah nomor seri 39448 karena 39448 hari setelah tanggal 1 Januari 1900. Microsoft Excel untuk Macintosh menggunakan sistem tanggal yang berbeda sebagai default.
- Jika argumen bukan tanggal yang valid, WORKDAY menghasilkan error #VALUE!.
- Jika tanggal_mulai ditambah hari menghasilkan tanggal tidak valid, rumus WORKDAY akan menghasilkan error NUM #!.
- Jika hari bukan integer, akan dibulatkan.
Contoh
=WORKDAY(A2,A3)
Akan menghasilkan nilai 4/30/2009, jika cell A2 bernilai 10/01/2008 dan A3 bernilai 151.
Fungsi WEEKDAY di Excel
Fungsi WEEKDAY mengembalikan hari minggu sesuai dengan tanggal. Hari diberikan sebagai integer, mulai dari 1 (Minggu) sampai 7 (Sabtu), secara default.
Sintax:
WEEKDAY(serial_number,return_type)
Serial_number adalah nomor urut yang merupakan tanggal hari Anda mencoba untuk menemukan. Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
Return_type adalah angka yang menentukan jenis nilai kembali.
Catatan:
Microsoft Excel menyimpan tanggal sebagai nomor seri urut sehingga mereka dapat digunakan dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor urut 1, dan 1 Januari 2008 adalah nomor seri 39448 karena 39448 hari setelah tanggal 1 Januari 1900. Microsoft Excel untuk Macintosh menggunakan sistem tanggal yang berbeda sebagai default.
Sintax:
WEEKDAY(serial_number,return_type)
Serial_number adalah nomor urut yang merupakan tanggal hari Anda mencoba untuk menemukan. Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
Return_type adalah angka yang menentukan jenis nilai kembali.
| Return_type | Hasil |
|---|---|
| 1 | Nomor 1 (Minggu) sampai 7 (Sabtu). Berperilaku seperti versi sebelumnya dari Microsoft Excel. |
| 2 | Nomor 1 (Senin) sampai 7 (Minggu) |
| 3 | Bilangan 0 (Senin) melalui 6 (Minggu). |
Catatan:
Microsoft Excel menyimpan tanggal sebagai nomor seri urut sehingga mereka dapat digunakan dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor urut 1, dan 1 Januari 2008 adalah nomor seri 39448 karena 39448 hari setelah tanggal 1 Januari 1900. Microsoft Excel untuk Macintosh menggunakan sistem tanggal yang berbeda sebagai default.
Fungsi EOMONTH di Excel
Fungsi EOMONTH menghasilkan nilai untuk hari terakhir dari bulan yang ditunjukkan adalah jumlah bulan sebelum atau setelah tanggal_mulai.
Sintax:
EOMONTH(start_date,months)
start_date adalah tanggal yang merupakan tanggal mulai. Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
months adalah jumlah bulan sebelum atau setelah start_date . Nilai positif selama berbulan-bulan menghasilkan tanggal di masa mendatang, nilai negatif menghasilkan tanggal masa lalu.
Jika months tidak integer, akan dibulatkan.
Keterangan
= EOMONTH (A2, 1)
Jika nilai pada cell A2 adalah 01/01/2008, maka hasil dari rumus di atas adalah 29 Februari 2008
Sintax:
EOMONTH(start_date,months)
start_date adalah tanggal yang merupakan tanggal mulai. Tanggal harus dimasukkan dengan menggunakan fungsi DATE, atau sebagai hasil dari formula atau fungsi lainnya. Misalnya, gunakan DATE (2008,5,23) untuk tanggal 23 Mei 2008. Masalah dapat terjadi bila tanggal dimasukkan sebagai teks.
months adalah jumlah bulan sebelum atau setelah start_date . Nilai positif selama berbulan-bulan menghasilkan tanggal di masa mendatang, nilai negatif menghasilkan tanggal masa lalu.
Jika months tidak integer, akan dibulatkan.
Keterangan
- Microsoft Excel meyimpan tanggal sebagai nomor seri urut sehingga mereka dapat digunakan dalam perhitungan. Secara default, 1 Januari 1900 adalah nomor urut 1, dan 1 Januari 2008 adalah nomor seri 39448 karena 39448 hari setelah tanggal 1 Januari 1900. Microsoft Excel untuk Macintosh menggunakan sistem tanggal yang berbeda sebagai default.
- Jika start_date bukan tanggal yang valid, EOMONTH mengembalikan NUM #!.
- Jika start_date menghasilkan bulan ditambah tanggal tidak valid, EOMONTH mengembalikan # NUM!.
= EOMONTH (A2, 1)
Jika nilai pada cell A2 adalah 01/01/2008, maka hasil dari rumus di atas adalah 29 Februari 2008
Fungsi IsBlank di Excel
Jika anda perlu untuk memeriksa apakah sebuah cell berisi data atau tidak anda dapat menggunakan fungsi IsBlank.
Cara penulisan fungsi IsBlank:
ISBLANK(Value)
Argumen Value adalah cell atau name yang merefer ke cell yang akan diperksa.
Contoh:
=ISBLANK(B12)
Hasil output bernilai TRUE jika cell B12 kosong dan FALSE jika cell B12 berisi suatu karakter, angka atau apapun selain kosong.
Cara penulisan fungsi IsBlank:
ISBLANK(Value)
Argumen Value adalah cell atau name yang merefer ke cell yang akan diperksa.
Contoh:
=ISBLANK(B12)
Hasil output bernilai TRUE jika cell B12 kosong dan FALSE jika cell B12 berisi suatu karakter, angka atau apapun selain kosong.
Fungs Concatenate di Excel
Fungsi Concatenate berguna untuk menggabungkan beberapa text string menjadi satu text string.
Cara penulisan fungsi Concatenat:
=CONCATENATE(Text1,Text2,Text3,...)
Argumen Text1, Text2, Text3, .... adalah 1 sampai 30 text string yang akan digabungkan menjadi satu text string dan dapat berupa text string, angka atau single-cell references.
Contoh 1:
=CONCATENATE("Saya"," ","suka"," ","makan"," ","nasi"," ","goreng")
Hasil output fungsi contoh 1 adalah "Saya suka makan nasi goreng"
Contoh 2:
Jika anda ingin menggabungkan text dalam beberapa cell dapat dituliskan dengan contoh berikut:
=CONCATENATE(D19,E19,D20,E19,D21)
Pada contoh dua ini cell D19,E19,D20,E19 dan D21 digabungkan dalam satu cell.
Cara penulisan fungsi Concatenat:
=CONCATENATE(Text1,Text2,Text3,...)
Argumen Text1, Text2, Text3, .... adalah 1 sampai 30 text string yang akan digabungkan menjadi satu text string dan dapat berupa text string, angka atau single-cell references.
Contoh 1:
=CONCATENATE("Saya"," ","suka"," ","makan"," ","nasi"," ","goreng")
Hasil output fungsi contoh 1 adalah "Saya suka makan nasi goreng"
Contoh 2:
Jika anda ingin menggabungkan text dalam beberapa cell dapat dituliskan dengan contoh berikut:
=CONCATENATE(D19,E19,D20,E19,D21)
Pada contoh dua ini cell D19,E19,D20,E19 dan D21 digabungkan dalam satu cell.
Fungsi DateValue di Excel
Fungsi DateValue mengkonversi data tanggal dalam format text ke angka yang merepresentasikan tanggal dalam Microsoft Excel date-time code.
Cara penulisan fungsi DateValue:
DATEVALUE(Date_text)
Argumen Date_text adalah data text yang menunjukkan tanggal dalam Microsoft Excel date format antara 1/1/1900 (Windows) atau 1/1/1904 (Macintosh) dan 12/31/9999
Contoh 1 :
Silakan anda ketikkan rumus ini pada Excel =DATEVALUE("1/1/1900"). Hasil output dari fungsi ini adalah angka 1.
Contoh 2 :
=DATEVALUE(TEXT(NOW(),"m/d/yyyy"))
Pada contoh dua akan menghasilkan angka yang merupakan urutan hari keberapa saat ini dihitung sejak tanggal 1/1/1900.
Cara penulisan fungsi DateValue:
DATEVALUE(Date_text)
Argumen Date_text adalah data text yang menunjukkan tanggal dalam Microsoft Excel date format antara 1/1/1900 (Windows) atau 1/1/1904 (Macintosh) dan 12/31/9999
Contoh 1 :
Silakan anda ketikkan rumus ini pada Excel =DATEVALUE("1/1/1900"). Hasil output dari fungsi ini adalah angka 1.
Contoh 2 :
=DATEVALUE(TEXT(NOW(),"m/d/yyyy"))
Pada contoh dua akan menghasilkan angka yang merupakan urutan hari keberapa saat ini dihitung sejak tanggal 1/1/1900.
Fungsi And di Excel
Fungsi And di Excel adalah fungsi untuk memeriksa suatu kondisi atau beberapa kondisi apakah benar, jika kondisi benar semua maka fungsi akan menghasilkan nilai TRUE. Jika kondisi lebih dari satu dan salah satu saja bernilai FALSE, maka fungsi akan mengembalikan nilai FALSE.
Cara penulisan fungsi And:
AND(logical1,logical2,....)
Argumen logical1,logical2,.... adalah kondisi yang akan di test dapat berupa TRUE, FALSE, dan dapat berupa nilai logikal, arrays, atau referensi.
Contoh penggunaan fungsi And
=AND(ISNONTEXT(1),ISERROR(2/0))
Hasil dari fungsi And pada contoh di atas adalah TRUE karena fungsi ISNONTEXT(1) menghasilkan TRUE dan fungsi ISERROR(2/0)
=AND(ISNONTEXT(1),ISERROR(2/0),ISNA(5))
Hasil dari fungsi And pada contoh kedua adalah FALSE karena fungsi ISNONTEXT(1) menghasilkan TRUE dan fungsi ISERROR(2/0) sedangkan ISNA(5) bernilai FALSE.
Cara penulisan fungsi And:
AND(logical1,logical2,....)
Argumen logical1,logical2,.... adalah kondisi yang akan di test dapat berupa TRUE, FALSE, dan dapat berupa nilai logikal, arrays, atau referensi.
Contoh penggunaan fungsi And
=AND(ISNONTEXT(1),ISERROR(2/0))
Hasil dari fungsi And pada contoh di atas adalah TRUE karena fungsi ISNONTEXT(1) menghasilkan TRUE dan fungsi ISERROR(2/0)
=AND(ISNONTEXT(1),ISERROR(2/0),ISNA(5))
Hasil dari fungsi And pada contoh kedua adalah FALSE karena fungsi ISNONTEXT(1) menghasilkan TRUE dan fungsi ISERROR(2/0) sedangkan ISNA(5) bernilai FALSE.
Fungsi IsNonText di Excel
Fungsi IsNonText adalah fungsi untuk memeriksa sebuah nilai atau cell apakah non text (cell kosong adalah non text) dan menghasilkan nilai TRUE atau FALSE.
Cara penulisan fungsi IsNonText
IsNonText(value)
Argumen value dapat berupa suatu nilai atau alamat cell.
Nilai dari rumus =ISNONTEXT(I26) akan TRUE jika cell I26 kosong atau kita isi dengan angka, dan akan bernilai FALSE jika kita isi dengan hruf atau tulisan.
Cara penulisan fungsi IsNonText
IsNonText(value)
Argumen value dapat berupa suatu nilai atau alamat cell.
Nilai dari rumus =ISNONTEXT(I26) akan TRUE jika cell I26 kosong atau kita isi dengan angka, dan akan bernilai FALSE jika kita isi dengan hruf atau tulisan.
Fungsi IsNumber di Excel
Fungsi IsNumber di Excel adalah fungsi untuk memeriksa apakah suatu nilai atau isi suatu cell bertipe numerik atau bukan.
Cara penulisan fungsi IsNumber adalah:
IsNumber(value)
Di mana value dapat berupa suatu nilai atau alamat cell.
Hasil output dari fungsi IsNumber ada TRUE atau FALSE. Nilai hasil akan bernilaiTRUE jika argumen value adalah data dengan tipe numerik dan akan bernilai FALSEjika value bukan data numerik.
=IsNumber(12) akan bernilai TRUE sedangkan =IsNumber(y) akan bernilai FALSE
=IsNumber(B12) akan bernilai TRUE jika kita isi cell B12 dengan angka dan akan bernilai FALSE jika kita isi dengan data huruf.
Cara penulisan fungsi IsNumber adalah:
IsNumber(value)
Di mana value dapat berupa suatu nilai atau alamat cell.
Hasil output dari fungsi IsNumber ada TRUE atau FALSE. Nilai hasil akan bernilaiTRUE jika argumen value adalah data dengan tipe numerik dan akan bernilai FALSEjika value bukan data numerik.
=IsNumber(12) akan bernilai TRUE sedangkan =IsNumber(y) akan bernilai FALSE
=IsNumber(B12) akan bernilai TRUE jika kita isi cell B12 dengan angka dan akan bernilai FALSE jika kita isi dengan data huruf.
Fungsi IsErr dan IsError pada Excel
Untuk mengetahui apakah nilai suatu cell error atau tidak anda dapat menggunakan fungsi IsErr atau IsError.
Cara penulisan fungsi IsError adalah:
IsErr(value) atau IsError(value)
Value dapat berupa suatu nilai atau alamat cell.
Perbedaan antara IsErr dan IsError adalah:
Contoh penggunaan:
Misalnya anda mempunyai rumus pembagian dalam kolom Persentase yang membandingkan kolom jumlah realisasi dibagi kolom jumlah
rencana dengan rumus =C16/D16. Jika nilai pada kolom rencana 0 (cell D16), maka akan muncul error #DIV/0!.
Untuk mencegah muncul #DIV/0! maka anda dapat menuliskan rumus menjadi:
=IF(ISERR(C16/D16),0,C16/D16)
Yang artinya jika rumus C16/D16 menghasilkan error maka tampilkan angka 0, tapi jika tidak error tampilkan hasil perhitungan C16/D16.
Cara penulisan fungsi IsError adalah:
IsErr(value) atau IsError(value)
Value dapat berupa suatu nilai atau alamat cell.
Perbedaan antara IsErr dan IsError adalah:
- IsErr memeriksa apakah value bernilai error (#VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL) tidak termasuk #N/A dan menghasilkan TRUE atau FALSE.
- IsError memeriksa apakah value bernilai error ( #N/A , #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL) menghasilkan TRUE atau FALSE.
Contoh penggunaan:
Misalnya anda mempunyai rumus pembagian dalam kolom Persentase yang membandingkan kolom jumlah realisasi dibagi kolom jumlah
rencana dengan rumus =C16/D16. Jika nilai pada kolom rencana 0 (cell D16), maka akan muncul error #DIV/0!.
Untuk mencegah muncul #DIV/0! maka anda dapat menuliskan rumus menjadi:
=IF(ISERR(C16/D16),0,C16/D16)
Yang artinya jika rumus C16/D16 menghasilkan error maka tampilkan angka 0, tapi jika tidak error tampilkan hasil perhitungan C16/D16.
Fungsi IsNa di Excel
Apakah anda pernah mengalami masalah #N/A pada file Excel ?
Jika Ya jawabannya, maka anda tidak seorang diri menghadapi masalah ini. Jika anda membuat report dengan Excel dan pada report muncul tulisan #N/A tentu terlihat kurang bagus.
Error #N/A terjadi jika kita membuat rumus dengan referensi nilai tertentu yang merujuk pada data lain di mana referensi yang dicari tidak ada pada data yang dirujuk. Misalnya anda menulis rumus vlookup sebagai berikut:
=VLOOKUP(A7,daftar,2,FALSE)
Ternyata nilai yang dicari (nilai di A7) tidak ada pada name daftar, maka akan muncul tampilan #N/A (artinya Not Available).
Anda dapat merubahnya secara manual pada rumus-rumus tersebut dengan cara mengeditnya atau mengganti rumus dengan nilai tertentu. Jika data yang harus diedit banyak tentu akan merepotkan.
Untuk mengatasi masalah ini dengan mudah kita dapat menggunakan kombinasi fungsi IF() dan ISNA(). Cara penggunaan fungsi IF() dan ISNA() untuk mengatasi kasus di atas adalah sebagai berikut:
=IF(ISNA(VLOOKUP(A7,daftar,2,FALSE)),"",VLOOKUP(A7,daftar,2,FALSE))
Penjelasan:
Fungsi IF(Kondisi,JikaBenar,JikaSalah).
Jika kondisi rumus Vlookup() nilainya #N/A, tampilkan nilai kosong ("") jika tidak maka tampilkan hasil rumus VLOOKUP(A7,daftar,2,FALSE)
Jika Ya jawabannya, maka anda tidak seorang diri menghadapi masalah ini. Jika anda membuat report dengan Excel dan pada report muncul tulisan #N/A tentu terlihat kurang bagus.
Error #N/A terjadi jika kita membuat rumus dengan referensi nilai tertentu yang merujuk pada data lain di mana referensi yang dicari tidak ada pada data yang dirujuk. Misalnya anda menulis rumus vlookup sebagai berikut:
=VLOOKUP(A7,daftar,2,FALSE)
Ternyata nilai yang dicari (nilai di A7) tidak ada pada name daftar, maka akan muncul tampilan #N/A (artinya Not Available).
Anda dapat merubahnya secara manual pada rumus-rumus tersebut dengan cara mengeditnya atau mengganti rumus dengan nilai tertentu. Jika data yang harus diedit banyak tentu akan merepotkan.
Untuk mengatasi masalah ini dengan mudah kita dapat menggunakan kombinasi fungsi IF() dan ISNA(). Cara penggunaan fungsi IF() dan ISNA() untuk mengatasi kasus di atas adalah sebagai berikut:
=IF(ISNA(VLOOKUP(A7,daftar,2,FALSE)),"",VLOOKUP(A7,daftar,2,FALSE))
Penjelasan:
Fungsi IF(Kondisi,JikaBenar,JikaSalah).
Jika kondisi rumus Vlookup() nilainya #N/A, tampilkan nilai kosong ("") jika tidak maka tampilkan hasil rumus VLOOKUP(A7,daftar,2,FALSE)
Rumus Hitung Waktu di Excel
Untuk melakukan perhitungan waktu baik hari, bulan, maupun tahun anda dapat menggunakan fungsi Date dari Microsoft Excel. Fungsi date memiliki 3 buah parameter yaitu year, month dan day.
Pada posting kali ini saya akan memberikan tips dan contoh kasus penggunaan fungsi Date menggunakan Microsoft Excel untuk perhitungan waktu yang akan datang.
Sebagai contoh kasus adalah menentukan waktu kadaluarsa dari suatu barang berdasarkan tanggal produksi barang tersebut. Misalnya suatu barang diproduksi pada tangga 2 Januari 2009 akan kadaluarsa pada satu tahun yang akan datang yaitu 2 Januari 2010.
Kita akan membuat tabel di excel dengan kolom-kolom Nomor, Nama Barang, Tanggal Produksi dan Tanggal Kadaluarsa. Misalkan judul kolom Nomor ada di A1, Nama Barang di B1, Tanggal Produksi di C1 dan Tanggal Kadaluarsa di D1.
Kolom Nomor diisi nomor urut, kolom Nama Barang diisi nama barang, kolom Tanggal Produksi diisi tanggal produksi dan kolom Tanggal Kadaluarsa di cell D2 diisi rumus =DATE(YEAR(C2)+1,MONTH(C2),DAY(C2))
Keterangan rumus:
Rumus di atas adalah untuk menghitung satu tahun dari tanggal produksi dengan parameter:
Begitu juga jika waktu kadaluarsa ditentukan dalam satuan hari misalnya 200 hari, maka rumus pada kolom tanggal kadaluarsa adalah: =DATE(YEAR(C2),MONTH(C2),DAY(C2)+200)
Pada posting kali ini saya akan memberikan tips dan contoh kasus penggunaan fungsi Date menggunakan Microsoft Excel untuk perhitungan waktu yang akan datang.
Sebagai contoh kasus adalah menentukan waktu kadaluarsa dari suatu barang berdasarkan tanggal produksi barang tersebut. Misalnya suatu barang diproduksi pada tangga 2 Januari 2009 akan kadaluarsa pada satu tahun yang akan datang yaitu 2 Januari 2010.
Kita akan membuat tabel di excel dengan kolom-kolom Nomor, Nama Barang, Tanggal Produksi dan Tanggal Kadaluarsa. Misalkan judul kolom Nomor ada di A1, Nama Barang di B1, Tanggal Produksi di C1 dan Tanggal Kadaluarsa di D1.
Kolom Nomor diisi nomor urut, kolom Nama Barang diisi nama barang, kolom Tanggal Produksi diisi tanggal produksi dan kolom Tanggal Kadaluarsa di cell D2 diisi rumus =DATE(YEAR(C2)+1,MONTH(C2),DAY(C2))
Keterangan rumus:
Rumus di atas adalah untuk menghitung satu tahun dari tanggal produksi dengan parameter:
- YEAR(C2)+1 adalah menghitung 1 tahun dari tanggal produksi.
- MONTH(C2) adalah menentukan bulan kadaluarsa sesuai dengan bulan produksi.
- DAY(C2) adalah menentukan hari kadaluarsa sesuai dengan hari produksi.
Begitu juga jika waktu kadaluarsa ditentukan dalam satuan hari misalnya 200 hari, maka rumus pada kolom tanggal kadaluarsa adalah: =DATE(YEAR(C2),MONTH(C2),DAY(C2)+200)
Fungsi Substitute() Microsoft Excel
Fungsi Substitute() adalah fungsi untuk mengganti suatu kata dengan kata yang lain dalam suatu expresi atau referensi cell.
Cara penulisan fungsi Substitute :
Substitute(text,text_lama,text_baru,urutan)
Parameter:
Text adalah huruf atau kata atau kalimat atau referensi cell.
Text_lama adalah huruf atau kata atau kalimat yang akan diganti.
Text_baru adalah huruf atau kata atau kalimat baru yang akan mengganti text lama.
Urutan adalah urutan ke berapa text yang diganti oleh text baru
Contoh penggunaan fungsi Substitute:
Misalnya data pada cell A2 berisi text “Sales Data”
Cell A3 berisi text “Quarter 1, 2008”
Cell A4 berisi text “Quarter 1, 2011”
Formula:
=SUBSTITUTE(A2, "Sales", "Cost") menghasilkan text ” Cost Data”
=SUBSTITUTE(A3, "1", "2", 1) menghasilkan “Quarter 2, 2008”
=SUBSTITUTE(A4, "1", "2", 3) menghasilkan “Quarter 1, 2012” angka 1 pada urutan ke 3 diganti dengan angka 2
Cara penulisan fungsi Substitute :
Substitute(text,text_lama,text_baru,urutan)
Parameter:
Text adalah huruf atau kata atau kalimat atau referensi cell.
Text_lama adalah huruf atau kata atau kalimat yang akan diganti.
Text_baru adalah huruf atau kata atau kalimat baru yang akan mengganti text lama.
Urutan adalah urutan ke berapa text yang diganti oleh text baru
Contoh penggunaan fungsi Substitute:
Misalnya data pada cell A2 berisi text “Sales Data”
Cell A3 berisi text “Quarter 1, 2008”
Cell A4 berisi text “Quarter 1, 2011”
Formula:
=SUBSTITUTE(A2, "Sales", "Cost") menghasilkan text ” Cost Data”
=SUBSTITUTE(A3, "1", "2", 1) menghasilkan “Quarter 2, 2008”
=SUBSTITUTE(A4, "1", "2", 3) menghasilkan “Quarter 1, 2012” angka 1 pada urutan ke 3 diganti dengan angka 2
Fungsi Find() Microsoft Excel
Fungsi find() pada Microsoft Excel adalah fungsi untuk mencari suatu huruf atau text dari suatu cell dan menghasilkan angka urutan ke berapa huruf tersebut pada cell yang dicari.
Cara penulisan Fungsi Find():
Find(text_cari, text_sumber, awal_pencarian)
Misalkan pada cell A2 ada tulisan "Miriam McGovern" (tanda tanda petik).
Maka:
Cara penulisan Fungsi Find():
Find(text_cari, text_sumber, awal_pencarian)
- text_cari adalah huruf atau text yang dicari pada text_sumber.
- text_sumber adalah text atau kalimat dimana huruf atau text_cari akan dicari.
- awal_pencarian adalah nomor urut keberapa pencarian mulai dilakukan, jika parameter ini kosong maka pencarian dilakukan dari awal
Misalkan pada cell A2 ada tulisan "Miriam McGovern" (tanda tanda petik).
Maka:
- Formula =Find("M", A2) akan menghasilkan nilai 1
- Formula =Find("m",A2) akan menghasilkan nilai 6
- Formula =Find("M", A2, 3) akan menghasilkan nilai 8
Fungsi Abs() Microsoft Excel
Fungsi ABS() akan mengembalikan nilai absolute dari sebuah angka. Nilai absolut adalah nilai dari angka tanpa tanda (tanda minus jika angka bernilai negatif).
Cara penulisan
ABS(angka)
Ketikkan rumus berikut pada cell di Excel:
=ABS(-4)
Hasil yang muncul pada cell tersebut adalah angka 4.
Hasil yang sama dengan rumus diatas adalah rumus berikut:
=ABS(4)
Jadi ABS(-4)=ABS(4)
Jika anda tuliskan rumus “=ABS(-4)=ABS(4)” tanpa tanda petik dalam sebua cell, maka cell akan berisi TRUE.
Cara penulisan
ABS(angka)
- angka adalah angka yang akan anda inginkan data absolutnya.
Ketikkan rumus berikut pada cell di Excel:
=ABS(-4)
Hasil yang muncul pada cell tersebut adalah angka 4.
Hasil yang sama dengan rumus diatas adalah rumus berikut:
=ABS(4)
Jadi ABS(-4)=ABS(4)
Jika anda tuliskan rumus “=ABS(-4)=ABS(4)” tanpa tanda petik dalam sebua cell, maka cell akan berisi TRUE.
Fungsi Ceiling() Microsoft Excel
Fungsi Ceiling() akan menghasilkan nilai pembulatan ke atas, ke nilai perkalian atau kelipatan terdekat dari signifikan. Misalnya jika anda ingin membulatkan angka ke kelipatan 0.5 maka anda dapat gunakan rumus =Ceiling(2.4,0.5)
Cara penulisan fungsi Ceiling adalah:
CEILING(angka_dibulatkan, signifikan)
Contoh penggunaan fungsi Ceiling():
Cara penulisan fungsi Ceiling adalah:
CEILING(angka_dibulatkan, signifikan)
- angka_dibulatkan adalah angka yang akan dibulatkan oleh rumus Ceiling
- signifikan adalah nilai kelipatan pembulatan terdekat
Contoh penggunaan fungsi Ceiling():
| Rumus | Keterangan (Hasil) |
| =CEILING(2.5, 1) | Membulatkan 2.5 ke atas terdekat dengan perkalian 1 (3) |
| =CEILING(-2.5, -2) | Membulatkan -2.5 ke atas terdekat dengan perkalian -2 (-4) |
| =CEILING(-2.5, 2) | Hasilnya error, karena -2.5 dan 2 mempunyai tanda yang berbeda (#NUM!) |
| =CEILING(1.5, 0.1) | Membulatkan 1.5 ke atas terdekat dengan perkalian of 0.1 (1.5) |
| =CEILING(0.234, 0.01) | Membulatkan 0.234 ke atas terdekat dengan perkalian 0.01 (0.24) |
Fungsi Int() Microsoft Excel
Fungsi Int() adalah untuk membulatkan angka ke bawah ke nilai integer terdekat.
Cara penulisan:
INT(angka)
Angka adalah nilai yang akan dibulatkan ke integer.
Contoh penggunaan:
Rumus “=INT(8.9)” pada suatu cell akan bernilai angka integer 8.
Rumus “=INT(-8.9)” pada suatu cell akan bernilai -9, bukan -8 karena pembulatan ke bawah dimana -9 lebih kecil atau lebih bawah dari -8.9 sedangkan -8 lebih besar atau di atas -8.9.
Rumus “=19.5-INT(19.5)” akan bernilai 0.5
Cara penulisan:
INT(angka)
Angka adalah nilai yang akan dibulatkan ke integer.
Contoh penggunaan:
Rumus “=INT(8.9)” pada suatu cell akan bernilai angka integer 8.
Rumus “=INT(-8.9)” pada suatu cell akan bernilai -9, bukan -8 karena pembulatan ke bawah dimana -9 lebih kecil atau lebih bawah dari -8.9 sedangkan -8 lebih besar atau di atas -8.9.
Rumus “=19.5-INT(19.5)” akan bernilai 0.5
Fungsi Mod() Microsoft Excel
Fungsi Mod() menghasilkan sisa dari pembagian suatu angka oleh angka pembaginya Tanda dari angka hasil sama dengan tanda pembagi.
Cara penulisan:
MOD(angka,pembagi)
Jika pembagi adalah 0, MOD akan menghasilkan nilai error #DIV/0! (divided by zero)
Fungsi MOD dapat memiliki hasil yang sama dengan fungsi INT yang dikolaborisakan: MOD(n, d) = n - d*INT(n/d)
Contoh penggunaan fungsi MOD:
Cara penulisan:
MOD(angka,pembagi)
- angka adalah angka yang akan anda cari sisa pembaginya.
- pembagi adalah angka yang membagi variabel Angka
Jika pembagi adalah 0, MOD akan menghasilkan nilai error #DIV/0! (divided by zero)
Fungsi MOD dapat memiliki hasil yang sama dengan fungsi INT yang dikolaborisakan: MOD(n, d) = n - d*INT(n/d)
Contoh penggunaan fungsi MOD:
| Rumus | Keterangan (Hasil) |
| =MOD(3, 2) | Sisa dari 3/2 (1) |
| =MOD(-3, 2) | Sisa dari -3/2. Tanda sama dengan tanda pembagi(1) |
| =MOD(3, -2) | Sisa dari 3/-2. Tanda sama dengan tanda pembagi(-1) |
| =MOD(-3, -2) | Sisa dari -3/-2. Tanda sama dengan tanda pembagi(-1) |
Fungsi Round() Microsoft Excel
Fungsi Round dalam Microsoft Excel adalah digunakan untuk membulatkan suatu angka ke digi tertentu. Misalnya kita ingin membulatkan angka 2.234 menjadi 1 digit menjadi 2.2
Cara penulisan:
ROUND(number,digit)
Cara penulisan:
ROUND(number,digit)
- number adalah angka yang akan dibulatkan.
- digit adalah jumlah digit angka hasil pembulatan
- Jika digit lebih besar dari 0, number dibulatkan ke decimal.
- Jika digit sama dengan 0, number dibulatkan ke integer terdekat.
- Jika digit lebih kecil dari 0, number dibulatkan ke sebelah kiri dari titik desimal.
| Rumus | Keterangan (Hasil) |
| =ROUND(2.15, 1) | Membulatkan 2.15 ke satu tempat decimal (2.2) |
| =ROUND(2.149, 1) | Membulatkan 2.149 ke satu tempat decimal (2.1) |
| =ROUND(-1.475, 2) | Membulatkan -1.475 ke dua tempat decimal (-1.48) |
| =ROUND(21.5, -1) | Membulatkan 21.5 ke satu tempat decimal sebelah kiri titik desimal atau ke puluhan terdekat (20) |
