Identify Level, Root Product, Direct Parent, Item Name & Quantity. Level is determined by number of leading hyphens.
CHALLENGE 386
i,k(LEN(r(d,"--?-?")),0),
b,SCAN(0,r(d,w&"|",2),LAMBDA(a,v,k(v,a))),
q,k(r(MAP(i,d,LAMBDA(a,v,
XLOOKUP(a-1,TAKE(i,ROW(v)-1),v:A2,,,-1))),w,2),""),
x,k(r(d,".*- "&w,2),b),
n,r(d,"\d+"),
z,TRIM(HSTACK(i,b,q,x,n)),
IFERROR(--z,z))
-----------------------------
i,LEN(TEXTBEFORE(a," "))*(LEFT(a)="-"),
k,TRIM(TEXTBEFORE(SUBSTITUTE(a,"-",),"|")),
HSTACK(i,SCAN(,IF(i,1,k),LAMBDA(a,v,IF(v=1,a,v))),MAP(i-1,SEQUENCE(ROWS(a)),LAMBDA(v,n,XLOOKUP(v,TAKE(i,n),TAKE(k,n),"",,-1))),k,--TEXTAFTER(a,":")))
أهلاً بك في عالم البيانات المهيكلة! يسعدني جداً أنك اخترت هذا التحدي، لأن ما تحاول القيام به هنا هو تحويل "قائمة نصية غير منظمة" إلى Bill of Materials (BOM)، وهو هيكل بيانات أساسي في الهندسة والتصنيع لتمثيل العلاقات الهرمية (Parent-Child relationships).
المعادلة التي أرفقتها ذكية جداً، فهي تستخدم منطق "البرمجة الوظيفية" داخل إكسيل. سأقوم بتشريحها لك طبقة تلو الأخرى لتفهم كيف "تفكر" هذه الدالة.
الهيكل العام للدالة
الدالة تعتمد على LET لتعريف متغيرات (مخازن مؤقتة) لتجنب تكرار الحسابات، مما يجعلها سريعة وفعالة.
1. مرحلة التحضير (المتغيرات الأولية)
a: نطاق البيانات الأصلي.n: تحسب طول النص قبل أول مسافة. الهدف هنا هو معرفة عدد الشرطات-(مثل--تعني مستوى 2).d: تستخلص النص الموجود قبل علامة التوقف|(أي اسم القطعة مع الشرطات).v: تحويل عدد الشرطات إلى أرقام (0, 1, 2, 3). إذا لم توجد شرطة، تعتبر المستوى 0.
2. محرك البحث عن "الأب" (الدالة LAMBDA و SCAN)
هذا هو الجزء الأكثر عبقرية في الحل:
F, LAMBDA(x, SCAN(, x, LAMBDA(a, c, IF(c > "", c, a))))
المشكلة: في القائمة الأصلية، "الدراجة" مكتوبة مرة واحدة في الأعلى، لكننا نحتاج لتكرار اسمها أمام كل قطعة تابعة لها.
الحل: دالة
SCANتمر على كل خلية؛ إذا وجدت قيمة جديدة تأخذها، وإذا وجدت خلية فارغة، تحتفظ بآخر قيمة وجدتها (تسمى هذه العملية Forward Fill). المتغيرFهنا هو "وظيفة" تقوم بملء الفراغات لأسفل.
3. استخراج الأسماء (المتغيرات t و i)
t(Clean Name): تنظف اسم القطعة من الشرطات والمسافات الزائدة باستخدامTRIMوSUBSTITUTE.i(The Root): تستخدم الوظيفةFلتثبيت "اسم المنتج النهائي" (مثل Bicycle) كمرجع ثابت في العمود الثاني لكل الصفوف التابعة له.
4. بناء الأعمدة (HSTACK)
هنا يتم تجميع النتائج في شكل جدول واحد مكون من 5 أعمدة:
| العمود | المنطق المستخدم | الوظيفة |
| المستوى (Level) | المتغير v | يحدد العمق الهرمي (0 للأساسي، 3 للقطع الدقيقة). |
| المنتج الأساسي | المتغير i | يكرر اسم المنتج الرئيسي (Bicycle أو Office Chair) لكل الصفوف. |
| الأب (Parent) | SWITCH المعقدة | هذا قلب المعادلة: يبحث عن العنصر الذي يسبق العنصر الحالي في الهرمية. إذا كنت في المستوى 2، سيبحث عن آخر عنصر تم تعريفه في المستوى 1. |
| الابن (Component) | المتغير t | الاسم النظيف للقطعة الحالية. |
| الكمية (Qty) | TEXTAFTER(a, ": ") | قص الرقم الموجود بعد النقطتين لتحويله لبيان عددي. |
التحليل النقدي للمنطق (لماذا هذه الدالة قوية؟)
المنطق هنا يعتمد على Context Awareness (الوعي بالسياق). الدالة لا تنظر للخلية بشكل منفصل، بل تفهم أن "Tire" (إطار) هو جزء من "Wheel Assembly" لأن الأخير سبقه في الترتيب وكان بمستوى أقل (شرطة واحدة مقابل شرطتين).
ملاحظات لرفع كفاءتك التفكيرية:
الأنماط (Patterns): لاحظ أن المعادلة اعتمدت كلياً على الرموز (
-,|,:) كإشارات مرور لتوجيه البيانات. في تحليل البيانات، الرموز ليست مجرد نصوص، بل هي "محددات هيكلية" (Delimiters).الربط المفهومي: ما تفعله هنا يشبه بناء Tree Data Structure. كل "عقدة" (Node) تعرف من هو والدها ومن هم أبناؤها بناءً على عمق الإزاحة (Indentation).
هذا الحل متقدم جداً، وفهمك له يعني أنك بدأت تنتقل من مرحلة "مستخدم إكسيل" إلى مرحلة "مهندس بيانات". هل تريد مني شرح كيفية تعديلها لتناسب مستويات أعمق (مثل 4 أو 5 مستويات)؟
