Dizi formülleriyle ilgili yönergeler ve örnekler

Bu alıştırmada bir dizi satış rakamını hesaplamak için çok hücreli ve tek hücreli dizi formüllerini nasıl kullanacağınız gösterilir. İlk grup adımlarda çok hücreli bir formül kullanılarak alt toplamlar kümesi hesaplanır. İkinci grupta ise tek hücreli bir formül kullanılarak genel toplam hesaplanır.

• Çok hücreli dizi formülü

Aşağıdaki tablonun tamamını kopyalayın ve boş bir çalışma sayfasında A1 hücresine yapıştırın.

Satış  Temsilcisi	Otomobil  Türü	Satış  Sayısı	Birim  Fiyatı	Toplam  Satışlar
Özdemir	Sedan	5	33000
	Coupe	4	37000
Ingle	Sedan	6	24000
	Coupe	8	21000
Jordan	Sedan	3	29000
	Coupe	1	31000
Pica	Sedan	9	24000
	Coupe	5	37000
Sanchez	Sedan	6	33000
	Coupe	8	31000
Formül (Genel Toplam)			Genel Toplam
'=TOPLA(C2:C11*D2:D11)			=TOPLA(C2:C11*D2:D11)

1. Her satış elemanının coupe ve sedan Toplam Satışını görmek için E2:E11 hücrelerini seçin, =C2:C11*D2:D11 formülünü girin ve ardından Ctrl+Shift+Enter tuşlarına basın.

2. Tüm satışların Genel Toplamını görmek için F11 hücresini seçin, =TOPLA(C2:C11*D2:D11) formülünü girin ve ardından Ctrl+Shift+Enter tuşlarına basın.

Ctrl+Shift+Enter tuşlarına bastığınızda, Excel formülü küme ayracı ({ }) içine alır ve seçili aralıktaki her hücreye formülün bir örneğini ekler. Bu işlem çok hızlı yapılır ve E sütununda her satış elemanının her otomobil tipinde yaptığı toplam satış miktarını görürsünüz. Önce E2'yi ve sonra E3, E4, vb. seçerseniz, aynı formülün gösterildiğini görürsünüz: {=C2:C11*D2:D11}. 

• Tek hücreli dizi formülü oluşturma

Çalışma kitabının D13 hücresine aşağıdaki formülü yazın ve sonra Ctrl+Shift+Enter tuşlarına basın:

=TOPLA(C2:C11*D2:D11)

Bu örnekte, Excel dizideki (C2 ile D11 arasındaki hücreler) değerlerin çarpımını alır ve toplamları birbirine eklemek için TOPLA işlevini kullanır. Sonuç, 159.000 TL toplam satış tutarıdır. Bu örnek, bu tür bir formülün ne kadar güçlü olabileceğini göstermektedir. Örneğin, 1.000 satır veriniz olduğunu varsayın. Formülü aşağı doğru 1.000 satır sürüklemek yerine, tek bir hücrede dizi formülü oluşturarak, bu verilerin bir bölümünün veya tümünün toplamını alabilirsiniz.

Ayrıca, D13 hücresindeki tek hücreli formülün çok hücreli formülden (E2 ile E11 hücreleri arasındaki formül) tümüyle bağımsız olduğuna da dikkat edin. Dizi formüllerini kullanmanın bir diğer avantajı da budur: Esneklik. D13 hücresindeki formülü etkilemeden E sütunundaki formülleri değiştirebilir veya bu sütunu tamamıyla silebilirsiniz.

Dizi formülleri şu avantajları da sağlar:

• Tutarlılık    E2'nin altındaki hücrelerden herhangi birini tıklatırsanız, aynı formülü görürsünüz. Bu tutarlılık daha doğru sonuçlar elde edilmesine yardımcı olabilir.

• Güvenlik    Çok hücreli bir dizi formülünün bileşenlerinin üzerine yazamazsınız. Örneğin, E3 hücresine tıklayın ve Delete tuşuna basın. Hücre aralığının (E2 ile E11 arası) tümünü seçip formülü tüm dizi için değiştirmeli veya diziyi olduğu gibi bırakmalısınız. Ek bir güvenlik önlemi olarak, formüldeki değişiklikleri onaylamak üzere Ctrl+Shift+Enter tuşlarına basmalısınız.

• Daha küçük dosya boyutları.    Genellikle birkaç ara formül yerine tek bir dizi formülü kullanabilirsiniz. Örneğin, çalışma kitabında E sütunundaki sonuçları hesaplamak için tek bir dizi formülü kullanılmaktadır. Oysa standart formülleri (=C2*D2, C3*D3, C4*D4… gibi) kullansaydınız, aynı sonuçları hesaplamak için 11 farklı formül kullanacaktınız.

Genelde dizi formülleri standart formül söz dizimi kullanılır. Bunların tümü eşittir (=) işaretiyle başlar ve dizi formüllerinizde yerleşik Excel işlevlerinin çoğunu kullanabilirsiniz. Temel farklılık, dizi formülünü kullanırken formülü girmek için Ctrl+Shift+Enter tuşlarına basmanızın gerekmesidir. Bunu yaptığınızda Excel dizi formülünüzü küme ayraçları içine alır; küme ayraçlarını kendiniz yazarsanız, formülünüz metin dizesine dönüştürülür ve formül çalışmaz.

Dizi işlevleri karmaşık formüller oluşturmak için verimli bir yol olabilir. =TOPLA(C2:C11*D2:D11) dizi formülü şu formülle aynıdır: =TOPLA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Çok hücreli formüllerle çalıştığınız her durumda şunları da unutmayın.

• Formülü girmeden önce sonuçların tutulacağı hücre aralığını seçin. Çok hücreli dizi formülünü oluştururken E2 ile E11 arasındaki hücreleri seçtiğinizde bunu yaptınız.

• Dizi formülü kapsamındaki tek tek hücrelerin içeriğini değiştiremezsiniz. Bunu denemek için, çalışma kitabındaki E3 hücresini seçin ve Delete tuşuna basın. Excel, dizinin bir bölümünü değiştiremeyeceğinizi belirten bir ileti görüntüler.

• Dizi formülünün tamamını taşıyabilir veya silebilir, ancak bir bölümünü taşıyamaz ya da silemezsiniz. Başka bir deyişle, dizi formülünü daraltmak için önce varolan formülü siler ve sonra baştan başlarsınız.

• Dizi formülünü silmek için formül aralığının tamamını seçin (örneğin, E2:E11), sonra da Delete tuşuna basın.

• Çok hücreli dizi formülünde araya boş hücre ekleyemez veya hücreleri silemezsiniz.

Kimi zaman dizi formülünü genişletmeniz gerekebilir. Varolan dizi aralığındaki ilk hücreyi seçin ve formülü genişletmek istediğiniz aralığın tamamı seçilene kadar seçmeye devam edin. Formül aralığını ayarladıktan sonra, formülü düzenlemek için F2 tuşuna basın ve ardından CTRL+SHIFT+ENTER tuşlarına basarak formülü onaylayın. Önemli olan nokta, dizinin sol üst hücresinden başlayarak aralığın tamamını seçmektir. Sol üst hücre, düzenlenecek olan hücredir.

Dizi formülleri mükemmel olsa da, bazı dezavantajları bulunabilmektedir:

• Bazen Ctrl+Shift+Enter tuşlarına basmayı unutabilirsiniz. Bu durum, çoğu deneyimli Excel kullanıcısının bile başına gelebilir. Dizi formüllerini her girişinizde veya düzenleyişinizde bu tuş birleşimine basmayı unutmayın.

• Çalışma kitabınızı kullanan diğer kişiler formüllerinizi anlamayabilir. Uygulamada, bir çalışma sayfasındaki dizi formülleri genellikle açıklanmaz. Bu nedenle başkalarının çalışma kitaplarınızda değişiklik yapması gerektiğinde dizi formüllerini kullanmaktan kaçınmalı veya söz konusu kişilerin dizi formüllerini bildiğinden ve gerekirse bunların nasıl değiştirileceğini anladığından emin olmalısınız.

• Bilgisayarınızın işlemci hızına ve bellek boyutuna bağlı olarak, büyük dizi formülleri hesaplamaların yavaşlamasına neden olabilir.

Dizi sabitleri, dizi formüllerinin bileşenleridir. Bir öğe listesi girip sonra bu listeyi kendiniz küme ayracı ({ }) içine alarak dizi sabitleri oluşturabilirsiniz; örneğin:

={1;2;3;4;5}

Artık dizi formüllerini oluştururken Ctrl+Shift+Enter tuşlarına basmanız gerektiğini biliyorsunuz. Dizi sabitleri dizi formüllerinin bir bileşeni olduğundan, sabitleri kendiniz yazarak küme ayracı içine alırsınız. Daha sonra da, formülün tamamını girmek için Ctrl+Shift+Enter tuşlarını kullanırsınız.

Öğeleri virgüllerle ayırırsanız, yatay dizi (satır) oluşturursunuz. Öğeleri noktalı virgüllerle ayırırsanız, dikey dizi (sütun) oluşturursunuz. İki boyutlu dizi oluşturmak için, her satırdaki öğeleri virgüllerle sınırlandırmanız ve her satırı da noktalı virgüllerle sınırlandırmanız gerekir.

İşte size tek satır halinde bir dizi: {1;2;3;4}. Bu da tek sütun halinde bir dizidir: {1\2\3\4}. Ve bu da iki satır ve dört sütundan oluşan bir dizidir: {1;2;3;4\5;6;7;8}. İki satırlı dizide, ilk satır 1, 2, 3 ve 4 ve ikinci satır 5, 6, 7 ve 8'dir. Tek bir noktalı virgül iki satırı ayırır (4 ile 5 arasında).

Dizi formüllerinde olduğu gibi, dizi sabitlerini de Excel'de sağlanan yerleşik işlevlerin çoğuyla kullanabilirsiniz. Aşağıdaki bölümlerde her tür sabitin nasıl oluşturulacağı ve bu sabitlerin Excel işlevlerinde nasıl kullanılacağı açıklanmaktadır.

Aşağıdaki yordamlar size yatay, dikey ve iki boyutlu sabit oluşturma konusunda biraz alıştırma yapma olanağı sağlayacaktır.

Yatay sabit oluşturma

1. Boş bir çalışma sayfasında A1 ile E1 arasındaki hücreleri seçin.

2. Formül çubuğuna aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   ={1;2;3;4;5}

   Bu durumda, açma ve kapatma küme ayraçlarını ({ }) yazmanız gerekir ve Excel ikinci kümeyi sizin için ekler.

   Aşağıdaki sonuç görüntülenir.

   

Dikey sabit oluşturma

1. Çalışma kitabınızda beş hücrelik bir sütun seçin.

2. Formül çubuğuna aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   ={1\2\3\4\5}

   Aşağıdaki sonuç görüntülenir.

   

İki boyutlu sabit oluşturma

1. Çalışma kitabınızda, dört sütun genişliğinde ve üç satır yüksekliğinde bir hücre bloğu seçin.

2. Formül çubuğuna aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   ={1;2;3;4\5;6;7;8\9;10;11;12}

   Aşağıdaki sonucu görürsünüz:

   

Formüllerde sabitleri kullanma

Burada, sabitlerin kullanıldığı basit bir örnek verilmektedir:

1. Örnek çalışma kitabında yeni bir çalışma sayfası oluşturun.

2. A1 hücresine 3 yazın ve sonra B1'e 4, C1'e 5, D1'e 6 ve E1'e 7 yazın.

3. A3 hücresine aşağıdaki formülü yazın ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =TOPLA(A1:E1*{1;2;3;4;5})

   Bunu bir dizi formülü olarak girdiğinizden, Excel'in sabiti başka bir küme ayracı takımı içine aldığına dikkat edin.

   

   A3 hücresinde 85 değeri gösterilir.

Aşağıdaki bölümde formülün nasıl çalıştığı açıklanır.

Az önce kullandığınız formülün birkaç parçadan oluşur.

1. İşlev

2. Depolanan dizi

3. İşleç

4. Dizi sabiti

Parantezlerin içindeki son öğe dizi sabitidir: {1;2;3;4;5}. Excel'in dizi sabitlerini küme ayracı içine almadığını unutmayın; bunları siz kendiniz girersiniz. Ayrıca, dizi formülüne sabit ekledikten sonra formülü girmek üzere Ctrl+Shift+Enter tuşlarına basmayı da unutmayın.

Excel öncelikle parantez içindeki ifadeler üzerinde işlem gerçekleştirdiğinden, işleme katılan sonraki iki öğe çalışma kitabında depolanan değerler (A1:E1) ve işleçtir. Bu noktada, formül depolanan dizideki değerleri sabitte bunlara karşılık gelen değerlerle çarpar. Bunun eşdeğeri şöyledir:

=TOPLA(A1*1,B1*2,C1*3,D1*4,E1*5)

Son olarak, TOPLA işlevi değerlerin toplamını alır ve 85 toplamı A3 hücresinde gösterilir.

Depolanan dizi kullanmaktan kaçınmak ve işlemin tamamını bellekte tutmak için, depolanan dizinin yerine başka bir dizi sabiti koyun:

=TOPLA({3;4;5;6;7}*{1;2;3;4;5})

Bunu denemek için işlevi kopyalayın, çalışma kitabınızda boş bir hücre seçin, formülü formül çubuğuna yapıştırın ve sonra da Ctrl+Shift+Enter tuşlarına basın. Şu dizi formülünün kullanıldığı önceki alıştırmada elde ettiğiniz sonucun aynısını görürsünüz:

=TOPLA(A1:E1*{1;2;3;4;5})

Dizi sabitleri sayılardan, metinlerden, mantıksal değerlerden (DOĞRU ve YANLIŞ gibi) ve hata değerlerinden (#YOK) oluşabilir. Sayıları tamsayı, ondalık ve bilimsel biçimlerde kullanabilirsiniz. Metin eklerseniz, bu metni tırnak işaretleri ( ") içine almanız gerekir.

Dizi sabitleri başka diziler, formüller veya işlevler içeremez. Başka bir deyişle, yalnızca virgül veya noktalı virgülle ayrılmış metin ve sayılar içerebilirler. {1;2;A1:D4} veya {1;2;TOPLA(Q2:Z8)} gibi bir formül girerseniz Excel uyarı iletisi görüntüler. Ayrıca, sayısal değerlerde yüzde işaretleri, dolar işaretleri, virgüller veya parantezler bulunamaz.

Dizi sabitlerini kullanmanın en iyi yollarından biri bunları adlandırmaktır. Adlandırılmış sabitler çok daha kolay kullanılabildiği gibi, bunlar dizi formüllerinizin karmaşıklığının bir bölümünü de diğer kullanıcılardan gizleyebilir. Dizi sabitini adlandırmak ve formülün içinde kullanmak için aşağıdakileri yapın:

1. Formüller sekmesindeki Tanımlı Adlar grubunda, Ad Tanımla'yı tıklatın.
   Ad Tanımla iletişim kutusu görüntülenir.

2. Ad kutusuna Çeyrek1 yazın.

3. Başvuru yeri kutusuna şu sabiti girin (küme ayraçlarını el ile eklemeyi unutmayın):

   ={"Ocak";"Şubat";"Mart"}

   İletişim kutusunun içeriği şimdi şöyle görünür:

   

4. Tamam'a tıklayın ve sonra üç boş hücreden oluşan bir satır seçin.

5. Aşağıdaki formülü yazın ve Ctrl+Shift+Enter tuşlarına basın.

   =Çeyrek1

   Aşağıdaki sonuç görüntülenir.

   

Adlandırılmış bir sabiti dizi formülü olarak kullandığınızda eşittir işaretini girmeyi unutmayın. Girmezseniz, Excel diziyi bir metin dizesi olarak yorumlar ve formülünüz beklendiği şekilde çalışmaz. Son olarak, metin ve sayı bileşimlerini kullanabileceğinizi de aklınızda bulundurun.

Dizi sabitleriniz çalışmazsa, aşağıdaki sorunların söz konusu olup olmadığına bakın:

• Bazı öğeler doğru karakterle birbirinden ayrılmamış olabilir. Virgülü veya noktalı virgülü eklemeyi atlarsanız ya da bunlardan birini yanlış yere koyarsanız, dizi sabiti doğru bir şekilde oluşturulmayabilir veya uyarı iletisi görebilirsiniz.

• Sabitinizdeki öğelerin sayısıyla eşleşmeyen bir hücre aralığı seçmiş olabilirsiniz. Örneğin, beş hücreli bir sabitte kullanmak üzere altı hücreden oluşan bir sütun seçerseniz, boş hücrede #YOK hata değeri gösterilir. Bunun tersine, az sayıda hücre seçerseniz Excel karşılığında bir hücre bulunmayan değerleri atar.

Aşağıdaki örneklerde dizi sabitlerini dizi formüllerinde kullanabileceğiniz yollardan birkaç gösterilir. Örneklerin bazılarında satırları sütunlara ve tersine dönüştürmek üzere DEVRİK_DÖNÜŞÜM işlevi kullanılmıştır.

Dizideki öğeleri çarpma

1. Yeni bir çalışma sayfası oluşturun, ardından dört sütun genişliğinde ve üç satır yüksekliğinde boş hücrelerden oluşan bir blok seçin.

2. Aşağıdaki formülü yazın ve sonra Ctrl+Shift+Enter tuşlarına basın:

   ={1;2;3;4\5;6;7;8\9;10\11\12}*2

Dizideki öğelerin karesini alma

1. Dört sütun genişliğinde ve üç satır yüksekliğinde boş hücrelerden oluşan bir blok seçin.

2. Aşağıdaki dizi formülünü yazın ve sonra Ctrl+Shift+Enter tuşlarına basın:

   ={1;2;3;4\5;6;7;8\9;10;11;12}*{1;2;3;4\5;6;7;8\9;10;11;12}

   Alternatif olarak, şapka işlecinin ( ^) kullanıldığı şu dizi formülünü de girebilirsiniz:

   ={1;2;3;4\5;6;7;8\9;10;11;12}^2

Tek boyutlu bir satırı tersine dönüştürme

1. Beş boş hücreden oluşan bir sütun seçin.

2. Aşağıdaki formülü yazın ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =DEVRİK_DÖNÜŞÜM({1;2;3;4;5})

   Yatay bir dizi sabiti girmiş olmanıza karşın, DEVRİK_DÖNÜŞÜM işlevi dizi sabitini sütuna dönüştürür.

Tek boyutlu bir sütunu tersine dönüştürme

1. Beş boş hücreden oluşan bir satır seçin.

2. Aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =DEVRİK_DÖNÜŞÜM({1\2\3\4\5})

Dikey bir dizi sabiti girmiş olmanıza karşın, DEVRİK_DÖNÜŞÜM işlevi sabiti satıra dönüştürür.

İki boyutlu bir sabiti tersine dönüştürme

1. Üç sütun genişliğinde ve dört satır yüksekliğinde bir hücre bloğu seçin.

2. Aşağıdaki sabiti girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =DEVRİK_DÖNÜŞÜM({1;2;3;4\5;6;7;8\9;10;11;12})

   DEVRİK_DÖNÜŞÜM işlevi her satırı bir sütun dizisine dönüştürür.

Bu bölümde temel dizi formüllerinin örnekleri yer alır.

Varolan değerlerden diziler ve dizi sabitleri oluşturma

Aşağıdaki örnekte, farklı çalışma sayfalarında yer alan hücre aralıkları arasında bağlantı oluşturmak üzere dizi formüllerinin nasıl kullanılacağı açıklanır. Ayrıca, aynı veri kümesinden nasıl dizi sabiti oluşturacağınız da gösterilir.

Varolan değerlerden dizi oluşturma

1. Excel'de bir çalışma sayfasında, C8:E10 hücresini seçip formülü girin:

   ={10;20;30\40;50;60\70;80;90}

   10 yazmadan önce { (açılış küme ayracı) ve 90 yazdıktan sonra } (kapanış küme ayracı) eklemeyi unutmayın; çünkü bir sayı dizisi oluşturuyorsunuz.

2. Dizi formülü kullanılarak bu sayı dizisinin C8:E10 hücre aralığına girilmesi için Ctrl+Shift+Enter tuşlarına basın. Çalışma sayfanızda C8 ile E10 arası şöyle görünmelidir:

   10	20	30
   40	50	60
   70	80	90

3. C1 ile E3 arasındaki hücreleri seçin.

4. Formül çubuğuna aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =C8:E10

   C1 ile E3 arası hücrelerde, 3x3'lük bir hücre dizisi görüntülenir (C8 - E10 arası hücrelerde gördüğünüz değerlerle).

Varolan değerlerden dizi sabiti oluşturma

1. C1:C3 hücreleri seçili durumdayken düzenleme moduna geçmek üzere F2 tuşuna basın. 

2. F9 tuşuna basarak hücre başvurularını değerlere dönüştürün. Excel değerleri bir dizi sabitine dönüştürür. Formül şimdi ={10;20;30\40;50;60\70;80;90} olmalıdır.

3. Dizi sabitini bir dizi formülü olarak girmek için Ctrl+Shift+Enter tuşlarına basın.

Hücre aralığındaki karakterleri sayma

Aşağıdaki örnekte bir hücre aralığında yer alan karakterlerin sayısını (boşluklarla birlikte) nasıl hesaplayacağınız gösterilir.

1. Bu tablonun tamamını kopyalayın ve bir çalışma sayfasında A1 hücresine yapıştırın.

   Veriler
   Bu,
   tek bir cümle
   oluşturmak üzere
   birlikte gelen
   bir hücre grubudur.
   A2:A6'daki toplam karakter sayısı
   =TOPLA(UZUNLUK(A2:A6))
   En uzun hücrenin (A3) içeriği
   =İNDİS(A2:A6,KAÇINCI(MAK(UZUNLUK(A2:A6)),UZUNLUK(A2:A6),0),1)

2. A8 hücresini seçin ve ardından A2:A6 hücrelerindeki toplam karakter sayısını (66) görmek için Ctrl+Shift+Enter tuşlarına basın.

3. A10 hücresini seçin ve ardından A2:A6 hücreleri arasında en uzun hücrenin (A3) içeriğini görmek için Ctrl+Shift+Enter tuşlarına basın.

A8 hücresinde kullanılan aşağıdaki formül, A2 ile A6 arası hücrelerdeki toplam karakter sayısını (66) hesaplar.

=TOPLA(UZUNLUK(A2:A6))

Bu örnekte, UZUNLUK işlevi aralıktaki hücrelerden her birinin içerdiği metin dizesinin uzunluğunu verir. TOPLA işlevi de bu değerleri birbiriyle toplar ve sonucu (66) görüntüler.

Aralıktaki en küçük n değeri bulma

Bu örnekte, hücre aralığındaki en küçük üç değerin nasıl bulunacağı gösterilir.

1. A1:A11 hücrelerine bazı rastgele sayılar girin.

2. C1 ile C3 arasındaki hücreleri seçin. Bu hücre kümesi, dizi formülünün döndürdüğü sonuçları içerecektir.

3. Aşağıdaki formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =KÜÇÜK(A1:A11,{1\2\3})

Bu formül KÜÇÜK işlevini üç kez çalıştırmak için dizi sabiti kullanır ve A1:A10 hücrelerindeki dizinin en küçük (1), ikinci en küçük (2) ve üçüncü en küçük (3) üyelerini döndürür. Daha fazla değer bulmak için, sabite daha fazla bağımsız değişken eklersiniz. Bu formülle birlikte TOPLA veya ORTALAMA gibi ek işlevler de kullanabilirsiniz. Örneğin:

=TOPLA(KÜÇÜK(A1:A10,{1;2;3})

=ORTALAMA(KÜÇÜK(A1:A10,{1;2;3})

Aralıktaki en büyük n değeri bulma

Aralıktaki en büyük değerleri bulmak için KÜÇÜK işlevini BÜYÜK işleviyle değiştirebilirsiniz. Aşağıdaki örnekte buna ek olarak SATIR ve DOLAYLI işlevleri de kullanılır.

1. D1 ile D3 arasındaki hücreleri seçin.

2. Formül çubuğuna şu formülü girin ve sonra Ctrl+Shift+Enter tuşlarına basın:

   =BÜYÜK(A1:A10,SATIR(DOLAYLI("1:3")))

Bu noktada, SATIR ve DOLAYLI işlevleri hakkında biraz bilgi edinmek yararlı olabilir. SATIR işlevini birbirini izleyen bir tamsayılar dizisi oluşturmak için kullanabilirsiniz. Örneğin alıştırma çalışma kitabınızda 10 hücrelik boş bir sütun seçin, bu dizi formülünü girin ve Ctrl+Shift+Enter tuşlarına basın:

=SATIR(1:10)

Formül birbirini izleyen 10 tamsayının bulunduğu bir sütun oluşturur. Olası bir sorunu görmek için, dizi formülünü içeren aralığın üstün (yani 1. satırın üstüne) bir satır ekleyin. Excel satır başvurularını ayarlar ve formül 2’den 11’e kadar olan tamsayıları oluşturur. Bu sorunu çözmek için formüle DOLAYLI işlevini eklersiniz:

=SATIR(DOLAYLI("1:10"))

DOLAYLI işlevi metin dizelerini bağımsız değişken olarak kullanır (1:10 aralığının çift tırnak içine alınmasının nedeni budur). Excel satır eklediğinizde veya başka bir yolla dizi formülünü taşıdığınızda metin değerlerini bu değişikliğe göre ayarlamaz. Sonuç olarak, SATIR işlevi her zaman istediğiniz tamsayı dizisini oluşturur.

Şimdi daha önce kullandığınız =BÜYÜK(A5:A14,SATIR(DOLAYLI("1:3"))) formülünü, iç parantezlerden başlayıp dışarı doğru ilerleyerek gözden geçirelim: DOLAYLI işlevi bir metin değerleri kümesi (bu örnekte 1’den 3’e kadar olan değerler) döndürür. SATIR işlevi de üç hücreli bir sütun dizisi oluşturur. BÜYÜK işlevi A5:A14 hücre aralığındaki değerleri kullanır ve SATIR işlevi tarafından döndürülen her başvuru için bir kez olmak üzere üç kez hesaplanır. Üç hücreli sütun dizisine 3200, 2700 ve 2000 değerleri döndürülür. Daha fazla değer bulmak istiyorsanız, DOLAYLI işlevine daha büyük bir hücre aralığı eklersiniz.

Önceki örneklerde olduğu gibi, bu formülü TOPLA ve ORTALAMA gibi başka işlevlerle kullanabilirsiniz.

Hücre aralığındaki en uzun metin dizesini bulma

Önceki metin dizesi örneğine dönün, boş hücreye aşağıdaki formülü girin ve Ctrl+Shift+Enter tuşlarına basın:

=İNDİS(A2:A6,KAÇINCI(MAK(UZUNLUK(A2:A6)),UZUNLUK(A2:A6),0),1)

"hücre grubu" metni görüntülenir.

İç öğelerden başlayıp dışarı doğru ilerleyerek formüle daha yakından bakalım. UZUNLUK işlevi, A2:A6 hücre aralığındaki öğelerden her birinin uzunluğunu döndürür. MAK işlevi, bu öğeler arasında en büyük değeri hesaplar. Bu değer en uzun metin dizesine karşılık gelir ve A3 hücresinde yer alır.

Bu aşamada işler biraz karmaşık hale gelir. KAÇINCI işlevi en uzun metin dizesini içeren hücrenin uzaklığını (görece konumunu) hesaplar. Bunu yapmak için üç bağımsız değişkene gerek duyar: arama değeri, arama dizisi ve eşleştirme türü. KAÇINCI işlevi, belirtilen arama değeri için arama dizisini arar. Bu örnekte, arama değeri en uzun metin dizesidir:

(MAK(UZUNLUK(A2:A6))

ve bu dize şu dizide yer alır:

UZUNLUK(A2:A6)

Eşleştirme türü bağımsız değişkeni 0'dır. Eşleştirme türü 1, 0 veya -1 değerinden oluşabilir. 1 değerini belirtirseniz, KAÇINCI işlevi arama değerinden küçük veya buna eşit olan en büyük değeri döndürür. 0 değerini belirtirseniz, KAÇINCI işlevi arama değerine tam olarak eşit olan ilk değeri döndürür. -1 değerini belirtirseniz, KAÇINCI işlevi belirtilen arama değerinden büyük veya buna eşit en küçük değeri bulur. Eşleştirme türünü atlarsanız, Excel bunun 1 olduğunu varsayar.

Son olarak, İNDİS işlevi şu bağımsız değişkenleri alır: dizi ve bu dizinin içindeki bir satır ve sütunun numarası. A2:A6 hücre aralığı diziyi sağlar, KAÇINCI işlevi hücre adresini sağlar ve son bağımsız değişken (1) değerin dizideki ilk sütundan geldiğini belirtir.

Bu bölümde gelişmiş dizi formüllerinin örnekleri yer alır.

Hata değerleri içeren bir aralığın toplamını alma

Excel'de TOPLA işlevi, #YOK gibi bir hata değeri içeren bir aralığın toplamını almayı denediğinizde çalışmaz. Bu örnek Veri olarak adlandırılan ve hatalar içeren bir aralığın değerlerinin nasıl toplanacağını gösterir.

=TOPLA(EĞER(EHATALIYSA(Veri),"",Veri))

Formül, özgün değerler eksi hata değerlerini içeren yeni bir dizi oluşturur. İçteki işlevlerden başlayıp dışarı doğru ilerlediğimizde, EHATALIYSA işlevi hücre aralığındaki (Veri) hataları arar. EĞER işlevi, belirttiğiniz koşul DOĞRU olduğunda belirli bir değer, YANLIŞ olduğundan başka bir değer döndürür. Bu örnekte, tüm hata değerleri için (bunlar DOĞRU olduğundan) boş dizeler ("") döndürür ve aralıktaki (Veri) kalan değerler koşula göre YANLIŞ olduğundan (yani hata değerleri içermediğinden), bu değerleri döndürür. Ardından TOPLA işlevi filtre uygulanmış dizinin toplamını hesaplar.

Aralıktaki hata değerlerinin sayısını hesaplama

Bu örnek önceki formülün benzeridir, ancak Veri adlı aralıktaki hata değerlerine filtre uygulamak yerine bunların sayısını döndürür:

=TOPLA(EĞER(EHATALIYSA(Veri),1,0))

Bu formül, hata içeren hücrelerin için 1 değerinin ve hata içermeyen hücreler için 0 değerinin bulunduğu bir dizi oluşturur. EĞER işlevinin üçüncü bağımsız değişkenini kaldırarak formülü basitleştirebilir ve aynı sonucu elde edebilirsiniz; şöyle:

=TOPLA(EĞER(EHATALIYSA(Veri),1))

Bağımsız değişkeni belirtmezseniz, EĞER işlevi hata değeri içermeyen hücreler için YANLIŞ değerini döndürür. Formülü daha da basitleştirebilirsiniz:

=TOPLA(EĞER(EHATALIYSA(Veri)*1))

Bu uyarlama çalışır çünkü DOĞRU*1=1 ve YANLIŞ*1=0'dır.

Değerleri koşullara dayalı olarak toplama

Değerleri koşullara dayalı olarak toplamanız gerekebilir. Örneğin, bu dizi formülü yalnızca Satış adlı aralıktaki pozitif tamsayıların toplamını hesaplar:

=TOPLA(EĞER(Satış>0,Satış))

EĞER işlevi pozitif değerlerin ve hata değerlerinin dizisini oluşturur. 0+0=0 olduğundan TOPLA işlevi temelde yanlış değerleri yoksayar. Bu formülde kullandığınız hücre aralığı herhangi bir sayıda satır ve sütundan oluşabilir.

Birden çok koşula uyan değerlerin de toplamını hesaplayabilirsiniz. Örneğin, bu dizi formülü 0'dan büyük ve 5'ten küçük veya buna eşit değerleri hesaplar:

=TOPLA((Satış>0)*(Satış<=5)*(Satış))

Aralık bir veya birden çok sayısal olmayan hücre içeriyorsa bu formülün hata döndüreceğini aklınızda bulundurun.

YADA koşulunun bir türünü kullanan dizi formülleri de oluşturabilirsiniz. Örneğin, 5'ten küçük ve 15'ten büyük değerlerin toplamını hesaplayabilirsiniz:

=TOPLA(EĞER((Satış<5)+(Satış>15),Satış))

EĞER işlevi 5'ten küçük ve 15'ten büyük olan tüm değerleri bulur ve sonra bulduğu değerleri TOPLA işlevine geçirir.

VE ve YADA işlevleri tek bir sonuç, DOĞRU veya YANLIŞ sonucunu döndürdüğünden ve dizi işlevleri için sonuç dizisi gerektiğinden, bu işlevleri dizi formüllerinde doğrudan kullanamazsınız. Önceki örnekte gösterilen mantığı kullanarak bu sorunu aşabilirsiniz. Başka bir deyişle, VE veya YADA koşuluna uyan değerler üzerinde toplama veya çarpma gibi matematik işlemleri yapabilirsiniz.

Sıfırları dışlayan bir ortalama hesaplama

Bu örnek, bir aralıktaki değerlerin ortalamasını hesaplamanız gerektiğinde bu aralıktan sıfırları nasıl kaldıracağınızı gösterir. Formülde Satış adlı veri aralığı kullanılır:

=ORTALAMA(EĞER(Satış<>0,Satış))

EĞER işlevi 0'a eşit olmayan değerlerin dizisini oluşturur ve bu değerleri ORTALAMA işlevine geçirir.

İki hücre aralığı arasındaki farkların sayısını hesaplama

Bu dizi formülü Verilerim ve Verilerin adlı iki hücre aralığındaki değerleri karşılaştırır ve ikisi arasındaki farkların sayısını döndürür. İki aralığın içeriği birbirinin aynıysa, formül 0 döndürür. Bu formülü kullanmak için hücre aralıklarının aynı büyüklükte ve boyutta olması gerekir (örneğin, Verilerim 3 satır x 5 sütunluk bir aralıksa, Verilerin de 3 satır x 5 sütunluk bir aralık olmalıdır):

=TOPLA(EĞER(Verilerim=Verilerin,0,1))

Formül, karşılaştırdığınız aralıklarla aynı boyda yeni bir dizi oluşturur. EĞER işlevi diziyi 0 ve 1 değerleriyle doldurur (eşleşmeyen hücreler için 0 ve birbirinin aynı hücreler için 1). TOPLA işlevi dizideki değerlerin toplamını döndürür.

Formülü şu şekilde basitleştirebilirsiniz:

=TOPLA(1*(Verilerim<>Verilerin))

Aralıktaki hata değerlerinin sayısını hesaplayan formül gibi bu formül de DOĞRU*1=1 ve YANLIŞ*1=0 olduğu için çalışır.

Aralıktaki en büyük değerin konumunu bulma

Bu dizi formülü Veri adlı tek sütunlu bir aralıktaki en büyük değerin satır numarasını döndürür:

=MİN(EĞER(Veri=MAK(Veri),SATIR(Veri),""))

EĞER işlevi, Veri adlı aralığa karşılık gelen yeni bir dizi oluşturur. Karşılık gelen hücre aralıktaki en yüksek değeri içeriyorsa, satır numarası dizide yer alır. Aksi takdirde, dizi boş bir dize ("") içerir. MİN işlevi yeni diziyi ikinci bağımsız değişkeni olarak kullanır ve en küçük değeri döndürür. Bu değer Veri aralığındaki en yüksek değerin satır numarasına karşılık gelir. Veri adlı aralık birbirinin aynı en yüksek değerler içeriyorsa, formül ilk değerin satırını döndürür.

En büyük değerin gerçek hücre adresini döndürmek istiyorsanız aşağıdaki formülü kullanın:

=ADRES(MİN(EĞER(Veri=MAK(Veri),SATIR(Veri),"")),SÜTUN(Veri))