استخدام مراجع مصنفة في جداول Excel

ماذا يحدث عند استخدام مراجع الخلايا الصريحة؟

إذا قمت بإدخال مراجع الخلايا الصريحة في عمود محسوب، قد يصعب معرفة ما تقوم الصيغة بحسابه.

1. في ورقة العمل النموذجية، انقر فوق الخلية E2

2. في شريط الصيغة، أدخل =C2*D2 واضغط على مفتاح الإدخال Enter.

تجدر الإشارة إلى عدم استخدام Excel للمراجع المصنفة أثناء قيامه بنسخ الصيغة أسفل العمود. على سبيل المثال، إذا كنت تقوم بإضافة عمود بين العمودين الموجودين C وD، فيجب عليك مراجعة الصيغة.

كيف أستطيع تغيير اسم الجدول؟

عندما تقوم بإنشاء جدول Excel، ينشئ Excel اسماً افتراضياً للجدول (الجدول1 والجدول2 وما إلى ذلك)، ولكن يمكنك تغيير اسم الجدول ليصبح أكثر وضوحاً.

1. حدد أي خلية في الجدول لإظهار علامة التبويب أدوات الجدول > تصميم على الشريط.

2. اكتب الاسم الذي تريده في المربع اسم الجدول ، ثم اضغط على مفتاح الإدخال Enter.

في البيانات النموذجية، استخدمنا الاسم DeptSales.

استخدم القواعد التالية لأسماء الجداول:

• استخدام أحرف صالحة  ابدأ دائما اسما بحرف أو حرف تسطير سفلي (_) أو شرطة مائلة عكسية (\). استخدم الأحرف والأرقام والنقاط والأحرف السفلية لبقية الاسم. لا يمكنك استخدام الأحرف "C" أو "c" أو "R" أو "r" لأنه تم تعيينها سابقاً كاختصار لتحديد الأعمدة أو الصفوف للخلية النشطة عند إدخالها في مربع الاسم أو الانتقال إلى.

• عدم استخدام مراجع الخلايا  لا يمكن أن تكون الأسماء هي نفسها مرجع الخلية، مثل Z$100 أو R1C1.

• عدم استخدام مسافة لفصل الكلمات  لا يمكن استخدام المسافات في الاسم. يمكنك استخدام حرف التسطير السفلي (_) والنقطة (.) كفواصل كلمات. على سبيل المثال، DeptSales أو Sales_Tax أو First.Quarter.

• استخدام ما لا يزيد عن 255 حرفا يمكن أن يحتوي اسم الجدول على ما يصل إلى 255 حرفا.

• <c0>استخدام أسماء جداول فريدة</c0>  لا يُسمح باستخدام الأسماء المتكررة. لا يفرّق Excel بين الأحرف الكبيرة والأحرف الصغيرة في الأسماء. لذلك، إذا أدخلت Sales (أي المبيعات) ولكن سبق استخدام اسم "SALES" آخر في نفس المصنف، فستتم مطالبتك باختيار اسم فريد.

• استخدام معرف كائن  إذا كنت تخطط للحصول على مزيج من الجداول وجداول PivotTables والمخططات، فمن المستحسن بادئة أسمائك بنوع العنصر. على سبيل المثال: tbl_Sales لجدول مبيعات، pt_Sales ل PivotTable للمبيعات، chrt_Sales لمخطط مبيعات، أو ptchrt_Sales لمبيعات PivotChart. يحتفظ هذا بجميع الأسماء في قائمة مرتبة في إدارة الأسماء.

قواعد بناء جملة مرجع مصنف

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

‎=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])‎

تتضمّن الصيغة مكونات المراجع المصنفة التالية:

• اسم الجدول:    DeptSales هو اسم جدول مخصص. وهو يشير إلى بيانات الجدول، من دون أي رأس أو إجمالي الصفوف. يمكنك استخدام اسم جدول افتراضي، مثل "الجدول1"، أو تغييره لاستخدام اسم مخصص.

• محدد العمود:    [مبلغ المبيعات] و [قيمة العمولة] هما محددات أعمدة تستخدم أسماء الأعمدة التي تمثلها. وهي تشير إلى بيانات الأعمدة، من دون أي رأس أو إجمالي الصفوف. يجب دائماً إحاطة المحددات بأقواس كما هو موضح.

• محدد العنصر:    [#Totals] و [#Data] محددات عناصر خاصة تشير إلى أجزاء معينة من الجدول، مثل الصف الإجمالي.

• محدد الجدول:    [[#Totals],[قيمة المبيعات]] و[[#Data],[قيمة العمولة]] عبارة عن محددات جداول تمثل الأجزاء الخارجية من المرجع المصنف. وتتبع المراجع الخارجية اسم الجدول، ويجب إحاطتهما بأقواس مربعة.

• مرجع منظم:    (DeptSales[[#Totals],[Sales Amount]] وDptSales[[#Data],[قيمة العمولة]] هي مراجع منظمة، ممثلة بسلسلة تبدأ باسم الجدول وتنتهي بمحدد العمود.

عندما تقوم بإنشاء مراجع مصنفة أو تحريرها، استخدم قواعد بناء الجملة هذه:

• استخدام القوسين حول المحددات    يجب تضمين كل محددات الجداول والأعمدة والعناصر الخاصة بين قوسين متطابقين ([ ]). يتطلب أي محدد يتضمن محددات أخرى أقواساً خارجية متطابقة لتضمين الأقواس الداخلية المتطابقة للمحددات الأخرى. على سبيل المثال: =DeptSales[[مندوب المبيعات]:[المنطقة]]

• كل رؤوس الأعمدة كسلاسل نصية    ولكنها لا تتطلب علامات اقتباس عند استخدامها في مرجع منظم. تُعتبر الأرقام أو التواريخ مثل 2014 أو 2014/1/1 سلاسل نصية أيضاً. لا يمكنك استخدام تعبيرات مع رؤوس الأعمدة. على سبيل المثال، إن التعبير DeptSalesFYSummary[[2014]:[2012]]‎ لن يعمل.

استخدم القوسين حول رؤوس الأعمدة مع الأحرف الخاصة    في حال وجود أحرف خاصة، يجب تضمين رأس العمود بالكامل بين قوسين، ما يعني أنه يجب استخدام قوسين مزدوجين في محدد عمود. على سبيل المثال: ‎=DeptSalesFYSummary[[إجمالي القيمة بالدولار]]

في ما يلي قائمة الأحرف الخاصة التي تحتاج إلى قوسين مزدوجين في الصيغة:

• Tab 

• موجز الأسطر

• إرجاع العربة

• فاصلة (،)

• علامة النقطتين (:)

• نقطة (.)

• قوس أيسر ([)

• قوس أيمن (])

• علامة الجنيه (#)

• علامة اقتباس مفردة (')

• علامة اقتباس مزدوجة (")

• قوس أيسر ({)

• قوس أيمن (})

• علامة الدولار ($)

• علامة الإقناق (^)

• علامة العطف (&)

• علامة نجمية (*)

• علامة الجمع (+)

• علامة المساواة (=)

• علامة الطرح (-)

• أكبر من رمز (>)

• أقل من رمز (<)

• علامة القسمة (/)

• عند التوقيع (@)

• شرطة مائلة للخلف (\)

• علامة تعجب (!)

• القوس الأيسر (()

• القوس الأيمن ())

• علامة النسبة المئوية (٪)

• علامة استفهام (؟)

• Backtick (')

• فاصلة منقوطة (؛)

• Tilde (~)

• شرطة سفلية (_)

• استخدام حرف هروب لبعض الأحرف الخاصة في رؤوس الأعمدة    لبعض الأحرف معنى خاص وتتطلب استخدام علامة اقتباس مفردة (') كحرف هروب. على سبيل المثال: ‎=DeptSalesFYSummary['#OfItems]‎

فيما يلي قائمة بالأحرف الخاصة التي تحتاج إلى حرف إلغاء (') في الصيغة:

• قوس أيسر ([)

• قوس أيمن (])

• علامة الجنيه (#)

• علامة اقتباس مفردة (')

• عند التوقيع (@)

استخدام حرف المسافة لتحسين قابلية القراءة في مرجع منظم    يمكنك استخدام أحرف المسافة لتحسين قابلية قراءة مرجع منظم. على سبيل المثال: =DeptSales[ [مندوب المبيعات]:[المنطقة] أو =DeptSales[[#Headers], [#Data], [٪ العمولة]]

من المستحسن استخدام مسافة واحدة:

• بعد القوس الأيسر الأول ([)

• قبل القوس الأيمن الأخير (]).

• بعد الفاصلة.

عوامل التشغيل المرجعية

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

محددات عناصر معينة

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

مراجع مصنفة مؤهلة في أعمدة محسوبة

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

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

أمثلة على استخدام مراجع مصنفة

تجد أدناه بعض الطرق لاستخدام مراجع مصنفة.

استراتيجيات لاستخدام المراجع المصنفة

ضع في اعتبارك ما يلي عند العمل مع المراجع المنظمة.

• استخدام ميزة الإكمال التلقائي للصيغ    قد تجد أن استخدام الإكمال التلقائي للصيغ مفيد جداً عند إدخال مراجع مصنفة وللتأكد من استخدام بناء جملة صحيح. لمزيد من المعلومات، راجع استخدام الإكمال التلقائي للصيغ.

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

• استخدام المصنفات ذات الارتباطات الخارجية لجداول Excel في مصنفات أخرى    إذا احتوى المصنف على ارتباط خارجي لجدول Excel في مصنف آخر، فيجب أن يكون المصنف المصدر المرتبط مفتوحا في Excel لتجنب #REF! أخطاء في المصنف الوجهة الذي يحتوي على الارتباطات. إذا فتحت المصنف الوجهة أولا وظهرت أخطاء #REF! ، حلها إذا قمت بفتح المصنف المصدر. إذا فتحت المصنف المصدر أولا، يجب ألا ترى أي رموز خطأ.

• تحويل نطاق إلى جدول وجدول إلى نطاق    عند تحويل جدول إلى نطاق، تتغير جميع مراجع الخلايا إلى مراجع نمط A1 المطلقة المكافئة لها. عند تحويل نطاق إلى جدول، لا يغير Excel تلقائيا أي مراجع خلايا لهذا النطاق إلى مراجع منظمة مكافئة لها.

• إيقاف تشغيل رؤوس الأعمدة    يمكنك تبديل رؤوس أعمدة الجدول وإيقاف تشغيلها من علامة التبويب تصميم الجدول > صف الرأس. إذا أوقفت تشغيل رؤوس أعمدة الجدول، فلن تتأثر المراجع المنظمة التي تستخدم أسماء الأعمدة، ولا يزال بإمكانك استخدامها في الصيغ. ستؤدي المراجع المنظمة التي تشير مباشرة إلى رؤوس الجدول (على سبيل المثال = DeptSales[[#Headers],[٪Commission]]) إلى #REF.

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

• إعادة تسمية جدول أو عمود    عند إعادة تسمية جدول أو عمود، يغير Excel تلقائياً استخدام رأس العمود والجدول هذا في المراجع المصنفة كافة المستخدمة في المصنف.

• نقل المراجع المصنفة ونسخها وتعبئتها    لا يطرأ أي تغيير على كل المراجع المصنفة عندما تنسخ صيغة تستخدم مرجعاً مصنفاً أو تنقلها.

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

هل تحتاج إلى مزيد من المساعدة؟

يمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel التقني أو الحصول على الدعم في المجتمعات.

مواضيع ذات صلة

نظرة عامة على جداول
Excelفيديو: إنشاء جدول
Excel وتنسيقهإجمالي البيانات في جدول
Excelتنسيق جدول
Excelتغيير حجم جدول عن طريق إضافة صفوف وأعمدة أو إزالتها
تصفية البيانات في نطاق أو جدول
تحويل جدول إلى نطاق
مشاكل
توافق جدول Excelتصدير جدول Excel إلى SharePoint
نظرة عامة على الصيغ في Excel