اكسل

وظيفة Excel OFFSET

Excel Offset Function

وظيفة Excel OFFSETملخص

ترجع الدالة Excel OFFSET مرجعًا إلى نطاق مكون من خمسة مدخلات: (1) نقطة بداية ، (2) إزاحة صف ، (3) إزاحة عمود ، (4) ارتفاع في الصفوف ، (5) عرض في الأعمدة. OFFSET مفيد في الصيغ التي تتطلب نطاقًا ديناميكيًا.



الغرض إنشاء إزاحة مرجعية من نقطة بداية معينة قيمة الإرجاع مرجع خلية. بناء الجملة = OFFSET (مرجع ، صفوف ، عمود ، [ارتفاع] ، [عرض]) وسيطات
  • المرجعي - نقطة البداية ، التي يتم توفيرها كمرجع خلية أو نطاق.
  • صفوف - عدد الصفوف المطلوب تعويضها أسفل مرجع البداية.
  • العواميد - عدد الأعمدة المطلوب إزاحتها على يمين مرجع البداية.
  • ارتفاع - [اختياري] الارتفاع في صفوف المرجع الذي تم إرجاعه.
  • العرض - [اختياري] العرض في أعمدة المرجع الذي تم إرجاعه.
إصدار برنامج Excel 2003 ملاحظات الاستخدام

ترجع الدالة Excel OFFSET نطاقًا ديناميكيًا مكونًا من خمسة مدخلات: (1) نقطة بداية ، (2) إزاحة صف ، (3) إزاحة عمود ، (4) ارتفاع في الصفوف ، (5) عرض في الأعمدة.

نقطة البداية ( المرجعي الوسيطة) يمكن أن تكون خلية واحدة أو نطاق من الخلايا. ال صفوف و العواميد الوسيطات هي عدد الخلايا المطلوب 'إزاحتها' من نقطة البداية. ال ارتفاع و العرض الوسائط اختيارية وتحدد حجم النطاق الذي تم إنشاؤه. متي ارتفاع و العرض تم حذفها ، فهي الافتراضية لارتفاع وعرض المرجعي .





Excel دالة عدد الخلايا مع النص

على سبيل المثال ، للإشارة إلى 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' بقيمة سالبة لوسائط الارتفاع أو العرض.

كيفية حذف الصفوف غير المستخدمة في Excel

أمثلة

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

مثال 1

في الشاشة أدناه ، نستخدم OFFSET لإرجاع القيمة الثالثة (مارس) في العمود الثاني (الغرب). الصيغة في H4 هي:

 
= OFFSET (B3,3,2) // returns D6

مثال دالة OFFSET 1

المثال رقم 2

في الشاشة أدناه ، نستخدم OFFSET لإرجاع آخر قيمة (يونيو) في العمود الثالث (الشمال). الصيغة في H4 هي:

 
= OFFSET (B3,6,3) // returns E9

مثال دالة OFFSET 2

المثال رقم 3

أدناه ، نستخدم OFFSET لإرجاع جميع القيم في العمود الثالث (الشمال). الصيغة في H4 هي:

 
= OFFSET (B3,1,3,6) // returns E4:E9

مثال على دالة OFFSET 3

المثال رقم 4

أدناه ، نستخدم OFFSET لإرجاع جميع القيم لشهر مايو (الصف الخامس). الصيغة في H4 هي:

 
= OFFSET (B3,5,1,1,4) // returns C8:F8

مثال دالة OFFSET 4

المثال الخامس

أدناه ، نستخدم OFFSET لإرجاع قيمة أبريل ومايو ويونيو للمنطقة الغربية. الصيغة في H4 هي:

 
= OFFSET (B3,4,2,3,1) // returns D7:D9

مثال دالة OFFSET 5

المثال رقم 6

أدناه ، نستخدم OFFSET لإرجاع قيم أبريل ومايو ويونيو للغرب والشمال. الصيغة في H4 هي:

كيفية الإشارة إلى خلية في ورقة عمل أخرى
 
= OFFSET (B3,4,2,3,2) // returns D7:E9

مثال دالة OFFSET 6

ملحوظات

  • تقوم OFFSET بإرجاع مرجع فقط ، ولا يتم نقل أي خلايا.
  • على حد سواء صفوف و العواميد يمكن توفيرها كأرقام سالبة لعكس اتجاه الإزاحة الطبيعي - سلبي العواميد تعويض إلى اليسار ، وسالب صفوف تعويض أعلاه.
  • OFFSET هو ' وظيفة متقلبة - ستتم إعادة الحساب مع كل تغيير في ورقة العمل. يمكن للدالات المتغيرة أن تجعل المصنفات الأكبر والأكثر تعقيدًا تعمل ببطء.
  • سيعرض OFFSET # REF! قيمة الخطأ إذا كانت الإزاحة خارج حافة ورقة العمل.
  • عندما يتم حذف الارتفاع أو العرض ، فإن ارتفاع وعرض المرجعي يستخدم.
  • يمكن استخدام OFFSET مع أي دالة أخرى تتوقع تلقي مرجع.
  • وثائق Excel تقول ارتفاع و العرض لا يمكن أن تكون سالبة ، لكن القيم السالبة تعمل.


^