דרייפּונקט טיש אַריבער קייפל דאַטן ריינדזשאַז

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

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

איידער מיר אָנהייבן, לאָמיר דערקלערן אַ פּאָר פון פונקטן. אַ פּריאָרי, איך גלויבן אַז די פאלגענדע טנאָים זענען באגעגנט אין אונדזער דאַטן:

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

מעטאָד 1: בויען טישן פֿאַר אַ דרייפּונקט ניצן Power Query

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

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

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

אין די פֿענצטער וואָס איז ארויס, אויסקלייַבן קיין בלאַט (עס טוט נישט ענין וואָס איינער) און דריקן די קנעפּל אונטן טוישן (רעדאַגירן):

די Power Query Query Editor פֿענצטער זאָל עפענען אויף שפּיץ פון עקססעל. אויף די רעכט זייַט פון די פֿענצטער אויף די טאַפליע בעטן פּאַראַמעטערס ויסמעקן אַלע אויטאָמאַטיש באשאפן סטעפּס אַחוץ דער ערשטער - מאָקער (Source):

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

ויסמעקן אַלע שפאלטן אַחוץ זייַל דאַטעדורך רעכט-קליקינג אַ זייַל כעדינג און סעלעקטירן ויסמעקן אנדערע שפאלטן (נעם אַוועק אנדערע שפאלטן):

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

אויב איר האָט געטאן אַלץ ריכטיק, אין דעם פונט איר זאָל זען די אינהאַלט פון אַלע טישן געזאמלט איינער אונטער די אנדערע:

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

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

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

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

אופֿן 2. מיר פאַרייניקן טישן מיט די UNION SQL באַפֿעל אין אַ מאַקראָו

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

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

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'בלאַט נאָמען, ווו די ריזאַלטינג דרייפּונקט וועט זיין געוויזן ResultSheetName = "דרייווע פון" אַ מענגע נעמען מיט מקור טישן SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") 'מיר פאָרעם אַ קאַש פֿאַר טישן פֿון שיץ פֿון SheetsNames מיט ActiveWorkbook ReDim arSQL (1 צו (UBound (SheetsNames) + 1) ) פֿאַר איך = לבאָונד (שיץ נאַמעס) צו UBound (שיץ נאַמעס) arSQL (איך + 1) = "סעלעקטירן * FROM [" & שיץנאַמעס (איך) & "$]" ווייַטער איך שטעלן objRS = CreateObject ("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) סוף מיט 'שייַעך-שאַפֿן דעם בלאַט צו ווייַזן די ריזאַלטינג דרייפּונקט טיש אויף טעות נעמענ זיכ ווידער אַפּפּליקאַטיאָן.דיספּלייַאַלערטס = פאַלש וואָרקשעעץ (רעזולטאַטשעעטנאַמע). נאָמען = ResultSheetName 'ווייַזן די דזשענערייטאַד קאַש קיצער אויף דעם בויגן באַשטעטיק objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) באַשטעטיק objPivotCache.Recordset = objRS Set objRS = גאָרנישט מיט wsPivot objPivotCache.CreatePivotTable":CreatePivotTable": objPivotCache = Nothing Range ("A3"). אויסקלייַבן סוף מיט סוף סוב    

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

קאָנס פון דעם צוגאַנג:

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

אבער אין די סוף מיר באַקומען אַ פאַקטיש פול-פלעדזשד דרייפּונקט טיש, געבויט אויף עטלעכע ריינדזשאַז פון פאַרשידענע שיץ:

אַזוי!

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

	 פּראָווידער=Microsoft.Jet.OLEDB.4.0;  

צו:

	פּראָווידער=Microsoft.ACE.OLEDB.12.0;  

און אראפקאפיע און ינסטאַלירן די פריי דאַטן פּראַסעסינג מאָטאָר פֿון אַקסעס פֿון די מיקראָסאָפט וועבזייטל - Microsoft Access Database Engine 2010 Redistributable

אופֿן 3: ​​קאָנסאָלידירן פּיוואָטטאַבלע וויזערד פֿון אַלט ווערסיעס פון עקססעל

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

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

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

און אין דער ווייַטער פֿענצטער, סעלעקטירן יעדער קייט אין קער און לייגן עס צו דער אַלגעמיין רשימה:

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

• קריייטינג רעפּאָרץ מיט פּיוואָטטאַבלעס
• שטעלן אַרויף חשבונות אין פּיוואָטטאַבלעס
• וואָס זענען מאַקראָס, ווי צו נוצן זיי, ווו צו נאָכמאַכן וובאַ קאָד, עטק.
• דאַטן זאַמלונג פון קייפל שיץ צו איין (PLEX אַדישאַן)