كيفية استخدام مشغلي TrimRange & Trim Ref لترتيب جدول بيانات Excel

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

بناء الجملة trimrange

وظيفة trimrange لها ثلاث وسيطات:

=TRIMRANGE(a,b,c)

أين

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

إذا حذفت الحجج ب و/أو ج، سوف يتفوق Excel على تقليص كل من الصفوف و/أو الأعمدة الرائدة.

مثال 1: trimrange مع مبلغ

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

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

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

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

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

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

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

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

مثال 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 بجد أكبر مما يحتاج إليه ، وهي مشكلة قد تؤثر على الأداء إذا كان لديك الكثير من الصفوف الفارغة في صيغتك.

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

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

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

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

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

باستخدام مشغلات TREM مرجع

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

يتضمن استخدام مشغل REF TRIM إضافة فترة (.) على جانبي القولون أو كلا من القولون داخل الصيغة:

باستخدام نفس بيانات مشغل كرة القدم كما في المثال 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 ، مثل استخدام استعلام الطاقة أو الاستفادة القصوى من الذكاء الاصطناعي من خلال Copilot.