كيفية إرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة؟

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

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 1

قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة بوظيفة محددة من قبل المستخدم

قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة باستخدام Kutools for Excel

قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة بوظيفة محددة من قبل المستخدم

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

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

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

رمز فبا: إرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. ثم احفظ هذا الرمز وأغلق نافذة الوحدة النمطية ، ثم عد إلى ورقة العمل الخاصة بك ، وأدخل هذه الصيغة: = SingleCellExtract (D2، A2: B15,2،XNUMX، "،") في خلية فارغة تريد إرجاع النتيجة. ثم اضغط على أدخل مفتاح للحصول على النتيجة ، انظر لقطة الشاشة:

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 2

ملاحظة: في الصيغة أعلاه:

D2: يشير إلى قيم الخلية التي تريد البحث عنها ؛

أ 2: ب 15: هو نطاق البيانات الذي تريد جلب البيانات ؛

2: الرقم 2 هو رقم العمود الذي سيتم إرجاع القيمة المطابقة ؛

,: الفاصلة هي الفاصل الذي تريده لفصل القيم المتعددة.

يمكنك تغييرها حسب حاجتك.

قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة باستخدام Kutools for Excel

إذا كان لديك كوتولس ل إكسيل، هذه المهمة لن تكون مشكلة بعد الآن. ال الجمع بين الصفوف المتقدمة قد تساعدك الأداة المساعدة في دمج جميع القيم النسبية بناءً على عمود.

كوتولس ل إكسيل : مع أكثر من 300 وظيفة إضافية مفيدة في Excel ، يمكنك تجربتها مجانًا دون قيود في أيام 30.

اذهب إلى Download
نسخة تجريبية مجانية 30 يومًا شراء
PayPal / MyCommerce

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

1. حدد نطاق البيانات الذي تريده لدمج كل القيم المتطابقة بناءً على عمود.

2. انقر كوتولس > دمج وتقسيم > الجمع بين الصفوف المتقدمة، انظر لقطة الشاشة:

3. في ضم الصفوف على أساس العمود في مربع الحوار ، انقر فوق اسم العمود الذي تريد الدمج بناءً عليه ، ثم انقر فوق "موافق" المفتاح الأساسي زر ، انظر لقطة الشاشة:

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 4

4. ثم انقر فوق اسم العمود الآخر الذي تريد دمج قيمها المتطابقة ، وانقر فوق دمج لاختيار فاصل واحد لفصل القيم المجمعة ، انظر الصورة:

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 5

5. ثم اضغط OK الزر ، تم دمج جميع الخلايا المقابلة بنفس القيمة في خلية واحدة مفصولة بفاصلة ، انظر لقطات الشاشة:

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 6

يُرجع المستند قيمًا متعددة مفصولة بفاصلة 7

قم بتنزيل Kutools for Excel والإصدار التجريبي المجاني الآن!

عرض توضيحي: قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة باستخدام Kutools for Excel

كوتولس ل إكسيل: مع أكثر من 300 وظيفة إضافية مفيدة في Excel ، يمكنك تجربتها مجانًا دون قيود خلال 30 يومًا. تنزيل وتجربة مجانية الآن!

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

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

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

اقرأ أكثر... تحميل مجاني ... شراء...

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

تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!

اقرأ أكثر... تحميل مجاني ... شراء...

التعليقات (16)

تقييم شنومك من شنومكس · تصنيفات 2

جانجادهار

منذ حوالي 5 سنوات

#19810

تم تصغير هذا التعليق بواسطة المشرف على الموقع

أثناء ذهابي للصق الوحدة وحفظها ، تظهر رسالة منبثقة تفيد بفقدان كبير لمدقق توافق الوظائف

رد

ضيف

منذ حوالي 5 سنوات

#20594

تم تصغير هذا التعليق بواسطة المشرف على الموقع

شكرا على هذا المنشور. هل تعرف كيف سأبدأ في معالجة العددين الصحيحين المنفصلين اللذين يتم تكوينهما. على سبيل المثال ، لنفترض أن دالة '= SingleCellExtract' تنتج الآن (1 ، 2). هل هناك طريقة لجعل خلية بجانبها (1 + .5، 2 + .5)؟

رد

ك راي

منذ حوالي 5 سنوات

#21678

تم تصغير هذا التعليق بواسطة المشرف على الموقع

هذا يعمل ولكنه يبطئ تفوقي بشكل كبير! أي نصائح للمساعدة في السرعة؟

رد

جائزة

منذ حوالي 5 سنوات

#21834

تم تصغير هذا التعليق بواسطة المشرف على الموقع

هذا ببساطة لا يعمل. لم أتمكن من تشغيله في تطبيقي الخاص ، لذلك قمت بنسخ / لصق vba والصيغة وأرجع خطأ في كل مرة

رد

إم ماكلام

منذ حوالي 5 سنوات

#23661

تم تصغير هذا التعليق بواسطة المشرف على الموقع

شكرًا لك ، أولاً تمكنت من جعل هذا يعمل دون التباطؤ في الأداء. أنا أستخدم القيم بدلاً من ذلك النص ، لذا سؤالي هو أنني أريد إعادة كل من لديهم أقل من 19 نقطة في القائمة. هل يمكن أن تعمل خلاصة الخلية المفردة لذلك أم يجب أن تكون قيمة محددة؟

رد

منذ حوالي 4 سنوات

#24897

تم تصغير هذا التعليق بواسطة المشرف على الموقع

ينكسر أمر VB عندما يكون النطاق أطول من 154 صفًا (على سبيل المثال: B154) ...

رد

أبهي

منذ حوالي 2 سنوات

#32700

تم تصغير هذا التعليق بواسطة المشرف على الموقع

ينبثق خطأ في حالة زيادة حجم المصفوفة

رد

بيبلاب داس

منذ حوالي 2 سنوات

#32802

تم تصغير هذا التعليق بواسطة المشرف على الموقع

عند تطابق معيارين ، قم بإرجاع قيم بحث متعددة في خلية واحدة مفصولة بفاصلة
A2 = B2 ثم الناتج من النطاق بواسطة "SingleCellExtract" - من فضلك .......

رد

كاثلين

منذ حوالي 9 أشهر

#37877

تم تصغير هذا التعليق بواسطة المشرف على الموقع

صباح الخير،

عملت التعليمات البرمجية لـ VBA بشكل مثالي مع ورقة العمل الخاصة بي ، وهي واضحة جدًا وبسيطة ، ومع ذلك ، كنت أحاول إيجاد طريقة لإخبار Excel بإرجاع القيم الفريدة فقط. هل سيكون ذلك ممكنا باستخدام هذا الرمز نفسه؟

رد

بلغ

تقييم شنومك من شنومكس

مانيكانتا

منذ حوالي 6 أشهر

#39460

تم تصغير هذا التعليق بواسطة المشرف على الموقع

مرحبا،

أريد رمز تحقق دقيقًا لقيم متعددة مفصولة بفاصلة ومسافة لكل قيمة.

على سبيل المثال:
لوسي ، توم ، نيكول ، أكاش ، أبل

يرجى إعادة إذا كان لديك أي اقتراحات.

رد

تقييم شنومك من شنومكس

سكايانغ مانيكانتا

منذ حوالي 5 أشهر

#39549

تم تصغير هذا التعليق بواسطة المشرف على الموقع

مرحبا ， مانيكانتا
لفصل القيم المتعددة بفاصلة ومسافة ، تحتاج فقط إلى إضافة مسافة خلف الفاصلة ، قم بتغيير الصيغة على النحو التالي: = SingleCellExtract (D2، A2: B15,2،XNUMX، "،").
من فضلك حاول ، آمل أن يساعدك!

رد

بلغ

مانيكانتا سكايانغ

منذ حوالي 5 أشهر

#39559

تم تصغير هذا التعليق بواسطة المشرف على الموقع

مرحبًا Skyyang ،

شكرا لاعادتها الخاص بك!

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

لوسي ، توم ، نيكول ، أكاش ، أبل ،

لن يعمل هذا مع ملف Json ، لذلك أريد القيم مفصولة بفاصلة ومسافة كما هو موضح أدناه.

لوسي ، توم ، نيكول ، أكاش ، أبل

شكرا!

رد

سكايانغ مانيكانتا

منذ حوالي 5 أشهر

#39567

تم تصغير هذا التعليق بواسطة المشرف على الموقع

مرحبا مانيكانتا
في هذه الحالة ، يمكنك تطبيق الوظيفة المحددة من قبل المستخدم أدناه:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function

بعد لصق الكود ، يرجى استخدام هذه الصيغة: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

من فضلك حاول ، آمل أن يساعدك هذا!
إذا كان لا يزال لديك أي مشكلة أخرى ، يرجى التعليق هنا.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png

رد

بلغ

مانيكانتا سكايانغ

منذ حوالي 5 أشهر

#39580

تم تصغير هذا التعليق بواسطة المشرف على الموقع

مرحبًا Skyyang ،

هذا يعمل الآن ، شكرًا لك على ردك السريع.

إنها مفيدة جدًا لي مرة أخرى شكرًا لك على مساعدتك.

التحيات،
مانيكانتا.

رد

تقييم شنومك من شنومكس

نعم

منذ حوالي 2 أشهر

#41721

تم تصغير هذا التعليق بواسطة المشرف على الموقع

No soy programador pero necesito ayuda para que la función de abajo en lugar de devolverme esto

S-01-08-0017-> Micro Post 1R ، أسود ، فولاذ مقاوم للصدأ - النهاية ، -> 4 ؛ S-01-08-0057-> Micro Post 2R ، أسود ، فولاذ مقاوم للصدأ - النهاية ، -> 2

لي devuelva los valores en líneas diferentes.
S-01-08-0017-> Micro Post 1R ، أسود ، الفولاذ المقاوم للصدأ - النهاية ، -> 4
S-01-08-0057-> Micro Post 2R ، أسود ، الفولاذ المقاوم للصدأ - النهاية ، -> 2

La Función es:
دالة SingleCellExtract (قيمة البحث كسلسلة ، نطاق البحث كنطاق ، رقم العمود كعدد صحيح ، حرف كسلسلة)
'تحديث بواسطة Extendoffice
أنا خافت وطويلة
خافت xRet كسلسلة
بالنسبة إلى I = 1 إلى LookupRange.Columns (1) .Cells.Count
إذا كان LookupRange.Cells (I، 1) = LookupValue ثم
إذا كان xRet = "" إذن
xRet = LookupRange.Cells (I ، ColumnNumber) & Char
آخر
xRet = xRet & "" & LookupRange.Cells (I ، ColumnNumber) & Char
إنهاء حالة
إنهاء حالة
التالى
SingleCellExtract = يسار (xRet ، Len (xRet) - 1)
نهاية وظيفة

رد

سكايانغ نعم

منذ حوالي 2 أشهر

#41787

تم تصغير هذا التعليق بواسطة المشرف على الموقع

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

Sub SplitAll()
    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim I As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
    If xRg Is Nothing Then Exit Sub
        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Kutools for Excel"
            Exit Sub
            End If
            Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")
            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False
                For Each xCell In xRg
                    xRet = Split(xCell.Value, ";")
                    xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    I = I + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

يرجى المحاولة ، آمل أن يساعدك!

رد

بلغ

لا توجد تعليقات منشورة هنا حتى الآن

اترك تعليقاتك

النشر كضيف

الدخول

الاسم (مطلوب)

البريد الإلكتروني (مطلوب)

قيم المنشور:

إعادة تعيين

0 الشخصيات

أنا أوافق على أحكام وشروط