العمل مع الجداول المرتبطة في Microsoft Excel

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

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

إنشاء الجداول المرتبطة

بادئ ذي بدء ، دعنا نتناول السؤال ، وبأي الطرق يمكن إنشاء رابط بين النطاقات الجدولة المختلفة.

الأسلوب 1: الربط المباشر من الجداول بواسطة الصيغة

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

لنرى كيف يمكن أن يشكل المثال رابطًا عن طريق الربط المباشر. لدينا اثنين من الجداول على ورقتين. في جدول واحد ، يتم حساب جدول الرواتب باستخدام صيغة بضرب معدل الموظفين من خلال معامل واحد للجميع.

يوجد في الورقة الثانية نطاق جدولي ، توجد فيه قائمة بالموظفين الذين يتقاضون رواتبهم. يتم تقديم قائمة الموظفين في كلتا الحالتين في أمر واحد.

فمن الضروري أن تجعل بحيث يتم رسم البيانات على معدلات من الورقة الثانية في الخلايا المقابلة من الأول.

1. في أول ورقة ، حدد الخلية الأولى لعمود "الرهان" . نضع فيه علامة "=" . بعد ذلك ، انقر فوق العلامة "ورقة 2" ، الموجودة على الجانب الأيسر من واجهة Excel فوق شريط الحالة.
2. ينتقل إلى المنطقة الثانية من المستند. نضغط على أول خلية في عمود "عروض الأسعار" . ثم انقر فوق الزر Enter الموجود على لوحة المفاتيح لإدخال البيانات في الخلية حيث تم تعيين علامة المساواة سابقًا.
3. ثم يحدث الانتقال التلقائي إلى الورقة الأولى. كما ترون ، يتم سحب قيمة معدل الموظف الأول من الجدول الثاني في الخلية المقابلة. وضع المؤشر على الخلية التي تحتوي على الرهان ، نرى أن الصيغة المعتادة تستخدم لعرض البيانات على الشاشة. ولكن قبل إحداثيات الخلية ، التي يتم إخراج البيانات منها ، يقف التعبير "Sheet2!" ، والذي يشير إلى اسم منطقة المستند حيث توجد. الصيغة العامة في حالتنا تبدو كالتالي:

   =Лист2!B2

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

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

5. تم سحب جميع البيانات الواردة من عمود مماثل في الورقة 2 إلى الجدول في الورقة 1 . إذا قمت بتغيير البيانات الموجودة في الورقة 2 ، فسيتم تغييرها آليًا على الورقة الأولى.

الطريقة 2: استخدام الارتباط INDEX-MAP

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

1. حدد العنصر الأول من عمود "الرهان" . انتقل إلى " معالج الدالة" عن طريق النقر فوق رمز "إدراج الدالة" .
2. في معالج الدالة في المجموعة "المراجع والمصفوفات" نجد واختار اسم "INDEX" .
3. هذا المشغل له شكلين: نموذج للعمل مع المصفوفات والمرجع. في حالتنا ، يكون الخيار الأول مطلوبًا ، لذلك في نافذة تحديد النموذج التالية التي تفتح ، حددها وانقر على زر "موافق" .
4. تم إطلاق نافذة الحجة الخاصة بـ INDEX . مهمة هذه الوظيفة هي إخراج قيمة موجودة في النطاق المحدد في السطر مع الرقم المحدد. الصيغة العامة لمشغل INDEX هي كما يلي:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   "Array" هي عبارة عن وسيطة تحتوي على عنوان النطاق الذي سنقوم من خلاله باسترداد المعلومات حسب عدد السلسلة المحددة.

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

   "رقم العمود" هو وسيط اختياري. لحل مشكلتنا على وجه التحديد ، لن نستخدمها ، وبالتالي ليس من الضروري وصف جوهرها بشكل منفصل.

   نضع المؤشر في حقل "المصفوفة" . بعد ذلك ، انتقل إلى " الورقة 2" ، واضغط على زر الماوس الأيسر ، وحدد محتويات عمود "الرهان" بأكمله.

5. بعد عرض الإحداثيات في نافذة المشغل ، ضع المؤشر في حقل "رقم الخط" . سنقوم بإخراج هذه الحجة بمساعدة مشغل LIST . لذلك ، انقر فوق المثلث الموجود على يسار سطر الوظيفة. يتم عرض قائمة بالعوامل المستخدمة حديثًا. إذا عثرت على الاسم "SEARCH" ، فيمكنك النقر عليه. خلاف ذلك ، انقر فوق العنصر الأخير في القائمة - "وظائف أخرى ..." .
6. يبدأ إطار المعالج القياسي. نمرر فيها إلى نفس المجموعة "المراجع والمصفوفات" . في هذا الوقت في القائمة اختر البند "بحث" . انقر فوق "موافق" زر.
7. تنشيط نافذة OPERATOR للوسائط منشط . الغرض من الوظيفة المحددة هو عرض رقم القيمة في صفيف معين حسب اسمها. وبفضل هذه الميزة ، نقوم بحساب رقم السطر لقيمة محددة للدالة INDEX . تكون صيغة MATCH كما يلي:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

   "المصفوفة الممسوحة ضوئيًا" هي عبارة عن وسيطة تمثل مرجعًا لمصفوفة يتم فيها البحث عن القيمة المحددة لتحديد موضعها. سيؤدي هذا الدور فينا تنفيذ عنوان عمود " الاسم" في الورقة 2 .

   "نوع المطابقة" عبارة عن وسيطة اختيارية ، ولكن بخلاف العبارة السابقة ، ستكون هناك حاجة إلى هذه الوسيطة الاختيارية. يشير إلى كيفية مطابقة المشغل لقيمة البحث مع المصفوفة. يمكن أن تحتوي هذه الوسيطة على واحدة من ثلاث قيم: -1 ؛ 0 1 . للصفائف غير مرتبة ، اختر الخيار "0" . هذا الخيار مناسب لحالنا.

   لذا ، دعونا نبدأ في ملء حقول نافذة الحجج. نضع المؤشر في الحقل "قيمة البحث" ، انقر على أول خلية في العمود "الاسم" في الورقة 1 .

8. بعد عرض الإحداثيات ، قمنا بتعيين المؤشر في حقل "مجموعة الممسوحة ضوئيًا" وانتقل إلى تصنيف "الورقة 2" ، الموجود أسفل نافذة Excel أعلى سطر الحالة. ثبّت زر الماوس الأيسر وحدد مع المؤشر جميع الخلايا في عمود "الاسم" .
9. بعد عرض إحداثياتهم في حقل "مجموعة الممسوحة ضوئيًا" ، انتقل إلى حقل "نوع المطابقة" واضبط الرقم "0" من لوحة المفاتيح هناك. بعد ذلك ، نعود إلى الحقل "صفيف المشاهدة" مرة أخرى. الأمر هو أننا سنقوم بنسخ الصيغة ، كما فعلنا في الطريقة السابقة. ستتغير العناوين ، لكننا نحتاج إلى إصلاح إحداثيات الصفيف الذي نقوم بمسحه. لا ينبغي أن يتحول. حدد الإحداثيات مع المؤشر واضغط على المفتاح الوظيفي F4 . كما ترون ، ظهرت علامة الدولار قبل الإحداثيات ، مما يعني أن الإشارة من النسبية إلى المطلقة. ثم انقر على زر "موافق" .
10. يتم عرض النتيجة في الخلية الأولى من عمود "الرهان" . ولكن قبل عمل نسخة ، نحتاج إلى إصلاح منطقة أخرى ، وهي الحجة الأولى لوظيفة INDEX . للقيام بذلك ، حدد عنصر العمود الذي يحتوي على الصيغة ، وانتقل إلى صف الصيغة. حدد الوسيطة الأولى لمشغل INDEX ( B2: B7 ) وانقر فوق الزر F4 . كما ترون ، ظهرت علامة الدولار بالقرب من الإحداثيات المحددة. نضغط على مفتاح Enter . بشكل عام ، أخذت الصيغة على الشكل التالي:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. يمكنك الآن النسخ باستخدام مقبض التعبئة. نسميها بنفس الطريقة التي تحدثنا عنها سابقًا ، ونوسعها إلى نهاية نطاق الجدول.
12. كما نرى ، على الرغم من حقيقة أن ترتيب الصفوف للجدولين المرتبطين لا يتطابق ، ومع ذلك ، يتم تشديد جميع القيم وفقا لألقاب الموظفين. وقد تحقق ذلك من خلال استخدام مجموعة من العوامل INDEX - POSITION .

اقرأ أيضا:
وظيفة INDEX في Excel
الدالة EXPLORATION في Excel

الطريقة الثالثة: تنفيذ العمليات الحسابية على البيانات ذات الصلة

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

دعونا نرى كيف يتم ذلك في الممارسة. دعونا نجعل ذلك على ورقة 3 سيتم عرض البيانات العامة للأجور على المؤسسة دون انهيار من قبل الموظفين. لهذا ، سيتم سحب معدلات الموظفين من ورقة 2 ، المضافة (باستخدام الدالة SUM ) وضربها بالمعامل باستخدام الصيغة.

1. حدد الخلية حيث سيتم عرض نتيجة حساب الأجور على ورقة 3 . انقر فوق "إدراج وظيفة" زر.
2. يجب أن تبدأ نافذة المعالج . نمر إلى المجموعة "الرياضية" واختيار اسم "SUMM" هناك . ثم انقر على زر "موافق" .
3. يتم نقل الدالة SUM إلى نافذة الوسيطة ، والتي تستخدم لحساب مجموع الأرقام المحددة. يحتوي على بناء الجملة التالي:

   =СУММ(число1;число2;…)

   تتوافق الحقول في النافذة مع وسيطات الدالة المحددة. على الرغم من أن عددهم يمكن أن يصل إلى 255 قطعة ، ولكن لغرضنا سيكون كافيا واحد فقط. نضع المؤشر في الحقل "Number1" . انقر على العلامة "ورقة 2" أعلى سطر الحالة.

4. بعد الانتقال إلى القسم المطلوب من الكتاب ، حدد العمود الذي يجب جمعه. نحن نفعل ذلك مع المؤشر ، مع الاستمرار على زر الماوس الأيسر. كما ترى ، يتم عرض إحداثيات المنطقة المحددة على الفور في حقل نافذة الحجج. ثم انقر على زر "موافق" .
5. بعد ذلك ، ننتقل تلقائيًا إلى الورقة 1 . كما ترون ، يتم عرض المبلغ الإجمالي لأجر الموظف بالفعل في العنصر المقابل.
6. لكن هذا ليس كل شيء. كما نتذكر ، يتم احتساب الراتب عن طريق ضرب معدل بواسطة معامل. لذلك ، نختار مرة أخرى الخلية التي يوجد فيها التجميع. بعد ذلك انتقل إلى شريط الصيغة. نضيف رمز الضرب ( * ) إلى الصيغة الموجودة فيه ، ثم نضغط على العنصر الذي يوجد به معامل المعامل. لإجراء الحساب ، انقر فوق مفتاح Enter على لوحة المفاتيح. كما ترى ، يحسب البرنامج الراتب الإجمالي للمؤسسة.
7. العودة إلى ورقة 2 وتغيير معدل أي موظف.
8. بعد ذلك ، ننتقل إلى الصفحة بالمبلغ الإجمالي. كما ترون ، بسبب التغييرات في الجدول المرتبط ، تم تلقائيا إعادة حساب نتيجة الراتب الإجمالي.

الطريقة الرابعة: إدراج خاص

يمكنك أيضًا ربط صفائف الجدول إلى Excel باستخدام إدراج خاص.

1. حدد القيم التي تريد "تشديد" في جدول آخر. في حالتنا ، هذا هو نطاق عمود "الرهان" في الورقة 2 . انقر على الجزء المحدد بزر الفأرة الأيمن. في القائمة المفتوحة ، حدد العنصر "نسخ" . مزيج بديل هو Ctrl + C. بعد ذلك ، انتقل إلى الورقة 1 .
2. الانتقال إلى المساحة المطلوبة من الكتاب ، حدد الخلايا التي تريد سحب القيم فيها. في حالتنا ، هذا هو العمود "الرهان" . انقر على الجزء المحدد بزر الفأرة الأيمن. في قائمة السياق في مربع "معلمات الإدراج" ، انقر فوق رمز "إدراج ارتباط" .

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

3. بعد ذلك ، يتم فتح نافذة الإدراج الخاصة. انقر فوق الزر "إدراج ارتباط" في الزاوية السفلية اليسرى من الخلية.
4. أيا كان الخيار الذي تختاره ، سيتم إدراج القيم من صف جدول واحد في الآخر. إذا قمت بتغيير البيانات في المصدر ، فسيتم تغييرها تلقائيًا أيضًا في النطاق المدرج.

الدرس: إدراج خاص في Excel

الطريقة الخامسة: العلاقة بين الجداول في عدة كتب

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

1. حدد نطاق البيانات الذي تريد نقله إلى كتاب آخر. انقر عليه بزر الفأرة الأيمن وحدد العنصر "نسخ" في القائمة المفتوحة.
2. ثم ننتقل إلى الكتاب الذي ستحتاج إلى إدخال هذه البيانات فيه. حدد النطاق المطلوب. انقر بالزر الأيمن للفأرة. في قائمة السياق في المجموعة "خيارات الإدراج" ، حدد العنصر "لصق الارتباط" .
3. بعد ذلك ، سيتم إدراج القيم. إذا قمت بتغيير البيانات في الكتاب المصدر ، سيقوم مصفوفة الجدول من المصنف بسحبها تلقائيًا. وليس من الضروري أن يكون كلا الكتابين مفتوحين لهذا. يكفي فتح مصنف واحد فقط ، وسوف يقوم تلقائيًا بسحب البيانات من المستند المرتبط المغلق إذا كان قد تم تغييره مسبقًا.

ولكن تجدر الإشارة إلى أنه في هذه الحالة سيتم إدخال في شكل مجموعة غير قابلة للتغيير. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные» . Щелкаем по значку «Изменить связи» , который расположен на ленте в блоке инструментов «Подключения» . Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь» .
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи» .
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать» . Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C .
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения» , которая размещена в группе инструментов «Параметры вставки» .
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

• ← طرق لمقارنة الجداول في Microsoft Excel
• المربع في Microsoft Excel →