Note: The other languages of the website are Google-translated. Back to English

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

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


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

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

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 ، انظر لقطة الشاشة:

  • الملاحظات:
  • في الصيغة الطويلة أعلاه: List1, List2 و النطاق 1 هي أسماء النطاقات التي أنشأتها في الخطوات السابقة ، C2 هي القيمة المحددة التي تريد إضافة الأرقام إليها.
  • إذا كان هناك أكثر من مجموعة واحدة من القيم لها مجموع يساوي القيمة المحددة ، فسيتم إدراج مجموعة واحدة فقط.

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

كوتولس ل إكسيل's تشكل رقم يمكن أن تساعدك الأداة في العثور على كافة التركيبات والتركيبات المحددة التي تساوي رقم مجموع معين وإدراجها بسرعة وسهولة. انقر لتنزيل Kutools for Excel!

كوتولس ل إكسيل: مع أكثر من وظائف Excel الإضافية سهلة الاستخدام ، يمكنك تجربتها مجانًا دون قيود في أيام. تنزيل وتجربة مجانية الآن!


ابحث عن مجموعة خلايا تساوي مجموعًا محددًا باستخدام 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) في خلية فارغة ، واضغط على أدخل المفتاح ، ستحصل على النتيجة التالية التي تعرض الأرقام المركبة التي تساوي مجموعًا معينًا ، انظر الصورة:

  • الملاحظات:
  • في الصيغة أعلاه ، 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 يومًا.
علامة تبويب kte 201905

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

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
فرز التعليقات حسب
التعليقات (47)
لا يوجد تقييم. كن أول من يقيم!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل هناك طريقة لتوسيع النطاق بحيث يشمل أكثر من 8 أرقام؟ أيضًا ، لست متأكدًا من كيفية عمل هذه الوظيفة: "= ROW (INDIRECT (" 1: "& 2 ^ ROWS (Range1)))". إذا حاولت توسيع "Range1" إلى ما بعد 15 صفًا ، فسيظهر لي الخطأ #Ref. إنه يعمل بشكل رائع مع الأرقام الثمانية فقط ، ولكن ماذا لو أردت تضمين 8 رقمًا أو حتى 50 مثلاً.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
المشكلة نفسها
تم تصغير هذا التعليق بواسطة المشرف على الموقع
رجل رائع !!! رجل رائع !!!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل هناك طريقة لتوسيع النطاق كما يقول Thom ، ليقول ما يصل إلى 50 رقمًا ، ولكن أيضًا لإجمالي ستة فقط من الأرقام خارج النطاق هذا المجموع إلى الإجمالي المحدد؟ سيوفر حاليًا جميع المجموعات بهذا المجموع إلى الإجمالي المحدد. شكرًا
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مدهش. تعذر الحصول على الصيغة الكبيرة للعمل ولكن الوظيفة الإضافية للحل عملت بشكل مثالي. أنقذني الكثير من العمل.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لكنها تستغرق الكثير من الوقت
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أنا في أحسن الأحوال مبتدئ متقدم في Excel. لقد جربت كل شيء ولم ينجح. ما الذي يمكن أن أفعله خطأ؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كيف لو احتجت أكثر من تركيبة واحدة؟ شكرا
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أهلاً. نسخة الصيغة لم تعمل معي أيضًا. يبدو أنه يفتقد خطوة. لا أرى أين يأتي الرقم المحدد في الخلية C2 في الصيغة.

شكر
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا ، دوري ،


لا توجد صيغة في C2 ، إنها فقط القيمة المحددة التي تريد إضافة الأرقام إليها.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، لقد قمت بتنزيل Kutools ولكن لا يمكنني العثور عليه للعثور على جميع المجموعات الأقل من الإجمالي المحدد.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
تمكنت من الحصول على المثال مع Range1 للعمل مع النطاق الخاص بي في 12 صفًا ، ولكن عندما قمت بتغيير النطاق إلى 42 صفًا ، لم يعمل. حتى أنني أعدت تشغيل العملية بأكملها بإصدار 42 صفًا ولم ينجح ذلك أيضًا. أيه أفكار؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
متألق!!!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل يمكنك تحميل ملف اكسل؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لم يعمل الماكرو إذا كان هناك أكثر من حل.
أيضًا ، لم أعمل إذا وجدت "0"
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا يا لورينا ،
قبل تطبيق رمز فبا أعلاه ، يجب عليك فرز قائمة الأرقام بترتيب تنازلي أولاً.
ثانيًا ، لا يعمل الرمز بشكل صحيح للحصول على العدد الإجمالي 0.
أتمنى أن يساعدك ، شكرا لك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أحاول تحديد أفضل مزيج من المنتج ولست متأكدًا مما إذا كانت هذه هي أفضل طريقة للقيام بذلك. على الأكثر ، أستخدم ثلاثة منتجات في مزيج بخمس مواصفات لكل منها. جميع المواصفات خطية ويمكن حساب المتوسط ​​عند مزجها. عادة ما يكون مزيج واحد 5 رطل وكل دفعة 45,000 رطل. في معظم الأوقات ، تكون توليفاتنا 30,000 كيلو + 15 كيلو لكني أود أن أكون قادرًا على حساب التوليفات غير العادية باستخدام الزيادات وصولاً إلى 30 رطل.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،

مشكلتي في هذه الصيغة هي أنها تعطيني قيمة واحدة لأوقات كافية للحصول على القيمة المستهدفة ..
توجد في قائمة القيم المختلفة بعض القيم التي تتساوى مع بعضها البعض.

على سبيل المثال ، لدي 0,16،3 لـ 593 مرات (القيم الأولى في القائمة) وتعطيني الصيغة الإجابة بأن القيمة المستهدفة هي 0,16 من XNUMX،XNUMX.

لماذا لا تجمع بين قيم مختلفة للحصول على القيمة المستهدفة الخاصة بي؟ إنه يختار قيمة واحدة فقط ويعطي عدد المرات التي يجب أن تكون فيها القيمة المستهدفة.

اي مساعدة او فكرة؟


شكر!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لا أحد يعرف ما إذا كان هذا يعمل على أوراق جوجل
تم تصغير هذا التعليق بواسطة المشرف على الموقع
نعم ، هناك امتداد مشابه لبرنامج Excel Solver يسمى "solver"
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل سيعرف شخص ما كيفية ضبط وظيفة VBA Getcombination بحيث لا يُسمح بالتكرار؟

على سبيل المثال ، بالنسبة للأرقام 1,2,3,4,5,13،14،1,13،14،1،XNUMX إذا كان سيتم تحقيق XNUMX ، فإن XNUMX،XNUMX هو حل ، وليس XNUMX من XNUMX.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
دالة GetCombination (CoinsRange As Range ، SumCellId As Double) كسلسلة
'تحديث بواسطة Extendoffice 20160506
خافت xStr كسلسلة
خافت xSum مزدوج
خافت xCell كمجموعة
xSum = SumCellId
لكل xCell في CoinsRange
إذا لم يكن (xSum / xCell <1) إذن
xStr = xStr & "1 من" & xCell & ""
xSum = xSum - xCell
إنهاء حالة
التالى
GetCombination = xStr
نهاية وظيفة
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، هذا يعطيني خطأ اسم مرتبكًا لرمز vba
أي مساعدة لأنني لا أعرف شيئًا في VBA
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا رام ، هذا يعمل بشكل جيد ولكنه لا يعطي المبلغ الفعلي.
مثال: إذا كان لدي 23,34,25,28,10,17،12،80،23,28,17،12،23,28,17 & 12 ولدي مبلغ XNUMX (وهو مجموع XNUMX،XNUMX،XNUMX & XNUMX) ، أحتاج إلى رمز vba الذي يمكنه العثور على هذه المجموعة (مجموع XNUMX ، XNUMX ، XNUMX و XNUMX) هل يمكنك مساعدتي في هذا؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،
شكرا جزيلا للمعلومات.
كيف يمكن إيجاد أكثر التركيبات تقريبية إذا لم يكن هناك قيمة دقيقة.
شكرا جزيلا،
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،
شكرا هذا جيد جدا
كيف يمكن إيجاد أكثر التركيبات تقريبية إذا لم يكن هناك قيمة دقيقة.
تشكرات
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل لن تعمل الوظيفة الإضافية للحل إذا كانت هناك أرقام سالبة في القائمة أو إذا كانت قيمة الرقم 0؟ أحاول إيجاد مجموع الأرقام في القائمة التي تساوي صفرًا مع كون بعض الأرقام سالبة وموجبة ، لكن الحل لا يعمل. لقد قمت بتغيير رقمين في قائمتي للاختبار للتأكد من اتباع الخطوات بشكل صحيح وأنها نجحت في الاختبار. يرجى توضيح ما إذا كانت هناك طريقة لحلها بأرقام سالبة وموجبة للعثور على قيمة 0.
L     a
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل حصلت على إجابة من قبل أو وجدت طريقة للقيام بذلك؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،
إذا كانت هناك أرقام موجبة وسالبة في العمود ، فإنني أوصيك بتطبيق ميزة Kutools for Excel's Make up a number ، فيمكنها حل مشكلتك بسرعة وسهولة.

يمكنك تنزيل Kutools for Excel والإصدار التجريبي المجاني 60 يومًا. أرجوك حاول!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لدي 1162 خلية للعثور على العدد x. يخبرني Excel أن هناك عددًا كبيرًا جدًا من الخلايا المتغيرة. مجموعة بيانات صغيرة جدًا! أي اقتراحات؟ شكرًا!
لا توجد تعليقات منشورة هنا حتى الآن
عرض المزيد
اترك تعليقاتك
النشر كضيف
×
قيم المنشور:
0   الشخصيات
المواقع المقترحة

تواصل معنا

حقوق التأليف والنشر © 2009 - شبكة الاتصالات العالمية.extendoffice.com. | كل الحقوق محفوظة. مشغل بواسطة ExtendOffice. | | خريطة الموقع
Microsoft وشعار Office هما علامتان تجاريتان أو علامتان تجاريتان مسجلتان لشركة Microsoft Corporation في الولايات المتحدة و / أو دول أخرى.
محمي بواسطة Sectigo SSL