Tabl colyn ar draws ystodau data lluosog

Ffurfio'r broblem

Mae tablau colyn yn un o'r offer mwyaf anhygoel yn Excel. Ond hyd yn hyn, yn anffodus, ni all unrhyw un o'r fersiynau o Excel wneud peth mor syml ac angenrheidiol ar y hedfan ag adeiladu crynodeb ar gyfer nifer o ystodau data cychwynnol sydd wedi'u lleoli, er enghraifft, ar wahanol ddalennau neu mewn tablau gwahanol:

Cyn i ni ddechrau, gadewch i ni egluro un neu ddau o bwyntiau. A priori, credaf fod yr amodau canlynol yn cael eu bodloni yn ein data:

• Gall tablau gael unrhyw nifer o resi gydag unrhyw ddata, ond rhaid iddynt gael yr un pennawd.
• Ni ddylai fod unrhyw ddata ychwanegol ar y dalennau gyda thablau ffynhonnell. Un ddalen - un bwrdd. Er mwyn rheoli, rwy'n eich cynghori i ddefnyddio llwybr byr bysellfwrdd Ctrl+diwedd, sy'n eich symud i'r gell a ddefnyddiwyd ddiwethaf yn y daflen waith. Yn ddelfrydol, hon ddylai fod y gell olaf yn y tabl data. Os pan fyddwch yn clicio ar Ctrl+diwedd mae unrhyw gell wag i'r dde neu o dan y tabl yn cael ei amlygu - dilëwch y colofnau gwag hyn i'r dde neu'r rhesi o dan y tabl ar ôl y tabl a chadwch y ffeil.

Dull 1: Adeiladu tablau ar gyfer colyn gan ddefnyddio Power Query

Gan ddechrau o fersiwn 2010 ar gyfer Excel, mae yna ychwanegiad Power Query am ddim a all gasglu a thrawsnewid unrhyw ddata ac yna ei roi fel ffynhonnell ar gyfer adeiladu tabl colyn. Nid yw datrys ein problem gyda chymorth yr ychwanegiad hwn yn anodd o gwbl.

Yn gyntaf, gadewch i ni greu ffeil wag newydd yn Excel - bydd cydosod yn digwydd ynddi ac yna bydd tabl colyn yn cael ei greu ynddi.

Yna ar y tab Dyddiad (os oes gennych Excel 2016 neu ddiweddarach) neu ar y tab Ymholiad Pwer (os oes gennych Excel 2010-2013) dewiswch y gorchymyn Creu Ymholiad - O Ffeil - Excel (Cael Data - O ffeil - Excel) a nodwch y ffeil ffynhonnell gyda'r tablau i'w casglu:

Yn y ffenestr sy'n ymddangos, dewiswch unrhyw ddalen (does dim ots pa un) a gwasgwch y botwm isod Newid (Golygu):

Dylai ffenestr Power Query Query Editor agor ar ben Excel. Ar ochr dde'r ffenestr ar y panel Paramedrau Cais dileu pob cam a grëwyd yn awtomatig ac eithrio'r cyntaf - ffynhonnell (Ffynhonnell):

Nawr rydym yn gweld rhestr gyffredinol o'r holl daflenni. Os, yn ogystal â thaflenni data, mae rhai dalennau ochr eraill yn y ffeil, yna ar y cam hwn ein tasg yw dewis y dalennau hynny y mae angen llwytho gwybodaeth ohonynt yn unig, heb gynnwys yr holl daflenni eraill sy'n defnyddio'r hidlydd ym mhennyn y tabl:

Dileu pob colofn ac eithrio colofn Dyddiadtrwy dde-glicio ar bennawd colofn a dewis Dileu colofnau eraill (Dileu colofnau eraill):

Yna gallwch ehangu cynnwys y tablau a gasglwyd trwy glicio ar y saeth ddwbl ar frig y golofn (blwch ticio Defnyddiwch enw'r golofn wreiddiol fel rhagddodiad gallwch chi ei ddiffodd):

Os gwnaethoch bopeth yn gywir, yna dylech weld cynnwys yr holl dablau a gasglwyd un o dan y llall:

Erys i godi'r rhes gyntaf i bennawd y tabl gyda'r botwm Defnyddiwch y llinell gyntaf fel penawdau (Defnyddiwch y rhes gyntaf fel penawdau) tab Hafan (Cartref) a thynnu penawdau tabl dyblyg o'r data gan ddefnyddio hidlydd:

Arbedwch bopeth a wneir gyda'r gorchymyn Cau a llwytho - Cau a llwytho i mewn… (Cau a Llwyth - Cau a Llwytho i…) tab Hafan (Cartref), ac yn y ffenestr sy'n agor, dewiswch yr opsiwn Cysylltiad yn unig (Cysylltiad yn unig):

Popeth. Erys dim ond i adeiladu crynodeb. I wneud hyn, ewch i'r tab Mewnosod – PivotTable (Mewnosod — Tabl Colyn), dewiswch yr opsiwn Defnyddiwch ffynhonnell ddata allanol (Defnyddiwch ffynhonnell ddata allanol)ac yna trwy glicio ar y botwm Dewiswch gysylltiad, ein cais. Mae creu a chyfluniad pellach o'r colyn yn digwydd mewn ffordd gwbl safonol trwy lusgo'r meysydd sydd eu hangen arnom i'r rhesi, y colofnau a'r ardal werthoedd:

Os bydd y data ffynhonnell yn newid yn y dyfodol neu os ychwanegir ychydig mwy o ddalennau storio, yna bydd yn ddigon i ddiweddaru'r ymholiad a'n crynodeb gan ddefnyddio'r gorchymyn Adnewyddu'r cyfan tab Dyddiad (Data - Adnewyddu Pawb).

Dull 2. Rydym yn uno tablau gyda'r gorchymyn UNION SQL mewn macro

Mae datrysiad arall i'n problem yn cael ei gynrychioli gan y macro hwn, sy'n creu set ddata (storfa) ar gyfer y tabl colyn gan ddefnyddio'r gorchymyn UNED Iaith ymholiad SQL. Mae'r gorchymyn hwn yn cyfuno tablau o bob un a nodir yn yr arae Enwau Taflenni taflenni o'r llyfr yn un tabl data. Hynny yw, yn lle copïo a gludo yn gorfforol amrywio o wahanol ddalennau i un, rydym yn gwneud yr un peth yn RAM y cyfrifiadur. Yna mae'r macro yn ychwanegu dalen newydd gyda'r enw a roddir (amrywiol ResultsheetName) ac yn creu crynodeb llawn (!) arno yn seiliedig ar y celc a gasglwyd.

I ddefnyddio macro, defnyddiwch y botwm Visual Basic ar y tab datblygwr (Datblygwr) neu lwybr byr bysellfwrdd Alt+F11. Yna rydyn ni'n mewnosod modiwl gwag newydd trwy'r ddewislen Mewnosod – Modiwl a chopïwch y cod canlynol yno:

Is Newydd_Multi_Table_Pivot() Dim i Mor Hir Dim arSQL() Fel Llinynnol Dim objPivotCache Fel PivotCache Dim objRS Fel Gwrthrych Dim CanlyniadSheetName Fel Llinyn Dim Enwau Taflenni Fel Enw dalen lle dangosir y colyn canlyniadol ResultSheetName = "Colyn" 'arae o ddalen enwau gyda thablau ffynhonnell SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") 'rydym yn ffurfio storfa ar gyfer tablau o ddalennau o Enwau Dalenni Gyda ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Ar gyfer i = LBound (SheetsNames) I UBound(SheetsNames) arSQL(i + 1) = "SELECT * O [" & SheetsNames(i) & "$]" Nesaf i Gosod objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Ymunwch $(Array(" Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,";Priodweddau Estynedig=""Excel 8.0;" "" ), vbNullString ) Diwedd Gyda ' ail-greu'r ddalen i ddangos y tabl colyn canlyniadol Ar Gwall Ail-ddechrau Cais Nesaf.DisplayAlerts = Taflenni Gwaith Ffug(ResultSheetName).Dileu Set wsPivot = Taflenni Gwaith.Ychwanegu wsPivo t. Name = ResultSheetName 'dangos y crynodeb cache a gynhyrchir ar y ddalen hon Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Gosod objPivotCache.Recordset = objRS Set objRS = Dim Gyda wsPivot objPivotCache.CreatePivotTable" Set" TableDivot. objPivotCache = Dim Ystod ("A3").Dewis Diwedd Gyda Diwedd Is    

Yna gellir rhedeg y macro gorffenedig gyda llwybr byr bysellfwrdd Alt+F8 neu'r botwm Macros ar y tab datblygwr (Datblygwr - Macros).

Anfanteision y dull hwn:

• Nid yw'r data yn cael ei ddiweddaru oherwydd nad oes gan y storfa unrhyw gysylltiad â'r tablau ffynhonnell. Os byddwch yn newid y data ffynhonnell, rhaid i chi redeg y macro eto ac adeiladu'r crynodeb eto.
• Wrth newid nifer y dalennau, mae angen golygu'r cod macro (arae Enwau Taflenni).
  

Ond yn y diwedd cawn fwrdd colyn llawn go iawn, wedi'i adeiladu ar sawl ystod o wahanol ddalennau:

Voilà!

Nodyn technegol: os cewch wall fel “Darparwr heb ei gofrestru” wrth redeg y macro, yna yn fwyaf tebygol mae gennych fersiwn 64-bit o Excel neu mae fersiwn anghyflawn o Office wedi'i gosod (dim Mynediad). I drwsio'r sefyllfa, disodli'r darn yn y cod macro:

	 Darparwr=Microsoft.Jet.OLEDB.4.0;  

i:

	Darparwr=Microsoft.ACE.OLEDB.12.0;  

A lawrlwythwch a gosodwch yr injan prosesu data am ddim o Access o wefan Microsoft - Microsoft Access Database Engine 2010 Redistributable

Dull 3: Cyfuno Dewin PivotTable o Hen Fersiynau Excel

Mae'r dull hwn ychydig yn hen ffasiwn, ond mae'n dal yn werth ei grybwyll. A siarad yn ffurfiol, ym mhob fersiwn hyd at a chan gynnwys 2003, roedd opsiwn yn y Dewin PivotTable i “adeiladu colyn ar gyfer sawl ystod cydgrynhoi”. Fodd bynnag, yn anffodus, bydd adroddiad a luniwyd fel hyn, yn anffodus, yn edrych yn druenus ar grynodeb cyflawn gwirioneddol ac nid yw’n cefnogi llawer o “sglodion” tablau colyn confensiynol:

Mewn colyn o'r fath, nid oes unrhyw benawdau colofn yn y rhestr caeau, nid oes gosodiad strwythur hyblyg, mae'r set o swyddogaethau a ddefnyddir yn gyfyngedig, ac, yn gyffredinol, nid yw hyn i gyd yn debyg iawn i dabl colyn. Efallai mai dyna pam, gan ddechrau yn 2007, tynnodd Microsoft y swyddogaeth hon o'r ymgom safonol wrth greu adroddiadau tabl colyn. Nawr mae'r nodwedd hon ar gael trwy fotwm wedi'i deilwra yn unig Dewin PivotTable(Dewin Tabl Colyn), y gellir, os dymunir, ei ychwanegu at y Bar Offer Mynediad Cyflym trwy Ffeil - Opsiynau - Addasu Bar Offer Mynediad Cyflym - Pob Gorchymyn (Ffeil - Opsiynau - Addasu Bar Offer Mynediad Cyflym - Pob Gorchymyn):

Ar ôl clicio ar y botwm ychwanegol, mae angen i chi ddewis yr opsiwn priodol ar gam cyntaf y dewin:

Ac yna yn y ffenestr nesaf, dewiswch bob ystod yn ei dro a'i ychwanegu at y rhestr gyffredinol:

Ond, unwaith eto, nid yw hwn yn grynodeb llawn, felly peidiwch â disgwyl gormod ohono. Dim ond mewn achosion syml iawn y gallaf argymell yr opsiwn hwn.

• Creu Adroddiadau gyda PivotTables
• Gosod cyfrifiadau yn PivotTables
• Beth yw macros, sut i'w defnyddio, ble i gopïo cod VBA, ac ati.
• Casglu data o dudalennau lluosog i un (ychwanegiad PLEX)