إكسيل هي واحدة من الأدوات الأكثر شعبية للعمل مع الجداول والحسابات. أحد التحديات التي قد تواجهها هو توزيع المبلغ بشكل متناسب بين المكونات المختلفة. على سبيل المثال ، لديك مبلغ إجمالي يجب توزيعه على عدة أقسام أو مشاريع وفقا لأسهم معينة. في هذه المقالة ، سوف ننظر في تعليمات مفصلة حول كيفية تنفيذ مثل هذا التوزيع في إكسيل وتقديم أمثلة لفهم أفضل.
الخطوة الأولى لتوزيع المبلغ بشكل متناسب هي تحديد حصص كل مكون. يمكن التعبير عن هذه الكسور بالنسب المئوية أو الكسور أو أي وحدات قياس أخرى. على سبيل المثال ، قد يكون لديك ثلاثة أقسام وتريد توزيع المبلغ وفقا لأسهمها: 40٪ لقسم المبيعات ، 30 ٪ لقسم التسويق و 30 ٪ لقسم التطوير. في إكسيل ، يمكنك استخدام الصيغ لحساب هذه الكسور.
بعد تحديد الأسهم ، يمكنك البدء في توزيع المبلغ. هناك عدة طرق لإنجاز هذه المهمة في إكسيل. تتمثل إحدى الطرق في استخدام صيغة تضاعف المبلغ الإجمالي في جزء كل مكون. على سبيل المثال ، إذا كان المبلغ الإجمالي هو 10000 روبل ، فستبدو الصيغة بالنسبة لقسم المبيعات كما يلي: =10000 * 0.4 ، لقسم التسويق — =10000 * 0.3 ولقسم التطوير — =10000 * 0.3.
يمكنك أيضا استخدام وظيفة سومبرودوكت لتوزيع المبلغ بشكل متناسب. للقيام بذلك ، تحتاج إلى إنشاء مجموعة من الصيغ ، حيث تضاعف كل صيغة المبلغ الإجمالي بالكسر المقابل. على سبيل المثال ، لتوزيع مبلغ 10000 روبل على أقسام المبيعات والتسويق والتطوير ، ستبدو الصيغة كما يلي: =منتج مستنقع({0.4 ، 0.3 ، 0.3} * 10000).
- المجموع والنسب
- الطريقة 1: استخدام الصيغة
- الطريقة 2: استخدام وظيفة "المجاميع الفرعية"
- الطريقة 3: استخدام "المجموع والمنتج"
- طريقة الوظيفة 4: استخدام صيغة بشرط
- مثال 1: توزيع المبلغ بين عدة فئات
- المثال 2: توزيع المبلغ اعتمادا على نسبة النسبة المئوية
- سؤال وجواب
- كيفية حساب نسبة كل عنصر من عناصر المجموع في إكسيل?
- كيف يمكنني توزيع المبلغ بين عدة أعمدة مع حصة متناسبة?
- كيف يمكنني توزيع المبلغ بين صفوف الجدول بحيث تضيف ما يصل إلى 100%?
- كيفية توزيع المبلغ بين صفوف الجدول تقريب إلى 2 منازل عشرية?
المبلغ والنسب
في الحسابات المالية ، غالبا ما يكون من الضروري توزيع المبلغ بين عدة عناصر بما يتناسب مع حصتها. على سبيل المثال ، لقد تلقيت مبلغا معينا من المال وتريد تقسيمه بين بنود الإنفاق المختلفة وفقا لأهميتها أو أولويتها.
هناك عدة طرق لحل مثل هذه المشاكل في إكسيل. واحد منهم هو استخدام صيغة النسبة. تساعد هذه الصيغة على توزيع الكمية وفقا للنسب المحددة.
- حدد الخلية التي تريد وضع نتيجة الحساب فيها.
- أدخل صيغة تشير إلى الخلية التي تحتوي على المبلغ الإجمالي والخلية التي تحتوي على المشاركة التي تريد توزيعها. على سبيل المثال:
| الخلية أ1: | =10000 |
| الخلية أ2: | =0.5 |
| الخلية ب1: | = أ1 * أ2 |
في هذا المثال ، نوزع مبلغ 10000 روبل وفقا لجزء من 0.5. الصيغة أ1*أ2 يحسب النتيجة ويضعها في الخلية ب1.
يمكنك نسخ هذه الصيغة لحساب القيم النسبية الأخرى. إذا كنت تريد أن يظل المبلغ الإجمالي كما هو ، يمكنك إضافة خلية أخرى لحساب الباقي:
| الخلية أ1: | =10000 |
| الخلية أ2: | =0.5 |
| الخلية ب1: | = أ1 * أ2 |
| الخلية ب2: | = أ1-ب1 |
في هذه الحالة ، تحتوي الخلية ب 2 على الباقي بعد تخصيص المبلغ.
وهكذا ، وذلك باستخدام الصيغ نسبة في إكسيل يسمح لك بسهولة وبسرعة توزيع المبلغ وفقا للكسور أو النسب المحددة.
الطريقة 1: استخدام الصيغة
طريقة واحدة لتوزيع المبلغ بشكل متناسب في إكسيل هو استخدام صيغة. دعونا نلقي نظرة فاحصة على كيفية القيام بذلك:
- فتح إكسل وإنشاء مستند جديد.
- في العمود الأول ، أدخل الأسماء أو التصنيفات التي تريد توزيع المبلغ عليها.
- في العمود الثاني ، أدخل القيم أو المعاملات المناسبة لكل عنصر.
- في العمود الثالث ، أدخل مبلغ التوزيع المطلوب.
- هي قيمة أو معامل هذا العنصر. اضغط على إدخال لتطبيق الصيغة.
- انسخ الصيغة من العمود الرابع أسفل الجدول بأكمله لتطبيقها على جميع العناصر.
- في العمود الخامس ، سترى القيم الموزعة لكل عنصر.
يمكنك استخدام هذه الطريقة لأي عدد من العناصر التي تريد توزيع المبلغ عليها بشكل متناسب. هذا هو واحد من أسهل الطرق للقيام بذلك في إكسيل.
مثال:
| الاسم | النسبة | المبلغ | التوزيع |
|---|---|---|---|
| أليس | 0.3 | 1000 | = ج2 * ب2 |
| بوب | 0.5 | 1000 | = ج3 * ب3 |
| كارول | 0.2 | 1000 | = ج4 * ب4 |
في هذا المثال ، يتم توزيع مجموع 1000 بين أليس وبوب وكارول بما يتناسب مع معاملاتهم. نتيجة لذلك ، ستحصل على القيم الموزعة في العمود الرابع.
الطريقة 2: استخدام وظيفة "المجاميع الفرعية"
في ميكروسوفت إكسيل ، هناك وظيفة خاصة تسمح لك بتوزيع المبلغ بشكل متناسب بين عدة قيم. هذه الوظيفة هي"المجاميع الفرعية".
- في العمود الأول من الجدول ، حدد القيم التي تريد توزيع المبلغ عليها.
- في العمود الثاني من الجدول ، حدد النسب المئوية التي يجب توزيع المبلغ عليها. يجب تحديد النسب المئوية في شكل عشري (على سبيل المثال ، 0.5 لـ 50٪).
- في العمود الثالث من الجدول ، استخدم وظيفة المجاميع الفرعية لتوزيع المبلغ. تبدو صيغة هذا كما يلي: = ب2 * C ج 1 1 ، حيث ب2 هي خلية بنسبة مئوية ، و C ج 1 1 هي خلية بالمبلغ المراد توزيعه.
- انسخ الصيغة إلى جميع خلايا العمود الثالث لتوزيع المجموع لكل قيمة.
- في الخلية الأخيرة من العمود الثالث ، حدد المبلغ المراد توزيعه. يجب تحديد هذه القيمة بنفس تنسيق النسب المئوية (على سبيل المثال ، 100 لـ 100٪).
بعد الانتهاء من هذه الخطوات ، ستقوم وظيفة "المجاميع الفرعية" بتوزيع المبلغ تلقائيا بما يتناسب مع النسب المئوية المحددة. يتم عرض النتائج في العمود الثالث من الجدول.
وبالتالي ، فإن استخدام وظيفة" المجاميع الفرعية " هي إحدى الطرق لتوزيع المبلغ بكفاءة بشكل متناسب في ميكروسوفت إكسيل.
الطريقة 3: استخدام وظيفة "المجموع والمنتج"
هناك طريقة أخرى لتوزيع المبلغ بشكل متناسب في إكسيل وهي استخدام وظيفة "المجموع والمنتج".
- في الخلية الأولى من العمود ، أدخل القيمة الأولى التي تريد توزيعها.
- في الخلية الثانية من العمود ، أدخل قيمة تحدد نسبة التوزيع.
- حدد كل من الخلايا ونسخها.
- في الصف أدناه ، حدد الخلية التي تريد بدء التوزيع منها.
- حدد عنصر القائمة" إدراج "وحدد" إدراج الصيغ " فيه.
يظهر مربع الحوار إدراج الصيغ ، حيث تحتاج إلى تحديد وظيفة المجموع والمنتج والنقر فوق موافق.
سيقوم إكسيل تلقائيا بتوزيع المبلغ بشكل متناسب في الصفوف أدناه باستخدام القيم والنسبة المدخلة.
| إدخال البيانات | النسبة | التوزيع |
|---|---|---|
| 100 | 0.5 | 50 |
| 200 | 0.3 | 60 |
| 150 | 0.2 | 30 |
وبالتالي ، باستخدام وظيفة المجموع والمنتج ، يمكنك بسهولة توزيع المبلغ بشكل متناسب في إكسيل.
الطريقة 4: استخدام صيغة مع شرط
طريقة أخرى لتوزيع المبلغ بشكل متناسب في إكسيل هو استخدام صيغة مع شرط. تتيح لك هذه الطريقة توزيع المبلغ تلقائيا وفقا للشروط المحددة.
1. إنشاء جدول مع البيانات ، بما في ذلك عمود مع النسب. على سبيل المثال:
| الاسم | النسبة |
|---|---|
| المنتج 1 | 0.3 |
| المنتج 2 | 0.2 |
| المنتج 3 | 0.5 |
2. قم بإنشاء خلية يتم فيها توزيع المبلغ. على سبيل المثال ، الخلية ب2.
3. اكتب الصيغة التالية في الخلية ب2:
=B1*إذا($A2="المنتج 1",$B2/SUM($B$2:$ب$4),إذا($A2="المنتج 2",$B2/SUM($B$2:$ب$4),إذا($A2="البند 3",$B2/SUM($B$2:$B4),"")))
ملاحظة: في الصيغة أعلاه ، استخدمنا الشرط إذا وظيفة. داخل دالة إف ، نتحقق من القيمة في العمود أ لكل صف من الجدول. إذا كانت القيمة هي " المنتج 1 "أو" المنتج 2 "أو" المنتج 3 " ، فإن الصيغة تحسب المبلغ مقسوما على مجموع كل النسب. إذا كانت القيمة لا تتطابق مع أي من الشروط ، تقوم الصيغة بإرجاع سلسلة فارغة.
4. انقر فوق الخلية ب2 واسحب الإكمال التلقائي لأسفل لتطبيق الصيغة على العمود بأكمله.
بعد الانتهاء من هذه الخطوات ، سيتم توزيع المبلغ تلقائيا في خلايا العمود ب وفقا للنسب المحددة. لاحظ أنه يمكن تقريب القيم الموجودة في العمود ب إلى العدد المطلوب من المنازل العشرية باستخدام وظيفة تنسيق الأرقام.
مثال 1: توزيع المبلغ بين عدة فئات
لنفترض أن لديك مبلغا من المال تريد توزيعه بين عدة فئات. على سبيل المثال ، لديك مبلغ 10000 روبل ، والتي تريد تقسيمها بين فئات "المنتجات" ، "النقل" ، "الترفيه".
لإكمال هذه المهمة ، يمكنك استخدام الخطوات التالية:
- قم بإنشاء جدول في إكسيل حيث سيحتوي العمود الأول على أسماء الفئات (على سبيل المثال ، "المنتجات" ، "النقل" ، "الترفيه") ، وسيتم ترك العمود الثاني فارغا لملء المبالغ.
- حدد النسب المئوية المطلوبة لكل فئة. على سبيل المثال ، يمكنك أن تقرر إنفاق 50 ٪ على "البقالة" و 30 ٪ على "النقل" و 20 ٪ على "الترفيه".
- اضرب المبلغ الذي تريد توزيعه (10000 روبل) بنسبة مئوية لتحديد المبلغ الذي يجب تخصيصه لكل فئة. املأ هذه القيم في العمود الثاني من الجدول.
- لخص النتائج بإضافة القيم من العمود الثاني من الجدول. تأكد من أن المبلغ يتوافق مع المبلغ الأولي (10000 روبل) ، واضبط الأرقام إذا لزم الأمر لضمان الاتساق.
نتيجة لذلك ، ستحصل على جدول يحتوي فيه العمود الثاني على المبالغ الموزعة بين كل فئة ، وفقا للنسب المئوية المحددة. الآن يمكنك استخدام هذه القيم لتخطيط النفقات وتتبع ميزانيتك.
على سبيل المثال ، قد يكون من المناسب لك استخدام هذه القيم لإنشاء رسوم بيانية أو مخططات لتمثيل كيفية توزيع المبلغ بين فئات مختلفة بشكل مرئي.
مثال 2: توزيع المبلغ اعتمادا على النسبة المئوية
إذا كنت بحاجة إلى توزيع المبلغ بشكل متناسب مع نسبة مئوية معينة ، فيمكنك استخدام صيغة تأخذ هذه النسبة في الاعتبار.
لنفترض أن لدينا البيانات التالية:
| الاسم | النسبة المئوية |
|---|---|
| الفئة 1 | 30% |
| الفئة 2 | 40% |
| الفئة 3 | 30% |
لتوزيع المبلغ بالتناسب مع النسبة المئوية المحددة ، اتبع الخطوات التالية:
- سجل النسبة المئوية لكل فئة.
- احسب المبلغ المراد توزيعه.
- اضرب كل نسبة في المقدار واقسمها على 100.
- ستكون القيم الناتجة مساوية لتوزيع المبلغ لكل فئة.
على سبيل المثال ، لدينا مبلغ 1000 روبل. يتم تعيين نسبة النسبة المئوية على النحو التالي:
- الفئة 1: 30%
- الفئة 2: 40%
- الفئة 3: 30%
سيكون توزيع المبلغ على النحو التالي:
- الفئة 1: 300 روبل (30 ٪ من 1000)
- الفئة 2: 400 روبل (40 ٪ من 1000)
- الفئة 3: 300 روبل (30 ٪ من 1000)
وبالتالي ، سيتم توزيع المبلغ بالتناسب مع النسبة المئوية المحددة.
سؤال وجواب
كيفية حساب نسبة كل عنصر من عناصر المجموع في إكسيل?
لحساب نسبة كل عنصر من عناصر المبلغ في إكسيل ، تحتاج إلى استخدام وظيفة حجم النسبة المئوية. على سبيل المثال ، إذا كان المبلغ المحدد في الخلية A1 و الخلايا B1 ، B2 ، B3 و B4 تشير إلى القيم التي تحتاج إلى أن تكون موزعة بشكل متناسب إلى هذا المبلغ ، ثم الصيغة سوف تبدو مثل هذا: =(B1/$A$1)*100, =(B2/$A$1)*100, =(B3/$A$1)*100, =(B4/$A$1)*100.
كيف يمكنني توزيع المبلغ بين عدة أعمدة مع حصة متناسبة?
لتوزيع المبلغ بين عدة أعمدة مع حصة متناسبة في إكسيل ، يمكنك استخدام وظيفة متابعة. على سبيل المثال ، إذا تم تحديد الكمية في الخلية أ1 ، وفي الخلايا ب1 ، ب2 و ب3 تحتاج إلى توزيع هذا المقدار بالتناسب مع 50٪ و 30 ٪ و 20 ٪ على التوالي ، فستبدو الصيغة كما يلي: = أ1 * سيستمر(50%,30%,20%).
كيف يمكنني توزيع المبلغ بين صفوف الجدول بحيث تضيف ما يصل إلى 100%?
لتوزيع المبلغ بين صفوف الجدول بحيث تضيف ما يصل إلى 100 ٪ في إكسيل ، يمكنك استخدام وظيفة تطبيع. على سبيل المثال ، إذا تم تحديد الكمية في الخلية أ1 ، وفي الخلايا ب1, ب2 و ب3 تحتاج إلى توزيع هذه الكمية بشكل متناسب مع 50 ٪ و 30 ٪ و 20 ٪ على التوالي ، فستبدو الصيغة كما يلي: =ب1*A أ A 1/تطبيع(ب1:ب3).
كيفية توزيع المبلغ بين صفوف الجدول تقريب إلى 2 منازل عشرية?
لتوزيع المبلغ بين صفوف الجدول مع التقريب إلى 2 منازل عشرية في إكسيل ، يجب عليك استخدام وظيفة الجولة. على سبيل المثال ، إذا تم تحديد الكمية في الخلية أ1 ، وفي الخلايا ب1 ، ب2 و ب3 تحتاج إلى توزيع هذه الكمية بشكل متناسب مع 50٪ و 30 ٪ و 20 ٪ على التوالي ، فستبدو الصيغة كما يلي: = جولة (ب1 * A أ 1 1 / تطبيع (ب1: ب3) ، 2).