Çözüldü Office 365 LAMBDA ve Recursive Formüllerle Makrosuz Ürün Ağacı (BOM)

veri tablonuzu ve bundan oluşturmak istediğiniz sonuç tablosunu basit bir örnekleme yaparak eklerseniz daha iyi olur sanırım.

Galapagos hocam selamlar, haklısınız ham veriyi netleştirmeden formül kurmak zor. Kusura bakmayın atlamışım. Kafamdaki mantığı ve gitmek istediğim hiyerarşik kırılımı basitçe şöyle örnekleyebilirim:
Elimdeki Ham Veri Yapısı (Giriş):

• Ana Parça (A) | Alt Parça (B)
• ARABA | MOTOR
• ARABA | ŞASİ
• MOTOR | PİSTON
• MOTOR | KRANK MİLİ

Formülle Almak İstediğim Hiyerarşik Çıktı (Sonuç):
Raporlama hücresine "ARABA" yazdığımda, formülün alt kırılımları derinliği fark etmeksizin tarayıp bana şu şekilde basmasını istiyorum:

• ARABA
• -- MOTOR
• ---- PİSTON
• ---- KRANK MİLİ
• -- ŞASİ

Normalde bunu VBA makrolarında iç içe döngülerle (Do While) çözebiliyoruz. Ama kurumsal dosya .xlsx formatında kalacağı için, makro kullanmadan tamamen 365'in kendi özyinelemeli (recursive) LAMBDA fonksiyonu ve Ad Yöneticisi kurgusuyla yapabilir miyiz.

Örnek için teşekkürler. Kırılım hep 2 tane mi ? Yoksa
Örneğin:
Araba
Motor
Piston
Xxxxxxxx

de olabiliyor mu yoksa ?

Galapagos hocam, kırılım sayısı sabit değil, ucu açık olabiliyor. Araba -> Motor -> Piston -> Segman -> Hammadde gibi 4-5 seviyeli derinliklere sahip ürün ağaçlarımız da mevcut.
Bu yüzden formülün katman sınırı olmaksızın, bir alt bileşen bulduğu sürece hiyerarşiyi aşağıya doğru patlatmaya devam etmesi gerekiyor. Ad tanımlamasındaki LAMBDA fonksiyonunun ucu açık çalışabilmesi tam olarak bu yüzden kritikti hocam. Yardımlarınız için şimdiden çok sağ olun.

Güzel bir "problem" :)
Benden hemen yanıt gelmez, araştıracağım. Ama biliyorum ki diğer katılımcılardan yanıt gelecektir. YZ de katkı verir sanırım.

Selamlar ayv74, üretim sektörünün bu ezeli dert küpü olan konusunu makro kullanmadan, tamamen yeni nesil Excel 365 fonksiyonlarıyla çözmek harika bir vizyon.

Bahsettiğiniz gibi ucu açık ilişkisel (Parent-Child) hiyerarşileri döngüye sokup alt alta patlatabilmek için Excel'in kendi kendini çağıran Öz yinelemeli (Recursive) LAMBDA yapısını kurmamız gerekiyor. Sizin için harici hiçbir eklenti veya kod istemeyen, Ad Tanımlama üzerinden çalışan nokta atışı bir formül kurguladım.

1. Excel'de Formüller > Ad Yöneticisi (Name Manager) > Yeni adımlarını takip edin.
2. Ad kısmına tam olarak şu ismi verin: BOM_PATLAT
3. Başvuru Yeri (Refers to) kısmına ise aşağıdaki Recursive LAMBDA formülünü birebir yapıştırın
   
   HTML:
   Kod: Kodları Kopyala

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

(Not: Formüldeki BOM_Tablosu, sizin ham verilerinizin durduğu tablonun adıdır. Sütun isimlerini kendi tablonuza göre [Ana_Urun], [Alt_Bilesen], [Miktar] şeklinde revize edebilirsiniz.)

Bu adı tanımladıktan sonra, Excel sayfasında boş bir hücreye gelip en üstteki ana ürün kodunu yazarak fonksiyonu çağırmanız yeterlidir.
=BOM_PATLAT("ANA_URUN_KODU"; 0)

Bu formül, girdiğiniz ana üründen başlayarak alta doğru tüm kırılımları bulur; her bir bileşenin yanına kaçıncı seviyede (0, 1, 2, 3...) olduğunu dinamik bir dizi (Spill array) şeklinde makrosuz otomatik döker. Veri tabanına yeni satırlar eklense bile formül dinamik olduğu için anında kendini günceller.

Örnek dosyanız üzerinde deneyip sonucu paylaşabilirseniz, gerekirse kırılımdaki kümülatif çarpan miktarları için de ufak bir LET optimizasyonu ekleyebiliriz.

Not : AI desteklidir.

...Normalde bunu VBA makrolarında iç içe döngülerle (Do While) çözebiliyoruz. ....

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

Makronuzu bir modül içine yazıp,
xxx.bas olarak export edin.
yeni dosyada bu xxx.bas dosyanızı tekrar import edip çalıştırın.
çıkışta modülü silin dosyanızı xlsx olrak kaydetmeye devam edin.

Veri Tabanınızı aşağıdaki gibi düzenleseniz daha kolay olmaz mı

Patron Hocam ve Alicimri Hocam, alternatif yaklaşımlarınız için çok teşekkürler.
Patron Hocam, .bas modülünü import/export etme fikri çok zekice ama dosyayı şirketteki son kullanıcılara açacağımız için onların bu teknik adımları manuel yapma şansı maalesef yok. Sürecin sıfır müdahaleyle akması gerekiyor.
Alicimri Hocam, veriyi seviye seviye yan yana kolonlara açmak statik işlerde pratik olsa da ERP'den gelen datada kırılım derinliği ucu açık (bazen 3, bazen 8 katman). Yan yana kolon sabitlediğimizde dinamik yapıyı kaçırıyoruz.

Akuma Hocam, paylaştığınız BOM_PATLAT LAMBDA kurgusunu Ad Yöneticisi'ne girdim ve denedim. Tam istediğim gibi aşağıya doğru dinamik dizi şeklinde kırılımları eksiksiz listeliyor, ellerinize sağlık.
Sadece görsel olarak alt parçaların başına seviyesine göre girinti (örneğin -- veya boşluk) ekleme işini bu LAMBDA kodunun içine nasıl gömebiliriz? O görsel dokunuşu da yapabilirsek sistem tamamen kusursuz olacak.
Destek veren herkese çok teşekkürler.

Akuma Hocam tek kelimeyle muazzam olmuş! YİNELE ve ↳ detayı tam olarak hayal ettiğim o ERP kırılım görüntüsünü verdi. Şu an şablonda hiçbir kayma veya görsel hata olmadan tüm alt kırılımlar merdiven gibi dökülüyor.
Makrosuz, tamamen dinamik dizi formülleriyle kurumsal bir dosyada bu işi çözebilmek harika oldu. Sayenizde çok değerli bir arşive dönüştü bu konu.
Fikir yürüten, destek olan tüm hocalarıma (Galapagos, Patron, Alicimri) ve çözümü sunan Akuma hocama tekrar çok teşekkür ederim.