صيغ الدالة OFFSET واستخدامها في برنامج Microsoft Excel الدالة OFFSET هي إحدى دوال برنامج Microsoft Excel، تختص الدالة OFFSET في برنامج Microsoft Excel بإرجاع مرجع إلى نطاق مكون من خمسة مدخلات: (1) نقطة بداية ، (2) إزاحة صف، (3) إزاحة عمود، (4) ارتفاع في الصفوف، (5) عرض في الأعمدة. الدالة OFFSET مفيدة في الصيغ التي تتطلب نطاقًا ديناميكيًا.
الهدف من استخدام الدالة OFFSET
القيام بإنشاء إزاحة مرجعية من نقطة بداية معينة.
بناء جملة الدالة يكون كالتالي:
=OFFSET (reference, rows, cols, [height], [width])
حيث أن:
- الوسيط (reference) هو نقطة البداية، يتم توفيرها كمرجع خلية أو نطاق.
- الوسيط (rows) هو عدد الصفوف المطلوب إزاحتها أسفل مرجع البداية.
- الوسيط (cols) هو عدد الأعمدة المطلوب إزاحتها إلى يمين مرجع البداية.
- الوسيط (height) وسيط اختياري، وهو الارتفاع في صفوف المرجع الذي تم إرجاعه.
- الوسيط (width) وسيط اختياري، وهو العرض في أعمدة المرجع الذي تم إرجاعه.
ملاحظات استخدام الدالة OFFSET
- ترجع الدالة Excel OFFSET نطاقًا ديناميكيًا مكونًا من خمسة مدخلات: (1) نقطة بداية، (2) إزاحة صف، (3) إزاحة عمود، (4) ارتفاع في الصفوف، (5) عرض في الأعمدة.
- يمكن أن تكون نقطة البداية (الوسيط reference) خلية واحدة أو نطاقًا من الخلايا.
- وسيطات الصفوف والعواميد هي عدد الخلايا المطلوب "إزاحتها" من نقطة البداية.
- تعتبر وسيطات الارتفاع والعرض اختيارية وتحدد حجم النطاق الذي تم إنشاؤه. عندما يتم حذف الطول والعرض، يتم تعيينهما افتراضيًا على ارتفاع وعرض المرجع.
على سبيل المثال، للإشارة إلى C5 بدءًا من A1، يكون المرجع A1، والصفوف 4، والعمود 2:
=OFFSET(A1,4,2) // returns reference to C5
- للإشارة إلى C1: C5 من A1، يكون المرجع A1، والصفوف 0، والعمود 2، والارتفاع 5، والعرض 1:
=OFFSET(A1,0,2,5,1) // returns reference to C1:C5
ملاحظة: يمكن حذف العرض؛ لأنه سيكون افتراضيًا على 1.
- من الشائع أن ترى الدالة OFFSET مغلفة في دالة
أخرى تتوقع نطاقًا. على سبيل المثال، إلى الدالة SUM C1: C5 ، بدءًا من A1:
=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5
- الغرض الرئيسي من الدالة OFFSET هو السماح للصيغ بالتكيف ديناميكيًا مع البيانات المتاحة أو لإدخال المستخدم.
- يمكن استخدام الدالة OFFSET لإنشاء نطاق ديناميكي مسمى للمخططات أو الجداول المحورية، لضمان تحديث بيانات المصدر دائمًا.
ملاحظة:
تنص وثائق برنامج Excel على أن الطول والعرض لا يمكن أن يكونا سالبين، ولكن يبدو أن القيم السالبة عملت بشكل جيد منذ أوائل التسعينيات. لن تسمح وظيفة الدالة OFFSET في "جداول بيانات Google" بقيمة سالبة لوسائط الارتفاع أو العرض.
أمثلة:
توضح الأمثلة التالية كيف يمكن تكوين الدالة OFFSET لإرجاع أنواع مختلفة من النطاقات. تم التقاط لقطات الشاشات هذه باستخدام Excel 365، لذا تُرجع الدالة OFFSET صفيفًا ديناميكيًا عندما تكون النتيجة أكثر من خلية واحدة. في الإصدارات القديمة من برنامج Excel، يمكنك استخدام المفتاح F9 للتحقق من النتائج التي تم إرجاعها من الدالة OFFSET.
مثال 1:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
القيمة الثالثة (mars) في العمود الثاني (west). الصيغة في H4 هي:
=OFFSET(B3,3,2) // returns D6
المثال رقم 2:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
آخر قيمة (يونيو) في العمود الثالث (الشمال). الصيغة في H4 هي:
=OFFSET(B3,6,3) // returns E9
المثال رقم 3:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
جميع القيم في العمود الثالث (الشمال). الصيغة في H4 هي:
=OFFSET(B3,1,3,6) // returns E4:E9
المثال رقم 4:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
جميع القيم لشهر مايو (الصف الخامس). الصيغة في H4 هي:
=OFFSET(B3,5,1,1,4) // returns C8:F8
المثال رقم 5:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
قيمة أبريل ومايو ويونيو للمنطقة الغربية. الصيغة في H4 هي:
=OFFSET(B3,4,2,3,1) // returns D7:D9
المثال رقم 6:
في الصورة التالية، نستخدم الدالة OFFSET لإرجاع
قيم أبريل ومايو ويونيو للغرب والشمال. الصيغة في H4 هي:
=OFFSET(B3,4,2,3,2) // returns D7:E9
ملاحظات مهمة:
-
تقوم الدالة OFFSET بإرجاع مرجع فقط، ولا يتم
نقل أي خلايا.
-
يمكن توفير كل من الصفوف والأعمدة كأرقام سالبة
لعكس اتجاه الإزاحة الطبيعي - إزاحة الأعمدة السالبة إلى اليسار، وإزاحة
الصفوف السالبة أعلاه.
-
الدالة OFFSET هي "وظيفة متغيرة" - ستتم إعادة الحساب مع كل تغيير في ورقة العمل. يمكن للدالات المتغيرة أن تجعل المصنفات الأكبر والأكثر تعقيدًا تعمل ببطء.
- سيعرض OFFSET # REF! قيمة الخطأ إذا كانت الإزاحة خارج حافة ورقة العمل.
-
عند حذف الارتفاع أو العرض، يتم استخدام ارتفاع
وعرض المرجع.
-
يمكن استخدام OFFSET مع أي دالة أخرى تتوقع تلقي
مرجع.
-
تشير وثائق Excel إلى أن الطول والعرض لا يمكن
أن يكونا سالبين، لكن القيم السالبة تعمل.
وفي نهاية المقال نتمنى أن نكون قد أفدناكم بما يخص هذه الدالة، ونضرب لكم موعدًا جديدًا مستقبلًا مع المزيد من المقالات عن الدوال في برنامج Microsoft Excel.
أسرة احترف الإكسيل
إرسال تعليق