Cara Merangking pada Microsoft Excel

Bagaimana cara merengking data di Microsoft Excel?

Bagaimana cara merengking data di Microsoft Excel 2007?

         Untuk mengurutkan data di Microsoft Excel 2007 kita bisa menggunakan sub menu Sort & Filter. Proses mengurutkan data dibagi kedalam 2 jenis:

         Proses mengurutkan data Alpabetik dari A ke Z, pada proses ini data yang mempunyai nilai terkecil akan menempati urutan pertama. Misal kita akan mengurutkan bilangan 5, 3, 1,2,4. Maka setelah proses pengurutan data, hasilnya adalah 1,2,3,4,5. Atau jika jenis karakter yang diurutkan berupa huruf, misal B,D,E,C,A. Maka setelah proses pengurutan data hasilnya adalah A,B,C,D,E.

         Proses mengurutkan data Alpabetik dari Z ke A, pada proses ini data yang mempunyai nilai terbesar akan menempati urutan pertama. Misal kita akan mengurutkan data berupa bilangan 5,3,1,2,4. Maka setelah proses pengurutan, hasilnya adalah 5,4,3,2,1.

Perhatikan contoh berikut:

          Sesuai tabel diatas, jika kita ingin mencari tahu urutan nilia siswa dari yang terbesar sampai yang terkcil, proses ini biasa dilakukan para wali kelas ketika ingin melakukan perengkingan untuk mengetahui urutan juara kelas berdasarkan akumulasi nilai keseluruhan mata pelajaran.

Langkah berikutnya:

Blok Cell dari mulai Cell B2 ( kolom No ) sampai dengan Cell E7 ( kolom Nilai, 80 ). Seperti gambar dibawah ini:

Klik sub menu ( baca: tab ribbon atau apalah namanya ) Sort & Filter

Pilih Custom Sort

Kemudian pada Sort by pilih Nilai ( untuk mengurutkan data berdasarkan kolom Nilai)

Pada Order pilih Largest to smallest, kemudian klik Ok

Lihata hasilnya, data sudah terurut dari urutan terbesar ke urutan terkecil.

Ket: Jika ingin merubah urutan data berdasarkan kategori nilai terkecil ke nilai terbesar, pada Order pilih smallest to largest

Demikian artikel singkat mengenai bagaimana cara mengurutkan data pada Microsoft Excel 2007, mudah-mudahan dapat membantu pembaca yang saat ini kebetulan belum mengetahui proses tersebut.

Penggunaan Fungsi LOOKUP

Apa itu VLOOKUP ?

VLOOKUP adalah salah satu fungsi pada Microsoft Excel yang sangat populer digunakan sejak versi awal sampai sekarang.

Fungsi lookup value pada Excel ada dua, yaitu :

• VLOOKUP (Vertical Lookup).
• HLOOKUP (Horizontal Lookup). 

Sesuai namanya, VLOOKUP digunakan untuk mencari data pada
baris demi baris (vertikal) pada tabel referensi.
Sebaliknya, HLOOKUP digunakan untuk mencari data dengan
referensi data kolom demi kolom (horisontal).

Untuk memperjelas cara kerja dari VLOOKUP, perhatikan gambar ilustrasi berikut ini.

Ilustrasi tersebut menggambarkan mekanisme
pencarian VLOOKUP sebagai berikut :

1. suatu cell data dengan nilai "kode 3"  (lookup value)                                                             akan digunakan sebagai kunci pencarian ketable referensi (table_array) .
2. data pada kolom pertama (kode 1, kode 2, kode 3, dan kode 4)                                           pada table_array berfungsi sebagai kunci referensi.
3. dari proses ini, kita akan ketemu data yang dicari pada                                                                posisi di baris ke 3 table referensi.
4. tetap pada baris ke 3 tersebut, kita akan mengambil nilai                                                                 dari kolom yang kita inginkan (col_index_num).                                                                                 Misalkan kita mengambil kolom ke 2, maka kita akan                                                                     mendapatkan nilai 4 sebagai datanya.
5. selesai.

Syntax VLookup

VLOOKUP                                                                                                   (lookup_value, table_array, col_index_num, range_lookup)

Keterangan :

• lookup_value  :                                                                  nilai yang akan dicari ke table_array
• table_array   :                                                                   range nilai dimana terdapat nilai yang dicari
• col_index_num :                                                                     indeks kolom yang dicari
• range_lookup  :                                                          berisi nilai 0 atau 1.                                                             Nilai 1 artinya jika tidak ada nilai yang cocok,                                   maka akan dicari pendekatannya.

Contoh Penggunaan

Kita akan mencoba menggunakan vlookup untuk mencari                                                                                               referensi nama produk dari kode produk dalam suatu daftar transaksi.

• Download file contoh dari link berikut :
  http://contoh-spreadsheet.googlecode.com/files/data_vlookup.xlsx
• Jalankan aplikasi MS Excel 2007 dan buka file data_vlookup.xlsx tersebut
• File ini berisi dua sheet :
• transaksi_penjualan : berisi contoh data transaksi penjualan
• master_produk : berisi data lengkap referensi produk

• Buka sheet transaksi_penjualan. Pada sheet ini tidak ada informasi nama produk,                            yang ada adalah data kode produk.                                                                                                                                             Dan melalui nilai kode produk ini kita akan cari data nama produk                                                       yang terdapat pada sheet master_produk.
• Sisipkan 1 kolom kosong diantara kolom E (kode_produk)                                                           dan F (jumlah_pembelian) sehingga F bergeser ke G.                                                                      Pada kolom F1 isikan nilai nama produk.

• Cell-cell yang berada di bawah kolom F (nama produk) ini akan dicari dari sheetmaster_produk berdasarkan nilai cell pada kolom E (kode_produk) dengan penggunaan fungsi VLookup.
  
  
  Sebelumnya pastikan bahwa table data pada sheet master_produk sudah terurut pada kolomB (kode_produk).
• Pada kolom F2 masukkan fungsi vlookup sebagai berikut :
  
  
  =VLOOKUP(E2,master_produk!$B$2:$F$42,3)
  
  
  dimana E2 menunjukkan nilai dari kode produk, yang akan dicari ke tabel data dengan range B2 s/d F42 di sheet master_produk.

• Dari range $B$2:$F$42 tersebut pastikan kolom pertama adalah kolom B (kode produk) sebagai referensi. Setelah itu kita ambil kolom ketiga dari range yang merupakan nama produk.
• Anda seharusnya mendapatkan nama produk "salak 1 kg" pada cell F2.
• Copy cell F2 ke seluruh baris di bawah. Anda seharusnya mendapatkan hasil sebagai berikut di bawah ini.

• Selesai

Penggunaan Fungsi IF

Fungi IF – Menentukan Nama Berdasarkan Kode

Microsoft Excel adalah program pengolah berbasis angka yang sering di gunakan di perkantoran ataupun di instansi – instansi lainnya yang membutuhkan penghitungan dan pembuatan laporan secara komputerisasi.

dan terkadang, banyak formula atau rumus yang diperlukan dalam membantu proses pengerjaan sebuah laporan tersebut.

berikut ini adalah fungsi “IF” sederhana dengan sebuah contoh kasus.
Inti dari fungsi if adalah penggunaan logika.

Kasus : Sebuah rumah sakit memiliki 3 kode ruang sebagai berikut :

Kode Ruang	Nama Ruang
V01	Mawar
V02	Melati
V03	Kamboja

Maka logikanya adalah :

Jika cell nya bertuliskan = “V02″, maka namanya “Melati”,
Jika cell nya bertuliskan = “V01″, maka namanya “Mawar”,
Jika cell nya bertuliskan = “V03″, maka namanya “Kamboja”

Fungsi If nya :

=IF(B3=”V02″,”Melati”,IF(B3=”V01″,”Mawar”,IF(B3=”V03″,”Kamboja”)))

- Keterangan berwarna Merah adalah tempat Anda menaruh kode tersebut. (silahkan sesuaikan dengan settingan Anda)

Berikut lebih jelasnya :

Demikianlah, fungsi If sederhana untuk menentukan nama berdasarkan kode, Anda dapat menerapkannya dengan kasus yang berbeda.

Untuk lebih jelas, Anda dapat mempelajarinya, silahkan unduh file disini (52,3 KB via 4Shared)

Mungkin nama ruangannya Anda ganti menjadi nama bagian suatu instansi :
Misalkan jika kodenya X011 nama instansinya Puskesmas
lalu kode X014 nama instansinya Sekolah Dasar

Dapat juga digunakan sebagai penentu harga berdasarkan kode :
Misalkan Jika kodenya 09123 maka Anda ganti keterangan diatas menjadi  Rp. 900.000
lalu kode 09100 Anda ganti nama “melati” diatas menjadi Rp.100.000

Fungsi IF – Untuk Menentukan Besaran Jumlah Tagihan

Sekarang kita akan membahas hal yang sama yaitu fungsi “if” sederhana, namun dengan tujuan dan contoh yang berbeda..
Berikut ini adalah fungsi “IF” sederhana dengan sebuah contoh kasus.
Inti dari fungsi if adalah penggunaan logika.

PENTING!

1. Tanda < artinya : Kurang dari
2. Tanda > artinya : Lebih dari
3. Tanda * artinya : dikali

 Contoh kasus :

Sebuah Rumah Sakit menetapkan bahwa :

Bila menginap (lebih dari) > 2 hari maka : Tarif harus di kalikan 20%
Bila menginap 2 hari maka : Tarif harus di kalikan 10%Bila menginap (Kurang dari) < 2 hari maka : Tarif harus di kalikan 5%

Maka logikanya adalah :

Jika cell nya (kolom lama menginapnya) lebih dari (>) 2, maka cell tarif dikali 20%
Jika cell nya (kolom lama menginapnya) Sama dengan (=) 2, maka cell tarif dikali 10%
Jika cell nya (kolom lama menginapnya) kurang dari (<) 2, maka cell tarif dikali 5%

Maka, Fungsi IF nya adalah :

=IF(F3>2,E3*20%,IF(F3<2,E3*5%,IF(F3=2,E3*10%)))

- keterangan yang berwarna Merah adalah Cell tempat menaruh lama menginap (Silahkan Anda sesuaikan dengan milik Anda sendiri)

- keterangan yang berwarna Biru adalah Cell tempat menaruh Tarif (Silahkan Anda sesuaikan dengan milik Anda sendiri)

Berikut Previewnya, Silahkan di klik untuk lebih jelas

Penggunaan Fungsi Statistika

        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.

Berikut merupakan fungsi statistik dalam Excel yang sering digunakan dalam aplikasi pekerjaan.

AVERAGE
Penjelasan: Menghasilkan nilai rata-rata untuk semua nilai yang diberikan. Syntax: AVERAGE(bilangan1,bilangan2,…). Contoh: Jika range A1:A5 diberi nama Nilai dan berisi nilai 10, 7, 9, 27, dan 2, maka: =AVERAGE(Nilai) menghasilkan 11.

COUNT
Penjelasan: Menghitung jumlah cell atau parameter/argument yang berisi bilangan. Parameter/argument yang berupa bilangan saja yang akan dihitung, selai itu tidak akan dihitung. Syntax: COUNT(nilai1,nilai2,…). Contoh: Jika kita memiliki data =COUNT(A1:A6) menghasilkan 3.

COUNTA
Penjelasan: Menghitung jumlah cell atau parameter/argument yang tidak kosong. Syntax: COUNTA(nilai1,nilai2,…). Contoh: Jika kita memiliki data =COUNTA(A1:A7) menghasilkan 6.

MAX
Penjelasan: Mencari bilangan terbesar dalam sekumpulan data yang diberikan. Syntax: MAX(bilangan1,bilangan2,…). Contoh: Jika range A1:A5 berisi 10, 7, 9, 27, dan 2 maka: =MAX(A1:A5) menghasilkan 27. =MAX(A1:A5,30) menghasilkan 30.

FORECAST

Penjelasan: Menghitung atau memperkirakan, nilai yang belum diketahui berdasarkan nilai-nilai yang sudah diketahui. Syntax: FORECAST(x_dicari,y_diketahui,x_diketahui). Contoh: Misal, kita sebagai orang yang bertanggung jawab dalam menyusun jadual produksi suatu perusahaan tempe RASA LANGIT™. Perusahaan tersebut memiliki mesin tempe otomatis SETENGAHMATENG® sebanyak lima buah. Kita kemudian mengumpulkan data jumlah tempe yang dihasilkan per jam (dalam ribuan) untuk jumlah mesin yang berbeda sebagai berikut: Karena Negara sedang mengadakan TEMPEISASI, maka perusahaan menerima pesanan sebanyak 200 ribu tempe/ hari. Untuk memenuhi pesanan tersebut, perusahaan ini akan menambahkan jumlah mesin yang ada menjadi 10 buah. Tetapi kita tidak yakin apa 10 mesin dapat menghasilkan 200 ribu tempe, karena itu kita dapat mencoba untuk memperkirakannya dengan menggunakan fungsi di dalam Excel sebagia berikut: =FORECAST(10,B2:B6,A2:A6) Apakah hasilnya benar???? Berapakah jumlah minimal untuk menghasilkan minimal 200 ribu tempe/ hari?

FREQUENCY

Penjelasan: Menghitung kemunculan data dengan batasan tertentu pada daftar data yang kita berikan dan mengembalikan kumpulan nilai berupa array vertical. Karena fungsi FREQUENCY ini menghasilkan nilai berupa array, maka dalam memasukkan fungsi ini harus menekan Ctrl+Shift+Enter. Syntax: FREQUENCY(array_sumber_data,array_batasan_data). Contoh: Misalnya, kita sebagai dosen mempunyai data nilai mahasiswa yang dimasukkan di range A1:A10 (array_sumber_data) sebagai berikut: 79, 54, 87, 49, 66, 91, 72, 83, 95, dan 65. Kemudian kita ingin mengelompokkan nilai-nilai tersebut berdasarkan batasan 0-65, 66-75, 76-85, dan 86-100, kemudian kita masukkan di range B1:B3 (array_batasan_data) sebagai berikut 65, 75, 85 (yang terakhir tidak perlu dimasukkan). Lalu kita harus membuat array formula di range C1:C4 dan memasukkan formula =FREQUENCY(A1:A10,B1:B3). Hasilnya kurang lebih seperti berikut ini:

MEDIAN
Penjelasan: Mencari median (bilangan tengah) di dalam sekumpulan data yang diberikan. Syntax: MEDIAN(bilangan1,bilangan2,…). Contoh: =MEDIAN(2,4,1,3,5) menghasilkan 3.

MIN
Penjelasan: Mencari bilangan terkecil dalam sekumpulan data yang diberikan. Syntax: MIN(bilangan1,bilangan2,…). Contoh: Jika range A1:A5 berisi 10, 7, 9, 27, dan 2 maka: =MIN(A1:A5) menghasilkan 2.

GROWTH

Penjelasan: Menghitung “nilai pertumbuhan” dengan menggunakan data yang sudah diketahui. Karena fungsi GROWTH ini menghasilkan nilai berupa array,maka ketika memasukkan fungsi ini, maka jangan lupa menekan Ctrl+Shift+Enter. Syntax: GROWTH(y_diketahui,x_diketahui,x_dicari). Contoh: Kita kembali membicarakan tentang perusahaan tempe RASA LANGIT™. Setelah sukses dalam tahun-tahun terakhir, terkadang perusahaan masih tetap kewalahan untuk menangani pesanan yang terus meningkat per bulannya. Untuk itulah, kita sekarang sudah diangkat menjadi Marketing Director yang dituntut untuk bisa membaca peluang pasar. Kita kemudian menyusun data penjualan tempe 6 bulan ke belakang (bulan 11 – 16) sebagai berikut: Lalu kita mengetahui peluang pasar 2 bulan berikutnya (bulan 17 -18), kita bisa memasukkan array formula di B8:B9 seperti berikut: =GROWTH(B2:B7,A2:A7,A8A9), sehingga kita akan mendapatkan bahwa peluang penjualan bulan ke-17 adalah 3201.967 (ribu) temped an bulan ke-18 adalah 4685.361 (ribu) tempe.

QUARTILE

Penjelasan: Mencari kuartil (nilai paruh) tertentu dari sekumpulan data yang diberikan. Kuartil diperlukan untuk menentukan 25% pemasukkan tertinggi. Syntax: QUERTILE(data,no_kuartil). No_kuartil Nilai yang dihasilkan 0 Nilai paling kecil (sama dengan fungsi MIN). 1 Kuartil pertama – 25%. 2 Kuartil kedua – 50% (sama dengan fungsi MEDIAN). 3 Kuartil ketiga – 75% 4 Nilai paling besar (sama dengan fungsi MAX) Contoh: =QUARTILE({1,2,4,7,8,9,10,12},1) menghasilkan 3.5.

MODE
Penjelasan: Mencari modus (bilangan yang paling sering muncul) di dalam sekumpulan data yang diberikan. Syntax: MODE(bilangan1,bilangan2,…). Contoh: =MEDIAN(5,6,4,4,3,2,4) menghasilkan 4.

PERMUT

Penjelasan: Menghitung banyak permutasi. Syntax: PERMUT(jumlah_bilangan,jumlah_dipilih). Contoh: Dengan fungsi ini kita bisa mengetahui banyaknya kendaraan bermotor yang memiliki nomor polisi KT xxxx UV. Kita mengetahui bahwa nomor normal kendaraan bermotor adalah 4 angka dengan kombinasi angka 0 sampai 9 (sebanyak 10 angka). Maka kita bisa memasukkan fungsi =PERMUT(10,4) dan hasilnya adalah 5040 kendaraan motor.

Pembuatan Lembar Kerja pada Microsoft Excel

1

1. Buka Excel . 

2

2. Perhatikan ada baris dan kolom. 

• Setiap kolom memiliki huruf kapital di atas untuk menunjukkan apa kolom itu. 

• Setiap baris memiliki jumlah ke kiri langsung dari kolom pertama, untuk menunjukkan apa baris itu. Misalnya, alamat sel pada baris, kolom pertama pertama adalah A1.Alamat dari sel dalam kolom kedua, baris ketiga adalah B3. 

• Jika Anda mengklik sel, alamat sel muncul tepat di atas kolom A. 

   3. Klik Simpan