FUNGSI DASAR EXCEL

Simpan Artikel (PDF)

Fungsi Logika

Digunakan untuk memeriksa kondisi. Jika kondisi bernilai benar maka yang dikerjakan adalah pernyataan BENAR, sebaliknya jika bernilai salah maka yang dikerjakan adalah pernyataan SALAH. Penulisannya adalah sebagai berikut :

=IF(Kondisi,pernyataan BENAR,pernyataan SALAH)

Contoh :

Di sel A1 berisi data 10, di sel B1 berisi data 8. Jika si sel C1 diberi fungsi : =IF(A1>B1,"PULUHAN","SATUAN") maka hasilnya adalah "PULUHAN".

Jika ada lebih dari 2 kondisi, maka digunakan fungsi logika majemuk atau bersarang.

Contoh :

Di sel A1 berisi data 70. Pernyataan yang diinginkan ialah jika A1 berisi data>80 maka di sel B1 tertulis "ISTIMEWA", jika diantara 65-80 "BAIK", selain itu tertulis "KURANG". Maka fungsi logikanya adalah :

=IF(A1>80,"ISTIMEWA",IF(A1>65,"BAIK","KURANG"))

Banyaknya fungsi =IF() ditentukan oleh banyaknya kondisi - 1. Jadi jika kondisi yang diperiksa 3, maka jumlah fungsi =IF() adalah 2. Jika kondisinya 4 berarti fungsi =IF()-nya 3, dst.

Operator Logika

Jika kita diperhadapkan pada dua atau lebih kondisi/kriteria yang diperiksa bersama. Misalnya seperti kasus berikut ini :

Jika hujan dan tersedia payung, maka Rina akan berangkat bekerja. Sebaliknya jika hujan, tapi tidak tersedia payung maka Rina tidak akan berangkat bekerja. Untuk mengatasi kasus seperti ini, digunakan fungsi berikut :

=IF(AND(HARI="HUJAN",PAYUNG="ADA"),"BERANGKAT","TAK BERANGKAT")

Berarti :

Operator logika AND(kondisi 1, kondisi 2, dst) akan bernilai BENAR jika semua kondisi juga bernilai BENAR.

Jika ingin menonton konser musik klasik, maka harus berusia lebih dari 17 tahun atau bisa memainkan biola, selain itu tidak boleh. Maka penulisan fungsinya adalah sebagai berikut :

=IF(OR(USIA>17,BIOLA="BISA"),"BOLEH","TIDAK BOLEH")

Berarti :

Operator logika AND(kondisi 1, kondisi 2, dst) akan bernilai BENAR jika semua kondisi juga bernilai BENAR.

Jika ingin mengekspor barang ke luar negeri akan dikenakan pajak sebesar 25% untuk barang dari kayu atau rotan, dan tujuan ekspornya ke negara-negara Eropa. Selain itu dikenakan pajak sebesar 10%. Maka penulisan fungsinya adalah sebagai berikut :

=IF(AND(OR(Barang="Kayu",Barang="Rotan"),Negara<>"Eropa"),10%,25%)

ATAU

=IF(AND(Negara<>"Eropa",OR(Barang="Kayu",Barang="Rotan")),10%,25%)

ATAU

=IF(AND(OR(Barang="Kayu",Barang="Rotan"),NOT(Negara="Eropa"),10%,25%)

Berarti :

Operator '<>' artinya tidak sama dengan. Operator NOT menetapkan ketentuan yang berlawanan. Misal, NOT(negara="Eropa") artinya negara yang bukan Eropa

PERHATIAN : Pada bagian formula untuk menampilkan data yang berupa huruf (kalimat) maka harus diapit dengan tanda petik (".."), Misalnya Negara="Eropa". Sedangkan untuk data yang berupa angka maka tidak boleh diapit dengan tanda petik, misalnya 10% (tanpa petik diantaranya) !!!

Fungsi String

Digunakan untuk membaca data yang berisi beberapa informasi tertentu atau mengambil data string pada posisi dan jumlah tertentu. Misalnya, di sel B1 berisi nomor pokok mahasiswa TA89107. TA berarti mahasiswa dari jurusan Teknik Arsitektur, 89 berarti angkatan tahunnya dan 107 adalah nomor urut mahasiswa. Bagaimana mengambil bagian-bagian data tersebut? Untuk menyelesaikan masalah ini maka digunakan fungsi sebagai berikut :

=LEFT(B1,2)	-	maka hasilnya adalah TA
=RIGHT(B1,3)	-	maka hasilnya adalah 107
=MID(B1,3,2)	-	maka hasilnya adalah 89

Berarti :

=LEFT(String,n)
Untuk mengambil String atau Label mulai sebelah kiri sebanyak n karakter.

=RIGHT(String,n)
Untuk mengambil String atau Label mulai sebelah kanan sebanyak n karakter.

=MID(String,a,n)
Untuk mengambil String atau Label mulai posisi a sebanyak n karakter.

Fungsi Pembacaan Tabel

Digunakan untuk membaca tabel data secara horisontal atau vertikal dengan cara membandingkan nilai kunci yang ada pada tabel isian dengan data yang terletak di kolom pertama data. Penulisan fungsinya adalah sebagai berikut :

=VLOOKUP(Nilai Kunci, Range Tabel Data, Offset Kolom)
=HLOOKUP(Nilai Kunci, Range Tabel Data, Offset Baris)

Catatan :

• VLOOKUP() untuk tabel data vertikal,s edangkan HLOOKUP() untuk tabel data horizontal
• Nilai Kunci adalah data yang berupa angka atau label yang dijadikan kunci atau penentu informasi yang akan dibaca atau dibandingkan dengan tabel data.
• Range Tabel Data adalah alamat Tabel Data.
• Offset Kolom atau Baris adalah nomor kolom atau baris, dimana data atau informasi yang diinginkan terletak.

Agar lebih jelas, perhatikan contoh berikut ini! Misalnya diketahui lembar kerja dengan tabel seperti gambar berikut ini :

Untuk mengisi bagian kosong yang bertitik-titik di kolom NAMA BRG (kolom C) digunakan fungsi berikut ini :

Penjelasan :

• Nilai Kunci ada di kolom B2 karena sama dengan data pada kolom pertama Tabel Data.
• Range Tabel Data beralamat tetap karena itu harus diawali dengan '$' dan ditentukan mulai data yang ada di bawah judul kolom sampai akhir tabel.
• Offsetnya menggunakan kolom karena tabel datanya vertikal dan diisi 2 karena nomor kolom NAMA BRG pada tabel data terletak pada kolom nomor 2.

Pemisah argumen dalam fungsi Excel dipengaruhi oleh Regional Setting bahasa yang digunakan. Jika menggunakan bahasa Inggris pemisah argumennya adalah , (koma). Bahasa indonesia menggunakan tanda ; (titik koma).

Fungsi Lain-Lain

=CONCATENATE(teks 1, teks 2, teks 3)

Digunakan untuk menggabungkan beberapa teks. Fungsi ini juga bisa ditulis dengan '&'. Misalnya :

=CONCATENATE("SMU ","PETRA 2 ","SURABAYA")

ATAU

="SMU "&"PETRA 2 "&"SURABAYA"

Hasilnya adalah :

SMU PETRA 2 SURABAYA

=REPT(teks/sel,n)

Fungsi ini digunakan untuk mengulang teks, isi sel atau karakter hingga n kal. Misalnya :

=REPT("A",10)

Hasilnya adalah :

AAAAAAAAAA

(Karakter A sebanyak 10 kali)

Perkalian Matriks

· Fungsi : mengalikan dua buah matriks

· Contoh : Perencanaan Kebutuhan Material (Material Requirement Planning)

Untuk menentukan kebutuhan bahan baku (raw material) secara tepat dan kapan paling tepat untuk pemesanan dibutuhkan manajemen perencanaan kebutuhan material (material requirement planning).

Perencanaan kebutuhan material membutuhkan informasi :

· Jadwal Induk Produksi

· Catatan Persediaan

· Struktur Produk (BOM – Bill of Material)

· Langkah 1 : Buat BOM

BOM menunjukkan kebutuhan bahan baku (raw material) per satuan barang jadi (finish good). Kolom menunjukkan material apa saja yang ada dan baris menunjukkan barang jadi.

· Langkah 2 : Buat Jadwal Induk Produksi (JIP)

Jadwal induk produksi menunjukkan jumlah barang jadi yang akan diproduksi untuk setiap periode produksi. Periode bisa harian atau mingguan.

· Langkah 3 : Buat MRP

Material requirement planning dihasilkan dari perkalian matriks antara BOM dan JIP (fungsi MMULT). Fungsi MMULT tidak memperbolehkan isi BOM dan JIP dengan karakter selain angka.

Isi C3 dengan =MMULT(BOM!E4:N9,JIP!D3:H12). Blok range C3 sampai G8. Tekan tombol keyboard F2 kemudian tekan Ctrl-Shift-Enter.

· Langkah 4 : Buat ORDER

Order diambil dari MRP dan digeser ke depan sebesar Lead Time yang ada pada BOM. Diasumsikan tidak menyimpan material sehingga tidak tersedia material sisa pada awal periode.

· Kolom C Order (tgl 29) hanya dapat mengambil kolom C MRP (mis =IF(BOM!$D4=3,MRP!C3,0) pada C3).

· Kolom D Order (tgl 30) hanya dapat mengambil kolom C dan D MRP (mis =IF(BOM!$D4=2,MRP!C3,IF(BOM!$D4=3,MRP!D3,0)) pada D3)

· Kolom E Order (tgl 31) hanya dapat mengambil kolom C, D dan E MRP (mis =IF(BOM!$D4=1,MRP!C3,IF(BOM!$D4=2,MRP!D3,IF(BOM!$D4=3,MRP!E3,0))) pada E3)

· Kolom F sampai J Order (tgl 1-5) hanya dapat mengambil kolom C-G MRP (mis=IF(BOM!$D4=0,MRP!C3,IF(BOM!$D4=1,MRP!D3,IF(BOM!$D4=2,MRP!E3,IF(BOM!$D4=3,MRP!F3,0)))) pada F3).

MODEL PENCARIAN DATA DENGAN VLOOKUP, HLOOKUP, LOOKUP

Fungsi VLOOKUP  Fungsi : mencari nilai kolom paling kiri dari tabel dan mengembalikan nilainya pada baris yang sama dari kolom yang telah ditentukan.  Sintaks : VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) o Lookup_value adalah nilai yang akan dicari pada kolom pertama array. Dapat berisi nilai, referen atau teks string. o Table array adalah informasi data yang akan dicari. o Col_index_num adalah kolom tabel yang cocok, yang akan dikekembalikan nilainya. o Range_lookup adalah nilai logikal apakah pencarian harus eksak atau yang paling mendekati. Jika bernilai TRUE, nilai terdekat yang akan dikembalikan. Jika bernilai FALSE, VLOOKUP akan mencari nilai yang paling cocok, jika tidak ditemukan akan bernilai salah. (#N/A). Contoh Soal Cara pengerjaan : Pada A8 masukkan nilai NIM terlebih dahulu yang terdapat pada tabel NIM-Nama. Pada B8 ketikkan formula = VLOOKUP(A8;$A$2:$B$5;2;0). Fungsi HLOOKUP Fungsi Hlookup merupakan fungsi bantuan references juga. Fungsi Hlookup dipakai untuk menghasilkan nilai pada tabel secara horizontal. Penulisan : =HLOOKUP(nama_kolom;tabel;baris_pencarian;range_lookup) Contoh Soal Cara pengerjaan : Pada C6 ketikkan formula =HLOOKUP(B6;$B$1:$F$3;2;0). LOOKUP VALUE Pada prinsipnya sama dengan Vlookup, namun pada lookup value ini memungkinkan kita untuk mengambil beberapa data dari tabel lain sabagai referensi / patokan. Contoh Soal Cara pengerjaan : • Nama pada cell B11 adalah =VLOOKUP(A11;$A$2:$D$5;2;0) • Perihal pada cell D11 adalah = VLOOKUP(C11;$F$2:$H$5;2;0) • Judul pada cell E11 adalah = VLOOKUP(C11;$F$2:$H$5;3;0) Contoh Aplikasi Siapkan file daftar barang yang berisi :  Nama barang  Profit (dalam %)  Harga beli  Profit (ditentukan sekian persen dari harga beli, harga jual =harga beli*profit)  Harga jual (=harga beli + profit)  Simpan di file Barang.xls dan buat form nota penjualan seperti berikut  Set unlock untuk kolom barang, jumlah, nama pembeli, alamat pembeli dan telpon pembeli  Isi kolom no untuk baris 7 dengan “=IF(TRIM(B7)="", "", 1)” dan no baris 8 dengan “=IF(TRIM(B8)="", "", A7+1)”. Kopikan baris lainnya dari baris 8. Tujuannya agar nomor tertulis otomatis jika Barang diisi.  Isi harga pada C7 dengan “=IF(TRIM(B7)="","",VLOOKUP(B7, 'D:\ Dosen\Pengabdian\[Barang.xls]DaftarBarang'!$B$4:$E$13,4,FALSE))”. Tujuannya jika nama barang tidak diisi, akan diisi kosong, jika ada isinya akan dicari dari harga jual barang dari file “Barang.xls”. Kopikan ke baris lainnya.  Isi total pada E7 dengan “=IF(OR(TRIM(C7)="",TRIM(D7)=""), "", C7*D7)”, dia akan bernilai kosong jika harga atau jumlah tidak diisi dan akan diisi harga barang X jumlah jika keduanya ada. Kopikan ke baris lainnya.  Isi E17 dengan “=SUM(E7:E16)”  Operator dapat mengisi nama barang, sesuai yang ada di daftar barang dan jumlah penjualan. Hasilnya adalah sebagai berikut : Simpan : Versi PDF / Versi Word

Mengamankan Data Microsoft Excel

Simpan Versi PDF Versi WORD Keamanan File • Tujuan : agar file yang sudah dibuat tidak dapat dibuka oleh orang lain. • Contoh : o Buka Excel o Edit file o Untuk keamanan file tekan File – Save as dan akan muncul seperti gambar di bawah. Tekan tools. o Tekan General Options dan akan muncul dialog seperti di bawah o Ada dua password : untuk membuka file dan modifikasi data. Dapat diisi salah satu. Tekan tombol OK dan akan diminta mengisi konfirmasi password. o Simpan file. o Setelah langkah di atas dilakukan, setiap kali file dibuka, akan diminta memasukkan password. Tanpa itu fie tidak dapat dibuka. o Untuk menghapus password, masuk dahulu ke dalam file, lakukan seperti perintah di atas dan hapus password pada dialog Save Options. Keamanan Cell • Tujuan : agar user hanya dapat mengisi/mengedit cell tertentu. • Contoh : o Buka Excel o Edit file o Pastikan bahwa cell yang akan diprotek dalam kondisi lock dan sel lainnya dalam kondisi unlock. Default cell dalam kondisi lock. Caranya pilih cell yang akan dilock/unlock. Tekan tombol kanan, pilih Format cells, pilih protection dan akan muncul gambar berikut : o Cek locked jika akan dilock dan sebaliknya. Tekan tombol OK. o Proteksi akan aktif jika kita telah mengunci Sheet. Tekan tombol Tools – Protection – Protect Sheet. Dan akan muncul dialog seperti berikut o Isi password dan pilih Select unlocked cells. Tekan tombol OK. o Setelah hal ini dilakukan, hanya cell yang diunlock yang dapat diedit dan dipilih. o Untuk membebaskan proteksi, tekan menu Tools – Protection – Unprotect sheet dan masukkan passwordnya.

AUTO FILTER

• Tujuan : fasilitas ini digunakan untuk menampilkan data dengan kriteria tertentu. Kriteria yang dipilih bisa lebih dari satu field. • Langkah-langkah yang dilakukan adalah : 1. Siapkan table seperti gambar berikut : 2. Klik cursor pada salah satu judul kolom tabel 3. Klik menu Data – Filter – AutoFilter dan akan ditampilkan tanda dropdown di setiap field. Menampilkan Data Tertentu • Untuk menampilkan data tertentu seperti data nasabah yang memiliki Gaji Pokok Rp 1,450,000.00, lakkan langkah berikut : 1. Klik dropdown pada field Gaji Pokok 2. Pilih dan klik 1,450,000.00 seperti pada gambar berikut : 3. Dan akan ditampilkan hasil sebagai berikut : Menampilkan Semua Data • Untuk menampilkan semua data, cukup diklik dropdown pada gaji pokok dan pilih dan klik All. Menentukan Pilihan dengan Custom • Jika akan menampilkan gaji pokok di atas Rp 1,450,000.00 maka yang dilakukan adalah : 1. Klik cursor pada salah satu judul kolom tabel 2. Beri perintah AutoFilter. 3. Klik dowpdown pada field Gaji Pokok. 4. Pilih dan klik Custom sehingga akan muncul seperti gambar berikut : 5. Setelah muncul dialog custom, pada pilihan Show rows where, pilih dan klik is greater than. 6. Pada kotak isian sebelah kanan, ketik angka 1,450,000.00. 7. Tekan OK dan hasilnya sebagai berikut : Simpan Versi PDF Versi WORD