ev / programlar/ Excel'de vp işlevi nasıl kullanılır. vpr işlevi nasıl çalışır?

Excel'de vpr işlevi nasıl kullanılır. vpr işlevi nasıl çalışır?

DÜŞEYARA, başka bir sütundaki verileri kullanarak belirli bir sütunda arama yapmanızı sağlayan bir Excel işlevidir. Excel'deki DÜŞEYARA işlevi, verileri bir tablodan diğerine aktarmak için de kullanılır. Üç koşul vardır:

Tablolar aynı Excel çalışma kitabında olmalıdır.
Yalnızca statik veriler arasında arama yapabilirsiniz (formüller değil).
Arama terimi, kullanılan verilerin ilk sütununda görünmelidir.

Excel'de DÜŞEYARA formülü

Russified Excel'de DÜŞEYARA sözdizimi şöyledir:

DÜŞEYARA (arama kriteri; veri aralığı; sonuç sütun numarası; arama koşulu)

Nihai sonucu bulmak için parantez içindeki argümanlar gereklidir.

Arama kriteri

hücre adresi Excel çalışma sayfası, tabloda bir arama yapmak için verileri gösterir.

Veri aralığı

Aramanın gerçekleştirildiği tüm adresler. İlk sütun, arama teriminin bulunduğu sütun olmalıdır.

Toplam için sütun numarası

Bir eşleşme bulunduğunda değerin alınacağı sütun numarası.

Arama koşulu

(1) aranacak yaklaşık bir eşleşmeyi veya bir tam eşleşmeyi (0) belirten bir Boole değeri (doğru / 1 veya yanlış / 0).

Excel'de DÜŞEYARA: İşlev Örnekleri

Fonksiyonun prensibi basittir. İlk argüman arama kriterlerini içerir. Tabloda bir eşleşme bulunur bulunmaz (ikinci argüman), bulunan satırın gerekli sütunundan (üçüncü argüman) bilgi alınır ve hücreye formülle değiştirilir.
DÜŞEYARA'nın basit bir uygulaması, bir Excel elektronik tablosunda değerleri bulmaktır. Büyük miktarda veride önemlidir.

Ay adına göre gerçekten piyasaya sürülen ürünlerin sayısını bulalım.
Sonuç, tablonun sağında görüntülenir. H3 adresli hücreye istenilen değeri gireceğiz. Örnekte, bu ayın adı olacaktır.
H4 hücresine işlevin kendisini girin. Bu manuel olarak yapılabilir veya sihirbazı kullanabilirsiniz. Aramak için işaretçiyi H4 hücresinin üzerine getirin ve formül çubuğunun yanındaki Fx simgesini tıklayın.

Excel İşlev Sihirbazı penceresi açılır. İçindeki DÜŞEYARA'yı bulmak gerekir. Açılır listeden "Alfabetik listeyi tamamla"yı seçin ve DÜŞEYARA yazmaya başlayın. Bulunan işlevi vurgulayın ve "Tamam" a basın.

Excel elektronik tablosu için DÜŞEYARA penceresi görünür.

İlk argümanı (ölçütü) belirtmek için, imleci ilk satıra getirin ve H3 hücresine tıklayın. Adresi satırda görünecektir. Bir aralık seçmek için imleci ikinci satıra getirin ve fare ile seçmeye başlayın. Pencere bir satıra daralacaktır. Bu, pencerenin tüm aralığı görmeyi engellememesi ve eylemleri gerçekleştirmeyi engellememesi için yapılır.

Seçme ve bırakma ile işiniz bittiğinde sol düğme fare, pencere normal durumuna dönecek ve aralığın adresi ikinci satırda görünecektir. Sol üst hücreden sağ alt hücreye kadar hesaplanır. Adresleri ":" operatörü ile ayrılır - ilk ve son arasındaki tüm adresler alınır.

İmleci üçüncü satıra getirin ve bir eşleşme bulunduğunda verilerin hangi sütundan alınacağını okuyun. Örneğimizde bu 3.

Son satırı boş bırakın. Varsayılan olarak değer 1'e eşit olacaktır, bakalım fonksiyonumuz hangi değeri gösteriyor. Tamam'ı tıklayın.

Sonuç cesaret kırıcı. "N / A", işlev için geçersiz veriler anlamına gelir. H3 hücresinde bir değer belirtmedik ve fonksiyon boş bir değer arıyor.

Ayın adını girelim ve değer değişecek.

Sadece gerçekle örtüşmemektedir, çünkü Ocak ayında piyasaya sürülen gerçek ürün sayısı 2000'dir.
Bu, Arama Koşulu bağımsız değişkeninin etkisidir. Bunu 0 olarak değiştirin. Bunu yapmak için işaretçiyi formülün bulunduğu hücrenin üzerine getirin ve tekrar Fx tuşuna basın. Açılan pencerede son satıra "0" girin.

Tamam'ı tıklayın. Gördüğünüz gibi sonuç değişti.

Yazımızın başından itibaren ikinci koşulu kontrol etmek için (fonksiyon formüller arasında arama yapmaz), fonksiyonun şartlarını değiştireceğiz. Aralığı artıralım ve hesaplanan değerlerle sütundan değeri çıkarmaya çalışalım. Değerleri ekran görüntüsünde gösterildiği gibi belirtin.

"Tamam" ı tıklayın. Gördüğünüz gibi, tablo% 85'lik bir değer içermesine rağmen, arama sonucu 0 çıktı.

Excel'deki DÜŞEYARA yalnızca sabit değerleri "anlar".

İki Excel tablosunun verilerini karşılaştırma

Excel'deki DÜŞEYARA, iki tablodaki verileri karşılaştırmak için kullanılabilir. Örneğin, iki çalıştayın çıktılarına ilişkin verileri içeren iki çalışma sayfamız olduğunu varsayalım. Her ikisi için de gerçek sürümle eşleşebiliriz. Sayfalar arasında geçiş yapmak için pencerenin altındaki kısayollarını kullanın.

İki sayfada, farklı verilerle aynı tablolara sahibiz.

Gördüğünüz gibi, çıkış planları aynı, ancak asıl olan farklı. Küçük miktarlardaki veriler için bile satır satır geçiş yapmak ve karşılaştırmak çok elverişsizdir. Üçüncü sayfada, üç sütunlu bir tablo oluşturacağız.

B2 hücresine DÜŞEYARA işlevine girelim. İlk argüman olarak, mevcut sayfada ayın olduğu hücreyi belirteceğiz ve "Shop1" sayfasından aralığı seçeceğiz. Kopyalama sırasında aralığın kaymasını önlemek için aralığı seçtikten sonra F4 tuşuna basın. Bu, bağlantıyı mutlak hale getirecektir.

Tüm sütunu doldurmak için formülü uzatın.

Benzer şekilde, bir sonraki sütuna formülü girin, yalnızca "Shop2" sayfasındaki aralığı seçin.

Kopyaladıktan sonra, iki sayfadan bir özet rapor alacaksınız.

Verilerin bir Excel tablosundan diğerine değiştirilmesi

Bu işlem de aynı şekilde gerçekleştirilir. Örneğimiz için, ayrı bir tablo oluşturamazsınız, ancak işlevi tablolardan herhangi birinin sütununa girmeniz yeterlidir. İlk örneği kullanarak gösterelim. İşaretçiyi son sütuna ayarlayın.

Ve G3 hücresine DÜŞEYARA işlevini koyun. Menzili tekrar bitişik sayfadan alıyoruz.

Sonuç olarak, ikinci tablonun sütunu birinciye kopyalanacaktır.

Göze çarpmayanla ilgili tüm bilgiler bu kadar, ama faydalı fonksiyon Aptallar için Excel'de DÜŞEYARA. Sorunları çözmenize yardımcı olacağını umuyoruz.

İyi günler!

kullanım örnekleri Excel formülleri DÜŞEYARA işleviyle, çalışma prensibini bulmanızı ve hatalardan kaçınmanızı sağlar:

fonksiyon alır parametreleri ayarla, ilk tablodaki kriteri kullanarak, değerlerin "yukarı çekileceği" ikinci aralığı ifade eder.
Ölçüt sütununun en üstteki hücresinden tüm değerlere bakar.
Belirtilen kriterlere uygun eşleşmeleri bulduktan sonra, belirtilen sayıda sütunu sağa doğru sayar ve istenen değerin bulunduğu hücrede biter, bu da formülün belirtildiği hücreye "çekilir".

DÜŞEYARA işlevini kullanma örneği

Verilen örnekte satılan malın maliyetini öğrenmek istiyorsunuz. Bunu hesaplamak için miktar ve fiyatların ürününü bulmalısınız (veri içeren sütunlar bitişik sütunlarda bulunur). Yanındaki boş bir sütuna iki hücrenin çarpımının formülünü yazarlar ve mal listesinin sonuna kadar uzatırlar.

Orijinal bilgiler farklı aralıklarda ve farklı bir sırada yer alabilir. İlk tablo satılan mal miktarını gösterir:

İkincisi fiyatlar içindir:

Her iki tablodaki mallar çakışırsa, Ctrl + C ve Ctrl + V tuş kombinasyonu kullanılarak, miktar yerine fiyat göstergeleri kolayca değiştirilebilir. Ancak masaların farklı bir konum sırası vardır.

Birçok ürünle ilgili bilgiler, bitişik göstergelere karşılık gelmiyor. Bu durumda, çarpma formülünü yazma ve tüm pozisyonlar için aşağı "çekme" olasılığı hariç tutulur.

İkinci tablodaki fiyatların birincisinden karşılık gelen miktar göstergelerine, yani A konumunun fiyatının A konumunun miktarına, B fiyatının B miktarına nasıl ikame edileceği.

DÜŞEYARA işlevini kullanırken, ikinci tablodaki fiyatlar, mal adlarına göre birinci tablodaki miktara "çekilir".

Prosedür:

İlk tabloda, fiyatların DÜŞEYARA formülü kullanılarak belirleneceği yeni bir sütun eklemeniz gerekir.

DÜŞEYARA işlevi, İşlev Sihirbazı aracılığıyla çağrılır veya manuel olarak yazılır.

Seçeneği Sihirbaz aracılığıyla çağırmak, formülün gösterileceği hücreyi etkinleştirmek ve formül çubuğunun başındaki f (x) düğmesine tıklamaktan ibarettir. Sihirbazın görünen iletişim kutusunda, önerilen listeden DÜŞEYARA'yı belirtmeniz gerekir.

Aptallar için Excel'deki DÜŞEYARA formülü, İşlev Sihirbazı iletişim kutusundaki alanların doğru doldurulmasını gerektirir:

İlk sütun "Arama değeri", formülün yazılacağı hücre için kriterleri belirlemenize olanak tanır. Bu örnekte, hücre "A" öğesini içerir.
Sonraki satır"Tablo". Bir veri aralığı girerken, istenen değerleri bulmanızı sağlayacaktır. Örneğin, fiyatların yer aldığı ikinci tablo kullanıldı. Fiyatlar miktara "yukarı çekildiğinden". Seçilen aralığın en soldaki (ilk soldaki) sütununun benzer arama kriterleri içermesi ihtiyacını hesaba katmak önemlidir. Örnekte bu, ürünün adının bulunduğu sütundur. Ardından tablo, gerekli verileri (fiyatları) içeren sütuna kadar sağda vurgulanır. Seçimi sağa doğru genişletebilirsiniz, ancak gerekli göstergelere sahip sütun benzersiz bir şekilde tanımlanacağı için bu hiçbir şeyi etkilemeyecektir. sonraki parametre... Seçilen tabloların bir kriter sütunuyla başlaması ve ilgilenilen veri sütununu yakalaması önemlidir.
"Sütun numarası" - gerekli verileri (fiyatları) içeren sütunların kriterlere (ürün adı) sahip sütundan ayrıldığı sayılar. Geri sayım, ölçüt sütununun kendisinden başlar. İkinci tabloda, her iki sütun yan yana yer alıyorsa, 2 sayısını belirtmelisiniz (birincisi kriterler, ikincisi fiyatlardır). 10 veya 20 sütun için kriterlere göre veri yerleştirmek mümkündür. Fark etmez, Excel doğru hesaplamaları yapacaktır.
Son sütun, arama seçeneklerinin gösterildiği "Aralık görünümü"dür: kriterlerin tam (0) veya yaklaşık (1) eşleşmeleri. Şimdi 0 (veya YANLIŞ) belirtmelisiniz.

Tamam veya Gir düğmesine tıklamak için kalır. Veriler doğru girilmişse ve her iki tabloda da bir kriter varsa, belirtilen formül yerine belirli bir gösterge görüntülenecektir. Formülü tablonun son satırına kadar uzatmak (veya kopyalamak) yeterlidir.

Fiyat-miktar çiftleri zaten aynı olduğundan, miktar ve fiyatın çarpımı belirlenerek, formülü tablonun sonuna kadar uzatarak daha fazla hesaplama yapılır.

Alternatif bir seçenek, DÜŞEYARA formülünü, parametreler arasına “;” işareti ekleyerek bir hücreye yazmaktır.

Küçük harfler kullanarak "vpr" adını yazmasına izin verilir, kayıt önemli değildir.

Özel Yapıştır'ı nasıl kullanırım?

DÜŞEYARA işlevini kullanmanın bir sonucu olarak, iki tablo bağlantılı kalır. Fiyat listesinde değişiklik yaptığınızda, depoya gelen malın maliyeti de değişecektir. Bu durumdan kaçınmak için "Özel Yapıştır" kullanmalısınız.

Belirtilen fiyatları içeren sütunu seçin.
Sağ tıklayın - "Kopyala".
Seçimi kaldırmadan, sağ tıklayın - "Özel Yapıştır".
Onay kutusunu "Değerler"e karşı ayarlayın. TAMAM.

Hücrelerde sadece değerler kalacak, formül iptal edilecektir.

DÜŞEYARA kullanarak iki tabloyu hızlı bir şekilde nasıl karşılaştırabilirim?

İşlev, büyük tablolardaki değerleri eşlemek için kullanılır. Örneğin, fiyat listesinde değişiklikler varsa. Eski fiyatları yenileriyle karşılaştırmak istiyorsunuz.

Prosedür:

Eski fiyat listesinde "Yeni fiyatlar" sütununu oluşturmanız gerekir.

İlk hücreyi seçin ve DÜŞEYARA formülünü seçin. Argümanları ayarlayın (yukarıya bakın). Örneğin:

Belirtilen formül, ürünün adını A2: A15 aralığından alma, A sütununu kullanarak "Yeni fiyat" içinde görüntüleme ihtiyacı hakkında bilgi verir. Ardından, yeni fiyat listesinin ikinci sütunundaki verileri kullanın (yeni fiyatlar) ve bunları C2 hücresine girin.

Verileri görüntülemenin bir yolunu seçmek, onları karşılaştırmanıza, sayı ve yüzdelerdeki farkı belirlemenize olanak tanır.

Excel'de bir dizi koşulla DÜŞEYARA formülü

Yukarıda, analiz örnekleri tek bir koşulla ele alındı - ürünün adı. Uygulamada, birkaç aralığı verilerle karşılaştırmak ve 2, 3 kritere göre değer seçmek gerekebilir. Aşağıda görsel bir genel bakış için bir tablo bulunmaktadır:

Oluklu mukavvanın JSC "Vostok" tan getirildiği fiyatı aramak için 2 koşul oluşturmalısınız: malzemenin adına ve tedarikçilere göre.

Ana zorluk, bir tedarikçiden birkaç ürün adının varlığında yatmaktadır.

Adım adım talimatlar bir dizi eylemi içerir:

Tabloya en soldaki sütunu ekleme (önemli!), "Tedarikçiler" ve "Malzemeler"i Birleştirme.

Gerekli kriterleri analoji ile birleştirmek:

İmleci doğru yere yerleştirmek ve formül için argümanları ayarlamak:

Excel doğru fiyatı arar.

Formülün ayrıntılı değerlendirmesi:

Ne aranmalı.
Nereye bakmalı.
Hangi verileri alacak.

DÜŞEYARA formülleri ve açılır listeler

Belirli verilerin bir açılır liste şeklinde girilmesine izin verin. Bu örnekte, bunlar Malzemelerdir. Bir ad seçtiğinizde fiyatın görüntülenmesi için işlevi yapılandırmak önemlidir.

Bir açılır liste oluşturmak için bir dizi eylem gerçekleştirmelisiniz:

İmleci, listeyi yerleştirmeyi planladığınız E8 hücresine yerleştirin.

"Veri" sekmesini açın. Veri menüsünü kontrol edin.

Veri türünü seçin - "Listeler". Kaynaklar - malzemelerin adlarını içeren bir aralık.

Tamam'ı tıkladığınızda, bir açılır liste oluşturulacaktır.

Malzeme seçerken fiyat sütununda karşılık gelen değerleri görüntülemeye izin veren bir işlev belirlemeye devam ediyor. İmleci E9 hücresine (fiyatın görüntüleneceği yere) yerleştirin.

"İşlev Sihirbazı"nı açın ve DÜŞEYARA'yı seçin.

İlk argüman "Arama değeri"dir - açılır listeleri olan hücreler. Tablo - malzeme ve fiyatların adını içeren bir aralık. Sütun - 2. İşlev aşağıdaki biçimde görüntülenecektir:

Geriye ENTER'a basmak ve sonucun keyfini çıkarmak kalıyor.

Malzemeyi değiştirdiğinizde fiyat değişir:

DÜŞEYARA işleviyle Excel'de bir açılır liste bu şekilde çalışır. Her şey birkaç saniye içinde otomatik modda yapılır.

DÜŞEYARA formülüyle çalışmanın özellikleri

Excel'de DÜŞEYARA işlevini kullanmadan önce, özelliklerini tanımalısınız:

Bir hücrede formül belirleyip diğerlerine kopyalayarak çok sayıda hücre için seçeneği kullanırken, referansların göreliliğini ve mutlaklığını kontrol etmek önemlidir. DÜŞEYARA'da, ölçütlerin (ilk alanlar), her hücre için kendi ölçütlerinin varlığıyla belirlenen göreli başvuruları ($ olmadan) olmalıdır. Aralıkların mutlak referansları olmalıdır (aralık adresleri $ ile gösterilir). Aksi takdirde, formülleri kopyalarken, aralık aşağı "kayar" ve aranacak hiçbir yer olmayacağından aramada birçok gösterge görüntülenmez.
Fonksiyon Sihirbazı kullanılırken üçüncü "Sütun numarası" alanında belirtilen sütun numaraları, ölçüt sütunundan sayılmalıdır.
Verilerin arandığı tablolarda herhangi bir kriter yoksa toplamların (toplam, ortalama vb.) hesaplanmasında zorluklara neden olan # N / A hatası ortaya çıkar. Sorunu çözmek için SUMIF işlevini (DÜŞEYARA yerine) veya ESLIOSHIBKA'yı (DÜŞEYARA'dan önce koy) kullanabilirsiniz.
Ölçüt (kodlar, makale numaraları) yerine sayısal değerler kullanıldığında, DÜŞEYARA formülünün hücre biçimlerine duyarlılığı artırılmıştır. Bir tabloda sayısal biçimde ve diğerinde metin biçiminde ölçütler varsa, göstergeler tamamen eşleşirse, # N / A hatası görüntülenecektir. Alan formatının kriterlere uyup uymadığını kontrol etmek ve onları özdeş hale getirmek veya SUMIF işlevini kullanmak yeterlidir (biçim bunun için önemli değildir).
"Rastgele" farklılıkların olasılığını azaltmak için uzun kriterler kullanılması önerilmez. Örneğin, kelimeler arasında fazladan boşluk olması veya bir yanlış harf olması, aynı kriter değerlerinin karşılaştırılabilir olmamasına yol açacaktır. Öğe numaraları veya ürün barkodları iyidir, ancak ölçüt için çok kelimeli adlar önerilmez.
Gerekli verileri içeren tablodaki DÜŞEYARA işlevleri, üstten ilk göstergeyi verir. Verilerin "çekildiği" ikinci tabloda, aynı kriterlere sahip bir dizi hücre varsa, üstten ilk gösterge seçilen DÜŞEYARA aralığında yakalanır. Bunun dikkate alınması önemlidir. Örneğin, miktarı başka bir aralıktan mal fiyatına sıkıştırmak gerekirse ve orada bu ürün birkaç sütunda tekrarlanırsa, üstten ilk göstergeler (miktar) fiyata gelir, diğerleri kalır. görmezden gelindi.
Son parametrenin 0 (sıfır) şeklinde bulunması zorunludur. Aksi takdirde, formül çarpık çalışır.
DÜŞEYARA işlevini kullandıktan sonra, yalnızca elde edilen sonuçları bırakarak formülü hemen silmeniz önerilir. Prosedür, "kopyala" düğmesini kullanarak elde edilen sonuçlarla aralığı vurgulamayı ve kullanarak değerleri bu yere ayarlamayı gerektirir. özel uç... Tabloları farklı Excel çalışma kitaplarına yerleştirirken, kırmak daha uygun olacaktır. dış ilişkiler(verileri bırakarak) Veri → Bağlantıları Düzenle altındaki aracı kullanarak.

Dış bağlantıları kırma işlevini çağırdıktan sonra, "Bağlantıyı kes" ve "Kapat" düğmesini etkinleştirmek için kaldığı bir iletişim kutusu görünecektir.

Bu, tüm harici bağlantıları bir kerede kaldıracaktır.

Alternatif bir DÜŞEYARA seçeneği YATAYARA seçeneğidir. Fark, veri listesinin yatay görünümüdür.

kesinlikle birçok aktif kullanıcılar elektronik tablo düzenleyicisi Excel, değerleri bir tablodan diğerine değiştirmenin gerekli olduğu durumlarla periyodik olarak uğraşmak zorunda kaldı. Sadece hayal edin, belirli bir ürün deponuza girdi. Elimizde iki dosya var: biri alınan malların adlarının listesi, ikincisi - bu ürünün fiyat listesi. Fiyat listesini açtıktan sonra, içinde daha fazla pozisyon olduğunu ve isimlerin bulunduğu dosyadakiyle aynı sırada yer almadıklarını görüyoruz. Her iki dosyayı da kontrol etme ve fiyatları bir belgeden diğerine manuel olarak aktarma fikri neredeyse hiç kimseden hoşlanmayacaktır. Tabii ki 5-10 pozisyondan bahsettiğimizde mekanik veri girişi oldukça mümkün, peki ya madde sayısı 1000'i geçerse? Bu durumda, Excel ve sihirli DÜŞEYARA işlevi (veya programın İngilizce sürümünden bahsediyorsak vlookup) monoton çalışma ile başa çıkmamıza yardımcı olacaktır.

Dolayısıyla verileri bir tablodan diğerine dönüştürme çalışmalarımızın başında DÜŞEYARA işlevine küçük bir genel bakış yapmak uygun olacaktır. Muhtemelen zaten anladığınız gibi, vlookup bir tablodan diğerine veri aktarmanıza, böylece ihtiyacımız olan hücreleri otomatik olarak doldurmanıza izin verir. DÜŞEYARA işlevinin doğru çalışması için tablonuzun başlıklarında birleştirilmiş hücrelerin bulunmasına dikkat edin. Varsa, onları parçalamanız gerekecek.

Diyelim ki "Sipariş tablosu"nu "Fiyat Listesi"ndeki verilerle doldurmamız gerekiyor.

Böylece, mevcut malların fiyatlarını isimlerinin bulunduğu bir tabloya aktarma ve her bir ürünün toplam maliyetini hesaplama görevi ile karşı karşıyayız. Bunu yapmak için aşağıdaki algoritmayı gerçekleştirmeliyiz:

Önce getir Excel elektronik tablo ihtiyacınız olan formda. Hazırlanan veri matrisine "Fiyat" ve "Maliyet" adlı iki sütun ekleyin. Yeni oluşturulan sütun aralığındaki hücreler için para birimi biçimini seçin.
Şimdi "Fiyat" bloğundaki ilk hücreyi etkinleştirin ve "İşlev Sihirbazı"nı çağırın. Bu, formül çubuğunun önünde bulunan "fx" düğmesine tıklayarak veya "Shift + F3" tuş kombinasyonunu basılı tutarak yapılabilir. Açılan iletişim kutusunda "Referanslar ve Diziler" kategorisini bulun. Burada DÜŞEYARA işlevinden başka bir şeyle ilgilenmiyoruz. Seçin ve "Tamam" ı tıklayın. Bu arada, DÜŞEYARA işlevinin, açılır listesi Referanslar ve Diziler kategorisini de içeren Formüller sekmesinden çağrılabileceği söylenmelidir.
DÜŞEYARA'yı etkinleştirdikten sonra, önünüzde seçtiğiniz işlevin argümanlarının listesini içeren bir pencere açılacaktır. "Arama değeri" alanında, alınan malların bir listesi ve miktarları ile tablonun ilk sütununda yer alan veri aralığını girmeniz gerekecektir. Yani, Excel'e ikinci tabloda tam olarak ne bulması gerektiğini söylemeniz ve ilkine aktarmanız gerekir.
İlk argüman belirtildikten sonra ikinciye geçebilirsiniz. Bizim durumumuzda, fiyat listesi içeren bir tablo ikinci argüman olarak hareket eder. Fare imlecini argüman alanına getirin ve fiyat listesinin bulunduğu sayfaya gidin. Ticari ürünlerin adları ve fiyatları ile sütunlar alanında bulunan hücrelere sahip bir aralığı manuel olarak seçin. Excel'e DÜŞEYARA işleviyle hangi değerlerin eşleneceğini söyleyin.
Excel'in kafanız karışmaması ve ihtiyacınız olan verilere başvurmaması için kendisine verilen bağlantıyı düzeltmeniz önemlidir. Bunu yapmak için "Tablo" alanında gerekli değerleri seçin ve F4 tuşuna basın. Her şey doğru yapılmışsa, ekranda $ işareti görünmelidir.
Şimdi "Sayfa numarası" argüman alanına gidiyoruz ve ona "2" değerini veriyoruz. Bu blok, çalışma sayfamıza gönderilmesi gereken tüm verileri içerir ve bu nedenle "Aralık görünümüne" yanlış bir değer atamak önemlidir (konumu "YANLIŞ" olarak ayarlayın). DÜŞEYARA işlevinin yalnızca kesin değerlerle çalışması ve bunları yuvarlamaması için bu gereklidir.

Artık gerekli tüm işlemler tamamlandığında, "Tamam" düğmesine basarak bunları onaylamamız gerekiyor. İlk hücredeki veriler değişir değişmez, DÜŞEYARA işlevini tüm Excel belgesine uygulamamız gerekecektir. Bunu yapmak için DÜŞEYARA'yı tüm "Fiyat" sütunu üzerinden çarpmanız yeterlidir. Bu, değiştirilen değerin bulunduğu hücrenin sağ alt köşesini sütunun en altına sürükleyerek yapılabilir. Her şey yolunda giderse ve veriler gerektiği gibi değiştiyse, mallarımızın toplam maliyetini hesaplamaya başlayabiliriz. Bu eylemi gerçekleştirmek için iki sütunun - "Miktarlar" ve "Fiyatlar" ürününü bulmamız gerekiyor. Excel tüm matematiksel formülleri içerdiğinden, hesaplama zaten bilinen "fx" simgesi kullanılarak "Formül Çubuğu"na sağlanabilir.

önemli bir nokta

Görünüşe göre her şey hazır ve DÜŞEYARA görevimizle başa çıktı, ancak durum böyle değildi. Gerçek şu ki, DÜŞEYARA işlevi “Fiyat” sütununda hala etkindir ve bu gerçek, ikincisinin formül çubuğunda görüntülenmesiyle kanıtlanmıştır. Yani, her iki tablomuz da birbirine bağlı kalır. Böyle bir tandem, fiyat listesi ile tablodaki veriler değiştiğinde, mal listesi ile çalışma dosyamızda yer alan bilgilerin de değişeceği gerçeğine yol açabilir.

Bu durumdan en iyi şekilde iki tabloyu bölerek kaçınılır. Bunu yapmak için, "Fiyat" sütununun aralığındaki hücreleri seçip sağ tıklamamız gerekiyor. Açılan pencerede "Kopyala" seçeneğini seçin ve etkinleştirin. Bundan sonra, seçili hücre alanından seçimi kaldırmadan, tekrar farenin sağ tuşuna basın ve "Özel Yapıştır" seçeneğini seçin.

Bu seçeneği etkinleştirmek, ekranınızda "Değer" kategorisinin yanındaki kutuyu işaretlemeniz gereken bir iletişim kutusu açacaktır. Yaptığınız işlemi "Tamam" butonuna tıklayarak onaylayın.

Formül çubuğumuza geri dönün ve Fiyat sütununda DÜŞEYARA işlevinin etkin olup olmadığını kontrol edin. Formül yerine yalnızca sayısal değerler görürseniz, her şey yolunda gitti ve DÜŞEYARA işlevi devre dışı bırakıldı. Yani ikisi arasındaki bağlantı Excel dosyaları bozuk ve fiyatla birlikte tablodan ekli verilerin plansız değişiklik veya silinme tehdidi yoktur. Artık bir elektronik tablo belgesini güvenle kullanabilir ve "Fiyat Listesi" kapatılırsa veya başka bir konuma taşınırsa ne olacağı konusunda endişelenmenize gerek kalmaz.

Excel'de iki tablo nasıl karşılaştırılır?

DÜŞEYARA işleviyle birden çok Farklı anlamlarörneğin, mevcut bir ürünün fiyatlarının nasıl değiştiğini karşılaştırmak için. Bunu yapmak için boş bir sütuna DÜŞEYARA yazmanız ve işlevi başka bir tablodaki değiştirilen değerlere başvurmanız gerekir. "Yeni Fiyat" sütununun "Fiyat" sütununun hemen ardından yer alması en iyisidir. Bu çözüm, fiyat değişikliklerini karşılaştırma için daha görsel hale getirmenize olanak tanır.

Birden fazla koşulla çalışabilme

DÜŞEYARA işlevinin bir başka şüphesiz avantajı, ürününüze özgü çeşitli parametrelerle çalışma yeteneğidir. Bir ürünü iki veya daha fazla özelliğe göre bulmak için şunları yapmalısınız:

İki (veya gerekirse daha fazla) arama terimi oluşturun.
İşlevin çalışması sırasında, ürünün aranacağı diğer tüm sütunların ekleneceği yeni bir sütun ekleyin.
Ortaya çıkan sütunda, yukarıdaki algoritmaya göre, DÜŞEYARA işlevinin zaten bilinen formülünü giriyoruz.

Sonuç olarak, Excel'in DÜŞEYARA gibi bir işlevi desteklemesinin tablo bilgileriyle çalışmayı çok daha kolay hale getirdiği söylenmelidir. DÜŞEYARA ile kullanmaktan çekinmeyin büyük miktar veri, çünkü nasıl biçimlendirildikleri önemli değil, işlevin prensibi her zaman aynıdır. Tek yapmanız gereken argümanlarını doğru bir şekilde tanımlamak.

işlevi kullanma DÜŞEYARA Excel'de çalışırken, gerekli bilgileri elektronik tablolardan çıkarabilirsiniz. Excel bu amaçlar için çeşitli işlevler sunar, ancak DÜŞEYARA aralarında en yaygın olanı. Bu derste fonksiyonla tanışacağız DÜŞEYARA ve ayrıca yeteneklerini basit bir örnekle düşünün.

İşlev DÜŞEYARA(dikey tarama) incelenmekte olan aralığın en soldaki sütununda bir değer arar ve ardından bulunan satır ile belirtilen sütunun kesişim noktasındaki hücrenin sonucunu döndürür.

örnek 1

Örneğin, aşağıdaki resim 10 soyadın bir listesini göstermektedir, her soyadının kendi numarası vardır. Verilen numaradan soyadının çıkarılması gerekmektedir.

işlevi kullanma DÜŞEYARA bunu yapmak oldukça basittir:

Formülden, fonksiyonun ilk argümanının DÜŞEYARA istenen sayıyı belirttiğimiz C1 hücresidir. İkincisi, nereye bakılacağını gösteren A1: B10 aralığıdır. Ve son argüman, sonucun döndürüleceği sütun numarasıdır. Örneğimizde, bu ikinci sütundur. Tıklayarak Girmek, istenen sonucu alacağız:

Örnek 2

Başka bir örnek alalım. Aşağıdaki resim öncekiyle aynı 10 ismi gösteriyor, sadece sayılar geçişli olarak geliyor.

Var olmayan bir sayının (örneğin, 007) soyadını bulmaya çalışırsak, formül hata vermek yerine sonucu bize güvenle döndürür. Bu nasıl olabilir?

Mesele şu ki, fonksiyon DÜŞEYARA ayrıca, sözde aralık taramasını ayarlamanıza izin veren dördüncü bir argümana sahiptir. İki anlamı olabilir: DOĞRU ve YANLIŞ. Ayrıca, argüman atlanırsa, bu gerçekle eşdeğerdir.

Dördüncü argüman DOĞRU olduğunda, işlev önce tam bir eşleşme arar ve böyle bir eşleşme yoksa, belirtilenden daha az olan en yakın olanı arar. Bu yüzden fonksiyon DÜŞEYARA“Panchenko” soyadını geri verdi. “008” vermiş olsaydık, formül “Panchenko” da verecekti.

Fonksiyonun dördüncü argümanının olması durumunda DÜŞEYARA YANLIŞ mantıksal değerine sahipse, işlev tam bir eşleşme arar. Örneğin, aşağıdaki şekilde, tam eşleşme bulunamadığından formül bir hata döndürür.

Eğer fonksiyonun dördüncü argümanı DÜŞEYARA DOĞRU ise veya atlanırsa, en soldaki sütun artan düzende sıralanmalıdır. Bu yapılmazsa, işlev DÜŞEYARA yanlış sonuç döndürebilir.

Dikey değil yatay tablolar oluşturmak isteyenler için Excel'de bir analog var. DÜŞEYARA ancak yatay arama için.

Excel'de Yatay DÜŞEYARA

Microsoft Excel'in bir işlevi var GPR(yatay görünüm) çok benzer DÜŞEYARA, tek fark, aralığın dikey olarak değil yatay olarak görüntülenmesidir. GPR arıyor set değeri v üst çizgi range ve bulunan sütun ile belirtilen satırın kesişim noktasındaki hücrenin sonucunu döndürür.

Yukarıdaki örneği yatay biçimde gösterirseniz, formül şöyle görünecektir:

Gördüğünüz gibi, her şey oldukça basit!

Bu dersimizi sonlandırıyor. Bugün muhtemelen en popüler Microsoft Excel aracıyla tanıştık - DÜŞEYARA işlevi ve birkaç için yeteneklerini analiz etti basit örnekler... Umarım bu eğitim yardımcı olmuştur. Excel öğrenmede en iyi ve başarı.

DÜŞEYARA () işlevi , DÜŞEYARA'nın İngilizce versiyonu (), bir tablonun ilk (en soldaki) sütununda bir değer arar ve tablodaki aynı satırdan ancak farklı bir sütundan bir değer döndürür.

DÜŞEYARA () işlevi, EXCEL'de en çok kullanılanlardan biridir, bu yüzden ayrıntılı olarak inceleyelim.

Bu makalede, standart olmayan bir yaklaşım benimsenmiştir: vurgu, işlevin kendisine değil, onun yardımıyla çözülebilecek görevlere odaklanmaktadır.

İşlev sözdizimi

DÜŞEYARA (arama_değeri; tablo; column_number; range_view)

aranan_değer veri sütununda bulmaya çalıştığınız değerdir. aranan_değer bir sayı veya metin olabilir, ancak çoğu zaman aranan bir sayıdır. Aradığınız değer, belirtilen hücre aralığının ilk (en soldaki) sütununda olmalıdır. tablo .

Tablo - bir hücre aralığına bir başvuru. Tablonun sol sütunu aranır aranan_değer, ve sağda bulunan sütunlardan ilgili sonuç görüntülenir (prensipte, değeri sol sütundan çıktı alabilirsiniz (bu durumda aranan_değer)). Genellikle sol sütun denir anahtar... İlk sütun içermiyorsa aranan_değer , # Yok

sütun_numarası- sütun numarası tablolar sonucun çıktısını almak için. En soldaki sütun (anahtar) 1 numaraya sahiptir (aranır).

Parametre interval_view 2 değer alabilir: DOĞRU (değer, kritere en yakın veya onunla örtüşür) ve YANLIŞ (kriterle tam olarak örtüşen değer aranır). TRUE, içindeki ilk sütunun tablo alfabetik veya artan şekilde sıralanır. Bu yöntem, aksi belirtilmedikçe varsayılan işlevde kullanılır.

Aşağıdaki makale, DÜŞEYARA () işlevi kullanılarak çözülebilecek popüler sorunları tartışmaktadır.

Görev 1. mal rehberi

Orijinal tablonun verilmesine izin verin (bkz. örnek dosya sayfası referansı).

Görev, isteneni seçmektir. satıcı koduürün, geri çek İsim ve Fiyat.

Not... Bu, DÜŞEYARA () kullanımına ilişkin "klasik" bir sorundur (makaleye bakın).

Para çekme için İsimler formülü kullan = DÜŞEYARA ($ E9, $ A $ 13: $ C $ 19,2, YANLIŞ) veya = DÜŞEYARA ($ E9; A $ 13: $ C $ 19; 2; DOĞRU) veya = DÜŞEYARA ($ E9; $ A $ 13: $ C $ 19; 2) (yani parametrenin değeri Aralık_görünümü YANLIŞ veya DOĞRU olarak ayarlanabilir veya tamamen atlanabilir). Parametre değeri sütun_numarası= 2 olarak ayarlamanız gerekir, çünkü sütun numarası İsim 2'ye eşittir (Anahtar sütun her zaman 1 numaradır).

Para çekme için Fiyat:% s benzer bir formül kullanın = DÜŞEYARA ($ E9; $ A $ 13: $ C $ 19; 3; YANLIŞ)(parametre değeri sütun_numarası= 3) olarak ayarlanmalıdır.

Bizim durumumuzdaki anahtar sütun sayıları içerir ve istenen değeri içermesi garanti edilmelidir (sorun koşulu). İlk sütun aradığınız öğeyi içermiyorsa , daha sonra fonksiyon hata değerini döndürür # Yok Bu, örneğin bir makale numarası girerken bir yazım hatası ile olabilir. İstediğiniz makaleye girerken hata yapmamak için kullanabilirsiniz (bkz. hücre E9 ).

Görevimizde anahtar sütunun kopya içermemesi gerektiği açıktır (bu, ürünü benzersiz olarak tanımlayan makalenin anlamıdır). Aksi takdirde, en üstteki değer görüntülenecektir.

Bu tür sorunları çözerken, anahtar sütun önceden daha iyidir (bu aynı zamanda Açılır liste daha açık bir şekilde). Ayrıca, sıralanmamış bir liste olması durumunda, DÜŞEYARA () parametresiyle Aralık_görünümü TRUE (veya atlanmış) çalışmayacaktır.

Ayrıca INDEX(), SEARCH() ve LOOKUP() fonksiyonları kullanılarak alternatif formüller ele alınır (aynı sonucu elde ederiz). Anahtar sütun (SKU'ları içeren sütun) tablonun en solunda değilse, DÜŞEYARA () işlevi geçerli değildir. Bu durumda alternatif formüller kullanmanız gerekir. Bir dizi işlev INDEX (), SEARCH () "doğru DÜŞEYARA" olarak adlandırılan işlevi oluşturur: = İNDEKS (B13: B19; ARAMA ($ E $ 9; $ A $ 13: $ A $ 19,0); 1)

V örnek dosya sayfası referansı formüllerin metin değerleri içeren anahtar sütunlar için geçerli olduğu gösterilmiştir, çünkü makale genellikle metinsel bir değerdir. Ayrıca, sıralanmamış bir anahtar sütun için sorun çözüldü.

Not... DÜŞEYARA () parametresini asla kullanmayın Aralık_görünümü Anahtar sütun artan düzende sıralanmamışsa DOĞRU (veya atlanmıştır). formülün sonucu tahmin edilemez (DÜŞEYARA () işlevi istenen değerden daha büyük bir değer bulursa, bir satır üzerinde bulunan değeri görüntüler).

Görev 2. En yakın sayıyı bulma

Diyelim ki fiyatı istenen ürüne eşit veya ona en yakın olan bir ürün bulmak istiyorsunuz.

Bu sorunu çözmek için DÜŞEYARA () işlevini kullanmak için birkaç koşulu yerine getirmeniz gerekir:

Aranacak anahtar sütun, tablonun en soldaki sütunu olmalıdır;
Anahtar sütunu artan düzende sıralanmalıdır;
Parametre değeri Aralık_görünümü DOĞRU olarak ayarlanmalı veya tamamen atlanmalıdır.

Para çekme için Öğe adı formülü kullan = DÜŞEYARA ($ A7; $ A $ 11: $ B $ 17; 2; DOĞRU)

Bulunan fiyatı görüntülemek için (verilen fiyatla mutlaka örtüşmesi gerekmez) aşağıdaki formülü kullanın: = DÜŞEYARA ($ A7, $ A $ 11: $ B $ 17,1, DOĞRU)

Yukarıdaki resimden de görebileceğiniz gibi, DÜŞEYARA () bulundu en yüksek fiyat verilenden küçük veya ona eşit olan (bkz. örnek dosya sayfası "En yakın numarayı bul"). Bu, işlevin arama biçiminden kaynaklanmaktadır: DÜŞEYARA () işlevi, istenen değerden daha büyük bir değer bulursa, bir satır üzerinde bulunan değeri görüntüler. Sonuç olarak, istenen değer anahtar sütunundaki minimum değerden küçükse, işlev bir hata döndürür. # Yok

Bulunan değer en yakın değer olmayabilir. Örneğin, 199 için en yakın fiyatı bulmaya çalışırsanız, işlev 150 döndürür (en yakın fiyat 200 olmasına rağmen). Bu yine, fonksiyonun verilenden küçük veya ona eşit en büyük sayıyı bulması gerçeğinin bir sonucudur.

İstenen değere gerçekten en yakın olanı bulmanız gerekiyorsa, DÜŞEYARA () burada yardımcı olmaz. Bu tür bir problem bölümde çözülmüştür. Orada, sıralanmamış bir anahtar sütunla en yakını bulma sorununa da bir çözüm bulabilirsiniz.

Not... Kolaylık sağlamak için, bulunan çözümü içeren tablo satırı vurgulanmıştır. Bu formül kullanılarak yapılabilir = ARAMA ($ A $ 7; $ A $ 11: $ A $ 17; 1) = STRING () - STRING ($ A $ 10).

Not: Anahtar sütunu istenen değerle eşleşen bir değere sahipse, parametreli işlev Aralık_görünümü= YANLIŞ, ilk bulunan değeri istenen değere eşit olarak döndürür ve parametre = TRUE - sonuncusu ile (aşağıdaki resme bakın).

Aranan sütun en soldaki değilse, DÜŞEYARA () yardımcı olmaz. Bu durumda ARA () + İNDEKS () veya GÖRÜNTÜLE () işlevlerini kullanmanız gerekir.