عد عدد الصفوف التي تحتوي على قيم محددة في Excel
قد يكون من السهل علينا حساب عدد الخلايا ذات القيمة المحددة في ورقة عمل Excel. ومع ذلك ، قد يكون الحصول على عدد الصفوف التي تحتوي على قيم محددة أمرًا معقدًا إلى حد ما. في هذه الحالة ، قد تفيدك صيغة أكثر تعقيدًا تستند إلى وظائف SUM و MMULT و TRANSPOSE و COLUMN. سيتحدث هذا البرنامج التعليمي عن كيفية إنشاء هذه الصيغة للتعامل مع هذه الوظيفة في Excel.
عد عدد الصفوف التي تحتوي على قيم محددة
على سبيل المثال ، لديك مجموعة من القيم في ورقة عمل ، والآن تحتاج إلى حساب عدد الصفوف بقيمة معينة "300" كما هو موضح أدناه لقطة الشاشة:
للحصول على عدد الصفوف التي تحتوي على قيم محددة ، فإن البنية العامة هي:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: نطاق الخلايا المراد التحقق مما إذا كانت تحتوي على قيمة محددة ؛
- X: القيمة المحددة التي تستخدمها لحساب الصفوف.
1. الرجاء إدخال الصيغة أدناه أو نسخها في خلية فارغة حيث تريد وضع النتيجة:
2. ثم اضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الصحيحة ، انظر لقطة الشاشة:
شرح الصيغة:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- - 2 دولار أسترالي: 12 دولار كندي = 300: يتحقق هذا التعبير مما إذا كانت القيمة "300" موجودة في النطاق A2: C12 ، وسيُنشئ نتيجة مصفوفة TRUE و False. يتم استخدام علامة السالب المزدوجة لتحويل القيم الحقيقية إلى 1 ثانية ، والخطأ إلى 0 ثانية. لذلك ، ستحصل على النتيجة على النحو التالي: {0,0,0،1,0,0،0,0,0؛ 0,1,1،0,0,0،0,1,0؛ 0,0,0،1,0,0،0,0,1؛ 0,0,0،1,1,1،11؛ 3،1،XNUMX؛ XNUMX،XNUMX،XNUMX؛ XNUMX،XNUMX ، XNUMX؛ XNUMX،XNUMX،XNUMX؛ XNUMX،XNUMX،XNUMX؛ XNUMX،XNUMX،XNUMX؛ XNUMX،XNUMX،XNUMX}. الصفيف المكون من XNUMX صفاً و XNUMX أعمدة سيعمل كصفيف XNUMX في دالة MMULT.
- ترانسبوز (عمود (2 دولار أسترالي: 12 دولار كندي)): تُستخدم الدالة COLUMN هنا للحصول على رقم عمود النطاق A2: C12 ، فهي تُرجع مصفوفة من 3 أعمدة مثل هذا: {1,2,3،3،1}. وبعد ذلك تقوم الدالة TRANSPOSE بتبديل هذه المصفوفة إلى صفيف مكون من 2 صفوف {3؛ 2؛ XNUMX} ، تعمل كمصفوفة XNUMX ضمن دالة MMULT.
- MMULT (- ($ A $ 2: $ C $ 12 = "Joanna") ، TRANSPOSE (COLUMN ($ A $ 2: $ C $ 12))): تعرض دالة MMULT منتج المصفوفة للمصفوفتين المذكورتين أعلاه ، وستحصل على النتيجة على النحو التالي: {0 ؛ 1 ؛ 0 ؛ 5 ؛ 0 ؛ 2 ؛ 0 ؛ 1 ؛ 3 ؛ 0 ؛ 6}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): أولاً ، تحقق من القيم في المصفوفة الأكبر من 0: إذا كانت القيمة أكبر من 0 ، فسيتم عرض TRUE ؛ إذا كان أقل من 0 ، يتم عرض FALSE. ثم تجبر الإشارة السالبة المزدوجة القيم الحقيقية والخطأ على أن تكون 1 ثانية وأصفار ، لذلك ستحصل على هذا: SUM ({0؛ 0؛ 1؛ 0؛ 1؛ 0؛ 1؛ 0؛ 1؛ 1؛ 0}). أخيرًا ، تجمع الدالة SUM القيم الموجودة في المصفوفة لإرجاع النتيجة: 1.
نصيحة:
إذا كنت بحاجة إلى حساب عدد الصفوف التي تحتوي على نص معين في ورقة عمل ، فالرجاء تطبيق الصيغة أدناه ، وتذكر الضغط على الزر كترل + شيفت + إنتر مفاتيح معًا للحصول على العدد الإجمالي:
الوظيفة النسبية المستخدمة:
- مجموع:
- ترجع الدالة Excel SUM مجموع القيم المتوفرة.
- مولت:
- ترجع الدالة MMULT في Excel منتج المصفوفة لمصفوفتين.
- تبديل موضع:
- ستعيد الدالة TRANSPOSE مصفوفة في اتجاه جديد بناءً على نطاق معين من الخلايا.
- عمود:
- تُرجع الدالة COLUMN رقم العمود الذي تظهر فيه الصيغة أو تُرجع رقم العمود للمرجع المحدد.
المزيد من المقالات:
- عد الصفوف إذا استوفت المعايير الداخلية
- لنفترض أن لديك تقرير مبيعات المنتج لهذا العام والعام الماضي ، والآن ، قد تحتاج إلى حساب المنتجات التي تكون فيها المبيعات في هذا العام أكبر من العام الماضي ، أو المبيعات في هذا العام أقل من العام الماضي على النحو التالي تظهر لقطة الشاشة. عادةً ، يمكنك إضافة عمود مساعد لحساب فرق البيع بين العامين ، ثم استخدام COUNTIF للحصول على النتيجة. لكن في هذه المقالة ، سأقدم وظيفة SUMPRODUCT للحصول على النتيجة مباشرة دون أي عمود مساعد.
- عد الصفوف إذا استوفت معايير متعددة
- قم بحساب عدد الصفوف في نطاق ما بناءً على معايير متعددة ، يعتمد بعضها على الاختبارات المنطقية التي تعمل على مستوى الصفوف ، قد تفيدك الدالة SUMPRODUCT في Excel.
- عدد الخلايا يساوي إحدى القيم العديدة
- لنفترض أن لدي قائمة بالمنتجات في العمود A ، الآن ، أريد الحصول على العدد الإجمالي لمنتجات محددة من Apple و Grape و Lemon المدرجة في النطاق C4: C6 من العمود A كما هو موضح أدناه. عادةً ، في Excel ، لن تعمل وظائف COUNTIF و COUNTIFS البسيطة في هذا السيناريو. في هذه المقالة ، سأتحدث عن كيفية حل هذه الوظيفة بسرعة وسهولة مع الجمع بين وظائف SUMPRODUCT و COUNTIF.
أفضل أدوات إنتاجية المكتب
Kutools for Excel - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
Office Tab - تمكين القراءة المبوبة والتحرير في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
