البرنامج التعليمي: استيراد البيانات إلى Excel، وإنشاء نموذج بيانات

الأقسام في هذا البرنامج التعليمي هي التالية:

• استيراد بيانات من قاعدة بيانات

• استيراد بيانات من جدول بيانات

• استيراد بيانات باستخدام النسخ واللصق

• إنشاء علاقة بين البيانات المستوردة

• نقطة تحقق واختبار

يمكنك إجراء الاختبار الموجود في نهاية هذا البرنامج التعليمي لاختبار الأمور التي تعلمتها.

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

استيراد بيانات من قاعدة بيانات

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

فلنبدأ بتنزيل بعض البيانات من الإنترنت. تصف البيانات الألعاب الأولمبية، وهي عبارة عن قاعدة بيانات Microsoft Access.

1. انقر فوق الارتباطات التالية لتنزيل الملفات التي سنستخدمها في سلسلة البرامج التعليمية هذه. قم بتنزيل كل ملف من الملفات الأربعة إلى موقع يمكن الوصول إليه بسهولة، مثل التنزيلات أو المستندات،أو إلى مجلد جديد تقوم بإنشاءه:
   > OlympicMedals.accdb Access
   >OlympicSports.xlsx Excel المصنف
   > Population.xlsx Excel مصنف
   >DiscImage_table.xlsx Excel مصنف

2. في Excel 2013، افتح مصنفاً فارغاً.

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

4. حدد ملف OlympicMedals.accdb الذي تريد تنزيله، وانقر فوق فتح. تظهر نافذة "تحديد جدول" التالية، وتعرض الجداول التي تم العثور عليها في قاعدة البيانات. تشبه الجداول في قاعدة البيانات أوراق العمل أو الجداول في Excel. حدد المربع تمكين تحديد جداول متعددة، وحدد كل الجداول. بعد ذلك، انقر فوق موافق.
   
   

5. تظهر نافذة "استيراد بيانات".

   ملاحظة: لاحظ خانة الاختيار في أسفل النافذة التي تسمح لك بإضافة هذه البيانات إلى نموذجالبيانات ، الموضحة في الشاشة التالية. يتم إنشاء نموذج بيانات تلقائيا عند استيراد جدولين أو أكثر أو العمل على جدولين أو أكثر في الوقت نفسه. يدمج نموذج البيانات الجداول، مما يمكن التحليل الموسع باستخدام PivotTables Power Pivot و Power View. عند استيراد جداول من قاعدة بيانات، يتم استخدام علاقات قاعدة البيانات الموجودة بين هذه الجداول لإنشاء نموذج البيانات في Excel. يكون نموذج البيانات شفافا في Excel، ولكن يمكنك عرضه وتعديله مباشرة باستخدام Power Pivot الإضافية. تمت مناقشة نموذج البيانات بتفاصيل إضافية لاحقا في هذا البرنامج التعليمي.

   
   حدد الخيار تقرير PivotTable، الذي يستورد الجداول إلى Excel ويجهز PivotTable لتحليل الجداول المستوردة، وانقر فوق موافق.
   
   

6. بعد استيراد البيانات، يتم إنشاء PivotTable باستخدام الجداول المستوردة.
   
   

مع البيانات المستوردة إلى Excel ونموذج البيانات الذي تم إنشاؤه تلقائياً، ستكون جاهزاً لاستكشاف البيانات.

استكشاف البيانات باستخدام PivotTable

يمكن استكشاف البيانات المستوردة بسهولة باستخدام PivotTable. في PivotTable، يمكنك سحب الحقول (مماثلة للأعمدة في Excel) من جداول (مماثلة للجداول التي انتهيت الآن من استيرادها من قاعدة بيانات Access) إلى مناطق مختلفة في PivotTable لضبط الطريقة التي يعرض بها بياناتك. يتضمّن جدول PivotTable أربع مناطق: عوامل التصفية والأعمدة والصفوف والقيم.

قد يتطلب إجراء اختبار لتحديد المنطقة التي يجب سحب الحقل إليها بعض الوقت. يمكنك سحب عدد كبير من الحقول أو بضعة حقول فقط من الجداول بحسب رغبتك، حتى يقدّم PivotTable بياناتك بالطريقة التي تريد رؤيتها فيها. لا تتردد في سحب الحقول إلى مناطق مختلفة في PivotTable، فالبيانات الأساسية لن تتأثر عندما ترتّب الحقول في PivotTable.

فلنستكشف بيانات الميداليات الأولمبية في PivotTable، بدءاً من الميداليات الأولمبية المرتبة بحسب التخصص الرياضي ونوع الميدالية وبلد الرياضي أو منطقته.

1. في حقول PivotTable، وسّع جدول Medals بالنقر فوق السهم إلى جانبه. ابحث عن الحقل NOC_CountryRegion في جدول Medals الموسّع، واسحبه إلى منطقة الأعمدة. يشير الاختصار NOC إلى اللجان الأولمية الوطنية، وهي الوحدة التنظيمية لبلد أو منطقة.

2. بعد ذلك، من جدول Disciplines، اسحب Discipline إلى منطقة الصفوف.

3. فلنطبق التصفية على جدول Disciplines بحيث تظهر خمس رياضات فقط: Diving وFencing وFigure Skating وSpeed Skating. يمكنك إجراء ذلك من ضمن منطقة حقول PivotTable أو من عامل التصفة تسميات الصفوف في جدول PivotTable بحد ذاته.

   1. انقر فوق أي مكان في PivotTable لضمان Excel PivotTable. في القائمة حقول PivotTable، حيث تم توسيع جدول Disciplines، مرر فوق حقل Discipline ويظهر سهم القائمة المنسدلة إلى يمين الحقل. انقر فوق المنسدلة، وانقر فوق (تحديد الكل)لإزالة كل التحديدات، ثم قم بالتمرير لأسفل وحدد الرماية والغطس والمبارزة والمبارزة الشكلي والتزلج على الجليد السريع. انقر فوق موافق.

   2. أو، في القسم تسميات الصفوف في PivotTable، انقر فوق سهم القائمة المنسدلة إلى جانب تسميات الصفوف في PivotTable، انقر فوق (تحديد الكل) لإزالة كل التحديدات، ثم مرّر للأسفل وحدد Diving وFencing وFigure Skating وSpeed Skating. انقر فوق موافق.

4. في حقول PivotTable ، من الجدول Medals، اسحب Medal إلى منطقة القيم. بما أن القيم يجب أن تكون رقمية، يغيّر Excel تلقائياً Medal إلى عدد Medal.

5. من جدول Medals، حدد Medal مرة أخرى واسحبها إلى منطقة عوامل التصفية.

6. فلنعمل الآن على تصفية PivotTable بحيث لا تظهر إلا البلدان أو المناطق التي فازت بأكثر من 90 ميدالية. إليك كيفية القيام بذلك.

   1. في PivotTable، انقر فوق سهم القائمة المنسدلة إلى يمين تسميات الأعمدة.

   2. حدد عوامل تصفية القيمة وحدد أكبر من….

   3. اكتب 90 في الحقل الأخير (إلى اليمين). انقر فوق موافق.
      

يبدو جدول PivotTable مماثلاً للشاشة التالية.

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

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

استيراد بيانات من جدول بيانات

فلنستورد الآن بيانات من مصدر آخر، هذه المرة من مصنف موجود، ثم سنحدد العلاقات بين البيانات الموجودة والبيانات الجديدة. تسمح لك العلاقات بتحليل مجموعات من البيانات في Excel، وإنشاء مؤثرات عرض رائعة ومثيرة للاهتمام من البيانات التي تستوردها.

سنبدأ بإنشاء ورقة عمل فارغة، ثم نستورد بيانات من مصنف Excel.

1. أدرج ورقة عمل Excel جديدة، وأدخل الاسم Sports لها.

2. استعرض وصولاً إلى المجلد الذي يحتوي على ملفات البيانات النموذجية التي قمت بتنزيلها، وافتح OlympicSports.xlsx.

3. حدد البيانات في Sheet1 وانسخها. إذا حددت خلية تحتوي على بيانات، كالخلية A1 مثلاً، فيمكنك الضغط على Ctrl + A لتحديد كل البيانات المتجاورة. أغلق المصنف OlympicSports.xlsx.

4. على ورقة العمل Sports، ضع المؤشر في الخلية A1 والصق البيانات.

5. مع استمرار تمييز البيانات، اضغط على Ctrl + T لتنسيق البيانات كجدول. يمكنك أيضاً تنسيق البيانات كجدول من الشريط بتحديد الصفحة الرئيسية > تنسيق كجدول. بما أن البيانات تحتوي على رؤوس، حدد يحتوي الجدول على رؤوس في النافذة إنشاء جدول التي تظهر، كما هو مبيّن هنا.
   
   
   
   إن تنسيق البيانات ك جدول له فوائد عديدة. يمكنك تعيين اسم إلى جدول، مما يسهّل التعرّف عليه. يمكنك أيضاً إنشاء علاقات بين الجداول، مما يمكّن الاستكشاف والتحليل في كل من PivotTables وPower Pivot وPower View.

6. أدخل اسماً للجدول. في أدوات الجدول > تصميم > خصائص، حدد موقع الحقل اسم الجدول واكتب Sports. يبدو الجدول مماثلاً للشاشة التالية.
   

7. احفظ المصنف.

استيراد بيانات باستخدام النسخ واللصق

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

1. أدرج ورقة عمل Excel جديدة، وأدخل الاسم بلدان مضيفة لها.

2. حدد الجدول التالي وانسخه، مع تضمين رؤوس الجدول.

1. في Excel، ضع المؤشر في الخلية A1 من ورقة العمل بلدان مضيفة والصق البيانات.

2. نسّق البيانات كجدول. كما ورد وصفه سابقاً في هذا البرنامج التعليمي، يمكنك الضغط على Ctrl + T لتنسيق البيانات كجدول، أو من علامة التبويب الصفحة الرئيسية > تنسيق كجدول. بما أن البيانات تحتوي على رؤوس، حدد يحتوي الجدول على رؤوس في النافذة إنشاء جدول التي تظهر.

3. أدخل اسماً للجدول. في أدوات الجدول > تصميم > خصائص، حدد موقع الحقل اسم الجدول، واكتب بلدان مضيفة.

4. حدد عمود السنة، ومن علامة التبويب الصفحة الرئيسية، نسّق هذا العمود كـ رقم بدون منازل عشرية.

5. احفظ المصنف. يبدو المصنف مماثلاً للشاشة التالية.

الآن وقد بات لديك مصنف Excel يتضمّن جداول، يمكنك إنشاء علاقات بينها. يسمح لك إنشاء علاقات بين الجداول بمزج البيانات من جدولين.

إنشاء علاقة بين البيانات المستوردة

يمكنك البدء باستخدام الحقول في PivotTable من الجداول المستوردة على الفور. إذا تعذّر على Excel تحديد كيفية تضمين حقل في PivotTable، فيجب إنشاء علاقة مع نموذج البيانات الموجود. في الخطوات التالية، ستتعلّم كيفية إنشاء علاقة بين البيانات التي استوردتها من مصادر مختلفة.

1. في ورقة1، في أعلى حقول PivotTable،انقر فوق الكل لعرض قائمة كاملة بالجداول المتوفرة، كما هو موضح في الشاشة التالية.
   

2. قم بالتمرير عبر القائمة لرؤية الجداول الجديدة التي أضفتها للتوّ.

3. وسّع Sports وحدد Sport لإضافتها إلى PivotTable. لاحظ أن Excel يطالبك بإنشاء علاقة، كما يظهر في الشاشة التالية.
   
    

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

4. انقر فوق إنشاء... في منطقة حقول PivotTable المميزة لفتح مربع الحوار إنشاء علاقة، كما يظهر في الشاشة التالية.
   
   

5. في الجدول، اختر Disciplines من القائمة المنسدلة.

6. في العمود (خارجي)، اختر SportID.

7. في الجدول المرتبط، اختر Sports.

8. في العمود المرتبط (أساسي)، اختر SportID.

9. انقر فوق موافق.

يتغيّر PivotTable لإظهار العلاقة الجديدة. غير أن PivotTable لا يظهر بشكل جيد بعد، بسبب ترتيب الحقول في منطقة الصفوف. إن التخصص الرياضي عبارة عن فئة فرعية من رياضة معينة، ولكن بما أننا قمنا بترتيب Discipline فوق Sport في منطقة الصفوف، فالترتيب غير صحيح. تُظهر الشاشة التالية هذا الترتيب غير المرغوب فيه.

1. في منطقة الصفوف، ضع Sport فوق Discipline. هذا أفضل بكثير، ويعرض PivotTable البيانات كما تريد رؤيتها، كما هو مبيّن في الشاشة التالية.
   
   

يعمل Excel في الخلفية على إنشاء نموذج بيانات يمكن استخدامه في المصنف بكامله أو في أي PivotTable أو PivotChart أو في Power Pivot أو أي تقرير Power View. تشكّل علاقات الجداول أساس نموذج البيانات، وما يحدد مسارات التنقل والحساب.

في البرنامج التعليمي التالي، توسيع علاقات نموذج البيانات باستخدام Excel 2013 وPower Pivot و DAX، يمكنك البناء على ما تعلمته هنا، والخطوة من خلال توسيع نموذج البيانات باستخدام أداة Excel الإضافية القوية والمرئية تسمى Power Pivot. ستتعرف أيضا على كيفية حساب الأعمدة في جدول، واستخدام هذا العمود المحسوب بحيث يمكن إضافة جدول غير مرتبط إلى نموذج البيانات.

نقطة تحقق واختبار

مراجعة الأمور التي تعلّمتها

لديك الآن مصنف Excel يتضمّن PivotTable يمكنه الوصول إلى بيانات في عدة جداول، وقد استوردت العديد منها بشكل منفصل. وتعلّمت كيفية الاستيراد من قاعدة بيانات ومن مصنف Excel آخر ومن النسخ واللصق في Excel.

لتمكين البيانات من العمل معاً، أنشأت علاقة جدول يستخدمها Excel لربط الصفوف فيما بينها. وتعلّمت أيضاً أن وجود أعمدة في جدول ترتبط ببيانات في جدول آخر يُعد أساسياً لإنشاء العلاقات والبحث عن الصفوف المرتبطة.

أنت الآن جاهز للبرنامج التعليمي التالي في هذه السلسلة. إليك الارتباط:

توسيع علاقات نموذج البيانات باستخدام Excel 2013 وPower Pivot وDAX

اختبار

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

السؤال 1: لماذا من المهم تحويل البيانات المستوردة إلى جداول؟

أ: لست بحاجة إلى تحويلها إلى جداول، إذ يتم تلقائياً تحويل كل البيانات المستوردة إلى جداول.

ب: إذا قمت بتحويل بيانات مستوردة إلى جداول، سيتم استبعادها من نموذج البيانات. ستتوفر في كل من PivotTables وPower Pivot وPower View فقط عند استبعادها من نموذج البيانات.

ج: إذا قمت بتحويل بيانات مستوردة إلى جداول، يمكن تضمينها في نموذج البيانات وجعلها متوفرة لكل من PivotTables Power Pivot وPower View.

د: لا يمكنك تحويل البيانات المستوردة إلى جداول.

السؤال 2: أي من مصادر البيانات التالية يمكنك استيرادها إلى Excel، وتضمينها في نموذج البيانات؟

أ: قواعد بيانات Access والعديد من قواعد البيانات الأخرى.

ب: ملفات Excel الموجودة.

ج: أي شيء يمكنك نسخه ولصقه في Excel وتنسيقه كجدول، بما في ذلك جداول البيانات في مواقع ويب أو المستندات أو أي شيء آخر يمكن لصقه في Excel.

د: كل ما سبق

السؤال 3: في PivotTable، ماذا يحدث عند إعادة ترتيب الحقول في مناطق حقول PivotTable الأربعة؟

أ: لا شيء – لا يمكنك إعادة ترتيب الحقول بعد وضعها في مناطق حقول PivotTable.

ب: يتغيّر تنسيق PivotTable لإظهار التخطيط، ولكن البيانات الأساسية لا تتأثر.

ج: يتغيّر تنسيق PivotTable لإظهار التخطيط، وتتغيّر كل البيانات الأساسية بشكل دائم.

د: تتغيّر البيانات الأساسية، مما يؤدي إلى الحصول على مجموعة بيانات جديدة.

السؤال 4: عند إنشاء علاقة بين الجداول، ما المطلوب؟

أ: لا يمكن لأي جدول أن يتضمّن عموداً يحتوي على قيم فريدة غير مكررة.

ب: يجب ألا يكون أحد الجداول جزءاً من مصنف Excel.

ج: يجب ألا تكون الأعمدة محوّلة إلى جداول.

د: كل الأجوبة أعلاه غير صحيحة.

إجابات الاختبار

1. الجواب الصحيح: ج

2. الإجابة الصحيحة: د

3. الإجابة الصحيحة: ب

4. الإجابة الصحيحة: د