كيفية حساب القيم الفريدة بناءً على معايير متعددة في Excel؟
في العديد من السيناريوهات العملية، غالبًا ما يكون من الضروري ليس فقط حساب القيم، ولكن تحديد عدد العناصر الفريدة التي تلبي شروطًا معينة ضمن بياناتك. على سبيل المثال، قد ترغب في معرفة عدد المنتجات المختلفة التي باعها شخص مبيعات معين، أو عدد الطلبات الفريدة التي تم تقديمها خلال فترة زمنية معينة. التعامل مع مثل هذه المهام بكفاءة في Excel يتطلب التعرف على الصيغ المناسبة، والميزات المتقدمة مثل جداول البيانات المحورية، أو حتى حلول VBA المخصصة. في هذه المقالة، سنستعرض عدة طرق عملية لحساب القيم الفريدة بناءً على معيار واحد أو أكثر، مع تعليمات خطوة بخطوة ونصائح.
احسب القيم الفريدة بناءً على معيار واحد
احسب القيم الفريدة بناءً على تاريخين محددين
احسب القيم الفريدة بناءً على معيارين
احسب القيم الفريدة بناءً على ثلاثة معايير
احسب القيم الفريدة باستخدام جدول محوري (عدد مميز، Excel2013+)
احسب القيم الفريدة باستخدام كود VBA (للحالات المعقدة/الآلية)
احسب القيم الفريدة بناءً على معيار واحد
لنفكر في حالة شائعة: تريد حساب عدد المنتجات المختلفة التي باعها توم. هذه الطريقة مناسبة عندما يكون لديك مجموعة بيانات بسيطة وتهدف إلى تقييم التفرد بناءً على شرط واحد، مثل سجل مبيعات شخص واحد. إنها مباشرة ولكن تتطلب استخدام دقيق لصيغ المصفوفات.
لهذا السيناريو، أدخل الصيغة التالية في خلية فارغة (على سبيل المثال، الخلية G2):
=SUM(IF("توم"=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, "توم", $A$2:$A$20, $A$2:$A$20)),0))
بعد كتابة الصيغة، اضغط على Ctrl + Shift + Enter (وليس فقط Enter) لتأكيد الصيغة كصيغة مصفوفة. ستظهر الأقواس حول الصيغة في شريط الصيغة، وسترى النتيجة فورًا كما هو موضح أدناه:
ملاحظة:
- “توم” هو الشرط الذي تريد استخدامه لتصفية النتائج. يمكنك استبدال "توم" بإشارة إلى خلية أخرى (مثل $F$2) إذا كنت ترغب في المزيد من المرونة.
- $C$2:$C$20 يحتوي على أسماء موظفي المبيعات الذين سيتم تقييمهم.
- $A$2:$A$20 هو العمود الذي يحتوي على المنتجات التي تريد حسابها بشكل فريد.
- إذا تغير نطاق بياناتك، تذكر ضبط الإشارات وفقًا لذلك.
نصيحة: إذا كنت تستخدم Excel 365 أو Excel 2019 وما بعدها، يمكنك تجربة استخدام الدوال UNIQUE و FILTER للحصول على صيغ أسهل.
إذا ظهرت لك أي أخطاء #DIV/0!، قم بمراجعة المعايير وتأكد أن نطاقاتك متساوية الطول.
احسب القيم الفريدة بناءً على تاريخين محددين
عندما تحتاج إلى إيجاد عدد العناصر الفريدة داخل نطاق زمني محدد، على سبيل المثال، جميع المنتجات الفريدة التي تم بيعها بين 2016/9/1 و2016/9/30، يمكنك تطبيق هذا الأسلوب. هذا مفيد بشكل خاص عند تحليل اتجاهات البيانات بين فترات معينة، مثل الشهري أو الفصلي أو نطاقات تواريخ مخصصة. ومع ذلك، كن حذرًا بشأن تنسيق التاريخ؛ يجب أن يتطابق مع قيم التاريخ في ورقة العمل الخاصة بك.
ضع الصيغة التالية في خلية فارغة حيث تريد عرض النتيجة:
=SUM(IF($D$2:$D$20<=DATE(2016,9,30)*($D$2:$D$20>=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30),$D$2:$D$20, ">="&DATE(2016,9,1))),0)
اضغط على Ctrl + Shift + Enter بعد إدخال الصيغة لتنفيذها كصيغة مصفوفة. توضح اللقطة أدناه النتيجة:
ملاحظة:
- 2016,9,1 و2016,9,30 هما معايير بداية ونهاية التاريخ. يمكنك تعديل هذه القيم حسب الحاجة، أو حتى استخدام إشارات الخلايا لفلاتر تاريخية ديناميكية.
- $D$2:$D$20 يحتوي على إدخالات التاريخ التي سيتم التحقق منها.
- $A$2:$A$20 هو مرة أخرى العمود الذي يحتوي على العناصر أو المنتجات التي تريد حسابها بشكل فريد.
- تأكد من تخزين تواريخك كتواريخ Excel صالحة، وليس كنصوص. إذا لم تظهر نتيجتك كما هو متوقع، تأكد من تنسيق التواريخ والنطاقات.
نصيحة: استخدم DATE(year, month, day) لتجنب المشاكل مع تنسيقات التاريخ الإقليمية. عند استخدام النطاقات الديناميكية، تفكّر في استخدام النطاقات المسماة للوضوح.
احسب القيم الفريدة بناءً على معيارين
لنفترض أنك تريد تحليل المنتجات التي باعها توم فقط خلال سبتمبر، بدمج الاسم ونطاق تاريخ في حسابك الفريد. هذا السيناريو شائع في مراجعات الأداء القائمة على الفترات أو التحليلات المجزأة. مع توسع معاييرك، تصبح الصيغة أكثر تعقيدًا، ويصبح الانتباه لدقة البيانات أكثر أهمية.
أدخل الصيغة أدناه في أي خلية فارغة، مثل H2:
=SUM(IF(("توم"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016,9,30)*($D$2:$D$20>=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, "توم", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30),$D$2:$D$20, ">="&DATE(2016,9,1))),0)
بعد كتابة الصيغة، أكد عليها باستخدام Ctrl + Shift + Enter. يجب أن ترى الحساب الفريد فورًا؛ تحقق من الرسم التوضيحي التالي:
ملاحظات:
- “توم” هو معيار الاسم، بينما “2016,9,1” و“2016,9,30” هما حدود نطاق التاريخ. قم بالتعديل حسب الحاجة أو اجعلها ديناميكية باستخدام إشارات الخلايا.
- $C$2:$C$20 هو عمود الموظفين (أو معيار آخر أولي)؛ $D$2:$D$20 هو عمود التاريخ؛ $A$2:$A$20 يحتوي على العناصر الفريدة المراد حسابها.
- يجب أن تكون جميع النطاقات متساوية الطول لمنع الأخطاء.
إذا كنت ترغب في استخدام معايير “أو”، مثل حساب المنتجات الفريدة التي باعها توم أو في المنطقة الجنوبية، يمكنك استخدام الصيغة التالية. هذا يمكّن من شروط بحث أوسع، على الرغم من أن النتائج قد تتداخل إذا كانت البيانات تطابق كلتا المعيارين:
=SUM(--(FREQUENCY(IF(("توم"=$C$2:$C$20)+("الجنوب"=$B$2:$B$20), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20))>0))
لا تنسَ الضغط على Ctrl + Shift + Enter. سترى النتائج كما هو موضح أدناه:
نصيحة: عند تطبيق معايير OR، كن على علم بإمكانية العد المزدوج إذا كان نفس السجل يحقق كلتا المعيارين. بالنسبة للمجموعات الكبيرة من البيانات، قد تتأثر الأداء.
احسب القيم الفريدة بناءً على ثلاثة معايير
أحيانًا قد يتطلب تحليلك ثلاثة معايير أو أكثر، مثل تحديد المنتجات الفريدة التي باعها توم في سبتمبر فقط في المنطقة الشمالية. هذا شائع في تحليل البيانات متعددة الأبعاد للتقارير أو الرؤى التجارية المستهدفة. إدارة المراجع بعناية أمر أساسي في التعامل مع مثل هذه المنطق المركب.
ضع هذه الصيغة المصفوفة في خلية فارغة (مثل I2):
=SUM(IF(("توم"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016,9,30))*($D$2:$D$20>=DATE(2016,9,1))*("الشمال"=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, "توم", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016,9,30), $D$2:$D$20, ">="&DATE(2016,9,1), $B$2:$B$20, "الشمال")),0)
اضغط على Ctrl + Shift + Enter لإنهاء. إليك نتيجة عينة للرجوع إليها:
بالنسبة للشروط المتقدمة، قم بمراجعة دقيقة للتأكد من أن جميع النطاقات متسقة وأن أنواع البيانات (مثل التاريخ والنص) صحيحة. يمكن أن تسبب عدم التطابقات أخطاء أو نتائج مضللة.
نصائح:
- إذا واجهت مشكلات في الأداء على المجموعات الكبيرة من البيانات، فكر في تقسيم الصيغة أو استخدام حلول جدول Pivot Table الخاص بـ Excel.
- تسمية النطاقات أو الإشارة إلى الخلايا لجميع المعايير تحسن القراءة وتقلل من أخطاء الصيغ.
- بالنسبة للاستخدام المتكرر، فكر في تسجيل هذه الصيغ في الإشارات المحددة للخلايا أو الوظائف المخصصة.
احسب القيم الفريدة باستخدام جدول محوري (Distinct Count، Excel 2013+)
للمستخدمين Excel 2013 أو الإصدارات الأحدث، تقدم الجداول المحورية بديلاً تفاعليًا وغير صيغي لحساب القيم الفريدة عبر معيار واحد أو أكثر. ميزة Distinct Count تساعدك على تلخيص وتصفية مجموعات كبيرة من البيانات بكفاءة، مما يجعل هذه الطريقة مناسبة بشكل خاص للبيئات الديناميكية القائمة على التقارير. ومع ذلك، لاحظ أن الإصدارات السابقة من Excel لا تدعم وظيفة Distinct Count داخل الجداول المحورية.
كيفية استخدام هذه الطريقة:
- حدد مجموعة بياناتك واذهب إلى إدراج > جدول محوري.
- في مربع الحوار إنشاء جدول محوري، اختر مكان وضع الجدول المحوري، وتحقق من مربع "إضافة هذه البيانات إلى نموذج البيانات"، ثم انقر فوق موافق.
- اسحب الحقل الذي تريد حسابه بشكل فريد (مثل المنتج) إلى منطقة القيم. بشكل افتراضي، سيتم عرضه كـ "عدد...".
- انقر على الحقل في منطقة القيم وحدد إعدادات حقل القيمة.
- في مربع الحوار المنبثقة، قم بالتمرير لأسفل وحدد Distinct Count (هذا الخيار متاح فقط في Excel 2013 أو الإصدارات الأحدث، ويظهر عندما يتم إنشاء الجدول المحوري مع خيار "إضافة هذه البيانات إلى نموذج البيانات" ممكّنًا).
- أضف حقول المعايير الخاصة بك (مثل موظف المبيعات، المنطقة، التاريخ) إلى منطقة الفلاتر أو الصفوف/الأعمدة لتطبيق شرط واحد أو أكثر.
- سيعرض الجدول المحوري الآن عدد القيم الفريدة المُرشحة بواسطة معاييرك المختارة.
المزايا: مرئية للغاية، سهلة لضبط الفلاتر دون تعديل الصيغ، ومناسبة للتقارير التفاعلية.
القيود: غير متوفرة في Excel 2010 أو الإصدارات الأقدم؛ إضافة بيانات جديدة يتطلب تحديث الجدول المحوري يدويًا.
نصيحة عملية: تأكد دائمًا من أن بيانات المصدر لا تحتوي على تكرارات داخل نفس السجل إذا لم يكن ذلك مقصودًا. إذا وجدت خيار Distinct Count مفقودًا، أعد إنشاء الجدول المحوري وتحقق من خيار “إضافة هذه البيانات إلى نموذج البيانات”.
احسب القيم الفريدة باستخدام كود VBA (للحالات المعقدة/الآلية)
أحيانًا، قد تحتاج إلى حساب القيم الفريدة بناءً على معايير مختلفة تلقائيًا، خاصة عند التعامل مع مجموعات بيانات كبيرة جدًا أو عند تكرار التحليل بشكل متكرر. تعتبر وحدة VBA Macro مثالية لمثل هذه الحالات، حيث يمكنها معالجة منطق مختلف—بما في ذلك التصفية متعددة المعايير—بدون تدخل يدوي بعد الإعداد. ومع ذلك، فإن VBA أكثر تقدمًا من ميزات Excel العادية، لذا فهي الأنسب للمستخدمين المريحين مع البرامج النصية أو أولئك الذين لديهم احتياجات تحليلية مستمرة.
خطوات التشغيل:
- اضغط على Alt + F11 لفتح محرر VBA. في المحرر، حدد إدراج > وحدة لإنشاء وحدة جديدة.
- انسخ والصق الكود VBA التالي في الوحدة:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- أغلق محرر VBA وعد إلى ورقة عملك. اضغط على Alt + F8، حدد CountUniqueWithCriteria، وقم بتشغيل البرنامج النصي.
- اتبع المطالبات لإدخال النطاقات والمعايير وفقًا لبياناتك. ستظهر النتيجة في الخلية التي تختارها وكذلك في صندوق رسالة.
شرح المعلمات والملاحظات:
- هذا البرنامج النصي معد حاليًا لمعيار واحد. لتوسيعه لمعايير متعددة، قم بتعديل المنطق
If ... Thenداخل الحلقة. - احرص دائمًا على حفظ مصنفك قبل تشغيل البرامج النصية حيث لا يمكن التراجع عن التغييرات.
- قم بتمكين البرامج النصية في إعدادات Excel إذا واجهت أخطاء في التنفيذ.
- تعمل هذه الطريقة بشكل جيد مع البيانات الكبيرة أو التي يتم تحديثها بشكل متكرر حيث تكون الصيغ اليدوية مرهقة.
الفوائد: قابلة للتخصيص بشكل كبير وأوتوماتيكية، تعالج مجموعات البيانات الكبيرة والمتغيرة بكفاءة. مناسبة لاحتياجات التدفق العمل المتقدمة أو المتكررة.
العيوب: تتطلب أذونات البرامج النصية، وقد يحتاج المبتدئون إلى وقت للتعرف على عمليات VBA.
عند العمل مع حسابات القيم الفريدة بناءً على المعايير، تأكد دائمًا من إشارات النطاق وتأكد من أن جميع أعمدة المعايير متساوية الحجم. تعد النطاقات غير المتطابقة مصدرًا شائعًا للأخطاء أو النتائج غير الصحيحة. إذا أعادت الصيغ نتائج غير متوقعة، تحقق من وجود مشاكل في التنسيق المخفي أو الخلايا الفارغة. بالنسبة للسيناريوهات الحرجة الأداء، توفر الجداول المحورية وVBA بدائل قوية لصيغ المصفوفات. اختر الحل الأنسب لمستوى راحتك وتعقيد مجموعة البيانات الخاصة بك. تذكر، Kutools for Excel توفر أدوات واختصارات إضافية يمكن أن تسرع العديد من هذه المهام لتحقيق كفاءة أكبر في المصنفات المعقدة.
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel باستخدام Kutools لـ Excel، واختبر كفاءة غير مسبوقة. Kutools لـ Excel يوفر أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة الأكثر أهمية بالنسبة لك...
Office Tab يجلب واجهة التبويب إلى Office ويجعل عملك أسهل بكثير
- تفعيل تحرير وقراءة عبر التبويبات في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
- افتح وأنشئ عدة مستندات في تبويبات جديدة في نفس النافذة، بدلاً من نوافذ مستقلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات اليومية من الفأرة!
جميع إضافات Kutools. مثبت واحد
حزمة Kutools for Office تجمع بين إضافات Excel وWord وOutlook وPowerPoint إضافة إلى Office Tab Pro، وهي مثالية للفرق التي تعمل عبر تطبيقات Office.
- حزمة الكل في واحد — إضافات Excel وWord وOutlook وPowerPoint + Office Tab Pro
- مثبّت واحد، ترخيص واحد — إعداد في دقائق (جاهز لـ MSI)
- الأداء الأفضل معًا — إنتاجية مُبسطة عبر تطبيقات Office
- تجربة كاملة لمدة30 يومًا — بدون تسجيل، بدون بطاقة ائتمان
- قيمة رائعة — وفر مقارنة بشراء الإضافات بشكل منفرد