Как транспонировать ячейки одного столбца на основе уникальных значений другого столбца?
Предположим, у вас есть диапазон данных, содержащий два столбца. Теперь вы хотите транспонировать ячейки одного столбца в горизонтальные строки на основе уникальных значений другого столбца, чтобы получить следующий результат. Есть ли у вас хорошие идеи для решения этой задачи в 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. Нажмите Вставка > Модуль и вставьте следующий код в окно Модуль.
Код 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. Затем нажмите кнопку ОК — после этого появится ещё одно диалоговое окно с запросом выбрать ячейку для размещения результата (см. скриншот):
6. Нажмите кнопку ОК, и данные из столбца B будут транспонированы на основе уникальных значений в столбце A (см. скриншот):
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью Kutools для Excel
Если у вас установлен Kutools для Excel, объединяющий возможности утилит Расширенное объединение строк и Разделить ячейки, вы сможете быстро выполнить эту задачу без использования формул или кода.
После установки Kutools для Excelвыполните следующие действия:
1. Выделите диапазон данных, который хотите использовать. (Если вы хотите сохранить исходные данные, сначала скопируйте и вставьте их в другое место.)
2. Затем нажмите Kutools > Объединить и разделить > Расширенное объединение строк (см. скриншот):
3. В диалоговом окне Объединить строки на основе столбца выполните следующие действия:
(1.) Щёлкните имя столбца, на основе которого вы хотите транспонировать данные, и выберите Первичный ключ;
(2.) Щёлкните другой столбец, который вы хотите транспонировать, нажмите Объединить, а затем выберите один из разделителей для объединённых данных — например, пробел, запятую или точку с запятой.
4. Затем нажмите кнопку ОК, и данные из столбца B объединятся в одной ячейке на основе значений из столбца A (см. скриншот):
5. Затем выделите объединённые ячейки и нажмите Kutools > Объединить и разделить > Разделить ячейки (см. скриншот):
6. В диалоговом окне Разделить ячейки выберите Разделить на столбцы в разделе Тип и укажите разделитель, который использовался при объединении данных (см. скриншот):
7. Затем нажмите кнопку ОК и выберите ячейку для размещения результата разделения в появившемся диалоговом окне (см. скриншот):
8. Нажмите кнопку ОК, и вы получите требуемый результат (см. скриншот):
Скачайте и бесплатно протестируйте Kutools для Excel прямо сейчас!
Демонстрация: Транспонирование ячеек в одном столбце на основе уникальных значений с помощью Kutools для Excel
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек