CARA MERANGKING / PERINGKAT DATA PADA MICROSOFT EXCEL

Ranking merupakan urutan penilai dari hasil pembandingan dengan beberapa nilai yang ada. Penerapan fungsi ini juga dapat mengurutkan rangking dari yang tertinggi ke yang terendah (descending), atapun sebaliknya ascending).

Contoh penerapan rumus:

Terdapat data Nilai Mahasiswa pada tabel dibawah ini:

Gambar K.1 (Menghitung Rangking suatu data)

Rumus atau fungsi yang kita gunakan untuk mencarinya adalah sebagai berikut:

=RANK(X;Y;Z)

Keterangan :

Nilai X = Nilai yang akan dicari nilai urutnya 

Nilai Y = Kelompok data yang ingin diurutkan. Nilai ini merupakan nilai numerik (harus angka) 

Nilai Z = [order] nilai yang menentukan pengurutan data. Jika diisi dengan nilai “0” atau dihapus, berarti akan diurutkan secara “descending”. Jika berisi nilai atau bukan nol (0), maka akan diurutkan secara “ascending”. 

Jadi secara lengkap penulisan rumus berdasarkan data diatas adalah sbb :

=RANK(I5,$I$5,$I$11,0) Lalu drag sel J5 hingga sel J11.

Keterangan :

Data pada cell J5 merupakan nilai yang akan dicari. 

$J$5:$J$11 adalah range data yang akan dibandingkan. Tanda $ (adalah nilai absolut) yang berfungsi untuk mengunci cell tersebut ketika rumusnya akan di copy (menggunakan F4). 

sekian.. semoga bermanfaat.. :)

MENGGUNAKAN FUNGSI LOOKUP PADA MICROSOFT EXCEL

      Nilai yang kita olah melalui Excel sebenarnya dapat dibagi menjadi dua bagian , yaitu nilai formula dan nilai acuan, nilai formula adalah , dimana semua nilai yang diolah menjadi satu dengan formula yang  dihitung , misal =A1 * 20. Angka 20 merupakan nilai formula. Sedangkan pada beberapa keadaan dimana nilai tersebut sering berubah  , bisa kita gunakan nilai acuan agar tidak perlu merubah melalui formula. Untuk memudahkan menggunakan nilai acuan , Excel menyediakan fasilitas Fungsi Lookup , fungsi  ini akan melihat nilai pada tabel yang lain apakah nilai yang di cocokan ada pada tabel tersebut , untuk kemudian diambil nilainya.

Contoh masalah: 

Gambar J.1 Contoh Penggunaan fungsi Lookup

Permasalahan yang akan diselesaikan adalah mengisi Gaji  Pokok berdasarkan data yang  ada di atasnya. Hal ini sebenarnya dapat diselesaikan dengan menggunakan percabangan IF, misalnya untu mengisi sel D8 dapat digunakan rumus  =IF(C3=1,$B$2,IF(C3=2,$B$3,IF(C3=3,$B$4,$B$5))).  Penyelesaian tersebut dapat digunakan jika  jumlah golongan yang ada hanya sedikit dan tidak akan berubah, bagaimana jika jumlah golongan mencapai 100 atau lebih ?. Untuk menyelesaikan permasalahan di atas telah disediakan fungsi VLOOKUP dan HLOOKUP dengan bentuk fungsinya adalah :

VLOOKUP(lookup value,table_array,col_index_num[,range_lookup])   dan

HLOOKUP(lookup value,table_array,col_index_num[,range_lookup]) , dimana :

•  lookup_value adalah data yang akan dicocokkan.

•  table_array adalah tempat pencarian data.

•  col_index_num adalah data pada kolom keberapa yang hendak diambil.

•  range_lookup (optional) adalah nilai logika yang dimasukkan, jika diisi TRUE maka

akan dicari sampai data terdekat, sedang  jika diisi FALSE maka akan dicari data

yang persis sama.

Pemakaian VLOOKUP untuk kasus di atas adalah pada sel D8 akan kita masukkan rumus  =VLOOKUP(C8,$A$2:$B$5,2), dimana C8 adalah data kunci yang akan dicocokkan, $A$2:$B$5 adalah area pencarian data termasuk kunci pencarian dan data yang akan diambil, dan 2 adalah menunjukkan kolom ke-2 dari range tersebut adalah data yang diambil.  Atau dengan cara lain dengan menggunakan function wizard sebagai berikut :

1.  Letakkan kursor pada sel C8

2.  Klik Insert -> Function

3.  Pilih kategori Lookup & Reference

Gambar J.2 (Pemakaian VLOOKUP)

4.  Pilih Menu VLOOKUP. Klik OK.

5.  Pada menu VLOOKUP Wizard pilih atau ketik sel C8.

Gambar J.3 (mengisi  Lookup_value)

6.  Klik tombol Browse pada Cell Range , Blok A1 hingga B5 , judul kolom tidak usah dipilih.

Gambar J.4 (Mengisi Table_array) 

7.  Tambahkan tanda $ untuk Range, sehingga menjadi $A$2:$B$5 , tanda $ ini untuk menjadikan sel absolut agar jika di copy ke sel dibawahnya tidak berubah referensinya.

8.  Ketik 2 dimana akan mengembalikan ke 2 pada Col_index_num, untuk lebih jelasnya

bisa dilihat ilustrasi pada gambar dibawah ini :

Gambar J.5 (Menjadikan Sel Absolut)

pada Col_index_num ketikan : 2

Gambar J.6 (Mengisi Indeks Kolom)

9.  Klik  OK, kemudian Copykan ke sel dibawahnya. Hasil akhirnya seperti di gambar

Gambar J.7 (Hasil Akhir VLOOKUP)

Sebagai pedoman dalam pemakaian VLOOKUP ini  adalah kunci pencarian harus berada di kolom paling kiri dari table_array dan kunci pencarian tersebut hars dalam keadaan sudah terurut. Pemakaian HLOOKUP sama dengan VLOOKUP, perbedaannya hanya dalam hal penyusunan datanya, yaitu kalau VLOOKUP datanya disusun secara vertikal sedangkan kalau HLOOKUP datanya disusun secara horisontal.

*sumber: Pelatihan Aplikasi Software Perkantoran.pdf

sekian.. semoga bermanfaat :)

MENGGUNAKAN FUNGSI IF PADA MICROSOFT EXCEL

              Fungsi IF merupakan fungsi dasar yang digunakan untuk mendapatkan nilai berdasarkan kondisi tertentu. Fungsi ini sering disebut sebagai fungsi keputusan, dimana kita menetapkan rule / aturan yang akan mengambil keputusan pengembalian nilai yang berbeda dari input yang masuk. Syntax dasar dari fungsi IF adalah sebagai berikut

IF(logical_test_value, value_if_true, value_if_false)

·                     logical_test, logika yang diisi dengan nilai atau ekspresi untuk menghasilkan nilai benar (TRUE) atau nilai salah (FALSE). Operator pembanding digunakan untuk pengisian logika ini antara kondisi dan syarat.

·                     value_if_true,diisi dengan nilai apabila logika bernilai benar(TRUE), Untuk nilai yang bertipe teks maka penulisannya diapit oleh tanda petik (" ")

·                     value_if_false,diisi dengan nilai apabila logika bernilai salah (FALSE).

Contoh fungsi IF:

Ketentuan:

Jika JK = P, maka JENIS sama dengan Pria. Jika JK = W, maka JENIS sama dengan WANITA.

Gambar I.1 (Menggunakan Fungsi IF)

Rumus yang kita gunakan dalam memecahkan kasus tersebut adalah, pada sel E4, ketikkan =IF(D4=”P”,”Pria”,”Wanita”).

Setelah itu untuk mengaplikasikan Fungsi IF pada keseluruhan data, maka arahkan kursor pada bagian kanan bawah sel D4. Drag atau tahan ke bawah sampai pada sel E10.

Jika kondisi lebih dari satu, maka kita gunakan operator AND atau OR.

·                     =AND (x,y)          : Menggabungkan 2 kondisi dengan operator AND (dan), kedua pernyataan harus bernilai benar.

·                     =OR(x,y)              :Menggabungkan dua kondisi dengan operator  OR(atau), maka salah satu kondisi harus benar, atau keduanya bernilai benar.

·                     Dimana x merupakan kondisi atau pernyataan pertama, sedangkan y merupakan kondisi kedua.

Contoh:

Ketentuan:

Jika umur lebih besar sama dengan 20 dan lebih kecil sama dengan 30, maka TERIMA  sama dengan Terima . Jika tidak terima sama dengan TOLAK, maka rumus yang ditulis adalah =IF(AND(C3>=20,C3<=30),”TERIMA”,”TOLAK”)

Gambar I.2 (Menghitung menggunakan Rumus IF dengan operator AND)

Untuk mengaplikasikan Fungsi IF pada keseluruhan data, maka arahkan kursor pada bagian kanan bawah sel D3. Drag atau tahan ke bawah sampai pada sel D9.

sekian... semoga bermanfaat :)

MENGGUNAKAN FUNGSI STATISTIKA DALAM MICROSOFT EXCEL

                 Function (fungsi) adalah sederetan atau sekumpulan formula yang sudah disediakan oleh Excel untuk melakukan operasi tertentu dengan menggunakan nilai yang disebut argument. Sebagai contoh, fungsi SUM akan menjumlahkan nilai semua cell pada range. Argument (argument) dapat berupa bilangan, teks, nilai logika (TRUE atau FALSE), array, atau alamat cell/range. Jenis argument yang kita berikan harus sesuai dengan ketentuan yang terdapat pada fungsi yang bersangkutan. Argument dapat berupa nilai konstan, formula, atau fungsi lain. Yang termasuk kedalam fungsi Statistik yaitu:

a.                   AVERAGE : Menghitug nilai rata-rata dari sejumlah data

Rumus dasar AVERAGE adalah  =AVERAGE (number1…number2)

Contoh: pada sel D11, masukan rumus =AVERAGE, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.1 (Rumus Menghitung Rata-Rata)

a.                   SUM : Menghitung total jumlah data yang ada

Rumus dasar SUM adalah =SUM(number1…number2)

Contoh: pada sel D11, masukan rumus =SUM, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.2 (Rumus Menghitung Jumlah Data)

b.                  COUNT: Menghitung jumlah cell atau parameter/argument yang berisi bilangan. Parameter/argument yang berupa bilangan saja yang akan dihitung, selai itu tidak akan dihitung.

Rumus dasar COUNT adalah =COUNT(value1…value2)

Contoh: pada sel D11, masukan rumus =COUNT, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.3 (Rumus Menghitung Banyak Data)

c.                   MAX : Menentukan bilangan dengan nilai tertinggi dari sejumlah data

Rumus dasar MAX adalah =MAX(number1…number2)

Contoh: pada sel D11, masukan rumus =MAX, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

 

Gambar H.4 (Rumus Menghitung Nilai Maksimum)

d.                  MIN : Menentukan bilangan dengan nilai terendah dari sejumlah data

Rumus dasar MIN adalah =MIN(number1…number2)

Contoh: pada sel D11, masukan rumus =MIN, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.5 (Rumus Menghitung Nilai Minimum)

  

e.                  MEDIAN : Mencari median (bilangan tengah) di dalam sekumpulan data

Rumus dasarnya adalah =MEDIAN(number1…number2)

Contoh: pada sel D11, masukan rumus =MEDIAN, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.6 (Rumus Menghitung Nilai Tengah)

f.                    MODE : Mencari modus (bilangan yang paling sering muncul) di dalam sekumpulan data yang diberikan

Rumus dasar nya adalah =MODE(number1…number2)

Contoh: pada sel D11, masukan rumus =MODE, kemudian klik sel D4. Arahkan kursor pada bagian kanan bawah sel, lalu tarik kebawah sampai sel D10.

Gambar H.7 (Rumus Menghitung Nilai Modus)

sekian.. semoga bermanfaat :)

MEMBUATAN LEMBAR KERJA MENGGUNAKAN MICROSOFT EXCEL

Pada artikel ini saya akan membahas  tentang cara menggunakan Microsoft Excel. Semoga     bermanfaat.. ^^

1.                  Sekilas Tentang Microsoft Excel

Microsoft Excel adalah Program aplikasi pada Microsoft Office yang digunakan dalam pengolahan angka, terdiri dari lembar kerja (Worksheet). Fungsi  dari  Microsoft  Excel  adalah  untuk  melakukan dan mempresentasikan data kedalam bentuk tabel. Worksheet pada Microsoft Excel terdiri dari kolom dan baris. Sedangkan pertemuan antara kolom dan baris disebut dengan sel / Cells. Ada 3 jenis data yang dimasukkan dalam lembar kerja, yaitu:

a)                  Label, berupa potongan teks/naskah, dengan ciri kas akan rata kiri apabila kita masukkan dalam sel.

b)                  Angka (numeric), berupa bilangan/angka, dengan cirri akan rata kanan.

c)                   Rumus (formula), berupa rumus perhitungan dalam Excel, dengan cirri khas selalu diawali dengan tanda sama dengan (=).

Semua type data diatas dapat dirubah aligmentnya sesuai dengan yang kita inginkan, dengan cara blog cells mana yang akan diubah lalu tunjuk salah satu aligment (rata kiri, tengah, kanan).

2.                Membuat Lembar kerja

Jika kita ingin membuat lembar kerja baru pada Microsoft Excel, langkah-langkah yang perlu dilakukan adalah:

a.                   Klik office button, lalu pilih New

b.                  Pilih tombol Create unltuk membuat worksheet

Gambar G.1 (Membuat  Lembar Kerja)

c.                   Setelah itu akan muncul lembar kerja Microsoft Excel seperti dibawah ini:

Gambar G.2 (Lembar Kerja Microsoft Excel)

3.                   Mengatur Lembar Kerja

Sebelum kita memasukkan data , sebaiknya kita tentukan batas-batas pengetikan dan bentuk kertas terlebih dahulu. Langkah-langkah untuk mengatur lembar kerja Ms. Excel yaitu:

a.                   Klik menu Page Layout

b.                  Pilih Margin lalu klik Custom Margin untuk menentukan batas pengetikan.

Gambar G.3 (Mengatur Lembar Kerja Microsoft Excel)

             ·                     Top, mengatur batas atas kertas

             ·                     Bottom, mengatur batas bawah kertas

             ·                     Left, mengatur batas kiri kertas

             ·                     Right, mengatur batas kanan kertas

c.                   Untuk mengatur bentuk kertas, pilih menu Page Layout, klik Margin, pilih Custom Margin, kemudian klik pilihan Page

Gambar G.4 (Mengatur Bentuk Kertas Lembar Kerja)

4.                   Bekerja dengan Microsoft Excel

            a.                   Memasukkan Data Ke Lembar Kerja

Berbagai jenis data dapat dimasukkan ke dalam lembar kerja seperti teks, nilai, tanggal, jam dan lain sebagainya. Untuk memasukkan data ke dalam suatu sel,  langkah-langkah yang dilakukan adalah  sebagai berikut :

1)                  Klik sel tempat yang akan dimasukkan data

2)                  Ketikkan data yang diinginkan

3)                  Kemudian tekan Enter, atau tekan tombol panah ke bawah dari keyboard

             b.                   Memperbaiki Kesalahan Pengetikan

Bila ada kesalahan pengetikan data, dapat diperbaiki dengan melakukan langkah  sebagai berikut :

                1.    Pilih sel yang datanya ingin diperbaiki lalu tekan tombol F2

                2.     Perbaiki data yang salah kemudian Enter

Karena jika tidak menggunakan F2 tetapi Delete, aka keseluruhan data pada sel akan terhapus.

              c.                   Membuat Range / Blok Sel

          Pada   saat   menggunakan   excel,   kita   tidak   hanya   bekerja   dalam   satu   sel   saja. Terkadang      menggunakan       kumpulan     sel  /  grup.  Misalnya   ingin  merubah    jenis huruf, rumus atau mengcopy serta menghapus dalam beberapa kolom dan (atau) baris.   Dalam   kondisi   yang   seperti   tersebut,   dapat   menggunakan   range/blok   sel untuk lebih memudahkan Range   dinamakan   menurut   alamat   sel   di   ujung   kiri   atas   sampai   ujung   kanan bawah. Contoh range dari sel B2 sampai F5 dituliskan sebagai range B2:F5 

Gambar G.5 (Membok Sel)

              d.                   Menghapus Data

          Untuk   menghapus   data   di   suatu   sel   atau   range   tertentu,   dapat   menggunakan  tombol delete. Dan bila ingin menghapus format tampilan data/sel dapat dilakukan dengan klik tombol kanan mouse kemudian pilih  Clear  Contents.

Gambar G.6 (Menghapus Data)

              e.                   Mengatur Lebar Kolom

          Kolom     dalam    excel   dapat   dirubah   sesuai   dengan     keinginan   ataupun    sesuai dengan lebar dari teks yang ada di dalam sel. Adapun caranya adalah sebagai berikut :

          -   Menggunakan Mouse

             1.    Arahkan pointer pada batas kanan kolom yang akan diubah hingga tanda

                  plus warna putih berubah menjadi tanda panah dua arah.

             2.    Drag (klik tahan) mouse sampai lebar kolom yang diinginkan

          -   Sesuai Dengan Data Terpanjang

             1.    Arahkan pointer mouse pada batas kanan huruf kolom yang akan diubah Lebarnya

             2.    Klik  dua   kali  pada    batas    kolom.    Lebar   kolom     akan   mengikuti     data

                   terpanjang yang ada pada kolom. 

              f.                   Meratakan Tampilan Data

           Bila   diperlukan,   tampilan   data   yang   tersimpan   dapat   diatur   pada   suatu   sel   atau range    tertentu   agar   posisinya   ditampilkan   rata    kanan,   kiri,  ditengah   sel  atau ditengah beberapa kolom tertentu. Cara yang dapat dilakukan adalah dengan menggunakan baris menu (menubar) atau toolbar formatting.

           -  Meratakan Data dengan Perintah di Baris Menu (MenuBar)

a.                   Blok sel atau range yang akan diubah

b.                  Klik menu Format  Cells pilih Alignment

c.                   Setelah muncul kotak dialog format cell, pilih tab Alignment

d.                  Lakukan pemilihan sesuai dengan keinginan:

   •  Vertical : digunakan untuk memilih perataan secara vertikal (atas bawah)

                     Top   :Rata atas

                      Center            : Rata tengah

                      Bottom           :Rata bawah

                      Justify             :  Data ditampilkan pada sel secara penuh

•  Horizontal : beberapa pilihan yang dapat dilakukan :

                      General                          :  Huruf rata kiri dan angka rata kanan

                      Left (Indent)                   : Rata kiri

                      Center                            : Rata tengah

                      Right                              : Rata kanan

                      Fill                                 : Mengisi seluruh sel dengan mengulang data

                      Justify                            :Data ditampilkan pada sel secara penuh              

                      Center Across Selection : Data ditampilkan ditengah – tengah  beberapa kolom

 •  Orientation : untuk mengatur orientasi data dan derajat kemiringannya.

 e.    Klik OK

Gambar G.7 (Merapikan Tampilan Data)

           g.                   Membuat atau Memberi Garis pada Sel

           Untuk   memperindah   atau   untuk   lebih   memudahkan   dalam   pembacaan,   dapat   juga   sel   atau   range   diberikan   garis   pemisah.   Dalam   memberikan   garis,   dapat  dilakukan sebagai berikut :

           1.     Pilih sel atau Blok range sel yang akan diberikan garis pembatas

           2.     Klik menubar Format  Cell  pilih Border 

           3.     Pada bagian Presets, pilih salah satu tombol

           4.     Pada bagian Border, pilih garis pembatas yang diinginkan

           5.     Pada kotak pilihan Style, pilih jenis garis yang diinginkan

           6.     Pada kotak pilihan Color, pilih jenis warna untuk garis yang diinginkan

           7.     Bila sudah selesai, klik OK

Gambar G.8 (Membuat Garis Pada sel)

           h.                   Menyisipkan Sel, Baris Dan Kolom

Adakalanya dalam memasukkan data ada yang terlewatkan. Untuk itu perlu kita menyisipkan baris atau kolom. Adapun langkah-langkah yang ditempuh adalah sebagai berikut :

1.  Pilih sel atau buatlah range tempat sel, baris atau kolom baru akan disisipkan

2.  Klik pada menu bar :

•  Insert  Rows   : untuk menyisipkan baris baru

•  Insert  Columns   : untuk menyisipkan kolom baru

•  Insert  Cells   : untuk menyisipkan sel baru

             i.                   Menghapus Baris/Kolom

                         ·                     Tunjuk sel pada baris atau kolom yang akan dihapus

                         ·                     Klik menu Edit, kemudian klik Delete

             j.                   Memindahkan Sel

                         ·                     Pilih sel/range yang akan dipindahkan

                         ·                     Lalu klik toolbar Cut (atau menggunakan menu Edit Cut)

                         ·                     Pindahkan sel ketempat yang diinginkan

                         ·                     Klik toolbar Paste (atau dari menu Edit Paste)

             k.                  Mengkopy Isi Sel

                         ·                     Pilih sel/range yang akan dicopy

                         ·                     Klik toolbar Copy (atau menggunakan tombol kanan Mouse lalu pilih Copy)

                         ·                     Pindahkan isi sel ketempat yang diinginkan

                         ·                     Klik toolbar Paste (atau menggunakan tombol kanan Mouse lalu pilih Paste)

              l.                   Membuat no Urut:

                          ·                     Awali angka awal pembuatan nomor urut dengan tanda petik atas (‘) contoh (‘1)

                         ·                     Kemudian copy ke bawah penunjuk sel diletakkan pada pojok kanan bawah, lalu        tarik kebawah atau kekanan sesuai yang diinginkan.

Gambar G.9 (Membuat Nomor Urut)

                   m.                   Merubah Lebar Baris:

                             ·                     Blok range/daerah lembar kerja

                             ·                     Pilih format Row Height

                             ·                     Isikan nilainya, kemudian tekan OK

                             ·                     Selain dengan cara diatas, dapat juga dilakukan dengan cara: letakan kursor pada garis baris yang akan diperlebar disebelah paling kiri (1,2,3,…) kemudian tarik ke bawah atau ke atas.

Gambar G.10 a & b (Merubah Lebar Baris)

                   n.                   Meletakan Posisi Judul agar Berada di Tengah

                            ·                     Blog judul sepanjang kolom yang ada

                            ·                     Lalu klik toolbar Merge and Center 

          Gambar G.11 (Menggunakan Merge and Center)

                   o.                   Mengurutkan Data:

                           ·                     Blok data yang akan diurutkan

                           ·                     Klik menu Data Sort

                           ·                     Pilih model pengurutan (ORDER) data . Ascending (AZ) digunakan untuk mengurutkan data dari data terkecil ke data terbesar, Descending( ZA) digunakan untuk mengurutkan data dari data terbesar ke terkecil.

                           ·                     Sort by adalah untuk memilih berdasarkan apa data akan diurutkan

Gambar G.12 (Mengurutkan Data)

Sekian.. Semoga bermanfaat.. :)