كيفية العثور على جميع التوليفات التي تساوي مجموعًا معينًا في Excel؟
اكتشاف جميع التوليفات الممكنة للأرقام داخل قائمة والتي تصل إلى مجموع محدد يمثل تحديًا قد يواجهه العديد من مستخدمي Excel، سواء لأغراض الميزانية أو التخطيط أو تحليل البيانات.
في هذا المثال، لدينا قائمة من الأرقام، والهدف هو تحديد أي التوليفات من هذه القائمة يصل مجموعها إلى 480. توضح لقطة الشاشة المقدمة وجود خمس مجموعات ممكنة من التوليفات التي تحقق هذا المجموع، بما في ذلك التوليفات مثل 300+120+60 و250+120+60+50، من بين أخرى. في هذه المقالة، سنستعرض طرقًا مختلفة لتحديد التوليفات المحددة للأرقام داخل قائمة والتي يصل مجموعها إلى قيمة محددة في Excel.
العثور على توليفة من الأرقام تساوي مجموعًا معينًا باستخدام وظيفة Solver
الحصول على جميع التوليفات للأرقام التي تساوي مجموعًا معينًا
الحصول على جميع التوليفات للأرقام التي يكون لها مجموع ضمن نطاق باستخدام كود VBA
العثور على توليفة من الخلايا التي تساوي مجموعًا معينًا باستخدام وظيفة Solver
الغوص في Excel للعثور على توليفات الخلايا التي تصل إلى رقم محدد قد يبدو أمرًا شاقًا، لكن إضافة Solver تجعل الأمر سهلًا. سنرشدك عبر الخطوات البسيطة لإعداد Solver والعثور على التوليفة الصحيحة من الخلايا، مما يجعل ما بدا وكأنه مهمة معقدة أمرًا بسيطًا وممكن التنفيذ.
الخطوة 1: تمكين إضافة Solver
- يرجى الانتقال إلى ملف > الخيارات, في خيارات Excel مربع الحوار، انقر فوق Add-Ins من الجزء الأيسر، ثم انقر فوق Go زر. انظر لقطة الشاشة:
- ثم، Add-Ins تظهر نافذة الحوار، قم بتحديد الخيار Solver Add-in وانقر فوق موافق لتثبيت هذه الإضافة بنجاح.
الخطوة 2: إدخال الصيغة
بعد تفعيل إضافة Solver، تحتاج إلى إدخال هذه الصيغة في الخلية B11:
=SUMPRODUCT(B2:B10,A2:A10)
الخطوة 3: تكوين وتشغيل Solver للحصول على النتيجة
- انقر فوق بيانات > Solver للذهاب إلى معلمات Solver مربع الحوار، في مربع الحوار، يرجى القيام بالعمليات التالية:
- (1.) انقر فوق
زر لتحديد الخلية B11 حيث توجد صيغتك من القسم Set Objective ؛
- (2.) ثم في قسم To، حدد Value Of، وأدخل القيمة المستهدفة 480 كما تحتاج؛
- (3.) أسفل القسم By Changing Variable Cells ، يرجى النقر فوق
زر لتحديد نطاق الخلايا B2:B10 حيث سيتم وضع علامة على الأرقام المقابلة.
- (4.) بعد ذلك، انقر فوق زر Add.
- (1.) انقر فوق
- ثم، إضافة قيد يظهر مربع الحوار، انقر فوق
زر لتحديد نطاق الخلايا B2:B10, وحدد bin من القائمة المنسدلة. أخيرًا، انقر فوق موافق زر. انظر لقطة الشاشة:
- في معلمات Solver مربع الحوار، انقر فوق الزر Solve زر، بعد بضع دقائق، نتائج Solver يظهر مربع الحوار، ويمكنك رؤية توليفة الخلايا التي تساوي مجموعًا معينًا 480 معلمة بـ 1 في العمود B. في نتائج Solver مربع الحوار، يرجى تحديد Keep Solver Solution الخيار، وانقر فوق موافق لمغادرة مربع الحوار. انظر لقطة الشاشة:
الحصول على جميع التوليفات للأرقام التي تساوي مجموعًا معينًا
استكشاف القدرات الأعمق لبرنامج Excel يتيح لك العثور على كل توليفة من الأرقام التي تتطابق مع مجموع محدد، وهو أسهل مما قد تظن. ستشير هذه الفقرة إلى طريقتين للعثور على جميع التوليفات للأرقام التي تساوي مجموعًا معينًا.
الحصول على جميع التوليفات للأرقام التي تساوي مجموعًا معينًا باستخدام دالة معرفة من قبل المستخدم
لكشف كل التوليفات الممكنة للأرقام من مجموعة محددة والتي تصل جماعيًا إلى قيمة معينة، تُعد الدالة المخصصة الموضحة أدناه أداة فعالة.
الخطوة 1: افتح محرر وحدة VBA ونسخ الكود
- اضغط باستمرار على مفاتيح ALT + F11 في Excel، وسيتم فتح نافذة Microsoft Visual Basic for Applications.
- انقر فوق إدراج > وحدة, والصق الكود التالي في نافذة الوحدة.
كود 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))

- تعمل هذه الدالة المخصصة فقط في Excel 365 و2021.
- هذه الطريقة فعالة حصريًا للأرقام الموجبة؛ يتم تقريب القيم العشرية تلقائيًا إلى أقرب عدد صحيح، والأرقام السالبة ستؤدي إلى أخطاء.
الحصول على جميع التوليفات للأرقام التي تساوي مجموعًا معينًا باستخدام ميزة قوية
بالنظر إلى القيود المذكورة أعلاه للدالة، نوصي بحل سريع وشامل: ميزة Make up a Number في Kutools لـ Excel، والتي تتوافق مع أي إصدار من Excel. يمكن لهذا البديل التعامل بفعالية مع الأرقام الموجبة، العشرية، والسالبة. باستخدام هذه الميزة، يمكنك بسرعة الحصول على جميع التوليفات التي تساوي مجموعًا معينًا.
- انقر فوق Kutools > المحتوى > Make Up A Number, انظر لقطة الشاشة:
- ثم، في تكوين رقم مربع الحوار، يرجى النقر فوق
زر لتحديد قائمة الأرقام التي تريد استخدامها من مصدر البيانات, ثم أدخل الرقم الإجمالي في مربع النص Sum . أخيرًا، انقر فوق موافق زر، انظر لقطة الشاشة:
- ومن ثم، سيظهر مربع منبثق لتذكيرك بتحديد خلية لوضع النتيجة، ثم انقر فوق موافق, انظر لقطة الشاشة:
- والآن، تم عرض جميع التوليفات التي تساوي الرقم المعطى كما هو موضح في لقطة الشاشة أدناه:
الحصول على جميع التوليفات للأرقام التي يكون لها مجموع ضمن نطاق باستخدام كود VBA
أحيانًا، قد تجد نفسك في موقف حيث تحتاج إلى تحديد جميع التوليفات الممكنة للأرقام التي يصل مجموعها إلى مجموع ضمن نطاق محدد. على سبيل المثال، قد تكون تبحث عن العثور على كل تجميع ممكن للأرقام حيث يكون المجموع الإجمالي بين 470 و480.
اكتشاف جميع التوليفات الممكنة للأرقام التي يصل مجموعها إلى قيمة ضمن نطاق محدد يمثل تحديًا مثيرًا وعمليًا للغاية في Excel. ستشير هذه الفقرة إلى كود VBA لحل هذه المهمة.
الخطوة 1: افتح محرر وحدة VBA ونسخ الكود
- اضغط باستمرار على مفاتيح ALT + F11 في Excel، وسيتم فتح نافذة Microsoft Visual Basic for Applications.
- انقر فوق إدراج > وحدة, والصق الكود التالي في نافذة الوحدة.
كود 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: تنفيذ الكود
- بعد لصق الكود، اضغط على F5 لتشغيل هذا الكود، في أول مربع حوار منبثق، حدد نطاق الأرقام الذي تريد استخدامه، وانقر فوق موافق. انظر لقطة الشاشة:
- في مربع الحوار الثاني المنبثق، حدد أو أدخل الرقم الحد الأدنى، وانقر فوق موافق. انظر لقطة الشاشة:
- في مربع الحوار الثالث المنبثق، حدد أو أدخل الرقم الحد الأقصى، وانقر فوق موافق. انظر لقطة الشاشة:
- في آخر مربع حوار منبثق، حدد خلية إخراج، وهي الخلية التي ستبدأ فيها نتائج الإخراج. ثم انقر فوق موافق. انظر لقطة الشاشة:
النتيجة
الآن، سيتم سرد كل توليفة مؤهلة في صفوف متتالية في ورقة العمل، بدءًا من الخلية المختارة للإخراج.
يقدم لك Excel عدة طرق للعثور على مجموعات من الأرقام التي تصل إلى مجموع معين، كل طريقة تعمل بشكل مختلف، لذلك يمكنك اختيار واحدة بناءً على مدى معرفتك بـ Excel وما تحتاجه لمشروعك. إذا كنت مهتمًا باستكشاف المزيد من نصائح وحيل Excel، يقدم موقعنا آلاف الدروس التعليمية. شكراً لك على القراءة، ونتطلع إلى تقديم المزيد من المعلومات المفيدة لك في المستقبل!
مقالات ذات صلة:
- سرد أو توليد جميع التوليفات الممكنة
- لنفترض أن لدي عمودين من البيانات كما هو موضح أدناه، وأريد الآن توليد قائمة بجميع التوليفات الممكنة بناءً على قائمتي القيمتين كما هو موضح في لقطة الشاشة اليسرى. ربما يمكنك سرد جميع التوليفات واحدة تلو الأخرى إذا كان هناك عدد قليل من القيم، ولكن إذا كان هناك عدة أعمدة تحتوي على قيم متعددة تحتاج إلى سرد التوليفات الممكنة، فإليك بعض الحيل السريعة التي قد تساعدك في التعامل مع هذه المشكلة في Excel.
- سرد جميع التوليفات الممكنة من عمود واحد
- إذا كنت ترغب في إرجاع جميع التوليفات الممكنة من بيانات عمود واحد للحصول على النتيجة كما هو موضح في لقطة الشاشة أدناه، هل لديك أي طرق سريعة للتعامل مع هذه المهمة في Excel؟
- توليد جميع التوليفات لثلاثة أعمدة أو أكثر
- لنفترض أن لدي 3 أعمدة من البيانات، وأريد الآن توليد أو سرد جميع التوليفات للبيانات في هذه الأعمدة الثلاثة كما هو موضح في لقطة الشاشة أدناه. هل لديك أي طرق جيدة لحل هذه المهمة في Excel؟
- توليد قائمة بجميع التوليفات الممكنة المكونة من 4 أرقام
- في بعض الحالات، قد نحتاج إلى توليد قائمة بجميع التوليفات الممكنة المكونة من 4 أرقام من الرقم 0 إلى 9، مما يعني توليد قائمة بـ 0000، 0001، 0002…9999. لحل هذه المهمة بسرعة في Excel، أقدم لك بعض الحيل.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel مع Kutools لـ Excel، واختبر الكفاءة كما لم يحدث من قبل. Kutools لـ Excel يقدم أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يقدم واجهة التبويب لـ Office، ويجعل عملك أسهل بكثير
- تمكين تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint.
- افتح وأنشئ عدة مستندات في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ منفصلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات بالماوس يومياً!