Pipin tabili sinu awọn iwe

Microsoft Excel ni ọpọlọpọ awọn irinṣẹ fun gbigba data lati awọn tabili pupọ (lati oriṣiriṣi awọn iwe tabi awọn faili oriṣiriṣi): awọn ọna asopọ taara, iṣẹ TỌN (AIRỌ), Ibeere agbara ati awọn afikun Pivot agbara, bbl Lati ẹgbẹ yii ti barricade, ohun gbogbo dara dara.

Ṣugbọn ti o ba ṣiṣe sinu iṣoro onidakeji - ntan data lati tabili kan si awọn iwe oriṣiriṣi - lẹhinna ohun gbogbo yoo jẹ ibanujẹ pupọ. Ni akoko yii, ko si awọn irinṣẹ ti a ṣe sinu ọlaju fun iru iyapa data ninu ohun ija ti Excel, laanu. Nitorinaa iwọ yoo ni lati lo Makiro ni Ipilẹ wiwo, tabi lo agbohunsilẹ Makiro + Apapo Ibeere Agbara pẹlu “itunṣe faili” diẹ lẹhin.

Jẹ ki a ṣe akiyesi diẹ sii bi eyi ṣe le ṣe imuse.

Ilana ti iṣoro naa

A ni bi data ibẹrẹ iru tabili kan pẹlu iwọn diẹ sii ju awọn ori ila 5000 fun tita:

Iṣẹ-ṣiṣe: lati pin kaakiri data lati tabili yii nipasẹ ilu lori awọn iwe lọtọ ti iwe yii. Awon. ni abajade, o nilo lati gba lori iwe kọọkan nikan awọn ori ila lati tabili nibiti tita naa wa ni ilu ti o baamu:

Mura

Ni ibere ki o má ba ṣe idiju koodu macro ki o jẹ ki o rọrun lati ni oye bi o ti ṣee, jẹ ki a ṣe awọn igbesẹ igbaradi meji kan.

Akọkọ, awọn ṣẹda tabili wiwa lọtọ, nibiti iwe kan yoo ṣe atokọ gbogbo awọn ilu fun eyiti o fẹ ṣẹda awọn iwe lọtọ. Nitoribẹẹ, itọsọna yii le ma ni gbogbo awọn ilu ti o wa ninu data orisun, ṣugbọn awọn ti a nilo awọn ijabọ nikan. Ọna to rọọrun lati ṣẹda iru tabili ni lati lo aṣẹ naa Data – Yọ Duplicates (Data - Yọ awọn ẹda-iwe kuro) fun iwe daakọ ikunsinu tabi iṣẹ UNIK (OTO) - ti o ba ni ẹya tuntun ti Excel 365.

Niwọn igba ti awọn iwe tuntun ni Excel ti ṣẹda nipasẹ aiyipada ṣaaju (si apa osi) lọwọlọwọ (tẹlẹ) ọkan, o tun jẹ oye lati to awọn ilu ni itọsọna yii ni ilana ti sọkalẹ (lati Z si A) - lẹhinna lẹhin ẹda, ilu naa. sheets yoo wa ni idayatọ adibi.

Keji, awọn пyi awọn mejeeji tabili to ìmúdàgba ("ọlọgbọn") lati jẹ ki o rọrun lati ṣiṣẹ pẹlu wọn. A lo aṣẹ naa Ile – Kika bi tabili (Ile - Ọna kika bi Tabili) tabi ọna abuja keyboard Konturolu+T. Lori taabu ti o han Alakoso (Apẹrẹ) ká pè wọ́n tablProdaji и TableCity, lẹsẹsẹ:

Ọna 1. Makiro fun pipin nipasẹ awọn iwe

Lori To ti ni ilọsiwaju taabu developer (Olùgbéejáde) tẹ lori bọtini visual Ipilẹ tabi lo ọna abuja keyboard alt+F11. Ninu ferese olootu Makiro ti o ṣii, fi module titun ti o ṣofo sii nipasẹ akojọ aṣayan Fi sii - Module ati daakọ koodu atẹle nibẹ:

Sub Splitter () Fun kọọkan cell Ni Range ("таблГорода") Ibiti ("таблПродажи") .AutoFilter Field: = 3, Criteria1: = cell.Iye Range ("таблПродажи[#Gbogbo]").SpecialCells(xlCell). Sheets.Fi ActiveSheet.Paste ActiveSheet.Name = cell.Iye ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Ohun").ShowAllData Ipari Sub	  

Nibi pẹlu lupu kan Fun Kọọkan… Next muse awọn aye nipasẹ awọn sẹẹli ti awọn liana TableCity, nibo ni ilu kọọkan ti wa ni filtered (ọna AutoFilter) ninu tabili tita atilẹba ati lẹhinna didakọ awọn abajade si iwe tuntun ti a ṣẹda. Ni ọna, dì ti o ṣẹda ti wa ni lorukọmii si orukọ kanna ti ilu naa ati pe o ni ibamu pẹlu iwọn awọn ọwọn fun ẹwa ti wa ni titan lori rẹ.

O le ṣiṣe macro ti o ṣẹda ni Excel lori taabu developer bọtìnnì Makiro (Olùgbéejáde - Macros) tabi ọna abuja keyboard alt+F8.

Ọna 2. Ṣẹda awọn ibeere pupọ ni Ibeere Agbara

Ọna ti tẹlẹ, fun gbogbo iwapọ ati ayedero rẹ, ni apadabọ pataki - awọn iwe ti a ṣẹda nipasẹ Makiro ko ni imudojuiwọn nigbati awọn ayipada ṣe ni tabili tita atilẹba. Ti imudojuiwọn lori fo jẹ pataki, lẹhinna o yoo ni lati lo lapapo Ibeere Agbara VBA +, tabi dipo, ṣẹda nipa lilo Makiro kan kii ṣe awọn iwe pẹlu data aimi, ṣugbọn awọn ibeere ibeere Agbara imudojuiwọn.

Makiro ninu ọran yii jẹ iru kan si ti iṣaaju (o tun ni iyipo kan Fun Kọọkan… Next lati ṣe atunṣe lori awọn ilu ni itọsọna), ṣugbọn inu lupu kii yoo jẹ sisẹ ati didakọ mọ, ṣugbọn ṣiṣẹda ibeere Ibeere Agbara ati ikojọpọ awọn abajade rẹ si iwe tuntun kan:

Sub Splitter2 () Fun alagbeka kọọkan Ni Ibiti ("Tabili Ilu") ActiveWorkbook.Queries.Fikun Orukọ:=cell.Value, Formula:= _"let" & Chr(13) &"" & Chr(10) &" Orisun = Excel.CurrentWorkbook(){[Name=""TableSales"][Akoonu]," & Chr(13) & "" & Chr(10) &" #""Iru Ayipada"" = Tabili.TransformColumnTypes(Orisun) , {{""Ẹka"", tẹ ọrọ}, {""Orukọ"', tẹ ọrọ}, {""Ilu", tẹ ọrọ}, {""Oluṣakoso"", tẹ ọrọ}, {""Deal ọjọ "", tẹ datetime}, {""Iye owo", tẹ nọmba}}), & Chr(13) & "" & Chr(10) & " #"" Awọn ori ila pẹlu àlẹmọ loo"" = Table.Se " & _ "lectRows(#""Ti a yipada iru"", kọọkan ([City] = """ & cell.Iye & """)" & Chr(13) & "" & Chr(10) & "ni " & Chr (13) & "" & Chr (10) & " #"" Awọn ori ila pẹlu àlẹmọ loo"" ActiveWorkbook.Worksheets.Fikun Pẹlu ActiveSheet.ListObjects.Add (OrisunType: = 0, Orisun: = _ "OLEDB; Olupese = Microsoft.Mashup.OleDb.1; Data Orisun = $ Workbook $; Location = " & cell.Value & "; Extended Properties = """"" _ , Ibi: = Ibiti ("$ A $ 1"). QueryTable .CommandType = xlCmd Sql .CommandText = Akopọ("Yan *LATI [" & cell.Iye & "]") .RowNumbers = Eke .FillAdjacentFormulas = Eke .PreserveFormatting = Otitọ .RefreshOnFileOpen = Eke .BackgroundQuery = Otitọ .RefreshStyle = False xlInsertDeleteCells .Fipamọ. SaveData = Òótọ́ .AdjustColumnWidth = Òótọ́ .RefreshPeriod = 0 .PreserveColumnInfo = Òótọ́ .ListObject.DisplayName = cell.Iye .Refresh BackgroundQuery:=Ipari eke Pẹlu ActiveSheet.Name = cell.Iyele cell Ipari Ipin Sub.  

Lẹhin ifilọlẹ rẹ, a yoo rii awọn iwe kanna nipasẹ ilu, ṣugbọn ti ṣẹda tẹlẹ Awọn ibeere Ibeere Agbara yoo ṣe agbekalẹ wọn:

Pẹlu eyikeyi awọn ayipada ninu data orisun, yoo to lati ṣe imudojuiwọn tabili ti o baamu pẹlu bọtini asin ọtun - aṣẹ naa Imudojuiwọn & Fipamọ (Tọtun) tabi ṣe imudojuiwọn gbogbo awọn ilu ni ẹẹkan ni olopobobo nipa lilo bọtini Mu Gbogbo rẹ ṣiṣẹ taabu data (Data - Tun gbogbo rẹ sọ).

• Kini macros, bii o ṣe le ṣẹda ati lo wọn
• Nfipamọ awọn iwe iṣẹ bi awọn faili lọtọ
• Gbigba data lati gbogbo awọn iwe ti iwe sinu tabili kan