هل تريد تنظيم كافة أوراق العمل في مصنف Excel الخاص بك؟ حاول إنشاء جدول محتويات. فهو يجعل من السهل العثور على أوراق محددة، خاصة إذا كان ملف Excel الخاص بك يحتوي على المئات منها. لسوء الحظ، لا يحتوي برنامج Excel على ميزة النقرة الواحدة لإنشاء جدول محتويات، ولكن هناك طريقة!
لماذا يجب عليك إضافة جدول محتويات إلى Excel
ماذا ستفعل إذا كان لديك مئات الأوراق في مصنف Excel وتحتاج إلى العثور على ورقة محددة لتحديث البيانات أو تعديلها؟ قد يستغرق البحث يدويًا الكثير من الوقت. ولكن، إذا قمت بإنشاء جدول محتويات، فيمكنك التنقل بسهولة عبر المصنف والعثور بسرعة على الورقة التي تحتاجها.
باعتباري كاتب محتوى لتحسين محركات البحث، أعمل أحيانًا مع ملفات Excel الكبيرة التي تحتوي على بيانات الكلمات الرئيسية وأديرها. باستخدام جدول المحتويات، يمكنني الانتقال بسهولة إلى ورقة Excel المحددة حيث يتم تخزين المعلومات المطلوبة، مما يوفر الكثير من الوقت والجهد. كما أنه يلغي الحاجة إلى التمرير عبر عدد لا يحصى من الأوراق وعلامات التبويب.
يساعدك جدول المحتويات في الحفاظ على تخطيط منظم من خلال تنظيم أوراق العمل والأقسام ذات الصلة بشكل منطقي، مما يؤدي إلى تحسين تجربة المستخدم بشكل عام. كما أنه يسهل على أعضاء فريقك العثور على أقسام محددة للإدخال والمراجعة. بالإضافة إلى ذلك، يمكنك تقليل الأخطاء عن طريق تقليل فرص تعديل البيانات غير ذات الصلة عن طريق الخطأ.
سأستخدم Microsoft Excel 365 لهذا العرض التوضيحي. يحتوي المصنف الخاص بي بالفعل على أربع أوراق عمل: الفريق أ، والفريق ب، والفريق ج، والفريق د.
إضافة جدول المحتويات إلى Excel يدويًا
لإنشاء جدول محتويات يدويًا، حدد أولاً المكان الذي تريد وضعه فيه. يوصى بإنشاء ورقة عمل جديدة لجدول المحتويات لتسهيل تحديد موقعه وإدارته.
لإنشاء ورقة جديدة، انقر بزر الماوس الأيمن على أي اسم ورقة عمل موجودة وانقر على “إدراج”، ثم حدد “ورقة عمل”. وبدلاً من ذلك، يمكنك الضغط على Shift+Alt+F1.
بعد ذلك، حدد الخلية التي تنوي إضافة الارتباط التشعبي إليها، مثل B5 (أو أي خلية تفضلها).
بعد تحديد الخلية، انتقل إلى علامة التبويب “إدراج”، وانقر فوق العنصر المنسدل “رابط”، وحدد خيار “إدراج رابط” لعرض مربع الحوار “إدراج ارتباط تشعبي”. يمكنك أيضًا الوصول إليه باستخدام الاختصار Ctrl + K.
انتقل إلى قسم “وضع في هذا المستند”، وحدد الورقة المفضلة لديك، ثم اكتب النص الذي تريد عرضه للارتباط التشعبي. بعد القيام بذلك، اضغط على “موافق” لإدراج الرابط.
كرر العملية للأوراق الأخرى.
هذا كل شيء! الآن لديك روابط قابلة للنقر ستنقلك مباشرةً إلى الأوراق المقابلة عند النقر عليها.
استخدم وظيفة/صيغة الارتباط التشعبي
هناك طريقة أخرى لإضافة جدول محتويات يدويًا في Excel وهي استخدام وظيفة الارتباط التشعبي. في هذه الطريقة، تحتاج إلى كتابة كافة أسماء أوراقك وإضافة صيغ الارتباط التشعبي لكل منها على حدة.
للبدء، اختر الخلية التي تريد أن يظهر فيها جدول المحتويات وأدخل الصيغة التالية:
=HYPERLINK("#'WorkSheetName'!A1", "FriendlyName")هنا، “WorkSheetName” هو اسم ورقة العمل التي تريد إنشاء ارتباط لها. يعرّف الرمز “#” ورقة العمل، وتمثل علامة التعجب “!A1” موقع الخلية في ورقة العمل المستهدفة. وأخيرًا، يمثل المتغير “FriendlyName” الاسم الذي سيتم عرضه في جدول المحتويات.
كرر هذه العملية للأوراق الأخرى باستخدام نفس الصيغة.
إنشاء جدول المحتويات تلقائيًا
يمكنك إنشاء جدول محتويات تلقائيًا باستخدام أداة Power Query في Excel. باستخدام هذه الأداة، يمكنك إدراج مئات الأوراق في ورقة معينة ببضع نقرات فقط وإنشاء ارتباطات تشعبية ستنقلك مباشرة إلى كل ورقة خاصة بها عند النقر عليها.
للبدء، انتقل إلى علامة التبويب “البيانات” في Excel. انقر فوق “الحصول على البيانات”، ثم حدد “من ملف” واضغط على خيار “من مصنف Excel”.
سيظهر مربع حوار حيث يمكنك تحديد المصنف الذي تعمل فيه حاليًا. بمجرد تحديده، انقر فوق “استيراد”.
بدلاً من اختيار جدول أو ورقة معينة من القائمة التي تظهر، حدد اسم المصنف نفسه. ثم انقر على “تحويل البيانات”.
سترى الآن قائمة بجميع الأوراق والجداول والأسماء المحددة داخل المصنف. وبما أننا نريد أسماء الأوراق فقط، فقم بتطبيق مرشح لإظهار الأوراق من خيار “النوع” فقط.
بعد ذلك، انقر بزر الماوس الأيمن على عمود “الاسم” (الذي يحتوي على أسماء الأوراق) وحدد “إزالة الأعمدة الأخرى”. تترك لك هذه الخطوة عمودًا واحدًا فقط يسرد جميع أسماء الورقة.
يمكنك أيضًا إعادة تسمية رأسك إلى أي اسم مفضل. بعد إجراء هذه التغييرات، انقر فوق خيار “Close & Load To”.
حدد “ورقة العمل الموجودة” وأدخل الخلية التي تريد أن تبدأ القائمة فيها (على سبيل المثال، الخلية A1 أو B5).
سيكون لديك الآن مجموعة من أسماء الأوراق الموجودة في المصنف الخاص بك.
الخطوة الأخيرة هي إنشاء ارتباطات تشعبية لأسماء الأوراق باستخدام صيغة الارتباط التشعبي. عند إنشاء ارتباط تشعبي للورقة الأولى والضغط على Enter، سيتم تحديث كافة أعمدة الورقة تلقائيًا باستخدام الارتباطات التشعبية الخاصة بها. إذا لم يكن الأمر كذلك، فيمكنك ببساطة سحب مقبض التعبئة لتطبيق الصيغة على كافة الصفوف في جدول المحتويات الخاص بك.
يمكنك إنشاء ارتباط تشعبي باستخدام الصيغة التالية:
=HYPERLINK("#'"&(@WorkSheetName)&"'!A1", (@FriendlyName))
الآن، إذا قمت بالنقر فوق أي من الارتباطات التشعبية، فسوف يأخذك مباشرة إلى الورقة المقابلة في المصنف الخاص بك.
ورقة التحديث التلقائي
إحدى الفوائد الرائعة لاستخدام Power Query هي أنه يمكنك بسهولة تحديث جدول المحتويات الخاص بك كلما قمت بإضافة أوراق أو إزالتها من المصنف الخاص بك.
على سبيل المثال، قمت بإضافة ورقة جديدة إلى المصنف الخاص بي وحفظتها باسم “الفريق E.” الآن أريد أن تظهر هذه الورقة في جدول المحتويات مع الارتباط التشعبي الخاص بها.
لتحديث المخطط التفصيلي، ما عليك سوى الرجوع إلى الورقة الرئيسية والنقر نقرًا مزدوجًا فوق استعلام Excel “جدول المحتويات” الذي يتم عرضه على يمين المصنف الخاص بك.
في القائمة المفتوحة، انقر فوق “تحديث المعاينة” لتحديث جدول المحتويات الخاص بك.
ومع ذلك، عند تحديثه، سيتم أيضًا تضمين أي جدول أو اسم محدد تمت إضافته مؤخرًا في جدول المحتويات المحدث. لتصفيته، انتقل إلى خيار “الصفوف التي تمت تصفيتها”، وانقر على القائمة المنسدلة “النوع”، وحدد “الورقة” فقط.
هذا كل شيء! سيقوم Power Query بتحديث جدول المحتويات تلقائيًا ويتضمن الورقة المضافة حديثًا.
استخدم البرنامج النصي لرمز VBA
إذا كان حجم المصنف الخاص بك كبيرًا، فيمكنك أيضًا استخدام ماكرو VBA لأتمتة العملية من خلال التكرار عبر كافة الأوراق، وإنشاء إدخال قائمة لكل منها، وإدراج ارتباط تشعبي.
لإضافة كود VBA، تحتاج إلى علامة التبويب المطور. إذا لم تتمكن من الوصول إليه من قبل، فلن يكون مرئيًا على “الشريط”. ومع ذلك، يمكنك تنشيطه بالانتقال إلى ملف > خيارات > تخصيص الشريط وتشغيل خيار “المطور”.
بعد ذلك، انتقل إلى علامة التبويب “المطور” وحدد خيار “Visual Basic” لفتح محرر VBA، أو ببساطة استخدم الاختصار Alt+F11.
لإدراج وحدة نمطية جديدة، انقر فوق إدراج > وحدة نمطية.
وأخيرًا، قم بلصق كود VBA المقدم من دينيس فالنتين في نافذة المحرر وانقر على “تشغيل” أو اضغط على F5 لتنفيذ التعليمات البرمجية.
وهذا كل شيء! لقد قمت بإنشاء جدول محتويات ورقة عمل لمصنف Excel الخاص بك.
أنشئ رابطًا للرجوع إلى ورقة جدول المحتويات
إذا كان المصنف الخاص بك يحتوي على العديد من الأوراق، فقد يكون من المفيد إضافة ارتباط تشعبي على كل ورقة يعيدك إلى صفحة جدول المحتويات الرئيسية.
للبدء، افتح الورقة التي تريد إضافة رابط الإرجاع فيها واختر الخلية التي تريد عرض الرابط فيها. بعد ذلك، انتقل إلى إدراج > رابط > ضع في هذا المستند. حدد الورقة الرئيسية واكتب “صفحة جدول المحتويات” كنص العرض.
لقد قمت الآن بإنشاء رابط، عند النقر عليه، يعيدك إلى صفحة جدول المحتوى الرئيسية. يمكنك بسهولة نسخ هذا الرابط ولصقه على جميع الأوراق الأخرى.
سواء كنت تتعامل مع أوراق قليلة أو مصنفًا كبيرًا، ستساعدك هذه الطرق المتوفرة في إنشاء جدول محتويات بكفاءة.