Çözüldü Excel 365 Recursive LAMBDA Hatası: Çok Seviyeli Ürün Ağacı (BOM) ve Döngüsel Referans

Çok karışık bir anlatım olmuş.
YariMamul_B1 hem Ana_Ürün hemde Alt_Bileşen olarak görünüyor.
Konuyu daha kısa ve net şekilde açıklarsanız
Seviyelenmeyi tablonuzda aşağıdaki gibi düzenleyin.

Alicimri hocam selamlar, öncelikle zaman ayırıp incelediğiniz için çok teşekkür ederim.
Haklısınız, konuyu ilk mesajda teorik olarak anlatmaya çalışırken biraz karmaşıklaştırmış olabilirim. Belirttiğiniz gibi YariMamul_B1 ürünü hem ana ürün hem de alt bileşendir. Bu durum üretimdeki çok seviyeli ürün ağacı (BOM) mantığından kaynaklanıyor; yani bir yarı mamul, üstündeki ana ürünü oluştururken kendisi de altındaki başka bileşenlerden veya hammaddelerden meydana geliyor.
Sizin önerdiğiniz gibi veriyi düz bir satırda kırılım (Türkiye -> Ankara -> Çankaya) haline getirebilseydim standart formüllerle işim çok kolay olurdu. Ancak veri tabanından aldığım ham veri ne yazık ki ucu açık ve sadece ikişerli eşleşmelerden (İlişkisel Hiyerarşi) oluşuyor.
Konuyu tam olarak netleştirmek adına sormak istediğim iki kritik teknik limit var:

1. Sonsuz Döngü Engelleme: Tablo içinde yanlışlıkla A -> B -> C -> A gibi döngüsel bir hata yapıldığında Excel kilitleniyor. Yardımcı sütun/makro kullanmadan, sadece Ad Yöneticisi'ndeki LAMBDA fonksiyonunun kendi içinde bir "ziyaret edilen düğüm listesi" tutarak bu döngüyü yakalaması mümkün müdür?
2. #SAYI! (Stack Overflow) Hatası: Excel 365'te hiyerarşi derinliği 12-13 seviyeye ulaştığında formül bellek sınırına takılıp #SAYI! veriyor. Bu yığın (stack) hafızası limitini REDUCE veya SCAN gibi döngü fonksiyonlarıyla bypass etmenin bir formül hilesi var mıdır?

Eklediğim örnek dosyada verilerin ham hali mevcut. Bu mantıkta döngüsel hataları hissedecek ve kilitlenmeyecek saf bir formül mimarisi kurabilir miyiz? Değerli tecrübelerinize dayanarak fikirlerinizi bekliyorum hocam.

Alicimri hocam ve değerli uzmanlar, konuyu kendi adıma biraz daha netleştirmek ve formülün kilitlenme/hafıza sorununu aşmak için yabancı kaynaklardan da yararlanarak şöyle bir Ad Yöneticisi (LAMBDA) yapısı kurguladım.
Aşağıdaki formül, arama yaptığı esnada uğradığı tüm düğümleri bir metin zinciri (Yol) olarak hafızada tutuyor. Eğer aranan bileşen bu zincirde zaten varsa, döngüyü orada kesip #DÖNGÜ! döndürüyor ve Excel'in kilitlenmesini engelliyor.


HTML:

• Hücrede Kullanımı: =BOM_PATLAT("A_URUNU"; "|")

Bu mantık teorik olarak çalışıyor ancak 12-13. seviyeden sonra hâlâ yığın (stack memory) uyarısı alabiliyorum. REDUCE fonksiyonunu dikey bir yığın yerine yatay bir kuyruk (queue) mimarisine tamamen nasıl çevirebiliriz? Bu hafıza sınırını aşmak için optimize edilmiş başka bir dizi formülü önerisi olan var mıdır?"

eyyup3469' Alıntı:

Alicimri hocam selamlar, öncelikle zaman ayırıp incelediğiniz için çok teşekkür ederim.
Haklısınız, konuyu ilk mesajda teorik olarak anlatmaya çalışırken biraz karmaşıklaştırmış olabilirim. Belirttiğiniz gibi YariMamul_B1 ürünü hem ana ürün hem de alt bileşendir. Bu durum üretimdeki çok seviyeli ürün ağacı (BOM) mantığından kaynaklanıyor; yani bir yarı mamul, üstündeki ana ürünü oluştururken kendisi de altındaki başka bileşenlerden veya hammaddelerden meydana geliyor.
Sizin önerdiğiniz gibi veriyi düz bir satırda kırılım (Türkiye -> Ankara -> Çankaya) haline getirebilseydim standart formüllerle işim çok kolay olurdu. Ancak veri tabanından aldığım ham veri ne yazık ki ucu açık ve sadece ikişerli eşleşmelerden (İlişkisel Hiyerarşi) oluşuyor.
Konuyu tam olarak netleştirmek adına sormak istediğim iki kritik teknik limit var:

1. Sonsuz Döngü Engelleme: Tablo içinde yanlışlıkla A -> B -> C -> A gibi döngüsel bir hata yapıldığında Excel kilitleniyor. Yardımcı sütun/makro kullanmadan, sadece Ad Yöneticisi'ndeki LAMBDA fonksiyonunun kendi içinde bir "ziyaret edilen düğüm listesi" tutarak bu döngüyü yakalaması mümkün müdür?
2. #SAYI! (Stack Overflow) Hatası: Excel 365'te hiyerarşi derinliği 12-13 seviyeye ulaştığında formül bellek sınırına takılıp #SAYI! veriyor. Bu yığın (stack) hafızası limitini REDUCE veya SCAN gibi döngü fonksiyonlarıyla bypass etmenin bir formül hilesi var mıdır?

Eklediğim örnek dosyada verilerin ham hali mevcut. Bu mantıkta döngüsel hataları hissedecek ve kilitlenmeyecek saf bir formül mimarisi kurabilir miyiz? Değerli tecrübelerinize dayanarak fikirlerinizi bekliyorum hocam.

Genişletmek için tıkla...

Deneyiniz

SNNAY Hocam Selamlar,
Zaman ayırıp örnek dosya üzerinde çalışıp çözüm ürettiğiniz ve emeğiniz için çok teşekkür ederim.
Paylaştığınız deneme dosyasını hemen indirdip kendi geniş veri setimde test ettim. Paylaştığınız mantık 3-4 seviyeli yüzeysel ağaçlarda gayet güzel çalışıyor fakat ilk mesajımda bahsettiğim 12+ derinlikteki Stack Overflow (#SAYI!) bellek sınırını ve döngüsel referans kilitlenmelerini ne yazık ki aşamıyor.
Excel 365'in özyinelemeli (Recursive) LAMBDA yapısı doğası gereği dikey bir yığın bellek (Stack Memory) kullanıyor. Bu yüzden derinlik 12-13 seviyeyi geçtiği an formül kaçınılmaz olarak hafıza hatası verip patlıyor. Ayrıca veri setinin içine manuel olarak ters yönlü bir döngü eklediğimde (örn: A -> B -> C -> A), formül bu döngüyü hafızasında takip edemediği için sonsuz döngüye giriyor ve Excel tamamen kilitlenip çöküyor.
Bu iki büyük sorunu (Stack Overflow ve Kilitlenme) makro/VBA olmadan saf formülle aşabilmek için REDUCE fonksiyonunu dikey özyineleme yerine yatay bir genişleme kuyruğu (Breadth-First Search) mantığıyla kurgulamamız gerekiyormuş. Yabancı kaynaklardaki algoritma mantığından yola çıkarak Ad Yöneticisi için şöyle bir yapı kurmaya çalıştım

HTML:
Bu yapı arama yaparken tüm düğümleri |A|B|C| gibi bir metin zinciri olarak hafızada tutuyor ve döngüyü hissettiği an durup Excel'i kilitlemeden #DÖNGÜ! uyarısı veriyor. Dikey bellek yerine yatay yığın kullandığı için de 12 seviyeyi rahatça devirebiliyor.
Emeğiniz ve desteğiniz için tekrardan çok teşekkür ederim hocam, hazırladığınız dosya mantık kurmamda çok yardımcı oldu. Şimdi bu patlatılan kuyruk mimarisinin içine hammadde maliyet çarpanlarını kümülatif olarak nasıl yedirebilirim, onun üzerinde çalışıyorum. Değerli fikirleriniz olursa duymaktan çok mutlu olurum.

52779
Hocam selamlar, emeğinize ve bilgisayarınıza sağlık.

Paylaştığınız her iki dosyayı da hemen indirip kendi geniş ve "derin" veri setimde detaylıca test ettim. Sonuç gerçekten muazzam!

Açıkçası benim Ad Yöneticisi içinde kurmaya çalıştığım o karmaşık Breadth-First Search (BFS) kuyruk mantığına veya dikey LAMBDA özyinelemelerine hiç gerek kalmadan, tek hücreden komple dökülmeli (dynamic array) olacak şekilde harika bir mimari kurmuşsunuz.

En çok korktuğum iki senaryoyu da özellikle test ettim:

1. 12+ Seviye Derinlik (Stack Overflow): Formülünüz hiçbir şekilde belleği şişirmedi, yığın sınırına takılmadan en alt kırılıma kadar tıkır tıkır döküldü. #SAYI! hatasından tamamen kurtulmuş olduk.
   
   
2. Döngüsel Referans (Kilitlenme): Araya bilerek manuel hatalı girişler (A -> B -> C -> A) sıkıştırmama rağmen Excel'i kilitlemedi ve süreci başarıyla yönetti.
   

Eski çalışmanızı tozlu raflardan indirip benim bu zorlu problemime uyarladığınız ve paylaştığınız için çok teşekkür ederim. Bu sayede ürün ağacı patlatma ve hammadde maliyet çarpanlarını kümülatif toplama işini macro/VBA kullanmadan, tamamen saf fonksiyonel formülle çözebilmiş olduk.

Benim durumumda olan ve bu tarz ileri düzey hiyerarşi/BOM raporlaması yapmak isteyen herkese çok değerli bir kaynak oldu.

Konu benim adıma tam anlamıyla ÇÖZÜLMÜŞTÜR. Hakkınızı helal edin, elleriniz dert görmesin hocam.