פליסנדיק גאַנץ אין עקססעל

אופֿן 1. פאָרמולאַס

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

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

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

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

אופֿן 2. דרייפּונקט טיש

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

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

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

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

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

די אַדוואַנידזשיז פון דעם צוגאַנג:

• א גרויס סומע פון ​​דאַטן איז געשווינד לייענען.
• קיין פאָרמולאַס דאַרפֿן צו זיין אריין מאַניואַלי.
• ווען טשאַנגינג די מקור דאַטן, עס איז גענוג צו דערהייַנטיקן די קיצער מיט די רעכט מויז קנעפּל אָדער מיט די באַפֿעל Data - Refresh All.

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

אופֿן 3: מאַכט אָנפֿרעג

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

דערנאָך מיר וועלן דורכפירן די פאלגענדע סטעפּס:

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

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

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

נאָכמאַכן דעם אויסדרוק צו די קליפּבאָרד פֿאַר ווייַטער נוצן.

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

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

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

עס בלייבט צו ויסמעקן די אינדעקס זייַל וואָס מיר ניט מער דאַרפֿן און צופֿעליקער די רעזולטאַטן צוריק צו עקססעל מיט די היים - נאָענט & לאָדן צו באַפֿעלן.

די פּראָבלעם איז סאַלווד.

שנעל און ופגעקאָכט

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

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

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

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

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

אן אנדער זאַך, רעכט? 🙂

• פּאַרעטאָ טשאַרט (80/20) און ווי צו בויען עס אין עקססעל
• קייוואָרד זוכן אין טעקסט און אָנפֿרעג באַפערינג אין Power Query