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

[eyyup3469]

Selamlar Değerli Üstatlar ve Excel Uzmanları,
Excel 365 fonksiyonlarının sınırlarını zorlayarak, üretim sektöründe genellikle VBA makro veya SQL hiyerarşi sorgularıyla yapılan "Çok Seviyeli Ürün Ağacı (Bill of Materials - BOM) Patlatma ve Maliyetlendirme" problemini tamamen dinamik dizi formülleriyle çözmek istiyorum. Ancak Google üzerinde yaptığım araştırmalarda net bir çözümünü bulamadığım, tamamen bellek mimarisi ve algoritma mantığına dayalı iki büyük sorunla karşılaştım.
Senaryo ve Veri Yapısı:
Elimde iki sütunlu bir hiyerarşi tablosu var: Ana_Ürün, Alt_Bileşen ve Kullanım_Miktarı. Bir ürün, başka bir ürünün alt bileşeni olabiliyor ve bu hiyerarşi ucu açık şekilde 7-8 seviye derinliğe kadar uzanıyor (Örn: A -> B -> C -> D -> E...). Ayrıca en alt seviyedeki hammaddelerin bir de Birim_Maliyet tablosu mevcut.
Yapmak İstediğim:
Herhangi bir ana ürün kodunu girdiğimde, Excel'in Ad Yöneticisi (Name Manager) üzerinden tanımlayacağım Özyinelemeli (Recursive) bir LAMBDA fonksiyonu yardımıyla, o ürüne bağlı tüm alt kırılımları seviyelerine göre bulup, hammadde maliyetlerini en dipten yukarıya doğru kümülatif olarak toplatarak ana ürünün toplam maliyetini tek bir hücrede hesaplatmak.

1. Döngüsel Referans (Circular Dependency) ve Excel Kilitlenme Hatası: Veri setinin içinde hatalı girişlerden dolayı döngüsel eşleşmeler oluşuyor (Örn: X ürünü Y'yi oluşturuyor, Y ürünü Z'yi oluşturuyor, ancak Z ürününün altında yanlışlıkla tekrar X tanımlanmış). Recursive LAMBDA bu döngüye girdiğinde Excel doğrudan kilitleniyor veya çöküyor. Formülün içine, daha önce ziyaret edilen hücreleri/düğümleri hafızada tutup, döngüyü sezdiği an sonsuz döngüye girmeden hata (#DÖNGÜ!) döndürecek bir Ziyaret Matrisi (Ziyaret Edilen Düğümler) kurgusu M dili veya VBA olmadan sadece formülle nasıl gömülebilir?
2. Stack Overflow (#SAYI!) Hafıza Sınırı: Excel'in özyinelemeli (recursive) LAMBDA fonksiyonları için arka planda ayırdığı yığın (stack memory) derinliği limiti nedir? Yaklaşık 12 seviyeli derin bir ağaç yapısında #SAYI! (Stack Overflow) hatası alıyorum. SCAN veya REDUCE fonksiyonlarını kullanarak bu derinlik sınırını bypass etmenin veya döngüyü optimize etmenin bir yolu var mıdır?

Hammadde maliyetlerini yukarıya doğru hatasız toplayacak, döngüsel hatalara karşı korumalı ve derin hiyerarşileri kaldırabilecek "Saf Fonksiyonel" (Pure Functional) bir formül mimarisi kurabilir miyiz?
Hocalarım, test edebilmeniz için örnek veri tablolarını aşağıda paylaşıyorum. Doğrudan kopyalayıp Excel sayfanıza yapıştırarak kullanabilirsiniz. Değerli fikirlerinizi ve çözüm önerilerinizi merakla bekliyorum.

 

[Alicimri]

Ç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.

Türkiye

Ankara

Çankaya

Kavaklıdere

 

[eyyup3469]

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.

 

[eyyup3469]

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:

İçeriği görebilmek için Giriş yap ya da Üye ol.

• 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?"

 

[SNNAY]

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.

Deneyiniz

 

[eyyup3469]

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:

İçeriği görebilmek için Giriş yap ya da Üye ol.

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]

Nasıl bir sonuç çıktısı alunacağına dair bir bilgi bulamadığım için,
Biraz eski bir çalışmamdan bazı düzenlemeler yaparak Urun Ağacı için TEK hücrede formül ile komple dökülmeli bir çözüm...



Bu çözümün aynısımım sizin dosyaya uyarlanmış hali ise



Dosyaları da ekliyorum...
Ben ne zaman dediğiniz hatalarla karşılaşırım şeklinde bir deneme yapmadım. Siz deneme yaparsınız...

 

[eyyup3469]

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.