Kaydır (Offset) ile Bağ_Değ_Dolu_Say (Counta) Fonksiyonları


KAYDIR (OFFSET) FONKSİYONU VE BAĞ_DEĞ_DOLU_SAY (COUNTA) KULLANARAK DİNAMİK ALAN OLUŞTURMAK

Öncelikle KAYDIR( OFFSET) fonksiyonun işlevinden bahsedelim. Bu fonksiyon Basit olarak referans olarak verdiğimiz hücreden yine bizim belirlediğimiz satır ve sütun uzaktaki hücrenin değerini döndürür.

KAYDIR(başv; satırlar; sütunlar; [yükseklik]; [genişlik])

OFFSET(reference,rows,cols,[height],[width])

Başv : referans (başlangıç) hücremiz

Satırlar: referans hücreden kaç satır uzağa gideceğimiz

Sütunlar: referans hücreden kaç sütun uzağa gideceğimiz

[yükseklik]: dönecek sonuçtaki satır sayısı (yazılmaz ise excel default değer olarak 1 atar)

[genişlik]: dönecek sonuçtaki sütun sayısı (yazılmaz ise excel default değer olarak 1 atar)

Burada satırlar ve sütunlar eksi ve ya artı değerler olabilir. Eksiler sola ve yukarıya, artılar sağa ve aşağıya anlamına gelir.

Aşağıdaki örnekle pekiştirmeye çalışalım:

Referans hücre olarak “C4” ü kullanacağız.

offset

not : Son yazdığımız formül #BAŞV! Hatası verdi çünkü C sütunundan sonra en fazla 2 sütun geriye gidebilir max A sütununa gelebiliriz.

Şimdi 2. Konumuza geçelim KAYDIR (OFFSET) fonksiyonu ile dinamik alan tanımlama:

Burada BAĞ_DEĞ_DOLU_SAY yani seçilen aralıktaki dolu hücrelerin sayısını döndüren fonksiyonumuzdan yaralanacağız.

Kaydır fonksiyonu ile yukarıda açıkladığımız gibi tek bir hücrenin sonucunu döndürebileceğimiz gibi [yükseklik] ve [genişlik] girdilerini değiştirerek bir hücre aralığından da bahsedebiliriz.

Ancak hücre aralığının sonucunu tek bir sonuç olarak döndüremeyeceğimiz için bu kısmı KAYDIR ile beraber TOPLAM,MAX,MİN,ORTALAMA gibi fonksiyonlarla tamamlayacağız.

Capture

393 = C2:C10 tüm satış adetlerinin toplamı,

Formülü açıklayalım:

B2: B2 hücresini referans hücre olarak al.

0: satır atlama

1: 1 sütun sağa kay (buraya kadarki işlem sonucu bize C2 hücresini yani 26 sonucunu verecek)

BAĞ_DEĞ_DOLU_SAY(B2:B10) : şimdi aynı formülü tekrar uygula her defasında satırı bir aşağıya kaydır. Taki dolu hücre sayısına ulaşana kadar.

10.794= C2:D10 toplamı,

Formülü açıklayalım:

B2: B2 hücresini referans hücre olarak al.

0: satır atlama

1: 1 sütun sağa kay (buraya kadarki işlem sonucu bize C2 hücresini yani 26 sonucunu verecek)

BAĞ_DEĞ_DOLU_SAY(B2:B10) : şimdi aynı formülü tekrar uygula her defasında satırı bir aşağıya kaydır. Taki dolu hücre sayısına ulaşana kadar.

2: Aynı işlemleri tekrar yap bu kez sütunu 2 kaydır.

Neden DİNAMİK hücre aralığı diyoruz?

Burada BAĞ_DEĞ_DOLU_SAY kullanarak dinamik bir formül elde etmemizin nedeni tablomuza bir satır eklendiğinde formül veri aralığınıotomatik olarak genişletecektir.

XXX satırını ekledikten sonra formül otomatik olarak veri aralığını genişletti.

Capture

Son olarak ETOPLA ile basitçe yapılacak işlemi aşağıda alan tanımlama ve bir takım iç içe fonksiyonlarla yapacağız. Bu yolu seçmemizin nedeni aşağıda göreceğimiz formüller ileride koşullu veri doğrulama konusu içerisinde işimize yarayacak.

Elimizde aylar bazında satış rakamları mevcut bir altta ay bazında adetlerin toplamını bulacağız.

Capture

B12 deki formülü okumaya çalışalım:

=TOPLA(KAYDIR($A$2;KAÇINCI(A12;$A$2:$A$10;0)-1;1;EĞERSAY($A$2:$A$10;A12);1))

A2: A2 hücresini referans hücre olarak al.

Satır: kaç satır kaydıracağım? KAÇINCI(A12;$A$2:$A$10;0)1 => Ocak ayı kaçıncı satırdan başlıyor ise (sonuç 1 çıkacak) onun 1 eksiği kadar satır kaydıracağım yani sıfır.

Sütun: kaç sütun kaydıracağım? : 1 (satış adetlerim bir yan sütunda)

EĞERSAY($A$2:$A$10;A12): Aynı işlemi her seferinde satırı “ocak” ayı sayısı kadar aşağıya kaydırarak (4 defa) tekrar et.

1: sütunla ilgili bir değişiklik yapmayacağım.

TOPLA: en son çıkan rakamları topla.

Dikkat: burada önemli kısım bu formülü kullanmak istiyorsak aynı gruptaki verilerim tablomda alt alta olmalı.

excel dosyasını indirmek icin tıklayınız:

download

 

Reklamlar

Kaydır (Offset) ile Bağ_Değ_Dolu_Say (Counta) Fonksiyonları” üzerine 3 yorum

    • Hangi tabloyu kastettiniz? eğer yapmak istediğiniz yukarıdaki aylar örneğinde olduğu gibi belli kategoriyi kendi içinde toplamak ise ETOPLA = COUNTIFS formülünü kullanabilirsiniz. daha basit bir formül olduğu için e-tablolarda çalışacağını düşünüyorum.

Bir Cevap Yazın

Aşağıya bilgilerinizi girin veya oturum açmak için bir simgeye tıklayın:

WordPress.com Logosu

WordPress.com hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Google+ fotoğrafı

Google+ hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Twitter resmi

Twitter hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Facebook fotoğrafı

Facebook hesabınızı kullanarak yorum yapıyorsunuz. Çıkış  Yap /  Değiştir )

Connecting to %s