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

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

بدلاً من استخدام مراجع الخلايا الصريحة	يستخدم Excel اسمي الجدول والعمود
‎=Sum(C2:C7)‎	‎[(قيمة ‎المبيعات]‎=SUM(DeptSales

تسمى هذه التركيبة من أسماء الجداول والأعمدة بالمرجع المصنف. يتم ضبط الأسماء الموجودة في المراجع المصنفة كلما أضفت بيانات إلى الجدول أو أزلتها منه.

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

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

مندوب المبيعات	المنطقة	قيمة المبيعات	نسبة العمولة المئوية	قيمة العمولة
فاضل	المنطقة الشمالية	260	‎10%
سلام	المنطقة الجنوبية	660	%15
آيلة	المنطقة الشرقية	940	%15
رجب	المنطقة الغربية	410	%12
علياء	المنطقة الشمالية	800	%15
جبران	المنطقة الجنوبية	900	%15

1. انسخ البيانات النموذجية في الجدول أعلاه، بما في ذلك عناوين الأعمدة، والصقها في الخلية A1 في ورقة عمل Excel جديدة.
2. لإنشاء جدول، حدد أي خلية في نطاق البيانات، واضغط على Ctrl+T.
3. تأكد من احتواء الجدول على رؤوس محدد، ثم حدد موافق.
4. في الخلية E2، اكتب علامة التساوي (=)، وحدد الخلية C2.
   في شريط الصيغة، يظهر المرجع المصنف [[قيمة المبيعات]@] بعد علامة التساوي.
5. اكتب العلامة النجمية (*) بعد قوس الإغلاق مباشرة، وحدد الخلية D2.
   في شريط الصيغة، يظهر المرجع المصنف [[نسبة العمولة المئوية]@] بعد العلامة النجمية.
6. اضغط Enter.
   يقوم 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 حرفا.
• استخدام أسماء جداول فريدة لا يسمح باستخدام الأسماء المتكررة. لذلك، إذا أدخلت 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]،[قيمة المبيعات]]وDeptSales[[#Data]،[قيمة العمولة]] هي مراجع مصنفة، تمثلها سلسلة تبدأ باسم الجدول وتنتهي بمحدد العمود.

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

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

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

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

• Tab
• تغذية الأسطر
• رجوع إلى عربة السطر
• فاصلة (،)
• علامة النقطتين (:)
• نقطة (.)
• قوس متوسط أيسر ([)
• قوس متوسط أيمن (])
• علامة الرطل (#)
• علامة اقتباس فردية (')
• علامة اقتباس مزدوجة (")
• قوس كبير أيسر ({)
• قوس كبير أيمن (})
• علامة الدولار ($)
• علامة الإقحام (^)
• علامة العطف (&)
• العلامة النجمية (*)
• علامة الجمع (+)
• علامة التساوي (=)
• علامة الطرح (-)
• رمز أكبر من (>)
• رمز أقل من (<)
• علامة القسمة (/)
• At sign (@)
• شرطة مائلة للخلف (\)
• علامة تعجب (!)
• القوس الأيسر ())
• القوس الأيمن ())
• علامة النسبة المئوية (٪)
• علامة استفهام (؟)
• Backtick (')
• فاصلة منقوطة (؛)
• علامة التلدة (~)
• التسطير السفلي (_)

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

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

• قوس متوسط أيسر ([)
• قوس متوسط أيمن (])
• علامة الرطل (#)
• علامة اقتباس فردية (')
• At sign (@)

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

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

• بعد القوس المتوسط الأيمن الأول ([)
• يسبق القوس المتوسط الأيمن الأخير (]).
• بعد فاصلة.

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

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

هذا المرجع المصنف:	للإشارة إلى:	باستخدام:	وهو نطاق الخلايا:
‎=DeptSales[[مندوب المبيعات]:[المنطقة]]‎	كافة الخلايا في عمودين متجاورين أو أكثر	: (علامة النقطتين) عامل النطاق	A2:B7
=DeptSales[قيمة المبيعات]،DeptSales[قيمة العمولة]	مجموعة من عمودين أو أكثر	، (فاصلة) عامل الاتحاد	C2:C7،E2:E7
=DeptSales[[مندوب المبيعات]:[قيمة المبيعات]] DeptSales[[المنطقة]:[% العمولة]]	تقاطع عمودين أو أكثر	(مسافة) عامل التقاطع	B2:C7

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

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

محددات العنصر المعين هذا:	للإشارة إلى:
‎#All	الجدول بأكمله، متضمناً رؤوس الأعمدة والبيانات والإجماليات (إن وجدت).
‎#Data	صفوف البيانات فقط.
‎#Headers	صف الرأس فقط.
‎#Totals	صف الإجمالي فقط. في حال عدم وجوده، سيُرجع قيمة خالية.
#This Row أو @ أو @[اسم العمود]	الخلايا في الصف نفسه فقط كالصيغة. لا يمكن جمع هذه المحددات مع أي محددات عناصر خاصة أخرى. استخدمها لفرض سلوك التقاطع الضمني للمرجع أو لتجاوز سلوك التقاطع الضمني والإشارة إلى قيم مفردة من عمود. يغيّر Excel تلقائياً المحدد #This Row إلى المحدد @ الأقصر في الجداول التي تتضمّن أكثر من صف بيانات واحد. ولكن إذا لم يتضمن جدولك سوى صف واحد، فلن يستبدل Excel المحدد "صف #This"، مما قد يسبب نتائج حسابية غير متوقعة عند إضافة المزيد من الصفوف. لتفادي حدوث مشاكل في العمليات الحسابية، تأكد من إدخال عدة صفوف في الجدول قبل إدخال أي صيغ مراجع مصنفة.

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

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

نوع المرجع المصنف	مثال	التعليق
غير مؤهل	=[قيمة المبيعات]*[% العمولة]	لضرب القيم المتطابقة من الصف الحالي.
مؤهل بشكل كامل	=DeptSales[قيمة المبيعات]*DeptSales[% العمولة]	ضرب القيم المتطابقة لكل صف من العمودين.

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

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

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

هذا المرجع المصنف:	للإشارة إلى:	وهو نطاق الخلايا:
=DeptSales[[#All]،[قيمة المبيعات]]	كل الخلايا في عمود "قيمة المبيعات".	C1:C8
=DeptSales[[#Headers]،[% العمولة]]	رأس العمود "% العمولة".	D1
‎=DeptSales[[#Totals],[المنطقة]]‎	إجمالي العمود "المنطقة". إذا لم يكن صف "الإجماليات" متوفراً، فسيُرجع قيمة خالية.	B8
=DeptSales[[#All],[قيمة المبيعات]:[% العمولة]]	كل الخلايا في "قيمة المبيعات" و"% العمولة".	C1:D8
=DeptSales[[#Data],[% العمولة]:[قيمة العمولة]]	فقط بيانات العمودين "% العمولة" و"قيمة العمولة".	D2:E7
=DeptSales[[#Headers]،[المنطقة]:[قيمة العمولة]]	فقط رؤوس الأعمدة الموجودة بين "المنطقة" و"قيمة العمولة".	B1:E1
=DeptSales[[#Totals],[قيمة المبيعات]:[قيمة العمولة]]	إجماليات "قيمة المبيعات" عبر أعمدة قيمة العمولة. إذا لم يكن صف "الإجماليات" متوفراً، فسيُرجع عندئذٍ قيمة خالية.	C8:E8
=DeptSales[[#Headers],[#Data],[% العمولة]]	فقط رأس العمود "%العمولة" وبياناته.	D1:D7
=DeptSales[[#This Row], [قيمة العمولة]] أو =DeptSales[@قيمة العمولة]	الخلية الموجودة عند نقطة تقاطع الصف الحالي وعمود "قيمة العمولة". عند استخدامه في الصف نفسه كصف رأس أو صف إجمالي، سيرجع الخطأ #VALUE! . إذا كتبت الشكل الأطول لهذا المرجع المصنف (#This Row) في جدول يتضمّن عدة صفوف بيانات، سيتم استبداله تلقائياً بالشكل الأقصر (@) بواسطة Excel. كلاهما يعمل بالطريقة نفسها.	E5 (إذا كان الصف الحالي هو 5)

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

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

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

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

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

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

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

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

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

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

  ملاحظة

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

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

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

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

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

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