http://ketutmoon.blogspot.com

Tutorial Rumus Microsoft Excel Lengkap Dengan Contoh dan Gambar

Ilustrasi Mikir Rumus Excel

Jika Anda sering menggunakan aplikasi Microsoft Excel baik yang 2003, 2007 maupun yang versi terbaru, maka pasti Anda sudah tidak asing lagi menggunakan rumus-rumus Microsoft Excel yang ada dalam aplikasi tersebut. Nah sekarang pertanyaannya adalah bagaimana jika Saya seorang pemula?, bagaimana jika Saya masih belum pernah Belajar Komputer?, bagaimana jika Saya masih belum tau sama sekali membuka aplikasi pengolahan data Microsoft Excel seperti yang akan dijelaskan sekarang ini.? Tidak perlu khawatir kawan, sekarang Anda juga akan bisa mengaplikasikan rumus-rumus dalam Microsoft Excel karena panduan Belajar Microsoft Excel ini disertai dengan contoh dan gambar yang akan memudahkan Anda untuk mempraktekkannya. Baiklah tanpa harus panjang lebar, sekarang langsung Kita masuk ke materi pembahasan mengenai rumus Microsoft Excel, simak dengan baik ya kawan.

Microsoft Office Excel memang dikenal dengan penggunaan rumus-rumus atau formula dalam lembar kerjanya. Penggunaan rumus yang efektif akan memudahkan Anda dalam membuat laporan pekerjaan dengan menggunakan MS Excel. Formula atau rumus MS Excel adalah keunggulan tersendiri untuk aplikasi ini, dengan kemampuannya dalam mengolah data melalui perhitungan matematis yang sangat beragam fungsinya.

Setiap rumus di dalam MS Excel memilik fungsi tersendiri. Oleh karena itu Anda harus benar-benar memahami masing-masing fungsi dasarnya agar penggunaan rumus dalam MS Excel bisa lebih maksimal dan efisien. Jika telah paham fungsi dasarnya, Saya rasa Anda bisa berkreasi dalam meracik rumus yang sesuai dengan kebutuhan data Anda.

Cara Menulis Rumus Excel

Sebelum mengenal  rumus-rumus excel 2007, hal pertama yang perlu Anda ketahui adalah bagaimana menuliskan rumus excel agar bisa benar. 

Cara 1: Menulis rumus dengan mengetikkan angka langsung

1.      Letakkan penunjuk sel pada sel tempat hasil rumus akan ditampilkan 

2.      Pada formula bar, ketikkan = 5000000+3500000, lalu tekan tombol enter.

Note : Menulis rumus dengan cara ini cukup mudah kalau rumusnya sederhana dan pendek serta angkanya tetap.

Cara 2: Menulis rumus dengan menggunakan alamat sel

1.      Letakkan penunjuk sel pada sel tempat hasil rumus ditampilkan 

2.      Pada formula bar, ketikkan = C4+C5, lalu tekan tombol enter.

Note : Menulis rumus dengan cara ini sangat bermanfaat jika datanya sering berubah.

Cara 3: Menulis rumus dengan bantuan mouse

1.      Letakkan penunjuk sel pada sel tempat hasil rumus akan ditampilkan

2.      Ketikkan = , kemudian pilih dan klik sel C4

3.      Ketik +, kemudian pilih dan klik sel C5

4.      Tekan tombol enter

Note : Menulis rumus dengan cara ini sangat dianjurkan karena memperkecil kemungkinan salah ketik alamat sel.

Nah... bagaimana mudah bukan cara menuliskan rumus excel, jika Anda sudah mahir dalam menuliskannya sekarang mari Kita lanjutkan pembahasannya. 

Operator Matematika yang Sering Digunakan (Rumus Dasar)

Rumus merupakan bagian terpenting dari Program Excel ini, karena setiap tabel dan dokumen yang Kita ketik akan selalu berhubungan dengan rumus dan fungsi. MS Excel memang sangat diunggulkan dalam menampung data-data yang bersifat numerik. Maka dari itu, di dalam MS Excel pun terdapat Operator dasar Matematika dan ini biasanya disebut sebagai rumus standar. Operator matematika yang akan sering digunakan dalam Rumus Microsoft Excel adalah sebagai berikut:

No	Operator	Fungsi	Contoh Penggunaan
1	+ (ikon plus)	Penjumlahan	=A1+A2
2	- (ikon minus)	Pengurangan	=A3-A4
3	* (ikon bintang)	Perkalian	=A5*A6
4	/ (ikon garis miring)	Pembagian	=A7/A8
5	^ (ikon caret)	Pangkat	=A9^2 (angka berapapun yang berada di cell A9 akan dipangkat 2
6	% (ikon persen)	Prosentase	=A1*10% (gunakan sesuai dengan kebutuhan)

Tabel Operator Matematika (Rumus Dasar Excel)

Tidak usah bingung, sekarang perhatikan gambar tabel yang ada dibawah ini.

Penjelasan Gambar Tabel Diatas :

1.      Mengisi Kolom Diskon

Untuk mengisi kolom diskon berikut adalah langkah-langkah yang harus dilakukan. 

o    Klik pada kolom yang akan diisikan Diskon Rp. (pada contoh ini sel yang akan digunakan adalah G6, silahkan aktifkan kolom G6)

o    Awali dengan menuliskan "=" (setiap rumus excel harus diawali menggunakan operator = tanpa tanda kutip agar dikenali sebagai fungsi)

o    Kalikan kolom E6 dengan F6 untuk mengetahui hasil persennya, bentuk penulisannya adalah =E6*F6 

o    Kemudian tekan enter untuk mengetahui hasilnya, maka akan didapat angka 30,000 pada kolom G6 tadi (30,000 adalah 20% dari 150,000)

o    Untuk menghitung rumus Diskon Rp. selanjutnya, silahkan copy format dengan cara drag pada rumus yang sudah selesai dikerjakan kemudian tarik hingga kolom G15.

Tips : Anda bisa langsung klik kolom yang akan dikalikan tanpa harus susah payah mengetik kolom yang akan dihitung satu persatu.

2.      Mengisi Total Bayar

Nah setelah selesai mengisi kolom Diskon Rp. sekarang lanjutkan dengan mengisi kolom Total Biaya.

o    Klik pada kolom yang akan diisikan Total Bayar (pada contoh ini sel yang akan digunakan adalah H6, silahkan aktifkan kolom H6)

o    Awali dengan menuliskan "=" (setiap rumus excel harus diawali menggunakan operator = tanpa tanda kutip agar dikenali sebagai fungsi)

o    Pada kolom H6 Kurangkan kolom Biaya (E6) dengan kolom Diskon Rp. (G6)  untuk mengetahui hasil pengurangannya, bentuk penulisannya adalah =E6-G6 

o    Kemudian tekan enter untuk mengetahui hasilnya, maka akan didapat hasil 120,000 pada kolom Total Bayar yang pertama.

o    Untuk menghitung rumus Total Bayar selanjutnya, silahkan copy format dengan cara drag pada rumus yang sudah selesai dikerjakan kemudian tarik hingga kolom H15.

Tips : Untuk melanjutkan mengisi kolom jumlah di bawahnya (kolom H7 dst), kita cukup memblok cell yang telah berisi rumus operator perkalian, arahkan kursor ke pojok kanan bawah cell/kolom. Tunggu hingga kursor berubah menjadi ikon (+). Setelah itu, click dan tahan lalu drag/geser ke bawah. Dengan demikian, kita telah meng-copy rumus tadi dan telah disesuaikan perubahannya untuk kolom yang akan dikalikan lagi.

Mengenal Fungsi atau Rumus Microsoft Excel

Setelah mengenal Tipe Data Microsoft Excel, dan bisa memulai menggunakan aplikasi excel sekarang Kita coba bahas rumus excel yang sering digunakan dalam dunia kerja atau administrasi perkantoran.

1. Fungsi Average

Fungsi ini digunakan untuk mencari nilai rata-rata dari sekumpulan data (range). Bentuk umum penulisannya adalah ;

=AVERAGE(number1, number2,…), dimana number1, number2, dan seterusnya adalah range data yang akan dicari nilai rata-ratanya. Misalnya untuk mengisi nilai rata-rata dari range data E8 sampai G8, maka rumusnya adalah =AVERAGE(E8:G8) kemudian tekan tombol Enter.

2. Fungsi Sum

Fungsi SUM digunakan untuk menjumlahkan sekumpulan data pada suatu range. Bentuk umum penulisan fungsi ini adalah =SUM(number1, number2,…). Dimana number1, number2 dan seterusnya adalah range data yang akan dijumlahkan. Misalnya untuk menjumlahkan range data E8 sampai G8, maka rumusnya adalah =SUM(E8:G8) kemudian tekan tombol Enter.

3. Fungsi Max

Fungsi ini digunakan untuk mencari nilai tertinggi dari sekumpulan data (range). Bentuk umum penulisannya adalah ;

=MAX(number1, number2,…), dimana number1, number2, dan seterusnya adalah range data (numerik) yang akan dicari nilai tertingginya. Misalnya untuk mencari nilai maksimal dari range data E8 sampai G8, maka rumusnya adalah =MAX(E8:G8) kemudian tekan tombol Enter.

4. Fungsi Min

Fungsi Min adalah kebalikan dari fungsi Max, jika fungsi Max untuk mencari nilai tertinggi atau maksimal maka Min adalah untuk mencari nilai terkecil atau minimum dari sekumpulan data numerik. Bentuk umum penulisannya adalah ; 

=MIN(number1, number2,…), dimana number1, number2, dan seterusnya adalah range data (numerik) yang akan dicari nilai tertingginya. Sebagai contoh untuk mencari nilai terendah dari range data E8 sampai G8, maka rumusnya adalah =MIN(E8:G8) kemudian tekan tombol Enter.

5. Fungsi Count

Fungsi Count digunakan untuk menghitung jumlah data dari suatu range yang kita pilih. Bentuk umum penulisannya adalah ; 

=COUNT(number1, number2,…), dimana number1, number2, dan seterusnya adalah range data (numerik) yang akan dihitung jumlah datanya. Sebagai contoh untuk menghitung jmlah data dari range data E8 sampai G8, maka rumusnya adalah =COUNT(E8:G8) kemudian tekan tombol Enter.

6. Fungsi Logika IF

Fungsi ini digunakan jika data yang dimasukkan mempunyai kondisi tertentu. Misalnya, jika nilai sel A1=1, maka hasilnya 2, jika tidak, maka akan bernilai 0. Biasanya fungsi ini dibantu oleh operator relasi (pembanding) seperti berikut:

Lambang Fungsi

= Sama dengan

< Lebih kecil dari

> Lebih besar dari

<= Lebih kecil atau sama dengan

>= Lebih besar atau sama dengan

<> Tidak sama dengan

Bentuk umum penulisan fungsi ini adalah ;

=IF(logical_test,value_if_true,value_if_false), artinya kalau ekspresi logika (logical_test) bernilai benar, maka perintah pada value_if_true akan dilaksanakan, jika salah, maka perintah pada value_if_false yang akan dilaksanakan. Perhatikan contoh berikut:

Kolom keterangan diisi dengan ketentuan, jika kolom TANDA sama dengan K, maka keterangannya KAWIN, jika tidak, maka keterangan berisi TIDAK KAWIN. Ini dapat diselesaikan dengan rumus =IF(C3=”K”, “KAWIN”,”TIDAK KAWIN”). Pada rumus diatas Kita lihat bahwa jika datanya bertipe Teks/alpha numerik harus diapit oleh tanda kutip dua, lain halnya dengan tipe data numerik, tidak boleh menggunakan tanda kutip. Nah sekarang bagaimana jika ada beberapa kondisi/bertingkat (IF Bertingkat) seperti pada kasus nilai mahasiswa. Tidak perlu khawatir, langsung saja perhatikan gambar diatas pada tabel sebelah kanan. =IF(H3>=81,"A",IF(H3>=66,"B",IF(H3>=56,"C",IF(H3>40,"D","E")))), hal yang sama juga bisa dilakukan untuk mengisi kolom keterangan, dengan ketentuan, Jika nilai hurufnya sama dengan “A” maka keterangan “SANGAT MEMUASKAN, jika “B” maka “MEMUASKAN”, jika “C”, maka “CUKUP”, dan jika “D” maka bernilai “KURANG”, selain dari itu, maka bernilai “GAGAL”. Nah sekarang sudah paham kan bagaimana cara menggunakan fungsi IF, sekarang langsung dipraktekkan deh supaya tidak lupa. Semoga Tutorial Belajar Microsoft Excel kali ini bisa membantu rekan-rekan sekalian, jika ada yang perlu ditanyakan silahkan hubungi Saya melalui Email, Google+, Facebook dll

Tutorial: Analisis data PivotTable menggunakan Model Data di Excel 2013

Dengan waktu kurang dari satu jam, Anda bisa menyusun laporan PivotTable di Excel yang menggabungkan data dari beberapa tabel. Bagian pertama langkah tutorial ini menjelaskan tentang impor dan eksplorasi data. Di pertengahan, Anda akan menggunakan add-in Power Pivot untuk memperbaiki model data yang ada di dalam laporan, mempelajari cara menambahkan perhitungan, hierarki, dan optimalisasi untuk pelaporan Power View.

Mari mulai dengan mengimpor beberapa data.

1.      Unduh contoh data (ContosoV2) untuk tutorial ini. Lihat Mendapatkan contoh data untuk DAX dan Model Data untuk penjelasan selengkapnya. Ekstrak dan simpan file data di lokasi yang mudah diakses, seperti Unduhan atau Dokumen Saya.

2.      Di Excel, buka buku kerja kosong.

3.      Klik Data > Dapatkan Data Eksternal > Dari Access.

4.      Masuk ke folder yang berisi file contoh data dan pilih ContosoSales.

5.      Klik Buka. Karena Anda tersambung ke file database yang berisi beberapa tabel, dialog Pilih Tabel akan muncul sehingga Anda bisa memilih tabel mana yang akan diimpor.

6.      Di Pilih Tabel, centang Aktifkan pilihan beberapa tabel.

7.      Pilih semua tabel dan klik OK.

8.      Di Impor Data, klik Laporan PivotTable dan klik OK.

CATATAN   Anda mungkin belum menyadarinya, tapi Anda baru saja membuat model data. Model merupakan lapisan integrasi data yang dibuat secara otomatis ketika Anda mengimpor atau bekerja dengan beberapa tabel secara bersamaan dalam laporan PivotTable yang sama.

Model sebagian besar transparan dalam Excel, tapi Anda bisa menampilkan dan mengubahnya langsung menggunakan add-in Power Pivot . Dalam Excel, kehadiran model data nampak jelas ketika Anda melihat koleksi tabel dalam daftar Bidang PivotTable. Ada beberapa cara untuk membuat model. Lihat Membuat Model Data di Excel untuk selengkapnya.

Menjelajahi data menggunakan PivotTable

Menjelajahi data itu mudah ketika Anda menyeret bidang ke area Nilai, Kolom, dan Baris pada daftar bidang PivotTable.

1.      Di daftar bidang, gulir ke bawah hingga Anda menemukan tabel FactSales.

2.      Klik SalesAmount. Karena merupakan data numerik, Excel secara otomatis menempatkannya SalesAmount di area Nilai.

3.      Di DimDate, seret CalendarYear ke kolom.

4.      Di DimProductSubcategory, seret ProductSubcategoryName ke Baris.

5.      Di DimProduct, seret BrandName ke Baris, tempatkan di bawah subkategori.

PivotTable Anda akan terlihat sama dengan layar berikut ini.

Dengan usaha minimal, Anda sekarang memiliki dasar PivotTable yang mencakup bidang dari empat tabel yang berbeda. Apa yang membuat tugas ini begitu sederhana adalah hubungan yang telah ada sebelumnya di antara tabel. Karena hubungan tabel sudah ada dalam sumber, dan karena Anda telah mengimpor semua tabel dalam operasi tunggal, Excel bisa membuat ulang hubungan di dalam model.

Tapi bagaimana jika data asli berasal dari sumber yang berbeda, atau apakah nantinya akan diimpor? Biasanya, Anda bisa menggabungkan data baru dengan membuat hubungan berdasarkan kolom yang cocok. Dalam langkah berikutnya, Anda akan mengimpor tabel tambahan dan mempelajari persyaratan dan langkah-langkah untuk membuat hubungan baru.

Menambahkan beberapa tabel

Untuk mempelajari cara mempersiapkan hubungan tabel Anda harus memiliki beberapa tabel tambahan yang tidak tersambung untuk digunakan. Pada langkah ini, Anda akan mendapatkan data yang tersisa yang digunakan dalam tutorial ini dengan mengimpor satu file database tambahan dan menempelkan data dari dua buku kerja lainnya.

Menambahkan kategori produk

1.      Di buku kerja, bukalah lembar baru. Anda akan menggunakannya untuk menyimpan data tambahan.

2.      Klik Data > Dapatkan Data Eksternal > Dari Access.

3.      Buka folder yang berisi file contoh data dan pilih ProductCategories. Klik Buka.

4.      Di Impor Data, pilih Tabel, dan klik OK.

Menambahkan data geografi

1.      Menyisipkan lembar lainnya.

2.      Dari file contoh data, buka Geography.xlsx, tempatkan kursor pada A1, lalu tekan Ctrl-Shift-End untuk memilih semua data.

3.      Salin data ke clipboard.

4.      Tempelkan data ke dalam lembar kosong yang baru Anda tambahkan.

5.      Klik Format sebagai Tabel, dengan memilih gaya apa pun. Memformat data sebagai tabel memungkinkan Anda menamainya, yang akan berguna ketika Anda menetapkan hubungan dalam langkah berikutnya.

6.      Di Format Sebagai Tabel, pastikan bahwa Tabel Saya memiliki header telah dipilih. Klik OK.

7.      Namai tabel Geography. Dalam Alat Tabel > Desain, ketikkan Geography dalam Nama Tabel.

8.      Tutup Geography.xlsx untuk mengosongkannya dari ruang kerja Anda.

Menambahkan data tersimpan

·         Ulangi langkah sebelumnya untuk file Stores.xlsx, tempelkan kontennya ke dalam lembar kosong. Namai tabel Stores.

Anda sekarang seharusnya memiliki empat lembar. Sheet1 berisi PivotTable, Sheet2 berisi ProductCategories, Sheet3 berisi Geography, dan Sheet4 berisi Stores. Karena Anda telah menamai setiap tabel, langkah berikutnya, membuat hubungan, akan lebih mudah.

Menggunakan bidang dari tabel yang baru diimpor

Anda bisa memulai secepatnya menggunakan bidang dari tabel yang baru Anda impor. Jika Excel tidak bisa menentukan cara menggabungkan bidang ke dalam laporan PivotTable, Anda akan diminta untuk membuat hubungan tabel yang mengaitkan tabel baru dengan tabel telah menjadi bagian dari model.

1.      Di sebelah atas Bidang PivotTable, klik Semua untuk menampilkan daftar lengkap dari tabel yang tersedia.

2.      Gulir ke bagian bawah daftar. Di sanalah Anda akan menemukan tabel baru yang baru saja Anda tambahkan.

3.      Perluas Stores.

4.      Seret StoreName ke area Filter.

5.      Perhatikan bahwa Excel meminta Anda untuk membuat hubungan. Pemberitahuan ini muncul karena Anda menggunakan bidang dari tabel yang tidak terkait ke model.

6.      Klik Buat untuk membuka dialog Buat Hubungan.

7.      Di Tabel, pilih FactSales. Dalam contoh data yang Anda gunakan, FactSales berisi detail informasi penjualan dan biaya mengenai bisnis Contoso, serta tombol ke tabel lain, termasuk kode toko yang juga disajikan dalam file Stores.xlsx yang Anda impor dalam langkah sebelumnya.

8.      Dalam Kolom (Asing), pilih StoreKey.

9.      Dalam Tabel Terkait, pilih Stores.

10.  Dalam Kolom Terkait (Utama), pilih StoreKey.

11.  Klik OK.

Di balik layar, Excel menyusun Model Data yang bisa digunakan di seluruh buku kerja dalam jumlah laporan PivotTable, PivotChart, atau Power View. Hal yang penting untuk model ini adalah bahwa hubungan tabel menentukan navigasi dan perhitungan jalur yang digunakan dalam laporan PivotTable. Dalam tugas berikutnya, Anda akan membuat hubungan secara manual untuk menyambungkan data yang baru saja Anda impor.

Menambahkan hubungan

Anda bisa membuat hubungan tabel secara sistematis untuk semua tabel baru yang telah Anda impor. Jika Anda berbagi buku kerja dengan kolega Anda, memiliki hubungan yang sudah ditentukan sebelumnya akan lebih baik jika mereka tidak mengetahui datanya sebaik Anda.

Ketika membuat hubungan secara manual, Anda akan bekerja dengan dua tabel sekaligus. Untuk setiap tabel, Anda akan memilih kolom yang memberi tahu Excel cara mencari baris terkait di dalam tabel lainnya.

Menghubungkan ProductSubcategory ke ProductCategory

1.      Di Excel, klik Data > Hubungan > Baru.

2.      Di Tabel, pilih DimProductSubcategory.

3.      Di Kolom (Asing), pilih ProductCategoryKey.

4.      Di Tabel Terkait, pilih Table_ProductCategory.accdb.

5.      Di Kolom Terkait (Utama), pilih ProductCategoryKey.

6.      Klik OK.

7.      Tutup dialog Kelola Hubungan.

Menambahkan kategori ke PivotTable

Walaupun Model Data telah diperbarui untuk menyertakan tabel tambahan dan hubungan, PivotTable belum menggunakannya. Dalam tugas ini, Anda akan menambahkan ProductCategory ke daftar Bidang PivotTable.

1.      Di Bidang PivotTable, klik Semua untuk memperlihatkan tabel yang sudah ada dalam Model Data.

2.      Gulir ke bagian bawah daftar.

3.      Di area Baris, hapus BrandName.

4.      Perluas Table_DimProductCategories.accdb.

5.      Seret ProductCategoryName ke area Baris, tempatkan di atas ProductSubcategory.

6.      Dalam Bidang PivotTable, klik Aktif untuk memverifikasi bahwa tabel yang baru saja Anda gunakan sekarang bisa digunakan secara aktif dalam PivotTable.

Checkpoint: Tinjau apa yang telah Anda pelajari

Anda sekarang memiliki PivotTable yang mencakup data dari beberapa tabel, beberapa di antaranya Anda impor dalam langkah selanjutnya. Untuk menggabungkan data ini sekaligus, Anda harus membuat hubungan tabel yang akan digunakan Excel untuk mengkorelasikan baris. Anda telah mengetahui bahwa memiliki kolom yang menyediakan data yang cocok sangat penting untuk mencari baris terkait. Dalam file contoh data, semua tabel meliputi kolom yang bisa digunakan untuk tujuan ini.

Walaupun PivotTable fungsional, Anda mungkin telah menyadari ada beberapa hal yang bisa ditingkatkan. Daftar Bidang PivotTable tampaknya memiliki tabel tambahan (DimEntity) dan kolom (ETLLoadID) yang tidak terkait dengan bisnis Contoso. Dan kami masih belum mengintegrasikan data Geography.

Berikutnya: Menampilkan dan memperluas model Anda dengan Power Pivot

Dalam seri tugas berikutnya, Anda akan menggunakan add-in PowerPivot di Microsoft Excel 2013 untuk memperluas model. Anda akan menemukan bahwa Anda bisa membuat hubungan lebih mudah dengan menggunakan add-in Tampilan Diagram yang sediakan. Anda juga bisa menggunakan add-in untuk membuat perhitungan dan hierarki, menyembunyikan item yang tidak seharusnya muncul dalam daftar bidang, dan mengoptimalkan data untuk laporan tambahan.

CATATAN    Add-in PowerPivot di Microsoft Excel 2013 tersedia di Office Professional Plus. LihatPowerPivot di Microsoft Excel 2013 Add-in untuk informasi selengkapnya.

Tambahkan Power Pivot ke pita Excel dengan mengaktifkan add-in Power Pivot.

1.      Buka File > Opsi > Add-In.

2.      Dalam kotak Kelola, klik COM Add-in> Ke.

1.      Centang kotak Microsoft Office PowerPivot di Microsoft Excel 2013, lalu klik OK.

Di pita sekarang ada tab Power Pivot.

Menambahkan hubungan menggunakan Tampilan Diagram di Power Pivot

1.      Di Excel, klik Sheet3 untuk menjadikannya sebagai lembar aktif. Sheet3 berisi tabel Geography yang telah Anda impor sebelumnya.

2.      Pada pita, klik Power Pivot > Tambahkan ke Model Data. Langkah ini menambahkan tabel Geography ke model. Ini juga membuka add-in Power Pivot, yang Anda akan gunakan untuk melakukan langkah selanjutnya dalam tugas ini.

3.      Perhatikan bahwa jendela Power Pivot memperlihatkan semua tabel dalam model, termasuk Geography . Klik melalui beberapa tabel. Di add-in, Anda bisa menampilkan semua data yang disertakan model Anda.

4.      Di jendela Power Pivot, di bagian Tampilan, klik Tampilan Diagram

5.      Gunakan bilah slide untuk mengubah ukuran diagram sehingga Anda bisa melihat semua objek dalam diagram. Perhatikan ada dua tabel yang tidak terkait dengan diagram lainnya: DimEntity dan Geography.

6.      Klik kanan DimEntity dan klik Hapus. Tabel ini merupakan artefak dari database asli dan tidak diperlukan dalam model ini.

7.      Perbesar Geography sehingga Anda bisa menampilkan semua bidangnya. Anda bisa menggunakan slider untuk membuat diagram tabel lebih besar.

8.      Perhatikan bahwa Geography memiliki GeographyKey. Kolom ini berisi nilai unik yang mengidentifikasi setiap baris dalam tabel Geography. Mari cari tahu apakah tabel lain dalam model tersebut juga gunakan tombol ini. Jika ya, kita bisa membuat hubungan yang menyambungkan Geography ke model lainnya.

9.      Klik Temukan.

10.  Dalam Temukan Metadata, ketik GeographyKey.

11.  Klik Temukan Berikutnya beberapa kali. Anda akan melihat GeographyKey muncul di tabel Geography dan di tabel Stores.

12.  Posisikan ulang tabel Geography sehingga tabel ini berada di samping Stores.

13.  Seret kolom GeographyKey di Stores ke kolom GeographyKey di Geography. Power Pivot menggambar garis di antara dua kolom, yang menunjukkan hubungan.

Dalam tugas ini, Anda akan mempelajari teknik baru untuk menambahkan tabel dan membuat hubungan. Anda sekarang memiliki model yang benar-benar terintegrasi, dengan semua tabel tersambung dan tersedia untuk PivotTable di Sheet1.

TIPS    Dalam Tampilan Diagram, beberapa diagram tabel diperluas sepenuhnya, memperlihatkan kolom seperti ETLLoadID, LoadDate, dan UpdateDate. Bidang tertentu ini merupakan artefak dari gudang data asli Contoso, ditambahkan untuk mendukung ekstraksi data dan operasi pemuatan. Anda tidak memerlukannya dalam model Anda. Untuk membuangnya, soroti dan klik kanan bidang, dan klik Hapus .

Membuat kolom terhitung

Dalam Power Pivot, Anda bisa menggunakan Data Analysis Expressions (DAX) untuk menambahkan perhitungan. Dalam tugas ini, Anda akan menghitung total laba, dan menambahkan kolom terhitung yang menjadi referensi nilai data dari tabel lain. Nanti, Anda akan melihat bagaimana cara menggunakan kolom yang direferensikan untuk menyederhanakan model Anda.

1.      Dalam jendela Power Pivot, kembali ke Tampilan Data.

2.      Ganti nama tabel Table_ProductCategories accdb ke nama yang lebih mudah dikenal. Anda akan merujuk tabel ini dalam langkah berikutnya dan nama yang lebih singkat akan membuat perhitungan lebih mudah dibaca. Klik kanan nama tabel, klik Ganti Nama, ketikkan ProductCategories, lalu tekan Enter.

3.      Pilih tabel FactSales.

4.      Klik Desain > Kolom > Tambahkan.

5.      Dalam bilah rumus di sebelah atas tabel, ketikkan rumus berikut ini. LengkapiOtomatis membantu Anda mengetikkan nama kolom dan tabel yang benar-benar memenuhi syarat, dan daftar fungsi yang tersedia. Anda juga bisa mengklik kolom dan Power Pivot akan menambahkan nama kolom ke rumus.

= [SalesAmount] - [TotalCost] - [ReturnAmount]

6.      Ketika Anda telah selesai menyusun rumus, tekan Enter untuk menerima rumus tersebut.

Nilai digabungkan untuk semua baris dalam kolom terhitung. Jika Anda menggulir ke bawah menelusuri tabel, Anda akan melihat baris bisa memiliki nilai yang berbeda untuk kolom ini, berdasarkan data yang ada di dalam setiap baris.

7.      Ganti nama kolom dengan mengklik kanan CalculatedColumn1 dan pilih Ganti Nama Kolom. Ketik Laba lalu tekan Enter.

8.      Sekarang pilih tabel DimProduct.

9.      Klik Desain > Kolom > Tambahkan.

10.  Dalam bilah rumus di sebelah atas tabel, ketikkan rumus berikut ini.

= RELATED(ProductCategories[ProductCategoryName])

Fungsi RELATED mengembalikan nilai dari tabel terkait. Dalam kasus ini, tabel ProductCategories menyertakan nama kategori produk, yang akan berguna untuk dimiliki dalam tabel DimProduct ketika Anda menyusun hierarki yang menyertakan informasi kategori. Untuk informasi selengkapnya tentang fungsi ini, lihat Fungsi RELATED (DAX).

11.  Ketika Anda telah selesai menyusun rumus, tekan Enter untuk menerima rumus tersebut.

Nilai digabungkan untuk semua baris dalam kolom terhitung. Jika Anda menggulir ke bawah menelusuri tabel, Anda akan melihat setiap baris sekarang memiliki Nama Kategori Produk.

12.  Ganti nama kolom dengan mengklik kanan CalculatedColumn1 dan pilih Ganti Nama Kolom. Ketik ProductCategory, lalu tekan Enter.

13.  Klik Desain > Kolom > Tambahkan.

14.  Di bilah rumus di atas tabel, ketikkan rumus berikut ini, lalu tekan Enter untuk menerima rumus.

= RELATED(DimProductSubcategory[ProductSubcategoryName])

15.  Ganti nama kolom dengan mengklik kanan CalculatedColumn1 dan pilih Ganti Nama Kolom. Ketik ProductSubcategory, lalu tekan Enter.

Membuat hierarki

Sebagian besar model menyertakan data yang mewarisi hierarki. Contoh umum yang menyertakan data kalender, data geografi, dan kategori produk. Membuat hierarki sangat berguna karena Anda bisa menyeret satu item (hierarki) ke laporan dan bukannya harus memasang dan memesan bidang yang sama berulang kali.

1.      Dalam Power Pivot, alihkan ke Tampilan Diagram. Perluas tabel DimDate sehingga Anda bisa lebih mudah melihat semua bidang ini.

2.      Tekan dan tahan Ctrl dan klik kolom CalendarYear, CalendarQuarter dan CalendarMonth (Anda harus menggulir ke bagian bawah tabel).

3.      Dengan tiga kolom yang telah dipilih, klik kanan salah satunya dan klik Buat Hierarki. Node hierarki induk, Hierarki 1, dibuat di bagian bawah tabel, dan kolom yang dipilih akan disalin di bagian bawah hierarki sebagai node turunan.

4.      Ketik Tanggal sebagai nama untuk hierarki baru Anda.

5.      Tambahkan kolom FullDateLabel ke hierarki. Klik kanan FullDateLabel dan pilih Tambahkan Hierarki. Pilih Tanggal. FullDateLabel berisi tanggal lengkap, termasuk tahun, bulan dan hari. Pastikan bahwa FullDateLabel muncul terakhir dalam hierarki. Anda sekarang memiliki hierarki multitingkat yang menyertakan tahun, kuartal, dan hari kalender individual.

6.      Masih di Tampilan Diagram, arahkan ke tabel DimProduct, lalu klik tombol Buat Hierarki pada header tabel. Node hierarki induk kosong muncul di bagian bawah tabel.

7.      Ketik Kategori Produk sebagai nama hierarki baru Anda.

8.      Untuk membuat node hierarki turunan, seret ProductCategory dan ProductSubcategory ke hierarki.

9.      Klik kanan ProductName dan pilih Tambahkan ke Hierarki. Pilih Kategori Produk.

Sekarang Anda mengetahui beberapa cara berbeda untuk membuat hierarki, mari kita gunakan cara ini dalam PivotTable.

1.      Kembali ke Excel.

2.      Di Sheet1 (lembar yang berisi PivotTable), hapus bidang dalam area Baris.

3.      Ganti bidang dengan hierarki baru Kategori Produk dalam DimProduct.

4.      Dengan cara yang sama, ganti CalendarYear dalam area Kolom dengan Hierarki Tanggal dalam DimDate.

Ketika Anda menjelajahi data saat ini, akan mudah untuk melihat manfaat menggunakan hierarki. Anda bisa memperluas dan menutup secara independen area PivotTable yang berbeda, menyediakan lebih banyak kontrol atas cara penggunaan ruang yang tersedia. Selanjutnya, dengan menambahkan hierarki tunggal ke Baris dan Kolom, Anda bisa mendapatkan penelusuran yang kaya dan cepat, tanpa harus menyusun beberapa bidang untuk mendapatkan efek yang serupa.

Menyembunyikan kolom

Sekarang Anda telah membuat hierarki Kategori Produk dan menempatkannya dalam DimProduct, Anda tidak lagi memerlukan DimProductCategory atau DimProductSubcategory dalam daftar Bidang PivotTable. Dalam tugas ini, Anda akan mempelajari cara sembunyikan tabel dan kolom yang tidak relevan yang mengambil ruang dalam daftar Bidang PivotTable. Dengan menyembunyikan tabel dan kolom, Anda meningkatkan pengalaman pelaporan tanpa mempengaruhi model yang menyediakan hubungan dan perhitungan data.

Anda bisa menyembunyikan kolom individual, rentang kolom, atau seluruh tabel. Nama tabel dan kolom berwarna abu-abu untuk mencerminkan bahwa tabel tersebut disembunyikan pada pelaporan klien yang menggunakan model tersebut. Kolom yang disembunyikan berwarna abu-abu dalam model untuk menunjukkan statusnya, tetapi tetap terlihat dalam Tampilan Data sehingga Anda bisa terus bekerja dengan data tersebut.

1.      Di Power Pivot, pastikan Tampilan Data telah dipilih.

2.      Dalam tab di bagian bawah, klik kanan DimProductSubcategory dan pilih Sembunyikan dari Alat Klien.

3.      Ulangi untuk ProductCategories.

4.      Buka DimProduct.

5.      Klik kanan kolom berikut ini dan klik Sembunyikan dari Alat Klien:

o    ProductKey

o    ProductLabel

o    ProductSubcategory

6.      Pilih beberapa kolom yang berdekatan. Mulai dengan ClassID dan lanjutkan ke ProductSubcategory di bagian akhir. Klik kanan untuk menyembunyikannya.

7.      Ulangi untuk tabel lain, dengan menghapus ID, tombol, atau detail lain yang tidak akan Anda gunakan dalam laporan.

Kembali ke Excel ke Sheet 1 dengan daftar Bidang PivotTable untuk melihat perbedaannya. Jumlah tabel dikurangi dan DimProduct hanya menyertakan item yang kemungkinan akan Anda gunakan ketika menganalisis penjualan