דינאַמיש קייט מיט אַוטאָ-סייזינג

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

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

אַלע דעם אין גאַנץ וועט נישט לאָזן איר באַקומען באָרד 😉

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

אופֿן 1. סמאַרט טיש

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

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

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

• טיש קסנומקס - לינק צו די גאנצע טיש אַחוץ פֿאַר די כעדער רודערן (A2: D5)
• טיש 1[#אַלע] - לינק צו די גאנצע טיש (A1: D5)
• טיש 1 [פעטרוס] - רעפֿערענץ צו אַ קייט זייַל אָן דער ערשטער צעל-כעדער (C2: C5)
• טיש 1[#כעאַדערס] - לינק צו די "כעדער" מיט די נעמען פון די שפאלטן (A1: D1)

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

= SUM (טיש 1 [מאָסקווע]) - כעזשבן פון די סאַכאַקל פֿאַר די זייַל "מאָסקווע"

or

=VPR(F5;טיש קסנומקס;3;0) - זוכן אין די טיש פֿאַר די חודש פֿון צעל F5 און אַרויסגעבן די סט פעטערבורג סאַכאַקל פֿאַר אים (וואָס איז VLOOKUP?)

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

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

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

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

אופֿן 2: דינאַמיש געהייסן קייט

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

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

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

געפֿינען די לעצטע צעל ניצן MATCH

MATCH (לוקאַפּ_וואַליו, קייט, גלייַכן_טיפּ) - אַ פֿונקציע וואָס זוכט פֿאַר אַ געגעבן ווערט אין אַ קייט (רודערן אָדער זייַל) און קערט די אָרדאַנאַל נומער פון דער צעל ווו עס איז געפֿונען. פֿאַר בייַשפּיל, די פאָרמולע MATCH ("מאַרץ"; A1: A5; 0) וועט צוריקקומען די נומער 4 ווי אַ רעזולטאַט, ווייַל די וואָרט "מאַרץ" איז ליגן אין דער פערט צעל אין זייַל A1: A5. די לעצטע פֿונקציע אַרגומענט Match_Type = 0 מיטל מיר זענען קוקן פֿאַר אַ פּינטלעך גלייַכן. אויב דעם אַרגומענט איז נישט ספּעסיפיעד, די פונקציע וועט באַשטימען צו די זוכן מאָדע פֿאַר די ניראַסט קלענסטער ווערט - דאָס איז פּונקט וואָס קענען זיין הצלחה געניצט צו געפֿינען די לעצטע פאַרנומען צעל אין אונדזער מענגע.

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

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

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

אויב עס זענען טעקסט וואַלועס אין אונדזער זייַל, ווי דער עקוויוואַלענט פון די גרעסטן מעגלעך נומער, איר קענען אַרייַנלייגן די קאַנסטראַקשאַן REPEAT ("איך", 255) - אַ טעקסט שטריקל קאַנסיסטינג פון 255 אותיות "איך" - די לעצטע בריוו פון דער אלפאבעט. Since Excel actually compares character codes when searching, any text in our table will technically be “smaller” than such a long “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” line:

דזשענערייט אַ לינק ניצן INDEX

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

אינדעקס(קייט; רודערן_נום; קאָלום_נום)

עס גיט די אינהאַלט פון דער צעל פון די קייט דורך רודערן און זייַל נומער, דאָס הייסט למשל, די פֿונקציע =INDEX(A1:D5;3;4) אין אונדזער טיש מיט שטעט און חדשים פון די פריערדיקע אופֿן וועט געבן 1240 - דער אינהאַלט פון די 3 רודערן און 4 זייַל, דאָס הייסט סעלז ד 3. אויב עס איז בלויז איין זייַל, זיין נומער קענען זיין איבערגעהיפּערט, ד"ה פאָרמולע INDEX(A2:A6;3) וועט געבן "סאַמאַראַ" אין די לעצטע סקרעענשאָט.

און עס איז איין ניט לעגאַמרע קלאָר ווי דער טאָג נואַנס: אויב דער אינדעקס איז נישט נאָר אריין אין דער צעל נאָך די = צייכן, ווי געוויינטלעך, אָבער איז געניצט ווי די לעצט טייל פון די רעפֿערענץ צו די קייט נאָך די צווייפּינטל, עס איז ניט מער גיט אויס. די אינהאַלט פון דער צעל, אָבער זייַן אַדרעס! אזוי, אַ פאָרמולע ווי $A$2:INDEX($A$2:$A$100;3) וועט געבן אַ רעפֿערענץ צו די קייט A2:A4 אין דער רעזולטאַט.

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

=$A$2:INDEX($A$2:$A$100; MATCH(REP("איך";255);A2:A100))

שאַפֿן אַ געהייסן קייט

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

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

• ניצן די VLOOKUP פֿונקציע צו פֿאַרבינדן טישן און לוקאַפּ וואַלועס
• ווי צו שאַפֿן אַן אַוטאָ-פּאַפּיאַלייטינג דראָפּדאָוון רשימה
• ווי צו שאַפֿן אַ דרייפּונקט טיש צו פונאַנדערקלייַבן אַ גרויס סומע פון ​​דאַטן