Aynı anda birkaç kritere göre gerçekleştirilir. DÜŞEYARA işleviyle gelişmiş örnekler: birden çok kriter kullanarak arama yapın. Vreq ve arama konumları işlevleri

1. Derecelendirmeyi belirteceğimiz ek sütuna RANK işlevini ekleyin (=RANK hücresine yazın ve listeden önerilen EXCEL işlevini seçin, formül çubuğunda fx'e tıklayın)

2. Açılan penceredeki argümanları doldurun: “Sayı” - tablomuzdaki ilk değeri formülün bulunduğu satırda belirtin.

3. “Bağlantı” - tüm veri dizisini belirtir; tüm sayıları içeren aralık (satış değerleri).

4. Gelecekte sürüklerken aralığın adresinin "hareket etmemesi" için bu aralığın sınırlarını sabitleyin (klavyede F4 tuşuna basın) ve Tamam'a basın.

5. Formülü "derecelendirme" sütununun tüm hücrelerine kadar genişletiyoruz.

Bu işlevi kullanırken derecelendirme otomatik olarak hesaplanır ve herhangi bir değeri değiştirirseniz derecelendirme otomatik olarak yeniden hesaplanır.

Materyali beğendiyseniz ve hatta yararlı bulduysanız, aşağıdaki düğmeyi kullanarak belirli bir miktarı aktararak yazara teşekkür edebilirsiniz:
(Kartla transfer etmek için VISA'ya ve ardından “transfer”e tıklayın)

Bir ürünün belirli bir bölgedeki satışlarının toplamı gibi birden fazla koşula sahip değerleri toplamak istediğinizi varsayalım. Bu, bir formülde ETOPLA işlevinin iyi bir kullanımıdır.

İki koşulumuzun olduğu bu örneğe dikkat edin: Gerekli Satış Tutarı et(C sütunundan) Güney alanlar (a sütunundan).

İşte bunu başarmak için kullanılabilecek bir formül:

= ÇOKETOPLA (D2: D11; a2: A11; "Güney"; C2: C11; "et")

Sonuç 14.719 değeridir.

Formülün her bir bölümünün daha ayrıntılı bir temsiline bakalım.

SUMIFS bir aritmetik formüldür. Bu durumda D sütununda yer alan sayıları hesaplar. İlk adım, sayıların konumunu belirlemektir.

=ETOPLA(D2:D11,

Başka bir deyişle, formülün, belirli koşulları karşılamaları durumunda o sütundaki sayıları toplamasını istiyorsunuz. Bu hücre aralığı ilk argüman bu formülde işlevin girdi olarak kullandığı ilk veri öğesi.

Daha sonra iki koşulu karşılayan verileri bulmanız ve ilk koşulu girmeniz, işlev için verilerin konumunu (a2: A11) ve koşulun "Güney" olduğunu belirtmeniz gerekir. Bireysel argümanlar arasındaki virgüllere dikkat edin.

=ETOPLA(D2:D11,A2:A11,"Güney";

"Güney" kelimesinin etrafındaki tırnak işaretleri bunun metin verisi olduğunu belirtir.

Son olarak, ikinci koşula ilişkin bağımsız değişkenleri girersiniz - Excel'in bunları eşleştirebilmesi için "Et" sözcüğünü ve sözcüğün kendisini (tırnak içine alınmış) içeren hücre aralığı (C2:C11). Kapanış parantezini kapatarak formülü tamamlayın ) ve ardından Enter tuşuna basın. Sonuç yine 14.719 oldu.

=ETOPLA(D2:D11,A2:A11,"Güney",C2:C11,"Et")

Excel'de ÇOKETOPLA fonksiyonunu yazdığınızda eğer bu argümanları hatırlamıyorsanız Yardım hazırdır. Formülü girdikten sonra = ETOPLA ( Otomatik tamamlama formülleri formülün altında görünecek ve bağımsız değişkenlerin listesi gereken sırayla listelenecektir.

Örneğimizdeki formül otomatik tamamlama ve bağımsız değişken listesi görseline bir göz atın Toplam aralığı toplanması gereken sayıların sütunu olan D2:D11'dir. kriter_aralığı1- a2. A11 - içeren veri sütunu durum1"Güney".

Siz formülü girerken, kalan bağımsız değişkenler (burada gösterilmemiştir) Formül Otomatik Tamamlama'da görünecektir; aralık_koşulları2 içeren veri sütunu olan C2:C11 aralığıdır. durum2- "Et".

Eğer tıklarsan ETOPLALAR Bir formülü otomatik olarak tamamladığınızda daha fazla bilgi alabileceğiniz bir makale açılır.

Pratik yapmaya çalışın

ÇOKETOPLA işleviyle denemeler yapmak istiyorsanız burada bazı örnek veriler ve işlevi kullanan bir formül verilmiştir.

Bu Web için Excel çalışma kitabında, örnek veriler ve formüllerle burada çalışabilirsiniz. Anında sonuçların değiştiğini görmek için değerleri ve formülleri değiştirin veya kendinizinkini ekleyin.

Aşağıdaki tablodaki tüm hücreleri kopyalayın ve yeni bir Excel sayfasının A1 hücresine yapıştırın. Formüllerin daha iyi görünmesini sağlamak için sütunların genişliğini ayarlayabilirsiniz.

Bölge	Satış elemanı

Batı
Oryantal	Pesotsky
Kuzey	Pesotsky	Günlük
	Marinova


Oryantal	Pesotsky
Kuzey

Fonksiyon eğitimimizin ikinci bölümünde DÜŞEYARA(DÜŞEYARA) Excel'de tüm gücünüzü yönlendirmenize yardımcı olacak birkaç örneğe bakacağız DÜŞEYARA En iddialı Excel problemlerini çözmek için. Örnekler, bu fonksiyonun nasıl çalıştığına dair temel bilgiye zaten sahip olduğunuzu varsaymaktadır. Değilse, bu eğitimin söz dizimini ve temel kullanımı açıklayan ilk bölümüyle başlamak ilginizi çekebilir. DÜŞEYARA. Peki, başlayalım.

Birden çok ölçüt kullanarak Excel'de arama yapın

İşlev DÜŞEYARA Excel, bir veritabanında belirli bir değeri aramak için gerçekten güçlü bir araçtır. Ancak önemli bir sınırlama vardır; sözdizimi yalnızca tek bir değeri aramanıza izin verir. Birkaç koşulu kullanarak arama yapmanız gerekirse ne olur? Çözümü aşağıda bulacaksınız.

Örnek 1: 2 farklı kriter kullanarak arama yapın

Diyelim ki bir sipariş listemiz var ve bulmak istiyoruz Mal miktarı(Miktar), iki kritere göre – Müşteri Adı(Müşteri) ve Ürünün adı(Ürün). Aşağıdaki tablodan da görülebileceği gibi, alıcıların her birinin birkaç tür mal sipariş etmesi nedeniyle mesele daha da karmaşık hale geliyor:

Düzenli fonksiyon DÜŞEYARA verilen arama değeriyle eşleşen bulunan ilk değeri döndüreceğinden bu senaryoda çalışmaz. Örneğin bir ürünün miktarını öğrenmek istiyorsanız Tatlılar, alıcı tarafından sipariş edildi Jeremy Tepesi, aşağıdaki formülü yazın:

DÜŞEYARA(B1,$A$5:$C$14,3,YANLIŞ)
=DÜŞEYARA(B1,$A$5:$C$14,3,YANLIŞ)

– bu formül sonucu döndürecektir 15 ürüne karşılık gelen Elmalar, çünkü bu ilk eşleşen değerdir.

Basit bir geçici çözüm var; her şeyin birleştirileceği ek bir sütun oluşturun gerekli kriterler. Örneğimizde bunlar sütunlardır Müşteri Adı(Müşteri) ve Ürünün adı(Ürün). İşlevin soldaki sütun olması nedeniyle, birleştirilen sütunun her zaman arama aralığında en soldaki sütun olması gerektiğini unutmayın. DÜŞEYARA bir değer ararken bakar.

Böylece, tabloya bir yardımcı sütun eklersiniz ve bunun gibi bir formülü tüm hücrelerine kopyalarsınız: =B2&C2. Dizenin daha okunabilir olmasını istiyorsanız birleştirilmiş değerleri boşlukla ayırabilirsiniz: =B2&” “&C2. Bundan sonra aşağıdaki formülü kullanabilirsiniz:

DÜŞEYARA("Jeremy Hill Tatlıları",$A$7:$D$18,4,YANLIŞ)
=DÜŞEYARA("Jeremy Hill Tatlıları";$A$7:$D$18,4;YANLIŞ)

DÜŞEYARA(B1,$A$7:$D$18,4,YANLIŞ)
=DÜŞEYARA(B1,$A$7:$D$18,4,YANLIŞ)

Hücre nerede B1 argümanın birleştirilmiş değerini içerir aranan_değer(arama_değeri) ve 4 - argüman sütun_index_num(sütun_numarası), yani Alınacak verileri içeren sütunun numarası.

Örnek 2: Tablonun başka bir sayfada görüntülendiği iki kritere dayalı DÜŞEYARA

Ana tabloyu, başka bir sayfada veya başka bir Excel çalışma kitabında bulunan ikinci bir tablodan (Arama tablosu) veri ekleyerek güncellemeniz gerekiyorsa, gerekli değeri doğrudan ana tabloya eklediğiniz formülde toplayabilirsiniz. .

Önceki örnekte olduğu gibi Arama tablosunda birleştirilmiş değerleri içeren bir yardımcı sütuna ihtiyacınız olacaktır. Bu sütun, arama için belirtilen aralığın en soldaki sütunu olmalıdır.

Yani, formülü DÜŞEYARAşöyle olabilir:

DÜŞEYARA(B2&" "&C2,Siparişler!$A&$2:$D$2,4,YANLIŞ)
=DÜŞEYARA(B2&" "Siparişler!$A&$2:$D$2;4;YANLIŞ)

Burada B ve C sütunları sırasıyla müşteri adlarını ve ürün adlarını içerir ve bağlantı Siparişler!$A&$2:$D$2 başka bir çalışma sayfasında aranacak tabloyu tanımlar.

Formülü daha okunaklı hale getirmek için, görüntülediğiniz aralığa bir ad verebilirsiniz; bu, formülün çok daha basit görünmesini sağlayacaktır:

DÜŞEYARA(B2&" "&C2,Siparişler,4,YANLIŞ)
=DÜŞEYARA(B2&" "Siparişler;4;YANLIŞ)

Formülün çalışması için görüntülediğiniz tablonun en sol sütununda yer alan değerlerin, arama kriterlerindekiyle aynı şekilde birleştirilmesi gerekir. Yukarıdaki şekilde değerleri birleştirip aralarına boşluk koyduk, aynı işlemi fonksiyonun ilk argümanında da yapmak gerekiyor (B2&” “&C2”).

Hatırlamak!İşlev DÜŞEYARA 255 karakterle sınırlı olduğundan, 255 karakterden uzun bir değeri arayamaz. Bunu aklınızda bulundurun ve aradığınız değerin uzunluğunun bu sınırı aşmadığından emin olun.

Yardımcı bir sütun eklemenin en şık ve her zaman kabul edilebilir bir çözüm olmadığını kabul ediyorum. Aynı şeyi yardımcı sütun olmadan da yapabilirsiniz ancak bu, işlevlerin birleşiminden oluşan çok daha karmaşık bir formül gerektirir. DİZİN(ENDEKS) ve KİBRİT(KİBRİT).

2., 3. vb.'yi çıkarıyoruz. DÜŞEYARA kullanan değerler

Sen bunu zaten biliyorsun DÜŞEYARA yalnızca bir eşleşen değer, daha doğrusu ilk bulunan değeri döndürebilir. Peki ya görüntülediğiniz dizide bu değer birkaç kez tekrarlanıyorsa ve siz bunlardan 2'nci veya 3'üncüyü çıkarmak istiyorsanız? Peki ya tüm değerler? Sorun karmaşık görünüyor ama bir çözümü var!

Tablonun bir sütununun müşterilerin adlarını (Müşteri Adı), diğer sütununun ise satın aldıkları ürünleri (Ürün) içerdiğini varsayalım. Belirli bir müşterinin satın aldığı 2., 3. ve 4. ürünleri bulmaya çalışalım.

En basit yol, sütunun önüne bir yardımcı sütun eklemektir. müşteri adı ve her ismin tekrar numarasını içeren müşteri adlarıyla doldurun, ör. John Doe1, John Doe2 vesaire. Fonksiyonu kullanarak numaralandırmayla ilgili hile yapalım COUNTIF(COUNTIF), müşteri adlarının B sütununda olduğu dikkate alındığında:

B2&EĞERSAY($B$2:B2;B2)
=B2&EĞERSAY($B$2:B2,B2)

Bundan sonra normal işlevi kullanabilirsiniz DÜŞEYARAİhtiyacınız olan siparişi bulmak için. Örneğin:

Bulduk 2. Dan Brown:
DÜŞEYARA("Dan Brown2",$A$2:$C$16,3,YANLIŞ)
=DÜŞEYARA("Dan Brown2",$A$2:$C$16,3,YANLIŞ)
Bulduk 3 üncü alıcının sipariş ettiği mallar Dan Brown:
DÜŞEYARA("Dan Brown3",$A$2:$C$16,3,YANLIŞ)
=DÜŞEYARA("Dan Brown3",$A$2:$C$16,3,YANLIŞ)

Eğer sadece arıyorsan 2. tekrarlayarak, bunu yardımcı sütun olmadan daha karmaşık bir formül oluşturarak yapabilirsiniz:

EĞERHATA(DÜŞEYARA($F$2,DOLAYLI("$B$"&(EŞLEŞTİR($F$2,Tablo4,0)+2)&":$C16"),2,YANLIŞ),"")
=EĞERHATA(DÜŞEYARA($F$2,DOLAYLI("$B$"&(EŞLEŞME($F$2,Tablo4,0)+2)&":$C16"),2,DOĞRU)"")

Bu formülde:

$F$2– alıcının adını içeren bir hücre (değişmemiştir, lütfen unutmayın – bağlantı mutlaktır);
$B$- kolon müşteri adı;
Tablo4– Masanız (burada düzenli bir aralık da olabilir);
16 $– tablonuzun veya aralığınızın son hücresi.

Bu formül yalnızca ikinci eşleşen değeri bulur. Kalan tekrarları çıkarmanız gerekiyorsa önceki çözümü kullanın.

Tüm eşleşmelerin bir listesine ihtiyacınız varsa - işlev DÜŞEYARA Bir seferde yalnızca bir değer döndürdüğü için burada herhangi bir yardım yoktur; hepsi bu. Ancak Excel'in bir işlevi var DİZİN(INDEX), bu görevle kolayca başa çıkabilir. Aşağıdaki örnekte böyle bir formülün nasıl görüneceğini öğreneceksiniz.

İstenilen değerin tüm tekrarlarının alınması

Yukarıda belirtildiği gibi, DÜŞEYARA görüntülenen aralıktaki tüm yinelenen değerleri çıkaramaz. Bunu yapmak için birkaç Excel işlevinden oluşan biraz daha karmaşık bir formüle ihtiyacınız olacak: DİZİN(ENDEKS), KÜÇÜK(KÜÇÜK) ve SIRA(ASTAR)

Örneğin, aşağıdaki formül, B2:B16 aralığındaki F2 hücresindeki bir değerin tüm oluşumlarını bulur ve C sütunundaki aynı satırların sonucunu döndürür.

(=EĞERHATA(ENDEKS($C$2:$C$16,KÜÇÜK(EĞER($F$2=B2:B16,SATIR(C2:C16)-1,""),SATIR()-3)),"") )
(=EĞERHATA(ENDEKS($C$2:$C$16,KÜÇÜK(EĞER($F$2=B2:B16,SATIR(C2:C16)-1;"");SATIR()-3));"") )

Bu dizi formülünü birden fazla bitişik hücreye girin; F4:F8 aşağıdaki şekilde gösterildiği gibi. Hücre sayısı, arama değerinin mümkün olan maksimum tekrar sayısına eşit veya bundan daha fazla olmalıdır. Tıklamayı unutmayın Ctrl+Üst Karakter+Enter Dizi formülünü doğru girmek için.

Nasıl çalıştığını anlamak istiyorsanız formülün ayrıntılarına biraz daha derinlemesine bakalım:

Bölüm 1:

IF($F$2=B2:B16,SATIR(C2:C16)-1,"")
IF($F$2=B2:B16;SATIR(C2:C16)-1;"")

$F$2=B2:B16– F2 hücresindeki değeri B2:B16 aralığındaki değerlerin her biriyle karşılaştırın. Bir eşleşme bulunursa ifade SATIR(C2:C16)-1 karşılık gelen satırın numarasını döndürür (değer -1 başlık satırını atlamanıza olanak tanır). Eşleşme yoksa işlev EĞER(IF) boş bir dize döndürür.

Fonksiyonun sonucu EĞER(EĞER) şöyle bir yatay dizi olacaktır: (1,"",3,"",5,"", ",","",,"",,"",,"",12," ","","")

Bölüm 2:

SATIR()-3
SATIR()-3

İşte fonksiyon SIRA(SATIR) ek bir sayaç görevi görür. Formül F4:F9 hücrelerine kopyalandığı için sayıyı çıkarıyoruz 3 değeri almak için fonksiyon sonucundan 1 bir hücrede F4(satır 4, 3'ü çıkarın) elde etmek için 2 bir hücrede F5(satır 5, 3'ü çıkarın) vb.

Bölüm 3:

KÜÇÜK(EĞER($F$2=B2:B16,SATIR(C2:C16)-1,""),SATIR()-3))
KÜÇÜK(EĞER($F$2=B2:B16,SATIR(C2:C16)-1,""),SATIR()-3))

İşlev KÜÇÜK(KÜÇÜK) şunu döndürür: n'inci veri dizisindeki en küçük değer. Bizim durumumuzda hangi konuma (en küçükten başlayarak) dönüleceği fonksiyon tarafından belirlenir. SIRA(HAT) (bkz. Bölüm 2). Yani hücre için F4 işlev KÜÇÜK((dizi);1)İadeler 1 inci dizinin (en küçük) elemanı, yani 1 . Hücre için F5İadeler 2. dizinin en küçük elemanı yani 3 , ve benzeri.

Bölüm 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,SATIR(C2:C16)-1,""),SATIR()-3))
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,SATIR(C2:C16)-1;""),SATIR()-3))

İşlev DİZİN(INDEX) yalnızca bir dizideki belirli bir hücrenin değerini döndürür C2:C16. Hücre için F4 işlev ENDEKS($C$2:$C$16,1) dönecek Elmalar, İçin F5 işlev ENDEKS($C$2:$C$16,3) dönecek Tatlılar ve benzeri.

Bölüm 5:

EĞERHATA()
EĞERHATA()

Son olarak formülü fonksiyonun içine yerleştiriyoruz. EĞER HATA(EĞERHATA), bir hata mesajından memnun olma olasılığınız düşük olduğundan #Yok(#YOK) formülün kopyalandığı hücre sayısı, görüntülenen aralıktaki yinelenen değerlerin sayısından azsa.

Bilinen satır ve sütuna göre iki boyutlu arama

Excel'de iki boyutlu arama gerçekleştirmek, bilinen bir satır ve sütun numarasını kullanarak bir değerin aranmasını içerir. Başka bir deyişle, belirli bir satır ve sütunun kesişimindeki hücre değerini alırsınız.

O halde hadi tablomuza gidelim ve fonksiyonun bulunduğu formülü yazalım. DÜŞEYARA Mart ayında satılan limonların maliyeti hakkında bilgi bulacak.

İki boyutlu arama gerçekleştirmenin birkaç yolu vardır. Mevcut seçenekleri öğrenin ve size en uygun olanı seçin.

DÜŞEYARA ve KAÇINCI işlevleri

Bir sürü işlevi kullanabilirsiniz DÜŞEYARA(DÜŞEYARA) ve ARAMAK(MATCH) alanların kesişimindeki değeri bulmak için Ürünün adı(dize) ve Ay Söz konusu dizinin (sütun):

DÜŞEYARA("Limonlar",$A$2:$I$9,EŞLEŞTİR("Mart",$A$1:$I$1,0),FALSE)
=DÜŞEYARA("Limonlar",$A$2:$I$9,EŞLEŞTİR("Mart",$A$1:$I$1,0),YANLIŞ)

Yukarıdaki formül düzenli bir fonksiyondur DÜŞEYARA A2'den A9'a kadar olan hücrelerde "Limonlar" değerinin tam eşleşmesini arar. Ancak Mart ayı satışlarının hangi sütunda olduğunu bilmediğiniz için fonksiyonun üçüncü argümanı için sütun numarasını belirtemezsiniz. DÜŞEYARA. Bunun yerine fonksiyon kullanılır ARAMAK Bu sütunu tanımlamak için.

KAÇINCI("Mart",$A$1:$I$1,0)
KAÇINCI("Mart",$A$1:$I$1,0)

İnsan diline çevrildiğinde bu formül şu anlama gelir:

“Mar” sembollerini arıyoruz – argüman aranan_değer(arama_değeri);
Argüman için A1'den I1'e kadar olan hücrelere bakıyoruz aranan_dizi(view_array);
Tam eşleşmeyi döndürme - bağımsız değişken eşleşme türü(eşleşme türü).

Kullanma 0 üçüncü argümanda fonksiyona şunu söylersiniz: ARAMAK arama değeriyle tam olarak eşleşen ilk değeri arayın. Bu değere eşdeğerdir YANLIŞ(YANLIŞ) dördüncü argüman için DÜŞEYARA.

İki boyutlu arama veya iki yönlü arama olarak da bilinen Excel'de iki yönlü arama için formülü bu şekilde oluşturabilirsiniz.

TOPLAMÇARPIM işlevi

İşlev ÖZETÜRÜN(SUMproduct) seçilen dizilerin çarpımlarının toplamını döndürür:

TOPLAMÇARPIM(($A$2:$A$9=Limonlar")*($A$1:$I$1=Mart"),$A$2:$I$9)
=TOPLAÇARP(($A$2:$A$9=Limonlar")*($A$1:$I$1=Mart");$A$2:$I$9)

INDEX ve KAÇINCI işlevleri

Bu işlevleri bir sonraki makalede ayrıntılı olarak açıklayacağım, dolayısıyla şimdilik bu formülü kopyalamanız yeterli:

ENDEKS($A$2:$I$9,KAÇINCI("Limonlar",$A$2:$A$9,0),KAÇINCI("Mart",$A$1:$I$1,0))
=ENDEX($A$2:$I$9,MATCH("Limonlar",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

Adlandırılmış Aralıklar ve Kesişme Operatörü

Tüm bu karmaşık Excel formüllerine deli değilseniz, bu görsel ve akılda kalıcı yöntem hoşunuza gidebilir:

Bir ad girerken, Microsoft Excel tıpkı bir formül girdiğinizde olduğu gibi, eşleşen adların listesini içeren bir ipucu gösterecektir.

Tıklamak Girmek ve sonucu kontrol edin

Genel olarak yukarıdaki yöntemlerden hangisini seçerseniz seçin, iki boyutlu aramanın sonucu aynı olacaktır:

Tek formülde birden fazla DÜŞEYARA kullanıyoruz

Ana tablo ile arama tablosunun ortak tek bir sütunu yoktur ve bu, olağan işlevin kullanılmasını engeller DÜŞEYARA. Ancak ilgilendiğimiz bilgileri içermeyen ancak ana tablo ve arama tablosuyla ortak sütunu olan başka bir tablo daha var.

Aşağıdaki örneğe bakalım. Sütunlu bir Ana tablomuz var SKU (yeni), başka bir tablodan karşılık gelen fiyatların bulunduğu bir sütun eklemeniz gerekir. Ayrıca 2 adet arama tablomuz var. İlki (Arama tablosu 1) güncellenmiş numaraları içerir SKU (yeni) ve ürün adları ve ikincisi (Arama tablosu 2) – ürün adları ve eski numaralar SKU (eski).

İkinci arama tablosundan ana tabloya fiyat eklemek için double olarak bilinen bir işlem yapmanız gerekir. DÜŞEYARA veya iç içe geçmiş DÜŞEYARA.

Fonksiyonu yaz DÜŞEYARA Tabloda ürün adını bulan Arama tablosu 1 kullanarak Stok Koduİstenilen değer olarak:
DÜŞEYARA(A2;Yeni_SKU;2;YANLIŞ)
=DÜŞEYARA(A2,Yeni_SKU,2,YANLIŞ)
Burada Yeni_SKU– adlandırılmış aralık $A:$B masada Arama tablosu 1, A 2 – bu, ürünlerin adlarını içeren B sütunudur (yukarıdaki resme bakın)
Bir tablodan fiyatları eklemek için bir formül yazın Arama tablosu 2 temelli ünlü isimler mal. Bunu yapmak için, daha önce oluşturduğunuz formülü yeni işlevin arama değeri olarak ekleyin DÜŞEYARA:
DÜŞEYARA(DÜŞEYARA(A2,Yeni_SKU,2,YANLIŞ),Fiyat,3,YANLIŞ)
=DÜŞEYARA(DÜŞEYARA(A2,Yeni_SKU,2,YANLIŞ),Fiyat,3,YANLIŞ)

Burada Fiyat– adlandırılmış aralık $A:$C masada Arama tablosu 2, A 3 fiyatları içeren C sütunudur.

Aşağıdaki şekil, oluşturduğumuz formülün döndürdüğü sonucu göstermektedir:

DÜŞEYARA ve DOLAYLI kullanarak farklı tablolardan verilerin dinamik olarak değiştirilmesi

Öncelikle birbirimizi doğru anladığımızdan emin olmak için “Farklı tablolardaki verilerin dinamik olarak değiştirilmesi” ifadesinden ne kastettiğimizi açıklayalım.

Aynı formattaki verileri içeren birkaç sayfanın olduğu durumlar vardır ve belirli bir hücreye girilen değere bağlı olarak gerekli bilgilerin belirli bir sayfadan çıkarılması gerekir. Bunu bir örnekle açıklamanın daha kolay olduğunu düşünüyorum.

Aynı ürünlere ve aynı formata sahip birden fazla bölge için satış raporlarınızın olduğunu hayal edin. Belirli bir bölgeye ait satış rakamlarını bulmanız gerekiyor:

Bu tür yalnızca iki raporunuz varsa, o zaman son derece basit ve işlevlere sahip bir formül kullanabilirsiniz. DÜŞEYARA Ve EĞER(IF) aranacak istenen raporu seçmek için:

DÜŞEYARA($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=DÜŞEYARA($D$2,EĞER($D3="FL",FL_Sales,CA_Sales),2,YANLIŞ)

$D$2– bu, ürünün adını içeren bir hücredir. Formülü diğer hücrelere kopyalarken arama değerinin değiştirilmesini önlemek için burada mutlak referanslar kullandığımızı lütfen unutmayın.
$D3– bu, bölgenin adını taşıyan bir hücredir. Formülü aynı sütundaki diğer hücrelere kopyalamayı planladığımız için sütun için mutlak bir başvuru, satır için ise göreli bir başvuru kullanıyoruz.
FL_Sales Ve CA_Satış– ilgili satış raporlarını içeren tabloların (veya adlandırılmış aralıkların) adları. Elbette normal sayfa adlarını ve hücre aralığı referanslarını kullanabilirsiniz; ‘FL Sayfası’!$A$3:$B$10 ancak adlandırılmış aralıklar çok daha kullanışlıdır.

Ancak bu tür çok sayıda tablo olduğunda işlev EĞER– bu en iyi çözüm değil. Bunun yerine işlevi kullanabilirsiniz. DOLAYLI(DOLAYLI) istenen arama aralığına dönmek için.

Muhtemelen bildiğiniz gibi, fonksiyon DOLAYLI bir metin dizesi verilen bir bağlantıyı döndürmek için kullanılır; şu anda tam olarak ihtiyacımız olan şey budur. Bu nedenle, ifadeyi yukarıdaki formüldeki fonksiyonla değiştirmekten çekinmeyin. EĞER işlevin bulunduğu bağlantıya DOLAYLI. Bu kombinasyon DÜŞEYARA Ve DOLAYLIçiftler halinde harika çalışıyor:

DÜŞEYARA($D$2,DOLAYLI($D3&"_Satışlar"),2,YANLIŞ)
=DÜŞEYARA($D$2,DOLAYLI($D3&"_Satışlar"),2,YANLIŞ)

$D$2– bu, ürünün adının bulunduğu bir hücredir, mutlak bağlantı nedeniyle değişmez.
$D3– bu, bölge adının ilk kısmını içeren hücredir. Örneğimizde bu FL.
_Satış– tüm adlandırılmış aralıkların veya tabloların adının ortak kısmı. D3 hücresindeki değerle birleştirildiğinde istenen aralığın tam adını oluşturur. Aşağıda bu işlevle ilgili deneyimi olmayanlar için bazı ayrıntılar verilmiştir. DOLAYLI.

DOLAYLI ve DÜŞEYARA nasıl çalışır?

Öncelikle size fonksiyonun sözdizimini hatırlatmama izin verin DOLAYLI(DOLAYLI):

A1 eğer argüman eşitse DOĞRU(DOĞRU) veya belirtilmemiş;
R1C1, Eğer FAYRICA(YALAN).

O halde satış raporlarımıza geri dönelim. Hatırlarsanız her rapor ayrı bir sayfada yer alan ayrı bir tablodur. Formülün doğru çalışması için tablolarınıza (veya aralıklarınıza) ad vermeniz ve tüm adların ortak bir parçasının olması gerekir. Örneğin şöyle: CA_Satış, FL_Satış, TX_Sales ve benzeri. Gördüğünüz gibi tüm isimler “_Sales” içeriyor.

İşlev DOLAYLI D sütunundaki değer ile "_Sales" metin dizesini birbirine bağlar ve böylece raporlama yapar DÜŞEYARA hangi tabloya bakılacağı "FL" değeri D3 hücresindeyse formül tabloda arama yapar FL_Satış, eğer tabloda “CA” varsa CA_Satış ve benzeri.

Fonksiyonların sonucu DÜŞEYARA Ve DOLAYLI aşağıdaki olacaktır:

Veriler farklı Excel çalışma kitaplarında bulunuyorsa çalışma kitabının adını adlandırılmış aralığın önüne eklemeniz gerekir, örneğin:

DÜŞEYARA($D$2,DOLAYLI($D3&"Çalışma Kitabı1!_Satışlar"),2,YANLIŞ)
=DÜŞEYARA($D$2,DOLAYLI($D3&"Çalışma Kitabı1!_Satışlar"),2,YANLIŞ)

Eğer fonksiyon DOLAYLI başka bir çalışma kitabına başvuruyorsa bu çalışma kitabının açık olması gerekir. Kapalıysa fonksiyon bir hata bildirecektir #REF!(#BAĞLANTI!).

Yukarıda, tek bir verimlilik göstergesi W'yi maksimuma çıkaracak (veya minimuma indirecek) bir çözüm seçmenin gerekli olduğu yöneylem araştırması problemini ele aldık. Pratikte, genellikle bir operasyonun etkinliğinin arttığı bir durum vardır. tek bir göstergeyle değil, aynı anda birkaç göstergeyle değerlendirilmek: bunlardan birinin daha fazla gösterge yapılması arzu edilirken, diğerlerinin daha az olması arzu edilir.

Tipik olarak büyük, karmaşık operasyonların etkinliği tek bir gösterge kullanılarak kapsamlı bir şekilde karakterize edilemez; kendisine yardım etmeleri için başkalarını, başkalarını çekmesi gerekiyor.

Örneğin performansı değerlendirirken sanayi kuruluşu Aşağıdakiler gibi bir dizi göstergeyi dikkate almak gerekir:

Kâr,

Toplam üretim hacmi (“şaft”),

Maliyet vb.

Bir savaş operasyonunu analiz ederken, etkinliğini karakterize eden ana göstergeye (örneğin, düşmana verilen hasarın matematiksel beklentisi) ek olarak, aşağıdakiler gibi bir dizi ek göstergeyi de hesaba katmak gerekir:

Kendi kayıpları

Operasyon yürütme süresi,

Mühimmat tüketimi vb.

Bazılarının maksimuma çıkarılması ve diğerlerinin minimuma indirilmesi istenen performans göstergelerinin bu kadar çokluğu, herhangi bir karmaşık yöneylem araştırması problemi için tipiktir. Soru ortaya çıkıyor: ne yapmalı?

Öncelikle ileri sürülen gerekliliklerin genel anlamda uyumsuz olduğunun altı çizilmelidir. Bir göstergeyi maksimuma çeviren çözüm, kural olarak diğer göstergeleri maksimuma ya da minimuma çevirmez. Bu nedenle yaygın formülasyon “maksimum etkiyi elde etmek”tir. minimum maliyetler" İçin bilimsel araştırma uymuyor. "Belirli maliyetlerle maksimum etkiyi elde etmek" veya "belirli bir etkiyi minimum maliyetle elde etmek" formülasyonlarından herhangi biri doğrudur.

Genel durumda, bir göstergeyi maksimuma ve aynı zamanda başka bir göstergeyi maksimuma (veya minimuma) dönüştürecek bir çözüm yoktur; özellikle de birden fazla gösterge için böyle bir çözüm mevcut değildir. Ancak niceliksel performans analizi, çoklu performans göstergelerinin olduğu durumlarda da çok faydalı olabilir.

Her şeyden önce, daha aşağı düzeyde olan, açıkça irrasyonel olan karar seçeneklerini önceden bir kenara atmanıza olanak tanır. en iyi seçenekler tüm göstergelere göre.

Bunu bir örnekle açıklayalım. İki göstergeyle değerlendirilen O savaş operasyonunu analiz edelim:

W - bir savaş görevini tamamlama olasılığı (“etkinlik”);

S - harcanan fonların maliyeti.

Açıkçası, ilk göstergeyi maksimuma, ikinciyi minimuma çevirmeniz tavsiye edilir.

Basitlik açısından aralarından seçim yapılabilecek sonlu bir sayı olduğunu varsayalım - 20 Çeşitli seçeneklerçözümler; bunları her biri için hem W hem de göstergelerin değerlerini gösterelim.

Açıklık sağlamak için, her çözüm seçeneğini W ve S koordinatlarına sahip bir düzlem üzerinde bir nokta olarak gösterelim (Şekil 1.1).

Şekle baktığımızda bazı çözüm seçeneklerinin “rekabetsiz” olduğunu ve bir an önce bir kenara atılması gerektiğini görüyoruz. Gerçekte, aynı S maliyetine sahip diğer seçeneklere göre verimlilik W avantajına sahip olan seçenekler bölgenin sağ sınırında yer almalıdır. olası seçenekler. Eşit verimlilikle daha düşük maliyetlere sahip olan seçenekler, olası seçenekler aralığının alt sınırında yer almalıdır.

Etkinliği iki göstergeyi kullanarak değerlendirirken hangi seçenekler tercih edilmelidir? Açıkçası, bölgenin hem sağ hem de alt sınırlarında aynı anda uzananlar (Şekil 1.1'deki noktalı çizgiye bakınız). Nitekim sınırın bu bölümünde yer almayan seçeneklerin her biri için, her zaman verimlilik açısından kendisinden daha aşağı olmayan, daha ucuz veya tam tersine ucuzluk açısından daha aşağı olmayan, daha etkili olan başka bir seçenek vardır. Bu nedenle, daha önce öne sürülen 20 seçenekten çoğunluğu rekabetin dışında kalıyor ve biz sadece geri kalan dört seçeneği analiz ediyoruz: . Bunlardan en etkili olanıdır ancak nispeten pahalıdır; - en ucuzu ama o kadar etkili değil. Karar vericinin görevi, verimlilikteki belirli bir artış için hangi bedeli ödemeye hazır olduğumuzu veya tam tersine, çok büyük maddi kayıplara maruz kalmamak için verimlilikten ne kadar fedakarlık etmeye hazır olduğumuzu bulmaktır.

Seçeneklerin benzer bir önizlemesi (böyle bir görsel geometrik yorum olmasa da) birçok gösterge durumunda yapılabilir:

Rekabetçi olmayan çözüm seçeneklerine yönelik bu tür bir ön tarama prosedürü, her zaman çeşitli göstergelere sahip bir yöneylem araştırması probleminin çözümünden önce gelmelidir. Bu, uzlaşma ihtiyacını ortadan kaldırmasa da, seçimin yapıldığı kararlar kümesini önemli ölçüde azaltır.

Bir operasyonun aynı anda birkaç göstergeye dayalı kapsamlı bir değerlendirmesinin zor olması ve derinlemesine düşünmeyi gerektirmesi nedeniyle, pratikte çoğu zaman birkaç göstergeyi yapay olarak tek bir genelleştirilmiş göstergede (veya kriterde) birleştirmeye çalışırlar. Genellikle bir kesir bu kadar genelleştirilmiş (bileşik) bir kriter olarak alınır; payda arttırılması arzu edilen göstergeleri ve paydada azaltılması arzu edilenleri koyarlar:

Örneğin bir savaş operasyonundan bahsediyorsak payda “bir savaş görevini tamamlama olasılığı” veya “düşman kayıpları” gibi değerler yer alıyor; paydada - “kendi kayıpları”, “mühimmat tüketimi”, “operasyon süresi” vb.

(5.1) türündeki “bileşik kriterlerin” ortak dezavantajı, bir göstergedeki verim eksikliğinin her zaman bir başka göstergeyle telafi edilebilmesidir (örneğin, Düşük olasılık bir savaş görevinin yerine getirilmesi - düşük mühimmat tüketimi vb. nedeniyle). Bu tür kriterler, Leo Tolstoy'un şakacı bir şekilde kesir şeklinde önerdiği "bir kişiyi değerlendirme kriterini" anımsatıyor; burada pay, kişinin gerçek değerleri ve payda, onun kendisi hakkındaki görüşüdür. Böyle bir kriterin tutarsızlığı açıktır: eğer ciddiye alırsak, o zaman neredeyse hiçbir erdemi olmayan, ancak tamamen kendine saygısı olmayan bir kişi sonsuz derecede daha büyük bir değere sahip olacaktır!

Çoğunlukla "bileşik kriterler" bir kesir olarak değil, bireysel performans göstergelerinin "ağırlıklı toplamı" olarak önerilmektedir:

pozitif veya negatif katsayılar nerede. En üst düzeye çıkarılması istenen göstergeler için olumlu olanlar belirlenir; en aza indirilmesi arzu edilenler için olumsuz. Katsayıların (“ağırlıklar”) mutlak değerleri göstergelerin önem derecesine karşılık gelir.

(5.2) tipindeki bir bileşik kriterin esas olarak (5.1) tipindeki bir kriterden farklı olmadığını ve aynı dezavantajlara sahip olduğunu (farklı göstergelerin karşılıklı olarak telafi edilmesi olasılığı) görmek kolaydır. Bu nedenle, her türlü “bileşik” kriterin eleştirilmeden kullanılması tehlikelerle doludur ve yanlış önerilere yol açabilir. Ancak bazı durumlarda "ağırlıklar" keyfi olarak değil, bileşik kriterin işlevini en iyi şekilde yerine getirecek şekilde seçildiğinde, onun yardımıyla sınırlı değerde bazı sonuçlar elde etmek mümkündür.

Bazı durumlarda, yalnızca bir (ana) verimlilik göstergesi seçip onu en üst düzeye çıkarmaya çalışırsak ve geri kalan yardımcı göstergelere yalnızca bazı kısıtlamalar getirirsek, birkaç göstergeyle ilgili bir sorun, tek bir göstergeyle ilgili bir soruna indirgenebilir:

Bu kısıtlamalar elbette belirtilen koşullar kümesine dahil edilecektir.

Örneğin, bir sanayi kuruluşunun çalışma planını optimize ederken kârın maksimuma çıkarılmasını, ürün çeşitliliği planının yerine getirilmesini ve üretim maliyetinin belirtilen planı aşmamasını talep edebilirsiniz. Bombardıman planlarken düşmana verilecek zararın maksimum olmasını isteyebilirsiniz ancak aynı zamanda kendi kayıplarınız ve operasyonun maliyeti de belirli sınırların dışına çıkmaz.

Sorunun bu şekilde formüle edilmesiyle, ana gösterge dışındaki tüm verimlilik göstergeleri, belirtilen çalışma koşulları kategorisine aktarılır. Verilen sınırlara uymayan çözüm seçenekleri, rekabetçi olmadığı gerekçesiyle anında elenir. Elde edilen tavsiyeler elbette destekleyici göstergelere ilişkin limitlerin nasıl seçildiğine bağlı olacaktır. Bunun nihai karar önerilerini ne kadar etkilediğini belirlemek için kısıtlamaları makul sınırlar içerisinde değiştirmek faydalı olacaktır.

Son olarak, uzlaşmacı bir çözüm oluşturmanın "ardışık tavizler yöntemi" olarak adlandırılabilecek başka bir olası yolu daha vardır.

Verimlilik göstergelerinin azalan önem sırasına göre düzenlendiğini varsayalım: önce ana gösterge, sonra diğerleri, yardımcı göstergeler: Basitlik açısından, her birinin maksimuma döndürülmesi gerektiğini varsayacağız (eğer bu değilse) durumda göstergenin işaretini değiştirmek yeterlidir). Uzlaşmış bir çözüm oluşturma prosedürü aşağıdaki gibidir. İlk olarak, verimliliğin ana göstergesini en üst düzeye çıkaran bir çözüm aranır. Daha sonra, pratik değerlendirmelere ve başlangıç verilerinin doğruluğuna (ve genellikle küçüktür) dayanarak, izin vermeyi kabul ettiğimiz belirli bir "taviz" verilir. İkinci göstergeyi maksimuma çıkarmak için göstergeye, W'nin mümkün olan maksimum değer olduğu yerden daha az olmayacak şekilde bir kısıtlama getiriyoruz ve bu kısıtlamayla onu maksimuma dönüştüren bir çözüm arıyoruz. göstergedeki imtiyaz” yine maliyeti en üst düzeye çıkarılabilecek şekilde atanır, vb.

Uzlaşmaya dayalı bir çözüm oluşturmanın bu yöntemi iyidir çünkü bir göstergeden "ödün vermenin" diğer bir göstergeden kazanç sağlamanın ne kadara mal olacağı hemen anlaşılır.

Maksimum alanda çözümün etkinliği genellikle çok az değiştiğinden, küçük "tavizler" pahasına elde edilen bir çözümü seçme özgürlüğünün önemli olabileceğini unutmayın.

Öyle ya da böyle, herhangi bir resmileştirme yöntemiyle, çeşitli göstergelere dayalı bir kararı niceliksel olarak gerekçelendirme görevi tam olarak tanımlanmamıştır ve nihai çözüm seçimi, "komutan" ın gönüllü eylemiyle belirlenir (şartlı olarak adlandıracağımız gibi) seçimden sorumlu kişi). Araştırmacının görevi komutana yeterli miktarda veri sağlamaktır, buna izin veriyorum. her çözüm seçeneğinin avantajlarını ve dezavantajlarını kapsamlı bir şekilde değerlendirebilir ve bunlara dayanarak nihai bir seçim yapabilir.

13.12.5. Bütünleyici kriterler: ekosistemlerin kalitesinin çeşitli göstergelere dayalı olarak değerlendirilmesi

Hidrobiyolojik ve mikrobiyolojik göstergelere göre su kalitesi sınıfları, hidrolojik, hidrokimyasal ve hidrobiyolojik göstergelerin izlenmesine yönelik programların içeriğini düzenleyen “Dolusavakların ve su yollarının su kalitesinin izlenmesine ilişkin kurallar” [GOST 17.1.3.07–82] ile belirlenir. izleme sıklığının yanı sıra numune alma noktalarının amacı ve konumu
(Tablo 13.7). Bu belgeye göre su kirliliğinin derecesi, Sladechek tarafından değiştirilen Pantle ve Bucca'ya göre saflık indeksi, Goodnight-Whitley ve Parele oligochaete indeksi, Woodiwiss biyotik indeksi ve geleneksel bir set dikkate alınarak değerlendirilir. mikrobiyolojik göstergelerin

İntegral gösterge E.V.'ye göre. Balushkina, karışık organik ve toksik kirliliğe maruz kalan su kütlelerinin ekosistemlerinin durumunu değerlendirmek için geliştirildi ve kullanıldı. Ladoga Gölü - nehir sisteminde kapsamlı testlerden geçti. Neva, Finlandiya Körfezi'nin doğu kısmıdır [Balushkina ve diğerleri, 1996]. İntegral IP göstergesi aşağıdaki formül kullanılarak hesaplanır:

IP = K 1 * S t + K 2 *OI + K 3 *K ch + K 4 / BI,

burada S t saprotoksisite indeksi V.A'dır. Yakovleva (K 1 = 25); OI – Goodnight ve Whitley oligochaete indeksi, oligochaetes sayısının toplam zoobentos sayısına yüzde olarak oranına eşittir (K 2 = 1); K ch – Balushkina chironomid indeksi (K 3 = 8,7); 1/BI, Woodiwiss biyotik indeksinin (K 4 @ 100) tersidir.

E.V. Balushkina, elde ettiği bütünleyici göstergenin ana endekslerin tüm en iyi özelliklerini içerdiğine ve bentik toplulukların özelliklerini mümkün olduğunca dikkate aldığına inanıyor: saprobotoksin gösterge türlerinin varlığı, daha yüksek taksonomik sıradaki hayvan gösterge gruplarının oranı , bireysel grupların hakimiyet derecesi ve bir bütün olarak topluluğun yapısı.

A.I.'ye göre birleşik toplum sağlığı indeksi. Bakanov. Yazar, Rusya'daki bir dizi nehir, göl ve rezervuardaki bentik toplulukların durumunu değerlendirirken, bentosun durumunu niceliksel olarak karakterize etmek için aşağıdaki göstergeleri kullanmıştır: sayı (N), örnek/m2; biyokütle (B), g/m2; tür sayısı (S); Shannon tür çeşitliliği (N), bit/ind.; Parele oligochaete indeksi (OPI, %), oligochaete tubificid sayısının toplam bentos sayısına oranına eşit olup, bentik organizmaların ilk üç baskın türünün ağırlıklı ortalama saprobitesi olarak hesaplanan ortalama saprobite (AS). Listelenen göstergelerin değerlerini birleştirmek ve bunları bir sayıyla değiştirmek için, sonuçta ortaya çıkan bir gösterge önerilmektedir - integral sıralama göstergelerini hesaplamak için olağan yöntem kullanılarak bulunan birleşik topluluk durumu endeksi (CISS; [Bakanov, 1997]):

burada R i, i'inci göstergeye göre istasyonun sırasıdır, R i bu göstergenin "ağırlığıdır", k göstergelerin sayısıdır.

İlk olarak, tüm istasyonlar her gösterge için sıralanır ve N, B, H ve S'nin maksimum değerlerine 1. sıra atanır. Birkaç istasyonda herhangi bir göstergenin değerleri aynıysa, o zaman şu şekilde karakterize edilir: ortalama bir sıralama. Makale, son formülün (4.22) farklı versiyonlarını sunmaktadır (formüllerin göstergelerin mutlak değerlerini değil, sıralarını içerdiğini vurguluyoruz):

KISS = (2B + N + H + S)/5, burada biyokütleye 2 "ağırlık" verilir, çünkü topluluktan geçen enerji akışı miktarıyla ilişkilidir ve bu, biyokütlenin durumunun değerlendirilmesi için son derece önemlidir;

KISS = (2CC + 1.5OIP + 1.5B + N + H + S)/8, burada ortalama sağlığın kirlilikle en yakından ilişkili olduğuna inanılıyor.

KISS değeri ne kadar düşük olursa toplumun durumu o kadar iyi olur.

Topluluğun durumu hem doğal çevresel faktörlere (derinlik, toprak, akıntı vb.) hem de kirliliğin varlığına, doğasına ve yoğunluğuna bağlı olduğundan, sıralama değerlerini de içeren birleşik bir kirlilik indeksi (CPI; [Bakanov, 1999]) , ayrıca üç gösterge hesaplanır:

KIZ = (SS + OIP + B)/3. (4.23)

Buradaki göstergelerin sıralaması ters sırada gerçekleştirilir (minimum değerlerden maksimuma)

KISS ve KIZ, istasyonları, seçilen göstergeler dizisine göre topluluğun en iyi durumunun minimum endeks değerleriyle ve en kötüsünün maksimum değerleriyle karakterize edildiği bir ölçekte sıralayan göreceli endekslerdir. Belirli bir istasyondaki göstergelerin değerlerini karakterize eden değerlere ek olarak, tüm istasyon seti için ortalama değerleri hesaplanır. Endekslerin tek tek istasyonlarda ortalamaya göre değişimi, genel eğilime kıyasla bu istasyonlarda işlerin daha mı kötü yoksa daha mı iyi olduğuna karar verilmesini mümkün kılıyor.

KISS ve KIZ değerleri arasındaki Spearman sıra korelasyon katsayısının hesaplanması, kirliliğin zoobenthos topluluklarının durumunu ne kadar etkilediğini gösterir. Bu endekslerin değerleri arasında anlamlı bir pozitif korelasyon varsa, o zaman bentik hayvan topluluklarının durumu büyük ölçüde kirliliğin varlığıyla belirlenir (aksi takdirde doğal çevresel faktörler tarafından belirlenir).

Öncesi