דינאַמיש ערייז אין עקססעל

וואָס זענען דינאַמיש ערייז

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

באַטראַכטן אַ פּשוט בייַשפּיל צו דערקלערן די עסאַנס.

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

אין אַלע פריערדיקע ווערסיעס פון עקססעל, נאָך געבן אַ קליק אויף אַרייַן מיר וואָלט באַקומען די אינהאַלט פון בלויז איין ערשטער צעל B2. ווי אַנדערש?

נו, אָדער עס וואָלט זיין מעגלעך צו ייַנוויקלען דעם קייט אין אַ מין פון אַגגרעגייטינג פֿונקציע ווי = SUM(B2: C4) און באַקומען אַ גאַנץ גאַנץ פֿאַר עס.

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

איצט אַלץ איז אַנדערש.

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

דאָס איז נישט מאַגיש, אָבער די נייַע דינאַמיש ערייז וואָס Microsoft Excel איצט האט. ברוכים הבאים צו די נייַע וועלט 🙂

פֿעיִקייטן פון ארבעטן מיט דינאַמיש ערייז

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

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

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

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

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

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

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

דינאַמיש מענגע ערראָרס

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

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

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

דינאַמיש ערייז און קלוג טישן

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

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

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

דינאַמיש ערייז און אנדערע עקססעל פֿעיִקייטן

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

נישט ממש.

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

יבערזעצונג

צו טראַנספּאָסירן אַ קייט (ויסבייַטן ראָוז און שפאלטן) Microsoft Excel האט שטענדיק האָבן אַ געבויט-אין פונקציע TRANSP (טראַנספּאָסע). אָבער, אין סדר צו נוצן עס, איר מוזן ערשטער ריכטיק אויסקלייַבן די קייט פֿאַר די רעזולטאַטן (למשל, אויב די אַרייַנשרייַב איז געווען אַ קייט פון 5 × 3, איר מוזן האָבן אויסגעקליבן 3 × 5), און אַרייַן די פונקציע און דריקן דעם קנעפּל. קאָמבינאַציע קטרל+יבעררוק+אַרייַן, ווייַל עס קען נאָר אַרבעטן אין מענגע פאָרמולע מאָדע.

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

קייפל טיש

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

גלוינג און קאַסטן קאַנווערזשאַן

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

מסקנא Top 3

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

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

VLOOKUP יקסטראַקטינג קייפל שפאלטן אין אַמאָל

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

OFFSET פֿונקציע צוריקקערן אַ דינאַמיש מענגע

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

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

לאָמיר נעמען אַ קוק אויף אירע טענות:

• A1 - סטאַרטינג צעל (רעפֿערענץ פונט)
• ПОИСКПОЗ(F2;A2:A30;0) - כעזשבן פון די יבעררוק פון די סטאַרטינג צעל אַראָפּ - צו דער ערשטער געפֿונען קרויט.
• 0 - יבעררוק פון די "פֿענצטער" צו די רעכט קאָרעוו צו די סטאַרטינג צעל
  
• СЧЁТЕСЛИ(A2:A30;F2) - כעזשבן פון די הייך פון די אומגעקערט "פֿענצטער" - די נומער פון שורות ווו עס איז קרויט.
• 4 - גרייס פון די "פֿענצטער" כאָריזאַנטאַלי, דאָס הייסט רעזולטאַט 4 שפאלטן

ניו פאַנגקשאַנז פֿאַר דינאַמיש אַררייַס

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

• מיינונג (סאָרט) - סאָרט די אַרייַנשרייַב קייט און פּראָדוצירן אַ דינאַמיש מענגע אויף דער רעזולטאַט
  
• SORTPO (SORTBY) - קענען סאָרט איין קייט לויט וואַלועס פון אנדערן
  
• פילטער (פילטער) - ריטריווז ראָוז פון די מקור קייט וואָס טרעפן די ספּעסיפיעד טנאָים
• UNIK (יינציק) - עקסטראַקט יינציק וואַלועס פון אַ קייט אָדער רימוווז דופּליקאַטן
  
• SLMASSIVE (ראַנדאַררייַ) - דזשענערייץ אַ מענגע פון ​​טראַפ - נומערן פון אַ געגעבן גרייס
• נאָך געבורט (סעקוואַנס) - פארמען אַ מענגע פון ​​אַ סיקוואַנס פון נומערן מיט אַ געגעבן שריט
  

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

קאָנקלוסיאָנס

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

א סך הכל די תוצאות, pluses דינאַמיש ערייז, איר קענען שרייַבן די פאלגענדע:

• איר קענען פאַרגעסן וועגן די קאָמבינאַציע קטרל+יבעררוק+אַרייַן. עקססעל איצט זעט קיין חילוק צווישן "רעגולער פאָרמולאַס" און "מענגע פאָרמולאַס" און טריץ זיי די זעלבע וועג.
• וועגן די פֿונקציע SUMPRODUCT (SUMPRODUCT), וואָס איז פריער געניצט צו אַרייַן מענגע פאָרמולאַס אָן קטרל+יבעררוק+אַרייַן איר קענען אויך פאַרגעסן - איצט עס איז גרינג גענוג סאַם и אַרייַן.
• סמאַרט טישן און באַקאַנט פאַנגקשאַנז (SUM, IF, VLOOKUP, SUMIFS, אאז"ו ו) איצט אויך גאָר אָדער טייל שטיצן דינאַמיש ערייז.
• עס איז צוריק קאַמפּאַטאַבילאַטי: אויב איר עפֿענען אַ וואָרקבאָאָק מיט דינאַמיש ערייז אין אַן אַלט ווערסיע פון ​​​​עקססעל, זיי וועלן ווערן אַ מענגע פאָרמולאַס (אין געגרייַזלט ברייסאַז) און פאָרזעצן צו אַרבעטן אין די "אַלט סטיל".

געפונען עטלעכע נומער מינוסעס:

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

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

ווו קען איך אראפקאפיע?

און לעסאָף, די הויפּט קשיא 🙂

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

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

• אויב איר האָט אַן אָפפיסע 365 אַבאָנעמענט, איר קענט נאָר וואַרטן ביז דער דערהייַנטיקן ריטשאַז איר. ווי געשווינד דאָס כאַפּאַנז דעפּענדס אויף ווי אָפט דערהייַנטיקונגען זענען איבערגעגעבן צו דיין אָפפיסע (אַמאָל אַ יאָר, אַמאָל יעדער זעקס חדשים, אַמאָל אַ חודש). אויב איר האָבן אַ פֿירמע פּיסי, איר קענען פרעגן דיין אַדמיניסטראַטאָר צו שטעלן אַרויף דערהייַנטיקונגען צו זיין דאַונלאָודיד מער אָפט.
  
• איר קענען פאַרבינדן די רייען פון די אָפפיסע ינסידערס פּרובירן וואַלאַנטירז - דעמאָלט איר וועט זיין דער ערשטער צו באַקומען אַלע די נייַע פֿעיִקייטן און פאַנגקשאַנז (אָבער עס איז אַ געלעגנהייַט צו פאַרגרעסערן וואָגן אין עקססעל, דאָך).
• אויב איר טאָן ניט האָבן אַ אַבאָנעמענט, אָבער אַ סטאַנדאַלאָנע ווערסיע פון ​​​​עקססעל, איר וועט האָבן צו וואַרטן ביז די מעלדונג פון דער ווייַטער ווערסיע פון ​​אָפפיסע און עקססעל אין מינדסטער 2022. יוזערז פון אַזאַ ווערסיעס באַקומען בלויז זיכערהייט דערהייַנטיקונגען און זשוק פיקסיז, און אַלע די נייַע "גודיז" איצט גיין בלויז צו אָפפיסע 365 אבאנענטן. טרויעריק אָבער אמת 🙂
  

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

• וואָס זענען מענגע פאָרמולאַס און ווי צו נוצן זיי אין עקססעל
• פֿענצטער (קייט) סאַמיישאַן ניצן די OFFSET פונקציע
• 3 וועגן צו טראַנספּאָסע אַ טיש אין עקססעל