ינהאַלט
דעם לעקציע דערקלערט ווי צו געשווינד האַנדלען מיט אַ סיטואַציע ווו אַ פֿונקציע VPR (VLOOKUP) טוט נישט וועלן צו אַרבעטן אין עקססעל 2013, 2010, 2007 און 2003, און ווי צו ידענטיפיצירן און פאַרריכטן פּראָסט ערראָרס און באַקומען לימיטיישאַנז VPR.
אין עטלעכע פריערדיקע אַרטיקלען, מיר האָבן יקספּלאָרד די פאַרשידן פאַסאַץ פון די פֿונקציע VPR אין עקססעל. אויב איר האָט לייענען זיי קערפאַלי, איר זאָל איצט זיין אַ מומחה אין דעם פעלד. אָבער, עס איז נישט אָן סיבה אַז פילע עקססעל עקספּערץ גלויבן VPR איינער פון די מער קאָמפּליצירט פֿעיִקייטן. עס האט אַ בינטל פון לימיטיישאַנז און פֿעיִקייטן וואָס ווערן דער מקור פון פילע פּראָבלעמס און ערראָרס.
אין דעם אַרטיקל איר וועט געפֿינען פּשוט דערקלערונגען פון ערראָרס #AT (#N/A), # נאָמען? (#נאָמען?) און # וואָלו! (#VALUE!) וואָס דערשייַנען ווען איר אַרבעט מיט די פֿונקציע VPR, ווי געזונט ווי טעקניקס און מעטהאָדס פון האַנדלינג מיט זיי. מיר וועלן אָנהייבן מיט די מערסט פּראָסט קאַסעס און די מערסט קלאָר ווי דער טאָג סיבות וואָס. VPR טוט נישט אַרבעטן, אַזוי עס איז בעסער צו לערנען די ביישפילן אין די סדר אין וואָס זיי זענען געגעבן אין דעם אַרטיקל.
פיקסיר # N/A טעות אין VLOOKUP פונקציע אין עקססעל
אין פאָרמולאַס מיט VPR טעות אָנזאָג #AT (#N/A) מיטל נישט עוועלעבעל (קיין דאַטן) - אויס ווען עקססעל קען נישט געפֿינען די ווערט איר זוכט פֿאַר. דאָס קען פּאַסירן פֿאַר עטלעכע סיבות.
1. דער געוואלט ווערט איז מיספּעלד
גוט געדאַנק צו קאָנטראָלירן דעם נומער ערשטער! טיפּאָס פאַלן אָפט ווען איר אַרבעט מיט זייער גרויס אַמאַונץ פון דאַטן, קאַנסיסטינג פון טויזנטער פון שורות, אָדער ווען די ווערט איר זוכט פֿאַר איז געשריבן אין אַ פאָרמולע.
2. #N/A טעות ווען איר זוכט פֿאַר אַן אַפּפּראָקסימאַטע גלייַכן מיט VLOOKUP
אויב איר נוצן אַ פאָרמולע מיט אַן אַפּפּראָקסימאַטע גלייַכן זוכן צושטאַנד, ד"ה אַרגומענט range_lookup (range_lookup) איז TRUE אָדער נישט ספּעסיפיעד, דיין פאָרמולע קען באַריכט אַ טעות # N / A אין צוויי פאלן:
- דער ווערט צו קוקן אַרויף איז ווייניקער ווי דער קלענסטער ווערט אין די מענגע וואָס איז געקוקט אַרויף.
- דער זוכן זייַל איז נישט אויסגעשטעלט אין שטייענדיקער סדר.
3. #N/A טעות ווען איר זוכט פֿאַר אַ פּינטלעך גלייַכן מיט VLOOKUP
אויב איר זוכט פֿאַר אַ פּינטלעך גלייַכן, ד"ה אַרגומענט range_lookup (range_lookup) איז FALSE און די פּינטלעך ווערט איז נישט געפֿונען, די פאָרמולע וועט אויך באַריכט אַ טעות # N / A. לערן מער וועגן ווי צו זוכן פֿאַר פּינטלעך און דערנענטערנ שוועבעלעך מיט אַ פֿונקציע VPR.
4. זוכן זייַל איז נישט לינקס
ווי איר מיסטאָמע וויסן, איינער פון די מערסט וויכטיק לימיטיישאַנז VPR עס איז אַז עס קען נישט פּנים צו די לינקס, דערפאר די זוכן זייַל אין דיין טיש מוזן זיין לינקס. אין פיר, מיר אָפט פאַרגעסן וועגן דעם, וואָס פירט צו אַ ניט-ארבעטן פאָרמולע און אַ טעות. # N / A.
באַשלוס: אויב עס איז ניט מעגלעך צו טוישן די דאַטן סטרוקטור אַזוי אַז די זוכן זייַל איז לינקס, איר קענען נוצן אַ קאָמבינאַציע פון פאַנגקשאַנז ינדעקס (אינדעקס) און מער יקספּאָוזד (MATCH) ווי אַ מער פלעקסאַבאַל אָלטערנאַטיוו פֿאַר VPR.
5. נומערן זענען פאָרמאַטטעד ווי טעקסט
אן אנדער מקור פון טעות # N / A אין פאָרמולאַס מיט VPR זענען נומערן אין טעקסט פֿאָרמאַט אין די הויפּט טיש אָדער לוקאַפּ טיש.
דעם יוזשאַוואַלי כאַפּאַנז ווען איר אַרייַנפיר אינפֿאָרמאַציע פון פונדרויסנדיק דאַטאַבייסיז, אָדער ווען איר דרוקן אַ אַפּאָסטראָפע איידער אַ נומער צו האַלטן די לידינג נול.
די מערסט קלאָר ווי דער טאָג וואונדער פון אַ נומער אין טעקסט פֿאָרמאַט זענען געוויזן אין די פיגור אונטן:
אין דערצו, נומערן קענען זיין סטאָרד אין דעם פֿאָרמאַט אַלגעמיין (אַלגעמיינע). אין דעם פאַל, עס איז בלויז איין באמערקט שטריך - די נומערן זענען אַליינד צו די לינקס ברעג פון דער צעל, און דורך פעליקייַט זיי זענען אַליינד צו די רעכט ברעג.
באַשלוס: אויב עס איז אַ איין ווערט, נאָר גיט אויף די טעות ייקאַן און סעלעקטירן גער צו נומער (גער צו נומער) פֿון די קאָנטעקסט מעניו.
אויב דאָס איז די סיטואַציע מיט פילע נומערן, סעלעקטירן זיי און רעכט גיט אויף די אויסגעקליבן געגנט. אין די קאָנטעקסט מעניו וואָס איז געוויזן, סעלעקטירן פֿאָרמאַט סעללס (פאָרמאַט סעלז) > קוויטל נומער (נומער) > פֿאָרמאַט נומער (נומעריק) און דריקן OK.
6. עס איז אַ פּלאַץ אין די אָנהייב אָדער אין די סוף
דאָס איז דער קלענסטער קלאָר ווי דער טאָג סיבה פֿאַר דעם טעות. # N / A אין פֿונקציע VPR, זינט עס איז וויזשוואַלי שווער צו זען די עקסטרע ספּייסאַז, ספּעציעל ווען ארבעטן מיט גרויס טישן, ווען רובֿ פון די דאַטן זענען אַוועק-פאַרשטעלן.
לייזונג 1: עקסטרע ספּייסאַז אין די הויפּט טיש (ווו די VLOOKUP פונקציע איז)
אויב עקסטרע ספּייסאַז דערשייַנען אין די הויפּט טיש, איר קענען ענשור אַז די פאָרמולאַס אַרבעט ריכטיק דורך ענקלאָוזינג די אַרגומענט לוקופּ_וואַלוע (לוקאַפּ_וואַליו) אין אַ פֿונקציע TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
לייזונג 2: עקסטרע ספּייסאַז אין די לוקאַפּ טיש (אין די לוקאַפּ זייַל)
אויב עקסטרע ספּייסאַז זענען אין די זוכן זייַל - פּשוט וועגן # N / A אין די פאָרמולע מיט VPR מען קען נישט אויסמיידן. אנשטאט VPR איר קענען נוצן אַ מענגע פאָרמולע מיט אַ קאָמבינאַציע פון פאַנגקשאַנז ינדעקס (אינדעקס), מער יקספּאָוזד (מאַטש) и TRIM (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
זינט דאָס איז אַ מענגע פאָרמולע, טאָן ניט פאַרגעסן צו דריקן Ctrl + Shift + Enter אַנשטאָט פון די געוויינטלעך אַרייַןצו אַרייַן די פאָרמולע ריכטיק.
טעות #VALUE! אין פאָרמולאַס מיט VLOOKUP
אין רובֿ קאַסעס, Microsoft Excel ריפּאָרץ אַ טעות # וואָלו! (#VALUE!) ווען די ווערט געניצט אין די פאָרמולע איז נישט פּאַסיק פֿאַר די דאַטן טיפּ. וועגן VPR, דעמאָלט עס זענען יוזשאַוואַלי צוויי סיבות פֿאַר דעם טעות # וואָלו!.
1. די ווערט איר זוכט פֿאַר איז מער ווי 255 אותיות
זיין אָפּגעהיט: פונקציאָנירן VPR קענען ניט זוכן פֿאַר וואַלועס מיט מער ווי 255 אותיות. אויב די ווערט איר זוכט פֿאַר יקסידז דעם שיעור, איר וועט באַקומען אַ טעות אָנזאָג. # וואָלו!.
באַשלוס: ניצן אַ פּלאַץ פון פֿעיִקייטן אינדעקס + גלייַכן (אינדעקס + גלייַכן). ונטער איז אַ פאָרמולע וואָס וועט זיין גוט פֿאַר דעם אַרבעט:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. דער פול וועג צו די זוכן וואָרקבאָאָק איז נישט ספּעסיפיעד
אויב איר זענען ריטריווינג דאַטן פון אן אנדער וואָרקבאָאָק, איר מוזן ספּעציפיצירן די פול דרך צו דער טעקע. מער ספּאַסיפיקלי, איר מוזן אַרייַננעמען די וואָרקבאָאָק נאָמען (אַרייַנגערעכנט די פאַרלענגערונג) אין קוואַדראַט בראַקאַץ [ ], נאכגעגאנגען דורך די בלאַט נאָמען, נאכגעגאנגען דורך אַ עקסקלאַמיישאַן פונט. אַלע דעם קאַנסטראַקשאַן מוזן זיין ענקלאָוזד אין אַפּאָסטראָפעס, אין פאַל די בוך אָדער בלאַט נאָמען כּולל ספּייסאַז.
דאָ איז די גאַנץ סטרוקטור פון די פֿונקציע VPR צו זוכן אין אן אנדער בוך:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
די פאַקטיש פאָרמולע קען קוקן ווי דאָס:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
דעם פאָרמולע וועט קוקן אַרויף די צעל ווערט A2 אין אַ זייַל B אויף די בלאַט שעעטקסנומקס אין די וואָרקבאָאָק ניו פּריסעס און עקסטראַקט די קאָראַספּאַנדינג ווערט פון די זייַל D.
אויב קיין טייל פון די טיש דרך איז איבערגעהיפּערט, דיין פונקציע VPR וועט נישט אַרבעטן און וועט באַריכט אַ טעות # וואָלו! (אפילו אויב די וואָרקבאָאָק מיט די לוקאַפּ טיש איז איצט אָפן).
פֿאַר מער אינפֿאָרמאַציע וועגן די פונקציע VPRריפערינג צו אן אנדער עקססעל טעקע, זען די לעקציע: זוכן אן אנדער וואָרקבאָאָק ניצן VLOOKUP.
3. אַרגומענט Column_num איז ווייניקער ווי 1
עס איז שווער צו ימאַדזשאַן אַ סיטואַציע ווו עמעצער גייט אריין אַ ווערט ווייניקער ווי 1צו אָנווייַזן די זייַל פון וואָס צו עקסטראַקט די ווערט. כאָטש עס איז מעגלעך אויב די ווערט פון דעם אַרגומענט איז קאַלקיאַלייטיד דורך אן אנדער עקססעל פֿונקציע נעסטעד ין VPR.
אַזוי, אויב עס כאַפּאַנז אַז די אַרגומענט קאָל_ינדעקס_נום (קאָלום_נומער) ווייניקער ווי 1פונקציאָנירן VPR וועט אויך באַריכט אַ טעות # וואָלו!.
אויב די טענה קאָל_ינדעקס_נום (קאָלום_נומער) איז גרעסער ווי די נומער פון שפאלטן אין די געגעבן מענגע, VPR וועט באַריכט אַ טעות #רעף! (#ססיל!).
טעות #NAME? אין VLOOKUP
דער סימפּלאַסט פאַל איז אַ טעות # נאָמען? (#NAME?) - וועט דערשייַנען אויב איר אַקסאַדענאַלי שרייַבן אַ פֿונקציע נאָמען מיט אַ טעות.
די לייזונג איז קלאָר ווי דער טאָג - טשעק דיין אויסלייג!
VLOOKUP טוט נישט אַרבעטן (לימיטיישאַנז, קייוויאַץ און דיסיזשאַנז)
אין אַדישאַן צו די גאַנץ קאָמפּליצירט סינטאַקס, VPR האט מער לימיטיישאַנז ווי קיין אנדערע עקססעל פונקציע. ווייַל פון די לימיטיישאַנז, פּאָנעם פּשוט פאָרמולאַס מיט VPR אָפט פירן צו אומגעריכט רעזולטאַטן. ונטער איר וועט געפֿינען סאַלושאַנז פֿאַר עטלעכע פּראָסט סינעריאָוז ווו VPR איז פאַלש.
1. VLOOKUP איז נישט פאַל-שפּירעוודיק
פונקציע VPR טוט נישט אונטערשיידן צווישן פאַל און אַקסעפּץ לאָווערקאַסע און ופּפּערקאַסע אותיות ווי די זעלבע. דעריבער, אויב עס זענען עטלעכע עלעמענטן אין די טיש וואָס זענען אַנדערש בלויז אין פאַל, די VLOOKUP פֿונקציע וועט צוריקקומען דער ערשטער עלעמענט געפֿונען, ראַגאַרדלאַס פון פאַל.
באַשלוס: ניצן אן אנדער עקססעל פֿונקציע וואָס קענען דורכפירן אַ ווערטיקאַל זוכן (LOOKUP, SUMPRODUCT, INDEX און MATCH) אין קאָמבינאַציע מיט EXACTא וואָס דיסטינגגווישיז פאַל. פֿאַר מער דעטאַילס, איר קענען לערנען פון די לעקציע - 4 וועגן צו מאַכן VLOOKUP פאַל-שפּירעוודיק אין עקססעל.
2. VLOOKUP קערט דער ערשטער ווערט געפֿונען
ווי איר שוין וויסן, VPR קערט די ווערט פון די געגעבן זייַל קאָראַספּאַנדינג צו דער ערשטער גלייַכן געפֿונען. אָבער, איר קענען עקסטראַקט די 2nd, 3rd, 4th אָדער קיין אנדערע יבערכאַזערונג פון די ווערט איר ווילן. אויב איר דאַרפֿן צו עקסטראַקט אַלע דופּליקאַט וואַלועס, איר דאַרפֿן אַ קאָמבינאַציע פון פאַנגקשאַנז ינדעקס (אינדעקס), לעצט (קליין) און ליניע (רודערן).
3. א זייַל איז צוגעגעבן אָדער אַוועקגענומען צו די טיש
צום באַדויערן, די פאָרמולאַס VPR האַלטן ארבעטן יעדער מאָל אַ נייַע זייַל איז מוסיף אָדער אַוועקגענומען צו די לוקאַפּ טיש. דאָס כאַפּאַנז ווייַל די סינטאַקס VPR ריקווייערז איר צו ספּעציפיצירן די פול קייט פון די זוכן און די ספּעציפיש זייַל נומער פֿאַר דאַטן יקסטראַקשאַן. געוויינטלעך, ביידע די געגעבן קייט און די זייַל נומער טוישן ווען איר ויסמעקן אַ זייַל אָדער אַרייַנלייגן אַ נייַע.
באַשלוס: און ווידער פאַנגקשאַנז זענען אין אַ ייַלן צו העלפן ינדעקס (אינדעקס) און מער יקספּאָוזד (מאַטש). אין די פאָרמולע אינדעקס + גלייַכן איר סעפּעראַטלי דעפינירן זוכן און ריטריוואַל שפאלטן, און ווי אַ רעזולטאַט, איר קענען ויסמעקן אָדער אַרייַנלייגן ווי פילע שפאלטן ווי איר ווילט אָן זאָרג וועגן איר דאַרפֿן צו דערהייַנטיקן אַלע פֿאַרבונדענע זוכן פאָרמולאַס.
4. צעל באַווייַזן זענען גאַרבאַלד ווען קאַפּיינג אַ פאָרמולע
דער כעדינג דערקלערט די עסאַנס פון די פּראָבלעם יגזאָסטיוולי, רעכט?
באַשלוס: שטענדיק נוצן אַבסאָלוט צעל באַווייַזן (מיט דעם סימבאָל $) אויף רעקאָרדס די קייט, פֿאַר בייַשפּיל $A$2:$C$100 or $ א: $ C. אין די פאָרמולע באַר, איר קענען געשווינד באַשטימען די לינק טיפּ דורך געבן אַ קליק F4.
VLOOKUP - ארבעטן מיט די פאַנגקשאַנז IFERROR און ISERROR
אויב איר טאָן נישט וועלן צו יבערשרעקן ניצערס מיט טעות אַרטיקלען # N / A, # וואָלו! or # נאָמען?, איר קענען ווייַזן אַ ליידיק צעל אָדער דיין אייגענע אָנזאָג. איר קענען טאָן דאָס דורך פּלייסינג VPR אין אַ פֿונקציע פאַלש (IFERROR) אין עקססעל 2013, 2010 און 2007 אָדער נוצן אַ פּלאַץ פון פאַנגקשאַנז אויב + טעות (IF + ISERROR) אין פריער ווערסיעס.
VLOOKUP: ארבעטן מיט די IFERROR פונקציע
פונקציע סינטאַקס פאַלש (IFERROR) איז פּשוט און רעדט פֿאַר זיך:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
דאָס הייסט, פֿאַר דער ערשטער אַרגומענט איר אַרייַנלייגן די ווערט צו זיין אָפּגעשטעלט פֿאַר אַ טעות, און פֿאַר די רגע אַרגומענט איר ספּעציפיצירן וואָס צו צוריקקומען אויב אַ טעות איז געפֿונען.
פֿאַר בייַשפּיל, דעם פאָרמולע קערט אַ ליידיק צעל אויב די ווערט איר זוכט פֿאַר איז ניט געפֿונען:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
אויב איר ווילן צו ווייַזן דיין אייגענע אָנזאָג אַנשטאָט פון די נאָרמאַל טעות אָנזאָג פון די פֿונקציע VPR, שטעלן עס אין ציטירט, ווי אַזוי:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ארבעטן מיט די ISERROR פונקציע
זינט די פֿונקציע פאַלש ארויס אין עקססעל 2007, ווען ארבעטן אין פריער ווערסיעס איר וועט האָבן צו נוצן די קאָמבינאַציע IF (אויב) און EOSHIBKA (ISERROR) ווי דאָס:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
פֿאַר בייַשפּיל, די פאָרמולע IF+ISERROR+VLOOKUP, ענלעך צו די פאָרמולע IFERROR+VLOOKUPגעוויזן אויבן:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
דאָס איז אַלע פֿאַר הייַנט. איך האָפֿן דעם קורץ טוטאָריאַל וועט העלפֿן איר צו האַנדלען מיט אַלע מעגלעך מיסטייקס. VPR און מאַכן דיין פאָרמולאַס אַרבעט ריכטיק.