אַסעמבאַל טישן פון פאַרשידענע עקססעל טעקעס מיט Power Query

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

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

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

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

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

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

מיר אויסקלייַבן וועפּאַנז

פֿאַר די לייזונג, מיר דאַרפֿן די לעצטע ווערסיע פון ​​​​עקססעל 2016 (די נויטיק פאַנגקשאַנאַליטי איז שוין געבויט אין עס דורך פעליקייַט) אָדער פרייַערדיק ווערסיעס פון עקססעל 2010-2013 מיט די פריי לייגן-אין אינסטאַלירן. מאַכט קווערי פֿון מייקראָסאָפֿט (אָפּלאָדירן עס דאָ). Power Query איז אַ סופּער פלעקסאַבאַל און סופּער שטאַרק געצייַג פֿאַר לאָודינג דאַטן אין עקססעל פֿון די דרויסנדיק וועלט, און דעמאָלט סטריפּינג און פּראַסעסינג עס. Power Query שטיצט כּמעט אַלע יגזיסטינג דאַטן קוואלן - פֿון טעקסט טעקעס צו SQL און אפילו Facebook 🙂

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

שריט 1. אַרייַנפיר איין טעקע ווי אַ מוסטער

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

• אויב איר האָבן עקססעל 2016, עפענען די קוויטל דאַטע און דעמאָלט שאַפֿן אָנפֿרעג - פֿון טעקע - פֿון בוך (דאַטאַ - ניו אָנפֿרעג - פֿון טעקע - פֿון עקססעל)
• אויב איר האָבן עקססעל 2010-2013 מיט די Power Query לייגן-אין אינסטאַלירן, דעמאָלט עפענען די קוויטל מאַכט קווערי און סעלעקטירן עס פֿון טעקע - פֿון בוך (פֿון טעקע — פֿון עקססעל)

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

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

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

• פילטער אויס ומנייטיק דאַטן, ליידיק שורות, שורות מיט ערראָרס
• סאָרט דאַטן דורך איין אָדער מער שפאלטן
• באַקומען באַפרייַען פון יבערכאַזערונג
• טיילן קלעפּיק טעקסט דורך שפאלטן (דורך דעלימיטערס, נומער פון אותיות, אאז"ו ו)
• שטעלן טעקסט אין סדר (אַרויסנעמען עקסטרע ספּייסאַז, ריכטיק פאַל, אאז"ו ו)
• גער דאַטן טייפּס אין יעדער מעגלעך וועג (מאַכן נומערן ווי טעקסט אין נאָרמאַל נומערן און וויצע ווערסאַ)
• יבערמאַכן (דרייען) טישן און יקספּאַנד צוויי-דימענשאַנאַל קרייַז-טישן אין פלאַך אָנעס
• לייג נאָך שפאלטן צו די טיש און נוצן פאָרמולאַס און פאַנגקשאַנז אין זיי ניצן די M שפּראַך געבויט אין Power Query.
• ...

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

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

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

איר קענען ויסשליסן שורות מיט ערראָרס אָדער ליידיק שורות, ווי געזונט ווי ומנייטיק מאַנאַדזשערז אָדער קאַסטאַמערז, ניצן אַ פּשוט פילטער:

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

ליכט און עלעגאַנט, איז ניט עס?

שריט 2. זאל ס יבערמאַכן אונדזער בקשה אין אַ פֿונקציע

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

אין די אָנפֿרעג עדיטאָר, גיין צו די View קוויטל און גיט די קנעפּל Advanced Editor (View — Advanced Editor). א פֿענצטער זאָל עפענען ווו אַלע אונדזער פריערדיקן אַקשאַנז וועט זיין געשריבן אין די פאָרעם פון קאָד אין די M שפּראַך. ביטע טאָן אַז דער דרך צו דער טעקע וואָס מיר ימפּאָרטיד פֿאַר דעם בייַשפּיל איז שווער קאָדעד אין די קאָד:

איצט לאָזן אונדז מאַכן אַ פּאָר פון אַדזשאַסטמאַנץ:

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

אַלע. דריקט אויף ענדיקן און זאָל זען דעם:

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

שריט 3. קאַלעקטינג אַלע די טעקעס

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

גיט טוישן (רעדאַגירן) און ווידער מיר באַקומען אין די באַקאַנט אָנפֿרעג רעדאַקטאָר פֿענצטער.

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

נאָך דריקט אויף OK די באשאפן זייַל זאָל זיין מוסיף צו אונדזער טיש אויף די רעכט.

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

און איצט די "וואַו מאָמענט" - דריקט אויף די ייקאַן מיט זיין אייגענע אַראָוז אין דער אויבערשטער רעכט ווינקל פון די צוגעלייגט זייַל מיט אונדזער פונקציע:

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

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

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

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

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

PS

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

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

און אַז ס אַלע! ליד!

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