Penutup Excel

Banyak para pengguna aplikasi Office yang hanya memanfaatkan Excel sekedar untuk membuat tabel data saja. Padahal aplikasi Excel memiliki banyak sekali fitur yang dapat membantu aktifitas pekerjaan kita dalam menangani tabel-tabel data tersebut. Salah satunya adalah Formula yang bisa digunakan untuk membantu mengerjakan beragam proses perhitungan data pada tabel secara cepat dan semi otomatis.

Dan itulah alasan utama saya untuk menyusun rangkaian artikel sebanyak 30 Part di TechNet Wiki ini. Yaitu untuk mengenalkan apa itu formula dan bagaimana konsep dasar serta cara penggunaannya. Sasaran utamanya adalah untuk siapa saja yang mungkin baru mempelajari aplikasi Excel dan kemudian tertarik untuk mempelajari penggunaan formula pada Excel.

Mudah-mudahan rangkaian artikel ini ada gunanya dan bisa menjadi dasar sebelum mempelajari penggunaan formula-formula lanjutan pada aplikasi Excel yang tentunya akan lebih kompleks lagi.

Read More

Contoh Penerapan Excel

Untuk lebih memahami bagaimana formula diterapkan dalam aplikasi Excel juga sebagai gambaran bagi Anda yang mungkin baru saja mempelajari tentang formula pada Excel, berikut ini contoh beberapa kasus yang mudah-mudahan bisa digunakan sebagai bahan pembelajaran.

Contoh 1: Laporan Persentase Komisi Sales

Pada tabel berikut ini Anda diminta untuk mengisi kolom Persentase Komisi serta Jumlah Komisi, dimana jumlah komisi tersebut dihitung berdasarkan Masa Kerja dan Nilai Penjualan. Tabel Data Perhitungan Komisi Sales digunakan sebagai acuan atau referensi untuk menyelesaikan perhitungan.

Solusinya, karena ada tabel lain sebagai acuan maka formula jelas akan melibatkan fungsi VLOOKUP. Namun pada tabel referensi tersebut ada 2 kondisi untuk masa kerja hingga fungsi IF harus digunakan juga. Dengan demikian formula yang digunakan adalah:

D4=VLOOKUP(C4;$A$12:$C$16;IF(B4<=3;2;3);TRUE)

E4=D4*C4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 2: Menghitung Total Upah Karyawan

Pada tabel berikut ini Anda diminta untuk menghitung Waktu Kerja dan Total Upah karyawan, dengan aturan jam kerja standar adalah 9 jam dengan upah Rp. 10.000 / jam. Untuk karyawan yang jam kerjanya melebihi jam kerja standar (lembur), akan mendapat upah Rp. 2.500 / jam

Solusi untuk kasus ini adalah menggunakan fungsi IF. Alasannya karena ada karyawan yang mendapat upah standar saja dan ada karyawan yang mendapat upah standar plus upah lembur juga. Selain fungsi IF Anda cukup menggunakanoperasi dasar matematika biasa. Kemudian mengingat perhitungan matematika yang digunakan cukup kompleks, Anda boleh menggunakan fungsi ROUNDDOWN agar nilai-nilai yang dihasilkan bulat. Dengan demikian formula yang digunakan adalah:

D9=ROUNDDOWN((C9-B9)*24;2)

E9=IF(D9<=9;D9*$C$5;(9*$C$5)+(D9-9)*$C$6)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 3: Mengurai Struktur NIM (Nomor Induk Mahasiswa)

Pada tabel berikut ini Anda diminta untuk mengisi kolom Angkatan, Jurusan dan Fakultas dengan fungsi yang sesuai. Namun pengisian kolom-kolom ini bersandar pada struktur penulisan NIM yang jumlahnya 8 digit, yaitu:

• Digit pertama menunjukan Fakultas
• Digit ke-2 dan ke-3 menunjukan Jurusan
• Digit ke-4 dan ke-5 menunjukan angkatan
• Tiga digit terakhir menunjukan nomor urut pendaftaran

Khusus untuk mengisi Fakultas dan Jurusan, disediakan referensi dalam 2 tabel terpisah, yaitu tabel Kode Fakultas dan tabel Kode Jurusan.

Solusinya, karena semua pengisian kolom bersandar pada struktur NIM dan tidak semua nilai NIM tersebut digunakan melainkan hanya digit-digit tertentu saja, maka Anda bisa menggunakan fungsi teks untuk mengambil beberapa karakter tertentu dari NIM tersebut.

Untuk mengisi kolom Angkatan pada sel C4, maka Anda harus menggunakan fungsi MID karena berdasarkan struktur NIM, angkatan ini merupakan digit yang berada di tengah yaitu digit ke-4 dan ke-5.

Untuk mengisi kolom Fakultas pada sel D4 serta kolom Jurusan pada sel E4, maka Anda harus menggunakan fungsiVLOOKUP karena ada tabel referensi yang disediakan, namun nilai kunci yang digunakan pada fungsi VLOOKUP tersebut menggunakan fungsi lain yaitu fungsi LEFT untuk Fakultas dan dungsi MID untuk Jurusan.

Dengan demikian formula yang digunakan adalah:

C4=MID(A4;4;2)

D4=VLOOKUP(LEFT(A4;1);$A$11:$B$13;2;FALSE)

E4=VLOOKUP(MID(A4;2;2);$D$11:$E$16;2;FALSE)

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 4: Discount Penjualan Rokok

Pada tabel berikut ini Anda diminta untuk mengisi kolom Jumlah Harga, Discount, dan Total Harga. Namun ada aturan yang harus dipenuhi yaitu untuk merk rokok Ardath, Gudang Garam dan Jarum mendapat discount 5%. Sedangkan untuk merk-merk lainnya tidak mendapat discount.

Solusinya, untuk kolom Jumlah Harga pada sel E4 dan Total Harga pada sel G4 hanya merupakan operasi perkalian dan pengurangan biasa. Barulah pada kolom Discount Anda harus menggunakan fungsi yaitu fungsi IF-OR. Alasannya karena perhitungan ini melibatkan 3 syarat atau kondisi. Dengan demikian formula yang digunakan adalah:

E4=D4*C4

F4=IF(OR(B4="ardath";B4="gudang garam";B4="jarum");5%;0)*E4

G4=E4-F4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Contoh 5: Biaya Paket Wisata Harian

Pada tabel berikut ini Anda diminta untuk mengisi kolom Biaya Tambahan per Hari, Tujuan Wisata, Harga Paket, Biaya Tambahan, serta kolom Jumlah Dibayar.

Biaya Tambahan per Hari pada sel G11 hanya merupakan operasi pembagian biasa dimana Anda tinggal membagiHarga Paket dengan Lama Wisata.

G11=F11/E11

Lalu Tujuan Wisata pada sel D4 dan Harga Paket pada sel F4 bisa Anda kerjakan dengan mudah menggunakan fungsiVLOOKUP dimana nilai kuncinya bersandar pada kolom Kode.

D4=VLOOKUP(C5;$C$11:$G$15;2;FALSE)

F4=VLOOKUP(C4;$C$11:$G$15;4;FALSE)

Formula yang cukup kompleks justru terdapat pada kolom Biaya Tambahan di sel G4. Disini ada kondisi yang harus ditetapkan yaitu jika peserta berwisata dengan lama hari sesuai dengan lama wisata paket atau dibawahnya maka peserta tidak dikenakan biaya tambahan. Namun jika peserta berwisata dengan lama hari diatas lama wisata paket maka peserta akan dikenakan biaya tambahan per hari. Biaya tambahan ini tentunya disesuaikan dengan kelebihan hari yang diambil peserta tersebut.

Karena ada 2 buah kondisi maka fungsi utama yang digunakan jelas fungsi IF, namun tiap-tiap argumen pada fungsi IFtersebut harus diurai dengan fungsi VLOOKUP mengingat tujuan wisata yang bervariasi. Dengan demikian formula yang digunakan adalah:

G4=IF(E4>VLOOKUP(C4;$C$11:$G$15;3;FALSE);(E4-VLOOKUP(C4;$C$11:$G$15;3;FALSE))*VLOOKUP(C4;$C$11:$G$15;5;FALSE);0)

Dan yang terakhir adalah kolom Jumlah Dibayar pada sel H4. Formula yang digunakan disini hanya merupakan operasi penjumlahan biasa dimana Anda tinggal menambahkan Harga Paket dengan Biaya Tambahan.

H4=F4+G4

Contoh hasil akhirnya bisa dilihat pada gambar berikut ini.

Demikian beberapa contoh kasus terkait dengan penerapan formula pada aplikasi Excel yang mungkin saja Anda temukan di lingkungan kerja Anda. Dan seperti Anda lihat bahwa formula ini bisa menyelesaikan beragam perhitungan secara semi otomatis pada tabel-tabel data yang sederhana hingga yang kompleks.

Catatan:

Penerapan formula pada artikel ini dibahas secara garis besar dan tidak terlalu detail. Tujuannya agar Anda bisa melakukan analisa sendiri terkait dengan formula dan fungsi yang digunakan tersebut.

Formula yang dituliskan pada artikel ini juga hanya formula untuk sel-sel pada baris teratas saja karena untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan proses Auto Fill serta bantuan Sel Absolut.

Jika Anda ingin mencoba sendiri contoh-contoh kasus pada artikel ini, Anda dapat mendownload file-nya di tautan berikut ini:
http://sdrv.ms/10xFnvw 

Read More

Tips Excel

Setelah Anda mempelajari berbagai konsep dasar tentang penggunaan formula pada aplikasi Excel, berikut ini beberapa tips singkat yang mudah-mudahan bisa membantu Anda dalam menerapkan suatu formula pada pekerjaan Anda sehari-hari.

• Untuk menentukan fungsi dan formula apa yang akan digunakan saat berhadapan dengan suatu kasus tertentu, cobalah untuk menterjemahkan kasus tersebut dalam operasi hitung dasar matematika biasa yang Anda kenal. Jika perlu lakukan coretan kasar pada kertas agar Anda bisa memahami solusi apa yang dibutuhkan pada kasus tersebut. Karena adakalanya suatu proses perhitungan yang tampak rumit ternyata malah bisa diselesaikan dengan menggunakan operator dasar matematika biasa alias tanpa menggunakan fungsi sama sekali.
• Perhatikan tanda pemisah argumen pada formula yang Anda gunakan karena tanda pemisah ini bergantung pada seting regional. Perhatikan juga dengan teliti tanda-tanda lainnya yang mungin Anda gunakan seperti misalnya tanda kurung atau tanda petik.
• Jika kasus yang dihadapi memiliki unsur pembanding atau ada syarat maupun kondisi tertentu yang harus dicapai maka umumnya kasus tersebut harus menggunakan fungsi logika seperti misalnya fungsi IF.
• Kasus yang melibatkan tabel lain sebagai tabel acuan bisa menggunakan fungsi referensi seperti misalnyaVLOOKUP atau HLOOKUP.
• Jika fungsi yang digunakan ternyata tidak dapat menyelesaikan kasus Anda, cobalah untuk menggabungkan fungsi tersebut dengan fungsi lainnya.
• Formula yang Anda gunakan tidak selalu harus berada dalam 1 Sheet Excel saja, melainkan bisa melibatkan Sheet-sheet lainnya bahkan Workbook yang berbeda.
• Hasil dari Fungsi dan Formula yang Anda terapkan pada suatu kasus adakalanya bergantung pada format sel yang digunakan. Sebagai contoh, hasil perhitungan formula yang melibatkan fungsi penanggalan harus diletakan pada sel yang memiliki format Date. Oleh karena itu perhatikan selalu format sel yang Anda gunakan pada tabel-tabel data Anda.
• Gunakan bantuan Auto Fill dan Sel Absolut untuk membantu mempercepat penerapan formula pada data-data selanjutnya.
• Setelah Anda menerapkan suatu formula tertentu, cobalah untuk merubah-rubah datanya lalu lihat apakah hasilnya ikut berubah juga, cara ini adalah cara yang paling cepat untuk memeriksa apakah formula yang Anda terapkan sudah benar.
• Gunakan fasilitas bantuan Excel seperti misalnya Error Checking untuk memeriksa barangkali terdapat kesalahan dalam penerapan formula yang Anda kerjakan.

Read More

Gabungan Fungsi di Excel

Pada artikel sebelumnya telah dibahas contoh beberapa fungsi yang umum digunakan. Banyak para pengguna aplikasi Excel yang mengira bahwa dalam satu sel dan satu formula hanya boleh digunakan satu fungsi saja. Opini ini tentunya salah besar, karena dalam penggunaannya, Anda dapat menggabungkan beberapa fungsi sekaligus untuk menghitung nilai yang dicari. Dan justru karena adanya penggabungan beberapa fungsi ini maka aplikasi Excel menjadi semakin powerfull namun tetap efisien. Salah satu hal yang mutlak harus diperhatikan dalam penggabungan beberapa fungsi sekaligus dalam satu formula adalah penggunaan tanda kurung serta tanda pemisah argumen yang telah dibahas sebelumnya pada artikel Aturan Penulisan Formula.

Tanda kurung ini berfungsi untuk memisahkan antara fungsi yang satu dengan fungsi yang lainnya. Kesalahan penempatan tanda kurung ini tentunya akan mengakibatkan formula menjadi error hingga Excel akan menampilkan pesan kesalahan. Jika Anda mengalami kesulitan dalam menempatkan tanda kurung ini, misalnya saja karena terlalu banyaknya fungsi yang akan digabungkan, akan lebih baik jika Anda menuliskannya dulu di kertas dalam bentuk coretan kasar. Gunakan saja prinsip matematika dasar dimana tiap-tiap perhitungan dalam suatu perhitungan yang kompleks selalu dipisahkan dengan tanda kurung.

Mulai Excel 2007, Microsoft juga telah menambahkan fitur berupa warna yang berbeda untuk tiap-tiap tanda kurung yang saling berhubungan. Fitur ini tentunya bisa Anda gunakan juga sebagai bantuan.

Berikut ini beberapa contoh untuk penggabungan fungsi ini.

Contoh 1: Menggabungkan Fungsi SUM dengan Perkalian

Pada tabel berikut ini Anda diminta untuk menghitung Total Laba pada sel C10.

Secara matematika, Total Laba tentunya diperoleh dengan menambahkan semua unit yang terjual kemudian dikalikan dengan Laba per Unit. Namun pada tabel tersebut tidak terdapat sel khusus untuk menghitung jumlah keseluruhan Unit Terjual. Dengan demikian solusinya adalah Anda harus menggabungkan fungsi SUM untuk menghitung jumlah Unit Terjual yang kemudian dikalikan dengan Laba per Unit. Formulanya sebagai berikut ini.

C10=SUM(C3:C6)*C8

Atau agar sesuai dengan konsep dasar matematika mengingat ada 2 perhitungan yang digunakan maka Anda juga dapat menuliskan formulanya sebagai berikut:

C10=(SUM(C3:C6))*C8

Contoh hasil akhirnya dapat dilihat pada gambar berikut ini.

Contoh 2: Menggunakan 2 Fungsi SUMIF dalam 1 Formula

Pada tabel berikut ini Anda diminta untuk menghitung stok Barang Bagus pada sel D9 serta stok Barang Rusak pada selD10. Data stok barang tersebut diambil dari tabel Laporan Stok Gudang.

Jika data hanya diambil dari satu gudang saja, misal Gudang 1 maka dengan mudah Anda dapat menghitung stok yang diminta yaitu dengan menggunakan formula SUMIF yang telah Anda pelajari sebelumnya. Namun dalam kasus ini gudangnya ada 2, maka solusinya Anda tinggal gabungkan saja formula SUMIF untuk-tiap gudang dalam 1 formula.

Formula untuk menghitung stok barang bagus dari Gudang 1 adalah:

=SUMIF(C4:C6;"BAGUS";D4:D6)

Dan formula untuk menghitung stok barang bagus dari Gudang 2 adalah:

=SUMIF(C4:C6;"BAGUS";E4:E6)

Dengan demikian formula akhirnya pada sel D9 adalah sebagai berikut:

D9=SUMIF(C4:C6;"BAGUS";D4:D6)+SUMIF(C4:C6;"BAGUS";E4:E6)

Untuk menghitung stok barang rusak tentunya Anda bisa menggunakan fitur Auto Fill. Dan contoh hasil akhirnya bisa Anda lihat pada gambar berikut ini.

Read More

SUM IF Excel

Selain COUNTIF, fungsi logika lainnya yang umum digunakan adalah fungsi SUMIF. Sesuai dengan fungsi dasarnya yaituSUM, fungsi SUMIF juga digunakan untuk menjumlahkan data pada range tertentu, hanya saja data yang akan dijumlahkan mengacu pada kondisi tertentu.

Aturan penulisan fungsi SUMIF ini sebagai berikut:

=SUMIF(range kondisi;kondisi;range data)

Agar lebih jelas perhatikan contoh pada tabel berikut ini. Yang ditanyakan pada tabel tersebut bukanlah total laba keseluruhan melainkan total laba hanya untuk barang yang bagus saja serta total laba hanya untuk barang yang rusak saja. 

Pada kasus ini, kata BAGUS dan RUSAK merupakan sebuah kondisi yang berada pada range C3 hingga C6. Sementara data yang akan dijumlahkan berada pada range D3 hingga D6. Dengan demikian, sesuai dengan aturan penulisan SUMIF, maka formula untuk menghitung Total Laba Barang Bagus di sel D9 adalah sebagai berikut:

D9=SUMIF(C3:C6;BAGUS;D3:D6)

Sesuai dengan aturan penulisan formula, kata BAGUS bukan merupakan variabel yang dikenali Excel, maka kita perlu menambahkan tanda kutip untuk mengapit kata tersebut, hingga formulanya akan menjadi:

D9=SUMIF(C3:C6;"BAGUS";D3:D6)

Lalu untuk menghitung Total Laba Barang Rusak di sel D10, tentunya Anda tinggal mengganti kata BAGUS menjadi RUSAK, hingga formulanya menjadi:

D10=SUMIF(C3:C6;"RUSAK";D3:D6)

Dan contoh hasilnya dapat dilihat pada gambar berikut ini.

Read More

COUNT IF Excel

Fungsi dalam aplikasi Excel dikelompokan berdasarkan beberapa kategori tertentu, salah satunya yaitu kategori fungsi Logika (Logic Functions). Fungsi yang terdapat dalam kategori ini digunakan untuk menghitung atau menganalisa data yang bersandar pada perbandingan suatu kondisi atau kriteria tertentu hingga fungsi-fungsi yang termasuk dalam kategori ini lazim disebut juga dengan Conditional Functions. Ada banyak fungsi yang terdapat pada kategori ini dimana hampir sebagian besar nama fungsinya memiliki akhiran IF yang jika diartikan secara harfiah adalah "jika". Fungsi logika juga pada umumnya banyak melibatkan penggunaan operator pembanding yang telah dibahas sebelumnya pada artikel Operator Pembanding.

Fungsi COUNTIF termasuk kedalam kategori Logic Functions tersebut. Fungsi ini pada dasarnya memiliki kegunaan yang sama dengan fungsi COUNT yaitu untuk menghitung banyaknya data. Namun pada fungsi COUNTIF, banyaknya data ini hanya akan dihitung jika sesuai dengan kriteria atau kondisi tertentu. Aturan penulisannya sebagai berikut:

=COUNTIF(Range Data;Kondisi)

Dari aturan penulisan tersebut dapat Anda lihat bahwa fungsi COUNTIF ini terdiri dari 2 argumen dimana tiap-tiap argumen dipisahkan dengan tanda pemisah tertentu. Hal ini berbeda dengan beberapa fungsi yang telah dibahas sebelumnya yang masih menggunakan argumen tunggal yang sangat sederhana. Perihal tanda pemisah argumen ini telah dibahas sebelumnya pada artikel Aturan Penulisan.

Agar lebih jelas dalam memahami penggunaan fungsi COUNTIF ini, perhatikan contoh tabel berikut ini. Tiap-tiap siswa memiliki status kelulusan yang berbeda dimana ada yang LULUS dan ada yang GAGAL. Sementara yang ditanyakan adalah berapakah jumlah siswa yang LULUS?

Berdasarkan aturan penulisan COUNTIF, status LULUS merupakan kondisi yang dikehendaki, dan range yang memuat status LULUS tersebut merupakan range data yang akan dihitung oleh Excel yaitu sel C3 hingga C6. Dengan demikian formula untuk menghitung banyaknya siswa yang lulus tersebut adalah sebagai berikut:

C7=COUNTIF(C3:C6;LULUS)

Namun berdasarkan aturan penulisan formula, LULUS adalah variabel yang tidak dikenali oleh Excel sehingga formula diatas jelas akan menghasilkan pesan kesalahan jika dijalankan. Agar kata LULUS tersebut dikenali, maka kita berikan tanda kutip diantaranya, sehingga formulanya menjadi:

C7=COUNTIF(C3:C6;"LULUS")

Dan sekarang formula ini akan menghasilkan nilai yang benar seperti tampak pada gambar berikut ini.

Pelajari baik-baik penggunaan formula dengan multi argumen ini karena dalam penerapan Excel lebih lanjut Anda akan banyak menjumpainya.

Read More

Max Min pada Excel

Fungsi MAX digunakan untuk mengetahui nilai maksimum dari sejumlah data dan fungsi MIN untuk mengetahui nilai minimum juga dari sejumlah data. Aturan penulisan fungsi MAX dan MIN sama saja, yaitu sebagai berikut:

=MAX(sel awal:sel akhir)

atau

=MIN(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi MAX / MIN juga bisa dituliskan sebagai berikut:

=MAX(range)

atau

=MIN(range)

Sebagai contoh, pada tabel berikut ini ditanyakan berapa nilai maksimum dan minimum untuk semua nilai yang ada, yaitu data nilai pada sel C3 hingga C6.

Maka formula yang digunakan untuk melihat nilai maksimum adalah:

C7=MAX(C3:C6)

Dan formula yang digunakan untuk melihat nilai minimum adalah:

C8=MIN(C3:C6)

Hasilnya akan ditampilkan pada sel C7 dan C8 seperti tampak pada gambar berikut ini.

Read More

Mengenalk Fungsi COUNT excel

Banyak pengguna aplikasi Excel yang menganggap fungsi COUNT ini sama dengan fungsi SUM, padahal kegunaannya jelas berbeda. Jika fungsi SUM digunakan untuk menghitung jumlah data maka fungsi COUNT digunakan untukmenghitung banyaknya pemilik data. Oleh karenanya berhati-hatilah saat Anda harus memutuskan fungsi mana yang akan Anda gunakan, apakah SUM atau COUNT.

Aturan penulisan fungsi COUNT sebagai berikut:

=COUNT(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi COUNT juga bisa dituliskan sebagai berikut:

=COUNT(range)

Agar lebih jelas dalam memahami fungsi COUNT ini juga agar tidak tertukar penggunaannya dengan fungsi SUM, perhatikan contoh berikut ini. Pada tabel yang ditanyakan adalah jumlah materi training yang ada dan bukan jumlah nilainya.

Maka formula yang digunakan adalah:

C7=COUNT(C3:C6)

Dan hasilnya akan ditampilkan pada sel C7 seperti tampak pada gambar berikut ini.

Perhatikan bahwa walaupun yang ditanyakan adalah jumlah pemilik data, dalam hal ini Materi Training, namun alamat range yang dimasukan dalam formula tetap range untuk datanya yaitu C3:C6 dan bukan B3:B6.

Read More

Troubleshooting Excel

Pada artikel sebelumnya Anda telah mengenal beberapa pesan kesalahan yang ditampilkan Excel seandainya terjadi kesalahan saat Anda menerapkan suatu formula. Disamping pesan-pesan kesalahan tersebut, aplikasi Excel juga menyediakan beberapa bantuan lain yang bisa Anda gunakan untuk proses Troubleshooting atau menangani terjadinya kesalahan dalam penerapan formula ini. Beberapa diantaranya sebagai berikut.

Peringatan Kesalahan

Dalam beberapa kondisi, saat terjadi kesalahan dalam penulisan atau penerapan formula maka aplikasi Excel akan memberikan peringatan dengan menampilkan kotak dialog kesalahan seperti berikut ini.

Jika Anda mendapatkan kotak dialog tersebut maka periksa kembali formula yang Anda gunakan termasuk periksa juga beragam aturannya karena barangkali saja ada yang terlewat atau mungkin ada kesalahan dalam penulisan formula.

Dokumentasi Bantuan

Saat aplikasi Excel menampikan kotak dialog kesalahan seperti diatas, jika Anda menekan tombol Help maka Excel akan menampilkan dokumentasi bantuan tentang penggunaan formula yang mungkin bisa membantu Anda dalam memperbaiki kesalahan yang terjadi.

Melihat Seluruh Formula

Dalam lembar kerja Excel Anda, formula apapun yang Anda terapkan tentunya tidak akan ditampilkan oleh Excel karena Excel hanya akan menampilkan hasil dari penerapan formula tersebut. Untuk memeriksa kesalahan penerapan formula yang mungkin terjadi maka Anda harus memilih sel demi sel yang berisikan formula tersebut untuk melihat formula dibelakangnya. Hal seperti ini tentunya sangat menyita waktu apalagi jika Anda sudah menerapkan banyak sekali formula dalam lembar kerja Excel Anda.

Sebagai solusinya, Anda dapat menggunakan perintah Show Formulas yang terletak pada tab Formulas > Formula Auditing > Show Formulas.

Jika Anda klik tombol perintah Show Formulas tersebut maka Excel akan menampilkan semua formula yang ada pada lembar kerja Anda.

Untuk kembali ke kondisi tampilan sebelumnya dimana sel menampilkan hasil formula, klik kembali tombol Show Formulas tersebut.

Melihat Hasil Fungsi pada Suatu Formula

Dalam penulisan formula yang cukup kompleks, terutama jika Anda banyak menggabungkan beragam fungsi, tentu saja Anda tidak dapat melihat hasil dari fungsi pada formula yang sedang Anda kerjakan tersebut karena Anda hanya menuliskan argumennya saja. Untuk memeriksa seandainya terjadi kesalahan, maka Anda dapat melihat hasil dari fungsi tersebut secara sementara. Caranya sebagai berikut.

Klik pada sel yang hendak Anda periksa tersebut hingga formulanya ditampilkan pada Formula Bar.

Pada Formula Bar, pilih atau sorot fungsi yang ingin Anda lihat hasilnya.

Tekan tombol F9 pada keyboard dan Excel akan menampilkan hasil dari fungsi yang telah Anda pilih sebelumnya.

Trace Precedents dan Trace Dependents

Trace Precedents dan Trace Dependents adalah perintah bantuan untuk memeriksa atau melacak hubungan antara data pada suatu sel dengan formulanya. Tombol untuk menjalankan kedua perintah ini juga terdapat pada tab Formulas > Formula Auditing.

Trace Precedents digunakan untuk melihat sel-sel mana saja yang mempengaruhi sel aktif yang berisikan formula. Anda tinggal meng-klik sel yang berisikan formula lalu klik tombol Trace Precedents. Sel-sel yang mempengaruhi berikut hubungannya akan ditunjukan dengan garis-garis panah, contohnya seperti tampak pada gambar berikut ini.

Trace Dependents adalah kebalikan dari Trace Precedents, yaitu untuk melihat suatu sel mempengaruhi formula pada sel yang mana. Cara menggunakannya juga sama, Anda tinggal memilih salah satu sel yang dilibatkan dalam suatu formula lalu klik tombol Trace Dependents. Hubungan sel tersebut dengan sel yang berisikan formula akan ditunjukan dengan garis-garis panah, contohnya seperti tampak pada gambar berikut ini.

Setelah menggunakan kedua perintah tersebut, Anda dapat menghilangkan garis-garis panah yang ditampilkan dengan mengklik tombol Remove Arrows yang terletak dalam group perintah yang sama dalam tab Formulas.

Evaluasi Formula

Masih didalam group perintah Formula Auditing, Anda juga akan menemukan tombol perintah Evaluate Formula. Sesuai dengan namanya, perintah ini akan melakukan evaluasi menyeluruh secara tahap demi tahap pada formula yang Anda terapkan. Anda tinggal meng-klik sel yang berisikan formula lalu klik tombol Evaluate Formula.

Pada kotak dialog Evaluate Formula yang ditampilkan, secara otomatis Excel akan menampilkan fungsi pertama yang ditemukan pada formula. Klik tombol Evaluate untuk melihat hasil dari fungsi yang ditampilkan tersebut. Jika ternyata fungsi tersebut memiliki hubungan dengan fungsi lainnya maka Anda bisa menggunakan tombol Step In untuk melihat hubungan-hubungan selanjutnya atau klik tombol Step Out untuk kembali ke hubungan sebelumnya.

Perintah evaluasi formula ini akan sangat berguna jika Anda menerapkan suatu formula yang kompleks dengan melibatkan banyak fungsi didalamnya.

Error Checking

Tombol perintah terakhir pada Formula Auditing adalah tombol perintah Error Checking. Perintah ini akan memeriksa formula pada sel aktif yang harus Anda pilih dulu sebelumnya.

Jika tidak ada kesalahan, maka akan ditampilkan pesan seperti tampak pada gambar berikut ini.

Namun jika ternyata ada kesalahan, maka kotak dialog Error Checking akan ditampilkan. Pada kotak dialog ini Anda dapat melihat dokumentasi bantuan dengan meng-klik tombol Help on this error; menjalankan evaluasi formula dengan meng-klik tombol Show Calculation Steps; mengabaikan kesalahan dengan meng-klik tombol Ignore Error; atau menyunting formula langsung secara manual dengan meng-klik tombol Edit in Formula Bar.

Demikian beberapa opsi bantuan yang disediakan oleh aplikasi Excel untuk proses Troubleshooting formula ini yang mudah-mudahan bisa membantu Anda seandainya formula yang Anda terapkan ternyata tidak berfungsi sebagaimana mestinya.

Read More

Mengenal fungsi Kesalahan pada Excel

Saat bekerja dengan formula pada aplikasi Excel, mungkin saja ada kesalahan yang Anda lakukan. Kesalahan ini akan ditampilkan oleh aplikasi Excel dalam bentuk kode atau pesan kesalahan tertentu. Dengan mengetahui arti dari pesan-pesan kesalahan tersebut diharapkan Anda bisa segera mencari solusi atau memperbaikinya. Berikut ini beberapa pesan kesalahan yang akan ditampilkan oleh Excel tersebut:

#####

Pesan kesalahan ini umum disebut dengan istilah "Railroad tracks”. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Anda menuliskan data yang panjang namun lebar kolomnya terlalu sempit hingga sel tidak dapat menampilkan data tersebut.
• Anda mengisi angka atau bilangan negatif pada suatu sel dimana sel tersebut memiliki format sel Date (tanggal) atau Time (waktu).

#REF!

REF bisa diartikan dengan REFERENCE atau referensi, hingga pesan kesalahan ini umumnya berhubungan dengan kesalahan pada formula yang melibatkan penggunaan tabel referensi sebagai acuan. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Anda membuat suatu formula yang terhubung dengan suatu tabel referensi namun kemudian tabel referensi tersebut terhapus.
• Pada fungsi lookup atau referensi seperti misalnya VLOOKUP dan HLOOKUP, Anda menggunakan nomor index kolom atau baris yang lebih besar dari jumlah kolom atau baris pada tabel referensinya.
• Data rujukan yang digunakan dalam formula dipindahkan ke tempat lain.

#VALUE!

VALUE bisa diartikan dengan nilai, hingga pesan kesalahan ini umumnya berhubungan dengan kesalahan nilai yang digunakan pada formula. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Anda menggunakan parameter teks pada argumen suatu fungsi yang seharusnya menggunakan parameter berupa angka atau bilangan. Misalnya seperti =HLOOKUP(B8;C3:E4;"dua";FALSE).
• Anda menjalankan formula perhitungan dengan Operator Dasar ataupun fungsi perhitungan lain namun argumen yang digunakan merujuk pada sel yang berisikan teks. Misalnya sel A1 berisikan data KOTA dan sel A2 berisikan data BANDUNG. Jika Anda menjalankan formula =A1+A2 maka hasilnya adalah pesan kesalahan ini.

#DIV/0!

DIV/0 bisa diartikan dengan Divide by Zero atau dibagi dengan nol, hingga pesan kesalahan ini umumnya berhubungan dengan proses pembagian dengan bilangan nol. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Formula yang Anda gunakan dibagi dengan angka nol.
• Formula yang Anda gunakan dibagi dengan sel yang kosong atau belum ada data apapun didalamnya.

#NULL!

NULL bisa diartikan dengan kosong atau belum ditentukan hingga pesan kesalahan ini umumnya berhubungan dengan argumen atau simbol yang hilang, atau bisa juga karena ada posisi penempatan simbol yang kosong. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Tidak ada tanda pemisah titik-dua (:) pada alamat range. Misalnya seharusnya tertulis A1:C1 tapi Anda menuliskannya (A1 C1).
• Terdapat spasi diantara alamat sel atau range. Hal ini jelas tidak diperkenankan karena diantara alamat sel atau range setidaknya Anda harus menggunakan Operator Dasar atau tanda pemisah argumen koma atau titik-koma.

#NUM!

NUM bisa diartikan dengan number atau bilangan hingga pesan kesalahan ini umumnya berhubungan dengan kesalahan yang terkait dengan bilangan. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Hasil perhitungan formula melebihi batas nilai yang dikenali Excel.
• Anda menggunakan fungsi iteratif seperti misalnya fungsi IRR dan formula yang Anda gunakan tidak dapat menemukan hasilnya.

#NAME?

NAME bisa diartikan dengan nama yang maksudnya ditujukan untuk nama sel. Jadi pesan kesalahan ini umumnya berhubungan dengan kesalahan pada penamaan alamat suatu sel atau range. Beberapa penyebab munculnya pesan kesalahan ini diantaranya adalah:

• Penulisan alamat range yang disatukan atau tidak dipisahkan tanda titik-dua. Misalkan range A1:D1 namun Anda tuliskan dengan A1D1.
• Nama suatu alamat range yang tidak ditemukan karena belum dibuat atau kesalahan dalam penulisan nama range tersebut.
• Nama fungsi yang Anda gunakan salah, misalnya saja HLOOKUP tapi Anda menuliskannya HHLOKUP.
• Teks pada suatu formula yang tidak diapit tanda kutip.

#N/A!

N/A bisa diartikan dengan Not Available atau tidak tersedia. Pesan kesalahan ini akan ditampilkan jika:

• Anda menggunakan fungsi lookup atau referensi seperti misalnya VLOOKUP atau HLOOKUP dalam suatu formula dan nilai yang dicari tidak ditemukan dalam tabel referensi atau tabel yang dijadikan acuan.
• Pesan ini juga merupakan pesan kesalahan generik atau umum dimana kesalahan yang terjadi tidak dapat diwakili oleh pesan-pesan kesalahan sebelumnya, misalnya seperti data yang hilang, nama fungsi yang tidak dikenali, penerapan nilai dalam suatu argumen yang salah, atau nilai yang Anda coba cari dari formula yang Anda gunakan ternyata tidak ada atau tidak berhasil ditemukan.

Read More

Mengenal fungsi TEKS pada Excel

Formula pada aplikasi Excel tidak hanya diperuntukan bagi data berupa angka, karena Excel menyediakan juga beberapa fungsi untuk mengolah data berupa teks, diantaranya yang sering digunakan adalah fungsi LEFT; MID; dan RIGHT. Ketiga fungsi tersebut memiliki kegunaan yang sama yaitu untuk mengambil beberapa karakter dari suatu teks. Sementara perbedaannya terletak pada posisi atau arah pengambilan karakternya. Penjelasan serta aturan penulisan untuk ketiga fungsi tersebut adalah sebagai berikut.

Fungsi LEFT akan mengambil sejumlah karakter dari arah kiri. Jumlah karakter yang akan diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah sebagai berikut:

=LEFT(teks;jumlah_karakter)

Sebagai contoh jika pada sel B2 Anda ketikan formula berikut ini:

=LEFT("KOMPUTER";3)

Maka hasilnya sel B2 tersebut akan menampilkan kata KOM, yaitu 3 karakter pertama dari arah kiri pada kata KOMPUTER seperti tampak pada gambar berikut ini.

Fungsi MID akan mengambil sejumlah karakter dari arah kiri. Namun awal pengambilan karakter tersebut bisa Anda tentukan sendiri pada argumen awal. Dan jumlah karakter yang akan diambil tetap ditentukan pada argumenjumlah_karakter. Aturan penulisan selengkapnya adalah sebagai berikut:

=MID(teks;awal;jumlah_karakter)

Sebagai contoh jika pada sel B2 Anda ketikan formula berikut ini:

=MID("KOMPUTER";4;2)

Maka hasilnya sel B2 tersebut akan menampilkan kata PU, yaitu 2 karakter yang dihitung mulai karakter ke-4 pada kata KOMPUTER seperti tampak pada gambar berikut ini.

Fungsi RIGHT akan mengambil sejumlah karakter dari arah kanan. Jumlah karakter yang akan diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah sebagai berikut:

=RIGHT(teks;jumlah_karakter)

Sebagai contoh jika pada sel B2 Anda ketikan formula berikut ini:

=RIGHT("KOMPUTER";3)

Maka hasilnya sel B2 tersebut akan menampilkan kata TER, yaitu 3 karakter pertama dari arah kanan pada kata KOMPUTER seperti tampak pada gambar berikut ini.

Seperti halnya formula lainnya, penggunaan fungsi teks ini tentu saja dapat merujuk ke alamat sel tertentu. Sebagai contoh perhatikan tabel berikut ini. Pada sel C2 digunakan formula MID dengan teks yang merujuk pada sel B2.

Read More