جمع بندی بر اساس بخشی از اطلاعات در یک سلول در اکسل

Question

کتی یک کاربرگ دارد که شامل اطلاعات تمام قطعات در انبارش است. در این برگه، شماره قطعات در ستون A با فرمت 12345 XXX نشان داده شده است، جایی که XXX نشان دهنده یک کد مکان است. این بدان معناست که او می‌تواند چندین ورودی را برای شماره‌های قطعه یکسان در کاربرگ داشته باشد، اما هر ورودی مکان متفاوتی را برای آن قسمت نشان می‌دهد. کتی به فرمولی نیاز دارد که مقادیر مربوط به هر قطعه را بدون توجه به کد مکان آن جمع کند. بنابراین، او به راهی برای جمع کردن ستون کمیت مربوط به قطعات 12345 ABC، 12345 DEF، 12345 GHI و غیره نیاز دارد. او به راهی برای انجام این کار بدون تقسیم کد مکان به ستون دیگری نیاز دارد.

بیش از یک راه برای دریافت پاسخ مورد نظر وجود دارد. برای مثال‌های این نکته، فرض کنید که شماره‌های قطعه در ستون A (همانطور که کتی نشان داد) و مقادیر هر قسمت در ستون B هستند. این مقادیر هستند که باید جمع شوند، فقط بر اساس یک بخشی از آنچه در هر سلول در ستون A وجود دارد. علاوه بر این، می توانید شماره قطعه (منهای کد مکان) مورد نظر را در سلول D2 قرار دهید.

اولین راه حل بالقوه استفاده از تابع SUMPRODUCT به این صورت است:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

این فرمول مقادیر در محدوده A2:A49 را بررسی می کند. باید مطمئن شوید که این محدوده بازتاب داده های واقعی شماست. اگر فرمول را طوری تعمیم دهید که به تمام ستون‌های A و B نگاه کند (مانند A:A و B:B)، یک خطای #VALUE دریافت می‌کنید، زیرا سعی می‌کند فرمول را برای سلول‌های خالی ستون‌ها اعمال کند.

با استفاده از فرمول آرایه ای مانند زیر می توانید نتیجه مشابهی بدست آورید:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

دوباره به یاد داشته باشید که این یک فرمول آرایه است، بنابراین باید با فشار دادن Shift+Ctrl+Enter آن را وارد کنید. همچنین توجه داشته باشید که این فرمول مقدار D2 را به متن برای مقایسه تبدیل می کند. این در فرمول قبلی انجام نشد زیرا در آنجا رشته فرعی انتخاب شده از ستون A با استفاده از تابع VALUE به یک مقدار عددی تبدیل شد.

همچنین می توانید از تابع DSUM برای ساخت فرمول کاری استفاده کنید. اجازه دهید فرض کنیم که شماره قسمت (ستون A) یک عنوان ستون در سلول A1 دارد. این هدر ستون (مانند "Part Num") را در سلول دیگری در کاربرگ مانند سلول D1 کپی کنید. در سلول D2، شماره قطعه را بدون کد محل آن و به دنبال آن یک ستاره وارد کنید. به عنوان مثال، می توانید "12345*" (بدون علامت نقل قول) را در سلول D2 وارد کنید. با تنظیم آن مشخصات، می توانید از این فرمول استفاده کنید:

=DSUM($A$1:$B$49,$B$1,D1:D2)

این فرمول از مشخصات موجود در سلول D2 (کاراکترهای 12345 به دنبال هر چیزی) به عنوان کلیدی استفاده می کند که مقادیر ستون B باید برای آن جمع شوند.

در نهایت، اگر در سلول D2 همان مشخصاتی را داشتید که با رویکرد DSUM استفاده کردید، می‌توانید از یک تابع SUMIF بسیار ساده به این صورت استفاده کنید:

=SUMIF(A:A,D2,B:B)

توجه داشته باشید که این روش به شما امکان می دهد از محدوده ستون کامل (A:A و B:B) در فرمول استفاده کنید.

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

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _
  FindPart As Variant) As Long
    Dim Pos As Integer
    Dim Pos2 As Integer
    Dim i As Long
    Dim tmp As String
    Dim tmpSum As Long
    Dim PC As Long

    PC = Parts.Count
    If PartsQty.Count <> PC Then
        MsgBox "Parts and PartsQty must be the same length", vbCritical
        Exit Function
    End If

    For i = 1 To PC
        Pos = InStr(1, Parts(i), " ")
        Pos2 = InStr(Pos + 1, Parts(i), " ")

        If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then
            tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))
        ElseIf Pos > 0 And Len(Parts(i)) > 0 Then
            tmp = CStr(Trim(Left(Parts(i), Pos - 1)))
        End If

        If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then
            tmpSum = tmpSum + PartStock(i)
        End If
    Next i

    AddPrtQty = tmpSum
End Function

برای استفاده از تابع، در کاربرگ خود آن را با استفاده از دو محدوده و شماره قطعه مورد نظر خود فراخوانی کنید:

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")