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

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

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

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

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

• باستخدام دالة معرّفة من قبل المستخدم
• باستخدام ميزة ذكية – Kutools لـ Excel

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

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

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

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

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

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

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

=SUMPRODUCT(B2:B10,A2:A10)

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

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

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

استكشاف الإمكانيات المتقدمة في 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)

تلميح: إذا كنت تريد عرض نتائج المجموعات رأسيًا في عمود، فالرجاء تطبيق الصيغة التالية:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

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

نظرًا لمحدوديات الدالة المذكورة أعلاه، نوصي بحلٍ سريع وشامل: ميزة **«إنشاء أرقام» من Kutools لـ Excel**، المتوافقة مع جميع إصدارات Excel. ويُمكّنك هذا البديل من التعامل بكفاءة مع الأرقام الموجبة، والسلبية، والعشرية على حدٍ سواء، مما يتيح لك الحصول فورًا على جميع التركيبات التي يساوي مجموعها القيمة المطلوبة.

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

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

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

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

الخطوة 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 لتشغيله. في المربع المنبثق الأول، حدد نطاق الأرقام الذي تريد استخدامه، ثم انقر فوقموافق. راجع لقطة الشاشة:
   
2. في المربع المنبثق الثاني، حدد أو اكتب الرقم الأدنى، ثم انقر فوقموافق. راجع لقطة الشاشة:
   
3. في المربع المنبثق الثالث، حدد أو اكتب الرقم الأعلى، ثم انقر فوقموافق. راجع لقطة الشاشة:
   
4. في المربع المنبثق الأخير، حدد خلية الإخراج—وهي الخلية التي ستبدأ منها كتابة النتائج—ثم انقر فوقموافق. راجع لقطة الشاشة:
   

النتيجة

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

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

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