Pivot rooj hla ntau cov ntaub ntawv ntau yam

Formulation ntawm qhov teeb meem

Pivot ntxhuav yog ib qho ntawm cov cuab yeej zoo tshaj plaws hauv Excel. Tab sis txog tam sim no, hmoov tsis, tsis muaj ib qho ntawm Excel versions tuaj yeem ua qhov yooj yim thiab tsim nyog ntawm ya raws li tsim cov ntsiab lus rau ntau qhov pib cov ntaub ntawv nyob hauv, piv txwv li, ntawm cov nplooj ntawv sib txawv lossis hauv cov rooj sib txawv:

Ua ntej peb pib, cia peb qhia ob peb lub ntsiab lus. Ib qho tseem ceeb, Kuv ntseeg tias cov xwm txheej hauv qab no tau ntsib hauv peb cov ntaub ntawv:

• Cov ntxhuav tuaj yeem muaj tus lej ntawm kab nrog cov ntaub ntawv, tab sis lawv yuav tsum muaj tib lub header.
• Yuav tsum tsis txhob muaj cov ntaub ntawv ntxiv ntawm cov nplooj ntawv nrog cov ntawv sau. Ib daim ntawv - ib lub rooj. Txhawm rau tswj, kuv qhia koj kom siv cov keyboard shortcut Ctrl+End, uas txav koj mus rau qhov kawg siv ntawm tes hauv daim ntawv ua haujlwm. Qhov zoo tshaj plaws, qhov no yuav tsum yog lub xov tooj kawg hauv cov ntaub ntawv. Yog thaum twg koj nyem rau Ctrl+End ib qho khoob ntawm tes mus rau sab xis lossis hauv qab lub rooj yog qhov tseem ceeb - tshem tawm cov kab khoob no mus rau sab xis lossis kab hauv qab lub rooj tom qab lub rooj thiab txuag cov ntaub ntawv.

Txoj Kev 1: Tsim cov rooj rau pivot siv Power Query

Pib los ntawm xyoo 2010 version rau Excel, muaj cov lus nug dawb pub dawb ntxiv uas tuaj yeem sau thiab hloov pauv cov ntaub ntawv thiab tom qab ntawd muab nws ua qhov chaw tsim lub rooj pivot. Kev daws peb qhov teeb meem nrog kev pab ntawm qhov kev ntxiv no tsis yog qhov nyuaj txhua.

Ua ntej, cia peb tsim cov ntaub ntawv khoob tshiab hauv Excel - kev sib dhos yuav tshwm sim hauv nws thiab tom qab ntawd cov lus pivot yuav raug tsim hauv nws.

Tom qab ntawd ntawm lub tab Cov ntaub ntawv (yog tias koj muaj Excel 2016 lossis tom qab) lossis hauv tab Lub Zog Lus Nug (yog tias koj muaj Excel 2010-2013) xaiv cov lus txib Tsim Cov Lus Nug - Los ntawm Cov Ntaub Ntawv - Excel (Tau txais cov ntaub ntawv - Los ntawm cov ntaub ntawv - Excel) thiab qhia meej cov ntaub ntawv qhov chaw nrog cov ntxhuav yuav tsum tau sau:

Hauv qhov rai uas tshwm, xaiv ib daim ntawv (tsis muaj teeb meem dab tsi) thiab nias lub pob hauv qab no hloov (Kho kom raug):

Lub Power Query Query Editor qhov rais yuav tsum qhib rau saum Excel. Nyob rau sab xis ntawm lub qhov rais ntawm lub vaj huam sib luag Thov Parameters rho tawm tag nrho cov kauj ruam tsim tau tsuas yog thawj zaug - Tau qhov twg los (Tau qhov twg los):

Tam sim no peb pom cov npe dav dav ntawm txhua nplooj ntawv. Yog tias ntxiv rau cov nplooj ntawv cov ntaub ntawv muaj qee cov nplooj ntawv sab hauv cov ntaub ntawv, tom qab ntawd ntawm cov kauj ruam no peb txoj haujlwm tsuas yog xaiv cov nplooj ntawv los ntawm cov ntaub ntawv uas yuav tsum tau thauj khoom, tsis suav tag nrho lwm tus siv cov lim hauv lub rooj header:

Rho tawm txhua kab tsuas yog kab Cov ntaub ntawvlos ntawm txoj cai-nias ntawm kab lus thiab xaiv Tshem tawm lwm kab ( Tshem tawm lwm kab):

Tom qab ntawd koj tuaj yeem nthuav cov ntsiab lus ntawm cov rooj sau los ntawm kev nyem rau ntawm ob lub xub nyob rau sab saum toj ntawm kab (checkbox Siv thawj kab npe ua ntej koj tuaj yeem tua nws):

Yog tias koj ua txhua yam kom raug, ces ntawm no koj yuav tsum pom cov ntsiab lus ntawm txhua lub rooj sau ib qho hauv qab no:

Nws tseem yuav tsa thawj kab rau lub rooj header nrog lub pob Siv thawj kab ua headers (Siv thawj kab ua headers) tab Tsev (Tsev) thiab tshem tawm duplicate table headers los ntawm cov ntaub ntawv siv lub lim:

Txuag txhua yam ua tiav nrog cov lus txib Kaw thiab thauj khoom - Kaw thiab thauj hauv… (Kaw & Load - Kaw & Load rau…) tab Tsev (Tsev), thiab nyob rau hauv lub qhov rais uas qhib, xaiv qhov kev xaiv Kev sib txuas nkaus xwb (Kev sib txuas nkaus xwb):

Txhua yam. Nws tseem tsuas yog tsim cov ntsiab lus. Txhawm rau ua qhov no, mus rau lub tab Insert – PivotTable (Insert — Pivot Table), xaiv qhov kev xaiv Siv cov ntaub ntawv sab nraud (Siv cov ntaub ntawv sab nraud)thiab tom qab ntawd nias lub pob Xaiv kev sib txuas, peb thov. Ntxiv creation thiab configuration ntawm pivot tshwm sim nyob rau hauv ib tug kiag li tus qauv txoj kev los ntawm dragging cov teb peb xav tau rau hauv kab, kab thiab qhov chaw muaj nuj nqis:

Yog tias cov ntaub ntawv hloov pauv yav tom ntej lossis ob peb lub khw muag khoom ntxiv ntxiv, ces nws yuav txaus los hloov cov lus nug thiab peb cov ntsiab lus uas siv cov lus txib Hloov tshiab txhua yam tab Cov ntaub ntawv (Cov ntaub ntawv - Refresh All).

Txoj Kev 2. Peb sib sau cov ntxhuav nrog UNION SQL hais kom ua hauv macro

Lwm qhov kev daws teeb meem rau peb qhov teeb meem yog sawv cev los ntawm macro no, uas tsim cov ntaub ntawv teeb tsa (cache) rau lub rooj pivot siv cov lus txib UNITY SQL lus nug. Cov lus txib no sib txuas cov ntxhuav los ntawm txhua qhov teev hauv array SheetNames nplooj ntawv ntawm phau ntawv mus rau hauv ib lub rooj ntaub ntawv. Ntawd yog, tsis yog lub cev luam tawm thiab muab tso rau ntawm cov nplooj ntawv sib txawv rau ib qho, peb ua tib yam hauv lub khoos phis tawj RAM. Tom qab ntawd lub macro ntxiv ib daim ntawv tshiab nrog lub npe muab (kuj hloov pauv ResultSheetNpe) thiab tsim cov ntsiab lus tag nrho (!) ntawm nws raws li cov ntaub ntawv khaws tseg.

Txhawm rau siv macro, siv Visual Basic khawm ntawm lub tab tsim tawm (Tus tsim tawm) los yog keyboard shortcut Alt+F11. Tom qab ntawd peb ntxig ib qho tshiab khoob module los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov cai hauv qab no rau hauv:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'sheet name where the resulting pivot will be displayed" sheet of ResultSheetName = "Pivot. cov npe nrog cov ntxhuav SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'peb tsim ib lub cache rau cov ntxhuav los ntawm cov ntawv los ntawm SheetsNames Nrog ActiveWorkbook ReDim arSQL(1 Rau (UBound(SheetsNames) + 1)) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Xaus Nrog 're-tsim daim ntawv los tso saib cov lus pivot tshwm sim Ntawm qhov yuam kev Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Lub npe = ResultSheetName 'pom cov ntsiab lus tsim cache rau ntawm daim ntawv no Teem objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Teem objPivotCache.Recordset = objRS Teeb objRS = Tsis muaj dab tsi Nrog wsPivot objPivotCache.CreatePivotation(3. objPivotCache = Nothing Range("A3").Select End With End Sub    

Tom qab ntawd cov macro tiav tuaj yeem khiav nrog keyboard shortcut Alt+F8 los yog Macros khawm ntawm lub tab tsim tawm (Tus tsim tawm - Macros).

Cons ntawm txoj kev no:

• Cov ntaub ntawv tsis tau hloov kho vim tias lub cache tsis muaj kev sib txuas rau lub rooj sib tham. Yog tias koj hloov cov ntaub ntawv los, koj yuav tsum khiav lub macro dua thiab tsim cov ntsiab lus ntxiv.
• Thaum hloov tus naj npawb ntawm nplooj ntawv, nws yog ib qho tsim nyog los hloov cov lej macro (array SheetNames).
  

Tab sis thaum kawg peb tau txais cov lus pivot tiag tiag, ua rau ntau qhov sib txawv ntawm cov nplooj ntawv sib txawv:

Voilà!

Technical Note: Yog tias koj tau txais qhov yuam kev xws li "Tus Muab Kev Pabcuam tsis tau sau npe" thaum khiav lub macro, ces feem ntau koj yuav muaj 64-ntsis version ntawm Excel lossis ib qho tsis tiav ntawm Chaw Ua Haujlwm raug teeb tsa (tsis muaj Access). Txhawm rau txhim kho qhov xwm txheej, hloov qhov seem hauv macro code:

	 Tus muab = Microsoft.Jet.OLEDB.4.0;  

mus rau:

	Tus muab = Microsoft.ACE.OLEDB.12.0;  

Thiab rub tawm thiab nruab lub tshuab ua cov ntaub ntawv pub dawb los ntawm Access los ntawm Microsoft lub vev xaib - Microsoft Access Database Engine 2010 Redistributable

Txoj Kev 3: Consolidate PivotTable Wizard los ntawm Qub Versions ntawm Excel

Txoj kev no yog me ntsis outdated, tab sis tseem tsim nyog hais. Raws li kev hais lus, hauv txhua qhov hloov pauv mus txog thiab suav nrog xyoo 2003, muaj kev xaiv hauv PivotTable Wizard los "tsim pivot rau ntau qhov sib koom ua ke". Txawm li cas los xij, ib daim ntawv qhia tsim los ntawm txoj kev no, hmoov tsis, yuav tsuas yog ib qho kev tsis txaus ntseeg ntawm cov ntsiab lus tseem ceeb thiab tsis txhawb ntau "chips" ntawm cov lus pivot:

Nyob rau hauv xws li ib tug pivot, tsis muaj kab lus nyob rau hauv daim teb daim ntawv teev npe, tsis muaj ib tug hloov tau yooj yim qauv teeb tsa, cov txheej txheem ntawm kev siv yog txwv, thiab, feem ntau, tag nrho cov no tsis zoo ib yam li ib tug pivot rooj. Tej zaum yog vim li cas, pib xyoo 2007, Microsoft tshem tawm txoj haujlwm no los ntawm tus qauv sib tham thaum tsim cov lus qhia pivot. Tam sim no qhov no tsuas yog muaj los ntawm kev cai khawm PivotTable Wizard(Pivot Table Wizard), uas, yog xav tau, tuaj yeem ntxiv rau Quick Access Toolbar ntawm Cov ntaub ntawv - Kev xaiv - Customize Quick Access Toolbar - Tag nrho cov lus txib (Cov ntaub ntawv - Kev xaiv - Customize Quick Access Toolbar - Tag nrho cov lus txib):

Tom qab txhaj rau ntawm lub pob ntxiv, koj yuav tsum xaiv qhov kev xaiv tsim nyog ntawm thawj kauj ruam ntawm tus wizard:

Thiab tom qab ntawd hauv lub qhov rais tom ntej, xaiv txhua qhov sib txawv thiab ntxiv rau cov npe dav dav:

Tab sis, dua, qhov no tsis yog tag nrho cov ntsiab lus, yog li tsis txhob cia siab ntau dhau los ntawm nws. Kuv tuaj yeem pom zoo qhov kev xaiv no tsuas yog hauv cov xwm txheej yooj yim heev.

• Tsim Cov Ntawv Qhia nrog PivotTables
• Teeb tsa kev suav hauv PivotTables
• Dab tsi yog macro, siv lawv li cas, qhov twg luam VBA code, thiab lwm yam.
• Cov ntaub ntawv sau los ntawm ntau nplooj ntawv mus rau ib qho (PLEX add-on)