مراجع منظمة في Excel | دليل خطوة بخطوة مع أمثلة
كيفية إنشاء مراجع منظمة في Excel؟
تبدأ المراجع الهيكلية بجداول Excel. بمجرد أن يتم إنشاء الجداول في Excel ، فإنه يقوم تلقائيًا بإنشاء مراجع منظمة لك.
الآن الق نظرة على الصورة أدناه.
- الخطوة 1: لقد أعطيت ارتباطًا بالخلية B3 ، بدلاً من إظهار الارتباط كـ B2 ، فإنه يظهر كـ Table1 [Sales]. هنا Table1 هو اسم الجدول و Sales هو العمود الذي نشير إليه. تتم الإشارة إلى جميع الخلايا الموجودة في هذا العمود باسم جدول ويتبعها اسم عنوان العمود.
- الخطوة 2: الآن سوف أقوم بتغيير اسم الجدول إلى Data_Table وتغيير عنوان العمود إلى المبلغ .
- الخطوة 3: لتغيير اسم الجدول ، ضع مؤشرًا داخل الجدول> انتقل إلى التصميم> اسم الجدول.
- الخطوة 4: أذكر اسم الجدول Data_Table.
- الخطوة 5: التغيير الآن يعطي إشارة إلى خلية B3.
لذلك فهمنا أن المرجع المنظم يتكون من جزأين اسم الجدول واسم العمود.
أمثلة
يمكنك تنزيل نموذج Excel للمراجع المنظمة من هنا - نموذج Excel للمراجع المنظمةمثال 1
باستخدام المراجع المصنفة ، يمكنك جعل صيغتك ديناميكية. على عكس مراجع الخلايا العادية ، فإنه يسمح للصيغة أن تكون مباشرة في حالة الإضافة والحذف في نطاق البيانات.
اسمحوا لي أن أطبق صيغة SUM لكل من النطاق الطبيعي وجدول Excel.
صيغة SUM للنطاق الطبيعي.
صيغة SUM لجدول Excel.
اسمحوا لي أن أضيف بضعة أسطر إلى بيانات كل من الجدول العادي و Excel. لقد أضفت عنصرين إلى البيانات ، والآن شاهد الفرق.
يُظهر المرجع المُنظَّم في جدول Excel القيمة المُحدَّثة ولكن نطاق البيانات العادي لا يُظهر القيم المُحدَّثة إلا إذا أجريت بعض التغييرات على الصيغة يدويًا.
المثال رقم 2
الآن ، انظر إلى مثال آخر. لدي معلومات عن اسم المنتج والكمية والسعر. أحتاج باستخدام هذه المعلومات للوصول إلى قيمة المبيعات.
من أجل الحصول على قيمة المبيعات ، تكون الصيغة هي الكمية * السعر . دعونا نطبق هذه الصيغة في الجدول.
الصيغة تقول [QTY] * [@ السعر]. هذا مفهوم أكثر من المرجع العادي لـ B2 * C2. لا نحصل على اسم الجدول إذا كنا نضع الصيغة داخل الجدول.
مشاكل المراجع الهيكلية في Excel
أثناء استخدام المراجع المنظمة ، نواجه بعض المشكلات المدرجة أدناه.
المشكلة رقم 1
المراجع المنظمة لها مشاكلها الخاصة أيضًا. نحن جميعًا على دراية بتطبيق صيغة excel ونسخها أو سحبها إلى الخلايا المتبقية الأخرى. هذه ليست نفس العملية في المراجع الهيكلية ، فهي تعمل بشكل مختلف قليلاً.
الآن ، انظر إلى المثال أدناه. لقد قمت بتطبيق صيغة SUM في Excel للنطاق الطبيعي.
إذا كنت أرغب في جمع السعر وقيمة المبيعات ، فسأقوم ببساطة بنسخ ولصق أو سحب الصيغة الحالية إلى الخليتين الأخريين وستعطيني قيمة SUM للسعر وقيمة البيع.
قم الآن بتطبيق نفس الصيغة لجدول excel لعمود الكمية.
الآن حصلنا على مجموع عمود الكمية. مثل النطاق العادي ، انسخ الصيغة الحالية والصقها في عمود السعر للحصول على إجمالي السعر.
يا إلهي!!! إنه لا يعرض إجمالي عمود السعر ولكنه لا يزال يعرض إجمالي عمود الكمية فقط. لذلك ، لا يمكننا نسخ هذه الصيغة ولصقها في الخلية المجاورة أو أي خلية أخرى للإشارة إلى العمود أو الصف النسبي.
اسحب الصيغة لتغيير المرجع
الآن نحن نعرف حدوده ، لا يمكننا القيام بمهمة النسخ واللصق بعد الآن باستخدام المراجع المنظمة. ثم كيف نتغلب على هذا القيد؟
الحل بسيط للغاية ، نحتاج فقط إلى سحب الصيغة بدلاً من نسخها. حدد خلية الصيغة واستخدم مقبض التعبئة واسحبه إلى الخليتين المتبقيتين لتغيير مرجع العمود إلى السعر وقيمة البيع.
الآن قمنا بتحديث الصيغ للحصول على الإجماليات المعنية.
المشكلة رقم 2
لقد رأينا مشكلة واحدة في مراجع البنية ووجدنا الحل أيضًا ، ولكن لدينا مشكلة أخرى هنا ، لا يمكننا إجراء الاستدعاء كمرجع مطلق إذا كنا نسحب الصيغة إلى خلايا أخرى.
دعنا نلقي نظرة على المثال أدناه الآن. لدي جدول مبيعات بإدخالات متعددة وأريد دمج البيانات باستخدام وظيفة SUMIF في Excel.
سأقوم الآن بتطبيق وظيفة SUMIF للحصول على قيم المبيعات الموحدة لكل منتج.
لقد قمت بتطبيق صيغة شهر يناير ، نظرًا لأنه مرجع منظم لا يمكننا نسخ الصيغة ولصقها في العمودين المتبقيين ، فلن يغير المرجع إلى فبراير ومارس ، لذلك سأقوم بسحب الصيغة.
أوه!! لم أحصل على أي قيم في عمود فبراير ومارس. ما هي المشكلة المتوقعة؟؟؟ انظر عن كثب إلى الصيغة.
لقد قمنا بسحب الصيغة من شهر يناير. في الدالة SUMIF ، تكون الوسيطة الأولى هي Criteria Range Sales_Table [المنتج] نظرًا لأننا قمنا بسحب الصيغة التي تحتوي على تغييرات إلى Sales _Table [يناير].
فكيف نتعامل معها ؟؟ نحتاج إلى جعل الوسيطة الأولى ، أي عمود المنتج كأعمدة مطلقة وأعمدة أخرى كمرجع نسبي. على عكس المرجع العادي ، ليس لدينا رفاهية استخدام مفتاح F4 لتغيير نوع المرجع.
الحل هو أننا نحتاج إلى تكرار العمود المرجعي كما هو موضح في الصورة أدناه.
الآن يمكننا سحب الصيغة إلى عمودين موسعين آخرين. سيكون نطاق المعايير ثابتًا وستتغير مراجع الأعمدة الأخرى وفقًا لذلك.
نصيحة للمحترفين: من أجل جعل ROW مرجعًا مطلقًا ، نحتاج إلى إدخال ROW مزدوج ولكننا نحتاج إلى وضع الرمز @ قبل اسم ROW.
= Sales_Table [@ [المنتج]: [المنتج]]
كيفية إيقاف تشغيل المرجع المهيكل في Excel؟
إذا لم تكن من محبي المراجع المنظمة ، فيمكنك إيقاف تشغيلها باتباع الخطوات التالية.
- الخطوة 1: اذهب إلى FILE> Options.
- الخطوة 2: الصيغ> قم بإلغاء تحديد استخدام أسماء الجداول في الصيغ.
أشياء للذكرى
- لعمل المرجع المطلق في المرجع المصنف ، نحتاج إلى مضاعفة اسم العمود.
- لا يمكننا نسخ صيغة المرجع المهيكل بدلاً من ذلك نحتاج إلى سحب الصيغة.
- لا يمكننا رؤية الخلية التي نشير إليها بالضبط في المراجع المنظمة.
- إذا لم تكن مهتمًا بالمراجع المنظمة ، فيمكنك إيقاف تشغيلها.
