السلام عليكم

هذه الدالة من أقوى و أجمل دوال إكسل

لكنها ليست واضحة فى أذهان كثيرين

لنبدأ العمل

عندنا عمود مكون من 3 خلايا قيمتها على الترتيب
1,2,3
و عمود آخر
4,5,6

هاتان مصفوفتان
و متطابقتان

يعنى كلاهما 3 عناصر

أنت تريد عمل شيئين

الأول
ضرب كل عنصر من المصفوفة الأولى
فى العنصر المقابل له فى المصفوفة الثانية

يعنى
1×4 = 4
2×5 = 10
3 × 6 = 18

أنت لن تكتفى بذلك

أنت تريد جمع نواتج الضرب
4+10+18 = 32

مارأيكم

دالة
Sumproduct

تقوم بالعمل

الصيغة العامة
http://posterous.com/getfile/files.posterous.com/kemas/dBYy2Kg8SzMsE2UlzzPo4zCDa9UpzYk9JYpz5Tz8iATe2Wzb2d 6x2mrqdGiN/image001.jpg
=sumproduct(array1;array2;array3;...)

=sumproduct(a1:a3;b1:b3)

على اعتبار أن المصفوفة الأولى فى a1:a3
و الثانية فى b1:b3

إلى هنا الأمر مذهل و رائع

لكنه عادى جدا – غير مذهل – بالنسبة لباقى إمكانات هذه الدالة الرهيبة

سنكتفى بهذا الليلة

على أن نستعرض بعض أمثلتها العجيبة
فى المشاركات التالية


إلا أن المرفق
يشتمل على كل الأمثلة

إن كنت من المستعجلين

فإليك الملف


هنا (http://posterous.com/getfile/files.posterous.com/kemas/ZUAo4dP8iXJKygOeDhrMOxPpukLgT3dBWxPFRi3jwzWTq7kz2q 75zK9uUiz6/Book1.xls)


و إلى لقاء
لنستكمل شرح الأمثلة فى الملف

بارك الله فيك

شكرا لك أخي العزيز وهي فرصة عظيمة لكل محبي الأكسل
ان يري ابداعاتهم

بارك الله فيك ونفعك مما علمك

أهلا و سهلا

نستكمل بقية الأمثلة
إن شاء الله

جارى التحضير

مثال
عندنا مصفوفتان كما بالصورة
http://posterous.com/getfile/files.posterous.com/kemas/oiiueMvY5ncYMBOpTSRZXivTNlav0klLdXCQEsGjFixxY2sGyS 3Jr6WOqhh9/image001.jpg
الأولى
K2:k4
والثانية
L2:l4
تريد ضرب كل عنصر مع العنصر المقابل له فى المصفوفة الأخرى
ثم بعد ذلك نجمع نواتج الضرب

أمامك طريقتان
الأولى
استخدام معامل الضرب لضرب 1×4
ثم
2×5
ثم
3×6
ثم بعد ذلك فى خطوة تالية
تجمع هذه النواتج

دالة
Sumproduct
تنفذ الخطوتين فى خطوة واحدة مختصرة
و إلى شرح مثال قادم من الأمثلة الموجودة بالملف المرفق
المثال الثالث
و فيه نتعرف على سبب مهم من أسباب الخطأ فى استعمال هذه الدالة
كما نتعرف على إمكانية هائلة و رائعة من إمكانياتها
انظر الصورة
http://posterous.com/getfile/files.posterous.com/kemas/SSmMVM2ncMKyicpARy70Q8PAGnE9DhFh2dopsGbDusXy4xT1Kl 83JAiCJ4oG/image002.jpg.scaled.500.jpg (http://posterous.com/getfile/files.posterous.com/kemas/mvGBXCyw7aVIHJuvz2cqQQX7okU3EyMKQMJbrJ2Ipy84gG86iC flSkwMCKf5/image002.jpg)
الجدول الذى أمامنا يحتوى على بيانات متنوعة
اسم الشخص
مهنته
مبلغ يخصه
أنت تريد
أن تجمع المبالغ
بشروط
لكن ليس كل هذه الشروط فى عمود واحد
أنت تريد أن تجمع بشرط أن
1 – يكون اسم الشخص هو محمد
2- أن يكون محمد الموظف و ليس المدرس ولا غيره
وهذا ينطبق كما ترى بالصورة على الصفوف المظللة بالأصفر
3 – الشرط الثالث فى العمود الثالث هو انتقاء الصفوف التى ينطبق عليها الشرطان السابقان فقط
فاختيار الخلية فى العمود الثالث
تابع للشرطين السابقين
هل تحلم أن تنفذ ذلك بدالة واحدة ؟
نعم هذا سهل جدا مع دالتنا هذه
بل يمكن زيادة شروط أخرى لو كانت هناك أعمدة أخرى
الآن
هل خمنتم ماذا ستجمع الدالة
نعم
ستجمع
محمد موظف 22
+ محمد موظف 32
يعنى ببساطة 52
لكن
الدالة لم تعمل
مع أننا كتبناها مضبوطة
و أعطتنا ناتج صفر
فما السبب
اكتشاف السبب وراء فهم كيفية حساب الدالة لهذا الناتج 52
و هذه هى فرصتنا لفهم هذه الدالة و طريقة عملها
لن نطيل و هذه هى الطريقة التى تختصر فى لمح البصر عند الضغط على مفتاح الإدخال
كل عمود من هذه الأعمدة الثلاثة هو مصفوفة
ستعمل الدالة كما عملت فى المثال السابق
ففى الصف الأول
نأخذ من اليمين العنصر الأول محمد
انطبق الشرط الأول
وبجواره مدرس
اختل الشرط
فيلغى هذا الصف وننظر للثانى
أى الصف الثانى بنقس الطريقة
على موظف 40
لشرط الثانى موجود
لكن الأول مفقود
وهكذا حتى نصل للصف الخامس من المصفوفة
وهو باللون الأصفر
فتجد
محمد موظف 20
و الشروط مكتملة
كما الحال فى الصف 12 المظلل بالأصفر
هذه هى طريقة العمل
فلماذا لم تعمل الدالة و أعطتنا ناتج صفر
الجواب بسرعة
هذه الدالة تتعامل مع الأرقام فقط
لذلك فهى لم تر كلمة محمد ولا موظف و لا مدرس
فكيف تتصرف الدالة عندما لا ترى الحروف ؟
ببساطة تحول النص إلى صفر
الآن اضرب مع من جديد العناصر الثلاثة فى المصفوفات الثلاثة
محمد مدرس 33
= صفر × صفر × 33
كم يساوى ؟ بساوى صفرا
طيب
الصف الذى انطبق عليه الشرط
محمد موظف 20
= صفر ×صفر ×20
بالطبع يساوى صفرا
و هكذا
الناتج النهائي يساوى صفرا
ما الحل ؟
ماذا نريد
نريد أن نجبر الدالة على رؤية محمد و تعطى 1
و رؤية غير محمد و تعطى صفرا
فكيف نجبر الدالة على رؤية النصوص أصلا ؟
الحل طبعا فى الملف المرفق لكن فى المعادلة التى تحت هذه
عليكم محاولة اكتشاف الفرق بين المعادلتين
فهذه هى الطريقة
قبل أن نواصل
إذا اكتمل فهمك لهذه الدالة و معرفة طريقة عملها
تكون قد حصلت على كنز كبير
إن شاء الله
وإلى لقاء
__________

مثال
عندنا مصفوفتان كما بالصورة
http://posterous.com/getfile/files.posterous.com/kemas/oiiueMvY5ncYMBOpTSRZXivTNlav0klLdXCQEsGjFixxY2sGyS 3Jr6WOqhh9/image001.jpg
الأولى
K2:k4
والثانية
L2:l4
تريد ضرب كل عنصر مع العنصر المقابل له فى المصفوفة الأخرى
ثم بعد ذلك نجمع نواتج الضرب

أمامك طريقتان
الأولى
استخدام معامل الضرب لضرب 1×4
ثم
2×5
ثم
3×6
ثم بعد ذلك فى خطوة تالية
تجمع هذه النواتج

دالة
Sumproduct
تنفذ الخطوتين فى خطوة واحدة مختصرة
و إلى شرح مثال قادم من الأمثلة الموجودة بالملف المرفق
المثال الثالث
و فيه نتعرف على سبب مهم من أسباب الخطأ فى استعمال هذه الدالة
كما نتعرف على إمكانية هائلة و رائعة من إمكانياتها
انظر الصورة
http://posterous.com/getfile/files.posterous.com/kemas/SSmMVM2ncMKyicpARy70Q8PAGnE9DhFh2dopsGbDusXy4xT1Kl 83JAiCJ4oG/image002.jpg.scaled.500.jpg (http://posterous.com/getfile/files.posterous.com/kemas/mvGBXCyw7aVIHJuvz2cqQQX7okU3EyMKQMJbrJ2Ipy84gG86iC flSkwMCKf5/image002.jpg)
الجدول الذى أمامنا يحتوى على بيانات متنوعة
اسم الشخص
مهنته
مبلغ يخصه
أنت تريد
أن تجمع المبالغ
بشروط
لكن ليس كل هذه الشروط فى عمود واحد
أنت تريد أن تجمع بشرط أن
1 – يكون اسم الشخص هو محمد
2- أن يكون محمد الموظف و ليس المدرس ولا غيره
وهذا ينطبق كما ترى بالصورة على الصفوف المظللة بالأصفر
3 – الشرط الثالث فى العمود الثالث هو انتقاء الصفوف التى ينطبق عليها الشرطان السابقان فقط
فاختيار الخلية فى العمود الثالث
تابع للشرطين السابقين
هل تحلم أن تنفذ ذلك بدالة واحدة ؟
نعم هذا سهل جدا مع دالتنا هذه
بل يمكن زيادة شروط أخرى لو كانت هناك أعمدة أخرى
الآن
هل خمنتم ماذا ستجمع الدالة
نعم
ستجمع
محمد موظف 22
+ محمد موظف 32
يعنى ببساطة 52
لكن
الدالة لم تعمل
مع أننا كتبناها مضبوطة
و أعطتنا ناتج صفر
فما السبب
اكتشاف السبب وراء فهم كيفية حساب الدالة لهذا الناتج 52
و هذه هى فرصتنا لفهم هذه الدالة و طريقة عملها
لن نطيل و هذه هى الطريقة التى تختصر فى لمح البصر عند الضغط على مفتاح الإدخال
كل عمود من هذه الأعمدة الثلاثة هو مصفوفة
ستعمل الدالة كما عملت فى المثال السابق
ففى الصف الأول
نأخذ من اليمين العنصر الأول محمد
انطبق الشرط الأول
وبجواره مدرس
اختل الشرط
فيلغى هذا الصف وننظر للثانى
أى الصف الثانى بنقس الطريقة
على موظف 40
لشرط الثانى موجود
لكن الأول مفقود
وهكذا حتى نصل للصف الخامس من المصفوفة
وهو باللون الأصفر
فتجد
محمد موظف 20
و الشروط مكتملة
كما الحال فى الصف 12 المظلل بالأصفر
هذه هى طريقة العمل
فلماذا لم تعمل الدالة و أعطتنا ناتج صفر
الجواب بسرعة
هذه الدالة تتعامل مع الأرقام فقط
لذلك فهى لم تر كلمة محمد ولا موظف و لا مدرس
فكيف تتصرف الدالة عندما لا ترى الحروف ؟
ببساطة تحول النص إلى صفر
الآن اضرب مع من جديد العناصر الثلاثة فى المصفوفات الثلاثة
محمد مدرس 33
= صفر × صفر × 33
كم يساوى ؟ بساوى صفرا
طيب
الصف الذى انطبق عليه الشرط
محمد موظف 20
= صفر ×صفر ×20
بالطبع يساوى صفرا
و هكذا
الناتج النهائي يساوى صفرا
ما الحل ؟
ماذا نريد
نريد أن نجبر الدالة على رؤية محمد و تعطى 1
و رؤية غير محمد و تعطى صفرا
فكيف نجبر الدالة على رؤية النصوص أصلا ؟
الحل طبعا فى الملف المرفق لكن فى المعادلة التى تحت هذه
عليكم محاولة اكتشاف الفرق بين المعادلتين
فهذه هى الطريقة
قبل أن نواصل
إذا اكتمل فهمك لهذه الدالة و معرفة طريقة عملها
تكون قد حصلت على كنز كبير
إن شاء الله
وإلى لقاء
__________


بارك الله فيك

أهلا وسهلا بك أخى
وشكرا لمرورك
اقتباس كبير

الابداع بحق
نتظر المزيد على لهفة
خالص تحياتى

السلام عليكم

كلما تغافلت عن إكمال هذه الشروح
لظنى أن أحدا لا يستفيد منها ولا ينتظرها

كلما أرجعنى عن هذا
ردود كرد الأخ الأستاذ سيد - حفظه الله -

فلنكمل
===========

عرفنا أن المعادلة التالية لاتعمل
مع أن بناءها صحيح
و صيغتها مضبوطة

=SUMPRODUCT($A$2:$A$14="محمد")*($B$2:$B$14="موظف")*($C$2:$C$14)

الأسباب

المطلوب من الدالة التحقق من أن قبمة الخلية هى " موظف"

هل تعلم أن التحقق يتم و يرجع النتيجة

إذا كانت قيمة الخلية " محمد "

يرجع إكسل القيمة
true

و إذا لم تكن قيمتها " محمد "
ترجع القيمة
false

true و false
هذه أيضا نصوص يعنى حروف
ليست أرقاما

وأختنا الدالة هذه لا تعمل إلا مع الأرقام

نريد أن نستبدل ب true و false أرقاما

مثلا
true = 1
false = 0

فما السبيل ؟

لاحظ التعديل الذى أجريناه هنا

=SUMPRODUCT(--($A$2:$A$14="محمد")*--($B$2:$B$14="موظف")*($C$2:$C$14))

لاحظتم الفرق ؟

وضعنا علامتى " ناقص " قبل المصفوفة

الأولى
والثانية
ولم نضع قبل الثالثة

لماذا

لأن المصفوفة الثالثة أرقام ؟
لا
لأن المصفوفة الثالثة ليس فيها شروط

و الأولى و الثانية معيار

يعنى شرط

يرجع

true و false

" لو أردت أن أضيف شرطا للمصفوفة الثالثة أيضا سأضع هاتين العلامتين -- " مع أنها رقمية

الآن ترجم إلى لغة الأرقام
الصف الأول
محمد مدرس 33 =
1 × صفر × 33 = صفر
http://posterous.com/getfile/files.posterous.com/kemas/SSmMVM2ncMKyicpARy70Q8PAGnE9DhFh2dopsGbDusXy4xT1Kl 83JAiCJ4oG/image002.jpg.scaled.500.jpg
الصف السادس

محمد موظف 20 =
1 × 1 × 20 = 20

و الصف ال 13

محمد موظف 32
1 × 1 × 32 = 32

و بالجمع

20 + 32 = 52

==========

أظن الآن أشرقت الشمس من جديد

ملخص

* إذا كنت ستضرب مصفوفات كلها أرقام و ليس لديك أى شرط تتحقق منه
فلا مشكلة عندك

* إذا كنت ستضرب مصفوفات بمعيار أو شرط معين
ضع ( -- ) قبل المصفوفة التى بها الشرط
حتى لو كانت المصفوفات المضروبة كلها أرقام
لكن هناك شروط

هذه الشروط ترجع نصوصا
true - false
فحولها لأرقام
صفر و 1

وإلى لقاء آخر مع مزيد من التوضيح

و الحمد لله أولا و آخرا

http://islamroses.com/zeenah_images/post14.gif

مرحبا بك أستاذى
جزاك الله خيرا

http://posterous.com/getfile/files.posterous.com/kemas/SSmMVM2ncMKyicpARy70Q8PAGnE9DhFh2dopsGbDusXy4xT1Kl 83JAiCJ4oG/image002.jpg.scaled.500.jpg

باقى الأمثلة أبسط من ذلك

هذه المعادلة
=SUMPRODUCT(--($A$2:$A$14="محمد"))

لحساب " عدد " الخلايا التى يوجد بها كلمة " محمد " فى المجال المحدد

و النتيجة = 4

وهذه جزء من المعادلة التى سبقتها


المعادلة

=SUMPRODUCT(--($C$2:$C$14<30))

لحساب عدد الخلايا التى هى أقل من 30

و عددها = 6

وذلك فى عمود المبلغ

لاحظ هنا أننا وضعنا علامتى الناقص
مع أن عمود المبلغ هو أرقام

لكن الشرط الذى وضعناه ليس رقما

لأن التحقق منه يرجع

true

أو
false

و هى نصوص


إذن المعادلة السابقة لعد الخلايا التى هى أقل من 30

ماذا لو أردت جمع قيم تلك الخلايا و ليس عدها فقط

المعادلة التالية هى نفس السابقة مع زيادة

=SUMPRODUCT(--($C$2:$C$14<30)*($C$2:$C$14))

هنا الشرط السابق مضروبا فى نفس المصفوفة بدون شروط

و إلى هنا ينتهى شرح هذه الدالة

و عليكم مواصلة دراستها و التطبيق عليها

فى عمل الشيتات و غيره

على سبيل المثال

حساب عدد الراسبين أو الناجحين
أو غيره


وشكرا للمتابعة

جزاك الله كل خير

ياه
موضوع قديم

شكرا لمرورك

جزاك الله خيرا

الكود التالى
=SUMPRODUCT(--($A$2:$A$14="محمد")*($B$2:$B$14="موظف")*($C$2:$C$14))يمكن أن يكتب هكذا بدون شرططين --
=sumproduct(($a$2:$a$14="محمد")*($b$2:$b$14="موظف")*($c$2:$c$14))

شكرا لكم
جزاكم الله خيرا

تمام أستاذ حسن - إذا كنت جربت ذلك
بارك الله فيك

جزاك الله كل الخير وزادك فهماً علي فهم وعلماً يُزاد بعلم

شكرا لك أستاذى العزيز
بارك الله فيك