لوحة المتصدرين

يُتيح Microsoft Excel إمكانية ربط البيانات من مصنفات متفرّقة عن طريق إنشاء مرجع خارجي External Reference (ويُدعى أيضًا بالرابط Link). يكون هذا الرابط فعّالا على الدوام بحيث يتم تحديث المصنّف التابع تلقائيًا كلّما قمت بإجراء تعديل على مصنّفات المصدر. وهذا ما يميّز ربط المصنّفات عن دمج المصنّفات المتعددة الذي كنا قد تحدّثنا عنه في درس سابق. ففي الأخير، لا يتم تحديث البيانات ما لم يتم إعادة الدمج. كيفية إنشاء مرجع خارجي (رابط) لكي نوضّح طريقة ربط المصنّفات المتفرّقة، سنستخدم الجدولين أدناه كمثال، وكل جدول محفوظ في مصنّف مستقل. يحتوي الجدول الأول على درجات مجموعة من الطلاب لاختبارات الفصل الأول الثلاثة: الجدول الثاني لا يختلف عن الأول، فهو يحتوي على نفس نوع البيانات لكن لاختبارات الفصل الثاني: المطلوب هو إيجاد متوسّط الدرجات للفصلين في مصنّف ثالث منفصل، مع إنشاء رابط للحفاظ على البيانات محدّثة: أولًا، نحدد الخلية التي سنجد فيها متوسّط درجات الفصلين للطالب الأول (B5 في هذا المثال) ثم نكتب الصيغة. نبدأ بعلامة = ثم الدالة Average: بعد ذلك نذهب إلى مصنّف الفصل الأول ونحدد متوسّط درجات الطالب الأول، نضع فاصلة منقوطة (;) ونذهب إلى مصنّف الفصل الثاني لنحدد متوسط درجات الطالب الأول: نلاحظ في شريط الصيغة أنّ البرنامج يشير إلى اسم المصنّف الأول واسم ورقة العمل ثم الخلية، ومن ثم اسم المصنّف الثاني، ورقة العمل والخلية. ولا يقتصر الربط على مصنّفين فقط، إذ يمكن ربط العدد المرغوب من المصنّفات. بعد كتابة الصيغة نضغط على مفتاح Enter للحصول على النتيجة. وبدل أن نكرر نفس الخطوة أعلاه لإيجاد المتوسط السنوي لجميع الطلّاب، سنستخدم التعبئة التلقائية لنسخ الصيغة إلى بقية خلايا العمود: لكن كما نلاحظ، تم تكرار نفس المتوسط السنوي (72) في جميع الخلايا، والسبب هو أنّ اكسل اتخذ الخلية E5 في مصنّف الفصل الأول والخلية E5 في مصنّف الفصل الثاني كمرجع مطلق. أي أنّه قام بنسخ الصيغة إلى بقية الخلايا لكن باستخدام نفس قيمة متوسط الفصل الأول والثاني للطالب الأول. لتصحيح ذلك، نتراجع عن أمر التعبئة التلقائية (Ctrl +Z) ثم نعدّل الصيغة بحذف علامة الدولار ($) المرافقة لاسم الخلية لتحويل المرجع المطلق إلى نسبي: بعد ذلك نعمل تعبئة تلقائية لملء بقية الخلايا: عندما تقوم بتغيير أي جزء من البيانات في مصنّفات المصدر (وهي ما تزال مفتوحة)، سيتم تحديث المصنّف التابع ليعكس هذه التحديثات تلقائيًا. أمّا إذا قمت بإغلاق جميع المصنّفات، ثم قمت لاحقًا بتعديل البيانات في أحد مصنّفات المصدر، أو كليهما، ستظهر رسالة تحذيرية عند فتح المصنّف التابع تخبرك بأنّ التحديث التلقائي غير مفعّل: يمكنك تفعيل التحديث التلقائي بالنقر على Enable Content إذا كانت التغييرات قد تم إجراؤها من قبلك أو من قبل أحد زملائك، وتريد أن ترى التحديث على المصنّف التابع. ملاحظة: عند غلق مصنّفات المصدر ستجد أنّ الصيغة في شريط الصيغة قد تغيّرت وأصبحت تشتمل أيضًا على امتداد المجلد الذي تُحفظ فيه مصنّفات المصدر: بإمكانك تفعيل خيار التحديث التلقائي للبيانات المرتبطة بشكل دائم بالذهاب إلى File> Options> Trust Center> Trust Center Settings: قم بتأشير الخيار Enable all Data Connections: مع ذلك، لا يُنصح بتفعيل هذا الخيار ويفضّل إبقاؤه كما هو لأغراض الأمان. إلّا في حال كان مصدر التغييرات موثوقًا دائمًا وكنت متأكدًا من أنّها غير ضارّة ببياناتك. في النهاية، الأمر راجع إلى تفضيلك الشخصي. تحديث الروابط وتعطيلها عندما تقوم بتغيير اسم مصنّفات المصدر أو تغيير المجلدات التي تُحفظ فيها، لن يكون بالإمكان تحديث البيانات في المصنّف التابع تلقائيًا، لأنّ الرابط لم يعد صالحًا، وأصبح اسم أو امتداد مصنّف المصدر مختلفًا عن الاسم والامتداد الموجود في الصيغة. لذلك يجب أن تقوم بإرشاد اكسل من جديد إلى مصنّفات المصدر لتحديث البيانات. على سبيل المثال، قمنا بتغيير اسم مصنّف "درجات الفصل الأول" إلى "درجات الفصل 1"، لذلك يجب أن نقوم بتحديث الروابط. نفتح المصنّف التابع، نذهب إلى تبويب File ثم ننقر على زر Edit Links to Files: ننقر على Change Source من مربّع الحوار Edit Links ثم نحدد المصنّف الذي قمنا بإعادة تسميته: من نفس مربّع الحوار يمكنك تعطيل الروابط بتحديدها الرابط ثم النقر على Break Link، وهذا في حال لم تعد بحاجة إلى وجود ارتباط بين المصنّفات: ستلاحظ تحوّل البيانات في المصنّف إلى مجرّد بيانات عادية واختفاء الصيغة من شريط الصيغة:

قد تتضمّن جداول البيانات أحيانًا صفوفًا أو أعمدة فارغة كنا قد تركناها سهوًا عند إنشاء الجدول. وفي أحيانٍ أخرى نضطر إلى التعامل مع الجداول التي أنشأها أحد الزملاء أو الأصدقاء ولم ينظّم الجدول بما مناسب، تاركًا الكثير من الفراغات فيه. إذا كان الجدول صغيرًا، وعدد الأعمدة/الصفوف الفارغة فيه قليلًا، يمكن حذفها يدويًا بتحديد الصف/العمود الذي نريد حذفه، النقر عليه بزر الفأرة الأيمن، واختيار حذف Delete: ومن ثم اختيار إزاحة الخلايا إلى الأعلى Shift cells up في حالة الصفوف، وإزاحة الخلايا إلى اليمين Shift cells right في حالة الأعمدة: أمّا إذا كان الجدول كبيرًا، فستصبح هذه العملية مُضنية وتستغرق وقتًا طويلًا. لذا من الأفضل استخدام طريقة أخرى أسهل وأسرع. غطيّنا في درس سابق خاصية Go to Special، وتعلّمنا كيفية استخدامها لتحديد خلايا بمواصفات معينة، كأن تكون فارغة مثلًا. وسنستخدم هذه الخاصية في هذا الدرس لتحديد الصفوف/الأعمدة التي نريد حذفها دفعة واحدة، ومن ثم تطبيق أمر الحذف عليها. ملاحظة: بما أنّ هذه العملية تنطوي على حذف صفوف، أعمدة، وخلايا، من الأفضل أن تحفظ نسخة أخرى من جدول البيانات تجنبًا لفقدان أيّ من بياناتك المهمة؛ من باب الاحتياط فقط. يجب أولًا أن نحدّد الجدول أو نطاق الخلايا الذي يحتوي على الصفوف الفارغة. في مثالنا هذا حدّدنا النطاق A1:D26: بعد ذلك ننقر على أمر بحث واستبدال Find & Replace من تبويب الصفحة الرئيسية Home، ونختار Go to Special من القائمة المنسدلة: سيُفتح مرّبع الحوار Go to Special، ومنه نحدّد الخيار الفراغات Blanks ثم ننقر على زر موافق OK: ستُحدَّد الصفوف الفارغة في الجدول وتُستثنى الخلايا التي تحتوي على قيم نصية أو رقمية أو صيغ: بعد تحديد الصفوف الفارغة، ننقر على السهم الصغير تحت الأمر حذف Delete من التبويب نفسه (الصفحة الرئيسية)، ثم نختار حذف صفوف الورقة Delete Sheet Rows من القائمة المنسدلة: سيتم حذف الصفوف المحددّة، وإزاحة الصفوف التي تقع تحتها إلى الأعلى تلقائيًا، وبذلك تحصل على جدول مرتب بصفوف متجاورة: إذا كان الصف الفارغ يقع على جهة اليمين من صف يحتوي على بيانات معينة، ونريد ملء الصف الفارغ بتلك البيانات، ففي هذه الحالة نحدّد نطاق الخلايا الذي يحتوي على الصفوف الفارغة: ثم نطبّق نفس الخطوات (Find & Replace> Go to Special> Blanks> Delete). لكن هذه المرّة نختار حذف خلايا Delete Cells بدلًا من حذف صفوف الورقة: سيظهر مربع الحوار Delete، ومنه يُحدَّد اتجاه إزالة الخلايا. الخيار المبدئي هو إزالة الخلايا إلى الأعلى، لكننا نرغب في جلب الخلايا على يسار الصفوف الفارغة ووضعها مكان تلك الفراغات، لذلك سنحدد الخيار Shift Cells Right وننقر على OK: انتبه عند تحديد الصفوف الفارغة إلى أنّ الجدول قد يحتوي أيضًا على خلايا فارغة مفردة ستُحدَّد أيضًا عند تطبيق الأمر Go to Special> Blanks، وبالتالي تُحذَف مع الصفوف الفارغة، مما يؤدي إلى تداخل البيانات وتغيير ترتيبها. إذا رغبت في الإبقاء على تلك الخلايا الفردية الفارغة، يمكنك أن تملأها بمحتوى معين قبل أن تطبق أمر التحديد والحذف. ومن ثم إعادة حذف محتواها بعد حذف الصفوف الفارغة. يمكن تطبيق الخطوات المذكورة أعلاه نفسها بالضبط إذا احتوى الجدول على أعمدة فارغة نريد إزالتها. وبذلك نكون قد تعلّمنا كيف أنّه بخطوات بسيطة يمكن أن نوفّر الكثير من الوقت والجهد عند حذف الصفوف أو الأعمدة الفارغة في جداول البيانات الكبيرة. إذا كانت لديكم أيّة أسئلة أو أفكار حول حذف الفارغات في جداول بيانات إكسل، شاركونا بها في صندوق التعليقات أو في قسم الأسئلة والأجوبة.

لقد غطينا في دروس سابقة كيفية تلخيص البيانات باستخدام المجاميع الفرعية، والجداول المحورية. وسنتعلّم اليوم كيفية تلخيص البيانات باستخدام خاصية الدمج. تُتيح خاصية Consolidate إمكانية دمج وتلخيص البيانات من عدة أوراق عمل أو مصنفات في ورقة عمل واحدة أو مصنّف واحد، وهي مفيدة في الكثير من الحالات. على سبيل المثال إذا كان لديك متجر وكانت بيانات المبيعات الخاصة بكل ربع محفوظة في مصنّفات منفصلة. عندما يحين وقت إعداد التقرير السنوي ستكون خاصية الدمج مناسبة لجمع البيانات من المصنفات المتعددة وتلخيصها في مصنف واحد. يمكن دمج البيانات بطريقتين رئيسيتين: حسب الموضع حسب الفئة وسنشرح كيفية الدمج بالطريقتين في هذا الدرس. دمج البيانات حسب الموضع تُستخدم هذه الطريقة إذا كانت البيانات الموزّعة على مصنفات متعددة (أو أوراق متعددة) لها نفس الترتيب ونفس أسماء الأعمدة والصفوف في جميع المصنفات أو أوراق العمل. مثال: لدينا هنا 4 مصنفات منفصلة، ويحتوي كل مصنف على المبيعات الخاصة بكل ربع. وكما نلاحظ، البيانات في جميع المصنفات لها نفس الترتيب ونفس عناوين الأعمدة والصفوف. سنقوم باستخدام خاصية الدمج لإيجاد مجموع المبيعات السنوي في مصنّف منفصل (لاحظ أنّ جميع الجداول تبدأ بالخلية C5 وتنتهي بالخلية F11): أولًا: نفتح جميع المصنفات الأربعة بالإضافة إلى مصنف خامس جديد (نسميّه الملخص) لإيجاد مجموع المبيعات السنوي. في المصنف الجديد، نقوم بإنشاء جدول بنفس البنية، يحتوي على نفس عدد الصفوف والأعمدة ونفس عناوينها. لنفترض أنّ المنتجات وأسعارها بقيت ثابتة طوال العام. نقوم بإدخالها في جدول الملخّص، ونترك عمودي "عدد الوحدات المباعة" و"المبيعات" فارغة: ما سنقوم به هو جمع عدد الوحدات المباعة للربع الأول + الثاني+ الثالث+ الرابع، لكل منتج ومن ثم نجد على ضوئها المبيعات لكل منتج، ثم مجموع المبيعات الكلي. ثانيًا: نحدد نطاق الخلايا الذي نريد عرض البيانات المدمجة فيه، وهو عمود "عدد الوحدات المباعة" في مصنّف الملخّص (E6:E11). من جديد أكرر، يجب أن تكون البيانات التي نريد دمجها موجودة في نفس نطاق الخلايا في مصنفات المصدر: ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate: من مربّع الحوار Consolidate، نحدد الدالة التي نريد استخدامها. هناك العديد من الخيارات، لكن ما يهمنا هنا هو دالة الجمع SUM لأننا نريد إيجاد مجموع "عدد الوحدات المباعة" في كل الأرباع: بعد ذلك، ننقر على زر السهم الصغير بجانب حقل مرجع Reference ثم نذهب إلى المصنف الأول (مبيعات الربع الأول) ونحدد نطاق الخلايا E6:E11 فيه: لاحظ الصيغة في حقل المرجع؛ يشير اكسل أولًا إلى اسم المصنّف، ثم اسم ورقة العمل، ثم نطاق الخلايا. انقر على زر Add لإضافة البيانات من المصنّف الثاني (مبيعات الربع الثاني)، وكرر نفس الخطوات على المصنفين الآخرين، وكما موضّح في الصورة أدناه: نتأكّد من تأشير مرّبع الخيار Create links to source data لإنشاء ارتباط مع مصدر البيانات، وهذا لأنّ بياناتنا موجودة على مصنفات مختلفة. أي لا حاجة لتأشير الخيار إذا كنت تريد دمج البيانات من أوراق عمل مختلفة في ورقة على نفس المصنّف. ننقر على OK، وسيقوم البرنامج بجمع كافة بيانات "عدد الوحدات المباعة" عبر جميع المصنفات. وإذا أردنا معرفة البيانات التفصيلية لكل رقم دون الرجوع إلى كل مصنّف، يوفّر لنا اكسل على مخطط تفصيلي على جانب المصنّف يمكننا استخدامه لإظهار (+) أو إخفاء البيانات (-) التي تم دمجها لنحصل على الرقم النهائي في كل خلية. وعند النقر على الرقم سيظهر في شريط الصيغة امتداد يمكننا الاستدلال به إلى اسم المصنف المصدر واسم ورقة العمل: دمج البيانات حسب الفئة تُستخدم هذه الطريقة إذا كانت البيانات التي تريد دمجها غير مرتبة بنفس الترتيب عبر جميع المصنفات أو أوراق العمل كأن يكون عدد عناصر الجدول أو ترتيبها مختلفًا. مع ذلك يجب أن يكون لها نفس أسماء الصفوف والأعمدة. مثال: لدينا هذه المرة 4 أوراق عمل (ويمكن أن تكون مصنفات أيضًا) تحتوي على 4 جداول. لكن كل جدول يحتوي على عدد/ترتيب مختلف من الفئات (المنتجات)، وقد توجد نفس الفئة مكررة في أكثر من جدول مع اختلاف بياناتها. ما نريده هو إيجاد مجموع المبيعات لكل منتج (أي لكل فئة) في ورقة عمل تمثّل الملخص. أولًا: نقوم بإنشاء ورقة عمل جديدة، ثم ننشئ جدول يبدأ من نفس الصف ونفس العمود لبقية الجداول في أوراق العمل المصدر (أي الخلية C5 في مثالنا): ثانيًا: يجب أن نحسب عدد الفئات الموجودة في جميع أوراق العمل، أو المصنفات، مع إهمال الفئات المكررة. في هذا المثال لدينا 6 فئات: ثلاجة، فرن كهربائي، طباخ، جهاز استقبال، تلفاز، مصباح كهربائي. بعد ذلك نذهب إلى ورقة العمل التي نريد دمج البيانات فيها ونحدد نطاق خلايا يتكون من 6 صفوف وحسب عدد أعمدة الجدول: ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate. سيظهر لنا نفس مربع الحوار الذي ظهر في الطريقة السابقة. نحدد الدالة التي نريد استخدامها من حقل Function ثم ننقر على زر السهم الصغير في حقل Reference: نذهب إلى الورقة الأولى ونحدد نطاق البيانات الأول (C6:F11 في مثالنا)، ثم الورقة الثانية ونحدد نطاق البيانات الثاني (C6:F11 في مثالنا) وهكذا: بعد ذلك نحدد أين توجد الفئات التي نريد دمجها سواء كانت في الصف العلوي Top Raw أو في العمود الأيمن Right Columnأو في كليهما. سنحدد العمود الأيمن لأنّ المنتجات تقع في العمود الأيمن في هذا المثال ثم ننقر على OK: سيتم إيجاد مجموع المبيعات لكل منتج في كل الأرباع حتّى إن كانت مواقعها مختلفة عبر أوراق العمل.