تعيد =OFFSET(A1,3,2) الخلية التي تبعد 3 صفوف إلى الأسفل وعمودين أفقيًا عن A1، أي C4. وإذا أعطيتها ارتفاعًا وعرضًا أيضًا، تعيد نطاقًا كاملًا، وهذا أكثر ما تُستخدم له OFFSET: المجاميع والمتوسطات على نطاق يتحرك أو ينمو.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
3 صفوف إلى الأسفل وعمودان أفقيًا من A1 توصل إلى C4، سعر Carrot، $0.80. اضبط Cols على 0 للاسم Carrot، أو Rows على 5 لصف Milk. يمكن أن تكون الصفوف والأعمدة سالبة للتحرك إلى الأعلى أو إلى الخلف، والتحرك خارج حافة الورقة أو أعلاها يعطي #REF!.
صيغة دالة OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: خلية البداية (أو النطاق).rowsوcols: مقدار التحرك. 0 يعني البقاء في المكان.heightوwidth: حجم النطاق المراد إرجاعه، بالعد من الخلية التي وصلت إليها. إذا حُذفا، فهما حجمreference.
وحدها في خلية، تمتد OFFSET التي تعيد عدة خلايا في Excel 365؛ وتعرض الإصدارات الأقدم عادة #VALUE!. وداخل SUM أو AVERAGE أو COUNT أو MAX تعمل كنطاق.
جمع آخر N صفوف
المهمة الكلاسيكية لـ OFFSET: مجموع يغطي دائمًا أحدث الصفوف، مهما أُضيف منها. تجد COUNT عدد القيم، وتنزل OFFSET إلى أول قيمة من آخر N قيم، ويأخذ الارتفاع N صفوف.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
هناك 7 قيم، لذا تبدأ OFFSET بعد 7-3+1، أي 5 صفوف تحت B1، عند B6، وتأخذ 3 صفوف: من May إلى Jul، 14,900. اكتب 4900 في B9 (أغسطس) فينتقل المجموع إلى Jun وJul وAug، لأن COUNT تجد الآن 8. ويترك النطاق B2:B13 مكانًا لبقية السنة. يجب ألا تكون في العمود خلايا فارغة بين القيم، وإلا ستعدّ COUNT أقل من اللازم وتقع النافذة في المكان الخطأ.
متوسط متحرك
عند نسخ OFFSET إلى أسفل عمود مع إزاحة صفوف سالبة، تعطي كل صف نافذة من الصفوف التي فوقه: هنا متوسط الشهر الحالي والشهرين اللذين قبله.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
تحسب C4 متوسط B2:B4 (من Jan إلى Mar)، 4,300. وكل صف تحته ينقل النافذة صفًا واحدًا إلى الأسفل. غيّر 3 إلى 6 و-2 إلى -5 لمتوسط ستة أشهر (وابدأ الصيغة عندها في الصف 7). هذه الحالة تحديدًا لا تحتاج إلى OFFSET أصلًا: تفعل =AVERAGE(B2:B4) المنسوخة إلى الأسفل من C4 الشيء نفسه، لأن المراجع النسبية تتحرك من تلقاء نفسها. وتستحق OFFSET مكانها عندما يأتي حجم النافذة من خلية.
لماذا تكون INDEX غالبًا الخيار الأفضل
OFFSET متقلبة: يعيد الاكسل حساب كل OFFSET بعد أي تعديل في أي مكان من المصنف، لأنه لا يستطيع أن يعرف مسبقًا إلى أي خلايا ستشير. والورقة التي فيها الآلاف منها تصبح بطيئة. وتعيد INDEX مرجعًا أيضًا، والنطاق المكتوب بالشكل start:INDEX(...) ينمو بالطريقة نفسها دون أن يكون متقلبًا:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
تقرأ الصيغتان أول E2 صفوف من العمود. وOFFSET أصعب في التدقيق أيضًا: يعرض "تتبع السابقات" والحدود الملونة التي يرسمها الاكسل أثناء تحرير الصيغة خلية البداية والوسائط، لا النطاق الذي تعيده OFFSET في النهاية. استخدم OFFSET لنموذج سريع أو لنطاق مخطط؛ وفضّل INDEX في المصنفات الكبيرة. في صفحة INDEX المزيد عن إرجاع النطاقات، وINDIRECT هي دالة المراجع المتقلبة الأخرى.
تمرين: مجموع أول N أشهر
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
دورك: في F2، استخدم OFFSET داخل SUM لجمع أول N أشهر، حيث N موجودة في E2.
الأسئلة الشائعة
ماذا تفعل OFFSET في الاكسل؟
تعيد مرجعًا يبعد عددًا معينًا من الصفوف والأعمدة عن خلية بداية، مع تغيير حجمه اختياريًا. =OFFSET(A1,3,2) هي الخلية التي تبعد 3 صفوف إلى الأسفل وعمودين إلى اليمين عن A1، أي C4.
كيف أجمع آخر N صفوف في الاكسل؟
ابدأ من العنوان وانزل إلى أول قيمة من آخر N قيم: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). تجد COUNT عدد القيم، ويأخذ الارتفاع N ذلك العدد من الصفوف. ولا يعمل ذلك إلا عندما لا تكون في العمود فجوات.
لماذا OFFSET دالة متقلبة؟
يعيد الاكسل حساب كل OFFSET بعد أي تغيير في المصنف، لأن الخلايا التي تشير إليها لا تُعرف إلا بعد تنفيذها. وفي المصنفات الكبيرة يبطئ ذلك العمل. والنطاق المبني بالدالة INDEX، مثل B2:INDEX(B2:B100,N)، يؤدي المهمة نفسها دون أن يكون متقلبًا.
ما وسائط OFFSET؟
OFFSET(reference, rows, cols, [height], [width]): خلية البداية، وكم صفًا إلى الأسفل (السالب إلى الأعلى)، وكم عمودًا أفقيًا (السالب إلى الخلف)، واختياريًا حجم النطاق المراد إرجاعه.
