قد لا تعرف كيفية استخدام وظيفة OFFSET في Excel

هل تعلم أن وظيفة OFFSET في Microsoft Excel يمكن أن تساعدك في القيام بالعديد من الأشياء الأخرى، وليس فقط مراجع الخلايا؟ فيما يلي طرق فريدة لاستخدام OFFSET في Excel والتي قد لا تعرفها.

تعتبر وظيفة OFFSET في Microsoft Excel أداة مرنة وقوية. نظرًا لتعقيدها وعدد الوظائف المماثلة، لسوء الحظ، فإن وظيفة Excel هذه غير مستغلة بشكل كافٍ.

يمكن لهذه الوظيفة البسيطة نسبيًا أن تساعد المستخدمين على توفير الوقت والجهد. إنه يبسط الحصول على نقاط بيانات دقيقة من مجموعة بيانات رئيسية مرتبة جيدًا. كما يسمح أيضًا بإنشاء تقارير مفصلة ومرئيات تفاعلية ولوحات معلومات معقدة بشكل بسيط.

إنشاء مجموعة بيانات ذات حجم مخصص

أحد الاستخدامات الأكثر شيوعًا لوظيفة OFFSET في Excel هو جمع مجموعات البيانات التي يمكن تغيير حجمها. مع مجموعة كبيرة من البيانات، قد يرغب المرء في النظر فقط في جزء صغير من البيانات في أي وقت محدد.

على سبيل المثال، لتقديم تقرير تلقائي بإجماليات مبيعات الموظفين، يتعين عليك الحصول على الإجمالي لأسبوع أو أسابيع معينة.

يمكن أن تكون كتابة دالة SUM مختلفة لكل إجمالي أمرًا سهلاً للغاية. ومع ذلك، فإن استخدام وظيفة OFFSET سيكون أسهل في الاستخدام وأسرع في إعادة الاستخدام.

أولاً، أدخل التاريخ الذي تريد البدء به في الخلية B6. ثم أدخل عدد الأيام التي تريد تشغيل التقرير فيها في B7. بعد ذلك، استخدم الدالتين SUM وAVERAGE البسيطتين للحصول على المزيد من البيانات لكل موظف خلال الفترة المحددة:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

يعد ضبط تاريخ البدء أو الوقت أمرًا بسيطًا مثل تحرير الخلية، ولكن يمكنك سحب مجموعات البيانات وتغيير حجمها بسهولة أكبر.

يسمح بإنشاء تقارير مخصصة

استخدم نفس الطريقة المذكورة أعلاه لإنشاء تقرير كامل عن طريق تخصيص المتغيرات بسهولة تامة. يمكنك جعل الوسيطة الأولى في الدالة OFFSET تشير إلى ورقة أخرى. وهذا يسمح لك بفصل إدخال البيانات وإعداد التقارير.

يتيح الإجراء أعلاه أيضًا إمكانية سحب أجزاء محددة من البيانات من ورقة عمل إلى أخرى. بالإضافة إلى ذلك، فهو يسمح لك بعرض المعلومات ذات الصلة بالتقرير الحالي فقط. يعد إعداد تقرير يعرض الإجماليات اليومية باستخدام نفس البيانات من قبل أمرًا بسيطًا للغاية. يمكنك القيام بذلك دون استرداد البيانات لتواريخ غير ذات صلة.

ستقوم وظيفة الإزاحة التالية الموضوعة في الخلية B5 باسترداد عدد الأيام المحددة في B2. يبدأ في الأسبوع المحدد في B1 ويعرض فقط بيانات الفترة ذات الصلة:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

من الممكن تقييد الإدخالات في B1 وB2 وإنشاء قائمة منسدلة في Excel بالأسابيع المتاحة باستخدام التحقق من صحة البيانات.

ترقيم صفحات جدول البيانات

يمكن لوظيفة OFFSET أيضًا أن تجعل عملية ترقيم الصفحات البسيطة في جدول البيانات ممكنة، وهي مهمة غالبًا ما ترتبط باستخدام علامة التبويب Excel Developer. وهذا يمكن أن يجعل معالجة مجموعات كبيرة من البيانات أسهل وأكثر فعالية.

عند العمل باستخدام جداول بيانات تحتوي على ما يصل إلى 30000 صف من البيانات، قد يكون من الصعب البحث في جميع البيانات مرة واحدة. قم بإنشاء قائمة منسدلة تقبل نطاقًا رقميًا من 1 إلى 300، مما يسمح لك بفصل البيانات بسرعة حسب الصفحة مع 100 نتيجة لكل صفحة.

يتم دائمًا وضع نقطة الربط الخاصة بوظيفة OFFSET في خلية البيانات العليا. ومع ذلك، الوسيطة الثانية هي الإزاحة العمودية. هذه القيمة تساوي رقم الصفحة المحدد ناقص 100. وتكون الإزاحة أفقية، ويمكن تعيين الوسيطة الثالثة على 0.

تم تعيين الوسيطة الرابعة على 100، والوسيطة الخامسة هي عدد الأعمدة في مجموعة البيانات. تجعل هذه الطريقة تقسيم جداول البيانات وإدارتها أمرًا سهلاً للغاية.

فيما يلي مثال للبيانات المرقّمة. يتضمن 500 قطعة من المعلومات المسجلة من جهاز استشعار في ورقة واحدة وتقرير مرقّم على الورقة الأخرى.

يستخدم التقرير التحقق من صحة البيانات في الخلية B1 لإنشاء قائمة منسدلة تحتوي على صفحات بداخلها. يقوم التعبير التالي في الخلية A3 بإنشاء نص التقرير:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

فيما يلي بعض الطرق لاستخدام وظيفة الإزاحة في Excel والتي لا يعرفها إلا القليل من الأشخاص ولكنها مفيدة حقًا، خاصة عندما تحتاج إلى معالجة كميات كبيرة من البيانات.

نأمل أن تكون المقالة مفيدة لك.