4.3الصيغ الديناميكية: FILTER، SORT، UNIQUE، LAMBDA
الأساسي في 30 ثانية
- صيغة واحدة، نتائج متعددة: FILTER يستخرج، وSORT يفرز، وUNIQUE يزيل التكرار — والنتيجة تنسكب (spill) على الخلايا المجاورة وتتحدّث وحدها.
- الرمز # يدلّ على المنطقة المنسكبة: إذا كانت D2 تحتوي =UNIQUE(...)، فإن D2# يشير إلى القائمة كلها — و#SPILL! تعني «أفرِغ المساحة».
- ردّ فعل وسيم: التركيب (SORT(UNIQUE(...))) لقوائم حيّة، وتسمية LAMBDA صغيرة (مثل ضريبة 19 %) بدل تكرار الحساب.
حالة مصغّرة متسلسلة — وسيم يُنتج قوائم حيّة للإقفال
وسيم، محاسب في الجزائر، يحضّر الإقفال: يحتاج قائمة الولايات المميّزة (مفروزة، محدّثة) واستخراج مبيعات الجزائر، دون إعادة مصافي يدوية كل مرة. يكتب =SORT(UNIQUE(Ventes[Région])) و=FILTER(Ventes;Ventes[Région]="Alger") : فينسكب الجدولان ويتبعان البيانات. يشير إلى القائمة عبر D2# في ملخّصاته وينشئ LAMBDA باسم TVA19 للمبالغ مع الضريبة. المعلم الثالث للمسار الرئيسي: قوائم تحيا مع البيانات.
تكملة حالات 4.1-4.2 — جداول وسيم المنظّفة والمنمذجة تُستثمر في صيغ حيّة.
1. صيغة تُعيد جدولاً
تُعيد الصيغ الديناميكية (Microsoft 365) عدة نتائج «تنسكب» (spill) على الخلايا المجاورة، دون نسخها. FILTER يستخرج الأسطر المستوفية لشرط؛ وSORT يفرز؛ وUNIQUE يحذف التكرارات. وتتحدّث النتيجة وحدها عند تغيّر المصدر — فتبقى قائمة ولايات وسيم صحيحة دون تدخّل.
نركّبها: =SORT(UNIQUE(Ventes[Région])) يعطي في صيغة واحدة قائمة منزوعة التكرار ومفروزة. صيغة واحدة تملأ عدة خلايا وتتبع البيانات: نهاية التلاعبات اليدوية المتكرّرة.
2. الانسكاب (#)
يُشار إلى منطقة الانسكاب بـ#: إذا كانت D2 تحتوي =UNIQUE(...)، فإن D2# يدلّ على القائمة الناتجة كلها — حتى عندما تنمو. لذا نبني الملخّصات على D2#، لا على نطاق مجمّد.
إذا حجبت خلية الانسكاب، يُعيد Excel الخطأ #SPILL! — ويكفي إفراغ المساحة تحت النتيجة ويمينها. ولا ننسخ صيغة ديناميكية أبداً: فهي تملأ منطقتها وحدها.
3. LAMBDA: أنشئ دوالك
تتيح LAMBDA تعريف دالتك الخاصة القابلة لإعادة الاستخدام، مسمّاة في إدارة الأسماء، دون VBA. مثال: دالة TVA19(x) = x*1,19 لضريبة القيمة المضافة الجزائرية بنسبة 19 %. ومع MAP/REDUCE/BYROW، تفتح حسابات أنيقة دون ماكرو. إنها ذروة الصيغ الحديثة.
احذر التوافق: تتطلّب FILTER/SORT/UNIQUE/LAMBDA إصدار Microsoft 365 أو Excel 2021+. في الإصدارات الأقدم لا وجود لها: نعود إلى المصافي أو الجداول المحورية أو الصيغ المصفوفية القديمة.
🔎 مثال ملموس — 🔎 مثال ملموس — استخراج وفرز وإزالة تكرار في صيغة واحدة
جدول مبيعات وسيم؛ قائمة الولايات ومبيعات الجزائر:
=SORT(UNIQUE(Ventes[Région])) → قائمة منزوعة التكرار ومفروزة (تنسكب)
=FILTER(Ventes; Ventes[Région]="Alger") → كل أسطر الجزائر (تنسكب)
الإشارة إلى النتيجة في مكان آخر: D2# (كل المنطقة المنسكبة)
// LAMBDA مسمّاة TVA19 (إدارة الأسماء):
=LAMBDA(montant; montant*1,19)
= TVA19([@Montant]) → المبلغ مع الضريبة 19 %القراءة: صيغة واحدة تملأ عدة خلايا وتتبع البيانات.
الدرس: FILTER/SORT/UNIQUE تحلّ محلّ تلاعبات يدوية متكرّرة.
مفردات القسم
- صيغة ديناميكية
- صيغة تُعيد جدولاً «منسكباً» (spill)، على Microsoft 365.
مثال بسيط: FILTER لدى وسيم يملأ وحده عشرات الأسطر.
ما ليس كذلك: صيغة كلاسيكية تُنسَخ نحو الأسفل.
رد فعل: نتيجة متعددة الأسطر: اكتب صيغة واتركها تنسكب.
- FILTER
- يستخرج الأسطر المستوفية لشرط معطى.
مثال بسيط: يستخرج وسيم كل مبيعات الجزائر في صيغة واحدة.
ما ليس كذلك: مصفي يدوي يُعاد كل إقفال.
رد فعل: مجموعة فرعية حيّة: فكّر في FILTER.
- SORT / UNIQUE
- يفرز نطاقاً (SORT) أو يحذف تكراراته (UNIQUE).
مثال بسيط: ينتج وسيم قائمة ولاياته بـ SORT(UNIQUE(...)).
ما ليس كذلك: فرز يدوي وحذف تكرار بنقرات.
رد فعل: قائمة مرجعية: ركّب SORT(UNIQUE(...)).
- LAMBDA
- يعرّف دالتك الخاصة القابلة لإعادة الاستخدام والمسمّاة، دون VBA.
مثال بسيط: يسمّي وسيم TVA19 لتطبيق الضريبة 19 % في كل مكان.
ما ليس كذلك: ماكرو أو نسبة مكرّرة مباشرة في كل صيغة.
رد فعل: حساب متكرّر: سمِّه بـ LAMBDA.
في التطبيق — عملياً — صيغ تنسكب
- اكتب =UNIQUE(عمود) للحصول على القيم المميّزة.
- غلّفها بـ =SORT(UNIQUE(...)) لفرز القائمة.
- استخرج مجموعة فرعية بـ =FILTER(جدول ; شرط).
- أنشئ دالة LAMBDA صغيرة مسمّاة (مثل TVA19) واستخدمها.
سكربت جاهز للقول — أظهر الانسكاب لزميل (30 ثانية)
«هذا وسيم: انظر، صيغة واحدة تملأ قائمة الولايات كاملة — وحين أضيف مبيعة، تتحدّث وحدها. أستخرج الجزائر بـ FILTER، وأشير عبر D2#، وضريبة 19 % دالة مسمّاة. انتهت المصافي المُعادة يدوياً.»
قُله عند الإقفال: اكتب الصيغة أمامهم وأضف سطراً لتُظهر التحديث.
قالب نسخ-لصق — الثلاثي الديناميكي + الضريبة
=SORT(UNIQUE(Ventes[Région]))
=FILTER(Ventes; Ventes[Région]="Alger")
=SORT(UNIQUE(FILTER(Ventes[Ville]; Ventes[Région]="Alger")))
// في إدارة الأسماء، سمِّ TVA19 :
// =LAMBDA(montant; montant*1,19)قائمة المندوبين
كما وسيم: =SORT(UNIQUE(Ventes[Commercial])) لقائمة حيّة بالمندوبين.
أعلى المتأخّرات
=FILTER(Ventes;Ventes[Statut]="Impayé") : يتبع الاستخراج التسويات دون إعادة مصفي.
الإشارة في مكان آخر
ابنِ الملخّصات على D2#: حين تنمو القائمة، يتبع كل شيء آلياً.
إصدار بلا 365
FILTER/SORT/UNIQUE غائبة: نعود إلى مصفاة تلقائية أو جدول محوري أو صيغ مصفوفية قديمة.
✓ ممارسات جيدة
- اترك مساحة للانسكاب.
- أشِر إلى منطقة منسكبة بـ # (D2#).
- اجمع FILTER/SORT/UNIQUE بالتركيب.
✗ أخطاء شائعة
- خلية مشغولة تحت النتيجة ← #SPILL!.
- نسخ صيغة ديناميكية بلا داعٍ.
- استخدام هذه الدوال على إصدار لا يملكها (قبل 365/2021).
النقاط الأساسية
- الصيغ الديناميكية تُعيد جدولاً ينسكب (spill).
- FILTER (استخراج)، SORT (فرز)، UNIQUE (إزالة تكرار).
- يُشار إلى المنطقة المنسكبة بـ # (مثل D2#).
- LAMBDA ينشئ دوالاً قابلة لإعادة الاستخدام دون VBA.
تحدّي 48 ساعة — طبّقه غداً
على جدول حقيقي: اكتب =SORT(UNIQUE(عمود))، واستخرج مجموعة فرعية بـ FILTER، وأشِر بـ # وسمِّ LAMBDA لحساب متكرّر.
- أعرف كيف أستخرج وأفرز وأزيل التكرار بصيغ ديناميكية.
- أعرف كيف أشير إلى منطقة منسكبة بـ # وأتعامل مع #SPILL!.
- أعرف كيف أسمّي وأستخدم دالة LAMBDA بسيطة.
أسئلة شائعة
لماذا #SPILL!؟
صيغة ديناميكية تريد «الانسكاب» لكن خلية في المنطقة الهدف مشغولة. أفرِغ المساحة تحت النتيجة/يمينها، فتملأ الصيغة كل المنطقة.
هل تعمل هذه الدوال في كل الإصدارات؟
FILTER/SORT/UNIQUE/LAMBDA موجودة في Microsoft 365 وExcel 2021+. في الإصدارات الأقدم لا وجود لها: نعود إلى مصافي أو جداول محورية أو صيغ مصفوفية قديمة (Ctrl+Shift+Enter).
ماذا تفعل دالة UNIQUE؟