وبلاگ شخصی -احمد سوری

فرض کنید لیست اعداد زیر را داریم و اکنون می‌خواهیم بدانیم ترکیب کدام یک از اعداد موجود در لیست برابر 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 مقدار معینی است که می‌خواهید اعداد به آن جمع شوند.
• اگر بیش از یک ترکیب از مقادیر برابر با مقدار خاص باشد، فقط یک ترکیب لیست می‌شود.