طریقه پیدا کردن ترکیبی از اعداد یک لیست در اکسل که مجموع آن‌ها برابر با یک مقدار معین باشد

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

لیست اعداد زیر را در نظر بگیرید.

۳۰۰, ۶۰, ۱۰, ۴۰, ۸۰, ۲۰, ۱۲۰, ۲۵۰

بطور مثال مجموع اعداد دو ترکیب زیر برابر با ۴۸۰ است:

۳۰۰+۶۰+۱۲۰ = ۴۸۰    

۳۰۰+۶۰+۴۰+۸۰ = ۴۸۰

اگر بخواهید اکسل برای شما این ترکیبات را پیدا کند سه روش وجود دارد:

روش اول) بوسیله فرمول

ابتدا لیست اعداد را در ستون A وارد کرده و عدد هدف یعنی ۴۸۰ را در سلول C2 تایپ کنید. در ادامه نیاز است تا چند نام محدوده ایجاد کنید. برای اینکار مراحل زیر را طی کنید:

۱- لیست اعداد را انتخاب کنید و در باکس نام که در شکل زیر نشان داده شده است نام Range1 را تایپ کنید و کلید اینتر را بفشارید.

۲- نیاز است علاوه بر نام بالا، دو نام دیگر نیز تعریف کنید. برای اینکار از تب Formulas روی دکمه Name Manager کلیک کنید تا کادر Name Manager باز شود. روی دکمه New کلیک کنید.

۳- در کادر باز شده، در قسمت Name نام List1 را تایپ کنید و در قسمت Refers to فرمول زیر را وارد کنید.

=ROW(INDIRECT(“1:”&ROWS(Range1)))

۴- روی OK کلیک کنید تا به کادر Name Manager برگردید. بار دیگر روی دکمه New کلیک کنید و در کادر New Name در قسمت Name نام List2 را تایپ کنید و در قسمت Refers to فرمول زیر را وارد کنید.

=ROW(INDIRECT(“1:”&2^ROWS(Range1)))

۵- در ادامه فرمول آرایه‌ای زیر را در سلول B1 وارد کنید. پس از وارد کردن فرمول، کلیدهای Ctrl + Shift+ Enter را همزمان فشار دهید. سپس فرمول را تا سلول B8 اعمال کنید. پس از اینکار اعدادی که مجموع آن‌ها برابر با ۴۸۰ می‌شود در ستون B با X نشان داده می‌شود.

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")

لازم به ذکر است اگر چند ترکیب مختلف وجود داشته باشد این روش تنها یک ترکیب ممکن را پیدا می‌کند.

روش دوم) بوسیله افزونه Solver

اگر موفق به استفاده از روش بالا نشدید اکسل دارای یک افزونه است که با آن نیز می‌توانید اینکار را انجام دهید.

۱- ابتدا باید افزونه Solver add-in را فعال کنید. برای اینکار از تب File روی گزینه Options کلیک کنید. در کادر Excel Options (تصویر زیر) در سمت چپ روی گزینه Add-Ins و سپس در قسمت Inactive Application Add-ins روی Solver Add-in کلیک کنید.

۲- در پایین کادر Excel Options در تصویر بالا روی دکمه Go کلیک کنید تا کادر Add-Ins باز شود. گزینه Solver Add-in را تیک بزنید و روی OK کلیک کنید تا این افزونه نصب شود.

۳- پس از فعال کردن افزونه، فرمول زیر را در سلول B9 وارد کنید و کلید اینتر را بفشارید.

=SUMPRODUCT(B1:B8,A1:A8)

۴- سپس از تب Data روی Solver کلیک کنید تا کادر Solver Parameter باز شود.

(شماره ۱) در قسمت Set Objective روی دکمه نشان داده شده کلیک کنید و سلول B9 را انتخاب کنید.

(شماره ۲) سپس در قسمت To گزینه Value Of را تیک بزنید عدد هدف یعنی ۴۸۰ را وارد کنید.

(شماره ۳) در قسمت By Changing Variable Cells روی دکمه نشان داده شده کلیک کنید و سلول‌های B1:B8 را انتخاب کنید.

۵- سپس روی دکمه Add کلیک کنید تا کادر Add Constraint باز شود. روی دکمه نشان داده شده کلیک کنید و سلول‌های B1:B8 را انتخاب کرده و از لیست کشویی روی گزینه bin کلیک کنید.

۶- روی دکمه OK کلیک کنید تا به کادر Solver Parameter باز گردید. سپس روی دکمه Solve کلیک کنید، چند دقیقه بعد کادر Solver Results (تصویر زیر) باز می‌شود و ترکیب سلول‌هایی که مجموع آن‌ها برابر با ۴۸۰ است با علامت ۱ نشان داده می‌شود. در کادر Solver Results گزینه Keep Solver Solution را تیک بزنید و روی OK کلیک کنید.

لازم به ذکر است در این روش نیز اگر چند ترکیب مختلف وجود داشته باشد تنها یک ترکیب ممکن پیدا می‌شود.

روش سوم) بوسیله ماکرو

برای ساخت ماکرو کلیدهای Alt + F11 را فشار دهید یا از تب Developer قسمت code گزینه Visual Basic را انتخاب کنید. پنجره Microsoft Visual Basic ظاهر می‌شود. در این پنجره از تب Insert گزینه Module را انتخاب نمائید.

در پنجره جدید باز شده کدهای زیر را کپی کنید.

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'pctarfand.ir & tarfandha.blog.ir'
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

سپس فایل را ذخیره کرده و پنجره را ببندید. در یک سلول خالی فرمول زیر را وارد کنید و کلیک اینتر را فشار دهید تا نتیجه زیر حاصل شود.