هل سبق لك استخدام VLOOKUP لإحضار عمود من جدول إلى جدول آخر؟ الآن بعد أن أصبح Excel يحتوي على نموذج بيانات مضمن، أصبح VLOOKUP قديما. يمكنك إنشاء علاقة بين جدولين من البيانات، استنادا إلى مطابقة البيانات في كل جدول. ثم يمكنك إنشاء أوراق Power View وإنشاء PivotTables وتقارير أخرى مع حقول من كل جدول، حتى عندما تكون الجداول من مصادر مختلفة. على سبيل المثال، إذا كانت لديك بيانات مبيعات العملاء، فقد ترغب في استيراد بيانات التحليل الذكي للوقت وربطها لتحليل أنماط المبيعات حسب السنة والشهر.
تكون كل الجداول الموجودة في مصنف مُدرجة في قوائم "حقول" PivotTable وPower View.
عندما تستورد جداول مرتبطة من قاعدة بيانات علائقية، بإمكان 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: البحث عن الأعمدة التي يمكن استخدامها لإنشاء مسار من جدول إلى آخر
بعد انتهائك من تحديد الجدول المنفصل عن باقي النموذج، يمكنك مراجعة أعمدته لتحديد ما إذا كان أحد الأعمدة، في مكان آخر في النموذج، يحتوي على قيم متطابقة.
على سبيل المثال، لنفترض أن نموذجك يحتوي على مبيعات المنتجات حسب المقاطعة، وأنك قمت نتيجة لذلك باستيراد بيانات سكانية لمعرفة ما إذا كانت هناك علاقة بين المبيعات والاتجاهات السكانية في كل مقاطعة. وبما أن البيانات السكانية تأتي من مصدر بيانات مختلفة، فسيتم عزل جداولها بشكل مبدئي عن باقي النموذج. لدمج البيانات السكانية بباقي النموذج، ستحتاج إلى البحث عن عمود في أحد جداول البيانات السكانية يتطابق مع العمود الذي تستخدمه. على سبيل المثال، إذا كانت البيانات السكانية منظمة حسب المنطقة، وكانت بيانات المبيعات تحدد المنطقة التي حدثت فيها عملية البيع، فيمكنك الربط بين مجموعتي البيانات عبر البحث عن عمود مشترك، مثل الولاية أو الرمز البريدي أو المنطقة، لتزويد عملية البحث.
إلى جانب مطابقة القيم، ثمة بعض المتطلبات الإضافية لإنشاء علاقة:
-
يجب أن تكون قيم البيانات في عمود البحث فريدة. بكلمات أخرى، لا يمكن للعمود أن يحتوي على تكرارات. في نموذج البيانات، تكون القيم الخالية مكافئة للقيم الفارغة، وهي قيمة بيانات متميزة. هذا يعني عدم إمكانية وجود عدة قيم خالية في عمود البحث.
-
يجب أن تكون أنواع البيانات في كل من عمود المصدر وعمود البحث متوافقة. لمزيد من المعلومات حول أنواع البيانات، راجع أنواع البيانات في نماذج البيانات.
لمعرفة المزيد حول العلاقات بين الجداول، راجع العلاقات بين الجداول في نموذج بيانات.
