كيفية إنشاء جداول ديناميكية في Excel (باستخدام وظيفة TABLE & OFFSET)

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

الجداول الديناميكية في Excel

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

الآن السؤال الذي يطرح نفسه هو لماذا نحتاج إلى النطاق الديناميكي أو الجداول الديناميكية. الجواب على ذلك هو أنه كلما تم تحديث أو تعديل قائمة أو نطاق بيانات ، لا يتأكد من تغيير التقرير وفقًا لتغيير البيانات.

في الأساس ، هناك ميزتان رئيسيتان للجداول الديناميكية:

  1. النطاق الديناميكي سوف يتوسع أو يتقلص تلقائيًا وفقًا لتغيير البيانات.
  2. يمكن تحديث الجداول المحورية المستندة إلى الجدول الديناميكي في Excel تلقائيًا عند تحديث المحور.

كيفية إنشاء جداول ديناميكية في Excel؟

هناك طريقتان أساسيتان لاستخدام الجداول الديناميكية في Excel - 1) استخدام الجداول و 2) استخدام دالة OFFSET.

يمكنك تنزيل قالب Excel للجدول الديناميكي من هنا - قالب Excel للجدول الديناميكي

# 1 - استخدام الجداول لإنشاء جداول ديناميكية في Excel

باستخدام الجداول ، يمكننا بناء جدول ديناميكي في التفوق ووضع محور على الجدول الديناميكي.

مثال

لدينا البيانات التالية ،

إذا قمنا بإنشاء جدول محوري باستخدام نطاق البيانات العادي هذا من A1: E6 ، إذا قمنا بإدخال بيانات في الصف 7 فلن تنعكس في الجدول المحوري.

لذلك سنقوم أولاً بإنشاء نطاق ديناميكي.

# 1 - حدد البيانات مثل A1: E6.

# 2 -  في الإدراج ، انقر فوق Tab "جداول" أسفل قسم الجداول.

# 3 -  ينبثق مربع حوار.

نظرًا لأن بياناتنا تحتوي على رؤوس ، تذكر أن تقوم بتحديد المربع "يحتوي الجدول الخاص بي على رؤوس" وانقر فوق "موافق".

# 4 -  تم إنشاء النطاق الديناميكي الخاص بنا.

# 5 -  حدد البيانات وفي علامة التبويب إدراج أسفل قسم جداول Excel ، انقر فوق الجداول المحورية.

# 6 -  نظرًا لأننا أنشأنا الجدول ، فإنه يأخذ نطاقًا كجدول 2. انقر فوق "موافق" وفي الجداول المحورية اسحب المنتج في الصفوف والمبيعات في القيم.

# 7 -  الآن في الورقة حيث لدينا جدولنا ، أدخل بيانات أخرى في المركز السابع

في الجدول المحوري ، قم بتحديث الجدول المحوري.

قام الجدول المحوري الديناميكي لدينا بتحديث بيانات المنتج 6 تلقائيًا في الجدول المحوري.

# 2 - استخدام دالة OFFSET لإنشاء جدول ديناميكي في Excel

يمكننا أيضًا استخدام دالة OFFSET لإنشاء جداول ديناميكية في Excel. دعونا نلقي نظرة على أحد الأمثلة.

مثال

لدي قائمة أسعار لمنتجاتي التي أستخدمها في حساباتي ،

حدد البيانات وقم بتسميتها

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

سنستخدم وظيفة الأوفست لجعل نطاق البيانات ديناميكيًا.

# 1 - ضمن علامة تبويب الصيغ في النطاق المحدد ، انقر فوق الاسم المحدد وينبثق مربع حوار.

# 2 - في مربع الاسم اكتب أي اسم ، سأستخدم PriceA. النطاق هو المصنف الحالي وهو يشير حاليًا إلى الخلية الحالية المحددة وهي B2.

في يشير إلى كتابة الصيغة التالية ،

= offset (Sheet2! $ B $ 2،1،0، counta (Sheet2! $ B: $ B) -1،2)

= تعويض (

# 3 - الآن حدد خلية البداية وهي B2 ،

# 4 - نحتاج الآن إلى كتابة 1،0 لأنه سيحسب عدد الصفوف أو الأعمدة التي يجب الانتقال إليها

# 5 - نحتاج الآن إلى حساب أي بيانات موجودة في العمود B واستخدامها كرقم الصفوف ، لذلك استخدم وظيفة COUNTA وحدد العمود B.

# 6 - بما أننا لا نريد أن يتم احتساب الصف الأول وهو رأس المنتج (-) 1 منه.

# 7 - الآن سيكون عدد الأعمدة دائمًا عمودين ، لذا اكتب 2 وانقر فوق موافق.

# 8 - لن يكون نطاق البيانات هذا مرئيًا بشكل افتراضي ، لذا لرؤية هذا ، نحتاج إلى النقر فوق Name Manager ضمن علامة تبويب الصيغة وتحديد المنتج ،

# 9 - إذا نقرنا على يشير إلى أنه يظهر نطاق البيانات ،

# 10 - أضف الآن منتجًا آخر في جدول المنتج 6.

# 11 - الآن انقر فوق Product Table في Name manager ، فإنه يشير أيضًا إلى البيانات الجديدة المدرجة ،

هذه هي الطريقة التي يمكننا بها استخدام وظيفة الأوفست لعمل جداول ديناميكية.

أشياء للذكرى

  1. يتم تحديث الجداول المحورية المستندة إلى النطاق الديناميكي تلقائيًا عند التحديث.
  2. يمكن رؤية استخدام وظيفة الإزاحة في الأسماء المعرفة من مدير الاسم في علامة تبويب الصيغة.