تقسیم کردن یک میز به صفحات

مایکروسافت اکسل ابزارهای زیادی برای جمع آوری داده ها از چندین جدول (از برگه های مختلف یا از فایل های مختلف) دارد: پیوندهای مستقیم، تابع غیر مستقیم (غیر مستقیم)، افزونه های Power Query و Power Pivot و غیره از این طرف سد همه چیز خوب به نظر می رسد.

اما اگر با یک مشکل معکوس روبرو شوید - توزیع داده ها از یک جدول به صفحات مختلف - همه چیز بسیار غم انگیزتر خواهد بود. متأسفانه در حال حاضر هیچ ابزار داخلی متمدنانه ای برای چنین جداسازی داده ها در زرادخانه اکسل وجود ندارد. بنابراین شما باید از یک ماکرو در ویژوال بیسیک استفاده کنید یا از ترکیب ضبط کننده ماکرو + Power Query با کمی «تصحیح فایل» استفاده کنید.

بیایید نگاهی دقیق تر به نحوه اجرای این کار بیندازیم.

فرمول بندی مسئله

ما به عنوان داده های اولیه چنین جدولی با اندازه بیش از 5000 ردیف برای فروش داریم:

وظیفه: توزیع داده های این جدول به تفکیک شهر در برگه های جداگانه این کتاب. آن ها در خروجی، باید در هر ورق فقط آن ردیف‌هایی را از جدولی که فروش در شهر مربوطه انجام شده است، قرار دهید:

آماده

برای اینکه کد ماکرو پیچیده نشود و درک آن تا حد ممکن آسان شود، اجازه دهید چند مرحله مقدماتی را انجام دهیم.

اول، یک جدول جستجوی جداگانه ایجاد کنید، که در آن یک ستون تمام شهرهایی را که می خواهید برگه های جداگانه برای آنها ایجاد کنید فهرست می کند. البته، این فهرست ممکن است شامل تمام شهرهای موجود در داده های منبع نباشد، بلکه فقط شامل شهرهایی باشد که برای آنها به گزارش نیاز داریم. ساده ترین راه برای ایجاد چنین جدولی استفاده از دستور است داده ها - موارد تکراری را حذف کنید (داده ها - حذف موارد تکراری) برای کپی ستون شهر: یا عملکرد یونیک (منحصر بفرد) – اگر آخرین نسخه اکسل 365 را دارید.

از آنجایی که برگه‌های جدید در اکسل به‌طور پیش‌فرض قبل از (در سمت چپ) صفحه فعلی (قبلی) ایجاد می‌شوند، منطقی است که شهرهای این فهرست را به ترتیب نزولی (از Z به A) مرتب کنیم - سپس پس از ایجاد، شهر. برگه ها بر اساس حروف الفبا مرتب خواهند شد.

دوم، пتبدیل هر دو جدول به پویا ("هوشمند") تا کار با آنها آسان تر شود. از دستور استفاده می کنیم صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول) یا میانبر صفحه کلید کلیدهای Ctrl+T. در برگه ای که ظاهر می شود سازنده (طرح) بیایید با آنها تماس بگیریم tablProdaji и تیبل سیتی، به ترتیب:

روش 1. ماکرو برای تقسیم بر ورق

در برگه Advanced توسعه دهنده (توسعه دهنده) روی دکمه کلیک کنید ویژوال بیسیک یا از میانبر صفحه کلید استفاده کنید دگرساز+F11. در پنجره ویرایشگر ماکرو که باز می شود، یک ماژول خالی جدید را از طریق منو وارد کنید درج - ماژول و کد زیر را در آنجا کپی کنید:

Sub Splitter() برای هر سلول در محدوده ("таблГорода") Range("таблПродажи"). فیلد AutoFilter:=3، Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Worksheets سلول بعدی("Данные").ShowAllData پایان فرعی	  

اینجا با یک حلقه برای هر … بعدی عبور از سلول های دایرکتوری را پیاده سازی کرد تیبل سیتی، جایی که برای هر شهر فیلتر می شود (روش فیلتر خودکار) در جدول فروش اصلی و سپس کپی کردن نتایج در برگه جدید ایجاد شده. در طول مسیر، شیت ایجاد شده به همین نام شهر تغییر نام داده و تنظیم خودکار عرض ستون ها برای زیبایی روی آن روشن می شود.

می توانید ماکرو ایجاد شده را در اکسل در تب اجرا کنید توسعه دهنده دکمه ماکرو (توسعه دهنده - ماکروها) یا میانبر صفحه کلید دگرساز+F8.

روش 2. چندین پرس و جو در Power Query ایجاد کنید

روش قبلی، با تمام فشردگی و سادگی، یک اشکال قابل توجه دارد - ورق های ایجاد شده توسط ماکرو زمانی که تغییرات در جدول فروش اصلی ایجاد می شود، به روز نمی شوند. اگر به‌روزرسانی سریع ضروری است، باید از بسته نرم‌افزاری VBA + Power Query استفاده کنید، یا بهتر است بگوییم، با استفاده از یک ماکرو، نه فقط برگه‌هایی با داده‌های استاتیک، بلکه کوئری‌های به‌روز شده Power Query ایجاد کنید.

ماکرو در این مورد تا حدی شبیه به قبلی است (یک چرخه نیز دارد برای هر … بعدی برای تکرار بر روی شهرها در فهرست)، اما در داخل حلقه دیگر فیلتر و کپی وجود نخواهد داشت، بلکه یک کوئری Power Query ایجاد می‌شود و نتایج آن در یک صفحه جدید آپلود می‌شود:

Sub Splitter2() برای هر سلول در محدوده ("جدول شهر") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Chand Type"" = Table.TransformColumnTypes(منبع , {{""دسته"، متن را تایپ کنید}، {""نام"، متن را تایپ کنید}، {""شهر"، متن را تایپ کنید}، {""مدیر"، متن را تایپ کنید}، {""معامله" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""ردیف هایی با فیلتر اعمال شده"" = Table.Se " & _ "lectRows(#""نوع تغییر یافته""، هر ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""ردیف هایی با فیلتر اعمال شده""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0، Source:= _ "OLEDB; ارائه دهنده =Microsoft.Mashup.OleDb.1;منبع داده=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertwordSeleavesells. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.NameEndcell.Name =cell  

پس از راه اندازی، ما همان برگه ها را بر اساس شهر خواهیم دید، اما پرس و جوهای Power Query از قبل ایجاد شده آنها را تشکیل می دهد:

با هر تغییری در داده های منبع، کافی است جدول مربوطه را با دکمه سمت راست ماوس به روز کنید - دستور به روز رسانی و ذخیره (تازه کردن) یا همه شهرها را به صورت انبوه با استفاده از دکمه به‌روزرسانی کنید همه را بروز رسانی کن برگ داده ها (داده ها - بازخوانی همه).

• ماکروها چیست، چگونه می توان از آنها استفاده کرد
• ذخیره برگه های کتاب کار به عنوان فایل های جداگانه
• جمع آوری داده ها از تمام برگه های کتاب در یک جدول