مقادیر پیوسته از تعداد متغیر سلول در اکسل

Question

Pam دو ستون داده دارد. در ستون A شناسه های ساده مانند A، B، C و غیره وجود دارد. در ستون B یک سری مقادیر صحیح وجود دارد. او می تواند داده ها را بر اساس شناسه و ثانیاً بر اساس مقادیر صحیح مرتب کند. حالا او می‌خواهد در ستون C فرمولی داشته باشد که تمام مقادیر صحیح را برای یک شناسه خاص به هم متصل کند. بنابراین، اگر A1:A4 همه حاوی شناسه A باشند، در سلول C1 او می‌خواهد تمام مقادیر B1:B4 به هم پیوسته و با کاما تقسیم شود، مانند "11، 17، 19، 25". از آنجایی که تعداد ردیف‌ها برای هر شناسه می‌تواند متفاوت باشد، Pam مطمئن نیست که چگونه الحاق را انجام دهد.

ساده ترین راه برای انجام این کار استفاده از یک ماکرو است که می تواند به عنوان یک تابع تعریف شده توسط کاربر ایجاد شود. یک مثال در اینجا آمده است:

Function CatSame(c As Range) As String
    Application.Volatile
    sTemp = ""
    iCurCol = c.Column
    If iCurCol = 3 Then
        If c.Row = 1 Then
            sLast = ""
        Else
            sLast = c.Offset(-1, -2)
        End If
        If c.Offset(0, -2) <> sLast Then
            J = 0
            Do
                sTemp = sTemp & ", " & c.Offset(J, -1)
                J = J + 1
            Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)
            sTemp = Right(sTemp, Len(sTemp) - 2)
        End If
    End If
    CatSame = sTemp
End Function

این تابع اساساً مقداری را می گیرد که به آن ارسال می شود (یک مرجع سلول) و تأیید می کند که مرجع سلول برای ستون C است. اگر اینطور باشد، سپس شروع به الحاق مقادیر از ستون B بر اساس مقادیر ستون A می کند. اگر مقدار در ستون A با مقدار ردیف بالای آن متفاوت باشد، رشته مقادیر به هم پیوسته را برمی گرداند. با فرض اینکه شناسه های شما در ستون A هستند و مقادیری که باید به هم متصل شوند در ستون B هستند، می توانید موارد زیر را در ستون C قرار دهید:

=CatSame(C1)

این را تا جایی که لازم است در ستون C کپی کنید و در نهایت دقیقاً همان چیزی را خواهید داشت که پم می خواست.

یک تابع همه کاره تر، عملکردی است که تا حدودی مانند VLOOKUP عمل می کند، اما فهرستی به هم پیوسته از مقادیر را باز می گرداند که با هر چیزی که به دنبال آن هستید مطابقت دارد. تابع زیر را در نظر بگیرید:

Function VLookupAll(vValue, rngAll As Range, _
  iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler

    Application.Volatile
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
        If rCell.Value = vValue Then _
          VLookupAll = VLookupAll & sSep & _
          rCell.Offset(0, iCol).Value
    Next rCell

    If VLookupAll = "" Then
        VLookupAll = CVErr(xlErrNA)
    Else
        VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
    End If
ErrHandler:
    If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
End Function

این تابع تا چهار آرگومان می گیرد. اولین مورد مقداری است که می خواهید در جستجوی خود مطابقت دهید. در مثال Pams، این شناسه‌ای است که شما می‌خواهید، مانند A، B یا C. آرگومان دوم محدوده سلول‌هایی است که در آن به دنبال موارد منطبق می‌گردید (ستون A در این مورد). آرگومان سوم یک افست (از محدوده در آرگومان دوم) است که مقادیری را که می‌خواهید به هم متصل شوند را نشان می‌دهد. شما می توانید تابع را به این صورت استفاده کنید:

=VLookupAll("B",A1:A99,1)

اگر می خواهید یک جداکننده متفاوت بین مقادیر مشخص کنید، می توانید آن را با استفاده از آرگومان چهارم اختیاری انجام دهید. به عنوان مثال، موارد زیر رشته ای را برمی گرداند که در آن یک خط تیره هر مقدار را جدا می کند:

=VLookupAll("B",A1:A99,1,"-")

راه حل ها تا کنون بر استفاده از ماکروها متمرکز بوده اند. دلیل این امر نسبتاً ساده است: راه حلی مبتنی بر فرمول وجود ندارد که بتواند آنچه را که Pam به آن نیاز دارد انجام دهد. استفاده از دستورات IF تو در تو برای ارزیابی آنچه در ستون A وجود دارد به خوبی کار نمی کند زیرا شما در عمق اینکه عبارات IF می توانند تودرتو شوند محدود هستید.

اگر اشکالی ندارید که مقادیر به هم پیوسته در آخرین نمونه از یک شناسه در ستون A باشد، می توانید از یک فرمول و یک نتیجه میانی استفاده کنید. با قرار دادن این فرمول در سلول C1 شروع کنید:

=B1

این فرمول باید وارد سلول C2 شود:

=IF(A2=A1,C1 & ", " & B2, B2)

این فرمول را به تعداد ردیف های مورد نیاز کپی کنید. چیزی که در نهایت به آن می‌رسید یک سری طولانی‌تر از مقادیر به هم پیوسته در ستون C است که طولانی‌ترین آنها در هر اجرا در همان ردیف با آخرین شناسه متوالی در ستون A است. سپس می‌توانید موارد زیر را در تمام سلول‌های کاربردی ستون قرار دهید. د:

=IF(LEN(C2)>LEN(C1),"",C1)

این فرمول تنها طولانی‌ترین رشته‌ها را از ستون C نشان می‌دهد، چیزی که Pam برای شروع به آن نیاز داشت.