Kusintha mawerengedwe mu PivotTable yokhala ndi masila

Zosefera m'matebulo a pivot zitha kugwiritsidwa ntchito osati mwanjira yachikale - kusefa zomwe zachokera, komanso kusinthana pakati pa mitundu yosiyanasiyana yowerengera pamtengo wamtengo wapatali:

Kukhazikitsa izi ndikosavuta - zomwe mungafune ndi mitundu ingapo ndi tebulo lothandizira. Chabwino, tidzachita zonsezi osati mwachidule, koma mwachidule chomangidwa molingana ndi Power Pivot Data Model.

Gawo 1. Kulumikiza Power Pivot add-in

Ngati ma tabo a Power Pivot add-in sakuwoneka mu Excel yanu, muyenera choyamba kuyatsa. Pali njira ziwiri za izi:

• Tab Woyambitsa - batani Zowonjezera za COM (Wopanga - Zowonjezera za COM)
• Fayilo - Zosankha - Zowonjezera - Zowonjezera za COM - Pitani (Fayilo - Zosankha - Zowonjezera - COM-Add-ins - Pitani ku)

Ngati izi sizikuthandizani, yesani kuyambitsanso Microsoft Excel.

Khwerero 2: Kwezani Data mu Power Pivot Data Model

Tidzakhala ndi matebulo awiri ngati deta yoyambira:

Yoyamba ndi tebulo ndi malonda, malingana ndi zomwe tidzamanga pambuyo pake chidule. Yachiwiri ndi tebulo lothandizira, pomwe mayina a mabatani a gawo lamtsogolo amalowetsedwa.

Matebulo onsewa akuyenera kusinthidwa kukhala "smart" (zamphamvu) ndi njira yachidule ya kiyibodi Ctrl+T kapena timu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table) ndipo ndi zofunika kuwapatsa mayina anzeru pa tabu Constructor (Kapangidwe). Zikhale, mwachitsanzo, Sales и Services.

Pambuyo pake, tebulo lirilonse liyenera kuikidwa mu Data Model - chifukwa cha izi timagwiritsa ntchito pa tabu mphamvu pivot batani Onjezani ku Data Model (Onjezani ku Data Model).

Khwerero 3. Pangani muyeso kuti muwone batani lomwe lasindikizidwa pagawo

Minda yowerengedwa mu PivotTable by Data Model imatchedwa miyeso. Tiyeni tipange muyeso womwe udzawonetse dzina la batani losindikizidwa pagawo lamtsogolo. Kuti muchite izi, patebulo lathu lililonse, sankhani selo lililonse lopanda kanthu mugawo lochepera lowerengera ndikulowetsa zomanga zotsatirazi mu bar ya formula:

Apa, dzina la muyeso limabwera koyamba (Dinani batani), ndiyeno pambuyo pa colon ndi chizindikiro chofanana, njira yowerengera pogwiritsa ntchito ntchitoyi ZINTHU DAX yomangidwa mu Power Pivot.

Ngati mubwereza izi osati mu Power Pivot, koma mu Power BI, ndiye kuti colon sikufunika ndipo m'malo mwake ZINTHU mutha kugwiritsa ntchito mnzake wamakono - ntchitoyo SELECTEDVALUE.

Sitisamala zolakwika zomwe zili m'munsi mwa zenera zomwe zimawoneka pambuyo polowetsa chilinganizo - zimatuluka, chifukwa sitinakhale ndi chidule ndi kagawo komwe chinachake chikudina.

Khwerero 4. Pangani muyeso wa kuwerengera pa batani lokanikiza

Chotsatira ndicho kupanga muyeso wa zosankha zosiyanasiyana zowerengera malinga ndi mtengo wa muyeso wapitawo Dinani batani. Nayi ndondomekoyi ndi yovuta kwambiri:

Tiyeni tiphwanye pang'onopang'ono:

1. ntchito kusinthana - analogue ya IF yosungidwa - imayang'ana kukwaniritsidwa kwa zomwe zafotokozedwa ndikubweza zikhalidwe zosiyanasiyana kutengera kukwaniritsidwa kwa zina mwazo.
2. ntchito ZOONA () - amapereka zomveka "zowona" kotero kuti zinthu zomwe zafufuzidwa pambuyo pake ndi ntchito ya SWITCH zimagwira ntchito pokhapokha ngati zakwaniritsidwa, mwachitsanzo, choonadi.
3. Kenako timayang'ana mtengo wa batani lopanikizidwa ndikuwerengera zotsatira zomaliza pazosankha zitatu - monga kuchuluka kwa mtengo, cheke chapakati ndi kuchuluka kwa ogwiritsa ntchito apadera. Kuti muwerenge zamtengo wapatali, gwiritsani ntchito ntchitoyi DISTINCTCOUNT, ndi kuzungulira - ROUND.
4. Ngati palibe chimodzi mwazinthu zitatu zomwe tazitchula pamwambapa, ndiye kuti mkangano womaliza wa ntchito ya SWITCH ukuwonetsedwa - timayiyika ngati dummy pogwiritsa ntchito ntchitoyi. CHOSACHITA ().

Khwerero 5. Kumanga chidule ndi kuwonjezera kagawo

Kubwerera kuchokera ku Power Pivot kupita ku Excel ndikupanga tebulo la pivot pamenepo pazambiri zathu zonse. Kuti muchite izi, pawindo la Power Pivot waukulu tab sankhani lamulo mwachidule tebulo (Kunyumba - Pivot Table).

ndiye:

1. Timaponya munda mankhwala kuchokera patebulo Sales ku dera Mizere (Mizere).
2. Kuponya munda pamenepo chifukwa kuchokera patebulo Services.
3. Dinani kumanja pagawolo chifukwandikusankha gulu Onjezani ngati kagawo (Onjezani ngati Slicer).
4. Kuponya muyeso wachiwiri Kumaliza kuchokera patebulo Services ku dera Makhalidwe (Makhalidwe).

Apa, kwenikweni, ndi zidule zonse. Tsopano mutha kudina mabatani a slicer - ndipo ziwopsezo zomwe zili patebulo la pivot zisintha zomwe mukufuna.

Kukongola 🙂

• Ubwino wa Pivot ndi Data Model
• Kusanthula zenizeni mu tebulo la pivot pa Power Pivot
• Pangani nkhokwe mu Excel pogwiritsa ntchito Power Pivot add-in