Home-theater-designers
غالبًا ما تحتوي مجموعات البيانات في Excel على نفس القيمة عدة مرات في عمود. في بعض الأحيان ، قد يكون من المفيد معرفة عدد القيم الفريدة الموجودة في العمود. على سبيل المثال ، إذا كنت تدير متجرًا ولديك جدول بيانات لجميع معاملاتك ، فقد ترغب في تحديد عدد العملاء الفريدين لديك ، بدلاً من حساب كل معاملة واحدة.
من الممكن القيام بذلك عن طريق حساب القيم الفريدة في Excel باستخدام الطرق التي سنتحدث عنها أدناه.
قم بإزالة البيانات المكررة من عمود
هناك طريقة سريعة وقذرة لحساب القيم الفريدة في Excel وهي إزالة التكرارات ومعرفة عدد الإدخالات المتبقية. يعد هذا خيارًا جيدًا إذا كنت بحاجة إلى إجابة سريعة ولا تحتاج إلى تتبع النتيجة.
انسخ البيانات في ورقة جديدة (حتى لا تحذف أي بيانات تحتاجها عن طريق الخطأ). حدد القيم أو العمود الذي تريد إزالة القيم المكررة منه. في ال أدوات البيانات قسم من البيانات حدد علامة التبويب إزالة التكرارات . يؤدي ذلك إلى إزالة كافة البيانات المكررة ويترك القيم الفريدة فقط.
تعمل نفس العملية إذا تم تقسيم المعلومات بين عمودين. الفرق هو أنك تحتاج إلى تحديد كلا العمودين. في مثالنا ، لدينا عمود للاسم الأول وعمود ثان لاسم العائلة.
إذا كنت تريد تتبع عدد القيم الفريدة ، فمن الأفضل كتابة صيغة بدلاً من ذلك. سنوضح لك كيفية القيام بذلك أدناه.
ذات صلة: كيفية التصفية في Excel لعرض البيانات التي تريدها
عد القيم الفريدة باستخدام صيغة Excel
لحساب القيم الفريدة فقط ، يتعين علينا دمج العديد من وظائف Excel. أولاً ، نحتاج إلى التحقق مما إذا كانت كل قيمة مكررة ، ثم نحتاج إلى حساب الإدخالات المتبقية. نحتاج أيضًا إلى استخدام دالة مصفوفة.
إذا كنت تبحث فقط عن الإجابة ، فاستخدم هذه الصيغة ، واستبدل كل مثيل من A2: A13 بالخلايا التي تريد استخدامها:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}كيف وصلنا إلى هناك معقد بعض الشيء. لذا ، إذا كنت تريد أن تفهم سبب نجاح هذه الصيغة ، فسنقسمها قطعة واحدة في المرة أدناه.
شرح دالة المصفوفة
لنبدأ أولاً بشرح ماهية المصفوفة. المصفوفة هي متغير واحد يحمل قيمًا متعددة. يشبه الإشارة إلى مجموعة من خلايا Excel في وقت واحد بدلاً من الإشارة إلى كل خلية على حدة.
هذا تمييز غريب من وجهة نظرنا. إذا أخبرنا صيغة ما بالنظر إلى الخلايا A2: A13 بشكل طبيعي أو كصفيف ، فإن البيانات ستبدو كما هي بالنسبة لنا. يكمن الاختلاف في كيفية تعامل Excel مع البيانات خلف الكواليس. إنه اختلاف دقيق لدرجة أن أحدث إصدارات Excel لم تعد تميز بينها بعد الآن ، على الرغم من أن الإصدارات القديمة تفعل ذلك.
لأغراضنا ، من المهم معرفة كيفية استخدام المصفوفات. إذا كان لديك أحدث إصدار من Excel ، فإنه يقوم تلقائيًا بتخزين البيانات كمصفوفة عندما يكون القيام بذلك أكثر كفاءة. إذا كان لديك إصدار أقدم ، عند الانتهاء من كتابة الصيغة ، اضغط على Ctrl + Shift + Enter . بمجرد القيام بذلك ، سيتم إحاطة الصيغة بأقواس متعرجة لإظهار أنها في وضع الصفيف.
التعريف بوظيفة التردد
تخبرنا الدالة FREQUENCY بعدد المرات التي يظهر فيها رقم في القائمة. هذا أمر رائع إذا كنت تعمل مع الأرقام ، لكن قائمتنا نصية. لاستخدام هذه الوظيفة ، علينا إيجاد طريقة لتحويل نصنا إلى أرقام أولاً.
إذا كنت تحاول حساب القيم الفريدة في قائمة الأرقام ، فيمكنك تخطي الخطوة التالية.
استخدام وظيفة المباراة
ترجع الدالة MATCH موضع التكرار الأول لقيمة ما. يمكننا استخدام هذا لتحويل قائمة الأسماء لدينا إلى قيم رقمية. يحتاج إلى معرفة ثلاث أجزاء من المعلومات:
- ما القيمة التي تبحث عنها؟
- ما مجموعة البيانات التي تقوم بفحصها؟
- هل تبحث عن قيم أعلى أم أدنى أم مساوية للقيمة المستهدفة؟
في مثالنا ، نريد البحث عن كل اسم لعملائنا في جدول بيانات Exel الخاص بنا لمعرفة ما إذا كان اسمه بالضبط يظهر مرة أخرى في مكان آخر.
كيفية فصل وحدة تحكم ps4 عن ps4
في المثال أعلاه ، نبحث في قائمتنا (A2: A13) عن Tiah Gallagher (A2) ونريد مطابقة تامة. يحدد 0 في الحقل الأخير أنه يجب أن يكون مطابقًا تمامًا. تخبرنا نتيجتنا بمكان ظهور الاسم أولاً في القائمة. في هذه الحالة ، كان الاسم الأول ، وبالتالي تكون النتيجة 1.
المشكلة في هذا هي أننا مهتمون بجميع عملائنا ، وليس فقط Tiah. ولكن ، إذا حاولنا البحث عن A2: A13 بدلاً من A2 فقط ، فسنحصل على خطأ. هذا هو المكان الذي تكون فيه وظائف الصفيف سهلة الاستخدام. يمكن أن تأخذ المعلمة الأولى متغيرًا واحدًا فقط وإلا فإنها تُرجع خطأً. ولكن ، يتم التعامل مع المصفوفات كما لو كانت متغيرًا واحدًا.
الآن تقوم وظيفتنا بإخبار Excel بالتحقق من وجود تطابقات لمجموعة كاملة لدينا. لكن انتظر ، نتيجتنا لم تتغير! ما زال يقول 1. ما الذي يحدث هنا؟
وظيفتنا هي إرجاع مصفوفة. يمر عبر كل عنصر في صفيفنا ويتحقق من وجود تطابقات. يتم حفظ نتائج جميع الأسماء في مصفوفة ، ويتم إرجاعها كنتيجة. نظرًا لأن الخلية تعرض متغيرًا واحدًا فقط في كل مرة ، فإنها تعرض القيمة الأولى في المصفوفة.
يمكنك التحقق من هذا بنفسك. إذا غيرت النطاق الأول إلى A3: A13 ، ستتغير النتيجة إلى 2. وذلك لأن اسم إيليا هو الثاني في القائمة ويتم حفظ هذه القيمة أولاً في المصفوفة الآن. إذا قمت بتغيير النطاق الأول إلى A7: A13 ، فستحصل على 1 مرة أخرى لأن اسم Tiah يظهر أولاً في الموضع الأول من مجموعة البيانات التي نتحقق منها.
متعلق ب: صيغ Excel التي ستساعدك على حل مشاكل الحياة الحقيقية
استخدام وظيفة التردد
الآن بعد أن قمنا بتغيير الأسماء إلى قيم رقمية ، يمكننا استخدام الدالة FREQUENCY. على غرار MATCH ، فهي تتطلب هدفًا للبحث عنه ومجموعة بيانات للتحقق منها. على غرار MATCH أيضًا ، لا نريد البحث عن قيمة واحدة فقط ، نريد أن تتحقق الوظيفة من كل عنصر في قائمتنا.
الهدف الذي نريد أن تتحقق منه الدالة FREQUENCY هو كل عنصر في المصفوفة أعادته دالة MATCH. ونريد التحقق من مجموعة البيانات التي تم إرجاعها بواسطة وظيفة MATCH. وبالتالي ، نرسل وظيفة MATCH التي صممناها أعلاه لكلا المعلمتين.
إذا كنت تبحث عن أرقام فريدة وتخطيت الخطوة السابقة ، فيمكنك إرسال نطاق الأرقام كمعلمات. للبحث في جميع الأرقام الموجودة في قائمتك ، ستحتاج إلى استخدام دالة مصفوفة أيضًا ، لذا تذكر أن تضغط Ctrl + Shift + Enter بعد إدخال الصيغة إذا كنت تستخدم إصدارًا أقدم من Excel.
الآن النتيجة هي 2. مرة أخرى ، تقوم الدالة بإرجاع مصفوفة. تقوم بإرجاع مصفوفة لعدد المرات التي ظهرت فيها كل قيمة فريدة. تعرض الخلية القيمة الأولى في المصفوفة. في هذه الحالة ، يظهر اسم Tiah مرتين ، لذا فإن التردد الذي يتم إرجاعه هو 2.
استخدام وظيفة IF
الآن المصفوفة لدينا لديها نفس عدد القيم كما لدينا قيم فريدة. لكننا لم ننتهي تمامًا. نحن بحاجة إلى طريقة لإضافة هذا. إذا قمنا بتحويل جميع القيم في المصفوفة إلى 1 ، وجمعناها ، فسنعرف أخيرًا عدد القيم الفريدة التي لدينا.
يمكننا إنشاء دالة IF التي تغير جميع القيم فوق الصفر إلى 1. ثم ستساوي جميع القيم 1.
للقيام بذلك ، نريد أن تتحقق وظيفة IF الخاصة بنا مما إذا كانت القيم في مجموعة FREQUENCY الخاصة بنا أكبر من الصفر. إذا كان هذا صحيحًا ، يجب أن يُرجع القيمة 1. ستلاحظ أن القيمة الأولى في المصفوفة تُرجع كواحد.
استخدام وظيفة SUM
نحن في المرحلة النهائية! الخطوة الأخيرة هي جمع الصفيف.
لف الوظيفة السابقة في دالة SUM. تم الانتهاء من! إذن الصيغة النهائية هي:
{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0)) >0, 1))}حساب الإدخالات الفريدة في Excel
هذه وظيفة متقدمة تتطلب الكثير من المعرفة حول Excel. قد يكون الأمر مخيفًا أن تحاول. ولكن بمجرد إعداده ، يمكن أن يكون مفيدًا للغاية ، لذلك قد يكون من المفيد العمل من خلال شرحنا للتأكد من فهمك له.
إذا لم تكن بحاجة إلى حساب الإدخالات الفريدة كثيرًا ، فإن النصيحة السريعة والقذرة لإزالة القيم المكررة ستعمل في قرصة!
يشارك يشارك سقسقة بريد الالكتروني كيفية نسخ الصيغ في Microsoft Excelيعد تعلم أفضل الطرق لنسخ الصيغ ولصقها في جدول بيانات Excel طريقة رائعة لبدء توفير الوقت.
اقرأ التالي مواضيع ذات صلة- إنتاجية
- جدول
- مايكروسوفت اكسل
- تحليل البيانات
J. سيتون كاتب علوم متخصص في تحليل الموضوعات المعقدة. حاصلة على دكتوراه من جامعة ساسكاتشوان. ركز بحثها على الاستفادة من التعلم القائم على الألعاب لزيادة مشاركة الطلاب عبر الإنترنت. عندما لا تعمل ، ستجدها معها تقرأ أو تلعب ألعاب الفيديو أو تعمل في البستنة.
المزيد من Jennifer Seatonاشترك في نشرتنا الإخبارية
انضم إلى النشرة الإخبارية لدينا للحصول على نصائح تقنية ومراجعات وكتب إلكترونية مجانية وصفقات حصرية!
انقر هنا للاشتراك