يُعرف برنامج Excel بأنه برنامج يسمح لك بإنشاء جداول بيانات معقدة. ومع ذلك، فإن بعض الأشخاص أقل دراية بنوع خاص من المراجع التي تأتي مع هذه الجداول: المراجع المنظمة.
يمكن استخدام المراجع المنظمة في Excel 2016 أو الإصدارات الأحدث، بما في ذلك Excel 365.
ما هو المرجع المنظم؟
تربط مراجع الخلايا المباشرة في Excel خلية بأخرى باستخدام تسميات رؤوس الأعمدة والصفوف. على سبيل المثال، الخلية A1 موجودة في العمود الأول والصف الأول، بينما الخلية C10 موجودة في العمود الثالث والصف العاشر.
من ناحية أخرى، بدلاً من استخدام تسميات رؤوس الأعمدة والصفوف، تستخدم المراجع المنظمة أسماء الجداول والأعمدة. في المثال أدناه،
=SUM((@(Daily profit))*7)
يأخذ القيم الموجودة في كل خلية في العمود المسمى “الربح اليومي” ويضربها في سبعة.
استخدام المراجع المنظمة داخل الجدول
لاستخدام المراجع المنظمة داخل جدولك، كما في المثال أعلاه، تحتاج أولاً إلى إضافة بعض البيانات إلى ورقة عمل Excel، مع تضمين أسماء الأعمدة في الجزء العلوي من هذه البيانات. إذا لم تقم بإضافة أسماء الأعمدة، فسيتم تعيين Excel افتراضيًا على العمود 1 والعمود 2 وما إلى ذلك، وهو أمر أقل وضوحًا عندما تريد استخدام الرؤوس في الصيغ لاحقًا.
قبل استخدام أي من البيانات لإنشاء حسابات، حدد خلية ضمن بياناتك، ثم انقر فوق “تنسيق كجدول” في علامة التبويب “الصفحة الرئيسية” على الشريط. هناك، حدد التصميم الذي يناسبك بشكل أفضل.
الآن، استخدم مربع الحوار “إنشاء جدول” للتحقق من تحديد جميع بياناتك، وحدد مربع الاختيار “يحتوي جدولي على رؤوس”، ثم انقر فوق “موافق”.
الآن بعد أن أصبحت بياناتك موجودة في جدول Excel منسق، أصبحت جاهزًا لاستخدام المراجع المنظمة.
في المثال الخاص بي، سأقوم بتحديد الخلية C2 (الخلية الأولى التي أريد إنشاء عملية حسابية فيها)، واكتب:
=SUM(
الآن، سأقوم بالنقر فوق الخلية الأولى التي تحتوي على البيانات في عمود “الربح اليومي”، وسيقوم برنامج Excel بإدراج مرجع منظم لهذا العمود.
=SUM((@(Daily profit))
الأقواس المربعة هي طريقة Excel للإشارة إلى أنه يستخدم مرجعًا منظمًا، ويعني الرمز @ (المعروف أيضًا باسم عامل التقاطع) أنه سيتم تطبيق الحساب على التوالي على كل صف داخل الجدول.
أخيرًا، أحتاج إلى مضاعفة القيم الموجودة في عمود “الربح اليومي” في سبعة للحصول على الربح الأسبوعي، وإغلاق الأقواس الدائرية.
=SUM((@(Daily profit))*7)
عندما أضغط على Enter، سيتم نسخ الصيغة تلقائيًا إلى الخلايا المتبقية في عمود “الربح الأسبوعي”.
إذا قمت بإزالة الرمز @ يدويًا، فإن الحساب في عمود “الربح الأسبوعي” سيجمع عمود “الربح اليومي” بالكامل، ويضرب هذا الإجمالي في سبعة.
استخدام المراجع المنظمة خارج الجدول
تُستخدم المراجع المصنفة أيضًا ضمن الصيغ خارج جدول Excel والتي تشير إلى بيانات الجدول. في هذا المثال، سأستخدم صيغة XLOOKUP لتوليد ربح أسبوعي في الخلية E4 استنادًا إلى الموقع المذكور في الخلية E2.
الخطوة الأولى هي تسمية الجدول. وإلا، فسيقوم Excel بتسمية بياناتي باسم جدول 1، الأمر الذي قد يكون مربكًا إذا كان هناك عدة جداول في المصنف الخاص بي. في الواقع، من الجيد أن تعتاد على تسمية جداولك كلما قمت بإنشائها في Excel، حتى إذا كنت لا تنوي الإشارة إليها في أي مكان آخر في جدول البيانات الخاص بك.
للقيام بذلك، سأقوم بتحديد أي خلية داخل الجدول، وفتح علامة التبويب “تصميم الجدول” على الشريط، واكتب الاسم المناسب في حقل اسم الجدول. في حالتي، سأطلق على طاولتي اسم “الأرباح”.
هناك قواعد معينة يجب عليك اتباعها عند تسمية الجدول الخاص بك:
يجب أن يبدأ اسم الجدول الخاص بك بحرف أو شرطة سفلية (_) أو شرطة مائلة عكسية (\).
يمكن أن يتكون باقي اسم الجدول من مزيج يصل إلى 255 حرفًا ورقمًا ونقطة وشرطات سفلية.
لا يمكن تسمية الجدول الخاص بك بـ “C” أو “c” أو “R” أو “r” لأنها تُستخدم لأغراض أخرى في Excel.
لا يمكن أيضًا أن يكون جدولك مماثلاً لمرجع الخلية، مثل A1 أو $A$1.
حاول أن تجعل اسم الجدول مكونًا من كلمة واحدة حتى يسهل الرجوع إليه. ومع ذلك، إذا كنت يجب إذا استخدمت أكثر من كلمة واحدة، استخدم شرطة سفلية (وليس مسافة) بين كل كلمة.
تأكد من عدم استخدام اسم الجدول في أي مكان آخر في المصنف الخاص بك.
الآن، في الخلية E4، يمكنني بدء صيغة XLOOKUP الخاصة بي عن طريق كتابة:
=XLOOKUP(E2
بعد ذلك، أحتاج إلى تحديد مصفوفة البحث، وهي الخلايا من A2 إلى A9. لاحظ كيف تقوم صيغتي بتحويل هذا تلقائيًا إلى مرجع منظم، حيث يكون “الأرباح” هو اسم الجدول الذي قمت بإنشائه مسبقًا و”الموقع” هو رأس العمود.
=XLOOKUP(E2,Profits(Site),
أخيرًا، يمكنني تحديد مصفوفة الإرجاع، وهي الخلايا من C2 إلى C9، وإغلاق الأقواس الدائرية. مرة أخرى، يقوم Excel بتحويل هذا إلى مرجع منظم بالنسبة لي.
=XLOOKUP(E2,Profits(Site),Profits(Weekly profit))
عندما أضغط على Enter، يتم استرداد الربح الأسبوعي بنجاح من الجدول الخاص بي.
لماذا استخدام المراجع المنظمة؟
ربما تفكر، “ما الفائدة؟” حسنًا، هناك فوائد لا حصر لها لاستخدام المراجع المنظمة بدلاً من المراجع المباشرة عند التعامل مع الجداول في Excel.
أولاً، إن إنشاء المراجع المنظمة وقراءتها أسهل من إنشاء المراجع المباشرة. ونتيجة لذلك، يمكنني فهم الغرض من الصيغ الخاصة بي من خلال إلقاء نظرة سريعة عليها فقط، ويمكنني تحليل صيغتي بسهولة إذا قام Excel بإرجاع خطأ.
ثانيًا، إذا أردت إضافة صف إضافي إلى الجدول الخاص بي عن طريق النقر على المقبض الموجود في الزاوية اليمنى السفلية وسحبه (المسمى “A” في لقطة الشاشة أدناه)، ثم ملء الصف الإضافي ببيانات إضافية، فإن المراجع المنظمة سيتم تطبيق “البيانات” التي استخدمتها بالفعل في جدول البيانات الخاص بي تلقائيًا على هذه البيانات الجديدة.
في هذا المثال، قمت بإضافة بيانات لموقع إضافي (الصف 10). لم يقتصر الأمر على احتساب الربح الأسبوعي تلقائيًا عندما أضفت الربح اليومي، بل قمت أيضًا بتغيير مرجع الموقع في الخلية E2 إلى الموقع I، وتم استرداد البيانات بشكل صحيح. لو كنت قد استخدمت مراجع مباشرة، لاضطررت إلى إجراء العديد من التعديلات اليدوية لتحقيق نفس النتيجة.
ثالثًا، إذا أردت إضافة عمود آخر بين عمودي “الربح اليومي” و”الربح الأسبوعي”، فيمكنني الاطمئنان إلى معرفة أن أي إشارات إلى أعمدتي الحالية ستظل آمنة، حيث قمت بالإشارة إلى أسماء الأعمدة بدلاً من الحرف المرتبط بكل عمود.
وأخيرا، المراجع المنظمة ديناميكية. إذا قمت بتغيير اسم أحد أعمدة الجدول، فسيتم تحديث جميع المراجع المنظمة ذات الصلة وفقًا لذلك. في هذا المثال، قمت بتغيير عمود “الربح الأسبوعي” إلى “الإجماليات”، وقد اعتمدت صيغة XLOOKUP في الخلية E4 هذا التعديل.
باختصار، بمجرد إنشاء جدول في Excel، يجب أن تفكر في استخدام المراجع المنظمة لتحقيق أقصى استفادة من بياناتك.
إلى جانب المزايا المذكورة أعلاه، تستخدم المراجع المنظمة أيضًا مساحة أقل من ذاكرة جهاز الكمبيوتر الخاص بك مقارنةً بالمراجع المباشرة، وهي إحدى الطرق العديدة لتسريع جداول بيانات Excel.
(العلامات للترجمة)مايكروسوفت(ر)مايكروسوفت إكسل(ر)مايكروسوفت 365