Pivot Table Dasar

Pivot Table Dasar

    • Pada tutorial ini akan ditunjukkan langkah demi langkah pembuatan dasar PivotTable. Contoh file disediakan sehingga memudahkan Anda untuk mengikuti instruksi yang ada.

      (Catatan : Anda bisa mengklik tiap gambar untuk melihat ukuran aslinya.)

      File Latihan

      • Download file contoh transaksi.xlsx dari URL berikut : 
      • http://adf.ly/MBPUn
      • Simpan file tersebut pada lokasi folder yang Anda inginkan.


      Pembuatan PivotTable

      • Jalankan program Excel 2007 dan buka file latihan yang telah Anda download tersebut.

        Catatan : File ini merupakan file contoh fiktif data-data transaksi yang terjadi pada suatu minimarket, dengan produk buah-buahan, sayur-sayuran dan makanan & minuman.
      • Buka sheet transaksi, dimana datanya terlihat seperti pada gambar berikut ini.

      • Sekarang pilih range data A1:F43680 atau tekan tombol CTRL + SHIFT + *.
      • Klik tab Insert pada Ribbon, pilih menu PivotTable | Insert PivotTable.

      • Pada dialog yang muncul, pilih New Worksheet, klik tombol OK.

      • Sheet baru akan muncul disertai suatu kotak / placeholder PivotTable (PivotTable Box).

        Selain itu terdapat panel daftar field (PivotTable Field List) pada posisi sebelah kanan worksheet. Terlihat pada daftar tersebut 6 field heading dari range data yang kita pilih sebelumnya.



      • Pada bagian bawah panel terdapat 4 kotak area, dari tiap kotak tersebut dapat kita tambahkan field-field yang terdapat pada field list.

        Adapun fungsi dari 4 kotak tersebut adalah sebagai berikut :
        • Report Filter : pada kotak ini field akan digunakan sebagai filter yang mempengaruhi hasil data pada PivotTable namun tidak akan terlihat sebagai isi dari PivotTable itu sendiri.
        • Column Labels : data dari field akan ditempatkan pada bagian kolom dari table dengan level sesuai urutan susunan pada area ini.
        • Row Labels : data dari field akan ditempatkan  pada bagian baris  dari table dengan level sesuai urutan susunan pada area ini.
        • Values : nilai field yang terdapat pada kotak ini akan dijadikan sebagai basis perhitungan summary. Tipe summary yang bisa digunakan adaah count, sum, average, dan lain-lain.
      • Mari sekarang kita coba berbagai kombinasi penempatan field dalam empat kotak area tersebut.

        Susunlah layout field dengan urutan berikut :
        • Field nama_kategori ke kotak Column Labels.
        • Field nama_cabang ke kotak  Row Labels.
        • Field jumlah_unit ke kotak Values.

          Perhatikan pada saat ditempatkan di kotak Values, nama field jumlah_unitakan berubah menjadi Sum of jumlah_unit. Ini menandakan bahwa field adalah kalkulasi sum (penjumlahan) dari nilai-nilai field jumlah_unit.

      • Perhatikan hasil pengaturan ini pada area PivotTable.  Area ini akan berisi suatu table dengan grouping field nama_cabang pada bagian baris,  fieldnama_kategori pada kolom.

        Sedangkan nilai total jumlah_unit ditempatkan pada cell-cell hasil perpotongan item grouping baris dan kolom tersebut.



        Salah satu contoh perpotongan adalah total jumlah unit yang terjual pada PHI Mini Market - Jakarta Pusat 01 dan kategori Makanan & Minuman, adalah sebesar 525.138 unit.

      Menambahkan Tipe Summary Baru

      • Sekarang mari kita tambahkan field jumlah_unit kembali ke kotak Value. Akan muncul field baru dengan nama Sum of jumlah_unit2.

      • Kita akan mendapatkan satu kolom perhitungan baru yang sama dengan hasil sebelumnya. Tentunya bukan ini yang kita inginkan.

      • Mari kita kembali ke area Values, dan klik tombol panah bawah pada field Sum of jumlah_unit2. Pilih item Value Field Settings.

      • Pada dialog yang muncul rubah Sum menjadi Count.  Perhatikan nama field akan berubah menjadi Count of jumlah_unit2.

      • Klik tombol OK.
      • Pada area PivotTable, kita sekarang mendapatkan dua summary, yakni :
        • nilai total unit penjualan yang terjadi (sum)
        • jumlah transaksi yang terjadi (count).

Penggunaan Fungsi Index Match pada MS Excel 2007

Penggunaan Fungsi Index Match pada MS Excel 2007

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan terlibat dalam dua kondisi berikut :
  • Melakukan pencarian dari suatu nilai terhadap range data tertentu (referensi).
  • Pencarian terhadap referensi tersebut harus cukup dinamis, ini dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.
Untuk keperluan hal tersebut, kita dapat melakukannya dengan mudah dari penggunaan dua fungsi Excel, yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.


Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :
  • array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
  • row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
  • column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index
Dengan formula "=INDEX(B3:B8, 4, 2)", kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari "navigasi cell" pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :
  • lookup_value : adalah nilai yang ingin dicari pada lookup_array.
  • lookup_array : adalah range data dari suatu kolom ataupun baris.
  • match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
    • 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
    • 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
    • -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.


Gambar 3. Ilustrasi Contoh Penggunaan Match Type - Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.


Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type - Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom,

atau...

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.
Pencarian Multi Variabel dengan Match dan Index Excel

Pencarian Multi Variabel dengan Match dan Index Excel

Pertanyaan


Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:
  • Kolom 1 : Berupa Kode/ID
  • Kolom 2 : Berupa tipe konsumen (reseller/langsung)
  • Kolom 3 : Harga barang.
di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:
  • Kolom 1: Tanggal
  • Kolom 2: Kode/ID
  • Kolom 3: Tipe konsumen
Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.
Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Jawab

Solusi untuk masalah ini adalah fungsi yang digunakan adalah Index dan Match, tetapi dengan pendekatan Array Formula. Berikut adalah rincian jawabannya.

Untuk menjawab pertanyaan ini, kami menggunakan file yang dapat Anda download pada alamat http://adf.ly/MBQfJ. Workbooknya akan terlihat sebagai berikut.

Workbook ini memiliki sheet "data" dan "master_harga" (klik pada tab dengan nama sheet tersebut untuk melihat rincian dari tiap sheet).



Sekarang kita akan mengisi kolom Harga yang didapatkan dari pencarian kombinasi kolom Kode / ID dan Tipe ke sheet master_harga. (klik gambar untuk memperbesar)


Berikut adalah langkah-langkah untuk melakukan kombinasi pencarian tersebut :
  1. Jalankan aplikasi Microsoft Excel 2007 atau ke atas.
  2. Buka file yang Anda download tersebut.
  3. Pada sheet data, klik cell pada alamat D2 dan masukkan rumus di bawah ini. Perhatikan bahwa kita menggunakan operator dan ( & ) untuk menggabungkan cell B dan C (kolom Kode / ID dan Tipe).

    =INDEX(master_harga!$C$2:$C$11,MATCH(B2&C2,master_harga!$A$2:$A$11&master_harga!$B$2:$B$11))

    Tekan CTRL + SHIFT + ENTER untuk menyelesaikan formula tersebut (bukan Enter seperti biasanya).

  4. Hasilnya akan tampak seperti pada gambar screenshot sebagai berikut. Perhatikan bahwa rumus yang kita masukkan diapit oleh kurung kurawal {}. Ini pertanda bahwa formula tersebut adalah array formula.
  5. Copy rumus tersebut ke kolom Harga yang masih kosong.

  6. Selesai.

Referensi

Mengaktifkan Solver di MS Excel 2007

Mengaktifkan Solver di MS Excel 2007

Solver adalah fasilitas di Excel yang terdiri dari kumpulan fungsi / perintah untuk menemukan pemecahan yang optimal dari suatu permasalahan (dalam suatu batasan dan formulasi).


Contoh worksheet dari penggunaan Solver ini disertakan dalam distribusi Microsoft Excel 2007, lokasi dan nama filenya adalah :
  • C:\Program Files\Microsoft Office\Office12\SAMPLES\SOLVSAMP.XLS

Akses Menu Solver

  1. Solver dapat digunakan melalui menu tab "Data" | group "Analysis" | "Solver".

  2. Apabila menu tersebut belum ada maka lakukan petunjuk berikut ini untuk mengaktifkan menu solver :
    1. Klik tombol "Office Button"

    2. Klik tombol "Excel Options"

    3. Pada dialog "Excel Options", klik "Add-Ins" dan pilih "Solver Add-in" dari daftar aplikasi add-in yang tidak aktif - Inactive Application Add-Ins.

    4. Klik tombol "Go" pada bagian "Manage Excel Add-Ins".

    5. Pastikan "Solver Add-In" terpilih. Klik tombol "OK".

Contoh Penggunaan Fungsi IF

Contoh Penggunaan Fungsi IF

Pendahuluan

SUMIF adalah fungsi penjumlahan nilai-nilai yang terdapat pada suatu range data, namun nilai yang diambil adalah berdasarkan pada satu pengkondisian atau filter yang ditentukan oleh kita.


Syntax dari fungsi SUMIF adalah sebagai berikut :

SUMIF(range, criteria, [sum_range])

Keterangan :
  • range : adalah range dari cell-cell yang ingin kita evaluasi berdasarkan kriteria yang akan kita tentukan.
  • criteria : kondisi atau aturan filter yang ingin kita gunakan.
  • sum_range : jika ini disebutkan maka range ini yang akan jadi acuan untuk mengambil nilai, tetapi posisinya sesuai dengan kolom - baris dari range yang terfilter.

Contoh Penggunaan

  1. Sebagai contoh, kita memiliki data range seperti terlihat di bawah ini  (A2:D10). Yang ingin kita lakukan adalah mengambil total dari Kacang saja kemudian ditempatkan hasilnya pada D11.

  2. Tempatkan cursor pada cell D11 dan ketikkan formula berikut :

    =SUMIF(D3:D10,"=Kacang",C3:C10)

    ini artinya dari range kriteria dari D3:D10, kita mengambil yang nilainya Kacang saja. Setelah itu ambil range nilai yang akan dijumlah yaitu C1:C10.

  3. Hasilnya akan terlihat sebagai berikut. Nilai 15 didapatkan sebagai total jumlah untuk Kacang saja.

Fungsi IF pada EXCEL

Fungsi IF pada EXCEL

Pada Excel 2007 dan versi sebelumnya, fungsi IF 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.
If sebagai fungsi keputusan dari suatu kondisi


Syntax dasar dari fungsi IF adalah sebagai berikut

IF(logical_test_value, value_if_true, value_if_false)

Contoh Penggunaan

Misalkan kita memiliki contoh data penjualan sederhaan berikut ini. Total Bayar pada kolom "Total Bayar" (G) akan diisi dengan potongan diskon 10% dari "Total" jika pada kolom "Diskon" terdapat nilai "Y".


Pemecahannya untuk cell G2 adalah sebagai berikut :

=F2*IF(C2="Y",0.9,1)

Penjelasan :
  • Nilai G2 (Total Bayar) didapat dari hasil perkalian F2 (Total) dengan 0.9 (diskon 10%) jika nilai C2 (Diskon) adalah "Y".
  • Jika nilai C2 (Diskon) bukan "Y" maka tidak ada diskon, dan nilai G2 sama dengan nilai F2.
Pengguna'an ISBLANK pada EXCEL

Pengguna'an ISBLANK pada EXCEL

Fungsi ISBLANK digunakan untuk melakukan pengecekan apakah suatu cell berisi nilai kosong atau null. Jika memang kosong atau null, maka akan mengembalikan nilai TRUE (BENAR), sebaliknya mengembalikan FALSE (SALAH).


Sebagai contoh, pada Gambar.1 berikut cell C3 berisi rumus =ISBLANK(B3). Rumus ini digunakan untuk melakukan pengecekan apakah cell B3 kosong. Ternyata cell B3 berisi teks "Satu", dengan demikian tidak kosong dan fungsi ISBLANK(B3) akan mengembalikan nilai FALSE.

Gambar.1 Contoh Pengecekan ISBLANK yang mengembalikan nilai FALSE


Sedangkan pada Gambar.2 berikut cell C4 berisi rumus =ISBLANK(B4). Rumus ini digunakan untuk melakukan pengecekan apakah cell B4 kosong. Dan ternyata hasilnya memang kosong. Dengan demikian fungsi ISBLANK(B4) akan mengembalikan nilai TRUE.

Gambar.2 Contoh Pengecekan ISBLANK yang mengembalikan nilai TRUE

Demikian contoh penggunaan sederhana dari ISBLANK. Semoga bisa bermanfaat bagi para pengunjung sekalian. Saran, kritik dan komentar apapun dengan senang hati kami terima demi kemajuan situs ini. Terima kasih.