استخدم SUM و OFFSET للعثور على الإجماليات للنطاقات الديناميكية للبيانات
إذا كانت ورقة عمل Excel الخاصة بك تتضمن حسابات تستند إلى نطاق متغير من الخلايا ، فإن استخدام الدالتين SUM و OFFSET معًا في صيغة SUM OFFSET يبسّط مهمة الحفاظ على الحسابات محدثة.
إنشاء نطاق ديناميكي مع وظائف SUM و OFFSET
إذا كنت تستخدم الحسابات لفترة زمنية تتغير باستمرار - مثل إجمالي المبيعات للشهر - فإن وظيفة OFFSET تسمح لك بإعداد نطاق ديناميكي يتغير مع إضافة أرقام مبيعات كل يوم.
في حد ذاته ، يمكن أن تستوعب الدالة SUM عادة خلايا جديدة من البيانات يتم إدخالها في النطاق الذي يتم جمعه.
يحدث استثناء واحد عندما يتم إدخال البيانات في الخلية حيث توجد الدالة حاليًا.
في مثال الصورة التي ترافق هذه المقالة ، يتم إضافة أرقام المبيعات الجديدة لكل يوم إلى أسفل القائمة ، مما يفرض على الإجمالي أن يتحول باستمرار إلى خلية واحدة في كل مرة تتم فيها إضافة البيانات الجديدة.
إذا تم استخدام الدالة SUM بمفردها لإجمالي البيانات ، فسيكون من الضروري تعديل نطاق الخلايا المستخدمة كوسيطة للوظيفة في كل مرة يتم فيها إضافة بيانات جديدة.
ومع استخدام الدالتين SUM و OFFSET معًا ، يصبح النطاق الذي يتم تجميعه ديناميكيًا. بمعنى آخر ، يتغير ليتناسب مع خلايا بيانات جديدة. لا تتسبب إضافة خلايا جديدة من البيانات في حدوث مشكلات نظرًا لاستمرار ضبط النطاق عند إضافة كل خلية جديدة.
بناء الجملة والحجج
الرجوع إلى الصورة التي ترافق هذه المقالة لمتابعة مع هذا البرنامج التعليمي.
في هذه الصيغة ، يتم استخدام الدالة SUM لإجمالي نطاق البيانات المتوفرة كوسيطة لها. نقطة البداية لهذا النطاق ثابتة ويتم تحديدها كمرجع الخلية إلى الرقم الأول الذي سيتم جمعه بواسطة الصيغة.
تكون الدالة OFFSET متداخلة داخل الدالة SUM ويتم استخدامها لإنشاء نقطة نهاية ديناميكية لنطاق البيانات التي تم جمعها بواسطة الصيغة. يتم تحقيق ذلك عن طريق تعيين نقطة النهاية للنطاق على خلية واحدة فوق موقع الصيغة.
صيغة الصيغة:
= SUM (نطاق بداية: OFFSET (مرجع ، صفوف ، Cols))نطاق بداية - (مطلوب) نقطة البداية لنطاق الخلايا التي سيتم جمعها بواسطة الدالة SUM. في مثال الصورة ، هذه هي الخلية B2.
مرجع - (مطلوب) مرجع الخلية المستخدمة لحساب نقطة نهاية النطاق يقع العديد من الصفوف والأعمدة بعيدا. في مثال الصورة ، تعد الوسيطة Reference هي مرجع الخلية للصيغة نفسها حيث أننا نرغب دائمًا في أن يقوم النطاق بإنهاء خلية واحدة فوق الصيغة.
الصفوف - (مطلوب) عدد الصفوف فوق أو أسفل الوسيطة Reference المستخدمة في حساب الإزاحة. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على صفر.
إذا كان موقع الإزاحة أعلى من وسيطة Reference ، فإن هذه القيمة سالبة. إذا كان أدناه ، تكون وسيطة الصفوف موجبة. إذا كان الإزاحة موجودة في نفس الصف ، فإن هذه الوسيطة هي صفر. في هذا المثال ، يبدأ الإزاحة صفًا واحدًا فوق وسيطة Reference ، لذلك تكون قيمة هذه الوسيطة سالبة واحدة (-1).
Cols - (مطلوب) عدد الأعمدة إلى يمين أو يمين الوسيطة Reference المستخدمة في حساب الإزاحة. يمكن أن تكون هذه القيمة موجبة أو سالبة أو مضبوطة على صفر
إذا كان موقع الإزاحة على يسار وسيطة Reference ، فإن هذه القيمة سالبة. إذا إلى اليمين ، فإن حجة Cols إيجابية. في هذا المثال ، تكون البيانات التي يتم تجميعها في نفس العمود مثل الصيغة بحيث تكون قيمة هذه الوسيطة صفرًا.
استخدام صيغة SUM OFFSET إلى إجمالي مبيعات البيانات
يستخدم هذا المثال صيغة SUM OFFSET لإرجاع الإجمالي لأرقام المبيعات اليومية المسرودة في العمود B من ورقة العمل.
في البداية ، تم إدخال الصيغة في الخلية B6 وتم جمع بيانات المبيعات لمدة أربعة أيام.
الخطوة التالية هي نقل صيغة SUM OFFSET لأسفل صف لإفساح المجال لمجموع مبيعات اليوم الخامس.
يتم تحقيق ذلك عن طريق إدراج صف جديد 6 ، والذي ينقل الصيغة إلى الصف 7.
وكنتيجة لهذه الخطوة ، يقوم Excel تلقائيًا بتحديث الوسيطة Reference إلى الخلية B7 وإضافة الخلية B6 إلى النطاق الذي تم جمعه بواسطة الصيغة.
دخول صيغة SUM OFFSET
- انقر فوق الخلية B6 ، وهي الموقع الذي سيتم عرض نتائج الصيغة في البداية.
- انقر فوق علامة التبويب صيغ من قائمة الشريط .
- اختر Math & Trig من الشريط لفتح القائمة المنسدلة للوظيفة.
- انقر فوق SUM في القائمة لإظهار مربع الحوار الخاص بالوظيفة.
- في مربع الحوار ، انقر فوق سطر Number1 .
- انقر فوق الخلية B2 لإدخال مرجع الخلية هذا في مربع الحوار. هذا الموقع هو نقطة النهاية الثابتة للصيغة ؛
- في مربع الحوار ، انقر فوق سطر Number2 .
- أدخل الدالة OFFSET التالية: OFFSET (B6 ، -1،0) لتشكيل نقطة النهاية الديناميكية للصيغة.
- انقر فوق " موافق" لإكمال الدالة وإغلاق مربع الحوار.
المجموع 5679.15 دولار يظهر في الخلية B7.
عند النقر فوق الخلية B3 ، تظهر الدالة الكاملة = SUM (B2: OFFSET (B6، -1،0)) في شريط الصيغة أعلى ورقة العمل.
إضافة بيانات مبيعات اليوم التالي
لإضافة بيانات مبيعات اليوم التالي:
- انقر بزر الماوس الأيمن على رأس الصف للصف 6 لفتح قائمة السياق.
- في القائمة ، انقر فوق " إدراج" لإدراج صف جديد في ورقة العمل.
- وكنتيجة لذلك ، ينتقل الصيغة SUM OFFSET إلى الخلية B7 وأصبح الصف 6 فارغة الآن.
- انقر فوق الخلية A6 .
- أدخل الرقم 5 للإشارة إلى أنه يتم إدخال إجمالي المبيعات لليوم الخامس.
- انقر فوق الخلية B6.
- اكتب الرقم $ 1458.25 واضغط على مفتاح Enter على لوحة المفاتيح.
تحديثات الخلية B7 إلى الإجمالي الجديد من 7137.40 دولار.
عند النقر فوق الخلية B7 ، تظهر الصيغة المحدثة = SUM (B2: OFFSET (B7، -1،0)) في شريط الصيغة.
ملاحظة : تحتوي الدالة OFFSET على وسيطين اختياريين: Height and Width ، تم حذفهما في هذا المثال.
يمكن استخدام هذه الوسيطات لإخبار دالة OFFSET عن شكل المخرجات من حيث كونه عدد الصفوف مرتفعًا جدًا والعديد من الأعمدة واسعة.
بإهمال هذه الوسيطات ، تستخدم الدالة ، افتراضياً ، ارتفاع وعرض وسيطة المرجع بدلاً من ذلك ، والتي ، في هذا المثال ، يوجد صف واحد مرتفع وعمود واحد.