كيف يمكن استخراج القيم الفريدة من أعمدة متعددة في Excel؟
إذا كنت تعمل غالبًا مع مجموعات بيانات موزعة عبر عدة أعمدة في Excel، فقد تصادف قيمًا متكررة داخل عمودٍ واحد أو بين أعمدة مختلفة. وعند إعداد التقارير أو تحليل البيانات، يصبح من الضروري تحديد واستخراج جميع القيم الفريدة—أي تلك التي تظهر مرة واحدة فقط في كامل نطاق التحديد، بغض النظر عن موقعها. والقيام بذلك يدويًّا قد يستغرق وقتًا طويلاً ويؤدي إلى أخطاء، خاصةً مع مجموعات البيانات الكبيرة أو الجداول المعقدة. لحسن الحظ، يوفّر Excel طرقًا فعّالة لاستخراج هذه القيم الفريدة بكفاءة.
يقدّم هذا الدليل مجموعةً من الحلول التي يمكنك استخدامها وفقًا لإصدار Excel الخاص بك وتفضيلاتك—مثل الصيغ المتوافقة مع جميع الإصدارات، وصيغ المصفوفات الديناميكية المخصصة للإصدارات الحديثة، وأداة KUTOOLS AI Aide للحصول على نتائج فورية، والجدول المحوري لدمج البيانات بصريًّا، بالإضافة إلى كود 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 Aide
إذا كنت تبحث عن نهجٍ أكثر سلاسة وتريد تقليل الجهد اليدوي، فإن **KUTOOLS AI Aide** في Kutools لـ Excel يُسهّل عليك استخراج القيم الفريدة من أعمدة متعددة بنقرات قليلة. هذه الطريقة مثالية خصوصًا إذا لم تكن معتادًا على الصيغ أو أردت تجنب الأخطاء التي قد تنجم عنها. يقوم KUTOOLS AI Aide بتفسير تعليماتك ومعالجة البيانات تلقائيًا، مما يجعله الحل الأمثل لكلٍّ من المبتدئين والمستخدمين الذين يسعون إلى إنجاز المهام بسرعة ودقة.
بعد التثبيت، انقر فوقKUTOOLS AI>مساعد الذكاء الاصطناعيلفتح جزء «KUTOOLS AI Aide»:
- أدخل طلبك في مربع الدردشة، مثل:«استخرج القيم الفريدة من النطاق A2:C9، مع تجاهل الخلايا الفارغة، وضع النتائج بدءًا من الخلية E2:»
- انقر على "إرسال« أو اضغط Enter، ثم بعد أن يحلّل الذكاء الاصطناعي طلبك، انقر ببساطة على »تنفيذ" لتشغيله. ستظهر النتائج فورًا في ورقة العمل الخاصة بك، بالضبط في الموقع الذي حددته.
تلميح:يُعدّ هذا الحل مفيدًا جدًّا إذا كان سير عمل استخراج البيانات الخاص بك مختلفًا، أو إذا كنت تبحث عن ميزات معالجة اللغة الطبيعية. تذكّر أن تتحقّق مرتين من استخراج القائمة بالنسبة للخلايا الفارغة إذا كانت بياناتك الأصلية غير منتظمة تمامًا، إذ قد يتم تضمين الإدخالات الفارغة أو تصفية بناءً على تفاصيل طلب الذكاء الاصطناعي الخاص بك.
استخراج القيم الفريدة من أعمدة متعددة باستخدام جدول بيانات محوري
جدول البيانات المحوري هو وسيلة ملائمة أخرى لاستخراج القيم الفريدة، خاصةً إذا كنت تفضّل استخدام الأدوات البصرية وترغب في تلخيص العناصر الفريدة أو تحليلها بشكل أعمق، مثل حساب عدد مرات ظهور كل منها. ويتميّز هذا الأسلوب بأنه مباشر ولا يتطلب كتابة أي صيغ. ومع ذلك، فإنه يحتاج إلى بضع خطوات إعداد أولية وإعادة ترتيب طفيفة للبيانات، لا سيما إذا كانت الأعمدة المستهدفة تحتوي على عناوين مختلفة.
فيما يلي عملية مقترحة لاستخراج القيم الفريدة باستخدام جدول بيانات محوري:
1.أدرج عمودًا فارغًا جديدًا مباشرةً على يسار بياناتك. على سبيل المثال، أدرج عمودًا جديدًا A إذا كانت بياناتك تبدأ من العمود B. يساعد هذا التعديل في ضمان دمج النطاقات بشكل صحيح.
2. حدد أي خلية ضمن مجموعة البيانات الخاصة بك، ثم اضغطAlt + D، وسرعان ما اضغطPلتشغيل «معالج PivotTable و PivotChart». في الخطوة الأولى من المعالج، اختر "نطاقات تجميع متعددة"—فهذا يمكّنك من دمج القيم من أعمدة عديدة في حقل ملخّص واحد.
3. انقر فوقالتالي، ثم اختر "إنشاء حقل صفحة واحد لي". تنظّم هذه الخطوة جميع بياناتك كمجموعة واحدة لتسهيل استخراج القيم الفريدة.
4.في الخطوة التالية، حدد نطاق البيانات بالكامل (بما في ذلك العمود الفارغ الجديد)، ثم انقر فوق الزرإضافةلإدراج تحديدك في قائمة «جميع النطاقات»، وبعد ذلك انقر فوقالتالي.
5. في الخطوة الأخيرة من المعالج، حدد المكان الذي تريد وضع الجدول المحوري فيه—سواء في ورقة عمل جديدة أو ورقة موجودة—ثم انقر فوقإنهاءلتوليد تقرير الجدول المحوري.
6.في جدول البيانات المحوري الجديد، ألغِ تحديد جميع الحقول في قسم «اختر الحقول التي تريد إضافتها إلى التقرير» لمسح العرض الافتراضي.
7.أخيرًا، اسحب حقل «القيمة» إلى منطقةالصفوف. وسيعرض الجدول المحوري جميع القيم الفريدة من نطاق الأعمدة المتعددة الأصلي، مرتبةً بشكلٍ منظّم في عمودٍ واحد.
القيود:تحتاج البيانات إلى ترتيب أولي، وإذا تم تحديث مجموعة البيانات الأصلية الخاصة بك، فيجب عليك تحديث جدول بيانات محوري لرؤية القيم الفريدة الجديدة.
استخراج القيم الفريدة من أعمدة متعددة باستخدام كود VBA
عندما تحتاج إلى أتمتة عمليات الاستخراج أو التعامل مع مجموعات بيانات كبيرة وغير منتظمة، يُعدّ استخدام رمز VBA (Visual Basic for Applications) حلاً سريعًا وقابلًا لإعادة الاستخدام. وهو خيار مثالي للمستخدمين الذين لديهم إلمام أساسي بمُحرّر Excel VBA، أو لل(tasks) المتكررة التي ترغب فيها بتقليل الاعتماد على العمليات اليدوية. كما يتميّز 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! عند استخدام الصيغ، فتأكد من صحة نطاقاتك وأن منطقة الإخراج خالية تمامًا.
- تأكد دائمًا من وجود صفوف مخفية أو مدمجة في نطاق بياناتك، إذ قد تؤثر هذه العناصر على دقة استخراج القيم الفريدة.
- تُحدَّث صيغ المصفوفات والمصفوفات الديناميكية تلقائيًّا مع إجراء التغييرات، لكن قد تتطلب حلول المرشِّح المتقدم وجداول البيانات المحورية تحديثًا يدويًّا أو إعادة تشغيل.
- للمهام المتكررة، فكّر في أتمتة عملية الاستخراج باستخدام VBA لضمان الاتساق والسرعة.
- احفظ نسخة احتياطية من بياناتك قبل تطبيق أي إجراءات استخراج جماعي أو أتمتة، لا سيما في ملفات العمل المعقدة.
مقالات ذات صلة إضافية:
- حساب عدد القيم الفريدة والمميزة من قائمة
- افترض أن لديك قائمة طويلة من القيم تحتوي على عناصر مكررة، وترغب في حساب عدد القيم الفريدة (أي تلك التي تظهر مرة واحدة فقط) أو إجمالي القيم المميزة في عمود، كما يظهر في لقطة الشاشة على اليسار. يشرح هذا المقال طرقًا فعّالة لحساب القيم الفريدة والمميزة في Excel.
- استخراج القيم الفريدة بناءً على معايير في Excel
- افترض أنك تريد استخراج الأسماء الفريدة من العمود B بناءً على شرط معيّن في العمود A، للحصول على نتائج مشابهة لتلك الموضحة في لقطة الشاشة. يعرض هذا البرنامج التعليمي طرق تطبيق المعايير أثناء استخراج القيم الفريدة.
- السماح بالقيم الفريدة فقط في Excel
- إذا كنت ترغب في السماح بإدخالات فريدة فقط في عمود بورقة العمل ومنع تكرار القيم، يقدّم هذا المقال تقنيات عملية لفرض قواعد التفرُّد في Excel.
- جمع القيم الفريدة بناءً على معايير في Excel
- على سبيل المثال، قد تحتاج إلى جمع القيم الفريدة فقط في عمود «الطلب» بناءً على الأسماء الموجودة في العمود المجاور، كما يظهر في لقطة الشاشة. ويستعرض هذا المقال طرق دمج العمليات الحسابية الفريدة والمشروطة بفعالية.
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل