Mutlak Göreli Karma Başvurularla Dinamik Formül Oluşturma

Mutlak, göreli ve karma başvuru (absolute, relative and mixed reference), bir formül başka hücrelere kopyalandığında içindeki hücre adreslerinin değişip değişmeyeceğini belirleyen yazım biçimleridir. Adresin önüne dolar işareti koyarak satırı, sütunu ya da ikisini birden sabitler, böylece tek bir formülü yüzlerce hücreye hatasız kopyalayabilirsiniz. Bu sayfada üç başvuru türünün farkını, F4 tuşunun kullanımını ve veri büyüdükçe kendini uyduran dinamik formül yöntemlerini örneklerle öğreneceksiniz.

Mutlak, göreli ve karma başvuru farkı​

Excel, bir formülü başka bir hücreye kopyaladığınızda içindeki adresleri kopyalanan konuma göre kaydırır. Bu davranışı üç başvuru türü yönetir:

• Göreli başvuru: A1 biçimindedir. Formül kopyalandığında satır ve sütun, kopyalanan yöne göre değişir.
• Mutlak başvuru: $A$1 biçimindedir. Satır ve sütun sabitlenir, formül nereye kopyalanırsa kopyalansın aynı hücreye bakar.
• Karma başvuru: $A1 yalnızca sütunu, A$1 yalnızca satırı sabitler. Diğer bölüm göreli kalır.

Dolar işaretini elle yazabileceğiniz gibi, formülü düzenlerken adrese imleci getirip F4 tuşuna da basabilirsiniz. Her basışta adres A1, $A$1, A$1, $A1 sırasıyla döner ve yeniden A1 olur. Bazı dizüstü bilgisayarlarda işlev tuşunu kullanmak için Fn tuşuyla birlikte basmanız gerekebilir.

Örneğin B2 hücresindeki tutarı D2 hücresindeki sabit bir oranla çarpan formül şöyle yazılır:
Bu formül aşağı kopyalandığında B2 göreli olduğu için B3, B4 diye ilerler; $D$2 ise hep aynı oran hücresini gösterir.

Dinamik formül nasıl oluşturulur​

Dinamik formül, veri satırı eklendiğinde ya da silindiğinde adreslerini elle düzeltmenizi gerektirmeden doğru aralığa bakan formüldür. Başlıca yöntemler şunlardır:

• Tablo: Veriyi Ekle > Tablo (Insert > Table) ile tabloya çevirirseniz tablo, yeni satırlarla otomatik genişler ve formüllerde sütun adlarıyla başvuru yapabilirsiniz.
• Tanımlı ad: Formüller > Tanımlı Adlar > Ad Yöneticisi (Formulas > Defined Names > Name Manager) ile bir aralığa anlamlı bir ad verir, formüllerde adres yerine bu adı kullanırsınız. Ad, mutlak başvuruyla tanımlanır.
• Yayılan aralık başvurusu: Microsoft 365 ve Excel 2021 gibi dinamik dizi destekleyen sürümlerde, taşan bir formülün sonucuna hücre adresinin sonuna diyez işareti ekleyerek başvurabilirsiniz. Sonuç büyüyüp küçüldükçe başvuru da uyum sağlar.
• İNDİS ile son hücreyi bulma: Aralığın bitişini, dolu hücre sayısına göre hesaplatabilirsiniz.

İlk hücre mutlak ya da karma, son hücre ise hesaplanan bir başvuru olabilir. Örneğin B2 hücresinden başlayıp B sütunundaki son dolu hücreye kadar toplam almak için:
Bu yöntem, aralıkta boş satır bulunmadığı sürece doğru çalışır. KAYDIR ve DOLAYLI işlevleri de dinamik başvuru üretebilir; ancak bu iki işlev her hesaplamada yeniden çalıştığı için büyük çalışma kitaplarını yavaşlatabilir.

Hangi başvuru türü ne zaman kullanılır​

Doğru türü seçmek için formülü kopyalayacağınız yönü düşünün:

• Sabit değer hücresi: Oran, kur ya da hedef gibi tek bir hücredeki değeri tüm satırlarda kullanacaksanız mutlak başvuru seçin.
• Satır satır hesap: Her satırın kendi verisini kullanacağı hesaplarda göreli başvuru yeterlidir.
• Çapraz tablo: Bir sütundaki değerleri bir satırdaki değerlerle birleştirecekseniz karma başvuru kullanın. Sütun başlığı için satırı, satır başlığı için sütunu sabitleyin.
• Genişleyen aralık: Birikimli toplamda aralığın başlangıcını sabitleyip bitişini göreli bırakırsınız.

Başka bir sayfadaki hücreyi sabitlerken sayfa adı da adrese dahildir; örneğin Veri sayfasındaki oran hücresi Veri!$B$2 biçiminde yazılır. Formülü kopyalamadan önce üç dört hücrede sonucu elle doğrulamak, hatalı sabitlemeyi erken yakalamanızı sağlar.

1. Sabit oranı mutlak başvuruyla kullanma​

Satış tutarlarının yanındaki komisyon oranı tek bir hücrede (D2) duruyor. C sütunundaki formül aşağı kopyalanırken oran hücresinin sabit kalması gerekiyor.
C2 hücresindeki formül, B2 göreli ve $D$2 mutlak olacak biçimde yazılmıştır. Aşağı kopyalandığında B2 sırayla B3, B4 diye ilerler, $D$2 ise değişmez. Dizüstü bilgisayar için 750,00, tablet için 1.260,00 sonucu hesaplanır. Oranı D2 hücresinde değiştirirseniz tüm komisyonlar birlikte güncellenir.

2. Karma başvuruyla iskonto matrisi​

Birim fiyatlar B sütununda, iskonto oranları ise 1. satırda yer alıyor. Tek bir formülün hem sağa hem aşağı kopyalanabilmesi için fiyat sütunu ($B) ve oran satırı (1) karma başvuruyla sabitlenir.
C2 hücresine yazılan formül, C2:E6 aralığının tamamına kopyalanmıştır. $B2 ifadesinde sütun sabit olduğu için formül sağa kayarken hep birim fiyata bakar, satır göreli olduğu için aşağıda sıradaki ürüne geçer. C$1 ifadesinde ise satır sabit olduğu için hep 1. satırdaki oranı alır. Masanın iki farklı iskontolu fiyatı 1.140,00 ve 1.020,00 olarak hesaplanır.

3. Birikimli toplam ve genişleyen aralık​

Aylık satışların birikimli toplamı ve her ayın yıllık toplam içindeki payı hesaplanıyor. Aralığın başlangıcı mutlak ya da karma, bitişi göreli yazılır. E2 hücresi ise B sütunundaki son dolu hücreye kadar otomatik toplam alır.
C sütunundaki B$2:B2 aralığının başlangıcı sabit, bitişi göreli olduğu için aşağı indikçe aralık genişler. Haziran ayına kadar olan birikimli toplam 137.200 olarak hesaplanır. D sütununda $B$2:$B$7 mutlak olduğundan her ayın payı aynı genel toplama bölünür; Ocak ayının payı %13,5 çıkar. E2 hücresindeki formül aralığın bitişini dolu hücre sayısına göre belirlediği için B8:B13 arasına yeni aylar girdiğinizde toplam 137.200 değerinden otomatik olarak güncellenir; ancak C ve D sütunlarındaki formülleri yeni satırlara kopyalamanız gerekir.

Sık sorulan sorular​

• Formülü aşağı çekince neden sonuçlar kayıyor? Sabit kalması gereken hücre göreli yazılmıştır ve her satırda bir adım aşağı kaymıştır. O adrese F4 ile dolar işaretleri ekleyin.
• F4 tuşu çalışmıyor, ne yapmalıyım? Önce hücrenin düzenleme modunda ve imlecin adresin üzerinde olduğundan emin olun. Bazı bilgisayarlarda Fn tuşuyla birlikte basmak gerekir. Dolar işaretlerini elle de yazabilirsiniz.
• Satır eklersem mutlak başvuru bozulur mu? Hayır. Satır eklendiğinde ya da silindiğinde Excel, mutlak başvuruyu da aynı hücreyi gösterecek biçimde günceller. Mutlak başvuru yalnızca kopyalama sırasında sabit kalır.
• Karma başvuruda hangi işaret neyi sabitler? Dolar işareti, hemen sağındaki bölümü sabitler. $B2 sütunu, B$2 satırı sabitler.

Kullanırken dikkat edin​

• Karma başvuruda dolar işaretinin yeri: Dolar işareti, hemen sağındaki bölümü sabitler. Sütun harfinin önündeki işaret sütunu, satır numarasının önündeki işaret satırı sabitler. Yanlış yöne koyulan işaret, formül kopyalanınca hatalı sonuç verir.
• Başvuruyu formülü yazarken belirleyin: Formülü bir hücreye yazıp kopyalamadan önce hangi hücrelerin sabit kalacağını düşünün. Sonradan düzeltmek için tüm kopyaları yeniden doldurmanız gerekir. F4 tuşu düzenleme sırasında hızlı çözümdür.
• Uçucu işlevlere dikkat edin: KAYDIR ve DOLAYLI işlevleri çalışma sayfasında her değişiklikte yeniden hesaplanır. Çok sayıda hücrede kullanılırsa dosyanız yavaşlayabilir. Mümkünse tablo, tanımlı ad ya da İNDİS ile kurulan dinamik aralıkları tercih edin.
• Tabloya çevrilen veride başvurular: Veriyi tabloya çevirdiğinizde formüllerde A1 tarzı adresler yerine tablo ve sütun adları görünebilir. Bu yapılandırılmış başvurular, tabloya satır eklendiğinde kendiliğinden genişler ve dolar işareti gerektirmez.
• İlgili konular: Hücre başvuruları ile birlikte Formüller > Tanımlı Adlar > Ad Yöneticisi (Formulas > Defined Names > Name Manager), Ekle > Tablo (Insert > Table) ve F4 kısayolunu öğrenmeniz yararlı olur. Dinamik aralıklar için TOPLA, İNDİS, BAĞ_DEĞ_DOLU_SAY, KAYDIR, DOLAYLI ve ÇOKETOPLA işlevlerine de göz atın.

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.