Как да транспонирам клетки в една колона въз основа на уникални стойности в друга колона?
Да предположим, че имате диапазон от данни, който съдържа две колони, сега искате да транспонирате клетки в една колона в хоризонтални редове въз основа на уникални стойности в друга колона, за да получите следния резултат. Имате ли добри идеи за решаване на този проблем в Excel?
Транспонирайте клетки в една колона въз основа на уникални стойности с формули
Транспонирайте клетки в една колона въз основа на уникални стойности с VBA код
Транспонирайте клетки в една колона въз основа на уникални стойности с Kutools за Excel
Транспонирайте клетки в една колона въз основа на уникални стойности с формули
Със следните формули за масиви можете да извлечете уникалните стойности и да транспонирате съответните им данни в хоризонтални редове, моля, направете следното:
1. Въведете тази формула за масив: =ИНДЕКС($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 за приложения прозорец.
2. Щракнете Поставете > Модулии поставете следния код в Модули Window.
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 Sub
3. След това натиснете F5 за да изпълните този код и ще изскочи поле за подкана, за да ви напомни да изберете диапазона от данни, който искате да използвате, вижте екранна снимка:
4, И после щракнете върху OK ще изскочи друго поле за подкана, за да ви напомни да изберете клетка, за да поставите резултата, вижте екранната снимка:
6. Щракнете OK и данните в колона B са транспонирани въз основа на уникални стойности в колона A, вижте екранната снимка:
Транспонирайте клетки в една колона въз основа на уникални стойности с Kutools за Excel
Ако имате Kutools за Excel, съчетавайки на Разширено комбиниране на редове намлява Разделени клетки помощни програми, можете бързо да завършите тази задача без никакви формули или код.
| Kutools за Excel : с повече от 300 удобни добавки за Excel, безплатни за изпробване без ограничение за 30 дни. |
След инсталиране Kutools за Excel, моля, направете следното:
1. Изберете диапазона от данни, който искате да използвате. (Ако искате да запазите оригиналните данни, моля, първо копирайте и поставете данните на друго място.)
2, След това кликнете Kutools > Обединяване и разделяне > Разширено комбиниране на редове, вижте екранна снимка:
3. В Комбинирайте редове въз основа на колона диалогов прозорец, моля, извършете следните операции:
(1.) Щракнете върху името на колоната, въз основа на която искате да транспонирате данни, и изберете Първичен ключ;
(2.) Щракнете върху друга колона, която искате да транспонирате, и щракнете Комбинирам след това изберете един разделител, за да разделите комбинираните данни, като интервал, запетая, точка и запетая.
4, След това кликнете Ok бутон, данните в колона B са комбинирани заедно в една клетка въз основа на колона A, вижте екранната снимка:
5. След това изберете комбинираните клетки и щракнете Kutools > Обединяване и разделяне > Разделени клетки, вижте екранна снимка:
6. В Разделени клетки изберете диалоговия прозорец Разделяне на колони под Тип и след това изберете разделителя, който разделя вашите комбинирани данни, вижте екранната снимка:
7, След това кликнете Ok и изберете клетка, за да поставите резултата от разделянето в изскачащия диалогов прозорец, вижте екранната снимка:
8. Щракнете OK, и ще получите желания резултат. Вижте екранна снимка:
Изтеглете и изпробвайте безплатно Kutools за Excel сега!
Демонстрация: Транспонирайте клетки в една колона въз основа на уникални стойности с Kutools за Excel
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
