İçerik
Bu ders, bir işlevin bir işlevin olduğu bir durumla nasıl hızlı bir şekilde başa çıkılacağını açıklar. VPR (DÜŞEYARA) Excel 2013, 2010, 2007 ve 2003'te çalışmak istemiyor ve yaygın hataların nasıl belirlenip düzeltileceği ve sınırlamaların nasıl aşılacağı VPR.
Önceki birkaç makalede, işlevin çeşitli yönlerini araştırdık. VPR Excel'de. Bunları dikkatlice okuduysanız, artık bu alanda uzman olmalısınız. Ancak, birçok Excel uzmanının inandığı sebepsiz değildir. VPR daha karmaşık özelliklerden biridir. Birçok sorun ve hatanın kaynağı haline gelen bir dizi sınırlama ve özelliğe sahiptir.
Bu makalede hataların basit açıklamalarını bulacaksınız. #NS (#Yok), # İSİM? (#Isim ve # DEĞER! (#DEĞER!) işlevle çalışırken görünen VPR, yanı sıra bunlarla başa çıkma teknikleri ve yöntemleri. En yaygın vakalarla ve bunun en bariz nedenleriyle başlayacağız. VPR çalışmıyor, bu nedenle örnekleri makalede verildikleri sırayla incelemek daha iyidir.
Excel'deki DÜŞEYARA işlevinde #YOK hatasını düzeltme
ile formüllerde VPR hata iletisi #NS (#N/A) şu anlama gelir kullanılamaz (veri yok) – Excel aradığınız değeri bulamadığında görünür. Bu birkaç nedenden dolayı olabilir.
1. İstenen değer yanlış yazılmış
Önce bu öğeyi kontrol etmek iyi bir fikir! Yazım hataları genellikle binlerce satırdan oluşan çok büyük miktarda veriyle çalıştığınızda veya aradığınız değer bir formüle yazıldığında ortaya çıkar.
2. DÜŞEYARA ile yaklaşık bir eşleşme aranırken #YOK hatası
Yaklaşık eşleşme arama koşuluna sahip bir formül kullanırsanız, yani argüman Menzil araması (aralık_bak) DOĞRU veya belirtilmemiş, formülünüz bir hata bildirebilir # N / A iki durumda:
- Aranacak değer, aranan dizideki en küçük değerden küçük.
- Arama sütunu artan düzende sıralanmamıştır.
3. DÜŞEYARA ile tam eşleşme ararken #YOK hatası
Tam bir eşleşme arıyorsanız, yani argüman Menzil araması (aralık_bak) YANLIŞ ve tam değer bulunamadı, formül ayrıca bir hata bildirir # N / A. Bir işlevle tam ve yaklaşık eşleşmeleri nasıl arayacağınız hakkında daha fazla bilgi edinin VPR.
4. Arama sütunu en solda değil
Muhtemelen bildiğiniz gibi, en önemli sınırlamalardan biri VPR yani sola bakamaz, bu nedenle tablonuzdaki arama sütunu en solda olmalıdır. Uygulamada, genellikle bunu unutuyoruz, bu da çalışmayan bir formüle ve bir hataya yol açıyor. # N / A.
Karar: Arama sütunu en solda olacak şekilde veri yapısını değiştirmek mümkün değilse, bir işlev kombinasyonu kullanabilirsiniz. INDEX (INDEX) ve DAHA MARUZ KALMIŞ (MATCH) için daha esnek bir alternatif olarak VPR.
5. Sayılar metin olarak biçimlendirilir
Başka bir hata kaynağı # N / A ile formüllerde VPR ana tablodaki veya arama tablosundaki metin biçimindeki sayılardır.
Bu genellikle, harici veritabanlarından bilgi aldığınızda veya baştaki sıfırı tutmak için bir sayıdan önce bir kesme işareti yazdığınızda olur.
Bir sayının metin biçimindeki en belirgin işaretleri aşağıdaki şekilde gösterilmektedir:
Ayrıca, sayılar biçiminde saklanabilir genel (Genel). Bu durumda, göze çarpan tek bir özellik vardır - sayılar hücrenin sol kenarına hizalanırken, varsayılan olarak sağ kenara hizalanır.
Karar: Tek bir değerse, sadece hata simgesine tıklayın ve seçin Sayıya Dönüştür (Sayıya Dönüştür) öğesini bağlam menüsünden seçin.
Bu, çok sayıda sayının olduğu bir durumsa, onları seçin ve seçilen alana sağ tıklayın. Görüntülenen bağlam menüsünde, biçim Hücreler (Hücreleri Biçimlendir) > sekmesi Numara (Sayı) > biçim Numara (Sayısal) ve tuşuna basın OK.
6. Başında veya sonunda bir boşluk var
Bu, hatanın en az belirgin nedenidir. # N / A işlevde VPR, çünkü özellikle büyük tablolarla çalışırken, verilerin çoğu ekran dışındayken bu fazladan boşlukları görmek görsel olarak zor.
Çözüm 1: Ana tabloda fazladan boşluklar (DÜŞEYARA işlevinin olduğu yerde)
Ana tabloda fazladan boşluklar görünüyorsa, argümanı içine alarak formüllerin doğru çalışmasını sağlayabilirsiniz. lookup_value (arama_değeri) bir işleve TRIM (KIRMIZI):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Çözüm 2: Arama tablosunda fazladan boşluklar (arama sütununda)
Arama sütununda fazladan boşluk varsa – basit yollar # N / A ile formülde VPR kaçınılamaz. Onun yerine VPR İşlevlerin bir kombinasyonuyla bir dizi formülü kullanabilirsiniz. INDEX (İNDEKS), DAHA MARUZ KALMIŞ (MAÇ) ve TRIM (KIRMIZI):
=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))
Bu bir dizi formülü olduğundan, tuşuna basmayı unutmayın. Ctrl + Üst Karakter + Enter her zamanki yerine Keşfetformülü doğru girmek için
Hata #DEĞER! DÜŞEYARA ile formüllerde
Çoğu durumda, Microsoft Excel bir hata bildirir # DEĞER! (#DEĞER!) Formülde kullanılan değer veri türüyle eşleşmediğinde. İlişkin VPR, o zaman hatanın genellikle iki nedeni vardır # DEĞER!.
1. Aradığınız değer 255 karakterden uzun
Dikkatli olun: işlev VPR 255 karakterden fazla olan değerleri arayamaz. Aradığınız değer bu sınırı aşarsa bir hata mesajı alırsınız. # DEĞER!.
Karar: Bir dizi özellik kullanın İNDEKS+KAÇINCI (INDEX + MAÇ). Aşağıda bu görev için gayet iyi olacak bir formül var:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Arama çalışma kitabının tam yolu belirtilmemiş
Başka bir çalışma kitabından veri alıyorsanız, o dosyanın tam yolunu belirtmelisiniz. Daha spesifik olarak, çalışma kitabı adını (uzantı dahil) köşeli parantez [ ] içine, ardından sayfa adını ve ardından bir ünlem işareti eklemelisiniz. Kitap veya sayfa adının boşluk içermesi durumunda, tüm bu yapı kesme işaretleri içine alınmalıdır.
İşte fonksiyonun tam yapısı VPR başka bir kitapta aramak için:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Gerçek formül şöyle görünebilir:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Bu formül hücre değerini arayacaktır. A2 bir sütunda B sayfada Sheet1 çalışma kitabında Yeni Fiyatlar ve ilgili değeri sütundan çıkarın D.
Tablo yolunun herhangi bir kısmı atlanırsa, işleviniz VPR çalışmayacak ve bir hata bildirecek # DEĞER! (arama tablosunun bulunduğu çalışma kitabı şu anda açık olsa bile).
İşlev hakkında daha fazla bilgi için VPRbaşka bir Excel dosyasına atıfta bulunarak, şu derse bakın: DÜŞEYARA kullanarak başka bir çalışma kitabını arama.
3. Bağımsız Değişken Sütun_sayısı 1'den küçük
Birinin şu değerden daha düşük bir değer girdiği bir durumu hayal etmek zor. 1değerin çıkarılacağı sütunu belirtmek için. Bu bağımsız değişkenin değerinin, içinde yuvalanmış başka bir Excel işlevi tarafından hesaplanması mümkün olsa da VPR.
Yani, eğer olursa, argüman sütun_indis_sayısı (column_number) daha az 1işlev VPR ayrıca bir hata bildirecek # DEĞER!.
eğer argüman sütun_indis_sayısı (sütun_sayısı) verilen dizideki sütun sayısından büyükse, VPR bir hata bildirecek # BAŞV! (#SSYL!).
#NAME hatası? DÜŞEYARA'da
En basit durum bir hatadır # İSİM? (#NAME?) – yanlışlıkla hatalı bir işlev adı yazarsanız görünür.
Çözüm açık – yazımınızı kontrol edin!
DÜŞEYARA çalışmıyor (sınırlamalar, uyarılar ve kararlar)
Oldukça karmaşık sözdizimine ek olarak, VPR diğer Excel işlevlerinden daha fazla sınırlamaya sahiptir. Bu sınırlamalar nedeniyle, görünüşte basit formüller VPR genellikle beklenmedik sonuçlara yol açar. Aşağıda, birkaç yaygın senaryo için çözümler bulacaksınız. VPR Hata.
1. DÜŞEYARA büyük/küçük harf duyarlı değildir
işlev VPR büyük/küçük harf ayrımı yapmaz ve küçük ve büyük harfleri aynı kabul eder. Bu nedenle, tabloda yalnızca duruma göre farklılık gösteren birkaç öğe varsa, DÜŞEYARA işlevi, büyük/küçük harfe bakılmaksızın bulunan ilk öğeyi döndürür.
Karar: İle birlikte dikey arama (ARAMA, SUMPRODUCT, INDEX ve MATCH) gerçekleştirebilen başka bir Excel işlevi kullanın. TAMDavayı ayırt eden A. Daha fazla ayrıntı için, Excel'de DÜŞEYARA büyük/küçük harf duyarlı hale getirmenin 4 yolu dersinden öğrenebilirsiniz.
2. DÜŞEYARA, bulunan ilk değeri döndürür
Bildiğin üzere, VPR bulunan ilk eşleşmeye karşılık gelen verilen sütundaki değeri döndürür. Ancak, istediğiniz değerin 2., 3., 4. veya başka herhangi bir tekrarını çıkarmasını sağlayabilirsiniz. Tüm yinelenen değerleri çıkarmanız gerekiyorsa, bir işlev kombinasyonuna ihtiyacınız olacaktır. INDEX (İNDEKS), EN AZ (KÜÇÜK) ve LINE (SIRA).
3. Tabloya bir sütun eklendi veya kaldırıldı
Ne yazık ki, formüller VPR arama tablosuna her yeni sütun eklendiğinde veya kaldırıldığında çalışmayı durdurun. Bu, sözdizimi nedeniyle olur VPR aramanın tüm aralığını ve veri çıkarma için belirli sütun numarasını belirtmenizi gerektirir. Doğal olarak, bir sütunu sildiğinizde veya yenisini eklediğinizde hem verilen aralık hem de sütun numarası değişir.
Karar: Ve yine işlevler yardım etmek için acele ediyor INDEX (INDEX) ve DAHA MARUZ KALMIŞ (KİBRİT). formülde İNDEKS+KAÇINCI Arama ve alma sütunlarını ayrı ayrı tanımlarsınız ve sonuç olarak ilgili tüm arama formüllerini güncelleme endişesi duymadan istediğiniz kadar sütun silebilir veya ekleyebilirsiniz.
4. Formül kopyalanırken hücre referansları bozuluyor
Bu başlık, sorunun özünü kapsamlı bir şekilde açıklıyor, değil mi?
Karar: Her zaman mutlak hücre referansları kullanın ( $) örneğin aralığı kaydeder $ A $ 2: $ C $ 100 or A $: C $. Formül çubuğunda, tıklayarak bağlantı türünü hızlı bir şekilde değiştirebilirsiniz. F4.
DÜŞEYARA – IFERROR ve ISERROR işlevleriyle çalışma
Kullanıcıları hata mesajlarıyla korkutmak istemiyorsanız # N / A, # DEĞER! or # İSİM?, boş bir hücre veya kendi mesajınızı gösterebilirsiniz. yerleştirerek bunu yapabilirsiniz VPR bir fonksiyona EĞERHATA (IFERROR) Excel 2013, 2010 ve 2007'de veya bir dizi işlev kullanın EĞER+ISERROR (IF+ISERROR) önceki sürümlerde.
DÜŞEYARA: EĞERHATA işleviyle çalışma
İşlev sözdizimi EĞERHATA (IFERROR) basittir ve kendisi için konuşur:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Yani, ilk argüman için bir hata için kontrol edilecek değeri eklersiniz ve ikinci argüman için bir hata bulunursa ne döndürüleceğini belirtirsiniz.
Örneğin, aradığınız değer bulunamazsa bu formül boş bir hücre döndürür:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Fonksiyonun standart hata mesajı yerine kendi mesajınızı görüntülemek istiyorsanız VPR, tırnak içine alın, şöyle:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
DÜŞEYARA: ISERROR işleviyle çalışma
Fonksiyon beri EĞERHATA Excel 2007'de göründü, önceki sürümlerde çalışırken kombinasyonu kullanmanız gerekecek IF (IF) ve EOSHIBKA (ISERROR) şöyle:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Örneğin, formül EĞER+ISERROR+DÜŞEYARA, formüle benzer EĞER HATA+DÜŞEYARAYukarıda verilen:
=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;ЛОЖЬ))
Hepsi bugün için. Umarım bu kısa eğitim, olası tüm hatalarla başa çıkmanıza yardımcı olur. VPR ve formüllerinizin doğru çalışmasını sağlayın.