Menggunakan Fungsi SUBTOTAL (Organisasi Data Dengan Level)

download file praktek disini !

Setelah anda mengurutkan data di worksheet, anda dapat menggunakan fungsi Subtotal untuk membuat data anda disajikan secara bertingkat (berdasarkan Level). Misalnya didalam worksheet terdapat data penjualan untuk tiga kategori produk yang berbeda, anda bisa menyortir produk berdasarkan kategori dengan cara yang lebih mudah. Untuk menggunakan fungsi SubTotal anda bisa mengklik Tab Data  >> Groups Outline >> Subtotal

Gambar 1. Kotak Dialog Subtotal

Pada kotak dialog Subtotal, anda dapat memilih kolom yang menjadi dasar subtotal (seperti misalnya Tahun, Bulan, Semester), Formula Perhitungan yang ingin anda lakukan dan kolom dengan nilai yang akan anda hitung. Setelah Anda menentukan subtotal, data akan muncul di worksheet secara terstruktur.

Gambar 2. Hasil Subtotal

Bila Anda menerapkan subtotal untuk mengolah data pada worksheet, Excel juga mendefinisikan kelompok berdasarkan baris yang digunakan untuk menghitung subtotal. Pengelompokan akan membentuk suatu outline pada worksheet Anda berdasarkan kriteria yang digunakan untuk membuat subtotal. Sebagai contoh, semua baris yang mewakili bulan di tahun 2009 menjadi satu kelompok, baris yang mewakili bulan pada tahun 2010 menjadi satu kelompok dan seterusnya. Daerah garis di sisi kiri lembar kerja Anda memperlihatkan suatu kontrol yang dapat digunakan untuk menyembunyikan atau menampilkan kelompok baris dalam  worksheetAnda.

Tiga jenis kontrol dapat muncul di outline, yaitu: Hide Detail Button, Show Detail Button, dan Level Button. Hide Detail Button di samping kelompok baris dapat diklik untuk menyembunyikan baris dalam kelompok itu. Bila Anda menyembunyikan sekelompok baris, tombol yang ditampilkan di sebelah grup berubah menjadi Show Detail Button. Mengklik Show Detail Button akan  memperlihatkan kembali kelompok baris dalam worksheet.

Gambar 3. Tombol Hide Detail & Show Detail SubTotal

Level Button adalah tombol di wilayah outline worksheet dengan fungsi subtotal. Setiap tombol merupakan tingkat organisasi di dalam Worksheet; mengklik tombol tingkat menyembunyikan semua tingkat detail di bawah tombol yang Anda klik. Tabel berikut menjelaskan data yang terdapat pada setiap tingkat lembar kerja dengan tiga tingkat organisasi data worksheet

Level	Deskripsi
1	Grand Total
2	Subtotal setiap kelompok/groups
3	Baris Individual di dalam Worksheet

Mengklik tombol Level 2 di worksheet akan menyembunyikan baris dengan data, misalnya, pendapatan setiap bulan, tetapi akan meninggalkan baris yang berisi grand total (Level 1) dan semua baris yang berisi subtotal untuk setiap tahun (Level 2) terlihat dalam worksheet.

Gambar 4. Hasil Subtotal dengan Mengklik Tombol Level 2

Jika mau, anda dapat menambahkan tingkat detail untuk outline yang dibuat oleh Excel. Misalnya, anda mungkin ingin menyembunyikan pendapatan bulan Januari dan Februari, dikarenakan kedua bulan ini sebenarnya adalah bulan dengan penjualan yang besar, karenanya tidak perlu dianalisis lagi. Untuk membuat grup garis baru dalam grup yang sudah ada, pilih baris yang ingin anda kelompokkan pada Tab Data >> Groups Outline >> Groups  >> Klik Group.

Gambar 5. Melakukan Grouping pada Subtotal

Anda dapat menghapus grup dengan memilih baris dalam kelompok dan kemudian, pada kelompok Outline, klik Ungroup. Untuk Menghilangkan Subtotal, File Data >> Groups Outline >> Subtotal >> Remove All

Read More

Mengurutkan (Sorting) Data Worksheet

Dengan menggunakan Excel anda bisa sangat mudah memasukkan dan mengelola data. Meskipun begitu, apabila data tersebut tidak disortir, tentunya akan sangat merepotkan kita dan belum tentu membantu dalam pengambilan keputusan bisnis. Karena bisa saja data yang kita miliki terdiri dari puluhan ribu data dan tidak semuanya kita butuhkan. Misalnya, anda mungkin ingin menemukan produk layanan apa menghasilkan keuntungan yang paling besar? Dan Produk layanan mana yang memiliki biaya operasional paling tinggi? dan sebagainya. Dengan menggunakan Excel 2010, anda akan mendapatkan jawaban  pertanyanyaan-pertanyaan diatas dengan melakukan Penyortiran /Pengurutan Data (Sorting Data).

Bila anda mengurutkan data dalam worksheet berarti anda akan mengatur ulang baris worksheet berdasarkan isi sel dalam kolom tertentu. Misalnya, anda bisa menyortir worksheet untuk menemukan  layanan yang memberikan pendapatan tertinggi.

Anda dapat mengurutkan sekelompok baris data dalam worksheet dengan beberapa cara, tetapi langkah pertama adalah mengidentifikasi kolom yang menyediakan nilai-nilai baris yang akan disortir. Misalnya kolom Revenue, berisi data baris jumlah Pendapatan perusahaan, anda bisa menemukan total pendapatan tertinggi dengan menyortir pada sel-sel di kolom Pendapatan. Langkah berikutnya adalah letakkan pointer mouse pada kolom revenue, tampilkan Tab Home >> Groups Editing >> Sort & Filter List >> Filter >> Pada Kolom Revenue akan muncul Tanda panah >> Klik Tanda panah pada Kolom >> Klik Sort Largests To Smallest. Dengan mengklik Sort Largest to Smallest akan memerintahkan Excel menempatkan baris dengan nilai pendapatan tertinggi pada kolom Revenue di bagian atas worksheet dan  terus turun ke nilai terendah.

Gambar 1. Proses Sorting Data dengan Largest to Smallest

Perintah Sortir Smallest to Largest dan Sort Largest to Smallest memungkinkan anda untuk mengurutkan baris dalam worksheet dengan cepat, tetapi anda hanya dapat menggunakannya untuk menyortir worksheet berdasarkan isi dari satu kolom. Akan tetapi kadang kala kita ingin mengurutkan data dengan menggunakan data dari dua kolom atau lebih. Misalnya anda ingin melihat pelanggan yang paling sering menggunakan masing-masing kategori layanan. Untuk mengurutkan baris dalam worksheet dengan menggunakan dua kolom atau lebih anda bisa menggunakan  kotak dialog Custom Sort, dimana anda dapat memilih sejumlah kolom untuk digunakan sebagai kriteria pengurutan dan memilih apakah akan mengurutkan baris dalam urutan naik (Ascending) dan Turun (Descending). Cara mengaktifkan Custom Sort adalah sebagai berikut: Tab Home >> Groups Editing >> Sort & Filter >>  Custom Sort

Gambar 2. Kotak Dialog Custom Sort

Jika data anda memiliki header, pilih tanda centang Check Box My data has Headers sehingga judul kolom akan muncul di daftar Sort By. Setelah mengidentifikasi kolom mana yang ingin anda urutkan dan kolom tersebut ada pada daftar Sort By, anda bisa memilih berbagai macam cara untuk melakukan pengurutan  pada box Sort On misalnya urutkan berdasarkan Values (default),  Cell Color, Font Color, atau Cell Icon yang ditampilkan di sel. Akhirnya pada Box Order, anda dapat memilih cara bagaimana Excel akan melakukan pengurutan nilai didalam kolom. Cara-cara pengurutan tergantung pada jenis data yang akan dilakukan pengurutan.

Jika anda ingin Menambah, memindahkan, menyalin, dan menghapus level pengurutan anda tinggal meng-klik tombol add level, delete level, copy level dan Move up & down  yang telah tersedia tersedia di kotak dialog Custom Sort. Pada Excel 2003 dan versi sebelumnya dari, anda hanya bisa menentukan maksimal tiga level penyortiran. Pada Microsoft Excel 2010, anda dapat membuat hingga 64 level penyortiran.

Untuk menghapus Level, klik Level yang ada didalam daftar, dan kemudian klik Delete Level. Mengklik tombol Copy Level memungkinkan anda untuk menyalin semua setting dari satu aturan ke aturan yang lain sehingga menghemat pekerjaan anda jika hanya perlu mengubah satu item. The Move Up dan Move Down, yang diwakili tanda panah atas bawah memungkinkan anda untuk mengubah posisi tingkat penyortiran dalam urutan. Akhirnya, klik tombol Options menampilkan kotak dialog Sort Options, yang dapat anda gunakan untuk membuat kasus tingkat penyortiran case sensitif dan untuk mengubah orientasi pengurutan (top to down atau sebaliknya ).

Setting default untuk Excel adalah untuk mengurutkan nomor sesuai dengan nilai-nilai mereka dan untuk memilah kata-kata dalam urutan abjad. Bahkan secara default excel telah menyediakan pola-pola daftar urutan untuk beberapa kategori, misalnya Hari, Bulan dan Tahun . Akan tetapi tidak semua kategori daftar urutan terdapat di daftar Excel 2010, ataupun apabila kita ingin membuat daftar urutan tertentu yang secara pasti tidak ada didalam daftar Excel 2010, misalnya daftar urutan jadwal petugas piket. Untuk mengatasi masalah tersebut itu Excel 2010 menyediakan fungsi Custom List. Dengan  Custom List kita akan dapat membuat daftar urutan-urutan kategori sekehendak kita dan memasukkannya kedalam  list Excel untuk dipergunakan  lagi.

Untuk membuat daftar urutan baru, ketik daftar nilai yang anda inginkan ke sel yang bersebelahan (bisa menurun atau mendatar) >> seleksi range cel tersebut >> klik tab File >> Options  >> Advanced  >>  General >> Edit Custom List >> Import

Gambar 3. Kotak Dialog Custom List

Read More

Melakukan Penjumlahan Pada Data Yang Terfilter

Ada beberapa keterbatasan dalam mengolah data yang difilter dengan menggunakan fungsi seperti SUM dan AVERAGE. Salah satu keterbatasan adalah bahwa setiap formula yang Anda buat yang mencakup fungsi SUM dan AVERAGE tidak mengubah perhitungan mereka jika beberapa baris yang digunakan dalam formula tersembunyi oleh filter. Jadi  tetap saja apabila  kita menjumlah mereka dalam keadaan terfilter fungsi SUM & AVERAGE akan menghitung semua penjumlah baik yang terfilter maupun tidak.

Excel menyediakan dua cara untuk mengolah data untuk sel-sel yang terlihat dalam daftar data yang terfilter. Metode pertama adalah dengan menggunakan AutoCalculate. Untuk menggunakan AutoCalculate, Anda pilih sel yang akan diolah. Bila Anda melakukannya, Excel akan menampilkan rata-rata (AVERAGE) nilai dalam sel, jumlah (SUM) dari nilai dalam sel, dan jumlah sel yang terlihat (COUNT) dalam pemilihan. Anda akan menemukan tampilan pada status bar di tepi bawah jendela Excel.

Gambar 1. Autocalculate

Bila Anda menggunakan AutoCalculate, Anda tidak terbatas untuk menemukan SUM, AVERAGE, dan COUNT dari sel yang telah dipilih. Untuk menampilkan fungsi lain yang dapat Anda gunakan, klik kanan status bar dan pilih fungsi yang Anda inginkan dari menu shortcut. Hasil pilihan dari menu shortcut akan ditampilkan langsung di status bar.

Gambar 2. Menu Shortcut Perhitungan dengan Klik Kanan pada Autocalculate

AutoCalculate merupakan cara yang sangat berguna untuk mencari nilai SUM atau AVERAGE untuk data yang terfilter, akan tetapi itu AutoCalculate tidak membuat hasil perhitungannya tersedia dan tertulis didalam worksheet. Rumus seperti =SUM(C3:C26) selalu mempertimbangkan setiap sel dalam range, terlepas dari apakah Anda menfilternya ataupun tidak. Sehingga untuk memecahkan masalah tersebut anda perlu membuat formula dengan menggunakan Fungsi AGREGAT (yang merupakan fungsi yang baru di Excel 2010) untuk mengolah data terfilter. 

Dengan fungsi AGREGAT, Anda dapat memilih dari lebih luas fungsi dan menggunakan argumen lain untuk menentukan nilai yang diabaikan dalam perhitungan. AGREGAT memiliki dua sintaks yang dapat digunakan mungkin, tergantung pada operasi perhitungan yang ingin Anda pilih. Sintaks pertama =AGGREGATE(function_ num, options, ref1…), sedangkan  Sintaks kedua adalah  =AGGREGATE(function_num, options, array, [k]), digunakan untuk membuat fungsi AGREGAT yang menggunakan LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, dan QUARTILE.EXC

Tabel berikut menyajikan ringkasan operasi yang tersedia untuk digunakan dalam fungsi AGREGAT

Number	Fungsi	Deskripsi
1	AVERAGE	Untuk Mengetahui nilai rata-rata dari sederetan nilai yang terdapat dalam suatu range
2	COUNT	Untuk mengetahui jumlah sederetan nilai sel yang terdapat dalam range
3	COUNTA	berfungsi untuk menghitung jumlah sel yang tidak kosong dalam sebuah range data
4	MAX	Untuk mengetahui nilai tertinggi dari sederetan nilai yang terdapat dalam suatu range
5	MIN	Untuk mengetahui nilai tertinggi dari sederetan nilai yang terdapat dalam suatu range
6	PRODUCT	Untuk mengetahui nilai hasil perkalian dari sederetan nilai yang terdapat dalam suatu range
7	STDEV.S	Menghitung standar deviasi dari nilai-nilai dalam range data dengan memeriksa sampel dari nilai-nilai
8	STDEV.P	Menghitung standar deviasi dari nilai-nilai dalam range data dengan menggunakan semua nilai
9	SUM	Untuk mengetahui nilai total dari sederetean nilai yang terdapat dalam sutau range

Argumen Kedua, Options, memampukan anda untuk memilih item apa yang sebaiknya diabaikan. Item-item ini termasuk baris yang tersembunyi dan error. Tabel berikut meringkas nilai yang tersedia untuk argument Options dan berimbas pada hasil fungsi perhitungan

Number	Deskripsi
0	Mengabaikan Fungsi SUBTOTAL dan AGREGAT
1	Mengabaikan baris tersembunyi dan Fungsi SUBTOTAL & AGREGAT
2	Mengabaikan error values dan Fungsi SUBTOTAL  & AGREGAT
3	Mengabaikan baris tersembunyi, Error values, dan Fungsi SUBTOTAL & AGREGAT
4	Tidak Mengabaikan
5	Mengabaikan baris tersembunyi
6	Mengabaikan Error Values
7	Mengabaikan Baris tersembunyi dan Error Values

Selamat mencoba..

Read More

Mencari Informasi Menggunakan Fungsi VLOOKUP dan HLOOKUP

Setiap kali anda membuat worksheet yang menyimpan informasi tentang daftar item yang berbeda, seperti produk yang dijual oleh sebuah perusahaan, anda harus memastikan bahwa setidaknya satu kolom dalam daftar yang berisi nilai unik yang membedakan baris tersebut (dan item produk yang diwakili oleh baris) dengan baris lainnya dalam daftar. Menetapkan setiap baris kolom yang berisi nilai unik mengandung arti bahwa anda dapat menghubungkan data dalam satu daftar dengan data dalam daftar yang lain. Misalnya, jika anda menetapkan setiap pelanggan memiliki nomor identifikasi yang unik, anda dapat menyimpan informasi kontak pelanggan dalam satu lembar kerja dan semua pesanan pelanggan dalam worksheet lain. anda kemudian dapat mengasosiasikan pesanan pelanggan dan informasi kontak tanpa harus menulis informasi kontak dalam worksheet setiap kali pelanggan  melakukan pesanan.

misalnya PT. Consolidated Messenger sebuah perusahaan logistik memiliki data seperti pada gambar 1, kolom yang berisi nilai-nilai yang unik, juga disebut sebagai kolom primary key, adalah kolom ShipmentID

Gambar 1. Database Dengan Primary Key

Jika Anda mengetahui kode pengiriman dalam bentuk ShipmentID, tentu tidak akan merasa kesulitan untuk mencari sekitar 20 atau 30 item tentang pengiriman tertentu. Namun, jika Anda memiliki daftar ribuan pengiriman, melihat melalui daftar untuk menemukan sutau pengiriman akan menghabiskan banyak waktu. Untuk mengatasi hal tersebut, Anda bisa menggunakan fungsi LOOKUP.

Fungsi LOOKUP memiliki dua fungsi yaitu Vlookup dan Hlookup. Vlookup digunakan untuk mengetahui nilai pada suatu table secara vertical. Sedangkan Hlookup digunakan untuk mengetahui nilai pada suatu table secara horizontal.

Formula Vlookup =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Formula Hlookup =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Keterangan:

• lookup_value: nilai atau sel referensi yang dijadikan kunci dalam pencarian data
• table_array: tabel atau range yang menyimpan data yang ingin dicari. Range untuk contoh tabel di atas adalah: A2:C4 (tabel pertama - VLOOKUP) dan B1:D3 (tabel dua - HLOOKUP).
• col_index_num: nomor kolom yang ingin diambil nilainya untuk fungsi VLOOKUP. Untuk tabel pertama (VLOOKUP): nomor kolom adalah 2, bila ingin mengambil nilai pada kolom Name. Nomor kolom adalah 3, bila ingin mengambil nilai pada kolom Price.
• row_index_num: nomor baris yang ingin diambil nilainya untuk fungsi HLOOKUP. Untuk tabel dua (HLOOKUP): nomor baris adalah 2, bila ingin mengambil nilai sel pada baris Name. Nomor baris adalah 3, bila ingin mengambil nilai sel pada baris Price.
• range_lookup: Nilai logika TRUE atau FALSE, dimana Anda ingin fungsi VLOOKUP atau HLOOKUP mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan  nilai secara tepat (FALSE).

Fungsi VLOOKUP bekerja sedikit berbeda tergantung pada apakah argumen range_lookup disetel ke TRUE atau FALSE. Daftar berikut merangkum bagaimana fungsi bekerja berdasarkan nilai range_lookup:

1.    Jika argumen range_lookup dibiarkan kosong atau set ke TRUE, dan VLOOKUP tidak menemukan yang sama persis untuk lookup_value, fungsi mengembalikan nilai terbesar yang kurang dari lookup_value.

2.    Jika argumen range_lookup dibiarkan kosong atau set ke TRUE, dan lookup_value lebih kecil dari nilai terkecil di Name Range  akan dihasilkan error # N / A

3.    Jika argumen range_lookup dibiarkan kosong atau set ke TRUE, dan lookup_value lebih besar dari semua nilai dalam  Name Range akan dihasilkan nilai terbesar dalam  name range

4.    Jika argumen range_lookup diatur ke FALSE, dan VLOOKUP tidak menemukan yang sama persis untuk lookup_value, fungsi akan menghasilkan # N / A error.

Sebagai contoh fungsi VLOOKUP, pertimbangkan data berikut:

CustomerID	Customer
CI01	Fabrikam
CI02	Northwind Traders
CI03	Tailspin Toys
CI04	Contoso

Jika formula =VLOOKUP (E3,Table1,2,TRUE) yang digunakan, ketika anda mengetik CI02 dalam sel E3 dan tekan Enter, fungsi VLOOKUP akan mencari kolom pertama dari tabel, untuk menemukan nilai yang sama persis, dan menghasilkan  nilai Northwind Traders di sel F3.

Gambar 2. Penggunaan Vlookup sebagai Key

Misalkan ada data sebagai berikut:

Gambar 3. Contoh Data Vlookup

Berikut adalah langkah-langkah mengetahui nilai atau hasil dengan fungsi Vlookup:

1.      Klik Sel E5 (untuk mengetahui hasil tujuan)

2.      Ketikkan rumus: =VLOOKUP(D5,$I$9:$L$14,2)

3.      Tekan enter, maka muncul hasil tujuan pada E5

4.      Klik G5 (Untuk mengetahui nilai)

5.      Ketikkan rumus =VLOOKUP(D5,$I$9:$L$14,4)

6.      Tekanlah Enter. Maka akan muncul nilai pada Sel G5

Rumus =VLOOKUP(D5,$I$9:$L$14,4) dibuat dengan cara membawa pointer ke sel D5, disampingnya diberi tanda koma, memblok Tabel 1 range (I9:L14) dan menekan tombol fungsi F4, disampingnya diberi tanda koma, diberi angka 4 dan diberi tanda kurung tutup. Tombol fungsi F4 adalah tombol yang mengubah tanda blok sel menjadi Angka $ dan tanda koma, adalah alamat kolom yang akan diketahui nilainya, yaitu kolom jumlah yang berada di kolom 4 Tabel 1. Jangan lupa memberi tanda kurung dan tanda koma. Untuk mengetahui hasil tujuan pada sel berikutnya, anda tidak perlu mengetik rumus kembali. Arahkan pointer ke ujung bawah sel G5 hingga berubah menjadi lambang plus (+). Draglah mouse kebawah beberapa sel kemudian lepaskan tombol mouse.

Gambar 4. Hasil Data Vlookup

Selanjutnya akan dijelaskan langkah-langkah mengetahui nilai atau hasil dengan fungsi HLOOKUP. Misalkan terdapat data sebagai berikut:

Gambar 5. Contoh Data Hlookup

1.      Klik Sel E11 (untuk mengetahui hasil tujuan)

2.      Ketikkan rumus =HLOOKUP(D11,$B$3:$H$6,2)

3.      Tekan enter. Dan akan muncul hasil tujuan pada sel E11

4.      Klik Sel G11 (untuk mengetahui nilai tiket)

5.      Ketikkan rumus =HLOOKUP(D11,$B$3:$H$6,2)

6.      Tekan Enter, dan akan muncul nilai tiket pada sel G11

Rumus =HLOOKUP(D11,$B$3:$H$6,2) dibuat dengan cara membawa pointer ke sel D5, disampingnya diberi tanda koma, memblok Tabel 1 range (B3:H6) dan menekan tombol fungsi F4, disampingnya diberi tanda koma, diberi angka 2 dan diberi kurung tutup. Tombol fungsi F4 adalah tombol yang mengubah tanda blok sel menjadi angka $ (dollar), selanjutnya angka 2 disamping angka dolar dan tanda koma adalah alamat baris yang akan diketahui nilainya, yaitu baris Fakultas yang berada di baris 2   Tabel 1. Jangan lupa memberi tanda kurung dan tanda koma, pada rumus

Gambar 6. Hasil  Data Hlookup

Read More