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

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

• ابحث عن تركيبة الخلايا التي تساوي مجموعًا معينًا مع الصيغ
• ابحث عن مجموعة خلايا تساوي مجموعًا محددًا باستخدام Solver Add-in
• ابحث عن تركيبة الخلايا التي تساوي مجموعًا محددًا مع الوظيفة المحددة بواسطة المستخدم
• ابحث عن جميع التركيبات التي تساوي مبلغًا معينًا مع ميزة مذهلة

ابحث عن تركيبة الخلايا التي تساوي مجموعًا معينًا مع الصيغ

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

1. حدد قائمة الأرقام وحدد هذه القائمة اسم النطاق-- النطاق 1 في اسم صندوق، و اضغط أدخل مفتاح لإنهاء اسم النطاق المحدد ، انظر الصورة:

2. بعد تحديد اسم نطاق لقائمة الأرقام ، فأنت بحاجة إلى إنشاء اسمي نطاق آخرين في ملف مدير الاسم مربع ، الرجاء الضغط الصيغ > مدير الاسم، في مدير الاسم مربع الحوار، انقر فوق جديد زر ، انظر لقطات الشاشة:

3. في خرجت اسم جديد الحوار ، أدخل الاسم List1 في الاسم واكتب هذه الصيغة = ROW (INDIRECT ("1:" & ROWS (Range1))) (النطاق 1 هو اسم النطاق الذي قمت بإنشائه في الخطوة 1) في ملف يعود الى الحقل ، انظر لقطة الشاشة:

4. انقر OK للعودة إلى مدير الاسم الحوار ، ثم تابع للنقر جديد لإنشاء اسم نطاق آخر ، في اسم جديد الحوار ، أدخل الاسم List2 في الاسم واكتب هذه الصيغة = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (النطاق 1 هو اسم النطاق الذي قمت بإنشائه في الخطوة 1) في ملف يعود الى الحقل ، انظر لقطة الشاشة:

5. بعد إنشاء أسماء النطاقات ، يرجى تطبيق صيغة الصفيف التالية في الخلية B2:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")، و اضغط Shift + Ctrl + Enter معًا ، ثم اسحب مقبض التعبئة لأسفل إلى الخلية B8 ، الرقم الأخير من القائمة ، ويمكنك رؤية الأرقام التي يبلغ إجمالي المبلغ 480 تم تمييزها على أنها X في العمود B ، انظر لقطة الشاشة:

ابحث عن مجموعة خلايا تساوي مجموعًا محددًا باستخدام Solver Add-in

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

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

2. ثم اضغط Go زر للدخول الوظائف الإضافية الحوار ، تحقق الوظيفة الإضافية Solver الخيار، وانقر OK لتثبيت هذه الوظيفة الإضافية بنجاح.

3. بعد تنشيط الوظيفة الإضافية Solver ، فأنت بحاجة إلى إدخال هذه الصيغة في الخلية B9: = SUMPRODUCT (B2: B9، A2: A9)، (B2: B9 عبارة عن خلايا أعمدة فارغة بجانب قائمة الأرقام الخاصة بك ، و A2: A9 هي قائمة الأرقام التي تستخدمها. )، و اضغط أدخل مفتاح ، انظر لقطة الشاشة:

4. ثم اضغط البيانات > حلالا للذهاب إلى معلمة Solver في مربع الحوار ، يرجى القيام بالعمليات التالية:

(1.) انقر  زر لتحديد الخلية B10 حيث الصيغة الخاصة بك من تعيين الهدف قسم.

(2.) ثم في إلى القسم، حدد قيمة ال، وأدخل القيمة المستهدفة 480 ما تحتاج إليه؛

(3.) تحت عن طريق تغيير الخلايا المتغيرة القسم ، الرجاء الضغط زر لتحديد نطاق الخلايا B2: B9 حيث سيتم وضع علامة على الأرقام المقابلة الخاصة بك.

5. ثم انقر فوق أضف زر للذهاب إلى أضف القيد مربع الحوار، انقر فوق زر لتحديد نطاق الخلايا B2: B9وحدد بن من القائمة المنسدلة ، انظر لقطة الشاشة:

6. انقر OK للعودة معلمة Solver الحوار ، ثم انقر فوق حل زر ، بعد بضع دقائق ، أ نتائج Solver يظهر مربع الحوار ، ويمكنك رؤية مجموعة الخلايا التي تساوي مبلغًا محددًا 480 تم وضع علامة عليها كـ 1. في نتائج Solver الحوار ، الرجاء التحديد احتفظ بحل Solver الخيار، وانقر OK للخروج من مربع الحوار. انظر لقطة الشاشة:

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

تعتبر الطريقتان الأوليان معقدتين بالنسبة لمعظم مستخدمي Excel لدينا ، وهنا يمكنني إنشاء رمز VBA لحل هذه المهمة بسرعة وسهولة.

للحصول على النتيجة الصحيحة ، يجب عليك فرز قائمة الأرقام بترتيب تنازلي أولاً. ثم قم بالخطوات التالية:

1. اضغط باستمرار على ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، والصق الكود التالي في ملف وحدة نافذة او شباك.

كود فبا: ابحث عن تركيبة الخلايا التي تساوي مبلغًا معينًا:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. ثم احفظ وأغلق نافذة الكود هذه ، ثم ارجع إلى ورقة العمل ، وأدخل هذه الصيغة = getcombination (A2: A9، C2) في خلية فارغة ، واضغط على أدخل المفتاح ، ستحصل على النتيجة التالية التي تعرض الأرقام المركبة التي تساوي مجموعًا معينًا ، انظر الصورة:

ابحث عن جميع التركيبات التي تساوي مبلغًا معينًا مع ميزة مذهلة

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

بعد تثبيت كوتولس ل إكسيل، يرجى القيام بذلك على النحو التالي:

1. انقر كوتولس > وصف المنتج > تشكل رقم، انظر لقطة الشاشة:

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

3. ثم انقر فوق OK ، سيظهر مربع موجه لتذكيرك بتحديد خلية لتحديد النتيجة ، انظر لقطة الشاشة:

4. ثم انقر فوق OK، والآن ، تم عرض جميع التركيبات التي تساوي هذا الرقم كما هو موضح أدناه:

انقر لتنزيل Kutools for Excel والتجربة المجانية الآن!

عرض توضيحي: ابحث عن مجموعة خلايا تساوي مبلغًا معينًا في Excel

---

أفضل أدوات إنتاجية المكتب

Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪

• إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
• سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
• دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات
• حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
• نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
• استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
• سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
• اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
• أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2021 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.

اقرأ أكثر... تحميل مجاني ... شراء... 

---

يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

• تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
• فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
• يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!

اقرأ أكثر... تحميل مجاني ... شراء...