געפֿינען די ניראַסט נומער

ינהאַלט

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

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

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

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

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

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

אופֿן 1: נעסטעד יפס

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

געפֿינען די ניראַסט נומער

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

אין אַדישאַן, Microsoft Excel האט אַ נעסטינג שיעור פֿאַר די IF פֿונקציע - 7 מאָל אין עלטערע ווערסיעס און 64 מאָל אין נייַער ווערסיעס. וואָס אויב איר דאַרפֿן מער?

אופֿן 2. וולאָאָקופּ מיט מעהאַלעך מיינונג

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

געפֿינען די ניראַסט נומער

ווו

B4 - די ווערט פון די קוואַנטיטי פון סכוירע אין דער ערשטער טראַנסאַקטיאָן פֿאַר וואָס מיר זענען קוקן פֿאַר אַ אַראָפּרעכענען
$G$4:$H$8 - אַ לינק צו די אַראָפּרעכענען טיש - אָן אַ "כעדער" און מיט די אַדרעסעס פאַרפעסטיקט מיט די $ צייכן.
2 - די אָרדאַנאַל נומער פון די זייַל אין די אַראָפּרעכענען טיש פון וואָס מיר ווילן צו באַקומען די אַראָפּרעכענען ווערט
אמת – דאָ איז דער „הונט“ באַגראָבן. אויב ווי די לעצטע פֿונקציע אַרגומענט VPR ספּעציפיצירן ליגנעריש (פאַלש) אָדער קסנומקס, דעמאָלט דער פֿונקציע וועט קוקן פֿאַר שטרענג גלייַכן אין די קוואַנטיטי זייַל (און אין אונדזער פאַל עס וועט געבן אַ #N/A טעות, ווייַל עס איז קיין ווערט 49 אין די אַראָפּרעכענען טיש). אבער אויב אַנשטאָט ליגנעריש שרייַבן אמת (אמת) אָדער קסנומקס, דער פֿונקציע וועט נישט קוקן פֿאַר די פּינטלעך, אָבער ניראַסט קלענסטער ווערט און וועט געבן אונדז די פּראָצענט פון אַראָפּרעכענען מיר דאַרפֿן.

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

געפֿינען די ניראַסט נומער

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

מעטאָד 3. דערגייונג די ניראַסט גרעסטער ניצן די INDEX און MATCH פאַנגקשאַנז

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

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

געפֿינען די ניראַסט נומער

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

אופֿן 4. ניו פונקציע VIEW (XLOOKUP)

אויב איר האָבן אַ ווערסיע פון אָפפיסע 365 מיט אַלע דערהייַנטיקונגען אינסטאַלירן, אַנשטאָט פון VLOOKUP (VLOOKUP) איר קענען נוצן זייַן אַנאַלאָג - די VIEW פונקציע (XLOOKUP), וואָס איך האָב שוין אַנאַלייזד אין דעטאַל:

געפֿינען די ניראַסט נומער

דאָ:

B4 - דער ערשט ווערט פון די קוואַנטיטי פון די פּראָדוקט פֿאַר וואָס מיר זענען קוקן פֿאַר אַ אַראָפּרעכענען
$G$4:$G$8 - די קייט ווו מיר זענען קוקן פֿאַר שוועבעלעך
$ה$4:$ה$8 - די קייט פון רעזולטאַטן פון וואָס איר ווילן צו צוריקקומען די אַראָפּרעכענען
פערטע טענה (-1) כולל די זוכן פֿאַר די ניראַסט קלענסטער נומער וואָס מיר וועלן אַנשטאָט פון אַ פּינטלעך גלייַכן.

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

אָבער, ליידער, ניט אַלעמען האט דעם שטריך נאָך - בלויז צופרידן אָונערז פון אָפפיסע 365.

אופֿן 5. מאַכט אָנפֿרעג

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

זאל ס ערשטער טאָן עטלעכע פּריפּעראַטאָרי אַרבעט:

לאָמיר גער אונדזער מקור טישן צו דינאַמיש (קלוג) ניצן אַ קלאַוויאַטור דורכוועג קטרל+T אָדער מאַנשאַפֿט היים - פֿאָרמאַט ווי אַ טיש (היים - פֿאָרמאַט ווי טיש).
פֿאַר קלאָרקייט, לאָמיר זיי געבן נעמען. פארקויפונג и דיסקאַונץ קוויטל קאָנסטרוקטאָר (פּלאַן).
לאָדן יעדער פון די טישן אין קער אין Power Query ניצן די קנעפּל פֿון טיש / קייט קוויטל דאַטע (דאַטן - פֿון טיש / קייט). אין די לעצטע ווערסיעס פון עקססעל, דעם קנעפּל איז ריניימד צו מיט בלעטער (פון בלאַט).
אויב די טישן האָבן פאַרשידענע זייַל נעמען מיט קוואַנטאַטיז, ווי אין אונדזער ביישפּיל ("קוואַנטיטי פון סכוירע" און "קוואַנטיטי פון ..."), זיי מוזן זיין ריניימד אין Power Query און געהייסן די זעלבע.
נאָך דעם, איר קענען צוריקקומען צוריק צו עקססעל דורך סעלעקטירן דעם באַפֿעל אין די Power Query רעדאַקטאָר פֿענצטער היים - נאָענט און לאָדן - נאָענט און לאָדן אין ... (היים - נאָענט & לאָדן - נאָענט & לאָדן צו ...) און דעמאָלט אָפּציע נאָר שאַפֿן אַ קשר (נאָר שאַפֿן קשר).
דעמאָלט דער רובֿ טשיקאַווע הייבט. אויב איר האָט דערפאַרונג אין Power Query, איך יבערנעמען אַז דער ווייַטער שורה פון געדאַנק זאָל זיין אין דער ריכטונג פון צונויפגיסן די צוויי טישן מיט אַ פאַרבינדן אָנפֿרעג (מערדזש) אַ לאַ VLOOKUP, ווי איז געווען דער פאַל אין די פריערדיקע אופֿן. אין פאַקט, מיר וועלן דאַרפֿן צו צונויפגיסן אין לייגן מאָדע, וואָס איז נישט קלאָר ווי דער טאָג בייַ ערשטער בליק. אויסקלייַבן אין עקססעל קוויטל דאַטן - באַקומען דאַטן - קאַמביין ריקוועס - לייג (דאַטאַ - באַקומען דאַטן - קאַמביין פֿראגן - צוגעבן) און דערנאָך אונדזער טישן פארקויפונג и דיסקאַונץ אין די פֿענצטער וואָס איז געוויזן:
נאָך דריקט אויף OK אונדזער טישן וועט זיין קליימד אין אַ איין גאַנץ - אונטער יעדער אנדערער. ביטע טאָן אַז די שפאלטן מיט די קוואַנטיטי פון סכוירע אין די טישן זענען געפאלן אונטער יעדער אנדערע, ווייַל. זיי האָבן די זעלבע נאָמען:
אויב דער אָריגינעל סיקוואַנס פון ראָוז אין די פארקויפונג טיש איז וויכטיק פֿאַר איר, אַזוי אַז נאָך אַלע סאַבסאַקוואַנט טראַנספאָרמאַציע איר קענען ומקערן עס, לייגן אַ געציילט זייַל צו אונדזער טיש מיט די באַפֿעל. אַדינג אַ זייַל - אינדעקס זייַל (לייג זייַל — אינדעקס זייַל). אויב די סיקוואַנס פון שורות טוט נישט ענין צו איר, איר קענען האָפּקען דעם שריט.
איצט, ניצן די פאַל-אַראָפּ רשימה אין די כעדער פון די טיש, סאָרט עס דורך זייַל קוואַנטיטי שטייענדיק:
און דער הויפּט טריק: רעכט גיט אויף די זייַל כעדער אַראָפּרעכענען קלייַבן אַ קאָלעקטיוו פּלאָמבירן - אַראָפּ (פילן - אַראָפּ). ליידיק סעלז מיט נאַל אויטאָמאַטיש אָנגעפילט מיט די פריערדיקע אַראָפּרעכענען וואַלועס:
עס בלייבט צו ומקערן דער אָריגינעל סיקוואַנס פון ראָוז דורך סאָרטינג דורך זייַל ינדעקס (איר קענען בעשאָלעם ויסמעקן עס שפּעטער) און באַקומען באַפרייַען פון ומנייטיק שורות מיט אַ פילטער נאַל דורך זייַל טראַנסאַקטיאָן קאָד: