excel دالة countif - جدول محوري بسيط لحساب القيم الفريدة
تحديث: يمكنك القيام بذلك الآن تلقائيًا باستخدام Excel 2013. لقد قمت بإنشاء هذا كإجابة جديدة لأن إجابتي السابقة بالفعل تحل مشكلة مختلفة قليلاً.
إذا كان لديك هذا الإصدار ، فحدد بياناتك لإنشاء جدول محوري ، وعندما تقوم بإنشاء الجدول ، تأكد من أن خيار "إضافة هذه البيانات إلى نموذج البيانات" هو علامة الاختيار (انظر أدناه).
بعد ذلك ، عندما يفتح الجدول المحوري ، أنشئ صفوفًا وأعمدة وقيمًا بشكل طبيعي. ثم انقر فوق الحقل الذي تريد حساب عدد مميزة وتحرير إعدادات قيمة الحقل:
أخيرًا ، انتقل لأسفل حتى تصل إلى الخيار الأخير واختر "عدد متميز".
يجب أن يؤدي هذا إلى تحديث قيم جدول المحور الخاص بك لعرض البيانات التي تبحث عنها.
هذا يبدو وكأنه جدول بيفوت بسيط للتعلم معه. أرغب في إجراء عدد قيم فريدة لقيمة معينة أقوم بتجميعها.
على سبيل المثال ، لدي هذا:
ABC 123
ABC 123
ABC 123
DEF 456
DEF 567
DEF 456
DEF 456
ما أريده هو جدول محوري يوضح لي هذا:
ABC 1
DEF 2
إن الجدول المحوري البسيط الذي أقوم بإنشائه يعطيني هذا (عدد الصفوف):
ABC 3
DEF 4
لكنني أريد عدد القيم الفريدة بدلاً من ذلك.
ما أحاول فعله هو معرفة القيم الموجودة في العمود الأول لا تحتوي على نفس القيمة في العمود الثاني لجميع الصفوف. بمعنى آخر ، "ABC" هي "جيدة" ، "DEF" هي "سيئة"
أنا متأكد من أن هناك طريقة أسهل للقيام بذلك ولكن اعتقدت أنني سأعطي جدول محوري ...
أرغب في إضافة خيار إضافي إلى المزيج الذي لا يتطلب صيغة ولكن قد يكون مفيدًا إذا احتجت إلى حساب قيم فريدة ضمن المجموعة عبر عمودين مختلفين. باستخدام المثال الأصلي ، لم يكن لدي:
ABC 123
ABC 123
ABC 123
DEF 456
DEF 567
DEF 456
DEF 456
وتريد أن تظهر على النحو التالي:
ABC 1
DEF 2
لكن شيء أكثر مثل:
ABC 123
ABC 123
ABC 123
ABC 456
DEF 123
DEF 456
DEF 567
DEF 456
DEF 456
وأردت أن تظهر على النحو التالي:
ABC
123 3
456 1
DEF
123 1
456 3
567 1
لقد وجدت أن أفضل طريقة للحصول على بياناتي في هذا الشكل ، ثم أن أتمكن من التلاعب بها بشكل أكبر هو استخدام ما يلي:
بمجرد تحديد "تشغيل الإجمالي" ، اختر الرأس لمجموعة البيانات الثانوية (في هذه الحالة سيكون عنوان رأس أو عمود مجموعة البيانات التي تتضمن 123 و 456 و 567). سيعطيك هذا قيمة قصوى مع العدد الإجمالي للعناصر في هذه المجموعة ، ضمن مجموعة البيانات الأساسية الخاصة بك.
ثم قمت بنسخ هذه البيانات ، لصقها كقيم ، ثم وضعها في جدول محوري آخر للتلاعب بها بسهولة أكبر.
لقد كان لدي حوالي ربع مليون صف من البيانات ، لذا كان هذا أفضل بكثير من طرق الصيغة ، خاصة تلك التي تحاول المقارنة بين مجموعتين من الأعمدة / البيانات لأنها أبقت على التطبيق.
ليس من الضروري أن يتم فرز الجدول للصيغة التالية لإرجاع 1 لكل قيمة فريدة موجودة.
بافتراض أن نطاق الجدول للبيانات المعروضة في السؤال هو A1: B7 ، أدخل الصيغة التالية في الخلية C1:
=IF(COUNTIF($B$1:$B1,B1)>1,0,COUNTIF($B$1:$B1,B1))
قم بنسخ تلك الصيغة إلى كل الصفوف وسوف يحتوي الصف الأخير على:
=IF(COUNTIF($B$1:$B7,B7)>1,0,COUNTIF($B$1:$B7,B7))
يؤدي هذا إلى إرجاع 1 في المرة الأولى التي يتم العثور على سجل و 0 لكافة الأوقات بعد ذلك.
ما عليك سوى جمع العمود في جدول Pivot
كانت مقاربتى لهذه المشكلة مختلفة قليلاً عن ما أراه هنا ، لذلك سوف أشاركه.
- (قم بعمل نسخة من بياناتك أولاً)
- تسلسل الأعمدة
- أزل التكرارات على العمود المتسلسل
- الأخير - المحورية على المجموعة الناتجة
ملاحظة: أود تضمين صور لجعل هذا الأمر أكثر سهولة في الفهم لكن لا أستطيع ذلك لأن هذه هي أول مشاركة لي ؛)
يمكن لـ Excel 2013 إجراء عدد مميز في المحاور. إذا لم يكن هناك أي وصول إلى 2013 ، وهو عبارة عن كمية صغيرة من البيانات ، فأنا أصنع نسختين من البيانات الخام ، وفي النسخة b ، حدد كلا العمودين وأزل التكرارات. ثم اجعل المحور وأعد العمود ب.
الخطوة 1. إضافة عمود
الخطوة 2. استخدم الصيغة = IF(COUNTIF(C2:$C$2410,C2)>1,0,1)
في السجل الأول
الخطوة 3. اسحبها إلى جميع السجلات
الخطوة 4. قم بتصفية "1" في العمود بصيغة
إذا كان لديك البيانات فرزها .. أقترح استخدام الصيغة التالية
=IF(OR(A2<>A3,B2<>B3),1,0)
هذا أسرع لأنه يستخدم خلايا أقل لحساب.
لقد وجدت طريقة أسهل للقيام بذلك. بالإشارة إلى مثال Siddarth Rout ، إذا أردت حساب قيم فريدة في العمود A:
- إضافة عمود جديد C وملء C2 مع الصيغة "= 1 / COUNTIF ($ A: $ A، A2)"
- اسحب الصيغة لأسفل إلى بقية العمود
- pivot with column A as as label، and Sum {column C) in values to get the number of unique values in column A