შინაარსი
ეს გაკვეთილი განმარტავს, თუ როგორ სწრაფად გაუმკლავდეთ სიტუაციას, როდესაც ფუნქცია VPR (VLOOKUP) არ სურს იმუშაოს Excel 2013, 2010, 2007 და 2003 წლებში და როგორ ამოიცნოს და გამოასწოროს საერთო შეცდომები და გადალახოს შეზღუდვები VPR.
რამდენიმე წინა სტატიაში ჩვენ გამოვიკვლიეთ ფუნქციის სხვადასხვა ასპექტები VPR Excel-ში. თუ ყურადღებით წაიკითხეთ ისინი, ახლა ამ დარგის ექსპერტი უნდა იყოთ. თუმცა, ეს არ არის უმიზეზოდ, რასაც Excel-ის ბევრი ექსპერტი თვლის VPR ერთ-ერთი ყველაზე რთული თვისება. მას აქვს მთელი რიგი შეზღუდვები და მახასიათებლები, რომლებიც მრავალი პრობლემისა და შეცდომის წყარო ხდება.
ამ სტატიაში ნახავთ შეცდომების მარტივ ახსნას #ატ (# N/A), #NAME? (#NAME?) და #VALUE! (#VALUE!), რომლებიც ჩნდება ფუნქციასთან მუშაობისას VPR, ასევე მათთან გამკლავების ტექნიკა და მეთოდები. ჩვენ დავიწყებთ ყველაზე გავრცელებული შემთხვევებით და ყველაზე აშკარა მიზეზებით. VPR არ მუშაობს, ამიტომ უმჯობესია შეისწავლოთ მაგალითები იმ თანმიმდევრობით, რომლითაც ისინი მოცემულია სტატიაში.
Excel-ში VLOOKUP ფუნქციაში #N/A შეცდომის გამოსწორება
ფორმულებში ერთად VPR შეცდომა #ატ (# N/A) ნიშნავს მიუწვდომელია (მონაცემების გარეშე) – ჩნდება, როდესაც Excel ვერ პოულობს მნიშვნელობას, რომელსაც ეძებთ. ეს შეიძლება მოხდეს რამდენიმე მიზეზის გამო.
1. სასურველი მნიშვნელობა არასწორად არის დაწერილი
კარგი იდეაა ჯერ ეს ნივთი შეამოწმოთ! შეცდომა ხშირად ჩნდება, როდესაც მუშაობთ ძალიან დიდი რაოდენობით მონაცემებთან, რომელიც შედგება ათასობით სტრიქონისგან, ან როდესაც მნიშვნელობა, რომელსაც ეძებთ, იწერება ფორმულაში.
2. #N/A შეცდომა VLOOKUP-თან სავარაუდო შესატყვისის ძიებისას
თუ იყენებთ ფორმულას სავარაუდო შესატყვისი ძიების პირობით, ანუ არგუმენტი დიაპაზონის_ძიება (range_lookup) არის TRUE ან არ არის მითითებული, თქვენმა ფორმულამ შეიძლება შეატყობინოს შეცდომა # N / A ორ შემთხვევაში:
- შესანახი მნიშვნელობა ნაკლებია მასივის უმცირეს მნიშვნელობაზე, რომელსაც ეძებთ.
- საძიებო სვეტი არ არის დალაგებული ზრდის მიხედვით.
3. #N/A შეცდომა VLOOKUP-თან ზუსტი შესატყვისის ძიებისას
თუ თქვენ ეძებთ ზუსტ შესატყვისს, ანუ არგუმენტს დიაპაზონის_ძიება (range_lookup) არის FALSE და ზუსტი მნიშვნელობა ვერ მოიძებნა, ფორმულა ასევე შეატყობინებს შეცდომას # N / A. შეიტყვეთ მეტი იმის შესახებ, თუ როგორ მოიძიოთ ზუსტი და მიახლოებითი შესატყვისები ფუნქციით VPR.
4. საძიებო სვეტი არ არის ყველაზე მარცხენა
როგორც მოგეხსენებათ, ერთ-ერთი ყველაზე მნიშვნელოვანი შეზღუდვაა VPR ეს არის ის, რომ მას არ შეუძლია მარცხნივ, შესაბამისად, საძიებო სვეტი თქვენს ცხრილში უნდა იყოს მარცხნივ. პრაქტიკაში ხშირად გვავიწყდება ეს, რაც იწვევს არასამუშაო ფორმულას და შეცდომას. # N / A.
გადაწყვეტილება: თუ შეუძლებელია მონაცემთა სტრუქტურის შეცვლა ისე, რომ საძიებო სვეტი იყოს ყველაზე მარცხნივ, შეგიძლიათ გამოიყენოთ ფუნქციების კომბინაცია ინდექსი (INDEX) და უფრო გამოვლენილი (MATCH), როგორც უფრო მოქნილი ალტერნატივა VPR.
5. ნომრები ფორმატირებულია ტექსტად
შეცდომის კიდევ ერთი წყარო # N / A ფორმულებში VPR არის ნომრები ტექსტის ფორმატში მთავარ ცხრილში ან საძიებო ცხრილში.
ეს ჩვეულებრივ ხდება, როდესაც თქვენ იმპორტირებთ ინფორმაციას გარე მონაცემთა ბაზებიდან, ან როდესაც აკრიფებთ აპოსტროფს რიცხვის წინ, რათა შეინარჩუნოთ პირველი ნული.
რიცხვის ყველაზე აშკარა ნიშნები ტექსტის ფორმატში ნაჩვენებია ქვემოთ მოცემულ ფიგურაში:
გარდა ამისა, ნომრების შენახვა შესაძლებელია ფორმატში ზოგადი (გენერალი). ამ შემთხვევაში, მხოლოდ ერთი შესამჩნევი თვისებაა - რიცხვები გასწორებულია უჯრედის მარცხენა კიდეზე, ხოლო ნაგულისხმევად ისინი სწორდება მარჯვენა კიდეზე.
გადაწყვეტილება: თუ ეს არის ერთი მნიშვნელობა, უბრალოდ დააწკაპუნეთ შეცდომის ხატულაზე და აირჩიეთ გადაიყვანეთ რიცხვად (Convert to Number) კონტექსტური მენიუდან.
თუ ეს არის სიტუაცია მრავალი რიცხვით, აირჩიეთ ისინი და დააწკაპუნეთ მარჯვენა ღილაკით არჩეულ ზონაზე. კონტექსტურ მენიუში, რომელიც გამოჩნდება, აირჩიეთ უჯრედების ფორმატი (უჯრედების ფორმატირება) > ჩანართი ხმების (ნომერი) > ფორმატი ხმების (რიცხვითი) და დააჭირეთ OK.
6. არის სივრცე დასაწყისში ან ბოლოს
ეს არის შეცდომის ყველაზე ნაკლებად აშკარა მიზეზი. # N / A ფუნქციაში VPR, ვინაიდან ვიზუალურად რთულია ამ დამატებითი სივრცეების დანახვა, განსაკუთრებით დიდ მაგიდებთან მუშაობისას, როდესაც მონაცემების უმეტესობა ეკრანის გარეთაა.
გამოსავალი 1: დამატებითი სივრცეები მთავარ ცხრილში (სადაც არის VLOOKUP ფუნქცია)
თუ მთავარ ცხრილში გამოჩნდება დამატებითი სივრცეები, თქვენ შეგიძლიათ დარწმუნდეთ, რომ ფორმულები სწორად მუშაობენ არგუმენტის მიმაგრებით lookup_value (lookup_value) ფუნქციაში TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
გამოსავალი 2: დამატებითი სივრცეები საძიებო ცხრილში (საძიებო სვეტში)
თუ დამატებითი ადგილებია საძიებო სვეტში - მარტივი გზები # N / A ფორმულაში VPR თავიდან აცილება არ შეიძლება. Მაგივრად VPR შეგიძლიათ გამოიყენოთ მასივის ფორმულა ფუნქციების კომბინაციით ინდექსი (INDEX), უფრო გამოვლენილი (მატჩი) და 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! (#VALUE!) როდესაც ფორმულაში გამოყენებული მნიშვნელობა არ ემთხვევა მონაცემთა ტიპს. რაც შეეხება VPR, მაშინ, როგორც წესი, შეცდომის ორი მიზეზი არსებობს #VALUE!.
1. მნიშვნელობა, რომელსაც ეძებთ, აღემატება 255 სიმბოლოს
ფრთხილად იყავით: ფუნქციონირება VPR არ შეუძლია მოძებნოს მნიშვნელობები, რომლებიც შეიცავს 255 სიმბოლოზე მეტს. თუ მნიშვნელობა, რომელსაც ეძებთ, აღემატება ამ ლიმიტს, თქვენ მიიღებთ შეცდომის შეტყობინებას. #VALUE!.
გადაწყვეტილება: გამოიყენეთ რამოდენიმე ფუნქცია INDEX+MATCH (INDEX + MATCH). ქვემოთ მოცემულია ფორმულა, რომელიც ამ ამოცანის შესასრულებლად კარგად გამოდგება:
=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 ფურცელზე Sheet1 სამუშაო წიგნში ახალი ფასები და ამოიღეთ შესაბამისი მნიშვნელობა სვეტიდან D.
თუ ცხრილის ბილიკის რომელიმე ნაწილი გამოტოვებულია, თქვენი ფუნქცია VPR არ იმუშავებს და შეატყობინებს შეცდომას #VALUE! (მაშინაც კი, თუ სამუშაო წიგნი საძიებო ცხრილით ამჟამად ღიაა).
დამატებითი ინფორმაციისთვის ფუნქციის შესახებ VPRსხვა Excel ფაილზე მითითებით, იხილეთ გაკვეთილი: სხვა სამუშაო წიგნის ძიება VLOOKUP-ის გამოყენებით.
3. არგუმენტი Column_num არის 1-ზე ნაკლები
ძნელი წარმოსადგენია სიტუაცია, როდესაც ვინმე შეაქვს მნიშვნელობაზე ნაკლები 1მიუთითოს სვეტი, საიდანაც უნდა ამოიღოთ მნიშვნელობა. თუმცა შესაძლებელია, თუ ამ არგუმენტის მნიშვნელობა გამოითვლება სხვა Excel ფუნქციით, რომელიც ჩასმულია შიგნით VPR.
ასე რომ, თუ ეს მოხდება, რომ არგუმენტი col_index_num (სვეტის_ნომერი) ნაკლები 1ფუნქცია VPR ასევე შეატყობინებს შეცდომას #VALUE!.
თუ არგუმენტი col_index_num (სვეტის_ნომერი) მეტია მოცემული მასივის სვეტების რაოდენობაზე, VPR შეატყობინებს შეცდომას # REF! (#SSYL!).
შეცდომა #NAME? VLOOKUP-ში
უმარტივესი შემთხვევა შეცდომაა #NAME? (#NAME?) – გამოჩნდება, თუ შემთხვევით დაწერთ ფუნქციის სახელს შეცდომით.
გამოსავალი აშკარაა - შეამოწმეთ თქვენი მართლწერა!
VLOOKUP არ მუშაობს (შეზღუდვები, გაფრთხილებები და გადაწყვეტილებები)
საკმაოდ რთული სინტაქსის გარდა, VPR აქვს მეტი შეზღუდვა, ვიდრე ნებისმიერი სხვა Excel ფუნქცია. ამ შეზღუდვების გამო, ერთი შეხედვით მარტივი ფორმულები VPR ხშირად იწვევს მოულოდნელ შედეგებს. ქვემოთ ნახავთ გადაწყვეტილებებს რამდენიმე გავრცელებული სცენარისთვის, სადაც VPR არასწორია.
1. VLOOKUP არ არის რეგისტრირებული
ფუნქცია VPR არ განასხვავებს დიდ და პატარა ასოებს, როგორც ერთნაირად. ამიტომ, თუ ცხრილში რამდენიმე ელემენტია, რომლებიც განსხვავდება მხოლოდ შემთხვევის მიხედვით, VLOOKUP ფუნქცია დააბრუნებს პირველ ნაპოვნი ელემენტს, განურჩევლად შემთხვევისა.
გადაწყვეტილება: გამოიყენეთ Excel-ის სხვა ფუნქცია, რომელსაც შეუძლია შეასრულოს ვერტიკალური ძიება (LOOKUP, SUMPRODUCT, INDEX და MATCH) კომბინაციაში EXACTA რომ განასხვავებს შემთხვევას. დამატებითი ინფორმაციისთვის შეგიძლიათ ისწავლოთ გაკვეთილიდან – Excel-ში VLOOKUP-ის რეგისტრის მგრძნობიარე 4 გზა.
2. VLOOKUP აბრუნებს ნაპოვნი პირველ მნიშვნელობას
როგორც უკვე იცით, VPR აბრუნებს მნიშვნელობას მოცემული სვეტიდან, რომელიც შეესაბამება პირველ ნაპოვნი მატჩს. თუმცა, თქვენ შეგიძლიათ ამოიღოთ თქვენთვის სასურველი მნიშვნელობის მე-2, მე-3, მე-4 ან ნებისმიერი სხვა გამეორება. თუ თქვენ გჭირდებათ ყველა დუბლიკატი მნიშვნელობის ამოღება, დაგჭირდებათ ფუნქციების კომბინაცია ინდექსი (INDEX), სულ მცირე (პატარა) და LINE (ROW).
3. ცხრილს დაემატა ან ამოიღეს სვეტი
სამწუხაროდ, ფორმულები VPR შეწყვიტოს მუშაობა ყოველ ჯერზე, როდესაც ახალი სვეტი ემატება ან წაიშლება საძიებო ცხრილში. ეს ხდება სინტაქსის გამო VPR მოითხოვს, რომ მიუთითოთ ძიების სრული დიაპაზონი და მონაცემთა ამოღების კონკრეტული სვეტის ნომერი. ბუნებრივია, როგორც მოცემული დიაპაზონი, ასევე სვეტის ნომერი იცვლება სვეტის წაშლის ან ახალის ჩასმისას.
გადაწყვეტილება: და ისევ ფუნქციები ჩქარობენ დახმარებას ინდექსი (INDEX) და უფრო გამოვლენილი (მატჩი). ფორმულაში INDEX+MATCH თქვენ ცალ-ცალკე განსაზღვრავთ ძიების და მოძიების სვეტებს და შედეგად, შეგიძლიათ წაშალოთ ან ჩასვათ იმდენი სვეტი, რამდენიც გსურთ, ყველა დაკავშირებული საძიებო ფორმულის განახლების ფიქრის გარეშე.
4. ფორმულის კოპირებისას უჯრედების მითითებები ირღვევა
ეს სათაური ამომწურავად ხსნის პრობლემის არსს, არა?
გადაწყვეტილება: ყოველთვის გამოიყენეთ უჯრედის აბსოლუტური მითითებები (სიმბოლოებით $) ჩანაწერებზე დიაპაზონი, მაგალითად $A$2:$C$100 or $A:$C. ფორმულების ზოლში შეგიძლიათ სწრაფად შეცვალოთ ბმულის ტიპი დაწკაპუნებით F4.
VLOOKUP – მუშაობა ფუნქციებთან IFERROR და ISERROR
თუ არ გსურთ შეაშინოთ მომხმარებლები შეცდომის შეტყობინებებით # N / A, #VALUE! or #NAME?, შეგიძლიათ აჩვენოთ ცარიელი უჯრედი ან საკუთარი შეტყობინება. ამის გაკეთება შეგიძლიათ განთავსებით VPR ფუნქციაში Iferror (IFERROR) Excel 2013, 2010 და 2007 წლებში ან გამოიყენეთ რამდენიმე ფუნქცია IF+ISERROR (IF+ISERROR) წინა ვერსიებში.
VLOOKUP: მუშაობა IFERROR ფუნქციით
ფუნქციის სინტაქსი 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 ფუნქციით
ფუნქციიდან გამომდინარე Iferror გამოჩნდა Excel 2007-ში, წინა ვერსიებში მუშაობისას მოგიწევთ კომბინაციის გამოყენება IF (თუ) და ეოშიბკა (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 და გააკეთე შენი ფორმულები სწორად იმუშაოს.