إنشاء علاقة بين جدولين في Excel

اذا كنت تعاملت مع أحد برامج نظم ادارة قواعد البيانات مثل برنامج Access فبالتأكيد سيكون لديك خلفية جيدة بموضوع العلاقات بين الجداول , لحسن الحظ يتيح لك برنامج Excel عمل ربط بين الجداول عن طريق استخدام العلاقات كما سنرى في هذا الدرس .



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



الجدول الأول في الصفحة الأولى تقوم بتسجيل بيانات العملاء مثل ( الاسم - العنوان - رقم التليفون ... الخ ) لاحظ أنه منطقيا أنك ستقوم بتسجيل اسم العميل مرة واحدة فقط ويجب أن تكون الأسماء في هذا العمود مختلفة .

إنشاء علاقة بين جدولين في Excel




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

إنشاء علاقة بين جدولين في Excel





في العلاقات بين الجداول يسمى عمود اسم العميل Customer Name في الجدول الأول عمود أساسي Primary بينما يسمى في الجدول الثاني عمود أجنبي Foreign



العلاقة السابقة لن يفهمها برنامج Excel من نفسه , لابد أن تقوم أنت بنفسك بتعريفها لبرنامج Excel , قبل عمل العلاقة بين الجدولين لابد من تحويل كل نطاق إلى جدول Excel كما سنرى في الأجزاء التالية .



في الصفحة الأولى قم بتحديد النطاق A1:C7 ثم اذهب إلى التبويب Insert واضغط على Table

إنشاء علاقة بين جدولين في Excel




سيظهر مربع حوار , تأكيد من ترك الاختيار My table has headers مفعل , هذا الاختيار يجعل برنامج Excel يفهم أن الصف الأول في النطاق هو عناوين الأعمدة , ثم اضغط على زر OK

إنشاء علاقة بين جدولين في Excel




قم بتحديد أى خلية داخل الجدول , ثم اذهب إلى التبويب Design وقم بمسح الاسم الافتراضى وقم بكتابة أى اسم معبر وليكن مثلا العملاء Customers

إنشاء علاقة بين جدولين في Excel




في الصفحة الثانية قم بتحديد النطاق A1:D27 ثم اذهب إلى التبويب Insert واضغط على Table

إنشاء علاقة بين جدولين في Excel




سيظهر مربع حوار , تأكد من ترك الاختيار My table has headers مفعل , هذا الاختيار يجعل برنامج Excel يفهم أن الصف الأول في النطاق هو عناوين الأعمدة , ثم اضغط على زر OK

إنشاء علاقة بين جدولين في Excel




قم بتحديد أى خلية داخل الجدول , ثم اذهب إلى التبويب Design وقم بمسح الاسم الافتراضى وقم بكتابة أى اسم معبر وليكن مثلا الطلبيات Orders

إنشاء علاقة بين جدولين في Excel





بعد ما قمنا بعمل جدولين Excel لنقم الأن بعمل العلاقة بين الجدولين , اذهب إلى التبويب Data ثم اضغط على Relationships

إنشاء علاقة بين جدولين في Excel





سيظهر مربع حوار , اضغط على New لعمل علاقة جديدة .

إنشاء علاقة بين جدولين في Excel




قم بإختيار الجدول Orders والعمود Customer Name والجدول Customers والعمود Customer Name , لاحظ أننا قمنا بإختيار العمود Customer Name في الجدولين لأن هذا العمود هو العمود الذى يربط بين الجدولين , ثم اضغط على زر OK

إنشاء علاقة بين جدولين في Excel





ستعود إلى مربع الحوار السابق وستلاحظ أنه قد تم اضافة العلاقة بين الجدولين , اضغط على زر Close لغلق مربع الحوار .

إنشاء علاقة بين جدولين في Excel




الأن قمنا بعمل العلاقة بين الجدولين , سنتطرق الأن إلى مثال عملي سنرى فيه فائدة العلاقات بين الجداول . افترض أنك تريد عمل Pivot Table يلخص اجمالى الكمية المباعة من كل صنف لكل العملاء في كل المحافظات , أى أنك تريد عمل تلخيص للمبيعات وفقا للتسلسل التالي :

- العنوان Address

- اسم العميل Customer Name

- الصنف Item

- اجمالي الكمية Quantity


ملحوظة : للتعرف على أساسيات عمل Pivot Table قم بقراءة هذا الدرس : شرح pivot table



لعمل ما نريد سنقوم بعمل Pivot Table يأخد بيانات من جدول العملاء Customers وجدول الطلبيات Orders كما سنرى في الأجزاء التالية .



قم بتحديد جدول العملاء في الصفحة الأولى ثم اذهب إلى التبويب Insert واضغط على PivotTable

إنشاء علاقة بين جدولين في Excel




سيظهر مربع حوار , لاحظ أن برنامج Excel لم يقم بكتابة النطاق A1:C7 ولكن قام بكتابة Customers وهو اسم الجدول , اضغط على زر OK

إنشاء علاقة بين جدولين في Excel





سيتم ادراج Pivot Table في صفحة جديدة , الأن قمنا بعمل Pivot Table يأخذ بيانات من جدول العملاء فقط , ولكن نحن نريد بيانات من الجدول المرتبط به وهو جدول الطلبيات , لذا نريد اضافة جدول الطلبيات , لعمل ذلك اضغط على More Tables

إنشاء علاقة بين جدولين في Excel





ستظهر رسالة تخبرك أنه اذا كنت تريد اضافة جدول أخر سيتم عمل Pivot Table جديد يضم الجدولين , اضغط على زر Yes

إنشاء علاقة بين جدولين في Excel




بالفعل قام برنامج Excel بعمل Pivot Table جديد في صفحة جديدة يضم جدول العملاء Customers وجدول الطلبيات Orders

إنشاء علاقة بين جدولين في Excel




قم بفتح جدول العملاء واختر اضافة العمود Address والعمود Customer Name ثم افتح جدول الطلبيات واختر اضافة عمود Item وعمود Quantity كما هو مبين في الشكل التالي .

إنشاء علاقة بين جدولين في Excel




في الجانب الأيسر من الشاشة سترى النتيجة التي نريدها وهي عمل تلخيص لبيانات الجدول وفقا للتسلسل التالي :

- العنوان Address

- اسم العميل Customer Name

- الصنف Item

- اجمالي الكمية Quantity

إنشاء علاقة بين جدولين في Excel