نموذج البيانات في Excel | كيف يتم إنشاء نموذج بيانات؟ (مع أمثلة)
ما هو نموذج البيانات في Excel؟
نموذج البيانات في Excel هو نوع من جدول البيانات حيث يكون لدينا جدولين أو أكثر من جدولين في علاقة مع بعضنا البعض من خلال سلسلة بيانات مشتركة أو أكثر ، في جداول نموذج البيانات والبيانات من مختلف الأوراق أو المصادر الأخرى التي تجتمع معًا لتشكيل نموذج فريد الجدول الذي يمكنه الوصول إلى البيانات من جميع الجداول.
خاطئة
- يسمح بدمج البيانات من جداول متعددة عن طريق إنشاء علاقات تستند إلى عمود مشترك.
- تُستخدم نماذج البيانات بشفافية ، وتوفر بيانات مجدولة يمكن استخدامها في Pivot Table في Excel و Pivot Charts في Excel. فهو يدمج الجداول ، مما يتيح إجراء تحليل شامل باستخدام الجداول المحورية و Power Pivot و Power View في Excel.
- يسمح نموذج البيانات بتحميل البيانات في ذاكرة Excel.
- يتم حفظه في الذاكرة حيث لا يمكننا رؤيته مباشرة. ثم يمكن إرشاد Excel لربط البيانات ببعضها البعض باستخدام عمود مشترك. يشير جزء "النموذج" من نموذج البيانات إلى كيفية ارتباط جميع الجداول ببعضها البعض.
- يمكن لنموذج البيانات الوصول إلى جميع المعلومات التي يحتاجها حتى عندما تكون المعلومات في جداول متعددة. بعد إنشاء نموذج البيانات ، تتوفر البيانات في Excel في ذاكرته. من خلال البيانات الموجودة في ذاكرتها ، يمكن الوصول إلى البيانات بعدة طرق.
أمثلة
يمكنك تنزيل نموذج Excel لنموذج البيانات هذا من هنا - نموذج Excel لنموذج البياناتمثال 1
إذا كانت لدينا ثلاث مجموعات بيانات متعلقة بمندوب المبيعات: تحتوي أولاً على معلومات الإيرادات ، والثانية تحتوي على دخل مندوب المبيعات ، وثالثة تحتوي على نفقات مندوب المبيعات.
لربط مجموعات البيانات الثلاث هذه وإقامة علاقة معها ، نقوم بإنشاء نموذج بيانات بالخطوات التالية:
- تحويل مجموعات البيانات إلى كائنات جدول:
لا يمكننا إنشاء علاقة مع مجموعات البيانات العادية. يعمل نموذج البيانات مع كائنات جداول Excel فقط. لفعل هذا:
- الخطوة 1 - انقر في أي مكان داخل مجموعة البيانات ، ثم انقر فوق علامة التبويب "إدراج" ثم انقر فوق "جدول" في مجموعة "الجداول".
- الخطوة 2 - حدد الخيار أو ألغِ تحديده: "يحتوي My Table على رؤوس" وانقر فوق "موافق".
- الخطوة 3 - بعد تحديد الجدول الجديد ، أدخل اسم الجدول في "اسم الجدول" في مجموعة "الأدوات".
- الخطوة 4 - الآن يمكننا أن نرى أن مجموعة البيانات الأولى قد تم تحويلها إلى كائن "جدول". عند تكرار هذه الخطوات لمجموعتي البيانات الأخريين ، نرى أنهما يتم تحويلهما أيضًا إلى كائنات "جدول" على النحو التالي:
إضافة كائنات "الجدول" إلى نموذج البيانات: عبر الاتصالات أو العلاقات.
عبر اتصالات
- حدد جدولاً واحداً وانقر فوق علامة التبويب "البيانات" ثم انقر فوق "اتصالات".
- في مربع الحوار الناتج ، يوجد رمز "إضافة". قم بتوسيع القائمة المنسدلة "إضافة" وانقر فوق "إضافة إلى نموذج البيانات".
- انقر فوق "جداول" في مربع الحوار الناتج ثم حدد أحد الجداول وانقر فوق "فتح".
عند القيام بذلك ، سيتم إنشاء نموذج بيانات مصنف بجدول واحد ويظهر مربع حوار على النحو التالي:
لذلك إذا كررنا هذه الخطوات للجدولين الآخرين أيضًا ، فسيحتوي نموذج البيانات الآن على جميع الجداول الثلاثة.
يمكننا الآن رؤية أن جميع الجداول الثلاثة تظهر في اتصالات المصنف.
عبر العلاقات
إنشاء العلاقة: بمجرد أن تصبح مجموعتي البيانات كائنات جدول ، يمكننا إنشاء علاقة بينهما. لفعل هذا:
- انقر فوق علامة التبويب "البيانات" ثم انقر فوق "العلاقات".
- سنرى مربع حوار فارغًا حيث لا توجد اتصالات حالية.
- انقر فوق "جديد" ويظهر مربع حوار آخر.
- قم بتوسيع القائمتين المنسدلتين "جدول" و "جدول مرتبط": يظهر مربع حوار "إنشاء علاقة" لاختيار الجداول والأعمدة لاستخدامها في العلاقة. في توسيع "الجداول" ، حدد مجموعة البيانات التي نرغب في تحليلها بطريقة ما ، وفي "الجدول المرتبط" ، حدد مجموعة البيانات التي تحتوي على قيم بحث.
- جدول البحث في Excel هو الجدول الأصغر في حالة وجود علاقة واحدة إلى عدة علاقات ولا يحتوي على قيم مكررة في العمود المشترك. في توسيع "العمود (الخارجي)" ، حدد العمود المشترك في الجدول الرئيسي ، في "العمود المرتبط (أساسي)" ، حدد العمود المشترك في الجدول ذي الصلة.
- مع تحديد كل هذه الإعدادات الأربعة ، انقر فوق "موافق". يظهر مربع حوار على النحو التالي عند النقر فوق "موافق".
إذا كررنا هذه الخطوات لربط جدولين آخرين: جدول الإيرادات مع جدول المصروفات ، فسيتم ربطهما أيضًا في نموذج البيانات على النحو التالي:
ينشئ Excel الآن العلاقة خلف الكواليس من خلال دمج البيانات في نموذج البيانات استنادًا إلى عمود مشترك: معرف مندوب المبيعات (في هذه الحالة).
المثال رقم 2
الآن ، دعنا نقول في المثال أعلاه أننا نرغب في إنشاء جدول محوري يقوم بتقييم أو تحليل كائنات الجدول:
- انقر فوق "إدراج" -> "جدول محوري".
- في مربع الحوار الناتج ، انقر فوق الخيار الذي ينص على: "استخدام مصدر بيانات خارجي" ثم انقر فوق "اختيار اتصال".
- انقر فوق "جداول" في مربع الحوار الناتج وحدد نموذج بيانات المصنف الذي يحتوي على ثلاثة جداول وانقر فوق "فتح".
- حدد خيار "ورقة عمل جديدة" في الموقع وانقر على "موافق".
- سيعرض جزء حقول الجدول المحوري كائنات الجدول.
- يمكن الآن إجراء التغييرات في Pivot Table وفقًا لذلك لتحليل كائنات الجدول كما هو مطلوب.
على سبيل المثال ، في هذه الحالة ، إذا كنا نرغب في العثور على إجمالي الإيرادات أو الإيرادات لمندوب مبيعات معين ، فسيتم إنشاء Pivot Table على النحو التالي:
هذا مفيد للغاية في حالة وجود نموذج / جدول يحتوي على عدد كبير من الملاحظات.
لذلك ، يمكننا أن نرى أن Pivot Table يستخدم على الفور نموذج البيانات (ينتقيها عن طريق اختيار الاتصال) في ذاكرة Excel لإظهار العلاقات بين الجداول.
أشياء للذكرى
- باستخدام نموذج البيانات ، يمكننا تحليل البيانات من عدة جداول في وقت واحد.
- من خلال إنشاء علاقات مع نموذج البيانات ، فإننا نتجاوز الحاجة إلى استخدام صيغ VLOOKUP و SUMIF و INDEX و MATCH لأننا لا نحتاج إلى الحصول على جميع الأعمدة في جدول واحد.
- عندما يتم استيراد مجموعات البيانات في Excel من مصادر خارجية ، يتم إنشاء النماذج ضمنيًا.
- يمكن إنشاء علاقات الجدول تلقائيًا إذا قمنا باستيراد جداول ذات صلة لها علاقات مفاتيح أساسية وأخرى خارجية.
- أثناء إنشاء العلاقات ، يجب أن تحتوي الأعمدة التي نربطها في الجداول على نفس نوع البيانات.
- باستخدام الجداول المحورية التي تم إنشاؤها باستخدام نموذج البيانات ، يمكننا إضافة مقسمات أيضًا وتقسيم الجداول المحورية إلى أي حقل نريده.
- تتمثل ميزة Data Model عبر وظائف LOOKUP () في أنها تتطلب ذاكرة أقل بشكل كبير.
- يدعم Excel 2013 علاقات من واحد إلى واحد أو علاقة واحدة إلى عدة علاقات ، أي يجب ألا يحتوي أحد الجداول على قيم مكررة في العمود الذي نرتبط به.
