أريد دالة تقوم بالعد بعدة شروط

[رضا الخشن]

إخواني الأعزاء

السلام عليكم و رحمة الله و بركاته ،،

مرفق ملف به جدول مبيعات العملاء خلال فترة زمنية معينة من عدة أصناف

المطلوب هو :-

معادلة لحساب عدد العملاء الذي تم التعامل معهم في الجدول ( بدون شروط أخرى )

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و طبعا بدون تكرار ( بعض العملاء تم تكرار التعامل معهم خلال الفترة الزمنية ) .

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و من تصنيف معين و من صنف معين .

أي أن المطلوب دالة للعد بدون شرط و أخرى تدخل بها شروط متعددة أقوم بتحديدها بنفسي ربما تشبه طريقة العمل بالدالة الساحرة Sumproduct .

أفيدوني جزاكم الله خيرا ... و تقبلوا مني أفضل تحية

تجميع _عدد_بعدة شروط و بدون تكرار.rar

[يحيى حسين]

السلام عليكم

أخي بخصوص طلبك الأول

معادلة لحساب عدد العملاء الذي تم التعامل معهم في الجدول ( بدون شروط أخرى )

 

=COUNTA(C3:C19)

 

و اما طلبك الثاني

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و طبعا بدون تكرار ( بعض العملاء تم تكرار التعامل معهم خلال الفترة الزمنية ) .

على فرض أن الخلية F88 تحتوي على تاريخ البداية و الخلية H88 تحتوي على تاريخ النهاية جرب هذه المعادلة

 

=SUMPRODUCT(--(B3:B19>=F88),--(B3:B19<=H88),--(1/COUNTIF(C3:C19,C3:C19)))

 

أما بخصوص طلبك الثالث

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و من تصنيف معين و من صنف معين .

و مع نفس الفرضيات السابقة و إضافة الى ان الخلية I89 تحتوي التصنيف و الخلية J89 تحتوي اسم الصنف

 =SUMPRODUCT((F2:S2=J89)*(E3:E77=I89)*(B3:B77>=F89)*(B3:B77<=H89)*SIGN(

((C3:C77<>"")/COUNTIF(C3:C77,C3:C77&"")))) 

جرب المعادلات و اعلمني بالنتيجة

لانني قمت بعملها على عجلة من امري

[رضا الخشن]

أخي الكريم

بارك الله فيك ، أشكرك على ردك المفصل ، إلا أنني عندما قمت بتطبيق المعادلات في ورقة العمل ظهر لي من خلال رسالة في الإكسل أن هناك خطأ في المعادلة و رفض تطبيقها .

كما أن المعادلة الأولى تقوم بعد العملاء حتى مع وجود تكرار و أنا أريد عد العملاء في العمود بدون تكرار .

سأنتظر إفادتك و جزاك الله خيراًً

رضا الخشن

[بن علية حاجي]

السلام عليكم ورحمة الله

بارك الله أخي الكريم يحيى حسين في ما قدمه وما يقدمه للمنتدى وجزاه الله عنا خير الجزاء... إن الصيغ التي قدمها الأخ حسين تعمل جيدا وقد قمت بوضعها في ملفك مع تصرف طفيف في بعضها (وأطلب العذر منه في ذلك) لتعمل بشكل (أعتقده صحيحا) في مطلوبك...

أتمنى أن نكون قد وفّقنا في ذلك أخي الكريم... أنظر الملف المرفق... وأنتظر منك الرد على الموضوع إن كان هناك أي استفسار في المسألة...

أخوكم بن علية

تجميع _عدد_بعدة شروط و بدون تكرار.rar

[رضا الخشن]

أخي الكريم

جزاك الله خيراً على نقل معادلات الأخ يحيى لورقة العمل و تطبيقها . لقد أفادني ذلك كثيرا إلا أنك قمت بتحدد النطاق بلا فراغات عند كتابة معادلة ( عدد العملاء المتعامل معهم (دون تكرار) بين تاريخين ) و أنا أريدها بكامل العمود مع وجود الفراغات لأنني سوف أكمل الورقة فيما بعد

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

وفقك الله و الأخ يحيى ، و جزاكم الله خيراً على إهتمامكم بالرد و المتابعة عنـا خير الجزاء

رضا الخشن

[يحيى حسين]

السلام عليكم و رحمة و بركاته

شكراً للأخ بن علية على إضافته الجميلة

و أخي رضا

بخصوص طلبك الأخير

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و من تصنيف معين و من صنف معين .

الحل الأول شعرت بوجود بعض المشاكل فيه و خصوصاً المعادلة التي تقوم بإحتساب عدد العملاء بدون تكرار

لانها ستعيد أرقام بكسور عشرية و ذلك طبيبعي نظراً لطريقة عمل الدالة

و أيضا آلية أحتساب العد مع معيار بشكل افي و ليس عامودي

و لقد حاولت فيه بدالة أخرى أرجو أن تجربها و تعلمني بالنتيجة

 


=SUM(--(FREQUENCY(IF((B3:B77>=F89)*(B3:B77<=H89)*(E3:E77=I89)*(INDEX(F2:S2,0,MATCH(J89,F2:S2,0))<>0),MATCH(C3:C77,C3:C77,0)),ROW(D3:D77)-ROW(D3)-1)<>0))

و لا تنسى أن تضغط على Ctrl+Shift+enter

أتمنى أن تفي هذه الدالة برغبتك

و أن يكون لي نصيب من دعائك

تم تعديل فبراير 16, 2010 بواسطه يحيى حسين

[يحيى حسين]

السلام عليكم و رحمة الله

أيضاً أخي جرب المعادلة التالية على المطلوب الثاني

معادلة لحساب عدد العملاء الذي تم التعامل معهم خلال فترة زمنية محددة و طبعا بدون تكرار ( بعض العملاء تم تكرار التعامل معهم خلال الفترة الزمنية ) .

 

=SUM(--(FREQUENCY(IF((B3:B77>=F88)*(B3:B77<=H88),MATCH(C3:C77,C3:C77,0)),ROW(D3:D77)-ROW(D3)-1)<>0))

 

و لا تنسى أن تضغط على Ctrl+Shift+enter

[رضا الخشن]

أخي الكريم يحيى

السلام عليكم و رحمة الله و بركاته ،،

المعادلة الأولى أوفت بالنتيجة و هي أبسط كثيرا من المعادلة التي تكرمت بوضعها أخيراً ، فأنا أعمل على ورقة ملف بها أكثر من 200 سطر و قد نجحت المعادلة بنجاح منقطع النظير و النتيجة هائلة .

ما أردته في الرد السابق هو المعادلة التي تقوم بحساب عدد العملاء المتعاملين ( دون تكرار ) خلال فترة زمنية محددة مع مراعاة حساب المعادلة على النطاق بالكامل بالفراغات الموجوده بحيث يتجدد ناتج المعادلة مع أي إدخالات أخرى

مرفق الملف الذي تم تطبيق معادلتك الأولى عليه

و لكم أفضل تحية و أنتظر ردكم ..

تجميع _عدد_بعدة شروط و بدون تكرار.rar

[يحيى حسين]

السلام عليكم و رحمة الله

أخي رضا

مع إحترامي لرغبتك و لكني أتوقع أن الحل لاثاني بمعادلات الصفيف أدق و أقوى من الحل الأول و لقد وضحت لك سبب ذلك

و الأمر يعود لك

و طلبك بخصوص 200 سطر قم بتوسعة النطاقات في المعادلات حسب رغبتك

و لكن لاحظ اني قمت بتعديل المعادلة من هذا الشكل

الى الشكل التالي :

   

=SUMPRODUCT((C3:C77<>"")/COUNTIF(C3:C77,C3:C77&""))

و ذلك حتى لا يتعارض وجود اي خلية فارغة مع نتيجة المعادلة

[رضا الخشن]

أخي الكريم

المعادلة الأخيرة تم تطبيقها بنجاح إلا أنك نسيت أخي أن تضع بها شرط الفترة الزمنية بوجود تاريخ بداية و تاريخ نهاية لحساب عدد العملاء بدون تكرار .

أنتظر ردك

السلام عليكم

[يحيى حسين]

أخي رضا

لم أنساها

و لكن هدفي كان توضيح المدى الذي يجب أن يعالج لتجنب حدوث أي خطأ

[رضا الخشن]

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

[بن علية حاجي]

السلام عليكم ورحمة الله

أخي الكريم معذرة على التأخير ولكني كنت مسافرا ولم أعد إلا اللحظة... قرأت كل الردود وعرفت ما تريده ولكن الأخ حسين قد قام بالواجب وزيادة، لقد أضاف التغيير المناسب للصيغة وقد نسيت أن أضعه في ردي السابق (لم أنتبه لذلك)... وإليك الملف المرفق به ما تريد... وشكرا جزيلا للأخ حسين على كل ما قدمه...

أخوكم "بن علية"

تجميع _عدد_بعدة شروط و بدون تكرار.rar

[بن علية حاجي]

السلام عليكم ورحمة الله

نسيت أيضا أن أقوم بتغيير الصيغة التي تحسب "عدد العملاء الذين تم التعامل معهم (دون شروط)" من الصيغة:

=SUMPRODUCT((B3:B19>=MIN(B3:B19))*(B3:B19<=MAX(B3:B19))*(1/COUNTIF(C3:C19;C3:C19)))

إلى الصيغةالتالية:

=SUMPRODUCT((B3:B77>=MIN(B3:B77))*(B3:B77<=MAX(B3:B77))*(1/COUNTIF(C3:C77;(C3:C77)&"")))

ومعذرة مرة أخرى...

أخوك بن علية

تم تعديل فبراير 16, 2010 بواسطه hben

[رضا الخشن]

الأخ الكريم بن علية

السلام عليكم و رحمة الله و بركاته

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

المعادلة التي تحسب عدد العملاء ( بدون تكرار ) خلال فترة زمنية أعطت ناتج 7.5 عميل عند إدخال تاريخ 2010/01/01 و تاريخ 2010/01/07

و طبعا يا أخي لايوجد أنصاف عملاء القيمة لابد أن تكون صحيحة

مرفق الملف به التواريخ المسببة للخطأ ، أنتظر معالجتك بإذن الله

عدة شروط_بدون تكرار_فترة زمنية.rar

[بن علية حاجي]

السلام عليكم ورحمة الله

لقد أضفت لك عمودا D:D به صيغة تعدّ عدد تكرارات قيم العمود C:C وغيرت قليلا في صيغ عدّ عدد المتعاملين... لم أجد بدّا من ذلك وهذا ما استطعت أن أعرفه... ونبقى دائما في انتظار إخواننا في المنتدى ليفيدونا أكثر... إذا قبلت بالعمل الذي أقدّمه لك بالملف المرفق فأعتقد أنه الحل لمشكلتك أخي الكريم... وفقك الله في عملك ويسّر عليك كل صعب ...

أخوك بن علية

عدة شروط_بدون تكرار_فترة زمنية1.rar

[يحيى حسين]

الأخ الكريم بن علية

السلام عليكم و رحمة الله و بركاته

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

المعادلة التي تحسب عدد العملاء ( بدون تكرار ) خلال فترة زمنية أعطت ناتج 7.5 عميل عند إدخال تاريخ 2010/01/01 و تاريخ 2010/01/07

و طبعا يا أخي لايوجد أنصاف عملاء القيمة لابد أن تكون صحيحة

مرفق الملف به التواريخ المسببة للخطأ ، أنتظر معالجتك بإذن الله

أخي رضا لقد ذكرت لك سابقاً أن عمل الدالة سيعيد كسور

و لذلك اقترحت عليك استخدام الحل الثاني بمعادلات الصفيف

و ملاحظ بسيط لحل الأخ بن علية

بخصوص إستخدام الدالة في الخلية k87

=SUMPRODUCT((B3:B77>=MIN(B3:B77))*(B3:B77<=MAX(B3:B77))*(D3:D77=1))

يمكنك استخدام المعادلة التالية

=SUMPRODUCT(1/COUNTIF(C3:C19,C3:C19))

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

 =SUMPRODUCT((C3:C77<>"")/COUNTIF(C3:C77,C3:C77&"")) 

و عذراً على الإطالة

و لكن الإطالة لكي تزيد الفائدة

[الجزيرة]

رائع اخي يحيى

والشكر لكل من اثرى الموضوع : )

[رضا الخشن]

الأخ الكريم

يحيى حسين

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

معادلتك الأولى ناجحة بنسبة 100% و قصيرة .

المعادلة الثانية " الصفيف " ناجحة بنسبة 100% ولم تعطي كسور على الإطلاق .

المعادلة الثالثة أخي عند تطبيقها أدت إلى نتائج غير دقيقة عند تغيير التصنيف و مرفق الملف بالمعادلة المرجعة للخطأ .

أرجو أن لا تشعر مني بالملل أخي الكريم بعد الإطالة في هذا الموضوع

زادك الله من علمه ، و أرخى عليك ستر رحمته و عافيته

و السلام عليكم و رحمة الله و بركاته

المعادلة الثالثة.rar

[يحيى حسين]

السلام عليكم و رحمة الله و بركاته

أخي رضا

عذراً على التأخر بالرد

و لكني إبني في المستشفى و شغلت معه

و لقد قمت بتعديل هذه المعادلة و انا أحمله

فتارة أراقب نفس الطفل و تاره أراقب المعادلة

أسأل الله العلي القدير أن يشفيه و أن يرحمه برحمته الواسعه

و أتمنى أن يكون له نصيب من دعاء الأخوة

تفضل أخي جرب هذه المعادلة بدل المعادلة الثالثة

 

=SUM((F3:S19<>"")*(B3:B19>=F90)*(B3:B19<=H90)*(E3:E19=H89)*(FREQUENCY(IF(C3:C19<>"",MATCH(C3:C19,C3:C19,0)),ROW(INDIRECT("1:"&COUNTA(C3:C19)-1)))>0)*(F2:S2=J89))

[emany]

السلام عليكم ورحمة الله ويركاته

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

انا احتاج تقريبا في ملف لى طلب مماثل لطلب أ / رضا

حاولت استخدام sumproduct ودالة FREQUENCY مستعينة بالله ثم بالـ help

لكنى تعثرت لأننى لا أفهمها جيدا فقط كنت أغير المسارات فيما يتوافق مع ملفى

فعذرا اريد شرح مبسط لاسنخدام هذه الدالة لأنها مهمة جدا لى

شكرا

[يحيى حسين]

السلام عليكم و رحمة الله و بركاته

يا ريت لو ترفق ملفك

حتى يتمكن الأخوة من مساعدتك