Rhannu bwrdd yn ddalennau

Mae gan Microsoft Excel lawer o offer ar gyfer casglu data o sawl tabl (o wahanol daflenni neu o wahanol ffeiliau): dolenni uniongyrchol, swyddogaeth INDIRECT (ANUNIONGYRCHOL), Power Query a Power Pivot ychwanegion, ac ati O'r ochr hon i'r barricade, mae popeth yn edrych yn dda.

Ond os ydych chi'n dod ar draws problem wrthdro - gan wasgaru data o un tabl i wahanol ddalennau - yna bydd popeth yn llawer tristach. Ar hyn o bryd, yn anffodus, nid oes unrhyw offer adeiledig gwaraidd ar gyfer gwahanu data o'r fath yn arsenal Excel. Felly bydd yn rhaid i chi ddefnyddio macro yn Visual Basic, neu ddefnyddio'r recordydd macro + Power Query cyfuniad gydag ychydig o "gloywi ffeil" ar ôl.

Gadewch i ni edrych yn agosach ar sut y gellir gweithredu hyn.

Ffurfio'r broblem

Mae gennym ni fel data cychwynnol dabl o'r fath gyda maint o fwy na 5000 o resi ar gyfer gwerthiannau:

Rhannu bwrdd yn ddalennau

Tasg: dosbarthu’r data o’r tabl hwn fesul dinas ar ddalennau ar wahân o’r llyfr hwn. Y rhai. yn yr allbwn, mae angen i chi fynd ar bob dalen dim ond y rhesi hynny o'r tabl lle'r oedd y gwerthiant yn y ddinas gyfatebol:

Rhannu bwrdd yn ddalennau

Paratoi

Er mwyn peidio â chymhlethu'r cod macro a'i wneud mor hawdd i'w ddeall â phosibl, gadewch i ni berfformio ychydig o gamau paratoadol.

Yn gyntaf, mae'r creu tabl chwilio ar wahân, lle bydd colofn sengl yn rhestru'r holl ddinasoedd yr ydych am greu dalennau ar wahân ar eu cyfer. Wrth gwrs, efallai na fydd y cyfeiriadur hwn yn cynnwys yr holl ddinasoedd sy'n bresennol yn y data ffynhonnell, ond dim ond y rhai y mae angen adroddiadau arnynt. Y ffordd hawsaf i greu tabl o'r fath yw defnyddio'r gorchymyn Data - Dileu Dyblygiadau (Data - Dileu copïau dyblyg) am gopi colofn Dinas neu swyddogaeth UNIK (UNIQUE) – os oes gennych y fersiwn diweddaraf o Excel 365.

Gan fod dalennau newydd yn Excel yn cael eu creu yn ddiofyn cyn (i'r chwith) yr un gyfredol (blaenorol), mae hefyd yn gwneud synnwyr didoli'r dinasoedd yn y cyfeiriadur hwn mewn trefn ddisgynnol (o Z i A) - yna ar ôl creu, y ddinas bydd y dalennau'n cael eu trefnu yn nhrefn yr wyddor.

Yn ail, mae'r пtrosi'r ddau dabl i ddeinamig (“smart”) i’w gwneud yn haws gweithio gyda nhw. Rydym yn defnyddio'r gorchymyn Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl) neu lwybr byr bysellfwrdd Ctrl+T. Ar y tab sy'n ymddangos Constructor (Dylunio) gadewch i ni eu galw tablProdaji и Dinas Tabl, yn y drefn honno:

Rhannu bwrdd yn ddalennau

Dull 1. Macro ar gyfer rhannu â dalennau

Ar y tab Advanced datblygwr (Datblygwr) cliciwch ar y botwm Visual Basic neu ddefnyddio llwybr byr bysellfwrdd Alt+F11. Yn y ffenestr macro golygydd sy'n agor, mewnosodwch fodiwl gwag newydd trwy'r ddewislen Mewnosod – Modiwl a chopïwch y cod canlynol yno:

Is-holltwr() Ar gyfer Pob cell Mewn Ystod ("таблГорода") Ystod ("таблПродажи"). Maes AutoFilter:=3, Meini Prawf1:=Ystod cell.Value("таблПродажи[#All]").SpecialCells(VisCellspy). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Taflenni Gwaith cell nesaf("Данные").ShowAllData End Is

Yma gyda dolen Ar gyfer Pob … Nesaf gweithredu'r darn trwy gelloedd y cyfeiriadur Dinas Tabl, lle ar gyfer pob dinas mae'n cael ei hidlo (dull AutoFilter) yn y tabl gwerthu gwreiddiol ac yna copïo'r canlyniadau i'r ddalen newydd ei chreu. Ar hyd y ffordd, mae'r ddalen a grëwyd yn cael ei ailenwi i'r un enw'r ddinas ac mae gosod lled y colofnau ar gyfer harddwch yn cael ei droi arni yn awtomatig.

Gallwch chi redeg y macro a grëwyd yn Excel ar y tab datblygwr botwm Macros (Datblygwr - Macros) neu lwybr byr bysellfwrdd Alt+F8.

Dull 2. Creu ymholiadau lluosog yn Power Query

Mae gan y dull blaenorol, er ei holl grynodeb a symlrwydd, anfantais sylweddol - nid yw'r dalennau a grëwyd gan y macro yn cael eu diweddaru pan wneir newidiadau yn y tabl gwerthu gwreiddiol. Os oes angen diweddaru'r hedfan, yna bydd yn rhaid i chi ddefnyddio'r bwndel VBA + Power Query, neu yn hytrach, creu gan ddefnyddio macro nid yn unig dalennau gyda data statig, ond ymholiadau Power Query wedi'u diweddaru.

Mae'r macro yn yr achos hwn yn rhannol debyg i'r un blaenorol (mae ganddo gylchred hefyd Ar gyfer Pob … Nesaf i ailadrodd dros ddinasoedd yn y cyfeiriadur), ond y tu mewn i'r ddolen ni fydd hidlo a chopïo mwyach, ond yn creu ymholiad Power Query a llwytho ei ganlyniadau i ddalen newydd:

Is-hollti2() Ar gyfer Pob cell Mewn Ystod ("Tabl y Ddinas") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) &"" & Chr(10) & " Ffynhonnell = Excel.CurrentWorkbook(){[Name=""TablSales""]}[Cynnwys]," & Chr(13) &"" & Chr(10) &" #""Newid Math"" = Tabl.TransformColumnTypes(Ffynhonnell , {{""Categori"", teipio testun}, {""Enw"", teipio testun}, {""Dinas", teipio testun}, {""Rheolwr"", teipio testun}, {""Bargen date" ", type datetime}, {" "Cost"", math rhif }}), " &Chr(13) &" "&Chr(10) &" #" "Rhesi gyda hidlydd wedi'i gymhwyso"" = Tabl.Se. " & _ " lectRows(#" "Math wedi'i newid"", pob un ([Dinas] = """ & cell.Value & """))" & Chr(13) &" "&Chr(10) &" in " &Chr(13) &" "&Chr(10) &" #" "Rhesi gyda'r hidlydd wedi'i gymhwyso" "" "ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ " OLEDB; Darparwr =Microsoft.Mashup.OleDb.1;Ffynhonnell Data=$Llyfr Gwaith$;Lleoliad="&cell.Value&";Priodweddau Estynedig="""" _ , Cyrchfan:=Ystod("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Arae( "SELECT *O ["&cell.Value&"]") .RowNumbers = Gau .FillAdjacentFormulas = Gau .PreserveFormatting = Gwir .RefreshOnFileOpen = Gau .BackgroundQuery = Gwir .RefreshStyle = xlInsertDeleteCells .SavePassword = Ffug . SaveData = Gwir .AdjustColumnWidth = Gwir .RefreshPeriod = 0 .PreserveColumnInfo = Gwir .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Diwedd Anghywir Gyda ActiveSheet.Name = cell.Value Diwedd cell Nesaf Is

Ar ôl ei lansio, byddwn yn gweld yr un dalennau fesul dinas, ond bydd ymholiadau Power Query a grëwyd eisoes yn eu ffurfio:

Rhannu bwrdd yn ddalennau

Gydag unrhyw newidiadau yn y data ffynhonnell, bydd yn ddigon i ddiweddaru'r tabl cyfatebol gyda botwm dde'r llygoden - y gorchymyn Diweddaru ac Arbed (Adnewyddu) neu ddiweddaru pob dinas ar unwaith mewn swmp gan ddefnyddio'r botwm Diweddariad i Bawb tab Dyddiad (Data - Adnewyddu Pawb).

  • Beth yw macros, sut i'w creu a'u defnyddio
  • Cadw taflenni llyfr gwaith fel ffeiliau ar wahân
  • Casglu data o bob dalen o'r llyfr mewn un tabl

Gadael ymateb