Как транспонировать ячейки в одном столбце на основе уникальных значений в другом столбце?
Предположим, у вас есть диапазон данных, содержащий два столбца, и вы хотите транспонировать ячейки одного столбца в горизонтальные строки на основе уникальных значений из другого столбца, чтобы получить следующий результат. Есть ли у вас какие-либо хорошие идеи для решения этой задачи в Excel?
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью формул
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью кода VBA
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью Kutools для Excel
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью формул
С помощью следующих формул массива вы можете извлечь уникальные значения и транспонировать соответствующие им данные в горизонтальные строки, выполните следующие действия:
1. Введите эту формулу массива: =ИНДЕКС($A$2:$A$16; ПОИСКПОЗ(0; СЧЁТЕСЛИ($D$1:$D1; $A$2:$A$16); 0)) в пустую ячейку, например D2, и нажмите клавиши Shift + Ctrl + Enter вместе, чтобы получить правильный результат, см. скриншот:
Примечание: В приведенной выше формуле A2:A16 — это столбец, из которого вы хотите перечислить уникальные значения, а D1 — это ячейка над этой формульной ячейкой.
2. Затем перетащите маркер заполнения вниз по ячейкам, чтобы извлечь все уникальные значения, см. скриншот:
3. Далее продолжайте вводить эту формулу в ячейку E2: =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16; ПОИСКПОЗ(0; СЧЁТЕСЛИ($D2:D2;$B$2:$B$16)+ЕСЛИ($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. Затем нажмите кнопку OK, появится еще одно окно с запросом выбрать ячейку для размещения результата, см. скриншот:
6. Нажмите кнопку OK, и данные в столбце B будут транспонированы на основе уникальных значений в столбце A, см. скриншот:
Транспонирование ячеек в одном столбце на основе уникальных значений с помощью Kutools для Excel
Если у вас есть Kutools для Excel, комбинируя функции Расширенное объединение строк и Разделение ячеек, вы сможете быстро выполнить эту задачу без использования формул или кода.
После установки Kutools для Excel выполните следующие действия:
1. Выберите диапазон данных, который вы хотите использовать. (Если вы хотите сохранить исходные данные, сначала скопируйте и вставьте данные в другое место.)
2. Затем нажмите Kutools > Объединить и разделить > Расширенное объединение строк, см. скриншот:
3. В диалоговом окне Объединение строк на основе столбца выполните следующие операции:
(1.) Щелкните имя столбца, на основе которого вы хотите транспонировать данные, и выберите Основной ключ;
(2.) Щелкните другой столбец, который вы хотите транспонировать, и нажмите Объединить, затем выберите разделитель для объединенных данных, например пробел, запятую, точку с запятой.
4. Затем нажмите кнопку Ok, и данные в столбце B будут объединены в одну ячейку на основе столбца A, см. скриншот:
5. Затем выберите объединенные ячейки и нажмите Kutools > Объединить и разделить > Разделить ячейки, см. скриншот:
6. В диалоговом окне Разделить ячейки выберите Разделить на столбцы в разделе Тип, а затем выберите разделитель, который разделяет ваши объединенные данные, см. скриншот:
7. Затем нажмите кнопку Ok и выберите ячейку для размещения результата разделения в появившемся диалоговом окне, см. скриншот:
8. Нажмите OK, и вы получите результат, который вам нужен. См. скриншот:
Скачайте и попробуйте 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек