Note: The other languages of the website are Google-translated. Back to English

كيفية استخراج قيم فريدة بناءً على معايير في Excel؟

لنفترض أن لديك نطاق البيانات الأيسر الذي تريد سرد الأسماء الفريدة للعمود B فقط بناءً على معيار محدد للعمود A للحصول على النتيجة كما هو موضح أدناه لقطة الشاشة. كيف يمكنك التعامل مع هذه المهمة في Excel بسرعة وسهولة؟

استخراج القيم الفريدة استناداً إلى المعايير باستخدام صيغة الصفيف

استخرج قيمًا فريدة استنادًا إلى معايير متعددة باستخدام صيغة صفيف

استخرج القيم الفريدة من قائمة الخلايا بميزة مفيدة

 

استخراج القيم الفريدة استناداً إلى المعايير باستخدام صيغة الصفيف

لحل هذه المهمة ، يمكنك تطبيق صيغة صفيف معقدة ، يرجى القيام بما يلي:

1. أدخل الصيغة أدناه في خلية فارغة حيث تريد سرد نتيجة الاستخراج ، في هذا المثال ، سأضعها في الخلية E2 ، ثم اضغط Shift + Ctrl + Enter مفاتيح للحصول على أول قيمة فريدة.

=IFERROR(INDEX($B$2:$B$15, MATCH(0, IF($D$2=$A$2:$A$15, COUNTIF($E$1:$E1, $B$2:$B$15), ""), 0)),"")

2. بعد ذلك ، اسحب مقبض التعبئة لأسفل إلى الخلايا حتى يتم عرض الخلايا الفارغة ، والآن تم إدراج جميع القيم الفريدة المستندة إلى المعيار المحدد ، انظر لقطة الشاشة:

ملاحظة: في الصيغة أعلاه: B2: B15 هو نطاق العمود الذي يحتوي على القيم الفريدة التي تريد الاستخراج منها ، A2: A15 هو العمود الذي يحتوي على المعيار الذي تستند إليه ، D2 يشير إلى المعيار الذي تريد سرد القيم الفريدة بناءً عليه ، و E1 هي الخلية الموجودة أعلى الصيغة التي أدخلتها.

استخرج قيمًا فريدة استنادًا إلى معايير متعددة باستخدام صيغة صفيف

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

1. أدخل الصيغة أدناه في خلية فارغة حيث تريد سرد القيم الفريدة ، في هذا المثال ، سأضعها في الخلية G2 ، ثم اضغط Shift + Ctrl + Enter مفاتيح للحصول على أول قيمة فريدة.

=IFERROR(INDEX($C$2:$C$15,MATCH(0,COUNTIF(G1:$G$1,$C$2:$C$15)+IF($A$2:$A$15<>$E$2,1,0)+IF($B$2:$B$15<>$F$2,1,0),0)),"")

2. بعد ذلك ، اسحب مقبض التعبئة لأسفل إلى الخلايا حتى يتم عرض الخلايا الفارغة ، والآن تم إدراج جميع القيم الفريدة بناءً على الشرطين المحددين ، انظر لقطة الشاشة:

ملاحظة: في الصيغة أعلاه: C2: C15 هو نطاق العمود الذي يحتوي على القيم الفريدة التي تريد الاستخراج منها ، A2: A15 و E2 هي النطاق الأول بالمعايير التي تريد استخراج قيم فريدة بناءً عليها ، B2: B15 و F2 هي النطاق الثاني بالمعايير التي تريد استخراج قيم فريدة بناءً عليها ، و G1 هي الخلية الموجودة أعلى الصيغة التي أدخلتها.

استخرج القيم الفريدة من قائمة الخلايا بميزة مفيدة

في بعض الأحيان ، تريد فقط استخراج القيم الفريدة من قائمة الخلايا ، هنا ، سأوصي بأداة مفيدة-كوتولس ل إكسيل، بما لديها استخراج الخلايا بقيم فريدة (تضمين أول تكرار) فائدة ، يمكنك استخراج القيم الفريدة بسرعة.

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

بعد تثبيت كوتولس ل إكسيل، يرجى القيام بذلك على النحو التالي:

1. انقر فوق الخلية حيث تريد إخراج النتيجة. (ملاحظة: لا تنقر فوق خلية في الصف الأول.)

2. ثم اضغط كوتولس > مساعد الصيغة > مساعد الصيغة، انظر لقطة الشاشة:

3. في مساعد الصيغ مربع الحوار ، يرجى القيام بالعمليات التالية:

  • اختار نص الخيار من المعادلة النوع قائمة منسدلة؛
  • ثم اختر استخراج الخلايا بقيم فريدة (تضمين أول تكرار) من اختر فرومولا مربع القائمة
  • على اليمين مدخلات الحجج ، حدد قائمة الخلايا التي تريد استخراج القيم الفريدة.

4. ثم اضغط Ok الزر ، يتم عرض النتيجة الأولى في الخلية ، ثم حدد الخلية واسحب مقبض التعبئة إلى الخلايا التي تريد سرد جميع القيم الفريدة حتى تظهر الخلايا الفارغة ، انظر لقطة الشاشة:

تحميل مجاني Kutools لبرنامج Excel الآن!


المزيد من المقالات ذات الصلة:

  • عد عدد القيم الفريدة والمميزة من القائمة
  • لنفترض أن لديك قائمة طويلة من القيم مع بعض العناصر المكررة ، الآن ، تريد حساب عدد القيم الفريدة (القيم التي تظهر في القائمة مرة واحدة فقط) أو القيم المميزة (جميع القيم المختلفة في القائمة ، فهذا يعني فريد القيم + القيم المكررة الأولى) في عمود كما هو موضح في لقطة الشاشة اليسرى. في هذا المقال سأتحدث عن كيفية التعامل مع هذه الوظيفة في Excel.
  • جمع القيم الفريدة بناءً على المعايير في Excel
  • على سبيل المثال ، لدي مجموعة من البيانات التي تحتوي على أعمدة الاسم والنظام ، الآن ، لتلخيص القيم الفريدة فقط في عمود الأمر بناءً على عمود الاسم كما هو موضح في لقطة الشاشة التالية. كيف تحل هذه المهمة بسرعة وسهولة في Excel؟
  • تسلسل القيم الفريدة في Excel
  • إذا كانت لدي قائمة طويلة من القيم التي تم ملؤها ببعض البيانات المكررة ، فأنا الآن أرغب في العثور على القيم الفريدة فقط ثم تجميعها في خلية واحدة. كيف يمكنني التعامل مع هذه المشكلة بسرعة وسهولة في Excel؟

أفضل أدوات إنتاجية المكتب

Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪

  • إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2021 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.
علامة تبويب kte 201905

يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
فرز التعليقات حسب
التعليقات (40)
لا يوجد تقييم. كن أول من يقيم!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، شكرًا على هذا البرنامج التعليمي ، إنه يعمل بشكل مثالي. أحاول تعديله للعمل مع شرط أو ، لكن لا يبدو أنه يعمل - هل هذا ممكن؟ على سبيل المثال = INDEX ($ B $ 2: $ B $ 17 ، MATCH (0 ، IF (OR ($ D $ 2 = $ A $ 2: $ A $ 17 ، $ D $ 2 = $ B $ 2: $ B $ 17) ، COUNTIF ($ E $ 1 : $ E1، $ B $ 2: $ 17 $)، "")، 0))
تم تصغير هذا التعليق بواسطة المشرف على الموقع
شكرا لك على هذا البرنامج التعليمي! أحاول أيضًا تعديل الصيغة ، مثل المعلق أعلاه ، ولكن مع شرط "و" بحيث تفي بمعايير شرطية أخرى (على سبيل المثال ، في هذا المثال ، أود رؤية الأشياء فقط فوق عتبة معينة). هل تستطيع أن تنصحني من فضلك؟ شكرًا لك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، إحدى الطرق للقيام بذلك: استبدل صيغة if بـ sumproduct ((condition1 = rng1) + (condition2 = rng2)) * countif (... لقد نجحت معي. حظًا سعيدًا! باستبدال + بـ * يمكنك اجعله شرطًا أو ، لكن اعتني جيدًا بالأقواس!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
شكرا لك ، هذا رائع!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
شكرًا على هذا ، لقد جربت هذا ويبدو أنني أعمل بشكل جيد بشكل متقطع. المشكلة التي تستمر في التكرار هي أنه في بعض الأحيان ستعود فقط القيمة المطابقة الأولى ثم يتم تكرارها عندما أقوم بالسحب لأسفل لإرجاع جميع القيم المتطابقة. كيف أمنع هذا؟ أي اقتراحات؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
يعمل هذا بشكل جيد حقًا ، ولكن كلما تم تكرار القيمة التي يتم إدخالها ، فإنه يضع القيمة مرة واحدة فقط. على سبيل المثال ، إذا كانت قائمتك تحتوي على اثنين من Lucy's ، فإنها تجلب لوسي واحدة فقط إلى الجدول الجديد. هل هناك طريقة لإصلاح هذا؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
عند استخدام هذه الصيغة ، فإنها تستمر في تكرار القيمة الأولى ، كيف يمكنك إيقاف ذلك وتقديم قائمة القيم التي تساوي المنتج في D2؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، لإيقاف تكرار القيمة الأولى أثناء السحب لأسفل ، يجب عليك حساب الخلية أعلى الخلية التي تضع الصيغة فيها. على سبيل المثال ، إذا كانت الصيغة تسير في E2 ، فيجب عليك كتابة countif ($ E $ 1: $ E1 ...
تم تصغير هذا التعليق بواسطة المشرف على الموقع
اهلا ريان. تعمل الصيغ بشكل رائع ، ولكن عند السحب لأسفل ، يستمر تكرار القيمة الأولى. لقد تأكدت من أن COUNTIF تشير إلى الخلية أعلى الخلية التي تحتوي على الصيغة ، ولكن لا تزال تكرر القيمة الأولى عند السحب لأسفل؟ (على سبيل المثال ، إذا كانت صيغة الصفيف في C2 ، فإن COUNTIF يشير إلى الخلية $ C $ 1: $ C $ 1)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
اهلا ريان. تعمل الصيغ بشكل رائع ، ولكن عند السحب لأسفل ، يستمر تكرار القيمة الأولى. لقد تأكدت من أن COUNTIF تشير إلى الخلية أعلى الخلية التي تحتوي على الصيغة ، ولكن لا تزال تكرر القيمة الأولى عند السحب لأسفل؟ (على سبيل المثال ، إذا كانت صيغة الصفيف في C2 ، فإن COUNTIF يشير إلى الخلية $ C $ 1: $ C $ 1)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
ربما لا يعمل لأنك أغلقت الخلايا - حاول استبدال $ C $ 1: $ C $ 1 بـ $ C $ 1: $ C1
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كان هذا مفيدًا للغاية ، لكني أحصل على مضاعفات من جميع الأسماء مثل هذا:
دو ، جين
دو ، جين
هوفر ، توم
هوفر ، توم

كيف يمكنني التوقف عن هذا؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، تظهر لي رسالة الخطأ "# N / A" في "وظيفة المطابقة" ، هل يمكنك التوجيه؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
= IF (AL2 = "AP"، "AP"، IF (AK2 = "AD"، "AD"، IF (Z2> 500000، "Yes"، "No"))) أريد تلبية "جميع الشروط" ليقول نعم ... يتفوق يعكس الخطأ في هذه الصيغة .. الرجاء تقديم المشورة
تم تصغير هذا التعليق بواسطة المشرف على الموقع
في الواقع ، أريد أن تعكس الخلية "YES" إذا كان (AL2 = "AP" و AK2 = "AD" و Z2> 500000)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أتلقى خطأ # N / A في دالة المطابقة مع هذه الصيغة. هل يمكنك الرجاء المساعدة؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، تظهر لي رسالة الخطأ "# N / A" في "وظيفة المطابقة" ، هل يمكنك التوجيه؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
إذا تلقيت الخطأ # N / A ، فانتقل إلى الصيغة واستخدم Control + Shift + Enter بدلاً من Enter.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أحصل على 0 بدلاً من النتائج المتوقعة ، الصيغة تعمل بشكل جيد للبيانات الموجودة في نفس الورقة ، هل لديك أي حل للبيانات في ورقة مختلفة؟

هذه هي صيغتي

=IFERROR(INDEX('Switching Data'!$B$7:$B$204,MATCH(0,IF($A$2='Switching Data'!$A$7:$A$204,COUNTIF($A$4:A4,'Switching Data'!$B$7:$B$204),""),0)),0)
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا جون ،
بعد إدخال الصيغة ، يجب أن تضغط على Ctrl + Shift + Enter معًا ، وليس فقط مفتاح الإدخال.
من فضلك جربها ، شكرا لك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا غون ، أتمنى أن تكون بخير. أتساءل عما إذا كان يمكنك حل هذه المشكلة. أتلقى نفس الخطأ عندما تأتي الصيغة من ورقة مختلفة. سأقدر مشاركة الحل إذا حصلت عليه.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
شكرا لك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
كيف يمكنني الحصول على هذه الصيغة لإرجاع كل من التكرارات بدلاً من واحد من كل اسم؟ على سبيل المثال ، في المثال أعلاه ، كيف يمكنني الحصول على عمود النتائج (ب: ب) لإرجاع لوسي ، وروبي ، وآني ، وخوسيه ، ولوسي ، وآني ، وتوم؟ أنا أستخدم هذا كأداة للميزانية يتم سحبها إلى ملخصات حساب محددة من دفتر الأستاذ العام. ومع ذلك ، فإن العديد من المبالغ وأوصاف المعاملات مكررة في دفتر الأستاذ العام. بمجرد سحب أول القيم المكررة ، لن يتم سحب المزيد منها.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا جو ،
لاستخراج جميع القيم المقابلة بناءً على معايير خلية محددة ، يمكن أن تساعدك صيغة الصفيف التالية ، انظر لقطة الشاشة:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

بعد إدخال الصيغة ، يرجى الضغط على Shift + Ctrl + Enter معًا للحصول على النتيجة الصحيحة ، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع القيم.
آمل أن يساعدك هذا ، شكرًا لك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
حتى الان جيدة جدا. أنا قادر على تكرار النتائج في ورقة الاختبار ، وإجراء تغييرات على الصفيف ، ثم تصحيح الصيغة لحساب التغييرات التي أجريتها. أخطط لنقل هذا إلى الورقة الرئيسية اليوم وأرى كيف يعمل. شكرا للمساعدة!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
حسنًا ، إنه يعمل في المصنف الرئيسي. هناك استثناء واحد لم أتمكن من تحديد سبب: إذا لم تبدأ المصفوفة (في حالتي ، دفتر الأستاذ العام الذي بدأته في الصف 3) في الصف 1 ، فإن القيم التي تم إرجاعها غير صحيحة. ما الذي يسبب هذه المشكلة ، وأي مصطلح في الصيغة يصلحها؟ شكرا مرة أخرى على مساعدتكم مع هذا!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
السؤال الأخير: إذا أردت أن يعرض عمود النتائج جميع القيم غير المرتبطة بـ KTE أو KTO (لذلك ، سيكون D: D هو Tom و Nocol و Lily و Angelina و Genna) ، كيف أفعل ذلك؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
بالنسبة لي الصيغة لا تعمل. أضغط على ctrl shift enter وما زلت أحصل على خطأ N / A. أود أن أضيف أنني قمت بإعداد نفس البيانات تمامًا كما في البرنامج التعليمي. ما هو سبب عدم عملها؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لقد عمل هذا بشكل رائع بالنسبة لي مع قيمة بحث محددة. ومع ذلك ، إذا أردت استخدام حرف بدل للبحث عن قيم جزئية ، فكيف أفعل ذلك؟ على سبيل المثال ، إذا أردت البحث عن جميع الأسماء المرتبطة بـ KT؟

أنا أستخدم هذه الوظيفة للبحث عن الخلايا التي تحتوي على نصوص متعددة. على سبيل المثال ، إذا كان لكل منتج أيضًا منتجًا فرعيًا داخل نفس الخلية ولكني كنت أبحث فقط عن الأسماء المرتبطة بالمنتج الفرعي "elf".

KTE - قزم
KTE- الكرة
KTE - بيانو
KTO - قزم
KTO- الكرة
KTO - بيانو
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هل هناك طريقة لجعل هذا العمل مع السماح للقيم المكررة؟ على سبيل المثال ، أريد أن يتم إدراج جميع مثيلات Lucy في النتائج.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا قسطنطين
لاستخراج جميع القيم المقابلة بما في ذلك التكرارات بناءً على معايير خلية محددة ، يمكن أن تساعدك صيغة الصفيف التالية ، انظر لقطة الشاشة:
=IF(ISERROR(INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2)),"",
INDEX($A$1:$B$17,SMALL(IF($A$1:$A$17=$D$2,ROW($A$1:$A$17)),ROW(1:1)),2))

بعد إدخال الصيغة ، يرجى الضغط على Shift + Ctrl + Enter معًا للحصول على النتيجة الصحيحة ، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع القيم.
آمل أن يساعدك هذا ، شكرًا لك!
لا توجد تعليقات منشورة هنا حتى الآن
عرض المزيد
اترك تعليقاتك
النشر كضيف
×
قيم المنشور:
0   الشخصيات
المواقع المقترحة

تواصل معنا

حقوق التأليف والنشر © 2009 - شبكة الاتصالات العالمية.extendoffice.com. | كل الحقوق محفوظة. مشغل بواسطة ExtendOffice. | | خريطة الموقع
Microsoft وشعار Office هما علامتان تجاريتان أو علامتان تجاريتان مسجلتان لشركة Microsoft Corporation في الولايات المتحدة و / أو دول أخرى.
محمي بواسطة Sectigo SSL