Özet
Bir Excel Tablosunda çalışan bir sayı oluşturmak için, genişleyen bir aralık oluşturmak üzere yapılandırılmış bir referansla DİZİN işlevini kullanabilirsiniz. Gösterilen örnekte, F5'teki formül şöyledir:
=(@Color)&" - "&SUM(--(INDEX((Color),1):(@Color)=(@Color)))
Sütun aşağı kopyalandığında, bu formül Renk sütunundaki her bir renk için çalışan bir sayı döndürür.
Excel'in bazı sürümlerinde, bu bir dizi formülüdür ve control + shift + enter ile girilmelidir.
Açıklama
Temelde, bu formül aşağıdaki gibi genişleyen bir referans oluşturmak için INDEX kullanmaktadır:
INDEX((Color),1):(@Color) // expanding range
İki nokta üst üste (:) sol tarafında, INDEX işlevi sütun sütunundaki ilk hücreye bir başvuru döndürür.
INDEX((Color),1) // first cell in color
Bunun işe yaramasının nedeni, INDEX işlevinin gerçek değere değil, ilk hücreye bir başvuru döndürmesidir. Kolonun sağ tarafında, renk sütununun geçerli satırına şu şekilde bir referans alıyoruz:
(@Color) // current row of Color
Bu, "bu satır" için standart yapılandırılmış referans sözdizimidir. İki nokta üst üste ile birleştirilen bu iki referans, formül tablodan aşağı kopyalanırken genişleyen bir aralık oluşturur. Dolayısıyla, bu referansları TOPLA işleviyle değiştiriyoruz, elimizde:
SUM(--(B5:B5=(@Color))) // first row SUM(--(B5:B11=(@Color))) // last row
Yukarıdaki ifadelerin her biri bir DOĞRU / YANLIŞ değerler dizisi oluşturur ve bu değerleri 1'lere ve 0'lara dönüştürmek için çift negatif (-) kullanılır. Yani, son satırda şunu buluyoruz:
SUM((0;0;0;1;0;0;0;0;1;0;1)) // returns 3
Formülün geri kalanı, geçerli satırdaki rengi SUM tarafından döndürülen sayıya birleştirir:
=(@Color)&" - "&3 ="Gold"&" - "&3 ="Gold - 3"
Basit genişleyen aralık?
Neden bunun gibi basit bir genişleyen aralık kullanmıyorsunuz?
SUM(--($B$5:B5=(@Color)))
Bazı nedenlerden dolayı, satırlar eklendikçe bu tür bir karma başvuru Excel Tablosunda bozulur. INDEX'i yapılandırılmış bir referansla kullanmak sorunu çözer.
