استخدام مراجع مصنفة في جداول 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.

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

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

  • استخدام أسماء جداول فريدة لا يُسمح باستخدام الأسماء المتكررة. لا يفرّق 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]، [قيمه المبيعات]] و DeptSales [[#Data]، [قيمه العموله]] عباره عن مراجع مصنفه تمثلها سلسله تبدا ب# اسم الجدول و# تنتهي ب# محدد عمود.

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

  • استخدم القوسين حول المحددات    كافه الجداول و# الاعمده و# محددات عناصر معينه يلزم احاطه في مطابقه اقواس ([]). يتطلب اي محدد يتضمن محددات اخري اقواس متطابقه خارجيه احاطه الاقواس ل# المحددات الاخري المتطابقه الداخليه. علي سبيل المثال: = DeptSales [[مندوب المبيعات]: [المنطقه]]

  • كل رؤوس الاعمده ك# سلاسل نصيه    و# لكن لا تتطلب عروض الاسعار عند استخدامها في مرجع مصنف. ارقام او تواريخ، مثل 2014 او 1 / 1 / 2014، ف# سيتم ايضا اعتبار السلاسل النصيه. لا يمكنك استخدام تعبيرات مع رؤوس الاعمده. علي سبيل المثال، التعبير DeptSalesFYSummary [[2014]: [2012]] لن تعمل.

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

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

  • مفتاح Tab

  • تغذية الأسطر

  • السطر الجديد

  • الفاصلة (,)

  • النقطتان (:)

  • النقطة (.)

  • القوس المتعرج الأيسر ([)

  • القوس المتعرج الأيمن (])

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

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

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

  • القوس الكبير الأيسر ({)

  • القوس الكبير الأيمن (})

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

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

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

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

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

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

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

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

  • رمز أصغر من (<)

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

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

فيما يلي قائمه الاحرف الخاصه التي تحتاج الي حرف هروب (') في الصيغه:

  • القوس المتعرج الأيسر ([)

  • القوس المتعرج الأيمن (])

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

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

استخدام حرف المسافة لتحسين سهولة القراءة في مرجع مصنف    يمكنك استخدام أحرف المسافة لتحسين إمكانية قراءة مرجع مصنف بالطريقة التالية: على سبيل المثال: =DeptSales[ [مندوب المبيعات]:[المنطقة] ] أو =DeptSales[[#الرؤوس], [#البيانات], [نسبة العمولة المئوية]]

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

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

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

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

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

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

هذا المرجع المصنف:

للإشارة إلى:

باستخدام:

وهو نطاق الخلايا:

‎=DeptSales[[مندوب المبيعات]:[المنطقة]]‎

كافة الخلايا في عمودين متجاورين أو أكثر

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

A2:B7

=DeptSales[قيمة المبيعات]،DeptSales[قيمة العمولة]

مجموعة من عمودين أو أكثر

، (فاصلة) عامل الاتحاد

C2:C7،E2:E7

=DeptSales[[مندوب المبيعات]:[قيمة المبيعات]] DeptSales[[المنطقة]:[% العمولة]]

تقاطع عمودين أو أكثر

(مسافة) عامل التقاطع

B2:C7

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

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

محددات العنصر المعين هذا:

للإشارة إلى:

‎#All

الجدول بأكمله، متضمناً رؤوس الأعمدة والبيانات والإجماليات (إن وجدت).

‎#Data

صفوف البيانات فقط.

‎#Headers

صف الرأس فقط.

‎#Totals

صف الإجمالي فقط. في حال عدم وجوده، سيُرجع قيمة خالية.

#This Row

أو

@

أو

@[اسم العمود]

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

يغيّر Excel تلقائياً المحدد #This Row إلى المحدد @ الأقصر في الجداول التي تتضمّن أكثر من صف بيانات واحد. ولكن إذا لم يتضمّن جدولك سوى صف واحد، فلن يستبدل Excel المحدد #This Row، مما قد يسبّب نتائج حسابية غير متوقعة عند إضافة المزيد من الصفوف. لتفادي حدوث مشاكل في العمليات الحسابية، تأكد من إدخال عدة صفوف في الجدول قبل إدخال أي صيغ مراجع مصنفة.

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

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

نوع المرجع المصنف

مثال

التعليق

غير مؤهل

=[قيمة المبيعات]*[% العمولة]

لضرب القيم المتطابقة من الصف الحالي.

مؤهل بشكل كامل

=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 User Voice‏.

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

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

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

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×