Excel'de Dairesel Başvurular Nasıl Bulunur

Genellikle en kötü zamanda olur: Dün sorunsuz çalışan bir çalışma sayfası, teslim tarihinden hemen önce dairesel başvuru uyarısı verir.

İyi haber mi? Bu dairesel başvuruları düzeltmek sandığınızdan daha kolay.

Bu kılavuzda şunları açıklayacağım:

• Excel'de dairesel başvurular nasıl bulunur
• Farklı şekillerde nasıl ortaya çıktıklarını anlama
• Kanıtlanmış yöntemlerle nasıl düzelteceğiniz

Excel'de Dairesel Başvurular Nedir?

Excel'de dairesel başvuru, bir formül kendi hücresine (doğrudan veya dolaylı olarak) atıf yaptığında ve Excel'in çözemediği bir hesaplama döngüsü oluşturduğunda meydana gelir. Örneğin, A1 hücresinde =A1+10 varsa, Excel sonucu sonsuza dek hesaplamaya çalışır.

Dolaylı döngüler bile sorun yaratır. A1, B1'e, B1 C1'e ve C1 da A1'e bağlıysa, Excel kilitlenir. Dosya hesaplamak için uzun zaman alabilir, yanlış değerler gösterebilir veya tamamen güncellemeyi durdurabilir.

Bazı dairesel başvurular basit formül hatalarıyken, bazıları gelişmiş modellerde kasıtlı tasarım tercihidir. Bu ayrıma az sonra geleceğim, ancak önce farklı türlerini inceleyelim.

Doğrudan dairesel başvurular

Doğrudan dairesel başvuru, bir formülün kendi hücre adresini içermesiyle oluşur. Diyelim ki A2 ve B2 hücrelerinde iki değeriniz var. C2 hücresine A2 + B2 + C2 yazarsak, C2 doğrudan kendisine atıf yapmış olur. 

Formülü girip Enter tuşuna bastığınızda bir dairesel başvuru pop-up'ı görünür. Tamam'a tıkladığınızda ise formül beklenmedik bir sonuç döndürür.

Doğrudan dairesel başvuru. Görsel: Yazar.

Bu, 0 döndürür çünkü A2 ve B2'yi topladığımızda 6 dönmesi gerekirken 'C2' sürekli kendini ekler. Excel, C2'nin değerini bilmeden C2'yi hesaplayamaz. Bu, asla çözülemeyen anlık bir döngü oluşturur.

Not: Bu dairesel başvurular en kolay tespit edilenlerdir çünkü öz-başvuru doğrudan formül sözdiziminde görülür. Nitekim, Excel formül girildiği anda bunları algılar ve işaretler.

Dolaylı dairesel başvurular

Dolaylı dairesel başvuru, birden fazla hücrenin başlangıç noktasına geri dönen bir bağımlılık zinciri oluşturmasıyla meydana gelir.

Örneğin:

• F2, H4'e atıfta bulunur

• H4, E4'e atıfta bulunur

• E4, F2'ye atıfta bulunur

Dolaylı dairesel başvuru. Görsel: Yazar.

Tek başına bakıldığında hiçbir formül hatalı görünmez. Sorun, Excel tüm zinciri izlediğinde ve hesaplamanın başladığı yere döndüğünü fark ettiğinde ortaya çıkar.

Bunları tespit etmek daha zordur çünkü döngü, Excel dairesel bağımlılığı belirleyene kadar birçok hücreye ve karmaşık çalışma kitaplarında birden fazla çalışma sayfasına yayılabilir.

Gizli dairesel başvurular

Gizli dairesel başvuru, bağımlılık döngüsünün formül sözdiziminden açıkça anlaşılmadığı durumlardır. Bunlar genellikle şuralardan kaynaklanır:

• Adlandırılmış aralıklar

• INDIRECT() veya OFFSET() işlevleri

• Karmaşık koşullu mantık

Örneğin, QuarterlyTotal adlı bir aralık, çeyreklik satış değerlerini içeren C3 ile C5 hücrelerini kapsar.

C5 hücresinde, formül çubuğu şunu gösterir:

=QuarterlyTotal*0.1Altyapı:

İlk bakışta sorun yok gibi görünür. Formül C5'e doğrudan atıfta bulunmaz. Ancak QuarterlyTotal C5'i içerdiği için Excel, C5'i hesaplamak için kullanılan toplamın bir parçası olarak C5'i de hesaplamak zorundadır.

Bu, gizli bir döngü oluşturur:

• Excel QuarterlyTotal'ı hesaplamaya çalışır

• QuarterlyTotal, C5'i içerir

• C5, QuarterlyTotal'a bağlıdır

Formül sözdizimi bunu açıkça göstermese de başvuru daireseldir.

Gizli dairesel başvuruları sorun gidermeyi zorlaştıran da budur. Formüller doğru görünür, çalışma kitabı uzun süre normal hesaplanabilir ve dairesel başvuru ancak Excel adlandırılmış aralığı değerlendirdiğinde su yüzüne çıkar.

Gizli dairesel başvuru. Görsel: Yazar.

Koşullu mantık bunu daha da zor tespit edilebilir hale getirebilir. Neden? Çünkü içiçe IF() ifadeleri yalnızca belirli koşullarda dairesel başvuru oluşturabilir. Sonuç olarak dosya, bazı değerler değişene kadar çalışır.

Gizli dairesel başvurular, bağımlılık döngüsü görünür hücre başvurularıyla değil soyutlamayla gizlendiği için düzeltilmesi en zor olanlardır.

Kasıtlı ve kazara dairesel başvurular

Bazı dairesel başvurular tasarım gereğidir. Diğerleri ise ortadan kaldırmanız gereken hatalardır.

Kasıtlı (bilinçli) dairesel başvurular

Kasıtlı dairesel başvuru, değerlerin birbirine bağlı olduğu belirli modelleme ihtiyaçlarını desteklemek amacıyla bilerek oluşturulur.

Finansal modellemeyi örnek alalım: faiz gideri, borç bakiyesine bağlıdır; borç bakiyesi ise faiz giderini içeren nakit akışına göre değişir. Bu, gerçek dünyadaki iş mantığını yansıtan bir geri besleme döngüsü oluşturur.

Model basit bir yapıyla başlar:

• Borç Bakiyesi açılış tutarını temsil eder
• Faiz Gideri, borcun bir yüzdesi olarak hesaplanır
• Güncellenmiş Borç hem orijinal bakiyeyi hem de faizi içerir

Kasıtlı dairesel başvurular. Görsel: Yazar.

Faiz borca göre hesaplandığında ve güncellenmiş borç bu faizi içerdiğinde, hesaplama doğal olarak kendi üzerine döner. Excel bunu dairesel başvuru olarak işaretler ve varsayılan olarak sıfır döndürür çünkü bağımlılığı tek geçişte çözemaz.

Bunu önlemek için yinelemeli hesaplamayı şu şekilde açın:

1. Şuraya gidin: Dosyalar > Seçenekler > Formüller
2. Hesaplama Seçeneği bölümünde Yinelemeli hesaplamayı etkinleştir kutusunu işaretleyin

Yinelemeli hesaplama seçeneğini etkinleştirin. Görsel: Yazar.

Yinelemeli hesaplama etkinleştirildiğinde Excel, faiz ve güncellenmiş borç değerlerini tekrar tekrar yeniden hesaplar. Her geçişte sayılar biraz ayarlanır ve dengelenip değişmeyi bırakana kadar devam eder. 

Nihai sonuç, tek adımlı bir hesaplamadan ziyade yakınsayan bir çözümü temsil eder. Bu, dairesel başvurunun kasıtlı olmasından dolayı işe yarar. Faiz borcu, borç da faizi etkiler. Her değer diğerini etkilediğinden yinelemeli yeniden hesaplama beklenen ve gereklidir.

Yinelemeli hesaplama. Görsel: Yazar.

Not: Yinelemeli hesaplama tek başına anlamlı bir sonuç garanti etmez. Modelinize yerleştirdiğiniz mantığı tekrarlar; bu nedenle kasıtlı dairesel başvurular dikkatli izleme ve doğrulama gerektirir.

Kazara dairesel başvurular

Kazara dairesel başvuru, formül hataları, kopyala-yapıştır yanlışları veya istenmeyen değişikliklerden kaynaklanır. Diyelim ki A1:A3 hücrelerine 2, 4 ve 6 değerlerini girdiniz.

A4'te bir toplam istiyorsunuz ve şunu giriyorsunuz:

=SUM(A1:A4)Altyapı:

A4 hücresi artık kendisini de hesaplamaya dahil eder.

Enter'a bastığınızda Excel dairesel başvuru uyarısı gösterir. Tamam'a tıkladıktan sonra Excel 0 döndürür.

Kazara dairesel başvuru. Görsel: Yazar.

Bu dairesel başvuru gerçek bir mantığı temsil etmez. Formül aralığı hatalı olduğu için vardır.

Excel'de Dairesel Başvurular Nasıl Düzeltilir

Excel bir döngüyü işaretlediğinde, şimdi tam olarak nerede başladığını bulmanız ve nasıl çözeceğinize karar vermeniz gerekir. 

Dairesel başvuru kazara mı kasıtlı mı olduğuna bağlı olarak birkaç düzeltme yöntemine bakalım: 

Sorunu bulmak için hızlı tespit

Excel, her birinin kendi güçlü ve zayıf yönleri olan bir dizi yerleşik tespit aracı içerir. Bu seçenekleri birlikte inceleyelim. 

Durum çubuğu bildirimleri

Dairesel başvuruyu tespit etmenin en hızlı yolu, Excel penceresinin sol alt köşesindeki durum çubuğudur.

Dairesel başvuru olduğunda Excel, “Circular References: G4.” gibi bir metin gösterir. Bu, döngüye dahil olan en son tespit edilen hücreyi bildirir.

Durum çubuğunda dairesel başvuruyu görüntüleyin. Görsel: Yazar.

Ancak durum çubuğu, aynı anda yalnızca bir dairesel başvuruyu gösterir. Birden fazla döngü varsa Excel bunları teker teker ortaya çıkarır.

Bunu şu şekilde düzeltebilirsiniz: 

1. Durum çubuğunda gösterilen hücreyi düzeltin
2. Durum çubuğunu tekrar kontrol edin
3. Mesaj kaybolana kadar tekrarlayın

İpucu: Her hücre adresini not alın; çünkü bir döngüyü düzeltmek genellikle bir başkasını ortaya çıkarır.

İlk uyarı mesajları

Excel ayrıca şu durumlarda bir uyarı mesajı görüntüler:

• Dairesel bir başvuru oluşturduğunuzda veya
• Zaten dairesel başvuru içeren bir çalışma kitabını açtığınızda

Uyarı genellikle bir veya daha fazla formülün kendine atıfta bulunduğunu ve yanlış hesaplayabileceğini belirtir.

Şu durumlarda bu uyarılar görünmeyebilir:

• Daha önce Tamam'a tıklayarak mesajı kapattıysanız
• Yinelemeli hesaplama etkinse
• Hesaplama modu El ile olarak ayarlandıysa (Formüller > Hesaplama Seçenekleri > El ile)

Sonuçlar yanlış görünüyorsa ve hiçbir uyarı çıkmıyorsa, şu şekilde yeniden hesaplama yapın:

• Etkin çalışma sayfasını yeniden hesaplamak için F9'a basın
• Tüm çalışma sayfalarında yeniden hesaplamayı zorlamak için Ctrl + Alt + F9'a basın

İpucu: Çalışma kitabını kapatıp yeniden açmak da Excel'in uyarıyı tekrar göstermesini sağlayabilir.

Hata denetimi aracı

Birden fazla dairesel başvuruyu aynı anda görmek için Excel'in Hata Denetimi aracını kullanın: Formüller > Hata Denetimi > Dairesel Başvurular

Bu menü, tespit edilen tüm dairesel başvuruları listeler ve her hücreye doğrudan atlamanıza olanak tanır; bu da karmaşık çalışma kitaplarını teşhis etmeyi kolaylaştırır.

Dairesel Başvurular alt menüsü boş görünüyorsa ancak Excel hâlâ hatalı davranıyorsa, listeyi yenilemek için F9'a basarak yeniden hesaplamayı zorlayın.

Dairesel başvuruları bulmak için hata denetimi aracı. Görsel: Yazar.

Not: Excel 2007 ve sonrasında bu özellik Şerit'te bulunur. Daha eski sürümler benzer seçenekleri menü çubuğu üzerinden sunar.

Görsel izleme araçları

Sorunlu bir hücreyi belirledikten sonra, dairesel başvurunun nasıl oluştuğunu görmek için görsel izleme araçlarını kullanın.

Bu araçlar hücreler arasında oklar çizer ve bağımlılıkları formüllerin içinde gizli olmaktan çıkarıp görünür hale getirir. Her ikisi de Formüller sekmesindeki Formül Denetimi grubunda mevcuttur:

• Önceki Bağlantıları İzle, seçilen formüle hangi hücrelerin girdi sağladığını gösterir. Excel, giriş hücrelerinden formül hücresine doğru oklar çizer. Kısayolu Alt + M, P
• Bağımlıları İzle, seçilen hücrenin değerini hangi hücrelerin kullandığını gösterir. Excel, seçili hücreden ona atıfta bulunan formüllere oklar çizer. Kısayolu Alt + M, D

Önceki bağlantıları ve bağımlıları izlemek sonunda başlangıç hücresine geri dönüyorsa, oklar bir döngü oluşturur ve dairesel başvuruyu doğrular.

Önceki Bağlantıları İzle ve Bağımlıları İzle. Görsel: Yazar.

Formülü düzelttikten sonra oklar artık geri dönmez ve dairesel başvuru kaybolur. Aynı menüdeki Okları Kaldır seçeneğiyle okları silebilirsiniz.

Formül yeniden yapılandırma ve alternatif hesaplamalar

Dairesel başvuruların çoğu Excel'in arızalanmasından değil, hesaplamaların yanlış sıralanmasından kaynaklanır. Bu tür kazara dairesel başvurularda, en etkili çözüm formülleri yeniden yapılandırmaktır. 

Bunu düzeltmek için birkaç yöntem şunlardır:  

Kendi kendine bağımlılıkları kaldırmak için formülleri yeniden düzenleyin

Bir formül, değeri kendi sonucunu kullanarak hesaplamaya çalıştığında dairesel başvurular sık görülür.

Örneğin, A2 hücresindeki =A2+1 gibi bir formül, hücre kendisine bağlı olduğundan anında döngü oluşturur. Bunu düzeltmek için gerekli girdiyi ayrı bir hücreye taşıyın ve onun değerine atıfta bulunun.

Bağımlılık kaldırıldığında Excel formülü normal şekilde hesaplayabilir.

Ara hesaplamalar için yardımcı sütunlar kullanın

Tek bir formül birden çok adımı aynı anda yürütmeye çalıştığında da dairesel başvurular ortaya çıkar.

Örneğin, iki hücre ortak bir değeri hesaplamak için birbirine bağlıysa Excel'in net bir başlangıç noktası yoktur. Bunu düzeltmek için, önce ara hesaplamayı yapan bir yardımcı hücre ekleyin.

Ortak değerleri ayrı bir yardımcı hücrede hesaplayarak Excel'in izleyeceği net bir sıra oluşturursunuz:

1. Önce yardımcı hücre hesaplar
2. Bağımlı formüller sonra hesaplanır

Bu yaklaşım formülleri basitleştirir ve dairesel bağımlılıkları ortadan kaldırır.

Özetleri hesaplamalardan ayırın

Toplamlar ve özet formüller, bağlı oldukları hücrelerle aynı yerde olmamalıdır. Bir özet, kendisini de içeren bir aralıktan sayı çektiğinde Excel dairesel başvuru oluşturur. 

Örneğin, A2 1000'lik bir taban tutarı içeriyorsa ve B2 %10 oranını içeriyorsa, C2 toplam tutarı gösterir ve D2 bir ücreti hesaplar.

C2 =A2+D2 ve D2 =C2*$B$2 kullanıyorsa, toplam ücrete; ücret de toplama bağlı olduğundan bir dairesel başvuru oluşur. 

Excel bunlardan herhangi birini önce hesaplayamaz.

Bunu düzeltmek için ücret toplamdan değil, taban değerden hesaplanmalıdır. Bu nedenle D2'yi =A2*$B$2 olarak değiştirin ve C2'yi =A2+D2 olarak bırakın.

Dairesel başvurudan kaçınmak için ayrı hücreler kullanın. Görsel: Yazar.

Artık Excel önce ücreti hesaplayabilir ve ardından taban değere ekleyebilir. Her formülün net bir başlangıç noktası olduğundan dairesel başvuru ortadan kalkar.

Kasıtlı dairesel mantık için yinelemeli hesaplamayı etkinleştirme

Yinelemeli hesaplama, yalnızca dairesel başvurular kasıtlı tasarım öğeleri olduğunda kullanılmalıdır; kazara hatalarda değil. Bu yöntem, meşru geri besleme döngülerinin bulunduğu finansal modeller ve diğer senaryolar için uygundur; dairesel mantık gerçek dünyadaki davranışları yansıtır.

Windows için Excel'de yinelemeli hesaplamayı etkinleştirmek için:

1. Dosya > Seçenekler > Formüller yolunu izleyin
2. Hesaplama seçenekleri altında Yinelemeli hesaplamayı etkinleştir'i işaretleyin
3. Tamam'a tıklayın

Etkinleştirildiğinde Excel, dairesel başvurulara dahil formülleri stabil hale gelene veya bir durdurma koşuluna ulaşana kadar tekrar tekrar hesaplar.

Temel yineleme ayarları

Yinelemeli hesaplamanın davranışını iki ayar kontrol eder:

• Azami Yineleme, Excel'in durmadan önce formülleri kaç kez yeniden hesaplayacağını belirler. 100 yineleme, çoğu iş ve finansal model için iyi çalışır.
• Azami Değişim, Excel'in yeniden hesaplamayı durdurmadan önce hesaplama sonuçları arasındaki farkın ne kadar küçük olması gerektiğini tanımlar. 0,001 değeri, doğruluk ve performans arasında denge sağlayan yaygın bir başlangıç noktasıdır.

Her iki ayarı artırmak da hassasiyeti iyileştirebilir ancak büyük çalışma kitaplarında yeniden hesaplamayı yavaşlatabilir.

Bu yöntemi ne zaman kullanmalı

Yinelemeli hesaplamayı yalnızca şu durumlarda etkinleştirin:

• Dairesel başvuru geçerli iş mantığını temsil ediyorsa
• Modelde meşru geri besleme döngüleri varsa (örneğin, faizin borca bağlı olması ve borcun faize göre değişmesi)
• Değerlerin sonsuza dek salınmak yerine yakınsamasını bekliyorsanız

Bu yöntem, formül hatalarından kaynaklanan kazara dairesel başvurular için bir çözüm değildir.

Dikkate alınması gereken önemli uyarılar

Yinelemeli hesaplamayı etkinleştirmeden önce şu riskleri aklınızda tutun:

• Kazara dairesel başvurular artık uyarı tetiklemeyebilir; bu da hataları tespit etmeyi zorlaştırır.
• Büyük veya karmaşık modeller daha yavaş yeniden hesaplanabilir.
• Değerler dengelenmek yerine salınıyorsa sonuçlar güvenilmez olabilir.

Bu riskler nedeniyle yinelemeli hesaplama her zaman açıkça belgelenmelidir.

Karmaşık modeller için devre kesici uygulaması

Devre kesici, dairesel mantığın ne zaman etkin olacağı üzerinde size kontrol sağlayan gelişmiş bir tekniktir. Dairesel başvuruyu kaldırmak yerine, formül içinde bir kontrol değeri kullanarak bunu bilerek duraklatır veya etkinleştirirsiniz.

Bu yaklaşım, bazı senaryolarda dairesel mantığa ihtiyaç duyan ancak her zaman değil, finansal modelleyiciler ve ileri düzey kullanıcılar için en uygunudur.

Bir devre kesici genellikle şunları kullanır:

• Bir kontrol hücresi (genellikle 0 veya 1 olarak ayarlanır)

• Dairesel ve dairesel olmayan mantık arasında geçiş yapan bir IF ifadesi

Genel desen şöyle görünür:

=IF($A$1=1, circular_logic, non_circular_alternative)Altyapı:

• Kontrol hücresi 0'a eşit olduğunda formül dairesel başvurudan kaçınır
• Kontrol hücresi 1'e eşit olduğunda dairesel mantığın çalışmasına izin verilir

Diyelim ki şu değerleri girdiniz: 

• A1: Devre kesici (0 veya 1)

• A2: Başlangıç bakiyesi = 1000

• B2: Faiz

• C2: Güncellenmiş bakiye

B2'de =IF($A$1=1, C2*10%, A2*10%) ve C2'de =A2+B2 kullanın

Artık:

• A1 = 0 olduğunda faiz başlangıç bakiyesinden hesaplanır. Dairesel başvuru devre dışıdır ve Excel normal şekilde hesaplar.

• A1 = 1 olduğunda faiz güncellenmiş bakiyeden hesaplanır. Dairesel mantık etkin hale gelir ve kasıtlı bir geri besleme döngüsü yaratır.

Devre kesici kullanımı. Görsel: Yazar.

Devre kesici ne zaman kullanılmalı

Devre kesiciler şu durumlarda yardımcıdır:

• Formülleri oluşturup doğrularken dairesel mantığı kapalı tutmak
• Sorunları izole etmek ve sonuçları doğrulamak için dairesel mantığı açıp kapatmak
• Kabulleri ve işleyişi daha kolay açıklamak için modeli dairesel mantık devre dışıyken göstermek

Sorunlu formülleri kaldırıp baştan başlamak

Bu, nükleer seçenektir: Diğer yöntemler başarısız olduğunda kullanın. Umutsuzca dolaşık bağımlılıkları olan çalışma kitapları, mantığı belirsiz devralınmış dosyalar veya zaman baskısının hızlı ama güvenilir bir çözüm gerektirdiği durumlar için uygundur.

Bozuk formülleri çözmeye çalışmak yerine modeli sıfırlar ve yöntemli bir şekilde yeniden inşa edersiniz. 

Nasıl yapacağımıza bakalım.

Adım 1: Yedek bir kopya oluşturun

Herhangi bir değişiklik yapmadan önce çalışma kitabının tam bir yedeğini oluşturun. Bunu yapmak için:

• Çalışma kitabını Excel'de açın

• Dosya > Farklı Kaydet > Gözat yolunu izleyin ve farklı bir konum seçin veya aynı klasörde dosyanızı kaydedin

• Dosyayı yeniden adlandırın; örneğin, Workbook_Backup.xlsx

• Kaydet'e tıklayın

Bir çalışma kitabının yedek kopyasını oluşturun. Görsel: Yazar.

Bu, gerektiğinde daha sonra başvurabilmeniz veya geri alabilmeniz için orijinal formülleri, mantığı ve sonuçları korur.

Bu yöntemi asla bir dosyanın tek kopyası üzerinde denemeyin.

Adım 2: Mevcut yapıyı belgeleyin

Ardından, çalışma kitabının şu an nasıl çalıştığını kaydedin:

• Önemli bölümlerin ekran görüntülerini alın
• Her çalışma sayfasının ne hesapladığını not edin
• Girdileri, çıktıları ve özetleri belirleyin

Bu dokümantasyon, neyin yeniden inşa edilmesi gerektiğini anlamanıza yardımcı olur ve temizlik sırasında önemli mantığın kaybolmasını önler.

Adım 3: Formülleri değerlerle değiştirin

Tüm dairesel bağımlılıkları bir kerede kırmak için etkilenen aralıkları kopyalayın ve Özel Yapıştır kullanın. Formülleri hesaplanan sonuçlarıyla değiştirmek için Sağ tıklayın > Özel Yapıştır > Değerler'i seçin.

Özel Yapıştır seçeneğini kullanın. Görsel: Yazar.

Bu, görünen sayıları geçici bir temel çizgi olarak korurken tüm dairesel başvuruları anında kaldırır.

Adım 4: Formülleri aşamalı olarak yeniden oluşturun

Çalışma kitabını bir kerede değil, bölüm bölüm yeniden oluşturun:

1. Bir mantıksal blok için formülleri ekleyin
2. F9'a basarak yeniden hesaplamayı zorlayın
3. Dairesel başvuru görünmediğini doğrulayın
4. Sonraki bölüme geçin

Adım 5: Sonuçları yedekle karşılaştırın

Her bölüm yeniden oluşturulurken sonuçları yedek dosyayla karşılaştırın. Şunları gösteren farklılıklar fark edebilirsiniz:

• Eksik girdiler
• Hatalı formül mantığı
• Daha önce gizli kalmış hesaplama hataları

Zorlu başvurular için gelişmiş tespit

Bazı dairesel başvurular Excel'in standart araçlarıyla görünmez. Bu gibi durumlarda gelişmiş teknikleri deneyin: 

Kapsamlı tarama için VBA makroları

El ile izleme çok yavaş kaldığında, VBA saniyeler içinde binlerce hücreyi tarayabilir ve tüm çalışma kitabı genelinde dairesel başvuruları belirleyebilir.

Temel yaklaşım şöyledir:

• Visual Basic Düzenleyicisi'ni açmak için Alt + F11 tuşlarına basın.
• Ekle > Modül'e gidin.
• Bir dairesel başvuru tespit makrosu yapıştırın.
• Çalıştırmak için F5 tuşuna basın.

VBA arayüzü. Görsel: Yazar.

İpucu: VBA'yı sıfırdan yazmanız gerekmez. Ne istediğinizi düz bir dille anlatın ve özel bir tespit makrosunu ChatGPT'ye oluşturun.

İşte örnek bir VBA betiği: 

Option Explicit
Public Sub HighlightCircularReferences_ActiveSheet()
    Dim ws As Worksheet, c As Range
    Dim hasAny As Boolean

    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    ' Optional: clear previous highlighting (only on used range)
    On Error Resume Next
    ws.UsedRange.Interior.Pattern = xlNone
    On Error GoTo 0

    ' Scan formula cells only
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If IsCircularFormula(c) Then
            c.Interior.Color = RGB(255, 230, 153) ' light yellow
            hasAny = True
        End If
    Next c

    Application.ScreenUpdating = True

    If hasAny Then
        MsgBox "Circular reference cells highlighted on: " & ws.Name, vbInformation
    Else
        MsgBox "No circular references found on: " & ws.Name, vbInformation
    End If
End Sub

Private Function IsCircularFormula(ByVal startCell As Range) As Boolean
    Dim inPath As Object
    Set inPath = CreateObject("Scripting.Dictionary")

    IsCircularFormula = TraceForCycle(startCell, startCell, inPath, 0)
End Function

Private Function TraceForCycle(ByVal startCell As Range, ByVal curCell As Range, _
                               ByVal inPath As Object, ByVal depth As Long) As Boolean
    Dim prec As Range, a As Range, x As Range
    Dim key As String

    ' Safety limit to avoid deep/huge graphs freezing Excel
    If depth > 200 Then Exit Function

    key = curCell.Parent.Name & "!" & curCell.Address(False, False)

    If inPath.Exists(key) Then
        ' We hit a loop in the current path
        TraceForCycle = True
        Exit Function
    End If

    inPath.Add key, True

    ' Direct precedents (cells referenced by curCell)
    On Error Resume Next
    Set prec = curCell.DirectPrecedents
    On Error GoTo 0

    If Not prec Is Nothing Then
        For Each a In prec.Areas
            For Each x In a.Cells
                ' If any precedent is the start cell -> circular
                If x.Address(External:=True) = startCell.Address(External:=True) Then
                    TraceForCycle = True
                    Exit Function
                End If

                ' Recurse only within worksheets (avoid names/constants)
                If x.Parent Is curCell.Parent Or True Then
                    If TraceForCycle(startCell, x, inPath, depth + 1) Then
                        TraceForCycle = True
                        Exit Function
                    End If
                End If
            Next x
        Next a
    End If

    inPath.Remove key
End FunctionAltyapı:

Dairesel başvurulu hücreleri belirlemek için VBA makrolarını kullanın. Görsel: Yazar.

Bu makro, etkin sayfada formüllerdeki dairesel başvuruları kontrol eder. Bir formül doğrudan ya da diğer hücreler aracılığıyla kendine geri atıfta bulunuyorsa, makro bu hücreleri vurgular; böylece sorunun nerede olduğunu kolayca görebilirsiniz. Bu, Yinelemeli hesaplama AÇIK olsa bile çalışır.

VBA, dairesel başvurulu hücreleri vurguladı. Görsel: Yazar.

Çalışma sayfaları arası inceleme

Birden fazla çalışma sayfasına yayılan dairesel başvuruların tespiti daha zordur çünkü izleme okları sekmeler arasında geçmez.

Şöyle görünebilir:

• A Sayfası, B Sayfası'na atıfta bulunur
• B Sayfası, C Sayfası'na atıfta bulunur
• C Sayfası, A Sayfası'na atıfta bulunur

Bunu tespit etmenin bir yolu, sayfa bağımlılıklarını haritalamaktır. Bunu yapmak için hangi sayfaların diğer sayfalara atıfta bulunduğunu yazın ve bu listede döngüleri arayın.

Ayrıca çapraz sayfa başvurularını bulmak için Bul & Değiştir kullanabilirsiniz:

• Ctrl + H tuşlarına basın

• Bir sayfa adını ! işaretiyle birlikte girin (örneğin, Sheet1!)

• Tümünü Bul'a tıklayın

Excel, o sayfaya atıfta bulunan her formülü listeleyeceğinden sekmeler arası bağımlılıkları izleyebilirsiniz.

Bir çalışma kitabındaki bağlı sayfaları bulun. Görsel: Yazar.

Not: Bul & Değiştir dairesel başvuruları otomatik olarak tespit etmez. Yalnızca çapraz sayfa bağımlılıklarını bulur; döngü olup olmadığını siz analiz edersiniz.

Adlandırılmış aralık sorun giderme

Adlandırılmış aralıklar, formüller artık doğrudan hücre adreslerini göstermediğinden dairesel başvuruları gizleyebilir. İncelemek için:

• “Ad Yöneticisi”ni Ctrl + F3 ile açın
• Her adlandırılmış aralık tanımını kontrol edin
• Formüllerde aynı aralık adına atıfta bulunan hücreleri içeren aralıkları arayın

Adlandırılmış aralıkları kontrol edin. Görsel: Yazar.

Koşullu mantık sorunları

Bazı dairesel başvurular yalnızca belirli koşullar altında ortaya çıkar.

Örneğin:

=IF(A1>100, A2+10, 100)Altyapı:

A1 100'ün altındayken Excel ikinci koşulu kullanır ve dairesel başvuru yoktur. A1 100'ü aştığında Excel birinci koşulu değerlendirir ve A2'yi yine A2'yi kullanarak hesaplamaya çalışır; bu da dairesel başvuruyu tetikler.

Bu sorunları tespit etmek için:

• Tüm koşullu yolları tetiklemek için farklı giriş değerlerini test edin
• Yalnızca belirli koşullar altında görünen dairesel başvuru uyarılarını izleyin

Düzeltmek için dairesel mantığı ayrı yardımcı hücrelere taşıyın ve iç içe IF ifadelerini daha net, adım adım hesaplamalara basitleştirin.

Excel'de Dairesel Başvuruları Nasıl Önlersiniz

Birkaç ön hazırlık alışkanlığı, çalışma kitapları büyüdükçe veya başkalarıyla paylaşıldıkça saatlerce hata ayıklama süresini kurtarabilir. 

Bu nedenle, şu en iyi uygulamaları takip etmelisiniz: 

Çalışma kitabı planlama ve formül tasarımı

İyi yapı, çoğu dairesel başvuruyu daha başlamadan engeller. Şunları yaptığınızdan emin olun:

• Girdileri, hesaplamaları ve çıktıları ayırın; böylece formüller tek, net bir yönde ilerlesin.

• Her şeyi tek bir formülde yapmak yerine yardımcı hücreler veya yardımcı sütunlar kullanın.

• Hesaplamalar, kendi sonuçlarına veya aşağı akıştaki toplamlarına değil, önceki değerlere bağlı olmalıdır.

• Girdi hücrelerini, kendi kendine atıf yapan aralıklardan kaçınmak için toplamlar ve özetlerden uzak tutun.

Kopyala-yapıştır işlemleri ve başvuru yönetimi

Birçok dairesel başvuru kopyala-yapıştır sırasında oluşur. Bu yüzden şunları göz önünde bulundurmalısınız: 

• Önce formülü tek bir hücreye yapıştırın ve tüm aralığa kopyalamadan önce Formül Çubuğu'nu kontrol edin.

• Hareket etmemesi gereken oranlar ve varsayımlar için mutlak başvurular ($A$1 gibi) kullanın.

• Özet formülleri, hesapladıkları aralıkların içine koymayın.

• Açık isimler, bir formülün neye atıfta bulunduğunu belirginleştirir ve aralıklar genişledikçe hataları azaltır.

Dokümantasyon ve düzenli denetim

Hafif dokümantasyon bile çok şey kazandırır. Bu yüzden çalışırken şunları aklınızda tutmalısınız:

• Kısa hücre yorumları ekleyin veya basit bir dokümantasyon çalışma sayfası tutun.

• Önemli sunumlar veya devirler öncesinde Formül Denetimi araçlarıyla formülleri gözden geçirin.

• Kritik dosyaların sürümlerini kaydedin; böylece sorunların ne zaman ortaya çıktığını izleyebilirsiniz.

• Ortak çalışılan dosyalarda değişiklikleri düzenli olarak kontrol edin; böylece küçük hatalar yayılmaz.

Son Düşünceler

Şu anda bir dairesel başvuruyla uğraşıyorsanız, bunun kazara mı kasıtlı mı olduğunu doğrulayın. Bu karar, formülleri yeniden yapılandırmanız, yinelemeyi etkinleştirmeniz veya modelin bir kısmını yeniden tasarlamanız gerekip gerekmediğini belirler.

Mantığın gerçekten anlamlı olduğundan emin olmadan ayarlara veya geçici çözümlere yönelmeyin.

Sorun çözüldüğünde, dosyayı bundan sonra daha kolay kullanılabilir hale getirmek için bir an ayırın. Önemli formüllere kısa notlar ekleyin, çalışma kitabının temiz bir kopyasını kaydedin ve onu kullanan herkesin hesaplamaların nasıl çalışması gerektiğini anladığından emin olun.