دالة Excel OFFSET

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

بناء الجملة

=OFFSET ()reference, rows, cols, [height], [width])

المعاملات

قيمة العودة

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

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

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

أمثلة

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

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

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

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

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

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

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

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

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

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

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

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 صفوف (دون أي إزاحة أفقية) للوصول إلى المرجع C7:E7؛
3. بعد ذلك، تأخذ دالة SUM الشكل التالي: SUM(C7:E7)، وتعيد في النهاية مجموع القيم الموجودة في النطاق C7:E7.

مزيد من الأمثلة

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

كيف تُنشئ نطاقًا مسمّى ديناميكيًا في Excel؟

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