Değerleri değiştirmek için DÜŞEYARA işlevini kullanma

Kim çok tembel veya okumaya vakti yok - videoyu izleyin. Ayrıntılar ve nüanslar aşağıdaki metindedir.

Sorunun formülasyonu

Yani, iki tablomuz var - sipariş tablosu и fiyat listesi:

Görev, fiyat listesindeki fiyatları, daha sonra maliyeti hesaplayabilmeniz için ürünün adına odaklanarak otomatik olarak sipariş tablosuna değiştirmektir.

Çözüm

Excel fonksiyon setinde, kategori altında Referanslar ve diziler (Arama ve referans) bir işlev var VPR (DÜŞEYARA).Bu işlev, yukarıdan aşağıya hareket eden belirtilen tablonun (fiyat listesi) en soldaki sütununda belirli bir değeri arar (örneğimizde, bu "Elma" kelimesidir) ve onu bulduktan sonra bitişik hücrenin içeriğini görüntüler. (23 ruble) .Şematik olarak, bu fonksiyonun çalışması şu şekilde gösterilebilir:

Fonksiyonun daha fazla kullanımını kolaylaştırmak için, bir kerede bir şey yapın - fiyat listesindeki hücre aralığına kendi adınızı verin. Bunu yapmak için, "başlık" (G3: H19) hariç fiyat listesinin tüm hücrelerini seçin, menüden seçin Ekle – Ad – Ata (Ekle — Ad — Tanımla) basın veya CTRL + F3 ve herhangi bir ad girin (boşluk yok) Ücret… Şimdi, gelecekte bu ismi fiyat listesine bağlamak için kullanabilirsiniz.

Şimdi işlevi kullanıyoruz VPR… Girileceği hücreyi seçin (D3) ve sekmeyi açın Formüller – Fonksiyon Ekleme (Formüller — İşlev Ekle)… Kategoride Referanslar ve diziler (Arama ve Referans) işlevi bul VPR (DÜŞEYARA) ve basın OK… İşlev için argümanları girmek için bir pencere görünecektir:

Değerleri değiştirmek için DÜŞEYARA işlevini kullanma

Onları sırayla dolduruyoruz:

  • İstenen değer (Arama Değeri) – fonksiyonun fiyat listesinin en soldaki sütununda bulması gereken ürünün adı. Bizim durumumuzda, B3 hücresinden “Elmalar” kelimesi.
  • tablo (Masa dizisi) – uXNUMXbuXNUMXbare istenen değerlerin alındığı bir tablo, yani fiyat listemiz. Referans olarak, daha önce verilen kendi adımızı “Price” kullanıyoruz. Bir isim vermediyseniz, sadece tabloyu seçebilirsiniz, ancak düğmeye basmayı unutma F4bağlantıyı dolar işaretleri ile sabitlemek için, çünkü aksi takdirde formülümüzü D3:D30 sütunundaki hücrelerin geri kalanına kopyalarken aşağı kayar.
  • Sütun_numarası (Sütun dizin numarası) – Seri numarası (harf değil!) Fiyat listesindeki fiyat değerlerini alacağımız sütunun. İsimlerin bulunduğu fiyat listesinin ilk sütunu 1 numaralıdır, bu nedenle 2 numaralı sütundan fiyata ihtiyacımız var.
  • aralık_bak (Menzil araması) – bu alana sadece iki değer girilebilir: YANLIŞ veya DOĞRU:
      • Bir değer girilirse 0 or UZANMAK (YANLIŞ), o zaman aslında bu yalnızca aramaya izin verildiği anlamına gelir tam eşleşme, yani fonksiyon, fiyat listesinde sipariş tablosunda belirtilen standart dışı kalemi bulamazsa (örneğin “Hindistan Cevizi” girilirse), #N/A (veri yok) hatası üretecektir.
      • Bir değer girilirse 1 or DOĞRU (DOĞRU), o zaman bu, aramaya tam olarak izin vermediğiniz anlamına gelir, ancak yaklaşık eşleşme, yani “hindistan cevizi” durumunda, işlev “hindistan cevizi” a mümkün olduğunca yakın bir ada sahip bir ürün bulmaya çalışacak ve bu ad için fiyatı iade edecektir. Çoğu durumda, böyle yaklaşık bir ikame, gerçekte orada olan yanlış ürünün değerini değiştirerek kullanıcı üzerinde bir oyun oynayabilir! Bu nedenle, çoğu gerçek iş sorunu için, yaklaşık aramaya izin vermemek en iyisidir. Bunun istisnası, metin yerine sayıları aradığımız zamandır – örneğin, Adım İndirimlerini hesaplarken.

Her şey! basmak kalıyor OK ve girilen işlevi tüm sütuna kopyalayın.

# N / A hataları ve bunların bastırılması

işlev VPR (DÜŞEYARA) #YOK hatası döndürür (#Yok) Eğer bir:

  • Tam arama etkin (argüman Aralık görünümü = 0) ve istenen ad içinde değil tablo.
  • Kaba arama dahil (Aralık görünümü = 1), Fakat tabloaramanın yapıldığı , artan ad sırasına göre sıralanmaz.
  • Adın gerekli değerinin geldiği hücrenin formatı (örneğin, bizim durumumuzda B3) ve tablonun ilk sütununun (F3: F19) hücrelerinin formatı farklıdır (örneğin, sayısal ve metin). ). Bu durum özellikle metin adları yerine sayısal kodlar (hesap numaraları, tanımlayıcılar, tarihler vb.) kullanıldığında tipiktir. Bu durumda, işlevleri kullanabilirsiniz. Ч и METİN veri biçimlerini dönüştürmek için. Bunun gibi bir şey görünecek:

    =DÜŞEYARA(METİN(B3),fiyat,0)

    Bununla ilgili daha fazla bilgiyi buradan okuyabilirsiniz.

  • Kod boşluklar veya görünmez yazdırılamayan karakterler (satır sonları vb.) içerdiğinden işlev gerekli değeri bulamıyor. Bu durumda, metin işlevlerini kullanabilirsiniz. TRIM (KIRMIZI) и YAZDIR(TEMİZ) onları kaldırmak için:

    =DÜŞEYARA(KAZAN ALANLAR(TEMİZ(B3)),fiyat,0)

    =DÜŞEYARA(KES(TEMİZ(B3));fiyat;0)

Hata mesajını bastırmak için # N / A (#Yok) fonksiyonun tam eşleşme bulamadığı durumlarda fonksiyonu kullanabilirsiniz. EĞERHATA (EĞERHATA)… Yani, örneğin, bu yapı DÜŞEYARA tarafından oluşturulan tüm hataları yakalar ve bunları sıfırlarla değiştirir:

= EĞERHATA (DÜŞEYARA (B3, fiyat, 2, 0), 0)

= EĞERHATA (DÜŞEYARA (B3; fiyat; 2; 0); 0)

PS

Bir değeri değil, tüm seti bir kerede çıkarmanız gerekiyorsa (birkaç farklı değer varsa), o zaman dizi formülü ile şamanlaştırmanız gerekecektir. veya Office 365'in yeni DÜŞEYARA özelliğini kullanın.

 

  • DÜŞEYARA işlevinin geliştirilmiş bir sürümü (DÜŞEYARA 2).
  • DÜŞEYARA işlevini kullanarak adım (aralık) indirimlerinin hızlı hesaplanması.
  • INDEX ve MATCH işlevlerini kullanarak "sol DÜŞEYARA" nasıl yapılır
  • Listedeki verilerle formları doldurmak için DÜŞEYARA işlevi nasıl kullanılır?
  • İlk değil, tablodaki tüm değerler bir kerede nasıl çıkarılır
  • PLEX eklentisinden DÜŞEYARA2 ve DÜŞEYARA3 işlevleri

 

Yorum bırak