פיצול שולחן לסדינים

ל-Microsoft Excel כלים רבים לאיסוף נתונים ממספר טבלאות (מגיליונות שונים או מקבצים שונים): קישורים ישירים, פונקציה עקיף (עקיף), Power Query ו-Power Pivot תוספות וכו'. מהצד הזה של המתרס הכל נראה טוב.

אבל אם אתה נתקל בבעיה הפוכה - הפצת נתונים מטבלה אחת לגיליונות שונים - אז הכל יהיה הרבה יותר עצוב. כרגע, אין כלים מובנים מתורבתים להפרדת נתונים כזו בארסנל של אקסל, למרבה הצער. אז תצטרך להשתמש במאקרו ב-Visual Basic, או להשתמש בשילוב של מקליט מאקרו + Power Query עם קצת "חידוד קובץ" לאחר מכן.

בואו נסתכל מקרוב כיצד ניתן ליישם זאת.

ניסוח הבעיה

יש לנו כנתונים ראשוניים טבלה כזו בגודל של יותר מ-5000 שורות למכירות:

משימה: להפיץ את הנתונים מטבלה זו לפי עיר על גיליונות נפרדים של ספר זה. הָהֵן. בפלט, אתה צריך לקבל על כל גיליון רק את השורות מהטבלה שבה המכירה הייתה בעיר המתאימה:

להכין

כדי לא לסבך את קוד המאקרו ולהפוך אותו לקל ככל האפשר להבנה, הבה נבצע כמה שלבי הכנה.

ראשית, ליצור טבלת חיפוש נפרדת, שבו עמודה אחת תפרט את כל הערים שעבורן ברצונך ליצור גיליונות נפרדים. כמובן, ייתכן שספרייה זו אינה מכילה את כל הערים הקיימות בנתוני המקור, אלא רק את אלה שלגביהן אנו זקוקים לדיווחים. הדרך הקלה ביותר ליצור טבלה כזו היא להשתמש בפקודה נתונים - הסר כפילויות (נתונים - הסר כפילויות) להעתקת עמודות עִיר או פונקציה ייחודי (ייחודי) - אם יש לך את הגרסה העדכנית ביותר של Excel 365.

מכיוון שגליונות חדשים ב-Excel נוצרים כברירת מחדל לפני (משמאל ל) הנוכחי (הקודם), הגיוני גם למיין את הערים בספרייה זו בסדר יורד (מ-Z ל-A) - ואז לאחר היצירה, העיר גיליונות מסודרים בסדר אלפביתי.

שנית, пלהמיר את שתי הטבלאות לדינמיות ("חכם") כדי להקל על העבודה איתם. אנו משתמשים בפקודה בית - עיצוב כטבלה (בית - עיצוב כטבלה) או קיצור מקלדת Ctrl+T. בלשונית שמופיעה בנאי (לְעַצֵב) בואו נתקשר אליהם tablProdaji и TableCity, בהתאמה:

שיטה 1. מאקרו לחלוקה לפי גיליונות

בכרטיסיה מתקדם מפתחים (מפתח) לחץ על הכפתור Visual Basic או השתמש בקיצור מקלדת אחר+F11. בחלון עורך המאקרו שנפתח, הכנס מודול ריק חדש דרך התפריט הכנס - מודול והעתק לשם את הקוד הבא:

Sub Splitter() For Every cell In Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCell). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell sheets("Данные").ShowAllData End Sub	  

כאן עם לולאה לכל ... הבא יישם את המעבר דרך התאים של הספרייה TableCity, שבו עבור כל עיר הוא מסונן (שיטה סינון אוטומטי) בטבלת המכירות המקורית ולאחר מכן העתקת התוצאות לגיליון החדש שנוצר. על הדרך שונה שם הגיליון שנוצר לאותו שם העיר ומופעלת עליו התאמה אוטומטית של רוחב העמודים ליופי.

אתה יכול להפעיל את המאקרו שנוצר ב- Excel בכרטיסייה מפתחים לַחְצָן מאקרו (מפתח - מאקרו) או קיצור מקלדת אחר+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) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""קטגוריה"", הקלד טקסט}, {""שם"", הקלד טקסט}, {""עיר"", הקלד טקסט}, {""מנהל"", הקלד טקסט}, {""עסקה תאריך "", הקלד datetime}, {""עלות"", סוג מספר}})," & Chr(13) & "" & Chr(10) & " #""שורות עם מסנן מוחל"" = Table.Se " & _ "lectRows(#""Changed type"", כל אחד ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""שורות עם מסנן מוחל""" ActiveWorkbook.Worksheets.Add עם ActiveSheet.ListObjects.Add(SourceType:=0, מקור:= _ "OLEDB; ספק =Microsoft.Mashup.OleDb.1; Data Source=$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 = xlInsertDeleteCells = .SavePass. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value תא סיום משנה  

לאחר השקתו, נראה את אותם גיליונות לפי עיר, אך שאילתות Power Query שכבר נוצרו ירכיבו אותם:

עם כל שינוי בנתוני המקור, זה יהיה מספיק כדי לעדכן את הטבלה המתאימה עם לחצן העכבר הימני - הפקודה עדכן ושמור (לְרַעֲנֵן) או עדכן את כל הערים בבת אחת בכמות גדולה באמצעות הכפתור עדכון כל כרטיסייה נתונים (נתונים - רענן הכל).

• מהן פקודות מאקרו, כיצד ליצור אותן ולהשתמש בהן
• שמירת גליונות חוברת עבודה כקבצים נפרדים
• איסוף נתונים מכל גיליונות הספר לטבלה אחת