تنظيم جداول بيانات Excel باستخدام مشغلي TRIMRANGE وTrim Ref

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

هل تبحث عن طريقة لترتيب جدول البيانات الخاص بك وتقليل حجمه دون الحاجة إلى استخدام صيغ النطاق الديناميكي مثل OFFSET أو INDEX أو TOCOL؟ يكتشف TRIMRANGE الخلايا التي تشغلها بياناتك ويقوم تلقائيًا بتوسيعها وتقليصها معها.

ملاحظة
يعد عامل Trim Ref نسخة مختصرة أبسط من دالة TRIMRANGE، وسأتناول هذا لاحقًا في المقالة.

Table of Contents

بنية TRIMRANGE

تحتوي دالة TRIMRANGE على ثلاث وسيطات:

=TRIMRANGE(a,b,c)

حيث

a (مطلوب) هو النطاق المراد قصه،
b (اختياري) يحدد الصفوف المراد قصها (0 = لا توجد صفوف، 1 = صفوف بادئة، 2 = صفوف لاحقة، و3 = كل من الصفوف البادئة واللاحقة)، و
c (اختياري) يحدد الأعمدة المراد قصها (0 = لا توجد أعمدة، 1 = أعمدة بادئة، 2 = أعمدة لاحقة، و3 = كل من الأعمدة البادئة واللاحقة).

إذا حذفت الوسيطتين b و/أو c، فسيقوم Excel افتراضيًا بقص كل من الصفوف و/أو الأعمدة البادئة واللاحقة.

ملاحظة
في وقت كتابة هذا (يناير 2025)، لا يسمح لك TRIMRANGE بقص الصفوف أو الأعمدة الفارغة بين البيانات الموجودة. يمكنك فقط استخدامه لقص البيانات قبل أو بعد بياناتك. بدلاً من ذلك، استخدم طرقًا أخرى لحذف صفوف البيانات الفارغة بين الصفوف المملوءة بالبيانات.

المثال 1: TRIMRANGE مع المجموع

في هذا المثال، أريد طرح القيم الموجودة في العمود B من تلك الموجودة في العمود C لإنتاج ربح.

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

=(C2:C200)-(B2:B200)

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

بدلا من ذلك، سأكتب:

=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)

في الخلية D2، وهو ما من شأنه أن يقوم بشكل فعال بتقليص الخلايا المكررة في أسفل بياناتي لمنع Excel من العمل بجهد كبير والحفاظ على مظهر جدول البيانات الخاص بي مرتبًا.

ملاحظة
لقد حذفت الوسيطتين b وc في مراجع TRIMRANGE أعلاه، حيث يعمل الإعداد الافتراضي لبرنامج Excel لقص الأعمدة والصفوف البادئة واللاحقة بشكل جيد في هذا المثال، حيث لا توجد صفوف أو أعمدة بادئة فارغة، ولا توجد بيانات على يمين العمود D.

الآن، عندما أضيف المزيد من صفوف البيانات في الأسفل، سيتم حساب الخلايا المقابلة في عمود الربح تلقائيًا.

المثال 2: TRIMRANGE باستخدام XLOOKUP

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

أعلم أيضًا أنني سأقوم بتجنيد خمسة لاعبين آخرين، لذا عندما أضيف صيغة XLOOKUP، أحتاج إلى توسيعها إلى الصف 22. لذا، في الخلية E2، يمكنني كتابة:

=XLOOKUP(B2:B22,$G$2:$G$7,$H$2:$H$7)

حيث B2:B22 هو النطاق، و$G$2:$G$7 هي مصفوفة البحث، و$H$2:$H$7 هي مصفوفة الإرجاع. لقد استخدمت رموز $ لإخبار Excel بأن هذه مراجع خلايا مطلقة (ثابتة). ومع ذلك، كما هو الحال مع المثال السابق، سيؤدي هذا إلى ترك الصفوف الخمسة من البيانات الفارغة تبدو غير مرتبة بسبب الخطأ #N/A. أيضًا، يعمل Excel بجهد أكبر مما ينبغي، وهي مشكلة قد تؤثر على الأداء إذا كان لديك الكثير من الصفوف الفارغة المضمنة في الصيغة الخاصة بك.

يمكنني أيضًا استخدام OFFSET أو INDEX أو TOCOL لإنشاء صيغ النطاق الديناميكي، ولكن هذه طرق أكثر تعقيدًا لتحقيق نفس النتيجة مثل طريقة TRIMRANGE الأكثر بساطة.

لذا، في الخلية E2، سأكتب:

=XLOOKUP(TRIMRANGE(B2:B22,2),$G$2:$G$7,$H$2:$H$7)

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

هذه المرة، قام Excel بقص نتيجة XLOOKUP الخاصة بي، وعندما أضيف المزيد من صفوف البيانات في الأسفل، يتم ملء الخلية في العمود E تلقائيًا.

استخدام عوامل Trim Ref

من المؤكد أن بعض الصيغ التي استخدمتها في الأمثلة أعلاه معقدة للغاية. ولهذا السبب قدمت Microsoft أيضًا عوامل Trim Ref، وهي نسخة مختصرة من دالة TRIMRANGE. بعبارة أخرى، توفر عوامل Trim Ref عليك عدم الاضطرار إلى تضمين TRIMRANGE داخل وظائف أخرى. علاوة على ذلك، لا تتطلب عوامل Trim Ref منك تحديد ما إذا كنت تقوم بقص الصفوف أو الأعمدة، حيث تقوم تلقائيًا بقص كليهما.

يتضمن استخدام عامل Trim Ref إضافة نقطة (.) على أحد جانبي النقطتين أو كليهما داخل الصيغة الخاصة بك:

أين يتم إضافة النقطة

أي المسافات يتم تقليمها

بعد النقطتين الرأسيتين

المسافات الزائدة في النهاية

قبل النقطتين الرأسيتين

المسافات الزائدة في البداية

كلا جانبي النقطتين الرأسيتين

المسافات الزائدة في البداية والنهاية

ملاحظة
نظرًا لأن عوامل Trim Ref عبارة عن “نقطة” واحدة في الصيغة الخاصة بك، فقد يكون من الصعب اكتشافها عند مراجعة عملك أو جدول بيانات شخص آخر. إنه أمر يجب الانتباه إليه إذا لم تكن الأمور تسير كما تتوقع!

باستخدام نفس بيانات لاعب كرة القدم كما في المثال 2، في الخلية E2، سأكتب:

=XLOOKUP(B2:.B22,$G$2:$G$7,$H$2:$H$7)

لاحظ كيف أضفت نقطة بعد النقطتين في B2:.B22. وذلك لأنني أريد من Excel أن يقص الفراغات اللاحقة. تُظهر لقطة الشاشة أدناه نفس النتيجة، على الرغم من أنني استخدمت صيغة أكثر وضوحًا دون تضمين دالة TRIMRANGE داخل الصيغة الخاصة بي.

على سبيل المثال، عندما أستخدم وظيفة TRIMRANGE، أعلم أنه يمكنني إضافة المزيد من صفوف البيانات في الأسفل، وستُطبق وظيفة XLOOKUP على تلك البيانات.

لماذا تستخدم TRIMRANGE وTrim Refs بدلاً من الجداول المنظمة؟

قد تزعم بحق أنه يجب عليك بدلاً من ذلك تقديم بياناتك في جداول Excel منسقة واستخدام مراجع منظمة، والتي تتوسع تلقائيًا لتشمل بيانات جديدة وأي حسابات ذات صلة تقوم بإنشائها. في معظم المواقف، أوافق على ذلك.

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

إلى جانب وظيفة TRIMRANGE في Excel ومشغلي Trim Refs، هناك طرق أخرى لتنظيف بياناتك في Excel، مثل استخدام Power Query أو تحقيق أقصى استفادة من الذكاء الاصطناعي من خلال Copilot.