Dizi formülleriyle ilgili yönergeler ve örnekler

Dizi formülleriyle ilgili yönergeler ve örnekler

Dizi formülü, dizideki bir veya birden çok öğe üzerinde birden çok hesaplama gerçekleştiren bir formüldir. Bir diziyi bir değer satırı veya sütunu ya da değer satırları ve sütunlarının bir kombinasyonu olarak düşünebilirsiniz. Dizi formülleri birden çok sonuç veya tek bir sonuç döndürür.

Microsoft 365 için Eylül 2018 güncelleştirmeden başarak, birden çok sonuç getirebilirsiniz ve bu formüller otomatik olarak aşağı veya komşu hücrelere taşacaktır. Bu davranış değişikliğine birçok yeni dinamik dizi işlevi de eşlik eder. İster var olan işlevleri ister dinamik dizi işlevlerini kullanıyor olsunlar, dinamik dizi formülleri tek bir hücreye girişli, sonra Enter tuşuna basılarak onaylanır. Daha önce, eski dizi formülleri önce çıkış aralığının tamamını seçmeyi, sonra da formülü Ctrl+Shift+Enter tuşlarıyla onaylamayı gerektirir. Bunlar genellikle CSE formülleri olarak adlandırılır.

Aşağıdakiler gibi karmaşık görevleri gerçekleştirmek için dizi formüllerini kullanabilirsiniz:

  • Hızlı şekilde örnek veri kümeleri oluşturun.

  • Bir hücre aralığındaki karakterlerin sayısını sayma.

  • Yalnızca bir aralıkta yer alan en düşük değerler veya bir üst ve alt sınır arasında bulunan sayılar gibi belirli koşulları karşılayacak sayıların toplamını.

  • Değer aralığında her N. değerin toplamını.

Aşağıdaki örneklerde çok hücreli ve tek hücreli dizi formüllerini nasıl oluşturabilirsiniz? Mümkün olduğu sürece, dinamik dizi işlevlerinin yanı sıra hem dinamik hem eski diziler olarak girilen mevcut dizi formüllerine örnekler verilmiştir.

Örneklerimizi indirin

Bu makaledeki tüm dizi formülü örneklerini içeren bir örnek çalışma kitabı indirin.

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ü

    Birim fiyatıyla satılan otomobil sayısını hesaplamak için H10 hücresinde çok hücreli dizi işlevi =F10:F19*G10:G19

  • H10 hücresine =F10:F19*G10:G19 girerek her satış elemanının coupe ve sedan Toplam Satışlarını hesaplarız.

    Enter tuşuna basaraksonuçların H10:H19 hücrelerine taşarak olduğunu görüyorsunuz. Taşma aralığı içindeki herhangi bir hücreyi seçerek, taşma aralığının kenarlıkla vurgulanır. H10:H19 hücrelerinde yer alan formüllerin gri olduğunu da fark edersiniz. Bunlar yalnızca başvuru için burada, dolayısıyla formülü ayarlamak için ana formülün bulunduğu H10 hücresine ihtiyacınız vardır.

  • Tek hücreli dizi formülü

    =TOPLA(F10:F19*G10:G19) ile genel toplamı hesaplamak için tek hücreli dizi formülü

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

    Bu durumda, Excel dizide (F10 ile G19 hücre aralığı) değerleri çarpar ve toplamları bir araya 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ücre deki tek hücreli formülün çok hücreli formülden (H10 - H19 hücreleri) tamamen bağımsız olduğunu da fark edersiniz. Dizi formüllerini kullanmanın bir diğer avantajı da budur: Esneklik. H20 sütunundaki formülü etkilemeden H sütunundaki diğer formülleri değiştirebilirsiniz. Bunun gibi bağımsız toplamlara sahip olmak da iyi bir yöntem olabilir, çünkü sonuçlarınızı doğruluğunu doğrulamaya yardımcı olur.

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

    • Tutarlılık    H10'dan aşağıdan herhangi bir hücreye tıklarsanız aynı formülü görüyorsunuz. Bu tutarlılık daha doğru sonuçlar elde edilmesine yardımcı olabilir.

    • Güvenlik    Çok hücreli dizi formüllerinin bir bileşeninin üzerine yazamusunuz? Örneğin, H11 hücresine tıklayın ve Delete tuşuna basın. Excel, dizinin çıkışını değiştirmez. Bunu değiştirmek için, dizide sol üst hücreyi veya H10 hücresini seçmeniz gerekir.

    • Daha küçük dosya boyutları    Çoğu zaman, birkaç ara formül yerine tek bir dizi formülü kullanabilirsiniz. Örneğin, araba satış örneği E sütunundaki sonuçları hesaplamak için tek bir dizi formülü kullanır. =F10*G10, F11*G11, F12*G12, vb. gibi standart formüller kullandıysanız, aynı sonuçları hesaplamak için 11 farklı formül kullanabiliyor olsaydık. Çok da önemli değil, peki toplamı bin satır olacaksa ne olacak? Böylece 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ü şu formülle aynıdır: =TOPLA(F10*G10,F11*G11,F12*G12,F 13*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şacaktır. Kaynak verileriniz yeni bir tabloda yer Excel, siz veri ekleyeye veya kaldıra kadar dinamik dizi formülleri otomatik olarak yeniden boyutlandırılır.

    • #SPILL! hatasını    Bu hataya neden olan dinamik #SPILL! hatasıherhangi bir nedenden dolayı hedeflenen taşma aralığının engellenmiş olduğunu gösterir. Engeli çözerek, formül otomatik olarak taşacaktır.

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} 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 bir dizi oluşturmak için, her satırdaki öğeleri virgülle sınırlandırma ve her satırı da noktalı virgülle sınırlandırma.

Aşağıdaki yordamlar size yatay, dikey ve iki boyutlu sabit oluşturma konusunda biraz alıştırma yapma olanağı sağlayacaktır. Dizi sabitlerini otomatik olarak oluşturmak ve elle girilen dizi sabitlerini oluşturmak için SEQUENCE işlevini kullanan örnekleri gösterelim.

  • 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 =SıRALı(1,5) girin. SEQUENCE işlevi ={1,2,3,4,5}ile aynı 1 satıra 5 sütunlu bir dizi oluştur. Aşağıdaki sonuç görüntülenir:

    =SEQUENCE(1,5) veya ={1,2,3,4,5} ile yatay dizi sabiti oluşturma

  • Dikey sabit oluşturma

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

    =SEQUENCE(5) veya ={1;2;3;4;5} ile dikey dizi sabiti oluşturma

  • İki boyutlu sabit oluşturma

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

    =SEQUENCE(3,4) ile 3 satıra 4 sütunlu dizi sabiti oluşturma

    Şunları da girebilirsiniz: veya ={1,2,3,4;5,6,7,8;9,10,11,12} ancak noktalı virgülle virgül karşılaştırması yapmak istediğiniz yere dikkat etmek gerekir.

    Gördüğünüz gibi, SıRALA seçeneği dizi sabit değerlerinizi el ile girmeyle karşınıza önemli avantajlar sağlar. Öncelikle, size zaman kazandırır, ancak el ile girişin hatalarını azaltmaya da yardımcı olur. Ayrıca, özellikle de noktalı virgüller virgül ayırıcılarından ayırt etmek zor olduğu için okuması daha kolaydır.

Daha büyük bir formülün parçası olarak dizi sabitlerini kullanan bir örnek şu şekildedir. Örnek çalışma kitabında Formül çalışma sayfasında Sabit'e gidin veya yeni bir çalışma sayfası oluşturun.

D9 hücresine =SıRALı(1,5,3,1)formül girildi, ancak A9:H9 hücrelerine de 3, 4, 5, 6 ve 7 girilebilir. Bu özel sayı seçiminde özel bir şey yok, biz fark için 1-5 dışında bir seçim seçtik.

E11 hücresine =TOPLA(D9:H9*SıRALı(1,5))veya =TOPLA(D9:H9*{1,2,3,4,5})girin. Formüller 85'i geri verir.

Formüllerde dizi sabitleri kullanın. Bu örnekte biz =TOPLA(D9:H(*SEQUENCE(1,5))

SEQUENCE işlevi {1,2,3,4,5} dizi sabitin eşdeğerini oluşturmektedir. Bu Excel önce parantez içindeki ifadeler üzerinde işlem gerçekleştir olduğundan, işlem yapan sonraki iki öğe D9:H9'daki hücre değerleri ve çarpma işleci (*)dir. 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 ekler ve 85 döndürür.

Depolanan diziyi kullanmaktan kaçınmak ve işlemi tamamen bellekte tutmak için, başka bir dizi sabitiyle değiştirebilirsiniz:

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

Dizi sabitleri içinde kullanabileceğiniz öğeler

  • Dizi sabitleri sayılardan, metinlerden, mantıksal değerlerden (DOĞRU ve YANLIŞ gibi) ve #N içerebilir. Sayıları tamsayı, ondalık ve bilimsel biçimlerde kullanabilirsiniz. Metin eklemek için, 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ı isimlerini kullanmaktı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 'e > Tanımlı Adlar'a >Tanımla'ya tıklayın. 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 şöyle görünür:

Formüller ve Tanımlı Adlar'dan > dizi sabiti > Ad Yöneticisi > Yeni

Tamam'atıklayın, üç boş hücre içeren bir satır seçin ve =Çeyrek1 girin.

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

Formülde, =Çeyrek1 gibi adlandırılmış bir dizi sabiti kullanın; burada Çeyrek1 ={"Ocak","Şubat","Mart"} olarak tanımlanmıştır

Sonuçların yatay değil dikey olarak taşmalarını tercih ediyorsanız =DEPOSE(Çeyrek1) ifadesini kullanabilirsiniz.

Mali deyim oluşturulurken kullanabileceğiniz gibi 12 aylık bir listeyi görüntülemek için, SEQUENCE işleviyle geçerli yılı temel aya atabilirsiniz. Bu işlevle ilgili en güzel şey, yalnızca ay görüntüleniyor olsa bile arkasında geçerli bir tarih olmasıdır ve bu tarihi diğer hesaplamalarda kullanabilirsiniz. Bu örnekleri örnek çalışma kitabındaki Adlandırılmış dizi sabiti ve Hızlı örnek veri kümesi çalışma sayfalarında bulabilirsiniz.

=METNEÇEMİr(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

12 aya sahip dinamik bir liste oluşturmak için METNEZİ, TARIH, YıL, BUGÜN ve SıRALı işlevlerinin bir bileşimini kullanın

Bu, geçerli yılı temel alan bir tarih oluşturmak için DATE işlevini kullanır; SEQUENCE işlevi Ocak ile Aralık ayları için 1 ile 12 arasında bir dizi sabiti oluşturur, ardından METNEÇEVİr işlevi görüntü biçimini "mmm" (Oca, Şub, Mar vb.) biçimine dönüştürür. Tam ay adını (örneğin Ocak) görüntülemek için "mmmm" kullanabilirsiniz.

Adlandırılmış bir sabiti dizi formülü olarak kullanırsanız, eşittir işareti girmeyi unutmayın; yalnızca Çeyrek1 değil, =Çeyrek1. Girmezseniz, Excel diziyi bir metin dizesi olarak yorumlar ve formülünüz beklendiği şekilde çalışmaz. Son olarak, işlev, metin ve sayı bileşimlerini kullanabileceğiniz unutmayın. Tüm bunlar 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 için DE DÖNÜŞTÜR işlevi kullanılır.

  • Dizide birden çok öğe

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

    Ayrıca (/), birlikte (+) ile ekleyip ( - ) ilede çıkararak çıkarsınız.

  • Dizideki öğelerin karesini alma

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

  • Dizideki karekök öğelerin kare kökünü bulma

    Enter =SQRT(SEQUENCE(1,12)^2)veya =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

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

    =DEPOSE(SEQUENCE(1,5))veya =DEPOSE({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

    =DEPOSE(SEQUENCE(5;1))veya =DEPOSE({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

    =DEPOSE(SEQUENCE(3,4))veya =DEPOSE({1,2,3,4;5,6,7,8;9,10,11,12}) girin.

    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 dizi oluşturma

    Aşağıdaki örnekte, var olan bir diziden yeni dizi oluşturmak için dizi formüllerini kullanmanın nasıl açık olduğu açık dizisidir.

    =SEQUENCE(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 girdikten sonra } (kapanış küme ayracı) yazmayı sağlar, çünkü bir sayı dizisi oluşturuyoruz.

    Ardından, boş bir hücreye =D9#veya =D9:I11 girin. 3 x 6 hücre dizisi, D9:D11'de gördüğünüz değerlerle görüntülenir. # işaretine spilled rangeişleci denir ve Excel yerine tüm dizi aralığına başvurmak için bir yol sağlar.

    Mevcut bir diziye başvuru yapmak için taşan aralık işlecini (#) kullanın

  • Varolan değerlerden dizi sabiti oluşturma

    Bir dizi formülünü taşmış formülün sonuçlarını alıp bileşen bölümlerine dönüştürebilirsiniz. D9 hücresi seçin ve düzenleme moduna geçmek için F2 tuşuna basın. Ardından, F9 tuşuna basarak hücre başvurularını değerlere, Excel dizi sabite dönüştürür. Entertuşuna basıyorken =D9# formülü ={10,20,30;40,50,60;70,80,90} olmalıdır.

  • Hücre aralığındaki karakterleri sayma

    Aşağıdaki örnekte, hücre aralığındaki karakterlerin sayısını nasıl saymanız gerekir? Buna boşluklar da dahildir.

    Bir aralıkta toplam karakter sayısını ve metin dizeleriyle çalışmak için diğer dizileri sayma

    =TOPLA(UZUNLUK(C9:C13))

    Bu durumda, UZUNLUK işlevi aralıkta yer alan hücrelerden her bir metin dizesinin uzunluğunu verir. TOPLA işlevi de bu değerleri bir araya toplar ve sonucu (66) görüntüler. Ortalama sayıda karakter elde etmek için şunları kullanabilirsiniz:

    =ORTALAMA(UZUNLUK(C9:C13))

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

    =İnDİk(C9:C13,EŞLEM(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 öğelerin her biri uzunluğunu döndürür. MAK işlevi bu öğeler arasındaki en uzun değeri hesaplar; bu değer de D3 hücresinde yer alan en uzun metin dizesine karşılık gelen değerdir.

    Bu aşamada işler biraz karmaşık hale gelir. KAÇLIK işlevi en uzun metin dizesini içeren hücrenin uzak konumunu (göreli konumu) 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(C9:C13)

    ve bu dize şu dizide yer alır:

    UZUNLUK(C9:C13)

    Bu durumda eşleşme türü bağımsız değişkeni 0'dır. Eşleşme türü 1, 0 veya -1 değeri olabilir.

    • 1 - aranan değerden küçük veya bu değere eşit olan en büyük değeri verir

    • 0 - arama değerine tam olarak eşit olan ilk değeri verir

    • -1 - Belirtilen arama değerinden büyük veya bu değere eşit olan en küçük değeri döndürür

    • Eşleştirme türünü atlarsanız, Excel bunun 1 olduğunu varsayar.

    Son olarak, İnD IŞLEVI şu bağımsız değişkenleri alır: dizi ve bu dizinin içindeki bir satır ve sütun numarası. C9:C13 hücre aralığı diziyi sağlar, EŞ BAŞKAŞTIR işlevi hücre adresini sağlar ve son bağımsız değişken (1) değerin dizinin ilk sütunundan geldiğinden kaynak olduğunu belirtir.

    En küçük metin dizesinin içeriğini almak için yukarıdaki örnekte yer alan MAK yerine MIN yazın.

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

    Bu örnekte, B9:B18 hücrelerinde bir örnek veri dizisinin şu şekilde oluşturulmuş olduğu bir hücre aralığındaki en küçük üç değerin nasıl bulunuz: =TAMSAYI( TAMSAYI(10,1)*100) . RANDARRAY'ın geçici bir işlev olduğunu unutmayın; bu nedenle, her hesapta yeni bir Excel olur.

    Excel en küçük değerini bulmak için dizi formülünü kullanın: =KÜÇÜK(B9#,SıRALı(D9))

    =KÜÇÜK(B9#,SıRALı(D9), =KÜÇÜK(B9:B18,{1;2;3})

    Bu formül KÜÇÜK işlevini üç kez değerlendirmek için dizi sabiti kullanır ve B9:B18 hücrelerinde yer alan dizinin en küçük 3 üyesini döndürür; burada 3, D9 hücresinde değişken bir değerdir. Daha fazla değer bulmak için, SEQUENCE işlevinde değeri artırabilir veya 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(B9#,SıRALı(D9))

    =ORTALAMA(KÜÇÜK(B9#,SıRALı(D9))

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

    Aralıkta 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#,SATıR(DOLAYLI("1:3")))veya =BÜYÜK(B9:B18,SATıR(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, boş bir alan seçin ve şunları girin:

    =SATIR(1:10)

    Formül birbirini takip eden 10 tamsayı içeren bir sütun oluşturur. Olası bir sorunu görmek için, dizi formülünü içeren aralığın üstüne (yani, 1. satırın üstüne) satır eklersiniz. Excel, satır başvurularını ayarlar ve formül artık 2 ile 11 arasında 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 tırnak içine alınma nedeni de 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. SEQUENCE'i aynı şekilde kolayca kullanabilirsiniz:

    =SEQUENCE(10)

    Şimdi, iç parantezlerden başlayarak ve dışarı doğru çalışırken daha önce =BÜYÜK(B9#,SATıR(DOLAYLI("1:3"))) formülü incelenin: DOLAYLI işlevi bir metin değerleri kümesi döndürür (bu durumda 1 ile 3 arasında değerler). SATıR işlevi de üç hücreli bir sütun dizisi üretir. BÜYÜK işlevi B9:B18 hücre aralığındaki değerleri kullanır ve ROW işlevi tarafından döndürülen her başvuru için bir kez, üç kez değerlendirilir. Daha fazla değer bulmak için DOLAYLI işlevine daha fazla hücre aralığı eklersiniz. Son olarak, KÜÇÜK örneklerde olduğu gibi, bu formülü TOPLA ve ORTALAMA gibi diğer işlevlerle de kullanabilirsiniz.

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

    Aşağıdaki hücrelerde Excel işlevi, aşağıdaki gibi hata değeri içeren bir aralığın toplamını #VALUE! veya #N/A. Bu örnekte, Veri olarak adlandırılmış ve hatalar içeren bir aralıkta yer alan değerlerin nasıl toplan olduğu gösterir:

    Hatalarla uğraşmak için dizileri kullanın. Örneğin, =TOPLA(EĞER(EHATALISA(Veri),"",Veri) veri adlı aralığın toplamını (örneğin, hata içeriyorsa) #VALUE! ya da #NA!.

  • =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üle benzer, ancak Veri adlı aralıkta, filtre uygulama yerine hata değerlerinin 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.

Dizileri belirli koşulları temel alarak hesaplamak için kullanabilirsiniz. =TOPLA(EĞER(Satışlar>0,Satış)) Satış adlı aralıkta 0'dan büyük olan tüm değerlerin toplamını alır.

Örneğin, bu dizi formülü yalnızca Satış adlı aralıkta yer alan ve yukarıdaki örnekteki E9:E24 hücrelerini temsil eden pozitif tamsayıların toplamını almaktadır:

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

EĞER işlevi pozitif ve yanlış değerlerin 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ğerlerin toplamını hesapebilirsiniz:

=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 örnekte gösterilen mantığı kullanarak bu sorunu aşabilirsiniz. Başka bir deyişle, YADA veya VE koşuluna uygun değerler üzerinde toplama veya çarpma gibi matematik işlemleri gerçekleştirebilirsiniz.

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ı boyutta ve boyutta olması gerekir. Örneğin, Verilerim 3 satıra 5 sütun içeren bir aralıksa, Veriniz de 3 satıra 5 sütun olmak gerekir:

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

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ığına karşılık gelen yeni bir dizi oluşturur. Aralıkta karşılık gelen bir hücre en yüksek değeri içeriyorsa, dizi satır numarasını içerir. Bunun dışında, dizi boş dize ("") içerir. Mİn işlevi yeni diziyi ikinci bağımsız değişkeni olarak kullanır ve Veri'de en büyük değerin satır numarasına karşılık gelen en küçük değeri döndürür. Veri adlı aralık aynı en yüksek değerleri içeriyorsa, formül ilk değerin satırına 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ında benzer örnekleri Veri kümeleri arasındaki farklar çalışma sayfasında 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

Araba Türü

Satılan Numara

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ışlarını görmek için, E2:E11 hücrelerini seçin, =C2:C11*D2:D11formülünü girin ve ardından Ctrl+Shift+Entertuşlarına basın.

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

Ctrl+Shift+Entertuşlarına bas Excel formülü küme ayracı ({ }) içine alın ve seçili aralığın her hücresine 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}

E sütunundaki toplamlar bir dizi formülüyle hesaplanmaktadır

  • 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 durumda, Excel dizide (C2 ile D11 hücre aralığı) değerleri çarpar ve toplamları bir araya 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ücresi'deki tek hücreli formülün çok hücreli formülden (E2 ile E11 hücreleri arasında yer alan formül) tümüyle bağımsız olduğunu da fark edersiniz. Dizi formüllerini kullanmanın bir diğer avantajı da budur: Esneklik. D13'deki formülü etkilemeden E sütunundaki formülleri değiştirebilir veya bu sütunu tamamen 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ülde yapılan değişikliği onaylamak için Ctrl+Shift+Enter tuşlarına basmanız gerekir.

  • Daha küçük dosya boyutları    Çoğu zaman, birkaç ara formül yerine tek bir dizi formülü kullanabilirsiniz. Örneğin, çalışma kitabı E sütunundaki sonuçları hesaplamak için tek bir dizi formülü kullanır. Standart formüller (=C2*D2, C3*D3, C4*D4...) gibi kullanılmış olsaydı, aynı sonuçları hesaplamak için 11 farklı formül kullan olsaydı.

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. En önemli fark, dizi formülü kullanırken formülü girmek için Ctrl+Shift+Enter tuşlarına basmanızdır. 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).

Önemli: Dizi formülü girmeniz gereken her zaman Ctrl+Shift+Enter tuşlarına basın. Bu, hem tek hücreli hem de çok hücreli formüller için geçerlidir.

Ç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), sonraDelete tuşuna basın.

  • Çok hücreli dizi formülüne boş hücre ek olamaz veya hücreleri silemezsiniz.

Kimi zaman dizi formülünü genişletmeniz gerekebilir. Var olan dizi aralığındaki ilk hücreyi seçin ve formülü genişletmek istediğiniz aralığın tamamını seçene kadar devam eder. Formülü düzenlemek için F2 tuşuna basın, ardından formül aralığını ayarlayan formülü onaylamak için CTRL+SHIFT+ENTER tuşlarına basın. Önemli olan, dizide sol üst hücreden başlayarak tüm aralığı seçmektir. Sol üst hücre, düzenlenen hücredir.

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

  • Zaman zaman Ctrl+Shift+Enter tuşlarına bas girmeyi unutun. 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şka kişilerin çalışma kitaplarınızı değiştirmesi gerekirse dizi formüllerini kullanmamalı veya bu kişilerin dizi formüllerini olduğundan ve gerekirse bunları nasıl değiştir olduklarını anlayalı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ülleri oluşturmak için Ctrl+Shift+Enter tuşlarına basmanız gerek olduğunu biliyorsunuz. Dizi sabitleri dizi formüllerinin bir bileşeni olduğundan, sabitleri kendiniz yazarak el ile küme ayracı içine alırsınız. Ardından, formülün tamamını girmek için Ctrl+Shift+Enter tuşlarını kullanırsanı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 tek satırda 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 da 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ında 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ı ({ }) yazmalı ve Excel ikinci kümeyi sizin için eklesin.

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

    Formülde yatay dizi sabiti

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.

    Dizi formülünde dikey dizi sabiti

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

    Dizi formülünde iki boyutlu dizi sabiti

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.

    Dizi sabiti içeren dizi formülü

    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.

Dizi sabiti içeren dizi formülünün sözdizimi

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 sabiti ekledikten sonra formülü girmek için Ctrl+Shift+Enter tuşlarına basacaklarını 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 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ı isimlerini kullanmaktı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:

    Formülü içeren Ad Tanımla iletişim kutusu

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

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

    =Çeyrek1

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

    Formül olarak girilen adlandırılmış dizi

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 atlarsanız ya da bunlardan birini yanlış yere koysanız, dizi sabiti doğru oluşturulmayabilirsiniz veya bir uyarı iletisi alabilirsiniz.

  • 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 için DE DÖNÜŞTÜR işlevi kullanılı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ü kullanarak bu sayı dizisini C8:E10 hücre aralığına giren Ctrl+Shift+Entertuş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ında yer alan ve C8 ile E10 arasında geçen hücrelerde gördüğünüz değerlerle 3x3'lık bir hücre dizisi görünür.

Varolan değerlerden dizi sabiti oluşturma

  1. C1:C3 hücreleri seçiliyken, düzenleme moduna geçmek için F2 tuşuna basın. 

  2. F9'a basarak hücre başvurularını değerlere dönüştürebilirsiniz. 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 sabiti 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ücresi seçin ve ardından A2:A6 (66) hücrelerinde toplam karakter sayısını görmek için Ctrl+Shift+Enter tuşlarına basın.

  3. A10 hücresine tıklayın ve ardından A2:A6 hücrelerinin en uzun olanlarının (A3 hücresi) 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ında yer alan hücrelerde toplam karakter sayısını (66) sayar.

=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 bir araya 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 rastgele sayılar girin.

  2. C1 ile C3 arasında yer alan 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 değerlendirmek için dizi sabiti kullanır ve A1:A10 hücrelerinde yer alan 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ında yer alan hücreleri seçin.

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

    =BÜYÜK(A1:A10,SATıR(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 sonra Ctrl+Shift+Enter tuşlarına basın:

=SATIR(1:10)

Formül birbirini takip eden 10 tamsayı içeren bir sütun oluşturur. Olası bir sorunu görmek için, dizi formülünü içeren aralığın üstüne (yani, 1. satırın üstüne) satır eklersiniz. Excel başvurularını ayarlar ve formül 2 ile 11 arasında 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.

İç parantezlerden ve dışarı doğru çalışırken daha önce =BÜYÜK(A5:A14,DOLAYLI(DOLAYLI("1:3"))) kullanılan formüle bir göz atabilirsiniz: DOLAYLI işlevi bir metin değerleri kümesi döndürür (bu durumda 1 ile 3 arasında değerler). SATıR işlevi de üç hücreli sütunlu bir dizi üretir. BÜYÜK işlevi A5:A14 hücre aralığındaki değerleri kullanır ve ROW işlevi tarafından döndürülen her başvuru için bir kez, üç kez değerlendirilir. 3200, 2700 ve 2000 değerleri üç hücreli sütunlu diziye döndürülür. Daha fazla değer bulmak için DOLAYLI işlevine daha fazla hücre aralığı eklersiniz.

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

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

Önceki metin dizesi örneğine geri gidin, boş bir 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)

"Bu hücrelerden grup" 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 öğelerin her biri uzunluğunu döndürür. MAK işlevi bu öğeler arasındaki en uzun değeri hesaplar; bu değer A3 hücresinde yer alan en uzun metin dizesine karşılık gelen değerdir.

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, EŞ BAŞKAŞTIR işlevi hücre adresini sağlar ve son bağımsız değişken (1), değerin dizinin ilk sütunundan gelip geldiğinden bunu 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<>Verilerinizi))

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ığına karşılık gelen yeni bir dizi oluşturur. Aralıkta karşılık gelen bir hücre en yüksek değeri içeriyorsa, dizi satır numarasını içerir. Bunun dışında, dizi boş dize ("" )içerir. Mİn işlevi yeni diziyi ikinci bağımsız değişkeni olarak kullanır ve Veri'de en büyük değerin satır numarasına karşılık gelen en küçük değeri döndürür. Veri adlı aralık aynı en yüksek değerleri içeriyorsa, formül ilk değerin satırına 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))

Onay

Bu makalenin bölümleri, Colin Wilcox tarafından yazılan ve Excel 2002 Formülleri'nin 14. ve 15. parçalarından uyarlanmış, eski bir MVP olan John Walken bir kitap olan Excel Power User sütunlarını temel Excel temel almıştır.

Daha fazla yardım mı gerekiyor?

Sorularınızı Excel Teknoloji Topluluğu’ndaki uzmanlara sorabilir, Yanıt topluluğu’ndan destek alabilir ya da Excel için UserVoice platformuna yeni bir özellik veya iyileştirme önerisi sunabilirsiniz.

Ayrıca Bkz:

Dinamik diziler ve geçmiş dizi davranışı

Eski CSE dizi formülleri ile karşıt dinamik dizi formülleri

FİLTRE işlevi

RASGDİZİ işlevi

SIRALI işlevi

SIRALA işlevi

SIRALAÖLÇÜT işlevi

BENZERSİZ işlevi

Excel’de #SPILL! hataları

Örtük kesişim işleci: @

Formüllere genel bakış

Daha fazla yardıma mı ihtiyacınız var?

Office yeteneklerinizi geliştirin
Eğitimleri keşfedin
Yeni özellikleri ilk olarak siz edinin
Office Insider Programına Katılın

Bu bilgi yararlı oldu mu?

Görüşleriniz için teşekkür ederiz!

Geri bildiriminiz için teşekkürler! Office destek temsilcilerimizden biriyle görüşmeniz yararlı olabilir.

×