Advanced PivotTables: Pagsasama-sama ng Datos mula sa Maraming mga Sheet

() translation by (you can also view the original English article)

Kapag gusto mong lumikha ng PivotTable, ano ang ginagawa mo kapag mayroon kang datos sa iba-ibang worksheet? Kapag gumagamit ka ng Excel 2013, mayroon kang steamline na paraan ng paggawa nito. Mayroong pamamaraan na tinatawag na Data Model, at ito ay gumagamit ng psg-uugnay ng mga datos sa paraan na ginagawa ng database.

Sa tutorial na ito, ipapakita ko sa inyo ang lahat ng kailangan ninyo upang makagawa ng PivotTable sa Excel 2013 mula sa datos sa maraming sheet, gamit ang Data Model.

Screencast

Kung gusto niyong sundan ang tutorial na ito gamit ang inyong sariling file sa Excel, maaari ninyong gawin. O kaya kung mas gusto niyo, idownload ang file na zip na kasama sa tutorial na ito, na naglalaman ng halimbawang workbook na tinatawag na Pivot Consolidate.xlsx.

Pagsusuri ng Datos

Ang workbook na ito ay may tatlong worksheet: Customer Info, Order Info, at Payment Info.

I-click ang Customer Info sheet, at makikita na naglalaman ito ng mga number order, at ang pangalan at pamayanan ng mga kostumer.

I-click ang Order Info sheet, at makikita na naglalaman din ito ng mga order number, at ng mga field sa kasalukuyang buwan, mga produkto na inorder, at kung ang produkto ay organic o hindi.

I-click ang Payment Info sheet, at makikita na naglalaman ito ng mga order number, ang presyo sa dolyar ng bawat benta, ang paraan ng pagbabayad na ginagamit, at kung ang order ay ginawa ng bago o ng dati nang kostumer.

Sa pag-uugnay ng lahat ng mga sheet sa loob ng task pane ng PivotTable, maaari tayong mamili ng datos sa bawat sheet. Dahil ang order number ay naroon sa ng tatlong sheet, ang mga ito ang magiging puntos ng ugnayan. Ito ang tinatawag na primary key ng database. Tandaan na hindi laging kailangan na magkaroon ng primary key, ngunit nakakabawas ito sa tsansa na magkamali.

Paglikha ng Pinangalanang Table

Bago likhain ang PivotTable, lumikha muna tayo ng table mula sa bawat sheet.

I-click ang back sa Customer Table, pagkatapos ay mag-click kahit saan sa loob ng data area. Pumunta sa Insert tab ng ribbon bar, pagkatapos ay i-click ang Table icon.

creating a table from existing data — I-convert ang datos sa worksheet sa pamamagitan ng pagpili ng **Insert > Table**

Ang dialog box na Create Table ay matutukoy ang wastong lugar para sa table. Ang checkbox sa ibaba ay dapat ding makakilala na ang unang hilera sa table ay para sa mga header. (Kung hindi, piliin ang opsyon.)

Create Table dialog box — Ang Create Table dialog ay dapat mahulaan ng tama ang data area

I-click ang OK, at ngayon mayroon ka ng table na may stripe shading at mga filter button. Maaari kang mag-click sa loob nito upang tanggalin ang pagkapili, kung gusto mong tingnan nang mas mabuti ito (huwag lamang mag-click sa labas ng table). Ang ribbon bar ay nagpapakita rin ng Design tab para sa table. Sa kaliwang bahagi ng ribbon, ang kahon na Table Name ay nagpapakita ng pansamantalang pangalan ng Table1. Burahin ito, at tawagin itong Customer_Info (gamitin ang underscore sa halip na space). Pagkatapos ay pindutin ang Enter.

applying a name to a table — Lagyan ng pangalan ang bawat table

Ulitin ang prosesong ito sa mga sheet ng Order Info at Payment Info. Pangalanan ang mga table ng Order_Info at Payment_Info, nang naaayon.

Ngayon ay handa na tayong magpasok ng PivotTable.

Pagpapasok ng PivotTable

Sa sheet ng Payment Info, siguraduhing ang cursor ay nasa loob ng table. Bumalik sa Insert tab ng ribbon, at i-click ang icon ng PivotTable (ito ang pinakaunang icon)

inserting the PivotTable — Habang nasa loob ng isa sa mga table ang cursor, piliin ang **Insert > PivotTable**

Ang dialog box na lalabas ay dapat na matukoy nang wasto ang table at piliin na ang PivotTable ay pupunta sa panibagong worksheet. Sa ibaba, i-click ang check box para sa Add this data to the Data Model. Pagkatapos ay i-click ang OK.

add data to the Data Model — Ang pagdadagdag ng datos sa Data Model ang nagpapangyari upang maging matagumpay ang pag-uugnay.

Magkakaroon ka ngayon ng PivotTable sa bagong worksheet, magkakaroon ng task pane sa kanang bahagi ng screen, at ang ribbon bar ay magpapakita ng Analyze tab.

Ang task pane ay nagpapakita ng table at mga field ng mga aktibong sheet, kaya i-click ang ALL upang makita ang lahat ng mga table na nilikha mo. Ngunit bago mo magamit ang mga ito, kailangan muna nating iugnay ang mga ito sa bawat isa, at ito ay nangangahulugan ng paglikha ng ugnayan. I-click ang Relationships button sa ribbon bar.

Pagseset-up ng Table Relationships

Ang pagclick ng button ay magpapakita ng Manage Relationships dialog. I-click ang New button, at ito ay magpapakita ng Create Relationship dialog. Lilikha tayo ng dalawang relationship gamit ang Order # field bilang pang-ugnay.

Mula sa drop-down lists, piliin ang Payment_Info para sa table, at sunod dito, piliin ang Order # mula sa Column drop-down. Sa pangalawang hilera, piliin ang Customer_Info mula sa Related Table drop-down list, at sa tabi nito, piliin ang Order # mula sa Related Column drop-down list.

creating a table relationship — Mayroong tatlong table, kaya lumikha ng dalawang ugnayan.

Nangangahulugan itong ang mga table ng Payment_Info at Customer_Info ay magkaugnay kung saan sila ay mayroong magkatugmang order number.

I-click ang OK, at makikita natin ang nakalistang ugnayan sa Manage Relationships box.

Ulitin ang prosesong ito upang makalikha ng ugnayan na magsasama sa Payment_Info to Order_Info, gamit din ang Order # field. Ang Manage Relationships box ay ganito dapat ang hitsura ngayon:

list of all relationships — Ang Manage Relationships dialog ay magpapakita ng ugnayan na iyong nilikha

Tandaan na hindi laging kailangan na lumikha ng ugnayan sa pagitan ng mga table ng Order_Info at Customer_Info, sa kadahilanang sila ay kusang napag-uugnay sa pamamagitan ng table ng Payment_Info.

I-click ang Close button sa ilalim ng kahon. Ngayon maaari na nating hilahin ang mga field papunta sa PivotTable.

Pagpapasok ng mga Field sa PivotTable

Sa lahat ng seksiyon ng task pane, i-click ang maliliit na arrow upang paikot na buksan ang tatlong table, upang makita mo ang kanilang mga field. Hilahin ang mga field papunta sa mga PivotTable area sa ganitong pamamaraan:

Pamayanan at Buwan papunta sa mga hilera
Produkto papunta sa mga hanay
$ Benta papunta sa mga halaga
Katayuan papunta sa kanilang mga filter

final PivotTable — Hilahin ang mga field mula sa bawat isa sa tatlong table papunta sa PivotTable.

Ngayon maaari mo na itong gamitin at baguhin katulad ng kahit na anong ibang PivotTable.

Konklusiyon

Gamit ang bagong Object Data Model na tampok sa Excel 2013, maari mo ng piliin ang pinakamaganda sa mga field mula sa madaming worksheet upang makalikha ng pinag-isang PivotTable. Tandaan na ang mga hilera sa bawat table ay kailangang nakaugnay sa bawat isa sa ilang paraan. Magkakaroon ka ng mas mataas na tyansa ng pagtatagumpay kung ang mga table ay mayroong magkaparehong field na may natatanging halaga.

Kung naghahanap ka ng magandang paraan ng pagpapakita ng iyong datos, ang Envato Market ay may magandang pagpipilian ng mga Excel at PowerPoint template, pati na ng mga script at app para sa pagsasalin ng datos ng Excel sa web-friendly format at ang kabaligtaran nito.