Dizi formülleriyle ilgili yönergeler ve örnekler

Dizi formülleriyle ilgili yönergeler ve örnekler

Not: En güncel yardım içeriklerini, mümkün olduğunca hızlı biçimde kendi dilinizde size sunmak için çalışıyoruz. Bu sayfanın çevirisi otomasyon aracılığıyla yapılmıştır ve bu nedenle hatalı veya yanlış dil bilgisi kullanımları içerebilir. Amacımız, bu içeriğin sizin için faydalı olabilmesini sağlamaktır. Buradaki bilgilerin faydalı olup olmadığını bu sayfanın sonunda bize bildirebilir misiniz? Kolayca başvurabilmek için İngilizce makaleye buradan ulaşabilirsiniz.

Dizi formülü, bir dizideki bir veya birden çok öğe üzerinde birden çok hesaplama gerçekleştiren formüldür. Bir diziyi bir satır veya sütun değerleri ya da bir satır ve sütun değerleri birleşimi olarak düşünebilirsiniz. Dizi formülleri birden çok sonuç veya tek bir sonuç döndürebilir.

Eylül 2018 365 güncelleştirmesi ile başlayarak, birden çok sonuç döndürebileceğiniz tüm formüller otomatik olarak bunları aşağı veya bitişik hücrelere taşıacaktır. Bu davranışa çeşitli yeni dinamik dizi işlevleride birlikte yayımlanır. Dinamik dizi formülleri, var olan işlevleri veya dinamik dizi işlevlerini kullanıyor olsalar da yalnızca tek hücreye giriş yapmanız ve ardından ENTERtuşuna basılarak onaylanmaları gerekir. Daha önce, eski dizi formülleri önce tüm çıkış aralığını seçmek ve sonra CTRL + SHIFT + enterile formülü onaylamak için. Genellikle CSE formülleri olarak da adlandırılır.

Dizi formüllerini kullanarak karmaşık görevleri gerçekleştirebilirsiniz:

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

  • Bir hücre aralığında yer alan karakter sayısını sayma.

  • Yalnızca aralıktaki en düşük değerler veya bir üst ve alt sınır arasında kalan sayılar gibi belirli koşulları karşılayan sayıları toplama.

  • Değer aralığındaki her nth değerini topla.

Aşağıdaki örneklerde birden çok hücreli ve tek hücreli dizi formüllerinin nasıl oluşturulacağı gösterilmektedir. Mümkün olduğunda, dinamik dizi işlevlerinden bazılarının yanı sıra hem dinamik hem de eski diziler olarak girilmiş dizi formüllerinin örneklerini de ekledik.

Örneklerimizi indirin

Bu makaledeki tüm dizi formülü örnekleri ile birlikte örnek bir ç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ü

    H10 = F10 hücresinde çok hücreli dizi işlevi birim fiyata göre satılan araba sayısını hesaplamak için

  • Burada, G10 hücresinde = F10: f19 *: G19 girerek her satışçının toplam kupın ve sedan satışları hesaplanıyor.

    ENTERtuşuna bastığınızda, sonuçların H10: H19 hücrelerine taşma olduğunu 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. H10: H19 hücrelerindeki formüllerin gri renkte olduğunu fark edebilirsiniz. Yalnızca başvuru için orada olduğu gibi, formülü ayarlamak istiyorsanız, ana formülün yer aldığı H10 hücresini seçmeniz gerekir.

  • Tek hücreli dizi formülü

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

    Örnek çalışma kitabının H20 yazmak hücresinde, kopyalayıp yapıştırın = topla (F10: F19 * G10: G19)ve ENTERtuşuna basın.

    Bu durumda, Excel dizideki değerleri (F10-G19 hücre aralığı) çarpar 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 yazmak hücresindeki tek hücreli formülün, çok hücreli formülden (H10-H19 hücreleri) tamamen bağımsız olduğuna dikkat edin. Dizi formüllerini kullanmanın bir diğer avantajı da budur: Esneklik. H20 yazmak 'daki formülü etkilemeden, H sütunundaki diğer formülleri değiştirebilirsiniz. Bu, sonuçlarının doğruluğunu doğrulamaya yardımcı olduğundan, benzer şekilde bağımsız toplamlar yapmak iyi olabilir.

  • Dinamik dizi formüllerinde bu avantajları da sunmaktadır:

    • Tutarlılık    H10 aşağı doğru hücreleri 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 bir bileşeninin üzerine yazamazsınız. Örneğin, H11 hücresini tıklatın ve DELETE tuşuna basın. Excel dizinin çıkışını değiştirmeyecektir. Değiştirmek için, dizide veya H10 hücresinde sol üst hücreyi seçmeniz gerekir.

    • Daha küçük dosya boyutları    Birçok ara formül yerine genellikle tek bir dizi formülü kullanabilirsiniz. Örneğin, araba satışları örneği E sütunundaki sonuçları hesaplamak için tek bir dizi formülü kullanır. = F10 * G10, F11 * G11, F12 * G12 gibi standart formüller kullandıysanız, aynı sonuçları hesaplamak için 11 farklı formül kullandınız. Bu büyük bir işlem değil, ancak toplam binlerce satır istiyorsanız ne yapmalıyım? Böylece, büyük bir fark yapabilirsiniz.

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

    • Kalıncaya    Dinamik dizi formülleri otomatik olarak çıktı aralığına taşıacaktır. Kaynak verileriniz bir Excel tablosunda yer alıyorsa, siz verileri eklerken veya kaldırırken dinamik dizi formülleriniz otomatik olarak yeniden boyutlandırılır.

    • #SPILL! hatalar   #SPILL! hatası tanıtılan dinamik diziler, bir nedenle, hedeflenen taşma aralığının engellendiğini belirtir. Blockage 'i çözümlediğinizde, formül otomatik olarak taşma olur.

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üllerle sınırlandırın ve her satırı noktalı virgüllerle sınırlandırın.

Aşağıdaki yordamlar size yatay, dikey ve iki boyutlu sabit oluşturma konusunda biraz alıştırma yapma olanağı sağlayacaktır. El ile dizi sabitlerinin yanı sıra dizi sabitlerini otomatik olarak oluşturmak için dizi işlevini kullanan ö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ücreyi seçin ve = Sequence (1, 5)girin. DIZI işlevi, = {1, 2, 3, 4, 5}ile aynı olan 1 satırlık bir sütun dizisi oluşturur. Aşağıdaki sonuç görüntülenir:

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

  • Dikey sabit oluşturma

    Altında oda bulunan boş bir hücre seçin ve = Sequence (5)veya = {1; 2; 3; 4; 5}. 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ğda ve altında odadaki boş hücreleri seçin ve = Sequence (3, 4)girin. Aşağıdaki sonucu görürsünüz:

    = SıRASıYLA 3 satır 3 sütunlu bir satır sabiti oluşturma (3, 4)

    Ayrıca, veya = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} (veya

    Görebileceğiniz gibi, dizi seçeneği, dizi sabit değerinizi el ile girerek önemli avantajları sağlar. Öncelikle size zaman kazandırır, ancak el ile girişten hataları azaltmaya da yardımcı olur. Daha da kolay okunması, özellikle de noktalı virgüller olarak virgül ayırıcılarının ayırt edilmesi zor olabilir.

Daha büyük bir formülün parçası olarak dizi sabitlerini kullanan bir örnek aşağıda verilmiştir. Örnek çalışma kitabında, Formül çalışma sayfasında Sabitte veya yeni bir çalışma sayfası oluşturun.

D9 hücresinde = Sequence (1, 5, 3, 1), ancak A9: H9 arasında hücrelerine 3, 4, 5, 6 ve 7 girebilirsiniz. Bu belirli numara seçimindeki özel hiçbir şey yok, farklıma için 1-5 dışında bir şeyler seçiyoruz.

E11 hücresine = topla (D9: H9 arasında * Sequence (1, 5))veya = topla (D9: H9 arasında * {1, 2, 3, 4, 5})girin. Formüller 85 döndürür.

Formüllerde dizi sabitleri kullanma. Bu örnekte, = topla (D9: H (* SEQUENCE (1, 5)).

Dizi işlevi {1, 2, 3, 4, 5} dizi sabitinin eşdeğerini oluşturur. Excel öncelikle parantez içinde yer alan ifadelerde işlemler gerçekleştirdiğinden, yürütmeye gelen sonraki iki öğe D9: H9 arasında ve çarpma işlecinin (*) hücre değerleridir. 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 arasında * 5)veya = topla (3 * 1, 4 * 2, 5 * 3, 6 * 4, 7 * 5)

Son olarak, topla işlevi değerleri toplar ve 85 değerini döndürür.

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

= Topla (dizi (1, 5, 3, 1) * dizi (1,2))veya = topla ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Dizi sabitlerinde kullanabileceğiniz öğeler

  • Dizi sabitleri sayı, metin, mantıksal değerler (doğru ve yanlış gibi) ve #N/agibi hata değerleri içerebilir. Sayıları, ondalık ve bilimsel biçimlerde kullanabilirsiniz. Metin eklerseniz, bunu tırnak işaretleriyle ("metin") çevreetmeniz 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. 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:

_GT_ tanımlı adlar _GT_ ad tanımla'ya 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 şöyle görünmelidir:

Formüllerden adlandırılmış bir dizi sabiti ekleme > tanımlı adlar > ad Yöneticisi > yeni

Tamam'ı tıklatın, sonra üç boş hücre içeren herhangi bir satırı seçin ve = Çeyrek1yazın.

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

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

Sonuçların yatay yerine dikey olarak Kaydırılaği istiyorsanız =devrik(Çeyrek1)kullanabilirsiniz.

Finansal bir ekstreyi oluştururken kullandığınız şekilde 12 aylık bir liste görüntülemek istiyorsanız, geçerli yıl dışında bir sıra işlevi oluşturabilirsiniz. Bu işlev hakkında nete, yalnızca ayın görüntülenmesine rağmen, başka hesaplamalarda kullanabileceğiniz geçerli bir tarih vardır. Bu örnekleri, adlandırılmış dizi sabitinde ve örnek çalışma kitabındaki Hızlı örnek veri kümesi çalışma sayfalarında bulabilirsiniz.

= METNEÇEVIR (DATE (YEAR (Bugün ()), dızı (1, 12), 1), "aaa")

12 ayın dinamik listesini oluşturmak için metın, TARIH, yıl, bugün ve sıralı işlevlerinin birleşimini kullanın

Bu, geçerli yılı temel alan bir tarih oluşturmak için Tarih işlevini KULLANıR, dizi 1 ila 12 arasında bir dizi sabiti oluşturur ve ardından METNEÇEVİR işlevi görüntü biçimini "aaa" (Ocak, Şub, Mar vb.) olarak dönüştürür. Ocak gibi tam ay adını görüntülemek isterseniz "aaaa" ifadesini kullanırsınız.

Bir adlandırılmış sabiti dizi formülü olarak kullandığınızda, eşittir işaretini yalnızca Çeyrek1 değil, = Çeyrek1 olarak girmeyi unutmayın. 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ı birleşimlerini kullanabileceğiniz unutmayın. Bu tümün nasıl elde etmek istediğinize bağlıdır.

Aşağıdaki örneklerde dizi sabitlerini dizi formüllerinde kullanabileceğiniz yollardan birkaç gösterilir. Bazı örneklerde, satırları sütunlara dönüştürmek ve tersine çevirmek için devrik işlevi kullanılır.

  • Dizideki birden çok öğe

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

    Ayrıca (/) ile bölerek, (+) ile (-) ile bölebilirsiniz.

  • Dizideki öğelerin karesini alma

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

  • Bir dizideki kare içinde öğelerin karekökünü bulma

    ENTER =karekök(dizi (1, 12) ^ 2)veya = karekök ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)

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

    ENTER = devrik (dizi (1, 5))veya = devrik ({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

    ENTER = devrik (dizi (5, 1))veya = devrik ({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

    ENTER = devrik (dizi (3, 4))veya = devrik ({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 dizi oluşturma

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

    ENTER = dizi (3, 6, 10, 10)veya = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    180 yazdıktan sonra, bir sayı dizisi oluştururken, yazdıktan sonra {(açılış küme ayracı) ve} (kapanış küme ayracı) yazdığınızdan emin olun.

    Ardından, boş bir hücreye = D9 #veya = D9: I11 girin. 3 x 6 hücre dizisi, D9: D11 'te gördüğünüz değerlerle aynı değerlerle görüntülenir. # Sign, taşmış Aralık işleciolarak adlandırılır ve bu, yazmak zorunda kalmadan tüm dizi aralığına başvuru yoludur.

    Var olan bir diziye başvurmak için, sığmayan Aralık işlecini (#) kullanın

  • Varolan değerlerden dizi sabiti oluşturma

    Bir sığmayan dizi formülünün sonuçlarını alabilir ve bunu bileşen bölümlerine dönüştürebilirsiniz. D9 hücresini seçin ve ardından F2 tuşuna basarak düzenleme moduna geçin. Ardından, hücre başvurularını değerlere dönüştürmek için F9 tuşuna basın; böylece Excel bir dizi sabitine dönüşür. ENTERtuşuna bastığınızda, = 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, bir hücre aralığındaki karakter sayısını sayma gösterilmektedir. Bu, boşluklar içerir.

    Aralıktaki toplam karakter sayısını sayma ve metin dizeleriyle çalışmaya yönelik diğer diziler

    = TOPLA (LEN (C9: C13))

    Bu durumda, Uzunluk işlevi , aralıktaki her hücrede bulunan her bir metin dizesinin uzunluğunu döndürür. TOPLA işlevi bu değerleri birbirine ekler ve sonucu görüntüler (66). Ortalama karakter sayısını almak isterseniz şunları kullanabilirsiniz:

    = ORTALAMA (LEN (C9: C13))

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

    = INDIS (C9: C13, MATCH (MAX (LEN (C9: C13)), LEN (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. Len işlevi , D2: D6 hücre aralığındaki öğelerin her birinin uzunluğunu verir. Max işlevi , D3 hücresindeki en uzun metin dizesine karşılık gelen öğelerin arasındaki en büyük değeri hesaplar.

    Bu aşamada işler biraz karmaşık hale gelir. Kaçıncı işlevi , hücrenin en uzun metin dizesini içeren uzaklığını (göreli konum) 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:

    MAX (LEN (C9: C13)

    ve bu dize şu dizide yer alır:

    LEN (C9: C13)

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

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

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

    • -1-belirtilen arama değerinden büyük veya buna eşit 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, indis işlevi bu bağımsız değişkenleri alır: bir dizi ve bu dizi içinde bir satır ve sütun numarası. C9: C13 hücre aralığı, kaçıncı işlevi hücre adresini ve son bağımsız değişkeni (1) değerin dizideki ilk sütundan geldiğini belirtir.

    En küçük metin dizesinin içeriğini almak isterseniz, yukarıdaki örnekteki maks.

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

    Bu örnekte, hücre aralığındaki üç en küçük değer, B9: B18has: = INT (10, 1) * 100) hücresindeki örnek verilerin dizisini nasıl bulacağınızı gösterir. RASGELEDIZI bir geçici işlevdir, bu nedenle Excel her hesaplarken rastgele bir dizi rastgele sayı alırsınız.

    Nth en küçük değerini bulmak için Excel dizi formülü: = küçük (B9 #, dızı (D9))

    ENTER = küçük (B9 #, Sequence (D9), = küçük (B9: B18, {1; 2; 3})

    Bu formül, küçük işlevini üç kez değerlendirmek ve B9: B18 hücrelerinde bulunan dizideki en az 3 üyeyi döndürmek için bir dizi sabiti kullanır; burada 3, hücrede D9. Daha fazla değer bulmak için, dızı 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 #, DIZI (D9))

    = ORTALAMA (KÜÇÜK (B9 #, DIZI (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şlevi büyük fonksiyonladeğiştirebilirsiniz. Aşağıdaki örnekte buna ek olarak SATIR ve DOLAYLI işlevleri de kullanılır.

    ENTER = büyük (B9 #, Row (dolaylı ("1:3"))) veya = büyük (B9: B18, Row (dolaylı ("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 seçin ve girin:

    =SATIR(1:10)

    Formül, 10 ardışık tamsayılar sütunu oluşturur. Olası bir sorunu görmek için, dizi formülünü içeren aralığın üstüne bir satır ekleyin (yani, 1 ' den fazla). Excel satır başvurularını ayarlar ve şimdi formül 2 ile 11 arasında tamsayılar oluşturur. Bu sorunu düzeltmek için formüle dolaylı işlevi eklersiniz:

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

    DOLAYLı işlevi, bağımsız değişkenleri olarak metin dizelerini kullanır (Bu nedenle 1:10 aralığı tırnak işaretleriyle çevrelenmiş olur). 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. Yalnızca kolayca kullanım yapabilirsiniz:

    = DIZI (10)

    Daha önce kullandığınız formülü (= büyük (B9 #, satır (dolaylı ("1:3"))) — iç parantezden başlayarak, dışarı doğru çalışarak inceleyelim: dolaylı işlevi metin değerleri kümesini, bu örnekte 1 ile 3 arası değerleri döndürür. Sırayla satır işlevi üç hücreli bir sütun dizisini oluşturur. BÜYÜK işlevi, B9: B18 hücre aralığındaki değerleri kullanır ve satır işlevinin döndürdüğü her başvuru için bir kez olmak üzere üç kez değerlendirilir. Daha fazla değer bulmak istiyorsanız, dolaylı işlevine daha büyük bir hücre aralığı eklersiniz. Son olarak, küçük örneklerde olduğu gibi bu formülü, topla ve ortalama gibi diğer işlevlerle kullanabilirsiniz.

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

    Excel 'deki topla işlevi, #VALUE gibi bir hata değeri içeren bir aralığı toplamayı denediğinizde çalışmaz! veya #N/A. Bu örnekte, hata içeren veri adlı bir aralıktaki değerleri toplama yöntemi gösterilmektedir:

    Hatalarla ilgilenmek için dizileri kullanın. Örneğin, = topla (Eğer (EHATALıYSA (veri), "", veri), #VALUE gibi hatalar içerse de, veri olarak adlandırılan aralığı toplar! veya #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ülün gibidir, ancak bunları filtreleme yerine veri adlı bir aralıktaki hata değerlerinin sayısını verir:

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

Belirli koşullara göre hesaplamak için diziler kullanabilirsiniz. = TOPLA (Eğer (Sales>0, satışlar)), Satışlar adındaki bir aralıkta 0 ' dan büyük tüm değerleri toplar.

Örneğin, bu dizi formülü, yukarıdaki örnekteki E9: E24 hücrelerini temsil eden Satışlar adlı aralıktaki pozitif tamsayıları toplar:

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

Eğer işlevi pozitif değerler dizisi 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 ((Sales>0) * (Sales<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 ((Sales>0) + (Sales<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, OR veya AND koşulunu karşılayan değerleri toplama veya çarpma gibi matematik işlemleri gerçekleştirirsiniz.

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 aynı boyutta olması gerekir. Örneğin, MyData bir 3 satırlık bir aralıktır, verilerin de 5 satır üç sütun olması 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 * (MyData<>YourData))

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 büyük değeri içeriyorsa, dizi satır numarasını içerir. Aksi takdirde, dizi boş dize ("") içerir. MIN işlevi ikinci bağımsız değişkeni olarak yeni diziyi kullanır ve verilerdeki 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ı değerleri 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ında, veri kümeleri çalışma sayfası arasındaki farklılıklar hakkında benzer örnekler bulacaksınız.

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ışlar Kişi

Araba Tür

Numarası Satıldı

Birimi 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ış temsilcisinin tüm kuponları ve sedan satışları 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ücresini seçin, = topla (C2: C11 * D2: D11)formülünü girin ve CTRL + SHIFT + entertuşlarına basın.

Ctrl + Shift + Entertuşlarına bastığınızda, Excel formülü kaşlı ayraç ({}) ile çevreler ve seçilen 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 + entertuşlarına basın:

=TOPLA(C2:C11*D2:D11)

Bu durumda, Excel dizideki değerleri çarpar (C2-D11 hücre aralığı) ve Topla işlevinikullanarak toplamları birbirine ekleyin. 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-E11 hücreleri) tamamen bağımsız olduğuna dikkat edin. Dizi formüllerini kullanmanın bir diğer avantajı da budur: Esneklik. D13 'daki formülü etkilemeden E sütunundaki formülleri veya 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ücresini tıklatın ve Deletetuş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 için CTRL + SHIFT + enter tuşlarına basmanız gerekir.

  • Daha küçük dosya boyutları    Birçok ara formül yerine genellikle 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 kullandıysanız (= C2 * D2, C3 * D3, C4 * D4...), aynı sonuçları hesaplamak için 11 farklı formül kullandı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. Önemli fark, bir dizi formülü kullanırken, formülünüzü girmek için CTRL + SHIFT + enter tuşlarına basın. 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 etkili 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 gerektiğinde 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, tüm formül aralığını seçin (örneğin, E2: E11), ardından Deletetuşuna basın.

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

Kimi zaman dizi formülünü genişletmeniz gerekebilir. Var olan dizi aralığındaki ilk hücreyi seçin ve formülü uzatmak istediğiniz tüm aralığı seçinceye kadar devam edin. Formülü düzenlemek için F2 tuşuna basın, ardından formül aralığını ayarladıktan sonra formülü onaylamak için CTRL + SHIFT + enter tuşlarına basın. Bu anahtar, dizideki sol üst hücreden başlayarak tüm aralığı seçiyorum. Sol üst hücre, düzenlenmiş olan hücredir.

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

  • Bazen Ctrl + Shift + Entertuşlarına basmanız gerekebilir. 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, diğer kişilerin çalışma kitaplarınızı değiştirmek gerekirse, dizi formüllerinden kaçınmanız veya kişilerin herhangi bir dizi formülü hakkında bilgi sahibi olmalarını ve bunları nasıl değiştireceğinizi öğrenmek gerektiğini anlayabilmeniz gerekir.

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

Şimdi, dizi formülleri 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 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ı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.

Tek bir satırda 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ır dizisinde, 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 + entertuşlarına basın:

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

    Bu durumda, açılış ve kapanış ayraçları ({}) yazmanız gerekir ve Excel ikinci kümeyi sizin için ekler.

    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 + entertuş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 + entertuş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 + entertuş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öz dizimi

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, bir dizi formülüne sabit ekledikten sonra, formülü girmek için CTRL + SHIFT + enter tuşlarına bası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 + entertuş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 yolu. 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 + entertuş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 veya yanlış yere yerleştirirseniz, dizi sabiti doğru oluşturulmayabilir veya bir 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. Bazı örneklerde, satırları sütunlara dönüştürmek ve tersine çevirmek için devrik 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 + entertuş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 + entertuş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 + entertuş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 + entertuş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 + entertuş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 C8: E10 hücre aralığına bu sayı dizisini 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 + entertuşlarına basın:

    =C8:E10

    C8 ile E3 arasındaki hücrelerde, ile E10 arasındaki değerleri

Varolan değerlerden dizi sabiti oluşturma

  1. C1: C3 seçiliyken, Düzenle moduna geçmek için F2 tuşuna basın. 

  2. Hücre başvurularını değerlere dönüştürmek için F9 tuşuna bası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 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 sonra da a2: A6 (66) hücrelerindeki toplam karakter sayısını görmek için CTRL + SHIFT + enter tuşlarına basın.

  3. A10 hücresini seçin ve ardından CTRL + SHIFT + enter tuşlarına basarak a2: a6 hücrelerinin en uzun (a3 hücresi) içeriğini görün.

Aşağıdaki formül A8 hücresinde a2-a6 hücrelerindeki 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 bu değerleri birbirine ekler ve sonucu görüntüler (66).

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ı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 + entertuşlarına basın:

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

Bu formül, küçük işlevini üç kez değerlendirmek ve en küçük (1), ikinci en küçük (2) ve üçüncü en küçük (3) üyeyi a1: A10 hücrelerinde yer alan dizideki Sabitlen. 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 + entertuşlarına basın:

    = BÜYÜK (A1: A10, SATıR (DOLAYLı ("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ücreden oluşan boş bir sütun seçin, bu dizi formülünü girin ve CTRL + SHIFT + entertuşlarına basın:

=SATIR(1:10)

Formül, 10 ardışık tamsayılar sütunu oluşturur. Olası bir sorunu görmek için, dizi formülünü içeren aralığın üstüne bir satır ekleyin (yani, 1 ' den fazla). Excel satır başvurularını ayarlar ve formül 2 ile 11 arasında tamsayılar oluşturur. Bu sorunu düzeltmek için formüle dolaylı işlevi 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.

Daha önce kullandığınız formüle bir bakalım — = büyük (A5: A14, Row (dolaylı ("1:3"))) — iç parantezden başlayarak, dışarı doğru çalışarak: dolaylı işlevi metin değerleri kümesini döndürür; bu örnekte 1 ile 3 arası değerler. Sırayla satır işlevi üç hücreli bir sütun dizisi oluşturur. Büyük Işlevi, A5: A14 hücre aralığındaki değerleri kullanır ve satır işlevinin döndürdüğü her başvuru için bir kez olmak üzere üç defa değerlendirilir. 3200, 2700 ve 2000 değerleri üç hücreli sütun dizisine döndürülür. Daha fazla değer bulmak istiyorsanız, dolaylı işlevine daha büyük bir hücre aralığı eklersiniz.

Önceki örneklerde olduğu gibi bu formülü, topla ve Ortalamagibi diğer işlevlerle kullanabilirsiniz.

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

Önceki metin dizesi örneğe dönün, boş bir hücreye aşağıdaki formülü girin ve CTRL + SHIFT + entertuşlarına basın:

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

"Olduğu gibi hücreleri" gösteren metin.

İç öğelerden başlayıp dışarı doğru ilerleyerek formüle daha yakından bakalım. Len Işlevi, a2: a6 hücre aralığındaki öğelerin her birinin uzunluğunu verir. Max Işlevi, A3 hücresinde bulunan en uzun metin dizesine karşılık gelen öğeler arasındaki en büyük değeri hesaplar.

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:

(MAX (LEN (A2: A6))

ve bu dize şu dizide yer alır:

LEN (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çıncı işlevi hücre adresini sağlar ve son bağımsız değişkeni (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 * (MyData<>YourData))

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 büyük değeri içeriyorsa, dizi satır numarasını içerir. Aksi takdirde, dizi boş dize ("") içerir. MIN işlevi ikinci bağımsız değişkeni olarak yeni diziyi kullanır ve verilerdeki 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ı değerleri 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))

Alınmadığı

Bu makalenin bazı bölümleri, Colcox ile yazılan bir dizi Excel Power User sütunu ve Bölüm 14 ve 15 ' ten Excel 2002 formüllerinin, yani, eski Excel MVP.

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

Dinamik dizi formülleri ve eski CSE dizi formüllerinin karşılaştırması

FİLTRE işlevi

RASGDİZİ işlevi

SIRALI işlevi

TEK işlevi

SIRALA işlevi

SIRALAÖLÇÜT işlevi

BENZERSİZ işlevi

Excel’de #SPILL! hataları

Formüllere genel bakış

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.

×