انتقل إلى المحتوى الرئيسي

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

لنفترض أن لديك عدة أعمدة بقيم متعددة ، فإن بعض القيم مكررة في نفس العمود أو عمود مختلف. والآن تريد العثور على القيم الموجودة في أي من العمودين مرة واحدة فقط. هل هناك أي حيل سريعة يمكنك اتباعها لاستخراج قيم فريدة من عدة أعمدة في Excel؟


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

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

استخراج قيم فريدة من أعمدة متعددة باستخدام صيغة المصفوفة لجميع إصدارات Excel

بالنسبة للمستخدمين الذين لديهم أي إصدار من Excel، يمكن أن تكون صيغ الصفيف أداة فعالة لاستخراج القيم الفريدة عبر أعمدة متعددة. وإليك كيف يمكنك القيام بذلك:

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 يقف أمام الصفوف التي تحتوي على الخلايا التي تريد استخدامها ، و $ أ: $ ج يشير إلى الأعمدة التي تحتوي على الخلايا التي تريد استخدامها. يرجى تغييرها بنفسك.

2. ثم اضغط شيفت + كترل + إنتر معًا ، ثم اسحب مقبض التعبئة لاستخراج القيم الفريدة حتى تظهر الخلايا الفارغة. انظر لقطة الشاشة:

شرح هذه الصيغة:
  1. 2 دولار أسترالي: 9 دولارات كندية: يحدد هذا نطاق البيانات المراد فحصه، وهو الخلايا من A2 إلى C9.
  2. 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 يحدد ما إذا كانت قيم هذه الخلايا لم يتم إدراجها بعد في نطاق الخلايا من E1 إلى E1.
    • إذا تم استيفاء كلا الشرطين (على سبيل المثال، القيمة ليست فارغة ولم يتم إدراجها بعد في العمود E)، فإن الدالة IF تحسب رقمًا فريدًا استنادًا إلى الصف والعمود الخاص بها (ROW($2:$9)*100+COLUMN($A: $ج)).
    • إذا لم يتم استيفاء الشروط، تقوم الدالة بإرجاع رقم كبير (7^8)، والذي يعمل كعنصر نائب.
  3. دقيقة (...): يبحث عن أصغر رقم يتم إرجاعه بواسطة الدالة IF أعلاه، والذي يتوافق مع موقع القيمة الفريدة التالية.
  4. النص(...،"R0C00"): يحول هذا العدد الأدنى إلى عنوان نمط R1C1. يشير رمز التنسيق R0C00 إلى تحويل الرقم إلى تنسيق مرجع خلية Excel.
  5. غير مباشر(...): يستخدم الدالة INDIRECT لتحويل عنوان نمط R1C1 الذي تم إنشاؤه في الخطوة السابقة إلى مرجع خلية نمط A1 عادي. تسمح الدالة INDIRECT بمرجع الخلية بناءً على محتوى سلسلة نصية.
  6. &"": يضمن إلحاق &"" في نهاية الصيغة معالجة الإخراج النهائي كنص، لذلك سيتم عرض الأرقام الزوجية كنص.
 
استخرج قيمًا فريدة من أعمدة متعددة باستخدام صيغة Excel 365

يدعم Excel 365 المصفوفات الديناميكية، مما يسهل استخراج القيم الفريدة من أعمدة متعددة:

الرجاء إدخال الصيغة التالية أو نسخها في خلية فارغة حيث تريد وضع النتيجة، ثم انقر فوق أدخل مفتاح للحصول على كافة القيم الفريدة مرة واحدة. انظر لقطة الشاشة:

=UNIQUE(TOCOL(A2:C9,1))


استخرج قيمًا فريدة من أعمدة متعددة باستخدام Kutools AI Aide

أطلق العنان لقوة مساعد Kutools AI لاستخراج قيم فريدة من أعمدة متعددة بسلاسة في Excel. من خلال بضع نقرات فقط، تقوم هذه الأداة الذكية بغربلة بياناتك وتحديد الإدخالات الفريدة وإدراجها عبر أي نطاق محدد. انسَ متاعب الصيغ المعقدة أو كود vba؛ واحتضن كفاءة مساعد Kutools AI وتحويل سير عمل Excel إلى تجربة أكثر إنتاجية وخالية من الأخطاء.

ملاحظات: لاستخدام هذا مساعد Kutools AI of كوتولس ل إكسيلالرجاء قم بتنزيل وتثبيت Kutools لبرنامج Excel لأول مرة.

بعد تثبيت كوتولس ل إكسيل ، الرجاء الضغط كوتولس منظمة العفو الدولية > مساعد الذكاء الاصطناعي لفتح مساعد Kutools AI جزء:

  1. اكتب طلبك في مربع الدردشة، ثم انقر فوق أرسل زر أو اضغط أدخل مفتاح لإرسال السؤال؛
    "استخرج قيمًا فريدة من النطاق A2:C9، متجاهلاً الخلايا الفارغة، ثم ضع النتائج بدءًا من E2:"
  2. بعد التحليل اضغط تنفيذ زر للتشغيل. سيقوم Kutools AI Aide بمعالجة طلبك باستخدام AI وإرجاع النتائج في الخلية المحددة مباشرة في Excel.


استخراج قيم فريدة من أعمدة متعددة باستخدام Pivot Table

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

1. في البداية ، يرجى إدخال عمود فارغ جديد على يسار بياناتك ، في هذا المثال ، سأدرج العمود A بجانب البيانات الأصلية.

2. انقر فوق خلية واحدة في البيانات الخاصة بك ، واضغط على ألت + D مفاتيح ، ثم اضغط P مفتاح على الفور لفتح معالج PivotTable و PivotChart، اختر نطاقات التوحيد المتعددة في المعالج الخطوة 1 ، انظر الصورة:

3. ثم اضغط التالى زر ، تحقق قم بإنشاء حقل صفحة واحد لي الخيار في المعالج الخطوة 2 ، انظر الصورة:

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

5. بعد تحديد نطاق البيانات ، تابع النقر فوق التالى، في خطوة المعالج 3 ، اختر المكان الذي تريد وضع تقرير PivotTable فيه كما تريد.

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

7. ثم تحقق من الحقل القيم أو اسحب القيمة إلى الصفوف التسمية ، ستحصل الآن على القيم الفريدة من الأعمدة المتعددة على النحو التالي:


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

باستخدام رمز VBA التالي ، يمكنك أيضًا استخراج القيم الفريدة من أعمدة متعددة.

1. اضغط باستمرار على ALT + F11 مفاتيح ، ويفتح نافذة ميكروسوفت فيسوال باسيك للتطبيقات.

2. انقر إدراج > وحدة، والصق التعليمة البرمجية التالية في نافذة الوحدة النمطية.

فبا: استخراج قيم فريدة من أعمدة متعددة

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. ثم انقر فوق OK، سيظهر مربع موجه آخر للسماح لك باختيار مكان لوضع النتيجة ، انظر لقطة الشاشة:

5. انقر OK لإغلاق مربع الحوار هذا ، وقد تم استخراج جميع القيم الفريدة مرة واحدة.


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

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

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

🤖 مساعد Kutools AI: إحداث ثورة في تحليل البيانات على أساس: التنفيذ الذكي   |  إنشاء التعليمات البرمجية  |  إنشاء صيغ مخصصة  |  تحليل البيانات وإنشاء الرسوم البيانية  |  استدعاء وظائف Kutools...
الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |   جولة بدون صيغة 
سوبر بحث: معايير متعددة VLookup    VLookup ذات القيمة المتعددة  |   VLookup عبر أوراق متعددة   |   بحث غامض ....
قائمة منسدلة متقدمة: إنشاء القائمة المنسدلة بسرعة   |  القائمة المنسدلة التابعة   |  قائمة منسدلة متعددة التحديد ....
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة  |  تبديل حالة رؤية الأعمدة المخفية  |  مقارنة النطاقات والأعمدة 
الميزات المميزة: التركيز على الشبكة   |  عرض تصميم   |   شريط الفورمولا الكبير    مدير المصنفات والأوراق   |  مكتبة الموارد (النص السيارات)   |  منتقي التاريخ   |  اجمع أوراق العمل   |  تشفير/فك تشفير الخلايا    إرسال رسائل البريد الإلكتروني عن طريق القائمة   |  سوبر تصفية   |   مرشح خاص (تصفية غامق / مائل / يتوسطه خط ...) ...
أفضل 15 مجموعة أدوات12 نص الأدوات (إضافة نص, إزالة الأحرف، ...)   |   +50 رسم الأنواع (مخطط جانت، ...)   |   40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد، ...)   |   19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار، ...)   |   12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة، ...)   |   7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم الخلايا، ...)   |   ... و اكثر

عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت.  انقر هنا للحصول على الميزة التي تحتاجها أكثر...

الوصف


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

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations