Excel formülleriyle çalışırken bazen aşağıdaki uyarı istemini görebilirsiniz.
Bu istem, çalışma sayfanızda döngüsel bir başvuru olduğunu ve bunun formüller tarafından yanlış bir hesaplamaya yol açabileceğini veya yanlış bir hesaplamaya yol açabileceğini söyler. Ayrıca sizden bu döngüsel referans sorununu çözmenizi ve sıralamanızı ister.
Bu eğitimde, dairesel referans hakkında bilmeniz gereken her şeyi ele alacağım ve bunun yanı sıra Excel'de dairesel referanslar nasıl bulunur ve kaldırılır.
O halde başlayalım!
Excel'de Dairesel Referans Nedir?
Basit bir deyişle, bir hücrede bir formüle sahip olduğunuzda döngüsel bir başvuru gerçekleşir - bu, hesaplama için kendi içinde hücreyi (girildiği hücreyi) kullanır.
Bunu basit bir örnekle açıklamaya çalışayım.
A1:A5 hücresinde veri kümeniz olduğunu ve A6 hücresinde aşağıdaki formülü kullandığınızı varsayalım:
=TOPLA(A1:A6)
Bu size dairesel bir referans uyarısı verecektir.
Bunun nedeni, A1:A6 hücresindeki değerleri toplamak istemeniz ve sonucun A6 hücresinde olması gerektiğidir.
Bu, Excel'in A6 hücresine yeni değeri eklemeye devam ettiği ve değişmeye devam ettiği için bir döngü oluşturur (dolayısıyla dairesel bir referans döngüsü).
Excel'de Dairesel Referanslar Nasıl Bulunur?
Döngüsel başvuru uyarı istemi, bunun çalışma sayfanızda bulunduğunu söyleyecek kadar nazik olsa da, bunun nerede olduğunu ve hangi hücre başvurularının buna neden olduğunu söylemez.
Dolayısıyla, çalışma sayfasında döngüsel referansları bulmaya ve işlemeye çalışıyorsanız, bunları bir şekilde bulmanın bir yolunu bilmeniz gerekir.
Excel'de dairesel bir referans bulma adımları aşağıdadır:
- Döngüsel referansa sahip çalışma sayfasını etkinleştirin
- Formüller sekmesini tıklayın
- Formül Düzenleme grubunda, Hata Kontrolü açılır simgesine tıklayın (sağdaki küçük aşağı yönlü ok)
- İmleci Dairesel Referanslar seçeneğinin üzerine getirin. Çalışma sayfasında dairesel referansı olan hücreyi size gösterecektir.
- Hücre adresini (görüntülenen) tıklayın ve sizi çalışma sayfasındaki o hücreye götürecektir.
Sorunu çözdükten sonra, yukarıdaki aynı adımları tekrar uygulayabilirsiniz ve döngüsel referansa sahip daha fazla hücre referansı gösterecektir. Hiçbiri yoksa, herhangi bir hücre başvurusu görmezsiniz,
Döngüsel referansı bulmanın bir başka hızlı ve kolay yolu, Durum çubuğuna bakmaktır. Sol tarafında, hücre adresiyle birlikte Dairesel Referans metnini size gösterecektir.
Döngüsel referanslarla çalışırken bilmeniz gereken birkaç şey vardır:
- Yinelemeli hesaplamanın etkinleştirilmesi durumunda (bu öğreticide daha sonra ele alınacaktır), durum çubuğu dairesel referans hücre adresini göstermez.
- Döngüsel başvurunun etkin sayfada olmaması durumunda (ancak aynı çalışma kitabındaki diğer sayfalarda), hücre adresini değil yalnızca Dairesel başvuruyu gösterecektir.
- Döngüsel referans uyarı istemini bir kez alırsanız ve onu kapatırsanız, bir dahaki sefere istemi tekrar göstermez.
- Döngüsel başvuruya sahip bir çalışma kitabı açarsanız, çalışma kitabı açılır açılmaz size bilgi istemini gösterecektir.
Excel'de Dairesel Referans Nasıl Kaldırılır?
Sayfanızda döngüsel referanslar olduğunu belirledikten sonra, bunları kaldırmanın zamanı geldi (bir nedenden dolayı orada olmalarını istemiyorsanız).
Ne yazık ki, silme tuşuna basmak kadar basit değil. Bunlar formüllere bağlı olduğundan ve her formül farklı olduğundan, bunu duruma göre analiz etmeniz gerekir.
Soruna yanlışlıkla neden olan hücre referansının olması durumunda, referansı ayarlayarak basitçe düzeltebilirsiniz.
Ama bazen, o kadar basit değil.
Dairesel referans, birçok düzeyde birbirini besleyen birden fazla hücreye dayalı olarak da ortaya çıkabilir.
Size bir örnek göstereyim.
Aşağıda C6 hücresinde döngüsel bir başvuru vardır, ancak bu yalnızca basit bir öz başvuru durumu değildir. Hesaplamalarda kullandığı hücrelerin de birbirine referans verdiği çok seviyelidir.
- A6 hücresindeki formüller =TOPLA(A1:A5)+C6'dır.
- Formül C1 hücresidir =A6*0.1
- C6 hücresindeki formül =A6+C1'dir.
Yukarıdaki örnekte, C6 hücresindeki sonuç, sırayla C6 hücresine bağlı olan A6 ve C1 hücresindeki değerlere bağlıdır (böylece dairesel referans hatasına neden olur)
Ve yine, sadece demo amaçlı gerçekten basit bir örnek seçtim. Gerçekte, bunları anlamak oldukça zor olabilir ve belki de aynı çalışma sayfasında çok uzakta olabilir veya hatta birden çok çalışma sayfasına dağılmış olabilir.
Böyle bir durumda, dairesel referansa neden olan hücreleri tanımlamanın ve ardından bunları tedavi etmenin bir yolu vardır.
Emsalleri İzle seçeneğini kullanarak.
Aşağıda, dairesel referansa sahip hücreye beslenen hücreleri bulmak için izleme emsallerini kullanma adımları verilmiştir:
- Dairesel başvuruya sahip hücreyi seçin
- Formüller sekmesini tıklayın
- Emsalleri İzle'ye tıklayın
Yukarıdaki adımlar, seçili hücredeki formüle hangi hücrelerin beslendiğini söyleyen mavi okları gösterir. Bu şekilde formülleri ve hücreleri inceleyebilir ve döngüsel referanstan kurtulabilirsiniz.
Karmaşık finansal modellerle çalışıyorsanız, bu emsallerin de birden fazla düzeyde derinlere inmesi mümkün olabilir.
Aynı çalışma sayfasındaki hücrelere atıfta bulunan tüm formüllere sahipseniz, bu iyi çalışır. Birden çok çalışma sayfasındaysa, bu yöntem etkili değildir.
Excel'de Yinelemeli Hesaplamaları Etkinleştirme/Devre Dışı Bırakma
Bir hücrede dairesel bir referansınız olduğunda, önce aşağıda gösterildiği gibi uyarı istemini alırsınız ve bu iletişim kutusunu kapatırsanız, hücrede sonuç olarak size 0 verecektir.
Bunun nedeni, döngüsel bir başvuru olduğunda bunun sonsuz bir döngü olması ve Excel'in buna kapılmak istememesidir. Yani 0 döndürür.
Ancak bazı durumlarda döngüsel referansın aktif olmasını ve birkaç yineleme yapmasını isteyebilirsiniz. Böyle bir durumda sonsuz bir döngü yerine döngünün kaç kez çalıştırılacağına karar verebilirsiniz.
buna denir Excel'de yinelemeli hesaplama.
Excel'de yinelemeli hesaplamaları etkinleştirme ve yapılandırma adımları aşağıdadır:
- Dosya sekmesine tıklayın
- Seçenekler'e tıklayın. Bu, Excel Seçenekleri iletişim kutusunu açacaktır
- Sol bölmede Formül'ü seçin
- Hesaplama seçenekleri bölümünde, 'Yinelemeli hesaplamayı etkinleştir' kutusunu işaretleyin. Burada maksimum yinelemeleri ve maksimum değişiklik değerini belirleyebilirsiniz.
Bu kadar! Yukarıdaki adımlar, Excel'de yinelemeli hesaplamayı etkinleştirir.
Yinelemeli hesaplamadaki iki seçeneği de hızlıca açıklayayım:
- Maksimum yineleme: Bu, Excel'in size nihai sonucu vermeden önce hesaplamasını istediğiniz maksimum sayıdır. Yani bunu 100 olarak belirlerseniz, Excel size nihai sonucu vermeden önce döngüyü 100 kez çalıştıracaktır.
- Maksimum Değişim: Bu, iterasyonlar arasında elde edilmezse hesaplamanın durdurulacağı maksimum değişikliktir. Varsayılan olarak değer .001'dir. Bu değer ne kadar düşük olursa, sonuç o kadar doğru olur.
Yinelemeler ne kadar çok çalışırsa, Excel'in bunu yapması için o kadar fazla zaman ve kaynak gerektiğini unutmayın. Maksimum yinelemeleri yüksek tutarsanız, Excel'inizin yavaşlamasına veya çökmesine neden olabilir.
Not: Yinelemeli hesaplamalar etkinleştirildiğinde, Excel size dairesel başvuru uyarı istemini göstermez ve şimdi bunu durum çubuğunda da gösterir.Dairesel Referansları Kasıtlı Olarak Kullanmak
Çoğu durumda, çalışma sayfanızda döngüsel referans bulunması bir hata olur. İşte bu nedenle Excel size "Bu referansları kaldırmayı veya değiştirmeyi ya da formülleri farklı hücrelere taşımayı deneyin" diyen bir komut istemi gösterir.
Ancak, istenen sonucu alabilmeniz için dairesel bir referansa ihtiyaç duyduğunuz bazı özel durumlar olabilir.
Excel'deki bir hücredeki bir hücrede zaman damgası almak hakkında zaten yazdığım böyle özel bir durum.
Örneğin, bir formül oluşturmak istediğinizi varsayalım, böylece Sütun A'daki bir hücreye her giriş yapılır, zaman damgası Sütun B'de görünür (aşağıda gösterildiği gibi):
Aşağıdaki formülü kullanarak kolayca bir zaman damgası ekleyebilirsiniz:
=EĞER(A2"",EĞER(B2"",B2,ŞİMDİ()),"")
Yukarıdaki formülle ilgili sorun, çalışma sayfasında herhangi bir değişiklik yapılır yapılmaz veya çalışma sayfası yeniden açıldığında (ŞİMDİ formülü geçici olduğundan) tüm zaman damgalarını güncellemesidir.
Bu sorunu aşmak için döngüsel bir başvuru yöntemi kullanabilirsiniz. Aynı formülü kullanın, ancak yinelemeli hesaplamayı etkinleştirin.
Döngüsel referans kullanma yeteneğinin istendiği başka durumlar da vardır (burada bir örnek bulabilirsiniz).
Not: Döngüsel referansı kullanabileceğiniz bazı durumlar olsa da, bunu kullanmaktan kaçınmanın en iyisi olduğunu düşünüyorum. Dairesel referanslar da çalışma kitabınızın performansını olumsuz etkileyebilir ve yavaşlatabilir. İhtiyaç duyduğunuz nadir durumlarda, işi halletmek için her zaman VBA kodlarını kullanmayı tercih ederim.Umarım bu öğreticiyi faydalı bulmuşsunuzdur!
Yararlı bulabileceğiniz diğer Excel eğitimleri:
- #REF! Excel'de Hata; Referans Hatası Nasıl Düzeltilir!
- Excel VBA Hata İşleme
- #N/A Hatalarından Kurtulmak için EĞERHATA DÜŞEYARA ile birlikte kullanın
- Excel'de Başka Bir Sayfaya veya Çalışma Kitabına Nasıl Başvurulur (Örneklerle)
- Excel'de Mutlak, Göreli ve Karma Hücre Başvuruları
