تقسيم الجدول إلى أوراق

يحتوي Microsoft Excel على العديد من الأدوات لجمع البيانات من عدة جداول (من أوراق مختلفة أو من ملفات مختلفة): روابط مباشرة ، وظيفة غير مباشر (غير مباشر)والوظائف الإضافية Power Query و Power Pivot ، إلخ. من هذا الجانب من الحاجز ، يبدو كل شيء جيدًا.

ولكن إذا واجهت مشكلة عكسية - نشر البيانات من جدول واحد إلى أوراق مختلفة - فسيكون كل شيء أكثر حزنًا. في الوقت الحالي ، لا توجد أدوات مدمجة متحضرة لفصل البيانات في ترسانة Excel ، للأسف. لذلك سيتعين عليك استخدام ماكرو في Visual Basic ، أو استخدام تركيبة مسجل الماكرو + Power Query مع القليل من "تحسين الملف" بعد ذلك.

دعونا نلقي نظرة فاحصة على كيفية تنفيذ ذلك.

صياغة المشكلة

لدينا بيانات أولية مثل هذا الجدول بحجم أكثر من 5000 صف للمبيعات:

المهمة: لتوزيع البيانات من هذا الجدول حسب المدينة على أوراق منفصلة من هذا الكتاب. أولئك. عند الإخراج ، يجب أن تحصل في كل ورقة فقط على تلك الصفوف من الجدول حيث تم البيع في المدينة المقابلة:

إعداد

من أجل عدم تعقيد كود الماكرو وجعله سهل الفهم قدر الإمكان ، دعنا ننفذ بضع خطوات تحضيرية.

أولا، إنشاء جدول بحث منفصل، حيث يسرد عمود واحد جميع المدن التي تريد إنشاء أوراق منفصلة لها. بالطبع ، قد لا يحتوي هذا الدليل على جميع المدن الموجودة في بيانات المصدر ، ولكن فقط تلك التي نحتاج إلى تقارير عنها. أسهل طريقة لإنشاء مثل هذا الجدول هي استخدام الأمر البيانات - إزالة التكرارات (البيانات - إزالة التكرارات) لنسخ العمود المدينة أو وظيفة فريدة من نوعها (فريدة من نوعها) - إذا كان لديك أحدث إصدار من Excel 365.

نظرًا لأن الأوراق الجديدة في Excel يتم إنشاؤها افتراضيًا قبل (على يسار) الصفحة الحالية (السابقة) ، فمن المنطقي أيضًا فرز المدن في هذا الدليل بترتيب تنازلي (من Z إلى A) - ثم بعد الإنشاء ، المدينة سيتم ترتيب الأوراق أبجديًا.

ثانيا، пتحويل كلا الجدولين إلى ديناميكي ("ذكي") لتسهيل العمل معهم. نحن نستخدم الأمر الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول) أو اختصار لوحة المفاتيح CTRL+T. في علامة التبويب التي تظهر منشئ (التصميم) دعنا نسميهم تابل برودجي и TableCityعلى التوالي:

الطريقة 1. ماكرو للقسمة على الأوراق

في علامة التبويب خيارات متقدمة المطور (مطور) انقر على الزر Visual Basic أو استخدم اختصار لوحة المفاتيح قديم+F11. في نافذة محرر الماكرو التي تفتح ، أدخل وحدة فارغة جديدة من خلال القائمة إدراج - وحدة وانسخ الكود التالي هناك:

الفاصل الفرعي () لكل خلية في النطاق ("таблГорода") النطاق ("таблПродажи"). حقل التصفية التلقائية: = 3 ، المعايير 1: = نطاق القيمة cell.Value ("таблПродажи [#All]"). SpecialCells (xlCell. Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit أوراق عمل الخلية التالية ("Данные"). ShowAllData End Sub	  

هنا مع حلقة لكل ... التالي نفذت المرور عبر خلايا الدليل TableCity، حيث يتم تصفيتها لكل مدينة (طريقة فلتر السيارات) في جدول المبيعات الأصلي ثم نسخ النتائج إلى الورقة المنشأة حديثًا. على طول الطريق ، تتم إعادة تسمية الورقة التي تم إنشاؤها إلى نفس اسم المدينة ويتم تشغيل التهيئة التلقائية لعرض الأعمدة للجمال عليها.

يمكنك تشغيل الماكرو الذي تم إنشاؤه في Excel في علامة التبويب المطور زر وحدات الماكرو (المطور - وحدات الماكرو) أو اختصار لوحة المفاتيح قديم+F8.

الطريقة الثانية: إنشاء استعلامات متعددة في Power Query

الطريقة السابقة ، على الرغم من كونها مدمجة وبسيطة ، لها عيب كبير - لا يتم تحديث الأوراق التي تم إنشاؤها بواسطة الماكرو عند إجراء تغييرات في جدول المبيعات الأصلي. إذا كان التحديث الفوري ضروريًا ، فسيتعين عليك استخدام حزمة VBA + Power Query ، أو بدلاً من ذلك ، إنشاء باستخدام ماكرو ليس فقط الأوراق التي تحتوي على بيانات ثابتة ، ولكن استعلامات Power Query المحدثة.

الماكرو في هذه الحالة مشابه جزئيًا للسابق (يحتوي أيضًا على دورة لكل ... التالي للتكرار على المدن في الدليل) ، ولكن داخل الحلقة لن يكون هناك تصفية ونسخ ، ولكن إنشاء استعلام Power Query وتحميل نتائجه إلى ورقة جديدة:

Sub Splitter2 () لكل خلية في النطاق ("جدول المدينة") ActiveWorkbook.Queries.Add Name: = cell.Value ، الصيغة: = _ "let" & Chr (13) & "" & Chr (10) & "المصدر = Excel.CurrentWorkbook () {[Name = "" TableSales ""]} [المحتوى] ، "& Chr (13) &" "& Chr (10) &" # "" Changed Type "" = Table.TransformColumnTypes (المصدر ، {{"الفئة" "، اكتب النص} ، {" الاسم "" ، اكتب النص} ، {"المدينة" "، اكتب النص} ، {" "المدير" "، اكتب النص} ، {" "صفقة date ""، type datetime}، {"" Cost ""، type number}})، "& Chr (13) &" "& Chr (10) &" # "" صفوف مع تطبيق الفلتر "" = Table.Se "& _" lectRows (# "" Changed type "" ، كل ([City] = "" "& cell.Value &" "")) "& Chr (13) &" "& Chr (10) &" in "& Chr (13) &" "& Chr (10) &" # "" صفوف مع تطبيق عامل التصفية "" "ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add (SourceType: = 0 ، المصدر: = _" OLEDB؛ Provider = Microsoft.Mashup.OleDb.1 ؛ مصدر البيانات = $ Workbook $ ؛ الموقع = "& cell.Value &" ؛ الخصائص الممتدة = "" "" _ ، الوجهة: = النطاق ("$ A $ 1"). QueryTable .CommandType = xlCmd Sql .CommandText = Array ("SELECT *من ["& cell.Value &"] ") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery: = False End With ActiveSheet.Name = cell.Value Next cell End Sub  

بعد إطلاقه ، سنرى نفس الأوراق حسب المدينة ، لكن استعلامات Power Query التي تم إنشاؤها بالفعل ستشكلها:

مع أي تغييرات في البيانات المصدر ، سيكون كافياً لتحديث الجدول المقابل باستخدام زر الفأرة الأيمن - الأمر تحديث وحفظ (ينعش) أو تحديث جميع المدن دفعة واحدة باستخدام الزر تحديث جميع علامة التبويب البيانات (البيانات - تحديث الكل).

• ما هي وحدات الماكرو وكيفية إنشائها واستخدامها
• حفظ أوراق المصنف كملفات منفصلة
• تجميع البيانات من جميع أوراق الكتاب في جدول واحد