كيف يمكن تبديل خلايا عمود واحد بناءً على القيم الفريدة الموجودة في عمود آخر؟
لديك نطاق بيانات يحتوي على عمودين، وترغب في تبديل خلايا أحد الأعمدة إلى صفوف أفقية بناءً على القيم الفريدة في العمود الآخر للحصول على النتيجة المطلوبة. هل لديك أي أفكار فعّالة لحل هذه المشكلة في Excel؟
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام الصيغ
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام كود VBA
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام Kutools لـ Excel
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام الصيغ
باستخدام صيغ المصفوفة التالية، يمكنك استخراج القيم الفريدة وتحويل بياناتها المرتبطة إلى صفوف أفقية. يُرجى اتباع الخطوات التالية:
1. أدخل صيغة المصفوفة التالية:=INDEX($A$2:$A$16, MATCH(0, COUNTIF($D$1:$D1, $A$2:$A$16), 0)) في خلية فارغة، مثل D2، ثم اضغط على مفاتيحShift + Ctrl + Enter معًا للحصول على النتيجة الصحيحة. انظر لقطة الشاشة:
ملاحظة: في الصيغة أعلاه،A2:A16 هو العمود الذي تريد سرد القيم الفريدة منه، وD1 هي الخلية الموجودة فوق خلية هذه الصيغة.
2. بعد ذلك، اسحب مقبض التعبئة لأسفل إلى الخلايا لاستخراج جميع القيم الفريدة. انظر لقطة الشاشة:
3. بعد ذلك، أدخل هذه الصيغة في الخلية E2:=IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0)، ولا تنسَ الضغط على مفاتيحShift + Ctrl + Enter للحصول على النتيجة. انظر لقطة الشاشة:
ملاحظة: في الصيغة أعلاه،B2:B16 هو عمود البيانات الذي تريد تبديله، وA2:A16 هو العمود الذي تريد تبديل القيم استنادًا إليه، وD2 تحتوي على القيمة الفريدة التي استخرجتها في الخطوة 1.
4.بعد ذلك، اسحب مقبض التعبئة إلى يمين الخلايا التي تريد سرد البيانات المبدَّلة فيها حتى يظهر 0، انظر لقطة الشاشة:
5. ثم اسحب مقبض التعبئة لأسفل عبر نطاق الخلايا للحصول على البيانات المبدَّلة، كما هو موضح في لقطة الشاشة التالية:
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام كود VBA
قد تبدو الصيغ معقدة بعض الشيء، لذا يمكنك ببساطة تشغيل كود VBA التالي للحصول على النتيجة المطلوبة.
1. اضغط مع الاستمرار على مفاتيحALT + F11 لفتح نافذةMicrosoft Visual Basic for Applications.
2. انقر علىInsert > Module، ثم الصق الكود التالي في نافذةModule.
كود VBA: تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة في عمود آخر:
Sub transposeunique()
'updateby Extendoffice
Dim xLRow As Long
Dim i As Long
Dim xCrit As String
Dim xCol As New Collection
Dim xRg As Range
Dim xOutRg As Range
Dim xTxt As String
Dim xCount As Long
Dim xVRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If (xRg.Columns.Count <> 2) Or _
(xRg.Areas.Count > 1) Then
MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
Exit Sub
End If
Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
If xOutRg Is Nothing Then Exit Sub
Set xOutRg = xOutRg.Range(1)
xLRow = xRg.Rows.Count
For i = 2 To xLRow
xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
Next
Application.ScreenUpdating = False
For i = 1 To xCol.Count
xCrit = xCol.Item(i)
xOutRg.Offset(i, 0) = xCrit
xRg.AutoFilter Field:=1, Criteria1:=xCrit
Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
If xVRg.Count > xCount Then xCount = xVRg.Count
xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Next
xOutRg = xRg.Cells(1, 1)
xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
xRg.Rows(1).Copy
xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
xRg.AutoFilter
Application.ScreenUpdating = True
End Sub3.بعد ذلك، اضغط على مفتاحF5لتشغيل هذا الكود، وستظهر نافذة منبثقة لتذكيرك بتحديد نطاق البيانات الذي تريد استخدامه، انظر لقطة الشاشة:
4. بعد ذلك، انقر على زرOK، وستظهر نافذة منبثقة أخرى لتذكيرك بتحديد خلية لوضع النتيجة فيها. انظر لقطة الشاشة:
6. انقر على زرOK، وستكون البيانات في العمود B قد تم تبديلها استنادًا إلى القيم الفريدة في العمود A. انظر لقطة الشاشة:
تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام Kutools لـ Excel
إذا كنت تمتلكKutools لـ Excel، فيمكنك دمج أدواتالدمج المتقدم للصفوفوتقسيم الخلايالإنهاء هذه المهمة بسرعة دون الحاجة إلى أي صيغ أو أكواد.
بعد تثبيتKutools لـ Excel، يُرجى اتباع ما يلي:
1. حدد نطاق البيانات الذي تريد استخدامه. (إذا أردت الاحتفاظ بالبيانات الأصلية، فانسخها ولصقها في مكان آخر أولًا.)
2. ثم انقر علىKutools > دمج وتقسيم > دمج متقدم للصفوف، انظر لقطة الشاشة:
3. في مربع الحواردمج الصفوف Based on Column، يُرجى تنفيذ الخطوات التالية:
(1.) انقر على اسم العمود الذي تريد تبديل البيانات استنادًا إليه، ثم حددPrimary Key؛
（36.）انقر على عمود آخر تريد دمجه، ثم انقر علىCombine واختر فاصلًا لفصل البيانات المجمعة، مثل المسافة أو الفاصلة أو الفاصلة المنقوطة.
4. بعد ذلك، انقر على زرOk، وستتم دمج البيانات في العمود B معًا في خلية واحدة استنادًا إلى العمود A. راجع لقطة الشاشة:
5. بعد ذلك، حدد الخلايا المجمعة، ثم انقر علىKutools > دمج وتقسيم > تقسيم الخلايا. انظر لقطة الشاشة:
6. في مربع الحوارتقسيم الخلايا، حددتقسيم إلى أعمدةضمن خيارType، ثم اختر الفاصل الذي تم به فصل بياناتك المجمعة. انظر لقطة الشاشة:
7. بعد ذلك، انقر على زرOk، ثم حدد خليةً لوضع نتيجة التقسيم فيها من مربع الحوار المنبثق. انظر لقطة الشاشة:
8. انقر علىOK، وستحصل على النتيجة التي تحتاجها. انظر لقطة الشاشة:
حمّل Kutools لـ Excel وجربه مجانًا الآن!
عرض توضيحي: تبديل خلايا عمود واحد استنادًا إلى القيم الفريدة باستخدام Kutools لـ 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 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل