كيف تجد كل المجموعات التي تساوي مبلغًا معينًا في Excel؟
يعد اكتشاف كافة المجموعات المحتملة من الأرقام ضمن قائمة والتي تضيف ما يصل إلى مجموع معين تحديًا قد يواجهه العديد من مستخدمي Excel، سواء لأغراض إعداد الميزانية أو التخطيط أو تحليل البيانات.
في هذا المثال، لدينا قائمة من الأرقام، والهدف هو تحديد المجموعات من هذه القائمة التي يصل مجموعها إلى 480. توضح لقطة الشاشة المقدمة أن هناك خمس مجموعات محتملة من المجموعات التي تحقق هذا المجموع، بما في ذلك مجموعات مثل 300+120 +60، 250+120+60+50 وغيرها. في هذه المقالة، سوف نستكشف طرقًا مختلفة لتحديد مجموعات محددة من الأرقام داخل القائمة التي تبلغ إجمالي قيمة معينة في Excel.
ابحث عن مجموعة من الأرقام تساوي مجموعًا معينًا باستخدام وظيفة Solver
احصل على جميع مجموعات الأرقام المساوية لمجموع معين
احصل على كافة مجموعات الأرقام التي لها مجموع في نطاق باستخدام رمز VBA
ابحث عن مجموعة الخلايا التي تساوي مجموعًا معينًا باستخدام وظيفة Solver
قد يبدو الغوص في برنامج Excel للعثور على مجموعات الخلايا التي تضيف ما يصل إلى رقم معين أمرًا شاقًا، ولكن الوظيفة الإضافية Solver تجعل الأمر سهلاً. سنوجهك عبر الخطوات البسيطة لإعداد Solver والعثور على المجموعة الصحيحة من الخلايا، مما يجعل المهمة التي تبدو معقدة واضحة وقابلة للتنفيذ.
الخطوة 1: تمكين الوظيفة الإضافية Solver
- الرجاء التوجّه إلى قم بتقديم > مزيد من الخيارات، في خيارات إكسيل مربع الحوار، انقر فوق الوظائف الإضافية من الجزء الأيسر، ثم انقر فوق Go زر. انظر لقطة الشاشة:
- ثم ، الوظائف الإضافية يظهر مربع الحوار، تحقق من الوظيفة الإضافية Solver الخيار، وانقر OK لتثبيت هذه الوظيفة الإضافية بنجاح.
الخطوة 2: أدخل الصيغة
بعد تنشيط الوظيفة الإضافية Solver، يلزمك إدخال هذه الصيغة في الخلية B11:
=SUMPRODUCT(B2:B10,A2:A10)
الخطوة 3: تكوين وتشغيل Solver للحصول على النتيجة
- انقر البيانات > حلالا للذهاب إلى معلمة Solver في مربع الحوار ، يرجى القيام بالعمليات التالية:
- (1.) انقر زر لتحديد الخلية B11 حيث توجد الصيغة الخاصة بك من تعيين الهدف قسم.
- (2.) ثم في إلى القسم، حدد قيمة ال، وأدخل القيمة المستهدفة 480 ما تحتاج إليه؛
- (3.) تحت عن طريق تغيير الخلايا المتغيرة القسم ، الرجاء الضغط زر لتحديد نطاق الخلايا B2: B10 حيث سيتم وضع علامة على الأرقام المقابلة الخاصة بك.
- (4.) ثم انقر فوق أضف .
- ثم ، أ أضف القيد يتم عرض مربع الحوار، انقر فوق زر لتحديد نطاق الخلايا B2: B10وحدد بن من القائمة المنسدلة. في النهاية، انقر فوق OK زر. انظر لقطة الشاشة:
- في مجلة معلمة Solver الحوار ، انقر فوق حل زر ، بعد بضع دقائق ، أ نتائج Solver يظهر مربع الحوار، ويمكنك رؤية مجموعة الخلايا التي تساوي مجموعًا معينًا 480 تم وضع علامة عليها كـ 1 في العمود B. في نتائج Solver الحوار ، الرجاء التحديد احتفظ بحل Solver الخيار، وانقر OK للخروج من مربع الحوار. انظر لقطة الشاشة:
احصل على جميع مجموعات الأرقام المساوية لمجموع معين
يتيح لك استكشاف إمكانيات Excel الأعمق العثور على كل مجموعة أرقام تتطابق مع مجموع معين، وهو أمر أسهل مما تظن. سيوضح لك هذا القسم طريقتين للعثور على جميع مجموعات الأرقام المساوية لمجموع معين.
احصل على جميع مجموعات الأرقام المساوية لمجموع معين باستخدام وظيفة محددة من قبل المستخدم
للكشف عن كل مجموعة محتملة من الأرقام من مجموعة محددة تصل بشكل جماعي إلى قيمة معينة، تعمل الوظيفة المخصصة الموضحة أدناه كأداة فعالة.
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- اضغط باستمرار ALT + F11 في Excel ، ويفتح ملف ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.
رمز 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)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- تعمل هذه الوظيفة المخصصة فقط في Excel 365 و2021.
- هذه الطريقة فعالة حصريًا للأرقام الموجبة؛ يتم تقريب القيم العشرية تلقائيًا إلى أقرب عدد صحيح، وستؤدي الأرقام السالبة إلى حدوث أخطاء.
احصل على جميع مجموعات الأرقام المساوية لمجموع معين بميزة قوية
نظرًا للقيود المفروضة على الوظيفة المذكورة أعلاه، نوصي بحل سريع وشامل: ميزة Kutools for Excel's Make up a Number، المتوافقة مع أي إصدار من Excel. يمكن لهذا البديل التعامل بشكل فعال مع الأرقام الموجبة والكسور العشرية والأرقام السالبة. باستخدام هذه الميزة، يمكنك الحصول بسرعة على جميع المجموعات التي تساوي مجموعًا معينًا.
- انقر كوتولس > وصف المنتج > تشكل رقم، انظر لقطة الشاشة:
- ثم، في اصنع رقمًا مربع الحوار ، الرجاء الضغط لتحديد قائمة الأرقام التي تريد استخدامها من ملف مصدر البيانات، ثم أدخل الرقم الإجمالي في ملف مجموع مربع الكتابة. أخيرًا ، انقر فوق OK زر ، انظر لقطة الشاشة:
- وبعد ذلك، سيظهر مربع مطالبة لتذكيرك بتحديد خلية لتحديد النتيجة، ثم انقر فوق OK، انظر لقطة الشاشة:
- والآن، تم عرض جميع المجموعات التي تساوي هذا الرقم المحدد كما هو موضح أدناه في لقطة الشاشة:
احصل على كافة مجموعات الأرقام التي لها مجموع في نطاق باستخدام رمز VBA
في بعض الأحيان، قد تجد نفسك في موقف تحتاج فيه إلى تحديد جميع المجموعات الممكنة من الأرقام التي يصل مجموعها بشكل جماعي إلى مجموع ضمن نطاق معين. على سبيل المثال، قد تسعى إلى العثور على كل مجموعة ممكنة من الأرقام حيث يقع الإجمالي بين 470 و480.
يمثل اكتشاف جميع المجموعات الممكنة من الأرقام التي تلخص قيمة ضمن نطاق معين تحديًا رائعًا وعمليًا للغاية في برنامج Excel. سيقدم هذا القسم كود VBA لحل هذه المهمة.
الخطوة 1: افتح محرر وحدة VBA وانسخ الكود
- اضغط باستمرار ALT + F11 في Excel ، ويفتح ملف ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.
رمز 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 لتشغيل هذا الرمز، في مربع الحوار المنبثق الأول، حدد نطاق الأرقام التي تريد استخدامها، وانقر فوق OK. انظر لقطة الشاشة:
- في مربع المطالبة الثاني، حدد أو اكتب رقم الحد الأدنى، ثم انقر فوق OK. انظر لقطة الشاشة:
- في مربع المطالبة الثالث، حدد أو اكتب رقم الحد الأعلى، ثم انقر فوق OK. انظر لقطة الشاشة:
- في مربع المطالبة الأخير، حدد خلية الإخراج، حيث سيبدأ إخراج النتائج. ثم اضغط OK. انظر لقطة الشاشة:
نتيجة
الآن، سيتم إدراج كل مجموعة مؤهلة في صفوف متتالية في ورقة العمل، بدءًا من خلية الإخراج التي اخترتها.
يمنحك برنامج Excel عدة طرق للعثور على مجموعات من الأرقام التي يصل مجموعها إلى إجمالي معين، كل طريقة تعمل بشكل مختلف، لذا يمكنك اختيار واحدة بناءً على مدى معرفتك ببرنامج Excel وما تحتاجه لمشروعك. إذا كنت مهتمًا باستكشاف المزيد من النصائح والحيل الخاصة بـ Excel، فإن موقعنا يقدم آلاف البرامج التعليمية، من فضلك انقر هنا للوصول إليها. شكرًا لك على القراءة، ونتطلع إلى تزويدك بالمزيد من المعلومات المفيدة في المستقبل!
مقالات ذات صلة:
- قائمة أو توليد كافة المجموعات الممكنة
- دعنا نقول ، لدي عمودين من البيانات التاليين ، والآن ، أريد إنشاء قائمة بجميع المجموعات الممكنة بناءً على قائمتي القيم كما هو موضح في لقطة الشاشة اليسرى. ربما يمكنك سرد جميع المجموعات واحدة تلو الأخرى إذا كانت هناك قيم قليلة ، ولكن إذا كان هناك عدة أعمدة ذات قيم متعددة مطلوبة لإدراج المجموعات الممكنة ، فإليك بعض الحيل السريعة التي قد تساعدك في التعامل مع هذه المشكلة في Excel .
- قائمة بجميع المجموعات الممكنة من عمود واحد
- إذا كنت ترغب في إرجاع جميع المجموعات الممكنة من بيانات عمود واحد للحصول على النتيجة كما هو موضح أدناه ، هل لديك أي طرق سريعة للتعامل مع هذه المهمة في Excel؟
- قم بإنشاء جميع المجموعات المكونة من 3 أعمدة أو عدة أعمدة
- لنفترض أن لدي 3 أعمدة من البيانات ، الآن ، أريد إنشاء أو سرد كافة مجموعات البيانات في هذه الأعمدة الثلاثة كما هو موضح أدناه. هل لديك أي طرق جيدة لحل هذه المهمة في Excel؟
- قم بإنشاء قائمة بجميع المجموعات الممكنة المكونة من 4 أرقام
- في بعض الحالات ، قد نحتاج إلى إنشاء قائمة بجميع مجموعات الأرقام الأربعة الممكنة من الأرقام 4 إلى 0 ، مما يعني إنشاء قائمة من 9 ، 0000 ، 0001 ... 0002. لحل مهمة القائمة في Excel بسرعة ، أقدم لك بعض الحيل.
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!