دالة OFFSET في Excel

تقوم دالة OFFSET في Excel بإرجاع مرجع إلى خلية أو نطاق من الخلايا التي تكون مزاحة عن خلية معينة بعدد معين من الصفوف والأعمدة.

الصيغة

=OFFSET (المرجع, الصفوف, الأعمدة, [الارتفاع], [العرض])

الوسائط

قيمة الإرجاع

ستقوم دالة OFFSET بإرجاع مرجع خلية مزاح من نقطة بداية معينة.

ملاحظات الدالة

1. ستظهر قيمة الخطأ #VALUE! عندما يشير المرجع إلى نطاق من الخلايا غير المتصلة.
2. ستظهر قيمة الخطأ #REF! عندما تتجاوز الصفوف والأعمدة المزاحة حدود ورقة العمل.

أمثلة

المثال الأول: الاستخدام الأساسي لدالة OFFSET

إرجاع مرجع إلى خلية باستخدام الصيغة أدناه:
=OFFSET(B2,3,1)

في هذه الحالة، B2 هي نقطة البداية، الرقم 3 والرقم 1 يعنيان التحرك 3 صفوف لأسفل وعمود واحد لليمين من الخلية B2، وأخيرًا يتم إرجاع القيمة في C5. انظر لقطة الشاشة:

إرجاع مرجع إلى نطاق من الخلايا باستخدام الصيغة أدناه:
=OFFSET(B2,3,1,2,2)

في هذه الحالة، ستحصل على نتائج نطاق 2 × 2 الذي يقع أسفل بثلاث صفوف ويمين عمود واحد من الخلية B2.

ملاحظة: سيحدث خطأ #VALUE! عند تحديد خلية واحدة فقط لتطبيق دالة OFFSET لإرجاع نطاق من الخلايا. تحتاج إلى تحديد نطاق 2 × 2 (مثل 4 خلايا فارغة)، وإدخال الصيغة ثم الضغط على مفاتيح Ctrl + Shift + Enter للحصول على النتائج.

المثال الثاني: استخدام دالة OFFSET لجمع نطاق من القيم

كما ذكرنا أعلاه، إذا حاولت استخدام دالة OFFSET مثل =OFFSET(B2,3,1,2,2) بمفردها في خلية واحدة، فستعيد خطأ #VALUE!. ومع ذلك، إذا قمت بدمج دالتي SUM و OFFSET كما هو موضح في لقطة الشاشة أدناه، فسيتم إرجاع مجموع القيم في النطاق C5:D6 مباشرة.

1. حدد خلية فارغة، انسخ الصيغة أدناه إليها واضغط على Enter للحصول على النتيجة.
=SUM(OFFSET(B2,3,1,2,2)))

المثال الثالث: جمع الأعمدة بناءً على معايير معينة

كما هو موضح في لقطة الشاشة أدناه، كيف يمكن الحصول على إجمالي مبيعات المانجو من الأحد إلى الثلاثاء؟ الرجاء المحاولة كما يلي.

1. حدد خلية فارغة، انسخ الصيغة أدناه إليها واضغط على Enter للحصول على النتيجة.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

ملاحظات:

1. في الصيغة أعلاه، MATCH(G4,B3:B8) تبحث عن المانجو وتُرجع موقعها في النطاق B3:B8. لنرى، المانجو تقع 5 صفوف تحت الخلية B2، لذلك تُرجع الرقم 5؛
2. بما أن نتيجة MATCH هي 5، والفاصلة الأخيرة هنا تمثل 0، فإن دالة OFFSET الآن تظهر كـ OFFSET(C2:E2,5,0)، مما يعني أن الإزاحة تبدأ من النطاق C2:E2، تتحرك 5 صفوف لأسفل و0 عمود لليمين للحصول على المرجع إلى النطاق C7:E7؛
3. ثم تظهر دالة SUM كـ SUM(C7:E7)، وأخيرًا تُرجع مجموع القيم في النطاق C7:E7.

أمثلة أخرى

كيفية جمع كل n صف لأسفل في Excel؟

كيفية إنشاء نطاق اسم ديناميكي في Excel؟

كيفية إرجاع قيمة الخلية كل خمس صفوف أو الصف nth في Excel؟