إجمالي مرات مشاهدة الصفحة

الخميس، 26 مايو 2016

إرجاع نتائج متعددة بشرط واحد في نطاق Concatenate Data With Single Criteria

السلام عليكم ورحمة الله وبركاته

أقدم لكم دالة معرفة User-Defined Function تقوم بالبحث في نطاق عن شرط محدد ، ثم تقوم بإرجع القيم المقابلة لهذا النطاق في أي عمود آخر ، أي أن الدالة ترجع قيم متعددة ، ويكون الناتج في خلية واحدة فقط

بفرض أن لديك مجموعة مواد دراسية في العمود الأول ، وفي العمود الثالث القيمة 1 في حالة النجاح والقيمة صفر في حالة الرسوب ، والمطلوب إضافة وتجميع مواد الرسوب في خلية واحدة

خطوات الحل :
------------
قم بوضع الدالة المعرفة في موديول عادي بهذا الشكل :

Function Concat(a As Variant, Optional sep As String = "") As String
    Dim y As Variant

    If TypeOf a Is Range Then
        For Each y In a.Cells
            Concat = Concat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            Concat = Concat & y & sep
        Next y
    Else
        Concat = Concat & a & sep
    End If

    Concat = Left(Concat, Len(Concat) - Len(sep))
End Function

في أي خلية وليكن الخلية A14 ضع المعادلة التالية (معادلة صفيف أي يجب الضغط على Ctrl + Shift + Enter)

=SUBSTITUTE(Concat(IF(C2:C11=0," * "&A2:A11,""))," * ","",1)

المعادلة تقوم بتجميع مواد الرسوب باستخدام الدالة المعرفة Concat ، ويمثل النطاق C2:C11 النطاق الذي يحتوي الشرط المطلوب اختباره والشرط هنا في المثال أن تكون قيمة الخلية في النطاق تساوي صفر .. ويمثل النطاق B2:B11 النطاق الذي سيتم جلب البيانات أو النتائج منه وهي هنا في المثال مواد الرسوب
يمكن استخدام أي فاصل في النتائج بخلاف علامة النجمة المستخدمة كما يحلو لك ، فقط قم بتغيير الفاصلة (علامة النجمة) إلى الفاصلة التي ترغب فيها ، في المعادلة السابقة (سيكون التغيير للفاصلة في موضعين)



 إعداد / ياسر خليل أبو البراء

هناك 5 تعليقات:

  1. دالة جميلة بارك الله فيك .

    ردحذف
  2. بارك الله فيك أخي العزيز أحمد الحاوي على مرورك العطر بالمدونة

    ردحذف
  3. أخي ياسر
    ارجو مراجعة الكود المتعلق إرجاع نتائج متعددة بشرط واحد في نطاق
    حيث انه عند تغيير مادة الأسلامية يظهر الصفر في المادة الثانية مع انه ناجح فيها
    تحياتي

    ردحذف
  4. أخي الكريم علي مرجان
    الموضوع لا يخص الرسوب النجاح وإنما التركيز على دالة جمع النتائج المتعددة في خلية واحدة .. يمكنك التعديل في المعادلة ليتناسب مع المرجع الصحيح حيث اطلعت على الملف ووجدت أنه يجب التعديل على المعادلة في الخلية C3 ليصبح المرجع الخلية B2 وليس B1 ثم قم بسحب المعادلة
    تقبل تحياتي

    ردحذف
  5. .انضم إلى eToro وقُد ثورة التكنولوجيا المالية

    eToro هي السوق الرائدة للجيل التالي من المتداولين والمستثمرين. بإمكانك تحقيق أرباح مباشرة من خلال التداول عبر الإنترنت وقيادة خمسة ملايين متداول اجتماعي حول العالم، والحصول على الأموال باعتبارك تمثل جيلاً جديداً من مدراء الصناديق عندما ينسخ الآخرون استثماراتك.

    ردحذف