Ofisin asıl amacı Excel programları– hesaplamalar yapmak. Bu programın belgesi (Kitap), sayılar, metinler veya formüllerle dolu uzun tablolar içeren birçok sayfa içerebilir. Otomatik hızlı arama içlerinde gerekli hücreleri bulmanızı sağlar.

Basit arama

Bir değeri aramak için Excel elektronik tablo, "Giriş" sekmesindeki "Bul ve Değiştir" aracının açılır listesini açmanız ve "Bul" öğesine tıklamanız gerekir. Aynı etki Ctrl + F klavye kısayolu kullanılarak da elde edilebilir.

En basit durumda, beliren "Bul ve Değiştir" penceresinde istediğiniz değeri girmeniz ve "Tümünü Bul" düğmesine tıklamanız gerekir.

Gördüğünüz gibi arama sonuçları iletişim kutusunun alt kısmında göründü. Bulunan değerler tabloda kırmızı renkle altı çizilmiştir. “Tümünü bul” yerine “Sonraki bul”a tıklarsanız, önce bu değere sahip ilk hücre aranacak, tekrar tıkladığınızda ikinci hücre aranacaktır.

Metin araması da aynı şekilde gerçekleştirilir. Bu durumda aranan metin arama çubuğuna yazılır.

Excel tablosunun tamamında veri veya metin aranmıyorsa öncelikle arama alanının seçilmesi gerekir.

gelişmiş Arama

3000 ila 3999 aralığındaki tüm değerleri bulmak istediğinizi varsayalım. Bu durumda arama çubuğuna 3??? yazarsınız. Joker karakter "?" diğerlerinin yerini alır.

Aramanın sonuçları analiz edildiğinde, programın doğru 9 sonucun yanı sıra kırmızıyla vurgulanmış beklenmedik sonuçlar da ürettiği belirtilebilir. Bir hücrede veya formülde 3 sayısının varlığıyla ilişkilidirler.

Yanlış olanları göz ardı ederek elde edilen sonuçların çoğundan memnun kalabilirsiniz. Ancak Excel 2010'daki arama işlevi çok daha doğru çalışabilir. İletişim kutusundaki Seçenekler aracı bu amaç içindir.

Kullanıcı, "Seçenekler"i tıklatarak gelişmiş arama yapma olanağına sahip olur. Öncelikle varsayılan olarak “Formüller” olarak ayarlanan “Arama Alanı” maddesine dikkat edelim.

Bu, bir değerin değil formülün bulunduğu hücreler de dahil olmak üzere aramanın gerçekleştirildiği anlamına gelir. İçlerinde 3 sayısının bulunması üç yanlış sonuç verdi. Arama kapsamı olarak "Değerler"i seçerseniz yalnızca veri arayacaksınız ve formül hücrelerine ilişkin hatalı sonuçlar ortadan kalkacaktır.

İlk satırda kalan tek yanlış sonuçtan kurtulmak için gelişmiş arama penceresinde “Hücrenin tamamı” öğesini seçmeniz gerekir. Bundan sonra arama sonucu %100 doğru olur.

Bu sonuca hemen “Tüm Hücre” öğesi seçilerek (“Arama Alanında” “Formül” değeri bırakılarak bile) ulaşılabilir.

Şimdi “Arama” öğesine dönelim.

Varsayılan "Sayfada" yerine "Çalışma Kitabında"yı seçerseniz, aradığınız hücre sayfasında olmanıza gerek yoktur. Ekran görüntüsü, kullanıcının aramayı boş sayfa 2'de başlattığını gösteriyor.

Gelişmiş arama penceresindeki bir sonraki öğe, iki anlamı olan “Görünüm”dür. Varsayılan "satırlara göre"dir; bu, hücrelerin satır satır tarandığı anlamına gelir. Farklı bir değerin (sütunlara göre) seçilmesi yalnızca arama yönünü ve sonuçların sırasını değiştirir.

Belgelerde arama yaparken Microsoft Excel, başka bir joker karakter de kullanabilirsiniz – “*”. Eğer dikkate alınırsa "?" herhangi bir karakter anlamına geliyorsa, “*” bir değil, herhangi bir sayıda karakterin yerine geçer. Aşağıda Louisiana için yapılan bir aramanın ekran görüntüsü bulunmaktadır.

Bazen arama yaparken karakterlerin durumunu dikkate almak gerekir. Louisiana kelimesi büyük harfle yazılırsa arama sonuçları değişmeyecektir. Ancak gelişmiş arama penceresinde “Büyük/küçük harf eşleştir” seçeneğini seçerseniz arama başarısız olur. Program Louisiana ve Louisiana kelimelerini farklı değerlendirecek ve doğal olarak ilkini bulamayacaktır.

Arama türleri

Eşleşme ara

Bazen bir tablodaki yinelenen değerleri tespit etmek gerekebilir. Eşleşmeleri aramak için öncelikle bir arama aralığı seçmeniz gerekir. Ardından, “Stiller” grubundaki aynı “Giriş” sekmesinde “ Koşullu biçimlendirme" Ardından, sırayla “Hücreleri vurgulama kuralları” ve “Yinelenen değerler” öğelerini seçin.

Sonuç aşağıdaki ekran görüntüsünde gösterilmektedir.

Gerekirse kullanıcı eşleşen hücrelerin görsel ekranının rengini değiştirebilir.

Filtrasyon

Bir diğer arama türü ise filtrelemedir. Kullanıcının B sütununda 3000 ile 4000 aralığında sayısal değerler bulmak istediğini varsayalım.

Gördüğünüz gibi yalnızca girilen koşulu karşılayan satırlar görüntüleniyor. Geri kalan her şey geçici olarak gizlendi. Başlangıç ​​durumuna dönmek için 2. adımı tekrarlayın.

Örnek olarak Excel 2010 kullanılarak çeşitli arama seçenekleri tartışıldı. Diğer sürümlerin Excel'inde nasıl arama yapılır? 2003 versiyonunda filtrelemeye geçişte farklılık bulunmaktadır. “Veri” menüsünde “Filtre”, “Oto Filtre”, “Durum” ve “Özel Otomatik Filtre” komutlarını sırasıyla seçmelisiniz.

Video: Excel tablosunda arama yapın

Büyük, bütün bir tablo veya bitişik hücre aralıklarında da olsa, tek bir tablo içinde arama yapmak, farklı bitişik olmayan aralıklara veya hatta ayrı sayfalara dağılmış parçalara bölünmüş birkaç tablodan kesinlikle daha kolaydır. Yapsan bile otomatik arama birden fazla masada aynı anda önemli engeller ortaya çıkabilir. Ancak tüm verileri tek bir tabloda düzenlemek zordur, hatta bazen neredeyse imkansızdır. Açık spesifik örnek Excel'de birden fazla tablo arasında eşzamanlı arama için doğru çözümü göstereceğiz.

Birden fazla aralıkta eşzamanlı arama

Görsel bir örnek olarak, bir sayfanın bitişik olmayan aralıklarında yer alan üç basit ayrı tablo oluşturalım:

20 adet ürün üretmek için gereken miktarı aramalısınız. Maalesef bu veriler farklı sütun ve satırlardadır. Bu nedenle öncelikle bu ürünlerin üretiminin ne kadar süreceğini kontrol etmeniz gerekiyor (ilk tablo).

Elde edilen verilere dayanarak hemen başka bir tabloda aramaya devam etmeli ve belirli bir üretim hacmine kaç işçinin dahil olması gerektiğini bulmalısınız. Elde edilen sonuç üçüncü tablodaki verilerle karşılaştırılmalıdır. Böylece, üç tablodaki tek bir arama işleminde gerekli maliyetleri (tutar) anında belirleyeceğiz.

Ortalama bir Excel kullanıcısı DÜŞEYARA gibi formül tabanlı işlevleri kullanarak bir çözüm arar. Ve 3 aşamada (her tablo için ayrı ayrı) arama yapacaktır. Özel bir formül kullanarak sadece 1 aşamada arama yaparak anında hazır bir sonuç elde edebileceğiniz ortaya çıktı. Bunun için:

1. E6 hücresine arama sorgusunun koşulu olan 20 değerini girin.
2. E7 hücresine aşağıdaki formülü girin:

20 adet üretim maliyeti. belirli bir ürün.

DÜŞEYARA içeren formül çeşitli tablolarda nasıl çalışır:

Bu formülün çalışma prensibi, ana DÜŞEYARA işlevine (birincisi) ilişkin tüm bağımsız değişkenlerin sıralı olarak aranmasına dayanmaktadır. İlk olarak, üçüncü DÜŞEYARA işlevi, E6 hücresi için değer olarak belirtilen ürünün 20 parçasını üretmek için gereken süreyi ilk tabloda arar (bu daha sonra gerekirse değiştirilebilir). Daha sonra ikinci DÜŞEYARA işlevi, ana işlevin ilk bağımsız değişkeninin değerini arar.

Üçüncü fonksiyonun aranması sonucunda ikinci fonksiyonun ilk argümanı olan 125 değeri elde edilir. Tüm parametreleri aldıktan sonra ikinci fonksiyon, üretim için gerekli işçi sayısını ikinci tabloda arar. Sonuç olarak, daha sonra ana işlev tarafından kullanılacak olan 5 değeri döndürülür. Formül, alınan tüm verilere dayanarak hesaplamanın nihai sonucunu verir. Yani belirli bir üründen 20 adet üretmek için gereken 1.750 dolar.

Bu prensibi kullanarak, DÜŞEYARA işlevine yönelik formülleri birkaç sayfadan kullanabilirsiniz.

İyi günler sevgili Habro sakinleri!

Zaman zaman bazılarımız (belki de bazılarımızdan daha fazla), derlemeden analize kadar küçük miktarlardaki verileri işleme göreviyle karşı karşıya kalıyoruz. ev bütçesi ve iş, çalışma vb. için herhangi bir hesaplamayla sonlandırmak. Belki de bunun için en uygun araç Microsoft Excel'dir (veya belki başka analoglardır, ancak bunlar daha az yaygındır).

Arama bana Habré hakkında benzer bir konuyla ilgili yalnızca bir makale verdi: "Google SpreadSheet'te formülleri kullanan Talmud". Excel'de çalışmaya ilişkin temel şeylerin iyi bir tanımını verir (her ne kadar Excel'in kendisi ile ilgili %100 olmasa da).

Böylece, belirli bir istek/görev havuzu biriktirdikten sonra bunları yazma ve teklif etme fikri ortaya çıktı. Muhtemel çö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. Fonksiyon adları Rusça olarak verilecek, ancak Rusça orijinal adları ilk geçtiği yerde parantez içinde verilecektir. ingilizce dili(deneyimlere göre, kullanıcıların büyük çoğunluğunun Rusça sürümü yüklüdür).

Dava 1: Mantık fonksiyonları ve eşleşen işlevler
“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,

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

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

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

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

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

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.

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

Şimdi bir sonraki duruma geçelim:
“Koşul” sütunundaki değere bağlı olarak “Sonuç” sütununda belirli bir koşulun görüntülenmesi gerektiğini düşünelim; aşağıda değerler ile sonuç arasındaki yazışma yer almaktadır.
Durum:

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

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

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ü bunların hepsinin manuel olarak girilmesi gerekecek ve formülümüz büyük boyutlara "şişirilmiş", ancak yaklaşım, değerlerin tam "her yerde bulunması" ile ayırt ediliyor; 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.

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

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

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

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

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.

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ı:

Şimdi bir tablodan diğerine veri çekmeniz gereken ancak 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ı:

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

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

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 ))

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

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 sunarız), belirli özelliklere göre diziden değerler almamız ve bunları başka bir tablo biçiminde yerleştirmemiz gerektiğini hayal edelim. .

Buradan görülebileceği gibi tablo formları, her öğenin yalnızca bir adı yoktur (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:

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

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

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

Durum_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ö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 İSE"
"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. Olası aşağıdaki türler hatalar: #YOK, #DEĞER!, #REF!, #BÖL/0!, #SAYI!, #AD? ve #BOŞ!.

"DAHA DÜŞÜK"

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

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

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

• Ara_metin - bulunacak 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.

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

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 durumuna ve hücredeki konumuna bakılmaksızın 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

“Sonucu içeren tablo”dan bir değer bulmanız gereken bir durumu hayal edelim. iki boyutlu diziÇeşitli koşullar için "Dizin", yani "Ad" ve "Ay" değeri.
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.

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ı.

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.

Formülün bir kısmı:

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

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

Böylece “INDEX” fonksiyonunun “Array” argümanında arama yapmak için kullandığı (1; #N/A) noktasının koordinatlarını almış 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))

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

Bu olumsuz etkiyi ortadan kaldırmak 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)

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.
• view_vector 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. Küçük harf ve büyük harfli metin 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.

• 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 çıktısı:

=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, bir koşul sonucun bulunduğu bir tablodadır, bir dizi ise verilerin bulunduğu bir tablodadı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 çıktısı:

=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 için teşekkürler!

İÇİNDE Microsoft belgeleriÇok sayıda alandan oluşan Excel'in genellikle belirli verileri, satır adını vb. bulması gerekir. Doğru kelimeyi veya ifadeyi bulmak için çok sayıda satıra bakmanız gerektiğinde çok sakıncalıdır. Yerleşik Microsoft Excel araması zamandan ve sinirlerden tasarruf etmenize yardımcı olacaktır. Nasıl çalıştığını ve nasıl kullanılacağını anlayalım.

Microsoft Excel'deki arama işlevi, Bul ve Değiştir penceresi aracılığıyla istenen metni veya sayısal değerleri bulma olanağı sunar. Ayrıca uygulama gelişmiş veri arama yetenekleri sunar.

Yöntem 1: Basit Arama

Excel'de basit bir veri araması, arama penceresine girilen karakter kümesini (harfler, sayılar, kelimeler vb.) içeren tüm hücreleri, büyük/küçük harf dikkate alınmadan bulmanızı sağlar.

Yöntem 2: Belirtilen hücre aralığına göre arama yapın

Oldukça büyük bir tablonuz varsa, bu durumda tüm sayfayı aramak her zaman uygun değildir, çünkü arama sonuçları belirli bir durumda gerekli olmayan çok sayıda sonuç içerebilir. Arama alanını yalnızca belirli bir hücre aralığıyla sınırlamanın bir yolu vardır.

Yöntem 3: Gelişmiş Arama

Yukarıda bahsedildiği gibi, normal bir arama sırasında, arama sonuçları, büyük/küçük harfe bakılmaksızın, herhangi bir biçimde sıralı bir arama karakterleri kümesi içeren kesinlikle tüm hücreleri içerir.

Ayrıca sadece belirli bir hücrenin içeriği değil, aynı zamanda onun atıfta bulunduğu öğenin adresi de sonuçlara dahil edilebilmektedir. Örneğin E2 hücresi, A4 ve C3 hücrelerinin toplamından oluşan bir formül içerir. Bu toplam 10'dur ve E2 hücresinde görünen de bu sayıdır. Ancak aramaya “4” sayısını girersek aynı E2 hücresi sonuçlar arasında yer alacaktır. Bu nasıl olabilir? Sadece E2 hücresi formül olarak A4 hücresinin adresini içeriyor ve bu adres sadece istenen 4 sayısını içeriyor.

Ancak bunları ve açıkça kabul edilemez olan diğer arama sonuçlarını nasıl ortadan kaldırabiliriz? Excel gelişmiş araması bu amaçlar için mevcuttur.

1. Pencereyi açtıktan sonra "Bul ve Değiştir" Yukarıda açıklanan herhangi bir yöntemi kullanarak düğmeye tıklayın "Seçenekler".



2. Aramayı yönetmek için bir dizi ek araç pencerede görünür. Varsayılan olarak bu araçların tümü normal aramayla aynı durumdadır ancak gerekirse ayarlamalar yapılabilir.

   

   Varsayılan olarak işlevler "Maç durumu" Ve "Bütün hücreler" devre dışı bırakılır, ancak ilgili öğelerin yanındaki kutuları işaretlersek, bu durumda sonuç oluşturulurken girilen durum ve tam eşleşme dikkate alınacaktır. Küçük harfli bir kelime girerseniz, bu kelimenin büyük harfle yazılışını içeren hücreler, varsayılan olarak olduğu gibi artık arama sonuçlarında görünmez. Ayrıca, işlev etkinleştirilmişse "Bütün hücreler", bu durumda sonuçlara yalnızca tam adı içeren öğeler eklenecektir. Örneğin, "Nikolaev" arama sorgusunu ayarlarsanız, "Nikolaev A.D." metnini içeren hücreler arama sonuçlarına eklenmez.

   

   Varsayılan olarak arama yalnızca etkin Excel sayfasında gerçekleştirilir. Ama eğer parametre "Aramak" pozisyona transfer olacaksın "Kitapta", ardından arama açık dosyanın tüm sayfalarında gerçekleştirilecektir.

   

   Parametrede "Araştır" arama yönünü değiştirebilirsiniz. Varsayılan olarak yukarıda belirtildiği gibi arama satır satır sırayla gerçekleştirilir. Anahtarın konumuna getirilmesi "Sütunlara göre", ilk sütundan başlayarak arama sonuçlarının oluşturulma sırasını ayarlayabilirsiniz.

   

   Sütunda "Arama alanı" hangi belirli unsurların arandığı belirlenir. Varsayılan olarak bunlar formüllerdir, yani bir hücreye tıkladığınızda formül çubuğunda görüntülenen verilerdir. Bu bir kelime, sayı veya hücre referansı olabilir. Aynı zamanda program, arama yaparken sonucu değil yalnızca bağlantıyı görür. Bu etki yukarıda tartışılmıştı. Tam olarak sonuçlara göre, formül çubuğunda değil hücrede görüntülenen verilere göre arama yapmak için anahtarı konumundan hareket ettirmeniz gerekir. "Formüller" pozisyona "Değerler". Ayrıca notlara göre arama yapmak da mümkündür. Bu durumda anahtarı konumuna getirin. "Notlar".

   

   Düğmeye tıklayarak daha da hassas bir arama belirleyebilirsiniz. "Biçim".

   

   Bu, Hücreleri Biçimlendir penceresini açar. Burada aramaya katılacak hücrelerin formatını ayarlayabilirsiniz. Sınırlamaları sayı biçimine, hizalamaya, yazı tipine, kenarlığa, dolguya ve korumaya, bunlardan herhangi birine veya bunların birleşimine göre ayarlayabilirsiniz.

   

   Belirli bir hücrenin formatını kullanmak istiyorsanız pencerenin altındaki düğmeye tıklayın. "Bu hücrenin biçimini kullan...".

   

   Bundan sonra pipet şeklinde bir araç belirir. Bunu kullanarak formatını kullanacağınız hücreyi seçebilirsiniz.

   

   Arama formatı yapılandırıldıktan sonra düğmeye tıklayın "TAMAM".

   

   Belirli bir kelime öbeğine göre değil, başka kelime ve sembollerle ayrılmış olsalar bile arama kelimelerinin herhangi bir sırayla bulunduğu hücreleri bulmanız gereken zamanlar vardır. Daha sonra bu kelimelerin her iki tarafı da “*” işaretiyle vurgulanmalıdır. Artık arama sonuçları bu kelimeleri içeren tüm hücreleri herhangi bir sırayla görüntüleyecektir.



3. Arama ayarları yapıldıktan sonra düğmeye tıklayın "Her şeyi bul" veya "Sonraki Bul" Arama sonuçlarına gitmek için

Gördüğünüz gibi Excel oldukça basit ama aynı zamanda çok işlevsel bir arama araçları setidir. Basit bir gıcırtı yapmak için arama penceresini çağırmanız, içine bir sorgu girmeniz ve düğmeye basmanız yeterlidir. Ancak aynı zamanda çok sayıda farklı parametre ve ek ayarlarla bireysel bir aramayı yapılandırmak da mümkündür.

Diyelim ki bir çalışanın numarasını kullanarak telefon numarasını bulmanız ve aynı zamanda satış tutarına ilişkin Komisyon oranını da doğru tahmin etmeniz gerekiyor. Bir listedeki belirli verileri hızlı ve verimli bir şekilde bulmak için verileri ararsınız ve verilerin doğru şekilde kullanıldığını otomatik olarak kontrol edersiniz. Verileri görüntüledikten sonra hesaplamalar yapabilir ve sonuçları, dönüş değerlerini belirterek görüntüleyebilirsiniz. Bir veri listesindeki değerleri aramanın ve sonuçları görüntülemenin birkaç yolu vardır.

Bu makalede

Tam eşleşmeyle bir listedeki değerleri dikey olarak bulun

Bu görevi gerçekleştirmek için DÜŞEYARA işlevini veya INDEX ve KAÇINCI işlevlerinin bir kombinasyonunu kullanabilirsiniz.

DÜŞEYARA işlevini kullanma örnekleri

DÜŞEYARA işlevi.

INDEX ve MATCHES örnekleri

Bu ne anlama geliyor:

=INDEX(C2:C10'dan gelen değeri döndürmeniz gerekir; bu, MATCH(B2:B10 dizisindeki ilk "Lahana" değeri) ile eşleşecektir)

Formül, C2:C10 hücresinde karşılık gelen ilk değeri arar. lahana(B7'de) ve C7'deki değeri döndürür ( 100 ) - karşılık gelen ilk değer lahana.

Daha fazla bilgi için INDEX işlevine ve MATCH işlevine bakın.

Yaklaşık eşleşmeye göre bir listedeki değerleri dikey olarak bulun

Bunu yapmak için DÜŞEYARA işlevini kullanın.

Önemli:İlk satırdaki değerlerin artan düzende sıralandığından emin olun.

Yukarıdaki örnekte DÜŞEYARA işlevi, A2:B7 aralığında 6 gecikmesi olan bir öğrencinin adını arar. Tabloda giriş yok 6 geç kalır, bu nedenle DÜŞEYARA işlevi 6'nın altındaki bir sonraki en yüksek eşleşmeyi arar ve ilk adla ilişkili 5 değerini bulur Dave ve bu nedenle geri döner Dave.

Daha fazla bilgi için bkz. DÜŞEYARA işlevi.

Tam eşleşme ile bilinmeyen boyuttaki bir listedeki dikey değerleri bulma

Bu görevi gerçekleştirmek için OFFSET ve MATCH işlevlerini kullanın.

Not: Bu yaklaşım, veriler her gün güncellediğiniz harici bir veri aralığındaysa kullanılır. B sütununun Fiyat olduğunu biliyorsunuz, ancak sunucunun kaç satır veri döndürdüğünü bilmiyorsunuz ve ilk sütun alfabetik olarak sıralanmıyor.

C1 aralığın sol üst hücresidir (ayrıca başlangıç ​​hücresi olarak da adlandırılır).

Eşleşme("portakallar"; C2: C7; 0) C2:C7 aralığında turuncu rengi arar. Başlangıç ​​hücresini aralığa dahil etmemelisiniz.

1 - dönüş değerinin döndürülmesi gereken başlangıç ​​hücresinin sağındaki sütunların sayısı. Örneğimizde dönüş değeri D sütunundadır, Satış.

Tam eşleşmeyle bir listedeki değerleri yatay olarak bulun

Bu görevi gerçekleştirmek için GLOOKUP işlevi kullanılır. Aşağıda bir örnek bulunmaktadır.

LOOKUP işlevi bir sütunda arama yapar Satış ve belirtilen aralıktaki 5. satırdaki değeri döndürür.

Daha fazla bilgi için bkz. ARAMA işlevleri.

Arama Sihirbazı'nı kullanarak arama formülü oluşturma (yalnızca Excel 2007)

Not: Arama Sihirbazı eklentisi Excel 2010'da kullanımdan kaldırılmıştır. Bu işlevin yerini İşlev Sihirbazı ve mevcut arama ve başvuru (başvuru) işlevi almıştır.

Excel 2007'de Arama Sihirbazı, satır ve sütun başlıklarına sahip çalışma sayfası verilerini temel alan bir arama formülü oluşturur. Arama Sihirbazı, bir sütundaki değeri bildiğinizde (veya tam tersi) satırdaki diğer değerleri bulmanıza yardımcı olur. Arama Sihirbazı, oluşturduğu formüllerde index ve MATCH'i kullanır.