triana nasir

FORMULA, GRAFIK, DAN FORMAT DATA Sma negeri 2 prabumulih 6/11/2012 TRIANA NASIR

A.      MEMASUKKAN FORMULA

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

Secara umum ada 9 kategori fungsi yaitu financial,tanggal dan waktu, matematika, statistika, lookup dan diference, database, logika, text, dan informasi

ü  FUNGSI FINANCIAL.

Berkaitan dengan akutansi, ekonomi, dan keuangan. Biasanya digunakan untuk mempermudah menghitung penyusutan barang. Terdiri dari 16 sub fungsi dengan kegunaan masing-masing.untuk dapat menampilkannya dilayar ikuti langkah-langkah berikut:

        1)aktifkan lembar kerja atau work sheet exel

        2)klik menu insert

        3)klik fungtion maka akan tampil

 

4)pilih financial,pada pilihan *or category*

5)pilih salah satu pada *select a fungtion*

6)klik OK

ü  FUNGSI MATEMATIKA

·         Fungsi SUM

 digunakan untuk menghitung jumlah satu data angka atau lebih yang diketikkan langsung dalam fungsi atau data angka yang terdapat dalam range. Berikut bentuk penulisan fungsi SUM:
=SUM(number1; [number2]; ...number1; [number2]; ... adalah data angka yang akan dihitung jumlahnya. Jumlah argumen yang dapat dimasukkan adalah 1 sampai 255.

·         Fungsi sudut

Digunakan untuk mencari nilai nilai sudut

=SIN(number) enter atau =COS(number)enter dan yang lainnya

          

·         Fungsi MOD

Untuk mencari sisa pembagian

=MOD(nilai;pembagi)enter

·         Fungsi SQRT

Digunakan untuk mencari ilai akar

=SQRT(nilai)enter

 

·         Fungsi INT

Digunakan untuk mengabaikan nilai desimal pada suatu bilangan

=INT(nilai) enter

 

ü  FUNGSI STATISTIK

·         Fungsi average

Fungsi AVERAGE digunakan untuk menghitung rata-rata satu data angka atau lebih yang diketikkan langsung dalam fungsi atau data angka yang terdapat dalam range. Berikut bentuk penulisan fungsi AVERAGE:

=AVERAGE(number1; [number2]; ...)

·         Fungsi max

Digunakan untuk menentukan nilai tertinggi yang terdapat dalam data

=max(number1; [number2]; ...)

·         Fungsi MIN

Digunakan unruk menentukan nilai terendah yang terdapat dalam data

=max(number1; [number2]; ...)

·         Fungsi COUNTA

Fungsi COUNTA digunakan untuk menghitung jumlah sel berisi data. Sel yang berisi data angka nol (0) disertakan dalam perhitungan, sedangkan sel kosong tidak disertakan dalam perhitungan. Berikut bentuk penulisan fungsi COUNTA:

=COUNTA(value1; [value2]; ...)

·         Fungsi COUNTIF
Fungsi COUNTIF digunakan untuk menghitung jumlah sel dalam range yang sesuai dengan kriteria yang ditentukan. Berikut bentuk penulisan fungsi COUNTIF:
=COUNTIF(range; criteria)

range adalah range yang akan dihitung jumlah sel yang sesuai dengan kriteria yang ditentukan.  criteria adalah kriteria yang digunakan untuk menghitung jumlah sel dalam range. Kriteria dapat berupa teks, angka atau nilai logika. Anda dapat menggunakan operator dengan cara mengapit kriteria dengan tanda petik, misalnya ”>100

A.Menghitung selisih jam

Formulanya   :=C2-B2

Rumus diatas akan berhasil tanpa error apabila waktu selesai lebih besar daripda waktu mulai,namun jika waktu selesai lebih kecil akan menghasilkan error.

Formula untuk menghilangkan error   :

=C2-B2+IF(B2>C2;1)

B.Menghitung Ansuran

Untuk menghitung ansuran perbulan,digunakan formula  :

=ABS(PMT(F5/12;F6;F4)

Sedangkan ubtuk mengisi kolom pokok pinjaman digunakan formula 

=ABS(PPMT($F$5/12;A11;$F$6;$F$4))

Untuk mengisi kolom ansuran digunakan formula :

=ABS(IPMT($F$5/12;A11;$F$6;$F$4))

Untuk mengisi kolom besarnya ansuran digunakan formula  :

=ABS(D12+E12)

Untuk mengisi sisa pinjaman pertama digunakan formula  :

=F10-D11

Formula diatas mengurangi nilai sisa pinjaman sebelumnya dengan sisa pijaman ansuran.

C.Membuat Rangking Otomatis

Untuk mengisi kolom rank :

1.nilai bahasa inggris                                     3.nilai jumlah nilai

   =RANK(D3;inggris;0)                                       =RANK(F3;jml_nilai;0)

2.nilai tes potensi akademik                        4.nilai rata-rata

   =RANK(E3;tpa;0)                                             =RANK(G3;rata_rata;0)

Bentuk umum formula RANK :

=RANK(nomor;range_cell;order)

D.Referensi cell dengan VLOOKUP

1.Mengisi merek mobil

Formulanya  :=VLOOKKUP(LEFT(C6;1);merek;2;FALSE

2.Mengisi Tipe

Formulanya  :=VLOOKKUP(MID(C6;3;3);tipe;2;FALSE)

3.Mengisi tahun keluar

Formulanya   =IF(MID(C6;7;2)=’’073;’’20073;IF(MID(C6;7;2)=’’083;’’20083;IF(MID

(C6;7;2)=’’093;’20093;’’20103)))

Formula diatas menggabungkan fungsi IF dan MID dimana fungsi MID hanya untuk mengambil dua karakter dari kode mobil.

4.Mengisi harga dasar

Formulanya:

=VLOOKUP(CONTATENATE(LEFT(C6;1);MID(C6;3;3));harga;2;FALSE)

5.Mengisi target harga jual

Formulanya  =G6+VLOOKUP(LEFT9C6;1);merek;3;FALSE)

Target harga jual merupakan laba yang diinginkan dikali dengan harga dasar/pokok,besarnya laba yang diinginkan ada pada tabel merek,untuk itu diambil setiap laba masing-masing merek kemudian dikali dengan harga dasar dan hasilnya ditambahkan dengan harga dasar.

6.Mengisi under/upper Estimate

Formulanya...=((I6-H6)*100

Under/upper estimate merupakan selisih kurang atau selisih lebih antara harga jual yang sesungguhnya dengan targer harga jual.jika nilainya minus maka dibawah target dan jika plus sebaliknya.

7.Mengisi Grafik

Formulanya  :=IF(J12<0;REPT(‘’n’’;-ROUND(J12*10;0));REPT(‘’n’’ROUND(J12*10;0)))

Formula diatas mengecek jika nilainya minus (<0) maka membuat huruf ‘’n’’diulang-ulang dengan fungsi REFT sebanyak kolom J (dibulatkan dengan ROUND) dikali 10, namun sebelum fungsi REFT diberi tanda minus (-), dan jika positif (>0) maka sama dengan penjelasan diatas namun fungsi REFT-nya tanpa tanda minus didepannya.

8.Mengubah Menjadi Grafik

Untuk mengubah warna karakter dikolom K dengan warna merah jika kolom J nilainya negatif dan warnanya kuning jika nilainya positif,maka langkah-langkahnya:

a.ubah jenis huruf menjadi wingding dan warna menjadi kuning

b.ubah nilai negatif menjadi berwarna merah,tempatkan pada cell K6 klik Conditional Formating pada ribbon home,kemudian klik manage rule.

c.selanjutnya akan tampil gambar dibawah,untuk membuat rule baru klik new

d.setelah keluarpilihan rule,anda pilih rule Use Formula to Determine Wich Cell to format,selanjutnya pada pada kotak formula isi=J6<(artinya jika kolom J6 minus),maka klik tombol format.

e.pada kotak dialog format cell ubah warnanya menjadi merah,klik Ok dan tutup semua jendela.

f.copy formula di K6 kebaris berikutnya.

E.Menghitung gaji karyawan

1.Pengisian kolom jumlah jam kerja

Formulanya  :SUM(C8:H8)

Formula diatas digunakan untuk menjumlahkan jam kerja dari kolom hari senin sampai hari sabtu,selanjutnya copy paste pada baris berikutnya.

2.Pengisian kolom jumlah jam lembur

Formulanya  :=IF(18>(Jam_normal*6);18-(jam_normal*6);0)

Formula diatas untuk melakukan pengecekkandengan fungsi IF,maka Jam kerja normal x6 hari.Setelah terisi lakukan format cell agar dibelakang angka hasil perhitungan terdapat satuan jam,caranya sebagai berikut:

Klik menu format ->cell,pilih tab general dan bagian Caption pilih custom dan pada bagian tipe ketik ## ‘’jam’’.

3.Pengisian Upah Pokok

Untuk mengisi upah pokok yaiti perkalian antara kolom jumlah jam kerja dengan upah per jam,maka formulanya :

=18*upah _per_jam

Untuk menambahkan simbol rupiah didepan angka,klik menu format cell,pada tab general pilih accounting pada bagian category,jika tidak ada lakukan perubahan pada seting regional.

4.Pengisian Upah Lembur

Jika karyawan memiliki jam lembur,kalikan jumlah jam lembur dengan upah lembur per jam.

Formulanya  :=IF(J8>0;J8*lembur_perJam;0)

5.P uang Makan

Formulanya  :=(COUNTIF(C8:H8;>03)*makan_per_hari)

6.Pengisian Uang Insentif

Hanya diberikan pada karyawan yang bekerja penuh dalam seminggu (6 hari).Formulanya :

=IF(COUNTIF (C8:H8;’’>03)=6;insentif_mingguan;0

7.Pengisian Gaji kotor

Gaji kotor merupakan penjumlahan dari upah pokok,upah lembur,uang makan,dan insentif maka formulanya :

=SUM(K8:N8)

8.Pengisian Pajak

Pajak ditentukan dengan tarif 10% dari gaji kotor maka formulanya adalah: =Pajak*O8

9.Pengisian gaji barsih

Gaji kotor diperoleh dari pengurangan antara gaji kotor dengan pajak,maka formulanya : =O8-P8

10.Menghilangkan ‘’0 jam’’pada kolom jumlah jam lembur

Terdapat karyawan yang memiliki 0 jam,untuk melakukannya kita akan memberikan CONDITIONAL FORMATING,untuk itu blok kolom jumlah jam lembur,pilih menu format,conditional formating.Pilih cell value is,pada drop down kedua pilih less than,dan pada kotak isian ketiga isi dengan angka 1,kemudian klik tombol format.Pada pilihan klik color pilih warna yang sesuai dengan warna latar belakang kolom jumlah jam lembur,kemudian klik tombol ok 2(dua) kali sampai kedua kotak dialog hilang.

11.Menghilangkan ‘’Rp 0’’pada kolomupah lembur

Untuk menghilangkan Rp 0 pada kolomupah lembur,blok kolom upah lembur dan lakukan conditional formating seperti pada langkah 10.

12.Mengisi Baris Total,Rata-rata,Terbesar dan Terkecil

Untuk mengisi baris total,rata-rata terkecil dan terbesargunakan fungsi SUM,AVERAGE,MIN dan MAX.

1.       Fungsi yang digunakan untuk menghitung jumlah data angka yaitu...

       a.sum.      c.min.           e.days

       b.max.     d.average

2.       Untuk mendeteksi apakah suatu sel berisi nilai teks lower case, digunakan fungsi...

        a.istext.      c.lower.        e.proper

        b.exact.       d.upper

3.       untuk membandingkan dua text string digunakan...

a.        =ISTEXT

b.       =DISTEXT

c.        =EXACT

d.       =DISEXACT

e.       =NOEXACT

4.       (logical1;logical2) merupakan rumus dari...

a.       and dan exact

b.       and dan lower

c.        and dan OR

d.       and dan NA

e.       and dan average

5.       Pembuatan nomor urut secara otomatis disebut...

1. Autofilter
2. Autofiel
3. Automizor
4. Numbering
5. Automatic

6.       Criteria dapat berupa...

1. gambar, musik, teks
2. teks, angka, nilai logika
3. angka, musik, nilai logika
4. gambar, nilai logika, teks
5. teks, angka, musik

7.       Fungsi statistik yang berfungsi untuk menentukan suatu kondisi bernilai benar atau salah adalah....

a.       IF                                            d. VLOOKUP

b.      LEFT                                       e. HLOOKUP

c.       RIGHT

8.       Fungsi pembacaan tabel secara vertikal disebut....

a.       VLOOKUP                            d.  HLOKUP

b.      VLOKUP                               e.  FIND

c.       HLOOKUP

9.       Ikon yang digunakan untuk membuat grafik di lembar kerja adalah....

a.       ikon object         d. ikon picture

b.      ikon graphics      e. ikon chart wizard

I    c.    ikon drawing

10.   Ctrl+P merupakan fungsi yang dapat digunakan untuk...

       a.menyimpan dokumen

       b.mencetak dokumen

       c.membatalkan perintah

       d.membuka dokumen

       e.membatalkan perintah

11.   Value adalah...

1. Gabungan sel
2. Gabungan range
3. Sel atau range yang akan dihitung
4. Gabungan range
5. Teks,angka music

12.   Untuk memberi catatan kaki pada halaman, digunakan pilihan...

a.header and footer.   c.margins.     e.repeat

b.page set up.              d.sheet

13.   untuk membuat nama rang gunakan...

a.        devisi range

b.      Range

c.        cell order

d.       devine range

e.       fine range

14.   Yang digunakan untuk menghasilkan #NA adalah fungsi...

1. Istext
2. Na
3. Lower
4. And
5. Counta

15.   =EXACT(text 1; text 2) termasuk rumus dari fungsi...

a.       EXACT

b.      OR

c.       LOWER

d.      NA

e.      AND

Variasi Teks, Tabel, Grafik, Gambar, dan Diagram Dalam Excel 2007

1. Membuat Diagram dan Grafik

Langkah-langkah dalam membuat Diagram dan Grafik adalah sebagai berikut :

1.       Blok data yang ingin ditampilkan dalam grafik

2.       Klik tombol Insert > klik tombol Column dibagian Chart

3.       Klik Desain untuk memilih layout tabelnya

2. Memilih Jenis Grafik

Langkah-langkahnya adalah sebagai berikut :

1.       Pilih range data, misalnya sel A1:B6

2.       Pada Insert tab, group Chart, klik Pie dan pilih bentuk.

Berikut adalah Langkah-langkah modifikasi pada Chart, yaitu:

a.       Mengganti Chart Styles

v  Klik design tab pada chart tools. Pada group chart stlyes klik tombol more dan pilih styles yang diinginkan.

a.       Atur chart layout

v  Pada chart tools design tab, group chart layouts klik tombol more dan pilih layout 6.

Maka akan tampil chart seperti ini

Selanjutnya kita akan mengatur format 3-D chart. Pada chart tools, Layout tab, group Background, klik 3-D Rotation

ü  atur 3-D Rotation (untuk mengatur background chart). Pilih Soft Round untuk Bevel (Top dan Bottom)

ü  Atur 3-D Rotation (untuk membuat permukaan cembung pada Chart)

Sekarang tampilan chart akan seperti ini

b.      Ubah Warna Chart

Pilih bagian chart yg ingin diubah warnanya dengan cara dobel klik. Pada Format tab, Group Shape Styles, klik tanda panah shape Fill dan pilih warna yang diinginkan.

c.       Atur Posisi Legend

Pada Layout tab, group Label, klik legend dan pilih Show Legend at Bottom.

Kemudian klik dan geser legend sedikit keatas

d.      Format Legend

Klik pada legend di chart. Pada Format tab, group Shape Styles, klik tanda More pada Shape Styles dan pilih Modarate Effect-Accent 5

e.      Ubah Warna Background pada Chart

Pada Format tab, group Shapee Styles, klik tanda panah Shapee Fill dan pilih warna.

Maka akan timbul tampilan chart yang sudah diformat

3. Menyertakan Data Tabel di Grafik

caranya : Klik Layout > data table > pilih sesuai keperluan. Kemudian klik layout > Data table > show data table With Legend Keys

4. Mengurutkan Data

1.       Blok data yang akan diurutkan

2.       Pilih Editing > Sort & Filter > Sort Smallest to Largest

3.       Sekarang data sudah Anda urutkan dari data yang terbesar hingga terkecil

Kalau and ingin mengurutkan data berdasarkan nama, maka yang harus dilakukan sama dengan mengurutkan Nilai. Hanya saja, yang anda ubah Bloknya adalah Kolom nama.

5. Menyisipkan Gambar dari Clip Art

Langkah-langkah- menyisipkan Clip Art, yaitu :

1.       Buka lembar kerja anda, kemudian klik menu Insert > Clip Art

2.       Kemudian muncul Taskpane Insert Clip Art

3.       Geser Scroolbar untuk menampilkan gambar yang dikendaki, kemudian klik salah satu gambar

4.       Gambar akan disisipkan pada posisi titik sisip

5.       Kemudian simpan hasil kerja anda

6. Menyisipkan Gambar dari File

Langkah-langkahnya adalah ;

1.        Klik menu Insert > Picture > Freom File

2.       Akan muncul kotak dialogInsert Picture.

3.       Klik salah satu gambar

4.       Gambar akan disisipkan pada posisi titip sisip

5.       Kemudian simpan hasil kerja anda

7. Memasukkan Gambar Latar belakang

Langkah-langkah memasukkan gambar latar belakang :

1.       Pilih Tab Page Layout

2.       Klik pada ikon Background

3.       Pilih salah satu foto atau gambar yang anda inginkan

4.       Tekan tombol Insert

5.        

Kalau ingin memasukkan oto kedalam worksheet dan apabila ingin menghilangkannya kembali, langkah-langkahnya, yaitu :

1.       Klik Tab Page Layout

2.       Kemudian, klik pada ikon Delete background

8. Mengecilkan Ukuran File Foto

Langkah-langkahnya, yaitu :

1.       Klik foto gambar atau yang sudah anda masukkan ke dalam worksheet

2.       Kemudian, klik tab Format

3.       Klik pada Opsi Compress Picture

4.       Sebelumnya, tekan tombol Options terlebih dahulu

5.       Kalau worksbook ini akan dikirim via-email, pilih opsi Email

6.       Tekan tombol OK sebanyak dua kali

9. Menyisipkan Gambar autoshapes

Langkah-langkahnya, yaitu :

1.       Klik menu Insert > Shapes

2.       Akan muncul Taskpane Autoshapes

3.       Geser Scroolbar untuk menampilkan gambar yang dikehendaki

4.       Klik salah satu gambar yang diinginkan

5.       Gambar akan disisipkan pada posisi titik sisip

6.       Simpan hasil kerja Anda

10. Membuat Shape Transparan

Langkah-langkahnya, yaitu :

1.       Buatlah Shape dan berilah warna solid (jangan warna yang gradasi)

2.       Tekan tombol kanan mouse pada Shape itu

3.       Pilihlah format shape

4.       Geser slide Transparency ke arah kanan biar semakin transparan

5.       Tekan tombol OK

Hasilnya, objek shape itu akan menjadi transparan dan anda bisa melihat data-data yang tadinya tertutup oleh objek tersebut.

11. Menulis Teks didalam Shape

Langkah-langkahnya, yaitu :

1.       Klik pada objek shape itu

2.       Tekan tombol kanan Mouse

3.       Pilihlah Edit Text

4.       Ketik Teks yang diinginkan

5.       Untuk menyelesaikannya, klik dibagian lain pada worksheet

12. Mengopi Shape Sekaligus Memindahakannya

Langkah-langkahnya, yaitu :

1.       Klik dan Drag objek shape itu namun dengan menggunakan tombol kanan Mouse

2.       Kalau sudah, lepaskan tombol kanan Mouse itu

3.       Pilihlah Copy Here

13. Menyisipkan WordArt

Langkah-langkahnya, yaitu :

1.       Klik Menu Imsert > WordArt

2.       Akan muncul Taskpanel WordArt

3.       Klik salah satu gambar

4.       Gambar akan disisipkan pada posisi titik sisip

5.       Simpan hasil kerja anda

14. Membuat Kotak Teks yang Artistik

Langkah-langkahnya, yaitu :

1.       Pilih tab Insert

2.       Kemudian, klik pada Opsi Text Box

3.       Klik dan Drag di dalam worksheet untuk membuat area text box terlebih dahulu

4.       Klik teks yang diinginkan didalam text box tersebut

5.       Klik pada text box tersebut dan pilih Tab Format

6.       Klik tombol More pada bagian Shape Styles

7.       Pilih salah satu desain text box yang sudah Anda buat tadi akan segera berubah

15. Menghapus Bagian Foto yang diPotong secara Permanen

Langkah-langkahnya, yaitu :

1.       Pastikan Foto tersebut sudah dipotong terlebih dahulu

2.       Klik Icon Compress Picture

3.       Pilihlah Opsi Options

4.       Aktifkan opsi Delete Cropped Areas of Pictures

5.       Tekan tombol OK

Saat itu pula, ketika worksheet ini sudah disimpan, maka bagian gambar atau foto akan hilang secara permanen.

16. Pengaturan Halaman

Langkah-langkahnya, yaitu :

Klik Page Layout > Margins > Costum Margins

        

1.        Mengatur posisi dan ukuran halaman

a.       Pastikan tab yg aktif adalah tab Page. Kemudian atur posisi halaman, pastikan kebagian Orientation

b.      Kalau Orientation yang dipilih Potrait, maka posisi halaman akan memanjang Vertikal dan Kalau Landscape yang dipilih, maka posisi halaman akan melebar(Harizontal).

c.       Bagian Paper Size. Paper Size adalah ukuran kertas yang akan dipakai. Ubahlah papersize menjadi yang anda inginkan. Setelah langkah diatas, berarti halaman bagian pertama telah diatur

2.       Mengatur batas tepi(Margin) halaman

a.        Pindahkan tab aktif ke tab margins

b.      Perhatikan batas-batas yang ada

1.       Top adalah batas atas halaman

2.       Bottom adalah batas bawah halaman

3.       Left adalah batas kiri halaman

4.       Right adalah batas kanan Halaman

3.       Mengatur Header dan Footer halaman

Header dan footer adalah catatan yang diletakkan pada bagian atas dan bawah halaman. Header dibagian atas sedangkan Footer dibagian bawah.

Cara untuk mengatur Header and footer, yaitu :

1.       Pindahkantab aktifke tab Header and Footer

2.       Pertama, kita akan membuat header. Kita bisa memilih daftar header yang sudah disediakan. Kalau anda ingin membuat header sendiri, klik saja tombol Custom Header, maka Anda bisa menuliskan header sesuai keinginan

3.       Untuk latihan ini, pilih Custom Header sehingga muncul Jendela baru. Isilah bagian Left section dengan nama anda, misalnya “Novi dj” sedang Right section dengan “soal-Soal latihan”

4.       Kalau sudah selesai, Klik OK

Untuk Footer, pilih salah satu model footer yang telah disediakan