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

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

يعد فهم هذه المراجع المصنفة: أسهل من مرجع الخلية التالي:
‎=SUM(DeptSales[SaleAmt])‎ ‎=Sum(C2:C7)‎

في هذه المقالة

مثال لجدول مبيعات القسم

مكونات مرجع مصنف

أسماء الجداول ومحددات الأعمدة

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

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

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

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

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

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

 

مثال لجدول مبيعات القسم

فيما يلي مثال، مشار إليه خلال هذه المقالة، لجدول يستند إلى مبيعات قسم يضم ستة عاملين مع آخر مبالغ للمبيعات والعمولات.

جدول مبيعات قسم

f289fbbd-93c4-4d2c-b44f-95262b5b31381

1. الجدول بأكمله (A1:E8)

2. بيانات الجدول (A2:E7)

3. عمود ورأس عمود (D1:D8)

4. عمود محسوب (E1:E8)

5. صف الإجماليات (A8:E8)

أعلى الصفحة

 

مكونات مرجع مصنف

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

12f96332-9252-437e-8cc2-319afd37e4221

1. يكون اسم الجدول اسمًا ذا معنى للإشارة إلى بيانات الجدول الفعلية (مستبعدًا صف العناوين وصف الإجماليات، إن وجدا).

2. يشتق محدد أي عمود من رأس العمود، الموجود بين قوسين، ويرجع إلى بيانات العمود (مستبعدًا رأس العمود والإجمالي، إن وجدا).

3. يعد محدد عنصر معين أسلوبًا للإشارة إلى أجزاء معينة من الجدول مثل صف الإجماليات.

4. محدد الجدول هو الجزء الخارجي للمرجع المصنف المتضمن بين قوسين مربعين يتلوان اسم الجدول.

5. يمثل المرجع المصنف سلسلة كاملة ابتداءً من اسم الجدول حتى محدد الجدول.

أعلى الصفحة

 

أسماء الجداول ومحددات الأعمدة

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

يشير اسم الجدول إلى كامل نطاق البيانات في الجدول باستثناء صفوف الرؤوس والإجماليات. ففي مثال لجدول مبيعات القسم، يشير اسم الجدول “DeptSales” إلى نطاق الخلايا A2:E7.

مثل أسماء الجداول، تمثل محددات الأعمدة مراجع إلى عمود كامل من البيانات باستثناء رأس العمود والإجمالي. في مثال لجدول مبيعات القسم، يشير محدد العمود، [Region]، إلى نطاق الخلايا B2:B7 ويشير محدد العمود، [ComPct]، إلى نطاق الخلايا D2:D7.

أعلى الصفحة

 

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

بالنسبة للمرونة المضافة عند تعيين نطاقات خلايا، يمكنك استخدام عوامل المراجع التالية لضم محددات الأعمدة.

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

أعلى الصفحة

 

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

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

محددات العنصر المعين هذا: تشير إلى: الذي يكون، في المثال، نطاق خلايا:
‎=DeptSales[#All]‎ الجدول بأكمله، متضمنًا رؤوس الأعمدة والبيانات والإجماليات (إن وجدت). A1:E8
‎=DeptSales[#Data]‎ البيانات فقط. A2:E7
‎=DeptSales[#Headers]‎ فقط صف الرأس. A1:E1
‎=DeptSales[#Totals]‎ فقط صف الإجمالي. إذا لم يوجد، فإنها ترجع قيمة خالية. A8:E8
‎=DeptSales[#This Row]‎ فقط جزء من الأعمدة الموجودة في الصف الحالي. يتعذر ضم #ThisRow مع أية محددات عناصر معينة أخرى. استخدمها لإجبار سلوك التقاطع الضمني للمرجع أو لتجاوز سلوك التقاطع الضمني والإشارة إلى قيم مفردة من عمود. للاطلاع على مزيد من الأمثلة، راجع أمثلة لاستخدام مراجع مصنفة. A5:E5 (إذا كان الصف الحالي هو 5)

أعلى الصفحة

 

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

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

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

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

أعلى الصفحة

 

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

هناك عدة طرق يمكنك بها استخدام هذه العناصر المعينة وضمها مع أسماء الجداول ومراجع الأعمدة كما تظهر المعلومات التالية:

هذا المرجع المصنف: يشير إلى: الذي يكون، في المثال، نطاق خلايا:
‎=DeptSales[[#All],[SaleAmt]]‎ كافة الخلايا في العمود SaleAmt. C1:C8
‎=DeptSales[[#Headers],[ComPct]]‎ رأس العمود ComPct. C1
‎=DeptSales[[#Totals],[Region]]‎ إجمالي العمود Region. إذا لم يكن هناك صف “إجماليات”، فإنها ترجع قيمة خالية. B8
‎=DeptSales[[#All],[SaleAmt]:[ComPct]]‎ كافة الخلايا في SaleAmt وComPct. C1:D8
‎=DeptSales[[#Data],[ComPct]:[ComAmt]]‎ بيانات العمودين ComPct وComAmt فقط. D2:E7
‎=DeptSales[[#Headers],[Region]:[ComAmt]]‎ فقط رؤوس الأعمدة الموجودة بين Region وComPct وComAmt. B1:E1
‎=DeptSales[[#Totals],[SaleAmt]:[ComAmt]]‎ إجماليات SaleAmt عبر أعمدة ComAmt. إذا لم يكن هناك صف “إجماليات”، فإنها ترجع قيمة خالية. C8:E8
‎=DeptSales[[#Headers],[#Data],[ComPct]]‎ فقط رأس ComPct والبيانات الخاصة به. D1:D7
‎=DeptSales[[#This Row], [ComAmt]]‎ الخلية الموجودة عند نقطة تقاطع الصف الحالي وعمود ComAmt. E5 (إذا كان الصف الحالي هو 5)

أعلى الصفحة

 

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

قم بمراعاة التالي عند العمل مع المراجع المصنفة.

استخدام الإكمال التلقائي للصيغ

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

للحصول على مزيد من المعلومات، راجع استخدام “الإكمال التلقائي للصيغ”.

تقرير إنشاء مراجع مصنفة للجداول في شبه تحديد

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

تحويل نطاق إلى جدول وجدول إلى نطاق

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

إيقاف تشغيل رؤوس الأعمدة

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

إضافة أعمدة وصفوف إلى الجدول وحذفها

نظرًا لتغير نطاقات بيانات الجدول غالبًا، يتم ضبط مرجع خلايا المراجع المصنفة تلقائيًا. على سبيل المثال، إذا قمت باستخدام اسم جدول في صيغة لحساب كافة خلايا البيانات في جدول “مبيعات القسم”، مثل ‎=COUNTA(DeptSales)‎ في مثال لجدول مبيعات القسم، تكون القيمة التي تم إرجاعها هي 30 لأن نطاق البيانات هو A2:E7. أما إذا قمت بعد ذلك بإضافة صف من البيانات، يتم ضبط مرجع الخلية تلقائيًا إلى A2:E8، وتكون القيمة الجديدة التي تم إرجاعها هي 35.

إعادة تسمية جدول أو عمود

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

نقل مراجع مصنفة ونسخها وتعبئتها

تظل كافة المراجع المصنفة كما هي عند نسخ صيغة تستخدم مرجعًا مصنفًا أو نقلها.

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

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

أعلى الصفحة

 

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

فيما يلي قائمة بقواعد البناء التي تحتاج إلى معرفتها عند إنشاء مراجع مصنفة وتحريرها.

ملاحظات  تتبع أسماء الجداول القواعد التي تتبعها الأسماء المعرفة نفسها.

لمزيد من المعلومات، انظر تعريف أسماء واستخدامها في الصيغ.

استخدام الأقواس في المحددات

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

مثال     ‎=DeptSales[[SalesPers]:[Region]]‎

تعتبر رؤوس الأعمدة سلاسل نصية

تعتبر كافة رؤوس الأعمدة سلاسل نصية، لكن لا تتطلب علامتي اقتباس عند استخدامها في مرجع مصنف. في رأس عمود يتضمن أرقام أو تواريخ، مثل 2004 أو 1/1/2004، لا نزال نعتبرها سلاسل نصية. نظرًا لأن رؤوس الأعمدة سلاسل نصية، يتعذر عليك استخدام تعبيرات بين الأقواس.

مثال     ‎=DeptSalesFYSummary[[2004]:[2002]]‎

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

إذا احتوى رأس عمود في جدول على واحدٍ من الأحرف الخاصة التالية، يجب تضمين رأس العمود بأكمله بين قوسين. بشكل فعّال، يعنى هذا أن من المطلوب وجود أقواس مزدوجة في محدد عمود مع الأحرف الخاصة التالية: المسافة وعلامة الجدولة وتغذية أسطر وحرف إرجاع والفاصلة (،) والنقطتين (:) والنقطة (.) والقوس الأيمن ([) والقوس الأيسر (]) وعلامة الجنيه (#) وعلامة الاقتباس الفردية (‘) وعلامة الاقتباس المزدوجة (“) وقوس كبير أيسر (}) وقوس كبير أيمن ({) وعلامة الدولار($) والعلامة (^) والعلامة (&) والعلامة النجمية (*) وعلامة الجمع (+) وعلامة المساواة (=) وعلامة الطرح (-) ورمز أكبر من (>) ورمز أصغر من (<) وعلامة القسمة (/).

مثال     ‎=DeptSalesFYSummary[[Total$Amount]]‎

الاستثناء الوحيد لذلك هو إذا كان الحرف الخاص الوحيد المستخدم هو حرف المسافة.

مثال    ‎=DeptSales[Total Amount]‎

أحرف خاصة في رؤوس الأعمدة التي تتطلب استخدام حرف الهروب

للأحرف التالية معنى خاص وتتطلب استخدام علامة اقتباس أحادية (‘) كحرف هروب: قوس أيمن ([) وقوس أيسر (]) وعلامة الجنيه (#) وعلامة اقتباس فردية (‘).

مثال     ‎=DeptSalesFYSummary[‘#OfItems]‎

استخدام حرف المسافة لتحسين إمكانية القراءة في مرجع مصنف

يمكنك استخدام أحرف المسافة لتحسين إمكانية قراءة مرجع مصنف بالطريقة التالية:

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

مثال     ‎=DeptSales[ [SalesPers]:[Region] ]‎

مثال     ‎=DeptSales[[#Headers], [#Data], [ComPct]]‎

أعلى الصفحة

الكاتب Geek4arab

Geek4arab

مواضيع متعلقة

اترك رداً