اذا كنت تعاملت مع أحد برامج نظم ادارة قواعد البيانات مثل برنامج Access فبالتأكيد سيكون لديك خلفية جيدة بموضوع العلاقات بين الجداول , لحسن الحظ يتيح لك برنامج Excel عمل ربط بين الجداول عن طريق استخدام العلاقات كما سنرى في هذا الدرس .
الهدف من العلاقات هو تجنب تكرار البيانات وأيضا تعكامل البيانات , على سبيل المثال لنفترض أنك تعمل محاسب في أحد محلات الأجهزة الالكترونية , اذا كنت تقوم بتسجيل المبيعات في جدول واحد فستقوم بتكرار بيانات العميل في كل مرة يقوم بعملية شراء , لذا من الأفضل عمل جدولين , بحيث يكون كل جدول في صفحة منفصلة .
الجدول الأول في الصفحة الأولى تقوم بتسجيل بيانات العملاء مثل ( الاسم - العنوان - رقم التليفون ... الخ ) لاحظ أنه منطقيا أنك ستقوم بتسجيل اسم العميل مرة واحدة فقط ويجب أن تكون الأسماء في هذا العمود مختلفة .
الجدول الثانى في الصفحة الثانية تقوم بتسجيل عمليات شراء العملاء عن طريق استخدام اسم العميل فقط دون كتابة باقى بياناته لأنها موجودة فعليا في الجدول الأول .
في العلاقات بين الجداول يسمى عمود اسم العميل Customer Name في الجدول الأول عمود أساسي Primary بينما يسمى في الجدول الثاني عمود أجنبي Foreign
العلاقة السابقة لن يفهمها برنامج Excel من نفسه , لابد أن تقوم أنت بنفسك بتعريفها لبرنامج Excel , قبل عمل العلاقة بين الجدولين لابد من تحويل كل نطاق إلى جدول Excel كما سنرى في الأجزاء التالية .
في الصفحة الأولى قم بتحديد النطاق A1:C7 ثم اذهب إلى التبويب Insert واضغط على Table
سيظهر مربع حوار , تأكيد من ترك الاختيار My table has headers مفعل , هذا الاختيار يجعل برنامج Excel يفهم أن الصف الأول في النطاق هو عناوين الأعمدة , ثم اضغط على زر OK
قم بتحديد أى خلية داخل الجدول , ثم اذهب إلى التبويب Design وقم بمسح الاسم الافتراضى وقم بكتابة أى اسم معبر وليكن مثلا العملاء Customers
في الصفحة الثانية قم بتحديد النطاق A1:D27 ثم اذهب إلى التبويب Insert واضغط على Table
سيظهر مربع حوار , تأكد من ترك الاختيار My table has headers مفعل , هذا الاختيار يجعل برنامج Excel يفهم أن الصف الأول في النطاق هو عناوين الأعمدة , ثم اضغط على زر OK
قم بتحديد أى خلية داخل الجدول , ثم اذهب إلى التبويب Design وقم بمسح الاسم الافتراضى وقم بكتابة أى اسم معبر وليكن مثلا الطلبيات Orders
بعد ما قمنا بعمل جدولين Excel لنقم الأن بعمل العلاقة بين الجدولين , اذهب إلى التبويب Data ثم اضغط على Relationships
سيظهر مربع حوار , اضغط على New لعمل علاقة جديدة .
قم بإختيار الجدول Orders والعمود Customer Name والجدول Customers والعمود Customer Name , لاحظ أننا قمنا بإختيار العمود Customer Name في الجدولين لأن هذا العمود هو العمود الذى يربط بين الجدولين , ثم اضغط على زر OK
ستعود إلى مربع الحوار السابق وستلاحظ أنه قد تم اضافة العلاقة بين الجدولين , اضغط على زر Close لغلق مربع الحوار .
الأن قمنا بعمل العلاقة بين الجدولين , سنتطرق الأن إلى مثال عملي سنرى فيه فائدة العلاقات بين الجداول . افترض أنك تريد عمل Pivot Table يلخص اجمالى الكمية المباعة من كل صنف لكل العملاء في كل المحافظات , أى أنك تريد عمل تلخيص للمبيعات وفقا للتسلسل التالي :
- العنوان Address
- اسم العميل Customer Name
- الصنف Item
- اجمالي الكمية Quantity
ملحوظة : للتعرف على أساسيات عمل Pivot Table قم بقراءة هذا الدرس : شرح pivot table
لعمل ما نريد سنقوم بعمل Pivot Table يأخد بيانات من جدول العملاء Customers وجدول الطلبيات Orders كما سنرى في الأجزاء التالية .
قم بتحديد جدول العملاء في الصفحة الأولى ثم اذهب إلى التبويب Insert واضغط على PivotTable
سيظهر مربع حوار , لاحظ أن برنامج Excel لم يقم بكتابة النطاق A1:C7 ولكن قام بكتابة Customers وهو اسم الجدول , اضغط على زر OK
سيتم ادراج Pivot Table في صفحة جديدة , الأن قمنا بعمل Pivot Table يأخذ بيانات من جدول العملاء فقط , ولكن نحن نريد بيانات من الجدول المرتبط به وهو جدول الطلبيات , لذا نريد اضافة جدول الطلبيات , لعمل ذلك اضغط على More Tables
ستظهر رسالة تخبرك أنه اذا كنت تريد اضافة جدول أخر سيتم عمل Pivot Table جديد يضم الجدولين , اضغط على زر Yes
بالفعل قام برنامج Excel بعمل Pivot Table جديد في صفحة جديدة يضم جدول العملاء Customers وجدول الطلبيات Orders
قم بفتح جدول العملاء واختر اضافة العمود Address والعمود Customer Name ثم افتح جدول الطلبيات واختر اضافة عمود Item وعمود Quantity كما هو مبين في الشكل التالي .
في الجانب الأيسر من الشاشة سترى النتيجة التي نريدها وهي عمل تلخيص لبيانات الجدول وفقا للتسلسل التالي :
- العنوان Address
- اسم العميل Customer Name
- الصنف Item
- اجمالي الكمية Quantity
- اجمالي الكمية Quantity
