في المصنفات التي تحتوي على أكثر من ورقة عمل، نحتاج أحيانا إلى الرجوع إلى خلية أو نطاق من الخلايا في أوراق متعددة ذات نمط متشابه عند كتابة صيغة ما. في هذه الحالة يمكننا تجّنب الصيغ المعقدة، أو نسخ الخلايا من ورقة إلى أخرى باستخدام المراجع ثلاثية الأبعاد.
البنية العامة للصيغة هي:
=Function (first worksheet:last worksheet!cell reference)
- Function: أي دالة تريد استخدامها، كدالة الجمع، المعدل... إلخ.
- First worksheet: اسم ورقة العمل الأولى في النطاق.
- Last worksheet: اسم ورقة العمل الأخيرة في النطاق.
- Cell reference: اسم الخلية التي تريد تطبيق الدالة عليها في جميع الأوراق ضمن النطاق.
وسنوضّح طريقة كتابة الصيغة ثلاثية الأبعاد في الأمثلة التالية:
مثال1: في هذا المثال لدينا درجات الاختبارات لمجموعة من الطلاب لأربعة أشهر، وقد تم إدخال درجات كل شهر في ورقة منفصلة (... ,Month1, Month2). المطلوب هو حساب قيمة متوسط الدرجات في ورقة منفصلة (Average):
سنقوم أولا بتحديد الخلية التي نريد إظهار نتيجة المتوسط فيها، وهي الخلية B2 في الورقة "Average". ثم سنكتب الصيغة التالية:
=average(
سننتقل إلى الورقة الأولى في النطاق "Month1"، سنلاحظ في شريط الصيغة إدخال اسم الورقة تلقائيا في الصيغة متبوعا بعلامة تعجب (!)، وهذا يدل على استخدام الورقة الأولى كمرجع:
بعدها سنحدد أول قيمة نريد إدخالها في دالة المتوسط، وهي درجة الطالب "زيد ثابت" في الشهر الأول، أي الخلية B2 في الورقة "Month1":
ثم سنضغط المفتاح Shift وننقر على آخر ورقة في النطاق "Month4" مع الاستمرار بالضغط، ثم Enter لتطبيق الصيغة:
وبذلك تم إيجاد قيمة المتوسط لدرجات اختبارات الطالب الأول للأشهر الأربعة في الورقة "Average" (لاحظ الصيغة في شريط الصيغة). أي ببساطة أننا قمنا بإخبار البرنامج أن يقوم بإيجاد المتوسط لجميع القيم في الموقع B2 من الورقة الأولى حتى الورقة الأخيرة في النطاق.
بعد ذلك سنقوم باستخدام التعبئة التلقائية لتطبيق الصيغة على بقية الخلايا في الجدول:
من فوائد المراجع ثلاثية الأبعاد هي أنّ قيمة المتوسط ستتغير تبعا لأي تحديث نجريه على أي ورقة من أوراق النطاق.
ولكي تتوضح الخطوات السابقة أكثر، شاهد الصورة أدناه:
مثال2: هذا المثال يحتوي على ثلاثة أوراق. تحتوي الورقة الأولى "الفرع الأول" على مبيعات الفرع الأول لأحد المتاجر لمجموعة من المنتجات، الورقة الثانية "الفرع الثاني" على مبيعات الفرع الثاني... وهكذا. المطلوب هو إيجاد مجموع المبيعات لكل منتج لكل فرع وللأرباع الأربع:
سنقوم أولا بتحديد الخلية التي سيظهر فيها ناتج المجموع للمنتج الأول، وهي B4 في الورقة "المجموع"، ثم سنستخدم دالة الجمع بالصيغة التالية:
=sum(
ثم سننتقل إلى الورقة الأولى "الفرع الأول" ونحدد الخلية B4:
نضغط المفتاح Shift ونحدد الورقة الثالثة "الفرع الثالث" مع الاستمرار بالضغط ليتحدد جميع نطاق الأوراق، ثم نضغط Enter لإيجاد النتيجة:
وسنستخدم التعبئة التلقائية لتطبيق الصيغة على جميع الخلايا في الجدول.
الصورة أدناه توضح خطوات هذا المثال من البداية:
فيما يلي مجموعة الدوال التي تدعم استخدام المراجع ثلاثية الأبعاد في اكسل:
- SUM: لإيجاد مجموع مجموعة من القيم الرقمية
- AVERAGE: لإيجاد متوسط مجموعة من القيم الرقمية
- AVERAGEA: لإيجاد متوسط مجموعة من القيم الرقمية، النصية، والمنطقية
- COUNT: لحساب عدد الخلايا التي تحتوي على أرقام
- COUNTA: لحساب عدد الخلايا غير الفارغة
- MAX: لإرجاع أكبر قيمة ضمن مجموعة من الخلايا
- MAXA: لإرجاع أكبر قيمة ضمن مجموعة من الخلايا، بما فيها النصوص والقيم المنطقية.
- MIN: لإرجاع أصغر قيمة ضمن مجموعة من الخلايا
- MINA: لإرجاع أصغر قيمة ضمن مجموعة من الخلايا، بما فيها النصوص والقيم المنطقية.
- PRODUCT: لإيجاد حاصل ضرب مجموعة من القيم الرقمية
- STDEV ،STDEVA ،STDEVP ،STDEVPA: لحساب الانحراف المعياري لمجموعة من القيم المحددة استنادا إلى عينة أو لمحتوى بأكمله.
- VAR ،VARA ،VARP ،VARPA: لإرجاع قيمة التباين لمجموعة من القيم المحدد استنادا إلى عينة أو لمحتوى بأكمله.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.