Excel SUM وصيغة OFFSET

إذا كانت ورقة عمل Excel الخاصة بك تتضمن حسابات تستند إلى نطاق متغير من الخلايا ، فإن استخدام الدالتين SUM و OFFSET معًا في صيغة SUM OFFSET يبسّط مهمة الحفاظ على الحسابات محدثة.

إنشاء نطاق ديناميكي مع وظائف SUM و OFFSET

إذا كنت تستخدم الحسابات لفترة زمنية تتغير باستمرار - مثل إجمالي المبيعات للشهر - فإن وظيفة OFFSET في Excel تسمح لك بإعداد نطاق ديناميكي يتغير مع إضافة أرقام مبيعات كل يوم.

في حد ذاته ، يمكن أن تستوعب الدالة SUM عادة إدخال خلايا جديدة من البيانات في النطاق يتم جمعها.

يحدث استثناء واحد عندما يتم إدخال البيانات في الخلية حيث توجد الدالة حاليًا.

في مثال الصورة أعلاه ، يتم إضافة أرقام المبيعات الجديدة لكل يوم في أسفل القائمة ، مما يؤدي إلى إزاحة الإجمالي إلى أسفل باستمرار خلية واحدة في كل مرة يتم فيها إضافة البيانات الجديدة.

إذا تم استخدام الدالة SUM بمفردها لإجمالي البيانات ، فسيكون من الضروري تعديل نطاق الخلايا المستخدمة كوسيطة للوظيفة في كل مرة يتم فيها إضافة بيانات جديدة.

ومع استخدام الدالتين SUM و OFFSET معًا ، يصبح النطاق الذي يتم تجميعه ديناميكيًا. بمعنى آخر ، يتغير ليتناسب مع خلايا بيانات جديدة. لا تتسبب إضافة خلايا جديدة من البيانات في حدوث مشكلات نظرًا لاستمرار ضبط النطاق عند إضافة كل خلية جديدة.

بناء الجملة والحجج

الرجوع إلى الصورة أعلاه لمتابعة مع هذا البرنامج التعليمي.

في هذه الصيغة ، يتم استخدام الدالة SUM لإجمالي نطاق البيانات المتوفرة كوسيطة لها. نقطة البداية لهذا النطاق ثابتة ويتم تحديدها كمرجع الخلية إلى الرقم الأول الذي سيتم جمعه بواسطة الصيغة.

تكون الدالة OFFSET متداخلة داخل الدالة SUM ويتم استخدامها لإنشاء نقطة نهاية ديناميكية لنطاق البيانات التي تم جمعها بواسطة الصيغة. يتم تحقيق ذلك عن طريق تعيين نقطة النهاية للنطاق على خلية واحدة فوق موقع الصيغة.

صيغة الصيغة:

= SUM (نطاق بداية: OFFSET (مرجع ، صفوف ، Cols))

• بداية المدى: مطلوب نقطة البداية لنطاق الخلايا التي سيتم تجميعها بواسطة الدالة SUM. في مثال الصورة ، هذه هي الخلية B2.
• مرجع: يقع مرجع الخلية المطلوبة المستخدمة لحساب نقطة نهاية النطاق العديد من الصفوف والأعمدة بعيداً. في مثال الصورة ، تكون الوسيطة Reference هي مرجع الخلية للصيغة نفسها لأنك تريد دومًا أن يقوم النطاق بإنهاء خلية واحدة فوق الصيغة.
• الصفوف: عدد الصفوف فوق أو أسفل الوسيطة Reference المستخدمة في حساب الإزاحة مطلوب. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على صفر. إذا كان موقع الإزاحة أعلى مرجع الوسيطة ، القيمة سالبة. إذا كان أدناه ، تكون وسيطة الصفوف موجبة. إذا كان الإزاحة موجودة في نفس الصف ، تكون الوسيطة صفرًا. في هذا المثال ، يبدأ الإزاحة صفًا واحدًا فوق وسيطة Reference ، لذلك تكون قيمة هذه الوسيطة سالبة واحدة (-1).
• العواميد: عدد الأعمدة إلى يسار أو يمين الوسيطة Reference المستخدمة في حساب الإزاحة مطلوب. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على صفر. إذا كان موقع الإزاحة على يسار وسيطة Reference ، فإن هذه القيمة سالبة. إذا إلى اليمين ، فإن حجة Cols إيجابية. في هذا المثال ، تكون البيانات التي يتم تجميعها في نفس العمود كالصيغة ، لذلك تكون قيمة هذه الوسيطة صفرًا.

استخدام صيغة SUM OFFSET إلى إجمالي مبيعات البيانات

يستخدم هذا المثال صيغة SUM OFFSET لإرجاع الإجمالي لأرقام المبيعات اليومية المسرودة في العمود B من ورقة العمل.

في البداية ، تم إدخال الصيغة في الخلية B6 وتم جمع بيانات المبيعات لمدة أربعة أيام.

الخطوة التالية هي نقل صيغة SUM OFFSET لأسفل صف لإفساح المجال لمجموع مبيعات اليوم الخامس. يتم تحقيق ذلك عن طريق إدراج صف جديد 6 ، والذي ينقل الصيغة إلى الصف 7.

نتيجة لهذه الخطوة ، يقوم Excel تلقائيًا بتحديث الوسيطة Reference إلى الخليةB7 ويضيف الخليةB6 إلى النطاق الذي تم جمعه بواسطة الصيغة.

دخول صيغة SUM OFFSET

1. انقر فوق الخلية B6 ، وهي الموقع الذي سيتم عرض نتائج الصيغة في البداية.
2. اضغط علىالصيغ التبويب من قائمة الشريط.
3. أخترالرياضيات و علم المثلثاتمن الشريط لفتح القائمة المنسدلة للوظيفة.
4. انقر فوقمجموع في القائمة لإظهار مربع الحوار الخاص بالوظيفة.
5. في مربع الحوار ، انقر فوقNUMBER1خط.
6. انقر فوق الخلية B2 لإدخال مرجع الخلية هذا في مربع الحوار. هذا الموقع هو نقطة النهاية الثابتة للصيغة.
7. في مربع الحوار ، انقر فوقرقم 2خط.
8. أدخل وظيفة OFFSET التالية: أوفست (B6، -1،0) لتشكيل نقطة النهاية الديناميكية للصيغة.
9. انقر حسنا لإكمال الوظيفة وإغلاق مربع الحوار.

يظهر المجموع في الخلية B7.

إضافة بيانات مبيعات اليوم التالي

لإضافة بيانات مبيعات اليوم التالي:

1. انقر بزر الماوس الأيمن على رأس الصف للصف 6 لفتح قائمة السياق.
2. في القائمة ، انقر فوقإدراج لإدراج صف جديد في ورقة العمل.
3. وكنتيجة لذلك ، ينتقل الصيغة SUM OFFSET إلى الخلية B7 وأصبح الصف 6 فارغة الآن.
4. انقر فوق الخلية A6.
5. أدخل الرقم5 للإشارة إلى أنه يتم إدخال إجمالي المبيعات لليوم الخامس.
6. انقر فوق الخلية B6.
7. اكتب الرقم$1458.25 واضغط علىأدخل المفتاح على لوحة المفاتيح.

تحديثات الخلية B7 إلى المجموع الجديد لل$7137.40.

عند النقر فوق الخلية B7 ، الصيغة المحدثة= SUM (B2: الإزاحة (B7، -1،0)) يظهر في شريط الصيغة.

تحتوي الدالة OFFSET على وسيطين اختياريين: Height and Width , التي لم تستخدم في هذا المثال.

يمكن استخدام هذه الوسيطات لإخبار الدالة OFFSET شكل المخرجات من حيث عدد الصفوف المرتفعة والعديد من الأعمدة واسعة.

بإهمال هذه الوسيطات ، تستخدم الدالة ، افتراضياً ، ارتفاع وعرض وسيطة المرجع بدلاً من ذلك ، والتي ، في هذا المثال ، يوجد صف واحد مرتفع وعمود واحد.