Tutorial Excel 2007

Background Color, memilih latar belakang cell yang dipilih

Fill Effects, untuk memberikan gradient, tekstur atau gambar sebagai warna latar belakang

More Colors, untuk menambahkan warna yang tidak anda temukan pada color pallete

Pattern Color, untuk member warna pada pola

Pattern Style, memilih pola yang anda inginkan untuk dijadikan latar belakang cell

Sample, melihat tampilan dari format yang anda buat

Protection

Locked, mengunci cell sehingga tidak bisa diubah, diubah ukurannya atau dihapus

Hidden, menyembunyikan formula atau rumus yang ada pada cell

BAB 3

RUMUS DAN RANGE

1.    Menggunakan Rumus

Rumus yang digunakan pada Microsoft Excel 2007 merupakan instruksi matematika, rumus tersebut dapat Anda masukkan ke dalam suatu sel pada buku kerja. Operator hitung yang dapat digunakan pada Microsoft Excel 2007 untuk memasukkan instruksi matematika adalah sebagai berikut :

+	Penjumlahan
-	Pengurangan
*	Perkalian
/	Pembagian
^	pemangkatan

Proses penghitungan akan dilakukan sesuai dengan derajat urutan atau hirarki operator hitung sebagai berikut :

Pertama	^
Kedua	* atau /
Ketiga	+ atau -

Jika akan menggunakan instruksi rumus yang cukup rumit, Anda dapat menggunakan tanda kurung “(             )”, instruksi yang diapit oleh tanda kurung tersebut akan diproses terlebih dahulu karena memiliki urutan atau hirarki operator yang lebih tinggi.

Menulis Formula pada Excel 2007

Apa itu formula?

Formula merupakan ekspresi matematika untuk melakukan operasi pada data lembar kerja. Anda dapat menggunakan Excel 2007 formula untuk melakukan operasi matematika, seperti penjumlahan dan perkalian, atau kita dapat membandingkan nilai pada lembar kerja, menggabugnkan teks, mencari nilai statistic, dll.

Formula dapat mengacu pada cell-cell lain pada lembar kerja yang sama, cell-cell pada lembar lain dalam buku kerja yang sama, atau cell pada lembar dalam buku kerja lainnya. Selain itu jika anda mengubah data dalam spreadsheet anda, excel akan secara otomatis menghitung ulang jawaban tanpa anda harus memasukkan kembali formula.

Sebuah format formula dasar harus dimulai dengan tanda sama dengan (=) diikuti oleh satu atau lebih Operan, dipisahkan oleh satu atau lebih Operator. Operan bisa angka, teks, cell cell, range, atau nama fungsi. Operator adalah symbol yang digunakan untuk mewakili operasi aritmatika dan perbandingan yang anda terapkan pada Operan.

Bagaimana menulis formula?

Sebagai contoh sederhana, kita akan mencari hasil perkalian antara cell A1 dan B1, hasilnya ditampilkan pada cell C1.

a.       Isi cell A1 dan B1 dengan sembarang angka, kemudian klik pada cell C1.

b.      Tuliskan =A1*B1 pada cell C1. Untuk menuliskan alamat cell seperti A1 atau B1 pada formula, anda bisa mengetikkan manual atau dengan mengklik cell A1 atau B2 itu sendiri.

c.       Tekan enter bila anda sudah selesai menulis formula. Maka pada cell C1 akan muncul hasil perkalian antara cell A1 dan B1.

Selain perkalian kita bisa menggunakan operator matematika lain seperti keterangan table diatas.

Bagaimana jika kita salah memasukkan formula?

Bila kita melakukan kesalahan dalam memasukkan formula, pada cell di mana kita menuliskan formula akan muncul pesan kesalahan seperti :

#######	lebar cell tidak sesuai dengan lebar data pada cell (kita hanya perlu melebarkan kolom).
#DIV/0!	mencoba operasi pembagian dengan nol yang tidak didefinisikan.
#NAME?	menggunakan nama cell yang tidak didefinisikan atau salah penulisan.
#VALUE!	mencoba memanipulasi sesuatu secara aritmatika yang bukan sebuah angka.
#NUM!	melakukan beberapa operasi matematika yang tidak mungkin, seperti menghitung akar bilangan negative.
#N/A	perbandingan data tidak sama jenisnya

2.    Range

Yang dimaksud dengan Range pada excel yaitu kumpulan dari beberapa cell. Untuk lebih jelasnya perhatikan gambar berikut :

Perhatikan bahwa Cell C3 sampai dengan Cell C13 telah diarsir secara bersamaan. Kumpulan Cell tersebut disebut Range dan penulisan range pada gambar di atas untuk aplikasi rumus dalam lembar kerja ditulis dengan C3:C13 (C3 tanda titik dua C13). Apabila sudah mengetahui apa yang dimaksud dengan cell dan range, maka untuk selanjutnya akan dijelaskan mengenai cara memasukkan data secara otomatis pada Excel.

3.    Memasukkan data secara otomatis pada Excel

Dengan Microsoft excel, kita dapat memasukkan data secara otomatis pada lembar kerja. Data-data yang dimaksud yaitu No. Urut, Hari, Bulan serta Tahun.

Contoh :

No urut                          : 1,2,3, …

Hari                                 : Sunday / Minggu, Monday / Senin, …

Bulan                              : January, February, …

Tahun                             : 2005,2006, …

Adapun langkah-langkahnya adalah sebagai berikut :

a.       Ketiklah dua unit data secara berurutan secara vertical, misalnya : 1, 2.

b.      Arsir / Blok data yang telah diketik.

c.       Setelah data berhasil di blok, maka lepaskan mouse dan klik dan seret tanda hitam kecil, ke bawah (tanda hitam yang dimaksud telah diberikan tanda lingkaran)

LATIHAN SOAL

BAB 4

FUNGSI STATISTIK DAN FUNGSI TEKS

1.    Fungsi Statistik Pada Excel 2007

AVERAGE

Fungsi AVERAGE digunakan untuk mencari nilai rata-rata untuk beberapa data. Secara matematika AVERAGE dirumuskan dengan jumlah dari seluruh data dibagi dengan banyaknya data.

=AVERAGE(number1,number2,…)

COUNT

Fungsi COUNT adalah untuk menghitung banyaknya cell yang mengandung angka.

=COUNT(value1,value2,…)

COUNTA

Fungsi COUNTA adalah untuk menghitung banyaknya cell yang tidak kosong.

=COUNTA(value1,value2,…)

MAX

Fungsi MAX adalah untuk menghitung nilai terbesar dari data.

=MAX(number1,number2,…)

MIN

Fungsi MIN adalah untuk menghitung nilai terkecil dari data.

=MIN(number1,number2,…)

Banyaknya data yang bisa dicari dengan fungsi statistic adalah dari 1 sampai 255.

2.    Menggunakan Referensi Absolut, Referensi Relatif Dan Referensi Campuran

Excel menyediakan tiga jenis referensi sel dalam membuat rumus di dalam batang rumus. Ketiga referensi atau acuan tersebut adalah acuan sel mutlak (Absolut References), acuan sel relative (Relative References), dan acuan sel campuran (Mixed References). Acuan sel mutlak dibentuk dengan symbol $ di depan huruf Kolom dan Baris. Contoh penulisan $B$1, artinya Excel akan menetapkan nilai yang ada pada sel B1 secara tetap.

-          Alamat Relatif

Jika ktia punya sebuah rumus yang merupakan operasi dari beberapa cell, maka Excel akan menerjemahkan alamat relative sebagai lokasi relative suatu alamat cell atau range dari lokasi alamat lainnya.

Contoh : Pada Cell E5 berisi rumus “ =C5*D5 “, Jika rumus tersebut dicopy ke sel E6, maka akan otomatis berubah menjadi “ =C6*D6 “

-          Alamat Absolut

Alamat absolute adalah alamat yang selalu bernilai sama meskipun rumusnya dicopykan kemanapun. Penulisan alamat absolute selalu ditambahkan tanda “ $ “ pada nama kolom dan atau nomor barisnya atau dengan menekan tombol F4 pada keyboard.

Terdapat 2 jenis alamat absolute :

a.       Semi Absolut

Pada alamat semi absolute, penguncian haya dilakukan pada salah satu diantara kolom dan baris.

Contoh :

$C9 : Penguncian kolom C, dengan cara ini ketika cell dicopy ke kanan, alamat cell akan tetap dibaca sebagai $C9 bukan D9, namun ketika dicopy kebawah, alamat cell akan berubah menjadi $C10.

C$9 : Penguncian baris 9, dengan cara ini ketika cell dicopy ke bawah, alamat cell akan tetap dibaca sebagai C$9 bukan C10, namun ketika dicopy ke kanan, alamat cell akan berubah menjadi D$9.

b.      Absolut Mutlak

Pada alamat absolute mutlak, penguncian akan dilakukan pada kolom dan baris sekaligus.

Contoh :

Padas el E3 kita isikan rumus “ =E2 * $E$2 “. Jika rumus itu kita kopikan ke sel F4, maka rumusnya akan menjadi “ =F2 * $E$2 “. Terlihat bahwa alamat E2 akan tetap. E2 disebut alamat absolute mutlak.

Sebuah catatan yang pernah saya buat sewaktu masih kuliah :

Sekarang cobalah Studi Kasus berikut ini :

3.    Penggunaan Fungsi Teks (Left, Mid, Right)

a.    Fungsi LEFT

Fungsi Left digunakan untuk mengambil karakter dari sebelah kiri pada string. Rumus atau Formula dasar dari fungsi left adalah … .

=LEFT(String, X) // =LEFT(alamat sel, X) atau

Keterangan:

String       : kalimat / string, atau sel yang diambil karakternya

X                : Jumlah karakter yang diambil

Misal :

                =LEFT(“bondowoso”,5) – Enter – hasilnya : bondo

                =LEFT(“suroboyo”,4) – Enter – hasilnya : suro

Bagaimana jika menggunakan table di Excel seperti gambar dibawah ini :

b.    Fungsi MID

Fungsi MID digunakan untuk mengambil karakter di tengah string/ alamat sel. Rumus / Formula dasar dari fungsi MID adalah … .

=MID(string,x1,x2) // =MID(alamat sel,x1,x2)

Keterangan :

String       : kalimat/string, atau sel yang akan diambil karakternya

X1              : string diambil dimulai dari karakter ke x1

X2              : banyaknya karakter yang diambil dari kiri

Misal :

=MID(“bondowoso”,5,4) – Enter – hasil : owos

Ilustrasinya seperti keterangan berikut :

Bagaimana jika digunakan pada table Excel seperti gambar dibawah ini :

c.     Fungsi RIGHT

Fungsi RIGHT digunakan untuk mengambil karakter dari sebelah kanan pada string. Rumus / Formula dasar fungsi RIGHT adalah … .

=RIGHT(String, X) // =RIGHT(alamat sel, X)

Keterangan :

String       : Kalimat / String, atau sel yang akan diambil karakternya

X                : Jumlah karakter yang diambil dari kanan.

Misal :

                =RIGHT(“bondowoso”,4) – Enter – hasilnya : wos            

                =RIGHT(“suroboyo”,4) – Enter – apakah hasilny?

Bagaimana jika menggunakan table di Excel seperti gambar dibawah ini :

LATIHAN SOAL FUNGSI STATISTIK :

LATIHAN SOAL ABSOLUTE :

LATIHAN SOAL FUNGSI TEKS :

BAB 5

FUNGSI LOGIKA

1.     Ekspresi atau Pernyataan Logika

Apabila ingin menggunakan operasi logika, biasanya diperlukan ekspresi atau pernyataan logika. Untuk menggunakan ekspresi atau pernyataan logika memerlukan salah satu operator sebagai berikut :

=	Sama dengan
<	Kurang dari
>	Lebih dari
<=	Kurang dari sama dengan
>=	Lebih dari sama dengan
<>	Tidak sama dengan

Sebagai contoh, isi sel B2 dengan angka 75 dan sel C2 dengan angka 100. Selanjutnya isi sel D2 dengan ekspresi atau pernyataan logika berikut ini :

=C2>B2

Dengan eskpresi logika ini, Microsoft Excel 2007 akan menampilkan tulisan TRUE bila pernyataan itu benar dan akan menuliskan FALSE bila pernyataan itu salah. Lihat Gambar diatas.

2.    Operasi Logika

And

Fungsi AND untuk membandingkan dua atau lebih pernyataan, nilai TRUE akan diberikan jika kondisi semua pernyataan bernilai TRUE.

=AND(logika1,logika2,…, …, logika ke-n)

Logika yang ingin dibandingkan bisa sampai 255.

Contoh :

Ø  =AND(TRUE, TRUE) semua pernyataan benar maka nilainya TRUE

Ø  =AND(TRUE, FALSE) satu pernyataan salah maka nilainya FALSE

Ø  =AND(2+2=4, 2+3=5) semua pernyataan benar maka nilainya TRUE

OR

Fungsi OR akan membandingkan dua atau lebih pernyataan, nilai TRUE akan diberikan jika salah satu pernyataan bernilai benar.

=OR(logika1, logika2,…, …, logika ke-n)

Logika yang ingin dibandingkan bisa sampai 255.

Ø  =OR(TRUE) semua pernyataan benar maka nilainya TRUE

Ø  =OR(TRUE, FALSE) satu pernyataan salah maka nilainya TRUE

Ø  =OR(2+2=2, 2+3=4) semua pernyataan benar maka nilainya FALSE

NOT

Fungsi NOT untuk memberikan nilai TRUE jika pernyataan yang ditulis bernilai FALSE atau sebaliknya.

=NOT(logical)

Contoh :

=NOT(FALSE) karena pernyataan bernilai salah maka nilainya mejadi TRUE

=NOT(1+1=2) karena pernyataan bernilai benar maka nilainya menjadi FALSE

3.    Fungsi Logika If

Fungsi IF untuk melakukan suatu perintah untuk setiap kondisi pernyataan bernilai TRUE dan kondisi pernyataan bernilai FALSE.

Bentuk IF tunggal

=IF(logical_test, value_if_true, value_if_false)

Logical_test adalah nilai atau ekspresi yang bisa bernilai TURE atau FALSE. Misalnya A1=100 adalah ekspresi logika. Jika A1 bernilai 100 maka ekspresi itu bernilai TRUE, dan jika A1 bukan 100 maka ekspresi itu bernilai FALSE.

Value_if_true adalah perintah bila Logical_test bernilai TRUE. Misalnya “Seratus”, maka bila A1 100 maka akan muncul “Seratus”.

Value_if_false adalah perintah bila Logical_test bernilai FALSE. Misalnya “Bukan Seratus”, maka bila A1 100 maka akan muncul “Bukan Seratus”

Bentuk IF bertingkat

=IF(logical_test, value_if_true,…IF(logical_test,value_if_true,value_if_false)…)

Penggunaan fungsi IF bisa mengandung sampai logical_test.

Contoh 1 :

Cell A1=A

=IF(A1=”A”,”APEL”,”BELIMBING”) – Enter – Hasil adalah “APEL”

Contoh 2 :

Cell A1=3

=IF(A1=1,”ANGKA 1”,IF(A1=2,”ANGKA 2”,IF(A1=3,”ANGKA 3”,”TIDAK TAHU”)))

Hasil = “ANGKA 3”

Microsoft Excel menyediakan fungsi tambahan yang dapat digunakan untuk menganalisis data Anda berdasarkan suatu kondisi. Misalnya, untuk menghitung jumlah kemunculan string teks atau nomor dalam kisaran sel, gunakan fungsi COUNTIF dan worksheet COUNTIFS. Untuk menghitung jumlah berdasarkan string teks atau nomor dalam jangkauan, gunakan fungsi SUMIF dan SUMIFS worksheet.

LATIHAN SOAL IF :

BAB 6

LOOKUP DAN GRAFIK

1.    Fungsi Pencarian dan Rujukan

VLOOKUP

Fungsi Vlookup digunakan untuk mencari nilai berdasarkan pembacaan pada table referensi yang ditentukan berdasarkan no kolom (data tersusun secara vertical). Lihat Tabel :

Bentuk Penulisan

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Lookup_value, value yang dicari pada kolom pertama table (sel acuan)

Table_array, Tabel kunci yang terdiri dari dua kolom atau lebih dimana kolom pertama berisi value yang dicari (table acuan)

Col_index_num, menyatakan kolom ke berapa dari table kunci yang ingin ditampilkan bila data ditemukan.

Range_lookup, diisi false atau 0 jika data pada table array belum diurutkan, diisi true atau 1 jika data pada table array sudah diurutkan.

Contoh :

=VLOOKUP(C3,$F$3:$G$7,2,FALSE)

=VLOOKUP(C3,TABEL,2,0)

HLOOKUP

Fungsi Hlookup digunakan untuk mencari nilai berdasarkan pembacaan pada taabel referensi yang ditentukan berdasarkan no baris (data tersusun secara horizontal). Lihat Tabel :

Bentuk Penulisan

=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Lookup_value, value yang dicari pada baris pertama table (sel acuan)

Table_array, Tabel kunci yang terdiri dari dua baris atau lebih dimana baris pertama berisi value yang dicari. (table acuan)

Col_index_num, menyatakan baris ke berapa dari table kunci yang ingin ditampilkan bila data ditemukan.

Range_lookup, diisi false atau 0 jika data pada table array belum diurutkan, diisi true atau 1 jika data pada table array sudah diurutkan.

2.    Pembuatan Grafik

Pada excel 2007 membuat grafik sangatlah mudah. Cukup dengan memilih jenis grafik, tata letak grafik style grafik yang dapat dipilih dengan mudah pada Ribbon.

Contoh kali ini adalah membuat grafik Ekspor Hasil Pertanian. Sebelum membuat grafik pastikan anda sudah membuat tabelnya. Perhatikan gambar di bawah ini.

1) Blok table yang ingin dibuat grafik termasuk kolom bulan (Januari, Februari, Maret) dan kolom hasil pertanian (Kopi, Cokelat, Kelapa)

2) Klik Tab Insert – Chart – Column

3) Setelah anda klik Column, akan muncul tipe grafik untuk kolom. Pilih Clustered Colomn yang ada pada 2-D Column. Hasilnya adalah seperti gambar di bawah :

Setiap anda melakukan perubahan pada table, secara otomatis pada grafik juga akan berubah.

3.    Mengubah Layout atau Style pada Grafik Excel 2007

Setelah anda membuat sebuah grafik, anda dapat dengan mudah untuk memodifikasi tampilannya. Anda bisa memilih Layout dan Style grafik standar yang disediakan oleh Excel. Microsoft Office Excel menyediakan banyak jenis Layout dan Style grafik, tetapi anda juga bisa mengedit secara manual setiap elemen yang ada pada grafik yang telah anda buat.

a.       Memilih Layout Grafik Standar

Ø  Klik grafik yang anda ingin rubah layoutnya, ini akan memunculkan Chart Tools, yaitu tab Design tab Layout dan tab Format

Ø  Pada tab Design, dalam Chart Layout, klik tipe layout yang anda ingin gunakan.

Gunakan More  untuk menampilkan semua Layout.

b.      Memilih Style Grafik Standar

Ø  Klik grafik yang anda ingin ubah Stylenya, ini akan memunculkan Chart Tools, yaitu tab Design, tab Layout, dan tab Format

Ø  Pada tab Design, dalam Chart Style, klik style grafik yang anda ingin gunakan.

Gunakan More  untuk menampilkan semua Layout.

c.       Mengubah Layout Grafik secara Manual

Ø  Klik grafik atau pilih elemen dari grafik yang anda ingin ubah layoutnya.

Ø  Pada tab Layout pilih

Labels untuk mengatur Chart Title, Axis Titles, Legend, Data Labels dan Data Table.

Axes untuk mengatur Axes dan Gridlines

Background untuk mengatur latar belakang

d.      Mengubah Style Grafik secara Manual

Ø  Klik grafik yang anda ingin format

Ø  Pada tab Format, di Current Selection, klik tanda panah pada Chart Element untuk memilih elemen grafik yang anda ingin format.

Ø  Pilih style yang anda inginkan pada Shape Style. Anda juga bisa mengatur manual Shape Fill, Shape Outline dan Shape Effect.

e.      Menyimpan Grafik sebagai Template

Ø  Klik grafik yang ingin dijadikan sebagai Template

Ø  Pada tab Design, di Type Group, klik Save As Template

Posted in: