جستجو

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

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

خلاصه آمار

تبليغات

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

اکسل ماکرونویسی

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

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

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

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

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


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

ابتدا فایل اکسلی که حاوی داده‌های مدنظرتان است را باز کرده و سپس فایل اکسل فوق که دانلود کردید را اجرا کنید. با اینکار پنجره‌ای باز می‌شود. روی دکمه Enable Macros کلیک کنید.

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

=getcombination(A1:A8,C2)

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


روش سوم)

بوسیله افزونه 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 کلیک کنید.

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

منبع: extendoffice.com

 

برای مطلع شدن از جدیدترین مطالب سایت، کانال‌ پی‌سی‌ترفند در تلگرام را با آی‌دی pctarfandir@ دنبال کنید:

          

سوالات خود در زمینه ورد و اکسل و ... را می‌توانید از طریق ایمیل و تلگرام با ما مطرح کنید.

آدرس ایمیل: tarfandha.blog@gmail.com

آی‌دی تلگرام: pctarfand_admin@

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

نظرات (۱۷)

  1. سلام من همه روشها را امتحان کردم و فایل ماکرو را خریدم ولی اصلا کار نکرد و هیچ اتفاقی نیفتاد
    • پاسخ:

      سلام
      ممنون از خرید شما.
      ایمیلتان را چک کنید.
  2. سلام من روش اول رو استفاده کردم در پنجره اول که رنج ۱ رو میدم داخل کادر رفرنس فرمول فایند رو میزنم ولی در اخر کلید ترکیبی رو میزنم یه پنجره باز میشه بایت آپدیت فرمول رو اجرا نمیکند
    • پاسخ:

      سلام
      دقیقا متوجه مشکل نشدم!
    • پاسخ:

      سلام
      همه روش ها رو امتحان کردید؟
    • پاسخ:

      سلام
      چه خطایی؟
    • پاسخ:

      سلام
      متاسفانه در این زمینه اطلاعی ندارم.
    • پاسخ:

      سلام
      دلیل مشکل را نمی دانم. با روش های دیگر هم امتحان کرده اید؟
  3. سلام فرمول دوم را که امتحان می کنم برای اعداد زیاد خیلی سرچ می کنه و جواب رو نمیده (SUMPRODUCT)
    • پاسخ:

      سلام
      منظورتان روش دوم است؟
  4. این مطلب رو چند وقت پیش در یک سایت انگلیسی دیدم و فرمول اصلی که در ستون b1 بایستی تایپ بشه دارای خطای فرمولی هست و اکسل قبولش نمی کنه
    اگر امکانش هست خودتون فرمول رو برسسی و اصلاح بفرمائید.
    تشکر
    • پاسخ:

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

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

ارسال نظر

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

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

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

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

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

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

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

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