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

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

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

300, 60, 10, 40, 80, 20, 120, 250

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

300+60+120 = 480    

300+60+40+80 = 480

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

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

ابتدا لیست اعداد را در ستون A وارد کرده و عدد هدف یعنی 480 را در سلول 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 اعمال کنید. پس از اینکار اعدادی که مجموع آن‌ها برابر با 480 می‌شود در ستون 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 را تیک بزنید عدد هدف یعنی 480 را وارد کنید.

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

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

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

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

استاندارد حسابداری شماره 1...