Skip to main content

كيفية العثور على جميع التوليفات التي تساوي مجموعًا معينًا في Excel؟

Author: Xiaoyang Last Modified: 2025-05-30

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

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

get allpossible combinations of numbers

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

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

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


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

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

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

  1. يرجى الانتقال إلى ملف > الخيارات, في خيارات Excel مربع الحوار، انقر فوق Add-Ins من الجزء الأيسر، ثم انقر فوق Go زر. انظر لقطة الشاشة:
    go to Excel options box to select Add-in
  2. ثم، Add-Ins تظهر نافذة الحوار، قم بتحديد الخيار Solver Add-in وانقر فوق موافق لتثبيت هذه الإضافة بنجاح.
    Enable Solver Add-in

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

بعد تفعيل إضافة Solver، تحتاج إلى إدخال هذه الصيغة في الخلية B11:

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

enter a formula in a cell

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

  1. انقر فوق بيانات > Solver للذهاب إلى معلمات Solver مربع الحوار، في مربع الحوار، يرجى القيام بالعمليات التالية:
    • (1.) انقر فوق Solver Parameter button زر لتحديد الخلية B11 حيث توجد صيغتك من القسم Set Objective ؛
    • (2.) ثم في قسم To، حدد Value Of، وأدخل القيمة المستهدفة 480 كما تحتاج؛
    • (3.) أسفل القسم By Changing Variable Cells ، يرجى النقر فوق Solver Parameter button زر لتحديد نطاق الخلايا B2:B10 حيث سيتم وضع علامة على الأرقام المقابلة.
    • (4.) بعد ذلك، انقر فوق زر Add.
    • Configure Solver Parameter
  2. ثم، إضافة قيد يظهر مربع الحوار، انقر فوق Solver Parameter button زر لتحديد نطاق الخلايا B2:B10, وحدد bin من القائمة المنسدلة. أخيرًا، انقر فوق موافق زر. انظر لقطة الشاشة:
    Configure Add Constraint
  3. في معلمات Solver مربع الحوار، انقر فوق الزر Solve زر، بعد بضع دقائق، نتائج Solver يظهر مربع الحوار، ويمكنك رؤية توليفة الخلايا التي تساوي مجموعًا معينًا 480 معلمة بـ 1 في العمود B. في نتائج Solver مربع الحوار، يرجى تحديد Keep Solver Solution الخيار، وانقر فوق موافق لمغادرة مربع الحوار. انظر لقطة الشاشة:
    Configure Solver Results to get the result
ملاحظة: ومع ذلك، فإن لهذه الطريقة قيود: يمكنها فقط تحديد توليفة واحدة من الخلايا التي تصل إلى المجموع المحدد، حتى لو كانت هناك عدة توليفات صالحة موجودة.

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

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

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

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

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

  1. اضغط باستمرار على مفاتيح ALT + F11 في Excel، وسيتم فتح نافذة Microsoft Visual Basic for Applications.
  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: أدخل الصيغة المخصصة للحصول على النتيجة

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

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

Get all combinations of numbers horizontally

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

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

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

نصائح: لتطبيق ميزة Make Up A Number ، أولاً، يجب عليك تنزيل  Kutools لـ Excel، ثم تطبيق الميزة بسرعة وسهولة.
  1. انقر فوق Kutools > المحتوى > Make Up A Number, انظر لقطة الشاشة:
    Get all combinations of numbers with kutools
  2. ثم، في تكوين رقم مربع الحوار، يرجى النقر فوق select button زر لتحديد قائمة الأرقام التي تريد استخدامها من مصدر البيانات, ثم أدخل الرقم الإجمالي في مربع النص Sum . أخيرًا، انقر فوق موافق زر، انظر لقطة الشاشة:
    go to Make up a number dialog box to set the options
  3. ومن ثم، سيظهر مربع منبثق لتذكيرك بتحديد خلية لوضع النتيجة، ثم انقر فوق موافق, انظر لقطة الشاشة:
    select a cell to put the result
  4. والآن، تم عرض جميع التوليفات التي تساوي الرقم المعطى كما هو موضح في لقطة الشاشة أدناه:
    Get all combinations of numbers with kutools result
ملاحظة: لتطبيق هذه الميزة، يرجى تنزيل وتثبيت Kutools لـ Excel أولاً.

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

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

اكتشاف جميع التوليفات الممكنة للأرقام التي يصل مجموعها إلى قيمة ضمن نطاق محدد يمثل تحديًا مثيرًا وعمليًا للغاية في Excel. ستشير هذه الفقرة إلى كود VBA لحل هذه المهمة.
all possible combinations of numbers that sum up to a value within a specific range

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

  1. اضغط باستمرار على مفاتيح ALT + F11 في Excel، وسيتم فتح نافذة Microsoft Visual Basic for Applications.
  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 لتشغيل هذا الكود، في أول مربع حوار منبثق، حدد نطاق الأرقام الذي تريد استخدامه، وانقر فوق موافق. انظر لقطة الشاشة:
    all possible combinations of numbers that sum up to a value within a specific range vba code to select a data range
  2. في مربع الحوار الثاني المنبثق، حدد أو أدخل الرقم الحد الأدنى، وانقر فوق موافق. انظر لقطة الشاشة:
    all possible combinations of numbers that sum up to a value within a specific range vba code to select low limit number
  3. في مربع الحوار الثالث المنبثق، حدد أو أدخل الرقم الحد الأقصى، وانقر فوق موافق. انظر لقطة الشاشة:
    all possible combinations of numbers that sum up to a value within a specific range vba code to select high limit number
  4. في آخر مربع حوار منبثق، حدد خلية إخراج، وهي الخلية التي ستبدأ فيها نتائج الإخراج. ثم انقر فوق موافق. انظر لقطة الشاشة:
    all possible combinations of numbers that sum up to a value within a specific range vba code to select a cell to put the result

النتيجة

الآن، سيتم سرد كل توليفة مؤهلة في صفوف متتالية في ورقة العمل، بدءًا من الخلية المختارة للإخراج.
all possible combinations of numbers that sum up to a value within a specific range vba code to get the result

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


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

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