كيف يمكن تبديل المرجع أثناء استخدام التعبئة التلقائية لأسفل أو لليمين في Excel؟
عند العمل بالصيغ والتعبئة التلقائية في Excel، يزيد السلوك الافتراضي مراجع الأعمدة تلقائيًا عند سحب مقبض التعبئة أفقيًا إلى اليمين، ويزيد مراجع الصفوف عند السحب عموديًا لأسفل. لكن قد تحتاج أحيانًا إلى عكس هذا السلوك — مثل الرجوع إلى نطاق عمودي أثناء التعبئة أفقيًا، أو العكس. يُعد هذا الأسلوب مفيدًا جدًّا عند إعادة تنظيم تخطيطات البيانات أو إنشاء تقارير ديناميكية تتطلب ربط البيانات بطريقة مبدَّلة. ستستعرض هذه المقالة طرقًا عملية متنوعة لتحقيق هذه التعبئة التلقائية المبدَّلة، بما في ذلك استخدام الصيغ، ماكروهات VBA، وأدوات مساعدة فعّالة مثل Kutools. راجع الرسم التوضيحي أدناه لعرض سيناريو نموذجي للتعبئة التلقائية المبدَّلة:
تبديل المرجع أثناء التعبئة لأسفل أو لليمين
صيغة Excel – استخدام INDEX أو OFFSET لبناء مراجع مبدَّلة يدويًّا
تبديل المرجع أثناء التعبئة لأسفل أو لليمين
لتبديل مرجع أثناء التعبئة عبر الصفوف أو الأعمدة، يمكنك استخدام دالة Excel TRANSPOSE. هذه الطريقة مثالية عندما تريد تحويل بيانات مرتبة في عمود إلى صف (أو العكس)، حيث تقوم الصيغة بتعديل اتجاه المرجع تلقائيًّا أثناء التعبئة التلقائية.
حدِّد النطاق الذي تريد تطبيق المرجع المبدَّل عليه، ثم أدخل الصيغة التالية في شريط الصيغة:
=TRANSPOSE(Sheet2!$B$1:B12)
وبعد ذلك، اضغط علىCtrl + Shift + Enter معًا (للإصدارات السابقة لـ Microsoft 365 / Excel 2021). أما في الإصدارات الجديدة التي تدعم المصفوفات الديناميكية، فيكفي أن تضغط علىEnter فقط. راجع لقطة الشاشة أدناه:
في هذه الصيغة، يمثّلB1:B12 النطاق الذي ترغب في تبديله من ترتيب عمودي إلى ترتيب أفقي. يمكنك تعديل اسم الورقة والنطاق المحدد ليتناسب مع نطاق المصدر الفعلي الخاص بك.
تلميح:لتجنب أخطاء الصيغ، تأكد من أن حجم نطاق الوجهة للبيانات المبدَّلة مناسب (على سبيل المثال، إذا كان مصدرك عمودًا واحدًا يحتوي على 12 صفًّا، فيجب أن يكون نطاق الوجهة صفًّا واحدًا يحتوي على 12 عمودًا).
صيغة Excel – استخدام INDEX أو OFFSET لبناء مراجع مبدَّلة يدويًّا
أحيانًا لا يُفضَّل استخدام صيغ المصفوفة مثلTRANSPOSE، أو قد تحتاج إلى تحكُّمٍ أكبر في طريقة تبديل المراجع—خاصةً مع البيانات غير المتصلة أو عند العمل بإصدارات Excel القديمة. باستخدام دالتَيINDEX وOFFSET، يمكنك الرجوع مباشرةً إلى البيانات المبدَّلة عبر عمليات التعبئة القياسية، دون الحاجة إلى الضغط على Ctrl + Shift + Enter.
السيناريوهات المناسبة:تُفضَّل عندما تحتاج إلى مرجع مبدَّل واحد مقابل واحد لكل خلية، كأن تحوِّل محتوى الخلية A1 من الاتجاه العمودي إلى الأفقي في الخلية B1 (أو العكس). وهي مثالية لتجنب إدخال المصفوفات يدويًّا، أو للحصول على مراجع ديناميكية عبر أوراق العمل المعقدة.
المزايا:لا يتطلب إدخال مصفوفة، والصيغ سهلة السحب والتعبئة.
العيوب المحتملة:قد تكون الصيغة الأولية أكثر تعقيدًا قليلاً في الإعداد؛ لذا انتبه جيدًا لكيفية ضبط مؤشرات الصفوف والأعمدة.
كيفية استخدام INDEX للرجوع المبدَّل:
1. افترض أن لديك بيانات فيA1:A5 (عموديًّا)، وترغب في تعبئتها مبدَّلة فيB1:F1 (أفقيًّا). حدد الخليةB1 وأدخل الصيغة التالية:
=INDEX($A$1:$A$5,COLUMN(A1))2. اضغط علىEnter، ثم اسحب الصيغة إلى اليمين حتى الخليةF1. تستخدم الصيغةCOLUMN(A1) للزيادة أثناء السحب إلى اليمين، فتشير بالتالي إلى A1 وA2 و... وA5 عبر الخلايا B1:F1.
كيفية استخدام OFFSET للرجوع المبدَّل:
1. افترض أن بياناتك موجودة في الصفB1:F1 (أفقيًّا)، وترغب في تعبئتها عموديًّا لأسفل العمودG1:G5. في الخليةG1، أدخل:
=OFFSET($B$1,0,ROW(A1)-1)2. اضغط علىEnter، ثم اسحب الصيغة لأسفل حتىG5. أثناء التعبئة لأسفل، يزدادROW(A1)-1 من 0، مما يغيّر مؤشر العمود في دالة OFFSET لجلب B1 وC1 وD1 وما إلى ذلك.
عدّل نطاق الصيغة أو موضع البداية حسب الحاجة ليتناسب مع تخطيط بياناتك، وتأكد دائمًا من صحة المراجع—خاصةً عند نسخ الصيغ أو نقلها—لتجنب أي عدم تطابق في المراجع.
استكشاف الأخطاء وإصلاحها:إذا ظهر الخطأ#REF!، فتحقق مجددًا من نطاقات المرجع وتأكد من أن الصيغة لا تحاول الرجوع إلى خارج مجموعة البيانات المستهدَفة. كما قد تؤدي هذه الصيغ، عند استخدامها مع مجموعات بيانات كبيرة، إلى زيادة العبء الحسابي على المصنف.
باختصار، يعتمد اختيارك بين استخدام الصيغ أو VBA أو إضافة Excel على هيكل بياناتك، ومدى تكرار حاجتك لتنفيذ هذه المهمة، وراحتك مع ميزات Excel المتقدمة. إذا لم تناسبك إحدى الطرق، جرّب طريقة أخرى كما ورد أعلاه. وإذا واجهت مشكلات مستمرة في الصيغ أو المراجع، فراجع صيغك مرة أخرى بحثًا عن مراجع الخلايا المطلقة (استخدام رمز)$)، وقم بالتعديل اللازم للحصول على النتيجة الصحيحة عند السحب أو النسخ.
افتح سحر إكسل مع KUTOOLS AI
- التنفيذ الذكي: نفِّذ عمليات الخلايا، وحلِّل البيانات، وأنشئ المخططات البيانية — كل ذلك بأوامر بسيطة!
- الصيغ المخصصة: أنشئ صيغًا مخصصة لتبسيط سير عملك.
- برمجة VBA: اكتب وأَنفِذ أكواد VBA بسلاسة تامة.
- تفسير الصيغ: افهم الصيغ المعقدة بسهولة!
- ترجمة النصوص: اكسر الحواجز اللغوية في جداولك الإلكترونية!
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل