عدد مورد نظر جمع کدام اعداد است؟

فرض کنید لیست اعداد زیر را داریم و اکنون می‌خواهیم بدانیم ترکیب کدام یک از اعداد موجود در لیست برابر 480 است. در تصویر نشان داده‌شده در زیر، می‌توانید پنج گروه از ترکیب‌های احتمالی را مشاهده کنید که برابر می‌شوند با 480، مانند 300 + 60 + 120، یا 300 + 60 + 40 + 80، و غیره. در این مقاله، در مورد برخی از روش‌ها صحبت خواهیم کرد تا دریابیم که جمع کدام سلول‌ها در اکسل به یک مقدار خاص منتهی می‌شود.

• با فرمول‌نویسی
• با افزونه‌ی Slover
• با ماکرونویسی

یافتن ترکیب سلول‌هایی که با مجموع معینی برابر هستند با فرمول‌نویسی

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

1. لیست اعداد را انتخاب کنید و این لیست را به صورت یک نام بازه تعریف کنید - مقدار Range1 را در جعبه نام (Name Box) وارد کرده، و کلید Enter را فشار دهید تا تعریف نام بازه انجام گیرد، مطابق تصویر زیر:
2. بعد از مشخص کردن نام دامنه برای لیست اعداد، باید دو نام بازه‌ی دیگر در کادر Name Manager ایجاد کنید، بر روی تب Formulas و سپس Name Manager کلیک کنید، در کادر محاوره‌ای Name Manager، روی دکمه‌ی New کلیک کنید، به تصاویر زیر نگاه کنید:
3. در کادر New Name ظاهرشده، یک نام List1 را در قسمت Name بنویسد و فرمول زیر را در قسمت Refers to وارد کنید (Range1 نام بازه‌ای است که شما در گام 1 ایجاد کرده‌اید).
   =ROW(INDIRECT("1:"&ROWS(Range1)))
4. برای بازگشت به کادر نام Name Manager، دکمه‌ی OK را کلیک کنید و سپس بر روی دکمه‌ی New کلیک کنید تا نام بازه‌ی دیگری ایجاد کنید، در کادر New Name، نام List2 را وارد قسمت Name کنید و فرمول زیر را در قسمت Refers to وارد کنید (Range1 نام بازه‌ای است که شما در گام 1 ایجاد کرده‌اید).
   =ROW(INDIRECT("1:"&2^ROWS(Range1)))
5. پس از ایجاد نام‌های بازه، فرمول آرایه‌ای زیر را در سلول B1 بنویسید:

=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","")

و (به جای Enter) کلیدهای Shift + Ctrl + Enter را با هم فشار دهید، سپس دسته‌ی پر کردن را به پایین تا آخرین عدد لیست (در این‌جا سلول B8) بکشید؛ حال می‌توانید اعدادی را مشاهده کنید که مقدار کل آن 480 است که به صورت X در ستون B مشخص شده‌است. مانند تصویر زیر:

توجه:

• در فرمول طولانی بالا: List1، List2 و Range1 نام بازه‌ای است که شما در مراحل قبلی ایجاد کرده‌اید، C2 مقدار معینی است که می‌خواهید اعداد به آن جمع شوند.
• اگر بیش از یک ترکیب از مقادیر برابر با مقدار خاص باشد، فقط یک ترکیب لیست می‌شود.

یافتن ترکیب سلول‌هایی که با مجموع معینی برابر هستند با افزونه Solver

اگر با روش فوق سردرگم شده‌اید، اکسل حاوی یک ویژگی افزونه‌ی Solver است، با استفاده از این افزونه، می‌توانید اعدادی را شناسایی کنید که مقدار کل آن با یک مقدار معین برابر است.

1. ابتدا، باید افزونه‌ی Solver را فعال کنید، لطفاً به تب File سپس Options بروید، در کادر محاوره ای Excel Options، از سمت چپ پنجره بر روی Add-ins کلیک کنید، و سپس بر روی Solver Add-in از قسمت Inactive Application Add-ins کلیک کنید، به تصویر زیر نگاه کنید:
2. سپس بر روی دکمه‌ی Go کلیک کنید تا وارد کادر Add-Ins شوید، گزینه‌ی Solver Add-in را تیک بزنید و OK را کلیک کنید تا این افزونه با موفقیت نصب شود.
3. پس از فعال‌سازی افزونه‌ی Solver، باید این فرمول را در سلول B9 وارد کنید (B2:B9 یک ستونی خالی در کنار لیست اعداد شما است و A2:A9 لیست اعدادی است که شما استفاده می‌کنید):
   =SUMPRODUCT(B2:B9,A2:A9)
   حال کلید Enter را فشار دهید، مانند تصویر زیر:
4. سپس در تب Data بر روی Solver کلیک کنید تا به کادر محاوره‌ای Solver Parameter بروید، در کادر محاوره‌ای، مراحل زیر را انجام دهید:بر روی دکمه‌ی  کلیک کنید تا سلول B10 را که فرمول شما در آن قرار دارد، انتخاب کنید.سپس در قسمت To، گزینه‌ی Value Of را انتخاب کنید و مقدار مورد نظر خود را 480 وارد کنید.در بخش By Changing Variable Cells، روی دکمه‌ی  کلیک کنید تا بازه‌ی سلول B2:B9 را انتخاب کنید جایی که اعداد مربوطه‌ی شما علامت خواهند خورد.
5. و سپس بر روی دکمه Add کلیک کنید تا به کادر محاوره‌ای Add Constraint بروید، دکمه‌ی  را کلیک کنید تا بازه‌ی سلول B2:B9 را انتخاب کنید، و bin را از لیست کشویی انتخاب کنید، تصویر را مشاهده کنید:
6. برای بازگشت به کادر Solver Parameter، دکمه‌ی OK را کلیک کنید، سپس دکمه‌ی Solve را بزنید، چند دقیقه بعد، کادر محاوره‌ای Solver Results ظاهر می‌شود، و می‌توانید ترکیبی از سلول‌ها را ببینید که برابر با مقدار مشخص 480 هستند که به صورت 1 علامت‎گذاری شده‌اند. در کادر Solver Results، گزینه‌ی Keep Solver Solution را انتخاب کنید و برای خروج از کادر، روی OK کلیک کنید. تصویر زیر را مشاهده کنید:

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

یافتن ترکیب سلول‌هایی که با مجموع معینی برابر هستند با تابع تعریف‌شده توسط کاربر

هر دو روش اول برای اکثر کاربران اکسل ما پیچیده‌است، در اینجا می‌توانم یک کد VBA ایجاد کنیم تا این کار را به سرعت و به راحتی حل کند.

برای به دست آوردن نتیجه‌ی درست، ابتدا باید لیست اعداد را به ترتیب نزولی مرتب کنید؛ و سپس مراحل زیر انجام دهید:

1. کلیدهای ALT + F11 را نگه دارید تا پنجره‌ی Microsoft Visual Basic for Applications باز شود.
2. بر روی تب Insert سپس Module کلیک کنید و کد زیر را در پنجره‌ی Module قرار دهید.

   Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
   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

3. سپس پنجره‌ی کد را ببندید، و به ورک‌شیت برگردید و فرمول =getcombination(A2:A9,C2) را در یک سلول خالی وارد کنید و کلید Enter را فشار دهید، نتیجه‌ی زیر را بدست می‌آورید که اعداد ترکیبی را نشان می‌دهد؛ که برابر با مقدار معین است، به تصویر زیر مراجعه کنید:

توجه:

• در فرمول بالا ، A2:A9 بازه‌ی اعداد است و C2 حاوی مقدار هدف است که می‌خواهید با آن برابر باشد.
• اگر بیش از یک ترکیب از مقادیر برابر با مقدار خاص باشد، فقط یک ترکیب لیست می‌شود.

برگرفته شده از excelut.com