Pivot papa ma waena o nā pae ʻikepili he nui

Hoʻokumu i ka pilikia

ʻO nā papa pivot kekahi o nā mea hana kupanaha loa ma Excel. Akā i kēia manawa, ʻaʻole naʻe, ʻaʻole hiki i kekahi o nā mana o Excel ke hana i kahi mea maʻalahi a pono i ka lele ʻana e like me ke kūkulu ʻana i kahi hōʻuluʻulu no nā pae ʻikepili mua i loaʻa, no ka laʻana, ma nā ʻāpana like ʻole a i nā papa ʻokoʻa.

Ma mua o ka hoʻomaka ʻana, e wehewehe i kekahi mau helu. A priori, ke manaʻoʻiʻo nei au ua hoʻokō ʻia nā kūlana i loko o kā mākou ʻikepili:

• Hiki i nā papa ʻaina ke loaʻa kekahi helu o nā lālani me nā ʻikepili, akā pono lākou i ke poʻo like.
• ʻAʻole pono e loaʻa nā ʻikepili hou ma nā pepa me nā papa kumu. Hoʻokahi pepa - hoʻokahi papa. No ka hoʻomalu, ʻōlelo wau iā ʻoe e hoʻohana i kahi pōkole keyboard Ctrl+Ka pau, e hoʻoneʻe iā ʻoe i ka cell hope loa i hoʻohana ʻia ma ka pepa haʻawina. ʻO ke kūpono, ʻo ia ka cell hope loa ma ka papa ʻikepili. Inā ʻoe e kaomi ma luna Ctrl+Ka pau kahakiʻi ʻia nā cell hakahaka ma ka ʻākau a i ʻole ma lalo o ka pākaukau - e holoi i kēia mau kolamu hakahaka ma ka ʻākau a i ʻole nā ​​lālani ma lalo o ka pākaukau ma hope o ka pākaukau a mālama i ka faila.

Hana 1: E hana i nā papa no kahi pivot me ka Power Query

E hoʻomaka ana mai ka mana 2010 no Excel, aia kahi pākuʻi Power Query manuahi e hiki ke hōʻiliʻili a hoʻololi i kekahi ʻikepili a laila hāʻawi iā ia i kumu no ke kūkulu ʻana i kahi papa pivot. ʻAʻole paʻakikī loa ka hoʻoponopono ʻana i kā mākou pilikia me ke kōkua o kēia add-in.

ʻO ka mea mua, e hana mākou i kahi faila ʻole ma Excel - e hui ʻia ka hui ma laila a laila e hana ʻia kahi papa pivot i loko.

A laila ma ka pā 'Ikepili (inā loaʻa iā ʻoe ka Excel 2016 a i ʻole ma hope) a i ʻole ma ka pā Nīnau Uila (inā loaʻa iā ʻoe ka Excel 2010-2013) koho i ke kauoha E hana i ka nīnau - mai ka faila - Excel (E kiʻi i ka ʻikepili - Mai ka faila - Excel) a e kuhikuhi i ka waihona kumu me nā papa e ʻohi ʻia:

Ma ka puka aniani e kū mai ana, koho i kekahi pepa (ʻaʻole pili i ka mea) a kaomi i ke pihi ma lalo ho'ololi (Hoʻoponopono):

Pono e wehe ʻia ka puka makani ʻo Power Query Query Editor ma luna o Excel. Ma ka ʻaoʻao ʻākau o ka puka aniani ma ka panel Noi nā ʻāpana holoi i nā ʻanuʻu a pau i hana ʻia koe wale nō ka mea mua - kahi (Puna):

I kēia manawa ʻike mākou i kahi papa inoa o nā pepa āpau. Inā ma waho aʻe o nā pepa ʻikepili aia kekahi mau ʻaoʻao ʻaoʻao i loko o ka faila, a laila ma kēia ʻanuʻu kā mākou hana e koho wale i kēlā mau pepa e pono ai e hoʻouka ʻia ka ʻike, me ka ʻole o nā mea ʻē aʻe a pau e hoʻohana ana i ka kānana ma ke poʻo papa:

Holoi i nā kolamu a pau koe ke kolamu 'Ikepilima ke kaomi ʻana i kahi poʻomanaʻo kolamu a koho Holoi i nā kolamu ʻē aʻe (Wehe nā kolamu ʻē aʻe):

Hiki iā ʻoe ke hoʻonui i ka ʻike o nā papa i hōʻiliʻili ʻia ma ke kaomi ʻana i ka pua pālua ma luna o ke kolamu (pahu pahu E hoʻohana i ka inoa kolamu kumu ma ke ʻano he prefix hiki iā ʻoe ke hoʻopau iā ia):

Inā hana pololei ʻoe i nā mea āpau, a laila ma kēia wahi pono ʻoe e ʻike i nā ʻike o nā papa āpau i hōʻiliʻili ʻia ma lalo o kekahi:

E hoʻokiʻekiʻe i ka lālani mua i ke poʻo papa me ke pihi E hoʻohana i ka laina mua ma ke ʻano he poʻo (E hoʻohana i ka lālani mua ma ke ʻano he poʻomanaʻo) uku pila Home (Home) a wehe i nā poʻomanaʻo papa ʻelua mai ka ʻikepili me ka hoʻohana ʻana i kahi kānana:

E mālama i nā mea a pau i hana ʻia me ke kauoha Pani a hoʻouka - Pani a hoʻouka i loko… (Hoʻopili a hoʻouka - Hoʻopili a hoʻouka iā…) uku pila Home (Home), a ma ka puka aniani e wehe ana, koho i ke koho Pili wale (Hoʻohui wale nō):

ʻO nā mea a pau. Ke waiho wale nei no ke kūkulu ʻana i kahi hōʻuluʻulu. No ka hana ʻana i kēia, hele i ka pā Hoʻokomo – Pivottable (Hookomo — Papa Pivot), koho i ke koho E hoʻohana i ke kumu ʻikepili waho (E hoʻohana i ke kumu ʻikepili waho)a laila ma ke kaomi ʻana i ke pihi E koho i ka pilina, ko makou noi. ʻO ka hana hou a me ka hoʻonohonoho ʻana o ka pivot e hana ʻia ma ke ʻano maʻamau ma ke kau ʻana i nā māla e pono ai mākou i nā lālani, nā kolamu a me nā wahi waiwai:

Inā hoʻololi ka ʻikepili kumu i ka wā e hiki mai ana a i ʻole e hoʻohui ʻia kekahi mau pepa hale kūʻai, a laila lawa ia e hōʻano hou i ka nīnau a me kā mākou hōʻuluʻulu me ke kauoha. Hōʻano hou i nā mea a pau uku pila 'Ikepili (ʻIkepili — Hoʻohou i nā mea a pau).

Hana 2. Hoʻohui mākou i nā papa me ke kauoha UNION SQL ma kahi macro

Hōʻike ʻia kahi hopena ʻē aʻe i kā mākou pilikia e kēia macro, e hana ana i kahi ʻikepili (cache) no ka papa pivot me ka hoʻohana ʻana i ke kauoha. ʻO KA UNITY ʻōlelo nīnau SQL. Hoʻohui kēia kauoha i nā papa mai nā mea a pau i ʻōlelo ʻia ma ka laha Na inoa Pepa nā pepa o ka puke i loko o ka papa ʻikepili hoʻokahi. ʻO ia hoʻi, ma kahi o ke kope kino ʻana a me ka hoʻopili ʻana i nā pae mai nā ʻāpana like ʻole i hoʻokahi, hana like mākou i ka RAM o ke kamepiula. A laila hoʻohui ka macro i kahi pepa hou me ka inoa i hāʻawi ʻia (variable InoaPepa Hua) a hoʻokumu i kahi hōʻuluʻulu piha (!) ma muli o ka hūnā i hōʻiliʻili ʻia.

No ka hoʻohana ʻana i kahi macro, e hoʻohana i ke pihi Visual Basic ma ka pā mea haku polokalamu (Ka mea hoʻomohala) a i ʻole ka pōkole papa keyboard Alt+F11. A laila hoʻokomo mākou i kahi module hou ʻole ma o ka papa kuhikuhi Hoʻokomo - Module a kope i kēia code ma laila:

Sub New_Multi_Table_Pivot() Dim i Like Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'sheet name kahi e hōʻike ʻia ai ka array na pivot ' = "Pivot of sheetName. nā inoa me nā papa kumu SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'hoʻokumu mākou i kahi huna no nā papa mai nā SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) No i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "KOHO * MAI [" & SheetsNames(i) & "$]" Aʻe i hoʻonoho i objRS = CreateObject("ADODB.Recordset") objRS .Wehe Hui$( arSQL, " UNION ALL "), _ Hui $(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Hoʻopau Me ka 'hana hou i ka pepa no ka hōʻike ʻana i ka papa pivot i loaʻa ma ka Hapa Hoʻomaka hou i ka palapala noi.DisplayAlerts = False Worksheets(ResultSheetName). t. Inoa = ResultSheetName 'hōʻike i ka hōʻuluʻulu huna huna i hana ʻia ma kēia pepa Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = ʻAʻohe mea me wsPivot objPivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet"). objPivotCache = ʻAʻohe mea maʻamau ("A3"). E koho i End With End Sub    

Hiki ke holo ʻia ka macro i hoʻopau ʻia me kahi pōkole keyboard Alt+F8 a i ʻole ke pihi Macros ma ka pā mea haku polokalamu (Ka mea hoʻomohala — Macros).

Nā pōʻino o kēia ala:

• ʻAʻole hōʻano hou ʻia ka ʻikepili no ka mea ʻaʻohe pili o ka cache i nā papa kumu. Inā hoʻololi ʻoe i ka ʻikepili kumu, pono ʻoe e holo hou i ka macro a kūkulu hou i ka hōʻuluʻulu.
• Ke hoʻololi nei i ka helu o nā lau, pono e hoʻoponopono i ka code macro (array Na inoa Pepa).
  

Akā i ka hopena, loaʻa iā mākou kahi papaʻaina pivot piha piha, i kūkulu ʻia ma nā pae he nui mai nā lau like ʻole:

Eia!

memo loea: inā loaʻa iā ʻoe kahi hewa e like me "ʻAʻole i hoʻopaʻa inoa ʻia ka mea hoʻolako" i ka wā e holo ana i ka macro, a laila loaʻa paha iā ʻoe kahi mana 64-bit o Excel a i ʻole kahi mana piha o Office i hoʻokomo ʻia (ʻaʻohe Access). No ka hoʻoponopono ʻana i ke kūlana, hoʻololi i ka ʻāpana i loko o ke code macro:

	 Mea hoʻolako=Microsoft.Jet.OLEDB.4.0;  

i:

	Mea hoʻolako=Microsoft.ACE.OLEDB.12.0;  

A hoʻoiho a hoʻouka i ka ʻenekini hana ʻikepili manuahi mai Access mai ka pūnaewele Microsoft - Microsoft Access Database Engine 2010 Redistributable

Hana 3: Hoʻohui i ka Wizard PivotTable mai nā mana kahiko o Excel

He mea kahiko kēia ʻano, akā pono e haʻi ʻia. Ma ka ʻōlelo maʻamau, ma nā mana āpau a hiki i ka makahiki 2003, aia kahi koho ma ka Wizard PivotTable e "kūkulu i kahi pivot no kekahi mau pae hoʻohui". Eia nō naʻe, ʻo kahi hōʻike i kūkulu ʻia ma kēia ʻano, akā naʻe, he ʻano minamina wale nō ia o kahi hōʻuluʻulu piha piha maoli a ʻaʻole kākoʻo i ka nui o nā "chips" o nā papa pivot maʻamau:

I loko o ia ʻano pivot, ʻaʻohe poʻomanaʻo kolamu i ka papa inoa o ke kahua, ʻaʻohe hoʻonohonoho hoʻonohonoho maʻalahi, ka palena o nā hana i hoʻohana ʻia, a, ma ka laulā, ʻaʻole like kēia mau mea āpau me kahi papa pivot. ʻO ia ke kumu, e hoʻomaka ana i ka makahiki 2007, ua wehe ʻo Microsoft i kēia hana mai ke kamaʻilio maʻamau i ka wā e hana ana i nā hōʻike papa pivot. I kēia manawa, loaʻa kēia hiʻohiʻona ma o kahi pihi maʻamau Wizard Papa Pivot(Wizard Papa Pivot), inā makemake ʻia, hiki ke hoʻohui ʻia i ka Quick Access Toolbar ma o Kōnae - Nā koho - Hoʻopilikino i ka hāmeʻa komo wikiwiki - Nā kauoha āpau (Kōnae — Nā Koho — Hoʻopilikino i ka mea hana komo wikiwiki — Nā kauoha a pau):

Ma hope o ke kaomi ʻana i ke pihi i hoʻohui ʻia, pono ʻoe e koho i ke koho kūpono ma ka pae mua o ka wizard:

A laila ma ka puka aniani aʻe, e koho i kēlā me kēia ʻāpana a hoʻohui iā ia i ka papa inoa maʻamau:

Akā, eia hou, ʻaʻole kēia he hōʻuluʻulu piha, no laila, mai manaʻo nui ʻoe mai ia mea. Hiki iaʻu ke paipai i kēia koho i nā hihia maʻalahi loa.

• Ke hana nei i nā hōʻike me nā Pivot Tables
• Hoʻonohonoho i nā helu ʻana ma Pivot Tables
• He aha nā macros, pehea e hoʻohana ai iā lākou, kahi e kope ai i ka code VBA, etc.
• ʻOhi ʻikepili mai nā pepa he nui i hoʻokahi (PLEX add-on)