إذا كانت ورقة عمل Excel الخاصة بك تتضمن حسابات تستند إلى نطاق متغير من الخلايا ، فاستخدم الدالتين SUM و OFFSET معًا في صيغة SUM OFFSET لتبسيط مهمة الحفاظ على العمليات الحسابية محدثة.
تنطبق الإرشادات الواردة في هذه المقالة على Excel لـ Microsoft 365 و Excel 2019 و Excel 2016 و Excel 2013 و Excel 2010.
إنشاء نطاق ديناميكي باستخدام وظائف SUM و OFFSET
إذا كنت تستخدم حسابات لفترة زمنية تتغير باستمرار - مثل تحديد مبيعات الشهر - فاستخدم وظيفة OFFSET في Excel لإعداد نطاق ديناميكي يتغير مع إضافة أرقام مبيعات كل يوم.
في حد ذاتها ، يمكن أن تستوعب وظيفة SUM عادةً إدراج خلايا جديدة من البيانات في النطاق الذي يتم جمعه. يحدث استثناء واحد عندما يتم إدخال البيانات في الخلية حيث توجد الوظيفة حاليًا.
في المثال أدناه ، تتم إضافة أرقام المبيعات الجديدة لكل يوم في أسفل القائمة ، مما يجبر الإجمالي على الانتقال باستمرار لخلية واحدة في كل مرة مع إضافة البيانات الجديدة.
لمتابعة هذا البرنامج التعليمي ، افتح ورقة عمل Excel فارغة وأدخل بيانات العينة. لا يلزم تنسيق ورقة العمل الخاصة بك مثل المثال ، ولكن تأكد من إدخال البيانات في نفس الخلايا.
إذا تم استخدام وظيفة SUM فقط لإجمالي البيانات ، فسيحتاج نطاق الخلايا المستخدمة كوسيطة للدالة إلى التعديل في كل مرة يتم فيها إضافة بيانات جديدة.
باستخدام الدالتين SUM و OFFSET معًا ، يصبح النطاق الإجمالي ديناميكيًا ويتغير ليتناسب مع خلايا البيانات الجديدة. لا تسبب إضافة خلايا بيانات جديدة مشاكل لأن النطاق يستمر في الضبط مع إضافة كل خلية جديدة.
النحو والحجج
في هذه الصيغة ، تُستخدم الدالة SUM لإجمالي نطاق البيانات المتوفرة كوسيطة. نقطة البداية لهذا النطاق ثابتة ويتم تحديدها كمرجع الخلية للرقم الأول الذي سيتم جمعه بواسطة الصيغة.
تتداخل الدالة OFFSET داخل دالة SUM وتقوم بإنشاء نقطة نهاية ديناميكية لنطاق البيانات التي جمعتها الصيغة. يتم تحقيق ذلك عن طريق تعيين نقطة نهاية النطاق إلى خلية واحدة أعلى موقع الصيغة.
صيغة الصيغة هي:
=SUM (نطاق البداية: OFFSET (المرجع ، الصفوف ، الأعمدة))
الحجج هي:
- Range Start: نقطة البداية لنطاق الخلايا التي سيتم تجميعها بواسطة دالة SUM. في هذا المثال ، نقطة البداية هي الخلية B2.
- Reference: مرجع الخلية المطلوب المستخدم لحساب نقطة نهاية النطاق. في المثال ، الوسيطة Reference هي مرجع الخلية للصيغة لأن النطاق ينتهي بخلية واحدة أعلى الصيغة.
- Rows: عدد الصفوف أعلى أو أسفل وسيطة المرجع المستخدمة في حساب الإزاحة مطلوب. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على الصفر. إذا كان موقع الإزاحة أعلى وسيطة المرجع ، تكون القيمة سالبة. إذا كانت قيمة الإزاحة أدناه ، تكون وسيطة الصفوف موجبة. إذا كانت الإزاحة موجودة في نفس الصف ، فإن الوسيطة هي صفر. في هذا المثال ، تبدأ الإزاحة بصف واحد فوق الوسيطة Reference ، لذا فإن قيمة الوسيطة هي سالب واحد (-1).
- Cols: عدد الأعمدة إلى يسار أو يمين الوسيطة Reference المستخدمة لحساب الإزاحة. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على الصفر. إذا كان موقع الإزاحة على يسار وسيطة المرجع ، تكون هذه القيمة سالبة. إذا كانت الإزاحة على اليمين ، تكون وسيطة Cols موجبة. في هذا المثال ، البيانات التي يتم جمعها موجودة في نفس عمود الصيغة ، وبالتالي فإن قيمة هذه الوسيطة هي صفر.
استخدم صيغة SUM OFFSET لإجمالي بيانات المبيعات
يستخدم هذا المثال صيغة SUM OFFSET لإرجاع الإجمالي لأرقام المبيعات اليومية المدرجة في العمود B من ورقة العمل. في البداية ، تم إدخال الصيغة في الخلية B6 وإجمالي بيانات المبيعات لمدة أربعة أيام.
الخطوة التالية هي نقل صيغة SUM OFFSET لأسفل صفًا واحدًا لإفساح المجال لإجمالي مبيعات اليوم الخامس. يتم تحقيق ذلك عن طريق إدراج صف جديد 6 ، والذي ينقل الصيغة إلى الصف 7.
نتيجة النقل ، يقوم Excel تلقائيًا بتحديث الوسيطة Reference إلى الخلية B7 ويضيف الخلية B6 إلى النطاق الذي تم جمعه بواسطة الصيغة.
- حدد الخلية B6، وهو المكان الذي ستظهر فيه نتائج الصيغة في البداية.
-
حدد علامة التبويب الصيغمن الشريط.
Image -
اختر الرياضيات ومثلث.
Image -
حدد SUM.
Image - في مربع الحوار Function Arguments ، ضع المؤشر في مربع النص Number1.
-
في ورقة العمل ، حدد الخلية B2لإدخال مرجع الخلية هذا في مربع الحوار. هذا الموقع هو نقطة النهاية الثابتة للصيغة.
Image - في مربع الحوار Function Arguments ، ضع المؤشر في مربع النص Number2.
-
أدخل OFFSET (B6، -1، 0). تشكل دالة OFFSET هذه نقطة النهاية الديناميكية للصيغة.
Image -
حدد OKلإكمال الوظيفة وإغلاق مربع الحوار. يظهر الإجمالي في الخلية B6.
Image
أضف بيانات مبيعات اليوم التالي
لإضافة بيانات مبيعات اليوم التالي:
- انقر بزر الماوس الأيمن فوق رأس الصف للصف 6.
-
حدد Insertلإدراج صف جديد في ورقة العمل. تنتقل صيغة SUM OFFSET لأسفل صفًا واحدًا إلى الخلية B7 والصف 6 فارغ الآن.
Image - حدد الخلية A6 وأدخل الرقم 5للإشارة إلى إدخال إجمالي المبيعات لليوم الخامس.
-
حدد الخلية B6 ، أدخل $ 1458.25 ، ثم اضغط على أدخل.
Image - تحديثات الخلية B7 إلى المجموع الجديد 7137.40 دولارًا.
عند تحديد الخلية B7 ، تظهر الصيغة المحدثة في شريط الصيغة.
=SUM (B2: OFFSET (B7، -1، 0))
تحتوي الدالة OFFSET على وسيطين اختياريين: الارتفاع والعرض ، اللذان لم يتم استخدامهما في هذا المثال. تخبر هذه الوسائط دالة OFFSET شكل الناتج من حيث عدد الصفوف والأعمدة.
بحذف هذه الوسيطات ، تستخدم الوظيفة ارتفاع وعرض وسيطة المرجع بدلاً من ذلك ، والتي في هذا المثال ارتفاع صف واحد وعرض عمود واحد.
