هل سبق لك استخدام VLOOKUP لإحضار عمود من جدول إلى جدول آخر؟ الآن بعد أن أصبح Excel يحتوي على نموذج بيانات مضمن، أصبح VLOOKUP قديما. يمكنك إنشاء علاقة بين جدولين من البيانات، استنادا إلى مطابقة البيانات في كل جدول. ثم يمكنك إنشاء أوراق Power View وإنشاء PivotTables وتقارير أخرى مع حقول من كل جدول، حتى عندما تكون الجداول من مصادر مختلفة. على سبيل المثال، إذا كانت لديك بيانات مبيعات العملاء، فقد ترغب في استيراد بيانات التحليل الذكي للوقت وربطها لتحليل أنماط المبيعات حسب السنة والشهر.
تكون كل الجداول الموجودة في مصنف مُدرجة في قوائم "حقول" PivotTable وPower View.
ملاحظة: الرجاء التأكد من تمكين الميزة قبل اتباع الخطوات الواردة في هذه المقالة. لمزيد من المعلومات، انتقل إلى بدء الوظيفة الإضافية Power Pivot ل Excel.
عند استيراد الجداول ذات الصلة من قاعدة بيانات ارتباطية، يمكن ل Excel غالبا إنشاء هذه العلاقات في نموذج البيانات الذي يقوم بإنشائه خلف الكواليس. بالنسبة لجميع الحالات الأخرى، ستحتاج إلى إنشاء علاقات يدويا.
-
تأكد من احتواء المصنف على جدولين على الأقل، ومن أن كل جدول يتضمّن عموداً يمكن تعيينه إلى عمود في جدول آخر.
-
قم بأحد الإجراءات التالية: تنسيق البيانات كجدول، أو استيراد بيانات خارجية كجدول في ورقة عمل جديدة.
-
قم بتعيين اسم ذي معنى إلى كل جدول: في أدوات الجدول، انقر فوق تصميم > اسم الجدول > أدخل اسماً.
-
تحقق من أن العمود الموجود في أحد الجداول يتضمن قيم بيانات فريدة بدون أية تكرارات. بإمكان Excel أن ينشئ العلاقة فقط إذا احتوى أحد الأعمدة على قيم فريدة.
على سبيل المثال، للربط بين مبيعات العملاء وتحليل معلومات الوقت، يجب أن تكون التواريخ بالتنسيق نفسه في كلا الجدولين (مثل 1/1/2012)، ويسرد جدول واحد على الأقل (تحليل معلومات الوقت) كل تاريخ مرة واحدة فقط داخل العمود.
-
انقر فوق بيانات > علاقات.
إذا ظهر الخيار علاقات باللون الرمادي، فهذا يعني أن المصنف يحتوي على جدول واحد فقط.
-
في المربع إدارة العلاقات، انقر فوق جديد.
-
في المربع إنشاء علاقة، انقر فوق السهم لـ الجدول، وحدد جدولاً من القائمة. في علاقة رأس بأطراف، يجب أن يكون هذا الجدول في جانب الأطراف. وباستخدام مثال العملاء وتحليل معلومات الوقت، يمكنك أن تختار جدول مبيعات العملاء أولاً، لأنه من المحتمل تحقيق عدد كبير من المبيعات في أي يوم معين.
-
بالنسبة إلى العمود (خارجي)، حدد العمود الذي يحتوي على البيانات المرتبطة بالعمود المرتبط (أساسي). على سبيل المثال، عند وجود عمود تاريخ في الجدولين، يمكنك اختيار ذلك العمود الآن.
-
بالنسبة إلى الجدول المرتبط، حدد جدولاً يحتوي على عمود بيانات واحد على الأقل يرتبط بالجدول الذي حددته الآن في الجدول.
-
بالنسبة إلى العمود المرتبط (أساسي)، حدد عموداً يحتوي على قيم فريدة تتطابق مع القيم في العمود الذي حددته في العمود.
-
انقر فوق موافق.
المزيد حول العلاقات بين الجداول في Excel
ملاحظات حول العلاقات
-
ستعرف ما إذا كانت هناك علاقة عند سحب الحقول من جداول مختلفة إلى قائمة حقول PivotTable. إذا لم تتم مطالبتك بإنشاء علاقة، فإن Excel لديه بالفعل معلومات العلاقة التي يحتاجها لربط البيانات.
-
تُعد عملية إنشاء العلاقات مماثلة لاستخدام دالات VLOOKUP: إنك تحتاج إلى أعمدة تحتوي على بيانات متطابقة لتمكين Excel من إنشاء إسناد ترافقي بين الصفوف في أحد الجداول مع صفوف في جدول آخر. في مثال معلومات تحليل الوقت، قد يحتاج جدول العملاء إلى قيم تاريخ موجودة أيضاً في جدول تحليل معلومات الوقت.
-
في نموذج البيانات، يمكن أن تكون العلاقات بين الجداول عبارة عن علاقة رأس برأس (لدى كل مسافر بطاقة صعود إلى الطائرة) أو علاقة رأس بأطراف (تشتمل كل رحلة طيران على العديد من المسافرين)، وليست علاقة أطراف بأطراف. تؤدي العلاقات من متعدد إلى متعدد إلى أخطاء تبعية دائرية، مثل "تم الكشف عن تبعية دائرية." سيحدث هذا الخطأ إذا أجريت اتصالاً مباشراً بين جدولين تربطهما علاقة أطراف بأطراف، أو اتصالات غير مباشرة (سلسلة من العلاقات بين الجداول التي هي عبارة عن علاقة رأس بأطراف ضمن كل علاقة، ولكنها تكون علاقات أطراف بأطراف عند عرضها من نهاية إلى نهاية). اقرأ المزيد حول العلاقات بين الجداول في نموذج البيانات.
-
يجب أن تكون أنواع البيانات في العمودين متوافقة. راجع أنواع البيانات في نماذج بيانات Excel للحصول على التفاصيل.
-
قد تكون الطرق الأخرى لإنشاء العلاقات أكثر بديهية، خاصةً إذا لم تكن متأكداً من الأعمدة التي يجب استخدامها. راجع إنشاء علاقة في طريقة عرض الرسم التخطيطي في Power Pivot.
مثال: إنشاء ارتباط بين بيانات تحليل معلومات الوقت وبيانات رحلات شركة طيران
يمكنك معرفة المزيد حول علاقات الجدول وتحليل معلومات الوقت باستخدام البيانات المجانية في Microsoft Azure Marketplace. تتميز بعض مجموعات البيانات هذه بحجم كبير أكثر من اللازم، مما يستوجب توفر اتصال سريع بالإنترنت لإكمال عملية تنزيل البيانات خلال فترة زمنية معقولة.
-
ابدأ بتشغيل الوظيفة الإضافية Power Pivot في Microsoft Excel 2013 وافتح نافذة Power Pivot.
-
انقر فوق إحضار بيانات خارجية > من خدمة البيانات > من موقع تسوق Microsoft Azure Marketplace. تفتح صفحة Microsoft Azure Marketplace الرئيسية في "معالج استيراد الجدول".
-
ضمن Price، انقر فوق Free.
-
ضمن Category، انقر فوق Science & Statistics.
-
ابحث عن DateStream، وانقر فوق Subscribe.
-
أدخل إلى حسابك في Microsoft، وانقر فوق تسجيل الدخول. من المفترض أن تظهر معاينة للبيانات في النافذة.
-
قم بالتمرير للأسفل، وانقر فوق Select Query.
-
انقر فوق التالي.
-
اختر BasicCalendarUS، ثم انقر فوق Finish لاستيراد البيانات. باستخدام اتصال سريع بالإنترنت، من المفترض أن تستغرق عملية الاستيراد حوالي دقيقة واحدة. عند الانتهاء، من المفترض أن تشاهد تقريراً عن الحالة لإعلامك بنقل 73414 صفاً. انقر فوق Close.
-
انقر فوق إحضار بيانات خارجية > من خدمة البيانات > من موقع تسوق Microsoft Azure Marketplace لاستيراد مجموعة بيانات أخرى.
-
ضمن Type، انقر فوق Data.
-
ضمن Price، انقر فوق Free.
-
ابحث عن US Air Carrier Flight Delays، وانقر فوق Select.
-
قم بالتمرير للأسفل، وانقر فوق Select Query.
-
انقر فوق التالي.
-
انقر فوق Finish لاستيراد البيانات. باستخدام اتصال سريع بالإنترنت، من المفترض أن تستغرق عملية الاستيراد حوالي 15 دقيقة. عند الانتهاء، من المفترض أن تشاهد تقريراً عن الحالة لإعلامك بنقل 2427284 صفاً. انقر فوق Close. من المفترض أن يتضمّن الآن نموذج البيانات جدولين. لربطها، سنحتاج إلى أعمدة متوافقة في كل جدول.
-
لاحظ أن تنسيق DateKey في BasicCalendarUS هو 1/1/2012 12:00:00 ص. يتضمّن أيضاً الجدول On_Time_Performance عمود وقت وتاريخ FlightDate، وقيمة محددة بالتنسيق نفسه: 1/1/2012 12:00:00 ص. يحتوي العمودان على بيانات متطابقة، من نوع البيانات نفسه، ويحتوي أحد العمودين على الأقل (DateKey) على قيم فريدة فقط. في الخطوات العديدة التالية، ستستخدم هذه الأعمدة لربط الجداول.
-
في نافذة Power Pivot، انقر فوق PivotTable لإنشاء PivotTable في ورقة عمل جديدة أو موجودة.
-
في قائمة الحقول، وسّع On_Time_Performance وانقر فوق ArrDelayMinutes لإضافته إلى ناحية القيم. من المفترض أن تشاهد في PivotTable إجمالي وقت التأخير في الرحلات، وقد تم قياسها بالدقائق.
-
وسّع BasicCalendarUS وانقر فوق MonthInCalendar لإضافته إلى ناحية الصفوف.
-
لاحظ أن PivotTable يذكر الآن الأشهر، ولكن إجمالي الدقائق هو نفسه لكل شهر. تشير القيم المتكررة المماثلة إلى وجوب إنشاء علاقة.
-
في قائمة الحقول، في "قد تكون هناك حاجة إلى العلاقات بين الجداول"، انقر فوق إنشاء.
-
في الجدول المرتبط، حدد On_Time_Performance وفي الجدول المرتبط (أساسي)، حدد FlightDate.
-
في الجدول، حدد BasicCalendarUS وفي العمود (خارجي)، اختر DateKey. انقر فوق موافق لإنشاء العلاقة.
-
لاحظ أن مجموعة دقائق التأخير يختلف الآن من شهر إلى آخر.
-
في BasicCalendarUS اسحب YearKey إلى ناحية الصفوف، أعلى MonthInCalendar.
يمكنك الآن تقسيم تأخير موعد الوصول حسب السنة والشهر، أو قيم أخرى في التقويم.
تلميحات: يتم بشكلٍ افتراضي إدراج الأشهر بترتيب أبجدي. وباستخدام الوظيفة الإضافية Power Pivot، يمكنك تغيير ترتيب الفرز بحيث تظهر الأشهر بترتيب زمني.
-
تأكد من فتح الجدول BasicCalendarUS في نافذة Power Pivot.
-
في جدول الصفحة الرئيسية، انقر فوق فرز حسب العمود.
-
في فرز، اختر MonthInCalendar
-
في حسب، اختر MonthOfYear.
يقوم الآن PivotTable بفرز مجموعة الشهر-السنة (أكتوبر 2011، نوفمبر 2011) حسب رقم الشهر ضمن سنة (10، 11). يمكنك تغيير ترتيب الفرز بسهولة لأن موجز DateStream يوفر كل الأعمدة الضرورية التي تسمح بعمل هذا السيناريو. إذا كنت تستخدم جدول معلومات زمنية مختلفا، فستختلف خطوتك.
"قد تكون هناك حاجة إلى العلاقات بين الجداول"
أثناء إضافة حقول إلى PivotTable، سيتم إعلامك إذا كانت هناك حاجة إلى علاقة جدول لتجعل الحقول التي حددتها في PivotTable منطقية.
على الرغم من أن Excel يمكنه إعلامك عند الحاجة إلى علاقة، إلا أنه لا يمكنه إعلامك بالجداول والأعمدة التي يجب استخدامها، أو ما إذا كانت علاقة الجدول ممكنة. حاول اتباع هذه الخطوات للحصول على الأجوبة التي تحتاج إليها.
الخطوة 1: تحديد الجداول التي يجب تعيينها في العلاقة
إذا كان النموذج يحتوي على بضع جداول فقط، فقد تظهر فوراً وبوضوح الجداول التي يجب استخدامها. ولكنك قد تحتاج إلى بعض المساعدة إذا كنت تستخدم نماذج أكبر حجماً. وهناك طريقة لذلك تتمثل في استخدام طريقة عرض الرسم التخطيطي في الوظيفة الإضافية Power Pivot. توفر "طريقة عرض الرسم التخطيطي" تمثيلاً مرئياً لكل الجداول الموجودة في "نموذج البيانات". فباستخدام "طريقة عرض الرسم التخطيطي"، تستطيع أن تحدد سريعاً الجداول المنفصلة عن باقي النموذج.
ملاحظة: من الممكن إنشاء علاقات غامضة غير صالحة عند استخدامها في تقرير PivotTable أو Power View. لنفترض أن جميع الجداول مرتبطة بطريقة ما بجداول أخرى في النموذج، ولكن عند محاولة دمج حقول من جداول مختلفة، تحصل على رسالة "قد تكون هناك حاجة إلى العلاقات بين الجداول". السبب الأكثر احتمالا هو أنك واجهت علاقة متعدد إلى متعدد. إذا تابعت سلسلة علاقات الجداول التي تقوم بتوصيل الجداول التي تريد استخدامها، فستكتشف على الأرجح وجود علاقة رأس بأطراف واحدة أو أكثر. لا يوجد حل بديل سهل يصلح لكل الحالات، ولكن يمكنك أن تحاول إنشاء أعمدة محسوبة لدمج الأعمدة التي تريد استخدامها في جدول واحد.
الخطوة 2: البحث عن الأعمدة التي يمكن استخدامها لإنشاء مسار من جدول إلى آخر
بعد تحديد الجدول الذي تم قطع اتصاله ببقية النموذج، راجع أعمدةه لتحديد ما إذا كان عمود آخر، في مكان آخر في النموذج، يحتوي على قيم مطابقة.
على سبيل المثال، لنفترض أن نموذجك يحتوي على مبيعات المنتجات حسب المقاطعة، وأنك قمت نتيجة لذلك باستيراد بيانات سكانية لمعرفة ما إذا كانت هناك علاقة بين المبيعات والاتجاهات السكانية في كل مقاطعة. وبما أن البيانات السكانية تأتي من مصدر بيانات مختلفة، فسيتم عزل جداولها بشكل مبدئي عن باقي النموذج. لدمج البيانات الديموغرافية مع بقية النموذج الخاص بك، ستحتاج إلى العثور على عمود في أحد الجداول الديموغرافية التي تتوافق مع الجدول الذي تستخدمه بالفعل. على سبيل المثال، إذا كانت البيانات السكانية منظمة حسب المنطقة، وكانت بيانات المبيعات تحدد المنطقة التي حدثت فيها عملية البيع، فيمكنك الربط بين مجموعتي البيانات عبر البحث عن عمود مشترك، مثل الولاية أو الرمز البريدي أو المنطقة، لتزويد عملية البحث.
إلى جانب مطابقة القيم، ثمة بعض المتطلبات الإضافية لإنشاء علاقة:
-
يجب أن تكون قيم البيانات في عمود البحث فريدة. بمعنى آخر، لا يمكن أن يحتوي العمود على تكرارات. في نموذج البيانات، تكون القيم الخالية مكافئة للقيم الفارغة، وهي قيمة بيانات متميزة. وهذا يعني أنه لا يمكنك الحصول على عدة قيم خالية في عمود البحث.
-
يجب أن تكون أنواع البيانات في كل من عمود المصدر وعمود البحث متوافقة. لمزيد من المعلومات حول أنواع البيانات، راجع أنواع البيانات في نماذج البيانات.
لمعرفة المزيد حول العلاقات بين الجداول، راجع العلاقات بين الجداول في نموذج بيانات.
