كيفية استخدام دالة OFFSET في Excel
دالة OFFSET في Excel هي واحدة من الأدوات القوية التي تمكنك من تحديد نطاقات خلايا ديناميكية بمرونة. تُستخدم هذه الدالة لتحديد موقع خلية أو نطاق من الخلايا بشكل مرن، حيث تعتمد على نقطة مرجعية تتحرك وفقًا لقيم محددة. إذا كنت ترغب في إنشاء جداول وتقارير تعتمد على بيانات قابلة للتحديث التلقائي، فإن دالة OFFSET توفر لك إمكانية التوسع والمرونة في التعامل مع البيانات دون الحاجة إلى تعديل النطاقات يدويًا. في هذا الدليل، سنوضح كيفية استخدام دالة OFFSET بطرق متنوعة لتحقيق أقصى استفادة منها في تنظيم البيانات وتحليلها.
تتيح لك دالة OFFSET في برنامج Excel إنشاء مرجع يتم إزاحته حرفيًا عن نقطة البداية. وتسمح للمراجع بالتكيف ديناميكيًا مع التغييرات في بيانات وبنية جدول البيانات الخاص بك.
بنية OFFSET
قبل أن نلقي نظرة على كيفية عملها عمليًا، دعنا نستعرض البنية. تحتوي دالة OFFSET على خمس وسيطات:
=OFFSET(A,B,C,D,E)
حيث
A هو مرجع الخلية لنقطة البداية،
B هو عدد الصفوف التي يجب نقلها لأسفل من النقطة A،
C هو عدد الأعمدة التي يجب نقلها لليمين من النقطة A،
D (اختياري) هو عدد الصفوف (الارتفاع) المعروضة في النتيجة، و
E (اختياري) هو عدد الأعمدة (العرض) المعروضة في النتيجة.
يمكن أن تكون القيم B وC وD وE إما مراجع رقمية أو مراجع خلوية.
ملاحظة
استخدام قيمة سالبة في B من شأنه أن ينشئ إزاحة لأعلى، وقيمة سالبة في C من شأنها أن تنشئ إزاحة لليسار.
مثال أساسي على OFFSET
لتوضيح كيفية عمل OFFSET، سأستخدم مثالاً أساسيًا للغاية وغير عملي. الكتابة
=OFFSET(A1,2,3,2,3)
في الخلية A6، سيتم استخدام الخلية A1 كنقطة بداية (A)، والانتقال إلى أسفل صفين (B)، والانتقال عبر 3 أعمدة (C)، وإرجاع سلسلة من القيم بارتفاع خليتين (D) وعرض 3 خلايا (E):
إذا لم أكتب القيمتين D وE في صيغتي، فإن النتيجة كانت ستعود افتراضيًا إلى الخلية D3 (الغزال) وحدها. وذلك لأن Excel يفترض أن النتيجة بنفس حجم المرجع إذا لم تحدد الارتفاع والعرض.
بناءً على هذا المبدأ، يمكنك أيضًا تحديد حجم النتيجة داخل القيمة A. على سبيل المثال، كتابة
=OFFSET(A1:A3,2,3)
سيعيد نتيجة يبلغ ارتفاعها ثلاثة خلايا، حيث أن هذا هو حجم نقطة البداية A.
استخدام OFFSET في العالم الحقيقي
الآن، سأوضح لك كيف يمكن استخدام OFFSET في أمثلة من العالم الحقيقي.
مثال من العالم الحقيقي 1
هنا، لدي عشرة موظفين، وعدد الوحدات التي باعوها على مدار ثلاثة أسابيع. سأستخدم OFFSET لإنشاء ثلاثة تقارير بيانات.
في تقرير البيانات الأول (الأرجواني)، أريد أن يخبرني Excel بعدد الوحدات التي باعها الموظف عندما أدخل معرفه ورقم الأسبوع. لتحقيق ذلك، في الخلية H4، سأكتب
=OFFSET(B1,H2,H3)
حيث B1 هي نقطة البداية، ويخبر H2 Excel بعدد الصفوف التي يجب إزاحتها لأسفل من نقطة البداية (بعبارة أخرى، معرف الموظف)، ويخبر H3 Excel بعدد الأعمدة التي يجب إزاحتها من نقطة البداية (بعبارة أخرى، رقم الأسبوع). لاحظ كيف استخدمت مراجع الخلايا هنا، لأن هذا يعني أن البيانات التي تسحبها دالة OFFSET من الجدول الخاص بي يمكن أن تكون ديناميكية.
ملاحظة
أبدأ بالخلية B1 كأول إدخال لأنني أريد أن يكون الأسبوع الأول هو الخلية الأولى على يمين نقطة البداية هذه. لذا، في أي وقت تستخدم فيه دالة OFFSET، فكر مليًا في مكان نقطة البداية الخاصة بك.
بعد كتابة الصيغة أعلاه في الخلية H4، سأكتب 6 في مربع المعرف (الخلية H2)، و2 في مربع الأسبوع (الخلية H3). سيخبرني هذا بعدد الوحدات التي باعها Ollie في الأسبوع 2، لأن دالة OFFSET تسحب البيانات من الخلية ستة صفوف لأسفل وعمودين عبر نقطة البداية الخاصة بي، الخلية B1.
أستطيع الآن تغيير القيم في الخلايا H2 وH3 في أي وقت لسحب أي رقم فردي من الجدول.
بعد ذلك، أريد إنشاء تقرير موظف في الجدول الأزرق. عندما أدخل معرف موظف، سيخبرني Excel تلقائيًا باسم الموظف (باستخدام دالة VLOOKUP)، والعدد الإجمالي لأسابيع البيانات التي لدينا (باستخدام دالة COUNTIF)، والإجمالي والمتوسط للوحدات التي باعها هذا الموظف (باستخدام SUM وAVERAGE مع OFFSET).
أولاً، سأكتب معرف موظف في الخلية H7 للبدء. دعنا ننتقل إلى معرف الموظف 3، وهو Jason. بعد ذلك، سأستخدم دالة VLOOKUP لإرجاع اسم Jason في الخلية H8:
=VLOOKUP(H7,A2:B11,2)
الآن، سأستخدم الدالة COUNTIF مع حرف بدل لإخبار Excel بحساب عدد الخلايا في الصف الأول التي تحتوي على كلمة “أسبوع” متبوعة برقم:
=COUNTIF(1:1,"Week*")
يمكنني الآن استخدام هذه المعلومات لحساب إجمالي ومتوسط وحدات جيسون المباعة. أولاً، في الخلية H10، سأكتب
=SUM(OFFSET(B1,H7,1,1,H9))
حيث B1 هي نقطة البداية، وH7 هو عدد الصفوف المراد إزاحتها للأسفل (معرف الموظف)، والرقم 1 الأول هو عدد الأعمدة المراد إزاحتها لليمين، والرقم 1 الثاني هو ارتفاع النتيجة، وH9 هو عرض النتيجة (إجمالي عدد الأسابيع). ومع ذلك، نظرًا لأن OFFSET مضمنة داخل دالة SUM، فإن النتيجة ستكون خلية واحدة فقط في الحجم.
يمكنني نسخ ولصق نفس الصيغة في الخلية H11، ولكن تغيير SUM إلى AVERAGE، حيث أنني أستخدم نفس المراجع.
=AVERAGE(OFFSET(B1,H7,1,1,H9))
أستطيع الآن تغيير القيمة في الخلية H7 لإنشاء تقرير لأي موظف آخر في جدول البيانات الخاص بي. علاوة على ذلك، إذا أضفت أسبوعًا آخر إلى بياناتي، فستتغير الخلية H9 تلقائيًا إلى 4، وستتم أيضًا تحديث الحسابات اللاحقة في الخليتين H10 وH11.
أخيرًا، أريد إنشاء تقرير أسبوعي في الجدول البرتقالي. سيتطلب هذا مني إدخال رقم الأسبوع، ثم يقوم Excel بإنشاء إجمالي ومتوسط الوحدات المباعة لهذا الأسبوع. سأبدأ بكتابة 1 في الخلية H14، حتى ينتج Excel بيانات من الأسبوع 1. ثم في الخلية H15، سأكتب
=SUM(OFFSET(B1,1,H14,10,1))
حيث B1 هي نقطة البداية، والرقم 1 الأول هو عدد الصفوف المراد إزاحتها إلى الأسفل، والرقم H14 هو عدد الأعمدة المراد إزاحتها إلى اليمين (رقم الأسبوع)، والرقم 10 هو ارتفاع النتيجة، والرقم 1 الثاني هو عرض النتيجة. ومع ذلك، نظرًا لأن OFFSET مضمنة داخل دالة SUM، فإن النتيجة ستكون خلية واحدة فقط في الحجم.
وأخيرًا، أستطيع نسخ هذه الصيغة ولصقها في الخلية H15، وتغيير SUM إلى AVERAGE.
=AVERAGE(OFFSET(B1,1,H14,10,1))
مثال 2 من العالم الحقيقي
في هذا المثال، سأوضح لك كيفية استخدام OFFSET مع جدول منسق، وكيف يبدو الأمر عند استخدامه لإرجاع نتيجة في أكثر من خلية.
هنا، لدي المبيعات والأرباح الحالية حتى الآن هذا العام في جدول منسق، وأريد أن يعرض جدول التقرير الخاص بي على اليمين إجمالي أرباح الأشهر الثلاثة الماضية بشكل فردي، بالإضافة إلى متوسط الأرباح والإجمالي لنفس الفترة.
أولاً، أحتاج إلى برنامج Excel لمعرفة عدد الأشهر الكاملة التي انقضت حتى الآن، حتى أتمكن عند استخدام OFFSET من استخدام هذا الرقم لإخبار برنامج Excel بالمسافة التي أحتاجها من نقطة البداية للإزاحة. للقيام بذلك، في الخلية F1، سأكتب
=MONTH(TODAY())
الذي سيعرض رقم الشهر الحالي. في هذه الحالة، هو شهر نوفمبر، الشهر 11.
يمكننا الآن استخدام رقم الشهر هذا لعرض الأرباح من الأشهر الثلاثة الماضية. في الخلية F3، سأكتب
=OFFSET(
وانقر على الخلية A1، نقطة البداية. سيؤدي هذا إلى إضافة رأس عمود الشهر الخاص بالجدول المنسق تلقائيًا إلى الصيغة:
=OFFSET(Table1[[#Headers],[Month]]
الآن، سأضيف فاصلة، وأواصل صيغة الإزاحة. المرحلة التالية هي إخبار Excel بعدد الصفوف التي يجب إزاحتها للأسفل. سيكون هذا هو الشهر الحالي (الخلية F1) ناقص ثلاثة:
=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),
وأخيرًا، سأخبر Excel أننا بحاجة إلى إزاحة عمودين إلى اليمين، وستكون النتيجة ثلاثة صفوف في الارتفاع وعمود واحد في العرض.
=OFFSET(Table1[[#Headers],[Month]],SUM(F1-3),2,3,1)
لاحظ كيف تحتوي الخلايا F4 وF5 على بيانات، على الرغم من أن الصيغة موجودة في F3 فقط. يُعرف هذا باسم المصفوفة المنسكبة، لأن البيانات انسكبت على الخلية النشطة.
الآن، عندما يتغير الشهر إلى 12 (ديسمبر)، سينقل Excel هذه النتيجة تلقائيًا إلى أسفل خلية واحدة، لأن القيمة في الخلية F1 ستزداد بمقدار خلية واحدة.
يمكنني بعد ذلك إكمال الجدول الخاص بي عن طريق كتابة
=AVERAGE(F3#)
في الخلية F7، و
=SUM(F3#)
في الخلية F8.
ملحوظة
يعتبر رمز Octothorp (#) هو طريقة Excel لحساب المصفوفة المنسكبة.
الأشياء التي يجب تذكرها
هناك شيئان مهمان يجب تذكرهما عند استخدام OFFSET:
- OFFSET هي دالة متقلبة، مما يعني أنها تبحث دائمًا عن التحديث. وقد يتسبب هذا في حدوث مشكلات في الأداء إذا تم استخدامها في جدول بيانات كبير بشكل خاص أو على جهاز كمبيوتر ذي ذاكرة منخفضة.
- تُستخدم جميع الأمثلة أعلاه لتوضيح الطرق المختلفة التي يمكنك من خلالها استخدام دالة OFFSET. قد تكون هناك طرق أخرى أكثر كفاءة لإنشاء نفس النتائج، مثل استخدام الدالة INDIRECT. ومع ذلك، بمجرد فهمك لكيفية عمل الدالتين، يمكنك تحديد الطريقة الأفضل للاستخدام في حالتك!
شخصيًا، أستخدم دالة OFFSET لتسجيل وتحليل بيانات فريق كرة القدم المفضل لدي. بعد كل شيء، لا يقتصر برنامج Excel على المحاسبين – يمكنك استخدامه في المنزل جنبًا إلى جنب مع هواياتك!
إتقان دالة OFFSET في Excel يفتح لك فرصًا واسعة لتنظيم بياناتك بطريقة مرنة وديناميكية. يمكنك بفضلها التعامل مع مجموعات كبيرة من البيانات المتغيرة دون الحاجة إلى تحديث نطاقات الخلايا يدويًا، مما يسهل عليك تحليل المعلومات بدقة وسرعة. جرب هذه الدالة في مشاريعك المستقبلية لتطوير تقاريرك وجداولك وجعلها أكثر كفاءة.