לעצטנס איז צו מיר צוגעקומען א חבר מיינע מיט א בקשה צו העלפן מיט די דור פון אלע מעגליכע פראַסעס וואס באשטייט פון א סכום געגעבענע ווערטער. פּראָבלעמס פון דעם מין קענען אויפשטיין ווען קאַמפּיילינג רשימות פון טערמינען און פראַסעס פֿאַר אָנליין גאַנצע און סעאָ העכערונג, ווען איר דאַרפֿן צו דורכגיין אַלע די מעגלעך פּערמיוטיישאַנז פון ווערטער אין אַ זוכן אָנפֿרעג:
אין מאטעמאטיק רופט מען די אפעראציע קאַרטעסיאַן פּראָדוקט. די אפיציעלע דעפיניציע איז אזוי: דער קארטעזישער פראדוקט פון סעט א און ב איז דער סכום פון אלע פארן, דער ערשטער באשטאנד פון וועלכע געהערט צו דער סכום א, און דער צווייטער באשטאנדטייל געהערט צו דער סכום ב. דערצו, קענען די עלעמענטן פון סעץ זיין ביידע נומערן און טעקסט.
איבערגעזעצט אויף אַ מענטשלעכער שפּראַך, מיינט דאָס, אַז אויב אין גאַנג א האָבן מיר, למשל, די ווערטער "ווייַס" און "רויט", און אין סכום ב "במוו" און "מערסעדעס", דאַן נאָך דעם קאַרטעסיאַן פּראָדוקט פון די צוויי שטעלט. באַקומען אויף דער רעזולטאַט איז דער גאַנג פון אַלע מעגלעך וועריאַנץ פון פראַסעס, געמאכט פון די ווערטער פון ביידע רשימות:
- ווייַס במוו
- רויט במוו
- ווייַס מערסעדעס
- רויט מערסידעס
... ד"ה פּונקט וואָס מיר דאַרפֿן. זאל ס קוק אין אַ פּאָר פון וועגן צו סאָלווע דעם אַרבעט אין עקססעל.
אופֿן 1. פאָרמולאַס
לאָמיר אָנהייבן מיט פאָרמולאַס. לאָמיר יבערנעמען אַז ווי ערשט דאַטן מיר האָבן דריי רשימות פון אָריגינעל ווערטער אין שפאלטן א, ב און C, ריספּעקטיוולי, און די נומער פון עלעמענטן אין יעדער רשימה קענען בייַטן:
לאמיר קודם מאכן דריי שפאלטן מיט אינדעקס, דאס הייסט ארדנאלע נומערן פון ווערטער פון יעדע רשימה אין אלע מעגליכע קאמבינאציעס. דער ערשטער רודערן פון וניץ (E2:G2) וועט זיין אריין מאַניואַלי, און פֿאַר די מנוחה מיר נוצן די פאלגענדע פאָרמולע:
די לאָגיק דאָ איז פּשוט: אויב דער אינדעקס אין די העכער פרייַערדיק צעל האט שוין ריטשט די סוף פון דער רשימה, ד"ה איז גלייַך צו די נומער פון עלעמענטן אין דער רשימה קאַלקיאַלייטיד דורך די פֿונקציע. COUNT (COUNTA), דעמאָלט מיר ריסטאַרט די נאַמבערינג. אַנדערש, מיר פאַרגרעסערן דעם אינדעקס מיט 1. באַצאָלן ספּעציעל ופמערקזאַמקייַט צו די קלוג פיקסיר פון די ריינדזשאַז מיט דאָללאַר וואונדער ($) אַזוי אַז איר קענען נאָכמאַכן די פאָרמולע אַראָפּ און צו די רעכט.
איצט אַז מיר האָבן די אָרדאַנאַל נומערן פון די ווערטער וואָס מיר דאַרפֿן פון יעדער רשימה, מיר קענען עקסטראַקט די ווערטער זיך מיט די פֿונקציע ינדעקס (אינדעקס) אין דריי באַזונדערע שפאלטן:
אויב איר האָט נישט געפֿונען דעם פֿונקציע אין דיין אַרבעט פריער, איך רעקאָמענדירן איר צו לערנען עס בייַ מינדסטער דייאַגאַנאַלי - עס העלפּס אין פילע סיטואַטיאָנס און איז ניט ווייניקער נוציק (און אפילו מער!) VPR (VLOOKUP).
נו, נאָך דעם, עס בלייבט בלויז צו קליי די ריזאַלטינג פראַגמאַנץ שורה דורך שורה מיט די קאַנקאַטאַניישאַן סימבאָל (&):
... אָדער (אויב איר האָט די לעצטע ווערסיע פון עקססעל) מיט די האַנטיק פונקציע COMBINE (טעקסטדזשאָין), וואָס קענען קליי די גאנצע אינהאַלט פון די ספּעסיפיעד סעלז דורך אַ געגעבן סעפּאַראַטאָר כאַראַקטער (פּלאַץ):
אופֿן 2. דורך מאַכט אָנפֿרעג
מאַכט קווערי איז אַ שטאַרק לייגן-אין פֿאַר מיקראָסאָפט עקססעל וואָס פּערפאָרמז צוויי הויפּט טאַסקס: 1. לאָודינג דאַטן אין עקססעל פון כּמעט קיין פונדרויסנדיק מקור, און 2. אַלע מינים פון טראַנספערמיישאַנז פון לאָודיד טישן. Power Query איז שוין געבויט אין עקססעל 2016-2019, און פֿאַר עקססעל 2010-2013 עס איז אינסטאַלירן ווי אַ באַזונדער לייגן-אין (איר קענען אראפקאפיע עס פֿון דער באַאַמטער מיקראָסאָפט וועבזייטל פֿאַר פריי). אויב איר האָט נישט נאָך סטאַרטעד ניצן Power Query אין דיין אַרבעט, עס איז צייט צו טראַכטן וועגן אים, ווייַל טראַנספערמיישאַנז ווי די דיסקרייבד אויבן זענען דורכגעקאָכט לייכט און געוויינטלעך אין אַ פּאָר פון מווומאַנץ.
ערשטער, לאָזן אונדז לאָדן די מקור רשימות ווי באַזונדער קוויריז אין Power Query. צו טאָן דאָס, דורכפירן די פאלגענדע סטעפּס פֿאַר יעדער טיש:
- לאָמיר מאַכן טישן אין "קלוג" מיט אַ קנעפּל פֿאָרמאַט ווי אַ טיש קוויטל היים (היים - פֿאָרמאַט ווי טיש) אָדער קלאַוויאַטור דורכוועג קטרל+T. יעדער טיש וועט אויטאָמאַטיש באַקומען אַ נאָמען טיש 1,2,3…, וואָס, אָבער, קענען זיין געביטן אויב איר ווילט אויף די קוויטל קאָנסטרוקטאָר (פּלאַן).
- נאָך שטעלן די אַקטיוו צעל אין די טיש, דריקן דעם קנעפּל פון טיש (פון טיש) קוויטל דאַטע (דאַטע) אָדער אויף די בלאַט מאַכט קווערי (אויב איר האָט עס אינסטאַלירן ווי אַ באַזונדער לייגן-אין פֿאַר עקססעל 2010-2013).
- אין די אָנפֿרעג רעדאַקטאָר פֿענצטער וואָס אָפּענס, סעלעקטירן דעם באַפֿעל היים - נאָענט און לאָדן - נאָענט און לאָדן אין ... (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ..) און דעמאָלט דער אָפּציע נאָר שאַפֿן אַ קשר (שאַפֿן בלויז פֿאַרבינדונג). דאָס וועט לאָזן די לאָודיד טיש אין זכּרון און לאָזן עס אַקסעס אין דער צוקונפֿט.
אויב איר טאָן אַלץ ריכטיק, די רעזולטאַט אין די רעכט טאַפליע זאָל זיין דריי ריקוועס אין די מאָדע בלויז קשר מיט אונדזערע טישן נעמען:
איצט רעכט גיט אויף דער ערשטער אָנפֿרעג און סעלעקטירן דעם באַפֿעל רונג (רעפערענץ)צו מאַכן אַ ופּדאַטאַבלע קאָפּיע פון עס, און לייגן אַן נאָך זייַל צו די דאַטן דורך די באַפֿעל אַדינג אַ זייַל ž – מנהג זייַל (לייג זייַל -ž מנהג זייַל). אין די פאָרמולע אַרייַנשרייַב פֿענצטער, אַרייַן די נאָמען פון די נייַע זייַל (למשל, Fragment2) און אַ גאָר פּשוט אויסדרוק ווי אַ פאָרמולע:
= טיש2
... הייסט, אין אנדערע ווערטער, די נאָמען פון די רגע אָנפֿרעג:
נאָך דריקט אויף OK מיר וועלן זען אַ נייַע זייַל, אין יעדער צעל פון וואָס עס וועט זיין אַ נעסטעד טיש מיט פראַסעס פון די רגע טיש (איר קענען זען די אינהאַלט פון די טישן אויב איר קליקט אין דער הינטערגרונט פון דער צעל לעבן דעם וואָרט טיש):
עס בלייבט צו יקספּאַנד אַלע די אינהאַלט פון די נעסטעד טישן ניצן די קנעפּל מיט טאָפּל אַראָוז אין די כעדער פון די ריזאַלטינג זייַל און ונטשעקק ניצן אָריגינעל זייַל נאָמען ווי פּרעפיקס (ניצן אָריגינעל זייַל נאָמען ווי פּרעפיקס):
... און מיר באַקומען אַלע מעגלעך קאַמבאַניישאַנז פון עלעמענטן פון די ערשטער צוויי שטעלט:
אין דערצו, אַלץ איז ענלעך. לייג אן אנדער קאַלקיאַלייטיד זייַל מיט די פאָרמולע:
= טיש3
..., און דעמאָלט יקספּאַנד די נעסטעד טישן ווידער - און איצט מיר האָבן שוין אַלע די מעגלעך אָפּציעס פֿאַר פּערמיוטינג ווערטער פון די דריי שטעלט ריספּעקטיוולי:
עס בלייבט צו אויסקלייַבן אַלע דרייַ שפאלטן פון לינקס צו רעכט, האלטן קטרל, און קאַנקאַטאַנייט זייער אינהאַלט אפגעשיידט דורך ספּייסאַז ניצן די באַפֿעל צונויפגיסן שפאלטן (מערדזש שפאלטן) פון די קוויטל טראַנספאָרמאַציע (טראַנספאָרמירן):
די ריזאַלטינג רעזולטאַטן קענען זיין אַנלאָודיד צוריק אויף די בלאַט מיט די שוין באַקאַנטע באַפֿעל היים - נאָענט און לאָדן - נאָענט און לאָדן אין ... (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ..):
אויב אין דער צוקונפֿט עפּעס ענדערונגען אין אונדזער מקור טישן מיט פראַגמאַנץ, עס וועט זיין גענוג נאָר צו דערהייַנטיקן די דזשענערייטאַד אָנפֿרעג דורך רעכט-קליקינג אויף די ריזאַלטינג טיש און טשוזינג די באַפֿעל. דערהייַנטיקן & היט (דערפרישן) אָדער דורך דרינגלעך די קלאַוויאַטור דורכוועג קטרל+אַלט+F5.
- וואָס איז Power Query, Power Pivot, Power Map און Power BI און וואָס טאָן זיי דאַרפֿן אַן עקססעל באַניצער
- שאַפֿן אַ גאַנטט טשאַרט אין מאַכט אָנפֿרעג
- 5 וועגן צו נוצן די INDEX פונקציע