Materi Training Excel Lanjutan

TRAINEE’S HANDSOUT Training Penggunaan Excel Lanjutan Abstract Microsoft Excel saat ini sudah menjadi sofware penting dalam dunia kerja, terutama untuk anda yang memproses banyak data. Sudah maksimalkah anda menggunakan Microsoft Excel dalam pekerjaan anda? Jonathan Jacob [email protected]

Daftar IsiLesson 1: Formula Lanjutan ........................................................................2 Transpose ..............................................................................................2 Gabungan dan Irisan ...............................................................................3Lesson 2: Range Bernama & Konstanta Bernama ...........................................4 Range Bernama ......................................................................................4 Konstanta Bernama .................................................................................5 Pengelolaan Nama...................................................................................6 Range Bernama Dinamis ..........................................................................7Lesson 3: Validasi Input............................................................................ 11 Validasi Input untuk Angka..................................................................... 11 Validasi Input untuk Teks ....................................................................... 12 Validasi Input untuk Tanggal .................................................................. 13 Validasi Input dengan List ...................................................................... 14 Menambahkan Input Message................................................................. 15 Bermacam-macam Validasi Input ............................................................ 15Lesson 4: Workbook Template ................................................................... 16 Membuat Template................................................................................ 16 Membuat Workbook Baru dengan Template .............................................. 16Lesson 5: Beberapa Trik ........................................................................... 18 Operasi Aritmatika Cepat ....................................................................... 18 Paste Special Transpose ......................................................................... 20 Beberapa Shortcut Keyboard .................................................................. 21 1

Lesson 1: Formula LanjutanTransposeUntuk menggunakan fungsi Transpose, lakukan langkah-langkah berikut:1. Pertama, pilih range untuk hasil transpose yang sesuai2. Kedua, ketikkan ‘=TRANSPOSE(‘3. Lalu pilih range data asal, contoh: A1:C1, lalu tutup dengan tanda kurung tutup ‘)’4. Akhiri dengan menekan CTRL + SHIFT + ENTER.Catatan: Formula bar menunjukkan bahwa ini adalah sebuah array formuladengan mengurungnya dengan kurung kurawal {}. Untuk menghapus formula ini,pilih range-nya lalu tekan Delete. 2

Gabungan dan IrisanContoh ini menggambarkan bagaimana untuk mendapatkan gabungan dan irisandua range (contoh di bawah sebagai ilustrasi) di Excel.1. Gunakan koma untuk mendapatkan gabungan dari dua range.Gabungan Dua Range di ExcelPenjelasan: fungsi SUM menjadi = SUM (C4: D8) + SUM (D7: E11) = 20.2. Gunakan spasi untuk mendapatkan irisan dua range.Irisan Dua Range di ExcelPenjelasan: fungsi SUM menjadi = SUM (D7: D8) = 2. 3

Lesson 2: Range Bernama & Konstanta BernamaRange BernamaUntuk membuat range bernama, lakukan langkah-langkah berikut:1. Pilih range (contoh A1:A4).2. Pada tab Formulas, klik Define Name.3. Masukkan namanya dan klik OK. 4

Ada cara yang lebih cepat untuk melakukan hal ini:4. Pilih range, ketikkan namanya di kotak Name dan tekan Enter.5. Sekarang Anda dapat menggunakan nama range ini dalam formula Anda. sebagai contoh: sum(Prices).Konstanta BernamaUntuk membuat konstanta bernama, lakukan langkah-langkah berikut:1. Pada tab Formulas, klik Define Name.2. Masukkan sebuah name, ketikkan sebuah nilai, dan klik OK. 5

Sekarang Anda dapat menggunakan nama konstanta ini dalam formula Anda.Catatan: jika ada perubahan tarif pajak, gunakan Name Manager untuk mengeditnama dan Excel secara otomatis akan memperbarui semua rumus yangmenggunakan TaxRate.Pengelolaan NamaUntuk mengedit dan menghapis nama-nama yang terdefinisi, laksanakanlangkah-langkah berikut:.1. Di tab Formulas, klik Name Manager. 6

2. Contoh, pilih TaxRate dan klik Edit.Range Bernama DinamisSebuah range dinamis mengembang secara otomatis saat Anda menambahkannilai ke range tersebut.1. Contohnya, pilih range A1:A4 and namakan sebagai Prices2. Hitung jumlahnya dengan SUM() 7

3. Bila Anda menambahkan nilai ke range tersebut, Excel tidak akan memperbarui hasil SUM() nya.Untuk memperluas range bernama secara otomatis saat Anda menambahkan nilaike range tersebut, lakukan langkah-langkah berikut.4. Pada tab Formulas, klik Name Manager. 8

5. Klik Edit.6. Klik di kotak \"Refers to\" dan masukkan formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)Penjelasan: Fungsi Offset membutuhkan 5 argumen. Referensi: $A$1, barisoffset: 0, kolom offset: 0, tinggi: COUNTA ($A:$A), lebar: 1.COUNTA($A:$A) menghitung jumlah nilai dalam kolom A yang tidak kosong. BilaAnda menambahkan nilai ke range tersebut, COUNTA ($A:$A) meningkat.Akibatnya, range bernama akan mengembang.7. Klik OK dan tutup. 9

Sekarang, ketika Anda menambahkan nilai ke range tersebut, Excel akanmengubah nilai sum secara otomatis 10

Lesson 3: Validasi Input DataKadang mungkin kita ingin membuat aturan pada data yang diketikkan padaworksheet. Misalnya, pada range tertentu hanya boleh diisi dengan angka saja,kemudian untuk sel yang lain hanya boleh diisi dengan teks saja. Hal ini dapatdilakukan dengan menggunakan fitur Data Validation di Excel 2007.Data Validation membantu mengurangi kesalahan saat input data worksheet.Fitur ini sangat bermanfaat terutama bila file Excel dikerjakan oleh lebih dari satuorang.Misalnya: Pada range tertentu (B1:F1) hanya boleh diisi dengan angka 100 – 9999saja.Bila diisi dengan data lain akan diberikan peringatan seperti ini:Cara membuatnya cukup mudah. Berikut akan disajikan cara validasi input untukangka, teks, dan tanggal.Validasi Input untuk Angka1. Pilih kumpulan sel (range).2. Pada tab Data, group Data Tools, klik Data Validation, dan pilih Data Validation.3. Pada tab Setting, di bagian Allow, pilih Whole number.4. Di bagian Data, pilih between.5. Di kotak Minimum, isi angka terendah, misalnya 100. 11

6. Di kotak Maximum, isi angka tertinggi, misalnya 9999.7. Klik OK untuk menutup kotak dialog.Validasi Input untuk Teks1. Pilih kumpulan sel (range).2. Pada tab Data, group Data Tools, klik Data Validation, dan pilih Data Validation. Pada tab Setting, di bagian Allow, pilih Custom.3. Di kotak Formula, isi dengan formula: =IsText (B2), sel B2 merupakan sel pertama pada range.4. Klik OK untuk menutup kotak dialog. 12

Validasi Input untuk Tanggal1. Pilih kumpulan sel (range).2. Pada tab Data, group Data Tools, klik Data Validation, dan pilih Data Validation. Pada tab Setting, di bagian Allow, pilih Date.3. Di bagian Data, pilih between.4. Di kotak Start date, isi dengan tanggal awal, misalnya 3/1/2010.5. Di kotak End date, isi dengan tanggal akhir, misalnya 3/31/2010.6. Klik OK untuk menutup kotak dialog.Untuk mempermudah pengisian data, kita dapat menambahkan input message,sehingga saat sel tersebut diklik akan muncul keterangan (lihat gambar berikut)tentang data yang boleh diinput. 13

Validasi Input dengan ListUntuk membuat list atau daftar pilihan pada input, lakukan langkah-langkahberikut:1. Pilih range E2:E7.2. Pada tab Data, group Data Tools, klik Data Validation, dan pilih Data Validation.3. Pada dialog Data Validation, masukkan opsi berikut: • Pada pilihan Allow, masukkan List. • Pada kotak Source, masukkan range A2:A7.Sekarang jika anda tempatkan cursor pada sel E2 akan terlihat pada sel tersebutada tambahan panah bawah seperti pada combo box, dan jika Anda klik panahtersebut akan muncul pilihan nilai yang berasal dari range A2:A7.Selain itu, langkah 3 juga dapat menggunakan range bernama. Caranya:4. Tempatkan kursor pada kotak Source lalu tekan F3, maka akan mucul kotak dialog seperti ini: 14

5. Pilih salah satu range bernama yang sudah didefinisikan sebelumnya, lalu tekan OK.Dengan menggunakan range bernama kita bisa secara lebih mudah membuatpilihan nilai dari range bernama pada worksheet lain di dalam workbook yangsama.Menambahkan Input Message1. Saat membuat validasi di kotak dialog Data Validation, klik tab Input Message.2. Di bagian Title, isi dengan judul, misalnya: Keterangan.3. Di kotak Input message, isi dengan kalimat, misalnya: “Isi dengan angka100 – 9999”.4. Klik OK.Bermacam-macam Validasi InputUntuk mengetahu lebih banyak trik validasi data lainnya yang dimungkinkan dapatdilihat di http://www.contextures.com/xlDataVal07.html#Unique 15

Lesson 4: Workbook TemplateExcel menyediakan file template yang dapat kita tambahkan sendiri. Denganmenggunakan file template ini kita dapat menyiapkan pengaturan untuk format,validasi input, range bernama, konstanta bernama, dan lain sebagainya untukworkbook baru. Setiap kali kita membuat workbook dengan template tersebut,kita sudah langsung siap bekerja di dalamnya dengan environment yang kitainginkan.Membuat Template1. Buka workbook yang anda ingin gunakan sebagai template..2. Klik Microsoft Office Button , lalu klik Save As.3. Dalam kotak File name, ketik nama yang anda ingin gunakan untuk template.4. Di kotak Save as type, klik Excel Template, atau klik Excel Macro-Enabled Template jika workbook mengandung macro yang anda ingin sediakan di dalam template.5. Klik Save.Template secara otomatis ditempatkan dalam folder Template untuk memastikanbahwa itu tersedia bila Anda ingin menggunakannya untuk membuat workbookbaru.TIP Anda dapat menyalin buku kerja Excel ke dalam folder Template, maka Andadapat menggunakan buku kerja sebagai template tanpa menyimpannya dalamformat file template (.xltx atau .xltm). Pada Windows Vista/Windows 7/8/8.1,folder Template biasanya ada di C:\Users\<nama Anda>\AppData\Roaming\Microsoft\Templates. Pada Windows XP, folder Template biasanya ada diC:\Documents and Settings\<nama Anda>\Application Data\Microsoft\Templates.Membuat Workbook Baru dengan Template1. Klik Microsoft Office Button , lalu klik New.2. Pada Templates, lakukan langkah-langkah berikut: • Untuk membuat workbook berdasarkan template yang sering digunakan, klik Blank and recent, lalu pada Recently Used Templates, double-click template yang ingin digunakan. • Untuk membuat workbook berdasarkan template yang ada, klik Installed Templates, lalu pada Installed Templates, double-click template yang ingin digunakan. • Untuk membuat workbook berdasarkan template yang anda buat, klik My templates, lalu pada tab My Templates, double-click template yang ingin dipakai. 16

• Untuk membuat workbook berdasarkan workbook lainnya, klik New from Existing, lalu pilihlah workbook yang ingin digunakan.TIP Ketika Anda tersambung ke Internet, Anda juga dapat mengakses templateyang tersedia dari Office Online. Di bawah Microsoft Office Online, klik kategoritemplate yang ada, dan kemudian di bawah kategori tersebut, klik dua kalitemplate yang ingin Anda download. Jika template yang ingin Anda download telahdisediakan oleh anggota dari Komunitas Online Microsoft Office, Anda harusmengklik I accept to accept the License Agreement for Community TemplatesTerms of Use sebelum Anda dapat men-download template tersebut. 17

Lesson 5: Beberapa TrikOperasi Aritmatika CepatGunakan Paste Special untuk melakukan beberapa operasi terhadap rentangancell secara cepat di Excel.1. Pilih sel D3.2. Klik kanan lalu klik Copy.3. Kemudian pilih range A1:B8.4. Klik kanan, lalu klik Paste Special... 18

5. Klik MultiplyCatatan: bisa juga pilih Divide, Add atau Subtract untuk membagi, menambahkanatau mengurangi dengan suatu nilai.6. Klik OK.Catatan: semua nilai meningkat sebesar 1.1 kali. Tanpa fitur ini, Anda harusmembuat range sementara (dengan formula yang kalikan nilai-nilai dalam rangeA1: B8 dengan 1.1) dan kemudian mengganti range A1: B8 dengan copy danpaste as value dari range sementara. 19

Paste Special TransposeBerikut adalah cara termudah untuk transpose data:1. Pilih range A1:C1.2. Klik kanan, lalu klik Copy. Advertisement3. Pilih sel E2.4. Klik kanan, lalu klik Paste Special... 20

5. Centang Transpose.6. Klik OK.Beberapa Shortcut Keyboard• Copy ctrl+C• Cut ctrl+X• Paste ctrl+V (berbeda efek dengan Enter)• Print ctrl+P• Save ctrl+S• Lompat ke sel paling kanan sebelum sel kosong di baris yang sama ctrl+[panah kanan]• Lompat ke sel paling kiri sebelum sel kosong di baris yang sama ctrl+[panah kiri]• Lompat ke sel paling bawah sebelum sel kosong di kolom yang sama ctrl+[panah bawah]• Lompat ke sel paling bawah sebelum sel kosong di kolom yang sama ctrl+[panah bawah] 21