Bugün Excel işlevine bakacağız – BULMAK(İngilizce versiyonda BULMAK), arama metninin metin içindeki konumunu ilk karaktere göre bulmanızı sağlar. Genellikle, ancak her zaman değil, BUL işlevi, aşağıdaki makalelerde ele alacağımız diğer Excel işlevleriyle birlikte kullanılır ve bugün bu işlevin çalışma prensibini anlamaya çalışacağız.

Diyelim ki ad ve soyadların listesini içeren bir tablomuz var.

Görevimiz alanın ilk karaktere göre konumunu bulmaktır. Örnek olarak adınızı ve soyadınızı alalım. Dani Estrada Aradaki farkın nerede olduğunu kendimiz hesaplayalım. Bir boşluk var 5. ilk harfle başlayan karakter. Şimdi bunu FIND fonksiyonunu kullanarak otomatik olarak yapacağız.

İşlev sözdizimi aşağıdaki gibidir

BUL(arama_metni; görüntülenen_metin; [başlangıç_konumu]), burada

arama_metni metinde bulmamız gereken metindir, bizim durumumuzda boşluktur, " " şeklinde belirtilir.

görüntülenen_metin– bu, istenen metnin arandığı metindir, bizim durumumuzda bu, ad ve soyadı veya A2 hücresidir

start_position isteğe bağlı bir argümandır; metni hesapta hangi konumdan aramamız gerektiğini belirtir. Örneğin arama metni iki kez geçiyorsa ve ikinci arama metninin konumunu bilmemiz gerekiyorsa buna ihtiyaç duyulabilir.

=BUL(" " ;A2 ;1) formülünü yazıyoruz, aşağı sürükleyip ihtiyacımız olan sonucu alıyoruz.

Bu eğitimde işlevlerin temel faydaları açıklanmaktadır DİZİN Ve ARAMAK Excel'de, bu onları daha çekici kılıyor DÜŞEYARA. İşlevin karşılaştığı birçok karmaşık görevle kolayca başa çıkmanıza yardımcı olacak birkaç formül örneği göreceksiniz. DÜŞEYARA güçsüz.

Son birkaç makalemizde, fonksiyonun temellerini acemi kullanıcılara açıklamak için her türlü çabayı gösterdik. DÜŞEYARA ve ileri düzey kullanıcılar için daha karmaşık formüllerin örneklerini gösterin. Sizi kullanmaktan caydırmazsak, şimdi deneyeceğiz. DÜŞEYARA, o zaman en azından göster alternatif yollar Excel'de dikey aramanın uygulanması.

Buna neden ihtiyacımız var? – sen sor. Evet çünkü DÜŞEYARA Arama, Excel'deki tek arama özelliği değildir ve birçok sınırlaması, birçok durumda istediğiniz sonuçları almanızı engelleyebilir. Öte yandan işlevler DİZİN Ve ARAMAK– daha esnektir ve onları diğerlerine göre daha çekici kılan bir takım özelliklere sahiptir. DÜŞEYARA.

INDEX ve MATCH hakkında temel bilgiler

Bu eğitimin amacı fonksiyonların yeteneklerini göstermek olduğundan DİZİN Ve ARAMAK Excel'de dikey aramayı uygulamak için sözdizimleri ve uygulamaları üzerinde durmayacağız.

Burada işin özünü anlamak için gereken minimumu sunacağız, ardından kullanmanın avantajlarını gösteren formül örneklerini detaylı olarak inceleyeceğiz. DİZİN Ve ARAMAK yerine DÜŞEYARA.

INDEX – işlev sözdizimi ve kullanımı

İşlev DİZİN(INDEX) Excel'de verilen satır ve sütun numaralarındaki bir diziden bir değer döndürür. İşlev şu sözdizimine sahiptir:

Her argümanın çok basit bir açıklaması vardır:

• sıralamak(dizi), değeri çıkarmak istediğiniz hücre aralığıdır.
• satır_num(satır_numarası), dizideki değeri çıkarmak istediğiniz satırın numarasıdır. Belirtilmemişse bir argüman gereklidir sütun_num(sütun_numarası).
• sütun_num(sütun_numarası), dizideki değeri çıkarmak istediğiniz sütunun numarasıdır. Belirtilmemişse bir argüman gereklidir satır_num(satır_numarası)

Her iki argüman da belirtilirse, işlev DİZİN Belirtilen satır ve sütunun kesişimindeki hücredeki değeri döndürür.

İşte basit bir fonksiyon örneği DİZİN(ENDEKS):

DİZİN(A1:C10;2;3)
=ENDEKS(A1:C10,2,3)

Formül bir aralıkta arama yapar A1:C10 ve hücre değerini döndürür 2.çizgi ve 3. sütun, yani bir hücreden C2.

Çok basit, değil mi? Ancak pratikte hangi satıra ve sütuna ihtiyacınız olduğunu her zaman bilemezsiniz ve bu nedenle fonksiyonun yardımına ihtiyacınız vardır. ARAMAK.

MATCH - işlevin sözdizimi ve kullanımı

İşlev KİBRİT Excel'deki (MATCH), bir hücre aralığında belirli bir değeri arar ve bu değerin aralıktaki göreli konumunu döndürür.

Örneğin, aralıktaysa B1:B3 New York, Paris, Londra değerlerini içeriyorsa aşağıdaki formül sayıyı döndürecektir 3 , çünkü “Londra” listedeki üçüncü unsurdur.

KAÇINCI("Londra";B1:B3,0)
=KAÇINCI("Londra";B1:B3;0)

İşlev KİBRİT(MATCH) aşağıdaki sözdizimine sahiptir:

KAÇINCI(aranan_değer,aranan_dizi,)
KAÇINCI(aranan_değer, aranan_dizi, [eşleşme_türü])

• aranan_değer(arama_değeri) aradığınız sayı veya metindir. Bağımsız değişken, boolean içeren bir değer veya hücre başvurusu olabilir.
• aranan_dizi(viewed_array) – aramanın gerçekleştiği hücre aralığı.
• eşleşme_türü(match_type) – Bu argüman fonksiyona bilgi verir ARAMAK, tam veya yaklaşık bir eşleşme bulmak istiyorsanız:
  • 1 veya belirtilmemiş– bulur maksimum değer, istenilenden küçük veya ona eşit. Görüntülenen dizi artan sırada, yani küçükten büyüğe doğru sıralanmalıdır.
  • 0 – İstenilen değere eşit olan ilk değeri bulur. Kombinasyon için DİZİN/ARAMAK her zaman tam bir eşleşmeye ihtiyacınız vardır, dolayısıyla işlevin üçüncü argümanı ARAMAK eşit olmalı 0 .
  • -1 – arama değerinden büyük veya ona eşit en küçük değeri bulur. Görüntülenmekte olan dizi azalan düzende, yani büyükten küçüğe doğru sıralanmalıdır.

İlk bakışta fonksiyonun faydası ARAMAKşüphe uyandırıyor. Bir öğenin aralıktaki konumunu kimin bilmesi gerekir? Bu elementin anlamını bilmek istiyoruz!

Aradığımız değerin göreceli konumunun (yani satır ve/veya sütun numarasının) tam olarak argümanlar için belirtmemiz gereken şey olduğunu hatırlatalım. satır_num(satır_numarası) ve/veya sütun_num(sütun_numarası) işlevler DİZİN(ENDEKS). Hatırlayacağınız gibi fonksiyon DİZİN verilen satır ve sütunun kesişimindeki değeri döndürebilir ancak hangi satır ve sütunla ilgilendiğimizi belirleyemez.

Excel'de INDEX ve MATCH Nasıl Kullanılır

Artık bu iki fonksiyon hakkındaki temel bilgileri bildiğinize göre, fonksiyonların nasıl çalıştığının da zaten netleştiğine inanıyorum. ARAMAK Ve DİZİN birlikte çalışabiliriz. ARAMAK belirli bir hücre aralığında arama değerinin göreceli konumunu belirler ve DİZİN bu sayıyı (veya sayıları) kullanır ve ilgili hücreden sonucu döndürür.

Hala tam olarak net değil mi? İşlevleri sunun DİZİN Ve ARAMAK bu formda:

ENDEKS(,(EŞLEŞME ( arama değeri,aradığımız sütun,0))
=ENDEKS( çıkardığımız sütun;(KİBRİT( arama değeri;aradığımız sütun;0))

Bir örnekle anlamanın daha da kolay olacağını düşünüyorum. Aşağıdaki eyalet başkentleri listesine sahip olduğunuzu varsayalım:

Aşağıdaki formülü kullanarak başkentlerden birinin, örneğin Japonya'nın nüfusunu bulalım:

INDEX($D$2:$D$10,MATCH("Japonya",$B$2:$B$10,0))
=ENDEX($D$2:$D$10,MATCH("Japonya",$B$2:$B$10,0))

Şimdi bu formülün her bir öğesinin ne yaptığına bakalım:

• İşlev KİBRİT(MATCH) sütunda "Japonya" değerini arar B ve özellikle hücrelerde B2:B10 ve bir sayı döndürür 3 Çünkü “Japonya” listede üçüncü sırada yer alıyor.
• İşlev DİZİN(ENDEX) kullanımları 3 tartışma için satır_num(satır_numarası), değerin hangi satırdan döndürüleceğini belirtir. Onlar. basit bir formül elde ederiz:

  ENDEKS($D$2:$D$10,3)
  =ENDEKS($D$2:$D$10,3)

  Formül şunun gibi bir şey söylüyor: hücrelere bakın D2 ile D10 ve değeri üçüncü satırdan, yani hücreden çıkarın D4, çünkü sayma ikinci satırdan başlar.

Excel'de elde ettiğiniz sonuç budur:

Önemli! İşlevin kullandığı dizideki satır ve sütun sayısı DİZİN(INDEX), bağımsız değişken değerleriyle eşleşmelidir satır_num(satır_numarası) ve sütun_num(sütun_numarası) işlevler KİBRİT(KİBRİT). Aksi takdirde formülün sonucu hatalı olacaktır.

Durun, durun... neden bir fonksiyonu kullanamıyoruz? DÜŞEYARA(VPR)? Labirentleri çözmeye çalışarak zaman kaybetmenin bir anlamı var mı? ARAMAK Ve DİZİN?

DÜŞEYARA("Japonya",$B$2:$D$2,3)
=DÜŞEYARA("Japonya",$B$2:$D$2,3)

Bu durumda hiçbir anlamı yok! Bu örneğin amacı tamamen gösterim amaçlıdır, böylece fonksiyonların nasıl çalıştığını anlayabilirsiniz. ARAMAK Ve DİZİNçiftler halinde çalışın. Aşağıdaki örnekler size paketin gerçek gücünü gösterecektir. DİZİN Ve ARAMAK birçok zor durumla kolayca başa çıkabilen DÜŞEYARA kendini çıkmazda bulur.

INDEX/MATCH neden DÜŞEYARA'dan daha iyi?

Dikey arama için hangi formülün kullanılacağına karar verirken çoğu Excel gurusu şunu düşünür: DİZİN/ARAMAK bundan çok daha iyi DÜŞEYARA. Ancak birçok Excel kullanıcısı hala Excel'i kullanmaya devam ediyor. DÜŞEYARA, Çünkü bu işlev çok daha basittir. Bunun nedeni, çok az kişinin, geçiş yapmanın tüm faydalarını tam olarak anlamasıdır. DÜŞEYARA demet başına DİZİN Ve ARAMAK ve hiç kimse daha karmaşık bir formül üzerinde çalışarak zaman kaybetmek istemez.

Excel'de MATCH/INDEX Kullanmanın 4 Ana Faydası:

1. Sağdan sola doğru arama yapın. Her yetkin Excel kullanıcısının bildiği gibi, DÜŞEYARA sola bakılamaz; bu, aranan değerin mutlaka incelenen aralığın en sol sütununda olması gerektiği anlamına gelir. Bu durumuda ARAMAK/DİZİN, arama sütunu arama aralığının solunda veya sağında olabilir. Örnek: Bu özelliği çalışırken göstereceğiz.

2. Sütunları güvenle ekleyin veya kaldırın. Fonksiyonlu formüller DÜŞEYARA Arama tablosuna bir sütun eklerseniz veya kaldırırsanız çalışmayı durdurun veya hatalı değerler döndürün. İşlev için DÜŞEYARA eklenen veya kaldırılan herhangi bir sütun, sözdizimi nedeniyle formülün sonucunu değiştirecektir. DÜŞEYARA veri çıkarmak istediğiniz aralığın tamamını ve belirli sütun numarasını belirtmenizi gerektirir.

Örneğin bir masanız varsa A1:C10 ve bir sütundan veri almak istiyorsunuz B, ardından değeri ayarlamanız gerekir 2 tartışma için sütun_index_num(sütun_numarası) işlevler DÜŞEYARA, bunun gibi:

DÜŞEYARA("arama değeri",A1:C10,2)
=DÜŞEYARA("aranan değer";A1:C10;2)

Daha sonra sütunların arasına yeni bir sütun eklerseniz A Ve B, o zaman argümanın değerinin şu şekilde değiştirilmesi gerekecektir: 2 Açık 3 aksi takdirde formül, yeni eklenen sütunun sonucunu döndürür.

Kullanma ARAMAK/DİZİNİstenilen değeri içeren sütun doğrudan tanımlandığından, sonucu bozmadan incelenen aralığa sütunları kaldırabilir veya ekleyebilirsiniz. Gerçekten de öyle büyük avantajözellikle büyük miktarda veriyle çalışmanız gerektiğinde. Kullandığınız her işlevi düzeltme endişesi duymadan sütun ekleyip kaldırabilirsiniz. DÜŞEYARA.

3. Aranan değerin boyutunda herhangi bir sınırlama yoktur. Kullanma DÜŞEYARA aranan değerin uzunluğunun 255 karakterle sınırlı olduğunu unutmayın, aksi takdirde hata alma riskiyle karşı karşıya kalırsınız #DEĞER!(#DEĞER!). Dolayısıyla, eğer tablo uzun satırlar içeriyorsa uygulanabilir tek çözüm DİZİN/ARAMAK.

Bu formülü kullandığınızı varsayalım. DÜŞEYARA, hücrelerde arama yapan B5 ile D10 hücrede belirtilen değer A2:

DÜŞEYARA(A2;B5:D10;3;YANLIŞ)
=DÜŞEYARA(A2,B5:D10,3,YANLIŞ)

Hücredeki değer şu olursa formül çalışmaz: A2 255 karakterden uzun. Bunun yerine benzer bir formül kullanmanız gerekir. DİZİN/ARAMAK:

DİZİN(D5:D10,EŞLEŞTİRME(DOĞRU,DİZİN(B5:B10=A2,0),0))
=ENDEKS(D5:D10,EŞLEŞME(DOĞRU,ENDEKS(B5:B10=A2,0),0))

4. Daha Fazla yüksek hız iş. Küçük tablolarla çalışıyorsanız, Excel performansındaki fark büyük olasılıkla özellikle fark edilmeyecektir. en son sürümler. Binlerce satır ve yüzlerce arama formülü içeren büyük tablolarla çalışıyorsanız Excel, kullandığınızda çok daha hızlı çalışacaktır. ARAMAK Ve DİZİN yerine DÜŞEYARA. Genel olarak bu değiştirme hızı artırır Excel çalışması Açık 13% .

Etkilemek DÜŞEYARAÇalışma kitabının yüzlerce karmaşık dizi formülü içermesi durumunda Excel performansı özellikle fark edilir. DÜŞEYARA+TOPLAM. Gerçek şu ki, dizideki her bir değerin kontrol edilmesi ayrı bir işlev çağrısı gerektirir DÜŞEYARA. Bu nedenle bir dizi ne kadar çok değer içeriyorsa ve tablonuz ne kadar çok dizi formülü içeriyorsa Excel o kadar yavaş çalışır.

Öte yandan, fonksiyonları olan bir formül ARAMAK Ve DİZİN sadece bir arama yapar ve sonucu döndürür, benzer işleri fark edilir derecede daha hızlı gerçekleştirir.

INDEX ve MATCH - formül örnekleri

Artık fonksiyonları neden öğrenmeniz gerektiğini anladığınıza göre ARAMAK Ve DİZİNŞimdi işin eğlenceli kısmına geçelim ve teorik bilgileri pratiğe nasıl uygulayabileceğinizi görelim.

MATCH ve INDEX kullanılarak sol taraftan nasıl arama yapılır?

Herhangi bir ders kitabı DÜŞEYARA bu fonksiyonun sola bakamayacağını söylüyor. Onlar. baktığınız sütun arama aralığında en soldaki sütun değilse, buradan alma şansınız yoktur. DÜŞEYARA istenilen sonuç.

Fonksiyonlar ARAMAK Ve DİZİN Excel çok daha esnektir ve almak istediğiniz değerin bulunduğu sütunun nerede bulunduğunu umursamaz. Örneğin eyalet başkentleri ve nüfus tablosuna dönelim. Bu sefer formülü yazacağız ARAMAK/DİZİN Rusya'nın başkentinin (Moskova) nüfus açısından hangi yeri kapladığını gösterecek.

Aşağıdaki şekilde de görebileceğiniz gibi formül bu işi mükemmel bir şekilde yapıyor:

INDEX($A$2:$A$10,MATCH("Rusya",$B$2:$B$10,0))

Artık bu formülün nasıl çalıştığını anlamakta sorun yaşamazsınız:

• Öncelikle fonksiyonu kullanalım KİBRİT(MATCH), listede “Rusya”nın konumunu bulan:

  MATCH("Rusya",$B$2:$B$10,0))
  =KAÇINCI("Rusya",$B$2:$B$10,0))

• Ardından, işlevin aralığını ayarlayın DİZİN(INDEX) değerin çıkarılacağı yer. Bizim durumumuzda öyle A2:A10.
• Daha sonra her iki parçayı birleştirip formülü elde ederiz:

  INDEX($A$2:$A$10;MATCH("Rusya";$B$2:$B$10,0))
  =ENDEX($A$2:$A$10,MATCH("Rusya",$B$2:$B$10,0))

İpucu: Doğru çözüm her zaman mutlak referanslar kullanmaktır. DİZİN Ve ARAMAK Böylece formül diğer hücrelere kopyalanırken arama aralıkları kaybolmaz.

Excel'de INDEX ve MATCH kullanılarak yapılan hesaplamalar (ORTALAMA, MAX, MIN)

Diğer Excel işlevlerini iç içe yerleştirebilirsiniz. DİZİN Ve ARAMAKörneğin minimum, maksimum veya ortalama değere en yakın olanı bulmak için. Aşağıda tabloyla ilgili formüller için çeşitli seçenekler verilmiştir:

1. MAKS(MAKS.) Formül bir sütundaki maksimumu bulur D C aynı satır:

ENDEKS($C$2:$C$10,EŞLEŞTİRME(MAX($D$2:I$10),$D$2:D$10,0))
=ENDEX($C$2:$C$10,EŞLEŞTİRME(MAX($D$2:I$10),$D$2:D$10,0))

Sonuç: Pekin

2. dk.(MIN). Formül bir sütunda minimum değeri bulur D ve sütundaki değeri döndürür C aynı satır:

ENDEKS($C$2:$C$10,EŞLEŞTİRME(MIN($D$2:I$10),$D$2:D$10,0))
=ENDEX($C$2:$C$10,EŞLEŞME(MIN($D$2:I$10),$D$2:D$10,0))

Sonuç: Lima

3. ORTALAMA(ORTALAMA). Formül bir aralığın ortalamasını hesaplar D2:D10, ardından kendisine en yakın olanı bulur ve sütundaki değeri döndürür C aynı satır:

ENDEKS($C$2:$C$10,EŞLEŞTİRME(ORTALAMA($D$2:D$10),$D$2:D$10,1))
=ENDEX($C$2:$C$10,MATCH(ORTALAMA($D$2:D$10),$D$2:D$10,1))

Sonuç: Moskova

ORTALAMA işlevini INDEX ve MATCH ile kullanırken hatırlanması gerekenler

Fonksiyonun kullanılması ORTALAMA ile kombinasyon halinde DİZİN Ve ARAMAK, fonksiyonun üçüncü argümanı olarak ARAMAKçoğu zaman belirtmeniz gerekecek 1 veya -1 Görüntülediğiniz aralığın ortalamaya eşit bir değer içerdiğinden emin değilseniz. Eğer böyle bir değerin varlığından eminseniz 0 Tam bir eşleşme bulmak için.

• Eğer belirtirseniz 1 , arama sütunundaki değerler artan sırada sıralanmalıdır ve formül, ortalamadan küçük veya ortalamaya eşit maksimum değeri döndürecektir.
• Eğer belirtirseniz -1 arama sütunundaki değerler azalan sırada sıralanmalıdır ve ortalamadan büyük veya ortalamaya eşit olan minimum değer döndürülecektir.

Örneğimizde sütundaki değerler D artan sırada sıralandığından harmanlama türünü kullanırız 1 . Formül DİZİN/ARAMAPOZ Moskova şehrinin nüfusu ortalama değere (12.269.006) en yakın olan şehir olduğundan “Moskova” değerini döndürür.

Bilinen bir satır ve sütunda arama yapmak için INDEX ve MATCH nasıl kullanılır?

Bu formül iki boyutlu aramaya eşdeğerdir DÜŞEYARA ve belirli bir satır ve sütunun kesişimindeki değeri bulmanızı sağlar.

Bu örnekte formül DİZİN/ARAMAK Bu derste tartıştığımız formüllere tek bir farkla çok benzeyecektir. Bil bakalım hangisi?

Hatırlayacağınız gibi işlevin sözdizimi DİZİN(INDEX) üç bağımsız değişkene izin verir:

INDEX(dizi,satır_sayısı,)
INDEX(dizi; satır_numarası; [sütun_numarası])

Ve bunu tahmin edenleri tebrik ediyorum!

Formül şablonunu yazarak başlayalım. Bunu yapmak için zaten aşina olduğumuz formülü alalım. DİZİN/ARAMAK ve ona başka bir işlev ekleyin ARAMAK, sütun numarasını döndürecektir.

INDEX(Tablonuz ,(MATCH(, aranacak sütun,0))(EŞLEŞTİR(, aranacak satır,0))
=INDEX(Tablonuz ,(MATCH( dikey arama değeri,aranacak sütun,0))(EŞLEŞME( yatay arama değeri,aranacak satır,0))

İki boyutlu arama için argümanda tablonun tamamını belirtmeniz gerektiğini unutmayın. sıralamak(dizi) işlevler DİZİN(ENDEKS).

Şimdi bu modeli pratikte deneyelim. Aşağıda dünyanın en kalabalık ülkelerinin bir listesini görüyorsunuz. Diyelim ki görevimiz 2015 yılında Amerika Birleşik Devletleri'nin nüfusunu bulmak.

Tamam, formülü yazalım. Excel'de iç içe geçmiş işlevler içeren karmaşık bir formül oluşturmam gerektiğinde, öncelikle her iç içe geçmiş işlevi ayrı ayrı yazıyorum.

O halde iki fonksiyonla başlayalım ARAMAK işlevin satır ve sütun numaralarını döndürecek olan DİZİN:

• Sütun için EŞLEŞTİR– sütuna bakıyoruz B veya daha doğrusu aralıkta B2:B11, hücrede belirtilen değer H2(AMERİKA). Fonksiyon şöyle görünecek:

  KAÇINCI($H$2,$B$1:$B$11,0)
  =EŞLEŞTİR($H$2,$B$1:$B$11,0)

  4 , çünkü “ABD” sütundaki 4. liste öğesidir B(başlık dahil).

• dize için MATCH– hücre değerini arıyoruz H3(2015) doğrultusunda 1 yani hücrelerde A1:E1:

  KAÇINCI($H$3,$A$1:$E$1,0)
  =EŞLEŞTİR($H$3,$A$1:$E$1,0)

  Bu formülün sonucu şu şekilde olacaktır: 5 , çünkü “2015” 5. sütunda yer alıyor.

Şimdi bu formülleri fonksiyona ekliyoruz DİZİN ve işte:

ENDEKS($A$1:$E$11,EŞLEŞTİRME($H$2,$B$1:$B$11,0),EŞLEŞTİRME($H$3,$A$1:$E$1,0))
=ENDEX($A$1:$E$11,EŞLEŞTİRME($H$2,$B$1:$B$11,0),EŞLEŞME($H$3,$A$1:$E$1,0))

İşlevleri değiştirirseniz ARAMAK Döndürdükleri değerlere göre formül kolay ve anlaşılır hale gelecektir:

DİZİN($A$1:$E$11,4,5))
=ENDEX($A$1:$E$11,4,5))

Bu formül kesişimdeki değeri döndürür 4.çizgiler ve 5. aralıktaki sütun A1:E11 yani hücre değeri E4. Sadece? Evet!

INDEX ve MATCH ile çok kriterli arama

Eğitimde DÜŞEYARA fonksiyonu olan bir formül örneği gösterdik DÜŞEYARA birden çok kriter kullanarak arama yapmak için. Ancak bu çözümün önemli bir sınırlaması, yardımcı bir sütun ekleme ihtiyacıydı. İyi haber: formül DİZİN/ARAMAK yardımcı sütun oluşturmaya gerek kalmadan iki sütundaki değerler arasında arama yapabilirsiniz!

Bir sipariş listemiz olduğunu ve tutarı iki kritere göre bulmak istediğimizi varsayalım. alıcının adı(Müşteri) ve ürün(Ürün). Bir alıcının aynı anda birkaç farklı ürünü satın alabilmesi ve alıcıların adlarının sayfadaki tabloda yer alması, meseleyi daha da karmaşık hale getiriyor. Arama tablosu rastgele sırayla düzenlenmiştir.

İşte formül DİZİN/ARAMAK sorunu çözer:

(=INDEX("Arama tablosu"!$A$2:$C$13,MATCH(1,(A2="Arama tablosu"!$A$2:$A$13)*
(B2="Arama tablosu"!$B$2:$B$13),0),3))
(=INDEX("Arama tablosu"!$A$2:$C$13;MATCH(1,(A2="Arama tablosu"!$A$2:$A$13)*
(B2="Arama tablosu"!$B$2:$B$13);0);3))

Bu formül daha önce tartıştığımız diğer formüllerden daha karmaşıktır ancak işlevlere ilişkin bilgilerle donatılmıştır. DİZİN Ve ARAMAK Onu yeneceksin. En zor kısım fonksiyondur ARAMAKÖncelikle bunun açıklanması gerektiğini düşünüyorum.

MATCH(1,(A2=Arama tablosu"!$A$2:$A$13),0)*(B2=Arama tablosu"!$B$2:$B$13)
MATCH(1;(A2=Arama tablosu"!$A$2:$A$13);0)*(B2=Arama tablosu"!$B$2:$B$13)

Yukarıda gösterilen formülde aradığımız değer 1 ve arama dizisi çarpmanın sonucudur. Peki neyi çarpmamız gerekiyor ve neden? Her şeye sırayla bakalım:

• Sütundaki ilk değeri alın A(Müşteri) sayfada Ana tablo ve bunu sayfadaki tabloda yer alan tüm müşteri adlarıyla karşılaştırın Arama tablosu(A2:A13).
• Bir eşleşme bulunursa denklem geri döner 1 (DOĞRU) ve değilse - 0 (YALAN).
• Daha sonra aynı işlemi sütun değerleri için de yapıyoruz B(Ürün).
• Daha sonra elde edilen sonuçları (1 ve 0) çarpıyoruz. Yalnızca her iki sütunda da eşleşmeler bulunursa (yani her iki kriter de doğruysa), şunları alırsınız: 1 . Her iki kriter de yanlışsa veya yalnızca biri karşılanıyorsa, alacaksınız 0 .

Şimdi neden sorduğumuzu anlıyorsun 1 , istenen değer nedir? İşlevin doğru olduğu doğrudur ARAMAK pozisyonu yalnızca her iki kriter de karşılandığında döndürdü.

Lütfen aklınızda bulundurun: Bu durumda fonksiyonun isteğe bağlı üçüncü argümanını kullanmanız gerekir. DİZİN. Gerekli çünkü ilk argümanda tablonun tamamını belirtiriz ve fonksiyona değeri hangi sütundan alacağını söylemeliyiz. Bizim durumumuzda bu sütun C(Toplam) ve böylece girdik 3 .

Ve son olarak, çünkü dizideki her hücreyi kontrol etmemiz gerekiyor, bu formülün bir dizi formülü olması gerekiyor. Bunu içine aldığı küme parantezlerinden görebilirsiniz. Formülü girmeyi tamamladığınızda, tıklamayı unutmayın. Ctrl+Üst Karakter+Enter.

Her şey doğru yapılırsa aşağıdaki şekildeki gibi sonuç alırsınız:

INDEX ve MATCH, Excel'de EĞERHATA ile birleştirildi

Muhtemelen zaten (birden fazla kez) fark ettiğiniz gibi, yanlış bir değer girerseniz, örneğin görüntülenen dizide olmayan bir değer girerseniz, formül DİZİN/ARAMAK bir hata bildiriyor #Yok(#Yok) veya #DEĞER!(#DEĞER!). Böyle bir mesajı daha anlaşılır bir şeyle değiştirmek istiyorsanız, formülü ekleyebilirsiniz. DİZİN Ve ARAMAK bir fonksiyona EĞER HATA.

İşlev sözdizimi EĞER HATAçok basit:

EĞERHATA(değer,eğer_hataysa_değer)
EĞERHATA(değer,eğer_hataysa_değer)

Tartışma nerede değer(değer) bir hata için kontrol edilen değerdir (bizim durumumuzda formülün sonucudur) DİZİN/ARAMAK); ve argüman değer_if_error(eğer_hataysa_değer), formülün bir hata vermesi durumunda döndürülecek değerdir.

Örneğin, bir fonksiyona ekleyebilirsiniz EĞER HATA bunun gibi:

EĞERHATA(ENDEKS($A$1:$E$11,EŞLEŞTİRME($G$2,$B$1:$B$11,0),EŞLEŞME($G$3,$A$1:$E$1,0)),
"Eşleşme bulunamadı. Tekrar deneyin!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 : $E$1;0));
"Eşleşme bulunamadı. Tekrar deneyin!")

Ve şimdi, birisi yanlış bir değer girerse formül şu sonucu verecektir:

Hata durumunda hücreyi boş bırakmayı tercih ederseniz, fonksiyonun ikinci argümanının değeri olarak tırnak (“”) kullanabilirsiniz. EĞER HATA. Bunun gibi:

IFERROR(INDEX(dizi,MATCH(aranan_değer,aranan_dizi,0),"")
IFERROR(INDEX(dizi,MATCH(aranan_değer, aranan_dizi,0),"")

Umarım bu ders kitabında açıklanan en az bir formülü faydalı bulmuşsunuzdur. Bu dersteki bilgiler arasında uygun bir çözüm bulamadığınız başka arama sorunlarıyla karşılaştıysanız, sorununuzu yorumlarda anlatmaktan çekinmeyin, hep birlikte çözmeye çalışacağız.

Diyelim ki raporunuz birçok sütunda çok fazla veri içeren bir tablo içeriyor. Bu tür tabloların görsel analizini yapmak son derece zordur. Raporla çalışmanın görevlerinden biri de belirli bir aya ilişkin satır ve sütun başlıklarına ilişkin verileri analiz etmektir. İlk bakışta bu çok basit bir görev gibi görünse de tek bir işlemle çözülemez. standart fonksiyon. Evet, elbette şu aracı kullanabilirsiniz: “HOME” - “Düzenleme” - “Bul” CTRL + F, değer arama penceresini açmak için Excel sayfası. Veya tablo için koşullu biçimlendirme kuralı oluşturun. Ancak o zaman elde edilen sonuçlarla daha fazla hesaplama yapmak imkansız olacaktır. Bu nedenle uygun formülü oluşturmak ve doğru şekilde uygulamak gerekir.

Excel dizisinde bir değer bulma

Sorunun çözümü şuna benziyor:

• B1 hücresine ilgilendiğimiz verileri gireceğiz;
• B2 hücresi, B1 hücresinin değerini içeren sütun başlığını görüntüler
• B3 hücresi, B1 hücresinin değerini içeren satırın adını görüntüler.

Aslında Excel'de koordinat araması yapmanız gerekiyor. Bu ne için? Çoğu zaman tablo koordinatlarını değere göre almamız gerekir. Biraz ters matris analizine benziyor. Kısaca spesifik bir örnek buna benzer. Rakamlarla belirlenen hedef, başlangıç ​​değeridir; bu hedefe kimin ve ne zaman en yakın olduğunu belirlemeniz gerekir. Örneğin, aşağıdaki şekilde gösterildiği gibi üç çeyrekte satılan malların sayısına ilişkin bir rapor içeren basit bir veri matrisi kullanıyoruz. Tüm sayısal göstergelerin eşleşmesi önemlidir. Manuel olarak oluşturup doldurmak istemiyorsanız Excel elektronik tablosu sıfırdan, ardından makalenin sonunda hazır bir örnek indirebilirsiniz.

Değişen karmaşıklıktaki çözüm seçeneklerini ve makalenin sonunda nihai sonucu sırayla ele alacağız.

Excel sütununda değer bulma

Öncelikle tablo sütun başlıklarını değere göre nasıl alacağımızı öğrenelim. Bunu yapmak için şu adımları izleyin:

1. B1 hücresine, tablo 5277'den alınan değeri girin ve giriş alanını okunabilir hale getirmek için arka planını mavi renkle vurgulayın (daha sonra yeni değerleri denemek için B1 hücresine diğer sayıları gireceğiz).
2. C2 hücresine, bu değeri içeren tablo sütununun başlığını elde etmek için formülü girin:
3. Formülü girdikten sonra, formülün bir dizide yürütülmesi gerektiğinden onaylamak için CTRL+SHIFT+Enter kısayol tuş kombinasyonuna basın. Her şey doğru yapılırsa formül çubuğunun kenarlarında küme parantezleri ( ) görünecektir.

Excel dizesinde bir değer bulma

Şimdi aynı değerin (5277) satır numarasını alıyoruz. Bunu yapmak için C3 hücresine aşağıdaki formülü girin:

Onaylamak için formülü girdikten sonra CTRL+SHIFT+Enter tuş kombinasyonuna tekrar basın ve sonucu alın:

Formül 9 sayısını döndürdü; karşılık gelen tablo değerini kullanarak sayfa satırı başlığını buldu. Sonuç olarak D9 değerinin tam adresine sahip oluyoruz.



Bir tablonun sütun başlığı ve satır başlığı nasıl alınır?

Şimdi tüm sayfanın değil, mevcut tablonun koordinatlarını değere göre nasıl elde edeceğimizi öğrenelim. Kısaca, başlıkları elde etmek için D9 yerine 5277 değerine göre bulmamız gerekiyor:

• tablo sütunu için – Mart;
• satır için – Ürün4.

Bu sorunu çözmek için C2 ve C3 hücrelerinde halihazırda elde edilen değerleri içeren bir formül kullanacağız. Bunu yapmak için şunu yapıyoruz:

Sonuç olarak, tablonun iç koordinatları Mart ayı değerine göre elde edilir; Ürün 4:

Excel aralığında aynı değerleri bulma

Tablo değerleri arasında kopyaların varlığını kontrol etmek için kopyaların varlığı hakkında bizi bilgilendirebilecek ve sayısını sayabilecek bir formül oluşturacağız. Bunu yapmak için formülü E2 hücresine girin:

Ayrıca tablo bölümünün aralığı için koşullu biçimlendirme kuralı oluşturacağız:

Gördüğünüz gibi, kopyalar varsa, başlık formülü, başlığı ilk kopyadan yatay olarak (soldan sağa) alır. Ve bir satırın adını (sayısını) elde etmek için kullanılan formül, sayıyı ilk kopyadan dikey olarak (yukarıdan aşağıya) alır. Düzeltmek için bu karar 2 yol var:

Bu durumda formüllerden birini veya diğerini değiştiririz, ancak ikisini aynı anda değiştirmeyiz. Eski formülün C3 hücresinde kalması gerektiğini hatırlatmakta fayda var:

Burada ilk kopyanın dikey koordinatları doğru bir şekilde görüntüleniyor (yukarıdan aşağıya) - sayfa ve Ağustos için I7; Tablo için öğe2. Bu seçeneği bir sonraki son örneğe bırakalım.

Excel aralığında en yakın değeri bulma

Bu tablo hâlâ mükemmel değil. Sonuçta, analiz ederken tam olarak tüm değerlerini bilmeniz gerekir. Formül, tabloda B1 hücresine girilen sayıyı bulamazsa hata döndürülür - #DEĞER! Tabloda orijinal sayı yoksa tablonun içerdiği en yakın değerin seçilmesi formül açısından ideal olacaktır. Tabloları analiz etmek için böyle bir program oluşturmak için F1 hücresine yeni bir formül girin:

Bundan sonra diğer tüm formüllerde B1 yerine bağlantıyı değiştirin F1 olmalıdır! Ayrıca bağlantıyı koşullu biçimlendirmede de değiştirmeniz gerekir. Şunu seçin: “GİRİŞ” - “Stiller” - “Koşullu Biçimlendirme” - “Kuralları Yönet” - “Kural Düzenle”. Ve burada parametrelerde B1 yerine F1'i belirtin. Programın çalışmasını kontrol etmek için B1 hücresine tabloda olmayan bir sayı girin, örneğin: 8000. Bu, nihai sonuca yol açacaktır:

Artık herhangi bir başlangıç ​​​​değerini girebilirsiniz; programın kendisi tablonun içerdiği en yakın sayıyı seçecektir. Daha sonra geçerli değere ilişkin sütun başlığını ve satır adını görüntüler. Örneğin 5000 sayısını girerseniz yeni bir sonuç elde ederiz:

Excel programımız 4965'in orijinal değere - 5000'e en yakın değerini buldu. Böyle bir program, iş planlamasında, hedef belirlemede, rasyonel bir çözüm bulmada vb. çeşitli analitik sorunları otomatik olarak çözmek için yararlı olabilir. Ortaya çıkan satırlar ve sütunlar, yeni Excel formüllerini kullanarak bu tür raporların hesaplama yeteneklerini daha da genişletmenize olanak tanır.

Çoğu zaman, Excel'de verilerle çalışırken, bilgilerin bir kısmını seçerek silmeniz veya gizlemeniz gerekir; bunlar çoğunlukla belirli kelimeleri, harfleri, sayıları, sembolleri veya bunların kombinasyonlarını içeren veya içermeyen satırlar veya sütunlardır. Bunlar bu konuda yardımcı olur standart araçlar Arama, filtre ve gelişmiş filtre gibi Excel. Bu araçlar sorunu çözmeye yetmiyorsa VBA imdada yetişiyor.

Excel dizelerinde verilen kelimeleri nasıl bulabilirim? Excel'de kelimeleri, harfleri, sayıları ve sembolleri bulun

Belirli bir kelimeyi, harfi, sayıyı, sembolü veya bunların birleşimini aramanın en basit yolu standart aramadır. Tüm arama parametreleri, ana menüden çağrılabilen “Bul ve Değiştir” iletişim kutusuna veya “Ctrl+f” kısayol tuşu kombinasyonu (burada f, ilk harftir) kullanılarak girilir. İngilizce kelime bul - bul). Normal aramaya ek olarak değiştirmeli arama da yapabilirsiniz.

Excel'de belirli bir sözcüğü, harfi, sayıyı veya simgeyi bulmak için filtreleri kullanma

Kullanıcı tanımlı bilgileri içeren satırları seçmek için filtrelemeyi kullanabilirsiniz. Örneğin Excel 2007 ve sonraki sürümlerde, normal metin filtresine ek olarak, hücre dolgu rengine ve yazı tipi rengine göre bir filtre vardır. Metin filtresi "eşittir...", "eşit değildir...", "şununla başlar...", "şununla biter...", "içerir...", gibi koşulları kullanmanıza olanak tanır. “içermiyor…”. Gerekli tüm satırlar filtrelendikten sonra, satırları silmek de dahil olmak üzere bunlarla her türlü işlemi gerçekleştirebilirsiniz.

VBA Like operatörünü kullanarak Excel'deki belirli satırları programlı olarak nasıl bulabilir ve silebilirim?

Çeşitli nedenlerden dolayı standart Excel araçları belirli sorunları çözmek için her zaman uygun değildir. Aşağıda, kullanılan aralığın hücrelerinde şablon tarafından belirtilen metni bulmanızı ve belirtilen metni içeren hücreyi içeren aktif çalışma sayfasının tüm satırını silmenizi sağlayan bir makronun program kodu bulunmaktadır. Arama metni, kullanılarak "Şablon" değişkenine atanır. desene uyacak özel karakterler .

Sub Sil_Strok_Po_Shablonu() Dim r As Long, FirstRow As Long, LastRow As Long Dim Bölge As Range, iRow As Range, Cell As Range Dim Template As String Template = "arama metnini buraya girin" Set Region = ActiveSheet.UsedRange FirstRow = Bölge Satır LastRow = Region.Row - 1 + Region.Rows.Count For r = LastRow'dan FirstRow'a Adım -1 iRow = Region.Rows(r - FirstRow + 1) iRow.Cells'deki Her Hücre İçin Ayarlayın Hücre Şablon Gibiyse Sonra Satırlar ( r).Sonraki Hücrede Sonu Sil Sonraki r Son Sub

Bu makro, gerekli ifadeleri bulmak için bulanık aramayı ve dizeleri bir modelle karşılaştırmanıza olanak tanıyan VBA karşılaştırma işleci Like'ı kullanır. Dizeleri karşılaştırırken, bu operatör büyük ve küçük harfler arasında ayrım yapar ve karşılaştırmanın sonucu talimata bağlıdır Seçenek Karşılaştır .

Benzer sorunları çözmek için VBA işlevleri Instr ve Find da kullanılabilir.

Benzer eylemleri gerçekleştirir Excel eklentisi kullanımı aranan metni girmenize olanak tanır. özel karakterler iletişim kutusunda farklı arama alanlarını belirtin.

İyi günler sevgili Habro sakinleri!

Zaman zaman bazılarımız (ve belki de bazılarımızdan daha fazlası), ev bütçesini derlemek ve analiz etmekten iş, ders çalışma vb. ile ilgili herhangi bir hesaplamaya kadar küçük miktarlardaki verileri işleme görevleriyle uğraşmak zorunda kalıyoruz. Belki de bunun için en uygun araç Microsoft Excel(veya belki başka analoglar, ancak daha az yaygındırlar).

Arama bana benzer bir konu hakkında Habré hakkında yalnızca bir makale verdi: “Google SpreadSheet'te formülleri kullanan Talmud”. verir iyi açıklama excel'de çalışmak için temel şeyler (her ne kadar excel'in kendisi ile %100 ilgili olmasa da).

Böylece, belirli bir istek/görev havuzu biriktirdikten sonra bunları yazma ve teklif etme fikri ortaya çıktı. olası çözümler(hepsi mümkün olmasa da, hızlı bir şekilde sonuç veriyor).

Kullanıcıların en sık karşılaştığı sorunların çözümünden bahsedeceğiz.

Çözümlerin açıklaması şu şekilde yapılandırılmıştır: giderek daha karmaşık hale gelen ilk görevi içeren bir vaka verilir ve her adım için açıklamalarla birlikte ayrıntılı bir çözüm verilir. Fonksiyonların isimleri Rusça olarak verilecek, ancak Rusça orijinal isimleri ilk geçtiği yerde parantez içinde verilecektir. İngilizce(çünkü deneyimlere göre, kullanıcıların büyük çoğunluğunda Rusça sürümü yüklüdür).

Durum_1: Mantıksal işlevler ve eşleştirme işlevleri
“Bir tabloda bir değerler dizim var ve belirli bir koşul/koşullar dizisi karşılandığında belirli bir değerin görüntülenmesi gerekiyor” (c) Kullanıcı

Veriler genellikle tablo halinde sunulur:

Durum:

• "Miktar" sütunundaki değer 5'ten büyükse,
• daha sonra “Sonuç” sütununda “Sipariş gerekmiyor” değerini görüntülemeniz gerekir,

Mantıksal formülleri ifade eden ve formülde önceden yazdığımız değerleri çözümde üretebilen “IF” formülü bu konuda bize yardımcı olacaktır. Lütfen tüm metin değerlerinin tırnak işaretleri kullanılarak yazıldığını unutmayın.

Formül sözdizimi aşağıdaki gibidir:
IF(mantıksal_ifade, [doğruysa_değer], [yanlışsa_değer])

• Mantıksal ifade, DOĞRU veya YANLIŞ olarak değerlendirilen bir ifadedir.
• Value_if_true - mantıksal ifade doğruysa yazdırılan değer
• Yanlışsa_değer - mantıksal ifade yanlışsa yazdırılacak değer

Çözüm için formül sözdizimi:

=EĞER(C5>5, “Sipariş gerekli değil”, “Sipariş gerekli”)

Çıktıda şu sonucu alıyoruz:

Durumun daha karmaşık olduğu durumlar vardır, örneğin 2 veya daha fazla koşulun yerine getirilmesi:

• “Miktar” sütunundaki değer 5'ten büyük ve “Tip” sütunundaki değer “A” ise

Bu durumda artık kendimizi yalnızca “IF” formülünü kullanmakla sınırlayamayız; sözdizimine başka bir formül eklememiz gerekir. Ve bu başka bir mantıksal formül "VE" olacak.
Formül sözdizimi aşağıdaki gibidir:
VE(boolean_value1, [boolean_value2], ...)

• Boolean_value1-2 vb. - değerlendirilmesi DOĞRU veya YANLIŞ değeriyle sonuçlanan, test edilecek bir koşul

Sonucun D2 hücresine aktarılması:

=EĞER(VE(C2>5,B2=“A”),1,0)

Böylece 2 formülün birleşimini kullanarak sorunumuza çözüm buluyor ve sonuca ulaşıyoruz:

Görevi karmaşıklaştırmaya çalışalım - yeni bir durum:

• "Miktar" sütunundaki değer 10 ve "Tip" sütunundaki değer "A" ise
• veya Miktar sütunundaki değer 5'ten büyük veya eşittir ve Tür değeri B'dir
• o zaman “Sonuç” sütununda “1” değerini, aksi takdirde “0” değerini görüntülemeniz gerekir.

Çözüm sözdizimi aşağıdaki gibi olacaktır:
Sonucun D2 hücresine aktarılması:

=EĞER(VEYA(VE(C2=10,B2=“A”); VE(C2>=5,B2=“B”)),1,0)

Girişten de görebileceğiniz gibi, IF formülünde bir OR koşulu ve iki AND koşulu bulunur. 2. seviyenin koşullarından en az biri “DOĞRU” değerine sahipse “Sonuç” sütununda sonuç “1”, aksi halde “0” olacaktır.
Sonuç:

Şimdi bir sonraki duruma geçelim:
“Koşul” sütunundaki değere bağlı olarak şunu göstermesi gerektiğini düşünelim: belirli koşul“Sonuç” sütununda, değerler ile sonuç arasındaki yazışmalar aşağıdadır.
Durum:

• 1 = Bir
• 2 = B
• 3 = B
• 4 = G

"IF" işlevini kullanarak bir sorunu çözerken sözdizimi aşağıdaki gibi olacaktır:

=EĞER(A2=1,“A”, EĞER(A2=2,“B”, EĞER(A2=3,“C”, EĞER(A2=4,“D”,0))))

Sonuç:

Gördüğünüz gibi böyle bir formül yazmak çok kullanışlı ve zahmetli olmakla kalmıyor, aynı zamanda deneyimsiz bir kullanıcının hata durumunda onu düzenlemesi biraz zaman alabiliyor.
Bu yaklaşımın dezavantajı, az sayıda koşul için geçerli olmasıdır, çünkü hepsinin manuel olarak girilmesi ve formülümüzün büyük boyutlara "şişirilmesi" gerekir, ancak yaklaşım, değerlerin tam "her yerde bulunması" ile ayırt edilir. ve kullanım çok yönlülüğü.

Alternatif çözüm_1:
SEÇ formülünü kullanma
İşlev sözdizimi:
SELECT(indeks_numarası, değer1, [değer2], ...)

• Dizin_numarası - seçilen değer bağımsız değişkeninin numarası. Dizin numarası 1 ile 254 arasında bir sayı, bir formül veya 1 ile 254 arasında bir sayı içeren bir hücreye başvuru olmalıdır.
• Değer1, değer2,... - 1'den 254'e kadar değer bağımsız değişkenlerinden bir değer; burada "SEÇ" işlevi, dizin numarasını kullanarak gerçekleştirilecek değeri veya eylemi seçer. Bağımsız değişkenler sayılar, hücre başvuruları, belirli adlar, formüller, işlevler veya metin olabilir.

Kullanırken, belirtilen değerlere bağlı olarak koşulların sonuçlarını hemen giriyoruz.
Durum:

• 1 = Bir
• 2 = B
• 3 = B
• 4 = G

Formül sözdizimi:

=SEÇİM(A2, “A”, “B”, “C”, “D”)

Sonuç yukarıdaki IF fonksiyon zinciri çözümüne benzer.
Bu formülü uygularken aşağıdaki kısıtlamalar geçerlidir:
“A2” hücresine (indeks numarası) yalnızca sayılar girilebilir ve sonuç değerleri 1'den 254'e kadar artan sırada görüntülenecektir.
Başka bir deyişle, işlev yalnızca "A2" hücresinde 1'den 254'e kadar sayıları artan sırada içeriyorsa çalışır ve bu, bu formülü kullanırken belirli kısıtlamalar getirir.
Onlar. 5 sayısını belirtirken “G” değerinin görünmesini istiyorsak,

• 1 = Bir
• 2 = B
• 3 = B
• 5 = G

o zaman formül aşağıdaki sözdizimine sahip olacaktır:
Sonucun B2 hücresine aktarılması:

=SEÇİM(A31, “A”, “B”, “C”, “D”)

Gördüğünüz gibi formüldeki “4” değerini boş bırakıp, “G” sonucunu “5” seri numarasına aktarmamız gerekiyor.

Alternatif çözüm_2:
İşte en popülerlerden birine geliyoruz Excel işlevleri ustalığı herhangi bir ofis çalışanını otomatik olarak "deneyimli bir excel kullanıcısına" /alaycılığa/ dönüştürür.
Formül sözdizimi:
DÜŞEYARA(aranan_değer, tablo, sütun_numarası, [aralık_bak])

• Arama_değeri – işlev tarafından aranan değer.
• Tablo, veri içeren bir hücre aralığıdır. Arama bu hücrelerde gerçekleştirilecek. Değerler metin, sayısal veya boole olabilir.
• Sütun_numarası - “Tablo” bağımsız değişkeninde eşleşme olması durumunda değerin türetileceği sütunun numarası. Sütunların genel sayfa ızgarası (A.B,C,D, vb.) boyunca değil, "Tablo" bağımsız değişkeninde belirtilen dizi içinde sayıldığını anlamak önemlidir.
• Interval_lookup - fonksiyonun tam bir eşleşme mi yoksa yaklaşık bir eşleşme mi bulması gerektiğini belirler.

Önemli: DÜŞEYARA işlevi, aranan_değer Tablo bağımsız değişkeninde birden çok kez mevcutsa ve yalnızca ilk benzersiz kayıtta bir eşleşme ararsa farklı anlamlar, daha sonra "DÜŞEYARA" işlevi yalnızca İLK eşleşmeyi bulacaktır, diğer tüm eşleşmelerin sonuçları gösterilmeyecektir. "DÜŞEYARA" formülünün kullanımı, verilerle çalışmaya yönelik başka bir yaklaşımla, yani "dizinlerin oluşturulmasıyla" ilişkilidir. ”.
Yaklaşımın özü, koşulların ve karşılık gelen değerlerin yazıldığı ana diziden ayrı olarak, "Aranan_değer" argümanının belirli bir sonuca uygunluğunun bir "dizini" oluşturmaktır:

Daha sonra tablonun çalışma kısmına daha önce doldurulmuş referans kitabına bağlantı içeren bir formül yazılır. Onlar. Dizinde “D” sütununda “A” sütunundaki değer aranır ve eşleşme bulunduğunda “E” sütunundaki değer “B” sütununda görüntülenir.
Formül sözdizimi:
Sonucun B2 hücresine aktarılması:

Sonuç:

Şimdi bir tablodan diğerine veri çekmeniz gereken ve tabloların aynı olmadığı bir durumu hayal edin. Aşağıdaki örneğe bakın

Her iki tablonun “Ürün” sütunlarındaki satırların eşleşmediği görülüyor ancak bu “DÜŞEYARA” fonksiyonunun kullanılmasına engel değil.
Sonucun B2 hücresine aktarılması:

Ancak çözerken karşılaştığımız yeni sorun– Sağa yazdığımız formülü “B” sütunundan “E” sütununa “uzatırken” “sütun_numarası” argümanını manuel olarak değiştirmemiz gerekecek. Bu emek yoğun ve nankör bir görevdir, bu nedenle yardımımıza başka bir işlev gelir - “SÜTUN” (KOLON).
İşlev sözdizimi:
SÜTUN([bağlantı])

• Başvuru, sütun numarasını döndürmek istediğiniz bir hücre veya hücre aralığıdır.

Aşağıdaki gibi bir kayıt kullanırsanız:

daha sonra fonksiyon mevcut sütunun numarasını gösterecektir (formülün yazıldığı hücrede).
Sonuç, kullanacağımız ve aşağıdaki formülü elde edeceğimiz “DÜŞEYARA” işlevinde kullanılabilecek bir sayıdır:
Sonucun B2 hücresine aktarılması:

=DÜŞEYARA($A3,$H$3:$M$6, SÜTUN(),0)

"COLUMN" işlevi, dizindeki arama sütununun sayısını belirlemek için "Column_Number" bağımsız değişkeni tarafından kullanılacak geçerli sütunun numarasını belirleyecektir.
Alternatif olarak aşağıdaki yapıyı kullanabilirsiniz:

İstenilen sonucu elde etmek için "1" sayısı yerine herhangi bir sayıyı kullanabilirsiniz (ve yalnızca çıkarmakla kalmaz, aynı zamanda elde edilen değere de ekleyebilirsiniz), eğer sütundaki belirli bir hücreye atıfta bulunmak istemiyorsanız. ihtiyacımız olan sayı.
Ortaya çıkan sonuç:

Konuyu geliştirmeye ve durumu karmaşıklaştırmaya devam ediyoruz: Ürünlerle ilgili farklı verilere sahip iki dizinimiz olduğunu ve "Dizin" de ne tür bir dizinin belirtildiğine bağlı olarak tablodaki değerleri sonuçla birlikte görüntülememiz gerektiğini hayal edin. kolon
Durum:

• “Directory” sütununda 1 rakamı belirtiliyorsa belirtilen aya göre “Directory_1” tablosundan, rakam 2 ise “Directory_2” tablosundan veri çekilmelidir.

Hemen aklıma gelen çözüm şudur:

=EĞER($B3=1; DÜŞEYARA($A3,$G$3:$I$6; SÜTUN()-1,0); DÜŞEYARA($A3,$K$3:$M$6; SÜTUN()-1;0 ))

Artıları: dizinin adı herhangi bir şey olabilir (metin, sayılar ve bunların kombinasyonu), dezavantajlar - 3'ten fazla seçenek varsa pek uymuyor.
Dizin numaraları her zaman sayılardan oluşuyorsa aşağıdaki çözümü kullanmak mantıklı olacaktır:
Sonucun C3 hücresine aktarılması:

=DÜŞEYARA($A3, SEÇ($B3,$G$3:$I$6,$K$3:$M$6), SÜTUN()-1,0)

Artıları: formül 254'e kadar dizin adı içerebilir, dezavantajları - adları kesinlikle sayısal olmalıdır.
SELECT işlevini kullanan formülün sonucu:

Bonus: DÜŞEYARA, "arama_değeri" bağımsız değişkenindeki iki veya daha fazla özelliğe dayalıdır.
Durum:

• Her zaman olduğu gibi tablo biçiminde bir veri dizimiz olduğunu (eğer değilse verileri ona getiririz), belirli özelliklere göre diziden değerler almamız ve bunları başka bir tablo biçiminde yerleştirmemiz gerektiğini hayal edelim. .

Her iki tablo da aşağıda gösterilmektedir:

Tablo formlarından da görülebileceği gibi, her ürünün yalnızca bir adı yoktur (ki bu benzersiz değildir), aynı zamanda belirli bir sınıfa aittir ve kendi paketleme seçeneğine de sahiptir.
Ad, sınıf ve paketleme kombinasyonunu kullanarak bunun için yeni bir karakteristik oluşturabiliriz; verilerin bulunduğu tabloda aşağıdaki formülü kullanarak doldurduğumuz ek bir "Ek karakteristik" sütunu oluştururuz:

"&" sembolünü kullanarak üç özelliği bir araya getiriyoruz (kelimeler arasındaki ayırıcı herhangi bir şey olabilir veya hiç olmayabilir, asıl önemli olan arama için benzer bir kural kullanmaktır)
Formülün bir analogu “BİRLEŞTİR” işlevi olabilir, bu durumda şöyle görünecektir:

=BİRLEŞTİR(H3;"_";I3;"_";J3)

Veri tablosundaki her kayıt için ek bir öznitelik oluşturulduktan sonra, bu öznitelik için şöyle görünecek bir arama fonksiyonu yazmaya devam ediyoruz:
Sonucun D3 hücresine aktarılması:

=EĞERHATA(DÜŞEYARA(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

“DÜŞEYARA” işlevinde, “arama_değeri” argümanı olarak, üç özelliğin (isim_sınıf_paketleme) aynı kombinasyonunu kullanırız, ancak onu doldurmak için zaten tabloda alıyoruz ve doğrudan argümana giriyoruz (alternatif olarak, bağımsız değişkenin değerini tablodaki ek bir sütunda doldurun, ancak bu eylem gereksiz olacaktır).
İstenilen değer bulunamazsa "EĞERHATA" işlevini kullanmanın gerekli olduğunu ve "DÜŞEYARA" işlevinin bize "#YOK" değerini vereceğini hatırlatırım (bununla ilgili daha fazla bilgi aşağıdadır).
Sonuç aşağıdaki resimdedir:

Bu teknik daha fazla sayıda özellik için kullanılabilir; tek koşul, ortaya çıkan kombinasyonların benzersizliğidir; bu karşılanmazsa sonuç yanlış olacaktır.

Vaka_3 Bir dizide değer ararken veya DÜŞEYARA bize yardımcı olamadığında

Hücre dizisinin ihtiyacımız olan değerleri içerip içermediğini anlamamız gereken bir durumu ele alalım.
Görev:

• “Arama Durumu” sütunu bir değer içerir ve bu değerin “Arama Dizisi” sütununda mevcut olup olmadığını belirlemeniz gerekir

Görsel olarak her şey şöyle görünür:

Gördüğümüz gibi “DÜŞEYARA” işlevi burada güçsüz çünkü Tam bir eşleşme değil, ihtiyacımız olan değerin hücrede bulunmasını arıyoruz.
Sorunu çözmek için çeşitli işlevlerin bir kombinasyonunu kullanmak gerekir:
"EĞER"
"HATA VARSA"
"DAHA DÜŞÜK"
"BULMAK"

Herkes için sırasıyla “EĞER”den daha önce bahsetmiştik, o yüzden “EĞERHATA” fonksiyonuna geçelim.

EĞERHATA(değer; hata_değeri)

• Değer, hatalara karşı kontrol edilen bağımsız değişkendir.
• Hata_değeri - formül hesaplanırken bir hata olması durumunda döndürülen değer. Aşağıdaki hata türleri mümkündür: #YOK, #DEĞER!, #REF!, #BÖL/0!, #SAYI!, #AD? ve #BOŞ!.

Önemli: Bu formül, bilgi dizileri ve referans kitapları ile çalışırken neredeyse her zaman gereklidir, çünkü Çoğu zaman aradığınız değerin dizinde olmadığı görülür ve bu durumda işlev bir hata döndürür. Bir hücrede bir hata görüntüleniyorsa ve hücre örneğin bir hesaplamaya dahil oluyorsa, o zaman bu da bir hatayla birlikte ortaya çıkacaktır. Ayrıca formülün hata döndürdüğü hücrelere farklı değerler atanabilir, bu da onların istatistiksel işlemlerini kolaylaştırır. Ayrıca, bir hata durumunda, dizilerle çalışırken çok kullanışlı olan ve oldukça dallanmış koşulları hesaba katarak formüller oluşturmanıza olanak tanıyan diğer işlevleri de gerçekleştirebilirsiniz.

"DAHA DÜŞÜK"

• Metin - metin küçük harfe dönüştürülür.

Önemli: "LOWER" işlevi harf olmayan karakterlerin yerine geçmez.
Formüldeki rolü: "BUL" işlevi metnin büyük/küçük harflerini arayıp dikkate aldığından, tüm metnin aynı büyük/küçük harfe dönüştürülmesi gerekir, aksi takdirde "çay", "çay" vb.'ye eşit olmayacaktır. Bu, kayıt değerinin değerlerin aranması ve seçilmesi için bir koşul olmaması durumunda geçerlidir, aksi takdirde "LOWER" formülü kullanılamaz, dolayısıyla arama daha doğru olacaktır.

Şimdi FIND fonksiyonunun sözdizimine daha yakından bakalım.

BUL(arama_metni; görüntülenen_metin; [başlangıç_konumu])

• Arama_metni - bulunması gereken metin.
• Arama_metni - aranan metni bulmak istediğiniz metin.
• Start_position - aramanın başlatılacağı işaret. "Görünüm_metni" metnindeki ilk karakter 1 olarak numaralandırılır. Numara belirtilmezse varsayılan olarak 1 olur.

Çözüm formülünün sözdizimi şöyle görünecektir:
Sonucun B2 hücresine aktarılması:

=EĞER(EĞERHATA(BUL(SATIR(A2), SATIR(E2),1),0)=0,“başarısız”, “bingo!”)

Formülün mantığını adım adım inceleyelim:

1. LOWER(A2) – A2 hücresindeki Arama_Text bağımsız değişkenini küçük harfli metne dönüştürür
2. FIND işlevi, LOWER(E2) işlevi tarafından yine küçük harfe dönüştürülen Search_Text dizisinde dönüştürülmüş Search_Text bağımsız değişkenini aramaya başlar.
3. İşlev bir eşleşme bulursa, ör. eşleşen kelimenin/değerin ilk karakterinin seri numarasını döndürür, “IF” formülündeki TRUE koşulu tetiklenir, çünkü ortaya çıkan değer sıfır değildir. Sonuç olarak, "Sonuç" sütununda "Bingo!" değeri görüntülenecektir.
4. Ancak işlev bir eşleşme bulamazsa; eşleşen kelimenin/değerin ilk karakterinin seri numarası belirtilmez ve değer yerine hata döndürülür, “EĞERHATA” formülünde yer alan koşul tetiklenir ve karşılık gelen “0”a eşit bir değer döndürülür. “IF” formülündeki YANLIŞ koşuluna göre, çünkü ortaya çıkan değer “0”dır. Sonuç olarak, "Sonuç" sütununda "başarısız" değeri görüntülenecektir.

Yukarıdaki şekilde görüldüğü gibi “LOW” ve “FIND” fonksiyonları sayesinde karakterlerin durumu ve hücre içindeki konumu ne olursa olsun istenilen değerleri buluyoruz ancak 5. satıra dikkat etmemiz gerekiyor. .
Arama terimi "111" olarak ayarlanmıştır, ancak arama dizisi "1111111 çerezler" değerini içerir, ancak formül "Bingo!" sonucunu döndürür. Bunun nedeni, "111" değerinin "1111111" değer dizisine dahil edilmesi ve bunun sonucunda bir eşleşme bulunmasıdır. Aksi takdirde bu durum işe yaramayacak.

Durum_4 Çeşitli koşullara bağlı olarak bir dizide bir değer aramak veya DÜŞEYARA'nın bize yardımcı olamayacağı durumlarda

İki boyutlu "Dizin" dizisindeki "Sonuçlu Tablo"dan çeşitli koşullara göre, yani "Ad" ve "Ay" değerine göre bir değer bulmanız gereken bir durumu hayal edelim.
Görevin tablo şekli şöyle görünecektir:

Durum:

• “Ad” ve “Ay” koşullarının çakışmasına göre verilerin sonuçla birlikte tabloya çekilmesi gerekmektedir.

Bu sorunu çözmek için “DİZİN” ve “ARAMA” işlevlerinin bir kombinasyonu uygundur.

INDEX işlevinin sözdizimi

INDEX(dizi; satır_numarası; [sütun_numarası])

• Dizi - arama koşulları eşleştiğinde değerlerin gösterileceği bir hücre aralığı.
• Dizi yalnızca bir satır veya bir sütun içeriyorsa sırasıyla satır_numarası veya sütun_numarası bağımsız değişkeni isteğe bağlıdır.
• Dizi birden fazla satır ve bir sütunu kaplıyorsa ve satır_numarası ve sütun_numarası bağımsız değişkenlerinden yalnızca biri verilmişse, INDEX işlevi dizi bağımsız değişkeninin tüm satırından veya tüm sütunundan oluşan bir dizi döndürür.
• Satır_numarası - dizide değer döndürmek istediğiniz satırın numarası.
• sütun_numarası - dizideki bir değer döndürmek istediğiniz sütunun numarası.

Yani fonksiyon, “Dizi” argümanında belirtilen diziden, “Satır_Numara” ve “Sütun_Numara” argümanlarında belirtilen koordinatların kesişimindeki değeri döndürür.

MATCH İşlev Sözdizimi

KAÇINCI(aranan_değer, aranan_dizi, [eşleşme_türü])

• Aranan_değer, aranan_dizi bağımsız değişkenindeki değerlerle eşleşen değerdir. Aranan_değer bağımsız değişkeni bir değer (sayı, metin veya Boolean) veya böyle bir değeri içeren bir hücreye başvuru olabilir.
• Bakılan_dizi - aramanın gerçekleştirildiği hücre aralığı.
• match_type isteğe bağlı bir argümandır. Sayı -1, 0 veya 1'dir.

MATCH işlevi arama yapar belirtilen eleman bir hücre aralığındadır ve o öğenin aralıktaki göreli konumunu döndürür.
“DİZİN” ve “ARAMA” işlevlerinin bir kombinasyonunu kullanmanın özü, “koordinat eksenleri” boyunca değerlerin koordinatlarını adlarına göre aramamızdır.
Y ekseni “Ad” sütunu, X ekseni ise “Aylar” satırı olacaktır.

Formülün bir kısmı:

KAÇINCI($A4,$I$4:$I$7,0)

Y ekseni boyunca sayıyı döndürür; bu durumda 1'e eşit olacaktır, çünkü "A" değeri aranan aralıkta mevcuttur ve bu aralıkta göreceli olarak "1" konumuna sahiptir.
formülün bir kısmı:

KAÇINCI(B$3,$J$3:$L$3,0)

#N/A değerini döndürür çünkü "1" değeri görüntülenen aralıkta değil.

Böylece “INDEX” fonksiyonunun “Array” argümanında arama yapmak için kullandığı (1; #N/A) noktasının koordinatlarını elde etmiş olduk.
B4 hücresinin tam olarak yazılmış işlevi şöyle görünecektir:

=ENDEX($J$4:$L$7, KAÇINCI($A4,$I$4:$I$7,0), KAÇINCI(B$3,$J$3:$L$3,0))

Temel olarak ihtiyacımız olan değerin koordinatlarını bilseydik fonksiyon şöyle görünürdü:

=ENDEX($J$4:$L$7,1,#N/A))

"Sütun_Numara" argümanı "#N/A" değerine sahip olduğundan, "B4" hücresinin sonucu buna karşılık gelecektir.
Ortaya çıkan sonuçtan da görülebileceği gibi sonucun bulunduğu tablodaki değerlerin tamamı referans kitabıyla eşleşmiyor ve bunun sonucunda tablodaki bazı değerlerin “#N/A” olarak görüntülendiğini görüyoruz. Bu da verilerin daha sonraki hesaplamalar için kullanılmasını zorlaştırır.
Sonuç:

Bu olumsuz etkiyi etkisiz hale getirmek için daha önce okuduğumuz “EĞERHATA” fonksiyonunu kullanıyoruz ve hata durumunda döndürülen değeri “0” ile değiştiriyoruz, ardından formül şöyle görünecektir:

=EĞERHATA(ENDEKS($J$4:$L$7, KAÇINCI($A4,$I$4:$I$7,0), KAÇINCI(B$3,$J$3:$L$3,0))),0)

Sonuç gösterimi:

Resimde görebileceğiniz gibi, “#N/A” değerleri artık sonuç tablosundaki değerleri kullanarak sonraki hesaplamalarımıza müdahale etmiyor.

Vaka_5 Sayı aralığında bir değer bulma

Belirli bir aralıkta yer alan sayılara belirli bir işaret vermemiz gerektiğini düşünelim.
Durum:
Ürünün maliyetine bağlı olarak belirli bir kategoriye atanmalıdır.
Değer aralıktaysa

• 0'dan 1000'e = A
• 1001'den 1500'e = B
• 1501'den 2000'e = B
• 2001'den 2500'e = G
• 2501'den fazla = D

LOOKUP işlevi bir satırdan, sütundan veya diziden bir değer döndürür. Fonksiyonun iki sözdizimsel formu vardır: vektör formu ve dizi formu.

LOOKUP(aranan_değer; aranan_vektör; [sonuç_vektörü])

• aranan_değer LOOKUP fonksiyonunun ilk vektörde aradığı değerdir. Aranan_değer bir sayı, metin, boolean, ad veya değer referansı olabilir.
• Watch_vector, bir satır veya bir sütundan oluşan bir aralıktır. Aranan_vektör bağımsız değişkenindeki değerler metin, sayı veya boolean olabilir.
• Aranan_vektör argümanındaki değerler artan sırada olmalıdır: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; aksi takdirde LOOKUP işlevi hatalı bir sonuç döndürebilir. Alttaki metin ve büyük harf eşdeğer kabul edilir.
• sonuç_vektörü bir satır veya sütundan oluşan bir aralıktır. Sonuç_vektörü, aranan_vektör ile aynı boyuta sahip olmalıdır.

=GÖRÜNTÜLE(E3,$A$3:$A$7,$B$3:$B$7)

"Görünüm_vektörü" ve "Sonuç_vektörü" bağımsız değişkenleri dizi biçiminde yazılabilir; bu durumda, bunları bir Excel sayfasında ayrı bir tabloda görüntülemeniz gerekmez.
Bu durumda fonksiyon şöyle görünecektir:
Sonucun B3 hücresine aktarılması:
=GÖRÜNÜM(E3;(0;1001;1501;2001;2501);("A", "B", "C", "D", "D"))

Vaka_6 Sayıların özelliklerine göre toplamı

Belirli özelliklere göre sayıları toplamak için üç farklı işlevi kullanabilirsiniz:
SUMIF – yalnızca bir özelliğe göre toplamlar
SUMIFS – birden fazla özelliğin toplamı
SUMproduct – birden fazla özelliğin toplamı
"TOPLA" formülü bir diziye yükseltildiğinde "TOPLA" ve dizi formülü işlevini kullanma seçeneği de vardır:
((=TOPLA(()*()))
ancak bu yaklaşım oldukça sakıncalıdır ve "TOPLAM" formülü tamamen işlevsellik kapsamına girmektedir.
Şimdi “TOPLAM” sözdizimi hakkında daha fazla ayrıntı için:

TOPLAÇARP(dizi1, [dizi2], [dizi3],...)

• Dizi1, bileşenlerinin çarpılması ve ardından sonuçların eklenmesi gereken ilk dizidir.
• Dizi2, dizi3… - 2'den 255'e kadar diziler; bunların bileşenlerinin çarpılması ve ardından sonuçların eklenmesi gerekir.

Durum:

• Belirli bir süre için her ürün için toplam gönderi maliyetini bulun:

Verilerin yer aldığı tablodan da görülebileceği gibi, maliyeti hesaplamak için fiyatı miktarla çarpmak ve ortaya çıkan değeri, seçim koşullarını uygulayarak sonucun bulunduğu tabloya aktarmak gerekir.
Ancak SUMPROIZ formülü bu tür hesaplamaların formül içerisinde yapılmasına olanak sağlar.
Sonucun B4 hücresine aktarılması:

=TOPLAÇARP(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Formüle parçalar halinde bakalım:
– sonuç tablosundaki “Ad” sütunundaki verilerle birlikte tablonun “Ad” sütunundaki seçim koşulunu ayarlayın

($K$3:$K$11>=B$3)*($K$3:$K$11 – zaman çerçevesine dayalı bir koşul belirledik; tarih, içinde bulunulan ayın ilk gününden büyük veya ona eşit, ancak bir sonraki ayın ilk gününden küçük. Benzer şekilde koşul, sonucun bulunduğu tabloda, dizi ise verilerin bulunduğu tabloda yer alır.
– tablodaki “Miktar” ve “Fiyat” sütunlarını verilerle çarpın.
Bu fonksiyonun şüphesiz avantajı kayıt koşullarının serbest sırasıdır; herhangi bir sırayla yazılabilirler, bu sonucu etkilemez.
Sonuç:

Şimdi durumu karmaşıklaştıralım ve "çerezler" adı için seçimin yalnızca "küçük" ve "büyük" sınıflarında gerçekleşmesi ve "yuvarlanması" adı için "reçelli" sınıfı dışındaki her şeyin seçilmesi gerekliliğini ekleyelim:

Sonucun B4 hücresine aktarılması:

=TOPLAÇARP(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Çerezleri seçme formülüne yeni bir koşul eklendi:

(($I$3:$I$11=“küçük”)+($I$3:$I$11=“büyük”))

– görebileceğiniz gibi, bir sütundaki iki veya daha fazla koşul “+” sembolü kullanılarak ve koşullar ek parantez içine alınarak ayrı bir gruba ayrılmıştır.
Zarla seçim formülüne yeni bir koşul da eklendi:

=TOPLAÇARP(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “reçelli”);($L$3:$L$11)*($K$3:$K$11))

Bu:

($I$3:$I$11<>"reçelli")

– aslında bu formülde, çerezlerle seçim yaparken olduğu gibi aynı şekilde bir seçim koşulu yazmak mümkündü, ancak o zaman formülde üç koşulu listelemeniz gerekirdi, bu durumda bir istisna yazmak daha kolaydır - “reçelli”ye eşit değil bunun için " değerini kullanıyoruz<>».
Genel olarak, özellik/sınıf grupları önceden biliniyorsa, tüm koşulları bir fonksiyona yazıp onu şişirmektense bunları bu gruplar halinde birleştirerek referans kitapları oluşturmak daha iyidir.
Sonuç:

Aslında çok daha uzun olabilecek kısa kılavuzumuzun sonuna geldik, ancak amaç yine de en yaygın durumlara bir çözüm sağlamaktı ve çözümü belirli durumlara açıklamak değil (fakat çok daha ilginç durumlara) ).
Umarım bu kılavuz birisine Excel kullanarak sorunları çözmede yardımcı olur, çünkü bu, çalışmamın boşuna olmadığı anlamına gelecektir!

Zaman ayırdığınız için teşekkürler!