كيفية تحويل القيم وربطها في Excel؟
في Excel، غالبًا ما يتطلب العمل مع البيانات إعادة تنظيم المعلومات أو عرضها بتنسيق مختلف. على سبيل المثال، قد تحتاج إلى تحوير نطاق — أي تحويل الصفوف إلى أعمدة أو العكس — مع الحفاظ على ارتباط القيم المحورة بالبيانات الأصلية، بحيث تنعكس أي تحديثات تطرأ على المصدر فورًا في النطاق المحوَّر. بشكل افتراضي، يتيح لك Excel لصق البيانات المنسوخة كروابط، كما يدعم تنفيذ عمليات التحوير الأساسية، لكن الجمع بين هاتين الوظيفتين في خطوة واحدة ليس مدعومًا مباشرةً. يقدّم هذا المقال عدة طرق فعّالة لتحويل البيانات وربطها في آنٍ واحد، مصحوبة بإرشادات خطوة بخطوة ونصائح عملية لمساعدتك على إنجاز المهمة بسلاسة.
تحوير وربط القيم الملصقة باستخدام اسم وصيغة
التحوير والربط باستخدام دالة البحث والاستبدال
التحوير والربط باستخدام ماكرو VBA لتحديثات ديناميكية
تحوير وربط القيم الملصقة باستخدام اسم وصيغة
تسمح لك هذه الطريقة بتحويل مجموعة من القيم مع الحفاظ على ارتباطها الديناميكي بالبيانات الأصلية. وباستخدام نطاق مسمّى وصيغة، ستظهر أي تغييرات تُجرى على البيانات الأصلية فورًا في المجموعة المحولة.
1. حدد نطاق البيانات الذي تريد تحويله وربطه. في مربع الاسم (الموجود بجانب شريط الصيغة)، اكتب اسمًا وصفيًا لهذا النطاق (مثل SPORT)، ثم اضغط علىEnter. يساعدك تسمية النطاق على الرجوع إليه بسهولة في جميع أنحاء ملف العمل.
2. إذا كنت تستخدم Excel 365 أو Excel 2021 أو إصدارًا أحدث، فحدّد خلية واحدة لتبدأ فيها البيانات المحورة، وأدخل الصيغة=TRANSPOSE(SPORT)(استبدل «SPORT» باسم نطاق الخلايا الخاص بك)، ثم اضغط علىEnter. وسيقوم Excel تلقائيًا بتوزيع البيانات المحورة عبر خلايا متعددة.
وإذا كنت تستخدم Excel 2019 أو إصدارًا أقدم، فحدّد الخلايا المستهدفة التي تريد عرض البيانات المحورة فيها، واكتب نفس الصيغة، ثم اضغط علىShift + Ctrl + Enter لإنشاء صيغة مصفوفية.
ستظهر بياناتك المحورية وستظل متزامنة تلقائيًا مع البيانات الأصلية — فكلما تم تعديل البيانات في النطاق المسمى، يُحدَّث العرض المحوري على الفور.
نصائح واحتياطات:
- في إصدار Excel 2019 وما قبله، تتطلب الصيغ المصفوفية تحديد نطاق يطابق شكل الإخراج؛ فعلى سبيل المثال، إذا احتوت البيانات الأصل日晚间 على 5 صفوف، فعليك تحديد 5 أعمدة قبل إدخال الصيغة.
- تجعل النطاقات المسماة الصيغ أكثر سهولة وموثوقية – تجنّب استخدام أسماء عامة، وتأكد من كتابة الأسماء بدقة.
- إذا احتجت لاحقًا إلى توسيع نطاق البيانات الأصلي، فعليك تحديث مرجع النطاق المسمى وفقًا لذلك، ليتمكّن النطاق المنقول (المُحوَّل) من التقاط البيانات الجديدة.
- إذا ظهرت أخطاء (#VALUE!)، فتأكد من خلايا التحديد مرة أخرى، وأكمل إدخال الصيغة باستخدامShift + Ctrl + Enter، وليس فقطEnter.
التحوير والربط باستخدام دالة البحث والاستبدال
تستخدم هذه التقنية دالة البحث والاستبدال في Excel مع خيارات لصق خاص لتحقيق التحوير والربط معًا. وعلى الرغم من أنها تتّبع أسلوبًا غير مباشر نوعًا ما، إلا أنها تُعدّ خيارًا مفيدًا عندما لا تكون الصيغ المصفوفية مناسبة، أو للمستخدمين الذين يفضلون الاعتماد على خطوات قائمة على القوائم.
1. حدّد نطاق البيانات الأصلي، ثم اضغط علىCtrl + C للنسخ. بعد ذلك، انقر بزر الماوس الأيمن على الخلية التي تريد لصق البيانات فيها، واختر من القائمة الفرعيةلصق خاصالخيارLink. بهذه الطريقة، ستُلصَق روابط إلى البيانات بدلاً من قيمها الثابتة.
2. الآن، افتح مربع حوارالبحث والاستبدالبالضغط علىCtrl + H. في مربعFind what، أدخل=، وفي مربعReplace with، اكتب#=. يؤدي ذلك مؤقتًا إلى تغيير الصيغ لتجنب حدوث مشكلات أثناء التحرير لاحقًا.
3. انقر علىReplace All. ستظهر نافذة تعرض عدد الاستبدالات التي تمت. انقر علىOK، ثم علىClose.
4. انقل النطاق المعدّل بالضغط علىCtrl + C مرة أخرى. ثم انقر بزر الماوس الأيمن على الوجهة المستهدفة، وحددلصق خاص، ثم اخترTranspose. وسيقوم ذلك تلقائيًا بتبديل الاتجاه نيابةً عنك.
5. مع بقاء البيانات المحورة محددة، افتحالبحث والاستبدالمجددًا ()Ctrl + H). فيFind what، اكتب#=؛ وفيReplace with، أدخل= لاستعادة الروابط النشطة.
6. اخترReplace All مرة أخرى، وقم بتأكيد النافذة المنبثقة، ثم أغلقها. الآن، أصبحت قيمك محورة ومرتبطة بالمدخلات الأصلية.
نصائح واحتياطات:
- تأكد من استبدال جميع علامات التساوي (=) قبل تنفيذ عملية التحوير، وإلا فقد تواجه أخطاءً في الصيغ.
- بعد الانتهاء من الخطوات، تحقق مجددًا من أن القيم المحورية تتغيّر فور تعديل البيانات الأصلية، مما يؤكد أن الارتباط لا يزال نشطًا.
- إذا واجهت أخطاءً في الصيغ، فتأكد من مراجعة إجراءات البحث والاستبدال وكذلك تحديدات الخلايا الخاصة بك.
التحوير والربط باستخدام ماكرو VBA لتحديثات ديناميكية
بالنسبة للمستخدمين الذين يحتاجون إلى حل قابل للتوسع وأوتوماتيكي – مثل الحالات التي يغطي فيها النطاق المرتبط والمحور نطاقًا واسعًا، أو عند الحاجة إلى تحديثات متكررة – يمكن استخدام ماكرو VBA لإنشاء روابط محورة ديناميكيًا. تعد هذه الطريقة مفيدة بشكل خاص في الأوراق الكبيرة أو التي يتم تعديلها كثيرًا، حيث تكون الطرق اليدوية تستغرق وقتًا طويلاً، وتريد أن يظل النطاق المحور دائمًا محدّثًا وفقًا لـ البيانات الأصلية.
1. انتقل إلى تبويبDeveloper، ثم انقر علىVisual Basic لفتح محررMicrosoft Visual Basic for Applications. إذا لم يكن تبويب Developer ظاهرًا، راجع هذا الدليل: Show the Developer tab in Excel.
2. في محرر VBA، انقر علىInsert > Module لإنشاء وحدة جديدة. بعد ذلك، الصق الكود التالي في نافذة المحرر:
Sub TransposeLinkDynamic()
Dim SourceRange As Range
Dim DestCell As Range
Dim r As Long, c As Long
Dim nRows As Long, nCols As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SourceRange = Application.InputBox("Select the range to transpose and link", xTitleId, "", Type:=8)
Set DestCell = Application.InputBox("Select the top-left cell of transposed linked output", xTitleId, "", Type:=8)
nRows = SourceRange.Rows.Count
nCols = SourceRange.Columns.Count
For r = 1 To nCols
For c = 1 To nRows
DestCell.Offset(r - 1, c - 1).Formula = "=" & SourceRange.Cells(c, r).Address(ReferenceStyle:=xlA1, External:=True)
Next c
Next r
End Sub3. لتشغيل الماكرو، انقر على زر
أو اضغط علىF5. سيتم مطالبتك أولًا بتحديد نطاق المصدر الذي تريد تحويله. بعد ذلك، اختر الخلية العلوية اليسرى لمنطقة الإخراج، حيث ستظهر البيانات المحورة والمرتبطة.
ستحتوي جميع البيانات في منطقة الإخراج على صيغ مرتبطة ديناميكيًا بالنطاق الأصلي، لكنها تظهر بتنسيق محور. وهذا يعني أن أي تحديثات تُجريها على نطاق البيانات الأصلي ستنعكس فورًا في المنطقة المحورية المرتبطة، دون الحاجة إلى إعادة تنفيذ العملية.
نصائح واحتياطات:
- تُبنى جميع الصيغ ديناميكيًا – فإذا قمت بنقل نطاق المصدر أو تغيير حجمه، فما عليك سوى إعادة تشغيل الماكرو عند الحاجة.
- إذا ظهرت أخطاء تتعلق بالأذونات أو لم تتمكن من تشغيل الماكرو، فتأكد من تفعيل إعدادات أمان الماكرو لملف العمل الخاص بك، ثم احفظ الملف بصيغة .xlsm.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل