انتقل إلى المحتوى الرئيسي

كيف تجد كل المجموعات التي تساوي مبلغًا معينًا في Excel؟

يعد اكتشاف كافة المجموعات المحتملة من الأرقام ضمن قائمة والتي تضيف ما يصل إلى مجموع معين تحديًا قد يواجهه العديد من مستخدمي Excel، سواء لأغراض إعداد الميزانية أو التخطيط أو تحليل البيانات.

في هذا المثال، لدينا قائمة من الأرقام، والهدف هو تحديد المجموعات من هذه القائمة التي يصل مجموعها إلى 480. توضح لقطة الشاشة المقدمة أن هناك خمس مجموعات محتملة من المجموعات التي تحقق هذا المجموع، بما في ذلك مجموعات مثل 300+120 +60، 250+120+60+50 وغيرها. في هذه المقالة، سوف نستكشف طرقًا مختلفة لتحديد مجموعات محددة من الأرقام داخل القائمة التي تبلغ إجمالي قيمة معينة في Excel.

ابحث عن مجموعة من الأرقام تساوي مجموعًا معينًا باستخدام وظيفة Solver

احصل على جميع مجموعات الأرقام المساوية لمجموع معين

احصل على كافة مجموعات الأرقام التي لها مجموع في نطاق باستخدام رمز VBA


ابحث عن مجموعة الخلايا التي تساوي مجموعًا معينًا باستخدام وظيفة Solver

قد يبدو الغوص في برنامج Excel للعثور على مجموعات الخلايا التي تضيف ما يصل إلى رقم معين أمرًا شاقًا، ولكن الوظيفة الإضافية Solver تجعل الأمر سهلاً. سنوجهك عبر الخطوات البسيطة لإعداد Solver والعثور على المجموعة الصحيحة من الخلايا، مما يجعل المهمة التي تبدو معقدة واضحة وقابلة للتنفيذ.

الخطوة 1: تمكين الوظيفة الإضافية Solver

  1. الرجاء التوجّه إلى قم بتقديم > مزيد من الخيارات، في خيارات إكسيل مربع الحوار، انقر فوق الوظائف الإضافية من الجزء الأيسر، ثم انقر فوق Go زر. انظر لقطة الشاشة:
  2. ثم ، الوظائف الإضافية يظهر مربع الحوار، تحقق من الوظيفة الإضافية Solver الخيار، وانقر OK لتثبيت هذه الوظيفة الإضافية بنجاح.

الخطوة 2: أدخل الصيغة

بعد تنشيط الوظيفة الإضافية Solver، يلزمك إدخال هذه الصيغة في الخلية B11:

=SUMPRODUCT(B2:B10,A2:A10)
ملاحظات: في هذه الصيغة: B2: B10 عبارة عن عمود من الخلايا الفارغة بجانب قائمة الأرقام الخاصة بك، و A2: A10 هي قائمة الأرقام التي تستخدمها.

الخطوة 3: تكوين وتشغيل Solver للحصول على النتيجة

  1. انقر البيانات > حلالا للذهاب إلى معلمة Solver في مربع الحوار ، يرجى القيام بالعمليات التالية:
    • (1.) انقر زر لتحديد الخلية B11 حيث توجد الصيغة الخاصة بك من تعيين الهدف قسم.
    • (2.) ثم في إلى القسم، حدد قيمة ال، وأدخل القيمة المستهدفة 480 ما تحتاج إليه؛
    • (3.) تحت عن طريق تغيير الخلايا المتغيرة القسم ، الرجاء الضغط زر لتحديد نطاق الخلايا B2: B10 حيث سيتم وضع علامة على الأرقام المقابلة الخاصة بك.
    • (4.) ثم انقر فوق أضف .
  2. ثم ، أ أضف القيد يتم عرض مربع الحوار، انقر فوق زر لتحديد نطاق الخلايا B2: B10وحدد بن من القائمة المنسدلة. في النهاية، انقر فوق OK زر. انظر لقطة الشاشة:
  3. في مجلة معلمة Solver الحوار ، انقر فوق حل زر ، بعد بضع دقائق ، أ نتائج Solver يظهر مربع الحوار، ويمكنك رؤية مجموعة الخلايا التي تساوي مجموعًا معينًا 480 تم وضع علامة عليها كـ 1 في العمود B. في نتائج Solver الحوار ، الرجاء التحديد احتفظ بحل Solver الخيار، وانقر OK للخروج من مربع الحوار. انظر لقطة الشاشة:
ملاحظات: ومع ذلك، فإن هذه الطريقة لها قيود: فهي يمكنها فقط تحديد مجموعة واحدة من الخلايا التي تضيف ما يصل إلى المجموع المحدد، حتى في حالة وجود مجموعات صالحة متعددة.

احصل على جميع مجموعات الأرقام المساوية لمجموع معين

يتيح لك استكشاف إمكانيات Excel الأعمق العثور على كل مجموعة أرقام تتطابق مع مجموع معين، وهو أمر أسهل مما تظن. سيوضح لك هذا القسم طريقتين للعثور على جميع مجموعات الأرقام المساوية لمجموع معين.

احصل على جميع مجموعات الأرقام المساوية لمجموع معين باستخدام وظيفة محددة من قبل المستخدم

للكشف عن كل مجموعة محتملة من الأرقام من مجموعة محددة تصل بشكل جماعي إلى قيمة معينة، تعمل الوظيفة المخصصة الموضحة أدناه كأداة فعالة.

الخطوة 1: افتح محرر وحدة VBA وانسخ الكود

  1. اضغط باستمرار ALT + F11 في Excel ، ويفتح ملف ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
  2. انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.
    رمز VBA: احصل على جميع مجموعات الأرقام المساوية لمجموع معين
    Public Function MakeupANumber(xNumbers As Range, xCount As Long)
    'updateby Extendoffice
        Dim arrNumbers() As Long
        Dim arrRes() As String
        Dim ArrTemp() As Long
        Dim xIndex As Long
        Dim rg As Range
    
        MakeupANumber = ""
        
        If xNumbers.CountLarge = 0 Then Exit Function
        ReDim arrNumbers(xNumbers.CountLarge - 1)
        
        xIndex = 0
        For Each rg In xNumbers
            If IsNumeric(rg.Value) Then
                arrNumbers(xIndex) = CLng(rg.Value)
                xIndex = xIndex + 1
            End If
        Next rg
        If xIndex = 0 Then Exit Function
        
        ReDim Preserve arrNumbers(0 To xIndex - 1)
        ReDim arrRes(0)
        
        Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
        ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
        MakeupANumber = arrRes
    End Function
    
    Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
    
        Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
        Dim remainingNumbers() As Long, newCombination() As Long
        
        currentSum = 0
        If (Not Not ArrTemp) <> 0 Then
            For i = LBound(ArrTemp) To UBound(ArrTemp)
                currentSum = currentSum + ArrTemp(i)
            Next i
        End If
     
        If currentSum = Count Then
            indRes = UBound(arrRes)
            ReDim Preserve arrRes(0 To indRes + 1)
            
            arrRes(indRes) = ArrTemp(0)
            For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
                arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
            Next i
        End If
        
        If currentSum > Count Then Exit Sub
        If (Not Not Numbers) = 0 Then Exit Sub
        
        For i = 0 To UBound(Numbers)
            Erase remainingNumbers()
            num = Numbers(i)
            For j = i + 1 To UBound(Numbers)
                If (Not Not remainingNumbers) <> 0 Then
                    ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
                Else
                    ReDim Preserve remainingNumbers(0 To 0)
                End If
                remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
                
            Next j
            Erase newCombination()
    
            If (Not Not ArrTemp) <> 0 Then
                For k = 0 To UBound(ArrTemp)
                    If (Not Not newCombination) <> 0 Then
                        ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                    Else
                        ReDim Preserve newCombination(0 To 0)
                    End If
                    newCombination(UBound(newCombination)) = ArrTemp(k)
    
                Next k
            End If
            
            If (Not Not newCombination) <> 0 Then
                ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
            Else
                ReDim Preserve newCombination(0 To 0)
            End If
            
            newCombination(UBound(newCombination)) = num
    
            Combinations remainingNumbers, Count, newCombination, arrRes
        Next i
    
    End Sub
    

الخطوة 2: أدخل الصيغة المخصصة للحصول على النتيجة

بعد لصق الكود، أغلق نافذة الكود للرجوع إلى ورقة العمل. أدخل الصيغة التالية في خلية فارغة لإخراج النتيجة، ثم اضغط أدخل مفتاح للحصول على كافة المجموعات. انظر لقطة الشاشة:

=MakeupANumber(A2:A10,B2)
ملاحظات: في هذه الصيغة: A2: A10 هي قائمة الأرقام، و B2 هو المبلغ الإجمالي الذي تريد الحصول عليه.

معلومه- سرية: إذا كنت تريد إدراج نتائج المجموعة عموديًا في عمود، فيرجى تطبيق الصيغة التالية:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
القيود المفروضة على هذه الطريقة:
  • تعمل هذه الوظيفة المخصصة فقط في Excel 365 و2021.
  • هذه الطريقة فعالة حصريًا للأرقام الموجبة؛ يتم تقريب القيم العشرية تلقائيًا إلى أقرب عدد صحيح، وستؤدي الأرقام السالبة إلى حدوث أخطاء.

احصل على جميع مجموعات الأرقام المساوية لمجموع معين بميزة قوية

نظرًا للقيود المفروضة على الوظيفة المذكورة أعلاه، نوصي بحل سريع وشامل: ميزة Kutools for Excel's Make up a Number، المتوافقة مع أي إصدار من Excel. يمكن لهذا البديل التعامل بشكل فعال مع الأرقام الموجبة والكسور العشرية والأرقام السالبة. باستخدام هذه الميزة، يمكنك الحصول بسرعة على جميع المجموعات التي تساوي مجموعًا معينًا.

نصائح: لتطبيق هذا تشكل رقم الميزة، أولاً، يجب عليك تنزيلها كوتولس ل إكسيل، ثم قم بتطبيق الميزة بسرعة وسهولة.
  1. انقر كوتولس > وصف المنتج > تشكل رقم، انظر لقطة الشاشة:
  2. ثم، في اصنع رقمًا مربع الحوار ، الرجاء الضغط لتحديد قائمة الأرقام التي تريد استخدامها من ملف مصدر البيانات، ثم أدخل الرقم الإجمالي في ملف مجموع مربع الكتابة. أخيرًا ، انقر فوق OK زر ، انظر لقطة الشاشة:
  3. وبعد ذلك، سيظهر مربع مطالبة لتذكيرك بتحديد خلية لتحديد النتيجة، ثم انقر فوق OK، انظر لقطة الشاشة:
  4. والآن، تم عرض جميع المجموعات التي تساوي هذا الرقم المحدد كما هو موضح أدناه في لقطة الشاشة:
ملاحظات: لتطبيق هذه الميزة من فضلك قم بتنزيل وتثبيت Kutools لبرنامج Excel لأول مرة.

احصل على كافة مجموعات الأرقام التي لها مجموع في نطاق باستخدام رمز VBA

في بعض الأحيان، قد تجد نفسك في موقف تحتاج فيه إلى تحديد جميع المجموعات الممكنة من الأرقام التي يصل مجموعها بشكل جماعي إلى مجموع ضمن نطاق معين. على سبيل المثال، قد تسعى إلى العثور على كل مجموعة ممكنة من الأرقام حيث يقع الإجمالي بين 470 و480.

يمثل اكتشاف جميع المجموعات الممكنة من الأرقام التي تلخص قيمة ضمن نطاق معين تحديًا رائعًا وعمليًا للغاية في برنامج Excel. سيقدم هذا القسم كود VBA لحل هذه المهمة.

الخطوة 1: افتح محرر وحدة VBA وانسخ الكود

  1. اضغط باستمرار ALT + F11 في Excel ، ويفتح ملف ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
  2. انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.
    رمز VBA: احصل على كافة مجموعات الأرقام التي تصل إلى نطاق معين
    Sub Getall_combinations()
    'Updateby Extendoffice
        Dim xNumbers As Variant
        Dim Output As Collection
        Dim rngSelection As Range
        Dim OutputCell As Range
        Dim LowLimit As Long, HiLimit As Long
        Dim i As Long, j As Long
        Dim TotalCombinations As Long
        Dim CombTotal As Double
        Set Output = New Collection
        On Error Resume Next
        Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
        If rngSelection Is Nothing Then
            MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        xNumbers = rngSelection.Value
        LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
        HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
        On Error Resume Next
        Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
        If OutputCell Is Nothing Then
            MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
        For i = 1 To TotalCombinations - 1
            Dim tempArr() As Double
            ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
            CombTotal = 0
            Dim k As Long: k = 0
            
            For j = 1 To UBound(xNumbers, 1)
                If i And (2 ^ (j - 1)) Then
                    k = k + 1
                    tempArr(k) = xNumbers(j, 1)
                    CombTotal = CombTotal + xNumbers(j, 1)
                End If
            Next j
            If CombTotal >= LowLimit And CombTotal <= HiLimit Then
                ReDim Preserve tempArr(1 To k)
                Output.Add tempArr
            End If
        Next i
        Dim rowOffset As Long
        rowOffset = 0
        Dim item As Variant
        For Each item In Output
            For j = 1 To UBound(item)
                OutputCell.Offset(rowOffset, j - 1).Value = item(j)
            Next j
            rowOffset = rowOffset + 1
        Next item
    End Sub
    
    
    

الخطوة 2: تنفيذ الكود

  1. بعد لصق الرمز ، اضغط على F5 لتشغيل هذا الرمز، في مربع الحوار المنبثق الأول، حدد نطاق الأرقام التي تريد استخدامها، وانقر فوق OK. انظر لقطة الشاشة:
  2. في مربع المطالبة الثاني، حدد أو اكتب رقم الحد الأدنى، ثم انقر فوق OK. انظر لقطة الشاشة:
  3. في مربع المطالبة الثالث، حدد أو اكتب رقم الحد الأعلى، ثم انقر فوق OK. انظر لقطة الشاشة:
  4. في مربع المطالبة الأخير، حدد خلية الإخراج، حيث سيبدأ إخراج النتائج. ثم اضغط OK. انظر لقطة الشاشة:

نتيجة

الآن، سيتم إدراج كل مجموعة مؤهلة في صفوف متتالية في ورقة العمل، بدءًا من خلية الإخراج التي اخترتها.

يمنحك برنامج Excel عدة طرق للعثور على مجموعات من الأرقام التي يصل مجموعها إلى إجمالي معين، كل طريقة تعمل بشكل مختلف، لذا يمكنك اختيار واحدة بناءً على مدى معرفتك ببرنامج Excel وما تحتاجه لمشروعك. إذا كنت مهتمًا باستكشاف المزيد من النصائح والحيل الخاصة بـ Excel، فإن موقعنا يقدم آلاف البرامج التعليمية، من فضلك انقر هنا للوصول إليها. شكرًا لك على القراءة، ونتطلع إلى تزويدك بالمزيد من المعلومات المفيدة في المستقبل!


مقالات ذات صلة:

  • قائمة أو توليد كافة المجموعات الممكنة
  • دعنا نقول ، لدي عمودين من البيانات التاليين ، والآن ، أريد إنشاء قائمة بجميع المجموعات الممكنة بناءً على قائمتي القيم كما هو موضح في لقطة الشاشة اليسرى. ربما يمكنك سرد جميع المجموعات واحدة تلو الأخرى إذا كانت هناك قيم قليلة ، ولكن إذا كان هناك عدة أعمدة ذات قيم متعددة مطلوبة لإدراج المجموعات الممكنة ، فإليك بعض الحيل السريعة التي قد تساعدك في التعامل مع هذه المشكلة في Excel .
Comments (49)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi everyone,

I'm trying to find a way to to find all possible combinations of workershifts for a specific amount of workhours within a specific amount of working days.

Both, solver and Kutools basically work, but:

Solver only ever displays one combination not all of them (which is what I would need).

Kutools on the other hand only uses each value once, for example I know there are solutions with say 3 7-hour shifts but it won't pick the shift more than once.

Thanks in advance for any responses!

Hope I'm not threadnecroing too hard here.
This comment was minimized by the moderator on the site
Hello, simsok,
Sorry, I can't understand you clearly.
Could you give your problem more detailed, or can you insert a screenshot of your problem here?
Thank you!
This comment was minimized by the moderator on the site
Thank you so much!!! The solver add-in worked for me!
This comment was minimized by the moderator on the site
How to get list of cell names that add to a given no.
This comment was minimized by the moderator on the site
Hello, Ranka,
I'm sorry, at present, there is no good way for getting the cells that add to a given number.
Thank you!
This comment was minimized by the moderator on the site
ExtendOffice - How To Find All Combinations That Equal A Given Sum In Excel

In this example, I would like to run 100 rows instead of the 8 in your example. When I try to create more rows the formula stops working. I started over with a fresh sheet and I still can not get the formula to work.

Running windows 10
Excel 2207
Office 365
This comment was minimized by the moderator on the site
Hello, Shaw

Unfortunately, the formula in this article has a limit of 20 values, if there are more than 20 numbers, the result will not come out. In this case, I will recommend the Make Up A Number feature of Kutools for Excel, with it, you can find all combinations from the list of numbers. If you have a lot of numbers and many combination results, it will take much time, but you can set the number of combinations to make it faster. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-make-up-number-1.png

You can download this tool from here: https://www.extendoffice.com/download/kutools-for-excel.html
You can try it for free 30 days, please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi I have tried doing this with the first method and it just isn't working. I'm unsure what I'm doing wrong as I seem to have followed the instructions exactly. Are there any common errors to watch out for? There is no error showing in the formula itself and all cells are all in the same position as yours are, but it's a list of 48 numbers rather than 8.
This comment was minimized by the moderator on the site
Hello, Angie
If you can't get the result by using the first method, you can view the video at the bottom of this article:
https://www.extendoffice.com/documents/excel/3557-excel-find-all-combinations-that-equal-given-sum.html#demo
Also, you can try our tool-Kutools for Excel's Make up a number feature, it will get all combinations quickly and easily.
Thank you!
This comment was minimized by the moderator on the site
So will any of these work when I have been paid by a client, but with no remit so don’t know which invoices have been paid.
This comment was minimized by the moderator on the site
For the solver add in on excel, can you make it solve for closest to 480 rather than value of 480 (example used above)? I really need some help on how this might work out, thanks in advance

This comment was minimized by the moderator on the site
Could this be adapted to find combinations that sum up to specific range i.e. sum between 450 and 500? Is there a way to set it so that each cell value can be used only in one combination not more?
This comment was minimized by the moderator on the site
kutools works only integer value. Not support double. Like (395,52) ! Best solution is excel solver extention.
This comment was minimized by the moderator on the site
is there a way to find combination for a target average instead of sum ?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations