Mangpaat Pivot ku Model Data

Nalika ngawangun tabel pangsi dina Excel, dina kotak dialog anu pangheulana, dimana urang dipenta pikeun nyetél rentang awal sareng milih tempat pikeun nyelapkeun tabel pangsi, aya kotak centang anu teu jelas tapi penting pisan di handap - Tambahkeun data ieu kana Modél Data (Tambihkeun data ieu kana Modél Data) jeung, saeutik leuwih luhur, switch Anggo modél data buku ieu (Paké Modél Data tina workbook ieu):

Hanjakalna, seueur pangguna anu lami-lami akrab sareng tabel pangsi sareng suksés ngagunakeunana dina karyana kadang-kadang henteu ngartos hartos pilihan ieu sareng henteu pernah ngagunakeunana. Jeung sia. Barina ogé, nyieun tabel pangsi pikeun Modél Data méré urang sababaraha kaunggulan pohara penting dibandingkeun tabel pangsi Excel Palasik.

Nanging, sateuacan nimbang-nimbang "bun" ieu, hayu urang ngartos heula naon, kanyataanna, Model Data ieu?

Naon Modél Data

Modél Data (disingkat MD atanapi DM = Modél Data) mangrupikeun daérah khusus dina file Excel dimana anjeun tiasa nyimpen data tabular - hiji atanapi langkung tabel anu dihubungkeun, upami hoyong, saling. Nyatana, ieu mangrupikeun pangkalan data leutik (OLAP cube) anu dipasang dina buku kerja Excel. Dibandingkeun sareng neundeun data klasik dina bentuk tabel biasa (atanapi pinter) dina lembar Excel sorangan, Model Data ngagaduhan sababaraha kaunggulan anu signifikan:

• Tables bisa nepi ka 2 miliar garis, sareng lambaran Excel tiasa nyocogkeun sakedik langkung ti 1 juta.
• Sanajan ukuranana gigantic, ngolah tabel sapertos (nyaring, asihan, itungan dina eta, kasimpulan wangunan, jsb) dipigawé. gancang pisan Leuwih gancang ti Excel sorangan.
• Kalayan data dina Modél, anjeun tiasa ngalakukeun itungan tambahan (upami hoyong, rumit pisan) nganggo diwangun-di basa DAX.
  
• Sadaya inpormasi dimuat kana Modél Data pisan dikomprés pisan ngagunakeun arsip anu diwangun khusus sareng rada sedeng ningkatkeun ukuran file Excel asli.
  

Modél diurus sareng diitung ku tambihan khusus anu diwangun kana Microsoft Excel - powerpivotngeunaan nu kuring geus ditulis. Pikeun ngaktifkeunana, dina tab pamekar malik COM tambihan-in (Pamekar - COM Add-in) sareng pariksa kotak anu cocog:

Lamun tab pamekar (Pamekar)anjeun teu bisa nempo dina pita nu, Anjeun bisa ngaktipkeun ngaliwatan File - Pilihan - Setup Pita (File - Pilihan - Sesuaikeun Pita). Upami dina jandela anu dipidangkeun di luhur dina daptar tambihan COM anjeun henteu gaduh Power Pivot, maka éta henteu kalebet dina versi Microsoft Office anjeun 🙁

Dina tab Power Pivot nu nembongan, bakal aya tombol héjo lampu badag Management (Ngatur), ngaklik anu bakal muka jandela Power Pivot di luhur Excel, dimana urang bakal ningali eusi Model Data tina buku ayeuna:

Catetan penting sapanjang jalan: hiji workbook Excel ngan bisa ngandung hiji Modél Data.

Muat tabel kana Modél Data

Pikeun ngamuat data kana Modél, mimitina urang ngahurungkeun tabel kana potong kompas keyboard "pinter" dinamis Ctrl+T sareng masihan nami anu ramah dina tab Constructor (Desain). Ieu mangrupikeun léngkah anu diperyogikeun.

Teras anjeun tiasa nganggo salah sahiji tina tilu metode pikeun milih:

• Pencét tombol Tambahkeun ka Modél (Tambihkeun kana Modél Data) tab powerpivot tab imah (Bumi).
  
• Milih tim Selapkeun - PivotTable (Selapkeun - Pivot Table) tur hurungkeun kotak centang Tambahkeun data ieu kana Modél Data (Tambihkeun data ieu kana Modél Data). Dina hal ieu, numutkeun data anu dimuat kana Modél, méja pangsi ogé langsung diwangun.
  
• Dina tab Advanced data (Kaping) pencét dina tombol Ti Table / Rentang (Tina Méja/Rasaran)pikeun ngamuat méja kami kana redaktur Power Query. Jalur ieu pangpanjangna, tapi, upami hoyong, di dieu anjeun tiasa ngalaksanakeun beberesih data tambahan, ngedit sareng sagala jinis transformasi, dimana Power Query kuat pisan.

  Lajeng data combed diunggah ka Modél ku paréntah Imah - Tutup sareng Muatan - Tutup sareng Muat dina… (Imah — Tutup&Muat — Tutup&Muat ka…). Dina jandéla anu muka, pilih pilihan Ngan nyieun sambungan (Ngan nyieun sambungan) jeung, paling importantly, nempatkeun keletik a Tambahkeun data ieu kana Modél Data (Tambihkeun data ieu kana Modél Data).
  

Urang ngawangun kasimpulan Model Data

Pikeun ngawangun kasimpulan Modél Data, anjeun tiasa nganggo salah sahiji tina tilu pendekatan:

• Pencét tombol tabél kasimpulan (Tabel Pangsi) dina jandela Power Pivot.
• Pilih paréntah dina Excel Selapkeun - PivotTable tur pindah ka modeu Anggo modél data buku ieu (Selapkeun — Pivot Table — Anggo Model Data buku kerja ieu).
• Milih tim Selapkeun - PivotTable (Selapkeun - Pivot Table) tur hurungkeun kotak centang Tambahkeun data ieu kana Modél Data (Tambihkeun data ieu kana Modél Data). Tabel "pinter" ayeuna bakal dimuat kana Modél sareng méja kasimpulan bakal diwangun pikeun sakabéh Modél.

Ayeuna urang parantos terang kumaha ngamuat data kana Modél Data sareng ngawangun kasimpulan éta, hayu urang ngajalajah mangpaat sareng kauntungan anu dipasihkeun ku ieu.

Mangpaat 1: Hubungan antara tabel tanpa ngagunakeun rumus

Ringkesan biasa ngan ukur tiasa diwangun nganggo data tina hiji méja sumber. Upami anjeun gaduh sababaraha di antarana, contona, penjualan, daptar harga, diréktori palanggan, ngadaptar kontrak, jsb. (VLOOKUP), INDEKS (INDEKS), LEUWIH DIBUKA (PERTANDINGAN), SUMMESLIMN (SUMIFS) jeung sajabana. Ieu panjang, tedious sarta drive Excel anjeun kana "pamikiran" kalawan jumlah badag data.

Dina kasus kasimpulan Model Data, sadayana langkung saderhana. Cukup pikeun nyetél hubungan antara tabel sakali dina jandela Power Pivot - sareng parantos réngsé. Jang ngalampahkeun ieu, dina tab powerpivot pencét tombol Management (Ngatur) lajeng dina jandela nu nembongan - tombol Témbongkeun Bagan (Témbongkeun Diagram). Tetep nyered nami kolom (konci) umum (widang) antara tabel pikeun nyiptakeun tautan:

Saatos éta, dina kasimpulan pikeun Modél Data, anjeun tiasa ngalungkeun daérah kasimpulan (baris, kolom, saringan, nilai) sagala widang tina tabel anu aya hubunganana - sadayana bakal dihubungkeun sareng diitung sacara otomatis:

Kauntungan 2: Ngitung nilai unik

A tabel pangsi biasa méré urang kasempetan pikeun milih salah sahiji sababaraha diwangun-di fungsi itungan: jumlah, rata, cacah, minimum, maksimum, jsb Dina kasimpulan Modél Data, hiji fungsi pohara kapaké ditambahkeun kana daptar standar ieu pikeun ngitung Jumlah unik (non-repeating nilai). Kalayan pitulungna, contona, anjeun tiasa kalayan gampang ngitung jumlah barang unik (rentang) anu kami jual di unggal kota.

-Klik katuhu dina widang - paréntah Pilihan widang nilai jeung dina tab operasi milih Jumlah elemen béda (Cacah béda):

Kauntungan 3: Rumus DAX Khusus

Kadang-kadang anjeun kedah ngalakukeun sababaraha itungan tambahan dina tabel pangsi. Dina kasimpulan biasa, ieu dipigawé ngagunakeun widang diitung sarta objék, sedengkeun kasimpulan model data ngagunakeun ukuran dina basa DAX husus (DAX = Data Analisis Éksprési).

Pikeun nyieun ukuran, pilih dina tab powerpivot parentah Ukuran - Jieun Ukuran (Ukuran - Ukuran anyar) atawa ngan klik katuhu dina tabel dina daptar Widang Pangsi tur pilih Tambahkeun ukuran (Tambahkeun ukuran) dina menu konteks:

Dina jandéla anu muka, setel:

• Ngaran méjadimana ukuran anu diciptakeun bakal disimpen.
• Ukur ngaran – naon ngaran anjeun ngartos pikeun widang anyar.
• gambaran - opsional.
• rumus – hal pangpentingna, sabab di dieu urang boh sacara manual ngasupkeun, atawa klik dina tombol fx sareng pilih fungsi DAX tina daptar, anu kedah ngitung hasilna nalika urang teras ngalungkeun ukuran kana daérah Nilai.
• Dina bagian handap jandela, Anjeun bisa langsung nyetel format angka pikeun ukuran dina daptar Kategori.
  

Basa DAX teu salawasna gampang ngartos sabab ngoperasikeun teu kalawan nilai individu, tapi kalawan sakabéh kolom na tabel, ie merlukeun sababaraha restructuring pamikiran sanggeus rumus Excel Palasik. Sanajan kitu, éta patut eta, sabab kakuatan kamampuhan dina ngolah jumlah badag data hese overestimate.

Kauntungan 4: Hierarki widang khusus

Seringna, nalika nyiptakeun laporan standar, anjeun kedah ngalungkeun kombinasi lapangan anu sami kana tabel pangsi dina urutan anu dipasihkeun, contona. Taun-Saparapat-Bulan-Poé, atawa Kategori-Produk, atawa Nagara-Kota-klien jsb Dina kasimpulan Model Data, masalah ieu gampang direngsekeun ku nyieun sorangan hirarki - susunan widang custom.

Dina jandela Power Pivot, pindah ka modeu bagan nganggo tombol Témbongkeun Bagan tab imah (Imah - Témbongkeun Diagram), pilih jeung Ctrl widang nu dipikahoyong tur-klik katuhu dina eta. Menu konteks bakal ngandung paréntah Jieun Hierarki (Jieun hirarki):

Hierarki anu diciptakeun tiasa digentos sareng diseret kana éta kalayan beurit widang anu diperyogikeun, supados engké dina hiji gerakan tiasa dialungkeun kana kasimpulan:

kauntungan 5: stencils custom

Neruskeun ideu paragraf sateuacana, dina kasimpulan Model Data, anjeun ogé tiasa nyiptakeun set elemen anjeun nyalira pikeun unggal lapangan. Salaku conto, tina sadaya daptar kota, anjeun tiasa sacara gampil ngadamel set ngan ukur anu aya di daérah tanggung jawab anjeun. Atawa kumpulkeun ngan konsumén Anjeun, barang anjeun, jsb kana set husus.

Jang ngalampahkeun ieu, dina tab Analisis tabel pangsi dina daptar turun-handap Widang, Item, sareng Susunan aya paréntah saluyu (Analisis - Fields, Items & Sets - Jieun set dumasar kana item baris / kolom):

Dina jandéla anu muka, anjeun tiasa sacara selektif ngahapus, nambihan atanapi ngarobih posisi unsur naon waé sareng simpen set anu hasilna dina nami énggal:

Sadaya set anu diciptakeun bakal dipintonkeun dina panel Widang Pangsi dina polder anu misah, ti mana aranjeunna tiasa diseret sacara bébas ka barisan sareng kolom dina sagala PivotTable anyar:

Kauntungan 6: Selektif Sumputkeun Tabel jeung Kolom

Sanajan ieu téh leutik, tapi kaunggulan pisan pikaresepeun dina sababaraha kasus. Ku ngaklik katuhu dina ngaran widang atawa dina tab tabel dina jandela Power Pivot, Anjeun bisa milih paréntah Sumputkeun tina Client Toolkit (Sumputkeun tina Alat Klién):

Kolom atanapi tabel anu disumputkeun bakal ngaleungit tina panel Daptar Widang PivotTable. Hal ieu kacida merenah lamun kudu nyumputkeun ti pamaké sababaraha kolom bantu (contona, diitung atawa kolom kalawan nilai konci pikeun nyieun hubungan) atawa malah sakabéh tabel.

Mangpaat 7. Canggih bor-handap

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Dina kasimpulan Model Data, alat gunana ieu tiasa dianggo langkung halus. Ku nangtung dina sél mana waé kalayan hasil anu dipikaresep ku urang, anjeun tiasa ngaklik ikon kalayan kaca pembesar anu muncul di gigireunana (anu disebut Tren Express) teras pilih widang naon waé anu anjeun pikahoyong dina tabel anu aya hubunganana:

Saatos éta, nilai ayeuna (Modél = Explorer) bakal lebet kana daérah saringan, sareng kasimpulanna bakal diwangun ku kantor:

Tangtu, prosedur misalna bisa diulang sababaraha kali, konsistén delving kana data anjeun dina arah nu dipikaresep.

Kauntungan 8: Ngarobih Pivot kana Fungsi Kubus

Upami anjeun milih sél mana waé dina kasimpulan pikeun Modél Data teras pilih dina tab Analisis tabel pangsi parentah Alat OLAP - Ngarobih kana Rumus (Analisis — OLAP Tools — Convert kana rumus), mangka sakabéh kasimpulan bakal otomatis dirobah jadi rumus. Ayeuna nilai-nilai widang di daérah baris-kolom sareng hasil dina daérah nilai bakal dicandak tina Model Data nganggo fungsi kubus khusus: CUBEVALUE sareng CUBEMEMBER:

Téhnisna, ieu ngandung harti yén ayeuna urang teu nungkulan kasimpulan, tapi kalawan sababaraha sél kalawan rumus, nyaéta urang bisa kalayan gampang ngalakukeun sagala transformasi jeung laporan urang nu teu sadia dina kasimpulan, contona, nyelapkeun baris atawa kolom anyar ka tengah. laporan, ngalakukeun sagala itungan tambahan dina kasimpulan, ngatur aranjeunna dina sagala cara nu dipikahoyong, jsb.

Dina waktos anu sami, sambungan sareng data sumber, tangtosna, tetep sareng di hareup rumus ieu bakal diropéa nalika sumberna robih. kageulisan!

• Analisis rencana-fakta dina tabel pangsi sareng Power Pivot sareng Power Query
• Méja pangsi sareng header multiline
• Jieun pangkalan data dina Excel nganggo Power Pivot