Istilah-Istilah Dan Fungsi Formula Excel 2007

Microsoft Excel merupakan perangkat lunak untuk mengolah data secara otomatis meliputi perhitungan dasar, penggunaan fungsi-fungsi, pembuatan grafik dan manajemen data. Perangkat lunak ini sangat membantu untuk menyelesaikan permasalahan administratif mulai yang paling sederhana sampai yang lebih kompleks. Permasalahan sederhana tersebut misalnya membuat rencana kebutuhan barang meliputi nama barang, jumlah barang dan perkiraan harga barang.  

Contoh permasalahan yang lebih kompleks adalah pembuatan laporan keuangan (general ledger) yang memerlukan banyak perhitungan, manajemen data dengan menampilkan grafik atau pivot tabel atau penggunaan fungsi-fungsi matematis atau pun logika pada sebuah laporan. untuk lebih jelasnya fungsi-fungsi tersebut di sampaikan pada paparan di bawah ini :

Istilah-istilah dalam Excel

1. Cell :merupakan bagian terkecil dari worksheet yng dapat diisi dengan jumlah karakter (max. 255 karakter) isi cell dapat berupa value, formula atau text. Contoh : cell A3, cell D5

2. Worksheet (lembar Kerja) : merupakan kumpulan dari 256 kolom dan 65536 baris.

3. Workbook (buku kerja) : merupakan kumpulan dari 256 worksheet (berlabel sheet1 sampai dengan sheet 256)

4. Range : merupakan sekelompok cell yang akan mendapat aksi sama sesuai perintah yang anda jalankan. Pemberian alamat/ address dilakukan mulai dari cell sudut kiri atas sampai cell sudut kanan bawah. Contoh : A4:D6 → range mulai dari cell A4 sampai cell D6

5. Alamat Relatif : merupakan alamat yang jika dituliskan kedalam bentuk rumus atau fungsi akan berubah jika di copy ke cell lain. Contoh : cell berisi formula A5*6 ,B3 di copy ke C5 formula pada C5 berubah menjadi B8*6

6. Alamat Semi Absolut : merupakan alamat yang dituliskan dengan tanda $ didepan baris atau kolom sehingga nilai tidak akan berubah. Contoh : Cell B1 berisi formula $A1*7,B1 di copy kan ke D5 formula pada D5 menjadi $A5*7

7. Alamat Absolut :     merupakan alamat yang dituliskan dengan tanda $ didepan baris dan kolom.tekan tombol F4 untuk menghasilkan alamat absolut pada formula bar. Contoh : cell B1 berisi formula $A$1&5,B1 di copy kan ke C3 formula pada C3 menjadi $A$1*5

8. Name Box : menunjukkan pada cell/ range yang aktif saat itu. Anda dapat juga membuat nama range melalui kotak nama di sebelah kiri formula bar. Contoh : Holla nama lain range A5:G7

Menulis Rumus

1.    Operasi Logika

Dibawah ini terdapat operasi logika anda dapat menggunakan operasi relasi atau perbandingan dengan lambang yang digunakan pada Excel dan fungsinya sebagai berikut :

a. = : sama dengan

b. > : lebih besar dari

c. < : lebih kecil dari

d. >= : lebih besar atau sama dengan

e. <= : lebih kecil atau sama dengan

f. <> : tidak sama dengan

2.    Menggunakan Fungsi

Fungsi sebenarnya adalah rumus yang sudah ada disediakan oleh Excel 2003, yang akan membantu dalam proses perhitungan. Kita tinggal memanfaatkan sesuai dengan kebutuhan. Umumnya penulisan Fungsi harus dilengkapi dengan argumen, baik erupa angka, label, rumus, alamat sel atau range. Argumen ini harus ditulis dengan diapit tanda kurung ().

A.   Fungsi Logika

Fungsi logika adalah fungsi yang digunakan untuk menyelesaikan perhitungan-perhitungan yang berhubungan dengan pengambilan keputusan fungsi logika yang digunakan adalah :

1. Fungsi If : Bentuk penulisannya : =If(kondisi,nilai jika benar,nilai jika salah)

Contoh : =If(A5<17,”anak-anak”,”dewasa”)>30,”panas”,if(A5>0,”hangat”,”dingin”))

2. Fungsi String

Fungsi string berfungsi untuk mengubah isi text numeric menjadi bilangan

1.      Fungsi VALUE : digunakan untuk merubah nilai value menjadi nilai text, penulisannya : =VALUE(text)

2.      Fungsi FIND :

         digunakan untuk menghasilkan posisi substring dari sebuah string atau suatu nomor yang dicari,penulisannya : =FIND(cari text,pada text,mulai nomor)

3.      Fungsi MID

         digunakan untu mengambil karakter tertentu dari sederet karakter, penulisannya :

         =MID(text,posisi awal,jumlah karakter)

4.      Fungsi LEFT atau RIGHT :

         digunakan untuk mengambil substring sebelah kiri atau kanan string, penulisannya =LEFT atau =RIGHT(text,jumlah karakter)

5.      Fungsi REPLACE :

         digunakan untuk menggantikan substring dengan substring lain dalam sebuah string (sederetan karakter atau karakter),

         penulisannya : =REPLACE(text lama,nomor awal,jumlah karakter,text baru)

6.      Fungsi CONCATENATE :

         digunakan untuk menggabungkan string menjadi satu kalimat maksimal 30 string, penulisannya : =CONCATENATE(text1,text2,…)

B.      Fungsi Tabel :

          Fungsi HLOOKUP dan VLOOKUP digunakan untuk membaca tabel secara vertikal (VLOOKUP) atau secara horizontal (HLOOKUP),

          penulisanya : =HLOOKUP(lookup_value,table_array,row_index_num,…)

          =VLOOKUP(lookup_value,table_array,row_index_num,…)

Fungsi yang sering digunakan

1.       fungsi Sum :

          Digunakan untuk menjumlahkan sekumpulan data pada satu range, penulisannya :

          =SUM(number1,number2,..)

2.       Fungsi Average :

          Digunakan untuk mencari nilai rata-rata, penulisannya :

          =average(number1,number2,…)

3.       Fungsi Max :

          Digunakan untuk mencari nilai tertinggi dari sekumpulan data, penulisannya :

          =max(number1,number2,…)

4.       Fungsi Min:

          Digunakan untuk mencari nilai terendah dari sekumpulan data, penulisannya :

          =max(number1,number2,…)
5.       fungsi Count :

          Digunakan untuk menghitung jumlah data dari range yang kita pilih

6.       Fungsi Stedev :

          Digunakan untuk menentukan standart devisiasi dari suatu range, penulisannya :

          =stedev(number1,number2,…)
7.       Fungsi Var :

          Digunakan untuk menentukan nilai varience dari suatu range, penulisannya :

          =var(number1,number2,…)

RUMUS DAN FUNGSI

Bahasan : Pengenalan Rumus Dan Fungsi Pada Program Microsoft Excel Xp Tujuan :

1. Siswa Dapat Mengenal Fungsi Pada Microsoft Excel.

2. Siswa Dapat Menggunakan Fungsi Pada Microsoft Excel.

A.      FUNGSI TEKS

Fungsi ini dikhususkan untuk memanipulasi jenis data string. Ada beberapa model dari jenis fungsi teks ini, antara lain:

1.       LEFT

Fungsi ini digunakan untuk mengambil sejumlah karakter dari sebelah kiri. Adapun cara penulisan fungsi ini adalah: =LEFT(Teks,n)

dimana : Teks : merupakan string yang akan dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel. N : banyaknya jumlah karakter yang diambil dari Teks dari sebelah kiri. Contoh :

= LEFT (“Yogyakarta”; 5) Yogya

= LEFT (“Aisyah”; 3) Ais

2.       RIGHT

Seperti halnya fungsi left, fungsi right juga digunakan untuk memanipulasi teks, yaitu mengambil sejumlah karakter tertentu dari sebelah kanan. Adapun syntaxnya adalah:

=RIGHT(Teks,n)
dimana : Teks : merupakan string yang akan dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel.
N : banyaknya jumlah karakter yang diambil dari Teks dari sebelah kanan.

Contoh :

= RIGHT (“Yogyakarta”; 5) karta

= RIGHT (“Aisyah”; 3) yah

3.       MID

Lain halnya dengan fungsi left dan right, fungsi mid ini berfungsi untuk mengambil karakater berdasarkan ketentuan yang ditentukan sendiri. Adapun cara penulisannya adalah:

=MID(Teks,m,n)

dimana : Teks : merupakan string yang akan dimanipulasi. Bisa berupa teks biasa ataupun nama suatu sel.

M : urutan awal pengambilan karakter.

N : banyaknya jumlah karakter yang diambil dari Teks di mulai karakter ke-M.

Contoh :

= MID (“Yogyakarta”; 5; 4) akar

= MID (“Aisyah”; 2; 4) isya

4.       VALUE

Fungsi value ini digunakan untuk mengembalikan angka yang bertype string menjadi jenis numerik kembali.
Perintahnya adalah:

=VALUE(DataAngka)

dimana : DataAngka : adalah angka yang bertype data string yang akan dikonversikan ke type data numerik
Contoh :

= VALUE(“12345”) 12345

B.      FUNGSI LOGIKA

Fungsi ini digunakan untuk menyeleksi suatu kondisi dari data yang ada dan memberikan hasil atau nilai yang berbeda sesuai dengan ketentuan yang diberikan. Untuk memudahkan pemahaman fungsi logika, maka kita dapat membagi fungsi logika ini menjadi beberapa jenis, antara lain:

1.       LOGIKA SANGAT SEDERHANA

Kita menyebut logika sederhana karena dalam kondisi ini hanya ada dua keadaan yang kita uji. Misalkan kalau buka pria berarti wanita, begitu juga sebaliknya kalau buka wanita pasti pria. Maka jika hanya ada dua keadaan yang diuji anda bisa menggunakan logika sangat sederhana ini dengan syntax:

=IF(Kondisi;B;S)
dimana: Kondisi : merupakan keadaan atau kondisi atau nilai dari suatu data yang kita uji. Nilai kondisi hanya
ada dua yaitu benar atau salah.

B : pernyataan atau hasil jika kondisi bernilai Benar

S : pernyataan atau hasil jika kondisi bernilai Salah.

Modul Microsoft Excel Xp

Contoh:
Misalkan kita memeriksa apakah kode yang ada pada sel A1 itu A atau B. Jika kodenya A, maka tuliskan Pria dan jika B isikan Wanita.

=IF(A1=”A”; “Pria”; “Wanita”)

atau
=IF(A1=”B” ; “Wanita”; “Pria”)

2.       LOGIKA SEDERHANA

Jika keadaan yang harus diseleksi lebih dari dua, maka kita bisa menggunakan fungsi logika sederhana. Adapun cara penulisan logika sederhana ini adalah:

=IF(Kondisi-1;B-1;IF(Kondisi-2;B-2;…;IF(Kondisi-n;B-n)))

Jumlah IF yang digunakan adalah sejumlah kondisi atau minimal sejumlah kondisi-1

Contoh:

Jika sel A1 berisi data M, maka hasilnya Motor, jika S, Sepeda dan jika B, maka hasilnya Becak.

= IF (A1=”M”; ”Motor”; IF (A1=”S”; ”Sepeda”; IF (A1=”B”; ”Becak”)))

atau

= IF (A1=”M”; ”Motor”; IF (A1=”S”; ”Sepeda”; ”Becak”))

jumlah kurung tutup dibelakang rumus adalah sejumlah IF.

3.       LOGIKA OR

Logika OR ini digunakan jika ada seleksi dengan menggunakan kata atau. Adapun cara penulisan dari fungsi ini adalah:

=IF(OR(Kondisi-1;Kondisi-2);B;S)

dimana: Kondisi-1 dan Kondisi-2 merupakan keadaan yang diuji

B : pernyataan yang akan di isikan jika kondisi-1 dan atau kondisi-2 bernilai benar

S : pernyataan yang akan dipakai jika Kondisi-1 dan Kondisi-2 bernilai salah.

Contoh:

Jika sel A1 berisi data L atau sel B1 berisi data M, maka dapat tunjangan 2500.

=IF(OR(A1=”L”; B1=”M”); 2500; 0)

Atau

=IF(OR(B1=”M”; A1=”L”); 2500; 0)

4.       LOGIKA AND

Sama seperti halnya logika OR, pada logika AND juga harus ada dua kondisi atau keadaan yang diuji. Bedanya pernyataan Benar hanya akan dihasilkan jika kedua kondisi bernilai benar.

=IF(AND(Kondisi-1;Kondisi-2);B;S)

dimana: Kondisi-1 dan Kondisi-2 merupakan keadaan yang diuji

B : pernyataan yang akan di isikan jika kondisi-1 dan Kondisi-2 bernilai benar

S : pernyataan yang akan dipakai jika Kondisi-1 atau Kondisi-2 atau keduanya bernilai salah.

Contoh:

Jika sel A1 berisi data L dan sel B1 berisi data M, maka dapat tunjangan 2500.

=IF(AND(A1=”L”; B1=”M”); 2500; 0)

Atau

=IF(AND(B1=”M”; A1=”L”); 2500; 0)

5.       LOGIKA AND OR

Dari logika AND dan OR yang telah kita bahas, bisa dihasilkan sebuah logika baru yaitu AND OR. Untuk kombinasi ini, tentunya harus ada minimal tiga kondisi yang diuji.

=IF(AND(OR(Kondisi-1;Kondisi-2); Kondisi-b); B; S)

dimana: Kondisi-1 dan Kondisi-2 adalah kondisi dari logika OR sekaligus merupakan kondisi-a dari AND Kondisi-b merupakan kondisi kedua dari fungsi AND

Contoh:

Jika sel A1 berisi data L atau sel B1 berisi data M, dan C1 berisi data Q maka dapat tunjangan 2500.

=IF(AND(OR(A1=”L”; B1=”M”); C1=”Q”); 2500; 0)

6.       LOGIKA OR AND

Untuk logika OR AND juga memiliki syntax yang hampir sama yaitu:

=IF(OR(AND(Kondisi-a;Kondisi-b); Kondisi-2); B; S)

Contoh:

Jika sel A1 berisi data L dan sel B1 berisi data M, atau C1 berisi data Q maka dapat tunjangan 2500.

=IF(OR(AND(A1=”L”; B1=”M”); C1=”Q”); 2500; 0)

Modul Microsoft Excel Xp

CONTOH APLIKASI DAN PEMBAHASAN

Bahasan : Penggunaan Program Microsoft Excel Xp Dalam Aplikasi Nyata

Tujuan :

1. Siswa Mengetahui Fungsi Dan Formula Excel Dan Aplikasinya

2. Siiswa Dapat Menggunakan Program Excel Dalam Pemecahan Masalah

A.      CONTOH APLIKASI

Untuk mencoba memahami Excel lebih jauh, kita mulai dengan aplikasi langsung. Siapkan sebuah buku kerja dan lembar kerja kosong, kemudian masukan data seperti dibawah ini:

Ketika suatu nanti anda bekerja pada suatu perusahaan, dan anda disuruh untuk menghitung gaji karyawan, tentunya anda membutuhkan perhitungan matematis, anda bisa menggunakan microsoft Excel sebagai salah satu solusinya. Ketentuan perusahaan dalam menentukan gaji tiap karyawan, bisa anda bayangkan sebagai soal yang harus anda kerjakan. Adapun soal itu adalah sebagai berikut:

1.       Status pegawai diisi berdasarkan kode. Jika karakter ke-3 kode =1, maka status menikah, dan jika karakter ke-3 dari kode=0 berarti statusnya belum menikah. Untuk memudahkan kita menyelesaikan soal diatas, kita buat catatan kecil untuk menguraikan masalah tersebut. Misalnya kita membuat catatan sebagai berikut:

Jika karakter ke-3 kode=1, status = menikah

Jika karakter ke-3 kode=0, status = belum menikah

Kemudian kita terjemahkan kedalam bahasa Excel, menjadi:

IF(MID(A6;3;1)=”1”;”Menikah”;

IF(MID(A6;3;1)=”0”;”Belum Menikah”))

Karena hanya ada 2 kemungkinan, maka bisa disingkat menjadi:

=IF(MID(A6;3;1)=”1”;”Menikah”;”Belum Menikah”)

anda ketik rumus tersebut pada sel D6 (karena baris yang diseleksi adalah baris 6, sedangkan kita mengisi kolom D)

2.       Jenis kelamin diisi berdasarkan karakter ke-3 dari kanan, jika L=Laki-Laki dan P=Perempuan.
Seperti pada soal nomor satu diatas, kita buat logika sederhananya dahulu:

Jika karakter ke-5=L, Laki-Laki. Selain itu Perempuan. Sehingga bisa ditulis dalam Excel menjadi:

=IF(MID(A6;5;1)=”L”;”Laki-Laki”;”Perempuan”)

3.       Jabatan diisi berdasarkan karakter pertama dari kode, jika K=Karyawan, M=Manajer, S=Sekretaris

          =IF(LEFT(A6,1)=”K”;”Karyawan”;IF(LEFT(A6,1)=”M”,”M anajer”,”Sekretaris))

4.       Gaji pokok diisi dengan 2500000 untuk manajer, 1000000 untuk sekretaris serta 400000 untuk karyawan.

          =IF(F6=”Manajer”;2500000;IF(F6=”Sekretaris”;100000 0;400000))

5.       Tunjangan makan diberikan sebesar 300000 untuk manajer, 100000 untuk sekretaris dan 60000 untuk karyawan

          =IF(F6=”Manajer”;300000;IF(F6=”Sekretaris”;100000; 60000))

6.       Tunjangan Makan diberikan hanya untuk karyawan laki-laki sebesar 50000

          =IF(AND(E6=”Laki-Laki”;F6=”Karyawan”);50000;0)

7.       Khusus untuk manager tunjangan lembur sebesar 200000 dan lainnya sebesar 30000

          =IF(F6=”Manajer”;200000;30000)

8.       Tunjangan jabatan berupa sebuah mobil hanya untuk manager atau sekretaris.

          =IF(OR(F6=”Manajer”;F6=”Sekretaris”);”Mobil”;”-“)

9.       Potongan masing-masing 15 persen dari seluruh gaji

          =SUM(G6:K6) * 15%

10.     Gaji bersih merupakan kalkulasi seluruh gaji dikurangi potongan.

          =SUM(G6:K6) – L6

BELAJAR MEMBUAT RUMUS PANGKAT

Sekarang saatnya kita belajar tentang rumus pangkat dalam Microsoft Excel, Simbol yang digunakan untuk Pembagian adalah tanda ^. Sekarang kita praktikkan seperti langkah dibawah ini :

1.       Bukalah program MICROSOFT EXCEL 2003

2.       Ketiklah di cell A1 : MENGHITUNG PANGKAT DALAM EXCEL

3.       Kemudian ketik di cell A3 : No, B3 : Angka, C3 : Pangkat D3 : Jumlah

4.       Kemudian ketik data seperti berikut ini :

5.       Setelah selesai ketik simbol = di cell D4 kemudian klik di cell B4 dan tambahkan simbol ^ kemudian klik lagi cell C4 dan Enter. Hasilnya seperti ini =B4^C4.

6.         Setelah mendapatkan hasil seperti diatas, mari kita atur data diatas agar terlihat lebih menarik

Konversi Angka Menjadi Huruf di MS Excel

Konversi dari angka menjadi huruf biasanya digunakan untuk pembuatan kwitansi/receipt. Misalnya angka Rp.1.001.000,- ditulis menjadi “Satu juta seribu rupiah”. Berikut ada tips untuk mengkonversi angka menjadi huruf secara otomatis dengan memanfaatkan skrip VB.

[Catatan: Saya menggunakan MS Office 2007]

1. Tentu saja, buka dulu program MS Excel dengan cara yang paling disukai.
2. Sebelumnya tampilkan dulu Tab Menu Developer di Ribbon, yaitu dengan cara klik Microsoft Button yang ada di pojok kiri atas, lalu pilih Excel Options. Di tab Popular, aktifkan pilihan “Show Developer Tab in the Ribbon“, lalu tekan OTRE.
3. Di Tab menu Developer, pilih menu Visual Basic untuk menampilkan Visual Basic Editor (VBE). Pilih menu Insert > Module. Salin kode di bawah ke dalam jendela Module1.

Dim Huruf(0 To 9) As String

Dim ax(0 To 3) As Double

Function INIT_angka()

    Huruf(0) = ""

    Huruf(1) = "satu "

    Huruf(2) = "dua "

    Huruf(3) = "tiga "

    Huruf(4) = "empat "

    Huruf(5) = "lima "

    Huruf(6) = "enam "

    Huruf(7) = "tujuh "

    Huruf(8) = "delapan "

    Huruf(9) = "sembilan "

 End Function

Function dgratus(angka As Double) As String

    Temp = ""

    INIT_angka

    panjang = Len(Trim(Str(angka)))

    nilai = Right("000", 3 - panjang) + Trim(Str(angka))

    For y = 3 To 1 Step -1

        ax(y) = Mid(nilai, y, 1)

    Next y

    Select Case ax(1)

        Case Is = 1

            Temp = "Seratus "

        Case Is > 1

            Temp = Huruf(Val(ax(1))) + "" + "ratus "

        Case Else

            Temp = ""

        End Select

    Select Case ax(2)

        Case Is = 0

            Temp = Temp + Huruf(Val(ax(3)))

        Case Is = 1

            Select Case ax(3)

                Case Is = 1

                    Temp = Temp + "Sebelas"

                Case Is = 0

                    Temp = Temp + "Sepuluh"

                Case Else

                    Temp = Temp + Huruf(Val(ax(3))) + " belas"

            End Select

        Case Is > 1

            Temp = Temp + Huruf(Val(ax(2))) + "puluh"

            Temp = Temp + " " + Huruf(Val(ax(3)))

        End Select

    dgratus = Temp

End Function

Function dghuruf(angka As Double) As String

    Dim ratusan(0 To 6) As String

    Dim sebut(0 To 4) As String

    sebut(1) = " ribu "

    sebut(2) = " juta "

    sebut(3) = " milyar "

    sebut(4) = " triliyun "

    panjang = Len(Trim(Str(angka)))

    kali = Int(panjang / 3)

    If Int(panjang / 3) * 3 <> panjang Then

        kali = kali + 1

        sisa = panjang - Int(panjang / 3) * 3

        nilai = Right("000", 3 - sisa) + Trim(Str(angka))

    Else

        nilai = Trim(Str(angka))

    End If

    For x = 0 To kali

        ratusan(kali - x) = Mid(nilai, x * 3 + 1, 3)

    Next x

    For y = kali To 1 Step -1

        If y = 2 And Val(ratusan(y)) = 1 Then

            Temp = Temp + "seribu "

        Else

            If Val(ratusan(y)) = 0 Then

                Temp = Temp

            Else

                Temp = Temp + dgratus(Val(ratusan(y)))

                Temp = Temp + sebut(y - 1)

            End If

        End If

    Next y

    dghuruf = Temp

End Function

4. Setelah itu tutup jendela VBE dengan meng-klik menu File > Close and Return to Microsoft Excel.
5. Selanjutnya simpan lembar kerja tersebut dengan memilih tombol Office Button dan pilih menu Save As > Excel Macro – Enabled Workbook.
6. Sekarang silahkan menguji coba dengan menulis angka berapa saja di sembarang cell. Misalnya di A1 kita tulis 25081981. Di sel B1 kita ingin menampilkan hurufnya, maka di B1 ketikkan =dghuruf(A1)

Untuk menambahkan kata “rupiah” di belakangnya, tambahkan &” rupiah”, sehingga menjadi

=dghuruf(A1)&" rupiah".

Untuk mengubah supaya huruf depan tiap kata menjadi kapital, tambahkan fungsi PROPER, sehingga menjadi

=PROPER(dghuruf(A1)&" rupiah").

Untuk mengubah setiap huruf menjadi kapital, tambahkan fungsi UPPER, sehingga menjadi

=UPPER(dghuruf(A1)&" rupiah").

Selamat mencoba dan semoga bermanfaat.