النطاقات الديناميكية في Excel: متى تستخدم OFFSET أو جدولًا
حدّث الإجماليات وخيارات القوائم المنسدلة تلقائيًا عند إضافة صفوف. تعرّف على OFFSET وCOUNTA وأثر المعرّفات الفارغة وكيفية استخدام جداول Excel.
قد تضيف منتجًا إلى قائمة Excel ويظل المجموع وخيارات القائمة المنسدلة كما هي. إذا بقي المرجع A2:A10، فالصف الحادي عشر خارج النطاق. يتيح النطاق الديناميكي لـ Excel تحديد نهاية القائمة دون الحاجة إلى تعديل الصيغ مع كل تحديث.
حساب ارتفاع النطاق وعرضه بواسطة OFFSET
في قائمة متصلة، احسب عدد عناوين الأعمدة ومعرّفات المنتجات لتحديد عرض OFFSET وارتفاعه. أنشئ المثال في Sheet1 وأدخل صيغة المجموع في ورقة أخرى. اكتب نصوص العناوين اليابانية كما هي لتعمل المراجع المصنفة لاحقًا.
| الخلية | العمود A | العمود B | العمود C |
|---|---|---|---|
| الصف 1 | 商品ID (معرّف المنتج) |
商品名 (اسم المنتج) |
数量 (الكمية) |
| الصف 2 | A01 | قلم | 4 |
| الصف 3 | A02 | دفتر | 6 |
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))
ابدأ من A1 دون إزاحة. يحدد عدد قيم العمود A الارتفاع، وعدد العناوين في الصف الأول العرض. يعود المثال بالنطاق A1:C3، ثلاثة صفوف في ثلاثة أعمدة، والمجموع 10. أضف A03، مسطرة، و2 في الصف الرابع ليمتد النطاق إلى A1:C4 ويصبح المجموع 12. المعرّفات نصية ويُفترض ألا تتضمن نطاقات العد بيانات أخرى. مرجع Microsoft لدالة OFFSET
امسح الآن المعرّف وحده في A3. يبقى في A ثلاث خلايا تُحسب: العنوان A1 وخليتا المعرّف A2 وA4. لذا تُرجع OFFSET أول ثلاثة صفوف، A1:C3. تبقى الكمية 6 من الصف الثالث، لكن الكمية 2 من الرابع تخرج من النطاق، فيعود المجموع إلى 10.
قد يستبعد عنوان فارغ في وسط القائمة الأعمدة الأخيرة أيضًا. تحسب COUNTA الخلايا التي تحتوي على صيغة تُرجع سلسلة نصية فارغة (""). في مصنف موجود، اجعل العناوين والمعرّفات متصلة وافحص حدود النطاق والخلايا الفارغة. لا يتغير الارتفاع إذا كانت الكمية فارغة والمعرّف موجودًا. مرجع Microsoft لدالة COUNTA
عندما تبدأ العناوين من الصف الثاني
إذا كان الصف الأول لعنوان التقرير، ابدأ الجدول في A2 واحسب الارتفاع والعرض بدءًا من الصف الثاني. بعد نقل المثال صفًا إلى الأسفل، يصبح نطاق العناوين والمنتجين A2:C4 ويظل الإجمالي 10.
=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))
استخدام جدول يتمدد عند إضافة صفوف
لقائمة جديدة، حدد النطاق مع العناوين واختر إدراج ← جدول. سمّ الجدول Items. تجمع الصيغة التالية الكميات من خلية خارج الجدول، ويتمدد نطاقها عند إضافة صفوف. دليل Microsoft للمراجع المصنفة
=SUM(Items[数量])
جمع الصفوف التي تحتوي على معرّف منتج فقط
بعد تحويل A1:C4 إلى الجدول Items، تحسب الصيغة السابقة 4 + 6 + 2 وتعيد 12 حتى مع فراغ A3. لاختيار الصفوف التي لها معرّف، استخدم SUMIF بشرط ألا يكون معرّف المنتج فارغًا. تُجمع القيمتان في الصفين الثاني والرابع، 4 + 2، فتكون النتيجة 6. مرجع Microsoft لدالة SUMIF
=SUMIF(Items[商品ID],"<>",Items[数量])
ربط خيارات القائمة المنسدلة بالجدول
لعرض أسماء المنتجات في قائمة منسدلة على ورقة أخرى، افتح الصيغ ← مدير الأسماء في Excel لسطح المكتب. أنشئ الاسم ItemChoices بنطاق المصنف واكتب =Items[商品名] في حقل «يشير إلى». اضبط التحقق من صحة بيانات خلية الإدخال على «قائمة» والمصدر على =ItemChoices. لا يظهر عنوان العمود ضمن الخيارات، وتضيف الصفوف الجديدة في آخر الجدول أسماء جديدة. املأ الاسم عند إضافة كل صف لتجنب الخيارات الفارغة. دليل Microsoft لإنشاء قائمة منسدلة