جستجو

ترفندهای کامپیوتری

حضرت علی (ع): «زَکوةُ العِلمِ نَشرُهُ؛ زکات علم نشر آن است».
وبلاگ "ترفندها" (سایت پی‌سی‌ترفند) برای عمل به این حدیث شریف و با هدف ارتقاء دانش و مهارت‌های مرتبط با رایانه در ۳۰ مهرماه ۱۳۹۱ ایجاد شده است. به امید آنکه مفید واقع شود.
🔵 کانال پی‌سی‌ترفند در تلگرام:
telegram.me/pctarfand_ir

خلاصه آمار

تبليغات

پربحث ترين ها

۱

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

اکسل

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

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

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

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 کلیک کنید.

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


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

برای ساخت ماکرو کلیدهای 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

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

=getcombination(A1:A8,C2)

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

منبع: extendoffice.com

 

عضویت در خبرنامه:

برچسب ها solver ماکروی اکسل

نظرات (۱)

  1. با سلام و عرض خسته نباشید
    اگه ممکن باشه نمونه فایل تمرین بالا رو یا تو سایت بگزارین یا برای بنده به صورت خصوصی ارسال کنین ممنون میشم. ضمنا مورد ضروری هستش
    از مطالب مفید شما هم بسیار متشکرم
    • پاسخ:

      سلام
      نمونه فایلی موجود نیست. مطلب بالا از سایت منبع ترجمه شده است.
      موفق باشید.

ارسال نظر

قبل از ارسال نظر به نکات زیر توجه کنید:

۱- با توجه با اینکه نظرات خصوصی شما امکان نمایش در سایت را ندارد، بنابراین هنگام ارسال نظر، گزینه "" را انتخاب نکنید. چون تنها راه پاسخگویی به آن از طریق ایمیل شما است که با توجه به مشغله کاری فرصت ارسال پاسخ از طریق ایمیل وجود ندارد.

۲- قبل از مطرح کردن سوالتان، مطلب فوق را با دقت مطالعه کنید. اگر پاسخ سوالتان را پیدا نکردید در سایت جستجو کنید، ممکن است در مطلب دیگر پاسخ سوالتان را پیدا کنید.

۳- تنها به سوالاتی پاسخ داده می‌شود که از نحوه حل آن‌ها اطلاع داشته باشم.

۴- نظراتی با مضمون زیر، صرفا تایید و نمایش داده می‌شود و به آن‌ها پاسخی داده نمی‌شود.

الف) در مورد پاسخ سوال شما اطلاعی نداشته باشم،

ب) پاسخ سوال شما در مطلب فوق وجود داشته باشد.

ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
شما میتوانید از این تگهای html استفاده کنید:
<b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
تجدید کد امنیتی