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ü

  

• Burada H10 hücresine =F10:F19*G10:G19 girerek her satış temsilcisinin coupe ve sedan Toplam Satışlarını hesaplıyoruz.

  Enter tuşuna bastığınızda, sonuçların aşağı doğru H10:H19 hücrelerine taştığını görürsünüz. Taşma aralığındaki herhangi bir hücreyi seçtiğinizde taşma aralığının bir kenarlıkla vurgulandığına dikkat edin. Ayrıca H10:H19 hücrelerindeki formüllerin gri gösterildiği de dikkatinizi çekebilir. O formüller yalnızca başvuru amaçlıdır, dolayısıyla formülü ayarlamak isterseniz ana formülün yer aldığı H10 hücresini seçmeniz gerekir.

• Tek hücreli dizi formülü

  

  Örnek çalışma kitabının H20 hücresine =TOPLA(F10:F19*G10:G19) işlevini yazın veya kopyalayıp yapıştırın ve Enter tuşuna basın.

  Bu örnekte, Excel dizideki (F10 ile G19 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, H20 hücresindeki tek hücreli formülün çok hücreli formülden (H10 ile H19 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. H20 hücresindeki formülü etkilemeden H sütunundaki diğer formülleri değiştirebilirsiniz. Ayrıca bunun gibi bağımsız toplamlar elde etmek de iyi bir uygulama olabilir çünkü sonuçlarınızın doğruluğundan emin olmanıza yardım eder.

• Dinamik dizi formülleri şu avantajları da sağlar:

  • Tutarlılık    H10’un 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, H11 hücresine tıklayın ve Delete tuşuna basın. Excel dizinin çıkışını değiştirmeyecektir. Çıkışı değiştirmek için dizinin en üst sol hücresini (H10 hücresi) seçmeniz gerekir.

  • Daha küçük dosya boyutları.    Genellikle birkaç ara formül yerine tek bir dizi formülü kullanabilirsiniz. Örneğin, araba satışları örneğinde E sütunundaki sonuçları hesaplamak için tek bir dizi formülü kullanılır. Oysa =F10*G10, F11*G11, F12*G12 gibi standart formülleri kullansaydınız, aynı sonuçları hesaplamak için 11 farklı formül kullanacaktınız. Bu o kadar önemli değil ama bir de toplama eklenecek binlerce satırınız olduğunu düşünün. İşte bu büyük bir fark yaratabilir.

  • Verimlilik    Dizi işlevleri karmaşık formüller oluşturmak için verimli bir yol olabilir. =TOPLA(F10:F19*G10:G19) dizi formülü şununla aynıdır: =TOPLA(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

  • Taşma    Dinamik dizi formülleri otomatik olarak çıkış aralığına taşar. Kaynak verileriniz bir Excel tablosundaysa, siz verileri eklediğinizde veya kaldırdığınızda dinamik dizi formülleriniz otomatik olarak yeniden boyutlandırılır.

  • #TAŞMA! hatası    Dinamik dizilerde, amaçlanan taşma aralığının herhangi bir nedenle engellendiğini gösteren #TAŞMA! hatasıkullanılır. Engellemeyi çözdüğünüzde formül otomatik olarak taşırılır.

Dizi sabitleri, dizi formüllerinin bileşenlerinden biridir. 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} veya ={"Ocak";"Şubat";"Mart"}

Öğ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 ve her satırı da noktalı virgüllerle sınırlandırmanız gerekir.

Aşağıdaki yordamlar size yatay, dikey ve iki boyutlu sabitler oluşturma konusunda biraz alıştırma yapma olanağı sağlayacaktır. Hem SIRALI işlevini kullanıp dizi sabitlerini otomatik olarak oluşturma hem de el ile dizi sabiti oluşturma örnekleri göstereceğiz.

• Yatay sabit oluşturma

  Önceki örnekte yer alan çalışma kitabını kullanın veya yeni bir çalışma kitabı oluşturun. Boş bir hücre seçin ve =SIRALI(1,5) girin. SIRALI işlevi, ={1;2;3;4;5} ile aynı şekilde 1 satır ve 5 sütun içeren bir dizi oluşturur. Aşağıdaki sonuç görüntülenir:

  

• Dikey sabit oluşturma

  Altında yer olan boş bir hücre seçin ve =SIRALI(5) veya ={1\2\3\4\5} girin. Aşağıdaki sonuç görüntülenir:

  

• İki boyutlu sabit oluşturma

  Sağ tarafında ve altında yer olan boş bir hücre seçin ve =SIRALI(3,4) girin. Aşağıdaki sonucu görürsünüz:

  

  Şunu da girebilirsiniz: ={1;2;3;4\5;6;7;8\9;10;11;12}, ancak virgüller yerine noktalı virgülleri nereye koyacağınıza dikkat etmelisiniz.

  Sizin de görebileceğiniz gibi, SIRALI seçeneği dizi sabit değerlerinizi el ile girmeye göre önemli avantajlar getirir. Öncelikle zaman kazandırır ama el ile giriş yapmaya göre hataları azaltmaya da yardımcı olabilir. Ayrıca, özellikle noktalı virgülleri virgül ayırıcılarından ayırt etmenin zor olması nedeniyle, okunması daha kolaydır.

Burada daha büyük bir formülün parçası olarak dizi sabitlerinin kullanımı gösterilmiştir. Örnek çalışma kitabında Formülde sabit çalışma sayfasına gidin veya yeni bir çalışma sayfası oluşturun.

D9 hücresine =SIRALI(1,5,3,1) girmiştik ama siz A9:H9 hücrelerine 3, 4, 5, 6 ve 7 de girebilirsiniz. Bu belirli sayının seçilmesinin özel bir nedeni yoktur; yalnızca ayırt edilmesi için 1-5’ten farklı bir sayı seçtik.

E11 hücresine =TOPLA(D9:H9*SIRALI(1,5)) veya =TOPLA(D9:H9*{1;2;3;4;5}) girin. Formül 85 sayısını döndürür.

SIRALI işlevi, {1;2;3;4;5} dizi sabitinin eşdeğerini oluşturur. Excel öncelikle parantez içindeki ifadeler üzerinde işlem gerçekleştirdiğinden, işleme katılan sonraki iki öğe D9:H9 aralığındaki hücre değerleri ve çarpım işlecidir (*). Bu noktada, formül depolanan dizideki değerleri sabitte bunlara karşılık gelen değerlerle çarpar. Bunun eşdeğeri şöyledir:

=TOPLA(D9*1,E9*2,F9*3,G9*4,H9*5) veya =TOPLA(3*1,4*2,5*3,6*4,7*5)

Son olarak, TOPLA işlevi değerleri toplar ve 85 sayısını döndürür.

Depolanan diziyi kullanmaktan kaçınmak ve işlemin tamamını bellekte tutmak için, bunu başka bir dizi sabitiyle değiştirebilirsiniz:

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

Dizi sabitlerinde kullanabileceğiniz öğeler

• Dizi sabitleri sayı, metin, mantıksal değer (DOĞRU ve YANLIŞ gibi) ve hata değeri (#YOK gibi) içerebilir. Sayıları tamsayı, ondalık ve bilimsel biçimlerde kullanabilirsiniz. Metin eklerseniz, bunu tırnak işaretleri (“metin”) 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:

Formüller > Tanımlı Adlar > Ad Tanımla seçeneğine gidin. Ad kutusuna Çeyrek1 yazın. Başvuru yeri kutusuna şu sabiti girin (küme ayraçlarını el ile eklemeyi unutmayın):

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

İletişim kutusu şimdi bunun gibi görünmelidir:

Tamam’a tıklayın, üç boş hücresi olan bir satır seçin ve =Çeyrek1 girin.

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

Sonuçların yatay yerine dikey olarak taşmasını istiyorsanız =DEVRİK_DÖNÜŞÜM(Çeyrek1) işlevini kullanabilirsiniz.

Mali tablo oluştururken kullanabileceğiniz gibi 12 ayın listesini oluşturmak istiyorsanız, SIRALI işleviyle bir kerelik geçerli yılı temel alabilirsiniz. Bu işlevin hünerli yanı, yalnızca ay değeri görüntülense bile bu değerin arkasında geçerli bir tarihin olması ve bu tarihi başka hesaplamalarda kullanabilmenizdir. Örnek çalışma kitabının Adlandırılmış dizi sabiti ve Hızlı örnek veri kümesi çalışma sayfalarında bu örnekleri bulabilirsiniz.

=METNEÇEVİR(TARİH(YIL(BUGÜN()),SIRALI(1,12),1),"aaa")

Bu formülde geçerli yılı temel alan bir tarih oluşturmak için TARİH işlevi kullanılır, SIRALI işlevi Ocak’tan Aralık’a kadar olan aylar için 1 ile 12 arası bir dizi sabiti oluşturur, sonra da METNEÇEVİR işlevi görüntü biçimini "aaa" (Oca, Şub, Mar vb.) biçimine dönüştürür. Ocak gibi tam ay adının görüntülenmesini istiyorsanız "aaaa" kullanabilirsiniz.

Adlandırılmış bir sabiti dizi formülü olarak kullandığınızda eşittir işaretini girmeyi unutmayın; yalnızca Çeyrek1 değil =Çeyrek1 girin. Böyle girmezseniz, Excel diziyi bir metin dizesi olarak yorumlar ve formülünüz beklendiği gibi çalışmaz. Son olarak işlev, metin ve sayıların bileşimlerini kullanabileceğinizi de aklınızda bulundurun. Her şey ne kadar yaratıcı olmak istediğinize bağlıdır.

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

  =SIRALI(1,12)*2 veya ={1;2;3;4\5;6;7;8\9;10;11;12}*2 girin

  Ayrıca (/) ile bölme, (+) ile toplama ve (-) ile çıkarma da yapabilirsiniz.

• Dizideki öğelerin karesini alma

  =SIRALI(1,12)^2 veya ={1;2;3;4\5;6;7;8\9;10;11;12}^2 girin

• Dizide karesi alınmış öğelerin karekökünü bulma

  = KAREKÖK (SIRALI(1,12)^2) veya =KAREKÖK({1;2;3;4\5;6;7;8\9;10;11;12}^2) girin

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

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

  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

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

  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

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

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

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

• Varolan değerlerden dizi oluşturma

  Aşağıdaki örnekte varolan diziden yeni bir dizi oluşturmak için dizi formüllerinin nasıl kullanılacağı açıklanır.

  =SIRALI(3,6,10,10) veya ={10;20;30;40;50;60\70;80;90;100;110;120\130;140;150;160;170;180} girin

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

  Ardından, boş bir hücreye =D9# veya =D9:I11 girin. D9:D11’de gördüğünüz değerlerin aynılarıyla bir 3 x 6 hücre dizisi görüntülenir. Buradaki # işareti taşan aralık işleci olarak adlandırılır ve Excel’in dizi aralığını yazmak yerine bu aralığın tamamına başvurma yoludur.

  

• Varolan değerlerden dizi sabiti oluşturma

  Taşan dizi formülünün sonuçlarını alıp bunu bileşen parçalarına dönüştürebilirsiniz. D9 hücresini seçin ve sonra F2 tuşuna basarak düzenleme moduna geçin. Ardından, F9 tuşuna basarak hücre başvurularını değerlere dönüştürün; Excel bu değerleri bir dizi sabitine dönüştürür. Enter tuşuna bastığınızda =D9# formülü artık ={10;20;30\40;50;60\70;80;90} olmalıdır.

• Hücre aralığındaki karakterleri sayma

  Aşağıdaki örnekte bir hücre aralığında yer alan karakterlerin sayısını nasıl hesaplayacağınız gösterilir. Buna boşluklar da dahildir.

  

  =TOPLA(UZUNLUK(C9:C13))

  Bu örnekte, UZUNLUK işlevi aralıktaki hücrelerden her birinin içerdiği metin dizesinin uzunluğunu döndürür. TOPLA işlevi de bu değerleri birbiriyle toplar ve sonucu (66) görüntüler. Karakterlerin ortalama sayısını almak istiyorsanız şunu kullanabilirsiniz:

  =ORTALAMA(UZUNLUK(C9:C13))

• C9:C13 aralığındaki en uzun hücrenin içeriği

  =İNDİS(C9:C13,KAÇINCI(MAK(UZUNLUK(C9:C13)),UZUNLUK(C9:C13),0),1)

  Bu formülün çalışması için veri aralığının tek bir hücre sütunu içeriyor olması gerekir.

  İç öğelerden başlayıp dışarı doğru ilerleyerek formüle daha yakından bakalım. UZUNLUK işlevi, D2:D6 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 D3 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öreli konumunu) hesaplar. Bunu yapmak için üç bağımsız değişkene gerek duyar: aranan değer, aranan dizi ve eşleştir tür. KAÇINCI işlevi, belirtilen aranan değer için aranan dizinde arama yapar. Bu örnekte, aranan değer en uzun metin dizesidir:

  MAK(UZUNLUK(C9:C13)

  ve bu dize şu dizide yer alır:

  UZUNLUK(C9:C13)

  Bu örnekte eşleştirme türü bağımsız değişkeni 0’dır. Eşleştirme türü 1, 0 veya -1 olabilir.

  • 1 - aranan değerden küçük veya buna eşit olan en büyük değeri döndürür

  • 0 - aranan değere tam olarak eşit olan ilk değeri döndürür

  • -1 - belirtilen aranan değerden büyük veya buna eşit olan 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ı. C9:C13 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.

  En küçük metin dizesinin içeriğini almak istiyorsanız, yukarıdaki örnekte MAK işlevini MİN ile değiştirebilirsiniz.

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

  =TAMSAYI(RASGDİZİ(10,1)*100) ile B9:B18 hücrelerinde bir dizi örnek verinin oluşturulduğu bu örnekte, hücre aralığındaki en küçük üç değerin nasıl bulunacağı gösterilir. RASGDİZİ’nin geçici bir işlev olduğuna, dolayısıyla Excel her hesapladığında rastgele sayılardan oluşan yeni bir küme elde edeceğinize dikkat edin.

  

  =KÜÇÜK(B9#,SIRALI(D9), =KÜÇÜK(B9:B18,{1\2\3}) girin

  Bu formülde KÜÇÜK işlevinin üç kez hesaplanması için bir dizi sabiti kullanılır ve B9:B18 hücrelerinde yer alan dizideki en küçük 3 üye döndürülür; burada, D9 hücresindeki 3 bir değişken değeridir. Daha fazla değer bulmak için SIRALI işlevindeki değeri artırabilir veya sabite daha fazla bağımsız değişken ekleyebilirsiniz. Bu formülle birlikte TOPLA veya ORTALAMA gibi ek işlevler de kullanabilirsiniz. Örneğin:

  =TOPLA(KÜÇÜK(B9#,SIRALI(D9))

  =ORTALAMA(KÜÇÜK(B9#,SIRALI(D9))

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

  =BÜYÜK(B9#,SATIR(DOLAYLI("1:3"))) veya =BÜYÜK(B9:B18,SATIR(DOLAYLI("1:3"))) girin

  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 boş bir hücre seçin ve şunu girin:

  =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 şimdi 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şkeni olarak kullanır (1:10 aralığının 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. SIRALI işlevini de kolayca kullanabilirsiniz:

  =SIRALI(10)

  Şimdi daha önce kullandığınız =BÜYÜK(B9#,SATIR(DOLAYLI("1:3"))) formülünü, iç parantezlerden başlayıp dışarı doğru ilerleyerek inceleyelim: 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 B9:B18 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. Daha fazla değer bulmak istiyorsanız, DOLAYLI işlevine daha büyük bir hücre aralığı eklersiniz. Son olarak, aynı KÜÇÜK işlevi örneklerinde olduğu gibi, bu formülü TOPLA ve ORTALAMA gibi başka işlevlerle kullanabilirsiniz.

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

  Excel'deki TOPLA işlevi, #DEĞER! veya #YOK! gibi bir hata değeri içeren bir aralığın toplamını almayı denediğinizde çalışmaz. Bu örnekte Veri olarak adlandırılmış ve hatalar içeren bir aralığın değerlerinin nasıl toplanacağı gösterilir:

  

• =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 toplamanız gerekebilir.

Örneğin, bu dizi formülü yalnızca yukarıdaki örnekte E9:E24 hücrelerini temsil eden 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 2500’den küçük değerleri hesaplar:

=TOPLA((Satış>0)*(Satış<2500)*(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, 0’dan büyük VEYA 2500’den küçük değerleri toplayabilirsiniz:

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

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 formülde gösterilen mantığı kullanarak bu sorunu aşabilirsiniz. Başka bir deyişle, VEYA ya da VE koşuluna uyan değerler üzerinde toplama veya çarpma gibi matematik işlemleri yaparsınız.

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.

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 ve 5 sütundan oluşan bir aralıksa, Verilerin de 3 satır ve 5 sütundan oluşan bir aralık olmalıdır:

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

Formül, karşılaştırdığınız aralıklarla aynı boyutta 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.

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

Örnek çalışma kitabının Veri kümeleri arasındaki farklar çalışma sayfasında benzer örnekler bulabilirsiniz.

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