fungsi dalam excel

MODUL MEI semester VIII / STAI MUHAMMADIYAH TANGERANG

EXCEL

Disusun oleh:

Tini Kuslaeni, S.KomSTAI MUHAMMADIYAH TANGERANG

Tini Kuslaeni, S.Kom 1


EXCEL

Microsoft Excel adalah sebuah perangkat lunak pengolah angka –yaitu perangkat lunak yang

dipergunakan untuk mengolah data yang berupa angka seperti table, pembuatan laporan,

pengolahan database sederhana, kalkulasi, juga pembuatan bagain atau grafik- yang dikeluarkan

oleh perusahaan Microsoft.

Salah satu kelebihan dari Microsoft Excel adalah dalam penyediaan fungsi yang sangat

komplek, yang dapat digunakan dalam berbagai aplikasi yang berkaitan dengan perhitungan

beberapa fungsi yang digunakan dalam aplikasi pada modul ini adalah sebagai berikut:

CARA MEMULAI MICROSOFT EXCEL

1. Klik tombol Start

2. Klik All Programs

3. Klik Microsoft Office

4. Klik Microsoft Office Excel 2003. Tampilan di layar adalah sebagai berikut:



Bentuk tampilan Microsoft Excel adalah sebagai berikut:

Keterangan Gambar:

a. Workbook : file yang merupakan suatu buku kerja dalam Excel. Pada saat Excel

dibuka akan ditampilkan file Workbook dengan nama book1

b. Worksheet : lembar kerja yang terdapat di dalam Workbook yang terdiri dari lajur

kolom dan baris. Jumlah kolomnya ada 256 diberi tanda kolom A, B, C,

. . ., Z, AA, AB, . . ., IV. Sedangkan jumlah barisnya ada 65536.

c. Title Bar : baris judul yang digunakan sebagai identitas file yang aktif.

d. Menu Bar : Tampilan menu-menu yang ada dalam Microsoft Excel.

e. Toolbar : Merupakan ringkasan dari sejumlah perintah. Beberapa perintah pada

menu dapat diwakili oleh satu toolbar. Sebuah toolbar biasanya diwakili

oleh gambar, maka penggunaan tollbar disamping lebih efisien juga

lebih komunikatif dan menarik.

f. Formula Bar : baris yang digunakan untuk mengetik dan mengetahui formaula/rumus

dari suatu proses.

g. Alamat Kolom : judul/identitas kolom mulai dari A sampai IV (256 kolom)

h. Alamat Baris : judul/identitas kolom mulai dari 1 sampai 35536

i. Name Box : menunjukkan alamat cell aktif.



j. Task pane : task pane atau jendela tugas adalah fasilitas baru Microsoft Excel. Pada

jendela tugas ini tersedia beberapa fasilitas yang dapat digunakan

dengan cepat dan mudah. Task pane biasanya ditampilkan saat Excel

pertama kali dibuka.

k. Gridline : garis Bantu.

l. Cell : satuan terkecil dari suatu worksheet.

m. Scroll bar Horizontal : untuk menampilkan dokumen yang melebar ke samping. (menggulung

layar ke kiri dan ke kanan)

n. Scroll bar Vertical : untuk menampilkan dokumen yang panjang ke bawah. (menggulung

layar ke atas dan ke bawah)

Tampilan Pointer

Di dalam Excel tampilan kursor / pointer ada tiga, yaitu:

1. Tanda plus putih digunakan untuk menyeleksi / memblok cell.

2. Tanda plus hitam digunakan untuk menyalin / mengopi cell.

3. Tanda panah dengan 4 arah, digunakan untuk memindah cell

Operator Hitung

Operasi hitung yang sering dipakai dalam aplikasi Microsoft Excel antara lain:

OPERATOR CONTOH ARTI+ (plus) Addition (1+2) Penjumlahan

- (minus)Substraction (2-1) PenguranganNegation (-1) Negatif

* (Asterik) Multiplication (1*2) Perkalian/ (Back Slash) Division (2/1) Pembagian^ (Caret) Exponentiation (2^3) Perpangkatan= (Equal) Formula Awal Rumus

Mengatur Bingkai

1. Menggunakan Ikon

a. Klik / blok cell yang akan diformat / diatur

b. Kik tanda panah pada ikon di formatting toolbar

c. Pilih model bingkai yang diinginkan, atau

2. Menggunakan menu Format

a. Klik / blok cell yang akan diformat / diatur

b. Kik Format, Cells, tab Border

c. Lakukan perubahan sesuai dengan yang diinginkan dan diakhiri dengan OK

Perhatikan tampilan kotak dialog Format Cells di bawah ini:



Fungsi-fungsi yang umum dipakai pada Microxoft Excel1. SUM

Fungsi SUM ini digunakan untuk menjumlahkan seluruh data angka (numerik) dari sederetan angka.Bentuk umum dari fungsi SUM adalah:

Atau

2. AVERAGEFungsi AVERAGE ini berfungsi untuk mencari nilai rata-rata dari sederetan data angka (numeric).Bentuk umum dari fungsi AVERAGE adalah:

Atau

3. MAXFungsi MAX ini berfungsi untuk mencari nilai tertinggi dari sederetan data angka (numeric).Bentuk umum dari fungsi MAX adalah:

Atau

4. MINFungsi MIN ini berfungsi untuk mencari nilai terendah / terkecil dari sederetan data angka (numeric).Bentuk umum dari fungsi MIN adalah:

Atau

CONTOH KASUS


=SUM(number1,number2,…)

=AVERAGE(number1,number2,…)

=SUM(sel ke-1:sel ke-n)

=AVERAGE(sel ke-1:sel ke-n)

=MAX(number1,number2,…)

=MAX(sel ke-1:sel ke-n)

=MIN(number1,number2,…)

=MIN(sel ke-1:sel ke-n)


KASUS 01Daftar Upah Karyawan

PT. Merpati Utama Sejahtera adalah sebuah penerbit dan percetakan, akan membuat laporan gaji karyawan untuk bulan Juni 2007. Laporan yang dibutuhkan adalah sebagai berikut:

Ketentuan:d. Bagian yang diarsir diisi dengan menggunakan formula atau rumuse. Upah kerja normal sebesar Rp. 7.500/jamf. Upah kerja lembur sebesar Rp. 10.000/jamg. Total upah Bruto adalah hasil jumlah antara Upah Kerja dan Upah Lembur. Gunakan

fungsi SUMh. PPH (pajak) sebesar 10% dari Gaji yang diberikan pada seluruh upah Bruto karyawani. Total Upah Netto adalah hasil pengurangan antara Total Upah Bruto dengan PPHj. Hitung Total Upah Seluruhnya dengan menggunakan fungsi SUMk. Hitung Rata-rata Upah Netto dengan menggunakan fungsi AVERAGEl. Hitung Nilai Tertinggi Upah Netto dengan menggunakan fungsi MAXm. Hitung Nilai Teendah Upah Netto dengan menggunakan fungsi MIN.Penyelesaian:Untuk menyelesaikan kasus tersebut di atas, gunakan rumus atau formula berikut ini: Upah Kerja

D6 =B6*7500 Upah Lembur

E6 =C6*10000 Total Upah (Bruto)

F6 =SUM(D6:E6) Pajak 10%

G6 = 10%*F6 Total Upah Netto

H6 =F6-G6 Total Upah Seluruhnya

H14 =SUM(H6:H13) Rata-rata Upah Netto

H15 =AVERAGE(H6:H13) Nilai Tertinggi Upah Netto

H16 =MAX(H6:H13) Nilai Terendah Upah Netto

H17 =MIN(H6:H13)Hasil akhirnya akan terlihat seperti berikut:



LATIHAN 01FakturUD. Anugerah Sejahtera akan mengirimkan barang kepada PT. Setiawan Sentosa, sehingga diperlukan faktur penjualan terhadap barang tersebut. Buatlah faktur di bawah ini pada lembar kerja Microsoft excel!

Ketentuan:a. Bagian yang diarsir diisi dengan menggunakan formula atau rumusb. Jumlah Harga adalah hasil perkalian antara Jumlah dan Harga Satuanc. Hitung Subtotal untuk seluruh barang yang dibeli!d. Potongan (Pot.) diberikan sebesar 20% dari Subtotale. Pajak (Ppn.) dikenakan sebesar 10% dari Subtotalf. Hitung Total Pembelian dari barang yang dibeli oleh konsumen!

5. COUNT



Fungsi COUNT digunakan untuk menentukan berapa banyak data yang terdapat dalam range tertentu.Bentuk umum dari fungsi COUNT adalah:

Atau

Contoh

6. COUNT IFFungsi COUNTIF ini digunakan untuk menentukan berapa banyak data yang terdapat pada range tertentu berdasarkan kriteria atau kondisi tertentu.Bentuk umum dari fungsi COUNTIF adalah:

Range : digunakan untuk menentukan range yang akan dievaluasi / diuji. Criteria : digunakan untuk menentukan criteria atau kondisi tertentu yang akan dihitung.

Kriteria ini dapat berupa huruf, angka, atau rumus.Contoh:

7. Fungsi SUMIFFungsi SUMIF digunakan untuk menjumlahkan seluruh data angka (numerik) dari sederetan angka khusus untuk angka yang memenuhi kondisi atau kriteria tertentu.Bentuk umum dari fungsi SUMIF adalah:

Range : digunakan untuk menentukan range yang akan dievaluasi atau diuji.Criteria : digunakan untuk menentukan kriteria atau kondisi tertentu yang akan

dihitung. Kriteria ini dapat berupa huruf, angka, atau rumus.Sum_range : digunakan untuk menentukan range data yang akan dijumlahkan atau

dihitung.Kasus 02


=COUNT (number1,number2,…)

=COUNT(sel ke-1:sel ke-n)

=COUNTIF (range,criteria)

=SUMIF (range,criteria,sum_range)


pada lembar kerja berikut akan dihitung jumlah keseluruhan dari masing-masing jenis barang yang terjual, yaitu jenis barang Mouse, Printer, CD-Rom, dan Keyboard.

8. Fungsi Logika IFFungsi logika IF banyak berperan dalam menentukan scenario untuk menyelesaikan berbagai masalah. Fungsi IF mempunyai bentuk umum:

Fungsi logika IF ini menggunakan operator relasi =, <, >, <=, >=, dan <> dan juga menggunakan operator logika AND, OR, NOT. Misalnya sebagai berikut: Apabila nilai >= 60, maka dinyatakan LULUS Apabila nilai <= 60, maka dinyatakan GAGALMaka penulisan rumus IFnya adalah: =IF(Nilai>=60,”LULUS”,”GAGAL”)Contoh:

8.1. Fungsi IF BercabangFungsi IF ini merupakan suatu istilah yang digunakan untuk menyelesaikan beberapa tes logika yang bertingkat.

Kasus 03Grade Nilai SiswaPada lembar kerja sebelumnya ditambahkan ketentuan sebagai berikut:Nilai Ujian Huruf Keterangan95 – 100 A Sangat Memuaskan85 – 94 B Memuaskan75 – 84 C Cukup60 – 74 D Kurang<60 E Mengulang

Penyelesaian:


=IF (tes logika,nilai jika benar,nilai jika salah)


Nilai Huruf pada sel D2, diisi dengan rumus:=IF(B2<60,”E”,IF(B2,<74,”D”,IF(B2<84,”C”,IF(B2<94,”B”,”A”))))Predikat pada sel E2, diisi dengan rumus:=IF(B2<60,”Mengulang”,IF(B2,<74,”Kurang”,IF(B2<84,”Cukup”,IF(B2<94,”Memuask

an”,”Sangat Memuaskan”))))

Hasil yang diinginkan:

8.2. Fungsi IF dengan Dua Tes LogikaFungsi IF ini menguji dua tes logika dengan menggunakan bentuk umum:A. Fungsi IF-AND

B. Fungsi IF-OR

Kasus 04Tunjangan IstriSuatu perusahaan ingin memberikan Tunjangan Istri bagi karyawan laki-lakinya yang sudah menikah dengan ketentan: Tunjangan Istri diberikan sebesar Rp 150.000 bagi karyawan laki-laki yang sudah menikah.

PenyelesaianTunjangan Istri pada sel D2 diisi dengan rumus:=IF(AND(B2=”L”;C2=”Kawin”)150000;0)

LATIHAN 02


=IF (AND(tes logika1,tes logika2),nilai jika benar,nilai jika salah)

=IF (OR(tes logika1,tes logika2),nilai jika benar,nilai jika salah)


Perhitungan Gaji KaryawanCV. Dian Sejahtera akan mengadakan perhitungan gaji sehingga dapat diketahui berapa jumlah uang yang akan dibayarkan pada setiap karyawannya. Untuk menyelesaikan kasus tersebut, buatlah lembar kerja berikut!

Ketentuan:a. Status Perkawinan didapat dari Kode Status, yaitu apabila Kode Status K = Kawin, dan

Kode Status T = Tidak.b. Jabatan Pegawai didapat dengan ketentuan:

Jika Kode Pegawai = “A”, maka Jabatannya adalah “Pimpinan” Jika Kode Pegawai = “B”, maka Jabatannya adalah “Manager” Jika Kode Pegawai = “C”, maka Jabatannya adalah “Staff” Jika Kode Pegawai = “D”, maka Jabatannya adalah “Umum”

c. Gaji Pokok didapat dengan ketentuan: Jabatan Pimpinan sebesar Rp 3.500.000 Jabatan Manager sebesar Rp. 2.250.000 Jabatan Staff sebesar Rp. 1.000.000 Jabatan Umum sebesar Rp. 750.000

d. Tunjangan Jabatan hanya diberikan pada Jabatan Pimpinan dan Manager sebesar 3% dari Gaji Pokok

e. Tunjangan Anak diberikan pada seluruh karyawan yang mempunyai anak, sebesar 5% dari Gaji Pokok (maksimal 2 anak).

f. Hitung Gaji di Terima untuk setiap karyawang. Hitung Total Pengeluaran perusahaan untuk membayar gaji karyawan.

9. Fungsi TextFungsi tells yang banyak digunakan dalam aplikasi administrasi adalah: =LEFT(data teks,jumlah karakter)

Digunakan untuk mengambil karakter dari kiri =RIGHT(data teks,jumlah karakter)

Digunakan untuk mengambil karakter dari kanan =MID(data teks,posisi awal karakter,jumlah karakter)

Digunakan untuk mengambil karakter dari tengah =VALUE(data teks)

Merubah jenis data dari karakter/teks menjadi numeric/angka.Contoh:



Kasus 05:Biaya kursus di LPI SEJAHTERA

Ketentuan:Nomor Peserta terdiri dari:00 301- L 01

Nomor urut peserta pendidikanJenis Kelamin, L = Laki-laki dan P = PerempuanNama Paket dan Biaya Pendidikan, Yaitu:301 = Ms. Windows Biaya Rp 350.000302 = Ms. Word Biaya Rp 500.000303 = Ms. Excel Biaya Rp 650.000

Tahun PendaftaranJenis Kelamin pada sel C2 diisi dengan rumus:=IF(MID(A2,7,1)=”L”,”Laki-laki”,”Perempuan”)Nama Paket pada sel D2 diisi dengan rumus:=IF(MID(A2,3,3)=”301”,”Ms. Windows”, IF(MID(A2,3,3)=”302”,”Ms. Word”,”Ms. Excel”))Biaya Pendidikan pada sel E2 diisi dengan rumus:=IF(MID(A2,3,3)=”301”,35000, IF(MID(A2,3,3)=”302”,500000,600000))

LATIHAN 03Nilai Ujian dan Biaya Pendidikan

LPI Sejahtera akan membuat daftar peserta yang telah mengikuti pendidikan pada bulan mei 2007, sehingga akan diketahui program studi, nilai rata-rata, nilai huruf, keterangan, dan biaya pendidikan yang telah diterima LPI Sejahtera. Untuk memenuhi kebutuhan tersebut, buatlah table pada lembar kerja Microsoft Excel seperti di bawah ini, lalu isilah bagian-bagian yang masih kosong dengan rumus yang sudah kalian pelajari!



Ketentuan:1. Nomor Siswa terdiri dari : AXX/YY di mana:

A : Program Studi, yang terdiri dari:A : Komputer AdministrasiK : Komputer AkuntansiS : Sekretaris

XX : Jangka waktu semester yang digunakan dalam pembayaran, yaitu:01 : Rp. 175.00002 : Rp. 250.000

YY : Nomor urut siswa2. Nilai Rata-rata didapat dari haisl rata-rata antara Nilai Mid dengan Nilai Akhir3. Nilai Huruf ditentukan sebagai berikut:

Nilai Rata-rata <= 50 : E keterangan GAGAL Nilai Rata-rata 50 - 65 : D keterangan MENGULANG Nilai Rata-rata 65 - 75 : C keterangan LULUS Nilai Rata-rata 75 - 90 : B keterangan LULUS Nilai Rata-rata > 90 : A keterangan LULUS

4. Hitung seluruh total penerimaan LPI Sejahtera Tangerang untuk bulan Mei 2007 tersebut!

10. Fungsi Pembacaan TabelFungsi ini digunakan untuk membaca suatu tabel data. Fungsi ini terdiri dari: =VLOOKUP(nilai kunci,tabel data,nomor indek kolom,jenis)

Pembacaan tabel data yang disusun secara vertical. =HLOOKUP(nilai kunci,tabel data,nomor indek baris,jenis)

Pembacaan tabel data yang disusun secara horizontal. =LOOKUP(nilai kunci,table pembacaan data)

Pembacaan tabel data yang disusun baik secara vertikal maupun horizontal.Keterangan: Nilai kunci : nilai / sel yang digunakan sebagai dasar pembacaan tabel. Tabel data : range dari tabel data yang dibaca. Nomor indek kolom : nomor urut yang menyatakan posisi kolom dalam suatu table yang

dibaca. Dimulai dari 1. Nomor indek baris : nomor urut yang menyatakan posisi baris dalam suatu table yang

dibaca. Dimulai dari 1. Jenis : Berisi TRUE (bila table data yang dibaca diurutkan secara menaik)

dan FALSE(bila table data yang dibaca tidak diurutkan )



Kasus 06:Validitas soal di LPI SEJAHTERA

Dimasukkan nilai-nilai siswa ke dalam sebuah table nilai pada Sheet UAS seperti table di bawah ini. Di mana nilai-nilai tersebut akan digunakan untuk menilai validitas dari soal yang digunakan oleh seorang guru. Note: Jawaban benar = 1, dan Jawaban Salah = 0.



Jumlah jawaban dari soal tersebut dimasukkan ke dalam sheet daya pembeda seperti tampak pada table di bawah ini. Isilah kolom BA dan BB dengan menggunakan fasilitas Hlookup seperti terlihat pada table di bawah ini!


fungsi dalam excel

Documents