Değişken aralık nedir? Excel’de isim tanımlama çok faydalı olsa da sabit bir aralığı göstermektedir. Ne anlama geliyor bu? Bu aralığı bir kere tanımladığınızda sabit kalır ve özellikle bir tablo halinde sürekli güncellenen aralığı bu şekilde tanımlarsanız, isim’in gösterdiği alan kendini güncellemez ve isme müdahale edip gösterdiği aralığı değiştirmemiz gerekir. Bu da hem zaman alır hem de birden fazla kullanıcısı olan dosyalarda farklı kullanıcıların bu işlemi yapmayı atlaması nedeni ile hatalara neden olur.
Peki bu işi otomatik olarak yapabilen bir sistem olsa çok güzel olmaz mıydı? Bu yazımda Excel işte tam olarak bunu nasıl yapabileceğinizi göstereceğim. Belirli verilerin yer aldığı bir tabloda, şartlara ve eklemelere göre kendisini otomatik olarak güncelleyen aralıkların nasıl tanımlanabileceği, belirli şartı sağlayan değerlerin yer aldığı aralıklara nasıl erişilebileceğini gösteren çeşitli uygulamalara yer vereceğim. Bu yazımda anlattığım örnekleri uygulayabilmek için öncelikle şarta göre sayma, indis, kaydır formüllerini anlattığım diğer yazılarımı okuyun. Bu formülleri burada sıklıkla kullanacağım.
Aşağıda farklı uygulamalar için örnekler mevcut. İlk sekmeden itibaren kademeli olarak ilerleyebilirsiniz. Yada buradaki ilk tabloyu kopyalayarak tüm örnekleri bu tablo üzerinden çalışabilirsiniz.
İlk örneğimizde birden fazla sütuna yayılmış bir veri aralığımız olsun. Ve en sonunda da toplam değerini aldığımız bir hücremiz yer alsın. Aşağıda bu senaryo için bir örnek tablo yer almakta. Bu tabloyu lütfen B2 hücresinden itibaren yapıştırın.
| A Ürünü Ciro | B Ürünü Ciro | C Ürünü Ciro | Toplam Ciro | |
|---|---|---|---|---|
| Ahmet | 10.000 | 8.000 | 6.000 | 24.000 |
| Merve | 15.000 | 9.000 | 11.000 | 35.000 |
| Hasan | 12.000 | 6.000 | 13.000 | 31.000 |
| Mehmet | 9.000 | 12.000 | 8.000 | 29.000 |
| Saliha | 16.000 | 14.000 | 6.000 | 36.000 |
| Genel Toplam | 155.000 |
Şimdi bu tabloda genelde toplam için aşağıdaki formülü kullanırız:
=TOPLA(‘Sayfa İsmi’!C3:E3)
Burada bir aralık tanımlasak ve aralık ismini bu formülde kullansak nasıl olur? Burada karşınıza çıkacak ilk sıkıntı daha önce söylediğim gibi aralık isminin sabit olmasıdır. Ancak bizim alt alta birden fazla satırımız var ve hepsi için isim tanımlamak hem sıkıcı olur hem de yeni eleman eklemek istediğimizde bir de isim tanımlaması yapmak durumunda kalırız. Ancak isim tanımlarken hücre adresini sabit olarak girmez isek durumu tersine çevirebiliriz.
Şimdi isim tanımlama kısmına gelelim ve yeni bir isim tanımlayalım. Ben isim olarak “PersonelSatis” adını kullandım ve başvuru kısmına da aşağıdaki formülü yazın (‘SAYFA İSMİ’ kısmına kendi sayfanızın adını tek tırnak içinde yazmayı unutmayın, yada formülü önce bir hücreye yazın sonra kopyalayarak bu alana yapıştırın. Tamam dediğinizde sayfa isimleri otomatik olarak eklenecektir.):
=’SAYFA İSMİ’!$C3:$E3
Aşağıdaki resimde nasıl tanımladığımı görebilirsiniz:
Dikkat ederseniz “3” rakamının önünde $ işareti yok. Bu da formülün kullanıldığı yere göre sütunun her zaman C:E arasında olmasını ancak satırın, ismin kullanıldığı satırdan geleceğini ifade etmektedir. Şimdi toplam formülümüzü şu şekilde güncelleyelim, formül yazarken “F3” tuşuna basarsanız kullanabileceğiniz isimlerin listesi görüntülenir:
=TOPLA(PersonelSatis)
Ve aşağı doğru uzatarak tüm satırlara yayalım. Gördüğünüz gibi hesaplama sonuçları değişmedi. En son satıra yeni bir isim girerseniz dahi bu formülü güvenle kullanabilirsiniz. Şimdi yeni bir personelin işe başladığını ve benzer şekilde onun da satışlarını takip etmek gerektiğini düşünelim. İlgili verileri girdikten sonra formülü aşağı doğru uzattığımızda formülün de kendini güncellediğini göreceksiniz.
Aşağıdaki videoda nasıl yapıldığını gösterdim. Tıklanan hücre değiştiğinde aralığın kendini nasıl güncellediğine dikkat edin.
Bir önceki senaryomuzda yeni personel eklediğimiz bir durumu göz önüne almıştık. Şimdi ise yeni bir ürünün portföye eklendiğini düşünelim. Örnekte gösterdiğim “C Ürünü Cirosu” ile “Toplam” sütunları arasına bir sütun ekleyerek yeni bir ürün olan “D Ürünü Cirosu” başlığını girelim. Ve bu sütuna da ilgili değerleri yazalım. Aşağıda örnek tabloyu bulabilirsiniz. Tabloyu B2 hücresinden itibaren yapıştırın lütfen.
Office 2010’da yazdığınız toplama formüllerinin bulunduğu sütundan öncesine bir sütun ekleyip değer girerseniz toplama formülü yeni hücreyi de içerecek şekilde kendini günceller. Ancak ben burada formülün nasıl çalıştığını göstermek adına sabit ve genişleyen isimleri kullanacağım. Aşağıdaki videoda sütun eklenince aralığın değişmediğini görebilirsiniz.
Şimdi biz öyle bir isim tanımlayalım ki, eklediğimiz sütuna göre kendini sürekli güncellesin ki biz de isimleri ve/veya formülleri sürekli kontrol etme kve değiştirmek zorunda kalmayalım.
Bunun için öncelikle formülün kullanılacağı ilk hücreye tıklayarak, hücreyi etkinleştiriyoruz. Bu adım çok basit gibi görülse de eğer bu adımı atlarsanız isim tanımlarken kullandığınız hücre adresleri karışabilir ve alakasız hücreleri gösterebilir.
Sonrasında isim tanımlama penceresini açıyoruz. Ben isim olarak “PersonelSatisGen” kullandım. Ve aşağıdaki formülü tanımlıyoruz.
=KAYDIR(‘SAYFA ISMI’!$C3;0;0;1;BAG_DEG_DOL_SAY(‘Sayfa İsmi’!$2:$2)-1)
Aşağıdaki resimde nasıl tanımlandığını gösterdim:
Peki bu formül ne yapıyor? Kaydırmak istediğimiz aralık sütunlarda yer alıyor ve başlangıcı da C sütununda. Ancak satır kısmını formülün yer aldığı satırdan alınmasını istiyoruz bu nedenle başlangıç adresini “$C3” olarak her zaman C sütunundan başlayacak şekilde yazıyoruz. Sütun başlıkları 2. satırda yer alıyor. Bu nedenşe BAĞ_DEĞ_DOLU_SAY formülü ile 2. satırda içinde veri bulunan toplam hücre adedini buluyoruz ve “Toplam Ciro” sütununun bu aralıkta yer almasını istemediğimiz için de bu değerden 1 çıkarıyoruz. Böylece aralığımız C3 ile toplamda yan yana 4 hücreden oluşan bir aralık oluyor.
Şimdi Toplam Ciro sütununa da aşağıdaki formülü yazalım:
=TOPLA(PersonelSatisGen)
Burada dikkate etmeniz gereken ilk husus “KAYDIR” formülünde kullandığınız başvuru hücresi adresi olacaktır. Eğer satır kısmının başında da “$” işareti koyarsanız bu aralık hangi hücrede kullanılırsa kullanılsın her zaman 3. satırı gösterecektir. Bu da toplam değerin her defasında aynı olmasına yol açarak hataya sebebiyet verecektir.
İkinci bir husus ta BAĞ_DEĞ_DOLU_SAY ile bulunan değerden 1 düşüğünü almaktır. Yoksa aralık “Toplam Ciro” sütunu da alacak şekilde olacak ve bu da toplam formülünde “Döngüsel Başvuru” hatasına yol açacaktır.
Son husus ise genişlik için kullandığımız rakamın BAĞ_DEĞ_DOLU_SAY formülü ile 2. satırda yer alan dolu hücrelere göre bulunduğundan ve bu alanda başlık bilgisi yer aldığından yeni eklediğiniz sütuna da bir başlık yazmanız gerekliliğidir. Eğer bunu atlarsanız BAĞ_DEĞ_DOLU_SAY formülü, toplam sütun sayısından bir eksik değeri geri döndürecektir. Bu da aralığın daha dar olmasına sebep olacaktır.
Bu ismi tanımladıktan sonra “TOPLAM” sütunundan önce istediğiniz kadar sütun ekleyin, tanımladığımız isim kendini güncelleyecek ve toplam formülünüz her zaman doğru değeri verecektir. Ancak “TOPLAM” sütunundan sonraya gelecek şekilde sütun eklerseniz bu durumda ismimiz “TOPLAM” sütununu da alacak şekilde kendini güncelleyeceğinden formül “döngüsel başvuru” hatası verecektir. Buna dikkat edin.
Aşağıdaki videoda yaptığım işlemlerin nasıl çalıştığını gösterdim.
İlk sekmedeki tablomuzda yeni bir personelin işe girdiğini düşünelim. Bu personelin de satışlarını girdiğimizde genel toplam hücresindeki değerin değişmediğini görürüz. Aşağıdaki videoda nasıl olduğunu gösterdim.
| A Ürünü Ciro | B Ürünü Ciro | C Ürünü Ciro | Toplam Ciro | |
|---|---|---|---|---|
| Ahmet | 10.000 | 8.000 | 6.000 | 24.000 |
| Merve | 15.000 | 9.000 | 11.000 | 35.000 |
| Hasan | 12.000 | 6.000 | 13.000 | 31.000 |
| Mehmet | 9.000 | 12.000 | 8.000 | 29.000 |
| Saliha | 16.000 | 14.000 | 6.000 | 36.000 |
| Emre | 12.000 | 10.000 | 5.000 | 36.000 |
| Genel Toplam | 155.000 |
Peki burada nasıl bir formül yazarsak genel toplam alanı kendini sürekli güncel tutar? Yine isim tanımlama ekranına giriyoruz ve tanımlama kısmına aşağıdaki formülü yazıyoruz. Ben isim olarak “GenelToplamAralik” ismini girdim.
=KAYDIR(‘Sayfa İsmi’!$F$1;2;0;BAG_DEG_DOLU_SAY(‘Sayfa İsmi’!$F:$F)-2;1)
Aşağıda isim kutusu ve formülü görebilirsiniz.
Bu formül nasıl çalışıyor? Genel toplam F sütununda yer alıyor. Bu sütundaki il hücre olan F1 hücresinden başlayarak 2 hücre aşağıya kayıyor ve aralığın başlangıç hücresine geliyoruz. Sonrasında ise B:B sütununda veri girilmiş olan tüm hücrelerin toplamını buluyoruz ve bu değer kadar aralığımızı aşağıya doğru genişletiyoruz. Neden F:F sütununda yer alan hücrelerin toplamını kullanmadık? Toplam değeri de F sütununda yer alıyor. Ve eğer F:F sütununu kullanırsak toplam hesaplanan hücre de bu formülde yer alacağından döngüsel başvuru hatası alırız.
Genel Toplam hücresine de aşağıdaki formülü yazalım:
=TOPLA(GenelToplamAralik)
Artık genel toplam formülü, bu satırın üstüne ne kadar satır eklersek ekleyelim kendini güncelleyecektir. Burada da dikkat etmeniz gereken ilk husus kaydırma formülündeki başlangıç hücresidir. Bu hücre sütunun ilk hücresi olabileceği gibi direk ilk personelin toplam satış değerinin bulunduğu F3 hücresi de olabilir. Ancak bu durumda aşağı doğru kaydırma miktarını ifade eden “2” rakamını da buna göre değiştirmeniz gerekmektedir.
Bir diğer husus ta tabii ki yüksekliğin belirlendiği BAĞ_DEĞ_DOLU_SAY formülü ile bulunan değer içinde başlık ve toplam satırlarının olduğunu unutmamaktır. Eğer bu değeri, bu hücreleri dikkate alarak 2 azaltmazsanız aralığınız dikeyde tüm hücreleri içine alacağından toplam formülü hata verecektir.
Daha önce de belirttiğim gibi burada da toplam satırının altına bir değer eklerseniz bu aralık kendini güncelleyerek toplam satırını da içine alır ve formül “Döngüsel Başvuru Hatası” verir. Buna da azami dikkat etmeniz gerekmekte.
Aşağıdaki videoda bu işlemin nasıl yapıldığını gösterdim.
Bazen belli bir satırı veya sütunu değil de tüm tabloya veri ekleme-çıkarma gibi işlemler olabilir. Ve siz tüm tablonun eklenen yeni değerlere göre kendini güncellemesini istiyor olabilirsiniz. Bunu yapmak için Excel 2010’dan itibaren Tablo özelliği getirildi ancak biz burada isim kullanarak bu işin yapmak istediğimizi varsayıyoruz. Excel Tablolar güçlü bir özellik, daha sonra bu konuda da bir yazı yazacağım. Ancak şimdilik elimizdeki göreve odaklanalım. Tablomuzu tekrar aşağıya ekliyorum. Tabloyu B2 hücresinden itibaren yapıştırın lütfen.
| A Ürünü Ciro | B Ürünü Ciro | C Ürünü Ciro | D Ürünü Ciro | Toplam Ciro | |
|---|---|---|---|---|---|
| Ahmet | 10.000 | 8.000 | 6.000 | 8.000 | 24.000 |
| Merve | 15.000 | 9.000 | 11.000 | 10.000 | 35.000 |
| Hasan | 12.000 | 6.000 | 13.000 | 9.000 | 31.000 |
| Mehmet | 9.000 | 12.000 | 8.000 | 12.000 | 29.000 |
| Saliha | 16.000 | 14.000 | 7.000 | 6.000 | 36.000 |
| Genel Toplam | 155.000 |
Sürekli güncellenen bir tablomuz olduğunu düşünelim, aylık, haftalık raporlar, proje ile ilgili saatlerin yer aldığı tablolar gibi. Şimdi ben burada daha basit bir tablo kullanacağım tabii ancak kullanacağım formül tüm tablolarda aynı işleve sahip olacaktır.
Yine, her zaman olduğu gibi, isim tanımlama ekranını açıyoruz. Ben burada “TumTablo” ismini kullandım.Ve tanımlama kısmına aşağıdaki formülü girelim.
=KAYDIR(‘Sayfa İsmi’!$B$2;0;0;BAĞ_DEĞ_DOLU_SAY(‘Sayfa İsmi’!$B:$B)+1;BAĞ_DEĞ_DOLU_SAY(‘Sayfa İsmi’!$2:$2)+1)
Bu formül nasıl çalışıyor? Öncelikle referans hücremiz B2 hücresi satış değerlerinin yer aldığı ilk hücre. Bu hücreden başlayarak 2. sütunda veri yer alan tüm hücrelerin toplamını buluyoruz. Bu toplama göre aralığımızın yüksekliği belirleniyor. Ancak dikkat ederseniz bu aralıkta ilk hücrede değer yok bu yüzden bulunan değere 1 ekliyoruz. Genel Toplam satırını da almak istemiyorum, çünkü daha sonra genel toplam bulmak istersem hataya yol açacak. Sonrasında ise 2. satırdaki tüm veri içeren hücrelerin adedini hesaplayıp 1 ekleyerek aralığımızın genişliğini buluyoruz. Bu sayede verilerin yer aldığı tüm hücreleri içeren bir isme sahip oluyoruz.
İsim penceresinde formülün görünümünü aşağıda size veriyorum:
Bu formülün nasıl çalıştığı ile ilgili videoyu da aşağıda bulabilirsiniz.
Sütunlara, satırlara ve tüm tabloya eklenen verilere göre kendi kendine genişleyen bir isim tanımladık. Ancak büyük tablolarda tek başlarına bu isimleri kullanmak verimli olmayabilir. Misal burada verdiğim örnek tablodaki her bir ürünün cirosu için farklı grafikler hazırlamak isteyelim. Bu durumda her bir ürün için tek tek bir isim tanımlamak gerekir. Bu da tabii ki her seferinde el ile ek bir işlem yapmak demek. İyi de ya unutursak ya da atlarsak? Oluşturduğumuz grafikler eksik veya hatalı bilgiler içerebilir. Bunu engellemek için güzel bir yöntem var desem? Bunun için tüm tabloyu içeren bir değişken aralık tanımlamamız gerekecek tabii ama onlarca isim tanımlamak yerine bir tane isim tanımlamak çok daha kolay.
Tablomuzu hazırladıktan sonra ise yapmamız gereken artık çok kolay. Indis formülü ile tablonun istediğimiz alanlarını çekmek. Evet bu güzel formül ile çok büyük bir tablonun istediğimiz alanlarını istediğimiz gibi çekebiliriz. İlk örneğimizi yapalım isterseniz. Örnek tabloyu aşağıda veriyorum. Lütfen B2 hücresinden itibaren yapıştırın.
| A Ürünü Ciro | B Ürünü Ciro | C Ürünü Ciro | |
|---|---|---|---|
| Ahmet | 10.000 | 8.000 | 6.000 |
| Merve | 15.000 | 9.000 | 11.000 |
| Hasan | 12.000 | 6.000 | 13.000 |
| Mehmet | 9.000 | 12.000 | 8.000 |
| Saliha | 16.000 | 14.000 | 6.000 |
Şimdi yeni bir isim tanımlayalım. İsim için ben “VeriTablosu” ismini kullandım. Başvuru kısmına da aşağıdaki formülü yazalım.
=KAYDIR($B$2;0;0;BAĞ_DEĞ_DOLU_SAY($B:$B)+1;BAĞ_DEĞ_DOLU_SAY(!$3:$3))
Bir önceki sekmede anlattığım formülün biraz daha değiştirilmiş hali. Aşağıda formül penceresini görebilirsiniz.
Şimdi bu tabloda yer alan isimlere ve/veya ürünlere göre toplam satış cirosunu bulmak isteyelim. Tabii bunun için bir ek tabloya daha ihtiyacımız var. Örnek olarak tüm personelin isimlerinin yer aldığı bir tablo. Şimdi bu kişilerin yaptığı satışın cirosunu bulmak isteyelim. Aşağıda resmini gösteriyorum.
Şimdi personele göre toplam ciroyu bulmak istersek yazacağımız formül:
=TOPLA(İNDİS(VeriTablosu;KAÇINCI(‘Sayfa Adı!’$H4;$B:$B;0)-1;0))
Bu formül tam olarak nasıl çalışıyor? İndis formülü belirli bir aralığın içinde sütun, satır veya ikisinin kesişimi olan hücreyi geri döndürmek için kullanılıyordu. Burada belirli aralık kısmı bizim genişleyen aralık olan VeriTablosu isminden alınıyor. B:B aralığında ilgili personelin ismi, bu isim H4 sütununda yer alıyor, aranıyor ve hangi satırda bulundu ise tablodaki o satırın tümünü geri döndürüyor. Aramayı B:B aralığında yaptığımızı ve tablonun da B2 hücresinden başladığını dikkate alırsak bulduğumuz değerden 1 çıkarıyoruz. Topla ile de dönen aralıkta yer alan tüm veriyi topluyoruz. Bu formülün güzel yani aşağı doğru formülü çoğalttığımızda isimler H sütunundan alınacağından her bir personel için ayrı ayrı formül yazmamıza gerek kalmıyor. Ek olarak veri tablomuza yeni satırlar eklediğimizde de formülümüzü kullanmaya devam edebiliriz. Çünkü veri tablosu kendi kendini güncelleyecektir.
Aşağıda konu ile ilgili bir video paylaşıyorum.
Oldukça uzun ve kapsamlı bir yazı olduğunu düşünüyorum. Bu formülleri kullanarak tamamen güvenli ve sonrasıda sürekli güncelleme istemeyen bu sayede daha az hata oluşan otomatik raporlar, tablolar, grafikler oluşturabilirsiniz. Umarım sizlerde benim kadar eğlenmişsinizdir. Her zaman olduğu gibi örneklerde kullanılan dosyayı buraya tıklayarak indirebilirsiniz.
Son olarak değişken aralığın belkide tek zaafı olan özellikten bahsedeyim. Değişken aralıklara başka çalışma kitaplarından referans veremezsiniz. Yani birbirinden bağımsız iki çalışma kitabınız olsun. Bu iki kitabın birinde tablolarınız ve değişken aralık gösteren isimleriniz olsun. Diğer çalışma sayfasında ise bu sayfadaki değişken aralık gösteren isimler içeren formül yazdığımızı düşünelim. Bu durumda eğer değişken aralıkların olduğu dosya açık değil ise formüller hata verecektir ve güncelleme yapsanız dahi veriler okunmayacaktır.