שפּאַלטן אַ טיש אין שיץ

מיקראָסאָפט עקססעל האט פילע מכשירים פֿאַר קאַלעקטינג דאַטן פון עטלעכע טישן (פון פאַרשידענע שיץ אָדער פֿון פאַרשידענע טעקעס): דירעקט לינקס, פונקציע ינדירעקט (ינדירעקט), Power Query און Power Pivot אַדישאַנז, אאז"ו ו פון דעם זייַט פון די בעראַקייד, אַלץ קוקט גוט.

אָבער אויב איר לויפן אין אַ פאַרקערט פּראָבלעם - פאַרשפּרייטן דאַטן פון איין טיש צו פאַרשידענע שיץ - אַלץ וועט זיין פיל טרויעריק. אין דער מאָמענט, עס זענען קיין סיוואַלייזד געבויט-אין מכשירים פֿאַר אַזאַ דאַטן צעשיידונג אין די אַרסענאַל פון עקססעל, ליידער. אַזוי איר וועט האָבן צו נוצן אַ מאַקראָו אין וויסואַל באַסיק, אָדער נוצן די מאַקראָו מאַגנעטאָפאָן + מאַכט אָנפֿרעג קאָמבינאַציע מיט אַ ביסל "טעקע ראַפינירטקייַט" נאָך.

זאל ס נעמען אַ נעענטער קוק אין ווי דאָס קענען זיין ימפּלאַמענאַד.

פאָרמולאַטיאָן פון די פּראָבלעם

מיר האָבן ווי ערשט דאַטן אַזאַ אַ טיש מיט אַ גרייס פון מער ווי 5000 ראָוז פֿאַר פארקויפונג:

אַרבעט: צו פאַרשפּרייטן די דאַטן פון דעם טיש דורך שטאָט אויף באַזונדער שיץ פון דעם בוך. יענע. אין דער רעזולטאַט, איר דאַרפֿן צו באַקומען אויף יעדער בלאַט בלויז די ראָוז פון די טיש ווו די פאַרקויף איז געווען אין די קאָראַספּאַנדינג שטאָט:

צוגרייטן

אין סדר נישט צו קאָמפּליצירן די מאַקראָו קאָד און מאַכן עס ווי גרינג צו פֿאַרשטיין ווי מעגלעך, לאָזן אונדז דורכפירן אַ פּאָר פון פּריפּעראַטאָרי סטעפּס.

ערשטער, דער שאַפֿן אַ באַזונדער לוקאַפּ טיש, ווו אַ איין זייַל וועט רשימה אַלע די שטעט פֿאַר וואָס איר ווילן צו שאַפֿן באַזונדער שיץ. פון קורס, דער וועגווייַזער קען נישט אַנטהאַלטן אַלע די שטעט פאָרשטעלן אין די מקור דאַטן, אָבער בלויז די פֿאַר וואָס מיר דאַרפֿן ריפּאָרץ. די יזיאַסט וועג צו שאַפֿן אַזאַ אַ טיש איז צו נוצן דעם באַפֿעל דאַטאַ - אַראָפּנעמען דופּליקאַטן (דאַטאַ - אַראָפּנעמען דופּליקאַטן) פֿאַר זייַל קאָפּיע שטאָט אָדער פֿונקציע UNIK (יינציק) - אויב איר האָט די לעצטע ווערסיע פון ​​​​עקססעל 365.

זינט נייַ שיץ אין עקססעל זענען באשאפן דורך פעליקייַט איידער (צו די לינקס פון) די קראַנט (פרייַערדיק) איינער, עס אויך מאכט זינען צו סאָרט די שטעט אין דעם וועגווייַזער אין אראפנידערן סדר (פון ז צו א) - און נאָך שאַפונג, די שטאָט שיץ וועט זיין עריינדזשד אַלפאַבעטיקאַל.

רגע, די пגער ביידע טישן צו דינאַמיש ("קלוג") צו מאַכן עס גרינגער צו אַרבעטן מיט זיי. מיר נוצן די באַפֿעל היים - פֿאָרמאַט ווי אַ טיש (היים - פֿאָרמאַט ווי טיש) אָדער קלאַוויאַטור דורכוועג קטרל+T. אויף די קוויטל וואָס איז געוויזן קאָנסטרוקטאָר (פּלאַן) לאָמיר זיי רופן טאַבלפּראָדאַדזשי и טאַבלעסיטי, ריספּעקטיוולי:

מעטאָד 1. מאַקראָ פֿאַר אָפּטייל דורך שיץ

אויף די אַוואַנסירטע קוויטל דעוועלאָפּער (דעוועלאָפּער) דריקט אויף די קנעפּל וויסואַל באַסיק אָדער נוצן קלאַוויאַטור דורכוועג אַלט+פקסנומקס. אין די מאַקראָו רעדאַקטאָר פֿענצטער וואָס אָפּענס, אַרייַנלייגן אַ נייַ ליידיק מאָדולע דורך די מעניו אַרייַנלייגן - מאָדולע און קאָפּיע די פאלגענדע קאָד דאָרט:

סוב ספּליטטער() פֿאַר יעדער צעל אין ראַנגע("טאַבלעט") ראַנגע("טאַבלעץ"). אַוטאָפילטער פיעלד:=3, קריטעריאַ1:=סעלל.וואַלוע ראַנגע("טאַבלעט פּראָדוסעד [#אַלע]"). ספּעציעלע סעללס(קסלסעלל). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

דאָ מיט אַ שלייף פֿאַר יעדער ... ווייַטער ימפּלאַמענאַד די דורכפאָר דורך די סעלז פון די וועגווייַזער טאַבלעסיטי, ווו פֿאַר יעדער שטאָט עס איז פילטערד (מיטל אַוטאָפילטער) אין דער אָריגינעל פארקויפונג טיש און דאַן קאַפּיינג די רעזולטאַטן צו די ניי באשאפן בלאַט. צוזאמען דעם וועג, די באשאפן בלאַט איז ריניימד צו די זעלבע נאָמען פון די שטאָט און אַוטאָ-פּאַסן די ברייט פון די שפאלטן פֿאַר שיינקייט איז אויסגעדרייט אויף עס.

איר קענען לויפן די באשאפן מאַקראָו אין עקססעל אויף די קוויטל דעוועלאָפּער קנעפּל מאַקראָס (דעוועלאָפּער - מאַקראָס) אָדער קלאַוויאַטור דורכוועג אַלט+F8.

אופֿן 2. שאַפֿן קייפל קוויריז אין מאַכט קווערי

די פריערדיקע אופֿן, פֿאַר אַלע זייַן קאַמפּאַקטנאַס און פּאַשטעס, האט אַ באַטייטיק שטערונג - די שיץ באשאפן דורך די מאַקראָו זענען נישט דערהייַנטיקט ווען ענדערונגען זענען געמאכט אין דער אָריגינעל פארקויפונג טיש. אויב אַפּדייטינג אויף די פליען איז נייטיק, איר וועט האָבן צו נוצן די VBA + Power Query פּעקל, אָדער גאַנץ, מאַכן ניצן אַ מאַקראָו ניט בלויז שיץ מיט סטאַטיק דאַטן, אָבער דערהייַנטיקט Power Query קוויריז.

דער מאַקראָו אין דעם פאַל איז טייל ענלעך צו די פריערדיקע (עס אויך האט אַ ציקל פֿאַר יעדער ... ווייַטער צו יטערייט איבער שטעט אין די וועגווייַזער), אָבער אין די שלייף עס וועט ניט מער זיין פילטערינג און קאַפּיינג, אָבער קריייטינג אַ מאַכט אָנפֿרעג אָנפֿרעג און ופּלאָאַדינג די רעזולטאַטן צו אַ נייַע בלאַט:

סוב ספּליטטער2 () פֿאַר יעדער צעל אין ראַנגע ("שטאָט טיש") ActiveWorkbook.Queries.Add נאָמען:=סעלל.וואַלוע, פאָרמולע:= _ "לאָזן" & Chr(13) & "" & Chr(10) & "מקור = Excel.CurrentWorkbook(){[Name=""TableSales""]}[אינהאַלט]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""קאטעגאריע"", טיפּ טעקסט}, {""נאָמען"", טיפּ טעקסט}, {""שטאָט"", טיפּ טעקסט}, {""פאַרוואַלטער"", טיפּ טעקסט}, {""אָפּמאַך דאַטע "", טיפּ דאַטעטיים}, {""קאָסט"", טיפּ נומער}})," & Chr(13) & "" & Chr(10) & " #""ראָוז מיט פילטער געווענדט"" = Table.Se " & _ "lectRows(#""טשאַנגעד טיפּ"", יעדער ([סיטי] = """ & cell.value & """))" & Chr(13) & "" & Chr(10) & "אין " & Chr(13) & "" & Chr(10) & " #""ראָוז מיט פילטער געווענדט""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; פּראָווידער =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , דעסטיניישאַן:=ראַנגע("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = אמת .RefreshOnFileOpen = פאַלש .BackgroundQuery = אמת .RefreshStyle = xlInsertDeleteCells = .SavePass. SaveData = אמת .AdjustColumnWidth = אמת .RefreshPeriod = 0 .PreserveColumnInfo = אמת .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=פאַלש סוף מיט ActiveSheet.Name = cell.value ווייַטער צעל סוף סוב  

נאָך זיין קאַטער, מיר וועלן זען די זעלבע שיץ דורך שטאָט, אָבער שוין באשאפן Power Query קוויריז וועט פאָרעם זיי:

מיט קיין ענדערונגען אין די מקור דאַטן, עס וועט זיין גענוג צו דערהייַנטיקן די קאָראַספּאַנדינג טיש מיט די רעכט מויז קנעפּל - די באַפֿעל דערהייַנטיקן & היט (דערפרישן) אָדער דערהייַנטיקן אַלע שטעט אין אַמאָל מיט די קנעפּל דערהייַנטיקן אַלע קוויטל דאַטע (דאַטאַ - דערפרישן אַלע).

• וואָס זענען מאַקראָס, ווי צו שאַפֿן און נוצן זיי
• שפּאָרן וואָרקבאָאָק שיץ ווי באַזונדער טעקעס
• קאַלעקטינג דאַטן פון אַלע שיץ פון דעם בוך אין איין טיש