ינהאַלט
מיר לעצטנס דעדאַקייטאַד אַן אַרטיקל צו איינער פון די מערסט נוציק עקססעל פאַנגקשאַנז גערופן VPR און געוויזן ווי עס קענען זיין געוויינט צו עקסטראַקט די פארלאנגט אינפֿאָרמאַציע פון אַ דאַטאַבייס אין אַ ווערקשיט צעל. מיר אויך דערמאנט אַז עס זענען צוויי נוצן קאַסעס פֿאַר די פֿונקציע VPR און בלויז איינער פון זיי דילז מיט דייטאַבייס קוויריז. אין דעם אַרטיקל, איר וועט לערנען אן אנדער ווייניקער באַוווסט וועג צו נוצן די פונקציע VPR אין עקססעל.
אויב איר האָט נישט געטאן דעם נאָך, זיין זיכער צו לייענען די לעצטע אַרטיקל וועגן די פונקציע VPR, ווייַל אַלע די אינפֿאָרמאַציע אונטן אַסומז אַז איר זענט שוין באַקאַנט מיט די פּרינסאַפּאַלז דיסקרייבד אין דער ערשטער אַרטיקל.
ווען ארבעטן מיט דאַטאַבייסיז, פאַנגקשאַנז VPR אַ יינציק ידענטיפיער איז דורכגעגאנגען, וואָס איז געניצט צו ידענטיפיצירן די אינפֿאָרמאַציע מיר ווילן צו געפֿינען (למשל, אַ פּראָדוקט קאָד אָדער אַ קונה לעגיטימאַציע נומער). דעם יינציק קאָד מוזן זיין פאָרשטעלן אין די דאַטאַבייס, אַנדערש VPR וועט באַריכט אַ טעות. אין דעם אַרטיקל, מיר וועלן קוקן אין דעם וועג פון ניצן די פונקציע VPRווען די שייַן איז נישט עקסיסטירט אין די דאַטאַבייס. ווי אויב די פֿונקציע VPR סוויטשט צו דער דערנענטערנ מאָדע, און טשוזיז וואָס דאַטן צו צושטעלן אונדז ווען מיר ווילן צו געפֿינען עפּעס. אין זיכער צושטאנדן, דאָס איז פּונקט וואָס איז דארף.
א ביישפיל פון לעבן. מיר שטעלן די אַרבעט
לאָמיר אילוסטרירן דעם אַרטיקל מיט אַ פאַקטיש-לעבן בייַשפּיל - קאַלקיאַלייטינג קאַמישאַנז באזירט אויף אַ ברייט קייט פון פארקויפונג מעטריקס. מיר וועלן אָנהייבן מיט אַ זייער פּשוט אָפּציע, און דעמאָלט מיר ביסלעכווייַז קאָמפּליצירן עס ביז די בלויז באַרדאַסדיק לייזונג צו דעם פּראָבלעם איז צו נוצן די פֿונקציע. VPR. דער ערשט סצענאַר פֿאַר אונדזער פיקטישאַס אַרבעט איז ווי גייט: אויב אַ פאַרקויפער מאכט מער ווי $ 30000 אין פארקויפונג אין אַ יאָר, זיין קאָמיסיע איז 30%. אַנדערש, די קאָמיסיע איז בלויז 20%. זאל ס שטעלן עס אין די פאָרעם פון אַ טיש:
דער טרעגער גייט אריין זייער פארקויפונג דאַטן אין צעל ב 1, און די פאָרמולע אין צעל ב 2 דיטערמאַנז די ריכטיק קאָמיסיע קורס אַז דער טרעגער קענען דערוואַרטן. אין קער, די ריזאַלטינג קורס איז געניצט אין צעל B3 צו רעכענען די גאַנץ קאָמיסיע די טרעגער זאָל באַקומען (פשוט מאַלטאַפּלייינג סעלז B1 און B2).
די מערסט טשיקאַווע טייל פון די טיש איז קאַנטיינד אין צעל ב 2 - דאָס איז די פאָרמולע פֿאַר דיטערמאַנינג די קאָמיסיע קורס. דעם פאָרמולע כּולל אַן עקססעל פונקציע גערופן IF (אויב). פֿאַר די לייענער וואָס זענען נישט באַקאַנט מיט דעם פֿונקציע, איך וועט דערקלערן ווי עס אַרבעט:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
צושטאַנד איז אַ פֿונקציע אַרגומענט וואָס נעמט די ווערט פון יעדער TRUE CODE (אמת), אָדער FALSE (פאַלש). אין דעם בייַשפּיל אויבן, דער אויסדרוק B1
איז עס אמת אַז B1 איז ווייניקער ווי B5?
אָדער איר קענען זאָגן עס אַנדערש:
איז עס אמת אַז די גאַנץ סומע פון פארקויפונג פֿאַר די יאָר איז ווייניקער ווי די שוועל ווערט?
אויב מיר ענטפֿערן דעם קשיא יאָ (TRUE), דערנאָך די פֿונקציע קערט ווערט אויב אמת (ווערט אויב אמת). אין אונדזער פאַל, דאָס וועט זיין די ווערט פון צעל ב 6, דאָס הייסט קאָמיסיע קורס ווען די גאַנץ פארקויפונג איז אונטער די שוועל. אויב מיר ענטפֿערן די קשיא NO (פאַלש) דעמאָלט קערט ווערט אויב פאַלש (ווערט אויב FALSE). אין אונדזער פאַל, דאָס איז די ווערט פון צעל ב 7, דאָס הייסט קאָמיסיע קורס ווען גאַנץ פארקויפונג איז העכער די שוועל.
ווי איר קענען זען, אויב מיר נעמען די גאַנץ פארקויפונג פון $ 20000, מיר באַקומען אַ 2% קאָמיסיע קורס אין צעל ב 20. אויב מיר אַרייַן אַ ווערט פון $40000, די קאָמיסיע קורס וועט טוישן מיט 30%:
אַזוי אַרבעט אונדזער טיש.
מיר קאָמפּליצירן די אַרבעט
לאָמיר מאַכן די זאַך אַ ביסל מער שווער. לאָמיר שטעלן אן אנדער שוועל: אויב דער טרעגער ערנז מער ווי $40000, די קאָמיסיע קורס ינקריסיז צו 40%:
אַלץ סימז צו זיין פּשוט און קלאָר, אָבער אונדזער פאָרמולע אין צעל ב 2 ווערט נאָוטיסאַבלי מער קאָמפּליצירט. אויב איר קוק ענג אין די פאָרמולע, איר וועט זען אַז די דריט אַרגומענט פון די פֿונקציע IF (IF) פארקערט אין אן אנדער פול-פלעדזשד פֿונקציע IF (אויב). דעם קאַנסטראַקשאַן איז גערופן נעסטינג פון פאַנגקשאַנז אין יעדער אנדערער. עקססעל גליק אַלאַוז די קאַנסטראַקשאַנז, און זיי אפילו אַרבעט, אָבער זיי זענען פיל האַרדער צו לייענען און פֿאַרשטיין.
מיר וועלן נישט דעלוו אין די טעכניש דעטאַילס - וואָס און ווי עס אַרבעט, און מיר וועלן נישט גיין אין די נואַנסיז פון שרייבן נעסטעד פאַנגקשאַנז. נאָך אַלע, דאָס איז אַן אַרטיקל דעדאַקייטאַד צו די פֿונקציע VPR, ניט אַ גאַנץ פירן צו עקססעל.
וועלכער דער פאַל, די פאָרמולע ווערט מער קאָמפּליצירט! וואָס אויב מיר באַקענען אן אנדער אָפּציע פֿאַר אַ קאָמיסיע קורס פון 50% פֿאַר די סעלערז וואָס מאַכן מער ווי $ 50000 אין פארקויפונג. און אויב עמעצער האט פארקויפט מער ווי $ 60000, וועט זיי באַצאָלן 60% קאָמיסיע?
איצט די פאָרמולע אין צעל ב 2, אפילו אויב עס איז געווען געשריבן אָן ערראָרס, איז גאָר אַנרידאַבאַל. איך טראַכטן אַז עס זענען ווייניק וואָס ווילן צו נוצן פאָרמולאַס מיט 4 לעוועלס פון נעסטינג אין זייער פּראַדזשעקס. עס מוז זיין א גרינגער וועג?!
און עס איז אַזאַ אַ וועג! די פֿונקציע וועט העלפֿן אונדז VPR.
מיר צולייגן די VLOOKUP פונקציע צו סאָלווע די פּראָבלעם
זאל ס טוישן די פּלאַן פון אונדזער טיש אַ ביסל. מיר וועלן האַלטן אַלע די זעלבע פעלדער און דאַטן, אָבער צולייגן זיי אין אַ נייַע, מער סאָליד וועג:
נעמען אַ מאָמענט און מאַכן זיכער די נייַ טיש קורס טיש כולל די זעלבע דאַטן ווי די פריערדיקע שוועל טיש.
דער הויפּט געדאַנק איז צו נוצן די פֿונקציע VPR צו באַשטימען דעם געוואלט צאָל קורס לויט די טיש קורס טיש דיפּענדינג אויף פארקויפונג באַנד. ביטע טאָן אַז דער טרעגער קענען פאַרקויפן סכוירע פֿאַר אַ סומע וואָס איז נישט גלייַך צו איינער פון די פינף שוועלן אין די טיש. פֿאַר בייַשפּיל, ער קען פאַרקויפן פֿאַר $ 34988, אָבער עס איז ניט אַזאַ סומע. זאל ס זען ווי די פֿונקציע VPR קענען האַנדלען מיט אַזאַ אַ סיטואַציע.
ינסערטינג אַ VLOOKUP פֿונקציע
אויסקלייַבן צעל B2 (ווו מיר ווילן צו אַרייַנלייגן אונדזער פאָרמולע) און געפֿינען VLOOKUP (VLOOKUP) אין די עקססעל פאַנגקשאַנז ביבליאָטעק: Formulas (פאָרמולעס) > Function Library (פונקציע ביבליאָטעק) > לוקאַפּ & רעפערענץ (רעפערענצן און ערייז).
א דיאַלאָג קעסטל איז ארויס פונקציאָנירן אַרגומענטן (פונקציע טענות). מיר פּלאָמבירן די וואַלועס פון די אַרגומענטן איינער דורך איינער, סטאַרטינג מיט Lookup_value (לוקאַפּ_וואַליו). אין דעם בייַשפּיל, דאָס איז די גאַנץ סומע פון פארקויפונג פון צעל ב 1. שטעלן די לויפֿער אין דעם פעלד Lookup_value (לוקאַפּ_וואַליו) און סעלעקטירן צעל B1.
ווייַטער, איר דאַרפֿן צו ספּעציפיצירן די פאַנגקשאַנז VPRווו צו קוקן פֿאַר דאַטן. אין אונדזער בייַשפּיל, דאָס איז אַ טיש קורס טיש. שטעלן די לויפֿער אין דעם פעלד Table_array (טיש) און אויסקלייַבן די גאנצע טיש קורס טישאַחוץ פֿאַר כעדערז.
ווייַטער, מיר דאַרפֿן צו ספּעציפיצירן וואָס זייַל צו עקסטראַקט דאַטן פון ניצן אונדזער פאָרמולע. מיר זענען אינטערעסירט אין די קאָמיסיע קורס, וואָס איז אין די רגע זייַל פון די טיש. דעריבער, פֿאַר די אַרגומענט Col_index_num (Column_number) אַרייַן די ווערט 2.
און צום סוף, מיר באַקענען די לעצטע אַרגומענט - Range_lookup (ינטערוואַל_לוקאַפּ).
וויכטיק: עס איז די נוצן פון דעם אַרגומענט וואָס מאכט די חילוק צווישן די צוויי וועגן פון אַפּלייינג די פֿונקציע VPR. ווען ארבעטן מיט דאַטאַבייסיז, די אַרגומענט Range_lookup (range_lookup) מוזן שטענדיק האָבן אַ ווערט FALSE (פאַלש) צו זוכן פֿאַר אַ פּינטלעך גלייַכן. אין אונדזער נוצן פון די פֿונקציע VPR, מיר מוזן לאָזן דעם פעלד ליידיק, אָדער אַרייַן אַ ווערט TRUE CODE (אמת). עס איז גאָר וויכטיק צו קלייַבן דעם אָפּציע ריכטיק.
צו מאַכן עס קלאָר, מיר וועלן פאָרשטעלן TRUE CODE (TRUE) אין דעם פעלד Range_lookup (ינטערוואַל_לוקאַפּ). כאָטש, אויב איר לאָזן די פעלד ליידיק, דאָס וועט נישט זיין אַ טעות, זינט TRUE CODE איז זיין פעליקייַט ווערט:
מיר האָבן אָנגעפילט אַלע די פּאַראַמעטערס. איצט מיר דריקן OK, און עקססעל קריייץ אַ פאָרמולע פֿאַר אונדז מיט אַ פֿונקציע VPR.
אויב מיר עקספּערימענט מיט עטלעכע פאַרשידענע וואַלועס פֿאַר די גאַנץ סאַלעס סומע, מיר וועלן מאַכן זיכער אַז די פאָרמולע אַרבעט ריכטיק.
סאָף
ווען די פֿונקציע VPR אַרבעט מיט דאַטאַבייסיז, אַרגומענט Range_lookup (range_lookup) מוזן אָננעמען FALSE (פאַלש). און די ווערט אריין ווי Lookup_value (לוקאַפּ_וואַליו) מוזן עקסיסטירן אין די דאַטאַבייס. אין אנדערע ווערטער, עס איז קוקן פֿאַר אַ פּינטלעך גלייַכן.
אין דעם בייַשפּיל מיר האָבן געקוקט אין דעם אַרטיקל, עס איז ניט דאַרפֿן צו באַקומען אַ פּינטלעך גלייַכן. דאָס איז דער פאַל ווען די פֿונקציע VPR מוזן באַשטימען צו דערנענטערנ מאָדע צו צוריקקומען די געוואלט רעזולטאַט.
פֿאַר בייַשפּיל: מיר ווילן צו באַשליסן וואָס קורס צו נוצן אין די קאָמיסיע כעזשבן פֿאַר אַ פאַרקויפער מיט אַ פארקויפונג באַנד פון $ 34988. פֿונקציע VPR קערט אונדז אַ ווערט פון 30%, וואָס איז לעגאַמרע ריכטיק. אבער פארוואס האט די פאָרמולע אויסקלייַבן די רודערן מיט פּונקט 30% און נישט 20% אָדער 40%? וואָס איז מענט מיט דערנענטערנ זוכן? זאל ס זיין קלאָר.
ווען די טענה Range_lookup (interval_lookup) האט אַ ווערט TRUE CODE (TRUE) אָדער איבערגעהיפּערט, פֿונקציע VPR יטערייץ דורך דער ערשטער זייַל און סאַלעקץ די גרעסטע ווערט וואָס טוט נישט יקסיד די לוקאַפּ ווערט.
וויכטיק פונט: פֿאַר דעם סכעמע צו אַרבעטן, דער ערשטער זייַל פון די טיש מוזן זיין אויסגעשטעלט אין אַסענדינג סדר.