belajar excel

56
PEPATAH MENGATAKAN : TAK KENAL MAKA TAK SAYANGKENALILAH PROGRAM MICROSOFT (MS) EXCEL KARENA DENGAN MICROSOFT (MS) EXCEL KITA AKAN DIPERMUDAH DALAM SEGALA URUSAN PERHITUNGAN ADA KESULITAN ????? JANGAN KHAWATIR HUBUNGI : EMAIL : [email protected] [email protected] kesala6.blogspot.com [email protected] [email protected] kelasa6.blogspot.com 1

Upload: budi

Post on 10-Jun-2015

4.053 views

Category:

Documents


10 download

TRANSCRIPT

Page 1: Belajar Excel

PEPATAH MENGATAKAN :“TAK KENAL MAKA TAK SAYANG”

KENALILAH PROGRAM MICROSOFT (MS) EXCEL

KARENA DENGAN MICROSOFT (MS) EXCEL

KITA AKAN DIPERMUDAH DALAM SEGALA URUSAN PERHITUNGAN

ADA KESULITAN ????? JANGAN KHAWATIR

HUBUNGI :

EMAIL : [email protected]@gmail.comkesala6.blogspot.com

[email protected] [email protected]

1

Page 2: Belajar Excel

APLIKASI KOMPUTER BERBASIS KEUANGAN

1. PENGANTAR MICROSOFT (MS) EXCEL

Microsoft excel merupakan salah satu aplikasi dalam Microsoft Office. Versi dari MS excel menyesuaikan dengan versi MS Officenya.

a. Menjalankan dan pengenalan Komponen MS Excel

Langkah-langkah untuk menjalankan aplikasi MS excel adalah:Klik menu start, arahkan pointer pada all programs, pilih Microsoft office, kemudian klik Microsoft Office Excel

[email protected] [email protected]

2

Page 3: Belajar Excel

Gambar 1. Menu Short cut exel

Setelah langkah 1 maka akan muncul tampilan dengan berbagai pengenalan tombol sebagai berikut:

Gambar 2. Jendela Aplikasi Excel

Dari tampilan di atas terlihat beberapa bagian, yaitu: 1) Menu Bar terdiri dari beberapa menu yang dapat digunakan

untuk membantu pekerjaan kita: File, Edit, View, Insert, Format, Tools, Data, Windows dan Help.

2) Tollbar merupakan kotak yang berisi tombol-tombol untuk mempercepat proses yang dilakukan (tombol open, save, preview, dll).

3) Minimize untuk memperkecil tampilan excel dan akan terlihat icon pada taskbar.

4) Maximize untuk memperbesar tampilan excel sehingga penuh satu layer.

[email protected] [email protected]

3

Lembar kerja

Sel aktif/PointerToolbar Standar Toolbar formatting

Formula bar

Navigasi sheet

Menu Bar

Scroll Bar Vertikal

Scroll Bar Horisontal

Name Box

Minimize, restore, close

Task pane

Page 4: Belajar Excel

5) Restore untuk mengembalikan tampilan jendela windows (excel) kebentuk semula.

6) Name Box adalah sebuah kotak yang menunjukkan alamat sel aktif yang digunakan untuk memberi nama sel atau range.

7) Formula Bar berfungsi untuk menuliskan persamaan dan rumus yang akan ditampilkan dalam sel serta dapat digunakan untuk menuliskan isi dari suatu sel.

8) Task Pane merupakan sebuah panel yang menyediakan beberapa fasilitas bantuan atau informasi tertentu.

9) Scroll Bar berfungsi untuk menggulung layer secara vertical atau horizontal.

10) Lembar kerja terdiri dari baris dan kolom. Kolom-kolom sel diberi nama A, B, C dan seterusnya hingga IV dengan total kolom 256 kolom. Sedangkan baris-baris (rows) ditandai dengan angka 1, 2, 3, dan seterusnya hingga 65536.

11) Navigasi Worksheet berfungsi untuk menampilkan atau berpindah dari satu sheet ke sheet yang lain dalam satu buku kerja.

b. Mengaktifkan Buku Kerja

Langkah-langkah yang digunakan untuk mengaktifkan Buku Kerja adalah sebagai berikut:1) Klik menu Start, lalu pilih All Programs, kemudian klik open

office Document.2) Kemudian pilih file yang diinginkan dan klik menu open

Selain itu, ada cara lain: klik Start, pilih My Recent Document, kemudian pilih file yang diinginkan.

c. Memilih dan Memberi Nama Range

Langkah yang digunakan untuk memilih range dengan menggunakan tombol Ctrl adalah:1. Pilih beberapa sel yang diinginkan dengan dengan cara dragging2. Tekan tombol Ctrl dan jangan dilepaskan sebelum proses

memilih range selesai.3. Pilih beberapa sel atau range lain dan dengan langkah 2

tersebut, maka kita dapat memilih range sesuai dengan keinginan kita.Langkah untuk memberi nama pada range tertentu adalah:

1) Sorot range tertentu sesuai dengan keinginan kita.2) Klik Insert, pilih Name, kemudian klik Define.

[email protected] [email protected]

4

Page 5: Belajar Excel

3) Ketik nama range sesuai dengan keinginan kita pada bagian Names in workbook.

4) Pada bagian Refers to terlihat alamat range yang kita pilih.5) Klik tombol Add untuk membuat nama baru dan dimasukkan

dalam daftar nama range.6) Klik Close atau OK.

d. Menambah Komentar

Fitur komentar ini digunakan untuk memperjelas maksud dari sel tertentu. Langkah yang digunakan untuk membuat komentar adalah:1) Klik alamat sel yang akan diberi komentar2) Klik kanan pada alamat sel yang akan diberi komentar, lalu pilih

Insert Comment.3) Tuliskan komentar yang diinginkan

Untuk mengedit komentar: klik kanan sel yang diberi komentar, kemudian pilih Edit Comment.

2. ENTRI DATA DAN SISTEM SECURITY

a. Entri Data pada Lembar Kerja secara BerurutanData dapat dimasukkan dalam lembar kerja secara berurutan, misalnya dalam memberikan nomor urut secara cepat:1) Klik alamat sel A1 dan ketik angka 12) Klik alamat sel A2 dan ketik angka 23) Blok atau sorot range A1:A24) Posisikan mouse dipojok kanan bawah sehingga muncul tanda +5) Dragging atau tekan terus mouse dan digeser kebawah sesia dengan keinginan.Cara tersebut juga berlaku untuk teks, misalnya memberi nama hari dalam seminggu.

b. Entri Data dengan Referensi Sel dan Sheet lainData dapat diinput melalui sel dan sheet lain sehingga jika kita

mengubah perhitungan pada sel tertentu maka akan berubah semua secara otomatis. Untuk melakukan entri data dengan referensi sel lain adalah sebagai berikut: ketik tanda ( = ) pada sel tempat entri data, kemudian klik alamat sel yang diinginkan dapat berubah secara otomatis jika sel input data dirubah. Perhatikan gambar dibawah ini.

[email protected] [email protected]

5

Page 6: Belajar Excel

Gambar 3. Tampilan referensi sel

Selain itu, data dapat diinput dari sheet lain seingga jika sel tertentu pada suatu sheet dirubah, maka sheet lain yang sudah diberi rumus akan berubah pula. Cara ini dapat memudahkan penghitungan dan menghindari terjadinya salah pengetikan karena proses pengetikan data lebih diperkecil frekuensinya.Langkah yang digunakan adalah sebagai berikut:

1) Klik sel tertentu dalam sheet tertentu, misalnya sel A2 pada sheet 1 dan ketik angka tertentu (misalnya: angka 99).

2) Klik sheet lain dan pilih alamat sel tertentu pada sheet tersebut, misalnya sheet2 alamat sel A4.

3) Ketik rumus (=sheet1!A2) pada sheet2 sel A4 dan tekan enter, maka angka 99 akan muncul pada alamat yang diinginkan tersebut.

Gambar 4. Tampilan Data Referensi sheet lain

[email protected] [email protected]

6

Page 7: Belajar Excel

Di samping angka, cara tersebut juga berlaku untuk teks sehingga banyak memudahkan pekerjaan kita dan menghindari banyak kesalahan.

c. Entri Data dengan Persamaan Matematika

Operasi matematika yang digunakan adalah pangkat (^), perkalian (*), Pembagian (/), penambahan (+), dan pengurangan (-). Langkah yang digunakan untuk setiap operasi matematika tersebut sama, sebagai contohnya adalah1) Ketik angka yang akan dikalikan pada dua alamat sel berbeda

atau lebih, misalnya kita mengalikan 99 dengan 98 dan ditulis pada sel A2 dan Sel A3.

2) Tempatkan pointer pada sel yang akan dijadikan tempat hasil perkalian, misalnya sel A4.

3) Tuliskan pada sel A4 (=A2*A3), kemudian tekan enter, maka hasil perkaliannya (9702) akan muncul.

Untuk operasi matematika yang lain caranya sama seperti di atas.

d. Sistem Proteksi Lembar Kerja (Worksheet)

Langkah yang digunakan untuk melakukan proteksi terhadap lembar kerja (Worksheet) adalah:

1) Pilih lembar kerja yang akan diproteksi2) Pilih dan klik menubar Tools, kemudian pilih Protection, lalu

pilih Protect Sheet, sehingga muncul tampilan gambar sebagai berikut:

[email protected] [email protected]

7

Page 8: Belajar Excel

Gambar 5. Kotak Dialog Protect Sheet

3) Ketik sandi/password pada kotak dialognya dan klik OK4) Ketik sandi/password yang sama pada kotak dialog Confirm

Password, kemudian tekan OK

Untuk membuka Proteksi cara yang digunakan adalah klik Tools pilih Protection, kemudian klik Unprotct Sheet…, lalu masukkan passwordnya dan klik OK.

e. Menyimpan file dengan password

Untuk melindungi file-file kita sehingga tidak dapat diubah-ubah isinya, maka file tersebut perlu disimpan dengan password. Sistem security ini yang dapat menjaga keamanan isi file dan hanya orang-orang tertentu saja yang dapat membukanya.Langkah-langkah yang digunakan untuk menyimpan file dengan password adalah sebagai berikut:1) Klik menubar file, kemudian pilih save/save as.2) Ketik file name untuk memberi nama file pada bagian file name,

misalnya “latihan akuntansi sekolah-passw”.

Gambar 6. Proses penyimpanan data dengan password

[email protected] [email protected]

8

Page 9: Belajar Excel

3) Pada kotak dialog di atas klik tombol tools kemudian pilih general options.

4) Maka dilayar akan muncul kotak dialog save options sbb:

Gambar 7. Proses pengisian sandi5) Tempatkan kursor pada kotak password to open dan ketik

sandinya sesuai dengan keinginan saudara, kemudian tempatkan kursor pada password to modify dan ketik sandi yang berbeda atau sama dengan sebelumnya kemudian klik OK sehingga muncul tampilan berikut:

Gambar 8. Confrimasi sandi

6) Pada kotak dialog Confirm Password ketik ulang sandi pertama yang dibuat sebelumnya, yaitu pada password to open dan klik OK.

7) Kemudian akan muncul Confirm Password yang kedua dan ketik ulang sandi kedua yang dibuat sebelumnya, yaitu pada password to modify.

8) Klik tombol save untuk menyimpan file saudara.

3. Format Data

a. Format Perataan (Alignment)

[email protected] [email protected]

9

Page 10: Belajar Excel

Untuk mengatur posisi data ditengah, kanan, dan kiri dalam sel dilakukan format perataan melalui menu toolbar Formatting, sedangkan untuk mengatur teks sehingga tidak terlalu panjang dalam sel dilakukan dengan langkah sebagai berikut:

1) Block (sorot) range yang akan diformat2) Klik menubar Format, kemudian pilih Cells atau tekan Ctrl+13) Klik Alignment, kemudian pilih Center untuk vertical dan

horizontal4) Centang bagian Warp text untuk perataan kata sehingga

kelebihan kata dalam kolom akan berada dibawahnya5) Klik OK6) Klik diluar block untuk menghilangkan tanda block.

b. Format Angka Format angka digunakan untuk mengubah tampilan data normal menjadi format yang sesuai dengan kebutuhan kita, dan langkah-langkahnya sebagai berikut:

1) Sorot range yang akan diubah2) Klik menubar Format, lalu Cells atau menekan tombol Ctrl+13) Klik tab Number, lalu tentukan kategori angka (klik Category,

misal pilih general)4) Decimals Places berfungsi untuk menentukan digit angka

disebelah kanan tanda koma.5) Aktifkan pemisah ribuan dengan memberi ceklist pada Use 1000

separator6) Jika angka yang dimasukkan negatif, maka kita dapat memilih

format angka negatif pada Negative numbers7) Klik OK

Melalui langkah tersebut diatas juga dapat melakukan beberapa pilihan kategori format angka, misalnya format accounting, date, time, text, dan lain-lain, yang mana kita dapat mencobanya sendiri dengan langkah yang hampir sama, tetapi langkah 3 disesuaikan.

c. Format Bingkai (Border) dan Pewarnaan Range

Langkah yang digunakan untuk format border agar memudakan dalam pembacaan kelompok data adalah sebagai berikut:1. Sorot range yang akan diformat

[email protected] [email protected]

10

Page 11: Belajar Excel

2. Klik menubar Format, lalu Cells atau menekan tombol Ctrl+1

3. Klik tab Border4. Tentukan bingkai sesuai dengan keinginan, misal klik

outline dengan style garis tertentu (dengan klik garis pada pilihan style)

5. Bagian Color digunakan untuk memberi warna garis6. Klik OK

Untuk membuat format lembar kerja menarik dan memudahkan dalam melihat kelompok data, maka diperlukan perwarnaan range dengan langkah-langkah sebagai berikut:1) Sorot range yang akan diwarnai2) Klik menubar Format, lalu Cells atau menekan tombol Ctrl+13) Klik tab Patterns4) Tentukan pilihan warna sesuai dengan keinginan5) Kita dapat menentukan pola garis dan warna melalui tab

Patterns ini.6) Klik OK

d. Format Painter

Format painter berfungsi untuk menyalin atribut suatu data dan dikenakan pada data lainnya. Langkah-langkahnya adalah:1) Sorot range yang akan diformat2) Klik lambang painter pada Toolbar Standar, lalu klik ganda

lambang painter tersebut

3) Klik range yang akan dikenai format sesuai keinginan kita (pointer mouse akan berubah menjadi gambar kuas)

4) Untuk menormalkan klik ganda pada lambang painter lagi.

4. Proses Database

a. Mengurutkan Data

Langkah-langkah untuk mengurutkan data adalah sebagai berikut:1) Blok range data yang akan diurutkan2) Klik menubar Data, lalu Klik Sort….

[email protected] [email protected]

11

Page 12: Belajar Excel

3) Tentukan judul kolom sebagai panduan pengurutan pada bagian Sort by

4) Klik Ascending untuk pengurutan secara menaik atau klik Descending untuk pengurutan secara menurun.

5) Pada bagian My data range has beri pilihan Header row jika kita menganggap baris pertama dari range yang dipilih merupakan baris judul atau No Header row jika menganggap judul tidak ada.

6) Klik OK

b. Entri Data dengan Form

Langkah-langkah yang digunakan dalam pembuatan form untuk pengisian data adalah sebagai berikut:1) Buatlah database dengan judul kolom (field name), misalnya:

nomor induk mahasiswa, nama mahasiswa, alamat mahasiswa

Gambar 9. Tabel untuk data form

2) Blok judul kolom tersebut seperti gambar 9.3) Klik menubar Data, kemudian klik Form sehingga muncul

konfirmasi bahwa satu baris paling atas dari range yang dipilih akan digunakan sebagai baris judul dan akan ditampilkan

4) Klik OK, maka akan muncul kotak form untuk entri data yang sesuai dengan judul kolom tersebut.

[email protected] [email protected]

12

Page 13: Belajar Excel

Gambar 10. Pengisian data melalui Form

5) Terdapat beberapa tombol, yaitu New, Delete, Find Prev, Find Next, Criteria, Close

c. Melacak Data dengan Data FilterData filter digunakan untuk menemukan data secara cepat. Data filter ada dua macam, yaitu Autofilter dan Advanced FilterLangkah-langkah untuk pelacakan dengan AutoFilter adalah1) Blok range judul kolom2) Klik menubar Data, kemudian pilih Filter dan klik AutoFilter

sehingga muncul kotak pilihan (dropdown)

[email protected] [email protected]

13

Page 14: Belajar Excel

Gambar 11. Pelacakan data dengan AutoFilter

3) Pilih kriteria yang diinginkan pada setiap judul kolom (field), dengan cara klik daftar pilihan (dropdown) pada judul kolom field tersebut, misalnya mencari data berdasarkan kota asal, maka klik dropdown pada alamat mahasiswa dan pilih kota asalnya (yogyakarta), maka akan muncul mahasiswa yang dari Yogyakarta saja.

d. Validasi AngkaApabila kita ingin membuat validasi untuk mengisi data Pengeluaran yang hanya dapat diisi dengan nilai maximal 5.000.000,-, maka langkah-langkah yang digunakan adalah sebagai berikut:

1) Blok range yang akan dibatasi pengeluarannya.2) Klik menubar Data, kemudian pilih Validation sehingga muncul

tampilan sebagai berikut:

[email protected] [email protected]

14

Page 15: Belajar Excel

Gambar 12. Kotak Dialog Data Validation

3) Klik drop down Allow, kemudian tentukan pilihan Decimal4) Klik drop down Data, kemudian tentukan pilihan less than5) Ketik 5.000.000,- pada bagian Maximum6) Klik tab Input Message, kemudian ketik teks “Validasi” pada

bagian Title dan ketik teks “Entri data pengeluaran maximum 5.000.000,-“ pada bagian Input Message

7) Klik tab Error Alert, kemudian klik pilihan Stop pada bagian Style. Ketik teks “Salah” pada bagian Title dan ketik teks “data harus di bawah 5.000.000,- pada bagian Error message seperti tampilan berikut:

[email protected] [email protected]

15

Page 16: Belajar Excel

Gambar 13. Kotak Dialog Data Validation Error Alert

8) Klik Tombol OK

5. Tampilan Data dalam Bentuk Grafik

a. Membuat GrafikLangkah-langkah yang digunakan untuk membuat grafik adalah sebagai berikut:1) Blok tabel yang akan ditampilkan dalam bentuk grafik2) Klik menubar Insert, kemudian pilih Chart, klik tombol Chart

Wizard pada toolbar standar.

Gambar 14. Tabel dan Chart Wizard

[email protected] [email protected]

16

Page 17: Belajar Excel

3) Klik tab Standar Types, klik dan pilih Column pada bagian Chart Types, dan tentukan pula tampilan grafik yang sesuai dengan keinnginan kita pada bagian Chart sub-type, misalnya kita pilih 3-D

4) Jika kita ingin mengetahui tampilan grafik, maka klik dan tahan pada tombol Press and Hold to View Sample

5) Klik Next>6) Lengkapi elemen grafik pada bagian Chart title untuk judul

grafik, category (X) untuk judul sumbu X, series (Y) untuk judul sumbu Y, value (Z) untuk judul sumbu Z.

7) Pada tab Exes berfungsi untuk menampilkan atau menyembunyikan keterangan pada sumbu X, Y, dan Z.

8) Pada tab Gridlines berfungsi untuk menampilkan atau menyembunyikan garis skala pembantu pada sumbu X, Y, dan Z.

9) Pada tab Legend berfungsi untuk menampilkan atau menghilangkan keterangan gambar grafik.

10) Pada tab Data labels berfungsi untuk menempatkan label data pada grafik.

11) Pada tab Data tables berfungsi untuk menampilkan atau menyembunyikan tabel data pada tampilan lembar kerja grafik.

12) Klik tombol Finish untuk mengakhiri pembuatan grafik.

b. Pewarnaan Grafik

Langkah yang digunakan untuk menentukan atau merubah elemen warna grafik adalah sebagai berikut:1) Klik Chart Area grafik2) Klik tombol mouse sebelah kanan, kemudian klik Format Plot

Area3) Klik Patterns, tentukan pola warna sesuai dengan keinginan4) Klik OK

c. Merubah Tampilan Grafik 3D

1) Klik Chart Area grafik2) Klik kanan mouse, kemudian klik 3-D View

[email protected] [email protected]

17

Page 18: Belajar Excel

3) Klik Rotation dab ketik 30 untuk merubah tampilan menjadi 30 derajat.

4) Klik tombol OK

6. Fungsi Gabungan

a. Fungsi ROUNDFungsi ini digunakan untuk membulatkan data angka ke digit tertentu sesuai kebutuhan.Model perumusan:

Gambar 15. Contoh Fungsi Round

b. Fungsi SUM dan SUMIFLangkah-langkah dalam penggunaan fungsi SUM adalah1) Tempatkan pointer pada alamat sel B102) Ketik =SUM(3) Klik sel B34) Tekan tombol Shift bersamaan dengan Klik sel B95) Ketik )6) Tekan Enter, sehingga pada alamat sel D10 terlihat hasil dari

penjumlahan 627, lihat gambar 16.

[email protected] [email protected]

18

=ROUND(sel;digit pembulatan)

Page 19: Belajar Excel

Gambar 16. Contoh Fungsi SUM

Penggunaan fungsi SUMIF digunakan untuk menjumlahkan seluruh data angka (numerik) dari sederetan angka sesuai dengan kriteria tertentu. Langkah-langkahnya adalah sebagai berikut:

1) Tempatkan pointer pada alamat sel B102) Ketik =SUM(3) Klik sel B34) Tekan tombol Shift bersamaan dengan Klik sel B95) Ketik )6) Tekan Enter, sehingga pada alamat sel D10 terlihat hasil dari

penjumlahan 627, lihat gambar 16.

APLIKASI KOMPUTER BERBASIS KEUANGAN

1. Pengantar MS Excela. Menjalankan dan Pengenalan Komponen MS Excelb. Mengaktifkan Buku Kerja

[email protected] [email protected]

19

Page 20: Belajar Excel

c. Memilih dan Memberi nama Ranged. Menambah Komentar

2. Entri Data dan Sistem Securitya. Entri Data pada Lembar Kerja secara Berurutanb. Entri Data dengan Referensi Sel dan Sheet Lain c. Entri Data dengan Persamaan Matematikad. Sistem Proteksi Lembar Kerja (Sheet)e. Menyimpan file dengan Sistem Security (Password)

3. Format Dataa. Format Perataan Teks pada Rangeb. Format Angka c. Format Bingkai dan Pewarnaan Sel/Ranged. Format Painter

4. Proses Databasea. Mengurutkan Datab. Entri Data dengan Formc. Melacak Data dengan Data Filterd. Validasi Data

5. Tampilan Data dalam Bentuk Grafika. Membuat Garfikb. Pewarnaan Grafikc. Merubah Jenis Grafikd. Menentukan Elemen Grafike. Merubah Tampilan Grafik 3Df. Menyunting Elemen dan Shape Grafikg. Menyunting Teks Judul

6. Fungsi Gabungana. Fungsi ROUNDb. Fungsi AVERAGE, dan SUMIFc. Fungsi VLOOKUP, HLOOKUP, DAN IF

7. Penanganan Errora. Error ######b. Error #VALUE!c. Error #NUM!d. Error #NAME?

[email protected] [email protected]

20

Page 21: Belajar Excel

e. Error #REF!f. Error #NULLg. Error #DIV/0!

8. Aplikasi

A. PENGOPERASIAN SOFTWARE

[email protected] [email protected]

21

Page 22: Belajar Excel

Hal-hal yang perlu diperhatikan dalam pengoperasian software ini adalah : “Seandainya ada transaksi yang terlewat tidak masuk ke dalam Input Data Keuangan ini, sebaiknya jangan menyisipkan cell dengan cara meng-insert cell dan atau move data di tengah-tengah transaksi yang sudah dimasukkan, karena data yang sudah dimasukkan ke dalam table secara otomatis diproses oleh rumus sehingga akan mengganggu formula yang telah ada yang mengakibatkan tidak akan terbaca di output atau di laporan akhir, sebaiknya sorot dari cell dimana letak transaksi yang akan dimasukkan lalu Copy & Paste dibawah cell yang akan dikosongkan untuk memasukan transaksi yang terlewat”.

Dalam pengisian data pada K3 dan K4 langkah-langkahnya adalah sebagai berikut :

1. Untuk membuat laporan K3, klik sheet K3 dan pada cell A5 untuk memasukan asal dana, dalam hal ini BOS; cell A6 untuk memasukan periode yang diinginkan (contohnya Januari 2007); seperti pada contoh di bawah ini.

[email protected] [email protected]

22

Contoh data yang telah diisi

Page 23: Belajar Excel

Setelah melakukan langkah-langkah tersebut di atas, maka setelah di enter akan muncul data yang diinginkan, seperti gambar di bawah ini :

2. Untuk membuat laporan K4, kita tidak perlu lagi memasukan data apa-apa seperti asal dana dan periode yang diinginkan karena apabila di format K3 kita sudah memasukan asal dana

[email protected] [email protected]

23

Masukan Periode yang diinginkan disini (cell A6)

Masukan jenis Asal Dana disini

(cell A5)

Page 24: Belajar Excel

dan periode maka di sheet K4 ini telah otomatis keluar data sesuai dengan perintah yang ada di sheet K3, dan hasilnya seperti contoh pada gambar berikut :

B. LANGKAH-LANGKAH PEMBUATAN SOFTWARE

Untuk mengetahui dan memahami langkah-langkah pembuatan software Laporan dan format BOS, dibawah ini akan dijelaskan secara detail.

1. Buka program Microsoft excel; setelah dibuka maka akan kita lihat biasanya 3 sheet, yaitu sheet1, sheet2, dan sheet3.Untuk memudahkan kerja kita, tiap sheet kita beri nama :Sheet1 = DataSheet2 = TrackSheet3 = K3Langkah-langkahnya adalah :1. Klik kanan mouse di sheet1 – rename – tik “Data”2. Klik kanan mouse di sheet2 – rename – tik “Track”, dan3. Klik kanan mouse di sheet3 – rename – tik “K3”

[email protected] [email protected]

24

Page 25: Belajar Excel

2. Di sheet1 kita buat format untuk Data Input, seperti gambar berikut ini:

[email protected] [email protected]

25

Page 26: Belajar Excel

3. Di sheet2 kita buat format pengolah data dari sheet1 sehingga akan memudahkan pengerjaan selanjutnya di sheet3

4. Di sheet3 kita buat format K3, seperti gambar berikut :

[email protected] [email protected]

26

Page 27: Belajar Excel

Setelah ketiga sheet dibuat format masing-masing, maka kita mulai pengerjaan di sheet1/Data.

a. Masukan data pada format Data dengan transaksi keluar maupun masuk secara lengkap dari mulai nomor bukti pemasukan/pengeluaran, tanggal transaksi, asal dana terserbut diperoleh, dan jumlah uang yang diterima/dikeluarkan.

b. Setelah data secara lengkap dimasukkan, langkah selanjutnya adalah pembuatan Kode Nomorn Kode Transaksi; sorot cell A dan B lalu insert; cell A untuk Kode transaksi dan cell B untuk Nomor Transaksi

[email protected] [email protected]

27

Page 28: Belajar Excel

Setelah selesai berinama kolom A (Kode 1) dan kolom B (Kode 2)

c. Sebelum penomoran dimulai, kita harus tentukan dulu asal dana dan periode tanggal di sheet K3, karena penomoran di sheet Data akan berhubungan langsung dengan K3.

d. Pembuatan Kode Nomor dan Kode TransaksiKode Nomor (Sheet Data)

Rumus yang digunakan : IF, DATE, dan MAXA- Klik cursor di cell B4, apabila periode tanggal yang ada pada

sheet Data cell D4 sama dengan periode tanggal yang ada pada sheet K3 maka beri angka 1, dan kalau tidak sama kosongkan saja.

[email protected] [email protected]

28

Page 29: Belajar Excel

=IF(DATE(YEAR(D4);MONTH(D4);1)=DATE(YEAR('K3'!$A$6);MONTH('K3'!$A$6);1);1;"")

Rumus 1

Sampai disini penomoran khusus untuk nomor 1 sudah selesai tapi harus diantisipasi untuk menghindari kemungkinan-kemungkinan lain yang tidak bisa dibaca oleh excel; rumus tadi hanya menyatakan jika sama (=), untuk antisipasinya masukan rumus jika tidak sama (<>): Rumus 1 + Rumus antisipasiJika periode tanggal (D4) tidak sama dengan kosong (“”), maka jalan rumus 1 tadi, kalau sama dengan kosong maka kosongkan saja (“”)

[email protected] [email protected]

29

Rumus antisipasi

Page 30: Belajar Excel

Untuk penomoran selanjutnya copykan rumus pertama ke cell dibawahnya, dan tambahkan rumus MAXA, rumusnya sebagai berikut :

Apabila bila rumus ini berhasil, maka untuk selanjutnya tinggal di copy ke cell dibawahnya sesuai dengan kebutuhan untuk transaksi; hasilnya akan seperti gambar di bawah ini :

Kode TransaksiRumus yang digunakan : UPPER, IF, DATE, TEXTKode transaksi fungsinya untuk menyortir jenis asal dana dan periode yang ditampilkan atau dibutuhkan pada laporan akhir/output; langkah-laangkahnya sebagai berikut :

- Copy rumus di cell B4 ke cell A2, karena pada intinya sama hanya mengurutkan nomor transaksi, tapi di cell ini lebih spesifik lagi yaitu untuk menentukan jenis Asal Dana dan Periode yang diinginkan di sheet K3.

- Setelah selesai di copy ke cell A4 maka akan terlihat tampilan seperti ini karena ada beberapa rumus yang

[email protected] [email protected]

30

Page 31: Belajar Excel

tidak di kunci sehingga apabila dipindah/dicopy akan berubah karena rumus tersebut menyesuaikan dengan letak cell barunya. Antisipasinya cursor simpan di cell A4 klik status bar lalu ganti seperti pada cell asal :

Rumus di cell B4/cell asal

=IF(D4<>"";IF(DATE(YEAR(D4);MONTH(D4);1)=DATE(YEAR('K3'!$A$6);MONTH('K3'!$A$6);1);1;"");"")

Setelah dipindah ke cell A4, berubah jadi :

=IF(C4<>"";IF(DATE(YEAR(C4);MONTH(C4);1)=DATE (YEAR('K3'!$A$6);MONTH('K3'!$A$6);1);1;"");"")

Tampilan di cell A4 dan B4 akan sama apabila telah diganti C4-nya menjadi D4.

- Setelah angka 1 masuk ke cell A4, selanjutnya adalah mengabungkan asal dana dengan nomor tersebut caranya adalah dengan menggunakan rumus UPPER dan TEXT.

=IF(D4<>"";IF(DATE(YEAR(D4);MONTH(D4);1)=DATE(YEAR('K3'!$A$6);MONTH('K3'!$A$6);1);1;"");"")

Maka rumus barunya adalah :

=IF(D4<>"";UPPER(E4)&"-"&IF(DATE(YEAR(D4); MONTH(D4);1)=DATE(YEAR('K3'!$A$6);MONTH ('K3'!$A$6);1);TEXT(B4;"000");"");"")

[email protected] [email protected]

31

Sisipkan rumus UPPER dari asal

dana

Ganti angka 1 ini dengan rumus TEXT dari hasil

formula di cell B4

Page 32: Belajar Excel

Tampilannya sebagai berikut :

Setelah selesai pengkodean di cell A4, maka kebawahnya tinggal copy paste dari cell A4.

Selesai sudah pekerjaan di sheet Data dalam pembuat Kode Nomor dan Kode transaksi.

e. Pengerjaan di sheet2/sheet trackSheet2/sheet track adalah sheet yang dipakai untuk membuat rumus-rumus yang mengolah data dari sheet1; sheet ini akan disembunyikan (hide) nantinya karena hanya sebagai sheet

[email protected] [email protected]

32

Page 33: Belajar Excel

pembantu untuk mempermudah pengerjaan dan tampilan di sheet laporan.Format sheet pembantu ini bebas bentuknya, hanya sebagai contoh seperti gambar di atas tadi.

Di sheet Data kita telah menentukan nomor urut dan kode transaksi baik transaksi masuk maupun keluar di urutkan dalam satu array; di sheet pembantu/track ini nomor tersebut dipisah antara nomor transaksi keluar dan transaksi masuk karena mengikuti format laporan K3.Sebelum membagi nomor urut dan kode dari sheet Data, pertama-tama kita harus mentransfer dulu input data dari sheet Data, untuk mempermudah pengerjaan, kita harus membuat cell pembantu dulu supaya tidak terlalu memusingkan pekerjaan kita karena selalu bolak-balik ke sheet yang lain.Pertama kita transfer dulu tampilan asal dana dan periode dari sheet K3.

Langkah kedua yaitu pengkodean dengan memasukan tampilan di cell A7 (BOS) digabungkan dengan format TEXT “000” serta

[email protected] [email protected]

33

Page 34: Belajar Excel

mengambil data minimal (MIN) dari sheet data yang ada pada cell B4 s.d. B21 (Kode 2)

Rumusnya

=A1&"-"&TEXT(MIN(Data!B4:B21);"000")

Untuk nomor selanjutnya, harus mengambil nilai terbesar dari transaksi di sheet data dulu, caranya :

Maka baru kita dapat melakukan penomoran selanjutnya, caranya :

[email protected] [email protected]

34

Page 35: Belajar Excel

Setelah selesai penomoran yang ke 2, maka untuk selanjutnya tinggal meng-copy paste ke cell berikutnya sesuai dengan kebutuhan.

Langkah selanjutnya adalah pengambilan Nomor bukti dari sheet data, caranya :Dalam pengisian data disini yang menjadi patokan adalah cell A7 (Kode) BOS-001 karena kolom yang terletak dalam table array paling kiri (data di sheet Data).

[email protected] [email protected]

35

Page 36: Belajar Excel

Pengambilan nomor bukti yang ke 1 telah selesai, untuk nomor bukti selanjutnya hanya tinggal meng-copy paste dari cell 1 (B7) ke cell dibawahnya sebanyak yang kita butuhkan.

[email protected] [email protected]

36

Page 37: Belajar Excel

Mengambilan nomor bukti telah selesai, untuk pengambilan periode/tanggal transaksi, uraian/keterangan dari dan untuk apa dana tersebut, pemasukan dan pengeluaran dana pada prinsipnya sama dengan langkah-langkah yang dilakukan pada pengambilan nomor bukti seperti contoh di atas, hanya tinggal mengganti nomor kolom disesuaikan dengan nomor kolom yang dibutuhkan dan disesuaikan dengan kolom yang ada pada sheet Data.Untuk mempermudah pengerjaan kolom-kolom tersebut di atas, kita bisa menyorot/meng-copy paste dari cell C7, selanjutnya kita ganti nomor kolom yang sesuai dengan yang dibutuhkan.

Setelah selesai, tampilan nilai dan teks belum terlihat ada perbedaan dengan sebelum di copy paste rumus dari C7, karena harus ada yang diubah lookup_value dan nomor kolomnya.

Tampilan rumus di cell D7

[email protected] [email protected]

37

Patokan kita adalah lookup_value-nya harus sama yaitu pada kolom

paling kiri dalam table array (A7)

ingaa.. ingaa..

=IF(B7<>"";IF(ISERROR(VLOOKUP(B7;Data!$A$4:$H$21;4;0));"";VLOOKUP(B7;Data!$A$4:$H$21;4;0));"")

Page 38: Belajar Excel

B7 harus diganti menjadi A7 karena merupakan lookup_value, dan angka 4 ganti dengan angka 6, karena nomor kolom yang dicari adalah kolom uraian yang letaknya di kolom ke 6.

Tampilan rumus di cell E7

C7 harus diganti menjadi A7 karena merupakan lookup_value, dan angka 4 ganti dengan angka 7, karena nomor kolom yang dicari adalah kolom pemasukan yang letaknya di kolom ke 7.

Tampilan rumus di cell F7

D7 harus diganti menjadi A7 karena merupakan lookup_value, dan angka 4 ganti dengan angka 8, karena nomor kolom yang dicari adalah kolom pengeluaran yang letaknya di kolom ke 8.Apabila pengeditan telah selesai tampilan dengan sendirinya akan berubah, seperti gambar di bawah ini :

Langkah selanjutnya tinggal sorot cell D7 sasmpai F7 dan copy paste ke cell dibawahnya sesuai dengan kebutuhan.

[email protected] [email protected]

38

=IF(C7<>"";IF(ISERROR(VLOOKUP(C7;Data!$A$4:$H$21;4;0));"";VLOOKUP(C7;Data!$A$4:$H$21;4;0));"")

=IF(D7<>"";IF(ISERROR(VLOOKUP(D7;Data!$A$4:$H$21;4;0)) ;"";VLOOKUP(D7;Data!$A$4:$H$21;4;0));"")

Page 39: Belajar Excel

Pembuatan Sheet pembantu/track sampai disini sudah selesai, tapi ada satu lagi pekerjaan yang biasanya digunakan dalam laporan tidak terkecuali di dalam format-format BOS, yaitu saldo periode sebelumnya dan saldo akhir periode bulan berjalan.Untuk saldo akhir periode, bisa dibuat pada lembar laporan langsung, tetapi untuk saldo periode sebelumnya lebih baik di sheet pembantu supaya di lembar laporan tidak terlalu bertumpuk rumus yang akan mengganggu tampilan di layer monitor apabila cell di sorot.Langkah pertama kita harus mengambil dulu criteria atau syarat dari isi sel range yang harus dijumlahkan.

Klik cell A4, ambil tampilan di cell A1 (BOS) dan tambahkan teks -0 (&”-0”); “-0” ini bukan berupa nilai tetapi teks karena diapit dengan tanpa petik (“”). Maka hasilnya adalah BOS-0, nantinya rumus akan membaca atau menjumlahkan baik pemasukan maupun pengeluaran yang sejajar dengan teks BOS-0 saja.

Setelah diketahui syaratnya maka klik cell E4 untuk mengetahui seluruh pemasukan pada periode sebelumnya, dan klik cell F4 untuk mengetahui seluruh pengeluaran pada periode sebelumnya, keduanya menggunakan rumus SUMIF, Caranya :tik =SUMIF masuk ke range yang akan dievaluasi data-datanya (sheet 1/sheet data) tentukan kriterianya (A4) masuk ke sel-sel yang isinya akan dijumlahkan (sheet 1/sheet data).

[email protected] [email protected]

39

Page 40: Belajar Excel

Setelah jumlah pemasukan dan pengeluaran diketahui, maka untuk mengetahui saldo sebelumnya tinggal klik cell G4 lalu cell E4-F4 yang terlebih dahulu ketik tanda sama dengan (=).Saldo periode lalu di laporan ini belum terlihat karena bulan laporan Januari 2007 dan bulan sebelumnya (Des 2006) tidak ada transaksi. Untuk mengetahui hasil rumus tersebut supaya lebih jelas kita coba ganti periode di K3 ke bulan Februari 2007, maka hasilnya:

Untuk melanjutkan ke lembar berikutnya yaitu sheet K3, karena di format BOS K3 antara kolom pemasukan dan kolom pengeluaran terpisah, maka terlebih dahulu harus membuat rumus pemisahan transaksi masuk dan keluar, caranya insert 2 kolom paling kiri (kolom A dan B) di sheet pembantu/track yang kegunaannya kolom A untuk kolom nomor transaksi masuk, dan kolom B untuk kolom nomor transaksi keluar.Caranya sorot kolom A dan B, mouse klik kanan dan insert

Setelah selesai membuat kolom untuk penyimpan nomor, langkah selanjutnya adalah memisahkan nomor masuk dan keluar, caranyas adalah :

[email protected] [email protected]

40

Page 41: Belajar Excel

Nomor Masuk :Apabila kolom KODE tidak sama dengan kosong, kolom TANGGAL tidak sama dengan kosong, kolom MASUK tidak sama dengan kosong, kolom MASUK tidak sama dengan nol (0), maka munculkan angka 1, kalau kosong, kosongkan saja.

Untuk nomor selanjutnya telah dijelaskan pada penomoran di sheet Data.

Nomor Keluar :Caranya sama hanuya yang disorot bukan transaksi masuk tetapi transaksi keluar.Selanjutnya kita harus menentukan dan mengetahui nilai terbesar dari transaksi mauk dan keluar, caranya menggunakan rumus MAXA.

[email protected] [email protected]

41

Page 42: Belajar Excel

Apabila menentukan nilai terbesar dari transaksi sudah ditemukan, langkah selanjutnya ke sheet K3.

SHEET PELAPORAN BOS (K3)Pertama-tama yang harus dilakukan di sheet K3 ini adalah menyimpan saldo periode sebelumnya.Caranya :

Setelah saldo periode sebelumnya ada, langkah selanjutnya adalah mengambil data periode sekarang dari Track. Sebelum mengambil data dari track, kita harus menyimpan dulu jumlah maksimal transaksi periode sekarang yang nantinya akan di hide supaya tidak tampil dilayar.Caranya :Sorot cell A insert dan akan muncul di kolom paling kiri kolom kosong yang akan digunakan untuk kolom nomor transaksi.

[email protected] [email protected]

42

Page 43: Belajar Excel

Apabila kolom transaksi telah tersedia, maka rumus pertama untuk cell A11 adalah:Apabila nilai maksimal yang ada di sheet Bantu/track (H1) nilainya lebih besar dari 0 (nol) maka beri nilai 1, apabila tidak kosongkan saja.

Nomor berikutnya adalah apabila nilai di atas ditambah 1 (+1) lebih kecil/sama dengan (<=) nilai maksimal yang ada di sheet Bantu/track (H1), maka nilai di atas ditambah 1, kalau tidak sama (<>) kosongkan saja.

Untuk antisipasi beri lagi rumus:

Selanjutnya copy paste rumus di cell ke 2 ke cell dibawahnya sesuai dengan kebutuhan.

[email protected] [email protected]

43

=IF(Track!$H$1>0;1;"")

IF(A11+1<=Track!$H$1;A11+1;"");"")

=IF(A11<>"";IF(A11+1<=Track!$H$1;A11+1;"");"")

Page 44: Belajar Excel

Supaya tidak mengganggu tampilan format K3 kolom A di hide, sehingga yang tampil di layer mulai dari kolom B.Tahap pertama pada sheet laporan telah selesai, selanjutnya adalah mengambil data yang ada di track ke sheet K3 dari mulai Nomor Bukti keluar masuk, periode tanggal keluar masuk, uraian keluar masuk, jumlah dana keluar masuk.Pada prinsipnya sama dengan yang dilakukan pengambilan data di sheet Bantu/track, hanya bedanya kalau Track mengambil dari sheet Data, sedangkan sheet K3 mengambil dari sheet Track.

Rumus yang digunakan sama yaitu :VLOOKUP ditambah rumus antisipasi yaitu ISERROR dan IF, maka hasilnya :

SHEET PELAPORAN BOS (K4)Untuk membuat Laporan K4, pada prinsipnya sama dengan langkah-langkah yang dilakukan pada pembuatan lapaoran K3, hanya ada sedikit perbedaan dalam pengambilan Jenis asal Dana, Periode, dan Saldo.

Jenis asal dana rumusnya hanya sama dengan (=) klik Track klik asal dana yang ada di sheet tersebut ENTER; untuk Periode tanggal sama langkahnya seperti pengambilan asal dana.

[email protected] [email protected]

44

Page 45: Belajar Excel

Saldo sampai dengan akhir bulan lalu diambil dari Track, caranya :Sorot cell F4 di sheet K4 yang akan dijadikan cell penyimpanan saldo, sama dengan (=) klik Track klik cell jumlah pemasukan (H4) lalu kurangkan (-) ke cell jumlah pengeluaran (I4) – ENTER

Sebelum melangkah ke nomor urut transaksi, perlu diingat bahwa nomor urut ini harus diambil dari Track, sedangkan untuk laporan K4 di Track belum mempunyai kode nomor, maka langkah selanjutnya kembali ke sheet Track untuk membuat nomor transaksi di lembar K4.Caranya :Masuk ke Sheet Track.Sorot cell A dan insert, lalu beri nama No K4.Masukan rumus untuk penomoran ke 1 dan ke 2, untuk penomoran ke 3 dst, lakukan copy paste dari penomoran yang ke 2.

[email protected] [email protected]

45

Page 46: Belajar Excel

Apabila telah selesai penomoran, langkah selanjutnya ambil nilai maksimal dari nomor transaksi tersebut yang akan dijadikan patokan dalam pengisian data di laporan K4 nanti caranya dengan menggunakan rumus MAXA.

Tugas penomoran untuk laporan K4 selesai, kemudian kita kembali lagi ke Sheet K4 untuk melakukan penomoran di sheet tersebut.

[email protected] [email protected]

46

Page 47: Belajar Excel

Untuk penomoran di cell ke 3 dst, lakukan copy paste dari penomoran yang

ke 2.Langkah selanjutnya adalah memasukan data ke cell tanggal transaksi (B16), cell uraian kegiatan (C16), cell masuk dana (D16), dan cell keluar dana (E16).

Caranya (lihat langkah-langkah memasukan data ke laporan K3)

Setelah selesai memasukan data ke tiap-tiap cell dan copy paste ke cell dibawahnya.

[email protected] [email protected]

47

Page 48: Belajar Excel

Khusus untuk Cell F17 dan ke bawahnya, ada tambahan rumus antisipasi yaitu :Rumus 1 : Rumus 2 :

Secara utuh hasil laporan K4 adalah sebagai berikut :

[email protected] [email protected]

48

=F14+D16-E16 =IF(OR(D17<>””;E17<>””);F14

+D16-E16;””)