كيفية استخراج القيم الفريدة من عدة أعمدة في Excel؟

إذا كنت تعمل غالبًا مع مجموعات بيانات موزعة عبر عدة أعمدة في Excel، فقد تواجه حالات يكون فيها بعض القيم مكرر ضمن نفس العمود أو بين أعمدة مختلفة. في العديد من المهام المتعلقة بالتقارير أو تحليل البيانات، يصبح من الضروري تحديد واستخراج جميع القيم الفريدة — تلك التي تظهر مرة واحدة فقط عبر نطاق التحديد بأكمله، بغض النظر عن موقعها. القيام بذلك يدويًا قد يستغرق وقتًا طويلًا وعرضة للأخطاء، خاصة عند التعامل مع مجموعات بيانات كبيرة أو جداول معقدة. لحسن الحظ، يقدم Excel مجموعة من الطرق لاستخراج هذه القيم الفريدة بكفاءة.
يقدم هذا الدليل عدة حلول يمكنك استخدامها بناءً على إصدار Excel الخاص بك وتفضيلاتك — مثل الصيغ المناسبة لجميع الإصدارات، والصيغ الديناميكية المصفوفة للإصدارات الحديثة، واستخدام مساعد Kutools AI للحصول على نتائج مباشرة، والجداول المحورية للتجميع المرئي، وأكواد VBA للاستخراج التلقائي في السيناريوهات المعقدة.
استخراج القيم الفريدة من عدة أعمدة باستخدام الصيغ
قد تكون هناك أوقات ترغب فيها في تحقيق هذا الاستخراج باستخدام وظائف Excel المدمجة. يوضح هذا القسم كيفية القيام بذلك باستخدام طريقتين: صيغة مصفوفة مناسبة لجميع إصدارات Excel، وصيغة مصفوفة ديناميكية متاحة في الإصدارات الأحدث مثل Excel 365 وExcel 2021. هذه الأساليب مثالية عندما تريد حلاً مباشرًا بالاعتماد على الصيغ، يتطلب تحديثًا متكررًا مع تغير بياناتك، أو تحتاج إلى تجنب الوظائف الإضافية الخارجية أو الكود.
استخراج القيم الفريدة من عدة أعمدة باستخدام صيغة مصفوفة لجميع إصدارات Excel
لضمان التوافق عبر جميع إصدارات Excel، فإن استخدام صيغة مصفوفة يتيح لك استخراج القيم الفريدة من عدة أعمدة — حتى لو لم يكن Excel الخاص بك يدعم المصفوفات الديناميكية. تعتمد هذه الطريقة على مجموعة من وظائف INDIRECT، TEXT، MIN، IF، COUNTIF، ROW، وCOLUMN، مما يجعلها مرنة لمختلف الهياكل البياناتية.
لنفترض أن بياناتك موجودة في النطاق A2:C9. لاستخراج القيم الفريدة بدءًا من الخلية E2، استخدم الإجراء التالي:
1. اضغط على الخلية E2 (أو أول خلية في نطاق الإخراج)، وأدخل الصيغة المصفوفة التالية:
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
- A2:C9 هو النطاق الذي تريد استخراج القيم الفريدة منه.
- E1:E1 يشير إلى الخلايا الموجودة فورًا فوق أول خلية إخراج وهي ضرورية لتتبع العناصر التي تم إخراجها بالفعل.
- $2:$9 هي مراجع الصفوف الخاصة ببياناتك؛ $A:$C هي مراجع الأعمدة. قم بتعديل هذه كما يلزم لتناسب تخطيط ورقة العمل الخاصة بك.
2. بمجرد إدخال الصيغة، بدلاً من الضغط على Enter فقط، اضغط على Ctrl + Shift + Enter معًا لتأكيد ذلك كصيغة مصفوفة. عند القيام بذلك بشكل صحيح، ستظهر الأقواس {} حول صيغتك في شريط الصيغة. بعد ذلك، اسحب مقبض التعبئة من E2 إلى أسفل العمود. استمر في السحب حتى تظهر خلايا فارغة، مما يشير إلى عدم وجود المزيد من القيم الفريدة المتبقية للاستخراج. تضمن هذه العملية عرض جميع القيم الفريدة في العمود المستهدف.
- $A$2:$C$9: يحدد مجموعة الخلايا بالكامل التي سيتم فحصها للعثور على القيم الفريدة.
- IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C),7^8):
- $A$2:$C$9<>"" يضمن تجاهل الخلايا الفارغة.
- COUNTIF($E$1:E1,$A$2:$C$9)=0 يتأكد من تضمين القيم الجديدة (التي لم يتم استخراجها بعد) فقط.
- إذا كانت كلتا الشرطين صحيحتين، فإن الناتج سيكون حسابًا بناءً على صف الخلية وعمودها لإنشاء رقم فهرس فريد.
- إذا كانت أي من الشروط خاطئة، فإن الصيغة تعيد عددًا كبيرًا جدًا (7^8) لمنع الاختيار العشوائي.
- MIN(...): يحدد أقل رقم فهرس، مما يؤدي إلى تحديد موقع القيمة الفريدة التالية المتاحة داخل البيانات.
- TEXT(...,"R0C00"): يحول الفهرس إلى مرجع خلية صالح باستخدام نمط R1C1.
- INDIRECT(...): يحول مرجع الخلية الذي تم إنشاؤه أعلاه إلى قيمة من نطاق بياناتك.
- &"": يجبر نتيجة الصيغة على أن تُعامل كنص، مما يضمن عدم وجود مفاجآت في التنسيق.
استخراج القيم الفريدة من عدة أعمدة باستخدام صيغة لـ Excel 365، Excel 2021 والإصدارات الأحدث
إذا كنت تستخدم Excel 365، Excel 2021 أو إصدارًا أحدث، سيكون لديك إمكانية الوصول إلى وظائف المصفوفات الديناميكية، التي توفر طريقة أبسط وأكثر بديهية لاستخراج القيم الفريدة من عدة أعمدة. تقوم وظائف UNIQUE وTOCOL بتسهيل وتسريع عملية دمج البيانات عبر الأعمدة وإزالة التكرارات في خطوة واحدة — وهو أمر مفيد للغاية لأولئك الذين يعملون مع مجموعات بيانات تتغير باستمرار أو أكبر.
للاستخدام هذه الطريقة، ما عليك سوى اختيار خلية فارغة (على سبيل المثال، E2، أو أي مكان آخر ترغب في ظهور النتائج فيه)، وأدخل هذه الصيغة واضغط Enter:
=UNIQUE(TOCOL(A2:C9,1))
بعد الضغط على Enter، ستظهر جميع القيم الفريدة من النطاق A2:C9 تلقائيًا في الخلايا تحت الصيغة. هذه الميزة فعالة بشكل خاص — يتم تحديث الإخراج ديناميكيًا مع تغيير بيانات المصدر، مما يوفر عليك خطوات التحديث اليدوية.
- TOCOL(A2:C9,1): يحول نطاق القيم الخاص بك من عدة أعمدة إلى عمود واحد، ويقوم بإزالة الخلايا الفارغة تلقائيًا.
- UNIQUE(...): يستخرج كل قيمة مرة واحدة، مما يوفر قائمة نظيفة وخالية من التكرارات.
استخراج القيم الفريدة من عدة أعمدة باستخدام مساعد Kutools AI
إذا كنت ترغب في نهج أكثر سهولة وتقليل الجهد اليدوي، يمكن لمساعد Kutools AI في Kutools for Excel مساعدتك في استخراج القيم الفريدة من عدة أعمدة بسهولة. هذه الطريقة لها قيمة خاصة إذا لم تكن على دراية بالصيغ أو ترغب في تجنب مخاطر أخطاء الصيغ. يقوم مساعد Kutools AI بتفسير التعليمات الخاصة بك ومعالجة البيانات تلقائيًا، مما يجعله مثاليًا لكل من المبتدئين والمستخدمين الذين يبحثون عن حل سريع ببضع نقرات فقط.
بعد التثبيت، اضغط على Kutools AI > مساعد AI لفتح لوحة "مساعد Kutools AI":
- أدخل طلبك في مربع الدردشة، مثل: "استخراج القيم الفريدة من النطاق A2:C9، مع تجاهل الخلايا الفارغة، وضع النتائج بدءًا من E2:"
- انقر على "إرسال" أو اضغط على Enter، وبعد تحليل AI للطلب، اضغط ببساطة على "تنفيذ" لتشغيل الأمر. ستظهر النتائج فورًا في ورقة العمل الخاصة بك، في الموقع الذي حددته بالضبط.
نصيحة: هذه الحلول مفيدة جدًا إذا كان سير عمل استخراج البيانات الخاص بك يتغير أو إذا كنت ترغب في ميزات معالجة اللغة الطبيعية. تذكر التحقق من القائمة المستخرجة للخلايا الفارغة إذا لم تكن بياناتك الأصلية متسقة تمامًا، حيث قد يتم تضمين أو تصفيتها بناءً على تفاصيل طلبك للذكاء الاصطناعي.
استخراج القيم الفريدة من عدة أعمدة باستخدام الجدول المحوري
الجداول المحورية هي طريقة أخرى ملائمة لاستخراج القيم الفريدة، خاصة إذا كنت تفضل العمل بالأدوات المرئية وتلخيص أو تحليل العناصر الفريدة بشكل أكبر، مثل حساب التكرارات. هذه الطريقة مباشرة ولا تتطلب صيغ. ومع ذلك، تتطلب بضع خطوات إعدادية وإعادة ترتيب طفيفة للبيانات، خاصة إذا كانت الأعمدة المشاركة تحتوي على عناوين مختلفة.
إليك عملية مقترحة لاستخراج القيم الفريدة باستخدام جدول محوري:
1. أدخل عمودًا فارغًا جديدًا فورًا إلى يسار بياناتك. على سبيل المثال، أدخل عمودًا جديدًا A إذا بدأت بياناتك من العمود B. يساعد هذا التعديل في ضمان دمج النطاق بشكل صحيح.
2. حدد أي خلية داخل مجموعة البيانات الخاصة بك، واضغط Alt + D، ثم اضغط بسرعة P لتشغيل "معالج PivotTable وPivotChart." في الخطوة الأولى من المعالج، حدد "نطاقات تجميع متعددة." يتيح لك هذا دمج القيم من عدة أعمدة في مجال ملخص واحد.
3. انقر على التالي، ثم اختر "إنشاء حقل صفحة واحد لي." هذه الخطوة تنظم جميع بياناتك كمجموعة واحدة لتسهيل استخراج القيم الفريدة.
4. في الخطوة التالية، حدد نطاق البيانات بالكامل (بما في ذلك العمود الفارغ الجديد)، انقر على زر الإضافة لإدراج تحديدك في قائمة "كل النطاقات"، ثم انقر على التالي.
5. في الخطوة الأخيرة من المعالج، حدد المكان الذي ترغب في وضع الجدول المحوري فيه (ورقة عمل جديدة أو ورقة موجودة)، ثم انقر على إنهاء لتوليد تقرير الجدول المحوري.
6. في الجدول المحوري الجديد، قم بإلغاء تحديد جميع الحقول في قسم "اختيار الحقول لإضافتها إلى التقرير" لمسح العرض الافتراضي.
7. أخيرًا، اسحب حقل "القيمة" إلى منطقة الصفوف. سيعرض الجدول المحوري جميع القيم الفريدة من النطاق متعدد الأعمدة الأصلي، مرتبة بشكل أنيق في عمود واحد.
القيود: تحتاج البيانات إلى ترتيب مسبق، وإذا تم تحديث مجموعة البيانات المصدر، يجب تحديث الجدول المحوري لرؤية القيم الفريدة الجديدة.
استخراج القيم الفريدة من عدة أعمدة باستخدام كود VBA
في الحالات التي تحتاج فيها إلى أتمتة الاستخراج أو التعامل مع مجموعات بيانات كبيرة وغير منتظمة، يمكن أن توفر لك استخدام كود VBA (Visual Basic for Applications) حلاً سريعًا وقابلًا لإعادة الاستخدام. هذا مثالي للمستخدمين الذين لديهم معرفة أساسية بمحرر Excel VBA، أو للمهام المتكررة حيث ترغب في تقليل العمليات اليدوية. يمكن أيضًا لـ VBA التعامل مع أحجام البيانات الكبيرة بكفاءة أكبر من الصيغ المصفوفة.
1. افتح محرر VBA بالضغط على Alt + F11. في نافذة "Microsoft Visual Basic for Applications" التي تظهر، انقر إدراج > وحدة لإضافة وحدة جديدة.
2. في الوحدة الجديدة، ألصق الكود أدناه:
VBA: استخراج القيم الفريدة من عدة أعمدة
Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
If rng.Value <> "" Then
dt(rng.Value) = ""
End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub
3. اضغط على F5 لتشغيل الكود. سيظهر مربع حوار يطلب منك تحديد نطاق البيانات. حدد جميع الأعمدة ذات الصلة (بما في ذلك تلك التي تحتوي على خلايا فارغة).
4. بعد النقر على موافق، سيظهر موجه آخر يسأل عن مكان إخراج القيم الفريدة. حدد الخلية العلوية حيث تريد أن يتم إدراج النتائج (على سبيل المثال، E2).
5. انقر على موافق، وسيعمل الماكرو تلقائيًا. ستظهر جميع القيم الفريدة، بدءًا من الموقع الذي حددته.
- إذا تلقيت أخطاء مثل #VALUE! أو #SPILL! عند استخدام الصيغ، تحقق من نطاقاتك وتأكد من أن منطقة الإخراج خالية.
- تحقق دائمًا من وجود صفوف مخفية أو خلايا مدمجة في نطاق بياناتك، حيث قد تؤثر على صحة استخراج القيم الفريدة.
- الصيغ المصفوفة والصيغ المصفوفة الديناميكية يتم تحديثها تلقائيًا مع التغييرات، لكن حلول Advanced Filter والجداول المحورية قد تتطلب تحديثًا يدويًا أو إعادة التشغيل.
- بالنسبة للمهام المتكررة، فكر في أتمتة الاستخراج باستخدام VBA لتحقيق الاتساق والسرعة.
- قم بنسخ بياناتك الاحتياطيًا قبل تطبيق أي عمليات استخراج أو أتمتة واسعة النطاق، خاصة في المصنفات المعقدة.
مقالات ذات صلة:
- حساب عدد القيم الفريدة والمميزة من قائمة
- لنفترض أن لديك قائمة طويلة من القيم مع بعض العناصر المكررة، وتريد حساب عدد القيم الفريدة (القيم التي تظهر مرة واحدة فقط) أو القيم المميزة الإجمالية الموجودة في عمود، كما هو موضح في لقطة الشاشة اليسرى. يشرح هذا المقال طرقًا فعالة لحساب الإدخالات الفريدة والمميزة في Excel.
- استخراج القيم الفريدة بناءً على معايير في Excel
- لنفترض أنك تريد استخراج الأسماء الفريدة فقط من العمود B بناءً على شرط معين في العمود A، مما ينتج عنه نتائج كما هو موضح في لقطة الشاشة. يوضح هذا البرنامج التعليمي طرق تطبيق المعايير عند استخراج القيم الفريدة.
- السماح فقط بالقيم الفريدة في Excel
- إذا كنت ترغب في السماح فقط بالإدخالات الفريدة في عمود ورقة العمل ومنع القيم المكررة، يقدم هذا المقال تقنيات عملية لتطبيق قواعد الفريدة في Excel.
- جمع القيم الفريدة بناءً على معايير في Excel
- على سبيل المثال، قد تحتاج إلى جمع القيم الفريدة فقط في عمود "الطلب" بناءً على الأسماء في العمود المجاور، كما هو موضح في لقطة الشاشة. يناقش هذا المقال نهجًا لدمج الحسابات الفريدة والمشروطة.
أفضل أدوات زيادة الإنتاجية لمجموعة Office
عزز مهاراتك في Excel مع Kutools لـ Excel، وتمتع بكفاءة غير مسبوقة. يقدم Kutools لـ Excel أكثر من300 ميزة متقدمة لتعزيز الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يجلب تجربة التبويبات إلى Office، ويجعل عملك أسهل بكثير
- فعّل تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint
- افتح وأنشئ مستندات متعددة في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ جديدة.
- يمنحك زيادة إنتاجية تصل إلى50% ويوفر عليك مئات النقرات يومياً!