كيفية استخدام المراجع المنظمة في Microsoft Excel لتحسين الكفاءة

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

نصائح سريعة

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

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

تسمح لك المراجع المنظمة في Excel بتبسيط هذا العمل من خلال تعيين أسماء للجداول ورؤوسها. يمكن بعد ذلك استخدام هذه الأسماء كمراجع خلايا ضمنية حتى يتمكن Excel من جلب البيانات المنظمة وحسابها تلقائيًا.

فيما يلي بعض الطرق الأكثر شيوعًا لاستخدام المراجع المنظمة في Excel.

1. الحساب داخل الجداول

نظرًا لأن المراجع المنظمة تعمل فقط على الجداول، فإن أفضل طريقة للاستفادة منها هي داخل نفس الجداول.

على سبيل المثال، سننشئ جدولًا بسيطًا من B2 إلى F8 ببيانات المبيعات لمتجر. لاحظ أننا أطلقنا على الجدول اسم “المبيعات” (انظر “اسم الجدول” في الجزء العلوي الأيسر).

دعنا نحسب الإجمالي لكل عملية بيع:

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

الخطوة 2: قم بتسمية رأس العمود G بـ “الإجمالي”.

إلى الخطوة 3: في G3، أدخل =[@PricePerUnit]*[@Quantity] واضغط على Enter. قم بتنسيق إخراج الخلية حسب الحاجة.

“[@PricePerUnit]” و”[@Quantity]” هما مراجع للحقول المقابلة في تلك الأعمدة. تعني وسيطة “@” قبل أسماء الأعمدة أن كل خلية نتيجة ستستخدم المراجع من نفس صف الجدول.

للترجمة، الصيغة =[@PricePerUnit]*[@Quantity] في G3 هي نفس كتابة =$C3*$D3.

2. جلب نطاق خارج الجدول

عندما تريد استخدام مرجع منظم في خلية خارج الجدول، فأنت بحاجة إلى تقديم المرجع باسم الجدول. في مثالنا السابق، سيؤدي استخدام “Sales[Total]” إلى جلب النطاق بالكامل تحت العنوان “Total” للجدول “Sales”. وهذا يعني أنك ستحصل على قيم متعددة في مصفوفة يمكنك معالجتها.

هكذا يبدو هذا داخل Excel في الخلية I3، بشرط أن تترك مساحة كافية لامتداد النطاق.

3. جمع عمود ومجموع جزئي

لجمع عمود بالكامل بسرعة، يمكنك استخدام علامة الاختيار “إجمالي الصف” في خيارات “تصميم الجدول” (تحت “خيارات نمط الجدول”). فيما يلي مثال للحصول على الإجماليات لعمودي “الكمية” و”الإجمالي”.

في حين لا يمكن نقل صف “الإجمالي” بمفرده وسيتم وضعه في نهاية الجدول (مع السماح بالإدراج)، يمكنك تكرار نتيجته في مكان آخر:

للحصول على مجموع كل الصفوف في عمود “الإجمالي”، استخدم =SUM(Sales[Total]).
إذا كنت تريد الحصول على مجموع الأعمدة المرئية فقط، مثل بعد تصفية الجدول، استخدم =SUBTOTAL(109,Sales[Total]). هذه الصيغة هي ما يفعله خيار “Total Row” في “تنسيق الجدول” في صفه بالفعل.
يمكنك أيضًا الحصول على مجموع جزئي بناءً على متغير معين موجود داخل الجدول دون تنسيقه. على سبيل المثال:

للحصول على مجموع كل المبيعات التي قام بها Mike، يمكنك استخدام =SUMIF(Sales[Seller],”Mike”,Sales[Total]). في الصيغة، “Mike” عبارة عن سلسلة تم إدخالها يدويًا.
للحصول على مجموع جميع المنتجات التي تحمل المعرف “41230”، استخدم ما يلي =SUMIF(Sales[ProductID],41230,Sales[Total]). لاحظ أنه نظرًا لأن عمود ProductID له تنسيق “عام”، فيمكنك إدخال الرقم مباشرةً.

4. التحقق من صحة البيانات من جدول عبر INDIRECT

افترض أن لديك جدول Sales المستخدم سابقًا. يمكنك إنشاء خيارات مخصصة للتحقق من صحة البيانات لتسهيل البحث عبر الجدول. دعنا ننشئ جدولًا أصغر حجمًا يسمح لك بالاختيار بين ProductIDs أو Dates أو Sellers، ثم اختر أي عنصر فردي من هذه المجموعات الفرعية لعرض المجموع الفرعي.

الخطوة 1: في الخلية B13، أنشئ التحقق من صحة البيانات (علامة التبويب “البيانات” > أدوات البيانات > التحقق من صحة البيانات).

الخطوة 2: في النافذة المنبثقة، اختر “قائمة” من خيارات “السماح”، ثم أدخل قيم الأعمدة يدويًا في مربع “المصدر”، مفصولة بفواصل. في هذه الحالة، أدخلنا “معرف المنتج، البائع، التاريخ”.

إلى الخطوة 3: في الخلية C13، أنشئ عملية تحقق أخرى للبيانات. مرة أخرى، اختر “قائمة”. بالنسبة لـ “المصدر”، أدخل الصيغة التالية: =INDIRECT(“Sales[“&B13&”]”).

الخطوة 4: في الخلية D13، استخدم الصيغة التالية: =SUMIF(INDIRECT(“Sales[“&B13&”]”),B14,Sales[Total]).

يمكنك الآن اختيار الخيارات من قائمتي التحقق من صحة البيانات، وسيتم عرض المجموع الفرعي في D13.