[Excel] جدول محوري بسيط لحساب القيم الفريدة


Answers

تحديث: يمكنك القيام بذلك الآن تلقائيًا باستخدام Excel 2013. لقد قمت بإنشاء هذا كإجابة جديدة لأن إجابتي السابقة بالفعل تحل مشكلة مختلفة قليلاً.

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

بعد ذلك ، عندما يفتح الجدول المحوري ، أنشئ صفوفًا وأعمدة وقيمًا بشكل طبيعي. ثم انقر فوق الحقل الذي تريد حساب عدد مميزة وتحرير إعدادات قيمة الحقل:

أخيرًا ، انتقل لأسفل حتى تصل إلى الخيار الأخير واختر "عدد متميز".

يجب أن يؤدي هذا إلى تحديث قيم جدول المحور الخاص بك لعرض البيانات التي تبحث عنها.

Question

هذا يبدو وكأنه جدول بيفوت بسيط للتعلم معه. أرغب في إجراء عدد قيم فريدة لقيمة معينة أقوم بتجميعها.

على سبيل المثال ، لدي هذا:

ABC   123
ABC   123
ABC   123
DEF   456
DEF   567
DEF   456
DEF   456

ما أريده هو جدول محوري يوضح لي هذا:

ABC   1
DEF   2

إن الجدول المحوري البسيط الذي أقوم بإنشائه يعطيني هذا (عدد الصفوف):

ABC   3
DEF   4  

لكنني أريد عدد القيم الفريدة بدلاً من ذلك.

ما أحاول فعله هو معرفة القيم الموجودة في العمود الأول لا تحتوي على نفس القيمة في العمود الثاني لجميع الصفوف. بمعنى آخر ، "ABC" هي "جيدة" ، "DEF" هي "سيئة"

أنا متأكد من أن هناك طريقة أسهل للقيام بذلك ولكن اعتقدت أنني سأعطي جدول محوري ...




كانت مقاربتى لهذه المشكلة مختلفة قليلاً عن ما أراه هنا ، لذلك سوف أشاركه.

  1. (قم بعمل نسخة من بياناتك أولاً)
  2. تسلسل الأعمدة
  3. أزل التكرارات على العمود المتسلسل
  4. الأخير - المحورية على المجموعة الناتجة

ملاحظة: أود تضمين صور لجعل هذا الأمر أكثر سهولة في الفهم لكن لا أستطيع ذلك لأن هذه هي أول مشاركة لي ؛)




إذا كان لديك البيانات فرزها .. أقترح استخدام الصيغة التالية

=IF(OR(A2<>A3,B2<>B3),1,0)

هذا أسرع لأنه يستخدم خلايا أقل لحساب.




ليس من الضروري أن يتم فرز الجدول للصيغة التالية لإرجاع 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




أرغب في إضافة خيار إضافي إلى المزيج الذي لا يتطلب صيغة ولكن قد يكون مفيدًا إذا احتجت إلى حساب قيم فريدة ضمن المجموعة عبر عمودين مختلفين. باستخدام المثال الأصلي ، لم يكن لدي:

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. إضافة عمود

الخطوة 2. استخدم الصيغة = IF(COUNTIF(C2:$C$2410,C2)>1,0,1) في السجل الأول

الخطوة 3. اسحبها إلى جميع السجلات

الخطوة 4. قم بتصفية "1" في العمود بصيغة




يمكن لـ Excel 2013 إجراء عدد مميز في المحاور. إذا لم يكن هناك أي وصول إلى 2013 ، وهو عبارة عن كمية صغيرة من البيانات ، فأنا أصنع نسختين من البيانات الخام ، وفي النسخة b ، حدد كلا العمودين وأزل التكرارات. ثم اجعل المحور وأعد العمود ب.




لقد وجدت طريقة أسهل للقيام بذلك. بالإشارة إلى مثال 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