Manfaat Pivot menurut Model Data

Saat membangun tabel pivot di Excel, di kotak dialog pertama, di mana kita diminta untuk mengatur rentang awal dan memilih tempat untuk memasukkan tabel pivot, ada kotak centang yang tidak mencolok tetapi sangat penting di bawah ini – Tambahkan data ini ke Model Data (Tambahkan data ini ke Model Data) dan, sedikit lebih tinggi, sakelarnya Gunakan model data buku ini (Gunakan Model Data dari buku kerja ini):

Sayangnya, banyak pengguna yang sudah lama mengenal tabel pivot dan berhasil menggunakannya dalam pekerjaan mereka terkadang tidak begitu memahami arti dari opsi ini dan tidak pernah menggunakannya. Dan sia-sia. Bagaimanapun, membuat tabel pivot untuk Model Data memberi kita beberapa keuntungan yang sangat penting dibandingkan dengan tabel pivot Excel klasik.

Namun, sebelum mempertimbangkan "roti" ini dari dekat, mari kita pahami dulu apa sebenarnya Model Data ini?

Apa itu Model Data?

Model data (disingkat MD atau DM = Data Model) adalah area khusus di dalam file Excel di mana Anda dapat menyimpan data tabular – satu atau lebih tabel yang ditautkan, jika diinginkan, satu sama lain. Sebenarnya, ini adalah database kecil (OLAP cube) yang disematkan di dalam buku kerja Excel. Dibandingkan dengan penyimpanan data klasik dalam bentuk tabel biasa (atau cerdas) pada lembar Excel itu sendiri, Model Data memiliki beberapa keunggulan signifikan:

• Meja bisa sampai 2 miliar baris, dan lembar Excel dapat memuat sedikit lebih dari 1 juta.
• Terlepas dari ukurannya yang sangat besar, pemrosesan tabel seperti itu (pemfilteran, penyortiran, perhitungannya, ringkasan bangunan, dll.) dilakukan. sangat cepat Jauh lebih cepat dari Excel itu sendiri.
• Dengan data dalam Model, Anda dapat melakukan perhitungan tambahan (jika diinginkan, sangat kompleks) menggunakan bahasa DAX bawaan.
  
• Semua informasi yang dimuat ke dalam Model Data sangat sangat terkompresi menggunakan pengarsip bawaan khusus dan agak meningkatkan ukuran file Excel asli.
  

Model dikelola dan dihitung dengan add-in khusus yang ada di dalam Microsoft Excel – Powerpivottentang yang sudah saya tulis. Untuk mengaktifkannya, pada tab pembangun Klik COM tambahan (Pengembang — Add-in COM) dan centang kotak yang sesuai:

Jika tab pembangun (Pengembang)Anda tidak dapat melihatnya di pita, Anda dapat menyalakannya melalui File – Opsi – Pengaturan Pita (File — Opsi — Kustomisasi Pita). Jika pada jendela yang ditampilkan di atas dalam daftar add-in COM Anda tidak memiliki Power Pivot, maka Power Pivot tidak termasuk dalam versi Microsoft Office Anda

Pada tab Power Pivot yang muncul, akan ada tombol besar berwarna hijau muda Pengelolaan (Mengelola), mengklik yang akan membuka jendela Power Pivot di atas Excel, di mana kita akan melihat konten Model Data dari buku saat ini:

Catatan penting di sepanjang jalan: buku kerja Excel hanya bisa berisi satu Model Data.

Muat tabel ke dalam Model Data

Untuk memuat data ke dalam Model, pertama-tama kita ubah tabel menjadi pintasan keyboard "pintar" yang dinamis Ctrl+T dan beri nama yang ramah di tab Pembina (Rancangan). Ini adalah langkah yang diperlukan.

Kemudian Anda dapat menggunakan salah satu dari tiga metode untuk dipilih:

• Tekan tombol Tambahkan ke Model (Tambahkan ke Model Data) tab Powerpivot tab Beranda (Rumah).
  
• Memilih tim Sisipkan – PivotTable (Sisipkan — Tabel Pivot) dan nyalakan kotak centang Tambahkan data ini ke Model Data (Tambahkan data ini ke Model Data). Dalam hal ini, menurut data yang dimuat ke dalam Model, tabel pivot juga segera dibuat.
  
• Pada tab Lanjutan Data (Tanggal) klik tombolnya Dari Tabel/Rentang (Dari Tabel/Rentang)untuk memuat tabel kita ke editor Power Query. Jalur ini adalah yang terpanjang, tetapi, jika diinginkan, di sini Anda dapat melakukan pembersihan data tambahan, pengeditan, dan semua jenis transformasi, di mana Power Query sangat kuat.

  Kemudian data yang disisir diunggah ke Model dengan perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke…). Di jendela yang terbuka, pilih opsi Buat saja koneksi (Hanya buat koneksi) dan, yang paling penting, beri tanda centang Tambahkan data ini ke Model Data (Tambahkan data ini ke Model Data).
  

Kami membuat ringkasan Model Data

Untuk membuat Model Data ringkasan, Anda dapat menggunakan salah satu dari tiga pendekatan:

• Tekan tombol tabel ringkasan (Tabel pivot) di jendela Power Pivot.
• Pilih perintah di Excel Sisipkan – PivotTable dan beralih ke mode Gunakan model data buku ini (Sisipkan — Tabel Pivot — Gunakan Model Data buku kerja ini).
• Memilih tim Sisipkan – PivotTable (Sisipkan — Tabel Pivot) dan nyalakan kotak centang Tambahkan data ini ke Model Data (Tambahkan data ini ke Model Data). Tabel "pintar" saat ini akan dimuat ke dalam Model dan tabel ringkasan akan dibuat untuk seluruh Model.

Sekarang kita telah mengetahui cara memuat data ke dalam Model Data dan membuat ringkasannya, mari kita jelajahi manfaat dan keuntungan yang diberikannya kepada kita.

Manfaat 1: Hubungan antar tabel tanpa menggunakan rumus

Ringkasan biasa hanya dapat dibuat menggunakan data dari satu tabel sumber. Jika Anda memiliki beberapa di antaranya, misalnya, penjualan, daftar harga, direktori pelanggan, daftar kontrak, dll., maka Anda harus terlebih dahulu mengumpulkan data dari semua tabel menjadi satu menggunakan fungsi seperti VLOOKUP (VLOOKUP), INDEKS (INDEKS), LEBIH TERBUKTI (COCOK), RINGKASAN (SUMIF) dan sejenisnya. Ini panjang, membosankan, dan mendorong Excel Anda menjadi "pemikiran" dengan sejumlah besar data.

Dalam hal ringkasan Model Data, semuanya jauh lebih sederhana. Cukup menyetel hubungan antar tabel sekali di jendela Power Pivot – dan selesai. Untuk melakukan ini, pada tab Powerpivot tekan tombolnya Pengelolaan (Mengelola) dan kemudian di jendela yang muncul – tombol Tampilan Grafik (Tampilan Diagram). Tetap menyeret nama kolom (kunci) umum (bidang) di antara tabel untuk membuat tautan:

Setelah itu, dalam ringkasan untuk Model Data, Anda dapat memasukkan area ringkasan (baris, kolom, filter, nilai) bidang apa pun dari tabel terkait – semuanya akan ditautkan dan dihitung secara otomatis:

Manfaat 2: Hitung nilai unik

Tabel pivot biasa memberi kita kesempatan untuk memilih salah satu dari beberapa fungsi perhitungan bawaan: jumlah, rata-rata, hitungan, minimum, maksimum, dll. Dalam ringkasan Model Data, fungsi yang sangat berguna ditambahkan ke daftar standar ini untuk menghitung jumlah unik (nilai yang tidak berulang). Dengan bantuannya, misalnya, Anda dapat dengan mudah menghitung jumlah barang unik (range) yang kami jual di setiap kota.

Klik kanan pada bidang – perintah Opsi bidang nilai dan di tab Operasi Pilih Jumlah elemen yang berbeda (Jumlah berbeda):

Manfaat 3: Rumus DAX Khusus

Terkadang Anda harus melakukan berbagai perhitungan tambahan di tabel pivot. Dalam ringkasan biasa, ini dilakukan menggunakan bidang dan objek terhitung, sedangkan ringkasan model data menggunakan ukuran dalam bahasa DAX khusus (DAX = Ekspresi Analisis Data).

Untuk membuat ukuran, pilih pada tab Powerpivot perintah Ukuran – Buat Ukuran (Tindakan — Ukuran baru) atau cukup klik kanan pada tabel dalam daftar Pivot Fields dan pilih Tambahkan ukuran (Tambahkan ukuran) di menu konteks:

Di jendela yang terbuka, atur:

• Nama tabeldi mana ukuran yang dibuat akan disimpan.
• Mengukur nama – nama apa pun yang Anda pahami untuk bidang baru.
• Deskripsi Produk - pilihan.
• Rumus – yang paling penting, karena di sini kita masuk secara manual, atau mengklik tombol fx dan pilih fungsi DAX dari daftar, yang seharusnya menghitung hasilnya saat kita memasukkan ukuran kita ke area Nilai.
• Di bagian bawah jendela, Anda dapat segera mengatur format angka untuk ukuran dalam daftar Kategori.
  

Bahasa DAX tidak selalu mudah dipahami karena beroperasi tidak dengan nilai individual, tetapi dengan seluruh kolom dan tabel, yaitu memerlukan beberapa restrukturisasi pemikiran setelah rumus Excel klasik. Namun, itu sepadan, karena kekuatan kemampuannya dalam memproses data dalam jumlah besar sulit untuk ditaksir terlalu tinggi.

Manfaat 4: Hirarki bidang khusus

Seringkali, saat membuat laporan standar, Anda harus memasukkan kombinasi bidang yang sama ke dalam tabel pivot dalam urutan tertentu, misalnya Tahun-Kuartal-Bulan-Hari, atau Kategori-Produk, atau Negara-Kota-Klien dll. Dalam ringkasan Model Data, masalah ini mudah diselesaikan dengan membuat sendiri hierarki - set bidang khusus.

Di jendela Power Pivot, alihkan ke mode bagan dengan tombol Tampilan Grafik tab Beranda (Beranda — Tampilan Diagram), pilih dengan Ctrl bidang yang diinginkan dan klik kanan padanya. Menu konteks akan berisi perintah Buat Hirarki (Buat hierarki):

Hirarki yang dibuat dapat diganti namanya dan diseret ke dalamnya dengan mouse bidang yang diperlukan, sehingga nanti dalam satu gerakan mereka dapat dilemparkan ke dalam ringkasan:

Manfaat 5: Stensil khusus

Melanjutkan ide paragraf sebelumnya, dalam ringkasan Model Data, Anda juga dapat membuat kumpulan elemen Anda sendiri untuk setiap bidang. Misalnya, dari seluruh daftar kota, Anda dapat dengan mudah membuat satu set hanya yang berada di wilayah tanggung jawab Anda. Atau kumpulkan hanya pelanggan Anda, barang Anda, dll. ke dalam satu set khusus.

Untuk melakukan ini, pada tab Analisis tabel pivot dalam daftar tarik-turun Bidang, Item, dan Set ada perintah yang sesuai (Analisis — Bidang, Items & Sets — Buat set berdasarkan item baris/kolom):

Di jendela yang terbuka, Anda dapat secara selektif menghapus, menambah, atau mengubah posisi elemen apa pun dan menyimpan kumpulan yang dihasilkan dengan nama baru:

Semua kumpulan yang dibuat akan ditampilkan di panel Bidang PivotTable dalam folder terpisah, dari mana mereka dapat dengan bebas diseret ke area baris dan kolom dari PivotTable baru:

Manfaat 6: Selektif Sembunyikan Tabel dan Kolom

Meskipun ini adalah keuntungan kecil, tetapi sangat menyenangkan dalam beberapa kasus. Dengan mengklik kanan pada nama bidang atau pada tab tabel di jendela Power Pivot, Anda dapat memilih perintah Sembunyikan dari Perangkat Klien (Sembunyikan dari Alat Klien):

Kolom atau tabel tersembunyi akan hilang dari panel Daftar Bidang PivotTable. Sangat nyaman jika Anda perlu menyembunyikan dari pengguna beberapa kolom tambahan (misalnya, dihitung atau kolom dengan nilai kunci untuk membuat hubungan) atau bahkan seluruh tabel.

Manfaat 7. Penelusuran lanjutan

Jika Anda mengklik dua kali sel mana pun di area nilai dalam tabel pivot biasa, maka Excel akan menampilkan salinan fragmen data sumber yang terlibat dalam penghitungan sel ini pada lembar terpisah. Ini adalah hal yang sangat berguna, secara resmi disebut Drill-down (biasanya dikatakan "gagal").

Dalam ringkasan Model Data, alat praktis ini bekerja lebih halus. Dengan berdiri di sel mana saja dengan hasil yang menarik bagi kami, Anda dapat mengklik ikon dengan kaca pembesar yang muncul di sebelahnya (disebut Tren Ekspres) lalu pilih bidang apa pun yang Anda minati di tabel terkait:

Setelah itu, nilai saat ini (Model = Explorer) akan masuk ke area filter, dan ringkasan akan dibuat oleh kantor:

Tentu saja, prosedur seperti itu dapat diulang berkali-kali, secara konsisten menggali data Anda ke arah yang Anda minati.

Manfaat 8: Mengonversi Fungsi Pivot ke Kubus

Jika Anda memilih sel apa pun dalam ringkasan untuk Model Data dan kemudian pilih pada tab Analisis tabel pivot perintah Alat OLAP – Konversikan ke Rumus (Analisis — Alat OLAP — Konversikan ke rumus), maka seluruh ringkasan akan otomatis dikonversi ke rumus. Sekarang nilai bidang di area baris-kolom dan hasil di area nilai akan diambil dari Model Data menggunakan fungsi kubus khusus: CUBEVALUE dan CUBEMEMBER:

Secara teknis, ini berarti bahwa sekarang kita tidak berurusan dengan ringkasan, tetapi dengan beberapa sel dengan rumus, yaitu kita dapat dengan mudah melakukan transformasi apa pun dengan laporan kita yang tidak tersedia dalam ringkasan, misalnya, menyisipkan baris atau kolom baru ke tengah laporan, melakukan perhitungan tambahan di dalam ringkasan, mengaturnya dengan cara yang diinginkan, dll.

Pada saat yang sama, koneksi dengan sumber data, tentu saja, tetap ada dan di masa depan formula ini akan diperbarui ketika sumber berubah. Kecantikan!

• Analisis rencana fakta dalam tabel pivot dengan Power Pivot dan Power Query
• Tabel pivot dengan header multiline
• Membuat database di Excel menggunakan Power Pivot